Daily Report

From UG

(Difference between revisions)
Jump to: navigation, search
(Filters and Filters Matching Logic)
(History)
 
(37 intermediate revisions not shown)
Line 12: Line 12:
* This report should appear only in Saved/Scheduled Reports section (to run this report you have to schedule it).
* This report should appear only in Saved/Scheduled Reports section (to run this report you have to schedule it).
-
* Report MUST include ALL new shipments records that are created since previous report
+
* Report MUST include ALL new shipments records that are created since previous report, including undefined MOT
 +
* Report MUST ALSO include shipment records that the [[CT_bo#E0_Client]] is updated since previous report
* Report shows date time stamp and how many records are found (number)
* Report shows date time stamp and how many records are found (number)
* Report is sent to recipients on schedules via email.
* Report is sent to recipients on schedules via email.
Line 29: Line 30:
* Report should use [[CT#Created On Date]] date field to identify when shipment was created.
* Report should use [[CT#Created On Date]] date field to identify when shipment was created.
 +
 +
* Report should identify records where the [[CT_bo#E0_Client]] was updated since previous report.
 +
:* ''For example,'' '''IF''' CT# was created for E0.Client ''Company A'', this '''MUST''' be showing on the result for ''Company A''<br>'''THEN''' later on this CT# was updated E0. Client Company from ''A'' to ''B'', this '''MUST''' be showing on the result for ''Company B''.
=== User Defined Conditions ===
=== User Defined Conditions ===
Line 36: Line 40:
* Filters consist of several groups
* Filters consist of several groups
* Every filter is connected by '''AND''' and sub selections inside each filter combo-box are connected by '''OR'''
* Every filter is connected by '''AND''' and sub selections inside each filter combo-box are connected by '''OR'''
-
** For example: ((Client Companies = E.A. USA Domestic) '''OR'''(Client Companies = Elizabeth Arden)) '''AND''' ((Shippers = A-TESTCOMP - TestCity - test remark)'''OR'''(Shippers = ABC - HK - test))
+
** For example: ((Client Companies = "a") '''OR'''(Client Companies = "b")) '''AND''' ((Shippers = "c")'''OR'''(Shippers = "d"))
* If none of the filters are selected, generate ALL results.
* If none of the filters are selected, generate ALL results.
Line 45: Line 49:
* [[Reports Filters#Client]]
* [[Reports Filters#Client]]
-
* [[Reports Filter#Shippers]]
+
* [[Reports Filters#Shipper]]
-
* [[Report Filter#Consignees]]
+
* [[Reports Filters#Consignee]]
* [[Reports Filters#Origin Country]]
* [[Reports Filters#Origin Country]]
Line 59: Line 63:
=== Output ===
=== Output ===
* Create output tab with list of output headings
* Create output tab with list of output headings
-
* These output headings are almost the same as Daily Report but label names and orders are different
 
* Required default headings on the combo box, the rest of headings are in drop down filter output list
* Required default headings on the combo box, the rest of headings are in drop down filter output list
* List of headings [[#Columns Definitions]]
* List of headings [[#Columns Definitions]]
Line 72: Line 75:
** Default on the ''combo box'' selected list
** Default on the ''combo box'' selected list
-
* Vendor
+
* Customer References#
 +
** Mapped to [[CT#Customer Ref]]
 +
 
 +
* Transportation Modes
 +
** Mapped to [[CT#Mode Of Transport]]
 +
** Default on the ''combo box'' selected heading output list
 +
 
 +
* Shippers
** Mapped to [[CT#Shipper]]
** Mapped to [[CT#Shipper]]
** Default on the ''combo box'' selected heading output list
** Default on the ''combo box'' selected heading output list
-
* Division
+
* Consignees
-
** No mapping (Heading Only)
+
** Mapped to [[CT#Consignee]]
-
** Default value: N/A for data
+
** Default on the ''combo box'' selected heading output list
** Default on the ''combo box'' selected heading output list
-
* P/O num
+
* Origin Countries
 +
** Mapped to [[CT#Origin Country]]
 +
** Default on the ''combo box'' selected heading output list
 +
 
 +
* Destination Countries
 +
** Mapped to [[CT#Destination Country]]
 +
** Default on the ''combo box'' selected heading output list
 +
 
 +
* P.O.
** Mapped to [[Commodity#PO]]
** Mapped to [[Commodity#PO]]
** Default on the ''combo box'' selected heading output list
** Default on the ''combo box'' selected heading output list
-
* Item code
+
* SKU
** Mapped to [[Commodity#SKU]]
** Mapped to [[Commodity#SKU]]
** Default on the ''combo box'' selected heading output list
** Default on the ''combo box'' selected heading output list
Line 93: Line 110:
** Default on the ''combo box'' selected heading output list
** Default on the ''combo box'' selected heading output list
-
* Weight
+
* Item Description
-
** Mapped to [[Commodity#Gross Kg]]
+
** Mapped to [[Commodity#Description]]
-
** Default on the ''combo box'' selected heading output list
+
-
* Cartons
+
* Ttl # of plts
 +
** Mapped to [[Commodity#Plts]]
 +
 
 +
* Ttl pkgs on plts
** Mapped to [[Commodity#Pkgs On Plts]]
** Mapped to [[Commodity#Pkgs On Plts]]
-
** Default on the ''combo box'' selected heading output list
 
-
 
-
* Pallets
 
-
** Mapped to [[Commodity#Plts]]
 
-
** Default on the ''combo box'' selected heading output list
 
-
* Drums
+
* Loose pkgs
** Mapped to [[Commodity#Loose Pkgs]]
** Mapped to [[Commodity#Loose Pkgs]]
-
** Default on the ''combo box'' selected heading output list
 
-
* Carrier
+
* [[Reports_Output#Gross_KG]]
-
** No mapping (Heading Only)
+
* [[Reports_Output#Gross_LB]]
-
** Default value: Jaguar
+
-
** Default on the ''combo box'' selected heading output list
+
-
* Mode
+
* Commodity Item Invoice
-
** Mapped to [[CT#Mode Of Transport]]
+
** Mapped to [[TBD]]
-
** Default on the ''combo box'' selected heading output list
+
* Commodity Item Pieces Per Package
 +
** Mapped to [[TBD]]
 +
* Commodity Item Package Type
 +
** Mapped to [[TBD]]
-
* Carrier Remarks
+
* Custom fields
-
** No mapping (Heading Only)
+
** if E0 client has "custom fields" that is/are set in admin mode, these should be available list as well
-
** Default value to blank
+
** Heading on output tab and spreadsheet should be Extra 1, Extra 2,...etc
-
** Default on the ''combo box'' selected heading output list
+
-
* Destination
+
* Total CBM
-
** Mapped to [[CT#Consignee]]
+
** Mapped to [[Commodity#Grand Total: cbm]]  
-
** Default on the ''combo box'' selected heading output list
+
-
* Estimated Delivery
+
* Authorization
-
** Mapped to [[CT#Automated Estimated Delivery Date]]
+
-
** Default on the ''combo box'' selected heading output list
+
-
 
+
-
* Actual Delivery
+
-
** Mapped to [[CT#Actual Delivery Date]]
+
-
** Default on the ''combo box'' selected heading output list
+
-
 
+
-
* Revise Date
+
** No mapping (Heading Only)
** No mapping (Heading Only)
** Default value to blank
** Default value to blank
 +
** this field is allowed user to manage it manually [Y/N]
** Default on the ''combo box'' selected heading output list
** Default on the ''combo box'' selected heading output list
-
* Remarks Planning
+
* Cargo Available
-
** No mapping (Heading Only)
+
** Mapped to [[CT#Cargo_available]]
-
** Default value to blank
+
-
** Default on the ''combo box'' selected heading output list
+
-
* Customer References#  
+
* Ship from State
-
** Mapped to [[CT#Customer Ref]]
+
** Mapped to [[CT#Pick_Up_State]]
 +
* Ship from ZIP
 +
** Mapped to [[CT#Pick_Up_Zip]]
-
* Origin Countries
+
* Ship to State
-
** Mapped to [[CT#Origin Country]]
+
** Mapped to [[CT#Ship_to_State]]
 +
* Ship to ZIP
 +
** Mapped to [[CT#Pick_Up_Zip]]
-
* Destination Countries
+
* Container Description
-
** Mapped to [[CT#Destination Country]]
+
** Mapped to [[Container#Container_Num]]
 +
** Showed as [[Container#Container_Num | Container#]], [[Container#Size | Size]] [[Container#Type | Type]]. Example: ''CONT-200222-1, 20 ft 1. HQ (1 of 2)''.
 +
*** NOTE: ''Now is displayed '''1st''' container for each P.O. (bug?) --[[User:Andrei|Andrei]] 10:49, 2 September 2010 (EDT)''
 +
* Master Bill
 +
** Mapped to: if MOT=AIR: [[CT#MAWB]], if MOT=Ocean: [[CT#MBL]], otherwise leave blank.
-
* Item Description
+
* Shipper State
-
** Mapped to [[Commodity#Description]]
+
** Mapped to [[CT#Shipper_State]]
 +
* Shipper ZIP
 +
** Mapped to [[CT#Shipper_ZIP]]
-
* Custom fields
+
* Consignee State
-
** if E0 client has "custom fields" that is/are set in admin mode, these should be available list as well
+
** Mapped to [[CT#Consignee_State]]
-
** Heading on output tab should be Extra 1, Extra 2,...etc
+
* Consignee ZIP
-
** Heading Must be dynamic name on the spreadsheet
+
** Mapped to [[CT#Consignee_ZIP]]
-
*** For example: if there is a custom field "Extra 1 = Lot#", it MUST show "Lot#" instead of "Extra line 1" on the spreadsheet
+
-
* Total CBM
+
* Ship from
-
** Mapped to [[Commodity#Grand Total: cbm]]  
+
** Mapped to [[CT#Export_Pick_Up]]
-
 
+
* Ship to
-
* Authorization
+
** Mapped to [[CT#Export_Delivery_To]]
-
** No mapping (Heading Only)
+
-
** Default value to blank
+
-
** this field is allowed user to manage it manually [Y/N]
+
==== Sort By Definitions ====
==== Sort By Definitions ====
* Sort by CT# from earlier CT# to newer CT#
* Sort by CT# from earlier CT# to newer CT#
-
* Tab by alphabet order
+
 
==== Summary ====
==== Summary ====
Line 192: Line 201:
=== Special Cases ===
=== Special Cases ===
-
 
+
TBD
-
For client company which has an extra field (custom field), it should grab custom field for heading dynamically.
+
-
 
+
-
Example: Elizabeth Arden has a custom field (Extra 1) which is "Lot#". Lot# will be showing on the heading of the spreadsheet for Elizabeth Arden company.
+
=== Figures ===
=== Figures ===
==== Figure 1. Schedule dropdown menu ====
==== Figure 1. Schedule dropdown menu ====
 +
[[File:DailyScheduled.JPG]]
-
== Filters ==
+
==== Figure 2. General Tab ====
-
* Client Companies (E0 Client)
+
[[File:GenFilterDaily.JPG]]
-
* Shippers:
+
-
* Consignees:
+
-
* Origin Countries:
+
-
* Destination Countries:
+
-
* Origin Regions:
+
-
* Destination Regions:
+
-
== Output ==
+
==== Figure 3. Spreadsheet  ====
-
* Create output tab with list of output headings
+
[[File:DailySpreadSheet.JPG]]
-
* Some headings should be defaulted on the combo box and the rest of headings are in the dropdown filter output list
+
-
* List of headings
+
-
** CT# with link ->''defaulted on the combo box''
+
-
** Customer References# (GenTab C.Customer Ref)
+
-
** Transport Modes (GenTab F. Mode of Transport:)-> ''defaulted on the combo box''
+
-
** Shippers (GenTab 2.Shipper) ->''defaulted on the combo box''
+
-
** Consignees (GenTab 3.Consignee) ->''defaulted on the combo box''
+
-
** Origin Countries (GenTab 11. Origin country) -> ''defaulted on the combo box''
+
-
** Destination Countries (GenTab 12. Destination country) -> ''defaulted on the combo box''
+
-
** P.O (ContTab Table A.)-> ''defaulted on the combo box''
+
-
** SKU (ContTab Table A.)-> ''defaulted on the combo box''
+
-
** Quantity (ContTab Table A.) -> ''defaulted on the combo box''
+
-
** Item Description (ContTab Table A.)
+
-
** Ttl # of plts (ContTab Table A.)
+
-
** Ttl pkgs on pkgs (ContTab Table A.)
+
-
** Loose pkgs (ContTab Table A.)
+
-
** Ttl G kg (ContTab Table A.)
+
-
** ''Custom fields'' (if E0 client has "custom fields" that is/are set in admin mode, these should be available list as well)
+
-
** Total CBM (ContTab Table B.)
+
-
** Authorization [Y/N] (this field is not mapped to any field, it is always empty and allow user to manage it manually)-> ''defaulted on the combo box''
+
-
[[Media:Example.ogg]]
+
 +
== History ==
 +
=== Ver 1.1 ===
 +
* spec updated? No
-
[[File:DailyScheduled.JPG]]
+
*  0001882: (Daily) Add more OUTPUT options: MAWB, container num, Qty, Extra1, Extra2, Extra3 (as in 0001880)
 +
 
 +
* see mantis #1880 for details
 +
 
 +
 
 +
=== m2037 ===
 +
* http://mantis.jaguarfreight.com/mantis/view.php?id=2037
 +
 
 +
==== BA ====
 +
In Output:
 +
* Rename "Ttl G kg" into "Gross KG"
 +
* Add [[Reports_Output#Gross__LB]]
 +
 
 +
===== Part 2 =====
 +
* Add these fields to Output:
 +
** [[Reports Output#Cargo Available]]
 +
** [[Reports Output#Ship from State]]
 +
** [[Reports Output#Ship from Zip]]
 +
** [[Reports Output#SKU]]
 +
** [[Reports Output#Ship to State]]
 +
** [[Reports Output#Ship to Zip]]
 +
 
 +
==== SA ====
 +
* spec has been updated
 +
 
 +
=== m2084 ===
 +
* [http://mantis.jaguarfreight.com/mantis/view.php?id=2084 2084]
 +
 
 +
==== BA m2084 ====
 +
* Include CT records that the E0 Client is updated since the previous report
 +
* Final version: http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Daily_Report&oldid=10720
 +
==== SA m2084 ====
 +
* Report MUST ALSO include shipment records that the [[CT_bo#E0_Client]] is updated since previous report
 +
* Spec is updated: Y
 +
* Note: For example, If CT# was created for E0.Client Company A, this MUST be showing on the result for Company A, THEN later on this CT# was updated E0. Client Company from A to B, this MUST be showing on the result for Company B
 +
* Final version: [http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Daily_Report&oldid=10729 10729]
 +
 
 +
=== BA m2211 ===
 +
Make CT # as a hyperlink (but if more multiple lines items for CT, do not repeat hyperlink, make only 1st mention of CT a hyperlink)
 +
MOT = Air, Ocean (FCL, LCL) & Truck Ocean/Air, but do no include domestic truck records if possible (?)
 +
MOT = Air - show the chargeable weight, if entered CT record export tab 17b: Chargeable weight, is greater than gross weight CT record content table Ttl GW. If not greater, show gross weight
 +
MOT = Ocean FCL , AW and MLB - show the actual container count per CT record
 +
* IE CT record has container # AAAA 123456-7 & container # BBBB 456789-0, show container count: 2
 +
MOT = Ocean LCL - show the total CBM
 +
Shipper
 +
Consignee
 +
 
 +
=== 0002211: (Daily) For ALL clients need to customize report ===
 +
 
 +
== RFC ==

Current revision as of 22:51, 21 December 2010


Contents

[edit] Parent Mantis

1597

[edit] Business Needs and Requirements

This report is used to identify new shipments (CT's) created in the system since the last time the instance of this report run.

[edit] Technical Specification

[edit] Summary

  • This report should appear only in Saved/Scheduled Reports section (to run this report you have to schedule it).
  • Report MUST include ALL new shipments records that are created since previous report, including undefined MOT
  • Report MUST ALSO include shipment records that the CT_bo#E0_Client is updated since previous report
  • Report shows date time stamp and how many records are found (number)
  • Report is sent to recipients on schedules via email.

[edit] User Interface

See #Figure 1. Schedule dropdown menu

[edit] Filters

All filters for user defined report conditions are on one tab - see #Figure 2. General Tab

Also see #Filters and Filters Matching Logic.

[edit] Hard Coded Conditions

  • This report is used to identify new shipments (CT's) created in the system since the last time the instance of this report run.
  • Report should identify records where the CT_bo#E0_Client was updated since previous report.
  • For example, IF CT# was created for E0.Client Company A, this MUST be showing on the result for Company A
    THEN later on this CT# was updated E0. Client Company from A to B, this MUST be showing on the result for Company B.

[edit] User Defined Conditions

  • See Figure 2 for general filters tab below
  • All filters are "multi select"
  • Check box in front of each filter. If filter is set, check box MUST be automatically checked vise versa
  • Filters consist of several groups
  • Every filter is connected by AND and sub selections inside each filter combo-box are connected by OR
    • For example: ((Client Companies = "a") OR(Client Companies = "b")) AND ((Shippers = "c")OR(Shippers = "d"))
  • If none of the filters are selected, generate ALL results.


[edit] Filters and Filters Matching Logic

[edit] Output

  • Create output tab with list of output headings
  • Required default headings on the combo box, the rest of headings are in drop down filter output list
  • List of headings #Columns Definitions
  • See #Figure 3. Spreadsheet for output result (xls)

[edit] Columns Definitions

  • CT# with link (JG and Non-JG users)
    • Mapped to CT#CT Num
    • If user is client, CT# hyperlinks are link to view shipment page under client interface> shipment detail.
    • If user is internal user(JG users), CT# hyperlinks are linked to view shipment page in Internal interface.
    • Default on the combo box selected list
  • Transportation Modes
  • Shippers
    • Mapped to CT#Shipper
    • Default on the combo box selected heading output list
  • Consignees
    • Mapped to CT#Consignee
    • Default on the combo box selected heading output list
  • Origin Countries
  • Destination Countries
  • P.O.
    • Mapped to Commodity#PO
    • Default on the combo box selected heading output list
  • SKU
    • Mapped to Commodity#SKU
    • Default on the combo box selected heading output list
  • Quantity
    • Mapped to Commodity#Qty
    • Default on the combo box selected heading output list
  • Commodity Item Invoice
    • Mapped to TBD
  • Commodity Item Pieces Per Package
    • Mapped to TBD
  • Commodity Item Package Type
    • Mapped to TBD
  • Custom fields
    • if E0 client has "custom fields" that is/are set in admin mode, these should be available list as well
    • Heading on output tab and spreadsheet should be Extra 1, Extra 2,...etc
  • Authorization
    • No mapping (Heading Only)
    • Default value to blank
    • this field is allowed user to manage it manually [Y/N]
    • Default on the combo box selected heading output list
  • Container Description
  • Master Bill
    • Mapped to: if MOT=AIR: CT#MAWB, if MOT=Ocean: CT#MBL, otherwise leave blank.

[edit] Sort By Definitions

  • Sort by CT# from earlier CT# to newer CT#


[edit] Summary

  • Output is a spreadsheet
  • Spreadsheet tabs: There is ONLY one tab
  • Spreadsheet consists of Header, Body, and footer
  • Header:
    • Report name
    • Date Time Stamp
    • Criteria (list of filter(s) selected)
    • Jaguar Logo
  • Body: (general info)
    • Consists of columns and rows
      • Column: headings
      • Row: shipment records
  • Footer
    • There is no footer

[edit] Special Cases

TBD

[edit] Figures

[edit] Figure 1. Schedule dropdown menu

File:DailyScheduled.JPG

[edit] Figure 2. General Tab

File:GenFilterDaily.JPG

[edit] Figure 3. Spreadsheet

File:DailySpreadSheet.JPG

[edit] History

[edit] Ver 1.1

  • spec updated? No
  • 0001882: (Daily) Add more OUTPUT options: MAWB, container num, Qty, Extra1, Extra2, Extra3 (as in 0001880)
  • see mantis #1880 for details


[edit] m2037

[edit] BA

In Output:

[edit] Part 2

[edit] SA

  • spec has been updated

[edit] m2084

[edit] BA m2084

[edit] SA m2084

  • Report MUST ALSO include shipment records that the CT_bo#E0_Client is updated since previous report
  • Spec is updated: Y
  • Note: For example, If CT# was created for E0.Client Company A, this MUST be showing on the result for Company A, THEN later on this CT# was updated E0. Client Company from A to B, this MUST be showing on the result for Company B
  • Final version: 10729

[edit] BA m2211

Make CT # as a hyperlink (but if more multiple lines items for CT, do not repeat hyperlink, make only 1st mention of CT a hyperlink) MOT = Air, Ocean (FCL, LCL) & Truck Ocean/Air, but do no include domestic truck records if possible (?) MOT = Air - show the chargeable weight, if entered CT record export tab 17b: Chargeable weight, is greater than gross weight CT record content table Ttl GW. If not greater, show gross weight MOT = Ocean FCL , AW and MLB - show the actual container count per CT record

  • IE CT record has container # AAAA 123456-7 & container # BBBB 456789-0, show container count: 2

MOT = Ocean LCL - show the total CBM Shipper Consignee

[edit] 0002211: (Daily) For ALL clients need to customize report

[edit] RFC

Personal tools