USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep16112]    Script Date: 02/21/2018 09:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
DECLARE 
	@TglPosisi DATETIME = '2017-12-31',
	@TglPosisiYa BIT = 1, 
	@pi_jenis_polis INT = 0
EXEC proc_Rep16112 @TglPosisi, @TglPosisiYa, 2
*/

-- last modified by Pribadi Pamungkas. 2018-02-21. Ticket RQ2018020464 => tambah coalesce
-- previous modified by Tri Mulyo S. 2018-01-18. Ticket RQ2018010175
ALTER PROCEDURE [dbo].[proc_Rep16112]
      @TglPosisi DATETIME,
      @TglPosisiYa BIT, 
	  @pi_jenis_polis INT --0=All, 1=Normal, 2=Open --*****(Tri Ms)
AS
IF @pi_jenis_polis = 0 --All --*****(Tri Ms)
BEGIN
	SELECT DISTINCT TOP (100) PERCENT
	  ipap.Nama_Payor,
	  ipap.Mata_Uang,
	  ipap.GT_OG,
	  ipap.No_Proposal,
	  ipap.Nama_Perusahaan,
	  ipap.No_Polis,
	  ipap.Tgl_Mulai,
	  ipap.Tgl_Selesai,
	  ipap.Deskripsi,
	  ipap.Nama_Kelompok,
	  ipap.IP,
	  ipap.AP,
	  cp.Periode_Bayar,
	  cp.CP,
	  cp.Nama_Sub AS Sub_Kelompok,
  
	  --cp.UP,
	  --pstprod.UP, --*****(Tri Ms)
	 
	 (
	  CASE
		COALESCE --*****(Tri Ms)
		  (
			  (
				SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
				FROM dbo.Peserta_Produk 
				WHERE 
					--Status = 'I' 
					No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
					AND No_Proposal = cp.No_Proposal
					AND Sub_Kelompok = cp.Sub_Kelompok
			  ), 
			  0
			)
		WHEN 0 THEN 
			--0
			COALESCE --*****(Tri Ms)
			  (
				  (
					SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
					FROM dbo.Peserta_Produk 
					WHERE 
						--Status = 'I' 
						No_Peserta IN(SELECT No_Peserta 
										FROM peserta 
										WHERE 
											--status = 'I' 
											--AND 
											No_Proposal = cp.No_Proposal
											AND Tgl_Selesai <= @TglPosisi)
						AND No_Proposal = cp.No_Proposal
						AND Sub_Kelompok = cp.Sub_Kelompok
				  ), 
				  0
				)
		ELSE 
			COALESCE --*****(Tri Ms)
			  (
				  (
					SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
					FROM dbo.Peserta_Produk 
					WHERE 
						--Status = 'I' 
						No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
						AND No_Proposal = cp.No_Proposal
						AND Sub_Kelompok = cp.Sub_Kelompok
				  ), 
				  0
				)
	  END 
	  ) UP,

	 -- COALESCE --*****(Tri Ms)
	 -- (
		--  (
		--	SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
		--	FROM dbo.Peserta_Produk 
		--	WHERE 
		--		--Status = 'I' 
		--		No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
		--		AND No_Proposal = cp.No_Proposal
		--		AND Sub_Kelompok = cp.Sub_Kelompok
		--  ), 
		--  0
		--) UP,
  
	  cp.Santunan_Duka,
	  cp.Santunan_Cacat,
	  --ipap.Jml_Peserta,
  
	  --(ABS(cp.Jml_Peserta)+cp.Jml_Peserta)/2 AS Jml_Peserta,  -- jika negatif maka 0
	  COALESCE --*****(Tri Ms)
	  (
		  (
			--SELECT COUNT(No_Peserta) 
			--FROM dbo.Peserta 
			--WHERE 
			--	Status = 'I' 
			--	AND No_Proposal = cp.No_Proposal

			SELECT 
				COUNT(No_Peserta)
			FROM peserta_produk
			WHERE 
				--status = 'I'
				No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
				AND No_Proposal = cp.No_Proposal 
				AND Sub_Kelompok = cp.Sub_Kelompok
			GROUP BY 
				Sub_Kelompok
		  ),
		  0 
	   ) Jml_Peserta, 
  
	  --cp.Diskon_Pr,
	  pp.Diskon_Pr,
	  --cp.Diskon_PrRp,
	  pp.Diskon_PrRp,
	  --cp.Diskon_Cp,
	  pp.Diskon_Cp,
	  --cp.Diskon_CpRp,
	  pp.Diskon_CpRp,
	  cp.Nama_Sumber_Bisnis--,
	  --cp.Sub_Kelompok,
	  --pp.Status
	FROM
		(
		SELECT
		  ph.Nama_Payor,
		  mu.Deskripsi AS Mata_Uang,
		  prs.GT_OG,
		  pa.No_Proposal,
		  prs.Nama_Perusahaan,
		  pa.No_Polis,
		  pa.Tgl_Mulai,
		  pa.Tgl_Selesai,
		  pb.Deskripsi,
		  k.Nama_Kelompok,
		  SUM(ntd.Total) AS IP,
		  SUM(ntd.Total) * pb.Pengali AS AP,
		  sk.Nama_Sub,
		  SUM(COALESCE(ntd.Santunan,0)) AS UP,
		  SUM(COALESCE(ntd.Santunan_Duka,0)) AS Santunan_Duka,
		  SUM(COALESCE(ntd.Santunan_Cacat,0)) AS Santunan_Cacat,
		  SUM(COALESCE(ntd.Jml_Peserta,0)) AS Jml_Peserta,
		  SUM(COALESCE(ntd.Diskon_Pr,0)) AS Diskon_Pr,
		  SUM(COALESCE(ntd.Diskon_PrRp,0)) AS Diskon_PrRp,
		  SUM(COALESCE(ntd.Diskon_Cp,0)) AS Diskon_Cp,
		  SUM(COALESCE(ntd.Diskon_CpRp,0)) AS Diskon_CpRp,
		  sb.nama_sumber_bisnis AS Nama_Sumber_Bisnis,
		  ntd.Sub_Kelompok
		FROM
		  dbo.Polis_Asuransi AS pa INNER JOIN 
		  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN 
		  dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode LEFT OUTER JOIN 
		  dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal
										   AND ntm.Tgl_Awal = pa.Tgl_Mulai LEFT OUTER JOIN 
		  dbo.Note_Terbit_Detail AS ntd ON ntm.No_Ref = ntd.No_Ref INNER JOIN 
		  dbo.Kelompok AS k ON LEFT(ntd.Sub_Kelompok, 2) = k.Kelompok INNER JOIN 
		  dbo.Payor_Header AS ph ON pa.Id_Payor = ph.Id_Payor INNER JOIN 
		  dbo.Mata_Uang AS mu ON pa.Mata_Uang = mu.Kode INNER JOIN
		  dbo.Sub_Kelompok AS sk ON ntd.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
		  dbo.Sumber_Bisnis AS sb ON pa.kode_bisnis = sb.kode_bisnis
		WHERE
		  (ntm.Jenis IN (1, 2, 3))
		  --(ntm.Jenis IN (1,2,3,4,6,100,101,102,103,104,106, 11,12,111,112))
      
		  --AND (ntm.Status <> 'C')
		  AND (ntm.Status = 'I') --*****(Tri Ms)

		  AND (pa.Status <> 'C')
		  AND (pa.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
		  AND (pa.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
		GROUP BY
		  mu.Deskripsi,
		  pa.No_Proposal,
		  prs.Nama_Perusahaan,
		  pb.Pengali,
		  pa.No_Polis,
		  pa.Tgl_Mulai,
		  pa.Tgl_Selesai,
		  pb.Deskripsi,
		  k.Nama_Kelompok,
		  prs.GT_OG,
		  ph.Nama_Payor,
		  sk.Nama_Sub,
		  sb.nama_sumber_bisnis,
		  ntd.Sub_Kelompok
	  ) ipap LEFT JOIN
	  (
		SELECT
		  x.No_Proposal,
		  x.Periode_Bayar,
		  x.Nama_Kelompok,
		  SUM(x.CP) AS CP,
		  x.Nama_Sub,
		  SUM(x.UP) AS UP,
		  SUM(x.Santunan_Duka) AS Santunan_Duka,
		  SUM(x.Santunan_Cacat) AS Santunan_Cacat,
		  SUM(x.Jml_Peserta) AS Jml_Peserta,
		  SUM(x.Diskon_Pr) AS Diskon_Pr,
		  SUM(x.Diskon_PrRp) AS Diskon_PrRp,
		  SUM(x.Diskon_Cp) AS Diskon_Cp,
		  SUM(x.Diskon_CpRp) AS Diskon_CpRp,
		  x.Nama_Sumber_Bisnis,
		  x.Sub_Kelompok
		FROM
		  (
			SELECT
			  pa.No_Proposal,
			  CASE
				WHEN pb.Pengali=1 THEN pb.Deskripsi
				ELSE pb.Periode + ' ' + CAST(dbo.func_PrBayarKe(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali) AS VARCHAR)
			  END AS Periode_Bayar,
			  k.Nama_Kelompok,

			  --SUM(ntd.Total * ntm.Tipe) AS CP, 
			  SUM(ntd.Jumlah * ntm.Tipe) AS CP, --*****(Tri Ms)

			  sk.Nama_Sub,
			  SUM(COALESCE(ntd.Santunan,0)) AS UP,
			  SUM(COALESCE(ntd.Santunan_Duka,0)) AS Santunan_Duka,
			  SUM(COALESCE(ntd.Santunan_Cacat,0)) AS Santunan_Cacat,
			  --SUM(COALESCE(ntd.Jml_Peserta,0)* ntm.Tipe) AS Jml_Peserta,
			  CASE 
				WHEN ntm.Tipe=1 OR CAST(SUM(COALESCE(ntd.Santunan,0)) AS numeric(18,2))>0 THEN SUM(COALESCE(ntd.Jml_Peserta,0)) -- untuk CN (-1) jumlah peserta tidak dihitung, kecuali ada nilai Santunan, agar jumlah peserta match dgn UP
			  END AS Jml_Peserta,
			  SUM(COALESCE(ntd.Diskon_Pr,0)) AS Diskon_Pr,
			  SUM(COALESCE(ntd.Diskon_PrRp,0)) AS Diskon_PrRp,
			  SUM(COALESCE(ntd.Diskon_Cp,0)) AS Diskon_Cp,
			  SUM(COALESCE(ntd.Diskon_CpRp,0)) AS Diskon_CpRp,
			  sb.nama_sumber_bisnis AS Nama_Sumber_Bisnis,
			  ntd.Sub_Kelompok
			FROM
			  dbo.Note_Terbit_Master AS ntm INNER JOIN
			  dbo.Polis_Asuransi AS pa ON ntm.No_Proposal = pa.No_Proposal INNER JOIN 
			  dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode LEFT JOIN
			  dbo.Note_Terbit_Detail AS ntd ON ntm.No_Ref = ntd.No_Ref INNER JOIN 
			  dbo.Kelompok AS k ON LEFT(ntd.Sub_Kelompok, 2) = k.Kelompok INNER JOIN
			  dbo.Sub_Kelompok AS sk ON ntd.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
			  dbo.Sumber_Bisnis AS sb ON pa.kode_bisnis = sb.kode_bisnis
			WHERE
			  pa.Status <> 'C'
			  AND pa.Status <> 'C'

			  --AND ntm.Status <> 'C'
			  AND ntm.Status = 'I' --*****(Tri Ms)

			  --AND ntm.Jenis <= 19
			  AND (ntm.Jenis IN (1,2,3,4,6,
				--100,
				101,102,103,104,106, 11,12,111,112)) --*****(Tri Ms)

			  AND ntm.Tgl_Awal >= dbo.func_PrBayarAwal(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali)
			  AND (ntm.Tgl_Awal <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (@TglPosisi <= dbo.func_PrBayarAkhir(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali) OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (pa.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (pa.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			GROUP BY
			  pa.No_Proposal,
			  k.Nama_Kelompok,
			  pb.Pengali,
			  pb.Periode,
			  pa.Tgl_Mulai, 
			  pa.Tgl_Selesai, 
			  ntm.Tgl_Awal,
			  pb.Deskripsi,
			  sk.Nama_Sub,
			  sb.nama_sumber_bisnis,
			  ntd.Sub_Kelompok,
			  ntm.Tipe
		  ) AS x
		GROUP BY
		  x.No_Proposal,
		  x.Periode_Bayar,
		  x.Nama_Kelompok,
		  x.Nama_Sub,
		  x.Nama_Sumber_Bisnis,
		  x.Sub_Kelompok
	  ) AS cp ON ipap.No_Proposal = cp.No_Proposal
				 AND ipap.Nama_Kelompok = cp.Nama_Kelompok
				 AND ipap.Sub_Kelompok = cp.Sub_Kelompok
				 AND COALESCE(ipap.Nama_Sumber_Bisnis, '') = COALESCE(cp.Nama_Sumber_Bisnis, '')	-- tambah COALESCE by Pribadi Pamungkas. 2018-02-21
	  INNER JOIN
	  dbo.Produk_Polis AS pp ON cp.No_Proposal = pp.No_Proposal
							AND cp.Sub_Kelompok = pp.Sub_Kelompok

	 -- INNER JOIN --*****(Tri Ms) -> Tidak Tampil
	 -- (
		--SELECT No_Proposal, Sub_Kelompok, SUM(COALESCE(Curr_Santunan, 0)) UP 
		--FROM dbo.Peserta_Produk 
		--WHERE Status = 'I'
		--GROUP BY No_Proposal, Sub_Kelompok
	 -- ) AS pstprod ON cp.No_Proposal = pstprod.No_Proposal
		--					AND cp.Sub_Kelompok = pstprod.Sub_Kelompok

	--WHERE ipap.No_Proposal = '2016.0619' 
	--WHERE ipap.No_Proposal = '2017.0439'
	--WHERE ipap.No_Proposal = '2016.0451'
	--WHERE ipap.No_Proposal IN('2016.0451', '2016.0619')

	ORDER BY
	  ipap.Mata_Uang,
	  ipap.No_Proposal,
	  ipap.Nama_Kelompok
END
-------------------------------------------------------------------------------------------------------------------------------------------------
ELSE IF @pi_jenis_polis = 1 --Normal --*****(Tri Ms)
BEGIN
	SELECT DISTINCT TOP (100) PERCENT
	  ipap.Nama_Payor,
	  ipap.Mata_Uang,
	  ipap.GT_OG,
	  ipap.No_Proposal,
	  ipap.Nama_Perusahaan,
	  ipap.No_Polis,
	  ipap.Tgl_Mulai,
	  ipap.Tgl_Selesai,
	  ipap.Deskripsi,
	  ipap.Nama_Kelompok,
	  ipap.IP,
	  ipap.AP,
	  cp.Periode_Bayar,
	  cp.CP,
	  cp.Nama_Sub AS Sub_Kelompok,
  
	  --cp.UP,
	  --pstprod.UP, --*****(Tri Ms)

	  (
	  CASE
		COALESCE --*****(Tri Ms)
		  (
			  (
				SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
				FROM dbo.Peserta_Produk 
				WHERE 
					--Status = 'I' 
					No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
					AND No_Proposal = cp.No_Proposal
					AND Sub_Kelompok = cp.Sub_Kelompok
			  ), 
			  0
			)
		WHEN 0 THEN 
			--0
			COALESCE --*****(Tri Ms)
			  (
				  (
					SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
					FROM dbo.Peserta_Produk 
					WHERE 
						--Status = 'I' 
						No_Peserta IN(SELECT No_Peserta 
										FROM peserta 
										WHERE 
											--status = 'I' 
											--AND 
											No_Proposal = cp.No_Proposal
											AND Tgl_Selesai <= @TglPosisi)
						AND No_Proposal = cp.No_Proposal
						AND Sub_Kelompok = cp.Sub_Kelompok
				  ), 
				  0
				)
		ELSE 
			COALESCE --*****(Tri Ms)
			  (
				  (
					SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
					FROM dbo.Peserta_Produk 
					WHERE 
						--Status = 'I' 
						No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
						AND No_Proposal = cp.No_Proposal
						AND Sub_Kelompok = cp.Sub_Kelompok
				  ), 
				  0
				)
	  END 
	  ) UP,

	 -- COALESCE --*****(Tri Ms)
	 -- (
		--  (
		--	SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
		--	FROM dbo.Peserta_Produk 
		--	WHERE 
		--		--Status = 'I' 
		--		No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
		--		AND No_Proposal = cp.No_Proposal
		--		AND Sub_Kelompok = cp.Sub_Kelompok
		--  ), 
		--  0
		--) UP,
  
	  cp.Santunan_Duka,
	  cp.Santunan_Cacat,
	  --ipap.Jml_Peserta,
  
	  --(ABS(cp.Jml_Peserta)+cp.Jml_Peserta)/2 AS Jml_Peserta,  -- jika negatif maka 0
	  COALESCE --*****(Tri Ms)
	  (
		  (
			--SELECT COUNT(No_Peserta) 
			--FROM dbo.Peserta 
			--WHERE 
			--	Status = 'I' 
			--	AND No_Proposal = cp.No_Proposal

			SELECT 
				COUNT(No_Peserta)
			FROM peserta_produk
			WHERE 
				--status = 'I'
				No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
				AND No_Proposal = cp.No_Proposal 
				AND Sub_Kelompok = cp.Sub_Kelompok
			GROUP BY 
				Sub_Kelompok
		  ),
		  0 
	   ) Jml_Peserta, 
  
	  --cp.Diskon_Pr,
	  pp.Diskon_Pr,
	  --cp.Diskon_PrRp,
	  pp.Diskon_PrRp,
	  --cp.Diskon_Cp,
	  pp.Diskon_Cp,
	  --cp.Diskon_CpRp,
	  pp.Diskon_CpRp,
	  cp.Nama_Sumber_Bisnis--,
	  --cp.Sub_Kelompok,
	  --pp.Status
	FROM
		(
		SELECT
		  ph.Nama_Payor,
		  mu.Deskripsi AS Mata_Uang,
		  prs.GT_OG,
		  pa.No_Proposal,
		  prs.Nama_Perusahaan,
		  pa.No_Polis,
		  pa.Tgl_Mulai,
		  pa.Tgl_Selesai,
		  pb.Deskripsi,
		  k.Nama_Kelompok,
		  SUM(ntd.Total) AS IP,
		  SUM(ntd.Total) * pb.Pengali AS AP,
		  sk.Nama_Sub,
		  SUM(COALESCE(ntd.Santunan,0)) AS UP,
		  SUM(COALESCE(ntd.Santunan_Duka,0)) AS Santunan_Duka,
		  SUM(COALESCE(ntd.Santunan_Cacat,0)) AS Santunan_Cacat,
		  SUM(COALESCE(ntd.Jml_Peserta,0)) AS Jml_Peserta,
		  SUM(COALESCE(ntd.Diskon_Pr,0)) AS Diskon_Pr,
		  SUM(COALESCE(ntd.Diskon_PrRp,0)) AS Diskon_PrRp,
		  SUM(COALESCE(ntd.Diskon_Cp,0)) AS Diskon_Cp,
		  SUM(COALESCE(ntd.Diskon_CpRp,0)) AS Diskon_CpRp,
		  sb.nama_sumber_bisnis AS Nama_Sumber_Bisnis,
		  ntd.Sub_Kelompok
		FROM
		  dbo.Polis_Asuransi AS pa INNER JOIN 
		  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN 
		  dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode LEFT OUTER JOIN 
		  dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal
										   AND ntm.Tgl_Awal = pa.Tgl_Mulai LEFT OUTER JOIN 
		  dbo.Note_Terbit_Detail AS ntd ON ntm.No_Ref = ntd.No_Ref INNER JOIN 
		  dbo.Kelompok AS k ON LEFT(ntd.Sub_Kelompok, 2) = k.Kelompok INNER JOIN 
		  dbo.Payor_Header AS ph ON pa.Id_Payor = ph.Id_Payor INNER JOIN 
		  dbo.Mata_Uang AS mu ON pa.Mata_Uang = mu.Kode INNER JOIN
		  dbo.Sub_Kelompok AS sk ON ntd.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
		  dbo.Sumber_Bisnis AS sb ON pa.kode_bisnis = sb.kode_bisnis
		WHERE
		  (ntm.Jenis IN (1, 2, 3))
		  --(ntm.Jenis IN (1,2,3,4,6,100,101,102,103,104,106, 11,12,111,112))
      
		  --AND (ntm.Status <> 'C')
		  AND (ntm.Status = 'I') --*****(Tri Ms)

		  --AND (pa.Status <> 'C')
		  AND (pa.Status = 'I') --*****(Tri Ms)

		  AND (pa.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
		  AND (pa.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)

		  AND (pa.Open_Polis = 0) --*****(Tri Ms)
		GROUP BY
		  mu.Deskripsi,
		  pa.No_Proposal,
		  prs.Nama_Perusahaan,
		  pb.Pengali,
		  pa.No_Polis,
		  pa.Tgl_Mulai,
		  pa.Tgl_Selesai,
		  pb.Deskripsi,
		  k.Nama_Kelompok,
		  prs.GT_OG,
		  ph.Nama_Payor,
		  sk.Nama_Sub,
		  sb.nama_sumber_bisnis,
		  ntd.Sub_Kelompok
	  ) ipap LEFT JOIN
	  (
		SELECT
		  x.No_Proposal,
		  x.Periode_Bayar,
		  x.Nama_Kelompok,
		  SUM(x.CP) AS CP,
		  x.Nama_Sub,
		  SUM(x.UP) AS UP,
		  SUM(x.Santunan_Duka) AS Santunan_Duka,
		  SUM(x.Santunan_Cacat) AS Santunan_Cacat,
		  SUM(x.Jml_Peserta) AS Jml_Peserta,
		  SUM(x.Diskon_Pr) AS Diskon_Pr,
		  SUM(x.Diskon_PrRp) AS Diskon_PrRp,
		  SUM(x.Diskon_Cp) AS Diskon_Cp,
		  SUM(x.Diskon_CpRp) AS Diskon_CpRp,
		  x.Nama_Sumber_Bisnis,
		  x.Sub_Kelompok
		FROM
		  (
			SELECT
			  pa.No_Proposal,
			  CASE
				WHEN pb.Pengali=1 THEN pb.Deskripsi
				ELSE pb.Periode + ' ' + CAST(dbo.func_PrBayarKe(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali) AS VARCHAR)
			  END AS Periode_Bayar,
			  k.Nama_Kelompok,

			  --SUM(ntd.Total * ntm.Tipe) AS CP, 
			  SUM(ntd.Jumlah * ntm.Tipe) AS CP, --*****(Tri Ms)

			  sk.Nama_Sub,
			  SUM(COALESCE(ntd.Santunan,0)) AS UP,
			  SUM(COALESCE(ntd.Santunan_Duka,0)) AS Santunan_Duka,
			  SUM(COALESCE(ntd.Santunan_Cacat,0)) AS Santunan_Cacat,
			  --SUM(COALESCE(ntd.Jml_Peserta,0)* ntm.Tipe) AS Jml_Peserta,
			  CASE 
				WHEN ntm.Tipe=1 OR CAST(SUM(COALESCE(ntd.Santunan,0)) AS numeric(18,2))>0 THEN SUM(COALESCE(ntd.Jml_Peserta,0)) -- untuk CN (-1) jumlah peserta tidak dihitung, kecuali ada nilai Santunan, agar jumlah peserta match dgn UP
			  END AS Jml_Peserta,
			  SUM(COALESCE(ntd.Diskon_Pr,0)) AS Diskon_Pr,
			  SUM(COALESCE(ntd.Diskon_PrRp,0)) AS Diskon_PrRp,
			  SUM(COALESCE(ntd.Diskon_Cp,0)) AS Diskon_Cp,
			  SUM(COALESCE(ntd.Diskon_CpRp,0)) AS Diskon_CpRp,
			  sb.nama_sumber_bisnis AS Nama_Sumber_Bisnis,
			  ntd.Sub_Kelompok
			FROM
			  dbo.Note_Terbit_Master AS ntm INNER JOIN
			  dbo.Polis_Asuransi AS pa ON ntm.No_Proposal = pa.No_Proposal INNER JOIN 
			  dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode LEFT JOIN
			  dbo.Note_Terbit_Detail AS ntd ON ntm.No_Ref = ntd.No_Ref INNER JOIN 
			  dbo.Kelompok AS k ON LEFT(ntd.Sub_Kelompok, 2) = k.Kelompok INNER JOIN
			  dbo.Sub_Kelompok AS sk ON ntd.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
			  dbo.Sumber_Bisnis AS sb ON pa.kode_bisnis = sb.kode_bisnis
			WHERE
			  --pa.Status <> 'C'
			  --AND pa.Status <> 'C'
			  pa.Status = 'I' --*****(Tri Ms)

			  --AND ntm.Status <> 'C'
			  AND ntm.Status = 'I' --*****(Tri Ms)

			  --AND ntm.Jenis <= 19
			  AND (ntm.Jenis IN (1,2,3,4,6,
				--100,
				101,102,103,104,106, 11,12,111,112)) --*****(Tri Ms)

			  AND ntm.Tgl_Awal >= dbo.func_PrBayarAwal(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali)
			  AND (ntm.Tgl_Awal <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (@TglPosisi <= dbo.func_PrBayarAkhir(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali) OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (pa.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (pa.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)

			  AND (pa.Open_Polis = 0) --*****(Tri Ms)
			GROUP BY
			  pa.No_Proposal,
			  k.Nama_Kelompok,
			  pb.Pengali,
			  pb.Periode,
			  pa.Tgl_Mulai, 
			  pa.Tgl_Selesai, 
			  ntm.Tgl_Awal,
			  pb.Deskripsi,
			  sk.Nama_Sub,
			  sb.nama_sumber_bisnis,
			  ntd.Sub_Kelompok,
			  ntm.Tipe
		  ) AS x
		GROUP BY
		  x.No_Proposal,
		  x.Periode_Bayar,
		  x.Nama_Kelompok,
		  x.Nama_Sub,
		  x.Nama_Sumber_Bisnis,
		  x.Sub_Kelompok
	  ) AS cp ON ipap.No_Proposal = cp.No_Proposal
				 AND ipap.Nama_Kelompok = cp.Nama_Kelompok
				 AND ipap.Sub_Kelompok = cp.Sub_Kelompok
				 AND COALESCE(ipap.Nama_Sumber_Bisnis, '') = COALESCE(cp.Nama_Sumber_Bisnis, '')	-- tambah COALESCE by Pribadi Pamungkas. 2018-02-21
	  INNER JOIN
	  dbo.Produk_Polis AS pp ON cp.No_Proposal = pp.No_Proposal
							AND cp.Sub_Kelompok = pp.Sub_Kelompok

	 -- INNER JOIN --*****(Tri Ms) -> Tidak Tampil
	 -- (
		--SELECT No_Proposal, Sub_Kelompok, SUM(COALESCE(Curr_Santunan, 0)) UP 
		--FROM dbo.Peserta_Produk 
		--WHERE Status = 'I'
		--GROUP BY No_Proposal, Sub_Kelompok
	 -- ) AS pstprod ON cp.No_Proposal = pstprod.No_Proposal
		--					AND cp.Sub_Kelompok = pstprod.Sub_Kelompok

	--WHERE ipap.No_Proposal = '2016.0619' 
	--WHERE ipap.No_Proposal = '2017.0439'
	--WHERE ipap.No_Proposal = '2016.0451'
	--WHERE ipap.No_Proposal IN('2016.0451', '2016.0619')

	ORDER BY
	  ipap.Mata_Uang,
	  ipap.No_Proposal,
	  ipap.Nama_Kelompok
END
-------------------------------------------------------------------------------------------------------------------------------------------------
ELSE IF @pi_jenis_polis = 2 --Open --*****(Tri Ms)
BEGIN
	SELECT DISTINCT TOP (100) PERCENT
	  ipap.Nama_Payor,
	  ipap.Mata_Uang,
	  ipap.GT_OG,
	  ipap.No_Proposal,
	  ipap.Nama_Perusahaan,
	  ipap.No_Polis,
	  ipap.Tgl_Mulai,
	  ipap.Tgl_Selesai,
	  ipap.Deskripsi,
	  --ipap.Nama_Kelompok,
	  ipap.IP,
	  ipap.AP,
	  cp.Periode_Bayar,
	  --cp.CP,
	  COALESCE --*****(Tri Ms)
	  (
		  (
			SELECT SUM(COALESCE(Curr_Premi, 0)) CP 
			FROM dbo.Peserta_Produk 
			WHERE 
				--Status = 'I' 
				--No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
				No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal AND Tgl_Selesai > @TglPosisi)
				AND No_Proposal = cp.No_Proposal
				AND Sub_Kelompok = cp.Sub_Kelompok
		  ), 
		  0
		) CP, 

	  cp.Nama_Sub AS Sub_Kelompok,
  
	  --cp.UP,
	  --pstprod.UP, --*****(Tri Ms)
	  COALESCE --*****(Tri Ms)
	  (
		  (
			SELECT SUM(COALESCE(Curr_Santunan, 0)) UP 
			FROM dbo.Peserta_Produk 
			WHERE 
				--Status = 'I' 
				--No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
				--No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal AND (Tgl_Mulai > DATEADD(YEAR, -1, @TglPosisi)))
				No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal AND Tgl_Selesai > @TglPosisi)
				AND No_Proposal = cp.No_Proposal
				AND Sub_Kelompok = cp.Sub_Kelompok
		  ), 
		  0
		) UP,
  
	  cp.Santunan_Duka,
	  cp.Santunan_Cacat,
	  --ipap.Jml_Peserta,
  
	  --(ABS(cp.Jml_Peserta)+cp.Jml_Peserta)/2 AS Jml_Peserta,  -- jika negatif maka 0
	  COALESCE --*****(Tri Ms)
	  (
		  (
			--SELECT COUNT(No_Peserta) 
			--FROM dbo.Peserta 
			--WHERE 
			--	Status = 'I' 
			--	AND No_Proposal = cp.No_Proposal

			SELECT 
				COUNT(No_Peserta)
			FROM peserta_produk
			WHERE 
				--status = 'I'
				--No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal)
				--No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal AND (Tgl_Mulai > DATEADD(YEAR, -1, @TglPosisi)))
				No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal AND Tgl_Selesai > @TglPosisi)
				AND No_Proposal = cp.No_Proposal 
				AND Sub_Kelompok = cp.Sub_Kelompok
			GROUP BY 
				Sub_Kelompok
		  ),
		  0 
	   ) Jml_Peserta, 
  
	  --cp.Diskon_Pr,
	  pp.Diskon_Pr,
	  --cp.Diskon_PrRp,
	  pp.Diskon_PrRp,
	  --cp.Diskon_Cp,
	  pp.Diskon_Cp,
	  --cp.Diskon_CpRp,
	  pp.Diskon_CpRp,

	  --cp.Nama_Sumber_Bisnis--,
	  ipap.Nama_Sumber_Bisnis--, --*****(Tri Ms)

	  --cp.Sub_Kelompok,
	  --pp.Status
	FROM
		(
		SELECT
		  ph.Nama_Payor,
		  mu.Deskripsi AS Mata_Uang,
		  prs.GT_OG,
		  pa.No_Proposal,
		  prs.Nama_Perusahaan,
		  pa.No_Polis,
		  pa.Tgl_Mulai,
		  pa.Tgl_Selesai,
		  pb.Deskripsi,
		  --k.Nama_Kelompok,
		  SUM(ntd.Total) AS IP,
		  SUM(ntd.Total) * pb.Pengali AS AP,
		  sk.Nama_Sub,
		  SUM(COALESCE(ntd.Santunan,0)) AS UP,
		  SUM(COALESCE(ntd.Santunan_Duka,0)) AS Santunan_Duka,
		  SUM(COALESCE(ntd.Santunan_Cacat,0)) AS Santunan_Cacat,
		  SUM(COALESCE(ntd.Jml_Peserta,0)) AS Jml_Peserta,
		  SUM(COALESCE(ntd.Diskon_Pr,0)) AS Diskon_Pr,
		  SUM(COALESCE(ntd.Diskon_PrRp,0)) AS Diskon_PrRp,
		  SUM(COALESCE(ntd.Diskon_Cp,0)) AS Diskon_Cp,
		  SUM(COALESCE(ntd.Diskon_CpRp,0)) AS Diskon_CpRp,
		  sb.nama_sumber_bisnis AS Nama_Sumber_Bisnis,
		  ntd.Sub_Kelompok
		FROM
		  dbo.Polis_Asuransi AS pa INNER JOIN 
		  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN 
		  dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode LEFT OUTER JOIN 
		  dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal
										   --AND ntm.Tgl_Awal = pa.Tgl_Mulai LEFT OUTER JOIN 
										   LEFT OUTER JOIN --*****(Tri Ms)
		  dbo.Note_Terbit_Detail AS ntd ON ntm.No_Ref = ntd.No_Ref INNER JOIN 
		  --dbo.Kelompok AS k ON LEFT(ntd.Sub_Kelompok, 2) = k.Kelompok INNER JOIN --*****(Tri Ms)
		  dbo.Payor_Header AS ph ON pa.Id_Payor = ph.Id_Payor INNER JOIN 
		  dbo.Mata_Uang AS mu ON pa.Mata_Uang = mu.Kode INNER JOIN
		  dbo.Sub_Kelompok AS sk ON ntd.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
		  dbo.Sumber_Bisnis AS sb ON pa.kode_bisnis = sb.kode_bisnis
		WHERE
		  (ntm.Jenis IN (1, 2, 3))
		  --(ntm.Jenis IN (1,2,3,4,6,100,101,102,103,104,106, 11,12,111,112))
      
		  --AND (ntm.Status <> 'C')
		  AND (ntm.Status = 'I') --*****(Tri Ms)

		  --AND (pa.Status <> 'C')
		  AND (pa.Status = 'I')--*****(Tri Ms)

		  AND (pa.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
		  --AND (pa.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)

		  AND (pa.Open_Polis = 1) --*****(Tri Ms)
		GROUP BY
		  mu.Deskripsi,
		  pa.No_Proposal,
		  prs.Nama_Perusahaan,
		  pb.Pengali,
		  pa.No_Polis,
		  pa.Tgl_Mulai,
		  pa.Tgl_Selesai,
		  pb.Deskripsi,
		  --k.Nama_Kelompok,
		  prs.GT_OG,
		  ph.Nama_Payor,
		  sk.Nama_Sub,
		  sb.nama_sumber_bisnis,
		  ntd.Sub_Kelompok
	  ) ipap LEFT JOIN
	  (
		SELECT
		  x.No_Proposal,
		  x.Periode_Bayar,
		  --x.Nama_Kelompok,
		  SUM(x.CP) AS CP,
		  x.Nama_Sub,
		  SUM(x.UP) AS UP,
		  SUM(x.Santunan_Duka) AS Santunan_Duka,
		  SUM(x.Santunan_Cacat) AS Santunan_Cacat,
		  SUM(x.Jml_Peserta) AS Jml_Peserta,
		  SUM(x.Diskon_Pr) AS Diskon_Pr,
		  SUM(x.Diskon_PrRp) AS Diskon_PrRp,
		  SUM(x.Diskon_Cp) AS Diskon_Cp,
		  SUM(x.Diskon_CpRp) AS Diskon_CpRp,
		  --x.Nama_Sumber_Bisnis,
		  x.Sub_Kelompok
		FROM
		  (
			SELECT
			  pa.No_Proposal,
			  CASE
				WHEN pb.Pengali=1 THEN pb.Deskripsi
				ELSE pb.Periode + ' ' + CAST(dbo.func_PrBayarKe(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali) AS VARCHAR)
			  END AS Periode_Bayar,
			  --k.Nama_Kelompok,

			  --SUM(ntd.Total * ntm.Tipe) AS CP, 
			  SUM(ntd.Jumlah * ntm.Tipe) AS CP, --*****(Tri Ms)

			  sk.Nama_Sub,
			  SUM(COALESCE(ntd.Santunan,0)) AS UP,
			  SUM(COALESCE(ntd.Santunan_Duka,0)) AS Santunan_Duka,
			  SUM(COALESCE(ntd.Santunan_Cacat,0)) AS Santunan_Cacat,
			  --SUM(COALESCE(ntd.Jml_Peserta,0)* ntm.Tipe) AS Jml_Peserta,
			  CASE 
				WHEN ntm.Tipe=1 OR CAST(SUM(COALESCE(ntd.Santunan,0)) AS numeric(18,2))>0 THEN SUM(COALESCE(ntd.Jml_Peserta,0)) -- untuk CN (-1) jumlah peserta tidak dihitung, kecuali ada nilai Santunan, agar jumlah peserta match dgn UP
			  END AS Jml_Peserta,
			  SUM(COALESCE(ntd.Diskon_Pr,0)) AS Diskon_Pr,
			  SUM(COALESCE(ntd.Diskon_PrRp,0)) AS Diskon_PrRp,
			  SUM(COALESCE(ntd.Diskon_Cp,0)) AS Diskon_Cp,
			  SUM(COALESCE(ntd.Diskon_CpRp,0)) AS Diskon_CpRp,
			  --sb.nama_sumber_bisnis AS Nama_Sumber_Bisnis,
			  ntd.Sub_Kelompok
			FROM
			  dbo.Note_Terbit_Master AS ntm INNER JOIN
			  dbo.Polis_Asuransi AS pa ON ntm.No_Proposal = pa.No_Proposal INNER JOIN 
			  dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode LEFT JOIN
			  dbo.Note_Terbit_Detail AS ntd ON ntm.No_Ref = ntd.No_Ref INNER JOIN 
			  --dbo.Kelompok AS k ON LEFT(ntd.Sub_Kelompok, 2) = k.Kelompok INNER JOIN --*****(Tri Ms)
			  dbo.Sub_Kelompok AS sk ON ntd.Sub_Kelompok = sk.Sub_Kelompok --LEFT JOIN
			  --dbo.Sumber_Bisnis AS sb ON pa.kode_bisnis = sb.kode_bisnis
			WHERE
			  --pa.Status <> 'C'
			  --AND pa.Status <> 'C'
			  pa.Status = 'I'

			  --AND ntm.Status <> 'C'
			  AND ntm.Status = 'I' --*****(Tri Ms)

			  --AND ntm.Jenis <= 19
			  AND (ntm.Jenis IN (1,2,3,4,6,
				--100,
				101,102,103,104,106, 11,12,111,112)) --*****(Tri Ms)

			  AND ntm.Tgl_Awal >= dbo.func_PrBayarAwal(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali)
			  AND (ntm.Tgl_Awal <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			  --AND (@TglPosisi <= dbo.func_PrBayarAkhir(pa.Tgl_Mulai, pa.Tgl_Selesai, ntm.Tgl_Awal, pb.Pengali) OR ISNULL(@TglPosisiYa, 0) = 0)
			  AND (pa.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
			  --AND (pa.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)

			  AND (pa.Open_Polis = 1) --*****(Tri Ms)
			GROUP BY
			  pa.No_Proposal,
			  --k.Nama_Kelompok,
			  pb.Pengali,
			  pb.Periode,
			  pa.Tgl_Mulai, 
			  pa.Tgl_Selesai, 
			  ntm.Tgl_Awal,
			  pb.Deskripsi,
			  sk.Nama_Sub,
			  --sb.nama_sumber_bisnis,
			  ntd.Sub_Kelompok,
			  ntm.Tipe
		  ) AS x
		GROUP BY
		  x.No_Proposal,
		  x.Periode_Bayar,
		  --x.Nama_Kelompok,
		  x.Nama_Sub,
		  --x.Nama_Sumber_Bisnis,
		  x.Sub_Kelompok
	  ) AS cp ON ipap.No_Proposal = cp.No_Proposal
				 --AND ipap.Nama_Kelompok = cp.Nama_Kelompok
				 AND ipap.Sub_Kelompok = cp.Sub_Kelompok
				 --AND ipap.Nama_Sumber_Bisnis = cp.Nama_Sumber_Bisnis
	  INNER JOIN
	  dbo.Produk_Polis AS pp ON cp.No_Proposal = pp.No_Proposal
							AND cp.Sub_Kelompok = pp.Sub_Kelompok

	 -- INNER JOIN --*****(Tri Ms) -> Tidak Tampil
	 -- (
		--SELECT No_Proposal, Sub_Kelompok, SUM(COALESCE(Curr_Santunan, 0)) UP 
		--FROM dbo.Peserta_Produk 
		--WHERE Status = 'I'
		--GROUP BY No_Proposal, Sub_Kelompok
	 -- ) AS pstprod ON cp.No_Proposal = pstprod.No_Proposal
		--					AND cp.Sub_Kelompok = pstprod.Sub_Kelompok

	--WHERE ipap.No_Proposal = '2007.0613' 
	--WHERE ipap.No_Proposal = '2017.0439'
	--WHERE ipap.No_Proposal = '2016.0451'
	--WHERE ipap.No_Proposal IN('2016.0451', '2016.0619')

	WHERE --*****(Tri Ms)
		COALESCE 
		  (
			  (
				--SELECT COUNT(No_Peserta) 
				--FROM dbo.Peserta 
				--WHERE 
				--	Status = 'I' 
				--	AND No_Proposal = cp.No_Proposal

				SELECT 
					COUNT(No_Peserta)
				FROM peserta_produk
				WHERE 
					--status = 'I'
					No_Peserta IN(SELECT No_Peserta FROM peserta WHERE status = 'I' AND No_Proposal = cp.No_Proposal AND (Tgl_Mulai > DATEADD(YEAR, -1, @TglPosisi)))
					AND No_Proposal = cp.No_Proposal 
					AND Sub_Kelompok = cp.Sub_Kelompok
				GROUP BY 
					Sub_Kelompok
			  ),
			  0 
		   ) <> 0

	ORDER BY
	  ipap.Mata_Uang,
	  ipap.No_Proposal--,
	  --ipap.Nama_Kelompok
END

