1 PACKAGE BODY pa_org_utils AS
2 /* $Header: PAXORUTB.pls 120.7.12010000.2 2008/09/29 17:29:32 jcgeorge 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
280 Begin
281 x_err_code := 0;
282 old_stack := x_err_stack;
283 x_err_stack := x_err_stack || '->Pa_org_utils.maintain_org_history';
284
285 /* Project Orgs */
286 if ( (nvl(x_old_proj_org_version_id,x_new_proj_org_version_id +1) <> x_new_proj_org_version_id )
287 or
288 (nvl(x_old_proj_start_org_id,x_new_proj_start_org_id +1) <> x_new_proj_start_org_id )
289 ) then
290
291 insert into pa_all_organizations
292 (organization_id,
293 org_id,
294 pa_org_use_type )
295 ( (select se.organization_id_child
296 ,x_org_id
297 ,'PROJECTS'
298 from per_org_structure_elements se
299 where org_structure_version_id = x_new_proj_org_version_id
300 AND EXISTS (select 'X'
301 from hr_organization_information info
302 where info.organization_id = se.organization_id_child
303 and info.org_information1 = 'PA_PROJECT_ORG'
304 and info.org_information_context||'' = 'CLASS'
305 and info.org_information2 = 'Y')
306 connect by prior se.organization_id_child =
307 se.organization_id_parent
308 and org_structure_version_id = x_new_proj_org_version_id
309 start with se.organization_id_parent = x_new_proj_start_org_id
310 and org_structure_version_id = x_new_proj_org_version_id
311 union
312 select x_new_proj_start_org_id
313 ,x_org_id
314 ,'PROJECTS'
315 from sys.duaL /* Exists clause added for bug#2591146 */
316 where EXISTS (select 'X'
317 from hr_organization_information info
318 where info.organization_id = x_new_proj_start_org_id
319 and info.org_information1 = 'PA_PROJECT_ORG'
320 and info.org_information_context||'' = 'CLASS'
321 and info.org_information2 = 'Y')
322 )
323 minus
324 (select organization_id,
325 org_id,
326 pa_org_use_type
327 from pa_all_organizations
328 where pa_org_use_type = 'PROJECTS'
329 and org_id = x_org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
330 )
331 );
332 end if;
333
334 --- Delete all rows from pa_cc_tp_schedule_line_lkp
335 delete from pa_cc_tp_schedule_line_lkp;
336
337 --- changes for pa_org_hierarchy_denorm table
338 if (nvl(x_old_proj_start_org_id,x_new_proj_start_org_id +1) <> x_new_proj_start_org_id ) then
339 ---- start org is changed, update only if org is moved up
340
341 -- Bug 3795903- Modified the call to check_org_exists
342
343 open check_org_exists(x_old_proj_org_version_id ,x_old_proj_start_org_id,x_new_proj_start_org_id);
344 fetch check_org_exists into l_parent_org_id;
345 if check_org_exists%notfound then
346 rebuild_tab := 'Y';
347 end if;
348 close check_org_exists; /* Added for bug 1736069 */
349 end if;
350
351 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
352
353 --- org hierarchy is changed or version is changed
354 -- Bug 3795903- Modified the call to c_get_new_org by putting x_new_proj_start_org_id in the call
355 open c_get_new_org(x_new_proj_org_version_id,x_new_proj_start_org_id);
356 loop
357 fetch c_get_new_org into l_parent_org_id;
358 exit when c_get_new_org%notfound;
359
360 -- Bug 3795903- Modified the call to c_get_sub_orgs by putting x_new_proj_org_version_id in the call
361 open c_get_sub_orgs(l_parent_org_id ,x_new_proj_org_version_id);
362 loop
363 fetch c_get_sub_orgs into l_child_org_id;
364 exit when c_get_sub_orgs%notfound;
365 begin
366 select 'YES' into proj_org_true
367 from hr_organization_information
368 where organization_id = l_child_org_id
369 and ORG_INFORMATION_CONTEXT = 'CLASS'
370 and ORG_INFORMATION1 = 'PA_PROJECT_ORG'
371 and ORG_INFORMATION2 = 'Y';
372 exception when no_data_found then
373 proj_org_true := 'NO';
374 end ;
375
376 if proj_org_true = 'YES' then
377
378 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id=> l_parent_org_id
379 , p_child_organization_id => l_child_org_id
380 , p_org_hierarchy_version_id=> x_new_proj_org_version_id
381 , p_pa_org_use_type => 'PROJECTS'
385 end if;
382 , x_err_code => x_err_code
383 , x_err_stage => x_err_stage
384 , x_err_stack => x_err_stack);
386 end loop;
387 close c_get_sub_orgs;
388 end loop;
389 close c_get_new_org;
390
391 end if;
392
393
394
395 /* Exp orgs */
396 if ( (nvl(x_old_exp_org_version_id,x_new_exp_org_version_id +1) <> x_new_exp_org_version_id )
397 or
398 (nvl(x_old_exp_start_org_id,x_new_exp_start_org_id +1) <> x_new_exp_start_org_id )
399 ) then
400
401 insert into pa_all_organizations
402 (organization_id,
403 org_id,
404 pa_org_use_type)
405 ( (select se.organization_id_child
406 ,x_org_id
407 ,'EXPENDITURES'
408 from per_org_structure_elements se
409 where org_structure_version_id = x_new_exp_org_version_id
410 AND EXISTS (select 'X'
411 from hr_organization_information info
412 where info.organization_id = se.organization_id_child
413 and info.org_information1 = 'PA_EXPENDITURE_ORG'
414 and info.org_information_context||'' = 'CLASS'
415 and info.org_information2 = 'Y')
416 connect by prior se.organization_id_child =
417 se.organization_id_parent
418 and org_structure_version_id = x_new_exp_org_version_id
419 start with se.organization_id_parent = x_new_exp_start_org_id
420 and org_structure_version_id = x_new_exp_org_version_id
421 union
422 select x_new_exp_start_org_id
423 ,x_org_id
424 ,'EXPENDITURES'
425 from sys.duaL /* Exists clause added for bug#2591146 */
426 where EXISTS (select 'X'
427 from hr_organization_information info
428 where info.organization_id = x_new_exp_start_org_id
429 and info.org_information1 = 'PA_EXPENDITURE_ORG'
430 and info.org_information_context||'' = 'CLASS'
431 and info.org_information2 = 'Y')
432 )
433 minus
434 (select organization_id,
435 org_id,
436 pa_org_use_type
437 from pa_all_organizations
438 where pa_org_use_type = 'EXPENDITURES'
439 and org_id = x_org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
440 )
441 );
442
443 end if;
444
445 --- changes for pa_org_hierarchy_denorm table
446 if (nvl(x_old_exp_start_org_id,x_new_exp_start_org_id +1) <> x_new_exp_start_org_id ) then
447 ---- start org is changed, update only if org is moved up
448
449 -- Bug 3795903- Modified the call to check_org_exists
450 open check_org_exists(x_old_exp_org_version_id ,x_old_exp_start_org_id,x_new_exp_start_org_id);
451
452 fetch check_org_exists into l_parent_org_id;
453 if check_org_exists%notfound then
454 rebuild_tab := 'Y';
455 end if;
456 close check_org_exists; /* Added for bug 1736069 */
457 end if;
458
459 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
460 --- org hierarchy is changed or version is changed
461
462 --- Bug 3795903- Modified the call to c_get_new_org
463 open c_get_new_org(x_new_exp_org_version_id,x_new_exp_start_org_id);
464 loop
465 fetch c_get_new_org into l_parent_org_id;
466 exit when c_get_new_org%notfound;
467
468 --- Bug 3795903- Modified the call to c_get_sub_orgs
469 open c_get_sub_orgs(l_parent_org_id,x_new_exp_org_version_id);
470 loop
471 fetch c_get_sub_orgs into l_child_org_id;
472 exit when c_get_sub_orgs%notfound;
473 begin
474 select 'YES' into exp_org_true
475 from hr_organization_information
476 where organization_id = l_child_org_id
477 and ORG_INFORMATION_CONTEXT = 'CLASS'
478 and ORG_INFORMATION1 = 'PA_EXPENDITURE_ORG'
479 and ORG_INFORMATION2 = 'Y';
480 exception when no_data_found then
481 exp_org_true := 'NO';
482 end ;
483
484 if exp_org_true = 'YES' then
485
486 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id=> l_parent_org_id
487 , p_child_organization_id => l_child_org_id
488 , p_org_hierarchy_version_id=> x_new_exp_org_version_id
489 , p_pa_org_use_type => 'EXPENDITURES'
490 , x_err_code => x_err_code
491 , x_err_stage => x_err_stage
492 , x_err_stack => x_err_stack);
493 end if;
494 end loop;
495 close c_get_sub_orgs;
496 end loop;
497 close c_get_new_org;
501 /* Reporting Orgs */
498
499 end if;
500
502 If ( (nvl(x_old_org_structure_version_id,x_new_org_structure_version_id ) <>
503 x_new_org_structure_version_id )
504 or
505 (nvl(x_old_start_organization_id,x_new_start_organization_id ) <>
506 x_new_start_organization_id ) ) then
507 -- call procedure populate hierarchy denorm to insert rows into
508 -- into pa_org_hierarchy_denorm for reporting type of organizations
509
510 /* Bug 2976953 - Changed populate_hierarchy_denorm to populate_hierarchy_denorm2 and also added
511 the parameter p_org_id for that call */
512
513 pa_org_utils.populate_hierarchy_denorm2
514 ( p_org_version_id => x_new_org_structure_version_id
515 ,p_organization_id_parent=> x_new_start_organization_id
516 ,p_organization_id_child => 1
517 ,p_org_id => x_org_id /* Added this condition for bug 2976953 */
518 ,x_err_code => x_err_code
519 ,x_err_stage => x_err_stage
520 ,x_err_stack => x_err_stack
521 );
522
523 End If;
524
525 -- Start CC Change
526
527 /* Schedule line Traverse Org Hierarchy */
528
529 -- sri_msg(' START ................. ');
530
531 /* Bug#2723330 - Commenting the rebuild_tab = 'Y' Condition as TP_SCHEDULE is maintained
532 for full hierarchy in HR and will not be affected by movind start org in implementation options */
533
534 if (nvl(x_old_proj_org_version_id,x_new_proj_org_version_id +1)
535 <> x_new_proj_org_version_id) /* or rebuild_tab = 'Y' For Bug#2723330*/
536 then
537
538 /* Bug 4232613. Added the check before calling populate_hier_denorm_sch.
539 If the org version id is already used in the implementation option then the API
540 need not be called. */
541 OPEN proj_exp_check(x_new_proj_org_version_id,x_org_id);
542 FETCH proj_exp_check INTO l_imp_proj_exp;
543 IF proj_exp_check%NOTFOUND THEN
544 populate_hier_denorm_sch( p_org_version_id => x_new_proj_org_version_id
545 ,x_err_code => x_err_code
546 ,x_err_stage => x_err_stage
547 ,x_err_stack => x_err_stack );
548 END IF;
549 close proj_exp_check;
550
551 end if;
552
553 if (nvl(x_old_exp_org_version_id,x_new_exp_org_version_id +1) <>
554 x_new_exp_org_version_id ) /* or rebuild_tab = 'Y' For Bug#2723330 */
555 then
556
557 /* Bug 4232613. Added the check before calling populate_hier_denorm_sch.
558 If the org version id is already used in the implementation option then the API
559 need not be called. */
560 OPEN proj_exp_check(x_new_exp_org_version_id,x_org_id);
561 FETCH proj_exp_check INTO l_imp_proj_exp;
562 IF proj_exp_check%NOTFOUND THEN
563 populate_hier_denorm_sch( p_org_version_id => x_new_exp_org_version_id
564 ,x_err_code => x_err_code
565 ,x_err_stage => x_err_stage
566 ,x_err_stack => x_err_stack );
567 END IF;
568 close proj_exp_check;
569
570 end if;
571
572 -- End CC Change
573
574 x_err_stack := old_stack;
575
576 Exception
577 when others then
578 x_err_code := SQLCODE;
579 x_err_stage := SQLERRM;
580 return;
581 End;
582
583 -- Start CC Change
584
585 Procedure populate_hier_denorm_sch ( p_org_version_id in number,
586 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
587 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
588 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
589
590 l_dummy_level number := 0;
591
592 CURSOR c_get_new_org_sch(lp_org_version_id IN NUMBER,
593 lp_start_org_id IN NUMBER ) IS
594 SELECT l_dummy_level,lp_start_org_id
595 FROM dual
596 UNION ALL
597 SELECT level,organization_id_child
598 FROM per_org_structure_elements
599 WHERE org_structure_version_id = lp_org_version_id
600 CONNECT BY PRIOR organization_id_child = organization_id_parent
601 AND org_structure_version_id = lp_org_version_id
602 START WITH organization_id_parent = lp_start_org_id
603 AND org_structure_version_id = lp_org_version_id;
604
605 CURSOR c_get_sub_orgs_sch (lp_org_version_id IN NUMBER,
606 lp_org_id IN NUMBER) IS
607 SELECT l_dummy_level,lp_org_id
608 FROM dual
609 UNION ALL
610 SELECT level,organization_id_child
611 FROM per_org_structure_elements
612 WHERE org_structure_version_id = lp_org_version_id
613 CONNECT BY PRIOR organization_id_child = organization_id_parent
614 AND org_structure_version_id = lp_org_version_id
615 START WITH organization_id_parent = lp_org_id
616 AND org_structure_version_id = lp_org_version_id;
617
618 /* Commented this cursor for bug#2723330 as this cursor is not used now
619
623 WHERE not exists
620 CURSOR check_org_ver_not_exists ( lp_org_version_id IN NUMBER ) IS
621 SELECT 'x'
622 FROM dual
624 ( SELECT 'x' from pa_org_hierarchy_denorm
625 WHERE org_hierarchy_version_id = nvl(lp_org_version_id,org_hierarchy_version_id)
626 and pa_org_use_type = 'TP_SCHEDULE' );
627
628 */
629
630 -- End CC Change
631
632 -- Start CC Change
633
634 l_tmp_parent_org_id number;
635 l_tmp_child_org_id number;
636 l_tmp_chr varchar2(1);
637
638 -- End CC Change
639
640 -- Start CC Change
641
642 l_start_org_id number;
643 l_plevel number;
644 l_clevel number;
645 l_parent_level number;
646 l_child_level number;
647
648 -- End CC Change
649
650 BEGIN
651
652 /*
653
654 ********** Commented for bug#2723330
655 ********** open check_org_ver_not_exists ( p_org_version_id );
656 ********** LOOP
657 ********** fetch check_org_ver_not_exists into l_tmp_chr;
658 ********** exit when check_org_ver_not_exists%notfound;
659 **********
660
661 */
662
663 l_start_org_id := get_start_org_id_sch(p_org_version_id);
664
665 /* Start of code Added for bug#2723330 */
666
667 BEGIN
668 DELETE from pa_org_hierarchy_denorm
669 WHERE org_hierarchy_version_id = p_org_version_id
670 AND pa_org_use_type = 'TP_SCHEDULE';
671 EXCEPTION
672 WHEN others THEN
673 Null;
674 END;
675
676 /* End of code for bug#2723330 */
677
678 open c_get_new_org_sch ( p_org_version_id ,
679 l_start_org_id );
680
681 LOOP
682 fetch c_get_new_org_sch into l_parent_level, l_tmp_parent_org_id;
683 exit when c_get_new_org_sch%notfound;
684
685 l_plevel := l_parent_level +1 ;
686
687 open c_get_sub_orgs_sch ( p_org_version_id ,
688 l_tmp_parent_org_id );
689
690 LOOP
691
692 fetch c_get_sub_orgs_sch into l_child_level, l_tmp_child_org_id;
693 exit when c_get_sub_orgs_sch%notfound;
694
695 l_clevel := l_child_level + l_parent_level +1 ;
696
697 pa_org_utils.Create_org_hier_denorm_levels
698 (p_parent_organization_id=> l_tmp_parent_org_id
699 , p_child_organization_id => l_tmp_child_org_id
700 , p_org_hierarchy_version_id=> p_org_version_id
701 , p_pa_org_use_type => 'TP_SCHEDULE'
702 , p_parent_level => l_plevel
703 , p_child_level => l_clevel
704 , x_err_code => x_err_code
705 , x_err_stage => x_err_stage
706 , x_err_stack => x_err_stack);
707
708 END LOOP; /* end loop for cursor c_get_sub_orgs_sch */
709
710 close c_get_sub_orgs_sch;
711
712 END LOOP; /* end loop for cursor c_get_new_org_sch */
713
714 close c_get_new_org_sch;
715
716 /*
717
718 ********** Commented for bug#2723330
719
720 ********* END LOOP; end loop for cursor check_org_ver_not_exists
721
722 ********** close check_org_ver_not_exists;
723
724 */
725
726 Exception
727 when others then
728 x_err_code := SQLCODE;
729 x_err_stage := SQLERRM;
730 return;
731 END;
732
733 Procedure maintain_org_hist_bri(x_org_version_id in number,
734 x_organization_id_child in number,
735 x_organization_id_parent in number,
736 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
737 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
738 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
739 v_start_org_id number;
740 v_org_child_id number;
741 l_parent_org_id number;
742 l_exp_org_true varchar2(1);
743 l_proj_org_true varchar2(1);
744 org_exists_flag boolean; /* Added for the bug 2139709 */
745
746 /*
747 Bug#2620151- Commented the old cursor and added new logic for the cursor check_org_exists
748
749 New Logic: Traverse from x_organization_id_parent and get all the parents for this, union
750 with x_organization_id_parent.
751 In this result, check for v_start_org_id, if present then it means that the org
752 x_organization_id_parent is under v_start_org_id.
753
754 Earlier Logic: Traverse from v_start_org_d and get all the childs for this, union with
755 v_start_org_id.
756 In this result, check for x_organization_id_parent, if present then it means
757 that the org x_organization_id_parent is under v_start_org_id.
758 */
759
760 cursor check_org_exists is
761 (
762 select se.organization_id_parent
763 from per_org_structure_elements se
764 where se.org_structure_version_id = x_org_version_id
765 connect by prior se.organization_id_parent =
766 se.organization_id_child
767 and org_structure_version_id = x_org_version_id
768 start with se.organization_id_child =
769 x_organization_id_parent
773 )
770 and org_structure_version_id = x_org_version_id
771 union
772 select x_organization_id_parent from sys.dual
774 intersect
775 (
776 select v_start_org_id from sys.dual
777 );
778
779 /* Commented for Bug#2620151 Added cursor above
780 cursor check_org_exists is
781 (select se.organization_id_child
782 from per_org_structure_elements se
783 where se.org_structure_version_id =
784 x_org_version_id
785 connect by prior se.organization_id_child =
786 se.organization_id_parent
787 and org_structure_version_id = x_org_version_id
788 start with se.organization_id_parent =
789 v_start_org_id
790 and org_structure_version_id = x_org_version_id
791 union
792 select v_start_org_id
793 from sys.dual)
794 intersect
795 (select x_organization_id_parent
796 from sys.dual );
797 */
798
799 --- To populate pa_org_hierarchy_denorm
800 cursor get_all_orgs is
801 (SELECT x_organization_id_parent
802 FROM dual
803 UNION
804 SELECT organization_id_parent
805 FROM per_org_structure_elements
806 WHERE org_structure_version_id = x_org_version_id
807 CONNECT BY PRIOR organization_id_parent= organization_id_child
808 AND org_structure_version_id = x_org_version_id
809 START WITH organization_id_child = x_organization_id_parent
810 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 */
811 union ALL
812 (SELECT x_organization_id_child from dual);
813
814 -- Start CC Change
815
816 cursor get_all_orgs_sch(
817 lp_organization_id_parent in number,
818 lp_organization_id_child in number,
819 lp_org_version_id in number ) is
820 (SELECT lp_organization_id_parent
821 FROM dual
822 UNION
823 SELECT organization_id_parent
824 FROM per_org_structure_elements
825 WHERE org_structure_version_id = lp_org_version_id
826 CONNECT BY PRIOR organization_id_parent= organization_id_child
827 AND org_structure_version_id = lp_org_version_id
828 START WITH organization_id_child = lp_organization_id_parent
829 AND org_structure_version_id = lp_org_version_id)
830 union ALL
831 (SELECT lp_organization_id_child from dual);
832
833 l_plevel number;
834 l_clevel number;
835 l_start_org_id number;
836
837 /*Bug# 2247737*/
838 l_process_schedule_hier varchar2(1);
839 /*End bug# 2247737*/
840
841 -- End CC Change
842 Begin
843
844 /*Bug# 2247737*/
845 l_process_schedule_hier := 'N';
846 /*End bug# 2247737*/
847
848 /* For all operating units where the Project Org structure
849 specified is same as the changed Org structure */
850 for imp_rec in ( select org_id,proj_start_org_id
851 from pa_implementations_all imp
852 where proj_org_structure_version_id = x_org_version_id
853 )
854
855 loop
856
857 /*Bug# 2247737*/
858 l_process_schedule_hier := 'Y';
859 /*End bug# 2247737*/
860
861 v_start_org_id := imp_rec.proj_start_org_id;
862
863 /* Bug 2139709 Begin */
864 org_exists_flag := FALSE;
865
866 IF v_start_org_id = x_organization_id_parent THEN
867 org_exists_flag := TRUE;
868 ELSE
869 open check_org_exists;
870 fetch check_org_exists into v_org_child_id;
871 if check_org_exists%found then /* Added org is below
872 the start project org */
873 org_exists_flag := TRUE;
874 else
875 org_exists_flag := FALSE;
876 end if;
877 close check_org_exists;
878 END IF;
879
880 IF org_exists_flag = TRUE THEN
881 /* Bug 2139709 End */
882
883 insert into pa_all_organizations
884 (organization_id,
885 org_id,
886 pa_org_use_type)
887 (select
888 x_organization_id_child,
889 imp_rec.org_id,
890 'PROJECTS'
891 from sys.dual
892 where exists (select 'x'
893 from hr_organization_information info
894 where info.organization_id = x_organization_id_child
895 and info.org_information1 = 'PA_PROJECT_ORG'
896 and info.org_information_context||'' = 'CLASS'
897 and info.org_information2 = 'Y')
898 and not exists
899 (select 'X'
900 from pa_all_organizations
901 where organization_id = x_organization_id_child
902 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
903 and pa_org_use_type = 'PROJECTS')
904 ) ;
905
906 /* Added for bug 1550990 */
907 if(sql%rowcount=0) then
908 Update pa_all_organizations
909 set inactive_date = NULL
910 where organization_id = x_organization_id_child
911 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
912 and pa_org_use_type = 'PROJECTS'
916 and info.org_information1 = 'PA_PROJECT_ORG'
913 and exists (select 'x' -- EXISTS condition added for bug 2890516
914 from hr_organization_information info
915 where info.organization_id = x_organization_id_child
917 and info.org_information_context||'' = 'CLASS'
918 and info.org_information2 = 'Y');
919
920 end if ;
921
922 --- Delete all rows from pa_cc_tp_schedule_line_lkp
923 delete from pa_cc_tp_schedule_line_lkp;
924
925 -------- for pa_org_hierarchy_denorm
926 open get_all_orgs;
927 loop
928 fetch get_all_orgs into l_parent_org_id;
929 exit when get_all_orgs%notfound;
930
931 begin
932 select 'Y' into l_proj_org_true
933 from hr_organization_information info
934 where info.organization_id = x_organization_id_child
935 and info.org_information1 = 'PA_PROJECT_ORG'
936 and info.org_information_context||'' = 'CLASS'
937 and info.org_information2 = 'Y';
938 exception when no_data_found then
939 l_proj_org_true := 'N';
940 end;
941
942 if l_proj_org_true = 'Y' then
943 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
944 ,p_child_organization_id =>x_organization_id_child
945 ,p_org_hierarchy_version_id => x_org_version_id
946 ,p_pa_org_use_type => 'PROJECTS'
947 , x_err_code => x_err_code
948 , x_err_stage => x_err_stage
949 , x_err_stack => x_err_stack);
950 end if;
951 end loop;
952 close get_all_orgs;
953 --------
954
955 end if;
956
957 end loop;
958
959 /* For all operating units where the Exp Org structure
960 specified is same as the changed Org structure */
961 for imp_rec in ( select org_id, exp_start_org_id
962 from pa_implementations_all imp
963 where exp_org_structure_version_id = x_org_version_id
964 )
965 loop
966
967 /*Bug# 2247737*/
968
969 l_process_schedule_hier := 'Y';
970
971 /*End bug# 2247737*/
972
973 v_start_org_id := imp_rec.exp_start_org_id;
974
975 /* Bug 2139709 Begin */
976 org_exists_flag := FALSE;
977
978 IF v_start_org_id = x_organization_id_parent THEN
979 org_exists_flag := TRUE;
980 ELSE
981 open check_org_exists;
982 fetch check_org_exists into v_org_child_id;
983 if check_org_exists%found then /* Added org is below
984 the start project org */
985 org_exists_flag := TRUE;
986 else
987 org_exists_flag := FALSE;
988 end if;
989 close check_org_exists;
990 END IF;
991
992 IF org_exists_flag = TRUE THEN
993 /* Bug 2139709 End */
994
995 insert into pa_all_organizations
996 (organization_id,
997 org_id,
998 pa_org_use_type)
999 (select
1000 x_organization_id_child,
1001 imp_rec.org_id,
1002 'EXPENDITURES'
1003 from sys.dual
1004 where exists (select 'x'
1005 from hr_organization_information info
1006 where info.organization_id = x_organization_id_child
1007 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1008 and info.org_information_context||'' = 'CLASS'
1009 and info.org_information2 = 'Y')
1010 and not exists
1011 (select 'X'
1012 from pa_all_organizations
1013 where organization_id = x_organization_id_child
1014 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1015 and pa_org_use_type = 'EXPENDITURES')
1016 ) ;
1017
1018 /* Added for bug 1550990 */
1019 if(sql%rowcount=0) then
1020 Update pa_all_organizations
1021 set inactive_date=NULL
1022 where organization_id =x_organization_id_child
1023 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1024 and pa_org_use_type = 'EXPENDITURES'
1025 and exists (select 'x' -- EXISTS condition added for bug 2890516
1026 from hr_organization_information info
1027 where info.organization_id = x_organization_id_child
1028 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1029 and info.org_information_context||'' = 'CLASS'
1030 and info.org_information2 = 'Y');
1031
1032 end if ;
1033
1034 -------- for pa_org_hierarchy_denorm
1035 open get_all_orgs;
1036 loop
1037 fetch get_all_orgs into l_parent_org_id;
1038 exit when get_all_orgs%notfound;
1039
1040 begin
1041 select 'Y' into l_exp_org_true
1042 from hr_organization_information info
1043 where info.organization_id = x_organization_id_child
1044 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1048 l_exp_org_true := 'N';
1045 and info.org_information_context||'' = 'CLASS'
1046 and info.org_information2 = 'Y';
1047 exception when no_data_found then
1049 end;
1050 if l_exp_org_true = 'Y' then
1051 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1052 ,p_child_organization_id =>x_organization_id_child
1053 ,p_org_hierarchy_version_id => x_org_version_id
1054 ,p_pa_org_use_type => 'EXPENDITURES'
1055 , x_err_code => x_err_code
1056 , x_err_stage => x_err_stage
1057 , x_err_stack => x_err_stack);
1058 end if;
1059 end loop;
1060 close get_all_orgs;
1061 --------
1062 end if;
1063
1064 end loop;
1065 -- Start CC Change
1066
1067 /*Bug# 2247737*/
1068
1069 if ( l_process_schedule_hier = 'Y' ) then
1070
1071 /*End bug# 2247737*/
1072 l_start_org_id := get_start_org_id_sch(x_org_version_id);
1073
1074 /* Bug#2645200 - Changed x_organization_id_parent instead of x_organization_id_child as the parameter
1075 for p_child_parent_org_id */
1076
1077 l_clevel := get_org_level(
1078 p_org_version_id => x_org_version_id,
1079 p_child_parent_org_id => x_organization_id_parent, /* 2645200 x_organization_id_child,*/
1080 p_start_org_id => l_start_org_id );
1081
1082 l_clevel := l_clevel + 1; /* Bug#2645200 - Added as the level got is for parent in the call earlier */
1083
1084 open get_all_orgs_sch(x_organization_id_parent,
1085 x_organization_id_child,
1086 x_org_version_id );
1087 LOOP
1088 FETCH get_all_orgs_sch into l_parent_org_id;
1089 exit when get_all_orgs_sch%notfound; /*Bug# 2244878*/
1090
1091 /* 2645200 */
1092 if (l_parent_org_id = x_organization_id_child) then
1093 l_plevel := l_clevel;
1094 else
1095 l_plevel := get_org_level(
1096 p_org_version_id => x_org_version_id,
1097 p_child_parent_org_id => l_parent_org_id,
1098 p_start_org_id => l_start_org_id );
1099 end if;
1100
1101 pa_org_utils.Create_org_hier_denorm_levels
1102 (p_parent_organization_id=> l_parent_org_id
1103 , p_child_organization_id => x_organization_id_child
1104 , p_org_hierarchy_version_id=> x_org_version_id
1105 , p_pa_org_use_type => 'TP_SCHEDULE'
1106 , p_parent_level => l_plevel
1107 , p_child_level => l_clevel
1108 , x_err_code => x_err_code
1109 , x_err_stage => x_err_stage
1110 , x_err_stack => x_err_stack);
1111 END LOOP;
1112 close get_all_orgs_sch; /*Bug# 2244878*/
1113 -- End CC Change
1114
1115 /*Bug# 2247737*/
1116
1117 end if;
1118
1119 /*End bug# 2247737*/
1120
1121 /* Bug#2643047 - Call to populate reporting orgs in pa_org_hierarchy_denorm table */
1122
1123 pa_org_utils.populate_reporting_orgs(x_org_version_id ,
1124 x_organization_id_child ,
1125 x_organization_id_parent,
1126 x_err_code ,
1127 x_err_stage,
1128 x_err_stack );
1129
1130 Exception
1131 when others then
1132 x_err_code := SQLCODE;
1133 x_err_stage := SQLERRM;
1134 return;
1135
1136 End;
1137
1138 Procedure maintain_org_hist_brd(x_org_version_id in number,
1139 x_organization_id_child in number,
1140 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1141 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1142 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1143 CURSOR c_get_all_orgs IS
1144 SELECT child_organization_id
1145 from pa_org_hierarchy_denorm
1146 where org_hierarchy_version_id = x_org_version_id
1147 and parent_organization_id = x_organization_id_child;
1148
1149 l_parent_org_id number;
1150
1151 Begin
1152 /* For all operating units where the Project Org structure
1153 specified is same as the changed Org structure */
1154 for imp_rec in ( select org_id,proj_start_org_id
1155 from pa_implementations_all imp
1156 where proj_org_structure_version_id = x_org_version_id
1157 )
1158
1159 loop
1160 update pa_all_organizations
1161 set inactive_date = trunc(sysdate)
1162 where organization_id = x_organization_id_child
1163 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1164 and pa_org_use_type = 'PROJECTS'
1165 and inactive_date is null ; -- Bug Ref # 6367868
1166 end loop;
1167
1168
1169 /* For all operating units where the Exp Org structure
1170 specified is same as the changed Org structure */
1174 )
1171 for imp_rec in ( select org_id, exp_start_org_id
1172 from pa_implementations_all imp
1173 where exp_org_structure_version_id = x_org_version_id
1175 loop
1176 update pa_all_organizations
1177 set inactive_date = trunc(sysdate)
1178 where organization_id = x_organization_id_child
1179 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1180 and pa_org_use_type = 'EXPENDITURES'
1181 and inactive_date is null ; -- Bug Ref # 6367868
1182 end loop;
1183
1184 --- Delete all rows from pa_cc_tp_schedule_line_lkp
1185 delete from pa_cc_tp_schedule_line_lkp;
1186
1187 open c_get_all_orgs;
1188 loop
1189 fetch c_get_all_orgs into l_parent_org_id;
1190 exit when c_get_all_orgs%notfound;
1191 delete from pa_org_hierarchy_denorm
1192 where org_hierarchy_version_id = x_org_version_id
1193 and (child_organization_id = l_parent_org_id
1194 or parent_organization_id = l_parent_org_id);
1195 end loop;
1196 close c_get_all_orgs;
1197
1198 /* Bug#2643047 - call to restructure levels from pa_org_hierarchy_denorm table*/
1199
1200 pa_org_utils.restructure_rpt_orgs_denorm(
1201 x_org_version_id,
1202 x_err_code ,
1203 x_err_stage,
1204 x_err_stack
1205 );
1206 Exception
1207 when others then
1208 x_err_code := SQLCODE;
1209 x_err_stage := SQLERRM;
1210 return;
1211
1212 End;
1213
1214
1215 -----maintain_org_info_hist_bri created on HR_ORGANIZATION_INFORMATION
1216 ----- For update and insert in PA_ALL_ORGANIZATIO against BUG NO 1357522
1217 procedure maintain_org_info_hist_bri
1218 (x_organization_id in number ,
1219 x_org_information1 in varchar2,
1220 x_org_information_context in varchar2,
1221 x_org_information2 in varchar2,
1222 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1223 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1224 x_err_stack in out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
1225
1226
1227 v_org_structure_version_id number;
1228 v_org_child_id number;
1229 v_org_chk varchar2(60);
1230 v_start_org_id number;
1231
1232 /*
1233 Bug#2620151-Commented the old cursor and added new logic for the cursor check_org_exists
1234
1235 New Logic: Traverse from x_organization_id and get all the parents for this.
1236 In this result, check for v_start_org_id, if present then it means that the org
1237 x_organization_id is under v_start_org_id.
1238
1239 Earlier Logic: Traverse from v_start_org_id and get all the childs for this.
1240 In this result, check for x_organization_id, if present then it means that the
1241 org x_organization_id is under v_start_org_id.
1242 */
1243
1244 cursor check_org_exists is
1245 (
1246 select se.organization_id_parent
1247 from per_org_structure_elements se
1248 where se.org_structure_version_id = v_org_structure_version_id
1249 and se.organization_id_parent =
1250 v_start_org_id
1251 connect by prior se.organization_id_parent =
1252 se.organization_id_child
1253 and org_structure_version_id = v_org_structure_version_id
1254 start with se.organization_id_child =
1255 x_organization_id
1256 and org_structure_version_id = v_org_structure_version_id
1257 );
1258
1259 /* Commeneted the cursor below for bug#2620151, Added new cursor above
1260 cursor check_org_exists is
1261 (select se.organization_id_child
1262 from per_org_structure_elements se
1263 where se.org_structure_version_id = v_org_structure_version_id
1264 and se.organization_id_child = x_organization_id ---made changes as Suggested
1265 connect by prior se.organization_id_child =
1266 se.organization_id_parent
1267 and org_structure_version_id = v_org_structure_version_id
1268 start with se.organization_id_parent =
1269 v_start_org_id
1270 and org_structure_version_id = v_org_structure_version_id );
1271 */
1272
1273 --- To populate pa_org_hierarchy_denorm
1274 cursor get_all_orgs is
1275 (SELECT x_organization_id
1276 FROM dual
1277 UNION ALL
1278 SELECT organization_id_parent
1279 FROM per_org_structure_elements
1280 WHERE org_structure_version_id = v_org_structure_version_id
1281 CONNECT BY PRIOR organization_id_parent= organization_id_child
1282 AND org_structure_version_id = v_org_structure_version_id
1283 START WITH organization_id_child = x_organization_id);
1284
1285 l_parent_org_id number;
1286
1287 Begin
1288 --- Delete all rows from pa_cc_tp_schedule_line_lkp
1289 delete from pa_cc_tp_schedule_line_lkp;
1290
1291 if x_org_information1 = 'PA_PROJECT_ORG' AND
1295 ----- Means This Organization is being classified as
1292 x_org_information_context = 'CLASS' then
1293 if x_org_information2 = 'Y' then
1294
1296 ---- Projects Task / owning Organization and classification
1297 --- is enabled. Now Loop theough all the records from
1298 --- pa_implementations_all. And check that CUrrent Organization
1299 ---- Being changed is attached to an Organization structure which
1300 ---- is defined in Implementation as Project Structure.
1301 for imp_rec in (select proj_start_org_id, proj_org_structure_version_id,
1302 org_id
1303 from pa_implementations_all )
1304 loop
1305 v_start_org_id := imp_rec.proj_start_org_id;
1306 v_org_structure_version_id := imp_rec.proj_org_structure_version_id;
1307 ---- Check that Current Organization which is getting changed is below
1308 ---- The start organization specified for a Project Organization
1309 ---- Structure.
1310 IF(v_start_org_id <> x_organization_id) THEN --made changes as Suggested
1311 open check_org_exists;
1312 fetch check_org_exists into v_org_child_id;
1313 if check_org_exists%found then /* Added org is below the start project org */
1314 insert into pa_all_organizations
1315 (organization_id,
1316 org_id,
1317 pa_org_use_type)
1318 (select x_organization_id,
1319 imp_rec.org_id, 'PROJECTS'
1320 from sys.dual
1321 where not exists ( select 'X'
1322 from pa_all_organizations
1323 where organization_id = x_organization_id
1324 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1325 and pa_org_use_type = 'PROJECTS' ));
1326
1327 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1328 ---- Earlier, IN This case set inactive_date to
1329 --- NULL.
1330 update pa_all_organizations
1331 set inactive_date = null
1332 where organization_id = x_organization_id
1333 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1334 and pa_org_use_type = 'PROJECTS' ;
1335 end if;
1336
1337 open get_all_orgs;
1338 loop
1339 fetch get_all_orgs into l_parent_org_id;
1340 exit when get_all_orgs%notfound;
1341 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1342 , p_child_organization_id => x_organization_id
1343 , p_org_hierarchy_version_id => v_org_structure_version_id
1344 , p_pa_org_use_type => 'PROJECTS'
1345 , x_err_code => x_err_code
1346 , x_err_stage => x_err_stage
1347 , x_err_stack => x_err_stack);
1348 end loop;
1349 close get_all_orgs;
1350
1351 end if;
1352 close check_org_exists;
1353 ELSE
1354 ---- If Current Organization is Start Organizations then Record has to be
1355 ---- Created in PA_ALL_ORGANIZATIONS.
1356 insert into pa_all_organizations
1357 (organization_id,
1358 org_id,
1359 pa_org_use_type)
1360 (select x_organization_id,
1361 imp_rec.org_id, 'PROJECTS'
1362 from sys.dual
1363 where not exists ( select 'X'
1364 from pa_all_organizations
1365 where organization_id = x_organization_id
1366 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1367 and pa_org_use_type = 'PROJECTS' ));
1368 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1369 ---- Earlier, IN This case set inactive_date to
1370 --- NULL.
1371 update pa_all_organizations
1372 set inactive_date = null
1373 where organization_id = x_organization_id
1374 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1375 and pa_org_use_type = 'PROJECTS' ;
1376 end if;
1377
1378 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM ( p_parent_organization_id => v_start_org_id
1379 , p_child_organization_id => x_organization_id
1383 , x_err_stage => x_err_stage
1380 , p_org_hierarchy_version_id => v_org_structure_version_id
1381 , p_pa_org_use_type => 'PROJECTS'
1382 , x_err_code => x_err_code
1384 , x_err_stack => x_err_stack);
1385
1386 END IF;
1387 end loop;
1388 else
1389 for imp_rec in (select proj_start_org_id, proj_org_structure_version_id,
1390 org_id
1391 from pa_implementations_all )
1392 loop
1393
1394 update pa_all_organizations
1395 set inactive_date = trunc(sysdate)
1396 where organization_id = x_organization_id
1397 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1398 and pa_org_use_type = 'PROJECTS'
1399 and inactive_date is null; -- Bug Ref # 6367868
1400
1401 delete from pa_org_hierarchy_denorm
1402 where child_organization_id = x_organization_id
1403 and org_hierarchy_version_id = imp_rec.proj_org_structure_version_id
1404 and pa_org_use_type = 'PROJECTS'; /* Bug#2643047 - Only PROJECTS records need to be deleted as we are checking
1405 for Project task owning classification here */
1406
1407 end loop;
1408
1409 end if;
1410 end if;
1411 ----- Check for Expenditure Organization Type starts here.
1412 if x_org_information1 = 'PA_EXPENDITURE_ORG' and
1413 x_org_information_context = 'CLASS' then
1414
1415 if x_org_information2 = 'Y' then
1416 ----- Means This Organization is being classified as
1417 ---- Projects Expenditure owing Organization and classification
1418 --- is enabled. Now Loop theough all the records from
1419 --- pa_implementations_all. And check that Current Organization
1420 ---- Being changed is attached to an Organization structure which
1421 ---- is defined in Implementation as Project Structure.
1422 for imp_rec in (select exp_start_org_id, exp_org_structure_version_id,
1423 org_id
1424 from pa_implementations_all )
1425 loop
1426 v_start_org_id := imp_rec.exp_start_org_id;
1427 v_org_structure_version_id := imp_rec.exp_org_structure_version_id;
1428 ---- Check that Current Organization which is getting changed is below
1429 ---- The start organization specified for a Expenditure Organization
1430 ---- Structure.
1431 IF(v_start_org_id<>x_organization_id) THEN
1432 open check_org_exists;
1433 fetch check_org_exists into v_org_child_id;
1434 if check_org_exists%found then /* Added org is below the start project org */
1435 insert into pa_all_organizations
1436 (organization_id,
1437 org_id,
1438 pa_org_use_type)
1439 (select x_organization_id,
1440 imp_rec.org_id, 'EXPENDITURES'
1441 from sys.dual
1442 where not exists ( select 'X'
1443 from pa_all_organizations
1444 where organization_id = x_organization_id
1445 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1446 and pa_org_use_type = 'EXPENDITURES' ));
1447
1448 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1449 ---- Earlier, IN This case set inactive_date to
1450 --- NULL.
1451 update pa_all_organizations
1452 set inactive_date = null
1453 where organization_id = x_organization_id
1454 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1455 and pa_org_use_type = 'EXPENDITURES' ;
1456 end if;
1457 open get_all_orgs;
1458 loop
1459 fetch get_all_orgs into l_parent_org_id;
1460 exit when get_all_orgs%notfound;
1461 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id => l_parent_org_id
1462 , p_child_organization_id => x_organization_id
1463 , p_org_hierarchy_version_id => v_org_structure_version_id
1464 , p_pa_org_use_type => 'EXPENDITURES'
1465 , x_err_code => x_err_code
1466 , x_err_stage => x_err_stage
1467 , x_err_stack => x_err_stack);
1468 end loop;
1472 close check_org_exists;
1469 close get_all_orgs;
1470
1471 end if;
1473 ELSE
1474 ---- If Current Organization is Start Organizations then Record has to be
1475 ---- Created in PA_ALL_ORGANIZATIONS.
1476 insert into pa_all_organizations
1477 (organization_id,
1478 org_id,
1479 pa_org_use_type)
1480 (select x_organization_id,
1481 imp_rec.org_id, 'EXPENDITURES'
1482 from sys.dual
1483 where not exists ( select 'X'
1484 from pa_all_organizations
1485 where organization_id = x_organization_id
1486 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1487 and pa_org_use_type = 'EXPENDITURES' ));
1488 if sql%rowcount = 0 then --- Means Row was not inserted as it was there
1489 ---- Earlier, IN This case set inactive_date to
1490 --- NULL.
1491 update pa_all_organizations
1492 set inactive_date = null
1493 where organization_id = x_organization_id
1494 and org_id = imp_rec.org_id--MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1495 and pa_org_use_type = 'EXPENDITURES' ;
1496 end if;
1497
1498 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM ( p_parent_organization_id => v_start_org_id
1499 , p_child_organization_id => x_organization_id
1500 , p_org_hierarchy_version_id => v_org_structure_version_id
1501 , p_pa_org_use_type => 'EXPENDITURES'
1502 , x_err_code => x_err_code
1503 , x_err_stage => x_err_stage
1504 , x_err_stack => x_err_stack);
1505
1506 END IF;
1507 end loop;
1508 else
1509 for imp_rec in (select exp_start_org_id, exp_org_structure_version_id,
1510 org_id
1511 from pa_implementations_all )
1512 loop
1513
1514 update pa_all_organizations
1515 set inactive_date = trunc(sysdate)
1516 where organization_id = x_organization_id
1517 and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1518 and pa_org_use_type = 'EXPENDITURES'
1519 and inactive_date is null ; -- Bug Ref # 6367868
1520
1521 delete from pa_org_hierarchy_denorm
1522 where child_organization_id = x_organization_id
1523 and org_hierarchy_version_id = imp_rec.exp_org_structure_version_id
1524 and pa_org_use_type = 'EXPENDITURES'; /* Bug#2643047 - Only EXPENDITURES records need to be deleted as we
1525 are checking for Project/expenditure owning classification here */
1526
1527 end loop;
1528
1529 end if;
1530 end if;
1531 EXCEPTION
1532 when others then
1533 x_err_code := SQLCODE;
1534 x_err_stage := SQLERRM;
1535 return;
1536 end;
1537 -------maintain_org_info_hist_bri ends
1538
1539 Procedure Start_Org_Changed (x_old_org_version_id in number,
1540 x_new_org_version_id in number,
1541 x_old_start_org_id in number,
1542 x_new_start_org_id in number,
1543 x_org_use_type in varchar2,
1544 x_org_id in number,
1545 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1546 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1547 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
1548 IS
1549
1550 CURSOR cur_all_old_org IS
1551 SELECT se.organization_id_child organization_id
1552 FROM per_org_structure_elements se
1553 WHERE org_structure_version_id = x_old_org_version_id
1554 CONNECT BY PRIOR se.organization_id_child
1555 = se.organization_id_parent
1556 AND org_structure_version_id = x_old_org_version_id
1557 START WITH se.organization_id_parent = x_old_start_org_id
1558 AND org_structure_version_id = x_old_org_version_id
1559 UNION
1560 SELECT x_old_start_org_id FROM Sys.dual ;
1561
1562 CURSOR cur_proj_new_org IS
1563 SELECT se.organization_id_child organization_id
1564 FROM per_org_structure_elements se
1565 WHERE org_structure_version_id = x_new_org_version_id
1566 AND EXISTS (select 'X'
1567 from hr_organization_information info
1571 and info.org_information2 = 'Y')
1568 where info.organization_id = se.organization_id_child
1569 and info.org_information1 = 'PA_PROJECT_ORG'
1570 and info.org_information_context||'' = 'CLASS'
1572 CONNECT BY PRIOR se.organization_id_child
1573 = se.organization_id_parent
1574 AND org_structure_version_id = x_new_org_version_id
1575 START WITH se.organization_id_parent = x_new_start_org_id
1576 AND org_structure_version_id = x_new_org_version_id
1577 UNION
1578 SELECT x_new_start_org_id FROM Sys.dual -- Added exists clause for Bug# 1650520
1579 WHERE EXISTS (select 'X'
1580 from hr_organization_information info
1581 where info.org_information1 = 'PA_PROJECT_ORG'
1582 and info.org_information_context||'' = 'CLASS'
1583 and info.org_information2 = 'Y'
1584 and info.organization_id = x_new_start_org_id);
1585
1586
1587 CURSOR cur_exp_new_org IS
1588 SELECT se.organization_id_child organization_id
1589 FROM per_org_structure_elements se
1590 WHERE org_structure_version_id = x_new_org_version_id
1591 AND EXISTS (select 'X'
1592 from hr_organization_information info
1593 where info.organization_id = se.organization_id_child
1594 and info.org_information1 = 'PA_EXPENDITURE_ORG'
1595 and info.org_information_context||'' = 'CLASS'
1596 and info.org_information2 = 'Y')
1597 CONNECT BY PRIOR se.organization_id_child
1598 = se.organization_id_parent
1599 AND org_structure_version_id = x_new_org_version_id
1600 START WITH se.organization_id_parent = x_new_start_org_id
1601 AND org_structure_version_id = x_new_org_version_id
1602 UNION
1603 SELECT x_new_start_org_id FROM Sys.dual
1604 where EXISTS (select 'X' /* Made changes for BUG 1180635*/
1605 from hr_organization_information info
1606 where info.org_information1 = 'PA_EXPENDITURE_ORG'
1607 and info.org_information_context||'' = 'CLASS'
1608 and info.org_information2 = 'Y'
1609 and info.organization_id = x_new_start_org_id);
1610
1611
1612 rec_all_old_org cur_all_old_org%ROWTYPE;
1613 rec_exp_new_org cur_exp_new_org%ROWTYPE;
1614 rec_proj_new_org cur_proj_new_org%ROWTYPE;
1615
1616 BEGIN
1617 x_err_code := 0;
1618 x_err_stack := x_err_stack || 'Start_Org_Changed';
1619
1620 /* When either structure version id OR start organization id changes */
1621
1622 IF ( (NVL(x_old_org_version_id,-99) <> x_new_org_version_id )
1623 OR
1624 (NVL(x_old_start_org_id,-99) <> x_new_start_org_id ) ) THEN
1625
1626 FOR rec_all_old_org in cur_all_old_org LOOP
1627
1628 /* Update all Organizations (Projects or Expenditures)
1629 in the old hierarchy with inactive date as Sysdate.
1630 Union is to include the start organization id in the update */
1631
1632 UPDATE pa_all_organizations
1633 SET Inactive_Date = TRUNC(SYSDATE)
1634 WHERE Pa_Org_Use_Type = x_org_use_type
1635 AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1636 AND Organization_id = rec_all_old_org.organization_id
1637 and inactive_date is null ; -- Bug Ref # 6367868
1638
1639 /* In case there are no corresponding records for the new
1640 Organization hierarchy in the Pa_all_Organizations, then,
1641 create these records in the Pa_All_Organizations
1642 with Inactive_Date as SYSDATE */
1643
1644 IF SQL%NOTFOUND THEN
1645 INSERT INTO Pa_All_Organizations
1646 (organization_id,
1647 org_id,
1648 pa_org_use_type,
1649 inactive_date)
1650 VALUES
1651 (rec_all_old_org.organization_id,
1652 x_org_id,
1653 x_org_use_type,
1654 TRUNC(SYSDATE));
1655 END IF;
1656
1657 END LOOP;
1658
1659 IF x_org_use_type = 'EXPENDITURES' THEN
1660
1661 FOR rec_exp_new_org IN cur_exp_new_org LOOP
1662
1663 /* Update all Organizations (Expenditures)
1664 in the new hierarchy with inactive date as NULL.
1665 Union is to include the start organization id in the update */
1666
1667 UPDATE pa_all_organizations
1668 SET Inactive_Date = NULL
1669 WHERE Pa_Org_Use_Type = x_org_use_type
1670 AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1671 AND Organization_id = rec_exp_new_org.organization_id;
1672
1673 /* In case there are no corresponding records for the new
1674 Organization hierarchy in the Pa_all_Organizations, then,
1675 create these records in the Pa_All_Organizations
1676 with Inactive_Date as NULL */
1677
1678 IF SQL%NOTFOUND THEN
1682 pa_org_use_type,
1679 INSERT INTO Pa_All_Organizations
1680 (organization_id,
1681 org_id,
1683 inactive_date)
1684 VALUES
1685 (rec_exp_new_org.organization_id,
1686 x_org_id,
1687 x_org_use_type,
1688 NULL);
1689 END IF;
1690
1691 END LOOP;
1692
1693 END IF; /* End of IF for org_use_type = 'EXPENDITURES' */
1694
1695
1696 IF x_org_use_type = 'PROJECTS' THEN
1697
1698 FOR rec_proj_new_org IN cur_proj_new_org LOOP
1699
1700 /* Update all Organizations (Projects)
1701 in the new hierarchy with inactive date as NULL.
1702 Union is to include the start organization id in the update */
1703
1704 UPDATE pa_all_organizations
1705 SET Inactive_Date = NULL
1706 WHERE Pa_Org_Use_Type = x_org_use_type
1707 AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1708 AND Organization_id = rec_proj_new_org.organization_id;
1709
1710 /* In case there are no corresponding records for the new
1711 Organization hierarchy in the Pa_all_Organizations, then,
1712 create these records in the Pa_All_Organizations
1713 with Inactive_Date as NULL */
1714
1715 IF SQL%NOTFOUND THEN
1716 INSERT INTO Pa_All_Organizations
1717 (organization_id,
1718 org_id,
1719 pa_org_use_type,
1720 inactive_date)
1721 VALUES
1722 (rec_proj_new_org.organization_id,
1723 x_org_id,
1724 x_org_use_type,
1725 NULL);
1726 END IF;
1727
1728 END LOOP;
1729
1730 END IF; /* End of IF for org_use_type = 'PROJECTS' */
1731
1732 END IF;
1733
1734 Exception
1735 when others then
1736 x_err_code := SQLCODE;
1737 x_err_stage := SQLERRM;
1738 return;
1739
1740 END Start_Org_Changed;
1741
1742 PROCEDURE Create_org_hierarchy_denorm(p_parent_organization_id in number,
1743 p_child_organization_id in number,
1744 p_org_hierarchy_version_id in number,
1745 p_pa_org_use_type in varchar2,
1746 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1747 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1748 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1749 old_stack varchar2(4000);
1750 Begin
1751
1752 x_err_code := 0;
1753 old_stack := x_err_stack;
1754 x_err_stack := x_err_stack || 'Create_org_hierarchy_denorm';
1755 /*
1756 dbms_output.put_line('in create');
1757 dbms_output.put_line('p_pa_org_use_type ='||p_pa_org_use_type);
1758 dbms_output.put_line('p_parent_organization_id ='||p_parent_organization_id);
1759 dbms_output.put_line('p_child_organization_id ='||p_child_organization_id);
1760 dbms_output.put_line('p_org_hierarchy_version_id ='||p_org_hierarchy_version_id);
1761 */
1762
1763
1764
1765 INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
1766 , child_organization_id
1767 , org_hierarchy_version_id
1768 , pa_org_use_type
1769 , creation_date
1770 , created_by
1771 , last_update_date
1772 , last_updated_by
1773 , last_update_login
1774 )
1775 SELECT p_parent_organization_id
1776 , p_child_organization_id
1777 , p_org_hierarchy_version_id
1778 , p_pa_org_use_type
1779 , sysdate
1780 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1781 , sysdate
1782 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1783 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1784 from dual
1785 where not exists
1786 (select 'Y'
1787 from pa_org_hierarchy_denorm
1788 where pa_org_use_type = p_pa_org_use_type
1789 and parent_organization_id = p_parent_organization_id
1790 and child_organization_id = p_child_organization_id
1791 and org_hierarchy_version_id = p_org_hierarchy_version_id);
1792
1793 x_err_stack := old_stack;
1794 Exception
1795 when others then
1796 x_err_code := SQLCODE;
1797 x_err_stage := SQLERRM;
1798 return;
1799
1800 End;
1801
1802 -- Start CC Change
1803
1807 Assume that following is the hierarchy:
1804 /* Take following heirarchy for understanding this piece of logic
1805 for the use_type = TP_SCHEDULE
1806
1808 org1
1809 / \
1810 org2 org3 -> start org in pa_implementations
1811 / \
1812 org5 org6
1813 |
1814 org7
1815
1816
1817
1818 In this example the start org defined in the pa_implementations is org3.
1819
1820 For the TP_SCHEDULE purpose the hierarchy is always populated from the start
1821 of the hierarcy rather than the start org mentioned in the pa_implementations.
1822
1823 The aim of this procedure is to populate pa_org_denorm table with following data.
1824
1825 parent child p_level c_level
1826
1827 org1 org1 1 1
1828 org1 org2 1 2
1829 org1 org3 1 2
1830 org2 org2 2 2
1831 org3 org3 2 2
1832 org3 org5 2 3
1833 org3 org6 2 3
1834 org5 org5 3 3
1835 org5 org7 3 4
1836
1837 */
1838
1839 -- CC Change Added the below procedure.
1840 PROCEDURE Create_org_hier_denorm_levels(p_parent_organization_id in number,
1841 p_child_organization_id in number,
1842 p_org_hierarchy_version_id in number,
1843 p_pa_org_use_type in varchar2,
1844 p_parent_level in number,
1845 p_child_level in number,
1846 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1847 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1848 x_err_stack in out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1849 old_stack varchar2(4000);
1850 Begin
1851
1852 x_err_code := 0;
1853 old_stack := x_err_stack;
1854 x_err_stack := x_err_stack || 'Create_org_hierarchy_denorm';
1855 /*
1856 dbms_output.put_line('in create');
1857 dbms_output.put_line('p_pa_org_use_type ='||p_pa_org_use_type);
1858 dbms_output.put_line('p_parent_organization_id ='||p_parent_organization_id);
1859 dbms_output.put_line('p_child_organization_id ='||p_child_organization_id);
1860 dbms_output.put_line('p_org_hierarchy_version_id ='||p_org_hierarchy_version_id);
1861 */
1862
1863
1864
1865 INSERT INTO pa_org_hierarchy_denorm ( parent_organization_id
1866 , child_organization_id
1867 , org_hierarchy_version_id
1868 , pa_org_use_type
1869 , creation_date
1870 , created_by
1871 , last_update_date
1872 , last_updated_by
1873 , last_update_login
1874 , parent_level
1875 , child_level
1876 )
1877 SELECT p_parent_organization_id
1878 , p_child_organization_id
1879 , p_org_hierarchy_version_id
1880 , p_pa_org_use_type
1881 , sysdate
1882 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1883 , sysdate
1884 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
1885 ,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
1886 , p_parent_level
1887 , p_child_level
1888 from dual
1889 where not exists
1890 (select 'Y'
1891 from pa_org_hierarchy_denorm
1892 where pa_org_use_type = p_pa_org_use_type
1893 and parent_organization_id = p_parent_organization_id
1894 and child_organization_id = p_child_organization_id
1895 and org_hierarchy_version_id = p_org_hierarchy_version_id);
1896
1897 x_err_stack := old_stack;
1898 Exception
1899 when others then
1900 x_err_code := SQLCODE;
1901 x_err_stage := SQLERRM;
1902 return;
1903
1904 End;
1905
1906 -- End CC Change
1907 --
1908 -- Created by Ranga Iyengar
1909 -- Dated : 02-NOV-00
1910 -- PROCEDURE
1911 --
1912 -- populates org hierarchy denorm for reporting type of organizations
1913 --
1914 -- This Procedure inserts records into pa_org_hierarchy_denorm
1915 -- with parent level and child levels for reporting type of
1916 -- organizations
1917 PROCEDURE create_reporting_org
1918 ( p_parent_organization_id IN NUMBER
1919 ,p_child_organization_id IN NUMBER
1920 ,p_org_hierarchy_version_id IN NUMBER
1921 ,p_pa_org_use_type IN VARCHAR2
1922 ,p_parent_org_level IN NUMBER
1926 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1923 ,p_child_org_level IN NUMBER
1924 ,p_org_id IN NUMBER
1925 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1927 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1928 ) IS
1929 old_stack VARCHAR2(2000);
1930 BEGIN
1931
1932 x_err_code := 0;
1933 old_stack := x_err_stack;
1934 x_err_stack := x_err_stack || 'create_reporting_org';
1935 --r_msg('inserting into pa_org_hierarchy_denorm');
1936 INSERT INTO pa_org_hierarchy_denorm
1937 ( parent_organization_id
1938 , child_organization_id
1939 , org_hierarchy_version_id
1940 , pa_org_use_type
1941 , creation_date
1942 , created_by
1943 , last_update_date
1944 , last_updated_by
1945 , last_update_login
1946 , parent_level
1947 , child_level
1948 , org_id
1949 )
1950 SELECT p_parent_organization_id
1951 , p_child_organization_id
1952 , p_org_hierarchy_version_id
1953 , p_pa_org_use_type
1954 , sysdate
1955 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
1956 , sysdate
1957 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
1958 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
1959 , p_parent_org_level
1960 , p_child_org_level
1961 , p_org_id
1962 from dual
1963 where not exists
1964 (select 'Y'
1965 from pa_org_hierarchy_denorm
1966 where pa_org_use_type = p_pa_org_use_type
1967 and parent_organization_id = p_parent_organization_id
1968 and child_organization_id = p_child_organization_id
1969 and org_hierarchy_version_id = p_org_hierarchy_version_id
1970 and nvl(org_id, -99) = nvl(p_org_id, -99)); /* 2976953 Added nvl condition for org_id */
1971 IF SQL%FOUND THEN
1972 --r_msg('row inserted');
1973 null;
1974 else
1975 --r_msg('not inserted');
1976 null;
1977 end if;
1978 x_err_stack := old_stack;
1979
1980 EXCEPTION
1981 when others then
1982 x_err_code := SQLCODE;
1983 x_err_stage := SQLERRM;
1984 --r_msg('could not insert'||x_err_code||x_err_stage);
1985 return;
1986 END create_reporting_org;
1987
1988
1989 -- This procedure populates parent level and child level for the
1990 -- reporting organizations in pa org hierarchy denorm entity
1991 PROCEDURE populate_org_levels( P_org_version_id IN NUMBER
1992 , P_organization_id_parent IN NUMBER
1993 , P_organization_id_child IN NUMBER
1994 , P_org_level IN NUMBER
1995 , P_org_max_level IN NUMBER
1996 , P_org_id IN NUMBER
1997 , P_start_org_id IN NUMBER -- added this to fix bug : 1619922
1998 , x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1999 , x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2000 , x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2001 ) IS
2002
2003
2004 v_start_org_id number;
2005 v_org_child_id number;
2006 v_parent_org_id number;
2007 v_plevel number;
2008 v_clevel number;
2009 old_stack varchar2(2000);
2010 v_parent_org_level number;
2011
2012 --- logic to populate hierarchy denorm
2013 -- get all the parent organization for the child
2014 -- example 200p - 205c,201p-205c,202p-205c,203p-205c,204p-205c,205p-205c
2015 -- where p is parent org and c is the child orgs
2016 cursor get_all_orgs is
2017 SELECT P_organization_id_parent
2018 FROM dual
2019 UNION
2020 (SELECT distinct organization_id_parent
2021 FROM per_org_structure_elements
2022 WHERE org_structure_version_id = P_org_version_id
2023 CONNECT BY PRIOR organization_id_parent= organization_id_child
2024 AND org_structure_version_id = P_org_version_id
2025 START WITH organization_id_child = P_organization_id_parent
2026 AND org_structure_version_id = P_org_version_id
2027 /* ---- The following query is added to fix the bug : 1654453 ---
2028 ---- since the start organization id defined in per_org_structure_elements
2029 ---- may be different from the start_organization_id set up in pa_implementations
2033 SELECT distinct organization_id_parent
2030 ---- so always the reporting hierarchy is formed based on pa_implementations
2031 ---- start_organization_id */
2032 MINUS
2034 FROM per_org_structure_elements
2035 WHERE org_structure_version_id = P_org_version_id
2036 CONNECT BY PRIOR organization_id_parent= organization_id_child
2037 AND org_structure_version_id = P_org_version_id
2038 START WITH organization_id_child = P_start_org_id
2039 AND org_structure_version_id = P_org_version_id)
2040 UNION
2041 (SELECT P_organization_id_child from dual);
2042
2043 BEGIN
2044
2045 x_err_code := 0;
2046 old_stack := x_err_stack;
2047 x_err_stack := x_err_stack || 'populate_org_levels';
2048
2049 /* For all operating units where the Reporting Org structure
2050 specified is same as the changed Org structure */
2051
2052
2053 -------- for pa_org_hierarchy_denorm
2054 open get_all_orgs;
2055 loop
2056 fetch get_all_orgs into v_parent_org_id;
2057 exit when get_all_orgs%notfound;
2058 /*
2059 -- get the maximum level of the parent while child level is
2060 -- is calculated based on the formula maxlevel - level + 1
2061 SELECT max(parent_level) into v_plevel
2062 FROM pa_org_hierarchy_denorm
2063 WHERE org_hierarchy_version_id = P_org_version_id
2064 and pa_org_use_type = 'REPORTING'
2065 and parent_organization_id = v_parent_org_id
2066 and org_id = p_org_id;
2067
2068 if v_plevel is null then
2069 SELECT max(child_level) into v_plevel
2070 FROM pa_org_hierarchy_denorm
2071 WHERE org_hierarchy_version_id = P_org_version_id
2072 and pa_org_use_type = 'REPORTING'
2073 and child_organization_id = v_parent_org_id
2074 and org_id = p_org_id;
2075 end if;
2076
2077 if nvl(v_plevel,0) = 0 then
2078 v_plevel := P_org_max_level - P_org_level + 2;
2079 end if;
2080
2081 -- if v_clevel is null then
2082 v_clevel := P_org_max_level - P_org_level + 1;
2083 -- end if;
2084
2085
2086 -- where both parent and child orgs are same then level of
2087 -- parent org and level of child orgs are same
2088
2089 if P_organization_id_child = v_parent_org_id then
2090 v_plevel := v_clevel;
2091 end if;
2092
2093
2094 -- the above condition is commented out and added new condition to fix the bug : 1619922
2095 if (P_organization_id_child = v_parent_org_id) and (P_organization_id_child <> P_start_org_id) then
2096 v_plevel := v_clevel;
2097 elsif (P_organization_id_child = v_parent_org_id) and (P_organization_id_child = P_start_org_id) then
2098 v_clevel := v_plevel;
2099 end if;
2100 */
2101
2102 ----------- Added the Following lines to fix the bug : 1654453 --------------
2103 -- set the child level based on the formula
2104 v_clevel := P_org_max_level - P_org_level + 1;
2105
2106
2107 -- set the parent level based on the following condition
2108 -- get the parent organization level from the per_org_structure_elements
2109 -- then apply the formula
2110 SELECT max(level)
2111 INTO v_parent_org_level
2112 FROM per_org_structure_elements
2113 WHERE org_structure_version_id = P_org_version_id
2114 AND organization_id_parent = v_parent_org_id
2115 CONNECT BY PRIOR organization_id_child = organization_id_parent
2116 AND org_structure_version_id = P_org_version_id
2117 START WITH organization_id_parent = P_start_org_id
2118 AND org_structure_version_id = P_org_version_id;
2119 If v_parent_org_level is NULL then
2120
2121 SELECT max(level)
2122 INTO v_parent_org_level
2123 FROM per_org_structure_elements
2124 WHERE org_structure_version_id = P_org_version_id
2125 AND organization_id_child = v_parent_org_id
2126 CONNECT BY PRIOR organization_id_child = organization_id_parent
2127 AND org_structure_version_id = P_org_version_id
2128 START WITH organization_id_parent = P_start_org_id
2129 AND org_structure_version_id = P_org_version_id;
2130
2131
2132
2133 --- set the org level based on the formula
2134 v_plevel := P_org_max_level - v_parent_org_level + 1;
2135
2136 Else
2137
2138 v_plevel := P_org_max_level - v_parent_org_level + 2;
2139
2140 End if;
2141
2142 If P_organization_id_child = v_parent_org_id then
2143 v_clevel := v_plevel;
2144 End if;
2145 --r_msg('parent'||v_parent_org_id||'child'||P_organization_id_child||'plevel'||v_plevel||'clevel '||v_clevel);
2146
2147 ---------------------------------end of bug fix---------------------------
2148
2149
2150 create_reporting_org(
2151 p_parent_organization_id => v_parent_org_id
2152 ,p_child_organization_id =>P_organization_id_child
2153 ,p_org_hierarchy_version_id => P_org_version_id
2154 ,p_org_id => p_org_id
2155 ,p_pa_org_use_type => 'REPORTING'
2156 ,P_parent_org_level => v_plevel
2157 ,p_child_org_level => v_clevel
2158 ,x_err_code => x_err_code
2162
2159 ,x_err_stage => x_err_stage
2160 ,x_err_stack => x_err_stack
2161 );
2163 v_plevel := null;
2164 v_clevel := null;
2165 end loop;
2166 close get_all_orgs;
2167
2168 x_err_stack := old_stack;
2169 EXCEPTION
2170 when others then
2171 x_err_code := SQLCODE;
2172 x_err_stage := SQLERRM;
2173 --r_msg('error in populate org level'||x_err_code||x_err_stage);
2174 return;
2175
2176 END populate_org_levels;
2177
2178
2179 -- This Procedure is called from trigger org_maintn_hist_bri
2180 PROCEDURE populate_hierarchy_denorm
2181 ( p_org_version_id IN NUMBER
2182 ,p_organization_id_parent IN NUMBER
2183 ,p_organization_id_child IN NUMBER
2184 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2185 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2186 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2187 ) IS
2188 v_org_version_id number;
2189 v_child_org_id number;
2190 v_parent_org_id number;
2191 v_start_org_id number;
2192 v_org_level number;
2193 v_org_id number;
2194 v_maximumlevel number;
2195 v_dummy_child_id number;
2196 old_stack varchar2(2000);
2197 v_success_flag Number;
2198 dummy_level number := 1;
2199 -- get all orgs structure from per_org-structure_elemetns for
2200 -- given version- parameter and start org (from the pa implemetnaiton)
2201 cursor get_all_orgs(version_id NUMBER,start_org_id NUMBER) is
2202 SELECT dummy_level plevel --- This query is added to fix bug : 1619922
2203 ,start_org_id organization_id_parent
2204 ,start_org_id organization_id_child
2205 FROM dual
2206 UNION
2207 SELECT distinct
2208 level plevel
2209 ,organization_id_parent
2210 ,organization_id_child
2211 FROM per_org_structure_elements
2212 WHERE org_structure_version_id = version_id
2213 CONNECT BY PRIOR organization_id_child = organization_id_parent
2214 and org_structure_version_id = version_id
2215 START WITH organization_id_parent = start_org_id
2216 and org_structure_version_id = version_id
2217 ORDER by 1;
2218
2219 -- check whether the org exist in the given pa implementations ou
2220 -- and falls within the start org
2221 cursor check_org_exists(version_id NUMBER,start_org_id NUMBER) is
2222 (SELECT organization_id_child
2223 FROM per_org_structure_elements
2224 WHERE org_structure_version_id = version_id
2225 CONNECT BY PRIOR organization_id_child = organization_id_parent
2226 and org_structure_version_id = version_id
2227 START WITH organization_id_parent = start_org_id
2228 and org_structure_version_id = version_id
2229 UNION
2230 SELECT v_start_org_id
2231 FROM sys.dual)
2232 INTERSECT
2233 (SELECT p_organization_id_parent
2234 FROM sys.dual );
2235
2236
2237 BEGIN
2238
2239
2240 x_err_code := 0;
2241 old_stack := x_err_stack;
2242 x_err_stack := x_err_stack || 'populate_hierarchy_denorm';
2243
2244 --- Delete all rows from pa_cc_tp_schedule_line_lkp
2245 delete from pa_cc_tp_schedule_line_lkp;
2246
2247 ------- Acquire a lock before processing ----------------
2248
2249 if (pa_debug.acquire_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0)
2250 and (pa_debug.acquire_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) then
2251
2252 --dbms_output.put_line('acquired fclock and aclock');
2253
2254 for imp_rec in ( select org_id, start_organization_id
2255 from pa_implementations_all imp
2256 where org_structure_version_id = p_org_version_id
2257 )
2258 LOOP
2259 v_start_org_id := imp_rec.start_organization_id;
2260 v_org_id := imp_rec.org_id;
2261 ------ blow off the records and recreate with parent and child level-----
2262 --r_msg('startorg'||v_start_org_id||'OU'||v_org_id);
2263 open check_org_exists(p_org_version_id,v_start_org_id);
2264 fetch check_org_exists into v_dummy_child_id;
2265 if check_org_exists%found then
2266 --r_msg('exists');
2267 Begin
2268 DELETE from pa_org_hierarchy_denorm
2269 WHERE pa_org_use_type = 'REPORTING'
2270 and org_hierarchy_version_id = p_org_version_id
2271 and nvl(org_id, -99) = nvl(v_org_id, -99); /* 2976953-Added the nvl condition for org_id */
2272 if sql%rowcount > 0 then
2273 NULL;
2274 --r_msg('deleted');
2275 end if;
2276 Exception
2277 when others then
2278 null;
2279 End;
2280 end if;
2281 close check_org_exists;
2282
2283 ---------------------------------------------------------------------------
2284 /* For all operating units where the Reporting Org structure
2285 specified is same as the changed Org structure */
2286
2287 -- get maximum level for the set up
2288 SELECT max(level) into v_maximumlevel
2289 FROM per_org_structure_elements
2293 START WITH organization_id_parent = v_start_org_id
2290 WHERE org_structure_version_id = p_org_version_id
2291 CONNECT BY PRIOR organization_id_child = organization_id_parent
2292 and org_structure_version_id = p_org_version_id
2294 and org_structure_version_id = p_org_version_id;
2295
2296 -------- for pa_org_hierarchy_denorm ---------------------
2297 open get_all_orgs(p_org_version_id,v_start_org_id);
2298 loop
2299 fetch get_all_orgs into v_org_level,v_parent_org_id,v_child_org_id;
2300 exit when get_all_orgs%notfound;
2301 --r_msg('version'||p_org_version_id||'startorg'||v_start_org_id||'OU'||v_org_id);
2302 --r_msg('calling populate org levels');
2303
2304 populate_org_levels (
2305 P_org_version_id => p_org_version_id,
2306 P_organization_id_parent => v_parent_org_id,
2307 P_organization_id_child => v_child_org_id,
2308 P_org_level => v_org_level,
2309 P_org_max_level => v_maximumlevel,
2310 P_org_id => v_org_id,
2311 P_start_org_id => v_start_org_id,
2312 x_err_code => x_err_code,
2313 x_err_stage => x_err_stage ,
2314 x_err_stack => x_err_stack
2315 );
2316
2317 end loop;
2318 close get_all_orgs;
2319
2320 END LOOP; ------ end of imp loop
2321
2322 ------------- Release the Lock --------------
2323 if (pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0)
2324 and (pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) then
2325 --dbms_output.put_line('org version '||p_org_version_id ||' is lock released');
2326 --r_msg('Releasing the lock on project ...');
2327 null;
2328 end if;
2329
2330 --------------------------------------------
2331
2332 else -- not acquired the lock so exit and set the return status to error
2333 --x_return_status := FND_API.G_SET_RET_STS_ERROR;
2334 x_err_stage := 'Unable to Acqurie Lock on FORCASTING';
2335 --dbms_output.put_line('org version '||p_org_version_id ||' is locked');
2336 end if;
2337
2338 x_err_stack := old_stack;
2339
2340 EXCEPTION
2341 when others then
2342 v_success_flag := pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id));
2343 v_success_flag := pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id));
2344 x_err_code := SQLCODE;
2345 x_err_stage := SQLERRM;
2346 --r_msg('exception raised in outer'||x_err_code||x_err_stage);
2347 return;
2348 END populate_hierarchy_denorm ;
2349
2350
2351 PROCEDURE Check_Org_In_OrgHierarchy(
2352 p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2353 p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2354 p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2355 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2356 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2357 IS
2358
2359 l_exist VARCHAR2(1):= null;
2360
2361 BEGIN
2362
2363 pa_debug.init_err_stack ('pa_org_utils.Check_Org_In_OrgHierarchy');
2364
2365 select 'X' into l_exist
2366 from pa_org_hierarchy_denorm
2367 where parent_organization_id = p_organization_id
2368 and org_hierarchy_version_id = p_org_structure_version_id
2369 and pa_org_use_type = p_org_structure_type
2370 and rownum = 1;
2371
2372 x_return_status := FND_API.G_RET_STS_SUCCESS;
2373 pa_debug.reset_err_stack;
2374
2375 EXCEPTION
2376 WHEN NO_DATA_FOUND THEN
2377 x_return_status := FND_API.G_RET_STS_ERROR;
2378 x_error_message_code := 'PA_ORG_NOT_IN_ORGHIER';
2379
2380 WHEN OTHERS THEN
2381 fnd_msg_pub.add_exc_msg
2382 (p_pkg_name => 'PA_ORG_UTILS',
2383 p_procedure_name => pa_debug.g_err_stack );
2384 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2385 RAISE;
2386 END Check_Org_In_OrgHierarchy;
2387
2388 PROCEDURE Check_Org_Type(
2389 p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
2390 p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2391 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2392 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2393 IS
2394
2395 l_exist VARCHAR2(1):= null;
2396
2397 BEGIN
2398
2399 pa_debug.init_err_stack ('pa_org_utils.Check_Org_Type');
2400
2401 select 'X' into l_exist
2402 from pa_org_hierarchy_denorm
2403 where parent_organization_id = p_organization_id
2404 and pa_org_use_type = p_org_structure_type
2405 and rownum = 1;
2406
2407 x_return_status := FND_API.G_RET_STS_SUCCESS;
2408 pa_debug.reset_err_stack;
2409
2410 EXCEPTION
2411 WHEN NO_DATA_FOUND THEN
2412 x_return_status := FND_API.G_RET_STS_ERROR;
2413 IF p_org_structure_type = 'EXPENDITURES' THEN
2414 x_error_message_code := 'PA_NOT_EXP_ORG';
2415 ELSE
2419 WHEN OTHERS THEN
2416 x_error_message_code := 'PA_NOT_PROJ_ORG';
2417 END IF;
2418
2420 fnd_msg_pub.add_exc_msg
2421 (p_pkg_name => 'PA_ORG_UTILS',
2422 p_procedure_name => pa_debug.g_err_stack );
2423 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2424 RAISE;
2425 END Check_Org_Type;
2426
2427
2428 PROCEDURE Check_OrgHierarchy_Type(
2429 p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
2430 p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
2431 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2432 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2433 IS
2434
2435 l_exist VARCHAR2(1):= null;
2436
2437 BEGIN
2438
2439 pa_debug.init_err_stack ('pa_org_utils.Check_OrgHierarchy_Type');
2440
2441 select 'X' into l_exist
2442 from pa_org_hierarchy_denorm
2443 where org_hierarchy_version_id = p_org_structure_version_id
2444 and pa_org_use_type = p_org_structure_type
2445 and rownum = 1;
2446
2447 x_return_status := FND_API.G_RET_STS_SUCCESS;
2448 pa_debug.reset_err_stack;
2449
2450 EXCEPTION
2451 WHEN NO_DATA_FOUND THEN
2452 x_return_status := FND_API.G_RET_STS_ERROR;
2453 IF p_org_structure_type = 'EXPENDITURES' THEN
2454 x_error_message_code := 'PA_NOT_EXP_ORGHIER';
2455 ELSE
2456 x_error_message_code := 'PA_NOT_PROJ_ORGHIER';
2457 END IF;
2458
2459 WHEN OTHERS THEN
2460 fnd_msg_pub.add_exc_msg
2461 (p_pkg_name => 'PA_ORG_UTILS',
2462 p_procedure_name => pa_debug.g_err_stack );
2463 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2464 RAISE;
2465 END Check_OrgHierarchy_Type;
2466
2467
2468 --
2469 -- Procedure
2470 --
2471 -- Created by P Bandla
2472 -- Dated : 06-DEC-2000
2473 --
2474 --
2475 PROCEDURE Populate_Org_Hier_Denorm(
2476 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2477 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2478 IS
2479 cursor org_version is
2480 SELECT distinct
2481 imp.org_structure_version_id version_id
2482 ,imp.start_organization_id start_org_id
2483 FROM pa_implementations_all imp,
2484 per_org_structure_elements posg
2485 WHERE posg.org_structure_version_id = imp.org_structure_version_id;
2486
2487 v_err_code NUMBER;
2488 v_err_stack VARCHAR2(200);
2489 v_org_version_id NUMBER;
2490 v_organization_id_parent NUMBER;
2491 v_organization_id_child NUMBER;
2492 BEGIN
2493 x_return_status := FND_API.G_RET_STS_SUCCESS;
2494 FOR i IN org_version LOOP
2495 v_org_version_id := i.version_id;
2496 v_organization_id_parent := i.start_org_id;
2497 v_organization_id_child := 1;
2498
2499 pa_org_utils.populate_hierarchy_denorm(
2500 p_org_version_id => v_org_version_id
2501 ,p_organization_id_parent => v_organization_id_parent
2502 ,p_organization_id_child => v_organization_id_child
2503 ,x_err_code => v_err_code
2504 ,x_err_stage => x_msg_data
2505 ,x_err_stack => v_err_stack);
2506 END LOOP;
2507 EXCEPTION
2508 WHEN OTHERS THEN
2509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2510 v_err_code := SQLCODE;
2511 x_msg_data := SQLERRM;
2512 raise;
2513 END Populate_Org_Hier_Denorm;
2514
2515 PROCEDURE POPULATE_HIERARCHY_DENORM2
2516 (p_org_version_id IN NUMBER
2517 ,p_organization_id_parent IN NUMBER
2518 ,p_organization_id_child IN NUMBER
2519 ,p_org_id IN NUMBER
2520 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2521 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2522 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2523 ) IS
2524
2525 v_max_level NUMBER;
2526 old_stack VARCHAR2(2000);
2527 v_success_flag NUMBER;
2528
2529 /* Take following heirarchy for understanding this piece of logic
2530 org1
2531 / \
2532 org2 org3 -> start org in pa_implementations
2533 / / \
2534 org4 org5 org6
2535 | / \
2536 org7 org8 org9
2537 |
2538 org10
2539 in this example p_organization_id_parent = org3.
2540 The aim of this package is to populate pa_org_denorm table with following data.
2541 parent child p_level c_level
2542 org3 org5 4 3
2543 org3 org6 4 3
2544 org3 org7 4 2
2545 org3 org8 4 2
2546 org3 org9 4 2
2547 org3 org10 4 1
2548 org5 org7 3 2
2549 org6 org8 3 2
2550 org6 org9 3 2
2551 org6 org10 3 1
2552 org9 org10 2 1
2553 org3 org3 4 4
2554 org5 org5 3 3
2558 org9 org9 2 2
2555 org6 org6 3 3
2556 org7 org7 2 2
2557 org8 org8 2 2
2559 org10 org10 1 1
2560 */
2561
2562 /* this cursor will select all parents in the hierarchy below p_organization_id_parent
2563 including p_organization_id_parent. This cursor will not select leaf nodes
2564 for p_organization_id_parent = org3 it will return org3, org5,org6, org9 */
2565
2566 CURSOR all_parents (max_level number) IS
2567 SELECT distinct organization_id_parent
2568 ,(max_level - level + 1) rev_level
2569 FROM per_org_structure_elements a
2570 WHERE a.org_structure_version_id = p_org_version_id
2571 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2572 AND a.org_structure_version_id = p_org_version_id
2573 START WITH a.organization_id_parent = p_organization_id_parent
2574 AND a.org_structure_version_id = p_org_version_id
2575 ORDER by rev_level desc;
2576
2577 BEGIN
2578
2579 x_err_code := 0;
2580 old_stack := x_err_stack;
2581 x_err_stack := x_err_stack || 'populate_hierarchy_denorm';
2582
2583 --- Delete all rows from pa_cc_tp_schedule_line_lkp
2584 delete from pa_cc_tp_schedule_line_lkp;
2585
2586 ------- Acquire a lock before processing ----------------
2587
2588 IF (pa_debug.acquire_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0) AND
2589 (pa_debug.acquire_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) THEN
2590
2591 /*
2592 mano_msg('p_org_version_id = ' || p_org_version_id );
2593 mano_msg('p_organization_id_parent = ' || p_organization_id_parent );
2594 mano_msg('p_organization_id_child = ' || p_organization_id_child );
2595 mano_msg('p_org_id = ' || p_org_id );
2596 */
2597
2598 /* delete from pa_org_hierarchy_denorm */
2599
2600 DELETE from pa_org_hierarchy_denorm
2601 WHERE org_hierarchy_version_id = p_org_version_id
2602 AND nvl(org_id, -99) = nvl(p_org_id, -99)
2603 AND pa_org_use_type = 'REPORTING'; /* 2976953-Added nvl condition for org id and check for
2604 pa_org_use_type being REPORTING */
2605
2606 /* get the max level i.e. number of levels under p_organization_id_parent.
2607 value of max_level for the heirarchy shown for p_organization_id_parent = org3
2608 will be 4
2609 */
2610
2611 SELECT MAX(level+1)
2612 INTO v_max_level
2613 FROM per_org_structure_elements a
2614 WHERE a.org_structure_version_id = p_org_version_id
2615 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2616 AND a.org_structure_version_id = p_org_version_id
2617 START WITH a.organization_id_parent = p_organization_id_parent
2618 AND a.org_structure_version_id = p_org_version_id;
2619
2620 --mano_msg('v_max_level = ' || v_max_level);
2621
2622 FOR c1rec in all_parents (v_max_level) LOOP
2623
2624 /*
2625 mano_msg('c1rec.organization_id_parent = ' || c1rec.organization_id_parent);
2626 mano_msg('c1rec.rev_level = ' || c1rec.rev_level);
2627 */
2628
2629 /*
2630 for each parent insert all childs
2631 this will be called for each parent i.e. org3, org5, org6, org9
2632 For each parent it will insert all of its child at any level. Like
2633 for org3 it will insert org5-10 with their appropriate level
2634 */
2635
2636 INSERT INTO pa_org_hierarchy_denorm
2637 ( parent_organization_id
2638 , child_organization_id
2639 , org_hierarchy_version_id
2640 , pa_org_use_type
2641 , creation_date
2642 , created_by
2643 , last_update_date
2644 , last_updated_by
2645 , last_update_login
2646 , parent_level
2647 , child_level
2648 , org_id
2649 )
2650 (SELECT c1rec.organization_id_parent
2651 , organization_id_child
2652 , org_structure_version_id
2653 , 'REPORTING'
2654 , sysdate
2655 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2656 , sysdate
2657 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2658 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2659 , c1rec.rev_level
2660 , c1rec.rev_level - level
2661 , p_org_id
2662 FROM per_org_structure_elements a
2663 WHERE a.org_structure_version_id = p_org_version_id
2664 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2665 AND a.org_structure_version_id = p_org_version_id
2666 START WITH a.organization_id_parent = c1rec.organization_id_parent
2667 AND a.org_structure_version_id = p_org_version_id
2668 );
2669
2670 --mano_msg('inserted ' || sql%rowcount || ' rows');
2671
2672 END LOOP;
2673
2674 /* now insert all organizations in the heirarchy under p_organization_id_parent
2675 into this table with parent and child organization id same. As all organizations
2679
2676 in the heirarchy has to be a child of start org so select all childs from denorm
2677 table for this parent.
2678 */
2680 --mano_msg('now inserting for each child ');
2681
2682 INSERT INTO pa_org_hierarchy_denorm
2683 ( parent_organization_id
2684 , child_organization_id
2685 , org_hierarchy_version_id
2686 , pa_org_use_type
2687 , creation_date
2688 , created_by
2689 , last_update_date
2690 , last_updated_by
2691 , last_update_login
2692 , parent_level
2693 , child_level
2694 , org_id
2695 )
2696 (SELECT child_organization_id
2697 , child_organization_id
2698 , org_hierarchy_version_id
2699 , 'REPORTING'
2700 , sysdate
2701 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2702 , sysdate
2703 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2704 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2705 , child_level
2706 , child_level
2707 , p_org_id
2708 FROM pa_org_hierarchy_denorm
2709 where org_hierarchy_version_id = p_org_version_id -- org_hierarchy_version_id Changed via bug 2890156
2710 and nvl(org_id, -99) = nvl(p_org_id, -99) /* 2976953-Added nvl to the org_id condition */
2711 and pa_org_use_type = 'REPORTING' /* 2976953- Added this condition */
2712 and parent_organization_id = p_organization_id_parent
2713 UNION ALL
2714 SELECT p_organization_id_parent
2715 , p_organization_id_parent
2716 , p_org_version_id
2717 , 'REPORTING'
2718 , sysdate
2719 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2720 , sysdate
2721 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
2722 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
2723 , v_max_level
2724 , v_max_level
2725 , p_org_id
2726 FROM dual
2727 );
2728
2729 --mano_msg('inserted ' || sql%rowcount || ' rows');
2730
2731 ------------- Release the Lock --------------
2732 IF (pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id)) = 0) AND
2733 (pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id)) = 0) THEN
2734 NULL;
2735 END IF;
2736
2737
2738 ELSE
2739 -- not acquired the lock so exit and set the return status to error
2740 x_err_stage := 'Unable to Acqurie Lock on FORCASTING';
2741
2742 END IF;
2743
2744 x_err_stack := old_stack;
2745
2746 EXCEPTION
2747 WHEN OTHERS THEN
2748 v_success_flag := pa_debug.release_user_lock('FCLOCK:'||to_char(p_org_version_id));
2749 v_success_flag := pa_debug.release_user_lock('ACLOCK:'||to_char(p_org_version_id));
2750 x_err_code := SQLCODE;
2751 x_err_stage := SQLERRM;
2752 --r_msg('exception raised in outer'||x_err_code||x_err_stage);
2753 RETURN;
2754
2755 END POPULATE_HIERARCHY_DENORM2;
2756
2757 /* Bug#2643047 - This procedure is added so as to populate organizations for REPORTING pa_org_use_type
2758 when a new organization is added in the hierarchy. That is added in per_org_structure_elements table.
2759 The call to this procedure will be made from maintain_org_hist_bri
2760 The newly added organization is x_organiation_id_child which is added under x_organization_id_parent*/
2761
2762 Procedure populate_reporting_orgs(
2763 x_org_version_id in number,
2764 x_organization_id_child in number,
2765 x_organization_id_parent in number,
2766 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2767 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2768 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
2769 ) IS
2770 old_stack VARCHAR2(2000);
2771
2772 x_parent_level number;
2773 x_child_level number;
2774 x_exists_in_denorm varchar2(1);
2775
2776 Cursor get_all_orgs(p_org_id number) is
2777 select parent_organization_id, parent_level
2778 from pa_org_hierarchy_denorm
2779 where org_hierarchy_version_id = x_org_version_id
2780 and child_organization_id = x_organization_id_parent
2781 and pa_org_use_type = 'REPORTING'
2782 and nvl(org_id, -99) = nvl(p_org_id, -99);
2783
2784 Begin
2785 x_err_code := 0;
2786 old_stack := x_err_stack;
2787 x_err_stack := x_err_stack || 'populate_reporting_orgs';
2788
2789 /* For all operating units where the Org structure
2790 specified is same as the changed Org structure */
2791 for imp_rec in ( select org_id
2792 from pa_implementations_all imp
2793 where org_structure_version_id = x_org_version_id
2794 )
2795 loop
2796
2797 /* Get the level for the organization x_organization_id_parent */
2798 /* The variable x_exists_in_denorm is to check whether the parent organization is
2799 existing in pa_org_hierarchy_denorm table, if it is not there it means that the parent
2800 organization is not in the reporting hierarchy below the reporting start organization */
2801
2802 x_exists_in_denorm := 'Y';
2803
2804 begin
2805 select unique parent_level into x_parent_level from pa_org_hierarchy_denorm
2809 and pa_org_use_type = 'REPORTING'
2806 where org_hierarchy_version_id = x_org_version_id
2807 and parent_organization_id = x_organization_id_parent
2808 and child_organization_id = x_organization_id_parent
2810 and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953 - Added nvl for the org id condition */
2811 exception when NO_DATA_FOUND then
2812 x_exists_in_denorm := 'N';
2813 end;
2814
2815 /*
2816 Take following heirarchy for understanding this piece of logic
2817 org1
2818 / \
2819 org2 org3 -> start org in pa_implementations
2820 / / \
2821 org4 org5 org6
2822 | / \
2823 org7 org8 org9
2824 |
2825 org10
2826 in this example p_organization_id_parent = org3.
2827 The data in pa_org_hierarchy_denorm is as follows:
2828 parent child p_level c_level
2829 org3 org5 4 3
2830 org3 org6 4 3
2831 org3 org7 4 2
2832 org3 org8 4 2
2833 org3 org9 4 2
2834 org3 org10 4 1
2835 org5 org7 3 2
2836 org6 org8 3 2
2837 org6 org9 3 2
2838 org6 org10 3 1
2839 org9 org10 2 1
2840 org3 org3 4 4
2841 org5 org5 3 3
2842 org6 org6 3 3
2843 org7 org7 2 2
2844 org8 org8 2 2
2845 org9 org9 2 2
2846 org10 org10 1 1
2847 now suppose org11 is added under org10, then all the levels for this hierarchy needs to be changed else not reqd.
2848 So if parent_level is equal to 1, then parent is a leaf node, so when a new org is added to leaf org,
2849 the whole hierarchy levels needs to be changed (all levels need to be incremented by 1).
2850 Else not required.
2851 */
2852
2853 /* Need to process only if x_exists_in_denorm is 'Y' */
2854
2855 if (x_exists_in_denorm = 'Y') then
2856 if (x_parent_level = 1) then
2857 update pa_org_hierarchy_denorm
2858 set parent_level = parent_level + 1,
2859 child_level = child_level + 1
2860 where org_hierarchy_version_id = x_org_version_id
2861 and pa_org_use_type = 'REPORTING'
2862 and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953-Added nvl condition for org_id */
2863 x_child_level := 1; /* Child level is lowest, so 1 in this case */
2864 else
2865 x_child_level := x_parent_level - 1; /* Child level will be 1 less than parent level as parent level <> 1 */
2866 end if;
2867
2868 /* Now we have the child level, so far all the parents of x_organization_id_parent, we need add a record in pa_org_hierarchy_denorm
2869 along with x_organization_id_child */
2870
2871 for rec in get_all_orgs(imp_rec.org_id) loop
2872 create_reporting_org(rec.parent_organization_id,
2873 x_organization_id_child,
2874 x_org_version_id,
2875 'REPORTING',
2876 rec.parent_level,
2877 x_child_level,
2878 imp_rec.org_id,
2879 x_err_code,
2880 x_err_stage,
2881 x_err_stack);
2882 end loop;
2883 /* We need to populate a record with x_organization_id_child as parent as well as child */
2884 create_reporting_org(x_organization_id_child,
2885 x_organization_id_child,
2886 x_org_version_id,
2887 'REPORTING',
2888 x_child_level,
2889 x_child_level,
2890 imp_rec.org_id,
2891 x_err_code,
2892 x_err_stage,
2893 x_err_stack);
2894 end if; /* for check of x_exists_in_denorm */
2895
2896 end loop;
2897
2898 x_err_stack := old_stack;
2899 EXCEPTION
2900 WHEN OTHERS THEN
2901 x_err_code := SQLCODE;
2902 x_err_stage := SQLERRM;
2903 RETURN;
2904
2905 end populate_reporting_orgs;
2906
2907
2908 /* Bug#2643047 - This procedure is added to restructure the levels in the table
2909 pa_og_hierarchy_denorm for REPORTING pa_org_use_type when a organization is deleted from the hierarchy.
2910 The call to this procedure will be made from maintain_org_hist_brd.
2911 The deleted organization is x_organiation_id_child */
2912
2913 procedure restructure_rpt_orgs_denorm(
2914 x_org_version_id in number,
2915 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2916 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2917 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
2918 )
2919 IS
2920 old_stack VARCHAR2(2000);
2921
2922 x_exists number;
2923
2924 Cursor check_lower_org_exists(p_org_id number) IS
2925 Select 1
2926 from dual
2927 where exists
2928 (select 1 from pa_org_hierarchy_denorm
2929 where org_hierarchy_version_id = x_org_version_id and
2930 pa_org_use_type = 'REPORTING' and
2931 nvl(org_id, -99) = nvl(p_org_id, -99) and /* 2976953 Added nvl for org_id condition */
2932 parent_level = 1
2933 );
2934
2935
2939 x_err_stack := x_err_stack || 'restructure_rpt_orgs_denorm';
2936 Begin
2937 x_err_code := 0;
2938 old_stack := x_err_stack;
2940
2941 /* For all operating units where the Project Org structure
2942 specified is same as the changed Org structure */
2943 for imp_rec in ( select org_id
2944 from pa_implementations_all imp
2945 where org_structure_version_id = x_org_version_id
2946 )
2947 loop
2948
2949 /* check_lower_org_exists is the cursor to find if there is any other organization also at the leaf level
2950 as x_organization_id_child which was deleted, if yes no need to restructure the records else we need to decrement
2951 all the levels so that other organization (s) which are actually now leaf nodes in the hierarchy have their levels as 1
2952 and other organizations in the hierarchy have levels changed accordingly */
2953
2954 open check_lower_org_exists(imp_rec.org_id);
2955 fetch check_lower_org_exists into x_exists;
2956
2957 If check_lower_org_exists%notfound then
2958 update pa_org_hierarchy_denorm
2959 set parent_level = parent_level - 1,
2960 child_level = child_level - 1
2961 where org_hierarchy_version_id = x_org_version_id
2962 and pa_org_use_type = 'REPORTING'
2963 and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953 Added nvl for org_id condition */
2964 end if;
2965 close check_lower_org_exists;
2966 end loop;
2967
2968 x_err_stack := old_stack;
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971 x_err_code := SQLCODE;
2972 x_err_stage := SQLERRM;
2973 RETURN;
2974
2975 end restructure_rpt_orgs_denorm;
2976
2977 /* Bug 3649799 - This procedure will be called by statement level trigger for update on
2978 per_org_structure_elements. */
2979
2980 procedure maintain_org_hist_update(x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
2981 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
2982 x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
2983
2984
2985 /* This cursor is to get all the parents for the organization l_start_org_id in hierarchy l_org_struct_ver_id */
2986
2987 CURSOR all_parents (max_level number,
2988 l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
2989 l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
2990 SELECT distinct organization_id_parent
2991 ,(max_level - level + 1) rev_level
2992 FROM per_org_structure_elements a
2993 WHERE a.org_structure_version_id = l_org_struct_ver_id
2994 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
2995 AND a.org_structure_version_id = l_org_struct_ver_id
2996 START WITH a.organization_id_parent = l_start_org_id
2997 AND a.org_structure_version_id = l_org_struct_ver_id
2998 ORDER by rev_level desc;
2999
3000 /* Added this cursor for bug 4218163 */
3001 CURSOR proj_exp_check(c_org_struct_version_id IN per_org_structure_elements.org_structure_version_id%TYPE)
3002 IS
3003 SELECT 'Y' FROM pa_implementations_all
3004 WHERE proj_org_structure_version_id = c_org_struct_version_id
3005 OR exp_org_structure_version_id = c_org_struct_version_id;
3006
3007 l_version_id per_org_structure_elements.org_structure_version_id%TYPE;
3008 l_new_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3009 l_new_child_org_id per_org_structure_elements.organization_id_child%TYPE;
3010 v_max_level number;
3011 l_imp_proj_exp VARCHAR2(1);
3012
3013 -- Added for bug#5952671
3014
3015 cursor pa_org_hier_rec (l_org_id_parent per_org_structure_elements.organization_id_parent%TYPE,
3016 l_level number,
3017 l_version_id per_org_structure_elements.org_structure_version_id%TYPE) IS
3018 SELECT organization_id_child
3019 , l_level - level
3020 FROM per_org_structure_elements a
3021 WHERE a.org_structure_version_id = l_version_id
3022 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3023 AND a.org_structure_version_id = l_version_id
3024 START WITH a.organization_id_parent = l_org_id_parent
3025 AND a.org_structure_version_id = l_version_id;
3026
3027 cursor pa_org_hier_sec_rec (l_version_id per_org_structure_elements.org_structure_version_id%TYPE,
3028 l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE,
3029 l_org_id pa_implementations_all.org_id%type) IS
3030 SELECT child_organization_id
3031 , child_level
3032 FROM pa_org_hierarchy_denorm
3033 where org_hierarchy_version_id = l_version_id
3034 and nvl(org_id, -99) = nvl(l_org_id, -99)
3035 and pa_org_use_type = 'REPORTING'
3036 and parent_organization_id = l_start_org_id;
3037
3038 cursor all_org_id (l_version_id pa_implementations_all.org_structure_version_id%TYPE,
3039 l_start_org_id IN pa_implementations_all.start_organization_id%TYPE) IS
3040 SELECT org_id FROM pa_implementations_all
3041 WHERE org_structure_version_id = l_version_id
3042 and start_organization_id = l_start_org_id;
3043
3044 l_child_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3048 l_tmp_org_id pa_implementations_all.org_id%type;
3045 l_child_level_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3046 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3047
3049 l_plsql_max_array_size CONSTANT NUMBER := 1000;
3050 -- End for bug#5952671
3051
3052 --Start 5934849
3053 TYPE l_str_version_id_tbltype IS TABLE OF
3054 per_org_structure_elements.org_structure_version_id%TYPE index by binary_integer;
3055
3056 L_STR_VERSION_ID_TMP l_str_version_id_tbltype;
3057 L_STR_VERSION_ID l_str_version_id_tbltype;
3058 l_str_exists varchar2(1) :='N';
3059 --End 5934849
3060
3061 BEGIN
3062 --Start 5934849
3063 --Get the org_structure_version_id into L_STR_VERSION_ID_TMP tab
3064 --for the rowids in newRows tab
3065 FOR k in pa_org_utils.newRows.first..pa_org_utils.newRows.last Loop
3066 select distinct org_structure_version_id
3067 into L_STR_VERSION_ID_TMP(L_STR_VERSION_ID_TMP.count+1)
3068 from per_org_structure_elements
3069 where
3070 ROWID = pa_org_utils.newRows(k);
3071 End loop;
3072
3073 --Get the distinct rowids into L_STR_VERSION_ID tab
3074 --from L_STR_VERSION_ID_TMP tab
3075 FOR i IN 1 .. L_STR_VERSION_ID_TMP.COUNT LOOP
3076 l_str_exists := 'N';
3077 FOR j IN 1 .. L_STR_VERSION_ID.COUNT LOOP
3078 if L_STR_VERSION_ID_TMP(i) = L_STR_VERSION_ID(j) then
3079 l_str_exists := 'Y';
3080 Exit;
3081 end if;
3082 end loop;
3083 if l_str_exists = 'N' then
3084 L_STR_VERSION_ID(L_STR_VERSION_ID.count+1) := L_STR_VERSION_ID_TMP(i);
3085 End if;
3086 end loop;
3087
3088
3089 FOR i IN 1 .. L_STR_VERSION_ID.COUNT LOOP
3090
3091 l_version_id := L_STR_VERSION_ID(i);
3092 --End 5934849
3093
3094
3095 /*FOR i IN 1 .. pa_org_utils.newRows.COUNT LOOP commented for bug 5934849 */
3096
3097
3098 /* Fetching the Organization Hierarchy version id, the new parent organization id and child organization id
3099
3100 SELECT org_structure_version_id, organization_id_parent, organization_id_child
3101 INTO l_version_id, l_new_parent_org_id, l_new_child_org_id
3102 FROM per_org_structure_elements
3103 WHERE ROWID = pa_org_utils.newRows(i); */
3104 maintain_pa_all_org( x_org_version_id => l_version_id,
3105 x_err_code => x_err_code,
3106 x_err_stage => x_err_stage,
3107 x_err_stack => x_err_stack);
3108
3109 maintain_projexp_org_update( p_version_id => l_version_id,
3110 p_org_use_type => 'PROJECTS',
3111 x_err_code => x_err_code,
3112 x_err_stage => x_err_stage,
3113 x_err_stack => x_err_stack);
3114
3115 maintain_projexp_org_update( p_version_id => l_version_id,
3116 p_org_use_type => 'EXPENDITURES',
3117 x_err_code => x_err_code,
3118 x_err_stage => x_err_stage,
3119 x_err_stack => x_err_stack);
3120
3121 /* Bug 4218163 - Checking whether the hierarchy version is used in PA, calling rebuild
3122 for TP_SCHEDULE only if the hierarchy is used as Project / Expenditure hierarchy in PA */
3123
3124 OPEN proj_exp_check(l_version_id);
3125 FETCH proj_exp_check INTO l_imp_proj_exp;
3126 IF proj_exp_check%FOUND THEN
3127 populate_hier_denorm_sch( p_org_version_id => l_version_id
3128 ,x_err_code => x_err_code
3129 ,x_err_stage => x_err_stage
3130 ,x_err_stack => x_err_stack );
3131 END IF;
3132 CLOSE proj_exp_check;
3133
3134 /* The Code below is to populate the denorm data for Reporting pa_org_use_type.
3135 For each org id which uses the hierarchy updated, we first delete the data from denorm table
3136 Then get the maximum level in that hierarchy.
3137 Then insert the appropriate combinations in the denorm table.
3138 The code is similar to the populate_hierarchy_denorm2 procedure added in 115.25 version of this file */
3139
3140 /* Commented and added for bug#5952671
3141 FOR imp_rec IN (SELECT org_id, start_organization_id FROM
3142 pa_implementations_all
3143 WHERE org_structure_version_id = l_version_id) */
3144 FOR imp_rec IN (SELECT distinct start_organization_id FROM
3145 pa_implementations_all
3146 WHERE org_structure_version_id = l_version_id)
3147 LOOP
3148 /* Commented for bug#5952671
3149 DELETE from pa_org_hierarchy_denorm
3150 WHERE org_hierarchy_version_id = l_version_id
3151 AND org_id = imp_rec.org_id
3152 AND pa_org_use_type = 'REPORTING'; */
3153 /* Added For bug#5952671 */
3154 l_tmp_org_id := NULL;
3155 l_org_id_tbl.delete;
3156 OPEN all_org_id(l_version_id, imp_rec.start_organization_id);
3157 FETCH all_org_id BULK COLLECT INTO l_org_id_tbl;
3158 CLOSE all_org_id;
3159
3160 FORALL i in l_org_id_tbl.first..l_org_id_tbl.last
3161 DELETE from pa_org_hierarchy_denorm
3162 WHERE org_hierarchy_version_id = l_version_id
3163 AND nvl(org_id, -99) = nvl(l_org_id_tbl(i), -99)
3164 AND pa_org_use_type = 'REPORTING';
3165 l_tmp_org_id := l_org_id_tbl(l_org_id_tbl.last);
3166 /* End bug#5952671 */
3167
3168 SELECT MAX(level+1)
3172 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3169 INTO v_max_level
3170 FROM per_org_structure_elements a
3171 WHERE a.org_structure_version_id = l_version_id
3173 AND a.org_structure_version_id = l_version_id
3174 START WITH a.organization_id_parent = imp_rec.start_organization_id
3175 AND a.org_structure_version_id = l_version_id;
3176
3177 /* Added for bug#5952671 */
3178 FOR c1rec IN all_parents (v_max_level, l_version_id, imp_rec.start_organization_id) LOOP
3179
3180 OPEN pa_org_hier_rec(c1rec.organization_id_parent
3181 ,c1rec.rev_level
3182 , l_version_id);
3183 LOOP
3184 l_child_organization_id_tbl.delete;
3185 l_child_level_tbl.delete;
3186 FETCH pa_org_hier_rec BULK COLLECT INTO
3187 l_child_organization_id_tbl
3188 ,l_child_level_tbl
3189 LIMIT l_plsql_max_array_size;
3190
3191 IF nvl(l_child_organization_id_tbl.last,0) > 0 THEN
3192 FOR imp1_rec IN (SELECT org_id FROM
3193 pa_implementations_all
3194 WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
3195 LOOP
3196 forall i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
3197 INSERT INTO pa_org_hierarchy_denorm
3198 ( parent_organization_id
3199 , child_organization_id
3200 , org_hierarchy_version_id
3201 , pa_org_use_type
3202 , creation_date
3203 , created_by
3204 , last_update_date
3205 , last_updated_by
3206 , last_update_login
3207 , parent_level
3208 , child_level
3209 , org_id
3210 ) values
3211 (
3212 c1rec.organization_id_parent
3213 ,l_child_organization_id_tbl(i)
3214 ,l_version_id
3215 ,'REPORTING'
3216 ,sysdate
3217 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3218 ,sysdate
3219 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3220 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3221 ,c1rec.rev_level
3222 ,l_child_level_tbl(i)
3223 ,imp1_rec.org_id
3224 );
3225 END LOOP;
3226 END IF;
3227 EXIT WHEN nvl(l_child_organization_id_tbl.last,0) < l_plsql_max_array_size;
3228 END LOOP;
3229 CLOSE pa_org_hier_rec;
3230 END LOOP;
3231
3232 OPEN pa_org_hier_sec_rec (l_version_id ,
3233 imp_rec.start_organization_id,
3234 l_tmp_org_id);
3235 LOOP
3236 l_child_organization_id_tbl.delete;
3237 l_child_level_tbl.delete;
3238 FETCH pa_org_hier_sec_rec BULK COLLECT INTO
3239 l_child_organization_id_tbl
3240 ,l_child_level_tbl
3241 LIMIT l_plsql_max_array_size;
3242
3243 IF nvl(l_child_organization_id_tbl.last,0) > 0 THEN
3244 FOR imp1_rec IN (SELECT org_id FROM
3245 pa_implementations_all
3246 WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
3247 LOOP
3248 FORALL i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
3249 INSERT INTO pa_org_hierarchy_denorm
3250 ( parent_organization_id
3251 , child_organization_id
3252 , org_hierarchy_version_id
3253 , pa_org_use_type
3254 , creation_date
3255 , created_by
3256 , last_update_date
3257 , last_updated_by
3258 , last_update_login
3259 , parent_level
3260 , child_level
3261 , org_id
3262 ) values
3263 (
3264 l_child_organization_id_tbl(i)
3265 ,l_child_organization_id_tbl(i)
3266 ,l_version_id
3267 ,'REPORTING'
3268 ,sysdate
3269 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3270 ,sysdate
3271 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3272 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3273 ,l_child_level_tbl(i)
3274 ,l_child_level_tbl(i)
3275 ,imp1_rec.org_id
3276 );
3277 END LOOP;
3278 END IF;
3279 EXIT WHEN nvl(l_child_organization_id_tbl.last,0) < l_plsql_max_array_size;
3280 END LOOP;
3281 CLOSE pa_org_hier_sec_rec;
3282 FOR imp1_rec IN (SELECT org_id FROM
3283 pa_implementations_all
3287 ( parent_organization_id
3284 WHERE org_structure_version_id = l_version_id AND start_organization_id = imp_rec.start_organization_id)
3285 LOOP
3286 INSERT INTO pa_org_hierarchy_denorm
3288 , child_organization_id
3289 , org_hierarchy_version_id
3290 , pa_org_use_type
3291 , creation_date
3292 , created_by
3293 , last_update_date
3294 , last_updated_by
3295 , last_update_login
3296 , parent_level
3297 , child_level
3298 , org_id
3299 ) VALUES
3300 (
3301 imp_rec.start_organization_id
3302 ,imp_rec.start_organization_id
3303 ,l_version_id
3304 ,'REPORTING'
3305 ,sysdate
3306 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3307 ,sysdate
3308 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3309 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3310 ,v_max_level
3311 ,v_max_level
3312 ,imp1_rec.org_id
3313 );
3314 END LOOP;
3315 /* End bug#5952671*/
3316 /* Commented for bug#5952671
3317 FOR c1rec in all_parents (v_max_level, l_version_id, imp_rec.start_organization_id) LOOP
3318 INSERT INTO pa_org_hierarchy_denorm
3319 ( parent_organization_id
3320 , child_organization_id
3321 , org_hierarchy_version_id
3322 , pa_org_use_type
3323 , creation_date
3324 , created_by
3325 , last_update_date
3326 , last_updated_by
3327 , last_update_login
3328 , parent_level
3329 , child_level
3330 , org_id
3331 )
3332 (SELECT c1rec.organization_id_parent
3333 , organization_id_child
3334 , l_version_id
3335 , 'REPORTING'
3336 , sysdate
3337 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3338 , sysdate
3339 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3340 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3341 , c1rec.rev_level
3342 , c1rec.rev_level - level
3343 , imp_rec.org_id
3344 FROM per_org_structure_elements a
3345 WHERE a.org_structure_version_id = l_version_id
3346 CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
3347 AND a.org_structure_version_id = l_version_id
3348 START WITH a.organization_id_parent = c1rec.organization_id_parent
3349 AND a.org_structure_version_id = l_version_id
3350 );
3351 END LOOP;
3352
3353
3354 INSERT INTO pa_org_hierarchy_denorm
3355 ( parent_organization_id
3356 , child_organization_id
3357 , org_hierarchy_version_id
3358 , pa_org_use_type
3359 , creation_date
3360 , created_by
3361 , last_update_date
3362 , last_updated_by
3363 , last_update_login
3364 , parent_level
3365 , child_level
3366 , org_id
3367 )
3368 (SELECT child_organization_id
3369 , child_organization_id
3370 , org_hierarchy_version_id
3371 , 'REPORTING'
3372 , sysdate
3373 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3374 , sysdate
3375 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3376 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3377 , child_level
3378 , child_level
3379 , imp_rec.org_id
3380 FROM pa_org_hierarchy_denorm
3381 where org_hierarchy_version_id = l_version_id
3382 and org_id = imp_rec.org_id
3383 and pa_org_use_type = 'REPORTING' -- Added for bug#5361709
3384 and parent_organization_id = imp_rec.start_organization_id
3385 UNION ALL
3386 SELECT imp_rec.start_organization_id
3387 , imp_rec.start_organization_id
3388 , l_version_id
3389 , 'REPORTING'
3390 , sysdate
3391 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3392 , sysdate
3393 , nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
3394 , nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
3395 , v_max_level
3396 , v_max_level
3397 , imp_rec.org_id
3398 FROM dual
3399 ); */
3400
3401 END LOOP;
3402
3403 END LOOP;
3404
3405 END maintain_org_hist_update;
3406
3407 /* Bug 3649799 - This procedure will be called for rebuilding project and expenditure
3408 hierarchy in pa_org_hierarchy_denorm table. */
3409
3410 procedure maintain_projexp_org_update(p_version_id in number,
3411 p_org_use_type in varchar2,
3412 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3413 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3414 x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
3418 l_parent_org_id per_org_structure_elements.organization_id_parent%TYPE;
3415 l_imp_proj_exp VARCHAR2(1);
3416 l_exist_recs VARCHAR2(1);
3417 class_org_true varchar2(3);
3419 l_child_org_id per_org_structure_elements.organization_id_parent%TYPE;
3420
3421 Cursor start_orgs (c_version_id in number) IS
3422 SELECT decode(p_org_use_type, 'PROJECTS', proj_start_org_id, 'EXPENDITURES', exp_start_org_id) start_organization_id
3423 FROM pa_implementations_all imp
3424 WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
3425 'EXPENDITURES', exp_org_structure_version_id) = c_version_id;
3426
3427
3428 CURSOR c_get_new_org ( c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3429 c_start_org_id IN per_org_structure_elements.organization_id_child%TYPE)
3430 IS
3431 SELECT c_start_org_id FROM dual
3432 UNION ALL
3433 SELECT organization_id_child
3434 FROM per_org_structure_elements
3435 WHERE org_structure_version_id = c_org_struct_ver_id
3436 CONNECT BY PRIOR organization_id_child = organization_id_parent
3437 AND org_structure_version_id = c_org_struct_ver_id
3438 START WITH organization_id_parent = c_start_org_id
3439 AND org_structure_version_id = c_org_struct_ver_id;
3440
3441 CURSOR c_get_sub_orgs (c_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
3442 c_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
3443 SELECT c_org_id
3444 FROM dual
3445 UNION ALL
3446 SELECT organization_id_child
3447 FROM per_org_structure_elements
3448 WHERE org_structure_version_id = c_org_struct_ver_id
3449 CONNECT BY PRIOR organization_id_child = organization_id_parent
3450 AND org_structure_version_id = c_org_struct_ver_id
3451 START WITH organization_id_parent = c_org_id
3452 AND org_structure_version_id = c_org_struct_ver_id ;
3453
3454 BEGIN
3455
3456 BEGIN
3457
3458 SELECT 'Y' INTO l_imp_proj_exp
3459 FROM pa_implementations_all
3460 WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
3461 'EXPENDITURES', exp_org_structure_version_id) = p_version_id
3462 AND ROWNUM = 1;
3463
3464 DELETE FROM pa_org_hierarchy_denorm
3465 WHERE org_hierarchy_version_id = p_version_id
3466 AND pa_org_use_type = p_org_use_type;
3467
3468 EXCEPTION
3469 WHEN NO_DATA_FOUND THEN
3470 l_imp_proj_exp := 'N';
3471 END;
3472
3473 FOR imp_rec IN start_orgs(p_version_id)
3474 LOOP
3475 BEGIN
3476 SELECT 'Y' INTO l_exist_recs
3477 FROM pa_org_hierarchy_denorm
3478 WHERE org_hierarchy_version_id = p_version_id
3479 AND pa_org_use_type = p_org_use_type
3480 AND parent_organization_id = imp_rec.start_organization_id
3481 AND ROWNUM = 1;
3482 EXCEPTION
3483 WHEN NO_DATA_FOUND THEN
3484 l_exist_recs := 'N';
3485 END;
3486
3487 IF l_exist_recs = 'N' THEN
3488 OPEN c_get_new_org(p_version_id, imp_rec.start_organization_id);
3489 LOOP
3490 FETCH c_get_new_org INTO l_parent_org_id;
3491 EXIT WHEN c_get_new_org%NOTFOUND;
3492 OPEN c_get_sub_orgs(p_version_id, l_parent_org_id);
3493 LOOP
3494 FETCH c_get_sub_orgs INTO l_child_org_id;
3495 EXIT WHEN c_get_sub_orgs%NOTFOUND;
3496 BEGIN
3497 SELECT 'YES' INTO class_org_true
3498 FROM hr_organization_information
3499 WHERE organization_id = l_child_org_id
3500 AND ORG_INFORMATION_CONTEXT = 'CLASS'
3501 AND ORG_INFORMATION1 = decode(p_org_use_type, 'PROJECTS', 'PA_PROJECT_ORG',
3502 'EXPENDITURES', 'PA_EXPENDITURE_ORG')
3503 AND ORG_INFORMATION2 = 'Y';
3504 EXCEPTION WHEN NO_DATA_FOUND THEN
3505 class_org_true := 'NO';
3506 END ;
3507
3508 IF class_org_true = 'YES' THEN
3509 pa_org_utils.CREATE_ORG_HIERARCHY_DENORM(p_parent_organization_id=> l_parent_org_id
3510 , p_child_organization_id => l_child_org_id
3511 , p_org_hierarchy_version_id=> p_version_id
3512 , p_pa_org_use_type => p_org_use_type
3513 , x_err_code => x_err_code
3514 , x_err_stage => x_err_stage
3515 , x_err_stack => x_err_stack);
3516 END IF;
3517 END LOOP;
3518 CLOSE c_get_sub_orgs;
3519 END LOOP;
3520 CLOSE c_get_new_org;
3521 END IF;
3522 END LOOP;
3523
3524 END maintain_projexp_org_update;
3525
3526
3527 /* Bug 5934849 - This procedure will be called for maintaining project and expenditure
3528 data in pa_all_organizations table. */
3529
3530 Procedure maintain_pa_all_org(x_org_version_id in number,
3531 x_err_code in out NOCOPY number,
3532 x_err_stage in out NOCOPY varchar2,
3533 x_err_stack in out NOCOPY varchar2) IS
3534
3535 v_start_org_id number;
3536 v_org_id number;
3537
3541 WHERE org_structure_version_id = x_org_version_id
3538 CURSOR cur_proj_new_org IS
3539 SELECT se.organization_id_child organization_id
3540 FROM per_org_structure_elements se
3542 AND EXISTS (select 'X'
3543 from hr_organization_information info
3544 where info.organization_id = se.organization_id_child
3545 and info.org_information1 = 'PA_PROJECT_ORG'
3546 and info.org_information_context||'' = 'CLASS'
3547 and info.org_information2 = 'Y')
3548 CONNECT BY PRIOR se.organization_id_child
3549 = se.organization_id_parent
3550 AND org_structure_version_id = x_org_version_id
3551 START WITH se.organization_id_parent = v_start_org_id
3552 AND org_structure_version_id = x_org_version_id
3553 UNION
3554 SELECT v_start_org_id FROM Sys.dual -- Added exists clause for Bug# 1650520
3555 WHERE EXISTS (select 'X'
3556 from hr_organization_information info
3557 where info.org_information1 = 'PA_PROJECT_ORG'
3558 and info.org_information_context||'' = 'CLASS'
3559 and info.org_information2 = 'Y'
3560 and info.organization_id = v_start_org_id);
3561
3562 CURSOR cur_exp_new_org IS
3563 SELECT se.organization_id_child organization_id
3564 FROM per_org_structure_elements se
3565 WHERE org_structure_version_id = x_org_version_id
3566 AND EXISTS (select 'X'
3567 from hr_organization_information info
3568 where info.organization_id = se.organization_id_child
3569 and info.org_information1 = 'PA_EXPENDITURE_ORG'
3570 and info.org_information_context||'' = 'CLASS'
3571 and info.org_information2 = 'Y')
3572 CONNECT BY PRIOR se.organization_id_child
3573 = se.organization_id_parent
3574 AND org_structure_version_id = x_org_version_id
3575 START WITH se.organization_id_parent = v_start_org_id
3576 AND org_structure_version_id = x_org_version_id
3577 UNION
3578 SELECT v_start_org_id FROM Sys.dual
3579 where EXISTS (select 'X' /* Made changes for BUG 1180635*/
3580 from hr_organization_information info
3581 where info.org_information1 = 'PA_EXPENDITURE_ORG'
3582 and info.org_information_context||'' = 'CLASS'
3583 and info.org_information2 = 'Y'
3584 and info.organization_id = v_start_org_id);
3585
3586
3587 Begin
3588
3589
3590 for imp_rec in ( select org_id,proj_start_org_id
3591 from pa_implementations_all imp
3592 where proj_org_structure_version_id = x_org_version_id
3593 )
3594
3595 loop
3596 v_start_org_id := imp_rec.proj_start_org_id;
3597 v_org_id := imp_rec.org_id;
3598
3599 UPDATE pa_all_organizations
3600 SET Inactive_Date = TRUNC(SYSDATE)
3601 WHERE Pa_Org_Use_Type = 'PROJECTS'
3602 AND NVL(Org_id, -99) = NVL(v_org_id, -99);
3603
3604 FOR rec_proj_new_org IN cur_proj_new_org LOOP
3605
3606 /* Update all Organizations (Projects)
3607 in the hierarchy with inactive date as NULL.
3608 Union is to include the start organization id in the update */
3609
3610 UPDATE pa_all_organizations
3611 SET Inactive_Date = NULL
3612 WHERE Pa_Org_Use_Type = 'PROJECTS'
3613 AND NVL(Org_id, -99) = NVL(v_org_id, -99)
3614 AND Organization_id = rec_proj_new_org.organization_id;
3615
3616 /* In case there are no corresponding records for the
3617 Organization hierarchy in the Pa_all_Organizations, then,
3618 create these records in the Pa_All_Organizations
3619 with Inactive_Date as NULL */
3620
3621 IF SQL%NOTFOUND THEN
3622 INSERT INTO Pa_All_Organizations
3623 (organization_id,
3624 org_id,
3625 pa_org_use_type,
3626 inactive_date)
3627 VALUES
3628 (rec_proj_new_org.organization_id,
3629 v_org_id,
3630 'PROJECTS',
3631 NULL);
3632 END IF;
3633 END Loop;
3634
3635 END LOOP;
3636
3637 for imp_rec in ( select org_id, exp_start_org_id
3638 from pa_implementations_all imp
3639 where exp_org_structure_version_id = x_org_version_id
3640 )
3641
3642 loop
3643 v_start_org_id := imp_rec.exp_start_org_id;
3644 v_org_id := imp_rec.org_id;
3645
3646 UPDATE pa_all_organizations
3647 SET Inactive_Date = TRUNC(SYSDATE)
3648 WHERE Pa_Org_Use_Type = 'EXPENDITURES'
3649 AND NVL(Org_id, -99) = NVL(v_org_id, -99);
3650
3651 FOR rec_exp_new_org IN cur_exp_new_org LOOP
3652
3653 /* Update all Organizations (Projects)
3654 in the hierarchy with inactive date as NULL.
3655 Union is to include the start organization id in the update */
3656
3657 UPDATE pa_all_organizations
3658 SET Inactive_Date = NULL
3659 WHERE Pa_Org_Use_Type = 'EXPENDITURES'
3660 AND NVL(Org_id, -99) = NVL(v_org_id, -99)
3661 AND Organization_id = rec_exp_new_org.organization_id;
3662
3663 /* In case there are no corresponding records for the new
3664 Organization hierarchy in the Pa_all_Organizations, then,
3665 create these records in the Pa_All_Organizations
3666 with Inactive_Date as NULL */
3667
3668 IF SQL%NOTFOUND THEN
3669 INSERT INTO Pa_All_Organizations
3670 (organization_id,
3671 org_id,
3672 pa_org_use_type,
3673 inactive_date)
3674 VALUES
3675 (rec_exp_new_org.organization_id,
3676 v_org_id,
3677 'EXPENDITURES',
3678 NULL);
3679 END IF;
3680 END Loop;
3681
3682 END LOOP;
3683
3684 end maintain_pa_all_org;
3685
3686 END;
[Home] [Help]