1 package body PAY_SUB_CLASS_RULES_PKG as
2 /* $Header: pysbr.pkb 120.0 2005/05/29 01:50:52 appldev noship $ */
3 --
4 -- Declare global variables and cursors
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,
72 c_user_id,
73 c_login_id,
74 c_user_id,
75 sysdate
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)
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)
167 AND (p_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)
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)
178 OR ( (fetched_record.legislation_code IS NULL)
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;
310 --
311 begin
312 hr_utility.set_location ('pay_sub_class_rules_pkg.allowable_end_date',1);
313 --
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 function NEXT_RULE_ID return number is
331 --
332 --******************************************************************************
333 --* Retrieves next sequence number for rule id.
334 --******************************************************************************
335 --
336 cursor csr_new_row is
337 select pay_sub_classification_rules_s.nextval
338 from sys.dual;
339 --
340 v_next_id number(30);
341 --
342 begin
343 open csr_new_row;
344 fetch csr_new_row into v_next_id;
345 close csr_new_row;
346 return v_next_id;
347 --
348 end next_rule_id;
349 --------------------------------------------------------------------------------
350 procedure MAINTAIN_DELETION_INTEGRITY (
351 --
352 --******************************************************************************
353 --* Ensures that no children of a deleted row are orphaned.
354 --******************************************************************************
355 --
356 -- Parameters are:
357 --
358 p_sub_classification_rule_id number,
359 p_delete_mode varchar2,
360 p_validation_start_date date,
361 p_validation_end_date date ) is
362 --
363 begin
364 --
365 hr_utility.set_location ('pay_sub_class_rules_pkg.MAINTAIN_DELETION_INTEGRITY',1);
366 --
367 -- Delete balance feeds for this sub classification rule
368 hr_balance_feeds.del_bf_sub_class_rule (
369 --
370 p_sub_classification_rule_id,
371 p_delete_mode,
372 p_validation_start_date,
373 p_validation_end_date );
374 --
375 end MAINTAIN_DELETION_INTEGRITY;
376 --------------------------------------------------------------------------------
377 procedure DELETE_ROW (
378 --
379 --******************************************************************************
380 --* Handles deletion from the base table either for forms based on *
381 --* non-updatable view or for implicit deletions caused by action on other *
382 --* entities. *
383 --******************************************************************************
384 --
385 -- Parameters to be passed in are:
386 --
387 p_rowid varchar2,
388 p_sub_classification_rule_id number,
389 p_delete_mode varchar2,
390 p_validation_start_date date,
391 p_validation_end_date date ) is
392 --
393 begin
394 --
395 hr_utility.set_location ('pay_sub_class_rules_pkg.DELETE_ROW',1);
396 --
397 pay_sub_class_rules_pkg.maintain_deletion_integrity (
398 --
399 p_sub_classification_rule_id,
400 p_delete_mode,
401 p_validation_start_date,
402 p_validation_end_date );
403 --
404 -- Delete row from base table
405 --
406 delete from pay_sub_classification_rules_f
407 where rowid = p_rowid;
408 --
409 if sql%notfound then -- system error trap
410 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
411 hr_utility.set_message_token('PROCEDURE',
412 'PAY_SUB_CLASS_RULES_PKG.DELETE_ROW');
413 hr_utility.set_message_token('STEP','2');
414 hr_utility.raise_error;
415 end if;
416 --
417 delete from hr_application_ownerships
418 where key_name = 'SUB_CLASSIFICATION_RULE_ID'
419 and key_value = p_sub_classification_rule_id;
420 --
421 end delete_row;
422 --------------------------------------------------------------------------------
423 procedure PARENT_DELETED (
424 --
425 --******************************************************************************
426 --* Handles the case when any row referenced by a foreign key of the base *
427 --* is deleted (in whatever Date Track mode). ie If a parent record is zapped *
428 --* then the deletion is cascaded; if it is date-effectively deleted, then the *
429 --* rows referencing it are updated to have the same end-date. *
430 --******************************************************************************
431 --
432 -- Parameters to be passed in are:
433 --
434 p_parent_id number,-- The foreign key for the deleted parent
435 p_session_date date default trunc (sysdate),
436 p_validation_start_date date,
437 p_validation_end_date date,
438 p_delete_mode varchar2 default 'DELETE',
439 p_parent_name varchar2 -- The name of the parent entity
440 ) is
441 --
442 -- The following cursor fetches all rows identified by the foreign key to
443 -- the parent being deleted. The parent name identifies foreign key column
444 -- to use, thus the procedure is generic to any parent deletion
445 --
446 cursor csr_rows_owned_by_parent is
447 select rowid,pay_sub_classification_rules_f.*
448 from pay_sub_classification_rules_f
449 where p_parent_id = decode (p_parent_name,
450 'PAY_ELEMENT_TYPES_F',element_type_id,
451 classification_id)
452 for update;
453 --
454 begin
455 hr_utility.set_location ('pay_sub_class_rules_pkg.parent_deleted',1);
456 --
457 <<REMOVE_ORPHANED_ROWS>>
458 for fetched_rule in csr_rows_owned_by_parent LOOP
459 --
460 -- If in ZAP mode then all rows belonging to the deleted
461 -- parent must be deleted. If in DELETE (ie date-effective
462 -- delete) mode then only rows with a future start date
463 -- must be deleted, and current rows must be updated so
464 -- that their end dates match that of their closed-down
465 -- parent. Current and future are determined by session
466 -- date.
467 --
468 if p_delete_mode = 'ZAP' -- ie delete all rows
469 or (p_delete_mode = 'DELETE' -- ie delete all future rows
470 and fetched_rule.effective_start_date > p_session_date) then
471 --
472 delete_row( fetched_rule.rowid,
473 fetched_rule.sub_classification_rule_id,
474 p_delete_mode,
475 p_validation_start_date,
476 p_validation_end_date );
477 --
478 elsif p_delete_mode = 'DELETE'
479 and p_session_date between fetched_rule.effective_start_date
480 and fetched_rule.effective_end_date then
481 --
482 update pay_sub_classification_rules_f
483 set effective_end_date = p_session_date
484 where current of csr_rows_owned_by_parent;
485 --
486 -- Fix for bug 3660866.
487 -- If parent is deleted in DELETE_NEXT_CHANGE or FUTURE_CHANGE mode
488 -- then all child rows (Secondary balance classification) should
489 -- also be updated as per same.
490 elsif (p_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE')) then
491 update pay_sub_classification_rules_f
492 set effective_end_date = p_validation_end_date
493 where current of csr_rows_owned_by_parent;
494 --
495 end if;
496 --
497 end loop remove_orphaned_rows;
498 --
499 end parent_deleted;
500 --------------------------------------------------------------------------------
501 begin
502 --
503 c_user_id := fnd_global.user_id;
504 c_login_id := fnd_global.login_id;
505 --
506 end PAY_SUB_CLASS_RULES_PKG;
[Home] [Help]