USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10760]    Script Date: 04/20/2018 15:37:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-04-20. RQ2018040785 => Ibu Yis. Tambah kolom tanggal Terima, Penerima, dan Tanggal yang meng-input Tanggal Terima
ALTER Procedure [dbo].[proc_Rep10760]
@TglBuat1 datetime,
@TglBuat2 datetime,
@TglProduksi1 datetime,
@TglProduksi2 datetime,
@JenisNote varchar(200),
@CatNote Int,
@TipeNote int,
@NoPolis varchar(11),
@AdmedikaYa int,
@Valas int,
@SubInit int,
@GTOG int,
@Afiliasi Bit,
@RepJenis Int,
@IdPayor varchar(2),
@AsoYa bit,
@IdKelKlaim varchar(4),
@TglBuat1Ya bit,
@TglBuat2Ya bit,
@TglProduksi1Ya bit,
@TglProduksi2Ya bit
AS
BEGIN TRAN
-- SET @RepJenis=3
--Rekap Penerbitan Debit/Credit Note
  SET NOCOUNT ON
  --DECLARE @Id_Kel1 varchar(4)
  --DECLARE @Id_Kel2 varchar(4)
  --IF isnull(@IdKelKlaim,'')<>''
  --BEGIN
  --  SET @Id_Kel1= LEFT(isnull(@IdKelKlaim,''),2)+'00'
  --  SET @Id_Kel2= LEFT(isnull(@IdKelKlaim,''),2)+'99'
  --END
 
  EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10760'
  CREATE TABLE [TmpInsurance].[dbo].[Rep10760] (
	[No_Ref] [varchar] (11)  NOT NULL ,
	[Tgl_Buat] [datetime] NULL ,
	[No_Note] [varchar] (25)  NULL ,
--	[No_Polis] [varchar] (11)  NULL ,
	[Nama_Perusahaan] [varchar] (60)  NULL ,
--	[GT_OG] [varchar] (2)  NULL ,
--	[Sub_Init] [varchar] (1)  NULL ,
	[Jml_Pst] [int] NULL ,
	[Sub_Total] [float] NULL ,
	[Diskon] [float] NULL ,
	[Total] [float] NULL ,
	[Sub_0100] [float] NULL ,
	[Sub_0200] [float] NULL ,
	[Sub_0301] [float] NULL ,
	[Sub_0302] [float] NULL ,
	[Sub_0303] [float] NULL ,
	[Sub_0304] [float] NULL ,
	[Sub_0305] [float] NULL ,
	[Sub_0306] [float] NULL ,
	[Sub_0400] [float] NULL ,
	CONSTRAINT [PK_Rep10760] PRIMARY KEY  CLUSTERED 
	([No_Ref])  ON [PRIMARY]) ON [PRIMARY]

DECLARE @No_Ref varchar(11) 
DECLARE @Tgl_Buat datetime
DECLARE @Nama_Perusahaan varchar(50) 
DECLARE @No_Polis varchar(11) 
DECLARE @GT_OG varchar(2) 
DECLARE @Sub_Init varchar(2) 
DECLARE @No_Note varchar(25) 
DECLARE @Jml_Pst int
DECLARE @Sub_Total float 
DECLARE @Diskon float 
DECLARE @Total float 

DECLARE @Sub_0100 float
DECLARE @Sub_0200 float
DECLARE @Sub_0301 float
DECLARE @Sub_0302 float
DECLARE @Sub_0303 float
DECLARE @Sub_0304 float
DECLARE @Sub_0305 float
DECLARE @Sub_0306 float
DECLARE @Sub_0400 float
IF isnull(@CatNote,0)=1 SET @JenisNote=isnull(@JenisNote,'')+'-001-002-003-004-005-006-009-011-012-019-031-032-033-034-099'
ELSE IF isnull(@CatNote,0)=2 SET @JenisNote=isnull(@JenisNote,'')+'-020' 
ELSE IF isnull(@CatNote,0)=3 SET @JenisNote=isnull(@JenisNote,'')+'-102' 


