-- 1. cek yg tidak ada

SELECT
	pa.No_Proposal,
	prs.Nama_Perusahaan,
	ntm.No_Ref,
	krm.No_Klaim,
	ntm.Tgl_Bayar,
	CASE
	  WHEN ckl.id_klaim_link IS NULL THEN 'Tidak ada'
	  ELSE 'Ada'
	END AS 'CustPort'
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal INNER JOIN
	dbo.Klaim_Ref_Master AS krm ON ntm.No_Ref = krm.No_Ref LEFT JOIN
	dbo.custport_klaim_link AS ckl ON krm.No_Klaim = ckl.no_klaim
WHERE
  --prs.Group_Perusahaan = 23017
  prs.Group_Perusahaan IN (
                                    23017, -- MAP
                                    23186, -- PLI
                                    4      -- GT   
                                 )
  AND pa.Mata_Uang = '02'
  AND pa.No_Proposal <> '2016.0253' -- polis dummy
  AND ntm.Jenis = 21  -- klaim
  AND ntm.Status = 'I'
  --AND ntm.Tgl_Bayar = '2017-01-13'
  --AND ntm.Tgl_Bayar >= '2016-12-01'
  AND ckl.id_klaim_link IS NULL
ORDER BY
  ntm.Tgl_Bayar,
  pa.No_Proposal,
  ntm.No_Ref,
	krm.No_Klaim
  
------------------------------------------------------------------------------------------

--DECLARE 
--  @v_no_proposal VARCHAR(9),
--  @v_nama_perusahaan VARCHAR(50),
--  @v_no_ref VARCHAR(11),
--  @v_no_klaim VARCHAR(11)

--DECLARE list_cursor CURSOR FOR  

--SELECT
--	pa.No_Proposal,
--	prs.Nama_Perusahaan,
--	ntm.No_Ref,
--	krm.No_Klaim
--FROM
--	dbo.Polis_Asuransi AS pa INNER JOIN
--	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
--	dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal INNER JOIN
--	dbo.Klaim_Ref_Master AS krm ON ntm.No_Ref = krm.No_Ref LEFT JOIN
--	dbo.custport_klaim_link AS ckl ON krm.No_Klaim = ckl.no_klaim
--WHERE
--  prs.Group_Perusahaan = 23017
--  AND pa.Mata_Uang = '02'
--  AND pa.No_Proposal <> '2016.0253' -- polis dummy
--  AND ntm.Jenis = 21  -- klaim
--  AND ntm.Status = 'I'
--  AND ntm.Tgl_Bayar = '2016-12-16'
--  AND ckl.id_klaim_link IS NULL -- cari yg tidak ada
--ORDER BY
--  pa.No_Proposal,
--  ntm.No_Ref,
--	krm.No_Klaim

--OPEN list_cursor  
--FETCH NEXT FROM list_cursor INTO @v_no_proposal, @v_nama_perusahaan, @v_no_ref, @v_no_klaim

--WHILE @@FETCH_STATUS = 0  
--BEGIN  
--  SELECT
--  	*
--  FROM
--  	dbo.Klaim_Peserta
--  WHERE
--    No_Klaim = 
--  FETCH NEXT FROM list_cursor INTO @v_no_proposal, @v_nama_perusahaan, @v_no_ref, @v_no_klaim
--END  

--CLOSE list_cursor  
--DEALLOCATE list_cursor 


------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-- 2 cek yg salah no klaim

DECLARE 
  @v_no_proposal VARCHAR(9),
  @v_nama_perusahaan VARCHAR(50),
  @v_no_ref VARCHAR(11),
  @v_no_klaim VARCHAR(11),
  @v_claim_register VARCHAR(11)

DECLARE list_cursor CURSOR FOR
SELECT
	pa.No_Proposal,
	prs.Nama_Perusahaan,
	ntm.No_Ref,
	krm.No_Klaim,
	ck.no_klaim AS Claim_Register
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal INNER JOIN
	dbo.Klaim_Ref_Master AS krm ON ntm.No_Ref = krm.No_Ref LEFT JOIN
	dbo.custport_klaim_link AS ckl ON krm.No_Klaim = ckl.no_klaim INNER JOIN
	dbo.custport_klaim AS ck ON ckl.id_klaim = ck.id_klaim
WHERE
  prs.Group_Perusahaan = 23017
  AND pa.Mata_Uang = '02'
  AND pa.No_Proposal <> '2016.0253' -- selain polis dummy
  AND ntm.Jenis = 21  -- klaim
  AND ntm.Status = 'I'
  AND ck.status NOT IN ('Draft','Submitted','Registered')
ORDER BY
  pa.No_Proposal,
  ntm.No_Ref,
	krm.No_Klaim

SELECT
  ck.id_klaim,
	ck.no_klaim,
	ckl.no_klaim,
	kp.Status
FROM
	dbo.custport_klaim AS ck INNER JOIN
	dbo.custport_klaim_link AS ckl ON ck.id_klaim = ckl.id_klaim INNER JOIN
	dbo.Klaim_Peserta AS kp ON ck.no_proposal = kp.No_Proposal
	                           AND ckl.no_klaim = kp.No_Klaim
WHERE
  ck.submit_date >= '2016-09-01'
  AND ck.status NOT IN ('Draft','Submitted','Registered')
ORDER BY
  ck.id_klaim

SELECT
	COUNT(claim_id) AS jml_claim_id,
	claim_register
FROM
	dbo.custport_from_aai_header AS cfah
WHERE
  cfah.received_date >= '2016-09-01'
  AND claims_process_status IN ('13','14')
GROUP BY
  claim_register 
