SELECT 
	pa.No_Proposal,
	pa.No_Polis,
	prs.Nama_Perusahaan,
	pst.No_Peserta,
	pst.Nama AS Nama_Peserta,
	pst.Tgl_Lahir,
	pst.Tgl_Mulai,
	dbo.func_GetUmur(pst.Tgl_Lahir, pst.Tgl_Mulai) AS Usia,
	sbg.Status_Peserta AS Sebagai,
	pst.Kelamin AS Sex,
	ajb.Benefit AS AJB_UP,
	acc.Benefit AS ACC_UP
FROM
	dbo.Polis_Asuransi AS pa 
	INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs 
	INNER JOIN
	dbo.Peserta AS pst ON pa.No_Proposal = pst.No_Proposal 
	INNER JOIN
	dbo.Sebagai AS sbg ON pst.Sebagai = sbg.IDI
	LEFT JOIN
	( SELECT
	  	pp1.No_Proposal,
	  	pp1.No_Peserta,
	  	pp1.Premi,
	  	pp1.Curr_Premi,
	  	pp1.Kelas,
	  	pp1.Benefit,
	  	pp1.Santunan
	  FROM
	  	dbo.Peserta_Produk AS pp1
	  WHERE
	    pp1.Sub_Kelompok = '0100' -- AJB
	) AS ajb ON pst.No_Proposal = ajb.No_Proposal
	            AND pst.No_Peserta = ajb.No_Peserta 
	LEFT JOIN
	( SELECT
	  	pp2.No_Proposal,
	  	pp2.No_Peserta,
	  	pp2.Premi,
	  	pp2.Curr_Premi,
	  	pp2.Kelas,
	  	pp2.Benefit,
	  	pp2.Santunan
	  FROM
	  	dbo.Peserta_Produk AS pp2
	  WHERE
	    pp2.Sub_Kelompok = '0200' -- ACC
	) AS acc ON pst.No_Proposal = acc.No_Proposal
	            AND pst.No_Peserta = acc.No_Peserta

WHERE
  pa.Status = 'I' -- polis aktif
  AND pst.Status = 'I'  -- peserta aktif
  AND pst.Sebagai = 0 -- hanya karyawan saja
  --AND prs.Group_Perusahaan = 4  --Gajah Tunggal Group
  AND pa.No_Polis IN ('1992.0030.0',  -- sesuai list yang diberikan
                      '2001.0004.0',
                      '1991.0150.0',
                      '2006.0012.0',
                      '2016.0006.0',
                      '1992.0033.0',
                      '2006.0061.0',
                      '2015.0031.0',
                      '1998.0016.0',
                      '1997.0008.0',
                      '1997.0030.0',
                      '2010.0023.0',
                      '1991.0168.0',
                      '2009.0120.0',
                      '2003.0037.0',
                      '2016.0041.0',
                      '1999.0016.0',
                      '2016.0062.0',
                      '1991.0140.0',
                      '2014.0191.0',
                      '2002.0096.0',
                      '2009.0122.0',
                      '2011.0117.0',
                      '2007.0151.0',
                      '2006.0065.0',
                      '1999.0011.0',
                      '2016.0143.0',
                      '1999.0012.0',
                      '2014.0166.0',
                      '1999.0013.0',
                      '2014.0165.0',
                      '2004.0040.0',
                      '2016.0077.0',
                      '2003.0014.0',
                      '2012.0012.0',
                      '2006.0201.0',
                      '1999.0014.0',
                      '1999.0015.0',
                      '2015.0024.0',
                      '1996.0019.0',
                      '2015.0185.0',
                      '2007.0152.0',
                      '1992.0037.0',
                      '2016.0144.0',
                      '2015.0193.0',
                      '2007.0027.0',
                      '1994.0004.0',
                      '2010.0098.0',
                      '2011.0010.0',
                      '2015.0201.0')
ORDER BY
  prs.Nama_Perusahaan,
  pst.No_Peserta
