DECLARE 
  @no_proposal VARCHAR(9),
  @no_peserta VARCHAR(12),
  --@keterangan VARCHAR(30),
  @id_produk VARCHAR(10)

DECLARE list_cursor CURSOR FAST_FORWARD FOR
SELECT 
	pa.No_Proposal,
	pst.No_Peserta,
	pp.Id_Produk
FROM 
	Polis_Asuransi AS pa INNER JOIN
	Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	Peserta AS pst ON pa.No_Proposal = pst.No_Proposal INNER JOIN
	Peserta_Produk AS pp ON pst.No_Proposal = pp.No_Proposal
							AND pst.No_Peserta = pp.No_Peserta
WHERE
	prs.Group_Perusahaan = 23017
	AND pa.Tgl_Selesai >= GETDATE()
	AND pa.Tgl_Mulai <= GETDATE()
	AND pa.Mata_Uang = '01'
	AND pst.Status = 'I'
	AND pp.Status = 'I'
	AND pp.Sub_Kelompok = '0301'
	AND pp.Kelas IN ('RJ150', 'RJ225', 'RJ275', 'RJ375', 'RJ525')
ORDER BY
	prs.Nama_Perusahaan

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal, @no_peserta, @id_produk

WHILE @@FETCH_STATUS = 0  
BEGIN

		DECLARE	@Klaim float
		EXEC	[dbo].[proc_GetKlaimPeserta]
				@NoProposal = @no_proposal,
				@NoPeserta = @no_peserta,
				@KodeProduk = @id_produk,
				@Klaim = @Klaim OUTPUT


		UPDATE
			dbo.Peserta_Produk
		SET
			Status = 'O',
			Tgl_Ubah = '2019-04-01',	-- tgl lapse
			Jml_Klaim = @Klaim,	-- Proc_GetKlaimPeserta
			--Jumlah = Curr_Premi - (Curr_Premi * 0.075) - @Klaim,
			Jumlah = (ABS(Curr_Premi - (Curr_Premi * 0.075) - @Klaim) + Curr_Premi - (Curr_Premi * 0.075) - @Klaim)/2,	-- juka negatif, maka 0
			Adm_Keluar = Curr_Premi * 0.075,
			Tgl_Trans = '2019-04-01',	-- tgl lapse
			Jenis = 11,
			Change_By = 'SYS',
			Change_Date = CAST(CAST(GETDATE() AS DATE) AS DATETIME)
		WHERE
			No_Proposal = @no_proposal
			AND No_Peserta =  @no_peserta
			AND Id_Produk = @id_produk

  
  PRINT @no_proposal + ' ' + @no_peserta

  FETCH NEXT FROM list_cursor INTO @no_proposal, @no_peserta, @id_produk
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

