DECLARE
  @NoProposal VARCHAR(9),
  @NoPeserta VARCHAR(12),
  @IdProduk VARCHAR(10),
  @Keluarga BIT

SET @NoProposal = N'2014.0683'
SET @NoPeserta = N'1992005375-0'
SET @IdProduk = N'2014.02650'

SELECT
  T1.No_Peserta,
  T1.No_Klaim,
  T1.Tgl_Klaim,
  T2.Nama_Sub,
  T1.Jml_Diajukan,
  Jml_Deductible = CASE WHEN ISNULL(T2.No_Klaim, '') <> '' THEN T2.Jml_Deductible
                        ELSE T1.Jml_Deductible
                   END,
  Jml_Dibayar = CASE WHEN ISNULL(T2.No_Klaim, '') <> '' THEN T2.Jml_Dibayar
                     ELSE T1.Jml_Dibayar
                END,
  Jml_Ditolak = CASE WHEN ISNULL(T2.No_Klaim, '') <> '' THEN T2.Jml_Ditolak
                     ELSE T1.Jml_Ditolak
                END,
  T2.No_Ref,
  T2.Tgl_Lunas
FROM

  (SELECT
    T1.No_Peserta,
    T1.No_Klaim,
    T1.Tgl_Klaim,
    T2.Nama_Sub,
    T1.Jml_Diajukan,
    T1.Jml_Deductible,
    T1.Jml_Dibayar - T1.Jml_Dibayar_Old AS Jml_Dibayar,
    T1.Jml_Ditolak
   FROM
    dbo.Klaim_Peserta T1
    INNER JOIN dbo.Sub_Kelompok AS T2 ON T1.Sub_Kelompok = T2.Sub_Kelompok
   WHERE
    T1.No_Proposal = @NoProposal
    AND T1.No_Santunan = @NoPeserta
    AND T1.Id_Produk = @IdProduk) AS T1 LEFT OUTER JOIN 

  (SELECT
    T1.No_Peserta,
    T1.No_Klaim,
    T1.Tgl_Klaim,
    T2.Nama_Sub,
    T1.No_Ref,
    dbo.func_GetTglLunasKlaimRef(T1.Tgl_Klaim, T1.No_Ref) AS Tgl_Lunas,
    SUM(ISNULL(T1.Jml_Diajukan, 0)) AS Jml_Diajukan,
    SUM(ISNULL(T1.Jml_Deductible, 0)) AS Jml_Deductible,
    SUM(ISNULL(T1.Jml_Dibayar - T1.Jml_Dibayar_Old, 0)) AS Jml_Dibayar,
    SUM(ISNULL(T1.Jml_Ditolak, 0)) AS Jml_Ditolak
   FROM
    dbo.Klaim_Ref_Detail T1
    INNER JOIN dbo.Sub_Kelompok AS T2 ON T1.Sub_Kelompok = T2.Sub_Kelompok
   WHERE
    T1.No_Proposal = @NoProposal
    AND T1.No_Santunan = @NoPeserta
    AND T1.Id_Produk = @IdProduk
   GROUP BY
    T1.No_Peserta,
    T1.No_Klaim,
    T1.Tgl_Klaim,
    T2.Nama_Sub,
    T1.No_Ref,
    dbo.func_GetTglLunasKlaimRef(T1.Tgl_Klaim, T1.No_Ref)
  ) AS T2 ON T2.No_Peserta = T1.No_Peserta AND T2.No_Klaim = T1.No_Klaim AND T2.Tgl_Klaim = T1.Tgl_Klaim
