File Reference Numbers

From UG

(Difference between revisions)
Jump to: navigation, search
(→Jag Shenzhen file reference sequence)
(→SOW 8)
 
(143 intermediate revisions not shown)
Line 3: Line 3:
== Info ==
== Info ==
-
* parent: 0000100: (Ops Misc)  
+
=== Parent Mantis ===
 +
 
 +
0000100: (Ops Misc)
 +
 
 +
=== Spoke of this wiki ===
 +
 
 +
Automate the assignment of file reference numbers (Jaguar export ref, import ref, third party ref) and their file reference sequence that are assigned to CT records.
== Glossary ==
== Glossary ==
-
== Business Needs ==
+
[[Export_Reference_Number]]
 +
 
 +
[[Import_Reference_Number]]
 +
 
 +
[[Third_Party_Reference_Number]]
 +
 
 +
[[AM]]
 +
 
 +
== SOW 1 ==
 +
 
 +
* '''0003151: (Ops Misc) Have CT2 generate file ref #'s'''
 +
 
 +
=== Requirements ===
 +
==== Gen Tab: Ops functionality ====
 +
* System can auto generate file reference number with ability of Ops user to select some part of Reference#
 +
* System auto assigns the mode of direction (read only) once MOT is selected: [[CT_bo#MOT]] in the CT
 +
* System should allow user to change the MOT after a reference number has been assigned. Once the MOT is updated, the reference number should be auto updated as well according to Account Manager ID, the MOT, and client company and mode of direction
 +
* System should allow user to update the old CT records File Reference Number to the new automated number. The Account Manager ID (group of old CT records) will be set in the admin. Once it is set, user can set the number of account manager ID then the sequence should be automated.
 +
* System should reserve the number X00 for offices and Account Manager ID starts from X01 - X99
 +
:* Reference number consist of 3 parts:
 +
:** List of Account manager ID that is tied to default Office- List of Account Manager IDs for particular office for operator to select.
 +
:*** i.e. Drop down list of account managers ID for a particular office, such as if NY office is reserved "800" then when ops user from NY office manages a particular shipment, this part will provide list ID of Account Managers from NY office only. One Account Manager can have many ID such as:
 +
801 represents Rob
 +
802 represents John
 +
803 represents Jeanine
 +
804 represents Rob
 +
805 represents Rob
 +
Note: 800 represents NY office - this can be set in admin.
 +
Note: Duplicate ID cannot exist in the same office
 +
:*** i.e. If UK office is set to "700", dropdown list of account managers ID for UK office will be:
 +
701 represent Wassila
 +
702 represent Ginny
 +
Note: 700 represents UK office - this can be set in admin
 +
:*** An operator would manually select the “3 digit prefix” to create the reference number, and there should be a ‘link’ in the code restricting that operator’s “options” to his office, ie, if an operator is a JFS New York employee he would only have access to “8xx” sequences.
 +
:** Automate counter - This should consist of 2 parts:
 +
:*** 1) The first 2 number XX can be auto assigned as of irrespective specific client company - this can be managed in admin to be set as a specific client company.
 +
:**** Note: Each irrespective specific client company has it own XX number, this is for the big client companies such as EA, Coty,..etc. And the rest of small client companies will be used the same XX number. So it is definitely this won't go more than 99 set of XX companies. '''REMOVE CLIENT COMPANY XX COMPLETELY'''
 +
:*** 2) Counter - Automated sequence number 5 digit XXXXX. Read only
 +
:**** Contents/value of this sequence depend on office/account manager. Each Account Manager/office has it owns set of sequence and incrementail counter. The counter goes up by one number. I.E. if NY operator selects 801 the counter goes up by one number starts from 801 00001, 801 00002, 801 00003 etc. and once NY operator selects 802 the counter will goes up by one number starts from 802 00001, 802 00002, 802 00003, etc
 +
:**** Sequence cannot be 00000
 +
:** Mode
 +
:*** MOT -> undefined empty Ref# → gap issue
 +
:***  System auto provides default options dropdown once the MODE is selected by operator
 +
:*** The pair of MODE and direction with the MOT can be set in Admin
 +
:**** ie. AE - Air Export - this can be applied to Air shipment for example
 +
:**** ie. SE - Sea/Ocean Export - this can be applied to Ocean shipment for example
 +
* There are 3 reference number:
 +
:* Export Reference #: [[CT_bo#Export_Ref]]
 +
:* Import Reference #: [[CT_bo#Import_Ref]]
 +
:* Jaguar 3rd Reference#: [[CT_bo#Jaguar_3rd_Ref]]
 +
* NOTE: File Reference number is not mandatory field as there are use cases:
 +
:* CT created before setting other fields
 +
:* File Reference number has to be waited to set when operator knows what MOT for the shipment and what type of shipment:
 +
:** Import reference shipment
 +
:** Export reference shipment
 +
:** Jag 3rd reference Shipment
 +
:** Sometimes Jaguar only handles the import part, or export part as some customer handles some part by themselves or Jaguar handles both import and export. Sometimes the 3rd office who does not handle import and/or export get involved for billing which will apply Jaguar 3rd Reference#. So it is not always that all these 3 references will be set but at least one of the field is set!
 +
:
 +
 
 +
 
 +
IE. File Reference Number
 +
801 (3 digit ID code)
 +
10 00001 (Numerical - actual 'Ref' number, 10 is associated with client company A and 00001 is a sequence number
 +
SE (mode/direction - XX )
 +
Example Ref Num will show as: 801 10 00001 SE
 +
 
 +
==== Admin: File Reference # ====
 +
Provide admin table to assign specific reference number to specific offices and to specific Account Manager.
 +
 
 +
Provide ability for each office to set their own sequence numbers and link these to specific Account Manager within that office.
 +
 
 +
Each of office consists of account managers, each account manager can have many account ID. There are 3 account managers in NY office currently: Rob, John and Jeanine. Each account manager has their own team such as Rob has operator A, operator B and operator C work in his team. System does not need to record which staff works for which Account Manager but system should provide ability to add number of head count of staffs as a field in the table. Number of head count should allow .5 as well.
 +
 
 +
Provide ALL managing functionalities included in ONE location separated by tabs for example:
 +
* Tab 1: Table: Set/List office Digit (New York 7, London 5 etc)
 +
* Tab 2: Table: Set/List of Account Managers and Offices (Rob Link NY – Wassila London etc)
 +
* Tab 3: Table: Set/List staff per Account Manager (number to allow . 5)
 +
* Tab 4: Table: Set/List actual file prefixes (801 Rob Link – Description: (text box) … -  802 Jeanine Favia – Description (text) box
 +
 
 +
* Sequence and catagory of file reference number can be set via admin
 +
:* Admin>File Reference#
 +
:* Table to manage File Reference# consist of:
 +
:** Each office: specify by reserved number such as:
 +
:*** NY - 8 and account managers could start from 01 after the office prefix such as 801, 802, 803, 8XX
 +
:*** UK - 7 and account managers could start from 01 after the office prefix such as 701, 702, 703, 7XX
 +
:*** FR - 6 and account managers could start from 01 after the office prefix such as 601, 602, 603, 6XX
 +
:*** HK - 5 and account managers could start from 01 after the office prefix such as 501, 502, 503, 5XX
 +
:*** SZ - 4 and account managers could start from 01 after the office prefix such as 401, 402, 403, 4XX
 +
:*** Note: these numbers are assigned manually to each office in the admin manually
 +
:** Each Account Manager: specify by number - subset of each office sequence number such as Account Managers IDbelow
 +
:*** Rob  - NY - 801
 +
:*** John  - NY - 802
 +
:*** Jeanine - NY - 803
 +
:** Each Account Manager: Account Manager, ID, Description
 +
:** Each Account Manager: provide a field to show how many staff work for each account manager table. Field should allow a half headcount as well such as table below:
 +
:*** Rob - 5
 +
:*** John - 4
 +
:*** Jeanine - 3.5
 +
:** Ability to edit number of staff that work under each Account Manager and keep historical changes
 +
:** Automate a mode/direction for the last part of file reference requence numbers
 +
:*** Mode of direction table:
 +
:**** Export: AE (Air Export), SE (Sea Export), TR(Truck Export)
 +
:**** Import: AI (Air Import), SI (Sea Import), TR(Truck Import)
 +
:**** Jaguar: CT (Cross Trade - Billing office only)
 +
:*** Mode of Transportation should be associate with Mode of direction table such as
 +
:**** Air - AE or AI depend on what field
 +
:**** Ocean (LCL)  - SE or SI depend on what field
 +
:**** Ocean (FCL) - SE or SI depend on what field
 +
:**** Ocean Vendor Console - SE or SI depend on what field
 +
:**** Truck (DT - Dom) - TR
 +
:**** Truck (OT - Ocean Truck) - TR
 +
:**** Truck (AT - Air Truck) - TR
 +
:**** NOTE: This should be default to a correct direction per reference # and MOT such as the field Export Ref#, this part should auto read only default to AE if MOT for Air is selected.
 +
:** Volume Unit per Mode Table - to be used as a report
 +
:*** Air - KG - show as KG
 +
:*** Ocean FCL - TEU or FEU count - show as TEU
 +
:*** Ocean LCL - CBM - show as CBM
 +
:*** Ocean vendor console - TEU or FEU count - show as TEU
 +
:*** Truck Dom DT - Shipment count - SHPT
 +
:*** Truck Ocean -  Shipment count - SHPT
 +
:*** Truck Air -  Shipment count - SHPT
 +
 
 +
:* Report purpose - system should allow file reference numbers to be "counted" going back in time,ideally back to January 01, 2011 Account Manager will manually update the old record with the old file reference to be replaced by the new automated sequence by selecting an account manager that is set to reserve for old records. Also - the "old" reference number (replaced by the 'automated' number) would be included in the "update log" for future reference.
 +
:** i.e. For the past/history sequence, account manager ID will be set differently to separate the current/new shipments so as not to disturb a logical file numbering for current shipments, for example:
 +
John (General Imports)
 +
801 current
 +
891 'old' files
 +
 
 +
==== Volume value per mode ====
 +
Calculation:
 +
* Air(KG): Volume = Sum of [[CT_bo#Chargeable_Weight]]
 +
* Ocean LCL(CBM): Volume = Sum of [[Table B.Total (in cbm)]]
 +
* Ocean FCL(TEU): Volume = Sum of [[T.E.U]]
 +
:* Note: T.E.U ( Twenty Equivalent Unit: 20 ft = 1 Equivalent, 40 ft/hq or 45 ft = 2 TEU). For automatic calculation of TEU should be used values from Container Sizes table: Admin - Transportation - Container Sizes.
 +
* Ocean vendor console (TEU): Volume = sum of [[T.E.U]]
 +
:* Note: Calculation is the same as FCL
 +
* Truck Dom DT (SHPT): Sum of [[Shipment count]]
 +
* Truck Ocean (SHPT): Sum of [[Shipment count]]
 +
* Truck Air (SHPT): Sum of [[Shipment count]]
 +
:* Note: Shipment count = CT count
 +
 
 +
==== Logs functionality ====
 +
All actions should be recorded in CT update log as well as system log
-
=== Core need ===
+
=== UI for Gen Tab ===  
-
Automate the assignment of file reference numbers to CT records (Jaguar export ref, import ref, third party ref), to take the burden off individual operators.  As well by managing this somewhere inside of the CT2 Admin menu, they do not keep a separate log book per office to record these numbers.
+
This is required UI to manage Offices, Account Managers and Ref Nums.
-
== Requirements ==
+
See mock up below.
-
These fields are are found on the CT records Gen tab, see [[CT_bo#References]].
+
Each drop down will show all available combinations of Office/Acc Manager Id plus "Undefined OR None" value.
-
*Have CT automatically generate/create Jaguar's file ref #’s
+
"Undefined OR None" value is default value for all drop downs for new CTs.
-
*Allow user to add new file sequences
+
-
*Add reporting capabilities
+
-
**to report on the file sequence individually or corporately per office 
+
-
*Have some sort of admin ability for each office to set their sequence numbers, with the ability to create new sequences as each office has their own specific sequence. See [[#File_Reference_Number_Sequence_per_Office]]
+
-
'''Note phase I functionality would be for the user themselves to select which file reference sequence to apply to their shipment upon CT creation.  With phase II for CT to auto assign these sequences according to ops user groups.'''
+
'''Data migration note'''
 +
All existing CTs in the system will have all three drop downs set to "Undefined OR None".
 +
  Jaguar operators will set values for some of 2012 CTs.
-
=== File Reference Number Sequence per Office ===
+
'''Implementation Note'''
 +
There will be 2 places in the DB to identify Ref Nums - several related tables and text field in Shipment table that will hold a calculated copy.
-
Each Jaguar office has a sequence of file reference #'s that they use to identify how their office is involved in the shipment, if they are arranging the import or export.  Below are the current file ref sequences according to each office and how they are determined:
+
[[File:Gen Tab with EO IO OO TO.JPG]]
-
==== Jag NY file reference sequence ====
+
'''Implementation Note'''
 +
Red Box A will be done under [[File Reference Numbers]] (by AK) and Red Box B will be done under this SOW by Sasha/Misha.
-
JFS NY – their file ref #'s are per Client Company, which is according TO the operation team that handles that specific client company.
+
== SOW 2 ==
-
 
+
-
*'''Elizabeth Arden Import file ref #'s'''
+
-
**They begin with the number 87
+
-
**Currently they are 5 numbers long
+
-
**They end with a 2 letter code according to the MOT
+
-
***Ocean Imports = SI
+
-
***Air Imports = AI
+
-
****''IE: 87XXXXXSI''
+
-
*'''Elizabeth Arden Export file ref #'s'''
+
* '''0003196: (*OpsRep Misc) Weekly Shpt Counts Global Report (aka Activity DRs) SOW 2'''  
-
**They begin with the number 82
+
-
**Currently they are 5 numbers long
+
-
**They end with a 2 letter code according to the MOT
+
-
***Ocean Exports = SE
+
-
***Air Exports = AE
+
-
****''IE: 87XXXXXSE''
+
-
*'''Elizabeth Arden Domestic Trucking file ref #’s'''
+
=== Report Functionality ===
-
**They begin with the letters DO
+
==== Phase I ====
-
**Currently they are 5 numbers long
+
* A quick simplified version for a weekly report on file reference # per account manager and/or per office with some information from the system.
-
***''IE: DOXXXXX''
+
* This version of the report should be quickly generated by script outside of CyberTrax UI. This is what we normally calls " one time report". This is needed to be executed once a week until we integrate functionality to generate the report from the system (CyberTrax UI)
 +
:* Purpose of generating report: To know how much freight Jaguar Moves, How busy for each team is, Report for Export is the KEY to control the business
 +
:* Crtiteria:
 +
:** This report, file reference number is based on #SOW1. For the old records with manual reference number, please note that the automated file reference will be manually updated by operators for all the last year record until the last record before the new automated reference numbers functionality is in place
 +
:** Weekly starting Date: Consider the date that the file reference number is/was assigned. This can be found from the update log table and check (Export Ref# updated date OR Import Ref# updated date OR Jaguar 3rd Ref# updated date) = Date range selected. Show weekly starting date as of Monday date of each week on the report.
 +
:*** Monday is the starting day of each week such as week of 2-Jan-2012 (which is for the records that one of those reference number have been assigned or updated since 2-Jan- 2012 until 8-Jan- 2012).
 +
:** Week starting date is based on CT that the file reference number has been assigned/ manually updated. This info should be recorded in the updated log. As the file cannot be assigned to a 'team' until a file was created meaning until a reference number assigned to a record.
 +
:*** Option to select weekly starting date:
 +
:**** Date range from Date/month/year to Date/month/year (calendar) to show result of weekly starting date(Monday)
 +
:**** The last 26 weeks from "week starting date selected"
 +
:***** Information report should show data for the last 26 weeks per week. Such as current week 30, report should show data from week 5 to week 30(include).
 +
:* Result:
 +
:** Information requires:
 +
:*** See mock up worksheet per each column below
 +
:*** Office
 +
:*** Account Manager
 +
:*** Week Starting Date
 +
:*** Client Company
 +
:*** Mode/Direction
 +
:*** File count (For example, show how many files count in the system during that particular week is started on 2-Jan-2012 such as this worksheet mock up --> file count = 4 records in the system that associated with unique combination of ( Account Manager = "Wassila Tahrichi" AND Client Company = "Elizabeth Arden" AND Mode/Direction = "SI")
 +
:*** Volume (number accumulated value)
 +
:*** Volume Unit (this should be shown as it is in the setting from Admin for particular mode)
 +
:*** Gross Margin (represent gross margin from P/L tab to accumulated values for all records file count to see how much we make. For this example, it will represent accumulated values for the 4 shipments gross margin) --> Kostya knows how to calculate this, algorithm of Kostya can be used.
 +
:** Group by weekly, office, and Account Manager, Client Company, and Mode/direction in order.
 +
:** Sorting by last recent week to previous week
 +
* Note: It is very important to show all all number including the lines for combinations with file count = 0. Such as week 5, Account Manager = A AND Client Company = Elizabeth Arden AND Mode/Direction = SI --> Result of file count = 0 as of week 5 there is no movement for this particular unique combination.
 +
See example below, also attached to mantis.
-
*'''Hanover Trucking records (Domestic, Air & Ocean) file ref #’s'''
+
* See example below, also attached to mantis.
-
**They begin with the letters HDO
+
-
**Currently they are 3 numbers long
+
-
***''IE: HDOXXX''
+
-
*'''Coty Import file ref #'s'''
+
[[File:S count.JPG]]
-
**They begin with the numbers 89
+
-
**Currently they are 5 numbers long
+
-
**They end with a 2 letter code according to the MOT
+
-
***Ocean Imports = SI
+
-
***Air Imports = AI
+
-
****''IE: 89XXXXXSI''
+
-
*'''All other client companies ''Import'' file #'s'''
+
* See below mapping for volume unit and mode / direction
-
**They begin with 88'''
+
-
**Currently they are 5 numbers long
+
-
**They end with a 2 letter code according to the MOT
+
-
***Ocean Imports = SI
+
-
***Air Imports = AI
+
-
****''IE: 88XXXXXSI''
+
-
*'''Consolidation Import files from other Jaguar office’s'''
+
[[File:S count 2.JPG]]
-
**They begin with 86
+
-
**Currently they are 5 numbers long
+
-
**They end with a 2 letter code according to the MOT
+
-
***Ocean Imports = SI
+
-
***Air Imports = AI
+
-
****''IE: 86XXXXXSI''
+
-
*'''All other Export file #'s, including client company Coty
+
== SOW 3 ==
-
**They begin with 81
+
'''0003432: (*OpsRep Misc) Weekly Shpt Counts Global Report (SOW 3)'''  
-
**Currently they are 5 numbers long
+
-
**They end with a 2 letter code according to the MOT
+
-
***Ocean Exports = SE
+
-
***Air Exports = AE
+
-
****''IE: 81XXXXXSE''
+
-
==== Jag UK file reference sequence ====
+
'''Core requirements:'''
 +
* Make this report part of the system
 +
* add filters
 +
* additional columns?
 +
* dashboards in this SOW or next?
-
JFS UK - their file ref #'s are per mode of transportation, irrespective of the client company.
+
== SOW 4 ==
-
*'''General Air Export'''
+
'''Mantis: 0003576: Add Archive Functionality for Account Manager Ref Sequences # in CT Ref Numbers Settings'''
-
**They begin with 11AE
+
-
**Currently they are 6 numbers long
+
-
**They end with the CT #
+
-
***''IE: 11AEXXXXXX''
+
-
*'''General Sea Import'''
+
'''Core requirements:'''
-
**They begin with 22SI
+
-
**Currently they are 6 numbers long
+
-
**They end with the CT #
+
-
***''IE: 22SIXXXXXX''
+
-
*'''Consolidation'''
+
== SOW 5 ==
-
**They begin with 44CO
+
-
**Currently they are 6 numbers long
+
-
**They end with the CT #
+
-
***''IE: 44COXXXXXX''
+
-
*'''General Sea Export'''
+
'''Mantis: '''
-
**They begin with 12SE
+
-
**Currently they are 6 numbers long
+
-
**They end with the CT #
+
-
***''IE: 12SEXXXXXX''
+
-
*'''General Air Import'''
+
'''Core requirements:'''  
-
**They begin with 21AI
+
-
**Currently they are 6 numbers long
+
-
**They end with the CT #
+
-
***''IE: 21AIXXXXXX''
+
-
==== Jag FR file reference sequence ====
+
==== Phase II ====
 +
* File Reference Numbers should become a part of filtering options for ALL existing reports (account reports, stat reports, ops reports, dashboard reports etc etc etc).
-
JFS FR – their file ref #’s are per mode of transportation as follows, irrespective of the client company.
 
-
*'''AIR EXPORT'''
+
'''Question''': how to identify a list of all (Acc Manager;Client/Mode) combinations?
-
**They begin with AE, then the year and the month
+
-
**Currently they are 7 numbers long
+
-
**They end with 3 numbers
+
-
***''IE: AE1108XXX'' '''Note:''' ''2011 = the year & 08 = the month of August''
+
-
 
+
-
*'''AIR IMPORT'''
+
-
**They begin with AI, then the year and the month
+
-
**Currently they are 7 numbers long
+
-
**They end with 3 numbers
+
-
***''IE: AI1108XXX''
+
-
*'''SEA EXPORT'''
+
== SOW 6 ==
-
**They begin with SE, then the year and the month
+
-
**Currently they are 7 numbers long
+
-
**They end with 3 numbers
+
-
***''IE: SE1108XXX'''
+
-
*'''SEA IMPORT'''
+
Changes:
-
**They begin with SI, then the year and the month
+
-
**Currently they are 7 numbers long
+
-
**They end with 3 numbers
+
-
***''IE: SI1108XXX''
+
-
*'''DOMESTIC TRUCK'''
+
* run report for all records (not only closed)
-
**They begin with RE, then the year and the month
+
* run the report for actual departures in 2012
-
**Currently they are 7 numbers long
+
-
**They end with 3 numbers
+
-
***''IE: RE1108XXX''
+
-
==== Jag HK file reference sequence ====
+
For truck dom - use [[Ct#Actual_Pick_Up_Date]]
-
JFS HK – their file ref #’s, which they call Job numbers, and are per mode of transportation, irrespective of the client company.
+
For air / ocean - use [[Ct#Airport_Of_Departure_Actual_Date]] / [[Ct#Origin_Terminal_Actual_Date]]
-
*'''Air Export'''
+
== SOW 7 ==
-
**They begin with HK for the office, then the year
+
-
**Then the # 1, which defines this as an Air export
+
-
**Currently they are 5 numbers long
+
-
***''IE: HK111XXXXX'' '''Note:''' ''“HK” stands for JFS HK & “11” stands for 2011, then the number 1 for Air exports''
+
-
*'''Air Import'''
+
* run report according to [[#SOW_6]] changes but only for the first half of 2012
-
**They begin with HK for the office, then the year
+
** actual departures from Jan 1, 2012 through June 30, 2012
-
**Then the letter D & the # 2, which defines the shipment as an Air import
+
-
**Currently they are 5 numbers long
+
-
***''IE: HKD112XXXXX''
+
-
*'''Ocean Export'''
+
== SOW 8 ==
-
**They begin with HK for the office, then the year
+
-
**Then the letter D & the # 3, which defines this as an ocean export
+
-
**Currently they are 5 numbers long
+
-
***''IE: HKD113XXXXX''
+
-
*'''Ocean Import'''
+
* '''0003681: (*OpsRep Misc) Weekly Shpt Counts Global Report (SOW 8) Generate new ref # report
-
**They begin with HK for the office, then the year
+
-
**Then the letter D & the # 4, which defines this as an ocean import
+
-
**Currently they are 5 numbers long
+
-
***''IE: HKD114XXXXX''
+
-
==== Jag Shenzhen file reference sequence ====
+
*run the report for actual departures in 2012
-
JFS SZX - their file ref #’s, which they call Job register, are per mode of transportation, irrespective of the client company.
+
*results on xls to include:
 +
**Office
 +
**Account Manager
 +
**Year - evaluated based on shipment departure date
 +
**Month - evaluated based on shipment departure date
 +
**Week starting date - evaluated based on shipment departure date
 +
**Client
 +
**Origin Country
 +
**Origin Region
 +
**Destination Country
 +
**Destination Region
 +
**Mode/Direction
 +
**File count
 +
**Volume
 +
**Volume Unit
 +
**Gross Margin Per Office Columns
 +
**Average GM per Volume
 +
**Total GM
 +
**CT Numbers
-
*'''Air Export'''
+
Also (from 3733):
-
**They begin with SZ for the office, then the year
+
-
**Then the # 1, which defines this as an Air export
+
-
**Currently they are 5 numbers long
+
-
***''IE: SZ111XXXXX'' '''Note:''' ''“SZ” stands for JFS SZX & “11” stands for 2011, then the number 1 for Air exports''
+
-
*Air import
+
* Month (spelled out 3 letters Jan Feb Mar Apr etc.)
-
SZ112xxxxx
+
* Year 2011 – 2012 etc.
 +
* CT Count
-
*Ocean export
+
== History ==
-
SZ113xxxxx
+
-
Ocean import
+
=== 0003151: (Ops Misc) Have CT2 generate file ref #'s ===
-
SZ114xxxxx
+
-
“SZ” stands for JFS SZX & “11” stands for 2011
+
* [http://ct.jaguarfreight.com/mantis/view.php?id=3151 3151]

Current revision as of 15:54, 6 September 2012


Contents

[edit] Info

[edit] Parent Mantis

0000100: (Ops Misc)

[edit] Spoke of this wiki

Automate the assignment of file reference numbers (Jaguar export ref, import ref, third party ref) and their file reference sequence that are assigned to CT records.

[edit] Glossary

Export_Reference_Number

Import_Reference_Number

Third_Party_Reference_Number

AM

[edit] SOW 1

  • 0003151: (Ops Misc) Have CT2 generate file ref #'s

[edit] Requirements

[edit] Gen Tab: Ops functionality

  • System can auto generate file reference number with ability of Ops user to select some part of Reference#
  • System auto assigns the mode of direction (read only) once MOT is selected: CT_bo#MOT in the CT
  • System should allow user to change the MOT after a reference number has been assigned. Once the MOT is updated, the reference number should be auto updated as well according to Account Manager ID, the MOT, and client company and mode of direction
  • System should allow user to update the old CT records File Reference Number to the new automated number. The Account Manager ID (group of old CT records) will be set in the admin. Once it is set, user can set the number of account manager ID then the sequence should be automated.
  • System should reserve the number X00 for offices and Account Manager ID starts from X01 - X99
  • Reference number consist of 3 parts:
    • List of Account manager ID that is tied to default Office- List of Account Manager IDs for particular office for operator to select.
      • i.e. Drop down list of account managers ID for a particular office, such as if NY office is reserved "800" then when ops user from NY office manages a particular shipment, this part will provide list ID of Account Managers from NY office only. One Account Manager can have many ID such as: 
801 represents Rob
802 represents John
803 represents Jeanine
804 represents Rob
805 represents Rob
Note: 800 represents NY office - this can be set in admin.
Note: Duplicate ID cannot exist in the same office
      • i.e. If UK office is set to "700", dropdown list of account managers ID for UK office will be: 
701 represent Wassila
702 represent Ginny 
Note: 700 represents UK office - this can be set in admin
      • An operator would manually select the “3 digit prefix” to create the reference number, and there should be a ‘link’ in the code restricting that operator’s “options” to his office, ie, if an operator is a JFS New York employee he would only have access to “8xx” sequences.
    • Automate counter - This should consist of 2 parts:
      • 1) The first 2 number XX can be auto assigned as of irrespective specific client company - this can be managed in admin to be set as a specific client company.
        • Note: Each irrespective specific client company has it own XX number, this is for the big client companies such as EA, Coty,..etc. And the rest of small client companies will be used the same XX number. So it is definitely this won't go more than 99 set of XX companies. REMOVE CLIENT COMPANY XX COMPLETELY
      • 2) Counter - Automated sequence number 5 digit XXXXX. Read only
        • Contents/value of this sequence depend on office/account manager. Each Account Manager/office has it owns set of sequence and incrementail counter. The counter goes up by one number. I.E. if NY operator selects 801 the counter goes up by one number starts from 801 00001, 801 00002, 801 00003 etc. and once NY operator selects 802 the counter will goes up by one number starts from 802 00001, 802 00002, 802 00003, etc
        • Sequence cannot be 00000
    • Mode
      • MOT -> undefined empty Ref# → gap issue
      • System auto provides default options dropdown once the MODE is selected by operator
      • The pair of MODE and direction with the MOT can be set in Admin
        • ie. AE - Air Export - this can be applied to Air shipment for example
        • ie. SE - Sea/Ocean Export - this can be applied to Ocean shipment for example
  • There are 3 reference number:
  • NOTE: File Reference number is not mandatory field as there are use cases:
  • CT created before setting other fields
  • File Reference number has to be waited to set when operator knows what MOT for the shipment and what type of shipment:
    • Import reference shipment
    • Export reference shipment
    • Jag 3rd reference Shipment
    • Sometimes Jaguar only handles the import part, or export part as some customer handles some part by themselves or Jaguar handles both import and export. Sometimes the 3rd office who does not handle import and/or export get involved for billing which will apply Jaguar 3rd Reference#. So it is not always that all these 3 references will be set but at least one of the field is set! 


IE. File Reference Number
801 (3 digit ID code)
10 00001 (Numerical - actual 'Ref' number, 10 is associated with client company A and 00001 is a sequence number
SE (mode/direction - XX )
Example Ref Num will show as: 801 10 00001 SE

[edit] Admin: File Reference #

Provide admin table to assign specific reference number to specific offices and to specific Account Manager.

Provide ability for each office to set their own sequence numbers and link these to specific Account Manager within that office.

Each of office consists of account managers, each account manager can have many account ID. There are 3 account managers in NY office currently: Rob, John and Jeanine. Each account manager has their own team such as Rob has operator A, operator B and operator C work in his team. System does not need to record which staff works for which Account Manager but system should provide ability to add number of head count of staffs as a field in the table. Number of head count should allow .5 as well.

Provide ALL managing functionalities included in ONE location separated by tabs for example:

  • Tab 1: Table: Set/List office Digit (New York 7, London 5 etc)
  • Tab 2: Table: Set/List of Account Managers and Offices (Rob Link NY – Wassila London etc)
  • Tab 3: Table: Set/List staff per Account Manager (number to allow . 5)
  • Tab 4: Table: Set/List actual file prefixes (801 Rob Link – Description: (text box) … - 802 Jeanine Favia – Description (text) box
  • Sequence and catagory of file reference number can be set via admin
  • Admin>File Reference#
  • Table to manage File Reference# consist of:
    • Each office: specify by reserved number such as:
      • NY - 8 and account managers could start from 01 after the office prefix such as 801, 802, 803, 8XX
      • UK - 7 and account managers could start from 01 after the office prefix such as 701, 702, 703, 7XX
      • FR - 6 and account managers could start from 01 after the office prefix such as 601, 602, 603, 6XX
      • HK - 5 and account managers could start from 01 after the office prefix such as 501, 502, 503, 5XX
      • SZ - 4 and account managers could start from 01 after the office prefix such as 401, 402, 403, 4XX
      • Note: these numbers are assigned manually to each office in the admin manually
    • Each Account Manager: specify by number - subset of each office sequence number such as Account Managers IDbelow
      • Rob - NY - 801
      • John - NY - 802
      • Jeanine - NY - 803
    • Each Account Manager: Account Manager, ID, Description
    • Each Account Manager: provide a field to show how many staff work for each account manager table. Field should allow a half headcount as well such as table below:
      • Rob - 5
      • John - 4
      • Jeanine - 3.5
    • Ability to edit number of staff that work under each Account Manager and keep historical changes
    • Automate a mode/direction for the last part of file reference requence numbers
      • Mode of direction table:
        • Export: AE (Air Export), SE (Sea Export), TR(Truck Export)
        • Import: AI (Air Import), SI (Sea Import), TR(Truck Import)
        • Jaguar: CT (Cross Trade - Billing office only)
      • Mode of Transportation should be associate with Mode of direction table such as
        • Air - AE or AI depend on what field
        • Ocean (LCL) - SE or SI depend on what field
        • Ocean (FCL) - SE or SI depend on what field
        • Ocean Vendor Console - SE or SI depend on what field
        • Truck (DT - Dom) - TR
        • Truck (OT - Ocean Truck) - TR
        • Truck (AT - Air Truck) - TR
        • NOTE: This should be default to a correct direction per reference # and MOT such as the field Export Ref#, this part should auto read only default to AE if MOT for Air is selected.
    • Volume Unit per Mode Table - to be used as a report
      • Air - KG - show as KG
      • Ocean FCL - TEU or FEU count - show as TEU
      • Ocean LCL - CBM - show as CBM
      • Ocean vendor console - TEU or FEU count - show as TEU
      • Truck Dom DT - Shipment count - SHPT
      • Truck Ocean - Shipment count - SHPT
      • Truck Air - Shipment count - SHPT
  • Report purpose - system should allow file reference numbers to be "counted" going back in time,ideally back to January 01, 2011 Account Manager will manually update the old record with the old file reference to be replaced by the new automated sequence by selecting an account manager that is set to reserve for old records. Also - the "old" reference number (replaced by the 'automated' number) would be included in the "update log" for future reference.
    • i.e. For the past/history sequence, account manager ID will be set differently to separate the current/new shipments so as not to disturb a logical file numbering for current shipments, for example: 
John (General Imports)	
801 current 
891 'old' files

[edit] Volume value per mode

Calculation:

  • Note: T.E.U ( Twenty Equivalent Unit: 20 ft = 1 Equivalent, 40 ft/hq or 45 ft = 2 TEU). For automatic calculation of TEU should be used values from Container Sizes table: Admin - Transportation - Container Sizes.
  • Ocean vendor console (TEU): Volume = sum of T.E.U
  • Note: Calculation is the same as FCL
  • Note: Shipment count = CT count

[edit] Logs functionality

All actions should be recorded in CT update log as well as system log

[edit] UI for Gen Tab

This is required UI to manage Offices, Account Managers and Ref Nums.

See mock up below.

Each drop down will show all available combinations of Office/Acc Manager Id plus "Undefined OR None" value.

"Undefined OR None" value is default value for all drop downs for new CTs. 

Data migration note
All existing CTs in the system will have all three drop downs set to "Undefined OR None".
Jaguar operators will set values for some of 2012 CTs.

Implementation Note
There will be 2 places in the DB to identify Ref Nums - several related tables and text field in Shipment table that will hold a calculated copy.

File:Gen Tab with EO IO OO TO.JPG 

Implementation Note 
Red Box A will be done under File Reference Numbers (by AK) and Red Box B will be done under this SOW by Sasha/Misha.

[edit] SOW 2

  • 0003196: (*OpsRep Misc) Weekly Shpt Counts Global Report (aka Activity DRs) SOW 2

[edit] Report Functionality

[edit] Phase I

  • A quick simplified version for a weekly report on file reference # per account manager and/or per office with some information from the system.
  • This version of the report should be quickly generated by script outside of CyberTrax UI. This is what we normally calls " one time report". This is needed to be executed once a week until we integrate functionality to generate the report from the system (CyberTrax UI)
  • Purpose of generating report: To know how much freight Jaguar Moves, How busy for each team is, Report for Export is the KEY to control the business
  • Crtiteria:
    • This report, file reference number is based on #SOW1. For the old records with manual reference number, please note that the automated file reference will be manually updated by operators for all the last year record until the last record before the new automated reference numbers functionality is in place
    • Weekly starting Date: Consider the date that the file reference number is/was assigned. This can be found from the update log table and check (Export Ref# updated date OR Import Ref# updated date OR Jaguar 3rd Ref# updated date) = Date range selected. Show weekly starting date as of Monday date of each week on the report.
      • Monday is the starting day of each week such as week of 2-Jan-2012 (which is for the records that one of those reference number have been assigned or updated since 2-Jan- 2012 until 8-Jan- 2012).
    • Week starting date is based on CT that the file reference number has been assigned/ manually updated. This info should be recorded in the updated log. As the file cannot be assigned to a 'team' until a file was created meaning until a reference number assigned to a record.
      • Option to select weekly starting date:
        • Date range from Date/month/year to Date/month/year (calendar) to show result of weekly starting date(Monday)
        • The last 26 weeks from "week starting date selected"
          • Information report should show data for the last 26 weeks per week. Such as current week 30, report should show data from week 5 to week 30(include).
  • Result:
    • Information requires:
      • See mock up worksheet per each column below
      • Office
      • Account Manager
      • Week Starting Date
      • Client Company
      • Mode/Direction
      • File count (For example, show how many files count in the system during that particular week is started on 2-Jan-2012 such as this worksheet mock up --> file count = 4 records in the system that associated with unique combination of ( Account Manager = "Wassila Tahrichi" AND Client Company = "Elizabeth Arden" AND Mode/Direction = "SI")
      • Volume (number accumulated value)
      • Volume Unit (this should be shown as it is in the setting from Admin for particular mode)
      • Gross Margin (represent gross margin from P/L tab to accumulated values for all records file count to see how much we make. For this example, it will represent accumulated values for the 4 shipments gross margin) --> Kostya knows how to calculate this, algorithm of Kostya can be used.
    • Group by weekly, office, and Account Manager, Client Company, and Mode/direction in order.
    • Sorting by last recent week to previous week
  • Note: It is very important to show all all number including the lines for combinations with file count = 0. Such as week 5, Account Manager = A AND Client Company = Elizabeth Arden AND Mode/Direction = SI --> Result of file count = 0 as of week 5 there is no movement for this particular unique combination.

See example below, also attached to mantis.

  • See example below, also attached to mantis.

File:S count.JPG

  • See below mapping for volume unit and mode / direction

File:S count 2.JPG

[edit] SOW 3

0003432: (*OpsRep Misc) Weekly Shpt Counts Global Report (SOW 3)

Core requirements:

  • Make this report part of the system
  • add filters
  • additional columns?
  • dashboards in this SOW or next?

[edit] SOW 4

Mantis: 0003576: Add Archive Functionality for Account Manager Ref Sequences # in CT Ref Numbers Settings

Core requirements:

[edit] SOW 5

Mantis:

Core requirements:

[edit] Phase II

  • File Reference Numbers should become a part of filtering options for ALL existing reports (account reports, stat reports, ops reports, dashboard reports etc etc etc).


Question: how to identify a list of all (Acc Manager;Client/Mode) combinations?

[edit] SOW 6

Changes:

  • run report for all records (not only closed)
  • run the report for actual departures in 2012

For truck dom - use Ct#Actual_Pick_Up_Date

For air / ocean - use Ct#Airport_Of_Departure_Actual_Date / Ct#Origin_Terminal_Actual_Date

[edit] SOW 7

  • run report according to #SOW_6 changes but only for the first half of 2012
    • actual departures from Jan 1, 2012 through June 30, 2012

[edit] SOW 8

  • 0003681: (*OpsRep Misc) Weekly Shpt Counts Global Report (SOW 8) Generate new ref # report
  • run the report for actual departures in 2012
  • results on xls to include:
    • Office
    • Account Manager
    • Year - evaluated based on shipment departure date
    • Month - evaluated based on shipment departure date
    • Week starting date - evaluated based on shipment departure date
    • Client
    • Origin Country
    • Origin Region
    • Destination Country
    • Destination Region
    • Mode/Direction
    • File count
    • Volume
    • Volume Unit
    • Gross Margin Per Office Columns
    • Average GM per Volume
    • Total GM
    • CT Numbers

Also (from 3733):

  • Month (spelled out 3 letters Jan Feb Mar Apr etc.)
  • Year 2011 – 2012 etc.
  • CT Count

[edit] History

[edit] 0003151: (Ops Misc) Have CT2 generate file ref #'s

Retrieved from "https://wiki.jaguarfreight.com/File_Reference_Numbers"
Personal tools