[Oracle]檢查年度開帳資料

SELECT FDS.DB_SEQUENCE_NAME,
       FDSA.SET_OF_BOOKS_ID,
       SOB.NAME,
       FDS.DOC_SEQUENCE_ID,
       FAT.APPLICATION_NAME,
       FDS.NAME,
       FDS.START_DATE,
       FDS.END_DATE,
       FDS.INITIAL_VALUE,
       FDSA.DOC_SEQUENCE_ASSIGNMENT_ID,
       FDSC.NAME,
       FDSA.START_DATE,
       FDSA.END_DATE
  FROM FND_DOCUMENT_SEQUENCES       FDS,
       FND_DOC_SEQUENCE_ASSIGNMENTS FDSA,
       FND_DOC_SEQUENCE_CATEGORIES  FDSC,
       FND_APPLICATION_TL           FAT,
       GL_SETS_OF_BOOKS             SOB
 WHERE 1 = 1
   AND FDS.DOC_SEQUENCE_ID = FDSA.DOC_SEQUENCE_ID(+)
   AND FDSA.CATEGORY_CODE = FDSC.CODE(+)
   AND FDS.APPLICATION_ID = FAT.APPLICATION_ID(+)
   AND FAT.SOURCE_LANG = 'ZHT'
   AND FDSA.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID(+)
   AND TO_CHAR(FDS.START_DATE, 'YYYY') = '2023'
   --AND FDS.NAME = 'ACETJ_OM_1031012_23'
 ORDER BY FDSA.SET_OF_BOOKS_ID, FAT.APPLICATION_NAME

[Oracle]How to Delete Concurrent Program

請求建立後是無法刪除的，只能停用，可以透過以下API來刪除

 

DECLARE
  P_PROG_SHORT_NAME VARCHAR2(240) := 'CSTR002';
  P_EXEC_SHORT_NAME VARCHAR2(240) := 'CSTR002';
  P_APPL_FULL_NAME  VARCHAR2(240) := '成本管理系統';
  P_APPL_SHORT_NAME VARCHAR2(240) := 'CST';
  P_DEL_PROG_FLAG   VARCHAR2(1) := 'Y';
  P_DEL_EXEC_FLAG   VARCHAR2(1) := 'Y';
BEGIN
  --檢查是否存在
  IF FND_PROGRAM.PROGRAM_EXISTS(P_PROG_SHORT_NAME, P_APPL_SHORT_NAME) AND
     FND_PROGRAM.EXECUTABLE_EXISTS(P_EXEC_SHORT_NAME, P_APPL_SHORT_NAME) THEN
  
    IF P_DEL_PROG_FLAG = 'Y' THEN
      FND_PROGRAM.DELETE_PROGRAM(P_PROG_SHORT_NAME, P_APPL_FULL_NAME);
    END IF;
    IF P_DEL_EXEC_FLAG = 'Y' THEN
      FND_PROGRAM.DELETE_EXECUTABLE(P_EXEC_SHORT_NAME, P_APPL_FULL_NAME);
    END IF;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('刪除成功');
  ELSE
    DBMS_OUTPUT.PUT_LINE('刪除失敗');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
END;

[Oracle]How to Disable/Enable Oracle User Account

Disable Oracle User Account

  
  DECLARE
      P_USER_NAME      VARCHAR2(100) :='A0001'; --帳號
      P_FND_END_DATE DATE := TO_DATE('2022/12/29','YYYY/MM/DD'); --帳號啟動時間
  BEGIN
    --關閉帳號
    FND_USER_PKG.UPDATEUSER(X_USER_NAME => P_USER_NAME,
                            X_OWNER     => NULL,
                            X_END_DATE  => P_FND_END_DATE);
  END;
 

Enable Oracle User Account
 
  DECLARE
      P_USER_NAME      VARCHAR2(100) :='A0001'; --帳號
      P_FND_START_DATE DATE := TO_DATE('20221229','YYYYMMDD'); --帳號啟動時間
  BEGIN
    --使用FND_USER_PKG.UPDATEUSER失效日期壓空值沒有作用，改用FND_USER_PKG.ENABLEUSER
    FND_USER_PKG.ENABLEUSER(USERNAME   => P_USER_NAME,
                            START_DATE => P_FND_START_DATE);
  END;

[Oracle]Setup Document Sequences by Dataload

