====== pears.IQacAccountSettings ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== 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 | [[database:tables:pears_iqacaccount|pears.IQacAccount (LedgerID, AccountCode)]] | NOT NULL; ON DELETE CASCADE |
| iqacdocumenttemplate | documenttemplateid | [[database:tables:pears_iqacdocumenttemplate|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 =====
<code 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
</code>