[Home] [Help]
557: end if;
558:
559: -- if the parent is the root, then it doesn't get revised. This means if you
560: -- add to the root, or delete from the root, the information must be transferred
561: -- to the amw_approved_hierarchies table.
562: if p_parent_process_id = -2 then
563: if p_action = 'ADD' then
564: if( l_log_stmt_level >= l_curr_log_level ) then
565: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.write_approved_hierarchy',
1671: p_apply_rcm in varchar2) is
1672:
1673: cursor c1 (l_pid number) is
1674: select ah.child_id child_process_id
1675: from amw_approved_hierarchies ah
1676: where ah.parent_id = (select pp.process_id
1677: from amw_process pp
1678: where pp.process_id = ah.parent_id
1679: and pp.approval_date is not null
1845: ) is
1846:
1847: cursor c1 (l_pid number) is
1848: select ah.child_id child_process_id
1849: from amw_approved_hierarchies ah
1850: where ah.parent_id = (select pp.process_id
1851: from amw_process pp
1852: where pp.process_id = ah.parent_id
1853: and pp.approval_date is not null
2037: ) is
2038:
2039: cursor c1 (l_pid number) is
2040: select ah.child_id child_process_id
2041: from amw_approved_hierarchies ah
2042: where ah.parent_id = (select pp.process_id
2043: from amw_process pp
2044: where pp.process_id = ah.parent_id
2045: and pp.approval_date is not null
2246: procedure find_rl_app_hier_children(p_process_id in number)
2247: is
2248: cursor c1 (l_pid number) is
2249: select ah.child_id child_process_id
2250: from amw_approved_hierarchies ah
2251: where ah.parent_id = (select pp.process_id
2252: from amw_process pp
2253: where pp.process_id = ah.parent_id
2254: and pp.approval_date is not null
2662: select process_id
2663: from amw_process_organization
2664: where organization_id = p_org_id
2665: and process_id in ( select parent_id
2666: from amw_approved_hierarchies
2667: start with child_id = p_process_id and organization_id = p_org_id
2668: and start_date is not null and end_date is null
2669: connect by prior parent_id = child_id and organization_id = p_org_id
2670: and start_date is not null and end_date is null
2715: set risk_count = (select count(*) from (
2716: select distinct risk_id from amw_risk_associations
2717: where pk1 = p_org_id
2718: and pk2 in ( select child_id
2719: from amw_approved_hierarchies
2720: start with child_id = x(ctr) and organization_id = p_org_id
2721: and start_date is not null and end_date is null
2722: connect by prior child_id = parent_id and organization_id = p_org_id
2723: and start_date is not null and end_date is null
2757: select process_id
2758: from amw_process_organization
2759: where organization_id = p_org_id
2760: and process_id in ( select parent_id
2761: from amw_approved_hierarchies
2762: start with child_id = p_process_id and organization_id = p_org_id
2763: and start_date is not null and end_date is null
2764: connect by prior parent_id = child_id and organization_id = p_org_id
2765: and start_date is not null and end_date is null
2811: set control_count= (select count(*) from (
2812: select distinct control_id from amw_control_associations
2813: where pk1 = p_org_id
2814: and pk2 in ( select child_id
2815: from amw_approved_hierarchies
2816: start with child_id = x(ctr) and organization_id = p_org_id
2817: and start_date is not null and end_date is null
2818: connect by prior child_id = parent_id and organization_id = p_org_id
2819: and start_date is not null and end_date is null
3169: set APO.risk_count = (
3170: select count(distinct ARA.risk_id) from amw_risk_associations ARA
3171: where ARA.pk1 = APO.ORGANIZATION_ID
3172: and ARA.pk2 in ( select alh.child_id
3173: from amw_approved_hierarchies alh
3174: start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
3175: and alh.start_date is not null and alh.end_date is null
3176: connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
3177: and alh.start_date is not null and alh.end_date is null
3193: APO.control_count = (
3194: select count(distinct ACA.CONTROL_ID) from amw_control_associations ACA
3195: where ACA.pk1 = APO.ORGANIZATION_ID
3196: and ACA.pk2 in ( select alh.child_id
3197: from amw_approved_hierarchies alh
3198: start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
3199: and alh.start_date is not null and alh.end_date is null
3200: connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
3201: and alh.start_date is not null and alh.end_date is null
3230: set APO.risk_count = (
3231: select count(distinct ARA.risk_id) from amw_risk_associations ARA
3232: where ARA.pk1 = APO.ORGANIZATION_ID
3233: and ARA.pk2 in ( select alh.child_id
3234: from amw_approved_hierarchies alh
3235: start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
3236: and alh.start_date is not null and alh.end_date is null
3237: connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
3238: and alh.start_date is not null and alh.end_date is null
3254: APO.control_count = (
3255: select count(distinct ACA.CONTROL_ID) from amw_control_associations ACA
3256: where ACA.pk1 = APO.ORGANIZATION_ID
3257: and ACA.pk2 in ( select alh.child_id
3258: from amw_approved_hierarchies alh
3259: start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
3260: and alh.start_date is not null and alh.end_date is null
3261: connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
3262: and alh.start_date is not null and alh.end_date is null
3653: set risk_count = (select count(*) from (
3654: select distinct risk_id from amw_risk_associations
3655: where pk1 = l_orgs_tbl(ctr)
3656: and pk2 in ( select alh.child_id
3657: from amw_approved_hierarchies alh
3658: start with alh.child_id = l_procs_tbl(ctr) and alh.organization_id = l_orgs_tbl(ctr)
3659: and alh.start_date is not null and alh.end_date is null
3660: connect by prior alh.child_id = alh.parent_id and alh.organization_id = l_orgs_tbl(ctr)
3661: and alh.start_date is not null and alh.end_date is null
3677: control_count= (select count(*) from (
3678: select distinct control_id from amw_control_associations
3679: where pk1 = l_orgs_tbl(ctr)
3680: and pk2 in ( select alh.child_id
3681: from amw_approved_hierarchies alh
3682: start with alh.child_id = l_procs_tbl(ctr) and alh.organization_id = l_orgs_tbl(ctr)
3683: and alh.start_date is not null and alh.end_date is null
3684: connect by prior alh.child_id = alh.parent_id and alh.organization_id = l_orgs_tbl(ctr)
3685: and alh.start_date is not null and alh.end_date is null
4642:
4643: begin
4644: select child_id
4645: into l_dummy
4646: from amw_approved_hierarchies
4647: where parent_id = p_process_id
4648: and organization_id = p_organization_id
4649: and (end_date is null or end_date > sysdate)
4650: and child_id not in
4661: where parent_id = p_process_id
4662: and organization_id = p_organization_id
4663: and child_id not in
4664: (select child_id
4665: from amw_approved_hierarchies
4666: where parent_id = p_process_id
4667: and organization_id = p_organization_id
4668: and (end_date is null or end_date > sysdate));
4669: exception
7496: ) is
7497:
7498: cursor c1 (l_pid number) is
7499: select ah.child_id child_process_id
7500: from amw_approved_hierarchies ah
7501: where ah.parent_id = (select pp.process_id
7502: from amw_process pp
7503: where pp.process_id = ah.parent_id
7504: and pp.approval_date is not null
7722: ) is
7723:
7724: cursor c1 (l_pid number) is
7725: select ah.child_id child_process_id
7726: from amw_approved_hierarchies ah
7727: where ah.parent_id = (select pp.process_id
7728: from amw_process pp
7729: where pp.process_id = ah.parent_id
7730: and pp.approval_date is not null