--定義
SELECT FDS.NAME,
       'TAB',
       FAT.APPLICATION_NAME,
       'TAB',
       FDS.START_DATE,
       'TAB',
       FDS.END_DATE,
       'TAB',
       '自動',
       'TAB',
       'TAB',
       FDS.INITIAL_VALUE,
       'TAB',
       'ENT'
  FROM FND_DOCUMENT_SEQUENCES FDS, FND_APPLICATION_TL FAT
 WHERE 1 = 1
   AND FDS.APPLICATION_ID = FAT.APPLICATION_ID
   AND FAT.SOURCE_LANG = 'ZHT'
   AND FDS.NAME LIKE 'XXX%'
   --AND FAT.APPLICATION_NAME = '應付帳款管理系統'
   AND TO_CHAR(FDS.START_DATE, 'YYYY') = '2022'
ORDER BY FDS.NAME;

--指定
SELECT FAT.APPLICATION_NAME,
       'TAB',
       FDSC.NAME,
       'TAB',
       SOB.NAME,
       'TAB',
       'TAB',
       FDSA.START_DATE,
       'TAB',
       FDSA.END_DATE,
       'TAB',
       FDS.NAME,
       'ENT'
  FROM FND_DOCUMENT_SEQUENCES       FDS,
       FND_DOC_SEQUENCE_ASSIGNMENTS FDSA,
       FND_DOC_SEQUENCE_CATEGORIES  FDSC,
       FND_APPLICATION_TL           FAT,
       GL_SETS_OF_BOOKS             SOB
 WHERE 1 = 1
   AND FDS.DOC_SEQUENCE_ID = FDSA.DOC_SEQUENCE_ID
   AND FDSA.APPLICATION_ID = FAT.APPLICATION_ID
   AND FDSA.CATEGORY_CODE = FDSC.CODE
   AND FDSA.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
   AND FAT.SOURCE_LANG = 'ZHT'
   AND SOB.NAME = 'XX'
   --AND FAT.APPLICATION_NAME = '總帳會計管理系統'
   AND TO_CHAR(FDS.START_DATE, 'YYYY') = '2022'
 ORDER BY SOB.NAME,FAT.APPLICATION_NAME, FDSC.NAME, FDSA.START_DATE;

[Oracle]How to Query Organization

  
SELECT GL.LEDGER_ID,
       GL.CHART_OF_ACCOUNTS_ID      COA_ID,
       GL.NAME,
       GL.SHORT_NAME,
       GL.PERIOD_SET_NAME,
       GL.CURRENCY_CODE,
       HOU.DEFAULT_LEGAL_CONTEXT_ID LEGAL_ID,
       HOU.ORGANIZATION_ID          OU_ID,
       HOU.NAME                     OU_NAME,
       OOD.ORGANIZATION_ID          ORG_ID,
       OOD.ORGANIZATION_CODE        ORG_CODE,
       OOD.ORGANIZATION_NAME        ORG_NAME
  FROM HR_OPERATING_UNITS           HOU,
       ORG_ORGANIZATION_DEFINITIONS OOD,
       GL_LEDGERS                   GL
 WHERE HOU.ORGANIZATION_ID(+) = OOD.OPERATING_UNIT
   AND OOD.SET_OF_BOOKS_ID(+) = GL.LEDGER_ID
   AND HOU.DATE_TO IS NULL
 ORDER BY GL.CHART_OF_ACCOUNTS_ID, OOD.ORGANIZATION_ID

[Oracle]How to Query Value Set

SELECT * 
   FROM FND_FLEX_VALUE_SETS FFVS, 
        FND_FLEX_VALUES FFV 
 WHERE 1 = 1 
   AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID 
   AND FFVS.FLEX_VALUE_SET_ID = 1009721

[Oracle]How to Query Flexfield

