PO Overdue Vendor Shipments

➜ Back to Library
Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Overdue Vendor Shipments Report (XML)
Short Name: POXRVODS_XML
DB package: PO_POXRVODS_XMLP_PKG 
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
Parameter Name SQL text Validation
Category Structure 
 
 Number
Dynamic Precision Option 
 
 LOV Oracle
Organization Name 
 
 LOV Oracle
To 
 
Categories From 
 
Ship-to Location 
 
 LOV Oracle
To 
 
 LOV Oracle
Vendors From 
 
 LOV Oracle
Buyer Name 
 
 LOV Oracle
Overdue as of 
 
 Date
Title 
 
Download
Related Blitz Reports
PO Substitute Receipts
PO Receiving Transactions Register
PO Blanket and Planned PO Status
PO Overshipments
PO Receipt Adjustments
PO Receiving Exceptions
PO Purchase Requisition Status
PO Headers and Lines
PO Savings Analysis Report(by Category)
Blitz Report In Action