Customer dossier, contacts, invoices and orders — Pro

Useful party profiles; roles, contact/address history, purposes, directed relationships, invoice/order dossiers and lines.

UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.

For sales and customer management, finance and managers: open a useful party dossier rather than a list of names. Inspect roles, contact history, purposes, directed relationships, relevant profile values, invoices, orders and their lines through separate on-demand buttons.

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
Eleven live lists and ten related buttons: party to Roles, Contact history, Relationships, Invoices, Orders and Profile values; contact to Purposes and Contact values; invoice/order to its Lines. The opening period controls related invoice/order dates only, with both boundaries inherited [start, exclusive end). It does not filter parties or provide historical contact/profile snapshots. Reopen the root to change the period. A party without documents in that period can still appear.

USEFUL, TYPE-AWARE DATA
Common party fields retain the full name, external reference, stored status, type, description and preferred currency. Profile values show business-only or person-only attributes, not irrelevant fields for the other type. Business attributes include local name, office, employee count, ticker and comments. Person attributes include local/full name components, occupation, salutation, title and comments. Contact history retains each dated link, extension, verification/solicitation codes, role and comments. Contact values show only the selected email, website, telephone or postal profile: pertinent fields can still be NULL or empty. Derived profile checks and unresolved geography lookups are labelled, not represented as stored business values. Exact stored NULL, empty text and zero remain distinct.

ROLES, CONTACTS AND RELATIONSHIPS
Root membership uses these explicit default roles: CUSTOMER, BILL_TO_CUSTOMER, BULK_CUSTOMER, END_USER_CUSTOMER, PLACING_CUSTOMER and SHIP_TO_CUSTOMER. Custom descendants are not assumed. Orders use explicit matching party-role rows; several matching roles do not multiply headers. Contacts include only POSTAL_ADDRESS, TELECOM_NUMBER, EMAIL_ADDRESS and WEB_ADDRESS. INFO_STRING is used only for email/web; no automatic URL, email or dial action. All retained contact intervals and independently dated purposes are inspectable. Purpose history is linked by party/contact, not by equal start dates: this does not prove a purpose was active during the selected contact interval. There is no arbitrary preferred/current-contact choice. Relationships retain both directions and roles; the relationship type name describes the TO party.

DOCUMENTS AND AMOUNTS
Only exact sales invoices/orders for customers and purchase invoices/orders for suppliers. Document headers, current issuer/recipient/product/account descriptions, dates, references, source status and currency are retained. Source invoice AMOUNT and order UNIT_PRICE are unit amounts, not line totals. Quantity, cancellation, list/recurring price and frequency stay separate. Stored order grand total and remaining subtotal are not our ERP recalculation, unpaid balance or unshipped value. No invoice total, payment balance, ageing, margin, tax reconstruction or currency conversion is invented. Numeric values are exact source text without rounding; numeric filtering is not promised. The party preferred currency does not convert document values.

PRO PLAN AND PERFORMANCE
One authorised SQL Server source, one Home and eleven live read-only lists. Each result is capped at 2,000 rows; Search/local filters inspect loaded rows only. Related data loads only when opened. No writes, periodic refresh or widgets. Existing workspace resources count toward plan limits. A row cap does not guarantee a cheap query: an administrator must check indexes, execution plans and timeouts on the installed source. Separate reads are not a consistent transaction snapshot.

BEFORE USING AND CONFIDENTIALITY
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. Verify installed physical names, types, full keys, extensions, timestamp precision/timezone and compatibility. Null/repeated identities are excluded; ambiguous masters remain unavailable, never arbitrarily chosen. Current names/profiles/products are not historical snapshots. Reload parents if source context changes. Use validated TLS and least-privilege SELECT with source-enforced company/tenant/role restrictions. Direct SQL does not inherit OFBiz application permissions; local filters and relations are not an ACL. Technical relationship keys stay hidden. No user logins, bank/payment instruments, medical, encrypted identity, generic PartyAttribute or internal-note payloads are selected. Real names, comments, phone/email and addresses are still personal/confidential data: authorise access appropriately. No connection credentials or production rows are included; every gallery example is wholly fictional DEMO. Map your own authorised source and run Cifru read/compatibility tests before business use.

