[Home] [Help]
PACKAGE BODY: APPS.AMW_COMPLIANCE_ENVS_PKG
Source
1 PACKAGE BODY AMW_COMPLIANCE_ENVS_PKG as
2 /* $Header: amwtenvb.pls 120.1 2006/05/31 23:34:19 npanandi noship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_COMPLIANCE_ENVS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 06/24/2004 tsho Creates
11 -- ===============================================================
12
13
14 -- ===============================================================
15 -- Procedure name
16 -- INSERT_ROW
17 -- Purpose
18 -- create new compliance environment
19 -- in AMW_COMPLIANCE_ENVS_B and AMW_COMPLIANCE_ENVS_TL
20 -- ===============================================================
21 procedure INSERT_ROW (
22 X_ROWID in out nocopy VARCHAR2,
23 X_COMPLIANCE_ENV_ID in NUMBER,
24 X_START_DATE in DATE,
25 X_END_DATE in DATE,
26 X_ENABLED_FLAG in VARCHAR2,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_CREATION_DATE in DATE,
31 X_LAST_UPDATE_LOGIN in NUMBER,
32 X_SECURITY_GROUP_ID in NUMBER,
33 X_OBJECT_VERSION_NUMBER in NUMBER,
34 X_ATTRIBUTE_CATEGORY in VARCHAR2,
35 X_ATTRIBUTE1 in VARCHAR2,
36 X_ATTRIBUTE2 in VARCHAR2,
37 X_ATTRIBUTE3 in VARCHAR2,
38 X_ATTRIBUTE4 in VARCHAR2,
39 X_ATTRIBUTE5 in VARCHAR2,
40 X_ATTRIBUTE6 in VARCHAR2,
41 X_ATTRIBUTE7 in VARCHAR2,
42 X_ATTRIBUTE8 in VARCHAR2,
43 X_ATTRIBUTE9 in VARCHAR2,
44 X_ATTRIBUTE10 in VARCHAR2,
45 X_ATTRIBUTE11 in VARCHAR2,
46 X_ATTRIBUTE12 in VARCHAR2,
47 X_ATTRIBUTE13 in VARCHAR2,
48 X_ATTRIBUTE14 in VARCHAR2,
49 X_ATTRIBUTE15 in VARCHAR2,
50 X_COMPLIANCE_ENV_NAME in VARCHAR2,
51 X_COMPLIANCE_ENV_ALIAS in VARCHAR2,
52 X_COMPLIANCE_ENV_DESCRIPTION in VARCHAR2
53 ) is
54 cursor C is select ROWID from AMW_COMPLIANCE_ENVS_B
55 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID;
56 begin
57 insert into AMW_COMPLIANCE_ENVS_B (
58 COMPLIANCE_ENV_ID,
59 START_DATE,
60 END_DATE,
61 ENABLED_FLAG,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_DATE,
64 CREATED_BY,
65 CREATION_DATE,
66 LAST_UPDATE_LOGIN,
67 SECURITY_GROUP_ID,
68 OBJECT_VERSION_NUMBER,
69 ATTRIBUTE_CATEGORY,
70 ATTRIBUTE1,
71 ATTRIBUTE2,
72 ATTRIBUTE3,
73 ATTRIBUTE4,
74 ATTRIBUTE5,
75 ATTRIBUTE6,
76 ATTRIBUTE7,
77 ATTRIBUTE8,
78 ATTRIBUTE9,
79 ATTRIBUTE10,
80 ATTRIBUTE11,
81 ATTRIBUTE12,
82 ATTRIBUTE13,
83 ATTRIBUTE14,
84 ATTRIBUTE15
85 ) values (
86 X_COMPLIANCE_ENV_ID,
87 X_START_DATE,
88 X_END_DATE,
89 X_ENABLED_FLAG,
90 X_LAST_UPDATED_BY,
91 X_LAST_UPDATE_DATE,
92 X_CREATED_BY,
93 X_CREATION_DATE,
94 X_LAST_UPDATE_LOGIN,
95 X_SECURITY_GROUP_ID,
96 X_OBJECT_VERSION_NUMBER,
97 X_ATTRIBUTE_CATEGORY,
98 X_ATTRIBUTE1,
99 X_ATTRIBUTE2,
100 X_ATTRIBUTE3,
101 X_ATTRIBUTE4,
102 X_ATTRIBUTE5,
103 X_ATTRIBUTE6,
104 X_ATTRIBUTE7,
105 X_ATTRIBUTE8,
106 X_ATTRIBUTE9,
107 X_ATTRIBUTE10,
108 X_ATTRIBUTE11,
109 X_ATTRIBUTE12,
110 X_ATTRIBUTE13,
111 X_ATTRIBUTE14,
112 X_ATTRIBUTE15
113 );
114
115 insert into AMW_COMPLIANCE_ENVS_TL (
116 LAST_UPDATE_LOGIN,
117 COMPLIANCE_ENV_ID,
118 NAME,
119 ALIAS,
120 DESCRIPTION,
121 LAST_UPDATE_DATE,
122 LAST_UPDATED_BY,
123 CREATION_DATE,
124 CREATED_BY,
125 SECURITY_GROUP_ID,
126 LANGUAGE,
127 SOURCE_LANG
128 ) select
129 X_LAST_UPDATE_LOGIN,
130 X_COMPLIANCE_ENV_ID,
131 X_COMPLIANCE_ENV_NAME,
132 X_COMPLIANCE_ENV_ALIAS,
133 X_COMPLIANCE_ENV_DESCRIPTION,
134 X_LAST_UPDATE_DATE,
135 X_LAST_UPDATED_BY,
136 X_CREATION_DATE,
137 X_CREATED_BY,
138 X_SECURITY_GROUP_ID,
139 L.LANGUAGE_CODE,
140 userenv('LANG')
141 from FND_LANGUAGES L
142 where L.INSTALLED_FLAG in ('I', 'B')
143 and not exists
144 (select NULL
145 from AMW_COMPLIANCE_ENVS_TL T
146 where T.COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID
147 and T.LANGUAGE = L.LANGUAGE_CODE);
148
149 open c;
150 fetch c into X_ROWID;
151 if (c%notfound) then
152 close c;
153 raise no_data_found;
154 end if;
155 close c;
156
157 end INSERT_ROW;
158
159
160
161 -- ===============================================================
162 -- Procedure name
163 -- LOCK_ROW
164 -- Purpose
165 --
166 -- ===============================================================
167 procedure LOCK_ROW (
168 X_COMPLIANCE_ENV_ID in NUMBER,
169 X_START_DATE in DATE,
170 X_END_DATE in DATE,
171 X_ENABLED_FLAG in VARCHAR2,
172 X_SECURITY_GROUP_ID in NUMBER,
173 X_OBJECT_VERSION_NUMBER in NUMBER,
174 X_ATTRIBUTE_CATEGORY in VARCHAR2,
175 X_ATTRIBUTE1 in VARCHAR2,
176 X_ATTRIBUTE2 in VARCHAR2,
177 X_ATTRIBUTE3 in VARCHAR2,
178 X_ATTRIBUTE4 in VARCHAR2,
179 X_ATTRIBUTE5 in VARCHAR2,
180 X_ATTRIBUTE6 in VARCHAR2,
181 X_ATTRIBUTE7 in VARCHAR2,
182 X_ATTRIBUTE8 in VARCHAR2,
183 X_ATTRIBUTE9 in VARCHAR2,
184 X_ATTRIBUTE10 in VARCHAR2,
185 X_ATTRIBUTE11 in VARCHAR2,
186 X_ATTRIBUTE12 in VARCHAR2,
187 X_ATTRIBUTE13 in VARCHAR2,
188 X_ATTRIBUTE14 in VARCHAR2,
189 X_ATTRIBUTE15 in VARCHAR2,
190 X_COMPLIANCE_ENV_NAME in VARCHAR2,
191 X_COMPLIANCE_ENV_ALIAS in VARCHAR2,
192 X_COMPLIANCE_ENV_DESCRIPTION in VARCHAR2
193 ) is
194 cursor c is select
195 START_DATE,
196 END_DATE,
197 ENABLED_FLAG,
198 SECURITY_GROUP_ID,
199 OBJECT_VERSION_NUMBER,
200 ATTRIBUTE_CATEGORY,
201 ATTRIBUTE1,
202 ATTRIBUTE2,
203 ATTRIBUTE3,
204 ATTRIBUTE4,
205 ATTRIBUTE5,
206 ATTRIBUTE6,
207 ATTRIBUTE7,
208 ATTRIBUTE8,
209 ATTRIBUTE9,
210 ATTRIBUTE10,
211 ATTRIBUTE11,
212 ATTRIBUTE12,
213 ATTRIBUTE13,
214 ATTRIBUTE14,
215 ATTRIBUTE15
216 from AMW_COMPLIANCE_ENVS_B
217 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID
218 for update of COMPLIANCE_ENV_ID nowait;
219 recinfo c%rowtype;
220
221 cursor c1 is select
222 NAME,
223 ALIAS,
224 DESCRIPTION,
225 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
226 from AMW_COMPLIANCE_ENVS_TL
227 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID
228 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
229 for update of COMPLIANCE_ENV_ID nowait;
230 begin
231 open c;
232 fetch c into recinfo;
233 if (c%notfound) then
234 close c;
235 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
236 app_exception.raise_exception;
237 end if;
238 close c;
239 if (
240 ((recinfo.START_DATE = X_START_DATE)
241 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
242 AND ((recinfo.END_DATE = X_END_DATE)
243 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
244 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
245 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
246 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
247 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
248 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
249 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
250 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
251 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
252 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
253 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
254 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
255 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
256 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
257 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
258 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
259 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
260 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
261 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
262 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
263 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
264 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
265 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
266 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
267 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
268 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
269 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
270 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
271 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
272 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
273 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
274 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
275 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
276 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
277 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
278 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
279 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
280 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
281 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
282 ) then
283 null;
284 else
285 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
286 app_exception.raise_exception;
287 end if;
288
289 for tlinfo in c1 loop
290 if (tlinfo.BASELANG = 'Y') then
291 if ( (tlinfo.NAME = X_COMPLIANCE_ENV_NAME)
292 AND ((tlinfo.ALIAS = X_COMPLIANCE_ENV_ALIAS)
293 OR ((tlinfo.ALIAS is null) AND (X_COMPLIANCE_ENV_ALIAS is null)))
294 AND ((tlinfo.DESCRIPTION = X_COMPLIANCE_ENV_DESCRIPTION)
295 OR ((tlinfo.DESCRIPTION is null) AND (X_COMPLIANCE_ENV_DESCRIPTION is null)))
296 ) then
297 null;
298 else
299 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
300 app_exception.raise_exception;
301 end if;
302 end if;
303 end loop;
304 return;
305 end LOCK_ROW;
306
307
308
309 -- ===============================================================
310 -- Procedure name
311 -- UPDATE_ROW
312 -- Purpose
313 -- update AMW_COMPLIANCE_ENVS_B and AMW_COMPLIANCE_ENVS_TL
314 -- ===============================================================
315 procedure UPDATE_ROW (
316 X_COMPLIANCE_ENV_ID in NUMBER,
317 X_START_DATE in DATE,
318 X_END_DATE in DATE,
319 X_ENABLED_FLAG in VARCHAR2,
320 X_LAST_UPDATED_BY in NUMBER,
321 X_LAST_UPDATE_DATE in DATE,
322 X_LAST_UPDATE_LOGIN in NUMBER,
323 X_SECURITY_GROUP_ID in NUMBER,
324 X_OBJECT_VERSION_NUMBER in NUMBER,
325 X_ATTRIBUTE_CATEGORY in VARCHAR2,
326 X_ATTRIBUTE1 in VARCHAR2,
327 X_ATTRIBUTE2 in VARCHAR2,
328 X_ATTRIBUTE3 in VARCHAR2,
329 X_ATTRIBUTE4 in VARCHAR2,
330 X_ATTRIBUTE5 in VARCHAR2,
331 X_ATTRIBUTE6 in VARCHAR2,
332 X_ATTRIBUTE7 in VARCHAR2,
333 X_ATTRIBUTE8 in VARCHAR2,
334 X_ATTRIBUTE9 in VARCHAR2,
335 X_ATTRIBUTE10 in VARCHAR2,
336 X_ATTRIBUTE11 in VARCHAR2,
337 X_ATTRIBUTE12 in VARCHAR2,
338 X_ATTRIBUTE13 in VARCHAR2,
339 X_ATTRIBUTE14 in VARCHAR2,
340 X_ATTRIBUTE15 in VARCHAR2,
341 X_COMPLIANCE_ENV_NAME in VARCHAR2,
342 X_COMPLIANCE_ENV_ALIAS in VARCHAR2,
343 X_COMPLIANCE_ENV_DESCRIPTION in VARCHAR2
344 ) is
345 begin
346 update AMW_COMPLIANCE_ENVS_B set
347 START_DATE = X_START_DATE,
348 END_DATE = X_END_DATE,
349 ENABLED_FLAG = X_ENABLED_FLAG,
350 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
351 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
352 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
353 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
354 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
355 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
356 ATTRIBUTE1 = X_ATTRIBUTE1,
357 ATTRIBUTE2 = X_ATTRIBUTE2,
358 ATTRIBUTE3 = X_ATTRIBUTE3,
359 ATTRIBUTE4 = X_ATTRIBUTE4,
360 ATTRIBUTE5 = X_ATTRIBUTE5,
361 ATTRIBUTE6 = X_ATTRIBUTE6,
362 ATTRIBUTE7 = X_ATTRIBUTE7,
363 ATTRIBUTE8 = X_ATTRIBUTE8,
364 ATTRIBUTE9 = X_ATTRIBUTE9,
365 ATTRIBUTE10 = X_ATTRIBUTE10,
366 ATTRIBUTE11 = X_ATTRIBUTE11,
367 ATTRIBUTE12 = X_ATTRIBUTE12,
368 ATTRIBUTE13 = X_ATTRIBUTE13,
369 ATTRIBUTE14 = X_ATTRIBUTE14,
370 ATTRIBUTE15 = X_ATTRIBUTE15
371 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID;
372
373 if (sql%notfound) then
374 raise no_data_found;
375 end if;
376
377 update AMW_COMPLIANCE_ENVS_TL set
378 NAME = X_COMPLIANCE_ENV_NAME,
379 ALIAS = X_COMPLIANCE_ENV_ALIAS,
380 DESCRIPTION = X_COMPLIANCE_ENV_DESCRIPTION,
381 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
382 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
383 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
384 SOURCE_LANG = userenv('LANG')
385 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID
386 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
387
388 if (sql%notfound) then
389 raise no_data_found;
390 end if;
391
392 end UPDATE_ROW;
393
394
395 -- ===============================================================
396 -- Procedure name
397 -- LOAD_ROW
398 -- Purpose
399 -- load data to AMW_COMPLIANCE_ENVS_B and AMW_COMPLIANCE_ENVS_TL
400 -- ===============================================================
401 procedure LOAD_ROW (
402 X_COMPLIANCE_ENV_ID in NUMBER,
403 X_START_DATE in DATE,
404 X_END_DATE in DATE,
405 X_ENABLED_FLAG in VARCHAR2,
406 X_SECURITY_GROUP_ID in NUMBER,
407 X_OBJECT_VERSION_NUMBER in NUMBER,
408 X_ATTRIBUTE_CATEGORY in VARCHAR2,
409 X_ATTRIBUTE1 in VARCHAR2,
410 X_ATTRIBUTE2 in VARCHAR2,
411 X_ATTRIBUTE3 in VARCHAR2,
412 X_ATTRIBUTE4 in VARCHAR2,
413 X_ATTRIBUTE5 in VARCHAR2,
414 X_ATTRIBUTE6 in VARCHAR2,
415 X_ATTRIBUTE7 in VARCHAR2,
416 X_ATTRIBUTE8 in VARCHAR2,
417 X_ATTRIBUTE9 in VARCHAR2,
418 X_ATTRIBUTE10 in VARCHAR2,
419 X_ATTRIBUTE11 in VARCHAR2,
420 X_ATTRIBUTE12 in VARCHAR2,
421 X_ATTRIBUTE13 in VARCHAR2,
422 X_ATTRIBUTE14 in VARCHAR2,
423 X_ATTRIBUTE15 in VARCHAR2,
424 X_COMPLIANCE_ENV_NAME in VARCHAR2,
425 X_COMPLIANCE_ENV_ALIAS in VARCHAR2,
426 X_COMPLIANCE_ENV_DESCRIPTION in VARCHAR2,
427 X_OWNER in VARCHAR2
428 ) IS
429 l_user_id number;
430 l_compliance_env_id number;
431 l_row_id varchar2(32767);
432
433 BEGIN
434 -- Translate owner to file_last_updated_by
435 l_user_id := fnd_load_util.owner_id(X_OWNER);
436
437 select COMPLIANCE_ENV_ID into l_compliance_env_id
438 from AMW_COMPLIANCE_ENVS_B
439 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID;
440
441 AMW_COMPLIANCE_ENVS_PKG.UPDATE_ROW (
442 X_COMPLIANCE_ENV_ID => l_compliance_env_id,
443 X_START_DATE => X_START_DATE,
444 X_END_DATE => X_END_DATE,
445 X_ENABLED_FLAG => X_ENABLED_FLAG,
446 X_LAST_UPDATED_BY => l_user_id,
447 X_LAST_UPDATE_DATE => sysdate,
448 X_LAST_UPDATE_LOGIN => 0,
449 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
450 X_OBJECT_VERSION_NUMBER => 1,
451 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
452 X_ATTRIBUTE1 => X_ATTRIBUTE1,
453 X_ATTRIBUTE2 => X_ATTRIBUTE2,
454 X_ATTRIBUTE3 => X_ATTRIBUTE3,
455 X_ATTRIBUTE4 => X_ATTRIBUTE4,
456 X_ATTRIBUTE5 => X_ATTRIBUTE5,
457 X_ATTRIBUTE6 => X_ATTRIBUTE6,
458 X_ATTRIBUTE7 => X_ATTRIBUTE7,
459 X_ATTRIBUTE8 => X_ATTRIBUTE8,
460 X_ATTRIBUTE9 => X_ATTRIBUTE9,
461 X_ATTRIBUTE10 => X_ATTRIBUTE10,
462 X_ATTRIBUTE11 => X_ATTRIBUTE11,
463 X_ATTRIBUTE12 => X_ATTRIBUTE12,
464 X_ATTRIBUTE13 => X_ATTRIBUTE13,
465 X_ATTRIBUTE14 => X_ATTRIBUTE14,
466 X_ATTRIBUTE15 => X_ATTRIBUTE15,
467 X_COMPLIANCE_ENV_NAME => X_COMPLIANCE_ENV_NAME,
468 X_COMPLIANCE_ENV_ALIAS => X_COMPLIANCE_ENV_ALIAS,
469 X_COMPLIANCE_ENV_DESCRIPTION => X_COMPLIANCE_ENV_DESCRIPTION);
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 -- 07.29.2004 tsho: should use passed-in x_compliance_env_id
474 /*
475 select AMW_COMPLIANCE_ENV_S.nextval into l_compliance_env_id
476 from dual;
477 */
478
479 AMW_COMPLIANCE_ENVS_PKG.INSERT_ROW (
480 X_ROWID => l_row_id,
481 X_COMPLIANCE_ENV_ID => X_COMPLIANCE_ENV_ID,
482 X_START_DATE => X_START_DATE,
483 X_END_DATE => X_END_DATE,
484 X_ENABLED_FLAG => X_ENABLED_FLAG,
485 X_LAST_UPDATED_BY => l_user_id,
486 X_LAST_UPDATE_DATE => sysdate,
487 X_CREATED_BY => l_user_id,
488 X_CREATION_DATE => sysdate,
489 X_LAST_UPDATE_LOGIN => 0,
490 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
491 X_OBJECT_VERSION_NUMBER => 1,
492 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
493 X_ATTRIBUTE1 => X_ATTRIBUTE1,
494 X_ATTRIBUTE2 => X_ATTRIBUTE2,
495 X_ATTRIBUTE3 => X_ATTRIBUTE3,
496 X_ATTRIBUTE4 => X_ATTRIBUTE4,
497 X_ATTRIBUTE5 => X_ATTRIBUTE5,
498 X_ATTRIBUTE6 => X_ATTRIBUTE6,
499 X_ATTRIBUTE7 => X_ATTRIBUTE7,
500 X_ATTRIBUTE8 => X_ATTRIBUTE8,
501 X_ATTRIBUTE9 => X_ATTRIBUTE9,
502 X_ATTRIBUTE10 => X_ATTRIBUTE10,
503 X_ATTRIBUTE11 => X_ATTRIBUTE11,
504 X_ATTRIBUTE12 => X_ATTRIBUTE12,
505 X_ATTRIBUTE13 => X_ATTRIBUTE13,
506 X_ATTRIBUTE14 => X_ATTRIBUTE14,
507 X_ATTRIBUTE15 => X_ATTRIBUTE15,
508 X_COMPLIANCE_ENV_NAME => X_COMPLIANCE_ENV_NAME,
509 X_COMPLIANCE_ENV_ALIAS => X_COMPLIANCE_ENV_ALIAS,
510 X_COMPLIANCE_ENV_DESCRIPTION => X_COMPLIANCE_ENV_DESCRIPTION);
511
512 END LOAD_ROW;
513
514
515
516 -- ===============================================================
517 -- Procedure name
518 -- DELETE_ROW
519 -- Purpose
520 --
521 -- ===============================================================
522 procedure DELETE_ROW (
523 X_COMPLIANCE_ENV_ID in NUMBER
524 ) is
525 begin
526 delete from AMW_COMPLIANCE_ENVS_TL
527 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID;
528
529 if (sql%notfound) then
530 raise no_data_found;
531 end if;
532
533 delete from AMW_COMPLIANCE_ENVS_B
534 where COMPLIANCE_ENV_ID = X_COMPLIANCE_ENV_ID;
535
536 if (sql%notfound) then
537 raise no_data_found;
538 end if;
539 end DELETE_ROW;
540
541
542
543 -- ===============================================================
544 -- Procedure name
545 -- ADD_LANGUAGE
546 -- Purpose
547 --
548 -- ===============================================================
549 procedure ADD_LANGUAGE
550 is
551 begin
552 delete from AMW_COMPLIANCE_ENVS_TL T
553 where not exists
554 (select NULL
555 from AMW_COMPLIANCE_ENVS_B B
556 where B.COMPLIANCE_ENV_ID = T.COMPLIANCE_ENV_ID
557 );
558
559 update AMW_COMPLIANCE_ENVS_TL T set (
560 NAME,
561 ALIAS,
562 DESCRIPTION
563 ) = (select
564 B.NAME,
565 B.ALIAS,
566 B.DESCRIPTION
567 from AMW_COMPLIANCE_ENVS_TL B
568 where B.COMPLIANCE_ENV_ID = T.COMPLIANCE_ENV_ID
569 and B.LANGUAGE = T.SOURCE_LANG)
570 where (
571 T.COMPLIANCE_ENV_ID,
572 T.LANGUAGE
573 ) in (select
574 SUBT.COMPLIANCE_ENV_ID,
575 SUBT.LANGUAGE
576 from AMW_COMPLIANCE_ENVS_TL SUBB, AMW_COMPLIANCE_ENVS_TL SUBT
577 where SUBB.COMPLIANCE_ENV_ID = SUBT.COMPLIANCE_ENV_ID
578 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
579 and (SUBB.NAME <> SUBT.NAME
580 or SUBB.ALIAS <> SUBT.ALIAS
581 or (SUBB.ALIAS is null and SUBT.ALIAS is not null)
582 or (SUBB.ALIAS is not null and SUBT.ALIAS is null)
583 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
584 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
585 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
586 ));
587
588 insert into AMW_COMPLIANCE_ENVS_TL (
589 LAST_UPDATE_LOGIN,
590 COMPLIANCE_ENV_ID,
591 NAME,
592 ALIAS,
593 DESCRIPTION,
594 LAST_UPDATE_DATE,
595 LAST_UPDATED_BY,
596 CREATION_DATE,
597 CREATED_BY,
598 SECURITY_GROUP_ID,
599 LANGUAGE,
600 SOURCE_LANG
601 ) select
602 B.LAST_UPDATE_LOGIN,
603 B.COMPLIANCE_ENV_ID,
604 B.NAME,
605 B.ALIAS,
606 B.DESCRIPTION,
607 B.LAST_UPDATE_DATE,
608 B.LAST_UPDATED_BY,
609 B.CREATION_DATE,
610 B.CREATED_BY,
611 B.SECURITY_GROUP_ID,
612 L.LANGUAGE_CODE,
613 B.SOURCE_LANG
614 from AMW_COMPLIANCE_ENVS_TL B, FND_LANGUAGES L
615 where L.INSTALLED_FLAG in ('I', 'B')
616 and B.LANGUAGE = userenv('LANG')
617 and not exists
618 (select NULL
619 from AMW_COMPLIANCE_ENVS_TL T
620 where T.COMPLIANCE_ENV_ID = B.COMPLIANCE_ENV_ID
621 and T.LANGUAGE = L.LANGUAGE_CODE);
622 end ADD_LANGUAGE;
623
624 /**05.31.2006 npanandi: bug 5259681 fix, added translate row***/
625 procedure TRANSLATE_ROW(
626 X_COMPLIANCE_ENV_ID in NUMBER,
627 X_COMPLIANCE_ENV_NAME in VARCHAR2,
628 X_COMPLIANCE_ENV_DESCRIPTION in VARCHAR2,
629 X_COMPLIANCE_ENV_ALIAS in VARCHAR2,
630 X_LAST_UPDATE_DATE in VARCHAR2,
631 X_OWNER in VARCHAR2,
632 X_CUSTOM_MODE in VARCHAR2) is
633
634 f_luby number; -- entity owner in file
635 f_ludate date; -- entity update date in file
636 db_luby number; -- entity owner in db
637 db_ludate date; -- entity update date in db
638 begin
639 -- Translate owner to file_last_updated_by
640 f_luby := fnd_load_util.owner_id(X_OWNER);
641
642 -- Translate char last_update_date to date
643 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
644
645 select last_updated_by, last_update_date
646 into db_luby, db_ludate
647 from AMW_COMPLIANCE_ENVS_TL
648 where compliance_env_id = X_COMPLIANCE_ENV_ID
649 and language = userenv('LANG');
650
651 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) then
652 update AMW_COMPLIANCE_ENVS_TL
653 set name = X_COMPLIANCE_ENV_NAME,
654 description = nvl(X_COMPLIANCE_ENV_DESCRIPTION, description),
655 alias = nvl(X_COMPLIANCE_ENV_ALIAS, alias),
656 source_lang = userenv('LANG'),
657 last_update_date = f_ludate,
658 last_updated_by = f_luby,
659 last_update_login = 0
660 where compliance_env_id = X_COMPLIANCE_ENV_ID
661 and userenv('LANG') in (language, source_lang);
662 end if;
663
664 end TRANSLATE_ROW;
665 /**05.31.2006 npanandi: bug 5259681 fix ends***/
666
667
668 -- ----------------------------------------------------------------------
669 end AMW_COMPLIANCE_ENVS_PKG;