[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_OWNER
Source
1 PACKAGE BODY AS_SALES_LEAD_OWNER AS
2 /* $Header: asxvslnb.pls 115.7 2003/11/15 00:49:50 solin ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='AS_SALES_LEAD_OWNER';
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- Check_Uk_Items
10 --
11 ---------------------------------------------------------------------
12 PROCEDURE Check_Uk_Items(
13 p_Lead_Owner_rec IN Lead_Owner_rec_type
14 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
15 ,x_return_status OUT NOCOPY VARCHAR2
16 )
17 IS
18 l_valid_flag VARCHAR2(1);
19 BEGIN
20
21 x_return_status := FND_API.g_ret_sts_success;
22
23
24 END Check_Uk_Items;
25
26
27 ---------------------------------------------------------------------
28 -- PROCEDURE
29 -- Check_Fk_Items
30 --
31 ---------------------------------------------------------------------
32 PROCEDURE Check_Fk_Items(
33 p_Lead_Owner_rec IN Lead_Owner_rec_type
34 ,x_return_status OUT NOCOPY VARCHAR2
35 )
36 IS
37
38 l_cm_exsits VARCHAR2(10);
39
40 -- Cursor to validate the uniqueness
41 CURSOR c_cm_exsist(cv_cm_resource_id IN NUMBER) IS
42 SELECT 'ANYTHING'
43 FROM jtf_rs_resource_extns
44 WHERE resource_id = cv_cm_resource_id;
45
46 BEGIN
47
48 x_return_status := FND_API.g_ret_sts_success;
49
50
51 ----------------------- attribute_id ------------------------
52 IF p_Lead_Owner_rec.CM_resource_id <> FND_API.g_miss_num THEN
53
54 -- Check the uniqueness of the identifier
55 OPEN c_cm_exsist(p_Lead_Owner_rec.CM_RESOURCE_ID);
56 FETCH c_cm_exsist INTO l_cm_exsits;
57 -- Exit when the identifier uniqueness is established
58 IF c_cm_exsist%ROWCOUNT = 0 THEN
59 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
60 FND_MESSAGE.set_name('PV', 'PV_BAD_CMM_RESOURCE_ID');
61 FND_MESSAGE.SET_TOKEN('ID', to_char(p_Lead_Owner_rec.CM_resource_id) );
62 FND_MSG_PUB.add;
63 END IF;
64 CLOSE c_cm_exsist;
65
66 /*
67 IF AS_Utility_PVT.check_fk_exists(
68 'jtf_rs_resource_extns', -- Parent schema object having the primary key
69 'resource_id', -- Column name in the parent object that maps to the fk value
70 p_Lead_Owner_rec.CM_resource_id, -- Value of fk to be validated against the parent object's pk column
71 AS_Utility_PVT.g_number -- datatype of fk
72 ) = FND_API.g_false
73 THEN
74 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
75 THEN
76 FND_MESSAGE.set_name('PV', 'PV_BAD_CMM_RESOURCE_ID');
77 FND_MESSAGE.SET_TOKEN('ID', to_char(p_Lead_Owner_rec.CM_resource_id) );
78 FND_MSG_PUB.add;
79 END IF;
80 */
81 x_return_status := FND_API.g_ret_sts_error;
82 RETURN;
83 END IF;
84 END IF;
85
86 -- check other fk items
87
88 END Check_Fk_Items;
89
90
91 ---------------------------------------------------------------------
92 -- PROCEDURE
93 -- Check_Req_Items
94 --
95 ---------------------------------------------------------------------
96 PROCEDURE Check_Req_Items(
97 p_Lead_Owner_rec IN Lead_Owner_rec_type
98 ,x_return_status OUT NOCOPY VARCHAR2
99 )
100 IS
101 BEGIN
102
103 x_return_status := FND_API.g_ret_sts_success;
104
105 /*
106 ------------------------ entity --------------------------
107 IF p_Lead_Owner_rec.entity IS NULL THEN
108 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
109 THEN
110 FND_MESSAGE.set_name('PV', 'PV_NO_ENTITY');
111 FND_MSG_PUB.add;
112 END IF;
113
114 x_return_status := FND_API.g_ret_sts_error;
115 RETURN;
116 ------------------------ attribute id -------------------------------
117 ELSIF p_Lead_Owner_rec.attribute_id IS NULL THEN
118 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
119 THEN
120 FND_MESSAGE.set_name('PV', 'PV_NO_ATTR_ID');
121 FND_MESSAGE.SET_TOKEN('ID',to_char(p_Lead_Owner_rec.attribute_id) );
122 FND_MSG_PUB.add;
123 END IF;
124
125 x_return_status := FND_API.g_ret_sts_error;
126 RETURN;
127 END IF;*/
128
129 END Check_Req_Items;
130
131 ---------------------------------------------------------------------
132 -- PROCEDURE
133 -- Check_Lookup_Items
134 --
135 ---------------------------------------------------------------------
136 PROCEDURE Check_Lookup_Items(
137 p_Lead_Owner_rec IN Lead_Owner_rec_type
138 ,x_return_status OUT NOCOPY VARCHAR2
139 )
140 IS
141 BEGIN
142
143 x_return_status := FND_API.g_ret_sts_success;
144
145
146 -- check other lookup codes
147
148 END Check_Lookup_Items;
149
150
151 ---------------------------------------------------------------------
152 -- PROCEDURE
153 -- Check_Flag_Items
154 --
155 ---------------------------------------------------------------------
156 PROCEDURE Check_Flag_Items(
157 p_Lead_Owner_rec IN Lead_Owner_rec_type
158 ,x_return_status OUT NOCOPY VARCHAR2
159 )
160 IS
161 BEGIN
162
163 x_return_status := FND_API.g_ret_sts_success;
164
165
166 END Check_Flag_Items;
167
168 ---------------------------------------------------------------------
169 -- PROCEDURE
170 -- Check_Lead_Owner_items
171 --
172 ---------------------------------------------------------------------
173 PROCEDURE Check_Lead_Owner_items(
174 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
175 ,x_return_status OUT NOCOPY VARCHAR2
176 ,p_Lead_Owner_rec IN Lead_Owner_rec_type
177 )
178 IS
179 BEGIN
180
181 --DBMS_output.put_line(': start req items validate');
182 Check_Req_Items(
183 p_Lead_Owner_rec => p_Lead_Owner_rec
184 ,x_return_status => x_return_status
185 );
186
187 IF x_return_status <> FND_API.g_ret_sts_success THEN
188 RETURN;
189 END IF;
190
191 --DBMS_output.put_line(': start uk record validate');
192 Check_Uk_Items(
193 p_Lead_Owner_rec => p_Lead_Owner_rec
194 ,p_validation_mode => p_validation_mode
195 ,x_return_status => x_return_status
196 );
197
198 IF x_return_status <> FND_API.g_ret_sts_success THEN
199 RETURN;
200 END IF;
201
202 --DBMS_output.put_line(': start fk record validate');
203 Check_Fk_Items(
204 p_Lead_Owner_rec => p_Lead_Owner_rec
205 ,x_return_status => x_return_status
206 );
207
208 IF x_return_status <> FND_API.g_ret_sts_success THEN
209 RETURN;
210 END IF;
211
212 --DBMS_output.put_line(': start lookup record validate');
213 Check_Lookup_Items(
214 p_Lead_Owner_rec => p_Lead_Owner_rec
215 ,x_return_status => x_return_status
216 );
217
218 IF x_return_status <> FND_API.g_ret_sts_success THEN
219 RETURN;
220 END IF;
221
222 --DBMS_output.put_line(': start flag record validate');
223 Check_Flag_Items(
224 p_Lead_Owner_rec => p_Lead_Owner_rec
225 ,x_return_status => x_return_status
226 );
227
228 IF x_return_status <> FND_API.g_ret_sts_success THEN
229 RETURN;
230 END IF;
231
232 END Check_Lead_Owner_Items;
233
234
235 ---------------------------------------------------------------------
236 -- PROCEDURE
237 -- Check_Lead_Owner_rec
238 --
239 ---------------------------------------------------------------------
240 PROCEDURE Check_Lead_Owner_rec(
241 p_Lead_Owner_rec IN Lead_Owner_rec_type
242 ,p_complete_rec IN Lead_Owner_rec_type := NULL
243 ,p_mode IN VARCHAR2 := 'INSERT'
244 ,x_return_status OUT NOCOPY VARCHAR2
245 )
246 IS
247
248
249 BEGIN
250
251 x_return_status := FND_API.g_ret_sts_success;
252
253
254
255 -- do other record level checkings
256
257 END Check_Lead_Owner_rec;
258
259
260 ---------------------------------------------------------------------
261 -- PROCEDURE
262 -- Init_Lead_Owner_Rec
263 --
264 ---------------------------------------------------------------------
265 PROCEDURE Init_Lead_Owner_Rec(
266 x_Lead_Owner_rec OUT NOCOPY Lead_Owner_rec_type
267 )
268 IS
269 BEGIN
270
271 x_Lead_Owner_rec.Lead_Owner_id := FND_API.G_MISS_NUM;
272 x_Lead_Owner_rec.country := FND_API.G_MISS_CHAR;
273 x_Lead_Owner_rec.from_postal_code := FND_API.G_MISS_CHAR;
274 x_Lead_Owner_rec.to_postal_code := FND_API.G_MISS_CHAR;
275 x_Lead_Owner_rec.CM_resource_id := FND_API.G_MISS_NUM;
276 x_Lead_Owner_rec.last_update_date := FND_API.G_MISS_DATE;
277 x_Lead_Owner_rec.last_updated_by := FND_API.G_MISS_NUM;
278 x_Lead_Owner_rec.creation_date := FND_API.G_MISS_DATE;
279 x_Lead_Owner_rec.created_by := FND_API.G_MISS_NUM;
280 x_Lead_Owner_rec.last_update_login := FND_API.G_MISS_NUM;
281 x_Lead_Owner_rec.object_version_number := FND_API.G_MISS_NUM;
282 x_Lead_Owner_rec.request_id := FND_API.G_MISS_NUM;
283 x_Lead_Owner_rec.program_application_id := FND_API.G_MISS_NUM;
284 x_Lead_Owner_rec.program_id := FND_API.G_MISS_NUM;
285 x_Lead_Owner_rec.program_update_date := FND_API.G_MISS_DATE;
286
287 END Init_Lead_Owner_Rec;
288
289
290 ---------------------------------------------------------------------
291 -- PROCEDURE
292 -- Complete_Lead_Owner_Rec
293 --
294 ---------------------------------------------------------------------
295 PROCEDURE Complete_Lead_Owner_Rec(
296 p_Lead_Owner_rec IN Lead_Owner_rec_type
297 ,x_complete_rec OUT NOCOPY Lead_Owner_rec_type
298 )
299 IS
300
301 CURSOR c_Lead_Owner IS
302 SELECT *
303 FROM AS_SALES_LEAD_OWNERS
304 WHERE Lead_Owner_id = p_Lead_Owner_rec.Lead_Owner_id;
305
306 l_Lead_Owner_rec c_Lead_Owner%ROWTYPE;
307
308 BEGIN
309
310 x_complete_rec := p_Lead_Owner_rec;
311
312 OPEN c_Lead_Owner;
313 FETCH c_Lead_Owner INTO l_Lead_Owner_rec;
314 IF c_Lead_Owner%NOTFOUND THEN
315 CLOSE c_Lead_Owner;
316 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
317 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
318 FND_MSG_PUB.add;
319 END IF;
320 RAISE FND_API.g_exc_error;
321 END IF;
322 CLOSE c_Lead_Owner;
323
324 IF p_Lead_Owner_rec.request_id = FND_API.G_MISS_NUM THEN
325 x_complete_rec.request_id := l_Lead_Owner_rec.request_id;
326 END IF;
327
328 IF p_Lead_Owner_rec.program_application_id = FND_API.G_MISS_NUM THEN
329 x_complete_rec.program_application_id := l_Lead_Owner_rec.program_application_id;
330 END IF;
331
332 IF p_Lead_Owner_rec.program_id = FND_API.G_MISS_NUM THEN
333 x_complete_rec.program_id := l_Lead_Owner_rec.program_id;
334 END IF;
335
336 IF p_Lead_Owner_rec.program_update_date = FND_API.G_MISS_DATE THEN
337 x_complete_rec.program_update_date := l_Lead_Owner_rec.program_update_date;
338 END IF;
339
340 IF p_Lead_Owner_rec.country = FND_API.G_MISS_CHAR THEN
341 x_complete_rec.country := l_Lead_Owner_rec.country;
342 END IF;
343
344 IF p_Lead_Owner_rec.from_postal_code = FND_API.G_MISS_CHAR THEN
345 x_complete_rec.from_postal_code := l_Lead_Owner_rec.from_postal_code;
346 END IF;
347
348 IF p_Lead_Owner_rec.referral_type = FND_API.G_MISS_CHAR THEN
349 x_complete_rec.referral_type := l_Lead_Owner_rec.referral_type;
350 END IF;
351
352 IF p_Lead_Owner_rec.owner_flag = FND_API.G_MISS_CHAR THEN
353 x_complete_rec.owner_flag := l_Lead_Owner_rec.owner_flag;
354 END IF;
355
356 IF p_Lead_Owner_rec.to_postal_code = FND_API.G_MISS_CHAR THEN
357 x_complete_rec.to_postal_code := l_Lead_Owner_rec.to_postal_code;
358 END IF;
359
360 IF p_Lead_Owner_rec.object_version_number = FND_API.G_MISS_NUM THEN
361 x_complete_rec.object_version_number := l_Lead_Owner_rec.object_version_number;
362 END IF;
363
364
365 END Complete_Lead_Owner_Rec;
366
367 --------------------------------------------------------------------
368 -- PROCEDURE
369 -- Validate_Lead_Owner
370 --
371 --------------------------------------------------------------------
372 PROCEDURE Validate_Lead_Owner(
373 p_api_version IN NUMBER
374 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
375 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
376
377 ,x_return_status OUT NOCOPY VARCHAR2
378 ,x_msg_count OUT NOCOPY NUMBER
379 ,x_msg_data OUT NOCOPY VARCHAR2
380
381 ,p_Lead_Owner_rec IN Lead_Owner_rec_type
382 )
383 IS
384
385 l_api_version CONSTANT NUMBER := 1.0;
386 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Lead_Owner';
387 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
388 l_return_status VARCHAR2(1);
389
390 BEGIN
391
392 ----------------------- initialize --------------------
393 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
394
395 IF FND_API.to_boolean(p_init_msg_list) THEN
396 FND_MSG_PUB.initialize;
397 END IF;
398
399 IF NOT FND_API.compatible_api_call(
400 l_api_version,
401 p_api_version,
402 l_api_name,
403 g_pkg_name
404 ) THEN
405 RAISE FND_API.g_exc_unexpected_error;
406 END IF;
407
408 x_return_status := FND_API.g_ret_sts_success;
409
410 ---------------------- validate ------------------------
411 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': check items');
412 --DBMS_output.put_line(l_full_name||': start item validate');
413
414 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
415 Check_Lead_Owner_items(
416 p_Lead_Owner_rec => p_Lead_Owner_rec,
420
417 p_validation_mode => JTF_PLSQL_API.g_create,
418 x_return_status => l_return_status
419 );
421 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
422 RAISE FND_API.g_exc_unexpected_error;
423 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
424 RAISE FND_API.g_exc_error;
425 END IF;
426 END IF;
427
428 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': check record');
429 --DBMS_output.put_line(l_full_name||': start record validate');
430
431 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
432 Check_Lead_Owner_rec(
433 p_Lead_Owner_rec => p_Lead_Owner_rec,
434 p_complete_rec => NULL,
435 x_return_status => l_return_status
436 );
437
438 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
439 RAISE FND_API.g_exc_unexpected_error;
440 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
441 RAISE FND_API.g_exc_error;
442 END IF;
443 END IF;
444
445 -------------------- finish --------------------------
446 FND_MSG_PUB.count_and_get(
447 p_encoded => FND_API.g_false,
448 p_count => x_msg_count,
449 p_data => x_msg_data
450 );
451
452 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
453
454 EXCEPTION
455
456 WHEN FND_API.g_exc_error THEN
457 x_return_status := FND_API.g_ret_sts_error;
458 FND_MSG_PUB.count_and_get(
459 p_encoded => FND_API.g_false,
460 p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463
464 WHEN FND_API.g_exc_unexpected_error THEN
465 x_return_status := FND_API.g_ret_sts_unexp_error ;
466 FND_MSG_PUB.count_and_get(
467 p_encoded => FND_API.g_false,
468 p_count => x_msg_count,
469 p_data => x_msg_data
470 );
471
472 WHEN OTHERS THEN
473 x_return_status := FND_API.g_ret_sts_unexp_error;
474 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
475 THEN
476 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
477 END IF;
478
479 FND_MSG_PUB.count_and_get(
480 p_encoded => FND_API.g_false,
481 p_count => x_msg_count,
482 p_data => x_msg_data
483 );
484
485 END Validate_Lead_Owner;
486
487
488
489
490
491 ---------------------------------------------------------------------
492 -- PROCEDURE
493 -- Create_Lead_Owner
494 --
495 -- PURPOSE
496 -- Create a new Lead Owner record
497 --
498 -- PARAMETERS
499 -- p_Lead_Owner_rec: the new record to be inserted
500 -- x_Lead_Owner_id: return the Lead_Owner_id of the new record.
501 --
502 -- NOTES
503 -- 1. object_version_number will be set to 1.
504 -- 2. If Lead_Owner_id is passed in, the uniqueness will be checked.
505 -- Raise exception in case of duplicates.
506 -- 3. If Lead_Owner_id is not passed in, generate a unique one from
507 -- the sequence.
508 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
509 -- Raise exception for invalid flag.
510 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
511 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
512 ---------------------------------------------------------------------
513 PROCEDURE Create_Lead_Owner(
514 p_api_version IN NUMBER
515 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
516 ,p_commit IN VARCHAR2 := FND_API.g_false
517 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
518
519 ,x_return_status OUT NOCOPY VARCHAR2
520 ,x_msg_count OUT NOCOPY NUMBER
521 ,x_msg_data OUT NOCOPY VARCHAR2
522
523 ,p_Lead_Owner_rec IN Lead_Owner_rec_type
524 ,x_Lead_Owner_id OUT NOCOPY NUMBER
525 )
526 IS
527
528 l_api_version CONSTANT NUMBER := 1.0;
529 l_api_name CONSTANT VARCHAR2(30) := 'Create_Lead_Owner';
530 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
531
532 l_return_status VARCHAR2(1);
533 l_Lead_Owner_rec Lead_Owner_rec_type := p_Lead_Owner_rec;
534
535 l_object_version_number NUMBER := 1;
536
537 l_uniqueness_check VARCHAR2(10);
538
539
540 -- Cursor to get the sequence for Lead_Owner
541 CURSOR c_Lead_Owner_seq IS
542 SELECT AS_SALES_LEAD_OWNERS_S.NEXTVAL FROM DUAL;
543
544 -- Cursor to validate the uniqueness
545 CURSOR c_count(cv_Lead_Owner_id IN NUMBER) IS
546 SELECT 'ANYTHING'
547 FROM AS_SALES_LEAD_OWNERS
548 WHERE Lead_Owner_id = cv_Lead_Owner_id;
549
550
551 BEGIN
552
553 --------------------- initialize -----------------------
554 SAVEPOINT Create_Lead_Owner;
555
556 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
557
558
559 IF FND_API.to_boolean(p_init_msg_list) THEN
560 FND_MSG_PUB.initialize;
561 END IF;
562
566 l_api_name,
563 IF NOT FND_API.compatible_api_call(
564 l_api_version,
565 p_api_version,
567 g_pkg_name
568 ) THEN
569 RAISE FND_API.g_exc_unexpected_error;
570 END IF;
571
572 x_return_status := FND_API.g_ret_sts_success;
573
574 ----------------------- validate -----------------------
575 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': validate');
576
577 --DBMS_output.put_line(l_full_name||': validate');
578 Validate_Lead_Owner(
579 p_api_version => l_api_version,
580 p_init_msg_list => p_init_msg_list,
581 p_validation_level => p_validation_level,
582 x_return_status => l_return_status,
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 p_Lead_Owner_rec => l_Lead_Owner_rec
586 );
587
588
589 IF l_return_status = FND_API.g_ret_sts_error THEN
590 RAISE FND_API.g_exc_error;
591 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
592 RAISE FND_API.g_exc_unexpected_error;
593 END IF;
594
595
596 --DBMS_output.put_line(l_full_name||': back validate');
597
598 -------------------------- insert --------------------------
599 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': insert');
600
601 IF l_Lead_Owner_rec.Lead_Owner_id IS NULL THEN
602 LOOP
603 -- Get the identifier
604 OPEN c_Lead_Owner_seq;
605 FETCH c_Lead_Owner_seq INTO l_Lead_Owner_rec.Lead_Owner_id;
606 CLOSE c_Lead_Owner_seq;
607
608 -- Check the uniqueness of the identifier
609 OPEN c_count(l_Lead_Owner_rec.Lead_Owner_id);
610 FETCH c_count INTO l_uniqueness_check;
611 -- Exit when the identifier uniqueness is established
612 EXIT WHEN c_count%ROWCOUNT = 0;
613 CLOSE c_count;
614 END LOOP;
615 END IF;
616
617 IF (p_Lead_Owner_rec.country is null) THEN
618 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
619 FND_MESSAGE.set_name('PV', 'PV_NO_COUNTRY_FOUND');
620 FND_MSG_PUB.add;
621 END IF;
622 RAISE FND_API.g_exc_error;
623 END IF;
624
625 --DBMS_output.put_line(l_full_name||': start insert');
626 INSERT INTO AS_SALES_LEAD_OWNERS (
627 lead_Owner_id
628 ,category
629 ,country
630 ,from_postal_code
631 ,to_postal_code
632 ,CM_resource_id
633 ,referral_type
634 ,owner_flag
635 ,last_update_date
636 ,last_updated_by
637 ,creation_date
638 ,created_by
639 ,last_update_login
640 ,request_id
641 ,program_application_id
642 ,program_id
643 ,program_update_date
644 ,object_version_number
645 )
646 VALUES (
647 l_Lead_Owner_rec.Lead_Owner_id
648 ,l_Lead_Owner_rec.category
649 ,l_Lead_Owner_rec.country
650 ,l_Lead_Owner_rec.from_postal_code
651 ,l_Lead_Owner_rec.to_postal_code
652 ,l_Lead_Owner_rec.CM_resource_id
653 ,l_Lead_Owner_rec.referral_type
654 ,l_Lead_Owner_rec.owner_flag
655 ,SYSDATE -- LAST_UPDATE_DATE
656 ,NVL(FND_GLOBAL.user_id,-1) -- LAST_UPDATED_BY
657 ,SYSDATE -- CREATION_DATE
658 ,NVL(FND_GLOBAL.user_id,-1) -- CREATED_BY
659 ,NVL(FND_GLOBAL.conc_login_id,-1) -- LAST_UPDATE_LOGIN
660 ,l_Lead_Owner_rec.request_id
661 ,l_Lead_Owner_rec.program_application_id
662 ,l_Lead_Owner_rec.program_id
663 ,l_Lead_Owner_rec.program_update_date
664 ,l_object_version_number -- object_version_number
665 );
666
667 ------------------------- finish -------------------------------
668 x_Lead_Owner_id := l_Lead_Owner_rec.Lead_Owner_id;
669
670 IF l_return_status = FND_API.g_ret_sts_error THEN
671 RAISE FND_API.g_exc_error;
672 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
673 RAISE FND_API.g_exc_unexpected_error;
674 END IF;
675
676
677 -- Check for commit
678 IF FND_API.to_boolean(p_commit) THEN
679 COMMIT;
680 END IF;
681
682 FND_MSG_PUB.count_and_get(
683 p_encoded => FND_API.g_false,
684 p_count => x_msg_count,
685 p_data => x_msg_data
686 );
687
688 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
689
690 EXCEPTION
691
692 WHEN FND_API.g_exc_error THEN
693 ROLLBACK TO Create_Lead_Owner;
694 x_return_status := FND_API.g_ret_sts_error;
695 FND_MSG_PUB.count_and_get (
696 p_encoded => FND_API.g_false
697 ,p_count => x_msg_count
698 ,p_data => x_msg_data
699 );
700
701 WHEN FND_API.g_exc_unexpected_error THEN
702 ROLLBACK TO Create_Lead_Owner;
703 x_return_status := FND_API.g_ret_sts_unexp_error ;
704 FND_MSG_PUB.count_and_get (
705 p_encoded => FND_API.g_false
709
706 ,p_count => x_msg_count
707 ,p_data => x_msg_data
708 );
710
711 WHEN OTHERS THEN
712 ROLLBACK TO Create_Lead_Owner;
713 x_return_status := FND_API.g_ret_sts_unexp_error ;
714
715 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
716 THEN
717 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
718 END IF;
719
720 FND_MSG_PUB.count_and_get(
721 p_encoded => FND_API.g_false
722 ,p_count => x_msg_count
723 ,p_data => x_msg_data
724 );
725
726 END Create_Lead_Owner;
727
728
729 ---------------------------------------------------------------
730 -- PROCEDURE
731 -- Delete_Lead_Owner
732 --
733 ---------------------------------------------------------------
734 PROCEDURE Delete_Lead_Owner(
735 p_api_version IN NUMBER
736 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
737 ,p_commit IN VARCHAR2 := FND_API.g_false
738
739 ,x_return_status OUT NOCOPY VARCHAR2
740 ,x_msg_count OUT NOCOPY NUMBER
741 ,x_msg_data OUT NOCOPY VARCHAR2
742
743 ,p_Lead_Owner_id IN NUMBER
744 ,p_object_version IN NUMBER
745
746 )
747 IS
748
749 l_api_version CONSTANT NUMBER := 1.0;
750 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Lead_Owner';
751 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
752
753 BEGIN
754
755 --------------------- initialize -----------------------
756 SAVEPOINT Delete_Lead_Owner;
757
758 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
759
760 IF FND_API.to_boolean(p_init_msg_list) THEN
761 FND_MSG_PUB.initialize;
762 END IF;
763
764 IF NOT FND_API.compatible_api_call(
765 l_api_version,
766 p_api_version,
767 l_api_name,
768 g_pkg_name
769 ) THEN
770 RAISE FND_API.g_exc_unexpected_error;
771 END IF;
772
773 x_return_status := FND_API.G_RET_STS_SUCCESS;
774
775 ------------------------ delete ------------------------
776 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': delete');
777
778 DELETE FROM AS_SALES_LEAD_OWNERS
779 WHERE Lead_Owner_id = p_Lead_Owner_id
780 AND object_version_number = p_object_version;
781
782 IF (SQL%NOTFOUND) THEN
783 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
784 THEN
785 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
786 FND_MSG_PUB.add;
787 END IF;
788 RAISE FND_API.g_exc_error;
789 END IF;
790
791 -------------------- finish --------------------------
792 IF FND_API.to_boolean(p_commit) THEN
793 COMMIT;
794 END IF;
795
796 FND_MSG_PUB.count_and_get(
797 p_encoded => FND_API.g_false,
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801
802 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
803
804 EXCEPTION
805
806 WHEN FND_API.g_exc_error THEN
807 ROLLBACK TO Delete_Lead_Owner;
808 x_return_status := FND_API.g_ret_sts_error;
809 FND_MSG_PUB.count_and_get(
810 p_encoded => FND_API.g_false,
811 p_count => x_msg_count,
812 p_data => x_msg_data
813 );
814
815 WHEN FND_API.g_exc_unexpected_error THEN
816 ROLLBACK TO Delete_Lead_Owner;
817 x_return_status := FND_API.g_ret_sts_unexp_error ;
818 FND_MSG_PUB.count_and_get(
819 p_encoded => FND_API.g_false,
820 p_count => x_msg_count,
821 p_data => x_msg_data
822 );
823
824 WHEN OTHERS THEN
825 ROLLBACK TO Delete_Lead_Owner;
826 x_return_status := FND_API.g_ret_sts_unexp_error ;
827
828 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
829 THEN
830 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
831 END IF;
832
833 FND_MSG_PUB.count_and_get(
834 p_encoded => FND_API.g_false,
835 p_count => x_msg_count,
836 p_data => x_msg_data
837 );
838
839 END Delete_Lead_Owner;
840
841
842 -------------------------------------------------------------------
843 -- PROCEDURE
844 -- Lock_Lead_Owner
845 -- Not currently used.
846 --------------------------------------------------------------------
847 /*
848 PROCEDURE Lock_Lead_Owner(
849 p_api_version IN NUMBER
850 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
851
852 ,x_return_status OUT NOCOPY VARCHAR2
853 ,x_msg_count OUT NOCOPY NUMBER
854 ,x_msg_data OUT NOCOPY VARCHAR2
855
856 ,p_Lead_Owner_id IN NUMBER
857 ,p_object_version IN NUMBER
858 )
859 IS
860
861 l_api_version CONSTANT NUMBER := 1.0;
865 l_Lead_Owner_id NUMBER;
862 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Lead_Owner';
863 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
864
866
867 CURSOR c_Lead_Owner IS
868 SELECT Lead_Owner_id
869 FROM AS_SALES_LEAD_OWNERS
870 WHERE Lead_Owner_id = p_Lead_Owner_id
871 AND object_version_number = p_object_version
872 FOR UPDATE OF Lead_Owner_id NOWAIT;
873
874 BEGIN
875
876 -------------------- initialize ------------------------
877 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
878
879 IF FND_API.to_boolean(p_init_msg_list) THEN
880 FND_MSG_PUB.initialize;
881 END IF;
882
883 IF NOT FND_API.compatible_api_call(
884 l_api_version,
885 p_api_version,
886 l_api_name,
887 g_pkg_name
888 ) THEN
889 RAISE FND_API.g_exc_unexpected_error;
890 END IF;
891
892 x_return_status := FND_API.G_RET_STS_SUCCESS;
893
894 ------------------------ lock -------------------------
895 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': lock');
896
897 OPEN c_Lead_Owner;
898 FETCH c_Lead_Owner INTO l_Lead_Owner_id;
899 IF (c_Lead_Owner%NOTFOUND) THEN
900 CLOSE c_Lead_Owner;
901 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
902 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
903 FND_MSG_PUB.add;
904 END IF;
905 RAISE FND_API.g_exc_error;
906 END IF;
907 CLOSE c_Lead_Owner;
908
909
910 -------------------- finish --------------------------
911 FND_MSG_PUB.count_and_get(
912 p_encoded => FND_API.g_false,
913 p_count => x_msg_count,
914 p_data => x_msg_data
915 );
916
917 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
918
919 EXCEPTION
920
921 WHEN AS_Utility_PVT.resource_locked THEN
922 x_return_status := FND_API.g_ret_sts_error;
923 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
924 FND_MESSAGE.set_name('PV', 'PV_RESOURCE_LOCKED');
925 FND_MSG_PUB.add;
926 END IF;
927
928 FND_MSG_PUB.count_and_get(
929 p_encoded => FND_API.g_false,
930 p_count => x_msg_count,
931 p_data => x_msg_data
932 );
933
934 WHEN FND_API.g_exc_error THEN
935 x_return_status := FND_API.g_ret_sts_error;
936 FND_MSG_PUB.count_and_get(
937 p_encoded => FND_API.g_false,
938 p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941
942 WHEN FND_API.g_exc_unexpected_error THEN
943 x_return_status := FND_API.g_ret_sts_unexp_error ;
944 FND_MSG_PUB.count_and_get(
945 p_encoded => FND_API.g_false,
946 p_count => x_msg_count,
947 p_data => x_msg_data
948 );
949
950 WHEN OTHERS THEN
951 x_return_status := FND_API.g_ret_sts_unexp_error ;
952 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
953 THEN
954 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
955 END IF;
956
957 FND_MSG_PUB.count_and_get(
958 p_encoded => FND_API.g_false,
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962
963 END Lock_Lead_Owner;
964 */
965
966 ---------------------------------------------------------------------
967 -- PROCEDURE
968 -- Update_Lead_Owner
969 ----------------------------------------------------------------------
970 PROCEDURE Update_Lead_Owner(
971 p_api_version IN NUMBER
972 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
973 ,p_commit IN VARCHAR2 := FND_API.g_false
974 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
975
976 ,x_return_status OUT NOCOPY VARCHAR2
977 ,x_msg_count OUT NOCOPY NUMBER
978 ,x_msg_data OUT NOCOPY VARCHAR2
979 ,p_Lead_Owner_rec IN Lead_Owner_rec_type
980
981 )
982 IS
983
984 l_api_version CONSTANT NUMBER := 1.0;
985 l_api_name CONSTANT VARCHAR2(30) := 'Update_Lead_Owner';
986 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
987
988 l_Lead_Owner_rec Lead_Owner_rec_type;
989 l_return_status VARCHAR2(1);
990 l_mode VARCHAR2(30) := 'UPDATE';
991
992
993 BEGIN
994
995 -------------------- initialize -------------------------
996 SAVEPOINT Update_Lead_Owner;
997
998 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
999
1000 IF FND_API.to_boolean(p_init_msg_list) THEN
1001 FND_MSG_PUB.initialize;
1002 END IF;
1003
1004 IF NOT FND_API.compatible_api_call(
1005 l_api_version,
1006 p_api_version,
1007 l_api_name,
1008 g_pkg_name
1009 ) THEN
1010 RAISE FND_API.g_exc_unexpected_error;
1011 END IF;
1012
1016 ----------------------- validate ----------------------
1013 x_return_status := FND_API.G_RET_STS_SUCCESS;
1014
1015
1017 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': validate');
1018
1019 IF (p_Lead_Owner_rec.country is null) THEN
1020 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1021 FND_MESSAGE.set_name('PV', 'PV_NO_COUNTRY_FOUND');
1022 FND_MSG_PUB.add;
1023 END IF;
1024 RAISE FND_API.g_exc_error;
1025 END IF;
1026
1027 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1028 Check_Lead_Owner_Items(
1029 p_Lead_Owner_Rec => p_Lead_Owner_rec,
1030 p_validation_mode => JTF_PLSQL_API.g_update,
1031 x_return_status => l_return_status
1032 );
1033
1034 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1035 RAISE FND_API.g_exc_unexpected_error;
1036 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1037 RAISE FND_API.g_exc_error;
1038 END IF;
1039 END IF;
1040
1041 -- replace g_miss_char/num/date with current column values
1042 Complete_Lead_Owner_rec(p_Lead_Owner_rec, l_Lead_Owner_rec);
1043
1044 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1045 Check_Lead_Owner_rec(
1046 p_Lead_Owner_rec => p_Lead_Owner_rec,
1047 p_complete_rec => l_Lead_Owner_rec,
1048 p_mode => l_mode,
1049 x_return_status => l_return_status
1050 );
1051
1052 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1053 RAISE FND_API.g_exc_unexpected_error;
1054 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1055 RAISE FND_API.g_exc_error;
1056 END IF;
1057 END IF;
1058
1059
1060 -------------------------- update --------------------
1061 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': update');
1062
1063 UPDATE AS_SALES_LEAD_OWNERS SET
1064 category = l_Lead_Owner_rec.category
1065 ,country = l_Lead_Owner_rec.country
1066 ,from_postal_code = l_Lead_Owner_rec.from_postal_code
1067 ,to_postal_code = l_Lead_Owner_rec.to_postal_code
1068 ,cm_resource_id = l_Lead_Owner_rec.CM_resource_id
1069 ,referral_type = l_Lead_Owner_rec.referral_type
1070 ,owner_flag = l_Lead_Owner_rec.owner_flag
1071 ,last_update_date = SYSDATE
1072 ,last_updated_by = NVL(FND_GLOBAL.user_id,-1)
1073 ,last_update_login = NVL(FND_GLOBAL.conc_login_id,-1)
1074 ,request_id = l_Lead_Owner_rec.request_id
1075 ,program_application_id = l_Lead_Owner_rec.program_application_id
1076 ,program_id = l_Lead_Owner_rec.program_id
1077 ,program_update_date = l_Lead_Owner_rec.program_update_date
1078 ,object_version_number = l_Lead_Owner_rec.object_version_number + 1
1079 WHERE Lead_Owner_id = l_Lead_Owner_rec.Lead_Owner_id
1080 AND object_version_number = l_Lead_Owner_rec.object_version_number;
1081
1082 IF (SQL%NOTFOUND) THEN
1083 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1084 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
1085 FND_MSG_PUB.add;
1086 END IF;
1087 RAISE FND_API.g_exc_error;
1088 END IF;
1089
1090 -------------------- finish --------------------------
1091
1092 -- Check for commit
1093 IF FND_API.to_boolean(p_commit) THEN
1094 COMMIT;
1095 END IF;
1096
1097 FND_MSG_PUB.count_and_get(
1098 p_encoded => FND_API.g_false,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102
1103 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
1104
1105 EXCEPTION
1106
1107 WHEN FND_API.g_exc_error THEN
1108 ROLLBACK TO Update_Lead_Owner;
1109 x_return_status := FND_API.g_ret_sts_error;
1110 FND_MSG_PUB.count_and_get(
1111 p_encoded => FND_API.g_false,
1112 p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115
1116 WHEN FND_API.g_exc_unexpected_error THEN
1117 ROLLBACK TO Update_Lead_Owner;
1118 x_return_status := FND_API.g_ret_sts_unexp_error ;
1119 FND_MSG_PUB.count_and_get(
1120 p_encoded => FND_API.g_false,
1121 p_count => x_msg_count,
1122 p_data => x_msg_data
1123 );
1124
1125 WHEN OTHERS THEN
1126 ROLLBACK TO Update_Lead_Owner;
1127 x_return_status := FND_API.g_ret_sts_unexp_error ;
1128
1129 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1130 THEN
1131 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1132 END IF;
1133
1134 FND_MSG_PUB.count_and_get(
1135 p_encoded => FND_API.g_false,
1136 p_count => x_msg_count,
1137 p_data => x_msg_data
1138 );
1139
1140 END Update_Lead_Owner;
1141
1142
1143 ---------------------------------------------------------------------
1144 -- PROCEDURE
1145 -- Get_Salesreps
1149 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1146 ----------------------------------------------------------------------
1147 PROCEDURE Get_Salesreps(
1148 p_api_version IN NUMBER
1150 ,p_commit IN VARCHAR2 := FND_API.g_false
1151 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1152
1153 ,p_sales_lead_id IN NUMBER
1154 ,x_salesreps_tbl OUT NOCOPY lead_owner_rec_tbl_type
1155
1156 ,x_return_status OUT NOCOPY VARCHAR2
1157 ,x_msg_count OUT NOCOPY NUMBER
1158 ,x_msg_data OUT NOCOPY VARCHAR2
1159 )
1160
1161 IS
1162
1163 l_api_version CONSTANT NUMBER := 2.0; -- As all the package and procedure are using ver 2
1164 l_api_name CONSTANT VARCHAR2(30) := 'Get_Salesreps';
1165 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1166
1167 l_lead_owner_rec lead_owner_rec_type;
1168 l_resource_id l_lead_owner_rec.cm_resource_id%type;
1169 l_owner_flag l_lead_owner_rec.owner_flag%type;
1170
1171 l_sql_text_postal VARCHAR2(1000);
1172 l_sql_text_country VARCHAR2(1000);
1173
1174
1175 CURSOR lc_lead_owners_postal(pc_lead_id NUMBER) IS
1176 SELECT aso.cm_resource_id, aso.owner_flag
1177 FROM AS_SALES_LEADS LEAD, HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, AS_SALES_LEAD_OWNERS ASO
1178 WHERE LEAD.SALES_LEAD_ID = pc_lead_id AND LEAD.ADDRESS_ID = PS.PARTY_SITE_ID(+)
1179 AND PS.LOCATION_ID = LOC.LOCATION_ID(+) AND ASO.COUNTRY = LOC.COUNTRY
1180 AND LOC.POSTAL_CODE BETWEEN ASO.FROM_POSTAL_CODE AND ASO.TO_POSTAL_CODE
1181 AND LEAD.REFERRAL_TYPE = ASO.REFERRAL_TYPE;
1182
1183 CURSOR lc_lead_owners_country(pc_lead_id NUMBER) IS
1184 SELECT aso.cm_resource_id, aso.owner_flag
1185 FROM AS_SALES_LEADS LEAD, HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, AS_SALES_LEAD_OWNERS ASO
1186 WHERE LEAD.SALES_LEAD_ID = pc_lead_id AND LEAD.ADDRESS_ID = PS.PARTY_SITE_ID(+)
1187 AND PS.LOCATION_ID = LOC.LOCATION_ID(+) AND ASO.COUNTRY = LOC.COUNTRY
1188 -- AND LOC.POSTAL_CODE BETWEEN ASO.FROM_POSTAL_CODE AND ASO.TO_POSTAL_CODE);
1189 AND LEAD.REFERRAL_TYPE = ASO.REFERRAL_TYPE;
1190
1191
1192 BEGIN
1193
1194 -- Initialize the return table
1195 x_salesreps_tbl := lead_owner_rec_tbl_type();
1196
1197
1198 -------------------- initialize -------------------------
1199 SAVEPOINT Get_Salesreps;
1200
1201 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
1202
1203 IF FND_API.to_boolean(p_init_msg_list) THEN
1204 FND_MSG_PUB.initialize;
1205 END IF;
1206
1207 IF NOT FND_API.compatible_api_call(
1208 l_api_version,
1209 p_api_version,
1210 l_api_name,
1211 g_pkg_name
1212 ) THEN
1213 RAISE FND_API.g_exc_unexpected_error;
1214 END IF;
1215
1216 x_return_status := FND_API.G_RET_STS_SUCCESS;
1217
1218 ----------------------- validate ----------------------
1219 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': validate');
1220
1221 IF (p_sales_lead_id is null) THEN
1222 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1223 FND_MESSAGE.set_name('PV', 'PV_NO_COUNTRY_FOUND');
1224 FND_MSG_PUB.add;
1225 END IF;
1226 RAISE FND_API.g_exc_error;
1227 ELSE
1228 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1229 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1230 fnd_message.Set_Token('TEXT', 'Sales lead id : ' || p_sales_lead_id);
1231 fnd_msg_pub.Add;
1232 END IF;
1233
1234 END IF;
1235
1236 open lc_lead_owners_postal(p_sales_lead_id);
1237 loop
1238 fetch lc_lead_owners_postal into l_resource_id, l_owner_flag;
1239 exit when lc_lead_owners_postal%notfound;
1240
1241 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1242 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1243 fnd_message.Set_Token('TEXT', 'Postal : salesforce_id : ' || l_resource_id || ' owner : ' || l_owner_flag);
1244 fnd_msg_pub.Add;
1245 END IF;
1246
1247 x_salesreps_tbl.extend;
1248 x_salesreps_tbl(x_salesreps_tbl.last).cm_resource_id := l_resource_id;
1249 x_salesreps_tbl(x_salesreps_tbl.last).owner_flag := l_owner_flag;
1250 end loop;
1251 close lc_lead_owners_postal;
1252
1253 -- check if the tablehas any data in it or not
1254 if (x_salesreps_tbl.count < 1) then
1255
1256 open lc_lead_owners_country(p_sales_lead_id);
1257 loop
1258 fetch lc_lead_owners_country into l_resource_id, l_owner_flag;
1259 exit when lc_lead_owners_country%notfound;
1260
1261 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1262 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1263 fnd_message.Set_Token('TEXT', 'Country : salesforce_id : ' || l_resource_id || ' owner : ' || l_owner_flag);
1264 fnd_msg_pub.Add;
1265 END IF;
1266
1267 x_salesreps_tbl.extend;
1268 x_salesreps_tbl(x_salesreps_tbl.last).cm_resource_id := l_resource_id;
1269 x_salesreps_tbl(x_salesreps_tbl.last).owner_flag := l_owner_flag;
1270 end loop;
1271 close lc_lead_owners_country;
1272 end if;
1273 -- Check for commit
1277
1274 IF FND_API.to_boolean(p_commit) THEN
1275 COMMIT;
1276 END IF;
1278 FND_MSG_PUB.count_and_get(
1279 p_encoded => FND_API.g_false,
1280 p_count => x_msg_count,
1281 p_data => x_msg_data
1282 );
1283
1284 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
1285
1286 EXCEPTION
1287
1288 WHEN FND_API.g_exc_error THEN
1289 ROLLBACK TO Get_Salesreps;
1290 x_return_status := FND_API.g_ret_sts_error;
1291 FND_MSG_PUB.count_and_get(
1292 p_encoded => FND_API.g_false,
1293 p_count => x_msg_count,
1294 p_data => x_msg_data
1295 );
1296
1297 WHEN FND_API.g_exc_unexpected_error THEN
1298 ROLLBACK TO Get_Salesreps;
1299 x_return_status := FND_API.g_ret_sts_unexp_error ;
1300 FND_MSG_PUB.count_and_get(
1301 p_encoded => FND_API.g_false,
1302 p_count => x_msg_count,
1303 p_data => x_msg_data
1304 );
1305
1306 WHEN OTHERS THEN
1307 ROLLBACK TO Get_Salesreps;
1308 x_return_status := FND_API.g_ret_sts_unexp_error ;
1309
1310 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1311 THEN
1312 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1313 END IF;
1314
1315 FND_MSG_PUB.count_and_get(
1316 p_encoded => FND_API.g_false,
1317 p_count => x_msg_count,
1318 p_data => x_msg_data
1319 );
1320
1321 END Get_Salesreps;
1322
1323
1324 -- Added by Ajoy
1325
1326 ---------------------------------------------------------------------
1327 -- PROCEDURE
1328 -- Get_Immatured_Lead_Owner
1329 -- Search in AS_SALES_LEAD_OWNER table for the lead woner where
1330 -- CATEGORY = 'IMMATURED'
1331 ----------------------------------------------------------------------
1332 PROCEDURE Get_Immatured_Lead_Owner(
1333 p_api_version IN NUMBER
1334 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1335 ,p_commit IN VARCHAR2 := FND_API.g_false
1336 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1337
1338 ,p_sales_lead_id IN NUMBER
1339 ,x_salesforce_id OUT NOCOPY NUMBER
1340
1341 ,x_return_status OUT NOCOPY VARCHAR2
1342 ,x_msg_count OUT NOCOPY NUMBER
1343 ,x_msg_data OUT NOCOPY VARCHAR2
1344 )
1345
1346 IS
1347
1348 l_api_version CONSTANT NUMBER := 2.0; -- As all the package and procedure are using ver 2
1349 l_api_name CONSTANT VARCHAR2(30) := 'Get_Immatured_Lead_Owner';
1350 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1351
1352 l_lead_owner_rec lead_owner_rec_type;
1353 l_resource_id l_lead_owner_rec.cm_resource_id%type;
1354 l_owner_flag l_lead_owner_rec.owner_flag%type;
1355 l_salesreps_tbl lead_owner_rec_tbl_type;
1356
1357 l_sql_text_postal VARCHAR2(1000);
1358 l_sql_text_country VARCHAR2(1000);
1359
1360
1361 CURSOR lc_lead_owners_postal(pc_lead_id NUMBER) IS
1362 SELECT aso.cm_resource_id, aso.owner_flag
1363 FROM AS_SALES_LEADS LEAD, HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, AS_SALES_LEAD_OWNERS ASO
1364 WHERE LEAD.SALES_LEAD_ID = pc_lead_id AND LEAD.ADDRESS_ID = PS.PARTY_SITE_ID(+)
1365 AND PS.LOCATION_ID = LOC.LOCATION_ID(+) AND ASO.COUNTRY = LOC.COUNTRY
1366 AND LOC.POSTAL_CODE BETWEEN ASO.FROM_POSTAL_CODE AND ASO.TO_POSTAL_CODE
1367 AND ASO.CATEGORY = 'IMMATURED'
1368 AND ASO.OWNER_FLAG = 'Y';
1369
1370 CURSOR lc_lead_owners_country(pc_lead_id NUMBER) IS
1371 SELECT aso.cm_resource_id, aso.owner_flag
1372 FROM AS_SALES_LEADS LEAD, HZ_PARTY_SITES PS, HZ_LOCATIONS LOC, AS_SALES_LEAD_OWNERS ASO
1373 WHERE LEAD.SALES_LEAD_ID = pc_lead_id AND LEAD.ADDRESS_ID = PS.PARTY_SITE_ID(+)
1374 AND PS.LOCATION_ID = LOC.LOCATION_ID(+) AND ASO.COUNTRY = LOC.COUNTRY
1375 -- AND LOC.POSTAL_CODE BETWEEN ASO.FROM_POSTAL_CODE AND ASO.TO_POSTAL_CODE);
1376 AND ASO.CATEGORY = 'IMMATURED'
1377 AND ASO.OWNER_FLAG = 'Y';
1378
1379 BEGIN
1380
1381 -- Initialize the return table
1382 l_salesreps_tbl := lead_owner_rec_tbl_type();
1383
1384 -------------------- initialize -------------------------
1385 SAVEPOINT Get_Salesreps;
1386
1387 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
1388
1389 IF FND_API.to_boolean(p_init_msg_list) THEN
1390 FND_MSG_PUB.initialize;
1391 END IF;
1392
1393 IF NOT FND_API.compatible_api_call(
1394 l_api_version,
1395 p_api_version,
1396 l_api_name,
1397 g_pkg_name
1398 ) THEN
1399 RAISE FND_API.g_exc_unexpected_error;
1400 END IF;
1401
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403
1404 ----------------------- validate ----------------------
1405 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': validate');
1406
1407 IF (p_sales_lead_id is null) THEN
1408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1409 FND_MESSAGE.set_name('PV', 'PV_NO_COUNTRY_FOUND');
1410 FND_MSG_PUB.add;
1414 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1411 END IF;
1412 RAISE FND_API.g_exc_error;
1413 ELSE
1415 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1416 fnd_message.Set_Token('TEXT', 'Sales lead id : ' || p_sales_lead_id);
1417 fnd_msg_pub.Add;
1418 END IF;
1419
1420 END IF;
1421
1422 open lc_lead_owners_postal(p_sales_lead_id);
1423 loop
1424 fetch lc_lead_owners_postal into l_resource_id, l_owner_flag;
1425 exit when lc_lead_owners_postal%notfound;
1426
1427 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1428 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1429 fnd_message.Set_Token('TEXT', 'Postal : salesforce_id : ' || l_resource_id || ' owner : ' || l_owner_flag);
1430 fnd_msg_pub.Add;
1431 END IF;
1432
1433 l_salesreps_tbl.extend;
1434 l_salesreps_tbl(l_salesreps_tbl.last).cm_resource_id := l_resource_id;
1435 l_salesreps_tbl(l_salesreps_tbl.last).owner_flag := l_owner_flag;
1436 end loop;
1437 close lc_lead_owners_postal;
1438
1439 -- check if the tablehas any data in it or not
1440 if (l_salesreps_tbl.count < 1) then
1441
1442 open lc_lead_owners_country(p_sales_lead_id);
1443 loop
1444 fetch lc_lead_owners_country into l_resource_id, l_owner_flag;
1445 exit when lc_lead_owners_country%notfound;
1446
1447 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1448 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1449 fnd_message.Set_Token('TEXT', 'Country : salesforce_id : ' || l_resource_id || ' owner : ' || l_owner_flag);
1450 fnd_msg_pub.Add;
1451 END IF;
1452
1453 l_salesreps_tbl.extend;
1454 l_salesreps_tbl(l_salesreps_tbl.last).cm_resource_id := l_resource_id;
1455 l_salesreps_tbl(l_salesreps_tbl.last).owner_flag := l_owner_flag;
1456 end loop;
1457 close lc_lead_owners_country;
1458 end if;
1459
1460 IF (l_salesreps_tbl.count > 0) THEN
1461 x_salesforce_id := l_salesreps_tbl(1).cm_resource_id;
1462 ELSE
1463 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Getting marketing owner from ptofile ');
1464 x_salesforce_id := fnd_profile.value('AS_DEFAULT_LEAD_MKTG_OWNER');
1465 END IF;
1466
1467 -- Check for commit
1468 IF FND_API.to_boolean(p_commit) THEN
1469 COMMIT;
1470 END IF;
1471
1472 FND_MSG_PUB.count_and_get(
1473 p_encoded => FND_API.g_false,
1474 p_count => x_msg_count,
1475 p_data => x_msg_data
1476 );
1477
1478 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
1479
1480 EXCEPTION
1481
1482 WHEN FND_API.g_exc_error THEN
1483 ROLLBACK TO Get_Salesreps;
1484 x_return_status := FND_API.g_ret_sts_error;
1485 FND_MSG_PUB.count_and_get(
1486 p_encoded => FND_API.g_false,
1487 p_count => x_msg_count,
1488 p_data => x_msg_data
1489 );
1490
1491 WHEN FND_API.g_exc_unexpected_error THEN
1492 ROLLBACK TO Get_Salesreps;
1493 x_return_status := FND_API.g_ret_sts_unexp_error ;
1494 FND_MSG_PUB.count_and_get(
1495 p_encoded => FND_API.g_false,
1496 p_count => x_msg_count,
1497 p_data => x_msg_data
1498 );
1499
1500 WHEN OTHERS THEN
1501 ROLLBACK TO Get_Salesreps;
1502 x_return_status := FND_API.g_ret_sts_unexp_error ;
1503
1504 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1505 THEN
1506 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1507 END IF;
1508
1509 FND_MSG_PUB.count_and_get(
1510 p_encoded => FND_API.g_false,
1511 p_count => x_msg_count,
1512 p_data => x_msg_data
1513 );
1514
1515 END Get_Immatured_Lead_Owner;
1516
1517
1518 END AS_SALES_LEAD_OWNER;