[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PS_STRATS_B_PKG
Source
1 PACKAGE BODY AMS_IBA_PS_STRATS_B_PKG as
2 /* $Header: amststrb.pls 120.1 2006/09/21 07:38:16 mayjain noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_IBA_PS_STRATS_B_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_IBA_PS_STRATS_B_PKG';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amststrb.pls';
18
19 ----------------------------------------------------------
20 ---- MEDIA ----
21 ----------------------------------------------------------
22
23 -- ========================================================
24 --
25 -- NAME
26 -- createInsertBody
27 --
28 -- PURPOSE
29 --
30 -- NOTES
31 --
32 -- HISTORY
33 --
34 -- ========================================================
35 PROCEDURE Insert_Row(
36 p_created_by NUMBER,
37 p_creation_date DATE,
38 p_last_updated_by NUMBER,
39 p_last_update_date DATE,
40 p_last_update_login NUMBER,
41 px_object_version_number IN OUT NOCOPY NUMBER,
42 px_strategy_id IN OUT NOCOPY NUMBER,
43 p_max_returned NUMBER,
44 p_strategy_type VARCHAR2,
45 p_content_type VARCHAR2,
46 p_strategy_ref_code VARCHAR2,
47 p_selector_class VARCHAR2,
48 p_strategy_name IN VARCHAR2,
49 p_strategy_description IN VARCHAR2)
50
51 IS
52 x_rowid VARCHAR2(30);
53
54 BEGIN
55
56 px_object_version_number := 1;
57
58 INSERT INTO AMS_IBA_PS_STRATS_B(
59 created_by,
60 creation_date,
61 last_updated_by,
62 last_update_date,
63 last_update_login,
64 object_version_number,
65 strategy_id,
66 max_returned,
67 strategy_type,
68 content_type,
69 strategy_ref_code,
70 selector_class
71 ) VALUES (
72 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
73 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
74 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
75 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
76 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
77 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
78 DECODE( px_strategy_id, FND_API.g_miss_num, NULL, px_strategy_id),
79 DECODE( p_max_returned, FND_API.g_miss_num, NULL, p_max_returned),
80 DECODE( p_strategy_type, FND_API.g_miss_char, NULL, p_strategy_type),
81 DECODE( p_content_type, FND_API.g_miss_char, NULL, p_content_type),
82 DECODE( p_strategy_ref_code, FND_API.g_miss_char, NULL, p_strategy_ref_code),
83 DECODE( p_selector_class, FND_API.g_miss_char, NULL, p_selector_class));
84
85 INSERT INTO AMS_IBA_PS_STRATS_TL (
86 created_by,
87 creation_date,
88 last_updated_by,
89 last_update_date,
90 last_update_login,
91 object_version_number,
92 strategy_id,
93 strategy_name,
94 strategy_description,
95 language,
96 source_lang
97 ) SELECT
98 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
99 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
100 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
101 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
102 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
103 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
104 DECODE( px_strategy_id, FND_API.g_miss_num, NULL, px_strategy_id),
105 DECODE( p_strategy_name, FND_API.G_MISS_CHAR, NULL, p_strategy_name),
106 DECODE( p_strategy_description, FND_API.G_MISS_CHAR, NULL, p_strategy_description),
107 l.language_code,
108 USERENV('LANG')
109 FROM fnd_languages l
110 WHERE l.installed_flag IN ('I', 'B')
111 AND NOT EXISTS
112 (SELECT null
113 FROM ams_iba_ps_strats_tl t
114 WHERE t.strategy_id = DECODE( px_strategy_id, FND_API.g_miss_num, NULL, px_strategy_id)
115 AND t.language = l.language_code);
116
117 END Insert_Row;
118
119 ----------------------------------------------------------
120 ---- MEDIA ----
121 ----------------------------------------------------------
122
123 -- ========================================================
124 --
125 -- NAME
126 -- createUpdateBody
127 --
128 -- PURPOSE
129 --
130 -- NOTES
131 --
132 -- HISTORY
133 --
134 -- ========================================================
135 PROCEDURE Update_Row(
136 p_created_by NUMBER,
137 p_creation_date DATE,
138 p_last_updated_by NUMBER,
139 p_last_update_date DATE,
140 p_last_update_login NUMBER,
141 p_object_version_number NUMBER,
142 p_strategy_id NUMBER,
143 p_max_returned NUMBER,
144 p_strategy_type VARCHAR2,
145 p_content_type VARCHAR2,
146 p_strategy_ref_code VARCHAR2,
147 p_selector_class VARCHAR2,
148 p_strategy_name IN VARCHAR2,
149 p_strategy_description IN VARCHAR2)
150
151 IS
152 BEGIN
153 Update AMS_IBA_PS_STRATS_B
154 SET
155 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
156 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
157 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
158 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
159 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
160 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
161 -- strategy_id = DECODE( p_strategy_id, FND_API.g_miss_num, strategy_id, p_strategy_id),
162 max_returned = DECODE( p_max_returned, FND_API.g_miss_num, max_returned, p_max_returned),
163 strategy_type = DECODE( p_strategy_type, FND_API.g_miss_char, strategy_type, p_strategy_type),
164 content_type = DECODE( p_content_type, FND_API.g_miss_char, content_type, p_content_type),
165 strategy_ref_code = DECODE( p_strategy_ref_code, FND_API.g_miss_char, strategy_ref_code, p_strategy_ref_code),
166 selector_class = DECODE( p_selector_class, FND_API.g_miss_char, selector_class, p_selector_class)
167 WHERE STRATEGY_ID = p_STRATEGY_ID;
168 -- AND object_version_number = p_object_version_number;
169
170 IF (SQL%NOTFOUND) THEN
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173
174 UPDATE ams_iba_ps_strats_tl SET
175 strategy_name = decode( p_strategy_NAME, FND_API.G_MISS_CHAR, strategy_NAME, p_strategy_NAME),
176 strategy_description = decode( p_strategy_DESCRIPTION, FND_API.G_MISS_CHAR, STRATEGY_DESCRIPTION, p_strategy_DESCRIPTION),
177 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
178 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
179 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
180 source_lang = USERENV('LANG')
181 WHERE strategy_id = p_strategy_id
182 AND USERENV('LANG') IN (language, source_lang);
183
184 IF (SQL%NOTFOUND) THEN
185 RAISE NO_DATA_FOUND;
186 END IF;
187
188 END Update_Row;
189
190 ----------------------------------------------------------
191 ---- MEDIA ----
192 ----------------------------------------------------------
193
194 -- =======================================================
195 --
196 -- NAME
197 -- createDeleteBody
198 --
199 -- PURPOSE
200 --
201 -- NOTES
202 --
203 -- HISTORY
204 --
205 -- ========================================================
206 PROCEDURE Delete_Row(
207 p_STRATEGY_ID NUMBER)
208 IS
209 BEGIN
210 DELETE FROM AMS_IBA_PS_STRATS_B
211 WHERE STRATEGY_ID = p_STRATEGY_ID;
212 If (SQL%NOTFOUND) then
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 End If;
215
216 DELETE FROM AMS_IBA_PS_STRATS_TL
217 WHERE STRATEGY_ID = p_STRATEGY_ID;
218 If (SQL%NOTFOUND) then
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 End If;
221
222 END Delete_Row ;
223
224 procedure ADD_LANGUAGE
225 is
226 begin
227 delete from AMS_IBA_PS_STRATS_TL T
228 where not exists
229 (select NULL
230 from AMS_IBA_PS_STRATS_B B
231 where B.STRATEGY_ID = T.STRATEGY_ID
232 );
233
234 update AMS_IBA_PS_STRATS_TL T set (
235 STRATEGY_NAME,
236 STRATEGY_DESCRIPTION
237 ) = (select
238 B.STRATEGY_NAME,
239 B.STRATEGY_DESCRIPTION
240 from AMS_IBA_PS_STRATS_TL B
241 where B.STRATEGY_ID = T.STRATEGY_ID
242 and B.LANGUAGE = T.SOURCE_LANG)
243 where (
244 T.STRATEGY_ID,
245 T.LANGUAGE
246 ) in (select
247 SUBT.STRATEGY_ID,
248 SUBT.LANGUAGE
249 from AMS_IBA_PS_STRATS_TL SUBB, AMS_IBA_PS_STRATS_TL SUBT
250 where SUBB.STRATEGY_ID = SUBT.STRATEGY_ID
251 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
252 and (SUBB.STRATEGY_NAME <> SUBT.STRATEGY_NAME
253 or SUBB.STRATEGY_DESCRIPTION <> SUBT.STRATEGY_DESCRIPTION
254 or (SUBB.STRATEGY_DESCRIPTION is null and SUBT.STRATEGY_DESCRIPTION is not null)
255 or (SUBB.STRATEGY_DESCRIPTION is not null and SUBT.STRATEGY_DESCRIPTION is null)
256 ));
257
258 insert into AMS_IBA_PS_STRATS_TL (
259 LAST_UPDATE_DATE,
260 CREATION_DATE,
261 LAST_UPDATED_BY,
262 CREATED_BY,
263 STRATEGY_ID,
264 STRATEGY_NAME,
265 STRATEGY_DESCRIPTION,
266 LAST_UPDATE_LOGIN,
267 OBJECT_VERSION_NUMBER,
268 LANGUAGE,
269 SOURCE_LANG
270 ) select
271 B.LAST_UPDATE_DATE,
272 B.CREATION_DATE,
273 B.LAST_UPDATED_BY,
274 B.CREATED_BY,
275 B.STRATEGY_ID,
276 B.STRATEGY_NAME,
277 B.STRATEGY_DESCRIPTION,
278 B.LAST_UPDATE_LOGIN,
279 B.OBJECT_VERSION_NUMBER,
280 L.LANGUAGE_CODE,
281 B.SOURCE_LANG
282 from AMS_IBA_PS_STRATS_TL B, FND_LANGUAGES L
283 where L.INSTALLED_FLAG in ('I', 'B')
284 and B.LANGUAGE = userenv('LANG')
285 and not exists
286 (select NULL
287 from AMS_IBA_PS_STRATS_TL T
288 where T.STRATEGY_ID = B.STRATEGY_ID
289 and T.LANGUAGE = L.LANGUAGE_CODE);
290 end ADD_LANGUAGE;
291
292 --------------------------------------------------
293 ---- MEDIA ----
294 --------------------------------------------------
295
296 -- =======================================================
297 --
298 -- NAME
299 -- createLockBody
300 --
301 -- PURPOSE
302 --
303 -- NOTES
304 --
305 -- HISTORY
306 --
307 -- ========================================================
308 PROCEDURE Lock_Row(
309 p_created_by NUMBER,
310 p_creation_date DATE,
311 p_last_updated_by NUMBER,
312 p_last_update_date DATE,
313 p_last_update_login NUMBER,
314 p_object_version_number NUMBER,
315 p_strategy_id NUMBER,
316 p_max_returned NUMBER,
317 p_strategy_type VARCHAR2,
318 p_content_type VARCHAR2,
319 p_strategy_ref_code VARCHAR2,
320 p_selector_class VARCHAR2)
321
322 IS
323 CURSOR C IS
324 SELECT *
325 FROM AMS_IBA_PS_STRATS_B
326 WHERE STRATEGY_ID = p_STRATEGY_ID
327 FOR UPDATE of STRATEGY_ID NOWAIT;
328 Recinfo C%ROWTYPE;
329 BEGIN
330 OPEN c;
331 FETCH c INTO Recinfo;
332 If (c%NOTFOUND) then
333 CLOSE c;
334 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
335 APP_EXCEPTION.RAISE_EXCEPTION;
336 END IF;
337 CLOSE C;
338 IF (
339 ( Recinfo.created_by = p_created_by)
340 AND ( ( Recinfo.creation_date = p_creation_date)
341 OR ( ( Recinfo.creation_date IS NULL )
342 AND ( p_creation_date IS NULL )))
343 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
344 OR ( ( Recinfo.last_updated_by IS NULL )
345 AND ( p_last_updated_by IS NULL )))
346 AND ( ( Recinfo.last_update_date = p_last_update_date)
347 OR ( ( Recinfo.last_update_date IS NULL )
348 AND ( p_last_update_date IS NULL )))
349 AND ( ( Recinfo.last_update_login = p_last_update_login)
350 OR ( ( Recinfo.last_update_login IS NULL )
351 AND ( p_last_update_login IS NULL )))
352 AND ( ( Recinfo.object_version_number = p_object_version_number)
353 OR ( ( Recinfo.object_version_number IS NULL )
354 AND ( p_object_version_number IS NULL )))
355 AND ( ( Recinfo.strategy_id = p_strategy_id)
356 OR ( ( Recinfo.strategy_id IS NULL )
357 AND ( p_strategy_id IS NULL )))
358 AND ( ( Recinfo.max_returned = p_max_returned)
359 OR ( ( Recinfo.max_returned IS NULL )
360 AND ( p_max_returned IS NULL )))
361 AND ( ( Recinfo.strategy_type = p_strategy_type)
362 OR ( ( Recinfo.strategy_type IS NULL )
363 AND ( p_strategy_type IS NULL )))
364 AND ( ( Recinfo.content_type = p_content_type)
365 OR ( ( Recinfo.content_type IS NULL )
366 AND ( p_content_type IS NULL )))
367 AND ( ( Recinfo.strategy_ref_code = p_strategy_ref_code)
368 OR ( ( Recinfo.strategy_ref_code IS NULL )
369 AND ( p_strategy_ref_code IS NULL )))
370 AND ( ( Recinfo.selector_class = p_selector_class)
371 OR ( ( Recinfo.selector_class IS NULL )
372 AND ( p_selector_class IS NULL )))
373 ) THEN
374 RETURN;
375 ELSE
376 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
377 APP_EXCEPTION.RAISE_EXCEPTION;
378 END IF;
379 END Lock_Row;
380
381 PROCEDURE TRANSLATE_ROW (
382 x_strategy_id IN NUMBER,
383 x_strategy_name IN VARCHAR2,
384 x_strategy_description IN VARCHAR2,
385 x_owner IN VARCHAR2,
386 x_custom_mode IN VARCHAR2
387 )
388 IS
389 cursor c_last_updated_by is
390 select last_updated_by
391 from ams_iba_ps_strats_tl
392 where strategy_id = X_STRATEGY_ID
393 and USERENV('LANG') = LANGUAGE;
394
395 l_luby number; --last updated by
396
397 BEGIN
398 open c_last_updated_by;
399 fetch c_last_updated_by into l_luby;
400 close c_last_updated_by;
401
402 if (l_luby IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
403 then
404 update ams_iba_ps_strats_tl
405 set
406 strategy_name = nvl(x_strategy_name, strategy_name),
407 strategy_description = nvl(x_strategy_description, strategy_description),
408 source_lang = userenv('LANG'),
409 last_update_date = sysdate,
410 last_updated_by = decode(x_owner, 'SEED', 1,
411 'ORACLE', 2,
412 'SYSADMIN', 0, -1),
413 last_update_login = 0
414 where strategy_id = x_strategy_id
415 and userenv('LANG') in (language, source_lang);
416 end if;
417 END TRANSLATE_ROW;
418
422 X_CONTENT_TYPE IN VARCHAR2,
419 PROCEDURE LOAD_ROW (
420 X_STRATEGY_ID IN NUMBER,
421 X_MAX_RETURNED IN NUMBER,
423 X_STRATEGY_TYPE IN VARCHAR2,
424 X_STRATEGY_REF_CODE IN VARCHAR2,
425 X_SELECTOR_CLASS IN VARCHAR2,
426 X_STRATEGY_NAME IN VARCHAR2,
427 X_STRATEGY_DESCRIPTION IN VARCHAR2,
428 X_OWNER IN VARCHAR2,
429 X_CUSTOM_MODE IN VARCHAR2
430 )
431 IS
432 l_user_id number := 1;
433 l_obj_verno number;
434 l_dummy_char varchar2(1);
435 l_row_id varchar2(100);
436 l_strategy_id number;
437 l_db_luby_id number;
438 /*
439 cursor c_obj_verno is
440 select object_version_number
441 from ams_iba_ps_strats_b
442 where strategy_id = x_strategy_id;
443 */
444 cursor c_db_data_details is
445 select last_updated_by, nvl(object_version_number,1)
446 from ams_iba_ps_strats_b
447 where strategy_id = x_strategy_id;
448
449 cursor c_chk_strategy_exists is
450 select 'x'
451 from ams_iba_ps_strats_b
452 where strategy_id = x_strategy_id;
453
454 cursor c_get_strategy_id is
455 select ams_iba_ps_strats_b_s.nextval
456 from dual;
457 BEGIN
458
459 if X_OWNER = 'SEED' then
460 l_user_id := 1;
461 elsif X_OWNER = 'ORACLE' then
462 l_user_id := 2;
463 elsif X_OWNER = 'SYSADMIN' then
464 l_user_id := 0;
465 end if;
466
467 open c_chk_strategy_exists;
468 fetch c_chk_strategy_exists into l_dummy_char;
469 if c_chk_strategy_exists%notfound THEN
470 if x_strategy_id is null then
471 open c_get_strategy_id;
472 fetch c_get_strategy_id into l_strategy_id;
473 close c_get_strategy_id;
474 else
475 l_strategy_id := x_strategy_id;
476 end if;
477
478 l_obj_verno := 1;
479
480 AMS_IBA_PS_STRATS_B_PKG.INSERT_ROW (
481 p_created_by => l_user_id,
482 p_creation_date => SYSDATE,
483 p_last_updated_by => l_user_id,
484 p_last_update_date => SYSDATE,
485 p_last_update_login => 1,
486 px_object_version_number => l_obj_verno,
487 px_strategy_id => l_strategy_id,
488 p_max_returned => x_max_returned,
489 p_strategy_type => x_strategy_type,
490 p_content_type => x_content_type,
491 p_strategy_ref_code => x_strategy_ref_code,
492 p_selector_class => x_selector_class,
493 p_strategy_name => x_strategy_name,
494 p_strategy_description => x_strategy_description
495 );
496 else
497 open c_db_data_details;
498 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
499 close c_db_data_details;
500
501 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
502 then
503 l_strategy_id := x_strategy_id;
504
505 AMS_IBA_PS_STRATS_B_PKG.UPDATE_ROW (
506 p_created_by => l_user_id,
507 p_creation_date => SYSDATE,
508 p_last_updated_by => l_user_id,
509 p_last_update_date => SYSDATE,
510 p_last_update_login => 1,
511 p_object_version_number => l_obj_verno+1,
512 p_strategy_id => l_strategy_id,
513 p_max_returned => x_max_returned,
514 p_strategy_type => x_strategy_type,
515 p_content_type => x_content_type,
516 p_strategy_ref_code => x_strategy_ref_code,
517 p_selector_class => x_selector_class,
518 p_strategy_name => x_strategy_name,
519 p_strategy_description => x_strategy_description
520 );
521 end if;
522
523 end if;
524 close c_chk_strategy_exists;
525 END LOAD_ROW;
526
527 END AMS_IBA_PS_STRATS_B_PKG;