[Home] [Help]
185: l_stmt1 := ' AND fin.STATEMENT_GROUP_ID = :2 AND fin.FINANCIAL_STATEMENT_ID = :3 AND fin.FINANCIAL_ITEM_ID = :4)';
186: l_stmt2 := ' AND fin.NATURAL_ACCOUNT_ID = :2)';
187:
188: l_stmt := ' select count(1) from (SELECT DISTINCT ORGANIZATION_ID, PROCESS_ID
189: FROM amw_fin_cert_scope fin WHERE FIN_CERTIFICATION_ID= :1 AND PROCESS_ID IS NOT NULL';
190:
191: IF P_OBJECT_TYPE = 'FINANCIAL ITEM' THEN
192: l_sql_stmt := l_stmt || l_stmt1;
193:
240: l_stmt := 'select count(1) from (
241: Select distinct fin.PROCESS_ID, fin.ORGANIZATION_ID
242: FROM
243: AMW_OPINION_MV aov,
244: amw_fin_cert_scope fin,
245: AMW_FIN_PROC_CERT_RELAN rel
246: WHERE
247: rel.FIN_STMT_CERT_ID = ' || P_CERTIFICATION_ID || '
248: and fin.FIN_CERTIFICATION_ID = :1
314: l_stmt := 'SELECT COUNT(1) FROM
315: (Select distinct fin.PROCESS_ID, fin.ORGANIZATION_ID
316: FROM
317: AMW_OPINION_MV aov,
318: amw_fin_cert_scope fin
319: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
320: and aov.object_name = ''AMW_ORG_PROCESS''
321: and aov.opinion_component_code = ''OVERALL''
322: and aov.PK3_VALUE = fin.ORGANIZATION_ID
375: l_stmt := 'select count(1) from (
376: select distinct fin.ORGANIZATION_ID
377: FROM
378: AMW_OPINION_MV aov,
379: amw_fin_cert_scope fin
380: WHERE
381: aov.AUTHORED_DATE in (select max(aov2.authored_date)
382: from AMW_OPINIONS aov2
383: where aov2.object_opinion_type_id = aov.object_opinion_type_id
441: l_stmt := 'select count(1) from (
442: select distinct fin.ORGANIZATION_ID
443: FROM
444: AMW_OPINION_MV aov,
445: amw_fin_cert_scope fin
446: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
447: and aov.object_name = ''AMW_ORGANIZATION''
448: and aov.opinion_component_code = ''OVERALL''
449: and aov.pk1_value = fin.organization_id
501: l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
502:
503: l_stmt := 'select COUNT(1)
504: from (select distinct fin.organization_id
505: from amw_fin_cert_scope fin
506: where
507: fin.organization_id is not null
508: and fin.FIN_CERTIFICATION_ID= :1 ';
509:
557: l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
558:
559: l_stmt := 'select count(1) from ( select distinct fin.organization_id FROM
560: AMW_OPINION_MV aov,
561: AMW_FIN_CERT_SCOPE fin
562: WHERE
563: aov.AUTHORED_DATE in (select max(aov2.authored_date)
564: from AMW_OPINIONS aov2
565: where aov2.object_opinion_type_id = aov.object_opinion_type_id
623: l_stmt := 'select count(1) from (
624: Select distinct fin.process_id, fin.ORGANIZATION_ID
625: FROM
626: AMW_OPINION_MV aov,
627: amw_fin_cert_scope fin
628: WHERE
629: aov.AUTHORED_DATE in (select max(aov2.authored_date)
630: from AMW_OPINIONS aov2
631: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1200: l_stmt := 'select count(1) from (
1201: Select distinct fin.process_ID, fin.ORGANIZATION_ID
1202: FROM
1203: AMW_OPINION_MV aov,
1204: amw_fin_cert_scope fin,
1205: amw_fin_proc_cert_relan rel
1206: WHERE
1207: rel.FIN_STMT_CERT_ID = ' || P_CERTIFICATION_ID || '
1208: and fin.FIN_CERTIFICATION_ID= :1
1537: )IS
1538: BEGIN
1539: -----orgs_pending_in_scope IS
1540: SELECT count(distinct fin.organization_id) INTO x_orgs_pending_in_scope
1541: FROM AMW_FIN_CERT_SCOPE fin
1542: WHERE fin.FIN_CERTIFICATION_ID = p_certification_id
1543: AND fin.organization_id is not null
1544: AND not exists ( SELECT 'Y'
1545: FROM AMW_OPINION_MV aov
1895:
1896: -- Initialize API return status to SUCCESS
1897: x_return_status := FND_API.G_RET_STS_SUCCESS;
1898:
1899: SELECT COUNT(1) INTO L_COUNT FROM AMW_FIN_CERT_SCOPE
1900: WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
1901:
1902: /** even if there is no process attached to an account. if the account belongs to the financial
1903: ** certification, we should add it to the amw_fin_cert_scope table
1899: SELECT COUNT(1) INTO L_COUNT FROM AMW_FIN_CERT_SCOPE
1900: WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
1901:
1902: /** even if there is no process attached to an account. if the account belongs to the financial
1903: ** certification, we should add it to the amw_fin_cert_scope table
1904: SELECT COUNT(1) INTO L_COUNT2 FROM AMW_FIN_PROCESS_EVAL_SUM
1905: WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
1906:
1907:
1911: ****/
1912:
1913: IF (L_COUNT = 0 OR L_COUNT IS NULL) THEN
1914: ----add those accounts that have one or more children linking to a process
1915: insert into amw_fin_cert_scope(
1916: FIN_CERT_SCOPE_ID ,
1917: FIN_CERTIFICATION_ID ,
1918: STATEMENT_GROUP_ID ,
1919: FINANCIAL_STATEMENT_ID,
1928: LAST_UPDATE_DATE ,
1929: LAST_UPDATE_LOGIN ,
1930: SECURITY_GROUP_ID ,
1931: OBJECT_VERSION_NUMBER )
1932: SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, itemaccmerge.statement_group_id, itemaccmerge.financial_statement_id, itemaccmerge.financial_item_id,
1933: 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,
1934: 1, sysdate, 1, sysdate, 1, null, 1
1935: FROM
1936: AMW_FIN_PROCESS_FLAT proc,
1986: and proc.parent_process_id (+) = itemaccmerge.process_id
1987: and proc.fin_certification_id (+) = p_certification_id;
1988:
1989: -- add account which has link to a item and also directly assocates with a process
1990: insert into amw_fin_cert_scope(
1991: FIN_CERT_SCOPE_ID ,
1992: FIN_CERTIFICATION_ID ,
1993: STATEMENT_GROUP_ID,
1994: FINANCIAL_STATEMENT_ID ,
2003: LAST_UPDATE_DATE ,
2004: LAST_UPDATE_LOGIN ,
2005: SECURITY_GROUP_ID ,
2006: OBJECT_VERSION_NUMBER )
2007: SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, itemaccmerge.statement_group_id, itemaccmerge.financial_statement_id, itemaccmerge.financial_item_id,
2008: 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,
2009: 1, sysdate, 1, sysdate, 1, null, 1
2010: FROM
2011: AMW_FIN_PROCESS_FLAT proc,
2054:
2055: --- add all of childen accounts which associate with the top account which directly links to an item
2056: --- e.g A2 is a child of A1. A1 links to an financial item which relates to fin certification
2057: -- and P1 is associated with A2. so we want to add one record which contains A2, P1 info. in scope table
2058: insert into amw_fin_cert_scope(
2059: FIN_CERT_SCOPE_ID ,
2060: FIN_CERTIFICATION_ID ,
2061: STATEMENT_GROUP_ID ,
2062: FINANCIAL_STATEMENT_ID ,
2071: LAST_UPDATE_DATE ,
2072: LAST_UPDATE_LOGIN ,
2073: SECURITY_GROUP_ID ,
2074: OBJECT_VERSION_NUMBER )
2075: SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, itemaccmerge.statement_group_id, itemaccmerge.financial_statement_id, itemaccmerge.financial_item_id,
2076: 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,
2077: 1, sysdate, 1, sysdate, 1, null, 1
2078: FROM
2079: AMW_FIN_PROCESS_FLAT proc,
2130:
2131:
2132: -- account has sub-account, but account itself doesn't associate with any item. His parent/parent's parent links to -- -- item.
2133: --- and its sub-account links to a process. e.g A1-A2-A3, A3-P1. so this query make A2-P
2134: insert into amw_fin_cert_scope(
2135: FIN_CERT_SCOPE_ID ,
2136: FIN_CERTIFICATION_ID ,
2137: STATEMENT_GROUP_ID ,
2138: FINANCIAL_STATEMENT_ID ,
2147: LAST_UPDATE_DATE ,
2148: LAST_UPDATE_LOGIN ,
2149: SECURITY_GROUP_ID ,
2150: OBJECT_VERSION_NUMBER )
2151: SELECT AMW_FIN_CERT_SCOPE_S.NEXTVAL, P_CERTIFICATION_ID, null statement_group_id, null financial_statement_id,
2152: null financial_item_id,
2153: itemaccmerge.account_group_id, itemaccmerge.natural_account_id,itemaccmerge.organization_id,
2154: case when proc.child_process_id = -2 then itemaccmerge.process_id else proc.child_process_id end process_id,
2155: 1, sysdate, 1, sysdate, 1, null, 1
2171: (select flat.account_group_id, flat.parent_natural_account_id, flat.child_natural_account_id
2172: from AMW_FIN_KEY_ACCT_FLAT flat
2173: start with (account_group_id, parent_natural_account_id) in
2174: (select account_group_id, natural_account_id
2175: from amw_fin_cert_scope
2176: where fin_certification_id = P_CERTIFICATION_ID)
2177: connect by parent_natural_account_id = prior child_natural_account_id
2178: and account_group_id = prior account_group_id) flat
2179: where not exists (
2177: connect by parent_natural_account_id = prior child_natural_account_id
2178: and account_group_id = prior account_group_id) flat
2179: where not exists (
2180: select 'Y'
2181: from AMW_FIN_CERT_SCOPE temp2
2182: where flat.account_group_id = temp2.account_group_id
2183: and flat.parent_natural_account_id = temp2.natural_account_id
2184: and temp2.fin_certification_id = P_CERTIFICATION_ID) ) temp
2185: WHERE
2282: SECURITY_GROUP_ID ,
2283: OBJECT_VERSION_NUMBER )
2284: SELECT distinct 'ACCOUNT' OBJECT_TYPE , fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
2285: account_group_id, natural_account_id, organization_id, control_id, control_rev_id, pk5 opinion_log_id, 1, sysdate, 1, sysdate, 1, null, 1
2286: from amw_fin_cert_scope scp,
2287: amw_control_associations ctrl
2288: where ctrl.pk1 = scp.fin_certification_id
2289: and ctrl.object_type = 'RISK_FINCERT'
2290: and scp.natural_account_id is not null
2293: and ctrl.pk1 = p_certification_id
2294: union all
2295: select distinct 'FINANCIAL ITEM' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, financial_item_id,
2296: 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
2297: from amw_fin_cert_scope scp,
2298: amw_control_associations ctrl
2299: where ctrl.pk1 = scp.fin_certification_id
2300: and ctrl.object_type = 'RISK_FINCERT'
2301: and scp.organization_id = ctrl.pk2
2303: and ctrl.pk1 = p_certification_id
2304: union all
2305: select distinct 'FINANCIAL STATEMENT' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
2306: 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
2307: from amw_fin_cert_scope scp,
2308: amw_control_associations ctrl
2309: where ctrl.pk1 = scp.fin_certification_id
2310: and ctrl.object_type = 'RISK_FINCERT'
2311: and scp.organization_id = ctrl.pk2
2406: SECURITY_GROUP_ID ,
2407: OBJECT_VERSION_NUMBER )
2408: SELECT distinct 'ACCOUNT' OBJECT_TYPE , fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
2409: 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
2410: from amw_fin_cert_scope scp,
2411: amw_risk_associations risk
2412: where risk.pk1 = scp.fin_certification_id
2413: and risk.object_type = 'PROCESS_FINCERT'
2414: and scp.natural_account_id is not null
2417: and risk.pk1 = p_certification_id
2418: union all
2419: select distinct 'FINANCIAL ITEM' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, financial_item_id,
2420: 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
2421: from amw_fin_cert_scope scp,
2422: amw_risk_associations risk
2423: where risk.pk1 = scp.fin_certification_id
2424: and risk.object_type = 'PROCESS_FINCERT'
2425: and scp.organization_id = risk.pk2
2427: and risk.pk1 = p_certification_id
2428: union all
2429: select distinct 'FINANCIAL STATEMENT' OBJECT_TYPE, fin_certification_id, statement_group_id, financial_statement_id, null financial_item_id,
2430: 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
2431: from amw_fin_cert_scope scp,
2432: amw_risk_associations risk
2433: where risk.pk1 = scp.fin_certification_id
2434: and risk.object_type = 'PROCESS_FINCERT'
2435: and scp.organization_id = risk.pk2
3410: x_return_status => l_return_status,
3411: x_msg_count => l_msg_count,
3412: x_msg_data => l_msg_data);
3413: l_end_time := sysdate;
3414: fnd_file.put_line(fnd_file.LOG, 'finish populating amw_fin_cert_scope table:p_certification_id = ' || p_certification_id || ' elapsed time is ' || (l_end_time-l_start_time)*24*60*60);
3415:
3416: INSERT_FIN_RISK
3417: (p_certification_id => p_certification_id,
3418: x_return_status => l_return_status,
4295: FROM AMW_FIN_PROCESS_EVAL_SUM sum
4296: WHERE sum.fin_certification_id = p_certification_id
4297: AND sum.organization_id = p_organization_id
4298: AND sum.process_id = p_process_id
4299: AND EXISTS ( SELECT 'Y' FROM AMW_FIN_CERT_SCOPE scp
4300: WHERE scp.fin_certification_id = sum.fin_certification_id
4301: and scp.organization_id = sum.organization_id
4302: and scp.process_id = sum.process_id
4303: and scp.process_id is not null);
4302: and scp.process_id = sum.process_id
4303: and scp.process_id is not null);
4304:
4305:
4306: SELECT COUNT(1) INTO l_new FROM AMW_FIN_CERT_SCOPE
4307: WHERE FIN_CERTIFICATION_ID = p_certification_id;
4308:
4309: IF( l_count = 0 and l_new <> 0 ) THEN RETURN;
4310: END IF;
6225:
6226: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS FINANCIAL CERTIFICATION
6227: CURSOR Get_all_items IS
6228: SELECT DISTINCT ITEM.STATEMENT_GROUP_ID, ITEM.FINANCIAL_STATEMENT_ID, ITEM.FINANCIAL_ITEM_ID
6229: FROM AMW_FIN_CERT_SCOPE ITEM
6230: WHERE ITEM.FIN_CERTIFICATION_ID = P_CERTIFICATION_ID
6231: AND ITEM.FINANCIAL_ITEM_ID IS NOT NULL;
6232:
6233: --CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS FINANCIAL CERTIFICATION
6232:
6233: --CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS FINANCIAL CERTIFICATION
6234: CURSOR Get_all_accts IS
6235: SELECT DISTINCT finitemAcc.statement_group_id, finitemAcc.financial_statement_id, finitemAcc.financial_item_id, finitemAcc.account_group_id, finitemAcc.natural_account_id
6236: FROM AMW_FIN_CERT_SCOPE finitemAcc
6237: WHERE finitemAcc.FIN_CERTIFICATION_ID = P_CERTIFICATION_ID
6238: AND finitemAcc.natural_account_id is not null;
6239:
6240: ---CURSOR TO GET ALL OF CERTIFICATION WITH 'DRAFT', 'ACTIVE' STATUS
6962:
6963:
6964: CURSOR orgs_in_scope IS
6965: SELECT count(distinct fin.organization_id)
6966: FROM AMW_FIN_CERT_SCOPE fin
6967: where fin.FIN_CERTIFICATION_ID= p_certification_id;
6968: --------------------------------------------------------------------------
6969:
6970: l_new_risks_added NUMBER;
8623:
8624: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS PROCESS
8625: CURSOR Get_all_items( l_org_id number, l_process_id number)IS
8626: SELECT DISTINCT fin.FIN_CERTIFICATION_ID, fin.STATEMENT_GROUP_ID, fin.FINANCIAL_STATEMENT_ID, fin.FINANCIAL_ITEM_ID
8627: FROM AMW_FIN_CERT_SCOPE fin,
8628: AMW_CERTIFICATION_B cert
8629: WHERE fin.ORGANIZATION_ID = l_org_id
8630: AND fin.PROCESS_ID = l_process_id
8631: AND fin.FINANCIAL_ITEM_ID IS NOT NULL
8634:
8635: ---CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS PROCESS
8636: CURSOR Get_all_accts (l_org_id number, l_process_id number)IS
8637: SELECT DISTINCT fin.fin_certification_id, fin.account_group_id, fin.natural_account_id
8638: FROM AMW_FIN_CERT_SCOPE fin,
8639: AMW_CERTIFICATION_B cert
8640: WHERE fin.ORGANIZATION_ID = l_org_id
8641: AND fin.PROCESS_ID = l_process_id
8642: AND fin.natural_account_id is not null
9622: IS
9623: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS ORGANIZATION
9624: CURSOR Get_all_items( l_org_id number)IS
9625: SELECT DISTINCT fin.FIN_CERTIFICATION_ID, fin.STATEMENT_GROUP_ID, fin.FINANCIAL_STATEMENT_ID, fin.FINANCIAL_ITEM_ID
9626: FROM AMW_FIN_CERT_SCOPE fin,
9627: AMW_CERTIFICATION_B cert
9628: WHERE fin.ORGANIZATION_ID = l_org_id
9629: AND fin.FINANCIAL_ITEM_ID IS NOT NULL
9630: AND fin.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
9632:
9633: ---CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS ORGANIZATION
9634: CURSOR Get_all_accts (l_org_id number)IS
9635: SELECT DISTINCT fin.fin_certification_id, fin.account_group_id, fin.natural_account_id
9636: FROM AMW_FIN_CERT_SCOPE fin,
9637: AMW_CERTIFICATION_B cert
9638: WHERE fin.ORGANIZATION_ID = l_org_id
9639: AND fin.natural_account_id is not null
9640: AND fin.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
10650: l_stmt := 'SELECT COUNT(1) FROM
10651: (Select distinct fin.PROCESS_ID, fin.ORGANIZATION_ID
10652: FROM
10653: AMW_OPINION_M_V aov,
10654: amw_fin_cert_scope fin
10655: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10656: and aov.object_name = ''AMW_ORG_PROCESS''
10657: and aov.opinion_component_code = ''OVERALL''
10658: and aov.PK3_VALUE = fin.ORGANIZATION_ID
10712: l_stmt := 'select count(1) from (
10713: select distinct fin.ORGANIZATION_ID
10714: FROM
10715: AMW_OPINION_M_V aov,
10716: amw_fin_cert_scope fin
10717: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10718: and aov.object_name = ''AMW_ORGANIZATION''
10719: and aov.opinion_component_code = ''OVERALL''
10720: and aov.pk1_value = fin.organization_id