Table of Contents



pears.IQXAccountsStatistics

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

Description

Incomplete attempt at speeding up margin reporting.

Columns

Column Type Null Default Comment
IQXAccountsStatisticsID char(20) NOT NULL
IQXYear smallint NULL
IQXWeek smallint NULL
AccountingDate date NULL
Amount numeric(12,2) NULL
Description char(100) NULL
Type char(20) NULL
NominalCodeSegment1 char(12) NULL
NominalCodeSegment2 char(12) NULL
NominalCodeSegment3 char(12) NULL
NominalCodeSegment4 char(12) NULL
NominalCodeSegment5 char(12) NULL
PayrollCompany char(10) NULL
DivisionAnalysisCode char(50) NULL
DepartmentAnalysisCode char(50) NULL
TimesheetAnalysisCode char(50) NULL
ConsultantAnalysisCode char(50) NULL
ClientAccountCode char(20) NULL
CompanyID char(20) NULL
PersonID char(20) NULL
TempTimesheetID char(20) NULL
TempTimesheetLineID char(20) NULL
TempDeskID char(20) NULL
StaffID char(20) NULL
PayslipID integer NULL
PayrollNumber char(20) NULL
PersonName char(50) NULL
CompanyName char(50) NULL
TimesheetSerial char(20) NULL
TimesheetLineNumber smallint NULL
DocumentNumber char(20) NULL
DocumentID char(20) NULL
DocumentLineNumber smallint NULL
SalesOrCost char(1) NULL
Currency char(3) NULL
PAYETaxSession char(20) NULL
TransferBatch integer NULL
PlacementID char(20) NULL
TempShiftID char(20) NULL
DocumentLineType char(1) NULL
WageTypeCode char(10) NULL
PlacementConsultantAnalysisCode char(50) NULL
VacancyConsultantAnalysisCode char(50) NULL
TimesheetConsultantAnalysisCode char(50) NULL
TimesheetStaffID char(20) NULL
PlacementStaffID char(20) NULL
VacancyStaffID char(20) NULL
JournalLineNumber smallint NULL
VacancyID char(20) NULL
PayBandPayRollFlag char(10) NULL
TaxMethod tinyint NULL
Paid double NULL
HolPay double NULL
HolLiab double NULL
AWRHolRate double NULL
Levy double NULL
HolLiabNI double NULL
ActualNI double NULL
Pension double NULL
Statutory double NULL
Charged double NULL
TempdeskAnalysiscode char(20) NULL
GoodsAmount numeric(12,2) NULL
PriceEach double NULL
VatAmount double NULL
VatCode char(3) NULL
JournalNominalCode char(12) NULL
PayrollIdentifier char(1) NULL
Period integer NULL
TimesheetSupplierCode char(12) NULL
ShiftAnalysisCode char(20) NULL
ShiftReferenceCode char(20) NULL
ShiftSerialNumber bigint NULL
TempShiftTypeID char(20) NULL
AnalysisBatch integer NULL
EmploymentPosition char(50) NULL
EssentialSkillGradeID char(4) NULL
vacancyTheirRef char(50) NULL
VacancyRefcode char(20) NULL
JournalClass char(20) NULL
Quanitity double NULL
UnitDescription char(50) NULL
CustomString1 char(40) NULL
CustomString2 char(40) NULL
CustomString3 char(40) NULL
CustomString4 char(40) NULL
CustomString5 char(40) NULL
CustomDouble1 double NULL
CustomDouble2 double NULL
CustomDouble3 double NULL
CustomDouble4 double NULL
CustomDouble5 double NULL
PersonStaffID char(20) NULL
Quantity double NULL
ShiftConsultantAnalysisCode char(50) NULL
TempShiftDate date NULL
IsExpenses smallint NULL
DocumentDate date NULL

Primary Key

Foreign Keys

Referenced By

Indexes

Name Type Columns Detail
IQXAccountStatistics_DocumentID Index DocumentID
IQXAccountStatistics_PAYETaxSession Index PAYETaxSession
IQXAccountStatistics_Period_DocumentID Index IQXYear, IQXWeek, DocumentID

Triggers

Original SQL

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQXAccountsStatistics"
-- Table comment: Incomplete attempt at speeding up margin reporting.
-- Statement count: 5
 
