Useful Queries for Oracle Applications

[dr_gori]

This topic serves to put ORACLE Applications - ERP R11, R12, etc.

if you have more useful wages on Oracle Apps, post in this topic

[color=# FF0000] If the Select der Error that the table does not exist, try putting the Owner Apps before [/color]

Size = 150] List Concurrents and your parameters [/size]\r\n [[99]


List details of a REQUEST SET [/ SIZE] [[99 ]

Select all

SELECT rs.user_request_set_name "Request Set" 
, rs.request_set_name 
, rss.display_sequence Seq 
, cp.user_concurrent_program_name "Concurrent Program" 
, e.EXECUTABLE_NAME 
, e.execution_file_name 
, lv.meaning file_type 
,fat.application_name "Application Name" 
-- ,get_appl_name(e.application_id) "Application Name" 
FROM apps.fnd_request_sets_vl rs 
, apps.fnd_req_set_stages_form_v rss 
, applsys.fnd_request_set_programs rsp 
, apps.fnd_concurrent_programs_vl cp 
, apps.fnd_executables e 
, apps.fnd_lookup_values lv 
, apps.fnd_application_tl fat 
WHERE 1=1 
--and rs.application_id IN ( 20006 ) 
AND rs.application_id = rss.set_application_id 
AND rs.request_set_id = rss.request_set_id 
AND e.APPLICATION_ID =FAT.APPLICATION_ID 
AND rss.set_application_id = rsp.set_application_id 
AND rss.request_set_id = rsp.request_set_id 
AND rss.request_set_stage_id = rsp.request_set_stage_id 
AND rsp.program_application_id = cp.application_id 
AND rsp.concurrent_program_id = cp.concurrent_program_id 
AND cp.executable_id = e.executable_id 
AND cp.executable_application_id = e.application_id 
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE' 
AND lv.lookup_code = e.execution_method_code 
and lv.language='US' 
and fat.language='US' 
AND rs.end_date_active IS NULL 
AND rs.request_set_name like  'XXGL1234_SET'  --:p_request_set_name 
ORDER BY 1,2;

[dr_gori]

List Responsibilities Informing the user \r\n

[[99]
Example:

Select all

SQL> / 
 
         USER_ID USER_NAME            RESPONSIBLITY             APPLICATION          RESPONSIBILITY_ID SOB 
---------------- -------------------- ------------------------- -------------------- ----------------- --------------- 
            8561 THOMAS               GL AS_CTRP_UXD SUPERUSER  General Ledger                   50584 AS_CTRP_UXD 
            8561 THOMAS               AP AS_CTRP_UXD SUPERUSER  Payables                         50305 AS_CTRP_UXD 
            8561 THOMAS               PO AS_CTRP_UXD SUPERUSER  Purchasing                       50343 AS_CTRP_UXD 
 
SQL> 

placed Owner apps on all tables

[dr_gori]

Description of Value Sets / Lookups \r\n

[[99]
Example:

Select all

SQL> / 
 
FLEX_VALUE_SET_NAME  FLEX_VALUE DESCRIPTION 
-------------------- ---------- ----------------------------- 
BIS_YES_NO           N          No 
BIS_YES_NO           Y          Yes

For lookups, it is much easier:

Select all

select * 
  from FND_LOOKUP_VALUES 
 where enabled_flag = 'Y' 
   and lookup_type  like '%nome_lookup%';

Meet some values ??of the value-set, it is easy to find it. (In the case below, I know the field can have the value Booked, Closed, Canceled and Entered. (In fact, there are several others, but these are the ones I know)

Select all

SELECT *  
  FROM FND_LOOKUP_VALUES A    
WHERE  EXISTS (SELECT 1 FROM FND_LOOKUP_VALUES B WHERE A.LOOKUP_TYPE = B.LOOKUP_TYPE  AND LOOKUP_CODE='BOOKED' ) 
   AND EXISTS (SELECT 1 FROM FND_LOOKUP_VALUES B WHERE A.LOOKUP_TYPE = B.LOOKUP_TYPE  AND LOOKUP_CODE='CLOSED' ) 
   AND EXISTS (SELECT 1 FROM FND_LOOKUP_VALUES B WHERE A.LOOKUP_TYPE = B.LOOKUP_TYPE  AND LOOKUP_CODE='CANCELLED' ) 
   AND EXISTS (SELECT 1 FROM FND_LOOKUP_VALUES B WHERE A.LOOKUP_TYPE = B.LOOKUP_TYPE  AND LOOKUP_CODE='ENTERED' ) 

[dr_gori]

X Responsibility X under \r\n

Select all

SELECT /*+ ORDERED */ DISTINCT A.CONCURRENT_PROGRAM_NAME, 
A.USER_CONCURRENT_PROGRAM_NAME,C.REQUEST_GROUP_NAME,D.RESPONSIBILITY_NAME,F.PROFILE_OPTION_VALUE SOB_ID,H.PROFILE_OPTION_VALUE SOB_NAME 
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, 
     APPS.FND_REQUEST_GROUP_UNITS B, 
     APPS.FND_REQUEST_GROUPS C, 
     APPS.FND_RESPONSIBILITY_VL D, 
     APPS.FND_PROFILE_OPTIONS_VL E, 
     APPS.FND_PROFILE_OPTION_VALUES F, 
     APPS.FND_PROFILE_OPTIONS_VL G, 
     APPS.FND_PROFILE_OPTION_VALUES H 
WHERE (UPPER(USER_CONCURRENT_PROGRAM_NAME) LIKE UPPER('%&PROGRAM_NAME%') 
OR CONCURRENT_PROGRAM_NAME LIKE UPPER('%&PROGRAM_NAME%')) 
AND B.REQUEST_UNIT_ID = A.CONCURRENT_PROGRAM_ID 
AND C.APPLICATION_ID = B.APPLICATION_ID 
AND C.REQUEST_GROUP_ID = B.REQUEST_GROUP_ID 
AND D.REQUEST_GROUP_ID = C.REQUEST_GROUP_ID 
AND E.PROFILE_OPTION_NAME = 'GL_SET_OF_BKS_ID' 
AND G.PROFILE_OPTION_NAME = 'GL_SET_OF_BKS_NAME' 
AND F.PROFILE_OPTION_ID = E.PROFILE_OPTION_ID 
AND F.LEVEL_VALUE_APPLICATION_ID = D.APPLICATION_ID 
AND F.LEVEL_VALUE = D.RESPONSIBILITY_ID 
AND H.PROFILE_OPTION_ID = G.PROFILE_OPTION_ID 
AND H.LEVEL_VALUE_APPLICATION_ID = D.APPLICATION_ID 
AND H.LEVEL_VALUE = D.RESPONSIBILITY_ID 
ORDER BY 1,3,4

The same SQL but for Request_Set

Select all

SELECT /*+ ORDERED */ DISTINCT A.request_set_NAME, 
A.USER_request_set_NAME,C.REQUEST_GROUP_NAME,D.RESPONSIBILITY_NAME,F.PROFILE_OPTION_VALUE SOB_ID,H.PROFILE_OPTION_VALUE SOB_NAME 
FROM APPS.FND_request_sets_VL A, 
     APPS.FND_REQUEST_GROUP_UNITS B, 
     APPS.FND_REQUEST_GROUPS C, 
     APPS.FND_RESPONSIBILITY_VL D, 
     APPS.FND_PROFILE_OPTIONS_VL E, 
     APPS.FND_PROFILE_OPTION_VALUES F, 
     APPS.FND_PROFILE_OPTIONS_VL G, 
     APPS.FND_PROFILE_OPTION_VALUES H 
WHERE (UPPER(USER_request_set_NAME) LIKE UPPER('%&PROGRAM_NAME%') 
OR request_set_NAME LIKE UPPER('%&PROGRAM_NAME%')) 
AND B.REQUEST_UNIT_ID = A.request_set_ID 
AND C.APPLICATION_ID = B.APPLICATION_ID 
AND C.REQUEST_GROUP_ID = B.REQUEST_GROUP_ID 
AND D.REQUEST_GROUP_ID = C.REQUEST_GROUP_ID 
AND E.PROFILE_OPTION_NAME = 'GL_SET_OF_BKS_ID' 
AND G.PROFILE_OPTION_NAME = 'GL_SET_OF_BKS_NAME' 
AND F.PROFILE_OPTION_ID = E.PROFILE_OPTION_ID 
AND F.LEVEL_VALUE_APPLICATION_ID = D.APPLICATION_ID 
AND F.LEVEL_VALUE = D.RESPONSIBILITY_ID 
AND H.PROFILE_OPTION_ID = G.PROFILE_OPTION_ID 
AND H.LEVEL_VALUE_APPLICATION_ID = D.APPLICATION_ID 
AND H.LEVEL_VALUE = D.RESPONSIBILITY_ID 
ORDER BY 1,3,4

[dr_gori]

Display Request Run, On Hold and Pendants \r\n

Select all

SELECT   fcr.request_id, 
         DECODE (fcr.phase_code, 
                 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', fl_pend.meaning), 
                 fl_pend.meaning 
                ) phase, 
         DECODE (fcr.phase_code, 
                 'P', DECODE (fcr.hold_flag, 
                              'Y', 'On Hold', 
                              DECODE (SIGN (fcr.requested_start_date - SYSDATE), 
                                      1, 'Scheduled', 
                                      fl_stat.meaning 
                                     ) 
                             ), 
                 fl_stat.meaning 
                ) status, 
         fcpt.user_concurrent_program_name, fcr.increment_dates, 
         fcr.resubmit_interval, fcr.resubmit_interval_unit_code, 
         fcr.resubmit_interval_type_code, parent_request_id, 
         fcr.requested_start_date, fu.user_name requested_by 
    FROM fnd_concurrent_requests fcr, 
         fnd_concurrent_programs_tl fcpt, 
         fnd_lookups fl_pend, 
         fnd_lookups fl_stat, 
         fnd_user fu 
   WHERE 1 = 1 
     AND fcpt.concurrent_program_id = fcr.concurrent_program_id 
     AND fcpt.LANGUAGE = USERENV ('LANG') 
     AND fcr.phase_code = fl_pend.lookup_code 
     AND fl_pend.lookup_type = 'CP_PHASE_CODE' 
     AND fcr.status_code = fl_stat.lookup_code 
     AND fl_stat.lookup_type = 'CP_STATUS_CODE' 
     AND fl_pend.meaning != 'Completed' 
     AND fu.user_id = fcr.requested_by 
ORDER BY fcr.request_id DESC

[dr_gori]

Displays version and patch information applied \r\n

Select all

SELECT   SUBSTR (a.application_name, 1, 60) Application_Name 
       , SUBSTR (i.product_version, 1, 4) Version 
       , i.patch_level  
       , i.application_id  
       , i.last_update_date  
FROM     apps.fnd_product_installations i 
       , apps.fnd_application_all_view a 
WHERE    i.application_id = a.application_id 
ORDER BY a.application_name

Select all

SELECT applied_patch_id 
     , patch_name 
     , patch_type 
     , source_code 
     , creation_date 
     , last_update_date 
FROM   ad_applied_patches

[dr_gori]

[size = 150] description flexfield x table [/size]\r\n

Select all

 
--  
-- EX : GL_SETS_OF_BOOK 
-- 
SELECT distinct A.APPLICATION_TABLE_NAME, 
       B.DESCRIPTIVE_FLEX_CONTEXT_CODE, 
       B.APPLICATION_COLUMN_NAME, 
       B.END_USER_COLUMN_NAME, 
       B.FORM_LEFT_PROMPT, 
       C.FLEX_VALUE_SET_NAME, 
       C.FLEX_VALUE_SET_ID 
FROM FND_FLEX_VALUE_SETS C, 
     FND_DESCR_FLEX_COL_USAGE_VL B, 
     FND_DESCRIPTIVE_FLEXS_VL A 
WHERE A.APPLICATION_TABLE_NAME LIKE UPPER('%&TABLE_NAME%') 
AND A.APPLICATION_ID = B.APPLICATION_ID 
AND A.DESCRIPTIVE_FLEXFIELD_NAME = B.DESCRIPTIVE_FLEXFIELD_NAME  
AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID 
AND FORM_LEFT_PROMPT LIKE '%Type%' 
ORDER BY 1,2,3

[dr_gori]

Responder_Group / Menu / Forms [/ Size]
Query to list crossing between responsibilities, menus, functions and forms of the application

Select all

SELECT frt.responsibility_name, 
       fr.responsibility_key, 
       frg.REQUEST_GROUP_CODE, 
       frg.REQUEST_GROUP_NAME, 
       m.menu_name Menu, 
       fmt.user_menu_name, 
       fff.function_name, 
       fffn.user_function_name, 
       ff.form_name,  
       fffn.description, 
       fff.parameters 
  FROM fnd_menus m, 
       fnd_menus_tl fmt, 
       fnd_menu_entries fme, 
       fnd_form_functions fff, 
       fnd_form_functions_tl fffn, 
       fnd_form ff, 
       fnd_responsibility_tl frt, 
       fnd_responsibility fr, 
       fnd_request_groups frg 
 WHERE m.MENU_ID              = fme.MENU_ID 
   AND fmt.menu_id            = m.menu_id 
   AND fme.FUNCTION_ID       = fff.function_id 
   AND fff.form_id            = ff.form_id 
   AND fffn.function_id       = fff.function_id 
   AND frt.responsibility_id = fr.responsibility_id 
   AND fr.menu_id             = m.menu_id 
   AND frg.request_group_id   = fr.request_group_id 
    
   AND fff.function_NAME      = nvl('&&FUNCTION', fff.function_NAME) 
   AND ff.form_name           = nvl('&&FORM', ff.form_name)

[dr_gori]

List profile value

This select returns the value of the possible profiles on the various levels: site, responsibility, user, etc. [url=http://www.jiltin.com/index.php/oracle- ... lications/]Fonte

Select all

SELECT po.profile_option_name "NAME", 
po.USER_PROFILE_OPTION_NAME, 
        decode(to_char(pov.level_id), 
               '10001', 'SITE', 
               '10002', 'APP', 
               '10003', 'RESP', 
               '10005', 'SERVER', 
               '10006', 'ORG', 
               '10004', 'USER', '???') "LEV", 
        decode(to_char(pov.level_id), 
               '10001', '', 
               '10002', app.application_short_name, 
               '10003', rsp.responsibility_key, 
               '10005', svr.node_name, 
               '10006', org.name, 
               '10004', usr.user_name, 
               '???') "CONTEXT", 
        pov.profile_option_value "VALUE" 
 FROM   FND_PROFILE_OPTIONS_VL po, 
        FND_PROFILE_OPTION_VALUES pov, 
        fnd_user usr, 
        fnd_application app, 
        fnd_responsibility rsp, 
        fnd_nodes svr, 
        hr_operating_units org 
 WHERE  po.profile_option_name LIKE '%&&profile%' 
 AND    pov.application_id = po.application_id 
 AND    pov.profile_option_id = po.profile_option_id 
 AND    usr.user_id (+) = pov.level_value 
 AND    rsp.application_id (+) = pov.level_value_application_id 
 AND    rsp.responsibility_id (+) = pov.level_value 
 AND    app.application_id (+) = pov.level_value 
 AND    svr.node_id (+) = pov.level_value 
 AND    org.organization_id (+) = pov.level_value 
/* 
 AND decode(to_char(pov.level_id), 
               '10001', '', 
               '10002', app.application_short_name, 
               '10003', rsp.responsibility_key, 
               '10005', svr.node_name, 
               '10006', org.name, 
               '10004', usr.user_name, 
               '???') LIKE '%&&username%' 
*/ 
 ORDER BY "NAME", pov.level_id, "VALUE";

Another example: (source: Metalink ID 201945.1)

Select all

select p.profile_option_name SHORT_NAME, 
       n.user_profile_option_name NAME, 
       decode(v.level_id, 
               10001, 'Site', 
               10002, 'Application', 
               10003, 'Responsibility', 
               10004, 'User', 
               10005, 'Server', 
               10006, 'Org', 
               10007, decode(to_char(v.level_value2), '-1', 'Responsibility', 
                             decode(to_char(v.level_value), '-1', 'Server', 
                             'Server+Resp')), 
               'UnDef') LEVEL_SET, 
       decode(to_char(v.level_id), 
               '10001', '', 
               '10002', app.application_short_name, 
               '10003', rsp.responsibility_key, 
               '10004', usr.user_name, 
               '10005', svr.node_name, 
               '10006', org.name, 
               '10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key, 
                          decode(to_char(v.level_value), '-1', 
                            (select node_name from fnd_nodes 
                             where node_id = v.level_value2), 
                        (select node_name from fnd_nodes 
                         where node_id = v.level_value2)||'-'||rsp.responsibility_key)), 
               'UnDef') "CONTEXT", 
       v.profile_option_value VALUE 
      ,v.last_update_date 
from fnd_profile_options p, 
     fnd_profile_option_values v, 
     fnd_profile_options_tl n, 
     fnd_user usr, 
     fnd_application app, 
     fnd_responsibility rsp, 
     fnd_nodes svr, 
     hr_operating_units org 
where p.profile_option_id = v.profile_option_id (+) 
  and p.profile_option_name = n.profile_option_name 
  and upper(p.profile_option_name) in ( 
            select profile_option_name from fnd_profile_options_tl 
            where  upper(profile_option_name) like upper('%&profile_name%') 
            and    upper(profile_option_name) in (select profile_option_name 
              from   fnd_profile_options_tl 
              where  upper(user_profile_option_name) like upper('%&user_profile_name%'))) 
  and    usr.user_id (+) = v.level_value 
  and    rsp.application_id (+) = v.level_value_application_id 
  and    rsp.responsibility_id (+) = v.level_value 
  and    app.application_id (+) = v.level_value 
  and    svr.node_id (+) = v.level_value 
  and    org.organization_id (+) = v.level_value 
  --and   UPPER(n.user_profile_option_name)  like '%FORM%' 
  order by  short_name, user_profile_option_name, level_id, level_set;

: -O

[alexrsilva]

Rafael,
see if that's what you want.

Select all

 
SELECT FA.APPLICATION_ID,  
       FA.APPLICATION_SHORT_NAME, 
       FRG.REQUEST_GROUP_NAME, 
       FRG.DESCRIPTION 
FROM   FND_CONCURRENT_PROGRAMS FNP, 
       FND_APPLICATION FA, 
       FND_REQUEST_GROUPS FRG 
WHERE  FNP.APPLICATION_ID = FA.APPLICATION_ID 
AND    FRG.APPLICATION_ID = FA.APPLICATION_ID 
AND    FNP.CONCURRENT_PROGRAM_NAME LIKE '%CONCURRENT_NAME%'; 

[madmax]

Below are some useful ones in the programming in apps:

Select all

 
-> FND_PROFILE.VALUE('USER_ID') 
-- Recupera o ID do usuário logado no EBS (USER_ID da FND_USER) 
 
-> FND_PROFILE.VALUE('USERNAME') 
-- Recupera o nome do usuário logado no EBS (USER_NAME da FND_USER) 
 
-> FND_GLOBAL.CONC_REQUEST_ID ou FND_PROFILE.VALUE('REQUEST_ID') 
-- Recupera o ID do concurrent em execução (REQUEST_ID da FND_CONCURRENT_REQUESTS) 
 
-> FND_GLOBAL.CONC_PROGRAM_ID 
-- Recupera o ID do programa em execução (CONCURRENT_PROGRAM_ID da FND_CONCURRENT_PROGRAMS) 
 
-> FND_GLOBAL.PROG_APPL_ID 
-- Recupera o ID da aplicação do concurrent em execução (APPLICATION_ID da FND_CONCURRENT_PROGRAMS) 
 
-> FND_PROFILE.VALUE('ORG_ID') 
-- Recupera o ID da Organização setada da responsabilidade corrente 
 
-> FND_GLOBAL.RESP_NAME; 
-- Recupera o nome da responsabilidade corrente 
 
-> FND_CLIENT_INFO.SET_ORG_CONTEXT('1') 
-- Seta organização no banco (por sessão) 

Several others here: http://docs.oracle.com/cd/E18727_01/doc ... 457084.htm


ABS.

[dr_gori]

Trace files: \r\n

Select all

-- Local e arquivo TRACE da sua sessão atual  
SELECT value 
FROM v$diag_info 
WHERE name = 'Default Trace File';    
 
--Local dos TRACES da sua instância 
SELECT value 
FROM v$diag_info 
WHERE name = 'Diag Trace'; 
 
 
--Traces de cada processo 
SELECT pid, program, tracefile 
FROM v$process;

- Helps get trace location run by a competitor .

Select all

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 --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 
    AND prog.executable_id=execname.executable_id

[dr_gori]

org_id, organization_id, inv_id, inv_code, etc. \r\n

[[99]
other legal form:

Select all

select  organization_id, organization_code 
from mtl_parameters_view

To set the organization in R12:

Select all

Begin mo_global.set_policy_context('S',  83 );  end;

An example:

Select all

SQL> begin mo_global.set_policy_context('S', 83 ); end; 
  2  / 
 
PL/SQL procedure successfully completed 
 
SQL> select org_id, count(*) from ap_invoices group by org_id; 
 
          ORG_ID   COUNT(*) 
---------------- ---------- 
              83      30618 
 
 
****AGORA, O MESMO SELECT COM OUTRA ORG_ID *** 
 
SQL> begin mo_global.set_policy_context('S', 95 ); end; 
  2  / 
 
PL/SQL procedure successfully completed 
 
SQL> select org_id, count(*) from ap_invoices group by org_id; 
 
          ORG_ID   COUNT(*) 
---------------- ---------- 
              95       1825 
 
SQL>

[dr_gori]

List customizations made in EBS \r\n

In my company, all customizations have the XG prefix. (Some have the prefix xx, etc.)

Select all

 
-- Mostra todos objetos de BANCO: 
select OWNER, OBJECT_NAME, OBJECT_TYPE from all_objects where object_name like 'XG%' order by 1,2 
 
 
-- Todas TRIGGERS 
select owner, trigger_name, table_owner, table_name from all_triggers where trigger_name like 'XG%' 
 
 
-- Todos os DATA DEFINITIONS de XML PUBLISHER 
select APPLICATION_SHORT_NAME, DATA_SOURCE_CODE From apps.XDO_DS_DEFINITIONS_B where data_source_code like 'XG%' order by 1,2 
 
 
-- Todos TEMPLATE do XML PUBLISHER 
select APPLICATION_SHORT_NAME, TEMPLATE_CODE, TEMPLATE_TYPE_CODE  From apps.XDO_TEMPLATES_B where TEMPLATE_code like 'XG%' order by 1,2 
 
 
-- Todas concurrents programs 
select CONCURRENT_PROGRAM_NAME, USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_vl WHERE concurrent_program_name LIKE 'XG%' ORDER BY 1,2; 
 
 
-- Todos executáveis 
select executable_name,  DECODE(execution_method_code, 'I', 'STORED PROCEDURE' 
                                                     , 'H', 'SHELL' 
                                                     , 'P', 'ORACLE REPORTS' 
                                                     , execution_method_code) execution_method_code, execution_file_name, user_executable_name  
from apps.fnd_executables_vl where executable_name like 'XG%' 
 
 
-- Todos Request Sets  
select request_set_name, user_request_set_name from APPS.fnd_request_sets_vl where request_set_name like 'XG%' 
 
 
-- Todos forms novos 
select form_name, description from apps.fnd_form_vl where form_name like 'XG%' 
 
 
-- Personalizações nos Forms Core 
select a.function_name, a.description, a.form_name, b.user_name CRIADO_POR from APPS.FND_FORM_CUSTOM_RULES a, APPS.fnd_user b where a.created_by = b.user_id(+)

of course, there are still many other things, as
- Flex Fields (done directly in the deployment)
- Conj of values ??(may not have the XG extension, perhaps has how to see by the name of who created)
- Lookups
- Profile
- Menus
- Sources -> Example: Bar code
- Configuration files -> On Linux
- Permissions
- DBLinks
- etc.

[dr_gori]

Initialize Session in R11 and R12 [/ Size]

Before running queries in SQL * Plus, Toad, PLSQL Developer, etc., we have to initialize the session with

Below we have a select useful for R11: it "rides" the command, and just run.

Select all

select 'begin fnd_global.apps_initialize(' || 
       fu.user_id || ',' || 
       fr.responsibility_id || ',' || 
       fr.application_id || '); end;' || chr(10) || '/' 
from   fnd_user fu 
,      fnd_responsibility_tl fr 
where  fu.user_name = 'SYSADMIN' 
and    fr.responsibility_name = 'System Administrator';

Output:

Select all

begin fnd_global.apps_initialize(0,20420,1); end; 
/ 

and R12, we usually do this:

Select all

begin   APPS.mo_global.init('PO'); end;

]

[dr_gori]

Copying a user's liability to another \r\n

Just edit the user_from and user_to below:

Select all

DECLARE 
   
  resp_count NUMBER := 0; 
   
  CURSOR src_user_resp_details 
  IS 
    SELECT DISTINCT fa.application_short_name, 
      fr.responsibility_key                  , 
      fsg.security_group_key 
       FROM fnd_application fa      , 
      fnd_responsibility fr         , 
      fnd_user fu                   , 
      fnd_user_resp_groups_all furga, 
      fnd_security_groups fsg 
      WHERE  
      AND fu.user_name                        = 'USER FROM' 
      AND fu.user_id                          = furga.user_id 
      AND fa.application_id                   = fr.application_id 
      AND furga.responsibility_id             = fr.responsibility_id 
      AND furga.responsibility_application_id = fa.application_id 
      AND fsg.security_group_id               = furga.security_group_id 
      AND furga.end_date IS NULL; 
   
   
BEGIN 
  FOR user_resp_details_rec IN src_user_resp_details 
  LOOP 
    BEGIN 
      -- 
      fnd_user_pkg.addresp 
                 (username            => 'USER_TO', 
                  resp_app            => user_resp_details_rec.application_short_name, 
                  resp_key            => user_resp_details_rec.responsibility_key, 
                  security_group      => user_resp_details_rec.security_group_key, 
                  description         => NULL, 
                  start_date          => SYSDATE, 
                  end_date            => NULL 
                 ); 
       
      resp_count := resp_count + 1; 
       
 EXCEPTION 
    WHEN OTHERS THEN 
       
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM ); 
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name ); 
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key ); 
       
    END; 
  END LOOP; 
   
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' ); 
   
  COMMIT; 
 
END;

] TIP Provided by MUHAMMAD AFI Aamiri Madani . [/ I]

[Porva]

How to determine / consult User_ID, resp_id, resp_appl_id when using fnd_global.apps_initialize \r\n

I found this tip very interesting, get resp_id and RESP_APPL_ID By the responsibility name:

How to determine user_id, resp_id when use fnd_global.apps_initialize

Select all

You need to post this in the E-Buisness Suite forum. Also, you can do a web search to find out... 
Here's an quick search result http://www.notesbit.com/index.php/oracle-applications/setting-the-applications-context-fnd_globalapps_initialize-in-oracle-applications/ 
 
procedure APPS_INITIALIZE(user_id IN number,resp_id IN number,resp_appl_id IN number security_group_id IN number); 
 
USER_ID – The User ID number. 
RESP_ID – The ID number of the responsibility. 
RESP_APPL_ID – The ID number of the application TO which the responsibility belongs. 
SECURITY_GROUP_ID – The ID number of the security GROUP. This argument IS automatically defaulted BY the API. The caller should NOT pass a value FOR it. 
 
SELECT user_id, user_name, description FROM applsys.fnd_user 
 
SELECT application_id, responsibility_id, LANGUAGE, responsibility_name, created_by, creation_date, last_updated_by, last_update_date, last_update_login, description, source_lang, security_group_id FROM applsys.fnd_responsibility_tl 
 
User_ID = USER_ID 
 
Responsibility_ID = RESP_ID 
 
Responsibility_Application_ID = RESP_APPL_ID

99]]
Source: https://forums.oracle.com/thread/1108336

