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)
沒有留言:
張貼留言