sshr ame approval

Configuring Oracle Approvals Management (R11i10) for Oracle Self-Service Human Resources V4 An Oracle White Paper August 9th 2005 (Added example of Position Support & example support functions)

Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 2

Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4

Executive overview............................................................................. 4 Introduction......................................................................................... 4

References....................................................................................... 5 SSHR Transaction Concepts............................................................... 5

Transactions Data Structures .......................................................... 5 Relating Transactions Data Across SSHR, Workflow, and AME.. 6

Approvals in SSHR............................................................................. 7 Basic Approvals Loop..................................................................... 7 Resolving Routing Decisions with AME or Customizable PL/SQL8 Calling AME from SSHR ............................................................... 8

Oracle Approvals Management .......................................................... 8 Overview......................................................................................... 8 Configuration .................................................................................. 9

Rules, Conditions and Attributes ................................................ 9 Operation......................................................................................... 9 Transaction Types and Transaction Categories ............................ 10

Headers and Line Items............................................................. 10 Reasons for Having Multiple Transaction Types.......................... 11

Different Attribute Usages ........................................................ 11 Reasons for Combining Multiple Transaction Categories............ 11

Common Attribute Usages........................................................ 11 Multiple Step Transactions Combining Different Transaction Categories ................................................................................. 11

Default AME Configuration For SSHR........................................ 11 Example Scenario ......................................................................... 12

Example Hierarchy ................................................................... 12 Example Transaction 1 Default Configuration ...................... 13

Analyzing Your Business Requirements .......................................... 15 Planning Grid ................................................................................ 16

Mapping Business Requirements to AME Configuration ................ 17 Turning on Approvals ................................................................... 17 Starting Points for Chains for Authority....................................... 18 Authority Levels............................................................................ 19 Default Approval Policy ............................................................... 19


Example Transaction 2 Personal Information........................ 20 Salary Change Policy .................................................................... 20

Example Transaction 3 Salary Change .................................. 21 Transfer Approval Policy.............................................................. 22

Example Transaction 4 Simple Transfer................................ 24 Example Transaction 5 Transfer Direct Reports.................... 24

Other Capabilities ......................................................................... 25 Configuring Attributes ...................................................................... 25

Identifying What Attributes Are Needed ...................................... 26 Policy Attributes ....................................................................... 26 Chain of Authority Attributes ................................................... 26 Conditional Attributes............................................................... 28

Attribute Usages............................................................................ 29 Transaction Categories.................................................................. 30 Header Level or Line Level .......................................................... 30 Transaction Types ......................................................................... 31 Migrating Configurations Between Instances ............................... 31

Summary of Configuration Methods and Skill Levels ..................... 32 Conclusion ........................................................................................ 32 Appendix A Attribute Usages........................................................... 33 Appendix B Functions ...................................................................... 36 Appendix C Dynamic Approval Groups........................................... 39 Appendix D Position Hierarchy Support .......................................... 41 Appendix E Example Support Functions.......................................... 45


EXECUTIVE OVERVIEW Your enterprise employs thousands of people in many departments around the world. Naturally, you require your Human Resources business processes to be conducted as efficiently as possible so that employees and managers are free to focus on their primary tasks. However, you also need assurance that all transactions are subject to effective controls. This paper describes how, within the Oracle e-Business Suite, you can exploit the flexibility of Oracle Approvals Management (AME) across the breadth of Self-Service Human Resources (SSHR) to establish and enforce appropriate approval rules for any kind of transaction.

RELEASE LEVEL Important: This document is meant for R11i10 customers and forward as there are changes to the construction of line level attributes for this release.

INTRODUCTION The decentralization that is possible with self-service applications is only practical if transactions are subject to appropriate management approval before they become effective. This requires an approval mechanism and the ability for system administrators to configure it to match their enterprise approvals policies, which may be different for different business process and in different organizations.

Since the earliest releases, SSHR modules, where appropriate, have been designed on top of an approvals workflow and provided with various configuration options to allow system administrators to turn approvals on or off for different processes. At certain points in the approvals workflow, SSHR calls a customizable PL/SQL package which customers may choose to modify to implement more complex approvals rules. A limitation of this approach is that a business process owner wishing to make changes to approvals policies has to enlist the services of a PL/SQL programmer.

To shift control of approvals as far as possible into the hands of the business process owners (for all self-service applications in the e-Business Suite, not just SSHR), Oracle has introduced the new Oracle Approvals Management (AME) application. AME is a generic, rules based approvals engine with a user-friendly configuration interface and an extensible architecture which is capable of meeting the needs of the most complex enterprises.

AME was first released in December 2001 and is subsequently being integrated into all e-Business Suite applications that use approvals. SSHR release 4.1 was the first version of SSHR to integrate with AME. By default it uses AME services to build and track the approval list for a transaction. (However, customers have the option to revert to the customizable PL/SQL package if they wish.)


The AME configuration delivered with SSHR releases 4.1 and 4.2 simply emulates the default logic delivered in the customizable PL/SQL package. The purpose of this paper is to provide general guidance on further configuring AME in association with SSHR. The paper begins with a review of SSHR transaction concepts and in particular the handling of approvals. This is followed by an overview of Oracle Approvals Management and an explanation of how AME integrates with SSHR. Finally, the paper provides example approval rules to support some typical business requirements in the Human Resources arena.

Note: Although this paper focuses on the integration of SSHR with AME, most of the principles covered here would be applicable to the integration of any application that calls AME. Conversely, this paper does not attempt to describe all of the capabilities of AME.

References This paper is intended to supplement the following reference materials which should also be studied for a full understanding of the topic.

Implementing Oracle Approvals Management

http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?282964

Implementing Oracle Self-Service Human Resources (SSHR) 4.2

http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?284440

SSHR TRANSACTION CONCEPTS A user-friendly interface guides the user through a series of options, gathering input until the user is ready to submit the transaction. The details of the transaction are held in temporary storage in the database until all necessary approvals have been recorded, at which time the changes become permanent. Meanwhile, attributes of the pending transaction may be used as the basis for approval rules.

Transactions Data Structures Although a business user should be able to configure rules and conditions without concerning themselves with the underlying details, knowledge of where an applications transaction details are stored is essential to the successful design and implementation of the approval attributes on which rules and conditions will be based.

Note: The information in this section applies specifically to SSHR, which includes the self-service components of Benefits, HR, Payroll, and Training. If you are creating attributes for another application (such as OTL, iRecruitment or any non-HRMS application) you will need to research the relevant documentation to determine the location of that applications temporary transaction data.


SSHR stores transaction data in a series of three master-detail-detail tables which have names beginning with HR_API_TRANSACTION. The table names and relationships are shown in the following diagram.

Each individual SSHR transaction is represented by a single row in HR_API_TRANSACTIONS and at least one row in HR_API_TRANSACTION_STEPS. The header row in HR_API_TRANSACTIONS contains a unique transaction_id and other attributes common to all transactions such as the requestor and timestamp.

In general, each row in HR_API_TRANSACTION_STEPS corresponds to an API used by the transaction to validate and/or commit data to the applications tables. Complex transactions (for example, workflow chained processes such as Employee Status Change V4.0) may have multiple steps. For example, one step may correspond to an Assignment API and another to a Pay Rate API.

Specific transaction information relating to each step is held in HR_API_TRANSACTION_VALUES in the form of parameter-value pairs. For example, a salary proposal transaction step may have a value row with Name = P_PROPOSED_SALARY and Number_Value = 50000.

These tables contain many of the attributes on which customers will want to base approval rules and conditions. Other attributes may be stored in other tables and derived by joining on foreign key information in the HR_API_TRANSACTIONS tables (for example, to get person or assignment information for the person selected in the current transaction).

