[Home] [Help]
PACKAGE BODY: APPS.CN_PSUM_PVT
Source
1 PACKAGE BODY CN_psum_PVT AS
2 /* $Header: cnvpsumb.pls 115.5 2002/11/21 21:15:53 hlchen ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'CN_psum_PVT';
4
5 -- Start of comments
6 -- API name : Get_psum_Data
7 -- Type : Private.
8 -- Pre-reqs : None.
9 -- Usage :
10 --
11 -- Desc :
12 --
13 --
14 --
15 -- Parameters :
16 -- IN : p_api_version NUMBER Require
17 -- p_init_msg_list VARCHAR2 Optional
18 -- Default = FND_API.G_FALSE
19 -- p_commit VARCHAR2 Optional
20 -- Default = FND_API.G_FALSE
21 -- p_validation_level NUMBER Optional
22 -- Default = FND_API.G_VALID_LEVEL_FULL
26 -- IN : p_mgr_id NUMBER
23 -- OUT : x_return_status VARCHAR2(1)
24 -- x_msg_count NUMBER
25 -- x_msg_data VARCHAR2(2000)
27 -- p_comp_group_id NUMBER
28 -- p_org_code VARCHAR2
29 -- p_period_id NUMBER
30 -- p_start_row NUMBER
31 -- p_rows NUMBER
32 -- OUT : x_psum_data psum_tbl_type
33 -- x_totarows NUMBER
34 -- Version : Current version 1.0
35 -- Initial version 1.0
36 --
37 -- Notes : Note text
38 --
39 -- End of comments
40
41
42 PROCEDURE Get_Psum_Data
43 (
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
46 p_commit IN VARCHAR2 := FND_API.G_FALSE,
47 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 p_mgr_id IN NUMBER,
52 p_comp_group_id IN NUMBER,
53 p_mgr_dtl_flag IN VARCHAR2,
54 p_effective_date IN DATE,
55 x_psum_data OUT NOCOPY psum_tbl_type,
56 x_total_rows OUT NOCOPY NUMBER
57 )
58 IS
59
60 l_api_name CONSTANT VARCHAR2(30) := 'Get_Psum_Data';
61 l_api_version CONSTANT NUMBER := 1.0;
62 l_srp_group_rec CN_SRP_HIER_PROC_PVT.SRP_GROUP_REC_TYPE;
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(2000);
66 l_srp_role_info_tbl CN_SRP_HIER_PROC_PVT.SRP_ROLE_INFO_TBL_TYPE;
67 l_returned_rows NUMBER;
68 l_mgr_name VARCHAR2(360);
69 l_srp CN_SRP_HIER_PROC_PVT.group_mbr_tbl_type;
70 l_agg_counter NUMBER := 0;
71
72 BEGIN
73 -- Standard Start of API savepoint
74 SAVEPOINT Get_Psum_Data_SP;
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.compatible_api_call
77 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
78 THEN
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END IF;
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 IF FND_API.to_Boolean( p_init_msg_list ) THEN
83 FND_MSG_PUB.initialize;
84 END IF;
85 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87 x_total_rows := 0;
88
89 -- API body
90 IF (p_mgr_dtl_flag = 'N') THEN
91
92 /* Setting the input variables */
93 l_srp_group_rec.salesrep_id := p_mgr_id;
94 l_srp_group_rec.group_id := p_comp_group_id;
95 l_srp_group_rec.effective_date := p_effective_date;
96
97
98 CN_SRP_HIER_PROC_PVT.Get_desc_role_info
99 ( p_api_version => 1.0,
100 p_init_msg_list => FND_API.G_FALSE,
101 p_commit => FND_API.G_FALSE,
102 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
103 p_srp => l_srp_group_rec,
104 p_return_current => 'N',
105 x_return_status => l_return_status,
106 x_msg_count => l_msg_count,
107 x_msg_data => l_msg_data,
108 x_srp => l_srp_role_info_tbl,
109 x_returned_rows => l_returned_rows
110 );
111
112 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
113 RAISE FND_API.G_EXC_ERROR;
114 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
115 THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END IF;
118
119
120 SELECT name
121 INTO l_mgr_name
122 FROM cn_srp_hr_data
123 WHERE srp_id = p_mgr_id
124 ;
125
126
127 IF l_srp_role_info_tbl.COUNT > 0 THEN
128 FOR lcount IN l_srp_role_info_tbl.FIRST .. l_srp_role_info_tbl.LAST LOOP
129
130 x_psum_data(lcount).mgr_id := p_mgr_id ;
131 x_psum_data(lcount).mgr_name := l_mgr_name ;
132
133 x_psum_data(lcount).srp_role_id := l_srp_role_info_tbl(lcount).srp_role_id ;
134 x_psum_data(lcount).srp_id := l_srp_role_info_tbl(lcount).srp_id ;
135 x_psum_data(lcount).overlay_flag := l_srp_role_info_tbl(lcount).overlay_flag ;
136 x_psum_data(lcount).non_std_flag := l_srp_role_info_tbl(lcount).non_std_flag ;
137 x_psum_data(lcount).role_id := l_srp_role_info_tbl(lcount).role_id ;
138 x_psum_data(lcount).role_name := l_srp_role_info_tbl(lcount).role_name ;
139 x_psum_data(lcount).job_title_id := l_srp_role_info_tbl(lcount).job_title_id ;
140 x_psum_data(lcount).job_discretion := l_srp_role_info_tbl(lcount).job_discretion ;
141 x_psum_data(lcount).status := l_srp_role_info_tbl(lcount).status ;
142 x_psum_data(lcount).plan_activate_status := l_srp_role_info_tbl(lcount).plan_activate_status;
143 x_psum_data(lcount).club_eligible_flag := l_srp_role_info_tbl(lcount).club_eligible_flag;
144 x_psum_data(lcount).org_code := l_srp_role_info_tbl(lcount).org_code ;
145 x_psum_data(lcount).start_date := l_srp_role_info_tbl(lcount).start_date ;
146 x_psum_data(lcount).end_date := l_srp_role_info_tbl(lcount).end_date ;
147 -- change made here
148 x_psum_data(lcount).group_id := p_comp_group_id ;
149
150 END LOOP;
151 END IF;
155 -- DBMS_OUTPUT.PUT_LINE('************** Entered in the Else ************' || p_mgr_id || ',' || p_comp_group_id || ',' || p_effective_date);
152 x_total_rows := x_psum_data.count;
153 ELSE
154
156 /* Getting the managers */
157 l_srp_group_rec.salesrep_id := p_mgr_id;
158 l_srp_group_rec.group_id := p_comp_group_id;
159 l_srp_group_rec.effective_date := p_effective_date;
160
161 CN_SRP_HIER_PROC_PVT.Get_Descendant_group_mbrs(
162 p_api_version => 1.0,
163 p_init_msg_list => FND_API.G_FALSE,
164 p_commit => FND_API.G_FALSE,
165 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
166 p_srp => l_srp_group_rec,
167 p_first_level_only => 'Y',
168 x_return_status => l_return_status,
169 x_msg_count => l_msg_count,
170 x_msg_data => l_msg_data,
171 x_srp => l_srp,
172 x_returned_rows => l_returned_rows
173 );
174
175 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
176 RAISE FND_API.G_EXC_ERROR;
177 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
178 THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181
182 l_agg_counter := 1;
183 -- dbms_output.put_line('Count from Get_Descendant_group_mbrs is : ' || l_srp.COUNT);
184 IF l_srp.COUNT > 0 THEN
185 FOR i in l_srp.FIRST..l_srp.LAST LOOP
186 l_srp_group_rec.salesrep_id := l_srp(i).salesrep_id;
187 l_srp_group_rec.group_id := l_srp(i).group_id;
188 l_srp_group_rec.effective_date := p_effective_date;
189
190 IF p_mgr_id <> l_srp(i).salesrep_id THEN
191 CN_SRP_HIER_PROC_PVT.Get_desc_role_info
192 (p_api_version => 1.0,
193 p_init_msg_list => FND_API.G_FALSE,
194 p_commit => FND_API.G_FALSE,
195 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
196 p_srp => l_srp_group_rec,
197 p_return_current => 'N',
198 x_return_status => l_return_status,
199 x_msg_count => l_msg_count,
200 x_msg_data => l_msg_data,
201 x_srp => l_srp_role_info_tbl,
202 x_returned_rows => l_returned_rows
203 );
204
205 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
206 RAISE FND_API.G_EXC_ERROR;
207 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
208 THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212
213 SELECT name INTO l_mgr_name FROM cn_srp_hr_data WHERE srp_id = l_srp_group_rec.salesrep_id;
214 -- DBMS_OUTPUT.PUT_LINE('************** Entered For Manager ************ ' || l_srp_group_rec.salesrep_id || ',' || l_mgr_name || ' Status is : ' || l_return_status || ' ROWS : ' || l_srp_role_info_tbl.count);
215
216 IF l_srp_role_info_tbl.COUNT > 0 THEN
217 FOR lcount IN l_srp_role_info_tbl.FIRST .. l_srp_role_info_tbl.LAST LOOP
218 -- DBMS_OUTPUT.PUT_LINE('Entered in the inner most loop ');
219 x_psum_data(l_agg_counter).mgr_id := l_srp_group_rec.salesrep_id;
220 x_psum_data(l_agg_counter).mgr_name := l_mgr_name;
221 x_psum_data(l_agg_counter).srp_role_id := l_srp_role_info_tbl(lcount).srp_role_id;
222 x_psum_data(l_agg_counter).srp_id := l_srp_role_info_tbl(lcount).srp_id;
223 x_psum_data(l_agg_counter).overlay_flag := l_srp_role_info_tbl(lcount).overlay_flag ;
224 x_psum_data(l_agg_counter).non_std_flag := l_srp_role_info_tbl(lcount).non_std_flag ;
225 x_psum_data(l_agg_counter).role_id := l_srp_role_info_tbl(lcount).role_id ;
226 x_psum_data(l_agg_counter).role_name := l_srp_role_info_tbl(lcount).role_name ;
227 x_psum_data(l_agg_counter).job_title_id := l_srp_role_info_tbl(lcount).job_title_id ;
228 x_psum_data(l_agg_counter).job_discretion := l_srp_role_info_tbl(lcount).job_discretion ;
229 x_psum_data(l_agg_counter).status := l_srp_role_info_tbl(lcount).status ;
230 x_psum_data(l_agg_counter).plan_activate_status := l_srp_role_info_tbl(lcount).plan_activate_status;
231 x_psum_data(l_agg_counter).club_eligible_flag := l_srp_role_info_tbl(lcount).club_eligible_flag;
232 x_psum_data(l_agg_counter).org_code := l_srp_role_info_tbl(lcount).org_code ;
233 x_psum_data(l_agg_counter).start_date := l_srp_role_info_tbl(lcount).start_date ;
234 x_psum_data(l_agg_counter).end_date := l_srp_role_info_tbl(lcount).end_date ;
235 -- change made here
236 x_psum_data(l_agg_counter).group_id := l_srp(i).group_id ;
237 l_agg_counter := l_agg_counter + 1;
238 END LOOP;
239 END IF;
240 END IF;
241 -- dbms_output.put_line('Finishing the iteration');
242 END LOOP;
243 END IF;
244 x_total_rows := x_psum_data.count;
245 END IF;
246
247
248
249 -- End of API body.
250
251 << end_api >>
252
253 -- Standard check of p_commit.
254 IF FND_API.To_Boolean( p_commit ) THEN
255 COMMIT WORK;
256 END IF;
257 -- Standard call to get message count and if count is 1, get message info.
258 FND_MSG_PUB.Count_And_Get
259 (
260 p_count => x_msg_count ,
261 p_data => x_msg_data ,
262 p_encoded => FND_API.G_FALSE
263 );
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 ROLLBACK TO Get_Psum_Data_SP ;
268 x_return_status := FND_API.G_RET_STS_ERROR ;
269 FND_MSG_PUB.Count_And_Get
270 (
274 );
271 p_count => x_msg_count ,
272 p_data => x_msg_data ,
273 p_encoded => FND_API.G_FALSE
275
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277 ROLLBACK TO Get_Psum_Data_SP ;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
279 FND_MSG_PUB.Count_And_Get
280 (
281 p_count => x_msg_count ,
282 p_data => x_msg_data ,
283 p_encoded => FND_API.G_FALSE
284 );
285 WHEN OTHERS THEN
286 ROLLBACK TO Get_Psum_Data_SP ;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
288 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
289 THEN
290 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
291 END IF;
292 FND_MSG_PUB.Count_And_Get
293 (
294 p_count => x_msg_count ,
295 p_data => x_msg_data ,
296 p_encoded => FND_API.G_FALSE
297 );
298 END Get_Psum_Data;
299
300 -- ***********************************
301 -- TBD : MO
302 -- ***********************************
303 PROCEDURE Get_MO_Psum_Data
304 (
305 p_api_version IN NUMBER,
306 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
307 p_commit IN VARCHAR2 := FND_API.G_FALSE,
308 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2,
312 p_mgr_id IN NUMBER,
313 p_comp_group_id IN NUMBER,
314 p_mgr_dtl_flag IN VARCHAR2,
315 p_effective_date IN DATE,
316 p_is_multiorg IN VARCHAR2,
317 x_psum_data OUT NOCOPY psum_tbl_type,
318 x_total_rows OUT NOCOPY NUMBER
319 )
320 IS
321
322 l_api_name CONSTANT VARCHAR2(30) := 'Get_Psum_Data';
323 l_api_version CONSTANT NUMBER := 1.0;
324 l_srp_group_rec CN_SRP_HIER_PROC_PVT.SRP_GROUP_REC_TYPE;
325 l_return_status VARCHAR2(1);
326 l_msg_count NUMBER;
327 l_msg_data VARCHAR2(2000);
328 l_srp_role_info_tbl CN_SRP_HIER_PROC_PVT.SRP_ROLE_INFO_TBL_TYPE;
329 l_returned_rows NUMBER;
330 l_mgr_name VARCHAR2(360);
331 l_srp CN_SRP_HIER_PROC_PVT.group_mbr_tbl_type;
332 l_agg_counter NUMBER := 0;
333
334 BEGIN
335 -- Standard Start of API savepoint
336 SAVEPOINT Get_MO_Psum_Data_SP;
337 -- Standard call to check for call compatibility.
338 IF NOT FND_API.compatible_api_call
339 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
340 THEN
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END IF;
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344 IF FND_API.to_Boolean( p_init_msg_list ) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347 -- Initialize API return status to success
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349 x_total_rows := 0;
350
351 -- API body
352 IF (p_mgr_dtl_flag = 'N') THEN
353
354 /* Setting the input variables */
355 l_srp_group_rec.salesrep_id := p_mgr_id;
356 l_srp_group_rec.group_id := p_comp_group_id;
357 l_srp_group_rec.effective_date := p_effective_date;
358
359
360 CN_SRP_HIER_PROC_PVT.Get_MO_desc_role_info
361 ( p_api_version => 1.0,
362 p_init_msg_list => FND_API.G_FALSE,
363 p_commit => FND_API.G_FALSE,
364 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
365 p_srp => l_srp_group_rec,
366 p_return_current => 'N',
367 p_is_multiorg => p_is_multiorg,
368 x_return_status => l_return_status,
369 x_msg_count => l_msg_count,
370 x_msg_data => l_msg_data,
371 x_srp => l_srp_role_info_tbl,
372 x_returned_rows => l_returned_rows
373 );
374
375 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
376 RAISE FND_API.G_EXC_ERROR;
377 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
378 THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 IF p_is_multiorg = 'Y' THEN
383 SELECT RE.RESOURCE_NAME INTO l_mgr_name
384 FROM JTF_RS_RESOURCE_EXTNS_VL RE, JTF_RS_SALESREPS S
385 WHERE RE.RESOURCE_ID = S.RESOURCE_ID AND TO_NUMBER(S.SALESREP_ID) > 0 AND
386 S.SALESREP_ID = p_mgr_id;
387 ELSE
388 SELECT name INTO l_mgr_name
389 FROM cn_srp_hr_data
390 WHERE srp_id = p_mgr_id;
391 END IF;
392
393
394 IF l_srp_role_info_tbl.COUNT > 0 THEN
395 FOR lcount IN l_srp_role_info_tbl.FIRST .. l_srp_role_info_tbl.LAST LOOP
396
397 x_psum_data(lcount).mgr_id := p_mgr_id ;
398 x_psum_data(lcount).mgr_name := l_mgr_name ;
399
400 x_psum_data(lcount).srp_role_id := l_srp_role_info_tbl(lcount).srp_role_id ;
401 x_psum_data(lcount).srp_id := l_srp_role_info_tbl(lcount).srp_id ;
402 x_psum_data(lcount).overlay_flag := l_srp_role_info_tbl(lcount).overlay_flag ;
403 x_psum_data(lcount).non_std_flag := l_srp_role_info_tbl(lcount).non_std_flag ;
404 x_psum_data(lcount).role_id := l_srp_role_info_tbl(lcount).role_id ;
405 x_psum_data(lcount).role_name := l_srp_role_info_tbl(lcount).role_name ;
406 x_psum_data(lcount).job_title_id := l_srp_role_info_tbl(lcount).job_title_id ;
407 x_psum_data(lcount).job_discretion := l_srp_role_info_tbl(lcount).job_discretion ;
411 x_psum_data(lcount).org_code := l_srp_role_info_tbl(lcount).org_code ;
408 x_psum_data(lcount).status := l_srp_role_info_tbl(lcount).status ;
409 x_psum_data(lcount).plan_activate_status := l_srp_role_info_tbl(lcount).plan_activate_status;
410 x_psum_data(lcount).club_eligible_flag := l_srp_role_info_tbl(lcount).club_eligible_flag;
412 x_psum_data(lcount).start_date := l_srp_role_info_tbl(lcount).start_date ;
413 x_psum_data(lcount).end_date := l_srp_role_info_tbl(lcount).end_date ;
414 -- change made here
415 x_psum_data(lcount).group_id := p_comp_group_id ;
416
417 END LOOP;
418 END IF;
419 x_total_rows := x_psum_data.count;
420 ELSE
421
422 -- DBMS_OUTPUT.PUT_LINE('************** Entered in the Else ************' || p_mgr_id || ',' || p_comp_group_id || ',' || p_effective_date);
423 /* Getting the managers */
424 l_srp_group_rec.salesrep_id := p_mgr_id;
425 l_srp_group_rec.group_id := p_comp_group_id;
426 l_srp_group_rec.effective_date := p_effective_date;
427
428 CN_SRP_HIER_PROC_PVT.Get_MO_Descendant_group_mbrs(
429 p_api_version => 1.0,
430 p_init_msg_list => FND_API.G_FALSE,
431 p_commit => FND_API.G_FALSE,
432 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
433 p_srp => l_srp_group_rec,
434 p_first_level_only => 'Y',
435 p_is_multiorg => p_is_multiorg,
436 x_return_status => l_return_status,
437 x_msg_count => l_msg_count,
438 x_msg_data => l_msg_data,
439 x_srp => l_srp,
440 x_returned_rows => l_returned_rows
441 );
442
443 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
444 RAISE FND_API.G_EXC_ERROR;
445 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
446 THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449
450 l_agg_counter := 1;
451 -- dbms_output.put_line('Count from Get_Descendant_group_mbrs is : ' || l_srp.COUNT);
452 IF l_srp.COUNT > 0 THEN
453 FOR i in l_srp.FIRST..l_srp.LAST LOOP
454 l_srp_group_rec.salesrep_id := l_srp(i).salesrep_id;
455 l_srp_group_rec.group_id := l_srp(i).group_id;
456 l_srp_group_rec.effective_date := p_effective_date;
457
458 IF p_mgr_id <> l_srp(i).salesrep_id THEN
459 CN_SRP_HIER_PROC_PVT.Get_MO_desc_role_info
460 (p_api_version => 1.0,
461 p_init_msg_list => FND_API.G_FALSE,
462 p_commit => FND_API.G_FALSE,
463 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
464 p_srp => l_srp_group_rec,
465 p_return_current => 'N',
466 p_is_multiorg => p_is_multiorg,
467 x_return_status => l_return_status,
468 x_msg_count => l_msg_count,
469 x_msg_data => l_msg_data,
470 x_srp => l_srp_role_info_tbl,
471 x_returned_rows => l_returned_rows
472 );
473
474 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
475 RAISE FND_API.G_EXC_ERROR;
476 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
477 THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END IF;
480
481
482 IF p_is_multiorg = 'Y' THEN
483 SELECT RE.RESOURCE_NAME INTO l_mgr_name
484 FROM JTF_RS_RESOURCE_EXTNS_VL RE, JTF_RS_SALESREPS S
485 WHERE RE.RESOURCE_ID = S.RESOURCE_ID AND TO_NUMBER(S.SALESREP_ID) > 0 AND
486 S.SALESREP_ID = l_srp_group_rec.salesrep_id;
487 ELSE
488 SELECT name INTO l_mgr_name
489 FROM cn_srp_hr_data
490 WHERE srp_id = l_srp_group_rec.salesrep_id;
491 END IF;
492
493 IF l_srp_role_info_tbl.COUNT > 0 THEN
494 FOR lcount IN l_srp_role_info_tbl.FIRST .. l_srp_role_info_tbl.LAST LOOP
495 -- DBMS_OUTPUT.PUT_LINE('Entered in the inner most loop ');
496 x_psum_data(l_agg_counter).mgr_id := l_srp_group_rec.salesrep_id;
497 x_psum_data(l_agg_counter).mgr_name := l_mgr_name;
498 x_psum_data(l_agg_counter).srp_role_id := l_srp_role_info_tbl(lcount).srp_role_id;
499 x_psum_data(l_agg_counter).srp_id := l_srp_role_info_tbl(lcount).srp_id;
500 x_psum_data(l_agg_counter).overlay_flag := l_srp_role_info_tbl(lcount).overlay_flag ;
501 x_psum_data(l_agg_counter).non_std_flag := l_srp_role_info_tbl(lcount).non_std_flag ;
502 x_psum_data(l_agg_counter).role_id := l_srp_role_info_tbl(lcount).role_id ;
503 x_psum_data(l_agg_counter).role_name := l_srp_role_info_tbl(lcount).role_name ;
504 x_psum_data(l_agg_counter).job_title_id := l_srp_role_info_tbl(lcount).job_title_id ;
505 x_psum_data(l_agg_counter).job_discretion := l_srp_role_info_tbl(lcount).job_discretion ;
506 x_psum_data(l_agg_counter).status := l_srp_role_info_tbl(lcount).status ;
507 x_psum_data(l_agg_counter).plan_activate_status := l_srp_role_info_tbl(lcount).plan_activate_status;
508 x_psum_data(l_agg_counter).club_eligible_flag := l_srp_role_info_tbl(lcount).club_eligible_flag;
509 x_psum_data(l_agg_counter).org_code := l_srp_role_info_tbl(lcount).org_code ;
510 x_psum_data(l_agg_counter).start_date := l_srp_role_info_tbl(lcount).start_date ;
511 x_psum_data(l_agg_counter).end_date := l_srp_role_info_tbl(lcount).end_date ;
512 -- change made here
516 END IF;
513 x_psum_data(l_agg_counter).group_id := l_srp(i).group_id ;
514 l_agg_counter := l_agg_counter + 1;
515 END LOOP;
517 END IF;
518 -- dbms_output.put_line('Finishing the iteration');
519 END LOOP;
520 END IF;
521 x_total_rows := x_psum_data.count;
522 END IF;
523
524
525
526 -- End of API body.
527
528 << end_api >>
529
530 -- Standard check of p_commit.
531 IF FND_API.To_Boolean( p_commit ) THEN
532 COMMIT WORK;
533 END IF;
534 -- Standard call to get message count and if count is 1, get message info.
535 FND_MSG_PUB.Count_And_Get
536 (
537 p_count => x_msg_count ,
538 p_data => x_msg_data ,
539 p_encoded => FND_API.G_FALSE
540 );
541
542 EXCEPTION
543 WHEN FND_API.G_EXC_ERROR THEN
544 ROLLBACK TO Get_MO_Psum_Data_SP ;
545 x_return_status := FND_API.G_RET_STS_ERROR ;
546 FND_MSG_PUB.Count_And_Get
547 (
548 p_count => x_msg_count ,
549 p_data => x_msg_data ,
550 p_encoded => FND_API.G_FALSE
551 );
552
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 ROLLBACK TO Get_MO_Psum_Data_SP ;
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
556 FND_MSG_PUB.Count_And_Get
557 (
558 p_count => x_msg_count ,
559 p_data => x_msg_data ,
560 p_encoded => FND_API.G_FALSE
561 );
562 WHEN OTHERS THEN
563 ROLLBACK TO Get_MO_Psum_Data_SP ;
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
565 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
566 THEN
567 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
568 END IF;
569 FND_MSG_PUB.Count_And_Get
570 (
571 p_count => x_msg_count ,
572 p_data => x_msg_data ,
573 p_encoded => FND_API.G_FALSE
574 );
575 END Get_MO_Psum_Data;
576
577
578 END CN_psum_PVT;
579