Query to get concurrent job completed and pending

SELECT fcr.request_id,

       fcpv.user_concurrent_program_name,

       fcpv.concurrent_program_name,

       fcpv.concurrent_program_id,

       fcr.status_code,

       fcr.phase_code

  FROM fnd_concurrent_programs_vl fcpv,

       fnd_executables fe,

       sys.dba_dependencies dd,

       fnd_concurrent_requests fcr

 WHERE     fcpv.executable_id = fe.executable_id

       AND fe.execution_method_code = 'i'

       AND SUBSTR (fe.execution_file_name,

                   1,

                     INSTR (fe.execution_file_name,

                            '.',

                            1,

                            1)

                   - 1) = UPPER (dd.name)

       AND dd.referenced_type IN ('view',

                                  'table',

                                  'trigger',

                                  'package')                -- add as required

       --and referenced_owner = 'xxcus'

       AND dd.referenced_name = UPPER ('&package_name')

       AND fcr.concurrent_program_id = fcpv.concurrent_program_id

       AND fcr.phase_code NOT IN ('c', 'p');