PO Overdue Vendor Shipments

SELECT  nvl(pll.promised_date,pll.need_by_date)                                      P_Date,
               hre.full_name                                          Buyer,
               poh.segment1                                        PO_Number,
               pol.line_num                                           Line,
               null                                            C_FLEX_CAT,
               msib.Segment1                                 C_FLEX_ITEM_DISP,
               pol.item_description                                   Item_Description,
               round(pll.quantity_received,:P_qty_precision)          Received,
               plc.displayed_field                                    Authorization_Status,
               pol.unit_meas_lookup_code                              Unit,
               loc.location_code                                      Location,
               round(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)-nvl(pll.quantity_cancelled,0),:P_qty_precision)   Due,
               pol.item_revision                                      Rev,
               pol.vendor_product_num                                 Product_Number,
               poh.currency_code                                  Currency,
ROUND(decode(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)-nvl(pll.quantity_cancelled,0),0,0,decode(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0),0,0,(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)-nvl(pll.quantity_cancelled,0))/(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))*100)), :P_qty_precision)  P_due,
               poh.po_header_id,
               poh.vendor_id,
               decode(psp.manual_po_num_type,'NUMERIC',null,poh.segment1)    Document_Numbering1,
               decode(psp.manual_po_num_type,'NUMERIC',poh.segment1,null)    Document_Numbering2,
               mp.organization_code     Org,
               poh.vendor_site_id child_vendor_site_id,
               nvl(poh.vendor_contact_id, -100) child_vendor_contact_id,
               pol.item_id item_id, 
	--PO_POXRVODS_XMLP_PKG.c_item_flexformula(pol.item_id, :C_ORGANIZATION_ID) C_FLEX_ITEM, 
	--&C_FLEX_ITEM_DISP C_FLEX_ITEM_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
FROM      hr_locations_all loc,
               mtl_parameters    mp,
               po_lookup_codes      plc,
               po_system_parameters   psp,
               mtl_categories       mca,
               po_headers           poh,
               po_lines             pol,
               po_line_locations    pll,
               po_line_types        plt,
               per_all_people_f hre,
               MTL_SYSTEM_ITEMS_B   msib
WHERE   pol.po_header_id           = poh.po_header_id
AND       pll.po_line_id             = pol.po_line_id
AND       plt.line_type_id         = pol.line_type_id
AND       plt.matching_basis    <> 'AMOUNT'
AND       poh.type_lookup_code   = 'STANDARD'
AND       pll.shipment_type     =  'STANDARD'
AND       plc.lookup_type            = 'AUTHORIZATION STATUS'
AND       plc.lookup_code   =  nvl(poh.authorization_status,'INCOMPLETE')
AND       nvl(loc.business_group_id, nvl(hr_general.get_business_group_id, -99) ) 
         = nvl(hr_general.get_business_group_id, -99) 
