Results for “ddr_load_seq”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

DDR_REF_UTIL_PKG is a reference-data utility package body residing in the APPS schema. It forms part of the Oracle Retail Data Model (DDR) reference data loading infrastructure, which populates and maintains the dimensional reference tables that underpin retail analytics and merchandise hierarchy reporting in Oracle E-Business Suite 12.1.1 and 12.2.2. The package encapsulates the validation, truncation, and error-handling logic required to load retail and manufacturing reference hierarchies — item, organization, and time dimensions — into the DDR star schemas. It is classified as an OTHER API, meaning it is not a public, supported interface in the traditional sense, but rather an internal utility invoked by the DDR extraction and load processes.

Key Procedures and Functions

The package exposes eight documented procedures and functions:

• GET_ERR_MSG — Retrieves error message text, typically for a previously logged load or validation error, supporting diagnostic reporting during reference data loads.
• TRUNC_INTERFACE — Truncates interface (staging) tables prior to a fresh reference data load, ensuring no residual rows contaminate the incoming extract.
• CHECK_MFG_ITEM_HCHY — Validates the manufacturing item hierarchy, confirming that item, class, company, division, group, and subclass relationships are internally consistent before load.
• CHECK_ORG_HCHY — Validates the organization hierarchy, including area, region, district, chain, business entity, and business unit levels.
• CHECK_RTL_ITEM_HCHY — Validates the retail item hierarchy across item, class, company, department, division, group, and subclass dimensions.
• CHECK_TIME_HCHY — Validates the time hierarchy (day, business week, month, quarter, year) for completeness and referential integrity.
• GET_REF_MAP_RUN_ID — Obtains the reference mapping run identifier, which ties a given load execution to its batch of reference hierarchy records and sequence control.
• PARSE_PAD_ACCOUNTS — Parses padded account strings, supporting account-segment normalization used in retail financial and organizational reference data.

Tables Accessed

The package references its tables through APPS synonyms. The multiplicity of referenced tables reflects its central role in hierarchy validation:

• DDR_LOAD_SEQ — The load sequence control table. This is the object tied to the search term "ddr_load_seq"; it supplies the sequence numbering used to coordinate and order reference loads. This explains the user query that surfaced this documentation.
• DDR_L_ERR and DDR_L_SYS_PARAM — Error logging and system parameter tables supporting diagnostics and configurable load behavior.
• Manufacturing item hierarchy tables — DDR_R_MFG_ITEM, DDR_R_MFG_ITEM_CLASS, DDR_R_MFG_ITEM_CMPNY, DDR_R_MFG_ITEM_DIV, DDR_R_MFG_ITEM_GRP, DDR_R_MFG_ITEM_SBC, and DDR_R_MFG_SKU_ITEM.
• Retail item hierarchy tables — DDR_R_RTL_ITEM, DDR_R_RTL_ITEM_CLASS, DDR_R_RTL_ITEM_CMPNY, DDR_R_RTL_ITEM_DEPT, DDR_R_RTL_ITEM_DIV, DDR_R_RTL_ITEM_GRP, DDR_R_RTL_ITEM_SBC, and DDR_R_RTL_SKU_ITEM.
• Organization hierarchy tables — DDR_R_ORG, DDR_R_ORG_AREA, DDR_R_ORG_BSNS_ENT, DDR_R_ORG_BSNS_UNIT, DDR_R_ORG_CHAIN, DDR_R_ORG_DSTRCT, and DDR_R_ORG_RGN.
• Time hierarchy tables — DDR_R_DAY, DDR_R_BSNS_WK, DDR_R_BSNS_MNTH, DDR_R_BSNS_QTR, and DDR_R_BSNS_YR.
• DUAL and STANDARD — System utility references.

Usage Notes

DDR_REF_UTIL_PKG is not referenced by any other database object per the documented metadata, indicating it is invoked directly by concurrent programs, SQL*Plus load scripts, or custom PL/SQL rather than through inter-package calls. Typical invocation occurs during DDR reference data refresh cycles, where an operator or scheduler runs the load program containing these calls after the extraction is staged. The validation routines (CHECK_*_HCHY) and TRUNC_INTERFACE are usually executed in sequence: truncate staging, load, validate, then resolve errors via GET_ERR_MSG. Because the package is an unsupported internal utility, customizations should avoid direct modification; instead, wrapper logic should be layered around it. The DDR_LOAD_SEQ table is central to sequencing these operations and is the most likely entry point for developers investigating load ordering issues.