USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10468_test]    Script Date: 07/07/2015 10:31:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


DECLARE
      @IdPayor VARCHAR(2) = NULL,
      @NoBV VARCHAR(20) = '003P 2015/7/13-0002',
      @RekAsal VARCHAR(5) = '003',
      @BankTujuan VARCHAR(5) = NULL,
      @Kota INT = NULL,
      @TglBayar1 DATETIME = NULL,
      @TglBayar2 DATETIME = NULL,
      @TglLunas1 DATETIME = '2015-07-13',
      @TglLunas2 DATETIME = '2015-07-13',
      @RekPolisYa BIT = 1,
      @RekProviderYa BIT = 1,
      @RekPesertaYa BIT = 1,
      @TglBayar1Ya BIT = 0,
      @TglBayar2Ya BIT = 0,
      @TglLunas1Ya BIT = 1,
      @TglLunas2Ya BIT = 1

DECLARE @Rep10468 TABLE ( 
        [Urut] [int] IDENTITY(1, 1) NOT NULL,
        [Kelompok] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Rek_Nama] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Bank_Nama] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Bank_Cabang] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Rek_No] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Sub_Total] [float] NULL,
        [Jml_Transfer] [float] NULL,
        [Biaya_Transfer] [float] NULL,
        [Total] AS (ISNULL([Jml_Transfer], 0) + ISNULL([Biaya_Transfer], 0)),
        [Keterangan1] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Keterangan2] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL--,
        --CONSTRAINT [PK_Rep10468] PRIMARY KEY CLUSTERED ([Urut]) ON [PRIMARY]
       )
--ON     [PRIMARY]

DECLARE @Status_Kota VARCHAR(1)
  --definisi lokasi kota
IF @Kota = 0 
   SET @Status_Kota = ''
ELSE 
   IF @Kota = 1 
      SET @Status_Kota = 'D'
   ELSE 
      IF @Kota = 2 
         SET @Status_Kota = 'L'

