[Home] [Help]
PACKAGE BODY: APPS.PV_BATCH_ASSIGN_PUB
Source
1 Package Body PV_BATCH_ASSIGN_PUB AS
2 /* $Header: pvbtasnb.pls 120.3 2006/01/10 13:52:09 amaram ship $ */
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* Global Variable Declaration */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13
14
15 /*************************************************************************************/
16 /* */
17 /* */
18 /* */
19 /* private procedure declaration */
20 /* */
21 /* */
22 /* */
23 /*************************************************************************************/
24 PROCEDURE Debug(
25 p_msg_string IN VARCHAR2,
26 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
27 );
28
29
30 PROCEDURE Set_Message(
31 p_msg_level IN NUMBER,
32 p_msg_name IN VARCHAR2,
33 p_token1 IN VARCHAR2 := NULL,
34 p_token1_value IN VARCHAR2 := NULL,
35 p_token2 IN VARCHAR2 := NULL,
36 p_token2_value IN VARCHAR2 := NULL,
37 p_token3 IN VARCHAR2 := NULL,
38 p_token3_value IN VARCHAR2 := NULL
39 );
40
41
42
43 --=============================================================================+
44 --| Public Procedure |
45 --| PROCESS_UNASSIGNED |
46 --| |
47 --| Parameters |
48 --| IN |
49 --| OUT |
50 --| |
51 --| |
52 --| NOTES |
53 --| The purpose of this procedure is to process all the timed out |
54 --| opportunites created by vendor. It will use opportunity_selection API |
55 --| to attemp to route the opportunities to partners. |
56 --| |
57 --| HISTORY |
58 --| |
59 --==============================================================================
60 PROCEDURE PROCESS_UNASSIGNED(ERRBUF OUT NOCOPY VARCHAR2,
61 RETCODE OUT NOCOPY VARCHAR2,
62 P_COUNTRY IN VARCHAR2,
63 P_USERNAME IN VARCHAR2,
64 P_FROMDATE IN VARCHAR2)
65 IS
66
67 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_UNASSIGNED';
68 l_api_version_number CONSTANT NUMBER := 1.0;
69
70 l_fromdate DATE := TO_DATE(p_fromdate, 'yyyy/mm/dd hh24:mi:ss');
71
72 CURSOR lc_vendor (pc_oppty_timeout NUMBER,
73 pc_fromdate DATE) IS
74 SELECT a.lead_id, a.description
75 FROM as_leads_all a,
76 pv_lead_workflows b,
77 pv_channel_types c,
78 as_statuses_b d
79 WHERE a.channel_code = c.channel_lookup_code
80 AND c.channel_lookup_type = 'SALES_CHANNEL'
81 AND c.indirect_channel_flag = 'Y'
82 AND a.lead_id = b.lead_id (+)
83 AND b.entity (+) = 'OPPORTUNITY'
84 AND b.latest_routing_flag (+) = 'Y'
85 AND a.status = d.status_code
86 AND d.opp_open_status_flag = 'Y'
87 AND a.last_update_date < sysdate - pc_oppty_timeout
88 AND a.creation_date >= NVL(pc_fromdate, SYSDATE - 36500)
89 AND (b.routing_status is null OR
90 b.routing_status IN ('RECYCLED','WITHDRAWN'));
91
92 CURSOR lc_vendor_cntry(pc_oppty_timeout NUMBER,
93 pc_country VARCHAR2,
94 pc_fromdate DATE) IS
95 SELECT a.lead_id, a.description
96 FROM as_leads_all a, pv_lead_workflows b,
97 pv_channel_types c,hz_party_sites d,
98 hz_locations e, as_statuses_b f
99 WHERE a.channel_code = c.channel_lookup_code
100 AND a.address_id = d.party_site_id
101 AND d.location_id = e.location_id
102 AND d.status IN ('A','I')
103 AND c.channel_lookup_type = 'SALES_CHANNEL'
104 AND c.indirect_channel_flag = 'Y'
105 AND a.lead_id = b.lead_id (+)
106 AND b.entity (+) = 'OPPORTUNITY'
107 AND b.latest_routing_flag (+) = 'Y'
108 AND a.status = f.status_code
109 AND f.opp_open_status_flag = 'Y'
110 AND e.country = pc_country
111 AND a.last_update_date < sysdate - pc_oppty_timeout
112 AND a.creation_date >= NVL(pc_fromdate, SYSDATE - 36500)
113 AND (b.routing_status is null OR
114 b.routing_status IN ('RECYCLED','WITHDRAWN'));
115
116 CURSOR lc_get_resource (pc_username varchar2) is
117 SELECT extn.category,
118 extn.resource_id
119 FROM fnd_user fuser,
120 jtf_rs_resource_extns extn
121 WHERE fuser.user_name = pc_username
122 AND fuser.user_id = extn.user_id;
123
124 l_rank number := 0;
125 l_assignment_type VARCHAR2(30);
126 l_rank_tbl JTF_NUMBER_TABLE;
127 l_source_type_tbl JTF_VARCHAR2_TABLE_100;
128 l_size NUMBER;
129 l_resource_id NUMBER;
130 l_partner_count NUMBER;
131 l_category VARCHAR2(30);
132 l_lead_id_tbl JTF_NUMBER_TABLE;
133 l_lead_desc_tbl JTF_VARCHAR2_TABLE_400;
134 l_partner_id_tbl JTF_NUMBER_TABLE;
135
136 l_return_status VARCHAR2(1);
137 l_message VARCHAR2(32000);
138 l_msg_data VARCHAR2(32000);
139 l_msg_count NUMBER;
140 l_ret_code NUMBER;
141 l_opp_count NUMBER := 0;
142 l_oppty_timeout_set NUMBER ;
143 l_user_name VARCHAR2(20);
144 l_no_partner_exec EXCEPTION;
145 l_user_not_emp_exec EXCEPTION;
146 l_auto_match_exec EXCEPTION;
147 l_no_user_exec EXCEPTION;
148 l_null_timeout_exec EXCEPTION;
149 l_selected_rule_id NUMBER;
150 l_failure_code VARCHAR2(1000);
151 l_lead_id NUMBER;
152
153 BEGIN
154 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
155 p_msg_name => 'PV_CREATE_BATCH_START_TIME',
156 p_token1 => 'P_DATE_TIME',
157 p_token1_value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
158
159
160 l_return_status := FND_API.G_RET_STS_SUCCESS ;
161
162 l_size := 0;
163 l_lead_id_tbl := JTF_NUMBER_TABLE();
164 l_lead_desc_tbl := JTF_VARCHAR2_TABLE_400();
165
166
167 IF (p_country is not null) THEN
168 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
169 p_msg_name => 'PV_BATCH_COUNTRY',
170 p_token1 => 'P_COUNTRY',
171 p_token1_value => p_country);
172
173 ELSE
174 Debug('Processing the Opportunity Assignment for all countries.');
175 END IF;
176
177
178 IF p_username is not null THEN
179 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
180 p_msg_name => 'PV_CREATE_BATCH_USER',
181 p_token1 => 'P_USER_NAME',
182 p_token1_value => p_username);
183
184 ELSE
185 RAISE l_no_user_exec;
186 END IF;
187
188 -- ----------------------------------------------------------------------
189 -- Retrieve the profile value for unassigned opportunity timeout.
190 -- ----------------------------------------------------------------------
191 l_oppty_timeout_set := FND_PROFILE.VALUE('PV_OPPTY_UNASIGNED_TIMEOUT');
192
193 IF (l_oppty_timeout_set IS NULL) THEN
194 RAISE l_null_timeout_exec;
195 END IF;
196
197
198 --l_user_name := FND_PROFILE.VALUE('PV_BATCH_ASSIGN_USER_NAME');
199
200
201 OPEN lc_get_resource(p_username);
202 FETCH lc_get_resource INTO l_category,l_resource_id;
203 Debug('l_category = ' || l_category);
204 Debug('l_resource_id = ' || l_resource_id);
205
206 IF (lc_get_resource%NOTFOUND) THEN
207 CLOSE lc_get_resource;
208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
209 END IF;
210
211 CLOSE lc_get_resource;
212
213 IF (l_category <> 'EMPLOYEE') THEN
214 RAISE l_user_not_emp_exec;
215 END IF;
216
217
218 IF p_country IS NULL THEN
219 OPEN lc_vendor(l_oppty_timeout_set, l_fromdate);
220 LOOP
221 l_lead_id_tbl.extend;
222 l_lead_desc_tbl.extend;
223
224 l_size := l_size + 1;
225 fetch lc_vendor into l_lead_id_tbl(l_size), l_lead_desc_tbl(l_size);
226 exit when lc_vendor%notfound;
227
228 END LOOP;
229 CLOSE lc_vendor;
230
231 ELSE
232 OPEN lc_vendor_cntry(l_oppty_timeout_set, p_country, l_fromdate);
233 LOOP
234 l_lead_id_tbl.extend;
235 l_lead_desc_tbl.extend;
236
237 l_size := l_size + 1;
238 fetch lc_vendor_cntry into l_lead_id_tbl(l_size), l_lead_desc_tbl(l_size);
239 exit when lc_vendor_cntry%notfound;
240
241 END LOOP;
242 CLOSE lc_vendor_cntry;
243 END IF;
244
245
246 l_lead_id_tbl.trim;
247 l_lead_desc_tbl.trim;
248
249
250 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
251 p_msg_name => 'PV_OPPORTUNITY_COUNT',
252 p_token1 => 'P_OPP_COUNT',
253 p_token1_value => l_lead_id_tbl.count);
254
255
256 -- -----------------------------------------------------------------------------
257 -- Start processing unassigned opportunities retrieved.
258 -- -----------------------------------------------------------------------------
259 IF (l_lead_id_tbl.count > 0) THEN
260 FOR j in 1..l_lead_id_tbl.count LOOP
261 BEGIN
262 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
263 p_msg_name => 'PV_OPPORTUNITY_NAME',
264 p_token1 => 'TEXT',
265 p_token1_value => 'Opportunity ID :' || l_lead_id_tbl(j) ||
266 ' Opportunity Name: '||l_lead_desc_tbl(j));
267
268
269 Debug('Starting Automatic matching and Routing');
270
271 l_lead_id := l_lead_id_tbl(j);
272 SAVEPOINT vendor_opp;
273
274 Debug('**********************************************************************');
275 Debug('Processing opportunity with lead_id = ' || l_lead_id);
276 Debug(l_lead_desc_tbl(j));
277 Debug('**********************************************************************');
278
279 pv_opp_match_pub.Opportunity_Selection(
280 p_api_version => l_api_version_number,
281 p_init_msg_list => FND_API.G_FALSE,
282 p_commit => FND_API.G_FALSE,
283 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
284 p_entity_id => l_lead_id,
285 p_entity => 'LEAD',
286 p_user_name => p_username,
287 p_resource_id => l_resource_id,
288 x_selected_rule_id => l_selected_rule_id,
289 x_matched_partner_count => l_partner_count,
290 x_failure_code => l_failure_code,
291 x_return_status => l_return_status,
292 x_msg_count => l_msg_count,
293 x_msg_data => l_msg_data
294 );
295
296 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS) THEN
297
298 IF l_msg_count > 0 THEN
299
300 l_message := fnd_msg_pub.get(
301 p_msg_index => fnd_msg_pub.g_first, p_encoded => FND_API.g_false
302 );
303
304 WHILE (l_message IS NOT NULL) LOOP
305 fnd_file.put_line(FND_FILE.LOG,substr(l_message,1,200));
306 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
307 END LOOP;
308
309 END IF;
310
311 END IF;
312
313
314 IF (l_partner_count = 0) THEN
315 RAISE l_no_partner_exec;
316
317 ELSIF ((l_partner_count > 0) AND (l_failure_code is null)) THEN
318 Debug('Number of Partners matched and routed for the rule ' ||
319 l_selected_rule_id || 'is :' || l_partner_count);
320 Debug('Completed the Assignment process ...');
321
322 ELSIF (l_failure_code is not null) THEN
323 RAISE l_auto_match_exec;
324 END IF;
325
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 ROLLBACK TO vendor_opp;
329
330 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
331 p_msg_name => 'PV_NO_OPP_ASSIGN',
332 p_token1 => 'P_OPP_NAME',
333 p_token1_value => l_lead_desc_tbl(j));
334
335 l_opp_count := l_opp_count + 1;
336
337 WHEN l_auto_match_exec THEN
338 ROLLBACK TO vendor_opp;
339 Debug('Opportunity matching and routing failed due to ' || l_failure_code);
340
341 l_opp_count := l_opp_count+1;
342
343 WHEN l_no_partner_exec THEN
344 ROLLBACK TO vendor_opp;
345
346 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
347 p_msg_name => 'PV_NO_PRTNR_FOR_OPPTY',
348 p_token1 => 'P_OPP_NAME',
349 p_token1_value => l_lead_desc_tbl(j));
350
351 l_opp_count := l_opp_count + 1;
352
353 WHEN OTHERS THEN
354 ROLLBACK TO vendor_opp;
355 l_opp_count := l_opp_count + 1;
356
357 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
358 p_msg_name => 'PV_UNKNOWN_ERROR',
359 p_token1 => 'TEXT',
360 p_token1_value => 'Database Error'||sqlcode||' '||sqlerrm);
361
362
363 END;
364 END LOOP;
365
366 ELSE
367 l_opp_count := null;
368
369 END IF;
370
371
372 -- --------------------------------------------------------------------------------
373 -- Every unassigned opportunity found have failed to be routed for one reason or
374 -- another. The user needs to check the log for the failure reason.
375 -- --------------------------------------------------------------------------------
376 IF (l_opp_count = l_lead_id_tbl.count) THEN
377 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
378 p_msg_name => 'PV_ALL_OPPTY_FAILED');
379
380 RETCODE := 1;
381 ERRBUF := fnd_message.get;
382
383 ELSIF ((l_opp_count <> l_lead_id_tbl.count) AND (l_opp_count <> 0)) THEN
384 Debug('Concurrent Program ran for '||l_lead_id_tbl.count||
385 'opportunities, of which '||l_opp_count||
386 ' opportunities failed. Check the Log');
387
388 RETCODE := 1;
389 ERRBUF := fnd_message.get;
390
391 ELSIF (l_opp_count = 0) THEN
392 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
393 p_msg_name => 'PV_ALL_OPPTY_SUCCESS');
394
395 RETCODE := 0;
396 ERRBUF := fnd_message.get;
397
398 ELSIF (l_opp_count IS null) THEN
399 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
400 p_msg_name => 'PV_NO_OPPTY_FOUND');
401
402
403 RETCODE := 0;
404 ERRBUF := fnd_message.get;
405 END IF;
406
407 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
408 p_msg_name => 'PV_CREATE_BATCH_END_TIME',
409 p_token1 => 'P_DATE_TIME',
410 p_token1_value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
411
412 EXCEPTION
413
414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
415 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
416 p_msg_name => 'PV_NO_RESOURCE_FOUND');
417
418 RETCODE := 2;
419 ERRBUF := fnd_message.get;
420
421 WHEN l_user_not_emp_exec THEN
422 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
423 p_msg_name => 'PV_USER_NOT_A_EMPLOYEE');
424
425 RETCODE := 2;
426 ERRBUF := fnd_message.get;
427
428 WHEN l_no_user_exec THEN
429 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
430 p_msg_name => 'PV_NO_BATCH_USER',
431 p_token1 => 'TEXT',
432 p_token1_value => 'No Assignment Manager specified, Cannot run the ' ||
433 'Assignment process');
434
435 RETCODE := 2;
436 ERRBUF := fnd_message.get;
437
438 WHEN l_null_timeout_exec THEN
439 Debug('Timeout value is not set. Set the timeout value and run the process');
440
441 RETCODE := 2;
442 ERRBUF := fnd_message.get;
443
444 WHEN OTHERS THEN
445 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
446 p_msg_name => 'PV_UNKNOWN_ERROR',
447 p_token1 => 'TEXT',
448 p_token1_value => 'Database Error:'||sqlcode||' '||sqlerrm);
449
450 RETCODE := 1;
451 ERRBUF := fnd_message.get;
452 END PROCESS_UNASSIGNED;
453
454
455 --=============================================================================+
456 --| Private Procedure |
457 --| |
458 --| Debug |
459 --| |
460 --| Parameters |
461 --| IN |
462 --| OUT |
463 --| |
464 --| |
465 --| NOTES: |
466 --| |
467 --| HISTORY |
468 --| |
469 --==============================================================================
470 PROCEDURE Debug(
471 p_msg_string IN VARCHAR2,
472 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
473 )
474 IS
475 BEGIN
476 FND_MESSAGE.Set_Name('PV', p_msg_type);
477 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
478
479 IF (g_log_to_file = 'N') THEN
480 FND_MSG_PUB.Add;
481
482 ELSIF (g_log_to_file = 'Y') THEN
483 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
484 END IF;
485 END Debug;
486 -- =================================End of Debug================================
487
488
489
490 --=============================================================================+
491 --| Public Procedure |
492 --| |
493 --| Set_Message |
494 --| |
495 --| Parameters |
496 --| IN |
497 --| OUT |
498 --| |
499 --| |
500 --| NOTES: |
501 --| |
502 --| HISTORY |
503 --| |
504 --==============================================================================
505 PROCEDURE Set_Message(
506 p_msg_level IN NUMBER,
507 p_msg_name IN VARCHAR2,
508 p_token1 IN VARCHAR2 := NULL,
509 p_token1_value IN VARCHAR2 := NULL,
510 p_token2 IN VARCHAR2 := NULL,
511 p_token2_value IN VARCHAR2 := NULL,
512 p_token3 IN VARCHAR2 := NULL,
513 p_token3_value IN VARCHAR2 := NULL
514 )
515 IS
516 BEGIN
517 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
518 FND_MESSAGE.Set_Name('PV', p_msg_name);
519 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
520
521 IF (p_token1 IS NOT NULL) THEN
522 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
523 END IF;
524
525 IF (p_token2 IS NOT NULL) THEN
526 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
527 END IF;
528
529 IF (p_token3 IS NOT NULL) THEN
530 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
531 END IF;
532
533 IF (g_log_to_file = 'N') THEN
534 FND_MSG_PUB.Add;
535
536 ELSIF (g_log_to_file = 'Y') THEN
537 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
538 END IF;
539 END IF;
540 END Set_Message;
541 -- ==============================End of Set_Message==============================
542
543
544 END;