Client Company

From UG

(Difference between revisions)
Jump to: navigation, search
(Solution)
 
(24 intermediate revisions not shown)
Line 3: Line 3:
== Info ==
== Info ==
-
* Classified As:  (large) [[component]] 
 
* Parent Mantis: [http://mantis.jaguarfreight.com/mantis/view.php?id=601 601]
* Parent Mantis: [http://mantis.jaguarfreight.com/mantis/view.php?id=601 601]
-
== Business Requirements ==
+
== Business Need ==
 +
 
 +
'''Client Company''' is one of the major entities in the system.
 +
 
 +
Normally it is a company that ordered 3PL services from Jaguar. As a result Jaguar would have some kind of service contract (with key rules identified such as incoterms and rates) and will bill that company for services.
 +
 
 +
It is often used as a major filter. And  often set up/defined with this purpose - see (a) below.
 +
 
 +
Note that it is a bit more complicated because:
 +
 
 +
* [[Bill To Party]] is not always the same name as Client Company (it could be one of its associated offices or divisions for example)
 +
 
 +
* One company (as registered by State and uniquely identified by the same internet domain name) could be presented in CT2 by multiple values. This is to distinguish between different types of moves. For example Elizabeth Arden is represented by many E0's:
 +
 
 +
Elizabeth Arden
 +
Elizabeth Arden AU
 +
Elizabeth Arden PR
 +
Elizabeth Arden SA
 +
EA DOM PORTAL
 +
EA USA Domestic
 +
etc .....
== Solution v1 ==
== Solution v1 ==
Line 20: Line 39:
=== Client Company Group ===
=== Client Company Group ===
-
[[File:Client Company group.JPG]]
+
[[File:Client Company group.JPG | 600px]]
=== Commissions functionality ===
=== Commissions functionality ===
Line 36: Line 55:
*** [N] - go back to edit mode
*** [N] - go back to edit mode
-
== History ==
+
== SOWs ==
-
=== m780 ===
+
=== SOW 1 ===
* 0000780: (*ph1)(Admin Client Companies) Implement "archive" function feature  
* 0000780: (*ph1)(Admin Client Companies) Implement "archive" function feature  
* implement "standard CT2 archive functionality"
* implement "standard CT2 archive functionality"
Line 45: Line 64:
** show it with everywhere else (with archive logo or print "archived" next to the name) where we have Clients lists
** show it with everywhere else (with archive logo or print "archived" next to the name) where we have Clients lists
-
=== m1878 ===
+
=== SOW 2 ===
* see [[#Commissions functionality]]
* see [[#Commissions functionality]]
 +
 +
=== SOW 3 Client Company to Bill To link ===
 +
 +
mant: 4078
 +
 +
spec:
 +
 +
===== Core Need =====
 +
 +
To identify what charges are associated  with specific Client Company E0.
 +
 +
Per Marc under given CT operator might create invoices of two types:
 +
 +
* a) invoice that bills Client Company (E0) XYZ (using one of several possible associated with this company Bill To entities/addresses in the system)
 +
 +
* b) invoice that Jaguar issues to collect money for its services from another company (that is not Client Company E0 or its division).
 +
 +
NOTE: ideally need further investigation here and real examples
 +
 +
This will be used in reporting. Costs of type "b" will not show on total cost report for Client Company XYZ. See for example [[KPI#SOW 52 Cost v2]] wiki.
 +
 +
===== Solution =====
 +
 +
Add list of Bill To Parties as an attribute to Client Company entity.
 +
 +
Relationship between [[Client Company]] and Bill To is many to many. (two different Client companies can link to same Bill to Party)
 +
 +
===== Mock Up =====
 +
 +
[[File:Bill to.JPG]]
 +
 +
== DB ==
 +
 +
<pre>
 +
mysql> select curdate(); describe tblClientCompany;                 
 +
+------------+
 +
| curdate()  |
 +
+------------+
 +
| 2013-05-15 |
 +
+------------+
 +
1 row in set (0.00 sec)
 +
 +
 +
+--------------------------------+-------------+------+-----+---------+----------------+
 +
| Field                          | Type        | Null | Key | Default | Extra          |
 +
+--------------------------------+-------------+------+-----+---------+----------------+
 +
| Id                            | int(10)    | NO  | PRI | NULL    | auto_increment |
 +
| CompanyName                    | varchar(50) | NO  |    | NULL    |                |
 +
| InternalOpsGroupId            | int(10)    | YES  | MUL | NULL    |                |
 +
| SalesPersonId                  | int(10)    | YES  | MUL | NULL    |                |
 +
| CreditStatusId                | int(10)    | YES  | MUL | NULL    |                |
 +
| CreditLimitId                  | int(10)    | YES  | MUL | NULL    |                |
 +
| CreditTermId                  | int(10)    | YES  | MUL | NULL    |                |
 +
| DisplayExchangeRates          | tinyint(1)  | NO  |    | 0      |                |
 +
| ShowDeliveryLocation          | tinyint(1)  | NO  |    | 0      |                |
 +
| IsArchived                    | bit(1)      | NO  |    | b'0'    |                |
 +
| IsCT2Numbering                | bit(1)      | NO  |    | b'1'    |                |
 +
| IsPOIssuing                    | bit(1)      | NO  |    | b'0'    |                |
 +
| IsShowReportScheduler          | bit(1)      | NO  |    | b'0'    |                |
 +
| EnableDailyReport              | bit(1)      | NO  |    | b'0'    |                |
 +
| SendToTrendset                | bit(1)      | NO  |    | b'0'    |                |
 +
| AccClientCompanyGroupId        | int(10)    | YES  | MUL | NULL    |                |
 +
| OwnerOfficeId                  | int(10)    | YES  | MUL | NULL    |                |
 +
| UseDefaultGrossMarginSplitRule | bit(1)      | NO  |    | b'1'    |                |
 +
| GrossMarginSplitRuleId        | int(10)    | YES  | MUL | NULL    |                |
 +
| IsGmsExcluded                  | bit(1)      | NO  | MUL | b'0'    |                |
 +
+--------------------------------+-------------+------+-----+---------+----------------+
 +
20 rows in set (0.00 sec)
 +
 +
mysql> show create table tblClientCompany;
 +
 +
| tblClientCompany | CREATE TABLE `tblClientCompany` (
 +
  `Id` int(10) NOT NULL AUTO_INCREMENT,
 +
  `CompanyName` varchar(50) NOT NULL,
 +
  `InternalOpsGroupId` int(10) DEFAULT NULL,
 +
  `SalesPersonId` int(10) DEFAULT NULL,
 +
  `CreditStatusId` int(10) DEFAULT NULL,
 +
  `CreditLimitId` int(10) DEFAULT NULL,
 +
  `CreditTermId` int(10) DEFAULT NULL,
 +
  `DisplayExchangeRates` tinyint(1) NOT NULL DEFAULT '0',
 +
  `ShowDeliveryLocation` tinyint(1) NOT NULL DEFAULT '0',
 +
  `IsArchived` bit(1) NOT NULL DEFAULT b'0',
 +
  `IsCT2Numbering` bit(1) NOT NULL DEFAULT b'1',
 +
  `IsPOIssuing` bit(1) NOT NULL DEFAULT b'0',
 +
  `IsShowReportScheduler` bit(1) NOT NULL DEFAULT b'0',
 +
  `EnableDailyReport` bit(1) NOT NULL DEFAULT b'0',
 +
  `SendToTrendset` bit(1) NOT NULL DEFAULT b'0',
 +
  `AccClientCompanyGroupId` int(10) DEFAULT NULL,
 +
  `OwnerOfficeId` int(10) DEFAULT NULL,
 +
  `UseDefaultGrossMarginSplitRule` bit(1) NOT NULL DEFAULT b'1',
 +
  `GrossMarginSplitRuleId` int(10) DEFAULT NULL,
 +
  `IsGmsExcluded` bit(1) NOT NULL DEFAULT b'0',
 +
 
 +
  PRIMARY KEY (`Id`),
 +
  KEY `tblClientCompany_ibfk_1` (`InternalOpsGroupId`),
 +
  KEY `tblClientCompany_ibfk_2` (`SalesPersonId`),
 +
  KEY `tblClientCompany_ibfk_3` (`CreditStatusId`),
 +
  KEY `tblClientCompany_ibfk_4` (`CreditLimitId`),
 +
  KEY `tblClientCompany_ibfk_5` (`CreditTermId`),
 +
  KEY `AccClientCompanyGroupId` (`AccClientCompanyGroupId`),
 +
  KEY `OwnerOfficeId` (`OwnerOfficeId`),
 +
  KEY `GrossMarginSplitRuleId` (`GrossMarginSplitRuleId`),
 +
  KEY `IsGmsExcluded` (`IsGmsExcluded`),
 +
 +
  CONSTRAINT `fk_AccClientCompanyGroupId` FOREIGN KEY (`AccClientCompanyGroupId`) REFERENCES `tblAccClientCompanyGroup` (`Id`) ON UPDATE CASCADE,
 +
  CONSTRAINT `tblClientCompany_fk` FOREIGN KEY (`OwnerOfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
 +
  CONSTRAINT `tblClientCompany_fk1` FOREIGN KEY (`GrossMarginSplitRuleId`) REFERENCES `tblGrossMarginSplitRule` (`Id`) ON UPDATE CASCADE,
 +
  CONSTRAINT `tblClientCompany_ibfk_1` FOREIGN KEY (`InternalOpsGroupId`) REFERENCES `tblJaguarUserGroup` (`Id`),
 +
  CONSTRAINT `tblClientCompany_ibfk_2` FOREIGN KEY (`SalesPersonId`) REFERENCES `tblJaguarUser` (`Id`),
 +
  CONSTRAINT `tblClientCompany_ibfk_3` FOREIGN KEY (`CreditStatusId`) REFERENCES `tblCreditStatus` (`Id`),
 +
  CONSTRAINT `tblClientCompany_ibfk_4` FOREIGN KEY (`CreditLimitId`) REFERENCES `tblCreditLimit` (`Id`),
 +
  CONSTRAINT `tblClientCompany_ibfk_5` FOREIGN KEY (`CreditTermId`) REFERENCES `tblCreditTerm` (`Id`)
 +
) ENGINE=InnoDB AUTO_INCREMENT=1987 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT COMMENT='InnoDB free: 9216 kB; (`InternalOpsGroupId`) REFER `cybertra' |
 +
 +
 +
</pre>

Current revision as of 05:15, 31 May 2013


Contents

[edit] Info

  • Parent Mantis: 601

[edit] Business Need

Client Company is one of the major entities in the system.

Normally it is a company that ordered 3PL services from Jaguar. As a result Jaguar would have some kind of service contract (with key rules identified such as incoterms and rates) and will bill that company for services.

It is often used as a major filter. And often set up/defined with this purpose - see (a) below.

Note that it is a bit more complicated because:

  • Bill To Party is not always the same name as Client Company (it could be one of its associated offices or divisions for example)
  • One company (as registered by State and uniquely identified by the same internet domain name) could be presented in CT2 by multiple values. This is to distinguish between different types of moves. For example Elizabeth Arden is represented by many E0's:
Elizabeth Arden
Elizabeth Arden AU
Elizabeth Arden PR
Elizabeth Arden SA
EA DOM PORTAL
EA USA Domestic
etc .....

[edit] Solution v1

Current!

[edit] List of companies and company profile

File:Client companies v1.JPG

File:Client companies profile v1.JPG

[edit] Client Company Group

[edit] Commissions functionality

[edit] Sales Person

Jag user who receives commissions for this Company's profit.

[edit] Sales Person Start Day

Day from which Sales Person receives his commission.

  • If Sales Person is set then this date must be set. Produce error pop up on save if not: "Error: Sales Person Start Day must be set! [OK]"
  • If Sales Person is changed but date remains the same (check on Save) produce Warning pop up on save:
    • "Warning! Sales Person is changed but Sales Person Start Day remains the same! Are you sure? [Y][N]"
      • [Y] - continue, save
      • [N] - go back to edit mode

[edit] SOWs

[edit] SOW 1

  • 0000780: (*ph1)(Admin Client Companies) Implement "archive" function feature
  • implement "standard CT2 archive functionality"
  • if Client Company is archived then:
    • hide it on Create CT page
    • show it with everywhere else (with archive logo or print "archived" next to the name) where we have Clients lists

[edit] SOW 2

[edit] SOW 3 Client Company to Bill To link

mant: 4078

spec:

[edit] Core Need

To identify what charges are associated with specific Client Company E0.

Per Marc under given CT operator might create invoices of two types:

  • a) invoice that bills Client Company (E0) XYZ (using one of several possible associated with this company Bill To entities/addresses in the system)
  • b) invoice that Jaguar issues to collect money for its services from another company (that is not Client Company E0 or its division).
NOTE: ideally need further investigation here and real examples

This will be used in reporting. Costs of type "b" will not show on total cost report for Client Company XYZ. See for example KPI#SOW 52 Cost v2 wiki.

[edit] Solution

Add list of Bill To Parties as an attribute to Client Company entity.

Relationship between Client Company and Bill To is many to many. (two different Client companies can link to same Bill to Party)

[edit] Mock Up

File:Bill to.JPG

[edit] DB

mysql> select curdate(); describe tblClientCompany;                  
+------------+
| curdate()  |
+------------+
| 2013-05-15 |
+------------+
1 row in set (0.00 sec)


+--------------------------------+-------------+------+-----+---------+----------------+
| Field                          | Type        | Null | Key | Default | Extra          |
+--------------------------------+-------------+------+-----+---------+----------------+
| Id                             | int(10)     | NO   | PRI | NULL    | auto_increment |
| CompanyName                    | varchar(50) | NO   |     | NULL    |                |
| InternalOpsGroupId             | int(10)     | YES  | MUL | NULL    |                |
| SalesPersonId                  | int(10)     | YES  | MUL | NULL    |                |
| CreditStatusId                 | int(10)     | YES  | MUL | NULL    |                |
| CreditLimitId                  | int(10)     | YES  | MUL | NULL    |                |
| CreditTermId                   | int(10)     | YES  | MUL | NULL    |                |
| DisplayExchangeRates           | tinyint(1)  | NO   |     | 0       |                |
| ShowDeliveryLocation           | tinyint(1)  | NO   |     | 0       |                |
| IsArchived                     | bit(1)      | NO   |     | b'0'    |                |
| IsCT2Numbering                 | bit(1)      | NO   |     | b'1'    |                |
| IsPOIssuing                    | bit(1)      | NO   |     | b'0'    |                |
| IsShowReportScheduler          | bit(1)      | NO   |     | b'0'    |                |
| EnableDailyReport              | bit(1)      | NO   |     | b'0'    |                |
| SendToTrendset                 | bit(1)      | NO   |     | b'0'    |                |
| AccClientCompanyGroupId        | int(10)     | YES  | MUL | NULL    |                |
| OwnerOfficeId                  | int(10)     | YES  | MUL | NULL    |                |
| UseDefaultGrossMarginSplitRule | bit(1)      | NO   |     | b'1'    |                |
| GrossMarginSplitRuleId         | int(10)     | YES  | MUL | NULL    |                |
| IsGmsExcluded                  | bit(1)      | NO   | MUL | b'0'    |                |
+--------------------------------+-------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)

mysql> show create table tblClientCompany;

| tblClientCompany | CREATE TABLE `tblClientCompany` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `CompanyName` varchar(50) NOT NULL,
  `InternalOpsGroupId` int(10) DEFAULT NULL,
  `SalesPersonId` int(10) DEFAULT NULL,
  `CreditStatusId` int(10) DEFAULT NULL,
  `CreditLimitId` int(10) DEFAULT NULL,
  `CreditTermId` int(10) DEFAULT NULL,
  `DisplayExchangeRates` tinyint(1) NOT NULL DEFAULT '0',
  `ShowDeliveryLocation` tinyint(1) NOT NULL DEFAULT '0',
  `IsArchived` bit(1) NOT NULL DEFAULT b'0',
  `IsCT2Numbering` bit(1) NOT NULL DEFAULT b'1',
  `IsPOIssuing` bit(1) NOT NULL DEFAULT b'0',
  `IsShowReportScheduler` bit(1) NOT NULL DEFAULT b'0',
  `EnableDailyReport` bit(1) NOT NULL DEFAULT b'0',
  `SendToTrendset` bit(1) NOT NULL DEFAULT b'0',
  `AccClientCompanyGroupId` int(10) DEFAULT NULL,
  `OwnerOfficeId` int(10) DEFAULT NULL,
  `UseDefaultGrossMarginSplitRule` bit(1) NOT NULL DEFAULT b'1',
  `GrossMarginSplitRuleId` int(10) DEFAULT NULL,
  `IsGmsExcluded` bit(1) NOT NULL DEFAULT b'0',
  
  PRIMARY KEY (`Id`),
  KEY `tblClientCompany_ibfk_1` (`InternalOpsGroupId`),
  KEY `tblClientCompany_ibfk_2` (`SalesPersonId`),
  KEY `tblClientCompany_ibfk_3` (`CreditStatusId`),
  KEY `tblClientCompany_ibfk_4` (`CreditLimitId`),
  KEY `tblClientCompany_ibfk_5` (`CreditTermId`),
  KEY `AccClientCompanyGroupId` (`AccClientCompanyGroupId`),
  KEY `OwnerOfficeId` (`OwnerOfficeId`),
  KEY `GrossMarginSplitRuleId` (`GrossMarginSplitRuleId`),
  KEY `IsGmsExcluded` (`IsGmsExcluded`),
 
  CONSTRAINT `fk_AccClientCompanyGroupId` FOREIGN KEY (`AccClientCompanyGroupId`) REFERENCES `tblAccClientCompanyGroup` (`Id`) ON UPDATE CASCADE,
  CONSTRAINT `tblClientCompany_fk` FOREIGN KEY (`OwnerOfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
  CONSTRAINT `tblClientCompany_fk1` FOREIGN KEY (`GrossMarginSplitRuleId`) REFERENCES `tblGrossMarginSplitRule` (`Id`) ON UPDATE CASCADE,
  CONSTRAINT `tblClientCompany_ibfk_1` FOREIGN KEY (`InternalOpsGroupId`) REFERENCES `tblJaguarUserGroup` (`Id`),
  CONSTRAINT `tblClientCompany_ibfk_2` FOREIGN KEY (`SalesPersonId`) REFERENCES `tblJaguarUser` (`Id`),
  CONSTRAINT `tblClientCompany_ibfk_3` FOREIGN KEY (`CreditStatusId`) REFERENCES `tblCreditStatus` (`Id`),
  CONSTRAINT `tblClientCompany_ibfk_4` FOREIGN KEY (`CreditLimitId`) REFERENCES `tblCreditLimit` (`Id`),
  CONSTRAINT `tblClientCompany_ibfk_5` FOREIGN KEY (`CreditTermId`) REFERENCES `tblCreditTerm` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1987 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT COMMENT='InnoDB free: 9216 kB; (`InternalOpsGroupId`) REFER `cybertra' |


Personal tools