Script for the BOM Routing Department Query in Oracle apps EBS R12

----- Script for the BOM Routing Department Query in Oracle apps EBS R12  ----


SELECT      (SELECT hou.NAME
            FROM hr_organization_units hou
           WHERE hou.organization_id = bd.organization_id) "Organization",        
         --bd.DEPARTMENT_ID,
         bd.department_code "Department Name",
         bd.description "Department Description",
         hl.location_code || ' - ' || hl.description LOCATION,        
         -- hl.ADDRESS_LINE_1 || ' '|| hl.ADDRESS_LINE_2 || ' '|| hl.ADDRESS_LINE_3  || ' '|| hl.POSTAL_CODE || ' '|| hl.TOWN_OR_CITY || ' '|| hl.REGION_2 || ' '|| hl.REGION_1 Full_Address,
         -- bdr.RESOURCE_ID ,
         br.resource_code "Resource Name",
         br.description "Resource Description",
         bdr.available_24_hours_flag "Available 24 hours",
         br.unit_of_measure "Resource UOM", bdr.share_capacity_flag "Share",
         bdr.capacity_units "Units"
    FROM bom_departments bd,
         hr_locations hl,
         bom_department_resources bdr,
         bom_resources br
   WHERE bd.location_id = hl.location_id(+)
     AND bd.department_id = bdr.department_id(+)
     AND bdr.resource_id = br.resource_id(+)
--and   bd.DEPARTMENT_ID =5014
     AND bd.organization_id = 85
ORDER BY 3