CREATE TABLE "pears"."IQXAccountsStatistics" (
    "IQXAccountsStatisticsID"        CHAR(20) NOT NULL
   ,"IQXYear"                        SMALLINT NULL
   ,"IQXWeek"                        SMALLINT NULL
   ,"AccountingDate"                 DATE NULL
   ,"Amount"                         NUMERIC(12,2) NULL
   ,"Description"                    CHAR(100) NULL
   ,"Type"                           CHAR(20) NULL
   ,"NominalCodeSegment1"            CHAR(12) NULL
   ,"NominalCodeSegment2"            CHAR(12) NULL
   ,"NominalCodeSegment3"            CHAR(12) NULL
   ,"NominalCodeSegment4"            CHAR(12) NULL
   ,"NominalCodeSegment5"            CHAR(12) NULL
   ,"PayrollCompany"                 CHAR(10) NULL
   ,"DivisionAnalysisCode"           CHAR(50) NULL
   ,"DepartmentAnalysisCode"         CHAR(50) NULL
   ,"TimesheetAnalysisCode"          CHAR(50) NULL
   ,"ConsultantAnalysisCode"         CHAR(50) NULL
   ,"ClientAccountCode"              CHAR(20) NULL
   ,"CompanyID"                      CHAR(20) NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"TempTimesheetID"                CHAR(20) NULL
   ,"TempTimesheetLineID"            CHAR(20) NULL
   ,"TempDeskID"                     CHAR(20) NULL
   ,"StaffID"                        CHAR(20) NULL
   ,"PayslipID"                      INTEGER NULL
   ,"PayrollNumber"                  CHAR(20) NULL
   ,"PersonName"                     CHAR(50) NULL
   ,"CompanyName"                    CHAR(50) NULL
   ,"TimesheetSerial"                CHAR(20) NULL
   ,"TimesheetLineNumber"            SMALLINT NULL
   ,"DocumentNumber"                 CHAR(20) NULL
   ,"DocumentID"                     CHAR(20) NULL
   ,"DocumentLineNumber"             SMALLINT NULL
   ,"SalesOrCost"                    CHAR(1) NULL
   ,"Currency"                       CHAR(3) NULL
   ,"PAYETaxSession"                 CHAR(20) NULL
   ,"TransferBatch"                  INTEGER NULL
   ,"PlacementID"                    CHAR(20) NULL
   ,"TempShiftID"                    CHAR(20) NULL
   ,"DocumentLineType"               CHAR(1) NULL
   ,"WageTypeCode"                   CHAR(10) NULL
   ,"PlacementConsultantAnalysisCode" CHAR(50) NULL
   ,"VacancyConsultantAnalysisCode"  CHAR(50) NULL
   ,"TimesheetConsultantAnalysisCode" CHAR(50) NULL
   ,"TimesheetStaffID"               CHAR(20) NULL
   ,"PlacementStaffID"               CHAR(20) NULL
   ,"VacancyStaffID"                 CHAR(20) NULL
   ,"JournalLineNumber"              SMALLINT NULL
   ,"VacancyID"                      CHAR(20) NULL
   ,"PayBandPayRollFlag"             CHAR(10) NULL
   ,"TaxMethod"                      tinyint NULL
   ,"Paid"                           DOUBLE NULL
   ,"HolPay"                         DOUBLE NULL
   ,"HolLiab"                        DOUBLE NULL
   ,"AWRHolRate"                     DOUBLE NULL
   ,"Levy"                           DOUBLE NULL
   ,"HolLiabNI"                      DOUBLE NULL
   ,"ActualNI"                       DOUBLE NULL
   ,"Pension"                        DOUBLE NULL
   ,"Statutory"                      DOUBLE NULL
   ,"Charged"                        DOUBLE NULL
   ,"TempdeskAnalysiscode"           CHAR(20) NULL
   ,"GoodsAmount"                    NUMERIC(12,2) NULL
   ,"PriceEach"                      DOUBLE NULL
   ,"VatAmount"                      DOUBLE NULL
   ,"VatCode"                        CHAR(3) NULL
   ,"JournalNominalCode"             CHAR(12) NULL
   ,"PayrollIdentifier"              CHAR(1) NULL
   ,"Period"                         INTEGER NULL
   ,"TimesheetSupplierCode"          CHAR(12) NULL
   ,"ShiftAnalysisCode"              CHAR(20) NULL
   ,"ShiftReferenceCode"             CHAR(20) NULL
   ,"ShiftSerialNumber"              BIGINT NULL
   ,"TempShiftTypeID"                CHAR(20) NULL
   ,"AnalysisBatch"                  INTEGER NULL
   ,"EmploymentPosition"             CHAR(50) NULL
   ,"EssentialSkillGradeID"          CHAR(4) NULL
   ,"vacancyTheirRef"                CHAR(50) NULL
   ,"VacancyRefcode"                 CHAR(20) NULL
   ,"JournalClass"                   CHAR(20) NULL
   ,"Quanitity"                      DOUBLE NULL
   ,"UnitDescription"                CHAR(50) NULL
   ,"CustomString1"                  CHAR(40) NULL
   ,"CustomString2"                  CHAR(40) NULL
   ,"CustomString3"                  CHAR(40) NULL
   ,"CustomString4"                  CHAR(40) NULL
   ,"CustomString5"                  CHAR(40) NULL
   ,"CustomDouble1"                  DOUBLE NULL
   ,"CustomDouble2"                  DOUBLE NULL
   ,"CustomDouble3"                  DOUBLE NULL
   ,"CustomDouble4"                  DOUBLE NULL
   ,"CustomDouble5"                  DOUBLE NULL
   ,"PersonStaffID"                  CHAR(20) NULL
   ,"Quantity"                       DOUBLE NULL
   ,"ShiftConsultantAnalysisCode"    CHAR(50) NULL
   ,"TempShiftDate"                  DATE NULL
   ,"IsExpenses"                     SMALLINT NULL
   ,"DocumentDate"                   DATE NULL
   ,PRIMARY KEY ("IQXAccountsStatisticsID" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."IQXAccountsStatistics" IS 
	'Incomplete attempt at speeding up margin reporting.'
GO
 
 
CREATE INDEX "IQXAccountStatistics_DocumentID" ON "pears"."IQXAccountsStatistics"
    ( "DocumentID" )
GO
 
 
CREATE INDEX "IQXAccountStatistics_PAYETaxSession" ON "pears"."IQXAccountsStatistics"
    ( "PAYETaxSession" )
GO
 
 
CREATE INDEX "IQXAccountStatistics_Period_DocumentID" ON "pears"."IQXAccountsStatistics"
    ( "IQXYear","IQXWeek","DocumentID" )
GO