Oracle Retail Knowledge Base

6 Data Validation Framework

16 min read

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

6

Data Validation Framework

The foundation file interfaces (such as product and organization hierarchies) have a set of validations and error checking jobs that execute with them to ensure the data is accurate, complete, and follows all basic requirements for RAP application usage. Review the contents of this chapter to understand what validations exist and how to reconfigure them per your implementation needs.

Architecture Overview

The validation framework consists of POM batch jobs that execute the validations, and database tables that control the types of validation rules and what happens when the rule is triggered. Some validation rules may cause the POM job to fail, which means the data has a critical issue that needs to be corrected before the batch process can continue. Other rules will simply write warnings to the database but allow the batch to proceed. In both cases, there are tables that can be queried to check the validation results and determine what actions need to be taken.

The table below summarizes the POM jobs that execute the validations:

Table 6-1 Data Validation POM Jobs

Job NameSummary
DIM_ORG_VALIDATOR_JOBExecutes validations on the Organization Hierarchy
data
DIM_PROD_VALIDATOR_JOBExecutes validations on the Product Hierarchy and
Product dimension data
DIM_CALENDAR_VALIDATOR_JOBExecutes validations on the Calendar Hierarchy staging
data
DIM_CALENDAR_LOAD_VALIDATOR_JO
B		Executes validations on the Calendar Period data after
the load has been run
DIM_EXCH_RATE_VALIDATOR_JOBExecutes validations on the Exchange Rate staging data
DIM_PROD_ATTR_VALIDATOR_JOBExecutes validations on the Product Attribute staging
data
FACT_POSFACT_VALIDATOR_JOBExecutes validations on the positional fact staging data
(inventory, POs, and so on). Refer to the POS DATA set
of validation rules, such asPOSINVATA_R1later in this
section, for details on what this job is looking for.

The jobs are included both in the nightly batch process flow and in separate ad hoc processes that can be executed as part of your historical data loads.

