[Home] [Help]
PACKAGE BODY: APPS.PER_CAGR_API_PARAMETERS_PKG
Source
1 package body PER_CAGR_API_PARAMETERS_PKG as
2 /* $Header: pecaplct.pkb 120.1.12000000.2 2007/05/15 08:52:52 ghshanka ship $ */
3
4
5 procedure KEY_TO_IDS (
6 X_DISPLAY_NAME in VARCHAR2,
7 X_API_NAME in VARCHAR2,
8 X_CAGR_API_PARAM_ID out nocopy NUMBER,
9 X_CAGR_API_ID out nocopy NUMBER
10
11 ) is
12
13 cursor CSR_CAGR_API( X_API_NAME VARCHAR2 ) is
14 select API.CAGR_API_ID
15 from PER_CAGR_APIS API
16 where API.API_NAME = X_API_NAME;
17
18 cursor CSR_CAGR_DISPLAY_NAME ( X_DISPLAY_NAME VARCHAR2, X_API_NAME VARCHAR2 ) is
19 select cap.CAGR_API_PARAM_ID
20 from PER_CAGR_API_PARAMETERS cap,
21 PER_CAGR_APIS api
22 where cap.DISPLAY_NAME = X_DISPLAY_NAME
23 and cap.cagr_api_id = api.cagr_Api_id
24 and api.api_name = X_API_NAME;
25
26 cursor CSR_SEQUENCE is
27 select PER_CAGR_API_PARAMETERS_S.nextval
28 from dual;
29
30 begin
31 open CSR_CAGR_API ( X_API_NAME );
32 fetch CSR_CAGR_API into X_CAGR_API_ID;
33
34 open CSR_CAGR_DISPLAY_NAME ( X_DISPLAY_NAME, X_API_NAME );
35 fetch CSR_CAGR_DISPLAY_NAME into X_CAGR_API_PARAM_ID;
36
37 if (CSR_CAGR_DISPLAY_NAME%notfound) then
38 open CSR_SEQUENCE;
39 fetch CSR_SEQUENCE into X_CAGR_API_PARAM_ID;
40 close CSR_SEQUENCE;
41 end if;
42 close CSR_CAGR_DISPLAY_NAME;
43 end KEY_TO_IDS;
44
45
46 procedure INSERT_ROW (
47 X_ROWID in out nocopy VARCHAR2,
48 X_CAGR_API_PARAM_ID in NUMBER,
49 X_DEFAULT_UOM in VARCHAR2,
50 X_CAGR_API_ID in NUMBER,
51 X_PARAMETER_NAME in VARCHAR2,
52 X_COLUMN_TYPE in VARCHAR2,
53 X_COLUMN_SIZE in NUMBER,
54 X_UOM_PARAMETER in VARCHAR2,
55 X_UOM_LOOKUP in VARCHAR2,
56 X_HIDDEN in VARCHAR2,
57 X_OBJECT_VERSION_NUMBER in NUMBER,
58 X_DISPLAY_NAME in VARCHAR2,
59 X_CREATION_DATE in DATE,
60 X_CREATED_BY in NUMBER,
61 X_LAST_UPDATE_DATE in DATE,
62 X_LAST_UPDATED_BY in NUMBER,
63 X_LAST_UPDATE_LOGIN in NUMBER
64 ) is
65 cursor C is select ROWID from PER_CAGR_API_PARAMETERS
66 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
67 ;
68 begin
69 insert into PER_CAGR_API_PARAMETERS (
70 DEFAULT_UOM,
71 CAGR_API_PARAM_ID,
72 DISPLAY_NAME,
73 CAGR_API_ID,
74 PARAMETER_NAME,
75 COLUMN_TYPE,
76 COLUMN_SIZE,
77 UOM_PARAMETER,
78 UOM_LOOKUP,
79 HIDDEN,
80 OBJECT_VERSION_NUMBER,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_LOGIN
86 ) values (
87 X_DEFAULT_UOM,
88 X_CAGR_API_PARAM_ID,
89 X_DISPLAY_NAME,
90 X_CAGR_API_ID,
91 X_PARAMETER_NAME,
92 X_COLUMN_TYPE,
93 X_COLUMN_SIZE,
94 X_UOM_PARAMETER,
95 X_UOM_LOOKUP,
96 X_HIDDEN,
97 X_OBJECT_VERSION_NUMBER,
98 X_CREATION_DATE,
99 X_CREATED_BY,
100 X_LAST_UPDATE_DATE,
101 X_LAST_UPDATED_BY,
102 X_LAST_UPDATE_LOGIN
103 );
104
105 insert into PER_CAGR_API_PARAMETERS_TL (
106 LAST_UPDATE_LOGIN,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATE_DATE,
110 LAST_UPDATED_BY,
111 CAGR_API_PARAM_ID,
112 DISPLAY_NAME,
113 LANGUAGE,
114 SOURCE_LANG
115 ) select
116 X_LAST_UPDATE_LOGIN,
117 X_CREATED_BY,
118 X_CREATION_DATE,
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATED_BY,
121 X_CAGR_API_PARAM_ID,
122 X_DISPLAY_NAME,
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 PER_CAGR_API_PARAMETERS_TL T
130 where T.CAGR_API_PARAM_ID = X_CAGR_API_PARAM_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 TRANSLATE_ROW (
144 X_DISPLAY_NAME1 in VARCHAR2 default null,
145 X_DISPLAY_NAME in VARCHAR2,
146 X_API_NAME in VARCHAR2 default null,
147 X_OWNER in VARCHAR2
148 ) is
149 X_CAGR_API_PARAM_ID NUMBER;
150 X_CAGR_API_ID NUMBER;
151 --X_API_NAME VARCHAR2(60);
152
153 begin
154
155 KEY_TO_IDS (
156 X_DISPLAY_NAME1,
157 X_API_NAME,
158 X_CAGR_API_PARAM_ID,
159 X_CAGR_API_ID
160 );
161
162
163 update per_cagr_api_parameters_tl set
164 display_name = X_display_NAME,
165 last_update_date = sysdate,
166 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
167 last_update_login = 0,
168 source_lang = userenv('LANG')
169 where cagr_api_param_id = X_CAGR_API_PARAM_ID
170 and userenv('LANG') in (language, source_lang);
171
172 end TRANSLATE_ROW;
173
174 procedure LOAD_ROW (
175 X_CAGR_API_NAME in VARCHAR2,
176 X_PARAMETER_NAME in VARCHAR2,
177 X_DISPLAY_NAME in VARCHAR2,
178 X_OWNER in VARCHAR2,
179 X_OBJECT_VERSION_NUMBER in NUMBER,
180 X_COLUMN_TYPE in VARCHAR2,
181 X_COLUMN_SIZE in NUMBER,
182 X_UOM_PARAMETER in VARCHAR2,
183 X_UOM_LOOKUP in VARCHAR2,
184 X_HIDDEN in VARCHAR2,
185 X_DEFAULT_UOM in VARCHAR2) is
186
187 X_ROW_ID ROWID;
188 user_id number := 0;
189 X_CAGR_API_PARAM_ID NUMBER;
190 X_CAGR_API_ID NUMBER;
191
192 begin
193
194 KEY_TO_IDS (
195 X_DISPLAY_NAME,
196 X_CAGR_API_NAME,
197 X_CAGR_API_PARAM_ID,
198 X_CAGR_API_ID
199 );
200
201 if (X_OWNER = 'SEED') then
202 user_id := 1;
203 else
204 user_id := 0;
205 end if;
206
207 PER_CAGR_API_PARAMETERS_PKG.UPDATE_ROW(
208 X_CAGR_API_PARAM_ID => X_CAGR_API_PARAM_ID,
209 X_DEFAULT_UOM => X_DEFAULT_UOM,
210 X_CAGR_API_ID => X_CAGR_API_ID,
211 X_PARAMETER_NAME => X_PARAMETER_NAME,
212 X_COLUMN_TYPE => X_COLUMN_TYPE,
213 X_COLUMN_SIZE => X_COLUMN_SIZE,
214 X_UOM_PARAMETER => X_UOM_PARAMETER,
215 X_UOM_LOOKUP => X_UOM_LOOKUP,
216 X_HIDDEN => X_HIDDEN,
217 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
218 X_DISPLAY_NAME => X_DISPLAY_NAME,
219 X_LAST_UPDATE_DATE => SYSDATE,
220 X_LAST_UPDATED_BY => USER_ID,
221 X_LAST_UPDATE_LOGIN => 0
222 );
223
224 exception
225 when NO_DATA_FOUND then
226
227 PER_CAGR_API_PARAMETERS_PKG.INSERT_ROW(
228 X_ROWID => X_ROW_ID,
229 X_CAGR_API_PARAM_ID => X_CAGR_API_PARAM_ID,
230 X_DEFAULT_UOM => X_DEFAULT_UOM,
231 X_CAGR_API_ID => X_CAGR_API_ID,
232 X_PARAMETER_NAME => X_PARAMETER_NAME,
233 X_COLUMN_TYPE => X_COLUMN_TYPE,
234 X_COLUMN_SIZE => X_COLUMN_SIZE,
235 X_UOM_PARAMETER => X_UOM_PARAMETER,
236 X_UOM_LOOKUP => X_UOM_LOOKUP,
237 X_HIDDEN => X_HIDDEN,
238 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
239 X_DISPLAY_NAME => X_DISPLAY_NAME,
240 X_CREATION_DATE => SYSDATE,
241 X_CREATED_BY => USER_ID,
242 X_LAST_UPDATE_DATE => SYSDATE,
243 X_LAST_UPDATED_BY => USER_ID,
244 X_LAST_UPDATE_LOGIN => 0
245 );
246
247
248 end LOAD_ROW;
249
250 procedure LOCK_ROW (
251 X_CAGR_API_PARAM_ID in NUMBER,
252 X_DEFAULT_UOM in VARCHAR2,
253 X_CAGR_API_ID in NUMBER,
254 X_PARAMETER_NAME in VARCHAR2,
255 X_COLUMN_TYPE in VARCHAR2,
256 X_COLUMN_SIZE in NUMBER,
257 X_UOM_PARAMETER in VARCHAR2,
258 X_UOM_LOOKUP in VARCHAR2,
259 X_HIDDEN in VARCHAR2,
260 X_OBJECT_VERSION_NUMBER in NUMBER,
261 X_DISPLAY_NAME in VARCHAR2
262 ) is
263 cursor c is select
264 DEFAULT_UOM,
265 CAGR_API_ID,
266 PARAMETER_NAME,
267 COLUMN_TYPE,
268 COLUMN_SIZE,
269 UOM_PARAMETER,
270 UOM_LOOKUP,
271 HIDDEN,
272 OBJECT_VERSION_NUMBER
273 from PER_CAGR_API_PARAMETERS
274 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
275 for update of CAGR_API_PARAM_ID nowait;
276 recinfo c%rowtype;
277
278 cursor c1 is select
279 DISPLAY_NAME,
280 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
281 from PER_CAGR_API_PARAMETERS_TL
282 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
283 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
284 for update of CAGR_API_PARAM_ID nowait;
285 begin
286 open c;
287 fetch c into recinfo;
288 if (c%notfound) then
289 close c;
290 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
291 app_exception.raise_exception;
292 end if;
293 close c;
294 if ( ((recinfo.DEFAULT_UOM = X_DEFAULT_UOM)
295 OR ((recinfo.DEFAULT_UOM is null) AND (X_DEFAULT_UOM is null)))
296 AND (recinfo.CAGR_API_ID = X_CAGR_API_ID)
297 AND ((recinfo.PARAMETER_NAME = X_PARAMETER_NAME)
298 OR ((recinfo.PARAMETER_NAME is null) AND (X_PARAMETER_NAME is null)))
299 AND ((recinfo.COLUMN_TYPE = X_COLUMN_TYPE)
300 OR ((recinfo.COLUMN_TYPE is null) AND (X_COLUMN_TYPE is null)))
301 AND ((recinfo.COLUMN_SIZE = X_COLUMN_SIZE)
302 OR ((recinfo.COLUMN_SIZE is null) AND (X_COLUMN_SIZE is null)))
303 AND ((recinfo.UOM_PARAMETER = X_UOM_PARAMETER)
304 OR ((recinfo.UOM_PARAMETER is null) AND (X_UOM_PARAMETER is null)))
305 AND ((recinfo.UOM_LOOKUP = X_UOM_LOOKUP)
306 OR ((recinfo.UOM_LOOKUP is null) AND (X_UOM_LOOKUP is null)))
307 AND ((recinfo.HIDDEN = X_HIDDEN)
308 OR ((recinfo.HIDDEN is null) AND (X_HIDDEN is null)))
309 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
310 ) then
311 null;
312 else
313 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
314 app_exception.raise_exception;
315 end if;
316
317 for tlinfo in c1 loop
318 if (tlinfo.BASELANG = 'Y') then
319 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
320 ) then
321 null;
322 else
323 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
324 app_exception.raise_exception;
325 end if;
326 end if;
327 end loop;
328 return;
329 end LOCK_ROW;
330
331 procedure UPDATE_ROW (
332 X_CAGR_API_PARAM_ID in NUMBER,
333 X_DEFAULT_UOM in VARCHAR2,
334 X_CAGR_API_ID in NUMBER,
335 X_PARAMETER_NAME in VARCHAR2,
336 X_COLUMN_TYPE in VARCHAR2,
337 X_COLUMN_SIZE in NUMBER,
338 X_UOM_PARAMETER in VARCHAR2,
339 X_UOM_LOOKUP in VARCHAR2,
340 X_HIDDEN in VARCHAR2,
341 X_OBJECT_VERSION_NUMBER in NUMBER,
342 X_DISPLAY_NAME in VARCHAR2,
343 X_LAST_UPDATE_DATE in DATE,
344 X_LAST_UPDATED_BY in NUMBER,
345 X_LAST_UPDATE_LOGIN in NUMBER
346 ) is
347 begin
348 update PER_CAGR_API_PARAMETERS set
349 DEFAULT_UOM = X_DEFAULT_UOM,
350 CAGR_API_ID = X_CAGR_API_ID,
351 PARAMETER_NAME = X_PARAMETER_NAME,
352 DISPLAY_NAME = X_DISPLAY_NAME,
353 COLUMN_TYPE = X_COLUMN_TYPE,
354 COLUMN_SIZE = X_COLUMN_SIZE,
355 UOM_PARAMETER = X_UOM_PARAMETER,
356 UOM_LOOKUP = X_UOM_LOOKUP,
357 HIDDEN = X_HIDDEN,
358 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
359 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
360 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
361 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
362 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID;
363
364 if (sql%notfound) then
365 raise no_data_found;
366 end if;
367
368 update PER_CAGR_API_PARAMETERS_TL set
369 DISPLAY_NAME = X_DISPLAY_NAME,
370 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
371 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
372 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
373 SOURCE_LANG = userenv('LANG')
374 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
375 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
376
377 if (sql%notfound) then
378 raise no_data_found;
379 end if;
380 end UPDATE_ROW;
381
382 procedure DELETE_ROW (
383 X_CAGR_API_PARAM_ID in NUMBER
384 ) is
385 begin
386 delete from PER_CAGR_API_PARAMETERS_TL
387 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID;
388
389 if (sql%notfound) then
390 raise no_data_found;
391 end if;
392
393 delete from PER_CAGR_API_PARAMETERS
394 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID;
395
396 if (sql%notfound) then
397 raise no_data_found;
398 end if;
399 end DELETE_ROW;
400
401 procedure ADD_LANGUAGE
402 is
403 begin
404 delete from PER_CAGR_API_PARAMETERS_TL T
405 where not exists
406 (select NULL
407 from PER_CAGR_API_PARAMETERS B
408 where B.CAGR_API_PARAM_ID = T.CAGR_API_PARAM_ID
409 );
410
411 update PER_CAGR_API_PARAMETERS_TL T set (
412 DISPLAY_NAME
413 ) = (select
414 B.DISPLAY_NAME
415 from PER_CAGR_API_PARAMETERS_TL B
416 where B.CAGR_API_PARAM_ID = T.CAGR_API_PARAM_ID
417 and B.LANGUAGE = T.SOURCE_LANG)
418 where (
422 SUBT.CAGR_API_PARAM_ID,
419 T.CAGR_API_PARAM_ID,
420 T.LANGUAGE
421 ) in (select
423 SUBT.LANGUAGE
424 from PER_CAGR_API_PARAMETERS_TL SUBB, PER_CAGR_API_PARAMETERS_TL SUBT
425 where SUBB.CAGR_API_PARAM_ID = SUBT.CAGR_API_PARAM_ID
426 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
427 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
428 ));
429
430 insert into PER_CAGR_API_PARAMETERS_TL (
431 LAST_UPDATE_LOGIN,
432 CREATED_BY,
433 CREATION_DATE,
434 LAST_UPDATE_DATE,
435 LAST_UPDATED_BY,
436 CAGR_API_PARAM_ID,
437 DISPLAY_NAME,
438 LANGUAGE,
439 SOURCE_LANG
440 ) select /*+ ORDERED */
441 B.LAST_UPDATE_LOGIN,
442 B.CREATED_BY,
443 B.CREATION_DATE,
444 B.LAST_UPDATE_DATE,
445 B.LAST_UPDATED_BY,
446 B.CAGR_API_PARAM_ID,
447 B.DISPLAY_NAME,
448 L.LANGUAGE_CODE,
449 B.SOURCE_LANG
450 from PER_CAGR_API_PARAMETERS_TL B, FND_LANGUAGES L
451 where L.INSTALLED_FLAG in ('I', 'B')
452 and B.LANGUAGE = userenv('LANG')
453 and not exists
454 (select NULL
455 from PER_CAGR_API_PARAMETERS_TL T
456 where T.CAGR_API_PARAM_ID = B.CAGR_API_PARAM_ID
457 and T.LANGUAGE = L.LANGUAGE_CODE);
458 end ADD_LANGUAGE;
459
460 end PER_CAGR_API_PARAMETERS_PKG;