-- LUNAS KLAIM
IF ISNULL(@RekPolisYa, 0) = 1 
   BEGIN
         IF EXISTS ( SELECT
                      No_Ref
                     FROM
                      dbo.Note_Lunas_Master
                     WHERE
                      (Tgl_Bayar >= @TglLunas1
                      OR @TglLunas1Ya = 0)
                      AND (Tgl_Bayar <= @TglLunas2
                      OR @TglLunas2Ya = 0)
                      AND (dbo.Note_Lunas_Master.No_Bukti = @NoBV
                      OR ISNULL(@NoBV, '') = '') ) 
            BEGIN
                  IF ISNULL(@RekPolisYa, 0) = 1 
                     BEGIN
    -- PEMBAYARAN CN KLAIM KANTOR PUSAT    
                           INSERT INTO @Rep10468
                                  (Kelompok,
                                   Rek_Nama,
                                   Bank_Nama,
                                   Bank_Cabang,
                                   Rek_No,
                                   Sub_Total,
                                   Jml_Transfer,
                                   Biaya_Transfer,
                                   Keterangan1,
                                   Keterangan2)
                                  SELECT
                                    dbo.func_GetKelBankSumber(T1.No_Rek, dbo.Polis_Rekening.Kode_Bank) AS Kelompok,
                                    dbo.Polis_Rekening.Rek_Nama,
                                    dbo.Bank_Master.Nama_Bank,
                                    dbo.Polis_Rekening.Bank_Cabang,
                                    dbo.Polis_Rekening.Rek_No,
                                    T1.Jml_Lunas AS Sub_Total,
                                    Jml_Transfer = CASE WHEN ISNULL(dbo.Polis_JenisRekening.CNKlaim_Biaya, 0) = 1 THEN T1.Jml_Lunas - dbo.func_GetBiayaTransfer(T1.No_Rek, dbo.Polis_Rekening.Kode_Bank, dbo.Polis_Rekening.Bank_Trans)
                                                        ELSE T1.Jml_Lunas
                                                   END,
                                    dbo.func_GetBiayaTransfer(T1.No_Rek, dbo.Polis_Rekening.Kode_Bank, dbo.Polis_Rekening.Bank_Trans) AS Biaya_Transfer,
                                    dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' AS Ket1,
                                    'Byr.Klaim' AS Ket2
                                  FROM
                                    (SELECT
                                      No_Ref,
                                      No_Rek,
                                      SUM(dbo.Note_Lunas_Master.Jumlah) AS Jml_Lunas
                                     FROM
                                      dbo.Note_Lunas_Master
                                     WHERE
                                      (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.No_Bukti = @NoBV OR ISNULL(@NoBV, '') = '')
                                      AND (dbo.Note_Lunas_Master.No_Rek = @RekAsal OR ISNULL(@RekAsal, '') = '')
                                     GROUP BY
                                      No_Ref,
                                      No_Rek) AS T1
                                    INNER JOIN dbo.Note_Terbit_Master ON dbo.Note_Terbit_Master.No_Ref = T1.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
                                    INNER JOIN dbo.Polis_Rekening ON dbo.Polis_Asuransi.No_Polis = dbo.Polis_Rekening.No_Polis
                                    INNER JOIN dbo.Bank_Master ON dbo.Polis_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
                                    INNER JOIN dbo.Polis_JenisRekening ON dbo.Polis_JenisRekening.No_Polis = dbo.Polis_asuransi.No_Polis
                                  WHERE
                                    (dbo.Polis_Rekening.Kode_Bank = @BankTujuan OR ISNULL(@BankTujuan, '') = '')
                                    AND (dbo.Polis_Rekening.Bank_Trans = @Status_Kota OR ISNULL(@Status_Kota, '') = '')
                                    AND (dbo.Note_Terbit_Master.Jenis <= 19 OR dbo.Note_Terbit_Master.Jenis IN (21, 22))
                                    AND (dbo.Polis_JenisRekening.CNKlaim_CrBayar = 1)
                                    AND (dbo.Polis_JenisRekening.CNKlaim_Jenis = 1)
                                    AND (dbo.Polis_Asuransi.Id_Payor = @IdPayor OR ISNULL(@IdPayor, '') = '')

    -- PEMBAYARAN CN KLAIM CABANG/UNIT
                           INSERT INTO @Rep10468
                                  (Kelompok,
                                   Rek_Nama,
                                   Bank_Nama,
                                   Bank_Cabang,
                                   Rek_No,
                                   Sub_Total,
                                   Jml_Transfer,
                                   Biaya_Transfer,
                                   Keterangan1,
                                   Keterangan2)
                                  SELECT
                                    dbo.func_GetKelBankSumber(T1.No_Rek, dbo.Cabang_Rekening.Kode_Bank) AS Kelompok,
                                    dbo.Cabang_Rekening.Rek_Nama,
                                    dbo.Bank_Master.Nama_Bank,
                                    dbo.Cabang_Rekening.Bank_Cabang,
                                    dbo.Cabang_Rekening.Rek_No,
                                    T1.Jml_Lunas AS Sub_Total,
                                    Jml_Transfer = CASE WHEN ISNULL(dbo.Polis_JenisRekening.CNKlaim_Biaya, 0) = 1 THEN T1.Jml_Lunas - dbo.func_GetBiayaTransfer(T1.No_Rek, dbo.Cabang_Rekening.Kode_Bank, dbo.Cabang_Rekening.Bank_Lokasi)
                                                        ELSE T1.Jml_Lunas
                                                   END,
                                    dbo.func_GetBiayaTransfer(T1.No_Rek, dbo.Cabang_Rekening.Kode_Bank, dbo.Cabang_Rekening.Bank_Lokasi) AS Biaya_Transfer,
                                    dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' AS Ket1,
                                    'Byr.Klaim' AS Ket2
                                  FROM
                                    (SELECT
                                      No_Ref,
                                      No_Rek,
                                      SUM(dbo.Note_Lunas_Master.Jumlah) AS Jml_Lunas
                                     FROM
                                      dbo.Note_Lunas_Master
                                     WHERE
                                      (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.No_Bukti = @NoBV OR ISNULL(@NoBV, '') = '')
                                      AND (dbo.Note_Lunas_Master.No_Rek = @RekAsal OR ISNULL(@RekAsal, '') = '')
                                     GROUP BY
                                      No_Ref,
                                      No_Rek) AS T1
                                    INNER JOIN dbo.Note_Terbit_Master ON dbo.Note_Terbit_Master.No_Ref = T1.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
                                    INNER JOIN dbo.Cabang_Rekening ON dbo.Polis_Asuransi.No_Polis = LEFT(dbo.Cabang_Rekening.Kode_Cabang, 11)
                                                                      AND SUBSTRING(dbo.Cabang_Rekening.Kode_Cabang, 13, 4) = dbo.Note_Terbit_Master.Cabang
                                    INNER JOIN dbo.Bank_Master ON dbo.Cabang_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
                                    INNER JOIN dbo.Polis_JenisRekening ON dbo.Polis_JenisRekening.No_Polis = dbo.Polis_asuransi.No_Polis
                                  WHERE
                                    (dbo.Cabang_Rekening.Kode_Bank = @BankTujuan OR ISNULL(@BankTujuan, '') = '')
                                    AND (dbo.Cabang_Rekening.Bank_Lokasi = @Status_Kota OR ISNULL(@Status_Kota, '') = '')
                                    AND (dbo.Note_Terbit_Master.Jenis <= 19 OR dbo.Note_Terbit_Master.Jenis IN (21, 22))
                                    AND (dbo.Polis_JenisRekening.CNKlaim_CrBayar = 1)
                                    AND (dbo.Polis_JenisRekening.CNKlaim_Jenis = 2)
                                    AND (dbo.Polis_Asuransi.Id_Payor = @IdPayor OR ISNULL(@IdPayor, '') = '')
                     END

                  IF ISNULL(@RekPesertaYa, 0) = 1 
                     BEGIN

-- PEMBAYARAN CN KLAIM PESERTA
                           INSERT INTO @Rep10468
                                  (Kelompok,
                                   Rek_Nama,
                                   Bank_Nama,
                                   Bank_Cabang,
                                   Rek_No,
                                   Sub_Total,
                                   Jml_Transfer,
                                   Biaya_Transfer,
                                   Keterangan1,
                                   Keterangan2)
                                  SELECT
                                    dbo.func_GetKelBankSumber(T1.No_Rek, dbo.Peserta_Rekening.Kode_Bank) AS Kelompok,
                                    dbo.Peserta_Rekening.Rek_Nama,
                                    dbo.Bank_Master.Nama_Bank,
                                    dbo.Peserta_Rekening.Bank_Cabang,
                                    dbo.Peserta_Rekening.Rek_No,
                                    T1.Jml_Lunas AS Sub_Total,
                                    Jml_Transfer = CASE WHEN ISNULL(dbo.Polis_JenisRekening.CNKlaim_Biaya, 0) = 1 THEN T1.Jml_Lunas - dbo.func_BiayaTransfer(T1.No_Rek, dbo.Peserta_Rekening.Kode_Bank, dbo.Peserta_Rekening.Bank_Lokasi)
                                                        ELSE T1.Jml_Lunas
                                                   END,
                                    dbo.func_BiayaTransfer(T1.No_Rek, dbo.Peserta_Rekening.Kode_Bank, dbo.Peserta_Rekening.Bank_Lokasi) AS Biaya_Transfer,
                                    dbo.Peserta.Nama AS Ket1,
                                    'Byr.Klaim' AS Ket2
                                  FROM
                                    (SELECT
                                      No_Ref,
                                      No_Rek,
                                      SUM(dbo.Note_Lunas_Master.Jumlah) AS Jml_Lunas
                                     FROM
                                      dbo.Note_Lunas_Master
                                     WHERE
                                      (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.No_Bukti = @NoBV OR ISNULL(@NoBV, '') = '')
                                      AND (dbo.Note_Lunas_Master.No_Rek = @RekAsal OR ISNULL(@RekAsal, '') = '')
                                     GROUP BY
                                      No_Ref,
                                      No_Rek) AS T1
                                    INNER JOIN dbo.Note_Terbit_Master ON dbo.Note_Terbit_Master.No_Ref = T1.No_Ref
                                    INNER JOIN dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal
                                    INNER JOIN dbo.Klaim_Ref_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Klaim_Ref_Detail.No_Ref
                                    INNER JOIN dbo.Peserta ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
                                                              AND dbo.Klaim_Ref_Detail.No_Santunan = dbo.Peserta.No_Peserta
                                    INNER JOIN dbo.Peserta_Rekening ON LEFT(dbo.Peserta.No_Peserta, 11) + '0' = dbo.Peserta_Rekening.No_Peserta
                                                                       AND dbo.Polis_Asuransi.No_Polis = dbo.Peserta_Rekening.No_Polis
                                    INNER JOIN dbo.Bank_Master ON dbo.Peserta_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
                                    INNER JOIN dbo.Polis_JenisRekening ON dbo.Polis_JenisRekening.No_Polis = dbo.Polis_asuransi.No_Polis
                                  WHERE
                                    (dbo.Peserta_Rekening.Kode_Bank = @BankTujuan OR ISNULL(@BankTujuan, '') = '')
                                    AND (dbo.Peserta_Rekening.Bank_Lokasi = @Status_Kota OR ISNULL(@Status_Kota, '') = '')
                                    AND (dbo.Note_Terbit_Master.Jenis IN (21, 22))
                                    AND (dbo.Polis_JenisRekening.CNKlaim_CrBayar = 1)
                                    AND (dbo.Polis_JenisRekening.CNKlaim_Jenis = 3)
                                    AND (dbo.Polis_Asuransi.Id_Payor = @IdPayor OR ISNULL(@IdPayor, '') = '')
                     END
--PEMBAYARAN REFUND PREMI
                  INSERT  INTO @Rep10468
                          (Kelompok,
                           Rek_Nama,
                           Bank_Nama,
                           Bank_Cabang,
                           Rek_No,
                           Sub_Total,
                           Jml_Transfer,
                           Biaya_Transfer,
                           Keterangan1,
                           Keterangan2)
                          SELECT TOP 100 PERCENT
                            dbo.func_GetKelBankSumber(T1.No_Rek, dbo.Polis_Rekening.Kode_Bank) AS Kelompok,
                            dbo.Polis_Rekening.Rek_Nama2,
                            dbo.Bank_Master.Nama_Bank,
                            dbo.Polis_Rekening.Bank_Cabang2,
                            dbo.Polis_Rekening.Rek_No2,
                            T1.Jml_Lunas AS Sub_Total,
                            Jml_Transfer = CASE WHEN ISNULL(dbo.Polis_JenisRekening.CNPremi_Biaya, 0) = 1 THEN T1.Jml_Lunas - dbo.func_GetBiayaTransfer(T1.No_Rek, dbo.Polis_Rekening.Kode_Bank2, dbo.Polis_Rekening.Bank_Trans2)
                                                ELSE T1.Jml_Lunas
                                           END,
                            dbo.func_BiayaTransfer(T1.No_Rek, dbo.Polis_Rekening.Kode_Bank2, dbo.Polis_Rekening.Bank_Trans2) AS Biaya_Transfer,
                            dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' AS Ket1,
                            'Ref.Premi' AS Ket2
                          FROM
                            (SELECT
                              No_Ref,
                              No_Rek,
                              SUM(dbo.Note_Lunas_Master.Jumlah) AS Jml_Lunas
                             FROM
                              dbo.Note_Lunas_Master
                             WHERE
                              (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.No_Bukti = @NoBV OR ISNULL(@NoBV, '') = '')
                              AND (dbo.Note_Lunas_Master.No_Rek = @RekAsal OR ISNULL(@RekAsal, '') = '')
                             GROUP BY
                              No_Ref,
                              No_Rek) AS T1
                            INNER JOIN dbo.Note_Terbit_Master ON dbo.Note_Terbit_Master.No_Ref = T1.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
                            INNER JOIN dbo.Polis_Rekening ON dbo.Note_Terbit_Master.No_Polis = dbo.Polis_Rekening.No_Polis
                            INNER JOIN dbo.Bank_Master ON dbo.Polis_Rekening.Kode_Bank2 = dbo.Bank_Master.Kode_Bank
                            INNER JOIN dbo.Polis_JenisRekening ON dbo.Polis_JenisRekening.No_Polis = dbo.Polis_asuransi.No_Polis
                          WHERE
                            (dbo.Polis_Rekening.Kode_Bank2 = @BankTujuan OR ISNULL(@BankTujuan, '') = '')
                            AND (dbo.Polis_Rekening.Bank_Trans2 = @Status_Kota OR ISNULL(@Status_Kota, '') = '')
                            AND (dbo.Note_Terbit_Master.Jenis IN (11, 12))
                            AND (dbo.Polis_Asuransi.Id_Payor = @IdPayor OR ISNULL(@IdPayor, '') = '')
            END
   END
