[Home] [Help]
PACKAGE BODY: APPS.PQP_UK_UNION_TEMPLATE
Source
1 PACKAGE BODY pqp_uk_union_template AS
2 /* $Header: pqgbundt.pkb 115.3 2003/10/01 09:01:37 bsamuel noship $ */
3
4 /*========================================================================
5 * CREATE_USER_TEMPLATE
6 *=======================================================================*/
7
8 g_proc VARCHAR2(31):= 'pqp_uk_union_template.';
9 g_element_extra_info_type pay_element_type_extra_info.information_type%TYPE:=
10 'PQP_UK_UNION_INFO';
11
12 FUNCTION create_user_template
13 (p_frm_union_name IN VARCHAR2
14 ,p_frm_element_name IN VARCHAR2
15 ,p_frm_reporting_name IN VARCHAR2
16 ,p_frm_description IN VARCHAR2 DEFAULT NULL
17 -- ,p_frm_classification IN VARCHAR2
18 ,p_frm_processing_type IN VARCHAR2
19 ,p_frm_override_amount IN VARCHAR2 DEFAULT 'N'
20 ,p_frm_tax_relief IN VARCHAR2 DEFAULT 'N'
21 ,p_frm_supplementary_levy IN VARCHAR2 DEFAULT 'N'
22 ,p_frm_union_level_balance IN VARCHAR2
23 ,p_frm_union_level_balance_yn IN VARCHAR2
24 ,p_frm_rate_type IN VARCHAR2 DEFAULT NULL
25 ,p_frm_fund_list IN VARCHAR2 DEFAULT NULL
26 ,p_frm_effective_start_date IN DATE DEFAULT NULL
27 ,p_frm_effective_end_date IN DATE DEFAULT NULL
28 ,p_frm_business_group_id IN NUMBER
29 )
30 RETURN NUMBER IS -- The union element type core object id
31
32
33
34 /*--------------------------------------------------------------------
35 The input values are explained below : V-varchar2, D-Date, N-number
36 Input-Name Type Valid Values/Explaination
37 ---------- ---- ------------------------------------
38 p_frm_union_name (V) - LOV based i/p Extra Element Info #1
39 p_frm_element_name (V) - User i/p Element name
40 p_frm_reporting_name (V) - User i/p reporting name
41 p_frm_description (V) - User i/p Description
42 -- p_frm_classification (V) - Assumed 'Voluntary Deductions'
43 p_frm_processing_type (V) - 'R'/'N' (Recurring/Non-recurring)
44 p_frm_override_amount (V) - 'Y'es/'N'o Exclusion Rule
45 p_frm_tax_relief (V) - 'Y'es/'N'o Exclusion Rule
46 p_frm_supplementary_levy (V) - 'Y'es/'N'o Exclusion Rule
47 p_frm_union_level_balance (V) - Union level Balance Name
48 p_frm_union_level_balance_yn (V) - 'Y'es/'N'o Exclusion Rule
49 p_frm_rate_type (V) - Extra Element Info #2
50 p_frm_fund_list (V) - Input Value to seed ?
51 p_frm_effective_start_date (D) - Default NULL Effective Start Date
52 p_frm_effective_end_date (D) - Default NULL Effective Start Date
53 p_frm_business_grp_id (N) - Business Group ID
54 ----------------------------------------------------------------------*/
55 l_proc VARCHAR2(61) := g_proc||'create_user_template';
56
57 c_iv_payvlu_nm CONSTANT pay_shadow_input_values.name%TYPE:=
58 'Pay Value';
59 c_iv_fdsltd_nm CONSTANT pay_shadow_input_values.name%TYPE:=
60 'Fund Selected';
61 l_te_usrstr_id pay_element_templates.template_id%TYPE;
62 l_te_source_id pay_element_templates.template_id%TYPE;
63
64 -- Return Value
65 l_el_core_id pay_template_core_objects.core_object_id%TYPE:= -1;
66
67 -- Generic Never to be passed IN
68 l_xx_rowid_id ROWID;
69 l_xx_unnddn_ovn pay_element_templates.object_version_number%TYPE;
70
71
72 l_bl_core_id pay_balance_types.balance_type_id%TYPE;
73 l_db_core_id pay_defined_balances.defined_balance_id%TYPE;
74 l_iv_core_id pay_template_core_objects.core_object_id%TYPE;
75 l_dm_baldmn_id pay_balance_dimensions.balance_dimension_id%TYPE;
76
77
78
79
80
81
82
83
84 l_bl_unnbal_nm pay_shadow_balance_types.balance_name%TYPE;
85 l_bf_unnbal_id pay_shadow_balance_feeds.balance_feed_id%TYPE;
86
87 l_ee_unnddn_id pay_element_type_extra_info.element_type_extra_info_id%TYPE;
88 l_ee_unnorg_id pay_element_type_extra_info.eei_information1%TYPE;
89 l_ee_unnddn_nm pay_element_type_extra_info.eei_information2%TYPE;
90 l_ee_rattyp_nm pay_element_type_extra_info.eei_information3%TYPE;
91
92 l_or_unnddn_id hr_organization_information.organization_id%TYPE;
93 l_oi_unndat_dt hr_organization_information.org_information2%TYPE;
94
95 l_ut_unnudt_nm pay_user_tables.user_table_name%TYPE;
96 l_ut_unnudt_id pay_user_tables.user_table_id%TYPE;
97 l_ut_tbltyp_nm pay_user_tables.range_or_match%TYPE;
98
99
100 l_frm_effective_end_date DATE:=NVL(p_frm_effective_end_date
101 ,TO_DATE('31/12/4712','DD/MM/YYYY'));
102
103 l_ERROR_MESSAGE VARCHAR2(2000);
104
105
106 CURSOR csr_el_unnddn(p_te_unnddn_id NUMBER
107 ,p_el_unnddn_nm VARCHAR2) IS
108 SELECT element_type_id
109 ,object_version_number
110 FROM pay_shadow_element_types
111 WHERE template_id = p_te_unnddn_id
112 AND element_name = p_el_unnddn_nm;
113
114 row_el_unnddn csr_el_unnddn%ROWTYPE;
115
116 CURSOR csr_bl_unnbal IS
117 SELECT pbt.balance_type_id
118 ,pbt.object_version_number
119 FROM pay_balance_types pbt
120 WHERE pbt.balance_name = p_frm_union_level_balance
121 AND pbt.business_group_id = p_frm_business_group_id
122 AND (pbt.legislation_code IS NULL
123 OR
124 pbt.legislation_code = 'GB');
125
126 row_bl_unnbal csr_bl_unnbal%ROWTYPE;
127
128 CURSOR csr_iv_payvlu(p_el_unnddn_id NUMBER
129 ,p_iv_payvlu_nm VARCHAR2) IS
130 SELECT siv.input_value_id
131 ,siv.object_version_number
132 FROM pay_shadow_input_values siv
133 WHERE siv.element_type_id = p_el_unnddn_id
134 AND siv.name = p_iv_payvlu_nm;
135
136
137 row_iv_payvlu csr_iv_payvlu%ROWTYPE;
138
139 CURSOR csr_or_unnorg(p_or_unnorg_nm VARCHAR2
140 ,p_bg_unnddn_id NUMBER ) IS
141 SELECT hou.organization_id
142 FROM hr_all_organization_units hou
143 WHERE hou.name = p_or_unnorg_nm
144 AND hou.business_group_id = p_bg_unnddn_id;
145
146 row_or_unnorg csr_or_unnorg%ROWTYPE;
147
148 -- Added cursor to get balance category info
149 CURSOR csr_get_balance_cat_id (c_category_name VARCHAR2)
150 IS
151 SELECT balance_category_id
152 FROM pay_balance_categories_f
153 WHERE category_name = c_category_name
154 AND legislation_code = 'GB'
155 AND p_frm_effective_start_date BETWEEN effective_start_date
156 AND effective_end_date;
157
158 l_balance_category_id NUMBER;
159
160
161
162 --======================================================================
163 -- FUNCTION GET_TEMPLATE_ID
164 --======================================================================
165 FUNCTION get_template_id (p_legislation_code IN VARCHAR2)
166 RETURN NUMBER IS
167 --
168 l_te_unnddn_id pay_element_templates.template_id%TYPE;
169 l_te_unnddn_nm pay_element_templates.template_name%TYPE;
170 l_proc_nm VARCHAR2(61):= g_proc||'get_template_id';
171 --
172 CURSOR csr_te_unnddn IS
173 SELECT template_id
174 FROM pay_element_templates
175 WHERE template_name = l_te_unnddn_nm
176 AND legislation_code = p_legislation_code
177 AND template_type = 'T'
178 AND business_group_id is NULL;
179 --
180 BEGIN
181 --
182 hr_utility.set_location('Entering: '||l_proc, 10);
183 --
184 l_te_unnddn_nm := 'PQP UNION DEDUCTIONS';
185 --
186 hr_utility.set_location(l_proc, 30);
187 --
188 FOR rec_te_unnddn IN csr_te_unnddn LOOP
189 l_te_unnddn_id := rec_te_unnddn.template_id;
190 END LOOP;
191 --
192 hr_utility.set_location('Leaving: '||l_proc, 100);
193 --
194 RETURN l_te_unnddn_id;
195 --
196 END get_template_id;
197
198
199 PROCEDURE create_table_columns(p_business_group_id NUMBER
200 ,p_ut_unnudt_id NUMBER
201 ,p_fund_list VARCHAR2
202 ) IS
203
204 l_column_rowid VARCHAR2(100);
205 -- l_user_table_id NUMBER;
206 l_user_column_id NUMBER;
207 l_column_exists NUMBER;
208
209 CURSOR c_lookup_values IS
210 SELECT lookup_code
211 ,meaning
212 FROM hr_lookups hrl
213 WHERE hrl.lookup_type = p_fund_list
214 AND hrl.enabled_flag = 'Y';
215
216 -- CURSOR get_user_table_id is
217 -- SELECT to_number(hoi.org_information1)
218 -- FROM hr_all_organization_units hou
219 -- ,hr_organization_information hoi
220 -- WHERE hou.organization_id = hoi.organization_id
221 -- AND org_information_context = 'GB_TRADE_UNION_INFO'
222 -- AND hou.name = p_union_name;
223
224 CURSOR c_column_exists(p_column_name VARCHAR2
225 ,p_user_table_id NUMBER) IS
226 SELECT user_column_id
227 FROM pay_user_columns
228 WHERE user_table_id = p_user_table_id
229 AND user_column_name = p_column_name;
230
231 BEGIN
232
233 -- OPEN c_get_user_table_id;
234 -- FETCH c_get_user_table_id INTO l_user_table_id;
235 -- CLOSE c_get_user_table_id;
236
237
238 FOR l_lookup_value IN c_lookup_values LOOP
239 --
240 OPEN c_column_exists(l_lookup_value.meaning||' Weekly', p_ut_unnudt_id);
241 FETCH c_column_exists into l_column_exists;
242 CLOSE c_column_exists;
243
244 IF l_column_exists IS NULL THEN
245 --
246 pay_user_columns_pkg.insert_row (
247 p_rowid => l_column_rowid
248 ,p_user_column_id => l_user_column_id
249 ,p_user_table_id => p_ut_unnudt_id
250 ,p_business_group_id => p_frm_business_group_id
251 ,p_legislation_code => NULL
252 ,p_legislation_subgroup => NULL
253 ,p_user_column_name => l_lookup_value.meaning||' Weekly'
254 ,p_formula_id => NULL
255 );
256 --
257 END IF;
258
259 l_column_exists := null;
260
261 OPEN c_column_exists(l_lookup_value.meaning||' Monthly'
262 , p_ut_unnudt_id);
263 FETCH c_column_exists INTO l_column_exists;
264 CLOSE c_column_exists;
265
266 IF l_column_exists IS NULL THEN
267 --
268 pay_user_columns_pkg.insert_row (
269 p_rowid => l_column_rowid
270 ,p_user_column_id => l_user_column_id
271 ,p_user_table_id => p_ut_unnudt_id
272 ,p_business_group_id => p_frm_business_group_id
273 ,p_legislation_code => NULL
274 ,p_legislation_subgroup => NULL
275 ,p_user_column_name => l_lookup_value.meaning||' Monthly'
276 ,p_formula_id => NULL
277 );
278 --
279 END IF;
280 --
281 END LOOP;
282 --
283 END create_table_columns;
284
285
286
287 --
288 --=======================================================================
289 -- FUNCTION GET_BALANCE_DIMENSION_ID
290 --=======================================================================
291
292 FUNCTION get_balance_dimension_id (p_dimension_name VARCHAR2)
293 RETURN NUMBER -- Null if the dimension name is not found.
294 IS
295
296 CURSOR csr_id_baldmn IS
297 SELECT balance_dimension_id
298 FROM pay_balance_dimensions
299 WHERE dimension_name = p_dimension_name
300 AND ((business_group_id is null and legislation_code is null)
301 OR
302 (legislation_code is null and business_group_id + 0 =
303 p_frm_business_group_id)
304 OR
305 (business_group_id is null and legislation_code = 'GB'));
306
307 l_bd_baldmn_id pay_balance_dimensions.balance_dimension_id%TYPE;
308
309 BEGIN
310 --
311 FOR csr_id_baldmn_rec IN csr_id_baldmn LOOP
312 l_bd_baldmn_id := csr_id_baldmn_rec.balance_dimension_id;
313 END LOOP;
314
315 RETURN l_bd_baldmn_id;
316 --
317 END get_balance_dimension_id;
318
319
320 --
321 --=======================================================================
322 -- FUNCTION GET_OBJECT_ID
323 --=======================================================================
324
325
326
327 FUNCTION get_object_id (p_object_type in varchar2,
328 p_object_name in varchar2,
329 p_shadow_id in number default null,
330 p_template_id in number default null)
331 RETURN NUMBER is
332 --
333 l_xx_object_id NUMBER:= NULL;
334 l_proc VARCHAR2(61):= g_proc||'get_object_id';
335 --
336 CURSOR csr_el_payele(p_xx_object_nm VARCHAR2) IS
337 SELECT element_type_id
338 FROM pay_element_types_f
339 WHERE element_name = p_xx_object_nm
340 AND business_group_id = p_frm_business_group_id;
341 --
342 CURSOR csr_bl_coreobj(p_xx_object_nm VARCHAR2) IS
343 SELECT ptco.core_object_id
344 FROM pay_shadow_balance_types psbt,
345 pay_template_core_objects ptco
346 WHERE psbt.template_id = l_te_usrstr_id
347 AND psbt.balance_name = p_xx_object_nm
348 AND ptco.template_id = psbt.template_id
349 AND ptco.shadow_object_id = psbt.balance_type_id;
350 --
351 CURSOR csr_id_coreobj IS
352 SELECT ptco.core_object_id
353 FROM pay_template_core_objects ptco
354 WHERE ptco.template_id = NVL(p_template_id,l_te_usrstr_id)
355 AND ptco.shadow_object_id = p_shadow_id
356 AND ptco.core_object_type = p_object_type;
357 --
358 BEGIN
359 hr_utility.set_location('Entering: '||l_proc, 10);
360 --
361 IF p_object_type = 'ELE' THEN
362 FOR rec_el_payele IN csr_el_payele(p_object_name) LOOP
363 l_xx_object_id := rec_el_payele.element_type_id; -- element id
364 END LOOP;
365 ELSIF p_object_type = 'BAL' THEN
366 FOR rec_bl_coreobj IN csr_bl_coreobj(p_object_name) LOOP
367 l_xx_object_id := rec_bl_coreobj.core_object_id; -- balance id
368 END LOOP;
369 ELSE
370 IF p_shadow_id IS NOT NULL THEN
371 FOR rec_id_coreobj IN csr_id_coreobj LOOP
372 l_xx_object_id := rec_id_coreobj.core_object_id;
373 END LOOP;
374 END IF;
375 END IF;
376 --
377 hr_utility.set_location('Leaving: '||l_proc, 50);
378 --
379 RETURN l_xx_object_id;
380 --
381 END get_object_id;
382 --
383 --=============================================================================
384 -- MAIN FUNCTION
385 --=============================================================================
386 BEGIN
387
388 hr_utility.set_location('Entering : '||l_proc, 10);
389 ---------------------
390 -- Set session date
391 ---------------------
392
393 pay_db_pay_setup.set_session_date(nvl(p_frm_effective_start_date, sysdate));
394 --
395 hr_utility.set_location(l_proc, 20);
396
397 IF (hr_utility.chk_product_install('Oracle Payroll',g_template_leg_code))
398 THEN
399
400 ---------------------------
401 -- Get Source Template ID
402 ---------------------------
403 l_te_source_id := get_template_id
404 (p_legislation_code => g_template_leg_code);
405
406 hr_utility.set_location(l_proc, 30);
407
408
409 /*--------------------------------------------------------------------
410 The input values are explained below : V-varchar2, D-Date, N-number
411 Input-Name Type Valid Values/Explaination
412 ---------- ---- ------------------------------------
413 p_frm_union_name (V) - LOV based i/p Extra Element Info #1
414 p_element_name (V) - User i/p Element name
415 p_reporting_name (V) - User i/p reporting name
416 p_description (V) - User i/p Description
417 -- p_classification (V) - Assumed 'Voluntary Deductions'
418 p_processing_type (V) - 'R'/'N' (Recurring/Non-recurring)
419 p_override_amount (V) - 'Y'es/'N'o Exclusion Rule
420 p_tax_relief (V) - 'Y'es/'N'o Exclusion Rule
421 p_supplementary_levy (V) - 'Y'es/'N'o Exclusion Rule
422 p_frm_union_level_balance (V) - Union level Balance Name
423 p_frm_union_level_balance_yn (V) - 'Y'es/'N'o Exclusion Rule
424 p_rate_type (V) - Extra Element Info #2
425 p_fund_list (V) - Input Value to seed ?
426 p_effective_start_date (D) - Default NULL Effective Start Date
427 p_effective_end_date (D) - Default NULL Effective Start Date
428 p_business_group_id (N) - Business Group ID
429 ----------------------------------------------------------------------*/
430
431
432 -------------------------------------------------------------------------
433 ------------ create user structure from the template --------------------
434 -------------------------------------------------------------------------
435 pay_element_template_api.create_user_structure
436 (p_validate => false
437 ,p_effective_date => p_frm_effective_start_date
438 ,p_business_group_id => p_frm_business_group_id
439 ,p_source_template_id => l_te_source_id
440 ,p_base_name => p_frm_element_name
441 ,p_configuration_information1 => p_frm_override_amount
442 ,p_configuration_information2 => p_frm_tax_relief
443 ,p_configuration_information3 => p_frm_supplementary_levy
444 -- ,p_configuration_information4 => p_frm_union_level_balance_yn
445 ,p_template_id => l_te_usrstr_id
446 ,p_object_version_number => l_xx_unnddn_ovn
447 );
448
449 hr_utility.set_location(l_proc, 40);
450
451
452
453
454 ------------------------------------------------------------------------
455 ------------------------- Update Shadow Structure ------------------------
456 ---------------------------------------------------------------------------
457
458 -- Update the user choice of Recurring or Non-Recurring processing type
459
460 OPEN csr_el_unnddn(l_te_usrstr_id, p_frm_element_name); -- <BASENAME>
461 LOOP
462 FETCH csr_el_unnddn INTO row_el_unnddn;
463 EXIT WHEN csr_el_unnddn%NOTFOUND;
464
465 pay_shadow_element_api.update_shadow_element
466 (p_validate => false
467 ,p_effective_date => p_frm_effective_start_date
468 ,p_element_type_id => row_el_unnddn.element_type_id
469 ,p_element_name => p_frm_element_name
470 ,p_description => p_frm_description
471 ,p_object_version_number => row_el_unnddn.object_version_number
472 ,p_processing_type => p_frm_processing_type
473 ,p_reporting_name => p_frm_reporting_name
474 );
475
476 END LOOP;
477 CLOSE csr_el_unnddn;
478
479
480 -- Update the fund list lookup type
481
482 OPEN csr_iv_payvlu(row_el_unnddn.element_type_id, c_iv_fdsltd_nm);
483 FETCH csr_iv_payvlu INTO row_iv_payvlu;
484 -- IF csr_iv_payvlu%NOTFOUND THEN
485 -- --
486 -- --Error Out
487 -- hr_utility.set_message(8303, 'PQP_UNNTEST_FUNDIVLU_NOT_FOUND');
488 -- hr_utility.raise_error;
489 --
490 -- ELSE
491
492 pay_siv_upd.upd
493 (p_effective_date => p_frm_effective_start_date
494 ,p_input_value_id => row_iv_payvlu.input_value_id
495 -- ,p_element_type_id => --in number default hr_api.g_number
496 -- ,p_display_sequence => --in number default hr_api.g_number
497 -- ,p_generate_db_items_flag => --in varchar2 default hr_api.g_varc
498 -- ,p_hot_default_flag => --in varchar2 default hr_api.g_varc
499 -- ,p_mandatory_flag => --in varchar2 default hr_api.g_varc
500 -- ,p_name => --in varchar2 default hr_api.g_varc
501 -- ,p_uom => --in varchar2 default hr_api.g_varc
502 ,p_lookup_type => p_frm_fund_list
503 ,p_default_value => NULL
504 -- ,p_max_value => --in varchar2 default hr_api.g_varc
505 -- ,p_min_value => --in varchar2 default hr_api.g_varc
506 -- ,p_warning_or_error => --in varchar2 default hr_api.g_varc
507 -- ,p_default_value_column => --in varchar2 default hr_api.g_varc
508 -- ,p_exclusion_rule_id => --in number default hr_api.g_number
509 ,p_object_version_number => l_xx_unnddn_ovn
510 );
511 -- END IF; /* IF csr_iv_payvlu%NOTFOUND THEN */
512 CLOSE csr_iv_payvlu;
513
514 hr_utility.set_location(l_proc, 50);
515
516
517
518 ---------------------------------------------------------------------------
519 ---------------------------- Generate Core Objects ------------------------
520 ---------------------------------------------------------------------------
521
522 pay_element_template_api.generate_part1
523 (p_validate => FALSE
524 ,p_effective_date => p_frm_effective_start_date
525 ,p_hr_only => FALSE
526 ,p_hr_to_payroll => FALSE
527 ,p_template_id => l_te_usrstr_id);
528
529 hr_utility.set_location(l_proc, 60);
530
531 pay_element_template_api.generate_part2
532 (p_validate => FALSE
533 ,p_effective_date => p_frm_effective_start_date
534 ,p_template_id => l_te_usrstr_id);
535
536 hr_utility.set_location(l_proc, 70);
537
538
539 l_el_core_id := get_object_id (p_object_type => 'ELE'
540 ,p_object_name => p_frm_element_name
541 );
542
543
544
545 IF p_frm_union_level_balance_yn = 'N' THEN
546 --
547 -- If this is the first time that the driver is being run for a driver
548 -- then create a union level balance with the given name and its associated
549 -- feed. All subsequent runs of the driver, for the same union, will only
550 -- create the feed.
551 --
552 -- NB This balance will not have a corresponding user structure created.
553 -- This is because, a user may delete the corresponding user structure and
554 -- thus corrupt the feeds created by other runs of the same template.
555 --
556 -- This places an additional requirement on the delete_user_structure
557 -- procedure to detect if the user structure being deleted is the last user
558 -- structure and if so it must then delete the corresponding union level
559 -- balance.In all cases the core objects may not be deleted if a payroll
560 -- has been run with the union element.
561 --
562 --
563 -- All GB balances should be categorized now
564 -- added this new piece of code to populate category information
565 --
566 l_balance_category_id := NULL;
567 OPEN csr_get_balance_cat_id ('Other Deductions');
568 FETCH csr_get_balance_cat_id INTO l_balance_category_id;
569 CLOSE csr_get_balance_cat_id;
570
571 l_xx_rowid_id := NULL;
572 pay_balance_types_pkg.insert_row
573 (X_Rowid => l_xx_rowid_id -- IN OUT VARCHAR2
574 ,X_Balance_Type_Id => l_bl_core_id -- IN OUT NUMBER
575 ,X_Business_Group_Id => p_frm_business_group_id -- NUMBER
576 ,X_Legislation_Code => NULL -- VARCHAR2
577 ,X_Currency_Code => 'GBP' -- VARCHAR2
578 ,X_Assignment_Remuneration_Flag => 'N' -- VARCHAR2
579 ,X_Balance_Name => p_frm_union_level_balance --VARCHAR2
580 ,X_Base_Balance_Name => p_frm_union_level_balance --VARCHAR2
581 ,X_Balance_Uom => 'M' --VARCHAR2
582 ,X_Comments => 'Union level balance for '||
583 p_frm_union_name -- VARCHAR2
584 ,X_Legislation_Subgroup => NULL -- VARCHAR2
585 ,X_Reporting_Name => p_frm_union_level_balance --VARCHAR2
586 ,X_Attribute_Category => NULL -- VARCHAR2
587 ,X_Attribute1 => NULL -- VARCHAR2
588 ,X_Attribute2 => NULL -- VARCHAR2
589 ,X_Attribute3 => NULL -- VARCHAR2
590 ,X_Attribute4 => NULL -- VARCHAR2
591 ,X_Attribute5 => NULL -- VARCHAR2
592 ,X_Attribute6 => NULL -- VARCHAR2
593 ,X_Attribute7 => NULL -- VARCHAR2
594 ,X_Attribute8 => NULL -- VARCHAR2
595 ,X_Attribute9 => NULL -- VARCHAR2
596 ,X_Attribute10 => NULL -- VARCHAR2
597 ,X_Attribute11 => NULL -- VARCHAR2
598 ,X_Attribute12 => NULL -- VARCHAR2
599 ,X_Attribute13 => NULL -- VARCHAR2
600 ,X_Attribute14 => NULL -- VARCHAR2
601 ,X_Attribute15 => NULL -- VARCHAR2
602 ,X_Attribute16 => NULL -- VARCHAR2
603 ,X_Attribute17 => NULL -- VARCHAR2
604 ,X_Attribute18 => NULL -- VARCHAR2
605 ,X_Attribute19 => NULL -- VARCHAR2
606 ,X_Attribute20 => NULL -- VARCHAR2
607 ,X_balance_category_id => l_balance_category_id
608 );
609
610 -- now create the defined balances also for _ASG_RUN/PROC_PTD/STAT_YTD
611
612 l_dm_baldmn_id := get_balance_dimension_id('_ASG_RUN');
613 l_xx_rowid_id := NULL;
614 l_db_core_id := NULL;
615 pay_defined_balances_pkg.insert_row
616 (x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
617 ,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
618 ,x_business_group_id => p_frm_business_group_id --NUMBER
619 ,x_legislation_code => NULL -- VARCHAR2
620 ,x_balance_type_id => l_bl_core_id -- NUMBER
621 ,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
622 ,x_force_latest_balance_flag => NULL -- VARCHAR2
623 ,x_legislation_subgroup => NULL -- VARCHAR2
624 ,x_grossup_allowed_flag => 'N' -- VARCHAR2
625 );
626
627 l_dm_baldmn_id := get_balance_dimension_id('_ASG_PROC_PTD');
628 l_xx_rowid_id := NULL;
629 l_db_core_id := NULL;
630 pay_defined_balances_pkg.insert_row
631 (x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
632 ,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
633 ,x_business_group_id => p_frm_business_group_id --NUMBER
634 ,x_legislation_code => NULL -- VARCHAR2
635 ,x_balance_type_id => l_bl_core_id -- NUMBER
636 ,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
637 ,x_force_latest_balance_flag => NULL -- VARCHAR2
638 ,x_legislation_subgroup => NULL -- VARCHAR2
639 ,x_grossup_allowed_flag => 'N' -- VARCHAR2
640 );
641
642 l_dm_baldmn_id := get_balance_dimension_id('_ASG_STAT_YTD');
643 l_xx_rowid_id := NULL;
644 l_db_core_id := NULL;
645 pay_defined_balances_pkg.insert_row
646 (x_rowid => l_xx_rowid_id -- IN OUT VARCHAR2
647 ,x_defined_balance_id => l_db_core_id -- IN OUT NUMBER
648 ,x_business_group_id => p_frm_business_group_id --NUMBER
649 ,x_legislation_code => NULL -- VARCHAR2
650 ,x_balance_type_id => l_bl_core_id -- NUMBER
651 ,x_balance_dimension_id => l_dm_baldmn_id -- NUMBER
652 ,x_force_latest_balance_flag => NULL -- VARCHAR2
653 ,x_legislation_subgroup => NULL -- VARCHAR2
654 ,x_grossup_allowed_flag => 'N' -- VARCHAR2
655 );
656
657 ELSE -- this is not the first run
658 -- so query out the core balance type id for the given balance name
659
660 OPEN csr_bl_unnbal;
661 FETCH csr_bl_unnbal INTO row_bl_unnbal;
662 IF csr_bl_unnbal%NOTFOUND THEN
663 --
664 hr_utility.set_message(8303, 'PQP_230532_UNNBAL_NOT_FOUND');
665 hr_utility.raise_error;
666 ELSE
667
668 l_bl_core_id := row_bl_unnbal.balance_type_id;
669
670 END IF;
671 CLOSE csr_bl_unnbal;
672
673 END IF;
674
675
676 OPEN csr_iv_payvlu(row_el_unnddn.element_type_id, c_iv_payvlu_nm);
677 FETCH csr_iv_payvlu INTO row_iv_payvlu;
678 -- IF csr_iv_payvlu%NOTFOUND THEN
679 -- --
680 -- hr_utility.set_message(8303, 'PQP_UNNTEST_PAYIVLU_NOT_FOUND');
681 -- hr_utility.raise_error;
682 -- END IF;
683 CLOSE csr_iv_payvlu;
684
685
686 l_iv_core_id := get_object_id
687 (p_object_type => 'IV'
688 ,p_object_name => 'Pay Value' -- dummy
689 ,p_shadow_id => row_iv_payvlu.input_value_id
690 );
691
692
693
694 IF l_iv_core_id IS NULL OR l_el_core_id IS NULL THEN
695 -- Error Out
696 hr_utility.set_message(8303, 'PQP_230533_GENERATE_PART_FAIL');
697 hr_utility.raise_error;
698
699 ELSE
700 l_xx_rowid_id := NULL;
701 pay_balance_feeds_f_pkg.insert_row
702 (x_rowid => l_xx_rowid_id --IN OUT VARCHAR2,
703 ,x_balance_feed_id => l_bf_unnbal_id --IN OUT NUMBER,
704 ,x_effective_start_date => p_frm_effective_start_date -- DATE,
705 ,x_effective_end_date => l_frm_effective_end_date -- DATE,
706 ,x_business_group_id => p_frm_business_group_id -- NUMBER,
707 ,x_legislation_code => g_template_leg_code -- VARCHAR2,
708 ,x_balance_type_id => l_bl_core_id -- NUMBER,
709 ,x_input_value_id => l_iv_core_id -- NUMBER,
710 ,x_scale => 1 -- NUMBER,
711 ,x_legislation_subgroup => NULL -- VARCHAR2
712 );
713
714
715 END IF; -- IF any core id is null THEN
716
717
718
719
720 ---------------------------------------------------------------------------
721 ---------------------------- Update Core Objects ------------------------
722 ---------------------------------------------------------------------------
723
724 -- Update input value Fund_Selected with the lookup type passed as Fund List
725
726
727
728 OPEN csr_or_unnorg(p_frm_union_name, p_frm_business_group_id);
729 FETCH csr_or_unnorg INTO row_or_unnorg;
730 -- IF csr_or_unnorg%NOTFOUND THEN
731 -- -- Error out, the union does not exist as a organization for the
732 -- -- given business group.
733 -- hr_utility.set_message(8303, 'PQP_UNNTEST_UNNORG_NOT_FOUND');
734 -- hr_utility.raise_error;
735 -- --
736 -- END IF;
737 CLOSE csr_or_unnorg;
738
739
740 --
741 -- Extract the Union Rates Table Name/Id and Type from Organisation
742 -- Information flexfields.If it has not been setup untill now then
743 -- error out.
744 --
745
746 IF pqp_uk_union_deduction.get_uk_union_org_info
747 (p_union_organization_id => row_or_unnorg.organization_id -- IN
748 ,p_union_rates_table_id => l_ut_unnudt_id -- OUT NUMBER
749 ,p_union_rates_table_name => l_ut_unnudt_nm -- OUT VARCHAR2
750 ,p_union_rates_table_type => l_ut_tbltyp_nm -- OUT VARCHAR2
751 ,p_union_recalculation_date => l_oi_unndat_dt -- OUT VARCHAR2
752 ,p_ERROR_MESSAGE => l_ERROR_MESSAGE -- OUT VARCHAR2
753 ) <> 0 THEN
754 --
755 -- Error Out
756 hr_utility.set_message(8303, 'PQP_230534_ORGINFO_NOT_FOUND');
757 hr_utility.raise_error;
758 --
759 ELSE
760
761 create_table_columns
762 (p_business_group_id => p_frm_business_group_id -- NUMBER
763 ,p_ut_unnudt_id => l_ut_unnudt_id -- NUMBER
764 ,p_fund_list => p_frm_fund_list -- VARCHAR2
765 );
766
767 END IF;
768
769
770 pay_element_extra_info_api.create_element_extra_info
771 (p_element_type_id => l_el_core_id
772 ,p_information_type => g_element_extra_info_type
773 ,p_eei_information_category => g_element_extra_info_type
774 ,p_eei_information1 => TO_CHAR(row_or_unnorg.organization_id)
775 ,p_eei_information2 => p_frm_union_level_balance
776 ,p_eei_information3 => p_frm_rate_type
777 ,p_eei_information4 => p_frm_fund_list
778 ,p_element_type_extra_info_id => l_ee_unnddn_id
779 ,p_object_version_number => l_xx_unnddn_ovn);
780
781
782 ELSE
783
784 hr_utility.set_message(8303, 'PQP_230535_GBORAPAY_NOT_FOUND');
785 hr_utility.raise_error;
786
787
788 END IF; -- IF chk_product_install('Oracle Payroll',g_template_leg_code))
789
790 RETURN l_el_core_id;
791
792 --
793 END create_user_template;
794 --
795 --
796 --==========================================================================
797 -- Deletion procedure
798 --==========================================================================
799 --
800 PROCEDURE delete_user_template
801 (p_frm_union_name IN VARCHAR2
802 ,p_frm_union_level_balance IN VARCHAR2
803 ,p_frm_element_type_id IN NUMBER
804 ,p_frm_element_name IN VARCHAR2
805 ,p_frm_business_group_id IN NUMBER
806 ,p_frm_effective_date IN DATE
807 ) IS
808 --
809 l_proc VARCHAR2(61):= g_proc||'delete_user_template';
810 l_te_unnddn_id pay_element_templates.template_id%TYPE;
811
812 l_ee_unnddn_id pay_element_type_extra_info.element_type_extra_info_id%TYPE;
813 l_ee_unnddn_ovn pay_element_type_extra_info.object_version_number%TYPE;
814
815
816 l_del_union_level_balance_yn VARCHAR2(1):= 'Y'; --Default delete the balance
817 --
818 CURSOR csr_ee_unnddn IS
819 SELECT element_type_extra_info_id
820 FROM pay_element_type_extra_info petei
821 WHERE element_type_id = p_frm_element_type_id;
822
823
824 CURSOR csr_te_unnddn IS
825 SELECT template_id
826 FROM pay_element_templates
827 WHERE base_name = p_frm_element_name
828 AND business_group_id = p_frm_business_group_id
829 AND template_type = 'U';
830
831
832 CURSOR csr_te_others (p_te_usrstr_id NUMBER) IS
833 SELECT usr_others.template_id
834 FROM pay_element_templates usr_this
835 ,pay_element_templates usr_others
836 WHERE usr_this.template_id = p_te_usrstr_id
837 AND usr_others.template_name = usr_this.template_name
838 AND usr_others.template_type = 'U'
839 AND usr_others.template_id <> usr_this.template_id;
840
841 row_te_others csr_te_others%ROWTYPE;
842
843 CURSOR csr_ee_unionm (p_te_usrstr_id NUMBER) IS
844 SELECT TO_NUMBER(peei.eei_information1) union_org_id
845 FROM pay_element_templates pets
846 ,pay_shadow_element_types pset
847 ,pay_template_core_objects ptco
848 ,pay_element_type_extra_info peei
849 -- ,hr_all_organization_units horg
850 WHERE pets.template_id = p_te_usrstr_id -- For the given user structure
851 AND pset.template_id = pets.template_id -- find the base element
852 AND pset.element_name = pets.base_name
853 AND ptco.template_id = pset.template_id -- For the base element
854 AND ptco.shadow_object_id = pset.element_type_id -- find the core element
855 AND ptco.core_object_type = 'ET'
856 AND ptco.core_object_id = peei.element_type_id -- For the core element
857 AND peei.information_type = g_element_extra_info_type -- find the eei info
858 -- AND horg.organization_id = TO_NUMBER(peei.eei_information1)
859 -- AND horg.name = p_frm_union_name
860 ;
861
862 row_ee_unionm csr_ee_unionm%ROWTYPE;
863 --
864 -- The above cursor had to be split into two bcos of the invalid number error
865 -- while joining eei to org
866 --
867
868 CURSOR csr_or_unionm (p_or_unnorg_id NUMBER) IS
869 SELECT horg.organization_id
870 FROM hr_all_organization_units horg
871 WHERE horg.organization_id = p_or_unnorg_id
872 AND horg.name = p_frm_union_name
873 AND ( horg.business_group_id = p_frm_business_group_id
874 OR horg.business_group_id IS NULL);
875
876 row_or_unionm csr_or_unionm%ROWTYPE;
877
878
879
880 CURSOR csr_bt_unnbal IS
881 SELECT pbts.rowid
882 ,pbts.balance_type_id
883 FROM pay_balance_types pbts
884 WHERE pbts.balance_name = p_frm_union_level_balance
885 AND pbts.business_group_id = p_frm_business_group_id
886 AND pbts.legislation_code IS NULL;
887
888 row_bt_unnbal csr_bt_unnbal%ROWTYPE;
889
890
891 --
892 BEGIN
893 --
894 hr_utility.set_location('Entering :'||l_proc, 10);
895 --
896 FOR csr_te_unnddn_rec IN csr_te_unnddn LOOP
897 l_te_unnddn_id := csr_te_unnddn_rec.template_id;
898 END LOOP;
899 --
900 -- Check to see if there are other user structures for the given template.
901 -- If there are then check to see if they have they belong to the same
902 -- union as the one being deleted.
903 --
904 OPEN csr_te_others(l_te_unnddn_id);
905 FETCH csr_te_others INTO row_te_others;
906 --
907 -- If no other structures were found this was the last user structure for
908 -- union deductions. So don't bother to check the extra element info and
909 -- delete the union level balance.If on the other hand more user structures
910 -- were found then loop thru each of them to check if they belong to the
911 -- same union.
912 --
913 IF csr_te_others%FOUND THEN
914 LOOP
915 OPEN csr_ee_unionm(row_te_others.template_id);
916 FETCH csr_ee_unionm INTO row_ee_unionm;
917 CLOSE csr_ee_unionm;
918 OPEN csr_or_unionm(row_ee_unionm.union_org_id);
919 FETCH csr_or_unionm INTO row_or_unionm;
920 IF csr_or_unionm%FOUND THEN
921 CLOSE csr_or_unionm;
922 l_del_union_level_balance_yn := 'N';
923 EXIT; -- Even if one more matching user structure exists
924 -- the balance cannot be deleted.
925 END IF;
926 CLOSE csr_or_unionm;
927 FETCH csr_te_others INTO row_te_others;
928 EXIT WHEN csr_te_others%NOTFOUND;
929 END LOOP;
930 --
931 END IF;
932 CLOSE csr_te_others;
933
934
935 IF l_del_union_level_balance_yn = 'Y' THEN
936 --
937 -- Delete the union level balance also.
938 -- NB This will also delete any dependent feeds and defined balances.
939 --
940 OPEN csr_bt_unnbal;
941 FETCH csr_bt_unnbal INTO row_bt_unnbal;
942 IF csr_bt_unnbal%NOTFOUND THEN
943 --
944 hr_utility.set_message(8303, 'PQP_230532_UNNBAL_NOT_FOUND');
945 hr_utility.raise_error;
946 --
947 END IF;
948 CLOSE csr_bt_unnbal;
949
950 pay_balance_types_pkg.delete_row
951 (x_rowid => row_bt_unnbal.rowid -- VARCHAR2
952 ,x_balance_type_id => row_bt_unnbal.balance_type_id -- NUMBER
953 );
954
955 END IF;
956
957
958 OPEN csr_ee_unnddn;
959 LOOP
960 FETCH csr_ee_unnddn INTO l_ee_unnddn_id;
961 EXIT WHEN csr_ee_unnddn%NOTFOUND;
962
963 pay_element_extra_info_api.delete_element_extra_info
964 (p_validate => FALSE
965 ,p_element_type_extra_info_id => l_ee_unnddn_id
966 ,p_object_version_number => l_ee_unnddn_ovn);
967
968 END LOOP;
969 CLOSE csr_ee_unnddn;
970
971
972 pay_element_template_api.delete_user_structure
973 (p_validate => FALSE
974 ,p_drop_formula_packages => TRUE
975 ,p_template_id => l_te_unnddn_id);
976
977
978 hr_utility.set_location('Leaving :'||l_proc, 50);
979 --
980 END delete_user_template;
981 --
982 END pqp_uk_union_template ;