[Home] [Help]
PACKAGE BODY: APPS.PER_CAGR_APIS_PKG
Source
1 package body PER_CAGR_APIS_PKG as
2 /* $Header: peapilct.pkb 120.2 2011/04/28 10:18:02 sidsaxen ship $ */
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 --
72 -- Added the following code as a part of Zero Downtime Patching Project.
73 -- Code Starts Here.
74 --
75
76 BEGIN
77 PER_RIC_PKG.chk_integrity (
78 p_entity_name => 'PER_CAGR_APIS_TL',
79 p_ref_entity => 'PER_CAGR_APIS',
80 p_ref_column_name => 'CAGR_API_ID',
81 p_ref_col_value_number => X_CAGR_API_ID,
82 p_ref_col_value_varchar => NULL,
83 p_ref_col_value_date => NULL,
84 p_ref_type => 'INS');
85
86 END;
87 --
88 -- Code Ends Here.
89 --
90
91 insert into PER_CAGR_APIS_TL (
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 CAGR_API_ID,
95 API_NAME,
96 LAST_UPDATE_LOGIN,
97 CREATED_BY,
98 CREATION_DATE,
99 LANGUAGE,
100 SOURCE_LANG
101 ) select
102 X_LAST_UPDATE_DATE,
103 X_LAST_UPDATED_BY,
104 X_CAGR_API_ID,
105 X_API_NAME,
106 X_LAST_UPDATE_LOGIN,
107 X_CREATED_BY,
108 X_CREATION_DATE,
109 L.LANGUAGE_CODE,
110 userenv('LANG')
111 from FND_LANGUAGES L
112 where L.INSTALLED_FLAG in ('I', 'B')
113 and not exists
114 (select NULL
115 from PER_CAGR_APIS_TL T
116 where T.CAGR_API_ID = X_CAGR_API_ID
117 and T.LANGUAGE = L.LANGUAGE_CODE);
118
119 open c;
120 fetch c into X_ROWID;
121 if (c%notfound) then
122 close c;
123 raise no_data_found;
124 end if;
125 close c;
126
127 end INSERT_ROW;
128
129 procedure TRANSLATE_ROW (
130 X_API_NAME1 in VARCHAR2 default null,
131 X_API_NAME in VARCHAR2,
132 X_OWNER in VARCHAR2
133 ) is
134 X_CAGR_API_ID NUMBER;
135
136 begin
137
138 KEY_TO_IDS (
139 X_API_NAME1,
140 X_CAGR_API_ID
141 );
142
143
144 update per_cagr_apis_tl set
145 api_name = X_API_NAME,
146 last_update_date = sysdate,
147 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
148 last_update_login = 0,
149 source_lang = userenv('LANG')
150 where cagr_api_id = X_CAGR_API_ID
151 and userenv('LANG') in (language, source_lang);
152
153 end TRANSLATE_ROW;
154
155
156 procedure LOAD_ROW (
157 X_API_NAME in VARCHAR2,
158 X_CATEGORY_NAME in VARCHAR2,
159 X_OWNER in VARCHAR2,
160 X_OBJECT_VERSION_NUMBER in NUMBER) is
161
162 X_ROW_ID ROWID;
163 user_id number := 0;
164 X_CAGR_API_ID NUMBER;
165
166 begin
167
168 KEY_TO_IDS (
169 X_API_NAME,
170 X_CAGR_API_ID
171 );
172
173 if (X_OWNER = 'SEED') then
174 user_id := 1;
175 else
176 user_id := 0;
177 end if;
178
179 PER_CAGR_APIS_PKG.UPDATE_ROW (
180 X_CAGR_API_ID => X_CAGR_API_ID,
181 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
182 X_CATEGORY_NAME => X_CATEGORY_NAME,
183 X_API_NAME => X_API_NAME,
184 X_LAST_UPDATE_DATE => SYSDATE,
185 X_LAST_UPDATED_BY => USER_ID,
186 X_LAST_UPDATE_LOGIN => 0);
187
188 exception
189 when NO_DATA_FOUND then
190
191
192 PER_CAGR_APIS_PKG.INSERT_ROW(
193 X_ROWID => X_ROW_ID,
194 X_CAGR_API_ID => X_CAGR_API_ID,
195 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
196 X_CATEGORY_NAME => X_CATEGORY_NAME,
197 X_API_NAME => X_API_NAME,
198 X_CREATION_DATE => SYSDATE,
199 X_CREATED_BY => USER_ID,
200 X_LAST_UPDATE_DATE => SYSDATE,
201 X_LAST_UPDATED_BY => USER_ID,
202 X_LAST_UPDATE_LOGIN => 0);
203
204 end LOAD_ROW;
205
206
207 procedure LOCK_ROW (
208 X_CAGR_API_ID in NUMBER,
209 X_OBJECT_VERSION_NUMBER in NUMBER,
210 X_CATEGORY_NAME in VARCHAR2,
211 X_API_NAME in VARCHAR2
212 ) is
213 cursor c is select
214 OBJECT_VERSION_NUMBER,
215 CATEGORY_NAME
216 from PER_CAGR_APIS
217 where CAGR_API_ID = X_CAGR_API_ID
218 for update of CAGR_API_ID nowait;
219 recinfo c%rowtype;
220
221 cursor c1 is select
222 API_NAME,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from PER_CAGR_APIS_TL
225 where CAGR_API_ID = X_CAGR_API_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of CAGR_API_ID nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
238 AND (recinfo.CATEGORY_NAME = X_CATEGORY_NAME)
239 ) then
240 null;
241 else
242 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
243 app_exception.raise_exception;
244 end if;
245
246 for tlinfo in c1 loop
247 if (tlinfo.BASELANG = 'Y') then
248 if ( (tlinfo.API_NAME = X_API_NAME)
249 ) then
250 null;
251 else
252 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
253 app_exception.raise_exception;
254 end if;
255 end if;
256 end loop;
257 return;
258 end LOCK_ROW;
259
260 procedure UPDATE_ROW (
261 X_CAGR_API_ID in NUMBER,
262 X_OBJECT_VERSION_NUMBER in NUMBER,
263 X_CATEGORY_NAME in VARCHAR2,
264 X_API_NAME in VARCHAR2,
265 X_LAST_UPDATE_DATE in DATE,
266 X_LAST_UPDATED_BY in NUMBER,
267 X_LAST_UPDATE_LOGIN in NUMBER
268 ) is
269 begin
270 update PER_CAGR_APIS set
271 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
272 CATEGORY_NAME = X_CATEGORY_NAME,
273 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
274 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
275 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
276 where CAGR_API_ID = X_CAGR_API_ID;
277
278 if (sql%notfound) then
279 raise no_data_found;
280 end if;
281
282 update PER_CAGR_APIS_TL set
283 API_NAME = X_API_NAME,
284 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
285 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
286 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
287 SOURCE_LANG = userenv('LANG')
288 where CAGR_API_ID = X_CAGR_API_ID
289 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
290
291 if (sql%notfound) then
292 raise no_data_found;
293 end if;
294 end UPDATE_ROW;
295
296 procedure DELETE_ROW (
297 X_CAGR_API_ID in NUMBER
298 ) is
299 begin
300 delete from PER_CAGR_APIS_TL
301 where CAGR_API_ID = X_CAGR_API_ID;
302
303 if (sql%notfound) then
304 raise no_data_found;
305 end if;
306 --
307 -- Added the following code as a part of Zero Downtime Patching Project.
308 -- Code Starts Here.
309 --
310
311 BEGIN
312 PER_RIC_PKG.chk_integrity (
313 p_entity_name => 'PER_CAGR_APIS',
314 p_ref_entity => 'PER_CAGR_APIS_TL',
315 p_ref_column_name => 'CAGR_API_ID',
316 p_ref_col_value_number => X_CAGR_API_ID,
317 p_ref_col_value_varchar => NULL,
318 p_ref_col_value_date => NULL,
319 p_ref_type => 'DEL');
320
321 END;
322 --
323 -- Code Ends Here.
324 --
325 delete from PER_CAGR_APIS
326 where CAGR_API_ID = X_CAGR_API_ID;
327
328 if (sql%notfound) then
329 raise no_data_found;
330 end if;
331 end DELETE_ROW;
332
333 procedure ADD_LANGUAGE
334 is
335 begin
336 delete from PER_CAGR_APIS_TL T
337 where not exists
338 (select NULL
339 from PER_CAGR_APIS B
340 where B.CAGR_API_ID = T.CAGR_API_ID
341 );
342
343 update PER_CAGR_APIS_TL T set (
344 API_NAME
345 ) = (select
346 B.API_NAME
347 from PER_CAGR_APIS_TL B
348 where B.CAGR_API_ID = T.CAGR_API_ID
349 and B.LANGUAGE = T.SOURCE_LANG)
350 where (
351 T.CAGR_API_ID,
352 T.LANGUAGE
353 ) in (select
354 SUBT.CAGR_API_ID,
355 SUBT.LANGUAGE
356 from PER_CAGR_APIS_TL SUBB, PER_CAGR_APIS_TL SUBT
357 where SUBB.CAGR_API_ID = SUBT.CAGR_API_ID
358 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
359 and (SUBB.API_NAME <> SUBT.API_NAME
360 ));
361
362 insert into PER_CAGR_APIS_TL (
363 LAST_UPDATE_DATE,
364 LAST_UPDATED_BY,
365 CAGR_API_ID,
366 API_NAME,
367 LAST_UPDATE_LOGIN,
368 CREATED_BY,
369 CREATION_DATE,
370 LANGUAGE,
371 SOURCE_LANG
372 ) select /*+ ORDERED */
373 B.LAST_UPDATE_DATE,
374 B.LAST_UPDATED_BY,
375 B.CAGR_API_ID,
376 B.API_NAME,
377 B.LAST_UPDATE_LOGIN,
378 B.CREATED_BY,
379 B.CREATION_DATE,
380 L.LANGUAGE_CODE,
381 B.SOURCE_LANG
382 from PER_CAGR_APIS_TL B, FND_LANGUAGES L
383 where L.INSTALLED_FLAG in ('I', 'B')
384 and B.LANGUAGE = userenv('LANG')
385 and not exists
386 (select NULL
387 from PER_CAGR_APIS_TL T
388 where T.CAGR_API_ID = B.CAGR_API_ID
389 and T.LANGUAGE = L.LANGUAGE_CODE);
390 end ADD_LANGUAGE;
391
392
393 end PER_CAGR_APIS_PKG;