Unclaimed Property Report (United States)
US unclaimed property (escheatment) — outstanding checks, credit balances, and unapplied receipts that have gone dormant past the state dormancy period, by state and owner, for the annual unclaimed-property filing.
Related Run with a snapshot date for the point-in-time view (formerly a separate Snapshot report).
Run note · Data latency Several tax reports read the Tax Reporting Ledger extract rather than live transaction tables — run the extract first or the report returns stale or empty results.
Sample build of the Unclaimed Property Report (United States) — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Owner | State | Property Type | Amount | Last Activity | Dormancy |
|---|---|---|---|---|---|
| Sample | Sample | Standard | $1,240,500.00 | Sample | Sample |
| — | — | Corporate | $842,150.75 | — | — |
| Sample | Sample | Standard | $96,400.00 | Sample | Sample |
| — | — | Default | $1,005,233.10 | — | — |
| Sample | Sample | Standard | $58,720.40 | Sample | Sample |
| Sample | Sample | Standard | $1,240,500.00 | Sample | Sample |
The report identifies dormant outstanding checks and credit balances past each state's dormancy period.
68 items are past dormancy and reportable this cycle — missing the state filing deadline triggers penalties and interest.
File, or perform owner due-diligence outreach, before each state's deadline; unclaimed property is a quiet but penalized compliance area.
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
Show / hide SQL
WITH SECURITY_TBL AS ( SELECT DISTINCT HOU.organization_id FROM hr_operating_units HOU, gl_ledgers GL, fun_user_role_data_asgnmnts FURDA, per_roles_dn PRD, per_user_roles PUR, per_users PU WHERE 1 =1 AND FURDA.org_id =HOU.organization_id AND HOU.set_of_books_id =GL.ledger_id AND UPPER(FURDA.active_flag) ='Y' AND UPPER(FURDA.role_name) =UPPER(PRD.role_common_name) AND PRD.role_id =PUR.role_id AND PUR.user_id =PU.user_id AND upper(pu.username) =upper(:xdo_user_name) AND pu.user_guid =FURDA.user_guid ) SELECT HOU.name BU_NAME , AC.bank_account_name , FLV.meaning PMT_MTD , AC.currency_code , DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.state,HL.state) STATE , TO_CHAR(AC.check_date,'DD-Mon-YY','nls_date_language=American') CHECK_DATE , AC.check_number , AC.status_lookup_code , AC.amount , POZ.vendor_type_lookup_code , NVL(AC.vendor_name,POZ.vendor_name) vendor_name --, HL.address1||','||HL.city||','||HL.state||','||HL.postal_code ADDRESS , DECODE(NVL(AC.remit_to_supplier_name,'-1'),'-1',PSS.address_line1,HL.address1)|| CASE WHEN DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.address_line2,HL.address2) IS NOT NULL THEN ',' END|| DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.address_line2,HL.address2)|| CASE WHEN DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.city,HL.city) IS NOT NULL THEN ',' END|| DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.city,HL.city)|| CASE WHEN DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.state,HL.state) IS NOT NULL THEN ',' END|| DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.state,HL.state)|| CASE WHEN DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.zip,HL.postal_code) IS NOT NULL THEN ',' END|| DECODE(NVL(AC.remit_to_supplier_name,'-999999'),'-999999',PSS.zip,HL.postal_code) ADDRESS FROM ap_checks_all AC JOIN hr_operating_units HOU ON AC.org_id=HOU.organization_id LEFT OUTER JOIN poz_suppliers_v POZ ON AC.vendor_id=POZ.vendor_id LEFT OUTER JOIN poz_supplier_sites_v PSS ON AC.vendor_site_id=PSS.vendor_site_id LEFT OUTER JOIN hz_party_sites HPS ON AC.remit_to_address_id =HPS.party_site_id JOIN SECURITY_TBL ST ON HOU.organization_id=ST.organization_id LEFT OUTER JOIN hz_locations HL ON HPS.location_id=HL.location_id LEFT OUTER JOIN fnd_lookup_values_tl FLV ON AC.payment_method_code=FLV.lookup_code AND FLV.LOOKUP_TYPE='PAYMENT METHOD' AND FLV.LANGUAGE ='US' WHERE 1=1 AND AC.status_lookup_code='ORA_ESCHEATED' AND HOU.name =:P_BU AND :P_RPT_LEVEL ='Set of Books' AND AC.bank_account_name=:P_BANK_ACC AND (FLV.meaning IN (:P_PMT_METHOD) OR 'ALL' IN (:P_PMT_METHOD||'ALL')) AND (POZ.vendor_type_lookup_code IN (:P_SUP_TYPE) OR 'ALL' IN (:P_SUP_TYPE||'ALL')) AND (HL.state IN (:P_STATE) OR 'ALL' IN (:P_STATE||'ALL')) AND TRUNC(AC.check_date) >=NVL(:P_FROM_DATE,TRUNC(AC.check_date)) AND TRUNC(AC.check_date) <=:P_TO_DATE ORDER BY POZ.vendor_name , AC.check_date
The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
| Element | Type | Definition |
|---|---|---|
| AR_CASH_RECEIPTS_ALL | dimension | dimension |
| AP_INVOICES_ALL | dimension | dimension |
| HZ_PARTIES | dimension | dimension |
| GL_LEDGERS | dimension | dimension |
| Amount | measure | measure |
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| AP_CHECKS_ALL | 44 | 6 |
| AR_CASH_RECEIPTS_ALL | 25 | 9 |
| AP_INVOICES_ALL | 63 | 15 |
| HZ_PARTIES | 81 | 144 |
| GL_LEDGERS | 10 | 104 |