IF @RepJenis=0 
BEGIN
-- EXEC Proc_gRepColumnSave '10760', 0
 EXEC gProc_RepColumnLoad '10760', 0
 SELECT  dbo.Note_Terbit_Master.No_Note, 
         dbo.Note_Terbit_Master.Tgl_buat, 
         --dbo.Note_Terbit_Master.Tgl_Produksi AS Tgl_buat,  -- Prib [2014-02-18] 
         dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']' as Nama_Perusahaan,         
         dbo.Note_Terbit_Master.Jml_Peserta, 
         dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init as Kode_Prs,
         dbo.Sub_Kelompok.Urutan, 
         dbo.Sub_Kelompok.Nama_Sub,
         dbo.Note_Terbit_Master.No_Ref, 
         Sum(dbo.Note_Terbit_Master.Tipe*dbo.Note_Terbit_Detail.Jumlah)- isnull(dbo.Note_Terbit_Master.PPN,0) as Sub_Total,  
         Sum(dbo.Note_Terbit_Master.Tipe*isnull(dbo.Note_Terbit_Detail.Diskon,0)) as Diskon,
         Sum(isnull(dbo.Note_Terbit_Detail.Jml_Peserta,0)) as Jml_Pst,
         Sum(dbo.Note_Terbit_Master.Tipe*(dbo.Note_Terbit_Detail.Jumlah - isnull(dbo.Note_Terbit_Detail.Diskon,0))) as Total, 
         Broker = CASE dbo.Polis_Asuransi.Broker_Ya
                    WHEN 1 THEN 'Broker'
                  ELSE '-'
                  END,
         dbo.Note_Shipping.Tgl_Terima,	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
         dbo.Note_Shipping.Penerima,	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
         dbo.Note_Shipping.Create_By AS Terima_Create_By,	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
         dbo.Note_Shipping.Create_Date AS Terima_Create_Date	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
 FROM   dbo.Note_Terbit_Master INNER JOIN
        dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
        dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN 
        dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref =dbo.Note_Terbit_Detail.No_Ref INNER JOIN 
        dbo.Sub_Kelompok ON dbo.Note_Terbit_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok LEFT JOIN
        dbo.Note_Shipping ON dbo.Note_Terbit_Master.No_Ref = dbo.Note_Shipping.No_Ref	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
 WHERE (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)>=dbo.func_fixed_date(@TglBuat1) OR @TglBuat1Ya=0) AND 
       (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)<=dbo.func_fixed_date(@TglBuat2) OR @TglBuat2Ya=0) AND 
       (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)>=dbo.func_fixed_date(@TglProduksi1) OR @TglProduksi1Ya=0) AND  -- Prib [2014-02-18] 
       (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)<=dbo.func_fixed_date(@TglProduksi2) OR @TglProduksi2Ya=0) AND  -- Prib [2014-02-18] 
       (dbo.Note_Terbit_Master.Status<>'C') AND 
       (dbo.Note_Terbit_Master.Status<>'G') AND 
       ((dbo.Note_Terbit_Master.Tipe=1 AND @TipeNote=1) OR (dbo.Note_Terbit_Master.Tipe=-1 AND @TipeNote=2) OR (@TipeNote=0)) AND 
       (dbo.Polis_Asuransi.Mata_Uang=RIGHT('00'+CAST(@Valas+1 as varchar),2)) AND 
       (dbo.Perusahaan.GT_OG='GT' OR @GTOG<>1) AND 
       (dbo.Perusahaan.GT_OG='OG' OR @GTOG<>2) AND 
       (dbo.Perusahaan.Afiliasi=@Afiliasi OR isnull(@Afiliasi,0)=0) AND        
       (dbo.Polis_Asuransi.Id_Payor=@IdPayor OR isnull(@IdPayor,'')='') AND
       (dbo.Polis_Asuransi.Aso_Ya=@AsoYa OR isnull(@AsoYa,0)=0) AND
       (dbo.Polis_Asuransi.Sub_Init='I' OR @SubInit<>1) AND 
       (dbo.Polis_Asuransi.Sub_Init='S' OR @SubInit<>2) AND 
       (dbo.Polis_Asuransi.No_Polis=@NoPolis OR isnull(@NoPolis,'')='') AND 
       --(isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=@AdmedikaYa OR isnull(@AdmedikaYa,0)=0) AND
       ((isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=1 AND @AdmedikaYa=1) OR (isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=0 AND @AdmedikaYa=2) OR (@AdmedikaYa=0)) AND  -- Prib [2014-02-19] 
       (CHARINDEX(RIGHT('000'+CAST(Note_Terbit_Master.Jenis as varchar(3)),3),@JenisNote)<>0 OR isnull(@JenisNote,'')='')
       AND (isnull(dbo.Note_Terbit_Master.Id_KelKlaim,'')=@IdKelKlaim OR isnull(@IdKelKlaim,'')='')
 GROUP BY 
       dbo.Note_Terbit_Master.No_Ref, 
       dbo.Note_Terbit_Master.Tgl_buat, 
       --dbo.Note_Terbit_Master.Tgl_Produksi,  -- Prib [2014-02-18]
       dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']',
       dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init,
       dbo.Note_Terbit_Master.No_Note, 
       dbo.Note_Terbit_Master.Jml_Peserta, 
       dbo.Sub_Kelompok.Urutan, 
       dbo.Sub_Kelompok.Nama_Sub, 
       CASE dbo.Polis_Asuransi.Broker_Ya
         WHEN 1 THEN 'Broker'
         ELSE '-'
       END, 
       dbo.Note_Terbit_Master.PPN,
       dbo.Note_Shipping.Tgl_Terima,	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
       dbo.Note_Shipping.Penerima,	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
       dbo.Note_Shipping.Create_By, 	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
       dbo.Note_Shipping.Create_Date	-- add by Pribadi Pamungkas. 2018-04-20. RQ2018040785 Ibu Yis.
 ORDER BY dbo.Note_Terbit_Master.No_Ref, dbo.Sub_Kelompok.Urutan 
