Facility stock, inventory records and movements — Pro

Choose a movement period; browse current facility stock, lot/serial/storage records, matching movements and the full Product dossier.

UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.

For warehouse, purchasing, sales and management teams: choose a facility, inspect product stock contexts, then open inventory records with lot/serial, storage positions, received/manufactured/expiry dates and current unit cost with currency. Choose the movement period when opening Facilities; current stock is NOT an as-of balance. Open Movements to inspect stored quantity changes and document references. Product dossier opens the complete 33-field current catalogue. All four related actions load only when opened.

Why Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.

PRO: one authorised SQL Server source, five live read-only lists, one Home, same-row Details and four on-demand related actions. SELECT/WITH custom SQL; no periodic refresh, widgets or source writes. Each read is capped at 2,000 results. Search/local filters see loaded rows only. Inventory groups include the full selected facility before result capping; quantities are not a sum of a loaded page. This does not guarantee fast or cheap queries. Root facility discovery includes registered facilities, unassigned inventory and unknown facility references. Administrator-confirmed indexes, execution plans and timeouts are required.

STOCK MEANING: on-hand, available-to-promise and accounting quantities are separate stored InventoryItem values, not physical-count certification, stock valuation or the ERP sellable-availability service. Groups keep exact facility, product, inventory owner, inventory type, stored status and inventory unit. Different owners/statuses/types/units are never added together. Missing product/unit identity, a missing quantity or invalid inventory identity makes that group total unknown, not zero or a partial total. Zero and negatives remain. Serialized records are not converted into inferred quantities by counting rows; kit/BOM availability, substitutions, reservation eligibility and unit conversion services are not reimplemented. Counts are source records, not sold units. No cross-product warehouse quantity is shown.

DETAILS: facility owner and inventory owner are different roles. Names/units/status/type/reason/storage lookups are one-row-or-unavailable; absent/repeated masters cannot multiply quantities. Full facility + location identity is used, never location alone. Business facility/product references and useful lot/serial references are retained; technical route keys remain hidden. Exact UTF-16-encoded keys preserve NULL, empty, spaces, case and Unicode; case/collation-mismatched master references remain unresolved, not silently normalised. Confirm source integrity/collation with the administrator. Repeated/null inventory identities have diagnostics and no chosen quantities or drill-through. Product/storage/date values remain current source values, with no invented active flag, enum meaning or timezone.

MOVEMENTS: complete native identity is inventory item + detail sequence, scoped to the selected current inventory context. Date selection uses [start, exclusive end) before capping. Missing/duplicate detail identities and missing/out-of-period dates are excluded rather than arbitrarily chosen. History is retained source detail only, not a historical stock balance or historical product/owner/facility assignment. The displayed unit is explicitly the CURRENT item unit, not a certified historical unit. Movement unit cost is omitted because that detail row stores no historical currency. Current inventory record unit cost has its own current currency; no totals, currency conversions or comparisons across currencies are made. If the current item moves or its context changes between reads, reload the parent; old route parameters must not expose another context. Referenced order/shipment/return/count values are stored references only, not new joined document details.

PREREQUISITES: Apache OFBiz 24.09.07 official ordinary models, literal Java physical naming and mssql type file pinned at 42819e5ae1d5339d3a204ac06b43e69d46a9c0ae. Existing dbo objects are explicit adapter requirements, not proof of your installed owner/DDL/keys. The vendor supports SQL Server, but the shipped default is Derby; this is not a Derby/H2/PostgreSQL or derivative package. Requires SQL Server 2012+ for CONCAT/TRY_CONVERT. Confirm all selected columns, numeric/date types, complete unique non-null keys and authorised tenant/model extensions. Direct SQL does not inherit OFBiz roles or application ACLs; enforce restrictions at the source using a dedicated least-privilege SELECT login and validated TLS. Opening selections and parent predicates are not access control. No server, credentials, real rows, accounts, grants, installed objects or vendor read/write-service execution are supplied.

VALIDATION: documentary and explicitly adapted offline tests are not real ERP/SQL Server/iOS, installed DDL, performance, security or purchase certification. The gallery is the reviewed native fictional demonstration. Map your authorised source, run the read/compatibility test and inspect real results before business use. Existing workspace resources count toward plan limits. Primary source: https://github.com/apache/ofbiz-framework/tree/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/datamodel/entitydef ; https://github.com/apache/ofbiz-framework/blob/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/framework/common/entitydef/entitymodel.xml

