1 PACKAGE AMW_FINSTMT_CERT_BES_PKG AUTHID CURRENT_USER AS
2 /* $Header: amwfbuss.pls 120.2.12000000.2 2007/03/12 15:34:24 dliao ship $ */
3
4
5 G_REFRESH_FLAG VARCHAR2(1) := 'N';
6
7 /**05.25.2006 npanandi: bug 5142819 test***/
8 G_ORG_ERROR varchar2(1) := 'N';
9
10 TYPE certification_array is TABLE of NUMBER INDEX by pls_integer;
11 m_certification_list certification_array;
12
13
14 procedure DELETE_ROWS
15 ( x_fin_certification_id IN NUMBER,
16 x_table_name IN VARCHAR2 );
17
18 FUNCTION Populate_Fin_Stmt_Cert_Sum
19 ( p_subscription_guid in raw,
20 p_event in out NOCOPY wf_event_t
21 ) return VARCHAR2;
22
23 FUNCTION Update_Fin_Stmt_Cert_Sum
24 ( p_subscription_guid in raw,
25 p_event in out NOCOPY wf_event_t
26 ) return VARCHAR2;
27
28 FUNCTION Evaluation_Create
29 ( p_subscription_guid in raw,
30 p_event in out NOCOPY wf_event_t
31 ) return VARCHAR2;
32
33 FUNCTION Evaluation_Update
34 ( p_subscription_guid in raw,
35 p_event in out NOCOPY wf_event_t
36 ) return VARCHAR2;
37
38
39 PROCEDURE Evaluation_Update_Handler(
40 p_api_version_number IN NUMBER := 1.0,
41 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
42 p_commit IN VARCHAR2 := FND_API.g_false,
43 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
44 p_opinion_log_id IN NUMBER,
45 x_return_status OUT nocopy VARCHAR2,
46 x_msg_count OUT nocopy NUMBER,
47 x_msg_data OUT nocopy VARCHAR2
48 );
49
50 PROCEDURE Certification_Update_Handler(
51 p_api_version_number IN NUMBER := 1.0,
52 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
53 p_commit IN VARCHAR2 := FND_API.g_false,
54 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
55 p_opinion_log_id IN NUMBER,
56 x_return_status OUT nocopy VARCHAR2,
57 x_msg_count OUT nocopy NUMBER,
58 x_msg_data OUT nocopy VARCHAR2
59 );
60
61 FUNCTION Certification_Update
62 ( p_subscription_guid in raw,
63 p_event in out NOCOPY wf_event_t
64 ) return VARCHAR2;
65
66 PROCEDURE Master_Fin_Proc_Eval_Sum(
67 p_api_version_number IN NUMBER := 1.0,
68 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
69 p_commit IN VARCHAR2 := FND_API.g_false,
70 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
71 p_certification_id IN NUMBER,
72 p_start_date IN DATE := null,
73 p_mode IN VARCHAR2 := 'NEW',
74 x_return_status OUT nocopy VARCHAR2,
75 x_msg_count OUT nocopy NUMBER,
76 x_msg_data OUT nocopy VARCHAR2
77 );
78
79 PROCEDURE Populate_All_Fin_Proc_Eval_Sum(
80 p_api_version_number IN NUMBER := 1.0,
81 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
82 p_commit IN VARCHAR2 := FND_API.g_false,
83 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
84 p_certification_id IN NUMBER,
85 x_return_status OUT nocopy VARCHAR2,
86 x_msg_count OUT nocopy NUMBER,
87 x_msg_data OUT nocopy VARCHAR2
88 );
89
90 PROCEDURE Refresh_All_Fin_Proc_Eval_Sum(
91 p_api_version_number IN NUMBER := 1.0,
92 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
93 p_commit IN VARCHAR2 := FND_API.g_false,
94 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
95 p_certification_id IN NUMBER,
96 x_return_status OUT nocopy VARCHAR2,
97 x_msg_count OUT nocopy NUMBER,
98 x_msg_data OUT nocopy VARCHAR2
99 );
100
101 /***
102 PROCEDURE Populate_All_Fin_Risk_Ass_Sum(
103 p_api_version_number IN NUMBER := 1.0,
104 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
105 p_commit IN VARCHAR2 := FND_API.g_false,
106 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
107 p_certification_id IN NUMBER,
108 x_return_status OUT nocopy VARCHAR2,
109 x_msg_count OUT nocopy NUMBER,
110 x_msg_data OUT nocopy VARCHAR2
111 );
112
113 PROCEDURE Populate_All_Fin_Ctrl_Ass_Sum(
114 p_api_version_number IN NUMBER := 1.0,
115 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
116 p_commit IN VARCHAR2 := FND_API.g_false,
117 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
118 p_certification_id IN NUMBER,
119 x_return_status OUT nocopy VARCHAR2,
120 x_msg_count OUT nocopy NUMBER,
121 x_msg_data OUT nocopy VARCHAR2
122 );
123
124 PROCEDURE Populate_All_Fin_AP_Ass_Sum(
125 p_api_version_number IN NUMBER := 1.0,
126 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
127 p_commit IN VARCHAR2 := FND_API.g_false,
128 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
129 p_certification_id IN NUMBER,
130 x_return_status OUT nocopy VARCHAR2,
131 x_msg_count OUT nocopy NUMBER,
132 x_msg_data OUT nocopy VARCHAR2
133 );
134 ***/
135
136 PROCEDURE Populate_All_Fin_Org_Eval_Sum(
137 p_api_version_number IN NUMBER := 1.0,
138 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
139 p_commit IN VARCHAR2 := FND_API.g_false,
140 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
141 p_certification_id IN NUMBER,
142 x_return_status OUT nocopy VARCHAR2,
143 x_msg_count OUT nocopy NUMBER,
144 x_msg_data OUT nocopy VARCHAR2
145 );
146
147
148
149 PROCEDURE build_amw_fin_cert_eval_sum(
150 p_api_version_number IN NUMBER := 1.0,
151 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
152 p_commit IN VARCHAR2 := FND_API.g_false,
153 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
154 p_certification_id IN NUMBER,
155 x_return_status OUT nocopy VARCHAR2,
156 x_msg_count OUT nocopy NUMBER,
157 x_msg_data OUT nocopy VARCHAR2
158 );
159
160 PROCEDURE compute_values_for_eval_sum(
161 p_api_version_number IN NUMBER := 1.0,
162 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
163 p_commit IN VARCHAR2 := FND_API.g_false,
164 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
165 p_certification_id IN NUMBER,
166 p_financial_statement_id IN NUMBER ,
167 p_statement_group_id IN NUMBER ,
168 p_financial_item_id IN NUMBER,
169 p_account_group_id IN NUMBER,
170 p_account_id IN NUMBER,
171 p_object_type IN VARCHAR2,
172 x_return_status OUT nocopy VARCHAR2,
173 x_msg_count OUT nocopy NUMBER,
174 x_msg_data OUT nocopy VARCHAR2
175 );
176
177 PROCEDURE Populate_Fin_Org_Eval_Sum(
178 p_api_version_number IN NUMBER := 1.0,
179 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
180 p_commit IN VARCHAR2 := FND_API.g_false,
181 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
182 p_certification_id IN NUMBER,
183 p_start_date IN DATE,
184 p_end_date IN DATE,
185 p_organization_id IN NUMBER,
186 x_return_status OUT nocopy VARCHAR2,
187 x_msg_count OUT nocopy NUMBER,
188 x_msg_data OUT nocopy VARCHAR2
189 );
190
191 PROCEDURE Populate_Fin_Process_Eval_Sum(
192 p_api_version_number IN NUMBER := 1.0,
193 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
194 p_commit IN VARCHAR2 := FND_API.g_false,
195 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
196 p_certification_id IN NUMBER,
197 p_start_date IN DATE,
198 p_end_date IN DATE,
199 p_process_org_rev_id IN NUMBER,
200 p_process_id IN NUMBER,
201 p_revision_number IN NUMBER,
202 p_organization_id IN NUMBER,
203 p_account_process_flag IN VARCHAR2,
204 x_return_status OUT nocopy VARCHAR2,
205 x_msg_count OUT nocopy NUMBER,
206 x_msg_data OUT nocopy VARCHAR2
207 );
208
209 PROCEDURE Populate_Fin_Risk_Ass_Sum(
210 p_api_version_number IN NUMBER := 1.0,
211 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
212 p_commit IN VARCHAR2 := FND_API.g_false,
213 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
214 p_certification_id IN NUMBER,
215 x_return_status OUT nocopy VARCHAR2,
216 x_msg_count OUT nocopy NUMBER,
217 x_msg_data OUT nocopy VARCHAR2
218 );
219
220 PROCEDURE Populate_Fin_Risk_Ass_Sum_M(
221 p_api_version_number IN NUMBER := 1.0,
222 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
223 p_commit IN VARCHAR2 := FND_API.g_false,
224 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
225 p_certification_id IN NUMBER,
226 x_return_status OUT nocopy VARCHAR2,
227 x_msg_count OUT nocopy NUMBER,
228 x_msg_data OUT nocopy VARCHAR2
229 );
230
231 PROCEDURE Populate_Fin_Ctrl_Ass_Sum(
232 p_api_version_number IN NUMBER := 1.0,
233 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
234 p_commit IN VARCHAR2 := FND_API.g_false,
235 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
236 p_certification_id IN NUMBER,
237 x_return_status OUT nocopy VARCHAR2,
238 x_msg_count OUT nocopy NUMBER,
239 x_msg_data OUT nocopy VARCHAR2
240 );
241
242 PROCEDURE Populate_Fin_Ctrl_Ass_Sum_M(
243 p_api_version_number IN NUMBER := 1.0,
244 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
245 p_commit IN VARCHAR2 := FND_API.g_false,
246 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
247 p_certification_id IN NUMBER,
248 x_return_status OUT nocopy VARCHAR2,
249 x_msg_count OUT nocopy NUMBER,
250 x_msg_data OUT nocopy VARCHAR2
251 );
252
253 PROCEDURE Populate_Fin_AP_Ass_Sum(
254 p_api_version_number IN NUMBER := 1.0,
255 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
256 p_commit IN VARCHAR2 := FND_API.g_false,
257 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
258 p_certification_id IN NUMBER,
259 x_return_status OUT nocopy VARCHAR2,
260 x_msg_count OUT nocopy NUMBER,
261 x_msg_data OUT nocopy VARCHAR2
262 );
263
264 PROCEDURE Populate_Fin_AP_Ass_Sum_M(
265 p_api_version_number IN NUMBER := 1.0,
266 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
267 p_commit IN VARCHAR2 := FND_API.g_false,
268 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
269 p_certification_id IN NUMBER,
270 x_return_status OUT nocopy VARCHAR2,
271 x_msg_count OUT nocopy NUMBER,
272 x_msg_data OUT nocopy VARCHAR2
273 );
274
275 PROCEDURE POPULATE_PROC_HIERARCHY(
276 p_api_version_number IN NUMBER := 1.0,
277 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
278 p_commit IN VARCHAR2 := FND_API.g_false,
279 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
280 P_CERTIFICATION_ID IN NUMBER,
281 P_PROCESS_ID IN NUMBER,
282 P_ORGANIZATION_ID IN NUMBER,
283 p_account_process_flag IN VARCHAR2,
284 x_return_status OUT nocopy VARCHAR2,
285 x_msg_count OUT nocopy NUMBER,
286 x_msg_data OUT nocopy VARCHAR2
287 );
288
289 PROCEDURE INSERT_FIN_CERT_SCOPE(
290 p_api_version_number IN NUMBER := 1.0,
291 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
292 p_commit IN VARCHAR2 := FND_API.g_false,
293 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
294 p_certification_id IN NUMBER,
295 x_return_status OUT nocopy VARCHAR2,
296 x_msg_count OUT nocopy NUMBER,
297 x_msg_data OUT nocopy VARCHAR2
298 );
299
300 PROCEDURE INSERT_FIN_CTRL(
301 p_api_version_number IN NUMBER := 1.0,
302 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
303 p_commit IN VARCHAR2 := FND_API.g_false,
304 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
305 p_certification_id IN NUMBER,
306 x_return_status OUT nocopy VARCHAR2,
307 x_msg_count OUT nocopy NUMBER,
308 x_msg_data OUT nocopy VARCHAR2
309 );
310
311 PROCEDURE INSERT_FIN_RISK(
312 p_api_version_number IN NUMBER := 1.0,
313 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
314 p_commit IN VARCHAR2 := FND_API.g_false,
315 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
316 p_certification_id IN NUMBER,
317 x_return_status OUT nocopy VARCHAR2,
318 x_msg_count OUT nocopy NUMBER,
319 x_msg_data OUT nocopy VARCHAR2
320 );
321
322 Procedure GetGLPeriodfor_FinCertEvalSum
323 (P_Certification_ID in number,
324 P_start_date out NOCOPY date,
325 P_end_date out NOCOPY date
326 );
327
328 PROCEDURE Initialize
329 (P_Certification_ID in number
330 );
331
332
336 P_FINANCIAL_STATEMENT_ID IN NUMBER,
333 --Get ratio for financial item and account
334 FUNCTION Get_Ratio_Fin_Cert
335 ( P_CERTIFICATION_ID IN NUMBER,
337 P_STATEMENT_GROUP_ID IN NUMBER ,
338 P_ACCOUNT_ID IN NUMBER,
339 P_FINANCIAL_ITEM_ID IN NUMBER,
340 P_OBJECT_TYPE IN VARCHAR2,
341 P_STMT IN VARCHAR2) RETURN NUMBER;
342
343 /*
344 PROCEDURE Get_Fin_Evaluation
345 ( P_CERTIFICATION_ID IN NUMBER,
346 P_FINANCIAL_ITEM_ID IN NUMBER,
347 P_ACCOUNT_ID IN NUMBER,
348 P_OBJECT_TYPE IN VARCHAR2,
349 X_FIN_EVALUATION OUT NOCOPY NUMBER
350 );*/
351
352 procedure insert_fin_cert_eval_sum(
353 p_api_version_number IN NUMBER := 1.0,
354 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
355 p_commit IN VARCHAR2 := FND_API.g_false,
356 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
357 X_FIN_CERTIFICATION_ID IN NUMBER,
358 X_FINANCIAL_STATEMENT_ID IN NUMBER,
359 X_FINANCIAL_ITEM_ID IN NUMBER,
360 X_ACCOUNT_GROUP_ID IN NUMBER,
361 X_NATURAL_ACCOUNT_ID IN NUMBER,
362 X_OBJECT_TYPE IN VARCHAR,
363 X_PROC_PENDING_CERTIFICATION IN NUMBER,
364 X_TOTAL_NUMBER_OF_PROCESSES IN NUMBER,
365 X_PROC_CERTIFIED_WITH_ISSUES IN NUMBER,
366 X_PROCS_FOR_CERT_DONE IN NUMBER,
367 x_proc_evaluated IN NUMBER,
368 X_ORG_WITH_INEFFECTIVE_CTRLS IN NUMBER,
369 -- X_ORG_CERTIFIED IN NUMBER,
370 x_orgs_FOR_CERT_DONE IN NUMBER,
371 x_orgs_evaluated IN NUMBER,
372 x_total_orgs IN NUMBER,
373 X_PROC_WITH_INEFFECTIVE_CTRLS IN NUMBER,
374 X_UNMITIGATED_RISKS IN NUMBER,
375 X_RISKS_VERIFIED IN NUMBER,
376 X_TOTAL_RISKS IN NUMBER,
377 X_INEFFECTIVE_CONTROLS IN NUMBER,
378 X_CONTROLS_VERIFIED IN NUMBER,
379 X_TOTAL_CONTROLS IN NUMBER,
380 X_OPEN_ISSUES IN NUMBER,
381 X_PRO_PENDING_CERT_PRCNT IN NUMBER,
382 X_PROCESSES_WITH_ISSUES_PRCNT IN NUMBER,
383 X_ORG_WITH_INEFF_CTRLS_PRCNT IN NUMBER,
384 X_PROC_WITH_INEFF_CTRLS_PRCNT IN NUMBER,
385 X_UNMITIGATED_RISKS_PRCNT IN NUMBER,
386 X_INEFFECTIVE_CTRLS_PRCNT IN NUMBER,
387 X_OBJ_CONTEXT IN NUMBER,
388 X_CREATED_BY IN NUMBER,
389 X_CREATION_DATE IN DATE,
390 X_LAST_UPDATED_BY IN NUMBER,
391 X_LAST_UPDATE_DATE IN DATE,
392 X_LAST_UPDATE_LOGIN IN NUMBER,
393 X_SECURITY_GROUP_ID IN NUMBER,
394 X_OBJECT_VERSION_NUMBER IN NUMBER,
395 x_return_status OUT nocopy VARCHAR2,
396 x_msg_count OUT nocopy NUMBER,
397 x_msg_data OUT nocopy VARCHAR2
398 );
399
400
401 FUNCTION GetTotalProcesses
402 (
403 P_CERTIFICATION_ID in number,
404 P_STATEMENT_GROUP_ID in number,
405 P_FINANCIAL_STATEMENT_ID in number,
406 P_FINANCIAL_ITEM_ID in number,
407 P_ACCOUNT_GROUP_ID in number,
408 P_ACCOUNT_ID in number,
409 P_OBJECT_TYPE in varchar2) RETURN Number;
410
411
412 FUNCTION Get_Proc_Certified_Done
413 (
414 P_CERTIFICATION_ID in number,
415 P_STATEMENT_GROUP_ID in number,
416 P_FINANCIAL_STATEMENT_ID in number,
417 P_FINANCIAL_ITEM_ID in number,
418 P_ACCOUNT_GROUP_ID in number,
419 P_ACCOUNT_ID in number,
420 P_OBJECT_TYPE in varchar2) RETURN Number;
421
422 FUNCTION Get_Proc_Verified
423 (
424 P_CERTIFICATION_ID in number,
425 P_STATEMENT_GROUP_ID in number,
426 P_FINANCIAL_STATEMENT_ID in number,
427 P_FINANCIAL_ITEM_ID in number,
428 P_ACCOUNT_GROUP_ID in number,
429 P_ACCOUNT_ID in number,
430 P_OBJECT_TYPE in varchar2) RETURN Number;
431
432 FUNCTION Get_Proc_Verified_M
433 (
434 P_CERTIFICATION_ID in number,
435 P_STATEMENT_GROUP_ID in number,
436 P_FINANCIAL_STATEMENT_ID in number,
437 P_FINANCIAL_ITEM_ID in number,
438 P_ACCOUNT_GROUP_ID in number,
439 P_ACCOUNT_ID in number,
440 P_OBJECT_TYPE in varchar2) RETURN Number;
441
442 FUNCTION Get_PROC_CERT_WITH_ISSUES
443 (
444 P_CERTIFICATION_ID in number,
445 P_STATEMENT_GROUP_ID in number,
446 P_FINANCIAL_STATEMENT_ID in number,
447 P_FINANCIAL_ITEM_ID in number,
448 P_ACCOUNT_GROUP_ID in number,
449 P_ACCOUNT_ID in number,
450 P_OBJECT_TYPE in varchar2) RETURN Number;
451
452
453 FUNCTION Get_ORG_WITH_INEFF_CTRLS
454 (
455 P_CERTIFICATION_ID in number,
456 P_STATEMENT_GROUP_ID in number,
457 P_FINANCIAL_STATEMENT_ID in number,
458 P_FINANCIAL_ITEM_ID in number,
459 P_ACCOUNT_GROUP_ID in number,
460 P_ACCOUNT_ID in number,
464 (
461 P_OBJECT_TYPE in varchar2) RETURN Number;
462
463 FUNCTION Get_ORG_EVALUATED
465 P_CERTIFICATION_ID in number,
466 P_STATEMENT_GROUP_ID in number,
467 P_FINANCIAL_STATEMENT_ID in number,
468 P_FINANCIAL_ITEM_ID in number,
469 P_ACCOUNT_GROUP_ID in number,
470 P_ACCOUNT_ID in number,
471 P_OBJECT_TYPE in varchar2) RETURN Number;
472
473 FUNCTION Get_ORG_EVALUATED_M
474 (
475 P_CERTIFICATION_ID in number,
476 P_STATEMENT_GROUP_ID in number,
477 P_FINANCIAL_STATEMENT_ID in number,
478 P_FINANCIAL_ITEM_ID in number,
479 P_ACCOUNT_GROUP_ID in number,
480 P_ACCOUNT_ID in number,
481 P_OBJECT_TYPE in varchar2) RETURN Number;
482
483 FUNCTION Get_ORG_CERTIFIED
484 (
485 P_CERTIFICATION_ID in number,
486 P_STATEMENT_GROUP_ID in number,
487 P_FINANCIAL_STATEMENT_ID in number,
488 P_FINANCIAL_ITEM_ID in number,
489 P_ACCOUNT_GROUP_ID in number,
490 P_ACCOUNT_ID in number,
491 P_OBJECT_TYPE in varchar2) RETURN Number;
492
493 FUNCTION Get_TOTAL_ORGS
494 (
495 P_CERTIFICATION_ID in number,
496 P_STATEMENT_GROUP_ID in number,
497 P_FINANCIAL_STATEMENT_ID in number,
498 P_FINANCIAL_ITEM_ID in number,
499 P_ACCOUNT_GROUP_ID in number,
500 P_ACCOUNT_ID in number,
501 P_OBJECT_TYPE in varchar2) RETURN Number;
502
503 FUNCTION Get_PROC_WITH_INEFF_CTRLS
504 (
505 P_CERTIFICATION_ID in number,
506 P_STATEMENT_GROUP_ID in number,
507 P_FINANCIAL_STATEMENT_ID in number,
508 P_FINANCIAL_ITEM_ID in number,
509 P_ACCOUNT_GROUP_ID in number,
510 P_ACCOUNT_ID in number,
511 P_OBJECT_TYPE in varchar2 ) RETURN Number;
512
513 FUNCTION Get_INEFFECTIVE_CONTROLS
514 (
515 P_CERTIFICATION_ID in number,
516 P_STATEMENT_GROUP_ID in number,
517 P_FINANCIAL_STATEMENT_ID in number,
518 P_FINANCIAL_ITEM_ID in number,
519 P_ACCOUNT_GROUP_ID in number,
520 P_ACCOUNT_ID in number,
521 P_OBJECT_TYPE in varchar2)RETURN Number;
522
523 FUNCTION Get_UNMITIGATED_RISKS
524 (
525 P_CERTIFICATION_ID in number,
526 P_STATEMENT_GROUP_ID in number,
527 P_FINANCIAL_STATEMENT_ID in number,
528 P_FINANCIAL_ITEM_ID in number,
529 P_ACCOUNT_GROUP_ID in number,
530 P_ACCOUNT_ID in number,
531 P_OBJECT_TYPE in varchar2 )RETURN Number;
532
533 FUNCTION Get_RISKS_VERIFIED
534 (
535 P_CERTIFICATION_ID in number,
536 P_STATEMENT_GROUP_ID in number,
537 P_FINANCIAL_STATEMENT_ID in number,
538 P_FINANCIAL_ITEM_ID in number,
539 P_ACCOUNT_GROUP_ID in number,
540 P_ACCOUNT_ID in number,
541 P_OBJECT_TYPE in varchar2) RETURN Number;
542
543 FUNCTION Get_RISKS_VERIFIED_M
544 (
545 P_CERTIFICATION_ID in number,
546 P_STATEMENT_GROUP_ID in number,
547 P_FINANCIAL_STATEMENT_ID in number,
548 P_FINANCIAL_ITEM_ID in number,
549 P_ACCOUNT_GROUP_ID in number,
550 P_ACCOUNT_ID in number,
551 P_OBJECT_TYPE in varchar2) RETURN Number;
552
553 FUNCTION Get_Total_RISKS
554 (
555 P_CERTIFICATION_ID in number,
556 P_STATEMENT_GROUP_ID in number,
557 P_FINANCIAL_STATEMENT_ID in number,
558 P_FINANCIAL_ITEM_ID in number,
559 P_ACCOUNT_GROUP_ID in number,
560 P_ACCOUNT_ID in number,
561 P_OBJECT_TYPE in varchar2 ) RETURN Number;
562
563 FUNCTION Get_CONTROLS_VERIFIED
564 (
565 P_CERTIFICATION_ID in number,
566 P_STATEMENT_GROUP_ID in number,
567 P_FINANCIAL_STATEMENT_ID in number,
568 P_FINANCIAL_ITEM_ID in number,
569 P_ACCOUNT_GROUP_ID in number,
570 P_ACCOUNT_ID in number,
571 P_OBJECT_TYPE in varchar2 )RETURN Number;
572
573 FUNCTION Get_CONTROLS_VERIFIED_M
574 (
575 P_CERTIFICATION_ID in number,
576 P_STATEMENT_GROUP_ID in number,
577 P_FINANCIAL_STATEMENT_ID in number,
578 P_FINANCIAL_ITEM_ID in number,
579 P_ACCOUNT_GROUP_ID in number,
580 P_ACCOUNT_ID in number,
581 P_OBJECT_TYPE in varchar2 )RETURN Number;
582
583 FUNCTION Get_TOTAL_CONTROLS
584 (
585 P_CERTIFICATION_ID in number,
586 P_STATEMENT_GROUP_ID in number,
587 P_FINANCIAL_STATEMENT_ID in number,
588 P_FINANCIAL_ITEM_ID in number,
589 P_ACCOUNT_GROUP_ID in number,
590 P_ACCOUNT_ID in number,
591 P_OBJECT_TYPE in varchar2 ) RETURN Number;
592
593 ------followings are the procedure for dashboard population
594 /**************
595 PROCEDURE Populate_All_Cert_General_Sum(
596 p_api_version_number IN NUMBER := 1.0,
597 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
598 p_commit IN VARCHAR2 := FND_API.g_false,
599 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
600 p_certification_id IN NUMBER,
601 x_return_status OUT nocopy VARCHAR2,
602 x_msg_count OUT nocopy NUMBER,
603 x_msg_data OUT nocopy VARCHAR2
604 );
605 *************/
606
607 PROCEDURE Populate_Cert_General_Sum(
608 p_api_version_number IN NUMBER := 1.0,
609 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
610 p_commit IN VARCHAR2 := FND_API.g_false,
611 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
612 p_certification_id IN NUMBER,
613 p_start_date IN DATE,
614 x_return_status OUT nocopy VARCHAR2,
615 x_msg_count OUT nocopy NUMBER,
616 x_msg_data OUT nocopy VARCHAR2
617 );
618
619 PROCEDURE Get_global_proc_not_certified
620 (
621 p_certification_id IN NUMBER,
622 x_global_proc_not_certified OUT NOCOPY Number
623 );
624
625 PROCEDURE Get_global_proc_with_issue
626 (
627 p_certification_id IN NUMBER,
628 x_global_proc_with_issue OUT NOCOPY Number
629 );
630
631 PROCEDURE Get_local_proc_not_certified
632 (
633 p_certification_id IN NUMBER,
634 x_local_proc_not_certified OUT NOCOPY Number
635 );
636 PROCEDURE Get_local_proc_with_issue
637 (
638 p_certification_id IN NUMBER,
639 x_local_proc_with_issue OUT NOCOPY Number
640 );
641
642 PROCEDURE Get_global_proc_ineff_ctrl
643 (
644 p_certification_id IN NUMBER,
645 x_global_proc_ineff_ctrl OUT NOCOPY Number
646 );
647 PROCEDURE Get_local_proc_ineff_ctrl
648 (
649 p_certification_id IN NUMBER,
650 x_local_proc_ineff_ctrl OUT NOCOPY Number
651 );
652 PROCEDURE Get_unmitigated_risks
653 (
654 p_certification_id IN NUMBER,
655 x_unmitigated_risks OUT NOCOPY Number
656 );
657
658
659 PROCEDURE Get_ineffective_controls
660 (
661 p_certification_id IN NUMBER,
662 x_ineffective_controls OUT NOCOPY Number
663 );
664 PROCEDURE Get_orgs_pending_in_scope
665 (
666 p_certification_id IN NUMBER,
667 x_orgs_pending_in_scope OUT NOCOPY Number
668 );
669
670
671 PROCEDURE Is_Eval_Change
672 (
673 old_opinion_log_id IN NUMBER,
674 new_opinion_log_id IN NUMBER,
675 x_change_flag OUT NOCOPY VARCHAR2
676 );
677
678 PROCEDURE RISK_EVALUATION_HANDLER(
679 p_api_version_number IN NUMBER := 1.0,
680 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
681 p_commit IN VARCHAR2 := FND_API.g_false,
682 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
683 p_risk_id IN NUMBER,
684 p_org_id IN NUMBER,
685 p_process_id IN NUMBER,
686 p_opinion_log_id IN NUMBER,
687 x_return_status OUT nocopy VARCHAR2,
688 x_msg_count OUT nocopy NUMBER,
689 x_msg_data OUT nocopy VARCHAR2
690 );
691
692 PROCEDURE CONTROL_EVALUATION_HANDLER(
693 p_api_version_number IN NUMBER := 1.0,
694 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
695 p_commit IN VARCHAR2 := FND_API.g_false,
696 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
697 p_ctrl_id IN NUMBER,
698 p_org_id IN NUMBER,
699 p_opinion_log_id IN NUMBER,
700 x_return_status OUT nocopy VARCHAR2,
701 x_msg_count OUT nocopy NUMBER,
702 x_msg_data OUT nocopy VARCHAR2
703 );
704
705 PROCEDURE ORGANIZATION_CHANGE_HANDLER(
706 p_api_version_number IN NUMBER := 1.0,
707 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
711 p_opinion_log_id IN NUMBER,
708 p_commit IN VARCHAR2 := FND_API.g_false,
709 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
710 p_org_id IN NUMBER,
712 p_action IN VARCHAR2,
713 x_return_status OUT nocopy VARCHAR2,
714 x_msg_count OUT nocopy NUMBER,
715 x_msg_data OUT nocopy VARCHAR2
716 );
717
718 PROCEDURE PROCESS_CHANGE_HANDLER(
719 p_api_version_number IN NUMBER := 1.0,
720 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
721 p_commit IN VARCHAR2 := FND_API.g_false,
722 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
723 p_org_id IN NUMBER,
724 p_process_id IN NUMBER,
725 p_opinion_log_id IN NUMBER,
726 p_action IN VARCHAR2,
727 x_return_status OUT nocopy VARCHAR2,
728 x_msg_count OUT nocopy NUMBER,
729 x_msg_data OUT nocopy VARCHAR2
730 );
731
732 PROCEDURE reset_amw_fin_cert_eval_sum(p_certification_id in number) ;
733
734 PROCEDURE reset_amw_fin_proc_eval_sum(p_certification_id in number);
735
736 PROCEDURE reset_amw_fin_org_eval_sum(p_certification_id in number);
737
738 PROCEDURE reset_amw_cert_dashboard_sum(p_certification_id in number);
739
740 PROCEDURE reset_fin_all(p_certification_id in number);
741
742 END AMW_FINSTMT_CERT_BES_PKG;
[Home] [Help]