SSOQ
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IDM_STAGING.EMSA_IDM_STAGING_LOADER
Parameters
Name
Type
Mode
Definition
PACKAGE BODY EMSA_IDM_STAGING_LOADER AS -- -- Service Oriented functions -- FUNCTION GetServiceIdFromCode (pCode IN VARCHAR2) RETURN VARCHAR2 Is Cursor cSrv (cpCode VARCHAR2) Is SELECT object_id FROM service WHERE code = cpCode; -- vSrvId service.object_id%type; Begin Open cSrv(pCode); Fetch cSrv Into vSrvId; Close cSrv; -- return vSrvId; End; -- FUNCTION GetServiceCodeFromId (pId IN VARCHAR2) RETURN VARCHAR2 Is Cursor cSrv (cpId VARCHAR2) Is SELECT code FROM service WHERE object_id = cpId; -- vSrvCode service.code%type; Begin Open cSrv(pId); Fetch cSrv Into vSrvCode; Close cSrv; -- return vSrvCode; End; -- -- Profile Oriented functions -- FUNCTION GetProfileIdFromCode (pCode IN VARCHAR2) RETURN VARCHAR2 Is Cursor cPrf (cpCode VARCHAR2) Is SELECT object_id FROM profile WHERE code = cpCode; -- vPrfId profile.object_id%type; Begin Open cPrf(pCode); Fetch cPrf Into vPrfId; Close cPrf; -- return vPrfId; End; -- FUNCTION GetProfileCodeFromId (pId IN VARCHAR2) RETURN VARCHAR2 Is Cursor cPrf (cpId VARCHAR2) Is SELECT code FROM profile WHERE object_id = cpId; -- vPrfCode profile.code%type; Begin Open cPrf(pId); Fetch cPrf Into vPrfCode; Close cPrf; -- return vPrfCode; End; -- -- Role Oriented functions -- FUNCTION GetRoleIdFromCode (pCode IN VARCHAR2) RETURN VARCHAR2 Is Cursor cRol (cpCode VARCHAR2) Is SELECT object_id FROM role WHERE code = cpCode; -- vRolId role.object_id%type; Begin Open cRol(pCode); Fetch cRol Into vRolId; Close cRol; -- return vRolId; End; -- FUNCTION GetRoleCodeFromId (pId IN VARCHAR2) RETURN VARCHAR2 Is Cursor cRol (cpId VARCHAR2) Is SELECT code FROM role WHERE object_id = cpId; -- vRolCode role.code%type; Begin Open cRol(pId); Fetch cRol Into vRolCode; Close cRol; -- return vRolCode; End; -- -- -- Function LoadProfiles (pCreateFlag In Varchar2 Default 'Y', pUpdateFlag In Varchar2 Default 'Y', pDeactivateFlag In Varchar2 Default 'N') Return Varchar2 As vRetCode Varchar2(20); -- Begin vRetCode := 'Error'; -- -- First Log and then do the work. After the work is done, logging conditions will fail INSERT INTO update_log (log_date, description) SELECT sysdate, 'Deactivated = ' || pDeactivateFlag || ' - ' || prf.CODE FROM profile prf WHERE prf.CODE IN ( SELECT stg.code FROM profile stg MINUS SELECT ref.code FROM tmp_profile ref ) AND prf.ACTIVE = '1'; -- If (pDeactivateFlag != 'N') Then UPDATE profile prf SET prf.ACTIVE = '0', prf.LAST_CHANGED = sysdate WHERE prf.CODE IN ( SELECT stg.code FROM profile stg MINUS SELECT ref.code FROM tmp_profile ref ) AND prf.ACTIVE = '1'; End If; -- -- Log all eventual updates done to existing records INSERT INTO update_log (log_date, description) SELECT sysdate, 'Updated = ' || pUpdateFlag || ' - ' || prf.CODE from profile prf, tmp_profile ref2 where prf.code = ref2.code and (prf.description != ref2.description or prf.active = '0' or prf.human_system != DECODE(ref2.human, 'PERSON', '1', '0')); -- If (pUpdateFlag != 'N') Then UPDATE profile prf SET (prf.ACTIVE, prf.LAST_CHANGED, prf.human_system, prf.description) = ( select '1', sysdate, DECODE(ref.human, 'PERSON', '1', '0'), ref.description from tmp_profile ref where prf.code = ref.code and (prf.description != ref.description or prf.active = '0' or prf.human_system != DECODE(ref.human, 'PERSON', '1', '0')) ) WHERE prf.ACTIVE = '1' AND EXISTS ( select ref2.description from tmp_profile ref2 where prf.code = ref2.code and (prf.description != ref2.description or prf.active = '0' or prf.human_system != DECODE(ref2.human, 'PERSON', '1', '0')) ); End If; -- -- Log all new profiles that are to be created INSERT INTO update_log (log_date, description) SELECT sysdate, 'Created = ' || pCreateFlag || ' - ' || ref.CODE FROM tmp_profile ref WHERE ref.code IN ( SELECT ref2.code FROM tmp_profile ref2 MINUS SELECT stg.code FROM profile stg ); -- If (pCreateFlag = 'Y') Then INSERT INTO profile (code, description, active, human_system, last_changed) SELECT ref.code, ref.description, '1', DECODE(ref.human, 'PERSON', '1', '0'), sysdate FROM tmp_profile ref WHERE ref.code IN ( SELECT ref2.code FROM tmp_profile ref2 MINUS SELECT stg.code FROM profile stg ); End If; -- Return vRetCode; End; -- -- Function LoadRoles (pCreateFlag In Varchar2 Default 'Y', pUpdateFlag In Varchar2 Default 'Y', pDeactivateFlag In Varchar2 Default 'N') Return Varchar2 As vRetCode Varchar2(20); -- Begin vRetCode := 'Error'; -- -- First Log and then do the work. After the work is done, logging conditions will fail INSERT INTO update_log (log_date, description) SELECT sysdate, 'Deactivated = ' || pDeactivateFlag || ' - ' || rol.CODE FROM ROLE rol WHERE rol.CODE IN ( SELECT stg.code FROM role stg MINUS SELECT ref.role_code FROM tmp_roles ref ) AND rol.ACTIVE = '1'; -- If (pDeactivateFlag != 'N') Then UPDATE role rol SET rol.ACTIVE = '0', rol.LAST_CHANGED = sysdate WHERE rol.CODE IN ( SELECT stg.code FROM role stg MINUS SELECT ref.role_code FROM tmp_roles ref ) AND rol.ACTIVE = '1'; End If; -- -- Log all eventual updates done to existing records INSERT INTO update_log (log_date, description) SELECT sysdate, 'Updated = ' || pUpdateFlag || ' - ' || rol.CODE from role rol, tmp_roles ref2 where rol.code = ref2.role_code and (rol.active = '0' or NVL(rol.security_level,'-1') != DECODE(ref2.sec_level, 'EMSA','4', 'National','3', 'Local','2', 'User','1', '1')); -- If (pUpdateFlag != 'N') Then UPDATE role rol SET (rol.ACTIVE, rol.LAST_CHANGED, rol.security_level) = ( select '1', sysdate, DECODE(ref.sec_level, 'EMSA','4', 'National','3', 'Local','2', 'User','1', '1') from tmp_roles ref where rol.code = ref.role_code and (rol.active = '0' or NVL(rol.security_level,'-1') != DECODE(ref.sec_level, 'EMSA','4', 'National','3', 'Local','2', 'User','1', '1')) ) WHERE rol.ACTIVE = '1' AND EXISTS ( select ref2.role_code from tmp_roles ref2 where rol.code = ref2.role_code and (rol.active = '0' or NVL(rol.security_level,'-1') != DECODE(ref2.sec_level, 'EMSA','4', 'National','3', 'Local','2', 'User','1', '1')) ); End If; -- -- Log all new profiles that are to be created INSERT INTO update_log (log_date, description) SELECT sysdate, 'Created = ' || pCreateFlag || ' - ' || ref.role_CODE FROM tmp_roles ref WHERE ref.role_code IN ( SELECT ref2.role_code FROM tmp_roles ref2 MINUS SELECT stg.code FROM role stg ); -- If (pCreateFlag = 'Y') Then INSERT INTO role (code, active, security_level, last_changed) SELECT ref.role_code, '1', DECODE(ref.sec_level, 'EMSA','4', 'National','3', 'Local','2', 'User','1', '1'), sysdate FROM tmp_roles ref WHERE ref.role_code IN ( SELECT ref2.role_code FROM tmp_roles ref2 MINUS SELECT stg.code FROM role stg ); End If; -- Return vRetCode; End; -- -- Function LoadServiceRoles (pCreateFlag In Varchar2 Default 'Y', pUpdateFlag In Varchar2 Default 'Y', pDeactivateFlag In Varchar2 Default 'N') Return Varchar2 As vRetCode Varchar2(20); -- Begin vRetCode := 'Error'; -- -- First Log and then do the work. After the work is done, logging conditions will fail INSERT INTO update_log (log_date, description) SELECT sysdate, 'Deactivated = ' || pDeactivateFlag || ' - ' || EMSA_IDM_STAGING_LOADER.GETSERVICECODEFROMID(srvrol.SERVICE_OBJECT_ID) || ' --> ' || EMSA_IDM_STAGING_LOADER.GETROLECODEFROMID(srvrol.ROLE_OBJECT_ID) FROM service_role srvrol WHERE (srvrol.SERVICE_OBJECT_ID, srvrol.ROLE_OBJECT_ID) IN ( SELECT stg.service_object_id, stg.role_object_id FROM service_role stg MINUS SELECT refs.object_id, refr.object_id FROM tmp_roles refsr, service refs, role refr WHERE refsr.service_code = refs.code AND refsr.role_code = refr.code ) AND srvrol.ACTIVE = '1'; -- If (pDeactivateFlag != 'N') Then UPDATE service_role srvrol SET srvrol.ACTIVE = '0', srvrol.LAST_CHANGED = sysdate WHERE (srvrol.SERVICE_OBJECT_ID, srvrol.ROLE_OBJECT_ID) IN ( SELECT stg.service_object_id, stg.role_object_id FROM service_role stg MINUS SELECT refs.object_id, refr.object_id FROM tmp_roles refsr, service refs, role refr WHERE refsr.service_code = refs.code AND refsr.role_code = refr.code ) AND srvrol.ACTIVE = '1'; End If; -- -- Log all eventual updates done to existing records INSERT INTO update_log (log_date, description) SELECT sysdate, 'Updated = ' || pUpdateFlag || ' - ' || EMSA_IDM_STAGING_LOADER.GETSERVICECODEFROMID(srvrol.SERVICE_OBJECT_ID) || ' --> ' || EMSA_IDM_STAGING_LOADER.GETROLECODEFROMID(srvrol.ROLE_OBJECT_ID) from service_role srvrol WHERE srvrol.ACTIVE = '0' AND (srvrol.service_object_id, srvrol.role_object_id) IN (SELECT EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(rols.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(rols.ROLE_CODE) FROM tmp_roles rols ); -- If (pUpdateFlag != 'N') Then UPDATE service_role srvrol SET srvrol.ACTIVE = '1', srvrol.LAST_CHANGED = sysdate WHERE srvrol.ACTIVE = '0' AND (srvrol.service_object_id, srvrol.role_object_id) IN (SELECT EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(rols.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(rols.ROLE_CODE) FROM tmp_roles rols ); End If; -- -- Log all new profiles that are to be created INSERT INTO update_log (log_date, description) SELECT sysdate, 'Created = ' || pCreateFlag || ' - ' || ref.SERVICE_CODE || ' --> ' || ref.ROLE_CODE FROM tmp_roles ref WHERE (EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(ref.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref.ROLE_CODE)) IN ( SELECT EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(ref2.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref2.ROLE_CODE) FROM tmp_roles ref2 MINUS SELECT stg.service_object_id, stg.role_object_id FROM service_role stg ); -- If (pCreateFlag = 'Y') Then INSERT INTO service_role (service_object_id, role_object_id, active, last_changed) SELECT EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(ref.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref.ROLE_CODE), '1', sysdate FROM tmp_roles ref WHERE (EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(ref.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref.ROLE_CODE)) IN ( SELECT EMSA_IDM_STAGING_LOADER.GETSERVICEIDFROMCODE(ref2.SERVICE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref2.ROLE_CODE) FROM tmp_roles ref2 MINUS SELECT stg.service_object_id, stg.role_object_id FROM service_role stg ); End If; -- Return vRetCode; End; -- -- Function LoadProfileRoles (pCreateFlag In Varchar2 Default 'Y', pUpdateFlag In Varchar2 Default 'Y', pDeactivateFlag In Varchar2 Default 'N') Return Varchar2 As vRetCode Varchar2(20); -- Begin vRetCode := 'Error'; -- -- First Log and then do the work. After the work is done, logging conditions will fail INSERT INTO update_log (log_date, description) SELECT sysdate, 'Deactivated = ' || pDeactivateFlag || ' - ' || EMSA_IDM_STAGING_LOADER.GETProfileCODEFROMID(prfrol.PROFILE_OBJECT_ID) || ' --> ' || EMSA_IDM_STAGING_LOADER.GETROLECODEFROMID(prfrol.ROLE_OBJECT_ID) FROM profile_role prfrol WHERE (prfrol.PROFILE_OBJECT_ID, prfrol.ROLE_OBJECT_ID) IN ( SELECT stg.profile_object_id, stg.role_object_id FROM profile_role stg MINUS SELECT refp.object_id, refr.object_id FROM tmp_profile_role refpr, profile refp, role refr WHERE refpr.profile_code = refp.code AND refpr.role_code = refr.code ) AND prfrol.ACTIVE = '1'; -- If (pDeactivateFlag != 'N') Then UPDATE profile_role prfrol SET prfrol.ACTIVE = '0', prfrol.LAST_CHANGED = sysdate WHERE (prfrol.PROFILE_OBJECT_ID, prfrol.ROLE_OBJECT_ID) IN ( SELECT stg.profile_object_id, stg.role_object_id FROM profile_role stg MINUS SELECT refp.object_id, refr.object_id FROM tmp_profile_role refpr, profile refp, role refr WHERE refpr.profile_code = refp.code AND refpr.role_code = refr.code ) AND prfrol.ACTIVE = '1'; End If; -- -- Log all eventual updates done to existing records INSERT INTO update_log (log_date, description) SELECT sysdate, 'Updated = ' || pUpdateFlag || ' - ' || EMSA_IDM_STAGING_LOADER.GETPROFILECODEFROMID(prfrol.PROFILE_OBJECT_ID) || ' --> ' || EMSA_IDM_STAGING_LOADER.GETROLECODEFROMID(prfrol.ROLE_OBJECT_ID) from profile_role prfrol WHERE prfrol.ACTIVE = '0' AND (prfrol.profile_object_id, prfrol.role_object_id) IN (SELECT EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(rolp.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(rolp.ROLE_CODE) FROM tmp_profile_role rolp ); -- If (pUpdateFlag != 'N') Then UPDATE profile_role prfrol SET prfrol.ACTIVE = '1', prfrol.LAST_CHANGED = sysdate WHERE prfrol.ACTIVE = '0' AND (prfrol.profile_object_id, prfrol.role_object_id) IN (SELECT EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(rolp.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(rolp.ROLE_CODE) FROM tmp_profile_role rolp ); End If; -- -- Log all new profiles that are to be created INSERT INTO update_log (log_date, description) SELECT sysdate, 'Created = ' || pCreateFlag || ' - ' || ref.PROFILE_CODE || ' --> ' || ref.ROLE_CODE FROM tmp_profile_role ref WHERE (EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(ref.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref.ROLE_CODE)) IN ( SELECT EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(ref2.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref2.ROLE_CODE) FROM tmp_profile_role ref2 MINUS SELECT stg.profile_object_id, stg.role_object_id FROM profile_role stg ); -- If (pCreateFlag = 'Y') Then INSERT INTO profile_role (profile_object_id, role_object_id, active, last_changed) SELECT EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(ref.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref.ROLE_CODE), '1', sysdate FROM tmp_profile_role ref WHERE (EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(ref.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref.ROLE_CODE)) IN ( SELECT EMSA_IDM_STAGING_LOADER.GETPROFILEIDFROMCODE(ref2.PROFILE_CODE), EMSA_IDM_STAGING_LOADER.GETROLEIDFROMCODE(ref2.ROLE_CODE) FROM tmp_profile_role ref2 MINUS SELECT stg.profile_object_id, stg.role_object_id FROM profile_role stg ); End If; -- Return vRetCode; End; -- -- Function SetProfileRoleRelation (pProfile In Varchar2, pRole In Varchar2) Return Varchar2 As vProfId Profile.Object_Id%Type; vRoleId Role.Object_Id%Type; vRetCode Varchar2(20); -- Cursor cProf (cpProf Varchar2) Is SELECT object_id FROM profile WHERE code = cpProf; -- Cursor cRole (cpRole Varchar2) Is SELECT object_id FROM role WHERE code = cpRole; Begin vRetCode := 'Error'; -- vProfId := '--NotFound--'; Open cProf(pProfile); Fetch cProf Into vProfId; Close cProf; -- vRoleId := '--NotFound--'; Open cRole(pRole); Fetch cRole Into vRoleId; Close cRole; -- If ((vProfId != '--NotFound--') And (vRoleId != '--NotFound--')) Then INSERT INTO PROFILE_ROLE (Profile_Object_Id, Role_Object_Id, Active, Last_Changed) VALUES (vProfId, vRoleId, '1', Sysdate); -- vRetCode := 'OK'; End If; -- Return vRetCode; End; -- END EMSA_IDM_STAGING_LOADER;