AP_INVOICE_DISTRIBUTIONS_ALL is the child table of AP_INVOICE_LINES_ALL and more or less can be used to find the GL Code Combinations used by the invoice line. To connect AP_INVOICE_LINES_ALL table with AP_INVOICE_DISTRIBUTIONS_ALL, use the column INVOICE_ID in conjunction with INVOICE_LINE_NUMBER. Of course INVOICE_ID is the primary key of Invoice Table. So we can say that this table AP_INVOICE_DISTRIBUTIONS_ALL plays a critical role in P2P Cycle in Oracle.
AP_INVOICE_DISTRIBUTIONS_ALL can also be used to find the connectivity between AP invoice & PO or AP invoices & Project. But keep an eye on column quantity_invoiced. As every AP invoice Line can be made of multiple PO Distributions or tasks, this table can give specific amount from PO Distribution or task.
(By the way, I was helped by this book a great deal, just in case you want to refer. No no.. I am neither the author nor the publisher of this book. )
Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| ACCOUNTING_DATE | DATE | N |
| ASSETS_ADDITION_FLAG | VARCHAR2(4) | N |
| ASSETS_TRACKING_FLAG | VARCHAR2(4) | N |
| DISTRIBUTION_LINE_NUMBER | NUMBER | N |
| DIST_CODE_COMBINATION_ID | NUMBER | N |
| INVOICE_ID | NUMBER | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LINE_TYPE_LOOKUP_CODE | VARCHAR2(100) | N |
| PERIOD_NAME | VARCHAR2(60) | N |
| SET_OF_BOOKS_ID | NUMBER | N |
| AMOUNT | NUMBER | Y |
| BASE_AMOUNT | NUMBER | Y |
| BATCH_ID | NUMBER | Y |
| CREATED_BY | VARCHAR2(256) | N |
| CREATION_DATE | TIMESTAMP(6) | N |
| DESCRIPTION | VARCHAR2(960) | Y |
| FINAL_MATCH_FLAG | VARCHAR2(4) | Y |
| INCOME_TAX_REGION | VARCHAR2(40) | Y |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| MATCH_STATUS_FLAG | VARCHAR2(4) | Y |
| POSTED_FLAG | VARCHAR2(4) | Y |
| PO_DISTRIBUTION_ID | NUMBER | Y |
| PROGRAM_APPLICATION_ID | NUMBER | Y |
| PROGRAM_ID | NUMBER | Y |
| PROGRAM_UPDATE_DATE | DATE | Y |
| QUANTITY_INVOICED | NUMBER | Y |
| REQUEST_ID | NUMBER | Y |
| REVERSAL_FLAG | VARCHAR2(4) | Y |
| TYPE_1099 | VARCHAR2(40) | Y |
| UNIT_PRICE | NUMBER | Y |
| ENCUMBERED_FLAG | VARCHAR2(4) | Y |
| STAT_AMOUNT | NUMBER | Y |
| ATTRIBUTE1 – ATTRIBUTE15 | VARCHAR2(600) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(600) | Y |
| EXPENDITURE_ITEM_DATE | DATE | Y |
| EXPENDITURE_ORGANIZATION_ID | NUMBER | Y |
| EXPENDITURE_TYPE | VARCHAR2(120) | Y |
| PARENT_INVOICE_ID | NUMBER | Y |
| PA_ADDITION_FLAG | VARCHAR2(4) | Y |
| PA_QUANTITY | NUMBER | Y |
| PREPAY_AMOUNT_REMAINING | NUMBER | Y |
| PROJECT_ID | NUMBER | Y |
| TASK_ID | NUMBER | Y |
| AWT_FLAG | VARCHAR2(4) | Y |
| AWT_GROUP_ID | NUMBER | Y |
| AWT_TAX_RATE_ID | NUMBER | Y |
| AWT_GROSS_AMOUNT | NUMBER | Y |
| AWT_INVOICE_ID | NUMBER | Y |
| AWT_ORIGIN_GROUP_ID | NUMBER | Y |
| REFERENCE_1 | VARCHAR2(120) | Y |
| REFERENCE_2 | VARCHAR2(120) | Y |
| ORG_ID | NUMBER | N |
| AWT_INVOICE_PAYMENT_ID | NUMBER | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE1 – GLOBAL_ATTRIBUTE20 | VARCHAR2(600) | Y |
| RECEIPT_VERIFIED_FLAG | VARCHAR2(4) | Y |
| RECEIPT_REQUIRED_FLAG | VARCHAR2(4) | Y |
| RECEIPT_MISSING_FLAG | VARCHAR2(4) | Y |
| JUSTIFICATION | VARCHAR2(960) | Y |
| EXPENSE_GROUP | VARCHAR2(320) | Y |
| START_EXPENSE_DATE | DATE | Y |
| END_EXPENSE_DATE | DATE | Y |
| RECEIPT_CURRENCY_CODE | VARCHAR2(60) | Y |
| RECEIPT_CONVERSION_RATE | NUMBER | Y |
| RECEIPT_CURRENCY_AMOUNT | NUMBER | Y |
| DAILY_AMOUNT | NUMBER | Y |
| WEB_PARAMETER_ID | NUMBER | Y |
| ADJUSTMENT_REASON | VARCHAR2(960) | Y |
| AWARD_ID | NUMBER | Y |
| CREDIT_CARD_TRX_ID | NUMBER | Y |
| DIST_MATCH_TYPE | VARCHAR2(100) | Y |
| RCV_TRANSACTION_ID | NUMBER | Y |
| INVOICE_DISTRIBUTION_ID | NUMBER | N |
| PARENT_REVERSAL_ID | NUMBER | Y |
| TAX_RECOVERABLE_FLAG | VARCHAR2(4) | Y |
| MERCHANT_DOCUMENT_NUMBER | VARCHAR2(320) | Y |
| MERCHANT_NAME | VARCHAR2(320) | Y |
| MERCHANT_REFERENCE | VARCHAR2(960) | Y |
| MERCHANT_TAX_REG_NUMBER | VARCHAR2(320) | Y |
| MERCHANT_TAXPAYER_ID | VARCHAR2(320) | Y |
| COUNTRY_OF_SUPPLY | VARCHAR2(20) | Y |
| MATCHED_UOM_LOOKUP_CODE | VARCHAR2(100) | Y |
| GMS_BURDENABLE_RAW_COST | NUMBER | Y |
| ACCOUNTING_EVENT_ID | NUMBER | Y |
| PREPAY_DISTRIBUTION_ID | NUMBER | Y |
| UPGRADE_POSTED_AMT | NUMBER | Y |
| UPGRADE_BASE_POSTED_AMT | NUMBER | Y |
| INVENTORY_TRANSFER_STATUS | VARCHAR2(4) | Y |
| COMPANY_PREPAID_INVOICE_ID | NUMBER | Y |
| CC_REVERSAL_FLAG | VARCHAR2(4) | Y |
| INVOICE_INCLUDES_PREPAY_FLAG | VARCHAR2(4) | Y |
| PA_CMT_XFACE_FLAG | VARCHAR2(4) | Y |
| CANCELLATION_FLAG | VARCHAR2(4) | Y |
| INVOICE_LINE_NUMBER | NUMBER | Y |
| CORRECTED_INVOICE_DIST_ID | NUMBER | Y |
| ROUNDING_AMT | NUMBER | Y |
| CHARGE_APPLICABLE_TO_DIST_ID | NUMBER | Y |
| CORRECTED_QUANTITY | NUMBER | Y |
| RELATED_ID | NUMBER | Y |
| ASSET_BOOK_TYPE_CODE | VARCHAR2(120) | Y |
| ASSET_CATEGORY_ID | NUMBER | Y |
| DISTRIBUTION_CLASS | VARCHAR2(120) | Y |
| FINAL_PAYMENT_ROUNDING | NUMBER | Y |
| FINAL_APPLICATION_ROUNDING | NUMBER | Y |
| AMOUNT_AT_PREPAY_XRATE | NUMBER | Y |
| CASH_BASIS_FINAL_APP_ROUNDING | NUMBER | Y |
| AMOUNT_AT_PREPAY_PAY_XRATE | NUMBER | Y |
| INTENDED_USE | VARCHAR2(120) | Y |
| INTENDED_USE_CLASSIF_ID | NUMBER | Y |
| DETAIL_TAX_DIST_ID | NUMBER | Y |
| REC_NREC_RATE | NUMBER | Y |
| RECOVERY_RATE_ID | NUMBER | Y |
| RECOVERY_RATE_NAME | VARCHAR2(600) | Y |
| RECOVERY_TYPE_CODE | VARCHAR2(120) | Y |
| RECOVERY_RATE_CODE | VARCHAR2(120) | Y |
| WITHHOLDING_TAX_CODE_ID | NUMBER | Y |
| TAX_ALREADY_DISTRIBUTED_FLAG | VARCHAR2(4) | Y |
| SUMMARY_TAX_LINE_ID | NUMBER | Y |
| TAXABLE_AMOUNT | NUMBER | Y |
| TAXABLE_BASE_AMOUNT | NUMBER | Y |
| EXTRA_PO_ERV | NUMBER | Y |
| PREPAY_TAX_DIFF_AMOUNT | NUMBER | Y |
| TAX_CODE_ID | NUMBER | Y |
| TOTAL_DIST_AMOUNT | NUMBER | Y |
| TOTAL_DIST_BASE_AMOUNT | NUMBER | Y |
| OLD_DISTRIBUTION_ID | NUMBER | Y |
| OLD_DIST_LINE_NUMBER | NUMBER | Y |
| AMOUNT_VARIANCE | NUMBER | Y |
| BASE_AMOUNT_VARIANCE | NUMBER | Y |
| HISTORICAL_FLAG | VARCHAR2(4) | Y |
| RCV_CHARGE_ADDITION_FLAG | VARCHAR2(4) | Y |
| AWT_RELATED_ID | NUMBER | Y |
| RELATED_RETAINAGE_DIST_ID | NUMBER | Y |
| RETAINED_AMOUNT_REMAINING | NUMBER | Y |
| BC_EVENT_ID | NUMBER | Y |
| RETAINED_INVOICE_DIST_ID | NUMBER | Y |
| FINAL_RELEASE_ROUNDING | NUMBER | Y |
| FULLY_PAID_ACCTD_FLAG | VARCHAR2(4) | Y |
| ROOT_DISTRIBUTION_ID | NUMBER | Y |
| XINV_PARENT_REVERSAL_ID | NUMBER | Y |
| RECURRING_PAYMENT_ID | NUMBER | Y |
| RELEASE_INV_DIST_DERIVED_FROM | NUMBER | Y |
| QUANTITY_VARIANCE | NUMBER | Y |
| BASE_QUANTITY_VARIANCE | NUMBER | Y |
| EXCHANGE_RATE_TYPE | VARCHAR2(120) | Y |
| EXCHANGE_RATE | NUMBER | Y |
| EXCHANGE_DATE | DATE | Y |
| PJC_CONTEXT_CATEGORY | VARCHAR2(160) | Y |
| PJC_PROJECT_ID | NUMBER | Y |
| PJC_TASK_ID | NUMBER | Y |
| PJC_EXPENDITURE_TYPE_ID | NUMBER | Y |
| PJC_EXPENDITURE_ITEM_DATE | DATE | Y |
| PJC_ORGANIZATION_ID | NUMBER | Y |
| PJC_BILLABLE_FLAG | VARCHAR2(4) | Y |
| PJC_CAPITALIZABLE_FLAG | VARCHAR2(4) | Y |
| PJC_WORK_TYPE_ID | NUMBER | Y |
| PJC_CONTRACT_ID | NUMBER | Y |
| PJC_CONTRACT_LINE_ID | NUMBER | Y |
| PJC_FUNDING_ALLOCATION_ID | NUMBER | Y |
| PJC_RESERVED_ATTRIBUTE1 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE2 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE3 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE4 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE5 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE6 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE7 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE8 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE9 | VARCHAR2(600) | Y |
| PJC_RESERVED_ATTRIBUTE10 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE1 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE2 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE3 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE4 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE5 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE6 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE7 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE8 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE9 | VARCHAR2(600) | Y |
| PJC_USER_DEF_ATTRIBUTE10 | VARCHAR2(600) | Y |
| ATTRIBUTE_NUMBER1 – ATTRIBUTE_NUMBER5 | NUMBER | Y |
| GLOBAL_ATTRIBUTE_NUMBER1 – GLOBAL_ATTRIBUTE_NUMBER5 | NUMBER | Y |
| GLOBAL_ATTRIBUTE_DATE1 – GLOBAL_ATTRIBUTE_DATE5 | DATE | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| JOB_DEFINITION_NAME | VARCHAR2(400) | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2(3600) | Y |
| BUDGET_DATE | DATE | Y |
| FUNDS_STATUS | VARCHAR2(120) | Y |
| CONSUMPTION_ADVICE_HEADER_ID | NUMBER | Y |
| CONSUMPTION_ADVICE_LINE_ID | NUMBER | Y |
| DATA_SET_ID | NUMBER | Y |
| DETAIL_TAX_LINE_ID | NUMBER | Y |
| PRODUCT_TABLE | VARCHAR2(120) | Y |
| REFERENCE_KEY1 | VARCHAR2(600) | Y |
| REFERENCE_KEY2 | VARCHAR2(600) | Y |
| REFERENCE_KEY3 | VARCHAR2(600) | Y |
| REFERENCE_KEY4 | VARCHAR2(600) | Y |
| REFERENCE_KEY5 | VARCHAR2(600) | Y |
| DEF_ACCTG_ACCRUAL_CCID | NUMBER | Y |
| DEF_ACCTG_START_DATE | DATE | Y |
| DEF_ACCTG_END_DATE | DATE | Y |
Indexes
| INDEX_NAME | INDEX_TYPE | UNIQUENESS | COLUMN NAME |
| AP_INVOICE_DISTRIBUTIONS_N12 | NORMAL | NONUNIQUE | PARENT_INVOICE_ID |
| AP_INVOICE_DISTRIBUTIONS_N13 | NORMAL | NONUNIQUE | PJC_PROJECT_ID |
| AP_INVOICE_DISTRIBUTIONS_N13 | NORMAL | NONUNIQUE | PJC_TASK_ID |
| AP_INVOICE_DISTRIBUTIONS_N14 | NORMAL | NONUNIQUE | PA_ADDITION_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N14 | NORMAL | NONUNIQUE | PJC_PROJECT_ID |
| AP_INVOICE_DISTRIBUTIONS_N14 | NORMAL | NONUNIQUE | REQUEST_ID |
| AP_INVOICE_DISTRIBUTIONS_N15 | NORMAL | NONUNIQUE | AWT_INVOICE_PAYMENT_ID |
| AP_INVOICE_DISTRIBUTIONS_N16 | NORMAL | NONUNIQUE | AWT_INVOICE_ID |
| AP_INVOICE_DISTRIBUTIONS_N17 | NORMAL | NONUNIQUE | RCV_TRANSACTION_ID |
| AP_INVOICE_DISTRIBUTIONS_N18 | NORMAL | NONUNIQUE | ACCOUNTING_EVENT_ID |
| AP_INVOICE_DISTRIBUTIONS_N19 | NORMAL | NONUNIQUE | INVENTORY_TRANSFER_STATUS |
| AP_INVOICE_DISTRIBUTIONS_N19 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICE_DISTRIBUTIONS_N2 | NORMAL | NONUNIQUE | POSTED_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N2 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICE_DISTRIBUTIONS_N20 | NORMAL | NONUNIQUE | PREPAY_DISTRIBUTION_ID |
| AP_INVOICE_DISTRIBUTIONS_N23 | NORMAL | NONUNIQUE | RELATED_ID |
| AP_INVOICE_DISTRIBUTIONS_N23 | NORMAL | NONUNIQUE | INVOICE_ID |
| AP_INVOICE_DISTRIBUTIONS_N23 | NORMAL | NONUNIQUE | AMOUNT |
| AP_INVOICE_DISTRIBUTIONS_N24 | NORMAL | NONUNIQUE | CORRECTED_INVOICE_DIST_ID |
| AP_INVOICE_DISTRIBUTIONS_N25 | NORMAL | NONUNIQUE | PARENT_REVERSAL_ID |
| AP_INVOICE_DISTRIBUTIONS_N28 | NORMAL | NONUNIQUE | CHARGE_APPLICABLE_TO_DIST_ID |
| AP_INVOICE_DISTRIBUTIONS_N29 | NORMAL | NONUNIQUE | DETAIL_TAX_DIST_ID |
| AP_INVOICE_DISTRIBUTIONS_N3 | NORMAL | NONUNIQUE | DIST_CODE_COMBINATION_ID |
| AP_INVOICE_DISTRIBUTIONS_N30 | NORMAL | NONUNIQUE | BC_EVENT_ID |
| AP_INVOICE_DISTRIBUTIONS_N31 | NORMAL | NONUNIQUE | ASSETS_ADDITION_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N31 | NORMAL | NONUNIQUE | POSTED_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N31 | NORMAL | NONUNIQUE | SET_OF_BOOKS_ID |
| AP_INVOICE_DISTRIBUTIONS_N31 | NORMAL | NONUNIQUE | ASSETS_TRACKING_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N31 | NORMAL | NONUNIQUE | LINE_TYPE_LOOKUP_CODE |
| AP_INVOICE_DISTRIBUTIONS_N31 | NORMAL | NONUNIQUE | ACCOUNTING_DATE |
| AP_INVOICE_DISTRIBUTIONS_N32 | NORMAL | NONUNIQUE | INVOICE_ID |
| AP_INVOICE_DISTRIBUTIONS_N32 | NORMAL | NONUNIQUE | INVOICE_LINE_NUMBER |
| AP_INVOICE_DISTRIBUTIONS_N32 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICE_DISTRIBUTIONS_N33 | NORMAL | NONUNIQUE | INVOICE_ID |
| AP_INVOICE_DISTRIBUTIONS_N33 | NORMAL | NONUNIQUE | CANCELLATION_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N33 | NORMAL | NONUNIQUE | MATCH_STATUS_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N4 | NORMAL | NONUNIQUE | ACCOUNTING_DATE |
| AP_INVOICE_DISTRIBUTIONS_N6 | NORMAL | NONUNIQUE | ASSETS_ADDITION_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N6 | NORMAL | NONUNIQUE | ASSETS_TRACKING_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N6 | NORMAL | NONUNIQUE | POSTED_FLAG |
| AP_INVOICE_DISTRIBUTIONS_N6 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICE_DISTRIBUTIONS_N7 | NORMAL | NONUNIQUE | PO_DISTRIBUTION_ID |
| AP_INVOICE_DISTRIBUTIONS_U1 | NORMAL | UNIQUE | INVOICE_ID |
| AP_INVOICE_DISTRIBUTIONS_U1 | NORMAL | UNIQUE | INVOICE_LINE_NUMBER |
| AP_INVOICE_DISTRIBUTIONS_U1 | NORMAL | UNIQUE | DISTRIBUTION_LINE_NUMBER |
| AP_INVOICE_DISTRIBUTIONS_U2 | NORMAL | UNIQUE | INVOICE_DISTRIBUTION_ID |
Related Posts
AP Invoices Table Structure
AP_invoice_Lines_all Table structure
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
From: Mohit Saxena
Author of “Oracle Fusion: Unconventional Ways to Enhance the Performance of BI Reports“