CAC Department Overhead Rates

select nvl(gl.short_name, gl.name) Ledger,
 haou.name Operating_Unit,
 mp.organization_code Org_Code,
 cct.cost_type Cost_Type,
 bd.department_code Department,
 br.resource_code Overhead,
 ml_basis.meaning Overhead_Basis,
 cdo.rate_or_amount Overhead_Rate,
 &segment_columns
 cdo.last_update_date Last_Update_Date
from bom_departments bd,
 cst_department_overheads cdo,
 bom_resources br,
 cst_cost_types cct,
 mtl_parameters mp,
 gl_code_combinations gcc,
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,
 hr_all_organization_units_vl haou2,
 gl_ledgers gl,
 mfg_lookups ml_basis
where mp.organization_id          = cdo.organization_id
and cdo.cost_type_id            = cct.cost_type_id
and cdo.department_id           = bd.department_id
and cdo.overhead_id             = br.resource_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1
and br.absorption_account       = gcc.code_combination_id
and ml_basis.lookup_code        = cdo.basis_type
and ml_basis.lookup_type        = 'CST_BASIS_SHORT'
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id         = mp.organization_id
and hoi.organization_id         = haou.organization_id -- this gets the organization name
and haou2.organization_id       = to_number (hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id                = to_number (hoi.org_information1) -- get the ledger_id
-- avoid selecting disabled inventory organizations
and sysdate < nvl(haou.date_to, sysdate + 1)
order by
 nvl(gl.short_name, gl.name), -- Ledger
 haou.name, -- Operating_Unit
 mp.organization_code, -- Org_Code
 cct.cost_type, -- Cost_Type
 bd.department_code, -- Department
 br.resource_code -- Overhead