Supplier purchase invoices, lines and payment details — Pro

Choose invoice dates; inspect amounts/currency, full lines/products, individual allocations/payment details and stored terms.

UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.

For purchasing/accounts payable and managers: choose an invoice-date period, inspect the issuer, recipient, external reference, due date, currency, default-profile total and current allocated/unapplied amounts. Open all invoice lines, their full current product dossier, allocation-level payment details and stored invoice/line terms without loading every child at once.

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
Choose an invoice-date period, then see the customer/supplier, invoice and external references, dates, currency, default-profile total and current allocated/unallocated amounts. Three buttons open invoice lines, individual allocations with payment details, and stored terms. From a product line, open its current product dossier. These are current enquiry views, not historical balances or certified ledger/aging reports. Current product/party names are not historical invoice snapshots.

PRO PLAN
One authorised SQL Server source, five live lists and four on-demand related buttons. Each result is capped at 2,000 rows; invoice totals consider matching source lines/allocations before that cap. Search/local filters only inspect loaded results. Exact source numbers are displayed as text; numeric filtering is not promised. No scheduled refresh, writes, executable services or widgets. Existing workspace resources count toward your plan limits.

UNDERSTANDING THE AMOUNTS
Invoice-line AMOUNT is a unit price, not the line total. The pinned default InvoiceWorker profile uses invoice.decimals=2 and HALF_UP for both sales and purchase; tax extensions first use 3 decimals and are grouped by the source tax authority/geography before rounding each group to 2. Tax types come from the live TAXABLE_INV_ITM_TY classification, not the taxable flag or an inferred rate. Missing quantity/amount default to 1/0 only in calculations; displayed source values stay missing. The rounded extension shown on a tax line is not necessarily its grouped contribution. Verify installed arithmetic settings before use. Missing/duplicate identity or invalid classification makes affected totals unavailable, not zero. Subtotals/applied amounts and the combined invoice total must fit 35 integer digits plus 2 decimals; aggregate overflow can still fail a read.

PAYMENTS AND TERMS
An allocation is expressed in invoice currency. Whole-payment and actual-currency amounts are separate; no FX rate is guessed. Payments with a NULL effective date or date at/before the parent source-clock cutoff are included; future allocations remain visible but excluded from current allocated totals. Recorded status/paid date is not proof of collection. Children inherit the parent read-time context; check the source clock/timezone and reopen after data changes. Separate reads are not a transactional snapshot. Terms show raw text/value/unit/days: no inferred discount, percentage or interest. Explicit account overrides are not complete posting distributions.

BEFORE USING
For Apache OFBiz 24.09.07 on an existing SQL Server 2012+ installation with the selected dbo objects and pinned calculation profile. OFBiz ships with Derby by default; this is not a Derby, H2 or PostgreSQL adapter. Your administrator must verify physical columns/types/full keys, tenant restrictions, TLS, indexes and read performance. Direct SQL does not inherit OFBiz application permissions: use a least-privilege SELECT account. An opening/local filter is not an ACL. Map only an authorised source and run Cifru read/compatibility tests before business use. Useful business references are shown, technical relationship keys remain hidden. Credentials, bank/card instrument details and production data are not included.

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 five routes/four buttons and133 useful Detail positions were checked privately. The eight native photos are representative, not every133 field in eight images. Every example is wholly fictional DEMO. The test transport displays absent values as literal null; production SQL NULL handling is not certified. Original screenshots and private RAW recordings are retained for future videos.

PRIMARY DOCUMENTATION
https://github.com/apache/ofbiz-framework/tree/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/datamodel/entitydef
https://github.com/apache/ofbiz-framework/blob/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/accounting/src/main/java/org/apache/ofbiz/accounting/invoice/InvoiceWorker.java

Screenshots

What this package creates

1 Home4 Details5 lists1 sources to map

