Orders query

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.

Filter

Description

Mandatory

Filter

Description

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

 

  • Arrival or departure date time (UTC) on or after / Arrival or departure date time (UTC) before

  • Arrival or departure date time (LT) on or after / Arrival or departure date time (LT) before

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

  • Batch Settled Timestamp (UTC) on or after / Batch Settled Timestamp (UTC) before: 

  • Batch Settled Timestamp (LT) on or after / Batch Settled Timestamp (LT) before

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

  • Payment Timestamp (UTC) on or after / Payment Timestamp (UTC) before

  • Payment Timestamp (LT) on or after / Payment Timestamp (LT) before

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

Setting

Type

Default

Description

Setting

Type

Default

Description

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.

  • For old orders it might happen that an exchange rate is not defined in the past. FBO One will use the most recent exchange rate that exists in the Exchange Rates table.

  • Using a currency code that doesn't exist in the Exchange Rate table will return an error.

  • The amounts in any of the other columns, such as "Amount Excl VAT in booked currency" are not converted to the CurrencyForReporting.

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 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 Guid? HandlingStationID { 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 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 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 DateTime? InitialOnBlocksDateTimeUtc { get; set; } public DateTime? FinalOffBlocksDateTimeUtc { get; set; } public DateTime? InitialOnBlocksDateTimeLt { get; set; } public DateTime? FinalOffBlocksDateTimeLt { 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; }

See also:

Movements query

Services query

PaxCrew query