-- PROCEDURE: camdecmpswks.check_session_failed(text, text)

-- DROP PROCEDURE camdecmpswks.check_session_failed(text, text);

CREATE OR REPLACE PROCEDURE camdecmpswks.check_session_failed(
	v_chk_session_id text,
	v_error_comment text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE

	errMsg text;
	errState text;
	errContext text;
	p_V_RESULT text;
	p_V_ERROR_MSG text;

BEGIN

				p_V_RESULT   := 'T';
				p_V_ERROR_MSG  := '';

				UPDATE CHECK_SESSION
				SET SEVERITY_CD = 'FATAL',
				SESSION_COMMENT = 'Check Session Failed: ' || COALESCE(V_ERROR_COMMENT, ''),
				SESSION_END_DATE = NOW(),
				UPDATED_STATUS_FLG = 'Y',
				LAST_UPDATED = NOW()
				WHERE CHK_SESSION_ID = V_CHK_SESSION_ID;

				RAISE INFO 'p_V_RESULT,%,p_V_ERROR_MSG,%',p_V_RESULT,p_V_ERROR_MSG;

				EXCEPTION
				WHEN OTHERS THEN
				GET STACKED DIAGNOSTICS
				errState = RETURNED_SQLSTATE,
				errMsg   = MESSAGE_TEXT,
				errContext = PG_EXCEPTION_CONTEXT;
				p_V_ERROR_MSG := 'SQL State: '|| errState || ';Message ' || errMsg || '; Context '|| errContext;
				p_V_RESULT := 'F';
				
END;
$BODY$;
