database:tables:pears_companyaccount



pears.CompanyAccount

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Description

Holds details of Client Account - only exists for companies with allocated account code.

Columns

Column Type Null Default Comment
CompanyID char(20) NOT NULL
NotLtdCo tinyint NULL 0
RegNo char(20) NULL
VatExempt tinyint NULL 0
VATNumber char(20) NULL
ERNIonInvoice tinyint NULL 0
TheirRefRequired tinyint NULL 0
GroupInvoice tinyint NULL 0 0=1 inv per timesheet 1=1 inv per client
Construction tinyint NULL 0
DaysCredit smallint NULL
InvoiceEmail char(250) NULL
AccountsContact char(20) NULL
TimesheetContact char(20) NULL
TransferBatch integer NULL
ExtNumber integer NULL
CreditLimit integer NULL
OnStop tinyint NULL 0
InvoiceFrequency char(1) NULL <W>eekly, <2> weekly, <4> weekly, <M>onthly
VatOnTempMargin tinyint NULL 0
BlockStatus tinyint NULL Used by LSM auto update file. 0=no block 1=Warn 2=Warn if Manager otherwise block 3=Block
BlockDescription char(50) NULL Used by LSM auto update file. Descriptive text for warning or block message
ETimesheets tinyint NULL 0
TempETimesheetEditCapability tinyint NULL 1=Confirm Only 2=Edit Existing Shifts 3=Add Shifts
TheirRefRequiredInvoice tinyint NULL 0
documenttemplateid char(12) NULL
PDFInvCreate tinyint NULL 0
PDFInvIncTS tinyint NULL 0
InvoiceManagementGroup char(1) NULL
FixedNI double NULL %
FixedWTR double NULL %
NoTSBarcode tinyint NULL 0
PDFExtraDocs smallint NULL 0/null - none 1 - std TS image 2 - std + 1 …
StatementEmail char(250) NULL
AWRFixedNI double NULL %
AWRFixedWTR double NULL %
statementfrequency char(1) NULL =None W=Weekly M=Monthly
NoPDFSecurity tinyint NULL 0
NotPDFInvIncTS tinyint NULL 0
NotImageInvIncTS tinyint NULL 0
AdhocDocumentTemplateID char(12) NULL

Primary Key

  • CompanyID

Foreign Keys

Constraint Columns References Delete/update action
Company CompanyID pears.Company (companyid) NOT NULL;
Employment AccountsContact pears.employment (employmentid) ON DELETE SET NULL
Employment2 TimesheetContact pears.employment (employmentid) ON DELETE SET NULL
iqacdocumenttemplate documenttemplateid pears.IQacDocumentTemplate (DocumentTemplateID) ON DELETE SET NULL

Referenced By

  • No incoming foreign keys found.

Indexes

Name Type Columns Detail
coac_extnumber Index ExtNumber
coac_transferbatch Index TransferBatch
coac_AdhocInvoice Index AdhocDocumentTemplateID

Triggers

Name Timing Event
companyaccountUpdate before update of “NotLtdCo”, “RegNo”,“VatExempt”,“VATNumber”,“ERNIonInvoice”,“TheirRefRequired”,“GroupInvoice”,“Construction”,“DaysCredit”,“InvoiceEmail”,“AccountsContact”,“TimesheetContact”, “ExtNumber”,“OnStop”,“InvoiceFrequency”,“VatOnTempMargin” order 1
CompanyAccountAudit before update of “NotLtdCo”, “RegNo”,“VatExempt”,“VATNumber”,“ERNIonInvoice”,“TheirRefRequired”,“GroupInvoice”,“Construction”,“DaysCredit”,“InvoiceEmail”,“AccountsContact”,“TimesheetContact”,“ExtNumber”,“CreditLimit”,“OnStop”,“InvoiceFrequency”,“VatOnTempMargin”,“BlockStatus”,“BlockDescription”, “ETimesheets”,“documenttemplateid”,“PDFInvCreate”,“PDFInvIncTS”,“TheirRefRequiredInvoice”,“InvoiceManagementGroup”,“FixedNI”,“FixedWTR”,“PDFExtraDocs”,“AWRFixedNI”,“AWRFixedWTR” order 2

Original SQL

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."CompanyAccount"
-- Table comment: Holds details of Client Account - only exists for companies with allocated account code.
-- Statement count: 24
 
