SSOQ
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IDM_STAGING.CMC_MANAGEMENT
Parameters
Name
Type
Mode
Definition
PACKAGE BODY CMC_MANAGEMENT AS FUNCTION ViewModifications( objectID IN VARCHAR2, entity IN VARCHAR2 ) RETURN modificationTable AS response modificationTable; tempcursor SYS_REFCURSOR; BEGIN response := modificationTable(); CASE UPPER(entity) WHEN 'PROFILE' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id , code , description , active , long_description FROM IDM_STAGING.profile_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description , active , long_description )) MINUS SELECT * FROM ( SELECT object_id , code , description , active , long_description FROM IDM_STAGING.profile_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description , active , long_description)) ); WHEN 'ROLE' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id , code , description , active , long_description, TO_CHAR(security_level,'FM9') AS security_level FROM IDM_STAGING.role_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description , active , long_description, security_level )) MINUS SELECT * FROM ( SELECT object_id , code , description , active , long_description, TO_CHAR(security_level,'FM9') AS security_level FROM IDM_STAGING.role_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description , active , long_description, security_level )) ); WHEN 'SERVICE' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id , code , description, active , long_description FROM IDM_STAGING.service_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active , long_description )) MINUS SELECT * FROM ( SELECT object_id , code , description, active , long_description FROM IDM_STAGING.service_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active , long_description )) ); WHEN 'OPERATION' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id , code , description, active , long_description FROM IDM_STAGING.operation_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active , long_description )) MINUS SELECT * FROM ( SELECT object_id , code , description, active , long_description FROM IDM_STAGING.operation_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active , long_description )) ); WHEN 'COUNTRY' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id, active FROM IDM_STAGING.country_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( active )) MINUS SELECT * FROM ( SELECT object_id, active FROM IDM_STAGING.country_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( active )) ); WHEN 'ENDPOINT' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id , code , description, active , url , credentials FROM IDM_STAGING.provisioning_endpoint_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active , url , credentials )) MINUS SELECT * FROM ( SELECT object_id , code , description, active , url , credentials FROM IDM_STAGING.provisioning_endpoint_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active , url , credentials )) ); WHEN 'ORGANIZATION' THEN OPEN tempcursor FOR SELECT modificationType(object_id,propname,propvalue) FROM ( SELECT * FROM ( SELECT object_id , code , description, active FROM IDM_STAGING.organization_v WHERE object_id=objectID AND uncommitted = 1) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active )) MINUS SELECT * FROM ( SELECT object_id , code , description, active FROM IDM_STAGING.organization_v WHERE object_id=objectID AND uncommitted = 0) unpivot include nulls (PROPVALUE FOR PROPNAME IN( code , description, active )) ); END CASE; FETCH tempcursor bulk collect INTO response; RETURN response; END ViewModifications; FUNCTION RollbackRelationshipChanges (relationship IN relationshiptype) RETURN VARCHAR2 AS response BOOLEAN := TRUE; BEGIN DELETE FROM RELATIONSHIP_TRANSACTION WHERE RELATIONSHIP_OBJECT_ID =relationship.relationship_object_id; IF response THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END RollbackRelationshipChanges; FUNCTION RollbackEntityChanges (entity IN entityType , withRelationships IN BOOLEAN) RETURN VARCHAR2 AS response BOOLEAN := TRUE; BEGIN DELETE FROM ENTITY_TRANSACTION WHERE ENTITY_OBJECT_ID=entity.entity_object_id; IF (withRelationships) THEN DELETE FROM RELATIONSHIP_TRANSACTION WHERE FIRST_OBJECT_ID =entity.entity_object_id OR SECOND_OBJECT_ID=entity.entity_object_id; END IF; IF response THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END RollbackEntityChanges; FUNCTION isRelationshipInTransaction(relationship IN relationshiptype) RETURN BOOLEAN AS response BOOLEAN := FALSE; counter NUMBER; BEGIN SELECT COUNT(*) AS match_exists INTO counter FROM IDM_STAGING.relationship_transaction WHERE relationship_object_id = relationship.relationship_object_id; IF counter > 0 THEN response := true; END IF; RETURN response; END isRelationshipInTransaction; FUNCTION insertupdatetransrelationship ( relationship IN relationshiptype) RETURN BOOLEAN AS response BOOLEAN := true; obj_id VARCHAR2(30); BEGIN MERGE INTO idm_staging.relationship_transaction et USING ( SELECT relationship.relationship_object_id id , relationship.first_object_id first_object_id, relationship.second_object_id second_object_id FROM dual ) tmp ON ( ( et.relationship_object_id = tmp.id ) OR ( et.first_object_id = tmp.first_object_id AND et.second_object_id = tmp.second_object_id ) ) WHEN MATCHED THEN UPDATE SET first_object_id = relationship.first_object_id , second_object_id = relationship.second_object_id, active = relationship.active , code = relationship.code , last_changed = SYSDATE WHEN NOT MATCHED THEN INSERT ( object_id , relationship , relationship_object_id, first_object_id , second_object_id , active , code , last_changed ) VALUES ( NULL , relationship.relationship , relationship.relationship_object_id, relationship.first_object_id , relationship.second_object_id , relationship.active , relationship.code , SYSDATE ); RETURN response; END insertupdatetransrelationship; FUNCTION CommitRelationshipChanges (relationship IN relationshiptype) RETURN VARCHAR2 AS response BOOLEAN := true; isRelationshipTran BOOLEAN := false; relationshipTable VARCHAR(30 CHAR) :=''; relationshipRow IDM_STAGING.relationship_transaction%rowtype; BEGIN isRelationshipTran := isRelationshipInTransaction(relationship); IF isRelationshipTran THEN --commit SELECT * INTO relationshipRow FROM relationship_transaction rt WHERE rt.relationship_object_id= relationship.relationship_object_id AND rownum = 1; END IF; if ( SUBSTR(relationshipRow.second_object_id, 1 ,3) = 'ent') OR ( SUBSTR(relationshipRow.first_object_id, 1 ,3) = 'ent') THEN RETURN 'FALSE'; END if; CASE UPPER(relationshipRow.relationship) WHEN 'PROFILE_ROLE' THEN MERGE INTO IDM_STAGING.profile_role et USING ( SELECT relationshipRow.relationship_object_id id FROM dual) tmp ON (et.object_id = tmp.id) WHEN MATCHED THEN UPDATE SET active =relationshipRow.active, last_changed = SYSDATE WHEN NOT MATCHED THEN INSERT ( object_id , profile_object_id, role_object_id , active , last_changed ) VALUES ( NULL , relationshipRow.first_object_id , relationshipRow.second_object_id, relationshipRow.active , SYSDATE ); WHEN 'PROFILE_ORGANIZATION' THEN MERGE INTO IDM_STAGING.profile_organization et USING ( SELECT relationshipRow.relationship_object_id id FROM dual) tmp ON (et.object_id = tmp.id) WHEN MATCHED THEN UPDATE SET active =relationshipRow.active, last_changed = SYSDATE WHEN NOT MATCHED THEN INSERT ( object_id , profile_object_id , organization_object_id, active , last_changed ) VALUES ( NULL , relationshipRow.first_object_id , relationshipRow.second_object_id, relationshipRow.active , SYSDATE ); WHEN 'SERVICE_ROLE' THEN MERGE INTO IDM_STAGING.service_role et USING ( SELECT relationshipRow.relationship_object_id id FROM dual) tmp ON (et.object_id = tmp.id) WHEN MATCHED THEN UPDATE SET active =relationshipRow.active, last_changed = SYSDATE WHEN NOT MATCHED THEN INSERT ( object_id , service_object_id, role_object_id , active , last_changed ) VALUES ( NULL , relationshipRow.first_object_id , relationshipRow.second_object_id, relationshipRow.active , SYSDATE ); WHEN 'OPERATION_ORGANIZATION' THEN MERGE INTO IDM_STAGING.operation_organization et USING ( SELECT relationshipRow.relationship_object_id id FROM dual) tmp ON (et.object_id = tmp.id) WHEN MATCHED THEN UPDATE SET active =relationshipRow.active, last_changed = SYSDATE WHEN NOT MATCHED THEN INSERT ( object_id , operation_object_id , organization_object_id, active , last_changed ) VALUES ( NULL , relationshipRow.first_object_id , relationshipRow.second_object_id, relationshipRow.active , SYSDATE ); WHEN 'ROLE_PROVISIONING' THEN MERGE INTO IDM_STAGING.role_provisioning et USING ( SELECT relationshipRow.relationship_object_id id FROM dual) tmp ON (et.object_id = tmp.id) WHEN MATCHED THEN UPDATE SET active =relationshipRow.active, code = relationshipRow.code , last_changed = SYSDATE WHEN NOT MATCHED THEN INSERT ( object_id , role_object_id , provisioning_object_id, active , code , last_changed ) VALUES ( NULL , relationshipRow.first_object_id , relationshipRow.second_object_id, relationshipRow.active , relationshipRow.code , SYSDATE ); ELSE response:=false; END CASE; IF response THEN DELETE FROM IDM_STAGING.relationship_transaction rt WHERE rt.relationship_object_id= relationship.relationship_object_id; commit; RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END CommitRelationshipChanges; -- ***************************************************************************************************************************** -- FUNCTION insertrelationship -- ***************************************************************************************************************************** FUNCTION insertrelationship ( relationship IN relationshiptype, commitimmediate IN BOOLEAN ) RETURN BOOLEAN AS response BOOLEAN := true; isRelationshipTran BOOLEAN:= false; stringresponse VARCHAR2(100 CHAR); BEGIN isRelationshipTran := isRelationshipInTransaction(relationship); IF (commitimmediate and not(isRelationshipTran) )THEN CASE UPPER(relationship.relationship) WHEN 'PROFILE_ROLE' THEN INSERT INTO IDM_STAGING.profile_role ( object_id, profile_object_id, role_object_id, active, last_changed ) VALUES ( NULL, relationship.first_object_id, relationship.second_object_id, relationship.active, SYSDATE ); WHEN 'PROFILE_ORGANIZATION' THEN INSERT INTO IDM_STAGING.profile_organization ( object_id, profile_object_id, organization_object_id, active, last_changed ) VALUES ( NULL, relationship.first_object_id, relationship.second_object_id, relationship.active, SYSDATE ); WHEN 'SERVICE_ROLE' THEN INSERT INTO IDM_STAGING.service_role ( object_id, service_object_id, role_object_id, active, last_changed ) VALUES ( NULL, relationship.first_object_id, relationship.second_object_id, relationship.active, SYSDATE ); WHEN 'OPERATION_ORGANIZATION' THEN INSERT INTO IDM_STAGING.operation_organization ( object_id, operation_object_id, organization_object_id, active, last_changed ) VALUES ( NULL, relationship.first_object_id, relationship.second_object_id, relationship.active, SYSDATE ); WHEN 'ROLE_PROVISIONING' THEN INSERT INTO IDM_STAGING.role_provisioning ( object_id, role_object_id, provisioning_object_id, active, code, last_changed ) VALUES ( NULL, relationship.first_object_id, relationship.second_object_id, relationship.active, relationship.code, SYSDATE ); END CASE; ELSIF (commitimmediate and isRelationshipTran ) THEN response:=InsertUpdateTransRelationship(relationship); stringresponse:=CommitRelationshipChanges(relationship); ELSE response:=InsertUpdateTransRelationship(relationship); END IF; RETURN response; END insertrelationship; -- Updates an already existing relationship with the latest information FUNCTION updaterelationship ( relationship IN relationshiptype, commitimmediate IN BOOLEAN ) RETURN BOOLEAN AS response BOOLEAN := true; BEGIN IF commitimmediate THEN CASE UPPER(relationship.relationship) WHEN 'PROFILE_ROLE' THEN UPDATE IDM_STAGING.profile_role SET active = relationship.active, last_changed = SYSDATE WHERE object_id = relationship.relationship_object_id; WHEN 'PROFILE_ORGANIZATION' THEN UPDATE IDM_STAGING.profile_organization SET active = relationship.active, last_changed = SYSDATE WHERE object_id = relationship.relationship_object_id; WHEN 'SERVICE_ROLE' THEN UPDATE IDM_STAGING.service_role SET active = relationship.active, last_changed = SYSDATE WHERE object_id = relationship.relationship_object_id; WHEN 'OPERATION_ORGANIZATION' THEN UPDATE IDM_STAGING.operation_organization SET active = relationship.active, last_changed = SYSDATE WHERE object_id = relationship.relationship_object_id; WHEN 'ROLE_PROVISIONING' THEN UPDATE IDM_STAGING.role_provisioning SET active = relationship.active, code = relationship.code, last_changed = SYSDATE WHERE object_id = relationship.relationship_object_id; END CASE; DELETE from IDM_STAGING.relationship_transaction rt where rt.relationship_object_id=relationship.relationship_object_id; ELSE response:= InsertUpdateTransRelationship( relationship); END IF; return response; END updaterelationship; -- Inserts or updates a relationship depending on if a relationship object id exist or not FUNCTION insertupdaterelationship ( relationship IN relationshiptype, commitimmediate IN BOOLEAN ) RETURN BOOLEAN AS response BOOLEAN := true; BEGIN IF relationship.relationship_object_id IS NULL THEN response := insertrelationship(relationship,commitimmediate); ELSE response := updaterelationship(relationship,commitimmediate); END IF; RETURN response; END insertupdaterelationship; FUNCTION validateRelationships ( entity IN entityType , relatedTable IN relationshipsTable ) RETURN BOOLEAN AS response boolean :=true; singlerelationship relationshiptype; BEGIN CASE entity.entity WHEN 'Profile' THEN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); if ( SUBSTR(singlerelationship.second_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; END LOOP; WHEN 'Role' THEN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); IF singlerelationship.relationship = 'role_provisioning' THEN if ( SUBSTR(singlerelationship.second_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; ELSE if ( SUBSTR(singlerelationship.first_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; END IF; END LOOP; WHEN 'Service' THEN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); if ( SUBSTR(singlerelationship.second_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; END LOOP; WHEN 'Operation' THEN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); if ( SUBSTR(singlerelationship.second_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; END LOOP; WHEN 'Country' THEN -- next release response :=true; WHEN 'Provisioning_Endpoint' THEN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); if ( SUBSTR(singlerelationship.first_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; END LOOP; WHEN 'Organization' THEN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); if ( SUBSTR(singlerelationship.first_object_id, 1 ,3) = 'ent') THEN response:=false; exit; END if; END LOOP; WHEN 'Security_Level' THEN response:=true ; END CASE; RETURN response; END validateRelationships; FUNCTION InsertUpdateTransactionEntity( entity IN entityType) RETURN BOOLEAN AS response BOOLEAN:=true; BEGIN merge into IDM_STAGING.entity_transaction et using (select entity.entity_object_id id from dual) tmp on (et.entity_object_id = tmp.id) when matched then update set description=entity.description, code=entity.code, active=entity.active, human_system=entity.human_system, last_changed=SYSDATE, long_description=entity.long_description, security_level=entity.security_level, country_id=entity.country_id, authority_type=entity.authority_type, authority_type_id=entity.authority_type_id, alphacode2=entity.alpha2code, alphacode3=entity.alpha3code, url=entity.url, credentials=entity.credentials when not matched then INSERT ( object_id, entity_object_id, description, code, active, human_system, last_changed, long_description, security_level, country_id, authority_type, authority_type_id, alphacode2, alphacode3, url, credentials, entity ) VALUES ( NULL, entity.entity_object_id, entity.description, entity.code, entity.active, entity.human_system, SYSDATE, entity.long_description, entity.security_level, entity.country_id, entity.authority_type, entity.authority_type_id, entity.alpha2code, entity.alpha3code, entity.url, entity.credentials, entity.entity ) ; return response; END InsertUpdateTransactionEntity; FUNCTION isEntityInTransaction(entity IN entityType) RETURN BOOLEAN AS response BOOLEAN := FALSE; counter NUMBER; BEGIN SELECT COUNT(*) AS match_exists INTO counter FROM IDM_STAGING.entity_transaction WHERE entity_object_id = entity.entity_object_id; IF counter > 0 THEN response := true; END IF; RETURN response; END isEntityInTransaction; FUNCTION FineCommitEntityChanges ( object_id IN VARCHAR2, columnName IN VARCHAR2 ) RETURN VARCHAR2 AS isInTransactions BOOLEAN := false; response VARCHAR2(100 CHAR) := 'FALSE'; updateQuery VARCHAR2 (2000 CHAR) :=''; entityRow entity_transaction%rowtype; selectQuery VARCHAR2 (1000 CHAR):=''; entity entityType; BEGIN entity := entityType(object_id,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL, NULL, NULL,NULL); isInTransactions := isEntityInTransaction(entity); IF (isInTransactions) THEN SELECT * INTO entityRow FROM IDM_STAGING.entity_transaction ent WHERE ent.entity_object_id = entity.entity_object_id AND rownum = 1; selectQuery := '(SELECT ' || columnName ||' from entity_transaction where entity_object_id = ''' ||object_id || ''' )'; updateQuery := 'UPDATE ' ||entityRow.entity ||' set ' ||columnName || ' = ' || selectQuery || ' where object_id = ''' ||object_id || ''' '; EXECUTE IMMEDIATE updateQuery; COMMIT; response:='TRUE'; END IF; RETURN response; END FineCommitEntityChanges; FUNCTION replaceTransactionID( old_id VARCHAR2, new_id VARCHAR2) RETURN BOOLEAN AS response BOOLEAN := TRUE; BEGIN UPDATE IDM_STAGING.relationship_transaction rt SET rt.first_object_id = new_id WHERE rt.first_object_id = old_id; UPDATE IDM_STAGING.relationship_transaction rt SET rt.second_object_id = new_id WHERE rt.second_object_id = old_id; RETURN response; END replaceTransactionID; FUNCTION hasEntityUncRelEntities ( entity IN entityType ) RETURN BOOLEAN AS counter NUMBER; BEGIN SELECT COUNT(*) INTO counter FROM IDM_STAGING.relationship_transaction WHERE ( first_object_id = entity.entity_object_id AND SUBSTR(second_object_id, 1 ,3) = 'ent' ) OR ( second_object_id = entity.entity_object_id AND SUBSTR(first_object_id, 1 ,3) = 'ent' ); RETURN (counter>0); END hasEntityUncRelEntities; FUNCTION CommitEntityChanges ( entity IN entityType , withRelationships IN BOOLEAN ) RETURN VARCHAR2 AS response BOOLEAN := FALSE; tempcheck VARCHAR2(100 CHAR); singlerelationship relationshiptype; alreadyexists BOOLEAN := true; isInTransactions BOOLEAN := false; entityRow IDM_STAGING.entity_transaction%rowtype; obj_id VARCHAR2(30 CHAR); BEGIN IF hasEntityUncRelEntities(entity) AND withRelationships THEN RETURN 'UNCOMMITTED_RELATED_ENTITIES'; END IF; isInTransactions := isEntityInTransaction(entity); IF (isInTransactions) THEN SELECT * INTO entityRow FROM IDM_STAGING.entity_transaction ent WHERE entity.entity_object_id=ent.entity_object_id AND rownum = 1; IF ( SUBSTR(entityRow.entity_object_id, 1 ,3) = 'ent') THEN CASE UPPER(entityRow.entity) WHEN 'PROFILE' THEN INSERT INTO IDM_STAGING.profile ( object_id , description , code , active , human_system , last_changed , long_description ) VALUES ( NULL , entityRow.description , entityRow.code , entityRow.active , entityRow.human_system , SYSDATE , entityRow.long_description ) RETURNING object_id INTO obj_id; WHEN 'ROLE' THEN INSERT INTO IDM_STAGING.role ( object_id , description , code , active , last_changed , long_description, security_level ) VALUES ( NULL , entityRow.description , entityRow.code , entityRow.active , SYSDATE , entityRow.long_description, entityRow.security_level ) RETURNING object_id INTO obj_id; WHEN 'SERVICE' THEN INSERT INTO IDM_STAGING.service ( object_id , description , code , active , last_changed, long_description ) VALUES ( NULL , entityRow.description, entityRow.code , entityRow.active , SYSDATE , entityRow.long_description ) RETURNING object_id INTO obj_id; WHEN 'OPERATION' THEN INSERT INTO IDM_STAGING.operation ( object_id , description , code , active , last_changed, long_description ) VALUES ( NULL , entityRow.description, entityRow.code , entityRow.active , SYSDATE , entityRow.long_description ) RETURNING object_id INTO obj_id; WHEN 'COUNTRY' THEN dbms_output.put_line('Can not create country'); WHEN 'PROVISIONING_ENDPOINT' THEN INSERT INTO IDM_STAGING.provisioning_endpoint ( object_id , description , code , active , last_changed, url , credentials ) VALUES ( NULL , entityRow.description, entityRow.code , entityRow.active , SYSDATE , entityRow.url , entityRow.credentials ) RETURNING object_id INTO obj_id; WHEN 'ORGANIZATION' THEN dbms_output.put_line('Can not create Organization'); WHEN -- next release 'SECURITY_LEVEL' THEN dbms_output.put_line('Security_Level'); END CASE; response:= replaceTransactionID(entity.entity_object_id,obj_id); if (withRelationships) THEN FOR i IN ( SELECT * FROM IDM_STAGING.relationship_transaction rt WHERE rt.first_object_id =obj_id OR rt.second_object_id=obj_id ) LOOP singlerelationship := relationshipType(NULL,i.relationship_object_id,NULL,NULL,NULL,NULL,NULL); tempcheck := CommitRelationshipChanges(singlerelationship); END LOOP; END IF; -- withRelationships block -- commit relationships response:=true; ELSE CASE UPPER(entityRow.entity) WHEN 'PROFILE' THEN UPDATE IDM_STAGING.PROFILE SET description = entityRow.description , code = entityRow.code , active = entityRow.active , human_system = entityRow.human_system , last_changed = SYSDATE , long_description = entityRow.long_description WHERE object_id = entityRow.entity_object_id; WHEN 'ROLE' THEN UPDATE IDM_STAGING.ROLE SET description = entityRow.description , code = entityRow.code , active = entityRow.active , last_changed = SYSDATE , long_description = entityRow.long_description, security_level = entityRow.security_level WHERE object_id = entityRow.entity_object_id; WHEN 'SERVICE' THEN UPDATE IDM_STAGING.SERVICE SET description = entityRow.description, code = entityRow.code , active = entityRow.active , last_changed = SYSDATE , long_description = entityRow.long_description WHERE object_id = entityRow.entity_object_id; WHEN 'OPERATION' THEN UPDATE IDM_STAGING.OPERATION SET description = entityRow.description, code = entityRow.code , active = entityRow.active , last_changed = SYSDATE , long_description = entityRow.long_description WHERE object_id = entityRow.entity_object_id; WHEN 'COUNTRY' THEN UPDATE IDM_STAGING.COUNTRY SET active = entityRow.active, last_changed = SYSDATE WHERE object_id = entityRow.entity_object_id; WHEN 'PROVISIONING_ENDPOINT' THEN UPDATE IDM_STAGING.PROVISIONING_ENDPOINT SET description = entityRow.description, code = entityRow.code , active = entityRow.active , url = entityRow.url , credentials = entityRow.credentials, last_changed = SYSDATE WHERE object_id = entityRow.entity_object_id; WHEN 'ORGANIZATION' THEN UPDATE IDM_STAGING.ORGANIZATION SET active = entityRow.active, last_changed = SYSDATE WHERE object_id = entityRow.entity_object_id; WHEN 'SECURITY_LEVEL' THEN dbms_output.put_line('Security_Level'); END CASE; -- commit relationships if withRelationships THEN FOR i IN ( SELECT * FROM IDM_STAGING.relationship_transaction rt WHERE rt.first_object_id =entityRow.entity_object_id OR rt.second_object_id=entityRow.entity_object_id ) LOOP singlerelationship := relationshipType(NULL,i.relationship_object_id,NULL,NULL,NULL,NULL,NULL); tempcheck := CommitRelationshipChanges(singlerelationship); END LOOP; END IF; -- withRelationships block response:=true; obj_id:=entityRow.entity_object_id; END IF; END IF; IF response THEN DELETE FROM IDM_STAGING.entity_transaction ent WHERE entity.entity_object_id=ent.entity_object_id; commit; RETURN obj_id; ELSE RETURN 'FALSE'; END IF; END CommitEntityChanges; -- ***************************************************************************************************************************** -- FUNCTION InsertUpdateRelatedTable -- ***************************************************************************************************************************** FUNCTION InsertUpdateRelatedTable (relatedTable IN relationshipsTable , commitImmediate IN BOOLEAN ) RETURN BOOLEAN AS response BOOLEAN := true; tempcheck BOOLEAN; singlerelationship relationshiptype; BEGIN FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); tempcheck := insertupdaterelationship(singlerelationship,commitImmediate); IF NOT tempcheck THEN response := false; EXIT; END IF; END LOOP; RETURN response; END InsertUpdateRelatedTable; -- ***************************************************************************************************************************** -- FUNCTION ModifyEntity -- ***************************************************************************************************************************** FUNCTION ModifyEntity ( entity IN entityType , relatedTable IN relationshipsTable , commitImmediate IN BOOLEAN ) RETURN VARCHAR2 AS response BOOLEAN := TRUE; tempcheck BOOLEAN; singlerelationship relationshiptype; alreadyexists BOOLEAN := true; isInTransactions BOOLEAN := false; responseString VARCHAR2(100 CHAR); obj_id VARCHAR2(30 CHAR) ; BEGIN isInTransactions := isEntityInTransaction(entity); IF commitimmediate THEN IF NOT ( validateRelationships(entity,relatedTable) ) THEN RETURN 'UNCOMMITTED_RELATED_ENTITIES'; END IF; END IF; IF commitimmediate and not(isInTransactions) THEN CASE upper(entity.entity) WHEN 'PROFILE' THEN UPDATE IDM_STAGING.PROFILE SET description = entity.description , code = entity.code , active = entity.active , human_system = entity.human_system , last_changed = SYSDATE , long_description = entity.long_description WHERE object_id = entity.entity_object_id; WHEN 'ROLE' THEN UPDATE IDM_STAGING.ROLE SET description = entity.description , code = entity.code , active = entity.active , last_changed = SYSDATE , long_description = entity.long_description, security_level = entity.security_level WHERE object_id = entity.entity_object_id; WHEN 'SERVICE' THEN UPDATE IDM_STAGING.SERVICE SET description = entity.description, code = entity.code , active = entity.active , last_changed = SYSDATE , long_description = entity.long_description WHERE object_id = entity.entity_object_id; WHEN 'OPERATION' THEN UPDATE IDM_STAGING.OPERATION SET description = entity.description, code = entity.code , active = entity.active , last_changed = SYSDATE , long_description = entity.long_description WHERE object_id = entity.entity_object_id; WHEN 'COUNTRY' THEN UPDATE IDM_STAGING.COUNTRY SET active = entity.active, last_changed = SYSDATE WHERE object_id = entity.entity_object_id; WHEN 'PROVISIONING_ENDPOINT' THEN UPDATE IDM_STAGING.PROVISIONING_ENDPOINT SET description = entity.description, code = entity.code , active = entity.active , url = entity.url , credentials = entity.credentials, last_changed = SYSDATE WHERE object_id = entity.entity_object_id; WHEN 'ORGANIZATION' THEN UPDATE IDM_STAGING.ORGANIZATION SET active = entity.active, last_changed = SYSDATE WHERE object_id = entity.entity_object_id; WHEN 'SECURITY_LEVEL' THEN UPDATE IDM_STAGING.SECURITY_LEVEL SET SECLEVEL_DESC = entity.description WHERE SECLEVEL_CODE = entity.entity_object_id; END CASE; obj_id:= entity.entity_object_id; response:= InsertUpdateRelatedTable(relatedtable,true); ELSIF commitimmediate and isInTransactions THEN response:= InsertUpdateRelatedTable(relatedtable,false); response:= InsertUpdateTransactionEntity(entity) AND response; obj_id:= CommitEntityChanges(entity,true); ELSE -- IF NOT IN TRANSACTION AND NOT COMMIT IMMEDIATELY response:= InsertUpdateRelatedTable(relatedtable,false); response:= InsertUpdateTransactionEntity(entity) AND response; obj_id:= entity.entity_object_id; END IF; IF response THEN commit; RETURN obj_id; ELSE RETURN ''; END IF; EXCEPTION WHEN dup_val_on_index THEN RETURN ''; END ModifyEntity; -- ***************************************************************************************************************************** -- FUNCTION codeExists -- ***************************************************************************************************************************** FUNCTION codeExists( entity IN entitytype, commitimmediate IN BOOLEAN ) RETURN BOOLEAN AS response NUMBER := 0; BEGIN CASE entity.entity WHEN 'Profile' THEN SELECT COUNT(*) AS match_exists INTO response FROM dual WHERE EXISTS ( SELECT * FROM IDM_STAGING.profile_v WHERE code = entity.code ); WHEN 'Role' THEN SELECT COUNT(*) AS match_exists INTO response FROM dual WHERE EXISTS ( SELECT * FROM IDM_STAGING.role_v WHERE code = entity.code ); WHEN 'Service' THEN SELECT COUNT(*) AS match_exists INTO response FROM dual WHERE EXISTS ( SELECT * FROM IDM_STAGING.service_v WHERE code = entity.code ); WHEN 'Operation' THEN SELECT COUNT(*) AS match_exists INTO response FROM dual WHERE EXISTS ( SELECT * FROM IDM_STAGING.operation_v WHERE code = entity.code ); WHEN 'Country' THEN response := 0; WHEN 'Provisioning_Endpoint' THEN SELECT COUNT(*) AS match_exists INTO response FROM dual WHERE EXISTS ( SELECT * FROM IDM_STAGING.provisioning_endpoint_v WHERE code = entity.code ); WHEN 'Organization' THEN SELECT COUNT(*) AS match_exists INTO response FROM dual WHERE EXISTS ( SELECT * FROM IDM_STAGING.organization_v WHERE code = entity.code ); WHEN 'Security_Level' THEN response := 0; END CASE; IF response > 0 THEN RETURN true; ELSE RETURN false; END IF; END codeExists; -- ***************************************************************************************************************************** -- FUNCTION createentity -- ***************************************************************************************************************************** FUNCTION createentity ( entity IN entitytype, relatedtable IN relationshipstable, commitimmediate IN BOOLEAN ) RETURN VARCHAR2 AS response BOOLEAN := true; tempcheck BOOLEAN := true; singlerelationship relationshiptype; obj_id service.object_id%type; -- Change with entity transaction object id updatedtable relationshipstable := relationshipstable (); alreadyexists BOOLEAN := true; BEGIN IF commitimmediate then if not(validateRelationships(entity,relatedTable)) then return 'UNCOMMITTED_RELATED_ENTITIES'; END IF; END IF; CASE UPPER(entity.entity) WHEN 'PROFILE' THEN IF commitimmediate THEN INSERT INTO IDM_STAGING.profile ( object_id, description, code, active, human_system, last_changed, long_description ) VALUES ( NULL, entity.description, entity.code, entity.active, entity.human_system, SYSDATE, entity.long_description ) RETURNING object_id INTO obj_id; ELSE INSERT INTO IDM_STAGING.entity_transaction ( object_id, description, code, active, human_system, last_changed, created_on, long_description, entity ) VALUES ( NULL, entity.description, entity.code, entity.active, entity.human_system, SYSDATE, SYSDATE, entity.long_description, entity.entity ) RETURNING object_id INTO obj_id; UPDATE IDM_STAGING.entity_transaction SET ENTITY_OBJECT_ID = obj_id WHERE object_id = obj_id; END IF; updatedtable.extend(relatedtable.count); FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); singlerelationship.first_object_id := obj_id; updatedtable(relationship) := singlerelationship; END LOOP; WHEN 'ROLE' THEN IF commitimmediate THEN INSERT INTO IDM_STAGING.role ( object_id, description, code, active, last_changed, long_description, security_level ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, entity.long_description, entity.security_level ) RETURNING object_id INTO obj_id; ELSE INSERT INTO IDM_STAGING.entity_transaction ( object_id, description, code, active, last_changed, created_on, long_description, security_level, entity ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, SYSDATE, entity.long_description, entity.security_level, entity.entity ) RETURNING object_id INTO obj_id; UPDATE IDM_STAGING.entity_transaction SET ENTITY_OBJECT_ID = obj_id WHERE object_id = obj_id; END IF; updatedtable.extend(relatedtable.count); FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); IF UPPER(singlerelationship.relationship) = 'ROLE_PROVISIONING' THEN singlerelationship.first_object_id := obj_id; ELSE singlerelationship.second_object_id := obj_id; END IF; updatedtable(relationship) := singlerelationship; END LOOP; WHEN 'SERVICE' THEN IF commitimmediate THEN INSERT INTO IDM_STAGING.service ( object_id, description, code, active, last_changed, long_description ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, entity.long_description ) RETURNING object_id INTO obj_id; ELSE INSERT INTO IDM_STAGING.entity_transaction ( object_id, description, code, active, last_changed, created_on, long_description, entity ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, SYSDATE, entity.long_description, entity.entity ) RETURNING object_id INTO obj_id; UPDATE IDM_STAGING.entity_transaction SET ENTITY_OBJECT_ID = obj_id WHERE object_id = obj_id; END IF; updatedtable.extend(relatedtable.count); FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); singlerelationship.first_object_id := obj_id; updatedtable(relationship) := singlerelationship; END LOOP; WHEN 'OPERATION' THEN IF commitimmediate THEN INSERT INTO IDM_STAGING.operation ( object_id, description, code, active, last_changed, long_description ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, entity.long_description ) RETURNING object_id INTO obj_id; ELSE INSERT INTO IDM_STAGING.entity_transaction ( object_id, description, code, active, last_changed, created_on, long_description, entity ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, SYSDATE, entity.long_description, entity.entity ) RETURNING object_id INTO obj_id; UPDATE IDM_STAGING.entity_transaction SET ENTITY_OBJECT_ID = obj_id WHERE object_id = obj_id; END IF; updatedtable.extend(relatedtable.count); FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); singlerelationship.first_object_id := obj_id; updatedtable(relationship) := singlerelationship; END LOOP; WHEN 'COUNTRY' THEN dbms_output.put_line('Can not create country'); WHEN 'PROVISIONING_ENDPOINT' THEN IF commitimmediate THEN INSERT INTO IDM_STAGING.provisioning_endpoint ( object_id, description, code, active, last_changed, url, credentials ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, entity.url, entity.credentials ) RETURNING object_id INTO obj_id; ELSE INSERT INTO IDM_STAGING.entity_transaction ( object_id, description, code, active, last_changed, created_on, url, credentials, entity ) VALUES ( NULL, entity.description, entity.code, entity.active, SYSDATE, SYSDATE, entity.url, entity.credentials, entity.entity ) RETURNING object_id INTO obj_id; UPDATE IDM_STAGING.entity_transaction SET ENTITY_OBJECT_ID = obj_id WHERE object_id = obj_id; END IF; updatedtable.extend(relatedtable.count); FOR relationship IN 1..relatedtable.count LOOP singlerelationship := relatedtable(relationship); singlerelationship.second_object_id := obj_id; updatedtable(relationship) := singlerelationship; END LOOP; WHEN 'ORGANIZATION' THEN dbms_output.put_line('Can not create Organization'); WHEN -- next release 'SECURITY_LEVEL' THEN dbms_output.put_line('Security_Level'); END CASE; FOR relationship IN 1..updatedtable.count LOOP singlerelationship := updatedtable(relationship); tempcheck := insertupdaterelationship(singlerelationship,commitimmediate); IF NOT tempcheck THEN response := false; exit; END IF; END LOOP; IF response THEN commit; RETURN obj_id; ELSE RETURN ''; END IF; END createentity; -- ***************************************************************************************************************************** -- FUNCTION gettablezone -- ***************************************************************************************************************************** FUNCTION gettablezone ( tablename IN VARCHAR2 ) RETURN NUMBER AS response INTEGER := 0; BEGIN CASE WHEN UPPER(tablename) IN ( 'ROLE','ENDPOINT','SERVICE' ) THEN response := 1; WHEN UPPER(tablename) = 'PROFILE' THEN response := 2; WHEN UPPER(tablename) IN ( 'COUNTRY','ORGANIZATION','OPERATION' ) THEN response := 3; ELSE response :=0; END CASE; RETURN response; END gettablezone; -- ***************************************************************************************************************************** -- FUNCTION getrelationshipquery -- ***************************************************************************************************************************** FUNCTION getrelationshipquery (tablename IN VARCHAR2, showdisabledrelationships IN BOOLEAN) RETURN VARCHAR2 AS response VARCHAR2(1000 CHAR) := ''; BEGIN IF ( NOT ( showdisabledrelationships ) ) THEN CASE WHEN UPPER(tablename) IN ('ROLE','SERVICE','ENDPOINT') THEN response := '(sr.active = 1 or sr.active is null ) and (rpr.active = 1 or rpr.active is null) and '; WHEN UPPER(tablename) = 'PROFILE' THEN response := ' (pror.active = 1 or pror.active is null ) and ( prog.active = 1 or prog.active is null) and '; WHEN UPPER(tablename) IN ( 'COUNTRY','ORGANIZATION','OPERATION' ) THEN response := '( oporg.active = 1 or oporg.active is null ) and '; ELSE response :=''; END CASE; ELSE CASE WHEN UPPER(tablename) IN ('ROLE','SERVICE','ENDPOINT') THEN response := ' ( sr.active is not null or rpr.active is not null ) and '; WHEN UPPER(tablename) = 'PROFILE' THEN response := ' ( pror.active is not null or prog.active is not null) and '; WHEN UPPER(tablename) IN ( 'COUNTRY','ORGANIZATION','OPERATION' ) THEN response := ''; ELSE response :=''; END CASE; END IF; RETURN response; END getrelationshipquery; -- ***************************************************************************************************************************** -- FUNCTION getRowQueryValue -- Returns the Query value string -- ***************************************************************************************************************************** FUNCTION getRowQueryValue (row IN queryRowType ) RETURN VARCHAR2 AS response VARCHAR2(1000 CHAR) := ''; BEGIN IF UPPER(row.operator) = 'LIKE' THEN response := ' upper (''%' || row.searchvalue ||'%'') '; ELSIF UPPER(row.operator) = '<' THEN response := ' date ''' || row.searchvalue ||''' '; ELSIF UPPER(row.operator) = '>' THEN response := ' date ''' || row.searchvalue ||''' '; ELSE response := ' upper (''' || row.searchvalue ||''') '; END IF; RETURN response; END getRowQueryValue; -- ***************************************************************************************************************************** -- FUNCTION getRowQueryColumn -- Returns the column query string -- ***************************************************************************************************************************** FUNCTION getRowQueryColumn (tableprefix IN VARCHAR2 , row IN queryRowType ) RETURN VARCHAR2 AS response VARCHAR2(1000 CHAR) := ''; BEGIN IF UPPER(row.operator) = 'LIKE' THEN response := 'upper(' ||tableprefix ||'.' ||row.searchcolumn ||') '; ELSIF UPPER(row.operator) = '<' THEN response := '' ||tableprefix ||'.' ||row.searchcolumn ||' '; ELSIF UPPER(row.operator) = '>' THEN response := '' ||tableprefix ||'.' ||row.searchcolumn ||' '; ELSE response := 'upper(' ||tableprefix ||'.' ||row.searchcolumn ||') '; END IF; RETURN response; END getRowQueryColumn; -- ***************************************************************************************************************************** -- FUNCTION getWhereClause -- Creates the where clause with multiple columns -- ***************************************************************************************************************************** FUNCTION getWhereClause( tableprefix IN VARCHAR2 , rows IN queryTable) RETURN VARCHAR2 AS response VARCHAR2(4000 CHAR) := ''; counter NUMBER :=1; singlerow queryRowType; BEGIN FOR row IN 1..rows.count LOOP singlerow:=rows(row); response := response || getRowQueryColumn(tableprefix,singlerow) || singlerow.operator || getRowQueryValue(singlerow); IF row != rows.count THEN response := response ||' ' || singlerow.conjuction || ' '; END IF; END LOOP; RETURN response; END getWhereClause; -- ***************************************************************************************************************************** -- FUNCTION gettablequery -- Creates the sql query for a single table -- ***************************************************************************************************************************** FUNCTION gettablequery ( tablename IN VARCHAR2, rows IN queryTable , showdisabledrelationships IN BOOLEAN ) RETURN VARCHAR2 AS response VARCHAR2(1000 CHAR) := ''; subquery VARCHAR2(10000 CHAR); BEGIN CASE WHEN UPPER(tablename) = 'ROLE' THEN subquery := getWhereClause('ro',rows); response := response || 'where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select null service_objectid, ro.object_id role_object_id, null endpoint_object_id from IDM_STAGING.role_v ro where ' ||' ( ' || subquery ||' ) '; subquery:=''; WHEN UPPER(tablename) = 'ENDPOINT' THEN subquery := getWhereClause('pro',rows); response := response || 'where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select null service_objectid, null role_object_id, pro.OBJECT_ID endpoint_object_id from IDM_STAGING.provisioning_endpoint_v pro where ' ||' ( ' || subquery ||' ) '; subquery:=''; WHEN UPPER(tablename) = 'SERVICE' THEN subquery := getWhereClause('ser',rows); response := response || 'where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select ser.object_id service_objectid, null role_object_id, null endpoint_object_id from IDM_STAGING.service_v ser where ' ||' ( ' || subquery ||' ) '; subquery:=''; WHEN UPPER(tablename) = 'PROFILE' THEN subquery := getWhereClause('pro',rows); response := response || ' where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select null role_object_id,pro.OBJECT_ID profile_object_id,null organization_object_id' || ' from IDM_STAGING.profile_v pro where ' ||' ( ' || subquery ||' ) '; subquery:=''; WHEN UPPER(tablename) = 'ORGANIZATION' THEN subquery := getWhereClause('org',rows); response := response || 'where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select null operation_object_id, org.object_id organization_object_id , null country_object_id from IDM_STAGING.organization_v org where ' ||' ( ' || subquery ||' ) '; subquery:=''; WHEN UPPER(tablename) = 'OPERATION' THEN subquery := getWhereClause('op',rows); response := response || 'where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select op.object_id operation_object_id, null organization_object_id ,null country_object_id from IDM_STAGING.operation_v op where ' ||' ( ' || subquery ||' ) '; subquery:=''; WHEN UPPER(tablename) = 'COUNTRY' THEN subquery := getWhereClause('cou',rows); response := response || 'where ' ||getrelationshipquery(tablename,showdisabledrelationships) ||' ( ' || subquery ||' ) ' || 'union select null operation_object_id, null organization_object_id , cou.object_id country_object_id from IDM_STAGING.country_v cou where ' ||' ( ' || subquery ||' ) '; subquery:=''; ELSE response := ''; END CASE; RETURN response; END gettablequery; -- ***************************************************************************************************************************** -- FUNCTION simpletablesearch -- Creates a simple table search query for one queriedtable -- ***************************************************************************************************************************** FUNCTION simpletablesearch ( queriedtable IN VARCHAR2, rows IN queryTable , selectedtable IN VARCHAR2, showdisabledrelationships IN BOOLEAN ) RETURN VARCHAR2 AS selectedzone INTEGER := 0; queriedzone INTEGER := 0; direction VARCHAR2(20 CHAR) := ' left join'; r_sqlquery VARCHAR2(15000 CHAR); value VARCHAR2(200 CHAR); BEGIN queriedzone := gettablezone(queriedtable); selectedzone := gettablezone(selectedtable); IF ( queriedzone >= selectedzone ) AND ( queriedzone + selectedzone > 2 ) THEN direction := ' right join'; END IF; r_sqlquery := 'select distinct ' || selectedtable || '_object_id object_id from ( select zone1.service_object_id service_object_id,zone1.role_object_id role_object_id, zone1.endpoint_object_id endpoint_object_id,zone2.profile_object_id ,zone3.organization_object_id organization_object_id,zone3.operation_object_id operation_object_id, zone3.country_object_id country_object_id from '; IF queriedzone = 1 OR selectedzone = 1 THEN r_sqlquery := r_sqlquery || '( select ser.object_id service_object_id,ro.object_id role_object_id,pro.OBJECT_ID endpoint_object_id from IDM_STAGING.role_v ro left join IDM_STAGING.service_role_v sr on sr.role_object_id=ro.object_id left join IDM_STAGING.service_v ser on ser.object_id=sr.service_object_id left join IDM_STAGING.role_provisioning_v rpr on ro.object_id=rpr.role_object_id left join IDM_STAGING.provisioning_endpoint_v pro on rpr.provisioning_object_id=pro.OBJECT_ID '; IF ( queriedzone = 1 ) THEN r_sqlquery := r_sqlquery || gettablequery(queriedtable,rows, showdisabledrelationships); ELSE r_sqlquery := r_sqlquery || ' WHERE ' || getrelationshipquery('ROLE',showdisabledrelationships) || ' 1=1'; END IF; r_sqlquery := r_sqlquery || ') zone1'; ELSE r_sqlquery := r_sqlquery || ' (select null service_object_id,null role_object_id,null endpoint_object_id from DUAL ) zone1'; END IF; r_sqlquery := r_sqlquery || direction; IF ( queriedzone = 1 AND selectedzone = 1 ) OR ( queriedzone = 3 AND selectedzone = 3 ) THEN r_sqlquery := r_sqlquery || '(select null role_object_id,null profile_object_id,null organization_object_id from DUAL ) zone2 on 1=1'; ELSE r_sqlquery := r_sqlquery || ' ( select pror.role_object_id role_object_id,pro.OBJECT_ID profile_object_id,prog.organization_object_id organization_object_id from IDM_STAGING.profile_v pro left join IDM_STAGING.profile_role_v pror on pror.profile_object_id = pro.OBJECT_ID left join IDM_STAGING.profile_organization_v prog on pro.OBJECT_ID = prog.profile_object_id '; IF ( queriedzone = 2 ) THEN r_sqlquery := r_sqlquery || gettablequery(queriedtable,rows,showdisabledrelationships); ELSE r_sqlquery := r_sqlquery || ' WHERE ' || getrelationshipquery('PROFILE',showdisabledrelationships) || ' 1=1'; END IF; r_sqlquery := r_sqlquery || ' ) zone2 on'; IF ( queriedzone > 1 AND selectedzone > 1 ) THEN r_sqlquery := r_sqlquery || ' 1=1'; ELSE r_sqlquery := r_sqlquery || ' zone2.role_object_id = zone1.role_object_id'; END IF; END IF; r_sqlquery := r_sqlquery || direction; IF queriedzone < 3 AND selectedzone < 3 THEN r_sqlquery := r_sqlquery || '(select null operation_object_id,null organization_object_id,null country_object_id from DUAL ) zone3 on 1=1'; ELSE r_sqlquery := r_sqlquery || ' ( select op.object_id operation_object_id,org.object_id organization_object_id ,cou.object_id country_object_id from IDM_STAGING.organization_v org join IDM_STAGING.country_v cou on org.country_id=cou.object_id left join IDM_STAGING.operation_organization_v oporg on org.object_id = oporg.organization_object_id left join IDM_STAGING.operation_v op on op.object_id = oporg.operation_object_id '; IF ( queriedzone = 3 ) THEN r_sqlquery := r_sqlquery || gettablequery(queriedtable,rows,showdisabledrelationships); ELSE r_sqlquery := r_sqlquery || ' WHERE ' || getrelationshipquery('ORGANIZATION',showdisabledrelationships) || ' 1=1'; END IF; r_sqlquery := r_sqlquery || ' ) zone3 on zone2.organization_object_id = zone3.organization_object_id'; END IF; r_sqlquery := r_sqlquery || ') where ' || selectedtable || '_object_id is not null '; RETURN r_sqlquery; END simpletablesearch; -- ***************************************************************************************************************************** -- FUNCTION SearchStaging -- ***************************************************************************************************************************** FUNCTION SearchStaging ( rows IN queryTable , selectedtable IN VARCHAR2, showdisabledrelationships IN BOOLEAN ) RETURN objectidtable AS TYPE resulttype IS REF CURSOR; response objectidtable; results resulttype; simplequeryrows queryTable; singlerow queryRowType; r_sqlquery VARCHAR2(15000 CHAR); queriedtable VARCHAR2(30 CHAR); counter NUMBER :=0; rowcounter NUMBER :=1; BEGIN -- The rows should be ordered based on the queried table (It will work regardless but better if they are ordered) -- Create one simple query per queried table -- UNION or INTERSECT multiple simple queries -- EXECUTE the query -- RETURN the object ids of the results simplequeryrows :=queryTable(); -- Set the queried table from input queriedtable := rows(1).entity; FOR row IN 1..rows.count LOOP -- Assing the current row of the loop singlerow := rows(row); -- If the queried table is the same IF queriedtable = singlerow.entity THEN -- Extend the simple query rows simplequeryrows.extend(1); -- Add the current row to the simple query rows simplequeryrows(rowcounter) := singlerow; -- Increase the counter rowcounter := rowcounter+1; ELSE -- If the queried table has changed -- If the general counter is more than one IF ( counter > 0 ) THEN IF UPPER(singlerow.conjuction) = 'OR' THEN r_sqlquery := r_sqlquery || ' UNION '; ELSE r_sqlquery := r_sqlquery || ' INTERSECT '; END IF; END IF; -- Get the search query r_sqlquery := r_sqlquery || simpletablesearch(queriedtable,simplequeryrows,selectedtable, showdisabledrelationships ); -- change table queriedtable := singlerow.entity; -- increase the general counter counter :=counter+1; -- restart row counter rowcounter :=1; -- reset the query table simplequeryrows:=queryTable(); simplequeryrows.extend(1); -- add the current row simplequeryrows(rowcounter) := singlerow; -- increase the row counter rowcounter := rowcounter+1; END IF; END LOOP; -- If the general counter is more than one IF ( counter > 0 ) THEN IF UPPER(singlerow.conjuction) = 'OR' THEN r_sqlquery := r_sqlquery || ' UNION '; ELSE r_sqlquery := r_sqlquery || ' INTERSECT '; END IF; END IF; --set the query r_sqlquery := r_sqlquery || simpletablesearch(queriedtable,simplequeryrows,selectedtable, showdisabledrelationships ); --DBMS_OUTPUT.PUT_LINE('r_sqlquery = ' || r_sqlquery); OPEN results FOR r_sqlquery; FETCH results BULK COLLECT INTO response; CLOSE results; RETURN response; END SearchStaging; END CMC_MANAGEMENT;