Services query

The Services query is used to export the service data on Orders, showing each service per row.

If you want to know how many services were sold and how much revenue was generated, or what services are ordered by clients for the next day, the services query can be used.

In FBO One two terms are used: Service and Product.

  • A product page in FBO One is where the attributes of a product are stored; the name, and settings. price agreements, suppliers. These can be viewed in the 'Product' and 'Edit product' screen. A service is the product when used on an order.

  • A service has a quantity, displayed on an Order. To determine what services will be shown by the query, filters can be applied in FBO One.

Beware: Some orders are not related to the handling of aircraft, such as orders for monthly parking fees or equipment rental. In FBO One the OTC (Over The Counter) orders are used for this. These orders are related to an aircraft registration but have no flight legs. Since they have no flight legs the OTC orders have no arrival or departure times. In FBO One the order date is the departure time of the order. The order date is used for pricing: only prices valid on that date will be taken into account. For OTC orders the invoice date is used as the date prices are referenced to, since there is no departure time. So for services on OTC orders the 'Arrival or departure date time' and 'Order date' filters cannot be used as they cannot display the services on OTC orders in the Excel report. Services on OTC orders will only be shown in the services report if the OTC orders are invoiced and the the report is filtered on invoice date.

To include OTC Orders using their Start/End Dates, the following data provider settings can be used: 

IncludeOtcOrdersByStartDate=true
IncludeOtcOrdersByEndDate=true 

Note: as this query was designed for financial reports, it won't show free services by default!

Optional Settings

