PO_DISTRIBUTIONS_ALL is the table used by Oracle to store the distribution information of Purchase Order Line and by distribution I mean GL account information i.e. which GL accounts will be hit by the system.
Also PO_DISTRIBUTIONS_ALL is the table which in my experience connects Purchase Order with most of the other objects such as Requisition, Orders, Projects etc. So let’s discuss the table structure & indexes of the table but before that few key points:
1. This table is child table of PO_LINE_LOCATIONS_ALL table which is used to store the shipment info.
2. Quantity Ordered, Delivered, Billed and Cancelled are few important quantities you need to consider while reporting.
3. You can directly query this table based from PO_LINES_ALL table using PO_LINE_ID unless you are looking for shipment information.
4. This table is also the one which connects a PO with a requisition using REQ_DISTRIBUTION_ID
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
PO_DISTRIBUTIONS_ALL: Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| PO_DISTRIBUTION_ID | NUMBER | N |
| BUDGET_DATE | DATE | Y |
| CANCEL_BUDGET_DATE | DATE | Y |
| CLOSE_BUDGET_DATE | DATE | Y |
| FUNDS_STATUS | VARCHAR2(100) | Y |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| DIST_INTENDED_USE | VARCHAR2(960) | Y |
| PRC_BU_ID | NUMBER | N |
| REQ_BU_ID | NUMBER | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| PO_HEADER_ID | NUMBER | N |
| PO_LINE_ID | NUMBER | N |
| LINE_LOCATION_ID | NUMBER | N |
| SET_OF_BOOKS_ID | NUMBER | N |
| CODE_COMBINATION_ID | NUMBER | Y |
| QUANTITY_ORDERED | NUMBER | Y |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| CREATION_DATE | TIMESTAMP(6) | N |
| CREATED_BY | VARCHAR2(256) | N |
| QUANTITY_DELIVERED | NUMBER | Y |
| QUANTITY_BILLED | NUMBER | Y |
| QUANTITY_CANCELLED | NUMBER | Y |
| CONSIGNMENT_QUANTITY | NUMBER | Y |
| REQ_HEADER_REFERENCE_NUM | VARCHAR2(100) | Y |
| REQ_LINE_REFERENCE_NUM | VARCHAR2(100) | Y |
| REQ_DISTRIBUTION_ID | NUMBER | Y |
| DELIVER_TO_LOCATION_ID | NUMBER | Y |
| DELIVER_TO_PERSON_ID | NUMBER | Y |
| RATE_DATE | DATE | Y |
| RATE | NUMBER | Y |
| AMOUNT_BILLED | NUMBER | Y |
| ACCRUED_FLAG | VARCHAR2(4) | Y |
| ENCUMBERED_FLAG | VARCHAR2(4) | Y |
| ENCUMBERED_AMOUNT | NUMBER | Y |
| UNENCUMBERED_QUANTITY | NUMBER | Y |
| UNENCUMBERED_AMOUNT | NUMBER | Y |
| FAILED_FUNDS_LOOKUP_CODE | VARCHAR2(100) | Y |
| GL_ENCUMBERED_DATE | DATE | Y |
| GL_ENCUMBERED_PERIOD_NAME | VARCHAR2(60) | Y |
| GL_CANCELLED_DATE | DATE | Y |
| DESTINATION_TYPE_CODE | VARCHAR2(100) | Y |
| DESTINATION_ORGANIZATION_ID | NUMBER | Y |
| DESTINATION_SUBINVENTORY | VARCHAR2(40) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(120) | Y |
| ATTRIBUTE1 | VARCHAR2(600) | Y |
| ATTRIBUTE2 | VARCHAR2(600) | Y |
| ATTRIBUTE3 | VARCHAR2(600) | Y |
| ATTRIBUTE4 | VARCHAR2(600) | Y |
| ATTRIBUTE5 | VARCHAR2(600) | Y |
| ATTRIBUTE6 | VARCHAR2(600) | Y |
| ATTRIBUTE7 | VARCHAR2(600) | Y |
| ATTRIBUTE8 | VARCHAR2(600) | Y |
| ATTRIBUTE9 | VARCHAR2(600) | Y |
| ATTRIBUTE10 | VARCHAR2(600) | Y |
| ATTRIBUTE11 | VARCHAR2(600) | Y |
| ATTRIBUTE12 | VARCHAR2(600) | Y |
| ATTRIBUTE13 | VARCHAR2(600) | Y |
| ATTRIBUTE14 | VARCHAR2(600) | Y |
| ATTRIBUTE15 | VARCHAR2(600) | Y |
| ATTRIBUTE16 | VARCHAR2(600) | Y |
| ATTRIBUTE17 | VARCHAR2(600) | Y |
| ATTRIBUTE18 | VARCHAR2(600) | Y |
| ATTRIBUTE19 | VARCHAR2(600) | Y |
| ATTRIBUTE20 | VARCHAR2(600) | Y |
| ATTRIBUTE_NUMBER1 | NUMBER | Y |
| ATTRIBUTE_NUMBER2 | NUMBER | Y |
| ATTRIBUTE_NUMBER3 | NUMBER | Y |
| ATTRIBUTE_NUMBER4 | NUMBER | Y |
| ATTRIBUTE_NUMBER5 | NUMBER | Y |
| ATTRIBUTE_NUMBER6 | NUMBER | Y |
| ATTRIBUTE_NUMBER7 | NUMBER | Y |
| ATTRIBUTE_NUMBER8 | NUMBER | Y |
| ATTRIBUTE_NUMBER9 | NUMBER | Y |
| ATTRIBUTE_NUMBER10 | NUMBER | Y |
| ATTRIBUTE_DATE1 | DATE | Y |
| ATTRIBUTE_DATE2 | DATE | Y |
| ATTRIBUTE_DATE3 | DATE | Y |
| ATTRIBUTE_DATE4 | DATE | Y |
| ATTRIBUTE_DATE5 | DATE | Y |
| ATTRIBUTE_DATE6 | DATE | Y |
| ATTRIBUTE_DATE7 | DATE | Y |
| ATTRIBUTE_DATE8 | DATE | Y |
| ATTRIBUTE_DATE9 | DATE | Y |
| ATTRIBUTE_DATE10 | DATE | Y |
| ATTRIBUTE_TIMESTAMP1 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP2 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP3 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP4 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP5 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP6 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP7 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP8 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP9 | TIMESTAMP(6) | Y |
| ATTRIBUTE_TIMESTAMP10 | TIMESTAMP(6) | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE1 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE2 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE3 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE4 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE5 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE6 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE7 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE8 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE9 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE10 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE11 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE12 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE13 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE14 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE15 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE16 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE17 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE18 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE19 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE20 | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE_DATE1 | DATE | Y |
| GLOBAL_ATTRIBUTE_DATE2 | DATE | Y |
| GLOBAL_ATTRIBUTE_DATE3 | DATE | Y |
| GLOBAL_ATTRIBUTE_DATE4 | DATE | Y |
| GLOBAL_ATTRIBUTE_DATE5 | DATE | Y |
| GLOBAL_ATTRIBUTE_NUMBER1 | NUMBER | Y |
| GLOBAL_ATTRIBUTE_NUMBER2 | NUMBER | Y |
| GLOBAL_ATTRIBUTE_NUMBER3 | NUMBER | Y |
| GLOBAL_ATTRIBUTE_NUMBER4 | NUMBER | Y |
| GLOBAL_ATTRIBUTE_NUMBER5 | NUMBER | Y |
| WIP_ENTITY_ID | NUMBER | Y |
| WIP_OPERATION_SEQ_NUM | NUMBER | Y |
| WIP_RESOURCE_SEQ_NUM | NUMBER | Y |
| WIP_REPETITIVE_SCHEDULE_ID | NUMBER | Y |
| WIP_LINE_ID | NUMBER | Y |
| BOM_RESOURCE_ID | NUMBER | Y |
| BUDGET_ACCOUNT_ID | NUMBER | Y |
| ACCRUAL_ACCOUNT_ID | NUMBER | Y |
| VARIANCE_ACCOUNT_ID | NUMBER | Y |
| PREVENT_ENCUMBRANCE_FLAG | VARCHAR2(4) | Y |
| GOVERNMENT_CONTEXT | VARCHAR2(120) | Y |
| DESTINATION_CONTEXT | VARCHAR2(120) | Y |
| DISTRIBUTION_NUM | NUMBER | N |
| SOURCE_DISTRIBUTION_ID | NUMBER | Y |
| REQUEST_ID | NUMBER | Y |
| JOB_DEFINITION_NAME | VARCHAR2(400) | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2(3600) | Y |
| PROGRAM_NAME | VARCHAR2(120) | Y |
| PROGRAM_APP_NAME | VARCHAR2(200) | Y |
| GL_CLOSED_DATE | DATE | Y |
| ACCRUE_ON_RECEIPT_FLAG | VARCHAR2(4) | Y |
| SOLDTO_BU_ID | NUMBER | Y |
| KANBAN_CARD_ID | NUMBER | Y |
| AWARD_ID | NUMBER | Y |
| END_ITEM_UNIT_NUMBER | VARCHAR2(120) | Y |
| TAX_RECOVERY_OVERRIDE_FLAG | VARCHAR2(4) | Y |
| RECOVERABLE_TAX | NUMBER | Y |
| NONRECOVERABLE_TAX | NUMBER | Y |
| RECOVERABLE_INCLUSIVE_TAX | NUMBER | Y |
| NONRECOVERABLE_INCLUSIVE_TAX | NUMBER | Y |
| TAX_EXCLUSIVE_AMOUNT | NUMBER | Y |
| RECOVERY_RATE | NUMBER | Y |
| OKE_CONTRACT_LINE_ID | NUMBER | Y |
| OKE_CONTRACT_DELIVERABLE_ID | NUMBER | Y |
| AMOUNT_ORDERED | NUMBER | Y |
| AMOUNT_DELIVERED | NUMBER | Y |
| AMOUNT_CANCELLED | NUMBER | Y |
| DISTRIBUTION_TYPE | VARCHAR2(100) | Y |
| AMOUNT_TO_ENCUMBER | NUMBER | Y |
| INVOICE_ADJUSTMENT_FLAG | VARCHAR2(4) | Y |
| DEST_CHARGE_ACCOUNT_ID | NUMBER | Y |
| DEST_VARIANCE_ACCOUNT_ID | NUMBER | Y |
| QUANTITY_FINANCED | NUMBER | Y |
| AMOUNT_FINANCED | NUMBER | Y |
| QUANTITY_RECOUPED | NUMBER | Y |
| AMOUNT_RECOUPED | NUMBER | Y |
| RETAINAGE_WITHHELD_AMOUNT | NUMBER | Y |
| RETAINAGE_RELEASED_AMOUNT | NUMBER | Y |
| INVOICED_VAL_IN_NTFN | NUMBER | Y |
| TAX_ATTRIBUTE_UPDATE_CODE | VARCHAR2(60) | Y |
| INTERFACE_DISTRIBUTION_REF | VARCHAR2(960) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| PJC_PROJECT_ID | NUMBER | Y |
| PJC_TASK_ID | NUMBER | Y |
| PJC_EXPENDITURE_ITEM_DATE | DATE | Y |
| PJC_ORGANIZATION_ID | NUMBER | Y |
| PJC_EXPENDITURE_TYPE_ID | NUMBER | Y |
| PJC_CONTEXT_CATEGORY | VARCHAR2(160) | Y |
| PJC_BILLABLE_FLAG | VARCHAR2(4) | 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 |
| PJC_CONTRACT_ID | NUMBER | Y |
| PJC_WORK_TYPE_ID | NUMBER | Y |
| PJC_CAPITALIZABLE_FLAG | VARCHAR2(4) | 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_FUNDING_ALLOCATION_ID | NUMBER | Y |
| PJC_CONTRACT_LINE_ID | NUMBER | Y |
| ORIGINAL_DISTRIBUTION_ID | NUMBER | Y |
| DELIVER_TO_CUST_LOCATION_ID | NUMBER | Y |
| DELIVER_TO_CUST_ID | NUMBER | Y |
| DELIVER_TO_CUST_CONTACT_ID | NUMBER | Y |
Indexes
| INDEX_NAME | INDEX_TYPE | UNIQUENESS | COLUMN_NAME |
| PO_DISTRIBUTIONS_F1 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00214$ |
| PO_DISTRIBUTIONS_F2 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00215$ |
| PO_DISTRIBUTIONS_N1 | NORMAL | NONUNIQUE | LINE_LOCATION_ID |
| PO_DISTRIBUTIONS_N10 | NORMAL | NONUNIQUE | ORIGINAL_DISTRIBUTION_ID |
| PO_DISTRIBUTIONS_N11 | NORMAL | NONUNIQUE | PO_HEADER_ID |
| PO_DISTRIBUTIONS_N11 | NORMAL | NONUNIQUE | PO_LINE_ID |
| PO_DISTRIBUTIONS_N11 | NORMAL | NONUNIQUE | LINE_LOCATION_ID |
| PO_DISTRIBUTIONS_N11 | NORMAL | NONUNIQUE | DISTRIBUTION_NUM |
| PO_DISTRIBUTIONS_N12 | NORMAL | NONUNIQUE | DELIVER_TO_CUST_ID |
| PO_DISTRIBUTIONS_N13 | NORMAL | NONUNIQUE | DELIVER_TO_CUST_CONTACT_ID |
| PO_DISTRIBUTIONS_N2 | NORMAL | NONUNIQUE | REQ_DISTRIBUTION_ID |
| PO_DISTRIBUTIONS_N4 | NORMAL | NONUNIQUE | PO_LINE_ID |
| PO_DISTRIBUTIONS_N5 | NORMAL | NONUNIQUE | REQ_HEADER_REFERENCE_NUM |
| PO_DISTRIBUTIONS_N5 | NORMAL | NONUNIQUE | REQ_LINE_REFERENCE_NUM |
| PO_DISTRIBUTIONS_N6 | NORMAL | NONUNIQUE | DELIVER_TO_PERSON_ID |
| PO_DISTRIBUTIONS_N9 | NORMAL | NONUNIQUE | PJC_PROJECT_ID |
| PO_DISTRIBUTIONS_N9 | NORMAL | NONUNIQUE | PJC_TASK_ID |
| PO_DISTRIBUTIONS_U1 | NORMAL | UNIQUE | PO_DISTRIBUTION_ID |
Related Posts
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
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
Check out the Amazon Deals of the day based on your interests..
