GMF OPM Lot Cost History

Description
Categories: BI Publisher
Columns: Item Number, Organization Code, Lot Number, Cost Trans Date, Cost Trans Id, Cost Trans Qty, Unit Trans Cost, New Unit Cost, New Onhand Qty, Cost Trans Uom ...
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