SELECT *
  FROM (SELECT 'GLOBAL' TYPE,
               FDFV.APPLICATION_ID,
               FPV.APPLICATION_SHORT_NAME,
               FDFV.APPLICATION_TABLE_NAME,
               FDFV.CONTEXT_COLUMN_NAME,
               FDFV.TITLE,
               FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE,
               FDFU.APPLICATION_COLUMN_NAME,
               FDFU.END_USER_COLUMN_NAME,
               FDFU.ENABLED_FLAG
          FROM FND_DESCRIPTIVE_FLEXS_VL    FDFV,
               FND_DESCR_FLEX_CONTEXTS_VL  FDFC,
               FND_DESCR_FLEX_COL_USAGE_VL FDFU,
               FND_APPLICATION_VL          FPV
         WHERE 1 = 1
           AND FDFV.APPLICATION_ID = FDFC.APPLICATION_ID
           AND FDFV.DESCRIPTIVE_FLEXFIELD_NAME =
               FDFC.DESCRIPTIVE_FLEXFIELD_NAME
           AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME =
               FDFU.DESCRIPTIVE_FLEXFIELD_NAME
              --AND FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE = FDFU.DESCRIPTIVE_FLEX_CONTEXT_CODE
           AND UPPER(FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE) =
               'GLOBAL DATA ELEMENTS'
           AND FDFV.APPLICATION_ID = FPV.APPLICATION_ID
        UNION ALL
        SELECT 'OTHERS' TYPE,
               FDFV.APPLICATION_ID,
               FPV.APPLICATION_SHORT_NAME,
               FDFV.APPLICATION_TABLE_NAME,
               FDFV.CONTEXT_COLUMN_NAME,
               FDFV.TITLE,
               FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE,
               FDFU.APPLICATION_COLUMN_NAME,
               FDFU.END_USER_COLUMN_NAME,
               FDFU.ENABLED_FLAG
          FROM FND_DESCRIPTIVE_FLEXS_VL    FDFV,
               FND_DESCR_FLEX_CONTEXTS_VL  FDFC,
               FND_DESCR_FLEX_COL_USAGE_VL FDFU,
               FND_APPLICATION_VL          FPV
         WHERE 1 = 1
           AND FDFV.APPLICATION_ID = FDFC.APPLICATION_ID
           AND FDFV.DESCRIPTIVE_FLEXFIELD_NAME =
               FDFC.DESCRIPTIVE_FLEXFIELD_NAME
           AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME =
               FDFU.DESCRIPTIVE_FLEXFIELD_NAME
           AND FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE =
               FDFU.DESCRIPTIVE_FLEX_CONTEXT_CODE
           AND FDFV.APPLICATION_ID = FPV.APPLICATION_ID)
 WHERE 1 = 1
   AND APPLICATION_TABLE_NAME = 'WIP_DISCRETE_JOBS'

[Oracle]Query Function in User Name

DECLARE
  P_COUNT NUMBER;
  CURSOR CUR_DATA IS
    SELECT FU.USER_NAME,
           FU.DESCRIPTION,
           FU.START_DATE          帳號啟用時間,
           FU.END_DATE            帳號關閉時間,
           FR.RESPONSIBILITY_NAME,
           FURG.START_DATE        權限啟用時間,
           FURG.END_DATE          權限關閉時間
      FROM FND_USER_RESP_GROUPS_DIRECT FURG,
           FND_USER                    FU,
           FND_RESPONSIBILITY_TL       FR
     WHERE 1 = 1
       AND FU.END_DATE IS NULL
       AND FURG.END_DATE IS NULL
          --AND substr(user_name, 1, 1) in ('C', 'K', 'S', 'L')
       AND user_name not in ('CS_CPA',
                             'CCS001',
                             'SYSADMIN',
                             'WIZARD',
                             'ASGADM',
                             'AUTOINSTALL',
                             'EFLOW_ADMIN',
                             'GUEST',
                             'IBEGUEST',
                             'IBE_ADMIN',
                             'MOBADM',
                             'MOBDEV',
                             'MOBILEADM')
       AND SUBSTR(USER_NAME, 1, 1) NOT IN ('B')
       AND FU.END_DATE IS NULL
       AND FR.LANGUAGE = 'US'
       AND FURG.USER_ID = FU.USER_ID
       AND FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
       AND SUBSTR(FU.USER_NAME, 1, 1) IN ('T')
     ORDER BY USER_NAME;
BEGIN
  FOR C1 IN CUR_DATA LOOP
    SELECT COUNT(*)
      INTO P_COUNT
      FROM FND_RESPONSIBILITY_VL FRV, FND_FORM_FUNCTIONS FFF
     WHERE 1 = 1
       AND FFF.FUNCTION_NAME LIKE 'WIP_WIPMRMDF%' --FORM FUNCTION NAME
       AND FRV.RESPONSIBILITY_NAME LIKE '%DGP%'
       AND FRV.RESPONSIBILITY_NAME = C1.RESPONSIBILITY_NAME
       AND FRV.MENU_ID IN
           (SELECT ME.MENU_ID
              FROM FND_MENU_ENTRIES ME
             START WITH ME.FUNCTION_ID = FFF.FUNCTION_ID
            CONNECT BY PRIOR ME.MENU_ID = ME.SUB_MENU_ID);
  
    IF P_COUNT >= 1 THEN
      DBMS_OUTPUT.PUT_LINE(C1.USER_NAME || '---' || C1.DESCRIPTION ||
                           '---' || C1.RESPONSIBILITY_NAME);
    ELSE
      NULL;
    END IF;
  END LOOP;
END;

 

