PO Vendor Service Performance Analysis

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Vendor Service Performance Analysis Report (XML)
Short Name: POXSERPR_XML
DB package: PO_POXSERPR_XMLP_PKG
SELECT null                                           C_FLEX_ITEM
,      pol.item_description                                    Item_Description
,      poh.segment1                                            PO_Number
,      pll.line_location_id                                   parent_line_location_id
,      round((pll.quantity - nvl(pll.quantity_cancelled,0)),:P_qty_precision)     pll_quantity_ordered
,      round(pll.quantity_rejected,:P_qty_precision)     pll_quantity_rejected
,      poh.vendor_id							vendor_id1
,      nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0)         Promised_date
,      nvl(pll.promised_date,pll.need_by_date ) + nvl(pll.days_late_receipt_allowed,0)	         Cutoff_date
,      pll.ship_to_location_id                                                                     Ship_to_location, 
NVL2(pol.item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),NULL) C_FLEX_ITEM_DISP,
	PO_POXSERPR_XMLP_PKG.c_per_item_earlyformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_early) C_per_item_early, 
	PO_POXSERPR_XMLP_PKG.c_per_item_lateformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_late) C_per_item_late, 
	PO_POXSERPR_XMLP_PKG.c_per_item_openformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_open) C_per_item_open, 
	PO_POXSERPR_XMLP_PKG.c_per_item_past_dueformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_past_due) C_per_item_past_due, 
	PO_POXSERPR_XMLP_PKG.c_per_item_on_timeformula(:C_item_sum_ord, :C_item_sum_rec, :c_item_sum_on_time) C_per_item_on_time, 
	PO_POXSERPR_XMLP_PKG.c_per_item_rejformula(:C_item_sum_rec, :C_item_sum_rej) C_per_item_rej, 
	PO_POXSERPR_XMLP_PKG.c_per_item_varformula(:C_item_sum_rec, :C_item_sum_days_qty) C_per_item_var, 
	PO_POXSERPR_XMLP_PKG.c_per_item_locformula(:C_item_sum_rec, :C_item_sum_w_loc) C_per_item_loc, 
	PO_POXSERPR_XMLP_PKG.varianceformula(:PO_RECEIVED, :PO_DAYS_QTY) Variance, 
	PO_POXSERPR_XMLP_PKG.p_openformula(:PO_ORDERED, :PO_RECEIVED, :PO_OPEN) P_open, 
	PO_POXSERPR_XMLP_PKG.p_past_dueformula(:PO_ORDERED, :PO_RECEIVED, :PO_PAST_DUE) P_past_due, 
	PO_POXSERPR_XMLP_PKG.earlyformula(:PO_ORDERED, :PO_RECEIVED, :PO_EARLY) Early, 
	PO_POXSERPR_XMLP_PKG.per_rejectedformula(:PO_RECEIVED, :PO_REJECTED) per_rejected, 
	PO_POXSERPR_XMLP_PKG.lateformula(:PO_ORDERED, :PO_RECEIVED, :PO_LATE) Late, 
	PO_POXSERPR_XMLP_PKG.locationformula(:PO_RECEIVED, :PO_WRONG_LOCATION) Location, 
	PO_POXSERPR_XMLP_PKG.on_timeformula(:PO_ORDERED, :PO_RECEIVED, :PO_ON_TIME) On_time, 
	PO_POXSERPR_XMLP_PKG.past_dueformula(:Received, :Ordered, nvl ( pll.promised_date , pll.need_by_date ) + nvl ( pll.days_late_receipt_allowed , 0 )) Past_due, 
	PO_POXSERPR_XMLP_PKG.openformula(:Received, :Ordered, nvl ( pll.promised_date , pll.need_by_date ) + nvl ( pll.days_late_receipt_allowed , 0 )) Open, 
--	PO_POXSERPR_XMLP_PKG.orderedformula(:shipment_conversion_rate, round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) --) , :P_qty_precision )) Ordered, 
    round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) ) , :P_qty_precision ) Ordered, 
	PO_POXSERPR_XMLP_PKG.rejectedformula(:shipment_conversion_rate, round ( pll.quantity_rejected , :P_qty_precision )) Rejected
