D365 Finance and Operations: Export posted item prices (trade agreements) using data management

The other day I was challenged to export item trade agreement prices (purchase and sales) which are posted and active and used in a particular legal entity and import them into another environment (or another legal entity) by using only OOB data management tooling.

At first you might think that this is not possible, as the standard data entity which allows this revolves around open (i.e. unposted) trade agreement lines and not active and posted item sales prices being used in the point of sale or e-commerce or call centre channels associated with that legal entity. The way to do it is as follows:

In Source environment / legal entity

STEP 1: Browse to https://<your-url>/?cmp=<your_cmp_name>&mi=PriceDiscTable_PricePurchAction directly from the browser address bar. Note that you can also use menu item name “PriceDiscTable_PriceSalesAction”. This does not influence the records displayed in the grid. Accessing this form directly in this way somehow breaks its designed filtering behaviour which, in this case, serves our purpose.

STEP 2: There you will see the POSTED trade agreements. Select them all (or the sub-set you need to copy) and click on the “Edit selected lines” button in the ribbon.

STEP 3: In the slider dialog that appears select a journal name and click ok.

STEP 4: When the process finishes generating the trade agreement journal you have created all your active price trade agreement lines into open trade agreement lines which means that the are now exportable via a standard data entity which is available out of the box.

STEP 5: Browse to the “Data management” work-space and click on the Export tile to create a new export project. Give a name and description to the project and create a line with data entity “Open sales price journal lines” data entity. You can also create a line with data entity “Open purchase price journal lines” data entity if this is what you require. Ensure to export to Excel format as you need to do some changes to the resulting file.

STEP 6: Click “Export” in the ribbon or click on “EXPORT OPTIONS” to export in batch

STEP 7: When the export is ready click on “Download file” and the Excel file will become downloadable in your browser

At this point, if you are exporting both sales prices and purchase prices, you should have two excel files at hand.

In Destination environment / legal entity

1. Create a new trade agreement journal header (or two headers if importing both sales and purchase prices) associating the relevant journal name. Once the header is created and saved please record the “Price discount journal number” field from this header record.
2. This must be copied to the “TRADEAGREEMENTJOURNALNUMBER” column of the Excel file(s) downloaded from the source system. Feel free to also modify other data in the Excel file for example site, warehouse, location dimensions or anything else you deem fit.
3. Browse to the “Data management” work-space and click on the Import tile to create a new export project. Give a name and description to the project and create a line with data entity “Open sales price journal lines” data entity. You can also create a line with data entity “Open purchase price journal lines” data entity if you also want to import purchase prices. Ensure to link the right data entity to the right excel file.
4. Click “Import” from the ribbon to start processing the import of your Excel file
5. When the upload is successful, you should see your journal lines as part of the journal(s) created in step 1 of the steps to be executed in the destination system / legal entity
6. Review that the journal lines look fine and post

At this point you are done! You have successfully exported active and posted trade agreement sales and purchase prices from a source environment or legal entity to a destination environment or legal entity using only out of the box functionality.