eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PER_CAGR_APIS_PKG

Source


1 package body PER_CAGR_APIS_PKG as
2 /* $Header: peapilct.pkb 120.1 2006/06/20 09:24:40 bshukla noship $ */
3 
4 procedure KEY_TO_IDS (
5   X_API_NAME  in VARCHAR2,
6   X_CAGR_API_ID out nocopy NUMBER
7 
8 ) is
9 
10   cursor CSR_CAGR_API_NAME (
11     X_API_NAME VARCHAR2
12     ) is
13     select API.CAGR_API_ID
14     from PER_CAGR_APIS API
15     where API.API_NAME = X_API_NAME;
16 
17   cursor CSR_SEQUENCE is
18     select PER_CAGR_APIS_S.nextval
19     from   dual;
20 
21 begin
22 
23   open CSR_CAGR_API_NAME (    X_API_NAME );
24   fetch CSR_CAGR_API_NAME into X_CAGR_API_ID;
25   if (CSR_CAGR_API_NAME%notfound) then
26     open CSR_SEQUENCE;
27     fetch CSR_SEQUENCE into X_CAGR_API_ID;
28     close CSR_SEQUENCE;
29   end if;
30   close CSR_CAGR_API_NAME;
31 end KEY_TO_IDS;
32 
33 procedure INSERT_ROW (
34   X_ROWID in out nocopy VARCHAR2,
35   X_CAGR_API_ID in NUMBER,
36   X_OBJECT_VERSION_NUMBER in NUMBER,
37   X_CATEGORY_NAME in VARCHAR2,
38   X_API_NAME in VARCHAR2,
39   X_CREATION_DATE in DATE,
40   X_CREATED_BY in NUMBER,
41   X_LAST_UPDATE_DATE in DATE,
42   X_LAST_UPDATED_BY in NUMBER,
43   X_LAST_UPDATE_LOGIN in NUMBER
44 ) is
45   cursor C is select ROWID from PER_CAGR_APIS
46     where CAGR_API_ID = X_CAGR_API_ID
47     ;
48 begin
49   insert into PER_CAGR_APIS (
50     OBJECT_VERSION_NUMBER,
51     CATEGORY_NAME,
52     API_NAME,
53     CAGR_API_ID,
54     CREATION_DATE,
55     CREATED_BY,
56     LAST_UPDATE_DATE,
57     LAST_UPDATED_BY,
58     LAST_UPDATE_LOGIN
59   ) values (
60     X_OBJECT_VERSION_NUMBER,
61     X_CATEGORY_NAME,
62     X_API_NAME,
63     X_CAGR_API_ID,
64     X_CREATION_DATE,
65     X_CREATED_BY,
66     X_LAST_UPDATE_DATE,
67     X_LAST_UPDATED_BY,
68     X_LAST_UPDATE_LOGIN
69   );
70 
71   insert into PER_CAGR_APIS_TL (
72     LAST_UPDATE_DATE,
73     LAST_UPDATED_BY,
74     CAGR_API_ID,
75     API_NAME,
76     LAST_UPDATE_LOGIN,
77     CREATED_BY,
78     CREATION_DATE,
79     LANGUAGE,
80     SOURCE_LANG
81   ) select
82     X_LAST_UPDATE_DATE,
83     X_LAST_UPDATED_BY,
84     X_CAGR_API_ID,
85     X_API_NAME,
86     X_LAST_UPDATE_LOGIN,
87     X_CREATED_BY,
88     X_CREATION_DATE,
89     L.LANGUAGE_CODE,
90     userenv('LANG')
91   from FND_LANGUAGES L
92   where L.INSTALLED_FLAG in ('I', 'B')
93   and not exists
94     (select NULL
95     from PER_CAGR_APIS_TL T
96     where T.CAGR_API_ID = X_CAGR_API_ID
97     and T.LANGUAGE = L.LANGUAGE_CODE);
98 
99   open c;
100   fetch c into X_ROWID;
101   if (c%notfound) then
102     close c;
103     raise no_data_found;
104   end if;
105   close c;
106 
107 end INSERT_ROW;
108 
109 procedure TRANSLATE_ROW (
110   X_API_NAME1                 in VARCHAR2 default null,
111   X_API_NAME                  in VARCHAR2,
112   X_OWNER                     in VARCHAR2
113    ) is
114 X_CAGR_API_ID NUMBER;
115 
116 begin
117 
118  KEY_TO_IDS (
119     X_API_NAME1,
120     X_CAGR_API_ID
121   );
122 
123 
124   update per_cagr_apis_tl set
125     api_name           = X_API_NAME,
126     last_update_date  = sysdate,
127     last_updated_by   = decode(X_OWNER, 'SEED', 1, 0),
128     last_update_login = 0,
129     source_lang       = userenv('LANG')
130   where cagr_api_id   = X_CAGR_API_ID
131   and userenv('LANG') in (language, source_lang);
132 
133 end TRANSLATE_ROW;
134 
135 
136 procedure LOAD_ROW (
137   X_API_NAME                  in VARCHAR2,
138   X_CATEGORY_NAME 	      in VARCHAR2,
139   X_OWNER                     in VARCHAR2,
140   X_OBJECT_VERSION_NUMBER     in NUMBER) is
141 
142   X_ROW_ID ROWID;
143   user_id number := 0;
144   X_CAGR_API_ID NUMBER;
145 
146 begin
147 
148  KEY_TO_IDS (
149     X_API_NAME,
150     X_CAGR_API_ID
151   );
152 
153 if (X_OWNER = 'SEED') then
154     user_id := 1;
155   else
156     user_id := 0;
157   end if;
158 
159 PER_CAGR_APIS_PKG.UPDATE_ROW (
160   X_CAGR_API_ID => X_CAGR_API_ID,
161   X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
162   X_CATEGORY_NAME => X_CATEGORY_NAME,
163   X_API_NAME => X_API_NAME,
164   X_LAST_UPDATE_DATE => SYSDATE,
165   X_LAST_UPDATED_BY => USER_ID,
166   X_LAST_UPDATE_LOGIN => 0);
167 
168 exception
169   when NO_DATA_FOUND then
170 
171 
172 PER_CAGR_APIS_PKG.INSERT_ROW(
173   X_ROWID => X_ROW_ID,
174   X_CAGR_API_ID => X_CAGR_API_ID,
175   X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
176   X_CATEGORY_NAME => X_CATEGORY_NAME,
177   X_API_NAME => X_API_NAME,
178   X_CREATION_DATE => SYSDATE,
179   X_CREATED_BY => USER_ID,
180   X_LAST_UPDATE_DATE => SYSDATE,
181   X_LAST_UPDATED_BY => USER_ID,
182   X_LAST_UPDATE_LOGIN => 0);
183 
184 end LOAD_ROW;
185 
186 
187 procedure LOCK_ROW (
188   X_CAGR_API_ID in NUMBER,
189   X_OBJECT_VERSION_NUMBER in NUMBER,
190   X_CATEGORY_NAME in VARCHAR2,
191   X_API_NAME in VARCHAR2
192 ) is
193   cursor c is select
194       OBJECT_VERSION_NUMBER,
195       CATEGORY_NAME
196     from PER_CAGR_APIS
197     where CAGR_API_ID = X_CAGR_API_ID
198     for update of CAGR_API_ID nowait;
199   recinfo c%rowtype;
200 
201   cursor c1 is select
202       API_NAME,
203       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
204     from PER_CAGR_APIS_TL
205     where CAGR_API_ID = X_CAGR_API_ID
206     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
207     for update of CAGR_API_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.CATEGORY_NAME = X_CATEGORY_NAME)
219   ) then
220     null;
221   else
222     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
223     app_exception.raise_exception;
224   end if;
225 
226   for tlinfo in c1 loop
227     if (tlinfo.BASELANG = 'Y') then
228       if (    (tlinfo.API_NAME = X_API_NAME)
229       ) then
230         null;
231       else
232         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
233         app_exception.raise_exception;
234       end if;
235     end if;
236   end loop;
237   return;
238 end LOCK_ROW;
239 
240 procedure UPDATE_ROW (
241   X_CAGR_API_ID in NUMBER,
242   X_OBJECT_VERSION_NUMBER in NUMBER,
243   X_CATEGORY_NAME in VARCHAR2,
244   X_API_NAME in VARCHAR2,
245   X_LAST_UPDATE_DATE in DATE,
246   X_LAST_UPDATED_BY in NUMBER,
247   X_LAST_UPDATE_LOGIN in NUMBER
248 ) is
249 begin
250   update PER_CAGR_APIS set
251     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
252     CATEGORY_NAME = X_CATEGORY_NAME,
253     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
254     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
255     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
256   where CAGR_API_ID = X_CAGR_API_ID;
257 
258   if (sql%notfound) then
259     raise no_data_found;
260   end if;
261 
262   update PER_CAGR_APIS_TL set
263     API_NAME = X_API_NAME,
264     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
265     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
266     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
267     SOURCE_LANG = userenv('LANG')
268   where CAGR_API_ID = X_CAGR_API_ID
269   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
270 
271   if (sql%notfound) then
272     raise no_data_found;
273   end if;
274 end UPDATE_ROW;
275 
276 procedure DELETE_ROW (
277   X_CAGR_API_ID in NUMBER
278 ) is
279 begin
280   delete from PER_CAGR_APIS_TL
281   where CAGR_API_ID = X_CAGR_API_ID;
282 
283   if (sql%notfound) then
284     raise no_data_found;
285   end if;
286 
287   delete from PER_CAGR_APIS
288   where CAGR_API_ID = X_CAGR_API_ID;
289 
290   if (sql%notfound) then
291     raise no_data_found;
292   end if;
293 end DELETE_ROW;
294 
295 procedure ADD_LANGUAGE
296 is
297 begin
298   delete from PER_CAGR_APIS_TL T
299   where not exists
300     (select NULL
301     from PER_CAGR_APIS B
302     where B.CAGR_API_ID = T.CAGR_API_ID
303     );
304 
305   update PER_CAGR_APIS_TL T set (
306       API_NAME
307     ) = (select
308       B.API_NAME
309     from PER_CAGR_APIS_TL B
310     where B.CAGR_API_ID = T.CAGR_API_ID
311     and B.LANGUAGE = T.SOURCE_LANG)
312   where (
313       T.CAGR_API_ID,
314       T.LANGUAGE
315   ) in (select
316       SUBT.CAGR_API_ID,
317       SUBT.LANGUAGE
318     from PER_CAGR_APIS_TL SUBB, PER_CAGR_APIS_TL SUBT
319     where SUBB.CAGR_API_ID = SUBT.CAGR_API_ID
320     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
321     and (SUBB.API_NAME <> SUBT.API_NAME
322   ));
323 
324   insert into PER_CAGR_APIS_TL (
325     LAST_UPDATE_DATE,
326     LAST_UPDATED_BY,
327     CAGR_API_ID,
328     API_NAME,
329     LAST_UPDATE_LOGIN,
330     CREATED_BY,
331     CREATION_DATE,
332     LANGUAGE,
333     SOURCE_LANG
334   ) select /*+ ORDERED */
335     B.LAST_UPDATE_DATE,
336     B.LAST_UPDATED_BY,
337     B.CAGR_API_ID,
338     B.API_NAME,
339     B.LAST_UPDATE_LOGIN,
340     B.CREATED_BY,
341     B.CREATION_DATE,
342     L.LANGUAGE_CODE,
343     B.SOURCE_LANG
344   from PER_CAGR_APIS_TL B, FND_LANGUAGES L
345   where L.INSTALLED_FLAG in ('I', 'B')
346   and B.LANGUAGE = userenv('LANG')
347   and not exists
348     (select NULL
349     from PER_CAGR_APIS_TL T
350     where T.CAGR_API_ID = B.CAGR_API_ID
351     and T.LANGUAGE = L.LANGUAGE_CODE);
352 end ADD_LANGUAGE;
353 
354 
355 end PER_CAGR_APIS_PKG;