SELECT fcr.request_id,

DECODE(fcpt.user_concurrent_program_name,

'Report Set',

'Report Set:' || fcr.description,

fcpt.user_concurrent_program_name) CONC_PROG_NAME,

argument_text PARAMETERS,

NVL2(fcr.resubmit_interval,

'PERIODICALLY',

NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,

DECODE(NVL2(fcr.resubmit_interval,

'PERIODICALLY',

NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),

'PERIODICALLY',

'EVERY ' || fcr.resubmit_interval || ' ' ||

fcr.resubmit_interval_unit_code || ' FROM ' ||

fcr.resubmit_interval_type_code || ' OF PREV RUN',

'ONCE',

'AT :' ||

TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),

'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,

fu.user_name USER_NAME,

requested_start_date START_DATE

FROM apps.fnd_concurrent_programs_tl fcpt,

apps.fnd_concurrent_requests fcr,

apps.fnd_user fu,

apps.fnd_conc_release_classes fcrc

WHERE fcpt.application_id = fcr.program_application_id

AND fcpt.concurrent_program_id = fcr.concurrent_program_id

AND fcr.requested_by = fu.user_id

AND fcr.phase_code = 'P'

AND fcr.requested_start_date > SYSDATE

AND fcpt.LANGUAGE = 'US'

AND fcrc.release_class_id(+) = fcr.release_class_id

AND fcrc.application_id(+) = fcr.release_class_app_id;

SELECT

    *

FROM

    (

        SELECT

            'N' select_flag,

            'N' hide_show,

            xgl.name              ledger,

            xte.ledger_id,

            le.name               legal_entity,

            xte.legal_entity_id,

            xlk.meaning           event_status,

            xe.event_status_code,

            xe.process_status_code,

            xecl.name             event_class,

            xecl.event_class_code,

            xetl.name             event_type,

            xetl.event_type_code,

            xte.transaction_number,

            xe.creation_date      event_creation_date,

            xe.event_id,

            'N' retrieve_flag,

            NULL transaction_id1_prompt,

            NULL transaction_id1_value,

            NULL transaction_id2_prompt,

            NULL transaction_id2_value,

            NULL transaction_id3_prompt,

            NULL transaction_id3_value,

            NULL transaction_id4_prompt,

            NULL transaction_id4_value,

            NULL transaction_id5_prompt,

            NULL transaction_id5_value,

            NULL transaction_id6_prompt,

            NULL transaction_id6_value,

            NULL transaction_id7_prompt,

            NULL transaction_id7_value,

            NULL transaction_id8_prompt,

            NULL transaction_id8_value,

            NULL transaction_id9_prompt,

            NULL transaction_id9_value,

            NULL transaction_id10_prompt,

            NULL transaction_id10_value,

            xe.event_date         event_date,

            xe.event_number       event_number,

            xte.entity_code,

            DECODE(xe.event_status_code, 'U', DECODE(xe.process_status_code, 'E', 'Error', 'I', 'Error', 'NoError'), 'NoError') error_switch

            ,

            xte.source_id_int_1,

            xte.source_id_int_2,

            xte.source_id_int_3,

            xte.source_id_int_4,

            xte.source_id_char_1,

            xte.source_id_char_2,

            xte.source_id_char_3,

            xte.source_id_char_4,

            xte.security_id_int_1,

            xte.security_id_int_2,

            xte.security_id_int_3,

            xte.security_id_char_1,

            xte.security_id_char_2,

            xte.security_id_char_3,

            xte.valuation_method,

            xlk.lookup_code       display_status_code,

            xe.application_id,

            xe.on_hold_flag,

            xlk1.meaning          on_hold_status,

            xe.transaction_date,

            xe.transaction_date   transaction_date_from,

            xe.transaction_date   transaction_date_to

        FROM

            xla_events                 xe,

            xle_entity_profiles        le,

            xla_transaction_entities   xte,

            gl_ledgers                 xgl,

            xla_event_classes_tl       xecl,

            xla_event_types_tl         xetl,

            xla_lookups                xlk,

            xla_lookups                xlk1

        WHERE

            xe.entity_id = xte.entity_id

            AND xte.application_id = xe.application_id

            AND xte.ledger_id = xgl.ledger_id

            AND xte.legal_entity_id = le.legal_entity_id (+)

            AND xetl.application_id = xe.application_id

            AND xetl.event_type_code = xe.event_type_code

            AND xetl.language = userenv('LANG')

            AND xecl.application_id = xetl.application_id

            AND xecl.entity_code = xetl.entity_code

            AND xecl.event_class_code = xetl.event_class_code

            AND xecl.language = userenv('LANG')

            AND xlk.lookup_type = 'XLA_EVENT_DISPLAY_STATUS'

            AND xlk.lookup_code = DECODE(xe.event_status_code, 'I', 'I', 'N', 'N', 'P', 'A', DECODE(xe.process_status_code, 'U', 'U'

            , 'D', 'D', 'R', 'R', 'E'))

            AND xlk1.lookup_type = 'XLA_YES_NO'

            AND xlk1.lookup_code = xe.on_hold_flag

            AND xe.application_id = P_Application_Id 

            AND xgl.object_type_code = 'L'

            AND xgl.le_ledger_type_code = 'L'

            AND xgl.ledger_category_code IN (

                'NONE',

                'PRIMARY',

                P_Leder_Id

            )

    ) qrslt

WHERE

    ( entity_code = P_ENTITY_CODE

      AND ledger_id = P_ledger_id

      AND nvl(source_id_int_1, - 99) = P_source_id_int_1);