Search Results pa_draft_invoices_pk

Overview

PA_DRAFT_INVOICES_ALL is the Oracle Projects (PA) transactional table that stores draft invoice header information generated for project contracts. Within Oracle EBS 12.1.1 and 12.2.2, it acts as the staging and control point for the invoice lifecycle — from draft generation through approval, release, and transfer into Oracle Receivables as an RA_CUSTOMER_TRX_ALL transaction. Each row represents a single draft invoice for a given project and draft invoice number combination.

From a dimensional modeling perspective, the FK structure suggests a satellite-leaning classification: the table is anchored to PA_PROJECTS_ALL via PROJECT_ID and carries the descriptive, mutable attributes of an invoice (amounts, dates, status flags, addresses). The presence of self-referencing FK relationships (PROJECT_ID and DRAFT_INVOICE_NUM_CREDITED, PROJECT_ID implemented as both PK and FK to itself) also makes it partially link-like, tying draft invoices to credited or predecessor invoice records.

The _ALL suffix indicates this is a multi-organization (MLS) table governed by ORG_ID, so all queries must respect the operating unit context.

Key Information Stored

The primary key is PA_DRAFT_INVOICES_PK (PROJECT_ID, DRAFT_INVOICE_NUM), and a second documented unique index, PA_DRAFT_INVOICES_U1, is defined on the same business-key pair. Among the 94 columns, the most operationally significant are:

• PROJECT_ID and DRAFT_INVOICE_NUM — composite PK identifying the draft invoice and its project.
• TRANSFER_STATUS_CODE, TRANSFERRED_DATE, TRANSFER_REJECTION_REASON — controls the hand-off to Receivables.
• RA_INVOICE_NUMBER and SYSTEM_REFERENCE — link to the resulting AR transaction in RA_CUSTOMER_TRX_ALL.
• CUSTOMER_ID, BILL_TO_CUSTOMER_ID, SHIP_TO_CUSTOMER_ID — customer references to HZ_CUST_ACCOUNTS.
• BILL_TO_ADDRESS_ID, SHIP_TO_ADDRESS_ID — address references to HZ_CUST_ACCT_SITES_ALL and RA_ADDRESSES_ALL.
• INVOICE_DATE, GL_DATE, PA_DATE, BILL_THROUGH_DATE — key dating attributes for reporting and accounting.
• INV_CURRENCY_CODE, INV_EXCHANGE_RATE, INV_RATE_TYPE — invoice currency and conversion details.
• RECEIVABLE_CODE_COMBINATION_ID, UNBILLED_CODE_COMBINATION_ID, UNEARNED_CODE_COMBINATION_ID — GL distribution accounts.
• CANCELED_FLAG, WRITE_OFF_FLAG, APPROVED_DATE, RELEASED_DATE — lifecycle status indicators.
• ORG_ID — operating unit / MLS discriminator.

Common Use Cases and Queries

Typical reporting scenarios include tracking draft invoices awaiting approval, identifying transfers that were rejected by AR, and reconciling project invoices to their AR transactions. A representative query joining to the customer master — directly relevant to the hz_cust_accounts search — is:

• Listing invoices by customer: SELECT pdi.draft_invoice_num, hca.account_number, hca.account_name FROM pa.pa_draft_invoices_all pdi, hz.hz_cust_accounts hca WHERE pdi.customer_id = hca.cust_account_id AND pdi.org_id = :org_id;
• Locating un-transferred invoices: filter on TRANSFER_STATUS_CODE IS NULL or unequal to the transferred value.
• Reconciling to Receivables via SYSTEM_REFERENCE joined to RA_CUSTOMER_TRX_ALL.
• Credit-memo tracing through DRAFT_INVOICE_NUM_CREDITED and CREDIT_MEMO_REASON_CODE.

Related Objects

Key dependencies and references include:

• PA_DRAFT_INVOICE_DETAILS_ALL and PA_DRAFT_INVOICE_ITEMS — line-level children referencing PROJECT_ID/DRAFT_INVOICE_NUM.
• HZ_CUST_ACCOUNTS — joined on CUSTOMER_ID, BILL_TO_CUSTOMER_ID, SHIP_TO_CUSTOMER_ID.
• HZ_CUST_ACCT_SITES_ALL and RA_ADDRESSES_ALL — joined on BILL_TO_ADDRESS_ID and SHIP_TO_ADDRESS_ID.
• RA_CUSTOMER_TRX_ALL — joined on SYSTEM_REFERENCE.
• PA_PROJECTS_ALL — joined on PROJECT_ID.
• GL_CODE_COMBINATIONS — joined on the various *_CODE_COMBINATION_ID columns.
• PA_MC_DRAFT_INV_ITEMS, PA_DISTRIBUTION_WARNINGS, and JAI_PA_DRAFT_INVOICES — referencing tables.