USE [AJK]
GO

/****** Object:  View [dbo].[view_api_claim_status_v2]    Script Date: 14/09/2018 14:59:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[view_api_claim_status_v2]

AS

SELECT
	cc.claim_no AS ID_CLAIM,
	pes.NAMA_PESERTA_1 AS CUSTOMER_NAME,
	pes.NO_HP AS MOBILE_NO,
	pes.NO_KONTRAK_EXTERNAL AS CONTRACT_ID,
	pes.KODE_PRODUKSI_EXTERNAL AS PROD_CODE_HCID,
	pes.ID_NASABAH_EXTERNAL AS CUID,
	CONVERT(VARCHAR(10), CAST(cc.register_date AS DATETIME), 103) + ' ' + CONVERT(VARCHAR(8), CAST(cc.register_date AS DATETIME), 108) AS CLAIM_DATETIME,
	UPPER(ct.description) AS CLAIM_TYPE,
	CONVERT(VARCHAR(10), CAST(cc.event_date AS DATETIME), 103) + ' ' + CONVERT(VARCHAR(8), CAST(cc.event_date AS DATETIME), 108) AS EVENT_DATE,	
	s.status_code AS STATUS_CLAIM_CODE,
	--UPPER(s.description) AS STATUS_CLAIM,	-- sebelum diubah
	CASE									-- diubah oleh Pribadi Pamungkas. 2018-09-14
		WHEN COALESCE(claim_core.STATUS_CLAIM,'') = '' THEN UPPER(s.description)
		ELSE claim_core.STATUS_CLAIM
	END AS STATUS_CLAIM,
	CONVERT(VARCHAR(10), CAST(ch.date AS DATETIME), 103) + ' ' + CONVERT(VARCHAR(8), CAST(ch.date AS DATETIME), 108) AS STATUS_DATE,	
	'0' as CITY_CATEGORY,
	UPPER(claim_core.REMARK) AS REMARK,
	UPPER(claim_core.CP_RELATIONSHIP) AS  CP_RELATIONSHIP,
	cc.claim_amount AS CLAIM_AMOUNT,
	UPPER(COALESCE(claim_core.REJECT_REASON, 'NON')) AS REJECT_REASON,
	UPPER(claim_core.DOCUMENT_STATUS_HCID) AS DOCUMENT_STATUS_HCID,
	UPPER(claim_core.DOCUMENT_STATUS) AS DOCUMENT_STATUS,
	UPPER(claim_core.OS_DOCUMENT) AS OS_DOCUMENT,
	CONVERT(VARCHAR(10), claim_core.DECISION_DATE, 103) + ' ' + CONVERT(VARCHAR(8), claim_core.DECISION_DATE, 108) AS DECISION_DATE
FROM
	custport_claim cc
	LEFT JOIN
	(SELECT MAX(claim_history_id) AS claim_history_id, claim_no FROM custport_claim_history GROUP BY claim_no) ch_max
	ON cc.claim_no = ch_max.claim_no
	LEFT JOIN
	custport_claim_history ch
	ON ch_max.claim_history_id = ch.claim_history_id
	LEFT JOIN
	custport_m_status s
	ON cc.status_code = s.status_code
	LEFT JOIN peserta pes 
	ON cc.contract_no = pes.no_kontrak_external
	LEFT JOIN
	custport_m_claim_type ct
	ON cc.claim_type_code = ct.claim_type_code
	LEFT JOIN
	(
		SELECT DISTINCT 
			--RIGHT(KL.ID_KLAIM,10) AS ID_CLAIM
			 KL.ID_KLAIM AS ID_CLAIM
			,PE.NAMA_PESERTA_1 AS CUSTOMER_NAME
			,PE.NO_HP AS MOBILE_NO
			,PE.NO_KONTRAK_EXTERNAL AS CONTRACT_ID
			,PE.KODE_PRODUKSI_EXTERNAL AS PROD_CODE_HCID
			,PE.ID_NASABAH_EXTERNAL AS CUID
			,CONVERT(VARCHAR(10), KL.TGL_PENGAJUAN, 103) + ' ' + CONVERT(VARCHAR(8), KL.TGL_PENGAJUAN, 108)  AS CLAIM_DATETIME
			,JK.DESKRIPSI AS CLAIM_TYPE
			,CONVERT(VARCHAR(10), KL.TGL_KEJADIAN, 103) + ' ' + CONVERT(VARCHAR(8), KL.TGL_KEJADIAN, 108)  AS EVENT_DATE
			,KL.id_status_klaim  AS STATUS_CLAIM_CODE
			,CASE  
					WHEN KL.id_status_klaim='05' AND NTL.approve_date is null THEN 'APPROVED'
					WHEN KL.id_status_klaim='05' AND NTL.STATUS='I' AND NTL.approve_date is not null THEN 'PAID' 
					WHEN KL.id_status_klaim in('01','02','03','04') THEN 'IN PROGRESS'
					WHEN KL.id_status_klaim in('00') AND KL.tgl_dokumen_lengkap is not null THEN 'IN PROGRESS'
					WHEN KL.id_status_klaim in('00') AND KL.tgl_dokumen_lengkap is null THEN 'PENDING DOCUMENT'
					WHEN KL.id_status_klaim in('06') THEN 'REJECT'
					WHEN KL.id_status_klaim in('00') THEN 'REJECT'
					WHEN KL.tgl_dokumen_lengkap is null THEN 'PENDING DOCUMENT'				  				  
					ELSE '' 
				END as STATUS_CLAIM
			,CASE KL.id_status_klaim 
					WHEN '05' THEN CONVERT(VARCHAR(10), KL.APPROVE_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.APPROVE_DATE, 108) 
					WHEN '06' THEN CONVERT(VARCHAR(10), KL.REJECTED_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.REJECTED_DATE, 108)
					WHEN '04' THEN CONVERT(VARCHAR(10), KL.SPECIAL_INVEST_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.SPECIAL_INVEST_DATE, 108)   
					WHEN '03' THEN CONVERT(VARCHAR(10), KL.CANCEL_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.CANCEL_DATE, 108)   
					WHEN '02' THEN CONVERT(VARCHAR(10), KL.VERIFY_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.VERIFY_DATE, 108)   
					ELSE  CONVERT(VARCHAR(10), KL.TGL_PENGAJUAN, 103) + ' ' + CONVERT(VARCHAR(8), KL.TGL_PENGAJUAN, 108)  
			END as STATUS_DATE
			,'0' as CITY_CATEGORY
			,STUFF
				(
					(
					SELECT ',' + M.keterangan
					FROM klaim_dokumen M
					WHERE M.id_klaim = KD.id_klaim and M.valid_ya='0'
					FOR XML PATH('')
				), 1, 1, ''
			) AS REMARK
			,aw.cp_name as CP_NAME,
				aw.cp_phone as CP_PHONE,
				aw.cp_address as CP_ADDRESS,
				CASE aw.id_jenis_relationship 
					WHEN '06' THEN aw.keterangan_relationship 
					ELSE jr.deskripsi 
				END as CP_RELATIONSHIP
			,COALESCE(KL.JML_DIAJUKAN,'',0) as CLAIM_AMOUNT
			,AR.DESKRIPSI as REJECT_REASON
			,STUFF
				(
						(
						SELECT ',' + I.deskripsi
						FROM klaim_dokumen M
						inner join item_dokumen I ON M.id_item_dokumen=I.id_item_dokumen
						WHERE M.id_klaim = KD.id_klaim and M.ada_ya='1'
						FOR XML PATH('')
						), 1, 1, ''
					) AS DOCUMENT_STATUS_HCID
			,STUFF
				(
						(
						SELECT ',' + I.deskripsi
						FROM klaim_dokumen M
						inner join item_dokumen I ON M.id_item_dokumen=I.id_item_dokumen
						WHERE M.id_klaim = KD.id_klaim and M.valid_ya='1'
						FOR XML PATH('')
						), 1, 1, ''
					) AS DOCUMENT_STATUS
			,STUFF
				(
						(
						SELECT ',' + I.deskripsi
						FROM klaim_dokumen M
						inner join item_dokumen I ON M.id_item_dokumen=I.id_item_dokumen
						WHERE M.id_klaim = KD.id_klaim and (M.valid_ya='0' or M.ada_ya='1')
						FOR XML PATH('')
						), 1, 1, ''
					) AS OS_DOCUMENT
			,CASE KL.id_status_klaim 
					WHEN '05' THEN CONVERT(VARCHAR(10), KL.APPROVE_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.APPROVE_DATE, 108) 
					WHEN '06' THEN CONVERT(VARCHAR(10), KL.REJECTED_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.REJECTED_DATE, 108)
					WHEN '04' THEN CONVERT(VARCHAR(10), KL.SPECIAL_INVEST_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.SPECIAL_INVEST_DATE, 108)   
					WHEN '03' THEN CONVERT(VARCHAR(10), KL.CANCEL_DATE, 103) + ' ' + CONVERT(VARCHAR(8), KL.CANCEL_DATE, 108)   
					ELSE '' 
			END as DECISION_DATE
		FROM note_terbit nt 
			INNER join note_lunas ntl on nt.id_note_terbit=ntl.id_note_terbit		
			RIGHT JOIN note_terbit_peserta ntp ON nt.id_note_terbit = ntp.id_note_terbit 
			RIGHT JOIN dbo.klaim KL	ON ntp.id_peserta = KL.id_peserta and kl.id_klaim=ntp.id_klaim	
			RIGHT JOIN peserta PE ON KL.id_peserta = PE.id_peserta
			RIGHT JOIN [jenis_klaim] JK	ON KL.ID_JENIS_KLAIM = JK.ID_JENIS_KLAIM
			RIGHT jOIN alasan_rejected_klaim_hci AR ON KL.id_rejected_klaim_hci=AR.id_rejected_klaim_hci
			RIGHT JOIN KLAIM_DOKUMEN KD ON KL.ID_KLAIM=KD.ID_KLAIM 
			LEFT JOIN dbo.ahli_waris aw ON kl.id_peserta = aw.id_peserta 
			LEFT JOIN dbo.jenis_relationship jr ON aw.id_jenis_relationship = jr.id_jenis_relationship
	) claim_core
	ON cc.id_klaim_core = claim_core.ID_CLAIM



GO