IncludeOrdersInStage(string; default = FrontAndBackOffice; possible values: Quote,FrontOffice,BackOffice,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.

IncludeAllLines (bool; default=false)

This allows to report on all service lines of an order, including header lines and collapsed lines which are normally not included in the reports.

When all lines are included, you should filter on the IsAmountHidden column before summing amounts. This prevents that header lines showing subtotals and collapsed lines are included in the total.

IncludeServicesWithZeroPrice (bool; default=false)
Normally, services with a zero price will not be shown in the report. This setting allows to show ALL zero priced services.

IncludeServicesWithZeroPriceShownOnInvoice (bool; default=false)
Normally, services with a zero price will not be shown in the report. This setting allows to show the zero prices services shown on the invoice. Setting this to false while IncludeServicesWithZeroPrice is true, will result in an error. (available after version 2986)

extraDaysInFuture (int; default = 0)
This allows to add some days to the 'end' parameter. Useful for daily reports that should span more than the default single day.

IncludeExportValues (bool; default=false) [DEPRECATED]
Normally, exportvalues (custom values) will not be shown. This setting allows showing export values in the report. If a service has multiple export values, extra lines will be created so each line has one service and one export value.
New behavior: use a custom property type column and specify the custom property you need. eg:

The settings defined for the Orders Query are also available here, for example CurrencyForReportingRecalculateOrdersIncludeCancelledOrders, IncludeOrdersInEndState, etc

Columns

Name

Description

Example

Administration name

The Administration name.

FBO One

AdministrationID

The Administration's unique ID in GUID format.

9bf6dcf7-ac35-46eb-8a5a-f60ec90ff9f3

Aircraft number of engines

The engine count for the aircraft registration’s aircraft type. See ‘Number of engines’ field in aircraft type.

2

Aircraft serial number

Aircraft registration’s serial number. See ‘Serial number’ field in aircraft registration.

125123

Aircraft type ICAO

Aircraft type's ICAO code for the registration. See List of aircraft type designators.

GLF5

AircraftRegistrationCode

Registration code for the aircraft.

NVART

AircraftRegistrationID

Aircraft registration’s unique ID in GUID format.

447c5755-8de7-41b5-90a0-953de06ccaec

AircraftTypeShortName

Aircraft registration’s aircraft type ‘Shortname’.

G550

Amount Excl. VAT

 

60

Amount Excl. VAT in booked currency

 

60

Amount Incl. VAT

 

60

Amount Incl. VAT in booked currency

 

60

Amount VAT

 

0

Amount VAT in booked currency

 

0

AmountInclVat

 

60

Arrival or Departure

 

A

Billing Remark

 

 

Booked currency code

 

EUR

CostCenter

 

COP

CountryForVat

 

Switzerland (CH)

Crew count in

 

2

Crew count out

 

3

Crew names in

 

Heimo KONCILIA, CHRISTIAN MARC GILBERT PICHON, James Cairns MACLEOD

Crew names out

 

Heimo KONCILIA, Michaela KONCILIA, James Cairns MACLEOD

CurrencyCode

 

EUR

Debtor Address 1

 

Business Aviation Center

Debtor Address 2

 

PO BOX 1

Debtor City

 

Zurich Airport

Debtor Country

 

Switzerland (CH)

Debtor Country for VAT

 

Switzerland (CH)

Debtor Email 1

 

fly@execujet.eu

Debtor Email 2

 

 

Debtor Fax

 

 

Debtor Headquarter Name

 

 

Debtor Headquarter Number

 

 

Debtor name

 

Quebec Air 12

Debtor Order No

 

 

Debtor Phone 1

 

0041 448 041616

Debtor Phone 2

 

0041 448 041617

Debtor State

 

SWITZERLAND

Debtor Zipcode

 

8058

DebtorAdministrationContactNo

 

 

DebtorContactContactNo

 

REX004

DebtorContactID

Debtor contact for the order unique ID in GUID format.

e3a7a287-59ee-4620-b666-5ac073fcc787

DebtorContactShortname

 

Quebec Air 12

DebtorOrderNoOnly

 

 

DebtorVatNumber

 

Non-EU

Delivery DateTime LT

 

 

Delivery DateTime UTC

 

 

Entry ID

Entry the order is booked in unique ID in GUID format.

qwe1232-55ee-4as0-b6s6-5asd73fcc71w

EntryNo

 

 

EntryPeriod

 

 

ExportCode

 

 

ExportValue

 

 

Fbolocation short name

 

FBO

FbolocationID

FBO location of the order unique ID in GUID format.

4e72f72c-b4bb-479f-b376-cb232c9947b4

Flight number in

 

 

Flight number out

 

 

Flight rules in

 

 

Flight rules out

 

 

Flight type in

 

Private

Flight type out

 

Private

FOP

 

BILL EUR

FopMappedProductCode

 

 

FromIata

 

GVA

FromIcao

 

LSGG

FromName

 

Geneva Cointrin

Front Office Workflow Name

 

Handling Order

Fuel Ticket Number

 

 

Handling category

 

Band M

Handling order ID

Handling order unique ID in GUID format.

dfb09196-ac58-4cb6-b743-0d344c0a883a

HandlingOfficeShortName

 

FBO

HandlingStationIcaoCode

 

EGKB

HandlingStationName

 

Biggin Hill

InboundOriginSystemId

Origin system ID for imported inbound flight legs. See StonefieldConfiguration.

RX000729022020011638A

InboundOriginSystemIdLastSeenAtUtc

Most recent inbound flight leg import time using Stonefield FIDS. See StonefieldConfiguration.

29-Feb-20

InboundOriginSystemLastRow

JSON import data for inbound flight leg from Stonefield FIDS. See StonefieldConfiguration.

{"Registration":"2TRAV","Aircraft":"GLF5","Callsign":"2TRAV","ADEP":"LSGG","ADES":"EGKB","From_To":"LSGG","EdmATD_ATA_Date":"2020-02-29T00:00:00Z","ATD_ATA":"1128","EdmSignificant_Date":"2020-02-29T00:00:00Z","Significant_Time":"1128","Category":"A","Touch_and_Gos":"0","Parking":"BH HANG 1","POB":"0","Notes":"TWR / DEP 3RD / SKYPETS / 5 PAX","ID":"RX000729022020011638A","Approaches":"                    "}

InboundOriginSystemNotes

Imported inbound flight leg system notes from Stonefield FIDS. See StonefieldConfiguration.

TWR / DEP 3RD / SKYPETS / 5 PAX

Internal remarks

 

 

Invoice

 

1014561

Invoice Debtor Country for VAT

 

Switzerland (CH)

Invoice ID

 

b97e6581-8ba8-487a-adbd-5e4268c28f01

Invoice unit price

 

60

Invoice unit price in booked currency

 

60

InvoiceDate

 

03-Mar-20

InvoiceInternalRemarks

 

 

InvoiceState

 

Created

Is amount hidden

 

FALSE

Is arrival pending

 

FALSE

Is departure pending

 

FALSE

Is on credit order

 

FALSE

Is on credited order

 

FALSE

Is on primary order

Indicates if the service is on a primary or a secondary/split order.

  • TRUE = Primary order e.g. FBO-100

  • FALSE = Secondary/Split order e.g. FBO-100-2

TRUE

Is on purchase order

 

FALSE

Is subtotal

 

FALSE

Is Visible For OPS

 

FALSE

Ledger

 

5200

Ledger Description

 

Handling Fees

MostActualArrivalDateTime

 

29-Feb-20 11:28

MostActualArrivalDateTimeLT

 

29-Feb-20 11:28

MostActualDepartureDateTime

 

03-Mar-20 13:32

MostActualDepartureDateTimeLT

 

03-Mar-20 13:32

MTOW in US Pounds

Aircraft registration’s maximum takeoff weight in US pounds. See ‘Specific MTOW’ field in registration, if blank see 'Default MTOW’ in aircraft type of registration.

90830

MTOW kg

Aircraft registration’s maximum takeoff weight in kilograms. See ‘Specific MTOW’ field in registration, if blank see 'Default MTOW’ in aircraft type of registration.

41200

MTOW ton

Aircraft registration’s maximum takeoff weight in ton (rounded up). See ‘Specific MTOW’ field in registration, if blank see 'Default MTOW’ in aircraft type of registration.

42

Operator Address 1

 

1 Main Road

Operator Address 2

 

Flat 2

Operator City

 

London

OperatorContactID

 

006f411f-77ff-46b3-b645-ddfbff84af05

OperatorContactContactNo

 

C1213123

OperatorAdministrationContactNo

 

D1231232

Operator Country

 

United Kingdom (GB)

Operator Country for VAT

 

United Kingdom (GB)

Operator Email 1

 

ops@fboone.com

Operator Email 2

 

acc@fboone.com

Operator Fax

 

+44 13123 123123

Operator Headquarter Name

 

 

Operator Headquarter Number

 

 

Operator Phone 1

 

+44 12312 123123

Operator Phone 2

 

+44 12223 212322

Operator name

 

Victor Jet 12

Operator shortest name

 

Victor Jet 12

Operator State

 

 

Operator Website URL

 

 

Operator Zip code

 

 

Order and cost center

 

FBO-57133 COP

Order and ledger

 

FBO-57133 5200

Order and product

 

FBO-57133 PETFLY ADDITIONAL PET(S)

Order ID

The order’s unique ID in GUID format.

353f3758-29c1-4b9f-bbe7-2c26400743ea

Order key

The Order key the service is added to.

FBO-57133

OrderLineId

The orderline’s (service) unique ID in GUID format.

ea9ab97a-64e1-416e-aa6e-fbda29b4b57b

OrderState

The Order current workflow state the service is added to.

Invoiced

OtcEndDateTimeLt

 

 

OtcEndDateTimeUtc

 

 

OtcStartDateTimeLt

 

 

OtcStartDateTimeUtc

 

 

OutboundOriginSystemId

Origin system ID for the imported outbound flight leg using Stonefield FIDS. See StonefieldConfiguration.

RX021103032020121836D

OutboundOriginSystemIdLastSeenAtUtc

Most recent outbound flight leg import time using Stonefield FIDS. See StonefieldConfiguration.

03-Mar-20

OutboundOriginSystemLastRow

JSON import data for outbound flight leg from Stonefield FIDS. See StonefieldConfiguration.

{"Registration":"2TRAV","Aircraft":"GLF5","Callsign":"2TRAV","ADEP":"EGKB","ADES":"LSGG","From_To":"LSGG","EdmATD_ATA_Date":"2020-03-03T00:00:00Z","ATD_ATA":"1332","EdmSignificant_Date":"2020-03-03T00:00:00Z","Significant_Time":"1332","Category":"D","Touch_and_Gos":"0","Parking":"BH APR M","POB":"0","Notes":"TWR DEP / 6 PAX","ID":"RX021103032020121836D","Approaches":"                    "}

OutboundOriginSystemNotes

Imported outbound flight leg system notes from Stonefield FIDS. See StonefieldConfiguration.

TWR DEP / 6 PAX

Parking position

Current parking position for the order.

STAND 1

Pax count in

Handling order’s arrival Pax count. See ‘Pax count' field in order’s arrival flight leg.

5

Pax count out

Handling order’s departure Pax count. See ‘Pax count' field in order’s departure flight leg.

6

Pax crew count in

 

7

Pax crew count out

 

9

Pax names in

 

Hansie, Honey

Pax names out

 

 

Pilot names in

 

Heimo KONCILIA

Pilot names out

 

Heimo KONCILIA

PriceCalculationDateTime

 

03-Mar-20 13:32

Product

 

PETFLY ADDITIONAL PET(S)

Product description

 

PetFly - Additional Pet(s)

Product ID

 

086d95a7-c48b-46cd-9c13-eb41a067a11b

ProductDescription

 

PetFly - Additional Pet(s)

ProgressEndLt

 

 

ProgressEndUtc

 

 

ProgressStartLt

 

 

ProgressStartUtc

 

 

Purchase amount in booked currency

 

 

Purchase amount in supplier currency

 

 

QRCode

 

 

Quantity

 

1

Remarks

 

 

Second In Command names in

 

CHRISTIAN MARC GILBERT PICHON

Second In Command names out

 

Michaela KONCILIA

Service state

 

Completed

ServiceDateTimeLt

 

29-Feb-20 11:28

ServiceDateTimeUtc

 

29-Feb-20 11:28

Show in ramp screen

 

FALSE

SubAdmin

 

 

Supplier Address 1

 

276-1622 Pellentesque Av.

Supplier Address 2

 

 

Supplier City

 

Québec City

SupplierContactContactNo

 

C222222

SupplierAdministrationContactNo

 

D333333

Supplier Country

 

Canada (CA)

Supplier Email 1

 

risus.a@diam.co.uk

Supplier Email 2

 

Mauris.blandit.enim@semper.co.uk

Supplier Fax

 

(015) 88289084

Supplier Headquarter Name

 

 

Supplier Headquarter Number

 

 

Supplier invoice created date time LT

 

 

Supplier invoice created date time UTC

 

 

Supplier Invoice Currency Code

 

 

Supplier Invoice Date

 

 

Supplier Invoice Line Amount Vat

 

 

Supplier Invoice Line Vat Code

 

 

Supplier Invoice Line Vat Code Id

 

 

Supplier Invoice Number

 

 

Supplier Invoice Supplier Name

 

 

Supplier Invoice Total Amount

 

 

Supplier invoice total amount in booked currency

 

 

Supplier Invoice Unit price

 

 

Supplier invoice unit price in booked currency

 

 

Supplier name

 

PetFly LTD

Supplier order number

 

 

Supplier Phone 1

 

0800 822400

Supplier Phone 2

 

563-9592

Supplier State

 

Quebec

Supplier Website URL

 

PharetraFoundation.com

Supplier Zip code

 

G5K 0G4

SupplierContactID

 

b29dab78-01ec-4412-947f-f2e95c3619e5

SupplierInvoiceID

 

s29da128-005c-4412-917f-f2e95c323215

SupplierInvoiceSupplierContactID

 

ss19dab8-02ec-4412-847f-a2295c31451

SupplierShortName

 

PetFly LTD

Supply order compiled body text

 

Arrival: 29-Feb-20 11:28 (UTC) / 29-Feb-20 11:28 (LT)

Arriving From: Geneva Cointrin Geneva Switzerland/LSGG
Operator: Victor Jet 12

 

Pax Names:

Pet Name(s):

Supply order template body text

 

Arrival:<b> ~MostActualArrivalTimeUTC~ / ~MostActualArrivalTimeLT~</b>

Arriving From:<b> ~OriginStationNameCountry~/~OriginStationIcao~ </b>
Operator:<b> ~OperatorName~</b>

 

Pax Names:

<b>Pet Name(s):</b>

ToIata

 

GVA

ToIcao

 

LSGG

ToName

 

Geneva Cointrin

Transit Pax Count In

Handling order’s arrival transit Pax count. See ‘Transit pax count' field in order’s arrival flight leg.

0

Transit Pax Count Out

Handling order’s departure transit Pax count. See ‘Transit pax count' field in order’s departure flight leg.

0

Trip Number

 

 

Trip Support Headquarter Name

 

 

Trip Support Headquarter Number

 

 

Trip Support Provider ID

 

 

Trip Support Provider name

 

 

Trip Support Provider shortest name

 

 

Unit code

 

Pet(s)

VatInvoiceCode

 

E

See also:

Movements query

Orders query