Relating Transactions Data Across SSHR, Workflow, and AME Some information about a SSHR transaction may also be found in the internal tables of Oracle Workflow and Oracle Approvals Management. These other

Figure 1 SSHR Transactions Tables

HR_API_TRANSACTIONS

HR_API_TRANSACTION_STEPS

HR_API_TRANSACTION_VALUES

applications provide generic services to a wide range of applications and therefore have their own methods of uniquely identifying a transaction as depicted in Table 1.

For SSHR transactions, the Transaction Id used in the AME unique identifier has the same value as the Transaction Id used in the HR_API_TRANSACTIONS table.

Note: AME requires the calling application to supply three pieces of identifying information with each transaction - Transaction Type, Application Id, and Transaction Id and it is the responsibility of the calling application, in this case SSHR, to ensure that the Transaction Id is unique within a Transaction Type.

For each transaction, SSHR stores the corresponding Workflow Item Type and Item Key as attributes in the HR_API_TRANSACTIONS table.

Note: SSHR generates the Item Key and Transaction Id values from different sequences.

APPROVALS IN SSHR

Basic Approvals Loop Note: The information in this section applies specifically to SSHR. For other applications, consult the relevant documentation.

All approvals mechanisms used in SSHR follow the basic approvals loop shown belo e hierfetc appsim

Table 1 Transaction Identifiers

Application Unique Identifier Example

Workflow Item Type

+ Item Key

HRSSA

12345

SSHR Transaction Id 65432

AME Transaction Type

+ Application Id

+ Transaction Id

SSHRMS

800

65432

w. The logic checks whether the current approver is the final approver in tharchy. If the current approver is not the final approver, the application hes the next approver who then receives the approval notification. The nextrover can either reject the transaction, approve the transaction, or (for plicity, not shown here) return the transaction for correction. Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 7


Refer to Approvals chapter of Implementing Oracle Self-Service Human Resources (SSHR) 4.1 or later for more information on this workflow process.

Resolving Routing Decisions with AME or Customizable PL/SQL The key decision points in this flow are the boxes labeled Final Approver? and Get Next Approver in the above diagram. Prior to the availability of AME, SSHR would resolve these decisions by executing corresponding functions in the customizable PL/SQL package HR_APPROVAL_CUSTOM. However, starting with release 4.1, it has been an option for SSHR to call equivalent AME APIs instead.

Calling AME from SSHR For any SSHR module to use AME, the AOL function that calls it must include the parameters pAMETranType and pAMEAppId. (SSHR adds TransactionId, the third element of the unique key, at runtime.)

Note: From SSHR release 4.1, seeded functions are delivered with these parameters in place and set to pAMETranType=SSHRMS and pAMEAppId=800. To revert to the customizable PL/SQL package you would strip these parameters from your custom functions.

ORACLE APPROVALS MANAGEMENT

Overview AME provides list management services to calling applications such as SSHR.

Note: AME does not manage the approvals flow, or the notification of approvers. These remain the responsibility of the calling application.

Figure 2 SSHR basic approvals loop

Yes

End (Rejected)

End (Approved)

Notify next approver

Get next approver

Start

No

Yes

No

Final approver?

Approved?


Configuration

Rules, Conditions and Attributes

Rules are defined in terms of conditions (expressed as an attribute and a range of values) and the corresponding required approval action.

Attributes and Attribute Usages

Attributes are defined in terms of an attribute name and attribute usage which may be either a static value or a dynamic SQL statement.

Approval Types and Approval Handlers

Approval actions are expressed in terms of an approval type, for example, a chain of authority based on supervisor level, associated with an approval handler. Approval types and handlers have been seeded for several variations on pre approvers, chain of authority approvers, and post approvers.

Operation The operation of AME in conjunction with SSHR can be described at a high level as follows. The description would apply equally well if you substituted another calling application (for example, Web Expenses) in place of SSHR.

A user submits a transaction. The transaction is associated with a specific transaction type (which can be configured by the system administrator during set-up) and a unique transaction_id (which is generated by the system at runtime).

SSHR passes the transaction type, application id, and transaction id to AME which generates the list of approvers based on the rules configured for this transaction type.

AME evaluates the conditions associated with the rules for the current transaction. For any rule that satisfies all its conditions, AME identifies the ordered list of approvers who must approve the transaction. The results from multiple rules are merged into a single ordered list in which pre approvers come before chain of authority approvers and post approvers come last.

SSHR presents the list of approvers to the user in the Review page. The user may choose to insert one or more additional approvers into the list using the Dynamic Approvals functionality. If so, SSHR passes this information on to AME to modify the list.

SSHR notifies each approver in turn. SSHR notifies each approver in turn. SSHR tracks each approvers response (approved or rejected)


and subsequently returns the Id of the next person on the list who has not yet approved the transaction. AME is iteratively called to set each approvers response (approved or rejected) status.

When there are no more approvers to be notified, SSHR records the transaction as complete.

Transaction Types and Transaction Categories AME is able to partition the approvals configuration for dissimilar transactions into different Transaction Types. Generally there will be at least one transaction type, possibly more, per calling application. For example, the transaction type SSHRMS is seeded for SSHR, but you may need additional transaction types depending on your requirements.

As you start to analyze your business requirements for approvals management you may find it useful initially to group your transactions into transaction categories and only later decide how many different transaction types you need to accommodate them.

Note: Transaction Category is not a defined term in either SSHR or AME. However it is used throughout this paper to identify distinct categories of transactions from a business perspective and as a stepping-stone to identifying distinct transaction types.

Start by formulating an approval policy (in other words, a set of rules) for each transaction category in a format that can be reviewed by the business owners. Next identify the attributes, conditions, and rules that will be needed to implement the rules for these policies.

Headers and Line Items

A single transaction may consist of a header record and multiple detail records, for example, an expense report. Transaction types for these categories of transactions need to define a line item query which AME will use to retrieve individual line items associated with a transaction id.

In SSHR you should consider taking advantage of this feature for transaction categories that have multiple lines in HR_API_TRANSACTION_STEPS for each row in HR_API_TRANSACTIONS (see Figure 1 in the Transactions Data Structures section), unless you have no need to reference step level attributes in your rule conditions.

A line item query would look like this:


select hats.transaction_step_id from hr_api_transaction_steps hats, hr_api_transactions hat where hats.transaction_id = hat.transaction_id and hat.transaction_id = :transactionId order by hats.transaction_step_id

Refer to Implementing Oracle Approvals Management for guidance on the syntax of a line item query. You will need to create an Item Class of Line Item since this is not seeded via the SSHR Integration.

Reasons for Having Multiple Transaction Types

Different Attribute Usages

If you find that your transaction categories have quite different attribute usages and unrelated rules, you will probably find it convenient to separate them into different transaction types. One advantage of this approach is that you can specify a transaction type as a securing attribute on the Limited Business User responsibility and use this to limit the scope of the configuration changes that a particular business user can make.

Reasons for Combining Multiple Transaction Categories

Common Attribute Usages

There is currently no provision in AME to share or copy attribute usages among transaction types. Consequently, if you discover significant commonality between the attribute usages of several transaction categories you may find it convenient to lump them all into a single transaction type.

Multiple Step Transactions Combining Different Transaction Categories

SSHR is unusual in that a single transaction may consist of several steps which are quite different in nature and have different attribute usages. For example, the Employee Status Change V4.0 function allows the user to combine assignment changes, a salary proposal, a change of manager, and other activities into a single transaction. You will need to combine all the transaction categories that can occur in a single transaction into a single transaction type.

Default AME Configuration For SSHR The AME configuration delivered for SSHR in releases 4.1 and 4.2 comprises the following components:

A single AME transaction type SSHRMS with multiple attribute usages,

A single condition (WORKFLOW_PROCESS_NAME attribute is in a configurable list of process names), and


