分类目录归档：系统层级

系统层级

EBS并发程序相关SQL

留下评论

–所有客户化并发程序
SELECT FC.USER_CONCURRENT_PROGRAM_NAME,
       FC.DESCRIPTION,
       FC.CONCURRENT_PROGRAM_NAME,
       UPPER(FE.EXECUTION_FILE_NAME)
  FROM FND_CONCURRENT_PROGRAMS_VL FC,
       FND_EXECUTABLES_FORM_V     FE,
       FND_APPLICATION            FA
 WHERE 1 = 1
   AND FA.APPLICATION_ID = FC.APPLICATION_ID
   AND FA.APPLICATION_SHORT_NAME = 'CUX'
   AND FC.CONCURRENT_PROGRAM_NAME = FE.EXECUTABLE_NAME
   AND FC.ENABLED_FLAG = 'Y'
 ORDER BY FC.CREATION_DATE DESC;

–并发程序对应参数
SELECT FV.COLUMN_SEQ_NUM                序号,
       FV.END_USER_COLUMN_NAME          参数,
       FV.DESCRIPTIVE_FLEXFIELD_NAME    可执行程序,
       FV.ENABLED_FLAG                  是否启用,
       FV.DISPLAY_FLAG                  显示,
       FFVS.FLEX_VALUE_SET_NAME         值集名称,
       FV.DEFAULT_TYPE                  默认类型,
       FV.DEFAULT_VALUE                 默认值,
       FV.REQUIRED_FLAG                 必需,
       FV.SECURITY_ENABLED_FLAG         启用安全性,
       FV.RANGE_CODE                    范围,
       FV.DISPLAY_SIZE                  显示大小,
       FV.MAXIMUM_DESCRIPTION_LEN       说明大小,
       FV.CONCATENATION_DESCRIPTION_LEN 级联说明大小,
       FV.FORM_ABOVE_PROMPT             提示,
       FV.SRW_PARAM                     变量
  FROM FND_DESCR_FLEX_COL_USAGE_VL FV, FND_FLEX_VALUE_SETS FFVS
 WHERE FV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
   AND SUBSTR(FV.DESCRIPTIVE_FLEXFIELD_NAME, 7) IN
       (SELECT FC.CONCURRENT_PROGRAM_NAME
          FROM FND_CONCURRENT_PROGRAMS_VL FC,
               FND_EXECUTABLES_FORM_V     FE,
               FND_APPLICATION            FA
         WHERE 1 = 1
           AND FA.APPLICATION_ID = FC.APPLICATION_ID
           AND FA.APPLICATION_SHORT_NAME = 'CUX'
           AND FC.CONCURRENT_PROGRAM_NAME = FE.EXECUTABLE_NAME
           AND FC.ENABLED_FLAG = 'Y')
 ORDER BY FV.DESCRIPTIVE_FLEXFIELD_NAME, FV.COLUMN_SEQ_NUM;

系统层级、经验闲谈

职责与配置文件的关系信息

留下评论

select fpv.profile_option_value, fpv.level_value, fst.responsibility_name
  from fnd_profile_options fpo,
       fnd_profile_option_values fpv,
       fnd_profile_options_tl tl,
       fnd_responsibility_tl fst,
       (SELECT 10001 level_id, '地点' 文件安全性
          FROM dual
        UNION
        SELECT 10002 level_id, '应用产品' 文件安全性
          FROM dual
        UNION
        SELECT 10003 level_id, '责任' 文件安全性
          FROM dual
        UNION
        SELECT 10004 level_id, '用户' 文件安全性
          FROM dual
        UNION
        SELECT 10005 level_id, '服务器' 文件安全性
          FROM dual
        UNION
        SELECT 10006 level_id, '组织' 文件安全性 FROM dual) lv
 where 1 = 1
   and fpv.profile_option_id = fpo.profile_option_id
   and tl.language = 'ZHS'
   and fst.language = 'ZHS'
   and tl.profile_option_name = fpo.profile_option_name
      –and tl.user_profile_option_name like '%MO%业务实体%'
      –and fpo.profile_option_name = 'DEFAULT_ORG_ID'
   and fpo.profile_option_name = 'CUX_INV_ACCESS_SINV_CQC'
   and fpv.level_value = fst.responsibility_id
   and lv.level_id = fpv.level_id
–and fpv.level_value = fnd_global.resp_id
–and fpv.level_id = 10003 –职责层

 

–初始化环境


BEGIN


  fnd_global.apps_initialize(user_id      => 0,


                             resp_id      => 50658,


                             resp_appl_id => 20003);


  mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 82);


  mo_global.init('SQLAP');


END;





–以下SQL查询当前配置的MO：安全性配置文件


SELECT *


  FROM per_security_profiles psp


 WHERE psp.security_profile_id =


 

       to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'));

和fnd_profile.value('ORG_ID') 来配置




–以下SQL用来获取当前安全性配置文件和当前用户可访问的OU


