Main Report
From UG
Mantis
BR: Business Requirements
- Need to add "Undefined" Mode to the list of Transport Mode filters for reports
- Need to add Report filter/output for Booking #
Specs
To my knowledge requirements are fully covered in these docs:
- http://mantis.jaguarfreight.com/svn/ct2/SS/reports/report_wizard/v2/specs.html
- http://mantis.jaguarfreight.com/svn/ct2/SS/reports/report_wizard/v2/REPORT-WIZARD.html
- http://mantis.jaguarfreight.com/svn/ct2/SS/reports/report_wizard/v2/QandA.html
- http://mantis.jaguarfreight.com/svn/ct2/SS/reports/report_wizard/v2/w5.Conditions_CLARIFICATIONS.html
todo: Tira, when you have free time pls migrate requirements above into this wiki doc
SD: Systems Design
Summary
Main Report (also called Main Wizard) is one of core reports of CT2. It provides the ability to search and display shipments, which comply to user-defined conditions.
- Main Report is similar to Where Is Report, but provides more options for searching of shipments and displaying them.
- Main Report (exactly the same as Where Is Report) NOT uses any Hard Coded Conditions.
- All needed User Defined Conditions (these are also called filters) are divided into groups in accordance with the area of them influence.
- Each group of filters is presented on the template of the Report in the form of Tab with the same name. For details see Filters section.
Interface
Main Report is available from the Main Menu of CT2 on the next path: "Reports -> Operational -> Main". See Figure 0.
- In order to generate report user have to:
- configure Filters on the template (see Figure 1) and
- click appropriate button for needed action
Main Report can be managed also through Saved/Scheduled Reports section.
- To do this, need to select this report from the class report dropdown and set the satisfied filters. See Figure 2.
Filters
Result of the Report that will be showed to user depends of the values of the Filters.
All Filters are divided into the next groups:
General Filters
General Filters Tab looks like on Figure 3.
GENERAL FILTERS are placed on the template in the following order:
- Client Companies:.
- Transport Modes:.
- Mapped to Mode Of Transport (MOT) (a.k.a. GenTab.F) from GenTab of CT.
- List of modes should contain all MOT including "Undefined".
- P.O. contains:.
- Mapped to PO from Commodity Table on ContTab of CT.
- SKU contains:.
- Mapped to SKU from Commodity Table on ContTab of CT.
- Export Ref # contains:.
- Mapped to Export Ref (a.k.a. GenTab.A) from GenTab of CT.
- Import Ref # contains:.
- Mapped to Import Ref (a.k.a. GenTab.B) from GenTab of CT.
- Customer Ref # contains:.
- Mapped to Customer Ref (a.k.a. GenTab.C) from GenTab of CT.
- Jaguar 3rd Ref # contains:.
- Mapped to Jaguar 3rd Ref (a.k.a. GenTab.D) from GenTab of CT.
- Steamshipline Booking Number contains:.
- Mapped to Booking Num (a.k.a. ExpTab.6) from ExpTab of CT.
- This filter is applicable only for MOT = Ocean and doesn't affect on the all of rest CTs.
- Extra Field 1 contains:.
- Mapped to Ext1 from Commodity Table on ContTab of CT.
- Extra Field 2 contains:.
- Mapped to Ext2 from Commodity Table on ContTab of CT.
- Extra Field 3 contains:.
- Mapped to Ext3 from Commodity Table on ContTab of CT.
- Shippers:.
- Consignees:.
- Origin Countries:.
- Mapped to Origin Country (a.k.a. GenTab.11) from GenTab of CT.
- Destination Countries:.
- Mapped to Destination Country (a.k.a. GenTab.12) from GenTab of CT.
- Origin Regions:.
- Mapped to Origin Country (a.k.a. GenTab.11) from GenTab of CT.
- Destination Regions:.
- Mapped to Destination Country (a.k.a. GenTab.12) from GenTab of CT.
- Shipping Terms:.
- Mapped to Shipping Terms (a.k.a. GenTab.14) from GenTab of CT.
- Pickup Locations:.
- Mapped to Export Pick Up (a.k.a. GenTab.5) from GenTab of CT.
- Should be displayed as pair Company-City.
- Pickup Location Cities:.
- Mapped to City what is derived from Export Pick Up Address (as part of GenTab.5) on GenTab of CT.
- Pickup Location States:.
- Mapped to State what is derived from Export Pick Up Address (as part of GenTab.5) on GenTab of CT.
- Pickup Location Postcodes:.
- Mapped to Zip what is derived from Export Pick Up Address (as part of GenTab.5) on GenTab of CT.
- Delivery Locations:.
- Mapped to Export Delivery To (a.k.a. GenTab.6) from GenTab of CT.
- Should be displayed as pair Company-City.
- Delivery Locations Cities:.
- Mapped to City what is derived from Delivery To Address (a.k.a. GenTab.6) from GenTab of CT.
- Delivery Locations States:.
- Mapped to State what is derived from Delivery To Address (a.k.a. GenTab.6) from GenTab of CT.
- Delivery Locations Postcodes:.
- Mapped to Zip what is derived from Delivery To Address (a.k.a. GenTab.6) from GenTab of CT.
- Cargo Locations:.
- Mapped to Cargo Location (a.k.a. ImpTab.8) from ImpTab of CT.
- This filter is applicable only for MOT = {Air, Ocean} and isn't affected at the Trucking CTs (all Trucking CTs will be included to report).
- No Archived.
- Checkbox that defines the presence of the "archived" CTs in the result of the Report.
Transportation Filters
Transportation Filters Tab looks like on Figure 4.
TRANSPORTATION FILTERS are placed on the template in the following order:
- Airlines:.
- Container #s:.
- Mapped to Container Num from Containers Table on ContTab of CT.
- This filter is applicable only for MOT = {Ocean FCL, Ocean LCL, Trucking-Ocean} and doesn't affect on the all of rest CTs.
- Origin Airports:.
- Mapped to Airport Of Departure (a.k.a. ExpTab.11a) from ExpTab of CT.
- This filter is applicable only for MOT = Air and doesn't affect on the all of rest CTs.
- Destination Airports:.
- Mapped to Airport Of Destination (a.k.a. ExpTab.14a) from ExpTab of CT.
- This filter is applicable only for MOT = Air and doesn't affect on the all of rest CTs.
- Steamshiplines:.
- Mapped to Steamshipline (a.k.a. ExpTab.4) from ExpTab of CT.
- Steamshiplines dropdown list should be mapped to Admin > Address Book > Vendors and provide for the selection a list of vendors with V2="Yes".
- This filter is applicable only for MOT = Ocean and doesn't affect on the all of rest CTs.
- Mapped to Steamshipline (a.k.a. ExpTab.4) from ExpTab of CT.
- Origin Ports/Terminals:.
- Mapped to Origin Terminal (a.k.a. ExpTab.11a) from ExpTab of CT.
- This filter is applicable only for MOT = Ocean and doesn't affect on the all of rest CTs.
- Destination Ports/Terminals:.
- Mapped to Destination Terminal (a.k.a. ExpTab.15a) from ExpTab of CT.
- This filter is applicable only for MOT = Ocean and doesn't affect on the all of rest CTs.
- Vessel Name contains:.
- Mapped to Vessel Name (a.k.a. ExpTab.9a) from ExpTab of CT.
- This filter is applicable only for MOT = Ocean and doesn't affect on the all of rest CTs.
- Pickup Truckers:.
- Mapped to Pick Up Trucker (a.k.a. GenTab.8) from GenTab of CT.
- Pickup Truckers dropdown list should be mapped to Admin > Address Book > Vendors and provide for the selection a list of vendors with V3="Yes".
- Mapped to Pick Up Trucker (a.k.a. GenTab.8) from GenTab of CT.
- PT Numbers:.
- Delivery Truckers:.
- Mapped to Delivery Trucker (a.k.a. ImpTab.9) from ImpTab of CT.
- Delivery Truckers dropdown list should be mapped to Admin > Address Book > Vendors and provide for the selection a list of vendors with V3="Yes".
- This filter is not applicable for all kinds of Trucking MOT.
- Mapped to Delivery Trucker (a.k.a. ImpTab.9) from ImpTab of CT.
- DT Numbers:.
- Co-Loaders Ocean:.
- Mapped to Co-Loaders (a.k.a. ExpTab.5) from ExpTab of CT.
- Co-Loaders Ocean dropdown list should be mapped to Admin > Address Book > Vendors and provide for the selection a list of vendors with V5="Yes".
- This filter is applicable only for MOT = {Ocean FCL, Ocean LCL, Trucking-Ocean} and doesn't affect on the all of rest CTs.
- Mapped to Co-Loaders (a.k.a. ExpTab.5) from ExpTab of CT.
- Co-Loaders Air:.
- Mapped to Co-Loaders (a.k.a. ExpTab.5) from ExpTab of CT.
- Co-Loaders Air dropdown list should be mapped to Admin > Address Book > Vendors and provide for the selection a list of vendors with V6="Yes".
- This filter is applicable only for MOT = Air and doesn't affect on the all of rest CTs.
- Mapped to Co-Loaders (a.k.a. ExpTab.5) from ExpTab of CT.
Date Filters
Date Filters Tab looks like on Figure 5.
Every Date Filter is a pair of "from" and "to" databoxes (to select range).
- NOTE: System uses the next logic for validating of every filter : from date MUST not be after to date (chronologically).
In the Main Report is used following DATE FILTERS (in that order of locating on the template):
- Estimated Pickup Date:. Mapped to:
- Estimated Export Pick Up Date (a.k.a. GenTab.9) from GenTab of CT for all modes except Ocean-FCL and Trucking-Ocean.
- Estimated Export Pick Up Date (a.k.a. Estimated collection) from Containers Table on ContTab of CT for MOT = {Ocean-FCL, Trucking-Ocean}.
- Actual Pickup Date:. Mapped to:
- Actual Export Pick Up Date (a.k.a. GenTab.10) from GenTab of CT for all modes except Ocean-FCL and Trucking-Ocean.
- Actual Export Pick Up Date (a.k.a. Actual collection) from Containers Table on ContTab of CT for MOT = {Ocean-FCL, Trucking-Ocean}.
- Estimated Departure Date:. Mapped to:
- Estimated Time of Departure (ETD a.k.a. ExpTab.11b) from ExpTab of CT for MOT = {Air, Ocean}.
- Estimated Pick Up Date (a.k.a. GenTab.9) from GenTab of CT for MOT = {Trucking-Air, Trucking-Domestic}.
- Estimated Export Pick Up Date (a.k.a. Estimated collection) from Containers Table on ContTab of CT for MOT = Trucking-Ocean.
- Actual Departure Date:. Mapped to:
- Actual Time of Departure (ATD a.k.a. ExpTab.11c) from ExpTab of CT for MOT={Air, Ocean}.
- Actual Pick Up Date (a.k.a. GenTab.10) from GenTab of CT for MOT = {Trucking-Air, Trucking-Domestic}.
- Actual Export Pick Up Date (a.k.a. Actual collection) from Containers Table on ContTab of CT for MOT = Trucking-Ocean.
- Estimated Arrival Date:. Mapped to:
- Estimated Time of Arrival (ETA a.k.a. ExpTab.15b) from ExpTab of CT for MOT=Ocean.
- Estimated Time of Arrival (ETA a.k.a. ExpTab.14b) from ExpTab of CT for MOT=Air.
- Estimated Delivery (a.k.a. GenTab.x) from GenTab of CT for MOT = {Trucking-Air, Trucking-Domestic}.
- Estimated Delivery Date (a.k.a. Requested Delivery) from Containers Table on ContTab of CT for MOT = Trucking-Ocean.
- Actual Arrival Date:. Mapped to:
- Actual Time of Arrival (ATA a.k.a. ExpTab.15c) from ExpTab of CT for MOT=Ocean.
- Actual Time of Arrival (ATA a.k.a. ExpTab.14c) from ExpTab of CT for MOT=Air.
- Actual Delivery (a.k.a. GenTab.y) from GenTab of CT for MOT = {Trucking-Air, Trucking-Domestic}.
- Actual Delivery Date (a.k.a. Actual Delivery) from Containers Table on ContTab of CT for MOT = Trucking-Ocean.
- Estimated Delivery Date:. Mapped to:
- Estimated Delivery Date from ImpTab of CT for MOT = {Air, Ocean-LCL}.
- Estimated Delivery Date (a.k.a. Requested Delivery) from Containers Table on ContTab of CT for MOT = {Ocean-FCL, Trucking-Ocean}.
- Estimated Delivery (a.k.a. GenTab.x) from GenTab of CT for MOT = {Trucking-Air, Trucking-Domestic}.
- Actual Delivery Date:. Mapped to:
- Actual Delivery Date from ImpTab of CT for MOT = {Air, Ocean-LCL}.
- Actual Delivery Date (a.k.a. Actual Delivery) from Containers Table on ContTab of CT for MOT = {Ocean-FCL, Trucking-Ocean}.
- Actual Delivery (a.k.a. GenTab.y) from GenTab of CT for MOT = {Trucking-Air, Trucking-Domestic}.
Condition Filters
Condition Filters Tab looks like on Figure 6.
In the Main Report is used following CONDITION FILTERS (in that order of locating on the template):
- On Customer Hold:. Radio group "Yes/No" pre-filled to "No" value.
- Mapped to On Customer Hold from GenTab of CT.
- Pending Approval Mode:. Radio group "Yes/No" pre-filled to "No" value.
- Mapped to Pending Approval Mode from GenTab of CT.
- Consol Warehouse:. Dropdown list pre-filled to "-undefined-" value.
- Mapped to Consol Warehouse from GenTab of CT.
- Billed:. Radio group "Yes/No" pre-filled to "Yes" value.
- When "Yes" is selected (means "Billed") then report should include only CTs for which Jaguar Sales Invoice was generated (at least one).
- When "No" is selected (means "Not Billed") then report should include only CTs for which Jaguar Sales Invoice wasn't generated (no Invoices).
- Created Since:. Datebox pre-filled to blank.
- Mapped to Created On Date.
- When datebox is filled with some value of date then report should include only those CTs which were created after that date.
NOTES
- All General Filters (except "No Archived" checkbox) and Transportation Filters are multiselect.
- Every filter connected by AND and selections inside filter are connected by OR.
- Empty Filters don't affect on the result of the Report.
Output Settings
There is one more Tab on the template - Output. It is located the latest immediately after the filter's tabs.
- This tab is designed to manage of the list of Output Fields (list of CT fields that can be output to the Report).
- On the Tab located the following elements (see Figure 7):
- Label "Fields".
- Dropdown list which corresponds to the following list of Output Fields:
- Shipper (output by default)
- Consignee (output by default)
- Transport Mode (output by default)
- Commodity Item P.O.
- Commodity Item SKU
- Commodity Item Description
- Commodity Item Quantity
- Commodity Item Total Pallets
- Commodity Item Total Loose Packages
- Commodity Item Gross Kg
- Chargeable Weight (list on commodity line)
- If more than 1 commodity line for CT, list only on the first line and leave 2nd, 3rd, etc blank. See Figure 10 for example
- Departure (output by default)
- Destination (output by default)
- Departure Region
- Destination Region
- Notification Date
- Authorization Date
- Estimated Departure Date
- Actual Departure Date (output by default)
- Estimated Arrival Date
- Actual Arrival Date (output by default)
- Estimated Collection Date
- Actual Collection Date
- Estimated Delivery Date
- Actual Delivery Date (output by default)
- Vessel Name
- Flight Number
- Container Description
- HAWB #
- Shipping Line
- Airline
- Commodity Item Extra Field 1
- Commodity Item Extra Field 2
- Commodity Item Extra Field 3
- Steamshipline Booking #
- Commodity Item Gross Lb
- Pickup Trucker
- Delivery Trucker
- PT Number
- DT Number
- Pickup Location
- Origin City
- Origin State
- Origin ZIP
- Delivery Location
- Destination City
- Destination State
- Destination ZIP
- Set of control buttons:
- ">>" - moves selected item from dropdown list to the listbox
- "<<" - removes selected item from the listbox (and move it to the listbox)
- "All" - moves all Output Fields from the dropdown list to the listbox
- "Clear" - completely clears the listbox
- "Up" - in the listbox moves selected item up one position
- "Down" - in the listbox moves selected item down one position
- Listbox that contains fields for output. By default contains list of output fields what are bolder than other in common list:
- Shipper, Consignee, Transport Mode, Departure, Destination, Actual Departure Date, Actual Arrival Date, Actual Delivery Date.
- NOTE: Dropdown list shouldn't include those fields that were selected for the output and are displayed in the listbox.
Action Controls
- Reset Form button:
- to clear form
- Generate Result button:
- to generate report
Output
The output results of Main Report is displayed in the new window (see Figure 3) and can be obtained in two formats:
- first, it is generated in HTML,
- then can be downloaded as a spreadsheet.
HTML output form
Header
- big bold label "Main"
- two action buttons:
- "Go Back" - allows go back to the template of the Report.
- "Download to Excel" - allows to save the report in spreadsheet's form to user's computer.
- list of selected Filters
Body
This is a table that contains a list of CTs, which are separated by tabs in accordance with the appropriate MOT.
There are five tabs on the resulting table:
- UNDEFINED - for UNDEFINED shipments
- OCEAN-FCL - same as above but for Ocean-FCL (AW/MLB) shipments
- OCEAN-LCL - for Ocean-LCL and Client Consol shipments
- AIR - for AIR shipments
- TRUCKING - for all kind of Trucking shipments
On the label of each tab also displays a count of found shipments.
Columns
- CT # - CT Number with hyperlink to View CT form in Internal App
- Shipper - Mapped to Shipper (a.k.a. GenTab.2) from GenTab of CT. Displays Shipper's Name with Shipper's city. Example: MHI - TETERBORO.
- Consignee - Mapped to Consignee (a.k.a. GenTab.3) from GenTab of CT. Displays Consignee's Name with Consignee's city. Looks the same as above.
- Transport Mode - Mapped to Mode Of Transport (MOT) (a.k.a. GenTab.F) from GenTab of CT.
- Departure - Location that shipment is moved from (port). Displays a pair of City and Country. Example: Hamburg, Germany.
- Mapped to:
- Origin Terminal (a.k.a. ExpTab.11a) from ExpTab of CT for MOT={Air, Ocean}.
- City From Shipper (a.k.a. GenTab.2) from GenTab of CT for MOT={Trucking, Undefined}.
- Mapped to:
- Destination - Location that shipment is moved to (port). Displays a pair of City and Country the same as for Departure.
- Airport Of Destination (a.k.a. ExpTab.14a) from ExpTab of CT for MOT=Air.
- Destination Terminal (a.k.a. ExpTab.15a) from ExpTab of CT for MOT=Ocean.
- City From Consignee (a.k.a. GenTab.3) from GenTab of CT for MOT={Trucking, Undefined}.
- Act Depart - Actual Date of Departure as define in Filters section for Actual Departure Date filter.
- Act Arriv - Actual Date of Arrival as define in Filters section for Actual Arrival Date filter.
- Act Deliv - Actual Date of Delivery as define in Filters section for Actual Delivery Date filter.
Paging and Sorting
- Output HTML table uses standart ZK Paging feature. Each page contains 20 lines.
- Sorting of table proceeds by first column - CT Number, in ascending order.
Spreadsheet output form
Header
- Title of the Report
- Datetime stamp
- Criteria(list of filters selected)
- CT2 logo in the top right corner
Body
Output spreadsheet contains information in separate tabs each of which corresponds to one MOT.
Tabs are placed in the next order (each tab name contains appropriate name of MOT and the count of shipments that found for it):
- UNDEFINED,
- OCEAN-FCL,
- OCEAN-LCL,
- AIR,
- TRUCKING.
At the beginning of body for each tab displays its name in accordance with the MOT and the count of found documents. In the next line of section displayed columns' headers. Then, line by line displayed information for each CT that were included to Report for this MOT.
Columns
List of columns is defined on the Output Tab on the template of the Main Report.
Paging and Sorting
- For the Spreadsheet output form a using of Paging is not required.
- Sorting of Spreadsheet table proceeds for the each MOT Tab by first column - CT Number, in ascending order.
Figures
Figure 0: Main Menu item
Figure 1: Main Report Filters
Figure 2: Saved/Scheduled Reports window
Figure 3: Main Report General Filters
Figure 4: Main Report Transportation Filters
Figure 5: Main Report Date Filters
Figure 6: Main Report Condition Filters
Figure 7: Main Report Output Tab
Figure 8: Main Report Output HTML form
Figure 9: Main Report Output Spreadsheet form
Figure 10: Main Report Output Spreadsheet form Chargeable Weight
History
previous version of spec - http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Main_Report&oldid=10240
Tweaks
tw1) Update the output list to be the same as Client Main Report Output list
tw2) Default selected output list in the combo box as list below:
- Shipper
- Consignee
- Mode
- Departure
- Destination
-
Chargeable Weight--> Removed from the default -- Tira 11:30 AM 16 Jan 2010 - Actual Departure Date
- Actual Arrival Date
- Actual Delivery Date
The rest of the list should be in default dropdown
tw3) Update label names:
- from "Departure Estimated Date" to "Estimated Departure Date"
- from "Departure Actual Date" to "Actual Departure Date"
- from "Arrival Estimated Date" to "Estimated Arrival Date"
- from "Arrival Actual Date" to "Actual Arrival Date"
tw4) Update filter names as listed below:
- From Countries -> Origin Countries
- To Countries -> Destination Countries
- From Regions -> Origin Regions
- To Regions -> Destination Regions
Tweaks version 1.1.a
Note: Tira 9:50 28 Oct 2009
tw1) Add summary shipment record() count on the report to indicate that how many shipments...
tw2) Create a HTML result page before excel report
tw3) Create download to excel button -> generate xls report
Note About Containers
Note about Archived CTs
They are not included into this Report
Tweaks version 1.1.b
tw1) Main Report - List of Transport Mode (filter) MUST include "UNDEFINED" as well. -- Tira 11:00 AM Feb 2, 2010
Ver 1.2
- spec updated? No
- 0001853: (Main Wizard) Add more OUTPUT options: Extra1, Extra2, Extra3 (as in 0001880)
- see 1880 for details
Ver 1.3
- spec updated? No
- 0001883: (Main Wizard) Add more FILTERS: PO contains, SKU contains; Extra1 contains, Extra2 contains, Extra3 contains (as in 1854)
- see 1854 for details
1924
- 0001924: (Main Wizard) Add Container Number Field to Output
- Add to the list below "Container Description"
m1752
BA
Mantis: 1752
- Need to add "Undefined" Mode to the list of Transport Mode filters for reports: scheduled reports, regular main reports and Main reports on the Client interface
- Also need to list this on the spreadsheet even if the Transportation Mode filter is not selected (as it must show results for all modes of transport including "UNDEFINED")
- current revision
SA
- Add Undefined Mode to Transport Mode filter on General filters tab
m1764
BA
Mantis: 1764
- Need to add Report filter for Booking # which is found on the export tab under field # 6; also Main reports on the Client interface
- Also need to list this output on the spreadsheet
- current revision
SA
- Add Booking # filter on Main report, Schedule Main report, client Main report m2040
Booking #
- Label: Booking # contains:
- Widget: textbox , can be multiple selected
- Location: After Jaguar 3rd Ref # contains:
- Add Booking # on Output tab
- Default: on the left side dropdown list
NOTE: this field is applied to Ocean shipments ONLY
m2618
BA
- Change: Do not repeat chargeable weight per commodity line
SOWs
SOW 1 Accounting fields should be visible for Account Users only
mantis: 4021
Core Requirements
- There are list of Accounting fields for output in Main Report:
- Vat Amount
- Duty Amount
- Freight Amount
- Air Amount
- FCL Amount
- LCL Amount
- Trucking Amount
- Other Amount
- Warehouse Amount
- Customs Amount
- China Amount
- We need remove these from usual Main Report but leave in special version of Main Report.
- Only SuperAcc and Management users could have access to special Main.
Solution
- It is suggested to have two types of Main Report
- common Main Report - without Accounting Fields and accessible for all users.
- special Main Report - with Accounting Fields and accessible for SuperAcc and Management users.