[Porva]

List Concurrents running: \r\n

Select all

SELECT fcr.request_id 
      ,DECODE (fcr.phase_code, 'P', DECODE (fcr.hold_flag, 'Y' 
                             , 'Inactive', fl_pend.meaning), fl_pend.meaning) phase 
      ,DECODE (fcr.phase_code, 'P', DECODE (fcr.hold_flag, 'Y', 'On Hold', DECODE (SIGN (fcr.requested_start_date - SYSDATE) 
                                                                                         ,1 , 'Scheduled', fl_stat.meaning)), fl_stat.meaning) status 
      ,fcpt.user_concurrent_program_name 
      ,fcr.increment_dates 
      ,fcr.resubmit_interval 
      ,fcr.resubmit_interval_unit_code 
      ,fcr.resubmit_interval_type_code 
      ,parent_request_id 
      ,fcr.requested_start_date 
      ,fu.user_name requested_by 
  FROM fnd_concurrent_requests    fcr 
      ,fnd_concurrent_programs_tl fcpt 
      ,fnd_lookups                fl_pend 
      ,fnd_lookups                fl_stat 
      ,fnd_user                   fu 
 WHERE 1 = 1 
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id 
   AND fcpt.LANGUAGE              = USERENV ('LANG') 
   AND fcr.phase_code             = fl_pend.lookup_code 
   AND fl_pend.lookup_type        = 'CP_PHASE_CODE' 
   AND fcr.status_code            = fl_stat.lookup_code 
   AND fl_stat.lookup_type        = 'CP_STATUS_CODE'    
   AND fu.user_id                 = fcr.requested_by 
   AND fl_pend.meaning            != 'Completed' 
 ORDER BY fcr.request_id DESC;

