USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10468_test]    Script Date: 03/02/2016 17:53:41 ******/
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

-- last modified by Pribadi Pamungkas, 2015-07-13
--ALTER Procedure [dbo].[proc_Rep10468_test]          
DECLARE
@IdPayor    varchar(2) = NULL,
@NoBV varchar (20) = '003P 2016/6/29-0001',
@RekAsal    varchar(5) = '003',
@BankTujuan varchar(5) = NULL,
@Kota int = NULL,
@TglBayar1 Datetime = NULL,
@TglBayar2 DATETIME = NULL,
@TglLunas1 Datetime = '2016-06-29',
@TglLunas2 Datetime = '2016-06-29',
@RekPolisYa BIT = 1,
@RekProviderYa bit = 1,
@RekPesertaYa bit = 1,
@TglBayar1Ya bit = 0,
@TglBayar2Ya bit = 0,
@TglLunas1Ya bit = 1,
@TglLunas2Ya bit = 1


	--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 @temp_table 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 ,
	  No_Ref VARCHAR(11),
	  No_Ref_Provider VARCHAR(11)
  )

  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)
    INSERT INTO @temp_table
          (Kelompok,
           Rek_Nama,
           Bank_Nama,
           Bank_Cabang,
           Rek_No,
           Sub_Total,
           Jml_Transfer,
           Biaya_Transfer,
           Keterangan1,
           Keterangan2,
           No_Ref)
    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, dbo.Note_Terbit_Master.No_Ref
    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)
    INSERT INTO @temp_table
          (Kelompok,
           Rek_Nama,
           Bank_Nama,
           Bank_Cabang,
           Rek_No,
           Sub_Total,
           Jml_Transfer,
           Biaya_Transfer,
           Keterangan1,
           Keterangan2,
           No_Ref)
    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, dbo.Note_Terbit_Master.No_Ref
    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)
INSERT INTO @temp_table
          (Kelompok,
           Rek_Nama,
           Bank_Nama,
           Bank_Cabang,
           Rek_No,
           Sub_Total,
           Jml_Transfer,
           Biaya_Transfer,
           Keterangan1,
           Keterangan2,
           No_Ref)
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, dbo.Note_Terbit_Master.No_Ref
    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)