A single rule (if the WORKFLOW_PROCESS_NAME condition is true, require approvals, using the approval type 'chains of authority based on number of supervisory levels', up to the top of the approval hierarchy or to 10 levels above the initiator, whichever comes sooner).

Example Scenario The concepts involved in approval routings are best understood in the context of example scenarios using a typical hierarchy.

Example Hierarchy

For the example scenarios described in this paper we will use the example hierarchy depicted in the following diagram. Each block represents a person and the lines represent the supervisor relationships between them.

Note: To avoid over complicating the diagram, each person has only a primary assignment. However, SSHR and AME can function equally well if people have more than one assignment.

The label on each person block indicates the name of the Person (for example, P21), the name of their current Job (for example, JB denotes Job B) and the Approval Authority level associated with that job (for example, L2 indicates approval authority level 2).

In the example scenarios we will generally select person P21 (highlighted in the diagram), initiate a transaction (which may involve other people) and then consider which people should approve the transaction.

Example Transaction 1 Default Configuration

In example transaction 1, person P31 logs into SSHR, selects the Change Base Salary V4.0 function, and initiates a transaction for person P21. The Change Base Salary V4.0 function is configured to use workflow process HR_P_RATE_JSP_PRC and AME transaction type SSHRMS.

The user progresses through the transaction to the Review page at which point SSHR calls AME and passes the Application ID (800), Transaction Type (SSHRMS) and Transaction ID (system generated).

AME builds the list of approvers for transaction 1 based on the rules defined for transaction type SSHRMS. There is only one rule, with a single condition based on the WORKFLOW_PROCESS_NAME attribute which, in this case, evaluates to the value P_PRC. Since this process name is in the conditions c the condition is true, the rule executes and AME runs the app the 'chains of authority based on number of supervisory ype. The default configuration requires AME to start with the

Figure 3 Example Supervisor Hierarchy

P12 JA L1

P11 JA L1

P13 JA L1

P21 JB L2

P31 JC L3

P41 JD L4

P51 JE L5

P15 JA L1

P35 JY L3

P26 JX L2

P25 JX L2

P27 JX L2

P55 JZ L5

Legend P21 = Person 21 JB = Job B L2 = Approval Authority level 2

P01 JH L3HR_P_RATE_JSonfigurable list,

roval handler forlevels approval tConfiguring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 13

transaction requestor (P31) and go up the supervisor hierarchy 10

levels (or to the top if there are fewer than 10 supervisors above the requestor). The initial list of approvers is, therefore, as shown in Table 2a below.

AME returns this list of approvers to SSHR which displays the names to the user in the Review page.

Table 2a Approvals for Example Transaction 1, Initial Status

Sequence Approver Notes Approved?

1 P41 Transaction requestors supervisor No

2 P51 2nd supervisor above the requestor No

Successive chain of authority approvers No


SSHR then asks AME for the name of the next approver who has not yet approved the transaction and AME responds with P41. The process is repeated until all approvers have approved.

Note: If the top of the supervisor hierarchy is reached before the specified number of levels, AME compares this persons ID with the value configured in the TOP_SUPERVISOR_PERSON_ID attribute and raises an error if they are not the same. (This prevents a transaction being approved at an unintended level in the case of an unplanned break in the supervisor hierarchy.)

When AME responds to SSHR that there are no more approvers who have not approved the transaction, SSHR applies the transaction data to the application tables (using the appropriate API) before clearing it from the transaction tables.

ANALYZING YOUR BUSINESS REQUIREMENTS Actual business requirements for approvals are likely to differ in one way or another from the behavior described in the above example. Listed below are some common variations:

Determine final approver in terms of job level rather than the number of steps up the hierarchy

Table 2c Approvals for Example Transaction 1, After First Approval

Table 2d Approvals for Example Transaction 1, after Final Approval


1 P01 Inserted Dynamic Approver Yes

2 P41 Transaction requestors supervisor No

3 P51 2nd supervisor above the requestor No

Successive chain of authority approvers No


Require pre and/or post-approval by one or more people, perhaps identified by their roles in relation to the transaction requestor.

Require different approvals for different kinds of transaction

Require different approvals based on transaction attributes (for example, the size of a proposed salary change)

Base chain of authority on position hierarchy instead of supervisor hierarchy

Note: A position hierarchy based approvals handler is not among the handlers that have been delivered with AME at the time of writing this paper, although future delivery is intended. Customers wishing to take advantage of AMEs extensible architecture to write their own approvals handlers should conform to the development guidelines provided by the Approvals Management development team.

Planning Grid Before starting to set up AME, it is a good idea to organize your approval policies into a planning grid, such as the one shown below, to clarify the level of approval (and any pre or post-approvers) required for each category of SSHR transaction, taking into account any particular conditions that might apply.

Table 3 Approvals Policy Planning Grid

Transaction

Category*

Details Pre Mgr VP SVP Post Notes

Salary Change Pct increase

30%

HR Y Y Y

Transfer Reassign

selected

employee to a

different

manager

Y Y Must be

approved in

both from

and to

management

hierarchies

Transfer Assign new

direct reports to

Various Y Y Must be pre

approved by MAPPING BUSINESS REQUIREMENTS TO AME CONFIGURATION We will now examine how to configure each of the above policies in AME. Although these represent only a few examples, they can be extrapolated to cover most eventualities. In this section, we discuss at a high level some of the attributes that will be needed in AME. A more detailed description of each attribute comes later.

Turning on Approvals SSHR provides workflow attributes with which you can turn approvals on or off for individual functions. However, configuring workflow attributes requires levels of skill and access not generally held by business users. An alternative strategy, once you have made the decision to use AME, is to have a system

the selected

employee. The

direct reports

currently report

to various

different

managers

the various

managers

involved

Other Default Policy Y The selected

persons

manager

*Note: Refer to the earlier section Transaction Types and Transaction Categories for an explanation of this term. Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 17


administrator set all the relevant workflow attributes to Yes (or Yes Dynamic Approvals) so that the business users are able to exercise full control in AME. Refer to Implementing Oracle Self-Service Human Resources (SSHR) 4.0 or above for guidance on how to change the workflow attributes.

Starting Points for Chains for Authority If you are using a chain-of-authority approval type you need to consider with whom you wish the chain to begin. For single chains of authority, AME defaults to the transaction requestor (in other words, the person initiating the transaction), but you can override this if you wish. For dual chains of authority (useful, for example, when a person is being transferred from one manager to another) you need to define usages for first and second starting point attributes.

In designing your policy for chains-of-authority starting points, you need to consider what to do in a variety of situations. For example, the selected persons manager may or may not be the requestor. In a transfer, the selected persons current or proposed manager may be NULL.

In our examples we adopt the policy shown in the table 4 below. (The attribute usages to achieve the above policy will be described later.)

In general, this means starting with the selected persons supervisor, or the next higher supervisor if the transaction is initiated by the selected persons supervisor. If there is no selected person for the current transaction, we start with the transaction requestors supervisor.

When transferring the selected person from one manager to another we use dual chains of authority using the selected persons current manager as the first starting point and the proposed manager as the second starting point. If either the current or proposed supervisor is NULL, we revert to a single chain of authority.

Table 4 Starting Points for Chains of Authority

Subjects Current

Supervisor

Subjects Proposed

Supervisor

Starting Point (Supervisory

or Job)

First Starting Point

(Dual Chains)

Second Starting Point (Dual Chains)

Requestor Requestors Supervisor

N/A N/A

Not Requestor Subjects Supervisor

N/A N/A

Requestor Requestors Supervisor

N/A N/A

Not Requestor Proposed Supervisor

N/A N/A

Requestor Not Requestor N/A Requestors Supervisor

Proposed Supervisor

Not Requestor Not Requestor N/A Subjects Supervisor

Proposed Supervisor

Not Requestor Requestor N/A Subjects Supervisor

Requestors Supervisor

Authority Levels Rather than requiring transactions to be approved by some predetermined number of supervisors above the starting point, you may prefer them to be approved by someone having a certain level of authority. This concept is implemented in AME and SSHR using Absolute Job Level approval types in conjunction with an Approval Authority column on the Job table. It is up to each customer to decide what numbers to use to represent different authority levels and then to assign an appropriate number to each job in Oracle Human Resources. In our example scenarios we use the approval authority levels shown in Table 5.

DefIt isapptranthe spec

HeroneTab

Table 5 Example Approval Authority Levels

Job Job Name Approval

Authority

E Senior Vice President, Operations 5

Z Senior Vice President, Sales 5

D Vice President, Operations 4

C Director, Operations 3 ault Approval Policy good practice to start by considering whether some minimum level of roval is required for all transactions. You may have a policy allowing SSHR sactions by default to be accepted without approval. However, if this is not case, you may want to define a default rule (in other words, a rule with no

Y Director, Sales 3

B Manager, Operations 2

X Manager, Sales 2

A Associate 1 Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 19

ific condition) so that at least one approval is required in all cases.

e we have chosen a default policy which requires approval by the supervisor level above the starting point. This can be accomplished using the rule in le 6a.

Example Transaction 2 Personal Information

In example transaction 2, person P31 logs into SSHR, selects the Change Personal Information V4.0 function, and initiates a transaction for person P21. This transaction satisfies none of the conditions of the more specific rules so only the default rule executes and the initial list of approvers contains a single approver as shown in Table 6b.

Salary Change Policy The example policy for salary changes involves a pre-approver in addition to chain-of-authority approvers. This example illustrates how the results from overlapping rules are combined to produce a single ordered list.

Here, we introduce a Transaction Category attribute which you can use to identify transactions that may involve a change in salary.

Table 6a Rules for Default Approval Policy

Table 6b Approvals for Example Transaction 2


1 P41 Supervisor of the selected persons

supervisor (since the selected persons

supervisor is the transaction requestor)

No

Conditions Rule Type Approval Approval Type

List

Creation

Require approvals up to

at most one level

Chains of authority

based on supervisory

level Since the policy specifies different approvals for increases above and below a threshold of 30%, we need an attribute representing Salary Increase Pct. The policy requires pre-approvals by the HR Rep for all changes in salary. Since the HR Rep may be different depending on the selected person, we need to create a dynamic approval group (in other words, it is based on a SQL query which returns the correct person Id at runtime).

This policy can now be implemented with the rules shown in Table 7a. Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 20

Table 7a Rules for Salary Change Approval Policy


Transaction Category

in {SALARY}

AND

Salary Increase Pct

Pre-List

Approval-

Group

Require Pre-Approval

from HR Rep

Group approval

before chain of

authority


If the proposed salary increase had been above 30%, the 4th rule in Table 7a would have fired in place of the 3rd rule, requiring approvals up to level 5. The list of approvers would have added an entry for P51 after the other two approvers in Table 7b.

Transfer Approval Policy The example policy for Transfers demonstrates the use of dual chains of authority and dynamic approval groups.

The key participants in a transfer (also known as a Change Manager or Change Supervisor) transaction are the selected person and their current and proposed managers. In addition, there may be other current (or proposed) managers for the selected persons proposed (or current) direct reports.

For a simple transfer, in which the selected person is assigned to a different manager, the example policy requires approvals up to VP level on both sides of the transfer, in other words, starting with the current manager and the proposed manager in turn. This can be accomplished using the dual chains of authority approval type.

The policy can now be implemented with the first three rules in Table 8a.

Note examthe evthird to geneffect

Table 8a Rules for Transfer Approval Policy



in {TRANSFER}

AND

Current Supervisor >0

AND

Transfer Proposed

Supervisor >0

List

Creation


at most level 4 in the first

chain

Chains of authority

includes two chains,

each based on job

level


in {TRANSFER}

AND

Current Supervisor >0

AND

Transfer Proposed

Supervisor >0

List

Creation

Require approvals at

most 3 levels up the

second chain

Chains of authority

includes two chains,

each based on job

level


in {TRANSFER}

List

Creation


at most level 4

Chain of authority

based on absolute job

level


in

Pre-List Require Pre-Approval Group approval that dual chains require two matching rules; one for each chain. For our ple policy we have added conditions to these rules to prevent them firing in ent that we cannot identify both starting points, in which case, only the

rule fires and we have a single chain of authority. The third rule is intended erate the same approvers as the first chain rule so that when both fire, the is the same as if either rule had fired.

{TRANSFER} Approval-

Group

from

TRANSER_PRE_APPRO

VERS

before chain of

authority Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 23

Example Transaction 4 Simple Transfer

In example transaction 4, person P31 logs into SSHR, selects the Change Manager V4.0 function, and initiates a transaction for person P21 specifying P35 as the new manager. This transaction satisfies the conditions of the default rule and all of the transfer policy rules. These rules all fire and the initial list of approvers will contain the approvers shown in Table 8b.

For a transfer involving the assignment of direct reports to, or away from, the selected person, the example policy requires the various other managers involved to pre-approve the transaction. This can be accomplished by defining a dynamic approval group based on a SQL query which identifies the supervisors in question. For the example, well call this group TRANSER_PRE_APPROVERS. The related query should be constructed to

Table 8b Approvals for Example Transaction 4



supervisor (Starting point for first chain,

AND general transfer rule AND default

rule)

Final approver in this chain, since this

person has level 4 approval authority

No

2 P35 Selected persons proposed supervisor

(Starting point for second chain)


person has level 3 approval authority,

next higher approver is level 5 and the

rule requires at most level 4.

No exclude any supervisor who is also included in one of the dual chains (for example, the current or proposed supervisor of the selected person) to avoid including the person both as a pre-approver and as a chain of authority approver.

The full transfer policy can now be implemented with the addition of the fourth rule in Table 8a.

Example Transaction 5 Transfer Direct Reports

In example transaction 5, person P31 logs into SSHR, selects the Change Manager V4.0 function, initiates a transaction for person P21 and reassigns Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 24

direct reports P11, P12 and P13 to managers P25, P26 and P27 respectively. This transaction satisfies the conditions of the default rule and all of the transfer policy rules. These rules all fire and the initial list of approvers contains the approvers shown in Table 8c.

Other Capabilities The example transactions demonstrated many of the capabilities of AME. Refer to Implementing Oracle Approvals Management for details on these and other

Table 8c Approvals for Example Transaction 5


1 P25 Proposed manager of one of the direct

reports (included in

TRANSFER_PRE_APPROVERS

dynamic approvals group)

No

2 P26 (same as previous) No

3 P27 (same as previous) No


supervisor (Starting point for first chain ,

AND general transfer rule AND default

rule)


person has level 4 approval authority

No

5 P35 Selected persons proposed supervisor

(Starting point for second chain)


person has level 3 approval authority,

next higher approver is level 5 and the

rule requires at most level 4.

No features including:

Chains of authority using relative job levels

Substitution rules

List modification rules

List Creation Exception rules

Priorities

CONFIGURING ATTRIBUTES Having identified the rules and conditions that the business users require to enforce their approvals policies, you need to ensure that the necessary attributes are in place. Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 25

Identifying What Attributes Are Needed When considering which attributes are needed to support your approval rules it can be helpful to break them down into groups according to their purpose.

Policy - attributes for setting overall policy,

Chain of Authority attributes used by certain approval types, for example for identifying the starting points of various approval hierarchies.

Conditional - attributes that form the basis for your rule conditions. These can be further broken down into:

o General useful for any kind of HR transaction o Specific for a particular category of transaction

Policy Attributes

AME uses several attributes to control overall policy in certain areas. Typically these will have static Boolean usages. You need to determine whether to set them to true or false depending on your enterprises policies.

Table 9 Example Policy Attributes

Attribute Name Type Description

ALLOW_DELETING_RULE_GENERATED_APPROVERS

boolean Determines whether AME allows an application to delete approvers required by the appropriate transaction types rules from a transactions approver list (at runtime).

ALLOW_EMPTY_APPROVAL_GROUPS

boolean Determines whether AME lets a requestor approve their own transaction, if they have sufficient Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 26

Chain of Authority Attributes

Chains of authority approval types use attributes to define the starting point of each chain. For the approval types you plan to use, you need to ensure that any corresponding attributes are appropriately defined. Refer to the section on

signing authority. ALLOW_REQUESTOR_APPROVAL boolean Set to False to deny users the

authority to approve a transaction they initiate EVEN IF they are otherwise qualified.

AT_LEAST_ONE_RULE_MUST_APPLY

boolean Whether to require that at least one rule apply to each transaction

INCLUDE_ALL_JOB_LEVEL_APPROVERS

boolean If False, will skip over successive people in the approval chain until reaching the first person with the next higher job level.

USE_RESTRICTIVE_LINE_ITEM_EVALUATION

boolean If True, a rule containing line-item conditions applies to a transaction only if one of the transactions line items satisifies all of the rules line-item conditions


Starting Points for Chains of Authority for an overview of the concepts involved.

Table 10 includes the AME attributes for starting points, another AME attribute which provides an independent check for a broken supervisor hierarchy, and several suggested attributes which may be used as stepping stones to defining usages for the others. For example, you might set up and test a usage for TRANSACTION_SUBJECT_PERSON_ID before attempting to define the usage for SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID.

Table 10 Chain of Authority Attributes


TRANSACTION_REQUESTOR_PERSON_ID

number (Required by Supervisory and any Job-Level-based approval types) Person ID of user initiating the transaction.

TRANSACTION_REQUESTOR_SUPERVISOR_ID

number Person ID of the supervisor of the user initiating the transaction.

CURRENT_ASSIGNMENT_ID number Assignment ID of user initiating the transaction.

TRANSACTION_SUBJECT_PERSON_ID

number Person ID of person selected in the transaction.

TRANSACTION_SUBJECT_PERSON_SUPERVISOR_ID

number Person ID of current supervisor of the person selected in the transaction.

TRANSACTION_SUBJECT_ASSIGNMENT_ID

number Assignment ID of person selected in the transaction.

JOB_LEVEL_NON_DEFAULT_STARTING_POINT_PERSON_ID

Number (Used by Job-Level-based approval types) If NULL, supervisory approval chain starts with requestors supervisor (supervisor of the TRANSACTION_REQUESTOR_PERSON_ID).

SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID

Number (Used by Supervisory approval type) If NULL, supervisory approval chain starts with requestors supervisor (supervisor of the TRANSACTION_REQUESTOR_PERSON_ID).

FIRST_STARTING_POINT_PERSON_ID

Number (Required by Dual Chains of Authority approval type) Person with whom to start the FIRST chain of authority. Note: chains follow primary assignments.

SECOND_STARTING_POINT_PERSON_ID

Number (Required by Dual Chains of Authority approval type) Person with whom to start the SECOND chain of authority. Note: chains follow primary assignments.

TOP_SUPERVISOR_PERSON_ID Number (Required by Supervisory approval type) Identifies the person at the top of the supervisor chain. Guards against accidental gaps in the supervisor hierarchy.

Conditional Attributes

Examine your approval polices and wherever you have specified a condition you will need to make sure that you have set up the appropriate attribute on which to base it.

You may want to start by identifying attributes that are generally applicable for all transactions regardless of functional area. For example, if your approval policies differ across the enterprise, you may have conditions based on

organizational entities such as organization, business group, and legislation code.

Table 11 Example Conditional Attributes - General


TRANSACTION_ORG_ID number Organization ID of user initiating the transaction.

TRANSACTION_ORG_NAME number Name of organization of user initiating the transaction.

TRANSACTION_GROUP_ID number Business group ID of user initiating the transaction.

TRANSACTION_GROUP_NAME number Name of business group of user initiating the transaction.

TRANSACTION_SUBJECT_ORG_ID number Organization ID of person selected in the transaction. Table 11 lists some examples of general conditional attributes.

Note: Attributes based on entity names are generally easier for business users than those based on ID, but there may be performance implications.

After identifying all general attributes, consider the attributes specific to each category of transaction. To support the example policies in Table 3 we need an attribute to identify transaction category and one for percentage increase in

TRANSACTION_SUBJECT_ORG_NAME

number Name of organization of person selected in the transaction.

TRANSACTION_SUBJECT_GROUP_ID

number Business group ID of person selected in the transaction.

TRANSACTION_SUBJECT_GROUP_NAME

number Name of business group of person selected in the transaction. Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 28

salary.

Table 12 Example Conditional Attributes Category Specific


HR_TRANSACTION_CATEGORY string Category of transaction (e.g. Salary, or Transfer)

SALARY_INCREASE_PCT number **Line item attribute ** Salary increase expressed as a percentage (30 indicates that proposed salary is 30% higher than current salary.)


As you examine policies for other functional areas you will uncover the need for more attributes.

Attribute Usages We are now ready to define the usages for our attributes.

The task of defining attribute usages must be performed initially by someone (or a combination of people) with system administration and PL/SQL skills and access privileges. Once the attribute usages have been defined and tested, the conditions and rules may be successfully completed by a business user.

In this section, we provide a few examples of usages to support the example attributes identified earlier. (A more complete listing appears in Appendix A.)

Keep in mind the following general advice:

It is best to encapsulate complex logic in a PL/SQL package which can be referenced from AME attribute usages. Many lines of SQL can be reduced to a simple usage of the form: select .(:transactionId) from dual

Note: In the examples below, we use the package name custom_package, but you would substitute the name of your own package.

Conversely, avoid burying in a PL/SQL package any details that are more conveniently held at the configuration level. For example, a Boolean attribute based on a function that detects a specific condition is less flexible than a number attribute based on a function that returns a value on which a variety of conditions can be based.

Ensure that attribute usages return NULL (or a specific value such as zero) rather than no rows returned when the attribute is not found on the current transaction.

Header-level dynamic usages will typically involve the where clause: where hr_api_transactions.transaction_id = :transactionId

Line-level dynamic usages must include the ordering by line item id as defined in the line-item item class

Note: Refer to Implementing Oracle Approvals Management for details and examples of the syntax rules you must follow.

Transaction Categories To determine the category of transaction, the HR_TRANSACTION_CATEGORY attribute uses the function

Table 13 Example Attribute Usages

Attribute Name Type Query

ALLOW_EMPTY_APPROVAL_GROUPS boolean true FIRST_STARTING_POINT_PERSON_ID

number select decode(custom_package.get_subject_sup_id(:transactionId), custom_package.get_requestor_person_id(:transactionId), custom_package.get_requestor_sup_id(:transactionId), custom_package.get_subject_sup_id(:transactionId) ) from dual

HR_TRANSACTION_CATEGORY string select custom_package.get_transaction_category(hats.transaction_step_id) from hr_api_transaction_steps hats where hats.transaction_id = :transactionId order by hats.transaction_step_id

SALARY_INCREASE_PCT number select custom_package.get_salary_increase_pct(:transactionId) from dual

