USE [AJK]
GO

/****** Object:  StoredProcedure [dbo].[proc_custport_set_cancel_claim]    Script Date: 16/07/2018 9:42:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- created by Pribadi Pamungkas. 2018-07-16
CREATE PROCEDURE [dbo].[proc_custport_process_claim]
	@pi_claim_no VARCHAR(20),
	@pi_user_id VARCHAR(20),
	@po_message VARCHAR(100) OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE
		@p_no_polis varchar(13),
		@p_id_peserta varchar(12),
		@p_certificate_no varchar(20),
		@p_contract_no varchar(20),
		@p_customer_name varchar(100),
		@p_register_date date,
		@p_event_date date,
		@p_claim_type_code varchar(50),
		@p_claim_core_code varchar(1),
		@p_currency_code varchar(50),
		@p_claim_amount decimal(12,2),
		@p_reference_no varchar(20),
		@p_status_code varchar(50),
		@p_flow_stage_code varchar(50),
		@p_reason_code varchar(50),
		@p_notes varchar(600),
		@p_company_code varchar(10),
		@p_company_region_code varchar(10),
		@p_tmp_guid varchar(50),
		@p_created_by varchar(15),
		@p_created_date datetime,
		@p_modified_by varchar(15),
		@p_modified_date datetime

	SELECT
		@p_no_polis = cc.no_polis,
		@p_id_peserta = cc.id_peserta,
		@p_certificate_no = cc.certificate_no,
		@p_contract_no = cc.contract_no,
		@p_customer_name = cc.customer_name,
		@p_register_date = cc.register_date,
		@p_event_date = cc.event_date,
		@p_claim_type_code = cc.claim_type_code,
		@p_claim_core_code = cmct.claim_core_code,
		@p_currency_code = cc.currency_code,
		@p_claim_amount = cc.claim_amount,
		@p_reference_no = cc.reference_no,
		@p_status_code = cc.status_code,
		@p_flow_stage_code = cc.flow_stage_code,
		@p_reason_code = cc.reason_code,
		@p_notes = cc.notes,
		@p_company_code = cc.company_code,
		@p_company_region_code = cc.company_region_code,
		@p_tmp_guid = cc.tmp_guid,
		@p_created_by = cc.created_by,
		@p_created_date = cc.created_date,
		@p_modified_by = cc.modified_by,
		@p_modified_date = cc.modified_date
	FROM 
		[dbo].[custport_claim] AS cc INNER JOIN
		[dbo].[custport_m_claim_type] AS cmct ON cc.claim_type_code = cmct.claim_type_code
	WHERE
		cc.claim_no = @pi_claim_no 
		AND cc.is_active = 1
		AND cmct.is_active = 1
	IF (@@ERROR = 0) AND (@@RowCount > 0)	-- jika tidak ada error dan ada row
	--IF EXISTS(SELECT 1 FROM dbo.custport_claim WHERE claim_no = @pi_claim_no AND is_active = 1)
		BEGIN
			BEGIN TRAN

			DECLARE 
				@p_curr_date datetime,
				@p_id_klaim varchar(12),
				@p_user varchar(4)

			SET @p_curr_date      = GETDATE()
			SET @p_user           = 'SYS'
		
			-- GET id_peserta
			EXEC AJK.dbo.gproc_GetNo
				@Tgl = @p_curr_date,
				@sField = 'id_klaim',
				@CurrNo = @p_id_klaim OUTPUT
			IF (@@Error <> 0 OR COALESCE(@p_id_klaim,'')='')
				BEGIN
					-- Gagal
					ROLLBACK TRAN
					SET @po_message = 'Error generating id_klaim'
					RETURN -1
				END
			INSERT INTO [dbo].[klaim]
							([id_klaim]
							,[tgl_pengajuan]
							,[tgl_kejadian]
							,[id_peserta]
							,[id_diagnosa]
							,[jml_uang_pertanggungan]
							,[jml_bayar]
							,[jml_tolakan]
							,[jml_penyesuaian]
							,[total_dibayar]
							,[catatan]
							,[status]
							,[create_date]
							,[create_by]
							,[change_date]
							,[change_by]
							,[id_jenis_klaim]
							,[id_status_klaim]
							,[id_kelompok_kota]
							,[keterangan]
							,[jml_diajukan]
							,[no_polis]
							,[coverage_installment]
							,[id_rejected_klaim_hci])
						VALUES
							(@p_id_klaim
							,CAST(@p_register_date AS DATETIME)
							,CAST(@p_event_date AS DATETIME)
							,@p_id_peserta
							,'99'
							,0	-- jml_uang_pertanggungan
							,0	-- jml_bayar
							,0
							,0
							,0	-- total_dibayar
							,LEFT(@p_notes, 100)
							,'G'
							,@p_curr_date
							,@p_user	-- create_by
							,@p_curr_date
							,@p_user	-- change_by
							,@p_claim_core_code
							,'01'	-- Draft
							,'9'
							,LEFT(@p_notes, 100)
							,CAST(@p_claim_amount AS FLOAT)
							,@p_no_polis
							,0
							,'00')
			IF (@@Error <> 0)
				BEGIN
					-- Gagal
					ROLLBACK TRAN
					SET @po_message = 'Failed inserting claim'
					RETURN -1
				END

			-- ubah status
			UPDATE
				dbo.custport_claim
			SET
				status_code = 'PRC',
				flow_stage_code = 'STG1800004',
				reason_code = NULL	--@pi_reason_code
			WHERE
				claim_no = @pi_claim_no
			IF (@@Error <> 0)
				BEGIN
					-- Gagal
					ROLLBACK TRAN
					SET @po_message = 'Error updating status'
					RETURN -1
				END

			-- simpan history (masih hardcode)
			INSERT INTO [dbo].[custport_claim_history]
						([claim_no]
						,[date]
						,[action_code]
						,[action_description]
						,[reason_code]
						,[notes]
						,[created_by]
						,[created_date]
						,[modified_by]
						,[modified_date]
						,[is_active])
					VALUES
						(@pi_claim_no
						,GETDATE()
						,'ACT00003'
						,'Process Claim'
						,NULL	-- @pi_reason_code
						,NULL	-- @pi_notes
						,@pi_user_id
						,GETDATE()
						,@pi_user_id
						,GETDATE()
						,1)
			IF (@@Error <> 0)
				BEGIN
					-- Gagal
					ROLLBACK TRAN
					SET @po_message = 'Error insert history'
					RETURN -1
				END



			COMMIT TRAN
	
			SET @po_message = 'Klaim berhasil diinput dengan Id Klaim: ' + @p_id_klaim
			RETURN 0

		END
	ELSE
		BEGIN
			SET @po_message = 'Not found'
			RETURN -1
		END

END


GO


