1 PACKAGE BODY CSC_ROUTING_UTL AS
2 /* $Header: cscotmrb.pls 120.20 2006/11/02 18:59:49 hbchung ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Procedure: decode_ani
6 -- Usage: used by the Routing module to decode the string passed in the
7 -- IVT values to the constituent telephone number pieces required
8 -- by the get_customerid_from_ANI function.
9 -- Parameters: NI code received
10 -- Return: 4 substrings
11 --
12 ------------------------------------------------------------------------------
13 PROCEDURE decode_ANI (
14 p_ANI_CODE IN VARCHAR2,
15 p_country_code OUT NOCOPY VARCHAR2,
16 p_area_code OUT NOCOPY VARCHAR2,
17 p_phone_num OUT NOCOPY VARCHAR2,
18 p_phone_ext OUT NOCOPY VARCHAR2)
19 IS
20
21 BEGIN
22 --
23 -- this is a much more complex problem thatn is initially coded here.
24 -- Once the contents of the IVR ANI is defined, thois code will
25 -- have to be modified.
26 --
27 IF LENGTH(p_ANI_CODE) = 7 THEN
28 p_country_code := null;
29 p_area_code := null;
30 p_phone_num := p_ANI_CODE;
31 p_phone_ext := null;
32 ELSIF LENGTH(p_ANI_CODE) = 10 THEN
33 p_country_code := null;
34 p_area_code := substr(p_ANI_CODE,1,3);
35 p_phone_num := substr(p_ANI_CODE,4,7);
36 p_phone_ext := null;
37 ELSIF LENGTH(p_ANI_CODE) > 10 THEN
38 p_country_code := substr(p_ANI_CODE,1,3);
39 p_area_code := substr(p_ANI_CODE,4,3);
40 p_phone_num := substr(p_ANI_CODE,7,7);
41 p_phone_ext := null;
42 -- if none of the above pass everything in phone number field
43 ELSE
44 p_country_code := null;
45 p_area_code := null;
46 p_phone_num := p_ANI_CODE;
47 p_phone_ext := null;
48 END IF;
49
50 END decode_ANI;
51
52 ------------------------------------------------------------------------------
53 -- Function : Get_Customer_From_Account_Num
54 -- Usage : Used by the Routing module to get the customer ID from the
55 -- customer account number
56 -- Description : This function retrieves the primary party id associated
57 -- with the specified customer account number.
58 -- Parameters :
59 -- p_customer_account_number IN VARCHAR2(30) Required
60 --
61 -- Return : NUMBER
62 -- If the given customer does not exist, this function returns
63 -- NULL.
64 ------------------------------------------------------------------------------
65
66
67 FUNCTION Get_Customer_From_Account_Num
68 ( p_cust_acct_number IN VARCHAR2)
69 RETURN NUMBER IS
70
71 p_party_id NUMBER(15) := null;
72
73 --
74 -- get party ID given customer account number
75 --
76 CURSOR c_party_id (anum VARCHAR2) IS
77 SELECT party_id
78 FROM hz_cust_accounts
79 WHERE account_number = anum;
80 BEGIN
81
82 OPEN c_party_id (p_cust_acct_number);
83 FETCH c_party_id INTO p_party_id;
84 IF c_party_id%NOTFOUND THEN
85 CLOSE c_party_id;
86 RETURN NULL;
87 END IF;
88 CLOSE c_party_id;
89 RETURN to_number(p_party_id);
90
91 END Get_Customer_From_Account_Num;
92
93
94 ------------------------------------------------------------------------------
95 -- Function : Get_Customer_From_Contract_Num
96 -- Usage : Used by the Routing module to get the customer ID from the
97 -- contract number.
98 -- Description : This function retrieves a party ID from
99 -- OKC_ given a contract number. If there
100 -- is more than one customer associated with the given
101 -- contract, this function will return the first party ID
102 -- that it retrieves.
103 -- Parameters :
104 -- p_contract_number IN VARCHAR2(30) Required
105 --
106 -- Return : NUMBER
107 -- If there is no party associated with the given contract,
108 -- or the contract does not exist, this function returns NULL.
109 ------------------------------------------------------------------------------
110 FUNCTION Get_Customer_From_Contract_Num
111 ( p_contract_number IN VARCHAR2)
112 RETURN NUMBER IS
113
114 p_party_id NUMBER(15);
115 --
116 -- get party ID given a contract number
117 --
118 /********
119 CURSOR c_party_id IS
120 SELECT customer_id
121 FROM ra_customer_trx_all
122 WHERE invoice_number = p_contract_number;
123 ***/
124
125 BEGIN
126
127 /******
128 OPEN c_party_id;
129 FETCH c_party_id INTO p_party_id;
130 IF c_party_id%NOTFOUND THEN
131 CLOSE c_party_id;
132 RETURN NULL;
133 END IF;
134 CLOSE c_party_id;
135 ****/
136
137 RETURN p_party_id;
138
139 END Get_Customer_From_Contract_Num;
140
141 ------------------------------------------------------------------------------
142 -- Function : Get_Customer_From_Site_Num
143 -- Usage : Used by the Routing module to get the customer ID from the
144 -- party site number.
145 -- Description : This function retrieves a party ID from
146 -- HZ_PARTY_SITES given a site number.
147 -- Parameters :
148 -- p_site_number IN VARCHAR2(30) Required
149 --
150 -- Return : NUMBER
151 -- If there is no party associated with the given site number,
152 -- this function returns NULL.
153 ------------------------------------------------------------------------------
154 FUNCTION Get_Customer_From_Site_Num (p_site_number IN VARCHAR2)
155 Return Number IS
156
157 p_party_id NUMBER(15);
158
159 --
160 -- get party ID given site number
161 --
162 CURSOR c_party_id (snum VARCHAR2) IS
163 SELECT party_id
164 FROM hz_party_sites
165 WHERE party_site_number = snum;
166 BEGIN
167
168 OPEN c_party_id (p_site_number);
169 FETCH c_party_id INTO p_party_id;
170 IF c_party_id%NOTFOUND THEN
171 CLOSE c_party_id;
172 RETURN NULL;
173 END IF;
174 CLOSE c_party_id;
175 RETURN p_party_id;
176
177 END Get_Customer_From_Site_Num;
178
179 ------------------------------------------------------------------------------
180 -- Function : Get_Customer_From_Repair_Num
181 -- Usage : Used by the Routing module to get the customer ID from the
182 -- repair number.
183 -- Description : This function retrieves a party ID from
184 -- CSD_REPAIRS_V given a repair number. If there
185 -- Parameters :
186 -- p_repair_number IN VARCHAR2(30) Required
187 --
188 -- Return : NUMBER
189 -- If there is no party associated with the given repair,
190 -- or the repair does not exist, this function returns NULL.
191 ------------------------------------------------------------------------------
192 FUNCTION Get_Customer_From_Repair_Num
193 ( p_repair_number IN VARCHAR2)
194 RETURN NUMBER
195 IS
196 p_party_id NUMBER(15);
197 --
198 -- get party ID given a repair number
199 --
200 CURSOR c_party_id (repnum NUMBER) IS
201 SELECT inc.customer_id
202 FROM csd_repairs rep,
203 cs_incidents_v inc
204 WHERE rep.repair_number = repnum
205 AND rep.incident_id = inc.incident_id;
206
207 BEGIN
208
209 OPEN c_party_id (p_repair_number);
210 FETCH c_party_id INTO p_party_id;
211 IF c_party_id%NOTFOUND THEN
212 CLOSE c_party_id;
213 RETURN NULL;
214 END IF;
215 CLOSE c_party_id;
216
217 RETURN p_party_id;
218
219 END Get_Customer_From_Repair_Num;
220
221 ------------------------------------------------------------------------------
222 -- Function : Get_Customer_From_Defect_Num
223 -- Usage : Used by the Routing module to get the customer ID from the
224 -- defect number.
225 -- Description : This function retrieves a party ID from
226 -- CSS_DEF_DEFECTS_ALL_V given a defect number.
227 -- Parameters :
228 -- p_defect_number IN VARCHAR2(30) Required
229 --
230 -- Return : NUMBER
231 -- If there is no party associated with the given defect,
232 -- or the defect does not exist, this function returns NULL.
233 ------------------------------------------------------------------------------
234 FUNCTION Get_Customer_From_Defect_Num
235 ( p_defect_number IN VARCHAR2)
236 RETURN NUMBER IS
237
238 p_party_id NUMBER(15);
239 /*
240 --
241 -- get party ID given a defect number
242 --
243 CURSOR c_party_id (defnum VARCHAR2) IS
244 SELECT inc.incident_Id
245 FROM css_def_defects_all_v def,
246 cs_incident_links inl,
247 cs_incidents_v inc
248 WHERE def.defect_number = defnum
249 AND def.defect_id = inl.link_id
250 AND inl.from_incident_id = inc.incident_id;
251 */
252
253 BEGIN
254
255 /* OPEN c_party_id (p_defect_number);
256 FETCH c_party_id INTO p_party_id;
257 IF c_party_id%NOTFOUND THEN
258 CLOSE c_party_id;
259 RETURN NULL;
260 END IF;
261 CLOSE c_party_id;
262 */
263
264 RETURN p_party_id;
265
266 END Get_Customer_From_Defect_Num;
267
268 ------------------------------------------------------------------------------
269 -- Function : Get_Customer_From_ANI
270 -- Usage : Used by the Routing module to get the party ID from the
271 -- contact's phone number
272 -- Description : This function retrieves a party ID from the
273 -- HZ_CONTACT_POINTS table given a phone number of a customer contact.
274 -- If there is more than one customer associated with the given phone
275 -- number, this function will return the first customer ID that
276 -- it retrieves.
277 -- Parameters :
278 -- p_area_code IN VARCHAR2(10) Optional
279 -- p_phone_number IN VARCHAR2(40) Required
280 -- p_extension IN VARCHAR2(20) Optional
281 -- p_country_code IN VARCHAR2(20) Optional
282 --
283 -- Return : NUMBER
284 -- If there is no customer associated with the given phone
285 -- number, or the phone number does not exist (in the system),
286 -- this function returns NULL.
287 ------------------------------------------------------------------------------
288
289
290 FUNCTION Get_Customer_From_ANI
291 ( p_country_code IN VARCHAR2 := NULL,
292 p_area_code IN VARCHAR2 := NULL,
293 p_phone_number IN VARCHAR2,
294 p_extension IN VARCHAR2 := NULL)
295 RETURN NUMBER IS
296
297 p_party_id NUMBER(15);
298
299
300 --
301 -- get party ID given phone number, country code, area code and extension
302 --
303 /* Enhancement: 1685717 Parties are queried by ordering last_update_date on *//* hz_contact_points in descending order, so that active parties are selected first */
304
305 /*
306 CURSOR c_party_id (p_country VARCHAR2,
307 p_area VARCHAR2,
308 p_phone VARCHAR2,
309 p_ext VARCHAR2) IS
310 SELECT party.party_id
311 FROM HZ_PARTIES party,
312 hz_contact_points cp
313 WHERE cp.phone_number = p_phone AND
314 cp.phone_country_code = p_country AND
315 cp.phone_area_code = p_area AND
316 cp.phone_extension = p_ext AND
317 cp.owner_table_name = 'HZ_PARTIES' AND
318 cp.owner_table_id = party.party_id ;
319 */
320
321 CURSOR c_party_id (p_country VARCHAR2,
322 p_area VARCHAR2,
323 p_phone VARCHAR2,
324 p_ext VARCHAR2) IS
325 SELECT cp.owner_table_id party_id
326 FROM hz_contact_points cp
327 WHERE cp.phone_number = p_phone AND
328 cp.phone_country_code = p_country AND
329 cp.phone_area_code = p_area AND
330 cp.phone_extension = p_ext AND
331 cp.owner_table_name = 'HZ_PARTIES'
332 ORDER BY cp.last_update_date DESC;
333 --
334 -- get customer ID given phone number and extension
335 --
336 /*
337 CURSOR c_party_id_w_area_code (p_phone VARCHAR2, p_ext VARCHAR2) IS
338 SELECT party.party_id
339 FROM HZ_PARTIES party,
340 hz_contact_points cp
341 WHERE cp.phone_number = p_phone AND
342 cp.phone_extension = p_ext AND
343 cp.owner_table_name = 'HZ_PARTIES' AND
344 cp.owner_table_id = party.party_id ;
345 */
346
347 CURSOR c_party_id_w_area_code (p_phone VARCHAR2, p_ext VARCHAR2) IS
348 SELECT cp.owner_table_id party_id
349 FROM hz_contact_points cp
350 WHERE cp.phone_number = p_phone AND
351 cp.phone_extension = p_ext AND
352 cp.owner_table_name = 'HZ_PARTIES'
353 ORDER BY cp.last_update_date DESC;
354
355 --
356 -- get customer ID given phone number and area code
357 --
358 /*
359 CURSOR c_party_id_w_ctry_code (p_area VARCHAR2, p_phone VARCHAR2) IS
360 SELECT party.party_id
361 FROM HZ_PARTIES party,
362 hz_contact_points cp
363 WHERE cp.phone_number = p_phone AND
364 cp.phone_area_code = p_area AND
365 cp.owner_table_name = 'HZ_PARTIES' AND
366 cp.owner_table_id = party.party_id ;
367 */
368 CURSOR c_party_id_w_ctry_code (p_area VARCHAR2, p_phone VARCHAR2) IS
369 SELECT cp.owner_table_id party_id
370 FROM hz_contact_points cp
371 WHERE cp.phone_number = p_phone AND
372 cp.phone_area_code = p_area AND
373 cp.owner_table_name = 'HZ_PARTIES'
374 ORDER BY cp.last_update_date DESC;
375 --
376 -- get customer ID given phone number, area code and country code
377 --
378 /*
379 CURSOR c_party_id_w_extension (p_country VARCHAR2,
380 p_area VARCHAR2,
381 p_phone VARCHAR2) IS
382 SELECT party.party_id
383 FROM HZ_PARTIES party,
384 hz_contact_points cp
385 WHERE cp.phone_number = p_phone AND
386 cp.phone_area_code = p_area AND
387 cp.phone_country_code = p_country AND
388 cp.owner_table_name = 'HZ_PARTIES' AND
389 cp.owner_table_id = party.party_id;
390 */
391
392 CURSOR c_party_id_w_extension (p_country VARCHAR2,
393 p_area VARCHAR2,
394 p_phone VARCHAR2) IS
395 SELECT cp.owner_table_id party_id
396 FROM hz_contact_points cp
397 WHERE cp.phone_number = p_phone AND
398 cp.phone_area_code = p_area AND
399 cp.phone_country_code = p_country AND
400 cp.owner_table_name = 'HZ_PARTIES'
401 ORDER BY cp.last_update_date DESC;
402 --
403 -- get customer ID given phone number only
404 --
405 /*
406 CURSOR c_party_id_w_all (p_phone VARCHAR2) IS
407 SELECT party.party_id
408 FROM HZ_PARTIES party,
409 hz_contact_points cp
410 WHERE cp.phone_number = p_phone AND
411 cp.owner_table_name = 'HZ_PARTIES' AND
412 cp.owner_table_id = party.party_id ;
413 */
414
415 CURSOR c_party_id_w_all (p_phone VARCHAR2) IS
416 SELECT cp.owner_table_id party_id
417 FROM hz_contact_points cp
418 WHERE cp.phone_number = p_phone AND
419 cp.owner_table_name = 'HZ_PARTIES'
420 ORDER BY cp.last_update_date DESC;
421
422 BEGIN
423
424 IF p_country_code IS NOT NULL AND
425 p_area_code IS NOT NULL AND
426 p_extension IS NOT NULL THEN
427 OPEN c_party_id(p_country_code, p_area_code, p_phone_number, p_extension);
428 FETCH c_party_id INTO p_party_id;
429 IF c_party_id%NOTFOUND THEN
430 CLOSE c_party_id;
431 RETURN NULL;
432 END IF;
433 CLOSE c_party_id;
434 RETURN to_number(p_party_id);
435 END IF;
436
437 IF p_country_code IS NULL AND p_area_code IS NULL AND p_extension IS NOT NULL THEN
438 OPEN c_party_id_w_area_code(p_phone_number, p_extension);
439 FETCH c_party_id_w_area_code INTO p_party_id;
440 IF c_party_id_w_area_code%NOTFOUND THEN
441 CLOSE c_party_id_w_area_code;
442 RETURN NULL;
443 END IF;
444 CLOSE c_party_id_w_area_code;
445 RETURN to_number(p_party_id);
446 END IF;
447
448 IF p_country_code IS NULL AND p_area_code IS NOT NULL AND
449 p_extension IS NULL THEN
450 OPEN c_party_id_w_ctry_code(p_area_code, p_phone_number);
451 FETCH c_party_id_w_ctry_code INTO p_party_id;
452 IF c_party_id_w_ctry_code%NOTFOUND THEN
453 CLOSE c_party_id_w_ctry_code;
454 RETURN NULL;
455 END IF;
456 CLOSE c_party_id_w_ctry_code;
457 RETURN to_number(p_party_id);
458 END IF;
459
460 IF p_country_code IS NOT NULL AND p_area_code IS NOT NULL
461 AND p_extension IS NULL THEN
462 OPEN c_party_id_w_extension(p_country_code, p_area_code, p_phone_number);
463 FETCH c_party_id_w_extension INTO p_party_id;
464 IF c_party_id_w_extension%NOTFOUND THEN
465 CLOSE c_party_id_w_extension;
466 RETURN NULL;
467 END IF;
468 CLOSE c_party_id_w_extension;
469 RETURN to_number(p_party_id);
470 END IF;
471
472 IF p_area_code IS NULL AND p_extension IS NULL
473 AND p_country_code IS NULL THEN
474 OPEN c_party_id_w_all(p_phone_number);
475 FETCH c_party_id_w_all INTO p_party_id;
476 IF c_party_id_w_all%NOTFOUND THEN
477 CLOSE c_party_id_w_all;
478 RETURN NULL;
479 END IF;
480 CLOSE c_party_id_w_all;
481 RETURN to_number(p_party_id);
482 END IF;
483
484 END Get_Customer_From_ANI;
485
486 ------------------------------------------------------------------------------
487 -- Function : Get_Customer_From_CustomerNum
488 -- Usage : Used by the Routing module to get the customer ID from the
489 -- customer number
490 -- Description : This function retrieves a party ID from the
491 -- HZ_PARTIES table given a party number. If there is more than one
492 -- party with the given number, this function will return the
493 -- first party ID that it retrieves.
494 -- Parameters :
495 -- p_party_number IN VARCHAR2(30) Required
496 --
497 -- Return : NUMBER
498 -- If the given customer does not exist, this function returns
499 -- NULL.
500 ------------------------------------------------------------------------------
501
502
503 FUNCTION Get_Customer_From_CustomerNum
504 ( p_party_number IN VARCHAR2)
505 RETURN NUMBER IS
506
507 --p_party_id ra_customers.customer_id%type;
508 p_party_id number;
509
510 --
511 -- get party ID gievn customer number
512 --
513 CURSOR c_party_id (pnum VARCHAR2) IS
514 SELECT party_id
515 FROM hz_parties
516 WHERE party_number = pnum;
517 BEGIN
518
519 OPEN c_party_id(p_party_number);
520 FETCH c_party_id INTO p_party_id;
521 IF c_party_id%NOTFOUND THEN
522 CLOSE c_party_id;
523 RETURN NULL;
524 END IF;
525 CLOSE c_party_id;
526 RETURN to_number(p_party_id);
527
528 END Get_Customer_From_CustomerNum;
529
530 ------------------------------------------------------------------------------
531 -- Function : Get_Customer_From_InvoiceNum
532 -- Usage : Used by the Routing module to get the customer ID from the
533 -- sales invoice number.
534 -- Description : This function retrieves a customer ID from the
535 -- RA_CUSTOMER_TRX_ALL table given an invoice number. If there
536 -- are more than one customer associated with the given
537 -- invoice, this function will return the first customer ID
538 -- that it retrieves.
539 -- Parameters :
540 -- p_invoice_number IN VARCHAR2(20) Required
541 --
542 -- Return : NUMBER
543 -- If there is no customer associated with the given invoice,
544 -- or the invoice does not exist, this function returns NULL.
545 ------------------------------------------------------------------------------
546 FUNCTION Get_Customer_From_InvoiceNum
547 ( p_invoice_number IN VARCHAR2)
548 RETURN NUMBER IS
549
550 p_ship_to_customer_id ra_customer_trx_all.ship_to_customer_id%type;
551 p_bill_to_customer_id ra_customer_trx_all.bill_to_customer_id%type;
552
553 --
554 -- get customer ID given an invoice number
555 --
556 CURSOR c_customer_id (invnum VARCHAR2) IS
557 SELECT ship_to_customer_id, bill_to_customer_id
558 FROM ra_customer_trx_all
559 WHERE trx_number = invnum;
560
561 BEGIN
562
563 OPEN c_customer_id(p_invoice_number);
564 FETCH c_customer_id INTO p_ship_to_customer_id, p_bill_to_customer_id;
565 IF c_customer_id%NOTFOUND THEN
566 CLOSE c_customer_id;
567 RETURN NULL;
568 END IF;
569 CLOSE c_customer_id;
570 IF p_ship_to_customer_id IS NOT NULL THEN
571 RETURN to_number(p_ship_to_customer_id);
572 ELSE
573 RETURN to_number(p_bill_to_customer_id);
574 END IF;
575
576 END Get_Customer_From_InvoiceNum;
577
578 ------------------------------------------------------------------------------
579 -- Function : Get_Name_Of_Customer
580 -- Usage : Used by the Routing module to get the customer name from the
581 -- customer ID
582 -- Description : This function retrieves the customer name from the
583 -- HZ_PARTIES table given a customer ID.
584 -- Parameters :
585 -- p_party_id IN NUMBER Required
586 --
587 -- Return : VARCHAR2(255)
588 -- If there is no customer with the given party ID, this
589 -- function returns NULL.
590 ------------------------------------------------------------------------------
591
592 FUNCTION Get_Name_Of_Customer
593 ( p_party_id IN NUMBER)
594 RETURN VARCHAR2 IS
595 p_party_name VARCHAR2(255);
596
597 --
598 -- get party name given a party ID
599 --
600 CURSOR c_party_name (pid NUMBER) IS
601 SELECT party_name
602 FROM hz_parties
603 WHERE party_id = pid;
604 BEGIN
605
606 OPEN c_party_name(p_party_id);
607 FETCH c_party_name INTO p_party_name;
608 IF c_party_name%NOTFOUND THEN
609 CLOSE c_party_name;
610 RETURN NULL;
611 END IF;
612 CLOSE c_party_name;
613 RETURN p_party_name;
614 END Get_Name_Of_Customer;
615
616 ------------------------------------------------------------------------------
617 -- Function : Get_Status_Of_Customer
618 -- Usage : Used by the Routing module to get the customer status
619 -- Description : This function retrieves the customer status flag from the
620 -- HZ_PARTIES table given a party ID.
621 -- Parameters :
622 -- p_party_id IN NUMBER Required
623 --
624 -- Return : VARCHAR2(1)
625 -- If there is no customer with the given customer ID, this
626 -- function returns NULL.
627 ------------------------------------------------------------------------------
628
629 FUNCTION Get_Status_Of_Customer(p_party_id IN NUMBER)
630 RETURN VARCHAR2
631 IS
632 p_customer_status varchar2(1);
633
634 --
635 -- get customer status given a party ID
636 --
637 CURSOR c_customer_status (pid NUMBER) IS
638 SELECT status
639 FROM hz_parties
640 WHERE party_iD = pid;
641 BEGIN
642
643 OPEN c_customer_status(p_party_id);
644 FETCH c_customer_status INTO p_customer_status;
645 IF c_customer_status%NOTFOUND THEN
646 CLOSE c_customer_status;
647 RETURN NULL;
648 END IF;
649 CLOSE c_customer_status;
650 RETURN p_customer_status;
651 END Get_Status_Of_Customer;
652
653
654 ------------------------------------------------------------------------------
655 -- Function : Is_CP_Existent
656 -- Usage : Used by the Routing module to determine whether a customer
657 -- product exists in the installed base with the given ID
658 -- Description : This function finds a row in the CS_CUSTOMER_PRODUCTS_ALL
659 -- table that matches the given customer product ID.
660 -- Parameters :
661 -- p_customer_product_id IN NUMBER Required
662 --
663 -- Return : BOOLEAN
664 -- If there is a row that matches the given ID, this function
665 -- returns TRUE. Else it returns FALSE.
666 ------------------------------------------------------------------------------
667
668 FUNCTION Is_CP_Existent(p_customer_product_id IN NUMBER)
669 RETURN BOOLEAN IS
670
671 p_customer_pid NUMBER(15);
672
673 --
674 -- check if the given customer product ID exists
675 --
676 CURSOR c_customer_product_id (prodid NUMBER) IS
677 SELECT customer_product_id
678 FROM cs_customer_products_all
679 WHERE customer_product_id = prodid;
680 BEGIN
681
682 OPEN c_customer_product_id(p_customer_product_id);
683 FETCH c_customer_product_id INTO p_customer_pid;
684 IF c_customer_product_id%NOTFOUND THEN
685 CLOSE c_customer_product_id;
686 RETURN false;
687 ELSE
688 CLOSE c_customer_product_id;
689 RETURN true;
690 END IF;
691 END Is_CP_Existent;
692
693
694 ------------------------------------------------------------------------------
695 -- Function : Customer_ID_Exists
696 -- Usage : Used by the Routing module to determine whether a customer
697 -- exists with the given ID
698 -- Description : This function finds a row in the HZ_PARTIES
699 -- table that matches the given customer ID.
700 -- Parameters :
701 -- p_customer_id IN NUMBER Required
702 --
703 -- Return : BOOLEAN
704 -- If there is a row that matches the given ID, this function
705 -- returns TRUE. Else it returns FALSE.
706 ------------------------------------------------------------------------------
707
708 FUNCTION Customer_ID_Exists(p_customer_id IN NUMBER) Return BOOLEAN IS
709
710 p_party_id NUMBER(15);
711
712 --
713 -- check if the given customer ID exists
714 --
715 CURSOR c_party_id (custid NUMBER) IS
716 SELECT party_id
717 FROM hz_parties
718 WHERE party_id = custid;
719 BEGIN
720
721 OPEN c_party_id(p_customer_id);
722 FETCH c_party_id INTO p_party_id;
723 IF c_party_id%NOTFOUND THEN
724 CLOSE c_party_id;
725 RETURN false;
726 ELSE
727 CLOSE c_party_id;
728 RETURN true;
729 END IF;
730 END Customer_ID_Exists;
731
732
733 ------------------------------------------------------------------------------
734 -- Function : Get_CP_From_ReferenceNum
735 -- Usage : Used by the Routing module to get the customer product ID
736 -- from the reference number
737 -- Description : This function retrieves a customer product ID from the
738 -- CS_CUSTOMER_PRODUCT_ALL table given a reference number.
739 -- Parameters :
740 -- p_reference_number IN NUMBER Required
741 --
742 -- Return : NUMBER
743 -- If the given reference number does not exist, this function
744 -- returns NULL.
745 ------------------------------------------------------------------------------
746
747 FUNCTION Get_CP_From_ReferenceNum(p_reference_number IN NUMBER)
748 RETURN NUMBER IS
749
750
751 p_customer_product_id cs_customer_products_all.customer_product_id%type;
752
753 --
754 -- get customer product ID given a reference number
755 --
756 CURSOR c_customer_product_id (refnum NUMBER) IS
757 SELECT customer_product_id
758 FROM cs_customer_products_all
759 WHERE reference_number = refnum;
760
761 BEGIN
762
763 OPEN c_customer_product_id(p_reference_number);
764 IF c_customer_product_id%NOTFOUND THEN
765 CLOSE c_customer_product_id;
766 RETURN NULL;
767 END IF;
768 FETCH c_customer_product_id INTO p_customer_product_id;
769 CLOSE c_customer_product_id;
770 RETURN to_number(p_customer_product_id);
771
772 END Get_CP_From_ReferenceNum;
773
774 ------------------------------------------------------------------------------
775 -- Function : Get_Product_From_ReferenceNum
776 -- Usage : Used by the Routing module to get the inventory item ID
777 -- from the reference number
778 -- Description : This function retrieves an inventory item ID from the
779 -- CS_CUSTOMER_PRODUCT_ALL table given a reference number.
780 -- Parameters :
781 -- p_reference_number IN NUMBER Required
782 --
783 -- Return : NUMBER
784 -- If the given reference number does not exist, this function
785 -- returns NULL.
786 ------------------------------------------------------------------------------
787
788 FUNCTION Get_Product_From_ReferenceNum(p_reference_number IN NUMBER)
789 RETURN NUMBER IS
790 p_inventory_item_id cs_customer_products_all.inventory_item_id%type;
791
792 --
793 -- get inventory item ID given a reference number
794 --
795 CURSOR c_inventory_item_id (refnum NUMBER) IS
796 SELECT inventory_item_id
797 FROM cs_customer_products_all
798 WHERE reference_number = refnum;
799 BEGIN
800
801 OPEN c_inventory_item_id(p_reference_number);
802 IF c_inventory_item_id%NOTFOUND THEN
803 CLOSE c_inventory_item_id;
804 RETURN NULL;
805 END IF;
806 FETCH c_inventory_item_id INTO p_inventory_item_id;
807 CLOSE c_inventory_item_id;
808 RETURN to_number(p_inventory_item_id);
809 END Get_Product_From_ReferenceNum;
810
811
812 ------------------------------------------------------------------------------
813 -- Function : Get_Name_Of_Product
814 -- Usage : Used by the Routing module to get the product name from the
815 -- inventory item ID
816 -- Description : This function retrieves the product name from the
817 -- MTL_SYSTEM_ITEMS table given an inventory item ID and an
818 -- organization ID. The inventory organization ID is required
819 -- because the same item can be defined in more than one
820 -- organization.
821 -- Parameters :
822 -- p_inventory_item_id IN NUMBER Required
823 -- p_inventory_org_id IN NUMBER Required
824 --
825 -- Return : VARCHAR2(240)
826 -- If there is no product with the given inventory item ID,
827 -- this function returns NULL.
828 ------------------------------------------------------------------------------
829 FUNCTION Get_Name_Of_Product
830 ( p_inventory_item_id IN NUMBER,
831 p_inventory_org_id IN NUMBER)
832 RETURN VARCHAR2 IS
833
834 p_product_name mtl_system_items.description%type;
835
836 --
837 -- get name given an inventory item ID and a inventory organization ID
838 --
839 CURSOR c_product_name (invid NUMBER, orgid NUMBER) IS
840 SELECT description
841 FROM mtl_system_items
842 WHERE inventory_item_id = invid AND
843 organization_id = orgid;
844 BEGIN
845
846 OPEN c_product_name(p_inventory_item_id, p_inventory_org_id);
847 IF c_product_name%NOTFOUND THEN
848 CLOSE c_product_name;
849 RETURN NULL;
850 END IF;
851 FETCH c_product_name INTO p_product_name;
852 CLOSE c_product_name;
853 RETURN p_product_name;
854
855 END Get_Name_Of_Product;
856
857 ------------------------------------------------------------------------------
858 -- Function : Get_Customer_From_Email
859 -- Usage : Used to get the party ID from the sender's email address (email
860 -- integration)
861 -- Description : This function retrieves a party ID from the
862 -- JTF_CONTACT_POINTS_V given a email address.
863 -- If there is more than one customer associated with the given email
864 -- address, this function will return the first customer ID that
865 -- it retrieves.
866 -- Parameters :
867 -- p_email_address IN VARCHAR2(2000) Required
868 --
869 -- Return : NUMBER
870 -- If there is no customer associated with the given email
871 -- address, or the email address does not exist (in the system),
872 -- this function returns NULL.
873 ------------------------------------------------------------------------------
874
875 FUNCTION Get_Customer_From_Email
876 ( p_email_address IN VARCHAR2)
877 Return Number Is
878
879 p_party_id NUMBER(15);
880 /*
881 CURSOR c_party_id (email VARCHAR2) IS
882 SELECT party_id
883 FROM JTF_CONTACT_POINTS_V
884 WHERE Upper(email_address) = Upper(email) ;
885 */
886
887 /* Enhancement: 1685717 Parties are queried by ordering last_update_date on *//* hz_contact_points in descending order, so that active parties are selected first */
888
889 CURSOR c_party_id (email VARCHAR2) IS
890 SELECT owner_table_id party_id
891 FROM hz_contact_points
892 WHERE owner_table_name = 'HZ_PARTIES'
893 AND contact_point_type = 'EMAIL'
894 AND UPPER(email_address) = UPPER(email)
895 ORDER BY last_update_date DESC;
896
897 BEGIN
898
899 OPEN c_party_id(p_email_address);
900 FETCH c_party_id INTO p_party_id;
901 IF c_party_id%NOTFOUND THEN
902 CLOSE c_party_id;
903 RETURN NULL;
904 END IF;
905 CLOSE c_party_id;
906
907 RETURN p_party_id;
908
909 END Get_Customer_From_Email;
910
911
912 --
913 ------------------------------------------------------------------------------
914 -- Function : Get_Customer_From_ContactNum
915 -- Usage : Used to get the party ID from the Contact Number passed in the IVR parms
916 -- Description : This function retrieves a party ID from the
917 -- HZ_PARTIES table by traversing the relationships from HZ_ORG_CONTACTS
918 -- and HZ_RELATIONSHIPS.
919 -- If there is more than one party_id associated with the given ContactNum
920 -- this function will return the first party_id that it retrieves.
921 -- Parameters :
922 -- p_contact_number IN VARCHAR2(1996) Required
923 --
924 -- Return : NUMBER
925 -- If there is no party_id associated with the given ContactNum
926 -- this function returns NULL.
927 ------------------------------------------------------------------------------
928
929 FUNCTION Get_Customer_From_ContactNum
930 ( p_contact_number IN VARCHAR2)
931 Return Number IS
932
933 CURSOR cnum (connum IN VARCHAR2) IS
934 SELECT p.party_id
935 FROM hz_parties p,
936 hz_relationships r,
937 hz_org_contacts c
938 WHERE c.contact_number = connum
939 AND c.party_relationship_id = r.relationship_id
940 AND r.object_id = p.party_id
941 AND r.subject_table_name = 'HZ_PARTIES'
942 AND r.object_table_name = 'HZ_PARTIES'
943 AND r.directional_flag = 'F';
944 p_party_id NUMBER := null;
945
946 BEGIN
947
948 OPEN cnum (p_contact_number);
949 FETCH cnum INTO p_party_id;
950 IF cnum%NOTFOUND THEN
951 CLOSE cnum;
952 RETURN NULL;
953 END IF;
954 CLOSE cnum;
955
956 RETURN p_party_id;
957
958 END Get_Customer_From_ContactNum;
959
960 ------------------------------------------------------------------------------
961 -- Function : reverse_number
962 -- Usage : Used to get reverse number for the number passed to function
963 -- Description : This function is built to reverse the number passed to it.
964 -- This function can be used instead of the standard REVERSE
965 -- function because the REVERSE fucntion cannot be used in PL/
966 -- Parameters :
967 -- p_phone_number IN VARCHAR2(1996) Required
968 --
969 -- Return : VARCHAR2
970 -----------------------------------------------------------------------------
971 FUNCTION reverse_number(p_phone_number VARCHAR2)
972 RETURN VARCHAR2
973 IS
974 l_filtered_number VARCHAR2(2000);
975 l_ret_number VARCHAR2(2000);
976 l_changed_number VARCHAR2(2000);
977
978 BEGIN
979
980 l_filtered_number := translate(
981 p_phone_number,
982 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .+''~`\/@#$^&*_,|}{[]?<>=";:',
983 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
984
985 IF l_filtered_number IS NULL OR l_filtered_number='' THEN
986 RETURN NULL;
987 END IF;
988 IF length(l_filtered_number) > 0 THEN
989 FOR I IN REVERSE 1..length(l_filtered_number) LOOP
990 l_ret_number := l_ret_number || substr(l_filtered_number,I,1);
991 END LOOP;
992 FOR I IN 1..length(l_ret_number) LOOP
993 l_filtered_number := substr(l_ret_number,I,1);
994 select decode(upper(l_filtered_number),'A','2','B','2','C','2',
995 'D','3','E','3','F','3',
996 'G','4','H','4','I','4',
997 'J','5','K','5','L','5',
998 'M','6','N','6','O','6',
999 'P','7','Q','7','R','7','S','7',
1000 'T','8','U','8','V','8',
1001 'W','9','X','9','Y','9','Z','9',l_filtered_number) into l_filtered_number from dual;
1002 l_changed_number := l_changed_number||l_filtered_number;
1003
1004 END LOOP;
1005 l_ret_number := l_changed_number;
1006 END IF;
1007
1008 RETURN l_ret_number;
1009 END reverse_number;
1010
1011
1012
1013 ------------------------------------------------------------------------------
1014 -- Function : Get_Customer_From_Reverse_ANI
1015 -- Usage : Used to get the party ID from the reversed ANI Number passed in the IVR parms
1016 -- Description : This function retrieves a owner_table_id from the
1017 -- HZ_CONTACT_POINTS
1018 -- If there is more than one party_id associated with the given ContactNum
1019 -- this function will return the first party_id that it retrieves.
1020 -- Parameters :
1021 -- p_rANI IN VARCHAR2(1996) Required
1022 --
1023 -- Return : NUMBER
1024 -- If there is no party_id associated with the given ContactNum
1025 -- this function returns NULL.
1026 ------------------------------------------------------------------------------
1027 FUNCTION get_customer_from_reverse_ANI(p_rANI IN VARCHAR2,
1028 x_uwq_multi_record_match OUT NOCOPY VARCHAR2,
1029 x_phone_id OUT NOCOPY NUMBER )
1030 RETURN NUMBER
1031 IS
1032 l_phone_num VARCHAR2(30):=NULL;
1033 l_party_id NUMBER:=NULL;
1034 l_phone_id NUMBER:=NULL;
1035 l_rANI VARCHAR2(30):=NULL;
1036 l_stripped_rANI VARCHAR2(30):= NULL;
1037 n_use_exact_ani VARCHAR2(2);
1038 --
1039 CURSOR transpose_phone_cur IS
1040 SELECT transposed_phone_number, owner_table_id, contact_point_id
1041 FROM hz_contact_points
1042 WHERE transposed_phone_number LIKE l_rANI
1043 AND owner_table_name = 'HZ_PARTIES'
1044 ORDER BY last_update_date DESC;
1045
1046 BEGIN
1047 IF p_rANI IS NULL THEN
1048 RETURN to_number(NULL);
1049 END IF;
1050
1051 fnd_profile.get('CSC_CUSTOMER_FROM_EXACT_ANI',n_use_exact_ani);
1052 IF NVL(n_use_exact_ani,'Y') = 'Y' THEN
1053 l_rANI := p_rANI||'%';
1054 OPEN transpose_phone_cur;
1055 LOOP
1056 IF transpose_phone_cur%NOTFOUND THEN
1057 EXIT;
1058 END IF;
1059 FETCH transpose_phone_cur INTO l_phone_num, l_party_id, l_phone_id;
1060 END LOOP;
1061 IF transpose_phone_cur%ROWCOUNT > 1 THEN
1062 CLOSE transpose_phone_cur;
1063 x_uwq_multi_record_match := '"MULTIPLE_PHONE_NUMBERS"';
1064 x_phone_id := l_phone_id;
1065 RETURN to_number(NULL);
1066 END IF;
1067 CLOSE transpose_phone_cur;
1068 x_phone_id := l_phone_id;
1069 RETURN l_party_id;
1070 ELSE
1071 l_stripped_rANI := SUBSTR(p_rANI,1, LENGTH(p_rANI)-1);
1072 l_rANI := l_stripped_rANI||'%';
1073 OPEN transpose_phone_cur;
1074 LOOP
1075 IF transpose_phone_cur%NOTFOUND THEN
1076 EXIT;
1077 END IF;
1078 FETCH transpose_phone_cur INTO l_phone_num, l_party_id, l_phone_id;
1079 END LOOP;
1080 IF transpose_phone_cur%ROWCOUNT > 1 THEN
1081 CLOSE transpose_phone_cur;
1082 x_uwq_multi_record_match := '"MULTIPLE_PHONE_NUMBERS" uwq_stripped_reverse_ani="'||l_stripped_rANI||'"';
1083 x_phone_id := l_phone_id;
1084 RETURN to_number(NULL);
1085 END IF;
1086 CLOSE transpose_phone_cur;
1087 x_phone_id := l_phone_id;
1088 RETURN l_party_id;
1089 END IF;
1090 END;
1091
1092 --------------------------------------------------------------------------
1093 --
1094 ------------------------------------------------------------------------------
1095 -- Procedure : Get_Cust_Acct_From_Account_Num
1096 -- Usage : Used to get Party_id,Cust_account_id,last_update_date from
1097 -- hz_cust_accounts
1098 -- Parameters :
1099 -- p_customer_account_number IN VARCHAR2(30) Required
1100 -- x_party_id OUT NOCOPY NUMBER(15)
1101 -- x_cust_account_id OUT NOCOPY NUMBER(15)
1102 -- x_last_update_date OUT NOCOPY DATE
1103 --
1104 ------------------------------------------------------------------------------
1105
1106 PROCEDURE Get_Cust_Acct_From_Account_Num
1107 ( p_cust_acct_number IN VARCHAR2,
1108 x_party_id OUT NOCOPY NUMBER,
1109 x_cust_account_id OUT NOCOPY NUMBER,
1110 x_last_update_date OUT NOCOPY DATE) IS
1111
1112 CURSOR c_party_id (anum VARCHAR2) IS
1113 SELECT party_id,cust_account_id,last_update_date
1114 FROM hz_cust_accounts
1115 WHERE account_number = anum;
1116 BEGIN
1117
1118 OPEN c_party_id (p_cust_acct_number);
1119 FETCH c_party_id INTO x_party_id,x_cust_account_id,x_last_update_date;
1120 CLOSE c_party_id;
1121
1122 END Get_Cust_Acct_From_Account_Num;
1123
1124 ------------------------------------------------------------------------------
1125 -- Function : Get_Party_Name_From_Party_id
1126 -- Usage : used to lookup customer name to pass over to softphone
1127 -- Parameters :
1128 -- p_party_id IN NOCOPY NUMBER
1129 --
1130 -- Returns :
1131 -- party_name varchar2(1996)
1132 ------------------------------------------------------------------------------
1133
1134 FUNCTION Get_Party_Name_From_Party_id
1135 (p_party_id IN number)
1136 Return varchar2 IS
1137
1138 x_party_name varchar2(1996);
1139
1140 BEGIN
1141 IF p_party_id IS NOT NULL THEN
1142 BEGIN
1143 SELECT party_name
1144 INTO x_party_name
1145 FROM hz_parties
1146 WHERE party_id = p_party_id;
1147 EXCEPTION WHEN NO_DATA_FOUND THEN
1148 x_party_name := 'NOTFOUND';
1149 return x_party_name;
1150 END;
1151 END IF;
1152
1153 return x_party_name;
1154 END;
1155
1156 ------------------------------------------------------------------------------
1157 -- Procedure : CSC_Customer_Lookup
1158 -- Usage : OTM will make a call to this API for customer lookup
1159 -- Parameters :
1160 -- p_media_data IN OUT NOCOPY CCT_KEY_VALUE_LIST
1161 --
1162 ------------------------------------------------------------------------------
1163 PROCEDURE CSC_Customer_Lookup
1164 ( p_media_data IN OUT NOCOPY cct_keyvalue_varr) IS
1165
1166 l_customer_num varchar2(100);
1167 l_contact_num varchar2(100);
1168 l_contract_num varchar2(240);
1169 l_sr_num varchar2(240);
1170 l_order_num varchar2(240);
1171 l_invoice_num varchar2(240);
1172 l_system varchar2(240);
1173 l_ssn varchar2(240);
1174 l_rma_num varchar2(240);
1175 l_system_name varchar2(240);
1176 l_tag_num varchar2(240);
1177 l_country_code varchar2(100);
1178 l_area_code varchar2(100);
1179 l_phone_num varchar2(100);
1180 l_complete_phone_num varchar2(100);
1181 l_serial_num varchar2(100);
1182 l_rma varchar2(100);
1183 l_ani varchar2(100);
1184 l_int_id varchar2(100);
1185 l_account_code varchar2(100);
1186 l_screen_pop_action varchar2(100);
1187 l_media_item_id varchar2(100);
1188 l_dnis varchar2(100);
1189 l_event_name varchar2(100);
1190 l_agent varchar2(100);
1191 l_media_type varchar2(100);
1192 l_callID varchar2(100);
1193 l_sender_name varchar2(100);
1194 l_cust_prod_id varchar2(100);
1195 l_inv_item_id varchar2(100);
1196 l_emp_id varchar2(100);
1197 l_lot_num varchar2(100);
1198 l_po_num varchar2(100);
1199 l_quote_num varchar2(100);
1200 l_instance_name varchar2(100);
1201 l_workitemID varchar2(100);
1202 x_exist_flag varchar2(1);
1203 l_put_result varchar2(256);
1204 l_phone_passed_flag varchar2(1):='N';
1205 l_email_cust_id number;
1206 l_rphone varchar2(1996);
1207 l_uwq_multi_record_match varchar2(2000);
1208 l_phone_id number;
1209 l_customer_id number;
1210 l_match varchar2(1):='0';
1211 l_cust_account_id number;
1212 l_acct_last_update_date date;
1213 l_hdr_info_tbl CSC_SERVICE_KEY_PVT.HDR_info_tbl_type;
1214
1215 --Variables used only for transfer/conference values
1216 l_xfer_action_id NUMBER;
1217 l_xfer_interaction_id NUMBER;
1218 l_xfer_service_key_name VARCHAR2(40);
1219 l_xfer_service_key_value VARCHAR2(240);
1220 l_xfer_call_reason VARCHAR2(40);
1221 l_party_id NUMBER;
1222 l_xfer_cust_party_id NUMBER;
1223 l_xfer_rel_party_id NUMBER;
1224 l_xfer_per_party_id NUMBER;
1225 l_xfer_cust_phone_id NUMBER;
1226 l_xfer_cust_email_id NUMBER;
1227 l_xfer_rel_phone_id NUMBER;
1228 l_xfer_rel_email_id NUMBER;
1229 l_xfer_cust_account_id NUMBER;
1230 l_trans_conf_flag VARCHAR2(1) := 'N';
1231 l_processed_flag VARCHAR2(1) := 'Y';
1232 n_use_exact_ani VARCHAR2(3) := 'Y';
1233 l_stripped_rANI VARCHAR2(50):= null;
1234
1235 l_instance_num varchar2(100);
1236 l_customer_name varchar2(1996);
1237
1238 l_done_it varchar2(10);
1239 BEGIN
1240 /*--New Flag to prevent this new lookup to be called again
1241 l_done_it := cct_collection_util_pub.get(p_media_data, 'DONE_IT',x_exist_flag);
1242
1243 IF nvl(l_done_it,'NO') = 'DONE' AND x_exist_flag = 'Y' THEN
1244 --Went thru the parsing already, no need to go again.
1245 RETURN;
1246 END IF;
1247 */
1248
1249 l_xfer_action_id := cct_collection_util_pub.get(p_media_data, 'ActionID',x_exist_flag);
1250 IF l_xfer_action_id is not null AND x_exist_flag = 'Y' THEN
1251 l_trans_conf_flag := 'Y';
1252 END IF;
1253 l_xfer_interaction_id := cct_collection_util_pub.get(p_media_data, 'INTERACTION_ID',x_exist_flag);
1254 l_xfer_service_key_name := cct_collection_util_pub.get(p_media_data, 'SERVICE_KEY_NAME',x_exist_flag);
1255 l_xfer_service_key_value := cct_collection_util_pub.get(p_media_data, 'SERVICE_KEY_VALUE',x_exist_flag);
1256 l_xfer_call_reason := cct_collection_util_pub.get(p_media_data, 'CALL_REASON',x_exist_flag);
1257 l_xfer_cust_party_id := cct_collection_util_pub.get(p_media_data, 'CUST_PARTY_ID',x_exist_flag);
1258 l_xfer_rel_party_id := cct_collection_util_pub.get(p_media_data, 'REL_PARTY_ID',x_exist_flag);
1259 l_xfer_per_party_id := cct_collection_util_pub.get(p_media_data, 'PER_PARTY_ID',x_exist_flag);
1260 l_xfer_cust_phone_id := cct_collection_util_pub.get(p_media_data, 'CUST_PHONE_ID',x_exist_flag);
1261 l_xfer_cust_email_id := cct_collection_util_pub.get(p_media_data, 'CUST_EMAIL_ID',x_exist_flag);
1262 l_xfer_rel_phone_id := cct_collection_util_pub.get(p_media_data, 'REL_PHONE_ID',x_exist_flag);
1263 l_xfer_rel_email_id := cct_collection_util_pub.get(p_media_data, 'REL_EMAIL_ID',x_exist_flag);
1264 l_xfer_cust_account_id := cct_collection_util_pub.get(p_media_data, 'CUST_ACCOUNT_ID',x_exist_flag);
1265 l_party_id := cct_collection_util_pub.get(p_media_data, 'PARTY_ID',x_exist_flag);
1266 IF l_party_id is not null AND x_exist_flag = 'Y' THEN
1267 l_customer_id := l_party_id;
1268 END IF;
1269
1270 l_customer_num := cct_collection_util_pub.get(p_media_data, 'CustomerNum',x_exist_flag);
1271 l_contact_num := cct_collection_util_pub.get(p_media_data, 'ContactNum', x_exist_flag);
1272 l_account_code := cct_collection_util_pub.get(p_media_data, 'AccountCode', x_exist_flag);
1273 l_contract_num := cct_collection_util_pub.get(p_media_data, 'ContractNum', x_exist_flag);
1274 l_invoice_num := cct_collection_util_pub.get(p_media_data, 'InvoiceNum', x_exist_flag);
1275 l_order_num := cct_collection_util_pub.get(p_media_data, 'OrderNum', x_exist_flag);
1276 l_ssn := cct_collection_util_pub.get(p_media_data, 'SocialSecurityNumber', x_exist_flag);
1277 l_rma_num := cct_collection_util_pub.get(p_media_data, 'RMANum', x_exist_flag);
1278 l_serial_num := cct_collection_util_pub.get(p_media_data, 'SerialNum', x_exist_flag);
1279 l_sr_num := cct_collection_util_pub.get(p_media_data, 'ServiceRequestNum', x_exist_flag);
1280 l_instance_name := cct_collection_util_pub.get(p_media_data, 'InstanceName', x_exist_flag);
1281 l_instance_num := cct_collection_util_pub.get(p_media_data, 'InstanceNum', x_exist_flag);
1282 l_tag_num := cct_collection_util_pub.get(p_media_data, 'TagNumber', x_exist_flag);
1283 l_system_name := cct_collection_util_pub.get(p_media_data, 'SystemName', x_exist_flag);
1284
1285
1286 --Set service key name and value based on the hierarchy specified in SRD
1287 if l_customer_id is not null then
1288 --p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
1289 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
1290 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
1291 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','PartyID');
1292 --this fix is for user who uses default or custom lookup, even when l_customer_id is not null
1293 --it will still check for service key value pairs
1294 if (l_sr_num is null) AND
1295 (l_instance_name is null) AND
1296 (l_instance_num is null) AND
1297 (l_serial_num is null) AND
1298 (l_tag_num is null) AND
1299 (l_system_name is null) AND
1300 (l_rma_num is null) AND
1301 (l_order_num is null) AND
1302 (l_ssn is null) AND
1303 (l_contract_num is null) AND
1304 (l_invoice_num is null) THEN
1305 l_match := 1;
1306 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1307 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1308 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1309
1310 --Resolve party_name from party_id
1311 l_customer_name := get_party_name_from_party_id(l_customer_id);
1312 IF l_customer_name <> 'NOTFOUND' THEN
1313 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1314 END IF;
1315 RETURN;
1316 end if;
1317 end if;
1318
1319 l_country_code := cct_collection_util_pub.get(p_media_data, 'CountryCode', x_exist_flag);
1320 if x_exist_flag = 'Y' then
1321 l_phone_passed_flag := 'Y';
1322 end if;
1323 l_area_code := cct_collection_util_pub.get(p_media_data, 'AreaCode', x_exist_flag);
1324 if x_exist_flag = 'Y' then
1325 l_phone_passed_flag := 'Y';
1326 end if;
1327 l_phone_num := cct_collection_util_pub.get(p_media_data, 'PhoneNumber', x_exist_flag);
1328 if x_exist_flag = 'Y' then
1329 l_phone_passed_flag := 'Y';
1330 end if;
1331 l_screen_pop_action := cct_collection_util_pub.get(p_media_data, 'occtScreenPopAction', x_exist_flag);
1332 l_ani := cct_collection_util_pub.get(p_media_data, 'occtANI', x_exist_flag);
1333 l_media_item_id := cct_collection_util_pub.get(p_media_data, 'occtMediaItemID', x_exist_flag);
1334 l_event_name := cct_collection_util_pub.get(p_media_data, 'occtEventName', x_exist_flag);
1335 l_agent := cct_collection_util_pub.get(p_media_data, 'occtAgentID', x_exist_flag);
1336 l_dnis := cct_collection_util_pub.get(p_media_data, 'occtDNIS', x_exist_flag);
1337 l_workitemID := cct_collection_util_pub.get(p_media_data, 'workItemID', x_exist_flag);
1338 l_media_type := cct_collection_util_pub.get(p_media_data, 'occtMediaType', x_exist_flag);
1339 l_callID := cct_collection_util_pub.get(p_media_data, 'occtCallID', x_exist_flag);
1340 l_sender_name := cct_collection_util_pub.get(p_media_data, 'oiemSenderName', x_exist_flag);
1341 if x_exist_flag = 'Y' and l_sender_name is not null then
1342 l_email_cust_id := csc_routing_utl.Get_Customer_from_Email(l_sender_name);
1343 end if;
1344 l_cust_prod_id := cct_collection_util_pub.get(p_media_data, 'CustomerProductID', x_exist_flag);
1345 l_inv_item_id := cct_collection_util_pub.get(p_media_data, 'InventoryItemID', x_exist_flag);
1346 l_emp_id := cct_collection_util_pub.get(p_media_data, 'employeeID', x_exist_flag);
1347 l_lot_num := cct_collection_util_pub.get(p_media_data, 'LotNum', x_exist_flag);
1348 l_po_num := cct_collection_util_pub.get(p_media_data, 'PurchaseOrderNum', x_exist_flag);
1349 l_quote_num := cct_collection_util_pub.get(p_media_data, 'QuoteNum', x_exist_flag);
1350
1351
1352 /*
1353 --First preference is given to user hook.
1354 IF JTF_USR_HKS.OK_TO_EXECUTE('CSC_UWQ_FORM_ROUTE', 'CSC_UWQ_FORM_OBJ', 'B', 'C') THEN
1355 l_customer_id := NULL;
1356 l_cust_account_id := NULL;
1357 l_phone_id := NULL;
1358
1359 --calling R12 user hook signature
1360 CSC_UWQ_FORM_ROUTE_CUHK.CSC_UWQ_FORM_OBJ_PRE(p_media_data => p_media_data,
1361 x_party_id => l_customer_id,
1362 x_cust_account_id => l_cust_account_id,
1363 x_phone_id => l_phone_id);
1364
1365
1366 If l_customer_id is not null THEN
1367 --append l_customer_id back to p_media_data
1368 --p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
1369 End if;
1370 If l_cust_account_id is not null Then
1371 --append l_cust_account_id back to p_media_data
1372 --p_action_param := p_action_param||'uwq_cust_account_id="'||l_cust_account_id ||'"'; End If;
1373 End if;
1374 If l_phone_id is not null Then
1375 --append l_phone_id back to p_media_data
1376 --p_action_param := p_action_param||'uwq_phone_id="'||l_phone_id ||'"';
1377 End If;
1378 RETURN;
1379 ELSE
1380
1381
1382 IF (l_trans_conf_flag = 'Y') THEN
1383 --This IF condition is added to fix bug 3773311. It is specific to xfr between
1384 --telesales and cc.
1385 IF (l_xfer_cust_party_id is not null) AND
1386 (l_xfer_rel_party_id is not null) AND
1387 (l_xfer_cust_party_id = l_xfer_rel_party_id) AND
1388 (l_xfer_cust_party_id <> nvl(l_xfer_per_party_id,-1)) THEN
1389
1390 --since this is a xfr call from ebc, all search keys would needed to be initialized
1391 --to avoid any confusion.
1392 l_sr_num := null;
1393 l_instance_name := null;
1394 l_instance_num := null;
1395 l_serial_num := null;
1396 l_tag_num := null;
1397 l_system_name := null;
1398 l_rma_num := null;
1399 l_order_num := null;
1400 l_ssn := null;
1401 l_contract_num := null;
1402 l_invoice_num := null;
1403
1404 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'FromEBC','Y');
1405 --foo needs to clear out uwq_service_key_name and value
1406 ELSE
1407 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'FromEBC','N');
1408 --foo needs to append all the xfr params to p_action_param
1409 END IF;
1410 END IF;
1411
1412 */
1413
1414 --Service Key gets priority
1415 if l_sr_num is not null then
1416 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','SR');
1417
1418 --call anand lookup to search for sr num
1419 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'SERVICE_REQUEST_NUMBER',
1420 p_skey_value => l_sr_num,
1421 x_hdr_info_tbl => l_hdr_info_tbl);
1422 IF (l_hdr_info_tbl.count = 0) THEN
1423 l_match := '0';
1424 ELSIF (l_hdr_info_tbl.count = 1) THEN
1425 l_match := '1';
1426 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1427
1428 IF l_hdr_info_tbl(1).rel_party_id is not null THEN
1429 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_rel_party_id',l_hdr_info_tbl(1).rel_party_id);
1430
1431 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).rel_party_id);
1432 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1433 END IF;
1434
1435 IF l_hdr_info_tbl(1).cust_party_id is not null THEN
1436 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1437
1438 IF l_customer_name IS NULL THEN
1439 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1440 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1441 END IF;
1442 END IF;
1443
1444 IF l_hdr_info_tbl(1).employee_id is not null THEN
1445 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'employeeID',l_hdr_info_tbl(1).employee_id);
1446 END IF;
1447
1448 IF l_hdr_info_tbl(1).cust_phone_id is not null THEN
1449 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_cust_phone_id',l_hdr_info_tbl(1).cust_phone_id);
1450 END IF;
1451
1452 IF l_hdr_info_tbl(1).account_id is not null THEN
1453 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1454 END IF;
1455
1456 IF l_hdr_info_tbl(1).rel_phone_id is not null THEN
1457 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_rel_phone_id',l_hdr_info_tbl(1).rel_phone_id);
1458 END IF;
1459
1460 IF l_hdr_info_tbl(1).per_party_id is not null THEN
1461 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_per_party_id',l_hdr_info_tbl(1).per_party_id);
1462 END IF;
1463
1464 IF l_hdr_info_tbl(1).rel_email_id is not null THEN
1465 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_rel_email_id',l_hdr_info_tbl(1).rel_email_id);
1466 END IF;
1467
1468 IF l_hdr_info_tbl(1).cust_email_id is not null THEN
1469 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_cust_email_id',l_hdr_info_tbl(1).cust_email_id);
1470 END IF;
1471
1472 --bug 5640146
1473 --passing service_key_id
1474 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1475 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1476 END IF;
1477
1478 --passing org_id
1479 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1480 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1481 END IF;
1482 --end of bug 5640146
1483
1484 ELSE --(l_hdr_info_tbl.count > 1) THEN
1485 l_match := 'M';
1486 --remember to append the SR # to the p_action_str in foo
1487 END IF;
1488 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1489 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1490 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1491 RETURN;
1492 elsif l_emp_id is not null then
1493 l_match := '0';
1494 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','Employee');
1495 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'employeeID',l_emp_id);
1496 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1497 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1498 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1499 RETURN;
1500 elsif l_instance_name is not null then
1501 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','InstanceName');
1502
1503 --call anand lookup to search for instance name
1504 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'INSTANCE_NAME',
1505 p_skey_value => l_instance_name,
1506 x_hdr_info_tbl => l_hdr_info_tbl);
1507 IF (l_hdr_info_tbl.count = 0) THEN
1508 l_match := '0';
1509 ELSIF (l_hdr_info_tbl.count = 1) THEN
1510 l_match := '1';
1511 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1512 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1513
1514 --must check if account_id is null before setting.
1515 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1516 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1517 END IF;
1518
1519 --bug 5640146
1520 --passing service_key_id
1521 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1522 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1523 END IF;
1524
1525 --passing org_id
1526 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1527 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1528 END IF;
1529 --end of bug 5640146
1530
1531 --Resolve party_name from party_id
1532 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1533 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1534 ELSE --(l_hdr_info_tbl.count > 1) THEN
1535 l_match := 'M';
1536 --remember to append the instance name to the p_action_str in foo
1537 END IF;
1538 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1539 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1540 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1541 RETURN;
1542 elsif l_instance_num is not null then
1543 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','InstanceNum');
1544
1545 --call anand lookup to search for instance num
1546 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'INSTANCE_NUMBER',
1547 p_skey_value => l_instance_num,
1548 x_hdr_info_tbl => l_hdr_info_tbl);
1549 IF (l_hdr_info_tbl.count = 0) THEN
1550 l_match := '0';
1551 ELSIF (l_hdr_info_tbl.count = 1) THEN
1552 l_match := '1';
1553 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1554 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1555
1556 --must check if account_id is null before setting.
1557 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1558 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1559 END IF;
1560
1561 --bug 5640146
1562 --passing service_key_id
1563 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1564 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1565 END IF;
1566
1567 --passing org_id
1568 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1569 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1570 END IF;
1571 --end of bug 5640146
1572
1573 --Resolve party_name from party_id
1574 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1575 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1576 ELSE --(l_hdr_info_tbl.count > 1) THEN
1577 l_match := 'M';
1578 END IF;
1579 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1580 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1581 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1582 RETURN;
1583 elsif l_serial_num is not null then
1584 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','SerialNum');
1585
1586 --call anand lookup to search for serial num
1587 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'SERIAL_NUMBER',
1588 p_skey_value => l_serial_num,
1589 x_hdr_info_tbl => l_hdr_info_tbl);
1590 IF (l_hdr_info_tbl.count = 0) THEN
1591 l_match := '0';
1592 ELSIF (l_hdr_info_tbl.count = 1) THEN
1593 l_match := '1';
1594 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1595 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1596
1597 --must check if account_id is null before setting.
1598 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1599 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1600 END IF;
1601
1602 --bug 5640146
1603 --passing service_key_id
1604 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1605 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1606 END IF;
1607
1608 --passing org_id
1609 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1610 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1611 END IF;
1612 --end of bug 5640146
1613
1614 --Resolve party_name from party_id
1615 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1616 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1617 ELSE --(l_hdr_info_tbl.count > 1) THEN
1618 l_match := 'M';
1619 --remember to append the serial # to the p_action_str in foo
1620 END IF;
1621 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1622 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1623 --l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1624 RETURN;
1625 elsif l_tag_num is not null then
1626 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','TagNum');
1627
1628 --call anand lookup to search for tag num
1629 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'EXTERNAL_REFERENCE',
1630 p_skey_value => l_tag_num,
1631 x_hdr_info_tbl => l_hdr_info_tbl);
1632 IF (l_hdr_info_tbl.count = 0) THEN
1633 l_match := '0';
1634 ELSIF (l_hdr_info_tbl.count = 1) THEN
1635 l_match := '1';
1636 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1637 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1638
1639 --must check if account_id is null before setting.
1640 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1641 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1642 END IF;
1643
1644 --bug 5640146
1645 --passing service_key_id
1646 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1647 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1648 END IF;
1649
1650 --passing org_id
1651 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1652 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1653 END IF;
1654 --end of bug 5640146
1655
1656 --Resolve party_name from party_id
1657 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1658 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1659 ELSE --(l_hdr_info_tbl.count > 1) THEN
1660 l_match := 'M';
1661 --remember to append the tag # to the p_action_str in foo
1662 END IF;
1663 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1664 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1665 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1666 RETURN;
1667 elsif l_system_name is not null then
1668 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','SystemName');
1669
1670 --call anand lookup to search for system name
1671 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'SYSTEM_NUMBER',
1672 p_skey_value => l_system_name,
1673 x_hdr_info_tbl => l_hdr_info_tbl);
1674 IF (l_hdr_info_tbl.count = 0) THEN
1675 l_match := '0';
1676 ELSIF (l_hdr_info_tbl.count = 1) THEN
1677 l_match := '1';
1678 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1679 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1680
1681 --must check if account_id is null before setting.
1682 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1683 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1684 END IF;
1685
1686 --bug 5640146
1687 --passing service_key_id
1688 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1689 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1690 END IF;
1691
1692 --passing org_id
1693 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1694 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1695 END IF;
1696 --end of bug 5640146
1697
1698 --Resolve party_name from party_id
1699 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1700 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1701 ELSE --(l_hdr_info_tbl.count > 1) THEN
1702 l_match := 'M';
1703 --remember to append the system # to the p_action_str in foo
1704 END IF;
1705 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1706 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1707 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1708 RETURN;
1709 elsif l_rma_num is not null then
1710 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','RMANum');
1711
1712 --call anand lookup to search for rma num
1713 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'RMA_NUMBER',
1714 p_skey_value => l_rma_num,
1715 x_hdr_info_tbl => l_hdr_info_tbl);
1716 IF (l_hdr_info_tbl.count = 0) THEN
1717 l_match := '0';
1718 ELSIF (l_hdr_info_tbl.count = 1) THEN
1719 l_match := '1';
1720 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1721 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1722
1723 --must check if account_id is null before setting.
1724 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1725 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1726 END IF;
1727
1728 --bug 5640146
1729 --passing service_key_id
1730 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1731 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1732 END IF;
1733
1734 --passing org_id
1735 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1736 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1737 END IF;
1738 --end of bug 5640146
1739
1740 --Resolve party_name from party_id
1741 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1742 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1743 ELSE --(l_hdr_info_tbl.count > 1) THEN
1744 l_match := 'M';
1745 --remember to append the rma # to the p_action_str in foo
1746 END IF;
1747 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1748 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1749 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1750 RETURN;
1751 elsif l_order_num is not null then
1752 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','OrderNum');
1753
1754 --call anand lookup to search for order num
1755 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'ORDER_NUMBER',
1756 p_skey_value => l_order_num,
1757 x_hdr_info_tbl => l_hdr_info_tbl);
1758 IF (l_hdr_info_tbl.count = 0) THEN
1759 l_match := '0';
1760 ELSIF (l_hdr_info_tbl.count = 1) THEN
1761 l_match := '1';
1762 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1763 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1764
1765 --must check if account_id is null before setting.
1766 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1767 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1768 END IF;
1769
1770 --bug 5640146
1771 --passing service_key_id
1772 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1773 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1774 END IF;
1775
1776 --passing org_id
1777 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1778 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1779 END IF;
1780 --end of bug 5640146
1781
1782 --Resolve party_name from party_id
1783 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1784 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1785 ELSE --(l_hdr_info_tbl.count > 1) THEN
1786 l_match := 'M';
1787 --remember to append the order # to the p_action_str in foo
1788 END IF;
1789 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1790 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1791 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1792 RETURN;
1793 elsif l_ssn is not null then
1794 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','SSN');
1795
1796 --call anand lookup to search for ssn
1797 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'SSN',
1798 p_skey_value => l_ssn,
1799 x_hdr_info_tbl => l_hdr_info_tbl);
1800 IF (l_hdr_info_tbl.count = 0) THEN
1801 l_match := '0';
1802 ELSIF (l_hdr_info_tbl.count = 1) THEN
1803 l_match := '1';
1804 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1805 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1806
1807 --bug 5640146
1808 --passing service_key_id
1809 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1810 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1811 END IF;
1812
1813 --passing org_id
1814 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1815 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1816 END IF;
1817 --end of bug 5640146
1818
1819 --Resolve party_name from party_id
1820 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1821 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1822 ELSE --(l_hdr_info_tbl.count > 1) THEN
1823 l_match := 'M';
1824 --remember to append the ssn to the p_action_str in foo
1825 END IF;
1826 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1827 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1828 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1829 RETURN;
1830 elsif l_contract_num is not null then
1831 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','ContractNum');
1832
1833 --call anand lookup to search for contract num
1834 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'CONTRACT_NUMBER',
1835 p_skey_value => l_contract_num,
1836 x_hdr_info_tbl => l_hdr_info_tbl);
1837 IF (l_hdr_info_tbl.count = 0) THEN
1838 l_match := '0';
1839 ELSIF (l_hdr_info_tbl.count = 1) THEN
1840 l_match := '1';
1841
1842 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1843 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1844
1845 --bug 5640146
1846 --passing service_key_id
1847 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1848 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1849 END IF;
1850
1851 --passing org_id
1852 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1853 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1854 END IF;
1855 --end of bug 5640146
1856
1857 --Resolve party_name from party_id
1858 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1859 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1860 ELSE --(l_hdr_info_tbl.count > 1) THEN
1861 l_match := 'M';
1862 --remember to append the contract # to the p_action_str in foo
1863 END IF;
1864 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1865 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1866 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1867 RETURN;
1868 elsif l_invoice_num is not null then
1869 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','InvoiceNum');
1870
1871 --call anand lookup to search for invoice num
1872 CSC_SERVICE_KEY_PVT.service_key_search(p_skey_name => 'INVOICE_NUMBER',
1873 p_skey_value => l_invoice_num,
1874 x_hdr_info_tbl => l_hdr_info_tbl);
1875 IF (l_hdr_info_tbl.count = 0) THEN
1876 l_match := '0';
1877 ELSIF (l_hdr_info_tbl.count = 1) THEN
1878 l_match := '1';
1879 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_hdr_info_tbl(1).cust_party_id);
1880 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_hdr_info_tbl(1).cust_party_id);
1881
1882 --must check if account_id is null before setting.
1883 IF l_hdr_info_tbl(1).account_id IS NOT NULL THEN
1884 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'SKEY_account_id',l_hdr_info_tbl(1).account_id);
1885 END IF;
1886
1887 --bug 5640146
1888 --passing service_key_id
1889 IF l_hdr_info_tbl(1).service_key_id IS NOT NULL THEN
1890 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'service_key_id',l_hdr_info_tbl(1).service_key_id);
1891 END IF;
1892
1893 --passing org_id
1894 IF l_hdr_info_tbl(1).org_id IS NOT NULL THEN
1895 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_skey_org_id',l_hdr_info_tbl(1).org_id);
1896 END IF;
1897 --end of bug 5640146
1898
1899 --Resolve party_name from party_id
1900 l_customer_name := get_party_name_from_party_id(l_hdr_info_tbl(1).cust_party_id);
1901 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1902 ELSE --(l_hdr_info_tbl.count > 1) THEN
1903 l_match := 'M';
1904 --remember to append the invoice # to the p_action_str in foo
1905 END IF;
1906 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1907 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1908 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1909 RETURN;
1910 end if;
1911
1912 --Customer Num LookUp
1913 If l_customer_num is not null THEN
1914 l_customer_id := null;
1915 l_customer_id := CSC_ROUTING_UTL.Get_Customer_From_CustomerNum(p_party_number => l_customer_num);
1916 IF l_customer_id IS NOT NULL THEN
1917 l_match := '1';
1918 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','PartyID');
1919
1920 --bug 5615536
1921 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
1922 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
1923 --end of bug 5615536
1924
1925 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1926 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1927
1928 --Resolve party_name from party_id
1929 l_customer_name := get_party_name_from_party_id(l_customer_id);
1930 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1931 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1932 RETURN;
1933 END IF;
1934 END IF;
1935
1936 --AccountCode lookup
1937 IF l_account_code IS NOT NULL THEN
1938 l_customer_id := null;
1939 CSC_ROUTING_UTL.Get_Cust_Acct_From_Account_Num( p_cust_acct_number => l_account_code,
1940 x_party_id => l_customer_id,
1941 x_cust_account_id => l_cust_account_id,
1942 x_last_update_date => l_acct_last_update_date);
1943 IF l_customer_id IS NOT NULL THEN
1944 l_match := '1';
1945 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','AccountCode');
1946 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
1947 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
1948
1949 --must check if account_id is null before setting.
1950 IF l_cust_account_id IS NOT NULL THEN
1951 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_cust_account_id',l_cust_account_id);
1952 END IF;
1953
1954 IF l_acct_last_update_date IS NOT NULL THEN
1955 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_last_update_date',l_acct_last_update_date);
1956 END IF;
1957
1958 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
1959 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
1960
1961 --Resolve party_name from party_id
1962 l_customer_name := get_party_name_from_party_id(l_customer_id);
1963 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1964 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
1965 RETURN;
1966 END IF;
1967 END IF;
1968
1969 --Phone lookup
1970 IF l_phone_passed_flag = 'Y' THEN
1971 l_complete_phone_num := l_country_code || l_area_code || l_phone_num;
1972 IF l_complete_phone_num IS NOT NULL THEN
1973 l_customer_id := NULL;
1974 l_rphone := HZ_PHONE_NUMBER_PKG.transpose(l_complete_phone_num);
1975 l_customer_id := CSC_ROUTING_UTL.get_customer_from_reverse_ANI(l_rphone,
1976 l_uwq_multi_record_match,
1977 l_phone_id);
1978 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','CompletePhone');
1979
1980 IF l_customer_id IS NOT NULL THEN
1981 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
1982 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
1983
1984 IF l_phone_id IS NOT NULL THEN
1985 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_phone_id',l_phone_id);
1986 END IF;
1987
1988 --Resolve party_name from party_id
1989 l_customer_name := get_party_name_from_party_id(l_customer_id);
1990 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
1991
1992 l_match := '1';
1993 ELSE
1994 IF l_uwq_multi_record_match is not null THEN
1995 l_match := 'M';
1996 l_uwq_multi_record_match := 'MULTIPLE_PHONE_NUMBERS';
1997 fnd_profile.get('CSC_CUSTOMER_FROM_EXACT_ANI',n_use_exact_ani);
1998 IF NVL(n_use_exact_ani,'Y') = 'N' THEN
1999 l_stripped_rANI := SUBSTR(l_rphone,1, LENGTH(l_rphone)-1);
2000 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_stripped_reverse_ani',l_stripped_rANI);
2001 END IF;
2002 ELSE
2003 l_match := '1';
2004 END IF;
2005 END IF;
2006
2007 IF l_uwq_multi_record_match IS NOT NULL THEN
2008 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_multi_record_match',l_uwq_multi_record_match);
2009 END IF;
2010
2011 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
2012
2013 IF l_rphone IS NOT NULL THEN
2014 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_reverse_ani',l_rphone);
2015 END IF;
2016
2017 IF l_complete_phone_num IS NOT NULL THEN
2018 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_complete_phone_num',l_complete_phone_num);
2019 END IF;
2020
2021 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
2022 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
2023 RETURN;
2024 END IF;
2025 END IF;
2026
2027 --Contact Num lookup
2028 IF l_contact_num IS NOT NULL THEN
2029 l_customer_id := null;
2030 l_customer_id := CSC_ROUTING_UTL.Get_Customer_From_CustomerNum( p_party_number => l_contact_num);
2031 IF l_customer_id IS NOT NULL THEN
2032 l_match := '1';
2033 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','PartyID');
2034 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
2035 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
2036 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
2037 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
2038
2039 --Resolve party_name from party_id
2040 l_customer_name := get_party_name_from_party_id(l_customer_id);
2041 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
2042 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
2043
2044 RETURN;
2045 END IF;
2046 END IF;
2047
2048
2049 --ANI lookup
2050 IF l_ani IS NOT NULL THEN
2051 --Note: standard REVERSE function cannot be used in PL-SQL as it is a reserved word in PL-SQL
2052 --to replace this function REVERSE_NUMBER function was created
2053 l_customer_id := NULL;
2054 l_rphone := HZ_PHONE_NUMBER_PKG.transpose(l_ani);
2055 l_customer_id := CSC_ROUTING_UTL.get_customer_from_reverse_ANI(l_rphone,
2056 l_uwq_multi_record_match,
2057 l_phone_id);
2058 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'WhichIVR','ANI');
2059
2060 IF l_customer_id is not null THEN --Exact Match
2061 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
2062 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
2063
2064 IF l_phone_id IS NOT NULL THEN
2065 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_phone_id',l_phone_id);
2066 END IF;
2067
2068 --Resolve party_name from party_id
2069 l_customer_name := get_party_name_from_party_id(l_customer_id);
2070 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerName',l_customer_name);
2071 l_match := '1';
2072 ELSE
2073 IF l_uwq_multi_record_match is not null THEN
2074 l_match := 'M';
2075 l_uwq_multi_record_match := 'MULTIPLE_PHONE_NUMBERS';
2076 fnd_profile.get('CSC_CUSTOMER_FROM_EXACT_ANI',n_use_exact_ani);
2077
2078 IF NVL(n_use_exact_ani,'Y') = 'N' THEN
2079 l_stripped_rANI := SUBSTR(l_rphone,1, LENGTH(l_rphone)-1);
2080 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_stripped_reverse_ani',l_stripped_rANI);
2081 END IF;
2082 ELSE
2083 l_match := '0';
2084 END IF;
2085 END IF;
2086
2087 IF l_uwq_multi_record_match IS NOT NULL THEN
2088 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_multi_record_match',l_uwq_multi_record_match);
2089 END IF;
2090
2091 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
2092 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'uwq_reverse_ani',l_rphone);
2093 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
2094 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
2095 RETURN;
2096
2097 END IF;
2098 EXCEPTION
2099 WHEN OTHERS THEN
2100 l_customer_id := null;
2101 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'CustomerID',l_customer_id);
2102 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'PARTY_ID',l_customer_id);
2103 l_match := '0';
2104 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'MatchFlag',l_match);
2105 l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'ProcessedFlag',l_processed_flag);
2106 -- l_put_result := CCT_COLLECTION_UTIL_PUB.PUT(p_media_data,'DONE_IT','DONE');
2107
2108 END CSC_Customer_Lookup;
2109
2110 END CSC_Routing_UTL;
2111
[Home] [Help]