WORKFLOW_PROCESS_NAME string SELECT HR_WORKFLOW_SS.GET_PROCESS_NAME(:transactionId) FROM DUAL get_transaction_category. This function, for which a code fragment is provided in Appendix B, makes the assumption that the api_name column in the HR_API_TRANSACTION_STEPS table is a good indicator of the category of transaction. For example, a Salary change transaction will use HR_PAY_RATE_SS.PROCESS_API while a transfer will use HR_SUPERVISOR_SS.PROCESS_API.

Header Level or Line Level This attribute is defined at the line level, for obvious reasons. Note, however, that since there can only be a single salary step in a given transaction it is permissible to define the SALARY_INCREASE_PCT attribute at the header level. The logic for the underlying function can be written to retrieve only rows for a step with transaction category of SALARY. The same approach can be taken for any other transaction category-specific attributes when it is known that there can only be one such step in the transaction. Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 30


Transaction Types The examples selected in this paper are all from within the Manage Employee Events area of SSHR in which any of these example transaction categories may be combined in a single transaction. Consequently these transaction categories, and their supporting attribute usages must be defined in a single transaction type. However, you may choose to create separate transaction types for other functional areas such as Training.

Migrating Configurations Between Instances An enhancement is under development to allow you to use the FNDLOAD utility to download all or parts of your AME configuration to a text file which you will then be able to upload to another Oracle Applications instance.

For further information, refer to enhancement request number 2603610.

SUMMARY OF CONFIGURATION METHODS AND SKILL LEVELS The following table indicates the methods and skill levels required to perform the various tasks involved in setting up and maintaining approvals policies.

CONCIn this ManagSelf-Sehas beethe apppresentmeans Implem

Table 14 Skill Levels Required to Perform Configuration Tasks

Task Method SSHR

User

Business

User

System

Admini-

strator

Pro-

gramme

r

Insert additional

name(s) into a

default approval list.

SSHR Review page

(Dynamic

Approvals)

Y

Configure approval

rules and conditions

AME configuration Y

Define transaction

types and attribute

usages

AME configuration

and PL/SQL

Y Y

Turn

or of

SSH

Mak

Appr

(or n

func

Add

type

LUSION paper you have seen how to take advantage of Oracle Approvals ement to put in place a powerful set of controls to govern transactions in rvice Human Resources. You have also seen how, once the initial setup n performed, it is feasible for a non-technical business user to fine-tune roval rules to reflect changing business requirements. The examples ed here are representative of typical business practices, but are by no

Approvals on

f for a specific

R function

Workflow Builder

Function

configuration

Y Y

e Dynamic

ovals available

ot) in a specific

tion.

Workflow Builder

Function

configuration

Y Y

a new approval

and handler

AME configuration

and PL/SQL

Y Y Configuring Oracle Approvals Management for Oracle Self-Service Human Resources V4 Page 32

exhaustive. To learn more about Oracle Approvals Management, refer to enting Oracle Approvals Management.


APPENDIX A ATTRIBUTE USAGES The following table represents an alphabetical listing of attributes along with their usages. (The list includes all the attributes used in the examples in this paper as well as some from the Training functional area.)

Attribute Name Type Attribute Usage

ALLOW_DELETING_RULE_GENERATED_APPROVERS

boolean false

ALLOW_EMPTY_APPROVAL_GROUPS

boolean true

ALLOW_REQUESTOR_APPROVAL boolean false AT_LEAST_ONE_RULE_MUST_APPLY

boolean true

EFFECTIVE_RULE_DATE date FIRST_STARTING_POINT_PERSON_ID

number select decode(custom_packagecustom_package.get_subject_sup_id(:transactionId), custom_packagecustom_package.get_requestor_person_id(:transactionId), custom_package.get_requestor_sup_id(:transactionId), custom_package.get_subject_sup_id(:transactionId) ) from dual

HR_TRANSACTION_CATEGORY string select custom_package.get_transaction_category(hats.transaction_step_id) from hr_api_transaction_steps hats where hats.transaction_id = :transactionId order by hats.transaction_step_id

OTA_ACTIVITY_TYPE string select ot_workflow_ss.get_activity_type(:transactionId) from dual

OTA_ENROLLMENT_STATUS string select ot_workflow_ss.get_enrollment_status(:transactionId) from dual

OTA_EVENT_STANDARD_PRICE number Select ot_workflow_ss.get_event_standard_price(:transactionId) from dual

OTA_EVENT_STANDARD_PRICE string select ot_workflow_ss.get_act_pm_category(:transactionId) from dual

OTA_PRIMARY_DELIVERY_METHOD

string select ot_workflow_ss.get_act_pm_delivery_method(:transactionId) from dual

INCLUDE_ALL_JOB_LEVEL_APPROVERS

boolean false



JOB_LEVEL_NON_DEFAULT_STARTING_POINT_PERSON_ID

number select decode( nvl(custom_package.get_subject_sup_id(:transactionId), custom_package.get_transfer_proposed_sup_id(:transactionId)), NULL, custom_package.get_requestor_sup_id(:transactionId), custom_package.get_requestor_person_id(:transactionId), custom_package.get_requestor_sup_id(:transactionId), nvl(custom_package.get_subject_sup_id(:transactionId), custom_package.get_transfer_proposed_sup_id(:transactionId)) ) from dual

SALARY_INCREASE_PCT number select custom_package.get_salary_increase_pct(:transactionId) from dual

SECOND_STARTING_POINT_PERSON_ID

number select decode(custom_package.get_transfer_proposed_sup_id(:transactionId), custom_package.get_requestor_person_id(:transactionId), custom_package.get_requestor_sup_id(:transactionId), custom_package.get_transfer_proposed_sup_id(:transactionId) ) from dual

SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID

number select decode( nvl(custom_package.get_subject_sup_id(:transactionId), custom_package.get_transfer_proposed_sup_id(:transactionId)), NULL, custom_package.get_requestor_sup_id(:transactionId), custom_package.get_requestor_person_id(:transactionId), custom_package.get_requestor_sup_id(:transactionId), nvl(custom_package.get_subject_sup_id(:transactionId), custom_package.get_transfer_proposed_sup_id(:transactionId)) ) from dual

TOP_SUPERVISOR_PERSON_ID number TRANSACTION_DATE date



TRANSACTION_GROUP_ID number select ppf.business_group_id from hr_api_transactions hat, per_people_f ppf where hat.transaction_id = :transactionId and ppf.person_id = hat.creator_person_id and custom_package.get_effective_date(:transactionId) between ppf.effective_start_date and ppf.effective_end_date

TRANSACTION_ORG_ID number select paf.organization_id from hr_api_transactions hat, per_assignments_f paf where hat.transaction_id = :transactionId and paf.person_id = hat.creator_person_id and paf.primary_flag = 'Y' and custom_package.get_effective_date(:transactionId) between paf.effective_start_date and paf.effective_end_date

TRANSACTION_PROPOSED_SUPERVISOR_ID

number select custom_package.get_transfer_proposed_sup_id(:transactionId) from dual

TRANSACTION_REQUESTOR_PERSON_ID

number select custom_package.get_requestor_person_id(:transactionId) from dual

TRANSACTION_REQUESTOR_SUPERVISOR_ID

number select custom_package.get_requestor_sup_id(:transactionId) from dual

TRANSACTION_REQUESTOR_USER_ID

number

TRANSACTION_SET_OF_BOOKS_ID

number

TRANSACTION_SUBJECT_PERSON_ID

number select custom_package.get_subject_person_id(:transactionId) from dual

TRANSACTION_SUBJECT_PERSON_SUPERVISOR_ID

number select custom_package.get_subject_sup_id(:transactionId) from dual

USE_RESTRICTIVE_LINE_ITEM_EVALUATION

boolean true

WORKFLOW_ITEM_KEY string SELECT ITEM_KEY FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID=:transactionId

WORKFLOW_ITEM_TYPE string SELECT ITEM_TYPE FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID=:transactionId

