--SELECT
--	*
--FROM
--	dbo.custport_user_login_client
--WHERE
--  id_user > 12
-----------------------------

DECLARE @list_user TABLE ( nama VARCHAR(20))

INSERT INTO @list_user ( nama ) VALUES ('revina')
INSERT INTO @list_user ( nama ) VALUES ('sugeng')
INSERT INTO @list_user ( nama ) VALUES ('anggiat')
INSERT INTO @list_user ( nama ) VALUES ('deswitha')
INSERT INTO @list_user ( nama ) VALUES ('reny')

DECLARE @list_polis TABLE ( no_polis VARCHAR(12), domain VARCHAR(30))

INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2014.0193.1', '@trilink.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2016.0014.1', '@provices.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2015.0178.1', '@hillebrand.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2014.0184.1', '@protelindo.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2013.0174.1', '@ingenico.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2012.0222.1', '@airfast.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2013.0080.1', '@airasia.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2012.0135.1', '@otsuka.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2013.0077.1', '@multimedia.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('2013.0185.1', '@waruna.com')
INSERT INTO @list_polis ( no_polis, domain ) VALUES ('1991.0150.0', '@gt.com')

DECLARE @tabel_no_peserta TABLE ( no_polis VARCHAR(12), no_peserta VARCHAR(12))
DECLARE @tabel_kelas_peserta TABLE ( no_polis VARCHAR(12), kelas VARCHAR(12))

DECLARE 
  @no_polis VARCHAR(12),
  @domain VARCHAR(30)

DECLARE list_cursor CURSOR FOR  
SELECT no_polis, domain
FROM @list_polis 

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis, @domain

WHILE @@FETCH_STATUS = 0  
BEGIN 

  DECLARE @nama VARCHAR(20)
  DECLARE list_cursor_dua CURSOR FOR  
  SELECT nama
  FROM @list_user 
  
  OPEN list_cursor_dua  
  FETCH NEXT FROM list_cursor_dua INTO @nama
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
  
    DECLARE 
      @no_peserta VARCHAR(12),
      @kelas_peserta VARCHAR(10)
    
    SELECT TOP 1
  	  @no_peserta = pst.No_Peserta,
  	  @kelas_peserta = pst.Kelas
    FROM
  	  dbo.Polis_Asuransi AS pa INNER JOIN
  	  dbo.Peserta AS pst ON pa.No_Proposal = pst.No_Proposal
    WHERE
      pa.Status = 'I'
      AND pa.Active_Ya = 1
      AND pst.Status = 'I'
      AND pst.Sebagai = 0
      AND pa.No_Polis = @no_polis
      AND pst.No_Peserta NOT IN (SELECT no_peserta FROM @tabel_no_peserta WHERE no_polis = @no_polis)
      AND pst.Kelas NOT IN (SELECT kelas FROM @tabel_kelas_peserta WHERE no_polis = @no_polis)

    INSERT INTO @tabel_no_peserta ( no_polis, no_peserta ) VALUES (@no_polis, @no_peserta)
    INSERT INTO @tabel_kelas_peserta ( no_polis, kelas ) VALUES (@no_polis, @kelas_peserta)
    
    INSERT INTO dbo.custport_user_login_client
            (username,
             password,
             no_polis,
             no_peserta,
             status,
             create_by,
             create_date,
             change_by,
             change_date)
    VALUES
            (@nama+@domain, -- username - varchar(80)
             '202cb962ac59075b964b07152d234b70', -- password - varchar(32)
             @no_polis, -- no_polis - varchar(11)
             @no_peserta, -- no_peserta - varchar(12)
             'I', -- status - varchar(1)
             'SUP', -- create_by - varchar(4)
             GETDATE(), -- create_date - datetime
             'SUP', -- change_by - varchar(4)
             GETDATE()  -- change_date - datetime
             )
    FETCH NEXT FROM list_cursor_dua INTO @nama
  END  

  CLOSE list_cursor_dua  
  DEALLOCATE list_cursor_dua 
  
  FETCH NEXT FROM list_cursor INTO @no_polis, @domain 
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT * FROM @tabel_no_peserta
SELECT * FROM @tabel_kelas_peserta