CREATE OR REPLACE FUNCTION pg_temp.exp_Query(INOUT Error_message character varying DEFAULT NULL::character varying)
--            RETURNS record
            LANGUAGE 'plpgsql'
            AS $$
            DECLARE
                p_date timestamp;
                p_FIS BIGINT=NULL;
                p_a BIGINT;
								p_err BIGINT = 0;
								p_zzz BIGINT = 0;
            BEGIN
                Error_message := '1';
                SELECT fis.FRMRIntegrationSession_id,
                       fis.ServiceListLog_id
                INTO p_FIS,
                     p_a
                from dbo.FRMRIntegrationSession fis
                     INNER JOIN stg.ServiceListLog ON ServiceListLog.ServiceListLog_id = fis.ServiceListLog_id
                     INNER JOIN stg.ServiceList ON ServiceList.ServiceList_id = ServiceListLog.ServiceList_id
                WHERE ServiceListLog.ServiceListResult_id = 2 AND
                      ServiceList_Code = 20
                ORDER BY fis.FRMRIntegrationSession_updDT
                LIMIT 1;

                SELECT DATEADD('minute', 3, coalesce(FRMRSessionHist_updDT, '2011-05-13 11:48:58.703'))
                INTO p_date
                from dbo.FRMRIntegrationSession fis
                     LEFT JOIN LATERAL
                     (
                       SELECT *
                       from FRMRSessionHist
                       WHERE FRMRIntegrationSession_id = fis.FRMRIntegrationSession_id
                       ORDER BY FRMRSessionHist.FRMRSessionHist_updDT DESC
                       LIMIT 1
                     ) as hist on true
                LIMIT 1;

                IF p_date < dbo.tzGetDate() THEN
                    SELECT COUNT(*) INTO p_err FROM dbo.FRMRSessionHist
                    WHERE FRMRIntegrationSession_id = p_FIS AND (FRMRSessionHist_AnswerXML IS NULL or FRMRSessionHist_AnswerXML='process');
                    IF p_err !=0 THEN
                        UPDATE stg.ServiceListLog SET ServiceListResult_id=3, ServiceListLog_endDT=dbo.tzGetDate() WHERE ServiceListLog_id = p_a;
                    end IF;

                    p_zzz=0;
                    SELECT COUNT(*) INTO p_zzz FROM dbo.FRMRSessionHist
                    WHERE FRMRIntegrationSession_id = p_FIS AND FRMRSessionHist_isError=2;

                    if p_zzz =0 THEN
                            UPDATE stg.ServiceListLog SET ServiceListResult_id=1, ServiceListLog_endDT=dbo.tzGetDate() WHERE ServiceListLog_id = p_a;
                    ELSE
                            UPDATE stg.ServiceListLog SET ServiceListResult_id=3, ServiceListLog_endDT=dbo.tzGetDate() WHERE ServiceListLog_id = p_a;
                    END IF;
                end if;
				EXCEPTION
					when others then Error_message:=SQLERRM;
            END;
            $$
