Skip to Content
Product Information
Author's profile photo Parul Kapur

Offline Mass Update of Purchase Contracts through Microsoft Excel

Download and Upload of Multiple Purchase Contracts

Introduction

In this blog, we will discuss about the offline mass update capabilities of fiori app Mass Changes to Purchase Contracts, you can perform mass update for multiple purchasing contracts simultaneously. You can change the purchase contract header, item, and pricing condition data for multiple purchase contracts. Additionally, you can view the change logs to verify the status of the changes made.

Note:

  • This feature is supported from release SAP S/4HANA Cloud 2208 onwards.
  • To know more about updating pricing in purchase contracts refer to the blog.

Methods Used for Mass Update

Mass update on purchase Contracts can be done using the following two methods:

  • Online: Using the list view of the app: Search and view purchase Contracts using filter criteria such as supplier, purchasing organization, material group, and many more. Apply mass changes on the search results as required.
  • Offline: Using Microsoft Excel: Search and filter purchase contracts and download them in Microsoft Excel format. Make relevant changes in the downloaded excel file and save it. Upload the updated excel in the app view. The edits will be saved and updated for the purchase contracts.

What are the capabilities of offline mass update/create?

Offline mass updates help purchasers who want to create new contracts via Microsoft Excel or update the existing purchasing contracts by maintaining the required data in a Microsoft E file and later uploading it to the system. User also have an option to download the existing contract to the Microsoft Excel and later allowed to do the required update in the excel based on the business need. Configuration, steps, and generic recommendation to perform offline upload listed below.

  • Configuration required for Download / Upload.
  • Steps to perform offline download / upload
  • Generic Recommendation to perform offline upload.

Configuration required for Microsoft Excel Download/Upload:

In general, all the configurations are delivered by default, however user has the provision to modify some configurations based on the business need.

  • Login to the SAP S/4 HANA system with user having access to the role SAP_BR_BPC_EXPERT.
  • Navigate to the tile Manage Your Solution.
  • Click on “Configure your Solution” and search for the configuration step – Make setting that are relevant for changing standard text for purchasing documents.
  • Click on configure in the Configuration Step – Define Setting for Excel-Based Data Exchange
  • Select the Business object EXL_OPS_CTR for Purchase Contracts and navigate in the worksheet menu.
  • Select the work sheet ID and navigate into the worksheet description which would contain the list of worksheet language supported and fields exposed for each sheet. Purchase contract fields would be segregated in three different sheets Purchase Contract Header, Purchase Contract Item, and Purchase c=Contract Item Pricing Info for download and upload. Each sheet would be mapped to worksheet id and only the relevant fields would be exposed within the sheet.
  • Worksheet Description

  •  Worksheet Columns: Each worksheet id and relevant fields exposed would be listed in this configuration and user have provision to update the configuration like defining the sequence number, downloading of the field in Microsoft Excel and the editability of the fields with different configuration options.

Seq.no:

This helps to download the fields in particular order within each sheet, User have provision to select the sequence based on business need.

Download:

Business user could decide to download only the relevant fields which is required for business. Only fields which are checked in this configuration would be downloaded to excel.

Note: All Fields exposed in Purchase Contract worksheets is not set to download by default, only selected fields are downloaded by default. User has the provision in the configuration to mark additional fields for download based on the business need.

Key Field:

User not allowed or recommended to modify the configuration for key fields delivered in standard.

 Read Only:

If a particular field is marked as read only, any updates done to this field in the excel during upload would not be considered for update.

Mandatory:

Mandatory fields value required to be for purchase contract creation/update.

Download:

Business user shall apply filter and select the Purchase Contract based on the business need and click on download to download all the existing contract to the Microsoft Excel.

Note: All Fields exposed in Purchase Contract worksheets is not set to download by default, Only selected fields are downloaded by default. User has the provision in the configuration to mark additional fields for download based on the business need.

Steps to download:

  • Login to SAP S/4 HANA system with user having the role assigned
  • Open the App “Mass Changes to Purchase Contracts”.
  • Apply filter and select the Contracts based on the business need and click on Download.

Downloaded excel would have content segregated in three different sheets based on the configuration defined.

  • Purchase Contract Header
  • Purchase Contract Item
  • Purchase Contract Item Pricing

Upload:

Business user shall upload the excel content Via the Mass Changes to Purchase Contract app.

Operations Supported in Excel Upload:

  • Purchase Contract Creation
  • Update of Existing Purchase Contracts
  • Delete Purchase Contract Item

Purchase Contract Creation:

Note: Purchase contract creation is allowed only with Account Assignment Category U.

Steps to create contract using excel upload:

Step 1: In the downloaded excel template maintain dummy value (Ex #1 or any value) in the Purchase Contract column which would be common in all the sheets that would help to link all the header, item, and pricing condition info in all the sheets.

Step 2: Enter all the mandatory field values and other fields based on the business requirement.

Step 3: Set “Is New” Flag to X in all the sheets.

 Step 4: Save the excel locally.

Step 5: Open the app “Mass Changes to Purchasing Contracts”.

Steps 6: Click on Upload.

  • Browse the file from local system and upload (Only one excel can be uploaded) at a time.
  • Enter the upload job description and schedule upload.

 

  • Schedule upload job and navigate to Application Jobs application to analyse the status log of the offline job scheduled.

  • Click on Job Log icon to see the details of the jobs executed.

Update the Existing Purchase Contracts

Download existing purchase contracts to the Microsoft Excel and makes updates like quantity, price etc. based on the configuration defined and upload the excel to the system.

 Note: Purchase contracts with all account assignment categories can be downloaded and updated.

Deleting Purchase Contract

Purchase contracts can be marked for deletion at levels like General data, Purchase organization data. Set (X) Purchase Contract Flagged for Deletion in all the sheets or only at general data or purchase organization level based on the requirement.

  • Prepare the excel and upload the excel in the system.

Application Jobs:

Application jobs help us to monitor all the background job triggered for upload and the job result.

DISCLAIMER

All views expressed in this blogs are my own, it may also subject to change.

 

 

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Zhiping Yu
      Zhiping Yu

      Thanks Parul for sharing the very detailed steps, I will share it to one of my customers. They are asking some details about this feature.

      Author's profile photo peter rudman prieto
      peter rudman prieto

      Hello Parul, thank you so much for this information. I had the opportunity to activate it for central contracts and works quite good. Would be possible to share the config steps for this functionality in "On-premise" version?

       

      Thank you!!

      Author's profile photo Karthik Duraikkannu
      Karthik Duraikkannu

      Hi Peter,

      For On premise version, you could find the config in path OLME> Purchasing > Excel- Based Data Exchange

       

      Thanks and Regards,

      Karthik D

      Author's profile photo Falko Niehues
      Falko Niehues

      Hi Karthik,

      we are also using this function on the onpremise version in the latest release.

      The Issue is, that the customizing  in path OLME> Purchasing > Excel- Based Data Exchange does not contain any business object. So we don't know how the file structure for  EXL_OPS_CTR should look like. Do you have an idea how we can figure it out ?

       

      Kind Regards

      Falko

      Author's profile photo mustafa mutlu
      mustafa mutlu

      do you able to fıgure it out?

      Author's profile photo Falko Niehues
      Falko Niehues

      For CCTR excel-based file upload/download, you can follow the steps mentioned in chapter 3.1.3.6 in following 2ME Setup Guide to configure the required content.

      https://support.sap.com/content/dam/SAAP/Sol_Pack/Library/Setup/2ME_S4HANA2022_Set-Up_EN_XX.pdf

      Author's profile photo Jason Xu
      Jason Xu

      Dear Niehues,

      thanks for your sharing. It is very helpful for us. by the way, do you have CTR excel-based file upload /download configuration document? if yes, could you pls. share it to us also, many thanks in advance. I have configured it, it is possible to download and upload to change existed Contracts, but it is impossible to mass create contract via mass upload, the error message always shown as" Check the data". I had checked my upload template, all mandatory fields and Key fields had been maintained, I just guess maybe there have somethings wrong on configuration doc. therefore, I want to refer to your configuration doc. to do double check again., thanks!

      Author's profile photo mustafa mutlu
      mustafa mutlu

      hi Jason, i tried to implement but i couldn't able to do it.  i am happy to hear that you able to configure it, congratulations.  which version of on premise, you configured? Could you let me know main steps which you completed so i can try again. thanks in advance.

      Author's profile photo Jason Xu
      Jason Xu

      Hi Mutlu, I configured on the latest version(202212) of premise. you can follow the steps mentioned in chapter 3.1.3.6 in following 2ME Setup Guide to configure the required content. https://support.sap.com/content/dam/SAAP/Sol_Pack/Library/Setup/2ME_S4HANA2022_Set-Up_EN_XX.pdf

      Author's profile photo Amitkumar Ramesh Bafna
      Amitkumar Ramesh Bafna

      Hi Karthik Duraikkannu

       

      I am using S/4 HANA On-Prem version and did the setting as described in https://support.sap.com/content/dam/SAAP/Sol_Pack/Library/Setup/2ME_S4HANA2022_Set-Up_EN_XX.pdf but Upload and Download button are visible in Mass Changes to Purchase Contracts, but I can see in Mass changes to Central Purchase Contracts app.

       

      Can you please advice how to bring this functionality in Mass Changes to Purchase Contracts app ?

       

      Regards

      Amit.

      Author's profile photo Peter Neu
      Peter Neu

      I also tried to set up this in onPrem for purchase contracts. However download won´t show any data and upload would fail everytime.

      So I assume its the slight differences in the details that make the difference.

      Could you please advise on what needs to be changed?