Skip Headers

Oracle Internet Expenses Implementation and Administration Guide
Release 12.1
Part Number E13454-03
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Implementing Client Extensions

This chapter covers the following topics:

Overview of Implementing Client Extensions

Many companies have business requirements beyond the standard functionality of Internet Expenses. To address these requirements, Internet Expenses provides client extensions. Client extensions use PL/SQL procedures to extend Self-Service functionality to implement and automate company-specific business rules. These procedures are called during the standard process flows of Internet Expenses.

Internet Expenses provides the following client extension packages:

Each package has multiple procedures which you can enable individually. This chapter provides detailed information on these packages and the associated procedures.

To implement client extensions, you must analyze your business requirements, design the client extension logic, and then write the appropriate PL/SQL procedures. Each of these steps is described in this section.

Analyzing Your Business Requirements

To determine if you need to use client extensions:

  1. Define and document your company's business requirements and rules.

  2. Determine if these business rules are handled by the standard features of Internet Expenses.

  3. For those business rules not handled by the standard functionality, determine which client extensions can address your specific business needs.

Designing Your Logic

To design your logic:

  1. Understand the client extensions you propose to use, including their purpose, processing flow, when Internet Expenses calls the extensions, and the input values.

  2. Define and document the requirements and logic of your business rules under all possible conditions. Determine the inputs, calculations performed, and resulting outputs.

  3. Determine the data elements required to enforce your rules and how you will select or derive each of the required elements. Define additional implementation data and document additional business procedures based on the requirements of your business rules.

  4. Step through various business scenarios to ensure that your logic handles each condition as you expect. You can use these scenarios as test cases when you test your actual client extension definition and procedure.

  5. Give the detailed specification to the team member who will write the PL/SQL procedure.

If you want to use different logic for different parts of your company, write one procedure that branches appropriately.

Writing PL/SQL Procedures

This section is a brief overview of PL/SQL procedures.

Packages

Packages are database objects that group logically related PL/SQL types, objects, and subprograms. Packages usually consist of two files: a package specification file and a package body file. The Specification file is the interface to your applications. It declares the types, variables, constants, exceptions, cursors, and subprograms available for use in the package. It also contains the package name, procedures, and functions declarations. The package body contains the actual PL/SQL code used to implement the business logic.

Procedures

Procedures are subprograms within a package. Procedures are invoked by the application and perform a specific action. Procedures define what parameters will be passed in as context for the program, how the inputs are processed, and what output is returned.

The client extension procedures you write to implement your business rules extend the functionality of Internet Expenses, and are considered customizations. Oracle supports the call out to the extension, processing the values returned by the extension, and the default logic provided in the extension. You are responsible for the support and upgrade of the logic within the procedures that you write, which may be affected by changes between releases of the Oracle Applications.

Warning: You must not directly insert or update records in any Oracle Applications tables; performing these activities with the client extensions is not supported by Oracle. Use the public, predefined procedures that Internet Expenses provides to insert or update records in Oracle Applications tables.

Validations and Defaults Client Extension

The Validations and Defaults client extension provides multiple procedures to perform validations or calculations, or provide default values for Cost Center field. This section describes the details for each of the procedures available in this client extension package:

Internet Expenses provides a template package and procedure that you use as the basis for extension procedures. The name of the template package is AP_WEB_CUST_DFLEX_PKG.

Print out these files before you begin writing procedures for your descriptive flexfields. The files are located in the Oracle Payables $AP_TOP/patch/115/sql directory:

Default Cost Center Procedure (CustomDefaultCostCenter)

When a user navigates to the Enter General Expense Report Information page, a default value automatically appears in the Cost Center field. This default cost center value is extracted from the DEFAULT_CODE_COMBINATION_ID column of the HR_EMPLOYEES_CURRENT_V view. Use the default cost center procedure to define a different value for the cost center field.

