Customization DS1320 Vendor Contract Pricing

Customization DS1320

Vendor Contract Pricing

Problem Definition:

While testing WilloWare’s Vendor Pricing module prior to purchasing and implementing it, ACME uncovered several requirements that could not be met by the module, and WilloWare determined that it would not be cost effective to modify the core product to address these requirements. Specifically, ACME needs to maintain the Vendor Pricing as a “Contract” and capture additional information pertaining to each contract, and they need pricing to be determined based on the Required Date rather than the Release Date.

ACME needs to report on the Quantity Limit on a contract, the Quantity currently on PO, and the Quantity Received. There needs to be a clear link between the Contract and the PO Lines created based on that Contract. Reporting will be created by RBC and/or their Partner.

One of the required Contract fields is “Threshold Percent”. When the total quantity on PO is within a specified percentage of the Contract Total Quantity (say 5%) it triggers warnings and alert emails, as follows:

• If creating a PO for the Item-Quantity will bring the total purchased within the Threshold Percent of the Contract Total Quantity, warn the user that the order will be within the Threshold Percent of the contract but allow them to continue (i.e. “Purchasing this item brings the contract within 5% of the contract total”). Email a notification to the Notifications Emails specified on the Contract.
• If creating a PO for the Item-Quantity will bring the total purchased to 100% or more of the Contract Total Quantity, warning the user that the order will exceed the contract, but allow them to continue (i.e. “Purchasing this item brings the contract over the contract total. The PO will be placed On Hold.”). Email a notification to the Notifications Emails specified on the Contract. Also, put the PO on HOLD.
• If creating a PO for an Item where the Required Date is after the End Date of the last available contract, the software will present a warning but allow them to continue (i.e. “The End Date for this Contract has expired.”) For example, if the Required Date is in August but the last available Contract has an End Date of July 31. Email a notification to the Notification Emails specified in the Contract.

The conditions above could result in multiple warning conditions. When that happens, the warning should provide the user with both.

The Threshold Percent and the Notifications Email Addresses will be stored in GP, but email alert will be handled via a SQL job created by ACME and/or their Partner.

The “warnings” mentioned above will be displayed inside Dynamics GP.

See the Solution Description section for the Contract fields.

Design Features:

Contract Maintenance

Navigation: Cards >> Purchasing >> Contract Maintenance

Field	Function
Contract ID	Start a new contract by creating new Contract ID. Click the NEW button to have the system auto-generate a new Contract ID. The Contract ID will be created using the 4-digit year and an incrementing number. For example: 202200001. This is a required field.
Description	Enter a description for the contract
Vendor ID	Enter a Vendor ID or select one from the lookup. This field is required.
Contract Number	Enter the Contract Number from the contract document provided by the Vendor.
Start Date/End Date	Enter the Start/End Dates for the contract. Both are required and End Date must come after Start Date. See Start Date/End Date controls below.
Quantity	Enter the contract quantity. Quantity needs to be greater than zero. This is a required field.
Warning Threshold	The percentage entered here affects when warnings appear in GP. For example, 5% will cause a warning to appear when adding an item to a PO and the quantity will bring the total ordered on the contract to within 5% of the Contract Total Quantity. 0% will cause no warnings to appear until the Total Quantity is reached/exceeded.
Notifications	Enter one or more emails for people to be notified when the Warning Threshold is reached and when the Contract Total Quantity is reached/exceeded.
VIEW	Items on Contract or Purchase Orders. See below for more details.
SAVE	Saves the Contract and clears the window.
DELETE	Asks user: Do you want to delete this contract and all linked Vendor Price Lists? If the user answers YES, the contract and all linked price lists will be deleted.
CLEAR	Clears the window without saving changes. NOTE: date changes are saved to the Contract and linked Vendor Price Lists immediately. Clicking Clear after a date change does not undo the date change.
NEW	Creates a new Contract ID
DELETE ROW	If a Vendor Price List is attached, the user will be ask: Do you want to remove this line and delete the linked Vendor Price List? If the user answers YES, the line will be removed and the price list deleted. If no price list is linked, the line will be deleted without prompting.
NOTE	Clicking the Note button opens up a window where the paperclip opens the Doc Attach window. This will allow ACME to attach scans of the Contracts to the Contract in GP.

Start Date/End Date Controls