[Oracle]How To Disable Users Using API

 

 
CREATE OR REPLACE PROCEDURE CBM_FND_PRC_001(P_USER_NAME     IN VARCHAR2,
											P_USER_END_DATE IN VARCHAR2,
                                            P_MESSAGE       OUT VARCHAR2) IS

  P_COUNT          NUMBER;
  P_USER_END_DATE1 VARCHAR2(20);
BEGIN
  P_USER_END_DATE1 := TO_DATE(P_USER_END_DATE, 'YYYY/MM/DD');

  SELECT COUNT(*)
    INTO P_COUNT
    FROM FND_USER FU
   WHERE 1 = 1
     AND FU.USER_NAME = P_USER_NAME
     AND FU.END_DATE IS NULL;

  IF P_COUNT = 1 THEN
    FND_USER_PKG.UPDATEUSER(X_USER_NAME => P_USER_NAME,
                            X_OWNER     => NULL,
                            X_END_DATE  => P_USER_END_DATE1);
  ELSE
    P_MESSAGE := '權限已經關閉';
  END IF;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    P_MESSAGE := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
                 DBMS_UTILITY.FORMAT_ERROR_STACK;
END;

 

[Oracle]Routine FDPREP cannot read the file

 

今天同仁上新的Report，出現以下錯誤，紀錄一下找不到rep，但是明明上Report不需要Compiler，結果問了一下Google大神，原來是副檔名問題
只要把副檔名改為小寫就可以了
Arguments
------------
P_ORDER_NUM='8108674'
------------
Routine FDPREP cannot read the file /u01/dev/devappl/ont/11.5.0/reports/US/CBM_OM_RPT_004.rep.

Check if the file exists under correct product directory.

Check if the person who started the Concurrent Manager has read privileges on the dir
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 19397257.

Review your concurrent request log and/or report output file for more detailed information.

[Oracle]Query to list all Oracle report assigned to a responsibility

 參考網址：http://dbnerdz.blogspot.tw/2013/09/query-to-list-all-oracle-reports.html

如何透過報表名稱，回查報表名稱位置，但是這個SQL應該可以改善，再找時間研究看看

SELECT DISTINCT FCPL.USER_CONCURRENT_PROGRAM_NAME "REPORT NAME",
       FNRTL.RESPONSIBILITY_NAME--,
       --FRG.REQUEST_GROUP_NAME,
       --FCP.CONCURRENT_PROGRAM_NAME       "CONCURRENT PROGRAM SHORT NAME"
  FROM APPS.FND_REQUEST_GROUPS         FRG,
       APPS.FND_REQUEST_GROUP_UNITS    FRGU,
       APPS.FND_CONCURRENT_PROGRAMS    FCP,
       APPS.FND_CONCURRENT_PROGRAMS_TL FCPL,
       APPS.FND_EXECUTABLES            FE,
       APPS.FND_RESPONSIBILITY         FNR,
       APPS.FND_RESPONSIBILITY_TL      FNRTL
 WHERE FRG.APPLICATION_ID = FRGU.APPLICATION_ID
   AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
   AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
   AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
   AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
   AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
   AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
   AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
   AND FE.EXECUTION_METHOD_CODE LIKE 'P' --'ORACLE REPORTS'
   AND FCP.EXECUTABLE_ID = FE.EXECUTABLE_ID
   AND FE.APPLICATION_ID = FCP.EXECUTABLE_APPLICATION_ID
   --AND FNRTL.RESPONSIBILITY_NAME LIKE '&RESPONSIBILITY_NAME'
      -- Example Resp. Name : Oracle Inventory or put '%' to get the list of all the responsibilities
   AND FNRTL.LANGUAGE = 'US'
   AND FCPL.LANGUAGE = 'US'
   AND FCP.CONCURRENT_PROGRAM_NAME LIKE 'CB%'

 

[Oracle]Query to list all Oracle function assigned to a responsibility

 參考資料：https://community.oracle.com/thread/2208873?tstart=0

                https://dotblogs.com.tw/jeff-yeh/2009/05/20/8489

可以輸入Function Name來查詢這個程式是位於那些權限

1. 需要找時間研究一下 CONNECT BY
2. 應該可以找到掛在那一階

