[Home] [Help]
PACKAGE BODY: APPS.AMW_PROCESS_NAMES_PKG
Source
1 package body AMW_PROCESS_NAMES_PKG as
2 /* $Header: amwprnmb.pls 120.1 2005/06/28 14:26:55 appldev noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_PROCESS_REV_ID in NUMBER,
7 X_CLASSIFICATION in NUMBER,
8 X_PROCESS_CODE in VARCHAR2,
9 X_REVISION_NUMBER in NUMBER,
10 X_START_DATE in DATE,
11 X_APPROVAL_DATE in DATE,
12 X_APPROVAL_END_DATE in DATE,
13 X_END_DATE in DATE,
14 X_DELETION_DATE in DATE,
15 X_PROCESS_TYPE in VARCHAR2,
16 X_CONTROL_ACTIVITY_TYPE in VARCHAR2,
17 X_RISK_COUNT_LATEST in NUMBER,
18 X_CONTROL_COUNT_LATEST in NUMBER,
19 X_STANDARD_VARIATION in NUMBER,
20 X_SIGNIFICANT_PROCESS_FLAG in VARCHAR2,
21 X_STANDARD_PROCESS_FLAG in VARCHAR2,
22 X_APPROVAL_STATUS in VARCHAR2,
23 X_CERTIFICATION_STATUS in VARCHAR2,
24 X_PROCESS_CATEGORY in VARCHAR2,
25 X_PROCESS_OWNER_ID in NUMBER,
26 X_PROCESS_ID in NUMBER,
27 X_ITEM_TYPE in VARCHAR2,
28 X_NAME in VARCHAR2,
29 X_CREATED_FROM in VARCHAR2,
30 X_REQUEST_ID in NUMBER,
31 X_ATTRIBUTE_CATEGORY in VARCHAR2,
32 X_ATTRIBUTE1 in VARCHAR2,
33 X_ATTRIBUTE2 in VARCHAR2,
34 X_ATTRIBUTE3 in VARCHAR2,
35 X_ATTRIBUTE4 in VARCHAR2,
36 X_ATTRIBUTE5 in VARCHAR2,
37 X_ATTRIBUTE6 in VARCHAR2,
38 X_ATTRIBUTE7 in VARCHAR2,
39 X_ATTRIBUTE8 in VARCHAR2,
40 X_ATTRIBUTE9 in VARCHAR2,
41 X_ATTRIBUTE10 in VARCHAR2,
42 X_ATTRIBUTE11 in VARCHAR2,
43 X_ATTRIBUTE12 in VARCHAR2,
44 X_ATTRIBUTE13 in VARCHAR2,
45 X_ATTRIBUTE14 in VARCHAR2,
46 X_ATTRIBUTE15 in VARCHAR2,
47 X_OBJECT_VERSION_NUMBER in NUMBER,
48 X_CONTROL_COUNT in NUMBER,
49 X_RISK_COUNT in NUMBER,
50 X_ORG_COUNT in NUMBER,
51 X_FINANCE_OWNER_ID in NUMBER,
52 X_APPLICATION_OWNER_ID in NUMBER,
53 X_DISPLAY_NAME in VARCHAR2,
54 X_DESCRIPTION in VARCHAR2,
55 X_CREATION_DATE in DATE,
56 X_CREATED_BY in NUMBER,
57 X_LAST_UPDATE_DATE in DATE,
58 X_LAST_UPDATED_BY in NUMBER,
59 X_LAST_UPDATE_LOGIN in NUMBER
60 ) is
61 cursor C is select ROWID from AMW_PROCESS
62 where PROCESS_REV_ID = X_PROCESS_REV_ID
63 ;
64 begin
65 insert into AMW_PROCESS (
66 CLASSIFICATION,
67 PROCESS_CODE,
68 REVISION_NUMBER,
69 START_DATE,
70 APPROVAL_DATE,
71 APPROVAL_END_DATE,
72 END_DATE,
73 DELETION_DATE,
74 PROCESS_TYPE,
75 CONTROL_ACTIVITY_TYPE,
76 RISK_COUNT_LATEST,
77 CONTROL_COUNT_LATEST,
78 STANDARD_VARIATION,
79 PROCESS_REV_ID,
80 SIGNIFICANT_PROCESS_FLAG,
81 STANDARD_PROCESS_FLAG,
82 APPROVAL_STATUS,
83 CERTIFICATION_STATUS,
84 PROCESS_CATEGORY,
85 PROCESS_OWNER_ID,
86 PROCESS_ID,
87 ITEM_TYPE,
88 NAME,
89 CREATED_FROM,
90 REQUEST_ID,
91 ATTRIBUTE_CATEGORY,
92 ATTRIBUTE1,
93 ATTRIBUTE2,
94 ATTRIBUTE3,
95 ATTRIBUTE4,
96 ATTRIBUTE5,
97 ATTRIBUTE6,
98 ATTRIBUTE7,
99 ATTRIBUTE8,
100 ATTRIBUTE9,
101 ATTRIBUTE10,
102 ATTRIBUTE11,
103 ATTRIBUTE12,
104 ATTRIBUTE13,
105 ATTRIBUTE14,
106 ATTRIBUTE15,
107 OBJECT_VERSION_NUMBER,
108 CONTROL_COUNT,
109 RISK_COUNT,
110 ORG_COUNT,
111 FINANCE_OWNER_ID,
112 APPLICATION_OWNER_ID,
113 CREATION_DATE,
114 CREATED_BY,
115 LAST_UPDATE_DATE,
116 LAST_UPDATED_BY,
117 LAST_UPDATE_LOGIN
118 ) values (
119 X_CLASSIFICATION,
120 X_PROCESS_CODE,
121 X_REVISION_NUMBER,
122 X_START_DATE,
123 X_APPROVAL_DATE,
124 X_APPROVAL_END_DATE,
125 X_END_DATE,
126 X_DELETION_DATE,
127 X_PROCESS_TYPE,
128 X_CONTROL_ACTIVITY_TYPE,
129 X_RISK_COUNT_LATEST,
130 X_CONTROL_COUNT_LATEST,
131 X_STANDARD_VARIATION,
132 X_PROCESS_REV_ID,
133 X_SIGNIFICANT_PROCESS_FLAG,
134 X_STANDARD_PROCESS_FLAG,
135 X_APPROVAL_STATUS,
136 X_CERTIFICATION_STATUS,
137 X_PROCESS_CATEGORY,
138 X_PROCESS_OWNER_ID,
139 X_PROCESS_ID,
140 X_ITEM_TYPE,
141 X_NAME,
142 X_CREATED_FROM,
143 X_REQUEST_ID,
144 X_ATTRIBUTE_CATEGORY,
145 X_ATTRIBUTE1,
146 X_ATTRIBUTE2,
147 X_ATTRIBUTE3,
148 X_ATTRIBUTE4,
149 X_ATTRIBUTE5,
150 X_ATTRIBUTE6,
151 X_ATTRIBUTE7,
152 X_ATTRIBUTE8,
153 X_ATTRIBUTE9,
154 X_ATTRIBUTE10,
155 X_ATTRIBUTE11,
156 X_ATTRIBUTE12,
157 X_ATTRIBUTE13,
158 X_ATTRIBUTE14,
159 X_ATTRIBUTE15,
160 X_OBJECT_VERSION_NUMBER,
161 X_CONTROL_COUNT,
162 X_RISK_COUNT,
163 X_ORG_COUNT,
164 X_FINANCE_OWNER_ID,
165 X_APPLICATION_OWNER_ID,
166 X_CREATION_DATE,
167 X_CREATED_BY,
168 X_LAST_UPDATE_DATE,
169 X_LAST_UPDATED_BY,
170 X_LAST_UPDATE_LOGIN
171 );
172
173 insert into AMW_PROCESS_NAMES_TL (
174 PROCESS_ID,
175 REVISION_NUMBER,
176 DISPLAY_NAME,
177 DESCRIPTION,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 LAST_UPDATE_LOGIN,
181 CREATION_DATE,
182 CREATED_BY,
183 OBJECT_VERSION_NUMBER,
184 PROCESS_REV_ID,
185 LANGUAGE,
186 SOURCE_LANG
187 ) select
188 X_PROCESS_ID,
189 X_REVISION_NUMBER,
190 X_DISPLAY_NAME,
191 X_DESCRIPTION,
192 X_LAST_UPDATE_DATE,
193 X_LAST_UPDATED_BY,
194 X_LAST_UPDATE_LOGIN,
195 X_CREATION_DATE,
196 X_CREATED_BY,
197 X_OBJECT_VERSION_NUMBER,
198 X_PROCESS_REV_ID,
199 L.LANGUAGE_CODE,
200 userenv('LANG')
201 from FND_LANGUAGES L
202 where L.INSTALLED_FLAG in ('I', 'B')
203 and not exists
204 (select NULL
205 from AMW_PROCESS_NAMES_TL T
206 where T.PROCESS_REV_ID = X_PROCESS_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_PROCESS_REV_ID in NUMBER,
221 X_CLASSIFICATION in NUMBER,
222 X_PROCESS_CODE in VARCHAR2,
223 X_REVISION_NUMBER in NUMBER,
224 X_START_DATE in DATE,
225 X_APPROVAL_DATE in DATE,
226 X_APPROVAL_END_DATE in DATE,
227 X_END_DATE in DATE,
228 X_DELETION_DATE in DATE,
229 X_PROCESS_TYPE in VARCHAR2,
233 X_STANDARD_VARIATION in NUMBER,
230 X_CONTROL_ACTIVITY_TYPE in VARCHAR2,
231 X_RISK_COUNT_LATEST in NUMBER,
232 X_CONTROL_COUNT_LATEST in NUMBER,
234 X_SIGNIFICANT_PROCESS_FLAG in VARCHAR2,
235 X_STANDARD_PROCESS_FLAG in VARCHAR2,
236 X_APPROVAL_STATUS in VARCHAR2,
237 X_CERTIFICATION_STATUS in VARCHAR2,
238 X_PROCESS_CATEGORY in VARCHAR2,
239 X_PROCESS_OWNER_ID in NUMBER,
240 X_PROCESS_ID in NUMBER,
241 X_ITEM_TYPE in VARCHAR2,
242 X_NAME in VARCHAR2,
243 X_CREATED_FROM in VARCHAR2,
244 X_REQUEST_ID in NUMBER,
245 X_ATTRIBUTE_CATEGORY in VARCHAR2,
246 X_ATTRIBUTE1 in VARCHAR2,
247 X_ATTRIBUTE2 in VARCHAR2,
248 X_ATTRIBUTE3 in VARCHAR2,
249 X_ATTRIBUTE4 in VARCHAR2,
250 X_ATTRIBUTE5 in VARCHAR2,
251 X_ATTRIBUTE6 in VARCHAR2,
252 X_ATTRIBUTE7 in VARCHAR2,
253 X_ATTRIBUTE8 in VARCHAR2,
254 X_ATTRIBUTE9 in VARCHAR2,
255 X_ATTRIBUTE10 in VARCHAR2,
256 X_ATTRIBUTE11 in VARCHAR2,
257 X_ATTRIBUTE12 in VARCHAR2,
258 X_ATTRIBUTE13 in VARCHAR2,
259 X_ATTRIBUTE14 in VARCHAR2,
260 X_ATTRIBUTE15 in VARCHAR2,
261 X_OBJECT_VERSION_NUMBER in NUMBER,
262 X_CONTROL_COUNT in NUMBER,
263 X_RISK_COUNT in NUMBER,
264 X_ORG_COUNT in NUMBER,
265 X_FINANCE_OWNER_ID in NUMBER,
266 X_APPLICATION_OWNER_ID in NUMBER,
267 X_DISPLAY_NAME in VARCHAR2,
268 X_DESCRIPTION in VARCHAR2
269 ) is
270 cursor c is select
271 CLASSIFICATION,
272 PROCESS_CODE,
273 REVISION_NUMBER,
274 START_DATE,
275 APPROVAL_DATE,
276 APPROVAL_END_DATE,
277 END_DATE,
278 DELETION_DATE,
279 PROCESS_TYPE,
280 CONTROL_ACTIVITY_TYPE,
281 RISK_COUNT_LATEST,
282 CONTROL_COUNT_LATEST,
283 STANDARD_VARIATION,
284 SIGNIFICANT_PROCESS_FLAG,
285 STANDARD_PROCESS_FLAG,
286 APPROVAL_STATUS,
287 CERTIFICATION_STATUS,
288 PROCESS_CATEGORY,
289 PROCESS_OWNER_ID,
290 PROCESS_ID,
291 ITEM_TYPE,
292 NAME,
293 CREATED_FROM,
294 REQUEST_ID,
295 ATTRIBUTE_CATEGORY,
296 ATTRIBUTE1,
297 ATTRIBUTE2,
298 ATTRIBUTE3,
299 ATTRIBUTE4,
300 ATTRIBUTE5,
301 ATTRIBUTE6,
302 ATTRIBUTE7,
303 ATTRIBUTE8,
304 ATTRIBUTE9,
305 ATTRIBUTE10,
306 ATTRIBUTE11,
307 ATTRIBUTE12,
308 ATTRIBUTE13,
309 ATTRIBUTE14,
310 ATTRIBUTE15,
311 OBJECT_VERSION_NUMBER,
312 CONTROL_COUNT,
313 RISK_COUNT,
314 ORG_COUNT,
315 FINANCE_OWNER_ID,
316 APPLICATION_OWNER_ID
317 from AMW_PROCESS
318 where PROCESS_REV_ID = X_PROCESS_REV_ID
319 for update of PROCESS_REV_ID nowait;
320 recinfo c%rowtype;
321
322 cursor c1 is select
323 DISPLAY_NAME,
324 DESCRIPTION,
325 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
326 from AMW_PROCESS_NAMES_TL
327 where PROCESS_REV_ID = X_PROCESS_REV_ID
328 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
329 for update of PROCESS_REV_ID nowait;
330 begin
331 open c;
332 fetch c into recinfo;
333 if (c%notfound) then
334 close c;
335 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
336 app_exception.raise_exception;
337 end if;
338 close c;
339 if ( ((recinfo.CLASSIFICATION = X_CLASSIFICATION)
343 AND ((recinfo.REVISION_NUMBER = X_REVISION_NUMBER)
340 OR ((recinfo.CLASSIFICATION is null) AND (X_CLASSIFICATION is null)))
341 AND ((recinfo.PROCESS_CODE = X_PROCESS_CODE)
342 OR ((recinfo.PROCESS_CODE is null) AND (X_PROCESS_CODE is null)))
344 OR ((recinfo.REVISION_NUMBER is null) AND (X_REVISION_NUMBER is null)))
345 AND ((recinfo.START_DATE = X_START_DATE)
346 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
347 AND ((recinfo.APPROVAL_DATE = X_APPROVAL_DATE)
348 OR ((recinfo.APPROVAL_DATE is null) AND (X_APPROVAL_DATE is null)))
349 AND ((recinfo.APPROVAL_END_DATE = X_APPROVAL_END_DATE)
350 OR ((recinfo.APPROVAL_END_DATE is null) AND (X_APPROVAL_END_DATE is null)))
351 AND ((recinfo.END_DATE = X_END_DATE)
352 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
353 AND ((recinfo.DELETION_DATE = X_DELETION_DATE)
354 OR ((recinfo.DELETION_DATE is null) AND (X_DELETION_DATE is null)))
355 AND ((recinfo.PROCESS_TYPE = X_PROCESS_TYPE)
356 OR ((recinfo.PROCESS_TYPE is null) AND (X_PROCESS_TYPE is null)))
357 AND ((recinfo.CONTROL_ACTIVITY_TYPE = X_CONTROL_ACTIVITY_TYPE)
358 OR ((recinfo.CONTROL_ACTIVITY_TYPE is null) AND (X_CONTROL_ACTIVITY_TYPE is null)))
359 AND ((recinfo.RISK_COUNT_LATEST = X_RISK_COUNT_LATEST)
360 OR ((recinfo.RISK_COUNT_LATEST is null) AND (X_RISK_COUNT_LATEST is null)))
361 AND ((recinfo.CONTROL_COUNT_LATEST = X_CONTROL_COUNT_LATEST)
362 OR ((recinfo.CONTROL_COUNT_LATEST is null) AND (X_CONTROL_COUNT_LATEST is null)))
363 AND ((recinfo.STANDARD_VARIATION = X_STANDARD_VARIATION)
364 OR ((recinfo.STANDARD_VARIATION is null) AND (X_STANDARD_VARIATION is null)))
365 AND ((recinfo.SIGNIFICANT_PROCESS_FLAG = X_SIGNIFICANT_PROCESS_FLAG)
366 OR ((recinfo.SIGNIFICANT_PROCESS_FLAG is null) AND (X_SIGNIFICANT_PROCESS_FLAG is null)))
367 AND ((recinfo.STANDARD_PROCESS_FLAG = X_STANDARD_PROCESS_FLAG)
368 OR ((recinfo.STANDARD_PROCESS_FLAG is null) AND (X_STANDARD_PROCESS_FLAG is null)))
369 AND ((recinfo.APPROVAL_STATUS = X_APPROVAL_STATUS)
370 OR ((recinfo.APPROVAL_STATUS is null) AND (X_APPROVAL_STATUS is null)))
371 AND ((recinfo.CERTIFICATION_STATUS = X_CERTIFICATION_STATUS)
372 OR ((recinfo.CERTIFICATION_STATUS is null) AND (X_CERTIFICATION_STATUS is null)))
373 AND ((recinfo.PROCESS_CATEGORY = X_PROCESS_CATEGORY)
374 OR ((recinfo.PROCESS_CATEGORY is null) AND (X_PROCESS_CATEGORY is null)))
375 AND ((recinfo.PROCESS_OWNER_ID = X_PROCESS_OWNER_ID)
376 OR ((recinfo.PROCESS_OWNER_ID is null) AND (X_PROCESS_OWNER_ID is null)))
377 AND (recinfo.PROCESS_ID = X_PROCESS_ID)
378 AND (recinfo.ITEM_TYPE = X_ITEM_TYPE)
379 AND (recinfo.NAME = X_NAME)
380 AND ((recinfo.CREATED_FROM = X_CREATED_FROM)
381 OR ((recinfo.CREATED_FROM is null) AND (X_CREATED_FROM is null)))
382 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
383 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
384 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
385 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
386 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
387 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
388 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
389 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
390 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
391 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
392 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
393 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
394 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
395 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
396 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
397 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
398 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
399 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
400 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
401 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
402 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
403 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
404 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
405 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
406 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
407 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
408 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
409 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
410 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
411 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
412 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
413 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
414 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
415 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
416 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
417 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
418 AND ((recinfo.CONTROL_COUNT = X_CONTROL_COUNT)
419 OR ((recinfo.CONTROL_COUNT is null) AND (X_CONTROL_COUNT is null)))
420 AND ((recinfo.RISK_COUNT = X_RISK_COUNT)
421 OR ((recinfo.RISK_COUNT is null) AND (X_RISK_COUNT is null)))
422 AND ((recinfo.ORG_COUNT = X_ORG_COUNT)
423 OR ((recinfo.ORG_COUNT is null) AND (X_ORG_COUNT is null)))
424 AND ((recinfo.FINANCE_OWNER_ID = X_FINANCE_OWNER_ID)
425 OR ((recinfo.FINANCE_OWNER_ID is null) AND (X_FINANCE_OWNER_ID is null)))
426 AND ((recinfo.APPLICATION_OWNER_ID = X_APPLICATION_OWNER_ID)
427 OR ((recinfo.APPLICATION_OWNER_ID is null) AND (X_APPLICATION_OWNER_ID is null)))
428 ) then
429 null;
430 else
434
431 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
432 app_exception.raise_exception;
433 end if;
435 for tlinfo in c1 loop
436 if (tlinfo.BASELANG = 'Y') then
437 if ( ((tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
438 OR ((tlinfo.DISPLAY_NAME is null) AND (X_DISPLAY_NAME is null)))
439 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
440 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
441 ) then
442 null;
443 else
444 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
445 app_exception.raise_exception;
446 end if;
447 end if;
448 end loop;
449 return;
450 end LOCK_ROW;
451
452 procedure UPDATE_ROW (
453 X_PROCESS_REV_ID in NUMBER,
454 X_CLASSIFICATION in NUMBER,
455 X_PROCESS_CODE in VARCHAR2,
456 X_REVISION_NUMBER in NUMBER,
457 X_START_DATE in DATE,
458 X_APPROVAL_DATE in DATE,
459 X_APPROVAL_END_DATE in DATE,
460 X_END_DATE in DATE,
461 X_DELETION_DATE in DATE,
462 X_PROCESS_TYPE in VARCHAR2,
463 X_CONTROL_ACTIVITY_TYPE in VARCHAR2,
464 X_RISK_COUNT_LATEST in NUMBER,
465 X_CONTROL_COUNT_LATEST in NUMBER,
466 X_STANDARD_VARIATION in NUMBER,
467 X_SIGNIFICANT_PROCESS_FLAG in VARCHAR2,
468 X_STANDARD_PROCESS_FLAG in VARCHAR2,
469 X_APPROVAL_STATUS in VARCHAR2,
470 X_CERTIFICATION_STATUS in VARCHAR2,
471 X_PROCESS_CATEGORY in VARCHAR2,
472 X_PROCESS_OWNER_ID in NUMBER,
473 X_PROCESS_ID in NUMBER,
474 X_ITEM_TYPE in VARCHAR2,
475 X_NAME in VARCHAR2,
476 X_CREATED_FROM in VARCHAR2,
477 X_REQUEST_ID in NUMBER,
478 X_ATTRIBUTE_CATEGORY in VARCHAR2,
479 X_ATTRIBUTE1 in VARCHAR2,
480 X_ATTRIBUTE2 in VARCHAR2,
481 X_ATTRIBUTE3 in VARCHAR2,
482 X_ATTRIBUTE4 in VARCHAR2,
483 X_ATTRIBUTE5 in VARCHAR2,
484 X_ATTRIBUTE6 in VARCHAR2,
485 X_ATTRIBUTE7 in VARCHAR2,
486 X_ATTRIBUTE8 in VARCHAR2,
487 X_ATTRIBUTE9 in VARCHAR2,
488 X_ATTRIBUTE10 in VARCHAR2,
489 X_ATTRIBUTE11 in VARCHAR2,
490 X_ATTRIBUTE12 in VARCHAR2,
491 X_ATTRIBUTE13 in VARCHAR2,
492 X_ATTRIBUTE14 in VARCHAR2,
493 X_ATTRIBUTE15 in VARCHAR2,
494 X_OBJECT_VERSION_NUMBER in NUMBER,
495 X_CONTROL_COUNT in NUMBER,
496 X_RISK_COUNT in NUMBER,
497 X_ORG_COUNT in NUMBER,
498 X_FINANCE_OWNER_ID in NUMBER,
499 X_APPLICATION_OWNER_ID in NUMBER,
500 X_DISPLAY_NAME in VARCHAR2,
501 X_DESCRIPTION in VARCHAR2,
502 X_LAST_UPDATE_DATE in DATE,
503 X_LAST_UPDATED_BY in NUMBER,
504 X_LAST_UPDATE_LOGIN in NUMBER
505 ) is
506 begin
507 update AMW_PROCESS set
508 CLASSIFICATION = X_CLASSIFICATION,
509 PROCESS_CODE = X_PROCESS_CODE,
510 REVISION_NUMBER = X_REVISION_NUMBER,
511 START_DATE = X_START_DATE,
512 APPROVAL_DATE = X_APPROVAL_DATE,
513 APPROVAL_END_DATE = X_APPROVAL_END_DATE,
514 END_DATE = X_END_DATE,
515 DELETION_DATE = X_DELETION_DATE,
516 PROCESS_TYPE = X_PROCESS_TYPE,
517 CONTROL_ACTIVITY_TYPE = X_CONTROL_ACTIVITY_TYPE,
518 RISK_COUNT_LATEST = X_RISK_COUNT_LATEST,
519 CONTROL_COUNT_LATEST = X_CONTROL_COUNT_LATEST,
520 STANDARD_VARIATION = X_STANDARD_VARIATION,
521 SIGNIFICANT_PROCESS_FLAG = X_SIGNIFICANT_PROCESS_FLAG,
522 STANDARD_PROCESS_FLAG = X_STANDARD_PROCESS_FLAG,
523 APPROVAL_STATUS = X_APPROVAL_STATUS,
524 CERTIFICATION_STATUS = X_CERTIFICATION_STATUS,
525 PROCESS_CATEGORY = X_PROCESS_CATEGORY,
526 PROCESS_OWNER_ID = X_PROCESS_OWNER_ID,
527 PROCESS_ID = X_PROCESS_ID,
528 ITEM_TYPE = X_ITEM_TYPE,
529 NAME = X_NAME,
530 CREATED_FROM = X_CREATED_FROM,
531 REQUEST_ID = X_REQUEST_ID,
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 ATTRIBUTE13 = X_ATTRIBUTE13,
546 ATTRIBUTE14 = X_ATTRIBUTE14,
547 ATTRIBUTE15 = X_ATTRIBUTE15,
548 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
549 CONTROL_COUNT = X_CONTROL_COUNT,
550 RISK_COUNT = X_RISK_COUNT,
551 ORG_COUNT = X_ORG_COUNT,
552 FINANCE_OWNER_ID = X_FINANCE_OWNER_ID,
553 APPLICATION_OWNER_ID = X_APPLICATION_OWNER_ID,
554 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
555 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
556 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
557 where PROCESS_REV_ID = X_PROCESS_REV_ID;
558
559 if (sql%notfound) then
560 raise no_data_found;
561 end if;
562
563 update AMW_PROCESS_NAMES_TL set
564 DISPLAY_NAME = X_DISPLAY_NAME,
565 DESCRIPTION = X_DESCRIPTION,
566 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
567 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
568 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
569 SOURCE_LANG = userenv('LANG')
570 where PROCESS_REV_ID = X_PROCESS_REV_ID
571 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
572
573 if (sql%notfound) then
574 raise no_data_found;
575 end if;
576 end UPDATE_ROW;
577
578 procedure DELETE_ROW (
579 X_PROCESS_REV_ID in NUMBER
580 ) is
581 begin
582 delete from AMW_PROCESS_NAMES_TL
583 where PROCESS_REV_ID = X_PROCESS_REV_ID;
584
585 if (sql%notfound) then
586 raise no_data_found;
587 end if;
588
589 delete from AMW_PROCESS
593 raise no_data_found;
590 where PROCESS_REV_ID = X_PROCESS_REV_ID;
591
592 if (sql%notfound) then
594 end if;
595 end DELETE_ROW;
596
597 procedure ADD_LANGUAGE
598 is
599 begin
600 delete from AMW_PROCESS_NAMES_TL T
601 where not exists
602 (select NULL
603 from AMW_PROCESS B
604 where B.PROCESS_REV_ID = T.PROCESS_REV_ID
605 );
606
607 update AMW_PROCESS_NAMES_TL T set (
608 DISPLAY_NAME,
609 DESCRIPTION
610 ) = (select
611 B.DISPLAY_NAME,
612 B.DESCRIPTION
613 from AMW_PROCESS_NAMES_TL B
614 where B.PROCESS_REV_ID = T.PROCESS_REV_ID
615 and B.LANGUAGE = T.SOURCE_LANG)
616 where (
617 T.PROCESS_REV_ID,
618 T.LANGUAGE
619 ) in (select
620 SUBT.PROCESS_REV_ID,
621 SUBT.LANGUAGE
622 from AMW_PROCESS_NAMES_TL SUBB, AMW_PROCESS_NAMES_TL SUBT
623 where SUBB.PROCESS_REV_ID = SUBT.PROCESS_REV_ID
624 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
625 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
626 or (SUBB.DISPLAY_NAME is null and SUBT.DISPLAY_NAME is not null)
627 or (SUBB.DISPLAY_NAME is not null and SUBT.DISPLAY_NAME is null)
628 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
629 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
630 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
631 ));
632
633 insert into AMW_PROCESS_NAMES_TL (
634 PROCESS_ID,
635 REVISION_NUMBER,
636 DISPLAY_NAME,
637 DESCRIPTION,
638 LAST_UPDATE_DATE,
639 LAST_UPDATED_BY,
640 LAST_UPDATE_LOGIN,
641 CREATION_DATE,
642 CREATED_BY,
643 OBJECT_VERSION_NUMBER,
644 PROCESS_REV_ID,
645 LANGUAGE,
646 SOURCE_LANG
647 ) select /*+ ORDERED */
648 B.PROCESS_ID,
649 B.REVISION_NUMBER,
650 B.DISPLAY_NAME,
651 B.DESCRIPTION,
652 B.LAST_UPDATE_DATE,
653 B.LAST_UPDATED_BY,
654 B.LAST_UPDATE_LOGIN,
655 B.CREATION_DATE,
656 B.CREATED_BY,
657 B.OBJECT_VERSION_NUMBER,
658 B.PROCESS_REV_ID,
659 L.LANGUAGE_CODE,
660 B.SOURCE_LANG
661 from AMW_PROCESS_NAMES_TL B, FND_LANGUAGES L
662 where L.INSTALLED_FLAG in ('I', 'B')
663 and B.LANGUAGE = userenv('LANG')
664 and not exists
665 (select NULL
666 from AMW_PROCESS_NAMES_TL T
667 where T.PROCESS_REV_ID = B.PROCESS_REV_ID
668 and T.LANGUAGE = L.LANGUAGE_CODE);
669 end ADD_LANGUAGE;
670
671
672 procedure load_seed_data (x_owner in varchar2,
673 x_last_update_date in varchar2,
674 x_display_name in varchar2,
675 x_description in varchar2,
676 x_process_rev_id in number,
677 x_process_code in varchar2) is
678 G_TXN_DATE date := sysdate;
679 L_ROWID varchar2(100);
680 l_OWNER number;
681 l_last_update_date date;
682
683 begin
684 l_OWNER := fnd_load_util.owner_id(X_OWNER);
685 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
686
687 delete from amw_process where process_rev_id = x_process_rev_id;
688 delete from amw_process_names_tl where process_rev_id = x_process_rev_id;
689
690 INSERT_ROW (
691 X_ROWID => L_ROWID,
692 X_PROCESS_REV_ID => x_process_rev_id,
693 X_CLASSIFICATION => null,
694 X_PROCESS_CODE => x_process_code,
695 X_REVISION_NUMBER => 1,
696 X_START_DATE => G_TXN_DATE,
697 X_APPROVAL_DATE => G_TXN_DATE,
698 X_APPROVAL_END_DATE => null,
699 X_END_DATE => null,
700 X_DELETION_DATE => null,
701 X_PROCESS_TYPE => 'P',
702 X_CONTROL_ACTIVITY_TYPE => null,
703 X_RISK_COUNT_LATEST => null,
704 X_CONTROL_COUNT_LATEST => null,
705 X_STANDARD_VARIATION => null,
706 X_SIGNIFICANT_PROCESS_FLAG => null,
707 X_STANDARD_PROCESS_FLAG => null,
708 X_APPROVAL_STATUS => 'A',
709 X_CERTIFICATION_STATUS => null,
710 X_PROCESS_CATEGORY => null,
711 X_PROCESS_OWNER_ID => null,
712 X_PROCESS_ID => x_process_rev_id,
713 X_ITEM_TYPE => 'AUDITMGR',
714 X_NAME => x_process_code,
715 X_CREATED_FROM => null,
716 X_REQUEST_ID => null,
717 X_ATTRIBUTE_CATEGORY => null,
718 X_ATTRIBUTE1 => null,
719 X_ATTRIBUTE2 => null,
720 X_ATTRIBUTE3 => null,
721 X_ATTRIBUTE4 => null,
722 X_ATTRIBUTE5 => null,
723 X_ATTRIBUTE6 => null,
724 X_ATTRIBUTE7 => null,
725 X_ATTRIBUTE8 => null,
726 X_ATTRIBUTE9 => null,
727 X_ATTRIBUTE10 => null,
728 X_ATTRIBUTE11 => null,
729 X_ATTRIBUTE12 => null,
730 X_ATTRIBUTE13 => null,
731 X_ATTRIBUTE14 => null,
732 X_ATTRIBUTE15 => null,
733 X_OBJECT_VERSION_NUMBER => 1,
734 X_CONTROL_COUNT => null,
735 X_RISK_COUNT => null,
736 X_ORG_COUNT => null,
737 X_FINANCE_OWNER_ID => null,
738 X_APPLICATION_OWNER_ID => null,
746
739 X_DISPLAY_NAME => x_display_name,
740 X_DESCRIPTION => x_description,
741 X_CREATION_DATE => l_last_update_date,
742 X_CREATED_BY => l_OWNER,
743 X_LAST_UPDATE_DATE => l_last_update_date,
744 X_LAST_UPDATED_BY => l_OWNER,
745 X_LAST_UPDATE_LOGIN => 0 );
750 X_PROCESS_REV_ID => -2,
747 /*
748 INSERT_ROW (
749 X_ROWID => L_ROWID,
751 X_CLASSIFICATION => null,
752 X_PROCESS_CODE => 'ORG_PROCESS',
753 X_REVISION_NUMBER => 1,
754 X_START_DATE => G_TXN_DATE,
755 X_APPROVAL_DATE => G_TXN_DATE,
756 X_APPROVAL_END_DATE => null,
757 X_END_DATE => null,
758 X_DELETION_DATE => null,
759 X_PROCESS_TYPE => 'P',
760 X_CONTROL_ACTIVITY_TYPE => null,
761 X_RISK_COUNT_LATEST => null,
762 X_CONTROL_COUNT_LATEST => null,
763 X_STANDARD_VARIATION => null,
764 X_SIGNIFICANT_PROCESS_FLAG => null,
765 X_STANDARD_PROCESS_FLAG => null,
766 X_APPROVAL_STATUS => 'A',
767 X_CERTIFICATION_STATUS => null,
768 X_PROCESS_CATEGORY => null,
769 X_PROCESS_OWNER_ID => null,
770 X_PROCESS_ID => -2,
771 X_ITEM_TYPE => 'AUDITMGR',
772 X_NAME => 'ORG_PROCESS',
773 X_CREATED_FROM => null,
774 X_REQUEST_ID => null,
775 X_ATTRIBUTE_CATEGORY => null,
776 X_ATTRIBUTE1 => null,
777 X_ATTRIBUTE2 => null,
778 X_ATTRIBUTE3 => null,
779 X_ATTRIBUTE4 => null,
780 X_ATTRIBUTE5 => null,
781 X_ATTRIBUTE6 => null,
782 X_ATTRIBUTE7 => null,
783 X_ATTRIBUTE8 => null,
784 X_ATTRIBUTE9 => null,
785 X_ATTRIBUTE10 => null,
786 X_ATTRIBUTE11 => null,
787 X_ATTRIBUTE12 => null,
788 X_ATTRIBUTE13 => null,
789 X_ATTRIBUTE14 => null,
790 X_ATTRIBUTE15 => null,
791 X_OBJECT_VERSION_NUMBER => 1,
792 X_CONTROL_COUNT => null,
793 X_RISK_COUNT => null,
794 X_ORG_COUNT => null,
795 X_FINANCE_OWNER_ID => null,
796 X_APPLICATION_OWNER_ID => null,
797 X_DISPLAY_NAME => 'Assigned Processes',
798 X_DESCRIPTION => 'Assigned Processes',
799 X_CREATION_DATE => l_last_update_date,
800 X_CREATED_BY => l_OWNER,
801 X_LAST_UPDATE_DATE => l_last_update_date,
802 X_LAST_UPDATED_BY => l_OWNER,
803 X_LAST_UPDATE_LOGIN => 0 );
804 */
805 end load_seed_data;
806
807 end AMW_PROCESS_NAMES_PKG;