[Home] [Help]
PACKAGE BODY: APPS.AS_RTTAP_ACCOUNT
Source
1 PACKAGE BODY AS_RTTAP_ACCOUNT as
2 /* $Header: asxrtacb.pls 120.6.12000000.2 2007/04/26 13:28:08 annsrini ship $ */
3
4 TYPE ResourceList is VARRAY(10000) OF NUMBER(15);
5 TYPE GroupList is VARRAY(10000) OF NUMBER(15);
6
7 TYPE ResourceRec is RECORD (
8 resource_id ResourceList := ResourceList(),
9 group_id GroupList := GroupList());
10 G_ENTITY CONSTANT VARCHAR2(20) := 'GAR::ACCOUNTS::RT::';
11 G_PARTY_ID NUMBER;
12 G_PKG_NAME CONSTANT VARCHAR2(20) := 'AS_RTTAP_ACCOUNT';
13
14 PROCEDURE PROCESS_RTTAP_ACCOUNT(p_party_Id NUMBER,p_return_status OUT NOCOPY VARCHAR2)
15 IS
16 l_msg_count NUMBER;
17 l_msg_data VARCHAR2(2000);
18 l_event_key VARCHAR2(240);
19 l_return_status VARCHAR2(1);
20 tap_return_status VARCHAR2(1);
21 BEGIN
22
23 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
24 AS_GAR.g_debug_flag := 'Y';
25 END IF;
26
27 AS_BUSINESS_EVENT_PUB.Before_Cust_STeam_Update(
28 p_api_version_number => 2.0,
29 p_init_msg_list => FND_API.G_FALSE,
30 p_commit => FND_API.G_FALSE,
31 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
32 p_cust_id => P_party_id,
33 x_return_status => l_return_status,
34 x_msg_count => l_msg_count,
35 x_msg_data => l_msg_data,
36 x_event_key => l_event_key);
37 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
38 AS_GAR.LOG('BE FOR ACCOUNT REALTIME TAP BEFORE UPDATE FAILED');
39 l_event_key := NULL;
40 END IF;
41
42 RTTAP_WRAPPER(p_party_Id,p_return_status);
43
44 IF l_event_key IS NOT NULL
45 THEN
46 AS_BUSINESS_EVENT_PUB.Upd_Cust_STeam_post_event(
47 p_api_version_number => 2.0,
48 p_init_msg_list => FND_API.G_FALSE,
49 p_commit => FND_API.G_FALSE,
50 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
51 p_cust_id => p_party_id,
52 p_event_key => l_event_key,
53 x_return_status => l_return_status,
54 x_msg_count => l_msg_count,
55 x_msg_data => l_msg_data);
56 END IF;
57 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
58 AS_GAR.LOG('BE FOR ACCOUNT REALTIME TAP AFTER UPDATE FAILED');
59 END IF;
60
61 END PROCESS_RTTAP_ACCOUNT;
62
63 FUNCTION CREATE_ORGANIZATION_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
64 p_return_status VARCHAR2(1);
65 BEGIN
66 PROCESS_RTTAP_ACCOUNT(p_event.GetValueForParameter('PARTY_ID'),p_return_status);
67 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70 RETURN 'SUCCESS';
71 EXCEPTION
72 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
73 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_ORGANIZATION_POST', p_event.getEventName(), p_subscription_guid);
74 WF_EVENT.setErrorInfo(p_event, 'ERROR');
75 RETURN 'ERROR';
76 WHEN OTHERS THEN
77 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_ORGANIZATION_POST', p_event.getEventName(), p_subscription_guid);
78 WF_EVENT.setErrorInfo(p_event, 'ERROR');
79 RETURN 'ERROR';
80 END CREATE_ORGANIZATION_POST;
81
82 FUNCTION UPDATE_ORGANIZATION_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
83 p_return_status VARCHAR2(1);
84 BEGIN
85 PROCESS_RTTAP_ACCOUNT(p_event.GetValueForParameter('PARTY_ID'),p_return_status);
86 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88 END IF;
89 RETURN 'SUCCESS';
90 EXCEPTION
91 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
92 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_ORGANIZATION_POST', p_event.getEventName(), p_subscription_guid);
93 WF_EVENT.setErrorInfo(p_event, 'ERROR');
94 RETURN 'ERROR';
95 WHEN OTHERS THEN
96 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_ORGANIZATION_POST', p_event.getEventName(), p_subscription_guid);
97 WF_EVENT.setErrorInfo(p_event, 'ERROR');
98 RETURN 'ERROR';
99 END;
100
101 FUNCTION CREATE_PERSON_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
102 p_return_status VARCHAR2(1);
103 BEGIN
104 PROCESS_RTTAP_ACCOUNT(p_event.GetValueForParameter('PARTY_ID'),p_return_status);
105 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108 RETURN 'SUCCESS';
109 EXCEPTION
110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
111 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_PERSON_POST', p_event.getEventName(), p_subscription_guid);
112 WF_EVENT.setErrorInfo(p_event, 'ERROR');
113 RETURN 'ERROR';
114 WHEN OTHERS THEN
115 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_PERSON_POST', p_event.getEventName(), p_subscription_guid);
116 WF_EVENT.setErrorInfo(p_event, 'ERROR');
117 RETURN 'ERROR';
118 END;
119
120
121 FUNCTION UPDATE_PERSON_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
122 p_return_status VARCHAR2(1);
123 BEGIN
124 PROCESS_RTTAP_ACCOUNT(p_event.GetValueForParameter('PARTY_ID'),p_return_status);
125 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128 RETURN 'SUCCESS';
129 EXCEPTION
130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
131 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_PERSON_POST', p_event.getEventName(), p_subscription_guid);
132 WF_EVENT.setErrorInfo(p_event, 'ERROR');
133 RETURN 'ERROR';
134 WHEN OTHERS THEN
135 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_PERSON_POST', p_event.getEventName(), p_subscription_guid);
136 WF_EVENT.setErrorInfo(p_event, 'ERROR');
137 RETURN 'ERROR';
138
139 END;
140
141 FUNCTION CREATE_PARTY_SITE_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
142 CURSOR c1 IS
143 SELECT party_id
144 FROM HZ_PARTY_SITES
145 WHERE party_site_id = p_event.GetValueForParameter('PARTY_SITE_ID');
146 l_party_id NUMBER;
147 p_return_status VARCHAR2(1);
148 BEGIN
149 OPEN c1;
150 FETCH c1 INTO l_party_id;
151 CLOSE c1;
152 PROCESS_RTTAP_ACCOUNT(l_party_id,p_return_status);
153 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156 RETURN 'SUCCESS';
157 EXCEPTION
158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_PARTY_SITE_POST', p_event.getEventName(), p_subscription_guid);
160 WF_EVENT.setErrorInfo(p_event, 'ERROR');
161 RETURN 'ERROR';
162 WHEN OTHERS THEN
163 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_PARTY_SITE_POST', p_event.getEventName(), p_subscription_guid);
164 WF_EVENT.setErrorInfo(p_event, 'ERROR');
165 RETURN 'ERROR';
166
167 END;
168
169 FUNCTION UPDATE_PARTY_SITE_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
170 CURSOR c1 IS
171 SELECT party_id
172 FROM HZ_PARTY_SITES
173 WHERE party_site_id = p_event.GetValueForParameter('PARTY_SITE_ID');
174 l_party_id NUMBER;
175 p_return_status VARCHAR2(1);
176 BEGIN
177 OPEN c1;
178 FETCH c1 INTO l_party_id;
179 CLOSE c1;
180 PROCESS_RTTAP_ACCOUNT(l_party_id,p_return_status);
181 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END IF;
184 RETURN 'SUCCESS';
185 EXCEPTION
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_PARTY_SITE_POST', p_event.getEventName(), p_subscription_guid);
188 WF_EVENT.setErrorInfo(p_event, 'ERROR');
189 RETURN 'ERROR';
190 WHEN OTHERS THEN
191 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_PARTY_SITE_POST', p_event.getEventName(), p_subscription_guid);
192 WF_EVENT.setErrorInfo(p_event, 'ERROR');
193 RETURN 'ERROR';
194
195 END;
196
197
198 FUNCTION CREATE_CONTACT_POINT_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
199 CURSOR c1 IS
200 SELECT owner_table_name,owner_table_id
201 FROM hz_contact_points
202 WHERE contact_point_id = p_event.GetValueForParameter('CONTACT_POINT_ID')
203 AND primary_flag = 'Y'
204 AND contact_point_type ='PHONE'
205 AND status <>'I';
206 CURSOR c2(p_party_site_id NUMBER) IS
207 SELECT party_id
208 FROM hz_party_sites
209 WHERE party_site_id= p_party_site_id;
210
211 l_owner_table_name VARCHAR2(30);
212 l_owner_table_id NUMBER;
213 l_party_id NUMBER;
214 p_return_status VARCHAR2(1);
215 BEGIN
216 OPEN c1;
217 FETCH c1 INTO l_owner_table_name,l_owner_table_id;
218 If (c1%NOTFOUND) THEN
219 CLOSE c1;
220 RETURN 'SUCCESS';
221 END IF;
222 Close c1;
223
224 IF l_owner_table_name= 'HZ_PARTY_SITES' THEN
225 OPEN C2(l_owner_table_id);
226 FETCH C2 INTO l_party_id;
227 CLOSE C2;
228 PROCESS_RTTAP_ACCOUNT(l_party_id,p_return_status);
229 ELSIF l_owner_table_name = 'HZ_PARTIES' THEN
230 PROCESS_RTTAP_ACCOUNT(l_owner_table_id,p_return_status);
231 END IF;
232 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
234 END IF;
235 RETURN 'SUCCESS';
236 EXCEPTION
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_CONTACT_POINT_POST', p_event.getEventName(), p_subscription_guid);
239 WF_EVENT.setErrorInfo(p_event, 'ERROR');
240 RETURN 'ERROR';
241 WHEN OTHERS THEN
242 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'CREATE_CONTACT_POINT_POST', p_event.getEventName(), p_subscription_guid);
243 WF_EVENT.setErrorInfo(p_event, 'ERROR');
244 RETURN 'ERROR';
245
246 END CREATE_CONTACT_POINT_POST;
247
248 FUNCTION UPDATE_CONTACT_POINT_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
249 CURSOR C1 IS
250 SELECT owner_table_name,owner_table_id
251 FROM hz_contact_points
252 WHERE contact_point_id = p_event.GetValueForParameter('CONTACT_POINT_ID')
253 AND primary_flag = 'Y'
254 AND contact_point_type ='PHONE'
255 AND status <>'I';
256 CURSOR C2(p_party_site_id NUMBER) IS
257 SELECT party_id
258 FROM hz_party_sites
259 WHERE party_site_id= p_party_site_id;
260
261 l_owner_table_name VARCHAR2(30);
262 l_owner_table_id NUMBER;
263 l_party_id NUMBER;
264 p_return_status VARCHAR2(1);
265 BEGIN
266 OPEN c1;
267 FETCH c1 into l_owner_table_name,l_owner_table_id;
268 IF (c1%NOTFOUND) then
269 CLOSE c1;
270 RETURN 'SUCCESS';
271 END IF;
272 CLOSE c1;
273
274 IF l_owner_table_name= 'HZ_PARTY_SITES' THEN
275 OPEN C2(l_owner_table_id);
276 FETCH C2 INTO l_party_id;
277 CLOSE C2;
278 PROCESS_RTTAP_ACCOUNT(l_party_id,p_return_status);
279 ELSIF l_owner_table_name = 'HZ_PARTIES' THEN
280 PROCESS_RTTAP_ACCOUNT(l_owner_table_id,p_return_status);
281 END IF;
282 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285 RETURN 'SUCCESS';
286 EXCEPTION
287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
288 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_CONTACT_POINT_POST', p_event.getEventName(), p_subscription_guid);
289 WF_EVENT.setErrorInfo(p_event, 'ERROR');
290 RETURN 'ERROR';
291 WHEN OTHERS THEN
292 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_CONTACT_POINT_POST', p_event.getEventName(), p_subscription_guid);
293 WF_EVENT.setErrorInfo(p_event, 'ERROR');
294 RETURN 'ERROR';
295
296 END UPDATE_CONTACT_POINT_POST;
297
298 FUNCTION UPDATE_LOCATION_POST ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2 IS
299
300 CURSOR C2 IS
301 SELECT DISTINCT party_id
302 FROM AS_PARTY_ADDRESSES_V
303 WHERE location_id = p_event.GetValueForParameter('LOCATION_ID') ;
304 p_return_status VARCHAR2(1);
305 BEGIN
306 FOR cur_party IN C2 LOOP
307 PROCESS_RTTAP_ACCOUNT(cur_party.party_id,p_return_status);
308 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310 END IF;
311 END LOOP;
312 RETURN 'SUCCESS';
313 EXCEPTION
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_LOCATION_POST', p_event.getEventName(), p_subscription_guid);
316 WF_EVENT.setErrorInfo(p_event, 'ERROR');
317 RETURN 'ERROR';
318 WHEN OTHERS THEN
319 WF_CORE.CONTEXT('AS_RTTAP_ACCOUNT', 'UPDATE_LOCATION_POST', p_event.getEventName(), p_subscription_guid);
320 WF_EVENT.setErrorInfo(p_event, 'ERROR');
321 RETURN 'ERROR';
322 END UPDATE_LOCATION_POST;
323
324 PROCEDURE RTTAP_WRAPPER(
325 p_party_id IN NUMBER,
326 X_Return_Status OUT NOCOPY VARCHAR2
327 )
328 IS
329
330
331 l_errbuf VARCHAR2(4000);
332 l_retcode VARCHAR2(255);
333 l_msg_count NUMBER;
334 l_msg_data VARCHAR2(255);
335 l_trans_rec JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
336 l_WinningTerrMember_tbl JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
337 l_return_status VARCHAR2(1);
338
339 BEGIN
340
341 G_PARTY_ID := p_party_id;
342
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344 IF FND_API.to_Boolean( FND_API.G_FALSE ) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 -- Initialize API return status to SUCCESS
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 IF NVL(FND_PROFILE.Value('AS_ENABLE_CUST_ONLINE_TAP'), 'N') <> 'Y' THEN
352 /*------------------------------------------------------+
353 | If REALTIME TAP profile is turned on there is NO need
354 | to insert into changed accounts since the ACCOUNT is
355 | processed immediately.
356 +-------------------------------------------------------*/
357 INSERT INTO AS_CHANGED_ACCOUNTS_ALL
358 ( customer_id,
359 address_id,
360 last_update_date,
361 last_updated_by,
362 creation_date,
363 created_by,
364 last_update_login,
365 change_type )
366 SELECT G_PARTY_ID,
367 NULL,
368 SYSDATE,
369 0,
370 SYSDATE,
371 0,
372 0,
373 'ACCOUNT'
374 FROM DUAL
375 WHERE NOT EXISTS
376 ( SELECT 'X'
377 FROM AS_CHANGED_ACCOUNTS_ALL ACC
378 WHERE ACC.customer_id = G_PARTY_ID
379 AND ACC.lead_id IS NULL
380 AND ACC.sales_lead_id IS NULL
381 AND ACC.request_id IS NULL );
382
383 ELSE
384 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
385
386
387 l_trans_rec.trans_object_id1 := jtf_terr_number_list(G_PARTY_ID);
388 l_trans_rec.trans_object_id2 := jtf_terr_number_list(null);
389 l_trans_rec.trans_object_id3 := jtf_terr_number_list(null);
390 l_trans_rec.trans_object_id4 := jtf_terr_number_list(null);
391 l_trans_rec.trans_object_id5 := jtf_terr_number_list(null);
392 l_trans_rec.txn_date := jtf_terr_date_list(null);
393 JTY_ASSIGN_REALTIME_PUB.get_winners(
394 p_api_version_number => 1.0,
395 p_init_msg_list => FND_API.G_FALSE,
396 p_source_id => -1001,
397 p_trans_id => -1002,
398 p_mode => 'REAL TIME:RESOURCE',
399 p_param_passing_mechanism => 'PBR',
400 p_program_name => 'SALES/ACCOUNT PROGRAM',
401 p_trans_rec => l_trans_rec,
402 p_name_value_pair => null,
403 p_role => null,
404 p_resource_type => null,
405 x_return_status => l_return_status,
406 x_msg_count => l_msg_count,
407 x_msg_data => l_msg_data,
408 x_winners_rec => l_WinningTerrMember_tbl);
409 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
410 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
411 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
412 FND_MSG_PUB.Count_And_Get
413 ( p_count => l_msg_count,
414 p_data => l_msg_data
415 );
416 AS_UTILITY_PVT.Get_Messages(l_msg_count, l_msg_data);
417 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_msg_data, 'ERROR');
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 End If;
420 If (l_WinningTerrMember_tbl.resource_id.count > 0) THEN
421 FOR i IN l_WinningTerrMember_tbl.terr_id.FIRST .. l_WinningTerrMember_tbl.terr_id.LAST LOOP
422 AS_GAR.LOG(G_ENTITY || 'Trans Object ID : ' || l_WinningTerrMember_tbl.trans_object_id(i) ||
423 'Trans Detail Object ID : ' || l_WinningTerrMember_tbl.trans_detail_object_id(i) ||
424 'Terr ID : ' || l_WinningTerrMember_tbl.terr_id(i) || ' Terr Name : ' || l_WinningTerrMember_tbl.terr_name(i) ||
425 ' Resource ID : ' || l_WinningTerrMember_tbl.resource_id(i) ||
426 ' Resource TYPE : ' || l_WinningTerrMember_tbl.resource_type(i));
427 END LOOP;
428 -- Explode GROUPS if any inside winners
429 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
430 AS_RTTAP_ACCOUNT.EXPLODE_GROUPS_ACCOUNTS(
431 x_errbuf => l_errbuf,
432 x_retcode => l_retcode,
433 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
434 x_return_status => l_return_status);
435
436 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
437 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
438
439 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
440 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442 End If;
443
444 -- Explode TEAMS if any inside winners
445 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
446 AS_RTTAP_ACCOUNT.EXPLODE_TEAMS_ACCOUNTS(
447 x_errbuf => l_errbuf,
448 x_retcode => l_retcode,
449 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
450 x_return_status => l_return_status);
451
452 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
453 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
454
455 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
456 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 End If;
459
460 -- Set team leader for ACCOUNTs
461 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
462 AS_RTTAP_ACCOUNT.SET_TEAM_LEAD_ACCOUNTS(
463 x_errbuf => l_errbuf,
464 x_retcode => l_retcode,
465 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
466 x_return_status => l_return_status);
467
468 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
469 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
470
471 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
472 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 End If;
475
476 -- Insert into ACCOUNT Accesses from Winners
477 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
478 AS_RTTAP_ACCOUNT.INSERT_ACCESSES_ACCOUNTS(
479 x_errbuf => l_errbuf,
480 x_retcode => l_retcode,
481 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
482 x_return_status => l_return_status);
483
484 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
485 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
486
487 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
488 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 End If;
491
492 -- Insert into territory Accesses
493 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
494 AS_RTTAP_ACCOUNT.INSERT_TERR_ACCESSES_ACCOUNTS(
495 x_errbuf => l_errbuf,
496 x_retcode => l_retcode,
497 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
498 x_return_status => l_return_status);
499
500 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
501 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
502
503 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
504 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 End If;
507 End If;
508
509 -- Remove (soft delete) records in access table that are not qualified
510 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
511 AS_RTTAP_ACCOUNT.PERFORM_ACCOUNT_CLEANUP(
512 x_errbuf => l_errbuf,
513 x_retcode => l_retcode,
514 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
515 x_return_status => l_return_status);
516
517 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
518 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
519
520 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
521 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523 End If;
524
525 END IF;
526
527 COMMIT;
528
529 EXCEPTION
530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 WHEN OTHERS THEN
533 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 END RTTAP_WRAPPER;
536
537
538 /************************** Start Explode Teams ACCOUNTs ******************/
539 PROCEDURE EXPLODE_TEAMS_ACCOUNTS(
540 x_errbuf OUT NOCOPY VARCHAR2,
541 x_retcode OUT NOCOPY VARCHAR2,
542 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
543 x_return_status OUT NOCOPY VARCHAR2)
544 IS
545
546 /*-------------------------------------------------------------------------+
547 | LOGIC
548 |
549 | A RESOURCE team can be comprised OF resources who belong TO one OR more
550 | GROUPS OF resources.
551 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
552 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
553 | WHERE they play a similar ROLE.
554 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
555 | are a RESOURCE within a team.
556 | INSERT these members INTO winners IF they are NOT already IN winners.
557 +-------------------------------------------------------------------------*/
558
559 l_errbuf VARCHAR2(4000);
560 l_retcode VARCHAR2(255);
561 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
562 TYPE vchar_list is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER;
563
564
565 l_resource_id num_list;
566 l_group_id num_list;
567 l_person_id num_list;
568 l_resource_type vchar_list;
569
570
571 BEGIN
572 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574 /* Get resources within a resource team */
575 /** Note
576 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
577 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
578 **/
579 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
580 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
581 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
582 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_TEAM' THEN
583
584 SELECT resource_id, group_id,person_id, DECODE(resource_category,'PARTY','RS_PARTY',
585 'PARTNER','RS_PARTNER',
586 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN') resource_type
587 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id,l_resource_type
588 FROM
589 (
590 SELECT TM.team_resource_id resource_id,
591 TM.person_id person_id2,
592 MIN(G.group_id)group_id,
593 MIN(T.team_id) team_id,
594 TRES.CATEGORY resource_category,
595 MIN(TRES.source_id) person_id
596 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
597 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
598 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
599 (
600 SELECT m.group_id group_id, m.resource_id resource_id
601 FROM jtf_rs_group_members m,
602 jtf_rs_groups_b g,
603 jtf_rs_group_usages u,
604 jtf_rs_role_relations rr,
605 jtf_rs_roles_b r,
606 jtf_rs_resource_extns res
607 WHERE m.group_id = g.group_id
608 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
609 AND NVL(g.end_date_active,SYSDATE)
610 AND u.group_id = g.group_id
611 AND u.usage IN ('SALES','PRM')
612 AND m.group_member_id = rr.role_resource_id
613 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
614 AND rr.delete_flag <> 'Y'
615 AND SYSDATE BETWEEN rr.start_date_active
616 AND NVL(rr.end_date_active,SYSDATE)
617 AND rr.role_id = r.role_id
618 AND r.role_type_code
619 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
620 AND r.active_flag = 'Y'
621 AND res.resource_id = m.resource_id
622 AND res.CATEGORY IN ('EMPLOYEE','PARTY','PARTNER')
623 ) G
624 WHERE tm.team_id = t.team_id
625 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
626 AND NVL(t.end_date_active,SYSDATE)
627 AND tu.team_id = t.team_id
628 AND tu.usage IN ('SALES','PRM')
629 AND tm.team_member_id = trr.role_resource_id
630 AND tm.delete_flag <> 'Y'
631 AND tm.resource_type = 'INDIVIDUAL'
632 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
633 AND trr.delete_flag <> 'Y'
634 AND SYSDATE BETWEEN trr.start_date_active
635 AND NVL(trr.end_date_active,SYSDATE)
636 AND trr.role_id = tr.role_id
637 AND tr.role_type_code IN
638 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
639 AND tr.active_flag = 'Y'
640 AND tres.resource_id = tm.team_resource_id
641 AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
642 AND tm.team_resource_id = g.resource_id
643 GROUP BY tm.team_resource_id,
644 tm.person_id,
645 tres.CATEGORY,
646 tres.source_id
647 UNION ALL
648 SELECT MIN(m.resource_id) resource_id,
649 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
650 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
651 MIN(res.source_id) person_id
652 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
653 jtf_rs_group_usages u, jtf_rs_role_relations rr,
654 jtf_rs_roles_b r, jtf_rs_resource_extns res,
655 (
656 SELECT tm.team_resource_id group_id,
657 t.team_id team_id
658 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
659 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
660 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
661 WHERE tm.team_id = t.team_id
662 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
663 AND NVL(t.end_date_active,SYSDATE)
664 AND tu.team_id = t.team_id
665 AND tu.usage IN ('SALES','PRM')
666 AND tm.team_member_id = trr.role_resource_id
667 AND tm.delete_flag <> 'Y'
668 AND tm.resource_type = 'GROUP'
669 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
670 AND trr.delete_flag <> 'Y'
671 AND SYSDATE BETWEEN trr.start_date_active
672 AND NVL(trr.end_date_active,SYSDATE)
673 AND trr.role_id = tr.role_id
674 AND tr.role_type_code IN
675 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
676 AND tr.active_flag = 'Y'
677 AND tres.resource_id = tm.team_resource_id
678 AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
679 ) jtm
680 WHERE m.group_id = g.group_id
681 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
682 AND NVL(g.end_date_active,SYSDATE)
683 AND u.group_id = g.group_id
684 AND u.usage IN ('SALES','PRM')
685 AND m.group_member_id = rr.role_resource_id
686 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
687 AND rr.delete_flag <> 'Y'
688 AND SYSDATE BETWEEN rr.start_date_active
689 AND NVL(rr.end_date_active,SYSDATE)
690 AND rr.role_id = r.role_id
691 AND r.role_type_code IN
692 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
693 AND r.active_flag = 'Y'
694 AND res.resource_id = m.resource_id
695 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
696 AND jtm.group_id = g.group_id
697 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
698
699 WHERE j.team_id = p_WinningTerrMember_tbl.resource_id(l_index);
700 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS ||
701 AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT ||' FOR TEAM '||p_WinningTerrMember_tbl.resource_id(l_index));
702
703 IF l_resource_id.COUNT > 0 THEN
704 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
705 /* No need to Check to see if it is already part of
706 p_WinningTerrMember_tbl because this will be slow,
707 So we insert into p_WinningTerrMember_tbl directly*/
708 ---IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
709 p_WinningTerrMember_tbl.resource_id.EXTEND;
710 p_WinningTerrMember_tbl.group_id.EXTEND;
711 p_WinningTerrMember_tbl.person_id.EXTEND;
712 p_WinningTerrMember_tbl.resource_type.EXTEND;
713 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
714 p_WinningTerrMember_tbl.terr_id.EXTEND;
715 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
716 p_WinningTerrMember_tbl.trans_detail_object_id.EXTEND;
717 p_WinningTerrMember_tbl.org_id.EXTEND;
718 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_id(i);
719 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_group_id(i);
720 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.person_id.COUNT ) := l_person_id(i);
721 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_type(i);
722 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.full_access_flag(l_index);
723 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
724 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_PARTY_ID;
725 p_WinningTerrMember_tbl.trans_detail_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := p_WinningTerrMember_tbl.trans_detail_object_id(l_index);
726 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.org_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
727 ---END IF;
728 END LOOP;
729 END IF;
730 END IF;
731 END LOOP;
732 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
733 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
734 EXCEPTION
735 WHEN others THEN
736 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
737 x_errbuf := SQLERRM;
738 x_retcode := SQLCODE;
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 END EXPLODE_TEAMS_ACCOUNTS;
741 /************************** End Explode Teams ACCOUNTs ******************/
742
743 /************************** Start Explode Groups ACCOUNTs ******************/
744 PROCEDURE EXPLODE_GROUPS_ACCOUNTS(
745 x_errbuf OUT NOCOPY VARCHAR2,
746 x_retcode OUT NOCOPY VARCHAR2,
747 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
748 x_return_status OUT NOCOPY VARCHAR2)
749 IS
750 -------------RS_GROUP---------
751 /*-------------------------------------------------------------------------+
752 | PROGRAM LOGIC
753 |
754 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
755 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
756 | winners IF they are NOT already IN winners.
757 +-------------------------------------------------------------------------*/
758 l_errbuf VARCHAR2(4000);
759 l_retcode VARCHAR2(255);
760
761 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
762 TYPE vchar_list is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER;
763 l_resource_id num_list;
764 l_group_id num_list;
765 l_person_id num_list;
766 l_resource_type vchar_list;
767
768 BEGIN
769 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
770 x_return_status := FND_API.G_RET_STS_SUCCESS;
771 /* Get resources within a resource group */
772 /** Note
773 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
774 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
775 **/
776 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
777 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
778 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
779 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_GROUP' THEN
780
781 SELECT resource_id, group_id, person_id,DECODE(resource_category,'PARTY','RS_PARTY',
782 'PARTNER','RS_PARTNER',
783 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN') resource_type
784 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id,l_resource_type
785 FROM
786 (
787 SELECT min(m.resource_id) resource_id,
788 res.category resource_category,
789 m.group_id group_id, min(res.source_id) person_id
790 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
791 jtf_rs_group_usages u, jtf_rs_role_relations rr,
792 jtf_rs_roles_b r, jtf_rs_resource_extns res
793 WHERE m.group_id = g.group_id
794 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
795 AND NVL(g.end_date_active,SYSDATE)
796 AND u.group_id = g.group_id
797 AND u.usage IN ('SALES','PRM')
798 AND m.group_member_id = rr.role_resource_id
799 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
800 AND rr.role_id = r.role_id
801 AND rr.delete_flag <> 'Y'
802 AND SYSDATE BETWEEN rr.start_date_active
803 AND NVL(rr.end_date_active,SYSDATE)
804 AND r.role_type_code IN
805 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
806 AND r.active_flag = 'Y'
807 AND res.resource_id = m.resource_id
808 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
809 GROUP BY m.group_member_id, m.resource_id, m.person_id,
810 m.group_id, res.CATEGORY) j
811 WHERE j.group_id = p_WinningTerrMember_tbl.resource_id(l_index);
812 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS ||
813 AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT ||' FOR GROUP '||p_WinningTerrMember_tbl.resource_id(l_index));
814 IF l_resource_id.COUNT > 0 THEN
815 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
816 /* No need to Check to see if it is already part of
817 p_WinningTerrMember_tbl because this will be slow,
818 So we insert into p_WinningTerrMember_tbl directly*/
819 --IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
820 p_WinningTerrMember_tbl.resource_id.EXTEND;
821 p_WinningTerrMember_tbl.group_id.EXTEND;
822 p_WinningTerrMember_tbl.person_id.EXTEND;
823 p_WinningTerrMember_tbl.resource_type.EXTEND;
824 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
825 p_WinningTerrMember_tbl.terr_id.EXTEND;
826 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
827 p_WinningTerrMember_tbl.trans_detail_object_id.EXTEND;
828 p_WinningTerrMember_tbl.org_id.EXTEND;
829 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_id(i);
830 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_group_id(i);
831 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.person_id.COUNT ) := l_person_id(i);
832 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_type(i);
833 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.full_access_flag(l_index);
834 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
835 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_PARTY_ID;
836 p_WinningTerrMember_tbl.trans_detail_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := p_WinningTerrMember_tbl.trans_detail_object_id(l_index);
837 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.org_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
838 --END IF;
839 END LOOP;
840 END IF;
841 END IF;
842 END LOOP;
843 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
844 COMMIT;
845 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
846 EXCEPTION
847 WHEN OTHERS THEN
848 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
849 x_errbuf := SQLERRM;
850 x_retcode := SQLCODE;
851 x_return_status := FND_API.G_RET_STS_ERROR;
852 END EXPLODE_GROUPS_ACCOUNTS;
853
854 PROCEDURE SET_TEAM_LEAD_ACCOUNTS(
855 x_errbuf OUT NOCOPY VARCHAR2,
856 x_retcode OUT NOCOPY VARCHAR2,
857 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
858 x_return_status OUT NOCOPY VARCHAR2) IS
859
860 src_id NUMBER:= 0;
861 BEGIN
862 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
863 x_return_status := FND_API.G_RET_STS_SUCCESS;
864 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
865 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
866 IF p_WinningTerrMember_tbl.resource_type(l_index) IN('RS_EMPLOYEE','RS_PARTY','RS_PARTNER')THEN
867 SELECT NVL(source_id,0) INTO src_id FROM JTF_RS_RESOURCE_EXTNS RES WHERE resource_id = p_WinningTerrMember_tbl.resource_id(l_index);
868 AS_GAR.LOG(G_ENTITY || G_PARTY_ID ||' : BEFORE UPDATE :'|| '::' || 'RESOURCE/GROUP/RESOURCE_TYPE/SOURCE_ID::' || p_WinningTerrMember_tbl.resource_id(l_index)
869 || '/' || p_WinningTerrMember_tbl.group_id(l_index) || '/' || p_WinningTerrMember_tbl.resource_type(l_index) || '/' || src_id);
870 ---IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' THEN
871 UPDATE AS_ACCESSES_ALL ACC
872 SET object_version_number = nvl(object_version_number,0) + 1,
873 ACC.last_update_date = SYSDATE,
874 ACC.last_updated_by = FND_GLOBAL.USER_ID,
875 ACC.last_update_login = FND_GLOBAL.USER_ID,
876 ACC.team_leader_flag = NVL(p_WinningTerrMember_tbl.full_access_flag(l_index),'N')
877 WHERE ACC.customer_id = G_PARTY_ID
878 AND ACC.lead_id IS NULL
879 AND ACC.sales_lead_id IS NULL
880 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
881 AND NVL(ACC.sales_group_id,-777) = NVL(p_WinningTerrMember_tbl.group_id(l_index),-777)
882 AND NVL(ACC.team_leader_flag,'N') <> NVL(p_WinningTerrMember_tbl.full_access_flag(l_index),'N');
883 ---END IF;
884 END IF;
885 END LOOP;
886 END IF;
887 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
888 EXCEPTION
889 WHEN OTHERS THEN
890 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
891 x_errbuf := SQLERRM;
892 x_retcode := SQLCODE;
893 x_return_status := FND_API.G_RET_STS_ERROR;
894 RAISE;
895 END SET_TEAM_LEAD_ACCOUNTS;
896
897
898 PROCEDURE INSERT_ACCESSES_ACCOUNTS(
899 x_errbuf OUT NOCOPY VARCHAR2,
900 x_retcode OUT NOCOPY VARCHAR2,
901 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
902 x_return_status OUT NOCOPY VARCHAR2) IS
903 BEGIN
904 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
907 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
908 AS_GAR.LOG(G_ENTITY || G_PARTY_ID ||' : BEFORE INSERT :'|| '::' || 'RESOURCE/GROUP/RESOURCE_TYPE ' || p_WinningTerrMember_tbl.resource_id(l_index)
909 || '/' || p_WinningTerrMember_tbl.group_id(l_index) || '/' || p_WinningTerrMember_tbl.resource_type(l_index) );
910
911 --added inline view in the select clause of Insert statement to fetch the salesforce role code for Employee resource --fix for bug 5869095
912
913 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' AND p_WinningTerrMember_tbl.group_id(l_index) IS NOT NULL THEN
914 INSERT INTO AS_ACCESSES_ALL
915 (access_id ,
916 last_update_date ,
917 last_updated_by,
918 creation_date ,
919 created_by ,
920 last_update_login,
921 access_type ,
922 freeze_flag,
923 reassign_flag,
924 team_leader_flag ,
925 customer_id ,
926 address_id ,
927 salesforce_id ,
928 person_id ,
929 sales_group_id,
930 created_by_tap_flag,
931 salesforce_role_code)
932 ---- JTY need to pass org_id as well
933 SELECT AS_ACCESSES_S.NEXTVAL access_id,
934 last_update_date ,
935 last_updated_by,
936 creation_date ,
937 created_by ,
938 last_update_login,
939 access_type ,
940 freeze_flag,
941 reassign_flag,
942 team_leader_flag ,
943 customer_id ,
944 address_id ,
945 salesforce_id ,
946 person_id ,
947 sales_group_id,
948 created_by_tap_flag,
949 salesforce_role_code
950 FROM
951 (SELECT SYSDATE last_update_date,
952 FND_GLOBAL.USER_ID last_updated_by,
953 SYSDATE creation_date,
954 FND_GLOBAL.USER_ID created_by,
955 FND_GLOBAL.USER_ID last_update_login,
956 'Online' access_type,
957 'N' freeze_flag,
958 'N' reassign_flag,
959 DECODE(p_WinningTerrMember_tbl.full_access_flag(l_index),'Y','Y','N') team_leader_flag,
960 G_PARTY_ID customer_id,
961 p_WinningTerrMember_tbl.trans_detail_object_id(l_index) address_id,
962 p_WinningTerrMember_tbl.resource_id(l_index) salesforce_id,
963 (SELECT source_id FROM JTF_RS_RESOURCE_EXTNS RES WHERE RES.resource_id = p_WinningTerrMember_tbl.resource_id(l_index)) person_id,
964 p_WinningTerrMember_tbl.group_id(l_index) sales_group_id,
965 'Y' created_by_tap_flag
966 FROM DUAL
967 WHERE NOT EXISTS
968 ( SELECT NULL FROM AS_ACCESSES_ALL ACC
969 WHERE ACC.customer_id = G_PARTY_ID
970 AND ACC.lead_id IS NULL
971 AND ACC.sales_lead_id IS NULL
972 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
973 AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index) ) ) asa,
974 ( SELECT USERS.EMPLOYEE_ID EMPLOYEE_ID ,
975 VAL.PROFILE_OPTION_VALUE SALESFORCE_ROLE_CODE
976 FROM FND_PROFILE_OPTION_VALUES VAL,
977 FND_PROFILE_OPTIONS OPTIONS,
978 FND_USER USERS
979 WHERE VAL.LEVEL_ID = 10004
980 AND USERS.EMPLOYEE_ID is not null
981 AND VAL.PROFILE_OPTION_VALUE is not null
982 AND USERS.USER_ID = VAL.LEVEL_VALUE
983 AND VAL.PROFILE_OPTION_VALUE is not null
984 AND OPTIONS.PROFILE_OPTION_ID = VAL.PROFILE_OPTION_ID
985 AND OPTIONS.APPLICATION_ID = VAL.APPLICATION_ID
986 AND OPTIONS.PROFILE_OPTION_NAME = 'AS_DEF_CUST_ST_ROLE') prf
987 WHERE asa.PERSON_ID = prf.EMPLOYEE_ID (+);
988 ELSIF p_WinningTerrMember_tbl.resource_type(l_index) IN ('RS_PARTY','RS_PARTNER') THEN
989 INSERT INTO AS_ACCESSES_ALL
990 (access_id ,
991 last_update_date ,
992 last_updated_by,
993 creation_date ,
994 created_by ,
995 last_update_login,
996 access_type ,
997 freeze_flag,
998 reassign_flag,
999 team_leader_flag ,
1000 customer_id ,
1001 address_id ,
1002 salesforce_id ,
1003 person_id ,
1004 sales_group_id,
1005 created_by_tap_flag,
1006 partner_customer_id,
1007 partner_cont_party_id,org_id)
1008 SELECT AS_ACCESSES_S.NEXTVAL,
1009 SYSDATE,
1010 FND_GLOBAL.USER_ID,
1011 SYSDATE,
1012 FND_GLOBAL.USER_ID,
1013 FND_GLOBAL.USER_ID,
1014 'Online',
1015 'N',
1016 'N',
1017 DECODE(p_WinningTerrMember_tbl.full_access_flag(l_index),'Y','Y','N'),
1018 G_PARTY_ID,
1019 p_WinningTerrMember_tbl.trans_detail_object_id(l_index),
1020 p_WinningTerrMember_tbl.resource_id(l_index),
1021 NULL,
1022 p_WinningTerrMember_tbl.group_id(l_index),
1023 'Y',
1024 DECODE(p_WinningTerrMember_tbl.resource_type(l_index),'RS_PARTNER',(SELECT source_id FROM JTF_RS_RESOURCE_EXTNS RES WHERE resource_id = p_WinningTerrMember_tbl.resource_id(l_index)),NULL),
1025 DECODE(p_WinningTerrMember_tbl.resource_type(l_index),'RS_PARTY',(SELECT source_id FROM JTF_RS_RESOURCE_EXTNS RES WHERE resource_id = p_WinningTerrMember_tbl.resource_id(l_index)),NULL),
1026 p_WinningTerrMember_tbl.org_id(l_index)
1027 FROM DUAL
1028 WHERE NOT EXISTS
1029 ( SELECT NULL FROM AS_ACCESSES_ALL ACC
1030 WHERE ACC.customer_id = G_PARTY_ID
1031 AND ACC.lead_id IS NULL
1032 AND ACC.sales_lead_id IS NULL
1033 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
1034 AND NVL(ACC.sales_group_id,-777) = NVL(p_WinningTerrMember_tbl.group_id(l_index),-777) );
1035 END IF;
1036 END LOOP;
1037 END IF;
1038 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_END);
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1042 x_errbuf := SQLERRM;
1043 x_retcode := SQLCODE;
1044 x_return_status := FND_API.G_RET_STS_ERROR;
1045 END INSERT_ACCESSES_ACCOUNTS;
1046
1047 PROCEDURE INSERT_TERR_ACCESSES_ACCOUNTS(
1048 x_errbuf OUT NOCOPY VARCHAR2,
1049 x_retcode OUT NOCOPY VARCHAR2,
1050 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
1051 x_return_status OUT NOCOPY VARCHAR2) IS
1052 BEGIN
1053 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1054 x_return_status := FND_API.G_RET_STS_SUCCESS;
1055 /*------------------------------------------------------------------------------+
1056 | we are deleting all rows for the entity from as_territory_accesses prior to
1057 | inserting into it because the logic for removing only certain terr_id/access_id
1058 | combinations is very complex and could be slow..
1059 +-------------------------------------------------------------------------------*/
1060 DELETE FROM AS_TERRITORY_ACCESSES TACC
1061 WHERE TACC.access_id IN
1062 (SELECT ACC.access_id
1063 FROM AS_ACCESSES_ALL ACC
1064 WHERE customer_id = G_PARTY_ID
1065 AND lead_id IS NULL
1066 AND sales_lead_id IS NULL);
1067 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
1068 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
1069 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' THEN
1070 INSERT INTO AS_TERRITORY_ACCESSES
1071 ( access_id,
1072 territory_id,
1073 user_territory_id,
1074 last_update_date,
1075 last_updated_by,
1076 creation_date,
1077 created_by,
1078 last_update_login )
1079 SELECT
1080 ACC.access_id,
1081 p_WinningTerrMember_tbl.terr_id(l_index),
1082 p_WinningTerrMember_tbl.terr_id(l_index),
1083 SYSDATE,
1084 FND_GLOBAL.USER_ID,
1085 SYSDATE,
1086 FND_GLOBAL.USER_ID,
1087 FND_GLOBAL.USER_ID
1088 FROM AS_ACCESSES_ALL ACC
1089 WHERE ACC.customer_id = G_PARTY_ID
1090 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
1091 AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index)
1092 AND NOT EXISTS ( SELECT 'Y'
1093 FROM AS_TERRITORY_ACCESSES TACC
1094 WHERE TACC.access_id = ACC.access_id
1095 AND TACC.territory_id = p_WinningTerrMember_tbl.terr_id(l_index)) ;
1096 ELSIF p_WinningTerrMember_tbl.resource_type(l_index) IN ('RS_PARTY','RS_PARTNER') THEN
1097 INSERT INTO AS_TERRITORY_ACCESSES
1098 ( access_id,
1099 territory_id,
1100 user_territory_id,
1101 last_update_date,
1102 last_updated_by,
1103 creation_date,
1104 created_by,
1105 last_update_login )
1106 SELECT ACC.access_id,
1107 p_WinningTerrMember_tbl.terr_id(l_index),
1108 p_WinningTerrMember_tbl.terr_id(l_index),
1109 SYSDATE,
1110 FND_GLOBAL.USER_ID,
1111 SYSDATE,
1112 FND_GLOBAL.USER_ID,
1113 FND_GLOBAL.USER_ID
1114 FROM AS_ACCESSES_ALL ACC
1115 WHERE ACC.customer_id = G_PARTY_ID
1116 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
1117 AND NVL(ACC.sales_group_id,-777) = NVL(p_WinningTerrMember_tbl.group_id(l_index),-777)
1118 AND (ACC.partner_customer_id IS NOT NULL OR ACC.partner_cont_party_id IS NOT NULL )
1119 AND NOT EXISTS ( SELECT 'Y'
1120 FROM AS_TERRITORY_ACCESSES TACC
1121 WHERE TACC.access_id = ACC.access_id
1122 AND TACC.territory_id = p_WinningTerrMember_tbl.terr_id(l_index)) ;
1123 END IF;
1124 END LOOP;
1125 END IF;
1126
1127 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1131 x_errbuf := SQLERRM;
1132 x_retcode := SQLCODE;
1133 x_return_status := FND_API.G_RET_STS_ERROR;
1134 END INSERT_TERR_ACCESSES_ACCOUNTS;
1135
1136 PROCEDURE PERFORM_ACCOUNT_CLEANUP(
1137 x_errbuf OUT NOCOPY VARCHAR2,
1138 x_retcode OUT NOCOPY VARCHAR2,
1139 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
1140 x_return_status OUT NOCOPY VARCHAR2) IS
1141
1142 TYPE access_type IS TABLE OF NUMBER;
1143 l_access_rec_id access_type;
1144
1145 BEGIN
1146 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
1147 x_return_status := FND_API.G_RET_STS_SUCCESS;
1148 DELETE FROM AS_ACCESSES_ALL ACC
1149 WHERE customer_id = G_PARTY_ID
1150 AND lead_id IS NULL
1151 AND sales_lead_id IS NULL
1152 AND NVL(freeze_flag, 'N') <> 'Y'
1153 AND SALESFORCE_ID||NVL(SALES_GROUP_ID,-777) NOT IN (
1154 SELECT RESTAB.RES||NVL(GRPTAB.GRP,-777) FROM
1155 (SELECT rownum ROW_NUM,A.COLUMN_VALUE RES FROM TABLE(CAST(p_WinningTerrMember_tbl.resource_id AS jtf_terr_number_list)) a) RESTAB,
1156 (SELECT rownum ROW_NUM,b.COLUMN_VALUE GRP FROM TABLE(CAST(p_WinningTerrMember_tbl.group_id AS jtf_terr_number_list)) b) GRPTAB
1157 WHERE RESTAB.ROW_NUM = GRPTAB.ROW_NUM
1158 ) ;
1159 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1160 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC, SQLERRM, TO_CHAR(SQLCODE));
1164 x_errbuf := SQLERRM;
1165 x_retcode := SQLCODE;
1166 x_return_status := FND_API.G_RET_STS_ERROR;
1167 END PERFORM_ACCOUNT_CLEANUP;
1168
1169
1170
1171 END AS_RTTAP_ACCOUNT;