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.

Filters
Data provider settings
Available fields

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 an asterisk sign * are mandatory.

Filter example:

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
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 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 us 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 us 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 an 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 an 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 the 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 formulas for auto-add products.

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 Guid? InvoiceID { get; set; }
public string InvoiceDebtorCountryForVat { get; set; }
public int? Entry { get; set; }
public Guid? EntryID { 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 DateTime? InvoiceDueDate { get; set; }
public DateTime? InvoiceCreatedDate { 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 OperatorPhone2 { 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 DebtorPhone2 { 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? StdFromOriginDateTimeUtc { get; set; }
public DateTime? StdFromOriginDateTimeLt { get; set; }
public DateTime? EtdFromOriginDateTimeUtc { get; set; }
public DateTime? EtdFromOriginDateTimeLt { get; set; }
public DateTime? AtdFromOriginDateTimeUtc { get; set; }
public DateTime? AtdFromOriginDateTimeLt { 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 TripSupportProviderPhone2 { 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 FromStationGroup { get; set; }
public string ToStationGroup { 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 FboLocationAddress1 { get; set; }
public string FboLocationAddress2 { get; set; }
public string FboLocationCity { get; set; }
public string FboLocationState { get; set; }
public string FboLocationCountry { get; set; }
public string FboLocationZipCode { get; set; }
public string FboLocationPoBox { get; set; }
public string FboLocationFrequency { get; set; }
public string FboLocationEmail { get; set; }
public string FboLocationEmailAc { get; set; }
public string FboLocationPhone { get; set; }
public string FboLocationFax { get; set; }
public string FboLocationSita { get; set; }
public string FboLocationReportFboLocation { 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 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 InboundOriginSystemOperationStatusCode { get; set; }
public string InboundOriginSystemOperationStatusDescription { 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 string OperatorCustomValues { get; set; }
public string TripSupportProviderCustomValues { get; set; }
public string DebtorCustomValues { get; set; }
public string AircraftRegistrationCustomValues { get; set; }
public string AircraftTypeCustomValues { get; set; }
public string FromStationCountryCustomValues { get; set; }
public string ToStationCountryCustomValues { 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; }

Search

Welcome to our Support Portal

Contents

Filters

Data provider settings

Available fields

Comments