VIEW: APPS.PO_CXML_LINE_LOC_ARCH_V

Source

View Text - Preformatted

SELECT PLLA.PO_HEADER_ID PO_HEADER_ID , PLLA.REVISION_NUM REVISION_NUM , PLLA.PO_RELEASE_ID , PLLA.PO_LINE_ID PO_LINE_ID , PLA.LINE_NUM LINE_NUM , PLLA.LINE_LOCATION_ID LINE_LOCATION_ID , PLLA.SHIPMENT_NUM SHIPMENT_NUM , ( PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED ) ORDERED_QUANTITY , MUOMTL.UOM_CODE UOM , PLLA.NEED_BY_DATE NEED_BY_DATE , HOU_V.NAME SHIPTO_ORG_NAME , PLLA.ORG_ID PARTNER_ID , FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY , PLLA.PROMISED_DATE PROMISED_DATE , PLLA.LAST_ACCEPT_DATE LAST_ACCEPT_DATE , PLLA.PRICE_OVERRIDE PRICE_OVERRIDE , PLLA.TAXABLE_FLAG TAXABLE_FLAG , PLLA.TAX_CODE_ID TAX_CODE_ID , PLLA. ESTIMATED_TAX_AMOUNT , PLLA.TAX_NAME TAX_NAME , PLLA.SHIP_TO_LOCATION_ID , PLA.FROM_HEADER_ID, PLA.FROM_LINE_ID, PLA.CONTRACT_ID, PLA.ITEM_ID, FSP.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID, PLA.ITEM_REVISION ITEMRV , PLA.ITEM_REVISION ITEMRVX , PLA.VENDOR_PRODUCT_NUM ITEMX , MSIV.SEGMENT1 ITEM , PLA.ITEM_DESCRIPTION DESCRIPTION , PHC.HAZARD_CLASS HAZRDMTL , PLA.NOTE_TO_VENDOR NOTE_TO_VENDOR , 'LINENUM' , 'SHIPMENTNUM' , 'BUYERPARTNUM' , PLA.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA , PO_LINES_ALL PLA , FINANCIALS_SYSTEM_PARAMS_ALL FSPA , HR_ORGANIZATION_UNITS_V HOU_V , MTL_UNITS_OF_MEASURE_TL MUOMTL , MTL_SYSTEM_ITEMS_B_KFV MSIV , FINANCIALS_SYSTEM_PARAMS_ALL FSP , PO_HAZARD_CLASSES PHC , PO_LINE_TYPES_B PLTB WHERE NVL ( MSIV.ORGANIZATION_ID , FSP.INVENTORY_ORGANIZATION_ID ) = FSP.INVENTORY_ORGANIZATION_ID AND FSP.ORG_ID = PLA.ORG_ID AND MSIV.INVENTORY_ITEM_ID ( + ) = PLA.ITEM_ID AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID AND ( PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED ) > 0 AND FSPA.ORG_ID = PLLA.ORG_ID AND HOU_V.ORGANIZATION_ID = PLLA.ORG_ID AND DECODE ( PLLA.UNIT_MEAS_LOOKUP_CODE , NULL , PLA.UNIT_MEAS_LOOKUP_CODE , PLLA.UNIT_MEAS_LOOKUP_CODE ) = MUOMTL.UNIT_OF_MEASURE AND MUOMTL.LANGUAGE = USERENV ( 'LANG' ) AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID ( + ) AND pla.LINE_TYPE_ID = pltb.LINE_TYPE_ID AND pla.ORDER_TYPE_LOOKUP_CODE <> 'FIXED PRICE' UNION ALL SELECT PLLA.PO_HEADER_ID PO_HEADER_ID , PLLA.REVISION_NUM REVISION_NUM , PLLA.PO_RELEASE_ID , PLLA.PO_LINE_ID PO_LINE_ID , PLA.LINE_NUM LINE_NUM , PLLA.LINE_LOCATION_ID LINE_LOCATION_ID , PLLA.SHIPMENT_NUM SHIPMENT_NUM , NULL ORDERED_QUANTITY , NULL UOM , PLLA.NEED_BY_DATE NEED_BY_DATE , HOU_V.NAME SHIPTO_ORG_NAME , PLLA.ORG_ID PARTNER_ID , FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY , PLLA.PROMISED_DATE PROMISED_DATE , PLLA.LAST_ACCEPT_DATE LAST_ACCEPT_DATE , PLLA.PRICE_OVERRIDE PRICE_OVERRIDE , PLLA.TAXABLE_FLAG TAXABLE_FLAG , PLLA.TAX_CODE_ID TAX_CODE_ID , PLLA.ESTIMATED_TAX_AMOUNT , PLLA.TAX_NAME TAX_NAME , PLLA.SHIP_TO_LOCATION_ID , PLA.FROM_HEADER_ID, PLA.FROM_LINE_ID, PLA.CONTRACT_ID, PLA.ITEM_ID, FSP.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID, PLA.ITEM_REVISION ITEMRV , PLA.ITEM_REVISION ITEMRVX , PLA.VENDOR_PRODUCT_NUM ITEMX , MSIV.SEGMENT1 ITEM , PLA.ITEM_DESCRIPTION DESCRIPTION , PHC.HAZARD_CLASS HAZRDMTL , PLA.NOTE_TO_VENDOR NOTE_TO_VENDOR , 'LINENUM' , 'SHIPMENTNUM' , 'BUYERPARTNUM' , PLA.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA , PO_LINES_ALL PLA , FINANCIALS_SYSTEM_PARAMS_ALL FSPA , HR_ORGANIZATION_UNITS_V HOU_V , MTL_SYSTEM_ITEMS_B_KFV MSIV , FINANCIALS_SYSTEM_PARAMS_ALL FSP , PO_HAZARD_CLASSES PHC , po_line_types_b pltb WHERE NVL ( MSIV.ORGANIZATION_ID , FSP.INVENTORY_ORGANIZATION_ID ) = FSP.INVENTORY_ORGANIZATION_ID AND FSP.ORG_ID = PLA.ORG_ID AND MSIV.INVENTORY_ITEM_ID ( + ) = PLA.ITEM_ID AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID AND FSPA.ORG_ID = PLLA.ORG_ID AND HOU_V.ORGANIZATION_ID = PLLA.ORG_ID AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID ( + ) AND pla.LINE_TYPE_ID = pltb.LINE_TYPE_ID AND pla.ORDER_TYPE_LOOKUP_CODE = 'FIXED PRICE' ORDER BY PO_HEADER_ID , PO_LINE_ID , LINE_LOCATION_ID

