GMS

From UG

(Difference between revisions)

Jump to: navigation, search

Current revision as of 05:37, 3 August 2013

[edit] Info

[edit] Mantis

0003321 [Profit Sharing Module]

[edit] Scope

Covers this one module - Profit Sharing Module. (aka GM Split)

[edit] User Guide

GM Split USER GUIDE

[edit] Glossary

[edit] Business Requirements 1

[edit] Intro

The purpose of this project is:

to automate distribution of Gross Margin across Jaguar Freight offices
to add open / close functionality to CT, Master, Group records

As a by product:

Viewers for CT, Master, Group and additional Reports has been created
number of minor changes has been introduced to Accounting and Ops modules

[edit] General Requirements

A “Gross Margin split” is to be implemented between all offices in 2012.

For 2012, the Gross Margin split will follow the suggested rules listed below.

Please keep in mind, every client will be assigned an single “Owner Office”. This office is responsible for selling to and maintaining the relationship with the client and will receive benefits from the related sales through the Gross Margin split.

It is expected that CyberTrax will automatically generate the needed internal invoices which will distribute the Gross Margin to the correct office.

Files will automatically be closed down daily with set dates, after the actual date of departure, according to the mode of shipment as outlined in the table below:

However, functionality will also be provided so CT records can be closed down manually ahead of the ‘automated closing date’ and this is strongly encouraged.

Files can be opened after their system close date for changes by and approved group of users (management or super users), but it is expected this will be the exception.

If a file is “re-opened” after “closing”, it will need to be “closed” again – at which point a recalculation of the GM Margin Split will need to be processed again.

Ability to report on II’s issued automatically will be required, average time it takes for files to be closed, how many files are “re-opened” after closing etc.

[edit] Systems Requirements

[edit] Admin Section

1. Client Company to JFS Office link “Owner Office”

2. GM Split Models and Percentages table

3. Automated File Closing table (mode, days)

4. Manual file “lock” and “unlock” subject to user type access rights

[edit] Accounting Section

