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:
|
| 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 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 |