View source for 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 ==
{| class="wikitable"
|+
!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]
{| class="wikitable"
|+
!Calculation
!Description
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!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 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 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
|
|
|}