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 on the 'Product' and 'Edit product' screens. 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 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 report is filtered on the 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 the back office stage will be included.
IncludeAllLines (bool; default=false)
This allows reporting 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 showing ALL zero-priced services.
IncludeServicesWithZeroPriceShownOnInvoice (bool; default=false)
Normally, services with a zero price will not be shown in the report. This setting allows showing the zero prices of 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 adding 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, export values (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.
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 CurrencyForReporting, RecalculateOrders, IncludeCancelledOrders, 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 |
| AircraftRegistrationCodeWithDash |
|
|
|
AircraftTypeID |
|
|
|
AircraftTypeModelName |
|
|
|
Aircraft type IATA |
|
|
|
Aircraft number of seats |
|
|
|
Aircraft manufacturer name |
|
|
|
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 |
|
|
|
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 |
| From city |
|
|
|
From country |
|
|
|
From name and country |
|
|
|
Front Office Workflow Name |
|
Handling Order |
|
Fuel Ticket Number |
Fuel ticket number added by the user. |
123213 |
|
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 |
| HandlingStationIataCode |
|
|
|
HandlingStationCountry |
|
|
|
HandlingStationNameAndCountry |
|
|
|
HandlingStationCity |
|
|
|
HandlingStationDisplayName |
|
|
|
HandlingStationID |
|
|
|
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. |
|
|
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 child product |
Indicates that the product is a child product and has a visible parent product/header |
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 |
|
Is parent product |
Indicates that the serivice the a parent product with child products under. Use with 'Is top level product' to see indicate if it's the top in the product tree. |
TRUE |
|
Is on purchase order |
|
FALSE |
|
Is subtotal |
|
FALSE |
|
Is top level product |
Indicates that the service is top level of a product tree and is not a child product |
TRUE |
|
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 |
The Operator's address 'State' field. |
New York |
|
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. |
|
|
OutboundOriginSystemNotes |
Imported outbound flight leg system notes from Stonefield FIDS. See StonefieldConfiguration. |
TWR DEP / 6 PAX |
| Parent orderline ID |
Parent orderline/service unique ID in GUID format. Note, if there is no parent for the service then this will be blank. |
086d95a7-c48b-46cd-9c13-eb41a06asdasd |
|
Parent product |
Parent product code. Note, if there is no parent for the service then this will be blank. |
AP-FEES |
|
Parent product description |
Parent product description. Note, if there is no parent for the service then this will be blank. |
Airport fees |
|
Parent product ID |
Parent product unique ID in GUID format. Note, if there is no parent for the service then this will be blank. |
08asdaa7-c98b-46cd-9c13-eb41a06asdasd |
|
Parent product sort index |
Parent product sort index. Note, if there is no parent for the service then this will be blank. |
10 |
|
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 |
Handling order’s arrival Pax and Crew count summed. See ‘Pax count' and 'Crew count' field in order’s arrival flight leg. |
7 |
|
Pax crew count out |
Handling order’s departure Pax and Crew count summed. See ‘Pax count' and 'Crew count' field in order’s departure flight leg. |
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) |
| Product sort index |
|
100 |
|
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 |
|
|
|
Supplier Email 2 |
|
|
|
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 |
|
|
|
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
Pax Names: Pet Name(s): |
|
Supply order template body text |
|
Arrival:<b> ~MostActualArrivalTimeUTC~ / ~MostActualArrivalTimeLT~</b> Arriving From:<b> ~OriginStationNameCountry~/~OriginStationIcao~ </b>
Pax Names: <b>Pet Name(s):</b> |
|
ToIata |
|
GVA |
|
ToIcao |
|
LSGG |
|
ToName |
|
Geneva Cointrin |
| To city |
|
|
|
To country |
|
|
|
To name and country |
|
|
|
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 |
|
|
| Trip Support Provider Address 1 |
|
|
|
Trip Support Provider Address 2 |
|
|
|
Trip Support Provider City |
|
|
|
Trip Support Provider Country |
|
|
|
Trip Support Provider Email 1 |
|
|
|
Trip Support Provider Email 2 |
|
|
|
Trip Support Provider Fax |
|
|
|
Trip Support Provider Phone 1 |
|
|
|
Trip Support Provider Phone 2 |
|
|
|
Trip Support Provider State |
|
|
|
Trip Support Provider Website URL |
|
|
|
Trip Support Provider Zip code |
|
|
|
Trip Support Provider Contact Contact No |
|
|
|
Trip Support Provider Administration Contact No |
|
|
|
Unit code |
|
Pet(s) |
|
VatInvoiceCode |
|
E |
See also:
Comments
0 comments
Please sign in to leave a comment.