4 -- Declare global variables and cursors
1 package body PAY_SUB_CLASS_RULES_PKG as
2 /* $Header: pysbr.pkb 120.0.12020000.2 2012/07/05 02:37:15 amnaraya ship $ */
3 --
5 --
6 -- Dummy variable for selecting into when not interested in the value returned
7 g_dummy number(1);
8 c_user_id number;
9 c_login_id number;
10 --------------------------------------------------------------------------------
11 -- Start of auto-generated code
12 --------------------------------------------------------------------------------
13 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
14 p_Sub_Classification_Rule_Id IN OUT NOCOPY NUMBER,
15 p_Effective_Start_Date DATE,
16 p_Effective_End_Date DATE,
17 p_Element_Type_Id NUMBER,
18 p_Classification_Id NUMBER,
19 p_Business_Group_Id NUMBER,
20 p_Legislation_Code VARCHAR2,
21 p_Last_Update_Date DATE,
22 p_Last_Updated_By NUMBER,
23 p_Last_Update_Login NUMBER,
24 p_Created_By NUMBER,
25 p_Creation_Date DATE) IS
26
27 cursor csr_new_rowid is
28 select rowid
29 from pay_sub_classification_rules_f
30 where sub_classification_rule_id = p_sub_classification_rule_id
31 and effective_start_date = p_effective_start_date;
32
33
34
35 cursor csr_next_id is
36 select pay_sub_classification_rules_s.nextval
37 from sys.dual;
38 BEGIN
39
40 hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',1);
41
42 if p_sub_classification_rule_id is null then
43 open csr_next_id;
44 fetch csr_next_id into p_sub_classification_rule_id;
45 close csr_next_id;
46 end if;
47
48 hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',2);
49 insert into pay_sub_classification_rules_f(
50
51 sub_classification_rule_id,
52 effective_start_date,
53 effective_end_date,
54 element_type_id,
55 classification_id,
56 business_group_id,
57 legislation_code,
58 last_update_date,
59 last_updated_by,
60 last_update_login,
61 created_by,
62 creation_date
63 ) VALUES (
64 p_Sub_Classification_Rule_Id,
65 p_Effective_Start_Date,
66 p_Effective_End_Date,
67 p_Element_Type_Id,
68 p_Classification_Id,
69 p_Business_Group_Id,
70 p_Legislation_Code,
71 sysdate,
75 sysdate
72 c_user_id,
73 c_login_id,
74 c_user_id,
76
77 );
78 hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',3);
79
80 open csr_new_rowid;
81 fetch csr_new_rowid into p_rowid;
82 if csr_new_rowid%notfound then
83 close csr_new_rowid;
84 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
85 hr_utility.set_message_token('PROCEDURE','PAY_SUB_CLASS_RULES_PKG.INSERT_ROW');
86 hr_utility.set_message_token('STEP','1');
87 hr_utility.raise_error;
88 end if;
89 close csr_new_rowid;
90 --
91 -- Create application ownership for startup data
92 if p_legislation_code is not null then
93 --
94 -- The 'not exists' clause is used to ensure that duplicate rows are not
95 -- entered. This could arise because the forms startup code also handles
96 -- application ownerships where a user enters a value on the form, but
97 -- this code is intended to handle third party insertion from the element
98 --
99 hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',4);
100 insert into hr_application_ownerships
101 (key_name,
102 key_value,
103 product_name)
104 select 'SUB_CLASSIFICATION_RULE_ID',
105 p_sub_classification_rule_id,
106 ao.product_name
107 from hr_application_ownerships ao
108 where ao.key_name = 'ELEMENT_TYPE_ID'
109 and ao.key_value = p_element_type_id
110 and not exists (select 'SUB_CLASSIFICATION_RULE_ID',
111 p_sub_classification_rule_id,
112 ao.product_name
113 from hr_application_ownerships ao
114 where ao.key_name = 'ELEMENT_TYPE_ID'
115 and ao.key_value = p_element_type_id);
116 --
117 end if;
118 --
119 hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',5);
120 hr_balance_feeds.ins_bf_sub_class_rule (p_Sub_Classification_Rule_Id);
121
122 end insert_row;
123
124
125
126
127
128 procedure LOCK_ROW(
129
130 p_rowid VARCHAR2,
131 p_Sub_Classification_Rule_Id NUMBER,
132 p_Effective_Start_Date DATE,
133 p_Effective_End_Date DATE,
134 p_Element_Type_Id NUMBER,
135 p_Classification_Id NUMBER,
136 p_Business_Group_Id NUMBER,
137 p_Legislation_Code VARCHAR2) IS
138
139 cursor csr_existing_row is
140 select *
141 from pay_sub_classification_rules_f
142 where rowid = p_rowid
143 for update of sub_classification_rule_id NOWAIT;
144
145 fetched_record csr_existing_row%rowtype;
146
147 begin
148 open csr_existing_row;
149 fetch csr_existing_row into fetched_record;
150 if csr_existing_row%notfound then
151 close csr_existing_row;
152 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
153 hr_utility.set_message_token('PROCEDURE','PAY_SUB_CLASS_RULES_PKG.LOCK_ROW');
154 hr_utility.set_message_token('STEP','1');
155 hr_utility.raise_error;
156 end if;
157 close csr_existing_row;
158 if (
159 ( (fetched_record.sub_classification_rule_id = p_Sub_Classification_Rule_Id)
160 OR ( (fetched_record.sub_classification_rule_id IS NULL)
161 AND (p_Sub_Classification_Rule_Id IS NULL)))
162 AND ( (fetched_record.effective_start_date = p_Effective_Start_Date)
163 OR ( (fetched_record.effective_start_date IS NULL)
167 AND (p_Effective_End_Date IS NULL)))
164 AND (p_Effective_Start_Date IS NULL)))
165 AND ( (fetched_record.effective_end_date = p_Effective_End_Date)
166 OR ( (fetched_record.effective_end_date IS NULL)
168 AND ( (fetched_record.element_type_id = p_Element_Type_Id)
169 OR ( (fetched_record.element_type_id IS NULL)
170 AND (p_Element_Type_Id IS NULL)))
171 AND ( (fetched_record.classification_id = p_Classification_Id)
172 OR ( (fetched_record.classification_id IS NULL)
173 AND (p_Classification_Id IS NULL)))
174 AND ( (fetched_record.business_group_id = p_Business_Group_Id)
178 OR ( (fetched_record.legislation_code IS NULL)
175 OR ( (fetched_record.business_group_id IS NULL)
176 AND (p_Business_Group_Id IS NULL)))
177 AND ( (fetched_record.legislation_code = p_Legislation_Code)
179 AND (p_Legislation_Code IS NULL)))
180 ) then
181 return;
182 else
183 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
184 APP_EXCEPTION.RAISE_EXCEPTION;
185 end if;
186 end Lock_Row;
187
188 PROCEDURE Update_Row(p_Rowid VARCHAR2,
189 p_Sub_Classification_Rule_Id NUMBER,
190 p_Effective_Start_Date DATE,
191 p_Effective_End_Date DATE,
192 p_Element_Type_Id NUMBER,
193 p_Classification_Id NUMBER,
194 p_Business_Group_Id NUMBER,
195 p_Legislation_Code VARCHAR2,
196 p_Last_Update_Date DATE,
197 p_Last_Updated_By NUMBER,
198 p_Last_Update_Login NUMBER) IS
199 BEGIN
200 UPDATE pay_sub_classification_rules_f
201 SET
202
203 sub_classification_rule_id = p_Sub_Classification_Rule_Id,
204 effective_start_date = p_Effective_Start_Date,
205 effective_end_date = p_Effective_End_Date,
206 element_type_id = p_Element_Type_Id,
207 classification_id = p_Classification_Id,
208 business_group_id = p_Business_Group_Id,
209 legislation_code = p_Legislation_Code,
210 last_update_date = sysdate,
211 last_updated_by = c_user_id,
212 last_update_login = c_login_id
213 WHERE rowid = p_rowid;
214
215 if (SQL%NOTFOUND) then
216 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
217 hr_utility.set_message_token('PROCEDURE','PAY_SUB_CLASS_RULES_PKG.UPDATE_ROW');
218 hr_utility.set_message_token('STEP','1');
219 hr_utility.raise_error;
220 end if;
221
222 END Update_Row;
223 --------------------------------------------------------------------------------
224 -- End of Auto-generated code
225 --------------------------------------------------------------------------------
226 procedure INSERT_DEFAULTS (
227 --
228 --******************************************************************************
229 --* Inserts a row into the base table for each default sub-classification *
230 --* belonging to the primary classification of a newly inserted element type *
231 --******************************************************************************
232 --
233 -- Parameters are:
234 --
235 p_element_type_id number,
236 p_classification_id number,
237 p_effective_start_date date,
238 p_effective_end_date date,
239 p_business_group_id number,
240 p_legislation_code varchar2 ) is
241 --
242 cursor csr_legislation_code is
243 select legislation_code
244 from per_business_groups_perf
245 where business_group_id = p_business_group_id;
246 --
247 dummy_rowid varchar2(18) default null;
248 dummy_id number(38) default null;
249 --
250 cursor csr_next_default is
251 select classification_id
252 from pay_element_classifications
253 where parent_classification_id = p_classification_id
254 and create_by_default_flag = 'Y'
255 and (p_business_group_id = business_group_id + 0
256 or (business_group_id is null
257 and (legislation_code =
258 nvl(hr_api.return_legislation_code(p_business_group_id)
259 ,p_legislation_code))
260 ));
261 --
262 begin
263 --
264 hr_utility.set_location ('pay_sub_class_rules_pkg.insert_defaults',1);
265 --
266 for default_insertion in csr_next_default LOOP
267 --
268 insert_row (
269 dummy_rowid,
270 dummy_id,
271 p_effective_start_date,
272 p_effective_end_date,
273 p_element_type_id,
274 default_insertion.classification_id,
275 p_business_group_id,
276 p_legislation_code,
277 null,null,null,null,null);
278 --
279 dummy_rowid := null;
280 dummy_id := null;
281 --
282 end loop;
283 --
284 end insert_defaults;
285 --------------------------------------------------------------------------------
286 function MAX_ALLOWABLE_END_DATE (
287 --
288 --******************************************************************************
289 --* Returns date of the last allowable end date which may be used for a row *
290 --* in order to maintain its uniqueness within time. *
291 --******************************************************************************
292 --
293 -- Parameters to be passed in are:
294 --
295 p_element_type_id number,
296 p_classification_id number,
297 p_session_date date,
298 p_error_if_true boolean default FALSE )
299 --
300 return date is
301 --
302 v_end_date date;
303 --
304 cursor csr_end_date is
305 select min(effective_start_date) -1
306 from pay_sub_classification_rules_f
307 where element_type_id = p_element_type_id
308 and classification_id = p_classification_id
309 and effective_end_date > p_session_date;
313 --
310 --
311 begin
312 hr_utility.set_location ('pay_sub_class_rules_pkg.allowable_end_date',1);
314 open csr_end_date;
315 fetch csr_end_date into v_end_date;
316 close csr_end_date;
317 --
318 hr_utility.trace ('End Date = '||v_end_date);
319 --
320 -- Return an error if the maximum allowable end date is prior to session date
321 if p_error_if_true and v_end_date <= p_session_date then
322 hr_utility.set_message (801,'HR_7128_SUB_CLASS_OVERLAPS');
323 hr_utility.raise_error;
324 end if;
325 --
326 return v_end_date;
327 --
328 end max_allowable_end_date;
329 --------------------------------------------------------------------------------
330
331 function MAX_ALLOWABLE_END_DATE (
332 --
333 --******************************************************************************
334 --* Returns date of the last allowable end date which may be used for a row *
335 --* in order to maintain its uniqueness within time. *
336 --******************************************************************************
337 --
338 -- Parameters to be passed in are:
339 --
340 p_element_type_id number,
341 p_classification_id number,
342 p_session_date date,
343 p_legislation_code varchar2,
344 p_business_group_id varchar2,
345 p_error_if_true boolean default FALSE )
346 --
347 return date is
348 --
349 v_end_date date;
350 --
351 cursor csr_end_date is
352 select min(effective_start_date) -1
353 from pay_sub_classification_rules_f
354 where element_type_id = p_element_type_id
355 and classification_id = p_classification_id
356 and effective_end_date > p_session_date
357 and nvl(legislation_code, nvl(p_legislation_code,'~~nvl~~')) = nvl(p_legislation_code,'~~nvl~~')
358 and nvl(business_group_id,nvl(p_business_group_id,-1)) = nvl(p_business_group_id,-1);
359
360 --
361 begin
362 hr_utility.set_location ('pay_sub_class_rules_pkg.allowable_end_date',1);
363 --
364 open csr_end_date;
365 fetch csr_end_date into v_end_date;
366 close csr_end_date;
367 --
368 hr_utility.trace ('End Date = '||v_end_date);
369 --
370 -- Return an error if the maximum allowable end date is prior to session date
371 if p_error_if_true and v_end_date <= p_session_date then
372 hr_utility.set_message (801,'HR_7128_SUB_CLASS_OVERLAPS');
373 hr_utility.raise_error;
374 end if;
375 --
376 return v_end_date;
377 --
378 end max_allowable_end_date;
379 ----------------------------------------------------------------------------------------------------
380
381 function NEXT_RULE_ID return number is
382 --
383 --******************************************************************************
384 --* Retrieves next sequence number for rule id.
385 --******************************************************************************
386 --
387 cursor csr_new_row is
388 select pay_sub_classification_rules_s.nextval
389 from sys.dual;
390 --
391 v_next_id number(30);
392 --
393 begin
394 open csr_new_row;
395 fetch csr_new_row into v_next_id;
396 close csr_new_row;
397 return v_next_id;
398 --
399 end next_rule_id;
400 --------------------------------------------------------------------------------
401 procedure MAINTAIN_DELETION_INTEGRITY (
402 --
403 --******************************************************************************
404 --* Ensures that no children of a deleted row are orphaned.
405 --******************************************************************************
406 --
407 -- Parameters are:
408 --
409 p_sub_classification_rule_id number,
410 p_delete_mode varchar2,
411 p_validation_start_date date,
412 p_validation_end_date date ) is
413 --
414 begin
415 --
416 hr_utility.set_location ('pay_sub_class_rules_pkg.MAINTAIN_DELETION_INTEGRITY',1);
417 --
418 -- Delete balance feeds for this sub classification rule
419 hr_balance_feeds.del_bf_sub_class_rule (
420 --
421 p_sub_classification_rule_id,
422 p_delete_mode,
423 p_validation_start_date,
424 p_validation_end_date );
425 --
426 end MAINTAIN_DELETION_INTEGRITY;
427 --------------------------------------------------------------------------------
428 procedure DELETE_ROW (
429 --
430 --******************************************************************************
431 --* Handles deletion from the base table either for forms based on *
432 --* non-updatable view or for implicit deletions caused by action on other *
433 --* entities. *
434 --******************************************************************************
435 --
436 -- Parameters to be passed in are:
437 --
438 p_rowid varchar2,
439 p_sub_classification_rule_id number,
440 p_delete_mode varchar2,
441 p_validation_start_date date,
442 p_validation_end_date date ) is
443 --
444 begin
445 --
446 hr_utility.set_location ('pay_sub_class_rules_pkg.DELETE_ROW',1);
447 --
448 pay_sub_class_rules_pkg.maintain_deletion_integrity (
449 --
450 p_sub_classification_rule_id,
451 p_delete_mode,
452 p_validation_start_date,
453 p_validation_end_date );
454 --
455 -- Delete row from base table
456 --
457 delete from pay_sub_classification_rules_f
458 where rowid = p_rowid;
459 --
460 if sql%notfound then -- system error trap
461 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
462 hr_utility.set_message_token('PROCEDURE',
463 'PAY_SUB_CLASS_RULES_PKG.DELETE_ROW');
467 --
464 hr_utility.set_message_token('STEP','2');
465 hr_utility.raise_error;
466 end if;
468 delete from hr_application_ownerships
469 where key_name = 'SUB_CLASSIFICATION_RULE_ID'
470 and key_value = p_sub_classification_rule_id;
471 --
472 end delete_row;
473 --------------------------------------------------------------------------------
474 procedure PARENT_DELETED (
475 --
476 --******************************************************************************
477 --* Handles the case when any row referenced by a foreign key of the base *
478 --* is deleted (in whatever Date Track mode). ie If a parent record is zapped *
479 --* then the deletion is cascaded; if it is date-effectively deleted, then the *
480 --* rows referencing it are updated to have the same end-date. *
481 --******************************************************************************
482 --
483 -- Parameters to be passed in are:
484 --
485 p_parent_id number,-- The foreign key for the deleted parent
486 p_session_date date default trunc (sysdate),
487 p_validation_start_date date,
488 p_validation_end_date date,
489 p_delete_mode varchar2 default 'DELETE',
490 p_parent_name varchar2 -- The name of the parent entity
491 ) is
492 --
493 -- The following cursor fetches all rows identified by the foreign key to
494 -- the parent being deleted. The parent name identifies foreign key column
495 -- to use, thus the procedure is generic to any parent deletion
496 --
497 cursor csr_rows_owned_by_parent is
498 select rowid,pay_sub_classification_rules_f.*
499 from pay_sub_classification_rules_f
500 where p_parent_id = decode (p_parent_name,
501 'PAY_ELEMENT_TYPES_F',element_type_id,
502 classification_id)
503 for update;
504 --
505 begin
506 hr_utility.set_location ('pay_sub_class_rules_pkg.parent_deleted',1);
507 --
508 <<REMOVE_ORPHANED_ROWS>>
509 for fetched_rule in csr_rows_owned_by_parent LOOP
510 --
511 -- If in ZAP mode then all rows belonging to the deleted
512 -- parent must be deleted. If in DELETE (ie date-effective
513 -- delete) mode then only rows with a future start date
514 -- must be deleted, and current rows must be updated so
515 -- that their end dates match that of their closed-down
516 -- parent. Current and future are determined by session
517 -- date.
518 --
519 if p_delete_mode = 'ZAP' -- ie delete all rows
520 or (p_delete_mode = 'DELETE' -- ie delete all future rows
521 and fetched_rule.effective_start_date > p_session_date) then
522 --
523 delete_row( fetched_rule.rowid,
524 fetched_rule.sub_classification_rule_id,
525 p_delete_mode,
526 p_validation_start_date,
527 p_validation_end_date );
528 --
529 elsif p_delete_mode = 'DELETE'
530 and p_session_date between fetched_rule.effective_start_date
531 and fetched_rule.effective_end_date then
532 --
533 update pay_sub_classification_rules_f
534 set effective_end_date = p_session_date
535 where current of csr_rows_owned_by_parent;
536 --
537 -- Fix for bug 3660866.
538 -- If parent is deleted in DELETE_NEXT_CHANGE or FUTURE_CHANGE mode
539 -- then all child rows (Secondary balance classification) should
540 -- also be updated as per same.
541 elsif (p_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE')) then
542 update pay_sub_classification_rules_f
543 set effective_end_date = p_validation_end_date
544 where current of csr_rows_owned_by_parent;
545 --
546 end if;
547 --
548 end loop remove_orphaned_rows;
549 --
550 end parent_deleted;
551 --------------------------------------------------------------------------------
552 begin
553 --
554 c_user_id := fnd_global.user_id;
555 c_login_id := fnd_global.login_id;
556 --
557 end PAY_SUB_CLASS_RULES_PKG;