ET Customer Invoice Details

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)	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
Selling Rate Currency
Mode Of Payment
Due Carrier Total