Queries uteis para o Oracle Applications

[dr_gori]

Este tópico serve pra colocarmos queries UTEIS relacionadas a ORACLE APPLICATIONS - ERP R11, R12, etc
Se você tiver mais queries úteis sobre Oracle Apps, poste nesse tópico

SE O SELECT DER ERRO QUE A TABELA NÃO EXISTE, TENTE COLOCAR O OWNER APPS ANTES

LISTAR CONCURRENTS E SEUS PARÂMETROS

Selecionar tudo

SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , par.end_user_column_name
     , par.form_left_prompt prompt
     , par.description     
     , par.enabled_flag
     , par.required_flag
     , par.display_flag
     , par.default_value
     , (select flex_value_set_name from  fnd_flex_value_sets where Flex_value_set_id=par.flex_value_set_id) VALUE_SET_NAME
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl par
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name LIKE '%&nome_conc%'
AND    fcpl.LANGUAGE = 'US'
AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

LISTAR DETALHES DE UM REQUEST SET

Selecionar tudo

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]

LISTA RESPONSABILIDADES INFORMANDO O USUÁRIO

Selecionar tudo

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
              SUBSTR (r.responsibility_name, 1, 60) responsiblity,
              SUBSTR (a.application_name, 1, 50) application,
              r.responsibility_id,
              fpov.profile_option_value SOB
         FROM APPS.fnd_user u,
              APPS.fnd_user_resp_groups g,
              APPS.fnd_application_tl a,
              APPS.fnd_responsibility_tl r,
              APPS.fnd_profile_options fpo, 
              APPS.fnd_profile_option_values fpov
        WHERE g.user_id(+) = u.user_id
          AND g.responsibility_application_id = a.application_id
          AND a.application_id = r.application_id
          AND g.responsibility_id = r.responsibility_id
          AND u.user_id = (select user_id from APPS.fnd_user where user_name='&USERNAME')
          and fpov.profile_option_id = fpo.profile_option_id
          and fpov.level_value = r.responsibility_id
          AND fpo.profile_option_name = 'GL_SET_OF_BKS_NAME'
     ORDER BY SUBSTR (user_name, 1, 30),
              SUBSTR (a.application_name, 1, 50),
              SUBSTR (r.responsibility_name, 1, 60);

Exemplo:

Selecionar tudo

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> 

Colocado owner APPS em todas tabelas

[dr_gori]

DESCRIÇÃO DOS VALUE SETS / LOOKUPS

Selecionar tudo

SELECT a.flex_value_set_name, b.flex_value, b.description
  FROM fnd_flex_value_sets a
     , fnd_flex_values_vl  b
 WHERE a.flex_value_set_id   = b.flex_value_set_id   
   AND a.flex_value_set_name LIKE '%&nome_value_set%' 
 ORDER BY 1,2,3

Exemplo:

Selecionar tudo

SQL> /

FLEX_VALUE_SET_NAME  FLEX_VALUE DESCRIPTION
-------------------- ---------- -----------------------------
BIS_YES_NO           N          No
BIS_YES_NO           Y          Yes

Para os lookups, é bem mais fácil:

Selecionar tudo

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

Caso você conheça alguns valores do value-set, é fácil de achar ele. (no caso abaixo, eu sei que o campo pode ter o valor BOOKED, CLOSED, CANCELLED e ENTERED. (na realidade, tem vários outros, mas esses são os que eu conheço)

Selecionar tudo

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]

PROGRAMA x RESPONSABILIDADE x SOB

Selecionar tudo

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

O MESMO SQL MAS PARA REQUEST_SET

Selecionar tudo

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]

MOSTRA REQUEST RODANDO, ON HOLD E PENDENTES

Selecionar tudo

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]

MOSTRA INFORMAÇÕES DE VERSÃO E PATCH APLICADOS

Selecionar tudo

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

Selecionar tudo

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

[dr_gori]

DESCRICAO FLEXFIELD X TABELA

Selecionar tudo

-- 
-- 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]

