USE [AJK]
GO
/****** Object:  StoredProcedure [dbo].[proc_custport_claim_history]    Script Date: 13/09/2018 18:59:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- created by Pribadi Pamungkas. 2018-08-27.
ALTER PROCEDURE [dbo].[proc_custport_claim_history]
--DECLARE
	@pi_claim_no VARCHAR(50)-- = 'CL1809000006'
AS
BEGIN

	SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE @approve_reject TABLE ( 
									[claim_history_id] INT,
									[claim_no] VARCHAR(50),
									[date] DATETIME,
									[action_code] VARCHAR(50),
									[action_description] VARCHAR(60),
									[reason_code] VARCHAR(50),
									[reason] VARCHAR(100),
									[notes] VARCHAR(300),
									[create_user] VARCHAR(100),
									[created_by] VARCHAR(15),
									[created_date] DATETIME,
									[is_active] BIT
								  )

	IF EXISTS(SELECT * FROM custport_claim AS cc INNER JOIN klaim AS k ON cc.id_klaim_core = k.id_klaim WHERE cc.claim_no = @pi_claim_no AND k.status = 'I')
		BEGIN
			INSERT INTO @approve_reject ([claim_history_id],[claim_no],[date],[action_code],[action_description],[reason_code],[reason],[notes],[create_user],[created_by],[created_date],[is_active])
			SELECT
				999998,
				@pi_claim_no,
				CASE k.id_status_klaim
					WHEN '05' THEN k.approve_date
					WHEN '06' THEN k.rejected_date
					ELSE NULL
				END,
				'',
				CASE k.id_status_klaim
					WHEN '05' THEN 'Approve'
					WHEN '06' THEN 'Reject'
					ELSE NULL
				END,
				NULL,
				'',
				k.approve_note,
				'Equity Life Indonesia',
				'SYS',
				create_date,
				1
			FROM
				custport_claim AS cc INNER JOIN
				klaim AS k ON cc.id_klaim_core = k.id_klaim
			WHERE
				cc.claim_no = @pi_claim_no
				AND k.status = 'I'
		END

	SELECT
		x.*
	FROM
		(
			SELECT 
				cch.[claim_history_id],
				cch.[claim_no],
				cch.[date],
				cch.[action_code],
				cch.[action_description],
				cch.[reason_code],
				cmr.description AS reason,
				cch.[notes],
				cmu.fullname AS create_user,
				cch.[created_by],
				cch.[created_date],
				cch.[is_active]
			FROM 
				[dbo].[custport_claim_history] AS cch LEFT JOIN
				[dbo].[custport_m_user] AS cmu ON cch.created_by = cmu.username LEFT JOIN
				[dbo].[custport_m_reason] AS cmr ON cch.reason_code = cmr.reason_code
			WHERE
				cch.[is_active] = 1
				--AND cmu.is_active = 1
				AND cch.[claim_no] = @pi_claim_no
			--ORDER BY
			--	date

			UNION ALL

			-- approve
			--SELECT
			--	999998 AS [claim_history_id],
			--	cc.claim_no,
			--	nt.approve_date AS [date],
			--	'' AS [action_code],
			--	'Approve' AS [action_description],
			--	NULL [reason_code],
			--	'' AS reason,
			--	nt.approve_note AS [notes],
			--	'Equity Life Indonesia' AS create_user,
			--	'SYS' AS [created_by],
			--	nt.create_date AS [created_date],
			--	1 AS [is_active]
			--FROM
			--	custport_claim AS cc INNER JOIN
			--	klaim AS k ON cc.id_klaim_core = k.id_klaim INNER JOIN
			--	note_terbit_peserta AS ntp ON k.id_klaim = ntp.id_klaim INNER JOIN
			--	note_terbit AS nt ON ntp.id_note_terbit = nt.id_note_terbit 
			--WHERE
			--	cc.claim_no = @pi_claim_no
			--	AND k.status = 'I'
			--	AND nt.status = 'I'
			--	AND ntp.status = 'I'
			SELECT 
				[claim_history_id],[claim_no],[date],[action_code],[action_description],[reason_code],[reason],[notes],[create_user],[created_by],[created_date],[is_active] 
			FROM 
				@approve_reject

			UNION ALL

			-- pembayaran
			SELECT
				999999 AS [claim_history_id],
				cc.claim_no,
				nl.tgl_lunas AS [date],
				'' AS [action_code],
				'Paid' AS [action_description],
				NULL [reason_code],
				'' AS reason,
				'' AS [notes],
				'Equity Life Indonesia' AS create_user,
				'SYS' AS [created_by],
				nl.create_date AS [created_date],
				1 AS [is_active]
			FROM
				custport_claim AS cc INNER JOIN
				klaim AS k ON cc.id_klaim_core = k.id_klaim INNER JOIN
				note_terbit_peserta AS ntp ON k.id_klaim = ntp.id_klaim INNER JOIN
				note_terbit AS nt ON ntp.id_note_terbit = nt.id_note_terbit INNER JOIN
				note_lunas AS nl ON nt.id_note_terbit = nl.id_note_terbit
			WHERE
				cc.claim_no = @pi_claim_no
				AND k.status = 'I'
				AND nt.status = 'I'
				AND ntp.status = 'I'
				AND nl.status = 'I' -- ?
		) AS x
	ORDER BY
		--x.date
		x.[claim_history_id]

END

