VIEW: APPS.ITG_PO_LINE_LOC_ARCH_V

Source

View Text - Preformatted

SELECT PLLA.PO_HEADER_ID PO_HEADER_ID, PLLA.REVISION_NUM REVISION_NUM, nvl(PLLA.PO_RELEASE_ID, - 9999) 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, '0' SHIPTO_ORG_OTF, 'ShipTo' SHIPTO_ORG_PARTNER_TYPE, 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.SHIP_TO_LOCATION_ID SHIP_TO_LOC_ID, NVL(PLLA.CONSIGNED_FLAG, 'N') CONSIGNED_FLAG, PLLA.DROP_SHIP_FLAG DROP_SHIP_FLAG, DECODE(plla.cancel_flag, 'Y', 'CANCELLED', 'OPEN') PSCLNSTATUS, PLLA.AMOUNT AMOUNT_SERVICES 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, PO_HEADERS_ALL PHA WHERE 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.SHIP_TO_ORGANIZATION_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 pha.po_header_id = plla.po_header_id and ((pha.type_lookup_code = 'STANDARD' and PLLA.PO_RELEASE_ID is null or PLLA.PO_RELEASE_ID = 0) or (pha.type_lookup_code <> 'STANDARD' and PLLA.PO_RELEASE_ID is not null )) and NVL(plla.shipment_type,'xx') <> 'PREPAYMENT'

View Text - HTML Formatted

SELECT PLLA.PO_HEADER_ID PO_HEADER_ID
, PLLA.REVISION_NUM REVISION_NUM
, NVL(PLLA.PO_RELEASE_ID
, - 9999) 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
, '0' SHIPTO_ORG_OTF
, 'SHIPTO' SHIPTO_ORG_PARTNER_TYPE
, 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.SHIP_TO_LOCATION_ID SHIP_TO_LOC_ID
, NVL(PLLA.CONSIGNED_FLAG
, 'N') CONSIGNED_FLAG
, PLLA.DROP_SHIP_FLAG DROP_SHIP_FLAG
, DECODE(PLLA.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, 'OPEN') PSCLNSTATUS
, PLLA.AMOUNT AMOUNT_SERVICES
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
, PO_HEADERS_ALL PHA
WHERE 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.SHIP_TO_ORGANIZATION_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 PHA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND ((PHA.TYPE_LOOKUP_CODE = 'STANDARD'
AND PLLA.PO_RELEASE_ID IS NULL OR PLLA.PO_RELEASE_ID = 0) OR (PHA.TYPE_LOOKUP_CODE <> 'STANDARD'
AND PLLA.PO_RELEASE_ID IS NOT NULL ))
AND NVL(PLLA.SHIPMENT_TYPE
, 'XX') <> 'PREPAYMENT'