--ALTER Procedure [dbo].[Proc_Rep10564_Create]
DECLARE
@BankAsal varchar(4) = '960',
@CompanyCode varchar(8) = '',
@NoRef varchar(11) = '2017.035089',
@Layout INT = 1,
@Biaya BIT = 0,
@Mode INT = 2
--AS
  SET NOCOUNT ON
  IF isnull(@Mode,0) in (1,3)
  BEGIN
    --EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10564_A'  
    --CREATE TABLE TmpInsurance.dbo.[Rep10564_A] (
    DECLARE @Rep10564_A TABLE ( 
    [Urut] [int] IDENTITY (1, 1) NOT NULL ,
    [No_Ref] [varchar] (11) NULL DEFAULT(''),
    [Jenis] [int] NULL DEFAULT(0),
    [Layout] [int] NULL DEFAULT(0),
    [Kode_Prs] [varchar] (8) NULL DEFAULT(''),
    [Valas] [varchar] (5) NULL DEFAULT(''),
    [Rek_No] [varchar](100) NULL DEFAULT(''),
    [Rek_Nama] [varchar](100) NULL DEFAULT(''),
    [Kode_Asal] [varchar] (3) NULL DEFAULT(''),
    [Bank_Asal] [varchar] (3) NULL DEFAULT(''),
    [Bank_Kode] [varchar] (3) NULL DEFAULT(''),
    [Bank_Lokasi] [varchar] (2) NULL DEFAULT(''),
    [Bank_Kota] [varchar] (100) NULL DEFAULT(''),
    [Bank_Cabang] [varchar] (100) NULL DEFAULT(''),
    [Bank_Nama] [varchar] (100) DEFAULT(''),
    [Sandi_BI]  [varchar] (100) DEFAULT(''),
    [Jumlah]	[Float] default(0),
    [Biaya_Ya]	[bit] default(0),
    [Keterangan] [varchar] (100)DEFAULT(''))--,
    --CONSTRAINT [PK_Rep10564_A] PRIMARY KEY  CLUSTERED 
    --([Urut] )  ON [PRIMARY] ) ON [PRIMARY]

    --EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10564_B'  
    --CREATE TABLE TmpInsurance.dbo.[Rep10564_B] (
    DECLARE @Rep10564_B TABLE ( 
    [Urut] [int] IDENTITY (1, 1) NOT NULL ,
    [Jenis] [int] NULL DEFAULT(0),
    [Kode_Prs] [varchar] (8) NULL DEFAULT(''),
    [Valas] [varchar] (5) NULL DEFAULT(''),
    [Rek_No] [varchar](100) NULL DEFAULT(''),
    [Rek_Nama] [varchar](100) NULL DEFAULT(''),
    [Bank_Kode] [varchar] (3) NULL DEFAULT(''),
    [Bank_Kota] [varchar] (100) NULL DEFAULT(''),
    [Bank_Cabang] [varchar] (100) NULL DEFAULT(''),
    [Bank_Nama] [varchar] (100) DEFAULT(''),
    [Sandi_BI]  [varchar] (100) DEFAULT(''),
    [Jumlah]	[Float] default(0),
    [Biaya]	[Float] default(0),
    [Keterangan] [varchar] (100)DEFAULT(''))--,
    --CONSTRAINT [PK_Rep10564_B] PRIMARY KEY  CLUSTERED 
    --([Urut] )  ON [PRIMARY] ) ON [PRIMARY]
  END
  DECLARE @Total float
  DECLARE @Count int
  DECLARE @Kode_Bank varchar(4)
  DECLARE @Tgl_Transfer datetime
  DECLARE @Biaya_Ya bit
  IF isnull(@Biaya,0)=0 SET @Biaya_Ya=1
  ELSE SET @Biaya_Ya=0

  SET @Tgl_Transfer=GetDate()
  SET @Kode_Bank=''
  IF EXISTS(SELECT * FROM Bank_Asal WHERE Kode=@BankAsal)
  SELECT @Kode_Bank=isnull(Kode_Bank,'') FROM Bank_Asal WHERE Kode=@BankAsal

  INSERT INTO @Rep10564_A ([No_Ref], [Layout], [Jenis], [Kode_Prs], [Valas], [Rek_No], [Rek_Nama], [Sandi_BI], [Jumlah], [Kode_Asal], [Bank_Asal], [Bank_Kode], [Bank_Lokasi], [Bank_Cabang], [Bank_Nama], [Biaya_Ya], [Keterangan])   
  SELECT  @NoRef, @Layout,
         Jenis=
          CASE  
          WHEN isnull(@Kode_Bank,'')='' THEN 1
          WHEN isnull(@Layout,0)=1 AND (LEN(dbo.func_TrimAll(dbo.Peserta_Rekening.Rek_Nama))>25 AND @Kode_Bank='008') OR @Kode_Bank='012' THEN 1
          WHEN NOT (@Kode_Bank IN ('001','008','010','020')) THEN 1
          ELSE 0
          END, 
          dbo.func_TrimAll(@CompanyCode),
          dbo.func_TrimAll(dbo.Mata_Uang.Deskripsi), 
          dbo.func_HilangkanTandaStr(isnull(dbo.Peserta_Rekening.Rek_No, 'Rek.Kosong'),'.-, ',''), 
          dbo.func_HilangkanTandaStr(dbo.Peserta_Rekening.Rek_Nama,',-',' '), 
          dbo.Bank_Master.Sandi_BI,
  Amount=
    Case 
    WHEN dbo.Note_Terbit_Master.Jenis in (21, 23, 41, 43) THEN    Round(SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar, 0)) - SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old, 0)),2)  
    WHEN dbo.Note_Terbit_Master.Jenis in (20, 22, 40, 42) THEN -1*(Round(SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Ditolak, 0)) - [dbo].[func_GetUangMuka] (dbo.Klaim_Ref_Detail.No_Klaim)  ,2)) 
    END, 
    @BankAsal, 
    @Kode_Bank,
    dbo.func_TrimAll(dbo.Peserta_Rekening.Kode_Bank),
    dbo.Peserta_Rekening.Bank_Lokasi,
    dbo.func_HilangkanTandaStr(dbo.Peserta_Rekening.Bank_Cabang,',-',' '), 
    CASE
      WHEN @Kode_Bank='008' THEN  -- jika CIMB pake nama bank panjang
        dbo.func_TrimAll(isnull(dbo.Bank_Master.Nama_Bank,'No.Pst:'+dbo.Klaim_Ref_Detail.No_Peserta)) 
      ELSE
        dbo.func_TrimAll(isnull(dbo.Bank_Master.Nama_Pendek,'No.Pst:'+dbo.Klaim_Ref_Detail.No_Peserta))
    END,
    @Biaya_Ya, 
    'ByrKlaim'
  FROM  dbo.Klaim_Ref_Detail INNER JOIN
        dbo.Polis_Asuransi ON dbo.Klaim_Ref_Detail.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
        dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs LEFT OUTER JOIN
        --dbo.Peserta_Rekening ON LEFT(dbo.Klaim_Ref_Detail.No_Peserta,11)+'0' = LEFT(dbo.Peserta_Rekening.No_Peserta,11)+'0' AND   -- original (with bugs)
        dbo.Peserta_Rekening ON LEFT(dbo.Klaim_Ref_Detail.No_Peserta,11)+'0' = dbo.Peserta_Rekening.No_Peserta AND  -- modified by Pribadi Pamungkas, 2014-12-24
        dbo.Polis_Asuransi.No_Polis = dbo.Peserta_Rekening.No_Polis LEFT OUTER JOIN
        dbo.Bank_Master ON dbo.Peserta_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank LEFT OUTER JOIN
        dbo.Note_Terbit_Master ON dbo.Klaim_Ref_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref LEFT OUTER JOIN
        dbo.Mata_Uang ON dbo.Polis_Asuransi.Mata_Uang=dbo.Mata_Uang.Kode LEFT OUTER JOIN dbo.Item_Santunan_Polis ON
  dbo.Klaim_Ref_Detail.Kode_Santunan=dbo.Item_Santunan_Polis.Kode_Santunan AND dbo.Klaim_Ref_Detail.Id_Produk=dbo.Item_Santunan_Polis.Id_produk
  WHERE (dbo.Klaim_Ref_Detail.No_Ref=@NoRef)
  --AND ABS((COALESCE(dbo.Klaim_Ref_Detail.Jml_Dibayar, 0) - COALESCE(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old, 0))) <> 0
  GROUP BY dbo.Klaim_Ref_Detail.No_Peserta, dbo.Mata_Uang.Deskripsi, dbo.Peserta_Rekening.Rek_Nama, dbo.Peserta_Rekening.Rek_No, dbo.Bank_Master.Nama_Bank, dbo.Bank_Master.Nama_Pendek, dbo.Peserta_Rekening.Bank_Cabang, dbo.Note_Terbit_Master.Jenis,
           dbo.Peserta_Rekening.Kode_Bank, dbo.Peserta_Rekening.Bank_Lokasi, dbo.Klaim_Ref_Detail.No_Klaim, dbo.Bank_Master.Sandi_BI
  --HAVING SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar, 0) - isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old, 0))<>0
  having
  Case 
    WHEN dbo.Note_Terbit_Master.Jenis in (21, 23, 41, 43) THEN    Round(SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar, 0)) - SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old, 0)),2)  
    WHEN dbo.Note_Terbit_Master.Jenis in (20, 22, 40, 42) THEN -1*(Round(SUM(isnull(dbo.Klaim_Ref_Detail.Jml_Ditolak, 0)) - [dbo].[func_GetUangMuka] (dbo.Klaim_Ref_Detail.No_Klaim)  ,2)) 
    END <> 0
  ORDER BY dbo.Peserta_Rekening.Rek_Nama

  IF isnull(@Mode,0) in (2,3)
  BEGIN
     INSERT INTO @Rep10564_B ([Jenis], [Kode_Prs], [Valas], [Rek_No], [Rek_Nama], [Sandi_BI], [Jumlah], [Biaya], [Bank_Kode], [Bank_Cabang], [Bank_Nama], [Keterangan])   
           SELECT [Jenis], [Kode_Prs], [Valas], [Rek_No], [Rek_Nama], [Sandi_BI], Sum([Jumlah]), dbo.func_BiayaTransfer(Bank_Asal, Bank_Kode, Bank_Lokasi), [Bank_Kode], [Bank_Cabang], [Bank_Nama], [Keterangan]
           FROM @Rep10564_A --WHERE Jenis=0 
           GROUP BY [Jenis], [Kode_Prs], [Bank_Kode], [Bank_Lokasi], [Bank_Asal], [Rek_No], [Rek_Nama], [Valas], [Bank_Cabang], [Bank_Nama], [Sandi_BI], [Keterangan]
     IF isnull(@Biaya,0)=1 UPDATE @Rep10564_B SET [Biaya]=0 
  END
  PRINT 'RETURN 0'
