ET Customer Invoice Details - Revision history

Collins.bahati at 08:19, 19 February 2026

2026-02-19T08:19:27Z

← Older revision		Revision as of 10:19, 19 February 2026
Line 368:		Line 368:

	)		)
	\|Grab the ~~quotation charges~~ table, rename it to:		\|Grab the '''quotation_charges''' table and rename it to '''quotation_charges_Total Revenue'''. This version includes all relevant charge codes required to calculate total revenue—specifically LINEHAUL, DTC, and CAC.
	'''quotation_charges_Total Revenue'''.		Next, perform a '''NATURALINNERJOIN''' between the '''quotations''' table and '''quotation_charges_Total Revenue''' to create '''QuotationCombinedTableTotalRevenue'''. From this joined table, retrieve the following fields: <code>quotation_id</code> (renamed as ID), <code>chargeable_weight</code> from quotations, and <code>code</code>, <code>sell_rate</code>, and <code>sell_min</code> from quotation_charges_Total Revenue.
	This version includes all relevant charge codes ~~for calculating~~ total revenue—specifically '''~~LINEHAUL~~''' and '''~~DTC~~'''.

	~~Next~~, ~~perform a '''NATURALINNERJOIN''' between~~ the ~~'''quotations''' table and '''quotation_charges_Total Revenue'''~~ to ~~create '''QuotationCombinedTableTotalRevenue'''~~. ~~From this joined table~~, ~~retrieve~~ the ~~following fields:~~		Total Revenue is then calculated using conditional logic per charge code. If <code>code = "LINEHAUL"</code>, first compute <code>sell_rate × chargeable_weight</code>, then compare the result to <code>sell_min</code>. If the calculated value is less than <code>sell_min</code>, use <code>sell_min</code>; otherwise, use the calculated value. This ensures that minimum billing logic is enforced and revenue is never below the defined minimum charge.

	* '''quotation_id''' as ID		If <code>code = "DTC"</code>, use <code>sell_min</code>. If <code>code = "CAC"</code>, also use <code>sell_min</code>. For any other charge code, return 0.
	* '''chargeable_weight''' from '''quotations'''
	* '''code~~'''~~, ~~'''sell_rate'''~~, ~~and '''~~sell_min~~''' from '''quotation_charges_Total Revenue'''~~

	Finally, ~~compute '''~~Total Revenue~~''' using a conditional~~ logic:		Finally, sum all resulting values per quotation to produce the Total Revenue. This updated logic ensures that LINEHAUL revenue respects minimum charge thresholds while DTC and CAC continue to contribute their minimum charges, resulting in accurate and protected revenue calculations.
			*
	* If '''code = "LINEHAUL~~"''', multiply '''sell_rate''' by '''chargeable_weight'''~~
	* If '''code = "DTC~~"'''~~, ~~use '''sell_min'''~~
	* ~~Otherwise, return 0~~
	\|-		\|-
	\|Total Revenue Currency		\|Total Revenue Currency
Line 439:		Line 433:
	\|-		\|-
	\|Due Carrier Total		\|Due Carrier Total
	\|'''Due Carrier Total ='''		\|'''Due Carrier Total''' =
	SWITCH(		SWITCH (

			TRUE(),

			QuotationCombinedTableDueCarrierTotal[On] = "CH",

			VAR CalculatedChargeCH =

			QuotationCombinedTableDueCarrierTotal[Cost Rate]

			* QuotationCombinedTableDueCarrierTotal[Chargeable Weight]

			RETURN

			IF (

			CalculatedChargeCH <= QuotationCombinedTableDueCarrierTotal[Cost Min],

			QuotationCombinedTableDueCarrierTotal[Cost Min],

			CalculatedChargeCH

			),

			QuotationCombinedTableDueCarrierTotal[On] = "GR",

			VAR CalculatedChargeGR =

			QuotationCombinedTableDueCarrierTotal[Cost Rate]

			* QuotationCombinedTableDueCarrierTotal[Gross Weight]

			RETURN

			IF (

			CalculatedChargeGR <= QuotationCombinedTableDueCarrierTotal[Cost Min],

			QuotationCombinedTableDueCarrierTotal[Cost Min],

			CalculatedChargeGR

	~~TRUE(~~),		),

	~~QuotationCombinedTableDueCarrierTotal~~[on] = "CH"~~, QuotationCombinedTableDueCarrierTotal[cost_rate] * QuotationCombinedTableDueCarrierTotal[chargeable_weight]~~,		QuotationCombinedTableDueCarrierTotal[On] = "FL",

	~~QuotationCombinedTableDueCarrierTotal[on] = "FL",~~ QuotationCombinedTableDueCarrierTotal[~~cost_min~~],		QuotationCombinedTableDueCarrierTotal[Cost Min],

	0		0

	)		)
Line 461:		Line 495:

	* '''chargeable_weight''' from '''quotations'''		* '''chargeable_weight''' from '''quotations'''
			* '''gross_weight''' from quotations.
	* '''cost_rate''', '''code''', '''cost_min''', and '''on''' from '''quotation_chargesDueCarrierTotal'''		* '''cost_rate''', '''code''', '''cost_min''', and '''on''' from '''quotation_chargesDueCarrierTotal'''
	* '''exchange_rate''' from '''quotation_exchange_rates'''		* '''exchange_rate''' from '''quotation_exchange_rates'''
Line 467:		Line 502:

	* If '''on = "CH"''', multiply '''cost_rate''' by '''chargeable weight'''		* If '''on = "CH"''', multiply '''cost_rate''' by '''chargeable weight'''
			* If '''on = "GR"''', multiply '''cost rate''' by '''gross weight'''
	* If '''on = "FL"''', use '''cost_min'''		* If '''on = "FL"''', use '''cost_min'''
	* Otherwise, return '''0'''		* Otherwise, return '''0'''
	\|}		\|}

Collins.bahati at 08:12, 19 February 2026

2026-02-19T08:12:56Z

← Older revision		Revision as of 10:12, 19 February 2026
Line 297:		Line 297:
	\|-		\|-
	\|Due Carrier (USD)		\|Due Carrier (USD)
	\|~~'''~~Due Carrier USD~~'''~~ =		\|Due Carrier USD =
	SWITCH(		SWITCH(

	~~TRUE~~(),		TRUE(),

	~~QuotationCombinedTableDueCarrierUSD~~[on] = "CH", QuotationCombinedTableDueCarrierUSD[~~cost_rate~~] * QuotationCombinedTableDueCarrierUSD[~~chargeable_weight~~],		QuotationCombinedTableDueCarrierUSD[On] = "CH", QuotationCombinedTableDueCarrierUSD[Cost Rate] * QuotationCombinedTableDueCarrierUSD[Chargeable Weight],

	~~QuotationCombinedTableDueCarrierUSD~~[on] = "FL", QuotationCombinedTableDueCarrierUSD[~~cost_min~~],		QuotationCombinedTableDueCarrierUSD[On] = "GR", QuotationCombinedTableDueCarrierUSD[Cost Rate] * QuotationCombinedTableDueCarrierUSD[Gross Weight],

	0		QuotationCombinedTableDueCarrierUSD[On] = "FL", QuotationCombinedTableDueCarrierUSD[Cost Min],

			0

	)		)
Line 319:		Line 321:

	* '''chargeable_weight''' from quotations		* '''chargeable_weight''' from quotations
			* '''gross_weight''' from quotations.
	* '''cost_rate''', '''code''', '''cost_min''', and '''on''' from quotation_chargesDueCarrierUSD		* '''cost_rate''', '''code''', '''cost_min''', and '''on''' from quotation_chargesDueCarrierUSD
	* '''exchange_rate''' from quotation_exchange_rates		* '''exchange_rate''' from quotation_exchange_rates
Line 325:		Line 328:

	* If '''on = "CH"''', multiply '''cost rate''' by '''chargeable weight'''		* If '''on = "CH"''', multiply '''cost rate''' by '''chargeable weight'''
			* If '''on = "GR"''', multiply '''cost rate''' by '''gross weight'''
	* If '''on = "FL"''', use '''cost_min'''		* If '''on = "FL"''', use '''cost_min'''
	* Otherwise, return 0		* Otherwise, return 0

