USE [GrpInsurance]
GO

UPDATE
	sp
SET
	no_ref = ppd.No_Ref,
	masa_auransi = dbo.func_date_format('dd-mm-yyyy', pst.Tgl_Mulai) +' s.d '+dbo.func_date_format('dd-mm-yyyy', pst.Tgl_Selesai),
	tgl_lahir = dbo.func_date_format('dd-mm-yyyy', pst.Tgl_Lahir),
	uang_pertanggungan = 'Rp '+dbo.func_curr_format_ina(pp.curr_santunan, 15, 2, 1),
	tgl_terbit = dbo.func_date_format('dd-mm-yyyy', GETDATE()),
	kode_cabang = pst.cabang,
	nama_cabang = cb.nama
FROM 
	sertifikat_peserta AS sp LEFT JOIN
	Peserta_Produk_Detail AS ppd ON sp.no_tertanggung = ppd.no_peserta INNER JOIN
	Note_Terbit_Master AS ntm ON ppd.No_Ref = ntm.No_Ref INNER JOIN
	(
		SELECT
			x.No_Peserta,
			x.Create_Date
		FROM
			(
				SELECT
					ppd2.No_Peserta,
					MAX(ntm2.Create_Date) AS Create_Date
				FROM
					Peserta_Produk_Detail AS ppd2 INNER JOIN
					Note_Terbit_Master AS ntm2 ON ppd2.No_Ref = ntm2.No_Ref
				WHERE
					ntm2.No_Proposal = '2018.0133'
					AND ntm2.Status = 'I'
					AND ntm2.Jenis = 1
					AND ppd2.Status <> 'O'
					AND LEFT(ppd2.Sub_Kelompok,3)='010'
				GROUP BY
					ppd2.No_Peserta
			) AS x
		GROUP BY
			x.No_Peserta,
			x.Create_Date
	) AS x ON ppd.No_Peserta = x.No_Peserta
			  AND ntm.Create_Date = x.Create_Date INNER JOIN
	Peserta AS pst ON ppd.No_proposal = pst.No_proposal
					  AND ppd.No_Peserta = pst.No_Peserta INNER JOIN
	Peserta_Produk AS pp ON ppd.No_Proposal = pp.No_Proposal
							AND ppd.No_Peserta = pp.No_Peserta
							AND ppd.Id_Produk = pp.Id_Produk INNER JOIN
	Polis_Asuransi AS pa ON ppd.No_proposal = pa.No_proposal INNER JOIN 
	Cabang AS cb ON cb.no_prs = pa.no_prs 
					and cb.kode=pst.cabang
WHERE
	LEFT(ppd.Sub_Kelompok,3)='010'
	AND ntm.Jenis = 1

