[Home] [Help]
60:
61: /* Code added for bug#2645200 */
62:
63: select parent_level into l_org_level
64: from pa_org_hierarchy_denorm
65: where org_hierarchy_version_id = p_org_version_id
66: and child_organization_id = p_child_parent_org_id
67: and parent_organization_id = p_child_parent_org_id
68: and pa_org_use_type = 'TP_SCHEDULE';
333:
334: --- Delete all rows from pa_cc_tp_schedule_line_lkp
335: delete from pa_cc_tp_schedule_line_lkp;
336:
337: --- changes for pa_org_hierarchy_denorm table
338: if (nvl(x_old_proj_start_org_id,x_new_proj_start_org_id +1) <> x_new_proj_start_org_id ) then
339: ---- start org is changed, update only if org is moved up
340:
341: -- Bug 3795903- Modified the call to check_org_exists
441: );
442:
443: end if;
444:
445: --- changes for pa_org_hierarchy_denorm table
446: if (nvl(x_old_exp_start_org_id,x_new_exp_start_org_id +1) <> x_new_exp_start_org_id ) then
447: ---- start org is changed, update only if org is moved up
448:
449: -- Bug 3795903- Modified the call to check_org_exists
504: or
505: (nvl(x_old_start_organization_id,x_new_start_organization_id ) <>
506: x_new_start_organization_id ) ) then
507: -- call procedure populate hierarchy denorm to insert rows into
508: -- into pa_org_hierarchy_denorm for reporting type of organizations
509:
510: /* Bug 2976953 - Changed populate_hierarchy_denorm to populate_hierarchy_denorm2 and also added
511: the parameter p_org_id for that call */
512:
620: CURSOR check_org_ver_not_exists ( lp_org_version_id IN NUMBER ) IS
621: SELECT 'x'
622: FROM dual
623: WHERE not exists
624: ( SELECT 'x' from pa_org_hierarchy_denorm
625: WHERE org_hierarchy_version_id = nvl(lp_org_version_id,org_hierarchy_version_id)
626: and pa_org_use_type = 'TP_SCHEDULE' );
627:
628: */
664:
665: /* Start of code Added for bug#2723330 */
666:
667: BEGIN
668: DELETE from pa_org_hierarchy_denorm
669: WHERE org_hierarchy_version_id = p_org_version_id
670: AND pa_org_use_type = 'TP_SCHEDULE';
671: EXCEPTION
672: WHEN others THEN
795: (select x_organization_id_parent
796: from sys.dual );
797: */
798:
799: --- To populate pa_org_hierarchy_denorm
800: cursor get_all_orgs is
801: (SELECT x_organization_id_parent
802: FROM dual
803: UNION
921:
922: --- Delete all rows from pa_cc_tp_schedule_line_lkp
923: delete from pa_cc_tp_schedule_line_lkp;
924:
925: -------- for pa_org_hierarchy_denorm
926: open get_all_orgs;
927: loop
928: fetch get_all_orgs into l_parent_org_id;
929: exit when get_all_orgs%notfound;
1030: and info.org_information2 = 'Y');
1031:
1032: end if ;
1033:
1034: -------- for pa_org_hierarchy_denorm
1035: open get_all_orgs;
1036: loop
1037: fetch get_all_orgs into l_parent_org_id;
1038: exit when get_all_orgs%notfound;
1117: end if;
1118:
1119: /*End bug# 2247737*/
1120:
1121: /* Bug#2643047 - Call to populate reporting orgs in pa_org_hierarchy_denorm table */
1122:
1123: pa_org_utils.populate_reporting_orgs(x_org_version_id ,
1124: x_organization_id_child ,
1125: x_organization_id_parent,
1141: x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1142: x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1143: CURSOR c_get_all_orgs IS
1144: SELECT child_organization_id
1145: from pa_org_hierarchy_denorm
1146: where org_hierarchy_version_id = x_org_version_id
1147: and parent_organization_id = x_organization_id_child;
1148:
1149: l_parent_org_id number;
1187: open c_get_all_orgs;
1188: loop
1189: fetch c_get_all_orgs into l_parent_org_id;
1190: exit when c_get_all_orgs%notfound;
1191: delete from pa_org_hierarchy_denorm
1192: where org_hierarchy_version_id = x_org_version_id
1193: and (child_organization_id = l_parent_org_id
1194: or parent_organization_id = l_parent_org_id);
1195: end loop;
1194: or parent_organization_id = l_parent_org_id);
1195: end loop;
1196: close c_get_all_orgs;
1197:
1198: /* Bug#2643047 - call to restructure levels from pa_org_hierarchy_denorm table*/
1199:
1200: pa_org_utils.restructure_rpt_orgs_denorm(
1201: x_org_version_id,
1202: x_err_code ,
1269: v_start_org_id
1270: and org_structure_version_id = v_org_structure_version_id );
1271: */
1272:
1273: --- To populate pa_org_hierarchy_denorm
1274: cursor get_all_orgs is
1275: (SELECT x_organization_id
1276: FROM dual
1277: UNION ALL
1397: and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1398: and pa_org_use_type = 'PROJECTS'
1399: and inactive_date is null; -- Bug Ref # 6367868
1400:
1401: delete from pa_org_hierarchy_denorm
1402: where child_organization_id = x_organization_id
1403: and org_hierarchy_version_id = imp_rec.proj_org_structure_version_id
1404: and pa_org_use_type = 'PROJECTS'; /* Bug#2643047 - Only PROJECTS records need to be deleted as we are checking
1405: for Project task owning classification here */
1517: and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1518: and pa_org_use_type = 'EXPENDITURES'
1519: and inactive_date is null ; -- Bug Ref # 6367868
1520:
1521: delete from pa_org_hierarchy_denorm
1522: where child_organization_id = x_organization_id
1523: and org_hierarchy_version_id = imp_rec.exp_org_structure_version_id
1524: and pa_org_use_type = 'EXPENDITURES'; /* Bug#2643047 - Only EXPENDITURES records need to be deleted as we
1525: are checking for Project/expenditure owning classification here */
1761: */
1762:
1763:
1764:
1765: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
1766: , child_organization_id
1767: , org_hierarchy_version_id
1768: , pa_org_use_type
1769: , creation_date
1783: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1784: from dual
1785: where not exists
1786: (select 'Y'
1787: from pa_org_hierarchy_denorm
1788: where pa_org_use_type = p_pa_org_use_type
1789: and parent_organization_id = p_parent_organization_id
1790: and child_organization_id = p_child_organization_id
1791: and org_hierarchy_version_id = p_org_hierarchy_version_id);
1861: */
1862:
1863:
1864:
1865: INSERT INTO pa_org_hierarchy_denorm ( parent_organization_id
1866: , child_organization_id
1867: , org_hierarchy_version_id
1868: , pa_org_use_type
1869: , creation_date
1887: , p_child_level
1888: from dual
1889: where not exists
1890: (select 'Y'
1891: from pa_org_hierarchy_denorm
1892: where pa_org_use_type = p_pa_org_use_type
1893: and parent_organization_id = p_parent_organization_id
1894: and child_organization_id = p_child_organization_id
1895: and org_hierarchy_version_id = p_org_hierarchy_version_id);
1910: -- PROCEDURE
1911: --
1912: -- populates org hierarchy denorm for reporting type of organizations
1913: --
1914: -- This Procedure inserts records into pa_org_hierarchy_denorm
1915: -- with parent level and child levels for reporting type of
1916: -- organizations
1917: PROCEDURE create_reporting_org
1918: ( p_parent_organization_id IN NUMBER
1931:
1932: x_err_code := 0;
1933: old_stack := x_err_stack;
1934: x_err_stack := x_err_stack || 'create_reporting_org';
1935: --r_msg('inserting into pa_org_hierarchy_denorm');
1936: INSERT INTO pa_org_hierarchy_denorm
1937: ( parent_organization_id
1938: , child_organization_id
1939: , org_hierarchy_version_id
1932: x_err_code := 0;
1933: old_stack := x_err_stack;
1934: x_err_stack := x_err_stack || 'create_reporting_org';
1935: --r_msg('inserting into pa_org_hierarchy_denorm');
1936: INSERT INTO pa_org_hierarchy_denorm
1937: ( parent_organization_id
1938: , child_organization_id
1939: , org_hierarchy_version_id
1940: , pa_org_use_type
1961: , p_org_id
1962: from dual
1963: where not exists
1964: (select 'Y'
1965: from pa_org_hierarchy_denorm
1966: where pa_org_use_type = p_pa_org_use_type
1967: and parent_organization_id = p_parent_organization_id
1968: and child_organization_id = p_child_organization_id
1969: and org_hierarchy_version_id = p_org_hierarchy_version_id
1986: END create_reporting_org;
1987:
1988:
1989: -- This procedure populates parent level and child level for the
1990: -- reporting organizations in pa org hierarchy denorm entity
1991: PROCEDURE populate_org_levels( P_org_version_id IN NUMBER
1992: , P_organization_id_parent IN NUMBER
1993: , P_organization_id_child IN NUMBER
1994: , P_org_level IN NUMBER
2049: /* For all operating units where the Reporting Org structure
2050: specified is same as the changed Org structure */
2051:
2052:
2053: -------- for pa_org_hierarchy_denorm
2054: open get_all_orgs;
2055: loop
2056: fetch get_all_orgs into v_parent_org_id;
2057: exit when get_all_orgs%notfound;
2058: /*
2059: -- get the maximum level of the parent while child level is
2060: -- is calculated based on the formula maxlevel - level + 1
2061: SELECT max(parent_level) into v_plevel
2062: FROM pa_org_hierarchy_denorm
2063: WHERE org_hierarchy_version_id = P_org_version_id
2064: and pa_org_use_type = 'REPORTING'
2065: and parent_organization_id = v_parent_org_id
2066: and org_id = p_org_id;
2066: and org_id = p_org_id;
2067:
2068: if v_plevel is null then
2069: SELECT max(child_level) into v_plevel
2070: FROM pa_org_hierarchy_denorm
2071: WHERE org_hierarchy_version_id = P_org_version_id
2072: and pa_org_use_type = 'REPORTING'
2073: and child_organization_id = v_parent_org_id
2074: and org_id = p_org_id;
2264: fetch check_org_exists into v_dummy_child_id;
2265: if check_org_exists%found then
2266: --r_msg('exists');
2267: Begin
2268: DELETE from pa_org_hierarchy_denorm
2269: WHERE pa_org_use_type = 'REPORTING'
2270: and org_hierarchy_version_id = p_org_version_id
2271: and nvl(org_id, -99) = nvl(v_org_id, -99); /* 2976953-Added the nvl condition for org_id */
2272: if sql%rowcount > 0 then
2292: and org_structure_version_id = p_org_version_id
2293: START WITH organization_id_parent = v_start_org_id
2294: and org_structure_version_id = p_org_version_id;
2295:
2296: -------- for pa_org_hierarchy_denorm ---------------------
2297: open get_all_orgs(p_org_version_id,v_start_org_id);
2298: loop
2299: fetch get_all_orgs into v_org_level,v_parent_org_id,v_child_org_id;
2300: exit when get_all_orgs%notfound;
2348: END populate_hierarchy_denorm ;
2349:
2350:
2351: PROCEDURE Check_Org_In_OrgHierarchy(
2352: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2353: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2354: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2355: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2356: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2349:
2350:
2351: PROCEDURE Check_Org_In_OrgHierarchy(
2352: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2353: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2354: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2355: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2356: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2357: IS
2350:
2351: PROCEDURE Check_Org_In_OrgHierarchy(
2352: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2353: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2354: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2355: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2356: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2357: IS
2358:
2362:
2363: pa_debug.init_err_stack ('pa_org_utils.Check_Org_In_OrgHierarchy');
2364:
2365: select 'X' into l_exist
2366: from pa_org_hierarchy_denorm
2367: where parent_organization_id = p_organization_id
2368: and org_hierarchy_version_id = p_org_structure_version_id
2369: and pa_org_use_type = p_org_structure_type
2370: and rownum = 1;
2385: RAISE;
2386: END Check_Org_In_OrgHierarchy;
2387:
2388: PROCEDURE Check_Org_Type(
2389: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2390: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2391: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2392: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2393: IS
2386: END Check_Org_In_OrgHierarchy;
2387:
2388: PROCEDURE Check_Org_Type(
2389: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2390: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2391: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2392: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2393: IS
2394:
2398:
2399: pa_debug.init_err_stack ('pa_org_utils.Check_Org_Type');
2400:
2401: select 'X' into l_exist
2402: from pa_org_hierarchy_denorm
2403: where parent_organization_id = p_organization_id
2404: and pa_org_use_type = p_org_structure_type
2405: and rownum = 1;
2406:
2425: END Check_Org_Type;
2426:
2427:
2428: PROCEDURE Check_OrgHierarchy_Type(
2429: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2430: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2431: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2432: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2433: IS
2426:
2427:
2428: PROCEDURE Check_OrgHierarchy_Type(
2429: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2430: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2431: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2432: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2433: IS
2434:
2438:
2439: pa_debug.init_err_stack ('pa_org_utils.Check_OrgHierarchy_Type');
2440:
2441: select 'X' into l_exist
2442: from pa_org_hierarchy_denorm
2443: where org_hierarchy_version_id = p_org_structure_version_id
2444: and pa_org_use_type = p_org_structure_type
2445: and rownum = 1;
2446:
2594: mano_msg('p_organization_id_child = ' || p_organization_id_child );
2595: mano_msg('p_org_id = ' || p_org_id );
2596: */
2597:
2598: /* delete from pa_org_hierarchy_denorm */
2599:
2600: DELETE from pa_org_hierarchy_denorm
2601: WHERE org_hierarchy_version_id = p_org_version_id
2602: AND nvl(org_id, -99) = nvl(p_org_id, -99)
2596: */
2597:
2598: /* delete from pa_org_hierarchy_denorm */
2599:
2600: DELETE from pa_org_hierarchy_denorm
2601: WHERE org_hierarchy_version_id = p_org_version_id
2602: AND nvl(org_id, -99) = nvl(p_org_id, -99)
2603: AND pa_org_use_type = 'REPORTING'; /* 2976953-Added nvl condition for org id and check for
2604: pa_org_use_type being REPORTING */
2632: For each parent it will insert all of its child at any level. Like
2633: for org3 it will insert org5-10 with their appropriate level
2634: */
2635:
2636: INSERT INTO pa_org_hierarchy_denorm
2637: ( parent_organization_id
2638: , child_organization_id
2639: , org_hierarchy_version_id
2640: , pa_org_use_type
2678: */
2679:
2680: --mano_msg('now inserting for each child ');
2681:
2682: INSERT INTO pa_org_hierarchy_denorm
2683: ( parent_organization_id
2684: , child_organization_id
2685: , org_hierarchy_version_id
2686: , pa_org_use_type
2704: , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2705: , child_level
2706: , child_level
2707: , p_org_id
2708: FROM pa_org_hierarchy_denorm
2709: where org_hierarchy_version_id = p_org_version_id -- org_hierarchy_version_id Changed via bug 2890156
2710: and nvl(org_id, -99) = nvl(p_org_id, -99) /* 2976953-Added nvl to the org_id condition */
2711: and pa_org_use_type = 'REPORTING' /* 2976953- Added this condition */
2712: and parent_organization_id = p_organization_id_parent
2774: x_exists_in_denorm varchar2(1);
2775:
2776: Cursor get_all_orgs(p_org_id number) is
2777: select parent_organization_id, parent_level
2778: from pa_org_hierarchy_denorm
2779: where org_hierarchy_version_id = x_org_version_id
2780: and child_organization_id = x_organization_id_parent
2781: and pa_org_use_type = 'REPORTING'
2782: and nvl(org_id, -99) = nvl(p_org_id, -99);
2795: loop
2796:
2797: /* Get the level for the organization x_organization_id_parent */
2798: /* The variable x_exists_in_denorm is to check whether the parent organization is
2799: existing in pa_org_hierarchy_denorm table, if it is not there it means that the parent
2800: organization is not in the reporting hierarchy below the reporting start organization */
2801:
2802: x_exists_in_denorm := 'Y';
2803:
2801:
2802: x_exists_in_denorm := 'Y';
2803:
2804: begin
2805: select unique parent_level into x_parent_level from pa_org_hierarchy_denorm
2806: where org_hierarchy_version_id = x_org_version_id
2807: and parent_organization_id = x_organization_id_parent
2808: and child_organization_id = x_organization_id_parent
2809: and pa_org_use_type = 'REPORTING'
2823: org7 org8 org9
2824: |
2825: org10
2826: in this example p_organization_id_parent = org3.
2827: The data in pa_org_hierarchy_denorm is as follows:
2828: parent child p_level c_level
2829: org3 org5 4 3
2830: org3 org6 4 3
2831: org3 org7 4 2
2853: /* Need to process only if x_exists_in_denorm is 'Y' */
2854:
2855: if (x_exists_in_denorm = 'Y') then
2856: if (x_parent_level = 1) then
2857: update pa_org_hierarchy_denorm
2858: set parent_level = parent_level + 1,
2859: child_level = child_level + 1
2860: where org_hierarchy_version_id = x_org_version_id
2861: and pa_org_use_type = 'REPORTING'
2864: else
2865: x_child_level := x_parent_level - 1; /* Child level will be 1 less than parent level as parent level <> 1 */
2866: end if;
2867:
2868: /* Now we have the child level, so far all the parents of x_organization_id_parent, we need add a record in pa_org_hierarchy_denorm
2869: along with x_organization_id_child */
2870:
2871: for rec in get_all_orgs(imp_rec.org_id) loop
2872: create_reporting_org(rec.parent_organization_id,
2924: Cursor check_lower_org_exists(p_org_id number) IS
2925: Select 1
2926: from dual
2927: where exists
2928: (select 1 from pa_org_hierarchy_denorm
2929: where org_hierarchy_version_id = x_org_version_id and
2930: pa_org_use_type = 'REPORTING' and
2931: nvl(org_id, -99) = nvl(p_org_id, -99) and /* 2976953 Added nvl for org_id condition */
2932: parent_level = 1
2954: open check_lower_org_exists(imp_rec.org_id);
2955: fetch check_lower_org_exists into x_exists;
2956:
2957: If check_lower_org_exists%notfound then
2958: update pa_org_hierarchy_denorm
2959: set parent_level = parent_level - 1,
2960: child_level = child_level - 1
2961: where org_hierarchy_version_id = x_org_version_id
2962: and pa_org_use_type = 'REPORTING'
3028: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE,
3029: l_org_id pa_implementations_all.org_id%type) IS
3030: SELECT child_organization_id
3031: , child_level
3032: FROM pa_org_hierarchy_denorm
3033: where org_hierarchy_version_id = l_version_id
3034: and nvl(org_id, -99) = nvl(l_org_id, -99)
3035: and pa_org_use_type = 'REPORTING'
3036: and parent_organization_id = l_start_org_id;
3145: pa_implementations_all
3146: WHERE org_structure_version_id = l_version_id)
3147: LOOP
3148: /* Commented for bug#5952671
3149: DELETE from pa_org_hierarchy_denorm
3150: WHERE org_hierarchy_version_id = l_version_id
3151: AND org_id = imp_rec.org_id
3152: AND pa_org_use_type = 'REPORTING'; */
3153: /* Added For bug#5952671 */
3157: FETCH all_org_id BULK COLLECT INTO l_org_id_tbl;
3158: CLOSE all_org_id;
3159:
3160: FORALL i in l_org_id_tbl.first..l_org_id_tbl.last
3161: DELETE from pa_org_hierarchy_denorm
3162: WHERE org_hierarchy_version_id = l_version_id
3163: AND nvl(org_id, -99) = nvl(l_org_id_tbl(i), -99)
3164: AND pa_org_use_type = 'REPORTING';
3165: l_tmp_org_id := l_org_id_tbl(l_org_id_tbl.last);
3193: pa_implementations_all
3194: WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
3195: LOOP
3196: forall i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
3197: INSERT INTO pa_org_hierarchy_denorm
3198: ( parent_organization_id
3199: , child_organization_id
3200: , org_hierarchy_version_id
3201: , pa_org_use_type
3245: pa_implementations_all
3246: WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
3247: LOOP
3248: FORALL i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
3249: INSERT INTO pa_org_hierarchy_denorm
3250: ( parent_organization_id
3251: , child_organization_id
3252: , org_hierarchy_version_id
3253: , pa_org_use_type
3282: FOR imp1_rec IN (SELECT org_id FROM
3283: pa_implementations_all
3284: WHERE org_structure_version_id = l_version_id AND start_organization_id = imp_rec.start_organization_id)
3285: LOOP
3286: INSERT INTO pa_org_hierarchy_denorm
3287: ( parent_organization_id
3288: , child_organization_id
3289: , org_hierarchy_version_id
3290: , pa_org_use_type
3314: END LOOP;
3315: /* End bug#5952671*/
3316: /* Commented for bug#5952671
3317: FOR c1rec in all_parents (v_max_level, l_version_id, imp_rec.start_organization_id) LOOP
3318: INSERT INTO pa_org_hierarchy_denorm
3319: ( parent_organization_id
3320: , child_organization_id
3321: , org_hierarchy_version_id
3322: , pa_org_use_type
3350: );
3351: END LOOP;
3352:
3353:
3354: INSERT INTO pa_org_hierarchy_denorm
3355: ( parent_organization_id
3356: , child_organization_id
3357: , org_hierarchy_version_id
3358: , pa_org_use_type
3376: , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3377: , child_level
3378: , child_level
3379: , imp_rec.org_id
3380: FROM pa_org_hierarchy_denorm
3381: where org_hierarchy_version_id = l_version_id
3382: and org_id = imp_rec.org_id
3383: and pa_org_use_type = 'REPORTING' -- Added for bug#5361709
3384: and parent_organization_id = imp_rec.start_organization_id
3404:
3405: END maintain_org_hist_update;
3406:
3407: /* Bug 3649799 - This procedure will be called for rebuilding project and expenditure
3408: hierarchy in pa_org_hierarchy_denorm table. */
3409:
3410: procedure maintain_projexp_org_update(p_version_id in number,
3411: p_org_use_type in varchar2,
3412: x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3460: WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
3461: 'EXPENDITURES', exp_org_structure_version_id) = p_version_id
3462: AND ROWNUM = 1;
3463:
3464: DELETE FROM pa_org_hierarchy_denorm
3465: WHERE org_hierarchy_version_id = p_version_id
3466: AND pa_org_use_type = p_org_use_type;
3467:
3468: EXCEPTION
3473: FOR imp_rec IN start_orgs(p_version_id)
3474: LOOP
3475: BEGIN
3476: SELECT 'Y' INTO l_exist_recs
3477: FROM pa_org_hierarchy_denorm
3478: WHERE org_hierarchy_version_id = p_version_id
3479: AND pa_org_use_type = p_org_use_type
3480: AND parent_organization_id = imp_rec.start_organization_id
3481: AND ROWNUM = 1;