HI CT

Thanks,

I ste those 2 variables to null after teh start of teh for loop, updated code:
http://pastebin.com/d62c748cd

Howevere really bizare i now get:
ERROR at line 1:
ORA-20001: FLEX-ID DOES NOT EXIST:
ORA-06512: at "APPS.HR_JOB_API", line 878
ORA-06512: at "APPS.XXCOMPANY_PKG_JOBS", line 47
ORA-06512: at line 1

KIND REGARDS

Tovia

OK I'll need to have a think about this one...

Right, the message text associated with that error_code is actually:

"ID &ID for the flexfield segment &SEGMENT does not exist in the value set &VALUESET."

Which suggests that the value you're trying to give one of the segments for one of the jobs you want to create, is not present in the value set for the segment.

This kind of raises another issue - the way you've got it structured at the moment, your whole process grinds to a halt the first error it encounters in the API call; rolling back any successful api calls in the process. What you really need to do is to trap the error for the api call, and thereafter allow the process to move on to the next row to be processed.

The way I've always approached this is to have an exception handler within the loop, so that errors can be trapped and recorded as necessary, but then the process is allowed to continue to the end of the data set. I also try and have some sort of commit strategy built into the loop, e.g. commit every 100 rows processed. Not a big deal for smallish record counts, but 10's of thousands being processed before a commit would probably be a bit risky.

eg.

FOR....LOOP
BEGIN
<do stuff>
<call api>
EXCEPTION
<record exception details>
END;
<commit if appropriate>
END LOOP;
:
<final commit at the end for good measure>
:
etc.

Hi CT

Thanks so much, i wish my boyfriend was as good as you, your amazing.

I have updated the code again and added an exception, however i added it after the end loop statement:
http://pastebin.com/d2b16fdea

8 records loaded and i got the following in sql*plus:

SQL> execute XXCOMPANY_PKG_JOBS. XXCOMPANY_LOAD_JOBS

PL/SQL procedure successfully completed.

SQL> select name
2 from per_jobs;

NAME
--------------------------------------------------------------------------------
Assistant.Finance
Department Head.Human Resources (Operations)
Designer.Information Technology
Designer.Marketing
Director.Engineering (Radio)
Director.Technology
Manager.Commercial
Project Manager.Information Technology

8 rows selected.

SQL> commit;

Commit complete.

SQL>
-----------------------------------------------------------------
It didnt load the rest of the records,
Which total 420+.


Why didnt it load all records did i close loop to early i dont understand.

Thanks in advance

Tovia S

OK, here's how I would have done it (note the following has been totally untested so you can reasonably expect some syntax errors when trying to compile it):

CREATE OR REPLACE PACKAGE BODY XXcompany_PKG_JOBS
IS
--
PROCEDURE XXcompany_LOAD_JOBS
IS
--
v_validate boolean:=false;
v_date_from DATE:=TRUNC(SYSDATE);
v_comments VARCHAR2(20);
v_approval_authority NUMBER:=NULL;
v_benchmark_job_flag VARCHAR2(20):='N';
v_benchmark_job_id NUMBER:=NULL;
v_emp_rights_flag VARCHAR2(20):='N';
v_job_group_id NUMBER:=21;
v_segment3 VARCHAR2(20):=NULL;
v_segment4 VARCHAR2(20):=NULL;
v_concat_segments VARCHAR2(20):=NULL;
v_language_code VARCHAR2(20):=hr_api.userenv_lang;
v_job_id NUMBER;
v_object_version_number NUMBER;
v_job_definition_id NUMBER;
v_name VARCHAR2(150);
--Inssert Value set Output Parameter
v_storage_value VARCHAR2(60);
v_business_group_id number:=81;
--
CURSOR cur_job_emp IS
--
SELECT job_code,
SUBSTR(job_code,1,(INSTR(job_code,'.')) -1 ) job_name,
SUBSTR(job_code,(instr(job_code,'.'))+1) job_desc
FROM company.company_JOBS;
--
-- v_emp_jobs cur_job_emp%ROWTYPE; -- not needed in a FOR ... IN <cursor> LOOP consruct
--
BEGIN
--
-- CET Only need to do this once, so removed it from within the loop
--
SELECT job_group_id
INTO v_job_group_id
FROM PER_JOB_GROUPS
WHERE master_flag='Y';
--
FOR v_emp_jobs IN cur_job_emp LOOP
--
BEGIN
--
SELECT job_id
INTO v_job_id
FROM per_jobs
WHERE name = v_emp_jobs.job_code;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_job_id := null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'There is no job id Exist For this job code'||v_emp_jobs.job_code);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While selecting from PER_JOBS: '||SQLCODE||SQLERRM);
--
END;
--
--
IF v_job_id IS NULL THEN
BEGIN
--
v_job_definition_id:=null;
--
hr_job_api.create_job
(p_validate => v_validate
,p_business_group_id => v_business_group_id
,p_date_from => v_date_from
,p_job_group_id => v_job_group_id
,p_segment1 => v_emp_jobs.job_name
,p_segment2 => v_emp_jobs.job_desc
,p_language_code => v_language_code
,p_job_id => v_job_id
,p_object_version_number => v_object_version_number
,p_job_definition_id => v_job_definition_id
,p_name => v_name
);
EXCEPTION -- trap any individual problems with the api call and report them but allow to return to the loop
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in CREATE_JOB API on job '||v_emp_jobs.job_code||'
': '||SQLCODE||SQLERRM);
END;
--
ELSE
DBMS_OUTPUT.PUT_LINE('Job '||v_emp_jobs.job_code||' already exists');
END IF;
--
DBMS_OUTPUT.PUT_LINE('Ex:'||v_job_id||','||v_object_version_number);
--
END LOOP;
COMMIT;
--
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
--
END XXcompany_LOAD_JOBS;
END XXcompany_PKG_JOBS;
/

