[Home] [Help]
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,
1937:
1938: (SELECT temp.STATEMENT_GROUP_ID, temp.FINANCIAL_STATEMENT_ID, temp.FINANCIAL_ITEM_ID,
1939: temp.ACCOUNT_GROUP_ID,
1940: case when temp.NATURAL_ACCOUNT_ID = -1 then temp.child_natural_account_id else temp.NATURAL_ACCOUNT_ID end natural_account_id,
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,
2012:
2013: (SELECT temp.STATEMENT_GROUP_ID, temp.FINANCIAL_STATEMENT_ID, temp.FINANCIAL_ITEM_ID,
2014: temp.ACCOUNT_GROUP_ID,temp.NATURAL_ACCOUNT_ID,ACCREL.PK1 organization_id, ACCREL.PK2 process_id
2015: FROM
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,
2080:
2081: (SELECT temp.STATEMENT_GROUP_ID, temp.FINANCIAL_STATEMENT_ID, temp.FINANCIAL_ITEM_ID,
2082: temp.ACCOUNT_GROUP_ID,
2083: temp.child_natural_account_id natural_account_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
2156: FROM
2157: AMW_FIN_PROCESS_FLAT proc,
2158:
2159: (SELECT temp.ACCOUNT_GROUP_ID,
2160: temp.NATURAL_ACCOUNT_ID,
2161: ACCREL.PK1 organization_id, ACCREL.PK2 process_id
2543:
2544: -- Initialize API return status to SUCCESS
2545: x_return_status := FND_API.G_RET_STS_SUCCESS;
2546:
2547: SELECT COUNT(1) INTO l_count FROM AMW_FIN_PROCESS_FLAT
2548: WHERE PARENT_PROCESS_ID = P_PROCESS_ID
2549: AND ORGANIZATION_ID = P_ORGANIZATION_ID
2550: AND FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
2551:
2552: --process directly associates to the account which belongs to this financial statement
2553: -- to simplify the query, try amw_org_hierarchy_denorm
2554: IF(l_count = 0 or l_count is null) THEN
2555: IF p_account_process_flag = 'Y' THEN
2556: INSERT INTO AMW_FIN_PROCESS_FLAT
2557: (
2558: FIN_CERTIFICATION_ID,
2559: PARENT_PROCESS_ID,
2560: CHILD_PROCESS_ID,
2584: and (up_down_ind = 'D'
2585: or (parent_child_id = -2 and up_down_ind= 'U'));
2586: */
2587: ELSE
2588: INSERT INTO AMW_FIN_PROCESS_FLAT
2589: (
2590: FIN_CERTIFICATION_ID,
2591: PARENT_PROCESS_ID,
2592: CHILD_PROCESS_ID,
2621: -- to be deleted because it's a less efficient solution
2622: -- Note: select p_process_id is very important. it's different from select parent_id
2623: /*
2624: IF p_account_process_flag = 'Y' THEN
2625: INSERT INTO AMW_FIN_PROCESS_FLAT
2626: (CERTIFICATION_ID,
2627: PARENT_PROCESS_ID,
2628: CHILD_PROCESS_ID,
2629: ORGANIZATION_ID)
2636: UNION ALL
2637: SELECT P_CERTIFICATION_ID, P_PROCESS_ID, -1, P_ORGANIZATION_ID FROM DUAL);
2638: -- sub processes of the process which directly links to the account
2639: ELSE
2640: INSERT INTO AMW_FIN_PROCESS_FLAT
2641: (CERTIFICATION_ID,
2642: PARENT_PROCESS_ID,
2643: CHILD_PROCESS_ID,
2644: ORGANIZATION_ID)
3714: ---for later use when multiple process certification is uptaken
3715: /*CURSOR c_child_processes(p_proc_id NUMBER, p_org_id NUMBER) IS
3716: SELECT distinct temp.child_process_id, temp.organization_id, orgproc.revision_number,
3717: orgproc.process_org_rev_id
3718: FROM AMW_FIN_PROCESS_FLAT temp,
3719: AMW_PROCESS_ORGANIZATION orgproc
3720: WHERE temp.parent_process_id = p_proc_id
3721: AND temp.organization_id = p_org_id
3722: AND temp.certification_id = p_certification_id
3730: --------------------------------------------------------*/
3731: /*
3732: CURSOR c_child_processes(p_proc_id NUMBER, p_org_id NUMBER) IS
3733: SELECT distinct proc.child_process_id, proc.organization_id
3734: FROM AMW_FIN_PROCESS_FLAT proc,
3735: WHERE proc.child_process_id <> -2
3736: AND proc.parent_process_id = p_proc_id
3737: AND proc.organization_id = p_org_id
3738: AND proc.fin_certification_id = p_certification_id;
3740:
3741: CURSOR c_child_processes(p_proc_id NUMBER, p_org_id NUMBER) IS
3742: SELECT distinct temp.child_process_id, temp.organization_id, orgproc.revision_number,
3743: orgproc.process_org_rev_id
3744: FROM AMW_FIN_PROCESS_FLAT temp,
3745: AMW_PROCESS_ORGANIZATION orgproc, amw_audit_units_v aauv
3746: WHERE /*02.13.07 npanandi: bug 5043879 fix for
3747: including only those orgs that are active */
3748: orgproc.organization_id = aauv.organization_id
4022: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
4023: where fin_stmt_cert_id = p_certification_id
4024: and end_date is null)
4025: AND aov.pk1_value in (select distinct(orgrel.child_process_id)
4026: from AMW_FIN_PROCESS_FLAT orgrel
4027: where orgrel.parent_process_id = p_process_id
4028: and orgrel.organization_id = p_organization_id
4029: and orgrel.fin_certification_id = p_certification_id
4030: );
4030: );
4031:
4032: CURSOR total_sub_processes IS
4033: SELECT count(distinct child_process_id)
4034: FROM AMW_FIN_PROCESS_FLAT
4035: WHERE parent_process_id = p_process_id
4036: AND organization_id = p_organization_id
4037: AND child_process_id <> -2
4038: AND fin_certification_id = p_certification_id;
4092: CURSOR unmitigated_risks IS
4093: SELECT count(1)
4094: FROM
4095: AMW_RISK_ASSOCIATIONS ara,
4096: AMW_FIN_PROCESS_FLAT orgrel,
4097: AMW_OPINION_MV aov
4098: WHERE ara.object_type = 'PROCESS_ORG'
4099: AND ara.pk1 = p_organization_id
4100: AND orgrel.fin_certification_id = p_certification_id
4124: CURSOR total_risks IS
4125: SELECT count(1)
4126: FROM
4127: AMW_RISK_ASSOCIATIONS ara,
4128: AMW_FIN_PROCESS_FLAT orgrel
4129: WHERE ara.object_type = 'PROCESS_ORG'
4130: AND ara.pk1 = p_organization_id
4131: and orgrel.fin_certification_id = p_certification_id
4132: and orgrel.organization_id = ara.pk1
4143: from (select distinct ara.pk1, ara.pk2, ara.risk_id
4144: FROM
4145: AMW_RISK_ASSOCIATIONS ara,
4146: AMW_OPINION_MV aov,
4147: AMW_FIN_PROCESS_FLAT orgrel
4148: WHERE ara.object_type = 'PROCESS_ORG'
4149: AND ara.pk1 = p_organization_id
4150: AND orgrel.fin_certification_id = p_certification_id
4151: and orgrel.organization_id = ara.pk1
4167: CURSOR ineffective_controls IS
4168: SELECT count(distinct aca.control_id)
4169: FROM
4170: AMW_CONTROL_ASSOCIATIONS aca,
4171: AMW_FIN_PROCESS_FLAT orgrel,
4172: AMW_OPINION_MV aov
4173: WHERE
4174: aca.object_type = 'RISK_ORG'
4175: and aca.pk1 = p_organization_id
4198: CURSOR total_controls IS
4199: SELECT count(distinct aca.control_id)
4200: FROM
4201: AMW_CONTROL_ASSOCIATIONS aca,
4202: AMW_FIN_PROCESS_FLAT orgrel
4203: WHERE aca.object_type = 'RISK_ORG'
4204: and aca.pk1 = p_organization_id
4205: and orgrel.fin_certification_id = p_certification_id
4206: and aca.pk1 = orgrel.organization_id
4213: CURSOR verified_controls IS
4214: SELECT count(distinct aca.control_id)
4215: FROM
4216: AMW_CONTROL_ASSOCIATIONS aca,
4217: AMW_FIN_PROCESS_FLAT orgrel,
4218: AMW_OPINION_MV aov
4219: WHERE
4220: aca.object_type = 'RISK_ORG'
4221: and aca.pk1 = p_organization_id
8657:
8658: --CURSOR TO GET ALL OF FINCERCIAL CERTIFICATION WHICH THIS PROCESS AS SUB-PROCESS
8659: CURSOR Get_all_fin_process(l_org_id number, l_process_id number) IS
8660: SELECT proc.FIN_CERTIFICATION_ID, proc.PARENT_PROCESS_ID
8661: FROM AMW_FIN_PROCESS_FLAT proc,
8662: AMW_CERTIFICATION_B cert
8663: WHERE proc.ORGANIZATION_ID = l_org_id
8664: AND proc.CHILD_PROCESS_ID = l_process_id
8665: AND cert.CERTIFICATION_ID = proc.FIN_CERTIFICATION_ID
8735: FROM amw_fin_process_eval_sum fin
8736: WHERE fin.organization_id = l_org_id
8737: AND fin.process_id in (
8738: SELECT proc.parent_process_id
8739: FROM amw_fin_process_flat proc
8740: WHERE proc.fin_certification_id = fin.fin_certification_id
8741: AND proc.organization_id = l_org_id
8742: AND proc.child_process_id = l_process_id)
8743: AND fin.fin_certification_id in (select rel.fin_stmt_cert_id from amw_fin_proc_cert_relan rel, amw_opinions_log opin