Jump to content

ET Customer Invoice Details

From PBI Reporting Wiki

Summary

This report was developed as a custom request from the Brazil-based Operations team to enable them to quickly generate an invoice report for sharing with carrier ET.

It forms part of an ongoing effort to streamline their workflow and support fast, efficient invoice generation and communication with the carrier.

Filters

Filter Description
Site This is locked to the Brazil site
Carrier This is locked to ET Brazil
Customer Allows the user to specifically select the customer they want to view the booking details for. Can only select a single customer, or show all.
Date Range The date range is linked to the depart date.

Data Transformation Applied

Query Transformations
quotations
  • Changed Type: created_at, depart_date to date
  • Merged Queries: Joined with quotation_carriers on id, main_carrier_id and quotation_id, carrier_id
  • Expanded Columns: carrier_id, Carrier, Site
  • Filtered Rows: Kept only rows where quotation_carriers.carrier_id = 250
hae_booking_db carriers
  • Merged Queries: Joined with sites on site_id and id
  • Expanded Column: name from sites
  • Renamed Column: hae_booking_db sites.name to Site
quotation_totals_local
  • Renamed Columns: total_cost → ET Refund (COST) total_sell → HAE Invoice Customer Total (Sales BRL)
  • Added Custom Column: HAE Over = total_sell_rate - total_cost_rate
quotation_charges
  • Changed Type: created_at to date
  • Duplicated Column: sell_rate → AWB Rate
  • Renamed Column: currency → Due Carrier Currency
  • Duplicated Column Again: Due Carrier Currency → HAE Commission Currency (USD)
quotation_routes
  • Merged Queries: Joined with quotation_charges on id and quotation_route_id
  • Expanded Columns: cost_rate, sell_rate, currency, currency_sell
  • Renamed Columns: cost_rate → Buying Rate sell_rate → Selling Rate currency → Buying Currency currency_sell → Sell Rate Currency
  • Changed Type: created_at to date
quotation_fields (Mode of Payment)
  • Renamed Column: field_value → Mode of Payment
  • Changed Type: created_at to date
quotation_fields (AWB Issue Date)
  • Renamed Column: field_value → AWB Issue Date
  • Added Custom Column: AWB Issue Date Final = if AWB Issue Date = "" then "NODATA" else AWB Issue Date
quotation_fields (Due Agent)
  • Renamed Column: field_value → Due Agent
  • Changed Type: created_at to date
quotation_exchange_rates


  • Changed Type: created_at to date
  • Grouped Rows: by quotation_id, retained max of created_at
  • Merged Queries: Joined with quotation_exchange_Original on quotation_id and created_at
  • Expanded Columns: exchange_rate, is_fixed
  • Renamed Columns: quotation_exchange_Original.exchange_rate → exchange_rate quotation_exchange_Original.is_fixed → is_fixed
quotation_exchange_Original Changed Type: created_at to date
quotation_chargesHAE_TAX
  • Filtered Rows: Initial filter kept all, then second filter kept rows where HAE Tax (charge currency) = USD
  • Renamed Columns: sell_min → HAE Tax USD currency_sell → HAE Tax (charge currency)
quotation_route_partsOriginal
  • Merged Queries: Joined with quotation_routes_flight_number on quotation_route_id
  • Expanded Column: quotation_id
  • Renamed Column: quotation_routes_flight_number.quotation_id → quotation_id
quotation_flight_number
  • Merged Queries: Joined with quotation_routes_flight_number on quotation_route_id
  • Expanded Column: quotation_id
  • Grouped Rows: by quotation_id, selected Min(id)
  • Merged Queries: Joined with quotation_route_partsOriginal
  • Expanded Columns: flight_number, carrier_code
  • Renamed Columns: quotation_route_partsOriginal.flight_number → flight_number quotation_route_partsOriginal.carrier_code → carrier_code
QuotationCombinedTableTotalRevenue To create a table that combines quotation data with revenue-related charge details. This helps in calculating Total Revenue.

How it works:

  • It joins the quotations table with the quotations_total_revenue table using matching quotation IDs.
  • From the joined data, it selects only these columns:
    • ID – the quotation ID
    • Chargeable Weight – used in revenue calculation
    • Code – type of charge (e.g., LINEHAUL, DTC)
    • Sell Rate – rate per weight unit
    • Sell Min – minimum selling value

This table is then used in the revenue formula that applies different logic depending on the charge code.

QuotationCombinedTableDueCarrierUSD Purpose:

To create a clean table for calculating Due Carrier USD, excluding rows where the charge code is LINEHAUL.

