USE [GrpInsurance]

----- load/init data -------------------------------------------------

DECLARE @list_map TABLE (no_proposal VARCHAR(9),
                         no_polis VARCHAR(11)
                        )
INSERT INTO @list_map
        (no_proposal, no_polis)
SELECT --TOP 1
	pa.No_Proposal,
	pa.No_Polis
FROM
  dbo.Polis_Asuransi AS pa INNER JOIN
  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
WHERE
  prs.Group_Perusahaan = 23017  -- MAP
  AND Active_Ya = 1
  --AND pa.Status = 'I'  -- polis yg dicari, semua status
  --AND pa.No_Polis = '2016.0005.0' -- testing saja
  
-----------------------------------------------------------------------

DECLARE @temp_tabel TABLE (no_polis VARCHAR(11),
                           no_proposal VARCHAR(9),
                           no_peserta VARCHAR(12),
                           rek_no VARCHAR(30),
                           rek_nama VARCHAR(50),
                           kode_bank VARCHAR(3),
                           bank_nama VARCHAR(60),
                           bank_cabang VARCHAR(60)
                           )

DECLARE 
  @no_proposal VARCHAR(9),
  @no_polis VARCHAR(11)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT --TOP 1
	pa.No_Proposal,
	pa.No_Polis
FROM
  dbo.Polis_Asuransi AS pa INNER JOIN
  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
WHERE
  prs.Group_Perusahaan = 23017  -- MAP
  AND Active_Ya = 1
  AND pa.Status = 'I' -- yg dicari (yg tdk ada rek) hanya polis aktif
--WHERE
--  no_polis = '2016.0005.0' -- testing saja

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

