Step 5: Define Master Index Code Lists

Note - MySQL is only supported in Java CAPS 6 Update 1.

You only need to perform this step if you defined any fields in the object structure to have a code module. The SQL script for entering processing codes and descriptions into the database is written in PL/SQL. The wizard creates a stanza incodelist.sql (located under the Database Script node of the project) for each code list you specified in the field properties. You need to customize the file by defining the entries for each code list. This script inserts data into two tables: sbyn_common_header, which lists the types of common table data, and sbyn_common_detail, which lists each common table data element. Before you begin this step, complete Step 4: Define Master Index External Systems.

Note - The codes you specify in this file can be no longer than eight characters (the codes are the second value in the value list for each common table data type and data element).

To Customize Common Table Data for MySQL

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open codelist.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open codelist.sql in a text editor.
2. Scroll to the first line that begins with “— **** “.

   The statements following this line must be customized.

3. In the first code list stanza, change “module description” in the first line to a brief description of the code type.

   For example:

   -- ****         PHONTYPE   ****
   insert into tCodeList values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’);

4. Create the entries for that module using the following syntax:

   insert into tCodeList values(’V’, ’code’, ’code description’);

   where “code” is the processing code of the data element and “code description” is the description of the element as you want it to appear on the Master Index Data Manager windows. For example:

   -- ****         PHONTYPE   ****
   insert into tCodeList values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’);
   insert into tCodeList values(’V’, ’H’, ’HOME’);
   insert into tCodeList values(’V’, ’C’, ’CELL’);
   insert into tCodeList values(’V’, ’F’, ’FAX’);
   insert into tCodeList values(’V’, ’O’, ’OFFICE’);
   insert into tCodeList values(’V’, ’HB’, ’HOME BUSINESS’);

5. Repeat the previous two steps for each code list type defined in the file.
6. If you specified additional code list fields in object.xml and midm.xml after the database scripts were generated, add a new stanza for each new code type.
7. Save and close the file.
8. Do one of the following:
   • If you need to define user code lists, continue to Step 6: Define Master Index User Code Lists.
   • If you need to create a custom database script, skip to Step 7: Create Custom Master Index Database Scripts.
   • If you are ready to create the master index database structure, skip to Step 8: Create the Master Index Database Structure.

To Customize Common Table Data for Oracle

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open codelist.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open codelist.sql in a text editor.
2. Scroll to the following line.

   codes tCodeList := tCodeList(

   The statements following this line must be customized.

3. In the first code list stanza, change “module description” in the first line to a brief description of the code type.

   For example:

   -- ****         PHONTYPE   ****
   tCode(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’),

4. Create the entries for the module using the following syntax:

   tCode(’V’, ’code’, ’code description’),

   where “code” is the processing code of the data element and “code description” is the description of the element as you want it to appear on the Master Index Data Manager windows. For example:

   -- ****         PHONTYPE   ****
   tCode(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’),
   tCode(’V’, ’H’, ’HOME’),
   tCode(’V’, ’C’, ’CELL’),
   tCode(’V’, ’F’, ’FAX’),
   tCode(’V’, ’O’, ’OFFICE’),
   tCode(’V’, ’HB’, ’HOME BUSINESS’),

5. Repeat the previous two steps for each code list type defined in the file.
6. If you specified additional code list fields in object.xml and midm.xml after the database scripts were generated, add a new stanza for each new code type.
7. In the last code module stanza, make sure each line except the last contains a comma at the end.

   For example:

   -- ****         ADDRTYPE   ****
   tCode(’L’, ’ADDRTYPE’, ’ADDRESS TYPE’),
   tCode(’V’, ’H’, ’HOME’),
   tCode(’V’, ’B’, ’BUSINESS’),
   tCode(’V’, ’M’, ’MAILING’)

8. Save and close the file.
9. Do one of the following:
   • If you need to define user code lists, continue to Step 6: Define Master Index User Code Lists.
   • If you need to create a custom database script, skip to Step 7: Create Custom Master Index Database Scripts.
   • If you are ready to create the master index database structure, skip to Step 8: Create the Master Index Database Structure.

To Customize Common Table Data for SQL Server

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open codelist.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open codelist.sql in a text editor.
2. Scroll to the following line.

   begin

   The statements following this line must be customized.

3. In the first code list stanza, change “module description” in the first line to a brief description of the code type.

   For example:

   -- ****         PHONTYPE   ****
   insert into @codelist values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’)

4. Create the entries for the module using the following syntax:

   insert into @codelist values(’V’, ’code’, ’code description’)

   where “code” is the processing code of the data element and “code description” is the description of the element as you want it to appear on the Master Index Data Manager windows. For example:

   -- ****         PHONTYPE   ****
   insert into @codelist values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’)
   insert into @codelist values(’V’, ’H’, ’HOME’)
   insert into @codelist values(’V’, ’C’, ’CELL’)
   insert into @codelist values(’V’, ’F’, ’FAX’)
   insert into @codelist values(’V’, ’O’, ’OFFICE’)
   insert into @codelist values(’V’, ’HB’, ’HOME BUSINESS’)

5. Repeat the previous two steps for each code list type defined in the file.
6. If you specified additional code list fields in object.xml and midm.xml, add a new stanza for each new code type.
7. Save and close the file.
8. Do one of the following:
   • To define user code lists, continue to Step 6: Define Master Index User Code Lists.
   • To create a custom database script, skip to Step 7: Create Custom Master Index Database Scripts.
   • To create the master index database structure, skip to Step 8: Create the Master Index Database Structure.