Constraints, indexes, Table Structure

PO_LINE_LOCATIONS_ALL: Table Structure and Indexes

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:

  1. Table mainly stores the shipment information of the items ordered in the purchase order.
  2. 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.
  3. 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...
https://www.amazon.in/gp/product/B093CC1CLD?ie=UTF8&tag=oraclebytes-21&camp=3638&linkCode=xm2&creativeASIN=B093CC1CLD

PO_LINE_LOCATIONS_ALL Table Structure

COLUMN_NAMEDATA_TYPENULLABLE
 LINE_LOCATION_IDNUMBER
 WORK_ORDER_SUB_TYPEVARCHAR2(120)
 WORK_ORDER_OPERATION_SEQNUMBER
 WORK_ORDER_OPERATION_IDNUMBER
 EXTERNAL_SYS_RCV_INTF_CO_SEQNUMBER
 FIRM_FLAGVARCHAR2(4)
 ORCHESTRATION_CODEVARCHAR2(80)
 MODE_OF_TRANSPORTVARCHAR2(120)
 SERVICE_LEVELVARCHAR2(120)
 REQUESTED_SHIP_DATEDATE
 PROMISED_SHIP_DATEDATE
 FUNDS_STATUSVARCHAR2(100)
 SFO_PTR_IDNUMBER
 SFO_AGREEMENT_NUMBERVARCHAR2(400)
 SFO_AGREEMENT_LINE_NUMBERNUMBER
 PO_TRADING_ORGANIZATION_IDNUMBER
 DESTINATION_TYPE_CODEVARCHAR2(100)
 AUTO_CLOSURE_MODEVARCHAR2(100)
 SCHEDULE_STATUSVARCHAR2(100)
 UNIT_OF_MEASURE_CLASSVARCHAR2(100)
 TRX_BUSINESS_CATEGORYVARCHAR2(960)
 PRODUCT_CATEGORYVARCHAR2(960)
 PRODUCT_FISC_CLASSIFICATIONVARCHAR2(960)
 LINE_INTENDED_USEVARCHAR2(960)
 PRC_BU_IDNUMBER
 REQ_BU_IDNUMBER
 PRODUCT_TYPEVARCHAR2(960)
 USER_DEFINED_FISC_CLASSVARCHAR2(120)
 ASSESSABLE_VALUENUMBER
 INPUT_TAX_CLASSIFICATION_CODEVARCHAR2(120)
 LAST_UPDATE_DATETIMESTAMP(6)
 LAST_UPDATED_BYVARCHAR2(256)
 PO_HEADER_IDNUMBER
 PO_LINE_IDNUMBER
 LAST_UPDATE_LOGINVARCHAR2(128)
 CREATION_DATETIMESTAMP(6)
 CREATED_BYVARCHAR2(256)
 QUANTITYNUMBER
 QUANTITY_RECEIVEDNUMBER
 QUANTITY_ACCEPTEDNUMBER
 QUANTITY_REJECTEDNUMBER
 QUANTITY_BILLEDNUMBER
 QUANTITY_CANCELLEDNUMBER
 UOM_CODEVARCHAR2(12)
 SHIP_TO_LOCATION_IDNUMBER
 CARRIER_IDNUMBER
 NEED_BY_DATEDATE
 PROMISED_DATEDATE
 LAST_ACCEPT_DATEDATE
 PRICE_OVERRIDENUMBER
 ENCUMBERED_FLAGVARCHAR2(4)
 ENCUMBERED_DATEDATE
 UNENCUMBERED_QUANTITYNUMBER
 FOB_LOOKUP_CODEVARCHAR2(100)
 FREIGHT_TERMS_LOOKUP_CODEVARCHAR2(100)
 TAXABLE_FLAGVARCHAR2(4)
 TAX_NAMEVARCHAR2(120)
 ESTIMATED_TAX_AMOUNTNUMBER
 FROM_HEADER_IDNUMBER
 FROM_LINE_IDNUMBER
 FROM_LINE_LOCATION_IDNUMBER
 START_DATEDATE
 END_DATEDATE
 LEAD_TIMENUMBER
 LEAD_TIME_UNITVARCHAR2(100)
 PRICE_DISCOUNTNUMBER
 TERMS_IDNUMBER
 CANCEL_FLAGVARCHAR2(4)
 CANCELLED_BYNUMBER
 CANCEL_DATEDATE
 CANCEL_REASONVARCHAR2(960)
 FIRM_STATUS_LOOKUP_CODEVARCHAR2(100)
 FIRM_DATEDATE
 FIRMED_BYNUMBER
 FIRM_REASONVARCHAR2(960)
 ATTRIBUTE_CATEGORYVARCHAR2(120)
 ATTRIBUTE1 –  ATTRIBUTE20VARCHAR2(600)
 ENCUMBER_NOWVARCHAR2(4)
 ATTRIBUTE_NUMBER1 –  ATTRIBUTE_NUMBER10NUMBER
 ATTRIBUTE_DATE1 –  ATTRIBUTE_DATE10DATE
 ATTRIBUTE_TIMESTAMP1 –  ATTRIBUTE_TIMESTAMP10TIMESTAMP(6)
 INSPECTION_REQUIRED_FLAGVARCHAR2(4)
 RECEIPT_REQUIRED_FLAGVARCHAR2(4)
 QTY_RCV_TOLERANCENUMBER
 QTY_RCV_EXCEPTION_CODEVARCHAR2(100)
 ENFORCE_SHIP_TO_LOCATION_CODEVARCHAR2(100)
 ALLOW_SUBSTITUTE_RECEIPTS_FLAGVARCHAR2(4)
 DAYS_EARLY_RECEIPT_ALLOWEDNUMBER
 DAYS_LATE_RECEIPT_ALLOWEDNUMBER
 RECEIPT_DAYS_EXCEPTION_CODEVARCHAR2(100)
 INVOICE_CLOSE_TOLERANCENUMBER
 RECEIVE_CLOSE_TOLERANCENUMBER
 SHIP_TO_ORGANIZATION_IDNUMBER
 SHIPMENT_NUMNUMBER
 SOURCE_SHIPMENT_IDNUMBER
 SHIPMENT_TYPEVARCHAR2(100)
 REQUEST_IDNUMBER
 JOB_DEFINITION_NAMEVARCHAR2(400)
 JOB_DEFINITION_PACKAGEVARCHAR2(3600)
 PROGRAM_NAMEVARCHAR2(120)
 PROGRAM_APP_NAMEVARCHAR2(200)
 GOVERNMENT_CONTEXTVARCHAR2(120)
 RECEIVING_ROUTING_IDNUMBER
 ACCRUE_ON_RECEIPT_FLAGVARCHAR2(4)
 CLOSED_REASONVARCHAR2(960)
 CLOSED_DATEDATE
 CLOSED_BYNUMBER
 SOLDTO_BU_IDNUMBER
 QUANTITY_SHIPPEDNUMBER
 GLOBAL_ATTRIBUTE_CATEGORYVARCHAR2(600)
 GLOBAL_ATTRIBUTE1 –  GLOBAL_ATTRIBUTE20VARCHAR2(600)
 COUNTRY_OF_ORIGIN_CODEVARCHAR2(8)
 TAX_USER_OVERRIDE_FLAGVARCHAR2(4)
 MATCH_OPTIONVARCHAR2(100)
 TAX_CODE_IDNUMBER
 CALCULATE_TAX_FLAGVARCHAR2(4)
 CHANGE_PROMISED_DATE_REASONVARCHAR2(960)
 NOTE_TO_RECEIVERVARCHAR2(4000)
 SECONDARY_QUANTITYNUMBER
 PREFERRED_GRADEVARCHAR2(600)
 SECONDARY_UOM_CODEVARCHAR2(12)
 SECONDARY_QUANTITY_RECEIVEDNUMBER
 SECONDARY_QUANTITY_ACCEPTEDNUMBER
 SECONDARY_QUANTITY_REJECTEDNUMBER
 SECONDARY_QUANTITY_CANCELLEDNUMBER
 VMI_FLAGVARCHAR2(4)
 CONSIGNED_FLAGVARCHAR2(4)
 RETROACTIVE_DATEDATE
 SUPPLIER_ORDER_LINE_NUMBERVARCHAR2(100)
 AMOUNTNUMBER
 AMOUNT_RECEIVEDNUMBER
 AMOUNT_BILLEDNUMBER
 AMOUNT_CANCELLEDNUMBER
 AMOUNT_REJECTEDNUMBER
 AMOUNT_ACCEPTEDNUMBER
 DROP_SHIP_FLAGVARCHAR2(4)
 SALES_ORDER_UPDATE_DATEDATE
 TRANSACTION_FLOW_HEADER_IDNUMBER
 SECONDARY_QUANTITY_SHIPPEDNUMBER
 FINAL_MATCH_FLAGVARCHAR2(4)
 MANUAL_PRICE_CHANGE_FLAGVARCHAR2(4)
 SHIPMENT_CLOSED_DATEDATE
 CLOSED_FOR_RECEIVING_DATEDATE
 CLOSED_FOR_INVOICE_DATEDATE
 VALUE_BASISVARCHAR2(100)
 MATCHING_BASISVARCHAR2(120)
 PAYMENT_TYPEVARCHAR2(120)
 GROUP_NAMEVARCHAR2(320)
 DESCRIPTIONVARCHAR2(960)
 WORK_APPROVER_IDNUMBER
 BID_PAYMENT_IDNUMBER
 QUANTITY_FINANCEDNUMBER
 AMOUNT_FINANCEDNUMBER
 QUANTITY_RECOUPEDNUMBER
 AMOUNT_RECOUPEDNUMBER
 RETAINAGE_WITHHELD_AMOUNTNUMBER
 RETAINAGE_RELEASED_AMOUNTNUMBER
 AMOUNT_SHIPPEDNUMBER
 OUTSOURCED_ASSEMBLYNUMBER
 TAX_ATTRIBUTE_UPDATE_CODEVARCHAR2(60)
 ORIGINAL_SHIPMENT_IDNUMBER
 OBJECT_VERSION_NUMBERNUMBER
 PJC_CONTEXT_CATEGORYVARCHAR2(160)
 ORIG_SCHEDULE_STATUSVARCHAR2(100)
 PRODUCT_FISC_CLASS_IDNUMBER
 LINE_INTENDED_USE_IDNUMBER
 EXTERNAL_SYS_RCV_INTF_STATUSVARCHAR2(100)
 BACK_TO_BACK_FLAGVARCHAR2(4)
 CUSTOMER_PO_NUMBERVARCHAR2(200)
 CUSTOMER_PO_LINE_NUMBERVARCHAR2(200)
 CUSTOMER_PO_SCHEDULE_NUMBERVARCHAR2(200)
 CUSTOMER_ITEMVARCHAR2(100)
 CUSTOMER_ITEM_DESCVARCHAR2(960)
 SALES_ORDER_NUMBERVARCHAR2(200)
 SALES_ORDER_LINE_NUMBERVARCHAR2(200)
 SALES_ORDER_SCHEDULE_NUMBERVARCHAR2(200)
 SHIP_TO_CUST_LOCATION_IDNUMBER
 SHIP_TO_CUST_IDNUMBER
 SHIP_TO_CUST_CONTACT_IDNUMBER
 CANCEL_BUDGET_DATE_OPTIONVARCHAR2(120)
 REINSTATE_BUDGET_DATE_OPTIONVARCHAR2(120)
 CANCEL_BUDGET_DATEDATE
 REINSTATE_BUDGET_DATEDATE
 FINAL_DISCHARGE_LOCATION_IDNUMBER
 REOPEN_FINAL_CLOSE_DATEDATE
 WORK_ORDER_IDNUMBER
 WORK_ORDER_NUMBERVARCHAR2(480)
 ANTICIPATED_ARRIVAL_DATEDATE
 DELIVERY_DATE_CONFIRMATION_REQVARCHAR2(4)
 TAX_EXCLUSIVE_PRICENUMBER
 EXTERNAL_SYS_RCV_GROUP_IDNUMBER