David.weller at 12:56, 26 September 2025

2025-09-26T12:56:23Z

← Older revision		Revision as of 14:56, 26 September 2025
Line 18:		Line 18:
	\|Customer		\|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.		\|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.
			\|-
			\|Date Range
			\|The date range is linked to the depart date.
	\|}		\|}

Collins.bahati at 09:31, 31 July 2025

2025-07-31T09:31:38Z

← Older revision		Revision as of 11:31, 31 July 2025
Line 241:		Line 241:
	\|-		\|-
	\|Buying Rate		\|Buying Rate
	\|		\|Buying Rate = quotation_routes[cost_rate]
	\|		\|'''Join''' the '''quotation_routes''' table (filtered where '''carrier_id = 12''') with the '''quotation_charges''' table, '''filtered where code = "LINEHAUL"'''.
			From this joined data, '''extract the cost_rate''' field and rename it as '''Buying Rate'''.
	\|-		\|-
	\|Buying Rate Total		\|Buying Rate Total

David.weller at 16:17, 30 July 2025

2025-07-30T16:17:32Z

← Older revision		Revision as of 18:17, 30 July 2025
Line 21:		Line 21:

	== Data Transformation Applied ==		== Data Transformation Applied ==
	~~[WIP]~~
	{\| class="wikitable"		{\| class="wikitable"
	\|+		\|+

Collins.bahati at 14:45, 30 July 2025

2025-07-30T14:45:03Z

@@ Line 116: / Line 116: @@
 * '''Renamed Columns''':  '''quotation_route_partsOriginal.flight_number''' → '''flight_number'''  '''quotation_route_partsOriginal.carrier_code''' → '''carrier_code'''
 |}
@@ Line 161: / Line 205: @@
 |-
 |Fixed_exRate
-|Fixed_exRate = IF(hae_quotations[is_fixed] = TRUE(), "✓", "×")
+|Fixed_exRate =
 |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
@@ Line 193: / Line 246: @@
 |-
 |Buying Rate Total
-|Buying Rate Total = [hae__routes.LINEHAUL.cost_rate] * [chargeable_weight]
+|'''Buying Rate Total''' =
 |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.
@@ Line 236: / Line 290: @@
 |-
 |HAE Tax BRL
-|HAE Tax BRL = if [Exchange_rate] = null or [Exchange_rate] = 0 then "NOEXR"
+|HAE Tax BRL = [HAE Tax USD] * [Exchange_rate]
 |convert the '''HAE Tax USD''' amount to '''BRL''' by multiplying it with the '''exchange_rate'''.
 |-
 |Due Carrier (USD)

Collins.bahati at 13:54, 30 July 2025

2025-07-30T13:54:54Z

@@ Line 25: / Line 25: @@
 |+
 !Query
-!Calculations
+!Transformations
 |-
+|
+|
+|
 |}

Collins.bahati at 13:16, 30 July 2025

2025-07-30T13:16:31Z

Show changes

Collins.bahati at 10:34, 29 July 2025

2025-07-29T10:34:28Z

← Older revision		Revision as of 12:34, 29 July 2025
Line 117:		Line 117:
	\|-		\|-
	\|Buying Currency		\|Buying Currency
	\|		\|'''Buying Currency = currency''' from quotation_charges, joined via quotation_id.
	\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.		\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
	From the '''quotation_charges''' table, retrieve the '''currency''' field.		From the '''quotation_charges''' table, retrieve the '''currency''' field.
	\|-		\|-
	\|AWB Rate		\|AWB Rate
	\|		\|'''AWB Rate = sell_rate''' from quotation_charges where code = 'LINEHAUL', joined via quotation_id.
	\|		\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
			From the '''quotation_charges''' table, filter to only include the charge where '''code = 'LINEHAUL''''.

			Retrieve the '''sell_rate''' field for this charge, which represents the '''AWB Rate'''.
	\|-		\|-
	\|Due Agent		\|Due Agent

Collins.bahati at 10:28, 29 July 2025

2025-07-29T10:28:48Z

← Older revision		Revision as of 12:28, 29 July 2025
Line 54:		Line 54:
	\|-		\|-
	\|Flight_Number		\|Flight_Number
	\|		\|'''flight_number''' — retrieved from hae_route_parts, filtered to the minimum quotation_route_id per quotation_id, and joined back to quotations via quotation_id
	\|		\|Retrieve the '''flight_number''' associated with each quotation by linking route data. This is done by identifying the first (minimum) '''quotation_route_id''' per '''quotation_id''', then using it to pull the corresponding '''flight_number''' from the route details.
	\|-		\|-
	\|Depart Date		\|Depart Date
Line 118:		Line 118:
	\|Buying Currency		\|Buying Currency
	\|		\|
	\|		\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
			From the '''quotation_charges''' table, retrieve the '''currency''' field.
	\|-		\|-
	\|AWB Rate		\|AWB Rate
Line 142:		Line 143:
	\|-		\|-
	\|HAE Tax (charge currency)		\|HAE Tax (charge currency)
	\|		\|'''HAE Tax (charge currency) = currency_sell''' from quotation_charges, joined via quotation_id.
	\|		\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
			From the '''quotation_charges''' table, retrieve the '''currency_sell''' field, which indicates the currency of the HAE Tax.
	\|-		\|-
	\|HAE Tax BRL		\|HAE Tax BRL
Line 216:		Line 218:
	\|-		\|-
	\|HAE Commision (Margin) BRL		\|HAE Commision (Margin) BRL
	\|		\|HAE Margin BRL =
	\|		if ([Exchange_rate] = null or [Exchange_rate] = 0) then null

			else [HAE Commission] * [Exchange_rate]
			\|After retrieving the '''HAE Commission''' and the applicable '''Exchange_rate''' (ensuring it's the most recent per quotation_id), calculate the '''HAE Margin in BRL''' by converting the commission using the exchange rate.
			If the '''Exchange_rate''' is null or zero, return null to avoid invalid conversion.
	\|-		\|-
	\|HAE Margin BRL		\|HAE Margin BRL
Line 237:		Line 243:
	\|-		\|-
	\|Selling Rate		\|Selling Rate
	\|		\|'''Sell Rate = sell rate''' from quotation_charges, joined via quotation_id.
	\|		\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
			From the '''quotation_charges''' table, retrieve the '''sell_rate''' field.
	\|-		\|-
	\|Selling Rate Currency		\|Selling Rate Currency
			\|'''Sell Rate Currency = currency sell''' from quotation_charges, joined via quotation_id
	\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.		\|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
	From the '''quotation_charges''' table, retrieve the '''currency sell''' field.		From the '''quotation_charges''' table, retrieve the '''currency sell''' field.
	~~\|'''Sell Rate Currency = currency sell''' from quotation_charges, joined via quotation_id~~
	\|-		\|-
	\|Mode Of Payment		\|Mode Of Payment
	\|~~'''Retrieved Value:'''~~		\|'''Mode of Payment = field_value''' from quotation_fields where carrier_quotation_field_id = 199, joined to quotations via quotation_id.
	'''Mode of Payment = field_value''' from quotation_fields where carrier_quotation_field_id = 199, joined to quotations via quotation_id.
	\|Retrieve the '''Mode of Payment''' from the '''quotation_fields''' table by filtering records where '''carrier_quotation_field_id = 199''', which corresponds to this customer field.		\|Retrieve the '''Mode of Payment''' from the '''quotation_fields''' table by filtering records where '''carrier_quotation_field_id = 199''', which corresponds to this customer field.
	Join the '''quotation_fields''' table with the '''quotations''' table using '''quotation_id''' to ensure the value is linked to the correct quotation.		Join the '''quotation_fields''' table with the '''quotations''' table using '''quotation_id''' to ensure the value is linked to the correct quotation.