VIEW: APPS.JAI_PO_SUMMARY_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)