[Home] [Help]
PACKAGE BODY: APPS.PRP_RTTAP_INT_PVT
Source
1 PACKAGE BODY PRP_RTTAP_INT_PVT as
2 /* $Header: PRPVRTPB.pls 120.4 2006/02/20 16:59:24 hekkiral noship $ */
3
4 G_PROPOSAL_ID NUMBER;
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PRP_RTTAP_INT_PVT';
6
7 PROCEDURE LOG_MESSAGES(
8 P_LOG_MESSAGE IN VARCHAR2,
9 P_MODULE_NAME IN VARCHAR2,
10 P_LOG_LEVEL IN NUMBER) IS
11 BEGIN
12
13 IF ( P_LOG_LEVEL >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 FND_LOG.STRING(LOG_LEVEL => P_LOG_LEVEL,
15 MODULE => G_PKG_NAME ||':'|| P_MODULE_NAME,
16 MESSAGE => P_LOG_MESSAGE);
17 END IF;
18
19 END LOG_MESSAGES;
20
21
22 PROCEDURE CALL_RUNTIME_TAP(
23 P_Api_Version_Number IN NUMBER,
24 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
25 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_Proposal_id IN NUMBER,
27 X_Return_Status OUT NOCOPY VARCHAR2,
28 X_Msg_Count OUT NOCOPY NUMBER,
29 X_Msg_Data OUT NOCOPY VARCHAR2
30 )
31 IS
32 l_errbuf VARCHAR2(4000);
33 l_retcode VARCHAR2(255);
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(1000);
36 l_trans_rec JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
37 l_WinningTerrMember_tbl JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
38 l_api_name CONSTANT VARCHAR2(30) := 'CALL_RUNTIME_TAP';
39 l_api_version_number CONSTANT NUMBER := 1.0;
40 L_RETURN_STATUS VARCHAR2(10);
41 BEGIN
42 G_PROPOSAL_ID := p_proposal_id;
43 SAVEPOINT CALL_RUNTIME_TAP;
44
45 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
46 p_api_version_number,
47 l_api_name,
48 G_PKG_NAME)
49 THEN
50 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
51 END IF;
52
53 -- Initialize message list if p_init_msg_list is set to TRUE.
54 IF FND_API.to_Boolean( p_init_msg_list ) THEN
55 FND_MSG_PUB.initialize;
56 END IF;
57
58 -- Log Debug Messages.
59 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
60 P_MODULE_NAME => l_api_name,
61 P_LOG_MESSAGE => 'In CALL_RUNTIME_TAP.. Parameters: ' ||'P_Proposal_id: ' || p_proposal_id);
62
63
64 -- Initialize API return status to SUCCESS
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 IF NVL(FND_PROFILE.Value('PRP_ENABLE_ONLINE_TAP'), 'N') = 'Y' THEN
68
69 l_trans_rec.trans_object_id1 := jtf_terr_number_list(G_PROPOSAL_ID);
70 l_trans_rec.trans_object_id2 := jtf_terr_number_list(null);
71 l_trans_rec.trans_object_id3 := jtf_terr_number_list(null);
72 l_trans_rec.trans_object_id4 := jtf_terr_number_list(null);
73 l_trans_rec.trans_object_id5 := jtf_terr_number_list(null);
74 l_trans_rec.txn_date := jtf_terr_date_list(null);
75
76 -- Log Debug Messages.
77 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
78 P_MODULE_NAME => l_api_name,
79 p_LOG_MESSAGE => 'Before Calling JTY_ASSIGN_REALTIME_PUB.get_winners');
80
81 JTY_ASSIGN_REALTIME_PUB.get_winners(
82 p_api_version_number => 1.0,
83 p_init_msg_list => FND_API.G_FALSE,
84 p_source_id => -1001,
85 p_trans_id => -1106,
86 p_mode => 'REAL TIME:RESOURCE',
87 p_param_passing_mechanism => 'PBR',
88 p_program_name => 'SALES/PROPOSAL PROGRAM',
89 p_trans_rec => l_trans_rec,
90 p_name_value_pair => null,
91 p_role => null,
92 p_resource_type => null,
93 x_return_status => l_return_status,
94 x_msg_count => l_msg_count,
95 x_msg_data => l_msg_data,
96 x_winners_rec => l_WinningTerrMember_tbl);
97
98 -- Log Debug Messages.
99 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
100 P_MODULE_NAME => l_api_name,
101 P_LOG_MESSAGE => 'After Calling JTY_ASSIGN_REALTIME_PUB.get_winners.. x_return_status: '||l_return_status || ' x_message_data: ' || l_msg_data);
102
103 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
104 If l_msg_data is NOT NULL THEN
105 FND_MSG_PUB.Add_Exc_Msg('JTY_ASSIGN_REALTIME_PUB',
106 'GET_WINNERS',
107 l_msg_data);
108 End If;
109
110 FND_MSG_PUB.Count_And_Get
111 ( p_count => x_msg_count,
112 p_data => x_msg_data
113 );
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117 IF (l_WinningTerrMember_tbl.resource_id.count > 0) THEN
118
119 FOR i IN l_WinningTerrMember_tbl.terr_id.FIRST .. l_WinningTerrMember_tbl.terr_id.LAST LOOP
120 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
121 P_MODULE_NAME => l_api_name,
122 P_LOG_MESSAGE => 'Data from Winning Records... Trans Object ID : ' || l_WinningTerrMember_tbl.trans_object_id(i) ||
123 'Trans Detail Object ID : ' || l_WinningTerrMember_tbl.trans_detail_object_id(i) ||
124 'Terr ID : ' || l_WinningTerrMember_tbl.terr_id(i) || ' Terr Name : ' || l_WinningTerrMember_tbl.terr_name(i) ||
125 ' Resource ID : ' || l_WinningTerrMember_tbl.resource_id(i) ||
126 ' Resource TYPE : ' || l_WinningTerrMember_tbl.resource_type(i));
127 END LOOP;
128
129 -- Log Debug Messages.
130 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
131 P_MODULE_NAME => l_api_name,
132 p_LOG_MESSAGE => 'Before Calling Explode_Groups');
133
134 -- Explode GROUPS if any inside winners
135 EXPLODE_GROUPS(
136 x_errbuf => l_errbuf,
137 x_retcode => l_retcode,
138 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
139 x_return_status => l_return_status);
140
141
142 -- Log Debug Messages.
143 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
144 P_MODULE_NAME => l_api_name,
145 p_LOG_MESSAGE => 'After Calling Explode_Groups... ' ||'x_return_status: ' || l_return_status);
146
147 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
148 x_msg_data := l_errbuf;
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 End If;
151
152 -- Log Debug Messages.
153 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
154 P_MODULE_NAME => l_api_name,
155 p_LOG_MESSAGE => 'Before Calling Explode_Teams');
156 -- Explode TEAMS if any inside winners
157 EXPLODE_TEAMS(
158 x_errbuf => l_errbuf,
159 x_retcode => l_retcode,
160 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
161 x_return_status => l_return_status);
162
163
164 -- Log Debug Messages.
165 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
166 P_MODULE_NAME => l_api_name,
167 p_LOG_MESSAGE => 'After Calling Explode_Teams... ' ||' x_return_Status: ' || l_return_status);
168
169 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
170 x_msg_data := l_errbuf;
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 End If;
173
174 -- Insert into Proposal Accesses from Winners
175
176 -- Log Debug Messages.
177 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
178 P_MODULE_NAME => l_api_name,
179 p_LOG_MESSAGE => 'Before Calling Insert_Accesses');
180 INSERT_ACCESSES(
181 x_errbuf => l_errbuf,
182 x_retcode => l_retcode,
183 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
184 x_return_status => l_return_status);
185
186 -- Log Debug Messages.
187 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
188 P_MODULE_NAME => l_api_name,
189 p_LOG_MESSAGE => 'After Calling Insert_Accesses... ' ||' x_return_Status: ' || l_return_status);
190
191 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
192 x_msg_data := l_errbuf;
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 End If;
195
196 -- Insert into territory Accesses
197
198 -- Log Debug Messages.
199 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
200 P_MODULE_NAME => l_api_name,
201 p_LOG_MESSAGE => 'Before Calling Insert_Terr_Accesses');
202 INSERT_TERR_ACCESSES(
203 x_errbuf => l_errbuf,
204 x_retcode => l_retcode,
205 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
206 x_return_status => l_return_status);
207
208 -- Log Debug Messages.
209 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
210 P_MODULE_NAME => l_api_name,
211 p_LOG_MESSAGE => 'After Calling Insert_Terr_Accesses... ' ||' x_return_Status: ' || l_return_status);
212
213 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
214 x_msg_data := l_errbuf;
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 End If;
217 END IF;
218
219
220 -- Log Debug Messages.
221 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
222 P_MODULE_NAME => l_api_name,
223 p_LOG_MESSAGE => 'Before Calling Perform_Cleanup');
224 PERFORM_CLEANUP(
225 x_errbuf => l_errbuf,
226 x_retcode => l_retcode,
227 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
228 x_return_status => l_return_status);
229
230
231 -- Log Debug Messages.
232 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
233 P_MODULE_NAME => l_api_name,
234 p_LOG_MESSAGE => 'After Calling Perform_Cleanup... ' ||' x_return_Status: ' || l_return_status);
235
236 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
237 l_msg_data := l_errbuf;
238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
239 End If;
240
241
242 END IF;
243
244 -- Standard check for p_commit
245 IF FND_API.to_Boolean( p_commit ) THEN
246 COMMIT WORK;
247 END IF;
248
249 EXCEPTION
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 ROLLBACK to CALL_RUNTIME_TAP;
252 X_Return_Status := FND_API.G_RET_STS_ERROR;
253 FND_MSG_PUB.Count_And_Get
254 ( p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257 WHEN OTHERS THEN
258 ROLLBACK to CALL_RUNTIME_TAP;
259 X_Return_Status := FND_API.G_RET_STS_ERROR;
260 FND_MSG_PUB.Count_And_Get
261 ( p_count => x_msg_count,
262 p_data => x_msg_data
263 );
264
265 END CALL_RUNTIME_TAP;
266
267 /************************** Start Explode Teams ******************/
268 PROCEDURE EXPLODE_TEAMS(
269 x_errbuf OUT NOCOPY VARCHAR2,
270 x_retcode OUT NOCOPY VARCHAR2,
271 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
272 x_return_status OUT NOCOPY VARCHAR2)
273 IS
274
275 /*-------------------------------------------------------------------------+
276 | LOGIC
277 |
278 | A RESOURCE team can be comprised OF resources who belong TO one OR more
279 | GROUPS OF resources.
280 | So get a LIST OF team members (OF TYPE employee AND play a ROLE OF salesrep )
281 | AND get atleast one GROUP id that they belong TO
282 | WHERE they play a similar ROLE.
283 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
284 | are a RESOURCE within a team.
285 | INSERT these members INTO winners IF they are NOT already IN winners.
286 +-------------------------------------------------------------------------*/
287
288 l_errbuf VARCHAR2(4000);
289 l_retcode VARCHAR2(255);
290 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
291
292 l_resource_id num_list;
293 l_group_id num_list;
294 l_person_id num_list;
295
296
297 BEGIN
298
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300 /* Get resources within a resource team */
301
302 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
303 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
304 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_TEAM' THEN
305
306 SELECT resource_id, group_id , person_id
307 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
308 FROM
309 (
310 SELECT TM.team_resource_id resource_id,
311 TM.person_id person_id2,
312 MIN(G.group_id)group_id,
313 MIN(T.team_id) team_id,
314 TRES.CATEGORY resource_category,
315 MIN(TRES.source_id) person_id
316 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
317 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
318 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
319 (
320 SELECT m.group_id group_id, m.resource_id resource_id
321 FROM jtf_rs_group_members m,
322 jtf_rs_groups_b g,
323 jtf_rs_group_usages u,
324 jtf_rs_role_relations rr,
325 jtf_rs_roles_b r,
326 jtf_rs_resource_extns res
327 WHERE m.group_id = g.group_id
328 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
329 AND NVL(g.end_date_active,SYSDATE)
330 AND u.group_id = g.group_id
331 AND u.usage = 'SALES'
332 AND m.group_member_id = rr.role_resource_id
333 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
334 AND rr.delete_flag <> 'Y'
335 AND SYSDATE BETWEEN rr.start_date_active
336 AND NVL(rr.end_date_active,SYSDATE)
337 AND rr.role_id = r.role_id
338 AND r.role_type_code
339 IN ('SALES', 'TELESALES', 'FIELDSALES')
340 AND r.active_flag = 'Y'
341 AND res.resource_id = m.resource_id
342 AND res.CATEGORY IN ('EMPLOYEE')
343 ) G
344 WHERE tm.team_id = t.team_id
345 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
346 AND NVL(t.end_date_active,SYSDATE)
347 AND tu.team_id = t.team_id
348 AND tu.usage = 'SALES'
349 AND tm.team_member_id = trr.role_resource_id
350 AND tm.delete_flag <> 'Y'
351 AND tm.resource_type = 'INDIVIDUAL'
352 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
353 AND trr.delete_flag <> 'Y'
354 AND SYSDATE BETWEEN trr.start_date_active
355 AND NVL(trr.end_date_active,SYSDATE)
356 AND trr.role_id = tr.role_id
357 AND tr.role_type_code IN
358 ('SALES', 'TELESALES', 'FIELDSALES')
359 AND tr.active_flag = 'Y'
360 AND tres.resource_id = tm.team_resource_id
361 AND tres.category IN ('EMPLOYEE')
362 AND tm.team_resource_id = g.resource_id
363 GROUP BY tm.team_resource_id,
364 tm.person_id,
365 tres.CATEGORY,
366 tres.source_id
367 UNION ALL
368 SELECT MIN(m.resource_id) resource_id,
369 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
370 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
371 MIN(res.source_id) person_id
372 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
373 jtf_rs_group_usages u, jtf_rs_role_relations rr,
374 jtf_rs_roles_b r, jtf_rs_resource_extns res,
375 (
376 SELECT tm.team_resource_id group_id,
377 t.team_id team_id
378 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
379 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
380 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
381 WHERE tm.team_id = t.team_id
382 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
383 AND NVL(t.end_date_active,SYSDATE)
384 AND tu.team_id = t.team_id
385 AND tu.usage = 'SALES'
386 AND tm.team_member_id = trr.role_resource_id
387 AND tm.delete_flag <> 'Y'
388 AND tm.resource_type = 'GROUP'
389 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
390 AND trr.delete_flag <> 'Y'
391 AND SYSDATE BETWEEN trr.start_date_active
392 AND NVL(trr.end_date_active,SYSDATE)
393 AND trr.role_id = tr.role_id
394 AND tr.role_type_code IN
395 ('SALES', 'TELESALES', 'FIELDSALES')
396 AND tr.active_flag = 'Y'
397 AND tres.resource_id = tm.team_resource_id
398 AND tres.category IN ('EMPLOYEE')
399 ) jtm
400 WHERE m.group_id = g.group_id
401 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
402 AND NVL(g.end_date_active,SYSDATE)
403 AND u.group_id = g.group_id
404 AND u.usage = 'SALES'
405 AND m.group_member_id = rr.role_resource_id
406 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
407 AND rr.delete_flag <> 'Y'
408 AND SYSDATE BETWEEN rr.start_date_active
409 AND NVL(rr.end_date_active,SYSDATE)
410 AND rr.role_id = r.role_id
411 AND r.role_type_code IN
412 ('SALES', 'TELESALES', 'FIELDSALES')
413 AND r.active_flag = 'Y'
414 AND res.resource_id = m.resource_id
415 AND res.category IN ('EMPLOYEE')
416 AND jtm.group_id = g.group_id
417 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
418 WHERE j.team_id = p_WinningTerrMember_tbl.resource_id(l_index);
419
420
421
422 IF l_resource_id.COUNT > 0 THEN
423 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
424 /* No need to Check to see if it is already part of
425 p_WinningTerrMember_tbl because this will be slow,
426 So we insert into p_WinningTerrMember_tbl directly*/
427 IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
428 p_WinningTerrMember_tbl.resource_id.EXTEND;
429 p_WinningTerrMember_tbl.group_id.EXTEND;
430 p_WinningTerrMember_tbl.person_id.EXTEND;
431 p_WinningTerrMember_tbl.resource_type.EXTEND;
432 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
433 p_WinningTerrMember_tbl.terr_id.EXTEND;
434 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
435 p_WinningTerrMember_tbl.org_id.EXTEND;
436 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_id(i);
437 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_group_id(i);
438 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.person_id.COUNT ) := l_person_id(i);
439 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT) := 'RS_EMPLOYEE';
440 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.full_access_flag(l_index);
441 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
442 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_PROPOSAL_ID;
443 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.org_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
444 END IF;
445 END LOOP;
446 END IF;
447 END IF;
448 END LOOP;
449 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
450 EXCEPTION
451 WHEN others THEN
452 x_errbuf := SQLERRM;
453 x_retcode := SQLCODE;
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','EXPLODE_TEAMS',SQLERRM);
456 -- Log Debug Messages.
457 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
458 P_MODULE_NAME => 'Insert_Terr_Accesses',
459 p_LOG_MESSAGE => 'Error While Exploding Teams.. ' ||' x_errbuf: ' || x_errbuf);
460 END EXPLODE_TEAMS;
461 /************************** End Explode Teams ******************/
462
463
464 /************************** Start Explode Groups ******************/
465 PROCEDURE EXPLODE_GROUPS(
466 x_errbuf OUT NOCOPY VARCHAR2,
467 x_retcode OUT NOCOPY VARCHAR2,
468 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
469 x_return_status OUT NOCOPY VARCHAR2)
470 IS
471 -------------RS_GROUP---------
472 /*-------------------------------------------------------------------------+
473 | PROGRAM LOGIC
474 |
475 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
476 | a sales ROLE AND are an employee AND INSERT back INTO winners IF they are
477 | NOT already IN winners.
478 +-------------------------------------------------------------------------*/
479 l_errbuf VARCHAR2(4000);
480 l_retcode VARCHAR2(255);
481
482 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
483
484 l_resource_id num_list;
485 l_group_id num_list;
486 l_person_id num_list;
487
488 BEGIN
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491 /* Get resources within a resource team */
492 /** Note
493 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
494 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
495 **/
496
497 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
498 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
499 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_GROUP' THEN
500 SELECT resource_id, group_id,person_id
501 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
502 FROM
503 (
504 SELECT min(m.resource_id) resource_id,
505 res.category resource_category,
506 m.group_id group_id, min(res.source_id) person_id
507 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
508 jtf_rs_group_usages u, jtf_rs_role_relations rr,
509 jtf_rs_roles_b r, jtf_rs_resource_extns res
510 WHERE m.group_id = g.group_id
511 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
512 AND NVL(g.end_date_active,SYSDATE)
513 AND u.group_id = g.group_id
514 AND u.usage = 'SALES'
515 AND m.group_member_id = rr.role_resource_id
516 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
517 AND rr.role_id = r.role_id
518 AND rr.delete_flag <> 'Y'
519 AND SYSDATE BETWEEN rr.start_date_active
520 AND NVL(rr.end_date_active,SYSDATE)
521 AND r.role_type_code IN
522 ('SALES', 'TELESALES', 'FIELDSALES')
523 AND r.active_flag = 'Y'
524 AND res.resource_id = m.resource_id
525 AND res.category IN ('EMPLOYEE')
526 GROUP BY m.group_member_id, m.resource_id, m.person_id,
527 m.group_id, res.CATEGORY) j
528 WHERE j.group_id = p_WinningTerrMember_tbl.resource_id(l_index);
529
530
531 IF l_resource_id.COUNT > 0 THEN
532 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
533 /* No need to Check to see if it is already part of
534 p_WinningTerrMember_tbl because this will be slow,
535 So we insert into p_WinningTerrMember_tbl directly*/
536 IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
537 p_WinningTerrMember_tbl.resource_id.EXTEND;
538 p_WinningTerrMember_tbl.group_id.EXTEND;
539 p_WinningTerrMember_tbl.person_id.EXTEND;
540 p_WinningTerrMember_tbl.resource_type.EXTEND;
541 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
542 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
543 p_WinningTerrMember_tbl.terr_id.EXTEND;
544 p_WinningTerrMember_tbl.org_id.EXTEND;
545 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_resource_id(i);
546 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_group_id(i);
547 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_person_id(i);
548 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT ) := 'RS_EMPLOYEE';
549 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT ) := p_WinningTerrMember_tbl.full_access_flag(l_index);
550 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_PROPOSAL_ID;
551 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
552 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.resource_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
553 END IF;
554 END LOOP;
555 END IF;
556 END IF;
557 END LOOP;
558
559 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
560 EXCEPTION
561 WHEN OTHERS THEN
562 x_errbuf := SQLERRM;
563 x_retcode := SQLCODE;
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','EXPLODE_GROUPS',SQLERRM);
566 -- Log Debug Messages.
567 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
568 P_MODULE_NAME => 'Insert_Terr_Accesses',
569 p_LOG_MESSAGE => 'Error While Exploding Groups.. ' ||' x_errbuf: ' || x_errbuf);
570 END EXPLODE_GROUPS;
571
572 /************************** End Explode Groups ******************/
573
574
575 /************************** Start Insert Accessses ***************/
576 PROCEDURE INSERT_ACCESSES(
577 x_errbuf OUT NOCOPY VARCHAR2,
578 x_retcode OUT NOCOPY VARCHAR2,
579 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
580 x_return_status OUT NOCOPY VARCHAR2) IS
581 BEGIN
582
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
585 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
586 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' AND p_WinningTerrMember_tbl.group_id(l_index) IS NOT NULL THEN
587
588 INSERT INTO PRP_PROPOSAL_ACCESSES
589 (proposal_access_id ,
590 last_update_date ,
591 last_updated_by,
592 creation_date ,
593 created_by ,
594 last_update_login,
595 proposal_id,
596 resource_id,
597 resource_group_id,
598 Access_level,
599 Keep_flag)
600 SELECT PRP_PROPOSAL_ACCESSES_S1.NEXTVAL,
601 SYSDATE,
602 FND_GLOBAL.USER_ID,
603 SYSDATE,
604 FND_GLOBAL.USER_ID,
605 FND_GLOBAL.USER_ID,
606 pp.Proposal_id,
607 p_WinningTerrMember_tbl.resource_id(l_index),
608 p_WinningTerrMember_tbl.group_id(l_index),
609 DECODE(p_WinningTerrMember_tbl.full_access_flag(l_index),'Y','FULL','READ'),
610 'N'
611 FROM PRP_PROPOSALS pp
612 WHERE pp.proposal_id = G_Proposal_ID
613 AND NOT EXISTS
614 ( SELECT NULL FROM PRP_PROPOSAL_ACCESSES ACC
615 WHERE ACC.proposal_id = pp.proposal_id
616 AND ACC.resource_id = p_WinningTerrMember_tbl.resource_id(l_index)
617 AND ACC.resource_group_id = p_WinningTerrMember_tbl.group_id(l_index) );
618 END IF;
619 END LOOP;
620 END IF;
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 x_errbuf := SQLERRM;
625 x_retcode := SQLCODE;
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','INSERT_ACCESS',SQLERRM);
628 -- Log Debug Messages.
629 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
630 P_MODULE_NAME => 'Insert_Terr_Accesses',
631 p_LOG_MESSAGE => 'Error While Inserting into PRP_Accesses... ' ||' x_errbuf: ' || x_errbuf);
632 END INSERT_ACCESSES;
633 /************************** End Insert Accessses ***************/
634
635 /************************** Start Insert Territory Accessses ***************/
636 PROCEDURE INSERT_TERR_ACCESSES(
637 x_errbuf OUT NOCOPY VARCHAR2,
638 x_retcode OUT NOCOPY VARCHAR2,
639 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
640 x_return_status OUT NOCOPY VARCHAR2) IS
641 BEGIN
642
643 /*------------------------------------------------------------------------------+
644 | we are deleting all rows for the entity from as_territory_accesses prior to
645 | inserting into it because the logic for removing only certain terr_id/access_id
646 | combinations is very complex and could be slow..
647 +-------------------------------------------------------------------------------*/
648 x_return_status := FND_API.G_RET_STS_SUCCESS;
649
650 DELETE FROM PRP_TERRITORY_ACCESSES TACC
651 WHERE TACC.proposal_access_id IN
652 (SELECT ACC.proposal_access_id
653 FROM PRP_PROPOSAL_ACCESSES ACC
654 WHERE proposal_id = G_PROPOSAL_ID);
655
656 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
657 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
658
659 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' THEN
660
661 INSERT INTO PRP_TERRITORY_ACCESSES
662 (proposal_access_id,
663 territory_id,
664 object_version_number,
665 last_update_date,
666 last_updated_by,
667 creation_date,
668 created_by,
669 last_update_login )
670 SELECT
671 ACC.proposal_access_id,
672 p_WinningTerrMember_tbl.terr_id(l_index),
673 1,
674 SYSDATE,
675 FND_GLOBAL.USER_ID,
676 SYSDATE,
677 FND_GLOBAL.USER_ID,
678 FND_GLOBAL.USER_ID
679 FROM PRP_PROPOSAL_ACCESSES ACC
680 WHERE ACC.proposal_id = G_PROPOSAL_ID
681 AND ACC.resource_id = p_WinningTerrMember_tbl.resource_id(l_index)
682 AND ACC.resource_group_id = p_WinningTerrMember_tbl.group_id(l_index)
683 AND NOT EXISTS ( SELECT 'Y'
684 FROM PRP_TERRITORY_ACCESSES
685 WHERE proposal_ACCESS_ID = ACC.Proposal_access_id
686 AND TERRITORY_ID = p_WinningTerrMember_tbl.terr_id(l_index)) ;
687 END IF;
688 END LOOP;
689 END IF;
690
691 EXCEPTION
692 WHEN OTHERS THEN
693 x_errbuf := SQLERRM;
694 x_retcode := SQLCODE;
695 x_return_status := FND_API.G_RET_STS_ERROR;
696
697 FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','INSERT_TERR_ACCESSES',SQLERRM);
698 -- Log Debug Messages.
699 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
700 P_MODULE_NAME => 'Insert_Terr_Accesses',
701 p_LOG_MESSAGE => 'Error While Inserting into PRP_Terroritory_Accesses... ' ||' x_errbuf: ' || x_errbuf);
702 END INSERT_TERR_ACCESSES;
703 /************************** End Insert Territory Accessses ***************/
704
705
706 /************************** Start Perform Cleanup ***************/
707 PROCEDURE PERFORM_CLEANUP(
708 x_errbuf OUT NOCOPY VARCHAR2,
709 x_retcode OUT NOCOPY VARCHAR2,
710 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
711 x_return_status OUT NOCOPY VARCHAR2) IS
712
713 BEGIN
714 x_return_status := FND_API.G_RET_STS_SUCCESS;
715
716 DELETE FROM PRP_PROPOSAL_ACCESSES ACC
717 WHERE proposal_id = G_PROPOSAL_ID
718 AND NVL(keep_flag, 'N') <> 'Y'
719 AND RESOURCE_ID||RESOURCE_GROUP_ID NOT IN (
720 SELECT RESTAB.RES||GRPTAB.GRP FROM
721 (SELECT rownum ROW_NUM,A.COLUMN_VALUE RES FROM TABLE(CAST(p_WinningTerrMember_tbl.resource_id AS jtf_terr_number_list)) a) RESTAB,
722 (SELECT rownum ROW_NUM,b.COLUMN_VALUE GRP FROM TABLE(CAST(p_WinningTerrMember_tbl.group_id AS jtf_terr_number_list)) b) GRPTAB
723 WHERE RESTAB.ROW_NUM = GRPTAB.ROW_NUM
724 );
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 x_errbuf := SQLERRM;
729 x_retcode := SQLCODE;
730 x_return_status := FND_API.G_RET_STS_ERROR;
731 FND_MSG_PUB.Add_Exc_Msg('PRP_RTTAP_INT_PVT','PERFORM_CLEANUP',SQLERRM);
732 -- Log Debug Messages.
733 LOG_MESSAGES(P_LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
734 P_MODULE_NAME => 'Insert_Terr_Accesses',
735 p_LOG_MESSAGE => 'Error in Perform_Cleanup... ' ||' x_errbuf: ' || x_errbuf);
736 END PERFORM_CLEANUP;
737 /************************** End Perform Cleanup ***************/
738
739 END PRP_RTTAP_INT_PVT;
740