View source for Quotations Extract

== Overview ==
Captures all job-related data from QMS, including every stage of the shipment lifecycle—from pre-quotes and formal quotations to bookings and automation sync events. This extract offers a complete view of how a job progresses through the system, making it ideal for tracking and analyzing conversion rates, booking efficiency, and automation coverage. Users can leverage this data to monitor performance, identify trends in quoting and booking behavior, and gain insight into the timing and effectiveness of automation within the booking process.

== Extract Data ==

=== Existing Extract (Kept Columns) ===
{| class="wikitable"
|+
!Column
!Description
|-
|quotation_id
|Job id for prequotes, quotes and bookings
|-
|brand
|The QMS portal name
|-
|customer_D365_code
|The customers dynamics 365 code
|-
|origin_airport
|The origin airport of shipment
|-
|destination_airport
|The destination airport of shipment
|-
|product
|If the job is airport to airport, door to airport, door to airport or door to door
|-
|customer_name
|Name of the customer
|-
|cargo_available_date
|The ready date of the shipment
|-
|volumetric_weight
|The volumetric weight of the shipment
|-
|gross_weight
|The gross weight of the shipment
|-
|chargeable_weight
|The chargeable weight of the shipment
|-
|total_pieces
|The total pieces on the shipment
|-
|total_volume
|The total volume of the shipment
|-
|carrier_name
|The carrier the shipment is booked on
|-
|site_name
|The site the shipment is on within QMS
|-
|deleted_at
|If there is a date present, the shipment was deleted on the date specified. If NULL it isn't deleted
|-
|cancelled
|If the shipment was requested to be cancelled by customer
|-
|lost
|If there is a date present, the shipment was lost on the date specified. If NULL it isn't lost
|-
|lost_reason
|The reason the shipment was lost
|-
|spot_rate
|If the shipment was spot rated
|-
|commodity_description
|The description of the shipment
|-
|margin_value
|The total commission made on a job based the invoice currency
|-
|commission_value
|Any additional commission made on the shipment
|-
|WFCProductCode
|Linked WFC Product codes to shipment
|-
|automated_created
|True / False. 0 = False, and 1 = True. 
|}

=== Existing Extract (New Columns) ===
{| class="wikitable"
|+
!Column
!Description
|-
|length_unit
|To show the length unit used on the shipment 
|-
|weight_unit
|To show the weight unit used on the shipment
|-
|master_airwaybill
|The master airway bill on used on the shipment
|-
|deleted_reasons
|If deleted, the reasons the shipment was deleted
|-
|is_booking_request
|True / False. 0 = False, and 1 = True. If true, shipment was a booking request. 
|-
|type
|Used to isolate if the shipment is a master of the brand bookings. Identified using '8' 
|-
|group_type
|Used to isolate the sub-records of master records. Identified using '8'
|-
|automated_created_count
|If value greater than 0, the automation ran on shipment. Greater than 1 shows how many times the automation ran on the shipment
|-
|schedule_auto_update
|To show the user if the automation schedule confirmation has ran, which would be signified by a 1 if it has ran.
|-
|schedule_auto_amount
|To show the user how many times the automation schedule confirmation ran
|-
|sanction_id
|To show if shipment went through a sanctions approval process
|-
|rebook_count
|How many times the shipment has been rebooked
|-
|pre_quoted_at
|Date/time stamp. If / when the shipment was prequoted
|-
|quoted_at
|Date/time stamp. If / when the shipment was quoted
|-
|booked_at
|Date/time stamp. If / when the shipment was booked
|-
|confirmed_at
|Date/time stamp. If / when the shipment was confirmed
|-
|total_sell
|The total sale of the shipment (customer invoice currency)
|-
|total_cost
|The total cost of the shipment (customer invoice currency)
|-
|total_sell_rate
|The total sale rate of the shipment (customer invoice currency)
|-
|total_cost_rate
|The total cost rate of the shipment (customer invoice currency)
|-
|total_sell_currency
|The total sale currency (customer invoice currency)
|-
|total_cost_currency
|The total cost currency (customer invoice currency)
|-
|hazardous_goods
|True / False. 0 = False, and 1 = True. If true, shipment is a dangerous goods shipment
|-
|cargo_only
|True / False. 0 = False, and 1 = True. If true, shipment is cargo aircraft only.
|-
|tariff_customer_id
|If shipment is using a contract rate. If the value is null, a contract tariff has not been used
|-
|flag_promo
|True / False. 0 = False, and 1 = True. If true, shipment is using a promotional tariff 
|-
|invoice_currency
|The currency of the customer and what they invoice in. 
|-
|customer_UUID
|Unique customer id from the master customer project. Can be "NULL" if no uuid provided. 
|}

