eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_ORG_UTILS dependencies on PER_ORG_STRUCTURE_ELEMENTS

Line 28: FROM per_org_structure_elements

24:
25: else
26: SELECT max(level)
27: INTO l_tmp_level
28: FROM per_org_structure_elements
29: WHERE org_structure_version_id = p_org_version_id
30: AND organization_id_parent = p_child_parent_org_id
31: CONNECT BY PRIOR organization_id_child = organization_id_parent
32: AND org_structure_version_id = p_org_version_id

Line 40: FROM per_org_structure_elements

36: If l_tmp_level is NULL then
37:
38: SELECT max(level)
39: INTO l_tmp_level
40: FROM per_org_structure_elements
41: WHERE org_structure_version_id = p_org_version_id
42: AND organization_id_child = p_child_parent_org_id
43: CONNECT BY PRIOR organization_id_child = organization_id_parent
44: AND org_structure_version_id = p_org_version_id

Line 83: FROM per_org_structure_elements

79: IS
80: l_max_org_level number;
81: BEGIN
82: SELECT max(level) into l_max_org_level
83: FROM per_org_structure_elements
84: WHERE org_structure_version_id = p_org_version_id
85: CONNECT BY PRIOR organization_id_child = organization_id_parent
86: and org_structure_version_id = p_org_version_id
87: START WITH organization_id_parent = p_start_org_id

Line 103: FROM per_org_structure_elements a

99: BEGIN
100:
101: SELECT a.organization_id_parent
102: INTO l_start_org_id
103: FROM per_org_structure_elements a
104: WHERE org_structure_version_id = p_org_version_id
105: AND not exists ( select 'a' from per_org_structure_elements b
106: WHERE a.organization_id_parent = b.organization_id_child and
107: a.org_structure_version_id = b.org_structure_version_id)

