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;
2017-12-20
[Oracle]Query Function in User Name
訂閱:
張貼留言 (Atom)
沒有留言:
張貼留言