DBA Data[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;