Modify the CustomDefaultCostCenter procedure so it returns a specific cost center value. The value that CustomDefaultCostCenter returns is the default cost center in the Enter General Expense Report Information page. If CustomDefaultCostCenter returns a value of NULL, Internet Expenses uses the standard default cost center method and inserts the employee's cost center as the default.

Cost Center Validation Procedure (CustomValidateCostCenter)

When an employee enters an expense report, Internet Expenses checks whether the cost center identified in the expense report is a valid value in your chart of accounts. If the cost center specified is invalid, the system displays an error message when the employee clicks on the Next button in the Enter General Expense Report Information page. You can use the Cost Center Validation Procedure to define additional custom validation rules for the values users enter in the Cost Center field.

To enable the CustomValidateCostCenter procedure, write your code so the function returns a value of TRUE. If the CustomValidateCostCenter procedure returns the value TRUE, Internet Expenses uses your cost center validation logic and skips the predefined cost center validation routine.

When a cost center is valid, your validation logic must set the p_CostCenterValid parameter to TRUE. When a cost center is not valid, your logic must set the p_CostCenterValid parameter to FALSE. You can also modify the error message that appears when validation fails. To modify this error message, assign a value to the p_cs_error variable. If you do not assign a value to this variable, Internet Expenses displays the standard error message.

Calculate Amount Procedure (CustomCalculateAmount)

Use the Calculate Amount procedure to define company-specific calculations for expense types such as per diem and mileage. If you use special criteria or complex calculations to arrive at the reimbursable amount, then you can make use of the Calculate Amount Procedure. You can update per diem rates, deductions, additions, and reimbursable amounts for per diems. For mileage, you can change passenger amounts, additional rate amounts, and reimbursable amounts.

Use the Calculate Amount procedure in one of the following ways:

Using the Calculate Amount Procedure Independently

To use the Calculate Amount procedure independently, enable the Calculate Amount option in the Payables Expense Report Template window for each expense type that you want to use with the Calculate Amount procedure.

Additionally, define a context-sensitive flexfield segment for each expense type to which you want to associate the Calculate Amount procedure. The values employees enter into these context-sensitive segments provide the input for your calculation logic.

For example, to write logic to calculate mileage expenses, define a context-sensitive flexfield segment called Miles and associate the segment with the Mileage expense type. When an employee selects the Mileage expense type, the Miles flexfield appears and the employee can enter a number in the Miles field. Clicking the Calculate Amount button executes your custom logic, which calculates the number of miles times the rate per mile, and then populates the Receipt Amount field.

Using the Calculate Amount Procedure Together with Schedules

You can also use the Calculate Amount procedure along with the per diem and mileage schedules that you have created. You can handle special calculations by combining the calculations using schedules and the Calculate Amount procedure.

When you use the procedure along with schedules, the reimbursement amount is first calculated based on the schedule. Then the Calculate Amount procedure is called, and the reimbursement amount is modified based on the way you have configured the Calculate Amount procedure.

Note: You can also define descriptive flexfields to capture any other relevant details. The Calculate Amount procedure can use the descriptive flexfield values to recalculate the reimbursable amounts.

For example, federal organizations in Finland reimburse half per diem if the trip is within 6 to 8 hours, provided at least 3 hours of travel is between 16:00 and 07:00. Otherwise, per diem is zero. For such a scenario, you can create a per diem schedule with reimbursement for 6 to 8 hours as zero. Then you can use the Calculate Amount procedure to update the rate if the travel is between 16:00 and 7:00.

Flexfield Validation Procedure (CustomValidateDFlexValues)

When defining descriptive flexfield segments, you associate a value set to each segment. You can define value sets to include validation rules by using the Format Validation region of the Value Sets window. Use the Flexfield Validation Procedure to define additional validation rules for the values entered in Internet Expenses flexfield segments. If you implement the Flexfield Validation procedure, Internet Expenses executes your custom validation logic as well as the validation you define for value sets.

You can define dynamic validation that is dependent on other fields in the expense report. The same API is called for both expense line level validation and for individual expense-type context-sensitive custom field validation.

