USE [GrpInsurance]
GO

-- query dari dbo.view_NotePolis
DECLARE
  @date_start DATE = '2018-02-01',
  @date_to DATE = '2018-02-28'
SELECT TOP (100) PERCENT
  --ntm.Status,
  ROW_NUMBER() OVER (ORDER BY ntm.No_Ref) AS No,
  kp.Id_RegKlaim,
  ntm.No_Ref,
  nj.Description,
  ntm.No_Note,
  kp.Tgl_Klaim,
  ntm.Tgl_Buat,
  ntm.Tgl_Bayar AS Tgl_PPK,
  ntm.Total,
  ntm.Tgl_Lunas,
  ntm.Jml_Lunas,
  ntm.Saldo,
  --CAST(ntm.Saldo AS NUMERIC(16,8)),
  ntm.Change_Date,
  ntm.Change_By,
  ntm.No_Proposal,
  ns.Tgl_Kirim,
  nj.Kode,
  ns.Pengirim,
  ns.Tgl_Terima,
  ns.Penerima,  
  MAX(nlm.Change_Date) AS FinDt,
  ntm.Jml_Peserta,
  ntm.Tgl_Produksi
FROM
  dbo.Klaim_Ref_Master AS krm 
  INNER JOIN
  dbo.Klaim_Peserta AS kp ON krm.No_Klaim = kp.No_Klaim 
  LEFT JOIN
  dbo.Note_Terbit_Master AS ntm ON krm.No_Ref = ntm.No_Ref
  LEFT JOIN
  dbo.Note_Jenis AS nj ON ntm.Jenis = nj.Kode
  LEFT JOIN
  dbo.Note_Shipping AS ns ON ntm.No_Ref = ns.No_Ref
  LEFT JOIN
  dbo.Note_Lunas_Master AS nlm ON ntm.No_Ref = nlm.No_Ref
  LEFT JOIN
  (
		SELECT
			kp.No_Klaim
		FROM
			dbo.Klaim_Ref_Master AS krm 
			INNER JOIN
			dbo.Klaim_Peserta AS kp ON krm.No_Klaim = kp.No_Klaim 
		WHERE
			kp.Status <> 'C'
			AND (kp.Tgl_Klaim >= @date_start AND kp.Tgl_Klaim <= @date_to)
		GROUP BY
			kp.No_Klaim
		HAVING
			COUNT(kp.No_Klaim) > 1
  ) AS reclaim ON kp.No_Klaim = reclaim.No_Klaim
WHERE
	ntm.Jenis IN (
      20,
      21,
      22,
      23,
      24,
      25,
      26,
      30,
      40,
      41,
      42,
      43,
      44,
      45,
      48,
      120,
      121,
      130,
      200
    ) 
	AND (kp.Tgl_Klaim >= @date_start AND kp.Tgl_Klaim <= @date_to) -- Sep, Okt, Nov, Des
	AND reclaim.No_Klaim IS NULL
GROUP BY
  --ntm.Status,
  kp.Id_RegKlaim,
  ntm.No_Ref,
  nj.Description,
  ntm.No_Note,
  kp.Tgl_Klaim,
  ntm.Tgl_Buat,
  ntm.Tgl_Bayar,
  ntm.Total,
  ntm.Tgl_Lunas,
  ntm.Jml_Lunas,
  ntm.Saldo,
  ntm.Change_Date,
  ntm.Change_By,
  ntm.No_Proposal,
  ns.Tgl_Kirim,
  ns.Pengirim,
  ns.Tgl_Terima,
  ns.Penerima,
  nj.Kode,
  ntm.Jml_Peserta,
  ntm.Tgl_Produksi
ORDER BY
  ntm.No_Ref
----20    Tagihan Provider
----21    Santunan Klaim
----22    Refund PROVIDER
----23    Santunan ReClaim
----24    Tagihan Klaim
----25    Tagihan ReClaim
----26    DN Diskon PROVIDER
----30    Pembayaran PROVIDER
----40    Tagihan Provider ASO
----41    Santunan Klaim ASO
----42    Refund Provider  ASO
----43    Santunan Re-Klaim ASO
----44    Tagihan Klaim ASO
----45    Tagihan Re-Klaim ASO
----48    Buffer Klaim
----120    Pengurang Santunan Klaim
----121    Pengurang Santunan Provider
----130    Tagihan Buffer Klaim
----200    Klaim Reibursment 
