
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2016-05-20
ALTER PROCEDURE proc_rep_klaim_ojk
	@pi_no_polis VARCHAR(11),
	@pi_bulan TINYINT, -- 0: Januari; 1: Feb
	@pi_tahun VARCHAR(4)
AS
BEGIN
	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	
  DECLARE
    @p_tgl_dari DATETIME,
    @p_tgl_sampai DATETIME

  SET @p_tgl_dari = CAST(@pi_tahun + RIGHT('0' + CAST(@pi_bulan + 1 AS VARCHAR(2)),2) + '01' AS DATETIME)
  SET @p_tgl_sampai = DATEADD(mm, DATEDIFF(mm,0,CAST(@pi_tahun + RIGHT('0' + CAST(@pi_bulan + 1 AS VARCHAR(2)),2) + '01' AS DATETIME)) + 1, -1)

  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_Polis = @pi_no_polis
        AND klaim.Tgl_Klaim >= @p_tgl_dari
        AND klaim.Tgl_Klaim <= @p_tgl_sampai

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

END
GO