END
ELSE IF @RepJenis=1
BEGIN
-- EXEC gProc_RepColumnSave '10760', 1
 EXEC gProc_RepColumnLoad '10760', 1
 SELECT dbo.Note_Terbit_Master.No_Ref, 
        dbo.Note_Terbit_Master.Tgl_buat, 
        --dbo.Note_Terbit_Master.Tgl_Produksi AS Tgl_buat,  -- Prib [2014-02-18] 
        dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']' AS Nama_Perusahaan, 
        dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init AS Kode_Prs,
        dbo.Note_Terbit_Master.No_Note, 
        dbo.Note_Terbit_Master.Jml_Peserta,
        Sum(dbo.Note_Terbit_Master.Tipe*dbo.Note_Terbit_Detail.Jumlah) - isnull(dbo.Note_Terbit_Master.PPN,0) AS Sub_Total,  
        Sum(isnull(dbo.Note_Terbit_Detail.Diskon,0)) AS Diskon,
        Sum(dbo.Note_Terbit_Master.Tipe*(dbo.Note_Terbit_Detail.Jumlah - isnull(dbo.Note_Terbit_Detail.Diskon,0))) AS Total
 FROM   dbo.Note_Terbit_Master INNER JOIN
        dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
        dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN 
        dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref =dbo.Note_Terbit_Detail.No_Ref INNER JOIN 
        dbo.Sub_Kelompok ON dbo.Note_Terbit_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
 WHERE  (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)>=dbo.func_fixed_date(@TglBuat1) OR @TglBuat1Ya=0) AND 
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)<=dbo.func_fixed_date(@TglBuat2) OR @TglBuat2Ya=0) AND 
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)>=dbo.func_fixed_date(@TglProduksi1) OR @TglProduksi1Ya=0) AND  -- Prib [2014-02-18] 
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)<=dbo.func_fixed_date(@TglProduksi2) OR @TglProduksi2Ya=0) AND  -- Prib [2014-02-18] 
        (dbo.Note_Terbit_Master.Status<>'C') AND 
        (dbo.Note_Terbit_Master.Status<>'G') AND 
        ((dbo.Note_Terbit_Master.Tipe=1 AND @TipeNote=1) OR (dbo.Note_Terbit_Master.Tipe=-1 AND @TipeNote=2) OR (@TipeNote=0)) AND 
        (dbo.Polis_Asuransi.Mata_Uang=RIGHT('00'+CAST(@Valas+1 as varchar),2)) AND 
        (dbo.Perusahaan.GT_OG='GT' OR @GTOG<>1) AND 
        (dbo.Perusahaan.GT_OG='OG' OR @GTOG<>2) AND 
        (dbo.Perusahaan.Afiliasi=@Afiliasi OR isnull(@Afiliasi,0)=0) AND        
        (dbo.Polis_Asuransi.Id_Payor=@IdPayor OR isnull(@IdPayor,'')='') AND
        (dbo.Polis_Asuransi.Aso_Ya=@AsoYa OR isnull(@AsoYa,0)=0) AND
        (dbo.Polis_Asuransi.Sub_Init='I' OR @SubInit<>1) AND 
        (dbo.Polis_Asuransi.Sub_Init='S' OR @SubInit<>2) AND 
        (dbo.Polis_Asuransi.No_Polis=@NoPolis OR isnull(@NoPolis,'')='') AND 
        --(isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=@AdmedikaYa OR isnull(@AdmedikaYa,0)=0) AND 
        ((isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=1 AND @AdmedikaYa=1) OR (isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=0 AND @AdmedikaYa=2) OR (@AdmedikaYa=0)) AND  -- Prib [2014-02-19]  
        (CHARINDEX(RIGHT('000'+CAST(Note_Terbit_Master.Jenis as varchar(3)),3),@JenisNote)<>0 OR isnull(@JenisNote,'')='')
        AND (isnull(dbo.Note_Terbit_Master.Id_KelKlaim,'')=@IdKelKlaim OR isnull(@IdKelKlaim,'')='')

 GROUP BY dbo.Note_Terbit_Master.No_Ref, 
        dbo.Note_Terbit_Master.Tgl_buat, 
        --dbo.Note_Terbit_Master.Tgl_Produksi,  -- Prib [2014-02-18]  
        dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']',
        dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init,
        dbo.Note_Terbit_Master.No_Note, 
        dbo.Note_Terbit_Master.Jml_Peserta, 
        dbo.Note_Terbit_Master.PPN
 ORDER BY dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init, 
        dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']', 
        dbo.Note_Terbit_Master.No_Ref
