Project and Portfolio Management Practitioners Forum
cancel

LDAP - PPM error

Highlighted
rinuvarghese
Trusted Contributor.

LDAP - PPM error

Hi,

 

I am getting the below error while importing user from ldap to PPM.

can you help me to address this?

 

I am getting correct count in KNTA_USER_INT TABLE that suppose to get from my filter values

 

P_SOURCE_CODE="LDAP_IMPORT", "LDAP_IMPORT" Executing PL/SQL procedure KNTA_USER_INT.Run_Derive_And_Validate ERROR: value: ORA-01427: single-row subquery returns more than one row {KNTA_USER_INT-2047} sqlType: VARCHAR Error executing PL/SQL procedure KNTA_USER_INT.Run_Derive_And_Validate: An unknown error has occurred. For more information, please contact your PPM administrator and provide the following GUID number: GUID=A3A285E3-95AB-A968-8090-B01BE551D5DB Out parameter p_group_id: value: 30998 sqlType: INTEGER Out parameter o_message_type: value: 20 sqlType: INTEGER Out parameter o_message_name: value: ORACLE_ERROR sqlType: VARCHAR Out parameter o_message: value: ORA-01427: single-row subquery returns more than one row {KNTA_USER_INT-2047} sqlType: VARCHAR

 

 

Regards,

Rinu Varghese

1 REPLY
jsalass
Honored Contributor.

Re: LDAP - PPM error

Hey Rinu,

 

The final part of the error supplied is the key:

    ORA-01427: single-row subquery returns more than one row {KNTA_USER_INT-2047} sqlType: VARCHAR

This means that where a single value is expected from the database, multiple results have been returned.

The KNTA_USER_INT-2047 indicates what is being checked.  This maps onto the PPM - Departments Enabled validation.

This is how I get the error "KNTA_USER_INT-2047" is related to departments:

So, Run SQL developer, connect to PPM database, Expand PACKAGES, and then "KNTA_USER_INT Body"
This will show a large set of queries. Scroll down the list to locate the number 2047:

-- Derive department.
l_stmt_num := 2047;
UPDATE KNTA_USERS_INT cui
SET department_code =
( SELECT lookup_code
FROM KNTA_LOOKUPS_NLS l_main, KNTA_LOOKUPS_LOC l_loc
WHERE lookup_type = 'DEPT'
AND NVL(l_loc.MEANING,l_main.MEANING) = cui.department_meaning
AND l_main.REFERENCE_CODE = l_loc.REFERENCE_CODE(+)
AND cui.DATA_LANG = l_loc.lang_id(+)
)
WHERE group_id = p_group_id
AND process_phase = KNTA_Interface_Constant.PROCESS_PHASE_DERIVING
AND process_status = KNTA_Interface_Constant.PROCESS_STATUS_IN_PROCESS
AND department_meaning IS NOT NULL
AND EXISTS
( SELECT 1
FROM KNTA_LOOKUPS_NLS l_main, KNTA_LOOKUPS_LOC l_loc
WHERE lookup_type = 'DEPT'
AND NVL(l_loc.MEANING,l_main.MEANING) = cui.department_meaning
AND l_main.REFERENCE_CODE = l_loc.REFERENCE_CODE(+)
AND cui.DATA_LANG = l_loc.lang_id(+)
);

So this confirms that the 2047 is indicating a duplicate department

To fix this, open the PPM workbench -> Configuration -> Validations -> and then open "PPM - Department - Enabled".

This will show a list of all the Departments that are configured in the "validation values" section.   Check to see if there are multiple departments enabled with the same ID.

 

You can check also the list of department by using this query and look for duplicates:

Get the output of the following-
SELECT lookup_code
FROM KNTA_LOOKUPS
WHERE lookup_type = 'DEPT'
order by meaning;

Hope this helps!

 

Regards,

Jonathan