PO_LINES_ALL is the table used by Oracle Fusion to store the Purchase Order Line Data. This table is the child table of PO_HEADERS_ALL and connected by PO_HEADER_ID. In case you are looking for the query to fetch the Purchase Order Details Query, here you go. Let’s see the structure of this table along with indexes and constraints.
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_LINES_ALL Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| PO_LINE_ID | NUMBER | N |
| DISCOUNT_TYPE | VARCHAR2(100) | Y |
| DISCOUNT | NUMBER | Y |
| DISCOUNT_REASON | VARCHAR2(960) | Y |
| LIST_PRICE | NUMBER | Y |
| FUNDS_STATUS | VARCHAR2(100) | Y |
| CONFIGURED_ITEM_FLAG | VARCHAR2(4) | N |
| PARENT_ITEM_ID | NUMBER | Y |
| SUPPLIER_PARENT_ITEM | VARCHAR2(1200) | Y |
| TOP_MODEL_ID | NUMBER | Y |
| SUPPLIER_TOP_MODEL | VARCHAR2(1200) | Y |
| COMPONENT_AMOUNT_RELEASED | NUMBER | Y |
| AMOUNT_RELEASED | NUMBER | Y |
| BASE_MODEL_ID | NUMBER | Y |
| BASE_MODEL_PRICE | NUMBER | Y |
| OPTIONS_PRICE | NUMBER | Y |
| AGING_PERIOD_DAYS | NUMBER | Y |
| CONSIGNMENT_LINE_FLAG | VARCHAR2(4) | N |
| CATALOG_NAME | VARCHAR2(1020) | Y |
| LINE_STATUS | VARCHAR2(100) | Y |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| PO_HEADER_ID | NUMBER | N |
| LINE_TYPE_ID | NUMBER | N |
| LINE_NUM | NUMBER | N |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| CREATION_DATE | TIMESTAMP(6) | N |
| CREATED_BY | VARCHAR2(256) | N |
| ITEM_ID | NUMBER | Y |
| ITEM_REVISION | VARCHAR2(72) | Y |
| CATEGORY_ID | NUMBER | Y |
| ITEM_DESCRIPTION | VARCHAR2(960) | Y |
| UOM_CODE | VARCHAR2(12) | Y |
| QUANTITY_COMMITTED | NUMBER | Y |
| COMMITTED_AMOUNT | NUMBER | Y |
| ALLOW_PRICE_OVERRIDE_FLAG | VARCHAR2(4) | Y |
| NOT_TO_EXCEED_PRICE | NUMBER | Y |
| LIST_PRICE_PER_UNIT | NUMBER | Y |
| UNIT_PRICE | NUMBER | Y |
| QUANTITY | NUMBER | Y |
| TAX_EXCLUSIVE_PRICE | NUMBER | Y |
| UN_NUMBER_ID | NUMBER | Y |
| HAZARD_CLASS_ID | NUMBER | Y |
| NOTE_TO_VENDOR | VARCHAR2(4000) | Y |
| FROM_HEADER_ID | NUMBER | Y |
| FROM_LINE_ID | NUMBER | Y |
| QTY_RCV_TOLERANCE | NUMBER | Y |
| PRC_BU_ID | NUMBER | N |
| REQ_BU_ID | NUMBER | Y |
| OVER_TOLERANCE_ERROR_FLAG | VARCHAR2(100) | Y |
| MARKET_PRICE | NUMBER | Y |
| UNORDERED_FLAG | VARCHAR2(4) | Y |
| CANCEL_FLAG | VARCHAR2(4) | Y |
| CANCELLED_BY | NUMBER | Y |
| CANCEL_DATE | DATE | Y |
| CANCEL_REASON | VARCHAR2(960) | Y |
| FIRM_STATUS_LOOKUP_CODE | VARCHAR2(120) | Y |
| FIRM_DATE | DATE | Y |
| VENDOR_PRODUCT_NUM | VARCHAR2(1200) | Y |
| TAXABLE_FLAG | VARCHAR2(4) | Y |
| TAX_NAME | VARCHAR2(120) | Y |
| TYPE_1099 | VARCHAR2(40) | Y |
| CAPITAL_EXPENSE_FLAG | VARCHAR2(4) | Y |
| NEGOTIATED_BY_PREPARER_FLAG | VARCHAR2(4) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(120) | Y |
| ATTRIBUTE1- ATTRIBUTE20 | VARCHAR2(600) | Y |
| REFERENCE_NUM | VARCHAR2(100) | Y |
| ATTRIBUTE_NUMBER1 – ATTRIBUTE_NUMBER10 | NUMBER | Y |
| ATTRIBUTE_DATE1 – ATTRIBUTE_DATE10 | DATE | Y |
| ATTRIBUTE_TIMESTAMP1 – ATTRIBUTE_TIMESTAMP10 | TIMESTAMP(6) | Y |
| MIN_RELEASE_AMOUNT | NUMBER | Y |
| PRICE_TYPE_LOOKUP_CODE | VARCHAR2(100) | Y |
| PRICE_BREAK_LOOKUP_CODE | VARCHAR2(100) | Y |
| GOVERNMENT_CONTEXT | VARCHAR2(120) | 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 |
| CLOSED_DATE | DATE | Y |
| CLOSED_REASON | VARCHAR2(960) | Y |
| CLOSED_BY | NUMBER | Y |
| SOLDTO_BU_ID | NUMBER | Y |
| QC_GRADE | VARCHAR2(100) | Y |
| BASE_UOM | VARCHAR2(100) | Y |
| BASE_QTY | NUMBER | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(600) | Y |
| GLOBAL_ATTRIBUTE1 – GLOBAL_ATTRIBUTE20 | VARCHAR2(600) | Y |
| LINE_REFERENCE_NUM | VARCHAR2(100) | Y |
| EXPIRATION_DATE | DATE | Y |
| TAX_CODE_ID | NUMBER | Y |
| OKE_CONTRACT_HEADER_ID | NUMBER | Y |
| OKE_CONTRACT_VERSION_ID | NUMBER | Y |
| SECONDARY_QUANTITY | NUMBER | Y |
| SECONDARY_UOM_CODE | VARCHAR2(12) | Y |
| PREFERRED_GRADE | VARCHAR2(600) | Y |
| AUCTION_HEADER_ID | NUMBER | Y |
| AUCTION_DISPLAY_NUMBER | VARCHAR2(960) | Y |
| AUCTION_LINE_NUMBER | NUMBER | Y |
| BID_NUMBER | NUMBER | Y |
| BID_LINE_NUMBER | NUMBER | Y |
| RETROACTIVE_DATE | DATE | Y |
| SUPPLIER_REF_NUMBER | VARCHAR2(600) | Y |
| CONTRACT_ID | NUMBER | Y |
| START_DATE | DATE | Y |
| AMOUNT | NUMBER | Y |
| JOB_ID | NUMBER | Y |
| CONTRACTOR_FIRST_NAME | VARCHAR2(960) | Y |
| CONTRACTOR_LAST_NAME | VARCHAR2(960) | Y |
| FROM_LINE_LOCATION_ID | NUMBER | Y |
| ORDER_TYPE_LOOKUP_CODE | VARCHAR2(100) | N |
| PURCHASE_BASIS | VARCHAR2(120) | N |
| MATCHING_BASIS | VARCHAR2(120) | N |
| SVC_AMOUNT_NOTIF_SENT | VARCHAR2(4) | Y |
| SVC_COMPLETION_NOTIF_SENT | VARCHAR2(4) | Y |
| BASE_UNIT_PRICE | NUMBER | Y |
| MANUAL_PRICE_CHANGE_FLAG | VARCHAR2(4) | Y |
| RETAINAGE_RATE | NUMBER | Y |
| MAX_RETAINAGE_AMOUNT | NUMBER | Y |
| PROGRESS_PAYMENT_RATE | NUMBER | Y |
| RECOUPMENT_RATE | NUMBER | Y |
| SUPPLIER_PART_AUXID | VARCHAR2(1020) | Y |
| TAX_ATTRIBUTE_UPDATE_CODE | VARCHAR2(60) | Y |
| LAST_UPDATED_PROGRAM | VARCHAR2(1020) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| ALLOW_DESCRIPTION_UPDATE_FLAG | VARCHAR2(4) | Y |
| PJC_CONTEXT_CATEGORY | VARCHAR2(160) | Y |
| SOURCE_DOC_REV_NUM | NUMBER | Y |
| NEXT_CONSUMPTION_ADV_GEN_DATE | DATE | Y |
| WORK_ORDER_PRODUCT | VARCHAR2(1200) | Y |
PO_LINES_ALL Indexes
| INDEX_NAME | INDEX_TYPE | UNIQUENESS | COLUMN_NAME |
| PO_LINES_F1 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00192$ |
| PO_LINES_F2 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00197$ |
| PO_LINES_N1 | NORMAL | NONUNIQUE | ITEM_ID |
| PO_LINES_N10 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| PO_LINES_N11 | NORMAL | NONUNIQUE | CONTRACT_ID |
| PO_LINES_N13 | NORMAL | NONUNIQUE | FROM_HEADER_ID |
| PO_LINES_N14 | NORMAL | NONUNIQUE | LINE_STATUS |
| PO_LINES_N15 | NORMAL | NONUNIQUE | PRC_BU_ID |
| PO_LINES_N16 | NORMAL | NONUNIQUE | PO_HEADER_ID |
| PO_LINES_N16 | NORMAL | NONUNIQUE | LINE_NUM |
| PO_LINES_N2 | NORMAL | NONUNIQUE | CREATION_DATE |
| PO_LINES_N3 | NORMAL | NONUNIQUE | CATEGORY_ID |
| PO_LINES_N4 | NORMAL | NONUNIQUE | FROM_LINE_ID |
| PO_LINES_N5 | NORMAL | NONUNIQUE | ITEM_DESCRIPTION |
| PO_LINES_N6 | NORMAL | NONUNIQUE | VENDOR_PRODUCT_NUM |
| PO_LINES_N8 | NORMAL | NONUNIQUE | CLOSED_BY |
| PO_LINES_N9 | NORMAL | NONUNIQUE | CANCELLED_BY |
| PO_LINES_U1 | NORMAL | UNIQUE | PO_LINE_ID |
Related Posts:
PO_HEADERS_ALL
Purchase Order Query
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..
1 thought on “PO_LINES_ALL: Table Structure & Indexes”
Comments are closed.