[dr_gori]

Code Description / Competitor [/ Size]

Status code:

Select all

select lookup_code, meaning from fnd_lookups   
where lookup_type='CP_STATUS_CODE' 
order by 1

Select all

A    Waiting 
B    Resuming 
C    Normal 
D    Cancelled 
E    Error 
G    Warning 
H    On Hold 
I     Normal 
M    No Manager 
P    Scheduled 
Q    Standby 
R      Normal 
S    Suspended 
T    Terminating 
U    Disabled 
W    Paused 
X    Terminated 
Z     Waiting

Other:

Select all

select lookup_type, lookup_code, meaning from fnd_lookups   
where lookup_type like 'CP%' 
order by 1,2

[dr_gori]

Query to find the Key-Flex-Fields \r\n

Select all

select  FIF.APPLICATION_ID  , 
        FIF.ID_FLEX_CODE    , 
        FIF.ID_FLEX_NAME    , 
        FIF.APPLICATION_TABLE_NAME , 
        FIF.DESCRIPTION     , 
        FIFS.ID_FLEX_NUM    , 
        FIFS.ID_FLEX_STRUCTURE_CODE  , 
        FIFSE.SEGMENT_NAME, 
        FIFSE.SEGMENT_NUM, 
        FIFSE.FLEX_VALUE_SET_ID 