PO_LINE_LOCATIONS_ALL Table Structure

PO_LINE_LOCATIONS_ALL Indexes

INDEX NAMEINDEX TYPEUNIQUENESSCOLUMN NAME
 PO_LINE_LOCATIONS_F1FUNCTION-BASED NORMALNONUNIQUESYS_NC00257$
 PO_LINE_LOCATIONS_N10NORMALNONUNIQUECLOSED_DATE
 PO_LINE_LOCATIONS_N11NORMALNONUNIQUELAST_UPDATE_DATE
 PO_LINE_LOCATIONS_N12NORMALNONUNIQUEFROM_HEADER_ID
 PO_LINE_LOCATIONS_N13NORMALNONUNIQUESCHEDULE_STATUS
 PO_LINE_LOCATIONS_N14NORMALNONUNIQUESHIP_TO_ORGANIZATION_ID
 PO_LINE_LOCATIONS_N15NORMALNONUNIQUEPRC_BU_ID
 PO_LINE_LOCATIONS_N16NORMALNONUNIQUEORIGINAL_SHIPMENT_ID
 PO_LINE_LOCATIONS_N17NORMALNONUNIQUEPO_LINE_ID
 PO_LINE_LOCATIONS_N17NORMALNONUNIQUESHIPMENT_NUM
 PO_LINE_LOCATIONS_N18NORMALNONUNIQUECARRIER_ID
 PO_LINE_LOCATIONS_N19NORMALNONUNIQUESHIP_TO_CUST_ID
 PO_LINE_LOCATIONS_N2NORMALNONUNIQUEPO_HEADER_ID
 PO_LINE_LOCATIONS_N2NORMALNONUNIQUEPO_LINE_ID
 PO_LINE_LOCATIONS_N20NORMALNONUNIQUESHIP_TO_CUST_CONTACT_ID
 PO_LINE_LOCATIONS_N21NORMALNONUNIQUEEXTERNAL_SYS_RCV_GROUP_ID
 PO_LINE_LOCATIONS_N3NORMALNONUNIQUESHIP_TO_LOCATION_ID
 PO_LINE_LOCATIONS_N4NORMALNONUNIQUEPROMISED_DATE
 PO_LINE_LOCATIONS_N6NORMALNONUNIQUECLOSED_BY
 PO_LINE_LOCATIONS_N7NORMALNONUNIQUECANCELLED_BY
 PO_LINE_LOCATIONS_N9NORMALNONUNIQUEFROM_LINE_LOCATION_ID
 PO_LINE_LOCATIONS_U1NORMALUNIQUELINE_LOCATION_ID
PO_LINE_LOCATIONS_ALL Indexes

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..

How useful was this post?

Click on a star to rate it!

Average rating 4 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?