IQX Quick Menu

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

Description

List of Composite Payroll Companies.

Column	Type	Null	Default	Comment
CompositeId	char(20)	NOT NULL
CompositePayCoReference	char(60)	NULL
VATNumber	char(20)	NULL
RegNo	char(20)	NULL
CompanyName	char(50)	NULL
BankRef	char(20)	NULL
BankAcNo	char(15)	NULL
BankAcName	char(50)	NULL
BankSortCode	char(8)	NULL
BankName	char(30)	NULL
TemplateName	char(30)	NULL
PayrollAddress	long varchar	NULL
VATRegistered	tinyint	NULL	0
TaxMethod	tinyint	NULL
PayFrequency	tinyint	NULL
FlagPayMethod	tinyint	NULL	0
FlagPayrollAddress	tinyint	NULL	0
FlagPayCoReference	tinyint	NULL	0
FlagVATNumber	tinyint	NULL	0
FlagVATRegistered	tinyint	NULL	0
FlagRegNo	tinyint	NULL	0
FlagCompanyName	tinyint	NULL	0
FlagTaxMethod	tinyint	NULL	0
FlagPayFrequency	tinyint	NULL	0
FlagBankRef	tinyint	NULL	0
FlagBankAcNo	tinyint	NULL	0
FlagBankAcName	tinyint	NULL	0
FlagBankSortCode	tinyint	NULL	0
FlagBankName	tinyint	NULL	0
PayMethod	tinyint	NULL
FlagIBAN	tinyint	NULL
IBAN	char(34)	NULL
FlagSWIFTBIC	tinyint	NULL
SWIFTBIC	char(11)	NULL
DirectlyEmployed	tinyint	NOT NULL	0
FlagDirectlyEmployed	tinyint	NULL	0
FlagPayDistribution	tinyint	NULL
PayDistribution	char(4)	NULL
HMRCEngagementDetail	char(1)	NULL
PayrollAddr1	char(40)	NULL
PayrollAddr2	char(40)	NULL
PayrollAddr3	char(40)	NULL
PayrollAddr4	char(40)	NULL
PayrollPostcode	char(30)	NULL
SupplierCode	char(12)	NULL		Self bill account for umbrella etc.
FlagSupplierCode	tinyint	NULL	0
EmailAddress	char(250)	NULL
FlagEmailAddress	tinyint	NULL	0
FlagHMRCEngagementDetail	tinyint	NULL
AnalysisCode	char(5)	NULL		Can be sent to Accord.

CompositeId

No outgoing foreign keys found.

Table	Constraint	Columns	Referenced columns
pears.Pay_Employee	Composite	CompositeID	CompositeId

No indexes found.

Name	Timing	Event
CompositeSelfBill	after	insert,update order 1
CompositeAudit	before	update of “CompositeId”, “CompositePayCoReference”,“VATNumber”,“RegNo”,“CompanyName”,“BankRef”,“BankAcNo”,“BankAcName”,“BankSortCode”,“BankName”, “TemplateName”,“PayrollAddress”,“VATRegistered”,“TaxMethod”,“PayFrequency”,“FlagPayMethod”,“FlagPayrollAddress”,“FlagPayCoReference”, “FlagVATNumber”,“FlagVATRegistered”,“FlagRegNo”,“FlagCompanyName”,“FlagTaxMethod”,“FlagPayFrequency”,“FlagBankRef”, “FlagBankAcNo”,“FlagBankAcName”,“FlagBankSortCode”,“FlagBankName”,“PayMethod”,“FlagIBAN”,“IBAN”,“FlagSWIFTBIC”, “SWIFTBIC”,“DirectlyEmployed”,“FlagDirectlyEmployed”,“FlagPayDistribution”,“PayDistribution”,“HMRCEngagementDetail”, “PayrollAddr1”,“PayrollAddr2”,“PayrollAddr3”,“PayrollAddr4”,“PayrollPostcode”,“SupplierCode”,“FlagSupplierCode”, “EmailAddress”,“FlagEmailAddress”,“FlagHMRCEngagementDetail”,“AnalysisCode” order 1

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Composite"
-- Table comment: List of Composite Payroll Companies.
-- Statement count: 8
 