View Text - HTML Formatted

SELECT PLLA.PO_HEADER_ID PO_HEADER_ID
, PLLA.REVISION_NUM REVISION_NUM
, PLLA.PO_RELEASE_ID
, PLLA.PO_LINE_ID PO_LINE_ID
, PLA.LINE_NUM LINE_NUM
, PLLA.LINE_LOCATION_ID LINE_LOCATION_ID
, PLLA.SHIPMENT_NUM SHIPMENT_NUM
, ( PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED ) ORDERED_QUANTITY
, MUOMTL.UOM_CODE UOM
, PLLA.NEED_BY_DATE NEED_BY_DATE
, HOU_V.NAME SHIPTO_ORG_NAME
, PLLA.ORG_ID PARTNER_ID
, FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY
, PLLA.PROMISED_DATE PROMISED_DATE
, PLLA.LAST_ACCEPT_DATE LAST_ACCEPT_DATE
, PLLA.PRICE_OVERRIDE PRICE_OVERRIDE
, PLLA.TAXABLE_FLAG TAXABLE_FLAG
, PLLA.TAX_CODE_ID TAX_CODE_ID
, PLLA. ESTIMATED_TAX_AMOUNT
, PLLA.TAX_NAME TAX_NAME
, PLLA.SHIP_TO_LOCATION_ID
, PLA.FROM_HEADER_ID
, PLA.FROM_LINE_ID
, PLA.CONTRACT_ID
, PLA.ITEM_ID
, FSP.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID
, PLA.ITEM_REVISION ITEMRV
, PLA.ITEM_REVISION ITEMRVX
, PLA.VENDOR_PRODUCT_NUM ITEMX
, MSIV.SEGMENT1 ITEM
, PLA.ITEM_DESCRIPTION DESCRIPTION
, PHC.HAZARD_CLASS HAZRDMTL
, PLA.NOTE_TO_VENDOR NOTE_TO_VENDOR
, 'LINENUM'
, 'SHIPMENTNUM'
, 'BUYERPARTNUM'
, PLA.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
, PO_LINES_ALL PLA
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, HR_ORGANIZATION_UNITS_V HOU_V
, MTL_UNITS_OF_MEASURE_TL MUOMTL
, MTL_SYSTEM_ITEMS_B_KFV MSIV
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_HAZARD_CLASSES PHC
, PO_LINE_TYPES_B PLTB
WHERE NVL ( MSIV.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID ) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
AND MSIV.INVENTORY_ITEM_ID ( + ) = PLA.ITEM_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND ( PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED ) > 0
AND FSPA.ORG_ID = PLLA.ORG_ID
AND HOU_V.ORGANIZATION_ID = PLLA.ORG_ID
AND DECODE ( PLLA.UNIT_MEAS_LOOKUP_CODE
, NULL
, PLA.UNIT_MEAS_LOOKUP_CODE
, PLLA.UNIT_MEAS_LOOKUP_CODE ) = MUOMTL.UNIT_OF_MEASURE
AND MUOMTL.LANGUAGE = USERENV ( 'LANG' )
AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID ( + )
AND PLA.LINE_TYPE_ID = PLTB.LINE_TYPE_ID
AND PLA.ORDER_TYPE_LOOKUP_CODE <> 'FIXED PRICE' UNION ALL SELECT PLLA.PO_HEADER_ID PO_HEADER_ID
, PLLA.REVISION_NUM REVISION_NUM
, PLLA.PO_RELEASE_ID
, PLLA.PO_LINE_ID PO_LINE_ID
, PLA.LINE_NUM LINE_NUM
, PLLA.LINE_LOCATION_ID LINE_LOCATION_ID
, PLLA.SHIPMENT_NUM SHIPMENT_NUM
, NULL ORDERED_QUANTITY
, NULL UOM
, PLLA.NEED_BY_DATE NEED_BY_DATE
, HOU_V.NAME SHIPTO_ORG_NAME
, PLLA.ORG_ID PARTNER_ID
, FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY
, PLLA.PROMISED_DATE PROMISED_DATE
, PLLA.LAST_ACCEPT_DATE LAST_ACCEPT_DATE
, PLLA.PRICE_OVERRIDE PRICE_OVERRIDE
, PLLA.TAXABLE_FLAG TAXABLE_FLAG
, PLLA.TAX_CODE_ID TAX_CODE_ID
, PLLA.ESTIMATED_TAX_AMOUNT
, PLLA.TAX_NAME TAX_NAME
, PLLA.SHIP_TO_LOCATION_ID
, PLA.FROM_HEADER_ID
, PLA.FROM_LINE_ID
, PLA.CONTRACT_ID
, PLA.ITEM_ID
, FSP.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID
, PLA.ITEM_REVISION ITEMRV
, PLA.ITEM_REVISION ITEMRVX
, PLA.VENDOR_PRODUCT_NUM ITEMX
, MSIV.SEGMENT1 ITEM
, PLA.ITEM_DESCRIPTION DESCRIPTION
, PHC.HAZARD_CLASS HAZRDMTL
, PLA.NOTE_TO_VENDOR NOTE_TO_VENDOR
, 'LINENUM'
, 'SHIPMENTNUM'
, 'BUYERPARTNUM'
, PLA.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
, PO_LINES_ALL PLA
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, HR_ORGANIZATION_UNITS_V HOU_V
, MTL_SYSTEM_ITEMS_B_KFV MSIV
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_HAZARD_CLASSES PHC
, PO_LINE_TYPES_B PLTB
WHERE NVL ( MSIV.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID ) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
AND MSIV.INVENTORY_ITEM_ID ( + ) = PLA.ITEM_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND FSPA.ORG_ID = PLLA.ORG_ID
AND HOU_V.ORGANIZATION_ID = PLLA.ORG_ID
AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID ( + )
AND PLA.LINE_TYPE_ID = PLTB.LINE_TYPE_ID
AND PLA.ORDER_TYPE_LOOKUP_CODE = 'FIXED PRICE' ORDER BY PO_HEADER_ID
, PO_LINE_ID
, LINE_LOCATION_ID