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 AS
IF 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 TRAN
END
GO

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/

Advertisements

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

  1. Bob Warren says:

    Hi Mariano,This is a great idea. We have a third-party addon that we purchased that (among other things) provides this functionality.What I’m wondering is if I might use a variant of this trigger to adjust requested ship dates on the SOP10100 and SOP10200 table lines as they are inserted or updated.We have our Dynamics system set to add 2 days to the Requested Ship Date field but the system does not account for weekends. Our order entry team spends all day Thursdays and Fridays manually changing the dates on every line item.I’m thinking to split this into two triggers – one on SOP10100 and one on SOP10200. Can I still make use of the IF UPDATE test and the INSERTED pseudo-table? I still only want to work with any changed (or inserted) records.Thanks,Bob

  2. Mariano Gomez says:

    Bob,Thanks for the follow up and your readership. You can certainly do a column test (IF UPDATE(ColumnName)) to perform changes based on your specific requirements. It sounds like you are on track with what you are already doing.All the code on this site is available for your personal use and can be modified to fit your specifics.Best regards,MG.-Mariano Gomez, MVPMaximum Global Business, LLChttp://www.maximumglobalbusiness.com

  3. Bob Warren says:

    Hi Mariano,I'm trying to add a trigger to either SOP10200 (first attempt) or SOP10100 (second shot) and both are failing. I can create the trigger successfully using the following code (SOP10200 version):CREATE TRIGGER dbo.tr_SOP10200_FixWeekendReqShipDateON dbo.SOP10200AFTER UPDATEASIF UPDATE(ReqShipDate)BEGIN BEGIN TRAN UPDATE L WITH (ROWLOCK) SET L.ReqShipDate = DateAdd(dd,2,I.ReqShipDate) FROM SOP10200 AS L INNER JOIN INSERTED AS I ON (L.SOPNUMBE = I.SOPNUMBE) AND (L.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3))IF @@ERROR <> 0 COMMIT TRANELSE ROLLBACK TRANENDGOWhen I then go into Dynamics and make any changes to the requested ship date on a line item I get a message box saying "A save operation on table SOP_LINE_WORK (45)" and that is all.If I adjust the SQL script to work on SOP10100 I get the same result except the error message references SOP_HDR_WORK. I can't find any references online to this error that seem relevant. Any ideas?Thanks,Bob

  4. Bob Warren says:

    I think I may have found the answer. It seems any errors in the UPDATE function will abort the trigger before it gets to the @@ERROR test. So I've rewritten as:CREATE TRIGGER dbo.tr_SOP10200_FixWeekendReqShipDate ON SOP10200 AFTER INSERT,UPDATE AS— Only fire if ReqShipDate changedIF UPDATE(ReqShipDate)BEGIN— Start a try blockBEGIN TRY — Start a transaction so we can rollback if any errors BEGIN TRAN — Set the first weekday to Monday for DatePart SET DATEFIRST 1 — roll down to all items UPDATE L WITH (ROWLOCK) SET L.ReqShipDate = DateAdd(dd,2,I.ReqShipDate) FROM SOP10200 AS L INNER JOIN INSERTED AS I ON (L.SOPNUMBE = I.SOPNUMBE) AND (L.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) WHERE DatePart(dw,I.ReqShipDate) > 5 IF @@ERROR <> 0 COMMIT TRANEND TRYBEGIN CATCH ROLLBACK TRANEND CATCHENDGOWhich seems to be working.Thanks,Bob

  5. Juan Decena says:

    Hola MarianoI know this an old post but what needs to be change in the Script to only update the requested ship date on the SOP lines items not Purchase Orders, we only want to change SOP not POP. Carlos "Convergence 2011"

  6. Carlos,The following should do only for Sales documents, not purchase orders: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 IF @@ERROR <> 0 COMMIT TRAN ELSE ROLLBACK TRANENDGOPlease let me know how it goes. Cordiales saludos!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: