[Home] [Help]
PACKAGE BODY: APPS.AMW_PROCESS_PKG
Source
1 PACKAGE BODY AMW_PROCESS_PKG as
2 /* $Header: amwtprlb.pls 120.0 2005/05/31 18:37:15 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_PROCESS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PROCESS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwtprlb.pls';
19
20 ----------------------------------------------------------
21 ---- MEDIA ----
22 ----------------------------------------------------------
23
24 -- ========================================================
25 --
26 -- NAME
27 -- createLoadBody
28 --
29 -- PURPOSE
30 --
31 -- NOTES
32 --
33 -- HISTORY
34 --
35 -- ========================================================
36
37
38 PROCEDURE Load_Row(
39 p_significant_process_flag VARCHAR2,
40 p_standard_process_flag VARCHAR2,
41 p_approval_status VARCHAR2,
42 p_certification_status VARCHAR2,
43 p_process_owner_id number,
44 p_last_update_date DATE,
45 p_last_updated_by NUMBER,
46 p_creation_date DATE,
47 p_created_by NUMBER,
48 p_last_update_login NUMBER,
49 p_item_type VARCHAR2,
50 p_name VARCHAR2,
51 p_created_from VARCHAR2,
52 p_request_id NUMBER,
53 p_program_application_id NUMBER,
54 p_program_id NUMBER,
55 p_program_update_date DATE,
56 p_attribute_category VARCHAR2,
57 p_attribute1 VARCHAR2,
58 p_attribute2 VARCHAR2,
59 p_attribute3 VARCHAR2,
60 p_attribute4 VARCHAR2,
61 p_attribute5 VARCHAR2,
62 p_attribute6 VARCHAR2,
63 p_attribute7 VARCHAR2,
64 p_attribute8 VARCHAR2,
65 p_attribute9 VARCHAR2,
66 p_attribute10 VARCHAR2,
67 p_attribute11 VARCHAR2,
68 p_attribute12 VARCHAR2,
69 p_attribute13 VARCHAR2,
70 p_attribute14 VARCHAR2,
71 p_attribute15 VARCHAR2,
72 p_security_group_id NUMBER,
73 p_control_count number,
74 p_risk_count number,
75 p_org_count number,
76 p_process_category varchar2 := null,
77 p_finance_owner_id number := null,
78 p_application_owner_id number := null,
79 p_standard_variation number := null,
80 px_object_version_number IN OUT NOCOPY NUMBER,
81 px_process_rev_id in out nocopy number,
82 px_process_id IN OUT NOCOPY NUMBER)
83
84 is
85 begin
86
87 -- declare
88 -- user_id number := 0;
89 -- row_id varchar2(64);
90
91 -- begin
92
93 null;
94
95 -- if (X_OWNER = 'SEED') then
96 -- user_id := -1;
97 -- end if;
98
99 -- user_id := 1;
100 -- AMW_PROCESS_PKG.Update_Row (
101 -- p_significant_process_flag => p_significant_process_flag,
102 -- p_standard_process_flag => p_standard_process_flag,
103 -- p_approval_status => p_approval_status,
104 -- p_certification_status => p_certification_status,
105 -- p_process_owner_id => p_process_owner_id,
106 -- p_last_update_date => p_last_update_date,
107 -- p_last_updated_by => p_last_updated_by,
108 -- p_creation_date => p_creation_date,
109 -- p_created_by => p_created_by,
110 -- p_last_update_login => p_last_update_login,
111 -- p_item_type => p_item_type,
112 -- p_name => p_name,
113 -- p_created_from => p_created_from,
114 -- p_request_id => p_request_id,
115 -- p_program_application_id => p_program_application_id,
116 -- p_program_id => p_program_id,
117 -- p_program_update_date => p_program_update_date,
118 -- p_attribute_category => p_attribute_category,
119 -- p_attribute1 => p_attribute1,
120 -- p_attribute2 => p_attribute2,
121 -- p_attribute3 => p_attribute3,
122 -- p_attribute4 => p_attribute4,
123 -- p_attribute5 => p_attribute5,
124 -- p_attribute6 => p_attribute6,
125 -- p_attribute7 => p_attribute7,
126 -- p_attribute8 => p_attribute8,
127 -- p_attribute9 => p_attribute9,
128 -- p_attribute10 => p_attribute10,
129 -- p_attribute11 => p_attribute11,
130 -- p_attribute12 => p_attribute12,
131 -- p_attribute13 => p_attribute13,
132 -- p_attribute14 => p_attribute14,
133 -- p_attribute15 => p_attribute15,
134 -- p_security_group_id => p_security_group_id,
135 -- p_control_count => p_control_count,
136 -- p_risk_count => p_risk_count,
137 -- p_org_count => p_org_count,
138 -- p_process_category => p_process_category,
139 -- p_finance_owner_id => p_finance_owner_id,
140 -- p_application_owner_id => p_application_owner_id,
141 -- p_standard_variation => p_standard_variation,
142 -- p_object_version_number => px_object_version_number,
143 -- p_process_rev_id => px_process_rev_id,
144 -- p_process_id => px_process_id);
145 --
146 --exception
147 -- when NO_DATA_FOUND then
148 --
149 -- AMW_PROCESS_PKG.Insert_Row(
150 -- p_significant_process_flag => p_significant_process_flag,
151 -- p_standard_process_flag => p_standard_process_flag,
152 -- p_approval_status => p_approval_status,
153 -- p_certification_status => p_certification_status,
154 -- p_process_owner_id => p_process_owner_id,
155 -- p_last_update_date => p_last_update_date,
156 -- p_last_updated_by => p_last_updated_by,
157 -- p_creation_date => p_creation_date,
158 -- p_created_by => p_created_by,
159 -- p_last_update_login => p_last_update_login,
160 -- p_item_type => p_item_type,
161 -- p_name => p_name,
162 -- p_created_from => p_created_from,
163 -- p_request_id => p_request_id,
164 -- p_program_application_id => p_program_application_id,
165 -- p_program_id => p_program_id,
166 -- p_program_update_date => p_program_update_date,
167 -- p_attribute_category => p_attribute_category,
168 -- p_attribute1 => p_attribute1,
169 -- p_attribute2 => p_attribute2,
170 -- p_attribute3 => p_attribute3,
171 -- p_attribute4 => p_attribute4,
172 -- p_attribute5 => p_attribute5,
173 -- p_attribute6 => p_attribute6,
174 -- p_attribute7 => p_attribute7,
175 -- p_attribute8 => p_attribute8,
176 -- p_attribute9 => p_attribute9,
177 -- p_attribute10 => p_attribute10,
178 -- p_attribute11 => p_attribute11,
179 -- p_attribute12 => p_attribute12,
180 -- p_attribute13 => p_attribute13,
181 -- p_attribute14 => p_attribute14,
182 -- p_attribute15 => p_attribute15,
183 -- p_security_group_id => p_security_group_id,
184 -- p_control_count => p_control_count,
185 -- p_risk_count => p_risk_count,
186 -- p_org_count => p_org_count,
187 -- p_process_category => p_process_category,
188 -- p_finance_owner_id => p_finance_owner_id,
189 -- p_application_owner_id => p_application_owner_id,
190 -- p_standard_variation => p_standard_variation,
191 -- px_object_version_number => px_object_version_number,
192 -- px_process_rev_id => px_process_rev_id,
193 -- px_process_id => px_process_id);
194 -- end;
195 --EXCEPTION WHEN OTHERS THEN
196 -----dbms_output.put_line(SQLERRM);
197 --RAISE ;
198 --RETURN;
199 end Load_Row;
200
201
202 ----------------------------------------------------------
203 ---- MEDIA ----
204 ----------------------------------------------------------
205
206 -- ========================================================
207 --
208 -- NAME
209 -- createInsertBody
210 --
211 -- PURPOSE
212 --
213 -- NOTES
214 --
215 -- HISTORY
216 --
217 -- ========================================================
218 PROCEDURE Insert_Row(
219 p_SIGNIFICANT_PROCESS_FLAG VARCHAR2,
220 p_STANDARD_PROCESS_FLAG VARCHAR2,
221 p_APPROVAL_STATUS VARCHAR2,
222 p_CERTIFICATION_STATUS VARCHAR2,
223 p_PROCESS_OWNER_ID NUMBER,
224 p_last_update_date DATE,
225 p_last_updated_by NUMBER,
226 p_creation_date DATE,
227 p_created_by NUMBER,
228 p_last_update_login NUMBER,
229 p_item_type VARCHAR2,
230 p_name VARCHAR2,
231 p_created_from VARCHAR2,
232 p_request_id NUMBER,
233 p_program_application_id NUMBER,
234 p_program_id NUMBER,
235 p_program_update_date DATE,
236 p_attribute_category VARCHAR2,
237 p_attribute1 VARCHAR2,
238 p_attribute2 VARCHAR2,
239 p_attribute3 VARCHAR2,
240 p_attribute4 VARCHAR2,
241 p_attribute5 VARCHAR2,
242 p_attribute6 VARCHAR2,
243 p_attribute7 VARCHAR2,
244 p_attribute8 VARCHAR2,
245 p_attribute9 VARCHAR2,
246 p_attribute10 VARCHAR2,
247 p_attribute11 VARCHAR2,
248 p_attribute12 VARCHAR2,
249 p_attribute13 VARCHAR2,
250 p_attribute14 VARCHAR2,
251 p_attribute15 VARCHAR2,
252 p_security_group_id NUMBER,
253 p_RISK_COUNT NUMBER,
254 p_control_COUNT NUMBER,
255 p_ORG_COUNT NUMBER,
256 p_process_category varchar2 := null,
257 p_finance_owner_id number := null,
258 p_application_owner_id number := null,
259 p_standard_variation number := null,
260 px_object_version_number IN OUT NOCOPY NUMBER,
261 px_PROCESS_REV_ID in out nocopy NUMBER,
262 px_process_id IN OUT NOCOPY NUMBER)
263
264 IS
265 x_rowid VARCHAR2(30);
266
267
268 BEGIN
269
270
271 px_object_version_number := 1;
272
273
274 INSERT INTO AMW_PROCESS(
275 significant_process_flag,
276 standard_process_flag,
277 approval_status,
278 certification_status,
279 process_owner_id,
280 process_id,
281 last_update_date,
282 last_updated_by,
283 creation_date,
284 created_by,
285 last_update_login,
286 item_type,
287 name,
288 created_from,
289 request_id,
290 program_application_id,
291 program_id,
292 program_update_date,
293 attribute_category,
294 attribute1,
295 attribute2,
296 attribute3,
297 attribute4,
298 attribute5,
299 attribute6,
300 attribute7,
301 attribute8,
302 attribute9,
303 attribute10,
304 attribute11,
305 attribute12,
306 attribute13,
307 attribute14,
308 attribute15,
309 security_group_id,
310 object_version_number,
311 process_rev_id,
312 control_count,
313 risk_count,
314 org_count,
315 process_category,
316 finance_owner_id,
317 application_owner_id,
318 standard_variation
319 ) VALUES (
320 DECODE( p_significant_process_flag, FND_API.g_miss_char, NULL, p_significant_process_flag),
321 DECODE( p_standard_process_flag, FND_API.g_miss_char, NULL, p_standard_process_flag),
322 DECODE( p_approval_status, FND_API.g_miss_char, NULL, p_approval_status),
323 DECODE( p_certification_status, FND_API.g_miss_char, NULL, p_certification_status),
324 DECODE( p_process_owner_id, FND_API.g_miss_num, NULL, p_process_owner_id),
325 DECODE( px_process_id, FND_API.g_miss_num, NULL, px_process_id),
326 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
327 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
328 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
329 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
330 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
331 DECODE( p_item_type, FND_API.g_miss_char, NULL, p_item_type),
332 DECODE( p_name, FND_API.g_miss_char, NULL, p_name),
333 DECODE( p_created_from, FND_API.g_miss_char, NULL, p_created_from),
334 DECODE( p_request_id, FND_API.g_miss_num, NULL, p_request_id),
335 DECODE( p_program_application_id, FND_API.g_miss_num, NULL, p_program_application_id),
336 DECODE( p_program_id, FND_API.g_miss_num, NULL, p_program_id),
337 DECODE( p_program_update_date, FND_API.g_miss_date, NULL, p_program_update_date),
338 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
339 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
340 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
341 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
342 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
343 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
344 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
345 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
346 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
347 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
348 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
349 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
350 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
351 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
352 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
353 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
354 DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id),
355 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
356 DECODE( px_process_rev_id, FND_API.g_miss_num, NULL, px_process_rev_id),
357 DECODE( p_control_count, FND_API.g_miss_num, NULL, p_control_count),
358 DECODE( p_risk_count, FND_API.g_miss_num, NULL, p_risk_count),
359 DECODE( p_org_count, FND_API.g_miss_num, NULL, p_org_count),
360 DECODE( p_process_category, FND_API.g_miss_char, NULL, p_process_category),
361 DECODE( p_finance_owner_id, FND_API.g_miss_num, NULL, p_finance_owner_id),
362 DECODE( p_application_owner_id, FND_API.g_miss_num, NULL, p_application_owner_id),
363 DECODE( p_standard_variation, FND_API.g_miss_num, NULL, p_standard_variation)
364 );
365 END Insert_Row;
366
367
368 ----------------------------------------------------------
369 ---- MEDIA ----
370 ----------------------------------------------------------
371
372 -- ========================================================
373 --
374 -- NAME
375 -- createUpdateBody
376 --
377 -- PURPOSE
378 --
379 -- NOTES
380 --
381 -- HISTORY
382 --
383 -- ========================================================
384 PROCEDURE Update_Row(
385 p_SIGNIFICANT_PROCESS_FLAG VARCHAR2,
386 p_STANDARD_PROCESS_FLAG VARCHAR2,
387 p_APPROVAL_STATUS VARCHAR2,
388 p_CERTIFICATION_STATUS VARCHAR2,
389 p_PROCESS_OWNER_ID NUMBER,
390 p_last_update_date DATE,
391 p_last_updated_by NUMBER,
392 p_creation_date DATE,
393 p_created_by NUMBER,
394 p_last_update_login NUMBER,
395 p_item_type VARCHAR2,
396 p_name VARCHAR2,
397 p_created_from VARCHAR2,
398 p_request_id NUMBER,
399 p_program_application_id NUMBER,
400 p_program_id NUMBER,
401 p_program_update_date DATE,
402 p_attribute_category VARCHAR2,
403 p_attribute1 VARCHAR2,
404 p_attribute2 VARCHAR2,
405 p_attribute3 VARCHAR2,
406 p_attribute4 VARCHAR2,
407 p_attribute5 VARCHAR2,
408 p_attribute6 VARCHAR2,
409 p_attribute7 VARCHAR2,
410 p_attribute8 VARCHAR2,
411 p_attribute9 VARCHAR2,
412 p_attribute10 VARCHAR2,
413 p_attribute11 VARCHAR2,
414 p_attribute12 VARCHAR2,
415 p_attribute13 VARCHAR2,
416 p_attribute14 VARCHAR2,
417 p_attribute15 VARCHAR2,
418 p_security_group_id NUMBER,
419 p_control_COUNT NUMBER,
420 p_RISK_COUNT NUMBER,
421 p_ORG_COUNT NUMBER,
422 p_process_category varchar2 := null,
423 p_finance_owner_id number := null,
424 p_application_owner_id number := null,
425 p_standard_variation number := null,
426 p_object_version_number NUMBER,
427 p_PROCESS_REV_ID NUMBER,
428 p_process_id NUMBER)
429
430 IS
431 cursor c1 is
432 select object_version_number from amw_process where process_rev_id=p_process_rev_id;
433
434 l_obj_num c1%rowtype;
435 l_object_version_number number := 0;
436
437 BEGIN
438 open c1;
439 fetch c1 into l_obj_num;
440 close c1;
441
442 l_object_version_number := l_obj_num.object_version_number+1;
443
444 Update AMW_PROCESS
445 SET
446 significant_process_flag = DECODE(p_significant_process_flag, FND_API.g_miss_char, significant_process_flag, p_significant_process_flag),
447 standard_process_flag = DECODE(p_standard_process_flag, FND_API.g_miss_char, standard_process_flag, p_standard_process_flag),
448 approval_status = DECODE(p_approval_status, FND_API.g_miss_char, approval_status, p_approval_status),
449 certification_status = DECODE(p_certification_status, FND_API.g_miss_char, certification_status, p_certification_status),
450 process_owner_id = DECODE( p_process_owner_id, FND_API.g_miss_num, process_owner_id, p_process_owner_id),
451 process_id = DECODE( p_process_id, FND_API.g_miss_num, process_id, p_process_id),
452 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
453 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
454 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
455 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
456 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
457 item_type = DECODE( p_item_type, FND_API.g_miss_char, item_type, p_item_type),
458 name = DECODE( p_name, FND_API.g_miss_char, name, p_name),
459 created_from = DECODE( p_created_from, FND_API.g_miss_char, created_from, p_created_from),
460 request_id = DECODE( p_request_id, FND_API.g_miss_num, request_id, p_request_id),
461 program_application_id = DECODE( p_program_application_id, FND_API.g_miss_num, program_application_id, p_program_application_id),
462 program_id = DECODE( p_program_id, FND_API.g_miss_num, program_id, p_program_id),
463 program_update_date = DECODE( p_program_update_date, FND_API.g_miss_date, program_update_date, p_program_update_date),
464 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
465 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
466 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
467 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
468 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
469 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
470 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
471 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
472 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
476 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
473 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
474 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
475 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
477 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
478 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
479 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
480 security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
481 object_version_number = DECODE( l_object_version_number, FND_API.g_miss_num, object_version_number, l_object_version_number),
482 control_count = DECODE( p_control_count, FND_API.g_miss_num, control_count, p_control_count),
483 risk_count = DECODE( p_risk_count, FND_API.g_miss_num, risk_count, p_risk_count),
484 org_count = DECODE( p_org_count, FND_API.g_miss_num, org_count, p_org_count),
485 process_category = DECODE( p_process_category, FND_API.g_miss_char, process_category, p_process_category),
486 finance_owner_id = DECODE( p_finance_owner_id, FND_API.g_miss_num, finance_owner_id, p_finance_owner_id),
487 application_owner_id = DECODE( p_application_owner_id, FND_API.g_miss_num, application_owner_id, p_application_owner_id),
488 standard_variation = DECODE( p_standard_variation, FND_API.g_miss_num, standard_variation, p_standard_variation)
489 WHERE PROCESS_rev_ID = p_PROCESS_rev_ID;
490
491 IF (SQL%NOTFOUND) THEN
492 RAISE NO_DATA_FOUND;
493 END IF;
494 END Update_Row;
495
496
497 ----------------------------------------------------------
498 ---- MEDIA ----
499 ----------------------------------------------------------
500
501 -- ========================================================
502 --
503 -- NAME
504 -- createDeleteBody
505 --
506 -- PURPOSE
507 --
508 -- NOTES
509 --
510 -- HISTORY
511 --
512 -- ========================================================
513 PROCEDURE Delete_Row(
514 p_PROCESS_rev_ID NUMBER)
515 IS
516 BEGIN
517 DELETE FROM AMW_PROCESS
518 WHERE PROCESS_rev_ID = p_PROCESS_rev_ID;
519 If (SQL%NOTFOUND) then
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 End If;
522 END Delete_Row ;
523
524
525
526 ----------------------------------------------------------
527 ---- MEDIA ----
528 ----------------------------------------------------------
529
530 -- ========================================================
531 --
532 -- NAME
533 -- createLockBody
534 --
535 -- PURPOSE
536 --
537 -- NOTES
538 --
539 -- HISTORY
540 --
541 -- ========================================================
542 PROCEDURE Lock_Row(
543 p_SIGNIFICANT_PROCESS_FLAG VARCHAR2,
544 p_STANDARD_PROCESS_FLAG VARCHAR2,
545 p_APPROVAL_STATUS VARCHAR2,
546 p_CERTIFICATION_STATUS VARCHAR2,
547 p_PROCESS_OWNER_ID NUMBER,
548 p_last_update_date DATE,
549 p_last_updated_by NUMBER,
550 p_creation_date DATE,
551 p_created_by NUMBER,
552 p_last_update_login NUMBER,
553 p_item_type VARCHAR2,
554 p_name VARCHAR2,
555 p_created_from VARCHAR2,
556 p_request_id NUMBER,
557 p_program_application_id NUMBER,
558 p_program_id NUMBER,
559 p_program_update_date DATE,
560 p_attribute_category VARCHAR2,
561 p_attribute1 VARCHAR2,
562 p_attribute2 VARCHAR2,
563 p_attribute3 VARCHAR2,
564 p_attribute4 VARCHAR2,
565 p_attribute5 VARCHAR2,
566 p_attribute6 VARCHAR2,
567 p_attribute7 VARCHAR2,
568 p_attribute8 VARCHAR2,
569 p_attribute9 VARCHAR2,
570 p_attribute10 VARCHAR2,
571 p_attribute11 VARCHAR2,
572 p_attribute12 VARCHAR2,
573 p_attribute13 VARCHAR2,
574 p_attribute14 VARCHAR2,
575 p_attribute15 VARCHAR2,
576 p_security_group_id NUMBER,
577 p_RISK_COUNT NUMBER,
578 p_control_COUNT NUMBER,
579 p_ORG_COUNT NUMBER,
580 p_process_category varchar2 := null,
581 p_finance_owner_id number := null,
582 p_application_owner_id number := null,
583 p_standard_variation number := null,
584 p_object_version_number NUMBER,
585 p_PROCESS_REV_ID NUMBER,
586 p_process_id NUMBER)
587
588 IS
589 CURSOR C IS
590 SELECT *
591 FROM AMW_PROCESS
592 WHERE PROCESS_rev_ID = p_PROCESS_rev_ID
593 FOR UPDATE of PROCESS_rev_ID NOWAIT;
594 Recinfo C%ROWTYPE;
595 BEGIN
596 OPEN c;
597 FETCH c INTO Recinfo;
598 If (c%NOTFOUND) then
599 CLOSE c;
600 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
601 APP_EXCEPTION.RAISE_EXCEPTION;
602 END IF;
606 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
603 CLOSE C;
604 IF (
605 ( Recinfo.last_update_date = p_last_update_date)
607 OR ( ( Recinfo.last_updated_by IS NULL )
608 AND ( p_last_updated_by IS NULL )))
609 AND ( ( Recinfo.creation_date = p_creation_date)
610 OR ( ( Recinfo.creation_date IS NULL )
611 AND ( p_creation_date IS NULL )))
612 AND ( ( Recinfo.created_by = p_created_by)
613 OR ( ( Recinfo.created_by IS NULL )
614 AND ( p_created_by IS NULL )))
615 AND ( ( Recinfo.last_update_login = p_last_update_login)
616 OR ( ( Recinfo.last_update_login IS NULL )
617 AND ( p_last_update_login IS NULL )))
618 AND ( ( Recinfo.item_type = p_item_type)
619 OR ( ( Recinfo.item_type IS NULL )
620 AND ( p_item_type IS NULL )))
621 AND ( ( Recinfo.name = p_name)
622 OR ( ( Recinfo.name IS NULL )
623 AND ( p_name IS NULL )))
624 AND ( ( Recinfo.created_from = p_created_from)
625 OR ( ( Recinfo.created_from IS NULL )
626 AND ( p_created_from IS NULL )))
627 AND ( ( Recinfo.request_id = p_request_id)
628 OR ( ( Recinfo.request_id IS NULL )
629 AND ( p_request_id IS NULL )))
630 AND ( ( Recinfo.program_application_id = p_program_application_id)
631 OR ( ( Recinfo.program_application_id IS NULL )
632 AND ( p_program_application_id IS NULL )))
633 AND ( ( Recinfo.program_id = p_program_id)
634 OR ( ( Recinfo.program_id IS NULL )
635 AND ( p_program_id IS NULL )))
636 AND ( ( Recinfo.program_update_date = p_program_update_date)
637 OR ( ( Recinfo.program_update_date IS NULL )
638 AND ( p_program_update_date IS NULL )))
639 AND ( ( Recinfo.attribute_category = p_attribute_category)
640 OR ( ( Recinfo.attribute_category IS NULL )
641 AND ( p_attribute_category IS NULL )))
642 AND ( ( Recinfo.attribute1 = p_attribute1)
643 OR ( ( Recinfo.attribute1 IS NULL )
644 AND ( p_attribute1 IS NULL )))
645 AND ( ( Recinfo.attribute2 = p_attribute2)
646 OR ( ( Recinfo.attribute2 IS NULL )
647 AND ( p_attribute2 IS NULL )))
648 AND ( ( Recinfo.attribute3 = p_attribute3)
649 OR ( ( Recinfo.attribute3 IS NULL )
650 AND ( p_attribute3 IS NULL )))
651 AND ( ( Recinfo.attribute4 = p_attribute4)
652 OR ( ( Recinfo.attribute4 IS NULL )
653 AND ( p_attribute4 IS NULL )))
654 AND ( ( Recinfo.attribute5 = p_attribute5)
655 OR ( ( Recinfo.attribute5 IS NULL )
656 AND ( p_attribute5 IS NULL )))
657 AND ( ( Recinfo.attribute6 = p_attribute6)
658 OR ( ( Recinfo.attribute6 IS NULL )
659 AND ( p_attribute6 IS NULL )))
660 AND ( ( Recinfo.attribute7 = p_attribute7)
661 OR ( ( Recinfo.attribute7 IS NULL )
662 AND ( p_attribute7 IS NULL )))
663 AND ( ( Recinfo.attribute8 = p_attribute8)
664 OR ( ( Recinfo.attribute8 IS NULL )
665 AND ( p_attribute8 IS NULL )))
666 AND ( ( Recinfo.attribute9 = p_attribute9)
667 OR ( ( Recinfo.attribute9 IS NULL )
668 AND ( p_attribute9 IS NULL )))
669 AND ( ( Recinfo.attribute10 = p_attribute10)
670 OR ( ( Recinfo.attribute10 IS NULL )
671 AND ( p_attribute10 IS NULL )))
672 AND ( ( Recinfo.attribute11 = p_attribute11)
673 OR ( ( Recinfo.attribute11 IS NULL )
674 AND ( p_attribute11 IS NULL )))
675 AND ( ( Recinfo.attribute12 = p_attribute12)
676 OR ( ( Recinfo.attribute12 IS NULL )
677 AND ( p_attribute12 IS NULL )))
678 AND ( ( Recinfo.attribute13 = p_attribute13)
679 OR ( ( Recinfo.attribute13 IS NULL )
680 AND ( p_attribute13 IS NULL )))
681 AND ( ( Recinfo.attribute14 = p_attribute14)
682 OR ( ( Recinfo.attribute14 IS NULL )
683 AND ( p_attribute14 IS NULL )))
684 AND ( ( Recinfo.attribute15 = p_attribute15)
685 OR ( ( Recinfo.attribute15 IS NULL )
686 AND ( p_attribute15 IS NULL )))
687 AND ( ( Recinfo.security_group_id = p_security_group_id)
688 OR ( ( Recinfo.security_group_id IS NULL )
689 AND ( p_security_group_id IS NULL )))
690 AND ( ( Recinfo.object_version_number = p_object_version_number)
691 OR ( ( Recinfo.object_version_number IS NULL )
692 AND ( p_object_version_number IS NULL )))
693 AND ( ( Recinfo.process_id = p_process_id)
694 OR ( ( Recinfo.process_id IS NULL )
695 AND ( p_process_id IS NULL )))
696 AND ( ( Recinfo.process_category = p_process_category)
697 OR ( ( Recinfo.process_category IS NULL )
698 AND ( p_process_category IS NULL )))
699 AND ( ( Recinfo.finance_owner_id = p_finance_owner_id)
700 OR ( ( Recinfo.finance_owner_id IS NULL )
701 AND ( p_finance_owner_id IS NULL )))
702 AND ( ( Recinfo.application_owner_id = p_application_owner_id)
703 OR ( ( Recinfo.application_owner_id IS NULL )
704 AND ( p_application_owner_id IS NULL )))
705 AND ( ( Recinfo.standard_variation = p_standard_variation)
706 OR ( ( Recinfo.standard_variation IS NULL )
707 AND ( p_standard_variation IS NULL )))
708 ) THEN
709 RETURN;
710 ELSE
714 END Lock_Row;
711 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
712 APP_EXCEPTION.RAISE_EXCEPTION;
713 END IF;
715
716 END AMW_PROCESS_PKG;