1 PACKAGE BODY hr_balances AS
2 /* $Header: pybalnce.pkb 120.1 2005/08/31 08:39:23 susivasu noship $ */
3 --
4 /*
5 /*
6
7 ******************************************************************
8 * *
9 * Copyright (C) 1989 Oracle Corporation UK Ltd., *
10 * Richmond, England. *
11 * *
12 * All rights reserved. *
13 * *
14 * This material has been provided pursuant to an agreement *
15 * containing restrictions on its use. The material is also *
16 * protected by copyright law. No part of this material may *
17 * be copied or distributed, transmitted or transcribed, in *
18 * any form or by any means, electronic, mechanical, magnetic, *
19 * manual, or otherwise, or disclosed to third parties without *
20 * the express written permission of Oracle Corporation UK Ltd, *
21 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
22 * England. *
23 * *
24 ******************************************************************
25 --
26 Name : hr_balances
27 --
28 Description : This package holds procedures and functions related to the
29 following tables :
30 --
31 PAY_ELEMENT_TYPES_F
32 PAY_INPUT_VALUES_F
33 PAY_SUB_CLASSIFICATION_RULES_F
34 PAY_BALANCE_FEEDS_F
35 PAY_BALANCE_TYPES
36 PAY_BALANCE_CLASSIFCIATIONS
37 PAY_DEFINED_BALANCES
38 --
39 Uses : hr_utility
40 Used By : n/a
41 --
42 Test List
43 ---------
44 Procedure Name Date Test Id Status
45 +----------------------------+----------+-----------+-------+------------+
46 get_pay_value NLBarlow 03-DEC-92 1 Completed
47 +------------------------------------------------------------------------+
48 ins_balance_feed NLBarlow 03-DEC-92 1 Completed
49 +------------------------------------------------------------------------+
50 upd_balance_feed NLBarlow 03-DEC-92 1 Completed
51 +------------------------------------------------------------------------+
52 chk_del_balance_feed M Dyer 05-Mar-93 1 Completed
53 +------------------------------------------------------------------------+
54 del_balance_feed NLBarlow 03-DEC-92 1 Completed
55 +------------------------------------------------------------------------+
56 del_balance_type_cascade NLBarlow 01-DEC-92 1 Completed
57 +------------------------------------------------------------------------+
58 --
59 --
60 Change List
61 -----------
62 Date Name Vers Bug No Description
63 ---- ---- ---- ------ -----------
64 07-DEC-92 NLBarlow 1.00 Prepared for ARCS.
65 29-DEC-92 NLBarlow 1.01 Added business group
66 and legislation code.
67 11-JAN-93 NLBarlow 1.02 Added startup logic.
68 20-JAN-93 NLBarlow 1.03 Change in standards.
69 08-FEB-93 NLBarlow 1.04 Added Delete Next Change
70 and optimised.
71 24-FEB-93 M DYER 1.06 Corrected message name
72 for HR_6185_BAL_FEED_EXISTS
73 05-Mar-93 M DYER 30.22 Added chk_del_balance_feed
74 procedure.
75 11-MAR-93 H MINTON 30.23 Added exit to last line.
76 18-MAR-93 NLBarlow 30.24 Trunced sysdates and
77 validation on primary
78 bal class
79 15-JUL-93 M Dyer 30.25 B82 changed behaviour of
80 ownership details for startup
81 records.
82 03-Aug-93 M Dyer 40.00 Added chk_ins_balance_feeds
83 and chk_ins_sub_class_rules
84 These check the max end date
85 for insert and next change
86 delete on these records.
87 16-Aug-93 M Kaddir 30.31/ B203 Replaced function
88 40.01 GET_PAY_VALUE with a call
89 to HR_INPUT_VALUES.GET_PAY_
90 VALUE_NAME to use HR_LOOKUPS
91 for Pay Value instead of
92 PAY_NAME_TRANSLATIONS
93 28-MAR-94 R Neale 40.02 Added header info
94 01-MAR-99 J. Moyano 115.1 MLS changes. Added references
95 to _TL tables.
96 15-FEB-00 A. Logue 115.4 Utf8 Support. Input Value
97 names extended to 80.
98 29-MAR-01 M.Reid 115.5 Added index hints for
99 balance_type_pk
100 01-OCT-2002 RThirlby 115.6 Bug 2595797 - Updated
101 ins_balance_feed to have an
102 option 'INS_PRIMARY_BALANCE
103 _FEED' used in balance form to
104 insert a feed when a primary
105 balance is inserted.
106 16-OCT-2002 RThirlby 115.7 Updated del_balnce_type_cascade
107 to ensure defined_balance
108 child rows are deleted prior
109 to deleting the defined bal.
110 03-DEC-2002 ALogue 115.8 2667222 Performance fixes in
111 chk_del_balance_feed.
112 Assumes Pay Value input values
113 have untranslated name in base
114 table.
115 31-AUG-2005 Adkumar 115.9 4571260 Changed call to
116 hr_balances.ins_balance_feed
117 for option=INS_MANUAL_FEED
118 to insert business group id
119 and legislation code as passed
120 by user as parameter instead of
121 inserting BG and legislation
122 of Element.
123
124 --
125 */
126 --
127 PROCEDURE ins_application_ownership
128 (p_balance_feed_id in varchar2,
129 p_input_value_id in number,
130 p_balance_type_id in number) IS
131 /*
132 NAME
133 ins_application_ownership
134 DESCRIPTION
135 Insert startup data into hr_application_ownerships.
136 NOTE (M Dyer)
137 Changes in the way child records in startup mode work mean that we
138 no longer want to derive the ownership of the balance feeds from the
139 current session. Instead we want to derive it from the record that is
140 responsible for creating the balance feed. If a balance feed is created
141 from the element type form then it will inherit the ownerships of the
142 input value record this will be clear because the balance_id will be
143 null. If the balance feed is created from the balance type form then
144 the input value id will be null.
145 */
146 --
147 BEGIN
148 --
149 hr_utility.set_location('ins_application_ownership', 1);
150 --
151 if p_input_value_id is null then
152 -- We want the balance feed to inherit the ownership of the balance type
153 --
154 INSERT INTO hr_application_ownerships
155 (KEY_NAME,PRODUCT_NAME,KEY_VALUE)
156 SELECT 'BALANCE_FEED_ID',
157 AO.PRODUCT_NAME,
158 p_balance_feed_id
159 FROM hr_application_ownerships AO
160 WHERE ao.key_name = 'BALANCE_TYPE_ID'
161 AND ao.key_value = to_char(p_balance_type_id);
162 --
163 elsif p_balance_type_id is null then
164 --
165 hr_utility.set_location('ins_application_ownership', 2);
166 --
167 INSERT INTO hr_application_ownerships
168 (KEY_NAME,PRODUCT_NAME,KEY_VALUE)
169 SELECT 'BALANCE_FEED_ID',
170 AO.PRODUCT_NAME,
171 p_balance_feed_id
172 FROM hr_application_ownerships AO
173 WHERE ao.key_name = 'INPUT_VALUE_ID'
174 AND ao.key_value = to_char(p_input_value_id);
175 --
176 end if;
177 --
178 END ins_application_ownership;
179 --
180 PROCEDURE ins_balance_feed
181 (p_option in varchar2,
182 p_input_value_id in number,
183 p_element_type_id in number,
184 p_primary_classification_id in number,
185 p_sub_classification_id in number,
186 p_sub_classification_rule_id in number,
187 p_balance_type_id in number,
188 p_scale in varchar2,
189 p_session_date in date,
190 p_business_group in varchar2,
191 p_legislation_code in varchar2,
192 p_mode in varchar2) IS
193 /*
194 NAME
195 ins_balance_feed
196 DESCRIPTION
197 Insert balance feeds.
198 */
199 --
200 -- Declare local variables
201 --
202 v_sequence number;
203 v_feed number;
204 v_pay_value varchar2(80);
205 l_new_end_date date;
206 --
207 -- Declare local cursors
208 --
209 CURSOR cur_get_pay_pay_value IS
210 SELECT /*+ INDEX (BT PAY_BALANCE_TYPES_PK) */
211 pay_balance_feeds_s.nextval a_balance_feed_id,
212 iv.effective_start_date a_effective_start_date,
213 iv.effective_end_date a_effective_end_date,
214 iv.input_value_id a_input_value_id,
215 bc.balance_type_id a_balance_type_id,
216 bc.scale a_scale,
217 iv.business_group_id a_business_group_id,
218 iv.legislation_code a_legislation_code,
219 iv.legislation_subgroup a_legislation_subgroup,
220 iv.last_updated_by a_last_updated_by,
221 iv.last_update_login a_last_update_login,
222 iv.created_by a_created_by
223 FROM pay_balance_types bt,
224 pay_balance_classifications bc,
225 pay_element_types_f et,
226 pay_input_values_f iv
227 WHERE iv.input_value_id = p_input_value_id
228 AND et.element_type_id = iv.element_type_id
229 AND et.effective_start_date <= p_session_date
230 AND et.effective_end_date >= p_session_date
231 AND bc.classification_id = p_primary_classification_id
232 AND bt.balance_type_id = bc.balance_type_id
233 AND nvl(bt.business_group_id,nvl(p_business_group,0))
234 = nvl(p_business_group,0)
235 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
236 = nvl(p_legislation_code,' ')
237 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
238 AND ((bt.balance_uom = 'M'
239 AND et.output_currency_code = bt.currency_code)
240 OR bt.balance_uom <> 'M')
241 FOR UPDATE;
242 --
243 CURSOR cur_check_pay_pay_value IS
244 SELECT feed.balance_feed_id
245 FROM pay_balance_feeds_f feed,
246 pay_balance_types bt,
247 pay_balance_classifications bc,
248 pay_element_types_f et,
249 pay_input_values_f iv
250 WHERE iv.input_value_id = p_input_value_id
251 AND et.element_type_id = iv.element_type_id
252 AND et.effective_start_date <= p_session_date
253 AND et.effective_end_date >= p_session_date
254 AND bc.classification_id = p_primary_classification_id
255 AND bt.balance_type_id = bc.balance_type_id
256 AND nvl(bt.business_group_id,nvl(p_business_group,0))
257 = nvl(p_business_group,0)
258 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
259 = nvl(p_legislation_code,' ')
260 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
261 AND ((bt.balance_uom = 'M'
262 AND et.output_currency_code = bt.currency_code)
263 OR bt.balance_uom <> 'M')
264 AND feed.input_value_id = iv.input_value_id
265 AND feed.balance_type_id = bt.balance_type_id
266 AND feed.effective_start_date <= iv.effective_end_date
267 AND feed.effective_end_date >= iv.effective_start_date;
268 --
269 CURSOR cur_get_per_pay_value_prim IS
270 SELECT /*+ INDEX (BT PAY_BALANCE_TYPES_PK) */
271 pay_balance_feeds_s.nextval a_balance_feed_id,
272 iv.effective_start_date a_effective_start_date,
273 iv.effective_end_date a_effective_end_date,
274 iv.input_value_id a_input_value_id,
275 bc.balance_type_id a_balance_type_id,
276 bc.scale a_scale,
277 iv.business_group_id a_business_group_id,
278 iv.legislation_code a_legislation_code,
279 iv.legislation_subgroup a_legislation_subgroup,
280 iv.last_updated_by a_last_updated_by,
281 iv.last_update_login a_last_update_login,
282 iv.created_by a_created_by
283 FROM pay_balance_types bt,
284 pay_balance_classifications bc,
285 pay_element_types_f et,
286 pay_input_values_f iv
287 WHERE iv.input_value_id = p_input_value_id
288 AND et.element_type_id = iv.element_type_id
289 AND et.effective_start_date <= p_session_date
290 AND et.effective_end_date >= p_session_date
291 AND bc.classification_id = p_primary_classification_id
292 AND bt.balance_type_id = bc.balance_type_id
293 AND nvl(bt.business_group_id,nvl(p_business_group,0))
294 = nvl(p_business_group,0)
295 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
296 = nvl(p_legislation_code,' ')
297 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
298 AND ((bt.balance_uom = 'M'
299 AND et.output_currency_code = bt.currency_code)
300 OR bt.balance_uom <> 'M')
301 FOR UPDATE;
302 --
303 CURSOR cur_check_per_pay_value_prim IS
304 SELECT feed.balance_feed_id
305 FROM pay_balance_feeds_f feed,
306 pay_balance_types bt,
307 pay_balance_classifications bc,
308 pay_element_types_f et,
309 pay_input_values_f iv
310 WHERE iv.input_value_id = p_input_value_id
311 AND et.element_type_id = iv.element_type_id
312 AND et.effective_start_date <= p_session_date
313 AND et.effective_end_date >= p_session_date
314 AND bc.classification_id = p_primary_classification_id
315 AND bt.balance_type_id = bc.balance_type_id
316 AND nvl(bt.business_group_id,nvl(p_business_group,0))
317 = nvl(p_business_group,0)
318 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
319 = nvl(p_legislation_code,' ')
320 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
321 AND ((bt.balance_uom = 'M'
322 AND et.output_currency_code = bt.currency_code)
323 OR bt.balance_uom <> 'M')
324 AND feed.input_value_id = iv.input_value_id
325 AND feed.balance_type_id = bt.balance_type_id
326 AND feed.effective_start_date <= iv.effective_end_date
327 AND feed.effective_end_date >= iv.effective_start_date;
328 --
329 CURSOR cur_get_per_pay_value_sub IS
330 SELECT /*+ INDEX (BT PAY_BALANCE_TYPES_PK) */
331 pay_balance_feeds_s.nextval a_balance_feed_id,
332 scr.effective_start_date a_effective_start_date,
333 scr.effective_end_date a_effective_end_date,
334 iv.input_value_id a_input_value_id,
335 bc.balance_type_id a_balance_type_id,
336 bc.scale a_scale,
337 iv.business_group_id a_business_group_id,
338 iv.legislation_code a_legislation_code,
339 iv.legislation_subgroup a_legislation_subgroup,
340 iv.last_updated_by a_last_updated_by,
341 iv.last_update_login a_last_update_login,
342 iv.created_by a_created_by
343 FROM pay_balance_types bt,
344 pay_balance_classifications bc,
345 pay_element_types_f et,
346 pay_sub_classification_rules_f scr,
347 pay_input_values_f iv
348 WHERE iv.input_value_id = p_input_value_id
349 AND scr.element_type_id = iv.element_type_id
350 AND iv.effective_start_date <= scr.effective_end_date
351 AND iv.effective_end_date >= scr.effective_start_date
352 AND et.element_type_id = iv.element_type_id
353 AND et.effective_start_date <= p_session_date
354 AND et.effective_end_date >= p_session_date
355 AND bc.classification_id = scr.classification_id
356 AND bt.balance_type_id = bc.balance_type_id
357 AND nvl(bt.business_group_id,nvl(p_business_group,0))
358 = nvl(p_business_group,0)
359 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
360 = nvl(p_legislation_code,' ')
361 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
362 AND ((bt.balance_uom = 'M'
363 AND et.output_currency_code = bt.currency_code)
364 OR bt.balance_uom <> 'M')
365 FOR UPDATE;
366 --
367 CURSOR cur_check_per_pay_value_sub IS
368 SELECT feed.balance_feed_id
369 FROM pay_balance_feeds_f feed,
370 pay_balance_types bt,
371 pay_balance_classifications bc,
372 pay_element_types_f et,
373 pay_sub_classification_rules_f scr,
374 pay_input_values_f iv
375 WHERE iv.input_value_id = p_input_value_id
376 AND scr.element_type_id = iv.element_type_id
377 AND iv.effective_end_date >= scr.effective_start_date
378 AND iv.effective_start_date <= scr.effective_end_date
379 AND et.element_type_id = iv.element_type_id
380 AND et.effective_start_date <= p_session_date
381 AND et.effective_end_date >= p_session_date
382 AND bc.classification_id = scr.classification_id
383 AND bt.balance_type_id = bc.balance_type_id
384 AND nvl(bt.business_group_id,nvl(p_business_group,0))
385 = nvl(p_business_group,0)
386 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
387 = nvl(p_legislation_code,' ')
388 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
389 AND ((bt.balance_uom = 'M'
390 AND et.output_currency_code = bt.currency_code)
391 OR bt.balance_uom <> 'M')
392 AND feed.input_value_id = iv.input_value_id
393 AND feed.balance_type_id = bt.balance_type_id
394 AND feed.effective_start_date <= scr.effective_end_date
395 AND feed.effective_end_date >= scr.effective_start_date;
396 --
397 CURSOR cur_lock_manual_feed IS
398 SELECT iv.input_value_id,
399 et.element_type_id,
400 bt.balance_type_id
401 FROM pay_balance_types bt,
402 pay_element_types_f et,
403 pay_input_values_f iv
404 WHERE iv.input_value_id = p_input_value_id
405 AND et.element_type_id = iv.element_type_id
406 AND et.effective_start_date <= p_session_date
407 AND et.effective_end_date >= p_session_date
408 AND bt.balance_type_id = p_balance_type_id
409 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
410 AND ((bt.balance_uom = 'M'
411 AND et.output_currency_code = bt.currency_code)
412 OR bt.balance_uom <> 'M');
413 --
414 CURSOR cur_check_manual_feed IS
415 SELECT feed.balance_feed_id
416 FROM pay_balance_feeds_f feed,
417 pay_balance_types bt,
418 pay_element_types_f et,
419 pay_input_values_f iv
420 WHERE iv.input_value_id = p_input_value_id
421 AND et.element_type_id = iv.element_type_id
422 AND et.effective_start_date <= p_session_date
423 AND et.effective_end_date >= p_session_date
424 AND bt.balance_type_id = p_balance_type_id
425 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
426 AND ((bt.balance_uom = 'M'
427 AND et.output_currency_code = bt.currency_code)
428 OR bt.balance_uom <> 'M')
429 AND feed.input_value_id = iv.input_value_id
430 AND feed.balance_type_id = bt.balance_type_id
431 AND feed.effective_start_date <= iv.effective_end_date
432 AND feed.effective_end_date >= iv.effective_start_date;
433 --
434 CURSOR cur_lock_sub_class_rule IS
435 SELECT iv.input_value_id,
436 et.element_type_id,
437 scr.sub_classification_rule_id,
438 bc.balance_classification_id,
439 bt.balance_type_id
440 FROM pay_balance_types bt,
441 pay_balance_classifications bc,
442 pay_element_types_f et,
443 pay_input_values_f_tl iv_tl,
444 pay_input_values_f iv,
445 pay_sub_classification_rules_f scr
446 WHERE iv_tl.input_value_id = iv.input_value_id
447 and userenv('LANG') = iv_tl.language
448 AND scr.sub_classification_rule_id
449 = p_sub_classification_rule_id
450 AND iv.element_type_id = scr.element_type_id
451 AND iv_tl.name = v_pay_value
452 AND iv.effective_start_date <= scr.effective_end_date
453 AND iv.effective_end_date >= scr.effective_start_date
454 AND et.element_type_id = iv.element_type_id
455 AND et.effective_start_date <= p_session_date
456 AND et.effective_end_date >= p_session_date
457 AND bc.classification_id = scr.classification_id
458 AND bt.balance_type_id = bc.balance_type_id
459 AND nvl(bt.business_group_id,nvl(p_business_group,0))
460 = nvl(p_business_group,0)
461 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
462 = nvl(p_legislation_code,' ')
463 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
464 AND ((bt.balance_uom = 'M'
465 AND bt.currency_code = et.output_currency_code)
466 OR bt.balance_uom <> 'M')
467 FOR UPDATE;
468 --
469 CURSOR cur_check_sub_class_rule IS
470 SELECT feed.balance_feed_id
471 FROM pay_balance_feeds feed,
472 pay_balance_types bt,
473 pay_balance_classifications bc,
474 pay_element_types_f et,
475 pay_input_values_f_tl iv_tl,
476 pay_input_values_f iv,
477 pay_sub_classification_rules_f scr
478 WHERE iv_tl.input_value_id = iv.input_value_id
479 and userenv('LANG') = iv_tl.language
480 and scr.sub_classification_rule_id
481 = p_sub_classification_rule_id
482 AND iv.element_type_id = scr.element_type_id
483 AND iv_tl.name = v_pay_value
484 AND iv.effective_start_date <= scr.effective_end_date
485 AND iv.effective_end_date >= scr.effective_start_date
486 AND et.element_type_id = iv.element_type_id
487 AND et.effective_start_date <= p_session_date
488 AND et.effective_end_date >= p_session_date
489 AND bc.classification_id = scr.classification_id
490 AND bt.balance_type_id = bc.balance_type_id
491 AND nvl(bt.business_group_id,nvl(p_business_group,0))
492 = nvl(p_business_group,0)
493 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
494 = nvl(p_legislation_code,' ')
495 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
496 AND ((bt.balance_uom = 'M'
497 AND bt.currency_code = et.output_currency_code)
498 OR bt.balance_uom <> 'M')
499 AND feed.input_value_id = iv.input_value_id
500 AND feed.balance_type_id = bt.balance_type_id
501 AND feed.effective_start_date <= scr.effective_end_date
502 AND feed.effective_end_date >= scr.effective_start_date;
503 --
504 CURSOR cur_iv_sub_class_rule IS
505 SELECT iv.input_value_id a_input_value_id,
506 scr.effective_start_date a_effective_start_date,
507 scr.effective_end_date a_effective_end_date,
508 iv.uom a_uom,
509 et.output_currency_code a_output_currency_code,
510 iv.business_group_id a_business_group_id,
511 iv.legislation_code a_legislation_code,
512 iv.legislation_subgroup a_legislation_subgroup
513 FROM pay_element_types_f et,
514 pay_input_values_f_tl iv_tl,
515 pay_input_values_f iv,
516 pay_sub_classification_rules_f scr
517 WHERE iv_tl.input_value_id = iv.input_value_id
518 and userenv('LANG') = iv_tl.language
519 and scr.sub_classification_rule_id
520 = p_sub_classification_rule_id
521 AND iv.element_type_id = scr.element_type_id
522 AND iv_tl.name = v_pay_value
523 AND nvl(iv.business_group_id,nvl(p_business_group,0))
524 = nvl(p_business_group,0)
525 AND nvl(iv.legislation_code,nvl(p_legislation_code,' '))
526 = nvl(p_legislation_code,' ')
527 AND iv.effective_end_date >= scr.effective_start_date
528 AND iv.effective_start_date <= scr.effective_end_date
529 AND et.element_type_id = iv.element_type_id
530 AND et.effective_start_date <= p_session_date
531 AND et.effective_end_date >= p_session_date;
532 --
533 CURSOR cur_bt_sub_class_rule (c_uom varchar2,
534 c_output_currency_code varchar2) IS
535 SELECT pay_balance_feeds_s.nextval a_balance_feed_id,
536 bc.balance_type_id a_balance_type_id,
537 bc.scale a_scale,
538 bc.last_updated_by a_last_updated_by,
539 bc.last_update_login a_last_update_login,
540 bc.created_by a_created_by
541 FROM pay_balance_types bt,
542 pay_balance_classifications bc,
543 pay_sub_classification_rules_f scr
544 WHERE scr.sub_classification_rule_id
545 = p_sub_classification_rule_id
546 AND bc.classification_id = scr.classification_id
547 AND bt.balance_type_id = bc.balance_type_id
548 AND nvl(bt.business_group_id,nvl(p_business_group,0))
549 = nvl(p_business_group,0)
550 AND nvl(bt.legislation_code,nvl(p_legislation_code,' '))
551 = nvl(p_legislation_code,' ')
552 AND substr(bt.balance_uom,1,1) = substr(c_uom,1,1)
553 AND ((bt.balance_uom = 'M'
554 AND bt.currency_code = c_output_currency_code)
555 OR bt.balance_uom <> 'M');
556 --
557 CURSOR cur_lock_primary_bal_class IS
558 SELECT iv.input_value_id,
559 et.element_type_id,
560 bt.balance_type_id
561 FROM pay_balance_types bt,
562 pay_input_values_f_tl iv_tl,
563 pay_input_values_f iv,
564 pay_element_types_f et
565 WHERE iv_tl.input_value_id = iv.input_value_id
566 and userenv('LANG') = iv_tl.language
567 and et.classification_id = p_primary_classification_id
568 AND et.effective_start_date <= p_session_date
569 AND et.effective_end_date >= p_session_date
570 AND nvl(et.business_group_id,nvl(p_business_group,0))
571 = nvl(p_business_group,0)
572 AND nvl(et.legislation_code,nvl(p_legislation_code,' '))
573 = nvl(p_legislation_code,' ')
574 AND iv.element_type_id = et.element_type_id
575 AND iv_tl.name = v_pay_value
576 AND bt.balance_type_id = p_balance_type_id
577 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
578 AND ((bt.balance_uom = 'M'
579 AND et.output_currency_code = bt.currency_code)
580 OR bt.balance_uom <> 'M')
581 FOR UPDATE;
582 --
583 CURSOR cur_check_primary_bal_class IS
584 SELECT feed.balance_feed_id
585 FROM pay_balance_feeds_f feed,
586 pay_balance_types bt,
587 pay_input_values_f_tl iv_tl,
588 pay_input_values_f iv,
589 pay_element_types_f et
590 WHERE iv_tl.input_value_id = iv.input_value_id
591 and userenv('LANG') = iv_tl.language
592 and et.classification_id = p_primary_classification_id
593 AND et.effective_start_date <= p_session_date
594 AND et.effective_end_date >= p_session_date
595 AND nvl(et.business_group_id,nvl(p_business_group,0))
596 = nvl(p_business_group,0)
597 AND nvl(et.legislation_code,nvl(p_legislation_code,' '))
598 = nvl(p_legislation_code,' ')
599 AND iv.element_type_id = et.element_type_id
600 AND iv_tl.name = v_pay_value
601 AND iv.effective_start_date <= p_session_date
602 AND iv.effective_end_date >= p_session_date
603 AND bt.balance_type_id = p_balance_type_id
604 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
605 AND ((bt.balance_uom = 'M'
606 AND et.output_currency_code = bt.currency_code)
607 OR bt.balance_uom <> 'M')
608 AND feed.input_value_id = iv.input_value_id
609 AND feed.balance_type_id = bt.balance_type_id
610 AND feed.effective_start_date <=
611 (SELECT max(iv.effective_end_date)
612 FROM pay_input_values_f iv1
613 WHERE iv1.input_value_id = iv.input_value_id
614 GROUP BY iv1.input_value_id)
615 AND feed.effective_end_date >=
616 (SELECT min(iv2.effective_start_date)
617 FROM pay_input_values_f iv2
618 WHERE iv2.input_value_id = iv.input_value_id
619 GROUP BY iv2.input_value_id);
620 --
621 CURSOR cur_iv_primary_bal_class IS
622 SELECT iv.input_value_id a_input_value_id,
623 min(iv.effective_start_date) a_effective_start_date,
624 max(iv.effective_end_date) a_effective_end_date,
625 iv.uom a_uom,
626 et.output_currency_code a_output_currency_code,
627 iv.business_group_id a_business_group_id,
628 iv.legislation_code a_legislation_code,
629 iv.legislation_subgroup a_legislation_subgroup
630 FROM pay_balance_types bt,
631 pay_input_values_f_tl iv_tl,
632 pay_input_values_f iv,
633 pay_element_types_f et
634 WHERE iv_tl.input_value_id = iv.input_value_id
635 and userenv('LANG') = iv_tl.language
636 and et.classification_id = p_primary_classification_id
637 AND et.effective_start_date <= p_session_date
638 AND et.effective_end_date >= p_session_date
639 AND nvl(et.business_group_id,nvl(p_business_group,0))
640 = nvl(p_business_group,0)
641 AND nvl(et.legislation_code,nvl(p_legislation_code,' '))
642 = nvl(p_legislation_code,' ')
643 AND iv.element_type_id = et.element_type_id
644 AND iv_tl.name = v_pay_value
645 AND bt.balance_type_id = p_balance_type_id
646 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
647 AND ((bt.balance_uom = 'M'
648 AND et.output_currency_code = bt.currency_code)
649 OR bt.balance_uom <> 'M')
650 GROUP BY iv.input_value_id, iv.uom, et.output_currency_code,
651 iv.business_group_id, iv.legislation_code, iv.legislation_subgroup;
652 --
653 CURSOR cur_lock_sub_bal_class IS
654 SELECT iv.input_value_id,
655 et.element_type_id,
656 scr.sub_classification_rule_id,
657 bt.balance_type_id
658 FROM pay_balance_types bt,
659 pay_element_types_f et,
660 pay_input_values_f_tl iv_tl,
661 pay_input_values_f iv,
662 pay_sub_classification_rules_f scr
663 WHERE iv_tl.input_value_id = iv.input_value_id
664 and userenv('LANG') = iv_tl.language
665 and scr.classification_id = p_sub_classification_id
666 AND iv.element_type_id = scr.element_type_id
667 AND iv_tl.name = v_pay_value
668 AND nvl(iv.business_group_id,nvl(p_business_group,0))
669 = nvl(p_business_group,0)
670 AND nvl(iv.legislation_code,nvl(p_legislation_code,' '))
671 = nvl(p_legislation_code,' ')
672 AND iv.effective_end_date >= scr.effective_start_date
673 AND iv.effective_start_date <= scr.effective_end_date
674 AND et.element_type_id = iv.element_type_id
675 AND bt.balance_type_id = p_balance_type_id
676 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
677 AND ((bt.balance_uom = 'M'
678 AND et.output_currency_code = bt.currency_code)
679 OR bt.balance_uom <> 'M')
680 FOR UPDATE;
681 --
682 CURSOR cur_check_sub_bal_class IS
683 SELECT feed.balance_feed_id
684 FROM pay_balance_feeds_f feed,
685 pay_balance_types bt,
686 pay_element_types_f et,
687 pay_input_values_f_tl iv_tl,
688 pay_input_values_f iv,
689 pay_sub_classification_rules_f scr
690 WHERE iv_tl.input_value_id = iv.input_value_id
691 and userenv('LANG') = iv_tl.language
692 and scr.classification_id = p_sub_classification_id
693 AND iv.element_type_id = scr.element_type_id
694 AND iv_tl.name = v_pay_value
695 AND nvl(iv.business_group_id,nvl(p_business_group,0))
696 = nvl(p_business_group,0)
697 AND nvl(iv.legislation_code,nvl(p_legislation_code,' '))
698 = nvl(p_legislation_code,' ')
699 AND iv.effective_end_date >= scr.effective_start_date
700 AND iv.effective_start_date <= scr.effective_end_date
701 AND et.element_type_id = iv.element_type_id
702 AND et.effective_end_date >= p_session_date
703 AND et.effective_start_date <= p_session_date
704 AND bt.balance_type_id = p_balance_type_id
705 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
706 AND ((bt.balance_uom = 'M'
707 AND et.output_currency_code = bt.currency_code)
708 OR bt.balance_uom <> 'M')
709 AND feed.input_value_id = iv.input_value_id
710 AND feed.balance_type_id = bt.balance_type_id
711 AND feed.effective_start_date <= scr.effective_end_date
712 AND feed.effective_end_date >= scr.effective_start_date;
713 --
714 CURSOR cur_iv_sub_bal_class IS
715 SELECT iv.input_value_id a_input_value_id,
716 scr.effective_start_date a_effective_start_date,
717 scr.effective_end_date a_effective_end_date,
718 iv.uom a_uom,
719 et.output_currency_code a_output_currency_code,
720 iv.business_group_id a_business_group_id,
721 iv.legislation_code a_legislation_code,
722 iv.legislation_subgroup a_legislation_subgroup
723 FROM pay_balance_types bt,
724 pay_element_types_f et,
725 pay_input_values_f_tl iv_tl,
726 pay_input_values_f iv,
727 pay_sub_classification_rules_f scr
728 WHERE iv_tl.input_value_id = iv.input_value_id
729 and userenv('LANG') = iv_tl.language
730 and scr.classification_id = p_sub_classification_id
731 AND iv.element_type_id = scr.element_type_id
732 AND iv_tl.name = v_pay_value
733 AND nvl(iv.business_group_id,nvl(p_business_group,0))
734 = nvl(p_business_group,0)
735 AND nvl(iv.legislation_code,nvl(p_legislation_code,' '))
736 = nvl(p_legislation_code,' ')
737 AND iv.effective_end_date >= scr.effective_start_date
738 AND iv.effective_start_date <= scr.effective_end_date
739 AND et.element_type_id = iv.element_type_id
740 AND et.effective_end_date >= p_session_date
741 AND et.effective_start_date <= p_session_date
742 AND bt.balance_type_id = p_balance_type_id
743 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
744 AND ((bt.balance_uom = 'M'
745 AND et.output_currency_code = bt.currency_code)
746 OR bt.balance_uom <> 'M');
747 --
748 BEGIN
749 --
750 v_feed := -1;
751 --
752 -- When payroll element created, pay value input value created, create feeds
753 -- for all balances associated with primary classification.
754 --
755 hr_utility.set_location('ins_balance_feed', 1);
756 --
757 IF p_option = 'INS_PAY_PAY_VALUE' THEN
758 --
759 hr_utility.set_location('INS_PAY_PAY_VALUE', 1);
760 --
761 IF (p_input_value_id is not NULL AND
762 p_primary_classification_id is not NULL AND
763 p_session_date is not NULL AND
764 p_legislation_code is not NULL AND
765 p_mode is not NULL) THEN
766 --
767 -- Lock all relevant records.
768 --
769 hr_utility.set_location('INS_PAY_PAY_VALUE', 2);
770 --
771 FOR lock_rec IN cur_get_pay_pay_value LOOP
772 NULL;
773 END LOOP;
774 --
775 -- Check for overlap.
776 --
777 hr_utility.set_location('INS_PAY_PAY_VALUE', 3);
778 --
779 OPEN cur_check_pay_pay_value;
780 FETCH cur_check_pay_pay_value INTO v_feed;
781 --
782 IF cur_check_pay_pay_value%FOUND THEN
783 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
784 hr_utility.raise_error;
785 --
786 END IF;
787 CLOSE cur_check_pay_pay_value;
788 --
789 hr_utility.set_location('INS_PAY_PAY_VALUE', 4);
790 --
791 FOR get_rec IN cur_get_pay_pay_value LOOP
792 --
793 INSERT INTO pay_balance_feeds_f
794 (balance_feed_id,
795 effective_start_date,
796 effective_end_date,
797 input_value_id,
798 balance_type_id,
799 scale,
800 business_group_id,
801 legislation_code,
802 legislation_subgroup,
803 last_update_date,
804 last_updated_by,
805 last_update_login,
806 created_by,
807 creation_date)
808 VALUES (get_rec.a_balance_feed_id,
809 get_rec.a_effective_start_date,
810 get_rec.a_effective_end_date,
811 get_rec.a_input_value_id,
812 get_rec.a_balance_type_id,
813 get_rec.a_scale,
814 get_rec.a_business_group_id,
815 get_rec.a_legislation_code,
816 get_rec.a_legislation_subgroup,
817 trunc(sysdate),
818 get_rec.a_last_updated_by,
819 get_rec.a_last_update_login,
820 get_rec.a_created_by,
821 trunc(sysdate));
822 --
823 IF p_mode <> 'USER' THEN
824 ins_application_ownership(get_rec.a_balance_feed_id,
825 get_rec.a_input_value_id,
826 NULL);
827 END IF;
828 --
829 END LOOP;
830 --
831 ELSE
832 --
833 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
834 hr_utility.raise_error;
835 --
836 END IF;
837 --
838 -- When pay value input value created for personnel element, create feeds
839 -- for primary and sub classifications for associated balances.
840 --
841 ELSIF p_option = 'INS_PER_PAY_VALUE' THEN
842 --
843 hr_utility.set_location('INS_PER_PAY_VALUE', 1);
844 --
845 IF (p_input_value_id is not NULL AND
846 p_primary_classification_id is not NULL AND
847 p_session_date is not NULL AND
848 p_legislation_code is not NULL AND
849 p_mode is not NULL) THEN
850 --
851 -- Insert against primary classifications
852 --
853 -- Lock all relevant records.
854 --
855 hr_utility.set_location('INS_PER_PAY_VALUE', 2);
856 --
857 FOR lock_rec IN cur_get_per_pay_value_prim LOOP
858 NULL;
859 END LOOP;
860 --
861 -- Check for overlap.
862 --
863 hr_utility.set_location('INS_PER_PAY_VALUE', 3);
864 --
865 OPEN cur_check_per_pay_value_prim;
866 FETCH cur_check_per_pay_value_prim INTO v_feed;
867 --
868 IF cur_check_per_pay_value_prim%FOUND THEN
869 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
870 hr_utility.raise_error;
871 --
872 END IF;
873 CLOSE cur_check_per_pay_value_prim;
874 --
875 hr_utility.set_location('INS_PER_PAY_VALUE', 4);
876 --
877 FOR get_rec IN cur_get_per_pay_value_prim LOOP
878 --
879 INSERT INTO pay_balance_feeds_f
880 (balance_feed_id,
881 effective_start_date,
882 effective_end_date,
883 input_value_id,
884 balance_type_id,
885 scale,
886 business_group_id,
887 legislation_code,
888 legislation_subgroup,
889 last_update_date,
890 last_updated_by,
891 last_update_login,
892 created_by,
893 creation_date)
894 VALUES (get_rec.a_balance_feed_id,
895 get_rec.a_effective_start_date,
896 get_rec.a_effective_end_date,
897 get_rec.a_input_value_id,
898 get_rec.a_balance_type_id,
899 get_rec.a_scale,
900 get_rec.a_business_group_id,
901 get_rec.a_legislation_code,
902 get_rec.a_legislation_subgroup,
903 trunc(sysdate),
904 get_rec.a_last_updated_by,
905 get_rec.a_last_update_login,
906 get_rec.a_created_by,
907 trunc(sysdate));
908 --
909 IF p_mode <> 'USER' THEN
910 ins_application_ownership(get_rec.a_balance_feed_id,
911 get_rec.a_input_value_id,
912 NULL);
913 END IF;
914 --
915 END LOOP;
916 --
917 -- AND
918 --
919 -- Insert against sub classifications
920 --
921 -- Lock all relevant records.
922 --
923 hr_utility.set_location('INS_PER_PAY_VALUE', 5);
924 --
925 FOR lock_rec IN cur_get_per_pay_value_sub LOOP
926 NULL;
927 END LOOP;
928 --
929 -- Check for overlap.
930 --
931 hr_utility.set_location('INS_PER_PAY_VALUE', 6);
932 --
933 OPEN cur_check_per_pay_value_sub;
934 FETCH cur_check_per_pay_value_sub INTO v_feed;
935 --
936 IF cur_check_per_pay_value_sub%FOUND THEN
937 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
938 hr_utility.raise_error;
939 --
940 END IF;
941 CLOSE cur_check_per_pay_value_sub;
942 --
943 hr_utility.set_location('INS_PER_PAY_VALUE', 7);
944 --
945 FOR get_rec IN cur_get_per_pay_value_sub LOOP
946 --
947 INSERT INTO pay_balance_feeds_f
948 (balance_feed_id,
949 effective_start_date,
950 effective_end_date,
951 input_value_id,
952 balance_type_id,
953 scale,
954 business_group_id,
955 legislation_code,
956 legislation_subgroup,
957 last_update_date,
958 last_updated_by,
959 last_update_login,
960 created_by,
961 creation_date)
962 VALUES (get_rec.a_balance_feed_id,
963 get_rec.a_effective_start_date,
964 get_rec.a_effective_end_date,
965 get_rec.a_input_value_id,
966 get_rec.a_balance_type_id,
967 get_rec.a_scale,
968 get_rec.a_business_group_id,
969 get_rec.a_legislation_code,
970 get_rec.a_legislation_subgroup,
971 trunc(sysdate),
972 get_rec.a_last_updated_by,
973 get_rec.a_last_update_login,
974 get_rec.a_created_by,
975 trunc(sysdate));
976 --
977 IF p_mode <> 'USER' THEN
978 ins_application_ownership(get_rec.a_balance_feed_id,
979 get_rec.a_input_value_id,
980 NULL);
981 END IF;
982 --
983 END LOOP;
984 --
985 ELSE
986 --
987 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
988 hr_utility.raise_error;
989 --
990 END IF;
991 --
992 -- Manually create a balance feed against primary clasification and selected
993 -- balance.
994 --
995 ELSIF p_option = 'INS_MANUAL_FEED' THEN
996 --
997 hr_utility.set_location('INS_MANUAL_FEED', 1);
998 --
999 IF (p_input_value_id is not NULL AND
1000 p_balance_type_id is not NULL AND
1001 p_scale is not NULL AND
1002 p_mode is not NULL) THEN
1003 --
1004 -- Lock all relevant records.
1005 --
1006 hr_utility.set_location('INS_MANUAL_FEED', 2);
1007 --
1008 FOR lock_rec IN cur_lock_manual_feed LOOP
1009 NULL;
1010 END LOOP;
1011 --
1012 -- Check for overlap.
1013 --
1014 hr_utility.set_location('INS_MANUAL_FEED', 3);
1015 --
1016 OPEN cur_check_manual_feed;
1017 FETCH cur_check_manual_feed INTO v_feed;
1018 --
1019 IF cur_check_manual_feed%FOUND THEN
1020 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
1021 hr_utility.raise_error;
1022 --
1023 END IF;
1024 CLOSE cur_check_manual_feed;
1025 --
1026 IF p_session_date is not NULL THEN
1027 --
1028 hr_utility.set_location('INS_MANUAL_FEED', 4);
1029 --
1030 SELECT pay_balance_feeds_s.nextval
1031 INTO v_sequence
1032 FROM sys.dual;
1033 --
1034 INSERT INTO pay_balance_feeds_f
1035 (balance_feed_id,
1036 effective_start_date,
1037 effective_end_date,
1038 input_value_id,
1039 balance_type_id,
1040 scale,
1041 business_group_id,
1042 legislation_code,
1043 legislation_subgroup,
1044 last_update_date,
1045 last_updated_by,
1046 last_update_login,
1047 created_by,
1048 creation_date)
1049 SELECT v_sequence,
1050 p_session_date,
1051 max(iv.effective_end_date),
1052 iv.input_value_id,
1053 bt.balance_type_id,
1054 p_scale,
1055 nvl(p_business_group,iv.business_group_id),
1056 decode(p_business_group,NULL,nvl(p_legislation_code,iv.legislation_code),NULL),
1057 iv.legislation_subgroup,
1058 trunc(sysdate),
1059 iv.last_updated_by,
1060 iv.last_update_login,
1061 iv.created_by,
1062 trunc(sysdate)
1063 FROM pay_input_values_f iv,
1064 pay_element_types_f et,
1065 pay_balance_types bt
1066 WHERE iv.input_value_id = p_input_value_id
1067 AND et.element_type_id = iv.element_type_id
1068 AND p_session_date between et.effective_start_date
1069 and et.effective_end_date
1070 AND bt.balance_type_id = p_balance_type_id
1071 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
1072 AND ((bt.balance_uom = 'M'
1073 AND et.output_currency_code = bt.currency_code)
1074 OR bt.balance_uom <> 'M')
1075 GROUP BY iv.input_value_id, bt.balance_type_id,
1076 iv.business_group_id, iv.legislation_code, iv.legislation_subgroup,
1077 iv.last_updated_by, iv.last_update_login, iv.created_by;
1078 --
1079 IF p_mode <> 'USER' THEN
1080 ins_application_ownership(v_sequence,
1081 NULL,
1082 p_balance_type_id);
1083 END IF;
1084 --
1085 END IF;
1086 --
1087 ELSE
1088 --
1089 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1090 hr_utility.raise_error;
1091 --
1092 END IF;
1093 --
1094 -- On adding sub classication rule to an element, create balance feeds for
1095 -- the pay value against all balances feed by classification.
1096 --
1097 ELSIF p_option = 'INS_SUB_CLASS_RULE' THEN
1098 --
1099 hr_utility.set_location('INS_SUB_CLASS_RULE', 1);
1100 --
1101 IF (p_sub_classification_rule_id is not NULL AND
1102 p_session_date is not NULL AND
1103 p_legislation_code is not NULL AND
1104 p_mode is not NULL) THEN
1105 --
1106 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1107 --
1108 -- Lock all relevant records.
1109 --
1110 hr_utility.set_location('INS_SUB_CLASS_RULE', 2);
1111 --
1112 FOR lock_rec IN cur_lock_sub_class_rule LOOP
1113 NULL;
1114 END LOOP;
1115 --
1116 hr_utility.set_location('INS_SUB_CLASS_RULE', 3);
1117 --
1118 OPEN cur_check_sub_class_rule;
1119 FETCH cur_check_sub_class_rule INTO v_feed;
1120 --
1121 IF cur_check_sub_class_rule%FOUND THEN
1122 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
1123 hr_utility.raise_error;
1124 --
1125 END IF;
1126 CLOSE cur_check_sub_class_rule;
1127 --
1128 hr_utility.set_location('INS_SUB_CLASS_RULE', 4);
1129 --
1130 FOR iv_rec IN cur_iv_sub_class_rule LOOP
1131 --
1132 hr_utility.set_location('INS_SUB_CLASS_RULE', 5);
1133 --
1134 FOR bt_rec IN cur_bt_sub_class_rule
1135 (iv_rec.a_uom, iv_rec.a_output_currency_code) LOOP
1136 --
1137 hr_utility.set_location('INS_SUB_CLASS_RULE', 6);
1138 --
1139 INSERT INTO pay_balance_feeds_f
1140 (balance_feed_id,
1141 effective_start_date,
1142 effective_end_date,
1143 input_value_id,
1144 balance_type_id,
1145 scale,
1146 business_group_id,
1147 legislation_code,
1148 legislation_subgroup,
1149 last_update_date,
1150 last_updated_by,
1151 last_update_login,
1152 created_by,
1153 creation_date)
1154 SELECT bt_rec.a_balance_feed_id,
1155 iv_rec.a_effective_start_date,
1156 iv_rec.a_effective_end_date,
1157 iv_rec.a_input_value_id,
1158 bt_rec.a_balance_type_id,
1159 bt_rec.a_scale,
1160 iv_rec.a_business_group_id,
1161 iv_rec.a_legislation_code,
1162 iv_rec.a_legislation_subgroup,
1163 trunc(sysdate),
1164 bt_rec.a_last_updated_by,
1165 bt_rec.a_last_update_login,
1166 bt_rec.a_created_by,
1167 trunc(sysdate)
1168 FROM sys.dual;
1169 --
1170 IF p_mode <> 'USER' THEN
1171 ins_application_ownership(bt_rec.a_balance_feed_id,
1172 iv_rec.a_input_value_id,
1173 NULL);
1174 END IF;
1175 --
1176 END LOOP;
1177 --
1178 END LOOP;
1179 --
1180 ELSE
1181 --
1182 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1183 hr_utility.raise_error;
1184 --
1185 END IF;
1186 --
1187 -- When balance classification created feeds for all elements with pay value
1188 -- input values associated with classification (primary or sub).
1189 --
1190 ELSIF p_option = 'INS_PRIMARY_BAL_CLASS' THEN
1191 --
1192 hr_utility.set_location('INS_PRIMARY_BAL_CLASS', 1);
1193 --
1194 IF (p_primary_classification_id is not NULL AND
1195 p_balance_type_id is not NULL AND
1196 p_scale is not NULL AND
1197 p_session_date is not NULL AND
1198 p_legislation_code is not NULL AND
1199 p_mode is not NULL) THEN
1200 --
1201 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1202 --
1203 -- Lock all relevant records.
1204 --
1205 hr_utility.set_location('INS_PRIMARY_BAL_CLASS', 2);
1206 --
1207 FOR lock_rec IN cur_lock_primary_bal_class LOOP
1208 NULL;
1209 END LOOP;
1210 --
1211 hr_utility.set_location('INS_PRIMARY_BAL_CLASS', 3);
1212 --
1213 OPEN cur_check_primary_bal_class;
1214 FETCH cur_check_primary_bal_class INTO v_feed;
1215 --
1216 IF cur_check_primary_bal_class%FOUND THEN
1217 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
1218 hr_utility.raise_error;
1219 --
1220 END IF;
1221 CLOSE cur_check_primary_bal_class;
1222 --
1223 hr_utility.set_location('INS_PRIMARY_BAL_CLASS', 4);
1224 --
1225 <<bal_loop>>
1226 FOR iv_rec IN cur_iv_primary_bal_class LOOP
1227 --
1228 EXIT bal_loop WHEN iv_rec.a_effective_start_date IS NULL
1229 OR iv_rec.a_effective_end_date IS NULL;
1230 --
1231 SELECT pay_balance_feeds_s.nextval
1232 INTO v_sequence
1233 FROM sys.dual;
1234 --
1235 INSERT INTO pay_balance_feeds_f
1236 (balance_feed_id,
1237 effective_start_date,
1238 effective_end_date,
1239 input_value_id,
1240 balance_type_id,
1241 scale,
1242 business_group_id,
1243 legislation_code,
1244 legislation_subgroup,
1245 last_update_date,
1246 last_updated_by,
1247 last_update_login,
1248 created_by,
1249 creation_date)
1250 SELECT v_sequence,
1251 iv_rec.a_effective_start_date,
1252 iv_rec.a_effective_end_date,
1253 iv_rec.a_input_value_id,
1254 p_balance_type_id,
1255 p_scale,
1256 iv_rec.a_business_group_id,
1257 iv_rec.a_legislation_code,
1258 iv_rec.a_legislation_subgroup,
1259 trunc(sysdate),
1260 -1,
1261 -1,
1262 -1,
1263 trunc(sysdate)
1264 FROM sys.dual;
1265 --
1266 IF p_mode <> 'USER' THEN
1267 ins_application_ownership(v_sequence,
1268 NULL,
1269 p_balance_type_id);
1270 END IF;
1271 --
1272 END LOOP bal_loop;
1273 --
1274 ELSE
1275 --
1276 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1277 hr_utility.raise_error;
1278 --
1279 END IF;
1280 --
1281 ELSIF p_option = 'INS_SUB_BAL_CLASS' THEN
1282 --
1283 hr_utility.set_location('INS_SUB_BAL_CLASS', 1);
1284 --
1285 IF (p_sub_classification_id is not NULL AND
1286 p_balance_type_id is not NULL AND
1287 p_scale is not NULL AND
1288 p_session_date is not NULL AND
1289 p_legislation_code is not NULL AND
1290 p_mode is not NULL) THEN
1291 --
1292 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1293 l_new_end_date := NULL;
1294 --
1295 -- Lock all relevant records.
1296 --
1297 hr_utility.set_location('INS_SUB_BAL_CLASS', 2);
1298 --
1299 FOR lock_rec IN cur_lock_sub_bal_class LOOP
1300 NULL;
1301 END LOOP;
1302 --
1303 hr_utility.set_location('INS_SUB_BAL_CLASS', 3);
1304 --
1305 for feed_rec in cur_check_sub_bal_class loop
1306 --
1307 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
1308 hr_utility.raise_error;
1309 --
1310 end loop;
1311 --
1312 hr_utility.set_location('INS_SUB_BAL_CLASS', 4);
1313 --
1314 FOR iv_rec IN cur_iv_sub_bal_class LOOP
1315 --
1316 SELECT pay_balance_feeds_s.nextval
1317 INTO v_sequence
1318 FROM sys.dual;
1319 --
1320 INSERT INTO pay_balance_feeds_f
1321 (balance_feed_id,
1322 effective_start_date,
1323 effective_end_date,
1324 input_value_id,
1325 balance_type_id,
1326 scale,
1327 business_group_id,
1328 legislation_code,
1329 legislation_subgroup,
1330 last_update_date,
1331 last_updated_by,
1332 last_update_login,
1333 created_by,
1334 creation_date)
1335 SELECT v_sequence,
1336 iv_rec.a_effective_start_date,
1337 least(nvl(l_new_end_date,iv_rec.a_effective_end_date),
1338 iv_rec.a_effective_end_date),
1339 iv_rec.a_input_value_id,
1340 p_balance_type_id,
1341 p_scale,
1342 iv_rec.a_business_group_id,
1343 iv_rec.a_legislation_code,
1344 iv_rec.a_legislation_subgroup,
1345 trunc(sysdate),
1346 -1,
1347 -1,
1348 -1,
1349 trunc(sysdate)
1350 FROM sys.dual;
1351 --
1352 IF p_mode <> 'USER' THEN
1353 ins_application_ownership(v_sequence,
1354 NULL,
1355 p_balance_type_id);
1356 END IF;
1357 --
1358 END LOOP;
1359 --
1360 ELSE
1361 --
1362 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1363 hr_utility.raise_error;
1364 --
1365 END IF;
1366 --
1367 -- This is very similar to INS_MANUAL_FEED, except the data mode (i.e. generic
1368 -- data has null BG and null LEG_code, startup data has not null LEG_CODE and
1369 -- null BG and user data has null LEG_CODE and not null BG), is determined
1370 -- by the data mode of the balance_type_id rather than the input_valud_id.
1371 --
1372 ELSIF p_option = 'INS_PRIMARY_BALANCE_FEED' THEN
1373 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 1);
1374 --
1375 IF (p_input_value_id is not NULL AND
1376 p_balance_type_id is not NULL AND
1377 p_scale is not NULL AND
1378 p_mode is not NULL) THEN
1379 --
1380 -- Lock all relevant records.
1381 --
1382 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 2);
1383 --
1384 FOR lock_rec IN cur_lock_manual_feed LOOP
1385 NULL;
1386 END LOOP;
1387 --
1388 -- Check for overlap.
1389 --
1390 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 3);
1391 --
1392 OPEN cur_check_manual_feed;
1393 FETCH cur_check_manual_feed INTO v_feed;
1394 --
1395 IF cur_check_manual_feed%FOUND THEN
1396 hr_utility.set_message (801,'HR_6185_BAL_FEED_EXISTS');
1397 hr_utility.raise_error;
1398 --
1399 END IF;
1400 CLOSE cur_check_manual_feed;
1401 --
1402 IF p_session_date is not NULL THEN
1403 --
1404 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 4);
1405 --
1406 SELECT pay_balance_feeds_s.nextval
1407 INTO v_sequence
1408 FROM sys.dual;
1409 --
1410 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 5);
1411 --
1412 INSERT INTO pay_balance_feeds_f
1413 (balance_feed_id,
1414 effective_start_date,
1415 effective_end_date,
1416 input_value_id,
1417 balance_type_id,
1418 scale,
1419 business_group_id,
1420 legislation_code,
1421 legislation_subgroup,
1422 last_update_date,
1423 last_updated_by,
1424 last_update_login,
1425 created_by,
1426 creation_date)
1427 SELECT v_sequence,
1428 p_session_date,
1429 max(iv.effective_end_date),
1430 iv.input_value_id,
1431 bt.balance_type_id,
1432 p_scale,
1433 bt.business_group_id,
1434 bt.legislation_code,
1435 bt.legislation_subgroup,
1436 trunc(sysdate),
1437 bt.last_updated_by,
1438 bt.last_update_login,
1439 bt.created_by,
1440 trunc(sysdate)
1441 FROM pay_input_values_f iv,
1442 pay_element_types_f et,
1443 pay_balance_types bt
1444 WHERE iv.input_value_id = p_input_value_id
1445 AND et.element_type_id = iv.element_type_id
1446 AND p_session_date between et.effective_start_date
1447 and et.effective_end_date
1448 AND bt.balance_type_id = p_balance_type_id
1449 AND substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
1450 AND ((bt.balance_uom = 'M'
1451 AND et.output_currency_code = bt.currency_code)
1452 OR bt.balance_uom <> 'M')
1453 GROUP BY iv.input_value_id, bt.balance_type_id
1454 , bt.business_group_id, bt.legislation_code
1455 , bt.legislation_subgroup, bt.last_updated_by
1456 , bt.last_update_login, bt.created_by;
1457 --
1458 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 6);
1459 --
1460 IF p_mode <> 'USER' THEN
1461 ins_application_ownership(v_sequence,
1462 NULL,
1463 p_balance_type_id);
1464 END IF;
1465 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 7);
1466 --
1467 END IF;
1468 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 8);
1469 --
1470 ELSE
1471 --
1472 hr_utility.set_location('INS_PRIMARY_BALANCE_FEED', 9);
1473 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1474 hr_utility.raise_error;
1475 --
1476 END IF;
1477 --
1478 END IF;
1479 --
1480 --
1481 END ins_balance_feed;
1482 --
1483 PROCEDURE upd_balance_feed
1484 (p_option in varchar2,
1485 p_balance_feed_id in number,
1486 p_primary_classification_id in number,
1487 p_sub_classification_id in number,
1488 p_balance_type_id in number,
1489 p_scale in varchar2,
1490 p_legislation_code in varchar2) IS
1491 /*
1492 NAME
1493 upd_balance_feed
1494 DESCRIPTION
1495 Updated balance feeds.
1496 */
1497 --
1498 -- Declare local variables
1499 --
1500 v_pay_value varchar2(80);
1501 --
1502 BEGIN
1503 --
1504 hr_utility.set_location('upd_balance_feed', 1);
1505 --
1506 IF p_option = 'UPD_MANUAL_FEED' THEN
1507 --
1508 hr_utility.set_location('UPD_MANUAL_FEED', 1);
1509 --
1510 IF (p_balance_feed_id is not NULL AND
1511 p_scale is not NULL) THEN
1512 --
1513 UPDATE pay_balance_feeds_f
1514 SET scale = p_scale
1515 WHERE balance_feed_id = p_balance_feed_id;
1516 --
1517 ELSE
1518 --
1519 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1520 hr_utility.raise_error;
1521 --
1522 END IF;
1523 --
1524 ELSIF p_option = 'UPD_PRIMARY_BAL_CLASS' THEN
1525 --
1526 hr_utility.set_location('UPD_PRIMARY_BAL_CLASS', 1);
1527 --
1528 IF (p_primary_classification_id is not NULL AND
1529 p_balance_type_id is not NULL AND
1530 p_scale is not NULL AND
1531 p_legislation_code is not NULL) THEN
1532 --
1533 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1534 --
1535 UPDATE pay_balance_feeds_f
1536 SET scale = p_scale
1537 WHERE balance_type_id = p_balance_type_id
1538 AND input_value_id in
1539 (SELECT iv.input_value_id
1540 FROM pay_input_values_f iv,
1541 pay_element_types_f et
1542 WHERE iv.element_type_id = et.element_type_id
1543 and et.classification_id
1544 = p_primary_classification_id
1545 AND iv.name = 'Pay Value');
1546 --
1547 ELSE
1548 --
1549 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1550 hr_utility.raise_error;
1551 --
1552 END IF;
1553 --
1554 ELSIF p_option = 'UPD_SUB_BAL_CLASS' THEN
1555 --
1556 hr_utility.set_location('UPD_SUB_BAL_CLASS', 1);
1557 --
1558 IF (p_sub_classification_id is not NULL AND
1559 p_balance_type_id is not NULL AND
1560 p_scale is not NULL AND
1561 p_legislation_code is not NULL) THEN
1562 --
1563 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1564 --
1565 UPDATE pay_balance_feeds_f
1566 SET scale = p_scale
1567 WHERE balance_type_id = p_balance_type_id
1568 AND input_value_id in
1569 (SELECT iv.input_value_id
1570 FROM pay_input_values_f iv,
1571 pay_sub_classification_rules_f scr
1572 WHERE iv.element_type_id = scr.element_type_id
1573 and scr.classification_id
1574 = p_sub_classification_id
1575 AND iv.name = 'Pay Value'
1576 AND iv.effective_end_date
1577 >= scr.effective_start_date
1578 AND iv.effective_start_date
1579 <= scr.effective_end_date);
1580 --
1581 ELSE
1582 --
1583 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1584 hr_utility.raise_error;
1585 --
1586 END IF;
1587 --
1588 END IF;
1589 --
1590 END upd_balance_feed;
1591 --
1592 /*
1593 NAME
1594 chk_del_balance_feed
1595 DESCRIPTION
1596 checks to see if it is permissable to delete balance feeds displayed on
1597 the element type form. This will not be allowed if the balance feed has
1598 been created by a balance classification.
1599 */
1600 PROCEDURE chk_del_balance_feed
1601 (p_balance_feed_id in number,
1602 p_balance_type_id in number) is
1603 --
1604 l_balance_name varchar2(80) := NULL;
1605 --
1606 begin
1607 --
1608 begin
1609 --
1610 select b_tl.balance_name
1611 into l_balance_name
1612 from pay_balance_types_tl b_tl,
1613 pay_balance_types b
1614 where b_tl.balance_type_id = b.balance_type_id
1615 and userenv('LANG') = b_tl.language
1616 and b.balance_type_id = p_balance_type_id
1617 and exists
1618 (select 1
1619 from pay_balance_classifications bc
1620 where b.balance_type_id = bc.balance_type_id);
1621 --
1622 exception
1623 when NO_DATA_FOUND then NULL;
1624 end;
1625 --
1626 if l_balance_name is not NULL then
1627 hr_utility.set_message(801, 'PAY_6608_BAL_NO_BF_DEL');
1628 hr_utility.set_message_token('BALANCE', l_balance_name);
1629 hr_utility.raise_error;
1630 end if;
1631 --
1632 end chk_del_balance_feed;
1633 --
1634 PROCEDURE del_balance_feed
1635 (p_option in varchar2,
1636 p_delete_mode in varchar2,
1637 p_balance_feed_id in number,
1638 p_input_value_id in number,
1639 p_element_type_id in number,
1640 p_primary_classification_id in number,
1641 p_sub_classification_id in number,
1642 p_sub_classification_rule_id in number,
1643 p_balance_type_id in number,
1644 p_session_date in date,
1645 p_effective_end_date in date,
1646 p_legislation_code in varchar2,
1647 p_mode in varchar2) IS
1648 /*
1649 NAME
1650 del_balance_feed
1651 DESCRIPTION
1652 Delete balance feeds.
1653 */
1654 -- declare local variables
1655 --
1656 v_pay_value varchar(80);
1657 --
1658 BEGIN
1659 --
1660 hr_utility.set_location('del_balance_feed', 1);
1661 --
1662 IF p_option = 'DEL_MANUAL_FEED' THEN
1663 --
1664 hr_utility.set_location('DEL_MANUAL_FEED', 1);
1665 --
1666 IF p_balance_feed_id is not NULL THEN
1667 IF p_delete_mode = 'ZAP' THEN
1668 --
1669 IF p_mode <> 'USER' THEN
1670 DELETE FROM hr_application_ownerships
1671 WHERE KEY_NAME = 'BALANCE_FEED_ID'
1672 AND KEY_VALUE = p_balance_feed_id;
1673 END IF;
1674 --
1675 DELETE FROM pay_balance_feeds_f
1676 WHERE balance_feed_id = p_balance_feed_id;
1677 --
1678 ELSIF (p_delete_mode = 'DELETE' AND
1679 p_session_date is not NULL) THEN
1680 --
1681 UPDATE pay_balance_feeds_f
1682 SET effective_end_date = p_session_date
1683 WHERE balance_feed_id = p_balance_feed_id
1684 AND effective_start_date <= p_session_date
1685 AND effective_end_date >= p_session_date;
1686 --
1687 DELETE FROM pay_balance_feeds_f
1688 WHERE balance_feed_id = p_balance_feed_id
1689 AND effective_start_date > p_session_date;
1690 --
1691 ELSIF (p_delete_mode = 'DELETE_NEXT_CHANGE' AND
1692 p_effective_end_date is not NULL AND
1693 p_session_date is not NULL) THEN
1694 --
1695 UPDATE pay_balance_feeds_f
1696 SET effective_end_date = p_effective_end_date
1697 WHERE balance_feed_id = p_balance_feed_id
1698 AND effective_start_date <= p_session_date
1699 AND effective_end_date >= p_session_date;
1700 --
1701 DELETE FROM pay_balance_feeds_f
1702 WHERE balance_feed_id = p_balance_feed_id
1703 AND effective_start_date > p_session_date;
1704 --
1705 ELSE
1706 --
1707 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1708 hr_utility.raise_error;
1709 --
1710 END IF;
1711 --
1712 ELSE
1713 --
1714 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1715 hr_utility.raise_error;
1716 --
1717 END IF;
1718 --
1719 ELSIF p_option = 'DEL_INPUT_VALUE' THEN
1720 --
1721 hr_utility.set_location('DEL_INPUT_VALUE', 1);
1722 --
1723 IF p_input_value_id is not NULL THEN
1724 IF p_delete_mode = 'ZAP' THEN
1725 --
1726 IF p_mode <> 'USER' THEN
1727 DELETE FROM hr_application_ownerships
1728 WHERE KEY_NAME = 'BALANCE_FEED_ID'
1729 AND KEY_VALUE IN
1730 (SELECT balance_feed_id
1731 FROM pay_balance_feeds_f
1732 WHERE input_value_id = p_input_value_id);
1733 END IF;
1734 --
1735 DELETE FROM pay_balance_feeds_f
1736 WHERE input_value_id = p_input_value_id;
1737 --
1738 ELSIF (p_delete_mode = 'DELETE' AND
1739 p_session_date is not NULL) then
1740 --
1741 UPDATE pay_balance_feeds_f
1742 SET effective_end_date = p_session_date
1743 WHERE input_value_id = p_input_value_id
1744 AND effective_start_date <= p_session_date
1745 AND effective_end_date >= p_session_date;
1746 --
1747 DELETE FROM pay_balance_feeds_f
1748 WHERE input_value_id = p_input_value_id
1749 AND effective_start_date > p_session_date;
1750 --
1751 ELSIF (p_delete_mode = 'DELETE_NEXT_CHANGE' AND
1752 p_effective_end_date is not NULL AND
1753 p_session_date is not NULL) then
1754 --
1755 hr_utility.trace(to_char(p_effective_end_date));
1756 hr_utility.trace(to_char(p_input_value_id));
1757 hr_utility.trace(to_char(p_session_date));
1758 --
1759 UPDATE pay_balance_feeds_f
1760 SET effective_end_date = p_effective_end_date
1761 WHERE input_value_id = p_input_value_id
1762 AND effective_start_date <= p_session_date
1763 AND effective_end_date >= p_session_date;
1764 --
1765 DELETE FROM pay_balance_feeds_f
1766 WHERE input_value_id = p_input_value_id
1767 AND effective_start_date > p_session_date;
1768 --
1769 ELSE
1770 --
1771 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1772 hr_utility.raise_error;
1773 --
1774 END IF;
1775 --
1776 ELSE
1777 --
1778 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1779 hr_utility.raise_error;
1780 --
1781 END IF;
1782 --
1783 ELSIF p_option = 'DEL_SUB_CLASS_RULE' THEN
1784 --
1785 hr_utility.set_location('DEL_SUB_CLASS_RULE', 1);
1786 --
1787 IF (p_element_type_id is not NULL AND
1788 p_sub_classification_id is not NULL AND
1789 p_legislation_code is not NULL) THEN
1790 --
1791 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1792 --
1793 IF p_delete_mode = 'ZAP' THEN
1794 --
1795 IF p_mode <> 'USER' THEN
1796 DELETE FROM hr_application_ownerships
1797 WHERE KEY_NAME = 'BALANCE_FEED_ID'
1798 AND KEY_VALUE IN
1799 (SELECT feed.balance_feed_id
1800 FROM pay_balance_feeds_f feed,
1801 pay_input_values_f iv,
1802 pay_balance_classifications bc
1803 WHERE iv.element_type_id = p_element_type_id
1804 AND iv.name = 'Pay Value'
1805 AND feed.input_value_id = iv.input_value_id
1806 AND bc.classification_id
1807 = p_sub_classification_id
1808 AND feed.balance_type_id
1809 = bc.balance_type_id);
1810 END IF;
1811 --
1812 DELETE FROM pay_balance_feeds_f
1813 WHERE balance_feed_id in
1814 (SELECT feed.balance_feed_id
1815 FROM pay_balance_feeds_f feed,
1816 pay_input_values_f iv,
1817 pay_balance_classifications bc
1818 WHERE iv.element_type_id = p_element_type_id
1819 AND iv.name = 'Pay Value'
1820 AND feed.input_value_id = iv.input_value_id
1821 AND bc.classification_id
1822 = p_sub_classification_id
1823 AND feed.balance_type_id
1824 = bc.balance_type_id);
1825 --
1826 ELSIF (p_delete_mode = 'DELETE' AND
1827 p_session_date is not NULL) THEN
1828 --
1829 UPDATE pay_balance_feeds_f
1830 SET effective_end_date = p_session_date
1831 WHERE balance_feed_id in
1832 (SELECT feed.balance_feed_id
1833 FROM pay_balance_feeds_f feed,
1834 pay_input_values_f iv,
1835 pay_balance_classifications bc
1836 WHERE iv.element_type_id = p_element_type_id
1837 AND iv.name = 'Pay Value'
1838 AND feed.input_value_id = iv.input_value_id
1839 AND bc.classification_id
1840 = p_sub_classification_id
1841 AND feed.balance_type_id
1842 = bc.balance_type_id)
1843 AND effective_start_date <= p_session_date
1844 AND effective_end_date >= p_session_date;
1845 --
1846 DELETE FROM pay_balance_feeds_f
1847 WHERE balance_feed_id in
1848 (SELECT feed.balance_feed_id
1849 FROM pay_balance_feeds_f feed,
1850 pay_input_values_f iv,
1851 pay_balance_classifications bc
1852 WHERE iv.element_type_id = p_element_type_id
1853 AND iv.name = 'Pay Value'
1854 AND feed.input_value_id = iv.input_value_id
1855 AND bc.classification_id
1856 = p_sub_classification_id
1857 AND feed.balance_type_id
1858 = bc.balance_type_id)
1859 AND effective_start_date > p_session_date;
1860 --
1861 ELSIF (p_delete_mode = 'DELETE_NEXT_CHANGE' AND
1862 p_effective_end_date is not NULL AND
1863 p_session_date is not NULL) THEN
1864 --
1865 UPDATE pay_balance_feeds_f
1866 SET effective_end_date = p_effective_end_date
1867 WHERE balance_feed_id in
1868 (SELECT feed.balance_feed_id
1869 FROM pay_balance_feeds_f feed,
1870 pay_input_values_f iv,
1871 pay_balance_classifications bc
1872 WHERE iv.element_type_id = p_element_type_id
1873 AND iv.name = 'Pay Value'
1874 AND feed.input_value_id = iv.input_value_id
1875 AND bc.classification_id
1876 = p_sub_classification_id
1877 AND feed.balance_type_id
1878 = bc.balance_type_id)
1879 AND effective_start_date <= p_session_date
1880 AND effective_end_date >= p_session_date;
1881 --
1882 DELETE FROM pay_balance_feeds_f
1883 WHERE balance_feed_id in
1884 (SELECT feed.balance_feed_id
1885 FROM pay_balance_feeds_f feed,
1886 pay_input_values_f iv,
1887 pay_balance_classifications bc
1888 WHERE iv.element_type_id = p_element_type_id
1889 AND iv.name = 'Pay Value'
1890 AND feed.input_value_id = iv.input_value_id
1891 AND bc.classification_id
1892 = p_sub_classification_id
1893 AND feed.balance_type_id
1894 = bc.balance_type_id)
1895 AND effective_start_date > p_session_date;
1896 --
1897 ELSE
1898 --
1899 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1900 hr_utility.raise_error;
1901 --
1902 END IF;
1903 --
1904 ELSE
1905 --
1906 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1907 hr_utility.raise_error;
1908 --
1909 END IF;
1910 --
1911 ELSIF p_option = 'DEL_PRIMARY_BAL_CLASS' THEN
1912 --
1913 hr_utility.set_location('DEL_PRIMARY_BAL_CLASS', 1);
1914 --
1915 IF (p_primary_classification_id is not NULL AND
1916 p_balance_type_id is not NULL AND
1917 p_legislation_code is not NULL) THEN
1918 IF p_delete_mode = 'ZAP' THEN
1919 --
1920 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1921 --
1922 IF p_mode <> 'USER' THEN
1923 DELETE FROM hr_application_ownerships
1924 WHERE KEY_NAME = 'BALANCE_FEED_ID'
1925 AND KEY_VALUE IN
1926 (SELECT balance_feed_id
1927 FROM pay_balance_feeds_f
1928 WHERE balance_type_id = p_balance_type_id
1929 AND input_value_id IN
1930 (SELECT iv.input_value_id
1931 FROM pay_input_values_f iv,
1932 pay_element_types_f et
1933 WHERE iv.element_type_id = et.element_type_id
1934 and et.classification_id
1935 = p_primary_classification_id
1936 AND iv.name = 'Pay Value'));
1937 END IF;
1938 --
1939 DELETE FROM pay_balance_feeds_f
1940 WHERE balance_type_id = p_balance_type_id
1941 AND input_value_id in
1942 (SELECT iv.input_value_id
1943 FROM pay_input_values_f iv,
1944 pay_element_types_f et
1945 WHERE iv.element_type_id = et.element_type_id
1946 and et.classification_id
1947 = p_primary_classification_id
1948 AND iv.name = 'Pay Value');
1949 --
1950 ELSE
1951 --
1952 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1953 hr_utility.raise_error;
1954 --
1955 END IF;
1956 --
1957 ELSE
1958 --
1959 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
1960 hr_utility.raise_error;
1961 --
1962 END IF;
1963 --
1964 ELSIF p_option = 'DEL_SUB_BAL_CLASS' THEN
1965 --
1966 hr_utility.set_location('DEL_SUB_BAL_CLASS', 1);
1967 --
1968 IF (p_sub_classification_id is not NULL AND
1969 p_balance_type_id is not NULL AND
1970 p_legislation_code is not NULL) THEN
1971 IF p_delete_mode = 'ZAP' THEN
1972 --
1973 v_pay_value := hr_input_values.get_pay_value_name(p_legislation_code);
1974 --
1975 IF p_mode <> 'USER' THEN
1976 DELETE FROM hr_application_ownerships
1977 WHERE KEY_NAME = 'BALANCE_FEED_ID'
1978 AND KEY_VALUE IN
1979 (SELECT balance_feed_id
1980 FROM pay_balance_feeds_f
1981 WHERE balance_type_id = p_balance_type_id
1982 AND input_value_id IN
1983 (SELECT iv.input_value_id
1984 FROM pay_input_values_f iv,
1985 pay_sub_classification_rules_f scr
1986 WHERE iv.element_type_id = scr.element_type_id
1987 and scr.classification_id
1988 = p_sub_classification_id
1989 AND iv.name = 'Pay Value'
1990 AND iv.effective_end_date
1991 >= scr.effective_start_date
1992 AND iv.effective_start_date
1993 <= scr.effective_end_date));
1994 END IF;
1995 --
1996 DELETE FROM pay_balance_feeds_f
1997 WHERE balance_type_id = p_balance_type_id
1998 AND input_value_id in
1999 (SELECT iv.input_value_id
2000 FROM pay_input_values_f iv,
2001 pay_sub_classification_rules_f scr
2002 WHERE iv.element_type_id = scr.element_type_id
2003 and scr.classification_id
2004 = p_sub_classification_id
2005 AND iv.name = 'Pay Value'
2006 AND iv.effective_end_date
2007 >= scr.effective_start_date
2008 AND iv.effective_start_date
2009 <= scr.effective_end_date);
2010 --
2011 ELSE
2012 --
2013 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
2014 hr_utility.raise_error;
2015 --
2016 END IF;
2017 --
2018 ELSE
2019 --
2020 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
2021 hr_utility.raise_error;
2022 --
2023 END IF;
2024 --
2025 ELSE
2026 --
2027 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
2028 hr_utility.raise_error;
2029 --
2030 END IF;
2031 --
2032 END del_balance_feed;
2033 --
2034 PROCEDURE del_balance_type_cascade
2035 (p_balance_type_id in number,
2036 p_legislation_code in varchar2,
2037 p_mode in varchar2) IS
2038 /*
2039 NAME
2040 del_balance_type_cascade
2041 DESCRIPTION
2042 Cascade delete children records of balance type record,
2043 Balance dimensions, Balance classifications, Balance feeds.
2044 Also delete of pay_balance_attributes when a defined balance is
2045 deleted.
2046 */
2047 --
2048 cursor get_def_bals(p_bal_type number)
2049 is
2050 select db.defined_balance_id
2051 from pay_defined_balances db
2052 where db.balance_type_id = p_bal_type;
2053 --
2054 BEGIN
2055 --
2056 hr_utility.set_location('del_balance_type_cascade', 1);
2057 --
2058 IF p_balance_type_id is not NULL THEN
2059 --
2060 for each_def_bal in get_def_bals(p_balance_type_id) loop
2061 --
2062 -- call chk_delete_defined_balance, which will delete org_pay_methods,
2063 -- backpay rules, and balance attributes.
2064 --
2065 pay_defined_balances_pkg.chk_delete_defined_balance
2066 (each_def_bal.defined_balance_id);
2067 --
2068 DELETE FROM pay_defined_balances
2069 WHERE defined_balance_id = each_def_bal.defined_balance_id;
2070 --
2071 end loop;
2072 --
2073 DELETE FROM pay_balance_classifications
2074 WHERE balance_type_id = p_balance_type_id;
2075 --
2076 IF p_mode <> 'USER' THEN
2077 DELETE FROM hr_application_ownerships
2078 WHERE KEY_NAME = 'BALANCE_FEED_ID'
2079 AND KEY_VALUE IN
2080 (SELECT balance_feed_id
2081 FROM pay_balance_feeds_f
2082 WHERE balance_type_id = p_balance_type_id);
2083 END IF;
2084 --
2085 DELETE FROM pay_balance_feeds_f
2086 WHERE balance_type_id = p_balance_type_id;
2087 --
2088 ELSE
2089 --
2090 hr_utility.set_message (801,'PAY_6541_PRO_INVALID_ARGS');
2091 hr_utility.raise_error;
2092 --
2093 END IF;
2094 --
2095 END del_balance_type_cascade;
2096 --
2097 /*
2098 NAME
2099 chk_ins_sub_class_rules
2100 DESCRIPTION
2101 Checks to see if it is ok to insert a sub classification rule. If it
2102 is then a new end date will be returned. This will be either the last
2103 end date of the element type or the last date the rule will not
2104 overlap with another sub_classification_rule.
2105 */
2106 FUNCTION chk_ins_sub_class_rules(
2107 p_sub_class_rule_id number,
2108 p_element_type_id number,
2109 p_classification_id number,
2110 p_session_date date) return date is
2111 --
2112 l_greatest_date date := NULL;
2113 --
2114 CURSOR get_dup_class_rules(p_element_type_id number,
2115 p_classification_id number) is
2116 select subcr.effective_start_date start_date,
2117 subcr.effective_end_date end_date
2118 from pay_sub_classification_rules_f subcr
2119 where p_element_type_id = subcr.element_type_id
2120 and subcr.sub_classification_rule_id <> p_sub_class_rule_id
2121 and p_classification_id = subcr.classification_id
2122 order by subcr.effective_start_date;
2123 begin
2124 --
2125 -- loop through all the duplicate sub classification rules. If they only
2126 -- start in the future then we need to record the first start date we come to
2127 -- as this -1 will be the effective_end_date of the new rule.
2128
2129 for subcr_rec in get_dup_class_rules(
2130 p_element_type_id,
2131 p_classification_id) loop
2132 --
2133 if p_session_date between subcr_rec.start_date and
2134 subcr_rec.end_date then
2135 --
2136 hr_utility.set_message(801,'PAY_6602_ELEMENT_NO_DUP_SUBCR');
2137 hr_utility.raise_error;
2138 --
2139 else
2140 l_greatest_date := subcr_rec.start_date - 1;
2141 exit;
2142 end if;
2143 end loop;
2144 --
2145 -- If the greatest rule date is still null then the greatest end date will
2146 -- be the last end date of the element type
2147 if l_greatest_date is null then
2148 --
2149 select max(et.effective_end_date)
2150 into l_greatest_date
2151 from pay_element_types_f et
2152 where p_element_type_id = et.element_type_id;
2153 --
2154 end if;
2155 --
2156 return(l_greatest_date);
2157 --
2158 end chk_ins_sub_class_rules;
2159 --
2160 --
2161 /*
2162 NAME
2163 chk_ins_balance_feed
2164 DESCRIPTION
2165 Checks to see if it is ok to insert a manual balance feed. If it
2166 is then a new end date will be returned. This will be either the last
2167 end date of the element type or the last date the feed will not
2168 overlap with another balance feed.
2169 */
2170 FUNCTION chk_ins_balance_feed(
2171 p_balance_feed_id number,
2172 p_input_value_id number,
2173 p_balance_type_id number,
2174 p_session_date date) return date is
2175 --
2176 l_greatest_date date := NULL;
2177 --
2178 CURSOR get_dup_balance_feeds(p_input_value_id number,
2179 p_balance_type_id number) is
2180 select bf.effective_start_date start_date,
2181 bf.effective_end_date end_date
2182 from pay_balance_feeds_f bf
2183 where p_input_value_id = bf.input_value_id
2184 and p_balance_feed_id <> bf.balance_feed_id
2185 and p_balance_type_id = bf.balance_type_id
2186 order by bf.effective_start_date;
2187 begin
2188 --
2189 -- loop through all the duplicate balance feeds. If they only
2190 -- start in the future then we need to record the first start date we come to
2191 -- as this -1 will be the effective_end_date of the new rule.
2192
2193 for bf_rec in get_dup_balance_feeds(
2194 p_input_value_id,
2195 p_balance_type_id) loop
2196 --
2197 if p_session_date between bf_rec.start_date and
2198 bf_rec.end_date then
2199 --
2200 hr_utility.set_message(801,'HR_6185_BAL_FEED_EXISTS');
2201 hr_utility.raise_error;
2202 --
2203 else
2204 l_greatest_date := bf_rec.start_date - 1;
2205 exit;
2206 end if;
2207 end loop;
2208 --
2209 -- If the greatest rule date is still null then the greatest end date will
2210 -- be the last end date of the input value
2211 if l_greatest_date is null then
2212 --
2213 select max(iv.effective_end_date)
2214 into l_greatest_date
2215 from pay_input_values_f iv
2216 where p_input_value_id = iv.input_value_id;
2217 --
2218 end if;
2219 --
2220 return(l_greatest_date);
2221 --
2222 end chk_ins_balance_feed;
2223
2224 /*
2225 NAME
2226 decode_balance
2227 DESCRIPTION
2228 This function looks in the table pay_balance_types_tl for balance type id passed to
2229 the function. This function is called for the creation of the view
2230 pay_us_earnings_amounts_v which is in the payusblv.odf.
2231 */
2232
2233 function DECODE_balance ( p_balance_type_id number) return varchar2 is
2234 --
2235 cursor csr_balance is
2236 select balance_name
2237 from pay_balance_types_tl pbt
2238 where balance_type_id = p_balance_type_id
2239 and language = USERENV('LANG') ;
2240 --
2241 v_balance_name varchar2(80) := null;
2242 --
2243 begin
2244 --
2245 -- Only open the cursor if the parameter is not null
2246 --
2247 if p_balance_type_id is not null then
2248 --
2249 open csr_balance;
2250 fetch csr_balance into v_balance_name;
2251 close csr_balance;
2252 --
2253 end if;
2254
2255 return v_balance_name;
2256
2257 end decode_balance;
2258 --
2259 END hr_balances;
[Home] [Help]