1 PACKAGE BODY cn_comp_grp_hier_pub AS
2 --$Header: cnpcghrb.pls 115.7 2002/05/20 13:01:28 pkm ship $
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_COMP_GRP_HIER_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpcghrb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11 --
12 PROCEDURE get_comp_group_hier(
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
16 p_salesrep_id IN NUMBER ,
17 p_comp_group_id IN NUMBER,
18 p_focus_cg_id IN NUMBER,
19 p_expand IN CHAR,
20 p_date IN DATE,
21 x_mgr_tbl OUT comp_group_tbl,
22 l_mgr_count OUT NUMBER,
23 x_period_year OUT VARCHAR2,
24 x_return_status OUT VARCHAR2,
25 x_msg_count OUT NUMBER,
26 x_msg_data OUT VARCHAR2,
27 x_loading_status OUT VARCHAR2) IS
28
29 l_api_name CONSTANT VARCHAR2(30) := 'get_comp_group_hier';
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_comp_group_name VARCHAR2(100);
32 l_comp_group_id NUMBER;
33 l_counter NUMBER;
34 l_grp_count NUMBER := 0;
35 l_parent_cg_id NUMBER;
36 l_child_cg_id NUMBER;
37 l_top_hier_tbl cn_comp_grp_hier_pub.comp_group_tbl;
38 l_top_hier CHAR(1);
39 l_out_counter NUMBER;
40 l_image_start CHAR(1);
41
42 -- For getting the sibling records for the first cursor.
43 CURSOR sibling_grp_cur(
44 l_parent_cg_id IN NUMBER,
45 l_date IN DATE) IS
46 SELECT hier.comp_group_id group_id, cg.name group_name,
47 hier.start_date_active,hier.end_date_active
48 FROM cn_comp_group_hier hier,
49 cn_comp_groups cg
50 WHERE hier.parent_comp_group_id = l_parent_cg_id
51 AND hier.comp_group_id = cg.comp_group_id
52 AND hier.comp_group_id <> DECODE(p_focus_cg_id,'0',p_comp_group_id,p_focus_cg_id)
53 AND delete_flag <> 'Y'
54 AND hier.start_date_active <= l_date
55 AND ((hier.end_date_active is null) OR
56 (hier.end_date_active >= l_date));
57
58 -- For getting the child records for the first cursor.
59 CURSOR child_grp_cur(
60 l_parent_cg_id IN NUMBER,
61 l_date IN DATE) IS
62 SELECT hier.comp_group_id group_id, cg.name group_name,
63 hier.start_date_active,hier.end_date_active
64 FROM cn_comp_group_hier hier,
65 cn_comp_groups cg
66 WHERE hier.parent_comp_group_id = l_parent_cg_id
67 AND hier.comp_group_id = cg.comp_group_id
68 AND delete_flag <> 'Y'
69 AND hier.start_date_active <= l_date
70 AND ((hier.end_date_active is null) OR
71 (hier.end_date_active >= l_date));
72
73 -- To get the manager/salesrep names based on the comp group id
74 -- from the main query
75 CURSOR mgr_cur(
76 l_comp_group_id IN NUMBER,
77 l_date IN DATE) IS
78 SELECT cs.name mgr_name,
79 cs.employee_number mgr_number,
80 cs.salesrep_id salesrep_id,
81 cscg.role_name mgr_role,
82 cscg.start_date_active mgr_start_date,
83 cscg.end_date_active mgr_end_date,
84 cscg.role_id mgr_role_id,
85 cscg.srp_role_id mgr_srp_role_id
86 FROM cn_srp_comp_groups_v cscg,
87 cn_salesreps cs
88 WHERE cscg.comp_group_id = l_comp_group_id
89 AND cscg.salesrep_id = cs.salesrep_id
90 AND cscg.start_date_active <= l_date
91 AND ((cscg.end_date_active is null) OR
92 (cscg.end_date_active >= l_date));
93
94 BEGIN
95 -- Standard call to check for call compatibility.
96 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
97 p_api_version ,
98 l_api_name,
99 G_PKG_NAME ) THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 -- Initialize message list if p_init_msg_list is set to TRUE.
104 IF FND_API.to_Boolean(p_init_msg_list ) THEN
105 FND_MSG_PUB.initialize;
106 END IF;
107
108 -- Initialize API return status to success
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 x_loading_status := 'CN_INSERTED';
111 l_mgr_count := 0;
112 l_counter := 0;
113 -- Before doing anything, check whether the comp_group_id
114 -- is available in the cn_cg_hier table
115 -- Changed by Zack at May-06-2002 to add checking for p_date is within the
116 -- effective date range of srp comp group assignment
117 SELECT count(*)
118 INTO l_counter
119 FROM cn_comp_groups cg, cn_srp_comp_groups_v cscg
120 WHERE cg.comp_group_id = p_comp_group_id
121 AND cg.start_date_active <= p_date
122 AND ((cg.end_date_active is null) OR
123 (cg.end_date_active >= p_date))
124 AND cscg.comp_group_id= cg.comp_group_id
125 and cscg.salesrep_id = p_salesrep_id
126 and cscg.start_date_active <= p_date
127 AND ((cscg.end_date_active is null) OR
128 (cscg.end_date_active >= p_date));
129
130
131 --
132 IF (l_counter <> 0) THEN
133 -- Get the period year which will be passed to YTD Summary
134 BEGIN
135 SELECT period_year
136 INTO x_period_year
137 FROM cn_repositories r, cn_period_statuses ps
138 WHERE r.period_type_id = ps.period_type_id
139 AND r.period_set_id = ps.period_set_id
140 AND period_status IN ('O','C')
141 AND p_date BETWEEN start_date AND end_date;
142 EXCEPTION
143 WHEN OTHERS THEN
144 x_period_year := TO_CHAR(SYSDATE,'RRRR');
145 END;
146 --
147 -- check whether it is called from submit button or
148 -- PLUS/MINUS symbol(drill down link)
149 IF (NVL(p_focus_cg_id,0) <> 0) THEN
150 l_comp_group_id := p_focus_cg_id;
151 ELSE
152 l_comp_group_id := p_comp_group_id;
153 END IF;
154
155 -- l_comp_group_id need to be remembered, since its value
156 -- will be changed in the following WHILE loop
157 l_child_cg_id := l_comp_group_id;
158
159 l_counter := 1;
160 l_top_hier := 'N';
161
162 -- This block will find the hierarchy from the selected
163 -- comp group to the top most.
164 BEGIN
165 WHILE l_top_hier = 'N'
166 LOOP
167 BEGIN
168 SELECT comp_group_id,
169 parent_comp_group_id,
170 start_date_active,
171 end_date_active
172 INTO l_top_hier_tbl(l_counter).cg_salesrep_id,
173 l_top_hier_tbl(l_counter).parent_comp_group_id,
174 l_top_hier_tbl(l_counter).start_date_active,
175 l_top_hier_tbl(l_counter).end_date_active
176 FROM cn_comp_group_hier cgh
177 WHERE comp_group_id = l_comp_group_id
178 AND cgh.start_date_active <= p_date
179 AND ((cgh.end_date_active is null) OR
180 (cgh.end_date_active >= p_date));
181 l_top_hier_tbl(l_counter).level := l_counter;
182 l_comp_group_id := l_top_hier_tbl(l_counter).parent_comp_group_id;
183 l_counter := l_counter + 1;
184 EXCEPTION
188 SELECT comp_group_id,
185 WHEN NO_DATA_FOUND THEN
186 l_top_hier := 'Y';
187 BEGIN
189 NULL,
190 start_date_active,
191 end_date_active
192 INTO l_top_hier_tbl(l_counter).cg_salesrep_id,
193 l_top_hier_tbl(l_counter).parent_comp_group_id,
194 l_top_hier_tbl(l_counter).start_date_active,
195 l_top_hier_tbl(l_counter).end_date_active
196 FROM cn_comp_groups cg
197 WHERE comp_group_id = l_comp_group_id
198 AND cg.start_date_active <= p_date
199 AND ((cg.end_date_active is null) OR
200 (cg.end_date_active >= p_date));
201 EXCEPTION
202 WHEN OTHERS THEN
203 EXIT;
204 END;
205 WHEN OTHERS THEN
206 EXIT;
207 END;
208 END LOOP;
209 END;
210 l_counter := l_top_hier_tbl.COUNT;
211 l_out_counter := 0;
212
213 -- To get the comp group names for the above IDs
214 FOR i IN 1..l_top_hier_tbl.COUNT
215 LOOP
216 l_out_counter := l_out_counter+1;
217 IF (l_counter = 2) THEN
218 -- This ID is required to get the siblings for the selected
219 -- comp group ID.
220 l_parent_cg_id := l_top_hier_tbl(l_counter).cg_salesrep_id;
221 END IF;
222 BEGIN
223 SELECT name
224 INTO x_mgr_tbl(l_out_counter).cg_salesrep_name
225 FROM cn_comp_groups
226 WHERE comp_group_id = l_top_hier_tbl(l_counter).cg_salesrep_id;
227 EXCEPTION
228 WHEN OTHERS THEN
229 NULL;
230 END;
231 x_mgr_tbl(l_out_counter).cg_salesrep_id := l_top_hier_tbl(l_counter).cg_salesrep_id;
232 x_mgr_tbl(l_out_counter).start_date_active := l_top_hier_tbl(l_counter).start_date_active;
233 x_mgr_tbl(l_out_counter).end_date_active := l_top_hier_tbl(l_counter).end_date_active;
234 x_mgr_tbl(l_out_counter).level := i;
235 x_mgr_tbl(l_out_counter).grp_or_name_flag := 'GROUP';
236
237 IF (l_top_hier_tbl(l_counter).cg_salesrep_id = p_comp_group_id) THEN
238 l_image_start := 'Y';
239 END IF;
240
241 -- This logic will set the PLUS/MINUS symbol based on the drilldown.
242 IF (NVL(l_image_start,'N') = 'Y') THEN
243 IF (l_top_hier_tbl(l_counter).cg_salesrep_id = l_child_cg_id) THEN
244 IF (p_expand = 'Y') THEN
245 x_mgr_tbl(l_out_counter).image := 'MINUS';
246 x_mgr_tbl(l_out_counter).expand := 'N';
247 ELSE
248 x_mgr_tbl(l_out_counter).image := 'PLUS';
249 x_mgr_tbl(l_out_counter).expand := 'Y';
250 END IF;
251 ELSE
252 x_mgr_tbl(l_out_counter).image := 'MINUS';
253 x_mgr_tbl(l_out_counter).expand := 'N';
254 END IF;
255 ELSE
256 x_mgr_tbl(l_out_counter).image := 'NONE';
257 x_mgr_tbl(l_out_counter).expand := 'N';
258 END IF;
259
260 -- For each comp group ID, get the corresponding members.
261 FOR mgr IN mgr_cur(l_top_hier_tbl(l_counter).cg_salesrep_id,p_date)
262 LOOP
263 l_out_counter := l_out_counter+1;
264 x_mgr_tbl(l_out_counter).cg_salesrep_name := mgr.mgr_name;
265 x_mgr_tbl(l_out_counter).cg_salesrep_id := mgr.salesrep_id;
266 x_mgr_tbl(l_out_counter).grp_or_name_flag := 'NAME';
267 x_mgr_tbl(l_out_counter).role_name := mgr.mgr_role;
268 x_mgr_tbl(l_out_counter).role_id := mgr.mgr_role_id;
269 x_mgr_tbl(l_out_counter).start_date_active := mgr.mgr_start_date;
270 x_mgr_tbl(l_out_counter).end_date_active := mgr.mgr_end_date;
271 END LOOP;
272 l_counter := l_counter - 1;
273
274 END LOOP;
275
276 -- Fetching children comp group IDs and the corresponding memebers
277 IF (p_expand = 'Y') THEN
278 FOR child_grp IN child_grp_cur(l_child_cg_id,p_date)
279 LOOP
280 l_out_counter := l_out_counter+1;
281 x_mgr_tbl(l_out_counter).cg_salesrep_name := child_grp.group_name;
282 x_mgr_tbl(l_out_counter).cg_salesrep_id := child_grp.group_id;
283 x_mgr_tbl(l_out_counter).grp_or_name_flag := 'GROUP';
284 x_mgr_tbl(l_out_counter).start_date_active := child_grp.start_date_active;
285 x_mgr_tbl(l_out_counter).end_date_active := child_grp.end_date_active;
286 x_mgr_tbl(l_out_counter).level := l_top_hier_tbl.COUNT + 1;
287 x_mgr_tbl(l_out_counter).image := 'PLUS';
288 x_mgr_tbl(l_out_counter).expand := 'Y';
289 --
290 FOR mgr IN mgr_cur(child_grp.group_id,p_date)
291 LOOP
292 l_out_counter := l_out_counter+1;
293 x_mgr_tbl(l_out_counter).cg_salesrep_name := mgr.mgr_name;
294 x_mgr_tbl(l_out_counter).cg_salesrep_id := mgr.salesrep_id;
295 x_mgr_tbl(l_out_counter).grp_or_name_flag := 'NAME';
296 x_mgr_tbl(l_out_counter).role_name := mgr.mgr_role;
297 x_mgr_tbl(l_out_counter).role_id := mgr.mgr_role_id;
298 x_mgr_tbl(l_out_counter).start_date_active := mgr.mgr_start_date;
299 x_mgr_tbl(l_out_counter).end_date_active := mgr.mgr_end_date;
300 END LOOP;
301 --
302 END LOOP;
303 END IF;
304 -- Fetching sibling records and their corresponding members
305 FOR sibling_grp IN sibling_grp_cur(l_parent_cg_id,p_date)
306 LOOP
307 l_out_counter := l_out_counter+1;
308 x_mgr_tbl(l_out_counter).cg_salesrep_name := sibling_grp.group_name;
309 x_mgr_tbl(l_out_counter).cg_salesrep_id := sibling_grp.group_id;
310 x_mgr_tbl(l_out_counter).grp_or_name_flag := 'GROUP';
311 x_mgr_tbl(l_out_counter).start_date_active := sibling_grp.start_date_active;
312 x_mgr_tbl(l_out_counter).end_date_active := sibling_grp.end_date_active;
313 x_mgr_tbl(l_out_counter).level := l_top_hier_tbl.COUNT;
314 x_mgr_tbl(l_out_counter).image := 'PLUS';
315 x_mgr_tbl(l_out_counter).expand := 'Y';
316 --
317 FOR mgr IN mgr_cur(sibling_grp.group_id,p_date)
318 LOOP
319 l_out_counter := l_out_counter+1;
320 x_mgr_tbl(l_out_counter).cg_salesrep_name := mgr.mgr_name;
321 x_mgr_tbl(l_out_counter).cg_salesrep_id := mgr.salesrep_id;
322 x_mgr_tbl(l_out_counter).grp_or_name_flag := 'NAME';
323 x_mgr_tbl(l_out_counter).role_name := mgr.mgr_role;
324 x_mgr_tbl(l_out_counter).role_id := mgr.mgr_role_id;
325 x_mgr_tbl(l_out_counter).start_date_active := mgr.mgr_start_date;
326 x_mgr_tbl(l_out_counter).end_date_active := mgr.mgr_end_date;
327 END LOOP;
328 --
329 END LOOP;
330 l_mgr_count := x_mgr_tbl.COUNT;
331 END IF;
332 EXCEPTION
333 WHEN FND_API.G_EXC_ERROR THEN
334 x_return_status := FND_API.G_RET_STS_ERROR ;
335 FND_MSG_PUB.Count_And_Get(
336 p_count => x_msg_count,
337 p_data => x_msg_data ,
338 p_encoded => FND_API.G_FALSE);
339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
340 x_loading_status := 'UNEXPECTED_ERR';
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
342 FND_MSG_PUB.Count_And_Get(
343 p_count => x_msg_count,
344 p_data => x_msg_data,
345 p_encoded => FND_API.G_FALSE);
346 WHEN OTHERS THEN
347 x_loading_status := 'UNEXPECTED_ERR';
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
349 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
350 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
351 END IF;
352 FND_MSG_PUB.Count_And_Get(
353 p_count => x_msg_count ,
354 p_data => x_msg_data ,
355 p_encoded => FND_API.G_FALSE);
356
357 END;
358 --
359 END cn_comp_grp_hier_pub;
[Home] [Help]