END
ELSE IF @RepJenis=2
BEGIN
-- EXEC gProc_RepColumnSave '10760', 2
   EXEC gProc_RepColumnLoad '10760', 2

 SELECT dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']' as Nama_Perusahaan, 
        dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init  as Kode_Prs,
        dbo.Sub_Kelompok.Urutan, 
        dbo.Sub_Kelompok.Nama_Sub,
        Sum(dbo.Note_Terbit_Master.Tipe*dbo.Note_Terbit_Detail.Jumlah) - isnull(dbo.Note_Terbit_Master.PPN,0) as Sub_Total,  
        Sum(dbo.Note_Terbit_Master.Tipe*isnull(dbo.Note_Terbit_Detail.Diskon,0)) as Diskon,
        Sum(dbo.Note_Terbit_Master.Tipe*(dbo.Note_Terbit_Detail.Jumlah - isnull(dbo.Note_Terbit_Detail.Diskon,0))) as Total
 FROM   dbo.Note_Terbit_Master INNER JOIN
        dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
        dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN 
        dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref =dbo.Note_Terbit_Detail.No_Ref INNER JOIN 
        dbo.Sub_Kelompok ON dbo.Note_Terbit_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
 WHERE  (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)>=dbo.func_fixed_date(@TglBuat1) OR @TglBuat1Ya=0) AND 
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)<=dbo.func_fixed_date(@TglBuat2) OR @TglBuat2Ya=0) AND     
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)>=dbo.func_fixed_date(@TglProduksi1) OR @TglProduksi1Ya=0) AND  -- Prib [2014-02-18]  
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)<=dbo.func_fixed_date(@TglProduksi2) OR @TglProduksi2Ya=0) AND  -- Prib [2014-02-18]  
        (dbo.Note_Terbit_Master.Status<>'C') AND 
        (dbo.Note_Terbit_Master.Status<>'G') AND 
        ((dbo.Note_Terbit_Master.Tipe=1 AND @TipeNote=1) OR (dbo.Note_Terbit_Master.Tipe=-1 AND @TipeNote=2) OR (@TipeNote=0)) AND 
        (dbo.Polis_Asuransi.Mata_Uang=RIGHT('00'+CAST(@Valas+1 as varchar),2)) AND 
        (dbo.Perusahaan.GT_OG='GT' OR @GTOG<>1) AND 
        (dbo.Perusahaan.GT_OG='OG' OR @GTOG<>2) AND 
        (dbo.Perusahaan.Afiliasi=@Afiliasi OR isnull(@Afiliasi,0)=0) AND        
        (dbo.Polis_Asuransi.Id_Payor=@IdPayor OR isnull(@IdPayor,'')='') AND
        (dbo.Polis_Asuransi.Aso_Ya=@AsoYa OR isnull(@AsoYa,0)=0) AND
        (dbo.Polis_Asuransi.Sub_Init='I' OR @SubInit<>1) AND 
        (dbo.Polis_Asuransi.Sub_Init='S' OR @SubInit<>2) AND 
        (dbo.Polis_Asuransi.No_Polis=@NoPolis OR isnull(@NoPolis,'')='') AND 
        --(isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=@AdmedikaYa OR isnull(@AdmedikaYa,0)=0) AND 
        ((isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=1 AND @AdmedikaYa=1) OR (isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=0 AND @AdmedikaYa=2) OR (@AdmedikaYa=0)) AND  -- Prib [2014-02-19]  
        (CHARINDEX(RIGHT('000'+CAST(Note_Terbit_Master.Jenis as varchar(3)),3),@JenisNote)<>0 OR isnull(@JenisNote,'')='')
        AND (isnull(dbo.Note_Terbit_Master.Id_KelKlaim,'')=@IdKelKlaim OR isnull(@IdKelKlaim,'')='')

 GROUP BY dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']', 
        dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init,
        dbo.Sub_Kelompok.Urutan, dbo.Sub_Kelompok.Nama_Sub, 
        dbo.Note_Terbit_Master.PPN
 ORDER BY dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init, 
        dbo.Perusahaan.Nama_Perusahaan+' ['+dbo.Polis_Asuransi.No_Polis+']', 
        dbo.Sub_Kelompok.Urutan
