How to Filter Planned Purchase Orders on Existing Purchase Agreements

Purchase Agreements are an important functionality in Dynamics 365 for Finance and Operations (D365FO). When I firm planned Purchase Orders, it is possible that the system will automatically link the created Purchase Order to an existing Purchase Agreement, but this option has to be allowed in the Master Planning parameters.

This is the parameter that has to be set to “yes.” Less important or rather irrelevant is the one above, “Group by Purchase Agreement.” When firming a number of planned Purchase orders, the system would create one multi-line Purchase order per Purchase Agreement? When I say “yes” to this parameter, the field “group by vendor” also switches to “yes.” That tells you enough. The parameter itself seems to have no meaning and cannot have any effect because when I have two purchase agreements for the same vendor for the same item in the same time period, the firming process will always take the one with the lowest Agreement ID.

In addition, and this is a key detail, the link between a PO and a Purchase Agreement is on the Header level. It is a violation of database schema to have one PO with PO lines linked to different Purchase Agreements. From this perspective the parameter seems redundant and is maybe anticipating the design change that one PO can be linked to different Agreements for the same vendor, depending on the item. We made this customization for a food distribution customer.

Using joins to get our filter

As always we start from unfiltered planned Purchase Orders and we will have to define a few joins to get our result. After a few failed attempts I think we have found the right way to create these joins.

From planned orders we join n:1 to the Vendors.

Highlight this table, do NOT hit OK but hit Select in the top left.

From vendors, we join 1:n to Purchase agreement (Company). Hit Select in the top left.

First join completed.

Now we return to the table Planned orders for join number 2.

From planned orders we join n:1 to Items.

From items we join 1:n to Agreement line (Item number)

We are done joining. Return to the Range tab.

Add the following two lines to the Range.

Agreement line: We are looking for existing Agreement lines for the item in the planned Purchase order that expire after today.

Purchase agreement: We are looking for Purchase agreements for the vendor in the planned purchase order that have status “effective.”

Our first attempt did not include the filter on Agreement line. That was a mistake because the system was not checking the item number in the planned purchase order and only filtered on vendors who have effective Purchase agreements.

When we added the Agreement line, it was not clear which field we would want to use, we just wanted the agreement line to be there. Expiration date is a logical choice but you could choose another field in the Agreement line.

Result: You can create a tile for all Planned Purchase orders that are going to “consume” an existing Agreement.

Business benefit: The buyer who is responsible for Purchase Agreements can better control their fulfillment. WARNING: Important to know is that the firming process will NOT send a warning when the effective Agreement line for the item is almost exhausted. If the full quantity on the planned purchase order is greater then what is left on the Agreement line, the system will create a Purchase order without connection to the Agreement. It will not take “whatever is left” on the Agreement line.

There is also no warning when the Purchase Agreement is expired or not effective. The system simply creates a stand-alone PO, which is all the more reason to have a special tile for the planned Purchase Orders that are supposed to “consume” Purchase Agreements!

Remember the existence of the Fulfillment report, that I wish we could add as a link to our Workspace.

Have questions about purchase agreements and Dynamics 365 Finance and Operations? Our experts are here and ready to help!