USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep16112]    Script Date: 09/06/2017 16:16:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- last modified by Pribadi Pamungkas. 2017-02-03
ALTER PROCEDURE [dbo].[proc_Rep16112]
      @TglPosisi DATETIME,
      @TglPosisiYa BIT
AS 
SELECT 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.Sub_Kelompok,
  cp.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
  cp.Diskon_Pr,
  cp.Diskon_PrRp,
  cp.Diskon_Cp,
  cp.Diskon_CpRp,
  cp.Nama_Sumber_Bisnis
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 AS Sub_Kelompok,
      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
    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))
      AND (ntm.Status <> 'C')
      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
  ) ipap LEFT JOIN
  (
    SELECT
      x.No_Proposal,
      x.Periode_Bayar,
      x.Nama_Kelompok,
      SUM(x.CP) AS CP,
      x.Sub_Kelompok,
      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
    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,
          sk.Nama_Sub AS Sub_Kelompok,
          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,
          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
        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.Jenis <= 19
          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
      ) AS x
    GROUP BY
      x.No_Proposal,
      x.Periode_Bayar,
      x.Nama_Kelompok,
      x.Sub_Kelompok,
      x.Nama_Sumber_Bisnis
  ) 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
ORDER BY
  ipap.Mata_Uang,
  ipap.No_Proposal,
  ipap.Nama_Kelompok