Line Validation Procedure (CustomValidateLine)

This procedure enables you to define custom validation at the line level. For example, you can prevent users from entering an expense line over a threshold amount, or require users to enter a value in a particular field under certain conditions. To help with duplicate expenses detection, you can write code to detect when two expenses are entered on the same or on different expense reports where the dates and amounts are the same.

You can use the Line Validation procedure in conjunction with descriptive flexfields that you define at the header level to validate the data users enter. For example, if you define header-level descriptive flexfields to capture the date range of an expense report, you can use this procedure to ensure that expense dates the user enters on each expense line are within the start and end dates of the expense report. See: Setting Up Descriptive Flexfields for more information.

You can check the number of employee and non-employee attendance.

You can enforce the entry of hotel name for specific night rate types using the Line Validate procedure.

If you want to perform custom validations on descriptive flexfields, use the Flexfield Validation Procedure (CustomValidateDFlexValues).

Workflow Client Extension

The Workflow client extension provides multiple procedures to extend and interact with the predefined expense report workflow process. This section describes the details for each of the procedures available in this client extension package:

Internet Expenses provides a template package and procedure that you use as the basis for your workflow procedures. The name of the template package is AP_WEB_EXPENSE_CUST_WF.

Print out these files before you begin writing procedures for your workflow processes. The files are located in the Oracle Payables $AP_TOP/patch/115/sql directory:

Management Involvement Procedure (DetermineMgrInvolvement)

Expense reports can be paid only if the Manager (Spending) Approval workflow process marks them as manager approved. By default, expense reports require manager review and approval. However, you can modify this procedure to allow the Level of Manager Involvement activity in the Manager (Spending) Approval workflow process to approve expense reports automatically, without a manager's approval.

The Level of Manager Involvement activity supports three levels of manager involvement:

Use the Manager Involvement Extension to specify how the Level of Manager Involvement processes expense reports. For example, you can define the manager involvement procedure so the level of manager involvement depends on the amount of the expense report or the expense template used.

For a detailed description of the default behavior of the Level of Manager Involvement activity, see: Level of Manager Involvement (Node 6).

The DetermineMgrInvolvement API contains some sample logic that is preceded by the comment delimiter. This sample code, if enabled, enforces these rules:

You can modify this sample logic by increasing or decreasing the values assigned to the variables l_approval_req_amount and l_notify_only_amount.

Note: You must add and remove the necessary comment delimiters to make the sample logic in this API control the Level of Manager Involvement activity.

Authority Verification Procedure (VerifyAuthority)

When a manager approves an expense report, the Verify Authority activity in the Manager (Spending) Approval workflow process determines whether the manager has the signing limit and cost center authority to approve the expense report. The Verify Authority activity bases its verification on values you enter in the Payables Employee Signing Limit window.

You can use the Authority Verification procedure to define additional custom rules that determine whether a manager has the authority to approve an expense report.

Note: Any changes to this procedure will be ignored if you have implemented Oracle Approvals Management.

Accounts Payable Involvement Procedure (CustomValidateExpenseReport)

Your accounts payable department must review and approve expense reports that contain lines for which a justification or an original receipt is required. Your accounts payable department approves an expense report by checking the Reviewed by Payables check box in the Payables Expense Reports window. Once reviewed by the payables department, the Approval workflow process marks the expense report as Payables Approved.

You can use the Accounts Payable Involvement Procedure to define additional rules for when accounts payables approval is necessary for expense reports. For example, you could define this procedure so only every third expense report requires accounts payable approval, regardless of whether the report contains lines that require a justification or an original receipt.

Find Approver Procedure (FindApprover)

The Find Approver activity in the Manager (Spending) Approval workflow process determines the first and (if necessary) subsequent approvers for an expense report based on the supervisor hierarchy defined in Oracle HRMS. You can use the Find Approver procedure to define custom rules for selecting approvers for expense reports.