WHILE @@FETCH_STATUS = 0  
BEGIN 

  DECLARE 
    @detail_no_peserta VARCHAR(12),
    --@no_polis VARCHAR(11),
    @detail_no_peserta_sumber VARCHAR(12),
    @detail_no_polis_sumber VARCHAR(11)
    
  DECLARE list_cursor_dua CURSOR FAST_FORWARD FOR   
  SELECT 
	  pst_tanpa_rek.No_Peserta,
	  --pst_tanpa_rek.Nama,
	  --pst_tanpa_rek.Tgl_Lahir,
	  --pst_tanpa_rek.Nik,
	  --pst_tanpa_rek.No_Proposal,
	  --pst_tanpa_rek.No_Polis,
	  
    pst_polis_lain_dgn_rek.No_Peserta,
    --pst_polis_lain_dgn_rek.Nama,
    --pst_polis_lain_dgn_rek.Tgl_Lahir,
    --pst_polis_lain_dgn_rek.Nik,
    --pst_polis_lain_dgn_rek.Rek_No,
    --pst_polis_lain_dgn_rek.Rek_Nama,
    --pst_polis_lain_dgn_rek.No_Proposal,
    pst_polis_lain_dgn_rek.no_polis
  FROM
    -- peserta status aktif pada polis current (polis pada posisi cursor), yang tidak mempunyai no rek
  	(
      SELECT
	      pst.No_Peserta,
      	pst.Nama,
      	pst.Tgl_Lahir,
      	pst.Nik,
      	pst.No_Proposal,
      	@no_polis AS No_Polis
      FROM
	      dbo.Peserta AS pst LEFT JOIN
	      dbo.Peserta_Rekening AS pr ON pst.No_Peserta = pr.No_Peserta
	                                    AND @no_polis = pr.No_Polis
      WHERE
        pst.No_Proposal = @no_proposal
        AND pr.No_Peserta IS NULL
        AND pst.Status = 'I'  -- hanya peserta aktif saja
        AND pst.Sebagai = 0 -- hanya karyawan saja
  	) AS pst_tanpa_rek INNER JOIN
  	-- semua peserta polis MAP lainnya (baik aktif maupun tidak), kecuali polis current (polis pada posisi cursor), yang mempunyai no rek
  	(
      SELECT
      	p2.No_Peserta,
      	p2.Nama,
      	p2.Tgl_Lahir,
      	p2.Nik,
      	pr2.Rek_No,
      	pr2.Rek_Nama,
      	p2.No_Proposal,
      	map.no_polis
      FROM
      	dbo.Peserta_Rekening AS pr2 INNER JOIN
      	@list_map AS map ON pr2.No_Polis = map.no_polis INNER JOIN
      	dbo.Peserta AS p2 ON map.no_proposal = p2.No_Proposal
      	                     AND pr2.No_Peserta = p2.No_Peserta
      WHERE
        pr2.No_Polis <> @no_polis
  	) AS pst_polis_lain_dgn_rek ON pst_tanpa_rek.Nama = pst_polis_lain_dgn_rek.Nama
  	                               AND pst_tanpa_rek.Tgl_Lahir = pst_polis_lain_dgn_rek.Tgl_Lahir
  ORDER BY
    pst_tanpa_rek.No_Peserta
  
  OPEN list_cursor_dua  
  FETCH NEXT FROM list_cursor_dua INTO @detail_no_peserta, @detail_no_peserta_sumber, @detail_no_polis_sumber

  WHILE @@FETCH_STATUS = 0  
  BEGIN 
    IF NOT EXISTS(SELECT * FROM	dbo.Peserta_Rekening WHERE No_Polis=@no_polis AND No_Peserta=@detail_no_peserta)
      BEGIN
        INSERT INTO dbo.Peserta_Rekening
                (No_Polis,
                 No_Peserta,
                 Kode_Bank,
                 Rek_No,
                 Rek_Nama,
                 Bank_Nama,
                 Bank_Cabang,
                 Bank_Alamat,
                 Bank_Asal,
                 Bank_Lokasi,
                 Sandi_BI,
                 Status,
                 Create_By,
                 Create_Date,
                 Change_By,
                 Change_Date)
        SELECT
        	  @no_polis,      -- No_Polis - varchar(11)
        	  @detail_no_peserta,    -- No_Peserta - varchar(12)
        	  Kode_Bank,     -- Kode_Bank - varchar(3)
        	  Rek_No,        -- Rek_No - varchar(30)
        	  Rek_Nama,      -- Rek_Nama - varchar(50)
        	  Bank_Nama,     -- Bank_Nama - varchar(40)
        	  Bank_Cabang,   -- Bank_Cabang - varchar(60)
        	  Bank_Alamat,   -- Bank_Alamat - varchar(60)
        	  Bank_Asal,     -- Bank_Asal - varchar(5)
        	  Bank_Lokasi,   -- Bank_Lokasi - varchar(1)
        	  Sandi_BI,      -- Sandi_BI - varchar(7)
        	  Status,        -- Status - varchar(1)
        	  Create_By,  --'DMK',     -- Create_By - varchar(4)
        	  Create_Date,  -- CAST(CAST(GETDATE() AS DATE) AS DATETIME),   -- Create_Date - datetime
        	  Change_By, --'DMK',     -- Change_By - varchar(4)
        	  Change_Date --CAST(CAST(GETDATE() AS DATE) AS DATETIME)    -- Change_Date - datetime
        FROM
        	dbo.Peserta_Rekening
        WHERE
          No_Polis = @detail_no_polis_sumber
          AND No_Peserta = @detail_no_peserta_sumber
        
        INSERT INTO @temp_tabel
                (no_polis,
                 no_proposal,
                 no_peserta,
                 rek_no,
                 rek_nama,
                 kode_bank,
                 bank_nama,
                 bank_cabang)
        SELECT
        	  @no_polis,      -- no_polis - varchar(11)
        	  @no_proposal, -- no_proposal - varchar(9)
        	  @detail_no_peserta, -- no_peserta - varchar(12)
        	  Rek_No,        -- rek_no - varchar(30)
        	  Rek_Nama,      -- rek_nama - varchar(50)
        	  Kode_Bank,     -- kode_bank VARCHAR(3),
        	  Bank_Nama,     -- bank_nama VARCHAR(60),
        	  Bank_Cabang    -- bank_cabang VARCHAR(60)
        FROM
        	dbo.Peserta_Rekening
        WHERE
          No_Polis = @detail_no_polis_sumber
          AND No_Peserta = @detail_no_peserta_sumber
          
      END
  
    FETCH NEXT FROM list_cursor_dua INTO @detail_no_peserta, @detail_no_peserta_sumber, @detail_no_polis_sumber
  END  

  CLOSE list_cursor_dua  
  DEALLOCATE list_cursor_dua 

  

  FETCH NEXT FROM list_cursor INTO @no_proposal, @no_polis
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT
	tmp.*,
	dbo.Bank_Master.Nama_Bank
FROM
	@temp_tabel AS tmp LEFT OUTER JOIN dbo.Bank_Master ON tmp.kode_bank = dbo.Bank_Master.Kode_Bank