AND       loc.location_id(+)         = pll.ship_to_location_id
AND       hre.person_id     =  poh.agent_id
AND       pol.category_id            = mca.category_id
AND       nvl(pll.closed_code,'OPEN') in ('OPEN','CLOSED FOR INVOICE')
AND       nvl(pll.cancel_flag,'N')   = 'N'
AND       nvl(pll.quantity_received,0)  - nvl(pll.quantity_cancelled,0) < nvl(pll.quantity,0)
AND       (nvl(pll.promised_date,need_by_date) <= decode(:P_OVERDUE_DATE,'', trunc(sysdate),
                                       trunc(to_date(to_char(:P_OVERDUE_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS') ) )
               OR   (nvl(pll.promised_date,pll.need_by_date) IS NULL 
                        AND TRUNC(SYSDATE) <= DECODE(:P_OVERDUE_DATE, '',trunc(SYSDATE),
                                        trunc(to_date(to_char(:P_OVERDUE_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS')))))
AND       nvl(loc.location_code,1) = nvl(:P_ship_to, nvl(loc.location_code,1))
AND       pll.ship_to_organization_id = nvl(:P_org_id, pll.ship_to_organization_id)
AND       &P_WHERE_CAT
AND       &P_WHERE_BUYER
AND       poh.vendor_site_id= :parent_vendor_site_id
AND       mp.organization_id = pll.ship_to_organization_id
AND       ((HRE.BUSINESS_GROUP_ID +0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) 
FROM FINANCIALS_SYSTEM_PARAMETERS FSP) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND 
HRE.EMPLOYEE_NUMBER IS NOT NULL) OR (HRE.PERSON_ID IS NULL))
and       msib.INVENTORY_ITEM_ID = pol.item_id 
and       mp.organization_id = msib.organization_id
UNION ALL
SELECT  nvl(pll.promised_date,pll.need_by_date)                                      P_Date,
               hre.full_name                                          Buyer,
               poh.segment1||'-'||por.release_num                                        PO_Number,
               pol.line_num                                           Line,
               null                                            C_FLEX_CAT,
               msib.Segment1                                 C_FLEX_ITEM_DISP,
               pol.item_description                                   Item_Description,
               round(pll.quantity_received,:P_qty_precision)          Received,
               plc.displayed_field                                    Authorization_Status,
               pol.unit_meas_lookup_code                              Unit,
               loc.location_code                                      Location,
               round(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)-nvl(pll.quantity_cancelled,0),:P_qty_precision)   Due,
               pol.item_revision                                      Rev,
               pol.vendor_product_num                                 Product_Number,
               poh.currency_code                                  Currency,
ROUND(decode(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)-nvl(pll.quantity_cancelled,0),0,0,decode(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0),0,0,(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)-nvl(pll.quantity_cancelled,0))/(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))*100)), :P_qty_precision)  P_due,
               poh.po_header_id, 
               poh.vendor_id,
               decode(psp.manual_po_num_type,'NUMERIC',null,poh.segment1||'-'||por.release_num)    Document_Numbering1,
               decode(psp.manual_po_num_type,'NUMERIC',poh.segment1||'-'||por.release_num,null)    Document_Numbering2,
               mp.organization_code     Org,
               poh.vendor_site_id child_vendor_site_id,
               nvl(poh.vendor_contact_id, -100) child_vendor_contact_id,
               pol.item_id item_id,
	--PO_POXRVODS_XMLP_PKG.c_item_flexformula(pol.item_id, :C_FLEX_ITEM, :C_ORGANIZATION_ID) C_FLEX_ITEM, 
	--&C_FLEX_ITEM_DISP C_FLEX_ITEM_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
FROM           hr_locations_all loc, 
               mtl_parameters    mp,
               po_lookup_codes      plc,
               po_system_parameters   psp,
               mtl_categories       mca,
               po_releases         por   ,
               po_headers           poh,
               po_lines             pol,
               po_line_locations    pll,
               po_line_types        plt,
               per_all_people_f hre,
               MTL_SYSTEM_ITEMS_B   msib
WHERE   pol.po_header_id           = poh.po_header_id
AND        pll.po_line_id             = pol.po_line_id
AND        plt.line_type_id         = pol.line_type_id
AND        plt.matching_basis <> 'AMOUNT'    
AND        poh.po_header_id     = por.po_header_id 
AND        poh.type_lookup_code   in ( 'BLANKET', 'PLANNED')
AND        pll.shipment_type     in ('BLANKET', 'SCHEDULED') 
AND        plc.lookup_type            = 'AUTHORIZATION STATUS'
AND        plc.lookup_code   =  nvl(por.authorization_status,'INCOMPLETE')
AND        nvl(loc.business_group_id, nvl(hr_general.get_business_group_id, -99) ) 
           = nvl(hr_general.get_business_group_id, -99) 
AND        loc.location_id(+)            = pll.ship_to_location_id
AND        hre.person_id     =  por.agent_id
AND        pol.category_id            = mca.category_id
AND        nvl(pll.closed_code,'OPEN') in ('OPEN','CLOSED FOR INVOICE' )
AND        nvl(pll.cancel_flag,'N')   = 'N'
AND        nvl(pll.quantity_received,0)  - nvl(pll.quantity_cancelled,0) < nvl(pll.quantity,0)
AND        (nvl(pll.promised_date,need_by_date) <= decode(:P_OVERDUE_DATE,'', trunc(sysdate),
                                            trunc(to_date(to_char(:P_OVERDUE_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS')) )
              OR (nvl(pll.promised_date,pll.need_by_date) IS NULL 
                      AND trunc(SYSDATE)  <= DECODE(:P_OVERDUE_DATE,  '',trunc(SYSDATE),
                                            trunc(to_date(to_char(:P_OVERDUE_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS')))))
AND        nvl(loc.location_code,1) = nvl(:P_ship_to, nvl(loc.location_code,1))
AND        pll.ship_to_organization_id = nvl(:P_org_id, pll.ship_to_organization_id)
AND        &P_WHERE_CAT
AND        &P_WHERE_BUYER
AND        poh.vendor_site_id= :parent_vendor_site_id
AND        mp.organization_id = pll.ship_to_organization_id
and         pll.po_release_id = por.po_release_id
AND       ((HRE.BUSINESS_GROUP_ID +0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) 
FROM FINANCIALS_SYSTEM_PARAMETERS FSP) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND 
HRE.EMPLOYEE_NUMBER IS NOT NULL) OR (HRE.PERSON_ID IS NULL)) 
 and poh.vendor_site_id=:parent_vendor_site_id 
 and nvl ( poh.vendor_contact_id , - 100 )=:parent_vendor_contact_id 
 and poh.vendor_id=:parent_vendor_id
 and msib.INVENTORY_ITEM_ID = pol.item_id 
 and mp.organization_id = msib.organization_id 
-- 1,2,19,20,4,11
ORDER BY 1,2,3,4,15,10,11,8,12,13,7,14,17,18,16,25,20,21