Table of Contents



pears.IQacAccount

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

Description

List of ledger against account names

Columns

Column Type Null Default Comment
LedgerID char(12) NOT NULL
AccountCode char(12) NOT NULL
Name char(50) NOT NULL

Primary Key

Foreign Keys

Constraint Columns References Delete/update action
IQacLedger LedgerID pears.IQacLedger (LedgerID) NOT NULL;

Referenced By

Table Constraint Columns Referenced columns
pears.IQacAccountSettings IQacAccount LedgerID, AccountCode LedgerID, AccountCode
pears.IQacDocument Account LedgerID, AccountCode LedgerID, AccountCode
pears.IQacDocumentTemplate PayAccount PaymentLedgerID, PaymentAccountCode LedgerID, AccountCode
pears.IQacJournal Account LedgerID, AccountCode LedgerID, AccountCode
pears.IQacNominal NomAccount LedgerID, AccountCode LedgerID, AccountCode

Indexes

Name Type Columns Detail
IQacAcc_Name Index Name

Triggers

Name Timing Event
IQacAccount_Audit before update of “Name” order 1

Original SQL

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQacAccount"
-- Table comment: List of ledger against account names
-- Statement count: 5
 
CREATE TABLE "pears"."IQacAccount" (
    "LedgerID"                       CHAR(12) NOT NULL
   ,"AccountCode"                    CHAR(12) NOT NULL
   ,"Name"                           CHAR(50) NOT NULL
   ,PRIMARY KEY ("LedgerID" ASC,"AccountCode" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."IQacAccount" IS 
	'List of ledger against account names'
GO
 
 
ALTER TABLE "pears"."IQacAccount"
    ADD NOT NULL FOREIGN KEY "IQacLedger" ("LedgerID" ASC)
    REFERENCES "pears"."IQacLedger" ("LedgerID")
GO
 
 
CREATE INDEX "IQacAcc_Name" ON "pears"."IQacAccount"
    ( "Name" )
GO
 
 
CREATE TRIGGER "IQacAccount_Audit" BEFORE UPDATE OF "Name"
ORDER 1 ON "pears"."IQacAccount"
REFERENCING OLD AS "old_acc" NEW AS "new_acc"
FOR each ROW
WHEN(EXISTS(SELECT * FROM "AuditItems" WHERE "ItemName" = 'Account Code Name' AND "AreaName" = 'Company Account' AND "AuditFlag" = 1))
BEGIN
  DECLARE "coid" VARCHAR(20);
  DECLARE @CompName CHAR(250);
  SELECT "getinvoicecompanyid"("new_acc"."AccountCode") INTO "coid";
  SET @CompName = (SELECT "name" FROM "company" WHERE "CompanyID" = "coid");
  CALL "AuditLog"('COMPANY',"coid","string"('Account Code Name Updated - ',@CompName),"old_acc"."name","new_acc"."name")
END
GO