Scheduled Concurrent Program Details

Now it is time to explore what concurrent jobs are scheduled

This is my answer :-

This will give the concurrent program details, schedule frequency, from which responsibility they are executing and requester etc. etc..


     SELECT V.request_id,
         (SELECT flv_phase.meaning
            FROM fnd_lookup_values_vl flv_phase
           WHERE     1 = 1
                 AND flv_phase.lookup_type = 'CP_PHASE_CODE'
                 AND flv_phase.enabled_flag = 'Y'
                 AND flv_phase.lookup_code = v.phase_code
                 AND view_application_id = 0)
            phase,
         (SELECT flv_status.meaning
            FROM fnd_lookup_values_vl flv_status
           WHERE     1 = 1
                 AND flv_status.lookup_type = 'CP_STATUS_CODE'
                 AND flv_status.enabled_flag = 'Y'
                 AND flv_status.lookup_code = v.status_code
                 AND view_application_id = 0)
            status_code,
         V.request_date,
         fu.user_name requestor,fu.description,
         V.requested_start_date,
         fres.responsibility_name,
         V.parent_request_id,
         V.actual_start_date,
         V.actual_completion_date,
         DECODE (fcp.user_concurrent_program_name, 'Report Set', 'Report Set:' || V.description, fcp.user_concurrent_program_name) CONC_PROG_NAME,
         NVL2 (V.resubmit_interval, 'PERIODICALLY', NVL2 (V.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
         DECODE (NVL2 (V.resubmit_interval, 'PERIODICALLY', NVL2 (V.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
                 'PERIODICALLY', 'EVERY ' || V.resubmit_interval || ' ' || V.resubmit_interval_unit_code || ' FROM ' || V.resubmit_interval_type_code || ' OF PREV RUN',
                 'ONCE', 'AT :' || TO_CHAR ( V.requested_start_date, 'DD-MON-RR HH24:MI'),
                 'EVERY: ' || fcrc.class_info)
            PROG_SCHEDULE,
         V.argument_text
    FROM apps.fnd_conc_release_classes fcrc, FND_CONCURRENT_REQUESTS V, Fnd_concurrent_programs_vl fcp, fnd_user fu,fnd_responsibility_vl fres
   WHERE     phase_code = 'P'
         AND V.status_code IN ('I', 'Q')
         AND (NVL (V.request_type, 'X') != 'S')        
         AND fcrc.release_class_id(+) = V.release_class_id
         AND v.concurrent_program_id = fcp.concurrent_program_id
         AND v.requested_by = fu.user_id
         AND fcrc.application_id(+) = V.release_class_app_id
         and fres.responsibility_id=v.responsibility_id
ORDER BY REQUEST_ID  desc