Search Results pa_draft_inv_details_efc

Overview

PA_DRAFT_INV_DETAILS_EFC is a table owned by the PA (Projects) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. As documented in the ETRM repository, it stores invoice detail NCU (Non-Currency-Unit / no-currency-concept) records captured prior to the EFC (Euro Functional Currency) switch. The table exists to preserve the pre-conversion state of draft invoice detail lines so that the euro currency transition does not destroy the original amounts, currencies, and rates held at the time of invoice drafting.

Its scope is narrow and transitional: it is populated and referenced within the EFC-related processing path rather than in the routine invoice lifecycle. The table is a standalone object in the documented FK structure and is not joined into the mainstream draft invoice model by default. Under the heuristic Data Vault classification mined from the actual foreign key relationships, PA_DRAFT_INV_DETAILS_EFC is modeled as a standalone, suggesting it functions neither as a shared hub nor as a dependent satellite of the primary invoice detail entity. Occasional EFC-era reporting and reconciliation queries against this table may therefore need to join back to PA_DRAFT_INVOICE_DETAILS_ALL on DRAFT_INVOICE_DETAIL_ID to recover the full context, as no other dependencies are documented.

The documented physical schema for 12.2.2 contains eight columns, reflecting a deliberately minimal footprint aligned to the pre-switch archival purpose rather than a full mirror of the draft invoice detail structure.

Key Information Stored

The eight documented columns fall into two groups: a key column and seven amount/currency attributes.

• DRAFT_INVOICE_DETAIL_ID — The surrogate primary key of this table and the foreign key to PA_DRAFT_INVOICE_DETAILS_ALL. It is the single documented join path between the pre-EFC record and its parent draft invoice detail line.
• SET_OF_BOOKS_ID — Identifies the ledger (set of books) under which the pre-switch invoice detail record was held. This is the principal business-key candidate and the primary partitioning attribute for querying, since pre-EFC balances must be examined per ledger.
• PROJACCT_CURRENCY_CODE — The currency of the project accounting amounts. This is the critical currency-of-record column: it preserves the currency in force before the functional-currency switch.
• PROJACCT_BILL_AMOUNT — The bill amount expressed in the project accounting currency.
• PROJACCT_COST_AMOUNT — The cost amount expressed in the project accounting currency.
• DENOM_BILL_AMOUNT — The denominated bill amount, holding the pre-switch bill value in its original denominator terms.
• BASE_AMOUNT — The base (functional) amount as recorded before the EFC conversion.
• BILL_RATE — The bill rate applied to the detail line at the time of the pre-switch record.

No unique indexes beyond DRAFT_INVOICE_DETAIL_ID are documented in the ETRM extract; the identifier column is the only surrogate key, while SET_OF_BOOKS_ID combined with the parent detail key forms the practical business-key candidate for retrieval.

Common Use Cases and Queries

The dominant use case is post-conversion reconciliation: auditors and project accountants compare pre-EFC draft invoice amounts with the converted values in the live draft invoice tables to confirm that the currency restatement was mathematically correct and complete.

• Pre-switch amount retrieval for a ledger. Query all preserved records for a given set of books to inspect original currency amounts:

SELECT draft_invoice_detail_id, projAcct_currency_code,
       projAcct_bill_amount, projAcct_cost_amount,
       denom_bill_amount, base_amount, bill_rate
FROM   pa.pa_draft_inv_details_efc
WHERE  set_of_books_id = :p_set_of_books_id;

• Reconciliation against the current detail line. Join to the parent table on DRAFT_INVOICE_DETAIL_ID to compare the preserved pre-switch values with current values, sizing discrepancies by bill amount:

SELECT e.draft_invoice_detail_id,
       e.base_amount       AS pre_efc_base,
       d.base_amount       AS current_base,
       e.bill_rate         AS pre_efc_rate,
       d.bill_rate         AS current_rate
FROM   pa.pa_draft_inv_details_efc e,
       pa.pa_draft_invoice_details_all d
WHERE  e.draft_invoice_detail_id = d.draft_invoice_detail_id
AND    e.set_of_books_id = :p_set_of_books_id;

• Currency-population analysis. Aggregate by PROJACCT_CURRENCY_CODE to determine which currencies were still represented at draft invoice detail level at the moment of the switch.
• Completeness validation. Count preserved records versus expected pre-switch detail lines per ledger, and identify orphaned keys where no matching current detail row exists.

Because the table is transitional, queries should always constrain on SET_OF_BOOKS_ID and be treated as point-in-time evidence rather than operational data.

Related Objects

The documented relationship graph for this object is intentionally shallow. The following are the significant related objects:

• PA_DRAFT_INVOICE_DETAILS_ALL — The parent draft invoice detail table and the only documented foreign-key target. Joined via PA_DRAFT_INV_DETAILS_EFC.DRAFT_INVOICE_DETAIL_ID = PA_DRAFT_INVOICE_DETAILS_ALL.DRAFT_INVOICE_DETAIL_ID, it supplies the current post-switch values and the broader invoice-line attributes (project, task, invoice, expenditure context) not duplicated in the EFC table.
• PA_DRAFT_INVOICES_ALL — The draft invoice header, reached through the parent detail line, providing the vendor, customer, invoice number, and billing currency context required for any meaningful pre-EFC comparison.
• PA_DRAFT_INVOICE_ITEMS_ALL — The intermediate item-level table between the invoice header and its detail lines; relevant when reconstructing the full pre-switch invoice hierarchy.
• AR/GL currency conversion and set-of-books reference data — FND and GL ledger definitions keyed by SET_OF_BOOKS_ID are required to translate the stored identifiers into ledger names for reporting.
• EFC conversion programs and PA concurrent processes — The pre-EFC switch maintenance and conversion routines that populated this table; these are the operational producers of its rows and the authority on when records were captured.

Since the ETRM data vault classification is standalone, no other hubs or satellites are documented as directly dependent on this table, and joins beyond the parent detail tuple, the draft invoice header, and the ledger reference are advisory rather than defined relationships. This limited footprint is consistent with the table's role as a targeted pre-conversion archive.