The configuration tables for the validation rules are called C_DIM_RULE_LIST and C_FACT_RULE_LIST . You can access these tables from the Control & Tactical Center’s Manage System Configurations screen. The tables allows you to edit the following fields:

  • Set the error message resulting from a validation rule ( ERROR_DESC )

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
CAL_R3TheSTART_DTparameter set on
C_ODI_PARAM_VWis not less than or
equal to your frst calendar period
start date. This may result in missing
calendar data.		Update theSTART_DTparameter from
the Control Center to be earlier than
your fscal calendar start date. Detailed
explanation of the start and end dates is
provided in Chapter 2 of the_RAP_
Implementation Guide.
CAL_R4Your calendar fle does not contain at
least 2 years prior to the current
system date. Many applications on
the platform require at least 2 years
before and after the current calendar
year (5 years total).		Load a new calendar fle having at least
2 years of fscal periods prior to the
current year.
CUSTHIER_R1Too many custom hierarchies have
been specifed as base intersections
using the BASE_INTERSECTION_FLG.		Check that
W_RTL_CUSTOM_HIER_TYPE_D.BASE_INT
ERSECTION_FLGis set toYfor at most 2
rows, and set all other rows toN.
CUSTHIER_R2Custom hierarchy type and level
defnitions do not have the same
HIER_NAME values.		Compare the values ofHIER_NAME
betweenW_RTL_CUSTOM_HIER_TYPE_D
andW_RTL_CUSTOM_HIER_LEVEL_Dand
ensure they contain the exact same
values across both tables.
CUSTHIER_R3Custom hierarchy is missing a top
level defnition.		Ensure that every hierarchy defned in
W_RTL_CUSTOM_HIER_LEVEL_Dhas
exactly 1 entry forHIER_LEVEL_ID=1.
CUSTHIER_R4Custom hierarchy has too few levels
defned.		Check inW_RTL_CUSTOM_HIER_LEVEL_D
that for every value ofHIER_NAMEthere
is >=2 rows of data. Add additional
levels as needed.
CUSTHIER_R5Custom hierarchy levels do not have
a proper sequence of level IDs
starting from 1 and increasing from
there.		Check inW_RTL_CUSTOM_HIER_LEVEL_D
that for every value ofHIER_NAME, the
MAX(HIER_LEVEL_ID)is equal to
COUNT(HIER_LEVEL_ID).For example, if
level IDs range from 1 to 5, then the
maximum value is 5 and the row count
should also be 5.
CUSTHIER_R6Custom hierarchy levels are missing
a column mapping.		Check inW_RTL_CUSTOM_HIER_LEVEL_D
thatID_SRCandDESC_SRCcolumns are
non-null on all rows.
CUSTHIER_R7Custom hierarchy levels contain an
invalid column mapping.		For each level mapping defned in
W_RTL_CUSTOM_HIER_LEVEL_D, ensure
the column names used are valid
columns in theW_RTL_CUSTOM_HIER_D
table.
CUSTHIER_R8Custom hierarchy data contains null
values in a column that should be a
part of the hierarchy structure.		Check that for every column mapped in
W_RTL_CUSTOM_HIER_LEVEL_D, the
named column in
W_RTL_CUSTOM_HIER_Dhas non-null
data for all rows of the associated
HIER_NAME.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
PROD_STG_R1Null descriptions were detected for
attributes having a non-null ID
column for one of the following:
BRAND_DESC,SUPPLIER_DESC		Populate valid descriptions for all rows
in the product data whereBRAND_NAME
orSUPPLIER_NUMare populated, and
then reload the fle.
PROD_R1Many-to-many relationships exist in
your product hierarchy, which is not
allowed. This is generally due to the
same child ID appearing below
multiple parent IDs.		Review all hierarchy levels for instances
of the same ID appearing under
multiple parents (such as a department
belonging to two different divisions or
groups) and modify the data to remove
the multi-parent issues.
PROD_R2The same product hierarchy node
has multiple descriptions on different
rows of the input fle.		Modify your product hierarchy fle such
that any given hierarchy ID has the
same description on all rows.
PROD_R3A node of the product hierarchy has
no children under it. This could be
due to a reclass that didn’t delete the
old nodes, or when a new node is
added but no items were created yet.
Initially, it is set as a Failure (F) rule,
but you may change it to a warning
(W) inC_DIM_RULE_LISTif you are
okay with the data in its current
format.		If possible, remove all cases of nodes
having no children (for example, if all
items are reclassed out of a subclass,
delete the old subclass). Some AI
Foundation functionality may fail if you
attempt to run it on empty nodes; for
example, if a user attempts to run CDT
on an empty subclass, it cannot
complete the run because there will be
no data found. MFCS may allow
hierarchies to exist without items, but it
can lead to user confusion in AIF, so it’s
best to clean up this data regularly.
PROD_R4Your product hierarchy levels use
alphanumeric characters for the level
IDs. This is not allowed if you are
implementing Retail Insights; all
levels must be numbers.		If you are implementing RI, you must
alter your hierarchy to only use
numbers for every level above item.
Other characters are not allowed.
PROD_R5You are attempting to delete an item
while also sending data for that item
in other fles on the same batch run.
You cannot delete an item if it is still
actively sending data on other input
interfaces.		Re-send the deleted item fle, removing
any items that are still active or posting
new data to RI. If the item should be
deleted, then re-send the other fles
having that item’s data to remove the
item from all other fles.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
PROD_R6This is an informational message and
not a hard failure. Null values are
being checked for product attribute
columns that are critical to the
operation of multiple RAP
applications. The warning message
columns map to the item level
(ATTR11), tran level (ATTR12), diff
aggregate (ATTR16), and item/parent/
grandparent (ATTR13,14,15) felds in
thePRODUCT.csvfle. (which loads
into theW_PRODUCT_ATTR_DSstaging
table)		Null values in theATTR11andATTR12
counts means that theITEM_LEVELand
TRAN_LEVELinput felds are not
populated, and these must be populated
for 100% of items at all levels.
ATTR13/14/15represent the item
hierarchy of multi-level items and some
null values are normal. The only check
to perform is whether 100% of rows
have nulls; if there are, then no parent/
child relationship was set up for any
items.ATTR16matches with the
DIFF_AGGREGATEinput feld (orDIFF
columns in MFCS) and it must have non-
null values for all fashion items (for
example, the color IDs for SKUs within a
style). MFCS allows you to confgure
fashion items to not have a diff
aggregate, but it is invalid for RAP
applications that require diff aggregates
to function.
PROD_R7Invalid hierarchy relationships exist
for two or more SKUs having the
same item-parents or grandparents
but different hierarchy levels. This
will break downstream integrations
with AIF and Planning.		Correct the hierarchy levels so that all
SKUs having the same item-parents also
have the same subclass and above
hierarchy levels. TheBAD_PRODUCT_DS
table shows multiple messages for this
validation depending on where the
issue is, such asPRODUCT L3 AND
ITEMPARENT L2 SUBCLASS MISMATCH
for item level 2 and 3 items not having
the same subclasses.
PROD_R8You have more than 1 top level
(company) ID, which is not allowed.		Correct theTOP_PRODCAT_IDto contain
only one value on all rows.
PROD_R9Your input fle contains a different
top level (company) ID than what is
already in the database.		Correct theTOP_PRODCAT_IDto match
the company ID already in the system,
or erase the data in the system to
perform a clean load of new hierarchy
data.
PROD_R10Your input fle contains a different
top level (company) domain member
ID (onW_DOMAIN_MEMBER_DS_TL)
than what is already in the database.		Correct theTOP_PRODCAT_IDto match
the company ID already in the system,
or erase the data in the system to
perform a clean load of new hierarchy
data.
PROD_R11You have more than 1 top level
(company) description, which is not
allowed.		Correct theTOP_PRODCAT_DESCto
contain only one value on all rows.
PROD_R12You have duplicate item IDs when
ignoring the case, such asXYZand
xyz, which is not allowed on the
platform. Item IDs are not case-
sensitive.		Delete duplicate items such that you
only have a single ID across all
lowercase/uppercase variations.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
PROD_R13You have a one-to-many relationship
between the display IDs for a
hierarchy level and their unique
(UID) identifers, such as
LVL4_PRODCAT_UIDand
LVL4_PRODCAT_ID.		For a given value in the unique (UID)
columns, you must have one and only
one value in the associated display ID
column. Correct either the UID or ID
columns to ensure they align.
PROD_R14Invalid hierarchy relationships exist
for two or more items having the
same item-parents or grandparents
but different subclass levels between
your incoming product data and the
existing data already inW_PRODUCT_D
andW_PRODUCT_ATTR_D. The
BAD_PRODUCT_DStable shows
multiple records for this validation
depending on the items involved,
using the error message:EXISTING
PRODUCT AND ITEMGRANDPARENT/
ITEMPARENT/CHILD SUBCLASS
MISMATCH.		Correct the hierarchy levels for the
incoming items so that all items having
the same item-parents also have the
same subclass and above hierarchy
levels. This rule is for comparing
incoming item data and items already in
the database from prior loads; you must
ensure consistent parents and hierarchy
levels across all of them. There is also a
supplemental view
W_PRODUCT_ATTR_D_Vyou may query to
check the item and hierarchy
relationships for the mismatching items.
PROD_R15You have an invalid change to item
parent/grandparent relationships
when comparing incoming data to
existing dimensions, specifc to level
3 items.		For all level 3 items (between both
W_PRODUCT_DTS and existing
dimension W_PRODUCT_ATTR_D), they
must be updated to have the same
parent and grandparent relationships
such that no multi-parent relationship is
created by this load. View
RI_PARENT_GRANDPARENT_MISMATCH
_D_V provides the list of issues.
PROD_R16You have an invalid change to item
parent relationships when
comparing incoming data to existing
dimensions, specifc to level 2 items.
For all level 2 items (between both
W_PRODUCT_DTS and existing
dimension W_PRODUCT_ATTR_D), they
must be updated to have the same
parent and grandparent relationships
such that no multi-parent relationship is
created by this load. View
RI_PARENT_GRANDPARENT_MISMATCH
_D_V provides the list of issues.
ORG_STG_R1Null descriptions were detected for
attributes having a non-null ID
column, for one of the following:
CHANNEL_NAME,
PLANNING_CHANNEL_NAME,
STORE_FORMAT_DESC		Populate valid values for all
descriptions whereCHANNEL_ID,
PLANNING_CHANNEL_IDor
STORE_FORMAT_IDhas a non-null value,
and then reload the fle.
ORG_R1Many-to-many relationships exist in
your organization hierarchy, which is
not allowed. This is generally due to
the same child ID appearing below
multiple parent IDs.		Review all hierarchy levels for instances
of the same ID appearing under
multiple parents (such as a district
belonging to two different regions or
areas) and modify the data to remove
the multi-parent issues.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
ORG_R2The same organization hierarchy
node has multiple descriptions on
different rows of the input fle.		Modify your organization hierarchy fle
such that any given hierarchy ID has the
same description on all rows.
ORG_R3A node of the organization hierarchy
has no children under it. This could
be due to a reclass that didn’t delete
the old nodes, or when a new node is
added but no stores were created yet.		If possible, remove all cases of nodes
having no children (for example, if all
stores are reclassed out of a district,
delete the old district). Some AI
Foundation functionality will fail if you
attempt to run it on empty nodes.
ORG_R4Your organization hierarchy levels
use alphanumeric characters for the
level IDs. This is not allowed if you
are implementing Retail Insights; all
levels must be numbers.		If you are implementing RI, you must
alter your hierarchy to only use
numbers for every level of the
organization hierarchy. Other
characters are not allowed.
ORG_R5Your location type code
(ORG_TYPE_CODE) contains invalid
values.		Only specifc codesS,W, orEare allowed
in theORG_TYPE_CODEfeld, so you must
correct any other values and reload the
fle.
ORG_R6You have more than 1 top level
(company) ID, which is not allowed.		Correct theORG_TOP_NUMto contain only
one value on all rows of your input fle
and reload it.
ORG_R7Your input fle contains a different
top level (company) ID than what is
already in the database, which is not
allowed.		Correct theORG_TOP_NUMto match the
company ID already in the database
(W_INT_ORG_DHtable) or erase the data
in the system to perform a clean load of
new hierarchy data, if you do not want
to keep your existing dataset.
ORG_R8Your input fle contains a different
top level (company) domain member
ID or description (on
W_DOMAIN_MEMBER_DS_TL) than what
is already in the database.		Correct theORG_TOP_NUMand
ORG_TOP_DESCto match the company
data already in the database
(W_DOMAIN_MEMBER_LKP_TLtable) or
erase the data in the system to perform
a clean load of new hierarchy data, if
you do not want to keep your existing
dataset.
ORG_R9You have more than 1 top level
(company) description, which is not
allowed.		Correct theORG_TOP_DESCto contain
only one value on all rows of your input
fle and reload it.
ORG_R10You are attempting to load a location
inW_INT_ORG_DSwhich is already in
the data warehouse and fagged
inactive or deleted.		You may not reload or reuse location
IDs that are already in the data
warehouse and have been deleted or
inactivated. Delete the location from
your input data that is specifed in
BAD_ORG_INT_ORG_DS. If you believe
this validation is incorrect you may
raise an SR for assistance.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
ORG_R11You are attempting to load a location
inW_INT_ORG_DHSwhich is already
in the data warehouse and fagged
inactive or deleted.		You may not reload or reuse location
IDs that are already in the data
warehouse and have been deleted or
inactivated. Delete the location from
your input data that is specifed in
BAD_ORG_INT_ORG_DHS. If you believe
this validation is incorrect you may
raise an SR for assistance.
ORG_R12You are attempting to run
W_INT_ORG_DH_JOBbut the staging
tableW_INT_ORG_DHSis empty. The
location hierarchy is a full snapshot
dimension load and must always
have data.		Populate the location hierarchy data
intoW_INT_ORG_DHStable and then re-
run the validation job.
EXCH_RATE_R1Exchange rate dates are overlapping
for the same conversion, which will
result in multiple rates active for the
same date and currency.		Modify the start/end dates for the
exchange rate records to ensure there
are no overlapping dates. Only one rate
may be effective per day/currency
combination.
EXCH_RATE_R2Exchange rate dates have gaps which
will result in no rate being active for
one or more dates.		Modify the start/end dates for the
exchange rate records to ensure they
have no gaps between one end date and
the next start date, for any given
currency rate.
EXCH_RATE_R3You have provided currency
conversion in one direction (for
example, USD > CAD) but you did not
provide it in the alternate direction
(CAD > USD).		The system requires that you provide
currency rates going in both directions
for each currency code pair, to ensure
we are always able to convert into and
out of any supported currency.
ATTR_R1There is mismatched data between
theATTR.csvandPROD_ATTR.csv
fles		TheATTR.csvfle must have a header
record for all attribute groups and
values found inPROD_ATTR.csv. Correct
theATTR.csvfle to match exactly with
PROD_ATTR.csvor delete the
mismatched rows fromPROD_ATTR.csv.
ATTR_R2PROD_ATTR.csvcolumn
ATTR_GRP_TYPEcontains an invalid
type code.		The only valid codes forATTR_GRP_TYPE
are (ITEMDIFF,ITEMUDA,ITEMLIST,
COLOR, andPRODUCT_ATTRIBUTES).
Correct thePROD_ATTR.csvfle and
reload the data.
ATTR_R3ATTR.csvcolumnATTR_TYPE_CODE
contains an invalid type code.		The only valid codes for
ATTR_TYPE_CODEare (FF,LV,DT,SIZE,
FABRIC,SCENT,FLAVOR,STYLE,COLOR,
andDIFF). Correct theATTR.csvfle and
reload the data.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
ATTR_R4ATTR.csvcolumnsATTR_GROUP_IDor
ATTR_VALUE_IDcontain invalid
characters.		Attribute group and value IDs are used
as a hierarchy in Planning apps and are
restricted from having any spaces,
colons, or quotation marks as part of
the IDs. Correct theATTR.csvand
PROD_ATTR.csvfles and reload the
data.
POSINVDATA_R
1		Dates other than the current business
date were found on
W_RTL_INV_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
POSINVUDATA_
R2		Dates other than the current business
date were found on
W_RTL_INVU_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
POSPRICEDATA
_R3		Dates other than the current business
date were found on
W_RTL_PRICE_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
POSNCOSTDATA
_R4		Dates other than the current business
date were found on
W_RTL_NCOST_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
POSBCOSTDATA
_R5		Dates other than the current business
date were found on
W_RTL_BCOST_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
POSPOONORDD
ATA_R6		Dates other than the current business
date were found on
W_RTL_PO_ONORD_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
POSPOONALCD
ATA_R7		Dates other than the current business
date were found on
W_RTL_PO_ONALC_IT_LC_DY_FS. The
DAY_DTis being compared to “select
MCAL_NUMfromW_RTL_CURR_MCAL_G
whereMCAL_TYPE = 'DT'”.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date; correct the data
and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

