[Home] [Help]
PACKAGE BODY: APPS.PQP_HRTCA_INTEGRATION
Source
1 Package Body PQP_HRTCA_Integration AS
2 /* $Header: pqphrtcaintg.pkb 120.3 2007/07/27 12:29:18 dbansal noship $ */
3
4 -- =============================================================================
5 -- ~ Global variables:
6 -- =============================================================================
7 g_pkg CONSTANT Varchar2(150) := 'PQP_HRTCA_Integration.';
8 g_adddt_sql CONSTANT Varchar2(1000) :=
9 'SELECT ihs.start_date ' ||
10 ' ,ihs.end_date ' ||
11 ' ,hps.identifying_address_flag ' ||
12 ' FROM hz_party_sites hps ' ||
13 ' ,igs_pe_hz_pty_sites ihs ' ||
14 ' WHERE ihs.party_site_id(+) = hps.party_site_id ' ||
15 ' AND hps.party_id = :c_party_id ' ||
16 ' AND hps.location_id = :c_location_id ' ||
17 ' AND hps.identifying_address_flag = '||'''Y''';
18
19
20 -- Cursor to get the existing address details to update
21 g_hz_add_sql CONSTANT Varchar2(1000) :=
22 ' SELECT hzl.location_id
23 ,hps.party_site_id
24 ,hzl.last_update_date
25 ,hps.object_version_number
26 ,hzl.object_version_number
27 ,hzl.ROWID
28 FROM hz_party_sites hps
29 ,hz_locations hzl
30 WHERE hps.party_id(+) = :c_party_id
31 AND hzl.location_id(+) = hps.location_id
32 AND hps.identifying_address_flag(+) = '||'''Y''';
33
34 g_business_group_id Number;
35 g_leg_code Varchar2(5);
36 g_effective_date Date;
37
38 -- =============================================================================
39 -- ~ SQL Cursor's and variables:
40 -- =============================================================================
41 -- Get the mapping value based on the info. category
42 CURSOR csr_TCA_Map (c_info_category IN Varchar2
43 ,c_bg_grp_id IN Number
44 ,c_bg_grp_leg_code IN Varchar2) IS
45 SELECT *
46 FROM pqp_configuration_values pcv
47 WHERE pcv.pcv_information_category = c_info_category
48 AND ((pcv.business_group_id = c_bg_grp_id) OR
49 (pcv.legislation_code = c_bg_grp_leg_code AND
50 pcv.business_group_id IS NULL) OR
51 (pcv.business_group_id IS NULL AND
52 pcv.legislation_code IS NULL)
53 );
54
55 -- Get the meaning and code for a lookup type
56 CURSOR csr_meaning_code (c_lookup_type IN Varchar2
57 ,c_meaning IN Varchar2
58 ,c_effective_date IN Date) IS
59 SELECT hrl.meaning
60 ,hrl.lookup_code
61 FROM hr_lookups hrl
62 WHERE hrl.lookup_type = c_lookup_type
63 AND (Upper(hrl.meaning) = Upper(c_meaning)
64 OR
65 Upper(hrl.lookup_code) = Upper(c_meaning)
66 )
67 AND Trunc(g_effective_date)
68 BETWEEN Nvl(hrl.start_date_active,Trunc(g_effective_date))
69 AND Nvl(hrl.end_date_active, Trunc(g_effective_date));
70 -- Cursor to get the country codes mapping
71 CURSOR csr_cntry_code
72 (c_country_code IN Varchar2
73 ,c_map_to IN Varchar2) IS
74 SELECT hrl.lookup_code ins_code
75 ,hrl.meaning ins_mapping
76 ,irs.lookup_code irs_code
77 ,irs.meaning irs_meaning
78 FROM hr_lookups hrl,
79 hr_lookups irs
80 WHERE hrl.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
81 AND hrl.enabled_flag = 'Y'
82 AND irs.lookup_type = 'PER_US_COUNTRY_CODE'
83 AND irs.enabled_flag = 'Y'
84 AND irs.lookup_code = substrb(hrl.meaning,-2)
85 AND ( (c_map_to = 'HR_TO_OSS' AND
86 hrl.lookup_code = c_country_code)
87 OR
88 (c_map_to = 'OSS_TO_HR' AND
89 irs.lookup_code = c_country_code)
90 );
91
92 -- Cursor to get the leg. code
93 CURSOR csr_bg_code (c_bg_grp_id IN Number) IS
94 SELECT pbg.legislation_code
95 FROM per_business_groups pbg
96 WHERE pbg.business_group_id = c_bg_grp_id;
97
98 -- Cursor to get the TCA location
99 CURSOR csr_hz_loc (c_location_id IN Number) IS
100 SELECT *
101 FROM hz_locations hzl
102 WHERE hzl.location_id = c_location_id;
103
104 -- Cursor to get the party site id
105 CURSOR csr_site_id (c_party_id IN Number
106 ,c_primary_flag IN Varchar2) IS
107 SELECT *
108 FROM hz_party_sites hps
109 WHERE hps.party_id = c_party_id
110 AND hps.identifying_address_flag = c_primary_flag
111 AND hps.status = 'A';
112
113 -- Cursor to check if the Address Style context exists
114 CURSOR csr_style (c_context_code IN Varchar2) IS
115 SELECT dfc.descriptive_flex_context_code
116 FROM fnd_descr_flex_contexts dfc
117 WHERE dfc.application_id = 800
118 AND dfc.Descriptive_flexfield_name = 'Address Structure'
119 AND dfc.enabled_flag ='Y';
120
121 -- Cursor to check if local legislation is installed for a leg code
122 CURSOR csr_chk_prod (c_leg_code IN Varchar2
123 ,c_app_short_name IN Varchar2) IS
124 SELECT hli.status
125 ,hli.pi_steps_exist
126 FROM hr_legislation_installations hli
127 WHERE hli.legislation_code = c_leg_code
128 AND hli.application_short_name = c_app_short_name;
129
130 -- =============================================================================
131 -- ~ Chk_OSS_Install: Check to see if the OSS product is installed or not. This
132 -- ~ check is req. before launching the student search page.
133 -- =============================================================================
134 Function Chk_OSS_Install
135 Return Varchar2 Is
136
137 Cursor csr_install Is
138 select fpi.product_version
139 ,fpi.status
140 ,fpi.db_status
141 ,fpi.patch_level
142 ,substr(fpi.patch_level,9,1) level_char -- fix for bug 5162947.
143 from fnd_product_installations fpi
144 ,fnd_application app
145 where fpi.application_id = app.application_id
146 and app.application_short_name ='IGS';
147 l_prd_dtls csr_install%ROWTYPE;
148 l_return_value Varchar2(5);
149
150 Begin
151 l_return_value := 'N';
152 Open csr_install;
153 Fetch csr_install Into l_prd_dtls;
154 If csr_install%FOUND Then
155 If l_prd_dtls.db_status <> 'I' and
156 l_prd_dtls.status <> 'I' Then
157 Return l_return_value;
158 Else
159 /* The check has been modified based on the product version. The earlier code
160 was catering only the need for 11i. As of current only version A of R12 has been
161 released, we are avoiding writing any check based on level_char.
162 At a later point, we should add similar check for R12 level version as is being
163 currently done for 11i */
164 If substr(l_prd_dtls.product_version,1,2)='11' and l_prd_dtls.level_char < 'L' Then
165 l_return_value := 'N';
166 Return l_return_value;
167 End If;
168 l_return_value := 'Y';
169 End If;
170 End If;
171 Close csr_install;
172 Return l_return_value;
173
174 Exception
175 When Others Then
176 Return l_return_value;
177 End Chk_OSS_Install;
178
179
180 -- =============================================================================
181 -- ~ InsUpd_Per_Extra_info: Insert, Update or Delete Person Extra Information
182 -- =============================================================================
183 PROCEDURE InsUpd_Per_Extra_info
184 (p_person_id IN Number
185 ,p_business_group_id IN Number
186 ,p_validate IN Boolean DEFAULT FALSE
187 ,p_action IN Varchar2
188 ,p_extra_info_rec IN OUT NOCOPY per_people_extra_info%ROWTYPE
189 ) IS
190
191 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'InsUpd_Per_Extra_info';
192 l_error_msg Varchar2(2000);
193 BEGIN
194 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
195 IF p_action = 'CREATE' THEN
196 Hr_Person_Extra_Info_Api.Create_Person_Extra_Info
197 (p_validate => p_validate
198 ,p_person_id => p_person_id
199 -- DDF Segments
200 ,p_information_type => p_extra_info_rec.information_type
201 ,p_pei_information_category => p_extra_info_rec.pei_information_category
202 ,p_pei_information1 => p_extra_info_rec.pei_information1
203 ,p_pei_information2 => p_extra_info_rec.pei_information2
204 ,p_pei_information3 => p_extra_info_rec.pei_information3
205 ,p_pei_information4 => p_extra_info_rec.pei_information4
206 ,p_pei_information5 => p_extra_info_rec.pei_information5
207 ,p_pei_information6 => p_extra_info_rec.pei_information6
208 ,p_pei_information7 => p_extra_info_rec.pei_information7
209 ,p_pei_information8 => p_extra_info_rec.pei_information8
210 ,p_pei_information9 => p_extra_info_rec.pei_information9
211 ,p_pei_information10 => p_extra_info_rec.pei_information10
212 ,p_pei_information11 => p_extra_info_rec.pei_information11
213 ,p_pei_information12 => p_extra_info_rec.pei_information12
214 ,p_pei_information13 => p_extra_info_rec.pei_information13
215 ,p_pei_information14 => p_extra_info_rec.pei_information14
216 ,p_pei_information15 => p_extra_info_rec.pei_information15
217 ,p_pei_information16 => p_extra_info_rec.pei_information16
218 ,p_pei_information17 => p_extra_info_rec.pei_information17
219 ,p_pei_information18 => p_extra_info_rec.pei_information18
220 ,p_pei_information19 => p_extra_info_rec.pei_information19
221 ,p_pei_information20 => p_extra_info_rec.pei_information20
222 ,p_pei_information21 => p_extra_info_rec.pei_information21
223 ,p_pei_information22 => p_extra_info_rec.pei_information22
224 ,p_pei_information23 => p_extra_info_rec.pei_information23
225 ,p_pei_information24 => p_extra_info_rec.pei_information24
226 ,p_pei_information25 => p_extra_info_rec.pei_information25
227 ,p_pei_information26 => p_extra_info_rec.pei_information26
228 ,p_pei_information27 => p_extra_info_rec.pei_information27
229 ,p_pei_information28 => p_extra_info_rec.pei_information28
230 ,p_pei_information29 => p_extra_info_rec.pei_information29
231 ,p_pei_information30 => p_extra_info_rec.pei_information30
232 -- DF Segments
233 ,p_pei_attribute_category => p_extra_info_rec.pei_attribute_category
234 ,p_pei_attribute1 => p_extra_info_rec.pei_attribute1
235 ,p_pei_attribute2 => p_extra_info_rec.pei_attribute2
236 ,p_pei_attribute3 => p_extra_info_rec.pei_attribute3
237 ,p_pei_attribute4 => p_extra_info_rec.pei_attribute4
238 ,p_pei_attribute5 => p_extra_info_rec.pei_attribute5
239 ,p_pei_attribute6 => p_extra_info_rec.pei_attribute6
240 ,p_pei_attribute7 => p_extra_info_rec.pei_attribute7
241 ,p_pei_attribute8 => p_extra_info_rec.pei_attribute8
242 ,p_pei_attribute9 => p_extra_info_rec.pei_attribute9
243 ,p_pei_attribute10 => p_extra_info_rec.pei_attribute10
244 ,p_pei_attribute11 => p_extra_info_rec.pei_attribute11
245 ,p_pei_attribute12 => p_extra_info_rec.pei_attribute12
246 ,p_pei_attribute13 => p_extra_info_rec.pei_attribute13
247 ,p_pei_attribute14 => p_extra_info_rec.pei_attribute14
248 ,p_pei_attribute15 => p_extra_info_rec.pei_attribute15
249 ,p_pei_attribute16 => p_extra_info_rec.pei_attribute16
250 ,p_pei_attribute17 => p_extra_info_rec.pei_attribute17
251 ,p_pei_attribute18 => p_extra_info_rec.pei_attribute18
252 ,p_pei_attribute19 => p_extra_info_rec.pei_attribute19
253 ,p_pei_attribute20 => p_extra_info_rec.pei_attribute20
254 --
255 ,p_person_extra_info_id => p_extra_info_rec.person_extra_info_id
256 ,p_object_version_number => p_extra_info_rec.object_version_number
257 );
258 ELSIF p_action = 'UPDATE' THEN
259 Hr_Person_Extra_Info_Api.Update_Person_Extra_Info
260 (p_validate => p_validate
261 ,p_person_extra_info_id => p_extra_info_rec.person_extra_info_id
262 ,p_object_version_number => p_extra_info_rec.object_version_number
263 --
264 ,p_pei_information_category => p_extra_info_rec.pei_information_category
265 ,p_pei_information1 => p_extra_info_rec.pei_information1
266 ,p_pei_information2 => p_extra_info_rec.pei_information2
267 ,p_pei_information3 => p_extra_info_rec.pei_information3
268 ,p_pei_information4 => p_extra_info_rec.pei_information4
269 ,p_pei_information5 => p_extra_info_rec.pei_information5
270 ,p_pei_information6 => p_extra_info_rec.pei_information6
271 ,p_pei_information7 => p_extra_info_rec.pei_information7
272 ,p_pei_information8 => p_extra_info_rec.pei_information8
273 ,p_pei_information9 => p_extra_info_rec.pei_information9
274 ,p_pei_information10 => p_extra_info_rec.pei_information10
275 ,p_pei_information11 => p_extra_info_rec.pei_information11
276 ,p_pei_information12 => p_extra_info_rec.pei_information12
277 ,p_pei_information13 => p_extra_info_rec.pei_information13
278 ,p_pei_information14 => p_extra_info_rec.pei_information14
279 ,p_pei_information15 => p_extra_info_rec.pei_information15
280 ,p_pei_information16 => p_extra_info_rec.pei_information16
281 ,p_pei_information17 => p_extra_info_rec.pei_information17
282 ,p_pei_information18 => p_extra_info_rec.pei_information18
283 ,p_pei_information19 => p_extra_info_rec.pei_information19
284 ,p_pei_information20 => p_extra_info_rec.pei_information20
285 ,p_pei_information21 => p_extra_info_rec.pei_information21
286 ,p_pei_information22 => p_extra_info_rec.pei_information22
287 ,p_pei_information23 => p_extra_info_rec.pei_information23
288 ,p_pei_information24 => p_extra_info_rec.pei_information24
289 ,p_pei_information25 => p_extra_info_rec.pei_information25
290 ,p_pei_information26 => p_extra_info_rec.pei_information26
291 ,p_pei_information27 => p_extra_info_rec.pei_information27
292 ,p_pei_information28 => p_extra_info_rec.pei_information28
293 ,p_pei_information29 => p_extra_info_rec.pei_information29
294 ,p_pei_information30 => p_extra_info_rec.pei_information30
295 -- DF Segments
296 ,p_pei_attribute_category => p_extra_info_rec.pei_attribute_category
297 ,p_pei_attribute1 => p_extra_info_rec.pei_attribute1
298 ,p_pei_attribute2 => p_extra_info_rec.pei_attribute2
299 ,p_pei_attribute3 => p_extra_info_rec.pei_attribute3
300 ,p_pei_attribute4 => p_extra_info_rec.pei_attribute4
301 ,p_pei_attribute5 => p_extra_info_rec.pei_attribute5
302 ,p_pei_attribute6 => p_extra_info_rec.pei_attribute6
303 ,p_pei_attribute7 => p_extra_info_rec.pei_attribute7
304 ,p_pei_attribute8 => p_extra_info_rec.pei_attribute8
305 ,p_pei_attribute9 => p_extra_info_rec.pei_attribute9
306 ,p_pei_attribute10 => p_extra_info_rec.pei_attribute10
307 ,p_pei_attribute11 => p_extra_info_rec.pei_attribute11
308 ,p_pei_attribute12 => p_extra_info_rec.pei_attribute12
309 ,p_pei_attribute13 => p_extra_info_rec.pei_attribute13
310 ,p_pei_attribute14 => p_extra_info_rec.pei_attribute14
311 ,p_pei_attribute15 => p_extra_info_rec.pei_attribute15
312 ,p_pei_attribute16 => p_extra_info_rec.pei_attribute16
313 ,p_pei_attribute17 => p_extra_info_rec.pei_attribute17
314 ,p_pei_attribute18 => p_extra_info_rec.pei_attribute18
315 ,p_pei_attribute19 => p_extra_info_rec.pei_attribute19
316 ,p_pei_attribute20 => p_extra_info_rec.pei_attribute20
317 );
318 ELSIF p_action ='DELETE' THEN
319 Hr_Person_Extra_Info_Api.Delete_Person_Extra_Info
320 (p_validate => p_validate
321 ,p_person_extra_info_id => p_extra_info_rec.person_extra_info_id
322 ,p_object_version_number => p_extra_info_rec.object_version_number
323 );
324 END IF;
325 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
326 EXCEPTION
327 WHEN Others THEN
328 l_error_msg := Substrb(SQLERRM,1,2000);
329 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
330 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
331 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
332 Hr_Utility.raise_error;
333
334 END InsUpd_Per_Extra_info;
335 -- =============================================================================
336 -- ~ InsUpd_SIT_info:
337 -- =============================================================================
338 PROCEDURE InsUpd_SIT_info
339 (p_person_id IN Number
340 ,p_business_group_id IN Number
341 ,p_validate IN Boolean
342 ,p_effective_date IN Date
343 ,p_action IN Varchar2
344 ,p_analysis_criteria_rec IN OUT NOCOPY per_analysis_criteria%ROWTYPE
345 ,p_analyses_rec IN OUT NOCOPY per_person_analyses%ROWTYPE
346 ) IS
347
348 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'InsUpd_SIT_info';
349 l_error_msg Varchar2(2000);
350 BEGIN
351 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
352 IF p_action = 'CREATE' THEN
353 Hr_SIT_Api.Create_SIT
354 (p_validate => p_validate
355 ,p_person_id => p_person_id
356 ,p_business_group_id => p_business_group_id
357 ,p_effective_date => p_effective_date
358 ,p_id_flex_num => p_analyses_rec.id_flex_num
359 ,p_comments => Null
360 ,p_date_from => p_analyses_rec.date_from
361 ,p_date_to => p_analyses_rec.date_to
362 --
363 ,p_request_id => p_analyses_rec.request_id
364 ,p_program_application_id => p_analyses_rec.program_application_id
365 ,p_program_id => p_analyses_rec.program_id
366 ,p_program_update_date => p_analyses_rec.program_update_date
367 --
368 ,p_attribute_category => p_analyses_rec.attribute_category
369 ,p_attribute1 => p_analyses_rec.attribute1
370 ,p_attribute2 => p_analyses_rec.attribute2
371 ,p_attribute3 => p_analyses_rec.attribute3
372 ,p_attribute4 => p_analyses_rec.attribute4
373 ,p_attribute5 => p_analyses_rec.attribute5
374 ,p_attribute6 => p_analyses_rec.attribute6
375 ,p_attribute7 => p_analyses_rec.attribute7
376 ,p_attribute8 => p_analyses_rec.attribute8
377 ,p_attribute9 => p_analyses_rec.attribute9
378 ,p_attribute10 => p_analyses_rec.attribute10
379 ,p_attribute11 => p_analyses_rec.attribute11
380 ,p_attribute12 => p_analyses_rec.attribute12
381 ,p_attribute13 => p_analyses_rec.attribute13
382 ,p_attribute14 => p_analyses_rec.attribute14
383 ,p_attribute15 => p_analyses_rec.attribute15
384 ,p_attribute16 => p_analyses_rec.attribute16
385 ,p_attribute17 => p_analyses_rec.attribute17
386 ,p_attribute18 => p_analyses_rec.attribute18
387 ,p_attribute19 => p_analyses_rec.attribute19
388 ,p_attribute20 => p_analyses_rec.attribute20
389 --
390 ,p_segment1 => p_analysis_criteria_rec.segment1
391 ,p_segment2 => p_analysis_criteria_rec.segment2
392 ,p_segment3 => p_analysis_criteria_rec.segment3
393 ,p_segment4 => p_analysis_criteria_rec.segment4
394 ,p_segment5 => p_analysis_criteria_rec.segment5
395 ,p_segment6 => p_analysis_criteria_rec.segment6
396 ,p_segment7 => p_analysis_criteria_rec.segment7
397 ,p_segment8 => p_analysis_criteria_rec.segment8
398 ,p_segment9 => p_analysis_criteria_rec.segment9
399 ,p_segment10 => p_analysis_criteria_rec.segment10
400 ,p_segment11 => p_analysis_criteria_rec.segment11
401 ,p_segment12 => p_analysis_criteria_rec.segment12
402 ,p_segment13 => p_analysis_criteria_rec.segment13
403 ,p_segment14 => p_analysis_criteria_rec.segment14
404 ,p_segment15 => p_analysis_criteria_rec.segment15
405 ,p_segment16 => p_analysis_criteria_rec.segment16
406 ,p_segment17 => p_analysis_criteria_rec.segment17
407 ,p_segment18 => p_analysis_criteria_rec.segment18
408 ,p_segment19 => p_analysis_criteria_rec.segment19
409 ,p_segment20 => p_analysis_criteria_rec.segment20
410 ,p_segment21 => p_analysis_criteria_rec.segment21
411 ,p_segment22 => p_analysis_criteria_rec.segment22
412 ,p_segment23 => p_analysis_criteria_rec.segment23
413 ,p_segment24 => p_analysis_criteria_rec.segment24
414 ,p_segment25 => p_analysis_criteria_rec.segment25
415 ,p_segment26 => p_analysis_criteria_rec.segment26
416 ,p_segment27 => p_analysis_criteria_rec.segment27
417 ,p_segment28 => p_analysis_criteria_rec.segment28
418 ,p_segment29 => p_analysis_criteria_rec.segment29
419 ,p_segment30 => p_analysis_criteria_rec.segment30
420 --
421 ,p_concat_segments => Null
422 ,p_analysis_criteria_id => p_analysis_criteria_rec.analysis_criteria_id
423 ,p_person_analysis_id => p_analyses_rec.person_analysis_id
424 ,p_pea_object_version_number => p_analyses_rec.object_version_number
425 );
426 ELSIF p_action = 'UPDATE' THEN
427
428 Hr_SIT_Api.Update_SIT
429 (p_validate => p_validate
430 ,p_person_analysis_id => p_analyses_rec.person_analysis_id
431 ,p_pea_object_version_number => p_analyses_rec.object_version_number
432 ,p_comments => NULL
433 ,p_date_from => p_analyses_rec.date_from
434 ,p_date_to => p_analyses_rec.date_to
435 ,p_concat_segments => Null
436 --
437 ,p_analysis_criteria_id => p_analysis_criteria_rec.analysis_criteria_id
438 --
439 ,p_request_id => p_analyses_rec.request_id
440 ,p_program_application_id => p_analyses_rec.program_application_id
441 ,p_program_id => p_analyses_rec.program_id
442 ,p_program_update_date => p_analyses_rec.program_update_date
443 --
444 ,p_attribute_category => p_analyses_rec.attribute_category
445 ,p_attribute1 => p_analyses_rec.attribute1
446 ,p_attribute2 => p_analyses_rec.attribute2
447 ,p_attribute3 => p_analyses_rec.attribute3
448 ,p_attribute4 => p_analyses_rec.attribute4
449 ,p_attribute5 => p_analyses_rec.attribute5
450 ,p_attribute6 => p_analyses_rec.attribute6
451 ,p_attribute7 => p_analyses_rec.attribute7
452 ,p_attribute8 => p_analyses_rec.attribute8
453 ,p_attribute9 => p_analyses_rec.attribute9
454 ,p_attribute10 => p_analyses_rec.attribute10
455 ,p_attribute11 => p_analyses_rec.attribute11
456 ,p_attribute12 => p_analyses_rec.attribute12
457 ,p_attribute13 => p_analyses_rec.attribute13
458 ,p_attribute14 => p_analyses_rec.attribute14
459 ,p_attribute15 => p_analyses_rec.attribute15
460 ,p_attribute16 => p_analyses_rec.attribute16
461 ,p_attribute17 => p_analyses_rec.attribute17
462 ,p_attribute18 => p_analyses_rec.attribute18
463 ,p_attribute19 => p_analyses_rec.attribute19
464 ,p_attribute20 => p_analyses_rec.attribute20
465 ,p_segment1 => p_analysis_criteria_rec.segment1
466 ,p_segment2 => p_analysis_criteria_rec.segment2
467 ,p_segment3 => p_analysis_criteria_rec.segment3
468 ,p_segment4 => p_analysis_criteria_rec.segment4
469 ,p_segment5 => p_analysis_criteria_rec.segment5
470 ,p_segment6 => p_analysis_criteria_rec.segment6
471 ,p_segment7 => p_analysis_criteria_rec.segment7
472 ,p_segment8 => p_analysis_criteria_rec.segment8
473 ,p_segment9 => p_analysis_criteria_rec.segment9
474 ,p_segment10 => p_analysis_criteria_rec.segment10
475 ,p_segment11 => p_analysis_criteria_rec.segment11
476 ,p_segment12 => p_analysis_criteria_rec.segment12
477 ,p_segment13 => p_analysis_criteria_rec.segment13
478 ,p_segment14 => p_analysis_criteria_rec.segment14
479 ,p_segment15 => p_analysis_criteria_rec.segment15
480 ,p_segment16 => p_analysis_criteria_rec.segment16
481 ,p_segment17 => p_analysis_criteria_rec.segment17
482 ,p_segment18 => p_analysis_criteria_rec.segment18
483 ,p_segment19 => p_analysis_criteria_rec.segment19
484 ,p_segment20 => p_analysis_criteria_rec.segment20
485 ,p_segment21 => p_analysis_criteria_rec.segment21
486 ,p_segment22 => p_analysis_criteria_rec.segment22
487 ,p_segment23 => p_analysis_criteria_rec.segment23
488 ,p_segment24 => p_analysis_criteria_rec.segment24
489 ,p_segment25 => p_analysis_criteria_rec.segment25
490 ,p_segment26 => p_analysis_criteria_rec.segment26
491 ,p_segment27 => p_analysis_criteria_rec.segment27
492 ,p_segment28 => p_analysis_criteria_rec.segment28
493 ,p_segment29 => p_analysis_criteria_rec.segment29
494 ,p_segment30 => p_analysis_criteria_rec.segment30
495 );
496 ELSIF p_action ='DELETE' THEN
497 Hr_SIT_Api.Delete_SIT
498 (p_validate => p_validate
499 ,p_person_analysis_id => p_analyses_rec.person_analysis_id
500 ,p_pea_object_version_number => p_analyses_rec.object_version_number
501 );
502 END IF;
503 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
504 EXCEPTION
505 WHEN Others THEN
506 l_error_msg := Substrb(SQLERRM,1,2000);
507 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
508 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
509 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
510 Hr_Utility.raise_error;
511
512 END InsUpd_SIT_info;
513 -- =============================================================================
514 -- ~ InsUpd_Asg_Extra_info: Insert, Update or Delete Assignment Extra Info.
515 -- =============================================================================
516 PROCEDURE InsUpd_Asg_Extra_info
517 (p_assignment_id IN Number
518 ,p_business_group_id IN Number
519 ,p_validate IN Boolean DEFAULT FALSE
520 ,p_action IN Varchar2
521 ,p_extra_info_rec IN OUT NOCOPY per_assignment_extra_info%ROWTYPE
522 ) IS
523 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'InsUpd_Asg_Extra_info';
524 l_error_msg Varchar2(2000);
525
526 BEGIN
527 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
528 IF p_action = 'CREATE' THEN
529 Hr_Assignment_Extra_Info_Api.Create_Assignment_Extra_Info
530 (p_validate => p_validate
531 ,p_assignment_id => p_assignment_id
532 -- DDF segments
533 ,p_information_type => p_extra_info_rec.information_type
534 ,p_aei_information_category => p_extra_info_rec.aei_information_category
535 ,p_aei_information1 => p_extra_info_rec.aei_information1
536 ,p_aei_information2 => p_extra_info_rec.aei_information2
537 ,p_aei_information3 => p_extra_info_rec.aei_information3
538 ,p_aei_information4 => p_extra_info_rec.aei_information4
539 ,p_aei_information5 => p_extra_info_rec.aei_information5
540 ,p_aei_information6 => p_extra_info_rec.aei_information6
541 ,p_aei_information7 => p_extra_info_rec.aei_information7
542 ,p_aei_information8 => p_extra_info_rec.aei_information8
543 ,p_aei_information9 => p_extra_info_rec.aei_information9
544 ,p_aei_information10 => p_extra_info_rec.aei_information10
545 ,p_aei_information11 => p_extra_info_rec.aei_information11
546 ,p_aei_information12 => p_extra_info_rec.aei_information12
547 ,p_aei_information13 => p_extra_info_rec.aei_information13
548 ,p_aei_information14 => p_extra_info_rec.aei_information14
549 ,p_aei_information15 => p_extra_info_rec.aei_information15
550 ,p_aei_information16 => p_extra_info_rec.aei_information16
551 ,p_aei_information17 => p_extra_info_rec.aei_information17
552 ,p_aei_information18 => p_extra_info_rec.aei_information18
553 ,p_aei_information19 => p_extra_info_rec.aei_information19
554 ,p_aei_information20 => p_extra_info_rec.aei_information20
555 ,p_aei_information21 => p_extra_info_rec.aei_information21
556 ,p_aei_information22 => p_extra_info_rec.aei_information22
557 ,p_aei_information23 => p_extra_info_rec.aei_information23
558 ,p_aei_information24 => p_extra_info_rec.aei_information24
559 ,p_aei_information25 => p_extra_info_rec.aei_information25
560 ,p_aei_information26 => p_extra_info_rec.aei_information26
561 ,p_aei_information27 => p_extra_info_rec.aei_information27
562 ,p_aei_information28 => p_extra_info_rec.aei_information28
563 ,p_aei_information29 => p_extra_info_rec.aei_information29
564 ,p_aei_information30 => p_extra_info_rec.aei_information30
565 --
566 ,p_aei_attribute_category => p_extra_info_rec.aei_attribute_category
567 ,p_aei_attribute1 => p_extra_info_rec.aei_attribute1
568 ,p_aei_attribute2 => p_extra_info_rec.aei_attribute2
569 ,p_aei_attribute3 => p_extra_info_rec.aei_attribute3
570 ,p_aei_attribute4 => p_extra_info_rec.aei_attribute4
571 ,p_aei_attribute5 => p_extra_info_rec.aei_attribute5
572 ,p_aei_attribute6 => p_extra_info_rec.aei_attribute6
573 ,p_aei_attribute7 => p_extra_info_rec.aei_attribute7
574 ,p_aei_attribute8 => p_extra_info_rec.aei_attribute8
575 ,p_aei_attribute9 => p_extra_info_rec.aei_attribute9
576 ,p_aei_attribute10 => p_extra_info_rec.aei_attribute10
577 ,p_aei_attribute11 => p_extra_info_rec.aei_attribute11
578 ,p_aei_attribute12 => p_extra_info_rec.aei_attribute12
579 ,p_aei_attribute13 => p_extra_info_rec.aei_attribute13
580 ,p_aei_attribute14 => p_extra_info_rec.aei_attribute14
581 ,p_aei_attribute15 => p_extra_info_rec.aei_attribute15
582 ,p_aei_attribute16 => p_extra_info_rec.aei_attribute16
583 ,p_aei_attribute17 => p_extra_info_rec.aei_attribute17
584 ,p_aei_attribute18 => p_extra_info_rec.aei_attribute18
585 ,p_aei_attribute19 => p_extra_info_rec.aei_attribute19
586 ,p_aei_attribute20 => p_extra_info_rec.aei_attribute20
587 --
588 ,p_Assignment_extra_info_id => p_extra_info_rec.Assignment_extra_info_id
589 ,p_object_version_number => p_extra_info_rec.Object_Version_Number
590 );
591 ELSIF p_action = 'UPDATE' THEN
592
593 Hr_Assignment_Extra_Info_Api.Update_Assignment_Extra_Info
594 (p_validate => p_validate
595 ,p_Assignment_extra_info_id => p_extra_info_rec.Assignment_extra_info_id
596 ,p_object_version_number => p_extra_info_rec.Object_Version_Number
597 -- DDF Segments
598 ,p_aei_information_category => p_extra_info_rec.aei_information_category
599 ,p_aei_information1 => p_extra_info_rec.aei_information1
600 ,p_aei_information2 => p_extra_info_rec.aei_information2
601 ,p_aei_information3 => p_extra_info_rec.aei_information3
602 ,p_aei_information4 => p_extra_info_rec.aei_information4
603 ,p_aei_information5 => p_extra_info_rec.aei_information5
604 ,p_aei_information6 => p_extra_info_rec.aei_information6
605 ,p_aei_information7 => p_extra_info_rec.aei_information7
606 ,p_aei_information8 => p_extra_info_rec.aei_information8
607 ,p_aei_information9 => p_extra_info_rec.aei_information9
608 ,p_aei_information10 => p_extra_info_rec.aei_information10
609 ,p_aei_information11 => p_extra_info_rec.aei_information11
610 ,p_aei_information12 => p_extra_info_rec.aei_information12
611 ,p_aei_information13 => p_extra_info_rec.aei_information13
612 ,p_aei_information14 => p_extra_info_rec.aei_information14
613 ,p_aei_information15 => p_extra_info_rec.aei_information15
614 ,p_aei_information16 => p_extra_info_rec.aei_information16
615 ,p_aei_information17 => p_extra_info_rec.aei_information17
616 ,p_aei_information18 => p_extra_info_rec.aei_information18
617 ,p_aei_information19 => p_extra_info_rec.aei_information19
618 ,p_aei_information20 => p_extra_info_rec.aei_information20
619 ,p_aei_information21 => p_extra_info_rec.aei_information21
620 ,p_aei_information22 => p_extra_info_rec.aei_information22
621 ,p_aei_information23 => p_extra_info_rec.aei_information23
622 ,p_aei_information24 => p_extra_info_rec.aei_information24
623 ,p_aei_information25 => p_extra_info_rec.aei_information25
624 ,p_aei_information26 => p_extra_info_rec.aei_information26
625 ,p_aei_information27 => p_extra_info_rec.aei_information27
626 ,p_aei_information28 => p_extra_info_rec.aei_information28
627 ,p_aei_information29 => p_extra_info_rec.aei_information29
628 ,p_aei_information30 => p_extra_info_rec.aei_information30
629 -- DF segments
630 ,p_aei_attribute_category => p_extra_info_rec.aei_attribute_category
631 ,p_aei_attribute1 => p_extra_info_rec.aei_attribute1
632 ,p_aei_attribute2 => p_extra_info_rec.aei_attribute2
633 ,p_aei_attribute3 => p_extra_info_rec.aei_attribute3
634 ,p_aei_attribute4 => p_extra_info_rec.aei_attribute4
635 ,p_aei_attribute5 => p_extra_info_rec.aei_attribute5
636 ,p_aei_attribute6 => p_extra_info_rec.aei_attribute6
637 ,p_aei_attribute7 => p_extra_info_rec.aei_attribute7
638 ,p_aei_attribute8 => p_extra_info_rec.aei_attribute8
639 ,p_aei_attribute9 => p_extra_info_rec.aei_attribute9
640 ,p_aei_attribute10 => p_extra_info_rec.aei_attribute10
641 ,p_aei_attribute11 => p_extra_info_rec.aei_attribute11
642 ,p_aei_attribute12 => p_extra_info_rec.aei_attribute12
643 ,p_aei_attribute13 => p_extra_info_rec.aei_attribute13
644 ,p_aei_attribute14 => p_extra_info_rec.aei_attribute14
645 ,p_aei_attribute15 => p_extra_info_rec.aei_attribute15
646 ,p_aei_attribute16 => p_extra_info_rec.aei_attribute16
647 ,p_aei_attribute17 => p_extra_info_rec.aei_attribute17
648 ,p_aei_attribute18 => p_extra_info_rec.aei_attribute18
649 ,p_aei_attribute19 => p_extra_info_rec.aei_attribute19
650 ,p_aei_attribute20 => p_extra_info_rec.aei_attribute20
651 );
652 ELSIF p_action ='DELETE' THEN
653 Hr_Assignment_Extra_Info_Api.Delete_Assignment_Extra_Info
654 (p_validate => p_validate
655 ,p_assignment_extra_info_id => p_extra_info_rec.Assignment_extra_info_id
656 ,p_object_version_number => p_extra_info_rec.object_version_number
657 );
658 END IF;
659 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
660 EXCEPTION
661 WHEN Others THEN
662 l_error_msg := Substrb(SQLERRM,1,2000);
663 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
664 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
665 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
666 Hr_Utility.raise_error;
667
668 END InsUpd_Asg_Extra_info;
669
670 -- =============================================================================
671 -- ~ InsUpd_OSS_PassPort:
672 -- =============================================================================
673 PROCEDURE InsUpd_OSS_PassPort
674 (p_person_id IN Number
675 ,p_party_id IN Number
676 ,p_action IN Varchar2
677 ,p_pei_info_rec_old IN per_people_extra_info%ROWTYPE
678 ,p_pei_info_rec_new IN per_people_extra_info%ROWTYPE
679 ) AS
680
681 TYPE csr_pp_t IS REF CURSOR;
682 csr_pp csr_pp_t;
683 SQLstmt Varchar2(2000);
684 PLSQL_Block Varchar2(2000);
685 l_oss_pp_rec_old oss_pp_rec;
686 l_oss_pp_rec oss_pp_rec;
687 l_Update_OSS_rec Boolean;
688 l_Insert_OSS_rec Boolean;
689 l_mode Varchar2(5);
690
691 l_return_status Varchar2(10);
692 l_msg_count Number;
693 l_msg_data Varchar2(2000);
694 l_passport_id Number(15);
695 e_passport_err EXCEPTION;
696 l_cntry_mapping csr_cntry_code%ROWTYPE;
697 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_OSS_PassPort';
698 l_error_msg Varchar2(2000);
699 BEGIN
700 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
701 l_Update_OSS_Rec := FALSE; l_Insert_OSS_Rec := FALSE;
702
703 SQLstmt := ' SELECT pap.rowid '||
704 ' ,pap.passport_id '||
705 ' ,pap.passport_cntry_code '||
706 ' ,pap.passport_number '||
707 ' ,pap.passport_expiry_date '||
708 ' ,pap.attribute_category '||
709 ' ,pap.attribute1 '||
710 ' ,pap.attribute2 '||
711 ' ,pap.attribute3 '||
712 ' ,pap.attribute4 '||
713 ' ,pap.attribute5 '||
714 ' ,pap.attribute6 '||
715 ' ,pap.attribute7 '||
716 ' ,pap.attribute8 '||
717 ' ,pap.attribute9 '||
718 ' ,pap.attribute10 '||
719 ' ,pap.attribute11 '||
720 ' ,pap.attribute12 '||
721 ' ,pap.attribute13 '||
722 ' ,pap.attribute14 '||
723 ' ,pap.attribute15 '||
724 ' ,pap.attribute16 '||
725 ' ,pap.attribute17 '||
726 ' ,pap.attribute18 '||
727 ' ,pap.attribute19 '||
728 ' ,pap.attribute20 '||
729 ' FROM igs_pe_passport pap '||
730 ' WHERE pap.person_id = :c_party_id '||
731 ' AND pap.passport_number = :c_passport_number ';
732
733 IF p_action = 'UPDATE' THEN
734
735 IF (p_pei_info_rec_new.pei_information5 =
736 p_pei_info_rec_old.pei_information5 AND
737 p_pei_info_rec_new.pei_information6 =
738 p_pei_info_rec_old.pei_information6 AND
739 Fnd_Date.Canonical_To_Date
740 (p_pei_info_rec_new.pei_information8) =
741 Fnd_Date.Canonical_To_Date
742 (p_pei_info_rec_old.pei_information8))
743 THEN
744 RETURN;
745 END IF;
746
747 IF ( p_pei_info_rec_old.pei_information8 is Null or
748 p_pei_info_rec_new.pei_information8 is Null) THEN
749 l_msg_data := 'Passport Expiry Date is required for a Student.';
750 RAISE e_passport_err;
751 END IF;
752
753 ELSIF p_action ='INSERT' THEN
754
755 IF (p_pei_info_rec_new.pei_information8 is Null) THEN
756 l_msg_data := 'Passport Expiry Date is required for a Student.';
757 RAISE e_passport_err;
758 END IF;
759 END IF;
760
761 -- ===========================================================================
762 -- Country (VS)-R = pei_information5 = igs_pe_passport.passport_cntry_code
763 -- Passport Number-R = pei_information6 = igs_pe_passport.passport_number
764 -- Issue Date = pei_information7 = default to null
765 -- Expiry Date = pei_information8 = igs_pe_passport.passport_expiry_date
766 -- ===========================================================================
767 IF p_action = 'INSERT' THEN
768 l_oss_pp_rec.passport_cntry_code := p_pei_info_rec_new.pei_information5;
769 l_oss_pp_rec.passport_number := p_pei_info_rec_new.pei_information6;
770 l_oss_pp_rec.passport_expiry_date:= Fnd_Date.Canonical_To_Date
771 (p_pei_info_rec_new.pei_information8);
772 l_Insert_OSS_Rec := TRUE;
773 -- If expiry date is null then raise a warning that Passport was not
774 -- created in OSS for the student employee as its a required field in OSS
775
776 ELSIF p_action = 'UPDATE' THEN
777 Hr_Utility.set_location('..p_action :'||p_action, 10);
778 Hr_Utility.set_location('..Old PP No:'||p_pei_info_rec_old.pei_information6, 10);
779 Hr_Utility.set_location('..New PP No:'||p_pei_info_rec_new.pei_information6, 10);
780 IF (Trim(p_pei_info_rec_old.pei_information6) =
781 Trim(p_pei_info_rec_new.pei_information6)) THEN
782 OPEN csr_pp FOR SQLstmt
783 Using p_party_id
784 ,p_pei_info_rec_new.pei_information6;
785 FETCH csr_pp INTO l_oss_pp_rec_old;
786 Hr_Utility.set_location('..After Dynamic SQL cursor ', 11);
787 IF csr_pp%FOUND AND
788 (p_pei_info_rec_new.pei_information8 <>
789 p_pei_info_rec_old.pei_information8) OR
790 (p_pei_info_rec_new.pei_information5 <>
791 p_pei_info_rec_old.pei_information5)
792 THEN
793 l_update_OSS_rec := TRUE;
794 l_oss_pp_rec := l_oss_pp_rec_old;
795 ELSE
796 l_Insert_OSS_Rec := TRUE;
797 END IF;
798 CLOSE csr_pp;
799 ELSIF (Trim(p_pei_info_rec_old.pei_information6) <>
800 Trim(p_pei_info_rec_new.pei_information6)) THEN
801
802 OPEN csr_pp FOR SQLstmt
803 Using p_party_id
804 ,p_pei_info_rec_new.pei_information6;
805 FETCH csr_pp INTO l_oss_pp_rec_old;
806 Hr_Utility.set_location('..After Dynamic SQL cursor ', 11);
807 IF csr_pp%NOTFOUND THEN
808 l_Insert_OSS_Rec := TRUE;
809 ELSIF (p_pei_info_rec_new.pei_information8 <>
810 p_pei_info_rec_old.pei_information8)
811 OR
812 (p_pei_info_rec_new.pei_information5 <>
813 p_pei_info_rec_old.pei_information5)
814 THEN
815 l_update_OSS_rec := TRUE;
816 l_oss_pp_rec := l_oss_pp_rec_old;
817 END IF;
818 CLOSE csr_pp;
819 END IF;
820
821 l_oss_pp_rec.passport_cntry_code := p_pei_info_rec_new.pei_information5;
822 l_oss_pp_rec.passport_number := p_pei_info_rec_new.pei_information6;
823 l_oss_pp_rec.passport_expiry_date:= Fnd_Date.Canonical_To_Date
824 (p_pei_info_rec_new.pei_information8);
825
826 -- If expiry date is null then raise a warning that Passport was not
827 -- created in OSS for the student employee
828 END IF;
829 l_mode := 'R';
830 OPEN csr_cntry_code
831 (c_country_code => l_oss_pp_rec.passport_cntry_code
832 ,c_map_to => 'HR_TO_OSS');
833 FETCH csr_cntry_code INTO l_cntry_mapping;
834 CLOSE csr_cntry_code;
835 l_oss_pp_rec.passport_cntry_code := NVL(l_cntry_mapping.irs_code
836 ,l_oss_pp_rec.passport_cntry_code);
837
838 -- Insert Into OSS
839 IF l_Insert_OSS_Rec THEN
840 Hr_Utility.set_location('Calling Dynamic PL/SQL Block: IGS_PE_Visapass_Pub.Create_Passport', 20);
841 Hr_Utility.set_location('..passport_number: '||l_oss_pp_rec.passport_number, 20);
842 Hr_Utility.set_location('..passport_expiry_date: '||l_oss_pp_rec.passport_expiry_date, 20);
843 Hr_Utility.set_location('..passport_cntry_code: '||l_oss_pp_rec.passport_cntry_code, 20);
844 Hr_Utility.set_location('..l_cntry_mapping.irs_code: '||l_cntry_mapping.irs_code, 20);
845
846 PLSQL_Block :=
847 'DECLARE
848 l_passport_rec Igs_Pe_Visapass_Pub.Passport_Rec_TYPE;
849 BEGIN
850 l_passport_rec.passport_number := :1;
851 l_passport_rec.passport_cntry_code := :2;
852 l_passport_rec.passport_expiry_date := :3;
853 l_passport_rec.person_id := :4;
854
855 Igs_Pe_Visapass_Pub.Create_Passport
856 (p_api_version => 1.0
857 ,p_init_msg_list => Fnd_Api.G_TRUE
858 ,p_passport_rec => l_passport_rec
859 ,x_return_status => :5
860 ,x_msg_count => :6
861 ,x_msg_data => :7
862 ,x_passport_id => :8
863 );
864 END;';
865 EXECUTE IMMEDIATE PLSQL_Block
866 Using l_oss_pp_rec.passport_number
867 ,l_oss_pp_rec.passport_cntry_code
868 ,l_oss_pp_rec.passport_expiry_date
869 ,p_party_id
870 ,OUT l_return_status
871 ,OUT l_msg_count
872 ,OUT l_msg_data
873 ,OUT l_passport_id;
874
875 END IF;
876
877 -- Update the existing OSS record
878 IF l_update_OSS_rec THEN
879 Hr_Utility.set_location('Calling Dynamic PL/SQL Block: Igs_Pe_Passport_Pkg.Update_Row', 21);
880 Hr_Utility.set_location('..passport_number: '||l_oss_pp_rec.passport_number, 21);
881 Hr_Utility.set_location('..passport_expiry_date: '||l_oss_pp_rec.passport_expiry_date, 21);
882 Hr_Utility.set_location('..passport_cntry_code: '||l_oss_pp_rec.passport_cntry_code, 21);
883 Hr_Utility.set_location('..passport_id: '||l_oss_pp_rec.passport_id, 21);
884 Hr_Utility.set_location('..rowid: '||l_oss_pp_rec.pp_rowid, 21);
885
886 PLSQL_Block :=
887 'DECLARE
888 l_passport_rec Igs_Pe_Visapass_Pub.Passport_Rec_TYPE;
889 BEGIN
890 l_passport_rec.passport_number := :1;
891 l_passport_rec.passport_cntry_code := :2;
892 l_passport_rec.passport_expiry_date := :3;
893 l_passport_rec.person_id := :4;
894 l_passport_rec.passport_id := :5;
895
896 Igs_Pe_Visapass_Pub.Update_Passport
897 (p_api_version => 1.0
898 ,p_init_msg_list => Fnd_Api.G_TRUE
899 ,p_passport_rec => l_passport_rec
900 ,x_return_status => :6
901 ,x_msg_count => :7
902 ,x_msg_data => :8
903 );
904 END;';
905 EXECUTE IMMEDIATE PLSQL_Block
906 Using l_oss_pp_rec.passport_number
907 ,l_oss_pp_rec.passport_cntry_code
908 ,l_oss_pp_rec.passport_expiry_date
909 ,p_party_id
910 ,l_oss_pp_rec.passport_id
911 ,OUT l_return_status
912 ,OUT l_msg_count
913 ,OUT l_msg_data;
914
915 END IF;
916 Hr_Utility.set_location(' l_return_status: '||l_return_status, 45);
917 Hr_Utility.set_location(' l_msg_data: '||l_msg_data, 45);
918 IF l_return_status IN ('E','U') THEN
919 RAISE e_passport_err;
920 END IF;
921 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
922 EXCEPTION
923 WHEN e_passport_err THEN
924 l_error_msg := Substrb(l_msg_data,1,2000);
925 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
926 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
927 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
928 Hr_Utility.raise_error;
929 WHEN Others THEN
930 l_error_msg := Substrb(SQLERRM,1,2000);
931 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
932 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
933 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
934 Hr_Utility.raise_error;
935 END InsUpd_OSS_PassPort;
936 -- =============================================================================
937 -- ~ InsUpd_OSS_Visa:
938 -- =============================================================================
939 PROCEDURE InsUpd_OSS_Visa
940 (p_person_id IN Number
941 ,p_party_id IN Number
942 ,p_action IN Varchar2
943 ,p_pei_info_rec_old IN per_people_extra_info%ROWTYPE
944 ,p_pei_info_rec_new IN per_people_extra_info%ROWTYPE
945 ) AS
946
947 TYPE csr_visa_t IS REF CURSOR;
948 csr_visa csr_visa_t;
949
950 l_oss_visa_rec_old oss_visa_rec;
951 l_oss_visa_rec oss_visa_rec;
952
953 SQLstmt Varchar2(2000);
954 PLSQL_Block Varchar2(2000);
955 l_Update_OSS_rec Boolean;
956 l_Insert_OSS_rec Boolean;
957 l_return_status Varchar2(10);
958 l_msg_count Number;
959 l_msg_data Varchar2(2000);
960 l_error_msg Varchar2(2000);
961 l_visa_id Number(15);
962 e_visa_syn_err EXCEPTION;
963 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_OSS_Visa';
964 l_dft_date Varchar2(150);
965 BEGIN
966 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
967 l_dft_date := Fnd_Date.Date_To_Canonical(sysdate);
968 Hr_Utility.set_location('..p_party_id: '||p_party_id, 5);
969 l_Update_OSS_Rec := FALSE; l_Insert_OSS_Rec := FALSE;
970
971 SQLstmt:='SELECT '||
972 ' rowid '||
973 ' ,visa_id '||
974 ' ,visa_type '||
975 ' ,visa_number '||
976 ' ,visa_issue_date '||
977 ' ,visa_expiry_date '||
978 ' ,visa_category '||
979 ' ,visa_issuing_post '||
980 ' ,passport_id '||
981 ' ,agent_org_unit_cd '||
982 ' ,agent_person_id '||
983 ' ,agent_contact_name '||
984 ' ,visa_issuing_country '||
985 ' ,attribute_category '||
986 ' ,attribute1 '||
987 ' ,attribute2 '||
988 ' ,attribute3 '||
989 ' ,attribute4 '||
990 ' ,attribute5 '||
991 ' ,attribute6 '||
992 ' ,attribute7 '||
993 ' ,attribute8 '||
994 ' ,attribute9 '||
995 ' ,attribute10 '||
996 ' ,attribute11 '||
997 ' ,attribute12 '||
998 ' ,attribute13 '||
999 ' ,attribute14 '||
1000 ' ,attribute15 '||
1001 ' ,attribute16 '||
1002 ' ,attribute17 '||
1003 ' ,attribute18 '||
1004 ' ,attribute19 '||
1005 ' ,attribute20 '||
1006 ' FROM igs_pe_visa '||
1007 'WHERE person_id = :c_party_id '||
1008 ' AND visa_number = :c_visa_number ';
1009 IF p_action = 'UPDATE' THEN
1010 IF (p_pei_info_rec_new.pei_information5 =
1011 p_pei_info_rec_old.pei_information5 AND
1012 p_pei_info_rec_new.pei_information6 =
1013 p_pei_info_rec_old.pei_information6 AND
1014 Fnd_Date.Canonical_To_Date
1015 (p_pei_info_rec_new.pei_information7) =
1016 Fnd_Date.Canonical_To_Date
1017 (p_pei_info_rec_old.pei_information7) AND
1018 Fnd_Date.Canonical_To_Date
1019 (nvl(p_pei_info_rec_new.pei_information8,l_dft_date)) =
1020 Fnd_Date.Canonical_To_Date
1021 (nvl(p_pei_info_rec_old.pei_information8,l_dft_date))
1022 )
1023 THEN
1024 RETURN;
1025 END IF;
1026 IF (p_pei_info_rec_new.pei_information5 <>
1027 p_pei_info_rec_old.pei_information5) or
1028 (p_pei_info_rec_new.pei_information6 <>
1029 p_pei_info_rec_old.pei_information6)or
1030 (Fnd_Date.Canonical_To_Date
1031 (nvl(p_pei_info_rec_new.pei_information7,l_dft_date)) <>
1032 Fnd_Date.Canonical_To_Date
1033 (nvl(p_pei_info_rec_old.pei_information7,l_dft_date))
1034 )
1035 THEN
1036 l_msg_data := 'For a student you cannot update the unique combination of '||
1037 'Visa Number, Visa Type and Issue Date';
1038 RAISE e_visa_syn_err;
1039 END IF;
1040 END IF;
1041 -- ===========================================================================
1042 -- Visa Type(VS)-R = pei_information5 = igs_pe_visa.visa_type
1043 -- Visa Number-R = pei_information6 = igs_pe_visa.visa_number
1044 -- Issue Date -R = pei_information7 = igs_pe_visa.visa_issue_date
1045 -- Expiry Date -R = pei_information8 = igs_pe_visa.visa_expiry_date
1046 -- J Visa Category = pei_information9 = default to 99
1047 -- Pass To Interface = pei_information10 = N
1048 -- ===========================================================================
1049 Hr_Utility.set_location('..Visa Number: '||p_pei_info_rec_new.pei_information6, 6);
1050 IF p_action = 'INSERT' THEN
1051 l_oss_visa_rec.visa_type := p_pei_info_rec_new.pei_information5;
1052 l_oss_visa_rec.visa_number := p_pei_info_rec_new.pei_information6;
1053 l_oss_visa_rec.visa_issue_date := Fnd_Date.Canonical_To_Date
1054 (p_pei_info_rec_new.pei_information7);
1055 l_oss_visa_rec.visa_expiry_date:= Fnd_Date.Canonical_To_Date
1056 (p_pei_info_rec_new.pei_information8);
1057 l_Insert_OSS_Rec := TRUE;
1058 -- If expiry date is null then raise a warning that Passport was not
1059 -- created in OSS for the student employee
1060 ELSIF p_action = 'UPDATE' THEN
1061
1062 IF (Trim(p_pei_info_rec_old.pei_information6) =
1063 Trim(p_pei_info_rec_new.pei_information6)) THEN
1064 OPEN csr_Visa FOR SQLstmt
1065 Using p_party_id
1066 ,p_pei_info_rec_old.pei_information6;
1067 FETCH csr_Visa INTO l_oss_visa_rec_old;
1068 Hr_Utility.set_location('..After Dynamic SQL cursor ', 11);
1069 IF csr_Visa%FOUND THEN
1070 IF (p_pei_info_rec_old.pei_information5 =
1071 p_pei_info_rec_new.pei_information5) AND
1072 (Fnd_Date.Canonical_To_Date
1073 (nvl(p_pei_info_rec_new.pei_information8,l_dft_date)) <>
1074 Fnd_Date.Canonical_To_Date
1075 (nvl(p_pei_info_rec_old.pei_information8,l_dft_date))
1076 )
1077 THEN
1078 Hr_Utility.set_location('..Visa Number found. ', 12);
1079 -- The Visa being updated exists in OSS
1080 l_update_OSS_rec := TRUE;
1081 l_oss_visa_rec := l_oss_visa_rec_old;
1082 END IF;
1083 ELSE
1084 Hr_Utility.set_location('..Visa Number NOT found. ', 12);
1085 -- That means the visa that is being updated in HR is not there
1086 -- in OSS, so create it.
1087 l_update_OSS_rec := FALSE;
1088 l_insert_OSS_rec := TRUE;
1089 END IF;
1090 CLOSE csr_Visa;
1091
1092 ELSIF (Trim(p_pei_info_rec_old.pei_information6) <>
1093 Trim(p_pei_info_rec_new.pei_information6)) THEN
1094
1095 OPEN csr_Visa FOR SQLstmt
1096 Using p_party_id
1097 ,p_pei_info_rec_new.pei_information6;
1098 FETCH csr_Visa INTO l_oss_visa_rec_old;
1099 Hr_Utility.set_location('..After Dynamic SQL cursor ', 13);
1100 IF csr_Visa%NOTFOUND THEN
1101 Hr_Utility.set_location('..Visa Number NOT found. ', 14);
1102 l_Insert_OSS_Rec := TRUE;
1103 ELSIF (p_pei_info_rec_new.pei_information5 =
1104 p_pei_info_rec_old.pei_information5) OR
1105 (Fnd_Date.Canonical_To_Date
1106 (nvl(p_pei_info_rec_new.pei_information8,l_dft_date)) <>
1107 Fnd_Date.Canonical_To_Date
1108 (nvl(p_pei_info_rec_old.pei_information8,l_dft_date))
1109 ) THEN
1110 Hr_Utility.set_location('..Visa Number found. ', 15);
1111 l_Update_OSS_Rec := TRUE;
1112 l_oss_visa_rec := l_oss_visa_rec_old;
1113 END IF;
1114 CLOSE csr_Visa;
1115 END IF;
1116
1117 l_oss_visa_rec.visa_type := p_pei_info_rec_new.pei_information5;
1118 l_oss_visa_rec.visa_number := p_pei_info_rec_new.pei_information6;
1119 l_oss_visa_rec.visa_issue_date := Fnd_Date.Canonical_To_Date
1120 (p_pei_info_rec_new.pei_information7);
1121 l_oss_visa_rec.visa_expiry_date:= Fnd_Date.Canonical_To_Date
1122 (p_pei_info_rec_new.pei_information8);
1123
1124 END IF;
1125
1126 -- Insert Into OSS
1127 Hr_Utility.set_location('..p_action: '||p_action, 19);
1128 IF l_insert_OSS_rec THEN
1129 Hr_Utility.set_location('..visa_type: '||l_oss_visa_rec.visa_type, 20);
1130 Hr_Utility.set_location('..visa_number: '||l_oss_visa_rec.visa_number,20);
1131 Hr_Utility.set_location('..visa_issue_date: '||l_oss_visa_rec.visa_issue_date, 20);
1132 Hr_Utility.set_location('..visa_expiry_date: '||l_oss_visa_rec.visa_expiry_date, 20);
1133
1134 PLSQL_Block :=
1135 'DECLARE
1136 l_visa_rec Igs_Pe_Visapass_Pub.Visa_Rec_TYPE;
1137 BEGIN
1138 l_visa_rec.visa_type := :1;
1139 l_visa_rec.visa_number := :2;
1140 l_visa_rec.visa_issue_date := :3;
1141 l_visa_rec.visa_expiry_date := :4;
1142 l_visa_rec.person_id := :5;
1143
1144 Igs_Pe_Visapass_Pub.Create_Visa
1145 (p_api_version => 1.0
1146 ,p_init_msg_list => Fnd_Api.G_TRUE
1147 ,p_visa_rec => l_visa_rec
1148 ,x_return_status => :6
1149 ,x_msg_count => :7
1150 ,x_msg_data => :8
1151 ,x_visa_id => :9
1152 );
1153 END;';
1154 EXECUTE IMMEDIATE PLSQL_Block
1155 Using l_oss_visa_rec.visa_type
1156 ,l_oss_visa_rec.visa_number
1157 ,l_oss_visa_rec.visa_issue_date
1158 ,l_oss_visa_rec.visa_expiry_date
1159 ,p_party_id
1160 ,OUT l_return_status
1161 ,OUT l_msg_count
1162 ,OUT l_msg_data
1163 ,OUT l_visa_id;
1164
1165 END IF;
1166 -- Update Into OSS
1167 IF l_update_OSS_rec THEN
1168 Hr_Utility.set_location('..visa_type: '||l_oss_visa_rec.visa_type, 21);
1169 Hr_Utility.set_location('..visa_number: '||l_oss_visa_rec.visa_number,21);
1170 Hr_Utility.set_location('..visa_issue_date: '||l_oss_visa_rec.visa_issue_date, 21);
1171 Hr_Utility.set_location('..visa_expiry_date: '||l_oss_visa_rec.visa_expiry_date, 21);
1172
1173 PLSQL_Block :=
1174 'DECLARE
1175 l_visa_rec Igs_Pe_Visapass_Pub.Visa_Rec_TYPE;
1176 BEGIN
1177 l_visa_rec.visa_type := :1;
1178 l_visa_rec.visa_number := :2;
1179 l_visa_rec.visa_issue_date := :3;
1180 l_visa_rec.visa_expiry_date := :4;
1181 l_visa_rec.person_id := :5;
1182 l_visa_rec.visa_id := :6;
1183
1184 Igs_Pe_Visapass_Pub.Update_Visa
1185 (p_api_version => 1.0
1186 ,p_init_msg_list => Fnd_Api.G_TRUE
1187 ,p_visa_rec => l_visa_rec
1188 ,x_return_status => :7
1189 ,x_msg_count => :8
1190 ,x_msg_data => :9
1191 );
1192 END;';
1193 EXECUTE IMMEDIATE PLSQL_Block
1194 Using l_oss_visa_rec.visa_type
1195 ,l_oss_visa_rec.visa_number
1196 ,l_oss_visa_rec.visa_issue_date
1197 ,l_oss_visa_rec.visa_expiry_date
1198 ,p_party_id
1199 ,l_oss_visa_rec.visa_id
1200 ,OUT l_return_status
1201 ,OUT l_msg_count
1202 ,OUT l_msg_data;
1203
1204 END IF;
1205 Hr_Utility.set_location(' l_return_status: '||l_return_status, 45);
1206 Hr_Utility.set_location(' l_msg_data: '||l_msg_data, 45);
1207 IF l_return_status IN ('E','U') THEN
1208 RAISE e_visa_syn_err;
1209 END IF;
1210
1211 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
1212 EXCEPTION
1213 WHEN e_visa_syn_err THEN
1214 l_error_msg := Substrb(l_msg_data,1,2000);
1215 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
1216 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
1217 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1218 Hr_Utility.raise_error;
1219 WHEN Others THEN
1220 l_error_msg := Substrb(SQLERRM,1,2000);
1221 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
1222 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
1223 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1224 Hr_Utility.raise_error;
1225 END InsUpd_OSS_Visa;
1226
1227 -- =============================================================================
1228 -- ~ InsUpd_OSS_VistHistory:
1229 -- =============================================================================
1230 PROCEDURE InsUpd_OSS_VisitHistory
1231 (p_person_id IN Number
1232 ,p_party_id IN Number
1233 ,p_action IN Varchar2
1234 ,p_pei_info_rec_old IN per_people_extra_info%ROWTYPE
1235 ,p_pei_info_rec_new IN per_people_extra_info%ROWTYPE
1236 ) AS
1237
1238 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_OSS_VistHistory';
1239 l_oss_vvhist_rec_old Visit_Hist_Rec;
1240 l_oss_vvhist_rec_new Visit_Hist_Rec;
1241 --
1242 TYPE csr_oss_t IS REF CURSOR;
1243 csr_visit csr_oss_t;
1244 csr_visa csr_oss_t;
1245 PLSQL_Block Varchar2(2000);
1246 SQLstmt Varchar2(2000);
1247 --
1248 l_oss_vvhist_cur_rec Visit_Hist_Rec;
1249 l_visa_rec oss_visa_rec;
1250 l_remarks Varchar2(2000);
1251 l_visa_no Varchar2(150);
1252 l_old_visa_id Number(15);
1253 l_new_visa_id Number(15);
1254 --
1255 l_return_status Varchar2(10);
1256 l_msg_count Number;
1257 l_msg_data Varchar2(2000);
1258 l_error_msg Varchar2(2000);
1259 e_visit_hstry_err EXCEPTION;
1260 --
1261 l_Update_OSS_rec Boolean;
1262 l_Insert_OSS_rec Boolean;
1263 l_dft_date Varchar2(150);
1264 BEGIN
1265 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1266 l_dft_date := Fnd_Date.Date_To_Canonical(sysdate);
1267 l_Update_OSS_Rec := FALSE; l_Insert_OSS_Rec := FALSE;
1268 SQLstmt := ' SELECT row_id
1269 ,port_of_entry
1270 ,port_of_entry_m
1271 ,cntry_entry_form_num
1272 ,visa_id
1273 ,visa_type
1274 ,visa_number
1275 ,visa_issue_date
1276 ,visa_expiry_date
1277 ,visa_category
1278 ,visa_issuing_post
1279 ,passport_id
1280 ,visit_start_date
1281 ,visit_end_date
1282 FROM igs_pe_visit_histry_v
1283 WHERE person_id = :c_party_id
1284 AND visa_number = :c_visa_number';
1285
1286 IF p_action = 'UPDATE' THEN
1287 IF (nvl(p_pei_info_rec_new.pei_information11,'$$') =
1288 nvl(p_pei_info_rec_old.pei_information11,'$$') AND
1289
1290 Fnd_Date.Canonical_To_Date
1291 (p_pei_info_rec_new.pei_information7) =
1292 Fnd_Date.Canonical_To_Date
1293 (p_pei_info_rec_old.pei_information7) AND
1294
1295 Fnd_Date.Canonical_To_Date
1296 (nvl(p_pei_info_rec_new.pei_information8,l_dft_date)) =
1297 Fnd_Date.Canonical_To_Date
1298 (nvl(p_pei_info_rec_old.pei_information8,l_dft_date)) AND
1299
1300 nvl(p_pei_info_rec_new.pei_information12,'$$') =
1301 nvl(p_pei_info_rec_old.pei_information12,'$$') AND
1302 nvl(p_pei_info_rec_new.pei_information13,'$$') =
1303 nvl(p_pei_info_rec_old.pei_information13,'$$')
1304 )
1305 THEN
1306 RETURN;
1307 END IF;
1308 IF(nvl(p_pei_info_rec_new.pei_information12,'$$') <>
1309 nvl(p_pei_info_rec_old.pei_information12,'$$')) or
1310 (nvl(p_pei_info_rec_new.pei_information13,'$$') <>
1311 nvl(p_pei_info_rec_old.pei_information13,'$$')) THEN
1312 l_msg_data := 'Port Of Entry and Entry Number cannot be changed for a student.';
1313 RAISE e_visit_hstry_err;
1314 END IF;
1315 ELSIF p_action = 'INSERT' THEN
1316
1317 IF(p_pei_info_rec_new.pei_information12 Is Null or
1318 p_pei_info_rec_new.pei_information13 Is Null) THEN
1319 l_msg_data := 'Port Of Entry and Entry Number are required for student.';
1320 RAISE e_visit_hstry_err;
1321 END IF;
1322 END IF;
1323 -- =================================================================================
1324 -- Purpose (VS)-R = pei_information5 = This is Specific to HRMS
1325 -- Visa Number = pei_information11 = igs_pe_visit_histry_v.visa_number
1326 -- Start Date -R = pei_information7 = igs_pe_visit_histry.visit_start_date
1327 -- End Date = pei_information8 = igs_pe_visit_histry.visit_end_date
1328 -- Spouse Accompanied (VS)-R = pei_information9 = This is Specific to HRMS
1329 -- Child Accompanied (VS)-R = pei_information10 = This is Specific to HRMS
1330 -- Entry Number = pei_information12 = igs_pe_visit_histry.cntry_entry_form_num
1331 -- Port Of Entry (VS) = pei_information13 = igs_pe_visit_histry.port_of_entry
1332 -- ================================================================================
1333 IF p_action = 'INSERT' THEN
1334 Hr_Utility.set_location(' p_action: '||p_action, 10);
1335 l_oss_vvhist_rec_new.visit_start_date
1336 := Fnd_Date.Canonical_To_Date(p_pei_info_rec_new.pei_information7);
1337 l_oss_vvhist_rec_new.visit_end_date
1338 := Fnd_Date.Canonical_To_Date(p_pei_info_rec_new.pei_information8);
1339 l_oss_vvhist_rec_new.cntry_entry_form_num
1340 := p_pei_info_rec_new.pei_information12;
1341 l_oss_vvhist_rec_new.port_of_entry
1342 := p_pei_info_rec_new.pei_information13;
1343
1344 l_Insert_OSS_Rec := TRUE;
1345 l_visa_no := p_pei_info_rec_new.pei_information11;
1346
1347 ELSIF p_action = 'UPDATE' THEN
1348 Hr_Utility.set_location(' p_action: '||p_action, 10);
1349 Hr_Utility.set_location(' Old Visa Number: '||p_pei_info_rec_old.pei_information11, 10);
1350 Hr_Utility.set_location(' New Visa Number: '||p_pei_info_rec_new.pei_information11, 10);
1351 IF (Trim(p_pei_info_rec_old.pei_information11) =
1352 Trim(p_pei_info_rec_new.pei_information11)) THEN
1353 Hr_Utility.set_location(' Visa Number: '||p_pei_info_rec_new.pei_information11, 11);
1354 OPEN csr_visit FOR SQLstmt
1355 Using p_party_id
1356 ,p_pei_info_rec_new.pei_information11;
1357 FETCH csr_visit INTO l_oss_vvhist_rec_old ;
1358 IF csr_visit%FOUND THEN
1359 Hr_Utility.set_location(' Visit History record found in OSS ', 11);
1360 Hr_Utility.set_location(' Old End Date: '||p_pei_info_rec_old.pei_information8, 11);
1361 Hr_Utility.set_location(' New End Date: '||p_pei_info_rec_new.pei_information8, 11);
1362 IF Fnd_Date.Canonical_To_Date
1363 (NVL(p_pei_info_rec_old.pei_information8,l_dft_date)) <>
1364 Fnd_Date.Canonical_To_Date
1365 (NVL(p_pei_info_rec_new.pei_information8,l_dft_date))
1366 THEN
1367 Hr_Utility.set_location(' New End Date <> Old End Date ', 11);
1368 -- The Visa Visit History being updated exists in OSS
1369 l_update_OSS_rec := TRUE;
1370 l_oss_vvhist_rec_new := l_oss_vvhist_rec_old;
1371 END IF;
1372 ELSE
1373 Hr_Utility.set_location(' Visit History record NOT found in OSS ', 11);
1374 -- That means the visa visit history that is being updated in
1375 -- HR is not there in OSS, so create it.
1376 l_update_OSS_rec := FALSE;
1377 l_insert_OSS_rec := TRUE;
1378 l_visa_no := p_pei_info_rec_new.pei_information11;
1379 END IF;
1380 CLOSE csr_visit;
1381
1382 ELSIF (Trim(p_pei_info_rec_old.pei_information11) <>
1383 Trim(p_pei_info_rec_new.pei_information11)) THEN
1384 Hr_Utility.set_location(' Old Visa Number : '||p_pei_info_rec_old.pei_information11, 11);
1385 Hr_Utility.set_location(' New Visa Number : '||p_pei_info_rec_new.pei_information11, 11);
1386 OPEN csr_visit FOR SQLstmt
1387 Using p_party_id
1388 ,p_pei_info_rec_new.pei_information11;
1389 FETCH csr_visit INTO l_oss_vvhist_rec_old ;
1390 IF csr_visit%NOTFOUND THEN
1391 l_Insert_OSS_Rec := TRUE;
1392 IF Fnd_Date.Canonical_To_Date
1393 (NVL(p_pei_info_rec_old.pei_information8,l_dft_date)) <>
1394 Fnd_Date.Canonical_To_Date
1395 (NVL(p_pei_info_rec_new.pei_information8,l_dft_date))
1396 THEN
1397 l_Update_OSS_Rec := TRUE;
1398 l_oss_vvhist_rec_new := l_oss_vvhist_rec_old;
1399 END IF;
1400 END IF;
1401 CLOSE csr_visit;
1402 END IF;
1403 l_oss_vvhist_rec_new.visit_start_date := Fnd_Date.Canonical_To_Date
1404 (p_pei_info_rec_new.pei_information7);
1405 l_oss_vvhist_rec_new.visit_end_date := Fnd_Date.Canonical_To_Date
1406 (p_pei_info_rec_new.pei_information8);
1407 l_oss_vvhist_rec_new.cntry_entry_form_num:= p_pei_info_rec_new.pei_information12;
1408 l_oss_vvhist_rec_new.port_of_entry := p_pei_info_rec_new.pei_information13;
1409
1410 END IF;
1411 -- Insert Into OSS
1412 Hr_Utility.set_location(' Get the visa_id for : '||p_pei_info_rec_old.pei_information11, 12);
1413 SQLstmt:='SELECT visa_id
1414 FROM igs_pe_visa
1415 WHERE person_id = :c_party_id
1416 AND visa_number = :c_visa_number';
1417
1418 OPEN csr_visa FOR SQLstmt Using p_party_id
1419 ,p_pei_info_rec_old.pei_information11;
1420 FETCH csr_visa INTO l_old_visa_id;
1421 CLOSE csr_visa;
1422 Hr_Utility.set_location(' Old Visa Id: '||l_old_visa_id, 13);
1423
1424 OPEN csr_visa FOR SQLstmt Using p_party_id
1425 ,p_pei_info_rec_new.pei_information11;
1426 FETCH csr_visa INTO l_new_visa_id;
1427 CLOSE csr_visa;
1428 Hr_Utility.set_location(' New Visa Id: '||l_new_visa_id, 13);
1429
1430 Hr_Utility.set_location(' port_of_entry: '||l_oss_vvhist_rec_new.port_of_entry, 15);
1431 Hr_Utility.set_location(' cntry_entry_form_num: '||l_oss_vvhist_rec_new.cntry_entry_form_num, 15);
1432 Hr_Utility.set_location(' visit_start_date: '||l_oss_vvhist_rec_new.visit_start_date, 15);
1433 Hr_Utility.set_location(' visit_end_date: '||l_oss_vvhist_rec_new.visit_end_date, 15);
1434
1435 IF l_insert_OSS_rec THEN
1436 l_oss_vvhist_rec_new.visa_id := l_new_visa_id;
1437 Hr_Utility.set_location(' visa_id: '||l_new_visa_id, 21);
1438 Hr_Utility.set_location(' Calling: IGS_PE_VisaPass_Pub.Create_VisitHistry', 21);
1439 PLSQL_Block :=
1440 'DECLARE
1441 l_visit_hstry_rec Igs_Pe_Visapass_Pub.Visit_Hstry_Rec_TYPE;
1442 BEGIN
1443 l_visit_hstry_rec.port_of_entry := :1;
1444 l_visit_hstry_rec.cntry_entry_form_num := :2;
1445 l_visit_hstry_rec.visa_id := :3;
1446 l_visit_hstry_rec.visit_start_date := :4;
1447 l_visit_hstry_rec.visit_end_date := :5;
1448 l_visit_hstry_rec.remarks := NULL;
1449
1450 Igs_Pe_Visapass_Pub.Create_VisitHistry
1451 (p_api_version => 1.0
1452 ,p_init_msg_list => Fnd_Api.G_TRUE
1453 ,p_visit_hstry_rec => l_visit_hstry_rec
1454 ,x_return_status => :6
1455 ,x_msg_count => :7
1456 ,x_msg_data => :8
1457 );
1458 END;';
1459
1460 EXECUTE IMMEDIATE PLSQL_Block
1461 Using l_oss_vvhist_rec_new.port_of_entry
1462 ,l_oss_vvhist_rec_new.cntry_entry_form_num
1463 ,l_oss_vvhist_rec_new.visa_id
1464 ,l_oss_vvhist_rec_new.visit_start_date
1465 ,l_oss_vvhist_rec_new.visit_end_date
1466 ,OUT l_return_status
1467 ,OUT l_msg_count
1468 ,OUT l_msg_data;
1469 END IF;
1470 -- Update Into OSS
1471 IF l_update_OSS_rec THEN
1472 l_oss_vvhist_rec_new.visa_id := l_new_visa_id;
1473 Hr_Utility.set_location(' visa_id: '||l_new_visa_id, 21);
1474 Hr_Utility.set_location(' Calling: IGS_PE_VisaPass_Pub.Update_VisitHistry', 21);
1475
1476 PLSQL_Block :=
1477 'DECLARE
1478 l_visit_hstry_rec Igs_Pe_Visapass_Pub.Visit_Hstry_Rec_TYPE;
1479 BEGIN
1480 l_visit_hstry_rec.port_of_entry := :1;
1481 l_visit_hstry_rec.cntry_entry_form_num := :2;
1482 l_visit_hstry_rec.visa_id := :3;
1483 l_visit_hstry_rec.visit_start_date := :4;
1484 l_visit_hstry_rec.visit_end_date := :5;
1485 l_visit_hstry_rec.remarks := NULL;
1486
1487 Igs_Pe_Visapass_Pub.Update_VisitHistry
1488 (p_api_version => 1.0
1489 ,p_init_msg_list => Fnd_Api.G_TRUE
1490 ,p_visit_hstry_rec => l_visit_hstry_rec
1491 ,x_return_status => :6
1492 ,x_msg_count => :7
1493 ,x_msg_data => :8
1494 );
1495 END;';
1496
1497 EXECUTE IMMEDIATE PLSQL_Block
1498 Using l_oss_vvhist_rec_new.port_of_entry
1499 ,l_oss_vvhist_rec_new.cntry_entry_form_num
1500 ,l_oss_vvhist_rec_new.visa_id
1501 ,l_oss_vvhist_rec_new.visit_start_date
1502 ,l_oss_vvhist_rec_new.visit_end_date
1503 ,OUT l_return_status
1504 ,OUT l_msg_count
1505 ,OUT l_msg_data;
1506 END IF;
1507 Hr_Utility.set_location(' l_return_status: '||l_return_status, 45);
1508 Hr_Utility.set_location(' l_msg_data: '||l_msg_data, 45);
1509 IF l_return_status IN ('E','U') THEN
1510 RAISE e_visit_hstry_err;
1511 END IF;
1512
1513 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
1514
1515 EXCEPTION
1516 WHEN e_visit_hstry_err THEN
1517 l_error_msg := Substrb(l_msg_data,1,2000);
1518 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
1519 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
1520 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1521 Hr_Utility.raise_error;
1522 WHEN Others THEN
1523 l_error_msg := Substrb(SQLERRM,1,2000);
1524 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
1525 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
1526 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1527 Hr_Utility.raise_error;
1528 END InsUpd_OSS_VisitHistory;
1529
1530 -- =============================================================================
1531 -- ~ InsUpd_InHR_PassPort:
1532 -- =============================================================================
1533 PROCEDURE InsUpd_InHR_PassPort
1534 (p_business_group_id IN Number
1535 ,p_person_id IN Number
1536 ,p_party_id IN Number
1537 ,p_effective_date IN Date
1538 ,p_pp_error_code OUT NOCOPY Varchar2
1539 ,p_passport_warning OUT NOCOPY Boolean
1540 ) AS
1541 -- Existing Passport details in HRMS
1542 CURSOR csr_pe_pass (c_person_id IN Number
1543 ,c_pp_number IN Varchar2
1544 ,c_pp_cntry_code IN Varchar2) IS
1545 SELECT pei.pei_information5
1546 ,pei.pei_information6
1547 ,pei.pei_information7
1548 ,pei.pei_information8
1549 ,pei.object_version_number
1550 ,pei.person_extra_info_id
1551 FROM per_people_extra_info pei
1552 WHERE pei.person_id = c_person_id
1553 AND pei.information_type ='PER_US_PASSPORT_DETAILS'
1554 AND pei.pei_information5 = c_pp_cntry_code
1555 AND pei.pei_information6 = c_pp_number ;
1556 l_hr_pe_pass csr_pe_pass%ROWTYPE;
1557
1558 TYPE csr_oss_t IS REF CURSOR;
1559 SQLstmt Varchar2(2000);
1560 csr_pp csr_oss_t;
1561 l_oss_pp_rec oss_pp_rec;
1562 l_action Varchar2(50);
1563 l_passport_category per_people_extra_info.information_type%TYPE;
1564 l_person_extra_info_rec per_people_extra_info%ROWTYPE;
1565 l_cntry_mapping csr_cntry_code%ROWTYPE;
1566 --
1567 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_InHR_PassPort';
1568 --
1569 BEGIN
1570 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1571 SAVEPOINT oss_passport_dtls;
1572 l_passport_category := 'PER_US_PASSPORT_DETAILS';
1573 --
1574 SQLstmt := ' SELECT pap.rowid '||
1575 ' ,pap.passport_id '||
1576 ' ,pap.passport_cntry_code '||
1577 ' ,pap.passport_number '||
1578 ' ,pap.passport_expiry_date '||
1579 ' ,pap.attribute_category '||
1580 ' ,pap.attribute1 '||
1581 ' ,pap.attribute2 '||
1582 ' ,pap.attribute3 '||
1583 ' ,pap.attribute4 '||
1584 ' ,pap.attribute5 '||
1585 ' ,pap.attribute6 '||
1586 ' ,pap.attribute7 '||
1587 ' ,pap.attribute8 '||
1588 ' ,pap.attribute9 '||
1589 ' ,pap.attribute10 '||
1590 ' ,pap.attribute11 '||
1591 ' ,pap.attribute12 '||
1592 ' ,pap.attribute13 '||
1593 ' ,pap.attribute14 '||
1594 ' ,pap.attribute15 '||
1595 ' ,pap.attribute16 '||
1596 ' ,pap.attribute17 '||
1597 ' ,pap.attribute18 '||
1598 ' ,pap.attribute19 '||
1599 ' ,pap.attribute20 '||
1600 ' FROM igs_pe_passport pap '||
1601 ' WHERE pap.person_id = :c_party_id ';
1602
1603
1604 l_person_extra_info_rec := NULL;
1605 -- Create the Passport details in HRMS
1606 Hr_Utility.set_location(' Creating: '||l_passport_category, 8);
1607 l_person_extra_info_rec.information_type := l_passport_category;
1608 l_person_extra_info_rec.pei_information_category := l_passport_category;
1609 -- ===========================================================================
1610 -- Country (VS)-R = pei_information5 = igs_pe_passport.passport_cntry_code
1611 -- Passport Number-R = pei_information6 = igs_pe_passport.passport_number
1612 -- Issue Date = pei_information7 = default to null
1613 -- Expiry Date = pei_information8 = igs_pe_passport.passport_expiry_date
1614 -- ===========================================================================
1615 OPEN csr_pp FOR SQLstmt Using p_party_id;
1616 LOOP
1617 FETCH csr_pp INTO l_oss_pp_rec;
1618 EXIT WHEN csr_pp%NOTFOUND;
1619 OPEN csr_cntry_code
1620 (c_country_code => l_oss_pp_rec.passport_cntry_code
1621 ,c_map_to => 'OSS_TO_HR');
1622 FETCH csr_cntry_code INTO l_cntry_mapping;
1623 CLOSE csr_cntry_code;
1624
1625 l_person_extra_info_rec.pei_information5
1626 := nvl(l_cntry_mapping.ins_code,
1627 l_oss_pp_rec.passport_cntry_code);
1628 l_person_extra_info_rec.pei_information6
1629 := l_oss_pp_rec.passport_number;
1630 l_person_extra_info_rec.pei_information7 := NULL;
1631 l_person_extra_info_rec.pei_information8
1632 := Fnd_Date.date_to_canonical(l_oss_pp_rec.passport_expiry_date);
1633 -- Check if the passport no. already exists in HRMS, if yes the update
1634 -- that passport number.
1635 OPEN csr_pe_pass (c_person_id => p_person_id
1636 ,c_pp_number => l_oss_pp_rec.passport_number
1637 ,c_pp_cntry_code => l_oss_pp_rec.passport_cntry_code);
1638 FETCH csr_pe_pass INTO l_hr_pe_pass;
1639
1640 IF csr_pe_pass%NOTFOUND THEN
1641 l_action := 'CREATE';
1642 ELSE
1643 l_action := 'UPDATE';
1644 l_person_extra_info_rec.pei_information7 := l_hr_pe_pass.pei_information7;
1645 l_person_extra_info_rec.person_extra_info_id := l_hr_pe_pass.person_extra_info_id;
1646 l_person_extra_info_rec.object_version_number := l_hr_pe_pass.object_version_number;
1647 END IF;
1648 InsUpd_Per_Extra_info
1649 (p_person_id => p_person_id
1650 ,p_business_group_id => p_business_group_id
1651 ,p_action => l_action
1652 ,p_extra_info_rec => l_person_extra_info_rec
1653 );
1654
1655 CLOSE csr_pe_pass;
1656 END LOOP;
1657 CLOSE csr_pp;
1658 p_passport_warning := FALSE;
1659 p_pp_error_code := NULL;
1660
1661 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1662
1663 EXCEPTION
1664 WHEN Others THEN
1665 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1666 IF csr_pp%ISOPEN THEN
1667 CLOSE csr_pp;
1668 END IF;
1669 IF csr_pe_pass%ISOPEN THEN
1670 CLOSE csr_pe_pass;
1671 END IF;
1672
1673 ROLLBACK TO oss_passport_dtls;
1674 p_passport_warning := TRUE;
1675 p_pp_error_code := SQLCODE;
1676 END InsUpd_InHR_PassPort;
1677
1678 -- =============================================================================
1679 -- ~ InsUpd_InHR_Visa:
1680 -- =============================================================================
1681 PROCEDURE InsUpd_InHR_Visa
1682 (p_business_group_id IN Number
1683 ,p_person_id IN Number
1684 ,p_party_id IN Number
1685 ,p_effective_date IN Date
1686 ,p_visa_error_code OUT NOCOPY Varchar2
1687 ,p_visa_warning OUT NOCOPY Boolean
1688 ) AS
1689 -- Existing Visa details in HRMS
1690 CURSOR csr_pe_visa (c_person_id IN Number
1691 ,c_visa_type IN Varchar2
1692 ,c_visa_number IN Varchar2) IS
1693 SELECT pei.pei_information5
1694 ,pei.pei_information6
1695 ,pei.pei_information7
1696 ,pei.pei_information8
1697 ,pei.pei_information9
1698 ,pei.pei_information10
1699 ,pei.object_version_number
1700 ,pei.person_extra_info_id
1701 FROM per_people_extra_info pei
1702 WHERE pei.person_id = c_person_id
1703 AND pei.information_type ='PER_US_VISA_DETAILS'
1704 AND pei.pei_information5 = c_visa_type
1705 AND pei.pei_information6 = c_visa_number;
1706
1707 l_hr_pe_visa csr_pe_visa%ROWTYPE;
1708
1709 TYPE csr_oss_t IS REF CURSOR;
1710 SQLstmt Varchar2(2000);
1711 csr_visa csr_oss_t;
1712 l_visa_rec oss_visa_rec;
1713 l_action Varchar2(50);
1714 l_visa_category per_people_extra_info.information_type%TYPE;
1715 l_person_extra_info_rec per_people_extra_info%ROWTYPE;
1716 --
1717 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_InHR_Visa';
1718 --
1719 BEGIN
1720 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1721 SAVEPOINT oss_visa_dtls;
1722 l_visa_category := 'PER_US_VISA_DETAILS';
1723 --
1724 SQLstmt:='SELECT '||
1725 ' rowid '||
1726 ' ,visa_id '||
1727 ' ,visa_type '||
1728 ' ,visa_number '||
1729 ' ,visa_issue_date '||
1730 ' ,visa_expiry_date '||
1731 ' ,visa_category '||
1732 ' ,visa_issuing_post '||
1733 ' ,passport_id '||
1734 ' ,agent_org_unit_cd '||
1735 ' ,agent_person_id '||
1736 ' ,agent_contact_name '||
1737 ' ,visa_issuing_country '||
1738 ' ,attribute_category '||
1739 ' ,attribute1 '||
1740 ' ,attribute2 '||
1741 ' ,attribute3 '||
1742 ' ,attribute4 '||
1743 ' ,attribute5 '||
1744 ' ,attribute6 '||
1745 ' ,attribute7 '||
1746 ' ,attribute8 '||
1747 ' ,attribute9 '||
1748 ' ,attribute10 '||
1749 ' ,attribute11 '||
1750 ' ,attribute12 '||
1751 ' ,attribute13 '||
1752 ' ,attribute14 '||
1753 ' ,attribute15 '||
1754 ' ,attribute16 '||
1755 ' ,attribute17 '||
1756 ' ,attribute18 '||
1757 ' ,attribute19 '||
1758 ' ,attribute20 '||
1759 ' FROM igs_pe_visa '||
1760 'WHERE person_id = :c_party_id ';
1761
1762
1763 l_person_extra_info_rec := NULL;
1764 -- Create the Passport details in HRMS
1765 Hr_Utility.set_location(' Creating: '||l_visa_category, 8);
1766 l_person_extra_info_rec.information_type := l_visa_category;
1767 l_person_extra_info_rec.pei_information_category := l_visa_category;
1768 -- ===========================================================================
1769 -- Visa Type(VS)-R = pei_information5 = igs_pe_visa.visa_type
1770 -- Visa Number-R = pei_information6 = igs_pe_visa.visa_number
1771 -- Issue Date -R = pei_information7 = igs_pe_visa.visa_issue_date
1772 -- Expiry Date -R = pei_information8 = igs_pe_visa.visa_expiry_date
1773 -- J Visa Category = pei_information9 = default to null
1774 -- Pass To Interface = pei_information10 = N
1775 -- ===========================================================================
1776 OPEN csr_visa FOR SQLstmt Using p_party_id;
1777 LOOP
1778 FETCH csr_visa INTO l_visa_rec;
1779 EXIT WHEN csr_visa%NOTFOUND;
1780 l_person_extra_info_rec.pei_information5 := l_visa_rec.visa_type;
1781 l_person_extra_info_rec.pei_information6 := l_visa_rec.visa_number;
1782 l_person_extra_info_rec.pei_information7
1783 := Fnd_Date.date_to_canonical(l_visa_rec.visa_issue_date);
1784 l_person_extra_info_rec.pei_information8
1785 := Fnd_Date.date_to_canonical(l_visa_rec.visa_expiry_date);
1786 l_person_extra_info_rec.pei_information9 := '99';
1787 l_person_extra_info_rec.pei_information10 := 'N';
1788 -- Check if the existing visa is already existing, if yes then update it.
1789 OPEN csr_pe_visa (c_person_id => p_person_id
1790 ,c_visa_type => l_visa_rec.visa_type
1791 ,c_visa_number => l_visa_rec.visa_number);
1792 FETCH csr_pe_visa INTO l_hr_pe_visa;
1793 IF csr_pe_visa%NOTFOUND THEN
1794 l_action := 'CREATE';
1795 ELSE
1796 l_action := 'UPDATE';
1797 l_person_extra_info_rec.pei_information9 := l_hr_pe_visa.pei_information9;
1798 l_person_extra_info_rec.pei_information10 := l_hr_pe_visa.pei_information10;
1799 l_person_extra_info_rec.person_extra_info_id := l_hr_pe_visa.person_extra_info_id;
1800 l_person_extra_info_rec.object_version_number := l_hr_pe_visa.object_version_number;
1801 END IF;
1802 InsUpd_Per_Extra_info
1803 (p_person_id => p_person_id
1804 ,p_business_group_id => p_business_group_id
1805 ,p_action => l_action
1806 ,p_extra_info_rec => l_person_extra_info_rec
1807 );
1808 CLOSE csr_pe_visa;
1809
1810 END LOOP;
1811 CLOSE csr_visa;
1812 p_visa_warning := FALSE;
1813 p_visa_error_code := NULL;
1814 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1815
1816 EXCEPTION
1817 WHEN Others THEN
1818 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1819 IF csr_visa%ISOPEN THEN
1820 CLOSE csr_visa;
1821 END IF;
1822 IF csr_pe_visa%ISOPEN THEN
1823 CLOSE csr_pe_visa;
1824 END IF;
1825
1826 ROLLBACK TO oss_visa_dtls;
1827 p_visa_warning := TRUE;
1828 p_visa_error_code := SQLCODE;
1829
1830 END InsUpd_InHR_Visa;
1831
1832 -- =============================================================================
1833 -- ~ InsUpd_InHR_Visit:
1834 -- =============================================================================
1835 PROCEDURE InsUpd_InHR_Visit
1836 (p_business_group_id IN Number
1837 ,p_person_id IN Number
1838 ,p_party_id IN Number
1839 ,p_effective_date IN Date
1840 ,p_visit_error_code OUT NOCOPY Varchar2
1841 ,p_visit_warning OUT NOCOPY Boolean
1842 ) AS
1843 -- Existing Visa details in HRMS
1844 CURSOR csr_pe_visit (c_person_id IN Number
1845 ,c_visa_number IN Varchar2) IS
1846 SELECT pei.pei_information5
1847 ,pei.pei_information11
1848 ,pei.pei_information7
1849 ,pei.pei_information8
1850 ,pei.pei_information9
1851 ,pei.pei_information10
1852 ,pei.object_version_number
1853 ,pei.person_extra_info_id
1854 FROM per_people_extra_info pei
1855 WHERE pei.person_id = c_person_id
1856 AND pei.information_type = 'PER_US_VISIT_HISTORY'
1857 AND pei.pei_information11 = c_visa_number;
1858 --
1859 l_hr_pe_visit csr_pe_visit%ROWTYPE;
1860 --
1861 TYPE csr_oss_t IS REF CURSOR;
1862 SQLstmt Varchar2(2000);
1863 csr_visit csr_oss_t;
1864 l_vv_rec Visit_Hist_Rec;
1865 l_visit_category per_people_extra_info.information_type%TYPE;
1866 l_person_extra_info_rec per_people_extra_info%ROWTYPE;
1867 l_action Varchar2(50);
1868 --
1869 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_InHR_Visit';
1870 --
1871 BEGIN
1872 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1873 SAVEPOINT oss_vv_dtls;
1874 l_visit_category := 'PER_US_VISIT_HISTORY';
1875 --
1876 SQLstmt:=' SELECT row_id '||
1877 ' ,port_of_entry '||
1878 ' ,port_of_entry_m '||
1879 ' ,cntry_entry_form_num '||
1880 ' ,visa_id '||
1881 ' ,visa_type '||
1882 ' ,visa_number '||
1883 ' ,visa_issue_date '||
1884 ' ,visa_expiry_date '||
1885 ' ,visa_category '||
1886 ' ,visa_issuing_post '||
1887 ' ,passport_id '||
1888 ' ,visit_start_date '||
1889 ' ,visit_end_date '||
1890 ' FROM igs_pe_visit_histry_v '||
1891 ' WHERE person_id = :c_party_id ';
1892
1893 l_person_extra_info_rec := NULL;
1894 -- Create the Visa Visit History details in HRMS
1895 Hr_Utility.set_location(' Creating: '||l_visit_category, 8);
1896 l_person_extra_info_rec.information_type := l_visit_category;
1897 l_person_extra_info_rec.pei_information_category := l_visit_category;
1898 -- =================================================================================
1899 -- Purpose (VS)-R = pei_information5 = This is Specific to HRMS
1900 -- Visa Number = pei_information11 = igs_pe_visit_histry.visa_number
1901 -- Start Date -R = pei_information7 = igs_pe_visit_histry.visit_start_date
1902 -- End Date = pei_information8 = igs_pe_visit_histry.visit_end_date
1903 -- Spouse Accompanied (VS)-R = pei_information9 = Default to N
1904 -- Child Accompanied (VS)-R = pei_information10 = Default to N
1905 -- ================================================================================
1906 OPEN csr_visit FOR SQLstmt Using p_party_id;
1907 LOOP
1908 FETCH csr_visit INTO l_vv_rec;
1909 EXIT WHEN csr_visit%NOTFOUND;
1910 l_person_extra_info_rec.pei_information5 := '01';
1911 l_person_extra_info_rec.pei_information11 := l_vv_rec.visa_number;
1912 l_person_extra_info_rec.pei_information7 :=
1913 Fnd_Date.date_to_canonical(l_vv_rec.visit_start_date);
1914 l_person_extra_info_rec.pei_information8 :=
1915 Fnd_Date.date_to_canonical(l_vv_rec.visit_end_date);
1916 l_person_extra_info_rec.pei_information9 := 'N';
1917 l_person_extra_info_rec.pei_information10 := 'N';
1918 l_person_extra_info_rec.pei_information12 := l_vv_rec.cntry_entry_form_num;
1919 l_person_extra_info_rec.pei_information13 := l_vv_rec.port_of_entry;
1920 -- The Visa Number value-set refers to the person_id profile value, hence
1921 -- setting the profile value to the current person_id passed.
1922 Fnd_Profile.put('PER_PERSON_ID',p_person_id);
1923 -- Check if the existing visa visit history is already existing,
1924 -- if yes then update it.
1925 OPEN csr_pe_visit (c_person_id => p_person_id
1926 ,c_visa_number => l_vv_rec.visa_number);
1927 FETCH csr_pe_visit INTO l_hr_pe_visit;
1928 IF csr_pe_visit%NOTFOUND THEN
1929 l_action := 'CREATE';
1930 ELSE
1931 l_person_extra_info_rec.pei_information11 := l_hr_pe_visit.pei_information11;
1932 l_person_extra_info_rec.person_extra_info_id := l_hr_pe_visit.person_extra_info_id;
1933 l_person_extra_info_rec.object_version_number := l_hr_pe_visit.object_version_number;
1934 l_action := 'UPDATE';
1935 END IF;
1936 CLOSE csr_pe_visit;
1937 InsUpd_Per_Extra_info
1938 (p_person_id => p_person_id
1939 ,p_business_group_id => p_business_group_id
1940 ,p_action => l_action
1941 ,p_extra_info_rec => l_person_extra_info_rec
1942 );
1943 END LOOP;
1944 CLOSE csr_visit;
1945 p_visit_warning := FALSE;
1946 p_visit_error_code := NULL;
1947 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1948
1949 EXCEPTION
1950 WHEN Others THEN
1951 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1952 IF csr_visit%ISOPEN THEN
1953 CLOSE csr_visit;
1954 END IF;
1955 IF csr_pe_visit%ISOPEN THEN
1956 CLOSE csr_pe_visit;
1957 END IF;
1958 ROLLBACK TO oss_vv_dtls;
1959 p_visit_warning := TRUE;
1960 p_visit_error_code := SQLCODE;
1961
1962 END InsUpd_InHR_Visit;
1963
1964 -- =============================================================================
1965 -- ~ InsUpd_InHR_OSSPerDtls:
1966 -- =============================================================================
1967 PROCEDURE InsUpd_InHR_OSSPerDtls
1968 (p_business_group_id IN Number
1969 ,p_person_id IN Number
1970 ,p_party_id IN Number
1971 ,p_effective_date IN Date
1972 ,p_oss_error_code OUT NOCOPY Varchar2
1973 ,p_ossDtls_warning OUT NOCOPY Boolean
1974 ) AS
1975 -- Existing OSS Person Details
1976 CURSOR csr_OSS_pe (c_person_id IN Number
1977 ,c_information_type IN Varchar2) IS
1978 SELECT pei.pei_information1
1979 ,pei.pei_information2
1980 ,pei.pei_information3
1981 ,pei.pei_information4
1982 ,pei.pei_information5
1983 ,pei.object_version_number
1984 ,pei.person_extra_info_id
1985 FROM per_people_extra_info pei
1986 WHERE pei.person_id = c_person_id
1987 AND pei.information_type = c_information_type;
1988 l_OSS_pe csr_oss_pe%ROWTYPE;
1989 TYPE oss_per_rec IS RECORD
1990 ( person_id_type Varchar2(150)
1991 ,api_person_id Varchar2(150)
1992 ,person_number Varchar2(150)
1993 ,system_type Varchar2(150)
1994 );
1995 TYPE csr_oss_t IS REF CURSOR;
1996 SQLstmt Varchar2(2000);
1997 csr_igs csr_oss_t;
1998 l_oss_per_details oss_per_rec;
1999 l_oss_person_details per_people_extra_info.information_type%TYPE;
2000 l_person_extra_info_rec per_people_extra_info%ROWTYPE;
2001 --
2002 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'InsUpd_InHR_OSSPerDtls';
2003 --
2004 CURSOR hz_pe (c_party_id IN Number) IS
2005 SELECT hzp.party_number
2006 FROM hz_parties hzp
2007 WHERE hzp.party_id = c_party_id;
2008 BEGIN
2009 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2010 SAVEPOINT oss_per_dtls;
2011 l_oss_person_details := 'PQP_OSS_PERSON_DETAILS';
2012 --
2013 SQLstmt:=
2014 ' SELECT igp.person_id_type ' ||
2015 ' ,igp.api_person_id ' ||
2016 ' ,igp.person_number ' ||
2017 ' ,ipt.system_type ' ||
2018 ' FROM igs_pe_person_v igp ' ||
2019 ' ,igs_pe_typ_instances_all pti ' ||
2020 ' ,igs_pe_person_types ipt ' ||
2021 ' WHERE igp.person_id = :c_party_id ' ||
2022 ' AND pti.person_type_code = ipt.person_type_code ' ||
2023 ' AND pti.person_id = igp.person_id ' ||
2024 ' AND ipt.system_type IN ('||'''STUDENT'''||',' ||
2025 '''FACULTY'''||','||'''OTHER''' ||')' ;
2026
2027 l_person_extra_info_rec := NULL;
2028 -- Create the OSS Person EIT information
2029 Hr_Utility.set_location(' Creating: PQP_OSS_PERSON_DETAILS', 8);
2030
2031 l_person_extra_info_rec.information_type := l_oss_person_details;
2032 l_person_extra_info_rec.pei_information_category := l_oss_person_details;
2033 -- ===========================================================================
2034 -- OSS Person Type -(R) = PEI_INFORMATION1
2035 -- OSS Person Number -(R) = PEI_INFORMATION2
2036 -- Alternate Id Type = PEI_INFORMATION3
2037 -- Alternate Id No = PEI_INFORMATION4
2038 -- Synchronize OSS Data -(R) = PEI_INFORMATION5
2039 -- ===========================================================================
2040 OPEN csr_igs FOR SQLstmt Using p_party_id;
2041 FETCH csr_igs INTO l_oss_per_details;
2042 CLOSE csr_igs;
2043 Hr_Utility.set_location(' After Dyn SQL Ref Cursor', 8);
2044 l_person_extra_info_rec.pei_information1 := Nvl(l_oss_per_details.system_type
2045 ,'STUDENT');
2046 l_person_extra_info_rec.pei_information2 := l_oss_per_details.person_number;
2047 IF l_oss_per_details.person_number IS NULL THEN
2048 OPEN hz_pe(c_party_id => p_party_id);
2049 FETCH hz_pe INTO l_oss_per_details.person_number;
2050 CLOSE hz_pe;
2051 l_person_extra_info_rec.pei_information2
2052 := l_oss_per_details.person_number;
2053 END IF;
2054 l_person_extra_info_rec.pei_information3 := Null;
2055 l_person_extra_info_rec.pei_information4 := Null;
2056 l_person_extra_info_rec.pei_information5 := 'Y';
2057 OPEN csr_OSS_pe (c_person_id => p_person_id
2058 ,c_information_type => l_oss_person_details);
2059 FETCH csr_OSS_pe INTO l_OSS_pe;
2060 Hr_Utility.set_location(' After Cursor :csr_OSS_pe', 8);
2061 IF csr_OSS_pe%NOTFOUND THEN
2062 InsUpd_Per_Extra_info
2063 (p_person_id => p_person_id
2064 ,p_business_group_id => p_business_group_id
2065 ,p_action => 'CREATE'
2066 ,p_extra_info_rec => l_person_extra_info_rec
2067 );
2068 ELSE
2069 l_person_extra_info_rec.person_extra_info_id := l_OSS_pe.person_extra_info_id;
2070 l_person_extra_info_rec.object_version_number := l_OSS_pe.object_version_number;
2071 l_person_extra_info_rec.pei_information3 := l_OSS_pe.pei_information3;
2072 l_person_extra_info_rec.pei_information4 := l_OSS_pe.pei_information4;
2073 l_person_extra_info_rec.pei_information5 := 'Y';
2074 InsUpd_Per_Extra_info
2075 (p_person_id => p_person_id
2076 ,p_business_group_id => p_business_group_id
2077 ,p_action => 'UPDATE'
2078 ,p_extra_info_rec => l_person_extra_info_rec
2079 );
2080 END IF;
2081 CLOSE csr_OSS_pe;
2082 p_oss_error_code := NULL;
2083 p_ossDtls_warning := FALSE;
2084 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2085
2086 EXCEPTION
2087 WHEN Others THEN
2088 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2089 IF csr_OSS_pe%ISOPEN THEN
2090 CLOSE csr_OSS_pe;
2091 END IF;
2092
2093 ROLLBACK TO oss_per_dtls;
2094 p_oss_error_code := SQLCODE;
2095 p_ossDtls_warning := TRUE;
2096
2097 END InsUpd_InHR_OSSPerDtls;
2098
2099 -- =============================================================================
2100 -- ~ Person_Address_API:
2101 -- =============================================================================
2102 PROCEDURE Person_Address_API
2103 (p_HR_Address_Rec IN OUT NOCOPY Per_Addresses%ROWTYPE
2104 ,p_validate IN Boolean
2105 ,p_action IN Varchar2
2106 ,p_effective_date IN Date
2107 ,p_pradd_ovlapval_override IN Boolean
2108 ,p_validate_county IN Boolean
2109 ,p_primary_flag IN Varchar2
2110 ,p_HR_address_id OUT NOCOPY Number
2111 ,p_HR_object_version_number OUT NOCOPY Number) AS
2112
2113 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'Person_Address_API';
2114 l_error_msg Varchar2(2000);
2115 BEGIN
2116 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2117 IF Nvl(p_action,'CREATE') = 'CREATE' THEN
2118 Hr_Utility.set_location('..Creating primary address: '||p_HR_Address_Rec.Style, 6);
2119 Hr_Person_Address_Api.Create_Person_Address
2120 (p_validate => Nvl(p_validate,FALSE)
2121 ,p_effective_date => p_effective_date
2122 ,p_pradd_ovlapval_override => Nvl(p_pradd_ovlapval_override,FALSE)
2123 ,p_validate_county => Nvl(p_validate_county,TRUE)
2124 ,p_person_id => p_HR_Address_Rec.person_id
2125 ,p_primary_flag => Nvl(p_primary_flag,'Y')
2126 ,p_style => p_HR_Address_Rec.Style
2127 ,p_date_from => p_HR_Address_Rec.date_from
2128 ,p_date_to => p_HR_Address_Rec.date_to
2129 ,p_address_type => p_HR_Address_Rec.address_type
2130 ,p_comments => p_HR_Address_Rec.comments -- NULL , By Dbansal
2131 ,p_address_line1 => p_HR_Address_Rec.address_line1
2132 ,p_address_line2 => p_HR_Address_Rec.address_line2
2133 ,p_address_line3 => p_HR_Address_Rec.address_line3
2134 ,p_town_or_city => p_HR_Address_Rec.town_or_city
2135 ,p_region_1 => p_HR_Address_Rec.region_1
2136 ,p_region_2 => p_HR_Address_Rec.region_2
2137 ,p_region_3 => p_HR_Address_Rec.region_3
2138 ,p_postal_code => p_HR_Address_Rec.postal_code
2139 ,p_country => p_HR_Address_Rec.country
2140 ,p_telephone_number_1 => p_HR_Address_Rec.telephone_number_1
2141 ,p_telephone_number_2 => p_HR_Address_Rec.telephone_number_2
2142 ,p_telephone_number_3 => p_HR_Address_Rec.telephone_number_3
2143 ,p_addr_attribute_category => p_HR_Address_Rec.addr_attribute_category
2144 ,p_addr_attribute1 => p_HR_Address_Rec.addr_attribute1
2145 ,p_addr_attribute2 => p_HR_Address_Rec.addr_attribute2
2146 ,p_addr_attribute3 => p_HR_Address_Rec.addr_attribute3
2147 ,p_addr_attribute4 => p_HR_Address_Rec.addr_attribute4
2148 ,p_addr_attribute5 => p_HR_Address_Rec.addr_attribute5
2149 ,p_addr_attribute6 => p_HR_Address_Rec.addr_attribute6
2150 ,p_addr_attribute7 => p_HR_Address_Rec.addr_attribute7
2151 ,p_addr_attribute8 => p_HR_Address_Rec.addr_attribute8
2152 ,p_addr_attribute9 => p_HR_Address_Rec.addr_attribute9
2153 ,p_addr_attribute10 => p_HR_Address_Rec.addr_attribute10
2154 ,p_addr_attribute11 => p_HR_Address_Rec.addr_attribute11
2155 ,p_addr_attribute12 => p_HR_Address_Rec.addr_attribute12
2156 ,p_addr_attribute13 => p_HR_Address_Rec.addr_attribute13
2157 ,p_addr_attribute14 => p_HR_Address_Rec.addr_attribute14
2158 ,p_addr_attribute15 => p_HR_Address_Rec.addr_attribute15
2159 ,p_addr_attribute16 => p_HR_Address_Rec.addr_attribute16
2160 ,p_addr_attribute17 => p_HR_Address_Rec.addr_attribute17
2161 ,p_addr_attribute18 => p_HR_Address_Rec.addr_attribute18
2162 ,p_addr_attribute19 => p_HR_Address_Rec.addr_attribute19
2163 ,p_addr_attribute20 => p_HR_Address_Rec.addr_attribute20
2164 ,p_add_information13 => p_HR_Address_Rec.add_information13
2165 ,p_add_information14 => p_HR_Address_Rec.add_information14
2166 ,p_add_information15 => p_HR_Address_Rec.add_information15
2167 ,p_add_information16 => p_HR_Address_Rec.add_information16
2168 ,p_add_information17 => p_HR_Address_Rec.add_information17
2169 ,p_add_information18 => p_HR_Address_Rec.add_information18
2170 ,p_add_information19 => p_HR_Address_Rec.add_information19
2171 ,p_add_information20 => p_HR_Address_Rec.add_information20
2172 ,p_party_id => p_HR_Address_Rec.party_id
2173 ,p_address_id => p_HR_address_id
2174 ,p_object_version_number => p_HR_object_version_number
2175 );
2176 ELSIF p_action ='UPDATE' THEN
2177 Hr_Utility.set_location('..Updating Primary Address: '||p_HR_Address_Rec.Style, 6);
2178 Hr_Person_Address_Api.Update_Person_Address
2179 (p_validate => Nvl(p_validate,FALSE)
2180 ,p_effective_date => p_effective_date
2181 ,p_validate_county => Nvl(p_validate_county,TRUE)
2182 ,p_address_id => p_HR_Address_Rec.address_id
2183 ,p_object_version_number => p_HR_Address_Rec.object_version_number
2184 ,p_date_from => p_HR_Address_Rec.date_from
2185 ,p_date_to => p_HR_Address_Rec.date_to
2186 ,p_primary_flag => p_HR_Address_Rec.primary_flag
2187 ,p_address_type => p_HR_Address_Rec.address_type
2188 ,p_comments => p_HR_Address_Rec.comments -- NULL, By Dbansal
2189 ,p_address_line1 => p_HR_Address_Rec.address_line1
2190 ,p_address_line2 => p_HR_Address_Rec.address_line2
2191 ,p_address_line3 => p_HR_Address_Rec.address_line3
2192 ,p_town_or_city => p_HR_Address_Rec.town_or_city
2193 ,p_region_1 => p_HR_Address_Rec.region_1
2194 ,p_region_2 => p_HR_Address_Rec.region_2
2195 ,p_region_3 => p_HR_Address_Rec.region_3
2196 ,p_postal_code => p_HR_Address_Rec.postal_code
2197 ,p_country => p_HR_Address_Rec.country
2198 ,p_telephone_number_1 => p_HR_Address_Rec.telephone_number_1
2199 ,p_telephone_number_2 => p_HR_Address_Rec.telephone_number_2
2200 ,p_telephone_number_3 => p_HR_Address_Rec.telephone_number_3
2201 ,p_addr_attribute_category => p_HR_Address_Rec.addr_attribute_category
2202 ,p_addr_attribute1 => p_HR_Address_Rec.addr_attribute1
2203 ,p_addr_attribute2 => p_HR_Address_Rec.addr_attribute2
2204 ,p_addr_attribute3 => p_HR_Address_Rec.addr_attribute3
2205 ,p_addr_attribute4 => p_HR_Address_Rec.addr_attribute4
2206 ,p_addr_attribute5 => p_HR_Address_Rec.addr_attribute5
2207 ,p_addr_attribute6 => p_HR_Address_Rec.addr_attribute6
2208 ,p_addr_attribute7 => p_HR_Address_Rec.addr_attribute7
2209 ,p_addr_attribute8 => p_HR_Address_Rec.addr_attribute8
2210 ,p_addr_attribute9 => p_HR_Address_Rec.addr_attribute9
2211 ,p_addr_attribute10 => p_HR_Address_Rec.addr_attribute10
2212 ,p_addr_attribute11 => p_HR_Address_Rec.addr_attribute11
2213 ,p_addr_attribute12 => p_HR_Address_Rec.addr_attribute12
2214 ,p_addr_attribute13 => p_HR_Address_Rec.addr_attribute13
2215 ,p_addr_attribute14 => p_HR_Address_Rec.addr_attribute14
2216 ,p_addr_attribute15 => p_HR_Address_Rec.addr_attribute15
2217 ,p_addr_attribute16 => p_HR_Address_Rec.addr_attribute16
2218 ,p_addr_attribute17 => p_HR_Address_Rec.addr_attribute17
2219 ,p_addr_attribute18 => p_HR_Address_Rec.addr_attribute18
2220 ,p_addr_attribute19 => p_HR_Address_Rec.addr_attribute19
2221 ,p_addr_attribute20 => p_HR_Address_Rec.addr_attribute20
2222 ,p_add_information13 => p_HR_Address_Rec.add_information13
2223 ,p_add_information14 => p_HR_Address_Rec.add_information14
2224 ,p_add_information15 => p_HR_Address_Rec.add_information15
2225 ,p_add_information16 => p_HR_Address_Rec.add_information16
2226 ,p_add_information17 => p_HR_Address_Rec.add_information17
2227 ,p_add_information18 => p_HR_Address_Rec.add_information18
2228 ,p_add_information19 => p_HR_Address_Rec.add_information19
2229 ,p_add_information20 => p_HR_Address_Rec.add_information20
2230 ,p_party_id => p_HR_Address_Rec.party_id
2231 );
2232 END IF;
2233 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
2234 EXCEPTION
2235 WHEN Others THEN
2236 l_error_msg := Substrb(SQLERRM,1,2000);
2237 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
2238 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
2239 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2240 Hr_Utility.raise_error;
2241
2242 END Person_Address_API;
2243
2244 -- =============================================================================
2245 -- ~ Create_Address_TCA_To_HR
2246 -- =============================================================================
2247 PROCEDURE Create_Address_TCA_To_HR
2248 (p_validate IN Boolean
2249 ,p_effective_date IN Date
2250 ,p_party_id IN Number
2251 ,p_business_group_id IN Number
2252 ,p_party_site_id IN Number
2253 ,p_style IN Varchar2
2254 ,p_location_id IN Number
2255 ,p_pradd_ovlapval_override IN Boolean
2256 ,p_validate_county IN Boolean
2257 ,p_primary_flag IN Varchar2
2258 ,p_address_type IN Varchar2
2259 ,p_overide_TCA_Mapping IN Varchar2
2260 --,p_HZ_Location_Rec IN Hz_Location_V2pub.Location_Rec_Type
2261 -- Out Variable from HR
2262 ,p_HR_address_id OUT NOCOPY Number
2263 ,p_HR_object_version_number OUT NOCOPY Number
2264 ) AS
2265
2266 l_party_site_rec hz_party_sites%ROWTYPE;
2267 l_HR_Address_Rec Per_Addresses%ROWTYPE;
2268 --l_HZ_Loc_Rec Hz_Location_V2pub.Location_Rec_Type;
2269 --l_HZ_Loc_Row Hz_Locations%ROWTYPE;
2270 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Create_Address_TCA_To_HR';
2271 l_map_info_category Varchar2(150);
2272 l_error_msg Varchar2(2000);
2273 BEGIN
2274 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2275 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
2276 EXCEPTION
2277 WHEN Others THEN
2278 l_error_msg := Substrb(SQLERRM,1,2000);
2279 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
2280 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
2281 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2282 Hr_Utility.raise_error;
2283
2284 END Create_Address_TCA_To_HR;
2285
2286 -- =============================================================================
2287 -- ~ Update_Address_TCA_To_HR:
2288 -- =============================================================================
2289 PROCEDURE Update_Address_TCA_To_HR
2290 (p_validate IN Boolean
2291 ,p_effective_date IN Date
2292 ,p_party_id IN Number
2293 ,p_business_group_id IN Number
2294 ,p_party_site_id IN Number
2295 ,p_style IN Varchar2
2296 ,p_location_id IN Number
2297 ,p_pradd_ovlapval_override IN Boolean
2298 ,p_validate_county IN Boolean
2299 ,p_primary_flag IN Varchar2
2300 ,p_address_type IN Varchar2
2301 ,p_overide_TCA_Mapping IN Varchar2
2302 --,p_HZ_Location_Rec IN Hz_Location_V2pub.Location_Rec_Type
2303 -- Out Variable from HR
2304 ,p_HR_object_version_number IN OUT NOCOPY Number
2305 ) AS
2306 l_proc_name Varchar2(150);
2307 BEGIN
2308 l_proc_name := g_pkg ||'Create_Address_TCA_To_HR';
2309 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2310
2311
2312 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
2313 END Update_Address_TCA_To_HR;
2314 -- =============================================================================
2315 -- ~ Check_HR_Validations:
2316 -- =============================================================================
2317 PROCEDURE Check_HR_Validations
2318 (p_column_name IN Varchar2
2319 ,p_column_value IN Varchar2
2320 ,p_style IN Varchar2
2321 ,p_col_lookup_type IN Varchar2
2322 ,p_valid_col_value OUT NOCOPY Varchar2) AS
2323
2324 CURSOR csr_ff_val (c_context_code IN Varchar2
2325 ,c_column_name IN Varchar2) IS
2326 SELECT fcu.column_seq_num
2327 ,fcu.application_column_name
2328 ,fcu.end_user_column_name
2329 ,fcu.enabled_flag
2330 ,fcu.required_flag
2331 ,fvs.maximum_size
2332 ,fvs.uppercase_only_flag
2333 ,fvs.maximum_value
2334 ,fvs.minimum_value
2335 ,fvs.alphanumeric_allowed_flag
2336 ,fvs.validation_type
2337 FROM fnd_descr_flex_column_usages fcu,
2338 fnd_flex_value_sets fvs,
2339 fnd_flex_validation_tables fvt
2340 WHERE fcu.descriptive_flexfield_name = 'Address Structure'
2341 AND fcu.descriptive_flex_context_code = c_context_code
2342 AND fcu.application_column_name = c_column_name
2343 AND fcu.application_id = 800
2344 AND fcu.enabled_flag = 'Y'
2345 AND fvs.flex_value_set_id(+) = fcu.flex_value_set_id
2346 AND fvt.flex_value_set_id(+) = fvs.flex_value_set_id
2347 ORDER BY fcu.column_seq_num;
2348
2349 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'Check_HR_Validations';
2350 l_dff_val csr_ff_val%ROWTYPE;
2351 l_lookup_rec csr_meaning_code%ROWTYPE;
2352
2353 BEGIN
2354 p_valid_col_value := p_column_value;
2355 --
2356 OPEN csr_ff_val (c_context_code => p_style
2357 ,c_column_name => p_column_name);
2358 FETCH csr_ff_val INTO l_dff_val;
2359 CLOSE csr_ff_val;
2360 IF l_dff_val.uppercase_only_flag ='Y' THEN
2361 p_valid_col_value := Upper(p_column_value);
2362 END IF;
2363 IF l_dff_val.required_flag = 'Y' AND
2364 p_column_value IS NULL THEN
2365 -- Required Value cannot be null, Raise Error
2366 NULL;
2367 END IF;
2368 IF Nvl(l_dff_val.maximum_size,0) > 0 THEN
2369 IF Lengthb(p_column_value) > l_dff_val.maximum_size THEN
2370 -- Value exceeds the max. length
2371 NULL;
2372 END IF;
2373 END IF;
2374 IF p_col_lookup_type IS NOT NULL THEN
2375 -- Check if the meaning is being passed
2376 OPEN csr_meaning_code (c_lookup_type => p_col_lookup_type
2377 -- c_meaning could be code or meaning
2378 ,c_meaning => p_column_value
2379 ,c_effective_date => Trunc(g_effective_date));
2380
2381 FETCH csr_meaning_code INTO l_lookup_rec;
2382 IF csr_meaning_code%FOUND THEN
2383 CLOSE csr_meaning_code;
2384 p_valid_col_value := l_lookup_rec.lookup_code;
2385 RETURN;
2386 END IF;
2387 CLOSE csr_meaning_code;
2388 -- If column is not required then set it to Null
2389 IF l_dff_val.required_flag <> 'Y' THEN
2390 NULL;
2391 --p_valid_col_value := NULL;
2392 END IF;
2393 END IF;
2394
2395 EXCEPTION
2396 WHEN Others THEN
2397 RAISE;
2398 END Check_HR_Validations;
2399
2400 -- =============================================================================
2401 -- ~ Chk_GeoCodes_Installed:
2402 -- =============================================================================
2403 FUNCTION Chk_GeoCodes_Installed
2404 (p_leg_code IN Varchar2)
2405 RETURN Varchar2 IS
2406
2407 CURSOR csr_get_us_city_names
2408 (c_leg_code IN Varchar2) IS
2409 SELECT NULL
2410 FROM pay_us_city_names
2411 WHERE (c_leg_code = 'CA'AND
2412 state_code = 70)
2413 OR
2414 (c_leg_code ='US' AND
2415 state_code < 52);
2416 l_exists Varchar2(1);
2417 BEGIN
2418 -- Check if any rows exist in the pay_us_city_names
2419 OPEN csr_get_us_city_names(p_leg_code);
2420 FETCH csr_get_us_city_names INTO l_exists;
2421 IF csr_get_us_city_names%FOUND THEN
2422 RETURN 'Y';
2423 ELSE
2424 RETURN 'N';
2425 END IF;
2426 CLOSE csr_get_us_city_names;
2427 END Chk_GeoCodes_Installed;
2428
2429 -- =============================================================================
2430 -- ~ Chk_Address_Style:
2431 -- =============================================================================
2432 FUNCTION Chk_Address_Style
2433 (p_party_id IN Number
2434 ,p_bus_grp_id IN Number
2435 ) RETURN Varchar2 AS
2436 -- Cursor to get the party site id
2437 CURSOR csr_site_id (c_party_id IN Number
2438 ,c_primary_flag IN Varchar2) IS
2439 SELECT hzl.country
2440 FROM hz_party_sites hps,
2441 hz_locations hzl
2442 WHERE hps.location_id = hzl.location_id
2443 AND hps.status = 'A'
2444 AND hps.party_id = c_party_id
2445 AND hps.identifying_address_flag = c_primary_flag;
2446
2447 l_leg_code Varchar2(5);
2448 l_HZ_code Varchar2(5);
2449 l_return_value Varchar2(25);
2450 BEGIN
2451 OPEN csr_bg_code (c_bg_grp_id => p_bus_grp_id);
2452 FETCH csr_bg_code INTO l_leg_code;
2453 CLOSE csr_bg_code;
2454 OPEN csr_site_id (c_party_id => p_party_id
2455 ,c_primary_flag => p_bus_grp_id);
2456 FETCH csr_site_id INTO l_HZ_code;
2457 CLOSE csr_site_id;
2458 l_return_value:= Chk_Address_Style
2459 (p_leg_code => l_leg_code
2460 ,p_HZ_country => l_HZ_code);
2461 RETURN l_return_value;
2462 END Chk_Address_Style;
2463
2464 -- =============================================================================
2465 -- ~ Chk_Address_Style:
2466 -- =============================================================================
2467 FUNCTION Chk_Address_Style
2468 (p_leg_code IN Varchar2
2469 ,p_HZ_country IN Varchar2) RETURN Varchar2 IS
2470
2471 l_Style per_addresses.style%TYPE;
2472 l_Style_Code per_addresses.style%TYPE;
2473 l_leg_inst_rec csr_chk_prod%ROWTYPE;
2474
2475 BEGIN
2476 IF p_leg_code = p_HZ_Country THEN
2477 l_Style := p_leg_code;
2478 ELSIF p_HZ_Country IS NULL THEN
2479 l_Style := p_leg_code;
2480 ELSIF p_leg_code <> p_HZ_Country THEN
2481 l_Style := p_HZ_Country;
2482 END IF;
2483
2484 OPEN csr_chk_prod (c_leg_code => l_Style
2485 ,c_app_short_name => 'PER');
2486 FETCH csr_chk_prod INTO l_leg_inst_rec;
2487 IF csr_chk_prod%NOTFOUND THEN
2488 l_Style := l_Style||'_GLB';
2489 END IF;
2490 CLOSE csr_chk_prod;
2491 -- Check if the Global address style for the country is
2492 -- available if not then default to GENERIC style.
2493 OPEN csr_style(l_Style);
2494 FETCH csr_style INTO l_Style_Code;
2495 IF csr_style%NOTFOUND THEN
2496 l_Style := 'GENERIC';
2497 END IF;
2498 CLOSE csr_style;
2499 --
2500 IF l_Style = 'US' AND
2501 Chk_GeoCodes_Installed(p_leg_code ) = 'N' THEN
2502 l_Style := 'US_GLB';
2503 ELSIF l_Style = 'CA' THEN
2504 OPEN csr_chk_prod (c_leg_code => p_HZ_Country
2505 ,c_app_short_name => 'PAY');
2506 FETCH csr_chk_prod INTO l_leg_inst_rec;
2507 IF csr_chk_prod%NOTFOUND THEN
2508 l_Style := 'CA_GLB';
2509 END IF;
2510 CLOSE csr_chk_prod;
2511 ELSIF l_Style ='GB_GLB' THEN
2512 l_Style := 'GB';
2513 END IF;
2514
2515 RETURN l_Style;
2516
2517 END Chk_Address_Style ;
2518
2519
2520 -- =============================================================================
2521 -- ~ Chk_Address_Style:Used by Web ADI in download query.
2522 -- ~ returns NULL if Address doesn't exist.
2523 -- =============================================================================
2524 FUNCTION Chk_Address_Style
2525 (p_leg_code IN Varchar2
2526 ,p_HZ_country IN Varchar2
2527 ,p_location_id IN Number
2528 ,p_party_id IN Number
2529 ,p_effective_date IN Date
2530 ,p_business_group_id IN Number
2531 ,p_primary_flag IN Varchar2
2532 ,p_party_site_id IN Number) RETURN Varchar2 IS
2533
2534 l_Style per_addresses.style%TYPE;
2535 l_Style_Code per_addresses.style%TYPE;
2536 l_leg_inst_rec csr_chk_prod%ROWTYPE;
2537
2538 BEGIN
2539 IF p_leg_code = p_HZ_Country THEN
2540 l_Style := p_leg_code;
2541 ELSIF p_HZ_Country IS NULL THEN
2542 l_Style := p_leg_code;
2543 ELSIF p_leg_code <> p_HZ_Country THEN
2544 l_Style := p_HZ_Country;
2545 END IF;
2546
2547 OPEN csr_chk_prod (c_leg_code => l_Style
2548 ,c_app_short_name => 'PER');
2549 FETCH csr_chk_prod INTO l_leg_inst_rec;
2550 IF csr_chk_prod%NOTFOUND THEN
2551 l_Style := l_Style||'_GLB';
2552 END IF;
2553 CLOSE csr_chk_prod;
2554 -- Check if the Global address style for the country is
2555 -- available if not then default to GENERIC style.
2556 OPEN csr_style(l_Style);
2557 FETCH csr_style INTO l_Style_Code;
2558 IF csr_style%NOTFOUND THEN
2559 l_Style := 'GENERIC';
2560 END IF;
2561 CLOSE csr_style;
2562 --
2563 IF l_Style = 'US' AND
2564 Chk_GeoCodes_Installed(p_leg_code ) = 'N' THEN
2565 l_Style := 'US_GLB';
2566 ELSIF l_Style = 'CA' THEN
2567 OPEN csr_chk_prod (c_leg_code => p_HZ_Country
2568 ,c_app_short_name => 'PAY');
2569 FETCH csr_chk_prod INTO l_leg_inst_rec;
2570 IF csr_chk_prod%NOTFOUND THEN
2571 l_Style := 'CA_GLB';
2572 END IF;
2573 CLOSE csr_chk_prod;
2574 ELSIF l_Style ='GB_GLB' THEN
2575 l_Style := 'GB';
2576 END IF;
2577
2578 -- Check if Address Exists or Not. If function returns NULL, then it means
2579 -- address doesn't exist and return NULL
2580 IF Get_Concat_HR_Address(p_location_id
2581 ,p_party_id
2582 ,p_effective_date
2583 ,p_business_group_id
2584 ,p_primary_flag
2585 ,p_party_site_id
2586 ) IS NULL THEN
2587 RETURN NULL;
2588 ELSE
2589 RETURN l_Style;
2590 END IF;
2591
2592 END Chk_Address_Style ;
2593
2594 -- =============================================================================
2595 -- ~ Map_To_HR_Address:
2596 -- =============================================================================
2597 PROCEDURE Map_To_HR_Address
2598 (p_column_name IN Varchar2
2599 ,p_column_value IN Varchar2
2600 ,p_col_lookup_type IN Varchar2
2601 ,p_HR_Address_Rec IN OUT NOCOPY Per_addresses%ROWTYPE
2602 ) AS
2603
2604 l_proc_name Varchar2(150);
2605 l_valid_col_value Varchar2(150);
2606
2607 BEGIN
2608 IF p_column_name = 'ADDRESS_LINE1' THEN
2609 Check_HR_Validations
2610 (p_column_name => p_column_name
2611 ,p_column_value => p_column_value
2612 ,p_style => p_HR_Address_Rec.Style
2613 ,p_col_lookup_type => p_col_lookup_type
2614 ,p_valid_col_value => l_valid_col_value);
2615
2616 p_HR_Address_Rec.Address_Line1 := l_valid_col_value;
2617
2618 ELSIF p_column_name = 'ADDRESS_LINE2' THEN
2619 Check_HR_Validations
2620 (p_column_name => p_column_name
2621 ,p_column_value => p_column_value
2622 ,p_style => p_HR_Address_Rec.Style
2623 ,p_col_lookup_type => p_col_lookup_type
2624 ,p_valid_col_value => l_valid_col_value);
2625
2626 p_HR_Address_Rec.Address_Line2 := l_valid_col_value;
2627
2628 ELSIF p_column_name = 'ADDRESS_LINE3' THEN
2629 Check_HR_Validations
2630 (p_column_name => p_column_name
2631 ,p_column_value => p_column_value
2632 ,p_style => p_HR_Address_Rec.Style
2633 ,p_col_lookup_type => p_col_lookup_type
2634 ,p_valid_col_value => l_valid_col_value);
2635
2636 p_HR_Address_Rec.Address_Line3 := l_valid_col_value;
2637
2638 ELSIF p_column_name = 'REGION_1' THEN
2639 Check_HR_Validations
2640 (p_column_name => p_column_name
2641 ,p_column_value => p_column_value
2642 ,p_style => p_HR_Address_Rec.Style
2643 ,p_col_lookup_type => p_col_lookup_type
2644 ,p_valid_col_value => l_valid_col_value);
2645
2646 p_HR_Address_Rec.Region_1 := l_valid_col_value;
2647
2648 ELSIF p_column_name = 'REGION_2' THEN
2649 Check_HR_Validations
2650 (p_column_name => p_column_name
2651 ,p_column_value => p_column_value
2652 ,p_style => p_HR_Address_Rec.Style
2653 ,p_col_lookup_type => p_col_lookup_type
2654 ,p_valid_col_value => l_valid_col_value);
2655
2656 p_HR_Address_Rec.Region_2 := l_valid_col_value;
2657
2658 ELSIF p_column_name = 'REGION_3' THEN
2659 Check_HR_Validations
2660 (p_column_name => p_column_name
2661 ,p_column_value => p_column_value
2662 ,p_style => p_HR_Address_Rec.Style
2663 ,p_col_lookup_type => p_col_lookup_type
2664 ,p_valid_col_value => l_valid_col_value);
2665
2666 p_HR_Address_Rec.Region_3 := l_valid_col_value;
2667
2668 ELSIF p_column_name = 'TOWN_OR_CITY' THEN
2669 Check_HR_Validations
2670 (p_column_name => p_column_name
2671 ,p_column_value => p_column_value
2672 ,p_style => p_HR_Address_Rec.Style
2673 ,p_col_lookup_type => p_col_lookup_type
2674 ,p_valid_col_value => l_valid_col_value);
2675
2676 p_HR_Address_Rec.Town_or_City := l_valid_col_value;
2677
2678 ELSIF p_column_name = 'POSTAL_CODE' THEN
2679 Check_HR_Validations
2680 (p_column_name => p_column_name
2681 ,p_column_value => p_column_value
2682 ,p_style => p_HR_Address_Rec.Style
2683 ,p_col_lookup_type => p_col_lookup_type
2684 ,p_valid_col_value => l_valid_col_value);
2685
2686 p_HR_Address_Rec.Postal_Code := l_valid_col_value;
2687
2688 ELSIF p_column_name = 'COUNTRY' THEN
2689 Check_HR_Validations
2690 (p_column_name => p_column_name
2691 ,p_column_value => p_column_value
2692 ,p_style => p_HR_Address_Rec.Style
2693 ,p_col_lookup_type => p_col_lookup_type
2694 ,p_valid_col_value => l_valid_col_value);
2695
2696 p_HR_Address_Rec.COUNTRY := l_valid_col_value;
2697
2698 ELSIF p_column_name = 'POSTAL_CODE' THEN
2699 Check_HR_Validations
2700 (p_column_name => p_column_name
2701 ,p_column_value => p_column_value
2702 ,p_style => p_HR_Address_Rec.Style
2703 ,p_col_lookup_type => p_col_lookup_type
2704 ,p_valid_col_value => l_valid_col_value);
2705
2706 p_HR_Address_Rec.Postal_Code := l_valid_col_value;
2707
2708 ELSIF p_column_name = 'DATE_FROM' THEN
2709 Check_HR_Validations
2710 (p_column_name => p_column_name
2711 ,p_column_value => p_column_value
2712 ,p_style => p_HR_Address_Rec.Style
2713 ,p_col_lookup_type => p_col_lookup_type
2714 ,p_valid_col_value => l_valid_col_value);
2715
2716 p_HR_Address_Rec.Date_From := Fnd_Date.Canonical_To_Date(l_valid_col_value);
2717
2718 ELSIF p_column_name = 'DATE_TO' THEN
2719 Check_HR_Validations
2720 (p_column_name => p_column_name
2721 ,p_column_value => p_column_value
2722 ,p_style => p_HR_Address_Rec.Style
2723 ,p_col_lookup_type => p_col_lookup_type
2724 ,p_valid_col_value => l_valid_col_value);
2725
2726 p_HR_Address_Rec.Date_To := Fnd_Date.Canonical_To_Date(l_valid_col_value);
2727
2728 ELSIF p_column_name = 'TELEPHONE_NUMBER_1' THEN
2729 Check_HR_Validations
2730 (p_column_name => p_column_name
2731 ,p_column_value => p_column_value
2732 ,p_style => p_HR_Address_Rec.Style
2733 ,p_col_lookup_type => p_col_lookup_type
2734 ,p_valid_col_value => l_valid_col_value);
2735
2736 p_HR_Address_Rec.Telephone_Number_1 := l_valid_col_value;
2737
2738 ELSIF p_column_name = 'TELEPHONE_NUMBER_2' THEN
2739 Check_HR_Validations
2740 (p_column_name => p_column_name
2741 ,p_column_value => p_column_value
2742 ,p_style => p_HR_Address_Rec.Style
2743 ,p_col_lookup_type => p_col_lookup_type
2744 ,p_valid_col_value => l_valid_col_value);
2745
2746 p_HR_Address_Rec.Telephone_Number_2 := l_valid_col_value;
2747
2748 ELSIF p_column_name = 'TELEPHONE_NUMBER_3' THEN
2749 Check_HR_Validations
2750 (p_column_name => p_column_name
2751 ,p_column_value => p_column_value
2752 ,p_style => p_HR_Address_Rec.Style
2753 ,p_col_lookup_type => p_col_lookup_type
2754 ,p_valid_col_value => l_valid_col_value);
2755
2756 p_HR_Address_Rec.Telephone_Number_3 := l_valid_col_value;
2757
2758 ELSIF p_column_name = 'ADD_INFORMATION13' THEN
2759 Check_HR_Validations
2760 (p_column_name => p_column_name
2761 ,p_column_value => p_column_value
2762 ,p_style => p_HR_Address_Rec.Style
2763 ,p_col_lookup_type => p_col_lookup_type
2764 ,p_valid_col_value => l_valid_col_value);
2765
2766 p_HR_Address_Rec.add_information13 := l_valid_col_value;
2767
2768 ELSIF p_column_name = 'ADD_INFORMATION14' THEN
2769 Check_HR_Validations
2770 (p_column_name => p_column_name
2771 ,p_column_value => p_column_value
2772 ,p_style => p_HR_Address_Rec.Style
2773 ,p_col_lookup_type => p_col_lookup_type
2774 ,p_valid_col_value => l_valid_col_value);
2775
2776 p_HR_Address_Rec.add_information14 := l_valid_col_value;
2777
2778 ELSIF p_column_name = 'ADD_INFORMATION15' THEN
2779 Check_HR_Validations
2780 (p_column_name => p_column_name
2781 ,p_column_value => p_column_value
2782 ,p_style => p_HR_Address_Rec.Style
2783 ,p_col_lookup_type => p_col_lookup_type
2784 ,p_valid_col_value => l_valid_col_value);
2785
2786 p_HR_Address_Rec.add_information15 := l_valid_col_value;
2787
2788 ELSIF p_column_name = 'ADD_INFORMATION16' THEN
2789 Check_HR_Validations
2790 (p_column_name => p_column_name
2791 ,p_column_value => p_column_value
2792 ,p_style => p_HR_Address_Rec.Style
2793 ,p_col_lookup_type => p_col_lookup_type
2794 ,p_valid_col_value => l_valid_col_value);
2795
2796 p_HR_Address_Rec.add_information16 := l_valid_col_value;
2797
2798 ELSIF p_column_name = 'ADD_INFORMATION17' THEN
2799 Check_HR_Validations
2800 (p_column_name => p_column_name
2801 ,p_column_value => p_column_value
2802 ,p_style => p_HR_Address_Rec.Style
2803 ,p_col_lookup_type => p_col_lookup_type
2804 ,p_valid_col_value => l_valid_col_value);
2805
2806 p_HR_Address_Rec.add_information17 := l_valid_col_value;
2807
2808 ELSIF p_column_name = 'ADD_INFORMATION18' THEN
2809 Check_HR_Validations
2810 (p_column_name => p_column_name
2811 ,p_column_value => p_column_value
2812 ,p_style => p_HR_Address_Rec.Style
2813 ,p_col_lookup_type => p_col_lookup_type
2814 ,p_valid_col_value => l_valid_col_value);
2815
2816 p_HR_Address_Rec.add_information18 := l_valid_col_value;
2817
2818 ELSIF p_column_name = 'ADD_INFORMATION19' THEN
2819 Check_HR_Validations
2820 (p_column_name => p_column_name
2821 ,p_column_value => p_column_value
2822 ,p_style => p_HR_Address_Rec.Style
2823 ,p_col_lookup_type => p_col_lookup_type
2824 ,p_valid_col_value => l_valid_col_value);
2825
2826 p_HR_Address_Rec.add_information19 := l_valid_col_value;
2827
2828 END IF;
2829
2830 END Map_To_HR_Address;
2831
2832 -- =============================================================================
2833 -- ~ Map_HR_Row_Values:
2834 -- =============================================================================
2835 PROCEDURE Map_HR_Row_Values
2836 (p_HR_Address_Rec IN OUT NOCOPY Per_addresses%ROWTYPE
2837 ,p_HZ_Location_Row IN Hz_Locations%ROWTYPE
2838 ,p_add_map IN Varchar2
2839 ,p_lookup_map IN Varchar2
2840 ,p_leg_code IN Varchar2
2841 ) AS
2842 CURSOR csr_phones (c_party_id IN Number) IS
2843 SELECT hcp.phone_country_code
2844 ,hcp.phone_area_code
2845 ,hcp.phone_number
2846 ,hcp.phone_extension
2847 ,hcp.primary_flag
2848 ,hcp.phone_line_type
2849 FROM hz_contact_points hcp
2850 WHERE hcp.contact_point_type = 'PHONE'
2851 AND hcp.status = 'A'
2852 AND hcp.owner_table_name = 'HZ_PARTIES'
2853 AND hcp.owner_table_id = c_party_id;
2854
2855 TYPE t_Phones IS TABLE OF hz_contact_points.phone_number%TYPE
2856 INDEX BY Binary_Integer;
2857 l_phone_rec t_Phones;
2858 l_phone_no hz_contact_points.phone_number%TYPE;
2859 l_Address_Mapping pqp_configuration_values%ROWTYPE;
2860 l_LookUp_Mapping pqp_configuration_values%ROWTYPE;
2861 ph_count Number(5);
2862
2863 BEGIN
2864 FOR i IN 1..3 LOOP
2865 l_phone_rec(i) := NULL;
2866 END LOOP;
2867 ph_count := 2;
2868 FOR ph_rec IN csr_phones(p_HR_Address_Rec.party_id)
2869 LOOP
2870 IF ph_rec.phone_country_code IS NOT NULL THEN
2871 l_phone_no := '+'|| ph_rec.phone_country_code;
2872 END IF;
2873 IF ph_rec.phone_area_code IS NOT NULL THEN
2874 l_phone_no := l_phone_no || ' ('||
2875 ph_rec.phone_area_code ||')';
2876 END IF;
2877 IF ph_rec.phone_number IS NOT NULL THEN
2878 l_phone_no := l_phone_no || ' '||
2879 ph_rec.phone_number;
2880 END IF;
2881 IF ph_rec.phone_extension IS NOT NULL THEN
2882 l_phone_no := l_phone_no || ' x '||
2883 ph_rec.phone_extension;
2884 END IF;
2885
2886 IF ph_rec.primary_flag ='Y' THEN
2887 l_phone_rec(1) := l_phone_no;
2888 ELSE
2889 l_phone_rec(ph_count) := l_phone_no;
2890 ph_count := ph_count + 1 ;
2891 END IF;
2892 l_phone_no := NULL;
2893 EXIT WHEN ph_count > 3;
2894 END LOOP;
2895
2896 OPEN csr_TCA_Map (c_info_category => p_add_map
2897 ,c_bg_grp_id => p_HR_Address_Rec.business_group_id
2898 ,c_bg_grp_leg_code => p_HR_Address_Rec.Style);
2899
2900 FETCH csr_TCA_Map INTO l_Address_Mapping;
2901 CLOSE csr_TCA_Map;
2902
2903 -- Get column Lookup mapping record from pqp_configuration_values table
2904 OPEN csr_TCA_Map (c_info_category => p_lookup_map
2905 ,c_bg_grp_id => p_HR_Address_Rec.business_group_id
2906 ,c_bg_grp_leg_code => p_HR_Address_Rec.Style);
2907
2908 FETCH csr_TCA_Map INTO l_LookUp_Mapping;
2909 CLOSE csr_TCA_Map;
2910 --Address1
2911 Map_To_HR_Address
2912 (p_column_name => l_Address_Mapping.pcv_information1
2913 ,p_column_value => p_HZ_Location_Row.Address1
2914 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information1
2915 ,p_HR_Address_Rec => p_HR_Address_Rec);
2916 -- Address2
2917 Map_To_HR_Address
2918 (p_column_name => l_Address_Mapping.pcv_information2
2919 ,p_column_value => p_HZ_Location_Row.Address2
2920 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information2
2921 ,p_HR_Address_Rec => p_HR_Address_Rec);
2922 -- Address3
2923 Map_To_HR_Address
2924 (p_column_name => l_Address_Mapping.pcv_information3
2925 ,p_column_value => p_HZ_Location_Row.Address3
2926 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information3
2927 ,p_HR_Address_Rec => p_HR_Address_Rec);
2928 -- Address4
2929 Map_To_HR_Address
2930 (p_column_name => l_Address_Mapping.pcv_information4
2931 ,p_column_value => p_HZ_Location_Row.Address4
2932 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information4
2933 ,p_HR_Address_Rec => p_HR_Address_Rec);
2934 -- City
2935 Map_To_HR_Address
2936 (p_column_name => l_Address_Mapping.pcv_information5
2937 ,p_column_value => p_HZ_Location_Row.City
2938 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information5
2939 ,p_HR_Address_Rec => p_HR_Address_Rec);
2940 -- State
2941 Map_To_HR_Address
2942 (p_column_name => l_Address_Mapping.pcv_information6
2943 ,p_column_value => p_HZ_Location_Row.State
2944 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information6
2945 ,p_HR_Address_Rec => p_HR_Address_Rec);
2946 -- Province
2947 Map_To_HR_Address
2948 (p_column_name => l_Address_Mapping.pcv_information7
2949 ,p_column_value => p_HZ_Location_Row.Province
2950 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information7
2951 ,p_HR_Address_Rec => p_HR_Address_Rec);
2952 -- County
2953 Map_To_HR_Address
2954 (p_column_name => l_Address_Mapping.pcv_information8
2955 ,p_column_value => p_HZ_Location_Row.County
2956 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information8
2957 ,p_HR_Address_Rec => p_HR_Address_Rec);
2958 -- Postal Code
2959 Map_To_HR_Address
2960 (p_column_name => l_Address_Mapping.pcv_information9
2961 ,p_column_value => p_HZ_Location_Row.Postal_Code
2962 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information9
2963 ,p_HR_Address_Rec => p_HR_Address_Rec);
2964 -- Country
2965 Map_To_HR_Address
2966 (p_column_name => l_Address_Mapping.pcv_information10
2967 ,p_column_value => p_HZ_Location_Row.Country
2968 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information10
2969 ,p_HR_Address_Rec => p_HR_Address_Rec);
2970 -- Primary Phone
2971 Map_To_HR_Address
2972 (p_column_name => l_Address_Mapping.pcv_information18
2973 ,p_column_value => l_phone_rec(1)
2974 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information18
2975 ,p_HR_Address_Rec => p_HR_Address_Rec);
2976 -- Secondary Phone one
2977 Map_To_HR_Address
2978 (p_column_name => l_Address_Mapping.pcv_information19
2979 ,p_column_value => l_phone_rec(2)
2980 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information19
2981 ,p_HR_Address_Rec => p_HR_Address_Rec);
2982 -- Secondary Phone two
2983 Map_To_HR_Address
2984 (p_column_name => l_Address_Mapping.pcv_information20
2985 ,p_column_value => l_phone_rec(3)
2986 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information20
2987 ,p_HR_Address_Rec => p_HR_Address_Rec);
2988
2989 END Map_HR_Row_Values;
2990
2991 -- =============================================================================
2992 -- ~ TCA_To_HR_Address:
2993 -- =============================================================================
2994 PROCEDURE TCA_To_HR_Address
2995 (p_HZ_Location_Row IN Hz_Locations%ROWTYPE
2996 ,p_Mapping_Type IN Varchar2 DEFAULT 'TCA_TO_HR'
2997 ,p_HR_Address_Rec IN OUT NOCOPY Per_addresses%ROWTYPE
2998 ) AS
2999 l_address_style per_addresses.style%TYPE;
3000 l_add_map Varchar2(150);
3001 l_lookup_map Varchar2(150);
3002 l_leg_code Varchar2(5);
3003 BEGIN
3004 OPEN csr_bg_code(p_HR_Address_Rec.Business_group_id);
3005 FETCH csr_bg_code INTO l_leg_code;
3006 CLOSE csr_bg_code;
3007 -- Get the appropiate HR Add. Style based on HZ country and bus. grp
3008 -- leg. code.
3009 l_address_style :=
3010 Chk_Address_Style
3011 (p_leg_code => l_leg_code
3012 ,p_HZ_country => p_HZ_Location_Row.Country);
3013
3014 p_HR_Address_Rec.Style := l_address_style;
3015
3016 l_add_map := 'PQP_HRTCA_PERADD_'|| Trim(l_address_style);
3017 l_lookup_map := l_add_map||'_LOOKUP';
3018
3019 Map_HR_Row_Values
3020 (p_HR_Address_Rec => p_HR_Address_Rec
3021 ,p_HZ_Location_Row => p_HZ_Location_Row
3022 ,p_add_map => l_add_map
3023 ,p_lookup_map => l_lookup_map
3024 ,p_leg_code => l_leg_code);
3025
3026 END TCA_To_HR_Address;
3027
3028 -- ===========================================================================
3029 -- ~ ConCat_Segments:
3030 -- ===========================================================================
3031
3032 PROCEDURE ConCat_Segments
3033 (p_HR_Address_Rec IN Per_addresses%ROWTYPE
3034 ,p_concat_string OUT NOCOPY Varchar2
3035 ) AS
3036
3037 CURSOR csr_delim IS
3038 SELECT concatenated_segment_delimiter
3039 FROM fnd_descriptive_flexs
3040 WHERE descriptive_flexfield_name = 'Address Structure'
3041 AND application_table_name = 'PER_ADDRESSES'
3042 AND application_id = 800;
3043
3044 CURSOR csr_add_cols (c_context IN Varchar) IS
3045 SELECT fcu.column_seq_num
3046 ,fcu.application_column_name
3047 ,fcu.end_user_column_name
3048 FROM fnd_descr_flex_column_usages fcu
3049 WHERE fcu.descriptive_flexfield_name = 'Address Structure'
3050 AND fcu.descriptive_flex_context_code = c_context
3051 AND fcu.application_id = 800
3052 AND fcu.enabled_flag = 'Y'
3053 ORDER BY fcu.column_seq_num;
3054
3055 l_delimiter Varchar2(15);
3056 l_rep_delimiter Varchar2(15);
3057
3058 BEGIN
3059 -- Get the flexfield DDF's delimilter
3060 OPEN csr_delim;
3061 FETCH csr_delim INTO l_delimiter;
3062 CLOSE csr_delim;
3063 l_rep_delimiter := '\'||l_delimiter;
3064
3065 -- First Seg. would be the DDF context i.e. Address style
3066 p_concat_string := REPLACE(p_HR_Address_Rec.Style
3067 ,l_delimiter
3068 ,l_rep_delimiter)|| l_delimiter;
3069
3070 FOR add_rec IN csr_add_cols (p_HR_Address_Rec.style)
3071 LOOP
3072
3073 IF add_rec.application_column_name = 'ADDRESS_LINE1' THEN
3074 p_concat_string := p_concat_string ||
3075 REPLACE(p_HR_Address_Rec.Address_Line1
3076 ,l_delimiter
3077 ,l_rep_delimiter)|| l_delimiter;
3078 ELSIF add_rec.application_column_name = 'ADDRESS_LINE2' THEN
3079 p_concat_string := p_concat_string ||
3080 REPLACE(p_HR_Address_Rec.Address_Line2
3081 ,l_delimiter
3082 ,l_rep_delimiter)|| l_delimiter;
3083 ELSIF add_rec.application_column_name = 'ADDRESS_LINE3' THEN
3084 p_concat_string := p_concat_string ||
3085 REPLACE(p_HR_Address_Rec.Address_Line3
3086 ,l_delimiter
3087 ,l_rep_delimiter) || l_delimiter;
3088 ELSIF add_rec.application_column_name = 'REGION_1' THEN
3089 p_concat_string := p_concat_string ||
3090 REPLACE(p_HR_Address_Rec.Region_1
3091 ,l_delimiter
3092 ,l_rep_delimiter) || l_delimiter;
3093
3094 ELSIF add_rec.application_column_name = 'REGION_2' THEN
3095 p_concat_string := p_concat_string ||
3096 REPLACE(p_HR_Address_Rec.Region_2
3097 ,l_delimiter
3098 ,l_rep_delimiter) || l_delimiter;
3099
3100 ELSIF add_rec.application_column_name = 'REGION_3' THEN
3101 p_concat_string := p_concat_string ||
3102 REPLACE(p_HR_Address_Rec.Region_3
3103 ,l_delimiter
3104 ,l_rep_delimiter) || l_delimiter;
3105
3106 ELSIF add_rec.application_column_name = 'TOWN_OR_CITY' THEN
3107 p_concat_string := p_concat_string ||
3108 REPLACE(p_HR_Address_Rec.Town_Or_City
3109 ,l_delimiter
3110 ,l_rep_delimiter) || l_delimiter;
3111 ELSIF add_rec.application_column_name = 'POSTAL_CODE' THEN
3112 p_concat_string := p_concat_string ||
3113 REPLACE(p_HR_Address_Rec.Postal_Code
3114 ,l_delimiter
3115 ,l_rep_delimiter) || l_delimiter;
3116
3117 ELSIF add_rec.application_column_name = 'COUNTRY' THEN
3118 p_concat_string := p_concat_string ||
3119 REPLACE(p_HR_Address_Rec.Country
3120 ,l_delimiter
3121 ,l_rep_delimiter) || l_delimiter;
3122 ELSIF add_rec.application_column_name = 'TELEPHONE_NUMBER_1' THEN
3123 p_concat_string := p_concat_string ||
3124 REPLACE(p_HR_Address_Rec.Telephone_Number_1
3125 ,l_delimiter
3126 ,l_rep_delimiter) || l_delimiter;
3127 ELSIF add_rec.application_column_name = 'TELEPHONE_NUMBER_2' THEN
3128 p_concat_string := p_concat_string ||
3129 REPLACE(p_HR_Address_Rec.Telephone_Number_2
3130 ,l_delimiter
3131 ,l_rep_delimiter) || l_delimiter;
3132 ELSIF add_rec.application_column_name = 'TELEPHONE_NUMBER_3' THEN
3133 p_concat_string := p_concat_string ||
3134 REPLACE(p_HR_Address_Rec.Telephone_Number_3
3135 ,l_delimiter
3136 ,l_rep_delimiter) || l_delimiter;
3137 END IF;
3138 --
3139 END LOOP;
3140
3141
3142 END ConCat_Segments;
3143
3144 -- =============================================================================
3145 -- ~ Get_Concat_HR_Address:
3146 -- =============================================================================
3147 FUNCTION Get_Concat_HR_Address
3148 (p_location_id IN Number
3149 ,p_party_id IN Number
3150 ,p_effective_date IN Date
3151 ,p_business_group_id IN Number
3152 ,p_primary_flag IN Varchar2
3153 ,p_party_site_id IN Number
3154 ) RETURN Varchar2 IS
3155
3156 CURSOR csr_sd IS
3157 SELECT effective_date FROM fnd_sessions
3158 WHERE session_id = (SELECT Userenv('SESSIONID')
3159 FROM dual);
3160 TYPE csr_oss_t IS REF CURSOR;
3161 csr_dt csr_oss_t;
3162 l_concat_add_string Varchar2(1000);
3163 l_HZ_Loc_Row Hz_Locations%ROWTYPE;
3164 l_HR_Address_Rec Per_addresses%ROWTYPE;
3165 l_business_group_id per_business_groups.business_group_id%TYPE;
3166 l_effective_date Date;
3167 BEGIN
3168 -- Get the HZ Locations details based on the id
3169 OPEN csr_hz_loc (c_location_id => p_location_id);
3170 FETCH csr_hz_loc INTO l_HZ_Loc_Row;
3171 CLOSE csr_hz_loc;
3172
3173 -- Assign the party id and bus. grp id to the address record.
3174 l_HR_Address_Rec.business_group_id := p_business_group_id;
3175 l_HR_Address_Rec.primary_flag := Nvl(p_primary_flag,'Y');
3176 l_HR_Address_Rec.party_id := p_party_id;
3177 g_effective_date := p_effective_date;
3178
3179 -- Map the Address from TCA to HR
3180 TCA_To_HR_Address
3181 (p_HZ_Location_Row => l_HZ_Loc_Row
3182 ,p_HR_Address_Rec => l_HR_Address_Rec
3183 ,p_Mapping_Type => 'TCA_TO_HR');
3184
3185 OPEN csr_dt FOR g_adddt_sql
3186 Using p_party_id
3187 ,p_location_id;
3188 FETCH csr_dt INTO l_HR_Address_Rec.Date_From,
3189 l_HR_Address_Rec.Date_To,
3190 l_HR_Address_Rec.Primary_Flag;
3191
3192 -- If no address exists then function should return NULL
3193 IF csr_dt%NOTFOUND THEN
3194 CLOSE csr_dt;
3195 RETURN NULL;
3196 END IF;
3197
3198 CLOSE csr_dt;
3199
3200 -- Return the concat. string
3201 ConCat_Segments
3202 (p_HR_Address_Rec => l_HR_Address_Rec
3203 ,p_concat_string => l_concat_add_string
3204 );
3205
3206 RETURN l_concat_add_string;
3207
3208 EXCEPTION
3209 WHEN Others THEN
3210 l_concat_add_string := NULL;
3211 RETURN l_concat_add_string;
3212
3213 END Get_Concat_HR_Address;
3214 -- =============================================================================
3215 -- ~ Get_Segment:
3216 -- =============================================================================
3217 FUNCTION Get_Segment
3218 (p_hzlocation_id IN Number
3219 ,p_party_id IN Number
3220 ,p_business_group_id IN Number
3221 ,p_seg_name IN Varchar2
3222 ) RETURN Varchar2 AS
3223
3224 TYPE csr_oss_t IS REF CURSOR;
3225 csr_dt csr_oss_t;
3226 l_HZ_Loc_Row Hz_Locations%ROWTYPE;
3227 l_HR_Address_Rec Per_addresses%ROWTYPE;
3228 l_business_group_id per_business_groups.business_group_id%TYPE;
3229 l_effective_date Date;
3230 l_return_value Varchar2(150);
3231
3232 BEGIN
3233 OPEN csr_hz_loc (c_location_id => p_hzlocation_id);
3234 FETCH csr_hz_loc INTO l_HZ_Loc_Row;
3235 CLOSE csr_hz_loc;
3236
3237 -- Assign the party id and bus. grp id to the address record.
3238 l_HR_Address_Rec.business_group_id := p_business_group_id;
3239 l_HR_Address_Rec.party_id := p_party_id;
3240 g_effective_date := Sysdate;
3241
3242 -- Map the Address from TCA to HR
3243 TCA_To_HR_Address
3244 (p_HZ_Location_Row => l_HZ_Loc_Row
3245 ,p_HR_Address_Rec => l_HR_Address_Rec
3246 ,p_Mapping_Type => 'TCA_TO_HR');
3247
3248 OPEN csr_dt FOR g_adddt_sql
3249 Using p_party_id
3250 ,p_hzlocation_id;
3251 FETCH csr_dt INTO l_HR_Address_Rec.Date_From,
3252 l_HR_Address_Rec.Date_To,
3253 l_HR_Address_Rec.Primary_Flag;
3254 CLOSE csr_dt;
3255
3256 IF p_seg_name = 'PRIMARY_FLAG' THEN
3257 l_return_value := l_HR_Address_Rec.Primary_Flag;
3258 ELSIF p_seg_name = 'STYLE' THEN
3259 l_return_value := l_HR_Address_Rec.Style;
3260 ELSIF p_seg_name = 'ADDRESS_LINE1' THEN
3261 l_return_value := l_HR_Address_Rec.Address_Line1;
3262 ELSIF p_seg_name = 'ADDRESS_LINE2' THEN
3263 l_return_value := l_HR_Address_Rec.Address_Line2;
3264 ELSIF p_seg_name = 'ADDRESS_LINE3' THEN
3265 l_return_value := l_HR_Address_Rec.Address_Line3;
3266 ELSIF p_seg_name = 'REGION_1' THEN
3267 l_return_value := l_HR_Address_Rec.Region_1;
3268 ELSIF p_seg_name = 'REGION_2' THEN
3269 l_return_value := l_HR_Address_Rec.Region_2;
3270 ELSIF p_seg_name = 'REGION_3' THEN
3271 l_return_value := l_HR_Address_Rec.Region_3;
3272 ELSIF p_seg_name = 'TOWN_OR_CITY' THEN
3273 l_return_value := l_HR_Address_Rec.Town_Or_City;
3274 ELSIF p_seg_name = 'POSTAL_CODE' THEN
3275 l_return_value := l_HR_Address_Rec.Postal_Code;
3276 ELSIF p_seg_name = 'COUNTRY' THEN
3277 l_return_value := l_HR_Address_Rec.Country;
3278 ELSIF p_seg_name = 'TELEPHONE_NUMBER_1' THEN
3279 l_return_value := l_HR_Address_Rec.Telephone_Number_1;
3280 ELSIF p_seg_name = 'TELEPHONE_NUMBER_2' THEN
3281 l_return_value := l_HR_Address_Rec.Telephone_Number_2;
3282 ELSIF p_seg_name = 'TELEPHONE_NUMBER_3' THEN
3283 l_return_value := l_HR_Address_Rec.Telephone_Number_3;
3284 ELSIF p_seg_name = 'DATE_FROM' THEN
3285 l_return_value := l_HR_Address_Rec.Date_From;
3286 ELSIF p_seg_name = 'DATE_TO' THEN
3287 l_return_value := l_HR_Address_Rec.Date_To;
3288 END IF;
3289
3290 RETURN l_return_value;
3291 EXCEPTION
3292 WHEN OTHERS THEN
3293 l_return_value := NULL;
3294 RETURN l_return_value;
3295 END Get_Segment;
3296 -- =============================================================================
3297 -- ~ Check_TCA_Validations:
3298 -- =============================================================================
3299 PROCEDURE Check_TCA_Validations
3300 (p_column_name IN Varchar2
3301 ,p_column_value IN Varchar2
3302 ,p_col_lookup_type IN Varchar2
3303 ,p_valid_col_value OUT NOCOPY Varchar2) AS
3304
3305
3306 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'Check_TCA_Validations';
3307 l_lookup_rec csr_meaning_code%ROWTYPE;
3308
3309 BEGIN
3310 p_valid_col_value := p_column_value;
3311 --
3312 IF p_col_lookup_type IS NOT NULL THEN
3313 -- Check if the meaning is being passed
3314 OPEN csr_meaning_code
3315 (c_lookup_type => p_col_lookup_type
3316 ,c_meaning => p_column_value
3317 ,c_effective_date => g_effective_date);
3318
3319 FETCH csr_meaning_code INTO l_lookup_rec;
3320 IF csr_meaning_code%FOUND THEN
3321 CLOSE csr_meaning_code;
3322 p_valid_col_value := l_lookup_rec.meaning;
3323 RETURN;
3324 END IF;
3325 CLOSE csr_meaning_code;
3326 END IF;
3327
3328 EXCEPTION
3329 WHEN Others THEN
3330 RAISE;
3331 END Check_TCA_Validations;
3332
3333 -- =============================================================================
3334 -- ~ Map_To_TCA_Address:
3335 -- =============================================================================
3336 PROCEDURE Map_To_TCA_Address
3337 (p_column_name IN Varchar2
3338 ,p_column_value IN Varchar2
3339 ,p_col_lookup_type IN Varchar2
3340 ,p_HZ_Location_Row IN OUT NOCOPY Hz_Locations%ROWTYPE
3341 ) AS
3342
3343 l_valid_col_value Varchar2(150);
3344 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'Map_To_TCA_Address';
3345 BEGIN
3346 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3347 Hr_Utility.set_location('..p_column_name : '||p_column_name, 6);
3348 Hr_Utility.set_location('..p_column_value : '||p_column_value, 6);
3349 Hr_Utility.set_location('..p_col_lookup_type: '||p_col_lookup_type, 6);
3350
3351 IF p_column_name IS NULL THEN
3352 RETURN;
3353 ELSIF p_column_name = 'ADDRESS1' THEN
3354 Check_TCA_Validations
3355 (p_column_name => p_column_name
3356 ,p_column_value => p_column_value
3357 ,p_col_lookup_type => p_col_lookup_type
3358 ,p_valid_col_value => l_valid_col_value);
3359
3360 p_HZ_Location_Row.Address1 := l_valid_col_value;
3361
3362 ELSIF p_column_name = 'ADDRESS2' THEN
3363 Check_TCA_Validations
3364 (p_column_name => p_column_name
3365 ,p_column_value => p_column_value
3366 ,p_col_lookup_type => p_col_lookup_type
3367 ,p_valid_col_value => l_valid_col_value);
3368
3369 p_HZ_Location_Row.Address2 := l_valid_col_value;
3370
3371 ELSIF p_column_name = 'ADDRESS3' THEN
3372 Check_TCA_Validations
3373 (p_column_name => p_column_name
3374 ,p_column_value => p_column_value
3375 ,p_col_lookup_type => p_col_lookup_type
3376 ,p_valid_col_value => l_valid_col_value);
3377
3378 p_HZ_Location_Row.Address3 := l_valid_col_value;
3379
3380 ELSIF p_column_name = 'ADDRESS4' THEN
3381 Check_TCA_Validations
3382 (p_column_name => p_column_name
3383 ,p_column_value => p_column_value
3384 ,p_col_lookup_type => p_col_lookup_type
3385 ,p_valid_col_value => l_valid_col_value);
3386
3387 p_HZ_Location_Row.Address4 := l_valid_col_value;
3388
3389 ELSIF p_column_name = 'CITY' THEN
3390 Check_TCA_Validations
3391 (p_column_name => p_column_name
3392 ,p_column_value => p_column_value
3393 ,p_col_lookup_type => p_col_lookup_type
3394 ,p_valid_col_value => l_valid_col_value);
3395
3396 p_HZ_Location_Row.City := l_valid_col_value;
3397
3398 ELSIF p_column_name = 'POSTAL_CODE' THEN
3399 Check_TCA_Validations
3400 (p_column_name => p_column_name
3401 ,p_column_value => p_column_value
3402 ,p_col_lookup_type => p_col_lookup_type
3403 ,p_valid_col_value => l_valid_col_value);
3404
3405 p_HZ_Location_Row.Postal_Code := l_valid_col_value;
3406
3407 ELSIF p_column_name = 'STATE' THEN
3408 Check_TCA_Validations
3409 (p_column_name => p_column_name
3410 ,p_column_value => p_column_value
3411 ,p_col_lookup_type => p_col_lookup_type
3412 ,p_valid_col_value => l_valid_col_value);
3413
3414 p_HZ_Location_Row.State := l_valid_col_value;
3415
3416 ELSIF p_column_name = 'PROVINCE' THEN
3417 Check_TCA_Validations
3418 (p_column_name => p_column_name
3419 ,p_column_value => p_column_value
3420 ,p_col_lookup_type => p_col_lookup_type
3421 ,p_valid_col_value => l_valid_col_value);
3422
3423 p_HZ_Location_Row.Province := l_valid_col_value;
3424
3425 ELSIF p_column_name = 'COUNTY' THEN
3426 Check_TCA_Validations
3427 (p_column_name => p_column_name
3428 ,p_column_value => p_column_value
3429 ,p_col_lookup_type => p_col_lookup_type
3430 ,p_valid_col_value => l_valid_col_value);
3431
3432 p_HZ_Location_Row.County := l_valid_col_value;
3433
3434 ELSIF p_column_name = 'COUNTRY' THEN
3435 Check_TCA_Validations
3436 (p_column_name => p_column_name
3437 ,p_column_value => p_column_value
3438 ,p_col_lookup_type => p_col_lookup_type
3439 ,p_valid_col_value => l_valid_col_value);
3440
3441 p_HZ_Location_Row.Country := l_valid_col_value;
3442
3443 END IF;
3444 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3445 END Map_To_TCA_Address;
3446
3447 -- =============================================================================
3448 -- Create_Address_HR_To_TCA:
3449 -- =============================================================================
3450 PROCEDURE Create_Address_HR_To_TCA
3451 (p_business_group_id IN Number
3452 ,p_person_id IN Number
3453 ,p_party_id IN Number
3454 ,p_address_id IN Number
3455 ,p_effective_date IN Date
3456 ,p_per_addr_rec_new IN per_addresses%ROWTYPE
3457 -- TCA
3458 ,p_party_type IN Varchar2
3459 ,p_action IN Varchar2
3460 ,p_status IN hz_party_sites.status%TYPE
3461 -- In Out Variables
3462 ,p_location_id IN OUT NOCOPY Number
3463 ,p_party_site_id IN OUT NOCOPY Number
3464 ,p_last_update_date IN OUT NOCOPY Date
3465 ,p_party_site_ovn IN OUT NOCOPY Number
3466 ,p_location_ovn IN OUT NOCOPY Number
3467 ,p_rowid IN OUT NOCOPY Varchar2
3468 -- Out Variables
3469 ,p_return_status OUT NOCOPY Varchar2
3470 ,p_msg_data OUT NOCOPY Varchar2
3471 ) AS
3472
3473 l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Create_Address_HR_To_TCA';
3474 l_HZ_Location_Row Hz_Locations%ROWTYPE;
3475 l_HR_Address_Rec Per_addresses%ROWTYPE;
3476 l_address_style per_addresses.style%TYPE;
3477 l_Address_Mapping pqp_configuration_values%ROWTYPE;
3478 l_LookUp_Mapping pqp_configuration_values%ROWTYPE;
3479 l_add_map Varchar2(150);
3480 l_lookup_map Varchar2(150);
3481 l_leg_code Varchar2(5);
3482
3483 PLSQL_Block Varchar2(2500);
3484 l_HZ_style Varchar2(50);
3485 l_other_details_1 Varchar2(150);
3486 l_other_details_2 Varchar2(150);
3487 l_other_details_3 Varchar2(150);
3488 e_oss_add_failure EXCEPTION;
3489 l_error_msg Varchar2(2000);
3490 --
3491 BEGIN
3492 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3493 -- Get the Person HR Address
3494 l_HR_Address_Rec := p_per_addr_rec_new;
3495
3496 -- Get the business group's Leg. Code
3497 OPEN csr_bg_code(l_HR_Address_Rec.Business_group_id);
3498 FETCH csr_bg_code INTO l_leg_code;
3499 CLOSE csr_bg_code;
3500 Hr_Utility.set_location('..l_leg_code : '||l_leg_code, 5);
3501 --
3502 l_add_map := 'PQP_HRTCA_TCAADD_'|| l_HR_Address_Rec.Style;
3503 l_lookup_map := l_add_map||'_LOOKUP';
3504 Hr_Utility.set_location('..l_add_map : '||l_add_map, 5);
3505 Hr_Utility.set_location('..l_lookup_map : '||l_lookup_map, 5);
3506 -- Get the Address Column mappings for context: PER_TCA_ADD_[XX]
3507 OPEN csr_TCA_Map
3508 (c_info_category => l_add_map
3509 ,c_bg_grp_id => l_HR_Address_Rec.business_group_id
3510 ,c_bg_grp_leg_code => l_HR_Address_Rec.Style);
3511
3512 FETCH csr_TCA_Map INTO l_Address_Mapping;
3513 IF csr_TCA_Map%NOTFOUND THEN
3514 Hr_Utility.set_location('..Mapping for rec not found for : '||l_add_map, 5);
3515 END IF;
3516 CLOSE csr_TCA_Map;
3517
3518 -- Get Column Lookup mapping record for context: PER_TCA_ADD_[XX]_LOOKUP
3519 OPEN csr_TCA_Map
3520 (c_info_category => l_lookup_map
3521 ,c_bg_grp_id => l_HR_Address_Rec.business_group_id
3522 ,c_bg_grp_leg_code => l_HR_Address_Rec.Style);
3523
3524 FETCH csr_TCA_Map INTO l_LookUp_Mapping;
3525 IF csr_TCA_Map%NOTFOUND THEN
3526 Hr_Utility.set_location('..Mapping for rec not found for : '||l_lookup_map, 5);
3527 END IF;
3528 CLOSE csr_TCA_Map;
3529 -- HR: Address Line1
3530 Map_To_TCA_Address
3531 (p_column_name => l_Address_Mapping.pcv_information1
3532 ,p_column_value => l_HR_Address_Rec.Address_Line1
3533 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information1
3534 ,p_HZ_Location_Row => l_HZ_Location_Row);
3535
3536 -- HR: Address Line2
3537 Map_To_TCA_Address
3538 (p_column_name => l_Address_Mapping.pcv_information2
3539 ,p_column_value => l_HR_Address_Rec.Address_Line2
3540 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information2
3541 ,p_HZ_Location_Row => l_HZ_Location_Row);
3542
3543 -- HR: Address Line3
3544 Map_To_TCA_Address
3545 (p_column_name => l_Address_Mapping.pcv_information3
3546 ,p_column_value => l_HR_Address_Rec.Address_Line3
3547 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information3
3548 ,p_HZ_Location_Row => l_HZ_Location_Row);
3549
3550 -- HR: Region 1
3551 Map_To_TCA_Address
3552 (p_column_name => l_Address_Mapping.pcv_information4
3553 ,p_column_value => l_HR_Address_Rec.Region_1
3554 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information4
3555 ,p_HZ_Location_Row => l_HZ_Location_Row);
3556
3557 -- HR: Region 2
3558 Map_To_TCA_Address
3559 (p_column_name => l_Address_Mapping.pcv_information5
3560 ,p_column_value => l_HR_Address_Rec.Region_2
3561 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information5
3562 ,p_HZ_Location_Row => l_HZ_Location_Row);
3563
3564 -- HR: Region 3
3565 Map_To_TCA_Address
3566 (p_column_name => l_Address_Mapping.pcv_information6
3567 ,p_column_value => l_HR_Address_Rec.Region_3
3568 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information6
3569 ,p_HZ_Location_Row => l_HZ_Location_Row);
3570
3571 -- HR: Town Or City
3572 Map_To_TCA_Address
3573 (p_column_name => l_Address_Mapping.pcv_information7
3574 ,p_column_value => l_HR_Address_Rec.town_or_city
3575 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information7
3576 ,p_HZ_Location_Row => l_HZ_Location_Row);
3577
3578 -- HR: Postal Code
3579 Map_To_TCA_Address
3580 (p_column_name => l_Address_Mapping.pcv_information8
3581 ,p_column_value => l_HR_Address_Rec.postal_code
3582 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information8
3583 ,p_HZ_Location_Row => l_HZ_Location_Row);
3584
3585 -- HR: Country
3586 Map_To_TCA_Address
3587 (p_column_name => l_Address_Mapping.pcv_information9
3588 ,p_column_value => l_HR_Address_Rec.Country
3589 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information9
3590 ,p_HZ_Location_Row => l_HZ_Location_Row);
3591
3592 -- HR: Address Date From
3593 Map_To_TCA_Address
3594 (p_column_name => l_Address_Mapping.pcv_information10
3595 ,p_column_value => l_HR_Address_Rec.Date_From
3596 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information10
3597 ,p_HZ_Location_Row => l_HZ_Location_Row);
3598
3599 -- HR: Address Date To
3600 Map_To_TCA_Address
3601 (p_column_name => l_Address_Mapping.pcv_information11
3602 ,p_column_value => l_HR_Address_Rec.Date_to
3603 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information11
3604 ,p_HZ_Location_Row => l_HZ_Location_Row);
3605
3606 -- HR: Add Information13
3607 Map_To_TCA_Address
3608 (p_column_name => l_Address_Mapping.pcv_information12
3609 ,p_column_value => l_HR_Address_Rec.Add_Information13
3610 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information12
3611 ,p_HZ_Location_Row => l_HZ_Location_Row);
3612
3613 -- HR: Add Information14
3614 Map_To_TCA_Address
3615 (p_column_name => l_Address_Mapping.pcv_information13
3616 ,p_column_value => l_HR_Address_Rec.Add_Information14
3617 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information13
3618 ,p_HZ_Location_Row => l_HZ_Location_Row);
3619
3620 -- HR: Add Information15
3621 Map_To_TCA_Address
3622 (p_column_name => l_Address_Mapping.pcv_information14
3623 ,p_column_value => l_HR_Address_Rec.Add_Information15
3624 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information14
3625 ,p_HZ_Location_Row => l_HZ_Location_Row);
3626
3627 -- HR: Add Information16
3628 Map_To_TCA_Address
3629 (p_column_name => l_Address_Mapping.pcv_information15
3630 ,p_column_value => l_HR_Address_Rec.Add_Information16
3631 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information15
3632 ,p_HZ_Location_Row => l_HZ_Location_Row);
3633
3634 -- HR: Add Information17
3635 Map_To_TCA_Address
3636 (p_column_name => l_Address_Mapping.pcv_information16
3637 ,p_column_value => l_HR_Address_Rec.Add_Information17
3638 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information16
3639 ,p_HZ_Location_Row => l_HZ_Location_Row);
3640
3641 -- HR: Add Information18
3642 Map_To_TCA_Address
3643 (p_column_name => l_Address_Mapping.pcv_information17
3644 ,p_column_value => l_HR_Address_Rec.Add_Information18
3645 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information17
3646 ,p_HZ_Location_Row => l_HZ_Location_Row);
3647
3648 -- HR: Telephone Number 1
3649 Map_To_TCA_Address
3650 (p_column_name => l_Address_Mapping.pcv_information18
3651 ,p_column_value => l_HR_Address_Rec.Telephone_Number_1
3652 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information18
3653 ,p_HZ_Location_Row => l_HZ_Location_Row);
3654
3655 -- HR: Telephone Number 2
3656 Map_To_TCA_Address
3657 (p_column_name => l_Address_Mapping.pcv_information19
3658 ,p_column_value => l_HR_Address_Rec.Telephone_Number_2
3659 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information19
3660 ,p_HZ_Location_Row => l_HZ_Location_Row);
3661 -- HR: Telephone Number 3
3662 Map_To_TCA_Address
3663 (p_column_name => l_Address_Mapping.pcv_information20
3664 ,p_column_value => l_HR_Address_Rec.Telephone_Number_3
3665 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information20
3666 ,p_HZ_Location_Row => l_HZ_Location_Row);
3667 Hr_Utility.set_location('..Call OSS Package : Igs_Pe_Person_Addr_Pkg.Insert_Row', 5);
3668 -- Call OSS API to create Student address in HZ
3669 PLSQL_Block :=
3670 'BEGIN '||
3671 'Igs_Pe_Person_Addr_Pkg.Insert_Row '||
3672 '(p_action => :p_action '||
3673 ',p_party_type => :p_party_type '||
3674 ',p_party_id => :p_party_Id '||
3675 ',p_status => :p_status '||
3676 ',p_start_dt => :p_date_from '||
3677 ',p_end_dt => :p_date_to '||
3678 ',p_country => :p_country '||
3679 ',p_address_style => :p_address_style '||
3680 ',p_addr_line_1 => :p_Address1 '||
3681 ',p_addr_line_2 => :p_Address2 '||
3682 ',p_addr_line_3 => :p_Address3 '||
3683 ',p_addr_line_4 => :p_Address4 '||
3684 ',p_date_last_verified => :p_effective_date '||
3685 ',p_correspondence => :p_correspondence '||
3686 ',p_city => :p_city '||
3687 ',p_state => :p_state '||
3688 ',p_province => :p_province '||
3689 ',p_county => :p_county '||
3690 ',p_postal_code => :p_postal_code '||
3691 ',p_address_lines_phonetic => :p_Address_Lines_Phonetic '||
3692 ',p_delivery_point_code => :p_Delivery_Point_Code '||
3693 ',p_other_details_1 => :p_other_details_1 '||
3694 ',p_other_details_2 => :p_other_details_2 '||
3695 ',p_other_details_3 => :p_other_details_3 '||
3696 -- In Out
3697 ',p_party_site_id => :p_party_site_id '||
3698 ',p_last_update_date => :p_last_update_date '||
3699 ',p_party_site_ovn => :p_party_site_ovn '||
3700 ',p_location_ovn => :p_location_ovn '||
3701 -- Out
3702 ',p_rowid => :p_rowid '||
3703 ',p_location_id => :p_location_id '||
3704 ',l_return_status => :p_return_status '||
3705 ',l_msg_data => :p_msg_data '||
3706 ' ); '||
3707 'END;';
3708
3709 EXECUTE IMMEDIATE PLSQL_Block
3710 Using p_action
3711 ,p_party_type
3712 ,p_party_Id
3713 ,p_status
3714 ,l_HR_Address_Rec.Date_From
3715 ,l_HR_Address_Rec.Date_To
3716 ,l_HZ_Location_Row.Country
3717 ,l_HZ_style
3718 ,l_HZ_Location_Row.Address1
3719 ,l_HZ_Location_Row.Address2
3720 ,l_HZ_Location_Row.Address3
3721 ,l_HZ_Location_Row.Address4
3722 ,p_effective_date
3723 ,'Y'
3724 ,l_HZ_Location_Row.City
3725 ,l_HZ_Location_Row.State
3726 ,l_HZ_Location_Row.Province
3727 ,l_HZ_Location_Row.County
3728 ,l_HZ_Location_Row.Postal_Code
3729 ,l_HZ_Location_Row.Address_Lines_Phonetic
3730 ,l_HZ_Location_Row.Delivery_Point_Code
3731 ,l_other_details_1
3732 ,l_other_details_2
3733 ,l_other_details_3
3734 -- In Out
3735 ,IN OUT p_party_site_id
3736 ,IN OUT p_last_update_date
3737 ,IN OUT p_party_site_ovn
3738 ,IN OUT p_location_ovn
3739 ,IN OUT p_rowid
3740 ,IN OUT p_location_id
3741 -- Out
3742 ,OUT p_return_status
3743 ,OUT p_msg_data;
3744 IF p_return_status IN ('E','U') THEN
3745 RAISE e_oss_add_failure;
3746 END IF;
3747 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
3748
3749 EXCEPTION
3750 WHEN e_oss_add_failure THEN
3751 l_error_msg := Substrb(p_msg_data,1,2000);
3752 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
3753 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
3754 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3755 Hr_Utility.raise_error;
3756
3757 WHEN Others THEN
3758 l_error_msg := Substrb(SQLERRM,1,2000);
3759 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
3760 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
3761 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3762 Hr_Utility.raise_error;
3763
3764 END Create_Address_HR_To_TCA;
3765 -- =============================================================================
3766 -- ~ Update_Address_HR_To_TCA
3767 -- =============================================================================
3768 PROCEDURE Update_Address_HR_To_TCA
3769 (p_business_group_id IN Number
3770 ,p_person_id IN Number
3771 ,p_party_id IN Number
3772 ,p_address_id IN Number
3773 ,p_effective_date IN Date
3774 ,p_per_addr_rec_new IN per_addresses%ROWTYPE
3775 ,p_per_addr_rec_old IN per_addresses%ROWTYPE
3776 -- TCA
3777 ,p_party_type IN Varchar2
3778 ,p_action IN Varchar2
3779 ,p_status IN Varchar2
3780 -- In Out Variables
3781 ,p_location_id IN OUT NOCOPY Number
3782 ,p_party_site_id IN OUT NOCOPY Number
3783 ,p_last_update_date IN OUT NOCOPY Date
3784 ,p_party_site_ovn IN OUT NOCOPY Number
3785 ,p_location_ovn IN OUT NOCOPY Number
3786 ,p_rowid IN OUT NOCOPY Varchar2
3787 -- Out Variables
3788 ,p_return_status OUT NOCOPY Varchar2
3789 ,p_msg_data OUT NOCOPY Varchar2
3790 ) AS
3791 -- Cursor to get the current Primary Location
3792 CURSOR csr_hz_loc(c_hz_location_id IN Number) IS
3793 SELECT hzl.*
3794 FROM hz_locations hzl
3795 WHERE hzl.location_id = c_hz_location_id;
3796
3797 l_proc_name CONSTANT Varchar2(150) := g_pkg ||'Update_Address_HR_To_TCA';
3798 l_HZ_Location_Row Hz_Locations%ROWTYPE;
3799 l_HZ_Location_Cur_Row Hz_Locations%ROWTYPE;
3800 l_HR_Address_Rec Per_addresses%ROWTYPE;
3801 l_address_style per_addresses.style%TYPE;
3802 l_Address_Mapping pqp_configuration_values%ROWTYPE;
3803 l_LookUp_Mapping pqp_configuration_values%ROWTYPE;
3804 --
3805 l_add_map Varchar2(150);
3806 l_lookup_map Varchar2(150);
3807 l_leg_code Varchar2(5);
3808 PLSQL_Block Varchar2(2500);
3809 l_HZ_style Varchar2(50);
3810 l_other_details_1 Varchar2(150);
3811 l_other_details_2 Varchar2(150);
3812 l_other_details_3 Varchar2(150);
3813 --
3814 l_OSS_Date_From Date;
3815 l_OSS_Date_To Date;
3816 l_OS_Primary_Flag Varchar2(5);
3817
3818 TYPE csr_party_addt IS REF CURSOR;
3819 csr_party_add csr_party_addt;
3820
3821 TYPE csr_oss_t IS REF CURSOR;
3822 csr_dt csr_oss_t;
3823
3824 l_update_pri_add Boolean;
3825 l_create_pri_add Boolean;
3826 --
3827 e_oss_add_failure EXCEPTION;
3828 l_error_msg Varchar2(2000);
3829
3830 BEGIN
3831 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3832 -- Get the Person HR Address
3833 l_HR_Address_Rec := p_per_addr_rec_new;
3834 g_effective_date := p_effective_date;
3835 --
3836 IF p_location_id IS NULL THEN
3837 Hr_Utility.set_location('..Dyn SQL to get ovn of location and site', 6);
3838 OPEN csr_party_add FOR g_hz_add_sql
3839 Using l_HR_Address_Rec.party_id;
3840 FETCH csr_party_add INTO
3841 p_location_id
3842 ,p_party_site_id
3843 ,p_last_update_date
3844 ,p_party_site_ovn
3845 ,p_location_ovn
3846 ,p_rowid;
3847 Hr_Utility.set_location('..After Fetch of Dyn SQL.', 6);
3848 IF csr_party_add%NOTFOUND THEN
3849 Hr_Utility.set_location('..Cannot find existing party primary address', 7);
3850 CLOSE csr_party_add;
3851 ELSE
3852 Hr_Utility.set_location('..Dyn SQL found loc record', 7);
3853 CLOSE csr_party_add;
3854
3855 OPEN csr_hz_loc(c_hz_location_id => p_location_id);
3856 FETCH csr_hz_loc INTO l_HZ_Location_Cur_Row;
3857 CLOSE csr_hz_loc;
3858 Hr_Utility.set_location('..Dyn SQL against OSS to get start date', 8);
3859 OPEN csr_dt FOR g_adddt_sql
3860 Using l_HR_Address_Rec.party_id
3861 ,p_location_id;
3862 FETCH csr_dt INTO l_OSS_Date_From,
3863 l_OSS_Date_To,
3864 l_OS_Primary_Flag;
3865 CLOSE csr_dt;
3866 IF l_HR_Address_Rec.Date_From BETWEEN
3867 NVL(l_OSS_Date_From,l_HR_Address_Rec.Date_From) AND
3868 Nvl(l_OSS_Date_To,To_Date('31/12/4712','DD/MM/YYYY')) THEN
3869 l_update_pri_add := TRUE;
3870 ELSE
3871 l_create_pri_add := TRUE;
3872 p_location_id := NULL;
3873 p_party_site_id := NULL;
3874 p_last_update_date := NULL;
3875 p_party_site_ovn := NULL;
3876 p_location_ovn := NULL;
3877 p_rowid := NULL;
3878
3879 END IF;
3880 END IF;
3881
3882 END IF;
3883 --
3884 Hr_Utility.set_location('..p_effective_date : '||p_effective_date, 8);
3885 -- Get the business group's Leg. Code
3886 OPEN csr_bg_code(l_HR_Address_Rec.Business_group_id);
3887 FETCH csr_bg_code INTO l_leg_code;
3888 IF csr_bg_code%NOTFOUND THEN
3889 Hr_Utility.set_location('..Leg Code not found for Id : '||
3890 l_HR_Address_Rec.Business_group_id,5);
3891 END IF;
3892 CLOSE csr_bg_code;
3893 --
3894 Hr_Utility.set_location('..Bus Grp Leg Code: '||l_leg_code, 6);
3895 l_add_map := 'PQP_HRTCA_TCAADD_'|| l_HR_Address_Rec.Style;
3896 l_lookup_map := l_add_map||'_LOOKUP';
3897
3898 Hr_Utility.set_location('..l_add_map : '||l_add_map, 7);
3899 Hr_Utility.set_location('..l_lookup_map: '||l_lookup_map,7);
3900 -- Get the Address Column mappings for context: PQP_HRTCA_TCAADD_[XX]
3901 OPEN csr_TCA_Map
3902 (c_info_category => l_add_map
3903 ,c_bg_grp_id => l_HR_Address_Rec.business_group_id
3904 ,c_bg_grp_leg_code => l_HR_Address_Rec.Style);
3905
3906 FETCH csr_TCA_Map INTO l_Address_Mapping;
3907 IF csr_TCA_Map%NOTFOUND THEN
3908 Hr_Utility.set_location('..Mapping rec. not found for : '||l_add_map,8);
3909 END IF;
3910 CLOSE csr_TCA_Map;
3911
3912 -- Get Column Lookup mapping record for context: PQP_HRTCA_TCAADD_[XX]_LOOKUP
3913 OPEN csr_TCA_Map
3914 (c_info_category => l_lookup_map
3915 ,c_bg_grp_id => l_HR_Address_Rec.business_group_id
3916 ,c_bg_grp_leg_code => l_HR_Address_Rec.Style);
3917
3918 FETCH csr_TCA_Map INTO l_LookUp_Mapping;
3919 IF csr_TCA_Map%NOTFOUND THEN
3920 Hr_Utility.set_location('..Mapping rec. not found for : '||l_lookup_map,8);
3921 END IF;
3922
3923 CLOSE csr_TCA_Map;
3924 -- HR: Address Line1
3925 Map_To_TCA_Address
3926 (p_column_name => l_Address_Mapping.pcv_information1
3927 ,p_column_value => l_HR_Address_Rec.Address_Line1
3928 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information1
3929 ,p_HZ_Location_Row => l_HZ_Location_Row);
3930
3931 -- HR: Address Line2
3932 Map_To_TCA_Address
3933 (p_column_name => l_Address_Mapping.pcv_information2
3934 ,p_column_value => l_HR_Address_Rec.Address_Line2
3935 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information2
3936 ,p_HZ_Location_Row => l_HZ_Location_Row);
3937
3938 -- HR: Address Line3
3939 Map_To_TCA_Address
3940 (p_column_name => l_Address_Mapping.pcv_information3
3941 ,p_column_value => l_HR_Address_Rec.Address_Line3
3942 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information3
3943 ,p_HZ_Location_Row => l_HZ_Location_Row);
3944
3945 -- HR: Region 1
3946 Map_To_TCA_Address
3947 (p_column_name => l_Address_Mapping.pcv_information4
3948 ,p_column_value => l_HR_Address_Rec.Region_1
3949 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information4
3950 ,p_HZ_Location_Row => l_HZ_Location_Row);
3951
3952 -- HR: Region 2
3953 Map_To_TCA_Address
3954 (p_column_name => l_Address_Mapping.pcv_information5
3955 ,p_column_value => l_HR_Address_Rec.Region_2
3956 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information5
3957 ,p_HZ_Location_Row => l_HZ_Location_Row);
3958
3959 -- HR: Region 3
3960 Map_To_TCA_Address
3961 (p_column_name => l_Address_Mapping.pcv_information6
3962 ,p_column_value => l_HR_Address_Rec.Region_3
3963 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information6
3964 ,p_HZ_Location_Row => l_HZ_Location_Row);
3965
3966 -- HR: Town Or City
3967 Map_To_TCA_Address
3968 (p_column_name => l_Address_Mapping.pcv_information7
3969 ,p_column_value => l_HR_Address_Rec.town_or_city
3970 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information7
3971 ,p_HZ_Location_Row => l_HZ_Location_Row);
3972
3973 -- HR: Postal Code
3974 Map_To_TCA_Address
3975 (p_column_name => l_Address_Mapping.pcv_information8
3976 ,p_column_value => l_HR_Address_Rec.postal_code
3977 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information8
3978 ,p_HZ_Location_Row => l_HZ_Location_Row);
3979
3980 -- HR: Country
3981 Map_To_TCA_Address
3982 (p_column_name => l_Address_Mapping.pcv_information9
3983 ,p_column_value => l_HR_Address_Rec.Country
3984 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information9
3985 ,p_HZ_Location_Row => l_HZ_Location_Row);
3986
3987 -- HR: Address Date From
3988 Map_To_TCA_Address
3989 (p_column_name => l_Address_Mapping.pcv_information10
3990 ,p_column_value => l_HR_Address_Rec.Date_From
3991 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information10
3992 ,p_HZ_Location_Row => l_HZ_Location_Row);
3993
3994 -- HR: Address Date To
3995 Map_To_TCA_Address
3996 (p_column_name => l_Address_Mapping.pcv_information11
3997 ,p_column_value => l_HR_Address_Rec.Date_to
3998 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information11
3999 ,p_HZ_Location_Row => l_HZ_Location_Row);
4000
4001 -- HR: Add Information13
4002 Map_To_TCA_Address
4003 (p_column_name => l_Address_Mapping.pcv_information12
4004 ,p_column_value => l_HR_Address_Rec.Add_Information13
4005 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information12
4006 ,p_HZ_Location_Row => l_HZ_Location_Row);
4007
4008 -- HR: Add Information14
4009 Map_To_TCA_Address
4010 (p_column_name => l_Address_Mapping.pcv_information13
4011 ,p_column_value => l_HR_Address_Rec.Add_Information14
4012 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information13
4013 ,p_HZ_Location_Row => l_HZ_Location_Row);
4014
4015 -- HR: Add Information15
4016 Map_To_TCA_Address
4017 (p_column_name => l_Address_Mapping.pcv_information14
4018 ,p_column_value => l_HR_Address_Rec.Add_Information15
4019 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information14
4020 ,p_HZ_Location_Row => l_HZ_Location_Row);
4021
4022 -- HR: Add Information16
4023 Map_To_TCA_Address
4024 (p_column_name => l_Address_Mapping.pcv_information15
4025 ,p_column_value => l_HR_Address_Rec.Add_Information16
4026 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information15
4027 ,p_HZ_Location_Row => l_HZ_Location_Row);
4028
4029 -- HR: Add Information17
4030 Map_To_TCA_Address
4031 (p_column_name => l_Address_Mapping.pcv_information16
4032 ,p_column_value => l_HR_Address_Rec.Add_Information17
4033 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information16
4034 ,p_HZ_Location_Row => l_HZ_Location_Row);
4035
4036 -- HR: Add Information18
4037 Map_To_TCA_Address
4038 (p_column_name => l_Address_Mapping.pcv_information17
4039 ,p_column_value => l_HR_Address_Rec.Add_Information18
4040 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information17
4041 ,p_HZ_Location_Row => l_HZ_Location_Row);
4042
4043 -- HR: Telephone Number 1
4044 Map_To_TCA_Address
4045 (p_column_name => l_Address_Mapping.pcv_information18
4046 ,p_column_value => l_HR_Address_Rec.Telephone_Number_1
4047 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information18
4048 ,p_HZ_Location_Row => l_HZ_Location_Row);
4049
4050 -- HR: Telephone Number 2
4051 Map_To_TCA_Address
4052 (p_column_name => l_Address_Mapping.pcv_information19
4053 ,p_column_value => l_HR_Address_Rec.Telephone_Number_2
4054 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information19
4055 ,p_HZ_Location_Row => l_HZ_Location_Row);
4056 -- HR: Telephone Number 3
4057 Map_To_TCA_Address
4058 (p_column_name => l_Address_Mapping.pcv_information20
4059 ,p_column_value => l_HR_Address_Rec.Telephone_Number_3
4060 ,p_col_lookup_type => l_LookUp_Mapping.pcv_information20
4061 ,p_HZ_Location_Row => l_HZ_Location_Row);
4062
4063 -- Call OSS API to update Student address in HZ
4064 Hr_Utility.set_location('..Calling OSS API : Igs_Pe_Person_Addr_Pkg.Update_Row',8);
4065 IF l_update_pri_add THEN
4066 PLSQL_Block :=
4067 'BEGIN ' ||
4068 'Igs_Pe_Person_Addr_Pkg.Update_Row ' ||
4069 '(p_action => :p_action ' ||
4070 ',p_party_type => :p_party_type ' ||
4071 ',p_party_id => :p_party_id ' ||
4072 ',p_status => :p_status ' ||
4073 ',p_start_dt => :p_Date_From ' ||
4074 ',p_end_dt => :p_Date_To ' ||
4075 ',p_country => :p_Country ' ||
4076 ',p_address_style => :p_hz_address_style ' ||
4077 ',p_addr_line_1 => :p_Address1 ' ||
4078 ',p_addr_line_2 => :p_Address2 ' ||
4079 ',p_addr_line_3 => :p_Address3 ' ||
4080 ',p_addr_line_4 => :p_Address4 ' ||
4081 ',p_date_last_verified => :p_effective_date ' ||
4082 ',p_correspondence => :p_correspondence ' ||
4083 ',p_city => :p_City ' ||
4084 ',p_state => :p_State ' ||
4085 ',p_province => :p_Province ' ||
4086 ',p_county => :p_County ' ||
4087 ',p_postal_code => :p_Postal_Code ' ||
4088 ',p_address_lines_phonetic => :p_Address_Lines_Phonetic ' ||
4089 ',p_delivery_point_code => :p_Delivery_Point_Code ' ||
4090 ',p_other_details_1 => :p_other_details_1 ' ||
4091 ',p_other_details_2 => :p_other_details_2 ' ||
4092 ',p_other_details_3 => :p_other_details_3 ' ||
4093 -- In Out
4094 ',p_party_site_id => :p_party_site_id ' ||
4095 ',p_last_update_date => :p_last_update_date ' ||
4096 ',p_party_site_ovn => :p_party_site_ovn ' ||
4097 ',p_location_ovn => :p_location_ovn ' ||
4098 ',p_rowid => :p_rowid ' ||
4099 ',p_location_id => :p_location_id ' ||
4100 -- Out
4101 ',l_return_status => :p_return_status ' ||
4102 ',l_msg_data => :p_msg_data ' ||
4103 '); ' ||
4104 'End;';
4105 EXECUTE IMMEDIATE PLSQL_Block
4106 Using p_action
4107 ,p_party_type
4108 ,p_party_Id
4109 ,p_status
4110 ,l_HR_Address_Rec.Date_From
4111 ,l_HR_Address_Rec.Date_To
4112 ,l_HZ_Location_Row.Country
4113 ,l_HZ_style
4114 ,l_HZ_Location_Row.Address1
4115 ,l_HZ_Location_Row.Address2
4116 ,l_HZ_Location_Row.Address3
4117 ,l_HZ_Location_Row.Address4
4118 ,p_effective_date
4119 ,'Y'
4120 ,l_HZ_Location_Row.City
4121 ,l_HZ_Location_Row.State
4122 ,l_HZ_Location_Row.Province
4123 ,l_HZ_Location_Row.County
4124 ,l_HZ_Location_Row.Postal_Code
4125 ,l_HZ_Location_Row.Address_Lines_Phonetic
4126 ,l_HZ_Location_Row.Delivery_Point_Code
4127 ,l_other_details_1
4128 ,l_other_details_2
4129 ,l_other_details_3
4130 -- In Out
4131 ,IN OUT p_party_site_id
4132 ,IN OUT p_last_update_date
4133 ,IN OUT p_party_site_ovn
4134 ,IN OUT p_location_ovn
4135 ,IN OUT p_rowid
4136 ,IN OUT p_location_id
4137 -- Out
4138 ,OUT p_return_status
4139 ,OUT p_msg_data;
4140 END IF;
4141 IF l_create_pri_add THEN
4142 Hr_Utility.set_location('..Call OSS Package : Igs_Pe_Person_Addr_Pkg.Insert_Row', 8);
4143 -- Call OSS API to create Student address in HZ
4144 PLSQL_Block :=
4145 'BEGIN '||
4146 'Igs_Pe_Person_Addr_Pkg.Insert_Row '||
4147 '(p_action => :p_action '||
4148 ',p_party_type => :p_party_type '||
4149 ',p_party_id => :p_party_Id '||
4150 ',p_status => :p_status '||
4151 ',p_start_dt => :p_date_from '||
4152 ',p_end_dt => :p_date_to '||
4153 ',p_country => :p_country '||
4154 ',p_address_style => :p_address_style '||
4155 ',p_addr_line_1 => :p_Address1 '||
4156 ',p_addr_line_2 => :p_Address2 '||
4157 ',p_addr_line_3 => :p_Address3 '||
4158 ',p_addr_line_4 => :p_Address4 '||
4159 ',p_date_last_verified => :p_effective_date '||
4160 ',p_correspondence => :p_correspondence '||
4161 ',p_city => :p_city '||
4162 ',p_state => :p_state '||
4163 ',p_province => :p_province '||
4164 ',p_county => :p_county '||
4165 ',p_postal_code => :p_postal_code '||
4166 ',p_address_lines_phonetic => :p_Address_Lines_Phonetic '||
4167 ',p_delivery_point_code => :p_Delivery_Point_Code '||
4168 ',p_other_details_1 => :p_other_details_1 '||
4169 ',p_other_details_2 => :p_other_details_2 '||
4170 ',p_other_details_3 => :p_other_details_3 '||
4171 -- In Out
4172 ',p_party_site_id => :p_party_site_id '||
4173 ',p_last_update_date => :p_last_update_date '||
4174 ',p_party_site_ovn => :p_party_site_ovn '||
4175 ',p_location_ovn => :p_location_ovn '||
4176 -- Out
4177 ',p_rowid => :p_rowid '||
4178 ',p_location_id => :p_location_id '||
4179 ',l_return_status => :p_return_status '||
4180 ',l_msg_data => :p_msg_data '||
4181 ' ); '||
4182 'END;';
4183
4184 EXECUTE IMMEDIATE PLSQL_Block
4185 Using p_action
4186 ,p_party_type
4187 ,p_party_Id
4188 ,p_status
4189 ,l_HR_Address_Rec.Date_From
4190 ,l_HR_Address_Rec.Date_To
4191 ,l_HZ_Location_Row.Country
4192 ,l_HZ_style
4193 ,l_HZ_Location_Row.Address1
4194 ,l_HZ_Location_Row.Address2
4195 ,l_HZ_Location_Row.Address3
4196 ,l_HZ_Location_Row.Address4
4197 ,p_effective_date
4198 ,'Y'
4199 ,l_HZ_Location_Row.City
4200 ,l_HZ_Location_Row.State
4201 ,l_HZ_Location_Row.Province
4202 ,l_HZ_Location_Row.County
4203 ,l_HZ_Location_Row.Postal_Code
4204 ,l_HZ_Location_Row.Address_Lines_Phonetic
4205 ,l_HZ_Location_Row.Delivery_Point_Code
4206 ,l_other_details_1
4207 ,l_other_details_2
4208 ,l_other_details_3
4209 -- In Out
4210 ,IN OUT p_party_site_id
4211 ,IN OUT p_last_update_date
4212 ,IN OUT p_party_site_ovn
4213 ,IN OUT p_location_ovn
4214 ,IN OUT p_rowid
4215 ,IN OUT p_location_id
4216 -- Out
4217 ,OUT p_return_status
4218 ,OUT p_msg_data;
4219 END IF;
4220 IF p_return_status IN ('E','U') THEN
4221 RAISE e_oss_add_failure;
4222 END IF;
4223 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
4224 EXCEPTION
4225 WHEN e_oss_add_failure THEN
4226 l_error_msg := Substrb(p_msg_data,1,2000);
4227 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
4228 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
4229 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4230 Hr_Utility.raise_error;
4231
4232 WHEN Others THEN
4233 l_error_msg := Substrb(SQLERRM,1,2000);
4234 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
4235 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
4236 Hr_Utility.set_location('Leaving: '||l_proc_name, 91);
4237 Hr_Utility.raise_error;
4238
4239 END Update_Address_HR_To_TCA;
4240
4241 END PQP_HRTCA_Integration;