VIEW: APPS.ECE_POCO_LINES_V

Source

View Text - Preformatted

SELECT POL.LINE_NUM LINE_NUM , POL.QUANTITY QUANTITY , POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE , POL.ITEM_ID ITEM_ID , POL.ITEM_REVISION ITEM_REVISION , POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER , POL.UNIT_PRICE UNIT_PRICE , POL.ITEM_DESCRIPTION ITEM_DESCRIPTION , PH2.SEGMENT1 PO_NUMBER , PH1.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER , POL.CANCEL_FLAG CANCEL_FLAG , POL.CANCEL_DATE CANCEL_DATE , POL.QUANTITY_COMMITTED QUANTITY_COMMITTED , POL.COMMITTED_AMOUNT COMMITTED_AMOUNT , POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT , POL.MARKET_PRICE MARKET_PRICE , POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE , POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG , POL.TAXABLE_FLAG TAXABLE_FLAG , POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE , POL.TYPE_1099 TYPE_1099 , PLT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE , PHCT.HAZARD_CLASS HAZARD_CLASS , PUNT.UN_NUMBER UN_NUMBER , PUNT.DESCRIPTION UN_DESCRIPTION , POL.NOTE_TO_VENDOR NOTE_TO_VENDOR , POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY , POL.ATTRIBUTE1 POL_ATTRIBUTE1 , POL.ATTRIBUTE2 POL_ATTRIBUTE2 , POL.ATTRIBUTE3 POL_ATTRIBUTE3 , POL.ATTRIBUTE4 POL_ATTRIBUTE4 , POL.ATTRIBUTE5 POL_ATTRIBUTE5 , POL.ATTRIBUTE6 POL_ATTRIBUTE6 , POL.ATTRIBUTE7 POL_ATTRIBUTE7 , POL.ATTRIBUTE8 POL_ATTRIBUTE8 , POL.ATTRIBUTE9 POL_ATTRIBUTE9 , POL.ATTRIBUTE10 POL_ATTRIBUTE10 , POL.ATTRIBUTE11 POL_ATTRIBUTE11 , POL.ATTRIBUTE12 POL_ATTRIBUTE12 , POL.ATTRIBUTE13 POL_ATTRIBUTE13 , POL.ATTRIBUTE14 POL_ATTRIBUTE14 , POL.ATTRIBUTE15 POL_ATTRIBUTE15 , MTL.ORGANIZATION_ID MTL_ORGANIZATION_ID , MTL.ATTRIBUTE_CATEGORY LP_ATTRIBUTE_CATEGORY , MTL.ATTRIBUTE1 LP_ATTRIBUTE1 , MTL.ATTRIBUTE2 LP_ATTRIBUTE2 , MTL.ATTRIBUTE3 LP_ATTRIBUTE3 , MTL.ATTRIBUTE4 LP_ATTRIBUTE4 , MTL.ATTRIBUTE5 LP_ATTRIBUTE5 , MTL.ATTRIBUTE6 LP_ATTRIBUTE6 , MTL.ATTRIBUTE7 LP_ATTRIBUTE7 , MTL.ATTRIBUTE8 LP_ATTRIBUTE8 , MTL.ATTRIBUTE9 LP_ATTRIBUTE9 , MTL.ATTRIBUTE10 LP_ATTRIBUTE10 , MTL.ATTRIBUTE11 LP_ATTRIBUTE11 , MTL.ATTRIBUTE12 LP_ATTRIBUTE12 , MTL.ATTRIBUTE13 LP_ATTRIBUTE13 , MTL.ATTRIBUTE14 LP_ATTRIBUTE14 , MTL.ATTRIBUTE15 LP_ATTRIBUTE15 , MTL.SEGMENT1 LP_SEGMENT1 , MTL.SEGMENT2 LP_SEGMENT2 , MTL.SEGMENT3 LP_SEGMENT3 , MTL.SEGMENT4 LP_SEGMENT4 , MTL.SEGMENT5 LP_SEGMENT5 , MTL.SEGMENT6 LP_SEGMENT6 , MTL.SEGMENT7 LP_SEGMENT7 , MTL.SEGMENT8 LP_SEGMENT8 , MTL.SEGMENT9 LP_SEGMENT9 , MTL.SEGMENT10 LP_SEGMENT10 , MTL.SEGMENT11 LP_SEGMENT11 , MTL.SEGMENT12 LP_SEGMENT12 , MTL.SEGMENT13 LP_SEGMENT13 , MTL.SEGMENT14 LP_SEGMENT14 , MTL.SEGMENT15 LP_SEGMENT15 , MTL.SEGMENT16 LP_SEGMENT16 , MTL.SEGMENT17 LP_SEGMENT17 , MTL.SEGMENT18 LP_SEGMENT18 , MTL.SEGMENT19 LP_SEGMENT19 , MTL.SEGMENT20 LP_SEGMENT20 , 0 POR_RELEASE_ID , 0 POR_RELEASE_NUM , TO_NUMBER(NULL) POR_SOURCE_LINE_NUM , POL.PO_HEADER_ID PO_HEADER_ID , POL.PO_LINE_ID PO_LINE_ID , TO_NUMBER(NULL) PO_LINE_LOCATION_ID , 'NON-RELEASE' SHIPMENT_TYPE , POL.EXPIRATION_DATE EXPIRATION_DATE , DECODE (PH1.GLOBAL_AGREEMENT_FLAG, 'Y', PH1.SEGMENT1 , NULL) GLOBAL_AGREEMENT_NUM , DECODE (PH1.GLOBAL_AGREEMENT_FLAG, 'Y', POL.LINE_NUM, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM , POL.SUPPLIER_REF_NUMBER SUPPLIER_CONFIG_ID , PLT.LINE_TYPE LINE_TYPE FROM MTL_SYSTEM_ITEMS_B MTL, PO_HAZARD_CLASSES_TL PHCT, PO_HAZARD_CLASSES_B PHCB, PO_UN_NUMBERS_TL PUNT, PO_UN_NUMBERS_B PUNB, PO_HEADERS_ALL PH1, PO_LINE_TYPES PLT, PO_LINES_ALL POL2, PO_LINES POL, PO_HEADERS_ALL PH2, FINANCIALS_SYSTEM_PARAMETERS FSP WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+) AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+) AND PHCT.LANGUAGE (+)= USERENV('LANG') AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+) AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+) AND PUNT.LANGUAGE (+)= USERENV('LANG') AND POL.ITEM_ID = MTL.INVENTORY_ITEM_ID AND POL2.PO_HEADER_ID = PH1.PO_HEADER_ID(+) AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+) AND MTL.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID AND POL.CONTRACT_ID=PH2.PO_HEADER_ID (+) UNION ALL SELECT POL.LINE_NUM LINE_NUM, POL.QUANTITY QUANTITY, POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE, POL.ITEM_ID ITEM_ID, POL.ITEM_REVISION ITEM_REVISION, POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER, POL.UNIT_PRICE UNIT_PRICE, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION, NULL PO_NUMBER, PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER, POL.CANCEL_FLAG CANCEL_FLAG, POL.CANCEL_DATE CANCEL_DATE, TO_NUMBER(NULL) QUANTITY_COMMITTED, TO_NUMBER(NULL) COMMITTED_AMOUNT, POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT, POL.MARKET_PRICE MARKET_PRICE, POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE, POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG, POL.TAXABLE_FLAG TAXABLE_FLAG, POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE, POL.TYPE_1099 TYPE_1099, PLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE, PHCT.HAZARD_CLASS HAZARD_CLASS, PUNT.UN_NUMBER UN_NUMBER, PUNT.DESCRIPTION UN_DESCRIPTION, POL.NOTE_TO_VENDOR NOTE_TO_VENDOR, POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY, POL.ATTRIBUTE1 POL_ATTRIBUTE1, POL.ATTRIBUTE2 POL_ATTRIBUTE2, POL.ATTRIBUTE3 POL_ATTRIBUTE3, POL.ATTRIBUTE4 POL_ATTRIBUTE4, POL.ATTRIBUTE5 POL_ATTRIBUTE5, POL.ATTRIBUTE6 POL_ATTRIBUTE6, POL.ATTRIBUTE7 POL_ATTRIBUTE7, POL.ATTRIBUTE8 POL_ATTRIBUTE8, POL.ATTRIBUTE9 POL_ATTRIBUTE9, POL.ATTRIBUTE10 POL_ATTRIBUTE10, POL.ATTRIBUTE11 POL_ATTRIBUTE11 , POL.ATTRIBUTE12 POL_ATTRIBUTE12, POL.ATTRIBUTE13 POL_ATTRIBUTE13, POL.ATTRIBUTE14 POL_ATTRIBUTE14, POL.ATTRIBUTE15 POL_ATTRIBUTE15, MTL.ORGANIZATION_ID MTL_ORGANIZATION_ID, MTL.ATTRIBUTE_CATEGORY LP_ATTRIBUTE_CATEGORY, MTL.ATTRIBUTE1 LP_ATTRIBUTE1, MTL.ATTRIBUTE2 LP_ATTRIBUTE2, MTL.ATTRIBUTE3 LP_ATTRIBUTE3, MTL.ATTRIBUTE4 LP_ATTRIBUTE4, MTL.ATTRIBUTE5 LP_ATTRIBUTE5, MTL.ATTRIBUTE6 LP_ATTRIBUTE6, MTL.ATTRIBUTE7 LP_ATTRIBUTE7, MTL.ATTRIBUTE8 LP_ATTRIBUTE8, MTL.ATTRIBUTE9 LP_ATTRIBUTE9, MTL.ATTRIBUTE10 LP_ATTRIBUTE10 , MTL.ATTRIBUTE11 LP_ATTRIBUTE11, MTL.ATTRIBUTE12 LP_ATTRIBUTE12, MTL.ATTRIBUTE13 LP_ATTRIBUTE13, MTL.ATTRIBUTE14 LP_ATTRIBUTE14, MTL.ATTRIBUTE15 LP_ATTRIBUTE15, MTL.SEGMENT1 LP_SEGMENT1, MTL.SEGMENT2 LP_SEGMENT2, MTL.SEGMENT3 LP_SEGMENT3, MTL.SEGMENT4 LP_SEGMENT4, MTL.SEGMENT5 LP_SEGMENT5, MTL.SEGMENT6 LP_SEGMENT6, MTL.SEGMENT7 LP_SEGMENT7, MTL.SEGMENT8 LP_SEGMENT8, MTL.SEGMENT9 LP_SEGMENT9, MTL.SEGMENT10 LP_SEGMENT10, MTL.SEGMENT11 LP_SEGMENT11 , MTL.SEGMENT12 LP_SEGMENT12, MTL.SEGMENT13 LP_SEGMENT13, MTL.SEGMENT14 LP_SEGMENT14, MTL.SEGMENT15 LP_SEGMENT15, MTL.SEGMENT16 LP_SEGMENT16, MTL.SEGMENT17 LP_SEGMENT17, MTL.SEGMENT18 LP_SEGMENT18, MTL.SEGMENT19 LP_SEGMENT19, MTL.SEGMENT20 LP_SEGMENT20, POR.PO_RELEASE_ID POR_RELEASE_ID, POR.RELEASE_NUM POR_RELEASE_NUM, POL.LINE_NUM POR_SOURCE_LINE_NUM, POR.PO_HEADER_ID PO_HEADER_ID, POL.PO_LINE_ID PO_LINE_ID, TO_NUMBER(NULL) PO_LINE_LOCATION_ID, NULL SHIPMENT_TYPE, POL.EXPIRATION_DATE EXPIRATION_DATE , DECODE (PH.GLOBAL_AGREEMENT_FLAG, 'Y', PH.SEGMENT1, NULL) GLOBAL_AGREEMENT_NUM , DECODE (PH.GLOBAL_AGREEMENT_FLAG, 'Y', POL2.LINE_NUM, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM , NULL SUPPLIER_CONFIG_ID , PLT.LINE_TYPE LINE_TYPE FROM MTL_SYSTEM_ITEMS_B MTL, PO_HAZARD_CLASSES_TL PHCT, PO_HAZARD_CLASSES_B PHCB, PO_UN_NUMBERS_TL PUNT, PO_UN_NUMBERS_B PUNB, PO_HEADERS_ALL PH, PO_HEADERS PH2, PO_LINE_TYPES PLT, PO_LINES_ALL POL2, PO_LINES POL, PO_RELEASES POR, FINANCIALS_SYSTEM_PARAMETERS FSP, (SELECT DISTINCT PO_LINE_ID, PO_RELEASE_ID FROM PO_LINE_LOCATIONS POLL) POLL WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+) AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+) AND PHCT.LANGUAGE (+)= USERENV('LANG') AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+) AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+) AND PUNT.LANGUAGE (+)= USERENV('LANG') AND POL.ITEM_ID = MTL.INVENTORY_ITEM_ID AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+) AND POL2.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND POL.PO_HEADER_ID = PH2.PO_HEADER_ID AND PH2.PO_HEADER_ID = POR.PO_HEADER_ID AND FSP.INVENTORY_ORGANIZATION_ID = MTL.ORGANIZATION_ID AND POLL.PO_LINE_ID = POL.PO_LINE_ID AND POLL.PO_RELEASE_ID=POR.PO_RELEASE_ID UNION ALL SELECT POL.LINE_NUM LINE_NUM , POL.QUANTITY QUANTITY , POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE , POL.ITEM_ID ITEM_ID , POL.ITEM_REVISION ITEM_REVISION , POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER , POL.UNIT_PRICE UNIT_PRICE , POL.ITEM_DESCRIPTION ITEM_DESCRIPTION , PH2.SEGMENT1 PO_NUMBER , PH1.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER , POL.CANCEL_FLAG CANCEL_FLAG , POL.CANCEL_DATE CANCEL_DATE , POL.QUANTITY_COMMITTED QUANTITY_COMMITTED , POL.COMMITTED_AMOUNT COMMITTED_AMOUNT , POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT , POL.MARKET_PRICE MARKET_PRICE , POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE , POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG , POL.TAXABLE_FLAG TAXABLE_FLAG , POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE , POL.TYPE_1099 TYPE_1099 , PLT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE , PHCT.HAZARD_CLASS HAZARD_CLASS , PUNT.UN_NUMBER UN_NUMBER , PUNT.DESCRIPTION UN_DESCRIPTION , POL.NOTE_TO_VENDOR NOTE_TO_VENDOR , POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY , POL.ATTRIBUTE1 POL_ATTRIBUTE1 , POL.ATTRIBUTE2 POL_ATTRIBUTE2 , POL.ATTRIBUTE3 POL_ATTRIBUTE3 , POL.ATTRIBUTE4 POL_ATTRIBUTE4 , POL.ATTRIBUTE5 POL_ATTRIBUTE5 , POL.ATTRIBUTE6 POL_ATTRIBUTE6 , POL.ATTRIBUTE7 POL_ATTRIBUTE7 , POL.ATTRIBUTE8 POL_ATTRIBUTE8 , POL.ATTRIBUTE9 POL_ATTRIBUTE9 , POL.ATTRIBUTE10 POL_ATTRIBUTE10 , POL.ATTRIBUTE11 POL_ATTRIBUTE11 , POL.ATTRIBUTE12 POL_ATTRIBUTE12 , POL.ATTRIBUTE13 POL_ATTRIBUTE13 , POL.ATTRIBUTE14 POL_ATTRIBUTE14 , POL.ATTRIBUTE15 POL_ATTRIBUTE15 , TO_NUMBER(NULL) MTL_ORGANIZATION_ID , NULL LP_ATTRIBUTE_CATEGORY , NULL LP_ATTRIBUTE1 , NULL LP_ATTRIBUTE2 , NULL LP_ATTRIBUTE3 , NULL LP_ATTRIBUTE4 , NULL LP_ATTRIBUTE5 , NULL LP_ATTRIBUTE6 , NULL LP_ATTRIBUTE7 , NULL LP_ATTRIBUTE8 , NULL LP_ATTRIBUTE9 , NULL LP_ATTRIBUTE10 , NULL LP_ATTRIBUTE11 , NULL LP_ATTRIBUTE12 , NULL LP_ATTRIBUTE13 , NULL LP_ATTRIBUTE14 , NULL LP_ATTRIBUTE15 , NULL LP_SEGMENT1 , NULL LP_SEGMENT2 , NULL LP_SEGMENT3 , NULL LP_SEGMENT4 , NULL LP_SEGMENT5 , NULL LP_SEGMENT6 , NULL LP_SEGMENT7 , NULL LP_SEGMENT8 , NULL LP_SEGMENT9 , NULL LP_SEGMENT10 , NULL LP_SEGMENT11 , NULL LP_SEGMENT12 , NULL LP_SEGMENT13 , NULL LP_SEGMENT14 , NULL LP_SEGMENT15 , NULL LP_SEGMENT16 , NULL LP_SEGMENT17 , NULL LP_SEGMENT18 , NULL LP_SEGMENT19 , NULL LP_SEGMENT20 , 0 POR_RELEASE_ID , 0 POR_RELEASE_NUM , TO_NUMBER(NULL) POR_SOURCE_LINE_NUM , POL.PO_HEADER_ID PO_HEADER_ID , POL.PO_LINE_ID PO_LINE_ID , TO_NUMBER(NULL) PO_LINE_LOCATION_ID , 'NON-RELEASE' SHIPMENT_TYPE , POL.EXPIRATION_DATE EXPIRATION_DATE , DECODE (PH1.GLOBAL_AGREEMENT_FLAG, 'Y', PH1.SEGMENT1 , NULL) GLOBAL_AGREEMENT_NUM , DECODE (PH1.GLOBAL_AGREEMENT_FLAG, 'Y', POL.LINE_NUM, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM , POL.SUPPLIER_REF_NUMBER SUPPLIER_CONFIG_ID , PLT.LINE_TYPE LINE_TYPE FROM PO_HAZARD_CLASSES_TL PHCT, PO_HAZARD_CLASSES_B PHCB, PO_UN_NUMBERS_TL PUNT, PO_UN_NUMBERS_B PUNB, PO_HEADERS_ALL PH1, PO_LINE_TYPES PLT, PO_LINES_ALL POL2, PO_LINES POL, PO_HEADERS_ALL PH2 WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+) AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+) AND PHCT.LANGUAGE (+)= USERENV('LANG') AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+) AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+) AND PUNT.LANGUAGE (+)= USERENV('LANG') AND POL.ITEM_ID IS NULL AND POL2.PO_HEADER_ID = PH1.PO_HEADER_ID(+) AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+) AND POL.CONTRACT_ID=PH2.PO_HEADER_ID (+) UNION ALL SELECT POL.LINE_NUM LINE_NUM, POL.QUANTITY QUANTITY, POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE, POL.ITEM_ID ITEM_ID, POL.ITEM_REVISION ITEM_REVISION, POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER, POL.UNIT_PRICE UNIT_PRICE, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION, NULL PO_NUMBER, PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER, POL.CANCEL_FLAG CANCEL_FLAG, POL.CANCEL_DATE CANCEL_DATE, TO_NUMBER(NULL) QUANTITY_COMMITTED, TO_NUMBER(NULL) COMMITTED_AMOUNT, POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT, POL.MARKET_PRICE MARKET_PRICE, POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE, POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG, POL.TAXABLE_FLAG TAXABLE_FLAG, POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE, POL.TYPE_1099 TYPE_1099, PLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE, PHCT.HAZARD_CLASS HAZARD_CLASS, PUNT.UN_NUMBER UN_NUMBER, PUNT.DESCRIPTION UN_DESCRIPTION, POL.NOTE_TO_VENDOR NOTE_TO_VENDOR, POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY, POL.ATTRIBUTE1 POL_ATTRIBUTE1, POL.ATTRIBUTE2 POL_ATTRIBUTE2, POL.ATTRIBUTE3 POL_ATTRIBUTE3, POL.ATTRIBUTE4 POL_ATTRIBUTE4, POL.ATTRIBUTE5 POL_ATTRIBUTE5, POL.ATTRIBUTE6 POL_ATTRIBUTE6, POL.ATTRIBUTE7 POL_ATTRIBUTE7, POL.ATTRIBUTE8 POL_ATTRIBUTE8, POL.ATTRIBUTE9 POL_ATTRIBUTE9, POL.ATTRIBUTE10 POL_ATTRIBUTE10, POL.ATTRIBUTE11 POL_ATTRIBUTE11 , POL.ATTRIBUTE12 POL_ATTRIBUTE12, POL.ATTRIBUTE13 POL_ATTRIBUTE13, POL.ATTRIBUTE14 POL_ATTRIBUTE14, POL.ATTRIBUTE15 POL_ATTRIBUTE15, TO_NUMBER(NULL) MTL_ORGANIZATION_ID, NULL LP_ATTRIBUTE_CATEGORY, NULL LP_ATTRIBUTE1, NULL LP_ATTRIBUTE2, NULL LP_ATTRIBUTE3, NULL LP_ATTRIBUTE4, NULL LP_ATTRIBUTE5, NULL LP_ATTRIBUTE6, NULL LP_ATTRIBUTE7, NULL LP_ATTRIBUTE8, NULL LP_ATTRIBUTE9, NULL LP_ATTRIBUTE10 , NULL LP_ATTRIBUTE11, NULL LP_ATTRIBUTE12, NULL LP_ATTRIBUTE13, NULL LP_ATTRIBUTE14, NULL LP_ATTRIBUTE15, NULL LP_SEGMENT1, NULL LP_SEGMENT2, NULL LP_SEGMENT3, NULL LP_SEGMENT4, NULL LP_SEGMENT5, NULL LP_SEGMENT6, NULL LP_SEGMENT7, NULL LP_SEGMENT8, NULL LP_SEGMENT9, NULL LP_SEGMENT10, NULL LP_SEGMENT11 , NULL LP_SEGMENT12, NULL LP_SEGMENT13, NULL LP_SEGMENT14, NULL LP_SEGMENT15, NULL LP_SEGMENT16, NULL LP_SEGMENT17, NULL LP_SEGMENT18, NULL LP_SEGMENT19, NULL LP_SEGMENT20, POR.PO_RELEASE_ID POR_RELEASE_ID, POR.RELEASE_NUM POR_RELEASE_NUM, POL.LINE_NUM POR_SOURCE_LINE_NUM, POR.PO_HEADER_ID PO_HEADER_ID, POL.PO_LINE_ID PO_LINE_ID, TO_NUMBER(NULL) PO_LINE_LOCATION_ID, NULL SHIPMENT_TYPE, POL.EXPIRATION_DATE EXPIRATION_DATE , DECODE (PH.GLOBAL_AGREEMENT_FLAG, 'Y', PH.SEGMENT1, NULL) GLOBAL_AGREEMENT_NUM , DECODE (PH.GLOBAL_AGREEMENT_FLAG, 'Y', POL2.LINE_NUM, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM , NULL SUPPLIER_CONFIG_ID , PLT.LINE_TYPE LINE_TYPE FROM PO_HAZARD_CLASSES_TL PHCT, PO_HAZARD_CLASSES_B PHCB, PO_UN_NUMBERS_TL PUNT, PO_UN_NUMBERS_B PUNB, PO_HEADERS_ALL PH, PO_HEADERS PH2, PO_LINE_TYPES PLT, PO_LINES_ALL POL2, PO_LINES POL, PO_RELEASES POR, FINANCIALS_SYSTEM_PARAMETERS FSP, (SELECT DISTINCT PO_LINE_ID, PO_RELEASE_ID FROM PO_LINE_LOCATIONS POLL) POLL WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+) AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+) AND PHCT.LANGUAGE (+)= USERENV('LANG') AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+) AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+) AND PUNT.LANGUAGE (+)= USERENV('LANG') AND POL.ITEM_ID IS NULL AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+) AND POL2.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND POL.PO_HEADER_ID = PH2.PO_HEADER_ID AND PH2.PO_HEADER_ID = POR.PO_HEADER_ID AND POLL.PO_LINE_ID = POL.PO_LINE_ID AND POLL.PO_RELEASE_ID=POR.PO_RELEASE_ID

