所有由小潘发布的文章

喜欢分享的小潘.
EBS_FA_SQL

资产类别信息

留下评论

SELECT FCV.SEGMENT1 || '.' || FCV.SEGMENT2 || '.' || FCV.SEGMENT3 资产类别,
       FCV.DESCRIPTION 类别描述,
       FCB.BOOK_TYPE_CODE 资产账簿,
       GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' ||
       GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' ||
       GCC.SEGMENT7 || '.' || 0 成本科目,
       GCC1.SEGMENT1 || '.' || GCC1.SEGMENT2 || '.' || GCC1.SEGMENT3 || '.' ||
       GCC1.SEGMENT4 || '.' || GCC1.SEGMENT5 || '.' || GCC1.SEGMENT6 || '.' ||
       GCC1.SEGMENT7 || '.' || 0 资产结算科目,
       GCC2.SEGMENT1 || '.' || GCC2.SEGMENT2 || '.' || GCC2.SEGMENT3 || '.' ||
       GCC2.SEGMENT4 || '.' || GCC2.SEGMENT5 || '.' || GCC2.SEGMENT6 || '.' ||
       GCC2.SEGMENT7 || '.' || 0 折旧费用科目,
       GCC3.SEGMENT1 || '.' || GCC3.SEGMENT2 || '.' || GCC3.SEGMENT3 || '.' ||
       GCC3.SEGMENT4 || '.' || GCC3.SEGMENT5 || '.' || GCC3.SEGMENT6 || '.' ||
       GCC3.SEGMENT7 || '.' || 0 累计折旧科目,
       GCC4.SEGMENT1 || '.' || GCC4.SEGMENT2 || '.' || GCC4.SEGMENT3 || '.' ||
       GCC4.SEGMENT4 || '.' || GCC4.SEGMENT5 || '.' || GCC4.SEGMENT6 || '.' ||
       GCC4.SEGMENT7 || '.' || 0 附加费用科目,
       GCC5.SEGMENT1 || '.' || GCC5.SEGMENT2 || '.' || GCC5.SEGMENT3 || '.' ||
       GCC5.SEGMENT4 || '.' || GCC5.SEGMENT5 || '.' || GCC5.SEGMENT6 || '.' ||
       GCC5.SEGMENT7 || '.' || 0 附加准备金科目

  FROM FA_CATEGORIES_VL     FCV,
       FA_CATEGORY_BOOKS    FCB,
       GL_CODE_COMBINATIONS GCC,
       GL_CODE_COMBINATIONS GCC1,
       GL_CODE_COMBINATIONS GCC2,
       GL_CODE_COMBINATIONS GCC3,
       GL_CODE_COMBINATIONS GCC4,
       GL_CODE_COMBINATIONS GCC5

 WHERE 1 = 1
   AND FCV.CATEGORY_ID = FCB.CATEGORY_ID
   AND FCV.ENABLED_FLAG = 'Y'
   AND FCB.ASSET_COST_ACCOUNT_CCID = GCC.CODE_COMBINATION_ID
   AND FCB.ASSET_CLEARING_ACCOUNT_CCID = GCC1.CODE_COMBINATION_ID
   AND FCB.DEPRN_EXPENSE_ACCOUNT_CCID = GCC2.CODE_COMBINATION_ID
   AND FCB.BONUS_RESERVE_ACCT_CCID = GCC3.CODE_COMBINATION_ID
   AND FCB.BONUS_EXPENSE_ACCOUNT_CCID = GCC4.CODE_COMBINATION_ID
   AND FCB.BONUS_RESERVE_ACCT_CCID = GCC5.CODE_COMBINATION_ID;

EBS PLSQL开发

并发程序连续调用带有模板的请求

留下评论

    昨天在做一个客户化功能的时候，我想实现的是一个程序包中有3个procedure，这3个procedure均被做为单独的并发请求，其中主请求是单纯的功能，两个子请求是针对此功能做的两个报表程序。我希望在主请求完成以后，连续调用两个子请求完成带有rtf模板的报表输出。如下图所示，在加载模板后，再调用请求执行，却发现只能正确执行前面并发请求的模板加载并成功输出，后面的并发请求能执行，但是不能加载模板文件，只能输出XML文件。

      连续调用加载模板

   于是尝试了下面的做法，如图所示，正确的做法是，在A做先调用B子程序，然后在子程序B中去调用C程序，这样便可将两个子程序B、C的模板正常加载完成（目前测试结果，求大家更深层次的指导）。

      思路

 

系统层级

查出低效的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;

系统层级

财务系统个性化信息查询

留下评论

SELECT FCR.FUNCTION_NAME,
       FCR.DESCRIPTION,
       FCR.TRIGGER_EVENT,
       FCR.TRIGGER_OBJECT,
       FCR.CONDITION,
       FCR.ENABLED,
       FCR.FIRE_IN_ENTER_QUERY,
       FCR.RULE_TYPE,
       FCR.FORM_NAME,
       FCS.LEVEL_ID,
       FCS.LEVEL_VALUE
  FROM APPLSYS.FND_FORM_CUSTOM_RULES   FCR,
       APPLSYS.FND_FORM_CUSTOM_SCOPES  FCS,
       APPLSYS.FND_FORM_CUSTOM_ACTIONS FCA
 WHERE FCR.ID = FCS.RULE_ID(+)
   AND FCR.ID = FCA.RULE_ID(+);