Steps:

  • Use three tables: quotations, quotation_chargesDueCarrierUSD (a filtered version of quotation_charges), and quotation_exchange_rates.
  • Join quotations with quotation_chargesDueCarrierUSD using a natural inner join.
  • Then join the result with quotation_exchange_rates using another natural inner join.
  • Select the following columns:
    • ID from quotations
    • Chargeable Weight from quotations
    • Cost Rate, Code, Cost Min, On from quotation_chargesDueCarrierUSD
    • Exchange Rate from quotation_exchange_rates
  • Use DISTINCT to remove any duplicate rows.
QuotationCombinedTableDueCarrierTotal To create a unified table that includes all carrier charges (including LINEHAUL) for calculating Total Due Carrier USD values.

Steps:

  • Start with the quotations, quotation_chargesDueCarrierTotal, and quotation_exchange_rates tables.
  • Perform a natural inner join between quotations and quotation_chargesDueCarrierTotal.
  • Then perform another natural inner join with quotation_exchange_rates.
  • Use SELECTCOLUMNS to extract the following fields:
    • ID from quotations
    • Chargeable Weight from quotations
    • Cost Rate, Code, Cost Min, On from quotation_chargesDueCarrierTotal
    • Exchange Rate from quotation_exchange_rates

Calculations

Columns Calculation Description
id id — sourced directly from the quotations table. The id is a unique identifier for each record and is retrieved directly from the quotations table.
master_airwaybill master_airwaybill — sourced directly from the quotations table. The master_airwaybill field contains the main airway bill number for the shipment. It is pulled directly from the quotations table for reference and tracking.
Customer Customer Name — pulled from the customer table via customer_id and linked to the quotations table. Join the customer table to the quotations table using customer_id as the key.

From the joined customer record, retrieve the name field to identify the customer associated with the quotation.

Flight_Number flight_number — retrieved from hae_route_parts, filtered to the minimum quotation_route_id per quotation_id, and joined back to quotations via quotation_id Retrieve the flight_number associated with each quotation by linking route data. This is done by identifying the first (minimum) quotation_route_id per quotation_id, then using it to pull the corresponding flight_number from the route details.
Depart Date depart_date — sourced directly from the quotations table. The depart_date is obtained directly from the quotations table.
AWB Issue Date AWB Issue Date = if [AWB Issue Date] = null then "NO DATA" else [AWB Issue Date] Retrieve the AWB Issue Date from the quotation_fields table where carrier_quotation_field_id equals 200, which corresponds to the AWB Issue Date field.

Then, join this data to the quotations table using quotation_id.

Finally, check if the AWB Issue Date is null; if so, return "NO DATA". Otherwise, return the AWB Issue Date.

Exchange_rate exchange_rate — retrieved from the exchange_rate table as the most recent entry (by created_at) for each quotation_id. Join the exchange_rate table with the quotations table using the quotation_id key.

From the exchange_rate table, retrieve the exchange_rate field that has the most recent created_at timestamp per quotation_id.

This ensures that each quotation is matched with the latest available exchange rate record

Fixed_exRate Fixed_exRate =

IF(

    quotation_exchange_rates[is_fixed] = TRUE(),

    "✓",

    "×"

)

Join the exchange_rate table with the quotations table using the quotation_id key.

From the exchange_rate table, retrieve the is_fixed field that with the most recent created_at per quotation_id

which indicates whether the exchange rate is fixed.

Then evaluate the value:

  • If is_fixed is TRUE, return "✓"
  • If is_fixed is FALSE, return "×"
origin origin — sourced directly from the quotations table. It is retrieved directly from the quotations table.
Destination destination — sourced directly from the quotations table. It is retrieved directly from the quotations table.
gross_weight gross_weight — sourced directly from the quotations table. It is retrieved directly from the quotations table.
chargeable_weight chargeable_weight — sourced directly from the quotations table. It is retrieved directly from the quotations table.
Buying Rate Buying Rate = quotation_routes[cost_rate] Join the quotation_routes table (filtered where carrier_id = 12) with the quotation_charges table, filtered where code = "LINEHAUL".

From this joined data, extract the cost_rate field and rename it as Buying Rate.

Buying Rate Total Buying Rate Total =

quotation_routes[Buying Rate] × RELATED(quotations[chargeable_weight])

Join the routes table (filtered to carrier_id = 12) with the quotation_charges table, where the Airfreight rate is LINEHAUL.

From this join, extract the cost_rate field.

Next, join this data with the quotations table to access the chargeable_weight.

Then, calculate the Buying Rate Total by multiplying the LINEHAUL cost_rate by the chargeable_weight.

