Search Results cday_calendar_date

Overview

APPS.POA_PORTAL_POPULATE_C is a PL/SQL package body that supports the Procurement Operations Analysis (POA) portal within Oracle E-Business Suite 12.1.1 and 12.2.2. Its central purpose is to populate the denormalized summary and fact tables that drive the POA dashboard, providing aggregated procurement metrics across purchasing, supplier performance, receiving, and payables dimensions. The package orchestrates a full refresh cycle: it first clears prior results, then derives period boundaries from the FII time dimension API, and finally inserts current-quarter ("C") and last-quarter ("L") rows into the portal summary structures. The driver procedure, POPULATE_POA, accepts an error buffer and return code so it can be scheduled as a concurrent program, and it emits a summary of row counts across each insert path for diagnostic purposes. The package is labeled with the ETRM classification "OTHER" and is owned by APPS. It is referenced by no other packages, indicating it is invoked as a top-level, scheduled entry point rather than as a shared utility. All positional date variables in the source (cycq_start, cycq_end, lycq_start, lycq_end) source their values from FII_TIME_WH_API, which is relevant to the searched term "cday_calendar_date" because that column in the EDW/FII time dimension is the calendar date key used to align fact rows to reporting periods.

Key Procedures and Functions

• POPULATE_POA — Main driver that truncates staging tables, establishes current-quarter and last-quarter date ranges, and calls each insert routine in sequence, assembling the row-count error buffer.
• POPULATE_POA_FII — Populates the FII-based procurement indicator summaries, aligning fact data with the financial intelligence time hierarchy.
• INSERT_ROWS_PD — Loads purchase distribution (PO distribution) fact rows for a given date range and period indicator.
• INSERT_ROWS_SP — Loads supplier performance rows into the POA portal summary structures.
• INSERT_ROWS_SR — Loads supplier response or sourcing-related rows.
• INSERT_ROWS_CM — Loads commitment or contract management summary rows.
• INSERT_ROWS_RCV — Loads receiving transaction aggregates for the reporting window.
• INSERT_ROWS_CROSS — Performs a cross-dimensional insert, combining multiple fact sources into a consolidated portal view.
• TRUNCATE_TABLES — Clears the target portal tables before each refresh, returning a success indicator.

Tables Accessed

The package reads from EDW dimension synonyms — EDW_ITEMS_M, EDW_LOOKUP_M, EDW_ORGANIZATION_M, EDW_TIME_M, and EDW_TRD_PARTNER_M — to enrich fact records with item, lookup, organization, time, and trading-partner attributes. EDW_TIME_M is the calendar date dimension and is the join point corresponding to the cday_calendar_date key. Source fact tables include FII_AP_OP_INDICATOR_SUMMARY, FII_AP_TRANS_BACKLOG_SUMMARY, POA_EDW_PO_DIST_F, POA_EDW_RCV_TXNS_F, and POA_EDW_SUP_PERF_F. The package writes to POA_PORTAL_FII_SUMMARY, POA_PORTAL_PDIST, POA_PORTAL_PDIST1, POA_PORTAL_PDIST2, and POA_PORTAL_RCPT_SUM, which are the tables truncated and repopulated on each run.

Usage Notes

POPULATE_POA is designed for scheduled execution, typically registered as a concurrent program or invoked from a shell/DBMS_SCHEDULER job that supplies the Errbuf and Retcode OUT parameters. Because TRUNCATE_TABLES removes existing portal content, the job should not overlap with active dashboard query windows. All target objects are accessed through APPS synonyms, so the package runs under standard EBS responsibility and security context. The FII_TIME_WH_API is used to establish the current and last quarter boundaries consistently with the FII time warehouse; upgrades or changes to the FII calendar that alter cday_calendar_date values will shift the reporting windows accordingly. No other database packages depend on POA_PORTAL_POPULATE_C, so its procedures are not exposed as a shared API.