DECLARE
  @pi_type TINYINT = 0
--RI (Rawat Inap) ==> jumlahnya apa adanya (semua item santunan)
--RJ (Rawat Jalan) ==> exclude item santunan melahirkan, kacamata, obat
--LH (Melahirkan)
--Obat (Obat obatan)
--KC (Kacamata)

-- item di RJ:
--RJ01	Konsultasi (per kunjungan, per hari )
--RJ0101	Pemeriksaan Dokter umum
--RJ0103	Dokter + Obat
--RJ0105	Dokter Spesialis tanpa surat pengantar dari dokter umum sebelumnya, per kunjungan
--RJ0215	Pembelian obat-obatan tanpa resep per tahun
--RJ03	Pemeriksaan penunjang diagnosa (laboratorium), per tahun
--RJ0508	Keluarga Berencana per tahun
--RJ0624	Imunisasi dan vaksinasi per tahun
--RJ1101	Fisioterapi per tahun
--RJG14	Perawatan gigi per tahun
--RJK02	Kacamata per tahun
--RJT1503	Medical check up per tahun

SELECT
  x.[MEMBER NO],
  x.[NAMA PESERTA],
  x.[NAMA KARYAWAN],
  x.[EMP ID],
  x.[NIK],
  x.[NO REFERENSI],
  x.[PROVIDER],
  x.[CLAIM NO],
  x.[DIAGNOSA],
  x.[JAMINAN],
  x.[PRODUK PLAN],
  x.[TGL MASUK],
  x.[TGL KELUAR],
  x.[TERIMA],
  x.[TGL AKSEP],
  x.[TGL ADMIN],
  x.[TGL BAYAR],
  x.[JML PENGAJUAN],
  x.[EXCESS],
  x.[DEPOSIT],
  x.[TOLAKAN],
  x.[JML_BAYAR],
  x.[DIVISION],
  x.[KET],
  SUM(x.[RJ01_Diajukan]) AS    RJ01,
  SUM(x.[RJ0101_Diajukan]) AS  RJ0101,
  SUM(x.[RJ0103_Diajukan]) AS  RJ0103,
  SUM(x.[RJ0105_Diajukan]) AS  RJ0105,
  SUM(x.[RJ0215_Diajukan]) AS RJ0215,
  SUM(x.[RJ03_Diajukan]) AS RJ03,
  SUM(x.[RJ0508_Diajukan]) AS RJ0508,
  SUM(x.[RJ0624_Diajukan]) AS RJ0624,
  SUM(x.[RJ1101_Diajukan]) AS RJ1101,
  SUM(x.[RJG14_Diajukan]) AS RJG14,
  SUM(x.[RJK02_Diajukan]) AS RJK02,
  SUM(x.[RJT1503_Diajukan]) AS RJT1503,
  --x.[Jml_Diajukan],
  SUM(x.[RJ01_Dibayar]) AS RJ01,
  SUM(x.[RJ0101_Dibayar]) AS RJ0101,
  SUM(x.[RJ0103_Dibayar]) AS RJ0103,
  SUM(x.[RJ0105_Dibayar]) AS RJ0105,
  SUM(x.[RJ0215_Dibayar]) AS RJ0215,
  SUM(x.[RJ03_Dibayar]) AS RJ03,
  SUM(x.[RJ0508_Dibayar]) AS RJ0508,
  SUM(x.[RJ0624_Dibayar]) AS RJ0624,
  SUM(x.[RJ1101_Dibayar]) AS RJ1101,
  SUM(x.[RJG14_Dibayar]) AS RJG14,
  SUM(x.[RJK02_Dibayar]) AS RJK02,
  SUM(x.[RJT1503_Dibayar]) AS RJT1503--,
  --x.[Jml_Dibayar]
