Script for the BOM Resource Query in Oracle Apps EBS R12

----- Script for the BOM Resource Query in Oracle Apps EBS R12 ----


SELECT ROWNUM sl_no,
       (SELECT hou.NAME
          FROM hr_organization_units hou
         WHERE hou.organization_id = br.organization_id) "Organization",
       br.resource_code, br.description, br.resource_type,
       br.unit_of_measure uom, br.autocharge_type charge_type,
       br.default_basis_type basis, br.cost_code_type osp_enable,
       (SELECT msib.segment1
          FROM mtl_system_items_b msib
         WHERE msib.inventory_item_id = br.purchase_item_id
           AND msib.organization_id = 85) item_code,
       (SELECT msib.description
          FROM mtl_system_items_b msib
         WHERE msib.inventory_item_id = br.purchase_item_id
           AND msib.organization_id = 85) item_description,
       br.allow_costs_flag cost_enable,
       (SELECT glc.concatenated_segments
          FROM gl_code_combinations_kfv glc
         WHERE code_combination_id = br.absorption_account)       absorption_account,
       (SELECT glc.concatenated_segments
          FROM gl_code_combinations_kfv glc
         WHERE code_combination_id =  br.rate_variance_account)    variance_account,
       (SELECT crc.resource_rate
          FROM cst_resource_costs crc
         WHERE crc.resource_id = br.resource_id
           AND crc.organization_id = 85) rate
  FROM bom_resources br, cst_activities ca
 WHERE ca.activity_id(+) = br.default_activity_id AND br.organization_id = 85