database:tables:pears_iqacdocument



pears.IQacDocument

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

Description

Invoices and other financial documents. Links to company/placement or timesheet (xref)

Columns

Column Type Null Default Comment
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 Optional link to other tables in the database
XRef char(1) NULL Type of record linked by XRefID
PrintBatch integer NULL 0
FullyAllocated date NULL The date when the outstanding balance reduced to zero
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 Headline amount of the document e.g. gross invoice value
VATAmount numeric(12,2) NULL VAT total of the document (if invoice)
DocumentClass char(20) NOT NULL Invoice,Payment,Allocation,JournalEntry,LedgerJournal
DocumentType char(20) NULL Descriptive document type e.g. Credit Note
InvoiceAddress long varchar NULL
EmailBatch integer NULL 0
InvoiceEmail char(250) NULL
WhoCreated char(20) NULL
WhenCreated timestamp NULL
WhenSent timestamp NULL
HowSent char(1) NULL P = print, E = Email, O = OB10
XRefID2 char(20) NULL Second Optional link to other tables in the database
XRef2 char(1) NULL Type of record linked by XRefID2 C=invoicing company
WhoAuthorised char(20) NULL
WhenAuthorised timestamp NULL
Adhoc tinyint NULL
TransferBatch integer NULL
Internal tinyint NULL 0 If 1 exclude from web publishing
InvoicePrefix char(10) NULL
LinkedDocumentID char(8) NULL only populated for manual

Primary Key

  • DocumentID

Foreign Keys

Constraint Columns References Delete/update action
IQacDocumentTemplate DocumentTemplateID pears.IQacDocumentTemplate (DocumentTemplateID)
IQacCurrency CurrencyCode pears.IQacCurrency (CurrencyCode)
Account LedgerID, AccountCode pears.IQacAccount (LedgerID, AccountCode)

Referenced By

Table Constraint Columns Referenced columns
pears.EmailToSendAttachment IQacDocument IQACDocumentID DocumentID
pears.IQacAllocation IQacDocument AllocationDocumentID DocumentID
pears.IQacJournal Document DocumentID DocumentID

Indexes

Name Type Columns Detail
XRefID Index XRefID
XRefID2 Index XRefID2
OurRef Index OurRef, LedgerID
AccountCode Index AccountCode
DocumentDate Index DocumentDate

Triggers

Name Timing Event
IQacDoc_InvAddr before insert order 1
IQacDoc_InvCreated before insert order 3
IQacDoc_XREF2 before insert order 2
IQacDoc_Authorised before update of “printbatch” order 1

Original SQL

-- 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
  • database/tables/pears_iqacdocument.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1