EAR: PTMATREFVW AE Program Abends At Step PTMATREFVW.MAIN.Step01 Due To Unique Constraint On Record PS_AR_ONACT_PY_MVW
(Doc ID 2758801.1)
Last updated on MARCH 09, 2021
Applies to:PeopleSoft Enterprise FIN Receivables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
It has been detected that the delivered Refreshes Materialized View process (PTMATREFVW AE Program) is abending at Step PTMATREFVW.MAIN.Step01 due to a unique constraint reported on Record PS_AR_ONACT_PY_MVW.
This happens if there are two rows in PS_ITEM Record with the same ITEM Field name, but under different Customers, and that one has an Item Status of Closed, and the other one as Open. View PS_AR_ONACT_PY_MVW has to do with On Accounts, so the affected Item rows need to also have an Entry Type of OA.
1.- Log into the FSCM Online Application as a Receivables User
2.- Create a new Regular Deposit with 2 Payment transactions, each for a different Customer
3.- Build 2 new Payment Worksheets, one for each Payment/Customer, and place the amount into a new On Account Item, making sure the Item ID value is the same on both cases (Example: On Account Item ID = OA-ITEM)
4.- Launch AR Update to process the two new Payment Groups
5.- Confirm that for each Customer Account, a new Item ID OA-ITEM has been posted for their respective payment amounts
6.- Create a new Pending Group with one single Item, for one of the customers previously used, equal to the respective Payment Amount, and set it to Post Action Batch Standard
7.- Launch AR Update to process any Pending Items
8.- Build a new Maintenance Worksheet for the Customer who has now an Item and an On Account Item, so they can be matched together, and set it to Post Action Batch Standard
9.- Launch AR Update to process all Maintenance Groups
10.- Confirm that the first Customer has the Item ID OA-ITEM as Closed, while the second Customer has their Item ID OA_ITEM still open
11.- Log into the FSCM Online Application as a Admin User ID
12.- Navigate to: PeopleTools > Utilities > Administration > Materialized Views > Materialized View Maintenance
13.- Create a new Run Control ID, and select the Accounts Receivable Views, including AR_ONACT_PY_MVW
14.- Launch the Refreshes Materialized View process (PTMATREFVW AE Program)
15.- Confirm that the program has abended in Error Message
" Materialized View AR_ONACT_PY_MVW Refresh Started (213,1)
File: /vob/peopletools/src/pssam/samutil.cppSQL error. Stmt #: 9657 Error Position: 0 Return: 12008 - ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at "SYS.DMBS_SNAPSHOT_KKXRCA", line 2952 ORA-06512: at "SYS.DMBS_SNAPSHOT_KKXRCA", line 2370 ORA-00001: unique constraint (PSF.PS_AR_ONACT_PY_MVW) violated ORA-06512: at "SYS.DMBS_S
Failed SQL stmt: EXECUTE DBMS_MVIEW.REFRESH( :1, :2 )
Materialized View AR_ONACT_PY_MVW Refresh Ended (213,2)
Materialized View AR_ONACT_PY_MVW Refreshed Successfully (213,3)
SQL.Fetch: object isn't open on a select. (2,288) PTMATREFVW.MAIN.GBL.default.1900-01-01.Step01.OnExecute PCPC:4315 Statement:42
Process 1929213 ABENDED at Step PTMATREFVW.MAIN.Step01 (PeopleCode) -- RC = 24 (108,524) "
As the Refreshes Materialized View process (PTMATREFVW AE Program) does not complete, the Views are not being updated, and hence, these Fluid Tile Views do not contain/show the latest information, and cannot be considered reliable.
The codeline behind the Accounts Receivable Materialized View PS_AR_ONACT_PY_MVW should be able to handle scenarios where the On Account Item ID value is used multiple times under different Customer ID values. This abend and Error Message should not be taking place when running the Refreshes Materialized View process (PTMATREFVW AE Program).
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document