Home: Supplier purchase invoices
Details: Invoice lines
Details: Allocations and payment details
Details: Invoice terms
Details: Current product dossier
Sub-button: Invoice lines
Sub-button: Allocations and payment details
Sub-button: Invoice terms
Sub-button: Current 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 Clock AS (SELECT CONVERT(nvarchar(33), SYSDATETIME(), 126) AS SourceAsOf),
Selected AS (SELECT MAX(i.INVOICE_ID) AS INVOICE_ID FROM dbo.INVOICE i WHERE i.INVOICE_ID IS NOT NULL AND CASE WHEN i.INVOICE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), i.INVOICE_TYPE_ID)), 2)) END=CASE WHEN N'PURCHASE_INVOICE' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'PURCHASE_INVOICE')), 2)) END AND i.INVOICE_DATE>=CAST(:date_from AS date) AND i.INVOICE_DATE<CAST(:date_until AS date) AND :date_from<:date_until GROUP BY CASE WHEN i.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), i.INVOICE_ID)), 2)) END),
HeaderRaw AS (SELECT i.INVOICE_ID, i.INVOICE_TYPE_ID, i.PARTY_ID_FROM, i.PARTY_ID, i.ROLE_TYPE_ID, i.STATUS_ID, i.BILLING_ACCOUNT_ID, i.CONTACT_MECH_ID, i.INVOICE_DATE, i.DUE_DATE, i.PAID_DATE, i.INVOICE_MESSAGE, i.REFERENCE_NUMBER, i.DESCRIPTION, i.CURRENCY_UOM_ID, i.RECURRENCE_INFO_ID, (SELECT COUNT_BIG(*) FROM dbo.INVOICE z WHERE z.INVOICE_ID=i.INVOICE_ID AND CASE WHEN z.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.INVOICE_ID)), 2)) END=CASE WHEN i.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), i.INVOICE_ID)), 2)) END) AS GlobalCopies FROM dbo.INVOICE i WHERE EXISTS (SELECT 1 FROM Selected s WHERE s.INVOICE_ID=i.INVOICE_ID AND CASE WHEN s.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), s.INVOICE_ID)), 2)) END=CASE WHEN i.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), i.INVOICE_ID)), 2)) END)),
H AS (SELECT MAX(INVOICE_ID) AS INVOICE_ID, MAX(GlobalCopies) AS GlobalCopies, CASE WHEN INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_ID)), 2)) END AS InvoiceLookupKey, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(INVOICE_TYPE_ID) ELSE NULL END AS INVOICE_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(PARTY_ID_FROM) ELSE NULL END AS PARTY_ID_FROM, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(PARTY_ID) ELSE NULL END AS PARTY_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(ROLE_TYPE_ID) ELSE NULL END AS ROLE_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(STATUS_ID) ELSE NULL END AS STATUS_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(BILLING_ACCOUNT_ID) ELSE NULL END AS BILLING_ACCOUNT_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(CONTACT_MECH_ID) ELSE NULL END AS CONTACT_MECH_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(INVOICE_DATE) ELSE NULL END AS INVOICE_DATE, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(DUE_DATE) ELSE NULL END AS DUE_DATE, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(PAID_DATE) ELSE NULL END AS PAID_DATE, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(INVOICE_MESSAGE) ELSE NULL END AS INVOICE_MESSAGE, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(REFERENCE_NUMBER) ELSE NULL END AS REFERENCE_NUMBER, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(CURRENCY_UOM_ID) ELSE NULL END AS CURRENCY_UOM_ID, CASE WHEN COUNT_BIG(*)=1 AND MAX(GlobalCopies)=1 THEN MAX(RECURRENCE_INFO_ID) ELSE NULL END AS RECURRENCE_INFO_ID FROM HeaderRaw GROUP BY CASE WHEN INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_ID)), 2)) END),
TaxRaw AS (SELECT e.ENUM_ID, (SELECT COUNT_BIG(*) FROM dbo.ENUMERATION z WHERE CASE WHEN z.ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.ENUM_ID)), 2)) END=CASE WHEN e.ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), e.ENUM_ID)), 2)) END) AS GlobalCopies FROM dbo.ENUMERATION e WHERE CASE WHEN e.ENUM_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), e.ENUM_TYPE_ID)), 2)) END=CASE WHEN N'TAXABLE_INV_ITM_TY' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'TAXABLE_INV_ITM_TY')), 2)) END),
TaxState AS (SELECT COALESCE(SUM(CASE WHEN ENUM_ID IS NULL OR GlobalCopies<>1 THEN 1 ELSE 0 END),0) AS InvalidTaxEnums FROM TaxRaw),
Lines AS (SELECT l.INVOICE_ID, l.INVOICE_ITEM_SEQ_ID, l.INVOICE_ITEM_TYPE_ID, l.OVERRIDE_GL_ACCOUNT_ID, l.OVERRIDE_ORG_PARTY_ID, l.INVENTORY_ITEM_ID, l.PRODUCT_ID, l.PRODUCT_FEATURE_ID, l.PARENT_INVOICE_ID, l.PARENT_INVOICE_ITEM_SEQ_ID, l.UOM_ID, l.TAXABLE_FLAG, l.QUANTITY, l.AMOUNT, l.DESCRIPTION, l.TAX_AUTH_PARTY_ID, l.TAX_AUTH_GEO_ID, l.TAX_AUTHORITY_RATE_SEQ_ID, l.SALES_OPPORTUNITY_ID, (SELECT COUNT_BIG(*) FROM dbo.INVOICE_ITEM z WHERE z.INVOICE_ID=l.INVOICE_ID AND CASE WHEN z.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.INVOICE_ID)), 2)) END=CASE WHEN l.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.INVOICE_ID)), 2)) END AND CASE WHEN z.INVOICE_ITEM_SEQ_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.INVOICE_ITEM_SEQ_ID)), 2)) END=CASE WHEN l.INVOICE_ITEM_SEQ_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.INVOICE_ITEM_SEQ_ID)), 2)) END) AS FullCopies, CASE WHEN EXISTS (SELECT 1 FROM TaxRaw e WHERE CASE WHEN e.ENUM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), e.ENUM_ID)), 2)) END=CASE WHEN l.INVOICE_ITEM_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.INVOICE_ITEM_TYPE_ID)), 2)) END) THEN 1 ELSE 0 END AS IsTax FROM dbo.INVOICE_ITEM l WHERE EXISTS (SELECT 1 FROM H WHERE H.INVOICE_ID=l.INVOICE_ID AND CASE WHEN H.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.INVOICE_ID)), 2)) END=CASE WHEN l.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.INVOICE_ID)), 2)) END)),
LineState AS (SELECT CASE WHEN INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_ID)), 2)) END AS InvoiceLookupKey, COUNT_BIG(*) AS LineRows, COALESCE(SUM(CASE WHEN INVOICE_ITEM_SEQ_ID IS NULL OR FullCopies<>1 OR INVOICE_ITEM_TYPE_ID IS NULL THEN 1 ELSE 0 END),0) AS InvalidLines, COALESCE(SUM(CASE WHEN IsTax=1 AND DATALENGTH(TAX_AUTH_PARTY_ID)=0 THEN 1 ELSE 0 END),0) AS BlankAuthorities, SUM(CASE WHEN IsTax=0 THEN CONVERT(decimal(37,2), ROUND((COALESCE(QUANTITY, 1) * COALESCE(AMOUNT, 0)), 2)) ELSE 0 END) AS NonTaxTotal FROM Lines GROUP BY CASE WHEN INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_ID)), 2)) END),
TaxGroups AS (SELECT CASE WHEN l.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.INVOICE_ID)), 2)) END AS InvoiceLookupKey, CASE WHEN l.TAX_AUTH_PARTY_ID IS NULL THEN N'Unattributed' ELSE CONCAT(N'T|', CASE WHEN l.TAX_AUTH_PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.TAX_AUTH_PARTY_ID)), 2)) END, N'|G|', CASE WHEN l.TAX_AUTH_GEO_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.TAX_AUTH_GEO_ID)), 2)) END) END AS TaxGroupKey, SUM(CONVERT(decimal(36,3), ROUND((COALESCE(l.QUANTITY, 1) * COALESCE(l.AMOUNT, 0)), 3))) AS CalcTaxTotal FROM Lines l WHERE l.IsTax=1 AND (l.TAX_AUTH_PARTY_ID IS NULL OR NOT (DATALENGTH(l.TAX_AUTH_PARTY_ID)=0)) GROUP BY CASE WHEN l.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.INVOICE_ID)), 2)) END, CASE WHEN l.TAX_AUTH_PARTY_ID IS NULL THEN N'Unattributed' ELSE CONCAT(N'T|', CASE WHEN l.TAX_AUTH_PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.TAX_AUTH_PARTY_ID)), 2)) END, N'|G|', CASE WHEN l.TAX_AUTH_GEO_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), l.TAX_AUTH_GEO_ID)), 2)) END) END),
TaxTotal AS (SELECT InvoiceLookupKey, SUM(CONVERT(decimal(37,2), ROUND(CalcTaxTotal, 2))) AS InvoiceTaxTotal FROM TaxGroups GROUP BY InvoiceLookupKey),
ApplicationsRaw AS (SELECT a.PAYMENT_APPLICATION_ID, a.PAYMENT_ID, a.INVOICE_ID, a.INVOICE_ITEM_SEQ_ID, a.BILLING_ACCOUNT_ID, a.OVERRIDE_GL_ACCOUNT_ID, a.TO_PAYMENT_ID, a.TAX_AUTH_GEO_ID, a.AMOUNT_APPLIED, (SELECT COUNT_BIG(*) FROM dbo.PAYMENT_APPLICATION z WHERE CASE WHEN z.PAYMENT_APPLICATION_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.PAYMENT_APPLICATION_ID)), 2)) END=CASE WHEN a.PAYMENT_APPLICATION_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.PAYMENT_APPLICATION_ID)), 2)) END) AS GlobalCopies FROM dbo.PAYMENT_APPLICATION a WHERE EXISTS (SELECT 1 FROM H WHERE H.INVOICE_ID=a.INVOICE_ID AND CASE WHEN H.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.INVOICE_ID)), 2)) END=CASE WHEN a.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.INVOICE_ID)), 2)) END)),
PaymentRaw AS (SELECT p.PAYMENT_ID,p.EFFECTIVE_DATE FROM dbo.PAYMENT p WHERE EXISTS (SELECT 1 FROM ApplicationsRaw a WHERE a.PAYMENT_ID=p.PAYMENT_ID AND CASE WHEN a.PAYMENT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.PAYMENT_ID)), 2)) END=CASE WHEN p.PAYMENT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), p.PAYMENT_ID)), 2)) END)),
Payments AS (SELECT CASE WHEN PAYMENT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), PAYMENT_ID)), 2)) END AS PaymentKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(EFFECTIVE_DATE) ELSE NULL END AS EFFECTIVE_DATE FROM PaymentRaw WHERE PAYMENT_ID IS NOT NULL GROUP BY CASE WHEN PAYMENT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), PAYMENT_ID)), 2)) END),
Applications AS (SELECT a.PAYMENT_APPLICATION_ID, a.PAYMENT_ID, a.INVOICE_ID, a.INVOICE_ITEM_SEQ_ID, a.BILLING_ACCOUNT_ID, a.OVERRIDE_GL_ACCOUNT_ID, a.TO_PAYMENT_ID, a.TAX_AUTH_GEO_ID, a.AMOUNT_APPLIED, a.GlobalCopies, p.Copies AS PaymentCopies,p.EFFECTIVE_DATE, CASE WHEN p.Copies=1 AND (p.EFFECTIVE_DATE IS NULL OR p.EFFECTIVE_DATE<=TRY_CONVERT(datetime2(7), Clock.SourceAsOf, 126)) THEN 1 ELSE 0 END AS Included FROM ApplicationsRaw a CROSS JOIN Clock LEFT JOIN Payments p ON p.PaymentKey=CASE WHEN a.PAYMENT_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.PAYMENT_ID)), 2)) END),
ApplicationState AS (SELECT CASE WHEN INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_ID)), 2)) END AS InvoiceLookupKey, COUNT_BIG(*) AS ApplicationRows, COALESCE(SUM(CASE WHEN PAYMENT_APPLICATION_ID IS NULL OR GlobalCopies<>1 OR PaymentCopies IS NULL OR PaymentCopies<>1 OR (Included=1 AND AMOUNT_APPLIED IS NULL) THEN 1 ELSE 0 END),0) AS InvalidApplications, COALESCE(SUM(CASE WHEN PaymentCopies=1 AND EFFECTIVE_DATE IS NOT NULL AND Included=0 THEN 1 ELSE 0 END),0) AS FutureApplications, SUM(CASE WHEN Included=1 THEN AMOUNT_APPLIED ELSE 0 END) AS AppliedRawTotal FROM Applications GROUP BY CASE WHEN INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_ID)), 2)) END),
Checks AS (SELECT H.INVOICE_ID, H.INVOICE_TYPE_ID, H.PARTY_ID_FROM, H.PARTY_ID, H.ROLE_TYPE_ID, H.STATUS_ID, H.BILLING_ACCOUNT_ID, H.CONTACT_MECH_ID, H.INVOICE_DATE, H.DUE_DATE, H.PAID_DATE, H.INVOICE_MESSAGE, H.REFERENCE_NUMBER, H.DESCRIPTION, H.CURRENCY_UOM_ID, H.RECURRENCE_INFO_ID, H.InvoiceLookupKey, H.GlobalCopies, Clock.SourceAsOf, CONCAT(N'I|', CASE WHEN H.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.INVOICE_ID)), 2)) END, N'|', CASE WHEN H.INVOICE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.INVOICE_TYPE_ID)), 2)) END, N'|', CASE WHEN H.PARTY_ID_FROM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PARTY_ID_FROM)), 2)) END, N'|', CASE WHEN H.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PARTY_ID)), 2)) END, N'|', CASE WHEN H.CURRENCY_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.CURRENCY_UOM_ID)), 2)) END) AS InvoiceKey, CONCAT(CONCAT(N'I|', CASE WHEN H.INVOICE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.INVOICE_ID)), 2)) END, N'|', CASE WHEN H.INVOICE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.INVOICE_TYPE_ID)), 2)) END, N'|', CASE WHEN H.PARTY_ID_FROM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PARTY_ID_FROM)), 2)) END, N'|', CASE WHEN H.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PARTY_ID)), 2)) END, N'|', CASE WHEN H.CURRENCY_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.CURRENCY_UOM_ID)), 2)) END), N'|AS|', CASE WHEN Clock.SourceAsOf IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), Clock.SourceAsOf)), 2)) END) AS InvoiceScopeKey, COALESCE(ls.LineRows,0) AS LineRows, COALESCE(ls.InvalidLines,0) AS InvalidLines, COALESCE(ls.BlankAuthorities,0) AS BlankAuthorities, COALESCE(aps.ApplicationRows,0) AS ApplicationRows, COALESCE(aps.InvalidApplications,0) AS InvalidApplications, COALESCE(aps.FutureApplications,0) AS FutureApplications, ts.InvalidTaxEnums, CASE WHEN H.GlobalCopies=1 AND H.CURRENCY_UOM_ID IS NOT NULL AND COALESCE(ls.InvalidLines,0)=0 AND ts.InvalidTaxEnums=0 AND TRY_CONVERT(decimal(37,2), COALESCE(ls.NonTaxTotal,0)) IS NOT NULL AND TRY_CONVERT(decimal(37,2), COALESCE(tt.InvoiceTaxTotal,0)) IS NOT NULL AND TRY_CONVERT(decimal(37,2), TRY_CONVERT(decimal(37,2), COALESCE(ls.NonTaxTotal,0))+TRY_CONVERT(decimal(37,2), COALESCE(tt.InvoiceTaxTotal,0))) IS NOT NULL THEN 1 ELSE 0 END AS LineReady, CASE WHEN H.GlobalCopies=1 AND H.CURRENCY_UOM_ID IS NOT NULL AND COALESCE(aps.InvalidApplications,0)=0 AND TRY_CONVERT(datetime2(7), Clock.SourceAsOf, 126) IS NOT NULL AND TRY_CONVERT(decimal(37,2), COALESCE(aps.AppliedRawTotal,0)) IS NOT NULL THEN 1 ELSE 0 END AS AppliedReady, COALESCE(ls.NonTaxTotal,0) AS NonTaxTotal, COALESCE(tt.InvoiceTaxTotal,0) AS InvoiceTaxTotal, COALESCE(aps.AppliedRawTotal,0) AS AppliedRawTotal FROM H CROSS JOIN Clock CROSS JOIN TaxState ts LEFT JOIN LineState ls ON ls.InvoiceLookupKey=H.InvoiceLookupKey LEFT JOIN TaxTotal tt ON tt.InvoiceLookupKey=H.InvoiceLookupKey LEFT JOIN ApplicationState aps ON aps.InvoiceLookupKey=H.InvoiceLookupKey WHERE 1=1),
PM 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 LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PARTY_ID) ELSE NULL END AS PARTY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PARTY_TYPE_ID) ELSE NULL END AS PARTY_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(STATUS_ID) ELSE NULL END AS STATUS_ID FROM dbo.PARTY WHERE PARTY_ID IS NOT NULL GROUP BY 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),
ST 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 Copies, 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 Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(UOM_ID) ELSE NULL END AS UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(UOM_TYPE_ID) ELSE NULL END AS UOM_TYPE_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),
IT AS (SELECT CASE WHEN INVOICE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_TYPE_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(INVOICE_TYPE_ID) ELSE NULL END AS INVOICE_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(PARENT_TYPE_ID) ELSE NULL END AS PARENT_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(DESCRIPTION) ELSE NULL END AS DESCRIPTION FROM dbo.INVOICE_TYPE WHERE INVOICE_TYPE_ID IS NOT NULL GROUP BY CASE WHEN INVOICE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), INVOICE_TYPE_ID)), 2)) END),
NameRows 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 LookupKey,GROUP_NAME AS PartyName 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),
PN AS (SELECT LookupKey,COUNT_BIG(*) AS Copies,CASE WHEN COUNT_BIG(*)=1 THEN MAX(PartyName) ELSE NULL END AS PartyName FROM NameRows GROUP BY LookupKey)
SELECT h.InvoiceKey AS InvoiceKey, h.InvoiceScopeKey AS InvoiceScopeKey, h.INVOICE_ID AS SourceInvoiceId, h.SourceAsOf AS SourceAsOf, h.INVOICE_ID AS InvoiceReference, h.CURRENCY_UOM_ID AS CurrencyCode, h.SourceAsOf AS ReadTime, h.INVOICE_DATE AS InvoiceDate, CONVERT(nvarchar(100), h.INVOICE_DATE, 126) AS InvoiceDateText, CONVERT(nvarchar(100), h.DUE_DATE, 126) AS DueDate, CONVERT(nvarchar(100), h.PAID_DATE, 126) AS PaidDate, h.REFERENCE_NUMBER AS PartnerReference, CASE WHEN IT.Copies=1 AND IT.DESCRIPTION IS NOT NULL THEN IT.DESCRIPTION ELSE N'Invoice type unresolved' END AS InvoiceType, CASE WHEN ST.Copies=1 AND ST.DESCRIPTION IS NOT NULL THEN ST.DESCRIPTION ELSE N'Stored invoice status unresolved' END AS Status, CASE WHEN IssuerP.Copies=1 AND IssuerN.Copies=1 AND IssuerN.PartyName IS NOT NULL THEN IssuerN.PartyName ELSE N'Party name unresolved' END AS Issuer, CASE WHEN RecipientP.Copies=1 AND RecipientN.Copies=1 AND RecipientN.PartyName IS NOT NULL THEN RecipientN.PartyName ELSE N'Party name unresolved' END AS Recipient, CASE WHEN IssuerP.Copies=1 AND IssuerN.Copies=1 THEN N'Unique current party and name' ELSE N'Current party/name missing or repeated' END AS IssuerState, CASE WHEN RecipientP.Copies=1 AND RecipientN.Copies=1 THEN N'Unique current party and name' ELSE N'Current party/name missing or repeated' END AS RecipientState, CASE WHEN U.Copies=1 AND U.DESCRIPTION IS NOT NULL THEN U.DESCRIPTION ELSE N'Invoice currency unresolved' END AS Currency, h.DESCRIPTION AS Description, h.INVOICE_MESSAGE AS Message, CASE WHEN h.LineReady=1 THEN CONVERT(nvarchar(100), CONVERT(decimal(38,2), h.NonTaxTotal)) ELSE NULL END AS NonTax, CASE WHEN h.LineReady=1 THEN CONVERT(nvarchar(100), CONVERT(decimal(38,2), h.InvoiceTaxTotal)) ELSE NULL END AS Tax, CASE WHEN h.LineReady=1 THEN CONVERT(nvarchar(100), CONVERT(decimal(38,2), TRY_CONVERT(decimal(37,2), h.NonTaxTotal)+TRY_CONVERT(decimal(37,2), h.InvoiceTaxTotal))) ELSE NULL END AS Total, CASE WHEN h.AppliedReady=1 THEN CONVERT(nvarchar(100), CONVERT(decimal(38,2), h.AppliedRawTotal)) ELSE NULL END AS Applied, CASE WHEN h.LineReady=1 AND h.AppliedReady=1 THEN CONVERT(nvarchar(100), CONVERT(decimal(38,2), TRY_CONVERT(decimal(37,2), TRY_CONVERT(decimal(37,2), h.NonTaxTotal)+TRY_CONVERT(decimal(37,2), h.InvoiceTaxTotal))-TRY_CONVERT(decimal(37,2), h.AppliedRawTotal))) ELSE NULL END AS Unapplied, CONVERT(nvarchar(100), h.GlobalCopies) AS GlobalCopies, CONVERT(nvarchar(100), h.LineRows) AS LineRows, CONVERT(nvarchar(100), h.InvalidLines) AS InvalidLines, CONVERT(nvarchar(100), h.BlankAuthorities) AS BlankAuthorities, CONVERT(nvarchar(100), h.InvalidTaxEnums) AS InvalidTaxEnums, CONVERT(nvarchar(100), h.ApplicationRows) AS ApplicationRows, CONVERT(nvarchar(100), h.InvalidApplications) AS InvalidApplications, CONVERT(nvarchar(100), h.FutureApplications) AS FutureApplications, N'Pinned InvoiceWorker defaults: amount 2; tax calculation 3; HALF_UP. Verify installed settings.' AS Profile, CASE WHEN h.LineReady=1 AND h.AppliedReady=1 THEN N'Default-profile totals available — not a certified ledger balance' ELSE N'Incomplete or repeated source identity/currency/classification; unavailable values are not zero' END AS CalculationState FROM Checks h LEFT JOIN IT ON IT.LookupKey=CASE WHEN h.INVOICE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.INVOICE_TYPE_ID)), 2)) END LEFT JOIN 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 ON U.LookupKey=CASE WHEN h.CURRENCY_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.CURRENCY_UOM_ID)), 2)) END LEFT JOIN PM IssuerP ON IssuerP.LookupKey=CASE WHEN h.PARTY_ID_FROM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PARTY_ID_FROM)), 2)) END LEFT JOIN PN IssuerN ON IssuerN.LookupKey=CASE WHEN h.PARTY_ID_FROM IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PARTY_ID_FROM)), 2)) END LEFT JOIN PM RecipientP ON RecipientP.LookupKey=CASE WHEN h.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PARTY_ID)), 2)) END LEFT JOIN PN RecipientN ON RecipientN.LookupKey=CASE WHEN h.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PARTY_ID)), 2)) END