pears.NetQuestionAudit

CREATE PROCEDURE "pears"."NetQuestionAudit"( 
  IN "pTagLocation" CHAR(3),IN "pTagID" CHAR(3),IN "pID" CHAR(20),IN "pOldValue" long VARCHAR ) 
BEGIN
  DECLARE "iAudit" SMALLINT;
  DECLARE "NewValue" long VARCHAR;
  DECLARE "stagdescrip" CHAR(100);
  DECLARE "sname" CHAR(100);
  DECLARE "stype" CHAR(12);
  SELECT "description","auditflag" INTO "stagdescrip","iAudit" FROM "tag" WHERE "taglocation" = "pTagLocation" AND "tagid" = "pTagID";
  IF "trim"("isnull"("iAudit",0)) = 0 THEN
    RETURN
  END IF;
  SET "NewValue" = "getquestanswer"("pTagLocation","pTagID","pID");
  CASE
  WHEN "pTagLocation" = 'C' THEN
    SET "stype" = 'COMPANY';
    SELECT "name" INTO "sname" FROM "company" WHERE "companyid" = "pID"
  WHEN "pTagLocation" = 'P' OR "pTagLocation" LIKE 'A%' THEN
    SET "stype" = 'PERSON';
    SELECT "name" INTO "sname" FROM "person" WHERE "personid" = "pID"
  WHEN "pTagLocation" = 'E' THEN
    SET "stype" = 'COMPANY';
    SELECT "company"."companyid","string"("person"."name",', ',"company"."name") INTO "pID","sname" FROM "employment" KEY JOIN("person","company") WHERE "employment"."employmentid" = "pID"
  WHEN "pTagLocation" LIKE 'V%' THEN
    SET "stype" = 'VACANCY';
    SELECT "string"("Company"."Name",' - ',"vacancy"."position",'(',"vacancy"."RefCode",')') INTO "sname" FROM "vacancy" KEY JOIN "employment" KEY JOIN "Company" WHERE "vacancy"."vacancyid" = "pID"
  WHEN "pTagLocation" LIKE 'L%' THEN
    SET "stype" = 'PLACEMENT';
    SELECT "string"("Company"."Name",' - ',"employment"."position") INTO "sname" FROM "placement" KEY JOIN "employment" KEY JOIN "Company" WHERE "placement"."placementid" = "pID"
  WHEN "pTagLocation" LIKE 'S%' THEN
    SET "stype" = 'SHIFTPLAN'
  WHEN "pTagLocation" LIKE 'T%' THEN
    SET "stype" = 'TIMESHEET';
    SELECT
      "isnull"(
      (SELECT FIRST IF "c"."Name" IS NOT NULL THEN
          "string"("t"."SerialNumber",': ',"p"."Name",' as ',"v"."Position",' at ',"c"."Name")
        ELSE "string"("t"."SerialNumber",': ',"p"."Name",' - Internal ')
        endif FROM "TempTimeSheet" AS "t" KEY JOIN "Person" AS "p","TempTimeSheet" AS "t" KEY LEFT OUTER JOIN("Placement" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company" AS "c") WHERE "t"."TempTimeSheetID" = "pID"),
      (SELECT FIRST "string"("t"."SerialNumber",'(P): ',"p"."Name",' as ',"v"."Position",' at ',"c"."Name") FROM "TempProvTimeSheet" AS "t" KEY LEFT OUTER JOIN "Person" AS "p","TempProvTimeSheet" AS "t"
          KEY LEFT OUTER JOIN("Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company" AS "c") WHERE "t"."TempProvTimeSheetID" = "pID"))
      INTO "sname"
  WHEN "pTagLocation" LIKE 'N%' THEN
    SET "stype" = 'CEVENT'
  WHEN "pTagLocation" = 'U' THEN
    SET "stype" = 'USER'
  WHEN "pTagLocation" LIKE 'R%' THEN
    SET "stype" = 'REFER'
  WHEN "pTagLocation" = 'I' THEN
    SET "stype" = 'PURCH'
  WHEN "pTagLocation" = 'X' THEN
    SET "stype" = 'AGENCY'
  WHEN "pTagLocation" LIKE 'J%' THEN
    SET "stype" = 'COLLECT'
  ELSE
    SET "stype" = "string"('TAG ',"pTagLocation")
  END CASE;
  IF "length"("pTagLocation") > 1 THEN
    SET "stagdescrip" = "string"((SELECT "name"+' ' FROM "department" WHERE "departmentid" = "substring"("pTagLocation",2)),"stagdescrip")
  END IF;
  IF "isnull"("sname",'') <> '' THEN
    SET "sname" = ' - '+"sname"
  END IF;
  SET "sname" = "string"("stagdescrip",' Updated',"sname");
  CALL "AuditLog"("stype","pID","sname","isnull"("pOldValue",''),"isnull"("NewValue",''))
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetQuestionAudit" IS 
{CREATE PROCEDURE pears."NetQuestionAudit" 
(IN pTagLocation CHAR(3), IN pTagID CHAR(3), IN pID CHAR(20), IN pOldValue long VARCHAR )
BEGIN
DECLARE iAudit SMALLINT;
DECLARE NewValue long VARCHAR;
DECLARE stagdescrip CHAR(100);
DECLARE sname CHAR(100);
DECLARE stype CHAR(12);
SELECT description,auditflag INTO stagdescrip,iAudit FROM tag WHERE taglocation=pTagLocation AND tagid=pTagID;
IF TRIM(isnull(iAudit,0))=0 THEN
   RETURN
END IF;
SET NewValue=getquestanswer(pTagLocation,pTagID,pID);
CASE 
    WHEN pTagLocation='C' THEN 
      SET stype = 'COMPANY';
      SELECT name INTO sname FROM company WHERE companyid = pID;
    WHEN pTagLocation = 'P' OR pTagLocation LIKE 'A%' THEN
      SET stype = 'PERSON';
      SELECT name INTO sname FROM person WHERE personid = pID;
    WHEN pTagLocation = 'E' THEN
      SET stype = 'COMPANY';
      SELECT company.companyid,string(person.name,', ',company.name) INTO pID,sname FROM employment KEY JOIN(person,company) WHERE employment.employmentid = pID;
    WHEN pTagLocation LIKE 'V%' THEN
      SET stype = 'VACANCY';
      SELECT string(Company.Name,' - ',vacancy.position,'(',vacancy.RefCode,')') INTO sname FROM vacancy KEY JOIN employment KEY JOIN Company WHERE vacancy.vacancyid = pID;
    WHEN pTagLocation LIKE 'L%' THEN
      SET stype = 'PLACEMENT';
      SELECT string(Company.Name,' - ',employment.position) INTO sname FROM placement KEY JOIN employment KEY JOIN Company WHERE placement.placementid = pID;
    WHEN pTagLocation LIKE 'S%' THEN
      SET stype = 'SHIFTPLAN';
    WHEN pTagLocation LIKE 'T%' THEN
      SET stype = 'TIMESHEET';
      SELECT
         isnull(
           (SELECT FIRST IF c.Name IS NOT NULL THEN
            string(t.SerialNumber,': ',p.Name,' as ',v.Position,' at ',c.Name)
            ELSE string(t.SerialNumber,': ',p.Name,' - Internal ')
            endif FROM TempTimeSheet AS t KEY JOIN Person AS p,TempTimeSheet AS t KEY LEFT OUTER JOIN(Placement KEY JOIN Vacancy AS v KEY JOIN Employment KEY JOIN Company AS c) WHERE t.TempTimeSheetID = pID),
           (SELECT FIRST string(t.SerialNumber,'(P): ',p.Name,' as ',v.Position,' at ',c.Name) FROM TempProvTimeSheet AS t KEY LEFT OUTER JOIN Person AS p,TempProvTimeSheet AS t
             KEY LEFT OUTER JOIN(Vacancy AS v KEY JOIN Employment KEY JOIN Company AS c) WHERE t.TempProvTimeSheetID = pID))
         INTO sname;
    WHEN pTagLocation LIKE 'N%' THEN
      SET stype = 'CEVENT';
    WHEN pTagLocation = 'U' THEN
      SET stype = 'USER';
    WHEN pTagLocation LIKE 'R%' THEN
      SET stype = 'REFER';
    WHEN pTagLocation = 'I' THEN
      SET stype = 'PURCH';
    WHEN pTagLocation = 'X' THEN
      SET stype = 'AGENCY';
    WHEN pTagLocation LIKE 'J%' THEN
      SET stype = 'COLLECT';
    ELSE
      SET stype = string('TAG ',pTagLocation);
END CASE;
IF LENGTH(pTagLocation) > 1 THEN
   SET stagdescrip = string((SELECT name + ' ' FROM department WHERE departmentid = SUBSTRING(pTagLocation,2)), stagdescrip)
END IF;
IF isnull(sname,'')<>'' THEN
    SET sname=' - '+sname;
END IF;
SET sname = string(stagdescrip,' Updated',sname);
CALL AuditLog(stype,pID,sname,isnull(pOldValue,''),isnull(NewValue,''))
END
}