USE [AJK]
GO

/****** Object:  View [dbo].[view_custport_claim]    Script Date: 31/10/2018 14.57.11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- last modified by Pribadi Pamungkas. 2018-10-31. => register_status ambil dari function
-- created by Pribadi Pamungkas. 2018-07-11
ALTER VIEW [dbo].[view_custport_claim]
AS
	SELECT 
		cc.claim_no,
		cc.no_polis,
		cc.id_peserta,
		cc.contract_no,
		cc.certificate_no,
		cc.customer_name,
		dbo.func_date_format('dd-mmm-YYYY',p.tgl_mulai) + ' - ' + dbo.func_date_format('dd-mmm-YYYY',p.tgl_akhir) AS insurance_periode,
		dbo.func_date_format('dd-mmm-YYYY',p.tgl_lahir_1) AS date_of_birth,
		'IDR' AS currency,	-- masih hardcode
		p.no_identitas AS ktp_id,
		p.uang_pertanggungan AS sum_insured,
		CASE p.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,
		p.no_hp,
		p.email,
		--cc.claim_id,
		cc.claim_no AS register_no,
		dbo.func_date_format('dd-mmm-YYYY',cc.register_date) AS register_date,
		CONVERT(VARCHAR(11), cc.register_date, 106) AS register_date_formatted,
		--cms.description AS register_status,
		dbo.func_custport_get_claim_status(cc.claim_no) AS register_status,
		cmu.fullname AS created_by,
		cmr.description AS reason,
		--REPLACE(CONVERT(VARCHAR(30),cc.event_date,106), ' ', '/') AS event_date_singkat,
		dbo.func_date_format('dd-mmm-YYYY',cc.event_date) AS event_date_panjang,
		CONVERT(VARCHAR(11), cc.event_date, 106) AS event_date_formatted,
		RIGHT('0' + CAST(DAY(cc.event_date) AS VARCHAR),2) + '/' + DATENAME(MONTH,cc.event_date) + '/' + CAST(YEAR(cc.event_date) AS VARCHAR) AS event_date,
		cc.currency_code AS 'claim_currency',
		cc.claim_type_code,
		cmct.description AS claim_type,
		cc.claim_amount,
		cc.notes,
		cc.tmp_guid,
		cmct.is_need_account,
		cc.alt_phone, 
		cc.alt_email, 
		cc.account_no, 
		cc.account_beneficiary, 
		cc.account_currency_code, 
		cc.bank_id, 
		cc.bank_name, 
		cc.bank_code, 
		cc.bank_branch
	FROM
		dbo.custport_claim AS cc 
		INNER JOIN
		dbo.peserta AS p ON cc.no_polis = p.no_polis
							AND cc.id_peserta = p.id_peserta
							--cc.certificate_no = p.no_peserta
							--AND cc.contract_no = p.no_kontrak_external
		INNER JOIN
		dbo.custport_m_status AS cms ON cc.status_code = cms.status_code
		INNER JOIN
		dbo.custport_m_user AS cmu ON cc.created_by = cmu.username 
		LEFT JOIN
		dbo.custport_m_reason AS cmr ON cc.reason_code = cmr.reason_code
		INNER JOIN
		dbo.custport_m_claim_type AS cmct ON cc.claim_type_code = cmct.claim_type_code
	WHERE
		cc.is_active = 1


GO


