SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2018-07-20
ALTER PROCEDURE dbo.proc_custport_app_action_claim
	@pi_claim_no VARCHAR(20),
	@pi_action_code VARCHAR(50),
	@pi_reason_code VARCHAR(50) = NULL,
	@pi_notes VARCHAR(300),
	@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.


	IF EXISTS(SELECT 1 FROM dbo.custport_claim WHERE claim_no = @pi_claim_no AND is_active = 1)
		BEGIN

			DECLARE 
				@p_curr_date DATETIME = GETDATE()

			BEGIN TRAN

				DECLARE
					@p_is_external_process BIT,
					@p_next_flow_stage VARCHAR(50),
					@p_next_status_code VARCHAR(50),
					@p_flow_action_description VARCHAR(150)

				SELECT
					@p_is_external_process = is_external_process,
					@p_next_flow_stage = next_flow_stage,
					@p_next_status_code = next_status_code,
					@p_flow_action_description = flow_action_description
				FROM
					dbo.view_custport_flow
				WHERE
					flow_action_code = @pi_action_code
				IF (@@ERROR <> 0) OR (@@RowCount < 1)	-- jika ada error atau tidak ada row
					BEGIN
						ROLLBACK TRAN
						SET @po_message = 'Action tidak dikenal'
						SELECT @po_message AS message
						RETURN -1
					END

				IF @p_is_external_process=1	-- proses ke core
					BEGIN
						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
							BEGIN
								DECLARE 
									@p_id_klaim varchar(12)
		
								-- 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
										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
												,@pi_user_id	-- create_by
												,@p_curr_date
												,@pi_user_id	-- 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
										ROLLBACK TRAN
										SET @po_message = 'Failed inserting claim to core'
										RETURN -1
									END

								-- update tabel claim
								UPDATE
									dbo.custport_claim
								SET
									status_code = @p_next_status_code,
									flow_stage_code = @p_next_flow_stage,
									reason_code = NULL,	--@pi_reason_code
									id_klaim_core = @p_id_klaim
								WHERE
									claim_no = @pi_claim_no
								IF (@@Error <> 0)
									BEGIN
										ROLLBACK TRAN
										SET @po_message = 'Error updating status'
										RETURN -1
									END

								SET @po_message = 'Klaim berhasil diinput dengan Id Klaim: ' + @p_id_klaim
							END

						---
					END
				ELSE	-- proses bukan ke core (cukup di portal aja)
					BEGIN
						-- update tabel claim
						UPDATE
							dbo.custport_claim
						SET
							status_code = @p_next_status_code,
							flow_stage_code = @p_next_flow_stage,
							reason_code = @pi_reason_code
						WHERE
							claim_no = @pi_claim_no
						IF (@@Error <> 0)
							BEGIN
								ROLLBACK TRAN
								SET @po_message = 'Error updating status'
								SELECT @po_message AS message
								RETURN -1
							END

						SET @po_message = 'Status berhasil diubah'
					END

				-- insert history
				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
							,@p_curr_date
							,@pi_action_code
							,LEFT(@p_flow_action_description,60)
							,@pi_reason_code
							,@pi_notes
							,@pi_user_id
							,@p_curr_date
							,@pi_user_id
							,@p_curr_date
							,1)
				IF (@@Error <> 0)
					BEGIN
						ROLLBACK TRAN
						SET @po_message = 'Error inserting history'
						SELECT @po_message AS message
						RETURN -1
					END

			COMMIT TRAN
	
			SELECT @po_message AS message
			RETURN 0

		END
	ELSE
		BEGIN
			SET @po_message = @pi_claim_no + ' tidak ditemukan'
			SELECT @po_message AS message
			RETURN -1
		END

END
GO
