USE [GrpInsurance]
GO


SELECT TOP (100) PERCENT
    dbo.Cabang_Rekening.Status,
    dbo.Cabang_Rekening.Kode_Cabang,
    dbo.Cabang.Nama AS Nama_Cabang,
    dbo.Cabang_Rekening.Rek_No,
    dbo.Cabang_Rekening.Rek_Nama,
    dbo.Bank_Master.Nama_Pendek,
    dbo.Cabang_Rekening.Bank_Cabang,
    dbo.Cabang_Rekening.Change_Date,
    dbo.Cabang_Rekening.Change_By,
    dbo.Polis_Asuransi.No_Polis,
    dbo.Polis_Asuransi.Active_Ya
FROM
    dbo.Cabang_Rekening
    INNER JOIN dbo.Polis_Asuransi ON LEFT(dbo.Cabang_Rekening.Kode_Cabang, 11) = dbo.Polis_Asuransi.No_Polis
                                     AND dbo.Polis_Asuransi.Active_Ya = 1
    INNER JOIN dbo.Cabang ON dbo.Polis_Asuransi.No_Prs = dbo.Cabang.No_Prs
                             AND SUBSTRING(dbo.Cabang_Rekening.Kode_Cabang, 13, 4) = dbo.Cabang.Kode
    LEFT OUTER JOIN dbo.Bank_Master ON dbo.Cabang_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
WHERE
    (dbo.Polis_Asuransi.Active_Ya = 1)


INSERT INTO dbo.Cabang_Rekening
        (Kode_Cabang,
         No_Polis,
         Cabang,
         Kode_Bank,
         Rek_No,
         Rek_Nama,
         Bank_Nama,
         Bank_Cabang,
         Bank_Alamat,
         Bank_Asal,
         Bank_Lokasi,
         Sandi_BI,
         Kode_Bank2,
         Rek_No2,
         Rek_Nama2,
         Bank_Nama2,
         Bank_Cabang2,
         Bank_Alamat2,
         Bank_Asal2,
         Bank_Lokasi2,
         Sandi_BI2,
         Status,
         Create_By,
         Create_Date,
         Change_By,
         Change_Date)
SELECT
  '2015.0017.1' + RIGHT(Kode_Cabang,5),
  '2015.0017.1', --No_Polis,
  Cabang,
  Kode_Bank,
  Rek_No,
  Rek_Nama,
  Bank_Nama,
  Bank_Cabang,
  Bank_Alamat,
  Bank_Asal,
  Bank_Lokasi,
  Sandi_BI,
  Kode_Bank2,
  Rek_No2,
  Rek_Nama2,
  Bank_Nama2,
  Bank_Cabang2,
  Bank_Alamat2,
  Bank_Asal2,
  Bank_Lokasi2,
  Sandi_BI2,
  Status,
  Create_By,
  Create_Date,
  Change_By,
  Change_Date
FROM
  Cabang_Rekening
WHERE
  No_Polis='2013.0104.1'

SELECT
  *
FROM
  Cabang_Rekening
WHERE
  No_Polis='2015.0017.1'