Using the AND_OP Attribute

The AND_OP attribute is used to enable the user to change the query filter operator dynamically. In the past. all operators were hardcoded in the SQL statement and appeared in the condition in the WHERE clause. The new attribute is used to inject the SQL operator (e.g., =, <, >, IN (….), <>) into the WHERE clause before execution.

The operator injection will be done only for optional parameters. The AND_OP attribute will be needed for each optional parameter.

The AND_OP syntax: <%AND_OP(“columnName”,<%?(paramName)%>)%>

columnName = The content of columnName must be surrounded with double quotes “ “. Inside the “ “ will be the column name to be filtered. The columnName format could be any legal SQL expression for column name, such as:

paramName = Unique identifier from the query parameter definition. It will be associated with a filter value that the user enters.

For example, if there are 2 parameters which can have filter values entered for them, and they have parameter definition names of E1.PROJECTXREF_OBJECT_ID and E1.SPECIAL_SAUCE, then the SQL string will be:

… WHERE T1.INACTIVE = <%?(T1.INACTIVE)%> AND T1.CONTEXTID = <%CONTEXT(projects.projects)%> <%AND_OP(“E1.PROJECTXREF_OBJECT_ID”,<%?( E1.PROJECTXREF_OBJECT_ID)%>)%> <%AND_OP(“E1.SPECIAL_SAUCE”,<%?( E1.SPECIAL_SAUCE)%>)%> AND T3.NEED_REMINDER = 1 ...

Each AND_OP attribute would be expanded to a legal SQL condition expression, depending on the operator that was selected by the user for each filter.

Each operator chosen from the UI will be translated to the matching SQL operator. For example:

If the “Equals” operator, is chosen, then

<%AND_OP(“E1.PROJECTXREF_OBJECT_ID”,<%?( E1.PROJECTXREF_OBJECT_ID)%>)%>

will be replaced wtih

AND E1.PROJECTXREF_OBJECT_ID = ?

The ‘?’ would then be bound with the parameter value that is associated with the parameter name.

A complete query string example:

SELECT <%RESULTS%>

FROM <%SCHEMA%>.FCI_MAS_COMPANY T1 <%DYN_JOIN%>

WHERE T1.INACTIVE = <%?(T1.INACTIVE)%>

AND T1.CONTEXTID=<%CONTEXT(masterdata.company)%>

<%DYN_FILTER%>

<%AND_OP(“UPPER(T1.DISPLAY_NAME)”,<%?(T1.DISPLAY_NAME)%>)%>

<%AND_OP(“T1.LOCATION_OBJECT_ID”,<%?(T1.LOCATION_OBJECT_ID)%>)%>

<%ORDERBY%>

Rules

Using the AND Attribute

The query string contains a placeholder (?) for each query parameter, including optional ones. Each placeholder is substituted with the parameter's value or with a type-specific default value when the parameter value is not provided.

The AND attribute is used to exclude a complex condition in the WHERE clause that is associated with one or more optional parameters when their values are not provided.

This attribute has parameter. The parameter is a string specifying the SQL condition that will be inserted in place of the attribute if all the query parameters have values provided for them. If no values are provided for any of the query parameters, this attribute will be substituted with an empty string. Values must be provided for all or none of the parameters that are named in the first attribute; otherwise, an error message will be displayed.

Example: a query string with one optional parameter that is part of a more complex SQL condition:

SELECT<%RESULTS%>FROM<%SCHEMA%>.FCI_MAS_VENDOR WHERE T1.INACTIVE = <%?(T1.INACTIVE)%> AND T1.CONTEXTID=<%CONTEXT(masterdata.Vendor)%> <%AND(“(T1.DISPLAY_NAME LIKE <%?(Name)%> OR T1.DOCUMENT_DESCRIPTION LIKE <%?(Name)%>”))%> <%ORDERBY%>

If the parameter value is provided, attribute substitution will produce the following:

SELECT T1.UNIQUE_DOC_NAME, T1.OBJECTID, 607 AS CLASSID, T1.DISPLAY_NAME, T1.DOCUMENT_DESCRIPTION, T1.CREATED_BY_USER_OBJECT_NAME, T1.MODIFIED_AT, UPPER(T1.DISPLAY_NAME) FROM FCI_MAS_VENDOR T1 WHERE T1.INACTIVE = 0 AND T1.CONTEXTID=-2147483548 AND (T1.DISPLAY_NAME LIKE ? OR T1.DOCUMENT_DESCRIPTION LIKE ?) ORDER BY 8

Using the CONTEXT Attribute

The CONTEXT attribute is an extremely important component of query development. The SAP Sourcing application allows master data objects and business documents to be associated with an organizational unit, and to limit access to those objects to users that are also associated with that business unit. Additional levels of security in the product provide more granular control, but the organizational unit level partitioning is the first level.

