#GPPT Purchase Order Processing Lock Cost Part 1

David Meego - Click for blog homepageI was recently working with a GP Power Tools customer who wanted to solve an issue with how the Purchase Order Processing (POP) module on their Microsoft Dynamics GP system functions.

They have international suppliers who specify the cost for their inventory items on a regular basis by sending through a price list document. Due to long lead times and shipping delays caused by the pandemic, cost pricing might change while the items are still in transit.

The issue is that when receiving a purchase order, the Originating Invoice Cost field on the Item Vendors Maintenance window is updated from the last received transaction, overwriting the current cost price provided by the supplier. The result is that subsequent purchase orders created have the incorrect cost value from the receivings transaction instead of from the supplier’s price list.

The question asked was “How can GP Power Tools prevent the Originating Invoice Cost field from being updated when inventory is received”.

This article takes you through the steps used to create this customisation from first principles. I intentionally did not do any research (such as looking at GP source code) before working with the customer on a screen sharing session.

Identifying the field (quick method, build 28+)

  • Open the Item Vendors Maintenance window (Inventory >> Cards >> Vendors) and select an item and vendor and click on Originating Invoice Cost field.
  • From the Tools menu on the window, select Resource Finder to open the Resource Finder window.

Note: If you open the Resource Information or Resource Finder windows BEFORE opening other windows and have the Show currently selected Window and Field information option selected, both windows will follow the currently focussed field.

Identifying the field

  • Open the Resource Information window (GP Power Tools >> Reports >> Resource Information) and ensure that it is set to Resource Type: Form, Window & Field and the Show currently selected Window and Field information option is selected.
  • Open the Item Vendors Maintenance window (Inventory >> Cards >> Vendors) and select an item and vendor and click on Originating Invoice Cost field.

  • The Resource Information window will now display the details of the field on the window, which identifies the field as ‘Last Originating Cost’.

  • Click on the Associated Tables button to show the tables on the form which contain the field.

  • Select the table by double clicking to swap the Resource Information window into Tables & Fields mode.

  • So now we have identified the field ‘Last Originating Cost’ (Last_Originating_Cost) and and the table it is stored in, IV_Item_MSTR_VNDR (IV00103).

Note: The Resource Information and Resource Finder windows are part of the the GP Power Tools – Administrator Tools module.

Create a logging trigger

The next step is to identify the scripts in Microsoft Dynamics GP that is responsible for updating the field. This can be handled simply with a logging trigger, so now we will create a project for our code and create the trigger to capture logs.

  • Open the Project Setup window (GP Power Tools >> Cards >> Project Setup). If a current project is displayed, click the Clear button to start a new project.
  • Enter Project ID: POP LOCK COST, Project Description: Prevent Originating Invoice Cost being updated on Receiving.
  • To make it easier to test, add the form you want to open using the Open Form hyperlink: IV_Item_Vendors in the 0: Microsoft Dynamics GP product.

  • Optional: Select the Current Project checkbox if you want the project opened by default.
  • Click Add >> Trigger Setup to open the Trigger Setup window.
  • Enter Trigger ID: POP LOCK COST, Trigger Description: Prevent Originating Invoice Cost being updated on Receiving, and select the Start Trigger Automatically on Login option.
  • Set Trigger Type: Table, Trigger Event: Save Record, Trigger Attach: After Table Event. On the Resource Tab, select Product: 0: Microsoft Dynamics GP, Enter Table Name: IV_Item_MSTR_VNDR and Field Name: Last Originating Cost.

  • On the Action Tab, select Display Message to screen using desktop alert. This will make a system alert show whenever the the field is changed.

  • On the Script Tab, no changes are required, the default script will detect when a record is added with a value in the field and when a record is updated and the field values has changed.

  • On the Options Tab, the Project ID will already be populated as will the default logging checkboxes.  Make sure that at least the Capture Dexterity Script Log option is selected.

  • Click Save and close the window.

Capturing the Logs

We now need to identify the script that is changing the field’s value in the table by capturing logs during the receiving process.

  • Prepare a Receivings transaction for the item you are using to test, but don’t post it yet.
  • On the Project Setup window, click Start >> Start Project Triggers.
  • To see that the trigger is running, click Add >> Trigger Status to open the Trigger Status window.

  • To see that the logging is active, from any window select Tools menu >> GP Power Tools Logging Control. The green “lights” on the GP Power Tools Logging Control window show you which logs are running.

  • Now that the trigger and logging is enabled, post the receivings transaction.
  • During posting you see a system alert pop up on the bottom right of the screen when the value gets changed.
  • On the Project Setup window, select Stop >> Stop All Triggers and Logging to stop the trigger and logging.
  • Click on the Configuration File Path hyperlink to open a Windows Explorer window to the logging folder. Sort by Date Modified (descending) to get the new log files showing at the top.
  • Open the just captured Script_<User>_<Company>_<Date>_<Time>.log file and scroll to the bottom.

  • Working backwards from the bottom you will find calls to the Trigger_Handler script in GP Power Tools. The first with a parameter of 2 is the table record being read and the second with the parameter of 8 is the table record being saved.
  • Working backwards from the Trigger_Handler calls, find a script that looks like it is specific to the process of updating the details for the Item Vendor information.
  • The actual script that has the save table command is Commit() of form IV_ItemVendor, being the first script above the Trigger_Handler script that is back one level. It is very likely that this generic script would be called from multiple locations.
  • For our customization, we are looking for a script that is more specific to the posting process that is not going to be called from anywhere else. Working backwards up the levels we can look at the calling scripts. In this case the best script looks like the form level function PostItemVendor() of form IV_TrxWorkLine.

Now, we have identified the script where the change to the table is being made, we should adjust the trigger to turn off the logging and the disable the system alert.

  • From the Project Setup window, double click on the POP LOCK COST Trigger to open the Trigger Setup window.
  • In the header section, uncheck the Start Trigger Automatically on Login option and check Do not activate Logging Mode and Minimize Log Entries options.
  • On the Action Tab, uncheck the Display Message to screen using desktop alert.

  • Click Save and close the window.

Please come back tomorrow for the next part of this customization example:

For more information see

David

This article was originally posted on http://www.winthropdc.com/blog.

2 thoughts on “#GPPT Purchase Order Processing Lock Cost Part 1

Please post feedback or comments

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.