PER_ALL_PEOPLE_F_ARIU AFTER INSERT
or update of person_type_id
ON per_all_people_f
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Type
AFTER EACH ROW
Event
INSERT OR UPDATE
Column
When
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
DECLARE
e_ResourceBusy EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ResourceBusy, -54);
e_NumOutSync EXCEPTION;
g_db_trigger varchar2(30) := 'per_all_people_f_ari trigger';
g_bg_context_name varchar2(30) := 'Business Group Information';
l_emp_method HR_ORGANIZATION_INFORMATION.Org_information2%TYPE;
l_apl_method HR_ORGANIZATION_INFORMATION.Org_information3%TYPE;
l_cwk_method HR_ORGANIZATION_INFORMATION.Org_information16%TYPE;
l_next_value per_number_generation_controls.NEXT_VALUE%TYPE;
l_sys_person_type per_person_types.system_person_type%TYPE;
--
cursor csr_method(cp_bg_id per_all_people.business_group_id%TYPE) is
SELECT Org_information3 -- method_of_generation_apl_num
, Org_information2 -- method_of_generation_emp_num
, Org_information16 -- method_of_generation_cwk_num
from hr_organization_information
where organization_id = cp_bg_id
and ORG_INFORMATION_CONTEXT = g_bg_context_name;
--
cursor csr_next_value(cp_bg_id per_all_people.business_group_id%TYPE,
cp_type varchar2) is
SELECT next_value
FROM per_number_generation_controls
WHERE business_group_id = cp_bg_id
AND type = cp_type;
--
FUNCTION person_exists (p_person_id IN number
, p_system_person_type IN varchar2
)
return boolean
is
l_dummy varchar2(1) := 'N';
CURSOR csr_person_type
IS
SELECT 'Y'
FROM per_person_types typ
,per_person_type_usages_f ptu
WHERE typ.system_person_type = p_system_person_type
AND typ.person_type_id = ptu.person_type_id
AND ptu.person_id = p_person_id;
begin
open csr_person_type;
fetch csr_person_type into l_dummy;
close csr_person_type;
if l_dummy = 'Y' then
return(TRUE);
else
return(FALSE);
end if;
END person_exists;
--
-- Added this function to handle scenario when "Global person numbering"
-- profile option is set (enhancement 2931775).
--
FUNCTION get_next_value(p_business_group_id IN number
,p_person_type IN varchar2)
return number is
l_person_number number := -2;
l_session_date date;
BEGIN
l_session_date := HR_GENERAL.Effective_Date;
if PER_BG_NUMBERING_METHOD_PKG.Global_person_numbering(p_person_type)
or PER_BG_NUMBERING_METHOD_PKG.Get_PersonNumber_Formula
(p_person_type,l_session_date) is not null
then
-- we cannot check current value of the sequence
-- since this is not being locked when generating the numbers
-- if custom algorithm is being used, then ignore check as well
--
l_person_number := -1;
else
open csr_next_value(p_business_group_id,p_person_type);
fetch csr_next_value into l_person_number;
close csr_next_value;
end if;
return(l_person_number);
END get_next_value;
--
BEGIN
hr_utility.set_location('Entering:'||g_db_trigger,100);
If hr_general.g_data_migrator_mode in ( 'Y','P') then --6365083
hr_utility.set_location('Data Migrator mode= '||hr_general.g_data_migrator_mode,101);
hr_utility.set_location('Leaving:'||g_db_trigger,996);
return;
Else
open csr_method(:NEW.business_group_id);
fetch csr_method into l_apl_method, l_emp_method, l_cwk_method;
close csr_method;
-- -------------------------------------------------------------------------+
-- Processing EMPLOYEES +
-- -------------------------------------------------------------------------+
IF :NEW.CURRENT_EMPLOYEE_FLAG = 'Y'
AND ((inserting) OR
(updating and :OLD.person_type_id <> :NEW.person_type_id)
)
AND not person_exists(:NEW.person_id, 'EMP')
then
hr_utility.set_location(g_db_trigger,101);
-- Special case for SSHR if the profile is set
-- as we need to make sure that the generation controls table is not
-- locked.
-- if profile option is set that the employee number will not be generated and
-- instead a null employee number will be returned. (see bug 2552720)
if fnd_profile.value('PER_SSHR_NO_EMPNUM_GENERATION') = 'Y' then
hr_utility.set_location('Leaving:'||g_db_trigger,996);
return;
end if;
hr_utility.trace('>>> method found : '||l_emp_method);
if l_emp_method = 'A' then
hr_utility.set_location(g_db_trigger,105);
l_next_value := get_next_value(:NEW.business_group_id,'EMP');
if l_next_value < 0 then -- global sequence has been used
hr_utility.set_location('Leaving:'||g_db_trigger,997);
return;
elsif l_next_value <> (to_number(:NEW.employee_number) + 1) then
hr_utility.trace('>>> l_next_value = '||to_char(l_next_value));
hr_utility.trace('>>>new employee number = '||:NEW.employee_number);
-- method is already Automatic and employee number is out of sync ...
hr_utility.set_location(g_db_trigger,110);
RAISE e_NumOutSync;
end if; -- next value check
end if; -- method check
END IF; -- current emp flag
-- -------------------------------------------------------------------------+
-- Processing APPLICANTS +
-- -------------------------------------------------------------------------+
IF :NEW.CURRENT_APPLICANT_FLAG = 'Y'
AND ((inserting) OR
(updating and :OLD.person_type_id <> :NEW.person_type_id)
)
AND not person_exists(:NEW.person_id, 'APL')
THEN
hr_utility.set_location(g_db_trigger,121);
hr_utility.trace('>>> method found : '||l_apl_method);
if l_apl_method = 'A' then
hr_utility.set_location(g_db_trigger,125);
l_next_value := get_next_value(:NEW.business_group_id,'APL');
if l_next_value < 0 then -- global sequence has been used
hr_utility.set_location('Leaving:'||g_db_trigger,998);
return;
elsif l_next_value <> (to_number(:NEW.applicant_number) + 1) then
hr_utility.trace('>>> l_next_value = '||to_char(l_next_value));
hr_utility.trace('>>>new applicant number = '||:NEW.applicant_number);
-- method is already Automatic and employee number is out of sync ...
hr_utility.set_location(g_db_trigger,127);
RAISE e_NumOutSync;
end if; -- next value check
end if; -- method check
END IF; -- current apl flag
-- -------------------------------------------------------------------------+
-- Processing CONTINGENT WORKERS +
-- -------------------------------------------------------------------------+
IF :NEW.CURRENT_NPW_FLAG = 'Y'
AND ((inserting) OR
(updating and :OLD.person_type_id <> :NEW.person_type_id)
)
AND not person_exists(:NEW.person_id, 'CWK')
THEN
hr_utility.set_location(g_db_trigger,131);
hr_utility.trace('>>> method found : '||l_cwk_method);
if l_cwk_method = 'A' then
hr_utility.set_location(g_db_trigger,135);
l_next_value := get_next_value(:NEW.business_group_id,'CWK');
if l_next_value < 0 then
hr_utility.set_location('Leaving:'||g_db_trigger,999);
return;
elsif l_next_value <> (to_number(:NEW.npw_number) + 1) then
hr_utility.trace('>>> l_next_value = '||to_char(l_next_value));
hr_utility.trace('>>>new CWK number = '||:NEW.npw_number);
-- method is already Automatic and employee number is out of sync ...
hr_utility.set_location(g_db_trigger,137);
RAISE e_NumOutSync;
end if; -- next value check
end if; -- method check
END IF;
hr_utility.set_location('Leaving:'||g_db_trigger,140);
END IF; --6365083
EXCEPTION
when VALUE_ERROR then
-- error converting the employee number to numeric
-- raise error since it is already different from "sequence"
hr_utility.set_message(800,'PER_289850_NUM_OUT_OF_SYNC');
hr_utility.raise_error;
when e_NumOutSync then
--
-- number does not match "sequence"
--
hr_utility.set_message(800,'PER_289850_NUM_OUT_OF_SYNC');
hr_utility.raise_error;
when OTHERS then
-- error: abnormal condition
RAISE;
END PER_ALL_PEOPLE_F_ARIU;
[Home] [Help]