[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.3 2011/04/28 10:14:35 sidsaxen 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
70 --
71 -- Added the following code as a part of Zero Downtime Patching Project.
72 -- Code Starts Here.
73 --
74
75 BEGIN
76 PER_RIC_PKG.chk_integrity (
77 p_entity_name => 'PER_CAGR_API_PARAMETERS',
78 p_ref_entity => 'PER_CAGR_APIS',
79 p_ref_column_name => 'CAGR_API_ID',
80 p_ref_col_value_number => X_CAGR_API_ID,
81 p_ref_col_value_varchar => NULL,
82 p_ref_col_value_date => NULL,
83 p_ref_type => 'INS');
84
85 END;
86 --
87 -- Code Ends Here.
88 --
89
90 insert into PER_CAGR_API_PARAMETERS (
91 DEFAULT_UOM,
92 CAGR_API_PARAM_ID,
93 DISPLAY_NAME,
94 CAGR_API_ID,
95 PARAMETER_NAME,
96 COLUMN_TYPE,
97 COLUMN_SIZE,
98 UOM_PARAMETER,
99 UOM_LOOKUP,
100 HIDDEN,
101 OBJECT_VERSION_NUMBER,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN
107 ) values (
108 X_DEFAULT_UOM,
109 X_CAGR_API_PARAM_ID,
110 X_DISPLAY_NAME,
111 X_CAGR_API_ID,
112 X_PARAMETER_NAME,
113 X_COLUMN_TYPE,
114 X_COLUMN_SIZE,
115 X_UOM_PARAMETER,
116 X_UOM_LOOKUP,
117 X_HIDDEN,
118 X_OBJECT_VERSION_NUMBER,
119 X_CREATION_DATE,
120 X_CREATED_BY,
121 X_LAST_UPDATE_DATE,
122 X_LAST_UPDATED_BY,
123 X_LAST_UPDATE_LOGIN
124 );
125
126 --
127 -- Added the following code as a part of Zero Downtime Patching Project.
128 -- Code Starts Here.
129 --
130
131 BEGIN
132 PER_RIC_PKG.chk_integrity (
133 p_entity_name => 'PER_CAGR_API_PARAMETERS_TL',
134 p_ref_entity => 'PER_CAGR_API_PARAMETERS',
135 p_ref_column_name => 'CAGR_API_PARAM_ID',
136 p_ref_col_value_number => X_CAGR_API_PARAM_ID,
137 p_ref_col_value_varchar => NULL,
138 p_ref_col_value_date => NULL,
139 p_ref_type => 'INS');
140
141 END;
142 --
143 -- Code Ends Here.
144 --
145
146 insert into PER_CAGR_API_PARAMETERS_TL (
147 LAST_UPDATE_LOGIN,
148 CREATED_BY,
149 CREATION_DATE,
150 LAST_UPDATE_DATE,
151 LAST_UPDATED_BY,
152 CAGR_API_PARAM_ID,
153 DISPLAY_NAME,
154 LANGUAGE,
155 SOURCE_LANG
156 ) select
157 X_LAST_UPDATE_LOGIN,
158 X_CREATED_BY,
159 X_CREATION_DATE,
160 X_LAST_UPDATE_DATE,
161 X_LAST_UPDATED_BY,
162 X_CAGR_API_PARAM_ID,
163 X_DISPLAY_NAME,
164 L.LANGUAGE_CODE,
165 userenv('LANG')
166 from FND_LANGUAGES L
167 where L.INSTALLED_FLAG in ('I', 'B')
168 and not exists
169 (select NULL
170 from PER_CAGR_API_PARAMETERS_TL T
171 where T.CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
172 and T.LANGUAGE = L.LANGUAGE_CODE);
173
174 open c;
175 fetch c into X_ROWID;
176 if (c%notfound) then
177 close c;
178 raise no_data_found;
179 end if;
180 close c;
181
182 end INSERT_ROW;
183
184 procedure TRANSLATE_ROW (
185 X_DISPLAY_NAME1 in VARCHAR2 default null,
186 X_DISPLAY_NAME in VARCHAR2,
187 X_API_NAME in VARCHAR2 default null,
188 X_OWNER in VARCHAR2
189 ) is
190 X_CAGR_API_PARAM_ID NUMBER;
191 X_CAGR_API_ID NUMBER;
192 --X_API_NAME VARCHAR2(60);
193
194 begin
195
196 KEY_TO_IDS (
197 X_DISPLAY_NAME1,
198 X_API_NAME,
199 X_CAGR_API_PARAM_ID,
200 X_CAGR_API_ID
201 );
202
203
204 update per_cagr_api_parameters_tl set
205 display_name = X_display_NAME,
206 last_update_date = sysdate,
207 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
208 last_update_login = 0,
209 source_lang = userenv('LANG')
210 where cagr_api_param_id = X_CAGR_API_PARAM_ID
211 and userenv('LANG') in (language, source_lang);
212
213 end TRANSLATE_ROW;
214
215 procedure LOAD_ROW (
216 X_CAGR_API_NAME in VARCHAR2,
217 X_PARAMETER_NAME in VARCHAR2,
218 X_DISPLAY_NAME in VARCHAR2,
219 X_OWNER in VARCHAR2,
220 X_OBJECT_VERSION_NUMBER in NUMBER,
221 X_COLUMN_TYPE in VARCHAR2,
222 X_COLUMN_SIZE in NUMBER,
223 X_UOM_PARAMETER in VARCHAR2,
224 X_UOM_LOOKUP in VARCHAR2,
225 X_HIDDEN in VARCHAR2,
226 X_DEFAULT_UOM in VARCHAR2) is
227
228 X_ROW_ID ROWID;
229 user_id number := 0;
230 X_CAGR_API_PARAM_ID NUMBER;
231 X_CAGR_API_ID NUMBER;
232
233 begin
234
235 KEY_TO_IDS (
236 X_DISPLAY_NAME,
237 X_CAGR_API_NAME,
238 X_CAGR_API_PARAM_ID,
239 X_CAGR_API_ID
240 );
241
242 if (X_OWNER = 'SEED') then
243 user_id := 1;
244 else
245 user_id := 0;
246 end if;
247
248 PER_CAGR_API_PARAMETERS_PKG.UPDATE_ROW(
249 X_CAGR_API_PARAM_ID => X_CAGR_API_PARAM_ID,
250 X_DEFAULT_UOM => X_DEFAULT_UOM,
251 X_CAGR_API_ID => X_CAGR_API_ID,
252 X_PARAMETER_NAME => X_PARAMETER_NAME,
253 X_COLUMN_TYPE => X_COLUMN_TYPE,
254 X_COLUMN_SIZE => X_COLUMN_SIZE,
255 X_UOM_PARAMETER => X_UOM_PARAMETER,
256 X_UOM_LOOKUP => X_UOM_LOOKUP,
257 X_HIDDEN => X_HIDDEN,
258 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
259 X_DISPLAY_NAME => X_DISPLAY_NAME,
260 X_LAST_UPDATE_DATE => SYSDATE,
261 X_LAST_UPDATED_BY => USER_ID,
262 X_LAST_UPDATE_LOGIN => 0
263 );
264
265 exception
266 when NO_DATA_FOUND then
267
268 PER_CAGR_API_PARAMETERS_PKG.INSERT_ROW(
269 X_ROWID => X_ROW_ID,
270 X_CAGR_API_PARAM_ID => X_CAGR_API_PARAM_ID,
271 X_DEFAULT_UOM => X_DEFAULT_UOM,
272 X_CAGR_API_ID => X_CAGR_API_ID,
273 X_PARAMETER_NAME => X_PARAMETER_NAME,
274 X_COLUMN_TYPE => X_COLUMN_TYPE,
275 X_COLUMN_SIZE => X_COLUMN_SIZE,
276 X_UOM_PARAMETER => X_UOM_PARAMETER,
277 X_UOM_LOOKUP => X_UOM_LOOKUP,
278 X_HIDDEN => X_HIDDEN,
279 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
280 X_DISPLAY_NAME => X_DISPLAY_NAME,
281 X_CREATION_DATE => SYSDATE,
282 X_CREATED_BY => USER_ID,
283 X_LAST_UPDATE_DATE => SYSDATE,
284 X_LAST_UPDATED_BY => USER_ID,
285 X_LAST_UPDATE_LOGIN => 0
286 );
287
288
289 end LOAD_ROW;
290
291 procedure LOCK_ROW (
292 X_CAGR_API_PARAM_ID in NUMBER,
293 X_DEFAULT_UOM in VARCHAR2,
294 X_CAGR_API_ID in NUMBER,
295 X_PARAMETER_NAME in VARCHAR2,
296 X_COLUMN_TYPE in VARCHAR2,
297 X_COLUMN_SIZE in NUMBER,
298 X_UOM_PARAMETER in VARCHAR2,
299 X_UOM_LOOKUP in VARCHAR2,
300 X_HIDDEN in VARCHAR2,
301 X_OBJECT_VERSION_NUMBER in NUMBER,
302 X_DISPLAY_NAME in VARCHAR2
303 ) is
304 cursor c is select
305 DEFAULT_UOM,
306 CAGR_API_ID,
307 PARAMETER_NAME,
308 COLUMN_TYPE,
309 COLUMN_SIZE,
310 UOM_PARAMETER,
311 UOM_LOOKUP,
312 HIDDEN,
313 OBJECT_VERSION_NUMBER
314 from PER_CAGR_API_PARAMETERS
315 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
316 for update of CAGR_API_PARAM_ID nowait;
317 recinfo c%rowtype;
318
319 cursor c1 is select
320 DISPLAY_NAME,
321 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
322 from PER_CAGR_API_PARAMETERS_TL
323 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
324 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
325 for update of CAGR_API_PARAM_ID nowait;
326 begin
327 open c;
328 fetch c into recinfo;
329 if (c%notfound) then
330 close c;
331 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
332 app_exception.raise_exception;
333 end if;
334 close c;
335 if ( ((recinfo.DEFAULT_UOM = X_DEFAULT_UOM)
336 OR ((recinfo.DEFAULT_UOM is null) AND (X_DEFAULT_UOM is null)))
337 AND (recinfo.CAGR_API_ID = X_CAGR_API_ID)
338 AND ((recinfo.PARAMETER_NAME = X_PARAMETER_NAME)
339 OR ((recinfo.PARAMETER_NAME is null) AND (X_PARAMETER_NAME is null)))
340 AND ((recinfo.COLUMN_TYPE = X_COLUMN_TYPE)
341 OR ((recinfo.COLUMN_TYPE is null) AND (X_COLUMN_TYPE is null)))
342 AND ((recinfo.COLUMN_SIZE = X_COLUMN_SIZE)
343 OR ((recinfo.COLUMN_SIZE is null) AND (X_COLUMN_SIZE is null)))
344 AND ((recinfo.UOM_PARAMETER = X_UOM_PARAMETER)
345 OR ((recinfo.UOM_PARAMETER is null) AND (X_UOM_PARAMETER is null)))
346 AND ((recinfo.UOM_LOOKUP = X_UOM_LOOKUP)
347 OR ((recinfo.UOM_LOOKUP is null) AND (X_UOM_LOOKUP is null)))
348 AND ((recinfo.HIDDEN = X_HIDDEN)
349 OR ((recinfo.HIDDEN is null) AND (X_HIDDEN is null)))
350 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
351 ) then
352 null;
353 else
354 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
355 app_exception.raise_exception;
356 end if;
357
358 for tlinfo in c1 loop
359 if (tlinfo.BASELANG = 'Y') then
360 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
361 ) then
362 null;
363 else
364 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
365 app_exception.raise_exception;
366 end if;
367 end if;
368 end loop;
369 return;
370 end LOCK_ROW;
371
372 procedure UPDATE_ROW (
373 X_CAGR_API_PARAM_ID in NUMBER,
374 X_DEFAULT_UOM in VARCHAR2,
375 X_CAGR_API_ID in NUMBER,
376 X_PARAMETER_NAME in VARCHAR2,
377 X_COLUMN_TYPE in VARCHAR2,
378 X_COLUMN_SIZE in NUMBER,
379 X_UOM_PARAMETER in VARCHAR2,
380 X_UOM_LOOKUP in VARCHAR2,
381 X_HIDDEN in VARCHAR2,
382 X_OBJECT_VERSION_NUMBER in NUMBER,
383 X_DISPLAY_NAME in VARCHAR2,
384 X_LAST_UPDATE_DATE in DATE,
385 X_LAST_UPDATED_BY in NUMBER,
386 X_LAST_UPDATE_LOGIN in NUMBER
387 ) is
388 begin
389 update PER_CAGR_API_PARAMETERS set
390 DEFAULT_UOM = X_DEFAULT_UOM,
391 CAGR_API_ID = X_CAGR_API_ID,
392 PARAMETER_NAME = X_PARAMETER_NAME,
393 DISPLAY_NAME = X_DISPLAY_NAME,
394 COLUMN_TYPE = X_COLUMN_TYPE,
395 COLUMN_SIZE = X_COLUMN_SIZE,
396 UOM_PARAMETER = X_UOM_PARAMETER,
397 UOM_LOOKUP = X_UOM_LOOKUP,
398 HIDDEN = X_HIDDEN,
402 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
399 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
400 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
401 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
403 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID;
404
405 if (sql%notfound) then
406 raise no_data_found;
407 end if;
408
409 update PER_CAGR_API_PARAMETERS_TL set
410 DISPLAY_NAME = X_DISPLAY_NAME,
411 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
412 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
413 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
414 SOURCE_LANG = userenv('LANG')
415 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID
416 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
417
418 if (sql%notfound) then
419 raise no_data_found;
420 end if;
421 end UPDATE_ROW;
422
423 procedure DELETE_ROW (
424 X_CAGR_API_PARAM_ID in NUMBER
425 ) is
426 begin
427 delete from PER_CAGR_API_PARAMETERS_TL
428 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID;
429
430 if (sql%notfound) then
431 raise no_data_found;
432 end if;
433 --
434 -- Added the following code as a part of Zero Downtime Patching Project.
435 -- Code Starts Here.
436 --
437
438 BEGIN
439 PER_RIC_PKG.chk_integrity (
440 p_entity_name => 'PER_CAGR_API_PARAMETERS',
441 p_ref_entity => 'PER_CAGR_API_PARAMETERS_TL',
442 p_ref_column_name => 'CAGR_API_PARAM_ID',
443 p_ref_col_value_number => X_CAGR_API_PARAM_ID,
444 p_ref_col_value_varchar => NULL,
445 p_ref_col_value_date => NULL,
446 p_ref_type => 'DEL');
447
448 END;
449 --
450 -- Code Ends Here.
451 --
452 delete from PER_CAGR_API_PARAMETERS
453 where CAGR_API_PARAM_ID = X_CAGR_API_PARAM_ID;
454
455 if (sql%notfound) then
456 raise no_data_found;
457 end if;
458 end DELETE_ROW;
459
460 procedure ADD_LANGUAGE
461 is
462 begin
463 delete from PER_CAGR_API_PARAMETERS_TL T
464 where not exists
465 (select NULL
466 from PER_CAGR_API_PARAMETERS B
467 where B.CAGR_API_PARAM_ID = T.CAGR_API_PARAM_ID
468 );
469
470 update PER_CAGR_API_PARAMETERS_TL T set (
471 DISPLAY_NAME
472 ) = (select
473 B.DISPLAY_NAME
474 from PER_CAGR_API_PARAMETERS_TL B
475 where B.CAGR_API_PARAM_ID = T.CAGR_API_PARAM_ID
476 and B.LANGUAGE = T.SOURCE_LANG)
477 where (
478 T.CAGR_API_PARAM_ID,
479 T.LANGUAGE
480 ) in (select
481 SUBT.CAGR_API_PARAM_ID,
482 SUBT.LANGUAGE
483 from PER_CAGR_API_PARAMETERS_TL SUBB, PER_CAGR_API_PARAMETERS_TL SUBT
484 where SUBB.CAGR_API_PARAM_ID = SUBT.CAGR_API_PARAM_ID
485 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
486 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
487 ));
488
489 insert into PER_CAGR_API_PARAMETERS_TL (
490 LAST_UPDATE_LOGIN,
491 CREATED_BY,
492 CREATION_DATE,
493 LAST_UPDATE_DATE,
494 LAST_UPDATED_BY,
495 CAGR_API_PARAM_ID,
496 DISPLAY_NAME,
497 LANGUAGE,
498 SOURCE_LANG
499 ) select /*+ ORDERED */
500 B.LAST_UPDATE_LOGIN,
501 B.CREATED_BY,
502 B.CREATION_DATE,
503 B.LAST_UPDATE_DATE,
504 B.LAST_UPDATED_BY,
505 B.CAGR_API_PARAM_ID,
506 B.DISPLAY_NAME,
507 L.LANGUAGE_CODE,
508 B.SOURCE_LANG
509 from PER_CAGR_API_PARAMETERS_TL B, FND_LANGUAGES L
510 where L.INSTALLED_FLAG in ('I', 'B')
511 and B.LANGUAGE = userenv('LANG')
512 and not exists
513 (select NULL
514 from PER_CAGR_API_PARAMETERS_TL T
515 where T.CAGR_API_PARAM_ID = B.CAGR_API_PARAM_ID
516 and T.LANGUAGE = L.LANGUAGE_CODE);
517 end ADD_LANGUAGE;
518
519 end PER_CAGR_API_PARAMETERS_PKG;