CST Intransit Value

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Intransit Value Report (XML)
Short Name: CSTRITVR_XML
DB package: BOM_CSTRITVR_XMLP_PKG
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
	  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
	  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
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 <= :P_AS_OF_DATE1
AND     mta.transaction_id (+) = RSL.mmt_transaction_id
AND     mta.accounting_line_type (+)= 11  
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
	  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
View Cost Privilege
 
Number
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
CST_SRS_COST_TYPE_DUMMY
 
Number
Chart of Accounts Id
 
Number
Organization Id
 
Number
Include Expense Items
 
LOV Oracle
Display Zero Costs Only
 
LOV Oracle
Quantities By Revision
 
LOV Oracle
Only Display Inventory You Own
 
LOV Oracle
Include Shipments
 
LOV Oracle
Include Pending Receipts
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Category To
 
Category From
 
Category Set
 
LOV Oracle
Item To
 
Item From
 
As of Date
 
DateTime
Cost Type
 
LOV Oracle
Sort Option
 
LOV Oracle
Report Option
 
LOV Oracle
Title
 
Ask a question