[Home] [Help]
PACKAGE BODY: APPS.PQP_HRTCA_SYNCHRONIZATION
Source
1 Package Body PQP_HRTCA_Synchronization AS
2 /* $Header: pqphrtcasync.pkb 120.0 2005/05/29 02:22:06 appldev noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_pkg CONSTANT Varchar2(150) := 'PQP_HRTCA_Synchronization.';
8 -- =============================================================================
9 -- ~ Package Body Cursor variables:
10 -- =============================================================================
11 -- Cursor to check if person is student
12 CURSOR csr_stu (c_person_id IN Number) IS
13 SELECT pei.pei_information5
14 FROM per_people_extra_info pei
15 WHERE pei.information_type = 'PQP_OSS_PERSON_DETAILS'
16 AND pei.pei_information_category = 'PQP_OSS_PERSON_DETAILS'
17 AND pei.person_id = c_person_id;
18
19 -- Get person party id
20 CURSOR csr_partyid (c_person_id IN Number) IS
21 SELECT ppf.party_id,pbg.legislation_code
22 FROM per_all_people_f ppf
23 ,per_business_groups_perf pbg
24 WHERE ppf.person_id = c_person_id
25 AND pbg.business_group_id = ppf.business_group_id;
26
27 -- Cursor to get the leg. code
28 CURSOR csr_bg_code (c_bg_grp_id IN Number) IS
29 SELECT pbg.legislation_code
30 FROM per_business_groups pbg
31 WHERE pbg.business_group_id = c_bg_grp_id;
32
33 -- =============================================================================
34 -- ~ Pei_DDF_Ins:
35 -- =============================================================================
36 PROCEDURE Pei_DDF_Ins
37 (p_person_extra_info_id IN Number
38 ,p_person_id IN Number
39 ,p_information_type IN Varchar2
40 -- DDF
41 ,p_pei_information_category IN Varchar2
42 ,p_pei_information1 IN Varchar2
43 ,p_pei_information2 IN Varchar2
44 ,p_pei_information3 IN Varchar2
45 ,p_pei_information4 IN Varchar2
46 ,p_pei_information5 IN Varchar2
47 ,p_pei_information6 IN Varchar2
48 ,p_pei_information7 IN Varchar2
49 ,p_pei_information8 IN Varchar2
50 ,p_pei_information9 IN Varchar2
51 ,p_pei_information10 IN Varchar2
52 ,p_pei_information11 IN Varchar2
53 ,p_pei_information12 IN Varchar2
54 ,p_pei_information13 IN Varchar2
55 ,p_pei_information14 IN Varchar2
56 ,p_pei_information15 IN Varchar2
57 ,p_pei_information16 IN Varchar2
58 ,p_pei_information17 IN Varchar2
59 ,p_pei_information18 IN Varchar2
60 ,p_pei_information19 IN Varchar2
61 ,p_pei_information20 IN Varchar2
62 ,p_pei_information21 IN Varchar2
63 ,p_pei_information22 IN Varchar2
64 ,p_pei_information23 IN Varchar2
65 ,p_pei_information24 IN Varchar2
66 ,p_pei_information25 IN Varchar2
67 ,p_pei_information26 IN Varchar2
68 ,p_pei_information27 IN Varchar2
69 ,p_pei_information28 IN Varchar2
70 ,p_pei_information29 IN Varchar2
71 ,p_pei_information30 IN Varchar2
72 ) AS
73 l_return_flag Boolean;
74 l_party_id per_all_people_f.party_id%TYPE;
75 l_bg_code per_business_groups.legislation_code%TYPE;
76 l_Stu_OSSData_Sync Varchar2(5);
77
78 l_pei_rec per_people_extra_info%ROWTYPE;
79 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Pei_DDF_Ins';
80
81 BEGIN
82 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
83 -- Check if the information type being created is for a student employee
84 -- If yes, then check 1) if the data-sync flag is set to Y
85 -- 2) profile option for Person info is Y
86 OPEN csr_partyid (c_person_id => p_person_id);
87 FETCH csr_partyid INTO l_party_id,l_bg_code;
88 CLOSE csr_partyid;
89
90 OPEN csr_stu (c_person_id => p_person_id);
91 FETCH csr_stu INTO l_Stu_OSSData_Sync;
92 CLOSE csr_stu;
93
94 IF Nvl(l_Stu_OSSData_Sync,'-1') <> 'Y' OR
95 Nvl(l_bg_code,'-$') <> 'US' OR
96 Nvl(Fnd_Profile.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'N' OR
97 l_party_id IS NULL
98 THEN
99 l_return_flag := TRUE;
100 END IF;
101
102 IF l_return_flag THEN
103 RETURN;
104 END IF;
105
106 l_pei_rec.person_extra_info_id := p_person_extra_info_id;
107 l_pei_rec.person_id := p_person_id;
108 l_pei_rec.information_type := p_information_type;
109 -- DDF
110 l_pei_rec.pei_information_category := p_pei_information_category;
111 l_pei_rec.pei_information1 := p_pei_information1;
112 l_pei_rec.pei_information2 := p_pei_information2;
113 l_pei_rec.pei_information3 := p_pei_information3;
114 l_pei_rec.pei_information4 := p_pei_information4;
115 l_pei_rec.pei_information5 := p_pei_information5;
116 l_pei_rec.pei_information6 := p_pei_information6;
117 l_pei_rec.pei_information7 := p_pei_information7;
118 l_pei_rec.pei_information8 := p_pei_information8;
119 l_pei_rec.pei_information9 := p_pei_information9;
120 l_pei_rec.pei_information10 := p_pei_information10;
121 l_pei_rec.pei_information11 := p_pei_information11;
122 l_pei_rec.pei_information12 := p_pei_information12;
123 l_pei_rec.pei_information13 := p_pei_information13;
124 l_pei_rec.pei_information14 := p_pei_information14;
125 l_pei_rec.pei_information15 := p_pei_information15;
126 l_pei_rec.pei_information16 := p_pei_information16;
127 l_pei_rec.pei_information17 := p_pei_information17;
128 l_pei_rec.pei_information18 := p_pei_information18;
129 l_pei_rec.pei_information19 := p_pei_information19;
130 l_pei_rec.pei_information20 := p_pei_information20;
131 l_pei_rec.pei_information21 := p_pei_information21;
132 l_pei_rec.pei_information22 := p_pei_information22;
133 l_pei_rec.pei_information23 := p_pei_information23;
134 l_pei_rec.pei_information24 := p_pei_information24;
135 l_pei_rec.pei_information25 := p_pei_information25;
136 l_pei_rec.pei_information26 := p_pei_information26;
137 l_pei_rec.pei_information27 := p_pei_information27;
138 l_pei_rec.pei_information28 := p_pei_information28;
139 l_pei_rec.pei_information29 := p_pei_information29;
140 l_pei_rec.pei_information30 := p_pei_information30;
141 IF l_bg_code = 'US' THEN
142 IF p_information_type = 'PER_US_PASSPORT_DETAILS' THEN
143 -- Call the OSS Passport pkg
144 Pqp_Hrtca_Integration.InsUpd_OSS_PassPort
145 (p_person_id => p_person_id
146 ,p_party_id => l_party_id
147 ,p_action => 'INSERT'
148 ,p_pei_info_rec_old => NULL
149 ,p_pei_info_rec_new => l_pei_rec
150 );
151 ELSIF p_information_type = 'PER_US_VISA_DETAILS' THEN
152 -- Call the OSS Visa pkg
153 Pqp_Hrtca_Integration.InsUpd_OSS_Visa
154 (p_person_id => p_person_id
155 ,p_party_id => l_party_id
156 ,p_action => 'INSERT'
157 ,p_pei_info_rec_old => NULL
158 ,p_pei_info_rec_new => l_pei_rec
159 );
160 ELSIF p_information_type = 'PER_US_VISIT_HISTORY' THEN
161 -- Call the OSS Visa Visit History pkg
162 Pqp_Hrtca_Integration.InsUpd_OSS_VisitHistory
163 (p_person_id => p_person_id
164 ,p_party_id => l_party_id
165 ,p_action => 'INSERT'
166 ,p_pei_info_rec_old => NULL
167 ,p_pei_info_rec_new => l_pei_rec
168 );
169 END IF;
170 END IF;
171 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
172
173 END Pei_DDF_Ins;
174 -- =============================================================================
175 -- ~ Pei_DDF_Upd:
176 -- =============================================================================
177 PROCEDURE Pei_DDF_Upd
178 (-- New PEI Information
179 p_person_extra_info_id IN Number
180 ,p_person_id IN Number
181 ,p_information_type IN Varchar2
182 -- New DDF
183 ,p_pei_information_category IN Varchar2
184 ,p_pei_information1 IN Varchar2
185 ,p_pei_information2 IN Varchar2
186 ,p_pei_information3 IN Varchar2
187 ,p_pei_information4 IN Varchar2
188 ,p_pei_information5 IN Varchar2
189 ,p_pei_information6 IN Varchar2
190 ,p_pei_information7 IN Varchar2
191 ,p_pei_information8 IN Varchar2
192 ,p_pei_information9 IN Varchar2
193 ,p_pei_information10 IN Varchar2
194 ,p_pei_information11 IN Varchar2
195 ,p_pei_information12 IN Varchar2
196 ,p_pei_information13 IN Varchar2
197 ,p_pei_information14 IN Varchar2
198 ,p_pei_information15 IN Varchar2
199 ,p_pei_information16 IN Varchar2
200 ,p_pei_information17 IN Varchar2
201 ,p_pei_information18 IN Varchar2
202 ,p_pei_information19 IN Varchar2
203 ,p_pei_information20 IN Varchar2
204 ,p_pei_information21 IN Varchar2
205 ,p_pei_information22 IN Varchar2
206 ,p_pei_information23 IN Varchar2
207 ,p_pei_information24 IN Varchar2
208 ,p_pei_information25 IN Varchar2
209 ,p_pei_information26 IN Varchar2
210 ,p_pei_information27 IN Varchar2
211 ,p_pei_information28 IN Varchar2
212 ,p_pei_information29 IN Varchar2
213 ,p_pei_information30 IN Varchar2
214 -- Old PEI Information
215 ,p_person_id_o IN Number
216 ,p_information_type_o IN Varchar2
217 ,p_pei_attribute_category_o IN Varchar2
218 -- Old DDF
219 ,p_pei_information_category_o IN Varchar2
220 ,p_pei_information1_o IN Varchar2
221 ,p_pei_information2_o IN Varchar2
222 ,p_pei_information3_o IN Varchar2
223 ,p_pei_information4_o IN Varchar2
224 ,p_pei_information5_o IN Varchar2
225 ,p_pei_information6_o IN Varchar2
226 ,p_pei_information7_o IN Varchar2
227 ,p_pei_information8_o IN Varchar2
228 ,p_pei_information9_o IN Varchar2
229 ,p_pei_information10_o IN Varchar2
230 ,p_pei_information11_o IN Varchar2
231 ,p_pei_information12_o IN Varchar2
232 ,p_pei_information13_o IN Varchar2
233 ,p_pei_information14_o IN Varchar2
234 ,p_pei_information15_o IN Varchar2
235 ,p_pei_information16_o IN Varchar2
236 ,p_pei_information17_o IN Varchar2
237 ,p_pei_information18_o IN Varchar2
238 ,p_pei_information19_o IN Varchar2
239 ,p_pei_information20_o IN Varchar2
240 ,p_pei_information21_o IN Varchar2
241 ,p_pei_information22_o IN Varchar2
242 ,p_pei_information23_o IN Varchar2
243 ,p_pei_information24_o IN Varchar2
244 ,p_pei_information25_o IN Varchar2
245 ,p_pei_information26_o IN Varchar2
246 ,p_pei_information27_o IN Varchar2
247 ,p_pei_information28_o IN Varchar2
248 ,p_pei_information29_o IN Varchar2
249 ,p_pei_information30_o IN Varchar2
250 ) AS
251
252 l_return_flag Boolean;
253 l_party_id per_all_people_f.party_id%TYPE;
254 l_Stu_OSSData_Sync Varchar2(5);
255 l_bg_code per_business_groups.legislation_code%TYPE;
256 l_pei_rec_new per_people_extra_info%ROWTYPE;
257 l_pei_rec_old per_people_extra_info%ROWTYPE;
258 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Pei_DDF_Upd';
259
260 BEGIN
261 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
262 -- Check if the information type being created is for a student employee
263 -- If yes, then check 1) if the data-sync flag is set to N
264 -- 2) profile option for Person info is Y
265 OPEN csr_partyid (c_person_id => p_person_id);
266 FETCH csr_partyid INTO l_party_id,l_bg_code;
267 CLOSE csr_partyid;
268 OPEN csr_stu (c_person_id => p_person_id);
269 FETCH csr_stu INTO l_Stu_OSSData_Sync;
270 CLOSE csr_stu;
271
272 IF Nvl(l_Stu_OSSData_Sync,'-1') <> 'Y' OR
273 Nvl(l_bg_code,'-$') <> 'US' OR
274 Nvl(Fnd_Profile.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'N' OR
275 l_party_id IS NULL
276 THEN
277 l_return_flag := TRUE;
278 END IF;
279
280 IF l_return_flag THEN
281 RETURN;
282 END IF;
283
284 l_pei_rec_old.person_extra_info_id := p_person_extra_info_id;
285 l_pei_rec_old.person_id := p_person_id_o;
286 l_pei_rec_old.information_type := p_information_type_o;
287 -- DDF
288 l_pei_rec_old.pei_information_category := p_pei_information_category_o;
289 l_pei_rec_old.pei_information1 := p_pei_information1_o;
290 l_pei_rec_old.pei_information2 := p_pei_information2_o;
291 l_pei_rec_old.pei_information3 := p_pei_information3_o;
292 l_pei_rec_old.pei_information4 := p_pei_information4_o;
293 l_pei_rec_old.pei_information5 := p_pei_information5_o;
294 l_pei_rec_old.pei_information6 := p_pei_information6_o;
295 l_pei_rec_old.pei_information7 := p_pei_information7_o;
296 l_pei_rec_old.pei_information8 := p_pei_information8_o;
297 l_pei_rec_old.pei_information9 := p_pei_information9_o;
298 l_pei_rec_old.pei_information10 := p_pei_information10_o;
299 l_pei_rec_old.pei_information11 := p_pei_information11_o;
300 l_pei_rec_old.pei_information12 := p_pei_information12_o;
301 l_pei_rec_old.pei_information13 := p_pei_information13_o;
302 l_pei_rec_old.pei_information14 := p_pei_information14_o;
303 l_pei_rec_old.pei_information15 := p_pei_information15_o;
304 l_pei_rec_old.pei_information16 := p_pei_information16_o;
305 l_pei_rec_old.pei_information17 := p_pei_information17_o;
306 l_pei_rec_old.pei_information18 := p_pei_information18_o;
307 l_pei_rec_old.pei_information19 := p_pei_information19_o;
308 l_pei_rec_old.pei_information20 := p_pei_information20_o;
309 l_pei_rec_old.pei_information21 := p_pei_information21_o;
310 l_pei_rec_old.pei_information22 := p_pei_information22_o;
311 l_pei_rec_old.pei_information23 := p_pei_information23_o;
312 l_pei_rec_old.pei_information24 := p_pei_information24_o;
313 l_pei_rec_old.pei_information25 := p_pei_information25_o;
314 l_pei_rec_old.pei_information26 := p_pei_information26_o;
315 l_pei_rec_old.pei_information27 := p_pei_information27_o;
316 l_pei_rec_old.pei_information28 := p_pei_information28_o;
317 l_pei_rec_old.pei_information29 := p_pei_information29_o;
318 l_pei_rec_old.pei_information30 := p_pei_information30_o;
319
320 l_pei_rec_new.person_extra_info_id := p_person_extra_info_id;
321 l_pei_rec_new.person_id := p_person_id;
322 l_pei_rec_new.information_type := p_information_type;
323 -- DDF
324 l_pei_rec_new.pei_information_category := p_pei_information_category;
325 l_pei_rec_new.pei_information1 := p_pei_information1;
326 l_pei_rec_new.pei_information2 := p_pei_information2;
327 l_pei_rec_new.pei_information3 := p_pei_information3;
328 l_pei_rec_new.pei_information4 := p_pei_information4;
329 l_pei_rec_new.pei_information5 := p_pei_information5;
330 l_pei_rec_new.pei_information6 := p_pei_information6;
331 l_pei_rec_new.pei_information7 := p_pei_information7;
332 l_pei_rec_new.pei_information8 := p_pei_information8;
333 l_pei_rec_new.pei_information9 := p_pei_information9;
334 l_pei_rec_new.pei_information10 := p_pei_information10;
335 l_pei_rec_new.pei_information11 := p_pei_information11;
336 l_pei_rec_new.pei_information12 := p_pei_information12;
337 l_pei_rec_new.pei_information13 := p_pei_information13;
338 l_pei_rec_new.pei_information14 := p_pei_information14;
339 l_pei_rec_new.pei_information15 := p_pei_information15;
340 l_pei_rec_new.pei_information16 := p_pei_information16;
341 l_pei_rec_new.pei_information17 := p_pei_information17;
342 l_pei_rec_new.pei_information18 := p_pei_information18;
343 l_pei_rec_new.pei_information19 := p_pei_information19;
344 l_pei_rec_new.pei_information20 := p_pei_information20;
345 l_pei_rec_new.pei_information21 := p_pei_information21;
346 l_pei_rec_new.pei_information22 := p_pei_information22;
347 l_pei_rec_new.pei_information23 := p_pei_information23;
348 l_pei_rec_new.pei_information24 := p_pei_information24;
349 l_pei_rec_new.pei_information25 := p_pei_information25;
350 l_pei_rec_new.pei_information26 := p_pei_information26;
351 l_pei_rec_new.pei_information27 := p_pei_information27;
352 l_pei_rec_new.pei_information28 := p_pei_information28;
353 l_pei_rec_new.pei_information29 := p_pei_information29;
354 l_pei_rec_new.pei_information30 := p_pei_information30;
355
356 IF l_bg_code = 'US' THEN
357 IF p_information_type = 'PER_US_PASSPORT_DETAILS' THEN
358 -- Call the OSS Passport pkg
359 Pqp_Hrtca_Integration.InsUpd_OSS_PassPort
360 (p_person_id => p_person_id
361 ,p_party_id => l_party_id
362 ,p_action => 'UPDATE'
363 ,p_pei_info_rec_old => l_pei_rec_old
364 ,p_pei_info_rec_new => l_pei_rec_new
365 );
366 ELSIF p_information_type = 'PER_US_VISA_DETAILS' THEN
367 -- Call the OSS Visa pkg
368 Pqp_Hrtca_Integration.InsUpd_OSS_Visa
369 (p_person_id => p_person_id
370 ,p_party_id => l_party_id
371 ,p_action => 'UPDATE'
372 ,p_pei_info_rec_old => l_pei_rec_old
373 ,p_pei_info_rec_new => l_pei_rec_new
374 );
375 ELSIF p_information_type = 'PER_US_VISIT_HISTORY' THEN
376 Pqp_Hrtca_Integration.InsUpd_OSS_VisitHistory
377 (p_person_id => p_person_id
378 ,p_party_id => l_party_id
379 ,p_action => 'UPDATE'
380 ,p_pei_info_rec_old => l_pei_rec_old
381 ,p_pei_info_rec_new => l_pei_rec_new
382 );
383 END IF;
384 END IF;
385 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
386
387 END Pei_DDF_Upd;
388 -- =============================================================================
389 -- ~ Pei_DDF_Del:
390 -- =============================================================================
391 PROCEDURE Pei_DDF_Del
392 (p_person_id_o IN Number
393 ,p_information_type_o IN Varchar2
394 ,p_pei_attribute_category_o IN Varchar2
395 -- Old DDF
396 ,p_pei_information_category_o IN Varchar2
397 ,p_pei_information1_o IN Varchar2
398 ,p_pei_information2_o IN Varchar2
399 ,p_pei_information3_o IN Varchar2
400 ,p_pei_information4_o IN Varchar2
401 ,p_pei_information5_o IN Varchar2
402 ,p_pei_information6_o IN Varchar2
403 ,p_pei_information7_o IN Varchar2
404 ,p_pei_information8_o IN Varchar2
405 ,p_pei_information9_o IN Varchar2
406 ,p_pei_information10_o IN Varchar2
407 ,p_pei_information11_o IN Varchar2
408 ,p_pei_information12_o IN Varchar2
409 ,p_pei_information13_o IN Varchar2
410 ,p_pei_information14_o IN Varchar2
411 ,p_pei_information15_o IN Varchar2
412 ,p_pei_information16_o IN Varchar2
413 ,p_pei_information17_o IN Varchar2
414 ,p_pei_information18_o IN Varchar2
415 ,p_pei_information19_o IN Varchar2
416 ,p_pei_information20_o IN Varchar2
417 ,p_pei_information21_o IN Varchar2
418 ,p_pei_information22_o IN Varchar2
419 ,p_pei_information23_o IN Varchar2
420 ,p_pei_information24_o IN Varchar2
421 ,p_pei_information25_o IN Varchar2
422 ,p_pei_information26_o IN Varchar2
423 ,p_pei_information27_o IN Varchar2
424 ,p_pei_information28_o IN Varchar2
425 ,p_pei_information29_o IN Varchar2
426 ,p_pei_information30_o IN Varchar2
427 ) AS
428 l_pei_rec per_people_extra_info%ROWTYPE;
429 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Pei_DDF_Del';
430 BEGIN
431 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
432 NULL;
433 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
434 END Pei_DDF_Del;
435
436 End PQP_HRTCA_Synchronization;