When creating a new contract, as an item is added to the contract, the software will check that it does not conflict with another contract for the same Item and same Vendor. For example, if the new contract is for APR-1 to JUN-30, and the user enters ITEM-A, but that Vendor already has a contract for ITEM-A with a period of JAN-1 to JUN-30, the user will be warned of the overlap and prevented from using the item. An item can only be added to a contract when there is no overlap between contract start/end date for the same Item-Vendor.

When changing dates, the software will perform a check to ensure the date change would not overlap with an existing contract. Changing dates on an existing Contract will primarily occur if the Contract Total Quantity has not been reached and they extend the End Date to finishing consuming the total quantity. The check will be performed as follows:

1. Does the Vendor have any other Contracts where the new date falls with the Start/End dates of the contract? If not, then the change is allowed. If so, then:

2. Does the contract have ANY items in common with the current contract? If not, then the change is allowed. If so, the user will be warned as follows:

“Contract ID 202100003 / Contract Number 20209902231 has conflicting Start/End Dates. The date change is not allowed.”

At that point the date will revert to the original value.

If a date change is allowed, and if Price Lists exists in Vendor Pricing Maintenance for the Contract, changing the Start/End Dates will prompt the user: Do you want to change the Start/End Date on all linked Vendor Price Lists? If the user answers YES, the new date will be rolled down to all linked Vendor Price Lists. If the user answers NO, the date will revert to its original value.

Items On Contract View

To create a Vendor Price List for the contract, first add one or more Items to the contract. Items must be added to the contract before pricing can be created.

Specify the Quantity you expect to purchase of each item. The window will ensure that the total quantity entered for all items equals the Contract Total Quantity. You will not be allowed to save the contract or exit the window until the Item Quantity matches the Total Quantity. Attempting to Save, Clear, Scroll, Lookup, etc will be prevented, and the user warned, until the quantities match.

If there are NO items on the contract, the contract can be saved with whatever Contract Total Quantity has been entered.

After entering one or more items, click on an item, then click the Expansion Button (blue arrow) to open the Vendor Pricing window.

The Vendor Pricing Maintenance window will default to the Default Purchasing U of M. Item Number and Vendor ID will be populated automatically based on the Contract. Start Date and End Date will also default from the Contract and will be locked.

Purchase Orders View

When PO’s have been created based on the Contract, they will appear here in the Purchase Orders view. Click on a row in the window, then click the PO Number zoom (PO Number column header) to open the PO in the PO Entry window.

Contract Lookup

Opens from the Lookup button next to Contract ID.

Field	Function
Contract ID	Defaults to whatever portion of the Contract ID was entered on the Contract Maintenance window. This field will restrict the window to all contracts that contain the value entered into the Vendor ID field. For example, entering “2021” will display all contracts where the Contract ID contains “2021”.
Vendor ID	Enter a Vendor ID, or a portion of a Vendor ID. This will restrict the window to all contracts that contain the value entered in the Vendor ID field. For example, entering “33” will return all contracts where the Vendor ID contains 33.
Item Number	Enter an Item Number, or a portion of an Item Number. This will restrict the window to all contracts that contain the value entered in the Item Number field. For example, entering “DCP” will return all contracts that are for an Item Number containing “DCP”.
Contract Num	Enter a Contract Number, or a portion of a Contract Number. This will restrict the window to all contracts that contain the value entered in the Contract Number field. For example, entering “940” will return all contracts that have a Contract Number containing “940”.
View	Options are ACTIVE and ALL. Active shows only contracts with a Start Date on or after today. All shows all contracts.

Any of the fields at the top of the window can be used alone, or in combination, to restrict the list of contracts displayed.

Controls and Enhancements

See the Problem Definition section for the conditions that will cause warnings to be displayed, and the language used on the warnings. This is referred to below as the Warning Conditions. The warnings will appear in the following locations:

Purchase Order Generator

The Vendor Pricing Inquiry window will be modified to have a large “status” box at the bottom of the window. The warning will be displayed in a YELLOW BOX at the bottom of the window with bold black text. When the focus is in a line in the PO Generator window, and purchasing the item would trigger a Warning Condition, the warning message will be displayed at the bottom of the Vendor Pricing Inquiry window. The Vendor Pricing Inquiry window will be modified to display the Contract NUMBER.

NOTE: The GoTo button will include an option that provides access to the Contract Maintenance window.

Purchase Request Resolution

