33 lines
1.2 KiB
SQL
33 lines
1.2 KiB
SQL
DECLARE
|
|
lv_user_id NUMBER;
|
|
lv_service_type SMP_VDV_PREFERRED_CREDENTIALS.SERVICE_TYPE%TYPE;
|
|
lv_service_name SMP_VDV_PREFERRED_CREDENTIALS.SERVICE_NAME%TYPE;
|
|
lv_credentials SMP_VDV_PREFERRED_CREDENTIALS.CREDENTIALS%TYPE;
|
|
BEGIN
|
|
|
|
SELECT u.USER_ID, SERVICE_TYPE, SERVICE_NAME, CREDENTIALS
|
|
INTO lv_user_id, lv_service_type, lv_service_name, lv_credentials
|
|
FROM SMP_VDV_PREFERRED_CREDENTIALS a, SMP_VDV_USER u
|
|
WHERE a.USER_ID = u.USER_ID
|
|
AND u.USER_NAME = 'VGUPTA'
|
|
AND service_name = 'amazon.eiffel.servista.com';
|
|
|
|
FOR I IN (
|
|
SELECT u.USER_ID, tt.NAME SERVICE_type, t.NAME
|
|
FROM SMP_VDV_USER u, SMP_VDN_TARGET_LIST t, SMP_VDN_TARGET_TYPE_DEFN tt, SMP_VDN_GROUP_LIST g, SMP_VDN_GROUP_TARGET gt
|
|
WHERE u.USER_NAME = 'VGUPTA'
|
|
AND g.ID = gt.GROUPID
|
|
AND gt.TARGETID = t.ID
|
|
AND t.TYPEID = tt.ID
|
|
AND tt.NAME = 'oracle_sysman_node'
|
|
AND g.NAME = 'Eiffel'
|
|
AND NOT EXISTS (SELECT 1 FROM SMP_VDV_PREFERRED_CREDENTIALS b
|
|
WHERE b.USER_ID = u.USER_ID
|
|
AND b.SERVICE_NAME = t.NAME)
|
|
)
|
|
LOOP
|
|
INSERT INTO SMP_VDV_PREFERRED_CREDENTIALS VALUES (i.user_id, i.SERVICE_type, i.NAME, lv_CREDENTIALS);
|
|
END LOOP;
|
|
END;
|
|
/
|