pears.IQacJournal

Invoice journal lines - links to timesheetlines

• DocumentID, LineNumber

• No triggers found.

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