Support Location, Product Category and Customer Type to Determine Sales and COGS
Currently, Elliott allow defining sales and COGS account by location and product category. We are facing with the following complaints:
(1) User only want to define sales account by product category, not by location. Because of the fix structure, user will have to define all combination of locations and product categories which is a burden.
(2) Sometime user may miss a combination, as a result the sales and COGS go to the default account causing reconciliation problem. System should make it easier to find potential missing setup problem.
(3) Some users want to define sales and COGS account by customer (customer type) instead of product category.
Therefore, we are proposing the following changes in Elliott V9 to make it easier and flexible for the user to define sales & COGS account in Elliott. Our thinking is we can come up with a replacement for Product Category/Location account file (CPPRDACT.) The new file CPSLSCOG will support all combinations of the following 3 variables:
We will have the following combinations:
1 = Location/Product Category/Customer Type
2 = Location/Product Category
3 = Location/Customer Type
4 = Product Category/Customer Type
5 = Location
6 = Product Category
7 = Customer Type
In the Global Setup, we will ask the default combination code to determine Sales and Cost of Good Sold. If it is not setup, it is considered “2 = Location / Product Category” which is like the current feature. We will need to handle the conversion from 7-8 database to 9.0 accordingly. The new database CPSLSCOG (COP Sales & Cost of Good Account File) will replace CPPRDACT file and will have the following fields as primary key:
Product Category Primary Key
Location Primary Key
Customer Type Primary key
User can only choose one code in global setup. For example, if user choose “7 = Customer Type”. Then the data entry for “Sales & Cost of Goods Account File” will only prompt for “Customer Type”. The value for Location and Product Category will always be blank.
User is allowed to change their sales and cost of goods account combination code from one to the other. For example, if they wish to change from code 7 to code 3. Then when they go to the user interface, system will prompt for Location and Customer Type. But system is not going to delete the old code 7 record automatically just in case user may want to change it back, or maybe still want to reference the old setup. The old codes is now become invalid, but will not cause problem staying in the database.
The printing of the edit list will allow the following parameters:
1. Starting Location
2. Ending Location
3. Starting Product Category
4. Ending Product Category
5. Starting Customer Type
6. Ending Customer Type
7. Print Invalid Setup Codes? (Default “N”)
8. Print Missing Sales & COG Setup? (Default “N”)
We allow “7. Print Invalid Setup Codes?” so users can print out the old setup for reference. At this moment, we are not providing an utility to purge them since purging is not necessary since they are harmless. If user really want to purge, we can purge for them in PSQL control center.
We allow “8. Print Missing Sales & COG Setup?” because it is often user forget to add the corresponding Sales & COGS setup when a new customer type, location or product category is added. This may be intentionally so system will default it, or a mistake of oversight. We give this flag 8 so user can verify if they have any combination of setup that they may miss. For example, if user choose “7 = Customer Type” in global setup, then we will loop through the customer type file and find out if any customer type have a corresponding record in CPSLSCOG file. If user choose “3 = Location/Customer Type”, then we look through every combination of location and customer type to see if the corresponding record is missing. We will print the missing records like a regular record, but in the sales and cogs area show “ Missing ”. We will finalize the layout before implementing.
Also, we should print in the grand total areas:
999,999 Records Printed
999,999 Invalid Records Printed (if flag 7 is setup to “Y” and invalid account codes > 0)
999,999 Records Missing Setup (if flag 8 is set to “Y” and records missing setup > 0)
We will also add a Global (Management) Event that support the following variables:
Record Number (Value always = 1)
Default Sales Account (From ARCTLFIL)
Default COGS Account (From CPCTLFIL)
All Locations Chosen Base on screen parameters and have value “Y”, “N” or blank if not applicable per global setup
All Category Chosen Base on screen parameters and have value “Y”, “N” or blank if not applicable per global setup
All Customer Type Chosen Base on screen parameters and have value “Y”, “N” or blank if not applicable per global setup
Total Records Printed
Total Invalid Records Printed If flag 7 is set to “Y”, otherwise 0
Total Records Missing Setup If flag 8 is set to “Y”, otherwise 0
The purpose is so that admin can subscribe to this event by filtering when “Total Records Missing Setup” > 0. Then admin can use defer processing to run this on the daily basis. If user miss the setup, then an email will be sent to warn the admin.
We will also modify CP0305.CBL to reflect the new logic.
Thomas Woody - Record USA, Inc. commented
I would like to see this follow a cascading scenario similar to the way price codes are applied. For example, if I define Sales & COGS for a product category ONLY - then that applies _by default_ regardless of the location sold from. If I then define, for example, Sales & COGS for a product category in location 01, then that applies if that Product Category/Location combination is met, but transactions against all other locations drop down to the Product Category only entry.
This would allow for a tremendous amount of flexibility for complex accounting environments, while also accommodating simpler environments with a minimum number of entries (less that currently required).
Allowable combinations and order of application could be defined in setup, mimicking Price Code Entry.