--ABSN-7387
CREATE OR REPLACE PROCEDURE updateemailsettingapp IS
  lv_count   NUMBER;
  gv_message abslog.message%type;
  procedure write_log(fp_priority in number, fp_message in varchar2) is
  begin
    /* Logging: write message in ABSLOG with these priorities:
    1 = Message
    2 = Warning
    3 = Error */
    abslogwrite.abslog_write(5,
                             'updateemailsettingapp',
                             null,
                             fp_priority,
                             fp_message);
    begin
      dbms_output.put_line(fp_message);
    exception
      when others then
        null; --ok no output does not matter
    end;
  end write_log;
BEGIN
  FOR x IN (
    SELECT esa.documenttype, esa.sendingapplication
      FROM emailsettingapp esa
     WHERE esa.documenttype IN (39, 40, 41)
       AND esa.sendingapplication = 0
  ) LOOP
    gv_message := 'check for records in emailsetting table with sendingapplication = 0';
    SELECT COUNT(1)
      INTO lv_count
      FROM emailsetting s
     WHERE s.documenttype = x.documenttype
       AND s.sendingapplication = 0;

    IF lv_count > 0 THEN
      gv_message := 'insert for emailsettingapp with sendingapplication = 1';
      INSERT INTO emailsettingapp (documenttype, sendingapplication, systemuser_id, timestamp)
      VALUES (x.documenttype, 1, 1, SYSDATE);

      gv_message := 'update emailsetting: sendingapplication = 1';
      UPDATE emailsetting es
         SET es.sendingapplication = 1, es.timestamp = sysdate, es.systemuser_id = 1
       WHERE es.documenttype = x.documenttype;

      gv_message := 'delete old record from emailsettingapp with sendingapplication = 0';
      DELETE FROM emailsettingapp ea
       WHERE ea.documenttype = x.documenttype
         AND ea.sendingapplication = x.sendingapplication;

      COMMIT;
	ELSE
       gv_message := 'update emailsetting: sendingapplication = 1';
       UPDATE emailsetting es
         SET es.sendingapplication = 1, es.timestamp = sysdate, es.systemuser_id = 1
       WHERE es.documenttype = x.documenttype;
       COMMIT;
    END IF;

    lv_count := 0;
  END LOOP;
 exception
  when others then
    begin
      rollback;
      write_log(3, 'Error updateemailsettingapp at ' || gv_message || sqlerrm);
 end;
END updateemailsettingapp;
/
execute updateemailsettingapp;
drop procedure updateemailsettingapp;