--select top 10 * from Peserta_Rekening

SELECT * FROM @Rep10564_A
SELECT * FROM @Rep10564_B

----ALTER Procedure [dbo].[Proc_Rep10564_Excel]
----AS
--  SET NOCOUNT ON
--  DECLARE @Kode_Bank_2 varchar(4)
--  DECLARE @Tgl_Transfer_2 datetime
--  DECLARE @Layout_2 int
--  SET @Kode_Bank_2=''
--  --IF EXISTS(SELECT * FROM Bank_Asal WHERE Kode=@BankAsal)
--  SELECT @Kode_Bank_2=A.Kode_Bank, @Layout_2=B.Layout FROM Bank_Asal A INNER JOIN @Rep10564_A B ON A.Kode=B.Kode_Asal
--  GROUP BY A.Kode_Bank, B.Layout
--  IF NOT EXISTS (SELECT * FROM @Rep10564_B WHERE Jenis=1)
--  BEGIN
--      SELECT '' as [Rek_No], '' as [Rek_Nama], '' as [Valas], 0 as [Jumlah], '' as [Bank_Kota], '' as [Bank_Cabang], '' as [Bank_Nama], '' as [Keterangan] 
--      PRINT 'RETURN 0'
--   END  

--  --BANK NISP
--  IF @Kode_Bank_2='012' 
--  BEGIN
--       SELECT [Rek_Nama]as [NAMAREK],
--       'T' as [CARA BYR],
--       [Rek_No] as [ACCREK], 
--       [Rek_Nama]as [NAMA],
--       [Bank_Nama]as [BANK],
--       [Bank_Kota]as [KOTA],
--       '1' as [GAJI],
--       [Keterangan] as [KETERANGAN],
--       dbo.func_FormatCurr1(Sum(isnull([Jumlah],0)-isnull([Biaya],0)),2) as [TOTAL]
--       FROM @Rep10564_B WHERE Jenis=0
--       GROUP BY [Rek_No], [Rek_Nama], [Valas], [Bank_Kota], [Bank_Cabang], [Bank_Nama], [Keterangan]
--  END
--  ELSE IF @Layout_2=1 AND @Kode_Bank_2='008'
--  SELECT [Rek_No], [Rek_Nama], [Valas], isnull([Jumlah],0)-isnull([Biaya],0) as Jumlah, [Bank_Kota], [Bank_Cabang], [Bank_Nama], [Keterangan] FROM @Rep10564_B WHERE Jenis=1
--  ELSE IF @Kode_Bank_2<>'008'
--  SELECT [Rek_No], [Rek_Nama], [Valas], isnull([Jumlah],0)-isnull([Biaya],0) as [Jumlah], [Bank_Kota], [Bank_Cabang], [Bank_Nama], [Keterangan] FROM @Rep10564_B
--  --SELECT [Rek_No], [Rek_Nama], [Valas], 99 as [Jumlah], [Bank_Kota], [Bank_Cabang], [Bank_Nama], [Keterangan] FROM TmpInsurance.dbo.[Rep10564_B]

