Overview 

RedSky users have an option to integrate financial processing done in RedSky with their Financial Application. This document describes the integration between the two systems and the methodology followed to interface between RedSky and Accounting Packages. Please keep in mind that integration is a customization service done by RedSky Technical team and based on experience will take between 2-3 weeks of programming time from RedSky team and a corresponding time from the Financial Application Consultant. Specifically, RedSky Technical team will provide the interfaces from RedSky, the integration between that interface and Financial Application is normally handled by the client’s consultant.

This document describes the integration done for MS-Great Plains using flat files. This document explains how Great Plains is used as a Financial Package in a client company where all corporate accounting functions are performed e.g. general ledgers,receivables, payables etc.

Managing interface between RedSky and Great Plains 

There are 3 setup items that need to be managed between the two systems: 

Vendor Setup

There are two options in RedSky: the vendor does not exist in RedSky or vendor exists. The steps to manage both are provided.

When there is a new Vendor who needs to be paid

Whenever there is a new vendor that needs to paid, they need to be setup as a vendor in Great Plains first. There will be a vendor code generated in Great Plains (Example: 30394).

• User then needs to log into RedSky and select Administration -> Partner
• Selects the type of Partner which needs to be added

Figure 1

• Enters the details as entered in Great Plains

Figure 2

• Saves the record by clicking on save button
• Then searches for the partner just added, edits and selects “Acct Ref” tab

Figure 3

• Click on the Add button
• Select the type Payable and enter the Great Plains vendor code here

Figure 4

When there is a existing Vendor code to be updated in RedSky

• User then needs to log into RedSky and selects Administration -> Partner
• Search by the name of the partner you are looking for
• Repeat steps as shown in figure 3 and figure 4.

Two way Integration 

RedSky can integrate with the Great Plains accounting data in two ways:

1. RedSky - Great Plains

All the Payables and Receivables are extracted from RedSky to be uploaded in Great Plains

2. Great Plains - RedSky

From Great Plains, details of Receivable (Payment Status paid/Not paid...) and Payables (check number / status) will be uploaded in RedSky

Integration from RedSky -> Great Plains

From RedSky -> Great Plains there are four files which need to be uploaded into Great Plains: 

1. Invoice File
2. Payable File
3. Partner File
4. Subcontractor File

The client needs to designate a user in Central Accounting function to run the Extract daily from RedSky, using the following functions (typically should be done either at the beginning of the work day or at the end, but the recommendation is that this is done at a fixed time daily).

They will perform this action as follows:

User selects Finance -> “Great Plains Interface”

Figure 5

Step 1: User first clicks on “Partner Data Extract”

Figure 6

• This option allows the user to extract partner data, specifically all the data for the bill-to-codes that will be used in the invoice extract. The system provides multiple options to extract this data, e.g. they need to provide start date, end date and company division. The menu currently gives a screen which allows the partners to be downloaded. A flat file is created from the system which is uploaded into Great Plains.
• The system looks up all partners whose records have been either created or modified during the date range and extracts it into the flat file. We suggest that they use the first option to enter the dates as follows:

                o   Yesterday’s date to today’s date

Figure 7

Figure 8

There are 4 scenarios for the partner update:

Scenario 1- Partner Data Extract

The system looks up all partners whose records have been either created or modified during the date range and extracts it into the flat file.

Scenario 2- Partner Data Extract with Invoice file 

The second scenario is to help the user when he /she has forgotten to extract partners, from the invoice file where bill to code for invoices is the same as partner code for all invoices mentioned in the invoice extract name. This will allow the user to enter a previously run invoice extract name, and get the partner file for the distinct bill to in the file.

Scenario 3- Partner Data Extract with Synchronize invoice

This will allow the user to extract the partner's data whose invoice has been generated after the “From Date”. Distinct partners will be fetched. Distinct bill to code for invoices will be picked and partner table will be queried for those records where partner code = bill to code.

Scenario 4- Agent Data Extract

This will allow user to extract only those partners which are of type agent (records whose agent field has value set as true) and works similar to Scenario 1 of the Partner Data Extract.

