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.role_name) IN ('XXC_ACCOUNTS_PAYABLE_ANALYST_JOB', 'XXC_ACCOUNTS_PAYABLES_MANAGER_JOB')
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_dateThe 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 |