[Home] [Help]
PACKAGE BODY: APPS.PQP_PENSION_PROVIDERS
Source
1 package body pqp_pension_providers as
2 /* $Header: pqppenpr.pkb 120.3 2005/06/28 00:29:36 sashriva noship $ */
3
4 g_package varchar(70) := ' PQP_Pension_Providers.';
5 --
6 -- ----------------------------------------------------------------------------
7 -- |---------------------< create_pension_provider >---------------------------|
8 -- ----------------------------------------------------------------------------
9 -- Pension Provider in NL HRMS is defined as a External Organization.
10 -- This procedure uses the core APIS,Packages to replicate the
11 -- Work Structures > Organization and Location forms.
12 -- It will create the location if the user has choosen to do so.
13
14 procedure create_pension_provider
15 (p_pension_provider_name in varchar2
16 ,p_business_group_id in number
17 ,p_legislation_code in varchar2
18 ,p_security_profile_id in number
19 ,p_effective_start_date in date
20 ,p_internal_external_flag in varchar2
21 ,p_type in varchar2
22 ,p_location_id in out nocopy number
23 ,p_create_new_location_flag in varchar2
24 ,p_org_information_context in varchar2
25 ,p_org_classification in varchar2
26 ,p_organization_id out nocopy number
27 ,p_org_information_id out nocopy number
28 ,p_location_code in varchar2
29 ,p_language_code in varchar2
30 ,p_country_code in varchar2
31 ,p_address_line_1 in varchar2 default null
32 ,p_address_line_2 in varchar2 default null
33 ,p_address_line_3 in varchar2 default null
34 ,p_postal_code in varchar2 default null
35 ,p_region_1 in varchar2 default null
36 ,p_region_2 in varchar2 default null
37 ,p_region_3 in varchar2 default null
38 ,p_style in varchar2 default null
39 ,p_tax_name in varchar2 default null
40 ,p_town_or_city in varchar2 default null
41 ,p_location_extra_info_id out nocopy number
42 ,p_information_type in varchar2 default null
43 ,p_lei_information_category in varchar2 default null
44 ,p_lei_information1 in varchar2 default null
45 ,p_lei_information2 in varchar2 default null
46 ,p_lei_information3 in varchar2 default null
47 ,p_lei_information4 in varchar2 default null
48 ,p_lei_information5 in varchar2 default null
49 ,p_lei_information6 in varchar2 default null
50 ,p_lei_information7 in varchar2 default null
51 ,p_lei_information8 in varchar2 default null
52 ,p_lei_information9 in varchar2 default null
53 ,p_lei_information10 in varchar2 default null
54 ,p_lei_information11 in varchar2 default null
55 ,p_lei_information12 in varchar2 default null
56 ,p_lei_information13 in varchar2 default null
57 ,p_lei_information14 in varchar2 default null
58 ,p_lei_information15 in varchar2 default null
59 ,p_lei_information16 in varchar2 default null
60 ,p_lei_information17 in varchar2 default null
61 ,p_lei_information18 in varchar2 default null
62 ,p_lei_information19 in varchar2 default null
63 ,p_lei_information20 in varchar2 default null
64 ,p_lei_information21 in varchar2 default null
65 ,p_lei_information22 in varchar2 default null
66 ,p_lei_information23 in varchar2 default null
67 ,p_lei_information24 in varchar2 default null
68 ,p_lei_information25 in varchar2 default null
69 ,p_lei_information26 in varchar2 default null
70 ,p_lei_information27 in varchar2 default null
71 ,p_lei_information28 in varchar2 default null
72 ,p_lei_information29 in varchar2 default null
73 ,p_lei_information30 in varchar2 default null
74
75 ) IS
76
77 -- Declare local variables
78
79 l_location_id number := NULL;
80 l_loc_ovn number;
81 l_lei_ovn number;
82 l_org_ovn number;
83 l_location_extra_info_id number;
84 l_organization_id number;
85 l_org_rowid rowid;
86 l_org_info_rowid rowid;
87 l_org_information_id number;
88
89 BEGIN
90
91 -- check to see if the organization name entered is unique
92
93 hr_organization.unique_name(
94 p_business_group_id => p_business_group_id,
95 p_organization_id => null,
96 p_organization_name => p_pension_provider_name);
97
98 -- Check if a new location is to be created.
99
100 IF p_create_new_location_flag = 'Y' AND p_location_id = -1 THEN
101
102 -- Create new Location with the information provided
103 hr_location_api.create_location(
104 p_validate => FALSE,
105 p_effective_date => p_effective_start_date,
106 p_language_code => p_language_code,
107 p_location_id => l_location_id,
108 p_object_version_number => l_loc_ovn,
109 p_tp_header_id => NULL,
110 p_ece_tp_location_code => NULL,
111 p_address_line_1 => p_address_line_1,
112 p_address_line_2 => p_address_line_2,
113 p_address_line_3 => p_address_line_3,
114 p_bill_to_site_flag => 'N',
115 p_country => p_country_code,
116 p_designated_receiver_id => NULL,
117 p_in_organization_flag => 'Y',
118 p_inactive_date => NULL,
119 p_operating_unit_id => NULL,
120 p_inventory_organization_id => NULL,
121 p_office_site_flag => 'N',
122 p_postal_code => p_postal_code,
123 p_receiving_site_flag => 'Y',
124 p_region_1 => p_region_1,
125 p_region_2 => p_region_2,
126 p_region_3 => p_region_3,
127 p_ship_to_location_id => NULL,
128 p_ship_to_site_flag => 'Y',
129 p_style => p_style,
130 p_tax_name => p_tax_name,
131 p_telephone_number_1 => NULL,
132 p_telephone_number_2 => NULL,
133 p_telephone_number_3 => NULL,
134 p_town_or_city => p_town_or_city,
135 p_loc_information13 => NULL,
136 p_loc_information14 => NULL,
137 p_loc_information15 => NULL,
138 p_loc_information16 => NULL,
139 p_loc_information17 => NULL,
140 p_loc_information18 => NULL,
141 p_loc_information19 => NULL,
142 p_loc_information20 => NULL,
143 p_attribute_category => NULL,
144 p_attribute1 => NULL,
145 p_attribute2 => NULL,
146 p_attribute3 => NULL,
147 p_attribute4 => NULL,
148 p_attribute5 => NULL,
149 p_attribute6 => NULL,
150 p_attribute7 => NULL,
151 p_attribute8 => NULL,
152 p_attribute9 => NULL,
153 p_attribute10 => NULL,
154 p_attribute11 => NULL,
155 p_attribute12 => NULL,
156 p_attribute13 => NULL,
157 p_attribute14 => NULL,
158 p_attribute15 => NULL,
159 p_attribute16 => NULL,
160 p_attribute17 => NULL,
161 p_attribute18 => NULL,
162 p_attribute19 => NULL,
163 p_attribute20 => NULL,
164 p_global_attribute_category => NULL,
165 p_global_attribute1 => NULL,
166 p_global_attribute2 => NULL,
167 p_global_attribute3 => NULL,
168 p_global_attribute4 => NULL,
169 p_global_attribute5 => NULL,
170 p_global_attribute6 => NULL,
171 p_global_attribute7 => NULL,
172 p_global_attribute8 => NULL,
173 p_global_attribute9 => NULL,
174 p_global_attribute10 => NULL,
175 p_global_attribute11 => NULL,
176 p_global_attribute12 => NULL,
177 p_global_attribute13 => NULL,
178 p_global_attribute14 => NULL,
179 p_global_attribute15 => NULL,
180 p_global_attribute16 => NULL,
181 p_global_attribute17 => NULL,
182 p_global_attribute18 => NULL,
183 p_global_attribute19 => NULL,
184 p_global_attribute20 => NULL,
185 p_business_group_id => NULL,
186 p_location_code => p_location_code,
187 p_description => p_location_code);
188
189 -- Create record in Extra Location information .
190 -- For NL the postal address is stored here .
191 hr_location_extra_info_api.create_location_extra_info
192 (p_validate => FALSE
193 ,p_location_id => l_location_id
194 ,p_information_type => 'NL_POSTAL_ADDRESS'
195 ,p_lei_attribute_category => NULL
196 ,p_lei_attribute1 => NULL
197 ,p_lei_attribute2 => NULL
198 ,p_lei_attribute3 => NULL
199 ,p_lei_attribute4 => NULL
200 ,p_lei_attribute5 => NULL
201 ,p_lei_attribute6 => NULL
202 ,p_lei_attribute7 => NULL
203 ,p_lei_attribute8 => NULL
204 ,p_lei_attribute9 => NULL
205 ,p_lei_attribute10 => NULL
206 ,p_lei_attribute11 => NULL
207 ,p_lei_attribute12 => NULL
208 ,p_lei_attribute13 => NULL
209 ,p_lei_attribute14 => NULL
210 ,p_lei_attribute15 => NULL
211 ,p_lei_attribute16 => NULL
212 ,p_lei_attribute17 => NULL
213 ,p_lei_attribute18 => NULL
214 ,p_lei_attribute19 => NULL
215 ,p_lei_attribute20 => NULL
216 ,p_lei_information_category => 'NL_POSTAL_ADDRESS'
217 ,p_lei_information1 => p_lei_information1
218 ,p_lei_information2 => p_lei_information2
219 ,p_lei_information3 => p_lei_information3
220 ,p_lei_information4 => p_lei_information4
221 ,p_lei_information5 => p_lei_information5
222 ,p_lei_information6 => p_lei_information6
223 ,p_lei_information7 => p_lei_information7
224 ,p_lei_information8 => p_lei_information8
225 ,p_lei_information9 => p_lei_information9
226 ,p_lei_information10 => p_lei_information10
227 ,p_lei_information11 => p_lei_information11
228 ,p_lei_information12 => p_lei_information12
229 ,p_lei_information13 => p_lei_information13
230 ,p_lei_information14 => p_lei_information14
231 ,p_lei_information15 => p_lei_information15
232 ,p_lei_information16 => p_lei_information16
233 ,p_lei_information17 => p_lei_information17
234 ,p_lei_information18 => p_lei_information18
235 ,p_lei_information19 => p_lei_information19
236 ,p_lei_information20 => p_lei_information20
237 ,p_lei_information21 => p_lei_information21
238 ,p_lei_information22 => p_lei_information22
239 ,p_lei_information23 => p_lei_information23
240 ,p_lei_information24 => p_lei_information24
241 ,p_lei_information25 => p_lei_information25
242 ,p_lei_information26 => p_lei_information26
243 ,p_lei_information27 => p_lei_information27
244 ,p_lei_information28 => p_lei_information28
245 ,p_lei_information29 => p_lei_information29
246 ,p_lei_information30 => p_lei_information30
247 ,p_location_extra_info_id => l_location_extra_info_id
248 ,p_object_version_number => l_lei_ovn
249 );
250
251 -- If the location_id is passed, use that location to create the org.
252
253 ELSIF p_create_new_location_flag = 'N' AND p_location_id <> -1 THEN
254
255 l_location_id := p_location_id;
256
257 END IF; -- Check to create a new location
258
259 -- Create the Organization with the location from above.
260 hr_organization_units_pkg.insert_row(
261 x_rowid => l_org_rowid,
262 x_organization_id => l_organization_id,
263 x_business_group_id => p_business_group_id,
264 x_cost_allocation_keyflex_id => NULL,
265 x_location_id => l_location_id,
266 x_soft_coding_keyflex_id => NULL,
267 x_date_from => p_effective_start_date,
268 x_name => p_pension_provider_name,
269 x_comments => p_pension_provider_name,
270 x_date_to => NULL,
271 x_internal_external_flag => p_internal_external_flag,
272 x_internal_address_line => NULL,
273 x_type => p_type,
274 x_security_profile_id => p_Security_Profile_Id,
275 x_view_all_orgs => 'Y',
276 x_attribute_category => NULL,
277 x_attribute1 => NULL,
278 x_attribute2 => NULL,
279 x_attribute3 => NULL,
280 x_attribute4 => NULL,
281 x_attribute5 => NULL,
282 x_attribute6 => NULL,
283 x_attribute7 => NULL,
284 x_attribute8 => NULL,
285 x_attribute9 => NULL,
286 x_attribute10 => NULL,
287 x_attribute11 => NULL,
288 x_attribute12 => NULL,
289 x_attribute13 => NULL,
290 x_attribute14 => NULL,
291 x_attribute15 => NULL,
292 x_attribute16 => NULL,
293 x_attribute17 => NULL,
294 x_attribute18 => NULL,
295 x_attribute19 => NULL,
296 x_attribute20 => NULL );
297
298 -- Add a Organization classification of Pension Provider to the Org
299 hr_org_information_pkg.insert_row(
300 x_rowid => l_org_info_rowid,
301 x_org_information_id => l_org_information_id,
302 x_org_information_context => p_org_information_context,
303 x_organization_id => l_organization_id,
304 x_org_information1 => p_org_classification,
305 x_org_information2 => 'Y',
306 x_org_information3 => NULL,
307 x_org_information4 => NULL,
308 x_org_information5 => NULL,
309 x_org_information6 => NULL,
310 x_org_information7 => NULL,
311 x_org_information8 => NULL,
312 x_org_information9 => NULL,
313 x_org_information10 => NULL,
314 x_org_information11 => NULL,
315 x_org_information12 => NULL,
316 x_org_information13 => NULL,
317 x_org_information14 => NULL,
318 x_org_information15 => NULL,
319 x_org_information16 => NULL,
320 x_org_information17 => NULL,
321 x_org_information18 => NULL,
322 x_org_information19 => NULL,
323 x_org_information20 => NULL,
324 x_attribute_category => NULL,
325 x_attribute1 => NULL,
326 x_attribute2 => NULL,
327 x_attribute3 => NULL,
328 x_attribute4 => NULL,
329 x_attribute5 => NULL,
330 x_attribute6 => NULL,
331 x_attribute7 => NULL,
332 x_attribute8 => NULL,
333 x_attribute9 => NULL,
334 x_attribute10 => NULL,
335 x_attribute11 => NULL,
336 x_attribute12 => NULL,
337 x_attribute13 => NULL,
338 x_attribute14 => NULL,
339 x_attribute15 => NULL,
340 x_attribute16 => NULL,
341 x_attribute17 => NULL,
342 x_attribute18 => NULL,
343 x_attribute19 => NULL,
344 x_attribute20 => NULL);
345
346 p_organization_id := l_organization_id;
347 p_location_id := l_location_id;
348 p_org_information_id := l_org_information_id;
349 p_location_extra_info_id := l_location_extra_info_id;
350
351 End Create_Pension_Provider;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------< update_pension_provider >--------------------------|
355 -- ----------------------------------------------------------------------------
356 --
357 Procedure update_pension_provider
358 (p_organization_id in number
359 ,p_business_group_id in number
360 ,p_provider_name in varchar2
361 ,p_location_id in number
362 ,p_date_from in date
363 ,p_date_to in date ) Is
364
365 Cursor csr_org Is
366 Select *
367 from hr_organization_units_v
368 where organization_id = p_organization_id;
369
370 l_proc varchar2(150) := g_package||'update_pension_provider';
371 l_org_rec csr_org%ROWTYPE;
372
373 Begin
374 hr_utility.set_location('Entering:'|| l_proc, 10);
375 Open csr_org;
376 Fetch csr_org Into l_org_rec;
377 If csr_org%NOTFOUND Then
378 Close csr_org;
379 fnd_message.set_name('PQP', 'PQP_230815_INVALID_ORG');
380 fnd_message.raise_error;
381 Else
382 Close csr_org;
383 End If;
384
385 -- check to see if the organization name entered is unique
386
387 hr_organization.unique_name(
388 p_business_group_id => p_business_group_id,
389 p_organization_id => p_organization_id,
390 p_organization_name => p_provider_name);
391
392 hr_organization_units_pkg.Update_Row
393 (x_Rowid => l_org_rec.row_id,
394 x_Organization_Id => l_org_rec.organization_id,
395 x_Business_Group_Id => l_org_rec.business_group_id,
396 x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
397 x_Location_Id => p_location_id,
398 x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
399 x_Date_From => p_date_from,
400 x_Name => p_provider_name,
401 x_Comments => l_org_rec.Comments,
402 x_Date_To => p_date_to,
403 x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
404 x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
405 x_Type => l_org_rec.Type,
406 x_Attribute_Category => l_org_rec.Attribute_Category,
407 x_Attribute1 => l_org_rec.Attribute1,
408 x_Attribute2 => l_org_rec.Attribute2,
409 x_Attribute3 => l_org_rec.Attribute3,
410 x_Attribute4 => l_org_rec.Attribute4,
411 x_Attribute5 => l_org_rec.Attribute5,
412 x_Attribute6 => l_org_rec.Attribute6,
413 x_Attribute7 => l_org_rec.Attribute7,
414 x_Attribute8 => l_org_rec.Attribute8,
415 x_Attribute9 => l_org_rec.Attribute9,
416 x_Attribute10 => l_org_rec.Attribute10,
417 x_Attribute11 => l_org_rec.Attribute11,
418 x_Attribute12 => l_org_rec.Attribute12,
419 x_Attribute13 => l_org_rec.Attribute13,
420 x_Attribute14 => l_org_rec.Attribute14,
421 x_Attribute15 => l_org_rec.Attribute15,
422 x_Attribute16 => l_org_rec.Attribute16,
423 x_Attribute17 => l_org_rec.Attribute17,
424 x_Attribute18 => l_org_rec.Attribute18,
425 x_Attribute19 => l_org_rec.Attribute19,
426 x_Attribute20 => l_org_rec.Attribute20
427 );
428 hr_utility.set_location('Leaving:'|| l_proc, 70);
429
430 End update_pension_provider;
431
432
433 -- ----------------------------------------------------------------------------
434 -- |-------------------< add_pension_types >----------------------------------|
435 -- ----------------------------------------------------------------------------
436
437 procedure add_pension_types
438 (p_organization_id in number
439 ,p_business_group_id in number
440 ,p_legislation_code in varchar2
441 ,p_org_information_context in varchar2
442 ,p_pension_type_id in number
443 ,p_org_information_id out nocopy number
444 ) IS
445
446 l_hoi_rowid rowid;
447 l_org_information_id number;
448
449 BEGIN
450
451 hr_org_information_pkg.insert_row
452 (x_rowid => l_hoi_rowid,
453 x_org_information_id => l_org_information_id,
454 x_org_information_context => p_org_information_context,
455 x_organization_id => p_organization_id,
456 x_org_information1 => to_char(p_pension_type_id),
457 x_org_information2 => NULL,
458 x_org_information3 => NULL,
459 x_org_information4 => NULL,
460 x_org_information5 => NULL,
461 x_org_information6 => NULL,
462 x_org_information7 => NULL,
463 x_org_information8 => NULL,
464 x_org_information9 => NULL,
465 x_org_information10 => NULL,
466 x_org_information11 => NULL,
467 x_org_information12 => NULL,
468 x_org_information13 => NULL,
469 x_org_information14 => NULL,
470 x_org_information15 => NULL,
471 x_org_information16 => NULL,
472 x_org_information17 => NULL,
473 x_org_information18 => NULL,
474 x_org_information19 => NULL,
475 x_org_information20 => NULL,
476 x_attribute_category => NULL,
477 x_attribute1 => NULL,
478 x_attribute2 => NULL,
479 x_attribute3 => NULL,
480 x_attribute4 => NULL,
481 x_attribute5 => NULL,
482 x_attribute6 => NULL,
483 x_attribute7 => NULL,
484 x_attribute8 => NULL,
485 x_attribute9 => NULL,
486 x_attribute10 => NULL,
487 x_attribute11 => NULL,
488 x_attribute12 => NULL,
489 x_attribute13 => NULL,
490 x_attribute14 => NULL,
491 x_attribute15 => NULL,
492 x_attribute16 => NULL,
493 x_attribute17 => NULL,
494 x_attribute18 => NULL,
495 x_attribute19 => NULL,
496 x_attribute20 => NULL
497 );
498
499 END add_pension_types;
500
501
502 -- ----------------------------------------------------------------------------
503 --|-------------------< add_provider_ref_number >------------------------------|
504 -- ----------------------------------------------------------------------------
505
506 procedure add_provider_ref_number
507 (p_organization_id in number
508 ,p_business_group_id in number
509 ,p_legislation_code in varchar2
510 ,p_org_information_context in varchar2
511 ,p_provider_reference_number in varchar2
512 ,p_org_information_id out nocopy number
513 ) IS
514
515 l_hoi_rowid rowid;
516 l_org_information_id number;
517
518 BEGIN
519
520 hr_org_information_pkg.insert_row
521 (x_rowid => l_hoi_rowid,
522 x_org_information_id => l_org_information_id,
523 x_org_information_context => p_org_information_context,
524 x_organization_id => p_organization_id,
525 x_org_information1 => p_provider_reference_number,
526 x_org_information2 => NULL,
527 x_org_information3 => NULL,
528 x_org_information4 => NULL,
529 x_org_information5 => NULL,
530 x_org_information6 => NULL,
531 x_org_information7 => NULL,
532 x_org_information8 => NULL,
533 x_org_information9 => NULL,
534 x_org_information10 => NULL,
535 x_org_information11 => NULL,
536 x_org_information12 => NULL,
537 x_org_information13 => NULL,
538 x_org_information14 => NULL,
539 x_org_information15 => NULL,
540 x_org_information16 => NULL,
541 x_org_information17 => NULL,
542 x_org_information18 => NULL,
543 x_org_information19 => NULL,
544 x_org_information20 => NULL,
545 x_attribute_category => NULL,
546 x_attribute1 => NULL,
547 x_attribute2 => NULL,
548 x_attribute3 => NULL,
549 x_attribute4 => NULL,
550 x_attribute5 => NULL,
551 x_attribute6 => NULL,
552 x_attribute7 => NULL,
553 x_attribute8 => NULL,
554 x_attribute9 => NULL,
555 x_attribute10 => NULL,
556 x_attribute11 => NULL,
557 x_attribute12 => NULL,
558 x_attribute13 => NULL,
559 x_attribute14 => NULL,
560 x_attribute15 => NULL,
561 x_attribute16 => NULL,
562 x_attribute17 => NULL,
563 x_attribute18 => NULL,
564 x_attribute19 => NULL,
565 x_attribute20 => NULL
566 );
567
568 END add_provider_ref_number;
569
570 -- ----------------------------------------------------------------------------
571 -- |-------------------< create_third_party_paymnt >--------------------------|
572 -- ----------------------------------------------------------------------------
573
574 procedure create_third_party_paymnt
575 (p_organization_id in number
576 ,p_org_information_id out nocopy number
577 ) IS
578
579 l_hoi_rowid rowid;
580 l_org_information_id number;
581
582 BEGIN
583
584 hr_org_information_pkg.insert_row
585 (x_rowid => l_hoi_rowid,
586 x_org_information_id => l_org_information_id,
587 x_org_information_context => 'CLASS',
588 x_organization_id => p_organization_id,
589 x_org_information1 => 'HR_PAYEE',
590 x_org_information2 => 'Y',
591 x_org_information3 => NULL,
592 x_org_information4 => NULL,
593 x_org_information5 => NULL,
594 x_org_information6 => NULL,
595 x_org_information7 => NULL,
596 x_org_information8 => NULL,
597 x_org_information9 => NULL,
598 x_org_information10 => NULL,
599 x_org_information11 => NULL,
600 x_org_information12 => NULL,
601 x_org_information13 => NULL,
602 x_org_information14 => NULL,
603 x_org_information15 => NULL,
604 x_org_information16 => NULL,
605 x_org_information17 => NULL,
606 x_org_information18 => NULL,
607 x_org_information19 => NULL,
608 x_org_information20 => NULL,
609 x_attribute_category => NULL,
610 x_attribute1 => NULL,
611 x_attribute2 => NULL,
612 x_attribute3 => NULL,
613 x_attribute4 => NULL,
614 x_attribute5 => NULL,
615 x_attribute6 => NULL,
616 x_attribute7 => NULL,
617 x_attribute8 => NULL,
618 x_attribute9 => NULL,
619 x_attribute10 => NULL,
620 x_attribute11 => NULL,
621 x_attribute12 => NULL,
622 x_attribute13 => NULL,
623 x_attribute14 => NULL,
624 x_attribute15 => NULL,
625 x_attribute16 => NULL,
626 x_attribute17 => NULL,
627 x_attribute18 => NULL,
628 x_attribute19 => NULL,
629 x_attribute20 => NULL
630 );
631
632 END create_third_party_paymnt;
633 --
634 -- ----------------------------------------------------------------------------
635 -- |-------------------< assign_pension_type_to_org >--------------------------|
636 -- ----------------------------------------------------------------------------
637 --
638 procedure assign_pension_type_to_org
639 ( p_transaction_mode in varchar2
640 ,p_organization_id in number
641 ,p_business_group_id in number
642 ,p_legislation_code in varchar2
643 ,p_pension_provider_id in number
644 ,p_pension_type_id in number
645 ,p_registration_number in varchar2
646 ,p_start_date date
647 ,p_end_date date
648 ,p_org_information_id in out nocopy number
649 ) Is
650
651 Cursor csr_pen_type Is
652 Select min(effective_start_date), max(effective_end_date)
653 from pqp_pension_types_f
654 where pension_type_id = p_pension_type_id;
655
656 Cursor csr_org_info Is
657 Select rowid
658 from hr_organization_information
659 where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
660 and org_information_id = p_org_information_id
661 and organization_id = p_organization_id;
662
663 Cursor csr_assigned_pen_types Is
664 Select pty.pension_type_name
665 from hr_organization_information hoi,
666 pqp_pension_types_f pty
667 where hoi.org_information_context = 'PQP_NL_ER_PENSION_TYPES'
668 and hoi.org_information2 = to_char(p_pension_type_id)
669 and hoi.organization_id = p_organization_id
670 and to_char(pty.pension_type_id) = hoi.org_information2
671 and to_char(p_pension_provider_id) = hoi.org_information1
672 and rownum = 1;
673
674 l_proc varchar2(150) := g_package||'assign_pension_type_to_org';
675 l_min_start_date date;
676 l_max_end_date date;
677 l_org_information_id number;
678 l_hoi_rowid rowid;
679 l_assigned_pen_type varchar2(150);
680
681 Begin
682 hr_utility.set_location('Entering:'|| l_proc, 10);
683 --
684 -- Check the start date and end date of the information
685 --
686 savepoint assign_pension_type_to_org;
687 Open csr_pen_type;
688 Fetch csr_pen_type Into l_min_start_date, l_max_end_date;
689 If csr_pen_type%Found Then
690 If p_start_date < l_min_start_date Or
691 p_end_date > l_max_end_date Then
692 -- Error : Start or End date can't be less than or greater
693 -- than the pension types Start or End date.
694 fnd_message.set_name('PQP', 'PQP_230816_INV_ST_ED_DATES');
695 fnd_message.raise_error;
696 End If;
697 Close csr_pen_type;
698 Else
699 -- Error : Invalid pension type id passed
700 fnd_message.set_name('PQP', 'PQP_230817_INVALID_PEN_TYPE');
701 fnd_message.raise_error;
702 Close csr_pen_type;
703 End If;
704 -- Insert the information into the hr_organization_information table
705 If p_transaction_mode = 'INSERT' Then
706 hr_utility.set_location(' INSERT Mode, Calling: hr_org_information_pkg.Insert_Row', 15);
707
708 open csr_assigned_pen_types;
709 Fetch csr_assigned_pen_types Into l_assigned_pen_type;
710 If csr_assigned_pen_types%Found Then
711 --Error : The pension type is already assigned to this org
712 fnd_message.set_name('PQP','PQP_230870_PTYPE_ALREADY_ASSIG');
713 fnd_message.set_token('PTYPE_NAME',l_assigned_pen_type);
714 fnd_message.raise_error;
715 Close csr_assigned_pen_types;
716 End If;
717
718 hr_org_information_pkg.insert_row
719 (x_rowid => l_hoi_rowid,
720 x_org_information_id => l_org_information_id,
721 x_org_information_context => 'PQP_NL_ER_PENSION_TYPES',
722 x_organization_id => p_organization_id,
723 x_org_information1 => to_char(p_pension_provider_id), -- Pension Provider
724 x_org_information2 => to_char(p_pension_type_id), -- Pension Type
725 x_org_information3 => p_registration_number, -- Registration Number
726 x_org_information4 => fnd_date.date_to_canonical(p_start_date),-- Start Date
727 x_org_information5 => fnd_date.date_to_canonical(p_end_date), -- End Date
728 x_org_information6 => NULL,
729 x_org_information7 => NULL,
730 x_org_information8 => NULL,
731 x_org_information9 => NULL,
732 x_org_information10 => NULL,
733 x_org_information11 => NULL,
734 x_org_information12 => NULL,
735 x_org_information13 => NULL,
736 x_org_information14 => NULL,
737 x_org_information15 => NULL,
738 x_org_information16 => NULL,
739 x_org_information17 => NULL,
740 x_org_information18 => NULL,
741 x_org_information19 => NULL,
742 x_org_information20 => NULL,
743 x_attribute_category => NULL,
744 x_attribute1 => NULL,
745 x_attribute2 => NULL,
746 x_attribute3 => NULL,
747 x_attribute4 => NULL,
748 x_attribute5 => NULL,
749 x_attribute6 => NULL,
750 x_attribute7 => NULL,
751 x_attribute8 => NULL,
752 x_attribute9 => NULL,
753 x_attribute10 => NULL,
754 x_attribute11 => NULL,
755 x_attribute12 => NULL,
756 x_attribute13 => NULL,
757 x_attribute14 => NULL,
758 x_attribute15 => NULL,
759 x_attribute16 => NULL,
760 x_attribute17 => NULL,
761 x_attribute18 => NULL,
762 x_attribute19 => NULL,
763 x_attribute20 => NULL
764 );
765 p_org_information_id := l_org_information_id;
766 ElsIf p_transaction_mode = 'UPDATE' Then
767
768 hr_utility.set_location(' UPDATE Mode, Calling: hr_org_information_pkg.Update_Row', 25);
769 --
770 -- Get the rowid for the org_information_id passed
771 --
772 Open csr_org_info;
773 Fetch csr_org_info Into l_hoi_rowid;
774 If csr_org_info%NOTFOUND Then
775 --
776 -- Invalid org_information_id, organization_id or combination
777 -- does not exists for PQP_NL_ER_PENSION_TYPES context
778 --
779 Close csr_org_info;
780 fnd_message.set_name('PQP', 'PQP_230815_INVALID_ORG');
781 fnd_message.raise_error;
782 Else
783 Close csr_org_info;
784 End If;
785 -- Update the information in the hr_organization_information table
786 hr_org_information_pkg.Update_Row
787 (X_Rowid => l_hoi_rowid,
788 X_Org_Information_Id => p_org_information_id,
789 x_org_information_context => 'PQP_NL_ER_PENSION_TYPES',
790 x_organization_id => p_organization_id,
791 x_org_information1 => to_char(p_pension_provider_id), -- Pension Provider
792 x_org_information2 => to_char(p_pension_type_id), -- Pension Type
793 x_org_information3 => p_registration_number, -- Registration Number
794 x_org_information4 => fnd_date.date_to_canonical(p_start_date),-- Start Date
795 x_org_information5 => fnd_date.date_to_canonical(p_end_date), -- End Date
796 x_org_information6 => NULL,
797 x_org_information7 => NULL,
798 x_org_information8 => NULL,
799 x_org_information9 => NULL,
800 x_org_information10 => NULL,
801 x_org_information11 => NULL,
802 x_org_information12 => NULL,
803 x_org_information13 => NULL,
804 x_org_information14 => NULL,
805 x_org_information15 => NULL,
806 x_org_information16 => NULL,
807 x_org_information17 => NULL,
808 x_org_information18 => NULL,
809 x_org_information19 => NULL,
810 x_org_information20 => NULL,
811 x_attribute_category => NULL,
812 x_attribute1 => NULL,
813 x_attribute2 => NULL,
814 x_attribute3 => NULL,
815 x_attribute4 => NULL,
816 x_attribute5 => NULL,
817 x_attribute6 => NULL,
818 x_attribute7 => NULL,
819 x_attribute8 => NULL,
820 x_attribute9 => NULL,
821 x_attribute10 => NULL,
822 x_attribute11 => NULL,
823 x_attribute12 => NULL,
824 x_attribute13 => NULL,
825 x_attribute14 => NULL,
826 x_attribute15 => NULL,
827 x_attribute16 => NULL,
828 x_attribute17 => NULL,
829 x_attribute18 => NULL,
830 x_attribute19 => NULL,
831 x_attribute20 => NULL
832 );
833 End If;
834 hr_utility.set_location(' Leaving:'|| l_proc, 60);
835 Exception
836 When Others Then
837 Rollback to assign_pension_type_to_org;
838 Raise;
839 hr_utility.set_location(' Leaving:'|| l_proc, 80);
840
841 End assign_pension_type_to_org;
842 --
843 -- ----------------------------------------------------------------------------
844 -- |-------------------< delete_pension_types >--------------------------------|
845 -- ----------------------------------------------------------------------------
846 --
847 Procedure delete_pension_types
848 (p_organization_id in number
849 ,p_business_group_id in number
850 ,p_legislation_code in varchar2
851 ,p_pension_type_id in number
852 ,p_org_information_id in number
853 ) IS
854
855 Cursor csr_org_info(c_org_info_context in varchar2) Is
856 Select rowid
857 from hr_organization_information
858 where org_information_context = c_org_info_context
859 and org_information_id = p_org_information_id
860 and organization_id = p_organization_id;
861
862 Cursor c_chk_asg_employer IS
863 select 'x'
864 from hr_organization_information
865 where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
866 -- ORG_INFORMATION2 is the pension_type_id from NL
867 and ORG_INFORMATION2 = to_char(p_pension_type_id)
868 and ORG_INFORMATION1 = to_char(p_organization_id);
869
870 Cursor c_is_pentype_attached_nl Is
871 Select 1
872 from pay_element_type_extra_info eei
873 where ((eei.information_type = 'PQP_NL_PRE_TAX_DEDUCTIONS'
874 and eei.eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS')
875 or (eei.information_type = 'PQP_NL_SAVINGS_DEDUCTIONS'
876 and eei.eei_information_category = 'PQP_NL_SAVINGS_DEDUCTIONS')
877 or (eei.information_type = 'PQP_NL_ABP_DEDUCTION'
878 and eei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'))
879 and eei.eei_information2 = p_pension_type_id
880 and eei.eei_information3 = p_organization_id;
881
882 Cursor c_is_pentype_attached_gb Is
883 Select 1
884 from pay_element_type_extra_info eei
885 where eei.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
886 and eei.eei_information_category = 'PQP_GB_PENSION_SCHEME_INFO'
887 and eei.eei_information3 = p_pension_type_id
888 and eei.eei_information2 = p_organization_id;
889
890 Cursor c_is_pentype_attached_hu Is
891 Select 1
892 from pay_element_type_extra_info eei
893 where eei.information_type = 'HU_PENSION_SCHEME_INFO'
894 and eei.eei_information_category = 'HU_PENSION_SCHEME_INFO'
895 and eei.eei_information3 = p_pension_type_id
896 and eei.eei_information2 = p_organization_id;
897
898 Cursor c_is_pentype_attached_ie Is
899 Select 1
900 from pay_element_type_extra_info eei
901 where eei.information_type = 'IE_PENSION_SCHEME_INFO'
902 and eei.eei_information_category = 'IE_PENSION_SCHEME_INFO'
903 and eei.eei_information1 = p_pension_type_id
904 and eei.eei_information2 = p_organization_id;
905
906 l_org_info_rowid rowid;
907 l_dummy varchar2(1);
908 l_org_info_context varchar2(30) default null;
909
910 Begin
911 if p_legislation_code = 'GB' THEN
912 l_org_info_context := 'PQP_GB_PENSION_TYPES_INFO';
913 elsif p_legislation_code = 'NL' THEN
914 l_org_info_context := 'PQP_NL_PENSION_TYPES';
915 elsif p_legislation_code = 'HU' THEN
916 l_org_info_context := 'HU_PENSION_TYPES_INFO';
917 elsif p_legislation_code = 'IE' THEN
918 l_org_info_context := 'IE_PENSION_TYPES_INFO';
919 end if;
920 OPEN csr_org_info(l_org_info_context);
921 FETCH csr_org_info INTO l_org_info_rowid;
922 IF csr_org_info%FOUND THEN
923 -- Before deleting the pension type or removing
924 -- it from the pension provider, check that the pension
925 -- type is not assigned to any employer or a scheme. If it is then
926 -- do not let the user delete the pension type from the
927 -- provider.
928 close csr_org_info;
929 -- chk if the pension type is attached for NL schemes
930 IF p_legislation_code = 'NL' THEN
931
932 OPEN c_chk_asg_employer;
933 FETCH c_chk_asg_employer INTO l_dummy;
934 IF c_chk_asg_employer%FOUND THEN
935 -- Error Pension type is assigned to an employer.
936 close c_chk_asg_employer;
937 fnd_message.set_name('PQP', 'PQP_230867_PTYPE_ASSIGN_TO_EMP');
938 fnd_message.raise_error;
939 ELSE
940 -- It is not assigned so it can be deleted.
941 close c_chk_asg_employer;
942 OPEN c_is_pentype_attached_nl;
943 FETCH c_is_pentype_attached_nl into l_dummy;
944 IF c_is_pentype_attached_nl%FOUND THEN
945 -- error the pension type cannot be removed since its used in a scheme
946 close c_is_pentype_attached_nl;
947 fnd_message.set_name('PQP', 'PQP_230960_PTYPE_ATTACH_SCHM');
948 fnd_message.raise_error;
949 ELSE
950 close c_is_pentype_attached_nl;
951 hr_org_information_pkg.delete_row(l_org_info_rowid);
952 END IF; -- if c_is_pentype_attached_nl
953 END IF; -- if c_chk_asg_employer
954 ELSIF p_legislation_code = 'GB' THEN
955 -- if the legislation code is GB
956 OPEN c_is_pentype_attached_gb;
957 FETCH c_is_pentype_attached_gb INTO l_dummy;
958 IF c_is_pentype_attached_gb%FOUND THEN
959 -- Error Pension type is attached to a scheme.
960 close c_is_pentype_attached_gb;
961 fnd_message.set_name('PQP', 'PQP_230960_PTYPE_ATTACH_SCHM');
962 fnd_message.raise_error;
963 ELSE
964 -- It is not assigned so it can be deleted.
965 close c_is_pentype_attached_gb;
966 hr_org_information_pkg.delete_row(l_org_info_rowid);
967 END IF;
968 ELSIF p_legislation_code = 'HU' THEN
969 -- if the legislation code is HU
970 OPEN c_is_pentype_attached_hu;
971 FETCH c_is_pentype_attached_hu INTO l_dummy;
972 IF c_is_pentype_attached_hu%FOUND THEN
973 -- Error Pension type is attached to a scheme.
974 close c_is_pentype_attached_hu;
975 fnd_message.set_name('PQP', 'PQP_230960_PTYPE_ATTACH_SCHM');
976 fnd_message.raise_error;
977 ELSE
978 -- It is not assigned so it can be deleted.
979 close c_is_pentype_attached_hu;
980 hr_org_information_pkg.delete_row(l_org_info_rowid);
981 END IF;
982 ELSIF p_legislation_code = 'IE' THEN
983 -- if the legislation code is IE
984 OPEN c_is_pentype_attached_ie;
985 FETCH c_is_pentype_attached_ie INTO l_dummy;
986 IF c_is_pentype_attached_ie%FOUND THEN
987 -- Error Pension type is attached to a scheme.
988 close c_is_pentype_attached_ie;
989 fnd_message.set_name('PQP', 'PQP_230960_PTYPE_ATTACH_SCHM');
990 fnd_message.raise_error;
991 ELSE
992 -- It is not assigned so it can be deleted.
993 close c_is_pentype_attached_ie;
994 hr_org_information_pkg.delete_row(l_org_info_rowid);
995 END IF;
996 END IF;
997 ELSE
998 close csr_org_info;
999 END IF;
1000
1001 End Delete_Pension_Types;
1002
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |-------------------< delete_third_party_paymnt >---------------------------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 Procedure delete_third_party_paymnt
1009 (p_organization_id in number
1010 ) IS
1011 -- cursor to select the rowid for the org information row
1012 Cursor csr_org_info Is
1013 Select rowid
1014 from hr_organization_information
1015 where org_information_context = 'CLASS'
1016 and org_information1 = 'HR_PAYEE'
1017 and org_information2 = 'Y'
1018 and organization_id = p_organization_id;
1019
1020 l_org_info_rowid rowid;
1021
1022 Begin
1023
1024 OPEN csr_org_info;
1025 FETCH csr_org_info INTO l_org_info_rowid;
1026 -- if a row has been found specifying third party payment
1027 -- then delete it from hr_org_information table
1028 IF csr_org_info%FOUND THEN
1029 close csr_org_info;
1030 hr_org_information_pkg.delete_row(l_org_info_rowid);
1031 ELSE
1032 close csr_org_info;
1033 END IF;
1034
1035 End Delete_third_party_paymnt;
1036
1037 --
1038 -- ----------------------------------------------------------------------------
1039 -- |---------------------< delete_pension_provider_nl>-------------------------|
1040 -- ----------------------------------------------------------------------------
1041 --
1042
1043
1044 Procedure delete_pension_provider_nl
1045 (p_organization_id in number)
1046 Is
1047
1048 Cursor csr_org_info Is
1049 Select rowid,org_information_id,org_information_context,org_information1
1050 from hr_organization_information
1051 where org_information_context = 'CLASS'
1052 and org_information1 = 'FR_PENSION'
1053 and organization_id = p_organization_id;
1054
1055
1056 Cursor csr_is_provider_assigned Is
1057 Select rowid
1058 from hr_organization_information
1059 where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
1060 and org_information1 = p_organization_id;
1061
1062 Cursor csr_pen_types_org_info Is
1063 Select rowid
1064 from hr_organization_information
1065 where org_information_context = 'PQP_NL_PENSION_TYPES'
1066 and organization_id = p_organization_id;
1067
1068 Cursor csr_is_provider_attached Is
1069 Select 1
1070 from pay_element_type_extra_info eei
1071 where ((eei.information_type = 'PQP_NL_PRE_TAX_DEDUCTIONS'
1072 and eei.eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS')
1073 or (eei.information_type = 'PQP_NL_SAVINGS_DEDUCTIONS'
1074 and eei.eei_information_category = 'PQP_NL_SAVINGS_DEDUCTIONS')
1075 or (eei.information_type = 'PQP_NL_ABP_DEDUCTION'
1076 and eei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'))
1077 and eei.eei_information3 = p_organization_id;
1078
1079
1080 l_org_info_rowid rowid;
1081 l_org_information_id number;
1082 l_org_information_context varchar2(40);
1083 l_org_classification varchar2(150);
1084 l_assigned_provider varchar2(150);
1085
1086
1087 BEGIN
1088
1089 hr_utility.set_location(' Leaving:' ,20);
1090 open csr_is_provider_assigned;
1091 Fetch csr_is_provider_assigned Into l_assigned_provider;
1092 If csr_is_provider_assigned%Found Then
1093 Close csr_is_provider_assigned;
1094 --Error : The provider is assigned to an employer
1095 fnd_message.set_name('PQP','PQP_230868_PRVDR_ASSIGN_TO_EMP');
1096 fnd_message.raise_error;
1097
1098 Else
1099 Close csr_is_provider_assigned;
1100
1101 End If;
1102
1103 open csr_is_provider_attached;
1104 Fetch csr_is_provider_attached Into l_assigned_provider;
1105 If csr_is_provider_attached%Found Then
1106 Close csr_is_provider_attached;
1107 --Error : The provider is attached to a scheme
1108 fnd_message.set_name('PQP','PQP_230959_PRVDR_ATTACH_SCHM');
1109 fnd_message.raise_error;
1110
1111 Else
1112 Close csr_is_provider_attached;
1113
1114 End If;
1115
1116 -- remove all the org_information rows containing the pension types
1117 --attached to this provider
1118 Open csr_pen_types_org_info;
1119
1120 loop
1121 fetch csr_pen_types_org_info into l_org_info_rowid;
1122 exit when csr_pen_types_org_info%NOTFOUND;
1123 -- delete the org info row containing this pension type
1124 hr_org_information_pkg.delete_row(l_org_info_rowid);
1125 end loop;
1126
1127 close csr_pen_types_org_info;
1128
1129 open csr_org_info;
1130 fetch csr_org_info into l_org_info_rowid,l_org_information_id,
1131 l_org_information_context,l_org_classification;
1132 close csr_org_info;
1133
1134 hr_utility.set_location(' Leaving:' ,40);
1135 -- Disable the Organization classification of Pension Provider of the Org
1136 hr_org_information_pkg.update_row(
1137 x_rowid => l_org_info_rowid,
1138 x_org_information_id => l_org_information_id,
1139 x_org_information_context => l_org_information_context,
1140 x_organization_id => p_organization_id,
1141 x_org_information1 => l_org_classification,
1142 x_org_information2 => 'N',
1143 x_org_information3 => NULL,
1144 x_org_information4 => NULL,
1145 x_org_information5 => NULL,
1146 x_org_information6 => NULL,
1147 x_org_information7 => NULL,
1148 x_org_information8 => NULL,
1149 x_org_information9 => NULL,
1150 x_org_information10 => NULL,
1151 x_org_information11 => NULL,
1152 x_org_information12 => NULL,
1153 x_org_information13 => NULL,
1154 x_org_information14 => NULL,
1155 x_org_information15 => NULL,
1156 x_org_information16 => NULL,
1157 x_org_information17 => NULL,
1158 x_org_information18 => NULL,
1159 x_org_information19 => NULL,
1160 x_org_information20 => NULL,
1161 x_attribute_category => NULL,
1162 x_attribute1 => NULL,
1163 x_attribute2 => NULL,
1164 x_attribute3 => NULL,
1165 x_attribute4 => NULL,
1166 x_attribute5 => NULL,
1167 x_attribute6 => NULL,
1168 x_attribute7 => NULL,
1169 x_attribute8 => NULL,
1170 x_attribute9 => NULL,
1171 x_attribute10 => NULL,
1172 x_attribute11 => NULL,
1173 x_attribute12 => NULL,
1174 x_attribute13 => NULL,
1175 x_attribute14 => NULL,
1176 x_attribute15 => NULL,
1177 x_attribute16 => NULL,
1178 x_attribute17 => NULL,
1179 x_attribute18 => NULL,
1180 x_attribute19 => NULL,
1181 x_attribute20 => NULL);
1182 hr_utility.set_location(' Leaving:' ,50);
1183
1184 End delete_pension_provider_nl;
1185
1186 --
1187 -- ----------------------------------------------------------------------------
1188 -- |---------------------< delete_pension_provider_gb>-------------------------|
1189 -- ----------------------------------------------------------------------------
1190 --
1191
1192
1193 Procedure delete_pension_provider_gb
1194 (p_organization_id in number
1195 ,p_effective_date in date)
1196 Is
1197
1198
1199 Cursor csr_org Is
1200 Select *
1201 from hr_organization_units_v
1202 where organization_id = p_organization_id;
1203
1204 Cursor csr_org_info Is
1205 Select rowid,org_information_id,org_information_context,org_information1
1206 from hr_organization_information
1207 where org_information_context = 'CLASS'
1208 and org_information1 = 'FR_PENSION'
1209 and organization_id = p_organization_id;
1210
1211 Cursor csr_third_party_org_info Is
1212 Select rowid,org_information_id,org_information_context,org_information1
1213 from hr_organization_information
1214 where org_information_context = 'CLASS'
1215 and org_information1 = 'HR_PAYEE'
1216 and organization_id = p_organization_id;
1217
1218 Cursor csr_pen_types_org_info Is
1219 Select rowid
1220 from hr_organization_information
1221 where org_information_context = 'PQP_GB_PENSION_TYPES_INFO'
1222 and organization_id = p_organization_id;
1223
1224 Cursor csr_is_provider_assigned Is
1225 Select 1
1226 from pay_element_type_extra_info eei
1227 where eei.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
1228 and eei.eei_information_category = 'PQP_GB_PENSION_SCHEME_INFO'
1229 and eei.eei_information2 = p_organization_id;
1230
1231 l_org_info_rowid rowid;
1232 l_org_information_id number;
1233 l_org_information_context varchar2(40);
1234 l_org_classification varchar2(150);
1235 l_assigned_provider number;
1236 l_org_rec csr_org%ROWTYPE;
1237
1238
1239 BEGIN
1240 hr_utility.set_location(' Leaving:' ,20);
1241 open csr_is_provider_assigned;
1242 Fetch csr_is_provider_assigned Into l_assigned_provider;
1243 If csr_is_provider_assigned%Found Then
1244 Close csr_is_provider_assigned;
1245 --Error : The provider is assigned to an employer
1246 fnd_message.set_name('PQP','PQP_230959_PRVDR_ATTACH_SCHM');
1247 fnd_message.raise_error;
1248
1249 Else
1250 Close csr_is_provider_assigned;
1251
1252 End If;
1253
1254 open csr_org_info;
1255 fetch csr_org_info into l_org_info_rowid,l_org_information_id,
1256 l_org_information_context,l_org_classification;
1257 close csr_org_info;
1258 hr_utility.set_location(' Leaving:' ,40);
1259 -- Disable the Organization classification of Pension Provider of the Org
1260 hr_org_information_pkg.update_row(
1261 x_rowid => l_org_info_rowid,
1262 x_org_information_id => l_org_information_id,
1263 x_org_information_context => l_org_information_context,
1264 x_organization_id => p_organization_id,
1265 x_org_information1 => l_org_classification,
1266 x_org_information2 => 'N',
1267 x_org_information3 => NULL,
1268 x_org_information4 => NULL,
1269 x_org_information5 => NULL,
1270 x_org_information6 => NULL,
1271 x_org_information7 => NULL,
1272 x_org_information8 => NULL,
1273 x_org_information9 => NULL,
1274 x_org_information10 => NULL,
1275 x_org_information11 => NULL,
1276 x_org_information12 => NULL,
1277 x_org_information13 => NULL,
1278 x_org_information14 => NULL,
1279 x_org_information15 => NULL,
1280 x_org_information16 => NULL,
1281 x_org_information17 => NULL,
1282 x_org_information18 => NULL,
1283 x_org_information19 => NULL,
1284 x_org_information20 => NULL,
1285 x_attribute_category => NULL,
1286 x_attribute1 => NULL,
1287 x_attribute2 => NULL,
1288 x_attribute3 => NULL,
1289 x_attribute4 => NULL,
1290 x_attribute5 => NULL,
1291 x_attribute6 => NULL,
1292 x_attribute7 => NULL,
1293 x_attribute8 => NULL,
1294 x_attribute9 => NULL,
1295 x_attribute10 => NULL,
1296 x_attribute11 => NULL,
1297 x_attribute12 => NULL,
1298 x_attribute13 => NULL,
1299 x_attribute14 => NULL,
1300 x_attribute15 => NULL,
1301 x_attribute16 => NULL,
1302 x_attribute17 => NULL,
1303 x_attribute18 => NULL,
1304 x_attribute19 => NULL,
1305 x_attribute20 => NULL);
1306 hr_utility.set_location(' Leaving:' ,50);
1307 -- remove all the org_information rows containing the pension types
1308 --attached to this provider
1309 Open csr_pen_types_org_info;
1310
1311 loop
1312 fetch csr_pen_types_org_info into l_org_info_rowid;
1313 exit when csr_pen_types_org_info%NOTFOUND;
1314 -- delete the org info row containing this pension type
1315 hr_org_information_pkg.delete_row(l_org_info_rowid);
1316 end loop;
1317
1318 close csr_pen_types_org_info;
1319
1320
1321 -- if a third party payment classification exists, disable that too
1322 open csr_third_party_org_info;
1323 fetch csr_third_party_org_info into l_org_info_rowid,l_org_information_id,
1324 l_org_information_context,l_org_classification;
1325 hr_utility.set_location(' Leaving:' ,60);
1326 if csr_third_party_org_info%Found Then
1327 -- Disable the Third Party Payment Organization classification
1328 -- of Pension Provider
1329 close csr_third_party_org_info;
1330 hr_org_information_pkg.update_row(
1331 x_rowid => l_org_info_rowid,
1332 x_org_information_id => l_org_information_id,
1333 x_org_information_context => l_org_information_context,
1334 x_organization_id => p_organization_id,
1335 x_org_information1 => l_org_classification,
1336 x_org_information2 => 'N',
1337 x_org_information3 => NULL,
1338 x_org_information4 => NULL,
1339 x_org_information5 => NULL,
1340 x_org_information6 => NULL,
1341 x_org_information7 => NULL,
1342 x_org_information8 => NULL,
1343 x_org_information9 => NULL,
1344 x_org_information10 => NULL,
1345 x_org_information11 => NULL,
1346 x_org_information12 => NULL,
1347 x_org_information13 => NULL,
1348 x_org_information14 => NULL,
1349 x_org_information15 => NULL,
1350 x_org_information16 => NULL,
1351 x_org_information17 => NULL,
1352 x_org_information18 => NULL,
1353 x_org_information19 => NULL,
1354 x_org_information20 => NULL,
1355 x_attribute_category => NULL,
1356 x_attribute1 => NULL,
1357 x_attribute2 => NULL,
1358 x_attribute3 => NULL,
1359 x_attribute4 => NULL,
1360 x_attribute5 => NULL,
1361 x_attribute6 => NULL,
1362 x_attribute7 => NULL,
1363 x_attribute8 => NULL,
1364 x_attribute9 => NULL,
1365 x_attribute10 => NULL,
1366 x_attribute11 => NULL,
1367 x_attribute12 => NULL,
1368 x_attribute13 => NULL,
1369 x_attribute14 => NULL,
1370 x_attribute15 => NULL,
1371 x_attribute16 => NULL,
1372 x_attribute17 => NULL,
1373 x_attribute18 => NULL,
1374 x_attribute19 => NULL,
1375 x_attribute20 => NULL);
1376
1377 else
1378 close csr_third_party_org_info;
1379 End If;
1380 hr_utility.set_location(' Leaving:' ,70);
1381
1382 -- end-date the pension provider organization with the current effective date
1383 Open csr_org;
1384 Fetch csr_org into l_org_rec;
1385 If csr_org%NOTFOUND Then
1386 Close csr_org;
1387 fnd_message.set_name('PQP', 'PQP_230815_INVALID_ORG');
1388 fnd_message.raise_error;
1389 Else
1390 Close csr_org;
1391 End If;
1392 hr_organization_units_pkg.Update_Row
1393 (x_Rowid => l_org_rec.row_id,
1394 x_Organization_Id => l_org_rec.organization_id,
1395 x_Business_Group_Id => l_org_rec.business_group_id,
1396 x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
1397 x_Location_Id => l_org_rec.Location_Id,
1398 x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
1399 x_Date_From => l_org_rec.Date_From,
1400 x_Name => l_org_rec.Name,
1401 x_Comments => l_org_rec.Comments,
1402 x_Date_To => p_effective_date,
1403 x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
1404 x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
1405 x_Type => l_org_rec.Type,
1406 x_Attribute_Category => l_org_rec.Attribute_Category,
1407 x_Attribute1 => l_org_rec.Attribute1,
1408 x_Attribute2 => l_org_rec.Attribute2,
1409 x_Attribute3 => l_org_rec.Attribute3,
1410 x_Attribute4 => l_org_rec.Attribute4,
1411 x_Attribute5 => l_org_rec.Attribute5,
1412 x_Attribute6 => l_org_rec.Attribute6,
1413 x_Attribute7 => l_org_rec.Attribute7,
1414 x_Attribute8 => l_org_rec.Attribute8,
1415 x_Attribute9 => l_org_rec.Attribute9,
1416 x_Attribute10 => l_org_rec.Attribute10,
1417 x_Attribute11 => l_org_rec.Attribute11,
1418 x_Attribute12 => l_org_rec.Attribute12,
1419 x_Attribute13 => l_org_rec.Attribute13,
1420 x_Attribute14 => l_org_rec.Attribute14,
1421 x_Attribute15 => l_org_rec.Attribute15,
1422 x_Attribute16 => l_org_rec.Attribute16,
1423 x_Attribute17 => l_org_rec.Attribute17,
1424 x_Attribute18 => l_org_rec.Attribute18,
1425 x_Attribute19 => l_org_rec.Attribute19,
1426 x_Attribute20 => l_org_rec.Attribute20
1427 );
1428 hr_utility.set_location(' Leaving:' ,80);
1429
1430 End delete_pension_provider_gb;
1431
1432 --
1433 -- ----------------------------------------------------------------------------
1434 -- |---------------------< delete_pension_provider_hu>-------------------------|
1435 -- ----------------------------------------------------------------------------
1436 --
1437
1438
1439 Procedure delete_pension_provider_hu
1440 (p_organization_id in number
1441 ,p_effective_date in date)
1442 Is
1443
1444
1445 Cursor csr_org Is
1446 Select *
1447 from hr_organization_units_v
1448 where organization_id = p_organization_id;
1449
1450 Cursor csr_org_info Is
1451 Select rowid,org_information_id,org_information_context,org_information1
1452 from hr_organization_information
1453 where org_information_context = 'CLASS'
1454 and org_information1 = 'FR_PENSION'
1455 and organization_id = p_organization_id;
1456
1457 Cursor csr_third_party_org_info Is
1458 Select rowid,org_information_id,org_information_context,org_information1
1459 from hr_organization_information
1460 where org_information_context = 'CLASS'
1461 and org_information1 = 'HR_PAYEE'
1462 and organization_id = p_organization_id;
1463
1464 Cursor csr_pen_types_org_info Is
1465 Select rowid
1466 from hr_organization_information
1467 where org_information_context = 'HU_PENSION_TYPES_INFO'
1468 and organization_id = p_organization_id;
1469
1470 Cursor csr_is_provider_assigned Is
1471 Select 1
1472 from pay_element_type_extra_info eei
1473 where eei.information_type = 'HU_PENSION_SCHEME_INFO'
1474 and eei.eei_information_category = 'HU_PENSION_SCHEME_INFO'
1475 and eei.eei_information2 = p_organization_id;
1476
1477 l_org_info_rowid rowid;
1478 l_org_information_id number;
1479 l_org_information_context varchar2(40);
1480 l_org_classification varchar2(150);
1481 l_assigned_provider number;
1482 l_org_rec csr_org%ROWTYPE;
1483
1484
1485 BEGIN
1486 hr_utility.set_location(' Leaving:' ,20);
1487 open csr_is_provider_assigned;
1488 Fetch csr_is_provider_assigned Into l_assigned_provider;
1489 If csr_is_provider_assigned%Found Then
1490 Close csr_is_provider_assigned;
1491 --Error : The provider is attached to a scheme
1492 fnd_message.set_name('PQP','PQP_230959_PRVDR_ATTACH_SCHM');
1493 fnd_message.raise_error;
1494
1495 Else
1496 Close csr_is_provider_assigned;
1497
1498 End If;
1499
1500 open csr_org_info;
1501 fetch csr_org_info into l_org_info_rowid,l_org_information_id,
1502 l_org_information_context,l_org_classification;
1503 close csr_org_info;
1504 hr_utility.set_location(' Leaving:' ,40);
1505 -- Disable the Organization classification of Pension Provider of the Org
1506 hr_org_information_pkg.update_row(
1507 x_rowid => l_org_info_rowid,
1508 x_org_information_id => l_org_information_id,
1509 x_org_information_context => l_org_information_context,
1510 x_organization_id => p_organization_id,
1511 x_org_information1 => l_org_classification,
1512 x_org_information2 => 'N',
1513 x_org_information3 => NULL,
1514 x_org_information4 => NULL,
1515 x_org_information5 => NULL,
1516 x_org_information6 => NULL,
1517 x_org_information7 => NULL,
1518 x_org_information8 => NULL,
1519 x_org_information9 => NULL,
1520 x_org_information10 => NULL,
1521 x_org_information11 => NULL,
1522 x_org_information12 => NULL,
1523 x_org_information13 => NULL,
1524 x_org_information14 => NULL,
1525 x_org_information15 => NULL,
1526 x_org_information16 => NULL,
1527 x_org_information17 => NULL,
1528 x_org_information18 => NULL,
1529 x_org_information19 => NULL,
1530 x_org_information20 => NULL,
1531 x_attribute_category => NULL,
1532 x_attribute1 => NULL,
1533 x_attribute2 => NULL,
1534 x_attribute3 => NULL,
1535 x_attribute4 => NULL,
1536 x_attribute5 => NULL,
1537 x_attribute6 => NULL,
1538 x_attribute7 => NULL,
1539 x_attribute8 => NULL,
1540 x_attribute9 => NULL,
1541 x_attribute10 => NULL,
1542 x_attribute11 => NULL,
1543 x_attribute12 => NULL,
1544 x_attribute13 => NULL,
1545 x_attribute14 => NULL,
1546 x_attribute15 => NULL,
1547 x_attribute16 => NULL,
1548 x_attribute17 => NULL,
1549 x_attribute18 => NULL,
1550 x_attribute19 => NULL,
1551 x_attribute20 => NULL);
1552 hr_utility.set_location(' Leaving:' ,50);
1553 -- remove all the org_information rows containing the pension types
1554 --attached to this provider
1555 Open csr_pen_types_org_info;
1556
1557 loop
1558 fetch csr_pen_types_org_info into l_org_info_rowid;
1559 exit when csr_pen_types_org_info%NOTFOUND;
1560 -- delete the org info row containing this pension type
1561 hr_org_information_pkg.delete_row(l_org_info_rowid);
1562 end loop;
1563
1564 close csr_pen_types_org_info;
1565
1566
1567 -- if a third party payment classification exists, disable that too
1568 open csr_third_party_org_info;
1569 fetch csr_third_party_org_info into l_org_info_rowid,l_org_information_id,
1570 l_org_information_context,l_org_classification;
1571 hr_utility.set_location(' Leaving:' ,60);
1572 if csr_third_party_org_info%Found Then
1573 -- Disable the Third Party Payment Organization classification
1574 -- of Pension Provider
1575 close csr_third_party_org_info;
1576 hr_org_information_pkg.update_row(
1577 x_rowid => l_org_info_rowid,
1578 x_org_information_id => l_org_information_id,
1579 x_org_information_context => l_org_information_context,
1580 x_organization_id => p_organization_id,
1581 x_org_information1 => l_org_classification,
1582 x_org_information2 => 'N',
1583 x_org_information3 => NULL,
1584 x_org_information4 => NULL,
1585 x_org_information5 => NULL,
1586 x_org_information6 => NULL,
1587 x_org_information7 => NULL,
1588 x_org_information8 => NULL,
1589 x_org_information9 => NULL,
1590 x_org_information10 => NULL,
1591 x_org_information11 => NULL,
1592 x_org_information12 => NULL,
1593 x_org_information13 => NULL,
1594 x_org_information14 => NULL,
1595 x_org_information15 => NULL,
1596 x_org_information16 => NULL,
1597 x_org_information17 => NULL,
1598 x_org_information18 => NULL,
1599 x_org_information19 => NULL,
1600 x_org_information20 => NULL,
1601 x_attribute_category => NULL,
1602 x_attribute1 => NULL,
1603 x_attribute2 => NULL,
1604 x_attribute3 => NULL,
1605 x_attribute4 => NULL,
1606 x_attribute5 => NULL,
1607 x_attribute6 => NULL,
1608 x_attribute7 => NULL,
1609 x_attribute8 => NULL,
1610 x_attribute9 => NULL,
1611 x_attribute10 => NULL,
1612 x_attribute11 => NULL,
1613 x_attribute12 => NULL,
1614 x_attribute13 => NULL,
1615 x_attribute14 => NULL,
1616 x_attribute15 => NULL,
1617 x_attribute16 => NULL,
1618 x_attribute17 => NULL,
1619 x_attribute18 => NULL,
1620 x_attribute19 => NULL,
1621 x_attribute20 => NULL);
1622
1623 else
1624 close csr_third_party_org_info;
1625 End If;
1626 hr_utility.set_location(' Leaving:' ,70);
1627
1628 -- end-date the pension provider organization with the current effective date
1629 Open csr_org;
1630 Fetch csr_org into l_org_rec;
1631 If csr_org%NOTFOUND Then
1632 Close csr_org;
1633 fnd_message.set_name('PQP', 'PQP_230815_INVALID_ORG');
1634 fnd_message.raise_error;
1635 Else
1636 Close csr_org;
1637 End If;
1638 hr_organization_units_pkg.Update_Row
1639 (x_Rowid => l_org_rec.row_id,
1640 x_Organization_Id => l_org_rec.organization_id,
1641 x_Business_Group_Id => l_org_rec.business_group_id,
1642 x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
1643 x_Location_Id => l_org_rec.Location_Id,
1644 x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
1645 x_Date_From => l_org_rec.Date_From,
1646 x_Name => l_org_rec.Name,
1647 x_Comments => l_org_rec.Comments,
1648 x_Date_To => p_effective_date,
1649 x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
1650 x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
1651 x_Type => l_org_rec.Type,
1652 x_Attribute_Category => l_org_rec.Attribute_Category,
1653 x_Attribute1 => l_org_rec.Attribute1,
1654 x_Attribute2 => l_org_rec.Attribute2,
1655 x_Attribute3 => l_org_rec.Attribute3,
1656 x_Attribute4 => l_org_rec.Attribute4,
1657 x_Attribute5 => l_org_rec.Attribute5,
1658 x_Attribute6 => l_org_rec.Attribute6,
1659 x_Attribute7 => l_org_rec.Attribute7,
1660 x_Attribute8 => l_org_rec.Attribute8,
1661 x_Attribute9 => l_org_rec.Attribute9,
1662 x_Attribute10 => l_org_rec.Attribute10,
1663 x_Attribute11 => l_org_rec.Attribute11,
1664 x_Attribute12 => l_org_rec.Attribute12,
1665 x_Attribute13 => l_org_rec.Attribute13,
1666 x_Attribute14 => l_org_rec.Attribute14,
1667 x_Attribute15 => l_org_rec.Attribute15,
1668 x_Attribute16 => l_org_rec.Attribute16,
1669 x_Attribute17 => l_org_rec.Attribute17,
1670 x_Attribute18 => l_org_rec.Attribute18,
1671 x_Attribute19 => l_org_rec.Attribute19,
1672 x_Attribute20 => l_org_rec.Attribute20
1673 );
1674 hr_utility.set_location(' Leaving:' ,80);
1675
1676 End delete_pension_provider_hu;
1677 --
1678 -- ----------------------------------------------------------------------------
1679 -- |---------------------< delete_pension_provider_ie>-------------------------|
1680 -- ----------------------------------------------------------------------------
1681 --
1682
1683
1684 Procedure delete_pension_provider_ie
1685 (p_organization_id in number
1686 ,p_effective_date in date)
1687 Is
1688
1689
1690 Cursor csr_org Is
1691 Select *
1692 from hr_organization_units_v
1693 where organization_id = p_organization_id;
1694
1695 Cursor csr_org_info Is
1696 Select rowid,org_information_id,org_information_context,org_information1
1697 from hr_organization_information
1698 where org_information_context = 'CLASS'
1699 and org_information1 = 'IE_PENSION'
1700 and organization_id = p_organization_id;
1701
1702 Cursor csr_third_party_org_info Is
1703 Select rowid,org_information_id,org_information_context,org_information1
1704 from hr_organization_information
1705 where org_information_context = 'CLASS'
1706 and org_information1 = 'HR_PAYEE'
1707 and organization_id = p_organization_id;
1708
1709 Cursor csr_pen_types_org_info Is
1710 Select rowid
1711 from hr_organization_information
1712 where org_information_context = 'IE_PENSION_TYPES_INFO'
1713 and organization_id = p_organization_id;
1714
1715 Cursor csr_is_provider_assigned Is
1716 Select 1
1717 from pay_element_type_extra_info eei
1718 where eei.information_type = 'IE_PENSION_SCHEME_INFO'
1719 and eei.eei_information_category = 'IE_PENSION_SCHEME_INFO'
1720 and eei.eei_information2 = p_organization_id;
1721
1722 l_org_info_rowid rowid;
1723 l_org_information_id number;
1724 l_org_information_context varchar2(40);
1725 l_org_classification varchar2(150);
1726 l_assigned_provider number;
1727 l_org_rec csr_org%ROWTYPE;
1728
1729
1730 BEGIN
1731 hr_utility.set_location(' Leaving:' ,20);
1732 open csr_is_provider_assigned;
1733 Fetch csr_is_provider_assigned Into l_assigned_provider;
1734 If csr_is_provider_assigned%Found Then
1735 Close csr_is_provider_assigned;
1736 --Error : The provider is attached to a scheme
1737 fnd_message.set_name('PQP','PQP_230959_PRVDR_ATTACH_SCHM');
1738 fnd_message.raise_error;
1739
1740 Else
1741 Close csr_is_provider_assigned;
1742
1743 End If;
1744
1745 open csr_org_info;
1746 fetch csr_org_info into l_org_info_rowid,l_org_information_id,
1747 l_org_information_context,l_org_classification;
1748 close csr_org_info;
1749 hr_utility.set_location(' Leaving:' ,40);
1750 -- Disable the Organization classification of Pension Provider of the Org
1751 hr_org_information_pkg.update_row(
1752 x_rowid => l_org_info_rowid,
1753 x_org_information_id => l_org_information_id,
1754 x_org_information_context => l_org_information_context,
1755 x_organization_id => p_organization_id,
1756 x_org_information1 => l_org_classification,
1757 x_org_information2 => 'N',
1758 x_org_information3 => NULL,
1759 x_org_information4 => NULL,
1760 x_org_information5 => NULL,
1761 x_org_information6 => NULL,
1762 x_org_information7 => NULL,
1763 x_org_information8 => NULL,
1764 x_org_information9 => NULL,
1765 x_org_information10 => NULL,
1766 x_org_information11 => NULL,
1767 x_org_information12 => NULL,
1768 x_org_information13 => NULL,
1769 x_org_information14 => NULL,
1770 x_org_information15 => NULL,
1771 x_org_information16 => NULL,
1772 x_org_information17 => NULL,
1773 x_org_information18 => NULL,
1774 x_org_information19 => NULL,
1775 x_org_information20 => NULL,
1776 x_attribute_category => NULL,
1777 x_attribute1 => NULL,
1778 x_attribute2 => NULL,
1779 x_attribute3 => NULL,
1780 x_attribute4 => NULL,
1781 x_attribute5 => NULL,
1782 x_attribute6 => NULL,
1783 x_attribute7 => NULL,
1784 x_attribute8 => NULL,
1785 x_attribute9 => NULL,
1786 x_attribute10 => NULL,
1787 x_attribute11 => NULL,
1788 x_attribute12 => NULL,
1789 x_attribute13 => NULL,
1790 x_attribute14 => NULL,
1791 x_attribute15 => NULL,
1792 x_attribute16 => NULL,
1793 x_attribute17 => NULL,
1794 x_attribute18 => NULL,
1795 x_attribute19 => NULL,
1796 x_attribute20 => NULL);
1797 hr_utility.set_location(' Leaving:' ,50);
1798 -- remove all the org_information rows containing the pension types
1799 --attached to this provider
1800 Open csr_pen_types_org_info;
1801
1802 loop
1803 fetch csr_pen_types_org_info into l_org_info_rowid;
1804 exit when csr_pen_types_org_info%NOTFOUND;
1805 -- delete the org info row containing this pension type
1806 hr_org_information_pkg.delete_row(l_org_info_rowid);
1807 end loop;
1808
1809 close csr_pen_types_org_info;
1810
1811
1812 -- if a third party payment classification exists, disable that too
1813 open csr_third_party_org_info;
1814 fetch csr_third_party_org_info into l_org_info_rowid,l_org_information_id,
1815 l_org_information_context,l_org_classification;
1816 hr_utility.set_location(' Leaving:' ,60);
1817 if csr_third_party_org_info%Found Then
1818 -- Disable the Third Party Payment Organization classification
1819 -- of Pension Provider
1820 close csr_third_party_org_info;
1821 hr_org_information_pkg.update_row(
1822 x_rowid => l_org_info_rowid,
1823 x_org_information_id => l_org_information_id,
1824 x_org_information_context => l_org_information_context,
1825 x_organization_id => p_organization_id,
1826 x_org_information1 => l_org_classification,
1827 x_org_information2 => 'N',
1828 x_org_information3 => NULL,
1829 x_org_information4 => NULL,
1830 x_org_information5 => NULL,
1831 x_org_information6 => NULL,
1832 x_org_information7 => NULL,
1833 x_org_information8 => NULL,
1834 x_org_information9 => NULL,
1835 x_org_information10 => NULL,
1836 x_org_information11 => NULL,
1837 x_org_information12 => NULL,
1838 x_org_information13 => NULL,
1839 x_org_information14 => NULL,
1840 x_org_information15 => NULL,
1841 x_org_information16 => NULL,
1842 x_org_information17 => NULL,
1843 x_org_information18 => NULL,
1844 x_org_information19 => NULL,
1845 x_org_information20 => NULL,
1846 x_attribute_category => NULL,
1847 x_attribute1 => NULL,
1848 x_attribute2 => NULL,
1849 x_attribute3 => NULL,
1850 x_attribute4 => NULL,
1851 x_attribute5 => NULL,
1852 x_attribute6 => NULL,
1853 x_attribute7 => NULL,
1854 x_attribute8 => NULL,
1855 x_attribute9 => NULL,
1856 x_attribute10 => NULL,
1857 x_attribute11 => NULL,
1858 x_attribute12 => NULL,
1859 x_attribute13 => NULL,
1860 x_attribute14 => NULL,
1861 x_attribute15 => NULL,
1862 x_attribute16 => NULL,
1863 x_attribute17 => NULL,
1864 x_attribute18 => NULL,
1865 x_attribute19 => NULL,
1866 x_attribute20 => NULL);
1867
1868 else
1869 close csr_third_party_org_info;
1870 End If;
1871 hr_utility.set_location(' Leaving:' ,70);
1872
1873 -- end-date the pension provider organization with the current effective date
1874 Open csr_org;
1875 Fetch csr_org into l_org_rec;
1876 If csr_org%NOTFOUND Then
1877 Close csr_org;
1878 fnd_message.set_name('PQP', 'PQP_230815_INVALID_ORG');
1879 fnd_message.raise_error;
1880 Else
1881 Close csr_org;
1882 End If;
1883 hr_organization_units_pkg.Update_Row
1884 (x_Rowid => l_org_rec.row_id,
1885 x_Organization_Id => l_org_rec.organization_id,
1886 x_Business_Group_Id => l_org_rec.business_group_id,
1887 x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
1888 x_Location_Id => l_org_rec.Location_Id,
1889 x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
1890 x_Date_From => l_org_rec.Date_From,
1891 x_Name => l_org_rec.Name,
1892 x_Comments => l_org_rec.Comments,
1893 x_Date_To => p_effective_date,
1894 x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
1895 x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
1896 x_Type => l_org_rec.Type,
1897 x_Attribute_Category => l_org_rec.Attribute_Category,
1898 x_Attribute1 => l_org_rec.Attribute1,
1899 x_Attribute2 => l_org_rec.Attribute2,
1900 x_Attribute3 => l_org_rec.Attribute3,
1901 x_Attribute4 => l_org_rec.Attribute4,
1902 x_Attribute5 => l_org_rec.Attribute5,
1903 x_Attribute6 => l_org_rec.Attribute6,
1904 x_Attribute7 => l_org_rec.Attribute7,
1905 x_Attribute8 => l_org_rec.Attribute8,
1906 x_Attribute9 => l_org_rec.Attribute9,
1907 x_Attribute10 => l_org_rec.Attribute10,
1908 x_Attribute11 => l_org_rec.Attribute11,
1909 x_Attribute12 => l_org_rec.Attribute12,
1910 x_Attribute13 => l_org_rec.Attribute13,
1911 x_Attribute14 => l_org_rec.Attribute14,
1912 x_Attribute15 => l_org_rec.Attribute15,
1913 x_Attribute16 => l_org_rec.Attribute16,
1914 x_Attribute17 => l_org_rec.Attribute17,
1915 x_Attribute18 => l_org_rec.Attribute18,
1916 x_Attribute19 => l_org_rec.Attribute19,
1917 x_Attribute20 => l_org_rec.Attribute20
1918 );
1919 hr_utility.set_location(' Leaving:' ,80);
1920
1921 End delete_pension_provider_ie;
1922
1923
1924 --
1925 -- ----------------------------------------------------------------------------
1926 -- |---------------------< create_pension_provider_swi >-----------------------|
1927 -- ----------------------------------------------------------------------------
1928
1929 procedure create_pension_provider_swi
1930 (p_pension_provider_name in varchar2
1931 ,p_business_group_id in number
1932 ,p_legislation_code in varchar2
1933 ,p_security_profile_id in number
1934 ,p_effective_start_date in date
1935 ,p_internal_external_flag in varchar2
1936 ,p_type in varchar2
1937 ,p_location_id in out nocopy number
1938 ,p_create_new_location_flag in varchar2
1939 ,p_org_information_context in varchar2
1940 ,p_org_classification in varchar2
1941 ,p_organization_id out nocopy number
1942 ,p_org_information_id out nocopy number
1943 ,p_location_code in varchar2
1944 ,p_language_code in varchar2
1945 ,p_country_code in varchar2
1946 ,p_address_line_1 in varchar2 default null
1947 ,p_address_line_2 in varchar2 default null
1948 ,p_address_line_3 in varchar2 default null
1949 ,p_postal_code in varchar2 default null
1950 ,p_region_1 in varchar2 default null
1951 ,p_region_2 in varchar2 default null
1952 ,p_region_3 in varchar2 default null
1953 ,p_style in varchar2 default null
1954 ,p_tax_name in varchar2 default null
1955 ,p_town_or_city in varchar2 default null
1956 ,p_location_extra_info_id out nocopy number
1957 ,p_information_type in varchar2 default null
1958 ,p_lei_information_category in varchar2 default null
1959 ,p_lei_information1 in varchar2 default null
1960 ,p_lei_information2 in varchar2 default null
1961 ,p_lei_information3 in varchar2 default null
1962 ,p_lei_information4 in varchar2 default null
1963 ,p_lei_information5 in varchar2 default null
1964 ,p_lei_information6 in varchar2 default null
1965 ,p_lei_information7 in varchar2 default null
1966 ,p_lei_information8 in varchar2 default null
1967 ,p_lei_information9 in varchar2 default null
1968 ,p_lei_information10 in varchar2 default null
1969 ,p_lei_information11 in varchar2 default null
1970 ,p_lei_information12 in varchar2 default null
1971 ,p_lei_information13 in varchar2 default null
1972 ,p_lei_information14 in varchar2 default null
1973 ,p_lei_information15 in varchar2 default null
1974 ,p_lei_information16 in varchar2 default null
1975 ,p_lei_information17 in varchar2 default null
1976 ,p_lei_information18 in varchar2 default null
1977 ,p_lei_information19 in varchar2 default null
1978 ,p_lei_information20 in varchar2 default null
1979 ,p_lei_information21 in varchar2 default null
1980 ,p_lei_information22 in varchar2 default null
1981 ,p_lei_information23 in varchar2 default null
1982 ,p_lei_information24 in varchar2 default null
1983 ,p_lei_information25 in varchar2 default null
1984 ,p_lei_information26 in varchar2 default null
1985 ,p_lei_information27 in varchar2 default null
1986 ,p_lei_information28 in varchar2 default null
1987 ,p_lei_information29 in varchar2 default null
1988 ,p_lei_information30 in varchar2 default null
1989
1990 ) IS
1991
1992 --
1993 -- Variables for API Boolean parameters
1994 l_validate boolean;
1995 --
1996 -- Variables for IN/OUT parameters
1997 --
1998 -- Other variables
1999 l_return_status varchar2(1);
2000 l_proc varchar2(72) := 'create_pension_provider';
2001 Begin
2002 hr_utility.set_location(' Entering:' || l_proc,10);
2003 --
2004 -- Issue a savepoint
2005 --
2006 savepoint create_pension_provider;
2007 --
2008 -- Initialise Multiple Message Detection
2009 --
2010 hr_multi_message.enable_message_list;
2011 --
2012 -- Remember IN OUT parameter IN values
2013 --
2014 --
2015 -- Convert constant values to their corresponding boolean value
2016 --
2017 l_validate :=
2018 hr_api.constant_to_boolean
2019 (p_constant_value => hr_api.g_false_num);
2020 --
2021 -- Register Surrogate ID or user key values
2022 --
2023 --
2024 -- Call API
2025 --
2026 pqp_pension_providers.create_pension_provider
2027 (p_pension_provider_name => p_pension_provider_name
2028 ,p_business_group_id => p_business_group_id
2029 ,p_legislation_code => p_legislation_code
2030 ,p_security_profile_id => p_security_profile_id
2031 ,p_effective_start_date => p_effective_start_date
2032 ,p_internal_external_flag => p_internal_external_flag
2033 ,p_type => p_type
2034 ,p_location_id => p_location_id
2035 ,p_create_new_location_flag => p_create_new_location_flag
2036 ,p_org_information_context => p_org_information_context
2037 ,p_org_classification => p_org_classification
2038 ,p_organization_id => p_organization_id
2039 ,p_org_information_id => p_org_information_id
2040 ,p_location_code => p_location_code
2041 ,p_language_code => p_language_code
2042 ,p_country_code => p_country_code
2043 ,p_address_line_1 => p_address_line_1
2044 ,p_address_line_2 => p_address_line_2
2045 ,p_address_line_3 => p_address_line_3
2046 ,p_postal_code => p_postal_code
2047 ,p_region_1 => p_region_1
2048 ,p_region_2 => p_region_2
2049 ,p_region_3 => p_region_3
2050 ,p_style => p_style
2051 ,p_tax_name => p_tax_name
2052 ,p_town_or_city => p_town_or_city
2053 ,p_location_extra_info_id => p_location_extra_info_id
2054 ,p_information_type => p_information_type
2055 ,p_lei_information_category => p_lei_information_category
2056 ,p_lei_information1 => p_lei_information1
2057 ,p_lei_information2 => p_lei_information2
2058 ,p_lei_information3 => p_lei_information3
2059 ,p_lei_information4 => p_lei_information4
2060 ,p_lei_information5 => p_lei_information5
2061 ,p_lei_information6 => p_lei_information6
2062 ,p_lei_information7 => p_lei_information7
2063 ,p_lei_information8 => p_lei_information8
2064 ,p_lei_information9 => p_lei_information9
2065 ,p_lei_information10 => p_lei_information10
2066 ,p_lei_information11 => p_lei_information11
2067 ,p_lei_information12 => p_lei_information12
2068 ,p_lei_information13 => p_lei_information13
2069 ,p_lei_information14 => p_lei_information14
2070 ,p_lei_information15 => p_lei_information15
2071 ,p_lei_information16 => p_lei_information16
2072 ,p_lei_information17 => p_lei_information17
2073 ,p_lei_information18 => p_lei_information18
2074 ,p_lei_information19 => p_lei_information19
2075 ,p_lei_information20 => p_lei_information20
2076 ,p_lei_information21 => p_lei_information21
2077 ,p_lei_information22 => p_lei_information22
2078 ,p_lei_information23 => p_lei_information23
2079 ,p_lei_information24 => p_lei_information24
2080 ,p_lei_information25 => p_lei_information25
2081 ,p_lei_information26 => p_lei_information26
2082 ,p_lei_information27 => p_lei_information27
2083 ,p_lei_information28 => p_lei_information28
2084 ,p_lei_information29 => p_lei_information29
2085 ,p_lei_information30 => p_lei_information30
2086 );
2087 --
2088 -- Convert API warning boolean parameter values to specific
2089 -- messages and add them to Multiple Message List
2090 --
2091 --
2092 -- Convert API non-warning boolean parameter values
2093 --
2094 --
2095 -- Derive the API return status value based on whether
2096 -- messages of any type exist in the Multiple Message List.
2097 -- Also disable Multiple Message Detection.
2098 --
2099 l_return_status := hr_multi_message.get_return_status_disable;
2100 hr_utility.set_location(' Leaving:' || l_proc,20);
2101 --
2102 exception
2103 when hr_multi_message.error_message_exist then
2104 --
2105 -- Catch the Multiple Message List exception which
2106 -- indicates API processing has been aborted because
2107 -- at least one message exists in the list.
2108 --
2109 rollback to create_pension_provider;
2110 --
2111 -- Reset IN OUT parameters and set OUT parameters
2112 --
2113
2114 hr_utility.set_location(' Leaving:' || l_proc, 30);
2115
2116 when others then
2117 --
2118 -- When Multiple Message Detection is enabled catch
2119 -- any Application specific or other unexpected
2120 -- exceptions. Adding appropriate details to the
2121 -- Multiple Message List. Otherwise re-raise the
2122 -- error.
2123 --
2124 rollback to create_pension_provider;
2125 if hr_multi_message.unexpected_error_add(l_proc) then
2126 hr_utility.set_location(' Leaving:' || l_proc,40);
2127 raise;
2128 end if;
2129 --
2130 -- Reset IN OUT and set OUT parameters
2131 --
2132 l_return_status := hr_multi_message.get_return_status_disable;
2133 hr_utility.set_location(' Leaving:' || l_proc,50);
2134
2135 END create_pension_provider_swi;
2136
2137 --
2138 -- ----------------------------------------------------------------------------
2139 -- |---------------------< update_pension_provider_swi >-----------------------|
2140 -- ----------------------------------------------------------------------------
2141 --
2142 Procedure update_pension_provider_swi
2143 (p_organization_id in number
2144 ,p_business_group_id in number
2145 ,p_provider_name in varchar2
2146 ,p_location_id in number
2147 ,p_date_from in date
2148 ,p_date_to in date ) Is
2149
2150 --
2151 -- Variables for API Boolean parameters
2152 l_validate boolean;
2153 --
2154 -- Variables for IN/OUT parameters
2155 --
2156 -- Other variables
2157 l_return_status varchar2(1);
2158 l_proc varchar2(72) := 'update_pension_provider';
2159 Begin
2160 hr_utility.set_location(' Entering:' || l_proc,10);
2161 --
2162 -- Issue a savepoint
2163 --
2164 savepoint update_pension_provider;
2165 --
2166 -- Initialise Multiple Message Detection
2167 --
2168 hr_multi_message.enable_message_list;
2169 --
2170 -- Remember IN OUT parameter IN values
2171 --
2172 --
2173 -- Convert constant values to their corresponding boolean value
2174 --
2175 l_validate :=
2176 hr_api.constant_to_boolean
2177 (p_constant_value => hr_api.g_false_num);
2178 --
2179 -- Register Surrogate ID or user key values
2180 --
2181 --
2182 -- Call API
2183 --
2184 pqp_pension_providers.update_pension_provider
2185 (p_organization_id => p_organization_id
2186 ,p_business_group_id => p_business_group_id
2187 ,p_provider_name => p_provider_name
2188 ,p_location_id => p_location_id
2189 ,p_date_from => p_date_from
2190 ,p_date_to => p_date_to
2191 );
2192 --
2193 -- Convert API warning boolean parameter values to specific
2194 -- messages and add them to Multiple Message List
2195 --
2196 --
2197 -- Convert API non-warning boolean parameter values
2198 --
2199 --
2200 -- Derive the API return status value based on whether
2201 -- messages of any type exist in the Multiple Message List.
2202 -- Also disable Multiple Message Detection.
2203 --
2204 l_return_status := hr_multi_message.get_return_status_disable;
2205 hr_utility.set_location(' Leaving:' || l_proc,20);
2206 --
2207 exception
2208 when hr_multi_message.error_message_exist then
2209 --
2210 -- Catch the Multiple Message List exception which
2211 -- indicates API processing has been aborted because
2212 -- at least one message exists in the list.
2213 --
2214 rollback to update_pension_provider;
2215 --
2216 -- Reset IN OUT parameters and set OUT parameters
2217 --
2218
2219 hr_utility.set_location(' Leaving:' || l_proc, 30);
2220
2221 when others then
2222 --
2223 -- When Multiple Message Detection is enabled catch
2224 -- any Application specific or other unexpected
2225 -- exceptions. Adding appropriate details to the
2226 -- Multiple Message List. Otherwise re-raise the
2227 -- error.
2228 --
2229 rollback to update_pension_provider;
2230 if hr_multi_message.unexpected_error_add(l_proc) then
2231 hr_utility.set_location(' Leaving:' || l_proc,40);
2232 raise;
2233 end if;
2234 --
2235 -- Reset IN OUT and set OUT parameters
2236 --
2237 l_return_status := hr_multi_message.get_return_status_disable;
2238 hr_utility.set_location(' Leaving:' || l_proc,50);
2239
2240 End update_pension_provider_swi;
2241
2242 --
2243 -- ----------------------------------------------------------------------------
2244 -- |---------------------< delete_pension_provider_swi >-----------------------|
2245 -- ----------------------------------------------------------------------------
2246 --
2247 Procedure delete_pension_provider_swi
2248 (p_organization_id in number
2249 ,p_effective_date in date
2250 ,p_business_group_id in number)
2251 Is
2252
2253 --
2254 -- Variables for API Boolean parameters
2255 l_validate boolean;
2256 --
2257 -- Variables for IN/OUT parameters
2258 --
2259 -- Other variables
2260 l_return_status varchar2(1);
2261 l_proc varchar2(72) := 'delete_pension_provider';
2262 l_leg_code varchar2(5);
2263
2264 Begin
2265 hr_utility.set_location(' Entering:' || l_proc,10);
2266 --
2267 -- Issue a savepoint
2268 --
2269 savepoint delete_pension_provider;
2270 --
2271 -- Initialise Multiple Message Detection
2272 --
2273 hr_multi_message.enable_message_list;
2274 --
2275 -- Remember IN OUT parameter IN values
2276 --
2277 --
2278 -- Convert constant values to their corresponding boolean value
2279 --
2280 l_validate :=
2281 hr_api.constant_to_boolean
2282 (p_constant_value => hr_api.g_false_num);
2283 --
2284 -- Register Surrogate ID or user key values
2285 --
2286
2287 --
2288 -- Get the legislation code for the business group
2289 --
2290 l_leg_code := pqp_utilities.pqp_get_legislation_code(p_business_group_id);
2291
2292 --
2293 -- Call API according to the appropriate legislation
2294 --
2295 IF l_leg_code = 'NL' THEN
2296
2297 pqp_pension_providers.delete_pension_provider_nl
2298 (p_organization_id => p_organization_id);
2299
2300 ELSIF l_leg_code = 'GB' THEN
2301
2302 pqp_pension_providers.delete_pension_provider_gb
2303 (p_organization_id => p_organization_id
2304 ,p_effective_date => p_effective_date);
2305
2306 ELSIF l_leg_code = 'HU' THEN
2307
2308 pqp_pension_providers.delete_pension_provider_hu
2309 (p_organization_id => p_organization_id
2310 ,p_effective_date => p_effective_date);
2311 ELSIF l_leg_code = 'IE' THEN
2312
2313 pqp_pension_providers.delete_pension_provider_ie
2314 (p_organization_id => p_organization_id
2315 ,p_effective_date => p_effective_date);
2316
2317
2318 END IF;
2319
2320 hr_utility.set_location(' Called:' || l_proc,15);
2321 --
2322 -- Convert API warning boolean parameter values to specific
2323 -- messages and add them to Multiple Message List
2324 --
2325 --
2326 -- Convert API non-warning boolean parameter values
2327 --
2328 --
2329 -- Derive the API return status value based on whether
2330 -- messages of any type exist in the Multiple Message List.
2331 -- Also disable Multiple Message Detection.
2332 --
2333 l_return_status := hr_multi_message.get_return_status_disable;
2334 hr_utility.set_location(' Leaving:' || l_proc,20);
2335 --
2336 exception
2337 when hr_multi_message.error_message_exist then
2338 --
2339 -- Catch the Multiple Message List exception which
2340 -- indicates API processing has been aborted because
2341 -- at least one message exists in the list.
2342 --
2343 rollback to delete_pension_provider;
2344 --
2345 -- Reset IN OUT parameters and set OUT parameters
2346 --
2347
2348 hr_utility.set_location(' Leaving:' || l_proc, 30);
2349
2350 when others then
2351 --
2352 -- When Multiple Message Detection is enabled catch
2353 -- any Application specific or other unexpected
2354 -- exceptions. Adding appropriate details to the
2355 -- Multiple Message List. Otherwise re-raise the
2356 -- error.
2357 --
2358 rollback to delete_pension_provider;
2359 if hr_multi_message.unexpected_error_add(l_proc) then
2360 hr_utility.set_location(' Leaving:' || l_proc,40);
2361 raise;
2362 end if;
2363 --
2364 -- Reset IN OUT and set OUT parameters
2365 --
2366 l_return_status := hr_multi_message.get_return_status_disable;
2367 hr_utility.set_location(' Leaving:' || l_return_status,45);
2368 hr_utility.set_location(' Leaving:' || l_proc,50);
2369 End delete_pension_provider_swi;
2370
2371 -- ----------------------------------------------------------------------------
2372 -- |-------------------< add_pension_types_swi >-------------------------------|
2373 -- ----------------------------------------------------------------------------
2374
2375 procedure add_pension_types_swi
2376 (p_organization_id in number
2377 ,p_business_group_id in number
2378 ,p_legislation_code in varchar2
2379 ,p_org_information_context in varchar2
2380 ,p_pension_type_id in number
2381 ,p_org_information_id out nocopy number
2382 ) IS
2383
2384 --
2385 -- Variables for API Boolean parameters
2386 l_validate boolean;
2387 --
2388 -- Variables for IN/OUT parameters
2389 --
2390 -- Other variables
2391 l_return_status varchar2(1);
2392 l_proc varchar2(72) := 'add_pension_types';
2393 Begin
2394 hr_utility.set_location(' Entering:' || l_proc,10);
2395 --
2396 -- Issue a savepoint
2397 --
2398 savepoint add_pension_types;
2399 --
2400 -- Initialise Multiple Message Detection
2401 --
2402 hr_multi_message.enable_message_list;
2403 --
2404 -- Remember IN OUT parameter IN values
2405 --
2406 --
2407 -- Convert constant values to their corresponding boolean value
2408 --
2409 l_validate :=
2410 hr_api.constant_to_boolean
2411 (p_constant_value => hr_api.g_false_num);
2412 --
2413 -- Register Surrogate ID or user key values
2414 --
2415 --
2416 -- Call API
2417 --
2418 pqp_pension_providers.add_pension_types
2419 (p_organization_id => p_organization_id
2420 ,p_business_group_id => p_business_group_id
2421 ,p_legislation_code => p_legislation_code
2422 ,p_org_information_context => p_org_information_context
2423 ,p_pension_type_id => p_pension_type_id
2424 ,p_org_information_id => p_org_information_id
2425 );
2426 --
2427 -- Convert API warning boolean parameter values to specific
2428 -- messages and add them to Multiple Message List
2429 --
2430 --
2431 -- Convert API non-warning boolean parameter values
2432 --
2433 --
2434 -- Derive the API return status value based on whether
2435 -- messages of any type exist in the Multiple Message List.
2436 -- Also disable Multiple Message Detection.
2437 --
2438 l_return_status := hr_multi_message.get_return_status_disable;
2439 hr_utility.set_location(' Leaving:' || l_proc,20);
2440 --
2441 exception
2442 when hr_multi_message.error_message_exist then
2443 --
2444 -- Catch the Multiple Message List exception which
2445 -- indicates API processing has been aborted because
2446 -- at least one message exists in the list.
2447 --
2448 rollback to add_pension_types;
2449 --
2450 -- Reset IN OUT parameters and set OUT parameters
2451 --
2452
2453 hr_utility.set_location(' Leaving:' || l_proc, 30);
2454
2455 when others then
2456 --
2457 -- When Multiple Message Detection is enabled catch
2458 -- any Application specific or other unexpected
2459 -- exceptions. Adding appropriate details to the
2460 -- Multiple Message List. Otherwise re-raise the
2461 -- error.
2462 --
2463 rollback to add_pension_types;
2464 if hr_multi_message.unexpected_error_add(l_proc) then
2465 hr_utility.set_location(' Leaving:' || l_proc,40);
2466 raise;
2467 end if;
2468 --
2469 -- Reset IN OUT and set OUT parameters
2470 --
2471 l_return_status := hr_multi_message.get_return_status_disable;
2472 hr_utility.set_location(' Leaving:' || l_proc,50);
2473
2474
2475 END add_pension_types_swi;
2476 -- ----------------------------------------------------------------------------
2477 -- |-------------------< add_provider_ref_number_swi >-------------------------------|
2478 -- ----------------------------------------------------------------------------
2479
2480 procedure add_provider_ref_number_swi
2481 (p_organization_id in number
2482 ,p_business_group_id in number
2483 ,p_legislation_code in varchar2
2484 ,p_org_information_context in varchar2
2485 ,p_provider_reference_number in varchar2
2486 ,p_org_information_id out nocopy number
2487 ) IS
2488
2489 --
2490 -- Variables for API Boolean parameters
2491 l_validate boolean;
2492 --
2493 -- Variables for IN/OUT parameters
2494 --
2495 -- Other variables
2496 l_return_status varchar2(1);
2497 l_proc varchar2(72) := 'add_provider_ref_number_swi';
2498 Begin
2499 hr_utility.set_location(' Entering:' || l_proc,10);
2500 --
2501 -- Issue a savepoint
2502 --
2503 savepoint add_provider_ref_number;
2504 --
2505 -- Initialise Multiple Message Detection
2506 --
2507 hr_multi_message.enable_message_list;
2508 --
2509 -- Remember IN OUT parameter IN values
2510 --
2511 --
2512 -- Convert constant values to their corresponding boolean value
2513 --
2514 l_validate :=
2515 hr_api.constant_to_boolean
2516 (p_constant_value => hr_api.g_false_num);
2517 --
2518 -- Register Surrogate ID or user key values
2519 --
2520 --
2521 -- Call API
2522 --
2523 pqp_pension_providers.add_provider_ref_number
2524 (p_organization_id
2525 ,p_business_group_id
2526 ,p_legislation_code
2527 ,p_org_information_context
2528 ,p_provider_reference_number
2529 ,p_org_information_id
2530 );
2531 --
2532 -- Convert API warning boolean parameter values to specific
2533 -- messages and add them to Multiple Message List
2534 --
2535 --
2536 -- Convert API non-warning boolean parameter values
2537 --
2538 --
2539 -- Derive the API return status value based on whether
2540 -- messages of any type exist in the Multiple Message List.
2541 -- Also disable Multiple Message Detection.
2542 --
2543 l_return_status := hr_multi_message.get_return_status_disable;
2544 hr_utility.set_location(' Leaving:' || l_proc,20);
2545 --
2546 exception
2547 when hr_multi_message.error_message_exist then
2548 --
2549 -- Catch the Multiple Message List exception which
2550 -- indicates API processing has been aborted because
2551 -- at least one message exists in the list.
2552 --
2553 rollback to add_provider_ref_number;
2554 --
2555 -- Reset IN OUT parameters and set OUT parameters
2556 --
2557
2558 hr_utility.set_location(' Leaving:' || l_proc, 30);
2559
2560 when others then
2561 --
2562 -- When Multiple Message Detection is enabled catch
2563 -- any Application specific or other unexpected
2564 -- exceptions. Adding appropriate details to the
2565 -- Multiple Message List. Otherwise re-raise the
2566 -- error.
2567 --
2568 rollback to add_provider_ref_number;
2569 if hr_multi_message.unexpected_error_add(l_proc) then
2570 hr_utility.set_location(' Leaving:' || l_proc,40);
2571 raise;
2572 end if;
2573 --
2574 -- Reset IN OUT and set OUT parameters
2575 --
2576 l_return_status := hr_multi_message.get_return_status_disable;
2577 hr_utility.set_location(' Leaving:' || l_proc,50);
2578
2579
2580 END add_provider_ref_number_swi;
2581
2582
2583
2584
2585 -- ----------------------------------------------------------------------------
2586 -- |-------------------< create_third_party_paymnt_swi >-----------------------|
2587 -- ----------------------------------------------------------------------------
2588
2589 procedure create_third_party_paymnt_swi
2590 (p_organization_id in number
2591 ,p_org_information_id out nocopy number
2592 ) IS
2593
2594 --
2595 -- Variables for API Boolean parameters
2596 l_validate boolean;
2597 --
2598 -- Variables for IN/OUT parameters
2599 --
2600 -- Other variables
2601 l_return_status varchar2(1);
2602 l_proc varchar2(72) := 'create_third_party_paymnt_swi';
2603 Begin
2604 hr_utility.set_location(' Entering:' || l_proc,10);
2605 --
2606 -- Issue a savepoint
2607 --
2608 savepoint create_third_party_paymnt_swi;
2609 --
2610 -- Initialise Multiple Message Detection
2611 --
2612 hr_multi_message.enable_message_list;
2613 --
2614 -- Remember IN OUT parameter IN values
2615 --
2616 --
2617 -- Convert constant values to their corresponding boolean value
2618 --
2619 l_validate :=
2620 hr_api.constant_to_boolean
2621 (p_constant_value => hr_api.g_false_num);
2622 --
2623 -- Register Surrogate ID or user key values
2624 --
2625 --
2626 -- Call API
2627 --
2628 pqp_pension_providers.create_third_party_paymnt
2629 (p_organization_id => p_organization_id
2630 ,p_org_information_id => p_org_information_id
2631 );
2632 --
2633 -- Convert API warning boolean parameter values to specific
2634 -- messages and add them to Multiple Message List
2635 --
2636 --
2637 -- Convert API non-warning boolean parameter values
2638 --
2639 --
2640 -- Derive the API return status value based on whether
2641 -- messages of any type exist in the Multiple Message List.
2642 -- Also disable Multiple Message Detection.
2643 --
2644 l_return_status := hr_multi_message.get_return_status_disable;
2645 hr_utility.set_location(' Leaving:' || l_proc,20);
2646 --
2647 exception
2648 when hr_multi_message.error_message_exist then
2649 --
2650 -- Catch the Multiple Message List exception which
2651 -- indicates API processing has been aborted because
2652 -- at least one message exists in the list.
2653 --
2654 rollback to create_third_party_paymnt_swi;
2655 --
2656 -- Reset IN OUT parameters and set OUT parameters
2657 --
2658
2659 hr_utility.set_location(' Leaving:' || l_proc, 30);
2660
2661 when others then
2662 --
2663 -- When Multiple Message Detection is enabled catch
2664 -- any Application specific or other unexpected
2665 -- exceptions. Adding appropriate details to the
2666 -- Multiple Message List. Otherwise re-raise the
2667 -- error.
2668 --
2669 rollback to create_third_party_paymnt_swi;
2670 if hr_multi_message.unexpected_error_add(l_proc) then
2671 hr_utility.set_location(' Leaving:' || l_proc,40);
2672 raise;
2673 end if;
2674 --
2675 -- Reset IN OUT and set OUT parameters
2676 --
2677 l_return_status := hr_multi_message.get_return_status_disable;
2678 hr_utility.set_location(' Leaving:' || l_proc,50);
2679
2680
2681 END create_third_party_paymnt_swi;
2682
2683 --
2684 -- ----------------------------------------------------------------------------
2685 -- |-------------------< delete_pension_types_swi >----------------------------|
2686 -- ----------------------------------------------------------------------------
2687
2688 procedure delete_pension_types_swi
2689 (p_organization_id in number
2690 ,p_business_group_id in number
2691 ,p_legislation_code in varchar2
2692 ,p_pension_type_id in number
2693 ,p_org_information_id in number
2694 ) IS
2695
2696 --
2697 -- Variables for API Boolean parameters
2698 l_validate boolean;
2699 --
2700 -- Variables for IN/OUT parameters
2701 --
2702 -- Other variables
2703 l_return_status varchar2(1);
2704 l_proc varchar2(72) := 'delete_pension_types';
2705 Begin
2706 hr_utility.set_location(' Entering:' || l_proc,10);
2707 --
2708 -- Issue a savepoint
2709 --
2710 savepoint delete_pension_types;
2711 --
2712 -- Initialise Multiple Message Detection
2713 --
2714 hr_multi_message.enable_message_list;
2715 --
2716 -- Remember IN OUT parameter IN values
2717 --
2718 --
2719 -- Convert constant values to their corresponding boolean value
2720 --
2721 l_validate :=
2722 hr_api.constant_to_boolean
2723 (p_constant_value => hr_api.g_false_num);
2724 --
2725 -- Register Surrogate ID or user key values
2726 --
2727 --
2728 -- Call API
2729 --
2730 pqp_pension_providers.delete_pension_types
2731 (p_organization_id => p_organization_id
2732 ,p_business_group_id => p_business_group_id
2733 ,p_legislation_code => p_legislation_code
2734 ,p_pension_type_id => p_pension_type_id
2735 ,p_org_information_id => p_org_information_id
2736 );
2737 --
2738 -- Convert API warning boolean parameter values to specific
2739 -- messages and add them to Multiple Message List
2740 --
2741 --
2742 -- Convert API non-warning boolean parameter values
2743 --
2744 --
2745 -- Derive the API return status value based on whether
2746 -- messages of any type exist in the Multiple Message List.
2747 -- Also disable Multiple Message Detection.
2748 --
2749 l_return_status := hr_multi_message.get_return_status_disable;
2750 hr_utility.set_location(' Leaving:' || l_proc,20);
2751 --
2752 exception
2753 when hr_multi_message.error_message_exist then
2754 --
2755 -- Catch the Multiple Message List exception which
2756 -- indicates API processing has been aborted because
2757 -- at least one message exists in the list.
2758 --
2759 rollback to delete_pension_types;
2760 --
2761 -- Reset IN OUT parameters and set OUT parameters
2762 --
2763
2764 hr_utility.set_location(' Leaving:' || l_proc, 30);
2765
2766 when others then
2767 --
2768 -- When Multiple Message Detection is enabled catch
2769 -- any Application specific or other unexpected
2770 -- exceptions. Adding appropriate details to the
2771 -- Multiple Message List. Otherwise re-raise the
2772 -- error.
2773 --
2774 rollback to delete_pension_types;
2775 if hr_multi_message.unexpected_error_add(l_proc) then
2776 hr_utility.set_location(' Leaving:' || l_proc,40);
2777 raise;
2778 end if;
2779 --
2780 -- Reset IN OUT and set OUT parameters
2781 --
2782 l_return_status := hr_multi_message.get_return_status_disable;
2783 hr_utility.set_location(' Leaving:' || l_proc,50);
2784
2785 END delete_pension_types_swi;
2786
2787 --
2788 -- ----------------------------------------------------------------------------
2789 -- |-------------------< delete_third_party_paymnt_swi >-----------------------|
2790 -- ----------------------------------------------------------------------------
2791
2792 procedure delete_third_party_paymnt_swi
2793 (p_organization_id in number
2794 ) IS
2795
2796 --
2797 -- Variables for API Boolean parameters
2798 l_validate boolean;
2799 --
2800 -- Variables for IN/OUT parameters
2801 --
2802 -- Other variables
2803 l_return_status varchar2(1);
2804 l_proc varchar2(72) := 'delete_third_party_paymnt_swi';
2805 Begin
2806 hr_utility.set_location(' Entering:' || l_proc,10);
2807 --
2808 -- Issue a savepoint
2809 --
2810 savepoint delete_third_party_paymnt;
2811 --
2812 -- Initialise Multiple Message Detection
2813 --
2814 hr_multi_message.enable_message_list;
2815 --
2816 -- Remember IN OUT parameter IN values
2817 --
2818 --
2819 -- Convert constant values to their corresponding boolean value
2820 --
2821 l_validate :=
2822 hr_api.constant_to_boolean
2823 (p_constant_value => hr_api.g_false_num);
2824 --
2825 -- Register Surrogate ID or user key values
2826 --
2827 --
2828 -- Call API
2829 --
2830 pqp_pension_providers.delete_third_party_paymnt
2831 (p_organization_id => p_organization_id
2832 );
2833 --
2834 -- Convert API warning boolean parameter values to specific
2835 -- messages and add them to Multiple Message List
2836 --
2837 --
2838 -- Convert API non-warning boolean parameter values
2839 --
2840 --
2841 -- Derive the API return status value based on whether
2842 -- messages of any type exist in the Multiple Message List.
2843 -- Also disable Multiple Message Detection.
2844 --
2845 l_return_status := hr_multi_message.get_return_status_disable;
2846 hr_utility.set_location(' Leaving:' || l_proc,20);
2847 --
2848 exception
2849 when hr_multi_message.error_message_exist then
2850 --
2851 -- Catch the Multiple Message List exception which
2852 -- indicates API processing has been aborted because
2853 -- at least one message exists in the list.
2854 --
2855 rollback to delete_third_party_paymnt;
2856 --
2857 -- Reset IN OUT parameters and set OUT parameters
2858 --
2859
2860 hr_utility.set_location(' Leaving:' || l_proc, 30);
2861
2862 when others then
2863 --
2864 -- When Multiple Message Detection is enabled catch
2865 -- any Application specific or other unexpected
2866 -- exceptions. Adding appropriate details to the
2867 -- Multiple Message List. Otherwise re-raise the
2868 -- error.
2869 --
2870 rollback to delete_third_party_paymnt;
2871 if hr_multi_message.unexpected_error_add(l_proc) then
2872 hr_utility.set_location(' Leaving:' || l_proc,40);
2873 raise;
2874 end if;
2875 --
2876 -- Reset IN OUT and set OUT parameters
2877 --
2878 l_return_status := hr_multi_message.get_return_status_disable;
2879 hr_utility.set_location(' Leaving:' || l_proc,50);
2880
2881 END delete_third_party_paymnt_swi;
2882
2883 --
2884 -- ----------------------------------------------------------------------------
2885 -- |-------------------< assign_pension_type_to_org_swi >----------------------|
2886 -- ----------------------------------------------------------------------------
2887 --
2888 procedure assign_pension_type_to_org_swi
2889 ( p_transaction_mode in varchar2
2890 ,p_organization_id in number
2891 ,p_business_group_id in number
2892 ,p_legislation_code in varchar2
2893 ,p_pension_provider_id in number
2894 ,p_pension_type_id in number
2895 ,p_registration_number in varchar2
2896 ,p_start_date date
2897 ,p_end_date date
2898 ,p_org_information_id in out nocopy number
2899 ) Is
2900
2901 --
2902 -- Variables for API Boolean parameters
2903 l_validate boolean;
2904 --
2905 -- Variables for IN/OUT parameters
2906 --
2907 -- Other variables
2908 l_return_status varchar2(1);
2909 l_proc varchar2(72) := 'assign_pension_type_to_org';
2910 Begin
2911
2912 hr_utility.set_location(' Entering:' || l_proc,10);
2913 --
2914 -- Issue a savepoint
2915 --
2916 savepoint assign_pension_type_to_org;
2917 --
2918 -- Initialise Multiple Message Detection
2919 --
2920 hr_multi_message.enable_message_list;
2921 --
2922 -- Remember IN OUT parameter IN values
2923 --
2924 --
2925 -- Convert constant values to their corresponding boolean value
2926 --
2927 l_validate :=
2928 hr_api.constant_to_boolean
2929 (p_constant_value => hr_api.g_false_num);
2930 --
2931 -- Register Surrogate ID or user key values
2932 --
2933 --
2934 -- Call API
2935 --
2936 pqp_pension_providers.assign_pension_type_to_org
2937 ( p_transaction_mode => p_transaction_mode
2938 ,p_organization_id => p_organization_id
2939 ,p_business_group_id => p_business_group_id
2940 ,p_legislation_code => p_legislation_code
2941 ,p_pension_provider_id => p_pension_provider_id
2942 ,p_pension_type_id => p_pension_type_id
2943 ,p_registration_number => p_registration_number
2944 ,p_start_date => p_start_date
2945 ,p_end_date => p_end_date
2946 ,p_org_information_id => p_org_information_id
2947 );
2948 --
2949 -- Convert API warning boolean parameter values to specific
2950 -- messages and add them to Multiple Message List
2951 --
2952 --
2953 -- Convert API non-warning boolean parameter values
2954 --
2955 --
2956 -- Derive the API return status value based on whether
2957 -- messages of any type exist in the Multiple Message List.
2958 -- Also disable Multiple Message Detection.
2959 --
2960 l_return_status := hr_multi_message.get_return_status_disable;
2961 hr_utility.set_location(' Leaving:' || l_proc,20);
2962 --
2963 exception
2964 when hr_multi_message.error_message_exist then
2965 --
2966 -- Catch the Multiple Message List exception which
2967 -- indicates API processing has been aborted because
2968 -- at least one message exists in the list.
2969 --
2970 rollback to assign_pension_type_to_org;
2971 --
2972 -- Reset IN OUT parameters and set OUT parameters
2973 --
2974
2975 hr_utility.set_location(' Leaving:' || l_proc, 30);
2976
2977 when others then
2978 --
2979 -- When Multiple Message Detection is enabled catch
2980 -- any Application specific or other unexpected
2981 -- exceptions. Adding appropriate details to the
2982 -- Multiple Message List. Otherwise re-raise the
2983 -- error.
2984 --
2985 rollback to assign_pension_type_to_org;
2986 if hr_multi_message.unexpected_error_add(l_proc) then
2987 hr_utility.set_location(' Leaving:' || l_proc,40);
2988 raise;
2989 end if;
2990 --
2991 -- Reset IN OUT and set OUT parameters
2992 --
2993 l_return_status := hr_multi_message.get_return_status_disable;
2994 hr_utility.set_location(' Leaving:' || l_proc,50);
2995
2996 End assign_pension_type_to_org_swi;
2997
2998 --
2999 -- ----------------------------------------------------------------------------
3000 -- |-------------------------< delete_assigned_providers_swi >--------------------------|
3001 -- ----------------------------------------------------------------------------
3002 --
3003 procedure delete_assigned_providers_swi
3004 ( p_organization_id in number
3005 ,p_org_information_id in number
3006 ) Is
3007
3008 --
3009 -- Variables for API Boolean parameters
3010 l_validate boolean;
3011 --
3012 -- Variables for IN/OUT parameters
3013 --
3014 -- Other variables
3015 l_return_status varchar2(1);
3016 l_proc varchar2(72) := 'del_assigned_providers_swi';
3017 Begin
3018
3019 hr_utility.set_location(' Entering:' || l_proc,10);
3020 --
3021 -- Issue a savepoint
3022 --
3023 savepoint del_assigned_provider_swi;
3024 --
3025 -- Initialise Multiple Message Detection
3026 --
3027 hr_multi_message.enable_message_list;
3028 --
3029 -- Remember IN OUT parameter IN values
3030 --
3031 --
3032 -- Convert constant values to their corresponding boolean value
3033 --
3034 l_validate :=
3035 hr_api.constant_to_boolean
3036 (p_constant_value => hr_api.g_false_num);
3037 --
3038 -- Register Surrogate ID or user key values
3039 --
3040 --
3041 -- Call API
3042 --
3043 pqp_pension_providers.delete_assigned_providers
3044 (p_organization_id => p_organization_id
3045 ,p_org_information_id => p_org_information_id
3046 );
3047 --
3048 -- Convert API warning boolean parameter values to specific
3049 -- messages and add them to Multiple Message List
3050 --
3051 --
3052 -- Convert API non-warning boolean parameter values
3053 --
3054 --
3055 -- Derive the API return status value based on whether
3056 -- messages of any type exist in the Multiple Message List.
3057 -- Also disable Multiple Message Detection.
3058 --
3059 l_return_status := hr_multi_message.get_return_status_disable;
3060 hr_utility.set_location(' Leaving:' || l_proc,20);
3061 --
3062 exception
3063 when hr_multi_message.error_message_exist then
3064 --
3065 -- Catch the Multiple Message List exception which
3066 -- indicates API processing has been aborted because
3067 -- at least one message exists in the list.
3068 --
3069 rollback to del_assigned_provider_swi;
3070 --
3071 -- Reset IN OUT parameters and set OUT parameters
3072 --
3073
3074 hr_utility.set_location(' Leaving:' || l_proc, 30);
3075
3076 when others then
3077 --
3078 -- When Multiple Message Detection is enabled catch
3079 -- any Application specific or other unexpected
3080 -- exceptions. Adding appropriate details to the
3081 -- Multiple Message List. Otherwise re-raise the
3082 -- error.
3083 --
3084 rollback to del_assigned_provider_swi;
3085 if hr_multi_message.unexpected_error_add(l_proc) then
3086 hr_utility.set_location(' Leaving:' || l_proc,40);
3087 raise;
3088 end if;
3089 --
3090 -- Reset IN OUT and set OUT parameters
3091 --
3092 l_return_status := hr_multi_message.get_return_status_disable;
3093 hr_utility.set_location(' Leaving:' || l_proc,50);
3094
3095 End delete_assigned_providers_swi;
3096
3097 --
3098 -- ----------------------------------------------------------------------------
3099 -- |--------------------------< delete_assigned_providers >----------------------------|
3100 -- ----------------------------------------------------------------------------
3101 --
3102 procedure delete_assigned_providers
3103 ( p_organization_id in number
3104 ,p_org_information_id in number
3105 ) Is
3106
3107 CURSOR csr_org_info IS
3108 Select rowid
3109 From hr_organization_information
3110 Where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
3111 AND organization_id = p_organization_id
3112 AND org_information_id = p_org_information_id;
3113
3114 l_hoi_rowid rowid;
3115
3116 Begin
3117
3118 --first query up the rowid of the org information row
3119 Open csr_org_info;
3120 Fetch csr_org_info INTO l_hoi_rowid;
3121 If csr_org_info%NOTFOUND Then
3122 --
3123 -- Invalid org_information_id, organization_id or combination
3124 -- does not exists for PQP_NL_ER_PENSION_TYPES context
3125 --
3126 Close csr_org_info;
3127 fnd_message.set_name('PQP', 'PQP_230815_INVALID_ORG');
3128 fnd_message.raise_error;
3129 Else
3130 -- found the rowid,now call the delete procedure
3131 Close csr_org_info;
3132 hr_org_information_pkg.delete_row(l_hoi_rowid);
3133 End If;
3134
3135 End delete_assigned_providers;
3136
3137 End PQP_Pension_Providers;
3138