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
master_airwaybill
Customer
Flight_Number
Depart Date
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
Fixed_exRate
origin
Destination
gross_weight
chargeable_weight
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
HAE Over
HAE Tax USD
HAE Tax (charge currency)
HAE Tax BRL
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