Payments query

Contents:

Filters

  • Administration: Filters on Administration. Single or multiple Administrations can be selected. Mandatory field.

  • Fbo location: Filters on FBO location. Single or multiple FBO locations can be selected.

  • 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: Filter between date/time (in UTC) of order’s arrival or departure. For OTC’s the start/end date/times are considered if data provider settings IncludeOtcOrdersByStartDate or IncludeOtcOrdersByEndDate are set to 'True'.

  • Arrival or departure date time (LT) on or after / Arrival or departure date time (LT) before: Filter between date/time (in UTC) of order’s arrival or departure. For OTC’s the start/end date/times are considered if data provider settings IncludeOtcOrdersByStartDate or IncludeOtcOrdersByEndDate are set to 'True'.

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

  • Invoice date on or after / Invoice date before: Filter between Invoice on or after date and Invoice before date.

  • Batch Settled Timestamp (UTC) on or after / Batch Settled Timestamp (UTC) before: Filter between date/times (in UTC) for batch settled timestamp. See Online Payments (Administration).

  • Batch Settled Timestamp (LT) on or after / Batch Settled Timestamp (LT) before: Filter between date/times (in UTC) for batch settled timestamp. See Online Payments (Administration).

  • Payment Timestamp (UTC) on or after / Payment Timestamp (UTC) before: Filter between date/times (in UTC) for payment timestamp.

  • Payment Timestamp (LT) on or after / Payment Timestamp (LT) before: Filter between date/times (in local time) for payment timestamp.

  • Debtor or operator: Filter by contact present on an order’s debtor or operator field.

  • Debtor: Filters on an order’s debtor contact.

  • Operator: Filters on an order’s Operator contact.

  • Debtor's order number: Filter on and order’s ‘Debtor’s order number’.

  • Aircraft type: Filter by an order’s aircraft type.

  • Aircraft registration: Filter by an order’s aircraft registration.

  • Entry period: Filters on entry periods.

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

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

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

     

Dataprovider 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 applies..

Setting

Type

Default

Description

Setting

Type

Default

Description

RecalculateOrders

Boolean

True

Recalculates all open orders that match the filter. This makes sure that any orders that are not yet invoiced are up to date.

CurrencyForReporting

String

-

Specifies a currency used to convert amount columns 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’s default currency to display any amounts.

  • For orders that have no exchange rate to use based on order date, 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

Indicates whether to replace commas inside custom values with underscores. The custom values are shown as a comma separated list. Setting this to yes, guarantees commas inside export values don'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

Indicate whether 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.

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.

Columns

Columns showing payment information are highlighted in green.

Name

Description

Example

Administration ID

 

8fde2311-9f95-40b8-a978-b0da7531c4ef

Administration Name

 

FBO One

Aircraft Manufacturer name

 

Bombardier

Aircraft number of engines

 

2

Aircraft registration code with dash

 

N12345

Aircraft registration ID

 

f0e7d85b-fa25-4317-b3c2-0255c6b7a213

Aircraft serial number

 

122212

Aircraft type ICAO

 

LJ40

Aircraft type ID

 

e6ca8c14-64a4-4975-8fa9-00ac3a2a9b59

AircraftRegistrationCountry

 

United States of America

AircraftTypeModelName

 

Learjet 40XR

AircraftTypeShortName

 

LJ40XR

Airport Slot Description In

 

21:05 (LT) | EHAM2911200538 (Confirmed)

Airport Slot Description Out

 

10:30 (LT) | EHAM2911200539 (Confirmed)

Airport Slot Remarks In

 

 

Airport Slot Remarks Out

 

 

AirportSlotInDateTimeLt

 

29-Nov-20 21:05

AirportSlotInDateTimeUtc

 

29-Nov-20 20:05

AirportSlotOutDateTimeLt

 

02-Dec-20 10:30

AirportSlotOutDateTimeUtc

 

02-Dec-20 09:30

AirwaySlotInDateTimeLt

 

29-Nov-20 21:00

