Hybrid – SOP Batch Splitting

Two weeks ago, I began working on a Microsoft CRM to Microsoft Dynamics GP integration project. Sales orders initiated in CRM and are transferred to GP once they are submitted in CRM. Once the orders got to GP they must be placed on automatic hold for document verification. However, once the hold was removed, the client wanted to be able to transfer all verified orders in bulk… so far good! The problem though was, when orders were transferred to invoices, the invoices remained mixed with the orders that did not get transferred in the same batch. This presented a control issue for the client as the staff performing the documentation is not the same staff transferring the orders.

Last year I posted the article Moving SOP Transactions from One Batch to Another. This article demonstrated a SQL script that would allow a database administrator to move SOP documents from one batch to another. The script in that article forms the basis for this customization. However, to make it more user friendly, I decided to provide the client with a way of running it from the Microsoft Dynamics GP interface by adding a Split button with Modifier to the SOP Batch Entry window. The following is a modified version of the SOP Batch Entry window with the Split button.

The Split button in turn, prompts the user for a new batch that will host the invoices. Upon entering the new batch number, the VBA script instantiates the UserInfo object to create an ADO connection. The connection executes a stored procedure to move the invoices into the batch entered by the user.

While the customization has been designed specifically to move invoices into a new batch, it can be adapted to move same SOP document types out of a batch containing other SOP document types.

Downloads

SOP Split Batch – Click here to download package file and SQL stored procedure.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

RW – Working with POSTNET barcodes for US zip codes

The Postal Numeric Encoding Technique (POSTNET) is a barcoding system developed by the United States Postal Service to assist with automatic mail sorting and routing. The POSTNET barcode uses a combination of half-bars and full-bars to encode the zip code that appears on a mail piece. The barcode starts and ends with a full bar (often called a guard rail or frame bar and represented as the letter “S” in the USPS TrueType Font).

In the United States, zip codes can be of 5 digits, 9 digits (also known as Zip+4), or 11 digits in the case of a specific delivery point. Each digit in the zip code is represented by 5 bars. Therefore a 5-digit zip code is represented by 25 bars. In addition, to ensurePOSTNET accuracy during mail processing, a check digit, which is five bars, is calculated and added to the zip code. Hence a 5-digit zip code would render a total of 32 bars: 25 bars for the 5-digit zip code + 5 bars for the check digit + the 2 enclosing guard rail bars. Hence, Zip+4 uses 52 bars, and a delivery point uses 62 bars.

To calculate the check digit, each digit in the zip code is added. The result is then subtracted from the nearest multiple of 10. For example, if each digit in the zip code 33076 is added, 3+3+0+7+6 = 19, and we subtract from the nearest multiple of 10, number 20, then the check digit is 1. Mathematically speaking 10 – (digit-sum mod 10). This will yield the following S330761S. This number is then represented by the following barcode (including the guard rails, represented by “S”):

While based on a binary system, the weight of each bar is different than in standard binary. The weight of each bar is as follows:

The following illustration shows are representation of all numbers:

To add zip code barcoding capabilities to a Report Writer report, for example an Invoice or a Check, you must first download and install the fonts. You can get a set of fonts from here. To install the fonts (the TTF files in the zipped file), extract the fonts to your My Documents folder, then copy them to your Windows\Fonts folder.

1) Open Report Writer and select the SOP Blank Invoice Form. Click the Layout button to open the report in the Layout window.

2) Add the RM_Customer_MSTR_ADDR.Zip field to both Report Header and Page Header sections. Set the fields properties to Invisible.

3) Create a string calculated field called (C) Postnet. Assign the RM_Customer_MSTR_ADDR.Zip field to this calculated field as shown in the picture:

4) Add the (C) Postnet calculated field to the Report Header and Page Header sections. Change the font to IDAutomationSPOSTNET. By now, your report layout should look like this:

5) Add the report to VBA. Go to Tools > Add Report to VBA.

6) Select all 4 fields, the 2 on the report header and the 2 on the page header and add them to VBA. Go to Tools > Add Fields to VBA.

