Magaya - Dynamics GP Extension User Manual

Introduction

Integration between Magaya and Dynamics GP. The proposed solution to fulfill the customer requirements is an extension developed in NodeJS and will runs in conjunction with Magaya System. It will be installed in the folder Extensions inside Magaya installation folder. The extension will have a background process to trigger all the functionalities agreed in this document. It is required to have installed Magaya 11 or higher.

Integration Perspective

The integration must have the capability to export 8 CSV files from Magaya and drop them in an FTP server. The FTP server must be provided by Trailer Bridge. The files generation will have two CSV sets, the main files for AR, AP, Clients, Vendors. The secondary files will be the “RECAP” files, only generated once a day at midnight EST. The integration must generate an email at midnight every day, with the subject: “TBII – Source System Name to GP Accounting Export Results” and includes all 4 RECAP files attached. The system needs the capability to import four CSV files from a shared folder. Those files will be dropped by Trailer Bridge once a day at 01:00 EST. The integration must process those four files by 05:00 EST each day. Files Description:

• ap_checks_YYYY-MM-DD_hh-mm-ss: will contain all payables vouchers paid in GP within the last 24 hour period
• ar_cashreceipts_YYYY-MM-DD_hh-mm-ss: will contain all receivables with cash application in GP within the last 24 hour period.
• ap_unapply_YYYY-MM-DD_hh-mm-ss and ar_unapply_YYYY-MM-DD_hh-mm-ss: will contain a list of all open AR and AP in GP from the Trailer Bridge agings that will be used by the source system to true-up any AR or AP transaction paid/open statuses within the source system to match the Trailer Bridge GP AR/AP agings.

The integration must generate an email each day at 05:00 EST with subject “TBII – GP to Source System Accounting Import Results” that includes all 4 csv import files, 2 generated CSV that capture all source AR and AP transaction that were trued-up in the source system using only the ap_unapply and ar_unapply import files. A summary in the body of the email with valid/invalid counts of the import activity for each of the 4 file import files as well as the 2 sources system unapply true-up files for AR and AP.

Integration Features

1. Export files from Magaya with the frequency of 30 minutes. Every :00 and :30 of each hour. The files are listed below:

   • Export AR file with the name in the following format: AR_YYYY-MM-DD_hh-mm-ss.
   • Export AP file with the name in the following format: AP_YYYY-MM-DD_hh-mm-ss.
   • Export CLIENT file with the name in the following format: CLIENT_YYYY-MM-DD_hh-mm-ss.
   • Export VENDOR file with the name in the following format: VENDOR_YYYY-MM-DD_hh-mm-ss.

2. Export files from Magaya with the frequency of once a day at midnight EST. The files are listed below:

   • Export AR RECAP file with the name in the following format: AR_RECAP_YYYY-MM-DD_hh-mm-ss.
   • Export AP RECAP file with the name in the following format: AP_RECAP_YYYY-MM-DD_hh-mm-ss.
   • Export CLIENT RECAP file with the name in the following format: CLIENT_RECAP_YYYY-MM-DD_hh-mm-ss.
   • Export VENDOR RECAP file with the name in the following format: VENDOR_RECAP_YYYY-MM-DD_hh-mm-ss.

3. Import files to Magaya with the frequency once a day at 01:00 EST. The files processing must be finalized by 05:00 EST.

   • Import file ap_checks_YYYY-MM-DD_hh-mm-ss
   • Import file ar_cashreceipts_YYYY-MM-DD_hh-mm-ss
   • Import file ap_unapply_YYYY-MM-DD_hh-mm-ss
   • Import file ar_unapply_YYYY-MM-DD_hh-mm-ss

4. Send an email with subject line “TBII – Source System Name to GP Accounting Export Results” that includes all 4 RECAP csv files attached for visibility.

5. Send an email by 05:00 EST each day with subject line “TBII – GP to Source System Accounting Import Results” that includes all 4 csv import files, 2 source system generated csv files that capture all source AR and AP transactions that were trued-up in the source system using only the ap_unapply and ar_unapply import files. A summary in the body of the email with valid/invalid counts of the import activity for each of the 4 file import files as well as the 2 sources system unapply true-up files for AR and AP

Proposed solution

Create a Magaya extension with the following specifications:

1. The extension will consist of a NodeJS application, that will run in conjunction with Magaya. It will be installed in the folder Extension inside the Magaya installation.
2. It’s required to have Magaya 11.5.1 or higher version installed.
3. The extension will have the following components:
   • Email module with the Email configuration page.
   • FTP module with FTP configuration page.
   • Business module with background process to controll and trigger all the actions required.

