SELECT
    No_Polis,
    Nama_Perusahaan,
    --No_Proposal,
	No_Peserta,
    Nama AS Nama_Peserta,
    --Status_Peserta,
    Tgl_Lahir,
    Kelamin,
    --Umur,
    Tgl_Mulai,
    Tgl_Selesai,
    --Pln,
    Kelas,
    SUM(AJB) AS AJB,
    SUM(ACC) AS ACC
FROM ( 
    SELECT
      pa.No_Polis,
      pa.No_Proposal,
      prs.Nama_Perusahaan,
      pst.No_Peserta,
      pst.Nama,
      dbo.Sebagai.Status_Peserta, 
      pst.Tgl_Lahir,
      pst.Kelamin,
      dbo.func_GetUmur(pst.Tgl_Lahir, pst.Tgl_Mulai) AS Umur, 
      pst.Tgl_Mulai,
      pst.Tgl_Selesai,
      pst.Pln,
      dbo.func_get_kelas_peserta(pst.No_Proposal, pst.No_Peserta) as Kelas,
      --prd.Sub_Kelompok,
      --prd.Benefit,
      CASE prd.Sub_Kelompok
        WHEN '0100' THEN prd.Benefit
        ELSE 0
      END AS AJB,
      CASE prd.Benefit
        WHEN '0200' THEN prd.Benefit
        ELSE 0
      END AS ACC
    FROM
      dbo.Peserta AS pst INNER JOIN
      dbo.Polis_Asuransi AS pa ON pst.No_Proposal = pa.No_Proposal INNER JOIN
      dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
      dbo.Peserta_Produk AS prd ON pst.No_Peserta=prd.No_Peserta AND pst.No_Proposal=prd.No_Proposal LEFT JOIN
      dbo.Sebagai ON pst.Sebagai = dbo.Sebagai.IDI 
    WHERE
      prs.GT_OG = 'GT'		-- GT
      AND pa.Active_Ya = 1	-- Proposal terakhir/terbaru
      AND pa.Status = 'I'	-- Polis Inforce
      AND pst.Status = 'I'	-- Peserta aktif
      AND prd.Sub_Kelompok IN ('0100','0200') -- AJB, ACC
      AND prd.Benefit >= 500000000
      AND pst.Sebagai = 0 -- karyawan
    ) AS x
GROUP BY
    No_Polis,
    No_Proposal,
    Nama_Perusahaan,
    No_Peserta,
    Nama,
    Status_Peserta,
    Tgl_Lahir,
    Kelamin,
    Umur,
    Tgl_Mulai,
    Tgl_Selesai,
    Pln,
    Kelas
ORDER BY
    x.Nama_Perusahaan,
    x.No_Peserta

