Updations done to introduce followUpStep in errorTypes for avni-integration-service

avni-int-service-error-type-follow-up-step.sql

-- List Process errorTypes
select *
from error_type
where integration_system_id = 2
  and name in (
               'DemandDeletionFailure',
               'DispatchDeletionFailure',
               'DispatchLineItemsDeletionFailure',
               'UpdateDispatchReceiptError',
               'DeleteEntityError',
               'AddressNotFoundError',
               'EntityIsDeleted'
    );

-- Update follow_up_step for Process errorTypes
update error_type
set follow_up_step = '1'
where name in (
               'DemandDeletionFailure',
               'DispatchDeletionFailure',
               'DispatchLineItemsDeletionFailure',
               'UpdateDispatchReceiptError',
               'DeleteEntityError',
               'AddressNotFoundError'
    );

-- List deprecated errorTypes
select *
from error_type
where integration_system_id = 2
  and name in (
               'NoDemandWithId',
               'NoDispatchWithId',
               'DemandIdChanged',
               'DispatchIdChanged',
               'SubjectIdChanged',
               'MultipleSubjectsWithId',
               'SubjectIdNull',
               'ErroredAvniEncounter',
               'ImplementationInventoryIdChanged',
               'NoSubjectWithId',
               'NoImplementationInventoryWithId'
    );

-- List deprecated errorTypes error_record
select *
from error_record er
         join error_record_log erl on er.id = erl.error_record_id
         join error_type et on erl.error_type_id = et.id
where
--       et.integration_system_id = 2 and
et.name in (
            'NoDemandWithId',
            'NoDispatchWithId',
            'DemandIdChanged',
            'DispatchIdChanged',
            'SubjectIdChanged',
            'MultipleSubjectsWithId',
            'SubjectIdNull',
            'ErroredAvniEncounter',
            'ImplementationInventoryIdChanged',
            'NoSubjectWithId',
            'NoImplementationInventoryWithId'
    );


-- Delete error_record_log that are deprecated error_type
delete
from error_record_log
    using error_record_log erl
        join error_type et on erl.error_type_id = et.id
where et.integration_system_id = 2
  and et.name in (
                  'NoDemandWithId',
                  'NoDispatchWithId',
                  'DemandIdChanged',
                  'DispatchIdChanged',
                  'SubjectIdChanged',
                  'MultipleSubjectsWithId',
                  'SubjectIdNull',
                  'ErroredAvniEncounter',
                  'ImplementationInventoryIdChanged',
                  'NoSubjectWithId',
                  'NoImplementationInventoryWithId'
    );

-- Delete error_record that are deprecated error_type

delete
from error_record
    using error_record er join error_record_log erl on er.id = erl.error_record_id
        join error_type et on erl.error_type_id = et.id
where et.integration_system_id = 2
  and et.name in (
                  'NoDemandWithId',
                  'NoDispatchWithId',
                  'DemandIdChanged',
                  'DispatchIdChanged',
                  'SubjectIdChanged',
                  'MultipleSubjectsWithId',
                  'SubjectIdNull',
                  'ErroredAvniEncounter',
                  'ImplementationInventoryIdChanged',
                  'NoSubjectWithId',
                  'NoImplementationInventoryWithId'
    );


-- Delete error_type that are deprecated error_type
delete
from error_type
where integration_system_id = 2
  and name in (
               'NoDemandWithId',
               'NoDispatchWithId',
               'DemandIdChanged',
               'DispatchIdChanged',
               'SubjectIdChanged',
               'MultipleSubjectsWithId',
               'SubjectIdNull',
               'ErroredAvniEncounter',
               'ImplementationInventoryIdChanged',
               'NoSubjectWithId',
               'NoImplementationInventoryWithId'
    );

-- List Internal errorTypes
select *
from error_type
where integration_system_id = 2
  and name in (
               'BadValueForRestrictedPicklist',
               'MustNotHave2SimilarElements',
               'FieldCustomValidationException',
               'AnswerMappingIsNull',
               'TargetCommunityIsNullError');

-- Update error_type to mark these as Internal error_type

update error_type
set follow_up_step = 3
where integration_system_id = 2
  and name in (
               'BadValueForRestrictedPicklist',
               'MustNotHave2SimilarElements',
               'FieldCustomValidationException',
               'AnswerMappingIsNull',
               'TargetCommunityIsNullError');

-- List terminal errorTypes
select *
from error_type
where integration_system_id = 2
  and name in (
               'DemandAttributesMismatch',
               'DispatchAttributesMismatch',
               'DispatchReceiptAttributesMismatch',
               'DistributionAttributesMismatch',
               'ActivityAttributesMismatch',
               'ImplementationInventoryAttributesMismatch',
               'ClassCastException',
               'UnclassifiedError'
    );

-- Update error_type to mark these as Terminal error_type
update error_type
set follow_up_step = 2
where integration_system_id = 2
  and name in (
               'DemandAttributesMismatch',
               'DispatchAttributesMismatch',
               'DispatchReceiptAttributesMismatch',
               'DistributionAttributesMismatch',
               'ActivityAttributesMismatch',
               'ImplementationInventoryAttributesMismatch',
               'ClassCastException',
               'UnclassifiedError'
    );

-- List  count of follow_up_step type in error_type
select follow_up_step, count(*)
from error_type
where integration_system_id = 2
group by follow_up_step;
-- "1","7"
-- "2","8"
-- "3","5"

-- -- Create integrating_entity_status for GoonjErrorRecordLog
-- INSERT INTO public.integrating_entity_status (id, entity_type, read_upto_numeric, read_upto_date_time,
--                                               integration_system_id, uuid, is_voided)
-- VALUES (DEFAULT, 'GoonjErrorRecordLog'::varchar(100), null::integer, '2023-09-22 00:00:00.000000'::timestamp,
--         2::integer, uuid_generate_v4(), false::boolean);


-- -- Command to get count of error_record_log with specified filters
-- select count(erl.*)
-- from error_record_log erl
--          join error_type et on erl.error_type_id = et.id
--          join error_record er on erl.error_record_id = er.id
-- where erl.logged_at >= :startDate
--   and erl.logged_at <= :endDate
--   and et.follow_up_step = :errorTypeFollowUpStep
--   and er.integration_system_id = :integrationSystemId