1. Ability to view / search automated invoices (report to see II’s only? Multiple lines on reports to see CT level details?

2. Scope requirements for a Gross Margin Split report (I believe we ALREADY HAVE a Gross Margin Split Report)

[edit] Automated Invoices

1. Generate automated “detail sheet” showing CT/Record transactional info

Ideally One Invoice between two offices, per day. Attached to that invoice is a ‘detailed report’ for what CT’s are included in the report.

[edit] Ops

1. Manual “lock file” feature, “unlock file” feature for selected users

2. Show II in P&L

[edit] KPI type reporting

Could we include a KPI type reporting in the Admin Tables?

For example:

Average time to lock files (per mode, office, team, client) with trending information
Average files ‘unlocked’ after ‘locked’ – weekly (per mode, office, team, client) w/trending

The idea is to keep KPI’s where they belong rather than clustering them (where they are not “critical”)

[edit] Calculation Models: Definitions

OO = Owner Office (Link/Table to be created)

EO = Export Office (identified by “export reference number”)

IO = Import Office (identified by “import reference number”

TO = Third party office (identified by “third party reference number”)

There is a Minimum of 1 “reference number” in a record, with a Maximum of 3 “reference numbers” in Cybertrax.

Reference Numbers: Found on General Tab of CT record (A, B, C)

The OO almost always plays one of the other roles (EO, IO, TO).\

Reference numbers are being automated (Tira is the SA for this task)

[edit] Calculation Models: RULES

“Handled by OO” means the OO is either the EO or IO If the “OO” is the “Third Party Reference” the OO did not “handle” the shipment.

[edit] X/Y rule

If the shipment is handled between two offices and one is the OO;

X% going to the OO (IO, or EO) and

Y% going to the other (non OO) office (IO, or EO)

No TO office under this rule

Example: 60/40

[edit] X/X/Y rule

If a shipment is handled by an OO and a sister office is noted as the import or export office on the record with a third office handling other details,

X% of GM to the OO (IO, or EO);

X% to the handling office listed on the record and (IO, or EO – non OO)

Y% to the 3rd office involved (TO, non OO)

TO cannot be = to OO under this rule

Example: 45/45/10

[edit] X/Y/Y rule

If a shipment is not handled by an OO and two other office are listed as the export and import offices,

X% of GM to the OO (TO)

Y% to each handling office (IO and EO)

TO must be = to OO under this rule

Example: 20/40/40

[edit] X/Y rule 2

If a shipment is handled by only one office but is not owned by that office,

X% of GM is attributed to the OO

Y% to the handling office (IO, EO)

No TO Office under this rule

Only 1 office on the record (cannot be the OO)\

Example: 20/80

[edit] 100 rule

If a shipment is handled and owned by only one office, the only office listed in the ‘ref’ numbers is also the OO

100% GM goes to the OO

Only 1 office on the record (must be the OO)

[edit] X/Y/Z rule

Example: 10 / 40 / 50

X% of GM is attributed to the OO

Y% to the handling office (EO)

Z% to the handling office (IO)

No TO Office under this rule

[edit] Closing dates

Files will automatically be closed down daily with set dates, after the actual date of departure, according to the mode of shipment as outlined in the table below:

Air - 35 days

FCL - 60 days

LCL - 70 days

Truck - 35 days

The above need to be editable by ‘management’ with a record of updates.

Files can be opened after their system close date for changes by and approved group of users, but it is expected this will be the exception.

If a file is “closed” and then “opened” again – it needs to be “closed” (manually -> Action required e-mail? Something else? How do we identify a file that was “opened” and not “closed” again??).

When a file is “closed again” the file needs to be included in the next automated “GM Margin” calculation and invoicing.

Questions: File “closing” -> What updates should be allowed? Comments, Internal Comments? What else?

[edit] All in one place

Marc: Can we have all related functionality in one place in the system - admin, reports, etc.

[edit] OC Functionality

[edit] OC status

At some point in the life of a shipment record it will not be updated anymore. Moreover some updates would become harmful. Therefore, certain types of functionality should be blocked for "old/mature" records. This would prevent undesirable situations such as:

altering shipment weight after invoices has been issued
issuing GMS invoice before all PI/SI are in
etc

Record that have limited functionality as far as updates/etc is called "closed" and should have status "closed". Otherwise it is "open".

We call this status "OC status" - (Open/Closed) status.

[edit] Closing Time Frame

We need a table in the system that defines how many days shipment should stay open since it was created.

For every MOT this number is different.

Typical values:

Air - 35 days
FCL - 60 days
LCL - 70 days
Truck (all 3 sub-modes) - 35 days

Number of days is editable field.

Set default as 1000.

Add this to User Access table.

[edit] OC Status Values

n/a

for CTs closed before 2012

[edit] Setting OC status automatically

When new shipment is created its status is automatically "open".

[edit] Closed CT condition

When the number of days between Actual Date Of Departure Arrival and today's day becomes equal to 
#Closing Time Frame CT is said to satisfy "closed" condition.

[edit] OC process

OC Process is a process that runs periodically and updates "OC status" and "GMS status":
- it checks on all "open" CTs to see if they can be set as "closed but not delivered" or "closed and delivered"
- it checks on all "closed but not delivered" and see if now they can be set as "closed and delivered"
- also if it is changing CT status to "closed and delivered" then it will also set GMS status to "pending" from "not ready"

Currently it is defined to run once a day (defined in Admin)

[edit] Setting OC status manually

We need to provide functionality to manually:

set status to "closed" on open and manually open CTs
set status to "mannually open" on closed CTs (user who opened CT manually must close CT manually)

This is subject to user access permissions.

Suggested implementation: add "Close/Open file" button to P/L Tab.

[edit] Closed CT have different permissions

CT (file) that has "closed" status should limit what operators can do with record.

See #Viewer for CT, Master and Group

[edit] Setting OC status by script

See new criteria here: #SOW 8 Change criteria for NA OC status

Tech Note
--------
To achieve above we need to write and run appropriate scripts prior Release !

[edit] Display OC status on Gen Tab

[edit] Opening and Closing shipments when Groups and Masters are involved

In general, we want to have Master or Group to be consistent in terms of open/close status. It means:

Master/Group has open status if and only if 
       every CT inside of that Master/Group has open status.
Master/Group has closed status if and only if 
       every CT inside of that Master/Group has closed status.

Closing

CT that is a part of a Group or/and of the Master can be closed only when ALL CTs in that Group or Master can be closed based on the individual time frames.

In case of automatic closing System would have to wait for all CTs to "mature" in terms of timeframes.

In case of manual closing System should give a warning to the user that all CTs (in related M/GRP) are going to be closed.

Opening

In case of manual opening System should give a warning to the user that all CTs (in related M/GRP) would be opened.

[edit] Viewer for CT, Master and Group

should mimic look and feel/layout for editors
block all functionality that would affect GM Split (editing records, adding CTs to Master, etc)

[edit] Interface to manually "open" and "close" CTs

"Open/close" button on P/L tab
This is subject to access table
Log who/when opened/closed

[edit] Block altering of the "closed" CT/Master/Group

For users who have no ability to open manually system should display error explaining why edit is not possible and suggest "view" instead.

[edit] DR report to show all manually opened CTs in the system

Lev 1: counter - how many CTs have status "manually" open
Lev 2: xls (if no extra time); sortable HTML with xls option (if enough time); Columns:
- CT# (hyperlink)
- Client
- who manually opened
- when manually opened

[edit] DR report for closed files without a delivery status

This report should show all CTs that have #OC_Status_Values = closed but not delivered

Lev 1: counter
Lev 2: xls (if no extra time); sortable HTML with xls option (if enough time); Columns:
- CT# (hyperlink)
- Client
- Days since closed

[edit] GMS functionality

[edit] Prerequisites for GMS calculations

In order to calculate GMS Split we need the following to be defined for the record:

OO
EO
IO
TO
GMS ratio
GMS schedule

[edit] Owners Office

Owners Office (OO) is a field (attribute) that identify what Jaguar office is the OO for particular Client Company - see Client Companies.

Relationship is one to one.

Add this to "Part A." section on UI.

Make sure changes are posted into Log.

[edit] Import, Export and Third Offices

EO, IO, TO are properties of CT and set on Gen Tab. See Also: RefNums

[edit] GMS section in Admin

Add number of configuration options - see below.

Add under Acc > GM Split.

[edit] GM Split Rules

See table below.

%-ages must be stored in the system.

Need a view of current %s.

User could edit %s.

One table is for default values and one for Client Company specific are required.

Add this as a property into User Access table.

Add changes to log.

[edit] One more GMS case

Business requested one more case see case#6 above.

Example:
00      =       NY
EO     =       HK
IO      =       UK
TO     =       none

[edit] GM ratio

More than one "GM ratio" (“percentage model”) could be applicable - more than one table as in Fig above could be defined.

Each case should have a name (optional, need textbox for that) and id generated by the system.

We should have one default "GM ratio", and then a number of alternative "GM ratio" (same scenarios, just different percentages). By default a Client Company would be subject to the ‘default’ model.

It means that "GM ratio" is a new property for Client Company entity.

Make sure changes are posted into Log.

[edit] Additional Description column

Add extra column to GM split table.

Call it Description.

Make it configurable in config file (so we do not have to re-release to edit).

Pull them from GM_Split_Requirements#Calculation_Models:_RULES.

See mock up below.

[edit] Schedule for GM Split calculation

For example it would be possible to say that GM Split calculation will happen once a day at 17:00 EST

[edit] Credit to the office

Per Karen this is no longer needed

[edit] Limitations on pending GMS and closed files

Not required at least for this release

[edit] Log for GMS Admin Changes

Post all changes into Log.

[edit] Access Rights

Have only one item in User Access Rights table regulating GM Split Admin (all tabs).

[edit] GMS status

GMS (Global Marging Split) Statuses:

n/a

-GMS status for CTs that have OC status "closed before 2012". Need script - see below.

not ready

- default status for all new CTs

pending

- these CTs will be included into the next run of GM Splitter

- this status is set by #OC Process

success

- after system runs Global Margin Splitter on this CT system successfully must change status to "completed"

- set by GM Splitter

error: <reason>

- after system runs Global Margin Splitter on this CT system unsuccessfully; :- examples of <error>:

CT does not fall under any of GMS case;

closed but not delivered -CTs closed by user or system (satisfy #Closed CT condition) but do NOT satisfy "delivered CT" condition - see List_CTs#DELIVERED

-set by GM Splitter

[edit] Setting GM status by script

All CTs that would close prior to Jan 1, 2012 (if this functionality would be available since the beginning) should have GM status "n/a"

Tech Note
--------
To achieve above we need to write and run appropriate scripts prior Release !

[edit] GMS Splitter Process

This process runs periodically for example once a day.

Exact schedule would be defined here: #Schedule for GM Split calculation.

[edit] Forcing GMS Splitter to run immediately

This functionality is provided by button in a GMS Admin.

After process finished running it should provide report in pop-up describing:

how many and what CTs were processed
how many and what Consolidated Invoices were generated

Ideally post this into Log as well.

[edit] Steps for GMS Splitter

Look at all CTs with GMS status = "pending"

See if CT info is complete/correct:
- OO is defined
- OO/IO/EO/TO combination falls under cases defined in GMS Admin

If info is complete/correct then:
- calculate GM Split - see #GMS Algorithm
- issue invoices - see also #Invoices generated by GM Splitter
- post results into DB
- set GMS status = "success"

Else set GMS status = "error"

[edit] GMS Algorithm

For every CT (subject to this algorithm):
- identify what GM case it belongs to (based on OO, IO, EO, TO values)
- calculate based on % defined in #GMS Section in Admin

Note: take into account previously issued internal invoices.

[edit] Example 1a

create: CT: Air, OO=NY, EO=NY, IO=HK, TO=none, with GMS case#: Case 1: (OO : US = 60%, A : HK = 40%)
set: NY issued/received 100 USD to Client
set: NY paid/posted 90 USD from Vendor
set: delivery date
close CT
verify: GMS status is set to: pending
run: GM Splitter
verify: P/L tab should show profits: 10USD for Jaguar, 6 for NY and 4 for HK
verify: Inv Tab should have: Indiv Inv for 4USD from HK to NY
verify: Consolidated Internal Invoices Report should have new invoice for 4USD from HK to NY

dev test: CT# 375464, AIR, AlexTest (ClientCompany)

[edit] Example 1b

For CT in 1a:

open CT
post 1USD purchase for NY
close
run splitter
verify: NY billed HK for 0.40 USD
verify new P/L

[edit] Example 1c

open CT
change GMS % to 50/50
close
run splitter
verify: HK billed NY for 0.90 USD
verify new P/L

[edit] Example 2

create CT with GMS Case 5: (OO : US = 100%)
close/deliver
run splitter
verify: no invoices has been generated

[edit] Example 3

[edit] Example 4

[edit] Invoices generated by GM Splitter

[edit] Number of invoices

There should be limited number of invoices issued each time Runner runs. It corresponds to num of unique pairs from the list of offices. In 2012 it would be:

NY/HK (in this case we would have one invoice: from NY to HK or from HK to NY)
NY/Lon
NY/Par
HK/Lon
HK/Par
Lon/Par

So every time it runs between 0 and 6 Consolidated Invoices would be generated.

Implementation Note:
------------------
Currently system often generates 2 invoices for each pair of offices.

[edit] Two types of Internal Invoices

System would continue registering and generating old types of Individual Internal Invoices (per CT or Group).

They would be generated automatically by the System.

Layout would stay the same.

Old functionality related to these invoices would be preserved (Inv Tab, P/L Tab, SearchInvoices, etc).

At the same time new Consolidated Invoices will be generated as well (by GMS Splitter).

[edit] Invoice numbering

New consolidated invoice should follow "old" sequence (as defined in Acc_CT_Tabs_Sales_or_Internal )

Individual Internal Invoices (per CT or Group) would follow this pattern:

<Related Consolidated Invoice>"-"<CT#>

Consolidated Invoices pattern:

"IIGMS"<office code><sequence>

Example:

Assume GM Splitter discovered only 2 CTs that are pending GMS.

for CT#555 office NY owes 10USD to office HK and
for CT#888 office HK owes 20USD to office NY

System would register:

one Consolidated Invoice IIGMSNY12345 from NY to HK for 10USD.
and two Individual Invoices:
- IIGMSHK12345-555 from HK to NY for 10USD
- IIGMSNY12345-888 from NY to HK for 20USD

[edit] Timing

Both Individual and consolidated invoices will be generated at the same time.

[edit] Consolidated Invoice Layout

See mock up below.

[edit] Change to pdf layout

Sec A: post total amount / currency there

Sec B: this section should have table with the following columns:

   CT# - with hyperlink 
   Client Company (Added!!!) 
   GMScase - number and % 
   OO 
   EO 
   IO 
   TO 
   JFS - USD P/L for JaguarFreight 
   US - USD P/L for this office 
   HK - USD P/L for this office 
   UK - USD P/L for this office 
   FR - USD P/L for this office

NOTE:

Please note that this is a subset of a report defined in #CTs Processed by GM Splitter report

See updated pdf above.

Implem Note:
-----------
This layout has changed again. See SOW 20.

[edit] GM Splitter Log

This was estimated to be done in one workday.

This Log will be located on additional tab of "Gross Margin Split Admin"
It will be in the form of a table
Table will show latest runs at the top
Table will have columns (left to right):

Timestamp - date/time when process completed ()

User - user who run it (or say "schedule" if it was called by a scheduler)

Success - number of CTs successfully processed

Error - number of CTs that were rejected by this process

Consol II - list of consol invoice numbers generated (with a hyperlink)

CTs - link to "CTs Processed by GM Splitter" xls report - see below.

[edit] CTs Processed by GM Splitter report

This report provides detailed info about CTs processed by GM Splitter.
Sorted by "Status" and then by "CT#"
Columns:

Status - success or error

Reason - reason for error

CT# - with hyperlink

Client Company (Added!!!)

GMScase - number and %

OO

EO

IO

TO

JF - USD P/L for JaguarFreight

US - USD P/L for this office

HK - USD P/L for this office

UK - USD P/L for this office

FR - USD P/L for this office

Cons Inv1 - Cons Inv Num 1

Cons Inv2 - Cons Inv Num 2

II1 from - from office for Indiv Inv #1

II1 to - to office for Indiv Inv #1

II1 USD - amount for Indiv Inv #1

II2 from - from office for Indiv Inv #2

II2 to - to office for Indiv Inv #2

II2 USD - amount for Indiv Inv #2

[edit] Group case

Add GRP# column
For Individual CTs this report will have one line with "n/a" in GRP# column
For Grouped CTs:
- this report will have one line with GRP# in GRP# column
- CT# column will show "lowest CT number" in the group

Implem Note:
------------
Report above is altered - see SOW 21.

[edit] CT Tabs

[edit] Inv Tab

No change.

[edit] P/L Tab

Add link to Consol Inv. See below.

[edit] Existing Acc Reports

We are changing the way we handle Internal Invoices. This could affect or even break some of the existing reports such as PnL Report for Multiple CTs (summary and detailed view).

[edit] Search Inv Report

[edit] P n L for Multiple CTs

[edit] Browser for Consolidated Internal Invoices

This module should provide basic capabilities to browse Consolidated Internal Invoices issued by the system.

We need at least core filters:

issuing date: from/to
issuing office

Paging and sorting are required.

Implem Note:
-----------
Implemented as additional option to "Search Inv" report.

[edit] GMS Misc

[edit] Display OO, EO, IO, GMS Status, GMS Case on Gen Tab

See mock up below, Red Box B.

[edit] UC: How to apply new GMS split rules to GMS completed CTs

Spec change
----------
See SOW 3

This is the case when we want to change GM Split %ages and apply them not only in the future GMS runs but also recalculate for CTs that have GMS status completed.

This can be achieved by changing OC status for such group of CTs from closed to open to closed. This will force GMS status to be "pending" and next time GMS Runner runs it will re-adjust GMS for these CTs.

This can be done just by user - no programming required.

[edit] Create DR report to identify all new shipments without an Owner Office

0003490: [GM Split ] Create DR report to identify all new shipments without an Owner Office

[edit] Info for E0 estimate

Core need: To identify early CTs that have Client Companies that do not have Owners Office set

Level one: counter
Level two: xls with 2 columns - CT#(with link) and Client Company

[edit] UC: CTs have different sets of IO EO TO

System must not allow groups in which 2 CTs have different sets of IO/EO/TO

[edit] UC: some CTs are pending GMS and some are not ready

UC: some CTs are "pending" GMS, some are "not ready"

System should wait until all CTs in the group are ready (they could be delivered at different times).

[edit] Change Requests

[edit] SOW1 VAT should be excluded

0003509 [GM Split ] VAT should be exluded from a) GM total; b) GM Split

Exclude VAT from P/L amount. Implications:

1) Exclude VAT from P/L on P/L tabs.

