Receipt line query

The data provider can be used for building a receipt/invoice or even single page invoice RDL based template.

Contents:

Filters

This query can return each receipt line for multiple orders. For a single order, filter by Order with OrderID.

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

NO

Take Comms One Services

Filter to show Comms One services.

NO

  • 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

  • Supplier invoice date on or after

  • Supplier invoice date before

Filter between Supplier invoice on or after date and before date, use both on or after and before to create a data range.

NO

  • Supplier invoice creation date (UTC) on or after / Supplier invoice creation date (UTC) before: 

  • Supplier invoice creation date (LT) on or after / Supplier invoice creation date (LT) before

Filter between Supplier invoice on or after creation date and before creation date, use both on or after and before to create a data range.

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

Supplier

Filter by orderline supplier.

NO

Aircraft registration

Filter by an order’s aircraft registration.

NO

Product

Filter by product code.

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

Supplier invoice

Filter by supplier invoice number.

NO

Is expanded

Indicates if the report should show rows that are collapsed to the customer.

NO

Receipt audience

Indicates the receipt audience.

  • Operators Copy = Prices are hidden based upon the Contract terms

  • Invoice attached receipt = Price are shown.

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.

Setting

Type

Default

Description

Setting

Type

Default

Description

ReceiptAudience

String

OperatorsCopy

Indicates the receipt audience. Possible values

  • OperatorsCopy - Prices are hidden based upon the Contract terms

  • InvoiceAttachedReceipt - Price are shown.

ExpandCollapsedGroups 

Boolean

FALSE

This setting allows to display detailed child order line services and prices.

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.

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.

Columns

Column

Description

Example

Column

Description

Example

AccountsReceivableAddressLine1

Accounts receivable address line 1’ defined in Administrations (Administration), see Administration (Fields).

Thermiekstraat 24

AccountsReceivableAddressLine2

Accounts receivable address line 2’ defined in Administrations (Administration), see Administration (Fields).

 Schiphol Airport

AccountsReceivableCity

Accounts receivable city’ defined in Administrations (Administration), see Administration (Fields).

Amsterdam

AccountsReceivableCountry

Accounts receivable country’ defined in Administrations (Administration), see Administration (Fields).

Netherlands

AccountsReceivableName

Accounts receivable name’ defined in Administrations (Administration), see Administration (Fields).

FBO One

AccountsReceivableVatNo

Accounts receivable vatno’ defined in Administrations (Administration), see Administration (Fields).

NL 1234 5678

AccountsReceivableZipCode

AccountsReceivableZipCode’ defined in Administrations (Administration), see Administration (Fields).

1117 BG

Aircraft type

 

Cessna Citation XLS - C56X (CITATION XLS)

Amount decimal

 

 

Amount formatted

 

 

AOC expiry date

 

-

AOC number

 

 

AOC number and expiry date

 

-

Arrival datetime

 

01-Jan-18 16:00

Arrival datetime LT

 

01-Jan-18 16:00

Arrival datetime UTC

 

01-Jan-18 16:00

Arrival Or Departure

 

A

Billing remark

 

 

Captains name

 

 

Charged currency code

 

EUR

Debtor address

 

Oscar Avia 13
67a Victoria Road
RH6 7QH Horley, West Sussex
United Kingdom

Debtor address 1

 

67a Victoria Road

Debtor address 2

 

 

Debtor city

 

Horley

Debtor contact number

 

RCA012

Debtor country

 

United Kingdom

Debtor name

 

Oscar Avia 13

Debtor order number

 

 

Debtor state

 

West Sussex

Debtor VAT number

 

GB976922467

Debtor zip code

 

RH6 7QH

DebtorName

 

Oscar Avia 13

Density

 

0

Density formatted

 

 

Departure datetime

 

 

Departure datetime LT

 

 

Departure datetime UTC

 

 

Form of payment for amount due

 

BILL EUR

Form of payment for amount due caption

 

Form of payment for balance due

Grand total decimal

 

522

Grand total excl VAT decimal

 

522

Grand total excl VAT formatted

 

522.00

Grand total formatted

 

522.00

Grand total in words

 

 

Grand total in words caption

 

Order amount in words:

Grand total VAT decimal

 

0

Grand total VAT formatted

 

0.00

GrandTotalInBookedCurrencyDecimal

 

522