SELECT DISTINCT FRT.RESPONSIBILITY_ID, FRT.RESPONSIBILITY_NAME
  FROM APPLSYS.FND_RESPONSIBILITY    FR,
       APPLSYS.FND_RESPONSIBILITY_TL FRT,
       APPLSYS.WF_LOCAL_USER_ROLES   WLUR
 WHERE FR.APPLICATION_ID = FRT.APPLICATION_ID
   AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
   AND FR.RESPONSIBILITY_ID = WLUR.ROLE_ORIG_SYSTEM_ID
   AND WLUR.ROLE_ORIG_SYSTEM = 'FND_RESP'
   AND SYSDATE BETWEEN FR.START_DATE AND NVL(FR.END_DATE, SYSDATE + 1)
   AND SYSDATE BETWEEN WLUR.START_DATE AND
       NVL(WLUR.EXPIRATION_DATE, SYSDATE + 1)
   AND FR.MENU_ID IN
       (SELECT MENU_ID
          FROM APPLSYS.FND_MENU_ENTRIES FME
        CONNECT BY PRIOR FME.MENU_ID = FME.SUB_MENU_ID
         START WITH FME.FUNCTION_ID =
                    (SELECT FUNCTION_ID
                       FROM APPLSYS.FND_FORM_FUNCTIONS FFF
                      WHERE FFF.FUNCTION_NAME = 'CBM_OM_FRM_040'));

20170517
http://192.168.50.10/wordpress/blog/2017/05/17/oraclestart-with-connect-by-prior%e7%94%a8%e6%b3%95/

[Oracle]Responsibility not display

 參考網址：

http://somebabytina.pixnet.net/blog/post/30684439
https://bbs.aliyun.com/simple/t499645.html

權限名稱：GL_會計負責人
狀況描述：User因為某些關係關閉 GL_會計負責人權限，但是今天因為測試需要重新開啟，於Define_User重新開啟權限後，卻發現沒有在清單中

執行Request：Workflow Directory Services User/Role Validation，參數：10000, Y, Y, Y,
沒有作用後選擇以下方式

SELECT USER_NAME,
       ROLE_NAME,
       USER_END_DATE,
       ROLE_END_DATE,
       ASSIGNING_ROLE_END_DATE,
       EFFECTIVE_END_DATE
  FROM WF_USER_ROLE_ASSIGNMENTS
 WHERE USER_NAME IN ('A0AA','A0BB')
   AND ROLE_NAME LIKE '%GL%CBM%';

SELECT USER_NAME,ROLE_NAME, USER_END_DATE, ROLE_END_DATE, EFFECTIVE_END_DATE,ROWID
  FROM WF_LOCAL_USER_ROLES
 WHERE USER_NAME IN ('A0AA','A0BB')
   AND ROLE_NAME LIKE '%GL%CBM%';

比對以上兩個資料表發現EFFECTIVE_END_DATE不一致，改為9999/1/1，User反應正常顯示

 

20170222補充，因為還是偶爾會遇到，進行程式改良

DECLARE
  P_USER_NAME          VARCHAR2(50) := 'R0028';
  P_USER_ID            NUMBER;
  P_RESPONSIBILITY_KEY VARCHAR2(50);
  P_EFFECTIVE_END_DATE DATE;

  CURSOR CUR_DATA IS
    SELECT FUR.RESPONSIBILITY_ID
      FROM FND_USER_RESP_GROUPS_DIRECT FUR
     WHERE FUR.USER_ID = (SELECT FU.USER_ID
                            FROM FND_USER FU
                           WHERE FU.USER_NAME = P_USER_NAME)
       AND TRUNC(NVL(FUR.END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE);
BEGIN

  FOR C1 IN CUR_DATA LOOP
    SELECT FRV.RESPONSIBILITY_KEY
      INTO P_RESPONSIBILITY_KEY
      FROM FND_RESPONSIBILITY_VL FRV
     WHERE FRV.RESPONSIBILITY_ID = C1.RESPONSIBILITY_ID;
  
    DBMS_OUTPUT.PUT_LINE(P_RESPONSIBILITY_KEY);
  
    UPDATE WF_LOCAL_USER_ROLES WL
       SET WL.EFFECTIVE_END_DATE = TO_DATE('9999/1/1', 'YYYY/MM/DD')
     WHERE WL.USER_NAME = P_USER_NAME
       AND WL.ROLE_NAME LIKE '%' || P_RESPONSIBILITY_KEY || '%';
  END LOOP;
  COMMIT;
END;

20170424補充，程式應該是有作用，只是似乎有背後排程
20171109補充，程式有作用，再去Define_User，同仁帳號表身看不到的權限上，隨便更動一下這筆資料，就會馬上生效
20171201補充

• Sync responsibility role data into the WF table
• Synchronize Workflow Local Tables（可能是R12）
• Synchronize WF LOCAL tables（11i 版本有找到這個）
• Workflow Directory Services User/Role Validation   ( 在跳出的參數選取畫面的第二欄Fix dangling user/roles 選擇yes)