PO Vendor Service Performance Analysis

➜ Back to Library
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
Download
Related Blitz Reports
XSECH : PO Vendor Service Performance Analysis PO Receiving Transactions Register PO Receipt Adjustments PO Receiving Exceptions CST AP and PO Accrual Reconciliation PO Headers and Lines 11i PO Substitute Receipts GL Account Analysis (Distributions) PO Purchase Price Variance
Blitz Report™

Blitz Report™ provides multiple benefits: