====== pears.IQacJournal ======
<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 =====
Invoice journal lines - links to timesheetlines

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **DocumentID** | char(8) | NOT NULL |  |  |
| **LineNumber** | smallint | NOT NULL |  |  |
| NominalCode | char(12) | NOT NULL |  |  |
| AccountingDate | date | NULL |  |  |
| LedgerID | char(12) | NULL |  |  |
| AccountCode | char(12) | NULL |  |  |
| TransferBatch | integer | NULL |  |  |
| VATReturn | smallint | NULL |  |  |
| XRefID | char(20) | NULL |  | Optional link to other tables in the database |
| XRef | char(1) | NULL |  | Type of record linked by XRefID N-TS ID NIRecharge I - TS Line ID NIRecharge |
| Description | long varchar | NULL |  |  |
| Amount | numeric(12,2) | NULL |  |  |
| JournalClass | char(20) | NOT NULL |  | InvoiceGoods,InvoiceVAT,InvoiceControl,PaymentCash,PaymentControl,JournalLine,ControlJournal |
| GoodsAmount | numeric(12,2) | NULL |  | Goods amount on an invoice line |
| VATAmount | numeric(12,2) | NULL |  | VAT amount on an invoice line |
| VATRate | double | NULL |  | VAT % Rate - usually for invoice lines |
| VATCode | char(3) | NULL |  | VAT Code - usually for invoice lines |
| PriceEach | double | NULL |  | For invoice lines |
| Quantity | double | NULL |  | For invoice lines - optional |
| ProductCode | char(20) | NULL |  |  |
| UnitDescription | char(50) | NULL |  |  |
| XRefDocumentID | char(8) | NULL |  | Allows for inter-document links |
| XRefLineNumber | smallint | NULL |  | Allows for inter-document links |
| CashVATCode | char(3) | NULL |  | Allows for cashbook analysis of unallocated payments |
| CashVATAmount | numeric(12,2) | NULL |  | Allows for cashbook analysis of unallocated payments |
| CashAmount | numeric(12,2) | NULL |  | Allows for cashbook analysis of unallocated payments |
| CashAccountLine | tinyint | NULL |  | Allows for cashbook analysis of unallocated payments |
| AutoAllocate | tinyint | NULL |  | Identifies the journal lines that should be used for automatic allocation in a cash accounting environment |
| SubAllocation | tinyint | NULL |  | Identifies the journal lines that are available for manual allocation in a cash accounting environment |
| AllocAmount | numeric(12,2) | NULL |  | Used in allocation in a cash accounting environment |
| AllocVATAmount | numeric(12,2) | NULL |  | Used in allocation in a cash accounting environment |
| AnalysisBatch | integer | NULL |  |  |

===== Primary Key =====
  * DocumentID, LineNumber

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| IQacNominal | NominalCode | [[database:tables:pears_iqacnominal|pears.IQacNominal (NominalCode)]] | NOT NULL;  |
| Account | LedgerID, AccountCode | [[database:tables:pears_iqacaccount|pears.IQacAccount (LedgerID, AccountCode)]] |  |
| Document | DocumentID | [[database:tables:pears_iqacdocument|pears.IQacDocument (DocumentID)]] | NOT NULL;  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_iqacallocation|pears.IQacAllocation]] | Journal | AllocatedDocumentID, AllocatedLineNumber | DocumentID, LineNumber |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| Journal_Nominal | Index | NominalCode, AccountingDate |  |
| Journal_Date | Index | AccountingDate, NominalCode |  |
| Journal_XRef | Index | XRef, XRefID |  |

===== Triggers =====
  * No triggers found.

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQacJournal"
-- Table comment: Invoice journal lines - links to timesheetlines
-- Statement count: 27

CREATE TABLE "pears"."IQacJournal" (
    "DocumentID"                     char(8) NOT NULL
   ,"LineNumber"                     smallint NOT NULL
   ,"NominalCode"                    char(12) NOT NULL
   ,"AccountingDate"                 date NULL
   ,"LedgerID"                       char(12) NULL
   ,"AccountCode"                    char(12) NULL
   ,"TransferBatch"                  integer NULL
   ,"VATReturn"                      smallint NULL
   ,"XRefID"                         char(20) NULL
   ,"XRef"                           char(1) NULL
   ,"Description"                    long varchar NULL
   ,"Amount"                         numeric(12,2) NULL
   ,"JournalClass"                   char(20) NOT NULL
   ,"GoodsAmount"                    numeric(12,2) NULL
   ,"VATAmount"                      numeric(12,2) NULL
   ,"VATRate"                        double NULL
   ,"VATCode"                        char(3) NULL
   ,"PriceEach"                      double NULL
   ,"Quantity"                       double NULL
   ,"ProductCode"                    char(20) NULL
   ,"UnitDescription"                char(50) NULL
   ,"XRefDocumentID"                 char(8) NULL
   ,"XRefLineNumber"                 smallint NULL
   ,"CashVATCode"                    char(3) NULL
   ,"CashVATAmount"                  numeric(12,2) NULL
   ,"CashAmount"                     numeric(12,2) NULL COMPUTE (if "JournalClass" = 'PaymentCash' then "Amount" else 0 endif)
   ,"CashAccountLine"                tinyint NULL COMPUTE (if "JournalClass" = 'PaymentCash' then 1 else 0 endif)
   ,"AutoAllocate"                   tinyint NULL COMPUTE (if "JournalClass" in( 'InvoiceGoods','PaymentCash' ) then 1 else 0 endif)
   ,"SubAllocation"                  tinyint NULL COMPUTE (if "JournalClass" = 'InvoiceGoods' then 1 else 0 endif)
   ,"AllocAmount"                    numeric(12,2) NULL COMPUTE (case "JournalClass" when 'InvoiceGoods' then-("GoodsAmount"+"VATAmount") when 'PaymentCash' then-"Amount" else 0 end)
   ,"AllocVATAmount"                 numeric(12,2) NULL COMPUTE (case "JournalClass" when 'InvoiceGoods' then-"VATAmount" when 'PaymentCash' then-"CashVATAmount" else 0 end)
   ,"AnalysisBatch"                  integer NULL
   ,PRIMARY KEY ("DocumentID" ASC,"LineNumber" ASC) 
)
go


