There are many clients who don't want to change their LDAP structure for clarity and they use common node which all application uses. They same time want that LDAP sync job don't unlock the users who were locked before running LDAP sync JOB. Following is the solution for that. Please add following trigger to cmn_sec_users.
CREATE OR REPLACE TRIGGER "NIKU"."LDAP_LOCK_USERS" BEFORE INSERT OR UPDATE ON CMN_SEC_USERS FOR EACH ROW declare userid number; BEGIN //This select statement checks status of Sync new and Update job if it is processing we pick user who schedule //that job. So if job is not running we will have last_updated_by will be null and following condition will never //satisfy so trigger will effective only while Sync new and update job runs. s //During that job is processing we will check for insertion and updating. //If last updated by user name matches we lock new users. In other words if currently edited user's updated by and user who is running the job are same we will lock the user. IF INSERTING and :NEW.LAST_UPDATED_BY = userid THEN :NEW.USER_STATUS_ID := 202; //If last updated by user name matches while updates and if user old status is lock we lock them (as sync new //and update job activates user on sync) ELSE IF UPDATING AND :OLD.USER_STATUS_ID = 202 and :new.last_updated_by = userid THEN :NEW.USER_STATUS_ID := 202; END IF; END IF; exception when NO_DATA_FOUND THEN NULL; END; |
*Note: Revisit this trigger after every SP/FP or Upgrade if this piece needs to added again.