7) Open the VBA Editor, locate the SOPBlankInvoiceForm (Report) object in the Project Explorer , double-click and add the following code:

Public Function EncodeZip(pZip As String) As String    Dim i As Integer    Dim sZip As String    Dim digitSum As Integer    Dim checkDigit As Integer

    sZip = "S"    digitSum = 0

    For i = 1 To Len(pZip)        If IsNumeric(Mid(pZip, i, 1)) Then            ' Concat the digit            sZip = sZip & Mid(pZip, i, 1)

            ' Add up the digits            digitSum = digitSum + CInt(Mid(pZip, i, 1))        End If    Next i

    ' Calculate check digit    checkDigit = 10 - (digitSum Mod 10)    sZip = sZip & CStr(checkDigit) & "S"

    EncodeZip = sZipEnd Function

Private Sub Report_BeforePH(SuppressBand As Boolean)    Dim sZip As String

    sZip = Zip    sZip = EncodeZip(sZip)    CPostnet = sZipEnd Sub

Private Sub Report_BeforeRH(SuppressBand As Boolean)    Dim sZip As String

    sZip = Zip    sZip = EncodeZip(sZip)    CPostnet = sZipEnd Sub

Compile the code.

Return to Microsoft Dynamics GP saving all changes as you exit.

9) Grant security to the modified report. Print an invoice to test.

I hope you found this article interesting and the implementation very simple. You can find the fonts and package file for this project at the bottom of this article.

Downloads

Postnet Fonts – Click here
v10 SOP Blank Invoice Form report – Click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Understanding the "Posting Accounts from Customer or Item" option in SOP

I have been a Microsoft Dynamics GP consultant for more than a decade now and have always found that a big point of contention (and confusion) during the implementation of Sales Order Processing in any environment has always been selecting whether posting accounts will default from the customer or from the item for the effects of creating an accounting distribution for transactions entered and processed.

From a business perspective…

Typically, organizations use one of these two criteria — there may be many more — for reporting profitability: by customer or by product, these two terms used in the broader sense.

Posting Accounts From can be found the Sales Order Processing Setup
window by going to MSDGP > Tools > Setup > Sales > Sales Order Processing

Accounting wise, Customer may refer to a segment in the chart of accounts that represent geographical market location (for example, sales territories, regions; or domestic customers vs international customers), or specific customer activity (for example, retail customers, wholesale customers, not-for-profits, etc). The end goal with this type of revenue and COGS segregation is to report profitability in the different markets where the organization does business. This will in turn allow the organization’s executives to implement certain market strategies that are geared towards improving its bottom line in these markets, or in extreme cases, determine whether having a presence in a specific market makes business sense. It’s also true that organizations that choose this revenue and COGS breakdown typically carry a small amount of product lines, with product costs and prices that do not vary that often.

Accounting wise, Item, may refer to a segment in the chart of account that represent different product lines (for example, analog, digital, embedded processing; or frozen, perishables, dry goods, meats, etc.), or a specific type of fabrication (for example, pinbrazing, vacuum brazing, induction brazing, etc). The end goal with this type of revenue and COGS segregation is to report profitability by the different products carried by the company. This will in turn allow the organization’s executives to implement certain business strategies geared towards minimizing production costs, or find better suppliers of raw material or finished goods, or in extreme cases, determine whether having a certain product line makes business sense. It’s also true that organizations that choose this revenue and COGS breakdown typically carry a wide array of products, where, contrary to the customer scenario, costs and prices may vary often due to market conditions.

Making the decision as to what option is best for the organization should always take into account the financial reporting structure for revenue and cost of sales.

From a technical perspective…

From a technical standpoint, Microsoft Dynamics GP’s chart of account is static, meaning, the chart of account is configured and stored (“hardcoded”) as a pre-requisite to any transaction activity in the system. This in turn imposes certain limitations in the way the system selects accounts at transaction time, but the most notorious limitation is the inability to match chart of account segments based on rules as opposed to selecting defaults. These limitations have given way to third party solutions that implement a rules based system to determine the account that must be used, for example eOne Integrated Business Solutions’ Flexicoder. Flexicoder allows organizations with complex revenue and COGS reporting requirements to build rules for GL accounts used in Sales Order Processing transactions.