The Select Cost window will be modified to have a large “status” box at the bottom of the window. The warning will be displayed in a YELLOW BOX at the bottom of the window with bold black text. When the focus is in a line in the PRR window, and purchasing the item would trigger a Warning Condition, the warning message will be displayed at the bottom of the Select Cost window. The Select Cost window will be modified to display the Contract NUMBER on the expanded scroll window line (i.e. next to Vendor Name).

NOTE: A GoTo button will be added to the Select Cost window to provide access to the Control Maintenance window.

Purchase Order Entry

The Vendor Pricing Inquiry window will be modified to have a large “status” box at the bottom of the window. The warning will be displayed in a YELLOW BOX at the bottom of the window with bold black text. When the focus is in a line in the PO Entry window, and purchasing the item would trigger a Warning Condition, the warning message will be displayed at the bottom of the Vendor Pricing Inquiry window.

The Vendor Pricing Inquiry window will be modified to display the Contract NUMBER.

Changing the Quantity on the PO Line may cause the warning to appear, or disappear, if the quantity causes the Warning Condition to alter.

NOTE: The GoTo button will include an option that provides access to the Contract Maintenance window.

PO Line Link to Contract

When a PO Line is created, regardless of the source (i.e. PO Generator, Purchase Request Resolution, or PO Entry), the enhancement will link the PO Line to the Contract (assuming the item is contracted. This does not apply to non-contracted items). The software will always link a PO Link to the contract from which it was created. Even if the Contract Total Quantity has been consumed, and/or the End Date has passed. Such conditions are not normal and will result in the warnings and emails previously covered.

The linking is important because the links allow a calculation of the total quantity ordered on a Contract. Creating the link implicitly means the PO Line Quantity will be subtracted from the linked Contract Total Quantity.

The linking will occur as follows:

• The PO Line ProjNum field, which is not visible on the User Interface, will be populated with the Contract ID. This will allow for easy linking on reports between PO Lines and the Contracts that generated them.
• The PO Line Comment Text field (and Comment_1) will be populated with: “Cont#” and the Contract NUMBER. For example:

Putting the Contract NUMBER into this location will cause it to print on GP reports. If needed, ACME can add additional text below the Contract NUMBER line to have additional information print on the report.

Vendor Pricing

The Vendor Pricing will be changed to use REQUIRED Date rather than Release Date.

• Purchase Request Resolution has a Required Date field. That will be used instead of Release Date.
• Purchase Order Generator does not have dates. It is assumed the requirement is “now”. All pricing will be selected based on PURCHASE ORDER DATE from the Generate Suggested Purchase Orders window.
• Purchase Order Entry: Vendor Pricing will use the Required Date from the Purchasing Date Entry window. In the event a PO is created with different Required Dates on each line, Rise will need to change the Required Date in the Date Entry window BEFORE adding a line. Then enter the PO Line. Then change the date again as needed.

• The “U of M” label will be changed to “Purch U of M” on Vendor Pricing Maintenance and Inquiry will be changed.
• When the Vendor Pricing window is opened directly (i.e. not from a Contract line item), it will check if any Item/Vendor combination entered into the window is already on a Contract. If so, it will not allow the user to continue. The Vendor Pricing for contracted items must begin from the Contract Maintenance window. This prevents accidental creation of a price list for an item that conflicts with a Contract. Non-contract items can be set up in the Vendor Price Maintenance window without any restrictions.

Assumptions/Requirements

1. The functionality described above will not work with eConnect, any software that uses eConnect, or any software that directly writes to, updates, or deletes from SQL tables.
2. The functionality described above is intended for the GP Desktop client.
3. National Accounts functionality is NOT used.
4. Advanced Distribution functionality is NOT used.
5. Unless otherwise noted in this document, reporting is not included in this estimate.
6. Unless otherwise noted in this document, Word Template functionality is not addressed.
7. Unless otherwise noted in this document, the enhancement will not integrate with 3^rd party products. Some examples of 3^rd party products would be:

• An ISV plug-in product including WilloWare products
• A dexterity customization designed by another developer
• Dynamics GP Modules including, but not limited to:
• Project Accounting
• MDA
• Analytical Accounting
• Copy functionality found in SOP, POP and Inventory
• Field Service
• Extended Pricing
• Manufacturing

For Information on this customization, or any other WilloWare customization or product, please contact us at:

Contact Me (willoware.com)