Adding Flexibility to Sales Account Assignments, a step toward multi-company in a single xTuple database

Hey! How have you all been?! I've stepped back into my cubicle and will attempt to grace the xTuple Blog-o-sphere once again with something I think EVERYONE may find interesting.

Sales Account Assignments — In the Beginning...

Setting up Sales Account Assignments within the bounds of Site, Product Category and Customer Type is more than sufficient to meet the needs of most organizations. However, in some circumstances a company may sell the same products in many different ways. Depending on how much detail a particular Controller wants to see in the General Ledger, they may have a need to book sales to differing accounts by the Type of Sale or the Region/Area the product was shipped into.

In xTuple, a Customer can only have one Customer Type. Sales Account Assignments can use this Customer Type to drive sales to particular GL Account Assignments based on the Site, the Product Category and the Customer Type. This does run into its limitations... quickly. One work-around in the past was to duplicate your customer. This can make things messy. Very Messy if you have many different ways to Sell a product and you want that detail in your GL. This leads to an Unwieldly Matrix of Sales Account Assignments and Duplicate Customers, Nightmares, Headaches and Nausea.

Another workable solution was to use Pattern Matching on the Customer Type, which — isn't very fun either (unless you enjoy Regular Expressions) — in addition to having to create all sorts of Customer Type patterns and maintain those on a per-Customer basis.

Not to mention that you can have multiple Companies set up in an xTuple database. You would have to create that Customer for EACH Company segment that they could conceivably buy your product from. It becomes very complicated to even think about trying — (Don't try it... I tried!). I retract what I said... This isn't workable, and isn't a solution.

This led our xTuple Team to determine and define the problem thusly...

Currently, the existing mechanisms to steer revenue to alternate revenue accounts is limited by the matrix created in the Sales Account Assignments screen. The current decision tree to select the appropriate GL account is a combination of SITE, CUSTOMER TYPE, and PRODUCT CATEGORY.

The limitation to this arises if an organization has a notion of 'Sale Type', whereby the context of a Sale drives revenue to differing GL accounts. Currently, depending on the type of sale one must create iterations of Items and Customers to drive the revenue to the proper accounts. This creates a Matrix that can get Unwieldy depending on the number of Customers, Sites, and Product Categories to cover the organization's definitions of Sales Types for which they want to book revenue in a particular fashion.

If the organization books revenue based on geographic sales — Regions, Shipping Zones, Counties, Zip Codes, IP Address, etc., it would follow that re-purposing the existing construct of Shipping Zone as a criteria to Sales Account Assignments would be generally useful.

Using an automobile example, this is similar to going to a dealership as a private person and buying a part vs. being Joe's Garage and buying the same part. The Joe's Garage sale might end up in a different GL account than the sale to the private person/retail sale. They don't create a duplicate part number or customer number. They just specify which type of sale it was — Retail Customer or Partner.

We pondered this for a while and realized that there might be a solution...

Extending the Sales Account Assignments to make use of the 'Sale Type' and 'Shipping Zones' adds a layer of flexibility which in effect relieves the need to create an Unwieldy Matrix. This simplifies setup immensely by alleviating the need to create multiple customers, products, etc.

Including 'Sale Type' and 'Shipping Zone' on the Sales Account Assignments screen would allow the following sample scenario:

On the Sales Account Assignments screen, define three different 'Sale Type' to be used at 3 different locations. The locations would be based on the 'Shipping Zone' (sales offices, sales areas, geographic reference) where sales revenue, costs, etc. are to be tracked for each of these types of sales.

On the Sales Order screen, we have re-purposed the 'Originated By' to be 'Sales Type'. Previously, this was hard-coded in the GUI and was primarily 'information only'. With the salestype enhancement package, this is configurable to cover all of your Sales Types needs.

Sales Type (old Originated By):

• In-network sale
• Out-of-network sale
• Internet
• Sales Rep

This enhancement also repurposes 'Shipping Zones' for use as Area or Region designations. It uses the core shipping zone table. — public.shipzone. Shipping Zone is tied to a Ship-To Address. Once these are set up in the address table, the likelyhood of a customer service agent selecting the wrong Shipping Zone (REGION) is eliminated, because they're only going to be able to select the available Ship-To addresses. These relationships should be setup prior.

Shipping Zones:

• NYC-New York City
• SEA-South East Asia
• BSM-Black Sea Mediterranean
• NOR-Norfolk, VA

— Disclaimers — 

This enhancement also allows the sale to be booked into different Companies. While it does not provide true Multi-Company from within a single database, it is a step in that direction. (and might be all you want).

This enhancement does NOT address A/P, just A/R.

This enhancement is in Beta.

— End of Disclaimers — 

So — Pretty Exciting Stuff eh?! I think so. And you should too! I will continue this blog to describe the GL Transactions in some more detail soon! Stay Tuned for Part II!

Click HERE for the large graphic to get a bigger and better look. The colors are coded to their relevent parts. BLUE and GREEN will be addressed in Part II.