Wiki: CONVERSION OF USER DEFINED FIELDS

If you wish to load user-defined fields for selected tables, you must create these user-defined fields with the scripts provided. The conversion application will support up to ten user-defined fields for each of the tables that are currently configured. Tables that currently support user defined fields are:

Table	SQL script to create User Defined Fields	User Defined Field names
INF_CV_JOBS	cv_uf_jobs.sql	DJB01 … DJB10
INF_CV_POSITIONS	cv_uf_positions.sql	DPS01 … DPS10
INF_CV_PERSONALS	cv_uf_personals.sql	EPS01 … EPS10
INF_CV_EMPLOYMENTS	cv_uf_employments.sql	EEM01 … EEM10
INF_CV_ASSIGNMENTS	cv_uf_assignments.sql	EAS01 … EAS02

During the conversion load process, the field names MUST correspond to the user defined field names shown here. These field names may be changed after all the conversion activities have completed.

Use of the script provided to create these user-defined fields would result in the fields named being created with a field type of CHAR.

For the examples below, reference is made to the CV_SVPERSONALS program and the PERSONALS.CTL file (found in the Conversion\CTL Files folder). Note that to do this same work with any other table, you just change the PERSONALS to the appropriate table - ASSIGNMENTS, EMPLOYMENTS, etc. Once you have made changes in the CV_SVPERSONALS.SQL file, you must re-apply it to the data base (this makes it a stored procedure), logged in as the P2K user.

To "HARD CODE" user defined field names#

At or near line 890 in the CV_SVPERSONALS.SQL program file, we see the following PL/SQL Code:

/* ***** validate USER_FIELD_VALUE_01 ***** */
    v_muf_id_01 := NULL;
    IF r_crs1.user_field_value_01 IS NOT NULL THEN
      OPEN c_user_field('EPS01');

In this case USER_FIELD_VALUE_01 is being mapped to a hard coded variable named (generically) EPS01. To link USER_FIELD_VALUE_01 to a specific field, we just need to change the occurrence of EPS01 to whatever we have defined the UDF in IMUF as. In the example below, we have created a field called DRUG_TEST, with a screen prompt of Drug Test Results.

To link the first UDF to that field, simply change EPS01 to DRUG_TEST in all places in the program (there are 3 - one for the lookup and two for messaging).

Repeat for as many user defined fields as you wish to.

To "ADD" more UDFs to the program#

Several conversion programs within the application allow for user defined fields (Personals(10), Employments(10), Assignments (12), Jobs(10), Positions(10), SundryLines(1) ) If this is insufficient, or you need to add logic to support more, here is how you do it:

Step 1 - Allow for an additional field in the table#

The table needs to have an additional field added to it. In SQL, as the P2K user, issue the following command to add an additional field(s) to the interface table:

ALTER TABLE INF_CV_PERSONALS ADD (USER_FIELD_VALUE_11 VARCHAR2(2000)
                                 ,USER_FIELD_VALUE_12 VARCHAR2(2000));

Step 2 - Provide for the SQL Loader to load it in#

Modify the PERSONALS.CTL file to include USER_FIELD_VALUE_11 at the appropriate place in the structure.

Step 3 - Modify the program to handle it#

In the CV_SVPERSONALS.SQL file, we need to allow for this variable to be read, validated, and processed.

in the cursor used to read the INF_CV_PERSONALS table, we need to add an additional line for the field(s) just added. This cursor starts near the top of the program and is usually called CURSOR c_crs1 IS ... and we just want to add a couple of new lines at the bottom of the cursor definition:

         , RTRIM(LTRIM(USER_FIELD_VALUE_10))  USER_FIELD_VALUE_10
         , RTRIM(LTRIM(USER_FIELD_VALUE_11))  USER_FIELD_VALUE_11
         , RTRIM(LTRIM(USER_FIELD_VALUE_12))  USER_FIELD_VALUE_12
         , rowid

We also need to add some variables to hold the values we will be processing. Later in the program (around line 275) we will see the definitions for the previous UDF, and so we just need to add to that: (henceforth I will only be working with one UDF in this example ... but repeat as needed for others)

  r_user_field_value_10 c_user_field%ROWTYPE;
  v_muf_id_10 NUMBER;
--------------------------------
  r_user_field_value_11 c_user_field%ROWTYPE;
  v_muf_id_11 NUMBER;
--------------------------------

And around line 357, we have a variable to hold the user field value, which we must copy:

  V_USER_FIELD_VALUE_10  VARCHAR2(2000);
  V_USER_FIELD_VALUE_11  VARCHAR2(2000);

The validation routine (which checks for the existence of the user defined field and then saves the value read from the INF_CV table is duplicated for each and every UDF. We can just copy and paste the code from the last user defined field (somewhere after line 1060), and change the names as appropriate:

--------------------------------
/* ***** validate USER_FIELD_VALUE_11 ***** */
    v_muf_id_11 := NULL;
    IF r_crs1.user_field_value_11 IS NOT NULL THEN
      OPEN c_user_field('DRUG_TEST');
      FETCH c_user_field INTO r_user_field_value_11;
      IF c_user_field%NOTFOUND THEN
        v_error := TRUE;
        p2k_smerl(p_mex_id, v_function,'0',
          r_crs1.user_field_value_11, 'P2K_AM_USER_FIELDS',
          'CV#-10001#1USER_FIELD#2DRUG_TEST#3P2K_AM_USER_FIELDS',
           '2', NULL);
      ELSE
        v_muf_id_11  := r_user_field_value_11.id;
        v_user_field_value_11 := r_crs1.user_field_value_11;
      END IF;
      CLOSE c_user_field;
    END IF;
---------------------------------

Finally, the routine which actually adds the User defined Fields into the UDF table needs to be cloned from another one, and the variable number changed (in this sample, I have compressed the calls to the message routine P2K_SMERL for readability) if you do this same thing for another table, you must copy from within each program - CV_SVASSIGNMENTS, CV_SVEMPLOYMENTS, etc.)

    IF v_muf_id_11 IS NOT NULL THEN
     IF v_eps_id IS NULL THEN
       v_error := TRUE;
          p2k_smerl(v_mex_id,v_function,'0',r_crs1.person_code,'P2K_AM_USER_FIELD_VALUES',
         'CV#-10019#1User Field#2Personals',2,NULL);
     ELSE
      OPEN c_ufv1(v_muf_id_11, v_eps_id);
      FETCH c_ufv1 INTO r_ufv1;
      IF c_ufv1%FOUND THEN
        p2k_smerl(v_mex_id,v_function,'0',r_crs1.person_code,'P2K_AM_USER_FIELD_VALUES','CV#-10003#1User Field Value#2' ||
                  r_crs1.person_code | '/DRUG_TEST/' ||v_effective || '#3P2K_AM_USER_FIELD_VALUES',0,NULL);
      ELSE
        BEGIN
          INSERT INTO P2K_AM_USER_FIELD_VALUES
            (MUF_ID,REFERENCE_ID,VALUE)
           VALUES
            (V_MUF_ID_11, V_EPS_ID, v_user_field_value_11);
          v_inserted_UF_VALUES := v_inserted_UF_VALUES + 1;
        EXCEPTION
          WHEN OTHERS THEN
            p2k_smerl(p_mex_id,v_function,'0','ABORT',NULL,NULL,2,' Error: ' || sqlerrm(sqlcode) -- run log text);
            v_error := TRUE;
        END;
      END IF;
      CLOSE c_ufv1;
     END IF;
    END IF;

Once these changes are made, save and re-apply the script to your data base.