--USE [GrpInsurance]
--GO
--/****** Object:  StoredProcedure [dbo].[proc_Rep10761try]    Script Date: 01/21/2016 09:55:11 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- last modified by Pribadi Pamungkas, 2015-09-15
--ALTER PROCEDURE [dbo].[proc_Rep10761try]
DECLARE
      @TglBuat1 DATETIME = NULL,
      @TglBuat2 DATETIME = NULL,
      @TglLunas1 DATETIME = '2015-01-27',
      @TglLunas2 DATETIME = '2015-02-27',
      @WriteOff BIT = 0,
      @TglEntry1 DATETIME = NULL,
      @TglEntry2 DATETIME = NULL,
      @JenisNote VARCHAR(200) = NULL,
      @CatNote INT = 0,
      @TipeNote INT = 0,
      @NoBV VARCHAR(20) = NULL,--'429P 2015/10/22-0002',
      @NoRek1 VARCHAR(20) = NULL,--'429',
      @NoRek2 VARCHAR(20) = NULL,
      @NoPolis VARCHAR(11) = NULL,
      @Valas INT = 0,
      @SubInit INT = 0,
      @GTOG INT = 0,
      @Afiliasi BIT = 0,
      @Pilihan INT = 0,
      @TglBuat1Ya BIT = 0,
      @TglBuat2Ya BIT = 0,
      @TglLunas1Ya BIT = 1,
      @TglLunas2Ya BIT = 1,
      @TglEntry1Ya BIT = 0,
      @TglEntry2Ya BIT = 0
--AS --Rekap Pelunasan Debit/Credit Note
SET NOCOUNT ON
--EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10761try'
EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10761prib'
--CREATE TABLE [TmpInsurance].[dbo].[Rep10761try] (
CREATE TABLE [TmpInsurance].[dbo].[Rep10761prib] (
        [No_Ref] [varchar](11) NOT NULL,
        [No_BV] [varchar](20) NOT NULL,
        [Tgl_Bayar] [datetime] NOT NULL,
        [Tgl_Input] [datetime] NULL,
        [No_Note] [varchar](25) NULL,
        [Tgl_Terima] [datetime] NULL,
        [No_Proposal] [varchar](9) NULL,
        [Tipe] [Int] NULL,
        [Saldo] [float] NULL,
        [Pelunasan] [float] NULL,
        [Selisih] [float] NULL,
        [Keterangan] [varchar](20) NULL,
        --[No_Rek] [varchar](20) NULL, -- remark by Pribadi Pamungkas, 2015-09-14.
        [Rek_No] [varchar](30) NULL, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
        [Rek_Nama] [varchar](50) NULL, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
        [Nama_Bank] [varchar](50) NULL, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
        [Bank_Cabang] [varchar](60) NULL, -- add by Pribadi Pamungkas, 2015-09-15. Request via JO no 2015.09.08-001 dari Finance
        --CONSTRAINT [PK_Rep10761try] PRIMARY KEY CLUSTERED ([No_Ref], [No_BV], [Tgl_Bayar]) ON [PRIMARY]
        CONSTRAINT [PK_Rep10761prib] PRIMARY KEY CLUSTERED ([No_Ref], [No_BV], [Tgl_Bayar]) ON [PRIMARY]
       )

DECLARE @Tgl_Bayar DATETIME
DECLARE @Tgl_BayarYa BIT
DECLARE @No_Ref VARCHAR(11) 
DECLARE @Tgl_Input DATETIME
DECLARE @No_Note VARCHAR(25) 
DECLARE @Tgl_Terima DATETIME
DECLARE @No_Proposal VARCHAR(11) 
DECLARE @Tipe INT 
DECLARE @Cicilan_Ke INT 
DECLARE @Saldo FLOAT 
DECLARE @Pelunasan FLOAT 
DECLARE @Jumlah FLOAT 
DECLARE @Selisih FLOAT 
DECLARE @No_BV VARCHAR(20)
DECLARE @Keterangan VARCHAR(20) 
--DECLARE @No_Rek VARCHAR(20)  -- remark by Pribadi Pamungkas, 2015-09-14.
DECLARE @Total FLOAT 
DECLARE @Jml_Adjust FLOAT 
DECLARE @Write_Off BIT
DECLARE @Rek_No VARCHAR(30) -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
DECLARE @Rek_Nama VARCHAR(50) -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
DECLARE @Nama_Bank VARCHAR(50) -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
DECLARE @Bank_Cabang VARCHAR(60) -- add by Pribadi Pamungkas, 2015-09-15. Request via JO no 2015.09.08-001 dari Finance

IF COALESCE(@CatNote, 0) = 1 
   SET @JenisNote = COALESCE(@JenisNote, '') + '-001-002-003-004-005-006-009-011-012-019-031-032-033-034-099'
ELSE 
   IF COALESCE(@CatNote, 0) = 2 
      SET @JenisNote = COALESCE(@JenisNote, '') + '-020'

DECLARE Rep10761try_Cursor CURSOR
FOR
SELECT
  dbo.Note_Lunas_Master.No_Ref,
  dbo.Note_Lunas_Master.Create_Date,
  dbo.Note_Lunas_Master.Tgl_Bayar,
  dbo.Note_Terbit_Master.No_Note,
  dbo.Note_Terbit_Master.Tipe,
  dbo.Polis_Asuransi.No_Proposal,
  dbo.Note_Terbit_Master.Total,
  dbo.Note_Terbit_Master.Jml_Adjust,
  dbo.Note_Lunas_Master.No_Bukti,
  --dbo.Note_Lunas_Master.Rek_No, -- remark by Pribadi Pamungkas, 2015-09-14.
  dbo.Note_Lunas_Master.Cicilan_Ke,
  Pelunasan = CASE dbo.Note_Terbit_Master.Write_Off
                WHEN 1 THEN dbo.Note_Terbit_Master.Tipe * SUM(dbo.Note_Terbit_Master.Jml_Adjust)
                ELSE dbo.Note_Terbit_Master.Tipe * SUM(dbo.Note_Lunas_Master.Jumlah)
              END,
  dbo.Note_Terbit_Master.Write_Off,
--  pr.Rek_No2, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
--  pr.Rek_Nama2, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
--  bm.Nama_Pendek, --Nama_Bank  -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
--  pr.Bank_Cabang2 AS Bank_Cabang,  -- add by Pribadi Pamungkas, 2015-09-15. Request via JO no 2015.09.08-001 dari Finance
  
  --dbo.Note_Terbit_Master.Jenis,
  --rek.Kode_Bank,
  rek.Rek_No,
  rek.Rek_Nama,
  --rek.Bank_Nama,
  bm.Nama_Pendek,
  rek.Bank_Cabang
FROM
  dbo.Note_Lunas_Master
  INNER JOIN dbo.Note_Terbit_Master ON dbo.Note_Lunas_Master.No_Ref = dbo.Note_Terbit_Master.No_Ref
  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
  LEFT JOIN dbo.Polis_Rekening AS pr ON dbo.Polis_Asuransi.No_Polis = pr.No_Polis -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance 
  --LEFT JOIN dbo.Bank_Master AS bm ON pr.Kode_Bank2 = bm.Kode_Bank -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
  LEFT JOIN dbo.Jenis_Rekening_Note AS jrn ON dbo.Note_Terbit_Master.Jenis = jrn.Jenis_Note
  CROSS APPLY dbo.func_rek_note_lunas(dbo.Polis_Asuransi.No_Polis,jrn.Jenis_Rek) AS rek
  LEFT JOIN dbo.Bank_Master AS bm ON rek.Kode_Bank = bm.Kode_Bank
  --LEFT JOIN dbo.Bank_Master AS bm ON rek.Bank_Src = bm.Kode_Bank
WHERE
  (dbo.Note_Terbit_Master.Tgl_Buat >= @TglBuat1 OR @TglBuat1Ya = 0)
  AND (dbo.Note_Terbit_Master.Tgl_Buat <= @TglBuat2 OR @TglBuat2Ya = 0)
  AND (dbo.Note_Lunas_Master.Tgl_Bayar >= @TglLunas1 OR @TglLunas1Ya = 0)
  AND (dbo.Note_Lunas_Master.Tgl_Bayar <= @TglLunas2 OR @TglLunas2Ya = 0)
  AND (dbo.Note_Lunas_Master.Create_Date >= @TglEntry1 OR @TglEntry1Ya = 0)
  AND (dbo.Note_Lunas_Master.Create_Date <= @TglEntry2 OR @TglEntry2Ya = 0)
  AND (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.Note_Lunas_Master.No_Bukti LIKE @NoBV OR COALESCE(@NoBV, '') = '')
  AND (dbo.Note_Lunas_Master.No_Rek >= @NoRek1 OR COALESCE(@NoRek1, '') = '')
  AND (dbo.Note_Lunas_Master.No_Rek <= @NoRek1 OR COALESCE(@NoRek2, '') = '')
  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 COALESCE(@Afiliasi, 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 COALESCE(@NoPolis, '') = '')
  AND (Note_Terbit_Master.Jenis <> 100)
  AND (CHARINDEX(RIGHT('000' + CAST(Note_Terbit_Master.Jenis AS VARCHAR(3)), 3), @JenisNote) <> 0 OR COALESCE(@JenisNote, '') = '')
  AND (COALESCE(dbo.Note_Terbit_Master.Write_Off, 0) = 1 OR COALESCE(@WriteOff, 0) = 0)
GROUP BY
  dbo.Note_Lunas_Master.No_Ref,
  dbo.Note_Lunas_Master.Create_Date,
  dbo.Note_Lunas_Master.Tgl_Bayar,
  dbo.Note_Terbit_Master.No_Note,
  dbo.Note_Terbit_Master.Tipe,
  dbo.Polis_Asuransi.No_Proposal,
  dbo.Note_Terbit_Master.Total,
  dbo.Note_Terbit_Master.Jml_Adjust,
  dbo.Note_Lunas_Master.No_Bukti,
  dbo.Note_Lunas_Master.Rek_No,
  dbo.Note_Lunas_Master.Cicilan_Ke,
  dbo.Note_Terbit_Master.Write_Off,
  pr.Rek_No2, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
  pr.Rek_Nama2, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
  bm.Nama_Pendek,  -- Nama_Bank -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
  pr.Bank_Cabang2,  -- add by Pribadi Pamungkas, 2015-09-15. Request via JO no 2015.09.08-001 dari Finance
  
  dbo.Note_Terbit_Master.Jenis,
  --rek.Kode_Bank,
  rek.Rek_No,
  rek.Rek_Nama,
  --rek.Bank_Nama,
  bm.Nama_Pendek,
  rek.Bank_Cabang

OPEN Rep10761try_Cursor
--FETCH NEXT FROM Rep10761try_Cursor INTO @No_Ref, @Tgl_Input, @Tgl_Bayar, @No_Note, @Tipe, @No_Proposal, @Total, @Jml_Adjust, @No_BV, @No_Rek, @Cicilan_Ke, @Pelunasan, @Write_Off  -- remark by Pribadi Pamungkas, 2015-09-14.
FETCH NEXT FROM Rep10761try_Cursor INTO @No_Ref, @Tgl_Input, @Tgl_Bayar, @No_Note, @Tipe, @No_Proposal, @Total, @Jml_Adjust, @No_BV, @Cicilan_Ke, @Pelunasan, @Write_Off, @Rek_No, @Rek_Nama, @Nama_Bank, @Bank_Cabang -- modified by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance

WHILE @@FETCH_STATUS = 0 
      BEGIN 
            SET @Jumlah = 0
            SET @Selisih = 0
            IF EXISTS ( SELECT
                          *
                        FROM
                          Note_Lunas_Master
                        WHERE
                          (No_Ref = @No_Ref)
                          AND (dbo.Note_Lunas_Master.Tgl_Bayar < @TglLunas1
                          OR @TglLunas1Ya = 0)
                          AND (dbo.Note_Lunas_Master.Tgl_Bayar < @TglLunas2
                          OR @TglLunas2Ya = 0) ) 
               SELECT
                @Jumlah = SUM(Jumlah)
               FROM
                Note_Lunas_Master
               WHERE
                (No_Ref = @No_Ref)
                AND (dbo.Note_Lunas_Master.Tgl_Bayar < @TglLunas1 OR @TglLunas1Ya = 0)
                AND (dbo.Note_Lunas_Master.Tgl_Bayar < @TglLunas2 OR @TglLunas2Ya = 0)  
     
            IF EXISTS ( SELECT
                          *
                        FROM
                          Note_Shipping
                        WHERE
                          No_Ref = @No_Ref ) 
               SELECT
                @Tgl_Terima = MAX(Tgl_Terima)
               FROM
                Note_Shipping
               WHERE
                No_Ref = @No_Ref

            IF COALESCE(@Write_Off, 0) = 1 
               SET @Saldo = @Total - @Tipe * (COALESCE(@Jumlah, 0))
            ELSE 
               SET @Saldo = @Total - @Tipe * (COALESCE(@Jumlah, 0) - COALESCE(@Jml_Adjust, 0))

            SET @Selisih = COALESCE(@Saldo, 0) - COALESCE(@Pelunasan, 0)
--     IF @Selisih<=10000 SET @Selisih=0
            IF @Selisih = 0 
               --SET @Keterangan = 'Lunas'  -- remark by Pribadi Pamungkas, 2015-09-15.
               SET @Keterangan = 'L'  -- modified by Pribadi Pamungkas, 2015-09-15. Request via telpon, penambahan JO no 2015.09.08-001 dari Finance
            ELSE 
               --SET @Keterangan = 'Cicilan ke ' + CAST(@Cicilan_Ke AS VARCHAR)  -- remark by Pribadi Pamungkas, 2015-09-15.
               SET @Keterangan = 'C-' + CAST(@Cicilan_Ke AS VARCHAR)  -- modified by Pribadi Pamungkas, 2015-09-15. Request via telpon, penambahan JO no 2015.09.08-001 dari Finance

    -- start: cek rekening cabang
    DECLARE 
      @cabang_note VARCHAR(14),
      @no_polis VARCHAR(11),
      @jenis_note SMALLINT,
      @new_rek_no VARCHAR(30) = '',
      @new_rek_nama VARCHAR(50) = '',
      @new_rek_bank VARCHAR(15) = '',
      @new_rek_cabang VARCHAR(60) = ''
    SELECT @cabang_note=COALESCE(ntm.Cabang,''), @no_polis=pa.No_Polis, @jenis_note=ntm.Jenis FROM dbo.Note_Terbit_Master AS ntm INNER JOIN dbo.Polis_Asuransi AS pa ON ntm.No_Proposal = pa.No_Proposal WHERE ntm.No_Ref = @No_Ref -- ada kolom No_Polis sih, tapi lebih aman join ke Polis_Asuransi
    
    -- start: get rekening polis
    SELECT
		  @new_rek_no=rek.Rek_No,
		  @new_rek_nama=rek.Rek_Nama,
		  @new_rek_bank=bm.Nama_Pendek,
		  @new_rek_cabang=rek.Bank_Cabang
  	FROM
  	  dbo.Jenis_Rekening_Note AS jrn CROSS APPLY 
  		dbo.func_rek_note_lunas(@no_polis,jrn.Jenis_Rek) AS rek LEFT JOIN
      dbo.Bank_Master AS bm ON rek.Kode_Bank = bm.Kode_Bank
    WHERE
      jrn.Jenis_Note = @jenis_note
    -- end: get rekening polis
          
    IF (@cabang_note <> '') AND (COALESCE(@new_rek_no,'') <> '') -- jika di Note Terbit Master didefinisikan cabang, dan ada rekening cabang, maka
      BEGIN
        -- start: replace dgn rekening cabang
        SELECT 
          @new_rek_no=rekcab.Rek_No,
          @new_rek_nama=rekcab.Rek_Nama,
          @new_rek_bank=bm.Nama_Pendek,
          @new_rek_cabang=rekcab.Bank_Cabang
        FROM 
          dbo.Jenis_Rekening_Note AS jrn CROSS APPLY 
          dbo.func_rek_cabang_note_lunas(@no_polis,jrn.Jenis_Rek,@cabang_note) AS rekcab LEFT JOIN
          dbo.Bank_Master AS bm ON rekcab.Kode_Bank = bm.Kode_Bank
        WHERE
          jrn.Jenis_Note = @jenis_note
        -- end: replace dgn rekening cabang
      END

            --INSERT  INTO TmpInsurance.dbo.Rep10761try
            INSERT  INTO TmpInsurance.dbo.Rep10761prib
                    (No_Ref,
                     Tgl_Bayar,
                     Tgl_Input,
                     No_Note,
                     Tgl_Terima,
                     No_Proposal,
                     Tipe,
                     Saldo,
                     Pelunasan,
                     Selisih,
                     No_BV,
                     Keterangan,
                     --No_Rek)  -- remark by Pribadi Pamungkas, 2015-09-14.
                     Rek_No, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
                     Rek_Nama, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
                     Nama_Bank, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
                     Bank_Cabang) -- add by Pribadi Pamungkas, 2015-09-15. Request via JO no 2015.09.08-001 dari Finance
            VALUES
                    (@No_Ref,
                     @Tgl_Bayar,
                     @Tgl_Input,
                     @No_Note,
                     @Tgl_Terima,
                     @No_Proposal,
                     @Tipe,
                     @Saldo,
                     @Pelunasan,
                     @Selisih,
                     COALESCE(@No_BV, 'BV:'),
                     @Keterangan,
                       --@No_Rek)  -- remark by Pribadi Pamungkas, 2015-09-14.
                     @new_rek_no,--  @Rek_No,  -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
                     @new_rek_nama,--  @Rek_Nama,  -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
                     @new_rek_bank,--  @Nama_Bank, -- add by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
                     @new_rek_cabang)--  @Bank_Cabang) -- add by Pribadi Pamungkas, 2015-09-15. Request via JO no 2015.09.08-001 dari Finance

            --FETCH NEXT FROM Rep10761try_Cursor INTO @No_Ref, @Tgl_Input, @Tgl_Bayar, @No_Note, @Tipe, @No_Proposal, @Total, @Jml_Adjust, @No_BV, @No_Rek, @Cicilan_Ke, @Pelunasan, @Write_Off   -- remark by Pribadi Pamungkas, 2015-09-14.
            FETCH NEXT FROM Rep10761try_Cursor INTO @No_Ref, @Tgl_Input, @Tgl_Bayar, @No_Note, @Tipe, @No_Proposal, @Total, @Jml_Adjust, @No_BV, @Cicilan_Ke, @Pelunasan, @Write_Off, @Rek_No, @Rek_Nama, @Nama_Bank, @Bank_Cabang -- modified by Pribadi Pamungkas, 2015-09-14. Request via JO no 2015.09.08-001 dari Finance
      END
CLOSE Rep10761try_Cursor
DEALLOCATE Rep10761try_Cursor

IF @Pilihan = 0 
   BEGIN
         --EXEC gproc_RepColumnLoad '10761try', 0
         SELECT TOP 100 PERCENT
          prib.Tgl_Bayar,
          dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' AS Nama_Perusahaan,
          dbo.Polis_Asuransi.Broker_Id,
          prib.No_Note,
          prib.Tgl_Input,
          prib.Tgl_Terima,
          dbo.Perusahaan.GT_OG + dbo.Polis_Asuransi.Sub_Init AS Kode_Prs, -- Kode_Prs
          prib.Saldo,
          prib.Pelunasan,
          prib.Selisih,
          prib.No_BV,
          prib.Keterangan,
          --prib.No_Rek,
          prib.No_Ref,
          prib.Tipe,
          prib.Rek_No,
          prib.Rek_Nama,
          prib.Nama_Bank,
          prib.Bank_Cabang,
          -------
          ntm.Cabang,
          ntm.Jenis,
          dbo.Polis_Asuransi.No_Polis
         FROM
          --TmpInsurance.dbo.Rep10761try
          TmpInsurance.dbo.Rep10761prib AS prib
          INNER JOIN dbo.Polis_Asuransi ON prib.No_Proposal = dbo.Polis_Asuransi.No_Proposal
          INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
          
          -- test aja
          LEFT JOIN dbo.Note_Terbit_Master AS ntm ON prib.No_Note = ntm.No_Note
          WHERE ntm.Cabang<>''
          
         ORDER BY
          Tgl_Bayar,
          dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']',
          No_Note
   END
ELSE 
   IF @Pilihan = 1 
      BEGIN
            --EXEC gproc_RepColumnLoad '10761try', 1

            SELECT TOP 100 PERCENT
              COALESCE(prib.Tgl_Bayar, 0),
              dbo.Perusahaan.GT_OG + dbo.Polis_Asuransi.Sub_Init AS Kode_Prs,
              SUM(COALESCE(prib.Saldo, 0)) AS Saldo,
              SUM(COALESCE(prib.Pelunasan, 0)) AS Pelunasan,
              SUM(COALESCE(prib.Selisih, 0)) AS Selisih,
              SUM(COALESCE(prib.Saldo, 0) - COALESCE(prib.Pelunasan, 0)) AS Saldo_Akhir
            FROM
              --TmpInsurance.dbo.Rep10761try
              TmpInsurance.dbo.Rep10761prib AS prib
              INNER JOIN dbo.Polis_Asuransi ON prib.No_Proposal = dbo.Polis_Asuransi.No_Proposal
              INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
            GROUP BY
              prib.Tgl_Bayar,
              dbo.Perusahaan.GT_OG + dbo.Polis_Asuransi.Sub_Init
            ORDER BY
              prib.Tgl_Bayar
      END

--EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10761try'