COMMENT ON COLUMN "pears"."IQacJournal"."XRefID" IS 
	'Optional link to other tables in the database'
go


COMMENT ON COLUMN "pears"."IQacJournal"."XRef" IS 
	'Type of record linked by XRefID N-TS ID NIRecharge I - TS Line ID NIRecharge'
go


COMMENT ON COLUMN "pears"."IQacJournal"."JournalClass" IS 
	'InvoiceGoods,InvoiceVAT,InvoiceControl,PaymentCash,PaymentControl,JournalLine,ControlJournal'
go


COMMENT ON COLUMN "pears"."IQacJournal"."GoodsAmount" IS 
	'Goods amount on an invoice line'
go


COMMENT ON COLUMN "pears"."IQacJournal"."VATAmount" IS 
	'VAT amount on an invoice line'
go


COMMENT ON COLUMN "pears"."IQacJournal"."VATRate" IS 
	'VAT % Rate - usually for invoice lines'
go


COMMENT ON COLUMN "pears"."IQacJournal"."VATCode" IS 
	'VAT Code - usually for invoice lines'
go


COMMENT ON COLUMN "pears"."IQacJournal"."PriceEach" IS 
	'For invoice lines'
go


COMMENT ON COLUMN "pears"."IQacJournal"."Quantity" IS 
	'For invoice lines - optional'
go


COMMENT ON COLUMN "pears"."IQacJournal"."XRefDocumentID" IS 
	'Allows for inter-document links'
go


COMMENT ON COLUMN "pears"."IQacJournal"."XRefLineNumber" IS 
	'Allows for inter-document links'
go


COMMENT ON COLUMN "pears"."IQacJournal"."CashVATCode" IS 
	'Allows for cashbook analysis of unallocated payments'
go


COMMENT ON COLUMN "pears"."IQacJournal"."CashVATAmount" IS 
	'Allows for cashbook analysis of unallocated payments'
go


COMMENT ON COLUMN "pears"."IQacJournal"."CashAmount" IS 
	'Allows for cashbook analysis of unallocated payments'
go


COMMENT ON COLUMN "pears"."IQacJournal"."CashAccountLine" IS 
	'Allows for cashbook analysis of unallocated payments'
go


COMMENT ON COLUMN "pears"."IQacJournal"."AutoAllocate" IS 
	'Identifies the journal lines that should be used for automatic allocation in a cash accounting environment'
go


COMMENT ON COLUMN "pears"."IQacJournal"."SubAllocation" IS 
	'Identifies the journal lines that are available for manual allocation in a cash accounting environment'
go


COMMENT ON COLUMN "pears"."IQacJournal"."AllocAmount" IS 
	'Used in allocation in a cash accounting environment'
go


COMMENT ON COLUMN "pears"."IQacJournal"."AllocVATAmount" IS 
	'Used in allocation in a cash accounting environment'
go


COMMENT ON TABLE "pears"."IQacJournal" IS 
	'Invoice journal lines - links to timesheetlines'
go


ALTER TABLE "pears"."IQacJournal"
    ADD NOT NULL FOREIGN KEY "IQacNominal" ("NominalCode" ASC)
    REFERENCES "pears"."IQacNominal" ("NominalCode")
go


ALTER TABLE "pears"."IQacJournal"
    ADD FOREIGN KEY "Account" ("LedgerID" ASC,"AccountCode" ASC)
    REFERENCES "pears"."IQacAccount" ("LedgerID","AccountCode")
go


ALTER TABLE "pears"."IQacJournal"
    ADD NOT NULL FOREIGN KEY "Document" ("DocumentID" ASC)
    REFERENCES "pears"."IQacDocument" ("DocumentID")
go


CREATE INDEX "Journal_Nominal" ON "pears"."IQacJournal"
    ( "NominalCode","AccountingDate" )
go


CREATE INDEX "Journal_Date" ON "pears"."IQacJournal"
    ( "AccountingDate","NominalCode" )
go


CREATE INDEX "Journal_XRef" ON "pears"."IQacJournal"
    ( "XRef","XRefID" )
go
</code>