[Home] [Help]
PACKAGE BODY: APPS.AMW_SETUP_RISK_TYPES_PKG
Source
1 PACKAGE BODY AMW_SETUP_RISK_TYPES_PKG as
2 /* $Header: amwtrtpb.pls 120.3 2006/08/23 19:06:19 npanandi noship $ */
3
4
5 -- ===============================================================
6 -- Package name
7 -- AMW_SETUP_RISK_TYPES_PKG
8 -- Purpose
9 --
10 -- History
11 -- 07/06/2004 tsho Creates
12 -- ===============================================================
13
14
15 -- ===============================================================
16 -- Procedure name
17 -- INSERT_ROW
18 -- Purpose
19 -- create new compliance environment
20 -- in AMW_SETUP_RISK_TYPES_B and AMW_SETUP_RISK_TYPES_TL
21 -- ===============================================================
22 procedure INSERT_ROW (
23 X_ROWID in out nocopy VARCHAR2,
24 X_SETUP_RISK_TYPE_ID in NUMBER,
25 X_RISK_TYPE_CODE in VARCHAR2,
26 X_PARENT_SETUP_RISK_TYPE_ID in NUMBER,
27 X_START_DATE in DATE,
28 X_END_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_CREATION_DATE in DATE,
33 X_LAST_UPDATE_LOGIN in NUMBER,
34 X_SECURITY_GROUP_ID in NUMBER,
35 X_OBJECT_VERSION_NUMBER in NUMBER,
36 X_TAG in VARCHAR2,
37 X_SETUP_RISK_TYPE_NAME in VARCHAR2,
38 X_SETUP_RISK_TYPE_DESCRIPTION in VARCHAR2
39 ) is
40 cursor C is select ROWID from AMW_SETUP_RISK_TYPES_B
41 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID;
42 begin
43 insert into AMW_SETUP_RISK_TYPES_B (
44 SETUP_RISK_TYPE_ID,
45 RISK_TYPE_CODE,
46 PARENT_SETUP_RISK_TYPE_ID,
47 START_DATE,
48 END_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATE_LOGIN,
54 SECURITY_GROUP_ID,
55 OBJECT_VERSION_NUMBER,
56 TAG
57 ) values (
58 X_SETUP_RISK_TYPE_ID,
59 X_RISK_TYPE_CODE,
60 X_PARENT_SETUP_RISK_TYPE_ID,
61 X_START_DATE,
62 X_END_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_DATE,
65 X_CREATED_BY,
66 X_CREATION_DATE,
67 X_LAST_UPDATE_LOGIN,
68 X_SECURITY_GROUP_ID,
69 X_OBJECT_VERSION_NUMBER,
70 X_TAG
71 );
72
73 insert into AMW_SETUP_RISK_TYPES_TL (
74 LAST_UPDATE_LOGIN,
75 SETUP_RISK_TYPE_ID,
76 NAME,
77 DESCRIPTION,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 CREATION_DATE,
81 CREATED_BY,
82 SECURITY_GROUP_ID,
83 LANGUAGE,
84 SOURCE_LANG
85 ) select
86 X_LAST_UPDATE_LOGIN,
87 X_SETUP_RISK_TYPE_ID,
88 X_SETUP_RISK_TYPE_NAME,
89 X_SETUP_RISK_TYPE_DESCRIPTION,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_SECURITY_GROUP_ID,
95 L.LANGUAGE_CODE,
96 userenv('LANG')
97 from FND_LANGUAGES L
98 where L.INSTALLED_FLAG in ('I', 'B')
99 and not exists
100 (select NULL
101 from AMW_SETUP_RISK_TYPES_TL T
102 where T.SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID
103 and T.LANGUAGE = L.LANGUAGE_CODE);
104
105 open c;
106 fetch c into X_ROWID;
107 if (c%notfound) then
108 close c;
109 raise no_data_found;
110 end if;
111 close c;
112
113 end INSERT_ROW;
114
115
116
117 -- ===============================================================
118 -- Procedure name
119 -- LOCK_ROW
120 -- Purpose
121 --
122 -- ===============================================================
123 procedure LOCK_ROW (
124 X_SETUP_RISK_TYPE_ID in NUMBER,
125 X_RISK_TYPE_CODE in VARCHAR2,
126 X_PARENT_SETUP_RISK_TYPE_ID in NUMBER,
127 X_START_DATE in DATE,
128 X_END_DATE in DATE,
129 X_SECURITY_GROUP_ID in NUMBER,
130 X_OBJECT_VERSION_NUMBER in NUMBER,
131 X_TAG in VARCHAR2,
132 X_SETUP_RISK_TYPE_NAME in VARCHAR2,
133 X_SETUP_RISK_TYPE_DESCRIPTION in VARCHAR2
134 ) is
135 cursor c is select
136 RISK_TYPE_CODE,
137 START_DATE,
138 END_DATE,
139 SECURITY_GROUP_ID,
140 OBJECT_VERSION_NUMBER,
141 TAG
142 from AMW_SETUP_RISK_TYPES_B
143 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID
144 and PARENT_SETUP_RISK_TYPE_ID = X_PARENT_SETUP_RISK_TYPE_ID
145 for update of SETUP_RISK_TYPE_ID nowait;
146 recinfo c%rowtype;
147
148 cursor c1 is select
149 NAME,
150 DESCRIPTION,
151 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
152 from AMW_SETUP_RISK_TYPES_TL
153 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID
154 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
155 for update of SETUP_RISK_TYPE_ID nowait;
156 begin
157 open c;
158 fetch c into recinfo;
159 if (c%notfound) then
160 close c;
161 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
162 app_exception.raise_exception;
163 end if;
164 close c;
165 if (
166 ((recinfo.RISK_TYPE_CODE = X_RISK_TYPE_CODE)
167 OR ((recinfo.RISK_TYPE_CODE is null) AND (X_RISK_TYPE_CODE is null)))
168 AND ((recinfo.START_DATE = X_START_DATE)
169 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
170 AND ((recinfo.END_DATE = X_END_DATE)
171 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
172 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
173 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
174 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
175 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
176 AND ((recinfo.TAG = X_TAG)
177 OR ((recinfo.TAG is null) AND (X_TAG is null)))
178 ) then
179 null;
180 else
181 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
182 app_exception.raise_exception;
183 end if;
184
185 for tlinfo in c1 loop
186 if (tlinfo.BASELANG = 'Y') then
187 if ( (tlinfo.NAME = X_SETUP_RISK_TYPE_NAME)
188 AND ((tlinfo.DESCRIPTION = X_SETUP_RISK_TYPE_DESCRIPTION)
189 OR ((tlinfo.DESCRIPTION is null) AND (X_SETUP_RISK_TYPE_DESCRIPTION is null)))
190 ) then
191 null;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196 end if;
197 end loop;
198 return;
199 end LOCK_ROW;
200
201
202
203 -- ===============================================================
204 -- Procedure name
205 -- UPDATE_ROW
206 -- Purpose
207 -- update AMW_SETUP_RISK_TYPES_B and AMW_SETUP_RISK_TYPES_TL
208 -- ===============================================================
209 procedure UPDATE_ROW (
210 X_SETUP_RISK_TYPE_ID in NUMBER,
211 X_RISK_TYPE_CODE in VARCHAR2,
212 X_PARENT_SETUP_RISK_TYPE_ID in NUMBER,
213 X_START_DATE in DATE,
214 X_END_DATE in DATE,
215 X_LAST_UPDATED_BY in NUMBER,
216 X_LAST_UPDATE_DATE in DATE,
217 X_LAST_UPDATE_LOGIN in NUMBER,
218 X_SECURITY_GROUP_ID in NUMBER,
219 X_OBJECT_VERSION_NUMBER in NUMBER,
220 X_TAG in VARCHAR2,
221 X_SETUP_RISK_TYPE_NAME in VARCHAR2,
222 X_SETUP_RISK_TYPE_DESCRIPTION in VARCHAR2
223 ) is
224 begin
225 update AMW_SETUP_RISK_TYPES_B set
226 RISK_TYPE_CODE = X_RISK_TYPE_CODE,
227 PARENT_SETUP_RISK_TYPE_ID = X_PARENT_SETUP_RISK_TYPE_ID,
228 START_DATE = X_START_DATE,
229 END_DATE = X_END_DATE,
230 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
231 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
232 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
233 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
234 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
235 TAG = X_TAG
236 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID;
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241
242 update AMW_SETUP_RISK_TYPES_TL set
243 NAME = X_SETUP_RISK_TYPE_NAME,
244 DESCRIPTION = X_SETUP_RISK_TYPE_DESCRIPTION,
245 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
246 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
247 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
248 SOURCE_LANG = userenv('LANG')
249 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID
250 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
251
252 if (sql%notfound) then
253 raise no_data_found;
254 end if;
255
256 end UPDATE_ROW;
257
258
259 -- ===============================================================
260 -- Procedure name
261 -- LOAD_ROW
262 -- Purpose
263 -- load AMW_SETUP_RISK_TYPE to AMW_SETUP_RISK_TYPES_B(_TL)
264 -- ===============================================================
265 procedure LOAD_ROW (
266 X_SETUP_RISK_TYPE_ID in NUMBER,
267 X_RISK_TYPE_CODE in VARCHAR2,
268 X_PARENT_SETUP_RISK_TYPE_ID in NUMBER,
269 X_START_DATE in DATE,
270 X_END_DATE in DATE,
271 X_SECURITY_GROUP_ID in NUMBER,
272 X_OBJECT_VERSION_NUMBER in NUMBER,
273 X_TAG in VARCHAR2,
274 X_SETUP_RISK_TYPE_NAME in VARCHAR2,
275 X_SETUP_RISK_TYPE_DESCRIPTION in VARCHAR2,
276 X_OWNER in VARCHAR2,
277 /** 08.23.2006 npanandi: bug 5486153 fix -- no need to pass
278 X_PARENT_SETUP_RISK_TYPE_NAME, as it creates translation issues
279 X_PARENT_SETUP_RISK_TYPE_NAME in VARCHAR2,
280 **/
281 X_COMPLIANCE_ENV_ID in NUMBER
282 ) IS
283
284 l_user_id number;
285 l_setup_risk_type_id number;
286 l_parent_setup_risk_type_id number;
287 l_risk_type_code varchar2(30);
288 l_existed_setup_risk_type_id number;
289 l_existed_risk_type_code varchar2(30);
290 l_row_id varchar2(32767);
291 l_setup_risk_type_name varchar2(240);
292 l_parent_setup_risk_type_name varchar2(240);
293 l_compliance_env_id number;
294
295 l_return_status varchar2(1);
296 l_msg_count number;
297 l_msg_data varchar2(2000);
298
299 cursor is_setup_risk_type_exist(l_setup_risk_type_name in varchar2) is
300 select b.setup_risk_type_id
301 from amw_setup_risk_types_b b
302 ,amw_setup_risk_types_tl tl
303 where b.setup_risk_type_id = tl.setup_risk_type_id
304 and tl.LANGUAGE = USERENV('LANG')
305 and tl.name = l_setup_risk_type_name;
306
307 cursor is_risk_type_code_exist(l_risk_type_code in varchar2) is
308 select b.risk_type_code
309 from amw_setup_risk_types_b b
310 where b.risk_type_code = l_risk_type_code;
311
312 cursor get_new_setup_risk_type_id is
313 select AMW_SETUP_RISK_TYPE_S.nextval
314 from dual;
315
316 BEGIN
317 -- Translate owner to file_last_updated_by
318 l_user_id := fnd_load_util.owner_id(X_OWNER);
319
320 l_setup_risk_type_name := X_SETUP_RISK_TYPE_NAME;
321 /** 08.23.2006 npanandi: bug 5486153 fix --- X_PARENT_SETUP_RISK_TYPE_NAME
322 is not being passed
323 l_parent_setup_risk_type_name := X_PARENT_SETUP_RISK_TYPE_NAME;
324 **/
325 l_parent_setup_risk_type_id := X_PARENT_SETUP_RISK_TYPE_ID;
326 l_risk_type_code := X_RISK_TYPE_CODE;
327 l_existed_setup_risk_type_id := null;
328 l_existed_risk_type_code := null;
329 l_compliance_env_id := X_COMPLIANCE_ENV_ID;
330
331
332 -- 10.26.2004 tsho: should handle loading Setup Risk Types other than Root
333 IF (X_SETUP_RISK_TYPE_ID <> -1) THEN
334 /*** 06.06.06 npanandi: commenting the below irrelevant portion because of
335 AppsRe bug 5282548 consideration -- below DELETEs et.al. are causing
336 major issues in an NLS environment, so the idea here is to adhere
337 to the standard LDT load_row format (i.e. w/o- any DELETEs)
338 to the extent possible
339 ***/
340
341 /***
342 IF (l_setup_risk_type_name is not null) THEN
343 OPEN is_setup_risk_type_exist (l_setup_risk_type_name);
344 FETCH is_setup_risk_type_exist INTO l_existed_setup_risk_type_id;
345 CLOSE is_setup_risk_type_exist;
346
347 -- Delete specified existing risk type and its descendant.
348 -- Delete associations records in AMW_COMPLIANCE_ENV_ASSOCS
349 -- for the specified existing risk type and its descendant.
350 IF(l_existed_setup_risk_type_id is not null) THEN
351 AMW_SETUP_RISK_TYPES_PVT.Delete_Risk_Types(
352 p_setup_risk_type_id => l_existed_setup_risk_type_id,
353 x_return_status => l_return_status,
354 x_msg_count => l_msg_count,
355 x_msg_data => l_msg_data);
356 END IF;
357
358 -- get the new setup_risk_type_id
359 OPEN get_new_setup_risk_type_id;
360 FETCH get_new_setup_risk_type_id INTO l_setup_risk_type_id;
361 CLOSE get_new_setup_risk_type_id;
362 ***/
363
364 -- find out the parent_setup_risk_type_id if passed-in X_PARENT_SETUP_RISK_TYPE_NAME is not null
365 -- otherwise, use passed-in X_PARENT_SETUP_RISK_TYPE_ID as l_parent_setup_risk_type_id
366 /** 08.23.2006 npanandi: bug 5486153 fix --- logic around
367 parentSetupriskTypeName is removed, due to translation issues
368 IF (l_parent_setup_risk_type_name is not null) THEN
369 OPEN is_setup_risk_type_exist (l_parent_setup_risk_type_name);
370 FETCH is_setup_risk_type_exist INTO l_parent_setup_risk_type_id;
371 CLOSE is_setup_risk_type_exist;
372 END IF;
373 **/
374
375 /**** 06.06.06 npanandi: AppsRe bug 5282548 -- commenting below too
376 for the reasons mentioned above
377 ****/
378
379 /**
380 -- check if the specified risk_type_code is in used already
384 CLOSE is_risk_type_code_exist;
381 IF (l_risk_type_code is not null) THEN
382 OPEN is_risk_type_code_exist (l_risk_type_code);
383 FETCH is_risk_type_code_exist INTO l_existed_risk_type_code;
385
386 IF (l_existed_risk_type_code is not null) THEN
387 l_risk_type_code := l_setup_risk_type_id;
388 END IF;
389 END IF;
390 **/
391
392 /*** 06.06.06 npanandi: AppsRe bug 5282548 --- added begin clause
393 to handle updates/inserts
394 ***/
395 begin
396 AMW_SETUP_RISK_TYPES_PKG.UPDATE_ROW (
397 X_SETUP_RISK_TYPE_ID => X_SETUP_RISK_TYPE_ID,
398 X_RISK_TYPE_CODE => X_RISK_TYPE_CODE,
399 /*** X_PARENT_SETUP_RISK_TYPE_ID => X_PARENT_SETUP_RISK_TYPE_ID, ***/
400 X_PARENT_SETUP_RISK_TYPE_ID => l_parent_setup_risk_type_id,
401 X_START_DATE => X_START_DATE,
402 X_END_DATE => X_END_DATE,
403 X_LAST_UPDATED_BY => l_user_id,
404 X_LAST_UPDATE_DATE => sysdate,
405 X_LAST_UPDATE_LOGIN => 0,
406 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
407 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
408 X_TAG => X_TAG,
409 X_SETUP_RISK_TYPE_NAME => X_SETUP_RISK_TYPE_NAME,
410 X_SETUP_RISK_TYPE_DESCRIPTION => X_SETUP_RISK_TYPE_DESCRIPTION);
411 exception
412 when no_data_found then
413 AMW_SETUP_RISK_TYPES_PKG.INSERT_ROW(
414 X_ROWID => l_row_id,
415 /*** 06.06.06 npanandi: the insert row here should take seeded
416 setupRiskTypeId, NOT any sequence generated one
417 X_SETUP_RISK_TYPE_ID => l_setup_risk_type_id, **/
418 X_SETUP_RISK_TYPE_ID => X_SETUP_RISK_TYPE_ID,
419 X_RISK_TYPE_CODE => X_RISK_TYPE_CODE, /**l_risk_type_code,**/
420 X_PARENT_SETUP_RISK_TYPE_ID => l_parent_setup_risk_type_id,
421 X_START_DATE => X_START_DATE,
422 X_END_DATE => X_END_DATE,
423 X_LAST_UPDATED_BY => l_user_id,
424 X_LAST_UPDATE_DATE => sysdate,
425 X_CREATED_BY => l_user_id,
426 X_CREATION_DATE => sysdate,
427 X_LAST_UPDATE_LOGIN => 0,
428 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
429 X_OBJECT_VERSION_NUMBER => 1,
430 X_TAG => X_TAG,
431 X_SETUP_RISK_TYPE_NAME => X_SETUP_RISK_TYPE_NAME,
432 X_SETUP_RISK_TYPE_DESCRIPTION => X_SETUP_RISK_TYPE_DESCRIPTION);
433
434 -- check if default compliance env id is specified to associate with
435 IF (l_compliance_env_id is not null) THEN
436 AMW_COMPLIANCE_ENV_ASSOCS_PVT.PROCESS_COMPLIANCE_ENV_ASSOCS (
437 p_select_flag => 'Y',
438 p_compliance_env_id => l_compliance_env_id,
439 p_object_type => 'SETUP_RISK_TYPE',
440 p_pk1 => X_SETUP_RISK_TYPE_ID, /**l_setup_risk_type_id,**/
441 x_return_status => l_return_status,
442 x_msg_count => l_msg_count,
443 x_msg_data => l_msg_data);
444 END IF; -- end of if: l_compliance_env_id is not null
445 /*** END IF; -- end of if: l_setup_risk_type_name is not null ***/
446 end; /** end of begin,exception for handling updates/inserts **/
447 ELSE
448 -- handle Root Setup Risk Type (aka, X_SETUP_RISK_TYPE_ID = -1)
449 BEGIN
450 select SETUP_RISK_TYPE_ID into l_setup_risk_type_id
451 from AMW_SETUP_RISK_TYPES_B
452 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID;
453
454 AMW_SETUP_RISK_TYPES_PKG.UPDATE_ROW (
455 X_SETUP_RISK_TYPE_ID => X_SETUP_RISK_TYPE_ID,
456 X_RISK_TYPE_CODE => X_RISK_TYPE_CODE,
457 X_PARENT_SETUP_RISK_TYPE_ID => X_PARENT_SETUP_RISK_TYPE_ID,
458 X_START_DATE => X_START_DATE,
459 X_END_DATE => X_END_DATE,
460 X_LAST_UPDATED_BY => l_user_id,
461 X_LAST_UPDATE_DATE => sysdate,
462 X_LAST_UPDATE_LOGIN => 0,
463 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
464 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
465 X_TAG => X_TAG,
466 X_SETUP_RISK_TYPE_NAME => X_SETUP_RISK_TYPE_NAME,
467 X_SETUP_RISK_TYPE_DESCRIPTION => X_SETUP_RISK_TYPE_DESCRIPTION);
468
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 -- 07.29.2004 tsho: should use the passed-in x_setup_risk_type_id
472 /*
473 select AMW_SETUP_RISK_TYPE_S.nextval into l_setup_risk_type_id
474 from dual;
475 */
476
477 AMW_SETUP_RISK_TYPES_PKG.INSERT_ROW(
478 X_ROWID => l_row_id,
479 X_SETUP_RISK_TYPE_ID => X_SETUP_RISK_TYPE_ID,
480 X_RISK_TYPE_CODE => X_RISK_TYPE_CODE,
481 X_PARENT_SETUP_RISK_TYPE_ID => X_PARENT_SETUP_RISK_TYPE_ID,
482 X_START_DATE => X_START_DATE,
483 X_END_DATE => X_END_DATE,
484 X_LAST_UPDATED_BY => l_user_id,
485 X_LAST_UPDATE_DATE => sysdate,
489 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
486 X_CREATED_BY => l_user_id,
487 X_CREATION_DATE => sysdate,
488 X_LAST_UPDATE_LOGIN => 0,
490 X_OBJECT_VERSION_NUMBER => 1,
491 X_TAG => X_TAG,
492 X_SETUP_RISK_TYPE_NAME => X_SETUP_RISK_TYPE_NAME,
493 X_SETUP_RISK_TYPE_DESCRIPTION => X_SETUP_RISK_TYPE_DESCRIPTION);
494 END;
495
496 END IF; -- end of if: X_SETUP_RISK_TYPE_ID <> -1
497
498
499 END LOAD_ROW;
500
501
502 -- ===============================================================
503 -- Procedure name
504 -- DELETE_ROW
505 -- Purpose
506 --
507 -- ===============================================================
508 procedure DELETE_ROW (
509 X_SETUP_RISK_TYPE_ID in NUMBER,
510 X_PARENT_SETUP_RISK_TYPE_ID in NUMBER
511 ) is
512 begin
513 delete from AMW_SETUP_RISK_TYPES_B
514 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID
515 and PARENT_SETUP_RISK_TYPE_ID = X_PARENT_SETUP_RISK_TYPE_ID;
516
517 if (sql%notfound) then
518 raise no_data_found;
519 end if;
520
521 delete from AMW_SETUP_RISK_TYPES_TL
522 where SETUP_RISK_TYPE_ID = X_SETUP_RISK_TYPE_ID
523 and SETUP_RISK_TYPE_ID not in (
524 select SETUP_RISK_TYPE_ID from AMW_SETUP_RISK_TYPES_B
525 )
526 and SETUP_RISK_TYPE_ID not in (
527 select PARENT_SETUP_RISK_TYPE_ID from AMW_SETUP_RISK_TYPES_B
528 );
529
530 if (sql%notfound) then
531 raise no_data_found;
532 end if;
533
534 end DELETE_ROW;
535
536
537
538 -- ===============================================================
539 -- Procedure name
540 -- ADD_LANGUAGE
541 -- Purpose
542 --
543 -- ===============================================================
544 procedure ADD_LANGUAGE
545 is
546 begin
547 delete from AMW_SETUP_RISK_TYPES_TL T
548 where not exists
549 (select NULL
550 from AMW_SETUP_RISK_TYPES_B B
551 where B.SETUP_RISK_TYPE_ID = T.SETUP_RISK_TYPE_ID
552 );
553
554 update AMW_SETUP_RISK_TYPES_TL T set (
555 NAME,
556 DESCRIPTION
557 ) = (select
558 B.NAME,
559 B.DESCRIPTION
560 from AMW_SETUP_RISK_TYPES_TL B
561 where B.SETUP_RISK_TYPE_ID = T.SETUP_RISK_TYPE_ID
562 and B.LANGUAGE = T.SOURCE_LANG)
563 where (
564 T.SETUP_RISK_TYPE_ID,
565 T.LANGUAGE
566 ) in (select
567 SUBT.SETUP_RISK_TYPE_ID,
568 SUBT.LANGUAGE
569 from AMW_SETUP_RISK_TYPES_TL SUBB, AMW_SETUP_RISK_TYPES_TL SUBT
570 where SUBB.SETUP_RISK_TYPE_ID = SUBT.SETUP_RISK_TYPE_ID
571 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
572 and (SUBB.NAME <> SUBT.NAME
573 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
574 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
575 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
576 ));
577
578 insert into AMW_SETUP_RISK_TYPES_TL (
579 LAST_UPDATE_LOGIN,
580 SETUP_RISK_TYPE_ID,
581 NAME,
582 DESCRIPTION,
583 LAST_UPDATE_DATE,
584 LAST_UPDATED_BY,
585 CREATION_DATE,
586 CREATED_BY,
587 SECURITY_GROUP_ID,
588 LANGUAGE,
589 SOURCE_LANG
590 ) select
591 B.LAST_UPDATE_LOGIN,
592 B.SETUP_RISK_TYPE_ID,
593 B.NAME,
594 B.DESCRIPTION,
595 B.LAST_UPDATE_DATE,
596 B.LAST_UPDATED_BY,
597 B.CREATION_DATE,
598 B.CREATED_BY,
599 B.SECURITY_GROUP_ID,
600 L.LANGUAGE_CODE,
601 B.SOURCE_LANG
602 from AMW_SETUP_RISK_TYPES_TL B, FND_LANGUAGES L
603 where L.INSTALLED_FLAG in ('I', 'B')
604 and B.LANGUAGE = userenv('LANG')
605 and not exists
606 (select NULL
607 from AMW_SETUP_RISK_TYPES_TL T
608 where T.SETUP_RISK_TYPE_ID = B.SETUP_RISK_TYPE_ID
609 and T.LANGUAGE = L.LANGUAGE_CODE);
610 end ADD_LANGUAGE;
611
612 /**05.31.2006 npanandi: bug 5259681 fix, added translate row***/
613 procedure TRANSLATE_ROW(
614 X_SETUP_RISK_TYPE_ID in NUMBER,
615 X_SETUP_RISK_TYPE_NAME in VARCHAR2,
616 X_SETUP_RISK_TYPE_DESCRIPTION in VARCHAR2,
617 X_LAST_UPDATE_DATE in VARCHAR2,
618 X_OWNER in VARCHAR2,
619 X_CUSTOM_MODE in VARCHAR2) is
620
621 f_luby number; -- entity owner in file
622 f_ludate date; -- entity update date in file
623 db_luby number; -- entity owner in db
624 db_ludate date; -- entity update date in db
625 begin
626 -- Translate owner to file_last_updated_by
627 f_luby := fnd_load_util.owner_id(X_OWNER);
628
629 -- Translate char last_update_date to date
630 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
631
632 select last_updated_by, last_update_date
633 into db_luby, db_ludate
634 from AMW_SETUP_RISK_TYPES_TL
635 where setup_risk_type_id = X_SETUP_RISK_TYPE_ID
636 and language = userenv('LANG');
637
638 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) then
639 update AMW_SETUP_RISK_TYPES_TL
640 set name = X_SETUP_RISK_TYPE_NAME,
641 description = nvl(X_SETUP_RISK_TYPE_DESCRIPTION, description),
642 source_lang = userenv('LANG'),
643 last_update_date = f_ludate,
644 last_updated_by = f_luby,
645 last_update_login = 0
646 where setup_risk_type_id = X_SETUP_RISK_TYPE_ID
647 and userenv('LANG') in (language, source_lang);
648 end if;
649
650 end TRANSLATE_ROW;
651 /**05.31.2006 npanandi: bug 5259681 fix ends***/
652
653
654 -- ----------------------------------------------------------------------
655 end AMW_SETUP_RISK_TYPES_PKG;