Orders query
The Orders query is used to generate order data, showing each order per row.
The orders query can be used in FBO One, to know for example how many orders were handled in a certain time period, or for reporting on total revenue per month for the top 10 customers. It will generate an Excel report where for every order one row is created.
Contents:
Filters
To determine what orders will be shown by the query, filters can be applied in FBO One. The filters available for the orders query are shown in the screenshot below. Filters with a asterisk sign * are mandatory.
Administration | Filters on Administration. Single or multiple Administrations can be selected. | YES |
Fbo location | Filters on FBO location. Single or multiple FBO locations can be selected. | NO |
Handling station | Filter by Handling station. See Handling station (Glossary). |
|
| Filter by date and time in UTC or LT of an order’s arrival or departure, use both on or after and before to create a data range. Note: ‘Arrival or departure date time’ UTC and LT filters cannot be used together. For OTC’s the start/end date/times are considered if data provider settings IncludeOtcOrdersByStartDate or IncludeOtcOrdersByEndDate are set to 'TRUE'. | NO |
Transaction date LT on or after / Transaction date LT before | Filter between order transaction on or after date and order transaction before date (last calculated date), use both on or after and before to create a data range. | NO |
Invoice date on or after / Invoice date before | Filter between Invoice on or after date and Invoice before date, use both on or after and before to create a data range. | NO |
| Filter by date and time in UTC or LT of batch settled timestamp in an order, use both on or after and before to create a data range.. See Online Payments (Administration). Note: ‘Batch Settled Timestamp’ UTC and LT filters cannot be used together. | NO |
| Filter by date and time of payment timestamps in an order, use both on or after and before to create a data range. Note: ‘Payment Timestamp’ UTC and LT filters cannot be used together. | NO |
Debtor or operator | Filter by contact present on an order’s debtor or operator field. | NO |
Debtor | Filters on an order’s debtor contact. | NO |
Operator | Filters on an order’s Operator contact. | NO |
Debtor's order number | Filter on an order’s ‘Debtor’s order number’. 
| NO |
Aircraft type | Filter by an order’s aircraft type. | NO |
Aircraft registration | Filter by an order’s aircraft registration. | NO |
Entry period | Filters on entry periods. See Entry periods (Back office). | NO |
Order | Filters on the Order ID. The Order ID can be found in the Classic screens when on Order page, in the browser address bar after ‘Orderid=’, e.g.  | NO |
Invoice | Filters on the Invoice ID. The Invoice ID can be found in the Classic screen when on Invoice page, in the browser address bar after ‘Invoiceid=’, e.g.  | NO |
Entry | Filters on the Entry ID. The Entry ID can be found in the Classic screen when on entry page, in the browser address bar after ‘Entryid=’, e.g.  | NO |
Data provider settings
The following ‘Data provider settings’ can be set in Reports (Administration) for the query of a report. All settings must be separated by a semi-colon ';'. If not specified the default settings apply.
The settings below are available for all of the following queries: Orders, Services, PaxCrew, Payments
RecalculateOrders | Boolean | TRUE | Recalculates all open orders that match the filter. This will make sure that any orders that are not yet invoiced are up to date. Each order is normally recalculated and stored when viewed in the order screen. An order may be out of date since it was last viewed, for example if a price agreement has been changed since. Setting RecalculateOrders to false will speed up the report and is generally safe. It may also prevent that the report fails due to a calculation error; for example when some orders have invalid data that need correction. |
CurrencyForReporting | CurrencyCode | Order default currency | This setting allows you to specify a currency that will be used to express the amounts in the report. FBO One will convert the Order Amount Incl. VAT, Amount Excl VAT and VAT Amount from the Order currency to this currency. When this setting is not used, FBO One always uses the Order Default currency to display the above amounts.
|
ReplaceCommas | Boolean | TRUE | Whether to replace commas inside custom values with underscores. The custom values are shown as a comma separated list. Setting this to TRUE, guarantees commas inside custom values won’t interfere with the separators of the list and allow to reliably check if a certain value is assigned to an order using a formula in the report. |
IncludeCancelledOrders | Boolean | TRUE | If Cancelled orders should be included in the report. |
ExtraDaysInFuture | Integer | 0 | This allows to add some days to the 'before' parameter. Note: Extra days are added to the following filters only: Arrival or departure date time, Transaction Date and Invoice date. Useful for daily reports that should span more than the default single day. |
IncludeOtcOrdersByStartDate | Boolean | FALSE | When set to true, it allows to add OTC orders which do not have flight legs into a order report based on the arrival/departure filter. The Arrival/Departure date filter will match on OTC order start time fields. |
IncludeOtcOrdersByEndDate | Boolean | FALSE | When set to true, it allows to add OTC orders which do not have flight legs into a order report based on the arrival/departure filter. The Arrival/Departure date filter will match on OTC order end time fields. |
IncludeOrdersInStage | String | FrontAndBackOffice | Whether or not orders in a particular stage should be included in the report. If not specified, orders being in front or in back office stage will be included. Possible values: Quote, FrontOffice, BackOffice, FrontAndBackOffice |
IncludeOrdersInEndState | Boolean | True | When setting this to false, the query will return only a set of orders that are not in a workflow end state. A typical workflow end state is the order invoiced state. For example you can use this setting when you need to report on all orders that haven’t been yet invoiced. |
Available fields
The following list shows all the columns that are available in the report.
Each field can also be to define custom formula's for auto-add products.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
public string HandlingOfficeShortName { get; set; }
public string HandlingStationName { get; set; }
public string HandlingStationIcaoCode { get; set; }
public string OrderKey { get; set; }
public string DebtorShortName { get; set; }
public string DebtorName { get; set; }
public string DebtorContactNumber { get; set; }
//DebtorCredit - the credit term days for the current order debtor and services
public int DebtorCredit { get; set; }
public string OperatorShortName { get; set; }
public string OperatorName { get; set; }
public string ApplicableAocNumber { get; set; }
public DateTime? ApplicableAocDate { get; set; }
public DateTime? MostActualArrivalDateTime { get; set; }
public string FromIcao { get; set; }
public int? PaxIn { get; set; }
public DateTime? MostActualDepartureDateTime { get; set; }
public string ToIcao { get; set; }
public int? PaxOut { get; set; }
public string RegistrationCode { get; set; }
public string AircraftTypeShortName { get; set; }
public string AircraftTypeModelName { get; set; }
public string HandlingCategory { get; set; }
public string Fop { get; set; }
public decimal? TotalAmount { get; set; }
public decimal? VatAmount { get; set; }
public decimal? NetAmount { get; set; }
public decimal? Invoice { get; set; }
public int? Entry { get; set; }
public string EntryPeriod { get; set; }
public string OrderState { get; set; }
public string ReceiptNumber { get; set; }
// This is now expressed as a local time in the timezone of the FBO
public DateTime PriceCalculationDateTime { get; set; }
public DateTime? InvoiceDate { get; set; }
public string InvoiceState { get; set; }
public string InboundFlightNumber { get; set; }
public bool ArrivalIsPending { get; set; }
public string OutboundFlightNumber { get; set; }
public bool DepartureIsPending { get; set; }
public Guid OrderID { get; set; }
public Guid HandlingOrderID { get; set; }
public Guid? AircraftRegistrationID { get; set; }
public string AircraftRegistrationCodeWithDash { get; set; }
public Guid? AircraftTypeID { get; set; }
public string AircraftTypeICAO { get; set; }
public string AircraftTypeIATA { get; set; }
public Guid? HandlingStationID { get; set; }
public bool IsPrimaryOrder { get; set; }
public int? PrimaryOrderNumber { get; set; }
public int? SecondaryOrderNumber { get; set; }
public Guid? OperatorID { get; set; }
public string OperatorShortestName { get; set; }
public Guid? DebtorID { get; set; }
public string DebtorShortestName { get; set; }
public string ParkingPosition { get; set; }
public int? MTOWkg { get; set; }
public int? MTOWton { get; set; }
public int? MTOWInUSPounds { get; set; }
public string ExternalRemarks { get; set; }
public string InternalRemarks { get; set; }
public string HandlingCountryName { get; set; }
public string HandlingCountryCode { get; set; }
public bool? HandlingCountryIsEU { get; set; }
public string HandlingStationDisplayName { get; set; }
public string DebtorOrderNo { get; set; }
public string BillingRemark { get; set; }
public string TripNumber { get; set; }
public string PilotNamesIn { get; set; }
public string PilotNamesOut { get; set; }
public string SecondInCommandNamesIn { get; set; }
public string SecondInCommandNamesOut { get; set; }
public int? CrewCountIn { get; set; }
public int? CrewCountOut { get; set; }
public int? PaxCountIn { get; set; }
public int? PaxCountOut { get; set; }
public int? PaxCrewCountIn { get; set; }
public int? PaxCrewCountOut { get; set; }
public string FlightTypeIn { get; set; }
public string FlightTypeOut { get; set; }
public string FlightNumberIn { get; set; }
public string FlightNumberOut { get; set; }
public int? NumberOfSeats { get; set; }
public Guid? TripSupportProviderID { get; set; }
public string TripSupportProviderName { get; set; }
public string TripSupportProviderShortestName { get; set; }
public string OperatorAddress1 { get; set; }
public string OperatorAddress2 { get; set; }
public string OperatorZipcode { get; set; }
public string OperatorCity { get; set; }
public string OperatorCountry { get; set; }
public string OperatorCountryForVAT { get; set; }
public string OperatorPhone1 { get; set; }
public string OperatorEmail1 { get; set; }
public string OperatorEmail2 { get; set; }
public string OperatorWebsiteURL { get; set; }
public string OperatorVatNumber { get; set; }
public string OperatorAocNumber { get; set; }
public DateTime? OperatorAocExpireDate { get; set; }
public string OperatorCarrierCode { get; set; }
public string DebtorAddress1 { get; set; }
public string DebtorAddress2 { get; set; }
public string DebtorZipcode { get; set; }
public string DebtorCity { get; set; }
public string DebtorCountry { get; set; }
public string DebtorCountryForVAT { get; set; }
public string DebtorPhone1 { get; set; }
public string DebtorEmail1 { get; set; }
public string DebtorEmail2 { get; set; }
public string DebtorWebsiteURL { get; set; }
public string DebtorVatNumber { get; set; }
public string DebtorAocNumber { get; set; }
public DateTime? DebtorAocExpireDate { get; set; }
public string DebtorCarrierCode { get; set; }
public string IsDomesticIn { get; set; }
public bool? IsGenDecRequiredIn { get; set; }
public string IsDomesticOut { get; set; }
public bool? IsGenDecRequiredOut { get; set; }
public string AirportSlotDescriptionIn { get; set; }
public string AirportSlotRemarksIn { get; set; }
public string AirportSlotDescriptionOut { get; set; }
public string AirportSlotRemarksOut { get; set; }
public DateTime? MostActualArrivalDateTimeLT { get; set; }
public DateTime? MostActualDepartureDateTimeLT { get; set; }
public DateTime? StaDateTime { get; set; } // UTC
public DateTime? StaDateTimeLt { get; set; }
public DateTime? EtaDateTime { get; set; } // UTC
public DateTime? EtaDateTimeLt { get; set; }
public DateTime? AtaDateTime { get; set; } // UTC
public DateTime? AtaDateTimeLt { get; set; }
public DateTime? StdDateTime { get; set; } // UTC
public DateTime? StdDateTimeLt { get; set; }
public DateTime? EtdDateTime { get; set; } // UTC
public DateTime? EtdDateTimeLt { get; set; }
public DateTime? AtdDateTime { get; set; } // UTC
public DateTime? AtdDateTimeLt { get; set; }
public DateTime? InitialOnBlocksDateTimeUtc { get; set; }
public DateTime? FinalOffBlocksDateTimeUtc { get; set; }
public DateTime? InitialOnBlocksDateTimeLt { get; set; }
public DateTime? FinalOffBlocksDateTimeLt { get; set; }
public DateTime? TobtDateTimeUtc { get; set; }
public DateTime? TobtDateTimeLt { get; set; }
public DateTime? TsatDateTimeUtc { get; set; }
public DateTime? TsatDateTimeLt { get; set; }
public string HandlingStationIataCode { get; set; }
public string FromIata { get; set; }
public string ToIata { get; set; }
public string FromName { get; set; }
public string ToName { get; set; }
public string MovementMessageContactsIn { get; set; }
public string MovementMessageContactsOut { get; set; }
public string PaxNamesIn { get; set; }
public string PaxNamesOut { get; set; }
public string TripSupportProviderAddress1 { get; set; }
public string TripSupportProviderAddress2 { get; set; }
public string TripSupportProviderZipcode { get; set; }
public string TripSupportProviderCity { get; set; }
public string TripSupportProviderCountry { get; set; }
public string TripSupportProviderPhone1 { get; set; }
public string TripSupportProviderEmail1 { get; set; }
public string TripSupportProviderEmail2 { get; set; }
public string TripSupportProviderWebsiteURL { get; set; }
public string DebtorFax { get; set; }
public string OperatorFax { get; set; }
public string TripSupportProviderFax { get; set; }
public string HandlingStationCountry { get; set; }
public string HandlingStationNameAndCountry { get; set; }
public string FromStationName { get; set; }
public string FromStationCountry { get; set; }
public string FromStationNameAndCountry { get; set; }
public string ToStationName { get; set; }
public string ToStationCountry { get; set; }
public string ToStationNameAndCountry { get; set; }
public string FromStationCity { get; set; }
public string ToStationCity { get; set; }
public string HandlingStationCity { get; set; }
public string FlightNumberIfNotRegistrationIn { get; set; }
public string FlightNumberIfNotRegistrationOut { get; set; }
public string RegistrationAndFlightNumberIfNotRegistrationIn { get; set; }
public string RegistrationAndFlightNumberIfNotRegistrationOut { get; set; }
public string PlanBoardMessagesShort { get; set; }
public string PlanBoardMessagesFull { get; set; }
public bool QuickTurn { get; set; }
public Guid AdministrationID { get; set; }
public string AdministrationName { get; set; }
public Guid FbolocationID { get; set; }
public string FbolocationShortName { get; set; }
public string DebtorContactContactNo { get; set; }
public string DebtorAdministrationContactNo { get; set; }
public string OperatorContactContactNo { get; set; }
public string OperatorAdministrationContactNo { get; set; }
public int? BaggagePiecesIn { get; set; }
public int? BaggageWeightIn { get; set; }
public int? BaggagePiecesOut { get; set; }
public int? BaggageWeightOut { get; set; }
public DateTime? StartDateTime { get; set; } // UTC
public DateTime? StartDateTimeLt { get; set; }
public DateTime? EndDateTime { get; set; } // UTC
public DateTime? EndDateTimeLt { get; set; }
public string DebtorState { get; set; }
public string OperatorState { get; set; }
public string TripSupportProviderState { get; set; }
public string FlightRulesIn { get; set; }
public string FlightRulesOut { get; set; }
public string AircraftManufacturerName { get; set; }
public string AircraftSerialNumber { get; set; }
public int? AircraftNumberOfEngines { get; set; }
public string CurrencyCode { get; set; }
public decimal? TotalAmountInBookedCurrency { get; set; }
public decimal? VatAmountInBookedCurrency { get; set; }
public decimal? NetAmountInBookedCurrency { get; set; }
public string BookedCurrencyCode { get; set; }
public bool IsPurchase { get; set; }
//IsCredit - marks if order is a credit order, via the "Credit order" button
public bool IsCredit { get; set; }
public bool IsCredited { get; set; }
public bool IsVisibleForOPS { get; set; }
public bool IsPrimaryOrder { get; set; }
public DateTime CreatedDateTimeUTC { get; set; }
public DateTime CreatedDateTimeLT { get; set; }
public int? TransitPaxCountIn { get; set; }
public int? TransitPaxCountOut { get; set; }
public int? NonTransitPaxCountIn { get; set; }
public int? NonTransitPaxCountOut { get; set; }
public decimal? AlreadyPaid { get; set; }
public decimal? AmountDue { get; set; }
public decimal? AlreadyPaidInBookedCurrency { get; set; }
public decimal? AmountDueInBookedCurrency { get; set; }
public string OperatorsHandlingOrderNumber { get; set; }
public string OperatorsHandlingOrderNumberOrigin { get; set; }
public string DebtorsOrderNumber { get; set; }
public string FrontOfficeWorkflowName { get; set; }
public string StageName { get; set; }
public int StageNumericValue { get; set; }
public string DebtorHeadquarterName { get; set; }
public string DebtorHeadquarterNumber { get; set; }
public string OperatorHeadquarterName { get; set; }
public string OperatorHeadquarterNumber { get; set; }
public string TripSupportHeadquarterName { get; set; }
public string TripSupportHeadquarterNumber { get; set; }
public DateTime? EtaAtDestinationUtc { get; set; }
public DateTime? EtaAtDestinationLt { get; set; }
public DateTime? AirportSlotInDateTimeUtc { get; set; }
public DateTime? AirportSlotInDateTimeLt { get; set; }
public DateTime? AirportSlotOutDateTimeUtc { get; set; }
public DateTime? AirportSlotOutDateTimeLt { get; set; }
public decimal? LengthInMeter { get; set; }
public decimal? WingSpanInMeter { get; set; }
public string InboundOriginSystemId { get; set; }
public string InboundOriginSystemNotes { get; set; }
public DateTime? InboundOriginSystemIdLastSeenAtUtc { get; set; }
public string InboundOriginSystemLastRow { get; set; }
public string OutboundOriginSystemId { get; set; }
public string OutboundOriginSystemNotes { get; set; }
public DateTime? OutboundOriginSystemIdLastSeenAtUtc { get; set; }
public string OutboundOriginSystemLastRow { get; set; }
public string AircraftRegistrationCountry { get; set; }
public string InvoiceInternalRemarks { get; set; }
public DateTime? AirwaySlotInDateTimeUtc { get; set; }
public DateTime? AirwaySlotInDateTimeLt { get; set; }
public DateTime? AirwaySlotOutDateTimeUtc { get; set; }
public DateTime? AirwaySlotOutDateTimeLt { get; set; }
public byte[] ReportLogoImage { get; set; }
public byte[] QRCode { get; set; }See also: