[Home] [Help]
PACKAGE BODY: APPS.AMW_PROC_ORG_HIERARCHY_PVT
Source
1 PACKAGE BODY AMW_PROC_ORG_HIERARCHY_PVT as
2 /* $Header: amwvpohb.pls 120.0 2005/05/31 20:34:58 appldev 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 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PROC_ORG_HIERARCHY_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwvpohb.pls';
18
19 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
20 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
21 G_RISK_COUNT number := 0;
22 G_control_COUNT number := 0;
23
24 --------------------- BEGIN: Declaring internal Procedures ----------------------
25
26 --------------------- END: Declaring internal Procedures ----------------------
27 -- ==============================================================================
28 -- Start of Comments
29 -- ==============================================================================
30 -- API Name
31 -- Process_Process_Hierarchy
32 -- Type
33 -- Public
34 -- Pre-Req
35 --
36 -- Parameters
37 --
38 -- IN
39 -- p_process_id IN NUMBER Optional Default = null
40 -- p_organization_id IN NUMBER Optional Default = null
41 -- p_mode IN VARCHAR2 Required Default = 'ASSOCIATE'
42 -- p_apo_type IN apo_type Optional Default = null
43 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
44 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
45 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
46 --
47 -- OUT
48 -- x_return_status OUT VARCHAR2
49 -- x_msg_count OUT NUMBER
50 -- x_msg_data OUT VARCHAR2
51 -- Version : Current version 1.0
52 -- Note:
53 --
54 -- End of Comments
55 -- ==============================================================================
56 --
57 procedure process_process_hierarchy(
58 p_process_id in number := null,
59 p_organization_id in number := null,
60 p_mode in varchar2 := 'ASSOCIATE',
61 p_level in number := 0,
62 p_apo_type in apo_type := g_miss_apo_type,
63 p_commit in varchar2 := FND_API.G_FALSE,
64 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
65 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
66 x_return_status out nocopy varchar2,
67 x_msg_count out nocopy number,
68 x_msg_data out nocopy varchar2
69 )
70
71 is
72 L_API_NAME CONSTANT VARCHAR2(30) := 'Process_Process_Hierarchy';
73 x_process_organization_id number := 0;
74 ----l_top_process_id number := p_process_id;
75 l_top_process_id number;
76
77 l_return_status varchar2(30) := 'false';
78 l_msg_count number;
79 l_msg_data varchar2(3000) := 'empty';
80
81 /**
82 cursor c1 is
83 select child_process_id,parent_process_id,parent_process_name,process_name from amw_process_hierarchy_v
84 start with child_process_id=p_process_id
85 connect by prior child_process_id=parent_process_id;
86 **/
87
88 cursor c1 (l_cpid number) is
89 select parent_process_id,child_process_id,parent_process_name,process_name
90 from amw_process_hierarchy_v
91 where parent_process_id=l_cpid;
92
93 cursor risk_ctrl is
94 select nvl(risk_count,0) as risk_count,
95 nvl(control_count,0) as control_count
96 from amw_process
97 where process_id=p_process_id;
98 ---and organization_id=p_organization_id;
99
100 l_risk_ctrl risk_ctrl%rowtype;
101
102 l_process_id c1%rowtype;
103 l_apo_type apo_type := p_apo_type;
104 l_level number := 0;
105 l_ppid number := 0;
106 l_mode varchar2(30) := p_mode;
107
108 begin
109 savepoint process_process_hierarchy_pub;
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 -- Initialize message list if p_init_msg_list is set to TRUE.
113 IF FND_API.to_Boolean( p_init_msg_list )
114 THEN
115 FND_MSG_PUB.initialize;
116 END IF;
117
118 /* Temporarily commenting out the validata session code ..... */
119 -- =========================================================================
120 -- Validate Environment
121 -- =========================================================================
122 IF FND_GLOBAL.User_Id IS NULL
123 THEN
124 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127
128 IF(p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
129 THEN
130 -- Debug message
131 AMW_UTILITY_PVT.debug_message('Private API: Validate_Process');
132
133 -- Invoke validation procedures
134 l_apo_type.process_id := p_process_id;
135 l_apo_type.organization_id := p_organization_id;
136 l_level := p_level;
137
138 validate_apo_type(
139 p_api_version_number => 1.0,
140 p_init_msg_list => FND_API.G_FALSE,
141 p_validation_level => p_validation_level,
142 p_apo_type => l_apo_type,
143 x_return_status => x_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data);
146 END IF;
147
148 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
149 RAISE FND_API.G_EXC_ERROR;
150 END IF;
151 -- =========================================================================
152 -- End Validate Environment
153 -- =========================================================================
154 -- End commenting the session validation code ....
155
156
157 if p_process_id is null or p_organization_id is null
158 then
159 -----------DBMS_OUTPUT.PUT_LINE ('ERROR -- exiting');
160 RAISE FND_API.G_EXC_ERROR;
161 end if;
162
163 -- open c1;
164 -- loop
165 -- fetch c1 into l_process_id;
166 -- EXIT WHEN c1%NOTFOUND;
167
168 if(p_level=0)then
169 l_top_process_id := p_process_id;
170
171 ---select parent_process_id into l_ppid from amw_process_hierarchy_v
172 ---where child_process_id=p_process_id;
173
174 l_ppid := get_parent_process_id(p_process_id,p_organization_id);
175 ----dbms_output.put_line('l_ppid: '||l_ppid);
176 ---end if;
177
178 associate_process_org(
179 p_apo_type => l_apo_type,
180 p_process_id => p_process_id,
181 p_top_process_id => l_top_process_id,
182 p_organization_id => p_organization_id,
183 p_parent_process_id => l_ppid,
184 p_mode => p_mode,
185 p_commit => p_commit,
186 p_validation_level => p_validation_level,
187 p_init_msg_list => p_init_msg_list,
188 x_return_status => x_return_status,
189 x_msg_count => x_msg_count,
190 x_msg_data => x_msg_data
191 );
192 end if;
193
194 for l_process_id in c1(p_process_id) loop
195 exit when c1%notfound;
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197
198 ---dbms_output.put_line('child_process_id: '||l_process_id.child_process_id);
199
200 associate_process_org(
201 p_apo_type => l_apo_type,
202 p_process_id => l_process_id.child_process_id,
203 p_top_process_id => l_top_process_id,
204 p_organization_id => p_organization_id,
205 p_parent_process_id => l_process_id.parent_process_id,
206 p_mode => l_mode,
207 p_commit => p_commit,
208 p_validation_level => p_validation_level,
209 p_init_msg_list => p_init_msg_list,
210 x_return_status => x_return_status,
211 x_msg_count => x_msg_count,
212 x_msg_data => x_msg_data
213 );
214
215 l_level := l_level+1;
216 process_process_hierarchy(p_process_id =>l_process_id.child_process_id,
217 p_organization_id => p_organization_id,
218 p_mode => l_mode,
219 p_level => l_level,
220 p_commit => p_commit,
221 x_return_status => x_return_status,
222 x_msg_count => x_msg_count,
223 x_msg_data => x_msg_data);
224 end loop;
225
226 ---dbms_output.put_line('Done with the recursion loop');
227 /*
228 open risk_ctrl;
229 loop
230 fetch risk_ctrl into l_risk_ctrl;
231 exit when risk_ctrl%notfound;
232 end loop;
233 close risk_ctrl;
234 */
235
236
237 if(p_level=0)then
238 process_hierarchy_count(
239 p_process_id =>p_process_id,
240 p_organization_id =>p_organization_id,
241 p_risk_count =>l_risk_ctrl.risk_count,
242 p_control_count =>l_risk_ctrl.control_count,
243 p_mode =>p_mode,
244 p_commit =>p_commit,
245 x_return_status =>l_return_status,
246 x_msg_count =>l_msg_count,
247 x_msg_data =>l_msg_data
248 );
249 end if;
250
251 /****
252 associate_process_org(
253 p_apo_type => l_apo_type,
254 p_process_id => l_process_id.child_process_id,
255 p_top_process_id => l_top_process_id,
256 p_organization_id => p_organization_id,
257 p_parent_process_id => l_process_id.parent_process_id,
258 p_mode => p_mode,
259 p_commit => p_commit,
260 p_validation_level => p_validation_level,
261 p_init_msg_list => p_init_msg_list,
262 x_return_status => x_return_status,
263 x_msg_count => x_msg_count,
264 x_msg_data => x_msg_data
265 );
266 --------dbms_output.put_line('Process_Process_Hierarchy x_return_status: '||x_return_status);
267 ---------dbms_output.put_line('FND_API.G_RET_STS_SUCCESS: '||FND_API.G_RET_STS_SUCCESS);
268
269 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
270 RAISE FND_API.G_EXC_ERROR;
271 END IF;
272 ****/
273
274 -- end loop;
275 -- close c1;
276
277 --Debug Message
278 AMW_UTILITY_PVT.debug_message('Private API: ' || L_API_NAME || 'end');
279
280 -- Standard call to get message count and if count is 1, get message info.
281 FND_MSG_PUB.Count_And_Get(
282 p_count => x_msg_count,
283 p_data => x_msg_data
284 );
285
286 exception
287 WHEN FND_API.G_EXC_ERROR THEN
288 ROLLBACK TO process_process_hierarchy_pub;
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 -- Standard call to get message count and if count=1, get the message
291 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
292
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294 ROLLBACK TO process_process_hierarchy_pub;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 -- Standard call to get message count and if count=1, get the message
297 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
298
299 WHEN OTHERS THEN
300 ROLLBACK TO process_process_hierarchy_pub;
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
303 THEN
304 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
305 END IF;
306 -- Standard call to get message count and if count=1, get the message
307 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
308 end process_process_hierarchy;
309
310 -- ==============================================================================
311 -- Start of Comments
312 -- ==============================================================================
313 -- API Name
314 -- Associate_Process_Org
315 -- Type
316 -- Private
317 -- Pre-Req
318 --
319 -- Parameters
320 --
321 -- IN
322 -- p_apo_type IN apo_type Optional Default = null
323 -- p_process_id IN NUMBER Optional Default = null
324 -- p_top_process_id IN NUMBER Optional Default = null
325 -- p_organization_id IN NUMBER Optional Default = null
326 -- p_parent_process_id IN NUMBER Optional Default = null
327 -- p_mode IN VARCHAR2 Required Default = 'ASSOCIATE'
328 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
329 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
330 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
331 --
332 -- OUT
333 -- x_return_status OUT VARCHAR2
334 -- x_msg_count OUT NUMBER
335 -- x_msg_data OUT VARCHAR2
336 -- Version : Current version 1.0
337 -- Note:
338 --
339 -- End of Comments
340 -- ==============================================================================
341 --
342
343 procedure associate_process_org(
344 p_apo_type in apo_type := g_miss_apo_type,
345 p_process_id in number := null,
346 p_top_process_id in number := null,
347 p_organization_id in number := null,
348 p_parent_process_id in number := null,
349 p_rcm_assoc in varchar2 := 'N',
350 p_batch_id in number := null,
351 p_rcm_org_intf_id in number := null,
352 p_risk_id in number := null,
353 p_control_id in number := null,
354 p_mode in varchar2 := 'ASSOCIATE',
355
356 p_commit in varchar2 := FND_API.G_FALSE,
357 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
358 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
359
360 x_return_status out nocopy varchar2,
361 x_msg_count out nocopy number,
362 x_msg_data out nocopy varchar2
363 )
364
365 is
366 L_API_NAME CONSTANT VARCHAR2(30) := 'Associate_Process_Org';
367 x_process_organization_id number := 0;
368 l_apo_type apo_type := p_apo_type;
369
370 cursor c1 is
371 select significant_process_flag,standard_process_flag,approval_status,
372 certification_status,process_category,process_owner_id,process_id,created_from,
373 request_id,program_application_id,program_id,program_update_date,
374 attribute_category,attribute1,attribute2,attribute3,attribute4,
375 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
376 attribute11,attribute12,attribute13,attribute14,attribute15,
377 security_group_id,object_version_number,
378 nvl(control_count,0) as control_count,
379 nvl(risk_count,0) as risk_count,nvl(org_count,0) as org_count,
380 finance_owner_id,application_owner_id
381 from amw_process where process_id=p_process_id;
382
383 l_amwp_rowtype c1%rowtype;
384 l_amwp amw_process_organization%rowtype;
385 l_count number := 0;
386 l_do_insert varchar2(30) := 'INSERT';
387 l_org_count number := 0;
388 l_parent_process_end_date date := null;
389
390 cursor cc1 is
391 select count(*) row_count from amw_process_organization
392 where process_id=p_process_id and organization_id=p_organization_id;
393
394 cc1_row cc1%rowtype;
395
396 cursor cc2 is
397 select count(*) row_count from amw_process_organization
398 where process_id=p_parent_process_id and organization_id=p_organization_id;
399
400 cc2_row cc2%rowtype;
401
402 cursor cc3 is
403 select end_date from amw_process_organization
404 where process_id=p_parent_process_id and organization_id=p_organization_id;
405
406 cc3_row cc3%rowtype;
407
408 cursor cc4 is
409 select count(*) row_count from amw_process_organization
410 where process_id=p_parent_process_id and organization_id=p_organization_id;
411
412 cc4_row cc4%rowtype;
413
414 cursor cc5 is
415 select end_date from amw_process_organization
416 where process_id=p_parent_process_id and organization_id=p_organization_id;
417
418 cc5_row cc5%rowtype;
419
420 begin
421 savepoint associate_process_org_pvt;
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423
424 ----DBMS_OUTPUT.PUT_LINE ( 'associate_process_org:' );
425 ----DBMS_OUTPUT.PUT_LINE ( 'top process id being passed to INSERT_IN_AMWPO:'|| p_top_process_id);
426
427 open c1;
428 loop
429 fetch c1 into l_amwp_rowtype;
430 exit when c1%notfound;
431
432 if(l_amwp_rowtype.org_count is null) then
433 l_org_count := 0;
434 else
435 l_org_count := l_amwp_rowtype.org_count;
436 end if;
437
438 l_apo_type.control_count := l_amwp_rowtype.control_count;
439 l_apo_type.risk_count := l_amwp_rowtype.risk_count;
440 l_apo_type.TOP_PROCESS_ID := p_top_process_id;
441 l_apo_type.process_organization_id := null;
442 l_apo_type.last_update_date := sysdate;
443 l_apo_type.last_updated_by := G_USER_ID;
444 l_apo_type.creation_date := sysdate;
445 l_apo_type.created_by := G_USER_ID;
446 l_apo_type.LAST_UPDATE_LOGIN := G_LOGIN_ID;
447 l_apo_type.PROCESS_ID := p_PROCESS_ID;
448 l_apo_type.STANDARD_PROCESS_FLAG := l_amwp_rowtype.STANDARD_PROCESS_FLAG;
449 l_apo_type.RISK_CATEGORY := null;
450 l_apo_type.APPROVAL_STATUS := l_amwp_rowtype.APPROVAL_STATUS;
451 l_apo_type.CERTIFICATION_STATUS := l_amwp_rowtype.CERTIFICATION_STATUS;
452 l_apo_type.LAST_AUDIT_STATUS := null;
453 l_apo_type.ORGANIZATION_ID := p_ORGANIZATION_ID;
454 l_apo_type.LAST_CERTIFICATION_DATE := null;
455 l_apo_type.LAST_AUDIT_DATE := null;
456 l_apo_type.NEXT_AUDIT_DATE := null;
457 l_apo_type.application_owner_id := l_amwp_rowtype.APPLICATION_OWNER_ID;
458 l_apo_type.process_owner_id := l_amwp_rowtype.process_owner_id;
459 l_apo_type.PROCESS_CATEGORY_CODE := l_amwp_rowtype.PROCESS_CATEGORY;
460 l_apo_type.SIGNIFICANT_PROCESS_FLAG := l_amwp_rowtype.SIGNIFICANT_PROCESS_FLAG;
461 l_apo_type.finance_owner_id := l_amwp_rowtype.FINANCE_OWNER_ID;
462 l_apo_type.CREATED_FROM := l_amwp_rowtype.CREATED_FROM;
463 l_apo_type.REQUEST_ID := l_amwp_rowtype.REQUEST_ID;
464 l_apo_type.PROGRAM_APPLICATION_ID := l_amwp_rowtype.PROGRAM_APPLICATION_ID;
465 l_apo_type.PROGRAM_ID := l_amwp_rowtype.PROGRAM_ID;
466 l_apo_type.PROGRAM_UPDATE_DATE := l_amwp_rowtype.PROGRAM_UPDATE_DATE;
467 l_apo_type.ATTRIBUTE_CATEGORY := l_amwp_rowtype.ATTRIBUTE_CATEGORY;
468 l_apo_type.attribute1 := l_amwp_rowtype.attribute1;
469 l_apo_type.attribute2 := l_amwp_rowtype.attribute2;
470 l_apo_type.attribute3 := l_amwp_rowtype.attribute3;
471 l_apo_type.attribute4 := l_amwp_rowtype.attribute4;
475 l_apo_type.attribute8 := l_amwp_rowtype.attribute8;
472 l_apo_type.attribute5 := l_amwp_rowtype.attribute5;
473 l_apo_type.attribute6 := l_amwp_rowtype.attribute6;
474 l_apo_type.attribute7 := l_amwp_rowtype.attribute7;
476 l_apo_type.attribute9 := l_amwp_rowtype.attribute9;
477 l_apo_type.attribute10 := l_amwp_rowtype.attribute10;
478 l_apo_type.attribute11 := l_amwp_rowtype.attribute11;
479 l_apo_type.attribute12 := l_amwp_rowtype.attribute12;
480 l_apo_type.attribute13 := l_amwp_rowtype.attribute13;
481 l_apo_type.attribute14 := l_amwp_rowtype.attribute14;
482 l_apo_type.attribute15 := l_amwp_rowtype.attribute15;
483 l_apo_type.security_group_id := l_amwp_rowtype.security_group_id;
484 l_apo_type.OBJECT_VERSION_NUMBER := 1;
485 l_apo_type.END_DATE := null; ---amwp_rowtype.END_DATE;
486
487 ---insert into amw_process_organization tbl this process for this organization
488 if(p_mode = 'ASSOCIATE') then
489 --check to see if this process id exists in amw_process_organization
490
491
492 select count(*) into l_count from amw_process_organization
493 where process_id=p_process_id and organization_id=p_organization_id;
494 ---DBMS_OUTPUT.PUT_LINE ( 'associate_process_heirarchy: top_process_id '|| p_top_process_id );
495 ---DBMS_OUTPUT.PUT_LINE ( 'l_count for checking if this child process_id '||p_process_id||' exists: '|| l_count);
496
497 -------------------dbms_output.put_line('cc1 l_count: '||l_count);
498 if l_count > 0 then
499 --this means that this record exists in amw_process_organization table
500 --so this row needs to be updated
501 l_do_insert := 'UPDATE';
502 l_apo_type.TOP_PROCESS_ID := p_process_id;
503
504 --so now we need to check if there is a parent process associated to this node
505
506 select count(*) into l_count from amw_process_organization
507 where process_id=p_parent_process_id and organization_id=p_organization_id;
508
509 -----------------dbms_output.put_line('cc2 l_count: '||l_count);
510 --if it does then, see if the parent process is associated or disassociated
511 if l_count > 0 then
512
513 select end_date into l_parent_process_end_date from amw_process_organization
514 where process_id=p_parent_process_id and organization_id=p_organization_id;
515
516 ------------------dbms_output.put_line('cc3 l_parent_process_end_date: '||l_parent_process_end_date);
517 if l_parent_process_end_date is not null then
518 --so this process row exists in amw_process_organization, so no new row insertion
519 --this node has a parent process in amw_process_organization which is disassociated
520 --so this parent process needs to be updated with top_process_id set to this p_id
521 l_apo_type.TOP_PROCESS_ID := p_process_id;
522 ----l_apo_type.object_version_number := l_apo_type.object_version_number+1;
523 else
524 l_apo_type.TOP_PROCESS_ID := null;
525 end if;
526 end if;
527 else
528 --this means that this record does not exist, but we need to check
529 --if the parent_process_id for this process exists in amw_process_organization,
530 l_count := 0;
531
532
533 select count(*) into l_count from amw_process_organization
534 where process_id=p_parent_process_id and organization_id=p_organization_id;
535
536 ---------------dbms_output.put_line('cc4 l_count: '||l_count);
537 --if it does then
538
539 if l_count > 0 then
540 l_do_insert := 'INSERT';
541
542
543 select end_date into l_parent_process_end_date from amw_process_organization
544 where process_id=p_parent_process_id and organization_id=p_organization_id;
545 ---------------------dbms_output.put_line('cc5 l_parent_process_end_date: '||l_parent_process_end_date);
546 if l_parent_process_end_date is null then
547 -- if parent_process' end_date is null (associated parent process,
548 -- insert this record with top_process_id,end_date=null
549 l_apo_type.end_date := null;
550 l_apo_type.top_process_id := null;
551 else
552 -- if parent_process' end_date is not null (disassociated parent process,
553 -- insert this record with top_process_id=current process_id
554 -- end_date = null
555 l_apo_type.end_date := null;
556 l_apo_type.top_process_id := p_process_id;
557 end if; -- end of l_parent_process_id check
558 end if; --end of l_count for existence of row check
559
560 end if; -- end of p_mode = 'ASSOCIATE'
561 elsif p_mode = 'DISASSOCIATE' then
562 -----------------DBMS_OUTPUT.PUT_LINE ( 'P_MODE: '|| p_mode||', top_process_id: '||p_top_process_id|| ', process_id: '||p_process_id);
563 if p_top_process_id = p_process_id then
564 --this is the top most process in the node hierarchy which we want to delete
565 l_do_insert := 'DELETE';
566 l_apo_type.top_process_id := p_process_id;
567 --------------dbms_output.put_line('disassoc mode: top p_process_id: '||p_process_id);
568 else
569 l_do_insert := 'DELETE';
570 l_apo_type.top_process_id := null;
574
571 ----------------dbms_output.put_line('disassoc mode: p_process_id: '||p_process_id);
572 end if;
573 end if;
575 ------------------DBMS_OUTPUT.PUT_LINE ( 'p_mode: '|| p_mode||', l_do_insert: '||l_do_insert);
576
577 process_amw_process_org(
578 p_apo_type => l_apo_type,
579 p_do_insert => l_do_insert,
580 p_org_count => l_org_count,
581 p_rcm_assoc => p_rcm_assoc,
582 p_batch_id => p_batch_id,
583 p_rcm_org_intf_id => p_rcm_org_intf_id,
584 p_risk_id => p_risk_id,
585 p_control_id => p_control_id,
586 p_commit => p_commit,
587 p_validation_level => p_validation_level,
588 p_init_msg_list => p_init_msg_list,
589 x_return_status => x_return_status,
590 x_msg_count => x_msg_count,
591 x_msg_data => x_msg_data
592 );
593
594 ---------------------------dbms_output.put_line('Associate_Process_Org x_return_status: '||x_return_status);
595
596 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599
600 AMW_UTILITY_PVT.debug_message('Private API: ' || L_API_NAME || 'end');
601
602 -- Standard call to get message count and if count is 1, get message info.
603 FND_MSG_PUB.Count_And_Get(
604 p_count => x_msg_count,
605 p_data => x_msg_data
606 );
607 end loop;
608 close c1;
609
610 exception
611 WHEN FND_API.G_EXC_ERROR THEN
612 ROLLBACK TO associate_process_org_pvt;
613 x_return_status := FND_API.G_RET_STS_ERROR;
614 -- Standard call to get message count and if count=1, get the message
615 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
616
617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
618 ROLLBACK TO associate_process_org_pvt;
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 -- Standard call to get message count and if count=1, get the message
621 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
622
623 WHEN OTHERS THEN
624 ROLLBACK TO associate_process_org_pvt;
625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
626 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
627 THEN
628 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
629 END IF;
630 -- Standard call to get message count and if count=1, get the message
631 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
632
633 end associate_process_org;
634
635 -- ==============================================================================
636 -- Start of Comments
637 -- ==============================================================================
638 -- API Name
639 -- Process_Amw_Process_Org
640 -- Type
641 -- Private
642 -- Pre-Req
643 --
644 -- Parameters
645 --
646 -- IN
647 -- p_apo_type IN apo_type Optional Default = null
648 -- p_do_insert IN VARCHAR2 Optional Default = 'INSERT'
649 -- p_org_count IN NUMBER Optional Default = 0
650 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
651 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
652 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
653 --
654 --
655 -- OUT
656 -- x_return_status OUT VARCHAR2
657 -- x_msg_count OUT NUMBER
658 -- x_msg_data OUT VARCHAR2
659 -- Version : Current version 1.0
660 -- Note:
661 --
662 -- End of Comments
663 -- ==============================================================================
664 --
665
666 procedure process_amw_process_org(
667 p_apo_type in apo_type := g_miss_apo_type,
668 p_do_insert in varchar2 := 'INSERT',
669 p_org_count in number := 0,
670 p_rcm_assoc in varchar2 := 'N',
671 p_batch_id in number := null,
672 p_rcm_org_intf_id in number := null,
673 p_risk_id in number := null,
674 p_control_id in number := null,
675 p_commit in varchar2 := FND_API.G_FALSE,
676 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
677 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
678 x_return_status out nocopy varchar2,
679 x_msg_count out nocopy number,
680 x_msg_data out nocopy varchar2
681 )
682 is
683 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Process_Org';
684 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
685 l_process_id number := 0;
686 l_organization_id number := 0;
687 l_process_organization_id number;
688 l_org_count number := 0;
689 l_assoc_mode varchar2(30) := 'ASSOCIATE';
690 l_obj_num number := 0;
691 process_end_date date := sysdate;
692
693 CURSOR c_proc_org_s IS
694 SELECT AMW_PROCESS_organization_s.NEXTVAL FROM dual;
695
696 cursor get_counts is
697 select nvl(risk_count,0) as risk_count,
698 nvl(control_count,0) as control_count from amw_process
699 where process_id=p_apo_type.process_id;
700
701 l_risk_ctrl_count get_counts%rowtype;
702
703 begin
704 savepoint insert_in_amwpo_pvt;
708
705
706 l_process_id := p_apo_type.process_id;
707 l_organization_id := p_apo_type.organization_id;
709 ---------------------DBMS_OUTPUT.PUT_LINE ( 'top_process id: '|| p_apo_type.top_process_id);
710
711 -- Initialize message list if p_init_msg_list is set to TRUE.
712 IF FND_API.to_Boolean( p_init_msg_list )
713 THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 if p_do_insert = 'INSERT' then
718 open c_proc_org_s;
719 fetch c_proc_org_s into l_process_organization_id;
720 close c_proc_org_s;
721
722 insert into AMW_process_organization(
723 control_count,
724 risk_count,
725 top_process_id,
726 process_organization_id,
727 LAST_UPDATE_DATE,
728 LAST_UPDATED_BY,
729 creation_date,
730 created_by,
731 LAST_UPDATE_LOGIN,
732 process_id,
733 standard_process_flag,
734 risk_category,
735 approval_status,
736 certification_status,
737 last_audit_status,
738 organization_id,
739 last_certification_date,
740 last_audit_date,
741 next_audit_date,
742 application_owner_id,
743 process_owner_id,
744 process_category_code,
745 significant_process_flag,
746 finance_owner_id,
747 created_from,
748 request_id,
749 program_application_id,
750 program_id,
751 program_update_date,
752 attribute_category,
753 attribute1,
754 attribute2,
755 attribute3,
756 attribute4,
757 attribute5,
758 attribute6,
759 attribute7,
760 attribute8,
761 attribute9,
762 attribute10,
763 attribute11,
764 attribute12,
765 attribute13,
766 attribute14,
767 attribute15,
768 security_group_id,
769 object_version_number,
770 end_date) values(
771 p_apo_type.control_count,
772 p_apo_type.risk_count,
773 p_apo_type.top_process_id,
774 l_process_organization_id,
775 sysdate,
776 G_USER_ID,
777 sysdate,
778 G_USER_ID,
779 G_LOGIN_ID,
780 p_apo_type.process_id,
781 decode(p_apo_type.standard_process_flag,null,'Y',p_apo_type.standard_process_flag),
782 decode(p_apo_type.risk_category,null,'High',p_apo_type.risk_category),
783 p_apo_type.approval_status,
784 p_apo_type.certification_status,
785 p_apo_type.last_audit_status,
786 p_apo_type.organization_id,
787 p_apo_type.last_certification_date,
788 p_apo_type.last_audit_date,
789 p_apo_type.next_audit_date,
790 p_apo_type.application_owner_id,
791 p_apo_type.process_owner_id,
792 p_apo_type.process_category_code,
793 p_apo_type.significant_process_flag,
794 p_apo_type.finance_owner_id,
795 p_apo_type.created_from,
796 p_apo_type.request_id,
797 p_apo_type.program_application_id,
798 p_apo_type.program_id,
799 p_apo_type.program_update_date,
800 p_apo_type.attribute_category,
801 p_apo_type.attribute1,
802 p_apo_type.attribute2,
803 p_apo_type.attribute3,
804 p_apo_type.attribute4,
805 p_apo_type.attribute5,
806 p_apo_type.attribute6,
807 p_apo_type.attribute7,
808 p_apo_type.attribute8,
809 p_apo_type.attribute9,
810 p_apo_type.attribute10,
811 p_apo_type.attribute11,
812 p_apo_type.attribute12,
813 p_apo_type.attribute13,
814 p_apo_type.attribute14,
815 p_apo_type.attribute15,
816 p_apo_type.security_group_id,
817 1,
818 null
819 );
820
821 --Amit's requirement to increment org_count in amw_process for every associate
822 select nvl(object_version_number,1) into l_obj_num from amw_process where process_id=p_apo_type.process_id;
823 l_obj_num := l_obj_num+1;
824
825 select nvl(org_count,0) into l_org_count from amw_process where process_id=p_apo_type.process_id;
826 l_org_count := l_org_count+1;
827
828 /* update amw_process set org_count=l_org_count,
829 object_version_number=l_obj_num,
830 last_update_date=sysdate,last_updated_by=G_USER_ID,last_update_login=G_LOGIN_ID
831 where process_id=p_apo_type.process_id; */
832
833 ---DBMS_OUTPUT.PUT_LINE('update amw_process set org_count=decode('||l_org_count||',null,1,'||l_org_count||'),last_update_date='||sysdate||',last_updated_by='||G_USER_ID||' where process_id='||p_apo_type.process_id);
834 elsif p_do_insert = 'UPDATE' then
835 --record already exists in some process hierarchy in amw_process_organization
836 --so, do not insert, just update with top_process_id,end_date set to null
837 select nvl(object_version_number,1) into l_obj_num from amw_process_organization
838 where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
839
840 l_obj_num := l_obj_num+1;
841
845
842 select end_date into process_end_date from amw_process_organization
843 where process_id=p_apo_type.process_id
844 and organization_id=p_apo_type.organization_id;
846 open get_counts;
847 fetch get_counts into l_risk_ctrl_count;
848 close get_counts;
849
850 update amw_process_organization
851 set top_process_id=p_apo_type.top_process_id,
852 risk_count=l_risk_ctrl_count.risk_count,
853 control_count=l_risk_ctrl_count.control_count,
854 object_version_number=l_obj_num,
855 end_date=null,
856 last_updated_by=G_USER_ID,
857 last_update_date=sysdate,
858 last_update_login=G_LOGIN_ID
859 where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
860
861 --Amit's requirement to increment org_count in amw_process for every associate
862 --check first to see if this process was already assigned to this org
863 --and is active
864
865 if(process_end_date is not null)then
866 --this means that tbis process may have not been assigned to this org,
867 --or, if assigned, may have been end-dated ....
868 select nvl(object_version_number,1) into l_obj_num from amw_process where process_id=p_apo_type.process_id;
869 l_obj_num := l_obj_num+1;
870
871 select nvl(org_count,0) into l_org_count from amw_process where process_id=p_apo_type.process_id;
872 l_org_count := l_org_count+1;
873
874 /* update amw_process
875 set org_count=l_org_count,
876 object_version_number=l_obj_num,
877 last_update_date=sysdate,
878 last_updated_by=G_USER_ID,
879 last_update_login=G_LOGIN_ID
880 where process_id=p_apo_type.process_id; */
881 end if;
882 --------------------DBMS_OUTPUT.PUT_LINE ( 'updated amw_process_organization, p_org_id: '||l_process_organization_id ||', updated amw_process for process_id: '|| p_apo_type.process_id);
883
884 elsif p_do_insert = 'DELETE' then
885 l_org_count := p_org_count-1;
886 l_assoc_mode := 'DISASSOCIATE';
887
888 select nvl(object_version_number,1) into l_obj_num from amw_process_organization
889 where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
890
891 l_obj_num := l_obj_num+1;
892
893 update amw_process_organization
894 set end_date=sysdate,
895 object_version_number=l_obj_num,
896 risk_count=0,
897 control_count=0,
898 last_updated_by=G_USER_ID,
899 last_update_date=sysdate
900 where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
901
902 --Amit's requirement to increment org_count in amw_process for every associate
903 select nvl(object_version_number,1) into l_obj_num from amw_process where process_id=p_apo_type.process_id;
904 l_obj_num := l_obj_num+1;
905
906 select nvl(org_count,0) into l_org_count from amw_process where process_id=p_apo_type.process_id;
907 if(l_org_count > 0)then
908 l_org_count := l_org_count-1;
909 end if;
910
911 /* update amw_process
912 set org_count=l_org_count,
913 object_version_number=l_obj_num,
914 last_updated_by=G_USER_ID,
915 last_update_date=sysdate,
916 last_update_login=G_LOGIN_ID
917 where process_id=p_apo_type.process_id; */
918 end if;
919
920 ---DBMS_OUTPUT.PUT_LINE(FND_API.to_Boolean(p_commit));
921 -- Standard check for p_commit
922 IF FND_API.to_Boolean(p_commit)
923 THEN
924 COMMIT WORK;
925 END IF;
926
927
928 x_return_status := FND_API.G_RET_STS_SUCCESS;
929
930 select process_organization_id into l_process_organization_id from amw_process_organization
931 where process_id=p_apo_type.process_id and organization_id=p_apo_type.organization_id;
932
933 ---passing for the financial statements modification as required by Qingdi
934 process_amw_acct_assoc(
935 p_assoc_mode => l_assoc_mode,
936 p_process_id => p_apo_type.process_id,
937 p_process_organization_id => l_process_organization_id,
938 p_commit => p_commit,
939 p_validation_level => p_validation_level,
940 x_return_status => x_return_status,
941 x_msg_count => x_msg_count,
942 x_msg_data => x_msg_data
943 );
944
945 fnd_file.put_line(fnd_file.LOG, 'INSIDE PROCESS_AMW_PROCESS_ORG');
946 fnd_file.put_line(fnd_file.LOG, 'p_rcm_assoc: '||p_rcm_assoc);
947 fnd_file.put_line(fnd_file.LOG, 'p_batch_id: '||p_batch_id);
948 fnd_file.put_line(fnd_file.LOG, 'p_risk_id: '||p_risk_id);
949 fnd_file.put_line(fnd_file.LOG, 'p_control_id: '||p_control_id);
950 fnd_file.put_line(fnd_file.LOG, '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
951
955 p_batch_id => p_batch_id,
952 if(p_rcm_assoc = 'Y')then
953 fnd_file.put_line(fnd_file.LOG, 'WILL BE CALLING THE NEW API HERE');
954 process_amw_rcm_org(
956 p_rcm_org_intf_id => p_rcm_org_intf_id,
957 p_process_organization_id => l_process_organization_id,
958 p_organization_id => p_apo_type.organization_id,
959 p_process_id => p_apo_type.process_id,
960 p_risk_id => p_risk_id,
961 p_control_id => p_control_id,
962 p_commit => p_commit,
963 p_validation_level => p_validation_level,
964 x_return_status => x_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data);
967
968 fnd_file.put_line(fnd_file.LOG, 'PROCESS_AMW_RCM_ORG END: X_RETURN_STATUS: '||X_RETURN_STATUS);
969 else
970 fnd_file.put_line(fnd_file.LOG, 'CALLING THE old API HERE');
971 process_amw_risk_assoc(
972 p_assoc_mode => l_assoc_mode,
973 p_process_id => p_apo_type.process_id,
974 p_process_organization_id => l_process_organization_id,
975 p_commit => p_commit,
976 p_validation_level => p_validation_level,
977 x_return_status => x_return_status,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data);
980 end if;
981
982 ----------------------dbms_output.put_line('Process_AMW_Process_Org x_return_status: '||x_return_status);
983 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
984 RAISE FND_API.G_EXC_ERROR;
985 END IF;
986
987 -- Debug Message
988 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
989
990 -- Standard call to get message count and if count is 1, get message info.
991 FND_MSG_PUB.Count_And_Get(
992 p_count => x_msg_count,
993 p_data => x_msg_data
994 );
995
996 exception
997 WHEN FND_API.G_EXC_ERROR THEN
998 ROLLBACK TO insert_in_amwpo_pvt;
999 x_return_status := FND_API.G_RET_STS_ERROR;
1000 -- Standard call to get message count and if count=1, get the message
1001 FND_MSG_PUB.Count_And_Get (
1002 p_encoded => FND_API.G_FALSE,
1003 p_count => x_msg_count,
1004 p_data => x_msg_data
1005 );
1006
1007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1008 ROLLBACK TO insert_in_amwpo_pvt;
1009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1010 -- Standard call to get message count and if count=1, get the message
1011 FND_MSG_PUB.Count_And_Get (
1012 p_encoded => FND_API.G_FALSE,
1013 p_count => x_msg_count,
1014 p_data => x_msg_data
1015 );
1016
1017 WHEN OTHERS THEN
1018 ROLLBACK TO insert_in_amwpo_pvt;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1021 THEN
1022 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1023 END IF;
1024 -- Standard call to get message count and if count=1, get the message
1025 FND_MSG_PUB.Count_And_Get (
1026 p_encoded => FND_API.G_FALSE,
1027 p_count => x_msg_count,
1028 p_data => x_msg_data
1029 );
1030 end process_amw_process_org;
1031
1032 procedure process_amw_rcm_org(
1033 p_batch_id in number := null,
1034 p_rcm_org_intf_id in number := null,
1035 p_process_organization_id in number := null,
1036 p_organization_id in number := null,
1037 p_process_id in number := null,
1038 p_risk_id in number := null,
1039 p_control_id in number := null,
1040 p_commit in varchar2 := FND_API.G_FALSE,
1041 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1042 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1043 x_return_status out nocopy varchar2,
1044 x_msg_count out nocopy number,
1045 x_msg_data out nocopy varchar2
1046 )
1047
1048 is
1049 cursor c1(p_batch_id in number, p_rcm_org_intf_id in number) is
1050 select risk_likelihood_code,
1051 risk_impact_code,
1052 upper(material) as material,
1053 decode(nvl(upper(material),'N'),'N',null,material_value) as material_value
1054 from amw_rcm_org_interface
1055 where batch_id=p_batch_id
1056 and rcm_org_interface_id=p_rcm_org_intf_id;
1057
1058 cursor c2(p_batch_id in number, p_rcm_org_intf_id in number) is
1059 select ap_name,
1060 upper(nvl(design_effectiveness,'N')) as design_effectiveness,
1061 upper(nvl(op_effectiveness,'N')) as op_effectiveness
1062 from amw_rcm_org_interface
1063 where batch_id=p_batch_id
1064 and rcm_org_interface_id=p_rcm_org_intf_id;
1065
1066 l_c1_type c1%rowtype;
1067 l_c2_type c2%rowtype;
1068 l_risk_association_id NUMBER;
1069 l_CONTROL_association_id NUMBER;
1070 L_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_AMW_RCM_ORG';
1071 begin
1072 IF FND_GLOBAL.User_Id IS NULL THEN
1073 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1074 RAISE FND_API.G_EXC_ERROR;
1075 END IF;
1076
1077 fnd_file.PUT_LINE( fnd_file.LOG, '%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&%^&');
1078 fnd_file.PUT_LINE( fnd_file.LOG, 'INSIDE PROCESS_AMW_RCM_ORG');
1079 fnd_file.put_line( fnd_file.Log, 'VALUES GOT --> ');
1083 fnd_file.PUT_LINE( fnd_file.LOG, 'p_organization_id: '||p_organization_id );
1080 fnd_file.PUT_LINE( fnd_file.LOG, 'p_batch_id: '||p_batch_id );
1081 fnd_file.PUT_LINE( fnd_file.LOG, 'p_rcm_org_intf_id: '||p_rcm_org_intf_id );
1082 fnd_file.PUT_LINE( fnd_file.LOG, 'p_process_organization_id: '||p_process_organization_id );
1084 fnd_file.PUT_LINE( fnd_file.LOG, 'p_process_id: '||p_process_id );
1085 fnd_file.PUT_LINE( fnd_file.LOG, 'p_risk_id: '||p_risk_id );
1086 fnd_file.PUT_LINE( fnd_file.LOG, 'p_batch_id: '||p_batch_id );
1087 fnd_file.PUT_LINE( fnd_file.LOG, 'p_control_id: '||p_control_id );
1088
1089 begin
1090 select risk_association_id
1091 into l_risk_association_id
1092 from amw_risk_associations
1093 where object_type='PROCESS_ORG'
1094 and pk1=p_process_organization_id
1095 and risk_id=p_risk_id;
1096
1097 open c1(p_batch_id,p_rcm_org_intf_id);
1098 fetch c1 into l_c1_type;
1099 close c1;
1100 fnd_file.put_line(fnd_file.LOG, 'After Select, l_risk_association_id: '||l_risk_association_id);
1101 fnd_file.put_line(fnd_file.LOG, 'UPDATING AMW_RISK_ASSOCIATIONS');
1102 UPDATE AMW_RISK_ASSOCIATIONS
1103 SET OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
1104 ,RISK_LIKELIHOOD_CODE=L_C1_TYPE.RISK_LIKELIHOOD_CODE
1105 ,RISK_IMPACT_CODE=L_C1_TYPE.RISK_IMPACT_CODE
1106 ,MATERIAL=L_C1_TYPE.MATERIAL
1107 ,MATERIAL_VALUE=L_C1_TYPE.MATERIAL_VALUE
1108 ,LAST_UPDATE_DATE=SYSDATE
1109 ,LAST_UPDATED_BY=G_USER_ID
1110 ,LAST_UPDATE_LOGIN=G_LOGIN_ID
1111 WHERE RISK_ASSOCIATION_ID=L_RISK_ASSOCIATION_ID;
1112
1113 fnd_file.put_line(fnd_file.LOG, 'UPDATED AMW_RISK_ASSOCIATIONS');
1114
1115 exception
1116 when no_data_found then
1117 fnd_file.put_line(fnd_file.LOG, 'INSIDE NO_DATA_FOUND');
1118 select amw_risk_associations_s.nextval into l_risk_association_id from dual;
1119
1120 open c1(p_batch_id,p_rcm_org_intf_id);
1121 fetch c1 into l_c1_type;
1122 close c1;
1123
1124 fnd_file.put_line(fnd_file.LOG, 'VALUES TO BE INSERTED INTO AMW_RISK_ASSOCIATIONS');
1125 FND_FILE.PUT_LINE(FND_FILE.LOG, 'risk_association_id: '||l_risk_association_id);
1126 FND_FILE.PUT_LINE(FND_FILE.LOG, 'risk_id: '||p_risk_id);
1127 FND_FILE.PUT_LINE(FND_FILE.LOG, 'pk1: '||p_process_organization_id);
1128 FND_FILE.PUT_LINE(FND_FILE.LOG, 'risk_likelihood_code: '||l_c1_type.risk_likelihood_code);
1129 FND_FILE.PUT_LINE(FND_FILE.LOG, 'risk_IMPACT_code: '||l_c1_type.risk_IMPACT_code);
1130 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATERIAL: '||l_c1_type.material);
1131 FND_FILE.PUT_LINE(FND_FILE.LOG, 'material_value: '||l_c1_type.material_value);
1132
1133 insert into amw_risk_associations(risk_association_id,
1134 last_update_date,
1135 last_updated_by,
1136 creation_date,
1137 created_by,
1138 last_update_login,
1139 risk_id,
1140 pk1,
1141 object_type,
1142 object_version_number,
1143 effective_date_from,
1144 risk_likelihood_code,
1145 risk_impact_code,
1146 material,
1147 material_value) values (
1148 ---amw_risk_associations_s.nextval,
1149 l_risk_association_id,
1150 sysdate,
1151 G_USER_ID,
1152 sysdate,
1153 G_USER_ID,
1154 G_LOGIN_ID,
1155 p_risk_id,
1156 p_process_organization_id,
1157 'PROCESS_ORG',
1158 1,
1159 sysdate,
1160 l_c1_type.risk_likelihood_code,
1161 l_c1_type.risk_impact_code,
1162 l_c1_type.material,
1163 l_c1_type.material_value);
1167 if(p_control_id is not null) then
1164 end;
1165 fnd_file.PUT_LINE( fnd_file.LOG, 'l_risk_association_id: '||l_risk_association_id);
1166
1168 FND_FILE.PUT_LINE( FND_FILE.LOG, '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@' );
1169 FND_FILE.PUT_LINE( FND_FILE.LOG, 'DOING CONTROL ASSOCIATIONS HERE' );
1170 begin
1171 select CONTROL_association_id
1172 into l_CONTROL_association_id
1173 from amw_CONTROL_associations
1174 where object_type='RISK_ORG'
1175 and pk1=l_risk_association_id
1176 and CONTROL_id=p_CONTROL_id;
1177
1178 fnd_file.put_line(fnd_file.LOG, 'After Select, l_CONTROL_association_id: '||l_CONTROL_association_id);
1179
1180 exception
1181 when no_data_found then
1182 fnd_file.put_line(fnd_file.LOG, 'INSIDE NO_DATA_FOUND');
1183 select amw_CONTROL_associations_s.nextval into l_CONTROL_association_id from dual;
1184
1185 fnd_file.put_line(fnd_file.LOG, 'VALUES TO BE INSERTED INTO AMW_CONTROL_ASSOCIATIONS');
1186 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CONTROL_association_id: '||l_CONTROL_association_id);
1187 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CONTROL_id: '||p_CONTROL_id);
1188 FND_FILE.PUT_LINE(FND_FILE.LOG, 'pk1: '||l_risk_association_id);
1189
1190 insert into amw_CONTROL_associations(CONTROL_association_id,
1191 last_update_date,
1192 last_updated_by,
1193 creation_date,
1194 created_by,
1195 last_update_login,
1196 CONTROL_id,
1197 pk1,
1198 object_type,
1199 object_version_number,
1200 effective_date_from) values (
1201 ---amw_risk_associations_s.nextval,
1202 l_CONTROL_association_id,
1203 sysdate,
1204 G_USER_ID,
1205 sysdate,
1206 G_USER_ID,
1207 G_LOGIN_ID,
1208 p_CONTROL_id,
1209 l_risk_association_id,
1210 'RISK_ORG',
1211 1,
1212 sysdate);
1213 end;
1214 else
1215 fnd_file.PUT_LINE( fnd_file.LOG, 'CANNOT DO CONTROL ASSOCIATIONS, BECAUSE CONTROL_ID IS NULL --> CONTROL_ID: '||P_CONTROL_ID);
1216 end if;
1217
1218 /*
1219 open c2(p_batch_id,p_rcm_org_intf_id);
1220 fatch c2 into l_c2_type;
1221 close c2;
1222 */
1223 if(p_control_id is not null) then
1224 process_amw_ap_assoc(
1225 p_assoc_mode => 'ASSOCIATE',
1226 p_control_association_id => l_control_association_id,
1227 p_control_id => P_control_id,
1228 p_commit => p_commit,
1229 p_validation_level => p_validation_level,
1230 x_return_status => x_return_status,
1231 x_msg_count => x_msg_count,
1232 x_msg_data => x_msg_data);
1233
1234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1235 RAISE FND_API.G_EXC_ERROR;
1236 END IF;
1237 END IF;
1238
1239 exception
1240 WHEN FND_API.G_EXC_ERROR THEN
1241 ROLLBACK;
1242 x_return_status := FND_API.G_RET_STS_ERROR;
1243 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1244
1245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1246 ROLLBACK;
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1249
1250 WHEN OTHERS THEN
1251 ROLLBACK;
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1253 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1254 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1255 END IF;
1256 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1257
1258 end process_amw_rcm_org;
1259
1260 procedure process_amw_acct_assoc(
1261 p_assoc_mode in varchar2 := 'ASSOCIATE',
1262 p_process_id in number,
1263 p_process_organization_id in number,
1264 p_commit in varchar2 := FND_API.G_FALSE,
1265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1266 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1267 x_return_status out nocopy varchar2,
1268 x_msg_count out nocopy number,
1269 x_msg_data out nocopy varchar2
1270 )
1271 is
1272 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Acct_Assoc';
1273 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1274 l_process_id number := p_process_id;
1275 l_process_organization_id number := p_process_organization_id;
1276 l_acct_assoc_id number := 0;
1277
1278 cursor c1 is
1279 select acct_assoc_id,last_update_date,last_updated_by,
1280 creation_date,created_by,last_update_login,object_type,pk1,pk2,pk3,pk4,pk5,
1281 natural_account_id,statement_id,statement_line_id,attribute_category,
1282 attribute1,attribute2,attribute3,attribute4,
1283 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
1284 attribute11,attribute12,---attribute13,
1285 attribute14,attribute15,
1286 security_group_id,object_version_number
1287 from amw_acct_associations
1288 where object_type='PROCESS'
1289 and pk1=l_process_id;
1290 l_aaa_row c1%rowtype;
1291
1295 and pk1=l_process_organization_id; ---and risk_id=;
1292 cursor c2 is
1293 select acct_assoc_id,natural_account_id
1294 from amw_acct_associations where object_type='PROCESS_ORG'
1296 l_update c2%rowtype;
1297
1298 row_count number := 0;
1299
1300 x_rowid number;
1301
1302 CURSOR c_id_exists (l_id IN NUMBER) IS
1303 SELECT 1
1304 FROM amw_acct_associations
1305 WHERE acct_assoc_id = l_id;
1306
1307 begin
1308 savepoint assoc_acct_pvt;
1309
1310 x_return_status := FND_API.G_RET_STS_SUCCESS;
1311 -- Initialize message list if p_init_msg_list is set to TRUE.
1312 IF FND_API.to_Boolean( p_init_msg_list )
1313 THEN
1314 FND_MSG_PUB.initialize;
1315 END IF;
1316
1317 -------------------------dbms_output.put_line('In amw_risk_associations: p_assoc_mode: '||p_assoc_mode);
1318 -- =========================================================================
1319 -- Validate Environment
1320 -- =========================================================================
1321 IF FND_GLOBAL.User_Id IS NULL
1322 THEN
1323 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1324 RAISE FND_API.G_EXC_ERROR;
1325 END IF;
1326
1327 if p_assoc_mode = 'ASSOCIATE' then
1328 open c1;
1329 loop
1330 fetch c1 into l_aaa_row;
1331 exit when c1%notfound;
1332
1333 select count(*) into row_count from amw_acct_associations
1334 where object_type='PROCESS_ORG'
1335 and pk1=l_process_organization_id
1336 and natural_account_id=l_aaa_row.natural_account_id;
1337
1338 ----------------dbms_output.put_line('In amw_risk_associations: row_count: '||row_count);
1339 ---------------dbms_output.put_line('In amw_risk_associations: pk1: '||l_process_organization_id);
1340 --------------dbms_output.put_line('In amw_risk_associations: risk_id: '||l_ara_row.risk_id);
1341
1342 if row_count = 0 then
1343 select amw_acct_associations_s.nextval into l_acct_assoc_id from dual;
1344
1345 --------------------dbms_output.put_line('In amw_risk_associations: inserting');
1346
1347 insert into amw_acct_associations (acct_assoc_id,last_update_date,
1348 last_updated_by,creation_date,created_by,
1349 last_update_login,object_type,pk1,pk2,pk3,pk4,pk5,
1350 natural_account_id,statement_id,statement_line_id,
1351 attribute_category,attribute1,attribute2,
1352 attribute3,attribute4,attribute5,attribute6,attribute7,
1353 attribute8,attribute9,attribute10,attribute11,attribute12,
1354 ---attribute13,
1355 attribute14,attribute15,security_group_id,
1356 object_version_number) values
1357 (l_acct_assoc_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
1358 G_LOGIN_ID,'PROCESS_ORG',l_process_organization_id,l_aaa_row.pk2,
1359 l_aaa_row.pk3,l_aaa_row.pk4,l_aaa_row.pk5,l_aaa_row.natural_account_id,
1360 l_aaa_row.statement_id,l_aaa_row.statement_line_id,l_aaa_row.attribute_category,
1361 l_aaa_row.attribute1,l_aaa_row.attribute2,l_aaa_row.attribute3,l_aaa_row.attribute4,
1362 l_aaa_row.attribute5,l_aaa_row.attribute6,l_aaa_row.attribute7,l_aaa_row.attribute8,
1363 l_aaa_row.attribute9,l_aaa_row.attribute10,l_aaa_row.attribute11,l_aaa_row.attribute12,
1364 ----l_aaa_row.attribute13,
1365 l_aaa_row.attribute14,l_aaa_row.attribute15,
1366 l_aaa_row.security_group_id,1);
1367
1368 open c_id_exists(l_acct_assoc_ID);
1369 fetch c_id_exists into X_ROWID;
1370 close c_id_exists;
1371
1372 ---IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1373 IF X_ROWID is null THEN
1374 RAISE FND_API.G_EXC_ERROR;
1375 END IF;
1376
1377 -- Standard check for p_commit
1378 IF FND_API.to_Boolean( p_commit )
1379 THEN
1380 COMMIT WORK;
1381 END IF;
1382 end if;
1383 end loop;
1384 close c1;
1385 elsif p_assoc_mode = 'DISASSOCIATE' then
1386 open c2;
1387 loop
1388 fetch c2 into l_update;
1389 exit when c2%notfound;
1390
1391 delete from amw_acct_associations
1392 where acct_assoc_id=l_update.acct_assoc_id
1393 and object_type='PROCESS_ORG'
1394 and pk1=p_process_organization_id;
1395
1396 ---------------------dbms_output.put_line('In amw_risk_associations: deleting');
1397
1398 -- Standard check for p_commit
1399 IF FND_API.to_Boolean( p_commit )
1400 THEN
1401 COMMIT WORK;
1402 END IF;
1403
1404 x_return_status := FND_API.G_RET_STS_SUCCESS;
1405
1406 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1407 RAISE FND_API.G_EXC_ERROR;
1408 END IF;
1412
1409 end loop;
1410 close c2;
1411 end if;
1413 -------------------dbms_output.put_line('Process_AMW_Risk_Assoc x_return_status: '||x_return_status);
1414 -- Debug Message
1415 ---AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1416 AMW_UTILITY_PVT.debug_message(l_api_name || '_end');
1417
1418 -- Standard call to get message count and if count is 1, get message info.
1419 FND_MSG_PUB.Count_And_Get(
1420 p_count => x_msg_count,
1421 p_data => x_msg_data
1422 );
1423
1424 exception
1425 WHEN FND_API.G_EXC_ERROR THEN
1426 ROLLBACK TO assoc_acct_pvt;
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 -- Standard call to get message count and if count=1, get the message
1429 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1430
1431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1432 ROLLBACK TO assoc_acct_pvt;
1433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1434 -- Standard call to get message count and if count=1, get the message
1435 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1436
1437 WHEN OTHERS THEN
1438 ROLLBACK TO assoc_acct_pvt;
1439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1440 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1441 THEN
1442 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1443 END IF;
1444 -- Standard call to get message count and if count=1, get the message
1445 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1446 end process_amw_acct_assoc;
1447
1448 -- ==============================================================================
1449 -- Start of Comments
1450 -- ==============================================================================
1451 -- API Name
1452 -- Process_Amw_Risk_Assoc
1453 -- Type
1454 -- Private
1455 -- Pre-Req
1456 --
1457 -- Parameters
1458 --
1459 -- IN
1460 -- p_assoc_mode IN VARCHAR2 Otional Default = 'ASSOCIATE'
1461 -- p_process_id IN number Required
1462 -- p_process_organization_id IN number Required
1463 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
1464 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1465 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1466 -- OUT
1467 -- x_return_status OUT VARCHAR2
1468 -- x_msg_count OUT NUMBER
1469 -- x_msg_data OUT VARCHAR2
1470 -- Version : Current version 1.0
1471 -- Note:
1472 --
1473 -- End of Comments
1474 -- ==============================================================================
1475 --
1476
1477 procedure process_amw_risk_assoc(
1478 p_assoc_mode in varchar2 := 'ASSOCIATE',
1479 p_process_id in number,
1480 p_process_organization_id in number,
1481 p_commit in varchar2 := FND_API.G_FALSE,
1482 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1483 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1484 x_return_status out nocopy varchar2,
1485 x_msg_count out nocopy number,
1486 x_msg_data out nocopy varchar2
1487 )
1488 is
1489 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Risk_Assoc';
1490 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1491 l_process_id number := p_process_id;
1492 l_process_organization_id number := p_process_organization_id;
1493 l_risk_association_id number := 0;
1494
1495 cursor c1 is
1496 select risk_association_id,last_update_date,last_updated_by,
1497 creation_date,created_by,last_update_login,risk_id,pk1,pk2,pk3,pk4,pk5,
1498 object_type,attribute_category,attribute1,attribute2,attribute3,attribute4,
1502 effective_date_to,risk_likelihood_code,risk_impact_code
1499 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
1500 attribute11,attribute12,attribute13,attribute14,attribute15,
1501 security_group_id,object_version_number,effective_date_from,
1503 ---added by npanandi on 01/22/2004 for 3362371 bug fix
1504 ,material,material_value
1505 ---finished addition on 01/22/2004
1506 from amw_risk_associations
1507 where object_type='PROCESS'
1508 and pk1=l_process_id;
1509 l_ara_row c1%rowtype;
1510
1511 cursor c2 is
1512 select risk_association_id,risk_id
1513 from amw_risk_associations where object_type='PROCESS_ORG'
1514 and pk1=l_process_organization_id; ---and risk_id=;
1515 l_update c2%rowtype;
1516
1517 row_count number := 0;
1518
1519 begin
1520 savepoint assoc_risk_pvt;
1521
1522 x_return_status := FND_API.G_RET_STS_SUCCESS;
1523 -- Initialize message list if p_init_msg_list is set to TRUE.
1524 IF FND_API.to_Boolean( p_init_msg_list )
1525 THEN
1526 FND_MSG_PUB.initialize;
1527 END IF;
1528
1529 -------------------------dbms_output.put_line('In amw_risk_associations: p_assoc_mode: '||p_assoc_mode);
1530 -- =========================================================================
1531 -- Validate Environment
1532 -- =========================================================================
1533 IF FND_GLOBAL.User_Id IS NULL
1534 THEN
1535 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1536 RAISE FND_API.G_EXC_ERROR;
1537 END IF;
1538
1539 if p_assoc_mode = 'ASSOCIATE' then
1540 open c1;
1541 loop
1542 fetch c1 into l_ara_row;
1543 exit when c1%notfound;
1544
1545 select count(*) into row_count from amw_risk_associations
1546 where object_type='PROCESS_ORG'
1547 and pk1=l_process_organization_id
1548 and risk_id=l_ara_row.risk_id;
1549
1550 ----------------dbms_output.put_line('In amw_risk_associations: row_count: '||row_count);
1551 ---------------dbms_output.put_line('In amw_risk_associations: pk1: '||l_process_organization_id);
1552 --------------dbms_output.put_line('In amw_risk_associations: risk_id: '||l_ara_row.risk_id);
1553
1554 if row_count = 0 then
1555 select amw_risk_associations_s.nextval into l_risk_association_id from dual;
1556
1557 --------------------dbms_output.put_line('In amw_risk_associations: inserting');
1558
1559 insert into amw_risk_associations (risk_association_id,last_update_date,
1560 last_updated_by,creation_date,created_by,
1561 last_update_login,risk_id,pk1,pk2,pk3,pk4,pk5,
1562 object_type,attribute_category,attribute1,attribute2,
1563 attribute3,attribute4,attribute5,attribute6,attribute7,
1564 attribute8,attribute9,attribute10,attribute11,attribute12,
1565 attribute13,attribute14,attribute15,security_group_id,
1566 object_version_number,effective_date_from,effective_date_to,
1567 risk_likelihood_code,risk_impact_code
1568 ---added by npanandi on 01/22/2004 for 3362371 bug fix
1569 ,material,material_value
1570 ---finished addition on 01/22/2004
1571 ) values
1572 (l_risk_association_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
1573 G_LOGIN_ID,l_ara_row.risk_id,l_process_organization_id,l_ara_row.pk2,
1574 l_ara_row.pk3,l_ara_row.pk4,l_ara_row.pk5,'PROCESS_ORG',l_ara_row.attribute_category,
1575 l_ara_row.attribute1,l_ara_row.attribute2,l_ara_row.attribute3,l_ara_row.attribute4,
1576 l_ara_row.attribute5,l_ara_row.attribute6,l_ara_row.attribute7,l_ara_row.attribute8,
1577 l_ara_row.attribute9,l_ara_row.attribute10,l_ara_row.attribute11,l_ara_row.attribute12,
1578 l_ara_row.attribute13,l_ara_row.attribute14,l_ara_row.attribute15,
1579 l_ara_row.security_group_id,1,
1580 l_ara_row.effective_date_from,l_ara_row.effective_date_to,
1581 l_ara_row.risk_likelihood_code,l_ara_row.risk_impact_code
1582 ---added by npanandi on 01/22/2004 for 3362371 bug fix
1583 ,l_ara_row.material,l_ara_row.material_value
1584 ---finished addition on 01/22/2004
1585 );
1589 COMMIT WORK;
1586 -- Standard check for p_commit
1587 IF FND_API.to_Boolean( p_commit )
1588 THEN
1590 END IF;
1591
1592 x_return_status := FND_API.G_RET_STS_SUCCESS;
1593
1594 process_amw_control_assoc(
1595 p_assoc_mode => p_assoc_mode,
1596 p_risk_association_id => l_risk_association_id,
1597 p_risk_id => l_ara_row.risk_id,
1598 p_commit => p_commit,
1599 p_validation_level => p_validation_level,
1600 x_return_status => x_return_status,
1601 x_msg_count => x_msg_count,
1602 x_msg_data => x_msg_data
1603 );
1604
1605 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1606 RAISE FND_API.G_EXC_ERROR;
1607 END IF;
1608 end if;
1609 end loop;
1610 close c1;
1611 elsif p_assoc_mode = 'DISASSOCIATE' then
1612 open c2;
1613 loop
1614 fetch c2 into l_update;
1615 exit when c2%notfound;
1616
1617 delete from amw_risk_associations
1618 where risk_association_id=l_update.risk_association_id
1619 and object_type='PROCESS_ORG'
1620 and pk1=p_process_organization_id;
1621
1622 ---------------------dbms_output.put_line('In amw_risk_associations: deleting');
1623
1624 -- Standard check for p_commit
1625 IF FND_API.to_Boolean( p_commit )
1626 THEN
1627 COMMIT WORK;
1628 END IF;
1629
1630 x_return_status := FND_API.G_RET_STS_SUCCESS;
1631
1632 process_amw_control_assoc(
1633 p_assoc_mode => p_assoc_mode,
1634 p_risk_association_id => l_update.risk_association_id,
1635 p_risk_id => l_update.risk_id,
1636 p_commit => p_commit,
1637 p_validation_level => p_validation_level,
1638 x_return_status => x_return_status,
1639 x_msg_count => x_msg_count,
1640 x_msg_data => x_msg_data
1641 );
1642
1643 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1644 RAISE FND_API.G_EXC_ERROR;
1645 END IF;
1646 end loop;
1647 close c2;
1648 end if;
1649
1650 -------------------dbms_output.put_line('Process_AMW_Risk_Assoc x_return_status: '||x_return_status);
1651 -- Debug Message
1652 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1653
1654 -- Standard call to get message count and if count is 1, get message info.
1655 FND_MSG_PUB.Count_And_Get(
1656 p_count => x_msg_count,
1657 p_data => x_msg_data
1658 );
1659
1660 exception
1661 WHEN FND_API.G_EXC_ERROR THEN
1662 ROLLBACK TO assoc_risk_pvt;
1663 x_return_status := FND_API.G_RET_STS_ERROR;
1664 -- Standard call to get message count and if count=1, get the message
1665 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1666
1667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1668 ROLLBACK TO assoc_risk_pvt;
1669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1670 -- Standard call to get message count and if count=1, get the message
1671 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1672
1673 WHEN OTHERS THEN
1674 ROLLBACK TO assoc_risk_pvt;
1675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1676 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1677 THEN
1678 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1679 END IF;
1680 -- Standard call to get message count and if count=1, get the message
1681 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1682 end process_amw_risk_assoc;
1683
1684 -- ==============================================================================
1685 -- Start of Comments
1686 -- ==============================================================================
1687 -- API Name
1688 -- Process_Amw_Control_Assoc
1689 -- Type
1690 -- Private
1691 -- Pre-Req
1692 --
1693 -- Parameters
1694 --
1695 -- IN
1696 -- p_assoc_mode IN VARCHAR2 Otional Default = 'ASSOCIATE'
1697 -- p_risk_association_id IN number Required
1698 -- p_risk_id IN number Required
1699 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
1700 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1701 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1702 -- OUT
1703 -- x_return_status OUT VARCHAR2
1704 -- x_msg_count OUT NUMBER
1705 -- x_msg_data OUT VARCHAR2
1706 -- Version : Current version 1.0
1707 -- Note:
1708 --
1709 -- End of Comments
1710 -- ==============================================================================
1711 --
1712
1713 procedure process_amw_control_assoc(
1714 p_assoc_mode in varchar2 := 'ASSOCIATE',
1715 p_risk_association_id in number,
1716 p_risk_id in number,
1717 p_commit in varchar2 := FND_API.G_FALSE,
1718 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1719 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1720 x_return_status out nocopy varchar2,
1721 x_msg_count out nocopy number,
1725 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Control_Assoc';
1722 x_msg_data out nocopy varchar2
1723 )
1724 is
1726 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1727 l_risk_id number := p_risk_id;
1728 l_risk_association_id number := p_risk_association_id;
1729 l_control_association_id number := 0;
1730
1731 cursor c1 is
1732 select control_association_id,last_update_date,last_updated_by,
1733 creation_date,created_by,last_update_login,control_id,pk1,pk2,pk3,pk4,pk5,
1734 object_type,attribute_category,attribute1,attribute2,attribute3,attribute4,
1735 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
1736 attribute11,attribute12,attribute13,attribute14,attribute15,
1737 security_group_id,object_version_number,effective_date_from,
1738 effective_date_to
1739 from amw_control_associations
1740 where object_type='RISK' and pk1=l_risk_id;
1741 l_aca_row c1%rowtype;
1742
1743 cursor c2 is
1744 select control_association_id,control_id
1745 from amw_control_associations where object_type='RISK_ORG'
1746 and pk1=l_risk_association_id; ---and risk_id=;
1747 l_update c2%rowtype;
1748
1749 row_count number := 0;
1750
1751 begin
1752 savepoint assoc_control_pvt;
1753
1754 x_return_status := FND_API.G_RET_STS_SUCCESS;
1755
1756 -- Initialize message list if p_init_msg_list is set to TRUE.
1757 IF FND_API.to_Boolean( p_init_msg_list )
1758 THEN
1759 FND_MSG_PUB.initialize;
1760 END IF;
1761
1762 -- =========================================================================
1763 -- Validate Environment
1764 -- =========================================================================
1765 IF FND_GLOBAL.User_Id IS NULL
1766 THEN
1767 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1768 RAISE FND_API.G_EXC_ERROR;
1769 END IF;
1770
1771 if p_assoc_mode = 'ASSOCIATE' then
1772 open c1;
1773 loop
1774 fetch c1 into l_aca_row;
1775 exit when c1%notfound;
1776 select count(*) into row_count from amw_control_associations
1777 where object_type='RISK_ORG'
1778 and pk1=l_risk_association_id
1779 and control_id=l_aca_row.control_id;
1780
1781 if row_count = 0 then
1782 select amw_control_associations_s.nextval into l_control_association_id from dual;
1783
1784 -----------------dbms_output.put_line('INSERTING IN AMW_CONTROL_ASSOCIATIONS');
1785 ----------------------dbms_output.put_line('control_association_id: '||l_control_association_id);
1786 insert into amw_control_associations(control_association_id,last_update_date,
1787 last_updated_by,creation_date,created_by,
1788 last_update_login,control_id,pk1,pk2,pk3,pk4,pk5,
1789 object_type,attribute_category,attribute1,attribute2,
1790 attribute3,attribute4,attribute5,attribute6,attribute7,
1791 attribute8,attribute9,attribute10,attribute11,attribute12,
1792 attribute13,attribute14,attribute15,security_group_id,
1793 object_version_number,effective_date_from,effective_date_to)
1794 values (l_control_association_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
1795 G_LOGIN_ID,l_aca_row.control_id,l_risk_association_id,l_aca_row.pk2,
1796 l_aca_row.pk3,l_aca_row.pk4,l_aca_row.pk5,'RISK_ORG',l_aca_row.attribute_category,
1797 l_aca_row.attribute1,l_aca_row.attribute2,l_aca_row.attribute3,l_aca_row.attribute4,
1798 l_aca_row.attribute5,l_aca_row.attribute6,l_aca_row.attribute7,l_aca_row.attribute8,
1799 l_aca_row.attribute9,l_aca_row.attribute10,l_aca_row.attribute11,l_aca_row.attribute12,
1800 l_aca_row.attribute13,l_aca_row.attribute14,l_aca_row.attribute15,
1801 l_aca_row.security_group_id,1,
1802 l_aca_row.effective_date_from,l_aca_row.effective_date_to);
1803 -- Standard check for p_commit
1804 IF FND_API.to_Boolean( p_commit )
1805 THEN
1806 COMMIT WORK;
1807 END IF;
1808
1809 x_return_status := FND_API.G_RET_STS_SUCCESS;
1810
1811 ----------------dbms_output.put_line('GOING TO INSERT_IN_AMW_AP_ASSOC: '|| l_control_association_id);
1812
1813 process_amw_ap_assoc(
1814 p_assoc_mode => p_assoc_mode,
1815 p_control_association_id => l_control_association_id,
1816 p_control_id => l_aca_row.control_id,
1817 p_commit => p_commit,
1818 p_validation_level => p_validation_level,
1819 x_return_status => x_return_status,
1820 x_msg_count => x_msg_count,
1821 x_msg_data => x_msg_data
1822 );
1823
1824 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1825 RAISE FND_API.G_EXC_ERROR;
1826 END IF;
1827 end if;
1828 end loop;
1829 close c1;
1830 elsif p_assoc_mode = 'DISASSOCIATE' then
1831 open c2;
1835
1832 loop
1833 fetch c2 into l_update;
1834 exit when c2%notfound;
1836
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838
1839 process_amw_ap_assoc(
1840 p_assoc_mode => p_assoc_mode,
1841 p_control_association_id => l_update.control_association_id,
1842 p_control_id => l_update.control_id,
1843 p_commit => p_commit,
1844 p_validation_level => p_validation_level,
1845 x_return_status => x_return_status,
1846 x_msg_count => x_msg_count,
1847 x_msg_data => x_msg_data
1848 );
1849 delete from amw_control_associations
1850 where control_association_id=l_update.control_association_id
1851 and object_type='RISK_ORG'
1852 and pk1=p_risk_association_id;
1853
1854
1855 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1856 RAISE FND_API.G_EXC_ERROR;
1857 END IF;
1858 -- Standard check for p_commit
1859 IF FND_API.to_Boolean( p_commit )
1860 THEN
1861 COMMIT WORK;
1862 END IF;
1863 end loop;
1864 close c2;
1865 end if;
1866
1867 -------------------dbms_output.put_line('Process_AMW_Control_Assoc x_return_status: '||x_return_status);
1868 -- Debug Message
1869 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1870
1871 -- Standard call to get message count and if count is 1, get message info.
1872 FND_MSG_PUB.Count_And_Get(
1873 p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876
1877 exception
1878 WHEN FND_API.G_EXC_ERROR THEN
1879 ROLLBACK TO assoc_control_pvt;
1880 x_return_status := FND_API.G_RET_STS_ERROR;
1881 -- Standard call to get message count and if count=1, get the message
1882 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1883
1884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1885 ROLLBACK TO assoc_control_pvt;
1886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1887 -- Standard call to get message count and if count=1, get the message
1888 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1889
1890 WHEN OTHERS THEN
1891 ROLLBACK TO assoc_control_pvt;
1892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1893 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1894 THEN
1895 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1896 END IF;
1897 -- Standard call to get message count and if count=1, get the message
1898 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1899 end process_amw_control_assoc;
1900
1901 -- ==============================================================================
1902 -- Start of Comments
1903 -- ==============================================================================
1904 -- API Name
1905 -- Process_Amw_Ap_Assoc
1906 -- Type
1907 -- Private
1908 -- Pre-Req
1909 --
1910 -- Parameters
1911 --
1912 -- IN
1913 -- p_assoc_mode IN VARCHAR2 Otional Default = 'ASSOCIATE'
1914 -- p_control_association_id IN number Required
1915 -- p_control_id IN number Required
1916 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
1917 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1918 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1919 -- OUT
1920 -- x_return_status OUT VARCHAR2
1921 -- x_msg_count OUT NUMBER
1922 -- x_msg_data OUT VARCHAR2
1923 -- Version : Current version 1.0
1924 -- Note:
1925 --
1926 -- End of Comments
1927 -- ==============================================================================
1928 --
1929
1930 procedure process_amw_ap_assoc(
1931 p_assoc_mode in varchar2 := 'ASSOCIATE',
1932 p_control_association_id in number,
1933 p_control_id in number,
1934 p_commit in varchar2 := FND_API.G_FALSE,
1935 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1936 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1937 x_return_status out nocopy varchar2,
1938 x_msg_count out nocopy number,
1939 x_msg_data out nocopy varchar2
1940 )
1941 is
1942 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_AP_Assoc';
1943 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1944 l_control_id number := p_control_id;
1945 l_control_association_id number := p_control_association_id;
1946 l_ap_association_id number := 0;
1947
1948 cursor c1 is
1949 select ap_association_id,last_update_date,last_updated_by,
1950 creation_date,created_by,last_update_login,pk1,pk2,pk3,pk4,pk5,
1951 object_type,audit_procedure_id,attribute_category,attribute1,attribute2,
1952 attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1953 attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,
1954 attribute15,security_group_id,object_version_number,design_effectiveness,
1955 op_effectiveness
1956 from amw_ap_associations where object_type='CTRL' and pk1=l_control_id;
1957 l_aaa_row c1%rowtype;
1958 /*
1962 and pk1=l_control_association_id; ---and risk_id=;
1959 cursor c2 is
1960 select ap_association_id,audit_procedure_id
1961 from amw_ap_associations where object_type='CTRL_ORG'
1963 */
1964
1965 --mpande added 11/14/2003
1966 cursor c2 is
1967 select ap_association_id,audit_procedure_id
1968 from amw_process_organization apo, amw_risk_associations ara, amw_control_associations aca, amw_ap_associations apa
1969 where apo.process_organization_id = ara.pk1
1970 and ara.risk_association_id = aca.pk1
1971 and aca.control_association_id = p_control_association_id
1972 and apa.pk1 = apo.organization_id
1973 and apa.pk2 = apo.process_id
1974 and apa.pk3 = aca.control_id
1975 and apa.object_type='CTRL_ORG';
1976
1977
1978 l_update c2%rowtype;
1979
1980 row_count number := 0;
1981 l_process_id number;
1982 l_org_id number;
1983
1984 begin
1985 savepoint assoc_ap_pvt;
1986
1987 -- Initialize message list if p_init_msg_list is set to TRUE.
1988 IF FND_API.to_Boolean( p_init_msg_list )
1989 THEN
1990 FND_MSG_PUB.initialize;
1991 END IF;
1992
1993 -- =========================================================================
1994 -- Validate Environment
1995 -- =========================================================================
1996 IF FND_GLOBAL.User_Id IS NULL
1997 THEN
1998 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1999 RAISE FND_API.G_EXC_ERROR;
2000 END IF;
2001
2002 x_return_status := FND_API.G_RET_STS_SUCCESS;
2003
2004
2005 if p_assoc_mode = 'ASSOCIATE' then
2006 open c1;
2007 loop
2008 fetch c1 into l_aaa_row;
2009 exit when c1%notfound;
2010
2011 select apo.process_id, apo.organization_id
2012 into l_process_id, l_org_id
2013 from amw_process_organization apo, amw_risk_associations ara, amw_control_associations aca
2014 where apo.process_organization_id = ara.pk1
2015 and ara.risk_association_id = aca.pk1
2016 and aca.control_association_id = p_control_association_id;
2017
2018
2019
2020 /*
2021 ** select count(*) into row_count from amw_ap_associations
2022 ** where object_type='CTRL_ORG'
2023 ** and pk1=l_control_association_id
2024 ** and audit_procedure_id=l_aaa_row.audit_procedure_id;
2025 */
2026
2027 select count(*) into row_count from amw_ap_associations
2028 where object_type='CTRL_ORG'
2029 and pk1=l_org_id
2030 and pk2 = l_process_id
2031 and pk3 = p_control_id
2032 and audit_procedure_id=l_aaa_row.audit_procedure_id;
2033
2034
2035 if row_count = 0 then
2036 select amw_ap_associations_s.nextval into l_ap_association_id from dual;
2037
2038
2039 insert into amw_ap_associations(ap_association_id,last_update_date,
2040 last_updated_by,creation_date,created_by,
2041 last_update_login,pk1,pk2,pk3,pk4,pk5,object_type,
2042 audit_procedure_id,attribute_category,attribute1,attribute2,
2043 attribute3,attribute4,attribute5,attribute6,attribute7,
2044 attribute8,attribute9,attribute10,attribute11,attribute12,
2045 attribute13,attribute14,attribute15,security_group_id,
2046 object_version_number,design_effectiveness,op_effectiveness)
2047 values
2048 (l_ap_association_id,sysdate,G_USER_ID,sysdate,G_USER_ID,
2049 G_LOGIN_ID,l_org_id,l_process_id, p_control_id,
2050 l_aaa_row.pk4,l_aaa_row.pk5,'CTRL_ORG',l_aaa_row.audit_procedure_id,
2051 l_aaa_row.attribute_category,l_aaa_row.attribute1,l_aaa_row.attribute2,
2052 l_aaa_row.attribute3,l_aaa_row.attribute4,l_aaa_row.attribute5,l_aaa_row.attribute6,
2053 l_aaa_row.attribute7,l_aaa_row.attribute8,l_aaa_row.attribute9,l_aaa_row.attribute10,
2054 l_aaa_row.attribute11,l_aaa_row.attribute12,l_aaa_row.attribute13,l_aaa_row.attribute14,
2055 l_aaa_row.attribute15,l_aaa_row.security_group_id,1,
2056 l_aaa_row.design_effectiveness,l_aaa_row.op_effectiveness);
2057
2058 -- Standard check for p_commit
2059 IF FND_API.to_Boolean( p_commit )
2060 THEN
2061 COMMIT WORK;
2062 END IF;
2063 end if;
2064 end loop;
2065 close c1;
2066 elsif p_assoc_mode = 'DISASSOCIATE' then
2067 open c2;
2068 loop
2069 fetch c2 into l_update;
2070 exit when c2%notfound;
2071
2072 delete from amw_ap_associations
2073 where ap_association_id=l_update.ap_association_id ;
2074 -- commented by mpande
2075 -- and object_type='CTRL_ORG' ;
2076 -- and pk1=p_control_association_id;
2077
2078 -- Standard check for p_commit
2079 IF FND_API.to_Boolean( p_commit )
2080 THEN
2081 COMMIT WORK;
2082 END IF;
2083
2084 end loop;
2085 close c2;
2089 -- Debug Message
2086 end if;
2087
2088 -----------------------dbms_output.put_line('Process_AMW_AP_Assoc x_return_status: '||x_return_status);
2090 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2091
2092 -- Standard call to get message count and if count is 1, get message info.
2093 FND_MSG_PUB.Count_And_Get(
2094 p_count => x_msg_count,
2095 p_data => x_msg_data
2096 );
2097
2098 exception
2099 WHEN FND_API.G_EXC_ERROR THEN
2100 ROLLBACK TO assoc_ap_pvt;
2101 x_return_status := FND_API.G_RET_STS_ERROR;
2102 -- Standard call to get message count and if count=1, get the message
2103 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2104
2105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2106 ROLLBACK TO assoc_ap_pvt;
2107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2108 -- Standard call to get message count and if count=1, get the message
2109 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2110
2111 WHEN OTHERS THEN
2112 ROLLBACK TO assoc_ap_pvt;
2113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2114 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2115 THEN
2116 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2117 END IF;
2118 -- Standard call to get message count and if count=1, get the message
2119 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2120 end process_amw_ap_assoc;
2121
2122 -- ==============================================================================
2123 -- Start of Comments
2124 -- ==============================================================================
2125 -- API Name
2126 -- Process_Hierarchy_Count
2127 -- Type
2128 -- Public
2129 -- Pre-Req
2130 --
2131 -- Parameters
2132 --
2133 -- IN
2134 -- p_process_id IN NUMBER Optional Default = null
2135 -- p_organization_id IN NUMBER Optional Default = null
2136 -- p_mode IN VARCHAR2 Required Default = 'ASSOCIATE'
2137 -- p_apo_type IN apo_type Optional Default = null
2138 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
2139 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2140 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2141 --
2142 -- OUT
2143 -- x_return_status OUT VARCHAR2
2144 -- x_msg_count OUT NUMBER
2145 -- x_msg_data OUT VARCHAR2
2146 -- Version : Current version 1.0
2147 -- Note:
2148 --
2149 -- End of Comments
2150 -- ==============================================================================
2151 --
2152
2153 PROCEDURE process_hierarchy_count (
2154 p_process_id IN NUMBER := NULL,
2155 p_organization_id IN NUMBER := NULL,
2156 p_risk_count in number := null,
2157 p_control_count in number := null,
2158 p_mode IN VARCHAR2 := 'ASSOCIATE',
2159 p_commit IN VARCHAR2 := fnd_api.g_false,
2160 x_return_status OUT NOCOPY VARCHAR2,
2161 x_msg_count OUT NOCOPY NUMBER,
2162 x_msg_data OUT NOCOPY VARCHAR2
2163 ) IS
2164
2165 l_api_name CONSTANT VARCHAR2 (30) := 'process_hierarchy_count';
2166 l_api_version_number CONSTANT NUMBER := 1.0;
2167 x_process_organization_id NUMBER := 0;
2168 l_process_id NUMBER;
2169
2170 --- this API will be called from the amw_proc_org_hierarchy_pvt
2171 --- For a given process, this API traverses all the upward processes
2172 --- in that Process hierarchy for that organization
2173 cursor c1 is
2174 select process_id,
2175 nvl(risk_count,0) as risk_count,
2176 nvl(control_count,0) as control_count,
2177 process_organization_id,object_version_number
2178 FROM amw_process_organization
2179 WHERE organization_id = p_organization_id AND process_id IN (
2180 SELECT DISTINCT p2.process_id
2181 FROM amw_process p1,
2182 amw_process p2,
2183 amw_process_organization apo1,
2184 amw_process_organization apo2,
2185 wf_activities wa
2186 WHERE (p2.NAME, p2.item_type) IN (
2187 SELECT activity_name,
2188 activity_item_type
2189 FROM wf_process_activities
2190 CONNECT BY activity_name = PRIOR process_name
2191 AND activity_item_type = PRIOR process_item_type
2192 START WITH activity_name = p1.NAME
2193 AND activity_item_type = p1.item_type)
2194 AND p2.NAME = wa.NAME
2195 AND p2.item_type = wa.item_type
2196 AND wa.end_date IS NULL
2197 AND p2.process_id = apo2.process_id
2198 AND apo2.organization_id = apo1.organization_id
2199 AND p1.process_id = apo1.process_id
2200 ---and apo1.process_id=142
2201 AND apo1.process_id = p_process_id
2202 AND apo1.organization_id = p_organization_id);
2203
2204 assoc_risk c1%rowtype;
2205 -----find the control_count for this risk, and append this
2206 -----to all the control_counts of upward processes
2207 BEGIN
2208 ---Inserting process_id
2209 SAVEPOINT process_hierarchy_count;
2210 x_return_status := fnd_api.g_ret_sts_success;
2211 -- Standard call to check for call compatibility.
2212
2213 -- Debug Message
2214 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || ' start');
2215
2216 -- Initialize API return status to SUCCESS
2217 x_return_status := fnd_api.g_ret_sts_success;
2218
2219 /* Temporarily commenting out the validata session code ..... */
2220 -- =========================================================================
2221 -- Validate Environment
2225 RAISE fnd_api.g_exc_error;
2222 -- =========================================================================
2223 IF fnd_global.user_id IS NULL THEN
2224 amw_utility_pvt.error_message(p_message_name => 'USER_PROFILE_MISSING');
2226 END IF;
2227
2228 amw_wf_hierarchy_pkg.reset_proc_org_risk_ctrl_count;
2229
2230 /* Commented by mpande 11/13/2003 bug#
2231 OPEN c1;
2232 LOOP
2233 FETCH c1
2234 INTO assoc_risk;
2235 EXIT WHEN c1%NOTFOUND;
2236 --increment risk count for associate
2237
2238
2239 --dbms_output.put_line('In the ''RISK'' mode');
2240 assoc_risk.object_version_number := assoc_risk.object_version_number+1;
2241 if(p_mode = 'ASSOCIATE')then
2242 assoc_risk.risk_count := assoc_risk.risk_count+p_risk_count;
2243 assoc_risk.control_count := assoc_risk.control_count+p_control_count;
2244 elsif(p_mode = 'DISASSOCIATE') then
2245 assoc_risk.risk_count := assoc_risk.risk_count-p_risk_count;
2246 assoc_risk.control_count := assoc_risk.control_count-p_control_count;
2247 end if;
2248
2249
2250 if(p_process_id <> assoc_risk.process_id or p_mode='DISASSOCIATE')then
2251 ---if(p_process_id=assoc_risk.process_id)then
2252 ---dbms_output.put_line('process_id '||p_process_id||' p_mode: '||p_mode);
2253 ---end if;
2254 --update amw_process' risk_count
2255 UPDATE amw_process_organization
2256 SET risk_count = assoc_risk.risk_count,
2257 control_count = assoc_risk.control_count,
2258 object_version_number = assoc_risk.object_version_number,
2259 last_updated_by = g_user_id,
2260 last_update_date = SYSDATE,
2261 last_update_login = g_login_id
2262 WHERE process_organization_id = assoc_risk.process_organization_id;
2263 end if;
2264
2265 END LOOP;
2266 CLOSE c1;
2267
2268 -- =========================================================================
2269 -- End Validate Environment
2270 -- =========================================================================
2271 -- End commenting the session validation code ....
2272 */
2273 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2274 RAISE fnd_api.g_exc_error;
2275 END IF;
2276
2277 -- Standard check for p_commit
2278 IF fnd_api.to_boolean (p_commit) THEN
2279 COMMIT WORK;
2280 END IF;
2281
2282 --Debug Message
2283 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || ' end');
2284 -- Standard call to get message count and if count is 1, get message info.
2285 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2286 p_data => x_msg_data);
2287
2288 EXCEPTION
2289 WHEN fnd_api.g_exc_error THEN
2290 ROLLBACK TO process_hierarchy_count;
2291 x_return_status := fnd_api.g_ret_sts_error;
2292 -- Standard call to get message count and if count=1, get the message
2293 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
2294 p_count => x_msg_count,
2295 p_data => x_msg_data
2296 );
2297 WHEN fnd_api.g_exc_unexpected_error THEN
2298 ROLLBACK TO process_hierarchy_count;
2299 x_return_status := fnd_api.g_ret_sts_unexp_error;
2300 -- Standard call to get message count and if count=1, get the message
2301 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
2302 p_count => x_msg_count,
2303 p_data => x_msg_data
2304 );
2305 WHEN OTHERS THEN
2306 ROLLBACK TO process_hierarchy_count;
2307 x_return_status := fnd_api.g_ret_sts_unexp_error;
2308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2309 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2310 END IF;
2311 -- Standard call to get message count and if count=1, get the message
2312 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
2313 p_count => x_msg_count,
2314 p_data => x_msg_data
2318 -- ==============================================================================
2315 );
2316 END process_hierarchy_count;
2317
2319 -- Start of Comments
2320 -- ==============================================================================
2321 -- API Name
2322 -- Validate_Apo_Type
2323 -- Type
2324 -- Private
2325 -- Pre-Req
2326 --
2327 -- Parameters
2328 --
2329 -- IN
2330 -- p_api_version_number IN NUMBER REQUIRED
2331 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2332 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2333 -- p_apo_type IN apo_type Required
2334 --
2335 -- OUT
2336 -- x_return_status OUT VARCHAR2
2337 -- x_msg_count OUT NUMBER
2338 -- x_msg_data OUT VARCHAR2
2339 -- Version : Current version 1.0
2340 -- Note:
2341 --
2342 -- End of Comments
2343 -- ==============================================================================
2344 --
2345
2346 PROCEDURE validate_apo_type(
2347 p_api_version_number IN NUMBER,
2348 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2349 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2350 p_apo_type IN apo_type,
2351 x_return_status OUT nocopy VARCHAR2,
2352 x_msg_count OUT nocopy NUMBER,
2353 x_msg_data OUT nocopy VARCHAR2
2354 )
2355 IS
2356 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Process';
2357 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2358 l_object_version_number NUMBER;
2359 --l_process_rec AMW_Process_PVT.process_rec_type;
2360
2361 BEGIN
2362 -- Standard Start of API savepoint
2363 SAVEPOINT validate_process_pvt;
2364
2365 -- Standard call to check for call compatibility.
2366 IF NOT FND_API.Compatible_API_Call(l_api_version_number,p_api_version_number,l_api_name,G_PKG_NAME)
2367 THEN
2368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2369 END IF;
2370
2371 -- Initialize message list if p_init_msg_list is set to TRUE.
2372 IF FND_API.to_Boolean( p_init_msg_list )
2373 THEN
2374 FND_MSG_PUB.initialize;
2375 END IF;
2376
2377 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2378 check_apo_row(
2379 p_apo_type => p_apo_type,
2380 p_validation_mode => JTF_PLSQL_API.g_update,
2381 x_return_status => x_return_status
2382 );
2383
2384 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2385 RAISE FND_API.G_EXC_ERROR;
2386 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2388 END IF;
2389 END IF;
2390
2391 -- Debug Message
2392 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2393
2394 -- Initialize API return status to SUCCESS
2395 x_return_status := FND_API.G_RET_STS_SUCCESS;
2396
2397 -- Debug Message
2398 AMW_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2399
2400 -- Standard call to get message count and if count is 1, get message info.
2401 FND_MSG_PUB.Count_And_Get(
2402 p_count => x_msg_count,
2403 p_data => x_msg_data
2404 );
2405 EXCEPTION
2406
2407 WHEN AMW_Utility_PVT.resource_locked THEN
2408 x_return_status := FND_API.g_ret_sts_error;
2409 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
2410
2411 WHEN FND_API.G_EXC_ERROR THEN
2412 ROLLBACK TO VALIDATE_Process_pvt;
2413 x_return_status := FND_API.G_RET_STS_ERROR;
2414 -- Standard call to get message count and if count=1, get the message
2418 p_data => x_msg_data
2415 FND_MSG_PUB.Count_And_Get (
2416 p_encoded => FND_API.G_FALSE,
2417 p_count => x_msg_count,
2419 );
2420
2421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2422 ROLLBACK TO VALIDATE_Process_pvt;
2423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2424 -- Standard call to get message count and if count=1, get the message
2425 FND_MSG_PUB.Count_And_Get (
2426 p_encoded => FND_API.G_FALSE,
2427 p_count => x_msg_count,
2428 p_data => x_msg_data
2429 );
2430
2431 WHEN OTHERS THEN
2432 ROLLBACK TO VALIDATE_Process_pvt;
2433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2434 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2435 THEN
2436 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2437 END IF;
2438 -- Standard call to get message count and if count=1, get the message
2439 FND_MSG_PUB.Count_And_Get (
2440 p_encoded => FND_API.G_FALSE,
2441 p_count => x_msg_count,
2442 p_data => x_msg_data
2443 );
2444 End validate_apo_type;
2445
2446 -- ==============================================================================
2447 -- Start of Comments
2448 -- ==============================================================================
2449 -- API Name
2450 -- Check_Apo_Row
2451 -- Type
2452 -- Private
2453 -- Pre-Req
2454 --
2455 -- Parameters
2456 --
2457 -- IN
2458 -- p_apo_type IN apo_type Required
2459 -- p_validation_mode IN VARCHAR2 Optional Default = JTF_PLSQL_API.g_create
2460 --
2461 -- OUT
2462 -- x_return_status OUT VARCHAR2
2463 -- Version : Current version 1.0
2464 -- Note:
2465 --
2466 -- End of Comments
2467 -- ==============================================================================
2468 --
2469
2470 PROCEDURE check_apo_row(
2471 p_apo_type IN apo_type,
2472 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2473 x_return_status OUT nocopy VARCHAR2
2474 )
2475 IS
2476
2477 BEGIN
2478 x_return_status := FND_API.g_ret_sts_success;
2479
2480 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2481 IF p_apo_type.organization_id = FND_API.g_miss_num OR p_apo_type.organization_id IS NULL THEN
2482 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_process_organization_NO_organization_id');
2483 x_return_status := FND_API.g_ret_sts_error;
2484 RETURN;
2485 END IF;
2486
2487 IF p_apo_type.process_id = FND_API.g_miss_num OR p_apo_type.process_id IS NULL THEN
2488 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_process_NO_process_id');
2489 x_return_status := FND_API.g_ret_sts_error;
2490 RETURN;
2491 END IF;
2492 END IF;
2493 END check_apo_row;
2494
2495 FUNCTION GET_parent_process_id(p_process_id in number,
2496 p_organization_id in number) return number is
2497
2498
2499 cursor c1 is
2500 select aphv.parent_process_id,apo.end_date
2501 from amw_process_hierarchy_v aphv,amw_process_organization apo
2502 where aphv.child_process_id=p_process_id
2503 and apo.process_id=p_process_id
2504 and apo.organization_id=p_organization_id;
2505
2506 l_ppid c1%rowtype;
2507
2508 l_rpid number := -1;
2509 l_row_pid number := -1;
2510
2511 l_api_name varchar2(30) := 'Get_Parent_Process_Id';
2512
2513 begin
2514 open c1;
2515 loop
2516 fetch c1 into l_ppid;
2517 exit when c1%notfound;
2518 ---dbms_output.put_line('l_ppid.parent_process_id: '||
2519 ----raise FND_API.G_EXC_ERROR;
2520 if(l_ppid.end_date is null)then
2521 l_rpid := l_ppid.parent_process_id;
2522 exit;
2523 end if;
2524 l_row_pid := l_ppid.parent_process_id;
2525 end loop;
2526 close c1;
2527
2528 if(l_rpid = -1)then
2529 l_rpid := l_row_pid;
2530 end if;
2531
2532 return l_rpid;
2533
2534 exception
2535
2536 WHEN OTHERS THEN
2537 -- ROLLBACK TO assoc_ap_pvt;
2538 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2539 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2540 THEN
2541 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2542 END IF;
2543 ----dbms_output.put_line('EXCEPTION');
2544 RAISE;
2545 -- Standard call to get message count and if count=1, get the message
2546 -- FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
2547 /*
2548 when no_data_found then
2549 return null;
2550 when others then
2551
2552 return null;
2553 */
2554 end GET_parent_process_id;
2555
2556 END AMW_PROC_ORG_HIERARCHY_PVT;