VALIDATION AND GALLERY
Pinned official model and documentary/synthetic tests plus native Android demonstrations: not a real ERP/SQL Server/iOS execution, security, performance or purchase certification. Private native traversal checks all eleven routes, ten opened actions and 113 useful Detail positions for one selected row per list, including interpretation fields; not 113 distinct business fields. The eight representative photos show a complete business root card, seven routes and all ten button labels, not all eleven routes or all 113 positions. Alternative person/postal/phone/web cases have synthetic tests, not complete native visual certification. TEST absence can display literal null; production SQL NULL handling is not certified. Original PNG/XML/input and first approximately 180 seconds of RAW remain private for future videos; RAW is not the whole later navigation.

PRIMARY DOCUMENTATION
https://github.com/apache/ofbiz-framework/blob/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/datamodel/entitydef/party-entitymodel.xml
https://github.com/apache/ofbiz-framework/blob/42819e5ae1d5339d3a204ac06b43e69d46a9c0ae/applications/party/data/PartySeedData.xml

Screenshots

What this package creates

1 Home10 Details11 lists1 sources to map

Home: Customers
Details: Party roles
Details: Contact history
Details: Contact purposes
Details: Relationships
Details: Invoices
Details: Invoice lines
Details: Orders
Details: Order lines
Details: Profile values
Details: Contact values
Sub-button: Party roles
Sub-button: Contact history
Sub-button: Contact purposes
Sub-button: Relationships
Sub-button: Invoices
Sub-button: Invoice lines
Sub-button: Orders
Sub-button: Order lines
Sub-button: Profile values
Sub-button: Contact values

Sources are mapped locally and verified before applying.

