PO Overdue Vendor Shipments

Description

Categories: BI Publisher
Application: Purchasing
Source: Overdue Vendor Shipments Report (XML)
Short Name: POXRVODS_XML
DB package: PO_POXRVODS_XMLP_PKG

Imported from BI Publisher
Overdue Vendor Shipments Report

SQL 150 lines

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

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

Parameters 11

Parameter	Type	List of values SQL
Operating Unit	LOV
Title	Char
Overdue as of	Date
Buyer Name	LOV Oracle
Vendors From	LOV Oracle
To	LOV Oracle
Ship-to Location	LOV Oracle
Categories From	Char
To 2	Char
Organization Name	LOV Oracle
Dynamic Precision Option	LOV Oracle

Description

SQL 150 lines

Parameters 11

Find out more

Features

Download

FAQ