When attempting to run the API –  HR_PERSONAL_PAY_METHOD_API  to create a personal payment methods, the following error occurs:

ORA-20001: FLEX-DSQL EXCEPTION

This Error has been observed in :

• CREATE_GB_PERSONAL_PAY_METHOD
• CREATE_US_PERSONAL_PAY_METHOD

Cause :

The issue is caused by the following setup:

There was a change in foreign key constraint lock modes introduced in 11g by  the fix for unpublished Bug 5909305 – Change to DML (TM) lock modes for foreign key constraints.

That is an intentional change which has been introduced in 11g.

Solution :

To resolve this issue, perform the following steps:

1- Change the behavior style by setting “_fix_control” to ‘5909305:OFF’

    alter session set “_fix_control”=’5909305:OFF’ ;

2- Re-run the CREATE_PERSONAL_PAY_METHOD API as normal.

3- Retest the issue and confirm on the results.

hope this helpful 🙂