USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10468_test]    Script Date: 11/28/2012 09:58:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC proc_Rep10468_test '', 'BV 2011/7/27-0025', '429', '', 0,  0, 0, '2011-07-27', '2011-07-27', 1, 0,  0,  0, 0, 1, 1

ALTER Procedure [dbo].[proc_Rep10468_test]          
@IdPayor    varchar(2),
@NoBV varchar (20)=null,
@RekAsal    varchar(5),
@BankTujuan varchar(5),
@Kota int,
@TglBayar1 Datetime,
@TglBayar2 Datetime,
@TglLunas1 Datetime,
@TglLunas2 Datetime,
@RekPolisYa bit,
@RekProviderYa bit,
@RekPesertaYa bit,
@TglBayar1Ya bit,
@TglBayar2Ya bit,
@TglLunas1Ya bit,
@TglLunas2Ya bit
AS
	SET NOCOUNT ON
	EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10468'
	CREATE TABLE [TmpInsurance].[dbo].[Rep10468](
	[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 [TmpInsurance].[dbo].[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 [TmpInsurance].[dbo].[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 [TmpInsurance].[dbo].[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 [TmpInsurance].[dbo].[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 [TmpInsurance].[dbo].[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_Pendek, 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_Pendek, 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 [TmpInsurance].[dbo].[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 [TmpInsurance].[dbo].[Rep10468]
ORDER BY Kelompok, Bank_Nama
