pears.IQacAccountSettings

pears.IQacAccountSettings

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

Description

Configuration for an iQac account.

Columns

Column	Type	Null	Default	Comment
LedgerID	char(12)	NOT NULL
AccountCode	char(12)	NOT NULL
InvoiceFrequency	char(1)	NULL		<W>eekly, <2> weekly, <4> weekly, <M>onthly
GroupInvoice	tinyint	NULL	0
OrderInvoice	tinyint	NULL	0
documenttemplateid	char(12)	NULL
VATNumber	char(20)	NULL
RegNo	char(20)	NULL
VATStatus	char(1)	NULL		links to IQACVatRate
Distribution	char(1)	NULL		E - email P - post B - both N - none
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
email	char(250)	NULL
Currency	char(3)	NULL		links to IQACCurrency
Notes	long varchar	NULL
attentionof	char(250)	NULL
banksortcode	char(8)	NULL
bankacno	char(15)	NULL
bankacname	char(18)	NULL

Primary Key

LedgerID, AccountCode

Foreign Keys

Constraint	Columns	References	Delete/update action
IQacAccount	LedgerID, AccountCode	pears.IQacAccount (LedgerID, AccountCode)	NOT NULL; ON DELETE CASCADE
iqacdocumenttemplate	documenttemplateid	pears.IQacDocumentTemplate (DocumentTemplateID)	ON DELETE SET NULL

Referenced By

No incoming foreign keys found.

Indexes

No indexes found.

Triggers

Name	Timing	Event
IQacAccountSettingsAudit	before	update order 2

Original SQL

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQacAccountSettings"
-- Table comment: Configuration for an iQac account.
-- Statement count: 10
 