Hi CT

Thanks a million!!

Final code for loading jobs via api's thanks and all credit to CT:
http://pastebin.com/f460e45cc

Sorry i took a while as i was trying to understand the code you wrote and why i only had 8 records loading, i had to debug a little, but just made the change in my code, and it worked,

However temp table has 329 rows, and per_jobs has 295, so 34 rows (jobs) wherent created.

I forgot to create job_function, and job_title for those 34 jobs.
under application developer > application Lookups > common

Which i suppose is done manually i will try to find an api for those, however i have grades,positions and employees to load via apis, so i must carry on with those as my line manager is on my back for updating receivables cutsomer address details via a TCA api, which i've done before anyway, but completely new to hrms apis.

So i will be using the same site again where i got my initial api code from, unless you can suggest otherwise:

http://irep11i10.oracle.com/OA_HTML/OA.jsp?page=%2Foracle%2Fapps%2Ffnd%2Frep%2Fwebui%2FInterfacesBrowsePG&_ti=350577385&oapc=3&oas=bXS7OQzJjhPqk-gcENqmSg..

Thanks a million CT!


Tovia Singer

That link looks very interesting, and even without having looked at it in great detail would seem to me to be an ideal starting point for how each api works. Thanks for sharing by the way.

Loading grades is pretty similar to loading jobs, so shouldn't be any unpleasant experiences there.

Positions is slightly more complicated, as in HRMS a position has to belong to a specific organization and a specific job. So you'll need to come up with the appropriate foreign key ids from the HR_ALL_ORGANIZATION_UNITS and PER_JOBS tables as well as all the other segment-related data.

Loading employees is marginally more complicated than that, but only because there are a lot more pieces of information needed to create an employee record. Essentially the same philosophy will apply:

Query in the data
For each row, execute a pl/sql block to get the values and call the api, and trap the error if it occurs
Next row...
Commit etc

From looking at my R12 instance, we still don't have an api for managing lookup values. There are FND_ type packages there, but the way they're written suggests to me that they are not in the public domain in the way that APIs are. ie you could use them but their use by the likes of thee and me is not supported. This of course is not necessarily a problem if it's for a one-off exercise such as a migration, but for day-to-day interfacing etc the issue of supportability (by Oracle) is very pertinent.

A possible alternative for loading data is of course the DataLoader tool, which used to be available from Comstar but I believe is now owned/maintained by Edenbrook. If you've never heard of it, it's basically a keystroke emulator which spits data and screen navigation keystroke information at an application instance from what looks very like a spreadsheet interface.

A much preferable way of course (and by far my favourite) is to report your findings to the functional team and invite them to earn some of their exorbitant fee keying the missing lookups in manually!


Good luck!

I've come in on the tail-end of this post - good stuff by the way...

I've tended to load lookups and valuesets using FNDLOAD. Valuesets have their own core LCT file for use with FNDLOAD, and lookups are relatively easy to do with a simple custom LCT file.

Co-incidentally, i've also used FNDLOAD to load grades as well, although cannot take full credit for the original idea.
Its stuff like this that reminds me that we need a reuse repository on this site....Time to manufacture an extra day in the week to do all these things.

Yes, a good point well made - I'd forgotten about FNDLOAD as an option on lookups. I'm intrigued by the notion of loading other entities using that mechanism though....