Consider VAT as only value entered on a sales side.

VAT related charge codes is something else - include them into P/L.

See mock up below:

2) Exclude VAT from P/L amount calculated in GM Split

3) Exclude VAT from P/L amount on P/L reports.

Apply same idea as in 1 above - do not add any columns just post amounts without VAT and add related Note:

* VAT has been excluded from all amounts

See mock ups below.

[edit] SOW2 New business rules for currency conversion

0003510[GM Split ] New business rules for currency conversion

When converting amounts to another currency (for P/L and GM Split!) use exchange rate available for date = "departure date" for CT.

In case of groups use this date from any CT. We believe that this date is the same for all CTs in the group since group is always a part of the Master.

[edit] Add Exchange rate on this report is based on XX notes

Since for some reports exchange rate will be based on "invoice created on" date (Search Invoices) and for others be based on "CT departure" date (P/L) we need appropriate note on screen or xls:

NOTE: exchange rate is based on "invoice created on" date
NOTE: exchange rate is based on "CT departure" date

[edit] SOW3 Recalculate exiting records in case ratios has changed

0003511[GM Split ] Add option to GM Split Rules tab to re-calculate exiting records in case ratios or OO has changed

Add option to GM Split Rules tab to recalculate exiting records in case ratios has changed

Do it for both client specific and global rules

Add "Recalculate CTs with departure date from" date - this is an option to consider when re-calculating. Place it on the tab. Required field.
- default is from tomorrow to blank
- for mapping - see Revenue Recognition Date

On change prompt user: New GMS ratios will be applied to all CTs with departure date from XXX to YYY next time GM Splitter runs. Apply changes? Y/N.

Recalculate during next GMS run.

Apply similar procedure if OO has changed.

See mock ups below. Use same or equivalent UIs (say you could re-use same pop-up in 3 cases).

[edit] SOW4 DR to show all CTs that have GMS status error with a reason

0003517[GM Split ] Create DR to show all CTs that have GMS status = error with a reason

level 1: counter
level 2: xls with columns: CT#, Client company, error reason

[edit] SOW5 Add new error reason/type: old RefNum

0003527 [GM Split ] Add new error type: “non-automated” Ref number is included in one of the REF fields

Add new error reason/type: “non-automated” Ref number is included in one of the REF field

This happens in CTs that are "not fixed" by new Ref Num interface.

See example below:

[edit] SOW6 Re think exg rates logic again

0003534: [GM Split ] UC: Actual departure date is not defined and as a result exchange rates are not defined for issued invoices

Add the following NOTE into header of all P/L reports and P/L tab:

NOTE:  Exchange rates are only defined (and used for Invoices) for CTs with Actual Departure Date defined

Add WARNING to the CT P/L tab if Actual Departure Date is not defined:

WARNING!:  Actual Departure Date is not defined therefore Exchange rates for invoices are not defined.

[edit] SOW 8 Change criteria for NA OC status

0003537: [GM Split ] SOW 8 Change criteria for OC status = n/a to actual departure date<=2012

New Rule:

All CTs that have Actual departure date 
OR 
Actual Delivery date < 2012 year
should have OC status "n/a".

[edit] SOW 9 Re-organize and rename DRs related to GM Split

M#0003538 [GM Split ] SOW 9 Re-organize/rename DRs related to GM Split

1 Remove "Profit Sharing Panel" from system and users profile (this was old way of doing GMS Reps)
2
3 Re-name "Shipments by Status" Report class into "GM Split"

[edit] Report format redesign

4 Name of the xls file for this report report should consist of:
- <Report Class>-<Report Sub-class>-<Description>-<Generated on date/time>.xls
- Example: GM Split Reports-Closed but not Delivered-My GMS reports for Coty-09-April-2012 1242 PM EDT.xls
5 Header of the report should look like this:

Report Class: GM Split  
Report Sub-class: Closed but not Delivered
Description: My GMS reports for Coty
Created by: Roman Lahno	
Generated on date/time: 09-April-2012 12:42 PM EDT		 		
Shipments on the report: 566 shipment(s)

6 On level one it should have additional column "Report Class/Sub-class:" before "Description" column

[edit] Saved Scheduled Reports page redesign

