SELECT pe.project_id aex_project_id,
pe.project_number aex_project_number,
pl2.meaning aex_asset_allocation_method,
pe.asset_or_project_error aex_error_type,
pe.project_asset_line_id aex_asset_line_id,
pt.task_number aex_task_number,
ppa.asset_name aex_asset_name,
pe.project_asset_id aex_asset_id,
pe.capital_event_id aex_cap_event_id,
pe.capital_event_number aex_event_number,
pe.event_name aex_eventname,
pl.lookup_code aex_error_code,
pl.meaning aex_error_message
FROM pa_asset_alloc_exceptions_v pe,
pa_project_assets ppa,
pa_tasks pt,
pa_lookups pl,
pa_lookups pl2
WHERE ppa.project_asset_id(+) = pe.project_asset_id
AND pe.request_id = :p_conc_request_id
AND pt.task_id = pe.task_id
AND pl.lookup_type = 'ASSET_ALLOCATION_EXCEPTIONS'
AND pl.lookup_code = pe.error_code
AND pl2.lookup_type = 'ASSET_ALLOCATION_METHOD'
AND pl2.lookup_code = pe.asset_allocation_method
AND (pe.event_name IS NULL
OR pe.event_name = nvl(:p_event_name,pe.event_name))
UNION ALL
SELECT pe.project_id aex_project_id,
pe.project_number aex_project_number,
pl2.meaning aex_asset_allocation_method,
pe.asset_or_project_error aex_error_type,
pe.project_asset_line_id aex_asset_line_id,
NULL,
ppa.asset_name aex_asset_name,
pe.project_asset_id aex_asset_id,
pe.capital_event_id aex_cap_event_id,
pe.capital_event_number aex_event_number,
pe.event_name aex_eventname,
pl.lookup_code aex_error_code,
pl.meaning aex_error_message
FROM pa_asset_alloc_exceptions_v pe,
pa_project_assets ppa,
pa_lookups pl,
pa_lookups pl2
WHERE ppa.project_asset_id(+) = pe.project_asset_id
AND pe.request_id = :p_conc_request_id
AND pe.task_id = 0
AND pl.lookup_type = 'ASSET_ALLOCATION_EXCEPTIONS'
AND pl.lookup_code = pe.error_code
AND pl2.lookup_type = 'ASSET_ALLOCATION_METHOD'
AND pl2.lookup_code = pe.asset_allocation_method
AND (pe.event_name IS NULL
OR pe.event_name = nvl(:p_event_name,pe.event_name)) |