pears.IQacAccount

List of ledger against account names

• LedgerID, AccountCode

-- 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