Note: Any changes to this procedure will be ignored if you have implemented Oracle Approvals Management.

Accounting Client Extensions

The Accounting client extension provides three procedures designed to let customers create custom rules to build and validate code combinations. If enabled, these PL/SQL APIs are called during expenses entry and in workflow (based on system setup), and in the expenses audit module. The name of the template package that contains the procedures for validating accounting code combinations is AP_WEB_CUST_ACCTG_PKG.

The first two are available for non-project expenses, and the third one is available for project-related expenses. This section describes the details for each of the procedures available in this client extension package:

For a description of the standard accounting generation process, refer to Implementing Accounting Client Extensions for Oracle Internet Expenses. (My Oracle Support Note Number 333199.1)

Implementation Considerations

Consider the following issues as you plan and implement your customizations:

Important: It is critical that you evaluate the performance impact if accounting generation is customized during expenses entry or in the audit module. In addition, it is recommended that you call the standard accounting validation instead of creating your own customized validation logic.

Print out these files before you begin writing procedures for your workflow processes. The files are located in the Oracle Payables $AP_TOP/patch/115/sql directory:

Build Account Procedure (BuildAccount)

The Build Account function helps build and validate account code combinations for non-project related expense lines. For details on how to validate key flexfields, refer to Oracle Applications Flexfields Guide. You may also refer to Internet Expenses Standard Accounting Generation logic in AP_WEB_ACCTG_PKG.BuildAccount ($AP_TOP/patch/115/sql/apwacctb.pls).

Get Is Custom Build Only Procedure (GetIsCustomBuildOnly)

When online validation is disabled, the GetIsCustomBuildOnly function allows you to indicate whether you want to enable the capability to use the BuildAccount procedure to rebuild account segments in expenses entry. The GetIsCustomBuildOnly function is called by the Expenses Entry Allocations page when the user either clicks Update or Next, while online validation is disabled.

If you do enable this capability, then you must update BuildAccount to support p_build_mode = C_CUSTOM_BUILD_ONLY. If you do not enable this capability, then the possibility for rebuilding the account segments may occur in Expenses Workflow with BuildAccount p_build_mode = C_VALIDATE.

Build Project Account Procedure (BuildProjectAccount)

The Build Project Account function helps build and validate account code combinations for project-related expense lines. For details on how to validate key flexfields, refer to Oracle Applications Flexfields Guide. You may also refer to Internet Expenses Standard Accounting Generation logic in AP_WEB_ACCTG_PKG.BuildProjectAccount ($AP_TOP/patch/115/sql/apwacctb.pls).

For more information on using these procedures, refer to Implementing Accounting Client Extensions for Oracle Internet Expenses. (My Oracle Support Note Number 333199.1)

Expense Report Summary Client Extension

The Expense Report Summary client extension lets you display your own customized message as the expense report summary on the Review page and the Confirmation page. Your customized message can include your own calculations for the expense report and fully-formatted message text.

The Expense Report Summary client extension is provided as a PL/SQL API that lets you add your own PL/SQL custom code. The package AP_WEB_OA_CUSTOM_PKG contains the function GetCustomizedExpRepSummary. The message text that this code returns is displayed on the Review Page and the Confirmation Page. By default, the custom region is not displayed. In order to display the custom message, the GetCustomizedExpRepSummary function must return a message string that you supply.

Note: The API can only access data after the user saves the expense report.

The GetCustomizedExpRepSummary function contains two input parameters:

P_CurrentPage is set to either:

To implement the Expense Report Summary client extension:

  1. Create a custom package to accept the GetCustomizedExpRepSummary input parameters and return a message string for display as the expense report summary.

  2. Modify the AP_WEB_OA_CUSTOM_PKG.GetCustomizedExpRepSummary to override the default null value.

  3. Hide the default Expense Report Summary region using either the Application Developer Common-Modules (AK) functionality or the Oracle Application Framework.

  4. Bounce the Application Server (9iAS) for the changes to take affect.

    For more information about the Expense Report Summary client extension, see Expense Report Summary Client Extension API , (My Oracle Support Note Number 201050.1).