END
ELSE IF @RepJenis=3
BEGIN
 -- EXEC gProc_RepColumnSave '10760', 3
 EXEC gProc_RepColumnLoad '10760', 3
 SELECT dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init as Kode_Prs, 
        dbo.Sub_Kelompok.Urutan, 
        dbo.Sub_Kelompok.Nama_Sub, 
        Sum(dbo.Note_Terbit_Master.Tipe*dbo.Note_Terbit_Detail.Jumlah) - isnull(dbo.Note_Terbit_Master.PPN,0) as Sub_Total,  
        Sum(dbo.Note_Terbit_Master.Tipe*isnull(dbo.Note_Terbit_Detail.Diskon,0)) as Diskon,
        Sum(dbo.Note_Terbit_Master.Tipe*(dbo.Note_Terbit_Detail.Jumlah - isnull(dbo.Note_Terbit_Detail.Diskon,0))) as Total
 FROM   dbo.Note_Terbit_Master INNER JOIN
        dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
        dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN 
        dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref =dbo.Note_Terbit_Detail.No_Ref INNER JOIN 
        dbo.Sub_Kelompok ON dbo.Note_Terbit_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
 WHERE  (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)>=dbo.func_fixed_date(@TglBuat1) OR @TglBuat1Ya=0) AND 
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Buat)<=dbo.func_fixed_date(@TglBuat2) OR @TglBuat2Ya=0) AND 
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)>=dbo.func_fixed_date(@TglProduksi1) OR @TglProduksi1Ya=0) AND  -- Prib [2014-02-18]  
        (dbo.func_fixed_date(dbo.Note_Terbit_Master.Tgl_Produksi)<=dbo.func_fixed_date(@TglProduksi2) OR @TglProduksi2Ya=0) AND  -- Prib [2014-02-18] 
        (dbo.Note_Terbit_Master.Status<>'C') AND 
        (dbo.Note_Terbit_Master.Status<>'G') AND 
        ((dbo.Note_Terbit_Master.Tipe=1 AND @TipeNote=1) OR (dbo.Note_Terbit_Master.Tipe=-1 AND @TipeNote=2) OR (@TipeNote=0)) AND 
        (dbo.Polis_Asuransi.Mata_Uang=RIGHT('00'+CAST(@Valas+1 as varchar),2)) AND 
        (dbo.Perusahaan.GT_OG='GT' OR @GTOG<>1) AND 
        (dbo.Perusahaan.GT_OG='OG' OR @GTOG<>2) AND 
        (dbo.Perusahaan.Afiliasi=@Afiliasi OR isnull(@Afiliasi,0)=0) AND        
        (dbo.Polis_Asuransi.Id_Payor=@IdPayor OR isnull(@IdPayor,'')='') AND
        (dbo.Polis_Asuransi.Aso_Ya=@AsoYa OR isnull(@AsoYa,0)=0) AND
        (dbo.Polis_Asuransi.Sub_Init='I' OR @SubInit<>1) AND 
        (dbo.Polis_Asuransi.Sub_Init='S' OR @SubInit<>2) AND 
        (dbo.Polis_Asuransi.No_Polis=@NoPolis OR isnull(@NoPolis,'')='') AND 
        --(isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=@AdmedikaYa OR isnull(@AdmedikaYa,0)=0) AND 
        ((isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=1 AND @AdmedikaYa=1) OR (isnull(dbo.Polis_Asuransi.AdMedika_Ya,0)=0 AND @AdmedikaYa=2) OR (@AdmedikaYa=0)) AND  -- Prib [2014-02-19]  
        (CHARINDEX(RIGHT('000'+CAST(Note_Terbit_Master.Jenis as varchar(3)),3),@JenisNote)<>0 OR isnull(@JenisNote,'')='')
        AND (isnull(dbo.Note_Terbit_Master.Id_KelKlaim,'')=@IdKelKlaim OR isnull(@IdKelKlaim,'')='')
 GROUP BY dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init, 
        dbo.Sub_Kelompok.Urutan, 
        dbo.Sub_Kelompok.Nama_Sub,
        dbo.Note_Terbit_Master.PPN
 ORDER BY dbo.Perusahaan.GT_OG+'-'+dbo.Polis_Asuransi.Sub_Init, 
        dbo.Sub_Kelompok.Urutan 
END
COMMIT TRAN

