Search Results upgrade_mrc

Overview

APP.PA_MC_UPG is a PL/SQL upgrade utility in Oracle Projects (PA) that supports the Multiple Reporting Currencies (MRC) conversion process. MRC allows a single business transaction to be accounted and reported in more than one set of books via a primary and one or more reporting set of books. The package encapsulates the procedural logic required to migrate pre-existing project transaction data into MRC-compliant reporting sets of books so that historical and ongoing project costs, events, invoices, and distributions are recognized in the reporting currency alongside the primary functional currency.

According to its header comment, PA_MC_UPG is the "Main procedure for MRC upgrade," accepting parameters passed from an external driving script. It performs validation, initialization, exchange rate caching, and mass data conversion across the Projects schema. Its return contract is consistent and documented: G_Err_Code reports 0 on success or -1 on abort (error), G_Err_Stage carries the corresponding error message, and Oracle errors are surfaced as raised exceptions. The package owner is APPS and it is classified under "OTHER." It is not referenced by any other PL/SQL package, indicating it is a standalone, script-invoked upgrade utility rather than a shared runtime API. The source header dates to 2005 (PAXMCUPS.pls 120.1).

Key Procedures and Functions

The package exposes 54 documented procedures and functions. The principal entry point, UPGRADE_MRC, orchestrates the MRC upgrade and receives the primary set of books, reporting set of books, from/to project number range, rounding and currency-rate options, a debug flag, an option to include closed projects, a process mode defaulting to "PLSQL," and a validation-check switch. The remaining routines decompose this flow:

• VALIDATE_PARAMS — validates the script-supplied parameters and sets dependent variables.
• INIT_UPGRADE — initializes global variables required by the upgrade run.
• CACHE_EXCHANGE_RATES — pre-loads exchange rates for all transaction currencies plus the reporting currency, listing any currency lacking a rate so it can be populated and the routine rerun.
• INSERT_TEMP_RATES — inserts a transaction currency and its fixed rate at the initial MRC date into the rate cache.
• VALIDATE_SOB_ASSIGN, VALIDATE_SOB, VALIDATE_FIRST_MRC_PERIOD, VALIDATE_SOB_ASSIGN — perform set-of-books assignment and first-MRC-period checks.
• CHECK_FUTURE_RECORD — identifies records dated beyond the MRC boundary.
• INSERT_HISTORY_REC — writes historical record entries during conversion.
• GET_PROJECT_NUMBER — resolves the project number under process.
• GET_USER_LOCK, RLS_USER_LOCK — acquire and release concurrency locks (backed by DBMS_LOCK).
• GET_TABLE_STATUS, CONVERT_TABLE — assess a source table and drive its conversion.
• INSERT_RECS, UPDATE_RECS, INSERT_CDL, INSERT_CRDL, INSERT_ERDL — build new reporting-currency rows in cost distribution, cost/expenditure, and related detail tables.

Tables Accessed

The package operates over the core Projects and MRC schema through APPS synonyms. PA_PROJECTS, PA_PROJECT_TYPES, PA_PROJECT_STATUSES and PA_IMPLEMENTATIONS_ALL provide project and implementation context. Source transaction data is read from PA_EXPENDITURE_ITEMS_ALL, PA_EVENTS, PA_DRAFT_INVOICES, PA_DRAFT_INVOICE_ITEMS, and PA_DRAFT_INVOICE_DETAILS_ALL. Converted output is written to PA_COST_DISTRIBUTION_LINES_ALL. PA_MC_UPGRADE_RATES stores the cached exchange rates produced by the rate routines. DBMS_LOCK provides advisory locking and UTL_FILE supplies file-based logging/diagnostics.

Usage Notes

PA_MC_UPG is an administrative, one-time migration utility invoked during the MRC enablement of an existing Oracle Projects implementation. It is not called from standard application forms at runtime; rather, it is driven by a SQL*Plus or concurrent-program wrapper that supplies the set-of-books and project-range parameters. Operators must ensure all required exchange rates exist before execution, since CACHE_EXCHANGE_RATES will halt on missing currencies and require rerun. Because the routines manipulate cost distribution and invoice detail tables directly, the package should be run inside a controlled maintenance window with appropriate backups, and the debug and validation switches used to verify scope before committing changes. Its non-referenced status confirms it is intended solely for scripted upgrade execution rather than ongoing application use.