Approval Communications Client Extension

You can use the Approval Communications Client Extension to create, duplicate, or delete notes. These values are defined for the Approval Communications Client Extension:

Creating Notes

You can use the AP_NOTES_PUB.Create_Note procedure to create notes. You may want to create notes with translations of standard text in the languages used by your company.

Define your translated notes using the following combinations of Object and Note Type Values.

p_Source_Object_Code Related Entity p_Note_Type Description
OIE_EXPENSE_REPORT Expense Report OIE_AUDITOR_AUDITOR Auditor only note.
OIE_EXPENSE_REPORT Expense Report OIE_PREPARER_AUDITOR Note required for approval communications.
OIE_CREDIT_CARD_TXN Credit Card Txn OIE_DISPUTES Note for disputing a credit card transaction.

Deleting Notes

Use the Delete_Notes procedure to delete notes. This procedure deletes all the notes associated with a Source Object and Note Type. If no Note Type is specified, the procedure will delete all the notes associated with the Source Object.

Duplicating Notes

Use the Copy_Notes Procedure to create duplicates of existing notes. You will need to create duplicate notes when you create -1 and -2 reports. The Source Object Code for each new note must be different than the original note's Source Object Codes.

AME Client Extensions

Oracle Approvals Management (AME) Header-Level Approver Client Extension

Use the predefined AP_WEB_CUST_AME_PKG.getHeaderLevelApprover function to set the first approver for approvals routing for the entire expense report. You can also use this function to change the order of precedence for approvals and route expense reports to the cost center owner, cost center business manager, project manager, or award manager. If you do not configure the order of precedence, the approvals are routed in the following order: awards approvers, projects approvers, cost center approvers. The AP_WEB_CUST_AME_PKG.getHeaderLevelApprover function returns the person ID.

Cost Center Approvers

When the approval is set to be routed to the cost center approvers, the system will return:

Project Approvers

When the approval is set to be routed to the project approvers, the system will return:

Awards Approvers

When the approval is set to be routed to the awards approvers, the system will return:

The AP_WEB_CUST_AME_PKG.getHeaderLevelApprover function resides in the apwamecb.pls file in the directory: /apdev/ap/11.5/patch/115/sql.

See: Integrating with Oracle Approvals Management.

The query for the applicable AME attribute is:

select AP_WEB_CUST_AME_PKG.getHeaderLevelApprover(:transactionId)
from ap_expense_report_headers_all
where report_header_id = :transactionId

Note: The JOB_LEVEL_NON_DEFAULT_STARTING_POINT_PERSON_ID attribute in AME is seeded with a query to get the value for the Approver field. Therefore, to use this client extension, you should update this attribute with the query provided above.

AME Cost Center Approver Client Extension

Use the predefined AP_WEB_CUST_AME_PKG.getCostCenterApprover function to route an expense report for approval to cost center owners. This API is only supported when you use Oracle Approvals Management for expense report approvals.

You can only use the call to this API with an AME dynamic approval group. The AP_WEB_CUST_AME_PKG.getCostCenterApprover function returns the person ID.

