Sales orders, lines, shipping and party dossiers — Pro

Choose order dates; inspect lines/products, shipping destinations and allocations, explicit parties, adjustments and terms.

UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.

For sales, warehouse, finance and managers: choose an order-date period, then inspect useful status, currency, dates and stored amounts. Open lines, current product dossiers, shipping groups, group allocations, explicit parties and roles, adjustments and terms on demand.

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.

IN THIS CONFIGURATION
Eight live screens and seven related buttons: order to lines to Current product; order to Shipping groups to Group allocations; order to Parties and roles, Adjustments or Terms. The required opening period selects order dates, not historical balances. Both date boundaries and the complete order/type/currency context travel to child screens. Reopen Orders to change the period.

PRO PLAN
One authorised SQL Server source, one Home and eight live read-only lists. Each result is capped at 2,000 rows; Search and local filters inspect loaded results only. Related data loads when its button is opened. Exact source numbers are shown as text, not rounded; numeric filtering is not promised. No writes, periodic refresh or widgets. Existing workspace resources count toward plan limits.

QUANTITIES, PRICES AND TOTALS
Stored grand total and remaining subtotal are source fields, not our reconstruction of the ERP display, unpaid balance or unshipped value. Source quantity and cancelled quantity stay separate. A shipping-group allocation is not the whole line; neither quantity proves delivery, receipt or stock availability. Unit, list and recurring prices are distinct; currency and recurring frequency remain visible. All retained line statuses, including cancellations, can be inspected. No line-total, margin, FX or cross-order total is invented.

SHIPPING, PARTIES AND SOURCE RULES
Registered shipping groups include empty groups. Tracking and expected dates are not delivery proof. Parties remain separate in their explicit source roles, rather than choosing an arbitrary main customer/vendor. Names, addresses, products and lookups are current rows, not historical order snapshots. Separate reads are not a consistent transaction snapshot. Stored adjustment amounts, percentages, recurring values, already included amounts and exemptions have different meanings; an absent amount can still describe a percentage rule. No tax/adjustment sum is reconstructed. Terms retain raw value, text, days and unit; the order currency does not establish a term value unit or percentage meaning.

BEFORE USING
For Apache OFBiz 24.09.07 on an EXISTING authorised SQL Server 2012+ deployment with the selected dbo objects. OFBiz ships with Derby by default; this is not a Derby, H2 or PostgreSQL adapter. Your administrator must verify installed physical names/types/full keys, extensions, timezone, indexes, execution plans and timeouts. A 2,000-row cap does not guarantee a cheap query. Duplicate/null identities are excluded and ambiguous lookups remain unavailable; this does not certify data quality or source security. Reload the parent when relevant source context changes.

ACCESS AND CONFIDENTIALITY
Use validated TLS and an administrator-created least-privilege SELECT account with source-enforced company/tenant/role restrictions. Direct SQL does not inherit OFBiz application permissions; an opening/local filter is not an ACL. Technical relationship keys remain hidden. Source addresses, comments and business references may be confidential: an adjustment customer reference can contain a party tax identifier according to the official model. Authorise these fields appropriately. No connection credentials or production rows are included in the package or gallery; all examples are wholly fictional DEMO. Map only your own authorised source and run Cifru read/compatibility tests before business use.

VALIDATION AND GALLERY
Documentary and adapted synthetic tests plus native Android demonstrations: not a real ERP/SQL Server/iOS execution, security, performance or purchase certification. All eight routes, seven buttons and 178 useful Detail positions were checked privately. Eight representative native photos cover all routes and related buttons, not every one of the 178 fields or every complete card in eight images. The test transport displays absent values as literal null; production SQL NULL handling is not certified. Original screenshots and whole private RAW recordings remain available for future videos.

PRIMARY DOCUMENTATION
https://github.com/apache/ofbiz-framework/blob/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/datamodel/entitydef/order-entitymodel.xml
https://github.com/apache/ofbiz-framework/blob/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/order/src/main/java/org/apache/ofbiz/order/order/OrderReadHelper.java

Screenshots

What this package creates

1 Home7 Details8 lists1 sources to map

Home: Sales orders
Details: Order lines
Details: Shipping groups
Details: Parties and source roles
Details: Adjustments
Details: Terms
Details: Group allocations
Details: Current product dossier
Sub-button: Order lines
Sub-button: Shipping groups
Sub-button: Group allocations
Sub-button: Parties and source roles
Sub-button: Adjustments
Sub-button: Terms
Sub-button: Current product dossier

Sources are mapped locally and verified before applying.

