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