I hope you’ve found this article useful and that you understand how each posting account configuration option is related to an organization’s financial reporting requirements.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

The wonders of CTRL+Q: saving and printing SOP documents in one step

How many times have you wished you could save and print a sales order, a fullfillment order, or an invoice document, with their corresponding packing slips and picking tickets all in one step? The SOP Quick Print feature offers just that! Written by my friend David Musgrave, and a standard part of Microsoft Dynamics GP, it’s one of the most overlooked (or perhaps, unknown) yet, one of the most requested options by users working with SOP.

David says “Just set it up with the Keep Current Document Displayed option unchecked.Then instead of saving and then printing. Just use Ctrl-Q to quick Print.That will then save the document and print it.That’s what I wrote the feature for.“

To access the setup window, click on the Options menu on the Sales Transaction Entry screen, then select Quick Print Setup. You can configure additional options even for packing slips and picking tickets, along with report output settings. You can also choose to let the document remain on the screen even after printed.

Once configured, you can then begin to enter your documents or select previously entered ones, then hit CTRL+Q from your keyboard. That’s it! Your document is saved then printed, all in one step! One of the most important benefits of this feature is, you don’t need to create your own customization.

Have a happy CTRL+Q with your SOP documents!

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Sales Order Processing posting statuses

Well, everyday I learn something new. My friend and fellow MVP Victoria Yudin had been wondering what could be the possible values stored in the PSTGSTUS (‘Posting Status’) column of the tables dbo.SOP10100 (technical name: SOP_HDR_WORK) and dbo.SOP30200 (technical name: SOP_HDR_HIST) statuses as a result of a posting operation in Sales Order Processing.

In fact, just recently on the Dynamics GP Newsgroup, someone reported seing a status 508 in the SOP30200 table. The complete list was provided by a Microsoft support engineer on the Partners forum — take note as they are not documented in the SDK.

Transcript

All transactions in SOP10100 that have not been posted yet should have a PSTGSTUS value of 0 (unposted). This value of this field will change to 2 (posted) when posted and the record will move to SOP30200. With this in mind, all transactions in SOP10100 should have PSTGSTUS value of 0 and all transactions in SOP30200 should have PSTGSTUS value of 2 (posted). Any other values in either table would indicate that there was a posting interruption.

I have found invalid values of 12, 14, 508, etc. These values may have been assigned to the record in different stages of the posting process. Meaning, the posting process did not complete and the transactions need to be recovered in the Batch Recovery window.

Example:

1. Transaction 1 in SOP10100 (Work) with PSTGSTUS = 0.
2. Transaction 1 is posted:

a. PSTGSTUS is assigned the value of 7 while GP is checking for duplicate Document Numbers.
b. PSTGSTUS is assigned the value of 12 while GP is verifying the accuracy of the amounts between the detail and header records.
c. PSTGSTUS is assigned the value of 14 while GP is calculating that the total detail amounts match the header amount.
d. PSTGSTUS is assigned the value of 508 while GP is is in the process of transferring the record from Work (SOP10100) to History (SOP30200).
e. PSTGSTUS is assigned the value of 600 while GP is validating if the detail and header records match in the History tables (SOP30200 and SOP30300).
f. PSTGSTUS is assigned the value of 2 upon the end of the posting process.

In this example, if the posting process was interrupted after step a, then the record will still be in SOP10100 with a PSTGSTUS = 7. Or, if the posting process was interrupted after step d, then the transaction may still be in SOP10100 with a PSTGSTUS = 508.

The example above is not the exact posting process in GP. This is just a way of showing how a record in SOP10100 can have a PSTGSTUS value other than 0 and 2.

I must personally add that a transaction will only make it to SOP30200 with statuses of 2 or 3, as the transcript indicates, the other status are more used as a workflow within the posting process than really a status.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Rebuilding Tax History

Business Situation

