USE [GrpInsurance]
GO




SELECT
  kp.Status,
  kp.No_Klaim,
  p.Nama,
  --kp.AdMedika_Ya,
  kp.No_Peserta,
  p.Grade,
  p.Kelas,
  dbo.Sub_Kelompok.Nama_Pendek,
  dbo.func_FixedDate(kp.Tgl_Klaim) AS Tgl_Klaim,
  dbo.func_FixedDate(kp.Tgl_Kwitansi) AS Tgl_Kwitansi,
  kp.Jml_Diajukan,
  kp.Jml_Deductible,
  kp.Jml_Dispensasi,
  kp.Jml_Dibayar,
  kp.Jml_Ditolak,
  kp.No_Proposal,
  p.No_Prs,
  kp.Claim_Ref,
  RTRIM(ISNULL(dbo.Provider.Nama, 'Reimbursment')) AS Rumah_Sakit,
  kp.Id_RegKlaim,
  kp.Kode_Diagnosa,
  dbo.Diagnosa.Diagnosis1,
  dbo.func_get_skema_klaim_per_proposal(kp.No_Proposal) AS skema_klaim,
  kp.Create_By,
  dbo.func_FixedDate(kp.Create_Date) AS Create_Date,
  Change_Date = CASE WHEN kp.Change_Date IS NULL THEN dbo.func_FixedDate(kp.Create_Date)
                     ELSE dbo.func_FixedDate(kp.Change_Date)
                END,
  Change_By = CASE WHEN kp.Change_By IS NULL THEN kp.Create_By
                   ELSE kp.Change_By
              END,
  c.Kode AS 'Kode_Cabang',
  c.Nama AS 'Nama_Cabang'
FROM
  dbo.Klaim_Peserta AS kp WITH (NOLOCK) INNER JOIN 
  dbo.Peserta AS p WITH (NOLOCK) ON kp.No_Proposal = p.No_Proposal
                                                AND kp.No_Peserta = p.No_Peserta LEFT OUTER JOIN 
  dbo.Sub_Kelompok WITH (NOLOCK) ON kp.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok LEFT OUTER JOIN 
  dbo.Diagnosa ON kp.Kode_Diagnosa = dbo.Diagnosa.Kode LEFT OUTER JOIN 
  dbo.Provider WITH (NOLOCK) ON kp.Kode_Provider = dbo.Provider.IDI INNER JOIN
  dbo.Cabang AS c ON p.Cabang = c.Kode AND c.No_Prs = '2001.0075'
WHERE
  kp.No_Proposal = '2014.0835'
  AND c.Kode LIKE 'KP%'
ORDER BY
  kp.No_Klaim