7 "Saved/Scheduled Reports" page (/Index.zul#reportsScheduled):
- rename column "Reports class" into "Report class/sub-class"
- post there: [Class name] / [sub-class name]
- Add one column "Report Category"; should be first column
- sort this table by 1st then by 2nd column

[edit] Report category concept and multiple DB panels for groups of DRs

8 We will have one DR Panel per "Report Category"! "Report Category" is:
- a short list that rarely changes
- hard coded
- defined already in the system - see Main Menu > Reports
- items we have so far:
  - Operational
  - Statistical
- new items to be added:
  - Acc (all GM Split DRs; for Billing Required, etc)

[edit] SOW 10 The reference numbers should be an automatic group save

0003539: [GM Split ] The reference numbers should be an “automatic group save”

Goal: to manage and assign same Ref Nums / related values to all CTs in the group

1 In CT editor for grouped CT make checkbox read-only for 3 dropdowns managing ref nums. See Fig. below.
2 If value(s) above have changed and user clicks "Save" (Save for individual CT) then produce pop-up that:
- gives warning - see below
- gives choice "OK", "Cancel"

Even though you selected "individual" Save system will apply the following changes to all CTs 
in the group:
 Export Acc Manager/Office changed from ... to ...
 Export Ref# changed from ... to ...
 .....
 .....

[edit] SOW 11 Create a script to identify GRPs with CTs that have not identical RefNums info

M#0003541

EI, IO, TO must be same for all CTs in the GRP.

After 0003539 is implemented it will be but until that we must use this script to collect corrupted records.

[edit] SOW 12 Rethink exg rates logic

0003534: [GM Split ] Rethink exg rates logic.

This is a new logic that we want to use:

see Exchange_Rates#v2

[edit] SOW 12a Use actual ExR with the new logic for GMS

see Purchase_Invoices#SOW_1_Change_exchange_rate_logic

[edit] SOW 13 Additional 3 cases for RefNums in case of GRPs

0003547: [GM Split ] Additional 3 cases for RefNums in case of GRPs

To remind, all CTs in the GRP must have same RefNum settings.

Therefore we must change the way system handles the following cases:

Creating GRP
- provide interface to allow user to chose between two options:
  - inherit RefNum settings from one of the CTs
  - select new RefNum values and apply them to all CTs in the group
  - in both cases above "limit selection related to logged user's office"

Adding CT to GRP
- This CT must acquire CT RefNum settings from GRP
- Provide warning to the user (pop-up with options "OK" and "Cancel")

In addition please also change logic for this case:

Removing CT from GRP
- set RefNum settings of this CT to undefined
- Provide warning to the user (pop-up with options "OK" and "Cancel")

[edit] SOW 14 Add conditions to DR reports

For all GM Split DR reports apply these changes:

exclude canceled, deleted CTs from report
add filter "CT Created on date (from/to)"

[edit] SOW 15 Replace combobox with listbox

0003556: [GM Split ] bug/change: Replace combobox with listbox(dropdown) for "Office / Account Manager" field; NullPointerException

This is to eliminate ability by user to erase value in combobox so that it shows as a "blank"

Please make sure that if user selects Office / Account Manager = "Undefined OR None" then Ref # = "blank". This is for both cases:

Ref# value is in a new format
Ref# value is in old format

[edit] SOW 16 Add one more GMS case

0003557: [GM Split ] Add one more GMS case (#7)

See updated image under #GM Split Rules

[edit] SOW 17 Add _exclude selected E0 from GM Split_ filter to GMS Admin tab

0003558: [GM Split ] SOW 17 Add *exclude selected E0 from GM Split* filter to GMS Admin tab

Add *exclude selected E0 from GM Split* filter
Add it to Gross Margin Split Admin > "Schedule ..." tab
It is multiselect
CTs identified by this filter should not be a subject to GM Split
For these CTs system should assign "GMS status: excluded"

[edit] SOW 18 Create one time xls report for CTs that have at least one old RefNum

mantis: 0003581

Create one time xls report for CTs that satisfy:

at least one RefNum is old 
AND
Actual departure date < year 2012

Columns:

CT#
MOT
shipper
consignee
country of origin
country of dest
export ref
import ref
third ref

[edit] SOW 19 Remove some confirmation pop-ups

Remove pop-up below:

CT#xxxxx is closed and not available for editing. Do you want to open viewer?
Reference numbers will be overwritten with current group values. Are you sure? (CT group edit dialog)
System will apply all reference numbers changes to all CTs in the group (when changing refNum in grouped CTs)
remove similar confirmation pop-ups to above

[edit] Release to Prod

[edit] SOW 20 Changes to Consolidated Inv: rename, replace columns, etc

[edit] Consolidated Internal Invoice

[edit] Handling Fee Statement

mantis: 0003674

Figure below shows Consol Inv as is.

Required changes:

no need 3 copies (original, files, accounts). Need one.
rename the document from "Consolidated Internal Invoice" to “Handling Fee Statement”
add GRP# to the right of CT#
replace columns "JFS, US, HK, UK, FR" with "Inv Amount"
- under this column post specific amount billed that is attributed to this specific CT(Group)

[edit] SOW 21 Changes to GMS Log: add Rev Rec date; create one line per invoice

mantis: 0003678

add RR (Revenue Recognition) date
- mapping - see here: Revenue Recognition Date
- see example on mock ups below

create one line per invoice
- see example on mock ups below:
  - instead of one line (see yelow) we added one more for one CT to post info for each Cons Inv on separate lines (see columns Q to X)

BEFORE CHANGE:

AFTER CHANGE:

[edit] SOW 22 Refactoring proj 1: implement OC process not only for CTs created after 2011 but all CTs

mantis: 3682

RF proj. mantis #1 : Implement close/open process not only for CTs created after 01.01.2011 but all CTs. Remove ShipmentOCStatus N/A - we don't need this status. For technical details pls contact Kostya or Sasha. Dev = Misha.

[edit] SOW 23 Refactoring proj 1: of ShipmentAccountingProcessor

mantis: 3683

RF proj. mantis #2: Make refactoring of ShipmentAccountingProcessor to implement support of calculation P/L amounts for all closed but not calculated yet CTs. Dev = Kostya

[edit] SOW 24 Refactoring proj 1: of GMSplit process

mantis: 3684

RF proj. mantis #3: Make refactoring of GMSplit process - this process should be independent on ShipmentAccountingProcessor process. Dev = Sasha

[edit] SOW 25 Grant specific GM Split functionality to non-management users

mantis: 3723

Right now, only Management users have the ability to manage many of the GM Split functions.However, some of the functionality should be made available to other users as well

1) Super Ops Users should have ability to Open & Close shipments via P/L tab.

2) All Ops users should have ability to Close shipments in P/L tab.

3) All Super Accounting users need access to the consolidated invoices in Admin > ACC > GM Split Admin > GM Split Log tab. Granting them access to GM Split Log tab should suffice - however, they should not have access to the other tabs in the GM Split Admin menu.

[edit] SOW 26 New cases for GM Split (Case 8 and Case 9)

mantis: 3724

Case 8: Owner Office does not handle this shipment. Office A is either Import/Export and Office B is the Third Office.

Case 9: Owner Office does not handle this shipment. Third Office is set.

Changes to the percentage values should be logged.
This change should be implemented in GM Split Admin (Admin->Acc->GM Split Admin) and Client Company Admin (GM Split Rules)

[edit] SOW 27 Ignore manual Int Invoices for GM Split calculations

mantis:

spec:

Change to logic below.

If internal invoice is issued "manually" by user (from Inv tab) then GMS process should ignore amounts on that invoice and split GM as if that invoice did not exist.

But of course that amount should be accounted for on P/L tab and P/L report.

I assume that is done to "override" standard formula in some exceptional cases.

[edit] Release Instructions

Following scripts have to be applied to data during release:

#SOW 8 Change criteria for NA OC status

[edit] Code

ongoing UAT: https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/

[edit] ZULs

dir:
- https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/internal/admin/grossMarginSplit/

discontinued component to post credit to Jag office
- https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/internal/admin/grossMarginSplit/CreditWindow.zul

"Apply changes to all CTs with departure date from ... to ..." pop-up
- https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/internal/admin/grossMarginSplit/RecalculateGrossMarginWindow.zul
- use="com.elco.cybertrax.internal.admin.grossMarginSplit.RecalculateGrossMarginWindow"

[edit] Java =

packages/imports:
- package com.elco.cybertrax.internal.admin.grossMarginSplit;
- import com.sibers.cybertrax.data.control.services.IShipmentService;

dir:
- https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/src/com/elco/cybertrax/internal/admin/grossMarginSplit/

[edit] DR reps

https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/src/com/elco/cybertrax/common/reports/shipmentsByStatus/ShipmentsByStatusForm.java

[edit] DB

@@ Line 1: / Line 1: @@
-[[Category:AccMisc]]
+[[Category: Acc]]
 == Info ==
@@ Line 6: / Line 6: @@
 === Scope ===
-Covers this one module - Profit Sharing Module.
+Covers this one module - Profit Sharing Module. (aka GM Split)
+=== User Guide ===
+[[GM Split USER GUIDE]]
 == Glossary ==
@@ Line 14: / Line 17: @@
 === Intro ===
-This is as received from Module Owner.
+The purpose of this project is:
+* to automate distribution of Gross Margin across Jaguar Freight offices
+* to add open / close functionality to CT, Master, Group records
+As a by product:
+* Viewers for CT, Master, Group  and additional Reports has been created
+* number of minor changes has been introduced to Accounting and Ops modules
 === General Requirements ===
@@ Line 100: / Line 110: @@
 If the “OO” is the “Third Party Reference” the OO did not “handle” the shipment.
-===== 60/40 rule =====
+===== X/Y rule =====
 If the shipment is handled between two offices and one is the OO;
-% going to the OO (IO, or EO) and
+X% going to the OO (IO, or EO) and
-% going to the other (non OO) office (IO, or EO)
+Y% going to the other (non OO) office (IO, or EO)
 No TO office under this rule
-===== 45/45/10 rule =====
+Example: 60/40
+===== X/X/Y rule =====
 If a shipment is handled by an OO and a sister office is noted as the import or export office on the record with a third office handling other details,
-% of GM to the OO (IO, or EO);
+X% of GM to the OO (IO, or EO);
-% to the handling office listed on the record and (IO, or EO – non OO)
+X% to the handling office listed on the record and (IO, or EO – non OO)
-% to the 3rd office involved (TO, non OO)
+Y% to the 3rd office involved (TO, non OO)
 TO cannot be = to OO under this rule
-===== 20/40/40 rule =====
+Example: 45/45/10
+===== X/Y/Y rule =====
 If a shipment is not handled by an OO and two other office are listed as the export and import offices,
-% of GM to the OO (TO)
+X% of GM to the OO (TO)
-% to each handling office (IO and EO)
+Y% to each handling office (IO and EO)
 TO must be = to OO under this rule
-===== 20/80 rule =====
+Example: 20/40/40
+===== X/Y rule 2 =====
 If a shipment is handled by only one office but is not owned by that office,
-% of GM is attributed to the OO
+X% of GM is attributed to the OO
-% to the handling office (IO, EO)
+Y% to the handling office (IO, EO)
 No TO Office under this rule
@@ Line 144: / Line 160: @@
 Only 1 office on the record (cannot be the OO)\
-===== 100% rule =====
+Example: 20/80
-– If a shipment is handled and owned by only one office, the only office listed in the ‘ref’ numbers is also the OO
+===== 100 rule =====
+If a shipment is handled and owned by only one office, the only office listed in the ‘ref’ numbers is also the OO
 % GM goes to the OO
 Only 1 office on the record (must be the OO)
+===== X/Y/Z rule =====
+Example: 10 / 40 / 50
+X% of GM is attributed to the OO
+Y% to the handling office (EO)
+Z% to the handling office (IO)
+No TO Office under this rule
 ==== Closing dates ====
@@ Line 178: / Line 208: @@
 Marc: Can we have all related functionality in one place in the system - admin, reports, etc.