-----
--ALTER Procedure [dbo].[Proc_Rep10564_Single]
--AS
  -- MASTER (SINGLE RECORD)
  SET NOCOUNT ON
  DECLARE @Total1 float
  DECLARE @Total3 float
  DECLARE @Count_3 int
  DECLARE @Layout_3 int
  DECLARE @Bank_Asal varchar(4)
  DECLARE @Kode_Bank_3 varchar(4)
  DECLARE @Company_Code varchar(8)
  DECLARE @Biaya_Ya_3 bit
  DECLARE @Tgl_Transfer_3 datetime
  SET   @Tgl_Transfer_3=GetDate()
  SET @Kode_Bank_3=''

  SELECT @Layout_3=B.Layout, @Kode_Bank_3=A.Kode_Bank,  @Bank_Asal=A.Kode,  @Company_Code=B.Kode_Prs, @Biaya_Ya_3=B.Biaya_Ya FROM Bank_Asal A INNER JOIN @Rep10564_A B ON A.Kode=B.Kode_Asal
  GROUP BY A.Kode_Bank, A.Kode, B.Kode_Prs, B.Biaya_Ya, B.Layout

  SELECT @Total1=SUM(isnull([Jumlah],0)-isnull(Biaya,0)), @Count_3=Count(*) FROM @Rep10564_B WHERE Jenis=1
  SELECT @Total3=SUM(isnull([Jumlah],0)-isnull(Biaya,0)), @Count_3=Count(*) FROM @Rep10564_B WHERE Jenis=0
  --FORMAT BANK MANDIRI
  IF @Kode_Bank_3='001' 
  BEGIN
       --SELECT '"'+'P'+'","'+dbo.func_date_format('YYYYMMDD',@Tgl_Transfer_3)+'","'+isnull(dbo.Bank_Asal.Rek_No,'')+'",'+CAST(@Count_3 as varchar)+','+dbo.func_FormatCurr1(@Total3,2)
       SELECT 'P'+','+dbo.func_date_format('YYYYMMDD',@Tgl_Transfer_3)+','+isnull(dbo.Bank_Asal.Rek_No,'')+','+CAST(@Count_3 as varchar)+','+dbo.func_FormatCurr1(@Total3,2)
       as teks
       FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
          dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
    WHERE dbo.Bank_Asal.Kode=@Bank_Asal
   END
  --FORMAT BANK LIPPO/CIMB
  IF @Kode_Bank_3='008'
  BEGIN
       IF @Layout_3 IN (0,1)
       SELECT '"'+isnull(dbo.Bank_Asal.Rek_No,'')   +'",'+ 
                  '"'+isnull(dbo.Bank_Asal.Rek_Nama,'') +'",'+ 
                  '"'+RTRIM(LTRIM(isnull(dbo.Mata_Uang.Deskripsi,'')))+'",'+  
                  dbo.func_FormatCurr1(@Total3,2)+','+ 
                  '""' as Teks
    FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
          dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
    WHERE dbo.Bank_Asal.Kode=@Bank_Asal
    ELSE 
    IF @Layout_3 IN (2)
       SELECT LEFT(isnull(dbo.Bank_Asal.Rek_No,''),11)   +','+ 
                  LEFT(isnull(dbo.Bank_Asal.Rek_Nama,''),40) +','+ 
                  LEFT(RTRIM(LTRIM(isnull(dbo.Mata_Uang.Deskripsi,''))),3)+','+  
                  dbo.func_FormatCurr1(@Total3,2)+','+ 
                  'Pembayaran Klaim'+','+CAST(@Count_3 as varchar)+','+CONVERT(VARCHAR(20), GETDATE(), 112) as Teks
    FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
          dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
    WHERE dbo.Bank_Asal.Kode=@Bank_Asal
    ELSE IF @Layout_3 IN (3)
       SELECT LEFT(isnull(dbo.Bank_Asal.Rek_No,''),11)   +','+ 
              LEFT(isnull(dbo.Bank_Asal.Rek_Nama,''),40) +','+ 
              LEFT(RTRIM(LTRIM(isnull(dbo.Mata_Uang.Deskripsi,''))),3)+','+  
              dbo.func_FormatCurr1(@Total3,2)+','+ 
              'Pembayaran Klaim'+','+CAST(@Count_3 as varchar)+','+CONVERT(VARCHAR(20), GETDATE(), 112)+',julius.setiawan@equity.co.id' as Teks
    FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
          dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
    WHERE dbo.Bank_Asal.Kode=@Bank_Asal
   END
   -- FORMAT BCA
   ELSE
   IF @Kode_Bank_3='010' 
   BEGIN
     SELECT
     '0'+
     'MP'+
     dbo.func_Tanggal(@Tgl_Transfer_3)+
     dbo.func_RataKiriSpace(dbo.Bank_Asal.Rek_No,' ',10)+' '+dbo.func_RataKiriSpace(@Company_Code,' ',8)+dbo.func_RataKiriSpace('',' ',15)+
     '00000'+dbo.func_RataKananSpace(dbo.func_FormatCurr1(@Total3,2),'0',15)+
     dbo.func_RataKananSpace(CAST(@Count_3 as varchar),'0',5)+
     dbo.func_RataKiriSpace('BCA',' ',18)+
     dbo.func_RataKiriSpace('Byr.Klaim',' ',18)+
     dbo.func_RataKiriSpace('',' ',18)+
     dbo.func_RataKiriSpace('',' ',18)+
     dbo.func_RataKiriSpace('',' ',18)+
     dbo.func_RataKiriSpace('',' ',18)+
     dbo.func_RataKiriSpace('',' ',23) as Teks
     FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
           dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
     WHERE dbo.Bank_Asal.Kode=@Bank_Asal
  END
  ELSE
  -- FORMAT BANK GANESHA
  IF @Kode_Bank_3='020'
  BEGIN
           SELECT '"'+isnull(dbo.Bank_Asal.Rek_No,'')   +'",'+ 
                  '"'+isnull(dbo.Bank_Asal.Rek_Nama,'') +'",'+ 
                  '"'+RTRIM(LTRIM(isnull(dbo.Mata_Uang.Deskripsi,'')))+'",'+  
                  dbo.func_FormatCurr1(@Total3,2)+','+ 
                  '"'+isnull(dbo.Bank_Asal.Bank_Kota,'') +'",'+ 
                  '"'+isnull(dbo.Bank_Master.Nama_Pendek,'') +'",'+ 
                  '"'+isnull(dbo.Bank_Asal.Bank_Cabang,'') +'",'+ 
                  '""' as Teks
    FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
          dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
    WHERE dbo.Bank_Asal.Kode=@Bank_Asal
  END
  ELSE
 -- FORMAT NISP
  IF @Kode_Bank_3='012'
  BEGIN
           SELECT 'TANGGAL PROSES '+dbo.func_Tanggal(@Tgl_Transfer_3) as Teks
    FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
          dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
    WHERE dbo.Bank_Asal.Kode=@Bank_Asal
  END
  -- BANK LAIN-LAIN
  ELSE         
     SELECT 'AUTO CREDIT '+isnull(dbo.Bank_Master.Nama_Bank,'')+' NO.REK:'+isnull(dbo.Bank_Asal.Rek_No,'')+'('+isnull(dbo.Bank_Asal.Rek_Nama,'') +') ' as Teks
     FROM  dbo.Bank_Asal INNER JOIN dbo.Bank_Master ON dbo.Bank_Asal.Kode_Bank = dbo.Bank_Master.Kode_Bank INNER JOIN
           dbo.Mata_Uang ON dbo.Bank_Asal.Valas = dbo.Mata_Uang.Kode
     WHERE dbo.Bank_Asal.Kode=@Bank_Asal

  PRINT 'RETURN 0'



