PO Receiving Value

SELECT 
/*	&P_ITEM_SEG		ITEM_NUMBER,*/
/*	&P_CAT_SEG		CATEGORY,*/
	RSL.ITEM_DESCRIPTION		DESCRIPTION, 
	RT.PRIMARY_UNIT_OF_MEASURE	UOM_CODE, 
	DECODE ( :P_SORT_OPTION,
                'LOCATION', LOT.LOCATION_CODE,
                'VENDOR', POV.VENDOR_NAME,
                NULL
              )		SORT_COLUMN,
	LOT.LOCATION_CODE	CURRENT_LOCATION,
	NVL(rsl.packing_slip, rsh.packing_slip)          PACKING_SLIP,   
	RSH.SHIPMENT_NUM                                SHIPMENT_NUM,  
	RSH.RECEIPT_NUM                                   RECEIPT_NUM,  
	RT.TRANSACTION_DATE                                RECEIPT_DATE, 
	DECODE(:P_ITEM_REVISION,1,RPAD(NVL(CIQT.REVISION,'   '),3,' '),NULL) REVISION, 
	POL.DISPLAYED_FIELD                   DOCUMENT_TYPE,
	NVL(ph.CLM_DOCUMENT_NUMBER,ph.SEGMENT1) 		DOCUMENT_NUMBER, 
	nvl(pl.LINE_NUM_DISPLAY, to_char(pl.LINE_NUM))		DOCUMENT_LINE_NUM, 
	sum(nvl(CIQT.rollback_qty,0)) 		QUANTITY, 
	nvl(CICT.item_cost,0) * sum(nvl(CIQT.rollback_qty,0))  TOTAL_PURCHASE_VALUE, 
    CIQT.RCV_TRANSACTION_ID, 
	PO_POXRRCVV_XMLP_PKG.cf_item_cost_dispformula(DECODE ( :P_SORT_OPTION , 'LOCATION' , LOT.LOCATION_CODE , 'VENDOR' , POV.VENDOR_NAME , NULL )) CF_ITEM_COST_DISP, 
	PO_POXRRCVV_XMLP_PKG.itemcatformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE')) ITEMCAT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') CATEGORY_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') ITEM_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT, 
	PO_POXRRCVV_XMLP_PKG.comp_avg_unit_price(:ITEM_QUANTITY, :ITEM_TOTAL_PUR_VALUE, :C_EXT_PRECISION) AVERAGE_UNIT_PRICE, 
	PO_POXRRCVV_XMLP_PKG.total_pur_valueformula(nvl ( CICT.item_cost , 0 ) * sum ( nvl ( CIQT.rollback_qty , 0 ) )) total_pur_value, 
-- commented by Atul
--	PO_POXRRCVV_XMLP_PKG.c_total_pur_valueformula(:total_pur_value) c_total_pur_value,
	PO_POXRRCVV_XMLP_PKG.c_total_pur_valueformula(PO_POXRRCVV_XMLP_PKG.total_pur_valueformula(nvl ( CICT.item_cost , 0 ) * sum ( nvl ( CIQT.rollback_qty , 0 ) ))) c_total_pur_value,
	PO_POXRRCVV_XMLP_PKG.c_quantityformula(sum ( nvl ( CIQT.rollback_qty , 0 ) )) c_quantity
FROM   
	CST_INV_QTY_TEMP		CIQT,
	CST_INV_COST_TEMP		CICT,
	MTL_SYSTEM_ITEMS		MSI,
	HR_LOCATIONS_ALL_TL		LOT,
	MTL_CATEGORIES			MC,
	PO_LOOKUP_CODES 		POL,
	PO_VENDORS			POV,
	RCV_TRANSACTIONS		RT,
	PO_HEADERS_ALL			PH,
	PO_LINES_ALL			PL,
	RCV_SHIPMENT_LINES		RSL,
	RCV_SHIPMENT_HEADERS	RSH
WHERE  
	   CIQT.qty_source in (9,10)
AND    CICT.cost_source in (3,4)
AND    CICT.organization_id = CIQT.organization_id
AND    nvl(CICT.inventory_item_id, -1) = nvl(CIQT.inventory_item_id, -1)
AND    CICT.rcv_transaction_id = CIQT.rcv_transaction_id
and     RT.transaction_id = CICT.rcv_transaction_id
AND    MSI.organization_id (+) = CIQT.organization_id
AND    MSI.inventory_item_id (+) = CIQT.inventory_item_id
AND    MC.category_id  = CIQT.category_id
and     RSH.shipment_header_id = RT.shipment_header_id
and     RSL.shipment_line_id = RT.shipment_line_id
and     PH.po_header_id = RT.po_header_id
and     PL.po_line_id = RT.po_line_id
and     RT.LOCATION_ID = LOT.LOCATION_ID(+) 
and     lot.language (+)= userenv('LANG')
AND    RSL.SOURCE_DOCUMENT_CODE   = 'PO' 
AND    POL.LOOKUP_TYPE           = 'DOCUMENT TYPE'
AND    POL.LOOKUP_CODE          = RSL.SOURCE_DOCUMENT_CODE
AND     POV.VENDOR_ID(+)     = RT.VENDOR_ID
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	RSL.ITEM_DESCRIPTION, 
	RT.PRIMARY_UNIT_OF_MEASURE, 
	DECODE ( :P_SORT_OPTION,
                'LOCATION', LOT.LOCATION_CODE,
                'VENDOR', POV.VENDOR_NAME,
                NULL
              ),
	LOT.LOCATION_CODE,
	NVL(rsl.packing_slip, rsh.packing_slip),   
	RSH.SHIPMENT_NUM,  
	RSH.RECEIPT_NUM,  
	RT.TRANSACTION_DATE, 
	DECODE(:P_ITEM_REVISION,1,RPAD(NVL(CIQT.REVISION,'   '),3,' '),NULL), 
	POL.DISPLAYED_FIELD,
	NVL(ph.CLM_DOCUMENT_NUMBER,ph.SEGMENT1) , 
	nvl(pl.LINE_NUM_DISPLAY, to_char(pl.LINE_NUM)), 
	nvl(CICT.item_cost, 0),
	CIQT.RCV_TRANSACTION_ID ,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE')
having round(sum(nvl(CIQT.rollback_qty,0)),:P_QTY_PRECISION) <> 0
order by SORT_COLUMN,ITEM_SEGMENT,DESCRIPTION,CURRENT_LOCATION,DOCUMENT_TYPE,SHIPMENT_NUM,DOCUMENT_NUMBER,DOCUMENT_LINE_NUM,RECEIPT_DATE,
CIQT . RCV_TRANSACTION_ID,ITEMCAT,ITEM_PSEG