PO_LINE_LOCATIONS_ALL is the table used by Oracle to store the shipment related data of a purchase order. This table is child table for PO_Lines_All table. Let’s see the table structure and indexes of the table but before that a few salient points:
- Table mainly stores the shipment information of the items ordered in the purchase order.
- Few important columns are:
QUANTITY_RECEIVED –> Quantity received by you
QUANTITY_ACCEPTED –> Quantity which you have verified and found to be ok.
QUANTITY_BILLED –> Quantity which has been invoiced. Remember one shipment can have multiple invoices against it.
QUANTITY_CANCELLED –> Quantity which you have cancelled ( Canceled if you are in US :P)
SHIPMENT_NUM –> To keep the shipment information.
MATCH_OPTION –> Tells you how do you want to match your AP invoice against the material you received like Quantity Received or Quantity Accepted etc. - This table can be considered the parent table of PO_DISTRIBUTIONS_ALL.
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_LINE_LOCATIONS_ALL Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| LINE_LOCATION_ID | NUMBER | N |
| WORK_ORDER_SUB_TYPE | VARCHAR2(120) | Y |
| WORK_ORDER_OPERATION_SEQ | NUMBER | Y |
| WORK_ORDER_OPERATION_ID | NUMBER | Y |
| EXTERNAL_SYS_RCV_INTF_CO_SEQ | NUMBER | Y |
| FIRM_FLAG | VARCHAR2(4) | Y |
| ORCHESTRATION_CODE | VARCHAR2(80) | Y |
| MODE_OF_TRANSPORT | VARCHAR2(120) | Y |
| SERVICE_LEVEL | VARCHAR2(120) | Y |
| REQUESTED_SHIP_DATE | DATE | Y |
| PROMISED_SHIP_DATE | DATE | Y |
| FUNDS_STATUS | VARCHAR2(100) | Y |
| SFO_PTR_ID | NUMBER | Y |
| SFO_AGREEMENT_NUMBER | VARCHAR2(400) | Y |
| SFO_AGREEMENT_LINE_NUMBER | NUMBER | Y |
| PO_TRADING_ORGANIZATION_ID | NUMBER | Y |
| DESTINATION_TYPE_CODE | VARCHAR2(100) | Y |
| AUTO_CLOSURE_MODE | VARCHAR2(100) | Y |
| SCHEDULE_STATUS | VARCHAR2(100) | Y |
| UNIT_OF_MEASURE_CLASS | VARCHAR2(100) | Y |
| TRX_BUSINESS_CATEGORY | VARCHAR2(960) | Y |
| PRODUCT_CATEGORY | VARCHAR2(960) | Y |
| PRODUCT_FISC_CLASSIFICATION | VARCHAR2(960) | Y |
| LINE_INTENDED_USE | VARCHAR2(960) | Y |
| PRC_BU_ID | NUMBER | N |
| REQ_BU_ID | NUMBER | Y |
| PRODUCT_TYPE | VARCHAR2(960) | Y |
| USER_DEFINED_FISC_CLASS | VARCHAR2(120) | Y |
| ASSESSABLE_VALUE | NUMBER | Y |
| INPUT_TAX_CLASSIFICATION_CODE | VARCHAR2(120) | Y |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| PO_HEADER_ID | NUMBER | N |
| PO_LINE_ID | NUMBER | N |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| CREATION_DATE | TIMESTAMP(6) | N |
| CREATED_BY | VARCHAR2(256) | N |
| QUANTITY | NUMBER | Y |
| QUANTITY_RECEIVED | NUMBER | Y |
| QUANTITY_ACCEPTED | NUMBER | Y |
| QUANTITY_REJECTED | NUMBER | Y |
| QUANTITY_BILLED | NUMBER | Y |
| QUANTITY_CANCELLED | NUMBER | Y |
| UOM_CODE | VARCHAR2(12) | Y |
| SHIP_TO_LOCATION_ID | NUMBER | Y |
| CARRIER_ID | NUMBER | Y |
| NEED_BY_DATE | DATE | Y |
| PROMISED_DATE | DATE | Y |
| LAST_ACCEPT_DATE | DATE | Y |
| PRICE_OVERRIDE | NUMBER | Y |
| ENCUMBERED_FLAG | VARCHAR2(4) | Y |
| ENCUMBERED_DATE | DATE | Y |
| UNENCUMBERED_QUANTITY | NUMBER | Y |
| FOB_LOOKUP_CODE | VARCHAR2(100) | Y |
| FREIGHT_TERMS_LOOKUP_CODE | VARCHAR2(100) | Y |
| TAXABLE_FLAG | VARCHAR2(4) | Y |
| TAX_NAME | VARCHAR2(120) | Y |
| ESTIMATED_TAX_AMOUNT | NUMBER | Y |
| FROM_HEADER_ID | NUMBER | Y |
| FROM_LINE_ID | NUMBER | Y |
| FROM_LINE_LOCATION_ID | NUMBER | Y |
| START_DATE | DATE | Y |
| END_DATE | DATE | Y |
| LEAD_TIME | NUMBER | Y |
| LEAD_TIME_UNIT | VARCHAR2(100) | Y |
| PRICE_DISCOUNT | NUMBER | Y |
| TERMS_ID | NUMBER | Y |
| CANCEL_FLAG | VARCHAR2(4) | Y |
| CANCELLED_BY | NUMBER | Y |
| CANCEL_DATE | DATE | Y |
| CANCEL_REASON | VARCHAR2(960) | Y |
| FIRM_STATUS_LOOKUP_CODE | VARCHAR2(100) | Y |
| FIRM_DATE | DATE | Y |
| FIRMED_BY | NUMBER | Y |
| FIRM_REASON | VARCHAR2(960) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(120) | Y |
| ATTRIBUTE1 – ATTRIBUTE20 | VARCHAR2(600) | Y |
| ENCUMBER_NOW | VARCHAR2(4) | Y |
| ATTRIBUTE_NUMBER1 – ATTRIBUTE_NUMBER10 | NUMBER | Y |
| ATTRIBUTE_DATE1 – ATTRIBUTE_DATE10 | DATE | Y |
| ATTRIBUTE_TIMESTAMP1 – ATTRIBUTE_TIMESTAMP10 | TIMESTAMP(6) | Y |
| INSPECTION_REQUIRED_FLAG | VARCHAR2(4) | Y |
| RECEIPT_REQUIRED_FLAG | VARCHAR2(4) | Y |
| QTY_RCV_TOLERANCE | NUMBER | Y |
| QTY_RCV_EXCEPTION_CODE | VARCHAR2(100) | Y |
| ENFORCE_SHIP_TO_LOCATION_CODE | VARCHAR2(100) | Y |
| ALLOW_SUBSTITUTE_RECEIPTS_FLAG | VARCHAR2(4) | Y |
| DAYS_EARLY_RECEIPT_ALLOWED | NUMBER | Y |
| DAYS_LATE_RECEIPT_ALLOWED | NUMBER | Y |
| RECEIPT_DAYS_EXCEPTION_CODE | VARCHAR2(100) | Y |
| INVOICE_CLOSE_TOLERANCE | NUMBER | Y |
| RECEIVE_CLOSE_TOLERANCE | NUMBER | Y |
| SHIP_TO_ORGANIZATION_ID | NUMBER | Y |
| SHIPMENT_NUM | NUMBER | N |
| SOURCE_SHIPMENT_ID | NUMBER | Y |
| SHIPMENT_TYPE | VARCHAR2(100) | N |
| 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 |
| GOVERNMENT_CONTEXT | VARCHAR2(120) | Y |
| RECEIVING_ROUTING_ID | NUMBER | Y |
| ACCRUE_ON_RECEIPT_FLAG | VARCHAR2(4) | Y |
| CLOSED_REASON | VARCHAR2(960) | Y |
| CLOSED_DATE | DATE | Y |
| CLOSED_BY | NUMBER | Y |
| SOLDTO_BU_ID | NUMBER | Y |
| QUANTITY_SHIPPED | NUMBER | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE1 – GLOBAL_ATTRIBUTE20 | VARCHAR2(600) | Y |
| COUNTRY_OF_ORIGIN_CODE | VARCHAR2(8) | Y |
| TAX_USER_OVERRIDE_FLAG | VARCHAR2(4) | Y |
| MATCH_OPTION | VARCHAR2(100) | Y |
| TAX_CODE_ID | NUMBER | Y |
| CALCULATE_TAX_FLAG | VARCHAR2(4) | Y |
| CHANGE_PROMISED_DATE_REASON | VARCHAR2(960) | Y |
| NOTE_TO_RECEIVER | VARCHAR2(4000) | Y |
| SECONDARY_QUANTITY | NUMBER | Y |
| PREFERRED_GRADE | VARCHAR2(600) | Y |
| SECONDARY_UOM_CODE | VARCHAR2(12) | Y |
| SECONDARY_QUANTITY_RECEIVED | NUMBER | Y |
| SECONDARY_QUANTITY_ACCEPTED | NUMBER | Y |
| SECONDARY_QUANTITY_REJECTED | NUMBER | Y |
| SECONDARY_QUANTITY_CANCELLED | NUMBER | Y |
| VMI_FLAG | VARCHAR2(4) | Y |
| CONSIGNED_FLAG | VARCHAR2(4) | Y |
| RETROACTIVE_DATE | DATE | Y |
| SUPPLIER_ORDER_LINE_NUMBER | VARCHAR2(100) | Y |
| AMOUNT | NUMBER | Y |
| AMOUNT_RECEIVED | NUMBER | Y |
| AMOUNT_BILLED | NUMBER | Y |
| AMOUNT_CANCELLED | NUMBER | Y |
| AMOUNT_REJECTED | NUMBER | Y |
| AMOUNT_ACCEPTED | NUMBER | Y |
| DROP_SHIP_FLAG | VARCHAR2(4) | Y |
| SALES_ORDER_UPDATE_DATE | DATE | Y |
| TRANSACTION_FLOW_HEADER_ID | NUMBER | Y |
| SECONDARY_QUANTITY_SHIPPED | NUMBER | Y |
| FINAL_MATCH_FLAG | VARCHAR2(4) | Y |
| MANUAL_PRICE_CHANGE_FLAG | VARCHAR2(4) | Y |
| SHIPMENT_CLOSED_DATE | DATE | Y |
| CLOSED_FOR_RECEIVING_DATE | DATE | Y |
| CLOSED_FOR_INVOICE_DATE | DATE | Y |
| VALUE_BASIS | VARCHAR2(100) | Y |
| MATCHING_BASIS | VARCHAR2(120) | Y |
| PAYMENT_TYPE | VARCHAR2(120) | Y |
| GROUP_NAME | VARCHAR2(320) | Y |
| DESCRIPTION | VARCHAR2(960) | Y |
| WORK_APPROVER_ID | NUMBER | Y |
| BID_PAYMENT_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 |
| AMOUNT_SHIPPED | NUMBER | Y |
| OUTSOURCED_ASSEMBLY | NUMBER | N |
| TAX_ATTRIBUTE_UPDATE_CODE | VARCHAR2(60) | Y |
| ORIGINAL_SHIPMENT_ID | NUMBER | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| PJC_CONTEXT_CATEGORY | VARCHAR2(160) | Y |
| ORIG_SCHEDULE_STATUS | VARCHAR2(100) | Y |
| PRODUCT_FISC_CLASS_ID | NUMBER | Y |
| LINE_INTENDED_USE_ID | NUMBER | Y |
| EXTERNAL_SYS_RCV_INTF_STATUS | VARCHAR2(100) | Y |
| BACK_TO_BACK_FLAG | VARCHAR2(4) | Y |
| CUSTOMER_PO_NUMBER | VARCHAR2(200) | Y |
| CUSTOMER_PO_LINE_NUMBER | VARCHAR2(200) | Y |
| CUSTOMER_PO_SCHEDULE_NUMBER | VARCHAR2(200) | Y |
| CUSTOMER_ITEM | VARCHAR2(100) | Y |
| CUSTOMER_ITEM_DESC | VARCHAR2(960) | Y |
| SALES_ORDER_NUMBER | VARCHAR2(200) | Y |
| SALES_ORDER_LINE_NUMBER | VARCHAR2(200) | Y |
| SALES_ORDER_SCHEDULE_NUMBER | VARCHAR2(200) | Y |
| SHIP_TO_CUST_LOCATION_ID | NUMBER | Y |
| SHIP_TO_CUST_ID | NUMBER | Y |
| SHIP_TO_CUST_CONTACT_ID | NUMBER | Y |
| CANCEL_BUDGET_DATE_OPTION | VARCHAR2(120) | Y |
| REINSTATE_BUDGET_DATE_OPTION | VARCHAR2(120) | Y |
| CANCEL_BUDGET_DATE | DATE | Y |
| REINSTATE_BUDGET_DATE | DATE | Y |
| FINAL_DISCHARGE_LOCATION_ID | NUMBER | Y |
| REOPEN_FINAL_CLOSE_DATE | DATE | Y |
| WORK_ORDER_ID | NUMBER | Y |
| WORK_ORDER_NUMBER | VARCHAR2(480) | Y |
| ANTICIPATED_ARRIVAL_DATE | DATE | Y |
| DELIVERY_DATE_CONFIRMATION_REQ | VARCHAR2(4) | Y |
| TAX_EXCLUSIVE_PRICE | NUMBER | Y |
| EXTERNAL_SYS_RCV_GROUP_ID | NUMBER | Y |
PO_LINE_LOCATIONS_ALL Indexes
| INDEX NAME | INDEX TYPE | UNIQUENESS | COLUMN NAME |
| PO_LINE_LOCATIONS_F1 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00257$ |
| PO_LINE_LOCATIONS_N10 | NORMAL | NONUNIQUE | CLOSED_DATE |
| PO_LINE_LOCATIONS_N11 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| PO_LINE_LOCATIONS_N12 | NORMAL | NONUNIQUE | FROM_HEADER_ID |
| PO_LINE_LOCATIONS_N13 | NORMAL | NONUNIQUE | SCHEDULE_STATUS |
| PO_LINE_LOCATIONS_N14 | NORMAL | NONUNIQUE | SHIP_TO_ORGANIZATION_ID |
| PO_LINE_LOCATIONS_N15 | NORMAL | NONUNIQUE | PRC_BU_ID |
| PO_LINE_LOCATIONS_N16 | NORMAL | NONUNIQUE | ORIGINAL_SHIPMENT_ID |
| PO_LINE_LOCATIONS_N17 | NORMAL | NONUNIQUE | PO_LINE_ID |
| PO_LINE_LOCATIONS_N17 | NORMAL | NONUNIQUE | SHIPMENT_NUM |
| PO_LINE_LOCATIONS_N18 | NORMAL | NONUNIQUE | CARRIER_ID |
| PO_LINE_LOCATIONS_N19 | NORMAL | NONUNIQUE | SHIP_TO_CUST_ID |
| PO_LINE_LOCATIONS_N2 | NORMAL | NONUNIQUE | PO_HEADER_ID |
| PO_LINE_LOCATIONS_N2 | NORMAL | NONUNIQUE | PO_LINE_ID |
| PO_LINE_LOCATIONS_N20 | NORMAL | NONUNIQUE | SHIP_TO_CUST_CONTACT_ID |
| PO_LINE_LOCATIONS_N21 | NORMAL | NONUNIQUE | EXTERNAL_SYS_RCV_GROUP_ID |
| PO_LINE_LOCATIONS_N3 | NORMAL | NONUNIQUE | SHIP_TO_LOCATION_ID |
| PO_LINE_LOCATIONS_N4 | NORMAL | NONUNIQUE | PROMISED_DATE |
| PO_LINE_LOCATIONS_N6 | NORMAL | NONUNIQUE | CLOSED_BY |
| PO_LINE_LOCATIONS_N7 | NORMAL | NONUNIQUE | CANCELLED_BY |
| PO_LINE_LOCATIONS_N9 | NORMAL | NONUNIQUE | FROM_LINE_LOCATION_ID |
| PO_LINE_LOCATIONS_U1 | NORMAL | UNIQUE | LINE_LOCATION_ID |
Related Posts
Purchase Order Detail Query in Fusion
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_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..