CREATE TABLE "pears"."IQacAccountSettings" (
    "LedgerID"                       CHAR(12) NOT NULL
   ,"AccountCode"                    CHAR(12) NOT NULL
   ,"InvoiceFrequency"               CHAR(1) NULL
   ,"GroupInvoice"                   tinyint NULL DEFAULT 0
   ,"OrderInvoice"                   tinyint NULL DEFAULT 0
   ,"documenttemplateid"             CHAR(12) NULL
   ,"VATNumber"                      CHAR(20) NULL
   ,"RegNo"                          CHAR(20) NULL
   ,"VATStatus"                      CHAR(1) NULL
   ,"Distribution"                   CHAR(1) 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
   ,"email"                          CHAR(250) NULL
   ,"Currency"                       CHAR(3) NULL
   ,"Notes"                          long VARCHAR NULL
   ,"attentionof"                    CHAR(250) NULL
   ,"banksortcode"                   CHAR(8) NULL
   ,"bankacno"                       CHAR(15) NULL
   ,"bankacname"                     CHAR(18) NULL
   ,PRIMARY KEY ("LedgerID" ASC,"AccountCode" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."IQacAccountSettings"."InvoiceFrequency" IS 
	'<W>eekly, <2> weekly, <4> weekly, <M>onthly'
GO
 
 
COMMENT ON COLUMN "pears"."IQacAccountSettings"."VATStatus" IS 
	'links to IQACVatRate'
GO
 
 
COMMENT ON COLUMN "pears"."IQacAccountSettings"."Distribution" IS 
	'E - email P - post B - both N - none'
GO
 
 
COMMENT ON COLUMN "pears"."IQacAccountSettings"."Currency" IS 
	'links to IQACCurrency'
GO
 
 
COMMENT ON TABLE "pears"."IQacAccountSettings" IS 
	'Configuration for an iQac account.'
GO
 
 
ALTER TABLE "pears"."IQacAccountSettings"
    ADD NOT NULL FOREIGN KEY "IQacAccount" ("LedgerID" ASC,"AccountCode" ASC)
    REFERENCES "pears"."IQacAccount" ("LedgerID","AccountCode")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."IQacAccountSettings"
    ADD FOREIGN KEY "iqacdocumenttemplate" ("documenttemplateid" ASC)
    REFERENCES "pears"."IQacDocumentTemplate" ("DocumentTemplateID")
    ON DELETE SET NULL
GO
 
 
CREATE TRIGGER "IQacAccountSettingsAudit" BEFORE UPDATE ORDER 2 ON
"pears"."IQacAccountSettings"
REFERENCING OLD AS "old_ac" NEW AS "new_ac"
FOR each ROW
BEGIN
  DECLARE @CompName CHAR(250);
  DECLARE @OldTemplate CHAR(250);
  DECLARE @NewTemplate CHAR(250);
  SET @CompName = (SELECT "name" FROM "IQacAccount" WHERE "accountcode" = "old_ac"."accountcode");
  IF UPDATE("documenttemplateid") THEN
    SET @OldTemplate = (SELECT "name" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "old_ac"."documenttemplateid");
    SET @newTemplate = (SELECT "name" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "new_ac"."documenttemplateid");
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Layout Updated - ',@CompName),@OldTemplate,@NewTemplate)
  END IF;
  IF UPDATE("InvoiceFrequency") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Invoice Frequency Updated - ',@CompName),"old_ac"."InvoiceFrequency","new_ac"."InvoiceFrequency")
  END IF;
  IF UPDATE("GroupInvoice") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Invoice Grouping Updated - ',@CompName),"old_ac"."GroupInvoice","new_ac"."GroupInvoice")
  END IF;
  IF UPDATE("OrderInvoice") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Invoice Ordering Updated - ',@CompName),"old_ac"."OrderInvoice","new_ac"."OrderInvoice")
  END IF;
  IF UPDATE("VATNumber") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('VAT Number Updated - ',@CompName),"old_ac"."VATNumber","new_ac"."VATNumber")
  END IF;
  IF UPDATE("RegNo") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Registration Number Updated - ',@CompName),"old_ac"."RegNo","new_ac"."RegNo")
  END IF;
  IF UPDATE("VATStatus") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('VAT Code Updated - ',@CompName),"old_ac"."VATStatus","new_ac"."VATStatus")
  END IF;
  IF UPDATE("Distribution") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Distribution Updated - ',@CompName),"old_ac"."Distribution","new_ac"."Distribution")
  END IF;
  IF UPDATE("email") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Email Updated - ',@CompName),"old_ac"."email","new_ac"."email")
  END IF;
  IF UPDATE("attentionof") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Attention Of Updated - ',@CompName),"old_ac"."attentionof","new_ac"."attentionof")
  END IF;
  IF UPDATE("banksortcode") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Bank Sort Code Updated - ',@CompName),"old_ac"."banksortcode","new_ac"."banksortcode")
  END IF;
  IF UPDATE("bankacno") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Bank Account Number Updated - ',@CompName),"old_ac"."bankacno","new_ac"."bankacno")
  END IF;
  IF UPDATE("bankacname") THEN
    CALL "AuditLog"('SUPPCODE',"old_ac"."accountcode","string"('Bank Account Name Updated - ',@CompName),"old_ac"."bankacname","new_ac"."bankacname")
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."IQacAccountSettings"."IQacAccountSettingsAudit" IS 
{CREATE TRIGGER IQacAccountSettingsAudit 
 BEFORE UPDATE  ORDER 2 ON pears.IQacAccountSettings
REFERENCING OLD AS old_ac NEW AS new_ac
FOR each ROW
BEGIN
  DECLARE @CompName CHAR(250);
  DECLARE @OldTemplate CHAR(250);
  DECLARE @NewTemplate CHAR(250);
  SET @CompName=(SELECT name FROM IQacAccount WHERE accountcode = old_ac.accountcode);
  IF  UPDATE(documenttemplateid) THEN
    SET @OldTemplate=(SELECT name FROM iqacdocumenttemplate WHERE documenttemplateid = old_ac.documenttemplateid);
    SET @newTemplate=(SELECT name FROM iqacdocumenttemplate WHERE documenttemplateid = new_ac.documenttemplateid);
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Layout Updated - ',@CompName),@OldTemplate,@NewTemplate)
  END IF;
  IF  UPDATE(InvoiceFrequency) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Invoice Frequency Updated - ',@CompName),old_ac.InvoiceFrequency,new_ac.InvoiceFrequency)
  END IF;
  IF  UPDATE(GroupInvoice) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Invoice Grouping Updated - ',@CompName),old_ac.GroupInvoice,new_ac.GroupInvoice)
  END IF;
  IF  UPDATE(OrderInvoice) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Invoice Ordering Updated - ',@CompName),old_ac.OrderInvoice,new_ac.OrderInvoice)
  END IF;
  IF  UPDATE(VATNumber) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('VAT Number Updated - ',@CompName),old_ac.VATNumber,new_ac.VATNumber)
  END IF;
  IF  UPDATE(RegNo) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Registration Number Updated - ',@CompName),old_ac.RegNo,new_ac.RegNo)
  END IF;
  IF  UPDATE(VATStatus) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('VAT Code Updated - ',@CompName),old_ac.VATStatus,new_ac.VATStatus)
  END IF;
  IF  UPDATE(Distribution) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Distribution Updated - ',@CompName),old_ac.Distribution,new_ac.Distribution)
  END IF;
  IF  UPDATE(email) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Email Updated - ',@CompName),old_ac.email,new_ac.email)
  END IF;
  IF  UPDATE(attentionof) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Attention Of Updated - ',@CompName),old_ac.attentionof,new_ac.attentionof)
  END IF;
  IF  UPDATE(banksortcode) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Bank Sort Code Updated - ',@CompName),old_ac.banksortcode,new_ac.banksortcode)
  END IF;
  IF  UPDATE(bankacno) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Bank Account Number Updated - ',@CompName),old_ac.bankacno,new_ac.bankacno)
  END IF;
  IF  UPDATE(bankacname) THEN
    CALL AuditLog('SUPPCODE',old_ac.accountcode,string('Bank Account Name Updated - ',@CompName),old_ac.bankacname,new_ac.bankacname)
  END IF
END
}
GO

Table of Contents

pears.IQacAccountSettings

Description

Columns

Primary Key

Foreign Keys

Referenced By

Indexes

Triggers

Original SQL