USE [GrpInsurance]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[proc_Rep10468_test]
		@IdPayor = NULL,
		@NoBV = N'429P 2015/7/14-0002',
		@RekAsal = '429',
		@BankTujuan = NULL,
		@Kota = NULL,
		@TglBayar1 = NULL,
		@TglBayar2 = NULL,
		@TglLunas1 = '2015-07-14',
		@TglLunas2 = '2015-07-14',
		@RekPolisYa = 1,
		@RekProviderYa = 1,
		@RekPesertaYa = 1,
		@TglBayar1Ya = 0,
		@TglBayar2Ya = 0,
		@TglLunas1Ya = 1,
		@TglLunas2Ya = 1


GO
--5870109

-----------------------------------------------------------------------

 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, '') = '')


        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
          