1 PACKAGE BODY pa_org_utils AS
2 /* $Header: PAXORUTB.pls 120.17 2011/11/14 10:41:11 djambhek ship $ */
3
4 -- Start CC Change
5
6 FUNCTION get_org_level(
7 p_org_version_id in number,
8 p_child_parent_org_id in number,
9 p_start_org_id in number
10 )
11 RETURN NUMBER
12 IS
13 l_tmp_level number;
14 l_org_level number;
15 BEGIN
16
17 /*
18
19 *************Commented this code for bug#2645200
20
21 if ( p_start_org_id = p_child_parent_org_id ) then
22
23 l_org_level := 1;
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
33 START WITH organization_id_parent = p_start_org_id
34 AND org_structure_version_id = p_org_version_id;
35
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
45 START WITH organization_id_parent = p_start_org_id
46 AND org_structure_version_id = p_org_version_id;
47
48 --- set the org level based on the formula
49 l_org_level := l_tmp_level + 1;
50
51 else
52
53 l_org_level := l_tmp_level ;
54
55 end if;
56
57 end if;
58
59 End of Code Commented */
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';
69
70 RETURN (l_org_level);
71
72 END;
73
74 FUNCTION get_max_org_level(
75 p_org_version_id in number,
76 p_start_org_id in number
77 )
78 RETURN NUMBER
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
88 and org_structure_version_id = p_org_version_id;
89
90 RETURN (l_max_org_level);
91 END;
92
93 FUNCTION get_start_org_id_sch(
94 p_org_version_id in number
95 )
96 RETURN NUMBER
97 IS
98 l_start_org_id number;
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)
108 AND rownum = 1;
109
110 RETURN (l_start_org_id);
111 END;
112
113
114 -- End CC Change
115 FUNCTION get_org_version_id(x_usage in varchar2) RETURN NUMBER
116 IS
117 x_org_version_id number;
118 BEGIN
119 select decode(x_usage,'PROJECTS',PROJ_ORG_STRUCTURE_VERSION_ID
120 ,'EXPENDITURES',EXP_ORG_STRUCTURE_VERSION_ID
121 ,'REPORTING',ORG_STRUCTURE_VERSION_ID)
122 into x_org_version_id
123 from pa_implementations;
124 RETURN (x_org_version_id);
125 END;
126
127 /* 1333116 Added this function to return the org hierarchy version and to
128 handle burdening hierarchy */
129 FUNCTION get_org_version_id2(x_usage in varchar2) RETURN NUMBER
130 IS
131 x_org_version_id number;
132 BEGIN
133 select decode(x_usage,'PROJECTS',PROJ_ORG_STRUCTURE_VERSION_ID
134 ,'EXPENDITURES',EXP_ORG_STRUCTURE_VERSION_ID
135 ,'REPORTING',ORG_STRUCTURE_VERSION_ID
136 ,'BURDENING',to_number(org_information2))
137 into x_org_version_id
138 from pa_implementations imp,hr_organization_information hr
139 where imp.business_group_id = hr.organization_id
140 and hr.org_information_context = 'Project Burdening Hierarchy';
141 RETURN (x_org_version_id);
142 EXCEPTION when no_data_found then
143 return NULL;
144 END;
145
146 FUNCTION get_start_org_id(x_usage in varchar2) RETURN NUMBER
147 IS
148 x_start_org_id number;
149 BEGIN
150 select decode(x_usage,'PROJECTS',PROJ_START_ORG_ID
151 ,'EXPENDITURES',EXP_START_ORG_ID
152 ,'REPORTING',START_ORGANIZATION_ID)
153 into x_start_org_id
154 from pa_implementations;
155 RETURN (x_start_org_id);
156 END;
157
158 /* 1333116 Added this function to return the org hierarchy
159 start organization and to handle burdening hierarchy */
160 FUNCTION get_start_org_id2(x_usage in varchar2) RETURN NUMBER
161 IS
162 x_start_org_id number;
163 BEGIN
164 if(x_usage <> 'BURDENING') then
165 select decode(x_usage,'PROJECTS',PROJ_START_ORG_ID
166 ,'EXPENDITURES',EXP_START_ORG_ID
167 ,'REPORTING',START_ORGANIZATION_ID)
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
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
181 and c.org_information_context = 'Project Burdening Hierarchy' ;
182 end if;
183 RETURN (x_start_org_id);
184 EXCEPTION when no_data_found then
185 return NULL;
186 END;
187
188 Procedure maintain_org_hist_imp(x_org_id in number,
189 x_old_proj_org_version_id in number,
190 x_new_proj_org_version_id in number,
191 x_old_exp_org_version_id in number,
192 x_new_exp_org_version_id in number,
193 x_old_org_structure_version_id in number,
194 x_new_org_structure_version_id in number,
195 x_old_proj_start_org_id in number,
196 x_new_proj_start_org_id in number,
197 x_old_exp_start_org_id in number,
198 x_new_exp_start_org_id in number,
199 x_old_start_organization_id in number,
200 x_new_start_organization_id in number,
201 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
202 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
203 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
204 IS
205 old_stack varchar2(630);
206
207 /* Bug 3795903 - Added parameters to the cursor c_get_new_org */
208 CURSOR c_get_new_org (l_new_org_version_id IN NUMBER, l_new_start_org_id IN NUMBER) IS
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
218 AND org_structure_version_id = l_new_org_version_id;
219
220 /* Bug 3795903 - Added parameters to the cursor c_get_sub_orgs */
221
222 CURSOR c_get_sub_orgs (l_org_id IN NUMBER , l_new_org_version_id IN NUMBER) IS
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
232 AND org_structure_version_id = l_new_org_version_id;
233
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
243 and org_structure_version_id = l_old_org_version_id
244 union
245 select l_old_org_start_id from sys.dual)
246 intersect
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
256 WHERE ( proj_org_structure_version_id = c_org_struct_version_id
257 OR exp_org_structure_version_id = c_org_struct_version_id )
258 and ORG_ID <> l_org_id;
259
260
261 exp_org_true varchar2(3);
262 proj_org_true varchar2(3);
263 rebuild_tab varchar2(1) := 'N';
264 l_parent_org_id number;
265 l_child_org_id number;
266 l_imp_proj_exp VARCHAR2(1); -- Added for bug 4232613.
267 -- Start CC Change
268
269 l_tmp_parent_org_id number;
270 l_tmp_child_org_id number;
271 l_tmp_chr varchar2(1);
272
273 -- End CC Change
274
275 -- Start CC Change
276
277 -- End CC Change
278
279 TYPE num1_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; --added for bug 6444664
280 l_child_org_id_var num1_tbl_type; --added for bug 6444664
281
282
283 Begin
284 x_err_code := 0;
285 old_stack := x_err_stack;
286 x_err_stack := x_err_stack || '->Pa_org_utils.maintain_org_history';
287
288 /* Project Orgs */
289 if ( (nvl(x_old_proj_org_version_id,x_new_proj_org_version_id +1) <> x_new_proj_org_version_id )
290 or
291 (nvl(x_old_proj_start_org_id,x_new_proj_start_org_id +1) <> x_new_proj_start_org_id )
292 ) then
293
294 insert into pa_all_organizations
295 (organization_id,
296 org_id,
297 pa_org_use_type )
298 ( (select se.organization_id_child
299 ,x_org_id
300 ,'PROJECTS'
301 from per_org_structure_elements se
302 where org_structure_version_id = x_new_proj_org_version_id
303 AND EXISTS (select 'X'
304 from hr_organization_information info
305 where info.organization_id = se.organization_id_child
306 and info.org_information1 = 'PA_PROJECT_ORG'
307 and info.org_information_context||'' = 'CLASS'
308 and info.org_information2 = 'Y')
309 connect by prior se.organization_id_child =
310 se.organization_id_parent
311 and org_structure_version_id = x_new_proj_org_version_id
312 start with se.organization_id_parent = x_new_proj_start_org_id
313 and org_structure_version_id = x_new_proj_org_version_id
314 union
315 select x_new_proj_start_org_id
316 ,x_org_id
317 ,'PROJECTS'
318 from sys.duaL /* Exists clause added for bug#2591146 */
319 where EXISTS (select 'X'
320 from hr_organization_information info
321 where info.organization_id = x_new_proj_start_org_id
322 and info.org_information1 = 'PA_PROJECT_ORG'
323 and info.org_information_context||'' = 'CLASS'
324 and info.org_information2 = 'Y')
325 )
326 minus
327 (select organization_id,
328 org_id,
329 pa_org_use_type
330 from pa_all_organizations
331 where pa_org_use_type = 'PROJECTS'
332 and org_id = x_org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
333 )
334 );
335 end if;
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
345
346 open check_org_exists(x_old_proj_org_version_id ,x_old_proj_start_org_id,x_new_proj_start_org_id);
347 fetch check_org_exists into l_parent_org_id;
348 if check_org_exists%notfound then
349 rebuild_tab := 'Y';
350 end if;
351 close check_org_exists; /* Added for bug 1736069 */
352 end if;
353
354 if (nvl(x_old_proj_org_version_id,x_new_proj_org_version_id +1) <> x_new_proj_org_version_id ) or rebuild_tab = 'Y' then
355
356 --- org hierarchy is changed or version is changed
357 -- Bug 3795903- Modified the call to c_get_new_org by putting x_new_proj_start_org_id in the call
358 open c_get_new_org(x_new_proj_org_version_id,x_new_proj_start_org_id);
359 loop
360 fetch c_get_new_org into l_parent_org_id;
361 exit when c_get_new_org%notfound;
362
363 -- Bug 3795903- Modified the call to c_get_sub_orgs by putting x_new_proj_org_version_id in the call
364 open c_get_sub_orgs(l_parent_org_id ,x_new_proj_org_version_id);
365 loop
366 --fetch c_get_sub_orgs into l_child_org_id;
367 fetch c_get_sub_orgs BULK COLLECT into l_child_org_id_var LIMIT 10000; --bug6444664
368 exit when l_child_org_id_var.count = 0;--8915976
369 /*bug6444664
370 begin
371 select 'YES' into proj_org_true
372 from hr_organization_information
373 where organization_id = l_child_org_id
374 and ORG_INFORMATION_CONTEXT = 'CLASS'
375 and ORG_INFORMATION1 = 'PA_PROJECT_ORG'
376 and ORG_INFORMATION2 = 'Y';
377 exception when no_data_found then
378 proj_org_true := 'NO';
379 end ;
380
381 if proj_org_true = 'YES' then
382
383 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id=> l_parent_org_id
384 , p_child_organization_id => l_child_org_id
385 , p_org_hierarchy_version_id=> x_new_proj_org_version_id
386 , p_pa_org_use_type => 'PROJECTS'
387 , x_err_code => x_err_code
388 , x_err_stage => x_err_stage
389 , x_err_stack => x_err_stack);
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
399 , created_by
400 , last_update_date
401 , last_updated_by
402 , last_update_login
403 )
404 SELECT l_parent_org_id
405 , l_child_org_id_var(i)
406 , x_new_proj_org_version_id
407 , 'PROJECTS'
408 , sysdate
409 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
410 , sysdate
411 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
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)
421 AND EXISTS (select 'X'
422 from hr_organization_information
423 where organization_id = l_child_org_id_var(i)
424 and org_information1 = 'PA_PROJECT_ORG'
425 and org_information_context = 'CLASS'
426 and org_information2 = 'Y');
427 END IF;
428 l_child_org_id_var.DELETE; --bug6444664
429 end loop;
430 close c_get_sub_orgs;
431 end loop;
432 close c_get_new_org;
433
434 end if;
435
436
437
438 /* Exp orgs */
439 if ( (nvl(x_old_exp_org_version_id,x_new_exp_org_version_id +1) <> x_new_exp_org_version_id )
440 or
441 (nvl(x_old_exp_start_org_id,x_new_exp_start_org_id +1) <> x_new_exp_start_org_id )
442 ) then
443
444 insert into pa_all_organizations
445 (organization_id,
446 org_id,
447 pa_org_use_type)
448 ( (select se.organization_id_child
449 ,x_org_id
450 ,'EXPENDITURES'
451 from per_org_structure_elements se
452 where org_structure_version_id = x_new_exp_org_version_id
453 AND EXISTS (select 'X'
454 from hr_organization_information info
455 where info.organization_id = se.organization_id_child
456 and info.org_information1 = 'PA_EXPENDITURE_ORG'
457 and info.org_information_context||'' = 'CLASS'
458 and info.org_information2 = 'Y')
459 connect by prior se.organization_id_child =
460 se.organization_id_parent
461 and org_structure_version_id = x_new_exp_org_version_id
462 start with se.organization_id_parent = x_new_exp_start_org_id
463 and org_structure_version_id = x_new_exp_org_version_id
464 union
465 select x_new_exp_start_org_id
466 ,x_org_id
467 ,'EXPENDITURES'
468 from sys.duaL /* Exists clause added for bug#2591146 */
469 where EXISTS (select 'X'
470 from hr_organization_information info
471 where info.organization_id = x_new_exp_start_org_id
472 and info.org_information1 = 'PA_EXPENDITURE_ORG'
473 and info.org_information_context||'' = 'CLASS'
474 and info.org_information2 = 'Y')
475 )
476 minus
477 (select organization_id,
478 org_id,
479 pa_org_use_type
480 from pa_all_organizations
481 where pa_org_use_type = 'EXPENDITURES'
482 and org_id = x_org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
483 )
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
493 open check_org_exists(x_old_exp_org_version_id ,x_old_exp_start_org_id,x_new_exp_start_org_id);
494
495 fetch check_org_exists into l_parent_org_id;
496 if check_org_exists%notfound then
497 rebuild_tab := 'Y';
498 end if;
499 close check_org_exists; /* Added for bug 1736069 */
500 end if;
501
502 if (nvl(x_old_exp_org_version_id,x_new_exp_org_version_id +1) <> x_new_exp_org_version_id ) or rebuild_tab = 'Y' then
503 --- org hierarchy is changed or version is changed
504
505 --- Bug 3795903- Modified the call to c_get_new_org
506 open c_get_new_org(x_new_exp_org_version_id,x_new_exp_start_org_id);
507 loop
508 fetch c_get_new_org into l_parent_org_id;
509 exit when c_get_new_org%notfound;
510
511 --- Bug 3795903- Modified the call to c_get_sub_orgs
512 open c_get_sub_orgs(l_parent_org_id,x_new_exp_org_version_id);
513 loop
514 fetch c_get_sub_orgs BULK COLLECT into l_child_org_id_var LIMIT 10000; --bug6444664
515 exit when l_child_org_id_var.count = 0;--8915976
516 /*
517 begin
518 select 'YES' into exp_org_true
519 from hr_organization_information
520 where organization_id = l_child_org_id
521 and ORG_INFORMATION_CONTEXT = 'CLASS'
522 and ORG_INFORMATION1 = 'PA_EXPENDITURE_ORG'
523 and ORG_INFORMATION2 = 'Y';
524 exception when no_data_found then
525 exp_org_true := 'NO';
526 end ;
527
528 if exp_org_true = 'YES' then
529
530 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id=> l_parent_org_id
531 , p_child_organization_id => l_child_org_id
532 , p_org_hierarchy_version_id=> x_new_exp_org_version_id
533 , p_pa_org_use_type => 'EXPENDITURES'
534 , x_err_code => x_err_code
535 , x_err_stage => x_err_stage
536 , x_err_stack => x_err_stack);
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
546 , created_by
547 , last_update_date
548 , last_updated_by
549 , last_update_login
550 )
551 SELECT l_parent_org_id
552 , l_child_org_id_var(i)
553 , x_new_exp_org_version_id
554 , 'EXPENDITURES'
555 , sysdate
556 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
557 , sysdate
558 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
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)
568 AND EXISTS (select 'X'
569 from hr_organization_information
570 where organization_id = l_child_org_id_var(i)
571 and org_information1 = 'PA_EXPENDITURE_ORG'
572 and org_information_context = 'CLASS'
573 and org_information2 = 'Y');
574 END IF;
575 l_child_org_id_var.DELETE; --bug6444664
576 end loop;
577 close c_get_sub_orgs;
578 end loop;
579 close c_get_new_org;
580
581 end if;
582
583 /* Reporting Orgs */
584 If ( (nvl(x_old_org_structure_version_id,x_new_org_structure_version_id ) <>
585 x_new_org_structure_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
595 pa_org_utils.populate_hierarchy_denorm2
596 ( p_org_version_id => x_new_org_structure_version_id
597 ,p_organization_id_parent=> x_new_start_organization_id
598 ,p_organization_id_child => 1
599 ,p_org_id => x_org_id /* Added this condition for bug 2976953 */
600 ,x_err_code => x_err_code
601 ,x_err_stage => x_err_stage
602 ,x_err_stack => x_err_stack
603 );
604
605 End If;
606
607 -- Start CC Change
608
609 /* Schedule line Traverse Org Hierarchy */
610
611 -- sri_msg(' START ................. ');
612
613 /* Bug#2723330 - Commenting the rebuild_tab = 'Y' Condition as TP_SCHEDULE is maintained
614 for full hierarchy in HR and will not be affected by movind start org in implementation options */
615
616 if (nvl(x_old_proj_org_version_id,x_new_proj_org_version_id +1)
617 <> x_new_proj_org_version_id) /* or rebuild_tab = 'Y' For Bug#2723330*/
618 then
619
620 /* Bug 4232613. Added the check before calling populate_hier_denorm_sch.
621 If the org version id is already used in the implementation option then the API
622 need not be called. */
623 OPEN proj_exp_check(x_new_proj_org_version_id,x_org_id);
624 FETCH proj_exp_check INTO l_imp_proj_exp;
625 IF proj_exp_check%NOTFOUND THEN
626 populate_hier_denorm_sch( p_org_version_id => x_new_proj_org_version_id
627 ,x_err_code => x_err_code
628 ,x_err_stage => x_err_stage
629 ,x_err_stack => x_err_stack );
630 END IF;
631 close proj_exp_check;
632
633 end if;
634
635 if (nvl(x_old_exp_org_version_id,x_new_exp_org_version_id +1) <>
636 x_new_exp_org_version_id ) /* or rebuild_tab = 'Y' For Bug#2723330 */
637 then
638
639 /* Bug 4232613. Added the check before calling populate_hier_denorm_sch.
640 If the org version id is already used in the implementation option then the API
641 need not be called. */
642 OPEN proj_exp_check(x_new_exp_org_version_id,x_org_id);
643 FETCH proj_exp_check INTO l_imp_proj_exp;
644 IF proj_exp_check%NOTFOUND THEN
645 populate_hier_denorm_sch( p_org_version_id => x_new_exp_org_version_id
646 ,x_err_code => x_err_code
647 ,x_err_stage => x_err_stage
648 ,x_err_stack => x_err_stack );
649 END IF;
650 close proj_exp_check;
651
652 end if;
653
654 -- End CC Change
655
656 x_err_stack := old_stack;
657
658 Exception
659 when others then
660 x_err_code := SQLCODE;
661 x_err_stage := SQLERRM;
662 return;
663 End;
664
665 -- Start CC Change
666
667 Procedure populate_hier_denorm_sch ( p_org_version_id in number,
668 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
669 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
670 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
671
672 l_dummy_level number := 0;
673
674 CURSOR c_get_new_org_sch(lp_org_version_id IN NUMBER,
675 lp_start_org_id IN NUMBER ) IS
676 SELECT l_dummy_level,lp_start_org_id
677 FROM dual
678 UNION ALL
679 SELECT level,organization_id_child
680 FROM per_org_structure_elements
681 WHERE org_structure_version_id = lp_org_version_id
682 CONNECT BY PRIOR organization_id_child = organization_id_parent
683 AND org_structure_version_id = lp_org_version_id
684 START WITH organization_id_parent = lp_start_org_id
685 AND org_structure_version_id = lp_org_version_id;
686
687 CURSOR c_get_sub_orgs_sch (lp_org_version_id IN NUMBER,
688 lp_org_id IN NUMBER) IS
689 SELECT l_dummy_level,lp_org_id
690 FROM dual
691 UNION ALL
692 SELECT level,organization_id_child
693 FROM per_org_structure_elements
694 WHERE org_structure_version_id = lp_org_version_id
695 CONNECT BY PRIOR organization_id_child = organization_id_parent
696 AND org_structure_version_id = lp_org_version_id
697 START WITH organization_id_parent = lp_org_id
698 AND org_structure_version_id = lp_org_version_id;
699
700 /* Commented this cursor for bug#2723330 as this cursor is not used now
701
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 */
711
712 -- End CC Change
713
714 -- Start CC Change
715
716 l_tmp_parent_org_id number;
717 l_tmp_child_org_id number;
718 l_tmp_chr varchar2(1);
719
720 -- End CC Change
721
722 -- Start CC Change
723
724 l_start_org_id number;
725 l_plevel number;
726 l_clevel number;
727 l_parent_level number;
728 l_child_level number;
729
730 -- End CC Change
731 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; --added for bug6444664
732 l_child_level_var number_tbl_type; --added for bug6444664
733 l_tmp_child_org_id_var number_tbl_type; --added for bug6444664
734 l_pa_org_use_type VARCHAR2(30); --added for bug6444664
735 BEGIN
736
737 /*
738
739 ********** Commented for bug#2723330
740 ********** open check_org_ver_not_exists ( p_org_version_id );
741 ********** LOOP
742 ********** fetch check_org_ver_not_exists into l_tmp_chr;
743 ********** exit when check_org_ver_not_exists%notfound;
744 **********
745
746 */
747
748 l_start_org_id := get_start_org_id_sch(p_org_version_id);
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
758 Null;
759 END;
760
761 /* End of code for bug#2723330 */
762
763 open c_get_new_org_sch ( p_org_version_id ,
764 l_start_org_id );
765
766 LOOP
767 fetch c_get_new_org_sch into l_parent_level, l_tmp_parent_org_id;
768 exit when c_get_new_org_sch%notfound;
769
770 l_plevel := l_parent_level +1 ;
771
772 open c_get_sub_orgs_sch ( p_org_version_id ,
773 l_tmp_parent_org_id );
774 --Performance improvement for Bug8395942 Begins Here
775 -- LOOP
776
777 --fetch c_get_sub_orgs_sch into l_child_level, l_tmp_child_org_id;
778 fetch c_get_sub_orgs_sch BULK COLLECT into l_child_level_var, l_tmp_child_org_id_var LIMIT 10000; --bug6444664
779 exit when l_child_level_var.count = 0;--8915976
780 close c_get_sub_orgs_sch;
781 -- l_clevel := l_child_level + l_parent_level +1 ;
782
783 /*pa_org_utils.Create_org_hier_denorm_levels
784 (p_parent_organization_id=> l_tmp_parent_org_id
785 , p_child_organization_id => l_tmp_child_org_id
786 , p_org_hierarchy_version_id=> p_org_version_id
787 , p_pa_org_use_type => 'TP_SCHEDULE'
788 , p_parent_level => l_plevel
789 , p_child_level => l_clevel
790 , x_err_code => x_err_code
791 , x_err_stage => x_err_stage
792 , x_err_stack => x_err_stack); */
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
802 , created_by
803 , last_update_date
804 , last_updated_by
805 , last_update_login
806 , parent_level
807 , child_level
808 )
809 SELECT l_tmp_parent_org_id
810 , l_tmp_child_org_id_var(i)
811 , p_org_version_id
812 , 'TP_SCHEDULE'
813 , sysdate
814 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
815 , sysdate
816 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
817 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
818 , l_plevel
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);
828 END IF;
829 l_child_level_var.DELETE;
830 l_tmp_child_org_id_var.DELETE;
831
832
833 -- END LOOP; /* end loop for cursor c_get_sub_orgs_sch */
834
835 --Performance improvement for Bug8395942 Ends Here
836
837 END LOOP; /* end loop for cursor c_get_new_org_sch */
838
839 close c_get_new_org_sch;
840
841 /*
842
843 ********** Commented for bug#2723330
844
845 ********* END LOOP; end loop for cursor check_org_ver_not_exists
846
847 ********** close check_org_ver_not_exists;
848
849 */
850
851 Exception
852 when others then
853 x_err_code := SQLCODE;
854 x_err_stage := SQLERRM;
855 return;
856 END;
857
858 Procedure maintain_org_hist_bri(x_org_version_id in number,
859 x_organization_id_child in number,
860 x_organization_id_parent in number,
861 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
862 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
863 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
864 v_start_org_id number;
865 v_org_child_id number;
866 l_parent_org_id number;
867 l_exp_org_true varchar2(1);
868 l_proj_org_true varchar2(1);
869 org_exists_flag boolean; /* Added for the bug 2139709 */
870
871 /*
872 Bug#2620151- Commented the old cursor and added new logic for the cursor check_org_exists
873
874 New Logic: Traverse from x_organization_id_parent and get all the parents for this, union
875 with x_organization_id_parent.
876 In this result, check for v_start_org_id, if present then it means that the org
877 x_organization_id_parent is under v_start_org_id.
878
879 Earlier Logic: Traverse from v_start_org_d and get all the childs for this, union with
880 v_start_org_id.
881 In this result, check for x_organization_id_parent, if present then it means
882 that the org x_organization_id_parent is under v_start_org_id.
883 */
884
885 cursor check_org_exists is
886 (
887 select se.organization_id_parent
888 from per_org_structure_elements se
889 where se.org_structure_version_id = x_org_version_id
890 connect by prior se.organization_id_parent =
891 se.organization_id_child
892 and org_structure_version_id = x_org_version_id
893 start with se.organization_id_child =
894 x_organization_id_parent
895 and org_structure_version_id = x_org_version_id
896 union
897 select x_organization_id_parent from sys.dual
898 )
899 intersect
900 (
901 select v_start_org_id from sys.dual
902 );
903
904 /* Commented for Bug#2620151 Added cursor above
905 cursor check_org_exists is
906 (select se.organization_id_child
907 from per_org_structure_elements se
908 where se.org_structure_version_id =
909 x_org_version_id
910 connect by prior se.organization_id_child =
911 se.organization_id_parent
912 and org_structure_version_id = x_org_version_id
913 start with se.organization_id_parent =
914 v_start_org_id
915 and org_structure_version_id = x_org_version_id
916 union
917 select v_start_org_id
918 from sys.dual)
919 intersect
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
929 SELECT organization_id_parent
930 FROM per_org_structure_elements
931 WHERE org_structure_version_id = x_org_version_id
932 CONNECT BY PRIOR organization_id_parent= organization_id_child
933 AND org_structure_version_id = x_org_version_id
934 START WITH organization_id_child = x_organization_id_parent
935 AND org_structure_version_id = x_org_version_id) /* Bug#2643047, Added this condition as we have to query only for this org_structure_version_id */
936 union ALL
937 (SELECT x_organization_id_child from dual);
938
939 -- Start CC Change
940
941 cursor get_all_orgs_sch(
942 lp_organization_id_parent in number,
943 lp_organization_id_child in number,
944 lp_org_version_id in number ) is
945 (SELECT lp_organization_id_parent
946 FROM dual
947 UNION
948 SELECT organization_id_parent
949 FROM per_org_structure_elements
950 WHERE org_structure_version_id = lp_org_version_id
951 CONNECT BY PRIOR organization_id_parent= organization_id_child
952 AND org_structure_version_id = lp_org_version_id
953 START WITH organization_id_child = lp_organization_id_parent
954 AND org_structure_version_id = lp_org_version_id)
955 union ALL
956 (SELECT lp_organization_id_child from dual);
957
958 l_plevel number;
959 l_clevel number;
960 l_start_org_id number;
961 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; --bug6444664
962 l_parent_org_id_var num_tbl_type; --bug6444664
963 /*Bug# 2247737*/
964 l_process_schedule_hier varchar2(1);
965 /*End bug# 2247737*/
966
967 -- End CC Change
968 l_parent_exists varchar2(1) := 'Y'; -- Bug 12618575
969
970 Begin
971
972 /*Bug# 2247737*/
973 l_process_schedule_hier := 'N';
974 /*End bug# 2247737*/
975
976 /* For all operating units where the Project Org structure
977 specified is same as the changed Org structure */
978 for imp_rec in ( select org_id,proj_start_org_id
979 from pa_implementations_all imp
980 where proj_org_structure_version_id = x_org_version_id
981 )
982
983 loop
984
985 /*Bug# 2247737*/
986 l_process_schedule_hier := 'Y';
987 /*End bug# 2247737*/
988
989 v_start_org_id := imp_rec.proj_start_org_id;
990
991 /* Bug 2139709 Begin */
992 org_exists_flag := FALSE;
993
994 IF v_start_org_id = x_organization_id_parent THEN
995 org_exists_flag := TRUE;
996 ELSE
997 open check_org_exists;
998 fetch check_org_exists into v_org_child_id;
999 if check_org_exists%found then /* Added org is below
1000 the start project org */
1001 org_exists_flag := TRUE;
1002 else
1003 org_exists_flag := FALSE;
1004 end if;
1005 close check_org_exists;
1006 END IF;
1007
1008 IF org_exists_flag = TRUE THEN
1009 /* Bug 2139709 End */
1010
1011 insert into pa_all_organizations
1012 (organization_id,
1013 org_id,
1014 pa_org_use_type)
1015 (select
1016 x_organization_id_child,
1017 imp_rec.org_id,
1018 'PROJECTS'
1019 from sys.dual
1020 where exists (select 'x'
1021 from hr_organization_information info
1022 where info.organization_id = x_organization_id_child
1023 and info.org_information1 = 'PA_PROJECT_ORG'
1024 and info.org_information_context||'' = 'CLASS'
1025 and info.org_information2 = 'Y')
1026 and not exists
1027 (select 'X'
1028 from pa_all_organizations
1029 where organization_id = x_organization_id_child
1030 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1031 and pa_org_use_type = 'PROJECTS')
1032 ) ;
1033
1034 /* Added for bug 1550990 */
1035 if(sql%rowcount=0) then
1036 Update pa_all_organizations
1037 set inactive_date = NULL
1038 where organization_id = x_organization_id_child
1039 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1040 and pa_org_use_type = 'PROJECTS'
1041 and exists (select 'x' -- EXISTS condition added for bug 2890516
1042 from hr_organization_information info
1043 where info.organization_id = x_organization_id_child
1044 and info.org_information1 = 'PA_PROJECT_ORG'
1045 and info.org_information_context||'' = 'CLASS'
1046 and info.org_information2 = 'Y');
1047
1048 end if ;
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; */
1058
1059 begin
1060 select 'Y' into l_proj_org_true
1061 from hr_organization_information info
1062 where info.organization_id = x_organization_id_child
1063 and info.org_information1 = 'PA_PROJECT_ORG'
1064 and info.org_information_context||'' = 'CLASS'
1065 and info.org_information2 = 'Y';
1066 exception when no_data_found then
1067 l_proj_org_true := 'N';
1068 end;
1069 open get_all_orgs;
1070 loop
1071 fetch get_all_orgs BULK COLLECT into l_parent_org_id_var LIMIT 10000;
1072 exit when l_parent_org_id_var.count = 0;--8915976
1073 if l_proj_org_true = 'Y' then
1074 /* pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1075 ,p_child_organization_id =>x_organization_id_child
1076 ,p_org_hierarchy_version_id => x_org_version_id
1077 ,p_pa_org_use_type => 'PROJECTS'
1078 , x_err_code => x_err_code
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
1088 , created_by
1089 , last_update_date
1090 , last_updated_by
1091 , last_update_login
1092 )
1093 SELECT l_parent_org_id_var(i)
1094 , x_organization_id_child
1095 , x_org_version_id
1096 , 'PROJECTS'
1097 , sysdate
1098 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1099 , sysdate
1100 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
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);
1110 END IF;
1111 l_parent_org_id_var.DELETE;
1112 end if;
1113 end loop;
1114 close get_all_orgs;
1115 --------
1116
1117 end if;
1118
1119 end loop;
1120
1121 /* For all operating units where the Exp Org structure
1122 specified is same as the changed Org structure */
1123 for imp_rec in ( select org_id, exp_start_org_id
1124 from pa_implementations_all imp
1125 where exp_org_structure_version_id = x_org_version_id
1126 )
1127 loop
1128
1129 /*Bug# 2247737*/
1130
1131 l_process_schedule_hier := 'Y';
1132
1133 /*End bug# 2247737*/
1134
1135 v_start_org_id := imp_rec.exp_start_org_id;
1136
1137 /* Bug 2139709 Begin */
1138 org_exists_flag := FALSE;
1139
1140 IF v_start_org_id = x_organization_id_parent THEN
1141 org_exists_flag := TRUE;
1142 ELSE
1143 open check_org_exists;
1144 fetch check_org_exists into v_org_child_id;
1145 if check_org_exists%found then /* Added org is below
1146 the start project org */
1147 org_exists_flag := TRUE;
1148 else
1149 org_exists_flag := FALSE;
1150 end if;
1151 close check_org_exists;
1152 END IF;
1153
1154 IF org_exists_flag = TRUE THEN
1155 /* Bug 2139709 End */
1156
1157 insert into pa_all_organizations
1158 (organization_id,
1159 org_id,
1160 pa_org_use_type)
1161 (select
1162 x_organization_id_child,
1163 imp_rec.org_id,
1164 'EXPENDITURES'
1165 from sys.dual
1166 where exists (select 'x'
1167 from hr_organization_information info
1168 where info.organization_id = x_organization_id_child
1169 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1170 and info.org_information_context||'' = 'CLASS'
1171 and info.org_information2 = 'Y')
1172 and not exists
1173 (select 'X'
1174 from pa_all_organizations
1175 where organization_id = x_organization_id_child
1176 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1177 and pa_org_use_type = 'EXPENDITURES')
1178 ) ;
1179
1180 /* Added for bug 1550990 */
1181 if(sql%rowcount=0) then
1182 Update pa_all_organizations
1183 set inactive_date=NULL
1184 where organization_id =x_organization_id_child
1185 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1186 and pa_org_use_type = 'EXPENDITURES'
1187 and exists (select 'x' -- EXISTS condition added for bug 2890516
1188 from hr_organization_information info
1189 where info.organization_id = x_organization_id_child
1190 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1191 and info.org_information_context||'' = 'CLASS'
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; */
1201
1202 begin
1203 select 'Y' into l_exp_org_true
1204 from hr_organization_information info
1205 where info.organization_id = x_organization_id_child
1206 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1207 and info.org_information_context||'' = 'CLASS'
1208 and info.org_information2 = 'Y';
1209 exception when no_data_found then
1210 l_exp_org_true := 'N';
1211 end;
1212 l_parent_org_id_var.DELETE;
1213 open get_all_orgs;
1214 loop
1215 fetch get_all_orgs BULK COLLECT into l_parent_org_id_var LIMIT 10000;
1216 exit when l_parent_org_id_var.count = 0;--8915976
1217 if l_exp_org_true = 'Y' then
1218 /*pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1219 ,p_child_organization_id =>x_organization_id_child
1220 ,p_org_hierarchy_version_id => x_org_version_id
1221 ,p_pa_org_use_type => 'EXPENDITURES'
1222 , x_err_code => x_err_code
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
1232 , created_by
1233 , last_update_date
1234 , last_updated_by
1235 , last_update_login
1236 )
1237 SELECT l_parent_org_id_var(i)
1238 , x_organization_id_child
1239 , x_org_version_id
1240 , 'EXPENDITURES'
1241 , sysdate
1242 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1243 , sysdate
1244 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
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);
1254 END IF;
1255 l_parent_org_id_var.DELETE;
1256 end if;
1257 end loop;
1258 close get_all_orgs;
1259 --------
1260 end if;
1261
1262 end loop;
1263 -- Start CC Change
1264
1265 /*Bug# 2247737*/
1266
1267 if ( l_process_schedule_hier = 'Y' ) then
1268
1269 /*End bug# 2247737*/
1270 l_start_org_id := get_start_org_id_sch(x_org_version_id);
1271
1272 /* Bug#2645200 - Changed x_organization_id_parent instead of x_organization_id_child as the parameter
1273 for p_child_parent_org_id */
1274 -- Added exception handler for Bug 12618575
1275 BEGIN
1276
1277 l_clevel := get_org_level(
1278 p_org_version_id => x_org_version_id,
1279 p_child_parent_org_id => x_organization_id_parent, /* 2645200 x_organization_id_child,*/
1280 p_start_org_id => l_start_org_id );
1281 EXCEPTION
1282 WHEN NO_DATA_FOUND THEN
1283 l_parent_exists := 'N';
1284 END;
1285
1286 IF l_parent_exists = 'Y' THEN -- Bug 12618575
1287
1288 l_clevel := l_clevel + 1; /* Bug#2645200 - Added as the level got is for parent in the call earlier */
1289
1290 open get_all_orgs_sch(x_organization_id_parent,
1291 x_organization_id_child,
1292 x_org_version_id );
1293 LOOP
1294 FETCH get_all_orgs_sch into l_parent_org_id;
1295 exit when get_all_orgs_sch%notfound; /*Bug# 2244878*/
1296
1297 /* 2645200 */
1298 if (l_parent_org_id = x_organization_id_child) then
1299 l_plevel := l_clevel;
1300 else
1301 l_plevel := get_org_level(
1302 p_org_version_id => x_org_version_id,
1303 p_child_parent_org_id => l_parent_org_id,
1304 p_start_org_id => l_start_org_id );
1305 end if;
1306
1307 pa_org_utils.Create_org_hier_denorm_levels
1308 (p_parent_organization_id=> l_parent_org_id
1309 , p_child_organization_id => x_organization_id_child
1310 , p_org_hierarchy_version_id=> x_org_version_id
1311 , p_pa_org_use_type => 'TP_SCHEDULE'
1312 , p_parent_level => l_plevel
1313 , p_child_level => l_clevel
1314 , x_err_code => x_err_code
1315 , x_err_stage => x_err_stage
1316 , x_err_stack => x_err_stack);
1317 END LOOP;
1318 close get_all_orgs_sch; /*Bug# 2244878*/
1319 -- End CC Change
1320
1321 /*Bug# 2247737*/
1322 END IF;
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,
1332 x_err_code ,
1333 x_err_stage,
1334 x_err_stack );
1335
1336 Exception
1337 when others then
1338 x_err_code := SQLCODE;
1339 x_err_stage := SQLERRM;
1340 return;
1341
1342 End;
1343
1344 Procedure maintain_org_hist_brd(x_org_version_id in number,
1345 x_organization_id_child in number,
1346 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
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;
1356
1357 Begin
1358 /* For all operating units where the Project Org structure
1359 specified is same as the changed Org structure */
1360 for imp_rec in ( select org_id,proj_start_org_id
1361 from pa_implementations_all imp
1362 where proj_org_structure_version_id = x_org_version_id
1363 )
1364
1365 loop
1366 update pa_all_organizations
1367 set inactive_date = trunc(sysdate)
1368 where organization_id = x_organization_id_child
1369 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1370 and pa_org_use_type = 'PROJECTS'
1371 and inactive_date is null ; -- Bug Ref # 6367868
1372 end loop;
1373
1374
1375 /* For all operating units where the Exp Org structure
1376 specified is same as the changed Org structure */
1377 for imp_rec in ( select org_id, exp_start_org_id
1378 from pa_implementations_all imp
1379 where exp_org_structure_version_id = x_org_version_id
1380 )
1381 loop
1382 update pa_all_organizations
1383 set inactive_date = trunc(sysdate)
1384 where organization_id = x_organization_id_child
1385 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1386 and pa_org_use_type = 'EXPENDITURES'
1387 and inactive_date is null ; -- Bug Ref # 6367868
1388 end loop;
1389
1390 --- Delete all rows from pa_cc_tp_schedule_line_lkp
1391 delete from pa_cc_tp_schedule_line_lkp;
1392
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;
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 ,
1409 x_err_stage,
1410 x_err_stack
1411 );
1412 Exception
1413 when others then
1414 x_err_code := SQLCODE;
1415 x_err_stage := SQLERRM;
1416 return;
1417
1418 End;
1419
1420
1421 -----maintain_org_info_hist_bri created on HR_ORGANIZATION_INFORMATION
1422 ----- For update and insert in PA_ALL_ORGANIZATIO against BUG NO 1357522
1423 procedure maintain_org_info_hist_bri
1424 (x_organization_id in number ,
1425 x_org_information1 in varchar2,
1426 x_org_information_context in varchar2,
1427 x_org_information2 in varchar2,
1428 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1429 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1430 x_err_stack in out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
1431
1432
1433 v_org_structure_version_id number;
1434 v_org_child_id number;
1435 v_org_chk varchar2(60);
1436 v_start_org_id number;
1437
1438 /*
1439 Bug#2620151-Commented the old cursor and added new logic for the cursor check_org_exists
1440
1441 New Logic: Traverse from x_organization_id and get all the parents for this.
1442 In this result, check for v_start_org_id, if present then it means that the org
1443 x_organization_id is under v_start_org_id.
1444
1445 Earlier Logic: Traverse from v_start_org_id and get all the childs for this.
1446 In this result, check for x_organization_id, if present then it means that the
1447 org x_organization_id is under v_start_org_id.
1448 */
1449
1450 cursor check_org_exists is
1451 (
1452 select se.organization_id_parent
1453 from per_org_structure_elements se
1454 where se.org_structure_version_id = v_org_structure_version_id
1455 and se.organization_id_parent =
1456 v_start_org_id
1457 connect by prior se.organization_id_parent =
1458 se.organization_id_child
1459 and org_structure_version_id = v_org_structure_version_id
1460 start with se.organization_id_child =
1461 x_organization_id
1462 and org_structure_version_id = v_org_structure_version_id
1463 );
1464
1465 /* Commeneted the cursor below for bug#2620151, Added new cursor above
1466 cursor check_org_exists is
1467 (select se.organization_id_child
1468 from per_org_structure_elements se
1469 where se.org_structure_version_id = v_org_structure_version_id
1470 and se.organization_id_child = x_organization_id ---made changes as Suggested
1471 connect by prior se.organization_id_child =
1472 se.organization_id_parent
1473 and org_structure_version_id = v_org_structure_version_id
1474 start with se.organization_id_parent =
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
1484 SELECT organization_id_parent
1485 FROM per_org_structure_elements
1486 WHERE org_structure_version_id = v_org_structure_version_id
1487 CONNECT BY PRIOR organization_id_parent= organization_id_child
1488 AND org_structure_version_id = v_org_structure_version_id
1489 START WITH organization_id_child = x_organization_id);
1490
1491 l_parent_org_id number;
1492
1493 Begin
1494 --- Delete all rows from pa_cc_tp_schedule_line_lkp
1495 delete from pa_cc_tp_schedule_line_lkp;
1496
1497 if x_org_information1 = 'PA_PROJECT_ORG' AND
1498 x_org_information_context = 'CLASS' then
1499 if x_org_information2 = 'Y' then
1500
1501 ----- Means This Organization is being classified as
1502 ---- Projects Task / owning Organization and classification
1503 --- is enabled. Now Loop theough all the records from
1504 --- pa_implementations_all. And check that CUrrent Organization
1505 ---- Being changed is attached to an Organization structure which
1506 ---- is defined in Implementation as Project Structure.
1507 for imp_rec in (select proj_start_org_id, proj_org_structure_version_id,
1508 org_id
1509 from pa_implementations_all )
1510 loop
1511 v_start_org_id := imp_rec.proj_start_org_id;
1512 v_org_structure_version_id := imp_rec.proj_org_structure_version_id;
1513 ---- Check that Current Organization which is getting changed is below
1514 ---- The start organization specified for a Project Organization
1515 ---- Structure.
1516 IF(v_start_org_id <> x_organization_id) THEN --made changes as Suggested
1517 open check_org_exists;
1518 fetch check_org_exists into v_org_child_id;
1519 if check_org_exists%found then /* Added org is below the start project org */
1520 insert into pa_all_organizations
1521 (organization_id,
1522 org_id,
1523 pa_org_use_type)
1524 (select x_organization_id,
1525 imp_rec.org_id, 'PROJECTS'
1526 from sys.dual
1527 where not exists ( select 'X'
1528 from pa_all_organizations
1529 where organization_id = x_organization_id
1530 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1531 and pa_org_use_type = 'PROJECTS' ));
1532
1533 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1534 ---- Earlier, IN This case set inactive_date to
1535 --- NULL.
1536 update pa_all_organizations
1537 set inactive_date = null
1538 where organization_id = x_organization_id
1539 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1540 and pa_org_use_type = 'PROJECTS' ;
1541 end if;
1542
1543 open get_all_orgs;
1544 loop
1545 fetch get_all_orgs into l_parent_org_id;
1546 exit when get_all_orgs%notfound;
1547 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1548 , p_child_organization_id => x_organization_id
1549 , p_org_hierarchy_version_id => v_org_structure_version_id
1550 , p_pa_org_use_type => 'PROJECTS'
1551 , x_err_code => x_err_code
1552 , x_err_stage => x_err_stage
1553 , x_err_stack => x_err_stack);
1554 end loop;
1555 close get_all_orgs;
1556
1557 end if;
1558 close check_org_exists;
1559 ELSE
1560 ---- If Current Organization is Start Organizations then Record has to be
1561 ---- Created in PA_ALL_ORGANIZATIONS.
1562 insert into pa_all_organizations
1563 (organization_id,
1564 org_id,
1565 pa_org_use_type)
1566 (select x_organization_id,
1567 imp_rec.org_id, 'PROJECTS'
1568 from sys.dual
1569 where not exists ( select 'X'
1570 from pa_all_organizations
1571 where organization_id = x_organization_id
1572 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1573 and pa_org_use_type = 'PROJECTS' ));
1574 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1575 ---- Earlier, IN This case set inactive_date to
1576 --- NULL.
1577 update pa_all_organizations
1578 set inactive_date = null
1579 where organization_id = x_organization_id
1580 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1581 and pa_org_use_type = 'PROJECTS' ;
1582 end if;
1583
1584 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM ( p_parent_organization_id => v_start_org_id
1585 , p_child_organization_id => x_organization_id
1586 , p_org_hierarchy_version_id => v_org_structure_version_id
1587 , p_pa_org_use_type => 'PROJECTS'
1588 , x_err_code => x_err_code
1589 , x_err_stage => x_err_stage
1590 , x_err_stack => x_err_stack);
1591
1592 END IF;
1593 end loop;
1594 else
1595 for imp_rec in (select proj_start_org_id, proj_org_structure_version_id,
1596 org_id
1597 from pa_implementations_all )
1598 loop
1599
1600 update pa_all_organizations
1601 set inactive_date = trunc(sysdate)
1602 where organization_id = x_organization_id
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 */
1612
1613 end loop;
1614
1615 end if;
1616 end if;
1617 ----- Check for Expenditure Organization Type starts here.
1618 if x_org_information1 = 'PA_EXPENDITURE_ORG' and
1619 x_org_information_context = 'CLASS' then
1620
1621 if x_org_information2 = 'Y' then
1622 ----- Means This Organization is being classified as
1623 ---- Projects Expenditure owing Organization and classification
1624 --- is enabled. Now Loop theough all the records from
1625 --- pa_implementations_all. And check that Current Organization
1626 ---- Being changed is attached to an Organization structure which
1627 ---- is defined in Implementation as Project Structure.
1628 for imp_rec in (select exp_start_org_id, exp_org_structure_version_id,
1629 org_id
1630 from pa_implementations_all )
1631 loop
1632 v_start_org_id := imp_rec.exp_start_org_id;
1633 v_org_structure_version_id := imp_rec.exp_org_structure_version_id;
1634 ---- Check that Current Organization which is getting changed is below
1635 ---- The start organization specified for a Expenditure Organization
1636 ---- Structure.
1637 IF(v_start_org_id<>x_organization_id) THEN
1638 open check_org_exists;
1639 fetch check_org_exists into v_org_child_id;
1640 if check_org_exists%found then /* Added org is below the start project org */
1641 insert into pa_all_organizations
1642 (organization_id,
1643 org_id,
1644 pa_org_use_type)
1645 (select x_organization_id,
1646 imp_rec.org_id, 'EXPENDITURES'
1647 from sys.dual
1648 where not exists ( select 'X'
1649 from pa_all_organizations
1650 where organization_id = x_organization_id
1651 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1652 and pa_org_use_type = 'EXPENDITURES' ));
1653
1654 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1655 ---- Earlier, IN This case set inactive_date to
1656 --- NULL.
1657 update pa_all_organizations
1658 set inactive_date = null
1659 where organization_id = x_organization_id
1660 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1661 and pa_org_use_type = 'EXPENDITURES' ;
1662 end if;
1663 open get_all_orgs;
1664 loop
1665 fetch get_all_orgs into l_parent_org_id;
1666 exit when get_all_orgs%notfound;
1667 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1668 , p_child_organization_id => x_organization_id
1669 , p_org_hierarchy_version_id => v_org_structure_version_id
1670 , p_pa_org_use_type => 'EXPENDITURES'
1671 , x_err_code => x_err_code
1672 , x_err_stage => x_err_stage
1673 , x_err_stack => x_err_stack);
1674 end loop;
1675 close get_all_orgs;
1676
1677 end if;
1678 close check_org_exists;
1679 ELSE
1680 ---- If Current Organization is Start Organizations then Record has to be
1681 ---- Created in PA_ALL_ORGANIZATIONS.
1682 insert into pa_all_organizations
1683 (organization_id,
1684 org_id,
1685 pa_org_use_type)
1686 (select x_organization_id,
1687 imp_rec.org_id, 'EXPENDITURES'
1688 from sys.dual
1689 where not exists ( select 'X'
1690 from pa_all_organizations
1691 where organization_id = x_organization_id
1692 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1693 and pa_org_use_type = 'EXPENDITURES' ));
1694 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1695 ---- Earlier, IN This case set inactive_date to
1696 --- NULL.
1697 update pa_all_organizations
1698 set inactive_date = null
1699 where organization_id = x_organization_id
1700 and org_id = imp_rec.org_id--MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1701 and pa_org_use_type = 'EXPENDITURES' ;
1702 end if;
1703
1704 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM ( p_parent_organization_id => v_start_org_id
1705 , p_child_organization_id => x_organization_id
1706 , p_org_hierarchy_version_id => v_org_structure_version_id
1707 , p_pa_org_use_type => 'EXPENDITURES'
1708 , x_err_code => x_err_code
1709 , x_err_stage => x_err_stage
1710 , x_err_stack => x_err_stack);
1711
1712 END IF;
1713 end loop;
1714 else
1715 for imp_rec in (select exp_start_org_id, exp_org_structure_version_id,
1716 org_id
1717 from pa_implementations_all )
1718 loop
1719
1720 update pa_all_organizations
1721 set inactive_date = trunc(sysdate)
1722 where organization_id = x_organization_id
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 */
1732
1733 end loop;
1734
1735 end if;
1736 end if;
1737 EXCEPTION
1738 when others then
1739 x_err_code := SQLCODE;
1740 x_err_stage := SQLERRM;
1741 return;
1742 end;
1743 -------maintain_org_info_hist_bri ends
1744
1745 Procedure Start_Org_Changed (x_old_org_version_id in number,
1746 x_new_org_version_id in number,
1747 x_old_start_org_id in number,
1748 x_new_start_org_id in number,
1749 x_org_use_type in varchar2,
1750 x_org_id in number,
1751 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1752 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1753 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
1754 IS
1755
1756 CURSOR cur_all_old_org IS
1757 SELECT se.organization_id_child organization_id
1758 FROM per_org_structure_elements se
1759 WHERE org_structure_version_id = x_old_org_version_id
1760 CONNECT BY PRIOR se.organization_id_child
1761 = se.organization_id_parent
1762 AND org_structure_version_id = x_old_org_version_id
1763 START WITH se.organization_id_parent = x_old_start_org_id
1764 AND org_structure_version_id = x_old_org_version_id
1765 UNION
1766 SELECT x_old_start_org_id FROM Sys.dual ;
1767
1768 CURSOR cur_proj_new_org IS
1769 SELECT se.organization_id_child organization_id
1770 FROM per_org_structure_elements se
1771 WHERE org_structure_version_id = x_new_org_version_id
1772 AND EXISTS (select 'X'
1773 from hr_organization_information info
1774 where info.organization_id = se.organization_id_child
1775 and info.org_information1 = 'PA_PROJECT_ORG'
1776 and info.org_information_context||'' = 'CLASS'
1777 and info.org_information2 = 'Y')
1778 CONNECT BY PRIOR se.organization_id_child
1779 = se.organization_id_parent
1780 AND org_structure_version_id = x_new_org_version_id
1781 START WITH se.organization_id_parent = x_new_start_org_id
1782 AND org_structure_version_id = x_new_org_version_id
1783 UNION
1784 SELECT x_new_start_org_id FROM Sys.dual -- Added exists clause for Bug# 1650520
1785 WHERE EXISTS (select 'X'
1786 from hr_organization_information info
1787 where info.org_information1 = 'PA_PROJECT_ORG'
1788 and info.org_information_context||'' = 'CLASS'
1789 and info.org_information2 = 'Y'
1790 and info.organization_id = x_new_start_org_id);
1791
1792
1793 CURSOR cur_exp_new_org IS
1794 SELECT se.organization_id_child organization_id
1795 FROM per_org_structure_elements se
1796 WHERE org_structure_version_id = x_new_org_version_id
1797 AND EXISTS (select 'X'
1798 from hr_organization_information info
1799 where info.organization_id = se.organization_id_child
1800 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1801 and info.org_information_context||'' = 'CLASS'
1802 and info.org_information2 = 'Y')
1803 CONNECT BY PRIOR se.organization_id_child
1804 = se.organization_id_parent
1805 AND org_structure_version_id = x_new_org_version_id
1806 START WITH se.organization_id_parent = x_new_start_org_id
1807 AND org_structure_version_id = x_new_org_version_id
1808 UNION
1809 SELECT x_new_start_org_id FROM Sys.dual
1810 where EXISTS (select 'X' /* Made changes for BUG 1180635*/
1811 from hr_organization_information info
1812 where info.org_information1 = 'PA_EXPENDITURE_ORG'
1813 and info.org_information_context||'' = 'CLASS'
1814 and info.org_information2 = 'Y'
1815 and info.organization_id = x_new_start_org_id);
1816
1817
1818 rec_all_old_org cur_all_old_org%ROWTYPE;
1819 rec_exp_new_org cur_exp_new_org%ROWTYPE;
1820 rec_proj_new_org cur_proj_new_org%ROWTYPE;
1821
1822 BEGIN
1823 x_err_code := 0;
1824 x_err_stack := x_err_stack || 'Start_Org_Changed';
1825
1826 /* When either structure version id OR start organization id changes */
1827
1828 IF ( (NVL(x_old_org_version_id,-99) <> x_new_org_version_id )
1829 OR
1830 (NVL(x_old_start_org_id,-99) <> x_new_start_org_id ) ) THEN
1831
1832 FOR rec_all_old_org in cur_all_old_org LOOP
1833
1834 /* Update all Organizations (Projects or Expenditures)
1835 in the old hierarchy with inactive date as Sysdate.
1836 Union is to include the start organization id in the update */
1837
1838 UPDATE pa_all_organizations
1839 SET Inactive_Date = TRUNC(SYSDATE)
1840 WHERE Pa_Org_Use_Type = x_org_use_type
1841 AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1842 AND Organization_id = rec_all_old_org.organization_id
1843 and inactive_date is null ; -- Bug Ref # 6367868
1844
1845 /* In case there are no corresponding records for the new
1846 Organization hierarchy in the Pa_all_Organizations, then,
1847 create these records in the Pa_All_Organizations
1848 with Inactive_Date as SYSDATE */
1849 --Bug#9923691 - Commented the following code for not inserting new records if organization is not having org_use_type
1850 /*IF SQL%NOTFOUND THEN
1851 INSERT INTO Pa_All_Organizations
1852 (organization_id,
1853 org_id,
1854 pa_org_use_type,
1855 inactive_date)
1856 VALUES
1857 (rec_all_old_org.organization_id,
1858 x_org_id,
1859 x_org_use_type,
1860 TRUNC(SYSDATE));
1861 END IF;*/
1862 --Bug#9923691 - fix end
1863
1864 END LOOP;
1865
1866 IF x_org_use_type = 'EXPENDITURES' THEN
1867
1868 FOR rec_exp_new_org IN cur_exp_new_org LOOP
1869
1870 /* Update all Organizations (Expenditures)
1871 in the new hierarchy with inactive date as NULL.
1872 Union is to include the start organization id in the update */
1873
1874 UPDATE pa_all_organizations
1875 SET Inactive_Date = NULL
1876 WHERE Pa_Org_Use_Type = x_org_use_type
1877 AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1878 AND Organization_id = rec_exp_new_org.organization_id;
1879
1880 /* In case there are no corresponding records for the new
1881 Organization hierarchy in the Pa_all_Organizations, then,
1882 create these records in the Pa_All_Organizations
1883 with Inactive_Date as NULL */
1884
1885 IF SQL%NOTFOUND THEN
1886 INSERT INTO Pa_All_Organizations
1887 (organization_id,
1888 org_id,
1889 pa_org_use_type,
1890 inactive_date)
1891 VALUES
1892 (rec_exp_new_org.organization_id,
1893 x_org_id,
1894 x_org_use_type,
1895 NULL);
1896 END IF;
1897
1898 END LOOP;
1899
1900 END IF; /* End of IF for org_use_type = 'EXPENDITURES' */
1901
1902
1903 IF x_org_use_type = 'PROJECTS' THEN
1904
1905 FOR rec_proj_new_org IN cur_proj_new_org LOOP
1906
1907 /* Update all Organizations (Projects)
1908 in the new hierarchy with inactive date as NULL.
1909 Union is to include the start organization id in the update */
1910
1911 UPDATE pa_all_organizations
1912 SET Inactive_Date = NULL
1913 WHERE Pa_Org_Use_Type = x_org_use_type
1914 AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1915 AND Organization_id = rec_proj_new_org.organization_id;
1916
1917 /* In case there are no corresponding records for the new
1918 Organization hierarchy in the Pa_all_Organizations, then,
1919 create these records in the Pa_All_Organizations
1920 with Inactive_Date as NULL */
1921
1922 IF SQL%NOTFOUND THEN
1923 INSERT INTO Pa_All_Organizations
1924 (organization_id,
1925 org_id,
1926 pa_org_use_type,
1927 inactive_date)
1928 VALUES
1929 (rec_proj_new_org.organization_id,
1930 x_org_id,
1931 x_org_use_type,
1932 NULL);
1933 END IF;
1934
1935 END LOOP;
1936
1937 END IF; /* End of IF for org_use_type = 'PROJECTS' */
1938
1939 END IF;
1940
1941 Exception
1942 when others then
1943 x_err_code := SQLCODE;
1944 x_err_stage := SQLERRM;
1945 return;
1946
1947 END Start_Org_Changed;
1948
1949 PROCEDURE Create_org_hierarchy_denorm(p_parent_organization_id in number,
1950 p_child_organization_id in number,
1951 p_org_hierarchy_version_id in number,
1952 p_pa_org_use_type in varchar2,
1953 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1954 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1955 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1956 old_stack varchar2(4000);
1957 Begin
1958
1959 x_err_code := 0;
1960 old_stack := x_err_stack;
1961 x_err_stack := x_err_stack || 'Create_org_hierarchy_denorm';
1962 /*
1963 dbms_output.put_line('in create');
1964 dbms_output.put_line('p_pa_org_use_type ='||p_pa_org_use_type);
1965 dbms_output.put_line('p_parent_organization_id ='||p_parent_organization_id);
1966 dbms_output.put_line('p_child_organization_id ='||p_child_organization_id);
1967 dbms_output.put_line('p_org_hierarchy_version_id ='||p_org_hierarchy_version_id);
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
1977 , created_by
1978 , last_update_date
1979 , last_updated_by
1980 , last_update_login
1981 )
1982 SELECT p_parent_organization_id
1983 , p_child_organization_id
1984 , p_org_hierarchy_version_id
1985 , p_pa_org_use_type
1986 , sysdate
1987 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1988 , sysdate
1989 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
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);
1999
2000 x_err_stack := old_stack;
2001 Exception
2002 when others then
2003 x_err_code := SQLCODE;
2004 x_err_stage := SQLERRM;
2005 return;
2006
2007 End;
2008
2009 -- Start CC Change
2010
2011 /* Take following heirarchy for understanding this piece of logic
2012 for the use_type = TP_SCHEDULE
2013
2014 Assume that following is the hierarchy:
2015 org1
2016 / \
2017 org2 org3 -> start org in pa_implementations
2018 / \
2019 org5 org6
2020 |
2021 org7
2022
2023
2024
2025 In this example the start org defined in the pa_implementations is org3.
2026
2027 For the TP_SCHEDULE purpose the hierarchy is always populated from the start
2028 of the hierarcy rather than the start org mentioned in the pa_implementations.
2029
2030 The aim of this procedure is to populate pa_org_denorm table with following data.
2031
2032 parent child p_level c_level
2033
2034 org1 org1 1 1
2035 org1 org2 1 2
2036 org1 org3 1 2
2037 org2 org2 2 2
2038 org3 org3 2 2
2039 org3 org5 2 3
2040 org3 org6 2 3
2041 org5 org5 3 3
2042 org5 org7 3 4
2043
2044 */
2045
2046 -- CC Change Added the below procedure.
2047 PROCEDURE Create_org_hier_denorm_levels(p_parent_organization_id in number,
2048 p_child_organization_id in number,
2049 p_org_hierarchy_version_id in number,
2050 p_pa_org_use_type in varchar2,
2051 p_parent_level in number,
2052 p_child_level in number,
2053 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2054 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2055 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
2056 old_stack varchar2(4000);
2057 Begin
2058
2059 x_err_code := 0;
2060 old_stack := x_err_stack;
2061 x_err_stack := x_err_stack || 'Create_org_hierarchy_denorm';
2062 /*
2063 dbms_output.put_line('in create');
2064 dbms_output.put_line('p_pa_org_use_type ='||p_pa_org_use_type);
2065 dbms_output.put_line('p_parent_organization_id ='||p_parent_organization_id);
2066 dbms_output.put_line('p_child_organization_id ='||p_child_organization_id);
2067 dbms_output.put_line('p_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
2077 , created_by
2078 , last_update_date
2079 , last_updated_by
2080 , last_update_login
2081 , parent_level
2082 , child_level
2083 )
2084 SELECT p_parent_organization_id
2085 , p_child_organization_id
2086 , p_org_hierarchy_version_id
2087 , p_pa_org_use_type
2088 , sysdate
2089 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
2090 , sysdate
2091 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
2092 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
2093 , p_parent_level
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);
2103
2104 x_err_stack := old_stack;
2105 Exception
2106 when others then
2107 x_err_code := SQLCODE;
2108 x_err_stage := SQLERRM;
2109 return;
2110
2111 End;
2112
2113 -- End CC Change
2114 --
2115 -- Created by Ranga Iyengar
2116 -- Dated : 02-NOV-00
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
2126 ,p_child_organization_id IN NUMBER
2127 ,p_org_hierarchy_version_id IN NUMBER
2128 ,p_pa_org_use_type IN VARCHAR2
2129 ,p_parent_org_level IN NUMBER
2130 ,p_child_org_level IN NUMBER
2131 ,p_org_id IN NUMBER
2132 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2133 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2134 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2135 ) IS
2136 old_stack VARCHAR2(2000);
2137 BEGIN
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
2147 , pa_org_use_type
2148 , creation_date
2149 , created_by
2150 , last_update_date
2151 , last_updated_by
2152 , last_update_login
2153 , parent_level
2154 , child_level
2155 , org_id
2156 )
2157 SELECT p_parent_organization_id
2158 , p_child_organization_id
2159 , p_org_hierarchy_version_id
2160 , p_pa_org_use_type
2161 , sysdate
2162 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2163 , sysdate
2164 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2165 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2166 , p_parent_org_level
2167 , p_child_org_level
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
2177 and nvl(org_id, -99) = nvl(p_org_id, -99)); /* 2976953 Added nvl condition for org_id */
2178 IF SQL%FOUND THEN
2179 --r_msg('row inserted');
2180 null;
2181 else
2182 --r_msg('not inserted');
2183 null;
2184 end if;
2185 x_err_stack := old_stack;
2186
2187 EXCEPTION
2188 when others then
2189 x_err_code := SQLCODE;
2190 x_err_stage := SQLERRM;
2191 --r_msg('could not insert'||x_err_code||x_err_stage);
2192 return;
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
2202 , P_org_max_level IN NUMBER
2203 , P_org_id IN NUMBER
2204 , P_start_org_id IN NUMBER -- added this to fix bug : 1619922
2205 , x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2206 , x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2207 , x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2208 ) IS
2209
2210
2211 v_start_org_id number;
2212 v_org_child_id number;
2213 v_parent_org_id number;
2214 v_plevel number;
2215 v_clevel number;
2216 old_stack varchar2(2000);
2217 v_parent_org_level number;
2218
2219 --- logic to populate hierarchy denorm
2220 -- get all the parent organization for the child
2221 -- example 200p - 205c,201p-205c,202p-205c,203p-205c,204p-205c,205p-205c
2222 -- where p is parent org and c is the child orgs
2223 cursor get_all_orgs is
2224 SELECT P_organization_id_parent
2225 FROM dual
2226 UNION
2227 (SELECT distinct organization_id_parent
2228 FROM per_org_structure_elements
2229 WHERE org_structure_version_id = P_org_version_id
2230 CONNECT BY PRIOR organization_id_parent= organization_id_child
2231 AND org_structure_version_id = P_org_version_id
2232 START WITH organization_id_child = P_organization_id_parent
2233 AND org_structure_version_id = P_org_version_id
2234 /* ---- The following query is added to fix the bug : 1654453 ---
2235 ---- since the start organization id defined in per_org_structure_elements
2236 ---- may be different from the start_organization_id set up in pa_implementations
2237 ---- so always the reporting hierarchy is formed based on pa_implementations
2238 ---- start_organization_id */
2239 MINUS
2240 SELECT distinct organization_id_parent
2241 FROM per_org_structure_elements
2242 WHERE org_structure_version_id = P_org_version_id
2243 CONNECT BY PRIOR organization_id_parent= organization_id_child
2244 AND org_structure_version_id = P_org_version_id
2245 START WITH organization_id_child = P_start_org_id
2246 AND org_structure_version_id = P_org_version_id)
2247 UNION
2248 (SELECT P_organization_id_child from dual);
2249
2250 BEGIN
2251
2252 x_err_code := 0;
2253 old_stack := x_err_stack;
2254 x_err_stack := x_err_stack || 'populate_org_levels';
2255
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;
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;
2282 end if;
2283
2284 if nvl(v_plevel,0) = 0 then
2285 v_plevel := P_org_max_level - P_org_level + 2;
2286 end if;
2287
2288 -- if v_clevel is null then
2289 v_clevel := P_org_max_level - P_org_level + 1;
2290 -- end if;
2291
2292
2293 -- where both parent and child orgs are same then level of
2294 -- parent org and level of child orgs are same
2295
2296 if P_organization_id_child = v_parent_org_id then
2297 v_plevel := v_clevel;
2298 end if;
2299
2300
2301 -- the above condition is commented out and added new condition to fix the bug : 1619922
2302 if (P_organization_id_child = v_parent_org_id) and (P_organization_id_child <> P_start_org_id) then
2303 v_plevel := v_clevel;
2304 elsif (P_organization_id_child = v_parent_org_id) and (P_organization_id_child = P_start_org_id) then
2305 v_clevel := v_plevel;
2306 end if;
2307 */
2308
2309 ----------- Added the Following lines to fix the bug : 1654453 --------------
2310 -- set the child level based on the formula
2311 v_clevel := P_org_max_level - P_org_level + 1;
2312
2313
2314 -- set the parent level based on the following condition
2315 -- get the parent organization level from the per_org_structure_elements
2316 -- then apply the formula
2317 SELECT max(level)
2318 INTO v_parent_org_level
2319 FROM per_org_structure_elements
2320 WHERE org_structure_version_id = P_org_version_id
2321 AND organization_id_parent = v_parent_org_id
2322 CONNECT BY PRIOR organization_id_child = organization_id_parent
2323 AND org_structure_version_id = P_org_version_id
2324 START WITH organization_id_parent = P_start_org_id
2325 AND org_structure_version_id = P_org_version_id;
2326 If v_parent_org_level is NULL then
2327
2328 SELECT max(level)
2329 INTO v_parent_org_level
2330 FROM per_org_structure_elements
2331 WHERE org_structure_version_id = P_org_version_id
2332 AND organization_id_child = v_parent_org_id
2333 CONNECT BY PRIOR organization_id_child = organization_id_parent
2334 AND org_structure_version_id = P_org_version_id
2335 START WITH organization_id_parent = P_start_org_id
2336 AND org_structure_version_id = P_org_version_id;
2337
2338
2339
2340 --- set the org level based on the formula
2341 v_plevel := P_org_max_level - v_parent_org_level + 1;
2342
2343 Else
2344
2345 v_plevel := P_org_max_level - v_parent_org_level + 2;
2346
2347 End if;
2348
2349 If P_organization_id_child = v_parent_org_id then
2350 v_clevel := v_plevel;
2351 End if;
2352 --r_msg('parent'||v_parent_org_id||'child'||P_organization_id_child||'plevel'||v_plevel||'clevel '||v_clevel);
2353
2354 ---------------------------------end of bug fix---------------------------
2355
2356
2357 create_reporting_org(
2358 p_parent_organization_id => v_parent_org_id
2359 ,p_child_organization_id =>P_organization_id_child
2360 ,p_org_hierarchy_version_id => P_org_version_id
2361 ,p_org_id => p_org_id
2362 ,p_pa_org_use_type => 'REPORTING'
2363 ,P_parent_org_level => v_plevel
2364 ,p_child_org_level => v_clevel
2365 ,x_err_code => x_err_code
2366 ,x_err_stage => x_err_stage
2367 ,x_err_stack => x_err_stack
2368 );
2369
2370 v_plevel := null;
2371 v_clevel := null;
2372 end loop;
2373 close get_all_orgs;
2374
2375 x_err_stack := old_stack;
2376 EXCEPTION
2377 when others then
2378 x_err_code := SQLCODE;
2379 x_err_stage := SQLERRM;
2380 --r_msg('error in populate org level'||x_err_code||x_err_stage);
2381 return;
2382
2383 END populate_org_levels;
2384
2385
2386 -- This Procedure is called from trigger org_maintn_hist_bri
2387 PROCEDURE populate_hierarchy_denorm
2388 ( p_org_version_id IN NUMBER
2389 ,p_organization_id_parent IN NUMBER
2390 ,p_organization_id_child IN NUMBER
2391 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2392 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2393 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2394 ) IS
2395 v_org_version_id number;
2396 v_child_org_id number;
2397 v_parent_org_id number;
2398 v_start_org_id number;
2399 v_org_level number;
2400 v_org_id number;
2401 v_maximumlevel number;
2402 v_dummy_child_id number;
2403 old_stack varchar2(2000);
2404 v_success_flag Number;
2405 dummy_level number := 1;
2406 -- get all orgs structure from per_org-structure_elemetns for
2407 -- given version- parameter and start org (from the pa implemetnaiton)
2408 cursor get_all_orgs(version_id NUMBER,start_org_id NUMBER) is
2409 SELECT dummy_level plevel --- This query is added to fix bug : 1619922
2410 ,start_org_id organization_id_parent
2411 ,start_org_id organization_id_child
2412 FROM dual
2413 UNION
2414 SELECT distinct
2415 level plevel
2416 ,organization_id_parent
2417 ,organization_id_child
2418 FROM per_org_structure_elements
2419 WHERE org_structure_version_id = version_id
2420 CONNECT BY PRIOR organization_id_child = organization_id_parent
2421 and org_structure_version_id = version_id
2422 START WITH organization_id_parent = start_org_id
2423 and org_structure_version_id = version_id
2424 ORDER by 1;
2425
2426 -- check whether the org exist in the given pa implementations ou
2427 -- and falls within the start org
2428 cursor check_org_exists(version_id NUMBER,start_org_id NUMBER) is
2429 (SELECT organization_id_child
2430 FROM per_org_structure_elements
2431 WHERE org_structure_version_id = version_id
2432 CONNECT BY PRIOR organization_id_child = organization_id_parent
2433 and org_structure_version_id = version_id
2434 START WITH organization_id_parent = start_org_id
2435 and org_structure_version_id = version_id
2436 UNION
2437 SELECT v_start_org_id
2438 FROM sys.dual)
2439 INTERSECT
2440 (SELECT p_organization_id_parent
2441 FROM sys.dual );
2442
2443
2444 BEGIN
2445
2446
2447 x_err_code := 0;
2448 old_stack := x_err_stack;
2449 x_err_stack := x_err_stack || 'populate_hierarchy_denorm';
2450
2451 --- Delete all rows from pa_cc_tp_schedule_line_lkp
2452 delete from pa_cc_tp_schedule_line_lkp;
2453
2454 ------- Acquire a lock before processing ----------------
2455
2456 if (pa_debug.acquire_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0)
2457 and (pa_debug.acquire_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) then
2458
2459 --dbms_output.put_line('acquired fclock and aclock');
2460
2461 for imp_rec in ( select org_id, start_organization_id
2462 from pa_implementations_all imp
2463 where org_structure_version_id = p_org_version_id
2464 )
2465 LOOP
2466 v_start_org_id := imp_rec.start_organization_id;
2467 v_org_id := imp_rec.org_id;
2468 ------ blow off the records and recreate with parent and child level-----
2469 --r_msg('startorg'||v_start_org_id||'OU'||v_org_id);
2470 open check_org_exists(p_org_version_id,v_start_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
2480 NULL;
2481 --r_msg('deleted');
2482 end if;
2483 Exception
2484 when others then
2485 null;
2486 End;
2487 end if;
2488 close check_org_exists;
2489
2490 ---------------------------------------------------------------------------
2491 /* For all operating units where the Reporting Org structure
2492 specified is same as the changed Org structure */
2493
2494 -- get maximum level for the set up
2495 SELECT max(level) into v_maximumlevel
2496 FROM per_org_structure_elements
2497 WHERE org_structure_version_id = p_org_version_id
2498 CONNECT BY PRIOR organization_id_child = organization_id_parent
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;
2508 --r_msg('version'||p_org_version_id||'startorg'||v_start_org_id||'OU'||v_org_id);
2509 --r_msg('calling populate org levels');
2510
2511 populate_org_levels (
2512 P_org_version_id => p_org_version_id,
2513 P_organization_id_parent => v_parent_org_id,
2514 P_organization_id_child => v_child_org_id,
2515 P_org_level => v_org_level,
2516 P_org_max_level => v_maximumlevel,
2517 P_org_id => v_org_id,
2518 P_start_org_id => v_start_org_id,
2519 x_err_code => x_err_code,
2520 x_err_stage => x_err_stage ,
2521 x_err_stack => x_err_stack
2522 );
2523
2524 end loop;
2525 close get_all_orgs;
2526
2527 END LOOP; ------ end of imp loop
2528
2529 ------------- Release the Lock --------------
2530 if (pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0)
2531 and (pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) then
2532 --dbms_output.put_line('org version '||p_org_version_id ||' is lock released');
2533 --r_msg('Releasing the lock on project ...');
2534 null;
2535 end if;
2536
2537 --------------------------------------------
2538
2539 else -- not acquired the lock so exit and set the return status to error
2540 --x_return_status := FND_API.G_SET_RET_STS_ERROR;
2541 x_err_stage := 'Unable to Acqurie Lock on FORCASTING';
2542 --dbms_output.put_line('org version '||p_org_version_id ||' is locked');
2543 end if;
2544
2545 x_err_stack := old_stack;
2546
2547 EXCEPTION
2548 when others then
2549 v_success_flag := pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id));
2550 v_success_flag := pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id));
2551 x_err_code := SQLCODE;
2552 x_err_stage := SQLERRM;
2553 --r_msg('exception raised in outer'||x_err_code||x_err_stage);
2554 return;
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
2564 IS
2565
2566 l_exist VARCHAR2(1):= null;
2567
2568 BEGIN
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;
2578
2579 x_return_status := FND_API.G_RET_STS_SUCCESS;
2580 pa_debug.reset_err_stack;
2581
2582 EXCEPTION
2583 WHEN NO_DATA_FOUND THEN
2584 x_return_status := FND_API.G_RET_STS_ERROR;
2585 x_error_message_code := 'PA_ORG_NOT_IN_ORGHIER';
2586
2587 WHEN OTHERS THEN
2588 fnd_msg_pub.add_exc_msg
2589 (p_pkg_name => 'PA_ORG_UTILS',
2590 p_procedure_name => pa_debug.g_err_stack );
2591 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
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
2601
2602 l_exist VARCHAR2(1):= null;
2603
2604 BEGIN
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
2614 x_return_status := FND_API.G_RET_STS_SUCCESS;
2615 pa_debug.reset_err_stack;
2616
2617 EXCEPTION
2618 WHEN NO_DATA_FOUND THEN
2619 x_return_status := FND_API.G_RET_STS_ERROR;
2620 IF p_org_structure_type = 'EXPENDITURES' THEN
2621 x_error_message_code := 'PA_NOT_EXP_ORG';
2622 ELSE
2623 x_error_message_code := 'PA_NOT_PROJ_ORG';
2624 END IF;
2625
2626 WHEN OTHERS THEN
2627 fnd_msg_pub.add_exc_msg
2628 (p_pkg_name => 'PA_ORG_UTILS',
2629 p_procedure_name => pa_debug.g_err_stack );
2630 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2631 RAISE;
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
2641
2642 l_exist VARCHAR2(1):= null;
2643
2644 BEGIN
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
2654 x_return_status := FND_API.G_RET_STS_SUCCESS;
2655 pa_debug.reset_err_stack;
2656
2657 EXCEPTION
2658 WHEN NO_DATA_FOUND THEN
2659 x_return_status := FND_API.G_RET_STS_ERROR;
2660 IF p_org_structure_type = 'EXPENDITURES' THEN
2661 x_error_message_code := 'PA_NOT_EXP_ORGHIER';
2662 ELSE
2663 x_error_message_code := 'PA_NOT_PROJ_ORGHIER';
2664 END IF;
2665
2666 WHEN OTHERS THEN
2667 fnd_msg_pub.add_exc_msg
2668 (p_pkg_name => 'PA_ORG_UTILS',
2669 p_procedure_name => pa_debug.g_err_stack );
2670 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2671 RAISE;
2672 END Check_OrgHierarchy_Type;
2673
2674
2675 --
2676 -- Procedure
2677 --
2678 -- Created by P Bandla
2679 -- Dated : 06-DEC-2000
2680 --
2681 --
2682 PROCEDURE Populate_Org_Hier_Denorm(
2683 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2684 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2685 IS
2686 cursor org_version is
2687 SELECT distinct
2688 imp.org_structure_version_id version_id
2689 ,imp.start_organization_id start_org_id
2690 FROM pa_implementations_all imp,
2691 per_org_structure_elements posg
2692 WHERE posg.org_structure_version_id = imp.org_structure_version_id;
2693
2694 v_err_code NUMBER;
2695 v_err_stack VARCHAR2(200);
2696 v_org_version_id NUMBER;
2697 v_organization_id_parent NUMBER;
2698 v_organization_id_child NUMBER;
2699 BEGIN
2700 x_return_status := FND_API.G_RET_STS_SUCCESS;
2701 FOR i IN org_version LOOP
2702 v_org_version_id := i.version_id;
2703 v_organization_id_parent := i.start_org_id;
2704 v_organization_id_child := 1;
2705
2706 pa_org_utils.populate_hierarchy_denorm(
2707 p_org_version_id => v_org_version_id
2708 ,p_organization_id_parent => v_organization_id_parent
2709 ,p_organization_id_child => v_organization_id_child
2710 ,x_err_code => v_err_code
2711 ,x_err_stage => x_msg_data
2712 ,x_err_stack => v_err_stack);
2713 END LOOP;
2714 EXCEPTION
2715 WHEN OTHERS THEN
2716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2717 v_err_code := SQLCODE;
2718 x_msg_data := SQLERRM;
2719 raise;
2720 END Populate_Org_Hier_Denorm;
2721
2722 PROCEDURE POPULATE_HIERARCHY_DENORM2
2723 (p_org_version_id IN NUMBER
2724 ,p_organization_id_parent IN NUMBER
2725 ,p_organization_id_child IN NUMBER
2726 ,p_org_id IN NUMBER
2727 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2728 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2729 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2730 ) IS
2731
2732 v_max_level NUMBER;
2733 old_stack VARCHAR2(2000);
2734 v_success_flag NUMBER;
2735
2736 /* Take following heirarchy for understanding this piece of logic
2737 org1
2738 / \
2739 org2 org3 -> start org in pa_implementations
2740 / / \
2741 org4 org5 org6
2742 | / \
2743 org7 org8 org9
2744 |
2745 org10
2746 in this example p_organization_id_parent = org3.
2747 The aim of this package is to populate pa_org_denorm table with following data.
2748 parent child p_level c_level
2749 org3 org5 4 3
2750 org3 org6 4 3
2751 org3 org7 4 2
2752 org3 org8 4 2
2753 org3 org9 4 2
2754 org3 org10 4 1
2755 org5 org7 3 2
2756 org6 org8 3 2
2757 org6 org9 3 2
2758 org6 org10 3 1
2759 org9 org10 2 1
2760 org3 org3 4 4
2761 org5 org5 3 3
2762 org6 org6 3 3
2763 org7 org7 2 2
2764 org8 org8 2 2
2765 org9 org9 2 2
2766 org10 org10 1 1
2767 */
2768
2769 /* this cursor will select all parents in the hierarchy below p_organization_id_parent
2770 including p_organization_id_parent. This cursor will not select leaf nodes
2771 for p_organization_id_parent = org3 it will return org3, org5,org6, org9 */
2772
2773 CURSOR all_parents (max_level number) IS
2774 SELECT distinct organization_id_parent
2775 ,(max_level - level + 1) rev_level
2776 FROM per_org_structure_elements a
2777 WHERE a.org_structure_version_id = p_org_version_id
2778 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2779 AND a.org_structure_version_id = p_org_version_id
2780 START WITH a.organization_id_parent = p_organization_id_parent
2781 AND a.org_structure_version_id = p_org_version_id
2782 ORDER by rev_level desc;
2783
2784 BEGIN
2785
2786 x_err_code := 0;
2787 old_stack := x_err_stack;
2788 x_err_stack := x_err_stack || 'populate_hierarchy_denorm';
2789
2790 --- Delete all rows from pa_cc_tp_schedule_line_lkp
2791 delete from pa_cc_tp_schedule_line_lkp;
2792
2793 ------- Acquire a lock before processing ----------------
2794
2795 IF (pa_debug.acquire_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0) AND
2796 (pa_debug.acquire_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) THEN
2797
2798 /*
2799 mano_msg('p_org_version_id = ' || p_org_version_id );
2800 mano_msg('p_organization_id_parent = ' || p_organization_id_parent );
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)
2810 AND pa_org_use_type = 'REPORTING'; /* 2976953-Added nvl condition for org id and check for
2811 pa_org_use_type being REPORTING */
2812
2813 /* get the max level i.e. number of levels under p_organization_id_parent.
2814 value of max_level for the heirarchy shown for p_organization_id_parent = org3
2815 will be 4
2816 */
2817
2818 SELECT MAX(level+1)
2819 INTO v_max_level
2820 FROM per_org_structure_elements a
2821 WHERE a.org_structure_version_id = p_org_version_id
2822 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2823 AND a.org_structure_version_id = p_org_version_id
2824 START WITH a.organization_id_parent = p_organization_id_parent
2825 AND a.org_structure_version_id = p_org_version_id;
2826
2827 --mano_msg('v_max_level = ' || v_max_level);
2828
2829 FOR c1rec in all_parents (v_max_level) LOOP
2830
2831 /*
2832 mano_msg('c1rec.organization_id_parent = ' || c1rec.organization_id_parent);
2833 mano_msg('c1rec.rev_level = ' || c1rec.rev_level);
2834 */
2835
2836 /*
2837 for each parent insert all childs
2838 this will be called for each parent i.e. org3, org5, org6, org9
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
2848 , creation_date
2849 , created_by
2850 , last_update_date
2851 , last_updated_by
2852 , last_update_login
2853 , parent_level
2854 , child_level
2855 , org_id
2856 )
2857 (SELECT c1rec.organization_id_parent
2858 , organization_id_child
2859 , org_structure_version_id
2860 , 'REPORTING'
2861 , sysdate
2862 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2863 , sysdate
2864 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2865 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2866 , c1rec.rev_level
2867 , c1rec.rev_level - level
2868 , p_org_id
2869 FROM per_org_structure_elements a
2870 WHERE a.org_structure_version_id = p_org_version_id
2871 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2872 AND a.org_structure_version_id = p_org_version_id
2873 START WITH a.organization_id_parent = c1rec.organization_id_parent
2874 AND a.org_structure_version_id = p_org_version_id
2875 );
2876
2877 --mano_msg('inserted ' || sql%rowcount || ' rows');
2878
2879 END LOOP;
2880
2881 /* now insert all organizations in the heirarchy under p_organization_id_parent
2882 into this table with parent and child organization id same. As all organizations
2883 in the heirarchy has to be a child of start org so select all childs from denorm
2884 table for this parent.
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
2894 , creation_date
2895 , created_by
2896 , last_update_date
2897 , last_updated_by
2898 , last_update_login
2899 , parent_level
2900 , child_level
2901 , org_id
2902 )
2903 (SELECT child_organization_id
2904 , child_organization_id
2905 , org_hierarchy_version_id
2906 , 'REPORTING'
2907 , sysdate
2908 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2909 , sysdate
2910 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
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
2920 UNION ALL
2921 SELECT p_organization_id_parent
2922 , p_organization_id_parent
2923 , p_org_version_id
2924 , 'REPORTING'
2925 , sysdate
2926 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2927 , sysdate
2928 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2929 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2930 , v_max_level
2931 , v_max_level
2932 , p_org_id
2933 FROM dual
2934 );
2935
2936 --mano_msg('inserted ' || sql%rowcount || ' rows');
2937
2938 ------------- Release the Lock --------------
2939 IF (pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0) AND
2940 (pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) THEN
2941 NULL;
2942 END IF;
2943
2944
2945 ELSE
2946 -- not acquired the lock so exit and set the return status to error
2947 x_err_stage := 'Unable to Acqurie Lock on FORCASTING';
2948
2949 END IF;
2950
2951 x_err_stack := old_stack;
2952
2953 EXCEPTION
2954 WHEN OTHERS THEN
2955 v_success_flag := pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id));
2956 v_success_flag := pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id));
2957 x_err_code := SQLCODE;
2958 x_err_stage := SQLERRM;
2959 --r_msg('exception raised in outer'||x_err_code||x_err_stage);
2960 RETURN;
2961
2962 END POPULATE_HIERARCHY_DENORM2;
2963
2964 /* Bug#2643047 - This procedure is added so as to populate organizations for REPORTING pa_org_use_type
2965 when a new organization is added in the hierarchy. That is added in per_org_structure_elements table.
2966 The call to this procedure will be made from maintain_org_hist_bri
2967 The newly added organization is x_organiation_id_child which is added under x_organization_id_parent*/
2968
2969 Procedure populate_reporting_orgs(
2970 x_org_version_id in number,
2971 x_organization_id_child in number,
2972 x_organization_id_parent in number,
2973 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2974 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2975 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
2976 ) IS
2977 old_stack VARCHAR2(2000);
2978
2979 x_parent_level number;
2980 x_child_level number;
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);
2990
2991 Begin
2992 x_err_code := 0;
2993 old_stack := x_err_stack;
2994 x_err_stack := x_err_stack || 'populate_reporting_orgs';
2995
2996 /* For all operating units where the Org structure
2997 specified is same as the changed Org structure */
2998 for imp_rec in ( select org_id
2999 from pa_implementations_all imp
3000 where org_structure_version_id = x_org_version_id
3001 )
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
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'
3017 and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953 - Added nvl for the org id condition */
3018 exception when NO_DATA_FOUND then
3019 x_exists_in_denorm := 'N';
3020 end;
3021
3022 /*
3023 Take following heirarchy for understanding this piece of logic
3024 org1
3025 / \
3026 org2 org3 -> start org in pa_implementations
3027 / / \
3028 org4 org5 org6
3029 | / \
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
3039 org3 org8 4 2
3040 org3 org9 4 2
3041 org3 org10 4 1
3042 org5 org7 3 2
3043 org6 org8 3 2
3044 org6 org9 3 2
3045 org6 org10 3 1
3046 org9 org10 2 1
3047 org3 org3 4 4
3048 org5 org5 3 3
3049 org6 org6 3 3
3050 org7 org7 2 2
3051 org8 org8 2 2
3052 org9 org9 2 2
3053 org10 org10 1 1
3054 now suppose org11 is added under org10, then all the levels for this hierarchy needs to be changed else not reqd.
3055 So if parent_level is equal to 1, then parent is a leaf node, so when a new org is added to leaf org,
3056 the whole hierarchy levels needs to be changed (all levels need to be incremented by 1).
3057 Else not required.
3058 */
3059
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'
3069 and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953-Added nvl condition for org_id */
3070 x_child_level := 1; /* Child level is lowest, so 1 in this case */
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,
3080 x_organization_id_child,
3081 x_org_version_id,
3082 'REPORTING',
3083 rec.parent_level,
3084 x_child_level,
3085 imp_rec.org_id,
3086 x_err_code,
3087 x_err_stage,
3088 x_err_stack);
3089 end loop;
3090 /* We need to populate a record with x_organization_id_child as parent as well as child */
3091 create_reporting_org(x_organization_id_child,
3092 x_organization_id_child,
3093 x_org_version_id,
3094 'REPORTING',
3095 x_child_level,
3096 x_child_level,
3097 imp_rec.org_id,
3098 x_err_code,
3099 x_err_stage,
3100 x_err_stack);
3101 end if; /* for check of x_exists_in_denorm */
3102
3103 end loop;
3104
3105 x_err_stack := old_stack;
3106 EXCEPTION
3107 WHEN OTHERS THEN
3108 x_err_code := SQLCODE;
3109 x_err_stage := SQLERRM;
3110 RETURN;
3111
3112 end populate_reporting_orgs;
3113
3114
3115 /* Bug#2643047 - This procedure is added to restructure the levels in the table
3116 pa_og_hierarchy_denorm for REPORTING pa_org_use_type when a organization is deleted from the hierarchy.
3117 The call to this procedure will be made from maintain_org_hist_brd.
3118 The deleted organization is x_organiation_id_child */
3119
3120 procedure restructure_rpt_orgs_denorm(
3121 x_org_version_id in number,
3122 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3123 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3124 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
3125 )
3126 IS
3127 old_stack VARCHAR2(2000);
3128
3129 x_exists number;
3130
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
3140 );
3141
3142
3143 Begin
3144 x_err_code := 0;
3145 old_stack := x_err_stack;
3146 x_err_stack := x_err_stack || 'restructure_rpt_orgs_denorm';
3147
3148 /* For all operating units where the Project Org structure
3149 specified is same as the changed Org structure */
3150 for imp_rec in ( select org_id
3151 from pa_implementations_all imp
3152 where org_structure_version_id = x_org_version_id
3153 )
3154 loop
3155
3156 /* check_lower_org_exists is the cursor to find if there is any other organization also at the leaf level
3157 as x_organization_id_child which was deleted, if yes no need to restructure the records else we need to decrement
3158 all the levels so that other organization (s) which are actually now leaf nodes in the hierarchy have their levels as 1
3159 and other organizations in the hierarchy have levels changed accordingly */
3160
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'
3170 and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953 Added nvl for org_id condition */
3171 end if;
3172 close check_lower_org_exists;
3173 end loop;
3174
3175 x_err_stack := old_stack;
3176 EXCEPTION
3177 WHEN OTHERS THEN
3178 x_err_code := SQLCODE;
3179 x_err_stage := SQLERRM;
3180 RETURN;
3181
3182 end restructure_rpt_orgs_denorm;
3183
3184 /* Bug 3649799 - This procedure will be called by statement level trigger for update on
3185 per_org_structure_elements. */
3186
3187 procedure maintain_org_hist_update(x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3188 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3189 x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
3190
3191
3192 /* This cursor is to get all the parents for the organization l_start_org_id in hierarchy l_org_struct_ver_id */
3193
3194 CURSOR all_parents (max_level number,
3195 l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3196 l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
3197 SELECT distinct organization_id_parent
3198 ,(max_level - level + 1) rev_level
3199 FROM per_org_structure_elements a
3200 WHERE a.org_structure_version_id = l_org_struct_ver_id
3201 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3202 AND a.org_structure_version_id = l_org_struct_ver_id
3203 START WITH a.organization_id_parent = l_start_org_id
3204 AND a.org_structure_version_id = l_org_struct_ver_id
3205 ORDER by rev_level desc;
3206
3207 /* Added this cursor for bug 4218163 */
3208 CURSOR proj_exp_check(c_org_struct_version_id IN per_org_structure_elements.org_structure_version_id%TYPE)
3209 IS
3210 SELECT 'Y' FROM pa_implementations_all
3211 WHERE proj_org_structure_version_id = c_org_struct_version_id
3212 OR exp_org_structure_version_id = c_org_struct_version_id;
3213
3214 l_version_id per_org_structure_elements.org_structure_version_id%TYPE;
3215 l_new_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3216 l_new_child_org_id per_org_structure_elements.organization_id_child%TYPE;
3217 v_max_level number;
3218 l_imp_proj_exp VARCHAR2(1);
3219
3220 -- Added for bug#5952671
3221
3222 cursor pa_org_hier_rec (l_org_id_parent per_org_structure_elements.organization_id_parent%TYPE,
3223 l_level number,
3224 l_version_id per_org_structure_elements.org_structure_version_id%TYPE) IS
3225 SELECT organization_id_child
3226 , l_level - level
3227 FROM per_org_structure_elements a
3228 WHERE a.org_structure_version_id = l_version_id
3229 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3230 AND a.org_structure_version_id = l_version_id
3231 START WITH a.organization_id_parent = l_org_id_parent
3232 AND a.org_structure_version_id = l_version_id;
3233
3234 cursor pa_org_hier_sec_rec (l_version_id per_org_structure_elements.org_structure_version_id%TYPE,
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;
3244
3245 cursor all_org_id (l_version_id pa_implementations_all.org_structure_version_id%TYPE,
3246 l_start_org_id IN pa_implementations_all.start_organization_id%TYPE) IS
3247 SELECT org_id FROM pa_implementations_all
3248 WHERE org_structure_version_id = l_version_id
3249 and start_organization_id = l_start_org_id;
3250
3251 l_child_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3252 l_child_level_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3253 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3254
3255 l_tmp_org_id pa_implementations_all.org_id%type;
3256 l_plsql_max_array_size CONSTANT NUMBER := 1000;
3257 -- End for bug#5952671
3258
3259 --Start 5934849
3260 TYPE l_str_version_id_tbltype IS TABLE OF
3261 per_org_structure_elements.org_structure_version_id%TYPE index by binary_integer;
3262
3263 L_STR_VERSION_ID_TMP l_str_version_id_tbltype;
3264 L_STR_VERSION_ID l_str_version_id_tbltype;
3265 l_str_exists varchar2(1) :='N';
3266 --End 5934849
3267
3268 BEGIN
3269 --Start 5934849
3270 --Get the org_structure_version_id into L_STR_VERSION_ID_TMP tab
3271 --for the rowids in newRows tab
3272 FOR k in pa_org_utils.newRows.first..pa_org_utils.newRows.last Loop
3273 select distinct org_structure_version_id
3274 into L_STR_VERSION_ID_TMP(L_STR_VERSION_ID_TMP.count+1)
3275 from per_org_structure_elements
3276 where
3277 ROWID = pa_org_utils.newRows(k);
3278 End loop;
3279
3280 --Get the distinct rowids into L_STR_VERSION_ID tab
3281 --from L_STR_VERSION_ID_TMP tab
3282 FOR i IN 1 .. L_STR_VERSION_ID_TMP.COUNT LOOP
3283 l_str_exists := 'N';
3284 FOR j IN 1 .. L_STR_VERSION_ID.COUNT LOOP
3285 if L_STR_VERSION_ID_TMP(i) = L_STR_VERSION_ID(j) then
3286 l_str_exists := 'Y';
3287 Exit;
3288 end if;
3289 end loop;
3290 if l_str_exists = 'N' then
3291 L_STR_VERSION_ID(L_STR_VERSION_ID.count+1) := L_STR_VERSION_ID_TMP(i);
3292 End if;
3293 end loop;
3294
3295
3296 FOR i IN 1 .. L_STR_VERSION_ID.COUNT LOOP
3297
3298 l_version_id := L_STR_VERSION_ID(i);
3299 --End 5934849
3300
3301
3302 /*FOR i IN 1 .. pa_org_utils.newRows.COUNT LOOP commented for bug 5934849 */
3303
3304
3305 /* Fetching the Organization Hierarchy version id, the new parent organization id and child organization id
3306
3307 SELECT org_structure_version_id, organization_id_parent, organization_id_child
3308 INTO l_version_id, l_new_parent_org_id, l_new_child_org_id
3309 FROM per_org_structure_elements
3310 WHERE ROWID = pa_org_utils.newRows(i); */
3311 maintain_pa_all_org( x_org_version_id => l_version_id,
3312 x_err_code => x_err_code,
3313 x_err_stage => x_err_stage,
3314 x_err_stack => x_err_stack);
3315
3316 maintain_projexp_org_update( p_version_id => l_version_id,
3317 p_org_use_type => 'PROJECTS',
3318 x_err_code => x_err_code,
3319 x_err_stage => x_err_stage,
3320 x_err_stack => x_err_stack);
3321
3322 maintain_projexp_org_update( p_version_id => l_version_id,
3323 p_org_use_type => 'EXPENDITURES',
3324 x_err_code => x_err_code,
3325 x_err_stage => x_err_stage,
3326 x_err_stack => x_err_stack);
3327
3328 /* Bug 4218163 - Checking whether the hierarchy version is used in PA, calling rebuild
3329 for TP_SCHEDULE only if the hierarchy is used as Project / Expenditure hierarchy in PA */
3330
3331 OPEN proj_exp_check(l_version_id);
3332 FETCH proj_exp_check INTO l_imp_proj_exp;
3333 IF proj_exp_check%FOUND THEN
3334 populate_hier_denorm_sch( p_org_version_id => l_version_id
3335 ,x_err_code => x_err_code
3336 ,x_err_stage => x_err_stage
3337 ,x_err_stack => x_err_stack );
3338 END IF;
3339 CLOSE proj_exp_check;
3340
3341 /* The Code below is to populate the denorm data for Reporting pa_org_use_type.
3342 For each org id which uses the hierarchy updated, we first delete the data from denorm table
3343 Then get the maximum level in that hierarchy.
3344 Then insert the appropriate combinations in the denorm table.
3345 The code is similar to the populate_hierarchy_denorm2 procedure added in 115.25 version of this file */
3346
3347 /* Commented and added for bug#5952671
3348 FOR imp_rec IN (SELECT org_id, start_organization_id FROM
3349 pa_implementations_all
3350 WHERE org_structure_version_id = l_version_id) */
3351 FOR imp_rec IN (SELECT distinct start_organization_id FROM
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 */
3361 l_tmp_org_id := NULL;
3362 l_org_id_tbl.delete;
3363 OPEN all_org_id(l_version_id, imp_rec.start_organization_id);
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);
3373 /* End bug#5952671 */
3374
3375 SELECT MAX(level+1)
3376 INTO v_max_level
3377 FROM per_org_structure_elements a
3378 WHERE a.org_structure_version_id = l_version_id
3379 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3380 AND a.org_structure_version_id = l_version_id
3381 START WITH a.organization_id_parent = imp_rec.start_organization_id
3382 AND a.org_structure_version_id = l_version_id;
3383
3384 /* Added for bug#5952671 */
3385 FOR c1rec IN all_parents (v_max_level, l_version_id, imp_rec.start_organization_id) LOOP
3386
3387 OPEN pa_org_hier_rec(c1rec.organization_id_parent
3388 ,c1rec.rev_level
3389 , l_version_id);
3390 LOOP
3391 l_child_organization_id_tbl.delete;
3392 l_child_level_tbl.delete;
3393 FETCH pa_org_hier_rec BULK COLLECT INTO
3394 l_child_organization_id_tbl
3395 ,l_child_level_tbl
3396 LIMIT l_plsql_max_array_size;
3397
3398 IF nvl(l_child_organization_id_tbl.last,0) > 0 THEN
3399 FOR imp1_rec IN (SELECT org_id FROM
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
3409 , creation_date
3410 , created_by
3411 , last_update_date
3412 , last_updated_by
3413 , last_update_login
3414 , parent_level
3415 , child_level
3416 , org_id
3417 ) values
3418 (
3419 c1rec.organization_id_parent
3420 ,l_child_organization_id_tbl(i)
3421 ,l_version_id
3422 ,'REPORTING'
3423 ,sysdate
3424 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3425 ,sysdate
3426 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3427 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3428 ,c1rec.rev_level
3429 ,l_child_level_tbl(i)
3430 ,imp1_rec.org_id
3431 );
3432 END LOOP;
3433 END IF;
3434 EXIT WHEN nvl(l_child_organization_id_tbl.last,0) < l_plsql_max_array_size;
3435 END LOOP;
3436 CLOSE pa_org_hier_rec;
3437 END LOOP;
3438
3439 OPEN pa_org_hier_sec_rec (l_version_id ,
3440 imp_rec.start_organization_id,
3441 l_tmp_org_id);
3442 LOOP
3443 l_child_organization_id_tbl.delete;
3444 l_child_level_tbl.delete;
3445 FETCH pa_org_hier_sec_rec BULK COLLECT INTO
3446 l_child_organization_id_tbl
3447 ,l_child_level_tbl
3448 LIMIT l_plsql_max_array_size;
3449
3450 IF nvl(l_child_organization_id_tbl.last,0) > 0 THEN
3451 FOR imp1_rec IN (SELECT org_id FROM
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
3461 , creation_date
3462 , created_by
3463 , last_update_date
3464 , last_updated_by
3465 , last_update_login
3466 , parent_level
3467 , child_level
3468 , org_id
3469 ) values
3470 (
3471 l_child_organization_id_tbl(i)
3472 ,l_child_organization_id_tbl(i)
3473 ,l_version_id
3474 ,'REPORTING'
3475 ,sysdate
3476 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3477 ,sysdate
3478 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3479 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3480 ,l_child_level_tbl(i)
3481 ,l_child_level_tbl(i)
3482 ,imp1_rec.org_id
3483 );
3484 END LOOP;
3485 END IF;
3486 EXIT WHEN nvl(l_child_organization_id_tbl.last,0) < l_plsql_max_array_size;
3487 END LOOP;
3488 CLOSE pa_org_hier_sec_rec;
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
3498 , creation_date
3499 , created_by
3500 , last_update_date
3501 , last_updated_by
3502 , last_update_login
3503 , parent_level
3504 , child_level
3505 , org_id
3506 ) VALUES
3507 (
3508 imp_rec.start_organization_id
3509 ,imp_rec.start_organization_id
3510 ,l_version_id
3511 ,'REPORTING'
3512 ,sysdate
3513 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3514 ,sysdate
3515 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3516 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3517 ,v_max_level
3518 ,v_max_level
3519 ,imp1_rec.org_id
3520 );
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
3530 , creation_date
3531 , created_by
3532 , last_update_date
3533 , last_updated_by
3534 , last_update_login
3535 , parent_level
3536 , child_level
3537 , org_id
3538 )
3539 (SELECT c1rec.organization_id_parent
3540 , organization_id_child
3541 , l_version_id
3542 , 'REPORTING'
3543 , sysdate
3544 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3545 , sysdate
3546 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3547 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3548 , c1rec.rev_level
3549 , c1rec.rev_level - level
3550 , imp_rec.org_id
3551 FROM per_org_structure_elements a
3552 WHERE a.org_structure_version_id = l_version_id
3553 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3554 AND a.org_structure_version_id = l_version_id
3555 START WITH a.organization_id_parent = c1rec.organization_id_parent
3556 AND a.org_structure_version_id = l_version_id
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
3566 , creation_date
3567 , created_by
3568 , last_update_date
3569 , last_updated_by
3570 , last_update_login
3571 , parent_level
3572 , child_level
3573 , org_id
3574 )
3575 (SELECT child_organization_id
3576 , child_organization_id
3577 , org_hierarchy_version_id
3578 , 'REPORTING'
3579 , sysdate
3580 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3581 , sysdate
3582 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
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
3592 UNION ALL
3593 SELECT imp_rec.start_organization_id
3594 , imp_rec.start_organization_id
3595 , l_version_id
3596 , 'REPORTING'
3597 , sysdate
3598 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3599 , sysdate
3600 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3601 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3602 , v_max_level
3603 , v_max_level
3604 , imp_rec.org_id
3605 FROM dual
3606 ); */
3607
3608 END LOOP;
3609
3610 END LOOP;
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
3620 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3621 x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
3622 l_imp_proj_exp VARCHAR2(1);
3623 l_exist_recs VARCHAR2(1);
3624 class_org_true varchar2(3);
3625 l_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3626 l_child_org_id per_org_structure_elements.organization_id_parent%TYPE;
3627
3628 TYPE num2_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3629 l_child_org_id2_var num2_tbl_type;
3630
3631 Cursor start_orgs (c_version_id in number) IS
3632 SELECT decode(p_org_use_type, 'PROJECTS', proj_start_org_id, 'EXPENDITURES', exp_start_org_id) start_organization_id
3633 FROM pa_implementations_all imp
3634 WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
3635 'EXPENDITURES', exp_org_structure_version_id) = c_version_id;
3636
3637
3638 CURSOR c_get_new_org ( c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3639 c_start_org_id IN per_org_structure_elements.organization_id_child%TYPE)
3640 IS
3641 SELECT c_start_org_id FROM dual
3642 UNION ALL
3643 SELECT organization_id_child
3644 FROM per_org_structure_elements
3645 WHERE org_structure_version_id = c_org_struct_ver_id
3646 CONNECT BY PRIOR organization_id_child = organization_id_parent
3647 AND org_structure_version_id = c_org_struct_ver_id
3648 START WITH organization_id_parent = c_start_org_id
3649 AND org_structure_version_id = c_org_struct_ver_id;
3650
3651 CURSOR c_get_sub_orgs (c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3652 c_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
3653 SELECT c_org_id
3654 FROM dual
3655 UNION ALL
3656 SELECT organization_id_child
3657 FROM per_org_structure_elements
3658 WHERE org_structure_version_id = c_org_struct_ver_id
3659 CONNECT BY PRIOR organization_id_child = organization_id_parent
3660 AND org_structure_version_id = c_org_struct_ver_id
3661 START WITH organization_id_parent = c_org_id
3662 AND org_structure_version_id = c_org_struct_ver_id ;
3663
3664 BEGIN
3665
3666 BEGIN
3667
3668 SELECT 'Y' INTO l_imp_proj_exp
3669 FROM pa_implementations_all
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
3679 WHEN NO_DATA_FOUND THEN
3680 l_imp_proj_exp := 'N';
3681 END;
3682
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;
3692 EXCEPTION
3693 WHEN NO_DATA_FOUND THEN
3694 l_exist_recs := 'N';
3695 END;
3696
3697 IF l_exist_recs = 'N' THEN
3698 /* Commented for 9811574
3699 OPEN c_get_new_org(p_version_id, imp_rec.start_organization_id);
3700 LOOP
3701 FETCH c_get_new_org INTO l_parent_org_id;
3702 EXIT WHEN c_get_new_org%NOTFOUND;
3703 OPEN c_get_sub_orgs(p_version_id, l_parent_org_id);
3704 LOOP
3705 FETCH c_get_sub_orgs BULK COLLECT INTO l_child_org_id2_var LIMIT 10000; --bug6444664
3706 EXIT WHEN l_child_org_id2_var.count = 0;--8915976
3707 */
3708 /*
3709 BEGIN
3710 SELECT 'YES' INTO class_org_true
3711 FROM hr_organization_information
3712 WHERE organization_id = l_child_org_id
3713 AND ORG_INFORMATION_CONTEXT = 'CLASS'
3714 AND ORG_INFORMATION1 = decode(p_org_use_type, 'PROJECTS', 'PA_PROJECT_ORG',
3715 'EXPENDITURES', 'PA_EXPENDITURE_ORG')
3716 AND ORG_INFORMATION2 = 'Y';
3717 EXCEPTION WHEN NO_DATA_FOUND THEN
3718 class_org_true := 'NO';
3719 END ;
3720
3721 IF class_org_true = 'YES' THEN
3722 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id=> l_parent_org_id
3723 , p_child_organization_id => l_child_org_id
3724 , p_org_hierarchy_version_id=> p_version_id
3725 , p_pa_org_use_type => p_org_use_type
3726 , x_err_code => x_err_code
3727 , x_err_stage => x_err_stage
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
3737 , created_by
3738 , last_update_date
3739 , last_updated_by
3740 , last_update_login
3741 )
3742 SELECT l_parent_org_id
3743 , l_child_org_id2_var(i)
3744 , p_version_id
3745 , p_org_use_type
3746 , sysdate
3747 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
3748 , sysdate
3749 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
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)
3759 and exists
3760 (SELECT 'YES'
3761 FROM hr_organization_information
3762 WHERE organization_id = l_child_org_id2_var(i)
3763 AND ORG_INFORMATION_CONTEXT = 'CLASS'
3764 AND ORG_INFORMATION1 = decode(p_org_use_type, 'PROJECTS', 'PA_PROJECT_ORG', 'EXPENDITURES', 'PA_EXPENDITURE_ORG'));
3765 l_child_org_id2_var.DELETE;
3766 END LOOP;
3767 CLOSE c_get_sub_orgs;
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
3777 , created_by
3778 , last_update_date
3779 , last_updated_by
3780 , last_update_login
3781 )
3782 SELECT per_org.parent
3783 , per_org.child
3784 , p_version_id
3785 , p_org_use_type
3786 , sysdate
3787 , 1 -- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
3788 , sysdate
3789 , 1 -- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
3790 , 1 -- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
3791 from (
3792 SELECT
3793 to_number(substrb(sys_connect_by_path(child_outer, '.'),
3794 instr(sys_connect_by_path(child_outer, '.'), '.',1,1)+1,
3795 decode(instr(sys_connect_by_path(child_outer, '.'), '.',1,2), 0,
3796 length(sys_connect_by_path(child_outer, '.'))-1,
3797 instr(sys_connect_by_path(child_outer, '.'), '.',1,2)-2))) parent,
3798 to_number(substrb(sys_connect_by_path(child_outer, '.'),
3799 instr(sys_connect_by_path(child_outer, '.'), '.',1,level)+1)) child
3800 FROM
3801 (
3802 select organization_id_child child_outer,
3803 organization_id_parent parent_outer,
3804 org_structure_version_id
3805 from
3806 per_org_structure_elements
3807 where org_structure_version_id = p_version_id
3808 CONNECT BY PRIOR organization_id_child =
3809 organization_id_parent
3810 AND org_structure_version_id = p_version_id
3811 START WITH organization_id_parent =
3812 imp_rec.start_organization_id
3813 AND org_structure_version_id = p_version_id
3814
3815 union
3816 select imp_rec.start_organization_id child_outer,
3817 null parent_outer,
3818 p_version_id org_structure_version_id
3819 from dual)
3820 where org_structure_version_id = p_version_id
3821 CONNECT BY PRIOR child_outer = parent_outer
3822 START WITH org_structure_version_id = p_version_id) per_org
3823 , HR_ORGANIZATION_INFORMATION
3824 where organization_id = per_org.child
3825 and org_information1 = 'PA_PROJECT_ORG'
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);
3835 /* Fix for bug 9811574 - End */
3836
3837 END IF;
3838 END LOOP;
3839
3840 END maintain_projexp_org_update;
3841
3842
3843 /* Bug 5934849 - This procedure will be called for maintaining project and expenditure
3844 data in pa_all_organizations table. */
3845
3846 Procedure maintain_pa_all_org(x_org_version_id in number,
3847 x_err_code in out NOCOPY number,
3848 x_err_stage in out NOCOPY varchar2,
3849 x_err_stack in out NOCOPY varchar2) IS
3850
3851 v_start_org_id number;
3852 v_org_id number;
3853
3854 CURSOR cur_proj_new_org IS
3855 SELECT se.organization_id_child organization_id
3856 FROM per_org_structure_elements se
3857 WHERE org_structure_version_id = x_org_version_id
3858 AND EXISTS (select 'X'
3859 from hr_organization_information info
3860 where info.organization_id = se.organization_id_child
3861 and info.org_information1 = 'PA_PROJECT_ORG'
3862 and info.org_information_context||'' = 'CLASS'
3863 and info.org_information2 = 'Y')
3864 CONNECT BY PRIOR se.organization_id_child
3865 = se.organization_id_parent
3866 AND org_structure_version_id = x_org_version_id
3867 START WITH se.organization_id_parent = v_start_org_id
3868 AND org_structure_version_id = x_org_version_id
3869 UNION
3870 SELECT v_start_org_id FROM Sys.dual -- Added exists clause for Bug# 1650520
3871 WHERE EXISTS (select 'X'
3872 from hr_organization_information info
3873 where info.org_information1 = 'PA_PROJECT_ORG'
3874 and info.org_information_context||'' = 'CLASS'
3875 and info.org_information2 = 'Y'
3876 and info.organization_id = v_start_org_id);
3877
3878 CURSOR cur_exp_new_org IS
3879 SELECT se.organization_id_child organization_id
3880 FROM per_org_structure_elements se
3881 WHERE org_structure_version_id = x_org_version_id
3882 AND EXISTS (select 'X'
3883 from hr_organization_information info
3884 where info.organization_id = se.organization_id_child
3885 and info.org_information1 = 'PA_EXPENDITURE_ORG'
3886 and info.org_information_context||'' = 'CLASS'
3887 and info.org_information2 = 'Y')
3888 CONNECT BY PRIOR se.organization_id_child
3889 = se.organization_id_parent
3890 AND org_structure_version_id = x_org_version_id
3891 START WITH se.organization_id_parent = v_start_org_id
3892 AND org_structure_version_id = x_org_version_id
3893 UNION
3894 SELECT v_start_org_id FROM Sys.dual
3895 where EXISTS (select 'X' /* Made changes for BUG 1180635*/
3896 from hr_organization_information info
3897 where info.org_information1 = 'PA_EXPENDITURE_ORG'
3898 and info.org_information_context||'' = 'CLASS'
3899 and info.org_information2 = 'Y'
3900 and info.organization_id = v_start_org_id);
3901
3902
3903 Begin
3904
3905
3906 for imp_rec in ( select org_id,proj_start_org_id
3907 from pa_implementations_all imp
3908 where proj_org_structure_version_id = x_org_version_id
3909 )
3910
3911 loop
3912 v_start_org_id := imp_rec.proj_start_org_id;
3913 v_org_id := imp_rec.org_id;
3914
3915 UPDATE pa_all_organizations
3916 SET Inactive_Date = TRUNC(SYSDATE)
3917 WHERE Pa_Org_Use_Type = 'PROJECTS'
3918 AND Org_id = v_org_id and Inactive_Date is null ;
3919
3920 FOR rec_proj_new_org IN cur_proj_new_org LOOP
3921
3922 /* Update all Organizations (Projects)
3923 in the hierarchy with inactive date as NULL.
3924 Union is to include the start organization id in the update */
3925
3926 UPDATE pa_all_organizations
3927 SET Inactive_Date = NULL
3928 WHERE Pa_Org_Use_Type = 'PROJECTS'
3929 AND Org_id = v_org_id
3930 AND Organization_id = rec_proj_new_org.organization_id;
3931
3932 /* In case there are no corresponding records for the
3933 Organization hierarchy in the Pa_all_Organizations, then,
3934 create these records in the Pa_All_Organizations
3935 with Inactive_Date as NULL */
3936
3937 IF SQL%NOTFOUND THEN
3938 INSERT INTO Pa_All_Organizations
3939 (organization_id,
3940 org_id,
3941 pa_org_use_type,
3942 inactive_date)
3943 VALUES
3944 (rec_proj_new_org.organization_id,
3945 v_org_id,
3946 'PROJECTS',
3947 NULL);
3948 END IF;
3949 END Loop;
3950
3951 END LOOP;
3952
3953 for imp_rec in ( select org_id, exp_start_org_id
3954 from pa_implementations_all imp
3955 where exp_org_structure_version_id = x_org_version_id
3956 )
3957
3958 loop
3959 v_start_org_id := imp_rec.exp_start_org_id;
3960 v_org_id := imp_rec.org_id;
3961
3962 UPDATE pa_all_organizations
3963 SET Inactive_Date = TRUNC(SYSDATE)
3964 WHERE Pa_Org_Use_Type = 'EXPENDITURES'
3965 AND Org_id = v_org_id and Inactive_Date is null ;
3966
3967 FOR rec_exp_new_org IN cur_exp_new_org LOOP
3968
3969 /* Update all Organizations (Projects)
3970 in the hierarchy with inactive date as NULL.
3971 Union is to include the start organization id in the update */
3972
3973 UPDATE pa_all_organizations
3974 SET Inactive_Date = NULL
3975 WHERE Pa_Org_Use_Type = 'EXPENDITURES'
3976 AND Org_id = v_org_id
3977 AND Organization_id = rec_exp_new_org.organization_id;
3978
3979 /* In case there are no corresponding records for the new
3980 Organization hierarchy in the Pa_all_Organizations, then,
3981 create these records in the Pa_All_Organizations
3982 with Inactive_Date as NULL */
3983
3984 IF SQL%NOTFOUND THEN
3985 INSERT INTO Pa_All_Organizations
3986 (organization_id,
3987 org_id,
3988 pa_org_use_type,
3989 inactive_date)
3990 VALUES
3991 (rec_exp_new_org.organization_id,
3992 v_org_id,
3993 'EXPENDITURES',
3994 NULL);
3995 END IF;
3996 END Loop;
3997
3998 END LOOP;
3999
4000 end maintain_pa_all_org;
4001
4002 END;
[Home] [Help]