[Home] [Help]
PACKAGE BODY: APPS.AMW_FINSTMT_CERT_MIG_PKG
Source
1 PACKAGE BODY AMW_FINSTMT_CERT_MIG_PKG AS
2 /* $Header: amwfmigb.pls 120.0 2005/09/09 14:50:09 appldev noship $ */
3
4 --G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
5 --G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
6
7 G_PKG_NAME CONSTANT VARCHAR2 (30) := 'AMW_FINSTMT_CERT_MIG_PKG';
8 G_API_NAME CONSTANT VARCHAR2 (15) := 'amwfmigb.pls';
9
10
11
12 PROCEDURE POPULATE_PROC_HIERARCHY(
13 p_api_version_number IN NUMBER := 1.0,
14 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
15 p_commit IN VARCHAR2 := FND_API.g_false,
16 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
17 P_CERTIFICATION_ID NUMBER,
18 P_PROCESS_ID NUMBER,
19 P_ORGANIZATION_ID NUMBER,
20 p_account_process_flag VARCHAR2,
21 x_return_status OUT nocopy VARCHAR2,
22 x_msg_count OUT nocopy NUMBER,
23 x_msg_data OUT nocopy VARCHAR2
24 )IS
25
26 l_count NUMBER;
27
28 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_PROC_HIERARCHY';
29 l_api_version_number CONSTANT NUMBER := 1.0;
30
31 l_return_status VARCHAR2(32767);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(32767);
34
35 BEGIN
36
37 SAVEPOINT POPULATE_PROC_HIERARCHY;
38
39 -- Standard call to check for call compatibility.
40 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
41 p_api_version_number,
42 l_api_name,
43 G_PKG_NAME)
44 THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47
48 -- Initialize message list if p_init_msg_list is set to TRUE.
49 IF FND_API.to_Boolean( p_init_msg_list )
50 THEN
51 FND_MSG_PUB.initialize;
52 END IF;
53
54 -- Initialize API return status to SUCCESS
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 SELECT COUNT(1) INTO l_count FROM AMW_FIN_PROCESS_FLAT
58 WHERE PARENT_PROCESS_ID = P_PROCESS_ID
59 AND ORGANIZATION_ID = P_ORGANIZATION_ID
60 AND FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
61
62 --process directly associates to the account which belongs to this financial statement
63 -- to simplify the query, try amw_org_hierarchy_denorm
64 IF(l_count = 0 or l_count is null) THEN
65 IF p_account_process_flag = 'Y' THEN
66 INSERT INTO AMW_FIN_PROCESS_FLAT
67 (
68 FIN_CERTIFICATION_ID,
69 PARENT_PROCESS_ID,
70 CHILD_PROCESS_ID,
71 ORGANIZATION_ID,
72 CREATED_BY ,
73 CREATION_DATE ,
74 LAST_UPDATED_BY ,
75 LAST_UPDATE_DATE ,
76 LAST_UPDATE_LOGIN ,
77 SECURITY_GROUP_ID ,
78 OBJECT_VERSION_NUMBER )
79 select P_CERTIFICATION_ID, process_id, parent_child_id, organization_id, 1, sysdate, 1, sysdate, 1, null, 1
80 from amw_org_hierarchy_denorm
81 where organization_id = P_ORGANIZATION_ID
82 and hierarchy_type = 'A'
83 and process_id = P_PROCESS_ID
84 and (up_down_ind = 'D'
85 or (parent_child_id = -2 and up_down_ind= 'U'));
86 ELSE
87 INSERT INTO AMW_FIN_PROCESS_FLAT
88 (
89 FIN_CERTIFICATION_ID,
90 PARENT_PROCESS_ID,
91 CHILD_PROCESS_ID,
92 ORGANIZATION_ID,
93 CREATED_BY ,
94 CREATION_DATE ,
95 LAST_UPDATED_BY ,
96 LAST_UPDATE_DATE ,
97 LAST_UPDATE_LOGIN ,
98 SECURITY_GROUP_ID ,
99 OBJECT_VERSION_NUMBER )
100 select P_CERTIFICATION_ID, process_id, parent_child_id, organization_id, 1, sysdate, 1, sysdate, 1, null, 1
101 from amw_org_hierarchy_denorm
102 where organization_id = P_ORGANIZATION_ID
103 and hierarchy_type = 'A'
104 and process_id = P_PROCESS_ID
105 and up_down_ind = 'D';
106
107 END IF;
108
109 END IF;
110
111 --process directly associates to the account which belongs to this financial statement
112 -- to be deleted because it's a less efficient solution
113 -- Note: select p_process_id is very important. it's different from select parent_id
114 /*
115 IF p_account_process_flag = 'Y' THEN
116 INSERT INTO AMW_FIN_PROCESS_FLAT
117 (CERTIFICATION_ID,
118 PARENT_PROCESS_ID,
119 CHILD_PROCESS_ID,
120 ORGANIZATION_ID)
121 (SELECT distinct P_CERTIFICATION_ID, P_PROCESS_ID, child_id, organization_id
122 FROM AMW_APPROVED_HIERARCHIES
123 START WITH parent_id = P_PROCESS_ID
124 AND organization_id = P_ORGANIZATION_ID
125 CONNECT BY prior child_id = parent_id
126 AND prior organization_id = organization_id
127 UNION ALL
128 SELECT P_CERTIFICATION_ID, P_PROCESS_ID, -1, P_ORGANIZATION_ID FROM DUAL);
129 -- sub processes of the process which directly links to the account
130 ELSE
131 INSERT INTO AMW_FIN_PROCESS_FLAT
132 (CERTIFICATION_ID,
133 PARENT_PROCESS_ID,
134 CHILD_PROCESS_ID,
135 ORGANIZATION_ID)
136 SELECT distinct P_CERTIFICATION_ID, P_PROCESS_ID, child_id, organization_id
137 FROM AMW_APPROVED_HIERARCHIES
138 START WITH parent_id = P_PROCESS_ID
139 AND organization_id = P_ORGANIZATION_ID
140 CONNECT BY prior child_id = parent_id
141 AND prior organization_id = organization_id;
142 END IF;
143
144 **********************/
145
146 if(p_commit <> FND_API.g_false)
147 then commit;
148 end if;
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 x_msg_count := 1;
156 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
157 WHEN OTHERS THEN
158 ROLLBACK TO POPULATE_PROC_HIERARCHY;
159 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
160 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 x_msg_count := 1;
163 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
164 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
165 FND_MSG_PUB.Count_And_Get(
166 p_encoded => FND_API.G_FALSE,
167 p_count => x_msg_count,
168 p_data => x_msg_data);
169 RETURN;
170
171 END POPULATE_PROC_HIERARCHY;
172
173 ----------------------------- ********************************** ----------------------
174 -- this procedure build a flat table for financial certification, item, account, process
175 -- it contains the whole structure of financial information no matter weather process associated to it or not
176 -- it contains 3 layesr joins for 4 situations.
177 -- layer 1: join certification with fin_stmnt_items
178 -- layter 2: join resultset of layer1 with key_account
179 -- layer 3: join resultset of layer2 with process hierarchy via account_association
180
181 --situation 1: accounts have one or more children linking to a process
182 --situation 2: account directly assocates with a process and also links to one or more financial items
183 --situation 3: --- add all of childen accounts which associate with the top account which directly links to an item
184 --- e.g A2 is a child of A1. A1 links to an financial item which relates to fin certification
185 -- and P1 is associated with A2. so we want to add one record which contains A2, P1 info. in scope table
186
187 --situation 4:
188 -- account has sub-account, but account itself doesn't associate with any item. His parent/parent's parent links to -- -- item.
189 --- and its sub-account links to a process. e.g A1-A2-A3, A3-P1. so this query make A2-P
190
191 ---for performance reason, we split a big query into 4 queries based on 4 situtation.
192 ----------------------------- ********************************** ----------------------
193
194 PROCEDURE INSERT_FIN_CERT_SCOPE(
195 p_api_version_number IN NUMBER := 1.0,
196 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
197 p_commit IN VARCHAR2 := FND_API.g_false,
198 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
199 p_certification_id IN NUMBER,
200 x_return_status OUT nocopy VARCHAR2,
201 x_msg_count OUT nocopy NUMBER,
202 x_msg_data OUT nocopy VARCHAR2
203 ) IS
204
205 L_COUNT NUMBER;
206 L_COUNT2 NUMBER;
207 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FIN_CERT_SCOPE';
208 l_api_version_number CONSTANT NUMBER := 1.0;
209
210 l_return_status VARCHAR2(32767);
211 l_msg_count NUMBER;
212 l_msg_data VARCHAR2(32767);
213
214 BEGIN
215
216 SAVEPOINT INSERT_FIN_CERT_SCOPE;
217
218 -- Standard call to check for call compatibility.
219 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
220 p_api_version_number,
221 l_api_name,
222 G_PKG_NAME)
223 THEN
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END IF;
226
227 -- Initialize message list if p_init_msg_list is set to TRUE.
228 IF FND_API.to_Boolean( p_init_msg_list )
229 THEN
230 FND_MSG_PUB.initialize;
231 END IF;
232
233 -- Initialize API return status to SUCCESS
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 SELECT COUNT(1) INTO L_COUNT FROM AMW_FIN_CERT_SCOPE
237 WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
238
239 /** even if there is no process attached to an account. if the account belongs to the financial
240 ** certification, we should add it to the amw_fin_cert_scope table
241 SELECT COUNT(1) INTO L_COUNT2 FROM AMW_FIN_PROCESS_EVAL_SUM
242 WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
243
244
245 IF (L_COUNT2 = 0 OR L_COUNT2 IS NULL) THEN
246 RETURN;
247 END IF;
248 ****/
249
250 IF (L_COUNT = 0 OR L_COUNT IS NULL) THEN
251 ----add those accounts that have one or more children linking to a process
252 insert into amw_fin_cert_scope(
253 FIN_CERT_SCOPE_ID ,
254 FIN_CERTIFICATION_ID ,
255 STATEMENT_GROUP_ID ,
256 FINANCIAL_STATEMENT_ID,
257 FINANCIAL_ITEM_ID,
258 ACCOUNT_GROUP_ID ,
259 NATURAL_ACCOUNT_ID ,
260 ORGANIZATION_ID ,
261 PROCESS_ID ,
262 CREATED_BY ,
263 CREATION_DATE ,
264 LAST_UPDATED_BY ,
265 LAST_UPDATE_DATE ,
266 LAST_UPDATE_LOGIN ,
267 SECURITY_GROUP_ID ,
268 OBJECT_VERSION_NUMBER )
269 SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, itemaccmerge.statement_group_id, itemaccmerge.financial_statement_id, itemaccmerge.financial_item_id,
270 itemaccmerge.account_group_id, itemaccmerge.natural_account_id,itemaccmerge.organization_id, case when proc.child_process_id = -2 then itemaccmerge.process_id else proc.child_process_id end process_id,
271 1, sysdate, 1, sysdate, 1, null, 1
272 FROM
273 AMW_FIN_PROCESS_FLAT proc,
274
275 (SELECT temp.STATEMENT_GROUP_ID, temp.FINANCIAL_STATEMENT_ID, temp.FINANCIAL_ITEM_ID,
276 temp.ACCOUNT_GROUP_ID,
277 case when temp.NATURAL_ACCOUNT_ID = -1 then temp.child_natural_account_id else temp.NATURAL_ACCOUNT_ID end natural_account_id,
278 ACCREL.PK1 organization_id, ACCREL.PK2 process_id
279 FROM
280 (SELECT NATURAL_ACCOUNT_ID, PK1, PK2 FROM AMW_ACCT_ASSOCIATIONS
281 WHERE OBJECT_TYPE = 'PROCESS_ORG'
282 AND APPROVAL_DATE IS NOT NULL
283 AND DELETION_APPROVAL_DATE IS NULL
284 ) ACCREL,
285
286 (select temp2.statement_group_id, temp2.financial_statement_id, temp2.financial_item_id,
287 temp2.account_group_id, temp2.natural_account_id, flat.child_natural_account_id
288 from AMW_FIN_KEY_ACCT_FLAT flat,
289 (select distinct temp1.statement_group_id, temp1.financial_statement_id,
290 case when temp1.financial_item_id = -1 then temp1.child_financial_item_id
291 else temp1.financial_item_id end financial_item_id, itemaccrel.account_group_id, itemaccrel.natural_account_id
292 from AMW_FIN_ITEMS_KEY_ACC ITEMACCREL,
293
294 (select -1 financial_item_id, itemb.financial_item_id child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
295 FROM AMW_CERTIFICATION_B cert,
296 AMW_FIN_STMNT_ITEMS_B itemb
297 WHERE cert.CERTIFICATION_ID = P_CERTIFICATION_ID
298 and cert.statement_group_id = itemb.statement_group_id
299 and cert.financial_statement_id = itemb.financial_statement_id
300 UNION ALL
301 select itemb.financial_item_id, itemflat.child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
302 from AMW_FIN_ITEM_FLAT itemflat,
303 AMW_FIN_STMNT_ITEMS_B itemb,
304 AMW_CERTIFICATION_B cert
305 where
306 cert.CERTIFICATION_ID = P_CERTIFICATION_ID
307 and cert.statement_group_id = itemb.statement_group_id
308 and cert.financial_statement_id = itemb.financial_statement_id
309 and itemflat.parent_financial_item_id = itemb.financial_item_id
310 and itemflat.statement_group_id = itemb.statement_group_id
311 and itemflat.financial_statement_id = itemb.financial_statement_id) temp1
312 where
313 temp1.statement_group_id = ITEMACCREL.statement_group_id (+)
314 and temp1.financial_statement_id = ITEMACCREL.financial_statement_id (+)
315 and temp1.child_financial_item_id = ITEMACCREL.financial_item_id (+)) temp2
316 where temp2.account_group_id = flat.account_group_id
317 and temp2.natural_account_id = flat.parent_natural_account_id) temp
318 WHERE
319 ACCREL.NATURAL_ACCOUNT_ID = temp.CHILD_NATURAL_ACCOUNT_ID) itemaccmerge
320 -- only insert those account whose childen have a link to the process
321 --ACCREL.NATURAL_ACCOUNT_ID (+) = temp.CHILD_NATURAL_ACCOUNT_ID) itemaccmerge
322 WHERE proc.organization_id (+) = itemaccmerge.organization_id
323 and proc.parent_process_id (+) = itemaccmerge.process_id
324 and proc.fin_certification_id (+) = p_certification_id;
325
326 -- add account which has link to a item and also directly assocates with a process
330 STATEMENT_GROUP_ID,
327 insert into amw_fin_cert_scope(
328 FIN_CERT_SCOPE_ID ,
329 FIN_CERTIFICATION_ID ,
331 FINANCIAL_STATEMENT_ID ,
332 FINANCIAL_ITEM_ID,
333 ACCOUNT_GROUP_ID ,
334 NATURAL_ACCOUNT_ID ,
335 ORGANIZATION_ID ,
336 PROCESS_ID ,
337 CREATED_BY ,
338 CREATION_DATE ,
339 LAST_UPDATED_BY ,
340 LAST_UPDATE_DATE ,
341 LAST_UPDATE_LOGIN ,
342 SECURITY_GROUP_ID ,
343 OBJECT_VERSION_NUMBER )
344 SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, itemaccmerge.statement_group_id, itemaccmerge.financial_statement_id, itemaccmerge.financial_item_id,
345 itemaccmerge.account_group_id, itemaccmerge.natural_account_id,itemaccmerge.organization_id, case when proc.child_process_id = -2 then itemaccmerge.process_id else proc.child_process_id end process_id,
346 1, sysdate, 1, sysdate, 1, null, 1
347 FROM
348 AMW_FIN_PROCESS_FLAT proc,
349
350 (SELECT temp.STATEMENT_GROUP_ID, temp.FINANCIAL_STATEMENT_ID, temp.FINANCIAL_ITEM_ID,
351 temp.ACCOUNT_GROUP_ID,temp.NATURAL_ACCOUNT_ID,ACCREL.PK1 organization_id, ACCREL.PK2 process_id
352 FROM
353 (SELECT NATURAL_ACCOUNT_ID, PK1, PK2 FROM AMW_ACCT_ASSOCIATIONS
354 WHERE OBJECT_TYPE = 'PROCESS_ORG'
355 AND APPROVAL_DATE IS NOT NULL
356 AND DELETION_APPROVAL_DATE IS NULL
357 ) ACCREL,
358
359 (select distinct temp1.statement_group_id, temp1.financial_statement_id,
360 case when temp1.financial_item_id = -1 then temp1.child_financial_item_id
361 else temp1.financial_item_id end financial_item_id, itemaccrel.account_group_id, itemaccrel.natural_account_id
362 from AMW_FIN_ITEMS_KEY_ACC ITEMACCREL,
363
364 (select -1 financial_item_id, itemb.financial_item_id child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
365 FROM AMW_CERTIFICATION_B cert,
366 AMW_FIN_STMNT_ITEMS_B itemb
367 WHERE cert.CERTIFICATION_ID = P_CERTIFICATION_ID
368 and cert.statement_group_id = itemb.statement_group_id
369 and cert.financial_statement_id = itemb.financial_statement_id
370 UNION ALL
371 select itemb.financial_item_id, itemflat.child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
372 from AMW_FIN_ITEM_FLAT itemflat,
373 AMW_FIN_STMNT_ITEMS_B itemb,
374 AMW_CERTIFICATION_B cert
375 where
376 cert.CERTIFICATION_ID = P_CERTIFICATION_ID
377 and cert.statement_group_id = itemb.statement_group_id
378 and cert.financial_statement_id = itemb.financial_statement_id
379 and itemflat.parent_financial_item_id = itemb.financial_item_id
380 and itemflat.statement_group_id = itemb.statement_group_id
381 and itemflat.financial_statement_id = itemb.financial_statement_id) temp1
382 where
383 temp1.statement_group_id = ITEMACCREL.statement_group_id (+)
384 and temp1.financial_statement_id = ITEMACCREL.financial_statement_id (+)
385 and temp1.child_financial_item_id = ITEMACCREL.financial_item_id (+)) temp
386 WHERE
387 ACCREL.NATURAL_ACCOUNT_ID (+) = temp.NATURAL_ACCOUNT_ID) itemaccmerge
388 WHERE proc.organization_id (+) = itemaccmerge.organization_id
389 and proc.parent_process_id (+) = itemaccmerge.process_id
390 and proc.fin_certification_id (+) = p_certification_id;
391
392 --- add all of childen accounts which associate with the top account which directly links to an item
393 --- e.g A2 is a child of A1. A1 links to an financial item which relates to fin certification
394 -- and P1 is associated with A2. so we want to add one record which contains A2, P1 info. in scope table
395 insert into amw_fin_cert_scope(
396 FIN_CERT_SCOPE_ID ,
397 FIN_CERTIFICATION_ID ,
398 STATEMENT_GROUP_ID ,
399 FINANCIAL_STATEMENT_ID ,
400 FINANCIAL_ITEM_ID ,
401 ACCOUNT_GROUP_ID ,
402 NATURAL_ACCOUNT_ID ,
403 ORGANIZATION_ID ,
404 PROCESS_ID ,
405 CREATED_BY ,
406 CREATION_DATE ,
407 LAST_UPDATED_BY ,
408 LAST_UPDATE_DATE ,
409 LAST_UPDATE_LOGIN ,
410 SECURITY_GROUP_ID ,
411 OBJECT_VERSION_NUMBER )
412 SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, itemaccmerge.statement_group_id, itemaccmerge.financial_statement_id, itemaccmerge.financial_item_id,
413 itemaccmerge.account_group_id, itemaccmerge.natural_account_id,itemaccmerge.organization_id, case when proc.child_process_id = -2 then itemaccmerge.process_id else proc.child_process_id end process_id,
414 1, sysdate, 1, sysdate, 1, null, 1
415 FROM
416 AMW_FIN_PROCESS_FLAT proc,
417
418 (SELECT temp.STATEMENT_GROUP_ID, temp.FINANCIAL_STATEMENT_ID, temp.FINANCIAL_ITEM_ID,
419 temp.ACCOUNT_GROUP_ID,
420 temp.child_natural_account_id natural_account_id,
421 ACCREL.PK1 organization_id, ACCREL.PK2 process_id
422 FROM
423 (SELECT NATURAL_ACCOUNT_ID, PK1, PK2 FROM AMW_ACCT_ASSOCIATIONS
424 WHERE OBJECT_TYPE = 'PROCESS_ORG'
425 AND APPROVAL_DATE IS NOT NULL
426 AND DELETION_APPROVAL_DATE IS NULL
427 ) ACCREL,
428
429 (select temp2.statement_group_id, temp2.financial_statement_id, temp2.financial_item_id,
430 flat.account_group_id, flat.child_natural_account_id
431 from AMW_FIN_KEY_ACCT_FLAT flat,
432 (select distinct temp1.statement_group_id, temp1.financial_statement_id,
433 case when temp1.financial_item_id = -1 then temp1.child_financial_item_id
434 else temp1.financial_item_id end financial_item_id, itemaccrel.account_group_id, itemaccrel.natural_account_id
435 from AMW_FIN_ITEMS_KEY_ACC ITEMACCREL,
436
437 (select -1 financial_item_id, itemb.financial_item_id child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
441 and cert.statement_group_id = itemb.statement_group_id
438 FROM AMW_CERTIFICATION_B cert,
439 AMW_FIN_STMNT_ITEMS_B itemb
440 WHERE cert.CERTIFICATION_ID = P_CERTIFICATION_ID
442 and cert.financial_statement_id = itemb.financial_statement_id
443 UNION ALL
444 select itemb.financial_item_id, itemflat.child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
445 from AMW_FIN_ITEM_FLAT itemflat,
446 AMW_FIN_STMNT_ITEMS_B itemb,
447 AMW_CERTIFICATION_B cert
448 where
449 cert.CERTIFICATION_ID = P_CERTIFICATION_ID
450 and cert.statement_group_id = itemb.statement_group_id
451 and cert.financial_statement_id = itemb.financial_statement_id
452 and itemflat.parent_financial_item_id = itemb.financial_item_id
453 and itemflat.statement_group_id = itemb.statement_group_id
454 and itemflat.financial_statement_id = itemb.financial_statement_id) temp1
455 where
456 temp1.statement_group_id = ITEMACCREL.statement_group_id (+)
457 and temp1.financial_statement_id = ITEMACCREL.financial_statement_id (+)
458 and temp1.child_financial_item_id = ITEMACCREL.financial_item_id (+))temp2
459 where temp2.account_group_id = flat.account_group_id
460 and temp2.natural_account_id = flat.parent_natural_account_id) temp
461 WHERE
462 ACCREL.NATURAL_ACCOUNT_ID (+) = temp.CHILD_NATURAL_ACCOUNT_ID) itemaccmerge
463 WHERE proc.organization_id (+) = itemaccmerge.organization_id
464 and proc.parent_process_id (+) = itemaccmerge.process_id
465 and proc.fin_certification_id (+) = p_certification_id;
466
467
468
469 -- account has sub-account, but account itself doesn't associate with any item. His parent/parent's parent links to -- -- item.
470 --- and its sub-account links to a process. e.g A1-A2-A3, A3-P1. so this query make A2-P
471 insert into amw_fin_cert_scope(
472 FIN_CERT_SCOPE_ID ,
473 FIN_CERTIFICATION_ID ,
474 STATEMENT_GROUP_ID ,
475 FINANCIAL_STATEMENT_ID ,
476 FINANCIAL_ITEM_ID ,
477 ACCOUNT_GROUP_ID ,
478 NATURAL_ACCOUNT_ID ,
479 ORGANIZATION_ID ,
480 PROCESS_ID ,
481 CREATED_BY ,
482 CREATION_DATE ,
483 LAST_UPDATED_BY ,
484 LAST_UPDATE_DATE ,
485 LAST_UPDATE_LOGIN ,
486 SECURITY_GROUP_ID ,
487 OBJECT_VERSION_NUMBER )
488 SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, null statement_group_id, null financial_statement_id,
489 null financial_item_id,
490 itemaccmerge.account_group_id, itemaccmerge.natural_account_id,itemaccmerge.organization_id,
491 case when proc.child_process_id = -2 then itemaccmerge.process_id else proc.child_process_id end process_id,
492 1, sysdate, 1, sysdate, 1, null, 1
493 FROM
494 AMW_FIN_PROCESS_FLAT proc,
495
496 (SELECT temp.ACCOUNT_GROUP_ID,
497 temp.NATURAL_ACCOUNT_ID,
498 ACCREL.PK1 organization_id, ACCREL.PK2 process_id
499 FROM
500 (SELECT NATURAL_ACCOUNT_ID, PK1, PK2 FROM AMW_ACCT_ASSOCIATIONS
501 WHERE OBJECT_TYPE = 'PROCESS_ORG'
502 AND APPROVAL_DATE IS NOT NULL
503 AND DELETION_APPROVAL_DATE IS NULL
504 ) ACCREL,
505
506 (select flat.account_group_id, flat.parent_natural_account_id natural_account_id, flat.child_natural_account_id
507 from
508 (select flat.account_group_id, flat.parent_natural_account_id, flat.child_natural_account_id
509 from AMW_FIN_KEY_ACCT_FLAT flat
510 start with (account_group_id, parent_natural_account_id) in
511 (select account_group_id, natural_account_id
512 from amw_fin_cert_scope
513 where fin_certification_id = P_CERTIFICATION_ID)
514 connect by parent_natural_account_id = prior child_natural_account_id
515 and account_group_id = prior account_group_id) flat
516 where not exists (
517 select 'Y'
518 from AMW_FIN_CERT_SCOPE temp2
519 where flat.account_group_id = temp2.account_group_id
520 and flat.parent_natural_account_id = temp2.natural_account_id
521 and temp2.fin_certification_id = P_CERTIFICATION_ID) ) temp
522 WHERE
523 ACCREL.NATURAL_ACCOUNT_ID (+) = temp.CHILD_NATURAL_ACCOUNT_ID) itemaccmerge
524 WHERE proc.organization_id (+) = itemaccmerge.organization_id
525 and proc.parent_process_id (+) = itemaccmerge.process_id
526 and proc.fin_certification_id(+) = P_CERTIFICATION_ID;
527
528 END IF;
529
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
535 x_return_status := FND_API.G_RET_STS_ERROR;
536 x_msg_count := 1;
537 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
538 WHEN OTHERS THEN
539 ROLLBACK TO INSERT_FIN_CERT_SCOPE;
540 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
541 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 x_msg_count := 1;
544 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
545 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
546 FND_MSG_PUB.Count_And_Get(
547 p_encoded => FND_API.G_FALSE,
548 p_count => x_msg_count,
549 p_data => x_msg_data);
550
551 END INSERT_FIN_CERT_SCOPE;
552
553 ---------------------------------The following procedures are only for migration purpose------------
554 ---------------------------------name convention is the regular procedure name_M ------------------
555 PROCEDURE Populate_Fin_Risk_Ass_Sum_M(
556 p_api_version_number IN NUMBER := 1.0,
560 p_certification_id IN NUMBER,
557 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
558 p_commit IN VARCHAR2 := FND_API.g_false,
559 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
561 x_return_status OUT nocopy VARCHAR2,
562 x_msg_count OUT nocopy NUMBER,
563 x_msg_data OUT nocopy VARCHAR2
564 ) IS
565
566 CURSOR c_finrisks IS
567 SELECT
568 risks.risk_id,
569 risks.PK1,
570 risks.PK2,
571 risks.ASSOCIATION_CREATION_DATE,
572 risks.APPROVAL_DATE,
573 risks.DELETION_DATE,
574 risks.DELETION_APPROVAL_DATE,
575 risk.RISK_REV_ID
576 FROM
577 AMW_RISK_ASSOCIATIONS risks,
578 AMW_FIN_PROCESS_EVAL_SUM eval,
579 AMW_RISKS_B risk
580 WHERE
581 eval.fin_certification_id = p_certification_id
582 and risk.risk_id = risks.risk_id
583 and risk.CURR_APPROVED_FLAG = 'Y'
584 and risks.object_type='PROCESS_ORG'
585 and risks.PK1 = eval.organization_id
586 and risks.PK2 = eval.process_id
587 and risks.approval_date is not null
588 and risks.approval_date <= sysdate
589 and risks.deletion_approval_date is null
590 UNION ALL
591 SELECT
592 risks.risk_id,
593 risks.PK1,
594 risks.PK2,
595 risks.ASSOCIATION_CREATION_DATE,
596 risks.APPROVAL_DATE,
597 risks.DELETION_DATE,
598 risks.DELETION_APPROVAL_DATE,
599 risk.RISK_REV_ID
600 FROM
601 AMW_RISK_ASSOCIATIONS risks,
602 AMW_FIN_PROCESS_EVAL_SUM eval,
603 AMW_RISKS_B risk
604 WHERE
605 eval.fin_certification_id = p_certification_id
606 and risk.risk_id = risks.risk_id
607 and risk.CURR_APPROVED_FLAG = 'Y'
608 and risks.object_type='ENTITY_RISK'
609 and risks.PK1 = eval.organization_id
610 and risks.approval_date is not null
611 and risks.approval_date <= sysdate
612 and risks.deletion_approval_date is null;
613
614 --in risk association table, if type = 'PROCESS_FINCERT', pk1=certification_id, pk2=organization_id, pk3=process_id, pk4=opinion_log_id
615 CURSOR last_evaluation(l_risk_id number, l_organization_id number, l_process_id number) IS
616 select distinct ao.opinion_log_id
617 from AMW_OPINIONS_LOG ao,
618 AMW_OBJECT_OPINION_TYPES aoot,
619 AMW_OPINION_TYPES_B aot,
620 FND_OBJECTS fo
621 where ao.OBJECT_OPINION_TYPE_ID = aoot.OBJECT_OPINION_TYPE_ID
622 and aoot.OPINION_TYPE_ID = aot.OPINION_TYPE_ID
623 and aoot.OBJECT_ID = fo.OBJECT_ID
624 and fo.obj_name = 'AMW_ORG_PROCESS_RISK'
625 and aot.opinion_type_code = 'EVALUATION'
626 and ao.pk3_value = l_organization_id
627 and ao.pk4_value = l_process_id
628 and ao.pk1_value = l_risk_id
629 and ao.authored_date = (select max(aov2.authored_date)
630 from AMW_OPINIONS aov2
631 where aov2.object_opinion_type_id = ao.object_opinion_type_id
632 and aov2.pk3_value = ao.pk3_value
633 and aov2.pk1_value = ao.pk1_value
634 and aov2.pk4_value = ao.pk4_value);
635
636 l_count NUMBER;
637 m_opinion_log_id NUMBER;
638 l_error_message varchar2(4000);
639
640
641 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Fin_Risk_Ass_Sum_M';
642 l_api_version_number CONSTANT NUMBER := 1.0;
643
644 l_return_status VARCHAR2(32767);
645 l_msg_count NUMBER;
646 l_msg_data VARCHAR2(32767);
647
648 BEGIN
649
650 SAVEPOINT Populate_Fin_Risk_Ass_Sum_M;
651
652 -- Standard call to check for call compatibility.
653 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
654 p_api_version_number,
655 l_api_name,
656 G_PKG_NAME)
657 THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660
661 -- Initialize message list if p_init_msg_list is set to TRUE.
662 IF FND_API.to_Boolean( p_init_msg_list )
663 THEN
664 FND_MSG_PUB.initialize;
665 END IF;
666
667 -- Initialize API return status to SUCCESS
668 x_return_status := FND_API.G_RET_STS_SUCCESS;
669
670
671 SELECT COUNT(1) INTO l_count FROM AMW_RISK_ASSOCIATIONS
672 WHERE object_type = 'PROCESS_FINCERT'
673 AND pk1 = p_certification_id;
674
675 IF (l_count = 0) THEN
676 FOR risk_rec IN c_finrisks LOOP
677 exit when c_finrisks%notfound;
678
679 m_opinion_log_id := null;
680 OPEN last_evaluation(risk_rec.risk_id, risk_rec.pk1, risk_rec.pk2);
681 FETCH last_evaluation INTO m_opinion_log_id;
682 CLOSE last_evaluation;
683
684
685
686 INSERT INTO AMW_RISK_ASSOCIATIONS(
687 RISK_ASSOCIATION_ID,
688 RISK_ID,
689 PK1,
690 PK2,
691 PK3,
692 PK4,
693 CREATED_BY,
694 CREATION_DATE,
695 LAST_UPDATE_DATE,
696 LAST_UPDATED_BY,
697 LAST_UPDATE_LOGIN,
698 OBJECT_VERSION_NUMBER,
699 OBJECT_TYPE,
700 ASSOCIATION_CREATION_DATE,
701 APPROVAL_DATE,
702 DELETION_DATE,
703 DELETION_APPROVAL_DATE,
704 RISK_REV_ID)
705 VALUES ( amw_risk_associations_s.nextval,
706 risk_rec.risk_id,
707 p_certification_id,
708 risk_rec.PK1,
709 risk_rec.PK2,
710 m_opinion_log_id,
711 FND_GLOBAL.USER_ID,
712 SYSDATE,
716 1,
713 SYSDATE,
714 FND_GLOBAL.USER_ID,
715 FND_GLOBAL.USER_ID,
717 'PROCESS_FINCERT',
718 risk_rec.ASSOCIATION_CREATION_DATE,
719 risk_rec.APPROVAL_DATE,
720 risk_rec.DELETION_DATE,
721 risk_rec.DELETION_APPROVAL_DATE,
722 risk_rec.RISK_REV_ID);
723
724 END LOOP;
725 if(p_commit <> FND_API.g_false)
726 then commit;
727 end if;
728
729 END IF;
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732
733 EXCEPTION
734 WHEN NO_DATA_FOUND THEN
735 IF c_finrisks%ISOPEN THEN
736 close c_finrisks;
737 END IF;
738 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 x_msg_count := 1;
741 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
742 WHEN OTHERS THEN
743 IF c_finrisks%ISOPEN THEN
744 close c_finrisks;
745 END IF;
746 ROLLBACK TO Populate_Fin_Risk_Ass_Sum_M;
747 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
748 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750 x_msg_count := 1;
751 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
752 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
753 FND_MSG_PUB.Count_And_Get(
754 p_encoded => FND_API.G_FALSE,
755 p_count => x_msg_count,
756 p_data => x_msg_data);
757 RETURN;
758
759 END Populate_Fin_Risk_Ass_Sum_M;
760
761 PROCEDURE Populate_Fin_Ctrl_Ass_Sum_M(
762 p_api_version_number IN NUMBER := 1.0,
763 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
764 p_commit IN VARCHAR2 := FND_API.g_false,
765 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
766 p_certification_id IN NUMBER,
767 x_return_status OUT nocopy VARCHAR2,
768 x_msg_count OUT nocopy NUMBER,
769 x_msg_data OUT nocopy VARCHAR2
770 ) IS
771 CURSOR c_fincontrols IS
772
773 SELECT
774 controls.control_id,
775 controls.PK1,
776 controls.PK2,
777 controls.PK3,
778 controls.ASSOCIATION_CREATION_DATE,
779 controls.APPROVAL_DATE,
780 controls.DELETION_DATE,
781 controls.DELETION_APPROVAL_DATE,
782 control.CONTROL_REV_ID
783 FROM
784 AMW_RISK_ASSOCIATIONS risks,
785 AMW_CONTROL_ASSOCIATIONS controls,
786 AMW_CONTROLS_B control
787 WHERE
788 controls.object_type='RISK_ORG'
789 and control.CURR_APPROVED_FLAG = 'Y'
790 and control.control_id = controls.control_id
791 and risks.PK1 = p_certification_id
792 and risks.PK2 = controls.PK1
793 and risks.PK3 = controls.PK2
794 and controls.PK3 = risks.risk_id
795 and risks.object_type = 'PROCESS_FINCERT'
796 UNION ALL
797 SELECT
798 controls.control_id,
799 controls.PK1,
800 controls.PK2,
801 controls.PK3,
802 controls.ASSOCIATION_CREATION_DATE,
803 controls.APPROVAL_DATE,
804 controls.DELETION_DATE,
805 controls.DELETION_APPROVAL_DATE,
806 control.CONTROL_REV_ID
807 FROM
808 AMW_RISK_ASSOCIATIONS risks,
809 AMW_CONTROL_ASSOCIATIONS controls,
810 AMW_CONTROLS_B control
811 WHERE
812 controls.object_type='ENTITY_CONTROL'
813 and control.CURR_APPROVED_FLAG = 'Y'
814 and control.control_id = controls.control_id
815 and risks.PK1 = p_certification_id
816 and risks.PK2 = controls.PK1
817 and risks.PK3 IS NULL
818 and controls.PK3 = risks.risk_id
819 and risks.object_type = 'PROCESS_FINCERT';
820
821
822
823 --in control association table, if type = 'RISK_FINCERT', pk1=certification_id, pk2=organization_id, pk3=process_id, pk4=risk_id, pk5=opinion_log_id
824 CURSOR last_evaluation(l_organization_id number, l_control_id number) IS
825 select distinct ao.opinion_log_id
826 from
827 AMW_OPINIONS_LOG ao,
828 AMW_OBJECT_OPINION_TYPES aoot,
829 AMW_OPINION_TYPES_B aot,
830 FND_OBJECTS fo
831 where ao.OBJECT_OPINION_TYPE_ID = aoot.OBJECT_OPINION_TYPE_ID
832 and aoot.OPINION_TYPE_ID = aot.OPINION_TYPE_ID
833 and aoot.OBJECT_ID = fo.OBJECT_ID
834 and fo.obj_name = 'AMW_ORG_CONTROL'
835 and aot.opinion_type_code = 'EVALUATION'
836 and ao.pk3_value = l_organization_id
837 and ao.pk1_value = l_control_id
838 and ao.authored_date = (select max(aov2.authored_date)
839 from AMW_OPINIONS aov2
840 where aov2.object_opinion_type_id = ao.object_opinion_type_id
841 and aov2.pk3_value = ao.pk3_value
842 and aov2.pk1_value = ao.pk1_value);
843
844 l_count NUMBER;
845 m_opinion_log_id NUMBER;
846
847 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Fin_Ctrl_Ass_Sum_M';
848 l_api_version_number CONSTANT NUMBER := 1.0;
849
850 l_return_status VARCHAR2(32767);
851 l_msg_count NUMBER;
852 l_msg_data VARCHAR2(32767);
853
854 BEGIN
855
856 SAVEPOINT Populate_Fin_Ctrl_Ass_Sum_M;
857
858 -- Standard call to check for call compatibility.
859 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
860 p_api_version_number,
861 l_api_name,
862 G_PKG_NAME)
863 THEN
867 -- Initialize message list if p_init_msg_list is set to TRUE.
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 END IF;
866
868 IF FND_API.to_Boolean( p_init_msg_list )
869 THEN
870 FND_MSG_PUB.initialize;
871 END IF;
872
873 -- Initialize API return status to SUCCESS
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875
876
877 SELECT COUNT(1) INTO l_count FROM AMW_CONTROL_ASSOCIATIONS
878 WHERE OBJECT_TYPE = 'RISK_FINCERT'
879 and PK1 = p_certification_id;
880
881 IF (l_count = 0) THEN
882 FOR control_rec IN c_fincontrols LOOP
883 exit when c_fincontrols%notfound;
884
885 m_opinion_log_id := null;
886 OPEN last_evaluation(control_rec.pk1, control_rec.control_id);
887 FETCH last_evaluation INTO m_opinion_log_id;
888 CLOSE last_evaluation;
889
890 INSERT INTO AMW_CONTROL_ASSOCIATIONS(
891 CONTROL_ASSOCIATION_ID,
892 CONTROL_ID,
893 PK1,
894 PK2,
895 PK3,
896 PK4,
897 PK5,
898 CREATED_BY,
899 CREATION_DATE,
900 LAST_UPDATE_DATE,
901 LAST_UPDATED_BY,
902 LAST_UPDATE_LOGIN,
903 OBJECT_VERSION_NUMBER,
904 OBJECT_TYPE,
905 ASSOCIATION_CREATION_DATE,
906 APPROVAL_DATE,
907 DELETION_DATE,
908 DELETION_APPROVAL_DATE,
909 CONTROL_REV_ID)
910 VALUES (AMW_CONTROL_ASSOCIATIONS_S.nextval,
911 control_rec.control_id,
912 p_certification_id,
913 control_rec.PK1,
914 control_rec.PK2,
915 control_rec.PK3,
916 m_opinion_log_id,
917 FND_GLOBAL.USER_ID,
918 SYSDATE,
919 SYSDATE,
920 FND_GLOBAL.USER_ID,
921 FND_GLOBAL.USER_ID,
922 1,
923 'RISK_FINCERT',
924 control_rec.ASSOCIATION_CREATION_DATE,
925 control_rec.APPROVAL_DATE,
926 control_rec.DELETION_DATE,
927 control_rec.DELETION_APPROVAL_DATE,
928 control_rec.CONTROL_REV_ID);
929
930 END LOOP;
931 if(p_commit <> FND_API.g_false)
932 then commit;
933 end if;
934 END IF;
935 x_return_status := FND_API.G_RET_STS_SUCCESS;
936
937 EXCEPTION
938 WHEN NO_DATA_FOUND THEN
939 IF c_fincontrols%ISOPEN THEN
940 close c_fincontrols;
941 END IF;
942 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
943 x_return_status := FND_API.G_RET_STS_ERROR;
944 x_msg_count := 1;
945 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
946 WHEN OTHERS THEN
947 IF c_fincontrols%ISOPEN THEN
948 close c_fincontrols;
949 END IF;
950 ROLLBACK TO Populate_Fin_Ctrl_Ass_Sum_M;
951 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
952 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954 x_msg_count := 1;
955 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
956 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
957 FND_MSG_PUB.Count_And_Get(
958 p_encoded => FND_API.G_FALSE,
959 p_count => x_msg_count,
960 p_data => x_msg_data);
961 RETURN;
962
963 END Populate_Fin_Ctrl_Ass_Sum_M;
967 PROCEDURE Populate_Fin_AP_Ass_Sum_M(
964
965
966 -------------populate control association which related to financial certification ----
968 p_api_version_number IN NUMBER := 1.0,
969 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
970 p_commit IN VARCHAR2 := FND_API.g_false,
971 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
972 p_certification_id IN NUMBER,
973 x_return_status OUT nocopy VARCHAR2,
974 x_msg_count OUT nocopy NUMBER,
975 x_msg_data OUT nocopy VARCHAR2
976 ) IS
977
978 CURSOR c_finap IS
979 SELECT
980 ap.AUDIT_PROCEDURE_ID,
981 ap.PK1,
982 ap.PK2,
983 ap.PK3,
984 ap.ASSOCIATION_CREATION_DATE,
985 ap.APPROVAL_DATE,
986 ap.DELETION_DATE,
987 ap.DELETION_APPROVAL_DATE,
988 apb.AUDIT_PROCEDURE_REV_ID
989 FROM
990 AMW_AP_ASSOCIATIONS ap,
991 AMW_CONTROL_ASSOCIATIONS controls,
992 AMW_AUDIT_PROCEDURES_B apb
993 WHERE
994 ap.object_type='CTRL_ORG'
995 and apb.CURR_APPROVED_FLAG = 'Y'
996 and ap.audit_procedure_id = apb.audit_procedure_id
997 and controls.PK1 = p_certification_id
998 and controls.PK2 = ap.PK1
999 and controls.PK2 = ap.PK2
1000 and controls.control_id = ap.PK3
1001 and controls.object_type = 'RISK_FINCERT'
1002 UNION ALL
1003 SELECT
1004 ap.AUDIT_PROCEDURE_ID,
1005 ap.PK1,
1006 ap.PK2,
1007 ap.PK3,
1008 ap.ASSOCIATION_CREATION_DATE,
1009 ap.APPROVAL_DATE,
1010 ap.DELETION_DATE,
1011 ap.DELETION_APPROVAL_DATE,
1012 apb.AUDIT_PROCEDURE_REV_ID
1013 FROM
1014 AMW_AP_ASSOCIATIONS ap,
1015 AMW_CONTROL_ASSOCIATIONS controls,
1016 AMW_AUDIT_PROCEDURES_B apb
1017 WHERE
1018 ap.object_type='ENTITY_CTRL_AP'
1019 and apb.CURR_APPROVED_FLAG = 'Y'
1020 and ap.audit_procedure_id = apb.audit_procedure_id
1021 and controls.PK1 = p_certification_id
1022 and controls.PK2 = ap.PK1
1023 --and controls.PK3 = ap.PK2
1024 and controls.PK3 is null
1025 and controls.control_id = ap.PK3
1026 and controls.object_type = 'RISK_FINCERT';
1027
1028 --need check opinion framework doc
1029 --in ap association table, if type = 'CTRL_FINCERT', pk1=certification_id, pk2=organization_id, pk3=process_id, pk4=control_id, pk5=opinion_id
1030 CURSOR last_evaluation(l_audit_procedure_id number, l_organization_id number, l_control_id number) IS
1031 SELECT distinct aov.opinion_id
1032 FROM AMW_OPINION_M_V aov
1033 WHERE
1034 aov.object_name = 'AMW_ORG_AP_CONTROL'
1035 AND aov.opinion_type_code = 'EVALUATION'
1036 AND aov.opinion_component_code = 'OVERALL'
1037 AND aov.pk3_value = l_organization_id
1038 AND aov.pk4_value = l_audit_procedure_id
1039 AND aov.pk1_value = l_control_id
1040 AND aov.authored_date = (select max(aov2.authored_date)
1041 from AMW_OPINIONS aov2
1042 where aov2.object_opinion_type_id = aov.object_opinion_type_id
1043 and aov2.pk3_value = aov.pk3_value
1044 and aov2.pk1_value = aov.pk1_value
1045 and aov2.pk4_value = aov.pk4_value);
1046
1047
1048 l_count NUMBER;
1049 m_opinion_id NUMBER;
1050
1051
1052 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Fin_AP_Ass_Sum_M';
1053 l_api_version_number CONSTANT NUMBER := 1.0;
1054
1055 l_return_status VARCHAR2(32767);
1056 l_msg_count NUMBER;
1057 l_msg_data VARCHAR2(32767);
1058
1059 BEGIN
1060
1061 SAVEPOINT Populate_Fin_AP_Ass_Sum_M;
1062
1063 -- Standard call to check for call compatibility.
1064
1065 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1066 p_api_version_number,
1067 l_api_name,
1068 G_PKG_NAME)
1069 THEN
1070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1071 END IF;
1072
1073 -- Initialize message list if p_init_msg_list is set to TRUE.
1074 IF FND_API.to_Boolean( p_init_msg_list )
1075 THEN
1076 FND_MSG_PUB.initialize;
1077 END IF;
1078
1079 -- Initialize API return status to SUCCESS
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082
1083
1084 SELECT COUNT(1) INTO l_count FROM AMW_AP_ASSOCIATIONS
1085 WHERE OBJECT_TYPE = 'CTRL_FINCERT'
1086 and PK1 = p_certification_id;
1087
1088 IF (l_count = 0) THEN
1089 FOR ap_rec IN c_finap LOOP
1090 exit when c_finap%notfound;
1091
1092 m_opinion_id := null;
1093 OPEN last_evaluation(ap_rec.audit_procedure_id, ap_rec.pk1, ap_rec.pk3);
1094 FETCH last_evaluation INTO m_opinion_id;
1095 CLOSE last_evaluation;
1096
1097
1098 INSERT INTO AMW_AP_ASSOCIATIONS(
1099 AP_ASSOCIATION_ID,
1100 AUDIT_PROCEDURE_ID,
1101 PK1,
1102 PK2,
1103 PK3,
1104 PK4,
1105 PK5,
1106 CREATED_BY,
1107 CREATION_DATE,
1108 LAST_UPDATE_DATE,
1109 LAST_UPDATED_BY,
1110 LAST_UPDATE_LOGIN,
1111 OBJECT_VERSION_NUMBER,
1112 OBJECT_TYPE,
1113 ASSOCIATION_CREATION_DATE,
1114 APPROVAL_DATE,
1115 DELETION_DATE,
1116 DELETION_APPROVAL_DATE,
1117 AUDIT_PROCEDURE_REV_ID)
1118 VALUES (AMW_AP_ASSOCIATIONS_S.nextval,
1119 ap_rec.audit_procedure_id,
1120 p_certification_id,
1121 ap_rec.PK1,
1122 ap_rec.PK2,
1123 ap_rec.PK3,
1127 SYSDATE,
1124 m_opinion_id,
1125 FND_GLOBAL.USER_ID,
1126 SYSDATE,
1128 FND_GLOBAL.USER_ID,
1129 FND_GLOBAL.USER_ID,
1130 1,
1131 'CTRL_FINCERT',
1132 ap_rec.ASSOCIATION_CREATION_DATE,
1133 ap_rec.APPROVAL_DATE,
1134 ap_rec.DELETION_DATE,
1135 ap_rec.DELETION_APPROVAL_DATE,
1136 ap_rec.AUDIT_PROCEDURE_REV_ID);
1137
1138
1139 END LOOP;
1140 if(p_commit <> FND_API.g_false)
1141 then commit;
1142 end if;
1143 END IF;
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 EXCEPTION
1147 WHEN NO_DATA_FOUND THEN
1148 IF c_finap%ISOPEN THEN
1149 close c_finap;
1150 END IF;
1151 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 x_msg_count := 1;
1154 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
1155 WHEN OTHERS THEN
1156 IF c_finap%ISOPEN THEN
1157 close c_finap;
1158 END IF;
1159 ROLLBACK TO Populate_Fin_AP_Ass_Sum_M;
1160 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
1161 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1163 x_msg_count := 1;
1164 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
1165 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1166 FND_MSG_PUB.Count_And_Get(
1167 p_encoded => FND_API.G_FALSE,
1168 p_count => x_msg_count,
1169 p_data => x_msg_data);
1170 RETURN;
1171
1172 END Populate_Fin_AP_Ass_Sum_M;
1173
1174 FUNCTION Get_Proc_Verified_M
1175 (
1176 P_CERTIFICATION_ID in number,
1177 P_STATEMENT_GROUP_ID in number,
1178 P_FINANCIAL_STATEMENT_ID in number,
1179 P_FINANCIAL_ITEM_ID in number,
1180 P_ACCOUNT_GROUP_ID in number,
1181 P_ACCOUNT_ID in number,
1182 P_OBJECT_TYPE in varchar2) RETURN NUMBER
1183
1184 IS
1185 l_stmt VARCHAR2(2000);
1186 l_stmt1 VARCHAR2(100);
1187 l_stmt2 VARCHAR2(100);
1188 l_sql_stmt VARCHAR2(2000);
1189
1190 X_PROC_VERIFIED Number;
1191 BEGIN
1192
1193 l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
1194 l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
1195
1196 l_stmt := 'SELECT COUNT(1) FROM
1197 (Select distinct fin.PROCESS_ID, fin.ORGANIZATION_ID
1198 FROM
1199 AMW_OPINION_M_V aov,
1200 amw_fin_cert_scope fin
1201 WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
1202 and aov.object_name = ''AMW_ORG_PROCESS''
1203 and aov.opinion_component_code = ''OVERALL''
1204 and aov.PK3_VALUE = fin.ORGANIZATION_ID
1205 and aov.PK1_VALUE = fin.PROCESS_ID
1206 and fin.process_id is not null
1207 and fin.FIN_CERTIFICATION_ID = :1 ';
1208
1209 IF P_OBJECT_TYPE = 'FINANCIAL ITEM' THEN
1210 l_sql_stmt := l_stmt || l_stmt1;
1211
1212 EXECUTE IMMEDIATE l_sql_stmt INTO X_PROC_VERIFIED USING P_CERTIFICATION_ID, P_STATEMENT_GROUP_ID, P_FINANCIAL_STATEMENT_ID, P_FINANCIAL_ITEM_ID ;
1213
1214 ELSIF P_OBJECT_TYPE = 'ACCOUNT' THEN
1215 l_sql_stmt := l_stmt || l_stmt2;
1216 EXECUTE IMMEDIATE l_sql_stmt INTO X_PROC_VERIFIED USING P_CERTIFICATION_ID, P_ACCOUNT_ID ;
1217 END IF;
1218
1219 RETURN X_PROC_VERIFIED;
1220 EXCEPTION
1221 WHEN NO_DATA_FOUND THEN
1222 /*** Record doesn't exist ***/
1223 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.Get_Proc_Verified_M');
1224 RETURN 0;
1225 WHEN OTHERS THEN
1226 /*** Raise any other error ***/
1227 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.Get_Proc_Verified_M');
1228 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1229 RAISE;
1230
1231 END Get_Proc_Verified_M;
1232
1233 FUNCTION Get_ORG_EVALUATED_M
1234 (
1235 P_CERTIFICATION_ID in number,
1236 P_STATEMENT_GROUP_ID in number,
1237 P_FINANCIAL_STATEMENT_ID in number,
1238 P_FINANCIAL_ITEM_ID in number,
1239 P_ACCOUNT_GROUP_ID in number,
1240 P_ACCOUNT_ID in number,
1241 P_OBJECT_TYPE in varchar2) RETURN NUMBER
1242
1243 IS
1244 l_stmt VARCHAR2(2000);
1245 l_stmt1 VARCHAR2(100);
1246 l_stmt2 VARCHAR2(100);
1247 l_sql_stmt VARCHAR2(2000);
1248
1249 X_ORG_EVALUATED Number;
1250
1251 BEGIN
1252
1253 l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
1254 l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
1255
1256 l_stmt := 'select count(1) from (
1257 select distinct fin.ORGANIZATION_ID
1258 FROM
1259 AMW_OPINION_M_V aov,
1260 amw_fin_cert_scope fin
1261 WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
1262 and aov.object_name = ''AMW_ORGANIZATION''
1263 and aov.opinion_component_code = ''OVERALL''
1264 and aov.pk1_value = fin.organization_id
1265 and fin.FIN_CERTIFICATION_ID= :1 ';
1266
1267
1268 IF P_OBJECT_TYPE = 'FINANCIAL ITEM' THEN
1269 l_sql_stmt := l_stmt || l_stmt1;
1270 EXECUTE IMMEDIATE l_sql_stmt INTO X_ORG_EVALUATED USING P_CERTIFICATION_ID, P_STATEMENT_GROUP_ID, P_FINANCIAL_STATEMENT_ID, P_FINANCIAL_ITEM_ID ;
1271 ELSIF P_OBJECT_TYPE = 'ACCOUNT' THEN
1272 l_sql_stmt := l_stmt || l_stmt2;
1273
1274 EXECUTE IMMEDIATE l_sql_stmt INTO X_ORG_EVALUATED USING P_CERTIFICATION_ID, P_ACCOUNT_ID ;
1275 END IF;
1276
1277 RETURN X_ORG_EVALUATED;
1278
1279 EXCEPTION
1280 WHEN NO_DATA_FOUND THEN
1284 WHEN OTHERS THEN
1281 /*** Record doesn't exist ***/
1282 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.Get_ORG_EVALUATED_M');
1283 RETURN 0;
1285 /*** Raise any other error ***/
1286 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.Get_ORG_EVALUATED_M');
1287 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1288 RAISE;
1289
1290 END Get_ORG_EVALUATED_M;
1291
1292 FUNCTION Get_RISKS_VERIFIED_M
1293 (
1294 P_CERTIFICATION_ID in number,
1295 P_STATEMENT_GROUP_ID in number,
1296 P_FINANCIAL_STATEMENT_ID in number,
1297 P_FINANCIAL_ITEM_ID in number,
1298 P_ACCOUNT_GROUP_ID in number,
1299 P_ACCOUNT_ID in number,
1300 P_OBJECT_TYPE in varchar2) RETURN NUMBER
1301
1302 IS
1303 l_stmt VARCHAR2(2000);
1304 l_stmt1 VARCHAR2(100);
1305 l_stmt2 VARCHAR2(100);
1306 l_sql_stmt VARCHAR2(2000);
1307
1308 X_RISKS_VERIFIED Number;
1309 BEGIN
1310
1311 l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
1312 l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
1313
1314 /*********** replace with the following query that uses opinion_log_id directly
1315 l_stmt := 'select count(1) from (
1316 select distinct fin.risk_id ,fin.organization_id, fin.Process_ID
1317 FROM
1318 AMW_OPINION_M_V aov,
1319 amw_fin_item_acc_risk fin
1320 WHERE
1321 aov.OPINION_TYPE_CODE = ''EVALUATION''
1322 and aov.object_name = ''AMW_ORG_PROCESS_RISK''
1323 and aov.opinion_component_code = ''OVERALL''
1324 and aov.pk1_value = fin.risk_id
1325 and aov.pk3_value = fin.organization_id
1326 and aov.pk4_value = fin.process_ID
1327 and fin.object_type = ''' || P_OBJECT_TYPE || '''' || '
1328 and fin.FIN_CERTIFICATION_ID= :1 ';
1329 ************/
1330
1331 l_stmt := 'select count(1) from (
1332 select distinct fin.risk_id ,fin.organization_id, fin.Process_ID
1333 FROM
1334 amw_opinion_m_v aov,
1335 amw_opinions_log aol,
1336 amw_fin_item_acc_risk fin
1337 WHERE
1338 aov.OPINION_TYPE_CODE = ''EVALUATION''
1339 and aov.object_name = ''AMW_ORG_PROCESS_RISK''
1340 and aov.opinion_component_code = ''OVERALL''
1341 and aol.opinion_log_id = fin.opinion_log_id
1342 and aol.opinion_id = aov.opinion_id
1343 and aol.opinion_set_id = aov.opinion_set_id
1344 and fin.object_type = ''' || P_OBJECT_TYPE || '''' || '
1345 and fin.FIN_CERTIFICATION_ID= :1 ';
1346
1347 IF P_OBJECT_TYPE = 'FINANCIAL ITEM' THEN
1348 l_sql_stmt := l_stmt || l_stmt1;
1349
1350 EXECUTE IMMEDIATE l_sql_stmt INTO X_RISKS_VERIFIED USING P_CERTIFICATION_ID, P_STATEMENT_GROUP_ID, P_FINANCIAL_STATEMENT_ID, P_FINANCIAL_ITEM_ID ;
1351
1352
1353 ELSIF P_OBJECT_TYPE = 'ACCOUNT' THEN
1354 l_sql_stmt := l_stmt || l_stmt2;
1355
1356 EXECUTE IMMEDIATE l_sql_stmt INTO X_RISKS_VERIFIED USING P_CERTIFICATION_ID, P_ACCOUNT_ID ;
1357
1358 END IF;
1359 RETURN X_RISKS_VERIFIED;
1360 EXCEPTION
1361 WHEN NO_DATA_FOUND THEN
1362 /*** Record doesn't exist ***/
1363 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.Get_RISKS_VERIFIED_M');
1364 RETURN 0;
1365 WHEN OTHERS THEN
1366 /*** Raise any other error ***/
1367 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.Get_RISKS_VERIFIED_M');
1368 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1369 RAISE;
1370
1371 END Get_RISKS_VERIFIED_M;
1372
1373 FUNCTION Get_CONTROLS_VERIFIED_M
1374 (
1375 P_CERTIFICATION_ID in number,
1376 P_STATEMENT_GROUP_ID in number,
1377 P_FINANCIAL_STATEMENT_ID in number,
1378 P_FINANCIAL_ITEM_ID in number,
1379 P_ACCOUNT_GROUP_ID in number,
1380 P_ACCOUNT_ID in number,
1381 P_OBJECT_TYPE in varchar2) RETURN NUMBER
1382
1383 IS
1384 l_stmt VARCHAR2(2000);
1385 l_stmt1 VARCHAR2(100);
1386 l_stmt2 VARCHAR2(100);
1387 l_sql_stmt VARCHAR2(2000);
1388
1389 X_CONTROLS_VERIFIED Number;
1390
1391
1392 BEGIN
1393
1394 l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
1395 l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
1396
1397 l_stmt := 'select count(1) from(
1398 select distinct fin.control_id, fin.organization_id
1399 FROM
1400 amw_opinion_m_v aov,
1401 amw_opinions_log aol,
1402 amw_fin_item_acc_ctrl fin
1403 WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
1404 and aov.object_name = ''AMW_ORG_CONTROL''
1405 and aov.opinion_component_code = ''OVERALL''
1406 and aol.opinion_log_id = fin.OPINION_LOG_ID
1407 and aol.opinion_id = aov.opinion_id
1408 and aol.opinion_set_id = aov.opinion_set_id
1409 and fin.object_type = ''' || P_OBJECT_TYPE || '''' || '
1410 and fin.fin_certification_id = :1 ';
1411
1412 IF P_OBJECT_TYPE = 'FINANCIAL ITEM' THEN
1413 l_sql_stmt := l_stmt || l_stmt1;
1414
1415 EXECUTE IMMEDIATE l_sql_stmt INTO X_CONTROLS_VERIFIED USING P_CERTIFICATION_ID, P_STATEMENT_GROUP_ID, P_FINANCIAL_STATEMENT_ID, P_FINANCIAL_ITEM_ID ;
1416 --RETURN X_CONTROLS_VERIFIED;
1417
1418 ELSIF P_OBJECT_TYPE = 'ACCOUNT' THEN
1419 l_sql_stmt := l_stmt || l_stmt2;
1420
1421 EXECUTE IMMEDIATE l_sql_stmt INTO X_CONTROLS_VERIFIED USING P_CERTIFICATION_ID, P_ACCOUNT_ID ;
1422 --RETURN X_CONTROLS_VERIFIED;
1423 END IF;
1424 RETURN X_CONTROLS_VERIFIED;
1425 EXCEPTION
1426 WHEN NO_DATA_FOUND THEN
1427 /*** Record doesn't exist ***/
1428 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.Get_CONTROLS_VERIFIED_M');
1429 RETURN 0;
1430 WHEN OTHERS THEN
1431 /*** Raise any other error ***/
1432 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.Get_CONTROLS_VERIFIED_M');
1436
1433 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1434 RAISE;
1435
1437 END Get_CONTROLS_VERIFIED_M;
1438
1439 PROCEDURE INSERT_FIN_RISK(
1440 p_api_version_number IN NUMBER := 1.0,
1441 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1442 p_commit IN VARCHAR2 := FND_API.g_false,
1443 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1444 p_certification_id IN NUMBER,
1445 x_return_status OUT nocopy VARCHAR2,
1446 x_msg_count OUT nocopy NUMBER,
1447 x_msg_data OUT nocopy VARCHAR2
1448 )IS
1449 L_COUNT NUMBER;
1450
1451 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FIN_RISK';
1452 l_api_version_number CONSTANT NUMBER := 1.0;
1453
1454 l_return_status VARCHAR2(32767);
1455 l_msg_count NUMBER;
1456 l_msg_data VARCHAR2(32767);
1457
1458 BEGIN
1459 SAVEPOINT INSERT_FIN_RISK;
1460
1461 -- Standard call to check for call compatibility.
1462 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1463 p_api_version_number,
1464 l_api_name,
1465 G_PKG_NAME)
1466 THEN
1467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1468 END IF;
1469
1470 -- Initialize message list if p_init_msg_list is set to TRUE.
1471 IF FND_API.to_Boolean( p_init_msg_list )
1472 THEN
1473 FND_MSG_PUB.initialize;
1474 END IF;
1475
1476 -- Initialize API return status to SUCCESS
1477 x_return_status := FND_API.G_RET_STS_SUCCESS;
1478
1479 SELECT COUNT(1) INTO L_COUNT FROM AMW_FIN_ITEM_ACC_RISK
1480 WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
1481
1482 IF (L_COUNT = 0 OR L_COUNT IS NULL) THEN
1483
1484 insert into amw_fin_item_acc_risk(
1485 OBJECT_TYPE,
1486 FIN_CERTIFICATION_ID,
1487 STATEMENT_GROUP_ID,
1488 FINANCIAL_STATEMENT_ID,
1489 FINANCIAL_ITEM_ID,
1490 ACCOUNT_GROUP_ID,
1491 NATURAL_ACCOUNT_ID,
1492 ORGANIZATION_ID,
1493 PROCESS_ID,
1494 RISK_ID,
1495 RISK_REV_ID,
1496 OPINION_LOG_ID,
1497 CREATED_BY ,
1498 CREATION_DATE ,
1499 LAST_UPDATED_BY ,
1500 LAST_UPDATE_DATE ,
1501 LAST_UPDATE_LOGIN ,
1502 SECURITY_GROUP_ID ,
1503 OBJECT_VERSION_NUMBER )
1504 SELECT distinct 'ACCOUNT' OBJECT_TYPE , fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
1505 account_group_id, natural_account_id, organization_id, process_id, risk_id, risk_rev_id, pk4 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1506 from amw_fin_cert_scope scp,
1507 amw_risk_associations risk
1508 where risk.pk1 = scp.fin_certification_id
1509 and risk.object_type = 'PROCESS_FINCERT'
1510 and scp.natural_account_id is not null
1511 and scp.organization_id = risk.pk2
1512 and scp.process_id = risk.pk3
1513 and risk.pk1 = p_certification_id
1514 union all
1515 select distinct 'FINANCIAL ITEM' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, financial_item_id,
1516 null account_group_id, null natural_account_id, organization_id, process_id, risk_id, risk_rev_id, pk4 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1517 from amw_fin_cert_scope scp,
1518 amw_risk_associations risk
1519 where risk.pk1 = scp.fin_certification_id
1520 and risk.object_type = 'PROCESS_FINCERT'
1521 and scp.organization_id = risk.pk2
1522 and scp.process_id = risk.pk3
1523 and risk.pk1 = p_certification_id
1524 union all
1525 select distinct 'FINANCIAL STATEMENT' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
1526 null account_group_id, null natural_account_id, organization_id, process_id, risk_id, risk_rev_id, pk4 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1527 from amw_fin_cert_scope scp,
1528 amw_risk_associations risk
1529 where risk.pk1 = scp.fin_certification_id
1530 and risk.object_type = 'PROCESS_FINCERT'
1531 and scp.organization_id = risk.pk2
1532 and scp.process_id = risk.pk3
1533 and risk.pk1 = p_certification_id;
1534
1535 if(p_commit <> FND_API.g_false)
1536 then commit;
1537 end if;
1538
1539 END IF;
1540 x_return_status := FND_API.G_RET_STS_SUCCESS;
1541 EXCEPTION
1542 WHEN NO_DATA_FOUND THEN
1543 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
1544 x_return_status := FND_API.G_RET_STS_ERROR;
1545 x_msg_count := 1;
1546 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
1547 WHEN OTHERS THEN
1548 ROLLBACK TO INSERT_FIN_RISK;
1549 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
1550 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1552 x_msg_count := 1;
1553 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
1554 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1555 FND_MSG_PUB.Count_And_Get(
1556 p_encoded => FND_API.G_FALSE,
1557 p_count => x_msg_count,
1558 p_data => x_msg_data);
1559 RETURN;
1560
1561 END INSERT_FIN_RISK;
1562
1563 PROCEDURE INSERT_FIN_CTRL(
1564 p_api_version_number IN NUMBER := 1.0,
1565 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1566 p_commit IN VARCHAR2 := FND_API.g_false,
1567 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1568 p_certification_id IN NUMBER,
1569 x_return_status OUT nocopy VARCHAR2,
1570 x_msg_count OUT nocopy NUMBER,
1571 x_msg_data OUT nocopy VARCHAR2
1572 ) IS
1573 L_COUNT NUMBER;
1577
1574 l_error_message VARCHAR2(4000);
1575 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FIN_CTRL';
1576 l_api_version_number CONSTANT NUMBER := 1.0;
1578 l_return_status VARCHAR2(32767);
1579 l_msg_count NUMBER;
1580 l_msg_data VARCHAR2(32767);
1581
1582 BEGIN
1583
1584 SAVEPOINT INSERT_FIN_CTRL;
1585
1586 -- Standard call to check for call compatibility.
1587 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1588 p_api_version_number,
1589 l_api_name,
1590 G_PKG_NAME)
1591 THEN
1592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1593 END IF;
1594
1595 -- Initialize message list if p_init_msg_list is set to TRUE.
1596 IF FND_API.to_Boolean( p_init_msg_list )
1597 THEN
1598 FND_MSG_PUB.initialize;
1599 END IF;
1600
1601 -- Initialize API return status to SUCCESS
1602 x_return_status := FND_API.G_RET_STS_SUCCESS;
1603
1604 SELECT COUNT(1) INTO L_COUNT FROM AMW_FIN_ITEM_ACC_CTRL
1605 WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
1606
1607 IF (L_COUNT = 0 OR L_COUNT IS NULL) THEN
1608
1609 insert into amw_fin_item_acc_ctrl
1610 (
1611 OBJECT_TYPE,
1612 FIN_CERTIFICATION_ID,
1613 STATEMENT_GROUP_ID ,
1614 FINANCIAL_STATEMENT_ID,
1615 FINANCIAL_ITEM_ID,
1616 ACCOUNT_GROUP_ID ,
1617 NATURAL_ACCOUNT_ID ,
1618 ORGANIZATION_ID ,
1619 CONTROL_ID ,
1620 CONTROL_REV_ID ,
1621 OPINION_LOG_ID,
1622 CREATED_BY ,
1623 CREATION_DATE ,
1624 LAST_UPDATED_BY ,
1625 LAST_UPDATE_DATE ,
1626 LAST_UPDATE_LOGIN ,
1627 SECURITY_GROUP_ID ,
1628 OBJECT_VERSION_NUMBER )
1629 SELECT distinct 'ACCOUNT' OBJECT_TYPE , fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
1630 account_group_id, natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1631 from amw_fin_cert_scope scp,
1632 amw_control_associations ctrl
1633 where ctrl.pk1 = scp.fin_certification_id
1634 and ctrl.object_type = 'RISK_FINCERT'
1635 and scp.natural_account_id is not null
1636 and scp.organization_id = ctrl.pk2
1637 and scp.process_id = ctrl.pk3
1638 and ctrl.pk1 = p_certification_id
1639 union all
1640 select distinct 'FINANCIAL ITEM' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, financial_item_id,
1641 null account_group_id, null natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1642 from amw_fin_cert_scope scp,
1643 amw_control_associations ctrl
1644 where ctrl.pk1 = scp.fin_certification_id
1645 and ctrl.object_type = 'RISK_FINCERT'
1646 and scp.organization_id = ctrl.pk2
1647 and scp.process_id = ctrl.pk3
1648 and ctrl.pk1 = p_certification_id
1649 union all
1650 select distinct 'FINANCIAL STATEMENT' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
1651 null account_group_id, null natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
1652 from amw_fin_cert_scope scp,
1653 amw_control_associations ctrl
1654 where ctrl.pk1 = scp.fin_certification_id
1655 and ctrl.object_type = 'RISK_FINCERT'
1656 and scp.organization_id = ctrl.pk2
1657 and scp.process_id = ctrl.pk3
1658 and ctrl.pk1 = p_certification_id;
1659
1660 if(p_commit <> FND_API.g_false)
1661 then commit;
1662 end if;
1663
1664 END IF;
1665 x_return_status := FND_API.G_RET_STS_SUCCESS;
1666
1667 EXCEPTION
1668 WHEN NO_DATA_FOUND THEN
1669 fnd_file.put_line(fnd_file.LOG, 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name );
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671 x_msg_count := 1;
1672 x_msg_data := 'NO DATA FOUND IN ' || G_PKG_NAME || '.' || l_api_name ;
1673 WHEN OTHERS THEN
1674 ROLLBACK TO INSERT_FIN_CTRL;
1675 fnd_file.put_line(fnd_file.LOG, 'Unexpected error in ' || G_PKG_NAME || '.' || l_api_name );
1676 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
1677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1678 x_msg_count := 1;
1679 x_msg_data := SUBSTR (SQLERRM, 1, 2000);
1680 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1681 FND_MSG_PUB.Count_And_Get(
1682 p_encoded => FND_API.G_FALSE,
1683 p_count => x_msg_count,
1684 p_data => x_msg_data);
1685 RETURN;
1686 END INSERT_FIN_CTRL;
1687
1688 END AMW_FINSTMT_CERT_MIG_PKG;