----------
--ALTER Procedure [dbo].[Proc_Rep10564_Multi]
--AS
  -- DETIL (MULTIPLE RECORD)
  SET NOCOUNT ON
  DECLARE @Total_2 float
  DECLARE @Count_2 int
  DECLARE @Layout_2 int
  DECLARE @Kode_Bank_2 varchar(4)
  DECLARE @Kode_Asal varchar(4)
  DECLARE @Tgl_Transfer_2 datetime
  SET   @Tgl_Transfer_2=GetDate()
  SELECT @Kode_Bank_2=A.Kode_Bank, @Layout_2=B.[Layout], @Kode_Asal=B.Kode_Asal FROM Bank_Asal A INNER JOIN @Rep10564_A B ON A.Kode=B.Kode_Asal
  GROUP BY A.Kode_Bank, B.[Layout], B.Kode_Asal

--  SELECT @Kode_Bank_2=Kode_Bank FROM Bank_Asal WHERE Kode=@BankAsal
  --FORMAT BANK MANDIRI
  IF @Kode_Bank_2='001' 
  BEGIN
    --IF @Layout_2=0 
           SELECT 
           [Rek_No]+','+    --Beneficiary Account No.	9(35)     -- '"'+[Rek_No]+'",'+ 
           '"'+[Rek_Nama]+'",'+  --Beneficiary Account Name	X(40)
           '"'+'Jakarta'+'",'+   --Beneficary Address 1	X(35)
           '"",'+          --Beneficary Address 2	X(35)
           '"",'+          --Beneficary Address 3	X(35)
           [Valas]+','+     --Transfer Currency	X(4)      -- '"'+[Valas]+'",'+
           ''+dbo.func_FormatCurr1(isnull([Jumlah],0)-isnull([Biaya],0),2)+','+ --Transfer Amount	9(15)v9(2)
           ''+','+  --Transaction Remark	X(20)
           ''+','+  --Customer Reference	X(19)
           --'IBU'+','+  --Unused	X(3)
           CASE WHEN @Kode_Bank_2<>[Bank_Kode] THEN
              CASE WHEN [Biaya]<100000000 THEN 'LBU,' --Mandiri ke lain, dibawah 100 juta
              ELSE 'RBU,' --Mandiri ke lain, 100 juta keatas
              END
           ELSE 'IBU,' --Mandiri ke Mandiri
           END
           +[Sandi_BI]+','+ --Beneficiary Bank Code	X(12)   -- +'"'+[Sandi_BI]+'",'+
           '"'+[Bank_Nama]+'",'+ --Beneficiary Bank Name	X(35)
           '"'+[Bank_Cabang]+'",'+ --Beneficiary Bank Address 1	X(35)
           ''+','+ --Beneficiary Bank Address 2	X(35)
           ''+','+ --Beneficiary Bank Address 3	X(35)
           ''+','+ --Beneficiary Bank City Name or Country Name	X(35) 
           'Y'+','+ --Beneficiary notification flag	X(1)
           '"roudhotul.janah@equity.co.id",'+ --Beneficiary notification email	X(100)
           ''+','+ --Organization Directory Name	X(40)
           ''+','+ --Identical Status	X(1)
           ''+','+ --Beneficiary Status	X(1)
           ''+','+ --Beneficiary Citizenship	X(1)
           ''+','+ --Purpose of Transaction	X(40)
           ''+','+ --Transaction Description	X(500)
           ''+','+ --Remittance Code 1	X(3)
           ''+','+ --Remittance Information 1	X(30)
           ''+','+ --Remittance Code 2	X(3)
           ''+','+ --Remittance Information 2	X(30)
           ''+','+ --Remittance Code 3	X(3)
           ''+','+ --Remittance Information 3	X(30)
           ''+','+ --Remittance Code 4	X(3)
           ''+','+ --Remittance Information 4	X(30)
           ''+','+ --Instruction Code 1	X(3)
           ''+','+ --Instruction Remark 1 	X(30)
           ''+','+ --Instruction Code 2	X(3)
           ''+','+ --Instruction Remark 2	X(30)
           ''+','+ --Instruction Code 3	X(3)
           ''+','+ --Instruction Remark 3	X(30)
           ''+','+ --Charge Instruction	X(3)
           ''+','+ --SWIFT Method	X(3)
           'A'+ --Extended Payment Detail	X(10000)
           '' as Teks
           FROM @Rep10564_B --WHERE Jenis=0
    
           
           
  END
  --FORMAT BANK LIPPO/CIMB
  IF @Kode_Bank_2='008' 
  BEGIN
    IF @Layout_2=0 
           SELECT '"'+[Rek_No]+'",'+'"'+[Rek_Nama]+'",'+'"'+[Valas]+'",'+
           dbo.func_FormatCurr1(isnull([Jumlah],0)-isnull([Biaya],0),2)+',"'+
           [Bank_Cabang]+'",'+'"'+[Bank_Nama]+'",'+'"'+[Keterangan]+'"' as Teks
           FROM @Rep10564_B WHERE Jenis=0
    ELSE   
    IF @Layout_2=1 SELECT '"'+LEFT([Rek_No],35)+'",'+
                               '"'+LEFT([Rek_Nama],25)+'",'+
                               '"'+LEFT([Valas],3)+'",'+
                               dbo.func_FormatCurr1(isnull([Jumlah],0)-isnull([Biaya],0),2)+',"'+
           LEFT([Bank_Cabang],24)+'",'+'"'+LEFT([Bank_Nama],15)+'",'+'"'+LEFT([Keterangan],23)+'"' as Teks
           FROM @Rep10564_B WHERE Jenis=0
    ELSE  
    IF @Layout_2=2 
           SELECT LEFT(REPLACE([Rek_No],'-',''),20)+','+
                               LEFT(REPLACE([Rek_Nama],'.',''),35)+','+
                               LEFT([Valas],3)+','+
                               dbo.func_FormatCurr1(isnull([Jumlah],0)-isnull([Biaya],0),2)+','+
           --LEFT([Bank_Cabang],40)+','+LEFT([Bank_Nama],40)+','+LEFT([Bank_Cabang],40)+','+
           LEFT([Keterangan],80) as Teks
           FROM @Rep10564_B WHERE Jenis=0
    IF @Layout_2=3
           SELECT LEFT(REPLACE([Rek_No],'-',''),20)+','+
                  LEFT(REPLACE([Rek_Nama],'.',''),35)+','+
                  LEFT(isnull([Valas],''),3)+','+
                  dbo.func_FormatCurr1(isnull([Jumlah],0)-isnull([Biaya],0),2)+','+
                  --LEFT(isnull([Bank_Cabang],'JAKARTA'),40)+','+
                  --LEFT(isnull([Bank_Nama],''),40)+','+
                  --LEFT(isnull([Bank_Cabang],'JAKARTA'),40)+','+
                  LEFT(isnull([Keterangan],''),80)+','+
                  LEFT(isnull([Bank_Nama],''),40)+','+
                  --CASE WHEN isnull([Sandi_BI],'')='' THEN '#BI-CODE harus Diisi!#' ELSE
                  --LEFT(isnull([Sandi_BI],''),7) END+',,' 
                  'Y,Y,' as Teks
           FROM @Rep10564_B WHERE Jenis=0

  END
  --FORMAT BANK BCA
  ELSE
  IF @Kode_Bank_2='010' 
  BEGIN
    SELECT
    '1'+
    dbo.func_RataKiriSpace([Rek_No],' ',16)+
    dbo.func_RataKiriSpace('',' ',18)+ 
    '0000'+dbo.func_RataKananSpace(dbo.func_FormatCurr1(isnull([Jumlah],0)-isnull([Biaya],0),2),'0',16)+
    dbo.func_RataKiriSpace([Rek_Nama],' ',35)+
    dbo.func_RataKiriSpace('',' ',7)+dbo.func_RataKiriSpace('',' ',7)+dbo.func_RataKiriSpace('',' ',4)+
--dbo.func_RataKiriSpace('',' ',12)+
    dbo.func_RataKiriSpace(Bank_Nama,' ',18)+
    dbo.func_RataKiriSpace(Bank_Cabang,' ',18)+
    dbo.func_RataKiriSpace('',' ',18)+
    dbo.func_RataKiriSpace('',' ',18)+
    dbo.func_RataKiriSpace('',' ',18)+
    CASE 
    WHEN @Kode_Bank_2='010' THEN '014' 
    ELSE '888'
    END as Teks
    FROM @Rep10564_B WHERE Jenis=0
  END

  --FORMAT BANK GANESHA
  ELSE
  IF @Kode_Bank_2='020'
  BEGIN
    SELECT '"'+[Rek_No]+'",'+'"'+[Rek_Nama]+'",'+'"'+[Valas]+'",'+ dbo.func_FormatCurr1(isnull([Jumlah],0)- isnull([Biaya],0),2)+',"'+[Bank_Cabang]+'",'+'"'+[Bank_Nama]+',"'+'"'+[Keterangan]+'"' as Teks
           FROM @Rep10564_B WHERE Jenis=0
  END

  PRINT 'RETURN 0'
