CST Intransit Value

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Intransit Value Report
Application: Bills of Material
Source: Intransit Value Report (XML)
Short Name: CSTRITVR_XML
DB package: BOM_CSTRITVR_XMLP_PKG
Run CST Intransit Value and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  &P_ITEM_SEG  ITEM_NUMBER,
         &P_CAT_SEG CATEGORY ,
        CUR_MSIVL.description DESCRIPTION,
        CUR_MSIVL.primary_uom_code UOM_CODE,
        OWN_MSI.primary_uom_code OWNING_ORG_PUOM_CODE,
        DECODE(
            :P_SORT_OPTION,
            5, RSH.freight_carrier_code,
            6, RPAD(TO_MP.organization_code,5,' ')||'TO_OOD.organization_name', 
            7, RSH.shipment_num) SORT_COLUMN,
        CIQT.organization_id OWNING_ORG_ID,
        FROM_MP.organization_id FROM_ORG_ID,
        FROM_MP.organization_code FROM_ORG_CODE,
        TO_MP.organization_code TO_ORG_CODE,
        FOB_LU.meaning FOB_POINT,
        RSH.shipment_num SHIPMENT_NUM,
        RSH.shipped_date SHIP_DATE,
        RSH.expected_receipt_date+NVL(TO_MSI.postprocessing_lead_time,0) ARRIVAL_DATE,
        RSH.freight_carrier_code FREIGHT_CARRIER_CODE,
        RSH.waybill_airbill_num WAYBILL_AIRBILL_NUM,
        RSH.num_of_containers NUM_OF_CONTAINERS,
        CCG.cost_group COST_GROUP,
        DECODE(
          :P_ITEM_REVISION,
          1,
          RPAD(NVL(CIQT.revision,'   '),3,' '),
          NULL
        ) REVISION,
        ROUND(SUM(CIQT.rollback_qty),:P_QTY_PRECISION) QUANTITY, 
        DECODE(
          CIQT.organization_id, 
          CIQT.from_organization_id, 
          FROM_MSI.primary_uom_code,
          TO_MSI.primary_uom_code
        ) UOMCODE,
        CIQT.inventory_item_id ITEM_ID,
        ROUND((NVL(CICT.item_cost,0)*:P_EXCHANGE_RATE1),:P_EXT_PREC) UNIT_COST,
        DECODE(:P_COST_TYPE_ID,CICT.cost_type_id,' ','*') DEFAULTED,
		ROUND((SUM(CIQT.rollback_qty * NVL(CICT.item_cost,0) )*:P_EXCHANGE_RATE1)/:ROUND_UNIT) * :ROUND_UNIT   TOTAL_COST, -- bug 17209556 has made changes, round off by currency precision ROUND_UNIT
        --ROUND((SUM(CIQT.rollback_qty * NVL(CICT.item_cost,0) )*:P_EXCHANGE_RATE1),:P_EXT_PREC)   TOTAL_COST, 
	CASE
	  WHEN from_mp.process_enabled_flag <> to_mp.process_enabled_flag 
	  THEN
	    0
	  WHEN ((MMT.TRANSACTION_ACTION_ID=12 AND MMT.TRANSACTION_SOURCE_TYPE_ID=101) OR
		(MMT.TRANSACTION_ACTION_ID=21 AND MMT.TRANSACTION_SOURCE_TYPE_ID=7))
	  THEN 
	    0	
	  ELSE
        decode( mip.MATL_INTERORG_TRANSFER_CODE, 
	             1,
	             ROUND((NVL(RSL.transfer_cost,0)/
                     (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),		
	             2,
		     ROUND((NVL(RSL.transfer_cost,0)/
                     (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),
		     3,
                     ROUND((decode(mip.fob_point,
		                   2,( NVL(CICT.item_cost,0 )*:P_EXCHANGE_RATE1 * rsl.TRANSFER_PERCENTAGE/100),
				   1,(nvl(mta.rate_or_amount,0)*:P_EXCHANGE_RATE1)
				   )/(gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), 
		           :P_EXT_PREC),
		     4,
		     ROUND((decode(mip.fob_point,
		                   2,( NVL(CICT.item_cost,0 )*:P_EXCHANGE_RATE1 * rsl.TRANSFER_PERCENTAGE/100),
				   1,(nvl(mta.rate_or_amount,0)*:P_EXCHANGE_RATE1)
				  )/(gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), 
			   :P_EXT_PREC) 
	       )
        END XFER_COST,
        ROUND((NVL(RSL.transportation_cost,0)/
                    (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC) XPORT_COST, 
	BOM_CSTRITVR_XMLP_PKG.itemcatformula( &P_CAT_SEG, :CATEGORY_SEGMENT, :CATEGORY_PSEG) ITEMCAT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', CUR_MCK.STRUCTURE_ID, NULL, CUR_MCK.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', CUR_MCK.STRUCTURE_ID, NULL, CUR_MCK.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') CATEGORY_PSEG, 
	nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, CUR_MSIVL.ORGANIZATION_ID, CUR_MSIVL.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),&P_ITEM_SEG)  ITEM_PSEG, 
	nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, CUR_MSIVL.ORGANIZATION_ID, CUR_MSIVL.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),&P_ITEM_SEG)  ITEM_SEGMENT, 
	BOM_CSTRITVR_XMLP_PKG.item_qtyformula(CUR_MSIVL.primary_uom_code, DECODE(
          CIQT.organization_id, 
          CIQT.from_organization_id, 
          FROM_MSI.primary_uom_code,
          TO_MSI.primary_uom_code
        ) ,CIQT.inventory_item_id ,ROUND(SUM(CIQT.rollback_qty),:P_QTY_PRECISION) ) ITEM_QTY, 
	BOM_CSTRITVR_XMLP_PKG.xport_cost_totalformula( ROUND((NVL(RSL.transportation_cost,0)/
                    (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),  ROUND(SUM(CIQT.rollback_qty),:P_QTY_PRECISION)) XPORT_COST_TOTAL, 
	BOM_CSTRITVR_XMLP_PKG.xfer_cost_totalformula(CASE
	  WHEN from_mp.process_enabled_flag <> to_mp.process_enabled_flag 
	  THEN
	    0
	  WHEN ((MMT.TRANSACTION_ACTION_ID=12 AND MMT.TRANSACTION_SOURCE_TYPE_ID=101) OR
		(MMT.TRANSACTION_ACTION_ID=21 AND MMT.TRANSACTION_SOURCE_TYPE_ID=7))
	  THEN 
	    0
	  ELSE
        decode( mip.MATL_INTERORG_TRANSFER_CODE, 
	             1,
	             ROUND((NVL(RSL.transfer_cost,0)/
                     (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),
	             2,
		     ROUND((NVL(RSL.transfer_cost,0)/
                     (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),
		     3,
                     ROUND((decode(mip.fob_point,
		                   2,( NVL(CICT.item_cost,0 )*:P_EXCHANGE_RATE1 *  rsl.TRANSFER_PERCENTAGE/100),
				   1,(nvl(mta.rate_or_amount,0)*:P_EXCHANGE_RATE1)
				   )/(gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), 
		           :P_EXT_PREC),
		     4,
		     ROUND((decode(mip.fob_point,
		                   2,( NVL(CICT.item_cost,0 )*:P_EXCHANGE_RATE1 * rsl.TRANSFER_PERCENTAGE/100),
				   1,(nvl(mta.rate_or_amount,0)*:P_EXCHANGE_RATE1)
				  )/(gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), 
			   :P_EXT_PREC) 
	       )
        END,  ROUND(SUM(CIQT.rollback_qty),:P_QTY_PRECISION)) XFER_COST_TOTAL, 
	BOM_CSTRITVR_XMLP_PKG.tot_costformula( CIQT.organization_id, FROM_MP.organization_id, OWN_MSI.primary_uom_code,  DECODE(
						  CIQT.organization_id, 
						  CIQT.from_organization_id, 
						  FROM_MSI.primary_uom_code,
						  TO_MSI.primary_uom_code
						)  , 
						CIQT.inventory_item_id, 
						ROUND(SUM(CIQT.rollback_qty),:P_QTY_PRECISION),  
						ROUND((NVL(CICT.item_cost,0)* :P_EXCHANGE_RATE1),:P_EXT_PREC), 
						ROUND((SUM(CIQT.rollback_qty * NVL(CICT.item_cost,0) )*:P_EXCHANGE_RATE1)/:ROUND_UNIT) * :ROUND_UNIT )      TOT_COST 
FROM    cst_inv_qty_temp CIQT,
        cst_inv_cost_temp CICT,
        rcv_shipment_headers RSH,
        rcv_shipment_lines RSL,
        cst_cost_groups CCG,
        mfg_lookups FOB_LU,
        mtl_parameters OWN_MP,
        mtl_parameters FROM_MP,
        mtl_parameters TO_MP,
        mtl_system_items_vl CUR_MSIVL,
        mtl_system_items OWN_MSI,
        mtl_system_items FROM_MSI,
        mtl_system_items TO_MSI,
        mtl_categories_kfv CUR_MCK,
        cst_organization_definitions COD,
        mtl_interorg_parameters mip,
        mtl_transaction_accounts mta,
		MTL_MATERIAL_TRANSACTIONS mmt
WHERE   CICT.organization_id (+) = CIQT.organization_id
AND     CICT.inventory_item_id (+) = CIQT.inventory_item_id
AND     CICT.cost_type_id (+) = CIQT.cost_type_id
AND     (   OWN_MP.primary_cost_method = 1 
        OR  NVL(CICT.cost_group_id,CIQT.cost_group_id) = CIQT.cost_group_id) 
AND     RSH.shipment_header_id = RSL.shipment_header_id
AND     RSL.shipment_line_id = CIQT.shipment_line_id
AND     RSH.shipped_date <= to_date(:P_AS_OF_DATE1,'DD-MON-YYYY HH24:MI:SS')
AND     mta.transaction_id (+) = RSL.mmt_transaction_id
AND     mta.accounting_line_type (+)= 11  
AND 	mmt.transaction_id (+) = RSL.mmt_transaction_id /*Added as part of Bug:34185106 change*/
AND     CCG.cost_group_id = CIQT.cost_group_id
AND     FOB_LU.lookup_type = 'MTL_FOB_POINT'
AND     FOB_LU.lookup_code = DECODE(CIQT.organization_id,CIQT.to_organization_id,1,2)
AND     OWN_MP.organization_id = CIQT.organization_id
AND     FROM_MP.organization_id = CIQT.from_organization_id
AND     TO_MP.organization_id = CIQT.to_organization_id
AND     CUR_MSIVL.organization_id = :P_ORG_ID
AND     CUR_MSIVL.inventory_item_id = CIQT.inventory_item_id
AND     OWN_MSI.organization_id = CIQT.organization_id
AND     OWN_MSI.inventory_item_id = CIQT.inventory_item_id
AND     FROM_MSI.organization_id = CIQT.from_organization_id
AND     FROM_MSI.inventory_item_id = CIQT.inventory_item_id
AND     TO_MSI.organization_id = CIQT.to_organization_id
AND     TO_MSI.inventory_item_id = CIQT.inventory_item_id
AND     CUR_MCK.category_id = CIQT.category_id
AND     mip.from_organization_id(+) = ciqt.from_organization_id
AND     mip.to_organization_id(+) = ciqt.to_organization_id
AND     COD.organization_id = FROM_MP.organization_id
GROUP 
BY      &P_ITEM_SEG,
        &P_CAT_SEG,
        CUR_MSIVL.description,
        CUR_MSIVL.primary_uom_code,
        OWN_MSI.primary_uom_code,
        DECODE(
            :P_SORT_OPTION,
            5, RSH.freight_carrier_code,
            6, RPAD(TO_MP.organization_code,5,' ')||'TO_OOD.organization_name', 
            7, RSH.shipment_num),
        CIQT.organization_id,
        FROM_MP.organization_id,
        FROM_MP.organization_code,
        TO_MP.organization_code,
        FOB_LU.meaning,
        RSH.shipment_num,
        RSH.shipped_date,
        RSH.expected_receipt_date+NVL(TO_MSI.postprocessing_lead_time,0),
        RSH.freight_carrier_code,
        RSH.waybill_airbill_num,
        RSH.num_of_containers,
        CCG.cost_group,
        DECODE(
          :P_ITEM_REVISION,
          1,
          RPAD(NVL(CIQT.revision,'   '),3,' '),
          NULL
        ),
        DECODE(
          CIQT.organization_id, 
          CIQT.from_organization_id, 
          FROM_MSI.primary_uom_code,
          TO_MSI.primary_uom_code
        ),
        ROUND((NVL(CICT.item_cost,0)*:P_EXCHANGE_RATE1), :P_EXT_PREC),
        CIQT.inventory_item_id,
        DECODE(:P_COST_TYPE_ID,CICT.cost_type_id,' ','*'),
	CASE
	  WHEN from_mp.process_enabled_flag <> to_mp.process_enabled_flag 
	  THEN
	    0
	  WHEN ((MMT.TRANSACTION_ACTION_ID=12 AND MMT.TRANSACTION_SOURCE_TYPE_ID=101) OR
		(MMT.TRANSACTION_ACTION_ID=21 AND MMT.TRANSACTION_SOURCE_TYPE_ID=7))
	  THEN 
	    0	
	  ELSE
        decode( mip.MATL_INTERORG_TRANSFER_CODE, 
	             1,
	             ROUND((NVL(RSL.transfer_cost,0)/
                     (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),
	             2,
		     ROUND((NVL(RSL.transfer_cost,0)/
                     (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC),
		     3,
                     ROUND((decode(mip.fob_point,
		                   2,( NVL(CICT.item_cost,0 )*:P_EXCHANGE_RATE1 * rsl.TRANSFER_PERCENTAGE/100),
				   1,(nvl(mta.rate_or_amount,0)*:P_EXCHANGE_RATE1)
				   )/(gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), 
		           :P_EXT_PREC),
		     4,
		     ROUND((decode(mip.fob_point,
		                   2,( NVL(CICT.item_cost,0 )*:P_EXCHANGE_RATE1 * rsl.TRANSFER_PERCENTAGE/100),
				   1,(nvl(mta.rate_or_amount,0)*:P_EXCHANGE_RATE1)
				  )/(gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), 
			   :P_EXT_PREC) 
	       )
        END,
        ROUND((NVL(RSL.transportation_cost,0)/
                    (gl_currency_api.get_rate(COD.set_of_books_id,:P_CURRENCY_CODE,sysdate,:P_CURR_TYPE))), :P_EXT_PREC)
					--added
					,
					fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', CUR_MCK.STRUCTURE_ID, NULL, CUR_MCK.CATEGORY_ID, 'ALL', 'Y', 'VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', CUR_MCK.STRUCTURE_ID, NULL, CUR_MCK.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, CUR_MSIVL.ORGANIZATION_ID, CUR_MSIVL.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, CUR_MSIVL.ORGANIZATION_ID, CUR_MSIVL.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  
having ROUND(SUM(CIQT.rollback_qty),:P_QTY_PRECISION) <> 0
order by  SORT_COLUMN,ITEMCAT,ITEM_PSEG,OWNING_ORG_PUOM_CODE,FROM_ORG_ID,FROM_ORG_CODE,
TO_ORG_CODE
Parameter Name SQL text Validation
Title 
 
 Char
Report Option 
 
 LOV Oracle
Sort Option 
 
 LOV Oracle
Cost Type 
 
 LOV Oracle
As of Date 
 
 DateTime
Item From 
 
 Char
Item To 
 
 Char
Category Set 
 
 LOV Oracle
Category From 
 
 Char
Category To 
 
 Char
Currency 
 
 LOV Oracle
Exchange Rate 
 
 LOV Oracle
Include Pending Receipts 
 
 LOV Oracle
Include Shipments 
 
 LOV Oracle
Only Display Inventory You Own 
 
 LOV Oracle
Quantities By Revision 
 
 LOV Oracle
Display Zero Costs Only 
 
 LOV Oracle
Include Expense Items 
 
 LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: