This chapter covers the following topics:
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:
Validations and Defaults client extension
Workflow client extension
Accounting client extensions
Expense Report Summary client extension
Approval Communications client extension
AME client extensions
Expense Report Number client extension
Employee Matching Rules
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.
To determine if you need to use client extensions:
Define and document your company's business requirements and rules.
Determine if these business rules are handled by the standard features of Internet Expenses.
For those business rules not handled by the standard functionality, determine which client extensions can address your specific business needs.
To design your logic:
Understand the client extensions you propose to use, including their purpose, processing flow, when Internet Expenses calls the extensions, and the input values.
Define and document the requirements and logic of your business rules under all possible conditions. Determine the inputs, calculations performed, and resulting outputs.
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.
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.
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.
This section is a brief overview of PL/SQL procedures.
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 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.
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:
apwdfcfs.pls
This file contains the specification template for this extension package. If you create procedures outside the predefined procedure within the AP_WEB_CUST_DFLEX_PKG package, you must also modify this file to include those new procedures.
apwdfcfb.pls
This file contains the procedures that you can modify to implement the extension. You can define as many procedures as you want within this package or within the predefined procedure.
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.
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.
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:
Independently
In conjunction with per diem and mileage schedules
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.
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.
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).
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:
apwxwfcs.pls
This file contains the specification template for this extension package. If you create procedures outside the predefined procedure within the AP_WEB_EXPENSE_CUST_WF package, you must also modify this file to include those new procedures.
apwxwfcb.pls
This file contains the procedures that you can modify to implement the extension. You can define as many procedures as you want within this package or within the predefined procedure.
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:
Manager Approval
Managers directly approve expense reports themselves.
Manager Notification
Expenses reports receive automatic manager approval and the workflow notifies the manager that the expense report was approved.
No Manager Involvement
Expenses reports receive automatic manager approval, but the workflow does not notify the manager that the expense report was approved.
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:
For expense reports in which the sum of reimbursable amounts for all expense lines is greater than or equal to 500, manager approval is required.
For expense reports in which the sum of reimbursable amounts for all expense lines is greater than or equal to 100 (and less than 500), only manager notification is required.
For expense reports in which the sum of reimbursable amounts for all expense lines is less than 100, there is no manager involvement.
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.
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.
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.
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.
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:
System Setup Controls Behavior. If you enable expense allocations without online validation, then only the build portion of the customization will be called during expenses entry.
Impact on Standard Accounting Generation. If the customization is performed, then both the standard build and validation logic are overridden. For the validation stage, you can call the standard validation logic from your customization. If the customization is performed, then both the standard build and validation logic are overridden. For the validation stage, you can call the standard validation logic from your customization.
Impact on Expenses Processing. Since the same PL/SQL API's are called during expenses entry, workflow, and expenses audit, the customization applies to all three points in the expenses process. In other words, you cannot choose to, for example, call the customization for expenses entry, and not for expenses audit.
Impact in Expenses Audit. The customization is only called when auditors change expense types. If auditors directly update accounting segments, then no builds occur, and the standard validation logic is invoked.
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:
apwcaccs.pls
This file contains the specification template for this extension package. If you create procedures outside the predefined procedure within the AP_WEB_CUST_ACCTG_PKG package, you must also modify this file to include those new procedures.
apwcaccb.pls
This file contains the procedures that you can modify to implement the extension. You can define as many procedures as you want within this package or within the predefined procedure.
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).
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.
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)
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_ReportHeaderId
P_CurrentPage
P_CurrentPage is set to either:
AP_WEB_OA_CUSTOM_PKG.C_ReviewPage
AP_WEB_OA_CUSTOM_PKG.C_ConfirmPage
To implement the Expense Report Summary client extension:
Create a custom package to accept the GetCustomizedExpRepSummary input parameters and return a message string for display as the expense report summary.
Modify the AP_WEB_OA_CUSTOM_PKG.GetCustomizedExpRepSummary to override the default null value.
Hide the default Expense Report Summary region using either the Application Developer Common-Modules (AK) functionality or the Oracle Application Framework.
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).
You can use the Approval Communications Client Extension to create, duplicate, or delete notes. These values are defined for the Approval Communications Client Extension:
Source Object. Use the Source Object Code and Source Object ID values to associate a given item to a note. For example, source object code = 'expense report' and source object id = '1121' associates expense report number 1121 with the note.
Note Type. Use the Note Type value to categorize a note as a particular type, such as 'auditor only'. Use the Note Type together with the Source Object values to control access to the notes.
Source Language. Use Source Language value to indicate which language the note is in for translation purposes. Seeded note text may be available in more than one language. Text entered by the user is not translated. The Source language value does not restrict the text characters in the note.
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. |
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.
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.
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.
When the approval is set to be routed to the cost center approvers, the system will return:
The person ID of the approver that is entered in the Approver field on the General Information page.
Null if no value is entered in the Approver field and the cost center entered on the General Information page is the same as the employee's default cost center.
The cost center business manager's person ID, if no value is entered in the Approver field and the header cost center is different from the employee's default cost center. If the business manager is not defined, then the cost center owner ID is returned.
When the approval is set to be routed to the project approvers, the system will return:
The person ID of the approver that is entered in the Approver field on the General Information page.
The project manager's person ID if no value is entered in the Approver field and all the expenses in the expense report are charged to the same project number.
Null if there is no value entered in the Approver field, and there are multiple project numbers in the expense report.
When the approval is set to be routed to the awards approvers, the system will return:
The person ID of the approver that is entered in the Approver field on the General Information page.
The award manager's person ID if no value is entered in the Approver field and all the expenses in the expense report are charged to the same award number.
Null if there is no value entered in the Approver field, and there are multiple award numbers in the expense report.
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.
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:
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.
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.
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
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:
p_employeeId - IN ap_expense_report_headers.employee_id%TYPE
p_userId - IN fnd_user.user_id%TYPE
p_reportHeaderId - IN ap_expense_report_headers.report_header_id%TYPE
The returned value is the new expense report number.
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:
American Express: AP_WEB_AMEX_PKG (file names are apwamexs.pls and apwamexb.pls).
Default (MasterCard and Visa): AP_WEB_MATCHING_RULE_PKG (apwmachs.pls, apwmachb.pls).
Employee Number: AP_WEB_EMP_NUM_MATCH_PKG (apwenmhb.pls, apwenmhs.pls).
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.
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:
Employee name
Employee number
National identifier
The provided Employee Number matching rule must exactly match only the employee number.
Employee name: This matching rule assumes that full_name (column in the American Express transaction files) will be populated with the employee's name in the format "First Middle Last". If the name is just one word, it is assumed to be the last name. If the name is two words, it is assumed to be first name and last name. Also, a case insensitive match will be performed on the name.
Employee number: The employee number has no special formatting.
National identifier: In the United States, this is an employee's social security number. American Express allows for 10 characters. Since social security numbers contain 9 characters, American Express provides a leading 0 which the matching rule then discards. If you are implementing in other countries that provide a national identifier, and there is one or more leading 0, the matching rule will still only discard the first leading zero, so you must create a new matching rule.
Employee name: If any of the columns FIRST_NAME, MIDDLE_NAME, LAST_NAME are populated, this will take precedence over FULL_NAME for matching against the employee name. Names always use a case-insensitive match.
Employee number: The employee number has no special formatting.
National identifier: The national identifier has no special formatting.
Employee number: The employee number has no special formatting.
To create a new employee matching rule:
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.
The pl/sql package must define a procedure with the following signature: PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER).
The procedure must populate the table AP_CARD_EMP_CANDIDATES with all possible matches.
To prevent overriding your customizations, save your new package in a directory other than the $AP_TOP/patch/115/sql directory.