Custom queriesPRO11 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 P0 AS (SELECT r.PARTY_ID, r.PARTY_TYPE_ID, r.EXTERNAL_ID, r.PREFERRED_CURRENCY_UOM_ID, r.DESCRIPTION, r.STATUS_ID, (SELECT COUNT_BIG(*) FROM dbo.PARTY z WHERE z.PARTY_ID=r.PARTY_ID AND CASE WHEN z.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), z.PARTY_ID)), 2)) END=CASE WHEN r.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), r.PARTY_ID)), 2)) END) AS IdentityCopies FROM dbo.PARTY r WHERE CAST(:date_from AS date)<CAST(:date_until AS date) AND EXISTS (SELECT 1 FROM dbo.PARTY_ROLE a WHERE a.PARTY_ID=r.PARTY_ID AND CASE WHEN a.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.PARTY_ID)), 2)) END=CASE WHEN r.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), r.PARTY_ID)), 2)) END AND (a.ROLE_TYPE_ID=N'CUSTOMER' AND CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END=CASE WHEN N'CUSTOMER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'CUSTOMER')), 2)) END OR a.ROLE_TYPE_ID=N'BILL_TO_CUSTOMER' AND CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END=CASE WHEN N'BILL_TO_CUSTOMER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'BILL_TO_CUSTOMER')), 2)) END OR a.ROLE_TYPE_ID=N'BULK_CUSTOMER' AND CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END=CASE WHEN N'BULK_CUSTOMER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'BULK_CUSTOMER')), 2)) END OR a.ROLE_TYPE_ID=N'END_USER_CUSTOMER' AND CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END=CASE WHEN N'END_USER_CUSTOMER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'END_USER_CUSTOMER')), 2)) END OR a.ROLE_TYPE_ID=N'PLACING_CUSTOMER' AND CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END=CASE WHEN N'PLACING_CUSTOMER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'PLACING_CUSTOMER')), 2)) END OR a.ROLE_TYPE_ID=N'SHIP_TO_CUSTOMER' AND CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END=CASE WHEN N'SHIP_TO_CUSTOMER' IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), N'SHIP_TO_CUSTOMER')), 2)) END) AND (SELECT COUNT_BIG(*) FROM dbo.PARTY_ROLE b WHERE b.PARTY_ID=a.PARTY_ID AND CASE WHEN b.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), b.PARTY_ID)), 2)) END=CASE WHEN a.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.PARTY_ID)), 2)) END AND b.ROLE_TYPE_ID=a.ROLE_TYPE_ID AND CASE WHEN b.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), b.ROLE_TYPE_ID)), 2)) END=CASE WHEN a.ROLE_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), a.ROLE_TYPE_ID)), 2)) END)=1)),
P AS (SELECT * FROM P0 WHERE IdentityCopies=1 AND PARTY_ID IS NOT NULL),
H AS (SELECT p.*, CONCAT(N'PARTY|', CASE WHEN p.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), p.PARTY_ID)), 2)) END, N'|') AS PartyKey, CONCAT(CONCAT(N'PARTY|', CASE WHEN p.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), p.PARTY_ID)), 2)) END, N'|'), N'C|', CASE WHEN p.PREFERRED_CURRENCY_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), p.PREFERRED_CURRENCY_UOM_ID)), 2)) END, N'|PERIOD|', 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 PartyScopeKey, CONVERT(nvarchar(10), CAST(:date_from AS date), 23) AS ScopeStart, CONVERT(nvarchar(10), CAST(:date_until AS date), 23) AS ScopeEnd FROM P p),
PM AS (SELECT CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PARTY_ID) ELSE NULL END AS PARTY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PARTY_TYPE_ID) ELSE NULL END AS PARTY_TYPE_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.EXTERNAL_ID) ELSE NULL END AS EXTERNAL_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PREFERRED_CURRENCY_UOM_ID) ELSE NULL END AS PREFERRED_CURRENCY_UOM_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.DESCRIPTION) ELSE NULL END AS DESCRIPTION, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.STATUS_ID) ELSE NULL END AS STATUS_ID FROM dbo.PARTY m WHERE m.PARTY_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.PARTY_ID=m.PARTY_ID AND 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=CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END)) GROUP BY CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END),
PG AS (SELECT CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PARTY_ID) ELSE NULL END AS PARTY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.GROUP_NAME) ELSE NULL END AS GROUP_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.GROUP_NAME_LOCAL) ELSE NULL END AS GROUP_NAME_LOCAL, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.OFFICE_SITE_NAME) ELSE NULL END AS OFFICE_SITE_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(CONVERT(nvarchar(100), m.NUM_EMPLOYEES)) ELSE NULL END AS NUM_EMPLOYEES, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.TICKER_SYMBOL) ELSE NULL END AS TICKER_SYMBOL, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.COMMENTS) ELSE NULL END AS COMMENTS FROM dbo.PARTY_GROUP m WHERE m.PARTY_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.PARTY_ID=m.PARTY_ID AND 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=CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END)) GROUP BY CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END),
PN AS (SELECT CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PARTY_ID) ELSE NULL END AS PARTY_ID, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.SALUTATION) ELSE NULL END AS SALUTATION, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.FIRST_NAME) ELSE NULL END AS FIRST_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.MIDDLE_NAME) ELSE NULL END AS MIDDLE_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.LAST_NAME) ELSE NULL END AS LAST_NAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PERSONAL_TITLE) ELSE NULL END AS PERSONAL_TITLE, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.SUFFIX) ELSE NULL END AS SUFFIX, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.NICKNAME) ELSE NULL END AS NICKNAME, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.FIRST_NAME_LOCAL) ELSE NULL END AS FIRST_NAME_LOCAL, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.MIDDLE_NAME_LOCAL) ELSE NULL END AS MIDDLE_NAME_LOCAL, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.LAST_NAME_LOCAL) ELSE NULL END AS LAST_NAME_LOCAL, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.OCCUPATION) ELSE NULL END AS OCCUPATION, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.COMMENTS) ELSE NULL END AS COMMENTS FROM dbo.PERSON m WHERE m.PARTY_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.PARTY_ID=m.PARTY_ID AND 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=CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_ID)), 2)) END)) GROUP BY CASE WHEN m.PARTY_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_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),
PT AS (SELECT CASE WHEN m.PARTY_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_TYPE_ID)), 2)) END AS LookupKey, COUNT_BIG(*) AS Copies, CASE WHEN COUNT_BIG(*)=1 THEN MAX(m.PARTY_TYPE_ID) ELSE NULL END AS PARTY_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.PARTY_TYPE m WHERE m.PARTY_TYPE_ID IS NOT NULL AND (EXISTS (SELECT 1 FROM H WHERE H.PARTY_TYPE_ID=m.PARTY_TYPE_ID AND CASE WHEN H.PARTY_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PARTY_TYPE_ID)), 2)) END=CASE WHEN m.PARTY_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_TYPE_ID)), 2)) END)) GROUP BY CASE WHEN m.PARTY_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), m.PARTY_TYPE_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.PREFERRED_CURRENCY_UOM_ID=m.UOM_ID AND CASE WHEN H.PREFERRED_CURRENCY_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), H.PREFERRED_CURRENCY_UOM_ID)), 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)
SELECT h.PartyKey AS PartyKey, h.PartyScopeKey AS PartyScopeKey, h.PARTY_ID AS SourcePartyId, h.ScopeStart AS ScopeStart, h.ScopeEnd AS ScopeEnd, h.PartyKey AS EntityKey, h.PartyScopeKey AS ChildScopeKey, CONVERT(nvarchar(10), CAST(:date_from AS date), 23) AS DocumentPeriodDate, CASE WHEN ownp.Copies=1 AND owng.Copies=1 AND ownn.Copies IS NULL THEN CASE WHEN NULLIF(LTRIM(RTRIM(owng.GROUP_NAME)), N'') IS NULL THEN N'Current business name not supplied' ELSE owng.GROUP_NAME END WHEN ownp.Copies=1 AND ownn.Copies=1 AND owng.Copies IS NULL THEN CASE WHEN NULLIF(LTRIM(RTRIM(LTRIM(RTRIM(CONCAT(ownn.FIRST_NAME, N' ', ownn.MIDDLE_NAME, N' ', ownn.LAST_NAME))))), N'') IS NULL THEN N'Current person name not supplied' ELSE LTRIM(RTRIM(CONCAT(ownn.FIRST_NAME, N' ', ownn.MIDDLE_NAME, N' ', ownn.LAST_NAME))) END ELSE N'Current party/name unresolved' END AS PartyName, h.EXTERNAL_ID AS ExternalReference, h.DESCRIPTION AS Description, h.PREFERRED_CURRENCY_UOM_ID AS PreferredCurrencyCode, CASE WHEN st.Copies=1 AND st.DESCRIPTION IS NOT NULL THEN st.DESCRIPTION ELSE N'Current Status unresolved' END AS Status, CASE WHEN pt.Copies=1 AND pt.DESCRIPTION IS NOT NULL THEN pt.DESCRIPTION ELSE N'Current PartyType unresolved' END AS PartyType, CASE WHEN u.Copies=1 AND u.DESCRIPTION IS NOT NULL THEN u.DESCRIPTION ELSE N'Current PreferredCurrency unresolved' END AS PreferredCurrency, CASE WHEN ownp.Copies=1 AND ((owng.Copies=1 AND ownn.Copies IS NULL) OR (ownn.Copies=1 AND owng.Copies IS NULL)) THEN N'One current person or group profile — not an immutable historical name' ELSE N'Missing/repeated/mixed profiles — no arbitrary name chosen' END AS NameCheck, N'Party membership is explicit CUSTOMER-family or SUPPLIER/VENDOR-family roles, not a balance or activity score. The selected period applies only to related invoice/order dates; contacts and relationships show all retained history.' AS Meaning FROM H h LEFT JOIN PM ownp ON ownp.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 PG owng ON owng.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 ownn ON ownn.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 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 PT pt ON pt.LookupKey=CASE WHEN h.PARTY_TYPE_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PARTY_TYPE_ID)), 2)) END LEFT JOIN U u ON u.LookupKey=CASE WHEN h.PREFERRED_CURRENCY_UOM_ID IS NULL THEN N'N' ELSE CONCAT(N'H', CONVERT(varchar(max), CONVERT(varbinary(max), CONVERT(nvarchar(max), h.PREFERRED_CURRENCY_UOM_ID)), 2)) END