INSERT INTO @temp_table
          (Kelompok,
           Rek_Nama,
           Bank_Nama,
           Bank_Cabang,
           Rek_No,
           Sub_Total,
           Jml_Transfer,
           Biaya_Transfer,
           Keterangan1,
           Keterangan2,
           No_Ref)
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, dbo.Note_Terbit_Master.No_Ref
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,'')='')
  
  
  -- start: Total Diskon & Jml_Pot pad Header (jika lebih dari 1 header) -- add by Pribadi Pamungkas, 2015-07-13
  DECLARE
    @Header_Diskon FLOAT,
    @Header_Jml_Pot FLOAT
  SELECT
    --dbo.Note_Terbit_Provider_Header.No_Ref_Provider,
    @Header_Diskon = dbo.Note_Terbit_Provider_Header.Diskon,
    @Header_Jml_Pot = dbo.Note_Terbit_Provider_Header.Jml_Pot
  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.No_Ref_Provider,
    dbo.Note_Terbit_Provider_Header.Diskon,
    dbo.Note_Terbit_Provider_Header.Jml_Pot
  -- end: Total Diskon & Jml_Pot pad Header (jika lebih dari 1 header) -- add by Pribadi Pamungkas, 2015-07-13
  
  
  --INSERT INTO [TmpInsurance].[dbo].[Rep10468] (Kelompok, Rek_Nama, Bank_Nama, Bank_Cabang, Rek_No, Sub_Total, Jml_Transfer, Biaya_Transfer,  Keterangan1, Keterangan2)
  INSERT INTO @temp_table
          (Kelompok,
           Rek_Nama,
           Bank_Nama,
           Bank_Cabang,
           Rek_No,
           Sub_Total,
           Jml_Transfer,
           Biaya_Transfer,
           Keterangan1,
           Keterangan2,
           No_Ref_Provider)
  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, -- sebelumnya (kesalahan: jika ada lebih dari 1 header, maka yg terbaca hanya 1 header saja)
    --SUM((dbo.Note_Terbit_Provider_Detail.Total)-isnull(dbo.Note_Terbit_Provider_Header.Diskon,0)) AS Sub_Total, -- modified by Pribadi Pamungkas, 2015-07-08 (kesalahan: jika ada lebih dari 1 detail, jml diskon yg terhitung adalah total diskon dikalikan jumlah detail)
    SUM(dbo.Note_Terbit_Provider_Detail.Total)-SUM(isnull(dbo.Note_Terbit_Provider_Detail.Jml_Diskon,0)) AS Sub_Total, -- modified by Pribadi Pamungkas, 2015-07-10 (perbaikan: jml diskon harusnya menghitung dari detail)

    --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, -- sebelumnya (kesalahan: jika ada lebih dari 1 header, maka yg terbaca hanya 1 header saja)
    --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, -- modified by Pribadi Pamungkas, 2015-07-08 (kesalahan: jika ada lebih dari 1 detail, jml diskon yg terhitung adalah total diskon dikalikan jumlah detail)
    --SUM(dbo.Note_Terbit_Provider_Detail.Total)-SUM(isnull(dbo.Note_Terbit_Provider_Header.Diskon,0))-SUM(isnull(dbo.Note_Terbit_Provider_Detail.Jml_Diskon,0))-SUM(isnull(dbo.Note_Terbit_Provider_Header.Jml_Pot,0)) AS Jml_Transfer, -- modified by Pribadi Pamungkas, 2015-07-10 (kesalahan: jika detail banyak, header salah)
    --SUM(dbo.Note_Terbit_Provider_Detail.Total)-isnull(@Header_Diskon,0)-SUM(isnull(dbo.Note_Terbit_Provider_Detail.Jml_Diskon,0))-isnull(@Header_Jml_Pot,0) AS Jml_Transfer, -- modified by Pribadi Pamungkas, 2015-07-10 (perbaikan: jml semua harusnya dihitung semua, agar hedaer lebih dari 1 atau detail lebih dari 1 bisa terhitung dgn benar)
    SUM(dbo.Note_Terbit_Provider_Detail.Total)-isnull(header.Diskon,0)-SUM(isnull(dbo.Note_Terbit_Provider_Detail.Jml_Diskon,0))-isnull(header.Jml_Pot,0) AS Jml_Transfer, -- modified by Pribadi Pamungkas, 2015-07-10 (perbaikan: jml semua harusnya dihitung semua, agar hedaer lebih dari 1 atau detail lebih dari 1 bisa terhitung dgn benar)

    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, dbo.Note_Terbit_Provider_Header.No_Ref_Provider
  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 
    INNER JOIN  -- add by Pribadi Pamungkas, 2015-07-13
    (   SELECT
          dbo.Note_Terbit_Provider_Header.No_Ref_Provider,
          dbo.Note_Terbit_Provider_Header.Diskon,
          dbo.Note_Terbit_Provider_Header.Jml_Pot
        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.No_Ref_Provider,
          dbo.Note_Terbit_Provider_Header.Diskon,
          dbo.Note_Terbit_Provider_Header.Jml_Pot) AS header ON dbo.Note_Terbit_Provider_Header.No_Ref_Provider = header.No_Ref_Provider

  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,
    dbo.Note_Terbit_Provider_Header.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,
    header.Diskon,
    header.Jml_Pot,
    dbo.Note_Terbit_Provider_Header.No_Ref_Provider
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,
  No_Ref,
  No_Ref_Provider
--INTO
--  temp_prib_20160628_10468
FROM
  --[TmpInsurance].[dbo].[Rep10468]
  @temp_table
ORDER BY
  Kelompok,
  Bank_Nama
