EEX 9.2: Stage Payments Abends At Step EX_STAGE_PMT.SHEETADV.SHADVTAO Due To Changes Performed On SQL View EX_SHEETADV_VW2 For DB2 And Oracle Databases
Last updated on JUNE 12, 2018
Applies to:PeopleSoft Enterprise FIN Expenses - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
The solution provided through <Bug 25220333>, (FSCM 9.2 Image #23), touched several objects, including the below two:
- Record: PS_EX_SHEET_ADV
- SQL View: EX_SHEETADV_VW2
From Table PS_EX_SHEET_ADV, new Fields were included, such as: LINE_NBR, FOREIGN_AMOUNT, FOREIGN_CURRENCY, RATE_MULT, RATE_DIV. While new codeline was delivered to make sure newly created transactions make use of all the new added Fields, for those already existing historical transactions, the same Fields will automatically store 0 or Blank values by default. And it is this very same thing that is causing the Expenses Processing Application Engine Program (EX_TRAN_PRCS) to Abend at Step EX_STAGE_PMT.SHEETADV.SHADVTAO with a unique constrain violation into Table PS_EX_SHTADV_TAO4.
This happens because the modified codeline from SQL View EX_SHEETADV_VW2 is not properly summing up the applied amounts of the eventual scenario when an Expense Report has gotten applied multiple Cash Advances prior to the upgrade of Image #23. Due to the fact that the new Fields will take 0 or Blank values for existing transactions, should an Expense Report exist with two lines in PS_EX_SHEET_ADV Record, the CASE SUM function in the SQL View will try to insert one row per Cash Advance linked to the same Expense Report, causing the duplicate Error Message.
1.- Have an FSCM 9.2 environment patched to FSCM 9.2 Image #22 at the most
2.- Create a couple of Cash Advances for Employee ID KU0021
3.- Submit them for approval
4.- Approve them for payment
5.- Stage the Cash Advances for Payment
6.- Post Liabilities for the involved Cash Advances
7.- Run a Pay Cycle to completion to generated the needed Payments
8.- Launch Post Payments process
9.- Create a new Expense Report on behalf of Employee ID KU0021
10.- Apply both Cash Advances, and ensure to have a remaining amount to be owed to the Employee
11.- Submit the Expense Report for Approval
12.- Approve the Expense Report
13.- Query PS_EX_SHEET_ADV Record, and confirm the Expense Report ID in question has 2 listed rows in the Table
14.- Upgrade the environment to FSCM 9.2 Image #23 (or at least make sure to apply <Bug 25220333>)
15.- Query again Record PS_EX_SHEET_ADV for the same Expense Report ID, and confirm that while the 2 rows are still present, all new added Fields (LINE_NBR, FOREIGN_AMOUNT, FOREIGN_CURRENCY, RATE_MULT, RATE_DIV) are filled with 0 or Blank values
16.- Launch the Expense Processing EX_TRAN_PRCS AE Program having selected the 'Stage Payments' option
17.- Confirm that EX_TRAN_PRCS AE Program abended in an Error Message
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.
" -- 20:29:21.317 ...(EX_STAGE_PMT.PAYMENTS.SHEETADV) (Call Section EX_STAGE_PMT.SHEETADV)
-- 20:29:21.318 ....(EX_STAGE_PMT.SHEETADV.SHADVTAO) (SQL)
INSERT INTO PS_EX_SHTADV_TAO4 (PROCESS_INSTANCE, EMPLID, SHEET_ID, LINE_NBR, CURRENCY_CD, MONETARY_AMOUNT, FOREIGN_AMOUNT, FOREIGN_CURRENCY, RATE_MULT, RATE_DIV) SELECT 119576, EMPLID, SHEET_ID, LINE_NBR, CURRENCY_CD, MONETARY_AMOUNT, FOREIGN_AMOUNT, FOREIGN_CURRENCY, RATE_MULT, RATE_DIV FROM PS_EX_SHEETADV_VW2
-- Row(s) affected: 0
-- 20:29:21.445 Process 119576 ABENDED at Step EX_STAGE_PMT.SHEETADV.SHADVTAO (Action SQL) -- RC = 805
-- 20:29:21.470 SQL Error: ORA-00001: unique constraint (EMDBO.PS_EX_SHTADV_TAO4) violated "
Since the solution for <Bug 25220333>, (FSCM 9.2 Image #23), has been implemented, no new Expense Reports, nor Cash Advance transactions are able to be staged for payment, and this delays the reimbursement of expenses owed to both Employees, and Credit Card Vendors.
The Stage Payments process should be able to handle historic rows in PS_EX_SHEET_ADV on the scenario of multiple Cash Advances applied against the same Expense Report. Whether legacy transaction data needs to follow the new data structure on PS_EX_SHEET_ADV Table, or the codeline from SQL View EX_SHEETADV_VW2 needs to be modified.
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