The SAP Sourcing application supports two levels of organizational units for this data security feature: enterprises and companies, where companies are contained within an enterprise. It is not necessary to use this partitioning in an implementation, which can be modeled as one enterprise, using other attributes to identify the business owner of the document, without blocking data access based on the organizational unit. Additionally, different classes of objects or documents can be scoped at different levels, for example, there can be one set of Supplier information shared across the enterprise, with RFPs, Auctions and Contracts isolated at the company level.

In most cases, it is desirable for queries to show users only those objects or documents that they subsequently have access to, for example, if there are RFPs associated with two separate companies within an enterprise stored in the database, users from one company should only be allowed to see the documents that are associated with their company. This is controlled in part by a CONTEXTID column in the top-level database row associated with that document, and managed by the SAP E-Sourcing framework.

For queries, the correct approach is to include a WHERE clause filter to check the context ID against the context association of the current user. The CONTEXT attribute simplifies this step: the software understands whether the object being queried is enterprise-scoped or company-scoped, and, based on the logged-in user, knows the ID of the user's associated enterprise or company. The required WHERE clause segment is therefore AND CONTEXTID = CONTEXT(name), where name is the logical class name of the object being queried.

To find the logical class name of an object, its integer class ID, or its table name, see the Class Reference section of the on-Line Reference Guide (RG). To see the table name, go to the object's schema view.

The CONTEXT attribute has a slightly different behavior for queries executed by sell-side users. For a company-scoped object (for example, RFxs), the CONTEXT attribute returns a comma-separated list of all of the context IDs that are children of the enterprise context containing the supplier. Since Suppliers and Contacts are typically enterprise-scoped, they can be invited to events by any company in any context that is part of that enterprise context. The unique sell-side behavior of the CONTEXT attribute ensures that sell-side queries can also see the full set of business documents in any context that is part of that enterprise. Supplier access to those documents is typically further controlled by whether the supplier is invited to participate. Using the comma-separated list returned by the CONTEXT attribute on the sell side implies a WHERE clause filter using the SQL IN, instead of the = operator used with single values.

Using the ARGn Attribute

This section describes the use of the ARGn attribute, including examples.

If a String, URL, or String Resource query column definition has the Resolve attribute(s) in Value checkbox set, and one or more attributes of the form <%ARGn%> returned in the value of the column, each of these attributes will be replaced by the string form of the nth result item following the defined column.

Example of providing date in user's format:

Multiple argument attribute example:

Resource ID example:

Other rules are as follows:

Using the CHECKCOLLAB_TBL Attribute

Some types of documents restrict access to a list of assigned collaborators. Query developers can determine whether the current user is a collaborator using this token.

The token resolves to SQL that performs the collaborator checks and also provides data about the kind of collaboration in a table whose alias is 'COLLAB'. It is included in the query as either an inner JOIN or a LEFT OUTER JOIN. In many cases, using this token will result in much better performance compared to using one of the check collaborator SQL functions.

This token takes the following three arguments:

CHECKCOLLAB_TBL( parent_objectid_column,

classid | logical classname | parent_classid_column,

keyvalue

)

The first two arguments relate to the JOIN columns and are required.

The first argument is the ObjectId column of the document. This is the document on which the collaborator check will be performed.

The second argument is the ClassId of the document. This can be the actual class ID, a column ID, or the logical class name. One or more of these values must be provided.

In some queries, the second argument can be a list of class ID entries and a column ID. If a class ID is provided, it will be used to filter the collaborator data and helps to improve performance. The class ID allows the scan of the collaborator table to ignore collaborator rows that match the document class.

The third argument is a list of key/value pairs. The third argument is optional.

There is 1 supported key:

IS_SILENT=TRUE or IS_SILENT=FALSE: Only specify if you want to restrict the query results to active collaborators or non-active (silent) collaborators. In general, IS_SILENT is not specified in queries. Use IS_SILENT=FALSE to limit results to active collaborators.

The COLLAB data contains the following columns; PARENT_OBJECT_ID, PARENT_CLASS_ID, IS_SILENT. All these fields are set unless a LEFT OUTER JOIN is used. In this case, all these fields will be NULL when the user is not a collaborator on the document.

IS_SILENT is either 0 or 1. 0 indicates that the user is a collaborator who receives notifications when the document is modified and the changes are configured to send alerts and e-mails. 1 indicates that the user does not receive notifications and generally indicates that the user is not expected to be an active participant on the document.

SQL can be included in a query that checks the COLLAB data. This is more common when this token is used with a LEFT OUTER JOIN. In this case, the test is a simple CASE. For example:

CASE WHEN COLLAB.PARENT_OBJECT_ID IS NOT NULL THEN <%LOAD_PAGE(contracts.Agreement,T2.OBJECTID)%> ELSE NULL END AS LINK,

Using Multi-String Related Attributes

Since Multi String Master Data’s name and description values are saved in its own multi string sub table, query developer needs to join to that table to get the correct localized values. A few new attributes have been created to make the query development easier.