-== SOW 1 ==
+== OC Functionality ==
-* '''0003403: [Profit Sharing Module] SOW 1 - part B - Misha'''
-=== Closing Time Frame ===
+=== OC status ===
-Represents pairs: MOT/Days.
+At some point in the life of a shipment record it will not be updated anymore.
+Moreover some updates would become harmful.
+Therefore, certain types of functionality should be blocked for "old/mature" records.
+This would prevent undesirable situations such as:
-Meaning: it takes this number of days since departure to receive and issue all related to particular CT invoices. Therefore only after CT is "closed" actual P/L and internal invoices can be calculated. It is used in calculating [[#OC status]].
+* altering shipment weight after invoices has been issued
+* issuing GMS invoice before all PI/SI are in
+* etc
-Example:
+Record that have limited functionality as far as updates/etc is called "closed" and should have status "closed".
+Otherwise it is "open".
+We call this status "OC status" - (Open/Closed) status.
+===== Closing Time Frame =====
+We need a table in the system that defines how many days shipment should stay open since it was created.
+For every MOT this number is different.
+Typical values:
   Air - 35 days
@@ Line 200: / Line 245: @@
 Add this to User Access table.
-=== OC status ===
+==== OC Status Values ====
-OC (Open/Closed) status.
+* '''n/a'''
+: for CTs closed before 2012
+:: see also SOW 22
+* '''open'''
+: for new CTs or manually re-opened CTs
+* '''manually open'''
+: for CTs that were manually opened by user
+* '''closed'''
+: for CTs that are closed
-Values:
-* closed before 2012
-* open
-* closed
 There are three ways to set this status: automatically, manually and by script.
@@ Line 215: / Line 264: @@
 When new shipment is created its status is automatically "open".
-When number of days between Actual Date Of Departure and today's day becomes equal to [[#Closing Time Frame]] OC status would change automatically to "closed".
+===== Closed CT condition =====
+ When the number of days between Actual Date Of <strike>Departure</strike> Arrival and today's day becomes equal to
+ [[#Closing Time Frame]] CT is said to satisfy "closed" condition.
+===== OC process =====
+* '''OC Process''' is a process that runs periodically and updates "OC status" and "GMS status":
+** it checks on all "open" CTs to see if they can be set as "closed but not delivered" or "closed and delivered"
+** it checks on all "closed but not delivered" and see if now they can be set as "closed and delivered"
+** also if it is changing CT status to "closed and delivered" then it will also set GMS status to "pending" from "not ready"
+* Currently it is defined to run once a day (defined in Admin)
+==== Setting OC status manually ====
+We need to provide functionality to manually:
+* set status to "closed" on open and manually open CTs
+* set status to "mannually open" on closed CTs (user who opened CT manually must close CT manually)
+This is subject to user access permissions.
+Suggested implementation: add "Close/Open file" button to P/L Tab.
+==== Closed CT have different permissions ====
+CT (file) that has "closed" status should limit what operators can do with record.
+See [[#Viewer for CT, Master and Group]]
 ==== Setting OC status by script ====
-Please note that all CTs that would close in 2012 (if this functionality would be available since the beginning) should have status "closed before 2012".
+See new criteria here: [[#SOW 8 Change criteria for NA OC status]]
   Tech Note
   --------
-  To achieve above we need to write and run script prior Release !
+  To achieve above we need to write and run appropriate scripts prior Release !
-==== Setting OC status manually ====
+==== Display OC status on Gen Tab ====
-We need to provide functionality to manually:
+=== Opening and Closing shipments when Groups and Masters are involved ===
-* set status to "closed" on open CTs
-* set status to "open" on closed CTs
-This is subject to user access permissions.
+In general, we want to have Master or Group to be consistent in terms of open/close status. It means:
-=== Owners Office ===
+ Master/Group has open status '''if and only if'''
+        every CT inside of that Master/Group has open status.
+ Master/Group has closed status '''if and only if'''
+        every CT inside of that Master/Group has closed status.
-Owners Office (OO) is a field (attribute) that identify what Jaguar office is the OO for particular Client Company - see [[Client Companies]].
+'''Closing'''
-Relationship is one to one.
+CT that is a part of a Group or/and of the Master can be closed only when ALL CTs in that Group or Master can be closed based on the individual time frames.
-Add this to "Part A." section on UI.
+In case of automatic closing System would have to wait for all CTs to "mature" in terms of timeframes.
-=== Import, Export and Third Offices ===
+In case of manual closing System should give a warning to the user that all CTs (in related M/GRP) are going to be closed.
-EO, IO, TO are identified by [[File Reference Numbers]]
+'''Opening'''
-=== Identify and Display Offices, Statuses and GMS Case on Gen Tab ===
+In case of manual opening System should give a warning to the user that all CTs (in related M/GRP) would be opened.
-See mock up below.
+=== Viewer for CT, Master and Group ===
-[[File:Gen Tab with EO IO OO TO.JPG]]
+* should mimic look and feel/layout for editors
+* block all functionality that would affect GM Split (editing records, adding CTs to Master, etc)
-=== Add GM Split section to Admin ===
+=== Interface to manually "open" and "close" CTs ===
+* "Open/close" button on P/L tab
+* This is subject to access table
+* Log who/when opened/closed
+=== Block altering of the "closed" CT/Master/Group ===
+For users who have no ability to open manually system should display error explaining why edit is not possible and suggest "view" instead.
+=== DR report to show all manually opened CTs in the system ===
+* Lev 1: counter - how many CTs have status "manually" open
+* Lev 2: xls (if no extra time); sortable HTML with xls option (if enough time); Columns:
+** CT# (hyperlink)
+** Client
+** who manually opened
+** when manually opened
+=== DR report for closed files without a delivery status ===
+This report should show all CTs that have [[#OC_Status_Values]] = closed but not delivered
+* Lev 1: counter
+* Lev 2: xls (if no extra time); sortable HTML with xls option (if enough time); Columns:
+** CT# (hyperlink)
+** Client
+** Days since closed
+== GMS functionality ==
+=== Prerequisites for GMS calculations ===
+In order to calculate GMS Split we need the following to be defined for the record:
+* OO
+* EO
+* IO
+* TO
+* GMS ratio
+* GMS schedule
+==== Owners Office ====
+Owners Office (OO) is a field (attribute) that identify what Jaguar office is the OO for particular Client Company - see [[Client Companies]].
+Relationship is one to one.
+Add this to "Part A." section on UI.
+Make sure changes are posted into Log.
+==== Import, Export and Third Offices ====
+EO, IO, TO are properties of CT and set on Gen Tab. '''See Also: RefNums'''
+==== GMS section in Admin ====
 Add number of configuration options - see below.
@@ Line 257: / Line 393: @@
 Add under Acc > GM Split.
+===== GM Split Rules =====
 See table below.
@@ Line 267: / Line 403: @@
 User could edit %s.
-New edit of the table should have associated time frame (from date to date) affecting GM Split re-calculations.
+One table is for default values and one for Client Company specific are required.
+Add this as a property into User Access table.
-Add this as a property into User Access table
+Add changes to log.
 [[File:Prfit split cases.JPG]]
-==== GM Split rules ====
+====== One more GMS case ======
+Business requested one more case see case#6 above.
-===== GM ratio =====
+ Example:
+      =       NY
+ EO     =       HK
+ IO      =       UK
+ TO     =       none
+====== GM ratio ======
 More than one "GM ratio" (“percentage model”) could be applicable - more than one table as in Fig above could be defined.
@@ Line 285: / Line 430: @@
 It means that "GM ratio" is a new property for Client Company entity.
-==== Limitations on pending GMS and closed file (CT) ====
+'''Make sure changes are posted into Log.'''
-List of check boxes that indicates what can not be done with "closed" file:
-* generate sales invoice/credit note
+====== Additional Description column ======
-* map PI invoice
-* edit any CT field
-* posting into Com
-* generating docs
-* upload files
-==== Schedule for GM Split calculation ====
+Add extra column to GM split table.
-For example it would be possible to say that GM Split calculation will happen once a day at 17:00 EST
+Call it Description.
-==== Credit to the office ====
+Make it configurable in config file (so we do not have to re-release to edit).
-Ability to post some credit from one office to another office.
+Pull them from [[GM_Split_Requirements#Calculation_Models:_RULES]].
-This amount will be taken into account when calculating GM split.
+See mock up below.
-Initially this list will be empty. Later someone could come and add lines similar to below:
+[[File:GMD Descr.JPG]]
-Move 100,000.00 USD from NY to Paris office on February 15, 2012.
+===== Schedule for GM Split calculation =====
-Table should have the following columns:
+For example it would be possible to say that GM Split calculation will happen once a day at 17:00 EST
-* from office
+===== Credit to the office =====
-* to office
-* amount
-* currency
-We should have a log of who posted and when.
+''Per Karen this is no longer needed''
-==== Calculate and display GMS status ====
+===== Limitations on pending GMS and closed files =====
-See mock up above.
+''Not required at least for this release''
+===== Log for GMS Admin Changes =====
+Post all changes into Log.
+===== Access Rights =====
+Have only one item in User Access Rights table regulating GM Split Admin (all tabs).
+=== GMS status ===
 GMS (Global Marging Split) Statuses:
-* '''open''' - regular shipment until it reached thresholds defined in [[#Closing time frames]]. This is based on "actual date of departure". After it reached this time frame it changes the status to "pending".
+* '''n/a'''
-* '''pending''' - until next GMS Scheduler runs
+:-GMS status for CTs that have OC status "closed before 2012". Need script - see below.
-* '''closed''' - after system runs Global Margin Split on this CT
-==== Change GMS status manually ====
+* '''not ready'''
+:- default status for all new CTs
-Add functionality to change GMS status manually.
+* '''pending'''
+:- these CTs will be included into the next run of GM Splitter
+:- this status is set by [[#OC Process]]
-Add this as a right into User Access table
+* '''success'''
+:- after system runs Global Margin Splitter on this CT system successfully must change status to "completed"
+:- set by GM Splitter
-==== Log for Admin Changes ====
+* '''error: <reason>'''
+:- after system runs Global Margin Splitter on this CT system unsuccessfully; :- examples of <error>:
+:: '''CT does not fall under any of GMS case''';
+:: '''closed but not delivered''' -CTs closed by user or system (satisfy [[#Closed CT condition]]) but do NOT satisfy "delivered CT" condition - see [[List_CTs#DELIVERED]]
+:-set by GM Splitter
-Post all changes into Log.
+==== Setting GM status by script ====
+All CTs that would close prior to Jan 1, 2012 (if this functionality would be available since the beginning) should have GM status "n/a"
+ Tech Note
+ --------
+ To achieve above we need to write and run appropriate scripts prior Release !
+=== GMS Splitter Process ===
+This process runs periodically for example once a day.
+Exact schedule would be defined here: [[#Schedule for GM Split calculation]].
+==== Forcing GMS Splitter to run immediately ====
+This functionality is provided by button in a GMS Admin.
+After process finished running it should provide report in pop-up describing:
+* how many and what CTs were processed
+* how many and what Consolidated Invoices were generated
+Ideally post this into Log as well.
+==== Steps for GMS Splitter ====
+* Look at all CTs with GMS status = "pending"
+* See if CT info is complete/correct:
+** OO is defined
+** OO/IO/EO/TO combination falls under cases defined in GMS Admin
+* If info is complete/correct then:
+** calculate GM Split - see [[#GMS Algorithm]]
+** issue invoices - see also [[#Invoices generated by GM Splitter]]
+** post results into DB
+** set GMS status = "success"
+* Else set GMS status = "error"
+==== GMS Algorithm ====
+* For every CT (subject to this algorithm):
+** identify what GM case it belongs to (based on OO, IO, EO, TO values)
+** calculate based on % defined in [[#GMS Section in Admin]]
+Note: take into account previously issued internal invoices.
+===== Example 1a =====
+#create: CT: Air, OO=NY, EO=NY, IO=HK, TO=none, with GMS case#: Case 1: (OO : US = 60%, A : HK = 40%)
+#set: NY issued/received 100 USD to Client
+#set: NY paid/posted 90 USD from Vendor
+#set: delivery date
+#close CT
+#verify: GMS status is set to: pending
+#run: GM Splitter
+#verify: P/L tab should show profits: 10USD for Jaguar, 6 for NY and 4 for HK
+#verify: Inv Tab should have: Indiv Inv  for 4USD from HK to NY
+#verify: Consolidated Internal Invoices Report should have new invoice for 4USD from HK to NY
+dev test:  CT# 375464, AIR, AlexTest (ClientCompany)
+===== Example 1b =====
+For CT in 1a:
+# open CT
+# post 1USD purchase for NY
+# close
+# run splitter
+# verify: NY billed HK for 0.40 USD
+# verify new P/L
+===== Example 1c =====
+# open CT
+# change GMS % to 50/50
+# close
+# run splitter
+# verify: HK billed NY for 0.90 USD
+# verify new P/L
+===== Example 2 =====
+# create CT with GMS Case 5: (OO : US = 100%)
+# close/deliver
+# run splitter
+# verify: no invoices has been generated
+===== Example 3 =====
+===== Example 4 =====
+==== Invoices generated by GM Splitter====
+===== Number of invoices =====
+There should be limited number of invoices issued each time Runner runs. It corresponds to num of unique pairs from the list of offices. In 2012 it would be:
+* NY/HK (in this case we would have one invoice: from NY to HK or from HK to NY)
+* NY/Lon
+* NY/Par
+* HK/Lon
+* HK/Par
+* Lon/Par
+So every time it runs between 0 and 6 Consolidated Invoices would be generated.
+ Implementation Note:
+ ------------------
+ Currently system often generates 2 invoices for each pair of offices.
+===== Two types of Internal Invoices =====
+System would continue registering and generating old types of Individual Internal Invoices (per CT or Group).
+They would be generated automatically by the System.
+Layout would stay the same.
+Old functionality related to these invoices would be preserved (Inv Tab, P/L Tab, SearchInvoices, etc).
+At the same time new Consolidated Invoices will be generated as well (by GMS Splitter).
+===== Invoice numbering =====
+New consolidated invoice should follow "old" sequence (as defined in [[Acc_CT_Tabs_Sales_or_Internal]] )
+Individual Internal Invoices (per CT or Group) would follow this pattern:
+ <Related Consolidated Invoice>"-"<CT#>
+Consolidated Invoices pattern:
+ "IIGMS"<office code><sequence>
+Example:
+Assume GM Splitter discovered only 2 CTs that are pending GMS.
+* for CT#555 office NY owes 10USD to office HK and
+* for CT#888 office HK owes 20USD to office NY
+System would register:
+* one Consolidated Invoice IIGMSNY12345 from NY to HK for 10USD.
+* and two Individual Invoices:
+** IIGMSHK12345-555 from HK to NY for 10USD
+** IIGMSNY12345-888 from NY to HK for 20USD
+===== Timing =====
+Both Individual and consolidated invoices will be generated at the same time.
+===== Consolidated Invoice Layout =====
+See mock up below.
+[[File:GM Split pdf 6.jpg | 600px]]
+====== Change to pdf layout ======
+Sec A: post total amount / currency there
+Sec B: this section should have table with the following columns:
+    CT# - with hyperlink
+    Client Company (Added!!!)
+    GMScase - number and %
+    OO
+    EO
+    IO
+    TO
+    JFS - USD P/L for JaguarFreight
+    US - USD P/L for this office
+    HK - USD P/L for this office
+    UK - USD P/L for this office
+    FR - USD P/L for this office
+NOTE:
+Please note that this is a subset of a report defined in [[#CTs Processed by GM Splitter report]]
+See updated pdf above.
+ Implem Note:
+ -----------
+ This layout has changed again. See SOW 20.
+=== GM Splitter Log ===
+This was estimated to be done in one workday.
+* This Log will be located on additional tab of "Gross Margin Split Admin"
+* It will be in the form of a table
+* Table will show latest runs at the top
+* Table will have columns (left to right):
+: '''Timestamp''' - date/time when process completed ()
+: '''User''' - user who run it (or say "schedule" if it was called by a scheduler)
+: '''Success''' - number of CTs successfully processed
+: '''Error''' - number of CTs that were rejected by this process
+: '''Consol II''' - list of consol invoice numbers generated (with a hyperlink)
+: '''CTs''' - link to "CTs Processed by GM Splitter" xls  report - see below.
+==== CTs Processed by GM Splitter report ====
+* This report provides detailed info about CTs processed by GM Splitter.
+* Sorted by "Status" and then by "CT#"
+* Columns:
+: '''Status''' - success or error
+: '''Reason''' - reason for error
+: '''CT#''' - with hyperlink
+: '''Client Company''' (Added!!!)
+: '''GMScase''' - number and %
+: '''OO'''
+: '''EO'''
+: '''IO'''
+: '''TO'''
+: '''JF''' - USD P/L for JaguarFreight
+: '''US''' - USD P/L for this office
+: '''HK''' - USD P/L for this office
+: '''UK''' - USD P/L for this office
+: '''FR''' - USD P/L for this office
+: '''Cons Inv1''' - Cons Inv Num 1
+: '''Cons Inv2''' - Cons Inv Num 2
+: '''II1 from''' - from office for Indiv Inv #1
+: '''II1 to''' - to office for Indiv Inv #1
+: '''II1 USD''' - amount for Indiv Inv #1
+: '''II2 from''' - from office for Indiv Inv #2
+: '''II2 to''' - to office for Indiv Inv #2
+: '''II2 USD''' - amount for Indiv Inv #2
+===== Group case =====
+* Add GRP# column
+* For Individual CTs this report will have one line with "n/a" in GRP# column
+* For Grouped CTs:
+** this report will have one line with GRP# in GRP# column
+** CT# column will show "lowest CT number" in the group
+ Implem Note:
+ ------------
+ Report above is altered - see SOW 21.
+=== CT Tabs ===
+==== Inv Tab ====
+No change.
+==== P/L Tab ====
+Add link to Consol Inv. See below.
+[[File:Link to Consol Inv on PL tab.jpg| 600px]]
+=== Existing Acc Reports ===
+We are changing the way we handle Internal Invoices. This could affect or even break some of the existing reports such as PnL Report for Multiple CTs (summary and detailed view).
+==== Search Inv Report ====
+==== P n L for Multiple CTs ====
+=== Browser for Consolidated Internal Invoices ===
+This module should provide basic capabilities to browse Consolidated Internal Invoices issued by the system.
+We need at least core filters:
+* issuing date: from/to
+* issuing office
+Paging and sorting are required.
+ Implem Note:
+ -----------
+ Implemented as additional option to "Search Inv" report.
+=== GMS Misc ===
+==== Display OO, EO, IO, GMS Status, GMS Case on Gen Tab ====
+See mock up below, Red Box B.
+[[File:Gen Tab with EO IO OO TO.JPG | 800px]]
+====  UC: How to apply new GMS split rules to GMS completed CTs====
+ Spec change
+ ----------
+ See SOW 3
+This is the case when we want to change GM Split %ages and apply them not only in the future GMS runs but also recalculate for CTs that have GMS status completed.
+This can be achieved by changing OC status for such group of CTs from closed to open to closed. This will force GMS status to be "pending" and next time GMS Runner runs it will re-adjust GMS for these CTs.
+This can be done just by user - no programming required.
+==== Create DR report to identify all new shipments without an Owner Office ====
+'''0003490''': [GM Split ] Create DR report to identify all new shipments without an Owner Office
+===== Info for E0 estimate =====
+Core need: To identify early CTs that have Client Companies that do not have Owners Office set
+* Level one: counter
+* Level two: xls with 2 columns - CT#(with link) and Client Company
+==== UC: CTs have different sets of IO EO TO ====
+System must not allow groups in which 2 CTs have different sets of IO/EO/TO
+==== UC: some CTs are pending GMS and some are not ready ====
+UC: some CTs are "pending" GMS, some are "not ready"
+System should wait until all CTs in the group are ready (they could be delivered at different times).
+== Change Requests ==
+==== SOW1 VAT should be excluded ====
+0003509	[GM Split ] VAT should be exluded from a) GM total; b) GM Split
+Exclude VAT from P/L amount. Implications:
+) Exclude VAT from P/L on P/L tabs.
+Consider VAT as only value entered on a sales side.
+VAT related charge codes is something else - include them into P/L.
+See mock up below:
+[[File:PnL tab with no VAT.JPG | 500px]]
+) Exclude VAT from P/L amount calculated in GM Split
+) Exclude VAT from P/L amount on P/L reports.
+Apply same idea as in 1 above - do not add any columns just post amounts without VAT and add related Note:
+ * VAT has been excluded from all amounts
+See mock ups below.
+[[File:Bill req with no vat.JPG | 500px]]
+[[File:P n l summary.JPG  | 500px]]
+[[File:Pnl detailed.JPG| 500px]]
+[[File:Pnl sh group.JPG |500px]]
+[[File:Pnl with cc num.JPG  |500px]]
+[[File:Pnl cc group.JPG  |500px]]
+==== SOW2 New business rules for currency conversion ====
+'''0003510'''[GM Split ] New business rules for currency conversion
+When converting amounts to another currency (for P/L and GM Split!) use exchange rate available for date = "departure date" for CT.
+In case of groups use this date from any CT. We believe that this date is the same for all CTs in the group since group is always a part of the Master.
+===== Add Exchange rate on this report is based on XX notes  =====
+Since for some reports exchange rate will be based on "invoice created on" date (Search Invoices) and for others be based on "CT departure" date (P/L) we need appropriate note on screen or xls:
+* NOTE: exchange rate is based on "invoice created on" date
+* NOTE: exchange rate is based on "CT departure" date
+==== SOW3 Recalculate exiting records in case ratios has changed ====
+'''0003511'''[GM Split ] Add option to GM Split Rules tab to re-calculate exiting records in case ratios or OO has changed
+* Add option to GM Split Rules tab to recalculate exiting records in case ratios has changed
+* Do it for both client specific and global rules
+* Add "Recalculate CTs with departure date from" date - this is an option to consider when re-calculating. Place it on the tab. Required field.
+** default is from tomorrow to blank
+** for mapping - see [[Revenue Recognition Date]]
+* On change prompt user: New GMS ratios will be applied to all CTs with departure date from XXX to YYY next time GM Splitter runs. Apply changes? Y/N.
+* Recalculate during next GMS run.
+* Apply similar procedure if OO has changed.
+* See mock ups below. Use same or equivalent UIs (say you could re-use same pop-up in 3 cases).
+[[File:Gms recalc on save.jpg]]
+[[File:Gms recalc on save client specific.jpg]]
+[[File:Gms recalc on save for OO.jpg]]
+==== SOW4 DR to show all CTs that have GMS status error with a reason ====
+'''0003517'''[GM Split ] Create DR to show all CTs that have GMS status = error with a reason
+* level 1: counter
+* level 2: xls with columns: CT#, Client company, error reason
+==== SOW5 Add new error reason/type: old RefNum ====
+'''0003527''' [GM Split ] Add new error type: “non-automated” Ref number is included in one of the REF fields
+Add new error reason/type: “non-automated” Ref number is included in one of the REF field
+This happens in CTs that are "not fixed" by new Ref Num interface.
+See example below:
+[[File:Old ref num.jpg]]
+=== SOW6 Re think exg rates logic again ===
+'''0003534''': [GM Split ] UC: Actual departure date is not defined and as a result exchange rates are not defined for issued invoices
+* Add the following NOTE into header of all P/L reports and P/L tab:
+ NOTE:  Exchange rates are only defined (and used for Invoices) for CTs with Actual Departure Date defined
+* Add WARNING to the CT P/L tab if Actual Departure Date is not defined:
+ WARNING!:  Actual Departure Date is not defined therefore Exchange rates for invoices are not defined.
+=== SOW 8 Change criteria for NA OC status  ===
+'''0003537''': [GM Split ] SOW 8 Change criteria for OC status = n/a to actual departure date<=2012
+New Rule:
+ All CTs that have Actual departure date
+ OR
+ Actual Delivery date < 2012 year
+ should have OC status "n/a".
+=== SOW 9 Re-organize and rename DRs related to GM Split ===
+'''M#0003538''' [GM Split ] SOW 9 Re-organize/rename DRs related to GM Split
+* 1 Remove "Profit Sharing Panel" from system and users profile (this was old way of doing GMS Reps)
+* 2
+* 3 Re-name "Shipments by Status" Report class into "GM Split"
+==== Report format redesign ====
+* 4 Name of the xls file for this report report should consist of:
+** <Report Class>-<Report Sub-class>-<Description>-<Generated on date/time>.xls
+** Example: ''GM Split Reports-Closed but not Delivered-My GMS reports for Coty-09-April-2012 1242 PM EDT.xls''
+* 5 Header of the report should look like this:
+ Report Class: GM Split
+ Report Sub-class: Closed but not Delivered
+ Description: My GMS reports for Coty
+ Created by: Roman Lahno
+ Generated on date/time: 09-April-2012 12:42 PM EDT
+ Shipments on the report: 566 shipment(s)
+* 6 On level one it should have additional column "Report Class/Sub-class:" before "Description" column
+==== Saved Scheduled Reports page redesign ====
+* 7 "Saved/Scheduled Reports" page (/Index.zul#reportsScheduled):
+** rename column "Reports class" into "Report class/sub-class"
+** post there: [Class name] / [sub-class name]
+** Add one column "Report Category"; should be first column
+** sort this table by 1st then by 2nd column
+==== Report category concept and multiple DB panels for groups of DRs ====
+* 8 We will have one DR Panel per "Report Category"! "Report Category" is:
+** a short list that rarely changes
+** hard coded
+** defined already in the system - see Main Menu > Reports
+** items we have so far:
+*** Operational
+*** Statistical
+** new items to be added:
+*** Acc (all GM Split DRs; for Billing Required, etc)
+=== SOW 10 The reference numbers should be an automatic group save ===
+'''0003539''': [GM Split ] The reference numbers should be an “automatic group save”
+Goal: to manage and assign '''same''' Ref Nums / related values to all CTs in the group
+* 1 In CT editor for grouped CT make checkbox read-only for 3 dropdowns managing ref nums. See Fig. below.
+* 2 If value(s) above have changed and user clicks "Save" (Save for individual CT) then produce pop-up that:
+** gives warning - see below
+** gives choice "OK", "Cancel"
+ Even though you selected "individual" Save system will apply the following changes to '''all''' CTs
+ in the group:
+  Export Acc Manager/Office changed from ... to ...
+  Export Ref# changed from ... to ...
+  .....
+  .....
+[[File:RefNums for a group.JPG]]
+=== SOW 11 Create a script to identify GRPs with CTs that have not identical RefNums info ===
+* '''M#0003541'''
+EI, IO, TO must be same for all CTs in the GRP.
+After 0003539 is implemented it will be but until that we must use this script to collect corrupted records.
+=== SOW 12 Rethink exg rates logic ===
+'''0003534''': [GM Split ] Rethink exg rates logic.
+This is a new logic that we want to use:
+* see [[Exchange_Rates#v2]]
+=== SOW 12a Use actual ExR with the new logic for GMS ===
+see [[Purchase_Invoices#SOW_1_Change_exchange_rate_logic]]
+=== SOW 13 Additional 3 cases for RefNums in case of GRPs ===
+'''0003547''': [GM Split ] Additional 3 cases for RefNums in case of GRPs
+To remind, all CTs in the GRP must have same RefNum settings.
+Therefore we must change the way system handles the following cases:
+* '''Creating GRP'''
+** provide interface to allow user to chose between two options:
+*** inherit RefNum settings from one of the CTs
+*** select new RefNum values and apply them to all CTs in the group
+*** in both cases above "limit selection related to logged user's office"
+* '''Adding CT to GRP'''
+** This CT must acquire CT RefNum settings from GRP
+** Provide warning to the user (pop-up with options "OK" and "Cancel")
+In addition please also change logic for this case:
+* '''Removing CT from GRP'''
+** set RefNum settings of this CT to undefined
+** Provide warning to the user (pop-up with options "OK" and "Cancel")
+=== SOW 14 Add conditions to DR reports ===
+For all GM Split DR reports apply these changes:
+* exclude canceled, deleted CTs from report
+* add filter "CT Created on date (from/to)"
+=== SOW 15 Replace combobox with listbox ===
+* '''0003556''': [GM Split ] bug/change: Replace combobox with listbox(dropdown) for "Office / Account Manager" field; NullPointerException
+This is to eliminate ability by user to erase value in combobox so that it shows as a "blank"
+Please make sure that if user selects Office / Account Manager = "Undefined OR None" then Ref # = "blank". This is for both cases:
+* Ref# value is in a new format
+* Ref# value is in old format
+=== SOW 16 Add one more GMS case ===
+* '''0003557''': [GM Split ] Add one more GMS case (#7)
+See updated image under [[#GM Split Rules]]
+=== SOW 17 Add _exclude selected E0 from GM Split_ filter to GMS Admin tab ===
+* '''0003558''': [GM Split ] SOW 17 Add *exclude selected E0 from GM Split* filter to GMS Admin tab
+* Add *exclude selected E0 from GM Split* filter
+* Add it to Gross Margin Split Admin > "Schedule ..." tab
+* It is multiselect
+* CTs identified by this filter should not be a subject to GM Split
+* For these CTs system should assign "GMS status: excluded"
+=== SOW 18 Create one time xls report for CTs that have at least one old RefNum ===
+'''mantis: 0003581'''
+Create one time xls report for CTs that satisfy:
+ at least one RefNum is old
+ AND
+ Actual departure date < year 2012
+Columns:
+* CT#
+* MOT
+* shipper
+* consignee
+* country of origin
+* country of dest
+* export ref
+* import ref
+* third ref
+=== SOW 19 Remove some confirmation pop-ups ===
+Remove pop-up below:
+* CT#xxxxx is closed and not available for editing. Do you want to open viewer?
+* Reference numbers will be overwritten with current group values. Are you sure? (CT group edit dialog)
+* System will apply all reference numbers changes to all CTs in the group (when changing refNum in grouped CTs)
+* remove similar confirmation pop-ups to above
+=== Release to Prod ===
+=== SOW 20 Changes to Consolidated Inv: rename, replace columns, etc ===
+==== Consolidated Internal Invoice ====
+==== Handling Fee Statement ====
+mantis: '''0003674'''
+Figure below shows Consol Inv as is.
+Required changes:
+* no need 3 copies (original, files, accounts). Need one.
+* rename the document from "Consolidated Internal Invoice"  to “Handling Fee Statement”
+* add GRP# to the right of CT#
+* replace columns "JFS, US, HK, UK, FR" with "Inv Amount"
+** under this column post specific amount billed that is attributed to this specific CT(Group)
+[[File:Consol inv.png | 800px]]
+=== SOW 21 Changes to GMS Log: add Rev Rec date; create one line per invoice ===
+mantis: '''0003678'''
+* add RR (Revenue Recognition) date
+** mapping - see here: [[Revenue Recognition Date]]
+** see example on mock ups below
+* create one line per invoice
+** see example on mock ups below:
+*** instead of one line (see yelow) we added one more for one CT to post info for each Cons Inv on separate lines (see columns Q to X)
+BEFORE CHANGE:
+[[File:GMS log before RR.jpg]]
+AFTER CHANGE:
+[[File:GMS log afte RR 1.PNG]]
+=== SOW 22   Refactoring proj 1: implement OC process not only for CTs created after 2011 but all CTs===
+mantis: '''3682'''
+RF proj. mantis #1 : Implement close/open process not only for CTs created after 01.01.2011 but all CTs. Remove ShipmentOCStatus N/A - we don't need this status. For technical details pls contact Kostya or Sasha. Dev = Misha.
+=== SOW 23  Refactoring proj 1: of ShipmentAccountingProcessor ===
+mantis: '''3683'''
+RF proj. mantis #2: Make refactoring of ShipmentAccountingProcessor to implement support of calculation P/L amounts for all closed but not calculated yet CTs. Dev = Kostya
+=== SOW 24 Refactoring proj 1: of GMSplit process ===
+mantis: '''3684'''
+RF proj. mantis #3: Make refactoring of GMSplit process - this process should be independent on ShipmentAccountingProcessor process. Dev = Sasha
+=== SOW 25 Grant specific GM Split functionality to non-management users ===
+mantis: '''3723'''
+Right now, only Management users have the ability to manage many of the GM Split functions.However, some of the functionality should be made available to other users as well
+) Super Ops Users should have ability to Open & Close shipments via P/L tab.
-=== SOW 1 Implementation Notes ===
+) All Ops users should have ability to Close shipments in P/L tab.
-==== GM Split Admin ====
+) All Super Accounting users need access to the consolidated invoices in Admin > ACC > GM Split Admin > GM Split Log tab. Granting them access to GM Split Log tab should suffice - however, they should not have access to the other tabs in the GM Split Admin menu.
-[[File:GM Split Admin.png | 800px]]
+=== SOW 26 New cases for GM Split (Case 8 and Case 9)===
-[[File:GM Split Admin 2.png]]
+mantis: '''3724'''
-[[File:GM Split Admin 3.png]]
+Case 8: Owner Office does not handle this shipment. Office A is either Import/Export and Office B is the Third Office.
-[[File:GM Split Admin 4.png]]
+Case 9: Owner Office does not handle this shipment. Third Office is set.
-[[File:GM Split Admin 5.png]]
+* Changes to the percentage values should be logged.
+* This change should be implemented in GM Split Admin (Admin->Acc->GM Split Admin) and Client Company Admin (GM Split Rules)
-[[File:GM Split Admin 6.png]]
+[[File:Prfit split cases 0812.JPG]]
-== SOW 2 ==
+=== SOW 27 Ignore manual Int Invoices for GM Split calculations ===
-* '''0003322: [Profit Sharing Module] SOW 1 - part A - Sasha'''
-=== Run GM Split and Generate Internal Invoices ===
+'''mantis''':
-System has to generate internal invoices between offices to re-distribute profit.
+'''spec''':
-System would Run GM Split and Generate Internal Invoices periodically for example once a day.
+Change to logic below.
+If internal invoice is issued "manually" by user (from Inv tab) then GMS process should ignore amounts on that invoice and split GM as if that invoice did not exist.
+But of course that amount should be accounted for on P/L tab and P/L report.
-Exact schedule would be defined here: [[#Schedule]].
+I assume that is done to "override" standard formula in some exceptional cases.
-One invoice would cover many many CTs.
+== Release Instructions ==
-==== Algorithm for scheduled GM Split Run triggered by Scheduler ====
+Following scripts have to be applied to data during release:
-) For every  GMS status = "pending" re-calculate and update GM Split
+* [[#SOW 8 Change criteria for NA OC status ]]
-) set GMS status = "closed" for those CTs
-) create global internal interoffice invoices for above
+== Code ==
-==== Algorithm for scheduled GM Split Run triggered by change in GM Split table ====
+* ongoing UAT: https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/
-See [[#GM Split rules ]].
+=== ZULs ===
-Every time this table is changed system should recalculate GM Split all CTs subject to above time frame.
-=== Example ===
+* dir:
+** https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/internal/admin/grossMarginSplit/
-Let's look at the simple case.
+* discontinued component to post credit to Jag office
+** https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/internal/admin/grossMarginSplit/CreditWindow.zul
-Assume that GM Split Run found only one CT with GMS Status = pending.
+* "Apply changes to all CTs with departure date from ... to ..." pop-up
+** https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/internal/admin/grossMarginSplit/RecalculateGrossMarginWindow.zul
+** use="com.elco.cybertrax.internal.admin.grossMarginSplit.RecalculateGrossMarginWindow"
-Assume that this CT is CT#325563, AIR, JACKEL COSMETICS (HK) LTD from live site.
+=== Java ====
+* packages/imports:
+** package com.elco.cybertrax.internal.admin.grossMarginSplit;
+** import com.sibers.cybertrax.data.control.services.IShipmentService;
-==== Pdf mock up ====
-[[File:GM Split pdf.png]]
-=== Misc ===
+* dir:
+** https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/src/com/elco/cybertrax/internal/admin/grossMarginSplit/
- === How P/L tab is affected ===
+=== DR reps ===
- === Removing existing II functionality that conflicts with new GM split functionality ===
+https://dev-kuchma.jaguarfreight.com/repos/cybertrax/tags/2.24.0/java/CyberTrax/src/com/elco/cybertrax/common/reports/shipmentsByStatus/ShipmentsByStatusForm.java
- * no need to issue II from CT profile
- === Reporting ===
- ==== Changes to existing reports ====
- ==== New non KPI DR reports ====
- ==== New KPI DR reports ====
-== SOW 3 ==
+=== DB ===

GMS