GMF OPM Lot Cost History

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Financials
Source: OPM Lot Cost History Report (XML)
Short Name: GMFLCHST_XML
DB package: GMF_GMFLCHST_XMLP_PKG
SELECT DISTINCT
	    mif.item_number, 
	    mp.organization_code organization_code, 
	    lc.lot_number lot_number, 
	    to_char(lcmat.cost_trans_date,'DD-MON-YY') cost_trans_date,
           lcmat.cost_trans_id	,
	    lcmat.cost_trans_qty, 
	    decode(lcmat.cost_trans_qty, 0,0, round((lcmat.total_trans_cost/lcmat.cost_trans_qty) * :p_exchange_rate_1,9) ) unit_trans_cost, 
	    round(lcmat.new_unit_cost*:p_exchange_rate_1,9) new_unit_cost, 
	    lcmat.new_onhand_qty, 
	    lcmat.cost_trans_uom, 
                    mtt.TRANSACTION_TYPE_NAME doc_type,
                    mmt.TRANSACTION_SOURCE_ID doc_id , 
                    mmt.TRX_SOURCE_LINE_ID line_id,
					GMF_GMFLCHST_XMLP_PKG.CF_SRC_TRANSFORMULA(mmt.TRANSACTION_SOURCE_ID,mmt.TRX_SOURCE_LINE_ID,mtt.TRANSACTION_TYPE_NAME,mp.organization_code) CF_SRC_TRANS,
		GMF_GMFLCHST_XMLP_PKG.CF_SRC_ONHAND_VALUEFORMULA(round(lcmat.new_unit_cost*:p_exchange_rate_1,9),lcmat.new_onhand_qty) CF_SRC_ONHAND_VALUE 
	FROM 
	    gmf_lot_costs lc, 
                    MTL_ITEM_FLEXFIELDS MIF,  	    
	    mtl_item_categories mic, 
	    mtl_categories_kfv mc, 
	    gmf_material_lot_cost_txns lcmat, 
	    mtl_parameters mp, 
	    hr_organization_information hoi,
                    mtl_material_transactions mmt,
                    mtl_transaction_types  mtt,
                    gmd_org_access_vw oa
	  WHERE hoi.org_information2 = :p_legal_entity_id 
          AND hoi.org_information_context = 'Accounting Information' 
          AND lc.cost_type_id = :p_cost_type_id 
          AND hoi.organization_id = lc.organization_id
          AND lc.inventory_item_id = mif.inventory_item_id 
          AND lc.organization_id = mif.organization_id 
          AND mic.inventory_item_id (+) = MIF.inventory_item_id 
          AND mic.organization_id (+) = MIF.organization_id
          AND mic.category_id = mc.category_id (+) 
          and mc.structure_id (+) = :p_structure_id 
          AND mp.organization_id = hoi.organization_id 
          AND mp.process_enabled_flag = 'Y' 
          AND LCMAT.TRANSACTION_ID = MMT.TRANSACTION_ID 
          AND LCMAT.COST_HEADER_ID = LC.HEADER_ID
          AND lcmat.transaction_id <> - lcmat.cost_trans_id 
          AND mmt.transaction_type_id = mtt.transaction_type_id 
          AND mp.organization_id = oa.organization_id 
          AND ( ( mc.concatenated_segments >= :p_from_cost_category 
          AND :p_from_cost_category IS NOT NULL ) OR ( :p_from_cost_category IS NULL ) ) 
          AND ( ( mc.concatenated_segments <= :p_to_cost_category AND :p_to_cost_category IS NOT NULL ) OR ( :p_to_cost_category IS NULL ) ) AND ( mif.item_number >= nvl ( :p_frm_item , mif.item_number ) ) AND ( mif.item_number <= nvl ( :p_to_item , mif.item_number ) ) AND ( lc.lot_number >= nvl ( :p_from_lot , lc.lot_number ) ) AND ( lc.lot_number <= nvl ( :p_to_lot , lc.lot_number ) ) AND ( mp.organization_code >= nvl ( :p_frm_orgn , mp.organization_code ) ) AND ( mp.organization_code <= nvl ( :p_to_orgn , mp.organization_code ) ) 
          AND ( lc.cost_date >= nvl ( :p_l_frm_date , lc.cost_date ) ) AND ( lc.cost_date <= nvl ( :p_l_to_date , lc.cost_date ) ) 
	  UNION ALL 
          SELECT DISTINCT
	    mif.item_number, 
	    mp.organization_code organization_code,  
	    lc.lot_number, 
	    to_char(lcmat.cost_trans_date,'DD-MON-YY') cost_trans_date, 
           lcmat.cost_trans_id	,
	    lcmat.cost_trans_qty, 
	    decode(lcmat.cost_trans_qty, 0, round((lcmat.total_trans_cost/decode(lcmat.new_onhand_qty, 0, 1, lcmat.new_onhand_qty))*1, 9), round((lcmat.total_trans_cost/lcmat.cost_trans_qty)*1, 9)) unit_trans_cost, 
	    round(lcmat.new_unit_cost*:p_exchange_rate_1,9) new_unit_cost, 
	    lcmat.new_onhand_qty, 
	    lcmat.cost_trans_uom, 
	    'LADJ' doc_type, 
	    -9 doc_id, 
	    trx.adjustment_id line_id,
		GMF_GMFLCHST_XMLP_PKG.CF_SRC_TRANSFORMULA(-9,trx.adjustment_id,'LADJ',mp.organization_code) CF_SRC_TRANS,
		GMF_GMFLCHST_XMLP_PKG.CF_SRC_ONHAND_VALUEFORMULA(round(lcmat.new_unit_cost*:p_exchange_rate_1,9),lcmat.new_onhand_qty) CF_SRC_ONHAND_VALUE
	FROM 
	    hr_organization_information hoi,
	    gmf_lot_costs lc, 
                    mtl_item_flexfields mif, 
	    gmf_material_lot_cost_txns lcmat, 
	    gmf_lot_cost_adjustments trx, 
	    mtl_item_categories mic, 
	    mtl_categories_kfv mc, 
	    mtl_parameters mp,
                    gmf_lot_cost_adjustment_dtls trxdet,
                    gmd_org_access_vw oa
	  WHERE hoi.org_information2 = :p_legal_entity_id 
          AND hoi.org_information_context = 'Accounting Information' 
          AND lc.cost_type_id = :p_cost_type_id 
          AND lc.organization_id = hoi.organization_id 
          AND mif.inventory_item_id = lc.inventory_item_id 
          AND mif.organization_id = lc.organization_id
          AND lcmat.cost_header_id = lc.header_id 
          AND lcmat.transaction_id = - lcmat.cost_trans_id 
          --AND trx.adjustment_id = lc.last_costing_doc_id --rnie bug17945655
          AND trx.new_cost_header_id = lc.header_id --rnie bug17945655
          AND trx.adjustment_date = lcmat.cost_trans_date --rnie bug17945655
          AND trx.adjustment_id = trxdet.adjustment_id 
          AND mic.inventory_item_id (+) = MIF.inventory_item_id 
          AND mic.organization_id (+) = MIF.organization_id 
          AND mic.category_id = mc.category_id (+) 
          and mc.structure_id (+) = :p_structure_id 
          AND mp.organization_id = hoi.organization_id 
          AND mp.process_enabled_flag = 'Y' 
          AND mp.organization_id = oa.organization_id 
          AND ( ( mc.concatenated_segments >= :p_from_cost_category AND :p_from_cost_category IS NOT NULL ) OR ( :p_from_cost_category IS NULL ) ) AND ( ( mc.concatenated_segments <= :p_to_cost_category AND :p_to_cost_category IS NOT NULL ) OR ( :p_to_cost_category IS NULL ) ) AND ( mif.item_number >= nvl ( :p_frm_item , mif.item_number ) ) AND ( mif.item_number <= nvl ( :p_to_item , mif.item_number ) ) AND ( lc.lot_number >= nvl ( :p_from_lot , lc.lot_number ) ) AND ( lc.lot_number <= nvl ( :p_to_lot , lc.lot_number ) ) AND ( mp.organization_code >= nvl ( :p_frm_orgn , mp.organization_code ) ) AND ( mp.organization_code <= nvl ( :p_to_orgn , mp.organization_code ) ) AND ( lc.cost_date >= nvl ( :p_l_frm_date , lc.cost_date ) ) AND ( lc.cost_date <= nvl ( :p_l_to_date , lc.cost_date ) ) 
          GROUP BY mif.item_number , mp.organization_code , lc.lot_number , to_char(lcmat.cost_trans_date,'DD-MON-YY') , lcmat.cost_trans_id ,lcmat.cost_trans_qty , lcmat.total_trans_cost , lcmat.new_unit_cost , lcmat.new_onhand_qty , lcmat.cost_trans_uom , trx.adjustment_id /*USKUMARI Bug 14091093*/
    UNION ALL
    SELECT DISTINCT
        mif.item_number,
        mp.organization_code organization_code,
        lc.lot_number,
        to_char(lcmat.cost_trans_date,'DD-MON-YY') ,
        lcmat.cost_trans_id	,
        lcmat.cost_trans_qty,
        decode(lcmat.cost_trans_qty, 0,sum(GLLCA.COSTED_QUANTITY), round((lcmat.total_trans_cost/lcmat.cost_trans_qty) * :p_exchange_rate,9) ) unit_trans_cost,
        round(lcmat.new_unit_cost*:p_exchange_rate,9) new_unit_cost,
        lcmat.new_onhand_qty,
        lcmat.cost_trans_uom,
        'LCADJ' doc_type,
        -9 doc_id,
        glat.adj_transaction_id line_id,
		GMF_GMFLCHST_XMLP_PKG.CF_SRC_TRANSFORMULA(-9,glat.adj_transaction_id,'LCADJ',mp.organization_code) CF_SRC_TRANS,						/* USKUMARI Bug 14091093 */
		GMF_GMFLCHST_XMLP_PKG.CF_SRC_ONHAND_VALUEFORMULA(round(lcmat.new_unit_cost*:p_exchange_rate_1,9),lcmat.new_onhand_qty) CF_SRC_ONHAND_VALUE   /* USKUMARI Bug 14091093 */
    FROM
        hr_organization_information hoi,
        gmf_lot_costs lc,
          mtl_item_flexfields mif,
        gmf_material_lot_cost_txns lcmat,
        gmf_LC_lot_cost_adjs gllca,
          gmf_lc_adj_transactions glat,
        mtl_item_categories mic,
        mtl_categories_kfv mc,
        mtl_parameters mp,
          gmd_org_access_vw oa
    WHERE
          hoi.org_information2= :p_legal_entity_id
          AND hoi.org_information_context = 'Accounting Information'
        AND lc.cost_type_id = :p_cost_type_id
        AND lc.organization_id = hoi.organization_id
        AND mif.inventory_item_id = lc.inventory_item_id
        AND mif.organization_id = lc.organization_id
        AND lcmat.cost_header_id = lc.header_id
        AND lcmat.transaction_id = -lcmat.cost_trans_id
        AND glat.adj_transaction_id = lc.last_costing_doc_id
        AND glat.adj_transaction_id = gllca.adj_transaction_id
        AND mic.inventory_item_id(+) = MIF.inventory_item_id
        AND mic.organization_id(+) = MIF.organization_id
        AND mic.category_id = mc.category_id(+)
        and mc.structure_id(+) = :p_structure_id
        AND mp.organization_id = hoi.organization_id
        AND mp.process_enabled_flag = 'Y'
        AND mp.organization_id = oa.organization_id
        AND ( ( mc.concatenated_segments >= :p_from_cost_category
                AND :p_from_cost_category IS NOT NULL )
                 OR (:p_from_cost_category IS NULL ))
        AND ((mc.concatenated_segments <= :p_to_cost_category
               AND :p_to_cost_category IS NOT NULL )
	       OR (:p_to_cost_category IS NULL ) )
        AND ( mif.item_number >= nvl(:p_frm_item,mif.item_number) )
        AND ( mif.item_number <= nvl(:p_to_item ,mif.item_number) )
        AND ( lc.lot_number >= nvl(:p_from_lot,lc.lot_number) )
        AND ( lc.lot_number <= nvl( :p_to_lot,lc.lot_number) )
	AND ( mp.organization_code >= nvl(:p_frm_orgn ,mp.organization_code) )
        AND ( mp.organization_code <= nvl(:p_to_orgn, mp.organization_code))
        AND ( lc.cost_date >= nvl(:p_l_frm_date,lc.cost_date) )
        AND ( lc.cost_date <= nvl(:p_l_to_date,lc.cost_date) )
        GROUP BY mif.item_number,
                 mp.organization_code,
                 lc.lot_number,
                 to_char(lcmat.cost_trans_date,'DD-MON-YY') , 
		   lcmat.cost_trans_id ,                 /*USKUMARI Bug 14091093*/
                 lcmat.cost_trans_qty,
                 lcmat.total_trans_cost,
                 lcmat.new_unit_cost,
                 lcmat.new_onhand_qty,
                 lcmat.cost_trans_uom,
                 glat.adj_transaction_id
          ORDER BY 1 ASC,2 ASC,3 ASC , 4 ASC, 5 ASC  /*USKUMARI Bug 14091093*/
Parameter Name SQL text Validation
Non Block Sql
 
LOV Oracle
SY ALL
 
Organization Context
 
Number
Dummy Structure ID
 
LOV Oracle
Exchange Rate
 
Number
Currency
 
LOV Oracle
To Date
 
DateTime
From Date
 
DateTime
To Item Cost Category
 
From Item Cost Category
 
To Lot
 
LOV Oracle
From Lot
 
LOV Oracle
P_TO_ITEM
 
P_FROM_ITEM
 
To Organization
 
LOV Oracle
From Organization
 
LOV Oracle
Cost Type
 
LOV Oracle
Legal Entity
 
LOV Oracle