EEX 9.0: Expense Report Approval E-mail Notifications Showing Variable %s In MSSQL Database (Doc ID 1680015.1)

Last updated on MARCH 16, 2016

Applies to:

PeopleSoft Enterprise FIN Expenses - Version 9 to 9 [Release 9]
Information in this document applies to any platform.

Symptoms

ISSUE:

The 'Expense Report Routed For Approval', and 'Approved For Payment' E-mail Notifications sent by the Expenses Workflow to the proper users (Approver, and Owner) do not display the expected transaction data values on the Variables contained and defined in their respective Generic Templates. Instead, it is showing only % figures, as below:

  Employee ID: %2
  Employee Name: %3
  Submission Date: %4
  Report Description: %6
  Report ID: %5
  Business Purpose: %7
  Total Amount: %8
  Reimbursement Amount: %9

This is happening when the below criteria are met:

  a.- The PeopleSoft Enterprise FSCM Environment is configured under a MicroSoft SQLServer Database Platform
  b.- The PeopleSoft Enterprise FSCM Environment is at least at patch level:
       - FSCM 9.0 ESA Bundle #39 (Changes introduced by <Bug 12949591>)
       - FSCM 9.1 ESA Bundle #17 (Changes introduced by <Bug 13085863>)
       - FSCM 9.2 Image #0
  c.- When the current Active Effective Date row from the defined set up values on Currency Code (PS_CURRENCY_CD_TBL), and Business Purpose (PS_EX_PURPOSE_TBL) Records is not the same

REPLICATION STEPS:

 - Have a PeopleSoft Enterprise FSCM environment using MicroSoft SQLServer Database Platform
 - Have the FSCM environment patched at ESA Bundle #39 (For 9.0 Release), ESA Bundle #17 (For 9.1 Version), or Image #0 (On FSCM 9.2)
 - Log into the FSCM Online Application as user ID VP1
 - Configure the ERApproval process to make sure to submit both Online Emails and Worklist Items
 - Navigate to: Set Up Financials/Supply Chain > Install > Installation options > Expenses
 - Make sure to have the following settings configured:
      - Workflow Enabled = Y
      - HR Manager – Supervisor = Y
 - Navigate to: Set Up Financials/Supply Chain > Product Related > Expenses > Management > Approval Setup > Transaction Definition
 - Open the existing configuration for Expense Reports, and make sure the HR Supervisor approval level is properly selected
 - Navigate to: Set Up Financials/Supply Chain > Product Related > Expenses > Purchase > Business Purpose
 - Open the existing Business Purpose ID 00013 under Set ID SHARE, and add a new effective date row with the below settings:
      - Set ID = SHARE
      - Business Purpose = 00013
      - Effective Date = January 1st 2014
      - Status = Active
      - Description = GCS Consulting Services
 - Log into the FSCM Online Application as User ID EXA1
 - Navigate to: Employee Self-Service > Travel and Expense Center > Expense Report > Create
 - Add a new Expense Report transaction on behalf of Employee ID KU0021, with at least one Expense Line, making sure that at the Header level the Business Purpose defined is ID 00013 (GCS Consulting Services)
 - Submit the Expense Report for approval
 - ISSUE #1: Check in the Email Account Inbox from HR Supervisor (User ID EXS1), and confirm that the 'Route For Approval' e-mail contains variable %s instead of values
 - Log into the FSCM Online Application as User ID EXS1
 - Navigate to: Manager Self-Service > Travel and Expense Center > Approvals > Approve Transactions
 - Find the submitted Expense Report, and approve it for payment
 - ISSUE #2: Check in the Email Account Inbox from Expense Report Owner (User ID EXA1), and confirm that the 'Approved For Payment' e-mail contains variable %s instead of values

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

BUSINESS IMPACT:

Users are confused because before the indicated ESA Bundles got installed, the same notifications provided useful information of the transaction submitted/approved, but now it is plain gibberish. This is due to the fact that the delivered SQL Definition EX_SHEET_HDR_SEL, has added for MicroSoft SQLServer platform a new codeline, with the new criteria on Record PS_CURRENCY_CD_TBL using alias G, where C.EFFDT = G.EFFDT, which is not always the case, specially being PS_EX_PURPOSE_TBL C. If a Business Process value is defined with an Effective Date different than a Currency Code Effective Date value, then the SQL Statement returns nothing.

EXPECTED BEHAVIOR:

The E-mail Notification should display always the proper transaction values in all Workflow-generated E-mail Notifications, so the information contained in the communication is useful.
 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms