GMF OPM Lot Cost Detail

SELECT item_number, 
        organization_id,
        organization_code,
        item_desc, 
        lot_number, 
        lot_desc, 
        cost_level, 
        component_code, 
        analysis_code,
        component_cost, 
        uom, 
        sequence,
        component_id,
        component_desc,
        test
FROM
(SELECT DISTINCT iim.item_number item_number,
        lc.organization_id,
        mp.organization_code,
        iim.DESCRIPTION item_desc,
        lm.lot_number lot_number,
        lm.DESCRIPTION lot_Desc,
        decode(lcd.cost_level,0,'Unit Cost',1,'Lower Level Cost','None') cost_level,
        cmm.cost_cmpntcls_code component_code,
        lcd.cost_analysis_code analysis_code,
        lcd.component_cost * :p_exchange_rate component_cost,
        iim.primary_uom_code uom,
        cmm.sort_sequence sequence,
        cmm.cost_cmpntcls_id component_id,
        cmm.cost_cmpntcls_desc component_desc,
        decode(nvl(lc.final_cost_flag,0), 1, null, 0, '***') test,
        rank() over (Partition by lc.inventory_item_id, lc.organization_id, lc.lot_number, lc.cost_mthd_code
        order by lc.cost_date desc, lc.header_id desc) lot_cost_rank
 FROM   mtl_item_flexfields iim,
        gmf_lot_costs lc,
        gmf_lot_cost_details lcd,
        cm_cmpt_mst cmm,
        mtl_lot_numbers lm,
        mtl_parameters mp,
        hr_organization_information hoi,
        mtl_categories_kfv mckfv,
        mtl_item_categories mic,
       gmd_org_access_vw oa
  WHERE
       lc.cost_type_id = :p_cost_type_id 
   AND  hoi.org_information2 = :p_legal_entity_id
   AND  iim.inventory_item_id = lm.inventory_item_id 
   AND  iim.organization_id = lc.organization_id
   AND  lm.organization_id = lc.organization_id                     
   AND  lm.lot_number = lc.lot_number
   AND  iim.inventory_item_id = lc.inventory_item_id
   AND  cmm.cost_cmpntcls_id = lcd.cost_cmpntcls_id
   AND  lcd.delete_mark = 0
   AND  lc.delete_mark = 0
   AND  lc.header_id = lcd.header_id
   AND  hoi.org_information_context = 'Accounting Information'
   AND  hoi.organization_id = lc.organization_id
   AND  mp.organization_id = lc.organization_id
   AND mic.inventory_item_id(+) = iim.inventory_item_id 
   AND mic.organization_id(+) = iim.organization_id 
   AND mic.category_id = mckfv.category_id(+)
   and mckfv.structure_id(+) = :p_structure_id
   AND iim.organization_id = oa.organization_id
        &organizationcp
        &itemcp
        &CostCategoryCP
        &LotCP
ORDER BY 1,2,4,9,5,6)
WHERE lot_cost_rank = 1