CREATE OR REPLACE FUNCTION strans.TASK001785639( state_ep numeric(38,0)
                                               , emp_ep numeric(38,0)
                                               , start_date timestamp without time zone
                                               , end_date timestamp without time zone)
    returns numeric(38,0)
    language plpgsql
AS
$$
DECLARE
    reg_count numeric(38,0);
BEGIN
    select count(distinct record_guid || reqte_endpoint_id || reqtde_endpoint_id) 
    into STRICT reg_count
    from sep_info_report_extract sire
    where sire.req_VALID_IND=1 and sire.Reqt_FAILED_IND != '1'
      and (sire.Reqxt_FAILED_IND is null or sire.Reqxt_FAILED_IND != '1')
      and (sire.res_VALID_IND=1 and sire.Rest_FAILED_IND != '1'
           or sire.res_VALID_IND is null and sire.Rest_FAILED_IND is null)
      and (sire.Resxt_FAILED_IND is null or sire.Resxt_FAILED_IND != '1')
      and sire.Reqt_Processing_Completed_Dts between start_date and (end_date + interval '1' day - interval '1' second)
      and sire.Reqtde_ORGANIZATION_TYPE != 'SEW_EMPLOYER'
      and sire.Reqte_Endpoint_Id = state_ep
      and sire.Reqtde_Endpoint_Id = emp_ep
      and (sire.rest_processing_completed_dts is not null
           or sire.response_due_date < date_trunc('day', clock_timestamp() at time zone 'America/New_York'))
      and not exists (
        SELECT * 
        from sep_info_report_extract sire2
        where sire2.req_VALID_IND=1 and sire2.Reqt_FAILED_IND != '1'
          and (sire2.Reqxt_FAILED_IND is null or sire2.Reqxt_FAILED_IND != '1')
          and sire2.res_VALID_IND=1 and sire2.Rest_FAILED_IND != '1'
          and sire2.separation_request_id != sire.separation_request_id
          and sire2.record_guid = sire.record_guid
          and sire2.Reqte_Endpoint_Id=sire.reqte_endpoint_id
          and sire2.reqtde_endpoint_id=sire.reqtde_endpoint_id
          and sire2.Reqt_Processing_Completed_Dts < sire.Reqt_Processing_Completed_Dts
          and sire2.Reqt_Processing_Completed_Dts between start_date - interval '35' day 
                                                    and (end_date + interval '1' day - interval '1' second)
          and sire2.Reqtde_ORGANIZATION_TYPE != 'SEW_EMPLOYER'
          and sire2.Rest_Processing_Completed_Dts is not null);
    return reg_count;
END;
$$