1 PACKAGE BODY hri_edw_dim_organization AS
2 /* $Header: hriedorg.pkb 120.3 2006/03/09 04:54:07 anmajumd noship $ */
3
4 PROCEDURE insert_row( p_organization_structure_id NUMBER
5 , p_org_structure_version_id NUMBER
6 , p_organization_id NUMBER
7 , p_parent_organization_id NUMBER
8 , p_organization_level NUMBER
9 , p_org_last_updated DATE) IS
10
11 l_org_business_group_id NUMBER; -- Holds business group id for organization
12
13 BEGIN
14
15 SELECT business_group_id INTO l_org_business_group_id
16 FROM hr_all_organization_units
17 WHERE organization_id = p_organization_id;
18 --
19 -- Fixed GSCC warning regarding explicit listing of columns in insert statements
20 --
21 INSERT INTO hri_primary_hrchys (
22 organization_structure_id
23 ,org_structure_version_id
24 ,organization_id
25 ,parent_organization_id
26 ,organization_level
27 ,business_group_id
28 ,org_last_updated
29 ,last_update_date
30 ,last_updated_by
31 ,last_update_login
32 ,created_by
33 ,creation_date )
34 VALUES
35 ( p_organization_structure_id
36 , p_org_structure_version_id
37 , p_organization_id
38 , p_parent_organization_id
39 , p_organization_level
40 , l_org_business_group_id
41 , NVL(p_org_last_updated,to_date('01-01-1990','DD-MM-YYYY'))
42 , to_date(null)
43 , to_number(null)
44 , to_number(null)
45 , to_number(null)
46 , to_date(null));
47
48 END insert_row;
49
50 PROCEDURE populate_primary_org_hrchy_tab IS
51
52
53 /* The variable below holds the number of levels in the EDW Organization Hierarchy */
54 /* When the number of levels in the actual hierarchy exceeds this, the base level */
55 /* organization needs to point at its closest ancestor at the bottom of the hierarchy */
56 /* This is kept in the variable following, and can be maintained because the tree */
57 /* walk is always done depth-first */
58 l_number_of_levels NUMBER := 8;
59 l_bottom_level_parent NUMBER := -1;
60
61 l_organization_parent NUMBER; -- parent of the current organization
62
63 /* Cursor retrieving the organization structure, version and top organization of */
64 /* every primary hierarchy */
65 CURSOR top_org_csr IS
66 SELECT DISTINCT
67 ost.organization_structure_id organization_structure_id
68 ,osv.org_structure_version_id org_structure_version_id
69 ,ele.organization_id_parent top_organization_id
70 ,MAX(ele.last_update_date) last_updated
71 FROM
72 per_organization_structures ost
73 ,per_org_structure_versions osv
74 ,per_org_structure_elements ele
75 WHERE
76 ost.organization_structure_id = osv.organization_structure_id
77 AND osv.org_structure_version_id = ele.org_structure_version_id
78 AND ost.primary_structure_flag = 'Y'
79 AND (osv.date_to IS NULL
80 OR SYSDATE BETWEEN osv.date_from AND osv.date_to)
81 AND NOT EXISTS (SELECT 1 FROM per_org_structure_elements dummy
82 WHERE dummy.org_structure_version_id = ele.org_structure_version_id
83 AND dummy.organization_id_child = ele.organization_id_parent)
84 GROUP BY
85 ost.organization_structure_id
86 ,osv.org_structure_version_id
87 ,ele.organization_id_parent;
88
89 /* Cursor which returns every organization in a given hierarchy along with its */
90 /* parent and level (because this is viewed from the perspective of the child */
91 /* organization, the level is incremented */
92 /* Bug 5070725, including version id within START WITH condition */
93 CURSOR walk_hierarchy_csr
94 ( cp_org_structure_version_id NUMBER
95 , cp_top_organization_id NUMBER) IS
96 SELECT
97 ele.organization_id_child child_organization_id
98 ,ele.organization_id_parent parent_organization_id
99 ,LEVEL+1 child_level
100 ,ele.last_update_date last_updated
101 FROM
102 per_org_structure_elements ele
103 WHERE
104 ele.org_structure_version_id = cp_org_structure_version_id
105 CONNECT BY (prior ele.organization_id_child = ele.organization_id_parent
106 AND prior ele.org_structure_version_id = ele.org_structure_version_id)
107 START WITH ele.organization_id_parent = cp_top_organization_id
108 AND ele.org_structure_version_id = cp_org_structure_version_id;
109
110 BEGIN
111
112 /* Clear out table */
113 DELETE FROM hri_primary_hrchys;
114
115 /* Loop through each primary hierarchy */
116 FOR hierarchy_rec IN top_org_csr LOOP
117
118 /* Insert row for top organization */
119 insert_row( hierarchy_rec.organization_structure_id
120 , hierarchy_rec.org_structure_version_id
121 , hierarchy_rec.top_organization_id
122 , NULL
123 , 1
124 , hierarchy_rec.last_updated);
125
126 /* Loop through the rest of the organizations in the hierarchy */
127 FOR organization_rec IN walk_hierarchy_csr
128 ( hierarchy_rec.org_structure_version_id
129 , hierarchy_rec.top_organization_id) LOOP
130
131 /* Check to see if the level exceeds the number of levels in the dimension */
132 IF (organization_rec.child_level = l_number_of_levels) THEN
133 /* Store the id of the child, since it will be the parent of any higher */
134 /* numbered levels */
135 l_bottom_level_parent := organization_rec.child_organization_id;
136 l_organization_parent := organization_rec.parent_organization_id;
137 ELSIF (organization_rec.child_level > l_number_of_levels) THEN
138 /* If the level is greater than number of levels in dimension, then point */
139 /* directly to stored parent (guaranteed closest ancestor by depth first */
140 /* tree walk) */
141 l_organization_parent := l_bottom_level_parent;
142 ELSE
143 /* Use the direct parent of the organization */
144 l_organization_parent := organization_rec.parent_organization_id;
145 END IF;
146
147 /* Insert row for current organization */
148 insert_row( hierarchy_rec.organization_structure_id
149 , hierarchy_rec.org_structure_version_id
150 , organization_rec.child_organization_id
151 , l_organization_parent
152 , organization_rec.child_level
153 , organization_rec.last_updated);
154
155 END LOOP; -- walk_hierarchy_csr
156
157 END LOOP; -- top_org_csr
158
159 END populate_primary_org_hrchy_tab;
160
161 END hri_edw_dim_organization;
[Home] [Help]