To use the AME Cost Center Approver Client Extension:

  1. Create a cost center approval group with this dynamic query:

    select 'person_id:'||AP_WEB_CUST_AME_PKG.getCostCenterApprover(:transactionId) person_id
    from ap_expense_report_headers_all
    where report_header_id = :transactionId
    

    The AP_WEB_CUST_AME_PKG.getCostCenterApprover function resides in the apwamecb.pls file in the directory: /apdev/ap/11.5/patch/115/sql. The file name is apwamecb.pls.

  2. According to your business requirements, set up a rule with Approval Type pre-chain-of-authority approvals or post-chain-of-authority approvals, then select "Require pre/post-approval from Cost Center Approval Group" for Approval.

  3. Before you enable the new rule, test the Approval Group using the AME Test tab.

    This table describes the different expenses entry scenarios and the value returned by the function for each scenario. The Cost Center default value refers to the value defined in the employee's HR setup.

    Cost Center Approver Field Value Returned
    Default Null Null
    Default Employee's supervisor Null
    Default Not employee's supervisor, but approver belongs to employee's cost center Null
    Default Not employee's supervisor, and approver does not belong to employee's cost center Cost center business manager ID or, if business manager is not defined, cost center owner ID
    Changed Null Cost center business manager ID or, if business manager is not defined, cost center owner ID
    Changed Employee's supervisor Cost center business manager ID or, if business manager is not defined, cost center owner ID
    Changed Not employee's supervisor, but approver belongs to employee's cost center Cost center business manager ID or, if business manager is not defined, cost center owner ID
    Changed Not employee's supervisor, approver not in employee's cost center, and approver's default cost center is different from the cost center entered Cost center business manager ID or, if business manager is not defined, cost center owner ID
    Changed Not employee's supervisor, but approver's default cost center is the same as the cost center entered Null

Related Topics

Approval Groups, Oracle Approvals Management Implementation Guide

Expense Report Number Client Extension

Use the Expense Report Number client extension to generate custom expense report numbers. You can, for example, customize the format of the expense report number to match your company's numbering system.

You create custom expense report numbers by modifying the plsql function AP_WEB_OA_CUSTOM_PKG.GetNewExpenseReportInvoice. This function does not return a default value. If you do not implement this function, Internet Expenses uses the current numbering sequence.

The AP_WEB_OA_CUSTOM_PKG.GetNewExpenseReportInvoice function is located in the directory $AP_TOP/patch/115/sql/apwcstmb.pls. The input parameters passed for this function are:

The returned value is the new expense report number.

Employee Matching Rules

Employee matching rules are used to automatically create new credit card accounts if they do not already exist in your system. New credit card accounts are created in the AP_CARD_DETAILS table. To enable this functionality, assign a matching rule in the Card Programs window.

Note: If no matching rule is assigned to the card program, then no employee matches will be performed. See: Defining Credit Card Programs for more information.

Internet Expenses provides two matching rules, a Default rule for MasterCard and Visa card programs and an American Express rule for American Express card programs. These matching rules are defined for the most common scenarios. However, there are cases where the employee information provided by the card issuers varies depending on the geographical region in which you operate. For instance, the national identifier (for example, social security number) or employee number may or may not be provided. Also, the employee name may in some cases be given in the "FIRSTNAME LASTNAME" format, but in other cases in the "LASTNAME, FIRSTNAME" format.

If the provided matching rules do not apply to your implementation, you will need to create a new matching rule. Employee matching rules are created as pl/sql packages. If you want to use one of the available matching rules as a template, the existing matching rules are stored in the following directory: $AP_TOP/patch/115/sql. The names of the packages are:

Note: If you need to define a matching rule, you should not modify the existing packages but instead should create a new pl/sql package.

Matching Rules Behavior

For the provided American Express and Default matching rules, in order for a match to be successful, all of the following items in the credit card transaction feed must have an exact match with the information in the Oracle Human Resources tables:

The provided Employee Number matching rule must exactly match only the employee number.

American Express Matching Rule

Default Matching Rule (MasterCard, Visa, American Express)

Employee Number Matching Rule

Employee number: The employee number has no special formatting.

Creating a Matching Rule

To create a new employee matching rule:

  1. The name of the pl/sql package must be used as the lookup Code value for the OIE_EMP_MATCH_RULE lookup. See: Defining Payables Lookups for Oracle Internet Expenses for more information.

  2. The pl/sql package must define a procedure with the following signature: PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER).

  3. The procedure must populate the table AP_CARD_EMP_CANDIDATES with all possible matches.

  4. To prevent overriding your customizations, save your new package in a directory other than the $AP_TOP/patch/115/sql directory.