PO Vendor Quality Performance Analysis

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