[Home] [Help]
PACKAGE BODY: APPS.AMW_RISK_CTRL_COUNT_PVT
Source
1 PACKAGE BODY AMW_RISK_CTRL_COUNT_PVT AS
2 /* $Header: amwvrccb.pls 115.9 2003/12/03 02:28:16 abedajna noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_Proc_Org_PVT
7 -- Purpose
8 --
9 -- History
10 -- mpande updated 11/13/2003 bug#3191406
11 --
12 -- NOTE
13 --
14 -- End of Comments
15 -- ===============================================================
16 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_RISK_CTRL_COUNT_PVT';
17 g_file_name CONSTANT VARCHAR2 (12) := 'amwvrccb.pls';
18 g_user_id NUMBER := fnd_global.user_id;
19 g_login_id NUMBER := fnd_global.conc_login_id;
20 --------------------- BEGIN: Declaring internal Procedures ----------------------
21
22 --------------------- END: Declaring internal Procedures ----------------------
23
24 -- ==============================================================================
25 -- Start of Comments
26 -- ==============================================================================
27 -- API Name
28 -- Process_Process_Hierarchy
29 -- Type
30 -- Public
31 -- Pre-Req
32 --
33 -- Parameters
34 --
35 -- IN
36 -- p_process_id IN NUMBER Optional Default = null
37 -- p_organization_id IN NUMBER Optional Default = null
38 -- p_mode IN VARCHAR2 Required Default = 'ASSOCIATE'
39 -- p_apo_type IN apo_type Optional Default = null
40 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
41 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
42 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
43 --
44 -- OUT
45 -- x_return_status OUT VARCHAR2
46 -- x_msg_count OUT NUMBER
47 -- x_msg_data OUT VARCHAR2
48 -- Version : Current version 1.0
49 -- Note:
50 --
51 -- End of Comments
52 -- ==============================================================================
53 --
54 PROCEDURE insert_risk_control_count (
55 p_process_id IN NUMBER := NULL,
56 p_risk_id IN NUMBER := NULL,
57 p_control_id IN NUMBER := NULL,
58 p_process_organization_id IN NUMBER := NULL,
59 p_association_mode IN VARCHAR2 := 'ASSOCIATE',
60 p_object IN VARCHAR2 := 'RISK',
61 p_commit IN VARCHAR2 := fnd_api.g_false,
62 p_validation_level IN NUMBER
63 := fnd_api.g_valid_level_full,
64 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
65 p_api_version_number IN NUMBER,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2
69 ) IS
70 -- l_api_name CONSTANT VARCHAR2 (30) := 'increase_risk_control_count';
71 -- l_api_version_number CONSTANT NUMBER := 1.0;
72 -- x_process_organization_id NUMBER := 0;
73 -- l_process_id NUMBER;
74 -- l_org_id NUMBER;
75 -- --- for risk association to a process, we are passed risk_id and process_id
76 -- --- foll. cursor traverses the process hierarchy tree to get all parent processes
77 -- --- for this process_id
78 -- CURSOR c1 IS
79 -- SELECT NVL (risk_count, 0) AS risk_count, process_id,
80 -- nvl (control_count,0) as control_count,
81 -- NVL (object_version_number, 0) object_version_number
82 -- FROM amw_process
83 -- WHERE process_id IN (
84 -- SELECT p2.process_id
85 -- FROM amw_process p1, amw_process p2, wf_activities wa
86 -- WHERE (p2.NAME, p2.item_type) IN (
87 -- SELECT activity_name, activity_item_type
88 -- FROM wf_process_activities
89 -- CONNECT BY activity_name = PRIOR process_name
90 -- AND activity_item_type =
91 -- PRIOR process_item_type
92 -- START WITH activity_name = p1.NAME
93 -- AND activity_item_type = p1.item_type)
94 -- AND p2.NAME = wa.NAME
95 -- AND p2.item_type = wa.item_type
96 -- AND wa.end_date IS NULL
97 -- AND p1.process_id = p_process_id);
98 --
99 -- -----find the control_count for this risk, and append this
100 -- -----to all the control_counts of upward processes
101 -- cursor cc1(l_risk_id in number) is
102 -- select count(*)
103 -- from amw_control_associations where object_type='RISK'
104 -- and pk1=l_risk_id;
105 --
106 -- --find the organizations to which this risk is associated.
107 -- /**cursor c2 is
108 -- select process_organization_id,organization_id,nvl(risk_count,0) as risk_count,
109 -- nvl(object_version_number,0) object_version_number
110 -- from amw_process_organization where process_id in (
111 -- select p2.process_id
112 -- from amw_process p1, amw_process p2, wf_activities wa
113 -- where (p2.name, p2.item_type) in (select activity_name, activity_item_type
114 -- from WF_PROCESS_ACTIVITIES
115 -- connect by activity_name = prior process_name
116 -- and activity_item_type = prior process_item_type
117 -- start with activity_name=p1.name
118 -- and activity_item_type=p1.item_type)
119 -- and p2.name=wa.name
120 -- and p2.item_type=wa.item_type
121 -- and wa.end_date is null
122 -- and p1.process_id=p_process_id);
123 -- **/
124 -- --find the organizations to which this risk is associated.
125 -- CURSOR c2 IS
126 -- SELECT NVL (risk_count, 0) AS risk_count, process_id, organization_id,
127 -- process_organization_id,
128 -- nvl (control_count,0) as control_count,
129 -- NVL (object_version_number, 0) object_version_number
130 -- FROM amw_process_organization
131 -- WHERE organization_id IN (
132 -- SELECT organization_id
133 -- FROM amw_process_organization
134 -- WHERE process_organization_id =
135 -- p_process_organization_id)
136 -- AND process_id IN (
137 -- SELECT DISTINCT p2.process_id
138 -- FROM amw_process p1,
139 -- amw_process p2,
140 -- amw_process_organization apo1,
141 -- amw_process_organization apo2,
142 -- wf_activities wa
143 -- WHERE (p2.NAME, p2.item_type) IN (
144 -- SELECT activity_name,
145 -- activity_item_type
146 -- FROM wf_process_activities
147 -- CONNECT BY activity_name =
148 -- PRIOR process_name
149 -- AND activity_item_type =
150 -- PRIOR process_item_type
151 -- START WITH activity_name = p1.NAME
152 -- AND activity_item_type =
153 -- p1.item_type)
154 -- AND p2.NAME = wa.NAME
155 -- AND p2.item_type = wa.item_type
156 -- AND wa.end_date IS NULL
157 -- AND p2.process_id = apo2.process_id
158 -- AND apo2.organization_id = apo1.organization_id
159 -- AND p1.process_id = apo1.process_id
160 -- ---and apo1.process_id=142
161 -- AND apo1.process_id IN (
162 -- SELECT process_id
163 -- FROM amw_process_organization
164 -- WHERE process_organization_id =
165 -- p_process_organization_id)
166 -- AND apo1.organization_id IN (
167 -- SELECT organization_id
168 -- FROM amw_process_organization
169 -- WHERE process_organization_id =
170 -- p_process_organization_id));
171 -- ---and apo1.process_organization_id=150
172 --
173 -- cursor cc2 (p_process_organization_id in number,
174 -- p_risk_id IN NUMBER ) is
175 -- select count(*)
176 -- from amw_control_associations
177 -- where object_type='RISK_ORG'
178 -- and pk1 in (
179 -- select risk_association_id
180 -- from amw_risk_associations
181 -- where object_type='PROCESS_ORG' and pk1=p_process_organization_id
182 -- and risk_id = p_risk_id);
183 --
184 -- --find the processes to which this control is associated
185 -- CURSOR c3 IS
186 -- SELECT NVL (control_count, 0) AS control_count, process_id,
187 -- NVL (object_version_number, 0) object_version_number
188 -- FROM amw_process
189 -- WHERE process_id IN (
190 -- SELECT DISTINCT process_id
191 -- FROM amw_process
192 -- WHERE process_id IN (
193 -- SELECT p2.process_id
194 -- FROM amw_process p1,
195 -- amw_process p2,
196 -- wf_activities wa
197 -- WHERE (p2.NAME, p2.item_type) IN (
198 -- SELECT activity_name,
199 -- activity_item_type
200 -- FROM wf_process_activities
201 -- CONNECT BY activity_name =
202 -- PRIOR process_name
203 -- AND activity_item_type =
204 -- PRIOR process_item_type
205 -- START WITH activity_name =
206 -- p1.NAME
207 -- AND activity_item_type =
208 -- p1.item_type)
209 -- AND p2.NAME = wa.NAME
210 -- AND p2.item_type = wa.item_type
211 -- AND wa.end_date IS NULL
212 -- AND p1.process_id IN (
213 -- SELECT pk1
214 -- FROM amw_risk_associations
215 -- WHERE risk_id = p_risk_id
216 -- AND object_type = 'PROCESS')));
217 -- --find the organizations to which this control is associated
218 -- /**cursor c4 is
219 -- select process_organization_id,organization_id,control_count,object_version_number
220 -- from amw_process_organization
221 -- where process_id in (
222 -- select distinct p2.process_id
223 -- from amw_process p1, amw_process p2, wf_activities wa
224 -- where (p2.name, p2.item_type) in (select activity_name, activity_item_type
225 -- from WF_PROCESS_ACTIVITIES
226 -- connect by activity_name = prior process_name
227 -- and activity_item_type = prior process_item_type
228 -- start with activity_name=p1.name
229 -- and activity_item_type=p1.item_type)
230 -- and p2.name=wa.name
231 -- and p2.item_type=wa.item_type
232 -- and wa.end_date is null
233 -- and p1.process_id in (select pk1 from amw_risk_associations where risk_id=p_risk_id and object_type='PROCESS_ORG')
234 -- );
235 -- **/
236 -- CURSOR c4 IS
237 -- SELECT NVL (control_count, 0) AS control_count, process_id,
238 -- organization_id, process_organization_id,
239 -- NVL (object_version_number, 0) object_version_number
240 -- FROM amw_process_organization
241 -- WHERE organization_id IN (
242 -- SELECT organization_id
243 -- FROM amw_process_organization
244 -- WHERE process_organization_id =
245 -- p_process_organization_id)
246 -- AND process_id IN (
247 -- SELECT DISTINCT p2.process_id
248 -- FROM amw_process p1,
249 -- amw_process p2,
250 -- amw_process_organization apo1,
251 -- amw_process_organization apo2,
252 -- wf_activities wa
253 -- WHERE (p2.NAME, p2.item_type) IN (
254 -- SELECT activity_name,
255 -- activity_item_type
256 -- FROM wf_process_activities
257 -- CONNECT BY activity_name =
258 -- PRIOR process_name
259 -- AND activity_item_type =
260 -- PRIOR process_item_type
261 -- START WITH activity_name = p1.NAME
262 -- AND activity_item_type =
263 -- p1.item_type)
264 -- AND p2.NAME = wa.NAME
265 -- AND p2.item_type = wa.item_type
266 -- AND wa.end_date IS NULL
267 -- ----and apo1.process_organization_id in (select pk1 from amw_risk_associations where risk_id=p_risk_id and object_type='PROCESS_ORG')
268 -- AND apo1.process_id IN (
269 -- /* Commneted by moadne bug#3191406
270 -- SELECT pk1
271 -- FROM amw_risk_associations
272 -- WHERE risk_id = p_risk_id
273 -- AND object_type = 'PROCESS')
274 -- */
275 -- SELECT ampo5.process_id
276 -- FROM amw_risk_associations ara1, amw_process_organization ampo5
277 -- WHERE risk_id = p_risk_id
278 -- AND object_type = 'PROCESS_ORG'
279 -- AND ara1.pk1=ampo5.process_organization_id and
280 -- ampo5.process_organization_id = p_process_organization_id)
281 -- AND apo1.organization_id IN (
282 -- SELECT organization_id
283 -- FROM amw_process_organization
284 -- WHERE process_organization_id =
285 -- p_process_organization_id)
286 -- AND apo2.process_id = p2.process_id
287 -- AND apo2.organization_id = apo1.organization_id
288 -- AND p1.process_id = apo1.process_id);
289 -- assoc_risk c1%ROWTYPE;
290 -- assoc_risk_org c2%ROWTYPE;
291 -- assoc_ctrl c3%ROWTYPE;
292 -- assoc_ctrl_org c4%ROWTYPE;
293 --
294 -- l_risk_control_count number := 0;
295 -- l_risk_org_control_count number := 0;
296 --
297 -- cursor cc5 IS
298 -- SELECT control_association_id ,control_id from amw_control_associations
299 -- where object_type='RISK_ORG'
300 -- and pk1 In (
301 -- select risk_association_id
302 -- from amw_risk_associations
303 -- where object_type='PROCESS_ORG' and pk1= p_process_organization_id
304 -- and risk_id = p_risk_id );
305 --
306 -- delete_ctrl_org cc5 %ROWTYPE;
307 --
308 --
309 BEGIN
310 null;
311 -- ---Inserting process_id
312 -- ----commit;
313 -- SAVEPOINT get_process_hierarchy_pvt;
314 -- x_return_status := fnd_api.g_ret_sts_success;
315 -- -- Standard call to check for call compatibility.
316 -- IF NOT fnd_api.compatible_api_call (l_api_version_number,
317 -- p_api_version_number,
318 -- l_api_name,
319 -- g_pkg_name
320 -- ) THEN
321 -- RAISE fnd_api.g_exc_unexpected_error;
322 -- END IF;
323 -- -- Initialize message list if p_init_msg_list is set to TRUE.
324 -- IF fnd_api.to_boolean (p_init_msg_list) THEN
325 -- fnd_msg_pub.initialize;
326 -- END IF;
327 -- -- Debug Message
328 -- amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
329 -- -- Initialize API return status to SUCCESS
330 -- x_return_status := fnd_api.g_ret_sts_success;
331 -- /* Temporarily commenting out the validata session code ..... */
332 -- -- =========================================================================
333 -- -- Validate Environment
334 -- -- =========================================================================
335 -- IF fnd_global.user_id IS NULL THEN
336 -- amw_utility_pvt.error_message
337 -- (p_message_name => 'USER_PROFILE_MISSING');
338 -- RAISE fnd_api.g_exc_error;
339 -- END IF;
340 -- IF (p_object = 'RISK') THEN
341 -- IF (p_process_id IS NULL) THEN
342 -- amw_utility_pvt.error_message
343 -- (p_message_name => 'AMW_NO_PROCESS_ID');
344 -- RAISE fnd_api.g_exc_error;
345 -- ELSIF (p_risk_id IS NULL) THEN
346 -- amw_utility_pvt.error_message
347 -- (p_message_name => 'AMW_NO_RISK_ID');
348 -- RAISE fnd_api.g_exc_error;
349 -- END IF;
350 -- END IF;
351 -- IF (p_object = 'CONTROL') THEN
352 -- IF (p_risk_id IS NULL) THEN
353 -- amw_utility_pvt.error_message
354 -- (p_message_name => 'AMW_NO_RISK_ID');
355 -- RAISE fnd_api.g_exc_error;
356 -- ELSIF (p_control_id IS NULL) THEN
357 -- amw_utility_pvt.error_message
358 -- (p_message_name => 'AMW_NO_CONTROL_ID');
359 -- RAISE fnd_api.g_exc_error;
360 -- END IF;
361 -- END IF;
362 -- IF (p_object = 'RISK_ORG') THEN
363 -- IF (p_process_organization_id IS NULL) THEN
364 -- amw_utility_pvt.error_message
365 -- (p_message_name => 'AMW_NO_ORG_ID'
366 -- );
367 -- RAISE fnd_api.g_exc_error;
368 -- END IF;
369 -- END IF;
370 -- IF (p_object = 'CONTROL_ORG') THEN
371 -- IF (p_process_organization_id IS NULL) THEN
372 -- amw_utility_pvt.error_message
373 -- (p_message_name => 'AMW_NO_ORG_ID'
374 -- );
375 -- RAISE fnd_api.g_exc_error;
376 -- ELSIF (p_risk_id IS NULL) THEN
377 -- amw_utility_pvt.error_message
378 -- (p_message_name => 'AMW_NO_RISK_ID');
379 -- RAISE fnd_api.g_exc_error;
380 -- ELSIF (p_control_id IS NULL) THEN
381 -- amw_utility_pvt.error_message
382 -- (p_message_name => 'AMW_NO_CONTROL_ID');
383 -- RAISE fnd_api.g_exc_error;
384 -- END IF;
385 -- END IF;
386 --
387 --
388 --
389 -- --Commenting out the validation level for now ....
390 -- /***
391 -- IF(p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
392 -- THEN
393 -- -- Invoke validation procedures
394 -- validate_apo_type(
395 -- p_api_version_number => 1.0,
396 -- p_init_msg_list => FND_API.G_FALSE,
397 -- p_validation_level => p_validation_level,
398 -- x_return_status => x_return_status,
399 -- x_msg_count => x_msg_count,
400 -- x_msg_data => x_msg_data);
401 -- END IF;
402 -- ***/
403 --
404 -- IF ( (p_object = 'RISK_ORG') OR (p_object = 'CONTROL_ORG') ) THEN
405 -- select organization_id
406 -- into l_org_id
407 -- from amw_process_organization
408 -- where process_organization_id = p_process_organization_id;
409 -- END IF;
410 --
411 --
412 -- IF (p_association_mode = 'ASSOCIATE') THEN
413 -- IF (p_object = 'RISK') THEN
414 -- --implement risk_count for
415 -- -----dbms_output.put_line('Associating Risk');
416 -- l_risk_control_count := 0;
417 -- OPEN cc1(p_risk_id);
418 -- FETCH cc1 INTO l_risk_control_count;
419 -- CLOSE cc1;
420 --
421 -- OPEN c1;
422 -- LOOP
423 -- FETCH c1
424 -- INTO assoc_risk;
425 -- EXIT WHEN c1%NOTFOUND;
426 -- -----dbms_output.put_line('process_id: '||assoc_risk.process_id);
427 -- --increment risk count for associate
428 -- assoc_risk.risk_count := assoc_risk.risk_count + 1;
429 -- assoc_risk.control_count := assoc_risk.control_count + l_risk_control_count;
430 -- assoc_risk.object_version_number :=
431 -- assoc_risk.object_version_number + 1;
432 --
433 -- --dbms_output.put_line('In the ''RISK'' mode');
434 --
435 --
436 --
437 -- --update amw_process' risk_count
438 -- UPDATE amw_process
439 -- SET risk_count = assoc_risk.risk_count,
440 -- control_count = assoc_risk.control_count,
441 -- object_version_number = assoc_risk.object_version_number,
442 -- last_updated_by = g_user_id,
443 -- last_update_date = SYSDATE,
444 -- last_update_login = g_login_id
445 -- WHERE process_id = assoc_risk.process_id;
446 -- END LOOP;
447 -- CLOSE c1;
448 -- ELSIF (p_object = 'RISK_ORG') THEN
449 -- AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
450 ---- /*
451 ---- l_risk_org_control_count := 0;
452 ---- OPEN cc2(p_process_organization_id,p_risk_id );
453 ---- FETCH cc2 INTO l_risk_org_control_count;
454 ---- CLOSE cc2;
455 ---- */
456 ----
457 ---- OPEN c2;
458 ---- LOOP
459 ---- FETCH c2
460 ---- INTO assoc_risk_org;
461 ---- EXIT WHEN c2%NOTFOUND;
462 ---- -----dbms_output.put_line('process_id: '||assoc_risk_org.process_id||' organization_id: '||assoc_risk_org.organization_id);
463 ---- --increment risk count for associate
464 ---- assoc_risk_org.risk_count := assoc_risk_org.risk_count + 1;
465 ---- /** Commenting out Control_Count increment on 11/12/2003
466 ---- because in Risk_Org context, Risk association does not mean
467 ---- Control association
468 ---- **/
469 ---- ---assoc_risk_org.control_count := assoc_risk_org.control_count + l_risk_org_control_count;
470 ---- assoc_risk_org.object_version_number := assoc_risk_org.object_version_number + 1;
471 --
472 -- --update amw_process' risk_count
473 -- UPDATE amw_process_organization
474 -- SET risk_count = assoc_risk_org.risk_count,
475 -- /* Commenting below for above reasons **/
476 -- ---control_count = assoc_risk_org.control_count,
477 -- object_version_number = assoc_risk_org.object_version_number,
478 -- last_updated_by = g_user_id,
479 -- last_update_date = SYSDATE,
480 -- last_update_login = g_login_id
481 -- WHERE process_organization_id =
482 -- assoc_risk_org.process_organization_id;
483 -- END LOOP;
484 -- CLOSE c2;
485 -- ELSIF (p_object = 'CONTROL') THEN
486 -- --associate a control
487 -- -----dbms_output.put_line('Associating Control');
488 -- OPEN c3;
489 -- LOOP
490 -- FETCH c3
491 -- INTO assoc_ctrl;
492 -- EXIT WHEN c3%NOTFOUND;
493 -- -----dbms_output.put_line('process_id: '||assoc_ctrl.process_id);
494 -- --increment risk count for associate
495 -- assoc_ctrl.control_count := assoc_ctrl.control_count + 1;
496 -- assoc_ctrl.object_version_number :=
497 -- assoc_ctrl.object_version_number + 1;
498 -- --update amw_process' risk_count
499 -- UPDATE amw_process
500 -- SET control_count = assoc_ctrl.control_count,
501 -- object_version_number = assoc_ctrl.object_version_number,
502 -- last_updated_by = g_user_id,
503 -- last_update_date = SYSDATE,
504 -- last_update_login = g_login_id
505 -- WHERE process_id = assoc_ctrl.process_id;
506 -- END LOOP;
507 -- CLOSE c3;
508 -- ELSIF (p_object = 'CONTROL_ORG') THEN
509 -- AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
510 ---- --associate a control
511 ---- -----dbms_output.put_line('Associating Control-Org');
512 ---- OPEN c4;
513 ---- LOOP
514 ---- FETCH c4
515 ---- INTO assoc_ctrl_org;
516 ---- EXIT WHEN c4%NOTFOUND;
517 ---- -----dbms_output.put_line('process_id: '||assoc_ctrl_org.process_id||' organization_id: '||assoc_ctrl_org.organization_id);
518 ---- --increment risk count for associate
519 ---- assoc_ctrl_org.control_count := assoc_ctrl_org.control_count + 1;
520 ---- assoc_ctrl_org.object_version_number :=
521 ---- assoc_ctrl_org.object_version_number + 1;
522 ---- --update amw_process' risk_count
523 ---- UPDATE amw_process_organization
524 ---- SET control_count = assoc_ctrl_org.control_count,
525 -- object_version_number =
526 -- assoc_ctrl_org.object_version_number,
527 -- last_updated_by = g_user_id,
528 -- last_update_date = SYSDATE,
529 -- last_update_login = g_login_id
530 -- WHERE process_organization_id =
531 -- assoc_ctrl_org.process_organization_id;
532 -- END LOOP;
533 -- CLOSE c4;
534 -- END IF;
535 -- ELSIF (p_association_mode = 'DISASSOCIATE') THEN
536 -- IF (p_object = 'RISK') THEN
537 -- --associate a process
538 -- -----dbms_output.put_line('Disassociating Risk');
539 -- l_risk_control_count := 0;
540 -- OPEN cc1(p_risk_id);
541 -- FETCH cc1 INTO l_risk_control_count;
542 -- CLOSE cc1;
543 --
544 -- OPEN c1;
545 -- LOOP
546 -- FETCH c1
547 -- INTO assoc_risk;
548 -- EXIT WHEN c1%NOTFOUND;
549 -- -----dbms_output.put_line('process_id: '||assoc_risk.process_id);
550 -- --increment risk count for associate
551 -- assoc_risk.risk_count := assoc_risk.risk_count - 1;
552 -- assoc_risk.control_count := assoc_risk.control_count - l_risk_control_count;
553 -- assoc_risk.object_version_number := assoc_risk.object_version_number + 1;
554 -- --update amw_process' risk_count
555 -- UPDATE amw_process
556 -- SET risk_count = assoc_risk.risk_count,
557 -- control_count = assoc_risk.control_count,
558 -- object_version_number = assoc_risk.object_version_number,
559 -- last_updated_by = g_user_id,
560 -- last_update_date = SYSDATE,
561 -- last_update_login = g_login_id
562 -- WHERE process_id = assoc_risk.process_id;
563 -- END LOOP;
564 -- CLOSE c1;
565 -- ELSIF (p_object = 'RISK_ORG') THEN
566 -- AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
567 --
571 ---- CLOSE cc2;
568 ---- l_risk_org_control_count := 0;
569 ---- OPEN cc2(p_process_organization_id,p_risk_id);
570 ---- FETCH cc2 INTO l_risk_org_control_count;
572 ----
573 ---- --mpande 11/13/2003
574 -- OPEN cc5;
575 -- LOOP
576 -- FETCH cc5 INTO delete_ctrl_org;
577 -- EXIT WHEN cc5%NOTFOUND;
578 --
579 --
580 -- /* - added mpande 11/14/2003 */
581 -- delete from amw_ap_associations
582 -- where object_type='CTRL_ORG'
583 -- and pk1 = ( SELECT organization_id from amw_process_organization
584 -- where process_organization_id = p_process_organization_id )
585 -- and pk2 = ( SELECT process_id from amw_process_organization
586 -- where process_organization_id = p_process_organization_id )
587 -- AND pk3 = delete_ctrl_org.control_id
588 -- and
589 -- not exists ( select control_id from amw_control_associations aca, amw_risk_associations ara
590 -- where aca.pk1= ara.risk_association_id
591 -- and ara.object_type = 'PROCESS_ORG'
592 -- and aca.object_type = 'RISK_ORG'
593 -- and control_id = delete_ctrl_org.control_id ) ;
594 --
595 -- delete from amw_control_associations
596 -- where control_association_id = delete_ctrl_org.control_association_id ;
597 -- /* addition ends */
598 -- END LOOP ;
599 -- CLOSE cc5 ;
600 --
601 -- OPEN c2;
602 -- LOOP
603 -- FETCH c2
604 -- INTO assoc_risk_org;
605 -- EXIT WHEN c2%NOTFOUND;
606 -- -----dbms_output.put_line('process_id: '||assoc_risk_org.process_id||' organization_id: '||assoc_risk_org.organization_id);
607 -- --increment risk count for associate
608 -- assoc_risk_org.risk_count := assoc_risk_org.risk_count - 1;
609 -- /** Commenting out Control_Count decrease on 11/12/2003
610 -- because in Risk_Org context, Risk disassociation does not mean
611 -- Control disassociation
612 -- **/
613 --
614 --
615 --
616 -- -- mpande 11/13/2003 control_count does get reduced when risk is disassocaited
617 -- assoc_risk_org.control_count := assoc_risk_org.control_count - l_risk_org_control_count;
618 -- assoc_risk_org.object_version_number := assoc_risk_org.object_version_number + 1;
619 -- --update amw_process' risk_count
620 -- UPDATE amw_process_organization
621 -- SET risk_count = assoc_risk_org.risk_count,
622 -- /** Commented below for above reasons **/ -- mpande 11/13/2003
623 -- control_count = assoc_risk_org.control_count,
624 -- object_version_number = assoc_risk_org.object_version_number,
625 -- last_updated_by = g_user_id,
626 -- last_update_date = SYSDATE,
627 -- last_update_login = g_login_id
628 -- WHERE process_organization_id = assoc_risk_org.process_organization_id;
629 -- END LOOP;
630 -- CLOSE c2;
631 -- ELSIF (p_object = 'CONTROL') THEN
632 -- --disassociate a control
633 -- -----dbms_output.put_line('Disassociating Control');
634 -- -- added 11/13/2003 mpande to delete ap
635 --
636 -- OPEN c3;
637 -- LOOP
638 -- FETCH c3
639 -- INTO assoc_ctrl;
640 -- EXIT WHEN c3%NOTFOUND;
641 -- -----dbms_output.put_line('process_id: '||assoc_ctrl.process_id);
642 -- --increment risk count for associate
643 -- assoc_ctrl.control_count := assoc_ctrl.control_count - 1;
644 -- assoc_ctrl.object_version_number :=
645 -- assoc_ctrl.object_version_number + 1;
646 -- --update amw_process' risk_count
647 -- UPDATE amw_process
648 -- SET control_count = assoc_ctrl.control_count,
649 -- object_version_number = assoc_ctrl.object_version_number,
650 -- last_updated_by = g_user_id,
651 -- last_update_date = SYSDATE,
652 -- last_update_login = g_login_id
653 -- WHERE process_id = assoc_ctrl.process_id;
654 -- END LOOP;
655 -- CLOSE c3;
656 -- ELSIF (p_object = 'CONTROL_ORG') THEN
657 -- AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
658 ---- /* - added mpande 11/14/2003 */
659 ---- delete from amw_ap_associations
660 ---- where object_type='CTRL_ORG'
661 ---- and pk1 = ( SELECT organization_id from amw_process_organization
662 ---- where process_organization_id = p_process_organization_id )
663 ---- and pk2 = ( SELECT process_id from amw_process_organization
664 ---- where process_organization_id = p_process_organization_id )
665 ---- AND pk3 = p_control_id
666 ---- and
667 ---- not exists ( select control_id from amw_control_associations aca, amw_risk_associations ara
668 ---- where aca.pk1= ara.risk_association_id
669 ---- and ara.object_type = 'PROCESS_ORG'
670 ---- and aca.object_type = 'RISK_ORG'
671 -- and control_id = p_control_id ) ;
672 --
673 --
674 -- OPEN c4;
675 -- LOOP
676 -- FETCH c4
677 -- INTO assoc_ctrl_org;
678 -- EXIT WHEN c4%NOTFOUND;
682 -- assoc_ctrl_org.object_version_number :=
679 -- -----dbms_output.put_line('process_id: '||assoc_ctrl_org.process_id||' organization_id: '||assoc_ctrl_org.organization_id);
680 -- --increment risk count for associate
681 -- assoc_ctrl_org.control_count := assoc_ctrl_org.control_count - 1;
683 -- assoc_ctrl_org.object_version_number + 1;
684 -- --update amw_process' risk_count
685 -- UPDATE amw_process_organization
686 -- SET control_count = assoc_ctrl_org.control_count,
687 -- object_version_number =
688 -- assoc_ctrl_org.object_version_number,
689 -- last_updated_by = g_user_id,
690 -- last_update_date = SYSDATE,
691 -- last_update_login = g_login_id
692 -- WHERE process_organization_id =
693 -- assoc_ctrl_org.process_organization_id;
694 -- END LOOP;
695 -- CLOSE c4;
696 -- END IF;
697 -- END IF;
698 -- =========================================================================
699 -- End Validate Environment
700 -- =========================================================================
701 -- End commenting the session validation code ....
702 -- IF x_return_status <> fnd_api.g_ret_sts_success THEN
703 -- RAISE fnd_api.g_exc_error;
704 -- END IF;
705 -- -- Standard check for p_commit
706 -- IF fnd_api.to_boolean (p_commit) THEN
707 -- COMMIT WORK;
708 -- END IF;
709 -- --Debug Message
710 -- amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
711 -- -- Standard call to get message count and if count is 1, get message info.
712 -- fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
713 -- EXCEPTION
714 -- WHEN fnd_api.g_exc_error THEN
715 -- ROLLBACK TO get_process_hierarchy_pvt;
716 -- x_return_status := fnd_api.g_ret_sts_error;
717 -- -- Standard call to get message count and if count=1, get the message
718 -- fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
719 -- p_count => x_msg_count,
720 -- p_data => x_msg_data
721 -- );
722 -- WHEN fnd_api.g_exc_unexpected_error THEN
723 -- ROLLBACK TO get_process_hierarchy_pvt;
724 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
725 -- -- Standard call to get message count and if count=1, get the message
726 -- fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
727 -- p_count => x_msg_count,
728 -- p_data => x_msg_data
729 -- );
730 -- WHEN OTHERS THEN
731 -- ROLLBACK TO get_process_hierarchy_pvt;
732 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
733 -- IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
734 -- fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
735 -- END IF;
736 -- -- Standard call to get message count and if count=1, get the message
737 -- fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
738 -- p_count => x_msg_count,
739 -- p_data => x_msg_data
740 -- );
741 END insert_risk_control_count;
742 -- ==============================================================================
743 -- Start of Comments
744 -- ==============================================================================
745 -- API Name
746 -- Process_Amw_Process_Org
747 -- Type
748 -- Private
749 -- Pre-Req
750 --
751 -- Parameters
752 --
753 -- IN
754 -- p_apo_type IN apo_type Optional Default = null
755 -- p_do_insert IN VARCHAR2 Optional Default = 'INSERT'
756 -- p_org_count IN NUMBER Optional Default = 0
757 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
758 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
759 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
760 --
761 --
762 -- OUT
763 -- x_return_status OUT VARCHAR2
764 -- x_msg_count OUT NUMBER
765 -- x_msg_data OUT VARCHAR2
766 -- Version : Current version 1.0
767 -- Note:
768 --
769 -- End of Comments
770 -- ==============================================================================
771 --/*
772 -- PROCEDURE validate_apo_type (
776 -- := fnd_api.g_valid_level_full,
773 -- p_api_version_number IN NUMBER,
774 -- p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
775 -- p_validation_level IN NUMBER
777 -- x_return_status OUT NOCOPY VARCHAR2,
778 -- x_msg_count OUT NOCOPY NUMBER,
779 -- x_msg_data OUT NOCOPY VARCHAR2
780 -- ) IS
781 -- l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Process';
782 -- l_api_version_number CONSTANT NUMBER := 1.0;
783 -- l_object_version_number NUMBER;
784 -- --l_process_rec AMW_Process_PVT.process_rec_type;
785 -- BEGIN
786 --
787 -- -- Standard Start of API savepoint
788 -- SAVEPOINT validate_process_pvt;
789 -- -- Standard call to check for call compatibility.
790 -- IF NOT fnd_api.compatible_api_call (l_api_version_number,
791 -- p_api_version_number,
792 -- l_api_name,
793 -- g_pkg_name
794 -- ) THEN
795 -- RAISE fnd_api.g_exc_unexpected_error;
796 -- END IF;
797 -- -- Initialize message list if p_init_msg_list is set to TRUE.
798 -- IF fnd_api.to_boolean (p_init_msg_list) THEN
799 -- fnd_msg_pub.initialize;
800 -- END IF;
801 -- IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
802 -- check_apo_row (p_validation_mode => jtf_plsql_api.g_update,
803 -- x_return_status => x_return_status
804 -- );
805 -- IF x_return_status = fnd_api.g_ret_sts_error THEN
806 -- RAISE fnd_api.g_exc_error;
807 -- ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
808 -- RAISE fnd_api.g_exc_unexpected_error;
809 -- END IF;
810 -- END IF;
811 -- -- Debug Message
812 -- amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
813 -- -- Initialize API return status to SUCCESS
814 -- x_return_status := fnd_api.g_ret_sts_success;
815 -- -- Debug Message
816 -- amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
817 -- -- Standard call to get message count and if count is 1, get message info.
818 -- fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
819 -- EXCEPTION
820 -- WHEN amw_utility_pvt.resource_locked THEN
821 -- x_return_status := fnd_api.g_ret_sts_error;
822 -- amw_utility_pvt.error_message
823 -- (p_message_name => 'AMW_API_RESOURCE_LOCKED');
824 -- WHEN fnd_api.g_exc_error THEN
825 -- ROLLBACK TO validate_process_pvt;
826 -- x_return_status := fnd_api.g_ret_sts_error;
827 -- -- Standard call to get message count and if count=1, get the message
828 -- fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
829 -- p_count => x_msg_count,
830 -- p_data => x_msg_data
831 -- );
832 -- WHEN fnd_api.g_exc_unexpected_error THEN
833 -- ROLLBACK TO validate_process_pvt;
834 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
835 -- -- Standard call to get message count and if count=1, get the message
836 -- fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
837 -- p_count => x_msg_count,
838 -- p_data => x_msg_data
839 -- );
840 -- WHEN OTHERS THEN
841 -- ROLLBACK TO validate_process_pvt;
842 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
843 -- IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
844 -- fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
845 -- END IF;
846 -- -- Standard call to get message count and if count=1, get the message
847 -- fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
848 -- p_count => x_msg_count,
849 -- p_data => x_msg_data
850 -- );
851 -- END validate_apo_type;
852 -- ==============================================================================
853 -- Start of Comments
854 -- ==============================================================================
855 -- API Name
856 -- Check_Apo_Row
857 -- Type
858 -- Private
859 -- Pre-Req
860 --
861 -- Parameters
862 --
863 -- IN
864 -- p_apo_type IN apo_type Required
865 -- p_validation_mode IN VARCHAR2 Optional Default = JTF_PLSQL_API.g_create
866 --
867 -- OUT
868 -- x_return_status OUT VARCHAR2
869 -- Version : Current version 1.0
870 -- Note:
871 --
872 -- End of Comments
873 -- ==============================================================================
874 --
875 -- PROCEDURE check_apo_row (
876 -- p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create,
877 -- x_return_status OUT NOCOPY VARCHAR2
878 -- ) IS
879 -- BEGIN
880 -- x_return_status := fnd_api.g_ret_sts_success;
881 -- /*
882 -- IF p_validation_mode = jtf_plsql_api.g_create THEN
883 -- IF 2 IS NULL THEN
884 -- amw_utility_pvt.error_message
885 -- (p_message_name => 'AMW_NO_ORGANIZATION_ID');
886 -- x_return_status := fnd_api.g_ret_sts_error;
887 -- RETURN;
888 -- END IF;
889 -- IF 3 IS NULL THEN
890 -- amw_utility_pvt.error_message
891 -- (p_message_name => 'AMW_NO_PROCESS_ID');
892 -- x_return_status := fnd_api.g_ret_sts_error;
893 -- RETURN;
894 -- END IF;
895 -- END IF;
896 --
897 -- END check_apo_row;
898 -- */
899 END amw_risk_ctrl_count_pvt;