FROM   po_line_locations            pll
,      po_lines                     pol
,      po_headers                   poh
,      po_vendors                   pov
,      mtl_system_items             msi
,      mtl_categories               mca
,      per_all_people_f             papf 
WHERE  pll.po_line_id               = pol.po_line_id
AND    pol.po_header_id             = poh.po_header_id
AND    pol.item_id                  = msi.inventory_item_id(+)
AND    pol.category_id          = mca.category_id
AND    nvl(msi.organization_id,:c_organization_id) = :c_organization_id
AND    poh.vendor_id                = pov.vendor_id
AND    &P_WHERE_ITEM
AND    &P_WHERE_CAT
AND    exists (select 'receiving transaction for this PO' from rcv_transactions rct where rct.transaction_type = 'RECEIVE' and rct.po_header_id = poh.po_header_id
and rct.po_line_id = pol.po_line_id 
and rct.po_line_location_id = pll.line_location_id)
and      poh.vendor_id = :Parent_vendor_id
AND    pll.shipment_type = 'STANDARD'
AND    poh.type_lookup_code = 'STANDARD'
AND    trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND    decode(hr_general.get_xbg_profile,'Y', papf.business_group_id ,
       hr_general.get_business_group_id) = papf.business_group_id
AND   papf.person_id = poh.agent_id
AND     papf.full_name = nvl(:P_buyer,papf.full_name)
AND     trunc(to_date(poh.creation_date,'DD-MON-YY')) between 
             trunc(to_date(nvl(:P_creation_date_from, poh.creation_date - 1) ,'DD-MON-YY'))
                       and 
              trunc(to_date(nvl(:P_creation_date_to, poh.creation_date + 1),'DD-MON-YY'))
AND    pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')          
UNION ALL
SELECT null                                            C_FLEX_ITEM
,      pol.item_description                                    Item_Description
,      poh.segment1||'-'||por.release_num                      PO_Number
,      pll.line_location_id                                   parent_line_location_id
,      round((pll.quantity - nvl(pll.quantity_cancelled,0)),:P_qty_precision)     pll_quantity_ordered
,      round(pll.quantity_rejected,:P_qty_precision)     pll_quantity_rejected
,      poh.vendor_id						vendor_id1
,      nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0)         Promised_date
,      nvl(pll.promised_date,pll.need_by_date ) + nvl(pll.days_late_receipt_allowed,0)	         Cutoff_date
,      pll.ship_to_location_id                                                                     Ship_to_location, 
NVL2(pol.item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),NULL) C_FLEX_ITEM_DISP,
	PO_POXSERPR_XMLP_PKG.c_per_item_earlyformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_early) C_per_item_early, 
	PO_POXSERPR_XMLP_PKG.c_per_item_lateformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_late) C_per_item_late, 
	PO_POXSERPR_XMLP_PKG.c_per_item_openformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_open) C_per_item_open, 
	PO_POXSERPR_XMLP_PKG.c_per_item_past_dueformula(:C_item_sum_ord, :C_item_sum_rec, :C_item_sum_past_due) C_per_item_past_due, 
	PO_POXSERPR_XMLP_PKG.c_per_item_on_timeformula(:C_item_sum_ord, :C_item_sum_rec, :c_item_sum_on_time) C_per_item_on_time, 
	PO_POXSERPR_XMLP_PKG.c_per_item_rejformula(:C_item_sum_rec, :C_item_sum_rej) C_per_item_rej, 
	PO_POXSERPR_XMLP_PKG.c_per_item_varformula(:C_item_sum_rec, :C_item_sum_days_qty) C_per_item_var, 
	PO_POXSERPR_XMLP_PKG.c_per_item_locformula(:C_item_sum_rec, :C_item_sum_w_loc) C_per_item_loc, 
	PO_POXSERPR_XMLP_PKG.varianceformula(:PO_RECEIVED, :PO_DAYS_QTY) Variance, 
	PO_POXSERPR_XMLP_PKG.p_openformula(:PO_ORDERED, :PO_RECEIVED, :PO_OPEN) P_open, 
	PO_POXSERPR_XMLP_PKG.p_past_dueformula(:PO_ORDERED, :PO_RECEIVED, :PO_PAST_DUE) P_past_due, 
	PO_POXSERPR_XMLP_PKG.earlyformula(:PO_ORDERED, :PO_RECEIVED, :PO_EARLY) Early, 
	PO_POXSERPR_XMLP_PKG.per_rejectedformula(:PO_RECEIVED, :PO_REJECTED) per_rejected, 
	PO_POXSERPR_XMLP_PKG.lateformula(:PO_ORDERED, :PO_RECEIVED, :PO_LATE) Late, 
	PO_POXSERPR_XMLP_PKG.locationformula(:PO_RECEIVED, :PO_WRONG_LOCATION) Location, 
	PO_POXSERPR_XMLP_PKG.on_timeformula(:PO_ORDERED, :PO_RECEIVED, :PO_ON_TIME) On_time, 
	PO_POXSERPR_XMLP_PKG.past_dueformula(:Received, :Ordered, nvl ( pll.promised_date , pll.need_by_date ) + nvl ( pll.days_late_receipt_allowed , 0 )) Past_due, 
	PO_POXSERPR_XMLP_PKG.openformula(:Received, :Ordered, nvl ( pll.promised_date , pll.need_by_date ) + nvl ( pll.days_late_receipt_allowed , 0 )) Open, 
