USE [GrpInsurance]
GO

SELECT
	*
FROM
	(
    SELECT 
      klaim.No_Proposal AS no_Proposal,
      klaim.No_Polis AS no_Polis,
      klaim.No_Klaim AS no_Klaim,
      klaim.No_Peserta AS no_Peserta,
      klaim.Nama AS nama_Peserta,
      klaim.Nama_Paket AS nama_Produk,
      klaim.Tgl_Klaim AS tgl_Pengajuan,
      klaim.Tgl_Kwitansi AS tgl_kuitansi,
      --klaim.Mata_Uang AS mata_Uang,
      klaim.Jml_Diajukan AS nilai_Klaim,
      --klaim.Jml_Deductible AS deductible,
      --klaim.Jml_Ditolak AS klaim_Tolak,
      --klaim.Jml_Dibayar AS klaim_Bayar,
      
      --deductible = CASE WHEN ISNULL(detail.No_Klaim, '') <> '' THEN detail.Jml_Deductible
      --                      ELSE klaim.Jml_Deductible
      --                 END,
      klaim_Tolak = CASE WHEN ISNULL(detail.No_Klaim, '') <> '' THEN detail.Jml_Ditolak
                         ELSE klaim.Jml_Ditolak
                    END,
      klaim_Bayar = CASE WHEN ISNULL(detail.No_Klaim, '') <> '' THEN detail.Jml_Dibayar
                         ELSE klaim.Jml_Dibayar
                    END,
      
      --detail.No_Ref,
      
      --detail.Tgl_Lunas AS tgl_Lunas,
      --ref_provider.Tgl_Bayar,
      COALESCE(detail.Tgl_Lunas,ref_provider.Tgl_Bayar) AS Tgl_Lunas,
      
      detail.No_Note AS no_Nota,
      klaim.Jenis_Pembayaran AS jenis_Bayar,
      klaim.rumah_Sakit
    FROM
      (
        SELECT
          pa.No_Proposal,
          pa.No_Polis,
          kp.No_Klaim,
          kp.No_Peserta,
          p.Nama,
          pp.Nama_Paket,
          kp.Tgl_Klaim,
          kp.Tgl_Kwitansi,
          --pa.Mata_Uang,
          mu.Deskripsi AS Mata_Uang,
          kp.Jml_Diajukan,
          kp.Jml_Deductible,
          kp.Jml_Ditolak,
          kp.Jml_Dibayar,
          kp.Id_Produk,
          CASE kp.Jenis_Pembayaran
            WHEN 'R' THEN 'Reimburse'
            WHEN 'P' THEN 'Provider'
            ELSE ''
          END AS Jenis_Pembayaran,
          rs.Nama AS rumah_Sakit
        FROM
          dbo.Klaim_Peserta AS kp INNER JOIN
          dbo.Polis_Asuransi AS pa ON kp.No_Proposal = pa.No_Proposal INNER JOIN
          dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal
                              AND kp.No_Peserta = p.No_Peserta INNER JOIN
          dbo.Produk_Polis AS pp ON kp.Id_Produk = pp.Id_Produk INNER JOIN
          dbo.Mata_Uang AS mu ON pa.Mata_Uang = mu.Kode LEFT JOIN
          dbo.Provider AS rs ON kp.Kode_Provider = rs.IDI
        WHERE
          kp.Status = 'I'
        --ORDER BY
        --  kp.Tgl_Klaim
      ) AS klaim LEFT OUTER JOIN 
      (
        SELECT
          krd.No_Peserta,
          krd.No_Klaim,
          krd.Tgl_Klaim,
          sk.Nama_Sub,
          krd.No_Ref,
          dbo.func_GetTglLunasKlaimRef(krd.Tgl_Klaim, krd.No_Ref) AS Tgl_Lunas,
          SUM(ISNULL(krd.Jml_Diajukan, 0)) AS Jml_Diajukan,
          SUM(ISNULL(krd.Jml_Deductible, 0)) AS Jml_Deductible,
          SUM(ISNULL(krd.Jml_Dibayar - krd.Jml_Dibayar_Old, 0)) AS Jml_Dibayar,
          SUM(ISNULL(krd.Jml_Ditolak, 0)) AS Jml_Ditolak,
          krd.Id_Produk,
          ntm.No_Note--,
          --ntm.Tgl_Buat
        FROM
          dbo.Klaim_Ref_Detail AS krd INNER JOIN 
          dbo.Sub_Kelompok AS sk ON krd.Sub_Kelompok = sk.Sub_Kelompok INNER JOIN
          dbo.Note_Terbit_Master AS ntm ON krd.No_Ref = ntm.No_Ref INNER JOIN
          (
            SELECT
              krd.No_Peserta,
              krd.No_Klaim,
              MAX(ntm.Tgl_Buat) AS Tgl_Buat
            FROM
              dbo.Klaim_Ref_Detail AS krd INNER JOIN 
              dbo.Sub_Kelompok AS sk ON krd.Sub_Kelompok = sk.Sub_Kelompok INNER JOIN
              dbo.Note_Terbit_Master AS ntm ON krd.No_Ref = ntm.No_Ref
            GROUP BY
              krd.No_Peserta,
              krd.No_Klaim
          ) AS note_maks ON krd.No_Klaim = note_maks.No_Klaim
                            AND ntm.Tgl_Buat = note_maks.Tgl_Buat
        --WHERE
        --  krd.No_Klaim = '2016.006175'
        GROUP BY
          krd.No_Peserta,
          krd.No_Klaim,
          krd.Tgl_Klaim,
          sk.Nama_Sub,
          krd.No_Ref,
          dbo.func_GetTglLunasKlaimRef(krd.Tgl_Klaim, krd.No_Ref),
          krd.Id_Produk,
          ntm.No_Note--,
          --ntm.Tgl_Buat
      ) AS detail ON detail.No_Peserta = klaim.No_Peserta
                     AND detail.No_Klaim = klaim.No_Klaim
                     AND detail.Tgl_Klaim = klaim.Tgl_Klaim
                     AND detail.Id_Produk = klaim.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 detail.No_Ref = ref_provider.No_Ref

    WHERE
      klaim.No_Proposal = '2016.0008'
      AND klaim.Tgl_Klaim >= '2016-02-01'
      AND klaim.Tgl_Klaim <= '2016-02-29'
      --AND klaim.Tgl_Klaim <= '2016-04-30'


    --ORDER BY
    --  klaim.Tgl_Klaim
	) AS x
WHERE
  x.Tgl_Lunas IS NOT NULL
ORDER BY
  x.tgl_Pengajuan,
  x.no_Peserta




GO