GrandTotalInBookedCurrencyFormatted

 

522.00

GrandTotalVATInBookedCurrencyDecimal

 

0

GrandTotalVATInBookedCurrencyFormatted

 

0.00

Handling ID

 

ce3d9e57-1ba0-4e14-9d14-a3c0ca1978a3

Handling location

 

EGKB/BQH (Biggin Hill)

Handling office name

 

FBO One Airport

Has unit and quantity

 

TRUE

Inbound flight leg description

 

Arrival from GMMX on 1-Jan-18 16:00 (UTC), 2 (? transit) pax

InboundOriginSystemId

 

 

InboundOriginSystemIdLastSeenAtUtc

 

 

InboundOriginSystemLastRow

 

 

InboundOriginSystemNotes

 

 

Indent level

 

0

Invoice date

 

 

Invoice ID

The Invoice unique ID in GUID format.

b9bfbc6c-269d-4dcb-877b-7f76f90eb138

Invoice number

The invoice number.

1014966

Invoice number and date

 

 

Invoice title

Invoice title’ defined in Administrations (Administration), see Administration (Fields).

 

InvoiceAmountDueDecimal

 

 

InvoiceAmountDueFormatted

 

 

InvoiceChargedCurrencyCode

 

 

InvoiceCreatedDateTimeLt

 

01-Jan-01

InvoiceCreatedDateTimeUtc

 

01-Jan-01

InvoiceFormOfPaymentForAmountDue

 

 

InvoiceInternalRemarks

 

 

Line item description

 

HANDLING FEES GROUP | Handling Fees

Logo image

 

 

MTOW

 

9163 kg

Net quantity

 

0

Net quantity formatted

 

 

Operator

 

Oscar Avia 13 (RCA012)

OperatorAddressExclName

 

67a Victoria Road
RH6 7QH Horley, West Sussex
United Kingdom

OperatorNameExclContactNumber

 

Oscar Avia 13

Order date

 

30-Oct-17

Order ID

 

84aea549-0d04-4ea4-97a0-18e18976ff6a

Order ID

The Order unique ID in GUID format.

af710d11-6159-4b34-8caf-88b735ed31be

Order key

The Order key contained in the invoice.

FBO-60066

Order line ID

 

43422cd0-263c-4360-bd27-8780aa298a87

Order state

 

Cancelled

Outbound flight leg description

 

Departure to - on -, 0 pax

OutboundOriginSystemId

 

 

OutboundOriginSystemIdLastSeenAtUtc

 

 

OutboundOriginSystemLastRow

 

 

OutboundOriginSystemNotes

 

 

Payment term days

 

30

Payment term description

 

30 days (31-Jan-18)

Payment term due date

 

31-Jan-18

Product code

 

HANDLING FEES GROUP

Product ID

 

f9dd8fe9-7722-416c-9f9d-98d4bf1b5393

QRCode

 

 

Quantity decimal

 

1

Quantity formatted

 

 

Receipt footer

 

VAT Registration No: GB 123 4567 89. FBO One is a subsidary of Regional Airports Ltd.
Registered Office: FBO One, Amsterdam

Receipt header

 

VAT Registration Number: GB 123 4567 89 | Business Registration Number: 12345678

Receipt remarks

 

-

Please direct enquiries to: Handling@fbo.one or Accounts@fbo.one

MONEY TRANSFERS TO:
De Nederlandsche Bank
Account No: 11111111 | Sort Code 12-34-56
Swift: NL1234
IBAN Code: GB12 BARC 2345 6789 0123 45

Receipt title

 

RECEIPT *TEST*

Registration code

 

DAWXL

Registration code and type name

 

DAWXL | Cessna Citation XLS - C56X (CITATION XLS)

Service date formatted

 

 

Service datetime LT

 

01-Jan-18 16:00

Show as headline

 

TRUE

Show price to audience

 

TRUE

Temperature

 

0

Temperature formatted

 

 

Trip number

 

 

Unit price decimal

 

 

Unit price formatted

 

 

VAT amount decimal

 

 

VAT amount formatted

 

 

VAT code percentage formatted

 

 

Weight

 

0

Weight formatted

 

 

 

See also

Receipt VAT subtotal line query

Receipt payment line query

Create custom receipt layout using RDL

Invoice queries

Create custom invoice and receipt layouts using RDL