View: JAI_PO_SUMMARY_V

Product:	JA - Asia/Pacific Localizations
Description:	This view will retrieve all the PO TYPES
Implementation/DBA Data:	APPS.JAI_PO_SUMMARY_V

View Text

SELECT PLL.ROWID
, POH.SEGMENT1
, POR.RELEASE_NUM
, POL.LINE_NUM
, PLL.SHIPMENT_NUM
, POH.TYPE_LOOKUP_CODE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, PDTL.TYPE_NAME
, PDTL2.TYPE_NAME)
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_MEAS_LOOKUP_CODE
, PLL.QUANTITY
, POH.CURRENCY_CODE
, PLL.PRICE_OVERRIDE
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.SHIP_TO_LOCATION_ID
, HOUT.NAME
, HRL.LOCATION_CODE
, POH.BILL_TO_LOCATION_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, POH.PO_HEADER_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, PLL.PO_RELEASE_ID
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE'))
, TO_CHAR(PLL.NEED_BY_DATE) NEED_BY_DATE
, POH.AGENT_ID
, POH.ORG_ID
, POH.BLANKET_TOTAL_AMOUNT
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_DOCUMENT_TYPES_ALL_B PDTB2
, PO_DOCUMENT_TYPES_ALL_TL PDTL2
, PO_SYSTEM_PARAMETERS PSP
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL2
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
WHERE POH.PO_HEADER_ID=POL.PO_HEADER_ID
AND POL.PO_LINE_ID=PLL.PO_LINE_ID
AND POR.PO_RELEASE_ID(+)=PLL.PO_RELEASE_ID
AND POV.VENDOR_ID (+) = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND HOUT.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HOUT.LANGUAGE(+) = USERENV('LANG')
AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LANGUAGE = USERENV('LANG')
AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE
AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE
AND (PDTL.ORG_ID IS NULL OR PDTL.ORG_ID = PLL.ORG_ID)
AND NVL(PDTL.ORG_ID
, -99) = NVL(PDT.ORG_ID
, -99)
AND PDTL.LANGUAGE = USERENV('LANG')
AND PDT.ORG_ID = PLL.ORG_ID
AND PDTB2.DOCUMENT_TYPE_CODE(+) = 'RELEASE'
AND PDTB2.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPE
AND PDTB2.DOCUMENT_TYPE_CODE = PDTL2.DOCUMENT_TYPE_CODE(+)
AND PDTB2.DOCUMENT_SUBTYPE = PDTL2.DOCUMENT_SUBTYPE(+)
AND NVL(PDTB2.ORG_ID
, -99) = NVL(PDTL2.ORG_ID
, -99)
AND (PDTB2.ORG_ID IS NULL OR PDTB2.ORG_ID = PSP.ORG_ID)
AND POH.ORG_ID = PSP.ORG_ID
AND PDTL2.LANGUAGE(+) = USERENV('LANG')
AND ( PLL.CANCEL_FLAG IS NULL OR PLL.CANCEL_FLAG = 'N' )
AND (LTRIM(RTRIM(POH.TYPE_LOOKUP_CODE)) || '_' || LTRIM(RTRIM(PLL.SHIPMENT_TYPE)) <> 'BLANKET_PRICE BREAK') UNION ALL SELECT PLL.ROWID
, POH.SEGMENT1
, POR.RELEASE_NUM
, POL.LINE_NUM
, PLL.SHIPMENT_NUM
, POH.TYPE_LOOKUP_CODE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, PDTL.TYPE_NAME
, PDTL2.TYPE_NAME)
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_MEAS_LOOKUP_CODE
, PLL.QUANTITY
, POH.CURRENCY_CODE
, PLL.PRICE_OVERRIDE
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.SHIP_TO_LOCATION_ID
, HOUT.NAME
, HRL.LOCATION_CODE
, POH.BILL_TO_LOCATION_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, POH.PO_HEADER_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, PLL.PO_RELEASE_ID
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, TO_CHAR(PLL.NEED_BY_DATE) NEED_BY_DATE
, POH.AGENT_ID
, POH.ORG_ID
, POH.BLANKET_TOTAL_AMOUNT
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_DOCUMENT_TYPES_ALL_B PDTB2
, PO_DOCUMENT_TYPES_ALL_TL PDTL2
, PO_SYSTEM_PARAMETERS PSP
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL2
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
WHERE POH.PO_HEADER_ID=POL.PO_HEADER_ID
AND POL.PO_LINE_ID=PLL.PO_LINE_ID
AND POR.PO_RELEASE_ID(+)=PLL.PO_RELEASE_ID
AND POV.VENDOR_ID (+) = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LANGUAGE = USERENV('LANG')
AND HOUT.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HOUT.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE
AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE
AND PDTL.ORG_ID = PLL.ORG_ID
AND NVL(PDTL.ORG_ID
, -99) = NVL(PDT.ORG_ID
, -99)
AND PDTL.LANGUAGE = USERENV('LANG')
AND (PDT.ORG_ID IS NULL OR PDT.ORG_ID = PLL.ORG_ID)
AND PDTB2.DOCUMENT_TYPE_CODE(+) = 'RELEASE'
AND PDTB2.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPE
AND PDTB2.DOCUMENT_TYPE_CODE = PDTL2.DOCUMENT_TYPE_CODE(+)
AND PDTB2.DOCUMENT_SUBTYPE = PDTL2.DOCUMENT_SUBTYPE(+)
AND NVL(PDTB2.ORG_ID
, -99) = NVL(PDTL2.ORG_ID
, -99)
AND (PDTB2.ORG_ID IS NULL OR PDTB2.ORG_ID = PSP.ORG_ID)
AND POH.ORG_ID = PSP.ORG_ID
AND PDTL2.LANGUAGE(+) = USERENV('LANG')
AND (POH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PLL.SHIPMENT_TYPE = 'PRICE BREAK' )
AND ( PLL.CANCEL_FLAG IS NULL OR PLL.CANCEL_FLAG = 'N' ) UNION ALL SELECT POL.ROWID
, POH.SEGMENT1
, POR.RELEASE_NUM
, POL.LINE_NUM
, NULL
, POH.TYPE_LOOKUP_CODE
, PDTL.TYPE_NAME
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.QUANTITY
, POH.CURRENCY_CODE
, POL.UNIT_PRICE
, NULL
, NULL
, NULL
, NULL
, POH.BILL_TO_LOCATION_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, POH.PO_HEADER_ID
, POL.PO_LINE_ID
, NULL
, POR.PO_RELEASE_ID
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, NULL
, POH.AGENT_ID
, POH.ORG_ID
, POH.BLANKET_TOTAL_AMOUNT
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_SYSTEM_PARAMETERS PSP
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POR.PO_HEADER_ID(+)=POL.PO_HEADER_ID
AND ( POL.CANCEL_FLAG IS NULL OR POL.CANCEL_FLAG = 'N' )
AND POV.VENDOR_ID (+) = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND POH.TYPE_LOOKUP_CODE IN ('BLANKET')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND NVL(PDT.ORG_ID
, -99) = NVL(POH.ORG_ID
, -99)
AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE
AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE
AND PDTL.LANGUAGE = USERENV('LANG')
AND NVL(PDTL.ORG_ID
, -99) = NVL(PDT.ORG_ID
, -99)
AND PDT.ORG_ID = PSP.ORG_ID
AND POH.ORG_ID = PSP.ORG_ID
AND NOT EXISTS (SELECT '1'
FROM PO_LINE_LOCATIONS_ALL
WHERE PO_HEADER_ID=POH.PO_HEADER_ID
AND PO_LINE_ID=POL.PO_LINE_ID)

Columns