BOM Department

SELECT dept_res.department_id               department_id2,
                   dept_res.resource_id                           resource_id1,
                  nvl(dept_res.share_from_dept_id,dept_res.department_id) linking_dept,
                   res.resource_code                                Name,
                   res.description                                      Description,
                   res.unit_of_measure                             UOM,
                   lu1.meaning                                           Allow_Sharing,
                   dept.department_code                         Share_From_Department,
                   ROUND(dept_res.capacity_units, :P_qty_precision)   Capacity_Units,
                   lu3.meaning                                           Available_24_Hours,
                   dept_res.resource_group_name        resource_group_name
            FROM   bom_department_resources dept_res,
                   bom_resources            res,
                   mfg_lookups              lu1,
                   bom_departments          dept,
                   mfg_lookups              lu3
            WHERE  dept_res.resource_id = res.resource_id
              and  dept_res.share_capacity_flag = lu1.lookup_code and
                   lu1.lookup_type = 'SYS_YES_NO'
              and  dept_res.share_from_dept_id = dept.department_id(+)
              and  dept_res.available_24_hours_flag = lu3.lookup_code and
                   lu3.lookup_type = 'SYS_YES_NO'
             and dept_res.department_id = :department_id_01
            ORDER BY res.resource_code