Skip to main content

Invoicing & Billing - Key Tables

Full column listings for the 5 key tables in this category.

CHARGERATE (27 columns)

# Column Type Length Nullable Default
1 CRid int NO
2 CRarea int NO
3 CRchargeid int NO
4 CRstartdate datetime NO
5 CRrate float YES ((0))
6 CRorg int YES ((-1))
7 CRMinimum float YES ((0))
8 CRIncrement float YES ((0))
9 CROOHMultiplier float YES ((0))
10 CRHolidayMultiplier float YES ((0))
11 CRWeekendMultiplier float YES ((0))
12 CRSurcharge bit YES ((0))
13 CRRound int YES ((0))
14 crcontractid int YES ((0))
15 CRuseAgentWorkingHours bit YES ((0))
16 CRuseBudgetRate bit YES ((0))
17 CRuseForTravel bit YES ((1))
18 CRuseForMileage bit YES ((1))
19 CRtravelSurchargeId int YES ((0))
20 CRcontractmultiplier float YES
21 CRrateoverride float YES
22 croverridesurcharge bit YES
23 crexpirydate datetime YES ((0))
24 crmileagerate float YES
25 crtreeid int YES ((0))
26 crticketid int YES ((0))
27 cragentslist nvarchar 500 YES

INVOICEDETAIL (123 columns)

# Column Type Length Nullable Default
1 IDid int NO
2 IdIHid int YES ((0))
3 IDItem_Code nvarchar 4000 YES
4 IDItem_ShortDescription nvarchar 4000 YES
5 IDItem_LongDescription ntext 1073741823 YES
6 IDNominal_Code nvarchar 4000 YES
7 IDTax_Code nvarchar 4000 YES
8 IDQty_Order float YES ((0))
9 IDUnit_Price float YES ((0))
10 IDNet_Amount float YES ((0))
11 IDTax_Amount float YES ((0))
12 IDTax_Rate float YES ((0))
13 IDComment_1 ntext 1073741823 YES
14 IDComment_2 ntext 1073741823 YES
15 IDItemOnOrder bit YES ((0))
16 IDDsite int YES ((0))
17 IDDDevnum int YES ((0))
18 ID_ItemID int YES ((0))
19 IDProductNumber nvarchar 4000 YES ('')
20 IDUnit_Cost float YES ((0.00))
21 IDDID int YES ((-1))
22 IDCHID int YES ((-1))
23 IDActionCode int YES ((-1))
24 IDSite int YES ((-1))
25 IDDMID int YES ((-1))
26 IDFaultid int YES ((-1))
27 IDLineActionDate datetime YES ((0))
28 IDLabourDepartmentID int NO ((0))
29 IDOLID int NO ((0))
30 IDOLSeq int NO ((0))
31 IDXero_LineId nvarchar 200 YES ('')
32 IDppid int YES ((-1))
33 IDSetPriceFromAssets bit YES ((0))
34 IDsetPriceFromUsers bit YES ((0))
35 IDOverrideASTTotal float YES ((0.00))
36 IDProRataNextInvoice bit YES ((0))
37 IDProRataAddQuantity float YES ((0.00))
38 IDProRataAmount float YES ((0.00))
39 IDProRataDescription nvarchar 500 YES ('')
40 IDProRataLongDescription nvarchar 1000 YES ('')
41 idisInactive bit YES ((0))
42 IDgroupID int YES ((0))
43 IDisGroupDesc bit YES ((0))
44 IDKashflowLineId int YES ((0))
45 idtaxcodeoverriden bit YES ((0))
46 idrecurringinvoiceid int YES ((0))
47 IDisMeter bit YES ((0))
48 IDrecurringInvoiceLineId int YES ((0))
49 IDRecurringInvoiceQuantityType int YES ((0))
50 idbillingperiod int YES ((0))
51 idpercentincrease float YES
52 idminmeterunits int YES
53 idsequenceid int YES ((10))
54 IDStartDate datetime YES ((0))
55 IDEndDate datetime YES ((0))
56 IDAutoRenew bit YES
57 idintacctlocid nvarchar 500 YES
58 idsyncedtointacct bit YES
59 idhideitems bit YES ((0))
60 idincludegrouppriceandqty bit YES ((0))
61 idautoincreaseperiod int YES
62 idautoincreaselastchangedate datetime YES ((0))
63 idintacctdepid nvarchar 4000 YES
64 idonetimecharge bit YES
65 idonetimechargeinvoiceid int YES ((0))
66 idneedsreviewbeforecreation bit YES
67 idreviewed bit YES
68 idmetertype int YES ((0))
69 IDDBCId nvarchar 100 YES ('')
70 idsdid int YES
71 idshid int YES
72 iddiscount float YES
73 idexcludefromautoincrease bit YES ((0))
74 ididprid int YES
75 idsupplier int YES
76 idhideonpdf bit YES
77 idintacctwarehouseid nvarchar 500 YES
78 idinvoiceclass nvarchar 1000 YES
79 idpercentinvoiced decimal YES
80 idisbundledline bit YES ((0))
81 idmeterminqty float YES
82 idoriginalclientid int YES
83 idmyobrowversion nvarchar 500 YES
84 idmyoblocation nvarchar 500 YES
85 idmyobrowid int YES
86 iddbcseq int YES ((0))
87 idminimumlinetotal float YES
88 idhidegroupeditemsprice bit YES
89 idunit_priceconverted float YES
90 idcreditlinkedidid int YES ((0))
91 idmeterpricingmethod int YES ((0))
92 idcreditid int YES
93 idbundleid int YES
94 idbundlelineid int YES
95 idisdeferredrevenue bit YES ((0))
96 idisrecognisedprepay bit YES ((0))
97 iddeferredrevenueolid int YES ((0))
98 iddeferredrevenueolseq int YES ((0))
99 idnotaxoverride bit YES ((0))
100 IDautorenewperiod int YES ((0))
101 idquantitycustom int YES
102 idxerocategory1lookup nvarchar 200 YES
103 idxerocategory2lookup nvarchar 200 YES
104 iduserpiincrease bit YES ((0))
105 idsurchargeid int YES
106 idconsumptiontype int YES ((0))
107 idconsumptionmarkup float YES
108 idconsumptioncost float YES
109 idcspinvoiceid int YES ((0))
110 idconsumptionsplitid nvarchar 1000 YES
111 idexpenseid int YES
112 idisdownpayment bit YES ((0))
113 iddownpaymentcomplete int YES
114 idrecognizedfromidid int YES
115 iddownpaymenttype int YES ((0))
116 idrecognisedquantity int YES ((0))
117 idrecognisedpercentage float YES
118 idoriginalorderid int YES
119 idoriginalorderseq int YES
120 idoccurrencecount int YES
121 iduseassetmeters bit YES
122 idlastinvoicednetamount decimal YES
123 IDIGId int YES

