1 package body AMW_CONTROLS_PKG as
2 /* $Header: amwcnthb.pls 120.0 2005/05/31 19:34:11 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_CONTROL_REV_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
8 X_LATEST_REVISION_FLAG in VARCHAR2,
9 X_REQUESTOR_ID in NUMBER,
10 X_CONTROL_ID in NUMBER,
11 X_APPROVAL_STATUS in VARCHAR2,
12 X_AUTOMATION_TYPE in VARCHAR2,
13 X_APPLICATION_ID in NUMBER,
14 X_JOB_ID in NUMBER,
15 X_CREATED_BY_MODULE in VARCHAR2,
16 X_ATTRIBUTE14 in VARCHAR2,
17 X_ATTRIBUTE13 in VARCHAR2,
18 X_ATTRIBUTE15 in VARCHAR2,
19 X_SECURITY_GROUP_ID in NUMBER,
20 X_CONTROL_LOCATION in VARCHAR2,
21 X_REV_NUM in NUMBER,
22 X_APPROVAL_DATE in DATE,
23 X_CONTROL_TYPE in VARCHAR2,
24 X_CATEGORY in VARCHAR2,
25 X_SOURCE in VARCHAR2,
26 X_ATTRIBUTE_CATEGORY in VARCHAR2,
27 X_ATTRIBUTE1 in VARCHAR2,
28 X_ATTRIBUTE2 in VARCHAR2,
29 X_ATTRIBUTE3 in VARCHAR2,
30 X_ATTRIBUTE4 in VARCHAR2,
31 X_ATTRIBUTE5 in VARCHAR2,
32 X_ATTRIBUTE6 in VARCHAR2,
33 X_ATTRIBUTE7 in VARCHAR2,
34 X_ATTRIBUTE8 in VARCHAR2,
35 X_ATTRIBUTE9 in VARCHAR2,
36 X_ATTRIBUTE10 in VARCHAR2,
37 X_ATTRIBUTE11 in VARCHAR2,
38 X_ATTRIBUTE12 in VARCHAR2,
39 X_END_DATE in DATE,
40 X_CURR_APPROVED_FLAG in VARCHAR2,
41 X_NAME in VARCHAR2,
42 X_DESCRIPTION in VARCHAR2,
43 X_PHYSICAL_EVIDENCE in VARCHAR2,
44 X_CREATION_DATE in DATE,
45 X_CREATED_BY in NUMBER,
46 X_LAST_UPDATE_DATE in DATE,
47 X_LAST_UPDATED_BY in NUMBER,
48 X_LAST_UPDATE_LOGIN in NUMBER,
49 x_preventive_control in varchar2 := null,
50 x_detective_control in varchar2 := null,
51 x_disclosure_control in varchar2 := null,
52 x_key_mitigating in varchar2 := null,
53 x_verification_source in varchar2 := null,
54 x_verification_source_name in varchar2 := null,
55 x_verification_instruction in varchar2 := null,
56 x_uom_code in varchar2 := null,
57 x_control_frequency in number := NULL,
58 ---NPANANDI 12.10.2004: ADDED BELOW FOR CTRL CLASSIFICATION
59 x_classification IN NUMBER DEFAULT NULL
60 ) is
61 cursor C is select ROWID from AMW_CONTROLS_B
62 where CONTROL_REV_ID = X_CONTROL_REV_ID
63 ;
64 begin
65 insert into AMW_CONTROLS_B (
66 OBJECT_VERSION_NUMBER,
67 CONTROL_REV_ID,
68 ORIG_SYSTEM_REFERENCE,
69 LATEST_REVISION_FLAG,
70 REQUESTOR_ID,
71 CONTROL_ID,
72 APPROVAL_STATUS,
73 AUTOMATION_TYPE,
74 APPLICATION_ID,
75 JOB_ID,
76 CREATED_BY_MODULE,
77 ATTRIBUTE14,
78 ATTRIBUTE13,
79 ATTRIBUTE15,
80 SECURITY_GROUP_ID,
81 CONTROL_LOCATION,
82 REV_NUM,
83 APPROVAL_DATE,
84 CONTROL_TYPE,
85 CATEGORY,
86 SOURCE,
87 ATTRIBUTE_CATEGORY,
88 ATTRIBUTE1,
89 ATTRIBUTE2,
90 ATTRIBUTE3,
91 ATTRIBUTE4,
92 ATTRIBUTE5,
93 ATTRIBUTE6,
94 ATTRIBUTE7,
95 ATTRIBUTE8,
96 ATTRIBUTE9,
97 ATTRIBUTE10,
98 ATTRIBUTE11,
99 ATTRIBUTE12,
100 END_DATE,
101 CURR_APPROVED_FLAG,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN,
107 preventive_control,
108 detective_control,
109 disclosure_control,
110 key_mitigating,
111 verification_source,
112 uom_code,
113 control_frequency,
114 ---npanandi 12.10.2004: added below for Ctrl Classification
115 classification
116 ) values (
117 X_OBJECT_VERSION_NUMBER,
118 X_CONTROL_REV_ID,
119 X_ORIG_SYSTEM_REFERENCE,
120 X_LATEST_REVISION_FLAG,
121 X_REQUESTOR_ID,
122 X_CONTROL_ID,
123 X_APPROVAL_STATUS,
124 X_AUTOMATION_TYPE,
125 X_APPLICATION_ID,
126 X_JOB_ID,
127 X_CREATED_BY_MODULE,
128 X_ATTRIBUTE14,
129 X_ATTRIBUTE13,
130 X_ATTRIBUTE15,
131 X_SECURITY_GROUP_ID,
132 X_CONTROL_LOCATION,
133 X_REV_NUM,
134 X_APPROVAL_DATE,
135 X_CONTROL_TYPE,
136 X_CATEGORY,
137 X_SOURCE,
138 X_ATTRIBUTE_CATEGORY,
139 X_ATTRIBUTE1,
140 X_ATTRIBUTE2,
141 X_ATTRIBUTE3,
142 X_ATTRIBUTE4,
143 X_ATTRIBUTE5,
144 X_ATTRIBUTE6,
145 X_ATTRIBUTE7,
146 X_ATTRIBUTE8,
147 X_ATTRIBUTE9,
148 X_ATTRIBUTE10,
149 X_ATTRIBUTE11,
150 X_ATTRIBUTE12,
151 X_END_DATE,
152 X_CURR_APPROVED_FLAG,
153 X_CREATION_DATE,
154 X_CREATED_BY,
155 X_LAST_UPDATE_DATE,
156 X_LAST_UPDATED_BY,
157 X_LAST_UPDATE_LOGIN,
158 x_preventive_control,
159 x_detective_control,
160 x_disclosure_control,
161 x_key_mitigating,
162 x_verification_source,
163 X_UOM_CODE,
164 X_CONTROL_FREQUENCY,
165 ---npanandi 12.10.2004: added below for Ctrl Classification
166 x_classification
167 );
168
169 insert into AMW_CONTROLS_TL (
170 CONTROL_REV_ID,
171 NAME,
172 DESCRIPTION,
173 PHYSICAL_EVIDENCE,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 CREATION_DATE,
177 CREATED_BY,
178 LAST_UPDATE_LOGIN,
179 SECURITY_GROUP_ID,
180 OBJECT_VERSION_NUMBER,
181 LANGUAGE,
182 SOURCE_LANG,
183 verification_source_name,
184 verification_instruction
185 ) select
186 X_CONTROL_REV_ID,
187 X_NAME,
188 X_DESCRIPTION,
189 X_PHYSICAL_EVIDENCE,
190 X_LAST_UPDATE_DATE,
191 X_LAST_UPDATED_BY,
192 X_CREATION_DATE,
193 X_CREATED_BY,
194 X_LAST_UPDATE_LOGIN,
195 X_SECURITY_GROUP_ID,
196 X_OBJECT_VERSION_NUMBER,
197 L.LANGUAGE_CODE,
198 userenv('LANG'),
199 x_verification_source_name,
200 x_verification_instruction
201 from FND_LANGUAGES L
202 where L.INSTALLED_FLAG in ('I', 'B')
203 and not exists
204 (select NULL
205 from AMW_CONTROLS_TL T
206 where T.CONTROL_REV_ID = X_CONTROL_REV_ID
207 and T.LANGUAGE = L.LANGUAGE_CODE);
208
209 open c;
210 fetch c into X_ROWID;
211 if (c%notfound) then
212 close c;
213 raise no_data_found;
214 end if;
215 close c;
216
217 end INSERT_ROW;
218
219 procedure LOCK_ROW (
220 X_CONTROL_REV_ID in NUMBER,
221 X_OBJECT_VERSION_NUMBER in NUMBER,
222 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
223 X_LATEST_REVISION_FLAG in VARCHAR2,
224 X_REQUESTOR_ID in NUMBER,
225 X_CONTROL_ID in NUMBER,
226 X_APPROVAL_STATUS in VARCHAR2,
227 X_AUTOMATION_TYPE in VARCHAR2,
228 X_APPLICATION_ID in NUMBER,
229 X_JOB_ID in NUMBER,
230 X_CREATED_BY_MODULE in VARCHAR2,
231 X_ATTRIBUTE14 in VARCHAR2,
232 X_ATTRIBUTE13 in VARCHAR2,
233 X_ATTRIBUTE15 in VARCHAR2,
234 X_SECURITY_GROUP_ID in NUMBER,
235 X_CONTROL_LOCATION in VARCHAR2,
236 X_REV_NUM in NUMBER,
237 X_APPROVAL_DATE in DATE,
238 X_CONTROL_TYPE in VARCHAR2,
239 X_CATEGORY in VARCHAR2,
240 X_SOURCE in VARCHAR2,
241 X_ATTRIBUTE_CATEGORY in VARCHAR2,
242 X_ATTRIBUTE1 in VARCHAR2,
243 X_ATTRIBUTE2 in VARCHAR2,
244 X_ATTRIBUTE3 in VARCHAR2,
245 X_ATTRIBUTE4 in VARCHAR2,
246 X_ATTRIBUTE5 in VARCHAR2,
247 X_ATTRIBUTE6 in VARCHAR2,
248 X_ATTRIBUTE7 in VARCHAR2,
249 X_ATTRIBUTE8 in VARCHAR2,
250 X_ATTRIBUTE9 in VARCHAR2,
251 X_ATTRIBUTE10 in VARCHAR2,
252 X_ATTRIBUTE11 in VARCHAR2,
253 X_ATTRIBUTE12 in VARCHAR2,
254 X_END_DATE in DATE,
255 X_CURR_APPROVED_FLAG in VARCHAR2,
256 X_NAME in VARCHAR2,
257 X_DESCRIPTION in VARCHAR2,
258 X_PHYSICAL_EVIDENCE in VARCHAR2,
259 x_preventive_control in varchar2,
260 x_detective_control in varchar2,
261 x_disclosure_control in varchar2,
262 x_key_mitigating in varchar2,
263 x_verification_source in varchar2,
264 x_verification_source_name in varchar2,
265 x_verification_instruction in varchar2,
266 x_uom_code in varchar2,
267 x_control_frequency in number,
268 ---npanandi 12.10.2004: added below for Ctrl Classification
269 x_classification in number
270 ) is
271 cursor c is select
272 OBJECT_VERSION_NUMBER,
273 ORIG_SYSTEM_REFERENCE,
274 LATEST_REVISION_FLAG,
275 REQUESTOR_ID,
276 CONTROL_ID,
277 APPROVAL_STATUS,
278 AUTOMATION_TYPE,
279 APPLICATION_ID,
280 JOB_ID,
281 CREATED_BY_MODULE,
282 ATTRIBUTE14,
283 ATTRIBUTE13,
284 ATTRIBUTE15,
285 SECURITY_GROUP_ID,
286 CONTROL_LOCATION,
287 REV_NUM,
288 APPROVAL_DATE,
289 CONTROL_TYPE,
290 CATEGORY,
291 SOURCE,
292 ATTRIBUTE_CATEGORY,
293 ATTRIBUTE1,
294 ATTRIBUTE2,
295 ATTRIBUTE3,
296 ATTRIBUTE4,
297 ATTRIBUTE5,
298 ATTRIBUTE6,
299 ATTRIBUTE7,
300 ATTRIBUTE8,
301 ATTRIBUTE9,
302 ATTRIBUTE10,
303 ATTRIBUTE11,
304 ATTRIBUTE12,
305 END_DATE,
306 CURR_APPROVED_FLAG,
307 preventive_control,
308 detective_control,
309 disclosure_control,
310 key_mitigating,
311 verification_source,
312 UOM_CODE,
313 CONTROL_FREQUENCY,
314 ---npanandi 12.10.2004: added below for Ctrl Classification
315 classification
316 from AMW_CONTROLS_B
317 where CONTROL_REV_ID = X_CONTROL_REV_ID
318 for update of CONTROL_REV_ID nowait;
319 recinfo c%rowtype;
320
321 cursor c1 is select
322 NAME,
323 DESCRIPTION,
324 PHYSICAL_EVIDENCE,
325 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG,
326 verification_source_name,
327 verification_instruction
328 from AMW_CONTROLS_TL
329 where CONTROL_REV_ID = X_CONTROL_REV_ID
330 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
331 for update of CONTROL_REV_ID nowait;
332 begin
333 open c;
334 fetch c into recinfo;
335 if (c%notfound) then
336 close c;
337 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
338 app_exception.raise_exception;
339 end if;
340 close c;
341 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
342 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
343 AND ((recinfo.ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE)
344 OR ((recinfo.ORIG_SYSTEM_REFERENCE is null) AND (X_ORIG_SYSTEM_REFERENCE is null)))
345 AND ((recinfo.LATEST_REVISION_FLAG = X_LATEST_REVISION_FLAG)
346 OR ((recinfo.LATEST_REVISION_FLAG is null) AND (X_LATEST_REVISION_FLAG is null)))
347 AND ((recinfo.REQUESTOR_ID = X_REQUESTOR_ID)
348 OR ((recinfo.REQUESTOR_ID is null) AND (X_REQUESTOR_ID is null)))
349 AND (recinfo.CONTROL_ID = X_CONTROL_ID)
350 AND ((recinfo.APPROVAL_STATUS = X_APPROVAL_STATUS)
351 OR ((recinfo.APPROVAL_STATUS is null) AND (X_APPROVAL_STATUS is null)))
352 AND ((recinfo.AUTOMATION_TYPE = X_AUTOMATION_TYPE)
353 OR ((recinfo.AUTOMATION_TYPE is null) AND (X_AUTOMATION_TYPE is null)))
354 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
355 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
356 AND ((recinfo.JOB_ID = X_JOB_ID)
357 OR ((recinfo.JOB_ID is null) AND (X_JOB_ID is null)))
358 AND ((recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE)
359 OR ((recinfo.CREATED_BY_MODULE is null) AND (X_CREATED_BY_MODULE is null)))
360 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
361 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
362 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
363 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
364 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
365 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
366 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
367 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
368 AND ((recinfo.CONTROL_LOCATION = X_CONTROL_LOCATION)
369 OR ((recinfo.CONTROL_LOCATION is null) AND (X_CONTROL_LOCATION is null)))
370 AND (recinfo.REV_NUM = X_REV_NUM)
371 AND ((recinfo.APPROVAL_DATE = X_APPROVAL_DATE)
372 OR ((recinfo.APPROVAL_DATE is null) AND (X_APPROVAL_DATE is null)))
373 AND ((recinfo.CONTROL_TYPE = X_CONTROL_TYPE)
374 OR ((recinfo.CONTROL_TYPE is null) AND (X_CONTROL_TYPE is null)))
375 AND ((recinfo.CATEGORY = X_CATEGORY)
376 OR ((recinfo.CATEGORY is null) AND (X_CATEGORY is null)))
377 AND ((recinfo.SOURCE = X_SOURCE)
378 OR ((recinfo.SOURCE is null) AND (X_SOURCE is null)))
379 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
380 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
381 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
382 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
383 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
384 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
385 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
386 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
387 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
388 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
389 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
390 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
391 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
392 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
393 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
394 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
395 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
396 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
397 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
398 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
399 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
400 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
401 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
402 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
403 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
404 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
405 AND ((recinfo.END_DATE = X_END_DATE)
406 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
407 AND ((recinfo.preventive_control = X_preventive_control)
408 OR ((recinfo.preventive_control is null) AND (X_preventive_control is null)))
409 AND ((recinfo.detective_control = X_detective_control)
410 OR ((recinfo.detective_control is null) AND (X_detective_control is null)))
411 AND ((recinfo.disclosure_control = X_disclosure_control)
412 OR ((recinfo.disclosure_control is null) AND (X_disclosure_control is null)))
413 AND ((recinfo.key_mitigating = X_key_mitigating)
414 OR ((recinfo.key_mitigating is null) AND (X_key_mitigating is null)))
415 AND ((recinfo.verification_source = X_verification_source)
416 OR ((recinfo.verification_source is null) AND (X_verification_source is null)))
417 AND ((recinfo.UOM_CODE = X_UOM_CODE)
418 OR ((recinfo.UOM_CODE is null) AND (X_UOM_CODE is null)))
419 AND ((recinfo.CONTROL_FREQUENCY = X_CONTROL_FREQUENCY)
420 OR ((recinfo.CONTROL_FREQUENCY is null) AND (X_CONTROL_FREQUENCY is null)))
421 ---npanandi 12.10.2004: added below AND clause for Ctrl Classification
422 AND ((recinfo.CLASSIFICATION = X_CLASSIFICATION)
423 OR ((recinfo.CLASSIFICATION is null) AND (X_CLASSIFICATION is null)))
424 AND ((recinfo.CURR_APPROVED_FLAG = X_CURR_APPROVED_FLAG)
425 OR ((recinfo.CURR_APPROVED_FLAG is null) AND (X_CURR_APPROVED_FLAG is null)))
426 ) then
427 null;
428 else
429 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
430 app_exception.raise_exception;
431 end if;
432
433 for tlinfo in c1 loop
434 if (tlinfo.BASELANG = 'Y') then
435 if ( (tlinfo.NAME = X_NAME)
436 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
437 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
438 AND ((tlinfo.PHYSICAL_EVIDENCE = X_PHYSICAL_EVIDENCE)
439 OR ((tlinfo.PHYSICAL_EVIDENCE is null) AND (X_PHYSICAL_EVIDENCE is null)))
440 AND ((tlinfo.verification_source_name = X_verification_source_name)
441 OR ((tlinfo.verification_source_name is null) AND (X_verification_source_name is null)))
442 AND ((tlinfo.verification_instruction = X_verification_instruction)
443 OR ((tlinfo.verification_instruction is null) AND (X_verification_instruction is null)))
444 ) then
445 null;
446 else
447 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
448 app_exception.raise_exception;
449 end if;
450 end if;
451 end loop;
452 return;
453 end LOCK_ROW;
454
455 procedure UPDATE_ROW (
456 X_CONTROL_REV_ID in NUMBER,
457 X_OBJECT_VERSION_NUMBER in NUMBER,
458 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
459 X_LATEST_REVISION_FLAG in VARCHAR2,
460 X_REQUESTOR_ID in NUMBER,
461 X_CONTROL_ID in NUMBER,
462 X_APPROVAL_STATUS in VARCHAR2,
463 X_AUTOMATION_TYPE in VARCHAR2,
464 X_APPLICATION_ID in NUMBER,
465 X_JOB_ID in NUMBER,
466 X_CREATED_BY_MODULE in VARCHAR2,
467 X_ATTRIBUTE14 in VARCHAR2,
468 X_ATTRIBUTE13 in VARCHAR2,
469 X_ATTRIBUTE15 in VARCHAR2,
470 X_SECURITY_GROUP_ID in NUMBER,
471 X_CONTROL_LOCATION in VARCHAR2,
472 X_REV_NUM in NUMBER,
473 X_APPROVAL_DATE in DATE,
474 X_CONTROL_TYPE in VARCHAR2,
475 X_CATEGORY in VARCHAR2,
476 X_SOURCE in VARCHAR2,
477 X_ATTRIBUTE_CATEGORY in VARCHAR2,
478 X_ATTRIBUTE1 in VARCHAR2,
479 X_ATTRIBUTE2 in VARCHAR2,
480 X_ATTRIBUTE3 in VARCHAR2,
481 X_ATTRIBUTE4 in VARCHAR2,
482 X_ATTRIBUTE5 in VARCHAR2,
483 X_ATTRIBUTE6 in VARCHAR2,
484 X_ATTRIBUTE7 in VARCHAR2,
485 X_ATTRIBUTE8 in VARCHAR2,
486 X_ATTRIBUTE9 in VARCHAR2,
487 X_ATTRIBUTE10 in VARCHAR2,
488 X_ATTRIBUTE11 in VARCHAR2,
489 X_ATTRIBUTE12 in VARCHAR2,
490 X_END_DATE in DATE,
491 X_CURR_APPROVED_FLAG in VARCHAR2,
492 X_NAME in VARCHAR2,
493 X_DESCRIPTION in VARCHAR2,
494 X_PHYSICAL_EVIDENCE in VARCHAR2,
495 X_LAST_UPDATE_DATE in DATE,
496 X_LAST_UPDATED_BY in NUMBER,
497 X_LAST_UPDATE_LOGIN in NUMBER,
498 x_preventive_control in varchar2 := null,
499 x_detective_control in varchar2 := null,
500 x_disclosure_control in varchar2 := null,
501 x_key_mitigating in varchar2 := null,
502 x_verification_source in varchar2 := null,
503 x_verification_source_name in varchar2 := null,
504 x_verification_instruction in varchar2 := null,
505 X_UOM_CODE IN VARCHAR2 := NULL,
506 X_CONTROL_FREQUENCY IN NUMBER := NULL,
507 ---npanandi 12.10.2004: added below AND clause for Ctrl Classification
508 X_classification IN NUMBER default null
509 ) is
510 begin
511 update AMW_CONTROLS_B set
512 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
513 ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE,
514 LATEST_REVISION_FLAG = X_LATEST_REVISION_FLAG,
515 REQUESTOR_ID = X_REQUESTOR_ID,
516 CONTROL_ID = X_CONTROL_ID,
517 APPROVAL_STATUS = X_APPROVAL_STATUS,
518 AUTOMATION_TYPE = X_AUTOMATION_TYPE,
519 APPLICATION_ID = X_APPLICATION_ID,
520 JOB_ID = X_JOB_ID,
521 CREATED_BY_MODULE = X_CREATED_BY_MODULE,
522 ATTRIBUTE14 = X_ATTRIBUTE14,
523 ATTRIBUTE13 = X_ATTRIBUTE13,
524 ATTRIBUTE15 = X_ATTRIBUTE15,
525 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
526 CONTROL_LOCATION = X_CONTROL_LOCATION,
527 REV_NUM = X_REV_NUM,
528 APPROVAL_DATE = X_APPROVAL_DATE,
529 CONTROL_TYPE = X_CONTROL_TYPE,
530 CATEGORY = X_CATEGORY,
531 SOURCE = X_SOURCE,
532 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
533 ATTRIBUTE1 = X_ATTRIBUTE1,
534 ATTRIBUTE2 = X_ATTRIBUTE2,
535 ATTRIBUTE3 = X_ATTRIBUTE3,
536 ATTRIBUTE4 = X_ATTRIBUTE4,
537 ATTRIBUTE5 = X_ATTRIBUTE5,
538 ATTRIBUTE6 = X_ATTRIBUTE6,
539 ATTRIBUTE7 = X_ATTRIBUTE7,
540 ATTRIBUTE8 = X_ATTRIBUTE8,
541 ATTRIBUTE9 = X_ATTRIBUTE9,
542 ATTRIBUTE10 = X_ATTRIBUTE10,
543 ATTRIBUTE11 = X_ATTRIBUTE11,
544 ATTRIBUTE12 = X_ATTRIBUTE12,
545 END_DATE = X_END_DATE,
546 CURR_APPROVED_FLAG = X_CURR_APPROVED_FLAG,
547 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
548 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
549 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
550 preventive_control = x_preventive_control,
551 detective_control = x_detective_control,
552 disclosure_control = x_disclosure_control,
556 CONTROL_FREQUENCY = X_CONTROL_FREQUENCY,
553 key_mitigating = x_key_mitigating,
554 verification_source = x_verification_source,
555 UOM_CODE = X_UOM_CODE,
557 ---npanandi 12.10.2004: added below for Ctrl Classification
558 classification = X_classification
559 where CONTROL_REV_ID = X_CONTROL_REV_ID;
560
561 if (sql%notfound) then
562 raise no_data_found;
563 end if;
564
565 update AMW_CONTROLS_TL set
566 NAME = X_NAME,
567 DESCRIPTION = X_DESCRIPTION,
568 PHYSICAL_EVIDENCE = X_PHYSICAL_EVIDENCE,
569 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
570 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
571 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
572 SOURCE_LANG = userenv('LANG'),
573 verification_source_name = x_verification_source_name,
574 verification_instruction = x_verification_instruction
575 where CONTROL_REV_ID = X_CONTROL_REV_ID
576 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
577
578 if (sql%notfound) then
579 raise no_data_found;
580 end if;
581 end UPDATE_ROW;
582
583 procedure DELETE_ROW (
584 X_CONTROL_REV_ID in NUMBER
585 ) is
586 begin
587 delete from AMW_CONTROLS_TL
588 where CONTROL_REV_ID = X_CONTROL_REV_ID;
589
590 if (sql%notfound) then
591 raise no_data_found;
592 end if;
593
594 delete from AMW_CONTROLS_B
595 where CONTROL_REV_ID = X_CONTROL_REV_ID;
596
597 if (sql%notfound) then
598 raise no_data_found;
599 end if;
600 end DELETE_ROW;
601
602 procedure ADD_LANGUAGE
603 is
604 begin
605 delete from AMW_CONTROLS_TL T
606 where not exists
607 (select NULL
608 from AMW_CONTROLS_B B
609 where B.CONTROL_REV_ID = T.CONTROL_REV_ID
610 );
611
612 update AMW_CONTROLS_TL T set (
613 NAME,
614 DESCRIPTION,
615 PHYSICAL_EVIDENCE,
616 verification_source_name,
617 verification_instruction
618 ) = (select
619 B.NAME,
620 B.DESCRIPTION,
621 B.PHYSICAL_EVIDENCE,
622 B.verification_source_name,
623 B.verification_instruction
624 from AMW_CONTROLS_TL B
625 where B.CONTROL_REV_ID = T.CONTROL_REV_ID
626 and B.LANGUAGE = T.SOURCE_LANG)
627 where (
628 T.CONTROL_REV_ID,
629 T.LANGUAGE
630 ) in (select
631 SUBT.CONTROL_REV_ID,
632 SUBT.LANGUAGE
633 from AMW_CONTROLS_TL SUBB, AMW_CONTROLS_TL SUBT
634 where SUBB.CONTROL_REV_ID = SUBT.CONTROL_REV_ID
635 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
636 and (SUBB.NAME <> SUBT.NAME
637 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
638 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
639 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
640 or SUBB.PHYSICAL_EVIDENCE <> SUBT.PHYSICAL_EVIDENCE
641 or (SUBB.PHYSICAL_EVIDENCE is null and SUBT.PHYSICAL_EVIDENCE is not null)
642 or (SUBB.PHYSICAL_EVIDENCE is not null and SUBT.PHYSICAL_EVIDENCE is null)
643 or SUBB.verification_source_name <> SUBT.verification_source_name
644 or (SUBB.verification_source_name is null and SUBT.verification_source_name is not null)
645 or (SUBB.verification_source_name is not null and SUBT.verification_source_name is null)
646 or SUBB.verification_instruction <> SUBT.verification_instruction
647 or (SUBB.verification_instruction is null and SUBT.verification_instruction is not null)
648 or (SUBB.verification_instruction is not null and SUBT.verification_instruction is null)
649 ));
650
651 insert into AMW_CONTROLS_TL (
652 CONTROL_REV_ID,
653 NAME,
654 DESCRIPTION,
655 PHYSICAL_EVIDENCE,
656 LAST_UPDATE_DATE,
657 LAST_UPDATED_BY,
658 CREATION_DATE,
659 CREATED_BY,
660 LAST_UPDATE_LOGIN,
661 SECURITY_GROUP_ID,
662 OBJECT_VERSION_NUMBER,
663 LANGUAGE,
664 SOURCE_LANG,
665 verification_source_name,
666 verification_instruction
667 ) select
668 B.CONTROL_REV_ID,
669 B.NAME,
670 B.DESCRIPTION,
671 B.PHYSICAL_EVIDENCE,
672 B.LAST_UPDATE_DATE,
673 B.LAST_UPDATED_BY,
674 B.CREATION_DATE,
675 B.CREATED_BY,
676 B.LAST_UPDATE_LOGIN,
677 B.SECURITY_GROUP_ID,
678 B.OBJECT_VERSION_NUMBER,
679 L.LANGUAGE_CODE,
680 B.SOURCE_LANG,
681 B.verification_source_name,
682 B.verification_instruction
683 from AMW_CONTROLS_TL B, FND_LANGUAGES L
684 where L.INSTALLED_FLAG in ('I', 'B')
685 and B.LANGUAGE = userenv('LANG')
686 and not exists
687 (select NULL
688 from AMW_CONTROLS_TL T
689 where T.CONTROL_REV_ID = B.CONTROL_REV_ID
690 and T.LANGUAGE = L.LANGUAGE_CODE);
691 end ADD_LANGUAGE;
692
693 end AMW_CONTROLS_PKG;
[Home] [Help]