PERIOD NAVIGATION: choose the movement period at the main report opening. The start and exclusive end are carried as typed, hidden values through stock contexts and inventory records. Movements uses those values without a second period dialog. Movement relation and record identities include the selected period, so previously loaded periods cannot match the new relation. To change the period, return to and reopen Facilities. Stock and the Product dossier remain current, not period-end snapshots. All five routes were demonstrated in native Cifru using wholly fictional data; not a real ERP/SQL Server or iOS execution test.

GALLERY: eight original native Cifru images represent all five configured routes: facility settings, complete stock-context and inventory cards, stored totals, current quantities/cost/currency, storage positions, both final sub-buttons, the complete selected movement dossier and current product identity/lifecycle. All four related actions and100 useful Detail positions were verified across21 private PNG/XML views. The eight public images are representative, not every configured field;68 Detail positions are paired in these public views. All values are wholly fictional DEMO, not a connected OFBiz installation. No value was retouched or field removed to fit. Test transport renders missing movement references as literal null, not a production SQL NULL-conversion test. Native monthly choice is October2026; stock remains current, not an as-of balance. Pro filming entitlement is not subscription purchase certification. Original PNG/XML/input and silent RAW remain private; PNG approval does not approve the whole RAW for publication.

Screenshots

What this package creates

1 Home4 Details5 lists1 sources to map

Home: Facilities and warehouses
Details: Products and stock contexts
Details: Inventory records
Details: Movements
Details: Product dossier
Sub-button: Stock by product and context
Sub-button: Inventory records and locations
Sub-button: Inventory movements
Sub-button: Product dossier

Sources are mapped locally and verified before applying.

Custom queriesPRO5 SQL

Custom queries are a PRO feature. Cifru repeats read-only validation against the local source before execution.

Statically verified read-only$.components.workspaceSelection.datasets.0.sqlQuery

