What is wrong with this api Pls help

[adelhussien]

I am creating api to migrate jobs from an extenal table to hr but when executing the script it only inserts the first record and gives this message
"-20001 ORA-20001: The job you have entered already exists in this Business Group. Please enter a unique name for your job."
as I mentioned the job which he means already created is the first row of the table
here is the code
DECLARE
l_count Number :=0;
v_business_group_id Number :=101;
v_date_from date := TO_DATE('01-01-1900','DD-MM-YYYY');
v_job_group_id number := 21;
v_object_version_number number :=1;
v_segment1 varchar2(10);
v_segment2 varchar2(150);
v_attribute2 varchar2(150);
v_attribute3 varchar2(100);
v_attribute4 varchar2(50);
v_attribute5 varchar2(50);
v_attribute6 varchar2(50);
cursor job is select DESGN_CODE,
DESGN_DESP_M_A,
DESGN_DESP_F_A,
DESGN_DESP_E ,
DESGN_TYPE ,
DESGN_CAT_CODE,
GRP_CODE
from aa_tdesignation
Where rownum < 10
and desgn_desp_e is not null;
v_segment5 varchar2(100) := NULL;
v_segment6 varchar2(100) := NULL;
v_job_id number;
v_job_definition_id number;
v_name varchar2(100);
begin
dbms_output.put_line('##########################################################');
dbms_output.put_line('Data Migration Of Jobs:');
dbms_output.put_line('##########################################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
for my_cur in job
LOOP
BEGIN
v_segment1 := my_cur.DESGN_CODE;
v_segment2 := my_cur.DESGN_DESP_M_A;
v_attribute2 := my_cur.DESGN_DESP_F_A;
v_attribute3 := my_cur.DESGN_DESP_E;
v_attribute4 := my_cur.DESGN_TYPE;
v_attribute5 := my_cur.DESGN_CAT_CODE;
v_attribute6 := my_cur.GRP_CODE;
HR_JOB_API.CREATE_JOB
(p_business_group_id => v_business_group_id
,p_date_from => v_date_from
,p_job_group_id => v_job_group_id
,p_object_version_number => v_object_version_number
,p_segment1 => v_segment1
,p_segment2 => v_segment2
,p_segment5 => v_segment5
,p_segment6 => v_segment6
,p_attribute2 => v_attribute2
,p_attribute3 => v_attribute3
,p_attribute4 => v_attribute4
,p_attribute5 => v_attribute5
,p_attribute6 => v_attribute6
,p_job_id => v_job_id
,p_job_definition_id => v_job_definition_id
,p_name => v_name);
Dbms_output.put_line('Sucess'||' '||my_cur.DESGN_CODE||' '||my_cur.DESGN_DESP_M_A);
L_COUNT:=L_COUNT+1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Total Nubmer of record'||L_COUNT);
Exception
When others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
DBMS_OUTPUT.PUT_LINE ('Total Nubmer of record'||L_COUNT);
end;
/
Thanks

[sathish_puttur]

There May be Possiblity that some of the jobs already defined by Oracle that may give conflict. for Eg Job "Manager" is already defined by Oracle, when you write API to upload the data The Job What you have created " Manager" will conflict each other.

To solve this issue, i can suggest you to go for <b>webADI</b> to upload the data.
In ADI you will come to know which Rows has the problem so you can solve those row or you can upload remaining rows.

regards
Sathish

[nhawi]

Dear,

I would like to inform you that when you create a job oracle has two main table PER_JOB, and PER_JOB_DEFINITIONS, please check if this tables contains your data.

thanks
Nakhla Al-Hawi

[adelhussien]

Thanks brother Sathish and brother nhawi
But I am sure that these jobs is not conflicting with the jobs created by oracle because It inserts the first job successfuly and it suppose to go to the second job but it won't, it goes again to the first record and it shows that error.

[adelhussien]

Yes I found jobs are there in per_job_defintions but I cannot delete them because they they have a referance key in per jobs
when query the per jobs no records are there .

[sathish_puttur]

Hello,

It is better try out WEBADI it is best tool to upload the data. some time API will not shows exact error, but WEBADI well give exact error and you can solve it easily.

regards
sathish

[adelhussien]

Thanks Sathish, I beleive it is a bug because what I did is ran the script no of times equal to the number of jobs and commit after each time and it works

Regards
Adel