CX Works

CX Works brings the most relevant leading practices to you.
It is a single portal of curated, field-tested and SAP-verified expertise for SAP Customer Experience solutions

Table Based Approvals in SAP CPQ

11 min read


Approvals are necessary in almost all implementations. But depending on the complexity and update frequency of the approval logic, moving to a flexible table based approval structure can assist in simplifying system maintenance.  The core concept is to remove information such as who the approver is, what the thresholds are from the approval rule itself. Instead these should be applied into a scalable set of tables that are then displayed in a Quote Table.  This Quote Table is then used to drive all of the approvals. Since no logic needs to be put into the approval rules themselves you can apply updates just by changing the tables. Further enhancements such as inserting in other approvers into the matrix can be performed on an ad-hoc basis.

It will take approximately 2-4 hours to complete all of the steps.

Table of Contents

Structure of the Custom Tables

The exact content of the tables is up to your specific use-case.  However, an overall guideline is that you should have three base tables.  This allows for significant flexibility without needlessly complicating the system.  All of these will be created using the Custom Table feature of SAP CPQ.  With it you can define your own structure for data including column names, data types, delegation of maintenance, and auditing.  In addition these tables can be maintained via API so that if there is a source for any of this data it could be maintained externally and updated in SAP CPQ, on a schedule.

Role Determination

This table defines the criteria that results in some role needing to approve. There are two columns that you should always add.  "Type" is the equivalent of an approval name. This classifies the rules into categories such as Management, Product, Financial, or Legal.  You can add as many types as you like.  The 2nd required column is "Role".  This the type of user in SAP CPQ that should be doing the approval.  The example below shows 2 hierarchies, one for sales management in the case of discounting, and a second for legal approval. After that you can add columns that have key criteria that can cause a quote to need approval.  For example we have a "Market", "Geography", and "Score".  Each of these is a data element found on the quote to be used in making an approval determination.  Think of deciding which roles are looked at as applying a filter.  If we apply a filter of "NA" to "Geography", and "Score" > 2.6, we would wind up with two rows.  A Sales Manager and a Regional Manager would be required to approve this quote.

Be careful when setting up the roles.  Don't add more than 1 role to a line.  Even in cases where there is a single condition with multiple approvers.  


Approver Ranking

This table defines which order the approvals should happen in. It is important for you to include this so that the order in the role determination table doesn't matter. New rules can be added in at the end of the table without the need to reorganize it.  Depending on how you  proceed with later steps, you might have parallel approvals happening.  In this case the sequence between parallel approvals isn't important. Only those within a single type will matter. In the below example, "Legal" has a rank of "3" while "Sales Manager" has a rank of "1".  However if both the legal and management approvals can happen in parallel both roles could approve the quote at the same time.

Role to User

This table will also have some required fields. "Role" and the "UserName" should always be included. These will take in the Role value from the first table as an input and provide a username as an output.

Your implementation likely has multiple approvers for the same role. There is then some criteria to determine which named user should be approving this. One such example would be "Geography", it could be the same as in the Role Determination table, or it could be different. There might be multiple criteria fields to decide which user needs to take action.  You want to keep this separate from the Role Determination table as the criteria for roles is often more broad. It keeps that table shorter and easier to read for example if there are legal teams that need to review changes to terms in 40 countries but it is always the legal role, you would have 1 row instead of 40.


Quote Tables 

Next you need a place to display this data. The best solution to this is using a Quote Table. Quote Tables are much like containers in the configurable products, except they are available on the Quote level.  A Quote Table will store and display all of the approvers that have been determined. It will also provide updates on who has approved, who the quote is pending on, and potentially other information deemed important such as a timestamp, comments, which rule triggered their determination, and others.

Quote Table Creation

A number of fields are needed in order to run the scripts successfully. Some may not be displayed depending on your use case. The columns "Type", "Role", "Approver" and "Status" are a minimum set. In addition, you may want "Comments", "Approval Date", "Backup Approvers", "Severity", among others. Your logic will need to be updated if you have additional requirements.

Creation of Quote Fields

You will need to also create some Quote Custom Fields in order to capture the data you want to use. These could be simple user selected choices, pulled from the customer record or calculated fields. For now you can make some simple user selected fields that can be used to test this process out. Once you confirmed their functionality you can change the data.

Scripting to Load the Table

Here you will need to write a global script to read in all the data from the tables and then populate the Quote Table. The key part of this is in forming a query to load the data correctly. Here's an example using the tables you previously created. It will return all rows where the geography and market match what is on the quote.  It also needs the score to be greater than or equal to what is on the Quote.

approval query
Market = Quote.GetCustomField("Market").Content
Geography = Quote.GetCustomField("Geography").Content
Score = float(Quote.GetCustomField("Score").Content)
approvalQuery = SqlHelper.GetList("SELECT TOP 100 type, market, geography, score, ApprovalHierarchy.role, rank FROM ApprovalHierarchy JOIN ApprovalRank ON ApprovalHierarchy.role = ApprovalRank.role WHERE (geography = '' OR geography = '{0}') AND (market = '' OR market = '{1}') AND (score is NULL OR score >= {2}) ORDER BY rank".format(Geography,Market,Score))

Once all of this is returned you can loop through the returned object and create rows in the quote table. You can find information on how to perform specific scripting tasks here and specifically on Quote Tables here.

In the end you should have something that looks like this:

Approval Rules

Next you need to create approval rules. Since the bulk of the work has been finished already, the idea is to simply use the data found in these tables to determine if the approval is needed and who it should go to. Typically an approval rule will have information such as your role, logic for each type individually and some type of method to determine who it is sent to. All of those were done in the script and are maintained in the tables and if done correctly, the need to update rules or scripts should be rare. The condition of your Approval Rule should look something like this.

[GT](<*TABLE(select count(*) from qt__Approvals where status != 'Approved' and cartid = <* CART_ID *> and ownerid = <* CART_OWNER_ID *>)*>,0)

Notice we are pulling from a table called qt__Approvals. This is the structure of how you access Quote Tables in your system. The qt__ is a prefix, followed by the table name.

Be careful to use the table Name instead of the Label

This will trigger an approval requirement if there are rows in the approval table that have yet to be approved. The cartid and ownerid gets the instance of the table specific to the quote you are currently in. Next is to assign an approver, which is usually done by marking specific users or by utilizing hierarchies (approving, ordering or managing parents). However we have the information in the Quote Table again.  

The custom approver logic here will find the first approver from the table that has yet to be approved and send it to that user. Since we are using usernames in our data table we have picked username as the result here.  At this point you are nearly finished.  You have data, a place to put it, fields that are used as key criteria, and a rule that finds that an approval is needed. The last piece is making sure that the table gets updated when approvals happen.  Since this isn't using the internal approval structure it is necessary to manually update this table.  

Updating the Quote Table

Approval Update
AllApprovers = Quote.QuoteTables["Approvals"]
approvalSet = 0
firstRow = 1
for row in AllApprovers.Rows:- This is often done using approval hierarchies or named approvers however we have the information in the Quote Table again.  
    if firstRow == 1 and row["Status"] == "Not Sent":
        row["Status"] = "In Progress"
        break
    firstRow = 0
    if approvalSet == 1:
        row["Status"] = "In Progress"
        break
    if row["Approver"] == User.UserName:
        row["Status"] = "Approved"
        approvalSet = 1
AllApprovers.Save()

This small script will run on the "Approve" or "Submit for Approval" action in the "Waiting for My Approval" tab in the workflow as well as the "Submit for Approval" under "My Quotes".  What it will do is find the first row that has the status "Not Sent" and change it to "In Progress". In the case of the initial sales user this will make it set it to the first approver. If the very first row that is not sent it means this is the sales user doing it, and in other cases it will be a later row. In this scenario it will find the row (likely the row right before their current) and change it from "In Progress" to "Approved". It checks to make sure the usernames match before changing it to approved.


Conclusion

Table based approvals are a powerful way to simplify system maintenance as well as providing insight into all of the approvals that are needed. Here we discussed the basic setup of such a process but you could easily extend this with your own requirements. Maybe you need to add in an approver uniquely to this quote. If so, you could modify the custom table with an additional row.

It is also important to know that even though you are changing where approvals typically get their data, you are still using the standard SAP CPQ approval process. This structure helps you create it as efficiently and maintenance free as possible.

Overlay