In this example, we will assume we are going to update a user defined field (defined on IMUF) for the P2K_HR_ASSIGNMENT_DETAILS table, and the UDF name is WORK CELL PHONE. We will further assume that our spreadsheet consists of two columns - A contains the person code and B contains the UDF value (in this case the cell phone), and the data starts in row 2 (row 1 being headers or even just blank)

1. In cell E1 paste this (the table name and user defined field - exactly as on on IMUF)

p2k_smputudf('P2K_HR_ASSIGNMENT_DETAILS','WORK CELL PHONE',p2k_pu.get_easd('

2. in cell F1 paste this

'',0),'

3. in cell G1 paste this

'');

4. in cell E2 paste this

=CONCATENATE($E$1,A2,$F$1,B2,$G$1)

so we are stringing together the PL/SQL fragments from E1, F1 and G1 cells with the data in cells A2 and B2 to make complete statement.

5. Press enter. If you had A2 = 1234567 and B2 = jjsmith@tst.com. you should now see something like

 p2k_smputudf('P2K_HR_ASSIGNMENT_DETAILS','WORK CELL PHONE',p2k_pu.get_easd('1234567',0),'jjsmith@tst.com');

which will find the most future EASD record for the prime assignment for the employee identified by the first parameter in P2K_PU.GET_EASD by person code (the second parameter is SSN), and put the User Defined Field identified by the table and UDF name into the User Defined Field Values table.

6. Now that we have 1 row good, let's click E2, press Ctrl-C (copy) and go to the end of our list of emloyees (let's say that is on row 765). In cell E765, click, and press Ctrl-Shift-up arrow. This will highlight all the cells in column E from 3 to 765. Press Ctrl-V (paste).

Now the same formula from E2 is pasted in the other rows, but the A2 and B2 parts are relative references (without the $ in the cell reference) so we see each line is different.

7. Next, highlight E2 to the end (E765), press Ctrl-V (copy) and paste into a text editor (new file).

8. Add, in front of the first line:

BEGIN
  p2k_pmsession.login('SQ','Update EASD Email addresses');

9. add, after the last line:

  p2k_pmsession.logoff;
END;
/
COMMIT;

10. If your script is thousands and thousands and thousands of lines long, then you may want to intersperse these statements in the middle, in order to break it up into manageable chunks. If not, then don't worry about it.

END;
/
COMMIT;
BEGIN

11. Save this script and execute against the database.

if your person codes have leading zeroes, Excel makes things difficult by treating them as numbers, and stripping off the leading zeroes, so we have to do something a little more.

Let's assume in our example, that person codes may be four five or six figits long, excluding leading zeroes, but in the application they all are six digits, with leading zeroes.

In cell D2 (currently empty), let's paste this, to force the column to have the leading zeroes.

=IF(LEN(A2)=4,CONCATENATE("00",A2),IF(LEN(A2)=5,CONCATENATE("0",A2),A2))

so we now see A2 = 1234 and D2 = 001234

and then we need to change E2 to reference this reformatted person code as such

=CONCATENATE($E$1,D2,$F$1,B2,$G$1)

and so we get

p2k_smputudf('P2K_HR_ASSIGNMENT_DETAILS','WORK CELL PHONE',p2k_pu.get_easd('001234',0),'jjsmith@tst.com');

with the leading zeroes, and then repeat steps six and seven above.