Hi

I am trying to load jobs from my temp/staging table in oracle to oracle hrms:

Code for my jobs api call package is here:
http://pastebin.com/m704097f6

I setup jobs flexfield as:

2 segments

job name
job desc

allow dynmic insert is set to on, etc..

then i ran the above api to load jobs information into oracle but i get strange error:

ERROR at line 1:
ORA-20001: HR_289477_JOB_GROUP_ID
ORA-06512: at "APPS.HR_JOB_API", line 878
ORA-06512: at "APPS.XXcompany_PKG_JOBS", line 58
ORA-06512: at line 1

I am confused, i even set job_group_id manually in the api call which in my code above as 21, which it is.
but Still gave me the same error.

Thanks in advance

Tovia Singer

Check whether you are populating the concat_segments.

Hello Tovia

Can I ask, how are you executing this procedure?

If you're running it as a standalone piece of code in a SQL or TOAD session, then I can see why it might not be delivering what you expect. I've looked at the package procedure PER_JOB_BUS.chk_job_group_id, and the thing that becomes immediately apparent is that it should work nicely provided you've supplied an appropriate value for business group id.

However, looking at your driving code that calls the api, you appear to be setting the business group by making a call to FND_PROFILE.value('PER_BUSINESS_GROUP_ID')

By way of a simple check, can you try setting the business group to an appropriate value, to see if you get past that problem? Also, whilst you are about it, can I please make another couple of suggestions:

1. If you're using sysdate as some kind of reference date, it's a very good idea to TRUNC it, in order to remove the time component. e.g. v_date_from DATE:=TRUNC(SYSDATE);

2. You will note that the parameter p_job_definition_id is defined as IN OUT - now the first time you call the api, the value you supply is NULL (as you haven't explicitly set the variable up as any particular value). On successful completion of the API, it will supply the value of the job_definition_id back to the calling procedure via that same parameter. However, the next call for the next job will end up passing back in that same value returned from the previous call. That will cause problems because the job definition id you 'gave' it will not match the values for the segments. To get round this, you need to explicitly set the variable v_job_definition_id to NULL just before calling the API.

Hope this helps

Hi CT

Thanks for the reply, your a genius,
i set the business group id manually, and alsi did trunc on sysdate, and set: v_job_definition_id NUMBER:=NULL;

updated code: http://pastebin.com/d7ce018bb

Whats happend now is its said procedure succesfully completed,
Howevere when i checked: per_jobs only old jobs where ion there not teh ones i tried to load using the api. And i also checke teh application:

hrms uk super user
workstructures > job > Description

So i then ran the proceudre again and it said:

ERROR at line 1:
ORA-20001: The job you have entered already exists in this Business Group. Please enter a unique na
ORA-06512: at "APPS.HR_JOB_API", line 878
ORA-06512: at "APPS.XXH3G_PKG_JOBS", line 46
ORA-06512: at line 1

but thats not true,

the only thing i can think of is:

Application Developer > Application: Lookups > Common
COMPANY_HR_JOB_FUNCTION

HERE I ENETERD ALL THE JOB FUNCTIONS MANUALLY.

AND UNDER THE LOOKUP:
COMPANY_HR_JOB_TITLE

I ENTERED ALL THE JOB TITLES MANUALLY AS WELL.

Now i was atempting to load job_codes, eg:

Personal Assistant.Administration
Personal Assistant.Human Resources
Planner.Engineering (Networks)
Planner.Engineering (Operations)
Planner.Engineering (Technology)
Planner.Marketing


However Personal Assistant only exists under COMPANY_HR_JOB_TITLE lookup, and Administration for the first job above exists only in the lookups: COMPANY_HR_JOB_FUNCTION,

They dont exist as a combination in: per_Jobs name field.

So does that mean i have to create all my jobs using the above two lookup values which are visible when i go to:

workstructures > job > Description

?

I have too many jobs: 436 jobs in total.

Shall i remove the lookup value values?

by the way job_code in my temp table where i loaded all my jobs stores the values as:
Planner.Engineering (Operations)
Planner.Engineering (Technology)


Thanks in advance

Tovia Singer

So when you say you can't see the jobs, how are you trying to find them?

Try to find them using the creation date:

SELECT *
FROM PER_JOBS
WHERE creation_date > trunc(sysdate);

and see what comes back...

Plainly the validation of the job name uniqueness (performed by PER_JOB_BUS.chk_unique_name) is finding them as you're getting that message...

Hi CT

Also when i try to find the job.desc in: workstrrtures > job > description

I get the error:
app-fnd-00906: You can only query existing flexfield combinations.
You entered query criteria...

Does that mean my api didnt create/ load the job?

job menaing the combination of the two segments as i epxlained eralier


Thanks in advance

Tovia Singer

Hi CT

Interesting: The first job(first record) in my company_jobs table (temp table) returned when i ran:
SELECT *
FROM PER_JOBS
WHERE creation_date > trunc(sysdate);

which is:

Manager.Engineering (Operations)

the rets arent there

thanks in advance

Tovia

Hi Tovia

I'm a little confused - which, for those who know me, will not be a surprise...

This process is all about creating jobs that don't exist, yes? And yet for each pass of the source table there is at the start of the loop a check to see if the job name already exists, and log a message if it doesn't!

Could you possibly explain what that particular bit of code is intended to achieve?
Actually I think I've just sussed it:

I suspect this will work right up to the point where that little query actually finds a job id. Thereafter, even if the next row to be processed is not found, the previous value of v_job_id will be unchanged. In other words, not finding a row in per_jobs will NOT set the v_job_id to null! In the immediate term therefore, you need to force that v_job_id to null at the start of the loop.

Oh and while you're about it, from looking at the updated code you still need to reset the job_definition_id, like:

BEGIN

FOR v_emp_jobs IN cur_job_emp LOOP
--
v_job_id := null;
v_job_definition_id := null;
--
BEGIN
SELECT job_id
INTO v_job_id
FROM per_jobs
WHERE name=v_emp_jobs.job_code;
:
:
etc

Hi CT

Thanks so much, i am still getting error:

ERROR at line 1:
ORA-20001: The job you have entered already exists in this Business Group.
Please enter a unique name for your job.
ORA-06512: at "APPS.HR_JOB_API", line 878
ORA-06512: at "APPS.XXH3G_PKG_JOBS", line 46
ORA-06512: at line 1

i set v_job_id NUMBER:=NULL; updated the code here:
http://pastebin.com/d3b255130

thanks in advance

Tovia

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....