[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PROFESSIONAL_BODY_TEMP
Source
1 PACKAGE BODY pqp_gb_professional_body_temp AS
2 /* $Header: pqgbpbtp.pkb 115.3 2003/10/01 09:01:00 bsamuel noship $ */
3
4 /*========================================================================
5 * CREATE_USER_TEMPLATE
6 *=======================================================================*/
7 FUNCTION create_user_template
8 (p_professional_body_name in varchar2
9 ,p_ele_name in varchar2
10 ,p_ele_reporting_name in varchar2
11 ,p_ele_description in varchar2 default NULL
12 ,p_ele_processing_type in varchar2
13 ,p_ele_third_party_payment in varchar2 default 'Y'
14 ,p_override_amount in varchar2 default 'N'
15 ,p_professional_body_level_bal in varchar2
16 ,p_professional_body_level_yn in varchar2
17 ,p_ele_eff_start_date in date default NULL
18 ,p_ele_eff_end_date in date default NULL
19 ,p_bg_id in number
20 )
21 RETURN NUMBER IS
22 --
23
24
25 /*--------------------------------------------------------------------
26 The input values are explained below : V-varchar2, D-Date, N-number
27 Input-Name Type Valid Values/Explaination
28 ---------- ---- --------------------------------------
29 p_professional_body_name (V) - LOV based i/p
30 p_ele_name (V) - User i/p Element name
31 p_ele_reporting_name (V) - User i/p reporting name
32 p_ele_description (V) - User i/p Description
33 p_ele_processiong_type (V) - 'R'/'N' (Recurring/Non-recurring)
34 p_ele_third_party_payment (V) - 'Y'/'N'
35 p_override_amount (V) - 'Y'/'N'
36 p_professional_body_level_bal (V) - Professional Body Level Balance Name
37 p_professional_body_level_yn (V) - Balance already exists ('Y'/'N')
38 p_ele_eff_start_date (D) - Trunc(start date)
39 p_ele_eff_end_date (D) - Trunc(end date)
40 p_bg_id (N) - Business group id
41 ----------------------------------------------------------------------*/
42 --
43 l_template_id pay_shadow_element_types.template_id%TYPE;
44 l_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
45 l_source_template_id pay_element_templates.template_id%TYPE;
46 l_object_version_number NUMBER(9);
47 l_proc VARCHAR2(80) :=
48 'pqp_gb_professional_body_temp.create_user_template';
49 l_override_amount VARCHAR2(1);
50 l_eei_info_id NUMBER;
51 l_ovn_eei NUMBER;
52 l_element_type_id NUMBER;
53 l_ele_obj_ver_number NUMBER;
54 l_ele_name pay_element_types_f.element_name%TYPE;
55 l_name pay_input_values_f.name%TYPE;
56 i NUMBER;
57
58 l_ele_core_id pay_template_core_objects.core_object_id%TYPE:= -1;
59 l_organization_id NUMBER;
60
61 -- Generic Never to be passed IN
62 l_xx_rowid_id ROWID;
63
64 l_bl_core_id pay_balance_types.balance_type_id%TYPE;
65 l_bf_pbdbal_id pay_shadow_balance_feeds.balance_feed_id%TYPE;
66 l_db_core_id pay_defined_balances.defined_balance_id%TYPE;
67 l_iv_core_id pay_template_core_objects.core_object_id%TYPE;
68 l_dm_baldmn_id pay_balance_dimensions.balance_dimension_id%TYPE;
69
70 -- Extra Information variables
71 l_eei_information3 pay_element_type_extra_info.eei_information3%TYPE;
72 l_eei_information4 pay_element_type_extra_info.eei_information4%TYPE;
73
74 --
75
76 TYPE t_dim IS TABLE OF VARCHAR2(80)
77 INDEX BY BINARY_INTEGER;
78
79 l_dim t_dim;
80
81 CURSOR csr_get_ele_info (c_ele_name varchar2) is
82 SELECT element_type_id
83 ,object_version_number
84 FROM pay_shadow_element_types
85 WHERE template_id = l_template_id
86 AND element_name = c_ele_name;
87
88 --
89 -- cursor to fetch the core element id
90 --
91 CURSOR c5 (c_element_name in varchar2) is
92 SELECT ptco.core_object_id
93 FROM pay_shadow_element_types psbt,
94 pay_template_core_objects ptco
95 WHERE psbt.template_id = l_template_id
96 AND psbt.element_name = c_element_name
97 AND ptco.template_id = psbt.template_id
98 AND ptco.shadow_object_id = psbt.element_type_id
99 AND ptco.core_object_type = 'ET';
100
101 CURSOR csr_get_pb_balid IS
102 SELECT pbt.balance_type_id
103 ,pbt.object_version_number
104 FROM pay_balance_types pbt
105 WHERE pbt.balance_name = p_professional_body_level_bal
106 AND pbt.business_group_id = p_bg_id
107 AND (pbt.legislation_code IS NULL
108 OR
109 pbt.legislation_code = 'GB');
110
111 csr_get_pb_balid_rec csr_get_pb_balid%ROWTYPE;
112
113 CURSOR csr_get_ivid (c_element_type_id NUMBER
114 ,c_inputvalue_name VARCHAR2) IS
115 SELECT siv.input_value_id
116 ,siv.object_version_number
117 FROM pay_shadow_input_values siv
118 WHERE siv.element_type_id = c_element_type_id
119 AND siv.name = c_inputvalue_name;
120
121 csr_get_ivid_rec csr_get_ivid%ROWTYPE;
122
123 CURSOR csr_get_orgid(c_org_name VARCHAR2) IS
124 SELECT hou.organization_id
125 FROM hr_all_organization_units hou
126 WHERE hou.name = c_org_name
127 AND (hou.business_group_id = p_bg_id OR
128 hou.business_group_id is null);
129
130 -- Added cursor to get balance category info
131 CURSOR csr_get_balance_cat_id (c_category_name VARCHAR2)
132 IS
133 SELECT balance_category_id
134 FROM pay_balance_categories_f
135 WHERE category_name = c_category_name
136 AND legislation_code = 'GB'
137 AND p_ele_eff_start_date BETWEEN effective_start_date
138 AND effective_end_date;
139
140 l_balance_category_id NUMBER;
141
142 --
143 --======================================================================
144 -- FUNCTION GET_TEMPLATE_ID
145 --======================================================================
146 FUNCTION get_template_id (p_legislation_code in varchar2 )
147 RETURN number IS
148 --
149 -- l_template_id NUMBER(9);
150 l_template_name VARCHAR2(80);
151 l_proc varchar2(60) := 'pqp_gb_professional_body_temp.get_template_id';
152 --
153 CURSOR csr_get_temp_id is
154 SELECT template_id
155 FROM pay_element_templates
156 WHERE template_name = l_template_name
157 AND legislation_code = p_legislation_code
158 AND template_type = 'T'
159 AND business_group_id is NULL;
160 --
161 BEGIN
162 --
163 hr_utility.set_location('Entering: '||l_proc, 10);
164 --
165 l_template_name := 'PQP PROFESSIONAL BODY';
166 --
167 hr_utility.set_location(l_proc, 20);
168 --
169 for csr_get_temp_id_rec in csr_get_temp_id loop
170 l_template_id := csr_get_temp_id_rec.template_id;
171 end loop;
172 --
173 hr_utility.set_location('Leaving: '||l_proc, 30);
174 --
175 RETURN l_template_id;
176 --
177 END get_template_id;
178
179 -----------------------------------------------------------------------------
180
181 --
182 --=======================================================================
183 -- FUNCTION GET_BALANCE_DIMENSION_ID
184 --=======================================================================
185
186 FUNCTION get_balance_dimension_id (p_dimension_name VARCHAR2)
187 RETURN NUMBER -- Null if the dimension name is not found.
188 IS
189
190 CURSOR csr_id_baldmn IS
191 SELECT balance_dimension_id
192 FROM pay_balance_dimensions
193 WHERE dimension_name = p_dimension_name
194 AND ((business_group_id is null and legislation_code is null)
195 OR
196 (legislation_code is null and business_group_id + 0 =
197 p_bg_id)
198 OR
199 (business_group_id is null and legislation_code = 'GB'));
200
201 l_bd_baldmn_id pay_balance_dimensions.balance_dimension_id%TYPE;
202 l_proc varchar2(80) := 'pqp_gb_professional_body_temp.get_balance_dimension_id';
203
204 BEGIN
205
206 hr_utility.set_location('Entering: '||l_proc, 10);
207 --
208 FOR csr_id_baldmn_rec IN csr_id_baldmn LOOP
209 l_bd_baldmn_id := csr_id_baldmn_rec.balance_dimension_id;
210 END LOOP;
211 --
212 hr_utility.set_location('Leaving: '||l_proc, 20);
213
214 --
215
216 RETURN l_bd_baldmn_id;
217 --
218 END get_balance_dimension_id;
219
220
221 --
222 --=======================================================================
223 -- FUNCTION GET_OBJECT_ID
224 --=======================================================================
225 FUNCTION get_object_id (p_object_type in varchar2
226 ,p_object_name in varchar2
227 ,p_shadow_id in number default null)
228 RETURN NUMBER is
229 --
230 l_object_id NUMBER := NULL;
231 l_proc varchar2(60) := 'pqp_gb_professional_body_temp.get_object_id';
232 --
233 CURSOR csr_get_ele_type_id (c_object_name varchar2) is
234 SELECT element_type_id
235 FROM pay_element_types_f
236 WHERE element_name = c_object_name
237 AND business_group_id = p_bg_id;
238 --
239 CURSOR csr_core_bal_id (c_object_name in varchar2) is
240 SELECT ptco.core_object_id
241 FROM pay_shadow_balance_types psbt,
242 pay_template_core_objects ptco
243 WHERE psbt.template_id = l_template_id
244 AND psbt.balance_name = c_object_name
245 AND ptco.template_id = psbt.template_id
246 AND ptco.shadow_object_id = psbt.balance_type_id;
247
248 --
249 CURSOR csr_core_obj_id is
250 SELECT ptco.core_object_id
251 FROM pay_template_core_objects ptco
252 WHERE ptco.template_id = l_template_id
253 AND ptco.shadow_object_id = p_shadow_id
254 AND ptco.core_object_type = p_object_type;
255 --
256 BEGIN
257
258 hr_utility.set_location('Entering: '||l_proc, 10);
259 --
260 if p_object_type = 'ELE' then
261 for csr_get_ele_type_id_rec in csr_get_ele_type_id (p_object_name) loop
262 l_object_id := csr_get_ele_type_id_rec.element_type_id; -- element id
263 end loop;
264 elsif p_object_type = 'BAL' then
265 for csr_core_bal_id_rec in csr_core_bal_id (p_object_name) loop
266 l_object_id := csr_core_bal_id_rec.core_object_id; -- balance id
267 end loop;
268 else
269 if p_shadow_id is not null then
270 for csr_core_obj_id_rec in csr_core_obj_id loop
271 l_object_id := csr_core_obj_id_rec.core_object_id; -- input value id
272 end loop;
273 end if;
274
275 end if;
276 --
277 hr_utility.set_location('Leaving: '||l_proc, 20);
278 --
279 RETURN l_object_id;
280 --
281 END get_object_id;
282 --
283 --===============================================================================
284 -- MAIN FUNCTION
285 --===============================================================================
286 BEGIN
287
288 hr_utility.set_location('Entering : '||l_proc, 10);
289 ---------------------
290 -- Set session date
291 ---------------------
292
293 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
294 --
295 hr_utility.set_location(l_proc, 20);
296 --
297
298 IF (hr_utility.chk_product_install('Oracle Payroll',g_template_leg_code))
299 THEN
300
301 ---------------------------
302 -- Get Source Template ID
303 ---------------------------
304 l_source_template_id := get_template_id
305 (p_legislation_code => g_template_leg_code
306 );
307
308 /*--------------------------------------------------------------------------
309 Create the user Structure
310 The Configuration Flex segments for the Exclusion Rules are as follows:
311 ---------------------------------------------------------------------------
312 Config1 --
313 Config2 --
314 ---------------------------------------------------------------------------*/
315
316 hr_utility.set_location(l_proc, 30);
317
318 l_override_amount := 'N';
319 i := 0;
320
321 -- Intialize all Extra Information type variables
322 l_eei_information3 := 'N';
323 l_eei_information4 := 'Y';
324
325 -- Check whether an override amount is included
326
327 IF p_override_amount = 'Y' THEN
328 l_override_amount := 'Y';
329 l_eei_information3 := 'Y';
330 END IF; -- End if of override amount check...
331
332 -- Check whether third party payment processing is excluded
333
334 IF p_ele_third_party_payment = 'N' THEN
335 l_eei_information4 := 'N';
336 END IF; -- End if of third party payment check..
337
338 --
339 -- create user structure from the template
340 --
341 pay_element_template_api.create_user_structure
342 (p_validate => false
343 ,p_effective_date => p_ele_eff_start_date
344 ,p_business_group_id => p_bg_id
345 ,p_source_template_id => l_source_template_id
346 ,p_base_name => p_ele_name
347 ,p_configuration_information1 => l_override_amount
348 ,p_template_id => l_template_id
349 ,p_object_version_number => l_object_version_number
350 );
351 --
352
353 hr_utility.set_location(l_proc, 40);
354 ---------------------------------------------------------------------------
355 ---------------------------- Update Shadow Structure ----------------------
356 --
357
358 OPEN csr_get_ele_info(p_ele_name);
359 LOOP
360 FETCH csr_get_ele_info INTO l_element_type_id,l_ele_obj_ver_number;
361 EXIT WHEN csr_get_ele_info%NOTFOUND;
362
363 pay_shadow_element_api.update_shadow_element
364 (p_validate => false
365 ,p_effective_date => p_ele_eff_start_date
366 ,p_element_type_id => l_element_type_id
367 ,p_element_name => p_ele_name
368 ,p_reporting_name => p_ele_reporting_name
369 ,p_description => p_ele_description
370 ,p_processing_type => p_ele_processing_type
371 ,p_third_party_pay_only_flag => p_ele_third_party_payment
372 ,p_object_version_number => l_ele_obj_ver_number
373 );
374
375 END LOOP;
376 CLOSE csr_get_ele_info;
377
378 -------------------------------------------------------------------------
379 --
380 --
381 hr_utility.set_location(l_proc, 50);
382 ---------------------------------------------------------------------------
383 ---------------------------- Generate Core Objects ------------------------
384 ---------------------------------------------------------------------------
385
386 pay_element_template_api.generate_part1
387 (p_validate => false
388 ,p_effective_date => p_ele_eff_start_date
389 ,p_hr_only => false
390 ,p_hr_to_payroll => false
391 ,p_template_id => l_template_id);
392 --
393 hr_utility.set_location(l_proc, 60);
394 --
395 pay_element_template_api.generate_part2
396 (p_validate => false
397 ,p_effective_date => p_ele_eff_start_date
398 ,p_template_id => l_template_id);
399 --
400
401 hr_utility.set_location(l_proc, 70);
402
403 l_ele_core_id := get_object_id (p_object_type => 'ELE'
404 ,p_object_name => p_ele_name
405 );
406
407 hr_utility.set_location(l_proc, 80);
408
409 IF p_professional_body_level_yn = 'N' THEN
410 --
411 -- If this is the first time that the driver is being run for an element
412 -- then create a professional body level balance with the given name and its associated
413 -- feed. All subsequent runs of the driver, for the same professional body, will only
414 -- create the feed.
415 --
416 -- NB This balance will not have a corresponding user structure created.
417 -- This is because, a user may delete the corresponding user structure and
418 -- thus corrupt the feeds created by other runs of the same template.
419 --
420 -- This places an additional requirement on the delete_user_structure
421 -- procedure to detect if the user structure being deleted is the last user
422 -- structure and if so it must then delete the corresponding professional body level
423 -- balance. In all cases the core objects may not be deleted if a payroll
424 -- has been run with the professional body element.
425 --
426 --
427 -- All GB balances should be categorized now
428 -- added this new piece of code to populate category information
429 --
430 l_balance_category_id := NULL;
431 OPEN csr_get_balance_cat_id ('Other Deductions');
432 FETCH csr_get_balance_cat_id INTO l_balance_category_id;
433 CLOSE csr_get_balance_cat_id;
434
435 l_xx_rowid_id := NULL;
436 pay_balance_types_pkg.insert_row
437 (X_Rowid => l_xx_rowid_id -- IN OUT VARCHAR2
438 ,X_Balance_Type_Id => l_bl_core_id -- IN OUT NUMBER
439 ,X_Business_Group_Id => p_bg_id -- NUMBER
440 ,X_Legislation_Code => NULL -- VARCHAR2
441 ,X_Currency_Code => 'GBP' -- VARCHAR2
442 ,X_Assignment_Remuneration_Flag => 'N' -- VARCHAR2
443 ,X_Balance_Name => p_professional_body_level_bal -- VARCHAR2
444 ,X_Base_Balance_Name => p_professional_body_level_bal -- VARCHAR2
445 ,X_Balance_Uom => 'M' -- VARCHAR2
446 ,X_Comments => 'Professional body Level balance for '||
447 p_professional_body_name -- VARCHAR2
448 ,X_Legislation_Subgroup => NULL -- VARCHAR2
449 ,X_Reporting_Name => p_professional_body_level_bal -- VARCHAR2
450 ,X_Attribute_Category => NULL -- VARCHAR2
451 ,X_Attribute1 => NULL -- VARCHAR2
452 ,X_Attribute2 => NULL -- VARCHAR2
453 ,X_Attribute3 => NULL -- VARCHAR2
454 ,X_Attribute4 => NULL -- VARCHAR2
455 ,X_Attribute5 => NULL -- VARCHAR2
456 ,X_Attribute6 => NULL -- VARCHAR2
457 ,X_Attribute7 => NULL -- VARCHAR2
458 ,X_Attribute8 => NULL -- VARCHAR2
459 ,X_Attribute9 => NULL -- VARCHAR2
460 ,X_Attribute10 => NULL -- VARCHAR2
461 ,X_Attribute11 => NULL -- VARCHAR2
462 ,X_Attribute12 => NULL -- VARCHAR2
463 ,X_Attribute13 => NULL -- VARCHAR2
464 ,X_Attribute14 => NULL -- VARCHAR2
465 ,X_Attribute15 => NULL -- VARCHAR2
466 ,X_Attribute16 => NULL -- VARCHAR2
467 ,X_Attribute17 => NULL -- VARCHAR2
468 ,X_Attribute18 => NULL -- VARCHAR2
469 ,X_Attribute19 => NULL -- VARCHAR2
470 ,X_Attribute20 => NULL -- VARCHAR2
471 ,X_balance_category_id => l_balance_category_id
472 );
473
474 hr_utility.set_location(l_proc, 90);
475
476 -- now create the defined balances also for _ASG_RUN/PROC_PTD/STAT_YTD
477
478 l_dim(1) := '_ASG_RUN';
479 l_dim(2) := '_ASG_PROC_PTD';
480 l_dim(3) := '_ASG_STAT_YTD';
481 l_dim(4) := '_PER_TD_YTD';
482
483 FOR i IN 1..l_dim.count LOOP
484
485 l_dm_baldmn_id := get_balance_dimension_id(l_dim(i));
486 l_xx_rowid_id := NULL;
487 l_db_core_id := NULL;
488 pay_defined_balances_pkg.insert_row
489 (x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
490 ,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
491 ,x_business_group_id => p_bg_id -- NUMBER
492 ,x_legislation_code => NULL -- VARCHAR2
493 ,x_balance_type_id => l_bl_core_id -- NUMBER
494 ,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
495 ,x_force_latest_balance_flag => NULL -- VARCHAR2
496 ,x_legislation_subgroup => NULL -- VARCHAR2
497 ,x_grossup_allowed_flag => 'N' -- VARCHAR2
498 );
499
500 END LOOP;
501
502
503 ELSE -- this is not the first run
504
505 -- so query out the core balance type id for the given balance name
506
507 hr_utility.set_location(l_proc, 100);
508
509 OPEN csr_get_pb_balid;
510 FETCH csr_get_pb_balid INTO csr_get_pb_balid_rec;
511 IF csr_get_pb_balid%NOTFOUND THEN
512 --
513 CLOSE csr_get_pb_balid;
514 hr_utility.set_message(8303, 'PQP_230538_PBDBAL_NOT_FOUND');
515 hr_utility.raise_error;
516
517 ELSE
518
519 l_bl_core_id := csr_get_pb_balid_rec.balance_type_id;
520
521 END IF;
522 CLOSE csr_get_pb_balid;
523
524 END IF;
525
526 hr_utility.set_location(l_proc, 110);
527
528 OPEN csr_get_ivid(l_element_type_id, 'Pay Value');
529 FETCH csr_get_ivid INTO csr_get_ivid_rec;
530 CLOSE csr_get_ivid;
531
532 hr_utility.set_location(l_proc, 120);
533
534 l_iv_core_id := get_object_id
535 (p_object_type => 'IV'
536 ,p_object_name => 'Pay Value' -- dummy
537 ,p_shadow_id => csr_get_ivid_rec.input_value_id
538 );
539
540 IF l_iv_core_id IS NULL OR l_ele_core_id IS NULL THEN
541 -- Error Out
542 hr_utility.set_message(8303, 'PQP_230539_PBD_GENERATE_FAILED');
543 hr_utility.raise_error;
544
545 ELSE
546
547 hr_utility.set_location(l_proc, 130);
548
549 l_xx_rowid_id := NULL;
550 pay_balance_feeds_f_pkg.insert_row
551 (x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2,
552 ,x_balance_feed_id => l_bf_pbdbal_id -- IN OUT NUMBER,
553 ,x_effective_start_date => p_ele_eff_start_date -- DATE,
554 ,x_effective_end_date => p_ele_eff_end_date -- DATE,
555 ,x_business_group_id => p_bg_id -- NUMBER,
556 ,x_legislation_code => g_template_leg_code -- VARCHAR2,
557 ,x_balance_type_id => l_bl_core_id -- NUMBER,
558 ,x_input_value_id => l_iv_core_id -- NUMBER,
559 ,x_scale => 1 -- NUMBER,
560 ,x_legislation_subgroup => NULL -- VARCHAR2
561 );
562
563
564 END IF; -- IF any core id is null THEN
565
566 hr_utility.set_location(l_proc, 140);
567
568 --
569 -- Retrieve organization id for the organization name
570 --
571
572 l_organization_id := NULL;
573 OPEN csr_get_orgid (p_professional_body_name);
574 FETCH csr_get_orgid INTO l_organization_id;
575 IF csr_get_orgid%NOTFOUND THEN
576
577 -- Error Out
578 CLOSE csr_get_orgid;
579 hr_utility.set_message(8303, 'PQP_230537_PBD_ORG_NOT_FOUND');
580 hr_utility.raise_error;
581
582 END IF; -- Organization id not found chk...
583 CLOSE csr_get_orgid;
584
585 hr_utility.set_location(l_proc, 160);
586
587 l_base_element_type_id := get_object_id ('ELE', p_ele_name);
588
589 hr_utility.set_location(l_proc, 170);
590
591 -- Create a row in pay_element_extra_info with all the element information
592
593 pay_element_extra_info_api.create_element_extra_info
594 (p_element_type_id => l_base_element_type_id
595 ,p_information_type => 'PQP_PROFESSIONAL_BODY_INFO'
596 ,P_EEI_INFORMATION_CATEGORY => 'PQP_PROFESSIONAL_BODY_INFO'
597 ,p_eei_information1 => TO_CHAR(l_organization_id)
598 -- ,p_eei_information1 => p_professional_body_name
599 ,p_eei_information2 => p_professional_body_level_bal
600 ,p_eei_information3 => l_eei_information3
601 ,p_eei_information4 => l_eei_information4
602 ,p_element_type_extra_info_id => l_eei_info_id
603 ,p_object_version_number => l_ovn_eei);
604
605 ELSE
606
607 hr_utility.set_message(8303, 'PQP_230535_GBORAPAY_NOT_FOUND');
608 hr_utility.raise_error;
609
610
611 END IF; -- IF chk_product_install('Oracle Payroll',g_template_leg_code))
612
613 hr_utility.set_location('Leaving :'||l_proc, 180);
614
615 RETURN l_base_element_type_id;
616
617 --
618 END create_user_template;
619 --
620 --
621 --==========================================================================
622 -- Deletion procedure
623 --==========================================================================
624 --
625 PROCEDURE delete_user_template
626 (p_professional_body_name in varchar2
627 ,p_professional_body_level_bal in varchar2
628 ,p_business_group_id in number
629 ,p_ele_type_id in number
630 ,p_ele_name in varchar2
631 ,p_effective_date in date
632 ) IS
633 --
634 l_template_id NUMBER(9);
635 l_proc varchar2(60) :='pqp_gb_professional_body_temp.delete_user_template';
636 l_eei_info_id number;
637 l_ovn_eei number;
638 l_del_pbd_level_balance_yn varchar2(1) := 'Y';
639 --
640 CURSOR eei is
641 SELECT element_type_extra_info_id
642 FROM pay_element_type_extra_info petei
643 WHERE element_type_id = p_ele_type_id ;
644
645
646 CURSOR csr_get_template_id is
647 SELECT template_id
648 FROM pay_element_templates
649 WHERE base_name = p_ele_name
650 AND business_group_id = p_business_group_id
651 AND template_type = 'U';
652
653 CURSOR csr_get_other_tempid (c_te_usrstr_id NUMBER) IS
654 SELECT usr_others.template_id
655 FROM pay_element_templates usr_this
656 ,pay_element_templates usr_others
657 WHERE usr_this.template_id = c_te_usrstr_id
658 AND usr_others.template_name = usr_this.template_name
659 AND usr_others.template_type = 'U'
660 AND usr_others.template_id <> usr_this.template_id;
661
662 csr_get_other_tempid_rec csr_get_other_tempid%ROWTYPE;
663
664 CURSOR csr_get_orginfo (c_te_usrstr_id NUMBER) IS
665 SELECT TO_NUMBER(peei.eei_information1) pbd_org_id
666 FROM pay_element_templates pets
667 ,pay_shadow_element_types pset
668 ,pay_template_core_objects ptco
669 ,pay_element_type_extra_info peei
670 WHERE pets.template_id = c_te_usrstr_id -- For the given user structure
671 AND pset.template_id = pets.template_id -- find the base element
672 AND pset.element_name = pets.base_name
673 AND ptco.template_id = pset.template_id -- For the base element
674 AND ptco.shadow_object_id = pset.element_type_id -- find the core element
675 AND ptco.core_object_type = 'ET'
676 AND ptco.core_object_id = peei.element_type_id -- For the core element
677 AND peei.information_type = 'PQP_PROFESSIONAL_BODY_INFO' -- find the eei info
678 ;
679
680 csr_get_orginfo_rec csr_get_orginfo%ROWTYPE;
681
682 CURSOR csr_get_orgid (c_pb_orgid NUMBER) IS
683 SELECT horg.organization_id
684 FROM hr_all_organization_units horg
685 WHERE horg.organization_id = c_pb_orgid
686 AND horg.name = p_professional_body_name
687 AND ( horg.business_group_id = p_business_group_id
688 OR horg.business_group_id IS NULL);
689
690 csr_get_orgid_rec csr_get_orgid%ROWTYPE;
691
692 CURSOR csr_get_pb_balid IS
693 SELECT pbts.rowid
694 ,pbts.balance_type_id
695 FROM pay_balance_types pbts
696 WHERE pbts.balance_name = p_professional_body_level_bal
697 AND pbts.business_group_id = p_business_group_id
698 AND pbts.legislation_code IS NULL;
699
700 csr_get_pb_balid_rec csr_get_pb_balid%ROWTYPE;
701
702 --
703 BEGIN
704 --
705 hr_utility.set_location('Entering :'||l_proc, 10);
706
707 --
708 FOR csr_get_template_id_rec IN csr_get_template_id LOOP
709 l_template_id := csr_get_template_id_rec.template_id;
710 END LOOP;
711
712 hr_utility.set_location(l_proc, 20);
713
714 --
715 -- Check to see if there are other user structures for the given template.
716 -- If there are then check to see if they have they belong to the same
717 -- professional body as the one being deleted.
718 --
719 OPEN csr_get_other_tempid(l_template_id);
720 FETCH csr_get_other_tempid INTO csr_get_other_tempid_rec;
721 --
722 -- If no other structures were found this was the last user structure for
723 -- professional body deductions. So don't bother to check the extra element info and
724 -- delete the professional body level balance. If on the other hand more user structures
725 -- were found then loop thru each of them to check if they belong to the
726 -- same professional body.
727 --
728 IF csr_get_other_tempid%FOUND THEN
729 LOOP
730
731 hr_utility.set_location(l_proc, 30);
732
733 OPEN csr_get_orginfo(csr_get_other_tempid_rec.template_id);
734 FETCH csr_get_orginfo INTO csr_get_orginfo_rec;
735 CLOSE csr_get_orginfo;
736
737 hr_utility.set_location(l_proc, 40);
738
739 OPEN csr_get_orgid(csr_get_orginfo_rec.pbd_org_id);
740 FETCH csr_get_orgid INTO csr_get_orgid_rec;
741 IF csr_get_orgid%FOUND THEN
742 CLOSE csr_get_orgid;
743 l_del_pbd_level_balance_yn := 'N';
744 EXIT; -- Even if one more matching user structure exists
745 -- the balance cannot be deleted.
746 END IF;
747 CLOSE csr_get_orgid;
748
749 hr_utility.set_location(l_proc, 50);
750
751 FETCH csr_get_other_tempid INTO csr_get_other_tempid_rec;
752 EXIT WHEN csr_get_other_tempid%NOTFOUND;
753 END LOOP;
754 --
755 END IF;
756 CLOSE csr_get_other_tempid;
757
758 hr_utility.set_location(l_proc, 60);
759
760 IF l_del_pbd_level_balance_yn = 'Y' THEN
761 --
762 -- Delete the professional body level balance also.
763 -- NB This will also delete any dependent feeds and defined balances.
764 --
765 OPEN csr_get_pb_balid;
766 FETCH csr_get_pb_balid INTO csr_get_pb_balid_rec;
767 IF csr_get_pb_balid%NOTFOUND THEN
768 --
769 CLOSE csr_get_pb_balid;
770 hr_utility.set_message(8303, 'PQP_230538_PBDBAL_NOT_FOUND');
771 hr_utility.raise_error;
772 --
773 END IF;
774 CLOSE csr_get_pb_balid;
775
776 hr_utility.set_location(l_proc, 70);
777
778 pay_balance_types_pkg.delete_row
779 (x_rowid => csr_get_pb_balid_rec.rowid -- VARCHAR2
780 ,x_balance_type_id => csr_get_pb_balid_rec.balance_type_id -- NUMBER
781 );
782
783 END IF;
784
785 hr_utility.set_location(l_proc, 80);
786 --
787 OPEN eei;
788 LOOP
789 FETCH eei INTO l_eei_info_id ;
790 EXIT WHEN eei%NOTFOUND;
791
792
793 pay_element_extra_info_api.delete_element_extra_info
794 (p_validate => FALSE
795 ,p_element_type_extra_info_id => l_eei_info_id
796 ,p_object_version_number => l_ovn_eei);
797
798
799 END LOOP;
800 CLOSE eei;
801
802 --
803 hr_utility.set_location(l_proc, 90);
804
805 pay_element_template_api.delete_user_structure
806 (p_validate => false
807 ,p_drop_formula_packages => true
808 ,p_template_id => l_template_id);
809 --
810
811 hr_utility.set_location('Leaving :'||l_proc, 100);
812 --
813 END delete_user_template;
814 --
815 END pqp_gb_professional_body_temp;
816