PA Generate Asset Lines for a Range of Projects

Description

Categories: BI Publisher
Columns: Aex Project Id, Aex Project Number, Aex Asset Allocation Method, Aex Error Type, Aex Asset Line Id, Aex Task Number, Aex Asset Name, Aex Asset Id, Aex Cap Event Id, Aex Event Number ...

Application: Projects
Source: PRC: Generate Asset Lines for a Range of Projects (XML)
Short Name: PAXCPGAL_XML
DB package: PA_PAXCPGAL_XMLP_PKG

Copy

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))