[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';
336:
337: --- Delete all rows from pa_cc_tp_schedule_line_lkp
338: delete from pa_cc_tp_schedule_line_lkp;
339:
340: --- changes for pa_org_hierarchy_denorm table
341: if (nvl(x_old_proj_start_org_id,x_new_proj_start_org_id +1) <> x_new_proj_start_org_id ) then
342: ---- start org is changed, update only if org is moved up
343:
344: -- Bug 3795903- Modified the call to check_org_exists
390: end if;
391: bug6444664*/
392: IF l_child_org_id_var.COUNT > 0 THEN
393: FORALL i IN l_child_org_id_var.FIRST..l_child_org_id_var.LAST
394: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
395: , child_organization_id
396: , org_hierarchy_version_id
397: , pa_org_use_type
398: , creation_date
412: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
413: from dual
414: where not exists
415: (select 'Y'
416: from pa_org_hierarchy_denorm
417: where pa_org_use_type = 'PROJECTS'
418: and parent_organization_id = l_parent_org_id
419: and child_organization_id = l_child_org_id_var(i)
420: and org_hierarchy_version_id = x_new_proj_org_version_id)
484: );
485:
486: end if;
487:
488: --- changes for pa_org_hierarchy_denorm table
489: if (nvl(x_old_exp_start_org_id,x_new_exp_start_org_id +1) <> x_new_exp_start_org_id ) then
490: ---- start org is changed, update only if org is moved up
491:
492: -- Bug 3795903- Modified the call to check_org_exists
537: end if;
538: bug6444664*/
539: IF l_child_org_id_var.COUNT > 0 THEN
540: FORALL i IN l_child_org_id_var.FIRST..l_child_org_id_var.LAST
541: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
542: , child_organization_id
543: , org_hierarchy_version_id
544: , pa_org_use_type
545: , creation_date
559: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
560: from dual
561: where not exists
562: (select 'Y'
563: from pa_org_hierarchy_denorm
564: where pa_org_use_type = 'EXPENDITURES'
565: and parent_organization_id = l_parent_org_id
566: and child_organization_id = l_child_org_id_var(i)
567: and org_hierarchy_version_id = x_new_exp_org_version_id)
586: or
587: (nvl(x_old_start_organization_id,x_new_start_organization_id ) <>
588: x_new_start_organization_id ) ) then
589: -- call procedure populate hierarchy denorm to insert rows into
590: -- into pa_org_hierarchy_denorm for reporting type of organizations
591:
592: /* Bug 2976953 - Changed populate_hierarchy_denorm to populate_hierarchy_denorm2 and also added
593: the parameter p_org_id for that call */
594:
702: CURSOR check_org_ver_not_exists ( lp_org_version_id IN NUMBER ) IS
703: SELECT 'x'
704: FROM dual
705: WHERE not exists
706: ( SELECT 'x' from pa_org_hierarchy_denorm
707: WHERE org_hierarchy_version_id = nvl(lp_org_version_id,org_hierarchy_version_id)
708: and pa_org_use_type = 'TP_SCHEDULE' );
709:
710: */
749:
750: /* Start of code Added for bug#2723330 */
751:
752: BEGIN
753: DELETE from pa_org_hierarchy_denorm
754: WHERE org_hierarchy_version_id = p_org_version_id
755: AND pa_org_use_type = 'TP_SCHEDULE';
756: EXCEPTION
757: WHEN others THEN
793:
794: IF l_child_level_var.COUNT > 0 THEN
795: FORALL i IN l_child_level_var.FIRST..l_child_level_var.LAST
796:
797: INSERT INTO pa_org_hierarchy_denorm ( parent_organization_id
798: , child_organization_id
799: , org_hierarchy_version_id
800: , pa_org_use_type
801: , creation_date
819: , (l_child_level_var(i) + l_parent_level +1)
820: from dual
821: where not exists
822: (select 'Y'
823: from pa_org_hierarchy_denorm
824: where pa_org_use_type = 'TP_SCHEDULE'
825: and parent_organization_id = l_tmp_parent_org_id
826: and child_organization_id = l_tmp_child_org_id_var(i)
827: and org_hierarchy_version_id = p_org_version_id);
920: (select x_organization_id_parent
921: from sys.dual );
922: */
923:
924: --- To populate pa_org_hierarchy_denorm
925: cursor get_all_orgs is
926: (SELECT x_organization_id_parent
927: FROM dual
928: UNION
1049:
1050: --- Delete all rows from pa_cc_tp_schedule_line_lkp
1051: delete from pa_cc_tp_schedule_line_lkp;
1052:
1053: -------- for pa_org_hierarchy_denorm
1054: /* open get_all_orgs;
1055: loop
1056: fetch get_all_orgs into l_parent_org_id;
1057: exit when get_all_orgs%notfound; */
1079: , x_err_stage => x_err_stage
1080: , x_err_stack => x_err_stack);*/
1081: IF l_parent_org_id_var.COUNT > 0 THEN
1082: FORALL i IN l_parent_org_id_var.FIRST..l_parent_org_id_var.LAST
1083: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
1084: , child_organization_id
1085: , org_hierarchy_version_id
1086: , pa_org_use_type
1087: , creation_date
1101: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1102: from dual
1103: where not exists
1104: (select 'Y'
1105: from pa_org_hierarchy_denorm
1106: where pa_org_use_type = 'PROJECTS'
1107: and parent_organization_id = l_parent_org_id_var(i)
1108: and child_organization_id = x_organization_id_child
1109: and org_hierarchy_version_id = x_org_version_id);
1192: and info.org_information2 = 'Y');
1193:
1194: end if ;
1195:
1196: -------- for pa_org_hierarchy_denorm
1197: /* open get_all_orgs;
1198: loop
1199: fetch get_all_orgs into l_parent_org_id;
1200: exit when get_all_orgs%notfound; */
1223: , x_err_stage => x_err_stage
1224: , x_err_stack => x_err_stack);*/
1225: IF l_parent_org_id_var.COUNT > 0 THEN
1226: FORALL i IN l_parent_org_id_var.FIRST..l_parent_org_id_var.LAST
1227: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
1228: , child_organization_id
1229: , org_hierarchy_version_id
1230: , pa_org_use_type
1231: , creation_date
1245: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1246: from dual
1247: where not exists
1248: (select 'Y'
1249: from pa_org_hierarchy_denorm
1250: where pa_org_use_type = 'EXPENDITURES'
1251: and parent_organization_id = l_parent_org_id_var(i)
1252: and child_organization_id = x_organization_id_child
1253: and org_hierarchy_version_id = x_org_version_id);
1323: end if;
1324:
1325: /*End bug# 2247737*/
1326:
1327: /* Bug#2643047 - Call to populate reporting orgs in pa_org_hierarchy_denorm table */
1328:
1329: pa_org_utils.populate_reporting_orgs(x_org_version_id ,
1330: x_organization_id_child ,
1331: x_organization_id_parent,
1347: x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1348: x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1349: CURSOR c_get_all_orgs IS
1350: SELECT child_organization_id
1351: from pa_org_hierarchy_denorm
1352: where org_hierarchy_version_id = x_org_version_id
1353: and parent_organization_id = x_organization_id_child;
1354:
1355: l_parent_org_id number;
1393: open c_get_all_orgs;
1394: loop
1395: fetch c_get_all_orgs into l_parent_org_id;
1396: exit when c_get_all_orgs%notfound;
1397: delete from pa_org_hierarchy_denorm
1398: where org_hierarchy_version_id = x_org_version_id
1399: and (child_organization_id = l_parent_org_id
1400: or parent_organization_id = l_parent_org_id);
1401: end loop;
1400: or parent_organization_id = l_parent_org_id);
1401: end loop;
1402: close c_get_all_orgs;
1403:
1404: /* Bug#2643047 - call to restructure levels from pa_org_hierarchy_denorm table*/
1405:
1406: pa_org_utils.restructure_rpt_orgs_denorm(
1407: x_org_version_id,
1408: x_err_code ,
1475: v_start_org_id
1476: and org_structure_version_id = v_org_structure_version_id );
1477: */
1478:
1479: --- To populate pa_org_hierarchy_denorm
1480: cursor get_all_orgs is
1481: (SELECT x_organization_id
1482: FROM dual
1483: UNION ALL
1603: and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1604: and pa_org_use_type = 'PROJECTS'
1605: and inactive_date is null; -- Bug Ref # 6367868
1606:
1607: delete from pa_org_hierarchy_denorm
1608: where child_organization_id = x_organization_id
1609: and org_hierarchy_version_id = imp_rec.proj_org_structure_version_id
1610: and pa_org_use_type = 'PROJECTS'; /* Bug#2643047 - Only PROJECTS records need to be deleted as we are checking
1611: for Project task owning classification here */
1723: and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1724: and pa_org_use_type = 'EXPENDITURES'
1725: and inactive_date is null ; -- Bug Ref # 6367868
1726:
1727: delete from pa_org_hierarchy_denorm
1728: where child_organization_id = x_organization_id
1729: and org_hierarchy_version_id = imp_rec.exp_org_structure_version_id
1730: and pa_org_use_type = 'EXPENDITURES'; /* Bug#2643047 - Only EXPENDITURES records need to be deleted as we
1731: are checking for Project/expenditure owning classification here */
1968: */
1969:
1970:
1971:
1972: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
1973: , child_organization_id
1974: , org_hierarchy_version_id
1975: , pa_org_use_type
1976: , creation_date
1990: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1991: from dual
1992: where not exists
1993: (select 'Y'
1994: from pa_org_hierarchy_denorm
1995: where pa_org_use_type = p_pa_org_use_type
1996: and parent_organization_id = p_parent_organization_id
1997: and child_organization_id = p_child_organization_id
1998: and org_hierarchy_version_id = p_org_hierarchy_version_id);
2068: */
2069:
2070:
2071:
2072: INSERT INTO pa_org_hierarchy_denorm ( parent_organization_id
2073: , child_organization_id
2074: , org_hierarchy_version_id
2075: , pa_org_use_type
2076: , creation_date
2094: , p_child_level
2095: from dual
2096: where not exists
2097: (select 'Y'
2098: from pa_org_hierarchy_denorm
2099: where pa_org_use_type = p_pa_org_use_type
2100: and parent_organization_id = p_parent_organization_id
2101: and child_organization_id = p_child_organization_id
2102: and org_hierarchy_version_id = p_org_hierarchy_version_id);
2117: -- PROCEDURE
2118: --
2119: -- populates org hierarchy denorm for reporting type of organizations
2120: --
2121: -- This Procedure inserts records into pa_org_hierarchy_denorm
2122: -- with parent level and child levels for reporting type of
2123: -- organizations
2124: PROCEDURE create_reporting_org
2125: ( p_parent_organization_id IN NUMBER
2138:
2139: x_err_code := 0;
2140: old_stack := x_err_stack;
2141: x_err_stack := x_err_stack || 'create_reporting_org';
2142: --r_msg('inserting into pa_org_hierarchy_denorm');
2143: INSERT INTO pa_org_hierarchy_denorm
2144: ( parent_organization_id
2145: , child_organization_id
2146: , org_hierarchy_version_id
2139: x_err_code := 0;
2140: old_stack := x_err_stack;
2141: x_err_stack := x_err_stack || 'create_reporting_org';
2142: --r_msg('inserting into pa_org_hierarchy_denorm');
2143: INSERT INTO pa_org_hierarchy_denorm
2144: ( parent_organization_id
2145: , child_organization_id
2146: , org_hierarchy_version_id
2147: , pa_org_use_type
2168: , p_org_id
2169: from dual
2170: where not exists
2171: (select 'Y'
2172: from pa_org_hierarchy_denorm
2173: where pa_org_use_type = p_pa_org_use_type
2174: and parent_organization_id = p_parent_organization_id
2175: and child_organization_id = p_child_organization_id
2176: and org_hierarchy_version_id = p_org_hierarchy_version_id
2193: END create_reporting_org;
2194:
2195:
2196: -- This procedure populates parent level and child level for the
2197: -- reporting organizations in pa org hierarchy denorm entity
2198: PROCEDURE populate_org_levels( P_org_version_id IN NUMBER
2199: , P_organization_id_parent IN NUMBER
2200: , P_organization_id_child IN NUMBER
2201: , P_org_level IN NUMBER
2256: /* For all operating units where the Reporting Org structure
2257: specified is same as the changed Org structure */
2258:
2259:
2260: -------- for pa_org_hierarchy_denorm
2261: open get_all_orgs;
2262: loop
2263: fetch get_all_orgs into v_parent_org_id;
2264: exit when get_all_orgs%notfound;
2265: /*
2266: -- get the maximum level of the parent while child level is
2267: -- is calculated based on the formula maxlevel - level + 1
2268: SELECT max(parent_level) into v_plevel
2269: FROM pa_org_hierarchy_denorm
2270: WHERE org_hierarchy_version_id = P_org_version_id
2271: and pa_org_use_type = 'REPORTING'
2272: and parent_organization_id = v_parent_org_id
2273: and org_id = p_org_id;
2273: and org_id = p_org_id;
2274:
2275: if v_plevel is null then
2276: SELECT max(child_level) into v_plevel
2277: FROM pa_org_hierarchy_denorm
2278: WHERE org_hierarchy_version_id = P_org_version_id
2279: and pa_org_use_type = 'REPORTING'
2280: and child_organization_id = v_parent_org_id
2281: and org_id = p_org_id;
2471: fetch check_org_exists into v_dummy_child_id;
2472: if check_org_exists%found then
2473: --r_msg('exists');
2474: Begin
2475: DELETE from pa_org_hierarchy_denorm
2476: WHERE pa_org_use_type = 'REPORTING'
2477: and org_hierarchy_version_id = p_org_version_id
2478: and nvl(org_id, -99) = nvl(v_org_id, -99); /* 2976953-Added the nvl condition for org_id */
2479: if sql%rowcount > 0 then
2499: and org_structure_version_id = p_org_version_id
2500: START WITH organization_id_parent = v_start_org_id
2501: and org_structure_version_id = p_org_version_id;
2502:
2503: -------- for pa_org_hierarchy_denorm ---------------------
2504: open get_all_orgs(p_org_version_id,v_start_org_id);
2505: loop
2506: fetch get_all_orgs into v_org_level,v_parent_org_id,v_child_org_id;
2507: exit when get_all_orgs%notfound;
2555: END populate_hierarchy_denorm ;
2556:
2557:
2558: PROCEDURE Check_Org_In_OrgHierarchy(
2559: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2560: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2561: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2562: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2563: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2556:
2557:
2558: PROCEDURE Check_Org_In_OrgHierarchy(
2559: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2560: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2561: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2562: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2563: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2564: IS
2557:
2558: PROCEDURE Check_Org_In_OrgHierarchy(
2559: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2560: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2561: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2562: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2563: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2564: IS
2565:
2569:
2570: pa_debug.init_err_stack ('pa_org_utils.Check_Org_In_OrgHierarchy');
2571:
2572: select 'X' into l_exist
2573: from pa_org_hierarchy_denorm
2574: where parent_organization_id = p_organization_id
2575: and org_hierarchy_version_id = p_org_structure_version_id
2576: and pa_org_use_type = p_org_structure_type
2577: and rownum = 1;
2592: RAISE;
2593: END Check_Org_In_OrgHierarchy;
2594:
2595: PROCEDURE Check_Org_Type(
2596: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2597: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2598: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2599: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2600: IS
2593: END Check_Org_In_OrgHierarchy;
2594:
2595: PROCEDURE Check_Org_Type(
2596: p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2597: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2598: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2599: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2600: IS
2601:
2605:
2606: pa_debug.init_err_stack ('pa_org_utils.Check_Org_Type');
2607:
2608: select 'X' into l_exist
2609: from pa_org_hierarchy_denorm
2610: where parent_organization_id = p_organization_id
2611: and pa_org_use_type = p_org_structure_type
2612: and rownum = 1;
2613:
2632: END Check_Org_Type;
2633:
2634:
2635: PROCEDURE Check_OrgHierarchy_Type(
2636: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2637: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2638: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2639: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2640: IS
2633:
2634:
2635: PROCEDURE Check_OrgHierarchy_Type(
2636: p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2637: p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2638: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2639: x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2640: IS
2641:
2645:
2646: pa_debug.init_err_stack ('pa_org_utils.Check_OrgHierarchy_Type');
2647:
2648: select 'X' into l_exist
2649: from pa_org_hierarchy_denorm
2650: where org_hierarchy_version_id = p_org_structure_version_id
2651: and pa_org_use_type = p_org_structure_type
2652: and rownum = 1;
2653:
2801: mano_msg('p_organization_id_child = ' || p_organization_id_child );
2802: mano_msg('p_org_id = ' || p_org_id );
2803: */
2804:
2805: /* delete from pa_org_hierarchy_denorm */
2806:
2807: DELETE from pa_org_hierarchy_denorm
2808: WHERE /*org_hierarchy_version_id = p_org_version_id For Bug 8523652*/
2809: nvl(org_id, -99) = nvl(p_org_id, -99)
2803: */
2804:
2805: /* delete from pa_org_hierarchy_denorm */
2806:
2807: DELETE from pa_org_hierarchy_denorm
2808: WHERE /*org_hierarchy_version_id = p_org_version_id For Bug 8523652*/
2809: nvl(org_id, -99) = nvl(p_org_id, -99)
2810: AND pa_org_use_type = 'REPORTING'; /* 2976953-Added nvl condition for org id and check for
2811: pa_org_use_type being REPORTING */
2839: For each parent it will insert all of its child at any level. Like
2840: for org3 it will insert org5-10 with their appropriate level
2841: */
2842:
2843: INSERT INTO pa_org_hierarchy_denorm
2844: ( parent_organization_id
2845: , child_organization_id
2846: , org_hierarchy_version_id
2847: , pa_org_use_type
2885: */
2886:
2887: --mano_msg('now inserting for each child ');
2888:
2889: INSERT INTO pa_org_hierarchy_denorm
2890: ( parent_organization_id
2891: , child_organization_id
2892: , org_hierarchy_version_id
2893: , pa_org_use_type
2911: , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2912: , child_level
2913: , child_level
2914: , p_org_id
2915: FROM pa_org_hierarchy_denorm
2916: where org_hierarchy_version_id = p_org_version_id -- org_hierarchy_version_id Changed via bug 2890156
2917: and nvl(org_id, -99) = nvl(p_org_id, -99) /* 2976953-Added nvl to the org_id condition */
2918: and pa_org_use_type = 'REPORTING' /* 2976953- Added this condition */
2919: and parent_organization_id = p_organization_id_parent
2981: x_exists_in_denorm varchar2(1);
2982:
2983: Cursor get_all_orgs(p_org_id number) is
2984: select parent_organization_id, parent_level
2985: from pa_org_hierarchy_denorm
2986: where org_hierarchy_version_id = x_org_version_id
2987: and child_organization_id = x_organization_id_parent
2988: and pa_org_use_type = 'REPORTING'
2989: and nvl(org_id, -99) = nvl(p_org_id, -99);
3002: loop
3003:
3004: /* Get the level for the organization x_organization_id_parent */
3005: /* The variable x_exists_in_denorm is to check whether the parent organization is
3006: existing in pa_org_hierarchy_denorm table, if it is not there it means that the parent
3007: organization is not in the reporting hierarchy below the reporting start organization */
3008:
3009: x_exists_in_denorm := 'Y';
3010:
3008:
3009: x_exists_in_denorm := 'Y';
3010:
3011: begin
3012: select unique parent_level into x_parent_level from pa_org_hierarchy_denorm
3013: where org_hierarchy_version_id = x_org_version_id
3014: and parent_organization_id = x_organization_id_parent
3015: and child_organization_id = x_organization_id_parent
3016: and pa_org_use_type = 'REPORTING'
3030: org7 org8 org9
3031: |
3032: org10
3033: in this example p_organization_id_parent = org3.
3034: The data in pa_org_hierarchy_denorm is as follows:
3035: parent child p_level c_level
3036: org3 org5 4 3
3037: org3 org6 4 3
3038: org3 org7 4 2
3060: /* Need to process only if x_exists_in_denorm is 'Y' */
3061:
3062: if (x_exists_in_denorm = 'Y') then
3063: if (x_parent_level = 1) then
3064: update pa_org_hierarchy_denorm
3065: set parent_level = parent_level + 1,
3066: child_level = child_level + 1
3067: where org_hierarchy_version_id = x_org_version_id
3068: and pa_org_use_type = 'REPORTING'
3071: else
3072: x_child_level := x_parent_level - 1; /* Child level will be 1 less than parent level as parent level <> 1 */
3073: end if;
3074:
3075: /* 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
3076: along with x_organization_id_child */
3077:
3078: for rec in get_all_orgs(imp_rec.org_id) loop
3079: create_reporting_org(rec.parent_organization_id,
3131: Cursor check_lower_org_exists(p_org_id number) IS
3132: Select 1
3133: from dual
3134: where exists
3135: (select 1 from pa_org_hierarchy_denorm
3136: where org_hierarchy_version_id = x_org_version_id and
3137: pa_org_use_type = 'REPORTING' and
3138: nvl(org_id, -99) = nvl(p_org_id, -99) and /* 2976953 Added nvl for org_id condition */
3139: parent_level = 1
3161: open check_lower_org_exists(imp_rec.org_id);
3162: fetch check_lower_org_exists into x_exists;
3163:
3164: If check_lower_org_exists%notfound then
3165: update pa_org_hierarchy_denorm
3166: set parent_level = parent_level - 1,
3167: child_level = child_level - 1
3168: where org_hierarchy_version_id = x_org_version_id
3169: and pa_org_use_type = 'REPORTING'
3235: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE,
3236: l_org_id pa_implementations_all.org_id%type) IS
3237: SELECT child_organization_id
3238: , child_level
3239: FROM pa_org_hierarchy_denorm
3240: where org_hierarchy_version_id = l_version_id
3241: and nvl(org_id, -99) = nvl(l_org_id, -99)
3242: and pa_org_use_type = 'REPORTING'
3243: and parent_organization_id = l_start_org_id;
3352: pa_implementations_all
3353: WHERE org_structure_version_id = l_version_id)
3354: LOOP
3355: /* Commented for bug#5952671
3356: DELETE from pa_org_hierarchy_denorm
3357: WHERE org_hierarchy_version_id = l_version_id
3358: AND org_id = imp_rec.org_id
3359: AND pa_org_use_type = 'REPORTING'; */
3360: /* Added For bug#5952671 */
3364: FETCH all_org_id BULK COLLECT INTO l_org_id_tbl;
3365: CLOSE all_org_id;
3366:
3367: FORALL i in l_org_id_tbl.first..l_org_id_tbl.last
3368: DELETE from pa_org_hierarchy_denorm
3369: WHERE org_hierarchy_version_id = l_version_id
3370: AND nvl(org_id, -99) = nvl(l_org_id_tbl(i), -99)
3371: AND pa_org_use_type = 'REPORTING';
3372: l_tmp_org_id := l_org_id_tbl(l_org_id_tbl.last);
3400: pa_implementations_all
3401: WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
3402: LOOP
3403: forall i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
3404: INSERT INTO pa_org_hierarchy_denorm
3405: ( parent_organization_id
3406: , child_organization_id
3407: , org_hierarchy_version_id
3408: , pa_org_use_type
3452: pa_implementations_all
3453: WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
3454: LOOP
3455: FORALL i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
3456: INSERT INTO pa_org_hierarchy_denorm
3457: ( parent_organization_id
3458: , child_organization_id
3459: , org_hierarchy_version_id
3460: , pa_org_use_type
3489: FOR imp1_rec IN (SELECT org_id FROM
3490: pa_implementations_all
3491: WHERE org_structure_version_id = l_version_id AND start_organization_id = imp_rec.start_organization_id)
3492: LOOP
3493: INSERT INTO pa_org_hierarchy_denorm
3494: ( parent_organization_id
3495: , child_organization_id
3496: , org_hierarchy_version_id
3497: , pa_org_use_type
3521: END LOOP;
3522: /* End bug#5952671*/
3523: /* Commented for bug#5952671
3524: FOR c1rec in all_parents (v_max_level, l_version_id, imp_rec.start_organization_id) LOOP
3525: INSERT INTO pa_org_hierarchy_denorm
3526: ( parent_organization_id
3527: , child_organization_id
3528: , org_hierarchy_version_id
3529: , pa_org_use_type
3557: );
3558: END LOOP;
3559:
3560:
3561: INSERT INTO pa_org_hierarchy_denorm
3562: ( parent_organization_id
3563: , child_organization_id
3564: , org_hierarchy_version_id
3565: , pa_org_use_type
3583: , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3584: , child_level
3585: , child_level
3586: , imp_rec.org_id
3587: FROM pa_org_hierarchy_denorm
3588: where org_hierarchy_version_id = l_version_id
3589: and org_id = imp_rec.org_id
3590: and pa_org_use_type = 'REPORTING' -- Added for bug#5361709
3591: and parent_organization_id = imp_rec.start_organization_id
3611:
3612: END maintain_org_hist_update;
3613:
3614: /* Bug 3649799 - This procedure will be called for rebuilding project and expenditure
3615: hierarchy in pa_org_hierarchy_denorm table. */
3616:
3617: procedure maintain_projexp_org_update(p_version_id in number,
3618: p_org_use_type in varchar2,
3619: x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3670: WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
3671: 'EXPENDITURES', exp_org_structure_version_id) = p_version_id
3672: AND ROWNUM = 1;
3673:
3674: DELETE FROM pa_org_hierarchy_denorm
3675: WHERE org_hierarchy_version_id = p_version_id
3676: AND pa_org_use_type = p_org_use_type;
3677:
3678: EXCEPTION
3683: FOR imp_rec IN start_orgs(p_version_id)
3684: LOOP
3685: BEGIN
3686: SELECT 'Y' INTO l_exist_recs
3687: FROM pa_org_hierarchy_denorm
3688: WHERE org_hierarchy_version_id = p_version_id
3689: AND pa_org_use_type = p_org_use_type
3690: AND parent_organization_id = imp_rec.start_organization_id
3691: AND ROWNUM = 1;
3728: , x_err_stack => x_err_stack);
3729: END IF; */
3730: /*
3731: FORALL i IN l_child_org_id2_var.FIRST..l_child_org_id2_var.LAST
3732: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
3733: , child_organization_id
3734: , org_hierarchy_version_id
3735: , pa_org_use_type
3736: , creation_date
3750: ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
3751: from dual
3752: where not exists
3753: (select 'Y'
3754: from pa_org_hierarchy_denorm
3755: where pa_org_use_type = p_org_use_type
3756: and parent_organization_id = l_parent_org_id
3757: and child_organization_id = l_child_org_id2_var(i)
3758: and org_hierarchy_version_id = p_version_id)
3768: END LOOP;
3769: CLOSE c_get_new_org;
3770: Commented for 9811574 */
3771: /* Fix for bug 9811574 - Start */
3772: INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
3773: , child_organization_id
3774: , org_hierarchy_version_id
3775: , pa_org_use_type
3776: , creation_date
3826: and org_information_context = 'CLASS'
3827: and org_information2 = 'Y'
3828: and not exists
3829: (select 'Y'
3830: from pa_org_hierarchy_denorm
3831: where pa_org_use_type = 'PROJECTS'
3832: and parent_organization_id = per_org.parent
3833: and child_organization_id = per_org.child
3834: and org_hierarchy_version_id = p_version_id);