Project Name

JP Purchasing Module – (JPPOM)

Industry

Not-For-Profit

Requirements Overview

  • Sage ERP Accpac 5.5 Custom Module to process purchase orders created in clients’ custom internally developed “JPPO” application.
  • Connect and download purchase order requisitions from a MySQL database and process into Sage ERP Accpac – Purchase Orders.
  • Integration into Sage ERP Accpac’s Common Services Optional Fields, Accounts Payable Vendors, General Ledger Accounts and Purchase Order modules.
  • Integration into the client’s “JPPO” MySQL Database
  • Microsoft SQL Server 2008
  • 64-Bit Server Environment
  • Microsoft .NET Framework
  • Windows 2000 / XP / Vista / Windows 7

Challenges

This project required the Purchase Order Interface Application to interface with a MySQL Database containing “JPPO” purchasing data. A “Settings” user interface was required to allow authorised users to define Connection Details and default SQL Statements to interface between Accpac and the MySQL Database.

The Purchase Order Interface Application was required to access an external MySQL Database hosted at a different location as to the client and be able to extract (download) data from a MySQL database into Sage ERP Accpac.

During the download, the vendors attached to the PO’s need to be validated and matched to vendors in Sage ERP Accpac (unmatched vendors need to be flagged as such and the user needs to have the ability to either select an existing Sage ERP Accpac vendor or create a new one and link it back to the PO). The downloaded PO’s would need to be viewed/edited by certain Sage ERP Accpac users and then marked as ready to post. PO’s marked as ready to be posted would be processed into the standard Sage ERP Accpac purchase order entry module, a Purchase Order report would then need to be created and sent as a PDF attachment via email as Purchase Order Notifications to relevant contacts which are attached to the incoming purchase order and certain managers.

Additionally, the project required the upload of the newly generated Sage ERP Accpac Purchase Order Number back into the “JPPO” MySQL Database along with details about the user who had done the processing and date/time stamp.

This project also required the synchronisation of “JPPO” Vendors and therefore required a mechanism to upload Sage ERP Accpac Vendor Data into the “JPPO” MySQL Database.

Solution

The Purchase Order Interface contains a number of custom UI’s providing flexible configuration of the Purchase Order Interface on the client’s system, including Email Contacts, PO Item Mapping, Connection Details, Purchase Order Report Paths, Email Configuration and G/L Account Mapping. The Connection Details User Interface provided a user editable interface defining MySQL Connection Strings and default Trans-SQL Statements (i.e. SELECT, INSERT and UPDATE) for interfacing with the external MySQL Database to and from Accpac.

Additionally the Purchase Orders Interface Options User Interfaces allowed authorised users to specify an email configuration including SMTP Address, Port, Timeout, Username, Password, Domain, From and To fields and add multiple email addresses for sending Crystal Report generated PDF “JPPO” Purchase Order Reports to relevant contacts.

The Purchase Order Interface provides custom modules enabling authorised users to download “JPPO” data for processing into Sage ERP Accpac, upload newly generated Purchase Order Numbers back into MySQL for successfully posted Purchase Orders, repost Purchase Orders (if they were unsuccessful) and to perform the processing of downloaded JP Notes Purchase Orders based on user selection.

Via the Purchase Order Manager User Interface, certain users are able to select non deleted “JPPO” Purchase Orders and post them into Sage ERP Accpac Purchase Orders module and emailed to relevant contacts.

The transitional Purchase Orders User Interface provided authorised users with addition flexibility in being able to add an additional item in the event that the “JPPO” Purchase Order Item had no matching Sage ERP Accpac Purchase Order Item and also change/add new vendor code to the PO.

Results

The benefits provided to the client:

  • An application that can be shared securely within the Organisation
  • An application that integrates with the Organisations current/future business model
  • An application that provides data integrity with pre-defined MySQL connection strings and default Trans SQL statements, providing added flexibility in Purchase Order Interface Application configuration.
  • An application that provides an inbuilt email handler to send Purchase Order Reports as attachments to applicable contacts.
  • Complete replacement of their previous processes of receiving the purchase orders as a fax or e-mail and then having a team of staff whose sole purpose was to manually re-key the purchase orders into Sage ERP Accpac – adding speed, efficiency and most notably the costs savings to the client.