1 PACKAGE BODY per_es_update_person AS
2 /* $Header: peesperp.pkb 120.6 2006/09/14 16:35:03 mgettins noship $ */
3
4 PROCEDURE update_es_person (p_person_id NUMBER
5 ,p_effective_date DATE
6 ,p_last_name VARCHAR2
7 ,p_first_name VARCHAR2
8 ,p_national_identifier VARCHAR2
9 ,p_per_information1 VARCHAR2
10 ,p_per_information2 VARCHAR2
11 ,p_per_information3 VARCHAR2) IS
12
13 CURSOR get_lookup_type(p_per_information2 VARCHAR2) IS
14 SELECT lookup_code
15 FROM hr_lookups
16 WHERE lookup_type='ES_IDENTIFIER_TYPE'
17 AND lookup_code=p_per_information2;
18
19 CURSOR csr_person_type IS
20 SELECT 'Y' FROM DUAL
21 WHERE EXISTS(SELECT ppt.system_person_type
22 FROM per_person_types ppt
23 ,per_person_type_usages_f ptu
24 ,fnd_sessions ses
25 WHERE ptu.person_id = p_person_id
26 AND ppt.person_type_id = ptu.person_type_id
27 AND ses.session_id=userenv('sessionid')
28 AND ses.effective_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
29 AND ppt.system_person_type IN ('EMP','EX_EMP','CWK','EX_CWK'));
30 --
31 CURSOR get_identifier_type (p_person_id VARCHAR2
32 ,p_effective_date DATE) IS
33 SELECT per_information2
34 FROM per_all_people_f
35 WHERE person_id=p_person_id
36 AND p_effective_date BETWEEN effective_start_date
37 AND effective_end_date;
38
39 CURSOR get_identifier_value (p_person_id VARCHAR2
40 ,p_effective_date DATE) IS
41 SELECT p_per_information3
42 FROM per_all_people_f
43 WHERE person_id=p_person_id
44 AND p_effective_date BETWEEN effective_start_date
45 AND effective_end_date;
46
47 CURSOR get_national_ident (p_person_id VARCHAR2
48 ,p_effective_date DATE) IS
49 SELECT national_identifier
50 FROM per_all_people_f
51 WHERE person_id=p_person_id
52 AND p_effective_date BETWEEN effective_start_date
53 AND effective_end_date;
54 --
55
56 l_person_type per_person_types.system_person_type%TYPE;
57 l_seed_person_type per_person_types.seeded_person_type_key%TYPE;
58 l_chk VARCHAR2(1);
59 l_chk_identifier VARCHAR2(1);
60 --
61 l_identifier_type hr_lookups.lookup_code%TYPE;
62 l_identifier_value per_all_people_f.per_information3%TYPE;
63 l_proc VARCHAR2(72);
64 l_national_identifier per_all_people_f.national_identifier%TYPE;
65 l_check_val VARCHAR2(1);
66 BEGIN
67 --
68 -- Added for GSI Bug 5472781
69 --
70 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
71 --
72 l_proc := 'PER_ES_UPDATE_PERSON.UPDATE_ES_PERSON';
73 OPEN csr_person_type;
74 FETCH csr_person_type into l_chk;
75 CLOSE csr_person_type;
76
77 --
78 --IF l_person_type <> 'OTHER' AND l_seed_person_type <> 'CONTACT' THEN
79 -- Validate identifier Type
80 l_identifier_type := p_per_information2;
81 l_identifier_value := p_per_information3;
82 -- p_per_information2 and p_per_information3 will be passed as NULL wen called
83 --from ppl mgt template and would be defaulted to g_varchar2 wen called from self service
84 --the below condition handles both the scenarios
85 IF p_per_information2 = hr_api.g_varchar2 AND p_per_information3 = hr_api.g_varchar2 THEN
86 null;
87 ELSE
88 l_identifier_type := p_per_information2;
89 l_identifier_value := p_per_information3;
90 --
91
92 IF p_per_information2 = hr_api.g_varchar2 THEN
93 OPEN get_identifier_type(p_person_id,p_effective_date);
94 FETCH get_identifier_type INTO l_identifier_type;
95 CLOSE get_identifier_type;
96 ELSIF p_per_information3 = hr_api.g_varchar2 THEN
97 OPEN get_identifier_value(p_person_id,p_effective_date);
98 FETCH get_identifier_value INTO l_identifier_value;
99 CLOSE get_identifier_value;
100 END IF;
101 --
102
103 IF l_identifier_type IS NOT NULL AND
104 l_identifier_value IS NULL THEN
105 OPEN get_lookup_type(l_identifier_type);
106 FETCH get_lookup_type into l_identifier_type;
107 IF get_lookup_type%NOTFOUND THEN
108 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
109 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_TYPE'));
110 hr_utility.raise_error;
111 END IF;
112 CLOSE get_lookup_type;
113
114 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
115 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_IDENTIFIER_TYPE',l_identifier_type));
116 hr_utility.raise_error;
117 END IF;
118 --
119 IF l_identifier_type IS NULL AND
120 l_identifier_value IS NOT NULL THEN
121 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
122 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_TYPE'));
123 hr_utility.raise_error;
124 END IF;
125 --
126 IF FND_PROFILE.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' AND ---Bug No 4718049
127 l_identifier_type IS NOT NULL AND l_identifier_value IS NOT NULL THEN
128 l_chk_identifier := hr_es_utility.validate_identifier(l_identifier_type,l_identifier_value);
129 END IF;
130 --
131 END IF;
132 --
133 IF p_national_identifier = hr_api.g_varchar2 AND l_identifier_value = hr_api.g_varchar2 THEN
134 null;
135 ELSE
136 l_national_identifier := p_national_identifier;
137 IF p_national_identifier = hr_api.g_varchar2 THEN
138 OPEN get_national_ident(p_person_id,p_effective_date);
139 FETCH get_national_ident INTO l_national_identifier;
140 CLOSE get_national_ident;
141 END IF;
142 IF l_identifier_value = hr_api.g_varchar2 THEN
143 OPEN get_identifier_value(p_person_id,p_effective_date);
144 FETCH get_identifier_value INTO l_identifier_value;
145 CLOSE get_identifier_value;
146 END IF;
147 IF l_chk = 'Y' THEN
148 IF FND_PROFILE.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' AND --- Bug No 4718049
149 l_national_identifier IS NULL AND l_identifier_value IS NULL THEN
150 hr_utility.set_message(800, 'HR_ES_REQ_NIF_IDT_MISSING');
151 hr_utility.raise_error;
152 END IF;
153 END IF;
154 /*IF p_national_identifier is not null THEN
155 l_national_identifier := hr_es_utility.check_NIF(p_national_identifier);
156 END IF;*/
157 IF l_national_identifier IS NOT NULL AND l_identifier_value IS NOT NULL THEN
158 IF FND_PROFILE.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' AND --- Bug No 4718049
159 substr(l_national_identifier,1,8) <> l_identifier_value AND
160 l_identifier_type = 'DNI' THEN
161 hr_utility.set_message(800,'HR_ES_INVALID_DNI_NIF');
162 hr_utility.raise_error;
163 END IF;
164 END IF;
165 END IF;
166 --
167 hr_api.mandatory_arg_error
168 (p_api_name => l_proc,
169 p_argument => hr_general.decode_lookup('ES_FORM_LABELS','LAST_NAME_1'),
170 p_argument_value => p_last_name
171 );
172 --
173 IF length(p_last_name)>40 THEN
174 hr_utility.set_message(800, 'HR_289712_UTF8_LENGTH_EXCEEDED');
175 hr_utility.set_message_token('COLUMN_NAME',hr_general.decode_lookup('ES_FORM_LABELS','LAST_NAME_1'));
176 hr_utility.set_message_token('COLUMN_VALUE',p_last_name);
177 hr_utility.set_message_token('MAX_LENGTH','40');
178 hr_utility.raise_error;
179 END IF;
180 --
181 --
182 IF p_per_information1 is not null THEN
183 IF length(p_per_information1)>40 THEN
184 hr_utility.set_message(800, 'HR_289712_UTF8_LENGTH_EXCEEDED');
185 hr_utility.set_message_token('COLUMN_NAME',hr_general.decode_lookup('ES_FORM_LABELS','LAST_NAME_2'));
186 hr_utility.set_message_token('COLUMN_VALUE',p_per_information1);
187 hr_utility.set_message_token('MAX_LENGTH','40');
188 hr_utility.raise_error;
189 END IF;
190 END IF;
191 --
192 IF p_first_name is not null THEN
193 IF length(p_first_name)>40 THEN
194 hr_utility.set_message(800, 'HR_289712_UTF8_LENGTH_EXCEEDED');
195 hr_utility.set_message_token('COLUMN_NAME',hr_general.decode_lookup('ES_FORM_LABELS','NAME'));
196 hr_utility.set_message_token('COLUMN_VALUE',p_first_name);
197 hr_utility.set_message_token('MAX_LENGTH','40');
198 hr_utility.raise_error;
199 END IF;
200 END IF;
201 END IF;
202 END update_es_person;
203 --
204 END PER_ES_UPDATE_PERSON;
[Home] [Help]