USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep16112]    Script Date: 01/13/2017 14:00:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ALTER PROCEDURE [dbo].[proc_Rep16112]
DECLARE
      @TglPosisi DATETIME,
      @TglPosisiYa BIT
--AS 
SELECT TOP (100) PERCENT
  Payor_Header.Nama_Payor,
  Mata_Uang.Deskripsi AS Mata_Uang,
  Perusahaan.GT_OG,
  Polis_Asuransi.No_Proposal,
  Perusahaan.Nama_Perusahaan,
  Polis_Asuransi.No_Polis,
  Polis_Asuransi.Tgl_Mulai,
  Polis_Asuransi.Tgl_Selesai,
  Periode_Bayar.Deskripsi,
  Kelompok.Nama_Kelompok,
  SUM(Note_Terbit_Detail.Total) AS IP,
  SUM(Note_Terbit_Detail.Total) * Periode_Bayar.Pengali AS AP,
  Note_Terbit_Detail.Sub_Kelompok,
  Note_Terbit_Master.No_Ref,
  Note_Terbit_Master.Status
FROM
  Polis_Asuransi INNER JOIN 
  Perusahaan         ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs INNER JOIN 
  Periode_Bayar      ON Polis_Asuransi.Periode_Bayar = Periode_Bayar.Kode LEFT OUTER JOIN 
  Note_Terbit_Master ON Polis_Asuransi.No_Proposal = Note_Terbit_Master.No_Proposal
                        AND Note_Terbit_Master.Tgl_Awal = Polis_Asuransi.Tgl_Mulai LEFT OUTER JOIN 
  Note_Terbit_Detail ON Note_Terbit_Master.No_Ref = Note_Terbit_Detail.No_Ref INNER JOIN 
  Kelompok           ON LEFT(Note_Terbit_Detail.Sub_Kelompok, 2) = Kelompok.Kelompok INNER JOIN 
  Payor_Header       ON Polis_Asuransi.Id_Payor = Payor_Header.Id_Payor INNER JOIN 
  Mata_Uang          ON Polis_Asuransi.Mata_Uang = Mata_Uang.Kode
WHERE
  (Note_Terbit_Master.Jenis IN (1, 2, 3))
  AND (Note_Terbit_Master.Status <> 'C')
  AND (Polis_Asuransi.Status <> 'C')
  --AND (Polis_Asuransi.Tgl_Mulai <= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
  --AND (Polis_Asuransi.Tgl_Selesai >= @TglPosisi OR ISNULL(@TglPosisiYa, 0) = 0)
  AND Polis_Asuransi.No_Proposal = '2015.0652'  -- testing
  AND Note_Terbit_Detail.Sub_Kelompok = '0200'  -- testing
  AND Note_Terbit_Master.No_Ref = '2016.004896'
GROUP BY
  Mata_Uang.Deskripsi,
  Polis_Asuransi.No_Proposal,
  Perusahaan.Nama_Perusahaan,
  Periode_Bayar.Pengali,
  Polis_Asuransi.No_Polis,
  Polis_Asuransi.Tgl_Mulai,
  Polis_Asuransi.Tgl_Selesai,
  Periode_Bayar.Deskripsi,
  Kelompok.Nama_Kelompok,
  Perusahaan.GT_OG,
  Payor_Header.Nama_Payor,
  Note_Terbit_Detail.Sub_Kelompok,
  Note_Terbit_Master.No_Ref,
  Note_Terbit_Master.Status
ORDER BY
  Mata_Uang.Deskripsi,
  Polis_Asuransi.No_Proposal,
  Kelompok.Nama_Kelompok
--------------------------------------------------------------------------------------------------------
SELECT 
  dbo.Note_Terbit_Master.No_Proposal, 
  --dbo.Peserta_Produk_Detail.No_Peserta, 
  dbo.Peserta_Produk_Detail.Id_Produk, 
  dbo.Peserta_Produk_Detail.Sub_Kelompok, -- testing
  dbo.Note_Terbit_Master.No_Ref,
  dbo.Note_Terbit_Master.Status,
  --SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0)) AS Jml_Premi
  SUM(dbo.Note_Terbit_Master.Tipe* ABS(isnull(dbo.Peserta_Produk_Detail.Jumlah,0))) AS Jml_Premi,
  dbo.Peserta_Produk_Detail.Jumlah
FROM 
  dbo.Note_Terbit_Master INNER JOIN
  dbo.Peserta_Produk_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Peserta_Produk_Detail.No_Ref INNER JOIN
  dbo.Peserta ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal 
                 AND dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta 
WHERE   
  (dbo.Note_Terbit_Master.Jenis <= 19) 
  AND (dbo.Note_Terbit_Master.Status<>'C') 
  AND (dbo.Note_Terbit_Master.No_Proposal = '2015.0652')  -- testing
  --AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglPremi1 OR isnull(@TglPremi1Ya,0)=0)
  --AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglPremi2 OR isnull(@TglPremi2Ya,0)=0)
  AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0200'  -- testing
  AND dbo.Note_Terbit_Master.No_Ref = '2016.004896'
GROUP BY 
  dbo.Note_Terbit_Master.No_Proposal, 
  --dbo.Peserta_Produk_Detail.No_Peserta, 
  dbo.Peserta_Produk_Detail.Id_Produk,
  dbo.Peserta_Produk_Detail.Sub_Kelompok,
  dbo.Note_Terbit_Master.No_Ref,
  dbo.Note_Terbit_Master.Status,
  dbo.Peserta_Produk_Detail.Jumlah