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.

Data Transformation Applied

[WIP]

Query Calculations Description

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(hae_quotations[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 Total Buying Rate Total = [hae__routes.LINEHAUL.cost_rate] * [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 = if [Exchange_rate] = null or [Exchange_rate] = 0 then "NOEXR"

else [HAE Tax USD] * [Exchange_rate]

convert the HAE Tax USD amount to BRL by multiplying it with the exchange_rate.

If the exchange_rate is null or zero, return "NOEXR" to indicate a missing or invalid rate.

Due Carrier (USD)
Due Carrier Total Due Carrier Total = if [LINEHAUL.on] = "CH" then [LINEHAUL.cost_rate] * [chargeable_weight]

else if [LINEHAUL.on] = "FL" then [LINEHAUL.cost_min]

else 0

First, join the routes table (filtered to carrier_id = 12) with the quotation_charges table, selecting only the charge with Airfreight rate = LINEHAUL.

From this join, retrieve the fields: on, cost_rate, and cost_min.

Then, join this data to the quotations table to access the chargeable_weight field.

Finally, calculate the Due Carrier Total as follows:

  • If on = "CH", multiply cost_rate by chargeable_weight
  • If on = "FL", return cost_min
  • Otherwise, return 0
Due Carrier (BRL) Due Carrier (BRL) = if [Exchange_rate] = null or [Exchange_rate] = 0 then "NOEXR" else [Due Carrier (USD)] * [Exchange_rate] This formula calculates the Due Carrier amount in BRL by multiplying [Due Carrier (USD)] by the [Exchange_rate].

If the [Exchange_rate] is null or zero, the formula returns the text "NOEXR" to indicate that no valid exchange rate exists.

Otherwise, it performs the conversion from USD to BRL.

Total Revenue Total Revenue = [LINEHAUL.sell_rate] * [chargeable_weight] + [DTC.sell_min] First, filter the quotation_charges table to get two specific charge types:
  • LINEHAUL, from which you retrieve the sell_rate
  • DTC, from which you retrieve the sell_min

Then, access the chargeable_weight from the quotations table.

Finally, calculate the Total Revenue by multiplying the LINEHAUL sell_rate with the chargeable_weight, and then adding the DTC sell_min.

Total Revenue Currency Total Revenue Currency =

CALCULATE(

 SELECTEDVALUE (quotation_charges[currency sell]),

 FILTER (

  quotation_charges,

  quotation_charges[quotation_id] = quotations[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 in which the revenue is quoted.

This logic ensures the currency is selected in the context of the current quotation.

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
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=ET_Customer_Invoice_Details&oldid=138"