INVOICEHEADER (157 columns)

# Column Type Length Nullable Default
1 IHid int NO
2 IHaarea int YES ((0))
3 IHsitenumber int YES ((0))
4 IHuid int YES ((0))
5 IHInvoiceNumber nvarchar 4000 YES
6 IH3rdPartyInvoiceNumber nvarchar 4000 YES
7 IHPosted bit NO ((0))
8 IHname nvarchar 4000 YES
9 IHAddress1 nvarchar 4000 YES
10 IHAddress2 nvarchar 4000 YES
11 IHAddress3 nvarchar 4000 YES
12 IHAddress4 nvarchar 4000 YES
13 IHAddress5 nvarchar 4000 YES
14 IHDelAddress1 nvarchar 4000 YES
15 IHDelAddress2 nvarchar 4000 YES
16 IHDelAddress3 nvarchar 4000 YES
17 IHDelAddress4 nvarchar 4000 YES
18 IHDelAddress5 nvarchar 4000 YES
19 IHTel_Number nvarchar 4000 YES
20 IHContactName nvarchar 4000 YES
21 IHGlobal_Tax_Code nvarchar 4000 YES
22 IHInvoice_Date datetime YES
23 IHDatePosted datetime YES
24 IHLast_Synced datetime YES
25 IHLast_Modified datetime YES
26 IHNotes_1 ntext 1073741823 YES
27 IHNotes_2 ntext 1073741823 YES
28 IHNotes_3 ntext 1073741823 YES
29 IHTaken_By nvarchar 4000 YES
30 IHOrder_Number nvarchar 4000 YES
31 IHCust_Order_Number nvarchar 4000 YES
32 IHPayment_Ref nvarchar 4000 YES
33 IHGlobal_Nom_Code nvarchar 4000 YES
34 IHGlobal_Details nvarchar 4000 YES
35 IHInvoice_Type_Code nvarchar 4000 YES
36 IHOHid int YES ((0))
37 IHdatetype int YES ((0))
38 IHpercent int YES ((0))
39 IHType int YES ((0))
40 IHchid int YES ((0))
41 IHdateSent datetime YES
42 IHCurrency nvarchar 10 YES ('£')
43 IHPaymentTerms int YES ((0))
44 IHHideInvoice bit YES ((0))
45 IHFaultid int YES ((0))
46 IHInvoiceType int NO ((0))
47 ihdatepaid datetime YES ((0))
48 IHPaymentStatus int NO ((-1))
49 IHXeroID nvarchar 500 YES ('')
50 IHAmountPaid float YES ((0.00))
51 IHAmountDue float YES ((0.00))
52 IHEmailStatus int YES ((0))
53 IHPdfTemplateId int YES ((0))
54 IHDue_Date datetime YES ((0))
55 IHReference nvarchar 4000 YES ('')
56 IHisRecurringInvoice bit YES ((0))
57 IHrecurringInvoiceId int YES ((0))
58 IHAddContractAssets int YES ((0))
59 IHAddLabour int YES ((0))
60 IHAddTravel int YES ((0))
61 IHAddMileage int YES ((0))
62 IHAddItemsIssued int YES ((0))
63 IHAddPrePay int YES ((0))
64 IHQBOId int YES ((0))
65 IHscheduleDate datetime YES ((0))
66 IHbillingCategory int YES ((0))
67 IHXeroTenantId nvarchar 100 YES ('')
68 IHXeroBrandingThemeId nvarchar 100 YES ('')
69 IHXeroBrandingThemeName nvarchar 100 YES ('')
70 IHCreatedBy int YES ((-1))
71 IHDueDateInt int YES ((0))
72 IHDueDateType int YES ((0))
73 IHinternalnote ntext 1073741823 YES
74 IHAddProject int YES ((0))
75 IHKashflowTenantId int YES ((0))
76 IHKashflowId int YES ((0))
77 IHKashflowPdf nvarchar 500 YES ('')
78 IHoriginalclientid int YES ((0))
79 IHXeroStatus nvarchar 10 YES ('')
80 IHaddSalesOrder int YES ((0))
81 IHSnelStartId nvarchar 100 YES ('')
82 IHcurrencycode int YES ((0))
83 IHcurrencyConversionRate float YES ((1))
84 IHdateCreated datetime YES ((0))
85 ihprorataperiodsahead int YES
86 ihnextcreationperiod nvarchar 200 YES
87 IHQBOCompanyId nvarchar 100 YES
88 ihintacctrecordno int YES
89 IHPeriodStartDate datetime YES ((0))
90 IHPeriodEndDate datetime YES ((0))
91 ihcreditlinkedtoinvoiceid int YES ((0))
92 IHDBCId nvarchar 100 YES ('')
93 IHDBCCompanyId nvarchar 100 YES ('')
94 ihshid int YES
95 ihsagebusinessclouddetailsid int YES
96 ihsagebusinesscloudid nvarchar 500 YES
97 ihexactdivision int YES
98 ihexactid nvarchar 250 YES
99 ihxerolinetax nvarchar 100 YES
100 ihautoincreaseperiod int YES ((-1))
101 ihpercentincrease float YES ((0))
102 ihscheduleignoredelete bit YES
103 ihassignedunum int YES
104 ihapprovalstatus int YES
105 ihapprovalagent int YES
106 ihapprovalemailaddr nvarchar 500 YES
107 ihapprovalemailreply nvarchar 500 YES
108 ihtechrequestedapproval int YES
109 ihapprovalrequestdate datetime YES ((0))
110 ihxeropaymenturl nvarchar 1000 YES
111 ihgenericpaymenturl nvarchar 1000 YES
112 ihpercentdecimal decimal YES
113 ihintacctsaveloc nvarchar 1000 YES
114 ihintacctseparatetaxline int YES
115 IHTakePaymentOnDueDate int YES ((0))
116 ihintacctclass nvarchar 500 YES
117 ihintacctclassname nvarchar 500 YES
118 ihintaccttaxsol nvarchar 500 YES
119 ihintaccttaxsolname nvarchar 500 YES
120 ihintacctproject nvarchar 500 YES
121 ihintacctprojectname nvarchar 500 YES
122 ihsupplierid int YES
123 ihincludeinautopay bit YES
124 IHCreationSource nvarchar 200 YES
125 ihtypeid int YES
126 ihmyobrowversion nvarchar 500 YES
127 IHTwilioInvoice bit YES
128 ihinvoiceseparately bit YES
129 ihminimumpriceactive bit YES
130 ihminimumlinedescription nvarchar 1000 YES
131 ihminimumamount float YES
132 ihavalaradetailsid int YES
133 ihmarkcreditasused bit YES
134 ihvoided bit YES
135 IHQBOTermName nvarchar 200 YES
136 IHQBOTermID int YES ((0))
137 ihoriginaddress1 nvarchar 4000 YES
138 ihoriginaddress2 nvarchar 4000 YES
139 ihoriginaddress3 nvarchar 4000 YES
140 ihoriginaddress4 nvarchar 4000 YES
141 ihoriginaddress5 nvarchar 4000 YES
142 ihexactconditionname nvarchar 200 YES
143 ihexactconditionid nvarchar 200 YES
144 ihcompositetaxtotal float YES
145 ihdontsyncto3rdparty bit YES
146 ihapprovalticketid int YES
147 ihcreditdate datetime YES ((0))
148 ihcspinvoiceid int YES ((0))
149 ihbcdposted bit YES
150 ihlastremindersent datetime YES ((0))
151 ihimmediateproratarecurringinvoiceid int YES
152 ihchangeseq int YES ((1))
153 ihlastchangeagent int YES
154 ihisdownpayment bit YES
155 ihisrecognition bit YES
156 ihguid uniqueidentifier NO (newid())
157 ihlastinvoicedrevenue decimal YES

