GMF OPM Lot Cost History

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: OPM Lot Cost History Report
Application: Process Manufacturing Financials
Source: OPM Lot Cost History Report (XML)
Short Name: GMFLCHST_XML
DB package: GMF_GMFLCHST_XMLP_PKG
Run GMF OPM Lot Cost History and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Legal Entity 
 
 LOV Oracle
Cost Type 
 
 LOV Oracle
From Organization 
 
 LOV Oracle
To Organization 
 
 LOV Oracle
P_FROM_ITEM 
 
 Char
P_TO_ITEM 
 
 Char
From Lot 
 
 LOV Oracle
To Lot 
 
 LOV Oracle
From Item Cost Category 
 
 Char
To Item Cost Category 
 
 Char
From Date 
 
 DateTime
To Date 
 
 DateTime
Currency 
 
 LOV Oracle
Exchange Rate 
 
 Number
Download
Related Blitz Reports
GMF OPM Lot Cost Detail OPM2 OPM WIP Value CAC Inventory Organization Summary GMF GL Item Cost Detail CAC Calculate Average Item Costs GL Account Analysis CAC Internal Order Shipment Margin GL Account Analysis (Distributions)
Blitz Report™

Blitz Report™ provides multiple benefits: