[Home] [Help]
PACKAGE BODY: APPS.AS_GAR_QOT_PUB
Source
1 PACKAGE BODY AS_GAR_QOT_PUB AS
2 /* $Header: asxgrqtb.pls 120.22 2006/02/02 21:30 amagupta noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Start of Comments
6 ---------------------------------------------------------------------------
7 -- PACKAGE NAME: AS_GAR_QOT_PUB
8 -- ---------------------------------------------------------------------
9 -- PURPOSE
10 -- --------
11 -- This package contains procedures to accomplish each of the following
12 -- tasks:
13 -- 1: Call the JTY API to process data from JTY trans tables and
14 -- populate JTY winners.
15 -- 2: Merge and insert records from winners into AS_ACCESSES_ALL_ALL
16 -- 3: Soft Delete unwanted records from AS_ACCESSES_ALL_ALL
17 -- 4: Lead Owner Assignment
18 --
19 ---------------------------------------------------------------------------
20 /*-------------------------------------------------------------------------+
21 | PRIVATE CONSTANTS
22 +-------------------------------------------------------------------------*/
23 G_BUSINESS_EVENT CONSTANT VARCHAR2(60) := 'oracle.apps.as.tap.batch_mode';
24 DEADLOCK_DETECTED EXCEPTION;
25 PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
26 G_ENTITY CONSTANT VARCHAR2(10) := 'GAR::QOT::';
27 /*-------------------------------------------------------------------------*
28 | PRIVATE VARIABLES
29 *-------------------------------------------------------------------------*/
30 /*-------------------------------------------------------------------------*
31 | PRIVATE ROUTINES SPECIFICATION
32 *-------------------------------------------------------------------------*/
33 /*------------------------------------------------------------------------*
34 | PUBLIC ROUTINES
35 *------------------------------------------------------------------------*/
36
37 /************************** Start GAR Wrapper *****************************/
38 PROCEDURE GAR_WRAPPER(
39 errbuf OUT NOCOPY VARCHAR2,
40 retcode OUT NOCOPY VARCHAR2,
41 p_run_mode IN VARCHAR2,
42 p_debug_mode IN VARCHAR2,
43 p_trace_mode IN VARCHAR2,
44 p_worker_id IN VARCHAR2,
45 P_percent_analyzed IN NUMBER )
46 IS
47 l_terr_globals AS_GAR.TERR_GLOBALS;
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_errbuf VARCHAR2(4000);
51 l_retcode VARCHAR2(255);
52 l_sub_exist VARCHAR2(1);
53 l_return_status VARCHAR2(1);
54 l_target_type VARCHAR2(15);
55 l_status BOOLEAN;
56 l_proc VARCHAR2(30):= 'GAR_WRAPPER::';
57 BEGIN
58 AS_GAR.g_debug_flag := p_debug_mode;
59 IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
60 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
61
62 IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
63 l_target_type := 'TOTAL';
64 ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
65 l_target_type := 'INCREMENTAL';
66 END If;
67
68 -- Set the Global variables
69 AS_GAR.INIT(
70 p_run_mode,
71 p_worker_id,
72 l_terr_globals);
73
74 /* This inserts into Oppty winners */
75 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
76 JTY_ASSIGN_BULK_PUB.GET_WINNERS
77 ( p_api_version_number => 1.0,
78 p_init_msg_list => FND_API.G_TRUE,
79 p_source_id => -1001,
80 p_trans_id => -1105,
81 P_PROGRAM_NAME => 'SALES/QUOTE PROGRAM',
82 P_mode => l_target_type,
83 P_percent_analyzed => NVL(P_percent_analyzed,20),
84 p_worker_id => p_worker_id,
85 x_return_status => l_return_status,
86 x_msg_count => l_msg_count,
87 x_msg_data => l_msg_data,
88 ERRBUF => l_errbuf,
89 RETCODE => l_retcode);
90 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
91 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
92
93 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
94 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 End If;
97
98 COMMIT;
99
100 --Commenting the following call since Real Time API is not supporting other than rs_employee
101 -- Explode GROUPS if any inside winners
102 /* AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
103 AS_GAR_QOT_PUB.EXPLODE_GROUPS_QOT(
104 x_errbuf => l_errbuf,
105 x_retcode => l_retcode,
106 p_terr_globals => l_terr_globals,
107 x_return_status => l_return_status);
108
109 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
110 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
111
112 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
113 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 End If;
116
117 COMMIT;
118
119 -- Explode TEAMS if any inside winners
120 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
121 AS_GAR_QOT_PUB.EXPLODE_TEAMS_QOT(
122 x_errbuf => l_errbuf,
123 x_retcode => l_retcode,
124 p_terr_globals => l_terr_globals,
125 x_return_status => l_return_status);
126
127 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
128 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
129
130 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
131 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 End If;
134
135 COMMIT;
136 */
137 -- Set team leader for Quotes
138 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
139 AS_GAR_QOT_PUB.SET_TEAM_LEAD_QOT(
140 x_errbuf => l_errbuf,
141 x_retcode => l_retcode,
142 p_terr_globals => l_terr_globals,
143 x_return_status => l_return_status);
144
145 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
146 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
147
148 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
149 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 End If;
152
153 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::' || AS_GAR.G_START);
154 AS_GAR_QOT_PUB.SET_FAF_QOT(
155 x_errbuf => l_errbuf,
156 x_retcode => l_retcode,
157 p_terr_globals => l_terr_globals,
158 x_return_status => l_return_status);
159
160 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::' || AS_GAR.G_END);
161 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::' || AS_GAR.G_RETURN_STATUS || l_return_status);
162
163 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
164 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || 'UPDATE FAF::', l_errbuf, l_retcode);
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 End If;
167
168 -- Insert into Qot Accesses from Winners
169 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
170 AS_GAR_QOT_PUB.INSERT_ACCESSES_QOT(
171 x_errbuf => l_errbuf,
172 x_retcode => l_retcode,
173 p_terr_globals => l_terr_globals,
174 x_return_status => l_return_status);
175
176 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
177 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
178
179 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
180 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
182 End If;
183 -- Insert into territory Accesses
184 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
185 AS_GAR_QOT_PUB.INSERT_TERR_ACCESSES_QOT(
186 x_errbuf => l_errbuf,
187 x_retcode => l_retcode,
188 p_terr_globals => l_terr_globals,
189 x_return_status => l_return_status);
190
191 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
192 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
193
194 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
195 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 End If;
198
199 -- Remove records in access table that are not qualified
200 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
201 AS_GAR_QOT_PUB.PERFORM_QOT_CLEANUP(
202 x_errbuf => l_errbuf,
203 x_retcode => l_retcode,
204 p_terr_globals => l_terr_globals,
205 x_return_status => l_return_status);
206
207 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
208 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
209
210 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
211 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 End If;
214 -- Quote Owner assignment
215 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_START);
216 AS_GAR_QOT_PUB.ASSIGN_QOT_OWNER(
217 x_errbuf => l_errbuf,
218 x_retcode => l_retcode,
219 p_terr_globals => l_terr_globals,
220 x_return_status => l_return_status);
221 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
222 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
223
224 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
225 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 End If;
228
229 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
230 EXCEPTION
231 WHEN OTHERS THEN
232 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
233 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
234 END GAR_WRAPPER;
235
236 /************************** End GAR Wrapper *****************************/
237
238 /************************** Start Explode Teams Opptys ******************/
239 PROCEDURE EXPLODE_TEAMS_QOT(
240 x_errbuf OUT NOCOPY VARCHAR2,
241 x_retcode OUT NOCOPY VARCHAR2,
242 p_terr_globals IN AS_GAR.TERR_GLOBALS,
243 x_return_status OUT NOCOPY VARCHAR2)
244 IS
245
246 /*-------------------------------------------------------------------------+
247 | LOGIC
248 |
249 | A RESOURCE team can be comprised OF resources who belong TO one OR more
250 | GROUPS OF resources.
251 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
252 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
253 | WHERE they play a similar ROLE.
254 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
255 | are a RESOURCE within a team.
256 | INSERT these members INTO winners IF they are NOT already IN winners.
257 +-------------------------------------------------------------------------*/
258
259 l_errbuf VARCHAR2(4000);
260 l_retcode VARCHAR2(255);
261 l_res_type_count NUMBER;
262 l_res_quot_count NUMBER;
263 l_resource_type VARCHAR2(10);
264 l_request_id NUMBER;
265 l_worker_id NUMBER;
266
267 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
268 IS
269 SELECT count(*)
270 FROM JTF_TAE_1001_QUOTE_WINNERS
271 WHERE request_id = c_request_id
272 AND resource_type = c_resource_type
273 AND worker_id = c_worker_id
274 AND ROWNUM < 2;
275
276 CURSOR count_res_quotes
277 IS
278 SELECT count(*)
279 FROM JTF_TERR_RSC_ALL rsc,
280 JTF_TERR_DENORM_RULES_ALL rules,
281 JTF_TERR_RSC_ACCESS_ALL acc
282 WHERE rules.terr_id = rsc.terr_id
283 AND rsc.resource_type = 'RS_TEAM'
284 AND acc.access_type = 'QUOTE'
285 AND rules.source_id = -1001
286 AND rsc.terr_rsc_id = acc.terr_rsc_id;
287
288 BEGIN
289 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291 l_request_id := p_terr_globals.request_id;
292 l_worker_id := p_terr_globals.worker_id;
293 l_resource_type := 'RS_TEAM';
294
295 OPEN count_res_quotes;
296 FETCH count_res_quotes INTO l_res_quot_count;
297 CLOSE count_res_quotes;
298
299 IF l_res_quot_count > 0 THEN
300 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
301 FETCH c_get_res_type_count INTO l_res_type_count;
302 CLOSE c_get_res_type_count;
303 END IF;
304
305 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
306 IF l_res_type_count > 0 THEN
307 /* Get resources within a resource team */
308 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
309 /** Note
310 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
311 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
312 **/
313 INSERT INTO JTF_TAE_1001_QUOTE_WINNERS
314 (trans_object_id,
315 trans_detail_object_id,
316 terr_id,
317 resource_id,
318 resource_type,
319 group_id,
320 full_access_flag,
321 request_id,
322 program_application_id,
323 program_id,
324 program_update_date,
325 source_id,
326 trans_object_type_id,
327 last_update_date,
328 last_updated_by,
329 creation_date,
330 created_by,
331 last_update_login,
332 absolute_rank,
333 top_level_terr_id,
334 num_winners,
335 terr_rsc_id,
336 ROLE,
337 ROLE_ID,
338 primary_contact_flag,
339 person_id,
340 org_id,
341 worker_id)
342 SELECT T.trans_object_id,
343 T.trans_detail_object_id,
344 T.terr_id,
345 J.resource_id,
346 DECODE(J.resource_category,'PARTY','RS_PARTY',
347 'PARTNER','RS_PARTNER',
348 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
349 J.group_id,
350 T.full_access_flag,
351 T.request_id,
352 T.program_application_id,
353 T.program_id, T.program_update_date,
354 T.source_id,
355 T.trans_object_type_id,
356 SYSDATE,
357 T.last_updated_by,
358 SYSDATE,
359 T.created_by,
360 T.last_update_login,
361 T.absolute_rank,
362 T.top_level_terr_id,
363 T.num_winners,
364 T.terr_rsc_id,
365 T.ROLE,
366 T.ROLE_ID,
367 T.primary_contact_flag,
368 J.person_id,
369 T.org_id,
370 T.worker_id
371 FROM
372 JTF_TAE_1001_QUOTE_WINNERS T,
373 (
374 SELECT TM.team_resource_id resource_id,
375 TM.person_id person_id2,
376 MIN(G.group_id)group_id,
377 MIN(T.team_id) team_id,
378 TRES.CATEGORY resource_category,
379 MIN(TRES.source_id) person_id
380 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
381 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
382 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
383 (
384 SELECT m.group_id group_id, m.resource_id resource_id
385 FROM jtf_rs_group_members m,
386 jtf_rs_groups_b g,
387 jtf_rs_group_usages u,
388 jtf_rs_role_relations rr,
389 jtf_rs_roles_b r,
390 jtf_rs_resource_extns res
391 WHERE m.group_id = g.group_id
392 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
393 AND NVL(g.end_date_active,SYSDATE)
394 AND u.group_id = g.group_id
395 AND u.usage IN ('SALES','PRM')
396 AND m.group_member_id = rr.role_resource_id
397 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
398 AND rr.delete_flag <> 'Y'
399 AND SYSDATE BETWEEN rr.start_date_active
400 AND NVL(rr.end_date_active,SYSDATE)
401 AND rr.role_id = r.role_id
402 AND r.role_type_code
403 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
404 AND r.active_flag = 'Y'
405 AND res.resource_id = m.resource_id
406 AND res.CATEGORY IN ('EMPLOYEE','PARTY','PARTNER')
407 ) G
408 WHERE tm.team_id = t.team_id
409 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
410 AND NVL(t.end_date_active,SYSDATE)
411 AND tu.team_id = t.team_id
412 AND tu.usage IN ('SALES','PRM')
413 AND tm.team_member_id = trr.role_resource_id
414 AND tm.delete_flag <> 'Y'
415 AND tm.resource_type = 'INDIVIDUAL'
416 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
417 AND trr.delete_flag <> 'Y'
418 AND SYSDATE BETWEEN trr.start_date_active
419 AND NVL(trr.end_date_active,SYSDATE)
420 AND trr.role_id = tr.role_id
421 AND tr.role_type_code IN
422 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
423 AND tr.active_flag = 'Y'
424 AND tres.resource_id = tm.team_resource_id
425 AND tres.CATEGORY IN ('EMPLOYEE','PARTY','PARTNER')
426 AND tm.team_resource_id = g.resource_id
427 GROUP BY tm.team_resource_id,
428 tm.person_id,
429 tres.CATEGORY,
430 tres.source_id
431 UNION ALL
432 SELECT MIN(m.resource_id) resource_id,
433 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
434 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
435 MIN(res.source_id) person_id
436 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
437 jtf_rs_group_usages u, jtf_rs_role_relations rr,
438 jtf_rs_roles_b r, jtf_rs_resource_extns res,
439 (
440 SELECT tm.team_resource_id group_id,
441 t.team_id team_id
442 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
443 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
444 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
445 WHERE tm.team_id = t.team_id
446 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
447 AND NVL(t.end_date_active,SYSDATE)
448 AND tu.team_id = t.team_id
449 AND tu.usage IN ('SALES','PRM')
450 AND tm.team_member_id = trr.role_resource_id
451 AND tm.delete_flag <> 'Y'
452 AND tm.resource_type = 'GROUP'
453 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
454 AND trr.delete_flag <> 'Y'
455 AND SYSDATE BETWEEN trr.start_date_active
456 AND NVL(trr.end_date_active,SYSDATE)
457 AND trr.role_id = tr.role_id
458 AND tr.role_type_code IN
459 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
460 AND tr.active_flag = 'Y'
461 AND tres.resource_id = tm.team_resource_id
462 AND tres.CATEGORY IN ('EMPLOYEE','PARTY','PARTNER')
463 ) jtm
464 WHERE m.group_id = g.group_id
465 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
466 AND NVL(g.end_date_active,SYSDATE)
467 AND u.group_id = g.group_id
468 AND u.usage IN ('SALES','PRM')
469 AND m.group_member_id = rr.role_resource_id
470 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
471 AND rr.delete_flag <> 'Y'
472 AND SYSDATE BETWEEN rr.start_date_active
473 AND NVL(rr.end_date_active,SYSDATE)
474 AND rr.role_id = r.role_id
475 AND r.role_type_code IN
476 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
477 AND r.active_flag = 'Y'
478 AND res.resource_id = m.resource_id
479 AND res.CATEGORY IN ('EMPLOYEE','PARTY','PARTNER')
480 AND jtm.group_id = g.group_id
481 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
482 WHERE j.team_id = t.resource_id
483 AND t.request_id = l_request_id
484 AND t.worker_id = l_worker_id
485 AND t.resource_type = 'RS_TEAM'
486 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_QUOTE_WINNERS rt1
487 WHERE rt1.resource_id = j.resource_id
488 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
489 AND rt1.request_id = t.request_id
490 AND rt1.worker_id = t.worker_id
491 AND rt1.trans_object_id = t.trans_object_id
492 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
493
494 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
495 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
496
497
498 COMMIT;
499
500 END IF; /* if l_res_type_count > 0 */
501 EXCEPTION
502 WHEN OTHERS THEN
503 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
504 x_errbuf := SQLERRM;
505 x_retcode := SQLCODE;
506 x_return_status := FND_API.G_RET_STS_ERROR;
507 RAISE;
508 END EXPLODE_TEAMS_QOT;
509 /************************** End Explode Teams Quotes ******************/
510
511 /************************** Start Explode Groups Quotes ******************/
512 PROCEDURE EXPLODE_GROUPS_QOT(
513 x_errbuf OUT NOCOPY VARCHAR2,
514 x_retcode OUT NOCOPY VARCHAR2,
515 p_terr_globals IN AS_GAR.TERR_GLOBALS,
516 x_return_status OUT NOCOPY VARCHAR2)
517 IS
518 -------------RS_GROUP---------
519 /*-------------------------------------------------------------------------+
520 | PROGRAM LOGIC
521 |
522 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
523 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
524 | winners IF they are NOT already IN winners.
525 +-------------------------------------------------------------------------*/
526 l_errbuf VARCHAR2(4000);
527 l_retcode VARCHAR2(255);
528 l_res_type_count NUMBER;
529 l_res_quot_count NUMBER;
530 l_resource_type VARCHAR2(10);
531 l_request_id NUMBER;
532 l_worker_id NUMBER;
533
534 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
535 IS
536 SELECT count(*)
537 FROM JTF_TAE_1001_QUOTE_WINNERS
538 WHERE request_id = c_request_id
539 AND resource_type = c_resource_type
540 AND worker_id = c_worker_id
541 AND ROWNUM < 2;
542
543
544 CURSOR count_res_quotes
545 IS
546 SELECT count(*)
547 FROM JTF_TERR_RSC_ALL rsc,
548 JTF_TERR_DENORM_RULES_ALL rules,
549 JTF_TERR_RSC_ACCESS_ALL acc
550 WHERE rules.terr_id = rsc.terr_id
551 AND rsc.resource_type = 'RS_GROUP'
552 AND acc.access_type = 'QUOTE'
553 AND rules.source_id = -1001
554 AND rsc.terr_rsc_id = acc.terr_rsc_id ;
555
556 BEGIN
557 l_resource_type := 'RS_GROUP';
558 x_return_status := FND_API.G_RET_STS_SUCCESS;
559 l_request_id := p_terr_globals.request_id;
560 l_worker_id := p_terr_globals.worker_id;
561
562 OPEN count_res_quotes;
563 FETCH count_res_quotes INTO l_res_quot_count;
564 CLOSE count_res_quotes;
565
566 IF l_res_quot_count > 0 THEN
567 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
568 FETCH c_get_res_type_count INTO l_res_type_count;
569 CLOSE c_get_res_type_count;
570 END IF;
571
572 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
573 IF l_res_type_count > 0 THEN
574 /* Get resources within a resource group */
575 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
576 /** Note
577 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
578 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
579 **/
580
581 INSERT INTO JTF_TAE_1001_QUOTE_WINNERS
582 (trans_object_id,
583 trans_detail_object_id,
584 terr_id,
585 resource_id,
586 resource_type,
587 group_id,
588 full_access_flag,
589 request_id,
590 program_application_id,
591 program_id,
592 program_update_date,
593 source_id,
594 trans_object_type_id,
595 last_update_date,
596 last_updated_by,
597 creation_date,
598 created_by,
599 last_update_login,
600 absolute_rank,
601 top_level_terr_id,
602 num_winners,
603 terr_rsc_id,
604 ROLE,
605 ROLE_ID,
606 primary_contact_flag,
607 person_id,
608 org_id,
609 worker_id)
610 SELECT T.trans_object_id,
611 T.trans_detail_object_id,
612 T.terr_id,
613 J.resource_id,
614 DECODE(J.resource_category,'PARTY','RS_PARTY',
615 'PARTNER','RS_PARTNER',
616 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
617 J.group_id,
618 T.full_access_flag,
619 T.request_id,
620 T.program_application_id,
621 T.program_id,
622 T.program_update_date,
623 T.source_id,
624 T.trans_object_type_id,
625 SYSDATE,
626 T.last_updated_by,
627 SYSDATE,
628 T.created_by,
629 T.last_update_login,
630 T.absolute_rank,
631 T.top_level_terr_id,
632 T.num_winners,
633 T.terr_rsc_id,
634 T.ROLE,
635 T.ROLE_ID,
636 T.primary_contact_flag,
637 J.person_id,
638 T.org_id,
639 T.worker_id
640 FROM
641 JTF_TAE_1001_QUOTE_WINNERS t,
642 (
643 SELECT MIN(m.resource_id) resource_id,
644 res.CATEGORY resource_category,
645 m.group_id group_id, MIN(res.source_id) person_id
646 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
647 jtf_rs_group_usages u, jtf_rs_role_relations rr,
648 jtf_rs_roles_b r, jtf_rs_resource_extns res
649 WHERE m.group_id = g.group_id
650 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
651 AND NVL(g.end_date_active,SYSDATE)
652 AND u.group_id = g.group_id
653 AND u.usage IN ('SALES','PRM')
654 AND m.group_member_id = rr.role_resource_id
655 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
656 AND rr.role_id = r.role_id
657 AND rr.delete_flag <> 'Y'
658 AND SYSDATE BETWEEN rr.start_date_active
659 AND NVL(rr.end_date_active,SYSDATE)
660 AND r.role_type_code IN
661 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
662 AND r.active_flag = 'Y'
663 AND res.resource_id = m.resource_id
664 AND res.CATEGORY IN ('EMPLOYEE','PARTY','PARTNER')
665 GROUP BY m.group_member_id, m.resource_id, m.person_id,
666 m.group_id, res.CATEGORY) j
667 WHERE j.group_id = t.resource_id
668 AND t.request_id = l_request_id
669 AND t.worker_id = l_worker_id
670 AND t.resource_type = 'RS_GROUP'
671 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_QUOTE_WINNERS rt1
672 WHERE rt1.resource_id = j.resource_id
673 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
674 AND rt1.request_id = t.request_id
675 AND rt1.worker_id = t.worker_id
676 AND rt1.trans_object_id = t.trans_object_id
677 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
678
679 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
680 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
681
682 COMMIT;
683 END IF; /* if l_res_type_count > 0 */
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
688 x_errbuf := SQLERRM;
689 x_retcode := SQLCODE;
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 RAISE;
692 END EXPLODE_GROUPS_QOT;
693 /************************** End Explode Groups Quotes ******************/
694
695 /************************** Start Set Quotes Team Leader *****************/
696
697 /*-------------------------------------------------------------------------+
698 | PROGRAM LOGIC
699 |
700 | The name of the procedure is a misnomer. The name is only for maintaining
701 | consistency. The functionality achieved is as follows:
702 | Get all records which are there in quote accesses table and
703 | in winners (the equi join will ensure this) . Out of these
704 | records get records which do not exist with same info
705 | for role,group,full access flag , terr_id in the winners
706 | These records are the candidates to be merged.
707 | The NOT EXISTS is written into this cursor rather than
708 | the merge update as this cursor should a diMINishing one
709 | when it is reopened repeatedly.
710 +-------------------------------------------------------------------------*/
711
712 PROCEDURE SET_TEAM_LEAD_QOT(
713 x_errbuf OUT NOCOPY VARCHAR2,
714 x_retcode OUT NOCOPY VARCHAR2,
715 p_terr_globals IN AS_GAR.TERR_GLOBALS,
716 x_return_status OUT NOCOPY VARCHAR2)
717 IS
718
719 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
720
721
722
723 l_quote_number_id num_list;
724 l_resource_id num_list;
725 l_var NUMBER;
726 l_worker_id NUMBER;
727 l_limit_flag BOOLEAN := FALSE;
728 l_MAX_fetches NUMBER := 10000;
729 l_loop_count NUMBER := 0;
730 l_flag BOOLEAN;
731 l_first NUMBER;
732 l_last NUMBER;
733 l_attempts NUMBER := 0;
734
735 CURSOR merge_records(c_worker_id number) IS
736 SELECT DISTINCT
737 A.quote_number , A.resource_id
738 FROM JTF_TAE_1001_QUOTE_WINNERS WIN,
739 ASO_QUOTE_ACCESSES A
740 WHERE WIN.trans_object_id = A.quote_number
741 AND WIN.source_id = -1001
742 AND WIN.resource_id = A.RESOURCE_ID
743 AND WIN.worker_id = c_worker_id
744 AND WIN.resource_type = 'RS_EMPLOYEE'
745 AND NVL(A.keep_flag,'N') <> 'Y'
746 AND (NVL(win.group_id,-777) <> NVL(A.resource_grp_id,-777)
747 OR NVL(win.role_id,-777) <> NVL(A.role_id,-777))
748 AND NOT EXISTS
749 (SELECT 'X'
750 FROM JTF_TAE_1001_QUOTE_WINNERS WIN1
751 WHERE WIN1.trans_object_id = A.quote_number
752 AND WIN1.source_id = -1001
753 AND WIN1.resource_id = A.RESOURCE_ID
754 AND WIN1.worker_id = c_worker_id
755 AND WIN1.resource_type = 'RS_EMPLOYEE'
756 AND NVL(A.keep_flag,'N') <> 'Y'
757 AND NVL(WIN1.group_id,-777) = NVL(A.resource_grp_id,-777)
758 AND NVL(WIN1.role_id,-777) = NVL(A.role_id,-777))
759 ORDER BY quote_number;
760
761 BEGIN
762 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764 l_worker_id:=p_terr_globals.worker_id;
765 l_var :=p_terr_globals.bulk_size;
766 l_MAX_fetches := p_terr_globals.cursor_limit;
767 LOOP -- For l_limit_flag
768 IF (l_limit_flag) THEN EXIT; END IF;
769 l_resource_id.DELETE;
770 l_quote_number_id.DELETE;
771 l_loop_count := l_loop_count + 1;
772 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'LOOPCOUNT :- ' || l_loop_count);
773
774 --------------------------------
775 OPEN merge_records(l_worker_id);
776 FETCH merge_records BULK COLLECT INTO
777 l_quote_number_id, l_resource_id LIMIT l_MAX_fetches;
778 CLOSE merge_records;
779
780 -- Initialize variables
781 l_flag := TRUE;
782 l_first := 0;
783 l_last := 0;
784 l_attempts := 1;
785
786 IF l_quote_number_id.COUNT < l_MAX_fetches THEN
787 l_limit_flag := TRUE;
788 END IF;
789 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
790
791 IF l_quote_number_id.COUNT > 0 THEN
792 l_flag := TRUE;
793 l_first := l_quote_number_id.FIRST;
794 l_last := l_first + l_var;
795 WHILE l_flag LOOP
796 IF l_last > l_quote_number_id.LAST THEN
797 l_last := l_quote_number_id.LAST;
798 END IF;
799 WHILE l_attempts < 3 LOOP
800 BEGIN
801 FORALL i IN l_first .. l_last
802 UPDATE ASO_QUOTE_ACCESSES A
803 SET (update_access_flag ,
804 resource_grp_id ,
805 role_id ,
806 territory_id )
807 = (
808 SELECT MAX(W.full_access_flag ),MIN( W.group_id),MIN(w.role_id) ,MIN(W.terr_id)
809 FROM JTF_TAE_1001_QUOTE_WINNERS W
810 WHERE trans_object_id = l_quote_number_id(I)
811 AND resource_id = l_resource_id(i)
812 GROUP BY trans_object_id,resource_id
813 )
814 WHERE quote_number = l_quote_number_id(I)
815 AND resource_id = l_resource_id(i) ;
816 COMMIT;
817 l_attempts := 3;
818 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
819 EXCEPTION
820 WHEN DEADLOCK_DETECTED THEN
821 BEGIN
822 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
823 ROLLBACK;
824 l_attempts := l_attempts +1;
825 IF l_attempts = 3 THEN
826 FOR i IN l_first .. l_last
827 LOOP
828 BEGIN
829 UPDATE ASO_QUOTE_ACCESSES A
830 SET (update_access_flag ,
831 resource_grp_id ,
832 role_id ,
833 territory_id )
834 = (
835 SELECT MAX(W.full_access_flag ),MIN( W.group_id),MIN(w.role_id) ,MIN(W.terr_id)
836 FROM JTF_TAE_1001_QUOTE_WINNERS W
837 WHERE trans_object_id = l_quote_number_id(I)
838 AND resource_id = l_resource_id(i)
839 GROUP BY trans_object_id,resource_id
840 )
841 WHERE quote_number = l_quote_number_id(I)
842 AND resource_id = l_resource_id(i) ;
843 EXCEPTION
844 WHEN OTHERS THEN
845 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
846 AS_GAR.LOG('QUOTE_NUMBER/RESOURCE_ID - ' || l_quote_number_id(i) || '/' || l_resource_id(i));
847 END;
848 END LOOP; -- for each record individually
849 COMMIT;
850 END IF;
851 END; -- end of deadlock exception
852 WHEN OTHERS THEN
853 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
854 x_errbuf := SQLERRM;
855 x_retcode := SQLCODE;
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 RAISE;
858 END;
859 END LOOP; -- loop for 3 attempts
860 /* For the next batch of records by bulk_size */
861 l_first := l_last + 1;
862 l_last := l_first + l_var;
863 IF l_first > l_quote_number_id.LAST THEN
864 l_flag := FALSE;
865 END IF;
866 END LOOP; -- loop for more records within the bulk_size
867 END IF; --l_quote_number_id.count > 0
868 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
869 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_quote_number_id.COUNT);
870 END LOOP; -- loop for more bulk_size fetches
871 l_quote_number_id.DELETE;
872 l_resource_id.DELETE;
873 EXCEPTION
874 WHEN OTHERS THEN
875 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
876 x_errbuf := SQLERRM;
877 x_retcode := SQLCODE;
878 x_return_status := FND_API.G_RET_STS_ERROR;
879 RAISE;
880 END SET_TEAM_LEAD_QOT;
881
882 /************************** End Set Quote Team Leader *****************/
883 /************************** Start Set Full Access Flag *****************/
884 PROCEDURE SET_FAF_QOT(
885 x_errbuf OUT NOCOPY VARCHAR2,
886 x_retcode OUT NOCOPY VARCHAR2,
887 p_terr_globals IN AS_GAR.TERR_GLOBALS,
888 x_return_status OUT NOCOPY VARCHAR2)
889 IS
890
891 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
892 TYPE char_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
893
894 l_access_id num_list;
895 l_terr_id num_list;
896 l_faf char_list;
897 l_var NUMBER;
898 l_worker_id NUMBER;
899 l_limit_flag BOOLEAN := FALSE;
900 l_MAX_fetches NUMBER := 10000;
901 l_loop_count NUMBER := 0;
902 l_flag BOOLEAN;
903 l_first NUMBER;
904 l_last NUMBER;
905 l_attempts NUMBER := 0;
906
907 CURSOR merge_records(c_worker_id number) IS
908 SELECT DISTINCT
909 A.access_id,WIN.full_access_flag,WIN.terr_id
910 FROM JTF_TAE_1001_QUOTE_WINNERS WIN,
911 ASO_QUOTE_ACCESSES A
912 WHERE WIN.trans_object_id = A.quote_number
913 AND WIN.source_id = -1001
914 AND WIN.resource_id = A.RESOURCE_ID
915 AND WIN.worker_id = c_worker_id
916 AND WIN.resource_type = 'RS_EMPLOYEE'
917 AND NVL(A.keep_flag,'N') <> 'Y'
918 AND NVL(WIN.group_id,-777) = NVL(A.resource_grp_id,-777)
919 AND NVL(WIN.role_id,-777) = NVL(A.role_id,-777)
920 AND (WIN.full_access_flag <> A.update_access_flag
921 OR WIN.terr_id <> A.territory_id)
922 ORDER BY access_id;
923
924 BEGIN
925 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_START);
926 x_return_status := FND_API.G_RET_STS_SUCCESS;
927 l_worker_id:=p_terr_globals.worker_id;
928 l_var :=p_terr_globals.bulk_size;
929 l_MAX_fetches := p_terr_globals.cursor_limit;
930 LOOP -- For l_limit_flag
931 IF (l_limit_flag) THEN EXIT; END IF;
932 l_access_id.DELETE;
933 l_terr_id.DELETE;
934 l_faf.DELETE;
935 l_loop_count := l_loop_count + 1;
936 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || 'LOOPCOUNT :- ' || l_loop_count);
937
938 --------------------------------
939 OPEN merge_records(l_worker_id);
940 FETCH merge_records BULK COLLECT INTO
941 l_access_id,l_faf, l_terr_id LIMIT l_MAX_fetches;
942 CLOSE merge_records;
943
944 -- Initialize variables
945 l_flag := TRUE;
946 l_first := 0;
947 l_last := 0;
948 l_attempts := 1;
949
950 IF l_access_id.COUNT < l_MAX_fetches THEN
951 l_limit_flag := TRUE;
952 END IF;
953 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
954
955 IF l_access_id.COUNT > 0 THEN
956 l_flag := TRUE;
957 l_first := l_access_id.FIRST;
958 l_last := l_first + l_var;
959 WHILE l_flag LOOP
960 IF l_last > l_access_id.LAST THEN
961 l_last := l_access_id.LAST;
962 END IF;
963 WHILE l_attempts < 3 LOOP
964 BEGIN
965 FORALL i IN l_first .. l_last
966 UPDATE ASO_QUOTE_ACCESSES A
967 SET update_access_flag = l_faf(i),
968 territory_id = l_terr_id(i)
969 WHERE access_id = l_access_id(i);
970 COMMIT;
971 l_attempts := 3;
972 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
973 EXCEPTION
974 WHEN DEADLOCK_DETECTED THEN
975 BEGIN
976 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_DEADLOCK ||l_attempts);
977 ROLLBACK;
978 l_attempts := l_attempts +1;
979 IF l_attempts = 3 THEN
980 FOR i IN l_first .. l_last
981 LOOP
982 BEGIN
983 UPDATE ASO_QUOTE_ACCESSES A
984 SET update_access_flag = l_faf(i),
985 territory_id = l_terr_id(i)
986 WHERE access_id = l_access_id(i);
987 EXCEPTION
988 WHEN OTHERS THEN
989 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
990 AS_GAR.LOG('ACCESS_ID - ' || l_access_id(i));
991 END;
992 END LOOP; -- for each record individually
993 COMMIT;
994 END IF;
995 END; -- end of deadlock exception
996 WHEN OTHERS THEN
997 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
998 x_errbuf := SQLERRM;
999 x_retcode := SQLCODE;
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 RAISE;
1002 END;
1003 END LOOP; -- loop for 3 attempts
1004 /* For the next batch of records by bulk_size */
1005 l_first := l_last + 1;
1006 l_last := l_first + l_var;
1007 IF l_first > l_access_id.LAST THEN
1008 l_flag := FALSE;
1009 END IF;
1010 END LOOP; -- loop for more records within the bulk_size
1011 END IF; --l_quote_number_id.count > 0
1012 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_END);
1013 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::' || AS_GAR.G_N_ROWS_PROCESSED || l_access_id.COUNT);
1014 END LOOP; -- loop for more bulk_size fetches
1015 l_access_id.DELETE;
1016 l_terr_id.DELETE;
1017 l_faf.DELETE;
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || 'UPDATE FAF::', SQLERRM, TO_CHAR(SQLCODE));
1021 x_errbuf := SQLERRM;
1022 x_retcode := SQLCODE;
1023 x_return_status := FND_API.G_RET_STS_ERROR;
1024 RAISE;
1025 END SET_FAF_QOT;
1026 /************************** End Set Full Access Flag*****************/
1027 /************************** Start Insert Into Entity Accesses*************/
1028
1029 PROCEDURE INSERT_ACCESSES_QOT(
1030 x_errbuf OUT NOCOPY VARCHAR2,
1031 x_retcode OUT NOCOPY VARCHAR2,
1032 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1033 x_return_status OUT NOCOPY VARCHAR2)
1034 IS
1035
1036 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1037 TYPE faf_list IS TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
1038
1039
1040 l_quote_number_id num_list;
1041 l_terr_id num_list;
1042 l_resource_id num_list;
1043 l_sales_group_id num_list;
1044 l_person_id num_list;
1045 l_role_id num_list;
1046 l_faf faf_list;
1047
1048 l_var NUMBER;
1049 l_worker_id NUMBER;
1050 l_limit_flag BOOLEAN := FALSE;
1051 l_MAX_fetches NUMBER := 10000;
1052 l_loop_count NUMBER := 0;
1053 l_flag BOOLEAN;
1054 l_first NUMBER;
1055 l_last NUMBER;
1056 l_attempts NUMBER := 0;
1057
1058 /*----------------------------------------------------------------------------+
1059 | Slightly different from the others because each resource is allowed only once
1060 | in the sales team. Hence the MIN/MAX etc..
1061 |-----------------------------------------------------------------------------*/
1062 CURSOR ins_acc(c_worker_id number) IS
1063 SELECT W.trans_object_id,
1064 W.resource_id,
1065 MIN(w.person_id),
1066 MIN(W.group_id),
1067 MIN(W.role_id) ,
1068 MAX(W.full_access_flag ) faf,
1069 MIN(W.terr_id)
1070 FROM JTF_TAE_1001_QUOTE_WINNERS W
1071 WHERE W.resource_type = 'RS_EMPLOYEE'
1072 AND W.source_id = -1001
1073 AND W.worker_id = c_worker_id
1074 AND NOT EXISTS
1075 (SELECT 'X'
1076 FROM aso_quote_accesses A
1077 WHERE W.trans_object_id = A.quote_number
1078 AND W.resource_id = a.RESOURCE_ID)
1079 GROUP BY W.trans_object_id, W.resource_id;
1080
1081 BEGIN
1082 /*-------------------------------------------------------------------------+
1083 | PROGRAM LOGIC
1084 |
1085 | Re-Initialize variables and null out if necessary.
1086 | Try bulk inserting into accesses. If this fails, insert records one by one.
1087 |
1088 +-------------------------------------------------------------------------*/
1089 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091 l_worker_id:=p_terr_globals.worker_id;
1092 l_var :=p_terr_globals.bulk_size;
1093 OPEN ins_acc(l_worker_id);
1094 LOOP
1095 IF (l_limit_flag) THEN EXIT; END IF;
1096
1097 l_quote_number_id.DELETE;
1098 l_resource_id.DELETE;
1099 l_person_id.DELETE;
1100 l_sales_group_id.DELETE;
1101 l_role_id.DELETE;
1102 l_faf.DELETE;
1103 l_terr_id.DELETE;
1104
1105 EXIT WHEN ins_acc%NOTFOUND;
1106
1107 l_loop_count := l_loop_count + 1;
1108 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'LOOPCOUNT :- ' || l_loop_count);
1109
1110 FETCH ins_acc BULK COLLECT INTO
1111 l_quote_number_id,l_resource_id, l_person_id,l_sales_group_id, l_role_id,l_faf,l_terr_id
1112 LIMIT l_MAX_fetches;
1113
1114 -- Initialize variables
1115 l_flag := TRUE;
1116 l_first := 0;
1117 l_last := 0;
1118
1119 IF l_quote_number_id.COUNT < l_MAX_fetches THEN
1120 l_limit_flag := TRUE;
1121 END IF;
1122
1123 IF l_quote_number_id.COUNT > 0 THEN
1124 l_flag := TRUE;
1125 l_first := l_quote_number_id.FIRST;
1126 l_last := l_first + l_var;
1127 WHILE l_flag LOOP
1128 IF l_last > l_quote_number_id.LAST THEN
1129 l_last := l_quote_number_id.LAST;
1130 END IF;
1131 BEGIN
1132 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' to '||l_last);
1133 FORALL i IN l_first .. l_last
1134 INSERT INTO ASO_QUOTE_ACCESSES (
1135 ACCESS_ID,
1136 QUOTE_NUMBER,
1137 RESOURCE_ID,
1138 RESOURCE_GRP_ID,
1139 CREATED_BY,
1140 CREATION_DATE,
1141 LAST_UPDATED_BY,
1142 LAST_UPDATE_LOGIN,
1143 LAST_UPDATE_DATE,
1144 REQUEST_ID,
1145 PROGRAM_APPLICATION_ID,
1146 PROGRAM_ID,
1147 PROGRAM_UPDATE_DATE,
1148 KEEP_FLAG,
1149 UPDATE_ACCESS_FLAG,
1150 CREATED_BY_TAP_FLAG,
1151 TERRITORY_ID,
1152 TERRITORY_SOURCE_FLAG,
1153 ROLE_ID
1154 ) VALUES (
1155 ASO_QUOTE_ACCESSES_S.nextval,
1156 l_quote_number_id(i),
1157 l_resource_id(i),
1158 l_sales_group_id(i),
1159 p_terr_globals.user_id,
1160 SYSDATE,
1161 p_terr_globals.user_id,
1162 p_terr_globals.last_update_login,
1163 SYSDATE,
1164 p_terr_globals.request_id,
1165 p_terr_globals.prog_appl_id,
1166 p_terr_globals.prog_id,
1167 SYSDATE,
1168 'N',
1169 l_faf(i),
1170 'Y',
1171 l_terr_id(i),
1172 'Y',
1173 l_role_id(i)
1174 );
1175 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1176 COMMIT;
1177 EXCEPTION
1178 WHEN DUP_VAL_ON_INDEX THEN
1179 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1180 FOR i IN l_first .. l_last LOOP
1181 BEGIN
1182 INSERT INTO ASO_QUOTE_ACCESSES (
1183 access_id,
1184 quote_number,
1185 resource_id,
1186 resource_grp_id,
1187 created_by,
1188 creation_date,
1189 last_updated_by,
1190 last_update_login,
1191 last_update_date,
1192 request_id,
1193 program_application_id,
1194 program_id,
1195 program_update_date,
1196 keep_flag,
1197 update_access_flag,
1198 created_by_tap_flag,
1199 territory_id,
1200 territory_source_flag,
1201 role_id
1202 ) VALUES (
1203 aso_quote_accesses_s.NEXTVAL,
1204 l_quote_number_id(i),
1205 l_resource_id(i),
1206 l_sales_group_id(i),
1207 p_terr_globals.user_id,
1208 SYSDATE,
1209 p_terr_globals.user_id,
1210 p_terr_globals.last_update_login,
1211 SYSDATE,
1212 p_terr_globals.request_id,
1213 p_terr_globals.prog_appl_id,
1214 p_terr_globals.prog_id,
1215 SYSDATE,
1216 'N',
1217 l_faf(i),
1218 'Y',
1219 l_terr_id(i),
1220 'Y',
1221 l_role_id(i)
1222 );
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 NULL;
1226 END;
1227 END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1228 COMMIT;
1229 WHEN OTHERS THEN
1230 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1231 x_errbuf := SQLERRM;
1232 x_retcode := SQLCODE;
1233 x_return_status := FND_API.G_RET_STS_ERROR;
1234 RAISE;
1235 END;
1236 l_first := l_last + 1;
1237 l_last := l_first + l_var;
1238 IF l_first > l_quote_number_id.LAST THEN
1239 l_flag := FALSE;
1240 END IF;
1241 END LOOP; /* l_flag loop */
1242 END IF; --l_quote_number_id.COUNT > 0
1243 END LOOP; -- loop for more bulk_size fetches
1244 l_quote_number_id.DELETE;
1245 l_resource_id.DELETE;
1246 l_person_id.DELETE;
1247 l_sales_group_id.DELETE;
1248 l_role_id.DELETE;
1249 l_faf.DELETE;
1250 l_terr_id.DELETE;
1251 IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1252 EXCEPTION
1253 WHEN others THEN
1254 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1255 x_errbuf := SQLERRM;
1256 x_retcode := SQLCODE;
1257 x_return_status := FND_API.G_RET_STS_ERROR;
1258 IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1259 RAISE;
1260 END INSERT_ACCESSES_QOT;
1261
1262 /************************** End Insert Into Entity Accesses*************/
1263 /************************** Start Insert Into Quote Terr Accesses*************/
1264
1265 PROCEDURE INSERT_TERR_ACCESSES_QOT(
1266 x_errbuf OUT NOCOPY VARCHAR2,
1267 x_retcode OUT NOCOPY VARCHAR2,
1268 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1269 x_return_status OUT NOCOPY VARCHAR2)
1270 IS
1271 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1272 l_terr_id num_list;
1273 l_quote_number_id num_list;
1274 l_resource_id num_list;
1275
1276
1277 l_var NUMBER;
1278 l_limit_flag BOOLEAN := FALSE;
1279 l_worker_id NUMBER;
1280 l_MAX_fetches NUMBER := 10000;
1281 l_loop_count NUMBER := 0;
1282 l_flag BOOLEAN;
1283 l_first NUMBER;
1284 l_last NUMBER;
1285
1286 CURSOR ins_tacc(c_worker_id number) IS
1287 SELECT w.terr_id
1288 ,w.trans_object_id
1289 ,w.resource_id
1290 FROM JTF_TAE_1001_QUOTE_WINNERS W
1291 WHERE W.SOURCE_ID = -1001
1292 AND W.worker_id = c_worker_id
1293 AND W.resource_type = 'RS_EMPLOYEE'
1294 GROUP BY W.TERR_ID,
1295 W.TRANS_OBJECT_ID,
1296 W.RESOURCE_ID;
1297 BEGIN
1298 /*-------------------------------------------------------------------------+
1299 | PROGRAM LOGIC
1300 |
1301 | Re-Initialize variables and null out if necessary.
1302 | Almost the same as accesses, except the insertion is into as_territory_accesses
1303 | and there is no involvement of role.
1304 |
1305 +-------------------------------------------------------------------------*/
1306 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308 l_worker_id := p_terr_globals.worker_id;
1309 l_var := p_terr_globals.bulk_size;
1310 OPEN ins_tacc(l_worker_id);
1311 LOOP
1312 IF (l_limit_flag) THEN EXIT; END IF;
1313 EXIT WHEN ins_tacc%NOTFOUND;
1314 l_loop_count := l_loop_count + 1;
1315 l_quote_number_id.DELETE;
1316 l_resource_id.DELETE;
1317 l_terr_id.DELETE;
1318 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'LOOPCOUNT :- ' || l_loop_count);
1319 BEGIN
1320
1321 FETCH ins_tacc BULK COLLECT INTO l_terr_id,l_quote_number_id, l_resource_id
1322 LIMIT l_MAX_fetches;
1323 -- Initialize variables
1324 l_flag := TRUE;
1325 l_first := 0;
1326 l_last := 0;
1327
1328 IF l_quote_number_id.COUNT < l_MAX_fetches THEN l_limit_flag := TRUE; END IF;
1329 IF l_quote_number_id.COUNT > 0 THEN
1330 l_flag := TRUE;
1331 l_first := l_quote_number_id.FIRST;
1332 l_last := l_first + l_var;
1333 WHILE l_flag LOOP
1334 IF l_last > l_quote_number_id.LAST THEN
1335 l_last := l_quote_number_id.LAST;
1336 END IF;
1337 BEGIN
1338 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1339 FORALL i IN l_first .. l_last
1340 INSERT INTO ASO_TERRITORY_ACCESSES
1341 ( access_id,
1342 territory_id,
1343 last_update_date,
1344 last_updated_by,
1345 creation_date,
1346 created_by,
1347 last_update_login,
1348 request_id,
1349 program_application_id,
1350 program_id,
1351 program_update_date
1352 )
1353 (
1354 SELECT
1355 V.acc_id,
1356 l_terr_id(i),
1357 SYSDATE,
1358 p_terr_globals.user_id,
1359 SYSDATE,
1360 p_terr_globals.user_id,
1361 p_terr_globals.last_update_login,
1362 p_terr_globals.request_id,
1363 p_terr_globals.prog_appl_id,
1364 p_terr_globals.prog_id,
1365 SYSDATE
1366 FROM
1367 (SELECT AA.acc_id
1368 FROM ( SELECT DISTINCT a.access_id acc_id
1369 FROM ASO_QUOTE_ACCESSES A
1370 WHERE A.quote_number = l_quote_number_id(i)
1371 AND A.resource_id=l_resource_id(i)
1372 ) AA
1373 WHERE NOT EXISTS
1374 (SELECT 'X'
1375 FROM ASO_TERRITORY_ACCESSES AST
1376 WHERE AST.access_id = AA.acc_id
1377 AND AST.territory_id = l_terr_id(i))
1378 ) V
1379 );
1380 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1381 COMMIT;
1382 EXCEPTION
1383 WHEN DUP_VAL_ON_INDEX THEN
1384 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1385 FOR i IN l_first .. l_last LOOP
1386 BEGIN
1387 INSERT INTO AS_TERRITORY_ACCESSES
1388 ( access_id,
1389 territory_id,
1390 user_territory_id,
1391 last_update_date,
1392 last_updated_by,
1393 creation_date,
1394 created_by,
1395 last_update_login,
1396 request_id,
1397 program_application_id,
1398 program_id,
1399 program_update_date
1400 )
1401 (
1402 SELECT
1403 V.acc_id,
1404 l_terr_id(i),
1405 l_terr_id(i),
1406 SYSDATE,
1407 p_terr_globals.user_id,
1408 SYSDATE,
1409 p_terr_globals.user_id,
1410 p_terr_globals.last_update_login,
1411 p_terr_globals.request_id,
1412 p_terr_globals.prog_appl_id,
1413 p_terr_globals.prog_id,
1414 SYSDATE
1415 FROM
1416 (SELECT AA.acc_id
1417 FROM ( SELECT DISTINCT a.access_id acc_id
1418 FROM ASO_QUOTE_ACCESSES A
1419 WHERE A.quote_number = l_quote_number_id(i)
1420 AND A.resource_id=l_resource_id(i)
1421 ) AA
1422 WHERE NOT EXISTS
1423 (SELECT 'X'
1424 FROM ASO_TERRITORY_ACCESSES AST
1425 WHERE AST.access_id = AA.acc_id
1426 AND AST.territory_id = l_terr_id(i))
1427 ) V
1428 );
1429 EXCEPTION
1430 WHEN Others THEN
1431 NULL;
1432 END;
1433 END LOOP;
1434 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1435 COMMIT;
1436 WHEN Others THEN
1437 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1438 x_errbuf := SQLERRM;
1439 x_retcode := SQLCODE;
1440 x_return_status := FND_API.G_RET_STS_ERROR;
1441 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1442 RAISE;
1443 END;
1444 l_first := l_last + 1;
1445 l_last := l_first + l_var;
1446 IF l_first > l_quote_number_id.LAST THEN
1447 l_flag := FALSE;
1448 END IF;
1449 END LOOP;
1450 END IF; --l_quote_number_id.COUNT > 0
1451 EXCEPTION
1452 WHEN Others THEN
1453 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC , SQLERRM, TO_CHAR(SQLCODE));
1454 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1455 x_errbuf := SQLERRM;
1456 x_retcode := SQLCODE;
1457 x_return_status := FND_API.G_RET_STS_ERROR;
1458 RAISE;
1459 END;
1460 END LOOP; -- end loop for insert into territory accesses
1461 l_quote_number_id.DELETE;
1462 l_resource_id.DELETE;
1463 l_terr_id.DELETE;
1464 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1465 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1466 EXCEPTION
1467 WHEN others THEN
1468 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1469 x_errbuf := SQLERRM;
1470 x_retcode := SQLCODE;
1471 x_return_status := FND_API.G_RET_STS_ERROR;
1472 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1473 RAISE;
1474 END INSERT_TERR_ACCESSES_QOT;
1475
1476 /************************** End Insert Into Quote Terr Accesses*************/
1477 /**************************** Start Assign Quote Owner ********************/
1478 PROCEDURE ASSIGN_QOT_OWNER(
1479 x_errbuf OUT NOCOPY VARCHAR2,
1480 x_retcode OUT NOCOPY VARCHAR2,
1481 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1482 x_return_status OUT NOCOPY VARCHAR2)
1483 IS
1484 l_return_status VARCHAR2(1);
1485 l_msg_count NUMBER;
1486 l_msg_data VARCHAR2(2000);
1487
1488 /*-------------------------------------------------------------------------------
1489 | SCENARIO # 1
1490 | -------------
1491 | Get all the quote numbers and accessids of those resources which are valid in jtf
1492 | by joining the current accesses with the list of quotes which are processed in
1493 | the current batch excluding the list of quotes that we need not process..
1494 | Note: These quotes will contain atleast one valid resource in the salesteam.
1495 | The one instance that is not handled in the above scenarios is Q8.
1496 | Note that R12 is not valid but Q8 will not be picked up in the first cursor or
1497 | the second..because of the existance of Q8 R10 F !!! This is handled by introducing
1498 | the valid salesrep joins in the not exists clause..
1499 |-------------------------------------------------------------------------------*/
1500 CURSOR set_primary_srep_sc1_total(c_worker_id number) IS
1501 SELECT W.trans_object_id, MAX(access_id)
1502 FROM ASO_QUOTE_ACCESSES AQA1,
1503 ( SELECT DISTINCT trans_object_id -- Q8
1504 FROM JTF_TAE_1001_QUOTE_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
1505 WHERE worker_id=c_worker_id
1506 AND TRANS.trans_object_id = AQH.quote_number
1507 AND NOT EXISTS
1508 (
1509 SELECT 'X'
1510 FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP1
1511 WHERE AQH.resource_Id = AQA2.resource_id
1512 AND AQH.quote_number = AQA2.quote_number
1513 AND SREP1.resource_id = AQA2.resource_id
1514 AND NVL(AQA2.update_access_flag,'N') = 'Y'
1515 AND NVL(SREP1.status,'A') = 'A'
1516 AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
1517 AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1518 )-- all Q except Q8 (pick only the record which has an invalid resource set in header and access)
1519 ) W, JTF_RS_SRP_Vl SREP
1520 WHERE AQA1.quote_number = W.trans_object_id
1521 AND AQA1.resource_id = SREP.resource_id
1522 AND NVL(AQA1.update_access_flag,'N') ='Y'
1523 AND NVL(SREP.status,'A') = 'A'
1524 AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
1525 AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1526 GROUP BY W.trans_object_id;
1527
1528 CURSOR set_primary_srep_sc1_nm(c_worker_id number) IS
1529 SELECT W.trans_object_id, MAX(access_id)
1530 FROM ASO_QUOTE_ACCESSES AQA1,
1531 ( SELECT DISTINCT trans_object_id -- Q8
1532 FROM JTF_TAE_1001_QUOTE_NM_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
1533 WHERE worker_id=c_worker_id
1534 AND TRANS.trans_object_id = AQH.quote_number
1535 AND NOT EXISTS
1536 (
1537 SELECT 'X'
1538 FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP1
1539 WHERE AQH.resource_Id = AQA2.resource_id
1540 AND AQH.quote_number = AQA2.quote_number
1541 AND SREP1.resource_id = AQA2.resource_id
1542 AND NVL(AQA2.update_access_flag,'N') = 'Y'
1543 AND NVL(SREP1.status,'A') = 'A'
1544 AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
1545 AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1546 )-- all Q except Q8 (pick only the record which has an invalid resource set in header and access)
1547 ) W, JTF_RS_SRP_Vl SREP
1548 WHERE AQA1.quote_number = W.trans_object_id
1549 AND AQA1.resource_id = SREP.resource_id
1550 AND NVL(AQA1.update_access_flag,'N') ='Y'
1551 AND NVL(SREP.status,'A') = 'A'
1552 AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
1553 AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1554 GROUP BY W.trans_object_id;
1555
1556 /*-------------------------------------------------------------------------------
1557 | SCENARIO # 2
1558 | -------------
1559 | Get the list of quotes which do not have any valid resources in the sales team
1560 | These quote headers and accesses should be updated with the profile defaults
1561 |-------------------------------------------------------------------------------*/
1562 CURSOR set_primary_srep_sc2_total(c_worker_id NUMBER) IS
1563 SELECT DISTINCT trans_object_id -- Q9, Q10
1564 FROM JTF_TAE_1001_QUOTE_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
1565 WHERE worker_id=c_worker_id
1566 AND TRANS.trans_object_id = AQH.quote_number
1567 AND NOT EXISTS
1568 (SELECT 'X'
1569 FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP
1570 WHERE SREP.resource_Id = AQA2.resource_id
1571 AND AQH.quote_number = AQA2.quote_number
1572 AND NVL(AQA2.update_access_flag,'N') = 'Y'
1573 AND NVL(SREP.status,'A') = 'A'
1574 AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
1575 AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1576 ) -- R1,R7,R8,R9,R3,R4,R2,R5,R10,R11
1577 GROUP BY trans_object_id;
1578
1579 CURSOR set_primary_srep_sc2_nm(c_worker_id NUMBER) IS
1580 SELECT DISTINCT trans_object_id -- Q9, Q10
1581 FROM JTF_TAE_1001_QUOTE_NM_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
1582 WHERE worker_id=c_worker_id
1583 AND TRANS.trans_object_id = AQH.quote_number
1584 AND NOT EXISTS
1585 (SELECT 'X'
1586 FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP
1587 WHERE SREP.resource_Id = AQA2.resource_id
1588 AND AQH.quote_number = AQA2.quote_number
1589 AND NVL(AQA2.update_access_flag,'N') = 'Y'
1590 AND NVL(SREP.status,'A') = 'A'
1591 AND NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
1592 AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1593 ) -- R1,R7,R8,R9,R3,R4,R2,R5,R10,R11
1594 GROUP BY trans_object_id;
1595
1596
1597 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1598
1599 l_quote_number_id num_list;
1600 l_access_id num_list;
1601 l_org_id num_list;
1602 l_sales_grp_id num_list;
1603 l_sales_rep_id num_list;
1604
1605 l_limit_flag BOOLEAN := FALSE;
1606 l_MAX_fetches NUMBER := 10000;
1607 l_loop_count NUMBER := 0;
1608
1609 l_attempts NUMBER := 0;
1610 l_exceptions BOOLEAN := FALSE;
1611
1612 l_flag BOOLEAN;
1613 l_first NUMBER;
1614 l_last NUMBER;
1615 l_worker_id NUMBER;
1616 l_var NUMBER;
1617 l_ind_org_id NUMBER;
1618 l_ind_sales_rep_id NUMBER;
1619 l_ind_sales_grp_id NUMBER;
1620
1621 NOT_NULL EXCEPTION;
1622 PRAGMA EXCEPTION_INIT(NOT_NULL, -1400);
1623
1624 BEGIN
1625 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
1626
1627 x_return_status := FND_API.G_RET_STS_SUCCESS;
1628 l_worker_id:=p_terr_globals.worker_id;
1629 l_var :=p_terr_globals.bulk_size;
1630 l_MAX_fetches := p_terr_globals.cursor_limit;
1631 /*-------------------------------------------------------------------+
1632 | SCENARIO # 1
1633 | ------------
1634 +--------------------------------------------------------------------*/
1635 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'SCENARIO # 1' || AS_GAR.G_START);
1636 LOOP
1637 IF (l_limit_flag) THEN EXIT; END IF;
1638 l_loop_count := l_loop_count + 1;
1639 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
1640 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1641 OPEN set_primary_srep_sc1_total(l_worker_id);
1642 FETCH set_primary_srep_sc1_total BULK COLLECT INTO l_quote_number_id,l_access_id LIMIT l_MAX_fetches;
1643 CLOSE set_primary_srep_sc1_total;
1644 ELSE
1645 OPEN set_primary_srep_sc1_nm(l_worker_id);
1646 FETCH set_primary_srep_sc1_nm BULK COLLECT INTO l_quote_number_id,l_access_id LIMIT l_MAX_fetches;
1647 CLOSE set_primary_srep_sc1_nm;
1648 END IF;
1649 l_flag := TRUE;
1650 l_first := 0;
1651 l_last := 0;
1652 l_attempts := 1;
1653
1654 IF l_quote_number_id.COUNT < l_MAX_fetches THEN
1655 l_limit_flag := TRUE;
1656 END IF;
1657
1658 IF l_quote_number_id.COUNT > 0 THEN
1659 l_flag := TRUE;
1660 l_first := l_quote_number_id.FIRST;
1661 l_last := l_first + l_var;
1662 WHILE l_flag LOOP
1663 IF l_last > l_quote_number_id.LAST THEN
1664 l_last := l_quote_number_id.LAST;
1665 END IF;
1666 WHILE l_attempts < 3 LOOP
1667 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS_ALL');
1668 BEGIN
1669 FORALL i in l_first .. l_last
1670 UPDATE ASO_QUOTE_HEADERS_ALL AQH
1671 SET last_update_date = SYSDATE,
1672 last_updated_by = FND_GLOBAL.USER_ID,
1673 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1674 (resource_id,resource_grp_id) =
1675 ( SELECT resource_id,resource_grp_id
1676 FROM ASO_QUOTE_ACCESSES AQA
1677 WHERE AQA.access_id = l_access_id(i)
1678 )
1679 WHERE quote_number = l_quote_number_id(I);
1680 COMMIT;
1681 l_attempts := 3;
1682 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1683 EXCEPTION
1684 WHEN deadlock_detected THEN
1685 BEGIN
1686 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
1687 ROLLBACK;
1688 l_attempts := l_attempts +1;
1689 IF l_attempts = 3 THEN
1690 FOR i IN l_first .. l_last LOOP
1691 BEGIN
1692 UPDATE ASO_QUOTE_HEADERS_ALL AQH
1693 SET last_update_date = SYSDATE,
1694 last_updated_by = FND_GLOBAL.USER_ID,
1695 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1696 (resource_id,resource_grp_id) =
1697 ( SELECT resource_id,resource_grp_id
1698 FROM ASO_QUOTE_ACCESSES AQA
1699 WHERE AQA.access_id = l_access_id(i)
1700 )
1701 WHERE quote_number = l_quote_number_id(I);
1702 EXCEPTION
1703 WHEN OTHERS THEN
1704 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
1705 AS_GAR.LOG('QUOTE NUMBER:- ' || l_quote_number_id(I));
1706 END;
1707 END LOOP;
1708 COMMIT;
1709 END IF;
1710 END; -- end of deadlock exception
1711 WHEN OTHERS THEN
1712 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
1713 x_errbuf := SQLERRM;
1714 x_retcode := SQLCODE;
1715 x_return_status := FND_API.G_RET_STS_ERROR;
1716 RAISE;
1717 END;
1718 END LOOP;
1719 l_first := l_last + 1;
1720 l_last := l_first + l_var;
1721 IF l_first > l_quote_number_id.LAST THEN
1722 l_flag := FALSE;
1723 END IF;
1724 END LOOP;
1725 END IF; --l_quote_number_id.count > 0
1726 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_quote_number_id.COUNT);
1727 END LOOP;
1728 l_access_id.delete;
1729 l_quote_number_id.delete;
1730 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'SCENARIO # 1' || AS_GAR.G_END);
1731 l_limit_flag := FALSE;
1732 /*-------------------------------------------------------------------+
1733 | SCENARIO # 2
1734 | ------------
1735 +--------------------------------------------------------------------*/
1736 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'SCENARIO # 2' || AS_GAR.G_START);
1737 LOOP
1738 IF (l_limit_flag) THEN EXIT; END IF;
1739 l_loop_count := l_loop_count + 1;
1740 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
1741 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1742 OPEN set_primary_srep_sc2_total(l_worker_id);
1743 FETCH set_primary_srep_sc2_total BULK COLLECT INTO l_quote_number_id LIMIT l_MAX_fetches;
1744 CLOSE set_primary_srep_sc2_total;
1745 ELSE
1746 OPEN set_primary_srep_sc2_nm(l_worker_id);
1747 FETCH set_primary_srep_sc2_nm BULK COLLECT INTO l_quote_number_id LIMIT l_MAX_fetches;
1748 CLOSE set_primary_srep_sc2_nm;
1749 END IF;
1750 l_flag := TRUE;
1751 l_first := 0;
1752 l_last := 0;
1753 l_attempts := 1;
1754
1755 IF l_quote_number_id.COUNT < l_MAX_fetches THEN
1756 l_limit_flag := TRUE;
1757 END IF;
1758
1759 IF l_quote_number_id.COUNT > 0 THEN
1760 /*---------------------------------------------------------------------+
1761 | Get default Rep, Role from profile
1762 | Find out if Profile Rep is person_id or resource_id or salesrep_id
1763 | Accordingly get the resource_id and store in var
1764 | Get default group ? Talk to PMs
1765 | Insert into quote accesses
1766 | Update quote headers
1767 | We have requested quoting team for more info on how to obtain the
1768 | following default values:
1769 | <default sales rep resource id>
1770 | <default sales rep group id>
1771 | <default sales rep role id>
1772 +---------------------------------------------------------------------*/
1773 l_flag := TRUE;
1774 l_first := l_quote_number_id.FIRST;
1775 l_last := l_first + l_var;
1776 WHILE l_flag LOOP
1777 IF l_last > l_quote_number_id.LAST THEN
1778 l_last := l_quote_number_id.LAST;
1779 END IF;
1780 WHILE l_attempts < 3 LOOP
1781 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS_ALL');
1782 BEGIN
1783 FORALL i in l_first .. l_last
1784 UPDATE ASO_QUOTE_HEADERS_ALL AQH
1785 SET last_update_date = SYSDATE,
1786 last_updated_by = FND_GLOBAL.USER_ID,
1787 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1788 (resource_id,resource_grp_id) =
1789 ( SELECT resource_id,org_information3
1790 FROM jtf_rs_Salesreps a , hr_organization_information b
1791 WHERE a.salesrep_number = b.org_information2
1792 AND a.org_id = b.organization_id
1793 AND b.org_information_context = 'ASO_ORG_INFO'
1794 AND b.organization_id = AQH.org_id)
1795 WHERE quote_number = l_quote_number_id(I) RETURNING ORG_ID,RESOURCE_ID,RESOURCE_GRP_ID
1796 BULK COLLECT INTO l_org_id,l_sales_rep_id,l_sales_grp_id;
1797 COMMIT;
1798 FORALL i in l_first .. l_last
1799 INSERT INTO ASO_QUOTE_ACCESSES (
1800 ACCESS_ID,
1801 QUOTE_NUMBER,
1802 RESOURCE_ID,
1803 RESOURCE_GRP_ID,
1804 CREATED_BY,
1805 CREATION_DATE,
1806 LAST_UPDATED_BY,
1807 LAST_UPDATE_LOGIN,
1808 LAST_UPDATE_DATE,
1809 REQUEST_ID,
1810 PROGRAM_APPLICATION_ID,
1811 PROGRAM_ID,
1812 PROGRAM_UPDATE_DATE,
1813 KEEP_FLAG,
1814 UPDATE_ACCESS_FLAG,
1815 CREATED_BY_TAP_FLAG,
1816 TERRITORY_ID,
1817 TERRITORY_SOURCE_FLAG,
1818 ROLE_ID
1819 )
1820 SELECT ASO_QUOTE_ACCESSES_S.nextval,
1821 l_quote_number_id(i),
1822 l_sales_rep_id(i),
1823 l_sales_grp_id(i),
1824 p_terr_globals.user_id,
1825 SYSDATE,
1826 p_terr_globals.user_id,
1827 p_terr_globals.last_update_login,
1828 SYSDATE,
1829 p_terr_globals.request_id,
1830 p_terr_globals.prog_appl_id,
1831 p_terr_globals.prog_id,
1832 SYSDATE,
1833 'N',
1834 'Y',
1835 'Y',
1836 NULL,
1837 'N',
1838 org_information4
1839 from hr_organization_information
1840 where org_information_context = 'ASO_ORG_INFO'
1841 and organization_id = l_org_id(i);
1842 COMMIT;
1843 l_attempts := 3;
1844 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1845 EXCEPTION
1846 WHEN DUP_VAL_ON_INDEX THEN
1847 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1848 FOR i IN l_first .. l_last LOOP
1849 BEGIN
1850 INSERT INTO ASO_QUOTE_ACCESSES (
1851 ACCESS_ID,
1852 QUOTE_NUMBER,
1853 RESOURCE_ID,
1854 RESOURCE_GRP_ID,
1855 CREATED_BY,
1856 CREATION_DATE,
1857 LAST_UPDATED_BY,
1858 LAST_UPDATE_LOGIN,
1859 LAST_UPDATE_DATE,
1860 REQUEST_ID,
1861 PROGRAM_APPLICATION_ID,
1862 PROGRAM_ID,
1863 PROGRAM_UPDATE_DATE,
1864 KEEP_FLAG,
1865 UPDATE_ACCESS_FLAG,
1866 CREATED_BY_TAP_FLAG,
1867 TERRITORY_ID,
1868 TERRITORY_SOURCE_FLAG,
1869 ROLE_ID
1870 )
1871 SELECT ASO_QUOTE_ACCESSES_S.nextval,
1872 l_quote_number_id(i),
1873 l_sales_rep_id(i),
1874 l_sales_grp_id(i),
1875 p_terr_globals.user_id,
1876 SYSDATE,
1877 p_terr_globals.user_id,
1878 p_terr_globals.last_update_login,
1879 SYSDATE,
1880 p_terr_globals.request_id,
1881 p_terr_globals.prog_appl_id,
1882 p_terr_globals.prog_id,
1883 SYSDATE,
1884 'N',
1885 'Y',
1886 'Y',
1887 NULL,
1888 'N',
1889 org_information4
1890 from hr_organization_information
1891 where org_information_context = 'ASO_ORG_INFO'
1892 and organization_id = l_org_id(i);
1893 EXCEPTION
1894 WHEN Others THEN
1895 NULL;
1896 END;
1897 END LOOP;
1898 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1899 COMMIT;
1900 l_attempts := 3;
1901 WHEN DEADLOCK_DETECTED THEN
1902 BEGIN
1903 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
1904 ROLLBACK;
1905 l_attempts := l_attempts +1;
1906 IF l_attempts = 3 THEN
1907 FOR i IN l_first .. l_last LOOP
1908 BEGIN
1909 UPDATE ASO_QUOTE_HEADERS_ALL AQH
1910 SET last_update_date = SYSDATE,
1911 last_updated_by = FND_GLOBAL.USER_ID,
1912 last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1913 (resource_id,resource_grp_id) =
1914 ( SELECT resource_id,org_information3
1915 FROM jtf_rs_Salesreps a , hr_organization_information b
1916 WHERE a.salesrep_number = b.org_information2
1917 AND a.org_id = b.organization_id
1918 AND b.org_information_context = 'ASO_ORG_INFO'
1919 AND b.organization_id = AQH.org_id)
1920 WHERE quote_number = l_quote_number_id(I) RETURNING ORG_ID,RESOURCE_ID,RESOURCE_GRP_ID
1921 INTO l_ind_org_id,l_ind_sales_rep_id,l_ind_sales_grp_id;
1922
1923 INSERT INTO ASO_QUOTE_ACCESSES (
1924 ACCESS_ID,
1925 QUOTE_NUMBER,
1926 RESOURCE_ID,
1927 RESOURCE_GRP_ID,
1928 CREATED_BY,
1929 CREATION_DATE,
1930 LAST_UPDATED_BY,
1931 LAST_UPDATE_LOGIN,
1932 LAST_UPDATE_DATE,
1933 REQUEST_ID,
1934 PROGRAM_APPLICATION_ID,
1935 PROGRAM_ID,
1936 PROGRAM_UPDATE_DATE,
1937 KEEP_FLAG,
1938 UPDATE_ACCESS_FLAG,
1939 CREATED_BY_TAP_FLAG,
1940 TERRITORY_ID,
1941 TERRITORY_SOURCE_FLAG,
1942 ROLE_ID
1943 )
1944 SELECT ASO_QUOTE_ACCESSES_S.nextval,
1945 l_quote_number_id(i),
1946 l_sales_rep_id(i),
1947 l_sales_grp_id(i),
1948 p_terr_globals.user_id,
1949 SYSDATE,
1950 p_terr_globals.user_id,
1951 p_terr_globals.last_update_login,
1952 SYSDATE,
1953 p_terr_globals.request_id,
1954 p_terr_globals.prog_appl_id,
1955 p_terr_globals.prog_id,
1956 SYSDATE,
1957 'N',
1958 'Y',
1959 'Y',
1960 NULL,
1961 'N',
1962 org_information4
1963 from hr_organization_information
1964 where org_information_context = 'ASO_ORG_INFO'
1965 and organization_id = l_org_id(i);
1966 EXCEPTION
1967 WHEN OTHERS THEN
1968 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
1969 AS_GAR.LOG('QUOTE NUMBER:- ' || l_quote_number_id(I));
1970 END;
1971 END LOOP;
1972 COMMIT;
1973 END IF;
1974 END; -- end of deadlock exception
1975 WHEN NOT_NULL THEN
1976 BEGIN
1977 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_PROCESS || 'NOT NULL Condition Exception');
1978 ROLLBACK;
1979 FOR i IN l_first .. l_last LOOP
1980 BEGIN
1981 IF aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.G_DEFAULT_SALESREP,l_org_id(i)) IS NOT NULL THEN
1982 INSERT INTO ASO_QUOTE_ACCESSES (
1983 ACCESS_ID,
1984 QUOTE_NUMBER,
1985 RESOURCE_ID,
1986 RESOURCE_GRP_ID,
1987 CREATED_BY,
1988 CREATION_DATE,
1989 LAST_UPDATED_BY,
1990 LAST_UPDATE_LOGIN,
1991 LAST_UPDATE_DATE,
1992 REQUEST_ID,
1993 PROGRAM_APPLICATION_ID,
1994 PROGRAM_ID,
1995 PROGRAM_UPDATE_DATE,
1996 KEEP_FLAG,
1997 UPDATE_ACCESS_FLAG,
1998 CREATED_BY_TAP_FLAG,
1999 TERRITORY_ID,
2000 TERRITORY_SOURCE_FLAG,
2001 ROLE_ID
2002 )
2003 SELECT ASO_QUOTE_ACCESSES_S.nextval,
2004 l_quote_number_id(i),
2005 l_sales_rep_id(i),
2006 l_sales_grp_id(i),
2007 p_terr_globals.user_id,
2008 SYSDATE,
2009 p_terr_globals.user_id,
2010 p_terr_globals.last_update_login,
2011 SYSDATE,
2012 p_terr_globals.request_id,
2013 p_terr_globals.prog_appl_id,
2014 p_terr_globals.prog_id,
2015 SYSDATE,
2016 'N',
2017 'Y',
2018 'Y',
2019 NULL,
2020 'N',
2021 org_information4
2022 from hr_organization_information
2023 where org_information_context = 'ASO_ORG_INFO'
2024 and organization_id = l_org_id(i);
2025 END IF;
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
2029 AS_GAR.LOG('QUOTE NUMBER:- ' || l_quote_number_id(I));
2030 END;
2031 END LOOP;
2032 COMMIT;
2033 l_attempts := 3;
2034 END; -- NOT NULL CONDITION
2035 WHEN OTHERS THEN
2036 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE ASO_QUOTE_HEADERS', SQLERRM, TO_CHAR(SQLCODE));
2037 x_errbuf := SQLERRM;
2038 x_retcode := SQLCODE;
2039 x_return_status := FND_API.G_RET_STS_ERROR;
2040 RAISE;
2041 END;
2042 END LOOP; -- L_attempt
2043 l_first := l_last + 1;
2044 l_last := l_first + l_var;
2045 IF l_first > l_quote_number_id.LAST THEN
2046 l_flag := FALSE;
2047 END IF;
2048 END LOOP; -- l_Flag
2049 END IF; --l_quote_number_id.count > 0
2050 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_quote_number_id.COUNT);
2051 END LOOP;
2052
2053 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'SCENARIO # 2' || AS_GAR.G_END);
2054 l_limit_flag := FALSE;
2055 l_loop_count := 0;
2056 l_access_id.delete;
2057 l_quote_number_id.delete;
2058 l_attempts := 1;
2059
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2063 x_errbuf := SQLERRM;
2064 x_retcode := SQLCODE;
2065 x_return_status := FND_API.G_RET_STS_ERROR;
2066 RAISE;
2067 END ASSIGN_QOT_OWNER;
2068
2069 /**************************** End Assign Qot Owner ********************/
2070 /************************** Start Quote Cleanup ***********************/
2071
2072 PROCEDURE PERFORM_QOT_CLEANUP(
2073 x_errbuf OUT NOCOPY VARCHAR2,
2074 x_retcode OUT NOCOPY VARCHAR2,
2075 p_terr_globals IN AS_GAR.TERR_GLOBALS,
2076 x_return_status OUT NOCOPY VARCHAR2)
2077 IS
2078
2079 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
2080 l_quote_number_id num_list;
2081 l_access_id num_list;
2082
2083
2084 l_flag BOOLEAN;
2085 l_first NUMBER;
2086 l_last NUMBER;
2087 l_var NUMBER;
2088 l_attempts NUMBER := 0;
2089
2090 l_worker_id NUMBER;
2091
2092 l_del_flag BOOLEAN:=FALSE;
2093 l_limit_flag BOOLEAN := FALSE;
2094 l_MAX_fetches NUMBER := 10000;
2095 l_loop_count NUMBER := 0;
2096 G_NUM_REC CONSTANT NUMBER:=10000;
2097 G_DEL_REC CONSTANT NUMBER:=10001;
2098
2099
2100 /* This cursor different from other entities since Quoting Real time tap is not removing
2101 sales person if winners not returning any records so same kind of logic followed here also
2102 */
2103 CURSOR del_quote_totalmode(c_worker_id number) IS
2104 SELECT distinct trans_object_id
2105 FROM JTF_TAE_1001_QUOTE_WINNERS
2106 WHERE worker_id=c_worker_id;
2107
2108 BEGIN
2109 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
2110 x_return_status := FND_API.G_RET_STS_SUCCESS;
2111 l_worker_id := p_terr_globals.worker_id;
2112 l_var := p_terr_globals.bulk_size;
2113 l_MAX_fetches := p_terr_globals.cursor_limit;
2114 OPEN del_quote_totalmode(l_worker_id);
2115 LOOP --{L1
2116 IF (l_limit_flag) THEN EXIT; END IF;
2117
2118 l_loop_count := l_loop_count + 1;
2119 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
2120 BEGIN
2121 EXIT WHEN del_quote_totalmode%NOTFOUND;
2122 FETCH del_quote_totalmode BULK COLLECT INTO l_quote_number_id
2123 LIMIT l_MAX_fetches;
2124 -- Initialize variables (Ist Init)
2125 l_flag := TRUE;
2126 l_first := 0;
2127 l_last := 0;
2128 l_attempts := 1;
2129
2130 IF l_quote_number_id.COUNT < l_MAX_fetches THEN
2131 l_limit_flag := TRUE;
2132 END IF;
2133
2134 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_START);
2135 IF l_quote_number_id.count > 0 THEN --{I1
2136 l_flag := TRUE;
2137 l_first := l_quote_number_id.FIRST;
2138 l_last := l_first + l_var;
2139 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED ||
2140 l_quote_number_id.FIRST || '-' ||
2141 l_quote_number_id.LAST);
2142 WHILE l_flag LOOP --{L2 10K cust loop
2143 IF l_last > l_quote_number_id.LAST THEN
2144 l_last := l_quote_number_id.LAST;
2145 END IF;
2146 l_del_flag := FALSE;
2147 l_attempts := 1;
2148 LOOP --{L3 to update only 10k record at a time
2149 IF (l_del_flag) THEN EXIT; END IF;
2150 l_del_flag := FALSE;
2151 WHILE l_attempts < 3 LOOP --{L4
2152 BEGIN
2153 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
2154 FORALL i in l_first..l_last
2155 DELETE FROM ASO_QUOTE_ACCESSES ACC
2156 WHERE ACC.QUOTE_NUMBER =l_quote_number_id(i)
2157 AND NVL(ACC.KEEP_FLAG,'N') <> 'Y'
2158 AND NOT EXISTS (SELECT 'X'
2159 FROM JTF_TAE_1001_QUOTE_WINNERS W
2160 WHERE W.TRANS_OBJECT_ID = ACC.QUOTE_NUMBER
2161 AND W.WORKER_ID = l_worker_id
2162 AND W.RESOURCE_ID = ACC.RESOURCE_ID)
2163 AND ROWNUM < G_DEL_REC;
2164 COMMIT;
2165 l_attempts := 3;
2166 IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
2167 EXCEPTION
2168 WHEN deadlock_detected THEN
2169 BEGIN --{I2
2170 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_DEADLOCK || l_attempts);
2171 ROLLBACK;
2172 l_attempts := l_attempts +1;
2173 IF l_attempts = 3 THEN
2174 FOR i IN l_first .. l_last LOOP --{L5
2175 BEGIN
2176 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_UPD || AS_GAR.G_START);
2177 DELETE FROM ASO_QUOTE_ACCESSES ACC
2178 WHERE ACC.QUOTE_NUMBER =l_quote_number_id(i)
2179 AND NVL(ACC.KEEP_FLAG,'N') <> 'Y'
2180 AND NOT EXISTS (SELECT 'X'
2181 FROM JTF_TAE_1001_QUOTE_WINNERS W
2182 WHERE W.TRANS_OBJECT_ID = ACC.QUOTE_NUMBER
2183 AND W.WORKER_ID = l_worker_id
2184 AND W.RESOURCE_ID = ACC.RESOURCE_ID);
2185 COMMIT;
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_GENERAL_EXCEPTION);
2189 AS_GAR.LOG('QUOTE id - ' || l_quote_number_id(i));
2190 END;
2191 END LOOP; --}L5
2192 COMMIT;
2193 l_del_flag := TRUE;
2194 END IF;
2195 END; --}I2 end of deadlock exception
2196 WHEN OTHERS THEN
2197 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2198 IF del_quote_totalmode%ISOPEN THEN CLOSE del_quote_totalmode; END IF;
2199 x_errbuf := SQLERRM;
2200 x_retcode := SQLCODE;
2201 x_return_status := FND_API.G_RET_STS_ERROR;
2202 RAISE;
2203 END;
2204 END LOOP; --}L4 l_attempts loop 3 trys
2205 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-' || l_last);
2206 END LOOP; --}L3 -- to update only 10k record at a time on accesses
2207 l_first := l_last + 1;
2208 l_last := l_first + l_var;
2209 IF l_first > l_quote_number_id.LAST THEN
2210 l_flag := FALSE;
2211 END IF;
2212 END LOOP; --}L2 while l_flag loop (10K cust loop)
2213 END IF;--}I1
2214 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
2215 COMMIT;
2216 EXCEPTION
2217 WHEN Others THEN
2218 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2219 IF del_quote_totalmode%ISOPEN THEN CLOSE del_quote_totalmode; END IF;
2220 x_errbuf := SQLERRM;
2221 x_retcode := SQLCODE;
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223 RAISE;
2224 END;
2225 END LOOP;--}L1
2226 IF del_quote_totalmode%ISOPEN THEN CLOSE del_quote_totalmode; END IF;
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2230 x_errbuf := SQLERRM;
2231 x_retcode := SQLCODE;
2232 x_return_status := FND_API.G_RET_STS_ERROR;
2233 RAISE;
2234 END PERFORM_QOT_CLEANUP;
2235
2236 /************************** End Quote Cleanup ***********************/
2237
2238
2239 END AS_GAR_QOT_PUB;