Search Results csd_returns_bi_pvt




Overview
Explore in AI Chat

Overview

CSD_RETURNS_BI_PVT is a private PL/SQL package in the APPS schema of Oracle E-Business Suite, classified as a Private (PVT) API and declared with AUTHID CURRENT_USER. Its purpose is to support the extract, transform, and load (ETL) processing that populates the Returns Business Intelligence fact tables used by Oracle's Supply Chain and Order Management analytics. The package coordinates the initial and incremental loading of transactional return data drawn from receiving, shipping, and material transaction sources into dedicated fact tables. As a private package, it is not a supported public integration point; it is invoked internally by the Returns BI concurrent program driver. The package declares global constants for concurrent manager return codes (error, warning, and success) and constants naming the three principal fact tables it maintains.

Key Procedures and Functions

  • GET_LAST_RUN_DATE — A function that returns the last run date for the ETL process. It accepts a fact name as input and is used to establish the high-water mark from which incremental data extraction should proceed. This is the object referenced in the user's search.
  • INITIAL_LOAD_RCVTXNS_ETL — A procedure that performs the initial load of Receiving transaction facts into the Returns BI receiving transactions fact table.
  • INITIAL_LOAD_SHPTXNS_ETL — A procedure introduced in the 12.2 line that performs the initial load of Shipping transaction facts.
  • INITIAL_LOAD_MTLTXNS_ETL — A procedure that performs the initial load of Material transaction facts.
  • INITIAL_LOAD — A coordinating procedure that orchestrates the initial load activities across the fact tables.
  • RETURNS_BI_DRIVER_MAIN — The main driver procedure that governs the overall ETL execution flow, determining whether an initial load is required and otherwise performing incremental processing.

The procedures that interact with the concurrent manager follow the standard errbuf and retcode pattern, returning success, warning, or error codes to the requesting concurrent program.

Tables Accessed

The package reads staging and dimension data and writes to the Returns BI fact tables. Reference and party data is drawn from HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTIES, HZ_PARTY_SITES, and HZ_GEO_NAME_REFERENCES to resolve customer, site, and geography attributes. Item data is sourced from MTL_ITEM_CATEGORIES, CST_ITEM_COSTS, and MTL_MATERIAL_TRANSACTIONS. Return reason information is resolved through CSD_MTL_DISPOSITION_REASONS and CSD_RETURN_STREAM_REASONS. Run-state tracking is maintained via CSD_FACT_DETAILS. The three target fact tables are CSD_RETURNS_BI_RCV_TXNS_F, CSD_RETURNS_BI_SHP_TXNS_F, and CSD_RETURNS_BI_MTL_TXNS_F.

Usage Notes

CSD_RETURNS_BI_PVT is invoked indirectly through the Returns BI ETL concurrent program rather than through direct API calls. The driver procedure RETURNS_BI_DRIVER_MAIN determines the execution mode, and GET_LAST_RUN_DATE is consulted to establish the incremental extraction window. Because the package is classified as PVT, Oracle does not guarantee its signature across releases, and custom code should not call it directly. The metadata indicates it is referenced by zero other packages, confirming its role as a terminal leaf that is driven from the concurrent manager layer rather than composed into other PL/SQL units. The presence of the shipping transaction procedure annotated for 12.2 reflects the incremental evolution of Returns BI content between the 12.1.1 and 12.2.2 releases.