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