FROM
	(
    SELECT
	    kp.No_Peserta AS [MEMBER NO],
	    pst.Nama AS [NAMA PESERTA],
	    karyawan.Nama AS [NAMA KARYAWAN],
	    NULL AS [EMP ID],
	    karyawan.Nik AS NIK,
	    note.No_Ref AS [NO REFERENSI],
	    rs.Nama AS PROVIDER,
	    kp.No_Klaim AS [CLAIM NO],
	    diag.Diagnosis1 AS DIAGNOSA,
	    NULL AS JAMINAN,
	    pp.Nama_Paket AS [PRODUK PLAN],
	    kp.Tgl_Masuk AS [TGL MASUK],
	    kp.Tgl_Keluar AS [TGL KELUAR],
	    kr.Tgl_Terima AS TERIMA,
	    NULL AS [TGL AKSEP],
	    NULL AS [TGL ADMIN],
	    --nlm.Tgl_Bayar AS [TGL BAYAR],
	    --dbo.func_GetTglLunasKlaimRef(kp.No_Klaim, note.No_Ref) AS Tgl_Lunas,
	    ----COALESCE(detail.Tgl_Lunas,ref_provider.Tgl_Bayar) AS Tgl_Lunas,
     -- ref_provider.Tgl_Bayar,
      CASE
        WHEN kp.Jenis_Pembayaran='P' THEN ref_provider.Tgl_Bayar
        ELSE dbo.func_GetTglLunasKlaimRef(kp.No_Klaim, note.No_Ref)
      END AS [TGL BAYAR],
    	
	    note.Jml_Diajukan AS [JML PENGAJUAN],
	    NULL AS EXCESS,
	    kp.Uang_Muka AS DEPOSIT,
	    kp.Tolakan AS TOLAKAN,
	    note.Jml_Dibayar AS JML_BAYAR,
	    NULL AS DIVISION,
	    kp.Notes AS KET,
	    --nlm.No_Ref,
	    --nlm.Jumlah
	    --kd.Kode_Santunan,
	    CASE
	      WHEN kd.Kode_Santunan='RJ01' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ01_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ0101' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ0101_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ0103' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ0103_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ0105' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ0105_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ0215' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ0215_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ03' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ03_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ0508' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ0508_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ0624' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ0624_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJ1101' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJ1101_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJG14' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJG14_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJK02' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJK02_Diajukan,
      CASE
	      WHEN kd.Kode_Santunan='RJT1503' THEN kd.Jml_Diajukan
	      ELSE 0
      END AS RJT1503_Diajukan,
      --kd.Jml_Diajukan,
	    
	    --kd.Jml_Ditolak,
	    CASE
	      WHEN kd.Kode_Santunan='RJ01' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ01_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ0101' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ0101_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ0103' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ0103_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ0105' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ0105_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ0215' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ0215_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ03' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ03_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ0508' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ0508_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ0624' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ0624_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJ1101' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJ1101_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJG14' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJG14_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJK02' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJK02_Dibayar,
      CASE
	      WHEN kd.Kode_Santunan='RJT1503' THEN kd.Jml_Dibayar
	      ELSE 0
      END AS RJT1503_Dibayar--,
	    --kd.Jml_Dibayar
    FROM
	    dbo.Klaim_Peserta AS kp INNER JOIN
	    dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim INNER JOIN
	    dbo.Polis_Asuransi AS pa ON kp.No_Proposal = pa.No_Proposal INNER JOIN
	    dbo.Peserta AS pst ON kp.No_Proposal = pst.No_Proposal
	                          AND kp.No_Peserta = pst.No_Peserta INNER JOIN
      dbo.Peserta AS karyawan ON kp.No_Proposal = karyawan.No_Proposal 
                                 AND LEFT(kp.No_Peserta,10)+'-0' = karyawan.No_Peserta LEFT JOIN
      ( SELECT
    	    ntm.No_Ref,
    	    ntm.No_Note,
    	    krm.No_Klaim,
    	    krm.Jml_Diajukan,
    	    krm.Jml_Ditolak,
    	    krm.Jml_Dibayar
        FROM
    	    dbo.Klaim_Ref_Master AS krm INNER JOIN
          dbo.Note_Terbit_Master AS ntm ON krm.No_Ref = ntm.No_Ref
      ) AS note ON kp.No_Klaim = note.No_Klaim LEFT JOIN
      dbo.Note_Lunas_Master AS nlm ON note.No_Ref = nlm.No_Ref LEFT JOIN
      dbo.Provider AS rs ON kp.Kode_Provider = rs.IDI LEFT JOIN
      dbo.Diagnosa AS diag ON kp.Kode_Diagnosa = diag.Kode LEFT JOIN
      dbo.Klaim_Register AS kr ON kp.Id_RegKlaim = kr.Id_RegKlaim LEFT JOIN
      dbo.Produk_Polis AS pp ON kp.Id_Produk = pp.Id_Produk LEFT JOIN
      (
        SELECT
          ntpd.No_Ref,
          MAX(ntph.Tgl_Bayar) AS Tgl_Bayar
        FROM
          dbo.Note_Terbit_Provider_Header AS ntph INNER JOIN
          dbo.Note_Terbit_Provider_Detail AS ntpd ON ntph.No_Ref_Provider = ntpd.No_Ref_Provider
        GROUP BY
          ntpd.No_Ref
      ) ref_provider ON note.No_Ref = ref_provider.No_Ref
    WHERE
      pa.No_Polis = '2016.0140.1'
      AND kp.Status = 'I'
      --AND note.Jml_Dibayar > 0
      AND pp.Sub_Kelompok = '0301'  -- RJ
    --ORDER BY
    --  kp.No_Klaim
  ) AS x
--WHERE
--  x.[TGL BAYAR] IS NOT NULL
GROUP BY
  x.[MEMBER NO],
  x.[NAMA PESERTA],
  x.[NAMA KARYAWAN],
  x.[EMP ID],
  x.[NIK],
  x.[NO REFERENSI],
  x.[PROVIDER],
  x.[CLAIM NO],
  x.[DIAGNOSA],
  x.[JAMINAN],
  x.[PRODUK PLAN],
  x.[TGL MASUK],
  x.[TGL KELUAR],
  x.[TERIMA],
  x.[TGL AKSEP],
  x.[TGL ADMIN],
  x.[TGL BAYAR],
  x.[JML PENGAJUAN],
  x.[EXCESS],
  x.[DEPOSIT],
  x.[TOLAKAN],
  x.[JML_BAYAR],
  x.[DIVISION],
  x.[KET]--,
  --x.[Jml_Diajukan],
  --x.[Jml_Dibayar]
ORDER BY
  x.[CLAIM NO]