USE [AJK]
GO

/****** Object:  View [dbo].[view_custport_app_list_claim]    Script Date: 20/07/2018 16:10:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- created by Pribadi Pamungkas. 2018-07-11
ALTER VIEW [dbo].[view_custport_app_list_claim]
AS
	SELECT
		cms.app_status AS status,	
		cc.claim_no AS register_no,
		cc.no_polis, 
		cc.id_peserta, 
		cc.customer_name, 
		CAST(cc.register_date AS DATETIME) AS register_date, 
		CAST(cc.event_date AS DATETIME) AS event_date, 
		cc.claim_type_code, 
		cmct.description AS claim_type,
		cc.currency_code, -- mata uang klaim
		cc.claim_amount, 
		cc.reference_no, 
		cc.status_code, 
		cc.flow_stage_code, 
		cc.reason_code, 
		cc.notes, 
		cc.certificate_no, 
		cc.contract_no,
		cc.company_code, 
		cc.company_region_code, 
		cc.id_klaim_core,
		cc.created_by, 
		cc.created_date, 
		cc.modified_by, 
		cc.modified_date,
		cc.claim_id,
		cms.description AS claim_status,
		cc.claim_no,
		pst.tgl_lahir_1 AS tgl_lahir,
		pst.no_identitas,
		pst.tgl_mulai,
		pst.tgl_akhir,
		pst.uang_pertanggungan,
		pst.id_valuta,	-- mata uang peserta (polis)
		CASE pst.status
			WHEN 'I' THEN 'Active'
			WHEN 'C' THEN 'Cancel'
			WHEN 'D' THEN 'Death'
			WHEN 'E' THEN 'Verifikasi'
			WHEN 'G' THEN 'Draft'
			WHEN 'M' THEN 'Maturity'
			WHEN 'O' THEN 'Keluar'
			WHEN 'R' THEN 'Reject'
			WHEN 'L' THEN 'Terminate'	-- ?
			ELSE 'Unknown'
		END AS participant_status,
		cmr.description AS reason,
		cmu.fullname AS created_user
	FROM
		dbo.custport_claim AS cc INNER JOIN
		dbo.custport_m_status AS cms ON cc.status_code = cms.status_code LEFT JOIN
		dbo.custport_m_claim_type AS cmct ON cc.claim_type_code = cmct.claim_type_code INNER JOIN
		dbo.peserta AS pst ON cc.no_polis = pst.no_polis
							  AND cc.id_peserta = pst.id_peserta LEFT JOIN
		dbo.custport_m_reason AS cmr ON cc.reason_code = cmr.reason_code INNER JOIN
		dbo.custport_m_user AS cmu ON cc.created_by = cmu.username
	WHERE
		(cc.is_active = 1)
		AND (cms.is_active = 1)
		AND (cmct.is_active = 1)
		AND (cc.status_code NOT IN ('DRF','CNC'))





GO