=== Extract Requested Fields By Man Lee (Approved) ===
{| class="wikitable"
!Column 
!Description
|-
|wfc_entity_id
|WFC identification numbers 
|-
|route
|Route with IATA codes separated by '-' (e.g. LHR-JFK-EMA) 
|-
|flight_numbers
|Full route of flight numbers, separated by '-' if available 
|-
|carrier_class
|Determine the carrier class (airline, truck, sea freight etc) 
|-
|is_cass
|True/False. If customer is CASS.
|-
|handling_code_1 
|3-letter cargo type code (e.g. PER, DGR) 
|-
|actual_flight_dates
|Actual Flight Dates, they will be in the following format: depart/arrive - depart/arrive - depart/arrive 
|-
|planned_flight_dates
|Planned Flight Dates based on what is booked, they will be in the following format: depart/arrive - depart/arrive - depart/arrive
|-
|currency_lcy
|Entity currency used on job (site currency) 
|}

=== Extract Requested Fields By Man Lee (Not Approved, In Discussion) ===
{| class="wikitable"
!Column 
!Description
!Comments
|-
|total_sell_fsc
|Sell fuel/security charges only 
|Discussing in spreadsheet
|-
|total_cost_fsc
|Cost fuel/security charges only
|Discussing in spreadsheet
|-
|sell_other_total
|Sell surcharges excluding linehaul, fuel, security
|Discussing in spreadsheet
|-
|cost_other_total
|Cost surcharges excluding linehaul, fuel, security
|Discussing in spreadsheet
|-
|AWB_Status
|N/A
|WFC team reviewing
|-
|GSA_Commission
|Remuneration/Gross Profit per AWB
|Discussing in spreadsheet
|-
|departed_origin_date
|Actual date flight departed from origin
|Discussing in spreadsheet, assumed not required as providing "flight_dates" which will include this
|}

== Example Calculations ==

====== Automation Time Saved (Booking Carrier Platform Sync) ======
To calculate the time saved transferring bookings from QMS into the carrier booking systems, you just need to follow the steps below:

# Identify if shipment was automated or not using <code>automated_created</code> and confirming is <code>true</code> which means the automation has ran and the booking isn't manually processed. 
# Then retrieve the amount of times the automation has ran on the shipment via the <code>automated_created_count</code> which will give you total times that the automation has ran on this shipment.
# After this you would multiply the <code>automated_created_count</code> by 5 to give you the total minutes saved. 
<pre>automated_created_count * 5 = total minutes saved</pre>

====== Automation Time Saved (Schedule Booking Confirmation) ======
To calculate the time saved for retrieving confirmation status and schedule confirmation automatically from the airline systems.
# Total the shipments with <code> schedule_auto_update = 1</code> and <code>booked_at </code> has a date value in it. 
# Multiply the number by 5 to calculate the time saved in minutes.
<pre>(schedule_auto_update & booked_at) * 5 = total minutes saved</pre>

====== Automation Time Saved (Converting from quotes to bookings) ======
To calculate the time saved for converting a quote into a booking.
# Total the shipments that have time date stamps in <code>quoted_at</code> and <code>booked_at</code> and multiply that by 5 to get your total minutes saved. 
<pre>(booked_at & quoted_at) * 5 = total minutes saved</pre>

====== Automation Time Saved (Converting from prequote to quote / booking) ======
To calculate the time saved for converting a prequote into a quote or a booking. 
# Total the number of shipments that have <code>pre_quote_at</code> & <code>quoted_at</code> to store as your total pre quotes to quotes conversion.
# Total the number of shipments that have <code>pre_quoted_at</code> & <code>booked_at</code> to store as your total pre quotes to bookings conversion.
# Get the totals together and multiply by 5 to get the total minutes saved.
<pre>(pre_quoted_at & quoted_at + pre_quoted_at & booked_at) * 5 = total minutes saved</pre>

====== Identify Prequotes, quotes, bookings and confirmed bookings ======
Within the dataset you have a single shipment which can be a different combinations of jobs status and it can easily be identified what status it is currently in and what status it has come from or been converted from. The absolute end time stamp would be <code>confirmed_at</code> this tells you that the job was confirmed by the airline as booked. The additional time stamps that are used would be <code>pre_quoted_at, quoted_at, booked_at</code> and it's using these which can tell you where the job is or where it came from, and using the time stamp you can determine at what time and date the job was moved into these states. If there isn't a timestamp present it means the job hasn't go through this method or reached that far. 

For example, if there isn't a time stamp for <code>confirmed_at</code> then the job hasn't been confirmed booked by the airline yet. You will not see <code>confirmed_at</code> without <code>booked_at</code> because the job is required to be booked in the QMS system to be confirmed by the airline. 

* <code>pre_quoted_at</code> alone would mean currently the shipment is only a pre quote.
* <code>quoted_at</code> alone would mean the shipment wasn't pre quoted before being made as a quote and isn't a booking yet. 
* <code>booked_at</code> alone would mean that the shipment was a direct booking with no pre quote or quote. 

From here you can have different combinations that would mean different things, please see some examples:
*   <code>pre_quoted_at</code> & <code>booked_at</code> would mean the shipment was originally pre quoted and then it was booked. 
* <code>quoted_at</code> & <code>booked_at</code> would mean the shipment was quoted and then booked but isn't yet confirmed. 
* <code>pre_quoted_at</code>, <code>quoted_at</code> & <code>booked_at</code> would mean the shipment was pre quoted, quoted and then booked but it hasn't been confirmed yet. 
* <code>booked_at</code> & <code>confirmed_at</code> it would mean that the shipment was a direct booking that has been confirmed by the airline system. 
* <code>pre_quoted_at</code>, <code>quoted_at</code>, <code>booked_at</code> & <code>confirmed_at</code> would mean the shipment has been pre quoted, quoted, booked and has been confirmed by the airline system. 

== Columns: Removal Planned ==
'''Note: The won't be removed until final confirmation from Sanders PBI team and until that time will continue to be included.'''  

Here are the columns that we would like to remove from the extract because they are either deprecated or wouldn't provide any reporting data that could be used. 
{| class="wikitable"
!Column
!Description
|-
|entity_name
|This is just an ID that can't be utilized by lata lake
|-
|customer_id
|This is the database ID of the customer which can't be utilized by data lake
|-
|hos_quote_reference
|As we are moving away from HOS this isn't required
|-
|quote_creation_date
|Covered in the new pre_quoted_at, quoted_at, booked_at fields
|-
|carrier_deleted
|It isn't relevant to data lake if the carrier is deleted
|-
|carrier_parent_id
|This isn't required for shipment level reporting
|-
|customer_deleted_at
|It isn't relevant to data lake if the customer is deleted
|-
|carrier_site
|This is an ID that can't be utilized by data lake
|-
|status
|High level status of the job isn't required
|-
|HasPrequote
|Covered in the new pre_quoted_at, quoted_at, booked_at fields
|-
|HasQuote
|Covered in the new pre_quoted_at, quoted_at, booked_at fields
|-
|HasBooking
|Covered in the new pre_quoted_at, quoted_at, booked_at fields
|-
|currency
|Only covers a single currency when there could be two
|-
|total_cost_value
|Not entirely sure where this value is captured from but captured in the "total_cost"
|-
|total_sale_value
|Not entirely sure where this value is captured from but captured in the "total_sale"
|-
|average_buy_rate
|We are now providing the actual rate, not the average
|-
|average_sell_rate
|We are now providing the actual rate, not the average
|-
|automated_cancel
|Deprecated and no longer used
|}