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
Description: Receiving Value Report
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 | |
---|---|---|---|
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 |