PO Vendor Service Performance Analysis

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Vendor Service Performance Analysis Report
Application: Purchasing
Source: Vendor Service Performance Analysis Report (XML)
Short Name: POXSERPR_XML
DB package: PO_POXSERPR_XMLP_PKG
Run PO Vendor Service Performance Analysis and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit
 
LOV
Title
 
Char
Items From
 
Char
To
 
Char
Vendors From
 
LOV Oracle
To 2
 
LOV Oracle
Categories From
 
Char
To 3
 
Char
Buyer Name
 
LOV Oracle
Creation Dates From
 
Date
To 4
 
Date
Detail
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle