PO Vendor Quality Performance Analysis

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Vendor Quality Performance Analysis Report (XML)
Short Name: POXQUAPR_XML
DB package: PO_POXQUAPR_XMLP_PKG
SELECT null                                            C_FLEX_ITEM
,      pol.item_id			Item_Id 
,      pol.item_description                                    Item_Description
,      poh.segment1                                            PO_Number
,      pll.line_location_id                                   parent_line_location_id
,      round(pll.quantity,:P_qty_precision)     pll_quantity_ordered
,      round(pll.quantity_accepted,:P_qty_precision)     pll_quantity_accepted
,      round(pll.quantity_rejected,:P_qty_precision)     pll_quantity_rejected
,      poh.vendor_id 				
,      uom.uom_code                     PO_unit_of_measure
,      nvl(msi.primary_uom_code, uom.uom_code)             Item_Unit 
,      nvl(msi.primary_unit_of_measure, pll.unit_meas_lookup_code)   
Item_primary_unit, 
	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') C_FLEX_ITEM_DISP, 
/*	PO_POXQUAPR_XMLP_PKG.c_item_per_acceptedformula(:C_item_sum_rec, :C_item_sum_acc) C_Item_Per_Accepted, 
	PO_POXQUAPR_XMLP_PKG.c_item_per_rejectedformula(:C_item_sum_rec, :C_item_sum_rej) C_Item_Per_Rejected, 
	PO_POXQUAPR_XMLP_PKG.c_item_per_uninsformula(:C_item_sum_rec, :C_item_sum_unins) C_item_Per_Unins, 
	PO_POXQUAPR_XMLP_PKG.c_item_per_returnedformula(:C_item_sum_rec, :C_item_sum_ret) C_Item_Per_Returned, 
	PO_POXQUAPR_XMLP_PKG.c_item_per_rtv_wout_insformula(:C_item_sum_rec, :C_item_sum_rtv_wout_ins) C_item_Per_Rtv_wout_ins, 
	PO_POXQUAPR_XMLP_PKG.uninspectedformula(:Received, :Accepted, :Rejected) Uninspected, 
	PO_POXQUAPR_XMLP_PKG.per_acceptedformula(:Received, :Accepted) Per_Accepted, 
	PO_POXQUAPR_XMLP_PKG.per_rejectedformula(:Received, :Rejected) Per_Rejected, 
	PO_POXQUAPR_XMLP_PKG.per_uninspectedformula(:Received, :Uninspected) Per_Uninspected, 
	PO_POXQUAPR_XMLP_PKG.per_returnedformula(:Received, :Returned) Per_Returned, 
	PO_POXQUAPR_XMLP_PKG.per_rtv_wout_inspectformula(:Received, :Rtv_wout_inspect) Per_Rtv_wout_inspect, */
	PO_POXQUAPR_XMLP_PKG.quantity_acceptedformula(round ( pll.quantity_accepted , :P_qty_precision ), PO_POXQUAPR_XMLP_PKG.conversion_rate(pol.item_id,nvl(msi.organization_id,:c_organization_id),nvl(msi.primary_uom_code, uom.uom_code),uom.uom_code)) quantity_accepted,    /* bug #11840167 */
	PO_POXQUAPR_XMLP_PKG.quantity_rejectedformula(PO_POXQUAPR_XMLP_PKG.conversion_rate(pol.item_id,nvl(msi.organization_id,:c_organization_id),nvl(msi.primary_uom_code, uom.uom_code),uom.uom_code), round ( pll.quantity_rejected , :P_qty_precision )) quantity_rejected,    /* bug #11840167 */
	PO_POXQUAPR_XMLP_PKG.quantity_orderedformula(PO_POXQUAPR_XMLP_PKG.conversion_rate(pol.item_id,nvl(msi.organization_id,:c_organization_id),nvl(msi.primary_uom_code, uom.uom_code),uom.uom_code), round ( pll.quantity , :P_qty_precision )) quantity_ordered,               /* bug #11840167 */
	PO_POXQUAPR_XMLP_PKG.c_tot_returnedformula(pll.line_location_id, pol.item_id) C_tot_returned, 
	PO_POXQUAPR_XMLP_PKG.c_tot_inspectedformula(pll.line_location_id, pol.item_id) C_tot_inspected, 
	PO_POXQUAPR_XMLP_PKG.c_tot_receivedformula(pll.line_location_id, pol.item_id) C_tot_received,
	/* bug #11840167 */
	PO_POXQUAPR_XMLP_PKG.uninspectedformula(PO_POXQUAPR_XMLP_PKG.c_tot_receivedformula(pll.line_location_id, pol.item_id), PO_POXQUAPR_XMLP_PKG.quantity_acceptedformula(round ( pll.quantity_accepted , :P_qty_precision ), PO_POXQUAPR_XMLP_PKG.conversion_rate(pol.item_id,nvl(msi.organization_id,:c_organization_id),nvl(msi.primary_uom_code, uom.uom_code),uom.uom_code)), PO_POXQUAPR_XMLP_PKG.quantity_rejectedformula(PO_POXQUAPR_XMLP_PKG.conversion_rate(pol.item_id,nvl(msi.organization_id,:c_organization_id),nvl(msi.primary_uom_code, uom.uom_code),uom.uom_code), round ( pll.quantity_rejected , :P_qty_precision ))) Uninspected
    /* bug #11840167 */
FROM   po_line_locations            pll
,      po_lines                     pol
,      po_headers                   poh
,      po_vendors                   pov
,      mtl_system_items             msi
,      mtl_units_of_measure   uom
,       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    nvl(pll.cancel_flag,'N')     = 'N'
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    poh.vendor_id = :Parent_vendor_id 
AND    papf.person_id = poh.agent_id
and    papf.full_name  = nvl(:P_buyer,papf.full_name)
AND PAPF.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) 
FROM FINANCIALS_SYSTEM_PARAMETERS FSP) 
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', 
HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF.PERSON_ID, PAPF.PERSON_TYPE_ID,
PAPF.EMPLOYEE_NUMBER,PAPF.APPLICANT_NUMBER 
, PAPF.NPW_NUMBER)) = 'TRUE'     
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID
and   pol.unit_meas_lookup_code = uom.unit_of_measure
and trunc(poh.creation_date) between nvl(:P_creation_date_from,poh.creation_date -1) and 
	nvl(:P_creation_date_to,poh.creation_date + 1) 
 and poh.vendor_id=:Parent_vendor_id
order by C_FLEX_ITEM_DISP,Item_Unit,poh.vendor_id,Item_Id,Item_Description,Item_primary_unit, poh.segment1, round(pll.quantity_accepted,:P_qty_precision), round(pll.quantity_rejected,:P_qty_precision), uom.uom_code, round(pll.quantity,:P_qty_precision), pll.line_location_id
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