A few days ago, a user was facing an issue in which apparently, their tax history was not being recorded or was missing for a good number of transactions created in Sales Order Processing. In addition, the user wanted to use Dynamics GP’s built-in tax reports as they did exactly what she needed.

Of course, the question came in: How can I rebuild my Tax History?

Solution

One thing I love about Dynamics GP is the fact that information created in the subsidiary modules, flow to other parts of the application tables when GP is instructed to do so. This instruction comes via the configuration options in the system.

In this particular case, when Sales Order Processing transactions are created and taxes are calculated for those documents, the tax detail information is stored in the Sales Tax Work and History table (dbo.SOP10105).

When invoices and returns are posted, and if history is being maintained for the tax details on these documents, the resulting tax records are then maintained in the Tax History table (dbo.TX30000) with tax information calculated from other subsidiaries.

With this in mind, the following T-SQL query will rebuild the missing sales tax information for all invoices that are not currently in the Tax History table.

/* 2009. Created by Mariano Gomez, MVP   This code is provided "AS IS" with no warranties expressed or implied

   To be executed against your company database*/WITH SOPDocs(SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR) AS ( SELECT SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR FROM SOP10100 WHERE SOPTYPE = 3 UNION ALL SELECT SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR FROM SOP30200 WHERE SOPTYPE = 3)INSERT INTO TX30000 (  DOCNUMBR,DOCTYPE,SERIES,RCTRXSEQ,SEQNUMBR,TAXDTLID,TXDTLPCT,TXDTLAMT,ACTINDX,DOCDATE,Tax_Date,PSTGDATE,TAXAMNT,ORTAXAMT,Taxable_Amount,  Originating_Taxable_Amt,DOCAMNT,ORDOCAMT,ECTRX,VOIDSTTS,CustomerVendor_ID,CURRNIDX,Included_On_Return,Tax_Return_ID,TXORGN,TXDTLTYP,  TRXSTATS,RETNUM,YEAR1,INVATRET,VATCOLCD,VATRPTID,Revision_Number,PERIODID,ISGLTRX)SELECT a.SOPNUMBE, a.SOPTYPE, 1, 0, ROW_NUMBER() OVER(PARTITION BY a.SOPNUMBE ORDER BY a.TAXDTLID), a.TAXDTLID, b.TXDTLPCT, b.TXDTLAMT, a.ACTINDX, c.DOCDATE, c.Tax_Date, c.GLPOSTDT, a.STAXAMNT, a.ORSLSTAX, a.TAXDTSLS, a.ORTXSLS, c.DOCAMNT, a.ORTOTSLS, c.ECTRX, c.VOIDSTTS, c.CUSTNMBR, a.CURRNIDX, 0, '', 1, 1, '', '', 0, 0, '', '', 0, 0, 0FROM SOP10105 a LEFT OUTER JOIN TX00201 b ON (a.TAXDTLID = b.TAXDTLID) LEFT OUTER JOIN SOPDocs c on (a.SOPNUMBE = c.SOPNUMBE) and (a.SOPTYPE = c.SOPTYPE) LEFT OUTER JOIN TX30000 d on (a.SOPNUMBE = d.DOCNUMBR) and (a.SOPTYPE = d.DOCTYPE)WHERE (a.SOPTYPE = 3) and (a.LNITMSEQ = 0) and (d.DOCNUMBR IS NULL)

With minor tweaks, you should be able to get the above code to run for Return documents.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to roll down SOP’s Requested Ship Date changes to line items and purchase orders – Part II

In the previous installment, you saw how to make changes to the Requested Ship Date roll down to the line items and linked purchase order documents via a SQL Server trigger.

This article will now show a VBA method combined with a SQL Server stored procedure call. The advantage of using VBA is to allow more interaction with the end-user and control the flow a bit more.

The following steps will get us there:

1) Add the Sales Transaction Entry window to VBA. Open the Sales Transaction Entry window and press CTRL + F11 on your keyboard.

2) Add the Document Type drop-down list, Document Number, and Customer ID fields to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on both fields. Press Shift + F11 to deactivate the visual cue.

