pears.AuditLog

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
}