AirwaySlotInDateTimeUtc

 

29-Nov-20 20:00

AirwaySlotOutDateTimeLt

 

02-Dec-20 10:25

AirwaySlotOutDateTimeUtc

 

02-Dec-20 09:25

Already paid

 

1154.73

Already paid in booked currency

 

1154.73

Amount due

 

0

Amount due after payment

 

0

Amount due after payment currency

 

EUR

Amount due in booked currency

 

0

Amount in charged currency

 

1154.73

Amount in default currency

 

1154.73

ApplicableAocDate

 

20-Nov-99

ApplicableAocNumber

 

RO-042

Arrival is pending

 

FALSE

AtaDateTime

 

29-Nov-20 20:59

AtaDateTimeLt

 

29-Nov-20 20:59

AtdDateTime

 

02-Dec-20 09:24

AtdDateTimeLt

 

02-Dec-20 10:24

Baggage pieces in

 

10

Baggage pieces out

 

10

Baggage weight in

 

100

Baggage weight out

 

100

BatchSettledLt

 

01-Jan-20

BatchSettledUtc

 

01-Jan-20

Billing Remark

 

 

Booked currency code

 

EUR

Charged currency

 

USD

Created date time LT

 

01-Dec-20 01:35

Created date time UTC

 

01-Dec-20 00:35

Crew count in

 

2

Crew count out

 

1

Currency code

 

EUR

Debtor Address 1

 

 

Debtor Address 2

 

 

Debtor AOC expire date

 

31-Dec-29

Debtor AOC number

 

D-326

Debtor carrier code

 

BAW

Debtor City

 

Amsterdam

Debtor Country

 

Netherlands (NL)

Debtor Country for VAT

 

Netherlands (NL)

Debtor Email 1

 

support@amsterdamsoftware.com

Debtor Email 2

 

 

Debtor Fax

 

 

Debtor Headquarter Name

 

 

Debtor Headquarter Number

 

 

Debtor ID

 

c8d02bb2-3760-4d0f-ad5e-b2fa3553798d

Debtor Order No

 

 

Debtor Phone 1

 

+31 (0)20 620 5697

Debtor shortest name

 

AMSOFT

Debtor State

 

 

Debtor to charge contact number

 

 

Debtor to charge name

 

 

Debtor to charge short name

 

 

Debtor VAT number

 

 

Debtor Website URL

 

https://www.amsterdamsoftware.com/

Debtor Zip code

 

 

DebtorAdministrationContactNo

 

 

DebtorContactContactNo

 

100000

DebtorContactNumber

 

100000

DebtorCredit

 

1

DebtorName

 

Amsterdam Software

Debtors order number

 

 

DebtorShortName

 

AMSOFT

Default currency

 

EUR

Departure is pending

 

FALSE

EndDateTime

 

 

Entry

 

 

EntryPeriod

 

 

EtaAtDestinationLt

 

 

EtaAtDestinationUtc

 

 

EtaDateTime

 

 

EtdDateTime

 

 

External POS receipt number

 

1700

External remarks

 

 

FBO location ID

 

22008ff4-5aeb-48c2-98a3-1f0a0c790e20

FBO location short name

 

AMS

Fbolocation short name

 

AMS

FbolocationID

 

22008ff4-5aeb-48c2-98a3-1f0a0c790e20

FinalOffBlocksDateTimeLt

 

 

FinalOffBlocksDateTimeUtc

 

 

Flight number if not registration in

 

 

Flight number if not registration out

 

 

Flight number in

 

 

Flight number out

 

 

Flight rules in

 

 

Flight rules out

 

 

Flight type in

 

Private

Flight type out

 

Private

FOP

 

CASH EUR

From Station City

 

Raleigh-durham

From Station Country

 

United States

From Station Name

 

Raleigh Durham Intl

From Station Name and Country

 

Raleigh Durham Intl Raleigh-durham United States

FromIata

 

RDU

FromIcao

 

KRDU

FromName

 

