PO Receiving Value

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Receiving Value Report
Application: Purchasing
Source: Receiving Value Report (XML)
Short Name: POXRRCVV_XML
DB package: PO_POXRRCVV_XMLP_PKG
Run PO Receiving Value and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Title
 
Char
Report Option
 
LOV Oracle
Sort Option
 
LOV Oracle
As of Date
 
DateTime
Items From
 
Char
Items To
 
Char
Category Set
 
LOV Oracle
Categories From
 
Char
Categories To
 
Char
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Quantities by Revision
 
LOV Oracle
Include One Time Items
 
LOV Oracle
Include Period End Accruals
 
LOV Oracle