Today we are privileged to have our first guest Tech Tuesday author, Janeece Moreland, Managing Consultant/Business Process Change Agent for Conexus SG, sharing her insights into Sales Order imports with GL Distributions.
To learn more about Janeece and Conexus, please take a look at their bios at the end of the article.
The purpose of this example is to provide the technical user with the details of how to create a map with a SQL Select statement and group data appropriately on the detail map functions.
The original map provides Distribution lines, EFT imports, Post Import SQL tasks and Rolling columns. We will be exploring only one portion of the map in this article.
Problem Statement: A user has provided you with an excel spreadsheet with Customer, item description, line amount, GL account numbers and distribution references to be imported as Invoices.
Issue: When you have one source line of data you will need an Accounts Receivable offset account and group the data for 1 invoice, multiple line items and multiple GL distributions.
Example: Our example today will create a Sales Invoice, Line Items, and distribution lines with distribution references. The complication in the data is the distribution lines normally sum based on the account number on the item but we are supplying the account number instead. In addition to the GL distribution, we want to provide the distribution reference which will be shown in the financial detail when the Sales Transaction is posted in the General Ledger. The Customer Number will be used to group the data on the Create Sales Transaction.
First, we will look at the ODBC connection.
The data is provided in an Excel file format but we want to control the fields that will be used. The following selections were made:
Data Source: ODBC Connection.
Connection Type: Custom Connection
Connection String:
Excel Workbook:
Key Fields: CustNbr
Select Workbook
Select the Drive+Path+Name of File
Our next step will be to select the data from the Excel spreadsheet.
This will be entered in the data source. An explanation of each field and its use is listed below:
The Statement accomplishes the following functions:
- TrxType – It establishes the type of transaction. The assumption is that all these transactions are invoices. Additional select statement functions can be added to create Returns if needed.
- Customer Number – used for the SOP Header and the Payment
- Invoice Date – Used for the SOP Header
- Item Number – used for the SOP lines
- Quantity – used for the SOP Line
- Item Desc – used for SOP Line
- Unit Price – used for the SOP Line
- Document Amount – used for the SOP Header, Payment and Cash Distribution
- EFT Date – used on the Payment map
- Distribution Reference – this could have been concatenated from the Item and Item Desc but the user wanted to have the flexibility to use it for additional maps. If the user types over the GP field length of 30 characters, the select will truncate the text.
- GL Account – used on the Distribution map
- DistType – used on the Distribution map
- Unit Price – used for sop line and distribution map
- Credit and Debit Amounts are used on the distribution map
- The UNION statement creates another line item that will be used for the distribution.
SELECT 3 AS TrxType, CustNbr,[Invoice Date],[Item Number],[Item Desc],[Quantity], [Unit Price] ,[DocumentAmount],[EFT Date],LEFT([Distribution Reference],30) AS DRef,
[GL ACCOUNT NUMBER] AS AccountString, 1 AS DistType,
[Unit Price] AS CreditAmount, 0 AS DebitAmount
FROM [Import$]
WHERE [DocumentAmount]> 0
UNION
SELECT 3 AS TrxType,
CustNbr,[Invoice Date],[Item Number],[Item Desc],[Quantity], [Unit Price] ,[DocumentAmount],[EFT Date],’EFT’ AS DRef,
‘10700-5101-00000-00’ AS AccountString,3 AS DistType,
0 AS CreditAmount,[DocumentAmount] AS DebitAmount
FROM [Import$]
WHERE [DocumentAmount]> 0
Grouping Data
Grouping Data imports the data into the GP Sales Invoice by Customer Number based on the default set on the high level map.
MAP: Create Sales Transaction
Notice that the Group data is checked but the CustNbr is not checked. The default is the Key field from the Data Source.
MAP: Add line item
Customer Number and Item Description are selected in this map. The item description was selected since the item number does not exist in the Inventory. The items are non-inventory items. The default is the Key field from the Data Source.
About the Author:
Janeece began her consulting career in 1987 working as the IT manager for the largest independent software distributor at the time in the US, Software Spectrum. She then worked in Sales at International Business Machines (IBM). From IBM she then moved to Platinum Software Corporation, now known as Epicor Software. At Platinum Software, Ms. Moreland served as a member of the SWAT consulting team covering the United States working with difficult installations as a problem solver. She joined Oracle in 1996 as a part of sales and consulting group focused on the Utilities Industry then moved to back into as an Oracle Sales Manager. Now a managing partner in the Microsoft Business Applications consulting world she manages varying projects but focuses on integrations from varying operational systems into Accounting applications and Manufacturing implementations. Her broad Applications software experience includes: Financial Accounting, Financial Analysis, Project Accounting (Costing), Distribution and Manufacturing Planning systems.
To contact Janeece, please email jmoreland@marksmangroup.com.
About Conexus SG:
Conexus SG specializes in financial systems and process consulting. A significant portion of their business revolves around the implementation, support, enhancement and upgrade of Microsoft ERP (accounting) software such as Dynamics GP (formerly Great Plains), Dynamics AX and Dynamics SL (formerly Solomon). To learn more about Conexus SG, visit their website: www.conexussg.com.