WORKFLOW_PROCESS_NAME string SELECT HR_WORKFLOW_SS.GET_PROCESS_NAME(:transactionId) FROM DUAL


APPENDIX B FUNCTIONS The following table lists several example functions with descriptions of the underlying logic. (The list includes all the functions used in the example attributes in this paper as well as some from the Training functional area.) Sample queries have been provided in representative cases. Appendix E is an example package header and detail that supports these functions.

Function Name Parameters Description

get_assignment_id transaction_id Get the Assignment ID of the selected person

get_current_hour transaction_id Get the current hour for the assignment

get_current_salary transaction_id Get the current salary for the assignment

get_effective_date transaction_id Get the effective date of this transaction

get_number_increase_pct number1, number2

Calculate the percentage increase number 2 over number 1

get_person_id transaction_id Get the Person ID of the selected person select number_value from wf_item_attribute_values wiav, hr_api_transactions hat where hat.item_type = wiav.item_type and hat.item_key = wiav.item_key and hat.transaction_id = p_transaction_id and name = 'CURRENT_PERSON_ID';

get_proposed_hour transaction_id Get the proposed hour for the assignment

get_proposed_salary transaction_id Get the proposed salary for the assignment select get_trans_step_number_value( hats.transaction_step_id, 'P_PROPOSED_SALARY') from hr_api_transaction_steps hats where hats.transaction_id = p_transaction_id and get_transaction_category(hats.transaction_step_id) = 'SALARY';

get_requestor_person_id transaction_id Get the id of the person creating/requesting/initiating the transaction select creator_person_id from HR_API_TRANSACTIONS where transaction_id=p_transaction_id;

get_requestor_sup_id transaction_id Get the id of the supervisor of the person creating/requesting/initiating the transaction

get_salary_increase_pct transaction_id Calculate increase in salary as a percentage

get_subject_person_id transaction_id Get the id of the person selected in the transaction

get_subject_sup_id transaction_id Get the id of the supervisor of the person selected in the transaction



get_trans_step_Boolean_value Transaction_step_id, attribute_name

Return the value of a boolean attribute

get_trans_step_date_value Transaction_step_id, attribute_name

Return the value of a date attribute

get_trans_step_number_value Transaction_step_id, attribute_name

Return the value of a number attribute

get_trans_step_value Transaction_step_id, attribute_name

Return the value of any attribute converted to varchar2. select decode(hatv.datatype, 'NUMBER',to_char(hatv.number_value), 'VARCHAR2',hatv.varchar2_value, 'BOOLEAN',hatv.varchar2_value, 'DATE',ame_util.versionDatetoString(hatv.date_value), NULL) into l_attribute_value from hr_api_transaction_values hatv , hr_api_transaction_steps hats where hatv.transaction_step_id = hats.transaction_step_id and hats.transaction_step_id = p_transaction_step_Id and hatv.name = p_attribute_name;

get_trans_step_varchar2_value

Transaction_step_id, attribute_name

Return the value of a varchar2 attribute

get_transaction_category Transaction_step_id

Derive the category of transaction based on the api_name used. select decode(hats.api_name, 'HR_PAY_RATE_SS.PROCESS_API','SALARY', 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API','ASSIGNMENT', 'HR_SUPERVISOR_SS.PROCESS_API','TRANSFER', , OTHER) from hr_api_transaction_steps hats where hats.transaction_step_id = p_transaction_step_id;

get_transfer_current_sup_id transaction_id Get the id of the current supervisor of the person selected for transfer

get_transfer_proposed_sup_id transaction_id Get the id of the proposed supervisor of the person selected for transfer



get_activity_type transaction_id Get the name of the activity definition for the current event. select oad.name from ota_events evt, ota_activity_versions oav, ota_activity_definitions oad where evt.event_id = c_event_id and evt.activity_version_id = oav.activity_version_id and oav.activity_id = oad.activity_id; c_event_id := wf_engine.GetItemAttrNumber( itemtype => c_item_type , itemkey => c_item_key, aname => 'OTA_EVENT_ID');

get_enrollment_status transaction_id Get the name of the current booking status type for the current booking.

get_event_standard_price transaction_id Get the standard_price of the current event.

get_act_pm_category transaction_id Get the activity category for the current event.

get_act_pm_delivery_method transaction_id Get the delivery method of the current event.


APPENDIX C DYNAMIC APPROVAL GROUPS The TRANSFER_PRE_APPROVERS group used in the Transfer policy examples uses the following dynamic query: select distinct 'person_id:'||to_char(supervisor_id) from ( select hatv.number_value supervisor_id from hr_api_transaction_values hatv, hr_api_transaction_steps hats where hatv.transaction_step_id = hats.transaction_step_id and hats.transaction_id = :transactionId and apps.xyz_sshr_functions.get_transaction_category( hats.transaction_step_id) = 'TRANSFER' and hatv.name like 'P_SUPERVISOR_ID%' and hatv.number_value >0 UNION select paaf.supervisor_id from hr_api_transaction_values hatv, hr_api_transaction_steps hats, per_all_assignments_f paaf where hatv.transaction_step_id = hats.transaction_step_id and hats.transaction_id = :transactionId and apps.xyz_sshr_functions.get_transaction_category( hats.transaction_step_id) = 'TRANSFER' and (hatv.name like 'P_EMP_ASG_ID%') and paaf.assignment_id = hatv.number_value and sysdate between paaf.effective_start_date and paaf.effective_end_date and paaf.primary_flag = 'Y' ) supervisors where supervisor_id not in ( select hatv.number_value from hr_api_transaction_values hatv, hr_api_transaction_steps hats where hatv.transaction_step_id = hats.transaction_step_id and hats.transaction_id = :transactionId and apps.xyz_sshr_functions.get_transaction_category( hats.transaction_step_id) = 'TRANSFER' and (hatv.name = 'P_SELECTED_PERSON_OLD_SUP_ID' or hatv.name = 'P_SELECTED_PERSON_SUP_ID') ) and supervisor_id is not NULL order by 1


APPENDIX D POSITION HIERARCHY SUPPORT R11i10 of AME delivers Position Hierarchy Support. However, until such time as it is supported within SSHR, here are a couple of examples of how to provide such support.

The first example shows how a function can be created which returns the approver(s) at a specific level.

First create a function which will return the Position id for a given transaction id. This function will then be used in conjunction with Dynamic Approval groups to return Approvers who exist in the position hierarchy.

Here is the function. The parameters to the function are the transaction id and the level to which you want the Position(s) returned.

create or replace function getApproverPositionId(transactionIdIn in integer, levelIn in integer) return integer is requestorId integer; positionLevel integer; positionId integer; parentPositionId integer; begin

select creator_person_id into requestorID from HR_API_TRANSACTIONS where transaction_id = transactionIdIn;

select position_id into positionId from per_all_assignments_f where person_id = requestorId and primary_flag = 'Y' and trunc(sysdate) between effective_start_date and nvl(effective_end_date,trunc(sysdate)) and rownum < 2;

positionLevel := 0;

while(positionLevel levelIn) loop

select str.parent_position_id into parentPositionId from per_pos_structure_elements str, per_pos_structure_versions psv, per_position_structures pst where str.subordinate_position_id = positionId and str.pos_structure_version_id = psv.pos_structure_version_id and pst.position_structure_id = psv.position_structure_id and pst.primary_position_flag = 'Y' and trunc(sysdate) between psv.date_from and nvl( psv.date_to , sysdate); positionId := parentPositionId; positionLevel := positionLevel+1; end loop; return positionId; exception when others then ame_util.runtimeException(packageNameIn => 'getApproverPositionId', routineNameIn => 'getApproverPositionId', exceptionNumberIn => sqlcode,


exceptionStringIn => sqlerrm); return null; end;

You then need to create a dynamic approval group that will call the function and will return the approvers at the level selected in the call to the function.

The approval group shown below will return the person(s) who holds the position, one level above the transaction requester.

Required SQL in Dynamic Approval Group

select distinct 'person_id:'||wf.orig_system_id from wf_roles wf,

per_all_assignments_f paf

where paf.position_id = getApproverPositionId(:transactionId,3) and paf.person_id = wf.orig_system_id

and paf.primary_flag = 'Y'

and paf.assignment_type in ('E','C')

and paf.assignment_status_type_id not in

(select assignment_status_type_id

from per_assignment_status_types

where per_system_status = 'TERM_ASSIGN')

and trunc(sysdate) between paf.effective_start_date

and nvl(paf.effective_end_date,trunc(sysdate))

and wf.status = 'ACTIVE'

and (wf.expiration_date is null or sysdate < wf.expiration_date)

and wf.orig_system = 'PER'

Replace the position number in the call (currently set to 3 in the above example) to the function with the desired position. If you want to climb n levels it is possible to create a dynamic approval group for each level and then create a static approval group and include as the members, a dynamic approval group for every level you want to include. It is also worthy of note that we only want to include active assignments and not job applicants.

This approval group can then be used within an Approval Rule as either Pre/Post Approval Group or Chain of Authority Includes an Approval Group Action type.

However, the second example below is a cleaner version of the same although it does require a substantial piece of SQL for each level you require to ascend.


The second example is where it is required to return approvers up to n level. Create a dynamic approval group with a meaningful name that reflects the number of levels you want to climb up the position hierarchy from the transaction requester. The SQL for the Dynamic Approval group should be as follows:-

select 'person_id:'||wf.orig_system_id from wf_roles wf, per_all_assignments_f paf where paf.position_id in ( select parent_position_id from ( select parent_position_id,pos_structure_version_id from ( select pos_structure_version_id,subordinate_position_id,parent_position_id,to_char(level) pos_level from per_pos_structure_elements start with subordinate_position_id = ( select position_id from per_all_assignments_f x where person_id = ( select creator_person_id from HR_API_TRANSACTIONS where transaction_id = :transactionId ) and trunc(sysdate) between x.effective_start_date and nvl(x.effective_end_date,trunc(sysdate)) ) connect by prior parent_position_id = subordinate_position_id and pos_structure_version_id = replacewithStructureVersionID )

where to_number(pos_level)


and trunc(sysdate) between x.effective_start_date and nvl(x.effective_end_date,trunc(sysdate)) ) and str.pos_structure_version_id = psv.pos_structure_version_id and pst.position_structure_id = psv.position_structure_id and pst.primary_position_flag = 'Y' and trunc(sysdate) between psv.date_from and nvl( psv.date_to , sysdate) ) ) and paf.person_id = wf.orig_system_id and paf.primary_flag = 'Y' and paf.assignment_type in ('E','C') and paf.assignment_status_type_id not in (select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN') and trunc(sysdate) between paf.effective_start_date and nvl(paf.effective_end_date,trunc(sysdate)) and wf.status = 'ACTIVE' and (wf.expiration_date is null or sysdate < wf.expiration_date) and wf.orig_system = 'PER'

The text replacewithActualPositionLevel in the above SQL should be replaced with the integer specifying number of levels to be climbed from the transaction requester.

The id replacewithStructureVersionID in the above SQL should be replaced with the integer specifying the structure version id. This can be derived with the by querying the table PER_POS_STRUCTURE_VERSIONS and PER_POS_STRUCTURES and writing a simple query as follows: - select pos_structure_version_id, Version_Number from per_pos_structure_versions where position_structure_Id = (select position_structure_Id from per_position_structures where business_group_id = > and Name = PositionHierarchy Name and primary_position_flag = 'Y') Note : This query is just a guideline. Customers may want to use a Non Primary Position Hierarchy in which case the above query needs to be changed appropriately.


APPENDIX E EXAMPLE SUPPORT FUNCTIONS Reproduced below is a sample package header and body which supports the examples in this document. The code herein is not supported and is supplied without warranty.

First the package header and then the package body.

REM +======================================================================+ REM | Copyright (c) 1997 Oracle Corporation | REM | Redwood Shores, California, USA | REM | All rights reserved. | REM +======================================================================+ REM Application : Human Resources Self Service Applications REM File Name : xyz_sshr_functions.pkb.pkh REM Package Name : xyz_sshr_functions REM Purpose : To be called from AME to determine attribute values REM Owner : APPS REM REM ========================================================================

Set Scan Off Set Verify Off WHENEVER OSERROR EXIT FAILURE ROLLBACK; WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

CREATE OR REPLACE package xyz_sshr_functions AUTHID CURRENT_USER as

------------------------------------------------------------------

-- General Functions ------------------------------------------------------------------

-- Name: Get_Number_Increase_Pct -- Desc: Calculate the percentage increase number 2 over number 1 -- Params: number value1 -- number value2 -- Returns: number ------------------------------------------------------------------

function get_number_increase_pct ( number_value1 number, number_value2 number) return number;

------------------------------------------------------------------

-- Transaction level functions ------------------------------------------------------------------

-- Name: Get_Effective_Date -- Desc: Get the effective date of this transaction -- Params: transaction_id -- Returns: date ------------------------------------------------------------------

function get_effective_date (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return date;

------------------------------------------------------------------

-- Name: Get_Person_ID -- Desc: Get the Person ID of the selected person -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_person_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: Get_Assignment_ID -- Desc: Get the Assignment ID of the selected person


-- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_assignment_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: Get_Current_Salary -- Desc: Get the current salary for the assignment -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_current_salary (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: Get_Current_Hour -- Desc: Get the current hour for the assignment -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_current_hour (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number ;

------------------------------------------------------------------

-- Name: get_requestor_person_id -- Desc: Get the id of the person -- creating/requesting/initiating the transaction -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_requestor_person_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: get_requestor_sup_id -- Desc: Get the id of the supervisor of the person -- creating/requesting/initiating the transaction -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_requestor_sup_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: get_subject_person_id -- Desc: Get the id of the person -- selected in the transaction -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_subject_person_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: get_subject_sup_id -- Desc: Get the id of the supervisor of the person -- selected in the transaction -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_subject_sup_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: get_transfer_current_sup_id


-- Desc: Get the id of the current supervisor of the person -- selected for transfer -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_transfer_current_sup_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: get_transfer_proposed_sup_id -- Desc: Get the id of the proposed supervisor of the person -- selected for transfer -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_transfer_proposed_sup_id (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: Get_Proposed_Salary -- Desc: Get the proposed salary for the assignment -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_proposed_salary (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: Get_Proposed_Hour -- Desc: Get the proposed hour for the assignment -- Params: transaction_id -- Returns: number ------------------------------------------------------------------

function get_proposed_hour (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Name: Get_Salary_Increase_Pct -- Desc: Calculate increase in salary as a percentage -- Params: transaction_step_id -- Returns: number ------------------------------------------------------------------

function get_salary_increase_pct (p_transaction_id IN hr_api_transactions.transaction_id%TYPE) return number;

------------------------------------------------------------------

-- Transaction Step level functions ------------------------------------------------------------------

-- Name: get_trans_step_value -- Desc: Return the value of an attribute converted to varchar2 -- Params: transaction_step_id -- attribute_name -- Returns: varchar2 ------------------------------------------------------------------

function get_trans_step_value ( p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE, p_attribute_name IN hr_api_transaction_values.name%TYPE) return varchar2;

------------------------------------------------------------------

-- Name: get_trans_step_number_value -- Desc: Return the value of a number attribute -- Params: transaction_step_id -- attribute_name -- Returns

sshr ame approval

Documents