Workflow

• General workflow for the integration, including the messages flow.

• Messages and execution fequencies.

Functional Requirements

Functional requirements agreed in the Analysis milestone.

1. Build FTP connection module to upload and download files from FTP, FTPS or SFTP server using credentials (User and password).
2. Build Email sender module.
3. Build FTP configuration page.
4. Build Email configuration page.
5. Build a background process to trigger all the functionalities as per configuration.
6. Download file ap_checks_YYYY-MM-DD_hh-mm-ss from the FTP to import in Magaya.
7. Download file ar_cashreceipts_YYYY-MM-DD_hh-mm-ss from the FTP to import in Magaya.
8. Download file ap_unapply_YYYY-MM-DD_hh-mm-ss from the FTP to import in Magaya.
9. Download file ar_unapply_YYYY-MM-DD_hh-mm-ss the FTP to import in Magaya.
10. Generate and send an email with the 4 files imported attached and 2 summary files with the capture of all sources AR and AP transactions were trued-up with the imported files ap_unapply and ar_unapply attached to the email as well. Every day at 05:00 EST with subject: “TBII – GP to Source System Accounting Import Results”
11. Generate file AR_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server every 30 minutes, at :00 and :30 each hour.
12. Generate file AP_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server every 30 minutes, at :00 and :30 each hour.
13. Generate file CLIENT_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server every 30 minutes, at :00 and :30 each hour.
14. Generate file VENDOR_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server every 30 minutes, at :00 and :30 each hour.
15. Generate file AR_RECAP_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server once a day at midnight.
16. Generate file AP_RECAP_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server once a day at midnight.
17. Generate file CLIENT_RECAP_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server once a day at midnight.
18. Generate file VENDOR_RECAP_YYYY-MM-DD_hh-mm-ss and upload it to the FTP server once a day at midnight.
19. Generate and send email at midnight with the 4 recap files attached and the subject “TBII – Source System Name to GP Accounting Export Results”.
20. Generate the transaction numbers as per the mapping specifications, to send it into the file and save it in a transaction custom field.
21. Generate the AR and AP transactions numbers. The number must be generated following the specifications bellow.
    • The transaction numbers are goning to be handled by Magaya as Magaya sequence number by transaction type.

Constrains

• Actual Pick-Up Date is a required field at the shipment level.
• Actual Delivery Date is a required field at the shipment level.
• Estimated Delivery Date is a required field at the shipment level.
• The vendor or customer must be specified.
• The Division field cannot be empty at the shipment level.
• The Department field cannot be empty at the shipment level.
• The freight type cannot be empty at the shipment level.
• The AP/AR transaction number cannot be empty.
• A user in Magaya will need to be able to “enter” an AR/AP transaction and save that transaction (“unposted” for accrual/shipment pricing purposes) at the time the shipment is first created. Later, once we have an actual AP invoice in hand from the vendor or have all the documents needed to bill the customer, a user in Magaya will need to “post” the AR/AP transaction by editing the initial AR/AP accruals and have it exported at this time. A user should be blocked from “posting” an AR/AP if the:
  • Actual Pickup Date field in that shipment is blank or an invalid date.
  • If any of the following three Division/Department/Freight Type are blank (needed to build GL string).
  • If the invoice number on the AP side is blank (assuming the invoice number on the AR side will be auto generated).
  • If the vendor or customer are not selected.
  • A transaction can be exported from Magaya to the accounting system only one time with the same number. GP needs a unique voucher number per transaction because duplicates will not be allowed. A transaction can be voided and then rebilled as a new transaction but will need a unique voucher number/invoice number per transaction.
• Integration trigger: The transactions will have a custom field called "Dynamics GP" with the initial value “Block” at creation time. The transactions will only be exported to GP if the user change the value of the custom field from “Block” to “Post”.
• Every action performed by the integration will be saved as Event in the transaction event table.

Troubleshoothing

The following instructions are directed for an IT person or Magaya Tech Support;

In case of system failure, please follow the steps below:

• Check if the communucation Suite is up and running;
• Restart the communication Suite;
• Check if the service of the integration is up and running
• Current Install folder: extension folder of your Magaya Installation;
• Log folder: [Magaya Installation]/nodejs/.npm-global/.pm2/logs/;
• Do NOT delete the logs on this folder in any case;
• Verify the access control log for more information;

In case of issues, please contact our tech support at Magaya Support.