Line 105: AND not exists ( select 'a' from per_org_structure_elements b

101: SELECT a.organization_id_parent
102: INTO l_start_org_id
103: FROM per_org_structure_elements a
104: WHERE org_structure_version_id = p_org_version_id
105: AND not exists ( select 'a' from per_org_structure_elements b
106: WHERE a.organization_id_parent = b.organization_id_child and
107: a.org_structure_version_id = b.org_structure_version_id)
108: AND rownum = 1;
109:

Line 172: from per_org_structure_elements a

168: into x_start_org_id
169: from pa_implementations;
170: else
171: select distinct organization_id_parent into x_start_org_id
172: from per_org_structure_elements a
173: ,pa_implementations b
174: ,hr_organization_information c
175: where organization_id_parent not in
176: ( select d.ORGANIZATION_ID_CHILD from per_org_structure_elements d

Line 176: ( select d.ORGANIZATION_ID_CHILD from per_org_structure_elements d

172: from per_org_structure_elements a
173: ,pa_implementations b
174: ,hr_organization_information c
175: where organization_id_parent not in
176: ( select d.ORGANIZATION_ID_CHILD from per_org_structure_elements d
177: where d.org_structure_version_id = to_number(c.org_information2)
178: )
179: and a.org_structure_version_id = to_number(c.org_information2)
180: and b.business_group_id = c.organization_id

Line 213: FROM per_org_structure_elements

209: SELECT l_new_start_org_id
210: FROM dual
211: UNION ALL
212: SELECT organization_id_child
213: FROM per_org_structure_elements
214: WHERE org_structure_version_id = l_new_org_version_id
215: CONNECT BY PRIOR organization_id_child = organization_id_parent
216: AND org_structure_version_id = l_new_org_version_id
217: START WITH organization_id_parent = l_new_start_org_id

Line 227: FROM per_org_structure_elements

223: SELECT l_org_id
224: FROM dual
225: UNION ALL
226: SELECT organization_id_child
227: FROM per_org_structure_elements
228: WHERE org_structure_version_id = l_new_org_version_id
229: CONNECT BY PRIOR organization_id_child = organization_id_parent
230: AND org_structure_version_id = l_new_org_version_id
231: START WITH organization_id_parent = l_org_id

Line 238: from per_org_structure_elements se

234: /* Bug 3795903 - Added parameters to the cursor check_org_exists */
235:
236: cursor check_org_exists (l_old_org_version_id IN NUMBER,l_old_org_start_id IN NUMBER, l_new_start_org_id IN NUMBER) is
237: (select se.organization_id_child
238: from per_org_structure_elements se
239: where se.org_structure_version_id = l_old_org_version_id
240: connect by prior se.organization_id_child = se.organization_id_parent
241: and org_structure_version_id = l_old_org_version_id
242: start with se.organization_id_parent = l_old_org_start_id

Line 251: c_org_struct_version_id IN per_org_structure_elements.org_structure_version_id%TYPE,

247: (select l_new_start_org_id from sys.dual );
248:
249: /* Added the cursor for bug 4232613 */
250: CURSOR proj_exp_check(
251: c_org_struct_version_id IN per_org_structure_elements.org_structure_version_id%TYPE,
252: l_org_id IN pa_implementations_all.org_id%TYPE
253: )
254: IS
255: SELECT 'Y' FROM pa_implementations_all

Line 298: from per_org_structure_elements se

294: pa_org_use_type )
295: ( (select se.organization_id_child
296: ,x_org_id
297: ,'PROJECTS'
298: from per_org_structure_elements se
299: where org_structure_version_id = x_new_proj_org_version_id
300: AND EXISTS (select 'X'
301: from hr_organization_information info
302: where info.organization_id = se.organization_id_child

Line 408: from per_org_structure_elements se

404: pa_org_use_type)
405: ( (select se.organization_id_child
406: ,x_org_id
407: ,'EXPENDITURES'
408: from per_org_structure_elements se
409: where org_structure_version_id = x_new_exp_org_version_id
410: AND EXISTS (select 'X'
411: from hr_organization_information info
412: where info.organization_id = se.organization_id_child

Line 598: FROM per_org_structure_elements

594: SELECT l_dummy_level,lp_start_org_id
595: FROM dual
596: UNION ALL
597: SELECT level,organization_id_child
598: FROM per_org_structure_elements
599: WHERE org_structure_version_id = lp_org_version_id
600: CONNECT BY PRIOR organization_id_child = organization_id_parent
601: AND org_structure_version_id = lp_org_version_id
602: START WITH organization_id_parent = lp_start_org_id

Line 611: FROM per_org_structure_elements

607: SELECT l_dummy_level,lp_org_id
608: FROM dual
609: UNION ALL
610: SELECT level,organization_id_child
611: FROM per_org_structure_elements
612: WHERE org_structure_version_id = lp_org_version_id
613: CONNECT BY PRIOR organization_id_child = organization_id_parent
614: AND org_structure_version_id = lp_org_version_id
615: START WITH organization_id_parent = lp_org_id

Line 763: from per_org_structure_elements se

759:
760: cursor check_org_exists is
761: (
762: select se.organization_id_parent
763: from per_org_structure_elements se
764: where se.org_structure_version_id = x_org_version_id
765: connect by prior se.organization_id_parent =
766: se.organization_id_child
767: and org_structure_version_id = x_org_version_id

Line 782: from per_org_structure_elements se

778:
779: /* Commented for Bug#2620151 Added cursor above
780: cursor check_org_exists is
781: (select se.organization_id_child
782: from per_org_structure_elements se
783: where se.org_structure_version_id =
784: x_org_version_id
785: connect by prior se.organization_id_child =
786: se.organization_id_parent

Line 805: FROM per_org_structure_elements

801: (SELECT x_organization_id_parent
802: FROM dual
803: UNION
804: SELECT organization_id_parent
805: FROM per_org_structure_elements
806: WHERE org_structure_version_id = x_org_version_id
807: CONNECT BY PRIOR organization_id_parent= organization_id_child
808: AND org_structure_version_id = x_org_version_id
809: START WITH organization_id_child = x_organization_id_parent

Line 824: FROM per_org_structure_elements

820: (SELECT lp_organization_id_parent
821: FROM dual
822: UNION
823: SELECT organization_id_parent
824: FROM per_org_structure_elements
825: WHERE org_structure_version_id = lp_org_version_id
826: CONNECT BY PRIOR organization_id_parent= organization_id_child
827: AND org_structure_version_id = lp_org_version_id
828: START WITH organization_id_child = lp_organization_id_parent

Line 1247: from per_org_structure_elements se

1243:
1244: cursor check_org_exists is
1245: (
1246: select se.organization_id_parent
1247: from per_org_structure_elements se
1248: where se.org_structure_version_id = v_org_structure_version_id
1249: and se.organization_id_parent =
1250: v_start_org_id
1251: connect by prior se.organization_id_parent =

Line 1262: from per_org_structure_elements se

1258:
1259: /* Commeneted the cursor below for bug#2620151, Added new cursor above
1260: cursor check_org_exists is
1261: (select se.organization_id_child
1262: from per_org_structure_elements se
1263: where se.org_structure_version_id = v_org_structure_version_id
1264: and se.organization_id_child = x_organization_id ---made changes as Suggested
1265: connect by prior se.organization_id_child =
1266: se.organization_id_parent

Line 1279: FROM per_org_structure_elements

1275: (SELECT x_organization_id
1276: FROM dual
1277: UNION ALL
1278: SELECT organization_id_parent
1279: FROM per_org_structure_elements
1280: WHERE org_structure_version_id = v_org_structure_version_id
1281: CONNECT BY PRIOR organization_id_parent= organization_id_child
1282: AND org_structure_version_id = v_org_structure_version_id
1283: START WITH organization_id_child = x_organization_id);

Line 1552: FROM per_org_structure_elements se

1548: IS
1549:
1550: CURSOR cur_all_old_org IS
1551: SELECT se.organization_id_child organization_id
1552: FROM per_org_structure_elements se
1553: WHERE org_structure_version_id = x_old_org_version_id
1554: CONNECT BY PRIOR se.organization_id_child
1555: = se.organization_id_parent
1556: AND org_structure_version_id = x_old_org_version_id

Line 1564: FROM per_org_structure_elements se

1560: SELECT x_old_start_org_id FROM Sys.dual ;
1561:
1562: CURSOR cur_proj_new_org IS
1563: SELECT se.organization_id_child organization_id
1564: FROM per_org_structure_elements se
1565: WHERE org_structure_version_id = x_new_org_version_id
1566: AND EXISTS (select 'X'
1567: from hr_organization_information info
1568: where info.organization_id = se.organization_id_child

Line 1589: FROM per_org_structure_elements se

1585:
1586:
1587: CURSOR cur_exp_new_org IS
1588: SELECT se.organization_id_child organization_id
1589: FROM per_org_structure_elements se
1590: WHERE org_structure_version_id = x_new_org_version_id
1591: AND EXISTS (select 'X'
1592: from hr_organization_information info
1593: where info.organization_id = se.organization_id_child

Line 2021: FROM per_org_structure_elements

2017: SELECT P_organization_id_parent
2018: FROM dual
2019: UNION
2020: (SELECT distinct organization_id_parent
2021: FROM per_org_structure_elements
2022: WHERE org_structure_version_id = P_org_version_id
2023: CONNECT BY PRIOR organization_id_parent= organization_id_child
2024: AND org_structure_version_id = P_org_version_id
2025: START WITH organization_id_child = P_organization_id_parent

Line 2028: ---- since the start organization id defined in per_org_structure_elements

2024: AND org_structure_version_id = P_org_version_id
2025: START WITH organization_id_child = P_organization_id_parent
2026: AND org_structure_version_id = P_org_version_id
2027: /* ---- The following query is added to fix the bug : 1654453 ---
2028: ---- since the start organization id defined in per_org_structure_elements
2029: ---- may be different from the start_organization_id set up in pa_implementations
2030: ---- so always the reporting hierarchy is formed based on pa_implementations
2031: ---- start_organization_id */
2032: MINUS

Line 2034: FROM per_org_structure_elements

2030: ---- so always the reporting hierarchy is formed based on pa_implementations
2031: ---- start_organization_id */
2032: MINUS
2033: SELECT distinct organization_id_parent
2034: FROM per_org_structure_elements
2035: WHERE org_structure_version_id = P_org_version_id
2036: CONNECT BY PRIOR organization_id_parent= organization_id_child
2037: AND org_structure_version_id = P_org_version_id
2038: START WITH organization_id_child = P_start_org_id

Line 2108: -- get the parent organization level from the per_org_structure_elements

2104: v_clevel := P_org_max_level - P_org_level + 1;
2105:
2106:
2107: -- set the parent level based on the following condition
2108: -- get the parent organization level from the per_org_structure_elements
2109: -- then apply the formula
2110: SELECT max(level)
2111: INTO v_parent_org_level
2112: FROM per_org_structure_elements

Line 2112: FROM per_org_structure_elements

2108: -- get the parent organization level from the per_org_structure_elements
2109: -- then apply the formula
2110: SELECT max(level)
2111: INTO v_parent_org_level
2112: FROM per_org_structure_elements
2113: WHERE org_structure_version_id = P_org_version_id
2114: AND organization_id_parent = v_parent_org_id
2115: CONNECT BY PRIOR organization_id_child = organization_id_parent
2116: AND org_structure_version_id = P_org_version_id

Line 2123: FROM per_org_structure_elements

2119: If v_parent_org_level is NULL then
2120:
2121: SELECT max(level)
2122: INTO v_parent_org_level
2123: FROM per_org_structure_elements
2124: WHERE org_structure_version_id = P_org_version_id
2125: AND organization_id_child = v_parent_org_id
2126: CONNECT BY PRIOR organization_id_child = organization_id_parent
2127: AND org_structure_version_id = P_org_version_id

Line 2211: FROM per_org_structure_elements

2207: SELECT distinct
2208: level plevel
2209: ,organization_id_parent
2210: ,organization_id_child
2211: FROM per_org_structure_elements
2212: WHERE org_structure_version_id = version_id
2213: CONNECT BY PRIOR organization_id_child = organization_id_parent
2214: and org_structure_version_id = version_id
2215: START WITH organization_id_parent = start_org_id

Line 2223: FROM per_org_structure_elements

2219: -- check whether the org exist in the given pa implementations ou
2220: -- and falls within the start org
2221: cursor check_org_exists(version_id NUMBER,start_org_id NUMBER) is
2222: (SELECT organization_id_child
2223: FROM per_org_structure_elements
2224: WHERE org_structure_version_id = version_id
2225: CONNECT BY PRIOR organization_id_child = organization_id_parent
2226: and org_structure_version_id = version_id
2227: START WITH organization_id_parent = start_org_id

Line 2289: FROM per_org_structure_elements

2285: specified is same as the changed Org structure */
2286:
2287: -- get maximum level for the set up
2288: SELECT max(level) into v_maximumlevel
2289: FROM per_org_structure_elements
2290: WHERE org_structure_version_id = p_org_version_id
2291: CONNECT BY PRIOR organization_id_child = organization_id_parent
2292: and org_structure_version_id = p_org_version_id
2293: START WITH organization_id_parent = v_start_org_id

Line 2484: per_org_structure_elements posg

2480: SELECT distinct
2481: imp.org_structure_version_id version_id
2482: ,imp.start_organization_id start_org_id
2483: FROM pa_implementations_all imp,
2484: per_org_structure_elements posg
2485: WHERE posg.org_structure_version_id = imp.org_structure_version_id;
2486:
2487: v_err_code NUMBER;
2488: v_err_stack VARCHAR2(200);

Line 2569: FROM per_org_structure_elements a

2565:
2566: CURSOR all_parents (max_level number) IS
2567: SELECT distinct organization_id_parent
2568: ,(max_level - level + 1) rev_level
2569: FROM per_org_structure_elements a
2570: WHERE a.org_structure_version_id = p_org_version_id
2571: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2572: AND a.org_structure_version_id = p_org_version_id
2573: START WITH a.organization_id_parent = p_organization_id_parent

Line 2613: FROM per_org_structure_elements a

2609: */
2610:
2611: SELECT MAX(level+1)
2612: INTO v_max_level
2613: FROM per_org_structure_elements a
2614: WHERE a.org_structure_version_id = p_org_version_id
2615: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2616: AND a.org_structure_version_id = p_org_version_id
2617: START WITH a.organization_id_parent = p_organization_id_parent

Line 2662: FROM per_org_structure_elements a

2658: , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2659: , c1rec.rev_level
2660: , c1rec.rev_level - level
2661: , p_org_id
2662: FROM per_org_structure_elements a
2663: WHERE a.org_structure_version_id = p_org_version_id
2664: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2665: AND a.org_structure_version_id = p_org_version_id
2666: START WITH a.organization_id_parent = c1rec.organization_id_parent

Line 2758: when a new organization is added in the hierarchy. That is added in per_org_structure_elements table.

2754:
2755: END POPULATE_HIERARCHY_DENORM2;
2756:
2757: /* Bug#2643047 - This procedure is added so as to populate organizations for REPORTING pa_org_use_type
2758: when a new organization is added in the hierarchy. That is added in per_org_structure_elements table.
2759: The call to this procedure will be made from maintain_org_hist_bri
2760: The newly added organization is x_organiation_id_child which is added under x_organization_id_parent*/
2761:
2762: Procedure populate_reporting_orgs(

Line 2978: per_org_structure_elements. */

2974:
2975: end restructure_rpt_orgs_denorm;
2976:
2977: /* Bug 3649799 - This procedure will be called by statement level trigger for update on
2978: per_org_structure_elements. */
2979:
2980: procedure maintain_org_hist_update(x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2981: x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2982: x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895

Line 2988: l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,

2984:
2985: /* This cursor is to get all the parents for the organization l_start_org_id in hierarchy l_org_struct_ver_id */
2986:
2987: CURSOR all_parents (max_level number,
2988: l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
2989: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
2990: SELECT distinct organization_id_parent
2991: ,(max_level - level + 1) rev_level
2992: FROM per_org_structure_elements a

Line 2989: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS

2985: /* This cursor is to get all the parents for the organization l_start_org_id in hierarchy l_org_struct_ver_id */
2986:
2987: CURSOR all_parents (max_level number,
2988: l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
2989: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
2990: SELECT distinct organization_id_parent
2991: ,(max_level - level + 1) rev_level
2992: FROM per_org_structure_elements a
2993: WHERE a.org_structure_version_id = l_org_struct_ver_id

Line 2992: FROM per_org_structure_elements a

2988: l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
2989: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
2990: SELECT distinct organization_id_parent
2991: ,(max_level - level + 1) rev_level
2992: FROM per_org_structure_elements a
2993: WHERE a.org_structure_version_id = l_org_struct_ver_id
2994: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2995: AND a.org_structure_version_id = l_org_struct_ver_id
2996: START WITH a.organization_id_parent = l_start_org_id

Line 3001: CURSOR proj_exp_check(c_org_struct_version_id IN per_org_structure_elements.org_structure_version_id%TYPE)

2997: AND a.org_structure_version_id = l_org_struct_ver_id
2998: ORDER by rev_level desc;
2999:
3000: /* Added this cursor for bug 4218163 */
3001: CURSOR proj_exp_check(c_org_struct_version_id IN per_org_structure_elements.org_structure_version_id%TYPE)
3002: IS
3003: SELECT 'Y' FROM pa_implementations_all
3004: WHERE proj_org_structure_version_id = c_org_struct_version_id
3005: OR exp_org_structure_version_id = c_org_struct_version_id;

Line 3007: l_version_id per_org_structure_elements.org_structure_version_id%TYPE;

3003: SELECT 'Y' FROM pa_implementations_all
3004: WHERE proj_org_structure_version_id = c_org_struct_version_id
3005: OR exp_org_structure_version_id = c_org_struct_version_id;
3006:
3007: l_version_id per_org_structure_elements.org_structure_version_id%TYPE;
3008: l_new_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3009: l_new_child_org_id per_org_structure_elements.organization_id_child%TYPE;
3010: v_max_level number;
3011: l_imp_proj_exp VARCHAR2(1);

Line 3008: l_new_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;

3004: WHERE proj_org_structure_version_id = c_org_struct_version_id
3005: OR exp_org_structure_version_id = c_org_struct_version_id;
3006:
3007: l_version_id per_org_structure_elements.org_structure_version_id%TYPE;
3008: l_new_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3009: l_new_child_org_id per_org_structure_elements.organization_id_child%TYPE;
3010: v_max_level number;
3011: l_imp_proj_exp VARCHAR2(1);
3012:

Line 3009: l_new_child_org_id per_org_structure_elements.organization_id_child%TYPE;

3005: OR exp_org_structure_version_id = c_org_struct_version_id;
3006:
3007: l_version_id per_org_structure_elements.org_structure_version_id%TYPE;
3008: l_new_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3009: l_new_child_org_id per_org_structure_elements.organization_id_child%TYPE;
3010: v_max_level number;
3011: l_imp_proj_exp VARCHAR2(1);
3012:
3013: -- Added for bug#5952671

Line 3015: cursor pa_org_hier_rec (l_org_id_parent per_org_structure_elements.organization_id_parent%TYPE,

3011: l_imp_proj_exp VARCHAR2(1);
3012:
3013: -- Added for bug#5952671
3014:
3015: cursor pa_org_hier_rec (l_org_id_parent per_org_structure_elements.organization_id_parent%TYPE,
3016: l_level number,
3017: l_version_id per_org_structure_elements.org_structure_version_id%TYPE) IS
3018: SELECT organization_id_child
3019: , l_level - level

Line 3017: l_version_id per_org_structure_elements.org_structure_version_id%TYPE) IS

3013: -- Added for bug#5952671
3014:
3015: cursor pa_org_hier_rec (l_org_id_parent per_org_structure_elements.organization_id_parent%TYPE,
3016: l_level number,
3017: l_version_id per_org_structure_elements.org_structure_version_id%TYPE) IS
3018: SELECT organization_id_child
3019: , l_level - level
3020: FROM per_org_structure_elements a
3021: WHERE a.org_structure_version_id = l_version_id

Line 3020: FROM per_org_structure_elements a

3016: l_level number,
3017: l_version_id per_org_structure_elements.org_structure_version_id%TYPE) IS
3018: SELECT organization_id_child
3019: , l_level - level
3020: FROM per_org_structure_elements a
3021: WHERE a.org_structure_version_id = l_version_id
3022: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3023: AND a.org_structure_version_id = l_version_id
3024: START WITH a.organization_id_parent = l_org_id_parent

Line 3027: cursor pa_org_hier_sec_rec (l_version_id per_org_structure_elements.org_structure_version_id%TYPE,

3023: AND a.org_structure_version_id = l_version_id
3024: START WITH a.organization_id_parent = l_org_id_parent
3025: AND a.org_structure_version_id = l_version_id;
3026:
3027: cursor pa_org_hier_sec_rec (l_version_id per_org_structure_elements.org_structure_version_id%TYPE,
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

Line 3028: l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE,

3024: START WITH a.organization_id_parent = l_org_id_parent
3025: AND a.org_structure_version_id = l_version_id;
3026:
3027: cursor pa_org_hier_sec_rec (l_version_id per_org_structure_elements.org_structure_version_id%TYPE,
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

Line 3054: per_org_structure_elements.org_structure_version_id%TYPE index by binary_integer;

3050: -- End for bug#5952671
3051:
3052: --Start 5934849
3053: TYPE l_str_version_id_tbltype IS TABLE OF
3054: per_org_structure_elements.org_structure_version_id%TYPE index by binary_integer;
3055:
3056: L_STR_VERSION_ID_TMP l_str_version_id_tbltype;
3057: L_STR_VERSION_ID l_str_version_id_tbltype;
3058: l_str_exists varchar2(1) :='N';

Line 3068: from per_org_structure_elements

3064: --for the rowids in newRows tab
3065: FOR k in pa_org_utils.newRows.first..pa_org_utils.newRows.last Loop
3066: select distinct org_structure_version_id
3067: into L_STR_VERSION_ID_TMP(L_STR_VERSION_ID_TMP.count+1)
3068: from per_org_structure_elements
3069: where
3070: ROWID = pa_org_utils.newRows(k);
3071: End loop;
3072:

Line 3102: FROM per_org_structure_elements

3098: /* Fetching the Organization Hierarchy version id, the new parent organization id and child organization id
3099:
3100: SELECT org_structure_version_id, organization_id_parent, organization_id_child
3101: INTO l_version_id, l_new_parent_org_id, l_new_child_org_id
3102: FROM per_org_structure_elements
3103: WHERE ROWID = pa_org_utils.newRows(i); */
3104: maintain_pa_all_org( x_org_version_id => l_version_id,
3105: x_err_code => x_err_code,
3106: x_err_stage => x_err_stage,

Line 3170: FROM per_org_structure_elements a

3166: /* End bug#5952671 */
3167:
3168: SELECT MAX(level+1)
3169: INTO v_max_level
3170: FROM per_org_structure_elements a
3171: WHERE a.org_structure_version_id = l_version_id
3172: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3173: AND a.org_structure_version_id = l_version_id
3174: START WITH a.organization_id_parent = imp_rec.start_organization_id

Line 3344: FROM per_org_structure_elements a

3340: , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3341: , c1rec.rev_level
3342: , c1rec.rev_level - level
3343: , imp_rec.org_id
3344: FROM per_org_structure_elements a
3345: WHERE a.org_structure_version_id = l_version_id
3346: CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3347: AND a.org_structure_version_id = l_version_id
3348: START WITH a.organization_id_parent = c1rec.organization_id_parent

Line 3418: l_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;

3414: x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
3415: l_imp_proj_exp VARCHAR2(1);
3416: l_exist_recs VARCHAR2(1);
3417: class_org_true varchar2(3);
3418: l_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3419: l_child_org_id per_org_structure_elements.organization_id_parent%TYPE;
3420:
3421: Cursor start_orgs (c_version_id in number) IS
3422: SELECT decode(p_org_use_type, 'PROJECTS', proj_start_org_id, 'EXPENDITURES', exp_start_org_id) start_organization_id

Line 3419: l_child_org_id per_org_structure_elements.organization_id_parent%TYPE;

3415: l_imp_proj_exp VARCHAR2(1);
3416: l_exist_recs VARCHAR2(1);
3417: class_org_true varchar2(3);
3418: l_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3419: l_child_org_id per_org_structure_elements.organization_id_parent%TYPE;
3420:
3421: Cursor start_orgs (c_version_id in number) IS
3422: SELECT decode(p_org_use_type, 'PROJECTS', proj_start_org_id, 'EXPENDITURES', exp_start_org_id) start_organization_id
3423: FROM pa_implementations_all imp

Line 3428: CURSOR c_get_new_org ( c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,

3424: WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
3425: 'EXPENDITURES', exp_org_structure_version_id) = c_version_id;
3426:
3427:
3428: CURSOR c_get_new_org ( c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3429: c_start_org_id IN per_org_structure_elements.organization_id_child%TYPE)
3430: IS
3431: SELECT c_start_org_id FROM dual
3432: UNION ALL

Line 3429: c_start_org_id IN per_org_structure_elements.organization_id_child%TYPE)

3425: 'EXPENDITURES', exp_org_structure_version_id) = c_version_id;
3426:
3427:
3428: CURSOR c_get_new_org ( c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3429: c_start_org_id IN per_org_structure_elements.organization_id_child%TYPE)
3430: IS
3431: SELECT c_start_org_id FROM dual
3432: UNION ALL
3433: SELECT organization_id_child

Line 3434: FROM per_org_structure_elements

3430: IS
3431: SELECT c_start_org_id FROM dual
3432: UNION ALL
3433: SELECT organization_id_child
3434: FROM per_org_structure_elements
3435: WHERE org_structure_version_id = c_org_struct_ver_id
3436: CONNECT BY PRIOR organization_id_child = organization_id_parent
3437: AND org_structure_version_id = c_org_struct_ver_id
3438: START WITH organization_id_parent = c_start_org_id

Line 3441: CURSOR c_get_sub_orgs (c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,

3437: AND org_structure_version_id = c_org_struct_ver_id
3438: START WITH organization_id_parent = c_start_org_id
3439: AND org_structure_version_id = c_org_struct_ver_id;
3440:
3441: CURSOR c_get_sub_orgs (c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3442: c_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
3443: SELECT c_org_id
3444: FROM dual
3445: UNION ALL

Line 3442: c_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS

3438: START WITH organization_id_parent = c_start_org_id
3439: AND org_structure_version_id = c_org_struct_ver_id;
3440:
3441: CURSOR c_get_sub_orgs (c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3442: c_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
3443: SELECT c_org_id
3444: FROM dual
3445: UNION ALL
3446: SELECT organization_id_child

Line 3447: FROM per_org_structure_elements

3443: SELECT c_org_id
3444: FROM dual
3445: UNION ALL
3446: SELECT organization_id_child
3447: FROM per_org_structure_elements
3448: WHERE org_structure_version_id = c_org_struct_ver_id
3449: CONNECT BY PRIOR organization_id_child = organization_id_parent
3450: AND org_structure_version_id = c_org_struct_ver_id
3451: START WITH organization_id_parent = c_org_id

Line 3540: FROM per_org_structure_elements se

3536: v_org_id number;
3537:
3538: CURSOR cur_proj_new_org IS
3539: SELECT se.organization_id_child organization_id
3540: FROM per_org_structure_elements se
3541: WHERE org_structure_version_id = x_org_version_id
3542: AND EXISTS (select 'X'
3543: from hr_organization_information info
3544: where info.organization_id = se.organization_id_child

Line 3564: FROM per_org_structure_elements se

3560: and info.organization_id = v_start_org_id);
3561:
3562: CURSOR cur_exp_new_org IS
3563: SELECT se.organization_id_child organization_id
3564: FROM per_org_structure_elements se
3565: WHERE org_structure_version_id = x_org_version_id
3566: AND EXISTS (select 'X'
3567: from hr_organization_information info
3568: where info.organization_id = se.organization_id_child