Thanks for response. I had opened an SR with oracle for this. It was a bug and the resolution is below. After this change, the process succeeds. However the metadata does not flow inside Hyperion Essbase application. Load method is classic. Do I need to configure any adapters for ERPI to work with Essbase?
When running a metadata rule within ERPi for a PeopleSoft Source System the metadata rule fails. The ODI Operator shows that the "PS_GL_LOAD_DIMENSION_MEMBERS" process is failing at step 20 PS Extract Dimension Members.
java.sql.SQLException: ORA-00001: unique constraint (ERPINTEGRATOR.AIF_DIM_MEMBERS_STG_U1) violated
This caused a unique constraint exception as there were multiple records with the same "NAME" but with different EFFDT values in the AIF_PS_CF_METADATA table.
Until the fix is included in the next service pack, the following update
statement can be run *after* every execution of the Initialize Source System
process on a PSFT source system:
SET EFFDT_FLAG = 'Y'
WHERE SOURCE_SYSTEM_ID = ?
AND EFFDT_FLAG = 'N'
(FIELDNAME = 'AFFILIATE_INTRA1' AND RECNAME = 'AFFINTRA1_VW')
OR (FIELDNAME = 'AFFILIATE_INTRA2' AND RECNAME = 'AFFINTRA2_VW')