3) Add the Sales Date Entry window to VBA. Click on the expansion button next to the Date field to open the Sales Date Entry window and press CTRL + F11 on your keyboard.

4) Add the Requested Ship Date field to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on the Requested Ship Date field. Press Shift + F11 to deactivate the visual cue.

5) Open the Visual Basic Editor. Press ALT + F11 on your keyboard. Locate the Microsoft Dynamics GP project in the Project Explorer window to the left of the screen. Expand to locate the SalesDateEntry (Window) object. Double-click to open the code editor.

6) Copy and paste the following code in the Editor:

'----------------------------------------------------------------------' Created by Mariano Gomez' Maximum Global Business, LLC - http://www.maximumglobalbusiness.com' (C) 2008' This code is provided "AS IS" with no warranties expressed or implied'----------------------------------------------------------------------Option Explicit

Private Sub RequestedShipDate_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)    Dim result As Integer    Dim SOPNumber As String    Dim SOPType As Integer

    Dim objConn As New ADODB.Connection    Dim objCmd As New ADODB.Command    Dim objRS As New ADODB.Recordset

    If Not (SalesTransactionEntry.DocumentNo.Empty) And Not (SalesTransactionEntry.CustomerID.Empty) Then        ' Check to see if there are any line items entered for this transaction, otherwise

        Set objConn = UserInfoGet.CreateADOConnection()

        With objConn           .CursorLocation = adUseClient           .DefaultDatabase = UserInfoGet.IntercompanyID        End With

        With objCmd            .ActiveConnection = objConn            .CommandType = adCmdText            .CommandText = "SELECT * FROM SOP10100 WHERE SOPNUMBE = '" & SOPNumber & "' AND SOPTYPE = " & CStr(SOPType)            Set objRS = .Execute        End With

        If objRS.RecordCount > 0 Then            ' We have some records to process in the SOP_Line_WORK

            result = MsgBox("Do you want to roll down changes to line items and linked POs?", vbYesNo, "Roll down Requested Ship Date")            If result = vbYes Then                SOPNumber = SalesTransactionEntry.DocumentNo

                ' Since the SOP Types in the DDL are not equivalent to the actual SOP Types stored in SOP10100                ' we need to switch them around a bit

                Select Case SalesTransactionEntry.TypeTypeID                    Case 1                        SOPType = 1  'Quote                    Case 2                        SOPType = 2  'Order                    Case 4                        SOPType = 3  'Invoice                    Case 5                        SOPType = 4  'Return                    Case 6                        SOPType = 5  'Back Order                    Case 3                        SOPType = 6  'Fulfillment Order                End Select

                With objCmd                    .CommandType = adCmdText                    .CommandText = "EXEC usp_SOPRollDownReqShipDate '" & SOPNumber & "', " & CStr(SOPType) & ", '" & CStr(RequestedShipDate) & "'"                    .Execute , , adExecuteNoRecords                End With

                Set objCmd = Nothing

                ' close and destroy Recordset object                objRS.Close                Set objRS = Nothing

                ' close and destroy connection object                objConn.Close                Set objConn = Nothing             End If

        End If    End IfEnd Sub

7) Go to the Debug menu and select Compile Microsoft Dynamics GP.

The above code calls the dbo.usp_SOPRollDownReqShipDate stored procedure which must be created for each company database. Open SQL Server Management Studio and create a New Query. Copy and paste the following code to your query window:

/*'----------------------------------------------------------------------' Created by Mariano Gomez' Maximum Global Business, LLC - http://www.maximumglobalbusiness.com' (C) 2008' This code is provided "AS IS" with no warranties expressed or implied'----------------------------------------------------------------------*/USE [YourCompanyDB]GO

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SOPRollDownReqShipDate]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]GO

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE PROCEDURE [dbo].[usp_SOPRollDownReqShipDate] @SOPNumber char(31), @SOPType smallint, @ReqShipDate datetimeAS

IF ISNULL(@SOPNumber, '') = '' RETURN

