2017-12-20

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

沒有留言:

張貼留言