SSOQ
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IDM_STAGING.NEW_PSC_DOMAIN
Parameters
Name
Type
Mode
P_DOM
VARCHAR2
IN
PP_DOM
VARCHAR2
IN
P_COUNTRY
VARCHAR2
IN
PCODE
VARCHAR2
IN
Definition
procedure new_psc_domain (p_dom in VARCHAR2, pp_dom in VARCHAR2, p_country in VARCHAR2, pcode in VARCHAR2) AS reu_id VARCHAR2(20); rad_id VARCHAr2(20); eucode VARCHAR2(50); adcode VARCHAR2(50); serid VARCHAR2(20); penid VARCHAR2(20); peu_id VARCHAR2(20); pad_id VARCHAR2(20); orgid VARCHAR2(20) := 'org9bb'; adm_id VARCHAR2(20) := 'rolefb'; eus_id VARCHAR2(20) := 'rolefc'; Begin -- ---- Role Section -- SELECT object_id INTO serid FROM service WHERE code = 'SRV_MAKCS'; -- SELECT object_id INTO penid FROM provisioning_endpoint WHERE code = 'LMS'; -- eucode := '_MAKCS_PSC_' || p_dom || '_' || upper(p_country) || '_END_USER'; adcode := '_MAKCS_PSC_' || p_dom || '_' || upper(p_country) || '_ADMIN'; -- INSERT INTO role (code, active, last_changed, security_level) VALUES ('ROL' || eucode, '1', sysdate, 1); -- INSERT INTO role (code, active, last_changed, security_level) VALUES ('ROL' || adcode, '1', sysdate, 1); -- SELECT object_id INTO reu_id FROM role WHERE code = 'ROL' || eucode; SELECT object_id INTO rad_id FROM role WHERE code = 'ROL' || adcode; -- INSERT INTO service_role (role_object_id, service_object_id, active, last_changed) VALUES (reu_id, serid, '1', sysdate); -- INSERT INTO service_role (role_object_id, service_object_id, active, last_changed) VALUES (rad_id, serid, '1', sysdate); -- INSERT INTO role_provisioning (role_object_id, provisioning_object_id, active, last_changed, code) VALUES (reu_id, penid, '1', sysdate, 'learner;' || pcode); -- INSERT INTO role_provisioning (role_object_id, provisioning_object_id, active, last_changed, code) VALUES (rad_id, penid, '1', sysdate, 'domainAdmin;' || pcode); -- -- ---- Profile Section -- INSERT INTO profile (description, code, active, human_system, last_changed, security_level) VALUES ('PSC - ' || pp_dom || ' - ' || p_country || ' - End User', 'PRF' || eucode, '1', '1', sysdate, 1); -- INSERT INTO profile (description, code, active, human_system, last_changed, security_level) VALUES ('PSC - ' || pp_dom || ' - ' || p_country || ' - Admin', 'PRF' || adcode, '1', '1', sysdate, 1); -- SELECT object_id INTO peu_id FROM profile WHERE code = 'PRF' || eucode; SELECT object_id INTO pad_id FROM profile WHERE code = 'PRF' || adcode; -- INSERT INTO profile_organization (profile_object_id, organization_object_id, active, last_changed) VALUES (peu_id, orgid, '1', sysdate); -- INSERT INTO profile_organization (profile_object_id, organization_object_id, active, last_changed) VALUES (pad_id, orgid, '1', sysdate); -- INSERT INTO profile_role (profile_object_id, role_object_id, active, last_changed) VALUES (peu_id, reu_id, '1', sysdate); -- Add EndUser LDAP INSERT INTO profile_role (profile_object_id, role_object_id, active, last_changed) VALUES (peu_id, eus_id, '1', sysdate); -- INSERT INTO profile_role (profile_object_id, role_object_id, active, last_changed) VALUES (pad_id, rad_id, '1', sysdate); -- Add Admin LDAP INSERT INTO profile_role (profile_object_id, role_object_id, active, last_changed) VALUES (pad_id, adm_id, '1', sysdate); -- End new_psc_domain;