SSOQ
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IDM_STAGING.EMSA_IDM_STAGING_INTERFACE
Parameters
Name
Type
Mode
Definition
PACKAGE BODY EMSA_IDM_STAGING_INTERFACE AS -- ***************************************************************************************************************************** -- FUNCTION GetServices -- ***************************************************************************************************************************** FUNCTION GetServices( code IN VARCHAR2, status IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Service infromation to be returned sqlQuery := 'SELECT s.CODE, s.DESCRIPTION, s.ACTIVE FROM SERVICE s '; -- Enhancement of the query to be used with the input criteria IF code IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(s.CODE) = UPPER(''' || code || ''')'; whereClause := 1; END IF; IF status IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(s.ACTIVE) = UPPER(''' || status || ''')'; whereClause := 1; ELSIF status IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(s.ACTIVE) = UPPER(''' || status || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetServices; -- ***************************************************************************************************************************** -- FUNCTION GetServices ends -- ***************************************************************************************************************************** -- ***************************************************************************************************************************** -- FUNCTION GetRoles -- ***************************************************************************************************************************** FUNCTION GetRoles( code IN VARCHAR2, status IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Role infromation to be returned sqlQuery := 'SELECT r.CODE, r.DESCRIPTION, r.ACTIVE, sl.SECLEVEL_DESC FROM ROLE r LEFT OUTER JOIN SECURITY_LEVEL sl ON r.SECURITY_LEVEL = sl.SECLEVEL_CODE '; -- Enhancement of the query to be used with the input criteria IF code IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(r.CODE) = UPPER(''' || code || ''')'; whereClause := 1; END IF; IF status IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(r.ACTIVE) = UPPER(''' || status || ''')'; whereClause := 1; ELSIF status IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(r.ACTIVE) = UPPER(''' || status || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetRoles; -- ***************************************************************************************************************************** -- FUNCTION GetRoles ends -- ***************************************************************************************************************************** -- ***************************************************************************************************************************** -- FUNCTION GetProfiles -- ***************************************************************************************************************************** FUNCTION GetProfiles( code IN VARCHAR2, status IN VARCHAR2, type IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Profile infromation to be returned sqlQuery := 'SELECT p.CODE, p.DESCRIPTION, p.ACTIVE, sl.SECLEVEL_DESC, p.HUMAN_SYSTEM FROM PROFILE p LEFT OUTER JOIN SECURITY_LEVEL sl ON p.SECURITY_LEVEL = sl.SECLEVEL_CODE '; -- Enhancement of the query to be used with the input criteria IF code IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(p.CODE) = UPPER(''' || code || ''')'; whereClause := 1; END IF; IF status IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(p.ACTIVE) = UPPER(''' || status || ''')'; whereClause := 1; ELSIF status IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(p.ACTIVE) = UPPER(''' || status || ''')'; END IF; IF type IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(p.HUMAN_SYSTEM) = UPPER(''' || type || ''')'; whereClause := 1; ELSIF type IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(p.HUMAN_SYSTEM) = UPPER(''' || type || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetProfiles; -- ***************************************************************************************************************************** -- FUNCTION GetProfiles ends -- ***************************************************************************************************************************** -- ***************************************************************************************************************************** -- FUNCTION GetOperations -- ***************************************************************************************************************************** FUNCTION GetOperations( code IN VARCHAR2, status IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Operation infromation to be returned sqlQuery := 'SELECT o.CODE, o.DESCRIPTION, o.ACTIVE FROM OPERATION o '; -- Enhancement of the query to be used with the input criteria IF code IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(o.CODE) = UPPER(''' || code || ''')'; whereClause := 1; END IF; IF status IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(o.ACTIVE) = UPPER(''' || status || ''')'; whereClause := 1; ELSIF status IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(o.ACTIVE) = UPPER(''' || status || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetOperations; -- ***************************************************************************************************************************** -- FUNCTION GetOperations ends -- ***************************************************************************************************************************** -- ***************************************************************************************************************************** -- FUNCTION GetRolesByProfile -- ***************************************************************************************************************************** FUNCTION GetRolesByProfile( profileCode IN VARCHAR2, roleStatus IN VARCHAR2, relationshipStatus IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Role infromation to be returned sqlQuery := 'SELECT r.CODE FROM PROFILE_ROLE pr LEFT OUTER JOIN ROLE r ON r.OBJECT_ID = pr.ROLE_OBJECT_ID LEFT OUTER JOIN PROFILE p ON p.OBJECT_ID = pr.PROFILE_OBJECT_ID '; -- Enhancement of the query to be used with the input criteria IF profileCode IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(p.CODE) = UPPER(''' || profileCode || ''')'; whereClause := 1; END IF; IF roleStatus IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(r.ACTIVE) = UPPER(''' || roleStatus || ''')'; whereClause := 1; ELSIF roleStatus IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(r.ACTIVE) = UPPER(''' || roleStatus || ''')'; END IF; IF relationshipStatus IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(pr.ACTIVE) = UPPER(''' || relationshipStatus || ''')'; whereClause := 1; ELSIF relationshipStatus IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(pr.ACTIVE) = UPPER(''' || relationshipStatus || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetRolesByProfile; -- ***************************************************************************************************************************** -- FUNCTION GetRolesByProfile ends -- ***************************************************************************************************************************** -- ***************************************************************************************************************************** -- FUNCTION GetProfilesByRole -- ***************************************************************************************************************************** FUNCTION GetProfilesByRole( roleCode IN VARCHAR2, profileStatus IN VARCHAR2, relationshipStatus IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Profile infromation to be returned sqlQuery := 'SELECT p.CODE FROM PROFILE_ROLE pr LEFT OUTER JOIN PROFILE p ON p.OBJECT_ID = pr.PROFILE_OBJECT_ID LEFT OUTER JOIN ROLE r ON r.OBJECT_ID = pr.ROLE_OBJECT_ID '; -- Enhancement of the query to be used with the input criteria IF roleCode IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(r.CODE) = UPPER(''' || roleCode || ''')'; whereClause := 1; END IF; IF profileStatus IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(p.ACTIVE) = UPPER(''' || profileStatus || ''')'; whereClause := 1; ELSIF profileStatus IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(p.ACTIVE) = UPPER(''' || profileStatus || ''')'; END IF; IF relationshipStatus IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(pr.ACTIVE) = UPPER(''' || relationshipStatus || ''')'; whereClause := 1; ELSIF relationshipStatus IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(pr.ACTIVE) = UPPER(''' || relationshipStatus || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetProfilesByRole; -- ***************************************************************************************************************************** -- FUNCTION GetProfilesByRole ends -- ***************************************************************************************************************************** -- ***************************************************************************************************************************** -- FUNCTION GetRolesByService -- ***************************************************************************************************************************** FUNCTION GetRolesByService( serviceCode IN VARCHAR2, roleStatus IN VARCHAR2, relationshipStatus IN VARCHAR2 ) RETURN SYS_REFCURSOR IS sqlQuery VARCHAR2(30000) := ''; whereClause INTEGER := 0; CURSOR_REF SYS_REFCURSOR; BEGIN -- The basic query with all the Role infromation to be returned sqlQuery := 'SELECT r.CODE FROM SERVICE_ROLE sr LEFT OUTER JOIN ROLE r ON r.OBJECT_ID = sr.ROLE_OBJECT_ID LEFT OUTER JOIN SERVICE s ON s.OBJECT_ID = sr.SERVICE_OBJECT_ID '; -- Enhancement of the query to be used with the input criteria IF serviceCode IS NOT NULL THEN sqlQuery := sqlQuery || ' WHERE UPPER(s.CODE) = UPPER(''' || serviceCode || ''')'; whereClause := 1; END IF; IF roleStatus IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(r.ACTIVE) = UPPER(''' || roleStatus || ''')'; whereClause := 1; ELSIF roleStatus IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(r.ACTIVE) = UPPER(''' || roleStatus || ''')'; END IF; IF relationshipStatus IS NOT NULL AND whereClause = '0' THEN sqlQuery := sqlQuery || ' WHERE UPPER(sr.ACTIVE) = UPPER(''' || relationshipStatus || ''')'; whereClause := 1; ELSIF relationshipStatus IS NOT NULL AND whereClause = '1' THEN sqlQuery := sqlQuery || ' AND UPPER(sr.ACTIVE) = UPPER(''' || relationshipStatus || ''')'; END IF; -- It opens the cursor OPEN CURSOR_REF FOR sqlQuery; RETURN CURSOR_REF; END GetRolesByService; -- ***************************************************************************************************************************** -- FUNCTION GetRolesByService ends -- ***************************************************************************************************************************** END EMSA_IDM_STAGING_INTERFACE;