====== pears.IQacDocument ======
<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 =====
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 | [[database:tables:pears_iqacdocumenttemplate|pears.IQacDocumentTemplate (DocumentTemplateID)]] |  |
| IQacCurrency | CurrencyCode | [[database:tables:pears_iqaccurrency|pears.IQacCurrency (CurrencyCode)]] |  |
| Account | LedgerID, AccountCode | [[database:tables:pears_iqacaccount|pears.IQacAccount (LedgerID, AccountCode)]] |  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_emailtosendattachment|pears.EmailToSendAttachment]] | IQacDocument | IQACDocumentID | DocumentID |
| [[database:tables:pears_iqacallocation|pears.IQacAllocation]] | IQacDocument | AllocationDocumentID | DocumentID |
| [[database:tables:pears_iqacjournal|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 =====
<code 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
</code>