[Home] [Help]
PACKAGE BODY: APPS.BEN_ICD_PKG
Source
1 PACKAGE BODY BEN_ICD_PKG AS
2 /* $Header: beicd.pkb 120.0.12010000.2 2009/06/16 13:46:55 vkodedal ship $ */
3 g_package varchar2(30) :='BEN_ICD_PKG';
4
5 procedure CREATE_TRANSACTION
6 (
7 P_ICD_TRANSACTION_ID IN NUMBER
8 ,P_ICD_CHC_RATE_ID IN NUMBER
9 ,P_BUSINESS_GROUP_ID IN NUMBER
10 ,P_PERSON_ID IN NUMBER
11 ,P_ASSIGNMENT_ID IN NUMBER
12 ,P_TRANSACTION_ID IN NUMBER
13 ,P_TRANSACTION_STEP_ID IN NUMBER
14 ,P_EFFECTIVE_START_DATE IN DATE
15 ,P_EFFECTIVE_END_DATE IN DATE
16 ,P_DML_OPERATION IN VARCHAR
17 ,P_DATETRACK_MODE IN VARCHAR
18 ,P_EFFECTIVE_DATE IN DATE
19 ,P_PL_TYP_ID IN NUMBER
20 ,P_PL_ID IN NUMBER
21 ,P_OPT_ID IN NUMBER
22 ,P_OIPL_ID IN NUMBER
23 ,P_ACTY_BASE_RT_ID IN NUMBER
24 ,P_RT_STRT_DT_CD IN VARCHAR
25 ,P_RT_STRT_DT IN DATE
26 ,P_RT_STRT_DT_RL IN NUMBER
27 ,P_RT_END_DT_CD IN VARCHAR
28 ,P_RT_END_DT IN DATE
29 ,P_RT_END_DT_RL IN NUMBER
30 ,P_NNMNTRY_UOM IN VARCHAR
31 ,P_BNF_RQD_FLAG IN VARCHAR
32 ,P_ELEMENT_TYPE_ID IN NUMBER
33 ,P_ELEMENT_LINK_ID IN NUMBER
34 ,P_ELEMENT_ENTRY_ID IN NUMBER
35 ,P_ELEMENT_TYPE_CONTEXT IN VARCHAR
36 ,P_INPUT_VALUE_ID1 IN NUMBER
37 ,P_INPUT_VALUE1 IN VARCHAR
38 ,P_INPUT_VALUE_ID2 IN NUMBER
39 ,P_INPUT_VALUE2 IN VARCHAR
40 ,P_INPUT_VALUE_ID3 IN NUMBER
41 ,P_INPUT_VALUE3 IN VARCHAR
42 ,P_INPUT_VALUE_ID4 IN NUMBER
43 ,P_INPUT_VALUE4 IN VARCHAR
44 ,P_INPUT_VALUE_ID5 IN NUMBER
45 ,P_INPUT_VALUE5 IN VARCHAR
46 ,P_INPUT_VALUE_ID6 IN NUMBER
47 ,P_INPUT_VALUE6 IN VARCHAR
48 ,P_INPUT_VALUE_ID7 IN NUMBER
49 ,P_INPUT_VALUE7 IN VARCHAR
50 ,P_INPUT_VALUE_ID8 IN NUMBER
51 ,P_INPUT_VALUE8 IN VARCHAR
52 ,P_INPUT_VALUE_ID9 IN NUMBER
53 ,P_INPUT_VALUE9 IN VARCHAR
54 ,P_INPUT_VALUE_ID10 IN NUMBER
55 ,P_INPUT_VALUE10 IN VARCHAR
56 ,P_INPUT_VALUE_ID11 IN NUMBER
57 ,P_INPUT_VALUE11 IN VARCHAR
58 ,P_INPUT_VALUE_ID12 IN NUMBER
59 ,P_INPUT_VALUE12 IN VARCHAR
60 ,P_INPUT_VALUE_ID13 IN NUMBER
61 ,P_INPUT_VALUE13 IN VARCHAR
62 ,P_INPUT_VALUE_ID14 IN NUMBER
63 ,P_INPUT_VALUE14 IN VARCHAR
64 ,P_INPUT_VALUE_ID15 IN NUMBER
65 ,P_INPUT_VALUE15 IN VARCHAR
66 ,P_ITEM_TYPE IN VARCHAR
67 ,P_ITEM_KEY IN VARCHAR
68 ,P_STATUS IN VARCHAR
69 ,P_PL_ORDR_NUM IN NUMBER
70 ,P_OIPL_ORDR_NUM IN NUMBER
71 ,P_REASON IN VARCHAR
72 ,P_FUTURE_DATA_EXISTS IN VARCHAR
73 ,P_OBJECT_VERSION_NUMBER IN NUMBER
74 ,P_E_OBJECT_VERSION_NUMBER IN NUMBER
75 ,P_COST_ALLOCATION_KEYFLEX_ID IN NUMBER
76 ,P_ATTRIBUTE_CATEGORY IN VARCHAR
77 ,P_ATTRIBUTE1 IN VARCHAR
78 ,P_ATTRIBUTE2 IN VARCHAR
79 ,P_ATTRIBUTE3 IN VARCHAR
80 ,P_ATTRIBUTE4 IN VARCHAR
81 ,P_ATTRIBUTE5 IN VARCHAR
82 ,P_ATTRIBUTE6 IN VARCHAR
83 ,P_ATTRIBUTE7 IN VARCHAR
84 ,P_ATTRIBUTE8 IN VARCHAR
85 ,P_ATTRIBUTE9 IN VARCHAR
86 ,P_ATTRIBUTE10 IN VARCHAR
87 ,P_ATTRIBUTE11 IN VARCHAR
88 ,P_ATTRIBUTE12 IN VARCHAR
89 ,P_ATTRIBUTE13 IN VARCHAR
90 ,P_ATTRIBUTE14 IN VARCHAR
91 ,P_ATTRIBUTE15 IN VARCHAR
92 ,P_ATTRIBUTE16 IN VARCHAR
93 ,P_ATTRIBUTE17 IN VARCHAR
94 ,P_ATTRIBUTE18 IN VARCHAR
95 ,P_ATTRIBUTE19 IN VARCHAR
96 ,P_ATTRIBUTE20 IN VARCHAR
97 ,P_ENTRY_INFORMATION_CATEGORY IN VARCHAR
98 ,P_ENTRY_INFORMATION1 IN VARCHAR
99 ,P_ENTRY_INFORMATION2 IN VARCHAR
100 ,P_ENTRY_INFORMATION3 IN VARCHAR
101 ,P_ENTRY_INFORMATION4 IN VARCHAR
102 ,P_ENTRY_INFORMATION5 IN VARCHAR
103 ,P_ENTRY_INFORMATION6 IN VARCHAR
104 ,P_ENTRY_INFORMATION7 IN VARCHAR
105 ,P_ENTRY_INFORMATION8 IN VARCHAR
106 ,P_ENTRY_INFORMATION9 IN VARCHAR
107 ,P_ENTRY_INFORMATION10 IN VARCHAR
108 ,P_ENTRY_INFORMATION11 IN VARCHAR
109 ,P_ENTRY_INFORMATION12 IN VARCHAR
110 ,P_ENTRY_INFORMATION13 IN VARCHAR
111 ,P_ENTRY_INFORMATION14 IN VARCHAR
112 ,P_ENTRY_INFORMATION15 IN VARCHAR
113 ,P_ENTRY_INFORMATION16 IN VARCHAR
114 ,P_ENTRY_INFORMATION17 IN VARCHAR
115 ,P_ENTRY_INFORMATION18 IN VARCHAR
116 ,P_ENTRY_INFORMATION19 IN VARCHAR
117 ,P_ENTRY_INFORMATION20 IN VARCHAR
118 ,P_ENTRY_INFORMATION21 IN VARCHAR
119 ,P_ENTRY_INFORMATION22 IN VARCHAR
120 ,P_ENTRY_INFORMATION23 IN VARCHAR
121 ,P_ENTRY_INFORMATION24 IN VARCHAR
122 ,P_ENTRY_INFORMATION25 IN VARCHAR
123 ,P_ENTRY_INFORMATION26 IN VARCHAR
124 ,P_ENTRY_INFORMATION27 IN VARCHAR
125 ,P_ENTRY_INFORMATION28 IN VARCHAR
126 ,P_ENTRY_INFORMATION29 IN VARCHAR
127 ,P_ENTRY_INFORMATION30 IN VARCHAR
128 ) is
129 ---vkodedal 16-Jun-2009 8248384
130 l_null varchar2(1):=null;
131
132 CURSOR c_entry_exists is
133 SELECT NULL
134 FROM ben_icd_transaction icd, pay_element_types_f pet
135 WHERE icd.assignment_id = P_ASSIGNMENT_ID
136 AND NVL(icd.effective_date,icd.effective_start_date) = nvl(P_EFFECTIVE_DATE,P_EFFECTIVE_START_DATE)
137 AND icd.element_type_id = P_ELEMENT_TYPE_ID
138 AND icd.transaction_id = P_TRANSACTION_ID
139 AND pet.element_type_id = icd.element_type_id
140 AND pet.multiple_entries_allowed_flag = 'N'
141 AND NVL(icd.effective_date,icd.effective_start_date) BETWEEN pet.effective_start_date
142 AND pet.effective_end_date;
143
144
145 begin
146 fnd_msg_pub.initialize;
147 open c_entry_exists;
148 fetch c_entry_exists into l_null;
149 if c_entry_exists%found then
150 hr_utility.set_message(801,'HR_7455_PLK_ELE_ENTRY_EXISTS');
151 hr_utility.raise_error;
152 end if;
153 close c_entry_exists;
154
155 INSERT INTO BEN_ICD_TRANSACTION
156 (
157 ICD_TRANSACTION_ID
158 ,ICD_CHC_RATE_ID
159 ,BUSINESS_GROUP_ID
160 ,PERSON_ID
161 ,ASSIGNMENT_ID
162 ,TRANSACTION_ID
163 ,TRANSACTION_STEP_ID
164 ,EFFECTIVE_START_DATE
165 ,EFFECTIVE_END_DATE
166 ,DML_OPERATION
167 ,DATETRACK_MODE
168 ,EFFECTIVE_DATE
169 ,PL_TYP_ID
170 ,PL_ID
171 ,OPT_ID
172 ,OIPL_ID
173 ,ACTY_BASE_RT_ID
174 ,RT_STRT_DT_CD
175 ,RT_STRT_DT
176 ,RT_STRT_DT_RL
177 ,RT_END_DT_CD
178 ,RT_END_DT
179 ,RT_END_DT_RL
180 ,NNMNTRY_UOM
181 ,BNF_RQD_FLAG
182 ,ELEMENT_TYPE_ID
183 ,ELEMENT_LINK_ID
184 ,ELEMENT_ENTRY_ID
185 ,ELEMENT_TYPE_CONTEXT
186 ,INPUT_VALUE_ID1
187 ,INPUT_VALUE1
188 ,INPUT_VALUE_ID2
189 ,INPUT_VALUE2
190 ,INPUT_VALUE_ID3
191 ,INPUT_VALUE3
192 ,INPUT_VALUE_ID4
193 ,INPUT_VALUE4
194 ,INPUT_VALUE_ID5
195 ,INPUT_VALUE5
196 ,INPUT_VALUE_ID6
197 ,INPUT_VALUE6
198 ,INPUT_VALUE_ID7
199 ,INPUT_VALUE7
200 ,INPUT_VALUE_ID8
201 ,INPUT_VALUE8
202 ,INPUT_VALUE_ID9
203 ,INPUT_VALUE9
204 ,INPUT_VALUE_ID10
205 ,INPUT_VALUE10
206 ,INPUT_VALUE_ID11
207 ,INPUT_VALUE11
208 ,INPUT_VALUE_ID12
209 ,INPUT_VALUE12
210 ,INPUT_VALUE_ID13
211 ,INPUT_VALUE13
212 ,INPUT_VALUE_ID14
213 ,INPUT_VALUE14
214 ,INPUT_VALUE_ID15
215 ,INPUT_VALUE15
216 ,ITEM_TYPE
217 ,ITEM_KEY
218 ,STATUS
219 ,PL_ORDR_NUM
220 ,OIPL_ORDR_NUM
221 ,REASON
222 ,FUTURE_DATA_EXISTS
223 ,OBJECT_VERSION_NUMBER
224 ,E_OBJECT_VERSION_NUMBER
225 ,COST_ALLOCATION_KEYFLEX_ID
226 ,ATTRIBUTE_CATEGORY
227 ,ATTRIBUTE1
228 ,ATTRIBUTE2
229 ,ATTRIBUTE3
230 ,ATTRIBUTE4
231 ,ATTRIBUTE5
232 ,ATTRIBUTE6
233 ,ATTRIBUTE7
234 ,ATTRIBUTE8
235 ,ATTRIBUTE9
236 ,ATTRIBUTE10
237 ,ATTRIBUTE11
238 ,ATTRIBUTE12
239 ,ATTRIBUTE13
240 ,ATTRIBUTE14
241 ,ATTRIBUTE15
242 ,ATTRIBUTE16
243 ,ATTRIBUTE17
244 ,ATTRIBUTE18
245 ,ATTRIBUTE19
246 ,ATTRIBUTE20
247 ,ENTRY_INFORMATION_CATEGORY
248 ,ENTRY_INFORMATION1
249 ,ENTRY_INFORMATION2
250 ,ENTRY_INFORMATION3
251 ,ENTRY_INFORMATION4
252 ,ENTRY_INFORMATION5
253 ,ENTRY_INFORMATION6
254 ,ENTRY_INFORMATION7
255 ,ENTRY_INFORMATION8
256 ,ENTRY_INFORMATION9
257 ,ENTRY_INFORMATION10
258 ,ENTRY_INFORMATION11
259 ,ENTRY_INFORMATION12
260 ,ENTRY_INFORMATION13
261 ,ENTRY_INFORMATION14
262 ,ENTRY_INFORMATION15
263 ,ENTRY_INFORMATION16
264 ,ENTRY_INFORMATION17
265 ,ENTRY_INFORMATION18
266 ,ENTRY_INFORMATION19
267 ,ENTRY_INFORMATION20
268 ,ENTRY_INFORMATION21
269 ,ENTRY_INFORMATION22
270 ,ENTRY_INFORMATION23
271 ,ENTRY_INFORMATION24
272 ,ENTRY_INFORMATION25
273 ,ENTRY_INFORMATION26
274 ,ENTRY_INFORMATION27
275 ,ENTRY_INFORMATION28
276 ,ENTRY_INFORMATION29
277 ,ENTRY_INFORMATION30
278 )
279
280 VALUES
281 (
282 P_ICD_TRANSACTION_ID
283 ,P_ICD_CHC_RATE_ID
284 ,P_BUSINESS_GROUP_ID
285 ,P_PERSON_ID
286 ,P_ASSIGNMENT_ID
287 ,P_TRANSACTION_ID
288 ,P_TRANSACTION_STEP_ID
289 ,P_EFFECTIVE_START_DATE
290 ,P_EFFECTIVE_END_DATE
291 ,P_DML_OPERATION
292 ,P_DATETRACK_MODE
293 ,P_EFFECTIVE_DATE
294 ,P_PL_TYP_ID
295 ,P_PL_ID
296 ,P_OPT_ID
297 ,P_OIPL_ID
298 ,P_ACTY_BASE_RT_ID
299 ,P_RT_STRT_DT_CD
300 ,P_RT_STRT_DT
301 ,P_RT_STRT_DT_RL
302 ,P_RT_END_DT_CD
303 ,P_RT_END_DT
304 ,P_RT_END_DT_RL
305 ,P_NNMNTRY_UOM
306 ,P_BNF_RQD_FLAG
307 ,P_ELEMENT_TYPE_ID
308 ,P_ELEMENT_LINK_ID
309 ,P_ELEMENT_ENTRY_ID
310 ,P_ELEMENT_TYPE_CONTEXT
311 ,P_INPUT_VALUE_ID1
312 ,P_INPUT_VALUE1
313 ,P_INPUT_VALUE_ID2
314 ,P_INPUT_VALUE2
315 ,P_INPUT_VALUE_ID3
316 ,P_INPUT_VALUE3
317 ,P_INPUT_VALUE_ID4
318 ,P_INPUT_VALUE4
319 ,P_INPUT_VALUE_ID5
320 ,P_INPUT_VALUE5
321 ,P_INPUT_VALUE_ID6
322 ,P_INPUT_VALUE6
323 ,P_INPUT_VALUE_ID7
324 ,P_INPUT_VALUE7
325 ,P_INPUT_VALUE_ID8
326 ,P_INPUT_VALUE8
327 ,P_INPUT_VALUE_ID9
328 ,P_INPUT_VALUE9
329 ,P_INPUT_VALUE_ID10
330 ,P_INPUT_VALUE10
331 ,P_INPUT_VALUE_ID11
332 ,P_INPUT_VALUE11
333 ,P_INPUT_VALUE_ID12
334 ,P_INPUT_VALUE12
335 ,P_INPUT_VALUE_ID13
336 ,P_INPUT_VALUE13
337 ,P_INPUT_VALUE_ID14
338 ,P_INPUT_VALUE14
339 ,P_INPUT_VALUE_ID15
340 ,P_INPUT_VALUE15
341 ,P_ITEM_TYPE
342 ,P_ITEM_KEY
343 ,P_STATUS
344 ,P_PL_ORDR_NUM
345 ,P_OIPL_ORDR_NUM
346 ,P_REASON
347 ,P_FUTURE_DATA_EXISTS
348 ,P_OBJECT_VERSION_NUMBER
349 ,P_E_OBJECT_VERSION_NUMBER
350 ,P_COST_ALLOCATION_KEYFLEX_ID
351 ,P_ATTRIBUTE_CATEGORY
352 ,P_ATTRIBUTE1
353 ,P_ATTRIBUTE2
354 ,P_ATTRIBUTE3
355 ,P_ATTRIBUTE4
356 ,P_ATTRIBUTE5
357 ,P_ATTRIBUTE6
358 ,P_ATTRIBUTE7
359 ,P_ATTRIBUTE8
360 ,P_ATTRIBUTE9
361 ,P_ATTRIBUTE10
362 ,P_ATTRIBUTE11
363 ,P_ATTRIBUTE12
364 ,P_ATTRIBUTE13
365 ,P_ATTRIBUTE14
366 ,P_ATTRIBUTE15
367 ,P_ATTRIBUTE16
368 ,P_ATTRIBUTE17
369 ,P_ATTRIBUTE18
370 ,P_ATTRIBUTE19
371 ,P_ATTRIBUTE20
372 ,P_ENTRY_INFORMATION_CATEGORY
373 ,P_ENTRY_INFORMATION1
374 ,P_ENTRY_INFORMATION2
375 ,P_ENTRY_INFORMATION3
376 ,P_ENTRY_INFORMATION4
377 ,P_ENTRY_INFORMATION5
378 ,P_ENTRY_INFORMATION6
379 ,P_ENTRY_INFORMATION7
380 ,P_ENTRY_INFORMATION8
381 ,P_ENTRY_INFORMATION9
382 ,P_ENTRY_INFORMATION10
383 ,P_ENTRY_INFORMATION11
384 ,P_ENTRY_INFORMATION12
385 ,P_ENTRY_INFORMATION13
386 ,P_ENTRY_INFORMATION14
387 ,P_ENTRY_INFORMATION15
388 ,P_ENTRY_INFORMATION16
389 ,P_ENTRY_INFORMATION17
390 ,P_ENTRY_INFORMATION18
391 ,P_ENTRY_INFORMATION19
392 ,P_ENTRY_INFORMATION20
393 ,P_ENTRY_INFORMATION21
394 ,P_ENTRY_INFORMATION22
395 ,P_ENTRY_INFORMATION23
396 ,P_ENTRY_INFORMATION24
397 ,P_ENTRY_INFORMATION25
398 ,P_ENTRY_INFORMATION26
399 ,P_ENTRY_INFORMATION27
400 ,P_ENTRY_INFORMATION28
401 ,P_ENTRY_INFORMATION29
402 ,P_ENTRY_INFORMATION30
403 );
404
405 EXCEPTION
406 when others then
407 fnd_msg_pub.add;
408
409 end CREATE_TRANSACTION;
410
411 procedure UPDATE_TRANSACTION
412 (
413 P_ICD_TRANSACTION_ID IN NUMBER
414 ,P_ICD_CHC_RATE_ID IN NUMBER
415 ,P_BUSINESS_GROUP_ID IN NUMBER
416 ,P_PERSON_ID IN NUMBER
417 ,P_ASSIGNMENT_ID IN NUMBER
418 ,P_TRANSACTION_ID IN NUMBER
419 ,P_TRANSACTION_STEP_ID IN NUMBER
420 ,P_EFFECTIVE_START_DATE IN DATE
421 ,P_EFFECTIVE_END_DATE IN DATE
422 ,P_DML_OPERATION IN VARCHAR
423 ,P_DATETRACK_MODE IN VARCHAR
424 ,P_EFFECTIVE_DATE IN DATE
425 ,P_PL_TYP_ID IN NUMBER
426 ,P_PL_ID IN NUMBER
427 ,P_OPT_ID IN NUMBER
428 ,P_OIPL_ID IN NUMBER
429 ,P_ACTY_BASE_RT_ID IN NUMBER
430 ,P_RT_STRT_DT_CD IN VARCHAR
431 ,P_RT_STRT_DT IN DATE
432 ,P_RT_STRT_DT_RL IN NUMBER
433 ,P_RT_END_DT_CD IN VARCHAR
434 ,P_RT_END_DT IN DATE
435 ,P_RT_END_DT_RL IN NUMBER
436 ,P_NNMNTRY_UOM IN VARCHAR
437 ,P_BNF_RQD_FLAG IN VARCHAR
438 ,P_ELEMENT_TYPE_ID IN NUMBER
439 ,P_ELEMENT_LINK_ID IN NUMBER
440 ,P_ELEMENT_ENTRY_ID IN NUMBER
441 ,P_ELEMENT_TYPE_CONTEXT IN VARCHAR
442 ,P_INPUT_VALUE_ID1 IN NUMBER
443 ,P_INPUT_VALUE1 IN VARCHAR
444 ,P_INPUT_VALUE_ID2 IN NUMBER
445 ,P_INPUT_VALUE2 IN VARCHAR
446 ,P_INPUT_VALUE_ID3 IN NUMBER
447 ,P_INPUT_VALUE3 IN VARCHAR
448 ,P_INPUT_VALUE_ID4 IN NUMBER
449 ,P_INPUT_VALUE4 IN VARCHAR
450 ,P_INPUT_VALUE_ID5 IN NUMBER
451 ,P_INPUT_VALUE5 IN VARCHAR
452 ,P_INPUT_VALUE_ID6 IN NUMBER
453 ,P_INPUT_VALUE6 IN VARCHAR
454 ,P_INPUT_VALUE_ID7 IN NUMBER
455 ,P_INPUT_VALUE7 IN VARCHAR
456 ,P_INPUT_VALUE_ID8 IN NUMBER
457 ,P_INPUT_VALUE8 IN VARCHAR
458 ,P_INPUT_VALUE_ID9 IN NUMBER
462 ,P_INPUT_VALUE_ID11 IN NUMBER
459 ,P_INPUT_VALUE9 IN VARCHAR
460 ,P_INPUT_VALUE_ID10 IN NUMBER
461 ,P_INPUT_VALUE10 IN VARCHAR
463 ,P_INPUT_VALUE11 IN VARCHAR
464 ,P_INPUT_VALUE_ID12 IN NUMBER
465 ,P_INPUT_VALUE12 IN VARCHAR
466 ,P_INPUT_VALUE_ID13 IN NUMBER
467 ,P_INPUT_VALUE13 IN VARCHAR
468 ,P_INPUT_VALUE_ID14 IN NUMBER
469 ,P_INPUT_VALUE14 IN VARCHAR
470 ,P_INPUT_VALUE_ID15 IN NUMBER
471 ,P_INPUT_VALUE15 IN VARCHAR
472 ,P_ITEM_TYPE IN VARCHAR
473 ,P_ITEM_KEY IN VARCHAR
474 ,P_STATUS IN VARCHAR
475 ,P_PL_ORDR_NUM IN NUMBER
476 ,P_OIPL_ORDR_NUM IN NUMBER
477 ,P_REASON IN VARCHAR
478 ,P_FUTURE_DATA_EXISTS IN VARCHAR
479 ,P_OBJECT_VERSION_NUMBER IN NUMBER
480 ,P_E_OBJECT_VERSION_NUMBER IN NUMBER
481 ,P_COST_ALLOCATION_KEYFLEX_ID IN NUMBER
482 ,P_ATTRIBUTE_CATEGORY IN VARCHAR
483 ,P_ATTRIBUTE1 IN VARCHAR
484 ,P_ATTRIBUTE2 IN VARCHAR
485 ,P_ATTRIBUTE3 IN VARCHAR
486 ,P_ATTRIBUTE4 IN VARCHAR
487 ,P_ATTRIBUTE5 IN VARCHAR
488 ,P_ATTRIBUTE6 IN VARCHAR
489 ,P_ATTRIBUTE7 IN VARCHAR
490 ,P_ATTRIBUTE8 IN VARCHAR
491 ,P_ATTRIBUTE9 IN VARCHAR
492 ,P_ATTRIBUTE10 IN VARCHAR
493 ,P_ATTRIBUTE11 IN VARCHAR
494 ,P_ATTRIBUTE12 IN VARCHAR
495 ,P_ATTRIBUTE13 IN VARCHAR
496 ,P_ATTRIBUTE14 IN VARCHAR
497 ,P_ATTRIBUTE15 IN VARCHAR
498 ,P_ATTRIBUTE16 IN VARCHAR
499 ,P_ATTRIBUTE17 IN VARCHAR
500 ,P_ATTRIBUTE18 IN VARCHAR
501 ,P_ATTRIBUTE19 IN VARCHAR
502 ,P_ATTRIBUTE20 IN VARCHAR
503 ,P_ENTRY_INFORMATION_CATEGORY IN VARCHAR
504 ,P_ENTRY_INFORMATION1 IN VARCHAR
505 ,P_ENTRY_INFORMATION2 IN VARCHAR
506 ,P_ENTRY_INFORMATION3 IN VARCHAR
507 ,P_ENTRY_INFORMATION4 IN VARCHAR
508 ,P_ENTRY_INFORMATION5 IN VARCHAR
509 ,P_ENTRY_INFORMATION6 IN VARCHAR
510 ,P_ENTRY_INFORMATION7 IN VARCHAR
511 ,P_ENTRY_INFORMATION8 IN VARCHAR
512 ,P_ENTRY_INFORMATION9 IN VARCHAR
513 ,P_ENTRY_INFORMATION10 IN VARCHAR
514 ,P_ENTRY_INFORMATION11 IN VARCHAR
515 ,P_ENTRY_INFORMATION12 IN VARCHAR
516 ,P_ENTRY_INFORMATION13 IN VARCHAR
517 ,P_ENTRY_INFORMATION14 IN VARCHAR
518 ,P_ENTRY_INFORMATION15 IN VARCHAR
519 ,P_ENTRY_INFORMATION16 IN VARCHAR
520 ,P_ENTRY_INFORMATION17 IN VARCHAR
521 ,P_ENTRY_INFORMATION18 IN VARCHAR
522 ,P_ENTRY_INFORMATION19 IN VARCHAR
523 ,P_ENTRY_INFORMATION20 IN VARCHAR
524 ,P_ENTRY_INFORMATION21 IN VARCHAR
525 ,P_ENTRY_INFORMATION22 IN VARCHAR
526 ,P_ENTRY_INFORMATION23 IN VARCHAR
527 ,P_ENTRY_INFORMATION24 IN VARCHAR
528 ,P_ENTRY_INFORMATION25 IN VARCHAR
529 ,P_ENTRY_INFORMATION26 IN VARCHAR
530 ,P_ENTRY_INFORMATION27 IN VARCHAR
531 ,P_ENTRY_INFORMATION28 IN VARCHAR
532 ,P_ENTRY_INFORMATION29 IN VARCHAR
533 ,P_ENTRY_INFORMATION30 IN VARCHAR
534 ) is
535 ---vkodedal 16-Jun-2009 8248384
536 l_null varchar2(1):=null;
537
538 CURSOR c_entry_exists is
539 SELECT NULL
540 FROM ben_icd_transaction icd, pay_element_types_f pet
541 WHERE icd.assignment_id = P_ASSIGNMENT_ID
542 AND NVL(icd.effective_date,icd.effective_start_date) = nvl(P_EFFECTIVE_DATE,P_EFFECTIVE_START_DATE)
543 AND icd.element_type_id = P_ELEMENT_TYPE_ID
544 AND icd.transaction_id = P_TRANSACTION_ID
545 AND pet.element_type_id = icd.element_type_id
546 AND pet.multiple_entries_allowed_flag = 'N'
547 AND NVL(icd.effective_date,icd.effective_start_date) BETWEEN pet.effective_start_date
548 AND pet.effective_end_date
549 AND icd.ICD_TRANSACTION_ID<>P_ICD_TRANSACTION_ID;
550
551 begin
552
553 fnd_msg_pub.initialize;
554 open c_entry_exists;
555 fetch c_entry_exists into l_null;
556 if c_entry_exists%found then
557 hr_utility.set_message(801,'HR_7455_PLK_ELE_ENTRY_EXISTS');
558 hr_utility.raise_error;
559 end if;
560 close c_entry_exists;
561
562 update
563 ben_icd_transaction
564 set
565 ICD_CHC_RATE_ID = P_ICD_CHC_RATE_ID
566 ,BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
567 ,PERSON_ID = P_PERSON_ID
568 ,ASSIGNMENT_ID = P_ASSIGNMENT_ID
569 ,TRANSACTION_ID = P_TRANSACTION_ID
570 ,TRANSACTION_STEP_ID = P_TRANSACTION_STEP_ID
571 ,EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE
572 ,EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE
573 ,DML_OPERATION = P_DML_OPERATION
574 ,DATETRACK_MODE = P_DATETRACK_MODE
575 ,EFFECTIVE_DATE = P_EFFECTIVE_DATE
579 ,OIPL_ID = P_OIPL_ID
576 ,PL_TYP_ID = P_PL_TYP_ID
577 ,PL_ID = P_PL_ID
578 ,OPT_ID = P_OPT_ID
580 ,ACTY_BASE_RT_ID = P_ACTY_BASE_RT_ID
581 ,RT_STRT_DT_CD = P_RT_STRT_DT_CD
582 ,RT_STRT_DT = P_RT_STRT_DT
583 ,RT_STRT_DT_RL = P_RT_STRT_DT_RL
584 ,RT_END_DT_CD = P_RT_END_DT_CD
585 ,RT_END_DT = P_RT_END_DT
586 ,RT_END_DT_RL = P_RT_END_DT_RL
587 ,NNMNTRY_UOM = P_NNMNTRY_UOM
588 ,BNF_RQD_FLAG = P_BNF_RQD_FLAG
589 ,FUTURE_DATA_EXISTS = P_FUTURE_DATA_EXISTS
590 ,OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER
591 ,E_OBJECT_VERSION_NUMBER = P_E_OBJECT_VERSION_NUMBER
592 ,ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
593 ,ELEMENT_LINK_ID = P_ELEMENT_LINK_ID
594 ,ELEMENT_ENTRY_ID = P_ELEMENT_ENTRY_ID
595 ,ELEMENT_TYPE_CONTEXT = P_ELEMENT_TYPE_CONTEXT
596 ,INPUT_VALUE_ID1 = P_INPUT_VALUE_ID1
597 ,INPUT_VALUE1 = P_INPUT_VALUE1
598 ,INPUT_VALUE_ID2 = P_INPUT_VALUE_ID2
599 ,INPUT_VALUE2 = P_INPUT_VALUE2
600 ,INPUT_VALUE_ID3 = P_INPUT_VALUE_ID3
601 ,INPUT_VALUE3 = P_INPUT_VALUE3
602 ,INPUT_VALUE_ID4 = P_INPUT_VALUE_ID4
603 ,INPUT_VALUE4 = P_INPUT_VALUE4
604 ,INPUT_VALUE_ID5 = P_INPUT_VALUE_ID5
605 ,INPUT_VALUE5 = P_INPUT_VALUE5
606 ,INPUT_VALUE_ID6 = P_INPUT_VALUE_ID6
607 ,INPUT_VALUE6 = P_INPUT_VALUE6
608 ,INPUT_VALUE_ID7 = P_INPUT_VALUE_ID7
609 ,INPUT_VALUE7 = P_INPUT_VALUE7
610 ,INPUT_VALUE_ID8 = P_INPUT_VALUE_ID8
611 ,INPUT_VALUE8 = P_INPUT_VALUE8
612 ,INPUT_VALUE_ID9 = P_INPUT_VALUE_ID9
613 ,INPUT_VALUE9 = P_INPUT_VALUE9
614 ,INPUT_VALUE_ID10 = P_INPUT_VALUE_ID10
615 ,INPUT_VALUE10 = P_INPUT_VALUE10
616 ,INPUT_VALUE_ID11 = P_INPUT_VALUE_ID11
617 ,INPUT_VALUE11 = P_INPUT_VALUE11
618 ,INPUT_VALUE_ID12 = P_INPUT_VALUE_ID12
619 ,INPUT_VALUE12 = P_INPUT_VALUE12
620 ,INPUT_VALUE_ID13 = P_INPUT_VALUE_ID13
621 ,INPUT_VALUE13 = P_INPUT_VALUE13
622 ,INPUT_VALUE_ID14 = P_INPUT_VALUE_ID14
623 ,INPUT_VALUE14 = P_INPUT_VALUE14
624 ,INPUT_VALUE_ID15 = P_INPUT_VALUE_ID15
625 ,INPUT_VALUE15 = P_INPUT_VALUE15
626 ,ITEM_TYPE = P_ITEM_TYPE
627 ,ITEM_KEY = P_ITEM_KEY
628 ,STATUS = P_STATUS
629 ,PL_ORDR_NUM = P_PL_ORDR_NUM
630 ,OIPL_ORDR_NUM = P_OIPL_ORDR_NUM
631 ,REASON = P_REASON
632 ,COST_ALLOCATION_KEYFLEX_ID = P_COST_ALLOCATION_KEYFLEX_ID
633 ,ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY
634 ,ATTRIBUTE1 = P_ATTRIBUTE1
635 ,ATTRIBUTE2 = P_ATTRIBUTE2
639 ,ATTRIBUTE6 = P_ATTRIBUTE6
636 ,ATTRIBUTE3 = P_ATTRIBUTE3
637 ,ATTRIBUTE4 = P_ATTRIBUTE4
638 ,ATTRIBUTE5 = P_ATTRIBUTE5
640 ,ATTRIBUTE7 = P_ATTRIBUTE7
641 ,ATTRIBUTE8 = P_ATTRIBUTE8
642 ,ATTRIBUTE9 = P_ATTRIBUTE9
643 ,ATTRIBUTE10 = P_ATTRIBUTE10
644 ,ATTRIBUTE11 = P_ATTRIBUTE11
645 ,ATTRIBUTE12 = P_ATTRIBUTE12
646 ,ATTRIBUTE13 = P_ATTRIBUTE13
647 ,ATTRIBUTE14 = P_ATTRIBUTE14
648 ,ATTRIBUTE15 = P_ATTRIBUTE15
649 ,ATTRIBUTE16 = P_ATTRIBUTE16
650 ,ATTRIBUTE17 = P_ATTRIBUTE17
651 ,ATTRIBUTE18 = P_ATTRIBUTE18
652 ,ATTRIBUTE19 = P_ATTRIBUTE19
653 ,ATTRIBUTE20 = P_ATTRIBUTE20
654 ,ENTRY_INFORMATION_CATEGORY = P_ENTRY_INFORMATION_CATEGORY
655 ,ENTRY_INFORMATION1 = P_ENTRY_INFORMATION1
656 ,ENTRY_INFORMATION2 = P_ENTRY_INFORMATION2
657 ,ENTRY_INFORMATION3 = P_ENTRY_INFORMATION3
658 ,ENTRY_INFORMATION4 = P_ENTRY_INFORMATION4
659 ,ENTRY_INFORMATION5 = P_ENTRY_INFORMATION5
660 ,ENTRY_INFORMATION6 = P_ENTRY_INFORMATION6
661 ,ENTRY_INFORMATION7 = P_ENTRY_INFORMATION7
662 ,ENTRY_INFORMATION8 = P_ENTRY_INFORMATION8
663 ,ENTRY_INFORMATION9 = P_ENTRY_INFORMATION9
664 ,ENTRY_INFORMATION10 = P_ENTRY_INFORMATION10
665 ,ENTRY_INFORMATION11 = P_ENTRY_INFORMATION11
666 ,ENTRY_INFORMATION12 = P_ENTRY_INFORMATION12
667 ,ENTRY_INFORMATION13 = P_ENTRY_INFORMATION13
668 ,ENTRY_INFORMATION14 = P_ENTRY_INFORMATION14
669 ,ENTRY_INFORMATION15 = P_ENTRY_INFORMATION15
670 ,ENTRY_INFORMATION16 = P_ENTRY_INFORMATION16
671 ,ENTRY_INFORMATION17 = P_ENTRY_INFORMATION17
672 ,ENTRY_INFORMATION18 = P_ENTRY_INFORMATION18
673 ,ENTRY_INFORMATION19 = P_ENTRY_INFORMATION19
674 ,ENTRY_INFORMATION20 = P_ENTRY_INFORMATION20
675 ,ENTRY_INFORMATION21 = P_ENTRY_INFORMATION21
676 ,ENTRY_INFORMATION22 = P_ENTRY_INFORMATION22
677 ,ENTRY_INFORMATION23 = P_ENTRY_INFORMATION23
678 ,ENTRY_INFORMATION24 = P_ENTRY_INFORMATION24
679 ,ENTRY_INFORMATION25 = P_ENTRY_INFORMATION25
680 ,ENTRY_INFORMATION26 = P_ENTRY_INFORMATION26
681 ,ENTRY_INFORMATION27 = P_ENTRY_INFORMATION27
682 ,ENTRY_INFORMATION28 = P_ENTRY_INFORMATION28
683 ,ENTRY_INFORMATION29 = P_ENTRY_INFORMATION29
684 ,ENTRY_INFORMATION30 = P_ENTRY_INFORMATION30
685 where
686 icd_transaction_id = P_ICD_TRANSACTION_ID;
687
688
689 EXCEPTION
690 when others then
691 RAISE;
692
693 end UPDATE_TRANSACTION;
694
695 PROCEDURE VALIDATE_TRANSACTION (
696 P_ICD_TRANSACTION_ID IN NUMBER
697 ,P_ASSIGNMENT_ID IN NUMBER
698 ,P_TRANSACTION_ID IN NUMBER
699 ,P_EFFECTIVE_START_DATE IN DATE
700 ,P_EFFECTIVE_END_DATE IN DATE
701 ,P_DML_OPERATION IN VARCHAR
702 ,P_EFFECTIVE_DATE IN DATE
703 ,P_ELEMENT_TYPE_ID IN NUMBER
704 ) IS
705 l_null varchar2(1):=null;
706
707 CURSOR c_entry_exists is
708 SELECT NULL
709 FROM ben_icd_transaction icd, pay_element_types_f pet
710 WHERE icd.assignment_id = P_ASSIGNMENT_ID
711 AND NVL(icd.effective_date,icd.effective_start_date) = nvl(P_EFFECTIVE_DATE,P_EFFECTIVE_START_DATE)
712 AND icd.element_type_id = P_ELEMENT_TYPE_ID
713 AND icd.transaction_id = P_TRANSACTION_ID
714 AND pet.element_type_id = icd.element_type_id
715 AND pet.multiple_entries_allowed_flag = 'N'
716 AND NVL(icd.effective_date,icd.effective_start_date) BETWEEN pet.effective_start_date
717 AND pet.effective_end_date
718 AND icd.ICD_TRANSACTION_ID<>P_ICD_TRANSACTION_ID;
719
720 begin
721
722 fnd_msg_pub.initialize;
723 open c_entry_exists;
724 fetch c_entry_exists into l_null;
725 if c_entry_exists%found then
726 hr_utility.set_message(801,'HR_7455_PLK_ELE_ENTRY_EXISTS');
727 FND_MSG_PUB.ADD;
728 end if;
729 close c_entry_exists;
730
731 END VALIDATE_TRANSACTION;
732
733
734 END BEN_ICD_PKG;
735