Oracle Retail Knowledge Base

Validate AIF Apps historical facts

3 min read

Context

Read-only checks to confirm that historical facts pushed from RI to AIF Apps landed correctly. Run each section right after the corresponding push in Push data to AIF Apps — do not push the next interface before the current one is clean. None of these queries modify data.

Two checks per interface:

  • count — every table populated by the push has rows.
  • range — earliest and latest date on record match the --from/--to used in the push. CAL_DY_ID / CAL_HIER_ID on the fact tables join to RSE_CAL_HIER.ID; the actual date sits on RSE_CAL_HIER.CAL_FROM_DT (a DATE column), so TO_CHAR(..., 'YYYYMMDD') to format. RSE_INV_PR_LC_HIST is the exception: it carries its own FIRST_RECEIPT_DATE column, so no join.

Quick access

  • Sales — RSE_SLS_TXN, RSE_SLS_PR_LC_CS_WK.
  • Inventory — RSE_INV_PR_LC_DY, RSE_INV_PR_LC_WK_A.
  • Receipts — RSE_INV_PR_LC_HIST.
  • Returns — RSE_RETURN_TXN, RSE_RET_PR_LC_CS_WK.
  • Price — RSE_PRICOST_PR_LC_WK.
  • Additional data groups — validation for -V and -PJ (pending confirmation).

Sales

Run after RSE_MASTER_ADHOC_PROCESS -xwa.

Counts:

SELECT COUNT(*) FROM RSE_SLS_TXN;
SELECT COUNT(*) FROM RSE_SLS_PR_LC_CS_WK;

Range on RSE_SLS_TXN.CAL_DY_ID, joined to RSE_CAL_HIER:

SELECT
	TO_CHAR(MIN(cal.CAL_FROM_DT), 'YYYYMMDD') AS min_dt,
	TO_CHAR(MAX(cal.CAL_FROM_DT), 'YYYYMMDD') AS max_dt
FROM RSE_SLS_TXN txn
JOIN RSE_CAL_HIER cal ON txn.CAL_DY_ID = cal.ID;

Confirm min_dt/max_dt match the --from/--to used in the Sales push. A narrower range than expected usually means the push has not finished, was scoped too tightly, or hit a date validation error.

Inventory

Run after RSE_MASTER_ADHOC_PROCESS -i.

Counts:

SELECT COUNT(*) FROM RSE_INV_PR_LC_DY;
SELECT COUNT(*) FROM RSE_INV_PR_LC_WK_A;

Range on RSE_INV_PR_LC_DY.CAL_HIER_ID, joined to RSE_CAL_HIER:

SELECT
	TO_CHAR(MIN(cal.CAL_FROM_DT), 'YYYYMMDD') AS min_dt,
	TO_CHAR(MAX(cal.CAL_FROM_DT), 'YYYYMMDD') AS max_dt
FROM RSE_INV_PR_LC_DY inv
JOIN RSE_CAL_HIER cal ON inv.CAL_HIER_ID = cal.ID;

Receipts

RSE_INV_PR_LC_HIST is populated together with the other RSE_INV_* tables during the Inventory push — validate it right after Inventory.

Count:

SELECT COUNT(*) FROM RSE_INV_PR_LC_HIST;

Range on FIRST_RECEIPT_DATE (a DATE column — no join needed):

SELECT
	TO_CHAR(MIN(FIRST_RECEIPT_DATE), 'YYYYMMDD') AS min_dt,
	TO_CHAR(MAX(FIRST_RECEIPT_DATE), 'YYYYMMDD') AS max_dt
FROM RSE_INV_PR_LC_HIST;

Returns

Run after RSE_MASTER_ADHOC_PROCESS -Te.

Counts:

SELECT COUNT(*) FROM RSE_RETURN_TXN;
SELECT COUNT(*) FROM RSE_RET_PR_LC_CS_WK;

Range on RSE_RETURN_TXN.CAL_DY_ID, joined to RSE_CAL_HIER:

SELECT
	TO_CHAR(MIN(cal.CAL_FROM_DT), 'YYYYMMDD') AS min_dt,
	TO_CHAR(MAX(cal.CAL_FROM_DT), 'YYYYMMDD') AS max_dt
FROM RSE_RETURN_TXN ret
JOIN RSE_CAL_HIER cal ON ret.CAL_DY_ID = cal.ID;

Price

Run after RSE_MASTER_ADHOC_PROCESS -C.

Count:

SELECT COUNT(*) FROM RSE_PRICOST_PR_LC_WK;

Range on RSE_PRICOST_PR_LC_WK.CAL_HIER_ID, joined to RSE_CAL_HIER:

SELECT
	TO_CHAR(MIN(cal.CAL_FROM_DT), 'YYYYMMDD') AS min_dt,
	TO_CHAR(MAX(cal.CAL_FROM_DT), 'YYYYMMDD') AS max_dt
FROM RSE_PRICOST_PR_LC_WK p
JOIN RSE_CAL_HIER cal ON p.CAL_HIER_ID = cal.ID;

Additional data groups

Run after RSE_MASTER_ADHOC_PROCESS -V and RSE_MASTER_ADHOC_PROCESS -PJ.

No dedicated validation for -V and -PJ yet

The target tables and expected row counts for the Forecast Lifecycle Classification (-V) and Product Attributes / LPO Rules Engine (-PJ) loads are not documented in this vault yet. Use Discover related tables to identify candidate tables, and add validated queries here once confirmed.

To find every table belonging to a given RSE family — useful for the -V/-PJ groups above, where the exact target table is not yet confirmed:

SELECT * FROM RSE_DATA_MODEL_SUPPORT_UTIL.GET_TABLE_LIST('RSE_<INTERFACE_PREFIX>%');

Reference

Graph View

Backlinks