BEGIN TRAN

  -- roll down to all items  UPDATE SOP10200 WITH (ROWLOCK) SET ReqShipDate = @ReqShipDate  WHERE SOPNUMBE = @SOPNumber and SOPTYPE = @SOPType

  -- updated the Required Date column for linked POs  UPDATE A WITH (ROWLOCK) SET A.REQDATE = @ReqShipDate  FROM POP10100 A INNER JOIN SOP60100 B ON A.PONUMBER = B.PONUMBER  WHERE (B.SOPNUMBE = @SOPNumber) AND (B.SOPTYPE = @SOPType)

  -- updated the Required Date column for the line items on linked POs  UPDATE A WITH (ROWLOCK) SET A.REQDATE = @ReqShipDate  FROM POP10110 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) AND (A.ORD = B.ORD)  WHERE (B.SOPNUMBE = @SOPNumber) AND (B.SOPTYPE = @SOPType)

 IF @@ERROR  0  COMMIT TRAN ELSE  ROLLBACK TRAN

GO

GRANT EXECUTE ON [dbo].[usp_SOPRollDownReqShipDate] TO DYNGRPGO

9) Execute the query against each company database to be able to run the VBA customization across more than one company.

Testing the Customization

To test the customization, select an existing sales order. Change the Requested Ship Date. Close the sales order saving any changes. Reopen the order and all line items should now reflect the correct requested ship date.

You can choose to select a sales order that has a linked purchase order to test changes to the Required Date on the purchase order and purchase order line items.

Downloads

You can download the package files and stored procedure for this project below

Click here to download the usp_SOPRollDownReqShipDate stored procedure SQL file.
Click here to download the v10_SOP_Transaction_Entry package file.
Click here to download the v10_SOP_Transaction_Entry_VBA_References package file.

Until next post!

MG.-
Mariano Gomez, MIS, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to roll down changes to SOP’s Requested Ship Date to line items and purchase orders

Business Situation

A sales order entry clerk has been instructed to change all requested ship dates on a 100-line item sales order. This sales order is also linked to a purchase order for the 100 line items. However, the clerk has discovered that Dynamics GP will not allow these changes to take effect by changing only the Requested Ship Date on the transaction header and must go into each line item to accomodate the for the new request. In addition, the clerk must communicate to the company’s buyer that all line items on the PO must be changed to meet the new Required Date.

Solution

It would be much easier sometimes if certain Dynamics GP functionality was available out-of-the-box. However, that would leave a lot of us without a job .

Throughout the application, we get so many messages about rolling down changes across multiple records, but SOP seems to be one of those modules where rolling down changes to line items don’t seem to be a choice. Take for example Requested Ship Date field. Over and over, I have seem so many newsgroup posts asking for this feature. There are typically two approaches: a) you can create a SQL Server trigger to address the changes when the Requested Ship Date is updated from the SOP document header, or b) you can create a VBA customization that will ask to rolldown the changes, and if the user acknowledges, then run a stored procedure to make these changes happen.

I like both approaches, however approach (b) provides ample flexibility and allows for an interactive user experience. In this article, I will address option (a) and will follow up — in a second article — with option (b).

So let’s take a look at our SQL Server trigger first.

CREATE TRIGGER dbo.sopRollDownReqShipDate ON SOP10100 AFTER UPDATE ASIF UPDATE(ReqShipDate)BEGIN BEGIN TRAN

  -- roll down to all items  UPDATE A WITH (ROWLOCK) SET A.ReqShipDate = I.ReqShipDate  FROM SOP10200 A INNER JOIN INSERTED I ON (A.SOPNUMBE = I.SOPNUMBE) AND (A.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

  -- updated the Required Date column for linked POs  UPDATE A WITH (ROWLOCK) SET A.REQDATE = I.ReqShipDate  FROM POP10100 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER)    INNER JOIN INSERTED I ON (B.SOPNUMBE = I.SOPNUMBE) AND (B.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

  -- updated the Required Date column for the line items on linked POs  UPDATE A WITH (ROWLOCK) SET A.REQDATE = I.ReqShipDate  FROM POP10110 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) AND (A.ORD = B.ORD)   INNER JOIN INSERTED I ON (B.SOPNUMBE = I.SOPNUMBE) AND (B.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

 IF @@ERROR  0  COMMIT TRAN ELSE  ROLLBACK TRANENDGO

Let study our trigger for a bit. By using a BEGIN TRAN and checking for errors before committing out transaction, with SQL Server’s @@ERROR global variable, we are ensuring that our trigger will work as a single block of code. This is, we are only committing all changes if the updates were all successful.

By performing an UPDATE A WITH (ROWLOCK), we are allowing our trigger exclusive access to the rows involved in the update. This will prevent other changes from taking place on these rows while our trigger attempts to change the records.

Finally, our DML trigger makes use of the INSERTED special table to account for all header records being modified at once. You may say, well sales orders and invoices are updated one at a time in GP. This is true, but if you have an integration that insert records in bulk as part of a transaction, say for example orders coming from a CRM system through a BizTalk orchestration, you may need to address all these orders as one transaction block instead of individually. Also, working with INSERTED prevents the use of SQL Server cursors and allow the use of a set-based approach to our implementation.

Please stay tuned for the follow up post!

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Deck the halls with Report Writer

Business Situation

The holidays are around the corner and your organization is looking to send season greetings on each invoice to all customers. However, the traditional Microsoft Dynamics GP text comment is all too dull and does not reflect the image or the character of your organization. Your marketing team has spent some good hours designing a holiday banner and would like to see it on the invoices at all cost.

Lets take a look at some cool ways to deck the halls with Report Writer.

Solution

For this example, we will work with the SOP Blank Invoice Form report.

1) Open the Sales Transaction Entry window, select an invoice and click on the print button. When the options window is displayed, select the Blank form type and then print to screen.

* Click on image to enlarge

2) Press CTRL + F9 on your keyboard to modify the current report. This will open Report Writer with the report in the layout window.

3) We will now add a picture to the Picture library to include on our invoice. In Report Writer, go to the Resources menu and select Pictures. This will open the Picture Definition window.

NOTE: Due to Dexterity limitations in color management, you will need to reduce the number of colors of your graphic image to 16 colors (4 bits per pixel). The file size must be less than 32KB which corresponds to the size limitation imposed by Dexterity.

4) Click the New button to enter a picture name, say Season_Greetings. Click inside the gray area, and paste the image from the clipboard with CTRL+V. Click OK to save the image.

NOTE: The picture must be loaded with a graphics editor, like Paint for example, and have been captured with the Copy feature of that editor. This will send the image to the clipboard.

5) To add the image to the report layout, click the Picture tool button in the Toolbox window. This will activate the visual cue indicator.

6) Click on the area of the report where the picture will be placed, for example on the footer. Size according to your display needs.

7) Return to Dynamics GP and grant security to your modified report. If the report has been previously modified, you are done!

Execute the report and verify it meets the visual requirements and complies with your company expectations.

RELATED ARTICLES:

How to get large amounts of text to look good on a report – by David Musgrave @ Developing for Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

All about SOP distributions

Microsoft MVP Victoria Yudin seems to be everywhere in these days. If you have ever wondered how GP calculates all these pesky SOP invoice accounting distributions, wonder no more! Victoria decodes in a very friendly and illustrative manner how GP decides the fate of the accounting distributions to be posted to General Ledger when an invoice is posted in the Sales Order Processing module.

If I may add one more thing about the SOP distribution types, this field is used to categorize the distributions for the purpose of verifying the distributions on a document and can be framed in one of these 23 types:

1 = SALES2 = RECV3 = CASH4 = TAKEN5 = AVAIL6 = TRADE7 = FREIGHT8 = MISC9 = TAXES10 = MARK11 = COMMEXP12 = COMMPAY13 = OTHER14 = COGS15 = INV16 = RETURN17 = IN USE18 = IN SERVICE19 = DAMAGED20 = UNIT21 = DEPOSITS22 = ROUND23 = REBATE

The corresponding numeric value is stored in the SOP_Distribution_WORK_HIST table (SOP10102).

Until next post!

MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com