Buying Currency Buying Currency = currency from quotation_charges, joined via quotation_id. Join the quotation_charges table with the quotations table using the quotation_id key.

From the quotation_charges table, retrieve the currency field.

AWB Rate AWB Rate = sell_rate from quotation_charges where code = 'LINEHAUL', joined via quotation_id. Join the quotation_charges table with the quotations table using the quotation_id key.

From the quotation_charges table, filter to only include the charge where code = 'LINEHAUL'.

Retrieve the sell_rate field for this charge, which represents the AWB Rate.

Due Agent Due Agent = value from quotation_fields (field ID 204), joined to quotations via quotation_id Retrieve the Due Agent value from the quotation_fields table by filtering for carrier_quotation_field_id = 204, which corresponds to the Due Agent field.

Then, join this data with the quotations table using the quotation_id.

HAE Over HAE Over = [Total Sell Rate] - [Total Cost Rate] Retrieve sell_rate and cost_rate from the hae_quotations_local table as Total Sell Rate and Total Cost Rate, respectively.

Then, join this data with the quotations table using the id field.

Finally, calculate the HAE Over by subtracting the Total Cost Rate from the Total Sell Rate.

HAE Tax USD HAE Tax USD = sell_min from quotation_charges where code = 'DTC', linked to quotations by quotation_id. Retrieve sell_min from the quotation_charges table where the code = 'DTC'.

Join this table with the quotations table using quotation_id to match the correct quotation. This value represents the HAE Tax USD, as the charge is stored in USD by default.

HAE Tax (charge currency) HAE Tax (charge currency) = currency_sell from quotation_charges, joined via quotation_id. Join the quotation_charges table with the quotations table using the quotation_id key.

From the quotation_charges table, retrieve the currency_sell field, which indicates the currency of the HAE Tax.

HAE Tax BRL HAE Tax BRL = [HAE Tax USD] * [Exchange_rate] convert the HAE Tax USD amount to BRL by multiplying it with the exchange_rate.
Due Carrier (USD) Due Carrier USD =

SWITCH(

 TRUE(),

 QuotationCombinedTableDueCarrierUSD[on] = "CH", QuotationCombinedTableDueCarrierUSD[cost_rate] * QuotationCombinedTableDueCarrierUSD[chargeable_weight],

 QuotationCombinedTableDueCarrierUSD[on] = "FL", QuotationCombinedTableDueCarrierUSD[cost_min],

 0

)

Grab quotation_charges table and rename it to quotation_chargesDueCarrierUSD.

From this duplicated table, exclude all rows where code = 'LINEHAUL', keeping only relevant carrier charges.

Then, join quotation_chargesDueCarrierUSD with the quotations table using quotation_id to create QuotationCombinedTableDueCarrierUSD.

Also, join the quotation_exchange_rates table to bring in the most recent exchange_rate per quotation.

From QuotationCombinedTableDueCarrierUSD, retrieve the following fields:

  • chargeable_weight from quotations
  • cost_rate, code, cost_min, and on from quotation_chargesDueCarrierUSD
  • exchange_rate from quotation_exchange_rates

Finally, calculate the Due Carrier USD using the logic based on the on field:

  • If on = "CH", multiply cost rate by chargeable weight
  • If on = "FL", use cost_min
  • Otherwise, return 0
Due Carrier Total BRL Total Due Carrier BRL =

QuotationCombinedTableDueCarrierTotal[Due Carrier Total] × QuotationCombinedTableDueCarrierTotal[Exchange Rate]

After calculating Due Carrier Total (which includes all carrier charges, including LINEHAUL), convert the amount into BRL by multiplying it with the relevant exchange rate.

Ensure that QuotationCombinedTableDueCarrierTotal includes both the Due Carrier Total and the correct Exchange Rate, retrieved as the most recent value per quotation_id from the quotation_exchange_rates table.

Due Carrier (BRL) Due Carrier BRL =

QuotationCombinedTableDueCarrierUSD[Due Carrier USD] * QuotationCombinedTableDueCarrierUSD[Exchange Rate]

After computing Due Carrier USD, convert the value into BRL by applying the corresponding exchange rate.

Ensure that QuotationCombinedTableDueCarrierUSD already contains the Due Carrier USD and the applicable Exchange Rate (retrieved from the most recent entry in the quotation_exchange_rates table, joined by quotation_id).

Total Revenue Total Revenue =

CALCULATE (

 SUMX (

  QuotationCombinedTableTotalRevenue,

  SWITCH (

   TRUE(),

   [Code] = "LINEHAUL", [Sell Rate] * [Chargeable Weight],

   [Code] = "DTC", [Sell Min],

   0

  )

 )

)

Grab the quotation charges table, rename it to:

quotation_charges_Total Revenue. This version includes all relevant charge codes for calculating total revenue—specifically LINEHAUL and DTC.

Next, perform a NATURALINNERJOIN between the quotations table and quotation_charges_Total Revenue to create QuotationCombinedTableTotalRevenue. From this joined table, retrieve the following fields:

  • quotation_id as ID
  • chargeable_weight from quotations
  • code, sell_rate, and sell_min from quotation_charges_Total Revenue

Finally, compute Total Revenue using a conditional logic:

  • If code = "LINEHAUL", multiply sell_rate by chargeable_weight
  • If code = "DTC", use sell_min
  • Otherwise, return 0
Total Revenue Currency Total Revenue Currency =

quotation_charges[currency_sell]

Join the quotation_charges table with the quotations table using the quotation_id key.

From the quotation_charges table, retrieve the currency_sell field, which indicates the currency in which the revenue is quoted.

HAE Invoice Customer Total (Sales BRL) Join the quotation_totals_local table with the quotations table using the quotation_id key.

From the quotation_totals_local table, retrieve the total_sell field.

HAE Invoice Customer Total (Sales) = total_sell from quotation_totals_local, joined via quotation_id.
HAE Commision (Margin) BRL HAE Margin BRL =

if ([Exchange_rate] = null or [Exchange_rate] = 0) then null

else [HAE Commission] * [Exchange_rate]

After retrieving the HAE Commission and the applicable Exchange_rate (ensuring it's the most recent per quotation_id), calculate the HAE Margin in BRL by converting the commission using the exchange rate.

If the Exchange_rate is null or zero, return null to avoid invalid conversion.

HAE Margin BRL HAE Margin BRL =

if ([Exchange_rate] = null or [Exchange_rate] = 0) then null

else [HAE Commission] * [Exchange_rate]

This formula calculates the HAE Margin amount in BRL by multiplying [HAE Commission 2] by the [Exchange_rate].

If the [Exchange_rate] is null or zero, the formula returns null to prevent an invalid calculation.

Otherwise, it performs the conversion from the commission value to BRL.

ET Refund (Cost BRL) ET Refund (COST) = total_cost from quotation_totals_local, joined via quotation_id. Join the quotation_totals_local table with the quotations table using the quotation_id key.

From the joined data, retrieve the total_cost field.

This value represents the ET Refund (COST) associated with the quotation.

Selling Rate Sell Rate = sell rate from quotation_charges, joined via quotation_id. Join the quotation_charges table with the quotations table using the quotation_id key.

From the quotation_charges table, retrieve the sell_rate field.

Selling Rate Currency Sell Rate Currency = currency sell from quotation_charges, joined via quotation_id Join the quotation_charges table with the quotations table using the quotation_id key.

From the quotation_charges table, retrieve the currency sell field.

Mode Of Payment Mode of Payment = field_value from quotation_fields where carrier_quotation_field_id = 199, joined to quotations via quotation_id. Retrieve the Mode of Payment from the quotation_fields table by filtering records where carrier_quotation_field_id = 199, which corresponds to this customer field.

Join the quotation_fields table with the quotations table using quotation_id to ensure the value is linked to the correct quotation.

Only values associated with carrier_quotation_field_id = 199 are displayed.

Due Carrier Total Due Carrier Total =

SWITCH(

 TRUE(),

 QuotationCombinedTableDueCarrierTotal[on] = "CH", QuotationCombinedTableDueCarrierTotal[cost_rate] * QuotationCombinedTableDueCarrierTotal[chargeable_weight],

 QuotationCombinedTableDueCarrierTotal[on] = "FL", QuotationCombinedTableDueCarrierTotal[cost_min],

 0

)

Grab the quotation_charges table and renamed it to quotation_chargesDueCarrierTotal.

From this duplicated table, include all rows, including those where code = 'LINEHAUL', to capture the full set of carrier charges.

Then, join quotation_chargesDueCarrierTotal with the quotations table using quotation_id to create QuotationCombinedTableDueCarrierTotal.

Also, join the quotation_exchange_rates table to bring in the most recent exchange_rate per quotation.

From QuotationCombinedTableDueCarrierTotal, retrieve the following fields:

  • chargeable_weight from quotations
  • cost_rate, code, cost_min, and on from quotation_chargesDueCarrierTotal
  • exchange_rate from quotation_exchange_rates

Finally, calculate the Due Carrier Total using the logic based on the on field:

  • If on = "CH", multiply cost_rate by chargeable weight
  • If on = "FL", use cost_min
  • Otherwise, return 0
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=ET_Customer_Invoice_Details&oldid=244"