General Queries

To Query Org related Information
================================

SELECT * FROM org_organization_definitions

SELECT * FROM hr_operating_units WHERE legal_entity_id=102 

SELECT * FROM hr_all_organization_units

To Query Concurrent Program Details
======================================

select * from fnd_concurrent_programs_tl where user_concurrent_program_name ='Vendor Hist Report'

select * from fnd_concurrent_requests where concurrent_program_id=49554

select * from fnd_responsibility_tl where responsibility_id=54348

Connect by Prior
================

select * from apps.fnd_concurrent_requests  connect by prior request_id=parent_request_id start with request_id=3972044

To find Concurrent Request Program Details
======================================

SELECT /*+RULE */
        TRUNC (
            ( ( (86400 * (SYSDATE - req.ACTUAL_START_DATE)) / 60) / 60) / 24)
            "Days",
           TRUNC ( ( (86400 * (SYSDATE - req.ACTUAL_START_DATE)) / 60) / 60)
         -   24
           * (TRUNC (
                   ( ( (86400 * (SYSDATE - req.ACTUAL_START_DATE)) / 60) / 60)
                 / 24))
            "Hrs",
           TRUNC ( (86400 * (SYSDATE - req.ACTUAL_START_DATE)) / 60)
         -   60
           * (TRUNC ( ( (86400 * (SYSDATE - req.ACTUAL_START_DATE)) / 60) / 60))
            "Min",
           TRUNC (86400 * (SYSDATE - req.ACTUAL_START_DATE))
         - 60 * (TRUNC ( (86400 * (SYSDATE - req.ACTUAL_START_DATE)) / 60))
            "Sec",
         --(sysdate - req.REQUESTED_START_DATE) "Total Time",
         SUBSTR (LTRIM (req.request_id), 1, 15) concreq,
         SUBSTR (look.meaning, 1, 10) rrreqph,
         SUBSTR (look1.meaning, 1, 10) reqst,
         SUBSTR (fcrv.requestor, 1, 9) REQUESTOR,
         vsess.inst_id,
         vsess.sid sid,
         vsess.serial# serial#,
         vsess.sql_id,
         vsess.module,
         fcrv.PROGRAM_SHORT_NAME,
         SUBSTR (fcrv.program, 1, 65) PROGRAM,
         SUBSTR (LTRIM (proc.oracle_process_id), 1, 15) opid,
         SUBSTR (vsess.username, 1, 10) dbuser,
         SUBSTR (vproc.spid, 1, 10) svrproc,
         SUBSTR (proc.os_process_id, 1, 15) clproc,
         req.argument_text,
         req.REQUESTED_START_DATE "Req StartTime",
         --TO_CHAR(req.REQUESTED_START_DATE,'DD.MM.YYYY:HH24:MI:SS') "req.REQUESTED_START_DATE",
         TO_CHAR (SYSDATE, 'MM/DD/YYYY:HH:MI:SS AM') "Present Time"
    FROM apps.fnd_conc_req_summary_v fcrv,
         apps.fnd_concurrent_requests req,
         apps.fnd_concurrent_processes proc,
         apps.fnd_lookups look,
         apps.fnd_lookups look1,
         gv$process vproc,
         gv$session vsess
   WHERE     fcrv.request_id = req.request_id
         AND req.controlling_manager = proc.concurrent_process_id
         AND REQ.oracle_process_id = VPROC.spid(+)
         AND vproc.addr = vsess.paddr(+)
         AND req.status_code = look.lookup_code
         AND look.lookup_type = 'CP_STATUS_CODE'
         AND req.phase_code = look1.lookup_code
         AND look1.lookup_type = 'CP_PHASE_CODE'
         AND look1.meaning = 'Running'
        -- and req.request_id=22858816
ORDER BY req.REQUESTED_START_DATE