SELECT hou.organization_id, hou.name


  FROM hr_operating_units hou


 WHERE hou.usable_flag IS NULL


   AND EXISTS


 (SELECT 1


          FROM per_organization_list per


         WHERE per.organization_id = hou.organization_id


           AND per.security_profile_id =


               to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))


        UNION


        SELECT 1


          FROM dual


         WHERE to_number(fnd_profile.value('ORG_ID')) = hou.organization_id)

 

 

表名：per_organization_list per, hr_operating_units hr

条件：WHERE per.security_profile_id(+) = to_number(fnd_profile.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL'))
AND hr.organization_id = per.organization_id(+)
AND hr.usable_flag is null
AND (EXISTS (SELECT 1
           FROM per_organization_list per1
          WHERE hr.organization_id = per1.organization_id
            AND per1.security_profile_id =
                to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))) OR
        fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL') = 0)

系统层级、经验闲谈

EBS常用的配置参数获取

留下评论

 Select fnd_profile.value('GL_SET_OF_BKS_ID') from dual

select fnd_profile.value('SO_ORGANIZATION_ID') from dual;

select fnd_profile.value('ORG_ID') from dual;

fnd_global.resp_id;

select TO_NUMBER(FND_PROFILE.VALUE('USER_ID')) from dual;
select TO_NUMBER(FND_PROFILE.VALUE('login_ID')) from dual;
org_id = :$PROFILES$.ORG_ID;也可用此来取得,在定义报表参数时来定义;

fnd_profile.value('MFG_ORGANIZATION_ID');

select FND_PROFILE.VALUE('CONCURRENT_REQUEST_ID') from dual;

fnd_global.resp_id;

fnd_global.resp_id;

— 取得当前的库存组织
SELECT t.organization_code
  FROM inv.mtl_parameters t
 WHERE t.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID');
— 取得当前的业务实体
SELECT hou.name
  FROM hr_operating_units hou
 WHERE hou.organization_id = fnd_profile.value('ORG_ID');
 — 取得当前的帐套
SELECT gl.name
  FROM gl_ledgers gl
 WHERE gl.ledger_id = fnd_profile.value('GL_SET_OF_BKS_ID');

系统层级

EBS Form导出Excel格式数据

留下评论

      有时候客户需要将类似日记账信息直接从主界面通过系统自动的导出功能导出数据到本地,系统本身提供的导出格式是"csv",当然这个格式通过其他阅读器也是可以正常打开,只是不能像通过Excel打开一样对数据进行直接加工。因此希望通过更改系统本身的配置文件和首选项相关项的设置直接生成Excel数据输出。具体方法如下两步(注意记录修改前数据):

1. 将配置文件名 "导出 MIME 类型"的地点层数据由"text/tab-separated-values"更改为"application/vnd.ms-excel";
2. 修改首选项中"客户机字符编码"为"简体中文(GB2312)",原本是Unicode(UTF-8)

说明：暂不明修改上述两项后对其他功能的影响,待实际应用过程中检验.

系统层级

查出低效的SQL

留下评论

–查找低效sql

SELECT EXECUTIONS,
       DISK_READS,
       BUFFER_GETS,
       ROUND((BUFFER_GETS – DISK_READS) / BUFFER_GETS, 2) Hit_radio,
       ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
       SQL_TEXT
  FROM V$SQLAREA
 WHERE EXECUTIONS > 0
   AND BUFFER_GETS > 0
   AND (BUFFER_GETS – DISK_READS) / BUFFER_GETS < 0.8
 ORDER BY 4 DESC;

–查找bad sql的方法：

select *
  from (select buffer_gets, sql_text
          from v$sqlarea
         where buffer_gets > 500000
         order by buffer_gets desc)
 where rownum <= 30;

—执行次数多的SQL：

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)

 where rownum < 81;

–读硬盘多的SQL：

select sql_text, disk_reads
  from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc)

 where rownum < 21;

–排序多的SQL：

select sql_text, sorts
  from (select sql_text, sorts from v$sqlarea order by sorts desc)

 where rownum < 21;

–分析的次数太多，执行的次数太少，要用绑变量的方法来写sql：

set pagesize 600;

set linesize 120;

select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"

  from v$sqlarea

 where executions < 5

 group by substr(sql_text, 1, 80)

having count(*) > 30

 order by 2;

–游标的观察：

set pages 300;

select sum(a.value), b.name

  from v$sesstat a, v$statname b

 where a.statistic# = b.statistiC#
     
   and b.name = 'opened cursors current'

 group by b.name;

select count(0) from v$open_cursor;

select user_name, sql_text, count(0)
  from v$open_cursor

 group by user_name, sql_text
having count(0) > 30;

–查看当前用户&username执行的SQL：

select sql_text
  from v$sqltext_with_newlines
 where (hash_value, address) in
       (select sql_hash_value, sql_address
          from v$session
         where username = '&username')
 order by address, piece;