--	PO_POXSERPR_XMLP_PKG.orderedformula(:shipment_conversion_rate, round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) --) , :P_qty_precision )) Ordered, 
    round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) ) , :P_qty_precision ) Ordered,
	PO_POXSERPR_XMLP_PKG.rejectedformula(:shipment_conversion_rate, round ( pll.quantity_rejected , :P_qty_precision )) Rejected
FROM   po_line_locations            pll
,      po_lines                     pol
,      po_headers                   poh
,      po_vendors                   pov
,      po_releases                  por 
,      mtl_system_items             msi
,      mtl_categories               mca
,      per_all_people_f             papf 
WHERE  pll.po_line_id               = pol.po_line_id
AND    pol.po_header_id             = poh.po_header_id
AND    por.po_header_id             = poh.po_header_id
AND    pll.po_release_id            = por.po_release_id
AND    pol.item_id                  = msi.inventory_item_id(+)
AND    pol.category_id          = mca.category_id
AND    nvl(msi.organization_id,:c_organization_id) = :c_organization_id
AND    poh.vendor_id                = pov.vendor_id
AND    &P_WHERE_ITEM
AND    &P_WHERE_CAT
AND    exists (select 'receiving transaction for this PO' from rcv_transactions rct where rct.transaction_type = 'RECEIVE' and rct.po_header_id = poh.po_header_id
and    rct.po_line_id = pol.po_line_id 
and    rct.po_line_location_id = pll.line_location_id)
and    poh.vendor_id = :Parent_vendor_id
AND    pll.shipment_type in ( 'BLANKET','SCHEDULED')
AND    poh.type_lookup_code in ( 'BLANKET','PLANNED')
AND    trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND    decode(hr_general.get_xbg_profile,'Y', papf.business_group_id ,
       hr_general.get_business_group_id) = papf.business_group_id
AND   papf.person_id = poh.agent_id
AND     papf.full_name = nvl(:P_buyer,papf.full_name)
AND     trunc(to_date(por.creation_date,'DD-MON-YY')) between 
              trunc(to_date(nvl(:P_creation_date_from, por.creation_date - 1) ,'DD-MON-YY'))
                       and 
	trunc(to_date(nvl(:P_creation_date_to, por.creation_date + 1),'DD-MON-YY'))
AND    pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT') 
ORDER BY 
vendor_id1,
--C_FLEX_ITEM,
C_FLEX_ITEM_DISP,
Item_Description,
PO_Number,
parent_line_location_id,
pll_quantity_ordered,
pll_quantity_rejected,
Promised_date,
Ship_to_location,
Cutoff_date
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
Detail
 
LOV Oracle
To
 
Date
Creation Dates From
 
Date
Buyer Name
 
LOV Oracle
To
 
Categories From
 
To
 
LOV Oracle
Vendors From
 
LOV Oracle
To
 
Items From
 
Title