from    FND_ID_FLEXS FIF    , 
        FND_ID_FLEX_STRUCTURES FIFS , 
        FND_ID_FLEX_SEGMENTS FIFSE 
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID 
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE 
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID 
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE 
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM 
and     FIF.ID_FLEX_CODE LIKE 'GL#' 
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

[Porva]

List scheduled concurrents. \r\n
Source: http://appselangovan.blogspot.com/2014/ ... grams.html
List of Scheduled Concurrent Programs Query
/ * Formatted on 7/17/2014 12:58:47 pm (QP5 v5.115.810.9015) * /

Select all

SELECT r.request_id, 
       p.user_concurrent_program_name 
       || CASE 
             WHEN p.user_concurrent_program_name = 'Report Set' 
             THEN 
                (SELECT ' - ' || s.user_request_set_name 
                 FROM apps.fnd_request_sets_tl s 
                 WHERE     s.application_id = r.argument1 
                       AND s.request_set_id = r.argument2 
                       AND language = 'US') 
             WHEN p.user_concurrent_program_name = 'Check Periodic Alert' 
             THEN 
                (SELECT ' - ' || a.alert_name 
                 FROM apps.alr_alerts a 
                 WHERE     a.application_id = r.argument1 
                       AND a.alert_id = r.argument2 
                       AND language = 'US') 
          END 
          concurrent_program_name, 
       DECODE (c.class_type, 
          'P', 'Periodic', 
          'S', 'On Specific Days', 
          'X', 'Advanced', 
          c.class_type) 
          schedule_type, 
       CASE 
          WHEN c.class_type = 'P' 
          THEN 
             'Repeat every ' 
             || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1) 
             || DECODE (SUBSTR (c.class_info, 
                                INSTR (c.class_info, ':', 1, 1) + 1, 
                                1 
                        ), 
                        'N', 
                        ' minutes', 
                        'M', 
                        ' months', 
                        'H', 
                        ' hours', 
                        'D', 
                        ' days' 
                ) 
             || DECODE (SUBSTR (c.class_info, 
                                INSTR (c.class_info, ':', 1, 2) + 1, 
                                1 
                        ), 
                        'S', 
                        ' from the start of the prior run', 
                        'C', 
                        ' from the completion of the prior run' 
                ) 
          WHEN c.class_type = 'S' 
          THEN 
             NVL2 (dates.dates, 'Dates: ' || dates.dates || '. ', NULL) 
             || DECODE (SUBSTR (c.class_info, 32, 1), 
                        '1', 
                        'Last day of month ' 
                ) 
             || DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))), 
                        '1', 
                           'Days of week: ' 
                        || DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ') 
                        || DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ') 
                        || DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ') 
                        || DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ') 
                        || DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ') 
                        || DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ') 
                        || DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa ') 
                ) 
       END 
          schedule, 
       r.requested_start_date next_run, 
       CASE 
          WHEN p.user_concurrent_program_name != 'Report Set' 
               AND p.user_concurrent_program_name != 'Check Periodic Alert' 
          THEN 
             r.argument_text 
       END 
          argument_text, 
       r.hold_flag on_hold, 
       c.date1 start_date, 
       c.date2 end_date, 
       c.class_info, 
       user_name 