Raleigh Durham Intl

Front office workflow name

 

HandlingOrder

Grand total in default currency

 

0

Handling country code

 

NL

Handling country is EU

 

TRUE

Handling country name

 

Netherlands

Handling order ID

 

acae293f-763f-40f9-a4dd-f830b09eeb94

Handling Station City

 

Amsterdam

Handling Station Country

 

Netherlands

Handling Station Displayname

 

EHAM/AMS (Amsterdam Schiphol)

Handling station ID

 

7294a082-de15-4e56-909d-a5435c5ebb41

Handling Station Name and Country

 

Amsterdam Schiphol Netherlands

HandlingCategory

 

0-10T

HandlingOfficeShortName

 

AMS

HandlingStationIataCode

 

AMS

HandlingStationIcaoCode

 

EHAM

HandlingStationName

 

Amsterdam Schiphol

Inbound flight number

 

 

InboundOriginSystemId

 

 

InboundOriginSystemIdLastSeenAtUtc

 

 

InboundOriginSystemLastRow

 

 

InboundOriginSystemNotes

 

 

InitialOnBlocksDateTimeLt

 

 

InitialOnBlocksDateTimeUtc

 

 

Internal remarks

 

 

Invoice

 

18000008

Invoice state

 

Booked

InvoiceDate

 

10-Dec-19

InvoiceInternalRemarks

 

 

Is credit

 

FALSE

Is Domestic In

 

INT

Is Domestic Out

 

INT

Is GenDec required In

 

TRUE

Is GenDec required Out

 

TRUE

Is purchase

 

FALSE

Is Visible For OPS

 

FALSE

Length in meter

 

16.9

MostActualArrivalDateTime

 

28-Nov-20 00:15

MostActualArrivalDateTimeLT

 

28-Nov-20 01:15

MostActualDepartureDateTime

 

28-Nov-20 23:30

MostActualDepartureDateTimeLT

 

29-Nov-20 00:30

Movement Message Contacts in

 

 

Movement Message Contacts out

 

 

MTOW in US Pounds

 

20999

MTOW kg

 

9525

MTOW ton

 

10

NET amount

 

0

NET amount in booked currency

 

0

Non-transit pax count in

 

 

Non-transit pax count out

 

 

Number of seats

 

 

OnlineAuthCode

 

4239515

OnlineAuthorizationRequest

 

<?xml version="1.0" encoding="utf-8"?>
<AHT-Authorization DocVersion="2">
  <Transaction>
    <LineItems>
      <LineItem>
        <Product UOM="Each">Airport Fees</Product>
        <ProductCode>AirportFee</ProductCode>
        <Quantity>1.000</Quantity>
        <UnitPrice>537.51000</UnitPrice>
        <LocalTax TaxRateType="PC" Name="ValueAddedTax">102.13</LocalTax>
        <LineItemTotal>537.51</LineItemTotal>
      </LineItem>
    </LineItems>
    <Currency>EUR</Currency>
    <CardData EntryMode="KEY" TrxType="Sale">49762712409195294832</CardData>
    <TotalAmount>639.64</TotalAmount>
    <TailNumber>N12345</TailNumber>
    <Ticket>18-MUC</Ticket>
    <CFDeliveryDate>11/29/2019</CFDeliveryDate>
    <CFDeliveryTime>00:30</CFDeliveryTime>
    <MinutesAgo>0</MinutesAgo>
  </Transaction>
</AHT-Authorization>

OnlineAuthorizationResponse

 

<AuthResponse Approved='True' TextResponse='APPROVED'><Invoice>000008</Invoice><Batch>000001</Batch><AuthCode>4239515</AuthCode><ProcessorRequestID></ProcessorRequestID><AVSResultCode>0</AVSResultCode></AuthResponse>

OnlineBatchNumber

 

000001

OnlineCardAlias

 

AHT Avcard, 4832

OnlineInvoiceID

 

000008

OnlineInvoiceResult

 

AHT status: AuthorizationApproved

