select res.resource_id,
res.name,
ou.resource_organization_id,
substr(hrorg.name,1,60) name1,
ou.resource_effective_start_date,
decode(ou.resource_effective_end_date, to_date('31-12-4712', 'DD-MM-YYYY'), to_date(null), ou.resource_effective_end_date) resource_effective_end_date
from pa_resources res, pa_resources_denorm ou, hr_all_organization_units hrorg
where res.resource_id = ou.resource_id(+)
and ou.resource_organization_id = hrorg.organization_id(+)
and (decode(res.request_id,:p_conc_request_id,res.request_id,NULL) = :p_conc_request_id
or decode(ou.request_id,:p_conc_request_id,ou.request_id,NULL) = :p_conc_request_id) |