Hi,
I have to update the employee number to some sequence, for that im using the below code
/* Formatted on 2009/07/16 12:51 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.emp (
p_business_group_id IN NUMBER
)
AS

l_effective_date DATE := '01-jan-1950';
l_person_id NUMBER;
l_eff_date DATE;
l_ovn NUMBER;
--l_message VARCHAR2 (2000);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_full_name VARCHAR2 (260);
l_comment NUMBER;
l1 BOOLEAN;
l2 BOOLEAN;
l3 BOOLEAN;
l_emp_number VARCHAR2 (8);
l_opname varchar2(200);

cursor c is
SELECT papf.person_id, papf.start_date, papf.object_version_number
INTO l_person_id, l_eff_date, l_ovn
FROM per_all_people_f papf
WHERE business_group_id = p_business_group_id

AND TRUNC (l_effective_date) BETWEEN papf.effective_start_date
AND papf.effective_end_date;

BEGIN
FOR i IN c
LOOP
l_person_id := NULL;
l_eff_date := NULL;
l_ovn := NULL;
-- l_message := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
l_full_name := NULL;
l_comment := NULL;
l1 := NULL;
l2 := NULL;
l3 := NULL;
l_emp_number := NULL;

BEGIN
select aa.nextval into L_EMP_NUMBER from dual;






DBMS_OUTPUT.put_line ('----START OF API-----');



hr_person_api.update_person
(p_validate => FALSE,
p_effective_date => TRUNC
(l_effective_date
),
p_datetrack_update_mode => 'CORRECTION',
p_person_id => l_person_id,
p_object_version_number => l_ovn,
p_employee_number => L_EMP_NUMBER,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment,
p_name_combination_warning => l1,
p_assign_payroll_warning => l2,
p_orig_hire_warning => l3
);
DBMS_OUTPUT.put_line ('----END OF API-----------');
/*
UPDATE xxzn_ga_emp_updt_stg
SET flag = 'S',
status = 'UPLOADED'
WHERE slno = i.slno;*/

COMMIT;
END;
--END IF;
END LOOP;
END;
-------------------------------------------------------
Sequence is
CREATE SEQUENCE aa
START WITH 53000
INCREMENT BY 1
MAXVALUE 53999
NOCACHE;
---------------------


Plase let me know wheather i cn faallow the above code or not
Thanks

I have not "proof-read" your code (and do not intend to)...

However, before you commence this, have you checked the setting for employee number generation? If it is set to automatic then this process will not work - as the API will reject any attempt to update the employee number.
You will need to change this setting to be Manual.

Regards

Yes the setting is Manual

You might also want to consider the assignment number that was set on the assignment when the employee record was first created. By default it will be the same as the employee number (or appended with '-2' if it already exists). I'm not 100% convinced that changing the employee number will automatically keep the assignment number in step with it. If it doesn't then you run the risk of causing a lot of confusion, unless your update that too.

Yes the above things worked...
Now i have to work for 10 opcos(country) with their respective employee number sequence, for example say for opco1 emp number range is 1-100, for opco 2 the employee number sequence should be in 200-300 range so on.
The above sequence for all the above opco's should work till 31 march 2011, from 01 april 2011 all the above should use a single squence say sequence starts with 1000 for example
if employee is created for 1'st opc on 1 april then the employee squence should be 1000, if 5nd opco creates then emp number should 1001 and so on.
how can we generate thise kind of sequence for the employees for different opcos
Any advice would be appricated.
Thanks and Regards
Arman

When you say Opcos (Country) i assume you also mean that they are in different business groups?

Yes they have different business groups

Is there any way to solve the above post..


Any suggessions will be appriciated.

Thanks

Different business groups is okay.
You can set the different starting sequence numbers by using the table PER_NUMBER_GENERATION_CONTROLS.
Each BG will have its own Emp and Apl last number used recorded as a row in this table.

Going forward for when you want a single sequence number then you need to look into using the global employee numbering facility. You may need to do a search within myOracleSupport for documents on this.

Regards

Barry

I Just had a light on your code.
Does it really work for all date tracked records.
Looks like the above snippet works only for the records effective on 01-jan-1950. Even if you remove the date condition, if the person record is date tracked, you would get different employee numbers for each record of the same person. This is what exactly you don't want.

Please correct me if I am wrong.

Peddi.