SELECT
  Kode_Broker = CASE WHEN ISNULL(Polis_Asuransi.Broker_Id, '') <> '' THEN 'Kode Broker:' + Polis_Asuransi.Broker_Id
                     ELSE ''
                END,
  dbo.Klaim_Ref_Detail.Kode_Provider,
  dbo.Klaim_Ref_Detail.No_Proposal,
  dbo.Klaim_Ref_Detail.Id_Produk,
  dbo.Klaim_Ref_Detail.No_Klaim,
  dbo.Klaim_Ref_Detail.No_Peserta,
  No_Nik = CASE WHEN ISNULL(dbo.Peserta.NIK, '') <> '' THEN dbo.Peserta.No_Peserta + '/NIK.' + dbo.Peserta.Nik
                ELSE dbo.Peserta.No_Peserta
           END,
  Nama = CASE dbo.Peserta.Sebagai
           WHEN 0 THEN 
                CASE
                  WHEN COALESCE(dbo.Peserta.Nik,'')<>'' THEN dbo.Peserta.Nama + ', NIK: ' + dbo.Peserta.Nik
                  ELSE dbo.Peserta.Nama
                END
           ELSE 
                CASE
                  WHEN COALESCE(Karyawan.Nik,'')<>'' THEN dbo.Peserta.Nama + '(' + ISNULL(Karyawan.Nama, '') + ', NIK: ' + Karyawan.Nik + ')'
                  ELSE dbo.Peserta.Nama + '(' + ISNULL(Karyawan.Nama, '') + ')'
                END
         END,

  dbo.Peserta.Cabang,
  dbo.Peserta.No_Induk,
  dbo.Klaim_Ref_Detail.Tgl_Klaim,
  dbo.Klaim_Ref_Detail.Tgl_Kwitansi,
  T1.Uang_Muka,
  dbo.Klaim_Ref_Detail.Jml_Diajukan,
  dbo.Klaim_Ref_Detail.Jml_Deductible,
  dbo.Klaim_Ref_Detail.Jml_Dibayar,
  dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,
  dbo.Klaim_Ref_Detail.Jml_Ditolak,
  dbo.Klaim_Ref_Detail.Kode_Tolakan,
  
  (dbo.Klaim_Ref_Detail.Jml_Dibayar-dbo.Klaim_Ref_Detail.Jml_Dibayar_Old) AS Sub_Total,
  
  dbo.Klaim_Ref_Detail.Keterangan,
  dbo.Klaim_Ref_Detail.Kode_Diagnosa,
  dbo.Item_Santunan_Polis.Deskripsi AS Nama_Santunan,
  dbo.Item_Santunan_Polis.Urutan,
  dbo.Produk_Polis.Nama_Paket,
  dbo.Cabang.Nama AS Nama_Cabang,
  dbo.Provider.Nama AS Rumah_Sakit,
  Tolakan.Nama AS Tolakan,
  --ISNULL(@NamaCabang, '') AS Cabang,
  dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' AS Nama_Perusahaan,
  dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Mulai) + ' - ' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Selesai) AS Masa_Asuransi,
  ISNULL(dbo.Perusahaan.Alamat3, '') AS Alamat,
  ISNULL(dbo.Perusahaan.Kota3, '') + ' Kode Pos: ' + ISNULL(dbo.Perusahaan.Kode_Pos3, '') AS Kota,
  'Telp: ' + ISNULL(dbo.Perusahaan.Telepon3, '') + ' Fax: ' + ISNULL(dbo.Perusahaan.Fax3, '') AS Telepon,
  dbo.Perusahaan.Contact_Person
FROM
  dbo.Klaim_Ref_Detail
  INNER JOIN dbo.Peserta ON dbo.Klaim_Ref_Detail.No_Peserta = dbo.Peserta.No_Peserta
                            AND dbo.Klaim_Ref_Detail.No_Proposal = dbo.Peserta.No_Proposal
  LEFT OUTER JOIN dbo.Peserta Karyawan ON LEFT(dbo.Klaim_Ref_Detail.No_Peserta, 11) + '0' = Karyawan.No_Peserta
                                          AND dbo.Klaim_Ref_Detail.No_Proposal = Karyawan.No_Proposal
                                          AND RIGHT(Karyawan.No_Peserta, 1) = '0' 
  INNER 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
  INNER JOIN dbo.Produk_Polis ON dbo.Klaim_Ref_Detail.Id_Produk = dbo.Produk_Polis.Id_Produk
  LEFT OUTER JOIN dbo.Provider ON dbo.Klaim_Ref_Detail.Kode_Provider = dbo.Provider.IDI
  LEFT OUTER JOIN dbo.Cabang ON dbo.Peserta.Cabang = dbo.Cabang.Kode
                                AND dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
  LEFT OUTER JOIN Tolakan ON dbo.Klaim_Ref_Detail.Kode_Tolakan = Tolakan.Kode
  LEFT OUTER JOIN (SELECT
                    No_Klaim,
                    Uang_Muka
                   FROM
                    Klaim_Ref_Detail
                   WHERE
                    No_Ref = '2015.084935'
                   GROUP BY
                    No_Klaim,
                    Uang_Muka) T1 ON T1.No_Klaim = dbo.Klaim_Ref_Detail.No_Klaim 
  INNER JOIN dbo.Polis_Asuransi ON dbo.Klaim_Ref_Detail.No_Proposal = dbo.Polis_Asuransi.No_Proposal
  INNER JOIN dbo.Perusahaan ON dbo.Peserta.No_Prs = dbo.Perusahaan.No_Prs
WHERE
  (dbo.Klaim_Ref_Detail.No_Ref = '2015.084935')
  --AND (Klaim_Ref_Detail.Jml_Ditolak <> 0)
  --AND ((Klaim_Ref_Detail.Jml_Ditolak <> 0 AND @Model = 1) OR @Model = 0)
ORDER BY
  dbo.Produk_Polis.Nama_Paket,
  Klaim_Ref_Detail.No_Klaim,
  Klaim_Ref_Detail.No_Peserta