PO Receiving Value

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Receiving Value Report (XML)
Short Name: POXRRCVV_XML
DB package: PO_POXRRCVV_XMLP_PKG
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
Parameter Name SQL text Validation
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
Chart of Accounts Id
 
Number
Organization Name
 
Number
Include Period End Accruals
 
LOV Oracle
Include One Time Items
 
LOV Oracle
Quantities by Revision
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Categories To
 
Categories From
 
Category Set
 
LOV Oracle
Items To
 
Items From
 
As of Date
 
DateTime
Sort Option
 
LOV Oracle
Report Option
 
LOV Oracle
Title