R12 Workflow SQL Queries

select * from apps.wf_notifications where subject like '%1555213%'

R12 File Versions

select * from ad_files where filename like 'apgdf%'

SELECT * FROM AD_FILE_VERSIONS WHERE file_id IN (464840)

select name, text
from all_source
where owner = 'APPS'
AND NAME = 'AP_ACCTG_DATA_FIX_PKG'
and line = 2;

FND Technical SQL Queries

FND Technical SQL Queries

SELECT oracle_process_id FROM fnd_concurrent_requests WHERE request_id=:Request_id

select * from fnd_user where employee_id=:Employee_id

select * from FND_GRANTS where GRANTEE_KEY=:User_Name

SELECT FU.USER_NAME
FROM APPS.PER_ALL_PEOPLE_F A ,
  APPS.FND_USER FU
WHERE A.PERSON_ID  = 161133
AND FU.EMPLOYEE_ID = a.PERSON_ID
AND sysdate BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE

SELECT MAX(log_sequence)
           FROM FND_LOG_MESSAGES;

 SELECT log.module, log.message_text
             FROM FND_LOG_MESSAGES LOG,
                          FND_LOG_TRANSACTION_CONTEXT CON
             WHERE CON.TRANSACTION_ID = '29238894'
             AND CON.TRANSACTION_TYPE = 'REQUEST'
             AND CON.TRANSACTION_CONTEXT_ID = LOG.TRANSACTION_CONTEXT_ID
             ORDER BY LOG.LOG_SEQUENCE

R12 DBA Queries

R12 DBA Queries

SELECT oracle_process_id FROM fnd_concurrent_requests WHERE request_id=<<conc request id>>

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    where 1=1
    AND req.request_id = :Request ID
    AND req.oracle_process_id=proc.spid(+)
    AND proc.addr = ses.paddr(+)
    AND dest.NAME='user_dump_dest'
    AND dbnm.NAME='db_name'
    AND req.concurrent_program_id = prog.concurrent_program_id
    and REQ.PROGRAM_APPLICATION_ID = PROG.APPLICATION_ID
    AND prog.application_id = execname.application_id

SELECT * FROM V$PARAMETER WHERE name LIKE '%dump%'


ALTER SYSTEM KILL SESSION 'sid,serial#'

   SELECT a.object_id,
  a.session_id,
  SUBSTR(b.object_name, 1, 40)
FROM v$locked_object a,
  dba_objects b
WHERE A.object_id = b.object_id
AND b.object_name LIKE 'AP_%'
ORDER BY b.object_name;


SELECT l.*,
  o.owner object_owner,
  o.object_name
FROM SYS.all_objects o,
  v$lock l
WHERE l.TYPE       = 'TM'
AND o.object_id    = l.id1
AND o.object_name IN ('AP_INVOICES_ALL', 'AP_INVOICE_LINES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');

  select s1.username || '-' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '-' || s2.machine || ' ( SID=' || s2.sid || ' )' AS blocking_status
  from v$lock l1,
       v$session s1,
       v$lock l2,
       v$session s2
 where s1.sid=l1.sid
   and s2.sid=l2.sid
   and l1.BLOCK = 1
   and l2.request > 0
   AND l1.id1 = l2.id1
   and l2.id2 = l2.id2;


SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, dba_Objects B
WHERE A.Object_ID = B.Object_ID;



select * from v$database