[Home] [Help]
PACKAGE BODY: APPS.PV_GE_BENEFITS_PKG
Source
1 package body PV_GE_BENEFITS_PKG as
2 /* $Header: pvxtpgbb.pls 120.1 2005/06/30 14:54:27 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_BENEFIT_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_BENEFIT_TYPE_CODE in VARCHAR2,
8 X_BENEFIT_STATUS_CODE in VARCHAR2,
9 X_BENEFIT_CODE in VARCHAR2,
10 X_DELETE_FLAG in VARCHAR2,
11 X_ADDITIONAL_INFO_1 in NUMBER,
12 X_ADDITIONAL_INFO_2 in VARCHAR2,
13 X_ATTRIBUTE_CATEGORY in VARCHAR2,
14 X_ATTRIBUTE1 in VARCHAR2,
15 X_ATTRIBUTE2 in VARCHAR2,
16 X_ATTRIBUTE3 in VARCHAR2,
17 X_ATTRIBUTE4 in VARCHAR2,
18 X_ATTRIBUTE5 in VARCHAR2,
19 X_ATTRIBUTE6 in VARCHAR2,
20 X_ATTRIBUTE7 in VARCHAR2,
21 X_ATTRIBUTE8 in VARCHAR2,
22 X_ATTRIBUTE9 in VARCHAR2,
23 X_ATTRIBUTE10 in VARCHAR2,
24 X_ATTRIBUTE11 in VARCHAR2,
25 X_ATTRIBUTE12 in VARCHAR2,
26 X_ATTRIBUTE13 in VARCHAR2,
27 X_ATTRIBUTE14 in VARCHAR2,
28 X_ATTRIBUTE15 in VARCHAR2,
29 X_BENEFIT_NAME in VARCHAR2,
30 X_DESCRIPTION in VARCHAR2,
31 X_CREATION_DATE in DATE,
32 X_CREATED_BY in NUMBER,
33 X_LAST_UPDATE_DATE in DATE,
34 X_LAST_UPDATED_BY in NUMBER,
35 X_LAST_UPDATE_LOGIN in NUMBER
36 ) is
37 cursor C is select ROWID from PV_GE_BENEFITS_B
38 where BENEFIT_ID = X_BENEFIT_ID
39 ;
40 begin
41 insert into PV_GE_BENEFITS_B (
42 BENEFIT_ID,
43 OBJECT_VERSION_NUMBER,
44 BENEFIT_TYPE_CODE,
45 BENEFIT_STATUS_CODE,
46 BENEFIT_CODE,
47 DELETE_FLAG,
48 ADDITIONAL_INFO_1,
49 ADDITIONAL_INFO_2,
50 ATTRIBUTE_CATEGORY,
51 ATTRIBUTE1,
52 ATTRIBUTE2,
53 ATTRIBUTE3,
54 ATTRIBUTE4,
55 ATTRIBUTE5,
56 ATTRIBUTE6,
57 ATTRIBUTE7,
58 ATTRIBUTE8,
59 ATTRIBUTE9,
60 ATTRIBUTE10,
61 ATTRIBUTE11,
62 ATTRIBUTE12,
63 ATTRIBUTE13,
64 ATTRIBUTE14,
65 ATTRIBUTE15,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_LOGIN
71 ) values (
72 X_BENEFIT_ID,
73 X_OBJECT_VERSION_NUMBER,
74 X_BENEFIT_TYPE_CODE,
75 X_BENEFIT_STATUS_CODE,
76 X_BENEFIT_CODE,
77 X_DELETE_FLAG,
78 X_ADDITIONAL_INFO_1,
79 X_ADDITIONAL_INFO_2,
80 X_ATTRIBUTE_CATEGORY,
81 X_ATTRIBUTE1,
82 X_ATTRIBUTE2,
83 X_ATTRIBUTE3,
84 X_ATTRIBUTE4,
85 X_ATTRIBUTE5,
86 X_ATTRIBUTE6,
87 X_ATTRIBUTE7,
88 X_ATTRIBUTE8,
89 X_ATTRIBUTE9,
90 X_ATTRIBUTE10,
91 X_ATTRIBUTE11,
92 X_ATTRIBUTE12,
93 X_ATTRIBUTE13,
94 X_ATTRIBUTE14,
95 X_ATTRIBUTE15,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_DATE,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_LOGIN
101 );
102
103 insert into PV_GE_BENEFITS_TL (
104 BENEFIT_ID,
105 BENEFIT_NAME,
106 DESCRIPTION,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATE_LOGIN,
112 LANGUAGE,
113 SOURCE_LANG
114 ) select
115 X_BENEFIT_ID,
116 X_BENEFIT_NAME,
117 X_DESCRIPTION,
118 X_CREATED_BY,
119 X_CREATION_DATE,
120 X_LAST_UPDATED_BY,
121 X_LAST_UPDATE_DATE,
122 X_LAST_UPDATE_LOGIN,
123 L.LANGUAGE_CODE,
124 userenv('LANG')
125 from FND_LANGUAGES L
126 where L.INSTALLED_FLAG in ('I', 'B')
127 and not exists
128 (select NULL
129 from PV_GE_BENEFITS_TL T
130 where T.BENEFIT_ID = X_BENEFIT_ID
131 and T.LANGUAGE = L.LANGUAGE_CODE);
132
133 open c;
134 fetch c into X_ROWID;
135 if (c%notfound) then
136 close c;
137 raise no_data_found;
138 end if;
139 close c;
140
141 end INSERT_ROW;
142
143 procedure LOCK_ROW (
144 X_BENEFIT_ID in NUMBER,
145 X_OBJECT_VERSION_NUMBER in NUMBER,
146 X_BENEFIT_TYPE_CODE in VARCHAR2,
147 X_BENEFIT_STATUS_CODE in VARCHAR2,
148 X_BENEFIT_CODE in VARCHAR2,
149 X_DELETE_FLAG in VARCHAR2,
150 X_ADDITIONAL_INFO_1 in NUMBER,
151 X_ADDITIONAL_INFO_2 in VARCHAR2,
152 X_ATTRIBUTE_CATEGORY in VARCHAR2,
153 X_ATTRIBUTE1 in VARCHAR2,
154 X_ATTRIBUTE2 in VARCHAR2,
155 X_ATTRIBUTE3 in VARCHAR2,
156 X_ATTRIBUTE4 in VARCHAR2,
157 X_ATTRIBUTE5 in VARCHAR2,
158 X_ATTRIBUTE6 in VARCHAR2,
159 X_ATTRIBUTE7 in VARCHAR2,
160 X_ATTRIBUTE8 in VARCHAR2,
161 X_ATTRIBUTE9 in VARCHAR2,
162 X_ATTRIBUTE10 in VARCHAR2,
163 X_ATTRIBUTE11 in VARCHAR2,
164 X_ATTRIBUTE12 in VARCHAR2,
165 X_ATTRIBUTE13 in VARCHAR2,
166 X_ATTRIBUTE14 in VARCHAR2,
167 X_ATTRIBUTE15 in VARCHAR2,
168 X_BENEFIT_NAME in VARCHAR2,
169 X_DESCRIPTION in VARCHAR2
170 ) is
171 cursor c is select
172 OBJECT_VERSION_NUMBER,
173 BENEFIT_TYPE_CODE,
174 BENEFIT_STATUS_CODE,
175 BENEFIT_CODE,
176 DELETE_FLAG,
177 ADDITIONAL_INFO_1,
178 ADDITIONAL_INFO_2,
179 ATTRIBUTE_CATEGORY,
180 ATTRIBUTE1,
181 ATTRIBUTE2,
182 ATTRIBUTE3,
183 ATTRIBUTE4,
184 ATTRIBUTE5,
185 ATTRIBUTE6,
186 ATTRIBUTE7,
187 ATTRIBUTE8,
188 ATTRIBUTE9,
189 ATTRIBUTE10,
190 ATTRIBUTE11,
191 ATTRIBUTE12,
192 ATTRIBUTE13,
193 ATTRIBUTE14,
194 ATTRIBUTE15
195 from PV_GE_BENEFITS_B
196 where BENEFIT_ID = X_BENEFIT_ID
197 for update of BENEFIT_ID nowait;
198 recinfo c%rowtype;
199
200 cursor c1 is select
201 BENEFIT_NAME,
202 DESCRIPTION,
203 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
204 from PV_GE_BENEFITS_TL
205 where BENEFIT_ID = X_BENEFIT_ID
206 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
207 for update of BENEFIT_ID nowait;
208 begin
209 open c;
210 fetch c into recinfo;
211 if (c%notfound) then
212 close c;
213 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
214 app_exception.raise_exception;
215 end if;
216 close c;
217 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
218 AND (recinfo.BENEFIT_TYPE_CODE = X_BENEFIT_TYPE_CODE)
219 AND (recinfo.BENEFIT_STATUS_CODE = X_BENEFIT_STATUS_CODE)
220 AND ((recinfo.BENEFIT_CODE = X_BENEFIT_CODE)
221 OR ((recinfo.BENEFIT_CODE is null) AND (X_BENEFIT_CODE is null)))
222 AND (recinfo.DELETE_FLAG = X_DELETE_FLAG)
223 AND ((recinfo.ADDITIONAL_INFO_1 = X_ADDITIONAL_INFO_1)
224 OR ((recinfo.ADDITIONAL_INFO_1 is null) AND (X_ADDITIONAL_INFO_1 is null)))
225 AND ((recinfo.ADDITIONAL_INFO_2 = X_ADDITIONAL_INFO_2)
226 OR ((recinfo.ADDITIONAL_INFO_2 is null) AND (X_ADDITIONAL_INFO_2 is null)))
227 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
228 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
229 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
230 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
231 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
232 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
233 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
234 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
235 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
236 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
237 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
238 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
239 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
240 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
241 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
242 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
243 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
244 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
245 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
246 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
247 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
248 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
249 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
250 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
251 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
252 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
253 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
254 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
255 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
256 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
257 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
258 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
259 ) then
260 null;
261 else
262 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
263 app_exception.raise_exception;
264 end if;
265
266 for tlinfo in c1 loop
267 if (tlinfo.BASELANG = 'Y') then
268 if ( ((tlinfo.BENEFIT_NAME = X_BENEFIT_NAME)
269 OR ((tlinfo.BENEFIT_NAME is null) AND (X_BENEFIT_NAME is null)))
270 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
271 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
272 ) then
273 null;
274 else
275 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
276 app_exception.raise_exception;
277 end if;
278 end if;
279 end loop;
280 return;
281 end LOCK_ROW;
282
283 procedure UPDATE_ROW (
284 X_BENEFIT_ID in NUMBER,
285 X_OBJECT_VERSION_NUMBER in NUMBER,
286 X_BENEFIT_TYPE_CODE in VARCHAR2,
287 X_BENEFIT_STATUS_CODE in VARCHAR2,
288 X_BENEFIT_CODE in VARCHAR2,
289 X_DELETE_FLAG in VARCHAR2,
290 X_ADDITIONAL_INFO_1 in NUMBER,
291 X_ADDITIONAL_INFO_2 in VARCHAR2,
292 X_ATTRIBUTE_CATEGORY in VARCHAR2,
293 X_ATTRIBUTE1 in VARCHAR2,
294 X_ATTRIBUTE2 in VARCHAR2,
295 X_ATTRIBUTE3 in VARCHAR2,
296 X_ATTRIBUTE4 in VARCHAR2,
297 X_ATTRIBUTE5 in VARCHAR2,
298 X_ATTRIBUTE6 in VARCHAR2,
299 X_ATTRIBUTE7 in VARCHAR2,
300 X_ATTRIBUTE8 in VARCHAR2,
301 X_ATTRIBUTE9 in VARCHAR2,
302 X_ATTRIBUTE10 in VARCHAR2,
303 X_ATTRIBUTE11 in VARCHAR2,
304 X_ATTRIBUTE12 in VARCHAR2,
305 X_ATTRIBUTE13 in VARCHAR2,
306 X_ATTRIBUTE14 in VARCHAR2,
307 X_ATTRIBUTE15 in VARCHAR2,
308 X_BENEFIT_NAME in VARCHAR2,
309 X_DESCRIPTION in VARCHAR2,
310 X_LAST_UPDATE_DATE in DATE,
311 X_LAST_UPDATED_BY in NUMBER,
312 X_LAST_UPDATE_LOGIN in NUMBER
313 ) is
314 begin
315 update PV_GE_BENEFITS_B set
316 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
317 BENEFIT_TYPE_CODE = X_BENEFIT_TYPE_CODE,
318 BENEFIT_STATUS_CODE = X_BENEFIT_STATUS_CODE,
319 BENEFIT_CODE = X_BENEFIT_CODE,
320 DELETE_FLAG = X_DELETE_FLAG,
321 ADDITIONAL_INFO_1 = X_ADDITIONAL_INFO_1,
322 ADDITIONAL_INFO_2 = X_ADDITIONAL_INFO_2,
323 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
324 ATTRIBUTE1 = X_ATTRIBUTE1,
325 ATTRIBUTE2 = X_ATTRIBUTE2,
326 ATTRIBUTE3 = X_ATTRIBUTE3,
327 ATTRIBUTE4 = X_ATTRIBUTE4,
328 ATTRIBUTE5 = X_ATTRIBUTE5,
329 ATTRIBUTE6 = X_ATTRIBUTE6,
330 ATTRIBUTE7 = X_ATTRIBUTE7,
331 ATTRIBUTE8 = X_ATTRIBUTE8,
332 ATTRIBUTE9 = X_ATTRIBUTE9,
333 ATTRIBUTE10 = X_ATTRIBUTE10,
334 ATTRIBUTE11 = X_ATTRIBUTE11,
335 ATTRIBUTE12 = X_ATTRIBUTE12,
336 ATTRIBUTE13 = X_ATTRIBUTE13,
337 ATTRIBUTE14 = X_ATTRIBUTE14,
338 ATTRIBUTE15 = X_ATTRIBUTE15,
339 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
340 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
341 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
342 where BENEFIT_ID = X_BENEFIT_ID;
343
344 if (sql%notfound) then
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346 end if;
347
348 update PV_GE_BENEFITS_TL set
349 BENEFIT_NAME = X_BENEFIT_NAME,
350 DESCRIPTION = X_DESCRIPTION,
351 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
352 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
353 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
354 SOURCE_LANG = userenv('LANG')
355 where BENEFIT_ID = X_BENEFIT_ID
356 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
357
358 if (sql%notfound) then
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 end if;
361 end UPDATE_ROW;
362
363 procedure UPDATE_SEED_ROW (
364 X_BENEFIT_ID in NUMBER,
365 X_OBJECT_VERSION_NUMBER in NUMBER,
366 X_BENEFIT_TYPE_CODE in VARCHAR2,
367 X_BENEFIT_STATUS_CODE in VARCHAR2,
368 X_BENEFIT_CODE in VARCHAR2,
369 X_DELETE_FLAG in VARCHAR2,
370 X_ADDITIONAL_INFO_1 in NUMBER,
371 X_ADDITIONAL_INFO_2 in VARCHAR2,
372 X_ATTRIBUTE_CATEGORY in VARCHAR2,
373 X_ATTRIBUTE1 in VARCHAR2,
374 X_ATTRIBUTE2 in VARCHAR2,
375 X_ATTRIBUTE3 in VARCHAR2,
376 X_ATTRIBUTE4 in VARCHAR2,
377 X_ATTRIBUTE5 in VARCHAR2,
378 X_ATTRIBUTE6 in VARCHAR2,
379 X_ATTRIBUTE7 in VARCHAR2,
380 X_ATTRIBUTE8 in VARCHAR2,
381 X_ATTRIBUTE9 in VARCHAR2,
382 X_ATTRIBUTE10 in VARCHAR2,
383 X_ATTRIBUTE11 in VARCHAR2,
384 X_ATTRIBUTE12 in VARCHAR2,
385 X_ATTRIBUTE13 in VARCHAR2,
386 X_ATTRIBUTE14 in VARCHAR2,
387 X_ATTRIBUTE15 in VARCHAR2,
388 X_BENEFIT_NAME in VARCHAR2,
389 X_DESCRIPTION in VARCHAR2,
390 X_LAST_UPDATE_DATE in DATE,
391 X_LAST_UPDATED_BY in NUMBER,
392 X_LAST_UPDATE_LOGIN in NUMBER
393 )
394 IS
395
396 CURSOR c_updated_by
397 IS
398 SELECT last_updated_by
399 FROM pv_ge_benefits_vl
400 WHERE benefit_id = X_BENEFIT_ID;
401
402 l_last_updated_by number;
403
404 BEGIN
405
406 for x in c_updated_by
407 loop
408 l_last_updated_by := x.last_updated_by;
409 end loop;
410
411 IF ( l_last_updated_by = 1) THEN
412 PV_GE_BENEFITS_PKG.UPDATE_ROW (
413 X_BENEFIT_ID => x_BENEFIT_ID
414 , X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
415 , X_BENEFIT_TYPE_CODE => x_BENEFIT_TYPE_CODE
416 , X_BENEFIT_STATUS_CODE => x_BENEFIT_STATUS_CODE
417 , X_BENEFIT_CODE => x_BENEFIT_CODE
418 , X_DELETE_FLAG => x_DELETE_FLAG
419 , X_ADDITIONAL_INFO_1 => x_ADDITIONAL_INFO_1
420 , X_ADDITIONAL_INFO_2 => x_ADDITIONAL_INFO_2
421 , X_ATTRIBUTE_CATEGORY => x_ATTRIBUTE_CATEGORY
422 , X_ATTRIBUTE1 => x_ATTRIBUTE1
423 , X_ATTRIBUTE2 => x_ATTRIBUTE2
424 , X_ATTRIBUTE3 => x_ATTRIBUTE3
425 , X_ATTRIBUTE4 => x_ATTRIBUTE4
426 , X_ATTRIBUTE5 => x_ATTRIBUTE5
427 , X_ATTRIBUTE6 => x_ATTRIBUTE6
428 , X_ATTRIBUTE7 => x_ATTRIBUTE7
429 , X_ATTRIBUTE8 => x_ATTRIBUTE8
430 , X_ATTRIBUTE9 => x_ATTRIBUTE9
431 , X_ATTRIBUTE10 => x_ATTRIBUTE10
432 , X_ATTRIBUTE11 => x_ATTRIBUTE11
433 , X_ATTRIBUTE12 => x_ATTRIBUTE12
434 , X_ATTRIBUTE13 => x_ATTRIBUTE13
435 , X_ATTRIBUTE14 => x_ATTRIBUTE14
436 , X_ATTRIBUTE15 => x_ATTRIBUTE15
437 , X_BENEFIT_NAME => x_BENEFIT_NAME
438 , X_DESCRIPTION => x_DESCRIPTION
439 , X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
440 , X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
441 , X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
442 );
443 ELSE
444 PV_GE_BENEFITS_PKG.SEED_UPDATE_ROW (
445 X_BENEFIT_ID => x_BENEFIT_ID
446 , X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
447 , X_BENEFIT_TYPE_CODE => x_BENEFIT_TYPE_CODE
451 , X_ADDITIONAL_INFO_1 => x_ADDITIONAL_INFO_1
448 , X_BENEFIT_STATUS_CODE => x_BENEFIT_STATUS_CODE
449 , X_BENEFIT_CODE => x_BENEFIT_CODE
450 , X_DELETE_FLAG => x_DELETE_FLAG
452 , X_ADDITIONAL_INFO_2 => x_ADDITIONAL_INFO_2
453 , X_ATTRIBUTE_CATEGORY => x_ATTRIBUTE_CATEGORY
454 , X_ATTRIBUTE1 => x_ATTRIBUTE1
455 , X_ATTRIBUTE2 => x_ATTRIBUTE2
456 , X_ATTRIBUTE3 => x_ATTRIBUTE3
457 , X_ATTRIBUTE4 => x_ATTRIBUTE4
458 , X_ATTRIBUTE5 => x_ATTRIBUTE5
459 , X_ATTRIBUTE6 => x_ATTRIBUTE6
460 , X_ATTRIBUTE7 => x_ATTRIBUTE7
461 , X_ATTRIBUTE8 => x_ATTRIBUTE8
462 , X_ATTRIBUTE9 => x_ATTRIBUTE9
463 , X_ATTRIBUTE10 => x_ATTRIBUTE10
464 , X_ATTRIBUTE11 => x_ATTRIBUTE11
465 , X_ATTRIBUTE12 => x_ATTRIBUTE12
466 , X_ATTRIBUTE13 => x_ATTRIBUTE13
467 , X_ATTRIBUTE14 => x_ATTRIBUTE14
468 , X_ATTRIBUTE15 => x_ATTRIBUTE15
469 , X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
470 , X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
471 , X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
472 );
473 END IF;
474
475
476 END UPDATE_SEED_ROW;
477
478 procedure SEED_UPDATE_ROW (
479 X_BENEFIT_ID in NUMBER,
480 X_OBJECT_VERSION_NUMBER in NUMBER,
481 X_BENEFIT_TYPE_CODE in VARCHAR2,
482 X_BENEFIT_STATUS_CODE in VARCHAR2,
483 X_BENEFIT_CODE in VARCHAR2,
484 X_DELETE_FLAG in VARCHAR2,
485 X_ADDITIONAL_INFO_1 in NUMBER,
486 X_ADDITIONAL_INFO_2 in VARCHAR2,
487 X_ATTRIBUTE_CATEGORY in VARCHAR2,
488 X_ATTRIBUTE1 in VARCHAR2,
489 X_ATTRIBUTE2 in VARCHAR2,
490 X_ATTRIBUTE3 in VARCHAR2,
491 X_ATTRIBUTE4 in VARCHAR2,
492 X_ATTRIBUTE5 in VARCHAR2,
493 X_ATTRIBUTE6 in VARCHAR2,
494 X_ATTRIBUTE7 in VARCHAR2,
495 X_ATTRIBUTE8 in VARCHAR2,
496 X_ATTRIBUTE9 in VARCHAR2,
497 X_ATTRIBUTE10 in VARCHAR2,
498 X_ATTRIBUTE11 in VARCHAR2,
499 X_ATTRIBUTE12 in VARCHAR2,
500 X_ATTRIBUTE13 in VARCHAR2,
501 X_ATTRIBUTE14 in VARCHAR2,
502 X_ATTRIBUTE15 in VARCHAR2,
503 X_LAST_UPDATE_DATE in DATE,
504 X_LAST_UPDATED_BY in NUMBER,
505 X_LAST_UPDATE_LOGIN in NUMBER
506 )
507 IS
508 BEGIN
509 update PV_GE_BENEFITS_B set
510 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
511 BENEFIT_TYPE_CODE = X_BENEFIT_TYPE_CODE,
512 BENEFIT_STATUS_CODE = X_BENEFIT_STATUS_CODE,
513 BENEFIT_CODE = X_BENEFIT_CODE,
514 DELETE_FLAG = X_DELETE_FLAG,
515 ADDITIONAL_INFO_1 = X_ADDITIONAL_INFO_1,
516 ADDITIONAL_INFO_2 = X_ADDITIONAL_INFO_2,
517 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
518 ATTRIBUTE1 = X_ATTRIBUTE1,
519 ATTRIBUTE2 = X_ATTRIBUTE2,
520 ATTRIBUTE3 = X_ATTRIBUTE3,
521 ATTRIBUTE4 = X_ATTRIBUTE4,
522 ATTRIBUTE5 = X_ATTRIBUTE5,
523 ATTRIBUTE6 = X_ATTRIBUTE6,
524 ATTRIBUTE7 = X_ATTRIBUTE7,
525 ATTRIBUTE8 = X_ATTRIBUTE8,
526 ATTRIBUTE9 = X_ATTRIBUTE9,
527 ATTRIBUTE10 = X_ATTRIBUTE10,
528 ATTRIBUTE11 = X_ATTRIBUTE11,
529 ATTRIBUTE12 = X_ATTRIBUTE12,
530 ATTRIBUTE13 = X_ATTRIBUTE13,
531 ATTRIBUTE14 = X_ATTRIBUTE14,
532 ATTRIBUTE15 = X_ATTRIBUTE15,
533 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
534 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
535 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
536 where BENEFIT_ID = X_BENEFIT_ID;
537
538 if (sql%notfound) then
539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
540 end if;
541 END SEED_UPDATE_ROW;
542
543 procedure DELETE_ROW (
544 X_BENEFIT_ID in NUMBER
545 ) is
546 begin
547 delete from PV_GE_BENEFITS_TL
548 where BENEFIT_ID = X_BENEFIT_ID;
549
550 if (sql%notfound) then
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 end if;
553
554 delete from PV_GE_BENEFITS_B
555 where BENEFIT_ID = X_BENEFIT_ID;
556
557 if (sql%notfound) then
558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
559 end if;
560 end DELETE_ROW;
561
562 procedure ADD_LANGUAGE
563 is
564 begin
565 delete from PV_GE_BENEFITS_TL T
566 where not exists
567 (select NULL
568 from PV_GE_BENEFITS_B B
572 update PV_GE_BENEFITS_TL T set (
569 where B.BENEFIT_ID = T.BENEFIT_ID
570 );
571
573 BENEFIT_NAME,
574 DESCRIPTION
575 ) = (select
576 B.BENEFIT_NAME,
577 B.DESCRIPTION
578 from PV_GE_BENEFITS_TL B
579 where B.BENEFIT_ID = T.BENEFIT_ID
580 and B.LANGUAGE = T.SOURCE_LANG)
581 where (
582 T.BENEFIT_ID,
583 T.LANGUAGE
584 ) in (select
585 SUBT.BENEFIT_ID,
586 SUBT.LANGUAGE
587 from PV_GE_BENEFITS_TL SUBB, PV_GE_BENEFITS_TL SUBT
588 where SUBB.BENEFIT_ID = SUBT.BENEFIT_ID
589 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
590 and (SUBB.BENEFIT_NAME <> SUBT.BENEFIT_NAME
591 or (SUBB.BENEFIT_NAME is null and SUBT.BENEFIT_NAME is not null)
592 or (SUBB.BENEFIT_NAME is not null and SUBT.BENEFIT_NAME is null)
593 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
594 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
595 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
596 ));
597
598 insert into PV_GE_BENEFITS_TL (
599 BENEFIT_ID,
600 BENEFIT_NAME,
601 DESCRIPTION,
602 CREATED_BY,
603 CREATION_DATE,
604 LAST_UPDATED_BY,
605 LAST_UPDATE_DATE,
606 LAST_UPDATE_LOGIN,
607 LANGUAGE,
608 SOURCE_LANG
609 ) select /*+ ORDERED */
610 B.BENEFIT_ID,
611 B.BENEFIT_NAME,
612 B.DESCRIPTION,
613 B.CREATED_BY,
614 B.CREATION_DATE,
615 B.LAST_UPDATED_BY,
616 B.LAST_UPDATE_DATE,
617 B.LAST_UPDATE_LOGIN,
618 L.LANGUAGE_CODE,
619 B.SOURCE_LANG
620 from PV_GE_BENEFITS_TL B, FND_LANGUAGES L
621 where L.INSTALLED_FLAG in ('I', 'B')
622 and B.LANGUAGE = userenv('LANG')
623 and not exists
624 (select NULL
625 from PV_GE_BENEFITS_TL T
626 where T.BENEFIT_ID = B.BENEFIT_ID
627 and T.LANGUAGE = L.LANGUAGE_CODE);
628 end ADD_LANGUAGE;
629
630 --procedure TRANSLATE_ROW
631 procedure TRANSLATE_ROW(
632 p_benefit_id in VARCHAR2
633 , p_benefit_name in VARCHAR2
634 , p_description in VARCHAR2
635 , p_owner in VARCHAR2
636 ) is
637 begin
638 update PV_GE_BENEFITS_TL set
639 description = nvl(p_description, description),
640 benefit_name = nvl( p_benefit_name, benefit_name),
641 source_lang = userenv('LANG'),
642 last_update_date = sysdate,
643 last_updated_by = decode(p_owner, 'SEED', 1, 0),
644 last_update_login = 0
645 where benefit_id = p_benefit_id
646 and userenv('LANG') in (language, source_lang);
647
648 end TRANSLATE_ROW;
649
650 PROCEDURE LOAD_ROW
651 ( p_BENEFIT_ID IN NUMBER,
652 p_BENEFIT_TYPE_CODE in VARCHAR2,
653 p_BENEFIT_STATUS_CODE in VARCHAR2,
654 p_BENEFIT_CODE in VARCHAR2,
655 p_DELETE_FLAG in VARCHAR2,
656 p_ADDITIONAL_INFO_1 in NUMBER,
657 p_ADDITIONAL_INFO_2 in VARCHAR2,
658 p_ATTRIBUTE_CATEGORY in VARCHAR2,
659 p_ATTRIBUTE1 in VARCHAR2,
660 p_ATTRIBUTE2 in VARCHAR2,
661 p_ATTRIBUTE3 in VARCHAR2,
662 p_ATTRIBUTE4 in VARCHAR2,
663 p_ATTRIBUTE5 in VARCHAR2,
664 p_ATTRIBUTE6 in VARCHAR2,
665 p_ATTRIBUTE7 in VARCHAR2,
666 p_ATTRIBUTE8 in VARCHAR2,
667 p_ATTRIBUTE9 in VARCHAR2,
668 p_ATTRIBUTE10 in VARCHAR2,
669 p_ATTRIBUTE11 in VARCHAR2,
670 p_ATTRIBUTE12 in VARCHAR2,
671 p_ATTRIBUTE13 in VARCHAR2,
672 p_ATTRIBUTE14 in VARCHAR2,
673 p_ATTRIBUTE15 in VARCHAR2,
674 p_BENEFIT_NAME in VARCHAR2,
675 p_DESCRIPTION in VARCHAR2,
676 p_owner in VARCHAR2 )
677 IS
678 L_USER_ID number := 0;
679 L_OBJ_VERNO number;
680 L_DUMMY_CHAR varchar2(1);
681 L_ROW_ID varchar2(100);
682 L_BENEFIT_ID number;
683
684 cursor C_OBJ_VERNO is
685 select OBJECT_VERSION_NUMBER
686 from PV_GE_BENEFITS_B
687 where BENEFIT_ID = l_BENEFIT_ID;
688
689 cursor c_chk_benefit_exists is
690 select 'x'
691 from PV_GE_BENEFITS_B
692 where BENEFIT_ID = l_BENEFIT_ID;
693
694
695 BEGIN
696
697
698 L_BENEFIT_ID := p_benefit_id;
699
700 if p_owner = 'SEED' then
701 l_user_id := 1;
702 end if;
703
704 open c_chk_benefit_exists;
705 fetch c_chk_benefit_exists into l_dummy_char;
706
707 if c_chk_benefit_exists%notfound then
708 close c_chk_benefit_exists;
709 l_obj_verno := 1;
710 PV_GE_BENEFITS_PKG.INSERT_ROW (
711 X_ROWID => L_ROW_ID
712 , X_BENEFIT_ID => p_BENEFIT_ID
713 , X_OBJECT_VERSION_NUMBER => L_OBJ_VERNO
714 , X_BENEFIT_TYPE_CODE => p_BENEFIT_TYPE_CODE
715 , X_BENEFIT_STATUS_CODE => p_BENEFIT_STATUS_CODE
716 , X_BENEFIT_CODE => p_BENEFIT_CODE
717 , X_DELETE_FLAG => p_DELETE_FLAG
718 , X_ADDITIONAL_INFO_1 => p_ADDITIONAL_INFO_1
719 , X_ADDITIONAL_INFO_2 => p_ADDITIONAL_INFO_2
720 , X_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY
721 , X_ATTRIBUTE1 => p_ATTRIBUTE1
722 , X_ATTRIBUTE2 => p_ATTRIBUTE2
723 , X_ATTRIBUTE3 => p_ATTRIBUTE3
724 , X_ATTRIBUTE4 => p_ATTRIBUTE4
725 , X_ATTRIBUTE5 => p_ATTRIBUTE5
726 , X_ATTRIBUTE6 => p_ATTRIBUTE6
727 , X_ATTRIBUTE7 => p_ATTRIBUTE7
728 , X_ATTRIBUTE8 => p_ATTRIBUTE8
729 , X_ATTRIBUTE9 => p_ATTRIBUTE9
730 , X_ATTRIBUTE10 => p_ATTRIBUTE10
731 , X_ATTRIBUTE11 => p_ATTRIBUTE11
732 , X_ATTRIBUTE12 => p_ATTRIBUTE12
733 , X_ATTRIBUTE13 => p_ATTRIBUTE13
734 , X_ATTRIBUTE14 => p_ATTRIBUTE14
738 , X_CREATION_DATE => SYSDATE
735 , X_ATTRIBUTE15 => p_ATTRIBUTE15
736 , X_BENEFIT_NAME => p_BENEFIT_NAME
737 , X_DESCRIPTION => p_DESCRIPTION
739 , X_CREATED_BY => l_user_id
740 , X_LAST_UPDATE_DATE => SYSDATE
741 , X_LAST_UPDATED_BY => l_user_id
742 , X_LAST_UPDATE_LOGIN => 0
743 );
744
745 else
746
747 close c_chk_benefit_exists;
748 open c_obj_verno;
749 fetch c_obj_verno into l_obj_verno;
750 close c_obj_verno;
751
752 PV_GE_BENEFITS_PKG.UPDATE_SEED_ROW (
753 X_BENEFIT_ID => p_BENEFIT_ID
754 , X_OBJECT_VERSION_NUMBER => L_OBJ_VERNO
755 , X_BENEFIT_TYPE_CODE => p_BENEFIT_TYPE_CODE
756 , X_BENEFIT_STATUS_CODE => p_BENEFIT_STATUS_CODE
757 , X_BENEFIT_CODE => p_BENEFIT_CODE
758 , X_DELETE_FLAG => p_DELETE_FLAG
759 , X_ADDITIONAL_INFO_1 => p_ADDITIONAL_INFO_1
760 , X_ADDITIONAL_INFO_2 => p_ADDITIONAL_INFO_2
761 , X_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY
762 , X_ATTRIBUTE1 => p_ATTRIBUTE1
763 , X_ATTRIBUTE2 => p_ATTRIBUTE2
764 , X_ATTRIBUTE3 => p_ATTRIBUTE3
765 , X_ATTRIBUTE4 => p_ATTRIBUTE4
766 , X_ATTRIBUTE5 => p_ATTRIBUTE5
767 , X_ATTRIBUTE6 => p_ATTRIBUTE6
768 , X_ATTRIBUTE7 => p_ATTRIBUTE7
769 , X_ATTRIBUTE8 => p_ATTRIBUTE8
770 , X_ATTRIBUTE9 => p_ATTRIBUTE9
771 , X_ATTRIBUTE10 => p_ATTRIBUTE10
772 , X_ATTRIBUTE11 => p_ATTRIBUTE11
773 , X_ATTRIBUTE12 => p_ATTRIBUTE12
774 , X_ATTRIBUTE13 => p_ATTRIBUTE13
775 , X_ATTRIBUTE14 => p_ATTRIBUTE14
776 , X_ATTRIBUTE15 => p_ATTRIBUTE15
777 , X_BENEFIT_NAME => p_BENEFIT_NAME
778 , X_DESCRIPTION => p_DESCRIPTION
779 , X_LAST_UPDATE_DATE => SYSDATE
780 , X_LAST_UPDATED_BY => l_user_id
781 , X_LAST_UPDATE_LOGIN => 0
782 );
783
784 end if;
785
786 END;
787 PROCEDURE load_seed_row
788 ( p_UPLOAD_MODE in VARCHAR2,
789 p_BENEFIT_ID IN NUMBER,
790 p_BENEFIT_TYPE_CODE in VARCHAR2,
791 p_BENEFIT_STATUS_CODE in VARCHAR2,
792 p_BENEFIT_CODE in VARCHAR2,
793 p_DELETE_FLAG in VARCHAR2,
794 p_ADDITIONAL_INFO_1 in NUMBER,
795 p_ADDITIONAL_INFO_2 in VARCHAR2,
796 p_ATTRIBUTE_CATEGORY in VARCHAR2,
797 p_ATTRIBUTE1 in VARCHAR2,
798 p_ATTRIBUTE2 in VARCHAR2,
799 p_ATTRIBUTE3 in VARCHAR2,
800 p_ATTRIBUTE4 in VARCHAR2,
801 p_ATTRIBUTE5 in VARCHAR2,
802 p_ATTRIBUTE6 in VARCHAR2,
803 p_ATTRIBUTE7 in VARCHAR2,
804 p_ATTRIBUTE8 in VARCHAR2,
805 p_ATTRIBUTE9 in VARCHAR2,
806 p_ATTRIBUTE10 in VARCHAR2,
807 p_ATTRIBUTE11 in VARCHAR2,
808 p_ATTRIBUTE12 in VARCHAR2,
809 p_ATTRIBUTE13 in VARCHAR2,
810 p_ATTRIBUTE14 in VARCHAR2,
811 p_ATTRIBUTE15 in VARCHAR2,
812 p_BENEFIT_NAME in VARCHAR2,
813 p_DESCRIPTION in VARCHAR2,
814 p_owner in VARCHAR2 )
815
816 IS
817 BEGIN
818 IF (p_upload_mode = 'NLS') THEN
819
820 TRANSLATE_ROW(
821 p_benefit_id => p_benefit_id
822 , p_benefit_name => p_benefit_name
823 , p_description => p_description
824 , p_owner => p_owner);
825 ELSE
826
827 LOAD_ROW (
828 p_benefit_id => p_benefit_id
829 ,p_benefit_type_code => p_benefit_type_code
830 ,p_benefit_status_code => p_benefit_status_code
831 ,p_benefit_code => p_benefit_code
832 ,p_delete_flag => p_delete_flag
833 ,p_additional_info_1 => p_additional_info_1
834 ,p_additional_info_2 => p_additional_info_2
835 ,p_attribute_category => p_attribute_category
836 ,p_attribute1 => p_attribute1
837 ,p_attribute2 => p_attribute2
838 ,p_attribute3 => p_attribute3
839 ,p_attribute4 => p_attribute4
840 ,p_attribute5 => p_attribute5
841 ,p_attribute6 => p_attribute6
842 ,p_attribute7 => p_attribute7
843 ,p_attribute8 => p_attribute8
844 ,p_attribute9 => p_attribute9
845 ,p_attribute10 => p_attribute10
846 ,p_attribute11 => p_attribute11
847 ,p_attribute12 => p_attribute12
848 ,p_attribute13 => p_attribute13
849 ,p_attribute14 => p_attribute14
850 ,p_attribute15 => p_attribute15
851 ,p_benefit_name => p_benefit_name
852 ,p_description => p_description
853 ,p_owner => p_owner);
854
855 END IF;
856
857 end LOAD_SEED_ROW;
858
859 end PV_GE_BENEFITS_PKG;