IF ISNULL(@RekProviderYa, 0) = 1 
   BEGIN
--PEMBAYARAN PROVIDER 
         DECLARE @Diskon_Rekap FLOAT
         SET @Diskon_Rekap = 0
         SELECT
          @Diskon_Rekap = SUM(ISNULL(dbo.Note_Terbit_Provider_Header.Diskon, 0))
         FROM
          dbo.Note_Lunas_Provider_Header
          INNER JOIN dbo.Note_Terbit_Provider_Header ON dbo.Note_Lunas_Provider_Header.No_Ref_Provider = dbo.Note_Terbit_Provider_Header.No_Ref_Provider
          INNER JOIN dbo.Provider_Rekening ON dbo.Note_Terbit_Provider_Header.Kode_Provider = dbo.Provider_Rekening.IDI
          INNER JOIN dbo.Provider ON dbo.Note_Terbit_Provider_Header.Kode_Provider = dbo.Provider.IDI
          INNER JOIN dbo.Bank_Master ON dbo.Provider_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
         WHERE
          (dbo.Note_Lunas_Provider_Header.Tgl_Bayar >= @TglLunas1 OR @TglLunas1Ya = 0)
          AND (dbo.Note_Lunas_Provider_Header.Tgl_Bayar <= @TglLunas2 OR @TglLunas2Ya = 0)
          AND (dbo.Note_Terbit_Provider_Header.Tgl_Bayar >= @TglBayar1 OR @TglBayar1Ya = 0)
          AND (dbo.Note_Terbit_Provider_Header.Tgl_Bayar <= @TglBayar2 OR @TglBayar2Ya = 0)
          AND (dbo.Note_Lunas_Provider_Header.Bank_Asal = @RekAsal OR ISNULL(@RekAsal, '') = '')
          AND (dbo.Provider_Rekening.Kode_Bank = @BankTujuan OR ISNULL(@BankTujuan, '') = '')
          AND (dbo.Provider_Rekening.Bank_Lokasi = @Status_Kota OR ISNULL(@Status_Kota, '') = '')
  
         INSERT INTO @Rep10468
                (Kelompok,
                 Rek_Nama,
                 Bank_Nama,
                 Bank_Cabang,
                 Rek_No,
                 Sub_Total,
                 Jml_Transfer,
                 Biaya_Transfer,
                 Keterangan1,
                 Keterangan2)
                SELECT
                  Kelompok = CASE WHEN (dbo.func_GetRefAso(dbo.Note_Terbit_Provider_Detail.No_Ref) = 0) THEN [dbo].func_GetKelBankSumber(dbo.Note_Lunas_Provider_Header.Bank_Asal, dbo.Provider_Rekening.Kode_Bank)
                                  ELSE [dbo].func_GetKelBankSumber([dbo].func_GetBankSumberAso(dbo.Note_Terbit_Provider_Detail.No_Proposal), dbo.Provider_Rekening.Kode_Bank)
                             END,
                  dbo.Provider_Rekening.Rek_Nama,
                  dbo.Bank_Master.Nama_Bank,
                  dbo.Provider_Rekening.Bank_Cabang,
                  dbo.Provider_Rekening.Rek_No,
                  SUM(dbo.Note_Terbit_Provider_Detail.Total) - ISNULL(dbo.Note_Terbit_Provider_Header.Diskon, 0) AS Sub_Total,
                  SUM(dbo.Note_Terbit_Provider_Detail.Total) - ISNULL(dbo.Note_Terbit_Provider_Header.Diskon, 0) - ISNULL(dbo.Note_Terbit_Provider_Header.Jml_Diskon, 0) - ISNULL(dbo.Note_Terbit_Provider_Header.Jml_Pot, 0) AS Jml_Transfer,
                  dbo.func_GetBiayaTransfer(dbo.Note_Lunas_Provider_Header.Bank_Asal, dbo.Provider_Rekening.Kode_Bank, dbo.Provider_Rekening.Bank_Lokasi) AS Biaya_Transfer,
                  ISNULL(Payor_Header.Nama_Payor, '') + ' ' + MAX(ISNULL(dbo.Note_Terbit_Provider_Header.No_Bukti, '')) AS Ket1,
                  dbo.Provider.Nama AS Ket2
                FROM
                  dbo.Note_Lunas_Provider_Header
                  INNER JOIN dbo.Note_Terbit_Provider_Header ON dbo.Note_Lunas_Provider_Header.No_Ref_Provider = dbo.Note_Terbit_Provider_Header.No_Ref_Provider
                  INNER JOIN dbo.Provider_Rekening ON dbo.Note_Terbit_Provider_Header.Kode_Provider = dbo.Provider_Rekening.IDI
                  INNER JOIN dbo.Provider ON dbo.Note_Terbit_Provider_Header.Kode_Provider = dbo.Provider.IDI
                  INNER JOIN dbo.Bank_Master ON dbo.Provider_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
                  INNER JOIN dbo.Note_Terbit_Provider_Detail ON dbo.Note_Lunas_Provider_Header.No_Ref_Provider = dbo.Note_Terbit_Provider_Detail.No_Ref_Provider
                  INNER JOIN dbo.Polis_Asuransi ON dbo.Note_Terbit_Provider_Detail.No_Proposal = dbo.Polis_Asuransi.No_Proposal
                  INNER JOIN Payor_Header ON dbo.Polis_Asuransi.Id_Payor = Payor_Header.Id_Payor
                WHERE
                  (dbo.Note_Lunas_Provider_Header.Tgl_Bayar >= @TglLunas1 OR @TglLunas1Ya = 0)
                  AND (dbo.Note_Lunas_Provider_Header.Tgl_Bayar <= @TglLunas2 OR @TglLunas2Ya = 0)
                  AND (dbo.Note_Terbit_Provider_Header.Tgl_Bayar >= @TglBayar1 OR @TglBayar1Ya = 0)
                  AND (dbo.Note_Terbit_Provider_Header.Tgl_Bayar <= @TglBayar2 OR @TglBayar2Ya = 0)
                  AND (dbo.Note_Lunas_Provider_Header.Bank_Asal = @RekAsal OR ISNULL(@RekAsal, '') = '')
                  AND (dbo.Provider_Rekening.Kode_Bank = @BankTujuan OR ISNULL(@BankTujuan, '') = '')
                  AND (dbo.Provider_Rekening.Bank_Lokasi = @Status_Kota OR ISNULL(@Status_Kota, '') = '')
                  AND (dbo.Polis_Asuransi.Id_Payor = @IdPayor OR ISNULL(@IdPayor, '') = '')
                GROUP BY
                  dbo.Note_Terbit_Provider_Header.Diskon,
                  Jml_Pot,
                  ISNULL(dbo.Note_Terbit_Provider_Header.Jml_Diskon, 0),
                  CASE WHEN (dbo.func_GetRefAso(dbo.Note_Terbit_Provider_Detail.No_Ref) = 0) THEN [dbo].func_GetKelBankSumber(dbo.Note_Lunas_Provider_Header.Bank_Asal, dbo.Provider_Rekening.Kode_Bank)
                       ELSE [dbo].func_GetKelBankSumber([dbo].func_GetBankSumberAso(dbo.Note_Terbit_Provider_Detail.No_Proposal), dbo.Provider_Rekening.Kode_Bank)
                  END,
                  dbo.Provider_Rekening.Rek_Nama,
                  dbo.Bank_Master.Nama_Bank,
                  dbo.Provider_Rekening.Bank_Cabang,
                  dbo.Provider_Rekening.Rek_No,
                  dbo.func_GetBiayaTransfer(dbo.Note_Lunas_Provider_Header.Bank_Asal, dbo.Provider_Rekening.Kode_Bank, dbo.Provider_Rekening.Bank_Lokasi),
                  dbo.Provider.Nama,
                  Payor_Header.Nama_Payor
   END
--UPDATE @Rep10468 SET Total=isnull(Sub_Total,0)-isnull(Biaya_Transfer,0)
SELECT
  Kelompok,
  Rek_Nama,
  Bank_Nama,
  Bank_Cabang,
  dbo.func_HilangkanTandaStr(Rek_No, '.- ', '') AS Rek_No,
  Sub_Total,
  Jml_Transfer,
  Biaya_Transfer,
  Total,
  Keterangan1,
  Keterangan2
FROM
  @Rep10468
WHERE
  Keterangan2 = 'DEWI SRI KARAWANG, RS'
ORDER BY
  Kelompok,
  Bank_Nama
