DECLARE 
    @no_polis VARCHAR(11),
    @email VARCHAR(80),
    @no_peserta VARCHAR(12)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  

SELECT 
	dapes.No_Polis,
	hris.[NoParticipants],
	LOWER(RTRIM(LTRIM(tmp.[email])))
FROM 
	[GrpInsurance].[dbo].[tmp_email_karyawan_eli] AS tmp LEFT JOIN
	[GrpInsurance].[dbo].[tmp_data_hris] AS hris ON tmp.nip = hris.IdNo LEFT JOIN
	(
		SELECT
			pa.No_Polis,
			prs.Nama_Perusahaan,
			pst.No_Peserta,
			pst.Nama,
			pa.Status AS PA_Status,
			pst.Status AS PST_Status
		FROM
			[ELI-DB10\grpinsurance].GrpInsurance.dbo.Polis_Asuransi AS pa INNER JOIN
			[ELI-DB10\grpinsurance].GrpInsurance.dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
			[ELI-DB10\grpinsurance].GrpInsurance.dbo.Peserta AS pst ON pa.No_Proposal = pst.No_Proposal
		WHERE
			pa.Active_Ya = 1
			AND prs.Nama_Perusahaan LIKE '%equity%'
	) AS dapes ON hris.[NoParticipants] = dapes.No_Peserta
WHERE
	COALESCE(tmp.[email],'') <> ''
	AND COALESCE(hris.[NoParticipants],'') <> ''
	AND hris.[Status] = 'V'
	AND dapes.PST_Status = 'I'

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis, @no_peserta, @email

WHILE @@FETCH_STATUS = 0  
BEGIN  

		IF NOT EXISTS(SELECT
							*
						FROM
							tmp_custport_user_login_client
						WHERE
							username = @email)
			BEGIN
				DECLARE @id_user INT
				INSERT INTO [dbo].[tmp_custport_user_login_client]
					    ([username]
					    ,[password]
					    ,[no_polis]
					    ,[no_peserta]
					    ,[status]
					    ,[create_by]
					    ,[create_date]
					    ,[change_by]
					    ,[change_date])
					VALUES
					    (@email
					    ,NULL
					    ,@no_polis
					    ,@no_peserta
					    ,'G'
					    ,'SYS'
					    ,GETDATE()
					    ,'SYS'
					    ,GETDATE())
				SELECT @id_user = SCOPE_IDENTITY()
				INSERT INTO [dbo].[tmp_custport_user_login_client_polis]
					   ([create_by]
					   ,[create_date]
					   ,[change_by]
					   ,[change_date]
					   ,[is_deleted]
					   ,[id_user]
					   ,[no_polis]
					   ,[no_peserta]
					   ,[status])
				  VALUES
					   ('SYS'
					   ,GETDATE()
					   ,'SYS'
					   ,GETDATE()
					   ,0
					   ,@id_user
					   ,@no_polis
					   ,@no_peserta
					   ,'I')
			END

    FETCH NEXT FROM list_cursor INTO @no_polis, @no_peserta, @email
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 