分类目录归档:EBS日常运维问题

Oracle计划请求信息

SELECT fcp.concurrent_program_name 并发代码,
decode(fcre.description,
NULL,
fcpt.user_concurrent_program_name,
fcre.description || ‘ (‘ || fcpt.user_concurrent_program_name || ‘)’) 请求名称,
fu.user_name 用户名,
conc.request_id 请求ID,
conc.root_request_id 父请求ID,
decode(fcre.status_code,
‘D’,
‘Cancelled’,
‘U’,
‘Disabled’,
‘E’,
‘Error’,
‘M’,
‘No Manager’,
‘R’,
‘Normal’,
‘H’,
‘On Hold’,
‘W’,
‘Paused’,
‘B’,
‘Resuming’,
‘I’,
‘Scheduled’,
‘Q’,
‘Standby’,
‘S’,
‘Suspended’,
‘X’,
‘Terminated’,
‘T’,
‘Terminating’,
‘A’,
‘Waiting’,
‘G’,
‘Warning’) 请求状态,

fcre.argument_text 请求参数,
sche.job_class 计划类型,
sche.class_info 频率

FROM apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcre,
apps.fnd_user fu,
(SELECT owner_req_id,
date1 start_date,
date2 end_date,
decode(class_type, ‘S’, ‘Specific Days’, ‘P’, ‘Periodically’) job_class,
decode(class_type,
‘P’,
substr(class_info, 1, instr(class_info, ‘:’) – 1) ||
decode(substr(class_info, instr(class_info, ‘:’) + 1, 1),
‘D’,
‘Days’,
‘H’,
‘Hours’,
‘N’,
‘Minutes’,
‘M’,
‘Months’,
‘W’,
‘Weeks’),
‘S’,
class_info) class_info
FROM apps.fnd_conc_release_classes fcr

WHERE class_type IN (‘S’, ‘P’) –Periodically and Specific Concurrent
AND enabled_flag = ‘Y’
AND (date2 IS NULL OR date2 > SYSDATE)
AND owner_req_id IS NOT NULL) sche,

(SELECT MAX(request_id) request_id, root_request_id
FROM apps.fnd_concurrent_requests
WHERE root_request_id IN
(SELECT nvl(root_request_id, request_id) request_id
FROM apps.fnd_concurrent_requests
WHERE request_id IN
(SELECT owner_req_id
FROM apps.fnd_conc_release_classes fcr
WHERE class_type IN (‘S’, ‘P’) –Periodically and Specific Concurrent
AND enabled_flag = ‘Y’
AND (date2 IS NULL OR date2 > SYSDATE)
AND owner_req_id IS NOT NULL)
AND status_code <> ‘D’)
GROUP BY root_request_id) concWHERE fcp.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = ‘ZHS’
AND fcp.concurrent_program_id = fcre.concurrent_program_id
AND fcre.status_code NOT IN (‘D’, ‘X’, ‘U’) –Cancelled and Terminated and Disabled
AND fcre.request_id = conc.request_id
AND conc.request_id = sche.owner_req_id
AND fcre.requested_by = fu.user_id

并发程序与模板的关系SQL

–并发程序与模板的关系
SELECT FC.USER_CONCURRENT_PROGRAM_NAME,
       FC.DESCRIPTION,
       FC.CONCURRENT_PROGRAM_NAME,
       UPPER(FE.EXECUTION_FILE_NAME),
       XTV.DEFAULT_OUTPUT_TYPE,
       FC.OUTPUT_FILE_TYPE
  FROM FND_CONCURRENT_PROGRAMS_VL FC,
       FND_EXECUTABLES_FORM_V     FE,
       FND_APPLICATION            FA,
       XDO_TEMPLATES_VL           XTV
 WHERE 1 = 1
   AND XTV.TEMPLATE_CODE = FC.CONCURRENT_PROGRAM_NAME
   AND XTV.APPLICATION_SHORT_NAME = FA.APPLICATION_SHORT_NAME
   AND FA.APPLICATION_ID = FC.APPLICATION_ID
   AND FC.CONCURRENT_PROGRAM_NAME = FE.EXECUTABLE_NAME
   AND FC.ENABLED_FLAG = 'Y'
   –AND FA.APPLICATION_SHORT_NAME = 'CUX'
   –AND FC.USER_CONCURRENT_PROGRAM_NAME = 'CUX:账户余额表'
 

Oracle EBS如何启用说明性弹性域

        相信大家在做Oracle EBS的时候,都会遇到需要开启弹性域(说明性弹性域)的时候,这里我就将整个过程做一个描述性记录:

前提:需要开启的弹性域对应的表已经在弹性域定义界面进行注册(如果未注册,需要单独进行注册,此处不再描述注册过程)

主要分以下两种类型:

1.Form界面开启说明性弹性域

    a.打开需要设置弹性域的界面,找到该界面对应的表或试图,这里以开启物料批次弹性域为例,如下图所示:

      

    b.找到对应的基表,MTL_LOT_NUMBERS_ALL_V,一般都是以基表注册,因此找到基表MTL_LOT_NUMBERS,如下图所示:

      

    c.定位至弹性域注册界面,通过基表去模糊匹配,查出开启弹性域对应的标题(标题也可从最终需启用界面去看),找到标题“维护批号”,如下图所示:

    

    d.在说明性弹性域–段下,在标题栏输入“维护批号”,随即进入定义弹性域最终界面,如图所示:

    

    e.正常情况下,弹性域定义界面是冻结状态,如果需要编辑,需去掉冻结前的勾,点击段,进入编辑状态,如图所示:

    

    f.具体定义界面,输入编号,名称,窗口提示,弹性域对应基表的列,值集(值集与LOV值集同样意义),已显示和已启用,如下图,

   

    g.对值集进行编译,主要可选择必输和非必输项,如下图所示:

   

    h.保存,勾上冻结选项,系统会自动进行编译,编译完成即可生效,具体生效截图,如文档第一幅图所示。

