--DROP TABLE TmpInsurance.dbo.temp_KlaimRasioOG

--------

SELECT 
  T1.No_Peserta,
  T1.Nama,
  T3.Sub_Kelompok,
  T3.Nama_Pendek,
  T2.Kelas,
  T1.Cabang AS Kode_Cabang,
  T4.Nama AS Nama_Cabang,
  T1.Nik,
  T1.Rate,
  T2.Curr_Santunan,
  CAST(0 AS FLOAT) AS Jml_Klaim,
  CAST(0 AS FLOAT) AS Sisa_Santunan,
  CAST(0 AS FLOAT) AS Rasio_Klaim,
  T1.No_Proposal,
  T1.No_Prs,
  T1.Status,
  T2.Id_Produk  -- temp
INTO
  TmpInsurance.dbo.temp_KlaimRasioOG
FROM
  dbo.Peserta AS T1 INNER JOIN 
  dbo.Peserta_Produk AS T2 ON T1.No_Proposal = T2.No_Proposal
                              AND T1.No_Peserta = T2.No_Peserta LEFT OUTER JOIN 
  dbo.Cabang AS T4 ON T1.No_Prs = T4.No_Prs
                      AND T1.Cabang = T4.Kode INNER JOIN 
  dbo.Sub_Kelompok AS T3 ON T3.Sub_Kelompok = T2.Sub_Kelompok
WHERE
  (T1.Status <> 'C')
  AND T1.No_Proposal = '2014.0835'
ORDER BY
  T1.No_Peserta

--------

DECLARE 
  @No_Peserta VARCHAR(12),
  @Sub_Kelompok VARCHAR(6),
  @No_Proposal VARCHAR(9),
  @Id_Produk VARCHAR(10)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT
  No_Peserta,
  Sub_Kelompok,
  No_Proposal,
  Id_Produk
FROM 
  TmpInsurance.dbo.temp_KlaimRasioOG

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @No_Peserta, @Sub_Kelompok, @No_Proposal, @Id_Produk

WHILE @@FETCH_STATUS = 0  
BEGIN  

  UPDATE
    TmpInsurance.dbo.temp_KlaimRasioOG
  SET
	  Jml_Klaim = dbo.func_GetKlaimDibayar_old(@No_Proposal, @No_Peserta, @Id_Produk)
	WHERE
	  No_Peserta = @No_Peserta
	  AND Sub_Kelompok = @Sub_Kelompok
    AND No_Proposal = @No_Proposal
    AND Id_Produk = @Id_Produk

  FETCH NEXT FROM list_cursor INTO @No_Peserta, @Sub_Kelompok, @No_Proposal, @Id_Produk  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

--------

UPDATE
  TmpInsurance.dbo.temp_KlaimRasioOG
SET
  Sisa_Santunan = Curr_Santunan - Jml_Klaim

--------

UPDATE
  TmpInsurance.dbo.temp_KlaimRasioOG
SET
  Rasio_Klaim = dbo.func_DivIsNol(100 * Jml_Klaim, Curr_Santunan, 0)
