[Home] [Help]
PACKAGE BODY: APPS.PER_CAGR_ENTITLEMENT_ITEMS_PKG
Source
1 package body PER_CAGR_ENTITLEMENT_ITEMS_PKG as
2 /* $Header: pepcilct.pkb 120.2 2006/06/27 11:10:36 bshukla noship $ */
3
4
5 procedure KEY_TO_IDS (
6 X_ITEM_NAME in VARCHAR2,
7 X_BUSINESS_GROUP_NAME in VARCHAR2,
8 X_LEGISLATION_CODE in VARCHAR2,
9 X_ELEMENT_TYPE in VARCHAR2,
10 X_INPUT_VALUE in VARCHAR2,
11 X_CAGR_API in VARCHAR2,
12 X_CAGR_API_PARAM in VARCHAR2,
13 X_FLEX_VALUE_SET in VARCHAR2,
14 X_BENEFICIAL_VALUE_SET in VARCHAR2,
15 X_CAGR_ENTITLEMENT_ITEM_ID out nocopy NUMBER,
16 X_BUSINESS_GROUP_ID out nocopy NUMBER,
17 X_ELEMENT_TYPE_ID out nocopy NUMBER,
18 X_INPUT_VALUE_ID out nocopy VARCHAR2,
19 X_CAGR_API_ID out nocopy NUMBER,
20 X_CAGR_API_PARAM_ID out nocopy NUMBER,
21 X_FLEX_VALUE_SET_ID out nocopy NUMBER,
22 X_BENEFICIAL_RULE_VALUE_SET_ID out nocopy NUMBER
23 ) is
24 cursor CSR_BUSINESS_GROUP (
25 X_NAME in VARCHAR2
26 ) is
27 select pbg.business_group_id
28 from per_business_groups pbg
29 where pbg.name = X_NAME;
30
31 cursor CSR_ELEMENT_TYPE (
32 X_ELEMENT_TYPE in VARCHAR2
33 ) is
34 select ELMT.ELEMENT_TYPE_ID
35 from PAY_ELEMENT_TYPES_F_TL ELMT
36 where ELMT.ELEMENT_NAME = X_ELEMENT_TYPE;
37
38
39 cursor CSR_INPUT_VALUE (
40 X_INPUT_VALUE in VARCHAR2
41 ) is
42 select VALUE.INPUT_VALUE_ID
43 from PAY_INPUT_VALUES_F_TL VALUE
44 where VALUE.NAME = X_INPUT_VALUE;
45
46 cursor CSR_CAGR_API (
47 X_CAGR_API in VARCHAR2
48 ) is
49 select API.CAGR_API_ID
50 from PER_CAGR_APIS API
51 where API.API_NAME = X_CAGR_API;
52
53
54 cursor CSR_CAGR_API_PARAM (
55 X_CAGR_API_PARAM in VARCHAR2
56 ) is
57 select PARAM.CAGR_API_PARAM_ID
58 from PER_CAGR_API_PARAMETERS PARAM
59 where PARAM.DISPLAY_NAME = X_CAGR_API_PARAM;
60
61
62 cursor CSR_FLEX_VALUE_SET (
63 X_FLEX_VALUE_SET in VARCHAR2
64 ) is
65 select VSET.FLEX_VALUE_SET_ID
66 from FND_FLEX_VALUE_SETS VSET
67 where VSET.FLEX_VALUE_SET_NAME = X_FLEX_VALUE_SET;
68
69
70 cursor CSR_CAGR_ENTITLEMENT_ITEM_NAME (
71 X_ITEM_NAME VARCHAR2,
72 X_BUSINESS_GROUP_ID in NUMBER,
73 X_LEGISLATION_CODE in VARCHAR2
74 ) is
75 select CEI.CAGR_ENTITLEMENT_ITEM_ID
76 from PER_CAGR_ENTITLEMENT_ITEMS CEI
77 where CEI.ITEM_NAME = X_ITEM_NAME
78 and ( CEI.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
79 or ( CEI.BUSINESS_GROUP_ID is null
80 and X_BUSINESS_GROUP_ID is null))
81 and ( CEI.LEGISLATION_CODE = X_LEGISLATION_CODE
82 or ( CEI.LEGISLATION_CODE is null
83 and X_LEGISLATION_CODE is null));
84 cursor CSR_SEQUENCE is
85 select PER_CAGR_ENTITLEMENT_ITEMS_S.nextval
86 from dual;
87 L_BUSINESS_GROUP_ID NUMBER;
88 begin
89
90
91
92
93 open CSR_BUSINESS_GROUP (
94 X_BUSINESS_GROUP_NAME
95 );
96 fetch CSR_BUSINESS_GROUP into L_BUSINESS_GROUP_ID;
97 close CSR_BUSINESS_GROUP;
98 X_BUSINESS_GROUP_ID := L_BUSINESS_GROUP_ID;
99
100 open CSR_ELEMENT_TYPE (
101 X_ELEMENT_TYPE
102 );
103 fetch CSR_ELEMENT_TYPE into X_ELEMENT_TYPE_ID;
104 close CSR_ELEMENT_TYPE;
105
106 open CSR_INPUT_VALUE (
107 X_INPUT_VALUE
108 );
109 fetch CSR_INPUT_VALUE into X_INPUT_VALUE_ID;
110 close CSR_INPUT_VALUE;
111
112 open CSR_CAGR_API (
113 X_CAGR_API
114 );
115 fetch CSR_CAGR_API into X_CAGR_API_ID;
116 close CSR_CAGR_API;
117
118 open CSR_CAGR_API_PARAM (
119 X_CAGR_API_PARAM
120 );
121 fetch CSR_CAGR_API_PARAM into X_CAGR_API_PARAM_ID;
122 close CSR_CAGR_API_PARAM;
123
124 open CSR_FLEX_VALUE_SET (
125 X_FLEX_VALUE_SET
126 );
127 fetch CSR_FLEX_VALUE_SET into X_FLEX_VALUE_SET_ID;
128 close CSR_FLEX_VALUE_SET;
129
130 open CSR_FLEX_VALUE_SET (
131 X_BENEFICIAL_VALUE_SET
132 );
133 fetch CSR_FLEX_VALUE_SET into X_BENEFICIAL_RULE_VALUE_SET_ID;
134 close CSR_FLEX_VALUE_SET;
135
136 open CSR_CAGR_ENTITLEMENT_ITEM_NAME (
137 X_ITEM_NAME,
138 L_BUSINESS_GROUP_ID,
139 X_LEGISLATION_CODE
140 );
141 fetch CSR_CAGR_ENTITLEMENT_ITEM_NAME into X_CAGR_ENTITLEMENT_ITEM_ID;
142 if (CSR_CAGR_ENTITLEMENT_ITEM_NAME%notfound) then
143 open CSR_SEQUENCE;
144 fetch CSR_SEQUENCE into X_CAGR_ENTITLEMENT_ITEM_ID;
145 close CSR_SEQUENCE;
146 end if;
147 close CSR_CAGR_ENTITLEMENT_ITEM_NAME;
148 end KEY_TO_IDS;
149
150 procedure INSERT_ROW (
151 X_ROWID in out nocopy VARCHAR2,
152 X_CAGR_ENTITLEMENT_ITEM_ID in NUMBER,
153 X_BUSINESS_GROUP_ID in NUMBER,
154 X_ELEMENT_TYPE_ID in NUMBER,
155 X_INPUT_VALUE_ID in VARCHAR2,
156 X_COLUMN_TYPE in VARCHAR2,
157 X_COLUMN_SIZE in NUMBER,
158 X_LEGISLATION_CODE in VARCHAR2,
159 X_BENEFICIAL_RULE in VARCHAR2,
160 X_CAGR_API_ID in NUMBER,
161 X_CAGR_API_PARAM_ID in NUMBER,
162 X_CATEGORY_NAME in VARCHAR2,
163 X_UOM in VARCHAR2,
164 X_BENEFICIAL_FORMULA_ID in NUMBER,
165 X_FLEX_VALUE_SET_ID in NUMBER,
166 X_BENEFICIAL_RULE_VALUE_SET_ID in NUMBER,
167 X_OBJECT_VERSION_NUMBER in NUMBER,
168 X_ITEM_NAME in VARCHAR2,
169 X_MULTI_ENTRIES_ALLOWED_FLAG in VARCHAR2,
170 X_AUTO_CREATE_ENTRIES_FLAG in VARCHAR2,
171 X_CREATION_DATE in DATE,
172 X_CREATED_BY in NUMBER,
173 X_LAST_UPDATE_DATE in DATE,
174 X_LAST_UPDATED_BY in NUMBER,
175 X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 cursor C is select ROWID from PER_CAGR_ENTITLEMENT_ITEMS
178 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID
179 ;
180 begin
181 insert into PER_CAGR_ENTITLEMENT_ITEMS (
182 CAGR_ENTITLEMENT_ITEM_ID,
183 ITEM_NAME,
184 BUSINESS_GROUP_ID,
185 ELEMENT_TYPE_ID,
186 INPUT_VALUE_ID,
187 COLUMN_TYPE,
188 COLUMN_SIZE,
189 LEGISLATION_CODE,
190 BENEFICIAL_RULE,
191 CAGR_API_ID,
192 CAGR_API_PARAM_ID,
193 CATEGORY_NAME,
194 UOM,
195 BENEFICIAL_FORMULA_ID,
196 FLEX_VALUE_SET_ID,
197 BENEFICIAL_RULE_VALUE_SET_ID,
198 MULTIPLE_ENTRIES_ALLOWED_FLAG,
199 AUTO_CREATE_ENTRIES_FLAG,
200 OBJECT_VERSION_NUMBER,
201 CREATION_DATE,
202 CREATED_BY,
203 LAST_UPDATE_DATE,
204 LAST_UPDATED_BY,
205 LAST_UPDATE_LOGIN
206 ) values (
207 X_CAGR_ENTITLEMENT_ITEM_ID,
208 X_ITEM_NAME,
209 X_BUSINESS_GROUP_ID,
210 X_ELEMENT_TYPE_ID,
211 X_INPUT_VALUE_ID,
212 X_COLUMN_TYPE,
213 X_COLUMN_SIZE,
214 X_LEGISLATION_CODE,
215 X_BENEFICIAL_RULE,
216 X_CAGR_API_ID,
217 X_CAGR_API_PARAM_ID,
218 X_CATEGORY_NAME,
219 X_UOM,
220 X_BENEFICIAL_FORMULA_ID,
221 X_FLEX_VALUE_SET_ID,
222 X_BENEFICIAL_RULE_VALUE_SET_ID,
223 X_MULTI_ENTRIES_ALLOWED_FLAG,
224 X_AUTO_CREATE_ENTRIES_FLAG,
225 X_OBJECT_VERSION_NUMBER,
226 X_CREATION_DATE,
227 X_CREATED_BY,
228 X_LAST_UPDATE_DATE,
229 X_LAST_UPDATED_BY,
230 X_LAST_UPDATE_LOGIN
231 );
232
233 insert into PER_CAGR_ENTITLEMENT_ITEMS_TL (
234 CREATED_BY,
235 CREATION_DATE,
236 LAST_UPDATE_LOGIN,
237 LAST_UPDATE_DATE,
238 LAST_UPDATED_BY,
239 CAGR_ENTITLEMENT_ITEM_ID,
240 ITEM_NAME,
241 LANGUAGE,
242 SOURCE_LANG
243 ) select
244 X_CREATED_BY,
245 X_CREATION_DATE,
246 X_LAST_UPDATE_LOGIN,
247 X_LAST_UPDATE_DATE,
248 X_LAST_UPDATED_BY,
249 X_CAGR_ENTITLEMENT_ITEM_ID,
250 X_ITEM_NAME,
251 L.LANGUAGE_CODE,
252 userenv('LANG')
253 from FND_LANGUAGES L
254 where L.INSTALLED_FLAG in ('I', 'B')
255 and not exists
256 (select NULL
257 from PER_CAGR_ENTITLEMENT_ITEMS_TL T
258 where T.CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID
259 and T.LANGUAGE = L.LANGUAGE_CODE);
260
261 open c;
262 fetch c into X_ROWID;
263 if (c%notfound) then
264 close c;
265 raise no_data_found;
266 end if;
267 close c;
268
269 end INSERT_ROW;
270
271 procedure TRANSLATE_ROW (
272 X_ITEM_NAME1 in VARCHAR2 default null,
273 X_ITEM_NAME in VARCHAR2,
274 X_BUSINESS_GROUP_NAME in VARCHAR2,
275 X_LEGISLATION_CODE in VARCHAR2,
276 X_OWNER in VARCHAR2,
277 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
278 X_CUSTOM_MODE IN VARCHAR2 default null
279 ) is
280 X_CAGR_ENTITLEMENT_ITEM_ID NUMBER;
281 X_BUSINESS_GROUP_ID NUMBER;
282
283 X_ELEMENT_TYPE VARCHAR2(60);
284 X_INPUT_VALUE VARCHAR2 (60);
285 X_CAGR_API VARCHAR2 (60);
286 X_CAGR_API_PARAM VARCHAR2(60);
287 X_FLEX_VALUE_SET VARCHAR2(60);
288 X_BENEFICIAL_VALUE_SET VARCHAR2(60);
289
290 X_ELEMENT_TYPE_ID number;
291 X_INPUT_VALUE_ID varchar2(60);
292 X_CAGR_API_ID number;
293 X_CAGR_API_PARAM_ID number;
294 X_FLEX_VALUE_SET_ID number;
295 X_BENEFICIAL_RULE_VALUE_SET_ID number;
296
297 f_luby number; -- entity owner in file
298 f_ludate date; -- entity update date in file
299 db_luby number; -- entity owner in db
300 db_ludate date; -- entity update date in db
301
302 begin
303
304 KEY_TO_IDS (
305 X_ITEM_NAME1,
306 X_BUSINESS_GROUP_NAME,
307 X_LEGISLATION_CODE,
308 X_ELEMENT_TYPE,
309 X_INPUT_VALUE,
310 X_CAGR_API,
311 X_CAGR_API_PARAM,
312 X_FLEX_VALUE_SET,
313 X_BENEFICIAL_VALUE_SET,
314 X_CAGR_ENTITLEMENT_ITEM_ID,
315 X_BUSINESS_GROUP_ID,
316 X_ELEMENT_TYPE_ID,
317 X_INPUT_VALUE_ID,
318 X_CAGR_API_ID,
319 X_CAGR_API_PARAM_ID,
320 X_FLEX_VALUE_SET_ID,
321 X_BENEFICIAL_RULE_VALUE_SET_ID
322 );
323
324 -- Translate owner to file_last_updated_by
325 f_luby := fnd_load_util.owner_id(x_owner);
326
327 -- Translate char last_update_date to date
328 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
329 select LAST_UPDATED_BY, LAST_UPDATE_DATE
330 into db_luby, db_ludate
331 from PER_CAGR_ENTITLEMENT_ITEMS_TL
332 where CAGR_ENTITLEMENT_ITEM_ID = TO_NUMBER(X_CAGR_ENTITLEMENT_ITEM_ID)
333 and LANGUAGE=userenv('LANG');
334 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
335 db_ludate,X_CUSTOM_MODE)) then
336 update per_cagr_entitlement_items_tl set
337 item_name = X_ITEM_NAME,
338 last_update_date = f_ludate,
339 last_updated_by = f_luby,
340 last_update_login = 0,
341 source_lang = userenv('LANG')
342 where cagr_entitlement_item_id = X_CAGR_ENTITLEMENT_ITEM_ID
343 and userenv('LANG') in (language, source_lang);
344 end if;
345 end TRANSLATE_ROW;
346
347 procedure LOAD_ROW (
348 X_ITEM_NAME in VARCHAR2,
349 X_OWNER in VARCHAR2,
350 X_LEGISLATION_CODE in VARCHAR2,
351 X_BUSINESS_GROUP_NAME in VARCHAR2,
352 X_ELEMENT_TYPE in VARCHAR2,
353 X_INPUT_VALUE in VARCHAR2,
354 X_COLUMN_TYPE in VARCHAR2,
355 X_COLUMN_SIZE in NUMBER,
356 X_BENEFICIAL_RULE in VARCHAR2,
357 X_CAGR_API in VARCHAR2,
358 X_CAGR_API_PARAM in VARCHAR2,
359 X_CATEGORY_NAME in VARCHAR2,
360 X_UOM in VARCHAR2,
361 X_BENEFICIAL_FORMULA_ID in NUMBER,
362 X_FLEX_VALUE_SET in VARCHAR2,
363 X_BENEFICIAL_VALUE_SET in VARCHAR2,
364 X_MULTI_ENTRIES_ALLOWED_FLAG in VARCHAR2,
365 X_AUTO_CREATE_ENTRIES_FLAG in VARCHAR2,
366 X_OBJECT_VERSION_NUMBER in NUMBER,
367 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
368 X_CUSTOM_MODE IN VARCHAR2 default null)
369 is
370
371 X_ROWID ROWID;
372 user_id number := 0;
373 X_CAGR_ENTITLEMENT_ITEM_ID NUMBER;
374 X_BUSINESS_GROUP_ID NUMBER;
375 X_FLEX_VALUE_SET_ID NUMBER;
376 X_BENEFICIAL_RULE_VALUE_SET_ID NUMBER;
377 X_ELEMENT_TYPE_ID NUMBER;
378 X_INPUT_VALUE_ID VARCHAR2(60);
379 X_CAGR_API_ID NUMBER;
380 X_CAGR_API_PARAM_ID NUMBER;
381 f_luby number; -- entity owner in file
382 f_ludate date; -- entity update date in file
383 db_luby number; -- entity owner in db
384 db_ludate date; -- entity update date in db
385
386 begin
387
388 KEY_TO_IDS (
389 X_ITEM_NAME,
390 X_BUSINESS_GROUP_NAME,
391 X_LEGISLATION_CODE,
392 X_ELEMENT_TYPE,
393 X_INPUT_VALUE,
394 X_CAGR_API,
395 X_CAGR_API_PARAM,
396 X_FLEX_VALUE_SET,
397 X_BENEFICIAL_VALUE_SET,
398 X_CAGR_ENTITLEMENT_ITEM_ID,
399 X_BUSINESS_GROUP_ID,
400 X_ELEMENT_TYPE_ID,
401 X_INPUT_VALUE_ID,
402 X_CAGR_API_ID,
403 X_CAGR_API_PARAM_ID,
404 X_FLEX_VALUE_SET_ID,
405 X_BENEFICIAL_RULE_VALUE_SET_ID
406 );
407
408 if (X_OWNER = 'SEED') then
409 user_id := 1;
410 else
411 user_id := 0;
412 end if;
413
414 f_luby := fnd_load_util.owner_id(X_OWNER);
415 -- Translate char last_update_date to date
416 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
417 select LAST_UPDATED_BY, LAST_UPDATE_DATE
418 into db_luby, db_ludate
419 from PER_CAGR_ENTITLEMENT_ITEMS
420 where CAGR_ENTITLEMENT_ITEM_ID = TO_NUMBER(X_CAGR_ENTITLEMENT_ITEM_ID);
421
422 -- Test for customization and version
423 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
424 db_ludate, X_CUSTOM_MODE)) then
425 PER_CAGR_ENTITLEMENT_ITEMS_PKG.UPDATE_ROW (
426 X_CAGR_ENTITLEMENT_ITEM_ID => X_CAGR_ENTITLEMENT_ITEM_ID,
427 X_BUSINESS_GROUP_ID => X_BUSINESS_GROUP_ID,
428 X_ELEMENT_TYPE_ID => X_ELEMENT_TYPE_ID,
429 X_INPUT_VALUE_ID => X_INPUT_VALUE_ID,
430 X_COLUMN_TYPE => X_COLUMN_TYPE,
431 X_COLUMN_SIZE => X_COLUMN_SIZE,
432 X_LEGISLATION_CODE => X_LEGISLATION_CODE,
433 X_BENEFICIAL_RULE => X_BENEFICIAL_RULE,
434 X_CAGR_API_ID => X_CAGR_API_ID,
435 X_CAGR_API_PARAM_ID => X_CAGR_API_PARAM_ID,
436 X_CATEGORY_NAME => X_CATEGORY_NAME,
437 X_UOM => X_UOM,
438 X_BENEFICIAL_FORMULA_ID => X_BENEFICIAL_FORMULA_ID,
439 X_FLEX_VALUE_SET_ID => X_FLEX_VALUE_SET_ID,
440 X_BENEFICIAL_RULE_VALUE_SET_ID => X_BENEFICIAL_RULE_VALUE_SET_ID,
441 X_MULTI_ENTRIES_ALLOWED_FLAG => X_MULTI_ENTRIES_ALLOWED_FLAG,
442 X_AUTO_CREATE_ENTRIES_FLAG => X_AUTO_CREATE_ENTRIES_FLAG,
443 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
444 X_ITEM_NAME => X_ITEM_NAME,
445 X_LAST_UPDATE_DATE => db_ludate,
446 X_LAST_UPDATED_BY => db_luby,
447 X_LAST_UPDATE_LOGIN => 0);
448 end if;
449 exception
450 when NO_DATA_FOUND then
451
452
453 PER_CAGR_ENTITLEMENT_ITEMS_PKG.INSERT_ROW (
454 X_ROWID => X_ROWID,
455 X_CAGR_ENTITLEMENT_ITEM_ID => X_CAGR_ENTITLEMENT_ITEM_ID,
456 X_BUSINESS_GROUP_ID => X_BUSINESS_GROUP_ID,
457 X_ELEMENT_TYPE_ID => X_ELEMENT_TYPE_ID,
458 X_INPUT_VALUE_ID => X_INPUT_VALUE_ID,
459 X_COLUMN_TYPE => X_COLUMN_TYPE,
460 X_COLUMN_SIZE => X_COLUMN_SIZE,
461 X_LEGISLATION_CODE => X_LEGISLATION_CODE,
462 X_BENEFICIAL_RULE => X_BENEFICIAL_RULE,
463 X_CAGR_API_ID => X_CAGR_API_ID,
464 X_CAGR_API_PARAM_ID => X_CAGR_API_PARAM_ID,
465 X_CATEGORY_NAME => X_CATEGORY_NAME,
466 X_UOM => X_UOM,
467 X_BENEFICIAL_FORMULA_ID => X_BENEFICIAL_FORMULA_ID,
468 X_FLEX_VALUE_SET_ID => X_FLEX_VALUE_SET_ID,
469 X_BENEFICIAL_RULE_VALUE_SET_ID => X_BENEFICIAL_RULE_VALUE_SET_ID,
470 X_MULTI_ENTRIES_ALLOWED_FLAG => X_MULTI_ENTRIES_ALLOWED_FLAG,
471 X_AUTO_CREATE_ENTRIES_FLAG => X_AUTO_CREATE_ENTRIES_FLAG,
472 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
473 X_ITEM_NAME => X_ITEM_NAME,
474 X_LAST_UPDATE_DATE => db_ludate,
475 X_LAST_UPDATED_BY => db_luby,
476 X_LAST_UPDATE_LOGIN => 0,
477 X_CREATION_DATE => SYSDATE,
478 X_CREATED_BY => user_id);
479
480 end LOAD_ROW;
481
482 procedure LOCK_ROW (
483 X_CAGR_ENTITLEMENT_ITEM_ID in NUMBER,
484 X_BUSINESS_GROUP_ID in NUMBER,
485 X_ELEMENT_TYPE_ID in NUMBER,
486 X_INPUT_VALUE_ID in VARCHAR2,
487 X_COLUMN_TYPE in VARCHAR2,
488 X_COLUMN_SIZE in NUMBER,
489 X_LEGISLATION_CODE in VARCHAR2,
490 X_BENEFICIAL_RULE in VARCHAR2,
491 X_CAGR_API_ID in NUMBER,
492 X_CAGR_API_PARAM_ID in NUMBER,
493 X_CATEGORY_NAME in VARCHAR2,
494 X_UOM in VARCHAR2,
495 X_BENEFICIAL_FORMULA_ID in NUMBER,
496 X_FLEX_VALUE_SET_ID in NUMBER,
497 X_BENEFICIAL_RULE_VALUE_SET_ID in NUMBER,
498 X_OBJECT_VERSION_NUMBER in NUMBER,
499 X_MULTI_ENTRIES_ALLOWED_FLAG in VARCHAR2,
500 X_AUTO_CREATE_ENTRIES_FLAG in VARCHAR2,
501 X_ITEM_NAME in VARCHAR2
502 ) is
503 cursor c is select
504 BUSINESS_GROUP_ID,
505 ELEMENT_TYPE_ID,
506 INPUT_VALUE_ID,
507 COLUMN_TYPE,
508 COLUMN_SIZE,
509 LEGISLATION_CODE,
510 BENEFICIAL_RULE,
511 CAGR_API_ID,
512 CAGR_API_PARAM_ID,
513 CATEGORY_NAME,
514 UOM,
515 BENEFICIAL_FORMULA_ID,
516 FLEX_VALUE_SET_ID,
517 BENEFICIAL_RULE_VALUE_SET_ID,
518 MULTIPLE_ENTRIES_ALLOWED_FLAG,
519 AUTO_CREATE_ENTRIES_FLAG,
520 OBJECT_VERSION_NUMBER
521 from PER_CAGR_ENTITLEMENT_ITEMS
522 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID
523 for update of CAGR_ENTITLEMENT_ITEM_ID nowait;
524 recinfo c%rowtype;
525
526 cursor c1 is select
527 ITEM_NAME,
528 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
529 from PER_CAGR_ENTITLEMENT_ITEMS_TL
530 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID
531 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
532 for update of CAGR_ENTITLEMENT_ITEM_ID nowait;
533 begin
534 open c;
535 fetch c into recinfo;
536 if (c%notfound) then
537 close c;
538 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
539 app_exception.raise_exception;
540 end if;
541 close c;
542 if ( (recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
543 AND ((recinfo.ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID)
544 OR ((recinfo.ELEMENT_TYPE_ID is null) AND (X_ELEMENT_TYPE_ID is null)))
545 AND ((recinfo.INPUT_VALUE_ID = X_INPUT_VALUE_ID)
546 OR ((recinfo.INPUT_VALUE_ID is null) AND (X_INPUT_VALUE_ID is null)))
547 AND (recinfo.COLUMN_TYPE = X_COLUMN_TYPE)
548 AND (recinfo.COLUMN_SIZE = X_COLUMN_SIZE)
549 AND (recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
550 AND ((recinfo.BENEFICIAL_RULE = X_BENEFICIAL_RULE)
551 OR ((recinfo.BENEFICIAL_RULE is null) AND (X_BENEFICIAL_RULE is null)))
552 AND ((recinfo.CAGR_API_ID = X_CAGR_API_ID)
553 OR ((recinfo.CAGR_API_ID is null) AND (X_CAGR_API_ID is null)))
554 AND ((recinfo.CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID)
555 OR ((recinfo.CAGR_API_PARAM_ID is null) AND (X_CAGR_API_PARAM_ID is null)))
556 AND (recinfo.CATEGORY_NAME = X_CATEGORY_NAME)
557 AND ((recinfo.UOM = X_UOM)
558 OR ((recinfo.UOM is null) AND (X_UOM is null)))
559 AND ((recinfo.BENEFICIAL_FORMULA_ID = X_BENEFICIAL_FORMULA_ID)
560 OR ((recinfo.BENEFICIAL_FORMULA_ID is null) AND (X_BENEFICIAL_FORMULA_ID is null)))
561 AND ((recinfo.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID)
562 OR ((recinfo.FLEX_VALUE_SET_ID is null) AND (X_FLEX_VALUE_SET_ID is null)))
563 AND ((recinfo.BENEFICIAL_RULE_VALUE_SET_ID = X_BENEFICIAL_RULE_VALUE_SET_ID)
564 OR ((recinfo.BENEFICIAL_RULE_VALUE_SET_ID is null) AND (X_BENEFICIAL_RULE_VALUE_SET_ID is null)))
565 AND ((recinfo.MULTIPLE_ENTRIES_ALLOWED_FLAG = X_MULTI_ENTRIES_ALLOWED_FLAG)
566 OR ((recinfo.MULTIPLE_ENTRIES_ALLOWED_FLAG is null) AND (X_MULTI_ENTRIES_ALLOWED_FLAG is null)))
567 AND ((recinfo.AUTO_CREATE_ENTRIES_FLAG = X_AUTO_CREATE_ENTRIES_FLAG)
568 OR ((recinfo.AUTO_CREATE_ENTRIES_FLAG is null) AND (X_AUTO_CREATE_ENTRIES_FLAG is null)))
569 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
570 ) then
571 null;
572 else
573 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
574 app_exception.raise_exception;
575 end if;
576
577 for tlinfo in c1 loop
578 if (tlinfo.BASELANG = 'Y') then
579 if ( (tlinfo.ITEM_NAME = X_ITEM_NAME)
580 ) then
581 null;
582 else
583 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
584 app_exception.raise_exception;
585 end if;
586 end if;
587 end loop;
588 return;
589 end LOCK_ROW;
590
591 procedure UPDATE_ROW (
592 X_CAGR_ENTITLEMENT_ITEM_ID in NUMBER,
593 X_BUSINESS_GROUP_ID in NUMBER,
594 X_ELEMENT_TYPE_ID in NUMBER,
595 X_INPUT_VALUE_ID in VARCHAR2,
596 X_COLUMN_TYPE in VARCHAR2,
597 X_COLUMN_SIZE in NUMBER,
598 X_LEGISLATION_CODE in VARCHAR2,
599 X_BENEFICIAL_RULE in VARCHAR2,
600 X_CAGR_API_ID in NUMBER,
601 X_CAGR_API_PARAM_ID in NUMBER,
602 X_CATEGORY_NAME in VARCHAR2,
603 X_UOM in VARCHAR2,
604 X_BENEFICIAL_FORMULA_ID in NUMBER,
605 X_FLEX_VALUE_SET_ID in NUMBER,
606 X_BENEFICIAL_RULE_VALUE_SET_ID in NUMBER,
607 X_OBJECT_VERSION_NUMBER in NUMBER,
608 X_MULTI_ENTRIES_ALLOWED_FLAG in VARCHAR2,
609 X_AUTO_CREATE_ENTRIES_FLAG in VARCHAR2,
610 X_ITEM_NAME in VARCHAR2,
611 X_LAST_UPDATE_DATE in DATE,
612 X_LAST_UPDATED_BY in NUMBER,
613 X_LAST_UPDATE_LOGIN in NUMBER
614 ) is
615 begin
616 update PER_CAGR_ENTITLEMENT_ITEMS set
617 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
618 ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID,
619 INPUT_VALUE_ID = X_INPUT_VALUE_ID,
620 COLUMN_TYPE = X_COLUMN_TYPE,
621 COLUMN_SIZE = X_COLUMN_SIZE,
622 LEGISLATION_CODE = X_LEGISLATION_CODE,
623 BENEFICIAL_RULE = X_BENEFICIAL_RULE,
624 CAGR_API_ID = X_CAGR_API_ID,
625 CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID,
626 CATEGORY_NAME = X_CATEGORY_NAME,
627 UOM = X_UOM,
628 BENEFICIAL_FORMULA_ID = X_BENEFICIAL_FORMULA_ID,
629 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
630 BENEFICIAL_RULE_VALUE_SET_ID = X_BENEFICIAL_RULE_VALUE_SET_ID,
631 MULTIPLE_ENTRIES_ALLOWED_FLAG = X_MULTI_ENTRIES_ALLOWED_FLAG,
632 AUTO_CREATE_ENTRIES_FLAG = X_AUTO_CREATE_ENTRIES_FLAG,
633 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
634 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
635 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
636 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
637 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID;
638
639 if (sql%notfound) then
640 raise no_data_found;
641 end if;
642
643 update PER_CAGR_ENTITLEMENT_ITEMS_TL set
644 ITEM_NAME = X_ITEM_NAME,
645 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
646 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
647 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
648 SOURCE_LANG = userenv('LANG')
649 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID
650 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
651
652 if (sql%notfound) then
653 raise no_data_found;
654 end if;
655 end UPDATE_ROW;
656
657 procedure DELETE_ROW (
658 X_CAGR_ENTITLEMENT_ITEM_ID in NUMBER
659 ) is
660 begin
661 delete from PER_CAGR_ENTITLEMENT_ITEMS_TL
662 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID;
663
664 if (sql%notfound) then
665 raise no_data_found;
666 end if;
667
668 delete from PER_CAGR_ENTITLEMENT_ITEMS
669 where CAGR_ENTITLEMENT_ITEM_ID = X_CAGR_ENTITLEMENT_ITEM_ID;
670
671 if (sql%notfound) then
672 raise no_data_found;
673 end if;
674 end DELETE_ROW;
675
676 procedure ADD_LANGUAGE
677 is
678 begin
679 delete from PER_CAGR_ENTITLEMENT_ITEMS_TL T
680 where not exists
681 (select NULL
682 from PER_CAGR_ENTITLEMENT_ITEMS B
683 where B.CAGR_ENTITLEMENT_ITEM_ID = T.CAGR_ENTITLEMENT_ITEM_ID
684 );
685
686 update PER_CAGR_ENTITLEMENT_ITEMS_TL T set (
687 ITEM_NAME
688 ) = (select
689 B.ITEM_NAME
690 from PER_CAGR_ENTITLEMENT_ITEMS_TL B
691 where B.CAGR_ENTITLEMENT_ITEM_ID = T.CAGR_ENTITLEMENT_ITEM_ID
692 and B.LANGUAGE = T.SOURCE_LANG)
693 where (
694 T.CAGR_ENTITLEMENT_ITEM_ID,
695 T.LANGUAGE
696 ) in (select
697 SUBT.CAGR_ENTITLEMENT_ITEM_ID,
698 SUBT.LANGUAGE
699 from PER_CAGR_ENTITLEMENT_ITEMS_TL SUBB, PER_CAGR_ENTITLEMENT_ITEMS_TL SUBT
700 where SUBB.CAGR_ENTITLEMENT_ITEM_ID = SUBT.CAGR_ENTITLEMENT_ITEM_ID
701 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
702 and (SUBB.ITEM_NAME <> SUBT.ITEM_NAME
703 ));
704
705 insert into PER_CAGR_ENTITLEMENT_ITEMS_TL (
706 CREATED_BY,
707 CREATION_DATE,
708 LAST_UPDATE_LOGIN,
709 LAST_UPDATE_DATE,
710 LAST_UPDATED_BY,
711 CAGR_ENTITLEMENT_ITEM_ID,
712 ITEM_NAME,
713 LANGUAGE,
714 SOURCE_LANG
715 ) select /*+ ORDERED */
716 B.CREATED_BY,
717 B.CREATION_DATE,
718 B.LAST_UPDATE_LOGIN,
719 B.LAST_UPDATE_DATE,
720 B.LAST_UPDATED_BY,
721 B.CAGR_ENTITLEMENT_ITEM_ID,
722 B.ITEM_NAME,
723 L.LANGUAGE_CODE,
724 B.SOURCE_LANG
725 from PER_CAGR_ENTITLEMENT_ITEMS_TL B, FND_LANGUAGES L
726 where L.INSTALLED_FLAG in ('I', 'B')
727 and B.LANGUAGE = userenv('LANG')
728 and not exists
729 (select NULL
730 from PER_CAGR_ENTITLEMENT_ITEMS_TL T
731 where T.CAGR_ENTITLEMENT_ITEM_ID = B.CAGR_ENTITLEMENT_ITEM_ID
732 and T.LANGUAGE = L.LANGUAGE_CODE);
733 end ADD_LANGUAGE;
734
735
736 end PER_CAGR_ENTITLEMENT_ITEMS_PKG;