1 package body HrBisOrgParams as
2 /* $Header: hrbistab.pkb 115.11 2004/06/16 06:07:00 prasharm ship $ */
3 --
4 -- Declare Package Globals
5 --
6 g_commit_count NUMBER;
7 --
8 --------------------------------------------------------------------------
9 --
10 -- This procedure does a commit every 100 times it is called;
11 --
12 --------------------------------------------------------------------------
13 --
14 PROCEDURE CommitCount IS
15 BEGIN
16 if MOD(g_commit_count,100) = 0 THEN
17
18 /* bug 1723733 HRI_ORG_PARAM_UK1 was being violated */
19 /* added exception handler to write exception to conc. log file */
20 begin
21 COMMIT;
22 exception
23 when others then
24 fnd_file.put_line(fnd_file.log,
25 'HrBisOrgParams - Failed on Commit statement in CommitCount procedure: ');
26 fnd_file.put_line(fnd_file.log,sqlerrm||' '||sqlcode);
27 end;
28
29
30 END IF;
31 g_commit_count := g_commit_count + 1;
32 END;
33 --
34 --------------------------------------------------------------------------
35 --
36 -- This function returns the next value on the sequence hri_org_param_s
37 --
38 --------------------------------------------------------------------------
39 --
40 function GetNextOrgParamID
41 return Number is
42 cursor c_next is
43 select hri_org_params_s.nextval
44 from dual;
45
46 l_org_param_id hri_org_params.org_param_id%TYPE;
47
48 begin
49 open c_next;
50 fetch c_next into l_org_param_id;
51 close c_next;
52
53 return l_org_param_id;
54 end GetNextOrgParamID;
55 --
56 --------------------------------------------------------------------------
57 --
58 -- The GetOrgStructureID returns an organization_structure_id for a
59 -- given org_structure_version_id
60 --
61 --------------------------------------------------------------------------
62 --
63 function GetOrgStructureID
64 ( p_org_structure_version_id IN Number )
65 return Number is
66 cursor c_ost
67 ( cp_osv_id Number )
68 is
69 select osv.organization_structure_id
70 from per_org_structure_versions osv
71 where osv.org_structure_version_id = cp_osv_id
72 and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate);
73
74 l_ost_id per_organization_structures.organization_structure_id%TYPE;
75
76 begin
77 open c_ost (p_org_structure_version_id);
78 fetch c_ost into l_ost_id;
79 close c_ost;
80
81 return l_ost_id;
82
83 end GetOrgStructureID;
84 --
85 --------------------------------------------------------------------------
86 --
87 -- The function GetOrgStructureVersionID returns an
88 -- organization_structure_version_id for a given org_structure_id.
89 --
90 --------------------------------------------------------------------------
91 --
92 function GetOrgStructureVersionID
93 ( p_organization_structure_id IN Number )
94 return Number is
95 cursor c_osv
96 ( cp_ost_id Number )
97 is
98 select osv.org_structure_version_id
99 from per_organization_structures ost
100 , per_org_structure_versions osv
101 where ost.organization_structure_id = cp_ost_id
102 and ost.organization_structure_id = osv.organization_structure_id
103 and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate);
104
105 l_osv_id per_org_structure_versions.org_structure_version_id%TYPE;
106
107 begin
108 open c_osv (p_organization_structure_id);
109 fetch c_osv into l_osv_id;
110 close c_osv;
111
112 return l_osv_id;
113
114 end GetOrgStructureVersionID;
115
116 ----------------------------------------------------------------------------------------------
117
118 function GetOrgParamID
119 return Number is
120
121 l_ost_id Number;
122 l_org_param_id hri_org_params.org_param_id%TYPE := null;
123
124 begin
125
126 -- Added by S.Bhattal, version 115.4, 01-OCT-1999
127 l_ost_id := HrFastAnswers.GetReportingHierarchy;
128
129 l_org_param_id := GetOrgParamID
130 ( p_organization_structure_id => l_ost_id
131 , p_organization_id => -1
132 , p_organization_process => 'ISNR' );
133
134 return (l_org_param_id);
135
136 end GetOrgParamID;
137
138 ----------------------------------------------------------------------------------------------
139
140 function GetOrgParamID
141 ( p_organization_structure_id IN Number
142 , p_organization_id IN Number
143 , p_organization_process IN Varchar2 )
144 return Number is
145
146 cursor c_toporg
147 ( cp_ost_id Number )
148 is
149 select ose.organization_id_parent
150 from per_organization_structures ost
151 , per_org_structure_versions osv
152 , per_org_structure_elements ose
153 where ost.organization_structure_id = cp_ost_id
154 and ost.organization_structure_id = osv.organization_structure_id
155 and osv.org_structure_version_id = ose.org_structure_version_id
156 and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
157 and not exists
158 ( select null
159 from per_org_structure_elements ose2
160 where ose2.org_structure_version_id = osv.org_structure_version_id
161 and ose.organization_id_parent = ose2.organization_id_child );
162
163 cursor c_prm
164 ( cp_ost_id Number
165 , cp_org_id Number
166 , cp_orgprc Varchar2 )
167 is
168 select bop.org_param_id
169 from hri_org_params bop
170 where bop.organization_structure_id = cp_ost_id
171 and bop.organization_id = cp_org_id
172 and bop.organization_process = cp_orgprc;
173
174 l_ost_id Number := p_organization_structure_id;
175 l_org_id Number := p_organization_id;
176 l_orgprc Varchar2(4) := p_organization_process;
177
178 l_org_param_id hri_org_params.org_param_id%TYPE := null;
179
180 begin
181 if (l_org_id = -1)
182 then
183 open c_toporg (l_ost_id);
184 fetch c_toporg into l_org_id;
185 close c_toporg;
186 l_orgprc := 'IS'||substr(l_orgprc,3);
187 end if;
188
189 open c_prm (l_ost_id, l_org_id, l_orgprc);
190 fetch c_prm into l_org_param_id;
191 close c_prm;
192
193 return l_org_param_id;
194
195 end GetOrgParamID;
196
197 ----------------------------------------------------------------------------------------------
198 procedure LoadOrgHierarchy
199 ( p_organization_structure_id IN Number
200 , p_organization_id IN Number
201 , p_organization_process IN Varchar2 )
202 is
203 ----------------------------------------------------------------------
204 -- The following organization structure is used to illustarte examples
205 -- at various points in this procedure:
206 --
207 -- A
208 -- / \
209 -- B C
210 -- / \
211 -- D E
212 -- / \
213 -- F G
214 -- / \
215 -- H I
216 --
217 ----------------------------------------------------------------------
218 -- For a given organization_structure_version_id,
219 -- organization_id this cursor will
220 -- return different rows depending on the process(ISNR,ISRO,SINR,SIRO).
221 --
222 -- Examples:
223 -- ---------
224 -- 1- For a given organization_structure_version_id pointing to the
225 -- above org structure with an organization_id of E and process ISNR
226 -- the following organization_id_start values
227 -- will be returned by this cursor: E,F,G,H,I
228 --
229 -- 2- For a given organization_structure_version_id pointing to the
230 -- above org structure with an organization_id of E and process ISRO
231 -- the following organization_id_start values
232 -- will be returned by this cursor: E,F,G,H,I
233 --
234 -- 3- For a given organization_structure_version_id pointing to the
235 -- above org structure with an organization_id of E and process SINR
236 -- the following organization_id_start values
237 -- will be returned by this cursor: E
238 --
239 -- 4- For a given organization_structure_version_id pointing to the
240 -- above org structure with an organization_id of E and process SIRO
241 -- the following organization_id_start values
242 -- will be returned by this cursor: E
243 --
244 -----------------------------------------------------------------------
245 --
246 cursor c_main
247 ( cp_org_structure_version_id Number
248 , cp_organization_id Number
249 , cp_organization_process Varchar2 )
250 is
251 select TREE.organization_id_start
252 from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
253 , (select ele.organization_id_parent organization_id_start
254 from per_org_structure_elements ele
255 where ele.org_structure_version_id = cp_org_structure_version_id
256 and cp_organization_process in ('ISNR', 'ISRO')
257 connect by prior ele.organization_id_child = ele.organization_id_parent
258 and ele.org_structure_version_id = cp_org_structure_version_id
259 start with ele.organization_id_parent = cp_organization_id
260 and ele.org_structure_version_id = cp_org_structure_version_id) TREE
261 where TREE.organization_id_start = org.organization_id
262 UNION
263 select TREE.organization_id_start
264 from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
265 , (select ele.organization_id_child organization_id_start
266 from per_org_structure_elements ele
267 where ele.org_structure_version_id = cp_org_structure_version_id
268 and cp_organization_process in ('ISNR', 'ISRO')
269 connect by prior ele.organization_id_child = ele.organization_id_parent
270 and ele.org_structure_version_id = cp_org_structure_version_id
271 start with ele.organization_id_parent = cp_organization_id
272 and ele.org_structure_version_id = cp_org_structure_version_id) TREE
273 where TREE.organization_id_start = org.organization_id
274 UNION
275 select org.organization_id organization_id_start
276 from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
277 where org.organization_id = cp_organization_id
278 order by 1;
279 -----------------------------------------------------------------------
280 --
281 -- This cursor will for a given organization_structure_version_id, and
282 -- organization_id_start this cursor will return different rows depending on the process_id
283 --
284 -- Examples:
285 -- ---------
286 -- 1- For a given organization_structure_version_id pointing to the above
287 -- org structure with an organization_id_start of E and process ISNR the
288 -- following organization_id_start values
289 -- will be returned by this cursor: E
290 --
291 -- 2- For a given organization_structure_version_id pointing to the above
292 -- org structure with an organization_id_start of E and process ISRO the
293 -- following organization_id_start values
294 -- will be returned by this cursor: E,F,G,H,I
295 --
296 -- 3- For a given organization_structure_version_id pointing to the above
297 -- org structure with an organization_id_start of E and process SINR the
298 -- following organization_id_start values
299 -- will be returned by this cursor: E
300 --
301 -- 4- For a given organization_structure_version_id pointing to the above
302 -- org structure
303 -- with an organization_id_start of E and process SIRO the following
304 -- organization_id_start values
305 -- will be returned by this cursor: E,F,G,H,I
306 --
307 -----------------------------------------------------------------------
308 --
309 cursor c_child
310 ( cp_org_structure_version_id Number
311 , cp_organization_id_start Number
312 , cp_organization_process Varchar2 )
313 is
314 select TREE.organization_id_group
315 , TREE.organization_id_child
316 from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
317 , (select cp_organization_id_start organization_id_group
318 , ele.organization_id_child organization_id_child
319 from per_org_structure_elements ele
320 where ele.org_structure_version_id = cp_org_structure_version_id
321 and cp_organization_process in ('SIRO', 'ISRO')
322 connect by prior ele.organization_id_child = ele.organization_id_parent
323 -- JTitmas Bug# 1296567 Altered line below to keep query in same hierarchy
324 and prior ele.org_structure_version_id = ele.org_structure_version_id
325 start with ele.organization_id_parent = cp_organization_id_start
326 and ele.org_structure_version_id = cp_org_structure_version_id) TREE
327 where TREE.organization_id_child = org.organization_id
328 UNION
329 select org.organization_id organization_id_group
330 , org.organization_id organization_id_child
331 from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
332 where org.organization_id = cp_organization_id_start
333 order by 1,2;
334 --
335 l_osv_id Number := GetOrgStructureVersionID (p_organization_structure_id);
336 l_org_id Number := p_organization_id;
337 l_orgprc Varchar2(4) := p_organization_process;
338 --
339 l_org_param_id hri_org_params.org_param_id%TYPE;
340 --
341 begin
342 --
343 --------------------------------------------------------------------
344 --
345 -- This call to GetNextOrgParamID gets the next org param id from the
346 -- sequence hri_org_params_s to be used as a primary key for the master
347 -- table hri_org_params and part of primary key for the detail table
348 -- hri_org_param_list
349 --
350 --------------------------------------------------------------------
351 --
352 l_org_param_id := GetNextOrgParamID;
353 --
354 --------------------------------------------------------------------
355 --
356 -- Create the master record in hri_org_params
357 --
358 --------------------------------------------------------------------
359 --
360
361 /* bug 1723733 HRI_ORG_PARAM_UK1 was being violated */
362 /* added exception handler to write exception to conc. log file */
363
364 begin
365
366 insert into hri_org_params
367 ( org_param_id
368 , organization_structure_id
369 , organization_id
370 , organization_process )
371 values
372 ( l_org_param_id
373 , p_organization_structure_id
374 , p_organization_id
375 , p_organization_process );
376
377 exception
378 when others then
379 fnd_file.put_line(fnd_file.log,
380 '----------------------------------------------------------- ');
381 fnd_file.put_line(fnd_file.log,
382 'HrBisOrgParams - Failed on insert into hri_org_params table: ');
383 fnd_file.put_line(fnd_file.log,sqlerrm||' '||sqlcode);
384 fnd_file.put_line(fnd_file.log,'org_param_id = '|| l_org_param_id);
385 fnd_file.put_line(fnd_file.log,'organization_structure_id = '
386 || p_organization_structure_id);
387 fnd_file.put_line(fnd_file.log,'organization_id = '
388 || p_organization_id);
389 fnd_file.put_line(fnd_file.log,'organization_process = '
390 || p_organization_process);
391 fnd_file.put_line(fnd_file.log,
392 '----------------------------------------------------------- ');
393 end;
394
395 --
396 CommitCount;
397
398 --
399 ----------------------------------------------------------------------------
400 --
401 -- As described above the values returned by the following cursor (c_main)
402 -- depend on the process (ISNR,ISRO,SINR,SIRO). For a given
403 -- org_structure_version_id and an organization_id of E based on the
404 -- example above the values returned by the cursor will be:
405 -- EFGHI, EFGHI, E, E respectively
406 -- (depending on whether ISNR,ISRO,SINR,SIRO).
407 --
408 ----------------------------------------------------------------------------
409 --
410 for r_main in c_main
411 ( l_osv_id, l_org_id, l_orgprc )
412 loop
413 --
414 --------------------------------------------------------------------------
415 --
416 -- As described above the values returned by the following cursor
417 -- (c_child) depend on the process (ISNR,ISRO,SINR,SIRO). For a given
418 -- org_structure_version_id and an organization_id_start
419 -- of E based on the example above the organization_id_group returned by
420 -- the cursor will be E and
421 -- the organization_id_child will be:
422 -- E, EFGHI, E, EFGHI respectively
423 -- (depending on whether ISNR,ISRO,SINR,SIRO).
424 --
425 --------------------------------------------------------------------------
426 --
427 for r_child in c_child
428 ( l_osv_id, r_main.organization_id_start, l_orgprc )
429 loop
430 --
431 ------------------------------------------------------------------------
432 --
433 -- Insert the organization_id_group and organization_id_child into the
434 -- hri_org_Param_list table.
435 --
436 ------------------------------------------------------------------------
437 --
438
439 /* bug 1723733 */
440 /* added exception handler to write exception to conc. log file */
441
442 begin
443 insert into hri_org_param_list
444 ( org_param_id
445 , organization_id_group
446 , organization_id_child )
447 values
448 ( l_org_param_id
449 , r_child.organization_id_group
450 , r_child.organization_id_child );
451 exception
452 when others then
453 fnd_file.put_line(fnd_file.log,
454 '----------------------------------------------------------- ');
455 fnd_file.put_line(fnd_file.log,
456 'HrBisOrgParams - Failed on insert into hri_org_param_list table: ');
457 fnd_file.put_line(fnd_file.log,sqlerrm||' '||sqlcode);
458 fnd_file.put_line(fnd_file.log,'org_param_id = ' || l_org_param_id);
459 fnd_file.put_line(fnd_file.log,'organization_id_group = ' ||
460 r_child.organization_id_group);
461 fnd_file.put_line(fnd_file.log,'organization_id_child = ' ||
462 r_child.organization_id_child);
463 fnd_file.put_line(fnd_file.log,
464 '----------------------------------------------------------- ');
465
466 end;
467
468 --
469 CommitCount;
470
471 end loop;
472 end loop;
473
474 end LoadOrgHierarchy;
475 --
476 --------------------------------------------------------------------------
477 --
478 -- This procedure is the main entry point to this package. This procedure
479 -- will need to be called by the concurrent manager on a regular basis or
480 -- every time the organisation hierarchy is changed. The purpose of this
481 -- procedure is to populate the hri_org_params and hri_org_param_list tables
482 -- which are used in many HRMS BIS reports to select organisation
483 -- hierarchies.
484 --
485 --------------------------------------------------------------------------
486 --
487 procedure LoadAllHierarchies
488 is
489 --
490 -- The following cursor selects all Parent organisations
491 -- (i.e. organisations at the top of
492 -- the tree for each organisation structure version)
493 --
494 cursor c_hierarchies is
495 select distinct
496 ost.organization_structure_id ost_id
497 , ose.organization_id_parent top_org_id
498 , ose.org_structure_version_id ver_id
499 from per_organization_structures ost
500 , per_org_structure_versions osv
501 , per_org_structure_elements ose
502 where ost.organization_structure_id = osv.organization_structure_id
503 and osv.org_structure_version_id = ose.org_structure_version_id
504 and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
505 and not exists
506 ( select null
507 from per_org_structure_elements ose2
508 where ose2.org_structure_version_id = osv.org_structure_version_id
509 and ose.organization_id_parent = ose2.organization_id_child );
510 --
511 -- This cursor has org_structure_version_id passed into cp_ost_id and a
512 -- top org (decribed in comment above) passed into cp_org_id This cursor
513 -- returns the top level parent id passed in along with all child records
514 -- for the parent/org_structure_version_id
515 --
516 cursor c_elements
517 ( cp_ost_id Number
518 , cp_org_id Number )
519 is
520 select organization_id_child organization_id
521 from per_org_structure_elements
522 where org_structure_version_id = cp_ost_id
523 UNION
524 select organization_id
525 from hr_all_organization_units -- S.Bhattal, 18-AUG-99, 115.2 -- cbridge 05-DEC-2000 115.6
526 where organization_id = cp_org_id;
527 --
528 --
529 l_sql_stmt VARCHAR2(2000);
530 l_dummy1 VARCHAR2(2000);
531 l_dummy2 VARCHAR2(2000);
532 l_schema VARCHAR2(400);
533 --
534 begin
535 --
536 -- Empty Org Hierarchy tables before re-creating data in them.
537 --
538 -- Bug 3658446 used truncate in place of delete
539 --
540 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
541 --
542 -- delete from hri_org_param_list;
543 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_ORG_PARAM_LIST';
544 EXECUTE IMMEDIATE(l_sql_stmt);
545 --
546 -- delete from hri_org_params;
547 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_ORG_PARAMS';
548 EXECUTE IMMEDIATE(l_sql_stmt);
549 --
550 END IF;
551 --
552 -- Set the commit count to 0 so that a commit will be done for every 100 inserts
553 --
554 g_commit_count := 0;
555 --
556 -- This cursor (described above) will return 1 row for each top level
557 -- organization for each org structure verion id
558 --
559 for r_hierarchies in c_hierarchies
560 loop
561 --
562 --
563 -- The c_elements cursor (described above) returns a row for the top
564 -- level organization identified in the c_hierarchies cursor and all
565 -- it's child organizations for the given org_structure_version_id
566 --
567 for r_elements in c_elements
568 (r_hierarchies.ver_id, r_hierarchies.top_org_id)
569 loop
570 --
571 -- The following calls to LoadOrgHierarchy are to populate the tables:
572 -- hri_org_param_list
573 -- hri_org_params
574 -- For each row returned by the c_elements cursor a call is made to the
575 -- LoadOrgHierarchy procedure for each different type of organization
576 -- process (SINR, ISNR, SIRO, ISRO).
577 -- These process types are described below.
578 --
579 -- The following examples are based on the Org structure defined below:
580 --
581 -- A
582 -- / \
583 -- B C
584 -- / \
585 -- D E
586 -- / \
587 -- F G
588 -- / \
589 -- H I
590 --
591 --
592 --
593 -- SINR (Single Instance No Rollup)
594 -- --------------------------------
595 -- SINR for organization A is basically just A on it's own with none of
596 -- A's sub organizations rolled into it.
597 -- This should result in something like the following records being
598 -- inserted into our org tables (N.B. These are not supposed to be
599 -- exact records as I am using letters in place of numbers):
600 --
601 -- hri_org_params:
602 -- +-------------+-------------------------+---------------+---------+
603 -- |ORG_PARAM_ID |ORGANIZATION_STRUCTURE_ID|ORGANIZATION_ID| PROCESS |
604 -- +-------------+-------------------------+---------------+---------+
605 -- |33 (for e.g.)|3(for e.g.) | A | SINR |
606 -- +-------------+-------------------------+---------------+---------+
607 --
608 -- hri_org_param_list:
609 -- +-------------+---------------------+---------------------+
610 -- |ORG_PARAM_ID |ORGANIZATION_ID_GROUP|ORGANIZATION_ID_CHILD|
611 -- +-------------+---------------------+---------------------+
612 -- | 33 | A | A |
613 -- +-------------+---------------------+---------------------+
614 --
615 --
616 LoadOrgHierarchy
617 ( r_hierarchies.ost_id
618 , r_elements.organization_id
619 , 'SINR');
620 --
621 --
622 -- ISNR (Include Subordinates No Rollup)
623 -- -------------------------------------
624 -- ISNR for organization A is A, and all of it's sub organizations
625 -- B,C,D,E,F,G,H,I
626 -- reported seperately.
627 -- This should result in something like the following records being
628 -- inserted into our org tables (N.B. These are not supposed to be exact
629 -- records as I am using letters in place of numbers):
630 --
631 -- hri_org_params:
632 -- +-------------+-------------------------+---------------+---------+
633 -- |ORG_PARAM_ID |ORGANIZATION_STRUCTURE_ID|ORGANIZATION_ID| PROCESS |
634 -- +-------------+-------------------------+---------------+---------+
635 -- |34 (for e.g.)|3(for e.g.) | A | ISNR |
636 -- +-------------+-------------------------+---------------+---------+
637 --
638 -- hri_org_param_list:
639 -- +-------------+---------------------+---------------------+
640 -- |ORG_PARAM_ID |ORGANIZATION_ID_GROUP|ORGANIZATION_ID_CHILD|
641 -- +-------------+---------------------+---------------------+
642 -- | 34 | A | A |
643 -- | 34 | B | B |
644 -- | 34 | C | C |
645 -- | 34 | D | D |
646 -- | 34 | E | E |
647 -- | 34 | F | F |
648 -- | 34 | G | G |
649 -- | 34 | H | H |
650 -- | 34 | I | I |
651 -- +-------------+---------------------+---------------------+
652 --
653 --
654 LoadOrgHierarchy
655 ( r_hierarchies.ost_id
656 , r_elements.organization_id
657 , 'ISNR');
658 --
659 --
660 -- SIRO (Single Instance Rollup)
661 -- -------------------------------------
662 -- SIRO for organization A is A, and all of it's sub organizations
663 -- B,C,D,E,F,G,H,I
664 -- figures included in any calculations for A.
665 --
666 -- This should result in something like the following records being
667 -- inserted into our org tables (N.B. These are not supposed to be exact
668 -- records as I am using letters in place of numbers):
669 --
670 -- hri_org_params:
671 -- +-------------+-------------------------+---------------+---------+
672 -- |ORG_PARAM_ID |ORGANIZATION_STRUCTURE_ID|ORGANIZATION_ID| PROCESS |
673 -- +-------------+-------------------------+---------------+---------+
674 -- |35 (for e.g.)|3(for e.g.) | A | ISNR |
675 -- +-------------+-------------------------+---------------+---------+
676 --
677 -- hri_org_param_list:
678 -- +-------------+---------------------+---------------------+
679 -- |ORG_PARAM_ID |ORGANIZATION_ID_GROUP|ORGANIZATION_ID_CHILD|
680 -- +-------------+---------------------+---------------------+
681 -- | 35 | A | A |
682 -- | 35 | A | B |
683 -- | 35 | A | C |
684 -- | 35 | A | D |
685 -- | 35 | A | E |
686 -- | 35 | A | F |
687 -- | 35 | A | G |
688 -- | 35 | A | H |
689 -- | 35 | A | I |
690 -- +-------------+---------------------+---------------------+
691 --
692 --
693 LoadOrgHierarchy
694 ( r_hierarchies.ost_id
695 , r_elements.organization_id
696 , 'SIRO');
697 --
698 --
699 -- ISRO (Include Subordinates Rollup)
700 -- -------------------------------------
701 -- ISRO for organization A is A, and all of it's sub organizations
702 -- B,C,D,E,F,G,H,I
703 -- figures included in any calculations for A, B and all of it's sub
704 -- organizations D,E,F,G,H,I included in any of the calculations for B,
705 -- C and all of it's sub
706 -- organizations (of which it has none) included in the calculation
707 -- for C and so on.
708 --
709 -- This should result in something like the following records being
710 -- inserted into our org tables (N.B. These are not supposed to be exact
711 -- records as I am using letters in place of numbers):
712 --
713 -- hri_org_params:
714 -- +-------------+-------------------------+---------------+---------+
715 -- |ORG_PARAM_ID |ORGANIZATION_STRUCTURE_ID|ORGANIZATION_ID| PROCESS |
716 -- +-------------+-------------------------+---------------+---------+
717 -- |35 (for e.g.)|3(for e.g.) | A | ISNR |
718 -- +-------------+-------------------------+---------------+---------+
719 --
720 -- hri_org_param_list:
721 -- +-------------+---------------------+---------------------+
722 -- |ORG_PARAM_ID |ORGANIZATION_ID_GROUP|ORGANIZATION_ID_CHILD|
723 -- +-------------+---------------------+---------------------+
724 -- | 35 | A | A |
725 -- | 35 | A | B |
726 -- | 35 | A | C |
727 -- | 35 | A | D |
728 -- | 35 | A | E |
729 -- | 35 | A | F |
730 -- | 35 | A | G |
731 -- | 35 | A | H |
732 -- | 35 | A | I |
733 -- | 35 | B | B |
734 -- | 35 | B | D |
735 -- | 35 | B | E |
736 -- | 35 | B | F |
737 -- | 35 | B | G |
738 -- | 35 | B | H |
739 -- | 35 | B | I |
740 -- | 35 | C | C |
741 -- | 35 | D | D |
742 -- | 35 | E | E |
743 -- | 35 | E | F |
744 -- | 35 | E | G |
745 -- | 35 | E | H |
746 -- | 35 | E | I |
747 -- | 35 | F | F |
748 -- | 35 | F | H |
749 -- | 35 | F | I |
750 -- | 35 | G | G |
751 -- +-------------+---------------------+---------------------+
752 --
753 --
754 LoadOrgHierarchy
755 ( r_hierarchies.ost_id
756 , r_elements.organization_id
757 , 'ISRO');
758 end loop;
759
760 end loop;
761
762 commit;
763
764 end LoadAllHierarchies;
765
766 --
767 -- Overloaded version of LoadAllHierarchies
768 -- The purpose of this version is to be called from
769 -- the Concurrent Manager.
770 --
771 procedure LoadAllHierarchies
772 ( errbuf OUT NOCOPY Varchar2
773 , retcode OUT NOCOPY Number )
774 is
775 begin
776 errbuf := null;
777 retcode := null;
778 --
779 LoadAllHierarchies;
780 --
781 exception
782 when Others then
783 --
784 errbuf := sqlerrm;
785 retcode := sqlcode;
786 --
787 delete from hri_org_param_list;
788 --
789 delete from hri_org_params;
790 --
791 fnd_file.put_line(fnd_file.log,sqlerrm||' '||sqlcode);
792 --
793 end LoadAllHierarchies;
794
795 ----------------------------------------------------------------------------------------------
796 function OrgInHierarchy
797 ( p_org_param_id IN Number
798 , p_organization_id_group IN Number
799 , p_organization_id_child IN Number )
800 return Number is
801 cursor c_opl
802 ( cp_org_param_id Number
803 , cp_organization_id_group Number
804 , cp_organization_id_child Number )
805 is
806 select 1
807 from hri_org_param_list opl
808 where opl.org_param_id = cp_org_param_id
809 and opl.organization_id_group = cp_organization_id_group
810 and opl.organization_id_child = cp_organization_id_child;
811
812 l_found Number;
813 l_organization_id_group Number := p_organization_id_group;
814
815 begin
816 if p_organization_id_group = -1 THEN
817 l_organization_id_group := p_organization_id_child;
818 end if;
819 --
820 open c_opl
821 ( p_org_param_id
822 , l_organization_id_group
823 , p_organization_id_child );
824 fetch c_opl into l_found;
825
826 if (c_opl%notfound) then
827 l_found := 0;
828 end if;
829
830 close c_opl;
831
832 return (l_found);
833
834 end OrgInHierarchy;
835
836 ----------------------------------------------------------------------------------------------
837 end HrBisOrgParams;
[Home] [Help]