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 Import Calculations

[WIP]

Calculation 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
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
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 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
HAE Invoice Customer Total (Sales BRL)
HAE Commision (BRL)
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)
Selling Rate
Selling Rate Currency
Mode Of Payment
Due Carrier Total
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=ET_Customer_Invoice_Details&oldid=130"