Great Plains    Dexterity    Dynamics     eEnterprise     Microsoft CRM    Microsoft RMS    Crystal Reports    SQL     International

Alba Home

Phones

Toll Free: 1-866-528-0577

Office: 1-630-961-5918

Cell (consultant-on-duty): 1-630-854-0597

Email: welcome@albaspectrum.com.

Customization for

XYZ Company (SAMPLE)

Prepared by:

Alba Spectrum Technologies

Customization Overview

Technologies.  Customization utilizes the following technologies:

• Great Plains ReportWriter
• Crystal Reports
• SQL Stored Procedures, Views, Triggers, SQL Mail
• Great Plains Modifier with VBA
• Great Plains Dexterity

With upgrade from Great Plains 5.5 to Great Plains 7.5 portion of Dexterity customization was converted into SQL Triggers, Views and Crystal Reports.  Our recommendation for the future is further conversion of Dexterity customization into SQL and Crystal Reports.

Customized Items  

• XYZ Number Tracking window
• Payment Terms addition to Recurred Billing Maintenance form
• Sales Report
• Paid Only Inquiry form
• Batch Notes on Invoice and Historical Invoice forms
• Invoice Payment SQL Mail Notification

This document describes customization as it is on April, 2003, Great Plains ver. 7.5

Payment Terms

Overview

Payment term per contract feature overrides default Payment Term on SOP Invoice and sets it to the Payment Term from the Contract in Batch Invoicing.

Functionality

This is modified Mekorma Batch Invoicing Recurring Invoice Maintenance window.  The field Payment Term is custom addition and this field is saved together with the contract settings.  Later on when Invoices are created for the contract – the contract Payment Term supercedes default Payment Term (from the Customer settings) in these invoices.

Technologies

• Great Plains Modifier – Payment Term field on the Recurring Invoice Maintenance window is added in Modifier.  Modified form is stored in BINVFRM.DIC and the access to this modified window should be given to the Great Plains user
• VBA – is used for ADO connection to SQL Server, where company database has zstblContractPaymentTerms table.  This table allows Payment Term to be associated with the contract.
• SQL Trigger - AS_PMT_TRM_SOP_Update.sql.  This trigger is for INSERT on ME50411 table (Mekorma Invoices Log).  When Mekorma creates new SOP Invoice – this trigger updates the payment term, based on payment term, stored in zstblContractPaymentTerms table for the contract

Attention! ADO connection string with SQL Server name, sa user id and password is hardcoded in VBA.  In order to change it you should open Visual Basic Editor from Great Plains:

select BatchInvoicing as the product and select RecurringInvoiceMaintenance window:

then locate the following code:

    adoCN.ConnectionString = "driver={SQL Server};database=DYNAMICS;uid=sa;pwd=kitchen;server=ACCT-SQL;"

and make the changes necessary.

Sales Report

Overview

This report shows unpaid or paid invoices for the customers in the specified dates range.

Fields:

·         Invoice Number

·         Contract Number

·         Customer Number

·         Customer Name

·         Batch Number

·         Invoice Date

·         Due Date

·         Original Amount

·         Current Amount

Report is sorted by Customer Number in ascending alphabetical order.

Technologies

• Crystal Reports – this is Crystal Report
• SQL View – report is based on AS_Paid_Unpaid_Report_View, this is custom view in the company database.  This view presents the information from RM20101 – Receivables transaction open and RM30101 – Receivables transaction historical table in conjunction  with RM00101 – customer master record, SOP30200 – Sales Transaction history and ME50411 – Mekorma Batch Invoices Log table

Report modification.  You can do formatting directly in Crystal Report Designer.  However if you plan to include new fields, not available in AS_Paid_Unpaid_Report_View we encourage you to modify the view, rather than do modification in Crystal Report Designer

Lot Number Tracking 

Overview

Serial Number Tracking window allows you to see the history of the item with Serial Number through the following modules:

·         Sales Order Processing

·         Purchase Order Processing

·         Inventory

·         Fixed Assets

Functionality

Window shows items, tracked by Serial/Lot Number (User Field 5 in case of Fixed Asset).

Restrictions (From/To):

• Serial Number (User Field 5 in case of Fixed Assets)
• Item Number (Inventory Item Number in case of Inventory, Sales Order Processing or Purchase Order Processing origin and Fixed Asset Number in case of Fixed Assets origin)
• Customer/Vendor – this restriction is applicable to SOP (Customer Number) and POP (Vendor ID) modules only and should be left empty in case of inquiring FA or Inventory documents
• Module – FA, INV, POP, SOP

If no restriction placed – all the documents with Serialized/Lot Numbered items should be shown.

Fields shown:

• Serial/Lot Number (User Field 5 in case of Fixed Assets)
• Item/Tag Number (with drill down to Inventory Item Maintenance for INV, POP and SOP documents, with drill down to Fixed Asset Maintenance in case of Fixed asset)
• Document Date
• Quantity
• Cost/Price
• Module (INV, POP, SOP or FA)
• TRX Source
• Doc Type
• Document Number (with drill down to INV: Inventory Transaction, Inventory Transfer; POP: Receiving Entry; SOP: Invoice and Return; FA: Fixed Asset Inquiry, Fixed Asset Transfer and Fixed Asset Retirement)
• TRX Location – Inventory Site for Inventory Item (INV, SOP, POP), not in use for Fixed Assets
• Quantity Type - Inventory Quantity types for SOP, IV, POP, not in use for FA
• Item Description – Inventory Item Description (SOP, IV, POP), Fixed Asset Description for FA
• Customer/Vendor (with drill down to POP: Vendor Maintenance, SOP: Customer Maintenance, IV – not in use, FA: Transfer Inquiry, Retirement Inquiry or General Information)

Buttons:

• Redisplay – applies restrictions and repopulates scrolling window
• Update – updates information in ASSerial table – this table is base table for the scrolling window.  Consider the following scenario.  You used this Serial Number Tracking window two weeks ago.  If you don’t press update button – you will not see all the transaction occurred in these two recent weeks.  In order to see these transactions you must press Update button to rebuild ASSerial table.

Technologies

• Dexterity – the window itself is created in Dexterity.  Base table for scrolling window is ASSerial
• SQL Stored Procedure: when you press Update button – Dexterity calls sp_sbRecreateSNumTbl SQL Stored procedure in the Company database which it turn rebuilds ASSerial table
• VBA with inline Sanscript (Dexterity programming language) code – is used to switch from Great Plains core product to Fixed Assets (Dynamics.vba and XYZ.vba) and open FA Inquiry, FA Retirement and FA General Information forms (XYZ.vba)

Following is sample VBA with Sanscript inline code, this procedure switches the product to ‘Fixed Assets’ and opens FA General Maintenance, Transfer or Retirement inquiry forms.  This procedure is called when user select Fixed Asset line, places the cursor and clicks on Customer/Vendor drill down field:

Private Sub CustomerVendor_AfterUserChanged()

    Dim SavedProduct As String

    Dim error_msg As String, error As Integer, dex_command As String, dex_command2 As String

    PurchaseOrderNumberWORKSTR = ASPONumberFrom

    If ASSerialNumberTracking.AssetIndex <> 0 Then

        SavedProduct = Dynamics.templateMain.DynamicsApp.currentproduct

        Dynamics.templateMain.DynamicsApp.currentproduct = "Fixed Assets"

        If ASSerialNumberTracking.IntegerValue = 1 Then 'new fixed asset

            dex_command = "open form FA_General_Maintenance;"

            dex_command = dex_command + " set 'Asset Index' of table FA_General_Information_MSTR to " + ASSerialNumberTracking.AssetIndex + ";"

            dex_command = dex_command + " get table FA_General_Information_MSTR;"

            dex_command2 = " set 'Asset ID' of window FA_General_Maintenance of form FA_General_Maintenance "

            dex_command2 = dex_command2 + "to 'Asset ID' of table FA_General_Information_MSTR;"

            dex_command2 = dex_command2 + " run script 'Asset ID' of window FA_General_Maintenance of form FA_General_Maintenance;"

            dex_command2 = dex_command2 + " focus 'Asset Description' of window FA_General_Maintenance of form FA_General_Maintenance;"

            error = Dynamics.templateMain.DynamicsApp.executesanscript(dex_command + dex_command2, error_msg)

        End If

        If ASSerialNumberTracking.IntegerValue = 2 Then 'transfer fixed asset

            dex_command = "open form FA_Transfer_Inquiry;"

            dex_command = dex_command + " set 'Transfer Index' of table FA_Transfer_MSTR to " + ASSerialNumberTracking.AssetIndex + ";"

            dex_command = dex_command + " get table FA_Transfer_MSTR;"

            dex_command2 = " set 'Asset ID' of window FA_Transfer_Inquiry of form FA_Transfer_Inquiry "

            dex_command2 = dex_command2 + "to 'Asset ID' of table FA_Transfer_MSTR;"

            dex_command2 = dex_command2 + " run script 'Asset ID' of window FA_Transfer_Inquiry of form FA_Transfer_Inquiry;"

            dex_command2 = dex_command2 + " focus 'Asset ID' of window FA_Transfer_Inquiry of form FA_Transfer_Inquiry;"

            error = Dynamics.templateMain.DynamicsApp.executesanscript(dex_command + dex_command2, error_msg)

        End If

        If ASSerialNumberTracking.IntegerValue = 3 Then 'retire fixed asset

            dex_command = "open form FA_Retirement_Inquiry;"

            dex_command = dex_command + " set 'Retirement Index' of table FA_Retirement_MSTR to " + ASSerialNumberTracking.AssetIndex + ";"

            dex_command = dex_command + " get table FA_Retirement_MSTR;"

            dex_command2 = " set 'Asset ID' of window FA_Retirement_Inquiry of form FA_Retirement_Inquiry "

            dex_command2 = dex_command2 + "to 'Asset ID' of table FA_Retirement_MSTR;"

            dex_command2 = dex_command2 + " run script 'Asset ID' of window FA_Retirement_Inquiry of form FA_Retirement_Inquiry;"

            dex_command2 = dex_command2 + " focus 'Asset ID' of window FA_Retirement_Inquiry of form FA_Retirement_Inquiry;"

            error = Dynamics.templateMain.DynamicsApp.executesanscript(dex_command + dex_command2, error_msg)

        End If

        Dynamics.templateMain.DynamicsApp.currentproduct = SavedProduct

    End If

End Sub

Paid Only Documents Window

Overview

Receivables Transaction Inquiry form by Customer has Paid Only custom check box, which allows filter Paid Only documents

Technologies

This is Dexterity customization for RM Transaction Inquiry by Customer Great Plains form.  Access should be granted to Alternate Great Plains form in sales series for AS XYZ Utilities.  Dexterity uses AS_RM_Transaction_Inquiry_Scroll_Before trigger (Dexterity trigger) to filter paid transactions in the scrolling window.

Batch Note Field on Invoice

Overview

This customization enables batch comment field to be placed on SOP Blank Invoice, SOP Blank Invoice with Options and SOP Historical Blank Invoice (for reprinting historical invoices)

Technologies

This is Dexterity customization.  In Dexterity we created two custom functions:

rw_get_sop_batch_comment

and

rw_get_sop_hist_batch_comment

for addressing batch comment field from un-posted and historical batches.  Prefix rw_ allows you to address these functions in calculated fields of the report in Great Plains ReportWriter.

Also the above reports were moved from DYNAMICS.DIC into customization.

In order to place batch comments fields you should open ReportWriter and select AS XYZ Utilities as a product

Then you will see the reports available for modification:

The above screen shows you the way of adding new calculated field of the Result Type text, which calls one of these functions.  The screen below shows you how to provide the parameter for the function (both functions require SOP Number, this one comes from Sop Work Header, in case of history it comes from SOP History Header tables)

Payment Message

Overview

When sales invoice is paid – ‘Mekorma’ users get automatic SQL mail notification if the contract status for the invoice is inactive.

The notification fires when either Apply To check box is checked or Auto Apply button is pressed in Apply Sales Documents window, see below

Technologies

• SQL Trigger - AS_Invoice_XYZ_If_Inactive_Notify_Trigger calls SQL stored procedure DYNAMICS.dbo.sp_sbInactiveInvoiceMail
• SQL Stored procedure DYNAMICS.dbo.sp_sbInactiveInvoiceMail calls in turn xp_sendmail
• xp_sendmail extended procedure for sending SQL mail to users in ‘Mekorma’ group; you have to grant access for execution of this procedure for public in SQL Server and Microsoft Outlook should be installed on SQL Server and user id, logged into SQL Windows Server should have mailbox in MS Exchange.  In this scenario all the notifications will be ‘sent’ by this user.

Not in Use

In Dexterity

·         AS_InvoicesReport, this is old Dexterity version of Sales report, which is now in Crystal, this obsolete version used ASPDUNP Dexterity table and AS_Paid_Unpaid_SOP_Invoices_Report_Range form

·         AS_Invoice_XYZ_If_Inactive_Notify_Trigger – this Dexterity trigger is now replaced with AS_Invoice_XYZ_If_Inactive_Notify_Trigger  SQL trigger, see Paid Invoice eMail Notification above

·         AS_Serial_And_Lot_Number_Tracking form, which is now replaced with AS_Serial_Number_Tracking Dexterity form

Alba Home