WITH F AS (SELECT CASE WHEN FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), FACILITY_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(FACILITY_ID) ELSE NULL END AS FACILITY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(OWNER_PARTY_ID) ELSE NULL END AS OWNER_PARTY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(FACILITY_NAME) ELSE NULL END AS FACILITY_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(FACILITY_SIZE) ELSE NULL END AS FACILITY_SIZE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(FACILITY_SIZE_UOM_ID) ELSE NULL END AS FACILITY_SIZE_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DEFAULT_DAYS_TO_SHIP) ELSE NULL END AS DEFAULT_DAYS_TO_SHIP, CASE WHEN COUNT_BIG(*)=1 THEN MAX(OPENED_DATE) ELSE NULL END AS OPENED_DATE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(CLOSED_DATE) ELSE NULL END AS CLOSED_DATE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.FACILITY WHERE FACILITY_ID IS NOT NULL GROUP BY CASE WHEN FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), FACILITY_ID)), 2)) END), P AS (SELECT CASE WHEN PRODUCT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), PRODUCT_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PRODUCT_ID) ELSE NULL END AS PRODUCT_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PRODUCT_NAME) ELSE NULL END AS PRODUCT_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(INTERNAL_NAME) ELSE NULL END AS INTERNAL_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(BRAND_NAME) ELSE NULL END AS BRAND_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION, CASE WHEN COUNT_BIG(*)=1 THEN MAX(INTRODUCTION_DATE) ELSE NULL END AS INTRODUCTION_DATE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(RELEASE_DATE) ELSE NULL END AS RELEASE_DATE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SALES_DISCONTINUATION_DATE) ELSE NULL END AS SALES_DISCONTINUATION_DATE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SUPPORT_DISCONTINUATION_DATE) ELSE NULL END AS SUPPORT_DISCONTINUATION_DATE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(INVENTORY_MESSAGE) ELSE NULL END AS INVENTORY_MESSAGE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(REQUIRE_INVENTORY) ELSE NULL END AS REQUIRE_INVENTORY, CASE WHEN COUNT_BIG(*)=1 THEN MAX(QUANTITY_INCLUDED) ELSE NULL END AS QUANTITY_INCLUDED, CASE WHEN COUNT_BIG(*)=1 THEN MAX(QUANTITY_UOM_ID) ELSE NULL END AS QUANTITY_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PIECES_INCLUDED) ELSE NULL END AS PIECES_INCLUDED, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PRODUCT_WEIGHT) ELSE NULL END AS PRODUCT_WEIGHT, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SHIPPING_WEIGHT) ELSE NULL END AS SHIPPING_WEIGHT, CASE WHEN COUNT_BIG(*)=1 THEN MAX(WEIGHT_UOM_ID) ELSE NULL END AS WEIGHT_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PRODUCT_HEIGHT) ELSE NULL END AS PRODUCT_HEIGHT, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SHIPPING_HEIGHT) ELSE NULL END AS SHIPPING_HEIGHT, CASE WHEN COUNT_BIG(*)=1 THEN MAX(HEIGHT_UOM_ID) ELSE NULL END AS HEIGHT_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PRODUCT_WIDTH) ELSE NULL END AS PRODUCT_WIDTH, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SHIPPING_WIDTH) ELSE NULL END AS SHIPPING_WIDTH, CASE WHEN COUNT_BIG(*)=1 THEN MAX(WIDTH_UOM_ID) ELSE NULL END AS WIDTH_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PRODUCT_DEPTH) ELSE NULL END AS PRODUCT_DEPTH, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SHIPPING_DEPTH) ELSE NULL END AS SHIPPING_DEPTH, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DEPTH_UOM_ID) ELSE NULL END AS DEPTH_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(RETURNABLE) ELSE NULL END AS RETURNABLE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(TAXABLE) ELSE NULL END AS TAXABLE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(CHARGE_SHIPPING) ELSE NULL END AS CHARGE_SHIPPING, CASE WHEN COUNT_BIG(*)=1 THEN MAX(INCLUDE_IN_PROMOTIONS) ELSE NULL END AS INCLUDE_IN_PROMOTIONS, CASE WHEN COUNT_BIG(*)=1 THEN MAX(IS_VIRTUAL) ELSE NULL END AS IS_VIRTUAL, CASE WHEN COUNT_BIG(*)=1 THEN MAX(IS_VARIANT) ELSE NULL END AS IS_VARIANT, CASE WHEN COUNT_BIG(*)=1 THEN MAX(ORDER_DECIMAL_QUANTITY) ELSE NULL END AS ORDER_DECIMAL_QUANTITY FROM dbo.PRODUCT WHERE PRODUCT_ID IS NOT NULL GROUP BY CASE WHEN PRODUCT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), PRODUCT_ID)), 2)) END), T AS (SELECT CASE WHEN INVENTORY_ITEM_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVENTORY_ITEM_TYPE_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(INVENTORY_ITEM_TYPE_ID) ELSE NULL END AS INVENTORY_ITEM_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.INVENTORY_ITEM_TYPE WHERE INVENTORY_ITEM_TYPE_ID IS NOT NULL GROUP BY CASE WHEN INVENTORY_ITEM_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVENTORY_ITEM_TYPE_ID)), 2)) END), S AS (SELECT CASE WHEN STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), STATUS_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(STATUS_ID) ELSE NULL END AS STATUS_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.STATUS_ITEM WHERE STATUS_ID IS NOT NULL GROUP BY CASE WHEN STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), STATUS_ID)), 2)) END), U AS (SELECT CASE WHEN UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), UOM_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(UOM_ID) ELSE NULL END AS UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(ABBREVIATION) ELSE NULL END AS ABBREVIATION, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.UOM WHERE UOM_ID IS NOT NULL GROUP BY CASE WHEN UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), UOM_ID)), 2)) END), E AS (SELECT CASE WHEN ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), ENUM_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(ENUM_ID) ELSE NULL END AS ENUM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.ENUMERATION WHERE ENUM_ID IS NOT NULL GROUP BY CASE WHEN ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), ENUM_ID)), 2)) END), OwnerNames AS (SELECT CASE WHEN PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), PARTY_ID)), 2)) END AS OwnerKey, GROUP_NAME AS OwnerName FROM dbo.PARTY_GROUP WHERE PARTY_ID IS NOT NULL UNION ALL SELECT CASE WHEN PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), PARTY_ID)), 2)) END, CONCAT(FIRST_NAME, N' ', MIDDLE_NAME, N' ', LAST_NAME) FROM dbo.PERSON WHERE PARTY_ID IS NOT NULL), O AS (SELECT OwnerKey AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(OwnerName) ELSE NULL END AS OwnerName FROM OwnerNames GROUP BY OwnerKey), L AS (SELECT CONCAT(CASE WHEN FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), FACILITY_ID)), 2)) END, N'|', CASE WHEN LOCATION_SEQ_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), LOCATION_SEQ_ID)), 2)) END) AS LookupKey, COUNT_BIG(*) AS LookupRows, CASE WHEN COUNT_BIG(*)=1 THEN MAX(FACILITY_ID) ELSE NULL END AS FACILITY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(LOCATION_SEQ_ID) ELSE NULL END AS LOCATION_SEQ_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(AREA_ID) ELSE NULL END AS AREA_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(AISLE_ID) ELSE NULL END AS AISLE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(SECTION_ID) ELSE NULL END AS SECTION_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(LEVEL_ID) ELSE NULL END AS LEVEL_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(POSITION_ID) ELSE NULL END AS POSITION_ID FROM dbo.FACILITY_LOCATION WHERE FACILITY_ID IS NOT NULL AND LOCATION_SEQ_ID IS NOT NULL GROUP BY CONCAT(CASE WHEN FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), FACILITY_ID)), 2)) END, N'|', CASE WHEN LOCATION_SEQ_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), LOCATION_SEQ_ID)), 2)) END)), FacilityRefs AS (SELECT CASE WHEN FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), FACILITY_ID)), 2)) END AS FacilityKey, FACILITY_ID FROM dbo.FACILITY WHERE FACILITY_ID IS NOT NULL UNION SELECT CASE WHEN FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), FACILITY_ID)), 2)) END, FACILITY_ID FROM dbo.INVENTORY_ITEM) SELECT r.FacilityKey AS FacilityKey, CASE WHEN F.LookupRows=1 AND F.FACILITY_NAME IS NOT NULL THEN F.FACILITY_NAME WHEN r.FacilityKey=N'N' THEN N'Facility not assigned' ELSE N'Facility master unresolved' END AS Warehouse, COALESCE(r.FACILITY_ID,N'Not assigned') AS FacilityReference, CASE WHEN O.LookupRows=1 AND O.OwnerName IS NOT NULL THEN O.OwnerName ELSE N'Facility owner unresolved' END AS FacilityOwner, CONVERT(nvarchar(100), F.FACILITY_SIZE) AS FacilitySize, CASE WHEN U.LookupRows=1 AND U.DESCRIPTION IS NOT NULL THEN U.DESCRIPTION ELSE N'Facility-size unit unresolved' END AS FacilitySizeUnit, CONVERT(nvarchar(100), F.DEFAULT_DAYS_TO_SHIP) AS DefaultShipDays, CONVERT(nvarchar(100), F.OPENED_DATE, 126) AS Opened, CONVERT(nvarchar(100), F.CLOSED_DATE, 126) AS Closed, F.DESCRIPTION AS FacilityDescription, CASE WHEN F.LookupRows=1 THEN N'Unique Facility master' WHEN F.LookupRows IS NULL THEN N'Facility master missing' ELSE N'Facility master repeated — values not chosen' END AS FacilityState, CONVERT(nvarchar(10), CAST(:date_from AS date), 23) AS ScopeStart, CONVERT(nvarchar(10), CAST(:date_until AS date), 23) AS ScopeEnd FROM FacilityRefs r LEFT JOIN F ON F.LookupKey=r.FacilityKey LEFT JOIN O ON O.LookupKey=CASE WHEN F.OWNER_PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), F.OWNER_PARTY_ID)), 2)) END LEFT JOIN U ON U.LookupKey=CASE WHEN F.FACILITY_SIZE_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), F.FACILITY_SIZE_UOM_ID)), 2)) END WHERE CONVERT(nvarchar(10), CAST(:date_from AS date), 23) < CONVERT(nvarchar(10), CAST(:date_until AS date), 23)