Scenario 1 and Scenario 4 need to be executed on an everyday basis (recommended as a Best Practice). Extract these files and save these file in a common location where Finance people can access them.

Step 2: Invoice Data Extract

Figure 9

The above extract will show all the available invoice lines, grouped by company division and posting dates.

Figure 10

Figure 11

Figure 12

Once the Invoice Data Extract has been run, the Actual Revenue amount is highlighted in Green

Figure 13

Step 3: Payable Data Extract

•  This option will post all the bills received from vendors to Great Plains. User would need to select the company division and then extract the data. The following screen will appear, if user clicks on the Payable Data Extract:

Figure 14

Once the Payable Data Extract has been run, the Expense amount is highlighted in Green

Figure 15

This account line will be updated with the ‘Sent to Accounting date’ field which is usually the Sysdate (date extract was run). The Xfer field will have the name of the flat file created and the username that generated that file will be recorded as Pay Xfer User, as shown below:

Figure 16

Step 4: Subcontractor Pay Extract

Figure 17

Extracts the payable data for subcontractors to Great Plains.

Step 5: These files need to be uploaded into GP system

It is recommended that all the files extracted in Step 1 to Step 4 are saved in the same folder where all Finance people can access the files.

Figure 18

Integration from Great Plains -> RedSky

Step 6: Reverse Integration

Accounting users goes to the GP system and extract the payable and receivable reverse integration files. These files needs to be uploaded in RedSky using the menu:

Figure 19

The user clicks on “Great Plains Pmt Update” and uploads the Payables and Receivables files, which updates the respective account lines.

Figure 20

Once the Payables and Receivables Uploads are processed, the Revenue and Expense amounts on the respective account lines are highlighted in Blue:

Figure 21

An overview of the types of data that are interchanged between the two systems is shown below:

 LEVEL1,"IN","R507955","T39224","11/10/2011","3813.09","JANICE","CLEMENTS","","","12/10/2011","U0061-01500-1",""

LEVEL4,"41211",3000000000000,,,,"455.94","Customer responsible/retirement taxes Fica","SSCW103355001","CRUTH","JMURPHY","VAI","N","R507955",ddupie

LEVEL4,"42310",3000000000000,,,,"1412.04","SIT Storage Charges 08/19/11 - 11/09/11","SSCW103355001","CRUTH","JMURPHY","VAI","N","R507955",ddupie

LEVEL4,"41211",3000000000000,,,,"1838.48","Customer responsible/retirement taxes Federal","SSCW103355001","CRUTH","JMURPHY","VAI","N","R507955",ddupie

LEVEL4,"41211",3000000000000,,,,"106.63","Customer responsible/retirement taxes medicare","SSCW103355001","CRUTH","JMURPHY","VAI","N","R507955",ddupie

                                Level 1:

·         R507955 -> Invoice #

·         T39224 -> Account / customer #

·         11/10/2011 -> Invoice date

·         3813.09 -> Total Invoice amount

Level 2:

·         41211 -> GL code

·         455.94 -> Line amount

level0,T11120, .~Champion Moving & Storage","1","A","100 Owens Road","","Brockport","NY","14420","USA","585-235-3500","","B","01","","","100 Owens Road","","Brockport","NY","14420","USA","585-235-3500","","","","Unassigned",""

level0,T11135, .~TheMIGroup (New York Office)","1","A","118 Algonquin Parkway","","Whippany","NE","07981-1602","USA","973-463-0712","973-463-0730","B","01","","","118 Algonquin Parkway","","Whippany","NE","07981-1602","USA","973-463-0712","973-463-0730","","","Unassigned",""

LEVEL1,"C66221","VO","334.68","11/03/2011","ARC1054511","334.68"

LEVEL4,"61222","2000000000000","334.68","SSCW103730605","334.68"

Level 1:

·         C66221 -> Vendor / Partner code who needs to be paid

·         334.68 -> total invoice amount

·         11/03/2011 -> Invoice date

·         ARC1054511 -> Invoice #

·         T39224 -> Account / customer code

Level 2:

·         61222 -> GL code

·         334.68 -> Line amount