This paper will explain how to build a system to manage user requirements information in Designer/2000. The following steps are necessary to accomplish this task:

1. Create new objects in Designer/2000 to support user requirements.
2. Create links to associate requirements with entities, functions, tables, and modules.
3. Create tables separate from the repository to hold the information about user requirements.
4. Create a form to manipulate the information in the local tables.
5. Using the API, create scripts to copy information from Designer/2000 to the local tables, and back again.

On the surface, the idea of copying the information back and forth between the repository and a set of local tables might not seem worth the effort. But after front ends are built that allow the user to maintain this information, the benefits become obvious. Once the system is in place, users can enter all of their changes without having to wait for Designer/2000 to commit them.

Another benefit to using local tables is that since the changes being made are not in the repository, anyone can be assigned the task of data entry. Any mistakes that were made can be found before the information is written to the repository. So, not only will the data entry be faster and easier, it can also be safer. These benefits will make the extra initial development time worth it.

The first step to creating this system is to draw the ERD. Not only will this help in creating the necessary objects in Designer/2000, but the tables that get generated will be the local tables that will be used.

The ERD should look like the drawing shown in Figure 1 below:

We will explain each entity beginning from the bottom of the diagram:

• Y_REQ_SOURCE holds types of requirement sources such as an interview or a legacy system.
• REQ_SOURCE holds sources for a requirement such as interview on Feb 2^nd, or legacy system XXX.
• Y_REQ_STATUS holds valid statuses for a requirement such as "Complete" or "In process."
• Y_REQUIREMENT holds types of requirements such as workflow.
• ASSOC_REQ holds all of the requirement types associated with a requirement.
• REQ_ACT holds all of the acts on a requirement.

The ENTITY, TABLE, MODULE and FUNCTION entities already exist in Designer/2000. Attribute these entities with any information you want to see about them in the Dev2k application system. The minimum list of attributes you will need from each of the above entities is as follows:

• ID
• NAME
• APPLICATION_SYSTEM_OWNED_BY

The ASSOC_ENTITY, ASSOC_TABLE, ASSOC_MODULE, and ASSOC_FUNCTION entities hold all of the requirements associated with the particular object.

This structure must be built in Designer/2000 with user extensions.

1. Elements – An object that will hold data. Element types will create all entities, except for the intersection entities.
2. Associations – A relationship between elements. All of the relationships will be created using one-to-many associations. All of the intersection entities will be created using many-to-many associations.
3. Text Objects – Any character field that needs to store more than 240 characters.

On the main Designer/2000 window, click on the Admin Utility button. Next, click on the Maintain User Extensibility button. The star next to the elements means that element has been published. Once it has been published, it can’t be deleted or changed. Therefore it is important to ensure that everything is correct before it is published.

Click on the new button. This will bring up the element type details window shown in Figure 2.

The Short Name is a unique identifier for the element. It has to be a value between E0 through E99. Oracle has already used E1, E2, E20, E3, E4, E5, and E6.

Name is the value that will be used when naming the API package.

Plural Name is the value that will be used when naming the API view.

Display Name is the value that will be displayed in the Matrix Diagrammer.

Displayed name will be used in the Matrix Diagrammer and the Repository Object Navigator, but the column in the view will be the name shown in the Property Name field. The rest of this window is self-explanatory.

Once all of the properties are created, you are ready to publish the new element. This is accomplished by clicking on the element and pressing the Publish button. This will create the DDL scripts that Designer/2000 will need to create the proper API objects. Once the elements are published, go back to the Admin Utility window, and press the Recreate button. Choose the Full Reconcile button and click on Start. This will create the API views and packages necessary to manipulate these new elements.

Choose association type radio button on the top of the screen. This will display the associations that currently exist. To create a new association click on the new button. The window shown in Figure 3 will appear:

There are two ways to design the application system. The applications could go against the repository directly, or they can be made to access a local table structure. We will discuss the costs and benefits of both.

Using Forms 5.0, a block can have functions that handle the insert, update, and delete logic. The block Property Palette is shown in Figure 4. With this new

functionality, it would be easy to create these front ends going directly against the repository. This will allow the user to avoid the time it takes to save each record in Designer/2000. However, this still does not address the problem of data entry mistakes. The user will also have to save to the repository multiple times in one session. The development time saved is not worth the lack of functionality.

Create the local table structure by generating the DDL from Designer/2000. The applications can be built based on this structure. Scripts can then be created to copy information from the repository to this structure. The users can use the applications to manipulate this data. They can even enter all of the necessary data without once touching the repository. This information can then be checked for errors before being written back to the repository. The following shows the code necessary to copy and write entity information.

PROCEDURE COPY_Entities(App_ID Number,Copy_Att Varchar2) IS

/* Copies all entities and their descriptions associated

|| with the application who’s ID gets passed in App_ID.

|| Copy_Att is either Y or N, depending on whether or not the user

|| wants to copy in attributes as well

|| J. Strano

|| Dulcian Inc.

*/

Current_ID Number(38); -- Current OID

/* Get all relevant information about the current entity */

Cursor c1 is

Select id,name

From ci_entities

Where Application_System_Owned_By = App_ID;

/* Entity Description */

Cursor c2 is

Select dsc_description

From sdd_descriptions

where dsc_type = 'CDIDSC'

and dsc_ref = Current_ID;

/* Generate the next available UID */

Cursor c3 is

select seq_entity.nextval

from dual;

Desc_Entity Varchar2(700); -- Piece together the description

entity_genid number(38); -- Hold the generated UID

BEGIN

/* Walk the entity table */

for c1ind in c1 loop

current_id := c1ind.id;

desc_entity := null;

/* Piece together the description */

for c2ind in c2 loop

Desc_Entity := Desc_Entity || c2ind.dsc_description;

end loop;

/* Generate the next available UID */

open c3;

fetch c3 into entity_genid;

close c3;

insert into entity values(entity_genid,c1ind.id,c1ind.name,LTRIM(desc_entity),app_id,'UNCHANGED','N');

/* Does the user want to copy attributes as well ? */

If Copy_Att = 'Y' Then

forms_ddl('COMMIT'); -- Need to commit so FK in attribute will recognize entity exists.

copy_attributes(Current_ID,Entity_GenID);

End If;

end loop;

forms_ddl('COMMIT');

END;

The Write To Repository procedure is shown below:

/* Walk the entity table */

For C1IND in C1 Loop

Cur_Ent_ID := C1Ind.Entity_UID;

New_Entity_ID := C1Ind.Entity_OID;

/* Should we delete the entity? */

If C1IND.Delete_Flag = 'Y' Then

Delete_Entity := 'Y';

API_Delete.Entity(C1Ind.Entity_OID,Cur_App_Name);

API_Delete.Comments(C1Ind.Entity_OID);

/* We have to delete all of the associated attributes first due to FK constraints */

Delete from Attribute where Entity_UID = C1Ind.Entity_UID;

/* If the entity is deleted, we don't want to do anything with

|| it's associated attributes but delete them. The API call

|| deleting the entity automatically deletes the attributes as

|| well. So I put the walk the attributes loop in the ELSE

|| clause of this IF statement. It appears after the INSERT

|| and UPDATE entity sections to follow.

*/

Else

/* Should we insert a new entity */

If C1Ind.Status = 'NEW' Then

API_Insert.Entity(C1Ind.Entity_Name,C1Ind.App_ID,Cur_App_Name);

/* Get the newly generated Entity OID */

Cur_Ent_Name := C1Ind.Entity_Name;

open c3;

fetch c3 into new_entity_id;

close c3;

API_Update.Add_Comments(New_Entity_ID,C1Ind.Comments);

Update Entity Set Entity_OID = new_entity_id,Status = 'UNCHANGED' where Entity_UID = Cur_Ent_ID;

Forms_DDL('Commit');

/* Update all changed records */

ElsIF C1Ind.Status = 'CHANGED' Then

API_Update.Entity(C1Ind.Entity_OID,C1Ind.Entity_Name,Cur_App_Name);

API_Update.Add_Comments(C1Ind.Entity_OID,C1Ind.Comments);

Update Entity Set Status = 'UNCHANGED' Where Entity_UID = Cur_Ent_ID;

End If;

PACKAGE BODY api_delete IS

/*

|| This package contains all of the procedures to delete items

|| from the repository.

|| J. Strano

|| Dulcian Inc

*/

procedure entity(in_id number,in_app_name varchar2) IS

act_status varchar2(255);

BEGIN

cdapi.initialize(in_app_name);

cdapi.open_activity;

cioentity.DEL(in_id);

cdapi.close_activity(act_status);

END;

PACKAGE BODY API_Insert IS

/*

|| This package contains all of the procedures for

|| inserting items into the repository.

|| The insertion of comments is handled by the

|| ADD_COMMENTS procedure located in API_UPDATE.

|| J. Strano

|| Dulcian Inc.

*/

Procedure Entity(in_Name Varchar2,in_app_id number,in_app_name varchar2) IS

Pass_ID Number;

R_Ent CIOEntity.Data;

act_status varchar2(255);

BEGIN

cdapi.initialize(in_app_name);

cdapi.open_activity;

R_Ent.v.name := in_name;

R_Ent.i.name := TRUE;

R_ent.v.Application_System_Owned_By := in_app_id;

R_Ent.i.Application_System_Owned_By := TRUE;

CIOEntity.INS(Pass_ID,R_Ent);

cdapi.close_activity(act_status);

END;

PACKAGE BODY API_Update IS

/*

|| This package contains all of the procedures to

|| update items in the repository. Since we are

|| deleting and re-inserting the comment instead of

|| trying to update it, the ADD_COMMENTS procedure

|| is also used by API_INSERT.

|| J. Strano

|| Dulcian Inc.

*/

Procedure Entity(in_ID Number,in_Name Varchar2,in_app_name varchar2) IS

R_Ent CIOEntity.Data;

act_status varchar2(255);

BEGIN

cdapi.initialize(in_app_name);

cdapi.open_activity;

r_ent.v.name := in_name;

r_ent.i.name := TRUE;

cioentity.UPD(in_id,r_ent);

cdapi.close_activity(act_status);

END;

By employing both user extensions and the API, an application system can be built to easily track user requirements in Designer/2000. The development time expended in the beginning is paid back the first time a data entry mistake is made caught before being written to the repository.