SELECT
  prs.Nama_Perusahaan,
  dbo.func_FixedDate(ck.submit_date) AS Submit_Date,
  ck.no_klaim AS Claim_Register,
  kr.Tgl_Terima,
  karyawan.Nama AS Nama_Karyawan,
  pst.Nama AS Nama_Pasien,
  pst.No_Peserta,
  ck.tgl_berobat,
  --cfah.diagnosis_code,
  --cfah.diagnosis_desc,
  --CASE
  --  WHEN cfah.diagnosis_desc='' THEN diag.Diagnosis1
  --  ELSE cfah.diagnosis_desc
  --END AS diagnosis_desc,
  ck.status,
  kp.No_Klaim,
  kp.Jml_Diajukan,
  kp.Jml_Ditolak,
  kp.Jml_Dibayar,
  note_terbit.No_Note,
  nlm.Tgl_Bayar,
  ck.remarks,
  --kp.Status,
  --note_terbit.Status_krm,
  --note_terbit.Status_ntm,
  --nlm.Status,
  --note_terbit.No_Ref, nlm.No_Ref,
  aai_det.remarks AS Detail_Remarks
FROM
  dbo.custport_klaim AS ck INNER JOIN
  dbo.Polis_Asuransi AS pa ON ck.no_proposal = pa.No_Proposal INNER JOIN
  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
  dbo.Peserta AS pst ON pa.No_Proposal = pst.No_Proposal AND ck.no_peserta = pst.No_Peserta INNER JOIN
  dbo.Peserta AS karyawan ON pa.No_Proposal = pst.No_Proposal AND LEFT(ck.no_peserta,10)+'-0' = karyawan.No_Peserta LEFT JOIN
  dbo.Klaim_Peserta AS kp ON ck.core_no_klaim = kp.No_Klaim LEFT JOIN
  ( SELECT
    	ntm.No_Ref,
    	ntm.No_Note,
    	krm.No_Klaim,
    	krm.Status AS Status_krm,
    	ntm.Status AS Status_ntm
    FROM
    	dbo.Klaim_Ref_Master AS krm INNER JOIN
      dbo.Note_Terbit_Master AS ntm ON krm.No_Ref = ntm.No_Ref
  ) AS note_terbit ON kp.No_Klaim = note_terbit.No_Klaim LEFT JOIN
  dbo.Note_Lunas_Master AS nlm ON note_terbit.No_Ref = nlm.No_Ref LEFT JOIN
  dbo.custport_from_aai_header AS cfah ON ck.id_from_aai_header = cfah.id_from_aai_header LEFT JOIN
  dbo.Diagnosa AS diag ON cfah.diagnosis_code = diag.Kode LEFT JOIN
  dbo.Klaim_Register AS kr ON kp.Id_RegKlaim = kr.Id_RegKlaim --LEFT JOIN
  --( SELECT
  --	  claim_id,
  --	  remarks
  --  FROM
  --  	dbo.custport_from_aai_detail
  --) AS aai_det ON cfah.claim_id = aai_det.claim_id
  CROSS APPLY dbo.func_custport_remark_detail(cfah.claim_id) AS aai_det 
WHERE
  ck.submit_date >= '2016-09-01'
  AND ck.submit_date < '2016-09-16'
ORDER BY
  dbo.func_FixedDate(ck.submit_date),
  ck.no_klaim

--SELECT STUFF(
--              ( SELECT ',' + remarks
--                FROM dbo.custport_from_aai_detail
--                GROUP BY claim_id
--                FOR XML PATH('')
--              ) ,1,1,''
--            ) AS Txt 

--SELECT * FROM dbo.Klaim_Ref_Master WHERE No_Klaim='2016.252148'
--SELECT * FROM dbo.Note_Terbit_Master WHERE No_Ref IN ('2016.050509','2016.050528')

--SELECT * FROM dbo.custport_klaim WHERE submit_date >= '2016-09-01'
--SELECT * FROM dbo.custport_klaim WHERE submit_date >= '2016-09-16' AND submit_date < '2016-10-01'
--SELECT * FROM dbo.custport_from_aai_header