-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQacDocument"
-- Table comment: Invoices and other financial documents. Links to company/placement or timesheet (xref)
-- Statement count: 29
CREATE TABLE "pears"."IQacDocument" (
"DocumentID" CHAR(8) NOT NULL
,"DocumentTemplateID" CHAR(12) NULL
,"OurRef" CHAR(50) NULL
,"LedgerID" CHAR(12) NULL
,"AccountCode" CHAR(12) NULL
,"XRefID" CHAR(20) NULL
,"XRef" CHAR(1) NULL
,"PrintBatch" INTEGER NULL DEFAULT 0
,"FullyAllocated" DATE NULL
,"LastLineNumber" SMALLINT NULL
,"CurrencyCode" CHAR(3) NULL
,"ExchangeRate" DOUBLE NULL
,"StoredAsForeignCurrency" tinyint NULL
,"AccountingDate" DATE NULL
,"Description" long VARCHAR NULL
,"DocumentDate" DATE NULL
,"DueDate" DATE NULL
,"ExpectedDate" DATE NULL
,"TheirRef" CHAR(50) NULL
,"AttentionOf" CHAR(100) NULL
,"PaymentMethod" CHAR(3) NULL
,"Amount" NUMERIC(12,2) NULL
,"VATAmount" NUMERIC(12,2) NULL
,"DocumentClass" CHAR(20) NOT NULL
,"DocumentType" CHAR(20) NULL
,"InvoiceAddress" long VARCHAR NULL
,"EmailBatch" INTEGER NULL DEFAULT 0
,"InvoiceEmail" CHAR(250) NULL
,"WhoCreated" CHAR(20) NULL
,"WhenCreated" TIMESTAMP NULL
,"WhenSent" TIMESTAMP NULL
,"HowSent" CHAR(1) NULL
,"XRefID2" CHAR(20) NULL
,"XRef2" CHAR(1) NULL
,"WhoAuthorised" CHAR(20) NULL
,"WhenAuthorised" TIMESTAMP NULL
,"Adhoc" tinyint NULL
,"TransferBatch" INTEGER NULL
,"Internal" tinyint NULL DEFAULT 0
,"InvoicePrefix" CHAR(10) NULL
,"LinkedDocumentID" CHAR(8) NULL
,PRIMARY KEY ("DocumentID" ASC)
)
GO
COMMENT ON COLUMN "pears"."IQacDocument"."XRefID" IS
'Optional link to other tables in the database'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."XRef" IS
'Type of record linked by XRefID'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."FullyAllocated" IS
'The date when the outstanding balance reduced to zero'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."Amount" IS
'Headline amount of the document e.g. gross invoice value'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."VATAmount" IS
'VAT total of the document (if invoice)'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."DocumentClass" IS
'Invoice,Payment,Allocation,JournalEntry,LedgerJournal'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."DocumentType" IS
'Descriptive document type e.g. Credit Note'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."HowSent" IS
'P = print, E = Email, O = OB10'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."XRefID2" IS
'Second Optional link to other tables in the database'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."XRef2" IS
'Type of record linked by XRefID2 C=invoicing company'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."Internal" IS
'If 1 exclude from web publishing'
GO
COMMENT ON COLUMN "pears"."IQacDocument"."LinkedDocumentID" IS
'only populated for manual'
GO
COMMENT ON TABLE "pears"."IQacDocument" IS
'Invoices and other financial documents. Links to company/placement or timesheet (xref)'
GO
ALTER TABLE "pears"."IQacDocument"
ADD FOREIGN KEY "IQacDocumentTemplate" ("DocumentTemplateID" ASC)
REFERENCES "pears"."IQacDocumentTemplate" ("DocumentTemplateID")
GO
ALTER TABLE "pears"."IQacDocument"
ADD FOREIGN KEY "IQacCurrency" ("CurrencyCode" ASC)
REFERENCES "pears"."IQacCurrency" ("CurrencyCode")
GO
ALTER TABLE "pears"."IQacDocument"
ADD FOREIGN KEY "Account" ("LedgerID" ASC,"AccountCode" ASC)
REFERENCES "pears"."IQacAccount" ("LedgerID","AccountCode")
GO
CREATE INDEX "XRefID" ON "pears"."IQacDocument"
( "XRefID" )
GO
CREATE INDEX "XRefID2" ON "pears"."IQacDocument"
( "XRefID2" )
GO
CREATE INDEX "OurRef" ON "pears"."IQacDocument"
( "OurRef","LedgerID" )
GO
CREATE INDEX "AccountCode" ON "pears"."IQacDocument"
( "AccountCode" )
GO
CREATE INDEX "DocumentDate" ON "pears"."IQacDocument"
( "DocumentDate" )
GO
CREATE TRIGGER "IQacDoc_InvAddr" BEFORE INSERT ORDER 1 ON
"pears"."IQacDocument"
REFERENCING NEW AS "new_doc"
FOR each ROW WHEN("new_doc"."DocumentClass" = 'Invoice')
BEGIN
DECLARE "addr" long VARCHAR;
DECLARE "ema" CHAR(250);
SELECT "string"("company"."name","char"(13),"char"(10),"formatcompanyaddress"("company"."companyid",0,'B')),
"companyaccount"."invoiceemail" INTO "addr","ema"
FROM "company" KEY JOIN "companyaccount" WHERE "company"."companyid" = "getinvoicecompanyid"("new_doc"."AccountCode");
IF "new_doc"."InvoiceAddress" IS NULL THEN SET "new_doc"."InvoiceAddress" = "addr" END IF;
IF "new_doc"."InvoiceEmail" IS NULL THEN SET "new_doc"."InvoiceEmail" = "ema"
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."IQacDocument"."IQacDoc_InvAddr" IS
{CREATE TRIGGER IQacDoc_InvAddr
BEFORE INSERT ORDER 1 ON
"pears"."IQacDocument"
REFERENCING NEW AS "new_doc"
FOR each ROW WHEN("new_doc"."DocumentClass" = 'Invoice')
BEGIN
DECLARE "addr" long VARCHAR;
DECLARE "ema" CHAR(250);
SELECT "string"("company"."name","char"(13),"char"(10),"formatcompanyaddress"("company"."companyid",0,'B')),
"companyaccount"."invoiceemail" INTO "addr","ema"
FROM "company" KEY JOIN "companyaccount" WHERE "company"."companyid" = "getinvoicecompanyid"("new_doc"."AccountCode");
IF "new_doc"."InvoiceAddress" IS NULL THEN SET "new_doc"."InvoiceAddress" = "addr" END IF;
IF "new_doc"."InvoiceEmail" IS NULL THEN SET "new_doc"."InvoiceEmail" = "ema" END IF
END
}
GO
CREATE TRIGGER "IQacDoc_InvCreated" BEFORE INSERT ORDER 3 ON
"pears"."IQacDocument"
REFERENCING NEW AS "new_doc"
FOR each ROW WHEN("new_doc"."WhenCreated" IS NULL)
BEGIN
SET "new_doc"."WhenCreated" = CURRENT TIMESTAMP;
IF "new_doc"."WhoCreated" IS NULL THEN SET "new_doc"."WhoCreated" = "userstaffid"
END IF
exception
WHEN others THEN SET "new_doc"."WhoCreated" = NULL
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."IQacDocument"."IQacDoc_InvCreated" IS
{CREATE TRIGGER IQacDoc_InvCreated
BEFORE INSERT ORDER 3 ON
IQacDocument
REFERENCING NEW AS new_doc
FOR each ROW WHEN(new_doc.WhenCreated IS NULL)
BEGIN
SET new_doc.WhenCreated = CURRENT TIMESTAMP;
IF new_doc.WhoCreated IS NULL THEN SET new_doc.WhoCreated=userstaffid
END IF
exception
WHEN others THEN
SET new_doc.WhoCreated=NULL
END
}
GO
CREATE TRIGGER "IQacDoc_XREF2" BEFORE INSERT ORDER 2 ON
"pears"."IQacDocument"
REFERENCING NEW AS "new_doc"
FOR each ROW WHEN("new_doc"."XRefID2" IS NULL)
BEGIN
DECLARE "coid" VARCHAR(20);
SELECT "getinvoicecompanyid"("new_doc"."AccountCode") INTO "coid";
SET "new_doc"."XRef2" = 'C';
SET "new_doc"."XRefID2" = "coid"
END
GO
CREATE TRIGGER "IQacDoc_Authorised" BEFORE UPDATE OF "printbatch"
ORDER 1 ON
"pears"."IQacDocument"
REFERENCING NEW AS "new_doc"
FOR each ROW WHEN("new_doc"."printbatch" = 1 AND "new_doc"."DocumentClass" = 'Invoice')
BEGIN
SET "new_doc"."WhenAuthorised" = CURRENT TIMESTAMP;
IF "varexists"('userstaffid') = 1 THEN
SET "new_doc"."WhoAuthorised" = "userstaffid"
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."IQacDocument"."IQacDoc_Authorised" IS
{CREATE TRIGGER IQacDoc_Authorised
BEFORE UPDATE OF printbatch ORDER 1 ON
IQacDocument
REFERENCING NEW AS new_doc
FOR each ROW WHEN(new_doc.printbatch = 1 AND new_doc.DocumentClass = 'Invoice')
BEGIN
SET new_doc.WhenAuthorised = CURRENT TIMESTAMP;
IF varexists('userstaffid') = 1 THEN
SET new_doc.WhoAuthorised=userstaffid
END IF
END
}
GO