Table 6-2 (Cont.) Validation Rule Details

Rule IDExplanationResolution
POSCOMPPRICE
DATA_R8		Dates other than the current business
date were found on
W_RTL_COMP_PRICE_IT_LC_DY_FS.		Daily positional fact data must only
contain aDAY_DTvalue matching the
current business date, correct the data
TheDAY_DTis being compared to
“selectMCAL_NUMfrom
W_RTL_CURR_MCAL_Gwhere		and reload. This can be a sign that an
incorrect fle was used in the batch or
the dates are out of sync.
MCAL_TYPE = 'DT'”.

In all cases where you need to correct your file and reload it, you are expected to push only the corrected files into the system using the data reprocessing ad hoc programs in the POM AIF DATA schedule. Refer to the AI Foundation Data Standalone Processes chapter for details on these programs:

1. REPROCESS_ZIP_FILE_PROCESS_ADHOC to upload your corrected file(s)

2. CSV_REPROCESS_ADHOC or DAT_REPROCESS_ADHOC to import the corrected files and transform the data from staging to target tables (only running the required programs, not the entire ad hoc process)

Operations Guide G56397-04 Copyright© 2026, Oracle and/or its affiliates.

In this guide

  • 2 AI Foundation Data Standalone Processes — AI Foundation Operations Guide · shares CSV_REPROCESS_ADHOC, C_ODI_PARAM_VW, DAT_REPROCESS_ADHOC, DIM_PROD_VALIDATOR_JOB
  • 2 Setup and Configuration — Retail Insights Cloud Service Implementation Guide · shares C_ODI_PARAM_VW, START_DT, W_PRODUCT_ATTR_D
  • 5 AI Foundation Data Standalone Process Flows — AI Foundation Operations Guide · shares DAT_REPROCESS_ADHOC, W_DOMAIN_MEMBER_DS_TL, W_DOMAIN_MEMBER_LKP_TL, W_INT_ORG_DHS
  • 2 Implementation Overview — AI Foundation Implementation Guide · shares W_DOMAIN_MEMBER_LKP_TL, W_INT_ORG_DH, W_PRODUCT_ATTR_D, W_PRODUCT_D
  • 18 Attribute Processing — AI Foundation Implementation Guide · shares W_DOMAIN_MEMBER_LKP_TL, W_PRODUCT_ATTR_D, W_PRODUCT_D
  • 10 Size Profiles — AI Foundation Implementation Guide · shares W_DOMAIN_MEMBER_LKP_TL, W_INT_ORG_DHS, W_INT_ORG_DS, W_PRODUCT_ATTR_DS

Graph View

Backlinks