View Text - HTML Formatted

SELECT POL.LINE_NUM LINE_NUM
, POL.QUANTITY QUANTITY
, POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE
, POL.ITEM_ID ITEM_ID
, POL.ITEM_REVISION ITEM_REVISION
, POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER
, POL.UNIT_PRICE UNIT_PRICE
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PH2.SEGMENT1 PO_NUMBER
, PH1.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER
, POL.CANCEL_FLAG CANCEL_FLAG
, POL.CANCEL_DATE CANCEL_DATE
, POL.QUANTITY_COMMITTED QUANTITY_COMMITTED
, POL.COMMITTED_AMOUNT COMMITTED_AMOUNT
, POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT
, POL.MARKET_PRICE MARKET_PRICE
, POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE
, POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG
, POL.TAXABLE_FLAG TAXABLE_FLAG
, POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE
, POL.TYPE_1099 TYPE_1099
, PLT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE
, PHCT.HAZARD_CLASS HAZARD_CLASS
, PUNT.UN_NUMBER UN_NUMBER
, PUNT.DESCRIPTION UN_DESCRIPTION
, POL.NOTE_TO_VENDOR NOTE_TO_VENDOR
, POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY
, POL.ATTRIBUTE1 POL_ATTRIBUTE1
, POL.ATTRIBUTE2 POL_ATTRIBUTE2
, POL.ATTRIBUTE3 POL_ATTRIBUTE3
, POL.ATTRIBUTE4 POL_ATTRIBUTE4
, POL.ATTRIBUTE5 POL_ATTRIBUTE5
, POL.ATTRIBUTE6 POL_ATTRIBUTE6
, POL.ATTRIBUTE7 POL_ATTRIBUTE7
, POL.ATTRIBUTE8 POL_ATTRIBUTE8
, POL.ATTRIBUTE9 POL_ATTRIBUTE9
, POL.ATTRIBUTE10 POL_ATTRIBUTE10
, POL.ATTRIBUTE11 POL_ATTRIBUTE11
, POL.ATTRIBUTE12 POL_ATTRIBUTE12
, POL.ATTRIBUTE13 POL_ATTRIBUTE13
, POL.ATTRIBUTE14 POL_ATTRIBUTE14
, POL.ATTRIBUTE15 POL_ATTRIBUTE15
, MTL.ORGANIZATION_ID MTL_ORGANIZATION_ID
, MTL.ATTRIBUTE_CATEGORY LP_ATTRIBUTE_CATEGORY
, MTL.ATTRIBUTE1 LP_ATTRIBUTE1
, MTL.ATTRIBUTE2 LP_ATTRIBUTE2
, MTL.ATTRIBUTE3 LP_ATTRIBUTE3
, MTL.ATTRIBUTE4 LP_ATTRIBUTE4
, MTL.ATTRIBUTE5 LP_ATTRIBUTE5
, MTL.ATTRIBUTE6 LP_ATTRIBUTE6
, MTL.ATTRIBUTE7 LP_ATTRIBUTE7
, MTL.ATTRIBUTE8 LP_ATTRIBUTE8
, MTL.ATTRIBUTE9 LP_ATTRIBUTE9
, MTL.ATTRIBUTE10 LP_ATTRIBUTE10
, MTL.ATTRIBUTE11 LP_ATTRIBUTE11
, MTL.ATTRIBUTE12 LP_ATTRIBUTE12
, MTL.ATTRIBUTE13 LP_ATTRIBUTE13
, MTL.ATTRIBUTE14 LP_ATTRIBUTE14
, MTL.ATTRIBUTE15 LP_ATTRIBUTE15
, MTL.SEGMENT1 LP_SEGMENT1
, MTL.SEGMENT2 LP_SEGMENT2
, MTL.SEGMENT3 LP_SEGMENT3
, MTL.SEGMENT4 LP_SEGMENT4
, MTL.SEGMENT5 LP_SEGMENT5
, MTL.SEGMENT6 LP_SEGMENT6
, MTL.SEGMENT7 LP_SEGMENT7
, MTL.SEGMENT8 LP_SEGMENT8
, MTL.SEGMENT9 LP_SEGMENT9
, MTL.SEGMENT10 LP_SEGMENT10
, MTL.SEGMENT11 LP_SEGMENT11
, MTL.SEGMENT12 LP_SEGMENT12
, MTL.SEGMENT13 LP_SEGMENT13
, MTL.SEGMENT14 LP_SEGMENT14
, MTL.SEGMENT15 LP_SEGMENT15
, MTL.SEGMENT16 LP_SEGMENT16
, MTL.SEGMENT17 LP_SEGMENT17
, MTL.SEGMENT18 LP_SEGMENT18
, MTL.SEGMENT19 LP_SEGMENT19
, MTL.SEGMENT20 LP_SEGMENT20
, 0 POR_RELEASE_ID
, 0 POR_RELEASE_NUM
, TO_NUMBER(NULL) POR_SOURCE_LINE_NUM
, POL.PO_HEADER_ID PO_HEADER_ID
, POL.PO_LINE_ID PO_LINE_ID
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, 'NON-RELEASE' SHIPMENT_TYPE
, POL.EXPIRATION_DATE EXPIRATION_DATE
, DECODE (PH1.GLOBAL_AGREEMENT_FLAG
, 'Y'
, PH1.SEGMENT1
, NULL) GLOBAL_AGREEMENT_NUM
, DECODE (PH1.GLOBAL_AGREEMENT_FLAG
, 'Y'
, POL.LINE_NUM
, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM
, POL.SUPPLIER_REF_NUMBER SUPPLIER_CONFIG_ID
, PLT.LINE_TYPE LINE_TYPE
FROM MTL_SYSTEM_ITEMS_B MTL
, PO_HAZARD_CLASSES_TL PHCT
, PO_HAZARD_CLASSES_B PHCB
, PO_UN_NUMBERS_TL PUNT
, PO_UN_NUMBERS_B PUNB
, PO_HEADERS_ALL PH1
, PO_LINE_TYPES PLT
, PO_LINES_ALL POL2
, PO_LINES POL
, PO_HEADERS_ALL PH2
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+)
AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+)
AND PHCT.LANGUAGE (+)= USERENV('LANG')
AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+)
AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+)
AND PUNT.LANGUAGE (+)= USERENV('LANG')
AND POL.ITEM_ID = MTL.INVENTORY_ITEM_ID
AND POL2.PO_HEADER_ID = PH1.PO_HEADER_ID(+)
AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+)
AND MTL.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID
AND POL.CONTRACT_ID=PH2.PO_HEADER_ID (+) UNION ALL SELECT POL.LINE_NUM LINE_NUM
, POL.QUANTITY QUANTITY
, POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE
, POL.ITEM_ID ITEM_ID
, POL.ITEM_REVISION ITEM_REVISION
, POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER
, POL.UNIT_PRICE UNIT_PRICE
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, NULL PO_NUMBER
, PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER
, POL.CANCEL_FLAG CANCEL_FLAG
, POL.CANCEL_DATE CANCEL_DATE
, TO_NUMBER(NULL) QUANTITY_COMMITTED
, TO_NUMBER(NULL) COMMITTED_AMOUNT
, POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT
, POL.MARKET_PRICE MARKET_PRICE
, POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE
, POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG
, POL.TAXABLE_FLAG TAXABLE_FLAG
, POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE
, POL.TYPE_1099 TYPE_1099
, PLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE
, PHCT.HAZARD_CLASS HAZARD_CLASS
, PUNT.UN_NUMBER UN_NUMBER
, PUNT.DESCRIPTION UN_DESCRIPTION
, POL.NOTE_TO_VENDOR NOTE_TO_VENDOR
, POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY
, POL.ATTRIBUTE1 POL_ATTRIBUTE1
, POL.ATTRIBUTE2 POL_ATTRIBUTE2
, POL.ATTRIBUTE3 POL_ATTRIBUTE3
, POL.ATTRIBUTE4 POL_ATTRIBUTE4
, POL.ATTRIBUTE5 POL_ATTRIBUTE5
, POL.ATTRIBUTE6 POL_ATTRIBUTE6
, POL.ATTRIBUTE7 POL_ATTRIBUTE7
, POL.ATTRIBUTE8 POL_ATTRIBUTE8
, POL.ATTRIBUTE9 POL_ATTRIBUTE9
, POL.ATTRIBUTE10 POL_ATTRIBUTE10
, POL.ATTRIBUTE11 POL_ATTRIBUTE11
, POL.ATTRIBUTE12 POL_ATTRIBUTE12
, POL.ATTRIBUTE13 POL_ATTRIBUTE13
, POL.ATTRIBUTE14 POL_ATTRIBUTE14
, POL.ATTRIBUTE15 POL_ATTRIBUTE15
, MTL.ORGANIZATION_ID MTL_ORGANIZATION_ID
, MTL.ATTRIBUTE_CATEGORY LP_ATTRIBUTE_CATEGORY
, MTL.ATTRIBUTE1 LP_ATTRIBUTE1
, MTL.ATTRIBUTE2 LP_ATTRIBUTE2
, MTL.ATTRIBUTE3 LP_ATTRIBUTE3
, MTL.ATTRIBUTE4 LP_ATTRIBUTE4
, MTL.ATTRIBUTE5 LP_ATTRIBUTE5
, MTL.ATTRIBUTE6 LP_ATTRIBUTE6
, MTL.ATTRIBUTE7 LP_ATTRIBUTE7
, MTL.ATTRIBUTE8 LP_ATTRIBUTE8
, MTL.ATTRIBUTE9 LP_ATTRIBUTE9
, MTL.ATTRIBUTE10 LP_ATTRIBUTE10
, MTL.ATTRIBUTE11 LP_ATTRIBUTE11
, MTL.ATTRIBUTE12 LP_ATTRIBUTE12
, MTL.ATTRIBUTE13 LP_ATTRIBUTE13
, MTL.ATTRIBUTE14 LP_ATTRIBUTE14
, MTL.ATTRIBUTE15 LP_ATTRIBUTE15
, MTL.SEGMENT1 LP_SEGMENT1
, MTL.SEGMENT2 LP_SEGMENT2
, MTL.SEGMENT3 LP_SEGMENT3
, MTL.SEGMENT4 LP_SEGMENT4
, MTL.SEGMENT5 LP_SEGMENT5
, MTL.SEGMENT6 LP_SEGMENT6
, MTL.SEGMENT7 LP_SEGMENT7
, MTL.SEGMENT8 LP_SEGMENT8
, MTL.SEGMENT9 LP_SEGMENT9
, MTL.SEGMENT10 LP_SEGMENT10
, MTL.SEGMENT11 LP_SEGMENT11
, MTL.SEGMENT12 LP_SEGMENT12
, MTL.SEGMENT13 LP_SEGMENT13
, MTL.SEGMENT14 LP_SEGMENT14
, MTL.SEGMENT15 LP_SEGMENT15
, MTL.SEGMENT16 LP_SEGMENT16
, MTL.SEGMENT17 LP_SEGMENT17
, MTL.SEGMENT18 LP_SEGMENT18
, MTL.SEGMENT19 LP_SEGMENT19
, MTL.SEGMENT20 LP_SEGMENT20
, POR.PO_RELEASE_ID POR_RELEASE_ID
, POR.RELEASE_NUM POR_RELEASE_NUM
, POL.LINE_NUM POR_SOURCE_LINE_NUM
, POR.PO_HEADER_ID PO_HEADER_ID
, POL.PO_LINE_ID PO_LINE_ID
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, NULL SHIPMENT_TYPE
, POL.EXPIRATION_DATE EXPIRATION_DATE
, DECODE (PH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, PH.SEGMENT1
, NULL) GLOBAL_AGREEMENT_NUM
, DECODE (PH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, POL2.LINE_NUM
, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM
, NULL SUPPLIER_CONFIG_ID
, PLT.LINE_TYPE LINE_TYPE
FROM MTL_SYSTEM_ITEMS_B MTL
, PO_HAZARD_CLASSES_TL PHCT
, PO_HAZARD_CLASSES_B PHCB
, PO_UN_NUMBERS_TL PUNT
, PO_UN_NUMBERS_B PUNB
, PO_HEADERS_ALL PH
, PO_HEADERS PH2
, PO_LINE_TYPES PLT
, PO_LINES_ALL POL2
, PO_LINES POL
, PO_RELEASES POR
, FINANCIALS_SYSTEM_PARAMETERS FSP
, (SELECT DISTINCT PO_LINE_ID
, PO_RELEASE_ID
FROM PO_LINE_LOCATIONS POLL) POLL
WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+)
AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+)
AND PHCT.LANGUAGE (+)= USERENV('LANG')
AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+)
AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+)
AND PUNT.LANGUAGE (+)= USERENV('LANG')
AND POL.ITEM_ID = MTL.INVENTORY_ITEM_ID
AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+)
AND POL2.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND POL.PO_HEADER_ID = PH2.PO_HEADER_ID
AND PH2.PO_HEADER_ID = POR.PO_HEADER_ID
AND FSP.INVENTORY_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.PO_RELEASE_ID=POR.PO_RELEASE_ID UNION ALL SELECT POL.LINE_NUM LINE_NUM
, POL.QUANTITY QUANTITY
, POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE
, POL.ITEM_ID ITEM_ID
, POL.ITEM_REVISION ITEM_REVISION
, POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER
, POL.UNIT_PRICE UNIT_PRICE
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PH2.SEGMENT1 PO_NUMBER
, PH1.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER
, POL.CANCEL_FLAG CANCEL_FLAG
, POL.CANCEL_DATE CANCEL_DATE
, POL.QUANTITY_COMMITTED QUANTITY_COMMITTED
, POL.COMMITTED_AMOUNT COMMITTED_AMOUNT
, POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT
, POL.MARKET_PRICE MARKET_PRICE
, POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE
, POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG
, POL.TAXABLE_FLAG TAXABLE_FLAG
, POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE
, POL.TYPE_1099 TYPE_1099
, PLT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE
, PHCT.HAZARD_CLASS HAZARD_CLASS
, PUNT.UN_NUMBER UN_NUMBER
, PUNT.DESCRIPTION UN_DESCRIPTION
, POL.NOTE_TO_VENDOR NOTE_TO_VENDOR
, POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY
, POL.ATTRIBUTE1 POL_ATTRIBUTE1
, POL.ATTRIBUTE2 POL_ATTRIBUTE2
, POL.ATTRIBUTE3 POL_ATTRIBUTE3
, POL.ATTRIBUTE4 POL_ATTRIBUTE4
, POL.ATTRIBUTE5 POL_ATTRIBUTE5
, POL.ATTRIBUTE6 POL_ATTRIBUTE6
, POL.ATTRIBUTE7 POL_ATTRIBUTE7
, POL.ATTRIBUTE8 POL_ATTRIBUTE8
, POL.ATTRIBUTE9 POL_ATTRIBUTE9
, POL.ATTRIBUTE10 POL_ATTRIBUTE10
, POL.ATTRIBUTE11 POL_ATTRIBUTE11
, POL.ATTRIBUTE12 POL_ATTRIBUTE12
, POL.ATTRIBUTE13 POL_ATTRIBUTE13
, POL.ATTRIBUTE14 POL_ATTRIBUTE14
, POL.ATTRIBUTE15 POL_ATTRIBUTE15
, TO_NUMBER(NULL) MTL_ORGANIZATION_ID
, NULL LP_ATTRIBUTE_CATEGORY
, NULL LP_ATTRIBUTE1
, NULL LP_ATTRIBUTE2
, NULL LP_ATTRIBUTE3
, NULL LP_ATTRIBUTE4
, NULL LP_ATTRIBUTE5
, NULL LP_ATTRIBUTE6
, NULL LP_ATTRIBUTE7
, NULL LP_ATTRIBUTE8
, NULL LP_ATTRIBUTE9
, NULL LP_ATTRIBUTE10
, NULL LP_ATTRIBUTE11
, NULL LP_ATTRIBUTE12
, NULL LP_ATTRIBUTE13
, NULL LP_ATTRIBUTE14
, NULL LP_ATTRIBUTE15
, NULL LP_SEGMENT1
, NULL LP_SEGMENT2
, NULL LP_SEGMENT3
, NULL LP_SEGMENT4
, NULL LP_SEGMENT5
, NULL LP_SEGMENT6
, NULL LP_SEGMENT7
, NULL LP_SEGMENT8
, NULL LP_SEGMENT9
, NULL LP_SEGMENT10
, NULL LP_SEGMENT11
, NULL LP_SEGMENT12
, NULL LP_SEGMENT13
, NULL LP_SEGMENT14
, NULL LP_SEGMENT15
, NULL LP_SEGMENT16
, NULL LP_SEGMENT17
, NULL LP_SEGMENT18
, NULL LP_SEGMENT19
, NULL LP_SEGMENT20
, 0 POR_RELEASE_ID
, 0 POR_RELEASE_NUM
, TO_NUMBER(NULL) POR_SOURCE_LINE_NUM
, POL.PO_HEADER_ID PO_HEADER_ID
, POL.PO_LINE_ID PO_LINE_ID
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, 'NON-RELEASE' SHIPMENT_TYPE
, POL.EXPIRATION_DATE EXPIRATION_DATE
, DECODE (PH1.GLOBAL_AGREEMENT_FLAG
, 'Y'
, PH1.SEGMENT1
, NULL) GLOBAL_AGREEMENT_NUM
, DECODE (PH1.GLOBAL_AGREEMENT_FLAG
, 'Y'
, POL.LINE_NUM
, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM
, POL.SUPPLIER_REF_NUMBER SUPPLIER_CONFIG_ID
, PLT.LINE_TYPE LINE_TYPE
FROM PO_HAZARD_CLASSES_TL PHCT
, PO_HAZARD_CLASSES_B PHCB
, PO_UN_NUMBERS_TL PUNT
, PO_UN_NUMBERS_B PUNB
, PO_HEADERS_ALL PH1
, PO_LINE_TYPES PLT
, PO_LINES_ALL POL2
, PO_LINES POL
, PO_HEADERS_ALL PH2
WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+)
AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+)
AND PHCT.LANGUAGE (+)= USERENV('LANG')
AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+)
AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+)
AND PUNT.LANGUAGE (+)= USERENV('LANG')
AND POL.ITEM_ID IS NULL
AND POL2.PO_HEADER_ID = PH1.PO_HEADER_ID(+)
AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+)
AND POL.CONTRACT_ID=PH2.PO_HEADER_ID (+) UNION ALL SELECT POL.LINE_NUM LINE_NUM
, POL.QUANTITY QUANTITY
, POL.UNIT_MEAS_LOOKUP_CODE UOM_CODE
, POL.ITEM_ID ITEM_ID
, POL.ITEM_REVISION ITEM_REVISION
, POL.VENDOR_PRODUCT_NUM VENDOR_PRODUCT_NUMBER
, POL.UNIT_PRICE UNIT_PRICE
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, NULL PO_NUMBER
, PH.QUOTE_VENDOR_QUOTE_NUMBER VENDOR_QUOTE_NUMBER
, POL.CANCEL_FLAG CANCEL_FLAG
, POL.CANCEL_DATE CANCEL_DATE
, TO_NUMBER(NULL) QUANTITY_COMMITTED
, TO_NUMBER(NULL) COMMITTED_AMOUNT
, POL.LIST_PRICE_PER_UNIT LIST_PRICE_PER_UNIT
, POL.MARKET_PRICE MARKET_PRICE
, POL.NOT_TO_EXCEED_PRICE NOT_TO_EXCEED_PRICE
, POL.NEGOTIATED_BY_PREPARER_FLAG NEGOTIATED_BY_PREPARER_FLAG
, POL.TAXABLE_FLAG TAXABLE_FLAG
, POL.TRANSACTION_REASON_CODE TRANSACTION_REASON_CODE
, POL.TYPE_1099 TYPE_1099
, PLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE
, PHCT.HAZARD_CLASS HAZARD_CLASS
, PUNT.UN_NUMBER UN_NUMBER
, PUNT.DESCRIPTION UN_DESCRIPTION
, POL.NOTE_TO_VENDOR NOTE_TO_VENDOR
, POL.ATTRIBUTE_CATEGORY POL_ATTRIBUTE_CATEGORY
, POL.ATTRIBUTE1 POL_ATTRIBUTE1
, POL.ATTRIBUTE2 POL_ATTRIBUTE2
, POL.ATTRIBUTE3 POL_ATTRIBUTE3
, POL.ATTRIBUTE4 POL_ATTRIBUTE4
, POL.ATTRIBUTE5 POL_ATTRIBUTE5
, POL.ATTRIBUTE6 POL_ATTRIBUTE6
, POL.ATTRIBUTE7 POL_ATTRIBUTE7
, POL.ATTRIBUTE8 POL_ATTRIBUTE8
, POL.ATTRIBUTE9 POL_ATTRIBUTE9
, POL.ATTRIBUTE10 POL_ATTRIBUTE10
, POL.ATTRIBUTE11 POL_ATTRIBUTE11
, POL.ATTRIBUTE12 POL_ATTRIBUTE12
, POL.ATTRIBUTE13 POL_ATTRIBUTE13
, POL.ATTRIBUTE14 POL_ATTRIBUTE14
, POL.ATTRIBUTE15 POL_ATTRIBUTE15
, TO_NUMBER(NULL) MTL_ORGANIZATION_ID
, NULL LP_ATTRIBUTE_CATEGORY
, NULL LP_ATTRIBUTE1
, NULL LP_ATTRIBUTE2
, NULL LP_ATTRIBUTE3
, NULL LP_ATTRIBUTE4
, NULL LP_ATTRIBUTE5
, NULL LP_ATTRIBUTE6
, NULL LP_ATTRIBUTE7
, NULL LP_ATTRIBUTE8
, NULL LP_ATTRIBUTE9
, NULL LP_ATTRIBUTE10
, NULL LP_ATTRIBUTE11
, NULL LP_ATTRIBUTE12
, NULL LP_ATTRIBUTE13
, NULL LP_ATTRIBUTE14
, NULL LP_ATTRIBUTE15
, NULL LP_SEGMENT1
, NULL LP_SEGMENT2
, NULL LP_SEGMENT3
, NULL LP_SEGMENT4
, NULL LP_SEGMENT5
, NULL LP_SEGMENT6
, NULL LP_SEGMENT7
, NULL LP_SEGMENT8
, NULL LP_SEGMENT9
, NULL LP_SEGMENT10
, NULL LP_SEGMENT11
, NULL LP_SEGMENT12
, NULL LP_SEGMENT13
, NULL LP_SEGMENT14
, NULL LP_SEGMENT15
, NULL LP_SEGMENT16
, NULL LP_SEGMENT17
, NULL LP_SEGMENT18
, NULL LP_SEGMENT19
, NULL LP_SEGMENT20
, POR.PO_RELEASE_ID POR_RELEASE_ID
, POR.RELEASE_NUM POR_RELEASE_NUM
, POL.LINE_NUM POR_SOURCE_LINE_NUM
, POR.PO_HEADER_ID PO_HEADER_ID
, POL.PO_LINE_ID PO_LINE_ID
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, NULL SHIPMENT_TYPE
, POL.EXPIRATION_DATE EXPIRATION_DATE
, DECODE (PH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, PH.SEGMENT1
, NULL) GLOBAL_AGREEMENT_NUM
, DECODE (PH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, POL2.LINE_NUM
, TO_NUMBER(NULL)) GLOBAL_AGREEMENT_LINE_NUM
, NULL SUPPLIER_CONFIG_ID
, PLT.LINE_TYPE LINE_TYPE
FROM PO_HAZARD_CLASSES_TL PHCT
, PO_HAZARD_CLASSES_B PHCB
, PO_UN_NUMBERS_TL PUNT
, PO_UN_NUMBERS_B PUNB
, PO_HEADERS_ALL PH
, PO_HEADERS PH2
, PO_LINE_TYPES PLT
, PO_LINES_ALL POL2
, PO_LINES POL
, PO_RELEASES POR
, FINANCIALS_SYSTEM_PARAMETERS FSP
, (SELECT DISTINCT PO_LINE_ID
, PO_RELEASE_ID
FROM PO_LINE_LOCATIONS POLL) POLL
WHERE POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID (+)
AND PHCB.HAZARD_CLASS_ID = PHCT.HAZARD_CLASS_ID (+)
AND PHCT.LANGUAGE (+)= USERENV('LANG')
AND POL.UN_NUMBER_ID = PUNB.UN_NUMBER_ID (+)
AND PUNB.UN_NUMBER_ID = PUNT.UN_NUMBER_ID (+)
AND PUNT.LANGUAGE (+)= USERENV('LANG')
AND POL.ITEM_ID IS NULL
AND POL.FROM_LINE_ID = POL2.PO_LINE_ID (+)
AND POL2.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND POL.PO_HEADER_ID = PH2.PO_HEADER_ID
AND PH2.PO_HEADER_ID = POR.PO_HEADER_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.PO_RELEASE_ID=POR.PO_RELEASE_ID