InvoicePayment (25 columns)

# Column Type Length Nullable Default
1 IPid int NO
2 IPIHid int NO
3 IPDate datetime NO
4 IPAmount float NO
5 IPMethod int NO
6 IPStatus int NO
7 IPMSID int NO
8 IPIntentID nvarchar 100 YES
9 IPIntentDate datetime YES
10 IPIntentResponse nvarchar -1 YES
11 IPWebhookReceivedDate datetime YES
12 IPWebhookPayload nvarchar -1 YES
13 ipsage50reference nvarchar 1000 YES ('')
14 ipqboid int YES ((0))
15 ipxeroid nvarchar 200 YES ('')
16 ipthirdpartyid nvarchar 250 YES
17 ipmyobid nvarchar 200 YES
18 iptype int YES
19 IPCreditNoteID int YES
20 IPPaymentMethod int YES
21 IPReference nvarchar 200 YES
22 ipnominalcode nvarchar 500 YES
23 ipposted bit YES
24 ipdateposted datetime YES ((0))
25 iplast_synced datetime YES ((0))

Tax (18 columns)

# Column Type Length Nullable Default
1 TaxID int NO
2 TaxDescription nvarchar 255 YES
3 TaxCode nvarchar 255 YES
4 TaxValue float YES
5 TaxXeroTenantId nvarchar 100 YES ('')
6 TaxThirdPartyDescription nvarchar 100 YES ('')
7 TaxIsComposite bit YES ((0))
8 TaxKashflowTenantId int YES ((0))
9 TaxQboCode nvarchar 10 YES ('')
10 TaxQboCodeName nvarchar 200 YES ('')
11 TaxQBOCompanyId nvarchar 100 YES
12 taxsagebusinesscloudtenantid int YES
13 taxexactdivision int YES
14 taxonseparateline bit YES
15 taxmyobdetailsid int YES
16 taxthirdpartyid nvarchar 1000 YES
17 TaxAvalaraId nvarchar 500 YES
18 taxcategorycode nvarchar 100 YES
Back to top