Custom queriesPRO8 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 H0 AS (SELECT o.ORDER_ID, o.ORDER_TYPE_ID, o.ORDER_NAME, o.EXTERNAL_ID, o.SALES_CHANNEL_ENUM_ID, o.ORDER_DATE, o.PRIORITY, o.ENTRY_DATE, o.PICK_SHEET_PRINTED_DATE, o.STATUS_ID, o.CURRENCY_UOM, o.ORIGIN_FACILITY_ID, o.PRODUCT_STORE_ID, o.NEEDS_INVENTORY_ISSUANCE, o.IS_RUSH_ORDER, o.REMAINING_SUB_TOTAL, o.GRAND_TOTAL, o.INVOICE_PER_SHIPMENT, (SELECT COUNT_BIG(*) FROM dbo.ORDER_HEADER z WHERE z.ORDER_ID=o.ORDER_ID AND CASE WHEN z.ORDER_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.ORDER_ID)), 2)) END=CASE WHEN o.ORDER_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), o.ORDER_ID)), 2)) END) AS IdentityCopies FROM dbo.ORDER_HEADER o WHERE o.ORDER_ID IS NOT NULL AND CASE WHEN o.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), o.ORDER_TYPE_ID)), 2)) END=CASE WHEN N'SALES_ORDER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'SALES_ORDER')), 2)) END AND o.ORDER_DATE>=CAST(:date_from AS date) AND o.ORDER_DATE<CAST(:date_until AS date) AND CAST(:date_from AS date)<CAST(:date_until AS date)),
H AS (SELECT o.*, CONCAT(N'O|', CASE WHEN o.ORDER_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), o.ORDER_ID)), 2)) END) AS OrderKey, CONCAT(CONCAT(N'O|', CASE WHEN o.ORDER_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), o.ORDER_ID)), 2)) END), N'|T|', CASE WHEN o.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), o.ORDER_TYPE_ID)), 2)) END, N'|C|', CASE WHEN o.CURRENCY_UOM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), o.CURRENCY_UOM)), 2)) END, N'|P|', CASE WHEN CONVERT(nvarchar(10), CAST(:date_from AS date), 23) IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), CONVERT(nvarchar(10), CAST(:date_from AS date), 23))), 2)) END, N'|', CASE WHEN CONVERT(nvarchar(10), CAST(:date_until AS date), 23) IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), CONVERT(nvarchar(10), CAST(:date_until AS date), 23))), 2)) END) AS OrderScopeKey, CONVERT(nvarchar(10), CAST(:date_from AS date), 23) AS ScopeStart, CONVERT(nvarchar(10), CAST(:date_until AS date), 23) AS ScopeEnd FROM H0 o WHERE o.IdentityCopies=1),
OT AS (SELECT CASE WHEN m.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.ORDER_TYPE_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.ORDER_TYPE_ID) ELSE NULL END AS ORDER_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PARENT_TYPE_ID) ELSE NULL END AS PARENT_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.ORDER_TYPE m WHERE m.ORDER_TYPE_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.ORDER_TYPE_ID=m.ORDER_TYPE_ID AND CASE WHEN H.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.ORDER_TYPE_ID)), 2)) END=CASE WHEN m.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.ORDER_TYPE_ID)), 2)) END)) GROUP BY CASE WHEN m.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.ORDER_TYPE_ID)), 2)) END),
ST AS (SELECT CASE WHEN m.STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.STATUS_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.STATUS_ID) ELSE NULL END AS STATUS_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.STATUS_ITEM m WHERE m.STATUS_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.STATUS_ID=m.STATUS_ID AND CASE WHEN H.STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.STATUS_ID)), 2)) END=CASE WHEN m.STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.STATUS_ID)), 2)) END)) GROUP BY CASE WHEN m.STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.STATUS_ID)), 2)) END),
U AS (SELECT CASE WHEN m.UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.UOM_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.UOM_ID) ELSE NULL END AS UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.UOM_TYPE_ID) ELSE NULL END AS UOM_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.ABBREVIATION) ELSE NULL END AS ABBREVIATION, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.UOM m WHERE m.UOM_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.CURRENCY_UOM=m.UOM_ID AND CASE WHEN H.CURRENCY_UOM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.CURRENCY_UOM)), 2)) END=CASE WHEN m.UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.UOM_ID)), 2)) END)) GROUP BY CASE WHEN m.UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.UOM_ID)), 2)) END),
F AS (SELECT CASE WHEN m.FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.FACILITY_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.FACILITY_ID) ELSE NULL END AS FACILITY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.FACILITY_NAME) ELSE NULL END AS FACILITY_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.OWNER_PARTY_ID) ELSE NULL END AS OWNER_PARTY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.FACILITY m WHERE m.FACILITY_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.ORIGIN_FACILITY_ID=m.FACILITY_ID AND CASE WHEN H.ORIGIN_FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.ORIGIN_FACILITY_ID)), 2)) END=CASE WHEN m.FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.FACILITY_ID)), 2)) END)) GROUP BY CASE WHEN m.FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.FACILITY_ID)), 2)) END),
PS AS (SELECT CASE WHEN m.PRODUCT_STORE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PRODUCT_STORE_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PRODUCT_STORE_ID) ELSE NULL END AS PRODUCT_STORE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.STORE_NAME) ELSE NULL END AS STORE_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.COMPANY_NAME) ELSE NULL END AS COMPANY_NAME FROM dbo.PRODUCT_STORE m WHERE m.PRODUCT_STORE_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.PRODUCT_STORE_ID=m.PRODUCT_STORE_ID AND CASE WHEN H.PRODUCT_STORE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PRODUCT_STORE_ID)), 2)) END=CASE WHEN m.PRODUCT_STORE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PRODUCT_STORE_ID)), 2)) END)) GROUP BY CASE WHEN m.PRODUCT_STORE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PRODUCT_STORE_ID)), 2)) END),
E AS (SELECT CASE WHEN m.ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.ENUM_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.ENUM_ID) ELSE NULL END AS ENUM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.ENUM_TYPE_ID) ELSE NULL END AS ENUM_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.ENUMERATION m WHERE m.ENUM_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.SALES_CHANNEL_ENUM_ID=m.ENUM_ID AND CASE WHEN H.SALES_CHANNEL_ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.SALES_CHANNEL_ENUM_ID)), 2)) END=CASE WHEN m.ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.ENUM_ID)), 2)) END)) GROUP BY CASE WHEN m.ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.ENUM_ID)), 2)) END)
SELECT h.OrderKey AS OrderKey, h.OrderScopeKey AS OrderScopeKey, h.ScopeStart AS ScopeStart, h.ScopeEnd AS ScopeEnd, h.ORDER_ID AS SourceOrderId, h.OrderKey AS EntityKey, h.OrderScopeKey AS ChildScopeKey, h.ORDER_ID AS OrderReference, h.CURRENCY_UOM AS OrderCurrency, h.ORDER_DATE AS OrderDate, CONVERT(nvarchar(100), h.ORDER_DATE, 126) AS OrderDateText, h.ORDER_NAME AS Name, h.EXTERNAL_ID AS ExternalReference, h.PRIORITY AS PriorityCode, CONVERT(nvarchar(100), h.ENTRY_DATE, 126) AS Entered, CONVERT(nvarchar(100), h.PICK_SHEET_PRINTED_DATE, 126) AS PickSheetPrinted, h.NEEDS_INVENTORY_ISSUANCE AS InventoryIssuanceCode, h.IS_RUSH_ORDER AS RushCode, CONVERT(nvarchar(100), h.GRAND_TOTAL) AS StoredGrandTotal, CONVERT(nvarchar(100), h.REMAINING_SUB_TOTAL) AS StoredRemainingSubtotal, h.INVOICE_PER_SHIPMENT AS InvoicePerShipmentCode, CASE WHEN OT.Copies=1 AND OT.DESCRIPTION IS NOT NULL THEN OT.DESCRIPTION ELSE N'Order type unresolved' END AS OrderType, CASE WHEN ST.Copies=1 AND ST.DESCRIPTION IS NOT NULL THEN ST.DESCRIPTION ELSE N'Order status unresolved' END AS Status, CASE WHEN U.Copies=1 AND U.DESCRIPTION IS NOT NULL THEN U.DESCRIPTION ELSE N'Order currency unresolved' END AS CurrencyDescription, CASE WHEN F.Copies=1 AND F.FACILITY_NAME IS NOT NULL THEN F.FACILITY_NAME ELSE N'Origin facility unresolved' END AS OriginFacility, CASE WHEN PS.Copies=1 AND PS.STORE_NAME IS NOT NULL THEN PS.STORE_NAME ELSE N'Store unresolved' END AS Store, CASE WHEN E.Copies=1 AND E.DESCRIPTION IS NOT NULL THEN E.DESCRIPTION ELSE N'Sales channel unresolved' END AS SalesChannel, PS.COMPANY_NAME AS StoreCompany, N'Stored header amounts; not the recalculated ERP display, open balance or a historical snapshot.' AS Meaning FROM H h LEFT JOIN OT OT ON OT.LookupKey=CASE WHEN h.ORDER_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.ORDER_TYPE_ID)), 2)) END LEFT JOIN ST ST ON ST.LookupKey=CASE WHEN h.STATUS_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.STATUS_ID)), 2)) END LEFT JOIN U U ON U.LookupKey=CASE WHEN h.CURRENCY_UOM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.CURRENCY_UOM)), 2)) END LEFT JOIN F F ON F.LookupKey=CASE WHEN h.ORIGIN_FACILITY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.ORIGIN_FACILITY_ID)), 2)) END LEFT JOIN PS PS ON PS.LookupKey=CASE WHEN h.PRODUCT_STORE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PRODUCT_STORE_ID)), 2)) END LEFT JOIN E E ON E.LookupKey=CASE WHEN h.SALES_CHANNEL_ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.SALES_CHANNEL_ENUM_ID)), 2)) END