RESPONS / REQUEST_GROUP / MENU / FORMS
Query para listar o cruzamento entre Responsabilidades, Menus, Funções e Forms do Aplication

Selecionar tudo

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]

LISTAR VALOR DE PROFILE

Este select retorna o valor dos profiles possíveis nos vários níveis: Site, Responsabilidade, usuário, etc. Fonte

Selecionar tudo

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";

Outro exemplo: (Fonte: Metalink ID 201945.1)

Selecionar tudo

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;

[alexrsilva]

Rafael,
Vê se é isso que você quer.

Selecionar tudo

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]

Abaixo alguns comendos uteis na programação em APPS:

Selecionar tudo

-> 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)

Vários outros aqui: http://docs.oracle.com/cd/E18727_01/doc ... 457084.htm


Abs.

[dr_gori]

LOCAIS dos arquivos TRACE:

Selecionar tudo

-- 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;

--Ajuda a obter local do trace executado por um concorrente.

Selecionar tudo

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

Selecionar tudo

SELECT 
  ou.organization_id     ou_id  
, ou.name                ou_org 
, inv.organization_id    inv_id
, inv.organization_code  inv_code
, uu.name                inv_name
--
, lo.description  
, lo.town_or_city
, lo.region_2
, lo.country
FROM
  apps.org_organization_definitions inv,
  apps.hr_all_organization_units    ou,
  apps.hr_organization_units        uu,
  apps.hr_locations                 lo
WHERE inv.operating_unit  = ou.organization_id
  AND inv.organization_id = uu.organization_id
  AND uu.location_id      = lo.location_id(+)
ORDER BY 1, 3

Outra forma legal:

Selecionar tudo

select  organization_id, organization_code
from mtl_parameters_view

PARA SETAR A ORGANIZAÇÃO NO R12:

Selecionar tudo

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

Aí vai um exemplo:

Selecionar tudo

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]

LISTAR CUSTOMIZAÇÕES FEITAS NO EBS

Na minha empresa, todas customizações possuem o prefixo XG. (Algumas tem o prefixo XX, etc)

Selecionar tudo

-- 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(+)

É claro, ainda existem muitas outras coisas, como
- FLEX FIELDS (feito diretamente na implantação)
- CONJ DE VALORES (pode não ter a extensão XG, talvez tem como ver pelo nome de quem criou)
- LOOKUPS
- PROFILE
- MENUS
- FONTES --> exemplo: codigo de barras
- ARQUIVOS DE CONFIGURAÇÃO --> no linux
- PERMISSÕES
- DBLINKS
- etc

[dr_gori]

INICIALIZAR SESSÃO NO R11 e R12

Antes de executar consultas no SQL*Plus, Toad, PLSQL Developer, etc, temos que inicializar a sessão com

Abaixo, temos um select util para o R11: Ele "monta" o comando, e basta executar.

Selecionar tudo

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';

Saída:

Selecionar tudo

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

E no R12, normalmente fazemos assim:

Selecionar tudo

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

[dr_gori]

COPIAR RESPONSABILIDADE DE UM USUÁRIO PARA OUTRO

Basta editar o USER_FROM e USER_TO abaixo:

Selecionar tudo

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;

Dica fornecida por MUHAMMAD RAFI AAMIRI MADANI.

[Porva]

Como determinar/consultar USER_ID, RESP_ID, RESP_APPL_ID quando usar a FND_GLOBAL.APPS_INITIALIZE

achei bem interessante essa dica, obter RESP_ID e RESP_APPL_ID pelo nome da responsabilidade:

How to determine USER_ID,RESP_ID when use FND_GLOBAL.APPS_INITIALIZE

Selecionar tudo

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

fonte: https://forums.oracle.com/thread/1108336

[Porva]

Listar concurrents em execução:

Selecionar tudo

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]

Descrição de Códigos / Concorrente

STATUS CODE:

Selecionar tudo

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

Selecionar tudo

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

Outros:

Selecionar tudo

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

[dr_gori]

Query para encontrar os Key-Flex-Fields

Selecionar tudo

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]

Listar concurrents programados.

fonte: 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) */

Selecionar tudo

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;