OnlinePostAuthorizationCode

 

 

OnlineRemarks

 

 

Operator Address 1

 

 

Operator Address 2

 

 

Operator AOC expire date

 

 

Operator AOC number

 

 

Operator carrier code

 

 

Operator City

 

 

Operator Country

 

Netherlands (NL)

Operator Country for VAT

 

 

Operator Email 1

 

support@amsterdamsoftware.com

Operator Email 2

 

 

Operator Fax

 

 

Operator Headquarter Name

 

 

Operator Headquarter Number

 

 

Operator ID

 

c8d02bb2-3760-4d0f-ad5e-b2fa3553798d

Operator Phone 1

 

+31 (0)20 620 5697

Operator shortest name

 

AMSOFT

Operator State

 

 

Operator VAT number

 

 

Operator Website URL

 

https://www.amsterdamsoftware.com/

Operator Zip code

 

 

OperatorAdministrationContactNo

 

 

OperatorContactContactNo

 

100000

OperatorName

 

Amsterdam Software

Operators handling order number

 

 

OperatorShortName

 

AMSOFT

Order ID

 

e0013fb6-9d2d-4c7e-9d5b-f493e64f7813

OrderKey

 

AMS-150

OrderState

 

Cancelled

Outbound flight number

 

 

OutboundOriginSystemId

 

 

OutboundOriginSystemIdLastSeenAtUtc

 

 

OutboundOriginSystemLastRow

 

 

OutboundOriginSystemNotes

 

 

Parking position

 

 

Pax count in

 

 

Pax count out

 

 

Pax crew count in

 

 

Pax crew count out

 

 

PAX in

 

 

Pax names in

 

 

Pax names out

 

 

PAX out

 

 

Payment FOP

 

AHT Avcard

Payment key

 

18-MUC

Payment number

 

18

Payment remarks

 

 

Payment state

 

Voided

Payment Timestamp LT

 

01-Dec-20 12:50

Payment Timestamp Utc

 

01-Dec-20 11:50

PaymentID

 

9855d8e3-7e6f-4833-be31-127ef7e1300d

Pilot names in

 

 

Pilot names out

 

 

Plan board messages full

 

 

Plan board messages short

 

 

PriceCalculationDateTime

 

29-Nov-20 00:30

Primary order number

 

150

Processed by employee contact

 

Adam Marriott

Processed by employee contact ID

 

ebd4e2a4-9176-4f9c-9205-ef7ec0b803ad

Quick turn

 

FALSE

ReceiptNumber

 

1700

Registration and flight number if not registration in

 

N12345

Registration and flight number if not registration out

 

N12345

RegistrationCode

 

N12345

Second In Command names in

 

 

Second In Command names out

 

 

Secondary order number

 

 

SeperatorCol

 

 

Source Payment Id

 

 

Source Payment Key

 

 

SplitPaymentAmount

 

0

SplitPaymentKey

 

 

StaDateTime

 

28-Nov-20 00:15

Stage name

 

Back office

Stage numeric value

 

2

StartDateTime

 

 

StdDateTime

 

28-Nov-20 23:30

To Station City

 

Raleigh-durham

To Station Country

 

United States

To Station Name

 

Raleigh Durham Intl

To Station Name and Country

 

Raleigh Durham Intl Raleigh-durham United States

ToIata

 

RDU

ToIcao

 

KRDU

ToName

 

Raleigh Durham Intl

Total amount

 

0

Total amount in booked currency

 

0

Transit pax count in

 

 

Transit pax count out

 

 

Trip Number

 

 

Trip Support Headquarter Name

 

 

Trip Support Headquarter Number

 

 

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 ID

 

 

Trip Support Provider name

 

 

Trip Support Provider Phone 1

 

 

Trip Support Provider shortest name

 

 

Trip Support Provider State

 

 

Trip Support Provider Website URL

 

 

Trip Support Provider Zip code

 

 

VAT amount

 

0

VAT amount in booked currency

 

0

Wingspan in meter

 

14.6