FROM apps.fnd_concurrent_requests r, 
     applsys.fnd_conc_release_classes c, 
     apps.fnd_concurrent_programs_tl p, 
     apps.fnd_user usr, 
     (SELECT release_class_id, 
             SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates, 
             a 
      FROM (SELECT release_class_id, 
                   RANK () OVER (PARTITION BY release_class_id ORDER BY s) a, 
                   s 
            FROM (SELECT c.class_info, 
                         l, 
                         c.release_class_id, 
                         DECODE (SUBSTR (c.class_info, l, 1), '1', TO_CHAR (l)) 
                            s 
                  FROM (SELECT LEVEL l 
                        FROM DUAL 
                        CONNECT BY LEVEL <= 31), 
                       apps.fnd_conc_release_classes c 
                  WHERE c.class_type = 'S') 
            WHERE s IS NOT NULL) 
      CONNECT BY PRIOR (a || release_class_id) = (a - 1) || release_class_id 
      GROUP BY release_class_id, a) dates 
WHERE     r.phase_code = 'P' 
      AND c.application_id = r.release_class_app_id 
      AND c.release_class_id = r.release_class_id 
      AND NVL (c.date2, SYSDATE + 1) > SYSDATE 
      AND c.class_type IS NOT NULL 
      AND p.concurrent_program_id = r.concurrent_program_id 
      AND p.application_id = r.program_application_id 
      AND p.language = 'US' 
      AND dates.release_class_id(+) = r.release_class_id 
      AND usr.user_id = requested_by 
ORDER BY requested_by, on_hold, next_run;