1 PACKAGE AMW_FINSTMT_CERT_PVT as
2 /* $Header: amwvfscs.pls 120.0 2005/05/31 20:29:53 appldev noship $ */
3 PROCEDURE UPDATE_NEXT_LEVEL_PROC_INFO
4 (p_process_id IN NUMBER,
5 p_org_id IN NUMBER,
6 p_certification_id IN NUMBER
7 );
8
9 PROCEDURE UPDATE_CERTIFICATION_DETAIL
10 (p_process_id IN NUMBER,
11 p_org_id IN NUMBER,
12 p_certification_id IN NUMBER
13 );
14
15 PROCEDURE UPDATE_GLOBAL_PROC_INFO
16 (p_process_id IN NUMBER,
17 p_certification_id IN NUMBER,
18 p_global_org_id IN NUMBER
19 );
20
21 PROCEDURE UPDATE_LAST_EVALUATION_INFO
22 (p_process_id IN NUMBER,
23 p_org_id IN NUMBER,
24 p_certification_id IN NUMBER
25 );
26
27 PROCEDURE UPDATE_UNMITIGATED_RISKS
28 (p_process_id IN NUMBER,
29 p_org_id IN NUMBER,
30 p_certification_id IN NUMBER
31 );
32
33 PROCEDURE UPDATE_INEFFECTIVE_CONTROLS
34 (p_process_id IN NUMBER,
35 p_org_id IN NUMBER,
36 p_certification_id IN NUMBER
37 );
38
39 PROCEDURE POPULATE_SUMMARY
40 (p_certification_id IN VARCHAR2
41 );
42
43 PROCEDURE POPULATE_ALL_CERT_SUMMARY
44 (x_errbuf OUT NOCOPY VARCHAR2,
45 x_retcode OUT NOCOPY NUMBER,
46 p_certification_id IN NUMBER
47 );
48
49 PROCEDURE POPULATE_CERT_GENERAL_SUM
50 (p_certification_id IN NUMBER,
51 p_start_date IN DATE
52 );
53
54 /***comment out since amw.d ***************
55 PROCEDURE POPULATE_ALL_CERT_GENERAL_SUM
56 (errbuf OUT NOCOPY VARCHAR2,
57 retcode OUT NOCOPY VARCHAR2,
58 p_certification_id IN NUMBER
59 );
60 ******************/
61
62 PROCEDURE Populate_All_Fin_Proc_Eval_Sum(
63 errbuf OUT NOCOPY VARCHAR2,
64 retcode OUT NOCOPY VARCHAR2,
65 p_certification_id IN NUMBER
66 );
67
68 /***comment out since amw.d ***************
69 PROCEDURE Populate_All_Fin_Org_Eval_Sum(
70 errbuf OUT NOCOPY VARCHAR2,
71 retcode OUT NOCOPY VARCHAR2,
72 p_certification_id IN NUMBER
73 );
74 ******************/
75 PROCEDURE Populate_Fin_Stmt_Cert_Sum(
76 errbuf OUT NOCOPY VARCHAR2,
77 retcode OUT NOCOPY VARCHAR2,
78 p_certification_id IN NUMBER
79 );
80
81 ----------- Begining of the block of code added by Krishnan --------------------------------
82 /***comment out since amw.d
83 PROCEDURE build_amw_fin_cert_eval_sum(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY VARCHAR2, P_CERTIFICATION_ID in number);
84 ***/
85
86 PROCEDURE reset_amw_fin_cert_eval_sum(p_certification_id in number) ;
87
88
89
90 Procedure compute_values_for_eval_sum(P_CERTIFICATION_ID IN NUMBER,
91 P_FINANCIAL_STATEMENT_ID in number, P_STATEMENT_GROUP_ID in number,
92 --P_PARENT_FIN_ITEM_ID NUMBER,
93 P_ACCOUNT_ID in NUMBER, P_ACCOUNT_GROUP_ID in number, P_FINANCIAL_ITEM_ID in number,
94 P_OBJECT_TYPE in varchar2 , P_PROC_PENDING_CERTIFICATION out NOCOPY number,
95 P_TOTAL_NUMBER_OF_PROCESSES out NOCOPY number, P_PROC_CERTIFIED_WITH_ISSUES out NOCOPY number,
96 P_PROC_VERIFIED out NOCOPY number, P_org_with_ineffective_ctrls out NOCOPY number,
97 P_org_certified out NOCOPY number, P_proc_with_ineffective_ctrls out NOCOPY number,
98 P_unmitigated_risks out NOCOPY number, P_risks_verified out NOCOPY number,
99 P_ineffective_controls out NOCOPY number, P_controls_verified out NOCOPY number,
100 P_open_issues out NOCOPY number, P_PRO_PENDING_CERT_PRCNT out NOCOPY number,
101 P_PROCESSES_WITH_ISSUES_PRCNT out NOCOPY number, P_ORG_WITH_INEFF_CTRLS_PRCNT out NOCOPY number,
102 P_PROC_WITH_INEFF_CTRLS_PRCNT out NOCOPY number, P_UNMITIGATED_RISKS_PRCNT out NOCOPY number,
103 P_INEFFECTIVE_CONTROLS_PRCNT out NOCOPY number, P_START_DATE IN DATE ,
104 P_END_DATE IN DATE, P_PROCS_FOR_CERT_DONE out NOCOPY NUMBER, p_org_evaluated out NOCOPY NUMBER);
105
106
107
108 procedure insert_fin_cert_eval_sum(
109 X_FIN_CERTIFICATION_ID IN NUMBER,
110 X_FINANCIAL_STATEMENT_ID IN NUMBER,
111 X_FINANCIAL_ITEM_ID IN NUMBER,
112 X_ACCOUNT_GROUP_ID IN NUMBER,
113 X_NATURAL_ACCOUNT_ID IN NUMBER,
114 X_OBJECT_TYPE IN VARCHAR,
115 X_PROC_PENDING_CERTIFICATION IN NUMBER,
116 X_TOTAL_NUMBER_OF_PROCESSES IN NUMBER,
117 X_PROC_CERTIFIED_WITH_ISSUES IN NUMBER,
118 X_PROCS_FOR_CERT_DONE IN NUMBER,
119 x_proc_evaluated IN NUMBER,
120 X_ORG_WITH_INEFFECTIVE_CTRLS IN NUMBER,
121 -- X_ORG_CERTIFIED IN NUMBER,
122 x_orgs_FOR_CERT_DONE IN NUMBER,
123 x_orgs_evaluated IN NUMBER,
124 X_PROC_WITH_INEFFECTIVE_CTRLS IN NUMBER,
125 X_UNMITIGATED_RISKS IN NUMBER,
126 X_RISKS_VERIFIED IN NUMBER,
127 X_INEFFECTIVE_CONTROLS IN NUMBER,
128 X_CONTROLS_VERIFIED IN NUMBER,
129 X_OPEN_ISSUES IN NUMBER,
130 X_PRO_PENDING_CERT_PRCNT IN NUMBER,
131 X_PROCESSES_WITH_ISSUES_PRCNT IN NUMBER,
132 X_ORG_WITH_INEFF_CTRLS_PRCNT IN NUMBER,
133 X_PROC_WITH_INEFF_CTRLS_PRCNT IN NUMBER,
134 X_UNMITIGATED_RISKS_PRCNT IN NUMBER,
135 X_INEFFECTIVE_CTRLS_PRCNT IN NUMBER,
136 X_OBJ_CONTEXT IN NUMBER,
137 X_CREATED_BY IN NUMBER,
138 X_CREATION_DATE IN DATE,
139 X_LAST_UPDATED_BY IN NUMBER,
140 X_LAST_UPDATE_DATE IN DATE,
141 X_LAST_UPDATE_LOGIN IN NUMBER,
142 X_SECURITY_GROUP_ID IN NUMBER,
143 X_OBJECT_VERSION_NUMBER IN NUMBER
144 )
145
146 ;
147
148
149
150 Procedure GetTotalProcesses_for_account(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_TOTAL_NUMBER_OF_PROCESSES OUT NOCOPY number);
151
152 Procedure GetGLPeriodfor_FinCertEvalSum(P_Certification_ID in number, P_start_date out NOCOPY date, P_end_date out NOCOPY date);
153
154 Procedure CountProcsCertRecorded_Accnts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_PROCS_IN_CERTIFICATION OUT NOCOPY Number, p_start_date in date, p_end_date in date , p_fin_cert_id in number) ;
155
156 Procedure CountProcsEvaluated_Accnts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_PROCS_EVALUATED OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
157
158 Procedure CountOrgsEvaluated_accounts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_org_evaluated OUT NOCOPY Number,
159
160 p_start_date in date, p_end_date in date) ;
161
162
163
164 Procedure CountProcswithIssues_Accounts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_PROC_CERTIFIED_WITH_ISSUES OUT NOCOPY Number, p_start_date in date, p_end_date in date, p_fin_cert_id in number) ;
165
166 Procedure CountOrgsIneffCtrl_Accounts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_org_with_ineffective_ctrls OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
167
168 Procedure CountOrgswithIssues_Accounts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_org_cert_with_issues OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
169
170 Procedure CountOrgsCertified_accounts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_org_certified OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
171
172 Procedure CountProcsIneffCtrl_accounts(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, P_proc_with_ineffective_ctrls OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
173
174 Procedure CountIneffectiveCtrls_account(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, p_ineffective_controls OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
175
176 Procedure CountUnmittigatedRisk_account(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, p_unmitigated_risks OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
177
178 Procedure CountRisksVerified_account(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, p_risks_verified OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
179
180 Procedure CountControlsVerified_account(P_NATURAL_ACCOUNT_ID in number, P_account_group_id IN NUMBER, p_controls_verified OUT NOCOPY Number, p_start_date in date, p_end_date in date) ;
181
182 ----------------------------------------- Financial Item Level Computation for Fin Item Tab -----------------------
183 Procedure GetTotalProcesses_for_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
184 P_FINANCIAL_ITEM_ID in number , P_TOTAL_NUMBER_OF_PROCESSES OUT NOCOPY Number
185 ) ;
186
187
188 Procedure CountProcsCertRecorded_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
189 P_FINANCIAL_ITEM_ID in number , P_PROCS_IN_CERTIFICATION OUT NOCOPY Number
190 , p_start_date in DATE , p_end_date in DATE, p_fin_cert_id in number) ;
191
192 Procedure CountProcsEvaluated_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
193 P_FINANCIAL_ITEM_ID in number , P_PROCS_EVALUATED OUT NOCOPY Number
194 , p_start_date in DATE , p_end_date in DATE) ;
195
196 Procedure CountProcswithIssues_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
197 P_FINANCIAL_ITEM_ID in number , P_PROC_CERTIFIED_WITH_ISSUES OUT NOCOPY Number
198 , p_start_date in DATE , p_end_date in DATE, p_fin_cert_id in number) ;
199
200
201 Procedure CountOrgsIneffCtrl_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
202 P_FINANCIAL_ITEM_ID in number , P_org_with_ineffective_ctrls OUT NOCOPY number
203 , p_start_date in DATE , p_end_date in DATE) ;
204
205 Procedure CountOrgsEvaluated_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
206 P_FINANCIAL_ITEM_ID in number , P_org_evaluated OUT NOCOPY number
207 , p_start_date in DATE , p_end_date in DATE) ;
208
209 Procedure CountOrgsCertified_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
210 P_FINANCIAL_ITEM_ID in number , P_org_certified OUT NOCOPY number
211 , p_start_date in DATE , p_end_date in DATE) ;
212
213
214 Procedure CountProcsIneffCtrl_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
215 P_FINANCIAL_ITEM_ID in number , P_proc_with_ineffective_ctrls OUT NOCOPY number
216 , p_start_date in DATE , p_end_date in DATE) ;
217
218
219 Procedure CountIneffectiveCtrls_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
220 P_FINANCIAL_ITEM_ID in number, p_ineffective_controls OUT NOCOPY number
221 , p_start_date in DATE , p_end_date in DATE) ;
222
223 Procedure CountUnmittigatedRisk_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
224 P_FINANCIAL_ITEM_ID in number, p_unmitigated_risks OUT NOCOPY number
225 , p_start_date in DATE , p_end_date in DATE) ;
226
227
228 Procedure CountRisksVerified_finitem( P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
229 P_FINANCIAL_ITEM_ID in number, p_risks_verified OUT NOCOPY number
230 , p_start_date in DATE , p_end_date in DATE) ;
231
232
236
233 Procedure CountControlsVerified_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
234 P_FINANCIAL_ITEM_ID in number, p_controls_verified OUT NOCOPY number
235 , p_start_date in DATE , p_end_date in DATE) ;
237 Procedure CountOrgswithIssues_finitem(P_STATEMENT_GROUP_ID in number, P_FINANCIAL_STATEMENT_ID in number,
238 P_FINANCIAL_ITEM_ID in number, P_org_cert_with_issues OUT NOCOPY number
239 , p_start_date in DATE , p_end_date in DATE) ;
240
241
242
243 ---------------- end of code added by Krishnan --------------------------------------------
244
245 END AMW_FINSTMT_CERT_PVT;
[Home] [Help]