CREATE TABLE "pears"."Composite" (
    "CompositeId"                    CHAR(20) NOT NULL
   ,"CompositePayCoReference"        CHAR(60) NULL
   ,"VATNumber"                      CHAR(20) NULL
   ,"RegNo"                          CHAR(20) NULL
   ,"CompanyName"                    CHAR(50) NULL
   ,"BankRef"                        CHAR(20) NULL
   ,"BankAcNo"                       CHAR(15) NULL
   ,"BankAcName"                     CHAR(50) NULL
   ,"BankSortCode"                   CHAR(8) NULL
   ,"BankName"                       CHAR(30) NULL
   ,"TemplateName"                   CHAR(30) NULL
   ,"PayrollAddress"                 long VARCHAR NULL
   ,"VATRegistered"                  tinyint NULL DEFAULT 0
   ,"TaxMethod"                      tinyint NULL
   ,"PayFrequency"                   tinyint NULL
   ,"FlagPayMethod"                  tinyint NULL DEFAULT 0
   ,"FlagPayrollAddress"             tinyint NULL DEFAULT 0
   ,"FlagPayCoReference"             tinyint NULL DEFAULT 0
   ,"FlagVATNumber"                  tinyint NULL DEFAULT 0
   ,"FlagVATRegistered"              tinyint NULL DEFAULT 0
   ,"FlagRegNo"                      tinyint NULL DEFAULT 0
   ,"FlagCompanyName"                tinyint NULL DEFAULT 0
   ,"FlagTaxMethod"                  tinyint NULL DEFAULT 0
   ,"FlagPayFrequency"               tinyint NULL DEFAULT 0
   ,"FlagBankRef"                    tinyint NULL DEFAULT 0
   ,"FlagBankAcNo"                   tinyint NULL DEFAULT 0
   ,"FlagBankAcName"                 tinyint NULL DEFAULT 0
   ,"FlagBankSortCode"               tinyint NULL DEFAULT 0
   ,"FlagBankName"                   tinyint NULL DEFAULT 0
   ,"PayMethod"                      tinyint NULL
   ,"FlagIBAN"                       tinyint NULL
   ,"IBAN"                           CHAR(34) NULL
   ,"FlagSWIFTBIC"                   tinyint NULL
   ,"SWIFTBIC"                       CHAR(11) NULL
   ,"DirectlyEmployed"               tinyint NOT NULL DEFAULT 0
   ,"FlagDirectlyEmployed"           tinyint NULL DEFAULT 0
   ,"FlagPayDistribution"            tinyint NULL
   ,"PayDistribution"                CHAR(4) NULL
   ,"HMRCEngagementDetail"           CHAR(1) NULL
   ,"PayrollAddr1"                   CHAR(40) NULL
   ,"PayrollAddr2"                   CHAR(40) NULL
   ,"PayrollAddr3"                   CHAR(40) NULL
   ,"PayrollAddr4"                   CHAR(40) NULL
   ,"PayrollPostcode"                CHAR(30) NULL
   ,"SupplierCode"                   CHAR(12) NULL
   ,"FlagSupplierCode"               tinyint NULL DEFAULT 0
   ,"EmailAddress"                   CHAR(250) NULL
   ,"FlagEmailAddress"               tinyint NULL DEFAULT 0
   ,"FlagHMRCEngagementDetail"       tinyint NULL
   ,"AnalysisCode"                   CHAR(5) NULL
   ,PRIMARY KEY ("CompositeId" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."Composite"."SupplierCode" IS 
	'Self bill account for umbrella etc.'
GO
 
 
COMMENT ON COLUMN "pears"."Composite"."AnalysisCode" IS 
	'Can be sent to Accord.'
GO
 
 
COMMENT ON TABLE "pears"."Composite" IS 
	'List of Composite Payroll Companies.'
GO
 
 
CREATE TRIGGER "CompositeSelfBill" after INSERT,UPDATE ORDER 1 ON
"pears"."Composite"
REFERENCING NEW AS "NewCompo"
FOR each ROW WHEN("isnull"("newcompo"."suppliercode",'') <> '')
BEGIN
  IF NOT EXISTS(SELECT * FROM "iqacaccount" WHERE "ledgerid" = 'Purchase' AND "accountcode" = "newcompo"."suppliercode") THEN
    INSERT INTO "iqacaccount"( "ledgerid","accountcode","name" ) VALUES( 'Purchase',"newcompo"."suppliercode","newcompo"."templatename" ) ;
    INSERT INTO "iqacaccountsettings"( "ledgerid","accountcode","addr1","addr2","addr3","postcode","VATNumber","RegNo","email","banksortcode","bankacno" ) VALUES
      ( 'Purchase',"newcompo"."suppliercode","newcompo"."payrolladdr1","newcompo"."payrolladdr2","newcompo"."payrolladdr3","newcompo"."payrollpostcode",
      "newcompo"."VATNumber","newcompo"."RegNo","newcompo"."emailaddress","newcompo"."banksortcode","newcompo"."bankacno" ) 
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Composite"."CompositeSelfBill" IS 
{CREATE TRIGGER CompositeSelfBill 
 after INSERT,UPDATE ORDER 1 ON
"pears"."Composite"
REFERENCING NEW AS "NewCompo"
FOR each ROW WHEN("isnull"("newcompo"."suppliercode",'') <> '')
BEGIN
  IF NOT EXISTS(SELECT * FROM "iqacaccount" WHERE "ledgerid" = 'Purchase' AND "accountcode" = "newcompo"."suppliercode") THEN
    INSERT INTO "iqacaccount"( "ledgerid","accountcode","name" ) VALUES( 'Purchase',"newcompo"."suppliercode","newcompo"."templatename" ) ;
    INSERT INTO "iqacaccountsettings"( "ledgerid","accountcode","addr1","addr2","addr3","postcode","VATNumber","RegNo", email,banksortcode,bankacno ) VALUES
      ( 'Purchase',"newcompo"."suppliercode","newcompo"."payrolladdr1","newcompo"."payrolladdr2","newcompo"."payrolladdr3","newcompo"."payrollpostcode",
      "newcompo"."VATNumber","newcompo"."RegNo","newcompo"."emailaddress","newcompo"."banksortcode","newcompo"."bankacno" ) 
  END IF
END
}
GO
 
 
CREATE TRIGGER "CompositeAudit" BEFORE UPDATE OF "CompositeId",
"CompositePayCoReference","VATNumber","RegNo","CompanyName","BankRef","BankAcNo","BankAcName","BankSortCode","BankName",
"TemplateName","PayrollAddress","VATRegistered","TaxMethod","PayFrequency","FlagPayMethod","FlagPayrollAddress","FlagPayCoReference",
"FlagVATNumber","FlagVATRegistered","FlagRegNo","FlagCompanyName","FlagTaxMethod","FlagPayFrequency","FlagBankRef",
"FlagBankAcNo","FlagBankAcName","FlagBankSortCode","FlagBankName","PayMethod","FlagIBAN","IBAN","FlagSWIFTBIC",
"SWIFTBIC","DirectlyEmployed","FlagDirectlyEmployed","FlagPayDistribution","PayDistribution","HMRCEngagementDetail",
"PayrollAddr1","PayrollAddr2","PayrollAddr3","PayrollAddr4","PayrollPostcode","SupplierCode","FlagSupplierCode",
"EmailAddress","FlagEmailAddress","FlagHMRCEngagementDetail","AnalysisCode" ORDER 1 ON "pears"."Composite"
REFERENCING OLD AS "old_name" NEW AS "new_name"
FOR each ROW /* WHEN( search_condition ) */
BEGIN
  /* Type the trigger statements here */
  IF UPDATE("CompositePayCoReference") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':CompositePayCoReference'),"old_name"."CompositePayCoReference","new_name"."CompositePayCoReference")
  END IF;
  IF UPDATE("VatNumber") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':VATNumber'),"old_name"."VATNumber","new_name"."VATNumber")
  END IF;
  IF UPDATE("RegNo") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':RegNo'),"old_name"."RegNo","new_name"."RegNo")
  END IF;
  IF UPDATE("CompanyName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':CompanyName'),"old_name"."CompanyName","new_name"."CompanyName")
  END IF;
  IF UPDATE("BankRef") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':BankRef'),"old_name"."BankRef","new_name"."BankRef")
  END IF;
  IF UPDATE("BankAcNo") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':BankAcNo'),"old_name"."BankAcNo","new_name"."BankAcNo")
  END IF;
  IF UPDATE("BankAcName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':BankAcName'),"old_name"."BankAcName","new_name"."BankAcName")
  END IF;
  IF UPDATE("BankSortCode") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':BankSortCode'),"old_name"."BankSortCode","new_name"."BankSortCode")
  END IF;
  IF UPDATE("BankName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':BankName'),"old_name"."BankName","new_name"."BankName")
  END IF;
  IF UPDATE("TemplateName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':TemplateName'),"old_name"."TemplateName","new_name"."TemplateName")
  END IF;
  IF UPDATE("PayRollAddress") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':PayRollAddress'),"old_name"."PayRollAddress","new_name"."PayRollAddress")
  END IF;
  IF UPDATE("VATRegistered") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':VATRegistered'),"old_name"."VATRegistered","new_name"."VATRegistered")
  END IF;
  IF UPDATE("TaxMethod") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':TaxMethod'),"old_name"."TaxMethod","new_name"."TaxMethod")
  END IF;
  IF UPDATE("PayFrequency") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':PayFrequency'),"old_name"."PayFrequency","new_name"."PayFrequency")
  END IF;
  IF UPDATE("PayMethod") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':PayMethod'),"old_name"."PayMethod","new_name"."PayMethod")
  END IF;
  IF UPDATE("IBAN") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':IBAN'),"old_name"."IBAN","new_name"."IBAN")
  END IF;
  IF UPDATE("SWIFTBIC") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':SWIFTBIC'),"old_name"."SWIFTBIC","new_name"."SWIFTBIC")
  END IF;
  IF UPDATE("DirectlyEmployed") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':DirectlyEmployed'),"old_name"."DirectlyEmployed","new_name"."DirectlyEmployed")
  END IF;
  IF UPDATE("PayDistribution") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':PayDistribution'),"old_name"."PayDistribution","new_name"."PayDistribution")
  END IF;
  IF UPDATE("HMRCEngagementDetail") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':HMRCEngagementDetail'),"old_name"."HMRCEngagementDetail","new_name"."HMRCEngagementDetail")
  END IF;
  IF UPDATE("SupplierCode") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':SupplierCode'),"old_name"."SupplierCode","new_name"."SupplierCode")
  END IF;
  IF UPDATE("EmailAddress") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':EmailAddress'),"old_name"."EmailAddress","new_name"."EmailAddress")
  END IF;
  IF UPDATE("AnalysisCode") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':AnalysisCode'),"old_name"."AnalysisCode","new_name"."AnalysisCode")
  END IF;
  IF UPDATE("FlagPayCoReference") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagPayCoReference'),"old_name"."FlagPayCoReference","new_name"."FlagPayCoReference")
  END IF;
  IF UPDATE("FlagVatNumber") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagVATNumber'),"old_name"."FlagVATNumber","new_name"."FlagVATNumber")
  END IF;
  IF UPDATE("FlagRegNo") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagRegNo'),"old_name"."FlagRegNo","new_name"."FlagRegNo")
  END IF;
  IF UPDATE("FlagCompanyName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagCompanyName'),"old_name"."FlagCompanyName","new_name"."FlagCompanyName")
  END IF;
  IF UPDATE("FlagBankRef") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagBankRef'),"old_name"."FlagBankRef","new_name"."FlagBankRef")
  END IF;
  IF UPDATE("FlagBankAcNo") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagBankAcNo'),"old_name"."FlagBankAcNo","new_name"."FlagBankAcNo")
  END IF;
  IF UPDATE("FlagBankAcName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagBankAcName'),"old_name"."FlagBankAcName","new_name"."FlagBankAcName")
  END IF;
  IF UPDATE("FlagBankSortCode") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagBankSortCode'),"old_name"."FlagBankSortCode","new_name"."FlagBankSortCode")
  END IF;
  IF UPDATE("FlagBankName") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagBankName'),"old_name"."FlagBankName","new_name"."FlagBankName")
  END IF;
  IF UPDATE("FlagPayRollAddress") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagPayRollAddress'),"old_name"."FlagPayRollAddress","new_name"."FlagPayRollAddress")
  END IF;
  IF UPDATE("FlagVATRegistered") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagVATRegistered'),"old_name"."FlagVATRegistered","new_name"."FlagVATRegistered")
  END IF;
  IF UPDATE("FlagTaxMethod") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagTaxMethod'),"old_name"."FlagTaxMethod","new_name"."FlagTaxMethod")
  END IF;
  IF UPDATE("FlagPayFrequency") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagPayFrequency'),"old_name"."FlagPayFrequency","new_name"."FlagPayFrequency")
  END IF;
  IF UPDATE("FlagPayMethod") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagPayMethod'),"old_name"."FlagPayMethod","new_name"."FlagPayMethod")
  END IF;
  IF UPDATE("FlagIBAN") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagIBAN'),"old_name"."FlagIBAN","new_name"."FlagIBAN")
  END IF;
  IF UPDATE("FlagSWIFTBIC") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagSWIFTBIC'),"old_name"."FlagSWIFTBIC","new_name"."FlagSWIFTBIC")
  END IF;
  IF UPDATE("FlagDirectlyEmployed") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagDirectlyEmployed'),"old_name"."FlagDirectlyEmployed","new_name"."FlagDirectlyEmployed")
  END IF;
  IF UPDATE("FlagPayDistribution") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':FlagPayDistribution'),"old_name"."FlagPayDistribution","new_name"."FlagPayDistribution")
  END IF;
  IF UPDATE("FlagHMRCEngagementDetail") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':HMRCEngagementDetail'),"old_name"."FlagHMRCEngagementDetail","new_name"."FlagHMRCEngagementDetail")
  END IF;
  IF UPDATE("FlagSupplierCode") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':SupplierCode'),"old_name"."FlagSupplierCode","new_name"."FlagSupplierCode")
  END IF;
  IF UPDATE("FlagEmailAddress") THEN
    CALL "AuditLog"('COMPOSITE',"old_name"."compositeid","string"('Template - ',"old_name"."templatename",':EmailAddress'),"old_name"."FlagEmailAddress","new_name"."FlagEmailAddress")
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Composite"."CompositeAudit" IS 
{CREATE TRIGGER CompositeAudit 
BEFORE UPDATE OF CompositeId,
CompositePayCoReference,VATNumber,RegNo,CompanyName,BankRef,BankAcNo,BankAcName,BankSortCode,BankName,
TemplateName,PayrollAddress,VATRegistered,TaxMethod,PayFrequency,FlagPayMethod,FlagPayrollAddress,FlagPayCoReference,
FlagVATNumber,FlagVATRegistered,FlagRegNo,FlagCompanyName,FlagTaxMethod,FlagPayFrequency,FlagBankRef,
FlagBankAcNo,FlagBankAcName,FlagBankSortCode,FlagBankName,PayMethod,FlagIBAN,IBAN,FlagSWIFTBIC,
SWIFTBIC,DirectlyEmployed,FlagDirectlyEmployed,FlagPayDistribution,PayDistribution,HMRCEngagementDetail,
PayrollAddr1,PayrollAddr2,PayrollAddr3,PayrollAddr4,PayrollPostcode,SupplierCode,FlagSupplierCode,
EmailAddress,FlagEmailAddress,FlagHMRCEngagementDetail,AnalysisCode ORDER 1 ON pears.Composite
REFERENCING OLD AS old_name NEW AS new_name
FOR each ROW /* WHEN( search_condition ) */
BEGIN
/* Type the trigger statements here */
  IF UPDATE(CompositePayCoReference) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':CompositePayCoReference'),old_name.CompositePayCoReference,new_name.CompositePayCoReference)
  END IF;
  IF UPDATE(VatNumber) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':VATNumber'),old_name.VATNumber,new_name.VATNumber)
  END IF;
  IF UPDATE(RegNo) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':RegNo'),old_name.RegNo,new_name.RegNo)
  END IF;
  IF UPDATE(CompanyName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':CompanyName'),old_name.CompanyName,new_name.CompanyName)
  END IF;
  IF UPDATE(BankRef) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':BankRef'),old_name.BankRef,new_name.BankRef)
  END IF;
  IF UPDATE(BankAcNo) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':BankAcNo'),old_name.BankAcNo,new_name.BankAcNo)
  END IF;
  IF UPDATE(BankAcName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':BankAcName'),old_name.BankAcName,new_name.BankAcName)
  END IF;
  IF UPDATE(BankSortCode) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':BankSortCode'),old_name.BankSortCode,new_name.BankSortCode)
  END IF;
  IF UPDATE(BankName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':BankName'),old_name.BankName,new_name.BankName)
  END IF;
  IF UPDATE(TemplateName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':TemplateName'),old_name.TemplateName,new_name.TemplateName)
  END IF;
  IF UPDATE(PayRollAddress) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':PayRollAddress'),old_name.PayRollAddress,new_name.PayRollAddress)
  END IF;
  IF UPDATE(VATRegistered) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':VATRegistered'),old_name.VATRegistered,new_name.VATRegistered)
  END IF;
  IF UPDATE(TaxMethod) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':TaxMethod'),old_name.TaxMethod,new_name.TaxMethod)
  END IF;
  IF UPDATE(PayFrequency) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':PayFrequency'),old_name.PayFrequency,new_name.PayFrequency)
  END IF;
  IF UPDATE(PayMethod) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':PayMethod'),old_name.PayMethod,new_name.PayMethod)
  END IF;
  IF UPDATE(IBAN) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':IBAN'),old_name.IBAN,new_name.IBAN)
  END IF;
  IF UPDATE(SWIFTBIC) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':SWIFTBIC'),old_name.SWIFTBIC,new_name.SWIFTBIC)
  END IF;
  IF UPDATE(DirectlyEmployed) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':DirectlyEmployed'),old_name.DirectlyEmployed,new_name.DirectlyEmployed)
  END IF;
  IF UPDATE(PayDistribution) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':PayDistribution'),old_name.PayDistribution,new_name.PayDistribution)
  END IF;
  IF UPDATE(HMRCEngagementDetail) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':HMRCEngagementDetail'),old_name.HMRCEngagementDetail,new_name.HMRCEngagementDetail)
  END IF;
  IF UPDATE(SupplierCode) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':SupplierCode'),old_name.SupplierCode,new_name.SupplierCode)
  END IF;
  IF UPDATE(EmailAddress) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':EmailAddress'),old_name.EmailAddress,new_name.EmailAddress)
  END IF;
  IF UPDATE(AnalysisCode) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':AnalysisCode'),old_name.AnalysisCode,new_name.AnalysisCode)
  END IF;
  IF UPDATE(FlagPayCoReference) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagPayCoReference'),old_name.FlagPayCoReference,new_name.FlagPayCoReference)
  END IF;
  IF UPDATE(FlagVatNumber) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagVATNumber'),old_name.FlagVATNumber,new_name.FlagVATNumber)
  END IF;
  IF UPDATE(FlagRegNo) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagRegNo'),old_name.FlagRegNo,new_name.FlagRegNo)
  END IF;
  IF UPDATE(FlagCompanyName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagCompanyName'),old_name.FlagCompanyName,new_name.FlagCompanyName)
  END IF;
  IF UPDATE(FlagBankRef) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagBankRef'),old_name.FlagBankRef,new_name.FlagBankRef)
  END IF;
  IF UPDATE(FlagBankAcNo) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagBankAcNo'),old_name.FlagBankAcNo,new_name.FlagBankAcNo)
  END IF;
  IF UPDATE(FlagBankAcName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagBankAcName'),old_name.FlagBankAcName,new_name.FlagBankAcName)
  END IF;
  IF UPDATE(FlagBankSortCode) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagBankSortCode'),old_name.FlagBankSortCode,new_name.FlagBankSortCode)
  END IF;
  IF UPDATE(FlagBankName) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagBankName'),old_name.FlagBankName,new_name.FlagBankName)
  END IF;
  IF UPDATE(FlagPayRollAddress) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagPayRollAddress'),old_name.FlagPayRollAddress,new_name.FlagPayRollAddress)
  END IF;
  IF UPDATE(FlagVATRegistered) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagVATRegistered'),old_name.FlagVATRegistered,new_name.FlagVATRegistered)
  END IF;
  IF UPDATE(FlagTaxMethod) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagTaxMethod'),old_name.FlagTaxMethod,new_name.FlagTaxMethod)
  END IF;
  IF UPDATE(FlagPayFrequency) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagPayFrequency'),old_name.FlagPayFrequency,new_name.FlagPayFrequency)
  END IF;
  IF UPDATE(FlagPayMethod) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagPayMethod'),old_name.FlagPayMethod,new_name.FlagPayMethod)
  END IF;
  IF UPDATE(FlagIBAN) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagIBAN'),old_name.FlagIBAN,new_name.FlagIBAN)
  END IF;
  IF UPDATE(FlagSWIFTBIC) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagSWIFTBIC'),old_name.FlagSWIFTBIC,new_name.FlagSWIFTBIC)
  END IF;
  IF UPDATE(FlagDirectlyEmployed) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagDirectlyEmployed'),old_name.FlagDirectlyEmployed,new_name.FlagDirectlyEmployed)
  END IF;
  IF UPDATE(FlagPayDistribution) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':FlagPayDistribution'),old_name.FlagPayDistribution,new_name.FlagPayDistribution)
  END IF;
  IF UPDATE(FlagHMRCEngagementDetail) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':HMRCEngagementDetail'),old_name.FlagHMRCEngagementDetail,new_name.FlagHMRCEngagementDetail)
  END IF;
  IF UPDATE(FlagSupplierCode) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':SupplierCode'),old_name.FlagSupplierCode,new_name.FlagSupplierCode)
  END IF;
  IF UPDATE(FlagEmailAddress) THEN
    CALL AuditLog('COMPOSITE',old_name.compositeid,string('Template - ',old_name.templatename,':EmailAddress'),old_name.FlagEmailAddress,new_name.FlagEmailAddress)
  END IF
END
}
GO

pears.Composite

Description

Columns

Primary Key

Foreign Keys

Referenced By

Indexes

Triggers

Original SQL

iqx