2.web界面开启弹性域(待续)

可参考以下链接:

http://blog.csdn.net/rfb0204421/article/details/7641831

http://www.cnblogs.com/benio/archive/2009/12/07/1618725.html

Oracle PLSQL 将数字转为对应的英文单词

用途
    将数字转为对应的英文单词,技术写代码可引用这一段
使用场景
    发票、对账单等处输出金额

命令如下,可点击sql查看,下面的内容直接贴出来似乎不能直接编译。
/*  FUNCTION money_format_us(p_money_amount IN NUMBER) RETURN VARCHAR2 IS*/
DECLARE
  p_money_amount NUMBER := -2.04;
  TYPE lt_array_type IS VARRAY(50) OF VARCHAR2(20);
   /*一维数组,字符串类型 */
  l_array        lt_array_type := lt_array_type('ONE ',
                                                'TWO ',
                                                'THREE ',
                                                'FOUR ',
                                                'FIVE ',
                                                'SIX ',
                                                'SEVEN ',
                                                'EIGHT ',
                                                'NINE ',
                                                'TEN ',
                                                'ELEVEN ',
                                                'TWELVE ',
                                                'THIRTEEN ',
                                                'FOURTEEN ',
                                                'FIFTEEN ',
                                                'SIXTEEN ',
                                                'SEVENTEEN ',
                                                'EIGHTEEN ',
                                                'NINETEEN ',
                                                'TWENTY ',
                                                'THIRTY ',
                                                'FORTY ',
                                                'FIFTY ',
                                                'SIXTY ',
                                                'SEVENTY ',
                                                'EIGHTY ',
                                                'NINETY ',
                                                'HUNDRED ',
                                                'THOUSAND ',
                                                'MILLION ',
                                                'BILLION ');
  c_money_amount VARCHAR2(14);
  l_string       VARCHAR2(600);
  n              CHAR;
  l_pre_n        CHAR;
  l_length       NUMBER;
  i              NUMBER;
  tmp            NUMBER;
  l_decimal_flag VARCHAR2(1);
  l_money_amount NUMBER;
  l_sign         VARCHAR2(10);
BEGIN
  l_money_amount := round(abs(p_money_amount), 2);
  IF p_money_amount < 0 THEN
    l_sign := 'NEGATIVE ';
  ELSE
    l_sign := '';
  END IF;
  tmp            := l_money_amount * 100;
  c_money_amount := rtrim(ltrim(to_char(tmp, '999999999999999999')));
  l_length       := length(c_money_amount);
  i              := 0;
  WHILE i < l_length – 2 LOOP
    i := i + 1;
    IF MOD(l_length – 2 – i, 3) = 2 THEN
      n := substr(c_money_amount, i, 1);
      IF n <> '0' THEN
        l_string := l_string || l_array(to_number(n));
        l_string := l_string || l_array(to_number(28));
      END IF;
    END IF;
    IF MOD(l_length – 2 – i, 3) = 1 THEN
      n := substr(c_money_amount, i, 1);
      IF n = '1' THEN
        l_pre_n := n;
      END IF;
      IF n NOT IN ('0', '1') THEN
        l_string := l_string || l_array(to_number(n) + 18);
      END IF;
    END IF;
    IF MOD(l_length – 2 – i, 3) = 0 THEN
      IF nvl(l_pre_n, 'X') = '1' THEN
        n        := substr(c_money_amount, i, 1);
        l_string := l_string || l_array(to_number(l_pre_n || n));
        l_pre_n  := NULL;
      ELSE
        n := substr(c_money_amount, i, 1);
        IF n <> '0' THEN
          l_string := l_string || l_array(to_number(n));
        END IF;
      END IF;
    END IF;
    IF l_length – i = 5 AND substr(c_money_amount, i – 2, 3) <> '000' THEN
      l_string := l_string || l_array(to_number(29));
    END IF;
    IF l_length – i = 8 AND substr(c_money_amount, i – 2, 3) <> '000' THEN
      l_string := l_string || l_array(to_number(30));
    END IF;
    IF l_length – i = 11 THEN
      l_string := l_string || l_array(to_number(31));
    END IF;
  END LOOP;
  n := substr(c_money_amount, l_length – 1, 1);
  IF n NOT IN ('0', '1') THEN
    l_decimal_flag := 'Y';
    l_string       := l_string || 'AND ';
    l_string       := l_string || l_array(to_number(n) + 18);
  END IF;
  IF n = '1' THEN
    l_decimal_flag := 'Y';
    l_string       := l_string || 'AND ';
    l_pre_n        := n;
    n              := substr(c_money_amount, l_length, 1);
    l_string       := l_string || l_array(to_number(l_pre_n || n));
  ELSE
    n := substr(c_money_amount, l_length, 1);
    IF n <> '0' THEN
      IF nvl(l_decimal_flag, 'N') = 'N' THEN
        l_string := l_string || 'AND ';
      END IF;
      l_decimal_flag := 'Y';
      l_string       := l_string || l_array(to_number(n));
    END IF;
  END IF;
  IF l_decimal_flag = 'Y' THEN
    l_string := l_string || 'CENTS ';
  ELSE
    l_string := l_string;
  END IF;
  l_string := l_sign || l_string; /*RETURN l_string;*/
  dbms_output.put_line(l_string); /* EXCEPTION  WHEN OTHERS THEN    RETURN(SQLERRM);*/
END;

–此段引用自:http://catonisland.cn/post/138.html