-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Company"
-- Table comment: Base Company / Client Record
-- Statement count: 56
CREATE TABLE "pears"."Company" (
"companyid" CHAR(20) NOT NULL
,"name" CHAR(60) NOT NULL
,"keyname" CHAR(60) NOT NULL CHECK("length"("trim"("keyname")) > 0)
,"clientcode" CHAR(12) NULL
,"fsflag" CHAR(1) NULL
,"bms_id" CHAR(12) NULL
,"addr1" CHAR(40) NULL
,"addr2" CHAR(40) NULL
,"addr3" CHAR(40) NULL
,"town" CHAR(30) NULL
,"county" CHAR(30) NULL
,"country" CHAR(30) NULL
,"postcode" CHAR(20) NULL
,"alert" CHAR(100) NULL
,"notes" long VARCHAR NULL
,"invoiceaddress" SMALLINT NULL
,"TempChargeCode" CHAR(100) NULL
,"VatOnMargin" SMALLINT NULL
,"TempShiftCode" CHAR(12) NULL
,"status" CHAR(1) NOT NULL DEFAULT 'P'
,"source" CHAR(1) NULL
,"ParentCompanyID" CHAR(20) NULL
,"currency" CHAR(3) NULL
,"divisionid" CHAR(20) NULL
,"staffid" CHAR(20) NULL
,"TransferNotes" long VARCHAR NULL
,"RemoteSelfBill" tinyint NULL
,"TempHolidayCode" CHAR(12) NULL
,"registrationdate" DATE NULL DEFAULT CURRENT DATE
,"lastcontactevent" TIMESTAMP NULL
,"ExtraNotes" long VARCHAR NULL
,"DefaultRateScheme" CHAR(20) NULL
,"CompanyWarning" long VARCHAR NULL
,"DefaultDocPack" CHAR(20) NULL
,"SupplierCode" CHAR(12) NULL
,"OriginID" CHAR(20) NULL
,"PrivateSector" SMALLINT NULL DEFAULT 0
,"IsPortal" SMALLINT NULL DEFAULT 0
,"AutoMatchUrgency" tinyint NULL DEFAULT 0
,"VATReverseCharge" tinyint NULL
,"AllowPAYE" tinyint NULL DEFAULT 1
,"AllowLTD" tinyint NULL DEFAULT 1
,"AllowLTDF" tinyint NULL DEFAULT 1
,"AllowSelf" tinyint NULL DEFAULT 1
,"CompanySalesStatusID" CHAR(20) NULL
,"TempComplianceCode" CHAR(12) NULL
,"DefaultVacancyRoleID" CHAR(20) NULL
,"ETimesheetProv" tinyint NOT NULL DEFAULT 0
,"InvoiceWorkedShifts" SMALLINT NULL DEFAULT 0
,"QuestionDepartmentID" CHAR(2) NULL
,PRIMARY KEY ("companyid" ASC)
)
GO
COMMENT ON COLUMN "pears"."Company"."TempChargeCode" IS
'Allows for the setting of of client or group based temp charge schemes'
GO
COMMENT ON COLUMN "pears"."Company"."TempShiftCode" IS
'Allows for the setting of of client or group based standard shifts'
GO
COMMENT ON COLUMN "pears"."Company"."currency" IS
'Only used if params.homecurrency is set. Otherwise currency follows the default currency of the logged in user.'
GO
COMMENT ON COLUMN "pears"."Company"."staffid" IS
'Made available in V2. Hidden by default'
GO
COMMENT ON COLUMN "pears"."Company"."RemoteSelfBill" IS
'Identifies a company downloaded by a master agency which self-bills'
GO
COMMENT ON COLUMN "pears"."Company"."TempHolidayCode" IS
'Allows client specific holidays for rate script use'
GO
COMMENT ON COLUMN "pears"."Company"."registrationdate" IS
'Added V2.2.2.11 Will be null for existing companies'
GO
COMMENT ON COLUMN "pears"."Company"."lastcontactevent" IS
'Added V2.2.2.13 Will be null for existing companies'
GO
COMMENT ON COLUMN "pears"."Company"."SupplierCode" IS
'Self bill account for secondary agency'
GO
COMMENT ON COLUMN "pears"."Company"."OriginID" IS
'This is the new Source'
GO
COMMENT ON COLUMN "pears"."Company"."PrivateSector" IS
'Flag to indicate public/private'
GO
COMMENT ON COLUMN "pears"."Company"."AutoMatchUrgency" IS
'Identifies automatch priority for a company'
GO
COMMENT ON COLUMN "pears"."Company"."VATReverseCharge" IS
'Construction rules on VAT charging'
GO
COMMENT ON COLUMN "pears"."Company"."AllowLTDF" IS
'Limited + HMRCEngagement = F'
GO
COMMENT ON COLUMN "pears"."Company"."TempComplianceCode" IS
'Allows client specific compliance domains'
GO
COMMENT ON COLUMN "pears"."Company"."InvoiceWorkedShifts" IS
'Invoice Shifts off Company Accounts'
GO
COMMENT ON COLUMN "pears"."Company"."QuestionDepartmentID" IS
'Department used for question, defaults to user department if not set'
GO
COMMENT ON TABLE "pears"."Company" IS
'Base Company / Client Record'
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "vacancyclass" ("source" ASC)
REFERENCES "pears"."vacancyclass" ("classcode")
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "staff" ("staffid" ASC)
REFERENCES "pears"."staff" ("staffid")
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "Company" ("ParentCompanyID" ASC)
REFERENCES "pears"."Company" ("companyid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "division" ("divisionid" ASC)
REFERENCES "pears"."Division" ("divisionid")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "origin" ("OriginID" ASC)
REFERENCES "pears"."Origin" ("OriginID")
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "CompanySalesStatus" ("CompanySalesStatusID" ASC)
REFERENCES "pears"."CompanySalesStatus" ("CompanySalesStatusID")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "VacancyRole" ("DefaultVacancyRoleID" ASC)
REFERENCES "pears"."VacancyRole" ("VacancyRoleID")
ON DELETE SET NULL
GO
ALTER TABLE "pears"."Company"
ADD NOT NULL FOREIGN KEY "companystatus" ("status" ASC)
REFERENCES "pears"."CompanyStatus" ("CompanyStatusID")
GO
ALTER TABLE "pears"."Company"
ADD FOREIGN KEY "Department" ("QuestionDepartmentID" ASC)
REFERENCES "pears"."Department" ("departmentid")
ON DELETE SET NULL
GO
CREATE INDEX "company_keyname" ON "pears"."Company"
( "keyname" )
GO
CREATE INDEX "company_postcode" ON "pears"."Company"
( "postcode" )
GO
CREATE INDEX "cotown" ON "pears"."Company"
( "town" )
GO
CREATE INDEX "company_clientcode" ON "pears"."Company"
( "clientcode" )
GO
CREATE INDEX "person_lastcontactevent" ON "pears"."Company"
( "lastcontactevent" DESC )
GO
CREATE TEXT INDEX "company_notestext" ON "pears"."Company"
( "notes" ) CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH
GO
CREATE TRIGGER "companyupdate" after UPDATE OF "name",
"clientcode","addr1","addr2","addr3","town","county","country","postcode",
"invoiceaddress","privatesector" ORDER 3 ON "pears"."Company"
REFERENCING NEW AS "new_comp"
FOR each ROW
BEGIN
UPDATE "companyaccount" SET "transferbatch" = 0 WHERE "companyid" = "new_comp"."companyid" AND "transferbatch" > 0;
IF "new_comp"."invoiceaddress" = 1 THEN
UPDATE "companyaccount" KEY JOIN "company" SET "companyaccount"."transferbatch" = 0 WHERE "company"."companyid" <> "new_comp"."companyid"
AND "company"."clientcode" = "new_comp"."clientcode" AND "companyaccount"."transferbatch" > 0
ELSE IF UPDATE("clientcode") THEN
UPDATE "company" AS "c" SET "currency" = "b"."currency" FROM "company" AS "c","company" AS "b"
WHERE "c"."companyid" = "new_comp"."companyid" AND "b"."companyid" = "getinvoicecompanyid"("new_comp"."clientcode");
UPDATE "companyaccount" AS "c" SET "creditlimit" = "b"."creditlimit","dayscredit" = "b"."dayscredit","groupinvoice" = "b"."groupinvoice",
"ernioninvoice" = "b"."ernioninvoice","invoiceemail" = "b"."invoiceemail","onstop" = "b"."onstop","TheirRefRequiredInvoice" = "b"."TheirRefRequiredInvoice",
"Documenttemplateid" = "b"."Documenttemplateid","PDFInvCreate" = "b"."PDFInvCreate","PDFInvIncTS" = "b"."PDFInvIncTS","FixedNI" = "b"."FixedNI",
"VATNumber" = "b"."VATNumber","VATExempt" = "b"."VATExempt","PDFExtraDocs" = "b"."PDFExtraDocs","INVOICEMANAGEMENTGROUP" = "b"."INVOICEMANAGEMENTGROUP","FixedWTR" = "b"."FixedWTR" FROM
"companyaccount" AS "c","companyaccount" AS "b"
WHERE "c"."companyid" = "new_comp"."companyid" AND "b"."companyid" = "getinvoicecompanyid"("new_comp"."clientcode")
END IF
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."companyupdate" IS
{CREATE TRIGGER companyupdate
after UPDATE OF "name",
"clientcode","addr1","addr2","addr3","town","county","country","postcode",
"invoiceaddress","privatesector" ORDER 3 ON "pears"."Company"
REFERENCING NEW AS "new_comp"
FOR each ROW
BEGIN
UPDATE "companyaccount" SET "transferbatch" = 0 WHERE "companyid" = "new_comp"."companyid" AND "transferbatch" > 0;
IF "new_comp"."invoiceaddress" = 1 THEN
UPDATE "companyaccount" KEY JOIN "company" SET "companyaccount"."transferbatch" = 0 WHERE "company"."companyid" <> "new_comp"."companyid"
AND "company"."clientcode" = "new_comp"."clientcode" AND "companyaccount"."transferbatch" > 0
ELSE IF UPDATE("clientcode") THEN
UPDATE "company" AS "c" SET "currency" = "b"."currency" FROM "company" AS "c","company" AS "b"
WHERE "c"."companyid" = "new_comp"."companyid" AND "b"."companyid" = "getinvoicecompanyid"("new_comp"."clientcode");
UPDATE "companyaccount" AS "c" SET "creditlimit" = "b"."creditlimit","dayscredit" = "b"."dayscredit","groupinvoice" = "b"."groupinvoice",
"ernioninvoice" = "b"."ernioninvoice","invoiceemail" = "b"."invoiceemail","onstop" = "b"."onstop","TheirRefRequiredInvoice" = "b"."TheirRefRequiredInvoice",
"Documenttemplateid" = "b"."Documenttemplateid","PDFInvCreate" = "b"."PDFInvCreate","PDFInvIncTS" = "b"."PDFInvIncTS","FixedNI" = "b"."FixedNI",
"VATNumber" = "b"."VATNumber","VATExempt" = "b"."VATExempt","PDFExtraDocs" = "b"."PDFExtraDocs","INVOICEMANAGEMENTGROUP" = "b"."INVOICEMANAGEMENTGROUP","FixedWTR" = "b"."FixedWTR" FROM
"companyaccount" AS "c","companyaccount" AS "b"
WHERE "c"."companyid" = "new_comp"."companyid" AND "b"."companyid" = "getinvoicecompanyid"("new_comp"."clientcode")
END IF
END IF
END
}
GO
CREATE TRIGGER "updateinvaddress" BEFORE UPDATE OF "invoiceaddress"
ORDER 2 ON "pears"."Company"
REFERENCING OLD AS "old_company" NEW AS "new_company"
FOR each ROW
WHEN("isnull"("new_company"."invoiceaddress",0) = 1
AND "isnull"("old_company"."invoiceaddress",0) = 0
AND "new_company"."clientcode" IS NOT NULL)
BEGIN
UPDATE "company" SET "invoiceaddress" = NULL
WHERE "clientcode" = "new_company"."clientcode"
AND "companyid" <> "new_company"."companyid"
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."updateinvaddress" IS
{CREATE TRIGGER updateinvaddress
BEFORE UPDATE OF invoiceaddress
ORDER 2 ON pears.Company
REFERENCING OLD AS old_company NEW AS new_company
FOR each ROW
WHEN(isnull(new_company.invoiceaddress,0) = 1 AND
isnull(old_company.invoiceaddress,0) = 0 AND
new_company.clientcode IS NOT NULL)
BEGIN
UPDATE company SET invoiceaddress = NULL WHERE
clientcode = new_company.clientcode AND
companyid <> new_company.companyid
END
}
GO
CREATE TRIGGER "insertinvoiceaddress" BEFORE INSERT ORDER 1 ON
"pears"."Company"
REFERENCING NEW AS "new_company"
FOR each ROW
WHEN("new_company"."invoiceaddress" = 1
AND "new_company"."clientcode" IS NOT NULL)
BEGIN
UPDATE "company" SET "invoiceaddress" = NULL
WHERE "clientcode" = "new_company"."clientcode"
AND "companyid" <> "new_company"."companyid"
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."insertinvoiceaddress" IS
{CREATE TRIGGER insertinvoiceaddress
BEFORE INSERT ORDER 1 ON
pears.Company
REFERENCING NEW AS new_company
FOR each ROW
WHEN(new_company.invoiceaddress = 1 AND
new_company.clientcode IS NOT NULL)
BEGIN
UPDATE company SET invoiceaddress = NULL WHERE
clientcode = new_company.clientcode AND
companyid <> new_company.companyid
END
}
GO
CREATE TRIGGER "WPK_company_SECAGENCY" after INSERT,DELETE,UPDATE ORDER 10 ON
"pears"."Company"
REFERENCING OLD AS "oldc" NEW AS "newc"
FOR each ROW
BEGIN
DECLARE "doit" SMALLINT;
IF inserting THEN IF "newc"."status" = 'A' THEN SET "doit" = 1
END IF END IF;
IF deleting THEN IF "oldc"."status" = 'A' THEN SET "doit" = 1
END IF END IF;
IF updating THEN IF(("newc"."status" = 'A') OR("oldc"."status" = 'A')) THEN
IF(("newc"."status" <> "oldc"."status") OR("oldc"."name" <> "newc"."name")) THEN SET "doit" = 1
END IF
END IF END IF;
IF "doit" = 1 THEN CALL "WPKTrackChange"('P','SECAGENCY')
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."WPK_company_SECAGENCY" IS
{CREATE TRIGGER WPK_company_SECAGENCY
after INSERT,DELETE,UPDATE ORDER 10 ON
pears.Company
REFERENCING OLD AS oldc NEW AS newc
FOR each ROW
BEGIN
DECLARE doit SMALLINT;
IF inserting THEN IF newc.status = 'A' THEN SET doit=1
END IF
END IF;
IF deleting THEN IF oldc.status = 'A' THEN SET doit=1
END IF
END IF;
IF updating THEN IF((newc.status = 'A') OR(oldc.status = 'A')) THEN
IF((newc.status <> oldc.status) OR(oldc.name <> newc.name)) THEN SET doit=1
END IF
END IF
END IF;
IF doit = 1 THEN CALL WPKTrackChange('P','SECAGENCY')
END IF END
}
GO
CREATE TRIGGER "Company_InsertTrim" BEFORE INSERT ORDER 4 ON
"pears"."company"
REFERENCING NEW AS "new_p"
FOR each ROW
BEGIN
SET "new_p"."keyname" = "trim"("new_p"."keyname");
SET "new_p"."clientcode" = "trim"("new_p"."clientcode")
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."Company_InsertTrim" IS
{CREATE TRIGGER Company_InsertTrim
BEFORE INSERT ORDER 4 ON
pears.company
REFERENCING NEW AS new_p
FOR each ROW
BEGIN
SET new_p.keyname = TRIM(new_p.keyname);
SET new_p.clientcode = TRIM(new_p.clientcode)
END
}
GO
CREATE TRIGGER "Company_UpdateTrim" BEFORE UPDATE ORDER 4 ON
"pears"."company"
REFERENCING NEW AS "new_p"
FOR each ROW
BEGIN
SET "new_p"."keyname" = "trim"("new_p"."keyname");
SET "new_p"."clientcode" = "trim"("new_p"."clientcode")
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."Company_UpdateTrim" IS
{CREATE TRIGGER Company_UpdateTrim
BEFORE UPDATE ORDER 4 ON
pears.company
REFERENCING NEW AS new_p
FOR each ROW
BEGIN
SET new_p.keyname = TRIM(new_p.keyname);
SET new_p.clientcode = TRIM(new_p.clientcode)
END
}
GO
CREATE TRIGGER "InsertStatusCompany" after INSERT ORDER 2 ON
"pears"."Company"
REFERENCING NEW AS "new_co"
FOR each ROW
BEGIN
INSERT INTO "StatusHistory"( "StatusHistoryID","companyid","staffid","newstatus" ) VALUES
( "uniquekey"("new_co"."companyid"),"new_co"."companyid","userstaffid","new_co"."status" ) ;
INSERT INTO "StatusHistory"( "StatusHistoryID","companyid","staffid","newstatus","IsSales" ) VALUES
( "uniquekey"("new_co"."companyid"),"new_co"."companyid","userstaffid","new_co"."CompanySalesStatusID",1 )
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."InsertStatusCompany" IS
{CREATE TRIGGER InsertStatusCompany
after INSERT ORDER 2 ON
pears.Company
REFERENCING NEW AS new_co
FOR each ROW
BEGIN
INSERT INTO StatusHistory (StatusHistoryID, companyid, staffid, newstatus)
VALUES
(uniquekey(new_co.companyid),new_co.companyid, userstaffid,new_co.status);
INSERT INTO StatusHistory (StatusHistoryID, companyid, staffid, newstatus, IsSales)
VALUES
(uniquekey(new_co.companyid),new_co.companyid, userstaffid,new_co.CompanySalesStatusID,1)
END
}
GO
CREATE TRIGGER "UpdateStatusCompany" after UPDATE OF "status",
"CompanySalesStatusID" ORDER 2 ON "pears"."Company"
REFERENCING OLD AS "old_co" NEW AS "new_co"
FOR each ROW
BEGIN
DECLARE "ID" CHAR(20);
IF UPDATE("status") THEN
SELECT FIRST "StatusHistoryID" INTO "ID" FROM "StatusHistory" WHERE "companyid" = "new_co"."companyid" AND "IsSales" = 0
AND "datediff"("minute","whenentered",CURRENT TIMESTAMP) < 1 AND "newstatus" = "old_co"."status" AND "oldstatus" = "new_co"."status" ORDER BY "whenentered" DESC;
IF "ID" IS NOT NULL THEN
DELETE FROM "StatusHistory" WHERE "StatusHistoryID" = "ID"
ELSE
INSERT INTO "StatusHistory"( "StatusHistoryID","companyid","staffid","oldstatus","newstatus" ) VALUES
( "uniquekey"("new_co"."companyid"),"new_co"."companyid","userstaffid","old_co"."status","new_co"."status" )
END IF
END IF;
IF UPDATE("CompanySalesStatusID") THEN
SELECT FIRST "StatusHistoryID" INTO "ID" FROM "StatusHistory" WHERE "companyid" = "new_co"."companyid" AND "IsSales" = 1
AND "datediff"("minute","whenentered",CURRENT TIMESTAMP) < 1 AND "newstatus" = "old_co"."CompanySalesStatusID" AND "oldstatus" = "new_co"."CompanySalesStatusID" ORDER BY "whenentered" DESC;
IF "ID" IS NOT NULL THEN
DELETE FROM "StatusHistory" WHERE "StatusHistoryID" = "ID"
ELSE
INSERT INTO "StatusHistory"( "StatusHistoryID","companyid","staffid","oldstatus","newstatus","IsSales" ) VALUES
( "uniquekey"("new_co"."companyid"),"new_co"."companyid","userstaffid","old_co"."CompanySalesStatusID","new_co"."CompanySalesStatusID",1 )
END IF
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."UpdateStatusCompany" IS
{CREATE TRIGGER UpdateStatusCompany
after UPDATE OF STATUS, CompanySalesStatusID
ORDER 2 ON pears.Company
REFERENCING OLD AS old_co NEW AS new_co
FOR each ROW
BEGIN
DECLARE ID CHAR(20);
IF UPDATE(STATUS) THEN
SELECT FIRST StatusHistoryID INTO ID FROM StatusHistory WHERE companyid = new_co.companyid AND IsSales = 0
AND datediff(MINUTE, whenentered, CURRENT TIMESTAMP) < 1 AND newstatus = old_co.status AND oldstatus = new_co.status ORDER BY whenentered DESC;
IF ID IS NOT NULL THEN
DELETE FROM StatusHistory WHERE StatusHistoryID = ID
ELSE
INSERT INTO StatusHistory (StatusHistoryID, companyid, staffid, oldstatus, newstatus)
VALUES
(uniquekey(new_co.companyid),new_co.companyid, userstaffid, old_co.status, new_co.status)
END IF;
END IF;
IF UPDATE(CompanySalesStatusID) THEN
SELECT FIRST StatusHistoryID INTO ID FROM StatusHistory WHERE companyid = new_co.companyid AND IsSales = 1
AND datediff(MINUTE, whenentered, CURRENT TIMESTAMP) < 1 AND newstatus = old_co.CompanySalesStatusID AND oldstatus = new_co.CompanySalesStatusID ORDER BY whenentered DESC;
IF ID IS NOT NULL THEN
DELETE FROM StatusHistory WHERE StatusHistoryID = ID
ELSE
INSERT INTO StatusHistory (StatusHistoryID, companyid, staffid, oldstatus, newstatus, IsSales)
VALUES
(uniquekey(new_co.companyid),new_co.companyid, userstaffid, old_co.CompanySalesStatusID, new_co.CompanySalesStatusID, 1)
END IF;
END IF
END
}
GO
CREATE TRIGGER "CompanyAudit" BEFORE UPDATE OF "name",
"keyname","addr1","addr2","addr3","town","county","country","postcode","status","ParentCompanyID","divisionid","staffid","alert",
"originid","ClientCode","notes","PrivateSector","companywarning","CompanySalesStatusID" ORDER 1 ON "pears"."Company"
REFERENCING OLD AS "old_comp" NEW AS "new_comp"
FOR each ROW
WHEN(EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Company' AND "AuditFlag" = 1))
BEGIN
DECLARE @AuditList long VARCHAR;
DECLARE @OldDescrip CHAR(250);
DECLARE @NewDescrip CHAR(250);
DECLARE @CompName CHAR(250);
SELECT "string"(',',"list"("ItemName"),',') INTO @AuditList FROM "AuditItems" WHERE "AreaName" = 'Company' AND "AuditFlag" = 1;
SET @CompName = "old_comp"."Name";
-- Status
IF "locate"(@AuditList,',Status,') > 0 AND UPDATE("Status") THEN
SELECT "name" INTO @OldDescrip FROM "status" WHERE "type" = 'C' AND "status"."status" = "old_comp"."status";
SELECT "name" INTO @NewDescrip FROM "status" WHERE "type" = 'C' AND "status"."status" = "new_comp"."status";
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Status Updated - ',@CompName),"string"("old_comp"."Status",' - ',@OldDescrip),"string"("new_comp"."Status",' - ',@NewDescrip))
END IF;
-- SalesStatus
IF "locate"(@AuditList,',SalesStatus,') > 0 AND UPDATE("companysalesstatusid") THEN
SELECT "name" INTO @OldDescrip FROM "companysalesstatus" WHERE "companysalesstatusid" = "old_comp"."companysalesstatusid";
SELECT "name" INTO @NewDescrip FROM "companysalesstatus" WHERE "companysalesstatusid" = "new_comp"."companysalesstatusid";
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Sales Status Updated - ',@CompName),"string"("old_comp"."companysalesstatusid",' - ',@OldDescrip),"string"("new_comp"."companysalesstatusid",' - ',@NewDescrip))
END IF;
-- Division
IF "locate"(@AuditList,',Division,') > 0 AND UPDATE("DivisionID") THEN
SELECT "name" INTO @OldDescrip FROM "Division" WHERE "divisionid" = "old_comp"."DivisionID";
SELECT "name" INTO @NewDescrip FROM "Division" WHERE "divisionid" = "new_comp"."DivisionID";
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Division Updated - ',@CompName),@OldDescrip,@NewDescrip)
END IF;
-- Name
IF "locate"(@AuditList,',Name,') > 0 AND UPDATE("Name") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Name Updated - ',@CompName),"old_comp"."name","new_comp"."name")
END IF;
-- Notes
IF "locate"(@AuditList,',Notes,') > 0 AND UPDATE("Notes") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Notes Updated - ',@CompName),"old_comp"."notes","new_comp"."notes")
END IF;
IF "locate"(@AuditList,',Warning,') > 0 AND UPDATE("companywarning") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Warning Updated - ',@CompName),"old_comp"."companywarning","new_comp"."companywarning")
END IF;
IF "locate"(@AuditList,',KeyName,') > 0 AND UPDATE("KeyName") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('KeyName Updated - ',@CompName),"old_comp"."keyname","new_comp"."keyname")
END IF;
-- Parent
IF "locate"(@AuditList,',Parent,') > 0 AND UPDATE("ParentCompanyID") THEN
SELECT "name" INTO @OldDescrip FROM "Company" WHERE "companyid" = "old_comp"."ParentCompanyID";
SELECT "name" INTO @NewDescrip FROM "Company" WHERE "companyid" = "new_comp"."ParentCompanyID";
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Parent Company Updated - ',@CompName),@OldDescrip,@NewDescrip)
END IF;
-- Address
IF "locate"(@AuditList,',Address,') > 0 AND(UPDATE("addr1") OR UPDATE("addr2") OR UPDATE("addr3") OR UPDATE("town") OR UPDATE("county") OR UPDATE("country") OR UPDATE("postcode")) THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Address Updated - ',@CompName),"string"("old_comp"."addr1",', ',"old_comp"."addr2",', ',"old_comp"."addr3",', ',"old_comp"."town",', ',"old_comp"."county",', ',"old_comp"."postcode",', ',"old_comp"."country"),"string"("new_comp"."addr1",', ',"new_comp"."addr2",', ',"new_comp"."addr3",', ',"new_comp"."town",', ',"new_comp"."county",', ',"new_comp"."postcode",', ',"new_comp"."country"))
END IF;
-- Consultant
IF "locate"(@AuditList,',Consultant,') > 0 AND UPDATE("StaffID") THEN
SELECT "userid" INTO @OldDescrip FROM "staff" WHERE "staffid" = "old_comp"."StaffID";
SELECT "userid" INTO @NewDescrip FROM "staff" WHERE "staffid" = "new_comp"."StaffID";
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Consultant Updated - ',@CompName),@OldDescrip,@NewDescrip);
INSERT INTO "staffhistory"( "staffhistoryid","companyid","oldstaff","newstaff","whoentered","whenentered" ) VALUES( "uniquekey"("new_comp"."StaffID"),"new_comp"."companyid","old_comp"."StaffID","new_comp"."StaffID","userstaffid",CURRENT TIMESTAMP )
END IF;
-- Alert
IF "locate"(@AuditList,',Alert,') > 0 AND UPDATE("Alert") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Alert Updated - ',@CompName),"old_comp"."Alert","new_comp"."Alert")
END IF;
-- Source
IF "locate"(@AuditList,',Source,') > 0 AND UPDATE("Originid") THEN
SELECT "descrip" INTO @OldDescrip FROM "origin" WHERE "originid" = "old_comp"."originid";
SELECT "descrip" INTO @NewDescrip FROM "origin" WHERE "originid" = "new_comp"."originid";
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Source Updated - ',@CompName),@OldDescrip,@NewDescrip)
END IF;
-- Account Code
IF "locate"(@AuditList,',Account Code,') > 0 AND UPDATE("ClientCode") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Account Code - ',@CompName),"old_comp"."ClientCode","new_comp"."ClientCode")
END IF;
IF "locate"(@AuditList,',Private Sector Outside IR35,') > 0 AND UPDATE("PrivateSector") THEN
CALL "AuditLog"('COMPANY',"old_comp"."companyid","string"('Private Sector Outside IR35 - ',@CompName),"old_comp"."PrivateSector","new_comp"."PrivateSector")
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."CompanyAudit" IS
{CREATE TRIGGER CompanyAudit
BEFORE UPDATE OF name,keyname,addr1,addr2,addr3,town,county,country,postcode,STATUS,ParentCompanyID,divisionid,staffid,alert,
originid,ClientCode,notes,PrivateSector, companywarning, CompanySalesStatusID ORDER 1 ON pears.Company
REFERENCING OLD AS old_comp NEW AS new_comp
FOR each ROW
WHEN(EXISTS(SELECT* FROM AuditItems WHERE AreaName = 'Company' AND AuditFlag = 1))
BEGIN
DECLARE @AuditList long VARCHAR;
DECLARE @OldDescrip CHAR(250);
DECLARE @NewDescrip CHAR(250);
DECLARE @CompName CHAR(250);
SELECT string(',',list(ItemName),',') INTO @AuditList FROM AuditItems WHERE AreaName = 'Company' AND AuditFlag = 1;
SET @CompName=old_comp.Name;
-- Status
IF locate(@AuditList,',Status,') > 0 AND UPDATE(STATUS) THEN
SELECT name INTO @OldDescrip FROM STATUS WHERE TYPE = 'C' AND STATUS.status = old_comp.status;
SELECT name INTO @NewDescrip FROM STATUS WHERE TYPE = 'C' AND STATUS.status = new_comp.status;
CALL AuditLog('COMPANY',old_comp.companyid,string('Status Updated - ',@CompName),string(old_comp.Status,' - ',@OldDescrip),string(new_comp.Status,' - ',@NewDescrip))
END IF;
-- SalesStatus
IF locate(@AuditList,',SalesStatus,') > 0 AND UPDATE(companysalesstatusid) THEN
SELECT name INTO @OldDescrip FROM companysalesstatus WHERE companysalesstatusid = old_comp.companysalesstatusid;
SELECT name INTO @NewDescrip FROM companysalesstatus WHERE companysalesstatusid = new_comp.companysalesstatusid;
CALL AuditLog('COMPANY',old_comp.companyid,string('Sales Status Updated - ',@CompName),string(old_comp.companysalesstatusid,' - ',@OldDescrip),string(new_comp.companysalesstatusid,' - ',@NewDescrip))
END IF;
-- Division
IF locate(@AuditList,',Division,') > 0 AND UPDATE(DivisionID) THEN
SELECT name INTO @OldDescrip FROM Division WHERE divisionid = old_comp.DivisionID;
SELECT name INTO @NewDescrip FROM Division WHERE divisionid = new_comp.DivisionID;
CALL AuditLog('COMPANY',old_comp.companyid,string('Division Updated - ',@CompName),@OldDescrip,@NewDescrip)
END IF;
-- Name
IF locate(@AuditList,',Name,') > 0 AND UPDATE(Name) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Name Updated - ',@CompName),old_comp.name,new_comp.name)
END IF;
-- Notes
IF locate(@AuditList,',Notes,') > 0 AND UPDATE(Notes) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Notes Updated - ',@CompName),old_comp.notes,new_comp.notes)
END IF;
IF locate(@AuditList,',Warning,') > 0 AND UPDATE(companywarning) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Warning Updated - ',@CompName),old_comp.companywarning,new_comp.companywarning)
END IF;
IF locate(@AuditList,',KeyName,') > 0 AND UPDATE(KeyName) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('KeyName Updated - ',@CompName),old_comp.keyname,new_comp.keyname)
END IF;
-- Parent
IF locate(@AuditList,',Parent,') > 0 AND UPDATE(ParentCompanyID) THEN
SELECT name INTO @OldDescrip FROM Company WHERE companyid = old_comp.ParentCompanyID;
SELECT name INTO @NewDescrip FROM Company WHERE companyid = new_comp.ParentCompanyID;
CALL AuditLog('COMPANY',old_comp.companyid,string('Parent Company Updated - ',@CompName),@OldDescrip,@NewDescrip)
END IF;
-- Address
IF locate(@AuditList,',Address,') > 0 AND(UPDATE(addr1) OR UPDATE(addr2) OR UPDATE(addr3) OR UPDATE(town) OR UPDATE(county) OR UPDATE(country) OR UPDATE(postcode)) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Address Updated - ',@CompName),string(old_comp.addr1,', ',old_comp.addr2,', ',old_comp.addr3,', ',old_comp.town,', ',old_comp.county,', ',old_comp.postcode,', ',old_comp.country),string(new_comp.addr1,', ',new_comp.addr2,', ',new_comp.addr3,', ',new_comp.town,', ',new_comp.county,', ',new_comp.postcode,', ',new_comp.country))
END IF;
-- Consultant
IF locate(@AuditList,',Consultant,') > 0 AND UPDATE(StaffID) THEN
SELECT userid INTO @OldDescrip FROM staff WHERE staffid = old_comp.StaffID;
SELECT userid INTO @NewDescrip FROM staff WHERE staffid = new_comp.StaffID;
CALL AuditLog('COMPANY',old_comp.companyid,string('Consultant Updated - ',@CompName),@OldDescrip,@NewDescrip);
INSERT INTO staffhistory (staffhistoryid, companyid, oldstaff, newstaff, whoentered, whenentered) VALUES (uniquekey(new_comp.StaffID),new_comp.companyid, old_comp.StaffID, new_comp.StaffID, userstaffid, CURRENT TIMESTAMP)
END IF;
-- Alert
IF locate(@AuditList,',Alert,') > 0 AND UPDATE(Alert) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Alert Updated - ',@CompName),old_comp.Alert,new_comp.Alert)
END IF;
-- Source
IF locate(@AuditList,',Source,') > 0 AND UPDATE(Originid) THEN
SELECT descrip INTO @OldDescrip FROM origin WHERE originid = old_comp.originid;
SELECT descrip INTO @NewDescrip FROM origin WHERE originid = new_comp.originid;
CALL AuditLog('COMPANY',old_comp.companyid,string('Source Updated - ',@CompName),@OldDescrip,@NewDescrip)
END IF;
-- Account Code
IF locate(@AuditList,',Account Code,') > 0 AND UPDATE(ClientCode) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Account Code - ',@CompName),old_comp.ClientCode,new_comp.ClientCode)
END IF;
IF locate(@AuditList,',Private Sector Outside IR35,') > 0 AND UPDATE(PrivateSector) THEN
CALL AuditLog('COMPANY',old_comp.companyid,string('Private Sector Outside IR35 - ',@CompName),old_comp.PrivateSector,new_comp.PrivateSector)
END IF
END
}
GO
CREATE TRIGGER "CompanyKeyWordsInsert" after INSERT ORDER 20 ON
"pears"."Company"
REFERENCING NEW AS "NewRow"
FOR each ROW
BEGIN
INSERT INTO "CompanyKeyWords"( "CompanyID","RefreshRequired" ) ON existing UPDATE defaults off VALUES( "NewRow"."CompanyID",1 )
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."CompanyKeyWordsInsert" IS
{CREATE TRIGGER CompanyKeyWordsInsert
after INSERT ORDER 20 ON
pears.Company
REFERENCING NEW AS NewRow
FOR each ROW
BEGIN
INSERT INTO CompanyKeyWords(CompanyID,RefreshRequired) ON existing UPDATE VALUES(NewRow.CompanyID,1);
END
}
GO
CREATE TRIGGER "CompanyKeyWordsUpdate" after UPDATE OF "Name",
"Addr1","Addr2","Addr3","Town","County","PostCode" ORDER 21 ON
"pears"."Company"
REFERENCING NEW AS "NewRow"
FOR each ROW
BEGIN
UPDATE "CompanyKeyWords" SET "RefreshRequired" = 1 WHERE "CompanyID" = "NewRow"."CompanyID"
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Company"."CompanyKeyWordsUpdate" IS
{CREATE TRIGGER CompanyKeyWordsUpdate
after UPDATE OF Name,Addr1,Addr2,Addr3,Town,County,PostCode ORDER 21 ON
pears.Company
REFERENCING NEW AS NewRow
FOR each ROW
BEGIN
UPDATE CompanyKeyWords SET RefreshRequired = 1 WHERE CompanyID = NewRow.CompanyID;
END
}
GO