====== pears.AuditLog ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."AuditLog"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "sAuditType" char(10),in "sRecordID" char(20),in "sDescription" long varchar,in "sOldValues" long varchar,in "sNewValues" long varchar ) 
begin
  declare "pk" char(20);
  declare "username" char(30);
  declare "ConnNum" integer;
  declare "fn" char(250);
  declare "fnx" char(30);
  begin
    if "sOldValues" = "sNewValues" then return
    end if;
    set "pk" = "uniquekey"('')
  exception
    when others then create variable "uniqueroll" smallint;
      set "pk" = "uniquekey"('')
  end;
  begin
    if "userstaffid" = 'NOAUDIT' then return
    end if;
    select "userid" into "username" from "staff" where "staffid" = "userstaffid"
  exception
    when others then set "username" = "connection_property"('Name')
  end;
  set "ConnNum" = "connection_property"('Number');
  insert into "audittrail"( "audittrailid","eventdatetime","userid","audittype","recordid","description","oldvalues","newvalues","ConnectionNumber" ) values
    ( "pk",current timestamp,"username","saudittype","srecordid","sdescription","soldvalues","snewvalues","ConnNum" ) ;
  if("sAuditType" = 'JOB') or("sAuditType" = 'REPORT') then
    set "fn" = "lcase"("REGEXP_SUBSTR"("sDescription",'[^\\\\]+$'));
    set "fnx" = "REGEXP_SUBSTR"("fn",'\\([[:digit:]]+\\)\\.(xml|rtm)$');
    if "fnx" is not null then
      set "fn" = "replace"("fn","fnx","right"("fn",4))
    end if;
    update "report" set "lastused" = current timestamp,"usecount" = "isnull"("usecount",0)+1,"lastusedby" = "username" where "stuff"("filename",1,"locate"("filename",'\\',-1),'') = "fn"
  end if
exception
  when others then return
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AuditLog" IS 
{create procedure AuditLog 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in sAuditType char(10),in sRecordID char(20),in sDescription long varchar,in sOldValues long varchar,in sNewValues long varchar)
begin
  declare pk char(20);
  declare username char(30);
  declare ConnNum integer;
  declare fn char(250);
  declare fnx char(30);
  begin
    if sOldValues = sNewValues then return
    end if;
    set pk=uniquekey('')
  exception
    when others then create variable uniqueroll smallint;
      set pk=uniquekey('')
  end;
  begin
    if userstaffid = 'NOAUDIT' then return
    end if;
    select userid into username from staff where staffid = userstaffid
  exception
    when others then set username=connection_property('Name')
  end;
  set ConnNum=connection_property('Number');
  insert into audittrail(audittrailid,eventdatetime,userid,audittype,recordid,description,oldvalues,newvalues,ConnectionNumber) values(
    pk,current timestamp,username,saudittype,srecordid,sdescription,soldvalues,snewvalues,ConnNum);
  if (sAuditType = 'JOB') or (sAuditType = 'REPORT') then 
    set fn=lcase(REGEXP_SUBSTR(sDescription,'[^\\\\]+$'));
    set fnx=REGEXP_SUBSTR(fn,'\([[:digit:]]+\)\.(xml|rtm)$');
    if fnx is not null then
        set fn=replace(fn,fnx,right(fn,4));
    end if;
    update report set lastused = current timestamp, usecount = isnull(usecount,0) + 1, lastusedby = username where stuff(filename,1, locate(filename,'\\',-1),'') = fn;
  end if
exception
  when others then return
end
}
</code>