Process LM_EXPR_WRN gives Unique Constraint error When Certification Uses Cascading Expirations (Doc ID 2152777.1)

Last updated on JUNE 23, 2016

Applies to:

PeopleSoft Enterprise ELM Enterprise Learning Management - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When a certification does not use cascading expirations, the certification as a whole has a single expiration date (along with single warning, recertification, etc. dates). When a certification uses cascading expirations, each section of the certification maintains its own distinct set of date parameters in LM_REG_SCN_CERT. This record maintains the individual section date values, and is keyed on both the LM_PRG_REG_ID and LM_SCN_ID (unique Section ID).

When the delivered LM_EXPR_WRN (Expiration Warning) notification process runs, it collects expiration data and loads it into a temporary table that is then used to generate notifications. This temporary table (LM_LECR3_TAO) contains two keys, the PROCESS_INSTANCE ID, and LM_PRG_REG_ID. When the process runs and picks up all learners who have reached the expiration warning date threshold, it loads them into the temp table.


The process would fail with unique constraint errors on certain individuals who had completed a certification with cascading expiration. 

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.  Create a program with 2 nested programs
2. Set the nested programs with the same validity – say 2 days and a 1 day expiration warning
3. Register a learner such as Anand Gundu or someone with a email address. Run the process

Changes

 SQL : LM_EXPR_WRN MAIN Step18 D in AE LM_EXPR_WRN

Cause

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