GMS

From UG

Revision as of 15:24, 3 April 2012 by Alex (Talk | contribs)
Jump to: navigation, search


Contents

Info

Mantis

  • 0003321 [Profit Sharing Module]

Scope

Covers this one module - Profit Sharing Module.

Glossary

Business Requirements 1

See GM Split Requirements.

OC Functionality

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 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.

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.

OC Status Values

  • n/a
for CTs closed before 2012
  • 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


There are three ways to set this status: automatically, manually and by script.

Setting OC status automatically

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

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.
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 (maybe 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

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

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

Display OC status on Gen Tab

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.

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)

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.


GMS section in Admin

Add number of configuration options - see below.

Add under Acc > GM Split.

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.

File:Prfit split cases.JPG

One more GMS case

Business requested one more case see case#6 above.

Example:
00      =       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.

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.


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.

File:GMD Descr.JPG

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

Credit to the office

Per Karen this is no longer needed

Limitations on pending GMS and closed files

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:

  • 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

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
  • 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
  1. create: CT: Air, OO=NY, EO=NY, IO=HK, TO=none, with GMS case#: Case 1: (OO : US = 60%, A : HK = 40%)
  2. set: NY issued/received 100 USD to Client
  3. set: NY paid/posted 90 USD from Vendor
  4. set: delivery date
  5. close CT
  6. verify: GMS status is set to: pending
  7. run: GM Splitter
  8. verify: P/L tab should show profits: 10USD for Jaguar, 6 for NY and 4 for HK
  9. verify: Inv Tab should have: Indiv Inv for 4USD from HK to NY
  10. 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:

  1. open CT
  2. post 1USD purchase for NY
  3. close
  4. run splitter
  5. verify: NY billed HK for 0.40 USD
  6. verify new P/L
Example 1c
  1. open CT
  2. change GMS % to 50/50
  3. close
  4. run splitter
  5. verify: HK billed NY for 0.90 USD
  6. verify new P/L
Example 2
  1. create CT with GMS Case 5: (OO : US = 100%)
  2. close/deliver
  3. run splitter
  4. 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
  • NY/Lon
  • NY/Par
  • HK/Lon
  • HK/Par
  • Lon/Par

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

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.

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.

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

CT Tabs

Inv Tab

No change.

P/L Tab

Add link to Consol Inv. See below.

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.

GMS Misc

Display OO, EO, IO, GMS Status, GMS Case on Gen Tab

See mock up below, Red Box B.

UC: How to apply new GMS split rules to GMS completed CTs

'!!! Spec changed !!!

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).

VAT should be excluded

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.

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

Recalculate exiting records in case ratios 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.
  • 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

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

Add new error reason/type: old RefNum

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

Personal tools