CREATE TABLE "pears"."CompanyAccount" (
    "CompanyID"                      CHAR(20) NOT NULL
   ,"NotLtdCo"                       tinyint NULL DEFAULT 0
   ,"RegNo"                          CHAR(20) NULL
   ,"VatExempt"                      tinyint NULL DEFAULT 0
   ,"VATNumber"                      CHAR(20) NULL
   ,"ERNIonInvoice"                  tinyint NULL DEFAULT 0
   ,"TheirRefRequired"               tinyint NULL DEFAULT 0
   ,"GroupInvoice"                   tinyint NULL DEFAULT 0
   ,"Construction"                   tinyint NULL DEFAULT 0
   ,"DaysCredit"                     SMALLINT NULL
   ,"InvoiceEmail"                   CHAR(250) NULL
   ,"AccountsContact"                CHAR(20) NULL
   ,"TimesheetContact"               CHAR(20) NULL
   ,"TransferBatch"                  INTEGER NULL
   ,"ExtNumber"                      INTEGER NULL
   ,"CreditLimit"                    INTEGER NULL
   ,"OnStop"                         tinyint NULL DEFAULT 0
   ,"InvoiceFrequency"               CHAR(1) NULL
   ,"VatOnTempMargin"                tinyint NULL DEFAULT 0
   ,"BlockStatus"                    tinyint NULL
   ,"BlockDescription"               CHAR(50) NULL
   ,"ETimesheets"                    tinyint NULL DEFAULT 0
   ,"TempETimesheetEditCapability"   tinyint NULL
   ,"TheirRefRequiredInvoice"        tinyint NULL DEFAULT 0
   ,"documenttemplateid"             CHAR(12) NULL
   ,"PDFInvCreate"                   tinyint NULL DEFAULT 0
   ,"PDFInvIncTS"                    tinyint NULL DEFAULT 0
   ,"InvoiceManagementGroup"         CHAR(1) NULL
   ,"FixedNI"                        DOUBLE NULL
   ,"FixedWTR"                       DOUBLE NULL
   ,"NoTSBarcode"                    tinyint NULL DEFAULT 0
   ,"PDFExtraDocs"                   SMALLINT NULL
   ,"StatementEmail"                 CHAR(250) NULL
   ,"AWRFixedNI"                     DOUBLE NULL
   ,"AWRFixedWTR"                    DOUBLE NULL
   ,"statementfrequency"             CHAR(1) NULL
   ,"NoPDFSecurity"                  tinyint NULL DEFAULT 0
   ,"NotPDFInvIncTS"                 tinyint NULL DEFAULT 0
   ,"NotImageInvIncTS"               tinyint NULL DEFAULT 0
   ,"AdhocDocumentTemplateID"        CHAR(12) NULL
   ,PRIMARY KEY ("CompanyID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."GroupInvoice" IS 
	'0=1 inv per timesheet 1=1 inv per client'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."InvoiceFrequency" IS 
	'<W>eekly, <2> weekly, <4> weekly, <M>onthly'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."BlockStatus" IS 
	'Used by LSM auto update file. 0=no block 1=Warn 2=Warn if Manager otherwise block 3=Block'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."BlockDescription" IS 
	'Used by LSM auto update file. Descriptive text for warning or block message'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."TempETimesheetEditCapability" IS 
	'1=Confirm Only 2=Edit Existing Shifts 3=Add Shifts'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."FixedNI" IS 
	'%'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."FixedWTR" IS 
	'%'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."PDFExtraDocs" IS 
	'0/null - none 1 - std TS image 2 - std + 1 ... '
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."AWRFixedNI" IS 
	'%'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."AWRFixedWTR" IS 
	'%'
GO
 
 
COMMENT ON COLUMN "pears"."CompanyAccount"."statementfrequency" IS 
	'=None W=Weekly M=Monthly'
GO
 
 
COMMENT ON TABLE "pears"."CompanyAccount" IS 
	'Holds details of Client Account - only exists for companies with allocated account code.'
GO
 
 
ALTER TABLE "pears"."CompanyAccount"
    ADD NOT NULL FOREIGN KEY "Company" ("CompanyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
GO
 
 
ALTER TABLE "pears"."CompanyAccount"
    ADD FOREIGN KEY "Employment" ("AccountsContact" ASC)
    REFERENCES "pears"."employment" ("employmentid")
    ON DELETE SET NULL
GO
 
 
ALTER TABLE "pears"."CompanyAccount"
    ADD FOREIGN KEY "Employment2" ("TimesheetContact" ASC)
    REFERENCES "pears"."employment" ("employmentid")
    ON DELETE SET NULL
GO
 
 
ALTER TABLE "pears"."CompanyAccount"
    ADD FOREIGN KEY "iqacdocumenttemplate" ("documenttemplateid" ASC)
    REFERENCES "pears"."IQacDocumentTemplate" ("DocumentTemplateID")
    ON DELETE SET NULL
GO
 
 
CREATE INDEX "coac_extnumber" ON "pears"."CompanyAccount"
    ( "ExtNumber" )
GO
 
 
CREATE INDEX "coac_transferbatch" ON "pears"."CompanyAccount"
    ( "TransferBatch" )
GO
 
 
CREATE INDEX "coac_AdhocInvoice" ON "pears"."CompanyAccount"
    ( "AdhocDocumentTemplateID" )
GO
 
 
CREATE TRIGGER "companyaccountUpdate" BEFORE UPDATE OF "NotLtdCo",
"RegNo","VatExempt","VATNumber","ERNIonInvoice","TheirRefRequired","GroupInvoice","Construction","DaysCredit","InvoiceEmail","AccountsContact","TimesheetContact",
"ExtNumber","OnStop","InvoiceFrequency","VatOnTempMargin" ORDER 1 ON
"pears"."CompanyAccount"
REFERENCING OLD AS "old_coac" NEW AS "new_coac"
FOR each ROW
WHEN("old_coac"."transferbatch" > 0)
BEGIN
  SET "new_coac"."transferbatch" = 0
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."CompanyAccount"."companyaccountUpdate" IS 
{CREATE TRIGGER companyaccountUpdate 
 BEFORE UPDATE OF NotLtdCo,
RegNo,VatExempt,VATNumber,ERNIonInvoice,TheirRefRequired,GroupInvoice,Construction,DaysCredit,InvoiceEmail,AccountsContact,TimesheetContact,
ExtNumber,OnStop,InvoiceFrequency,VatOnTempMargin ORDER 1 ON
pears.CompanyAccount
REFERENCING OLD AS old_coac NEW AS new_coac
FOR each ROW
WHEN(old_coac.transferbatch > 0)
BEGIN
  SET new_coac.transferbatch=0
END
}
GO
 
 
CREATE TRIGGER "CompanyAccountAudit" BEFORE UPDATE OF "NotLtdCo",
"RegNo","VatExempt","VATNumber","ERNIonInvoice","TheirRefRequired","GroupInvoice","Construction","DaysCredit","InvoiceEmail","AccountsContact","TimesheetContact","ExtNumber","CreditLimit","OnStop","InvoiceFrequency","VatOnTempMargin","BlockStatus","BlockDescription",
"ETimesheets","documenttemplateid","PDFInvCreate","PDFInvIncTS","TheirRefRequiredInvoice","InvoiceManagementGroup","FixedNI","FixedWTR","PDFExtraDocs","AWRFixedNI","AWRFixedWTR" ORDER 2 ON "pears"."CompanyAccount"
REFERENCING OLD AS "old_comp" NEW AS "new_comp"
FOR each ROW
WHEN(EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Company Account' 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 Account' AND "AuditFlag" = 1;
  SET @CompName = (SELECT "name" FROM "company" WHERE "Company"."CompanyID" = "old_comp"."CompanyID");
  --  Not Limited Company
  IF "locate"(@AuditList,',Not Limited Company,') > 0 AND UPDATE("NotLtdCo") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Not Limited Company Updated - ',@CompName),"old_comp"."NotLtdCo","new_comp"."NotLtdCo")
  END IF;
  --  Registered Number
  IF "locate"(@AuditList,',Registered Number,') > 0 AND UPDATE("RegNo") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Registered Number Updated - ',@CompName),"old_comp"."RegNo","new_comp"."RegNo")
  END IF;
  --  Vat Exempt
  IF "locate"(@AuditList,',Vat Exempt,') > 0 AND UPDATE("VatExempt") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Vat Exempt Updated - ',@CompName),"old_comp"."VatExempt","new_comp"."VatExempt")
  END IF;
  --  VAT Number
  IF "locate"(@AuditList,',VAT Number,') > 0 AND UPDATE("VATNumber") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('VAT Number Updated - ',@CompName),"old_comp"."VATNumber","new_comp"."VATNumber")
  END IF;
  --  ERNI On Invoice
  IF "locate"(@AuditList,',ERNI On Invoice,') > 0 AND UPDATE("ERNIonInvoice") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('ERNI On Invoice Updated - ',@CompName),"old_comp"."ERNIonInvoice","new_comp"."ERNIonInvoice")
  END IF;
  --  TheirRef Required
  IF "locate"(@AuditList,',TheirRef Required,') > 0 AND UPDATE("TheirRefRequired") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('TheirRef Required Updated - ',@CompName),"old_comp"."TheirRefRequired","new_comp"."TheirRefRequired")
  END IF;
  --  Group Invoice
  IF "locate"(@AuditList,',Group Invoice,') > 0 AND UPDATE("GroupInvoice") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Group Invoice Updated - ',@CompName),"old_comp"."GroupInvoice","new_comp"."GroupInvoice")
  END IF;
  --  Construction
  IF "locate"(@AuditList,',Construction,') > 0 AND UPDATE("Construction") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Construction Updated - ',@CompName),"old_comp"."Construction","new_comp"."Construction")
  END IF;
  --  Days Credit
  IF "locate"(@AuditList,',Days Credit,') > 0 AND UPDATE("DaysCredit") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Days Credit Updated - ',@CompName),"old_comp"."DaysCredit","new_comp"."DaysCredit")
  END IF;
  --  Invoice Email
  IF "locate"(@AuditList,',Invoice Email,') > 0 AND UPDATE("InvoiceEmail") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Invoice Email Updated - ',@CompName),"old_comp"."InvoiceEmail","new_comp"."InvoiceEmail")
  END IF;
  --  Accounts Contact
  IF "locate"(@AuditList,',Accounts Contact,') > 0 AND UPDATE("AccountsContact") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Accounts Contact Updated - ',@CompName),"old_comp"."AccountsContact","new_comp"."AccountsContact")
  END IF;
  --  Timesheet Contact
  IF "locate"(@AuditList,',Timesheet Contact,') > 0 AND UPDATE("TimesheetContact") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Timesheet Contact Updated - ',@CompName),"old_comp"."TimesheetContact","new_comp"."TimesheetContact")
  END IF;
  --  Ext Number
  IF "locate"(@AuditList,',Ext Number,') > 0 AND UPDATE("ExtNumber") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Ext Number Updated - ',@CompName),"old_comp"."ExtNumber","new_comp"."ExtNumber")
  END IF;
  --  Credit Limit
  IF "locate"(@AuditList,',Credit Limit,') > 0 AND UPDATE("CreditLimit") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Credit Limit Updated - ',@CompName),"old_comp"."CreditLimit","new_comp"."CreditLimit")
  END IF;
  --  On Stop
  IF "locate"(@AuditList,',On Stop,') > 0 AND UPDATE("OnStop") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('On Stop Updated - ',@CompName),"old_comp"."OnStop","new_comp"."OnStop")
  END IF;
  --  Invoice Frequency
  IF "locate"(@AuditList,',Invoice Frequency,') > 0 AND UPDATE("InvoiceFrequency") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Invoice Frequency Updated - ',@CompName),"old_comp"."InvoiceFrequency","new_comp"."InvoiceFrequency")
  END IF;
  --  Vat On Temp Margin
  IF "locate"(@AuditList,',Vat On Temp Margin,') > 0 AND UPDATE("VatOnTempMargin") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Vat On Temp Margin Updated - ',@CompName),"old_comp"."VatOnTempMargin","new_comp"."VatOnTempMargin")
  END IF;
  --  Block Status
  IF "locate"(@AuditList,',Block Status,') > 0 AND UPDATE("BlockStatus") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Block Status Updated - ',@CompName),"old_comp"."BlockStatus","new_comp"."BlockStatus")
  END IF;
  --  Block Description
  IF "locate"(@AuditList,',Block Description,') > 0 AND UPDATE("BlockDescription") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Block Description Updated - ',@CompName),"old_comp"."BlockDescription","new_comp"."BlockDescription")
  END IF;
  --  ETimesheets
  IF "locate"(@AuditList,',ETimesheets,') > 0 AND UPDATE("ETimesheets") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('ETimesheets Updated - ',@CompName),"old_comp"."ETimesheets","new_comp"."ETimesheets")
  END IF;
  --  Override Invoice Layout
  IF "locate"(@AuditList,',Override Invoice Layout,') > 0 AND UPDATE("documenttemplateid") THEN
    SET @OldDescrip = (SELECT "name" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "old_comp"."documenttemplateid");
    SET @NewDescrip = (SELECT "name" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "new_comp"."documenttemplateid");
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Override Invoice Layout Updated - ',@CompName),@OldDescrip,@NewDescrip)
  END IF;
  --  Invoice PDF Create
  IF "locate"(@AuditList,',Invoice PDF Create,') > 0 AND UPDATE("PDFInvCreate") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Invoice PDF Create Updated - ',@CompName),"old_comp"."PDFInvCreate","new_comp"."PDFInvCreate")
  END IF;
  --  PDF Invoice add Timesheets
  IF "locate"(@AuditList,',PDF Invoice add Timesheets,') > 0 AND UPDATE("PDFInvIncTS") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('PDF Invoice add Timesheets Updated - ',@CompName),"old_comp"."PDFInvIncTS","new_comp"."PDFInvIncTS")
  END IF;
  --  Their Ref Required
  IF "locate"(@AuditList,',Their Ref Required,') > 0 AND UPDATE("TheirRefRequiredInvoice") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Their Ref Required Updated - ',@CompName),"old_comp"."TheirRefRequiredInvoice","new_comp"."TheirRefRequiredInvoice")
  END IF;
  --  Invoice Management Group
  IF "locate"(@AuditList,',Invoice Management Group,') > 0 AND UPDATE("InvoiceManagementGroup") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Invoice Management Group Updated - ',@CompName),"old_comp"."InvoiceManagementGroup","new_comp"."InvoiceManagementGroup")
  END IF;
  --  Fixed NI
  IF "locate"(@AuditList,',Fixed NI,') > 0 AND UPDATE("FixedNI") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Fixed NI Updated - ',@CompName),"old_comp"."FixedNI","new_comp"."FixedNI")
  END IF;
  --  Fixed WTR
  IF "locate"(@AuditList,',Fixed WTR,') > 0 AND UPDATE("FixedWTR") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('Fixed WTR Updated - ',@CompName),"old_comp"."FixedWTR","new_comp"."FixedWTR")
  END IF;
  IF "locate"(@AuditList,',AWR Fixed NI,') > 0 AND UPDATE("AWRFixedNI") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('AWR Fixed NI Updated - ',@CompName),"old_comp"."AWRFixedNI","new_comp"."AWRFixedNI")
  END IF;
  --  Fixed WTR
  IF "locate"(@AuditList,',AWR Fixed WTR,') > 0 AND UPDATE("AWRFixedWTR") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('AWR Fixed WTR Updated - ',@CompName),"old_comp"."AWRFixedWTR","new_comp"."AWRFixedWTR")
  END IF;
  --  PDF must have Documents
  IF "locate"(@AuditList,',PDF must have Documents,') > 0 AND UPDATE("PDFExtraDocs") THEN
    CALL "AuditLog"('COMPANY',"old_comp"."CompanyID","string"('PDF must have Documents Updated - ',@CompName),"old_comp"."PDFExtraDocs","new_comp"."PDFExtraDocs")
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."CompanyAccount"."CompanyAccountAudit" IS 
{CREATE TRIGGER CompanyAccountAudit 
 BEFORE UPDATE OF NotLtdCo,RegNo,VatExempt,VATNumber,ERNIonInvoice,TheirRefRequired,GroupInvoice,Construction,DaysCredit,InvoiceEmail,AccountsContact,TimesheetContact,ExtNumber,CreditLimit,OnStop,InvoiceFrequency,VatOnTempMargin,BlockStatus,BlockDescription,
ETimesheets,documenttemplateid,PDFInvCreate,PDFInvIncTS,TheirRefRequiredInvoice,InvoiceManagementGroup,FixedNI,FixedWTR,PDFExtraDocs,  AWRFixedNI,AWRFixedWTR ORDER 2 ON pears.CompanyAccount
REFERENCING OLD AS old_comp NEW AS new_comp
FOR each ROW
WHEN(EXISTS(SELECT* FROM AuditItems WHERE AreaName = 'Company Account' 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 Account' AND AuditFlag = 1;
  SET @CompName=(SELECT name FROM company WHERE Company.CompanyID = old_comp.CompanyID);
  --  Not Limited Company
  IF locate(@AuditList,',Not Limited Company,') > 0 AND UPDATE(NotLtdCo) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Not Limited Company Updated - ',@CompName),old_comp.NotLtdCo,new_comp.NotLtdCo)
  END IF;
  --  Registered Number
  IF locate(@AuditList,',Registered Number,') > 0 AND UPDATE(RegNo) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Registered Number Updated - ',@CompName),old_comp.RegNo,new_comp.RegNo)
  END IF;
  --  Vat Exempt
  IF locate(@AuditList,',Vat Exempt,') > 0 AND UPDATE(VatExempt) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Vat Exempt Updated - ',@CompName),old_comp.VatExempt,new_comp.VatExempt)
  END IF;
  --  VAT Number
  IF locate(@AuditList,',VAT Number,') > 0 AND UPDATE(VATNumber) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('VAT Number Updated - ',@CompName),old_comp.VATNumber,new_comp.VATNumber)
  END IF;
  --  ERNI On Invoice
  IF locate(@AuditList,',ERNI On Invoice,') > 0 AND UPDATE(ERNIonInvoice) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('ERNI On Invoice Updated - ',@CompName),old_comp.ERNIonInvoice,new_comp.ERNIonInvoice)
  END IF;
  --  TheirRef Required
  IF locate(@AuditList,',TheirRef Required,') > 0 AND UPDATE(TheirRefRequired) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('TheirRef Required Updated - ',@CompName),old_comp.TheirRefRequired,new_comp.TheirRefRequired)
  END IF;
  --  Group Invoice
  IF locate(@AuditList,',Group Invoice,') > 0 AND UPDATE(GroupInvoice) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Group Invoice Updated - ',@CompName),old_comp.GroupInvoice,new_comp.GroupInvoice)
  END IF;
  --  Construction
  IF locate(@AuditList,',Construction,') > 0 AND UPDATE(Construction) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Construction Updated - ',@CompName),old_comp.Construction,new_comp.Construction)
  END IF;
  --  Days Credit
  IF locate(@AuditList,',Days Credit,') > 0 AND UPDATE(DaysCredit) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Days Credit Updated - ',@CompName),old_comp.DaysCredit,new_comp.DaysCredit)
  END IF;
  --  Invoice Email
  IF locate(@AuditList,',Invoice Email,') > 0 AND UPDATE(InvoiceEmail) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Invoice Email Updated - ',@CompName),old_comp.InvoiceEmail,new_comp.InvoiceEmail)
  END IF;
  --  Accounts Contact
  IF locate(@AuditList,',Accounts Contact,') > 0 AND UPDATE(AccountsContact) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Accounts Contact Updated - ',@CompName),old_comp.AccountsContact,new_comp.AccountsContact)
  END IF;
  --  Timesheet Contact
  IF locate(@AuditList,',Timesheet Contact,') > 0 AND UPDATE(TimesheetContact) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Timesheet Contact Updated - ',@CompName),old_comp.TimesheetContact,new_comp.TimesheetContact)
  END IF;
  --  Ext Number
  IF locate(@AuditList,',Ext Number,') > 0 AND UPDATE(ExtNumber) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Ext Number Updated - ',@CompName),old_comp.ExtNumber,new_comp.ExtNumber)
  END IF;
  --  Credit Limit
  IF locate(@AuditList,',Credit Limit,') > 0 AND UPDATE(CreditLimit) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Credit Limit Updated - ',@CompName),old_comp.CreditLimit,new_comp.CreditLimit)
  END IF;
  --  On Stop
  IF locate(@AuditList,',On Stop,') > 0 AND UPDATE(OnStop) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('On Stop Updated - ',@CompName),old_comp.OnStop,new_comp.OnStop)
  END IF;
  --  Invoice Frequency
  IF locate(@AuditList,',Invoice Frequency,') > 0 AND UPDATE(InvoiceFrequency) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Invoice Frequency Updated - ',@CompName),old_comp.InvoiceFrequency,new_comp.InvoiceFrequency)
  END IF;
  --  Vat On Temp Margin
  IF locate(@AuditList,',Vat On Temp Margin,') > 0 AND UPDATE(VatOnTempMargin) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Vat On Temp Margin Updated - ',@CompName),old_comp.VatOnTempMargin,new_comp.VatOnTempMargin)
  END IF;
  --  Block Status
  IF locate(@AuditList,',Block Status,') > 0 AND UPDATE(BlockStatus) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Block Status Updated - ',@CompName),old_comp.BlockStatus,new_comp.BlockStatus)
  END IF;
  --  Block Description
  IF locate(@AuditList,',Block Description,') > 0 AND UPDATE(BlockDescription) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Block Description Updated - ',@CompName),old_comp.BlockDescription,new_comp.BlockDescription)
  END IF;
  --  ETimesheets
  IF locate(@AuditList,',ETimesheets,') > 0 AND UPDATE(ETimesheets) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('ETimesheets Updated - ',@CompName),old_comp.ETimesheets,new_comp.ETimesheets)
  END IF;
  --  Override Invoice Layout
  IF locate(@AuditList,',Override Invoice Layout,') > 0 AND UPDATE(documenttemplateid) THEN
  SET @OldDescrip=(SELECT name FROM iqacdocumenttemplate WHERE documenttemplateid = old_comp.documenttemplateid);
  SET @NewDescrip=(SELECT name FROM iqacdocumenttemplate WHERE documenttemplateid = new_comp.documenttemplateid);
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Override Invoice Layout Updated - ',@CompName),@OldDescrip,@NewDescrip)
  END IF;
  --  Invoice PDF Create
  IF locate(@AuditList,',Invoice PDF Create,') > 0 AND UPDATE(PDFInvCreate) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Invoice PDF Create Updated - ',@CompName),old_comp.PDFInvCreate,new_comp.PDFInvCreate)
  END IF;
  --  PDF Invoice add Timesheets
  IF locate(@AuditList,',PDF Invoice add Timesheets,') > 0 AND UPDATE(PDFInvIncTS) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('PDF Invoice add Timesheets Updated - ',@CompName),old_comp.PDFInvIncTS,new_comp.PDFInvIncTS)
  END IF;
  --  Their Ref Required
  IF locate(@AuditList,',Their Ref Required,') > 0 AND UPDATE(TheirRefRequiredInvoice) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Their Ref Required Updated - ',@CompName),old_comp.TheirRefRequiredInvoice,new_comp.TheirRefRequiredInvoice)
  END IF;
  --  Invoice Management Group
  IF locate(@AuditList,',Invoice Management Group,') > 0 AND UPDATE(InvoiceManagementGroup) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Invoice Management Group Updated - ',@CompName),old_comp.InvoiceManagementGroup,new_comp.InvoiceManagementGroup)
  END IF;
  --  Fixed NI
  IF locate(@AuditList,',Fixed NI,') > 0 AND UPDATE(FixedNI) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Fixed NI Updated - ',@CompName),old_comp.FixedNI,new_comp.FixedNI)
  END IF;
  --  Fixed WTR
  IF locate(@AuditList,',Fixed WTR,') > 0 AND UPDATE(FixedWTR) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('Fixed WTR Updated - ',@CompName),old_comp.FixedWTR,new_comp.FixedWTR)
  END IF;
  IF locate(@AuditList,',AWR Fixed NI,') > 0 AND UPDATE(AWRFixedNI) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('AWR Fixed NI Updated - ',@CompName),old_comp.AWRFixedNI,new_comp.AWRFixedNI)
  END IF;
  --  Fixed WTR
  IF locate(@AuditList,',AWR Fixed WTR,') > 0 AND UPDATE(AWRFixedWTR) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('AWR Fixed WTR Updated - ',@CompName),old_comp.AWRFixedWTR,new_comp.AWRFixedWTR)
  END IF;
  --  PDF must have Documents
  IF locate(@AuditList,',PDF must have Documents,') > 0 AND UPDATE(PDFExtraDocs) THEN
    CALL AuditLog('COMPANY',old_comp.CompanyID,string('PDF must have Documents Updated - ',@CompName),old_comp.PDFExtraDocs,new_comp.PDFExtraDocs)
  END IF
END
}
GO
  • database/tables/pears_companyaccount.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1