1 PACKAGE BODY pay_batch_element_link_pkg AS
2 /* $Header: pybel.pkb 120.1 2006/09/26 15:01:02 thabara noship $ */
3
4 --------------------------------------------------------------------------------
5 procedure insert_row
6 (p_rowid in out nocopy varchar2
7 ,p_batch_element_link_id in out nocopy number
8 ,p_element_link_id in number
9 ,p_effective_date in date
10 ,p_payroll_id in number
11 ,p_job_id in number
12 ,p_position_id in number
13 ,p_people_group_id in number
14 ,p_cost_allocation_keyflex_id in number
15 ,p_organization_id in number
16 ,p_element_type_id in number
17 ,p_location_id in number
18 ,p_grade_id in number
19 ,p_balancing_keyflex_id in number
20 ,p_business_group_id in number
21 ,p_element_set_id in number
22 ,p_pay_basis_id in number
23 ,p_costable_type in varchar2
24 ,p_link_to_all_payrolls_flag in varchar2
25 ,p_multiply_value_flag in varchar2
26 ,p_standard_link_flag in varchar2
27 ,p_transfer_to_gl_flag in varchar2
28 ,p_comment_id in number
29 ,p_employment_category in varchar2
30 ,p_qualifying_age in number
31 ,p_qualifying_length_of_service in number
32 ,p_qualifying_units in varchar2
33 ,p_attribute_category in varchar2
34 ,p_attribute1 in varchar2
35 ,p_attribute2 in varchar2
36 ,p_attribute3 in varchar2
37 ,p_attribute4 in varchar2
38 ,p_attribute5 in varchar2
39 ,p_attribute6 in varchar2
40 ,p_attribute7 in varchar2
41 ,p_attribute8 in varchar2
42 ,p_attribute9 in varchar2
43 ,p_attribute10 in varchar2
44 ,p_attribute11 in varchar2
45 ,p_attribute12 in varchar2
46 ,p_attribute13 in varchar2
47 ,p_attribute14 in varchar2
48 ,p_attribute15 in varchar2
49 ,p_attribute16 in varchar2
50 ,p_attribute17 in varchar2
51 ,p_attribute18 in varchar2
52 ,p_attribute19 in varchar2
53 ,p_attribute20 in varchar2
54 ,p_object_status in varchar2
55 )
56 is
57 cursor csr_new_rowid is
58 select rowid
59 from pay_batch_element_links
60 where batch_element_link_id = p_batch_element_link_id
61 ;
62 --
63 cursor csr_next_ID is
64 select pay_element_links_s.nextval
65 from sys.dual;
66 --
67 l_proc varchar2(72):= 'pay_batch_element_link_pkg.insert_row';
68 begin
69 --
70 hr_utility.set_location('Entering: '||l_proc, 5);
71 --
72 if p_batch_element_link_id is null then
73 open csr_next_ID;
74 fetch csr_next_ID into p_batch_element_link_id;
75 close csr_next_ID;
76 end if;
77 --
78 insert into pay_batch_element_links(
79 --
80 batch_element_link_id,
81 effective_date,
82 element_link_id,
83 payroll_id,
84 job_id,
85 position_id,
86 people_group_id,
87 cost_allocation_keyflex_id,
88 organization_id,
89 element_type_id,
90 location_id,
91 grade_id,
92 balancing_keyflex_id,
93 business_group_id,
94 element_set_id,
95 pay_basis_id,
96 costable_type,
97 link_to_all_payrolls_flag,
98 multiply_value_flag,
99 standard_link_flag,
100 transfer_to_gl_flag,
101 comment_id,
102 employment_category,
103 qualifying_age,
104 qualifying_length_of_service,
105 qualifying_units,
106 attribute_category,
107 attribute1,
108 attribute2,
109 attribute3,
110 attribute4,
111 attribute5,
112 attribute6,
113 attribute7,
114 attribute8,
115 attribute9,
116 attribute10,
117 attribute11,
118 attribute12,
119 attribute13,
120 attribute14,
121 attribute15,
122 attribute16,
123 attribute17,
124 attribute18,
125 attribute19,
126 attribute20)
127 values (
128 p_batch_element_link_id,
129 p_effective_date,
130 p_element_link_id,
131 p_payroll_id,
132 p_job_id,
133 p_position_id,
134 p_people_group_id,
135 p_cost_allocation_keyflex_id,
136 p_organization_id,
137 p_element_type_id,
138 p_location_id,
139 p_grade_id,
140 p_balancing_keyflex_id,
141 p_business_group_id,
142 p_element_set_id,
143 p_pay_basis_id,
144 p_costable_type,
145 p_link_to_all_payrolls_flag,
146 p_multiply_value_flag,
147 p_standard_link_flag,
148 p_transfer_to_gl_flag,
149 p_comment_id,
150 p_employment_category,
151 p_qualifying_age,
152 p_qualifying_length_of_service,
153 p_qualifying_units,
154 p_attribute_category,
155 p_attribute1,
156 p_attribute2,
157 p_attribute3,
158 p_attribute4,
159 p_attribute5,
160 p_attribute6,
161 p_attribute7,
162 p_attribute8,
163 p_attribute9,
164 p_attribute10,
165 p_attribute11,
166 p_attribute12,
167 p_attribute13,
168 p_attribute14,
169 p_attribute15,
170 p_attribute16,
171 p_attribute17,
172 p_attribute18,
173 p_attribute19,
174 p_attribute20);
175 --
176 open csr_new_rowid;
177 fetch csr_new_rowid into p_rowid;
178 if (csr_new_rowid%notfound) then
179 close csr_new_rowid;
180 raise no_data_found;
181 end if;
182 --
183 pay_batch_object_status_pkg.set_status
184 (p_object_type => 'BEL'
185 ,p_object_id => p_batch_element_link_id
186 ,p_object_status => p_object_status
187 ,p_payroll_action_id => null
188 );
189 --
190 hr_utility.set_location('Leaving: '||l_proc, 100);
191 --
192 end insert_row;
193 --
194 -------------------------------------------------------------------------------
195 procedure lock_row
196 (p_rowid in varchar2
197 ,p_batch_element_link_id in number
198 ,p_element_link_id in number
199 ,p_effective_date in date
200 ,p_payroll_id in number
201 ,p_job_id in number
202 ,p_position_id in number
203 ,p_people_group_id in number
204 ,p_cost_allocation_keyflex_id in number
205 ,p_organization_id in number
206 ,p_element_type_id in number
207 ,p_location_id in number
208 ,p_grade_id in number
209 ,p_balancing_keyflex_id in number
210 ,p_business_group_id in number
211 ,p_element_set_id in number
212 ,p_pay_basis_id in number
213 ,p_costable_type in varchar2
214 ,p_link_to_all_payrolls_flag in varchar2
215 ,p_multiply_value_flag in varchar2
216 ,p_standard_link_flag in varchar2
217 ,p_transfer_to_gl_flag in varchar2
218 ,p_comment_id in number
219 ,p_employment_category in varchar2
220 ,p_qualifying_age in number
221 ,p_qualifying_length_of_service in number
222 ,p_qualifying_units in varchar2
223 ,p_attribute_category in varchar2
224 ,p_attribute1 in varchar2
225 ,p_attribute2 in varchar2
226 ,p_attribute3 in varchar2
227 ,p_attribute4 in varchar2
228 ,p_attribute5 in varchar2
229 ,p_attribute6 in varchar2
230 ,p_attribute7 in varchar2
231 ,p_attribute8 in varchar2
232 ,p_attribute9 in varchar2
233 ,p_attribute10 in varchar2
234 ,p_attribute11 in varchar2
235 ,p_attribute12 in varchar2
236 ,p_attribute13 in varchar2
237 ,p_attribute14 in varchar2
238 ,p_attribute15 in varchar2
239 ,p_attribute16 in varchar2
240 ,p_attribute17 in varchar2
241 ,p_attribute18 in varchar2
242 ,p_attribute19 in varchar2
243 ,p_attribute20 in varchar2
244 ,p_object_status in varchar2
245 )
246 is
247 --
248 cursor csr_locked_row is
249 select *
250 from pay_batch_element_links
251 where rowid = p_rowid
252 for update NOWAIT;
253 --
254 locked_row csr_locked_row%rowtype;
255 --
256 l_proc varchar2(72):= 'pay_batch_element_link_pkg.lock_row';
257 begin
258 --
259 hr_utility.set_location('Entering: '||l_proc, 5);
260 --
261 --
262 open csr_locked_row;
263 fetch csr_locked_row into locked_row;
264 if csr_locked_row%notfound then
265 close csr_locked_row;
266 raise no_data_found;
267 end if;
268 close csr_locked_row;
269 --
270 if ( ( (locked_row.batch_element_link_id = p_batch_element_link_id)
271 or ((locked_row.batch_element_link_id is null)
272 and (p_batch_element_link_id is null)))
273 and ( (locked_row.element_link_id = p_element_link_id)
274 or ((locked_row.element_link_id is null)
275 and (p_element_link_id is null)))
276 and ( (locked_row.effective_date = p_effective_date)
277 or ((locked_row.effective_date is null)
278 and (p_effective_date is null)))
279 and ( (locked_row.payroll_id = p_payroll_id)
280 or ((locked_row.payroll_id is null)
281 and (p_payroll_id is null)))
282 and ( (locked_row.job_id = p_job_id)
283 or ((locked_row.job_id is null)
284 and (p_job_id is null)))
285 and ( (locked_row.position_id = p_position_id)
286 or ((locked_row.position_id is null)
287 and (p_position_id is null)))
288 and ( (locked_row.people_group_id = p_people_group_id)
289 or ((locked_row.people_group_id is null)
290 and (p_people_group_id is null)))
291 and ( (locked_row.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id)
292 or ((locked_row.cost_allocation_keyflex_id is null)
293 and (p_cost_allocation_keyflex_id is null)))
294 and ( (locked_row.organization_id = p_organization_id)
295 or ((locked_row.organization_id is null)
296 and (p_organization_id is null)))
297 and ( (locked_row.element_type_id = p_element_type_id)
298 or ((locked_row.element_type_id is null)
299 and (p_element_type_id is null)))
300 and ( (locked_row.location_id = p_location_id)
301 or ((locked_row.location_id is null)
302 and (p_location_id is null)))
303 and ( (locked_row.grade_id = p_grade_id)
304 or ((locked_row.grade_id is null)
305 and (p_grade_id is null)))
306 and ( (locked_row.balancing_keyflex_id = p_balancing_keyflex_id)
307 or ((locked_row.balancing_keyflex_id is null)
308 and (p_balancing_keyflex_id is null)))
309 and ( (locked_row.business_group_id = p_business_group_id)
310 or ((locked_row.business_group_id is null)
311 and (p_business_group_id is null)))
312 and ( (locked_row.element_set_id = p_element_set_id)
313 or ((locked_row.element_set_id is null)
314 and (p_element_set_id is null)))
315 and ( (locked_row.pay_basis_id = p_pay_basis_id)
316 or ((locked_row.pay_basis_id is null)
317 and (p_pay_basis_id is null)))
318 and ( (locked_row.costable_type = p_costable_type)
319 or ((locked_row.costable_type is null)
320 and (p_costable_type is null)))
321 and ( (locked_row.link_to_all_payrolls_flag = p_link_to_all_payrolls_flag)
322 or ((locked_row.link_to_all_payrolls_flag is null)
326 and (p_multiply_value_flag is null)))
323 and (p_link_to_all_payrolls_flag is null)))
324 and ( (locked_row.multiply_value_flag = p_multiply_value_flag)
325 or ((locked_row.multiply_value_flag is null)
327 and ( (locked_row.standard_link_flag = p_standard_link_flag)
328 or ((locked_row.standard_link_flag is null)
329 and (p_standard_link_flag is null)))
330 and ( (locked_row.transfer_to_gl_flag = p_transfer_to_gl_flag)
331 or ((locked_row.transfer_to_gl_flag is null)
332 and (p_transfer_to_gl_flag is null)))
333 and ( (locked_row.comment_id = p_comment_id)
334 or ((locked_row.comment_id is null)
335 and (p_comment_id is null)))
336 and ( (locked_row.employment_category = p_employment_category)
337 or ((locked_row.employment_category is null)
338 and (p_employment_category is null)))
339 and ( (locked_row.qualifying_age = p_qualifying_age)
340 or ((locked_row.qualifying_age is null)
341 and (p_qualifying_age is null)))
342 and ( (locked_row.qualifying_length_of_service = p_qualifying_length_of_service)
343 or ((locked_row.qualifying_length_of_service is null)
344 and (p_qualifying_length_of_service is null)))
345 and ( (locked_row.qualifying_units = p_qualifying_units)
346 or ((locked_row.qualifying_units is null)
347 and (p_qualifying_units is null)))
348 and ( (locked_row.attribute_category = p_attribute_category)
349 or ((locked_row.attribute_category is null)
350 and (p_attribute_category is null)))
351 and ( (locked_row.attribute1 = p_attribute1)
352 or ((locked_row.attribute1 is null)
353 and (p_attribute1 is null)))
354 and ( (locked_row.attribute2 = p_attribute2)
355 or ((locked_row.attribute2 is null)
356 and (p_attribute2 is null)))
357 and ( (locked_row.attribute3 = p_attribute3)
358 or ((locked_row.attribute3 is null)
359 and (p_attribute3 is null)))
360 and ( (locked_row.attribute4 = p_attribute4)
361 or ((locked_row.attribute4 is null)
362 and (p_attribute4 is null)))
363 and ( (locked_row.attribute5 = p_attribute5)
364 or ((locked_row.attribute5 is null)
365 and (p_attribute5 is null)))
366 and ( (locked_row.attribute6 = p_attribute6)
367 or ((locked_row.attribute6 is null)
368 and (p_attribute6 is null)))
369 and ( (locked_row.attribute7 = p_attribute7)
370 or ((locked_row.attribute7 is null)
371 and (p_attribute7 is null)))
372 and ( (locked_row.attribute8 = p_attribute8)
373 or ((locked_row.attribute8 is null)
374 and (p_attribute8 is null)))
375 and ( (locked_row.attribute9 = p_attribute9)
376 or ((locked_row.attribute9 is null)
377 and (p_attribute9 is null)))
378 and ( (locked_row.attribute10 = p_attribute10)
379 or ((locked_row.attribute10 is null)
380 and (p_attribute10 is null)))
381 and ( (locked_row.attribute11 = p_attribute11)
382 or ((locked_row.attribute11 is null)
383 and (p_attribute11 is null)))
384 and ( (locked_row.attribute12 = p_attribute12)
385 or ((locked_row.attribute12 is null)
386 and (p_attribute12 is null)))
387 and ( (locked_row.attribute13 = p_attribute13)
388 or ((locked_row.attribute13 is null)
389 and (p_attribute13 is null)))
390 and ( (locked_row.attribute14 = p_attribute14)
391 or ((locked_row.attribute14 is null)
392 and (p_attribute14 is null)))
393 and ( (locked_row.attribute15 = p_attribute15)
394 or ((locked_row.attribute15 is null)
395 and (p_attribute15 is null)))
396 and ( (locked_row.attribute16 = p_attribute16)
397 or ((locked_row.attribute16 is null)
398 and (p_attribute16 is null)))
399 and ( (locked_row.attribute17 = p_attribute17)
400 or ((locked_row.attribute17 is null)
401 and (p_attribute17 is null)))
402 and ( (locked_row.attribute18 = p_attribute18)
403 or ((locked_row.attribute18 is null)
404 and (p_attribute18 is null)))
405 and ( (locked_row.attribute19 = p_attribute19)
406 or ((locked_row.attribute19 is null)
407 and (p_attribute19 is null)))
408 and ( (locked_row.attribute20 = p_attribute20)
409 or ((locked_row.attribute20 is null)
410 and (p_attribute20 is null)))
411 ) then
412 null;
413 else
414 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
415 app_exception.raise_exception;
416 end if;
417 --
418 -- Lock the object status.
419 --
420 pay_batch_object_status_pkg.lock_batch_object
421 (p_object_type => 'BEL'
422 ,p_object_id => p_batch_element_link_id
423 ,p_object_status => p_object_status
424 ,p_default_status => 'U'
425 );
426 --
427 hr_utility.set_location('Leaving: '||l_proc, 100);
428 --
429 end lock_row;
430 --
431 --------------------------------------------------------------------------------
432 procedure update_row
433 (p_rowid in varchar2
434 ,p_batch_element_link_id in number
435 ,p_element_link_id in number
436 ,p_effective_date in date
437 ,p_payroll_id in number
438 ,p_job_id in number
439 ,p_position_id in number
443 ,p_element_type_id in number
440 ,p_people_group_id in number
441 ,p_cost_allocation_keyflex_id in number
442 ,p_organization_id in number
444 ,p_location_id in number
445 ,p_grade_id in number
446 ,p_balancing_keyflex_id in number
447 ,p_business_group_id in number
448 ,p_element_set_id in number
449 ,p_pay_basis_id in number
450 ,p_costable_type in varchar2
451 ,p_link_to_all_payrolls_flag in varchar2
452 ,p_multiply_value_flag in varchar2
453 ,p_standard_link_flag in varchar2
454 ,p_transfer_to_gl_flag in varchar2
455 ,p_comment_id in number
456 ,p_employment_category in varchar2
457 ,p_qualifying_age in number
458 ,p_qualifying_length_of_service in number
459 ,p_qualifying_units in varchar2
460 ,p_attribute_category in varchar2
461 ,p_attribute1 in varchar2
462 ,p_attribute2 in varchar2
463 ,p_attribute3 in varchar2
464 ,p_attribute4 in varchar2
465 ,p_attribute5 in varchar2
466 ,p_attribute6 in varchar2
467 ,p_attribute7 in varchar2
468 ,p_attribute8 in varchar2
469 ,p_attribute9 in varchar2
470 ,p_attribute10 in varchar2
471 ,p_attribute11 in varchar2
472 ,p_attribute12 in varchar2
473 ,p_attribute13 in varchar2
474 ,p_attribute14 in varchar2
475 ,p_attribute15 in varchar2
476 ,p_attribute16 in varchar2
477 ,p_attribute17 in varchar2
478 ,p_attribute18 in varchar2
479 ,p_attribute19 in varchar2
480 ,p_attribute20 in varchar2
481 )
482 is
483 --
484 l_proc varchar2(72):= 'pay_batch_element_link_pkg.update_row';
485 begin
486 --
487 hr_utility.set_location('Entering: '||l_proc, 5);
488 --
489 update pay_batch_element_links
490 set
491 batch_element_link_id = p_batch_element_link_id
492 ,element_link_id = p_element_link_id
493 ,effective_date = p_effective_date
494 ,payroll_id = p_payroll_id
495 ,job_id = p_job_id
496 ,position_id = p_position_id
497 ,people_group_id = p_people_group_id
498 ,cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
499 ,organization_id = p_organization_id
500 ,element_type_id = p_element_type_id
501 ,location_id = p_location_id
502 ,grade_id = p_grade_id
503 ,balancing_keyflex_id = p_balancing_keyflex_id
504 ,business_group_id = p_business_group_id
505 ,element_set_id = p_element_set_id
506 ,pay_basis_id = p_pay_basis_id
507 ,costable_type = p_costable_type
508 ,link_to_all_payrolls_flag = p_link_to_all_payrolls_flag
509 ,multiply_value_flag = p_multiply_value_flag
510 ,standard_link_flag = p_standard_link_flag
511 ,transfer_to_gl_flag = p_transfer_to_gl_flag
512 ,comment_id = p_comment_id
513 ,employment_category = p_employment_category
514 ,qualifying_age = p_qualifying_age
515 ,qualifying_length_of_service = p_qualifying_length_of_service
516 ,qualifying_units = p_qualifying_units
517 ,attribute_category = p_attribute_category
518 ,attribute1 = p_attribute1
519 ,attribute2 = p_attribute2
520 ,attribute3 = p_attribute3
521 ,attribute4 = p_attribute4
522 ,attribute5 = p_attribute5
523 ,attribute6 = p_attribute6
524 ,attribute7 = p_attribute7
525 ,attribute8 = p_attribute8
526 ,attribute9 = p_attribute9
527 ,attribute10 = p_attribute10
528 ,attribute11 = p_attribute11
529 ,attribute12 = p_attribute12
530 ,attribute13 = p_attribute13
531 ,attribute14 = p_attribute14
532 ,attribute15 = p_attribute15
533 ,attribute16 = p_attribute16
534 ,attribute17 = p_attribute17
535 ,attribute18 = p_attribute18
536 ,attribute19 = p_attribute19
537 ,attribute20 = p_attribute20
538 where rowid = p_rowid;
539 --
540 if sql%notfound then
541 raise no_data_found;
542 end if;
543 --
544 hr_utility.set_location('Leaving: '||l_proc, 100);
545 --
546 end update_row;
547 --------------------------------------------------------------------------------
548 procedure delete_row
549 (p_rowid in varchar2
550 )
551 is
552 --
553 l_batch_element_link_id number;
554 l_proc varchar2(72):= 'pay_batch_element_link_pkg.delete_row';
555 begin
556 --
557 hr_utility.set_location('Entering: '||l_proc, 5);
558 --
559 select batch_element_link_id into l_batch_element_link_id
560 from pay_batch_element_links
561 where rowid = p_rowid;
562 --
566 ,p_payroll_action_id => null
563 pay_batch_object_status_pkg.delete_object_status
564 (p_object_type => 'BEL'
565 ,p_object_id => l_batch_element_link_id
567 );
568 --
569 delete from pay_batch_element_links
570 where rowid = p_rowid;
571 --
572 if sql%notfound then
573 raise no_data_found;
574 end if;
575 --
576 hr_utility.set_location('Leaving: '||l_proc, 100);
577 --
578 end delete_row;
579 --------------------------------------------------------------------------------
580
581 end pay_batch_element_link_pkg;
[Home] [Help]