1 PACKAGE BODY CSC_UWQ_FORM_ROUTE AS
2 /* $Header: cscpuwqb.pls 120.12 2006/11/07 21:44:47 hbchung ship $ */
3
4 --package private variables
5 pp_media_table SYSTEM.IEU_UWQ_MEDIA_DATA_NST;
6 l_country_code VARCHAR2(4);
7 l_area_code VARCHAR2(4);
8 l_phone_num VARCHAR2(10);
9 l_phone_ext VARCHAR2(10);
10
11 FUNCTION GET_MEDIA_VALUE ( p_media_name IN VARCHAR2)
12
13 --FUNCTION: get_media_value
14 --USAGE: internal, private function used by CSC_UWQ_Form_Obj
15 --Description: get the data value for the paramater named passed in
16 --the media table placed into the package variable pp_media_table
17
18 RETURN VARCHAR2 IS
19
20 BEGIN
21
22 FOR i in 1..pp_media_table.COUNT LOOP
23 IF pp_media_table(i).param_name = p_media_name THEN
24 RETURN pp_media_table(i).param_value;
25 END IF;
26 END LOOP;
27
28 return null;
29
30 END GET_MEDIA_VALUE;
31
32 PROCEDURE CSC_UWQ_FORM_OBJ ( p_ieu_media_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
33 p_action_type OUT NOCOPY NUMBER,
34 p_action_name OUT NOCOPY VARCHAR2,
35 p_action_param OUT NOCOPY VARCHAR2) IS
36
37 --Procedure : CSC_UWQ_Form_Obj
38 --Usage : Used by UWQ to call Contact Center Form
39 --Description : This procedure takes the table of objects containing
40 -- the meta data as input and gives the following as output:
41 -- 1. Action Type - Method to be used to call the contact center form
42 -- like APP_NAVIGATE.EXECUTE or FND_FUNCTION.EXECUTE etc.
43 -- 2. Action Name - Name of the function to call the contact center form.
44 -- 3. Action Param - Parameters to be passed to the contact center form.
45 -- Parameters : p_ieu_media_data IN SYSTEM.IEW_UWQ_MEDIA_DATA_NST Required
46 -- p_action_type OUT NUMBER
47 -- p_action_name OUT VARCHAR2
48 -- p_action_param OUT VARCHAR2
49
50 l_name VARCHAR2(500);
51 l_value VARCHAR2(1996);
52 l_type VARCHAR2(500);
53
54 --Variables used only for transfer/conference values
55 l_xfer_action_id NUMBER;
56 l_xfer_interaction_id NUMBER;
57 l_xfer_service_key_name VARCHAR2(40);
58 l_xfer_service_key_value VARCHAR2(240);
59 l_xfer_call_reason VARCHAR2(40);
60 l_xfer_cust_party_id NUMBER;
61 l_xfer_rel_party_id NUMBER;
62 l_xfer_per_party_id NUMBER;
63 l_xfer_cust_phone_id NUMBER;
64 l_xfer_cust_email_id NUMBER;
65 l_xfer_rel_phone_id NUMBER;
66 l_xfer_rel_email_id NUMBER;
67 l_xfer_cust_account_id NUMBER;
68 l_trans_conf_flag VARCHAR2(1) := 'N';
69 l_party_id NUMBER;
70
71 --Variables used only for inbound call values
72 l_customer_id NUMBER;
73 l_cust_account_id NUMBER;
74 l_acct_last_update_date DATE;
75 l_email_cust_id NUMBER;
76 l_contract_num VARCHAR2(240);
77 l_invoice_num VARCHAR2(240);
78 l_order_num VARCHAR2(240);
79 l_rma_num VARCHAR2(240);
80 l_serial_num VARCHAR2(240);
81 l_service_request_num VARCHAR2(240);
82 l_instance_name VARCHAR2(240);
83 l_tag_num VARCHAR2(240);
84 l_system_name VARCHAR2(240);
85 l_call_reason VARCHAR2(40);
86 l_ccode VARCHAR(10);
87 l_acode VARCHAR(10);
88 l_phone_number VARCHAR(40);
89 l_complete_phone_num VARCHAR(60);
90 l_phone_passed_flag VARCHAR2(1) := 'N';
91 l_phone_id NUMBER;
92 l_uwq_multi_record_match VARCHAR2(2000);
93 l_rphone VARCHAR2(1996);
94 n_use_exact_ani VARCHAR2(200);
95 l_ssn VARCHAR2(240);
96
97 --Variables that may be used for transfer/conference or inbound call
98 l_block_mode_flag VARCHAR2(1) := 'F';
99 l_cont_mode_flag VARCHAR2(1) := 'T';
100 l_open_new_CC VARCHAR2(1) := 'N';
101
102 --Created to pass a null party id in case of email where the party id is null
103 l_dummy_customer_id NUMBER;
104
105 --R12 new variables
106 l_match varchar2(1):='0';
107 l_processed_flag varchar2(1):='N';
108 l_fromEBC varchar2(1):='N';
109 l_whichIVR varchar2(20);
110 l_skey_cust_phone_id varchar2(100);
111 l_skey_account_id varchar2(100);
112 l_skey_rel_party_id varchar2(100);
113 l_skey_rel_phone_id varchar2(100);
114 l_skey_per_party_id varchar2(100);
115 l_skey_rel_email_id varchar2(100);
116 l_skey_cust_email_id varchar2(100);
117 l_uwq_multi_record_match2 varchar2(2000);
118 l_uwq_reverse_ani varchar2(200);
119 l_uwq_phone_id number;
120 l_uwq_complete_phone_num varchar2(60);
121 --aaa
122 l_uwq_cust_acct_id number;
123 l_uwq_acct_last_upd_date date;
124 l_uwq_stripped_rev_ani2 varchar2(200);
125
126 l_employee_id varchar2(200);
127 l_instance_num VARCHAR2(240);
128
129 --bug 5640146
130 l_service_key_id number;
131 l_uwq_skey_org_id number;
132 --end of bug 5640146
133
134
135 BEGIN
136
137 l_customer_id := null;
138
139 pp_media_table := p_ieu_media_data;
140
141 p_action_name := 'CSCCCCRC';
142 p_action_param := 'called_from="UWQ"';
143
144 --Loop through the array of data objects to retrieve the parameters that are passed
145 FOR i IN 1..p_ieu_media_data.COUNT LOOP
146 l_name := p_ieu_media_data(i).param_name;
147 l_value := p_ieu_media_data(i).param_value;
148 l_type := p_ieu_media_data(i).param_type;
149
150 --Check the names of the parameters and assign their values to build the string to be passed to CC
151 IF l_name = 'ACTION_ID' THEN
152 if (l_value is not null) then
153 l_trans_conf_flag := 'Y';
154 end if;
155 l_xfer_action_id := l_value;
156 ELSIF l_name = 'INTERACTION_ID' THEN
157 l_xfer_interaction_id := l_value;
158 ELSIF l_name = 'SERVICE_KEY_NAME' THEN
159 l_xfer_service_key_name := l_value;
160 ELSIF l_name = 'SERVICE_KEY_VALUE' THEN
161 l_xfer_service_key_value := l_value;
162 ELSIF l_name = 'CALL_REASON' THEN
163 l_xfer_call_reason := l_value;
164 ELSIF l_name = 'CUST_PARTY_ID' THEN
165 l_xfer_cust_party_id := l_value;
166 ELSIF l_name = 'REL_PARTY_ID' THEN
167 l_xfer_rel_party_id := l_value;
168 ELSIF l_name = 'PER_PARTY_ID' THEN
169 l_xfer_per_party_id := l_value;
170 ELSIF l_name = 'CUST_PHONE_ID' THEN
171 l_xfer_cust_phone_id := l_value;
172 ELSIF l_name = 'CUST_EMAIL_ID' THEN
173 l_xfer_cust_email_id := l_value;
174 ELSIF l_name = 'REL_PHONE_ID' THEN
175 l_xfer_rel_phone_id := l_value;
176 ELSIF l_name = 'REL_EMAIL_ID' THEN
177 l_xfer_rel_email_id := l_value;
178 ELSIF l_name = 'CUST_ACCOUNT_ID' THEN
179 l_xfer_cust_account_id := l_value;
180 ELSIF l_name = 'PARTY_ID' THEN
181 --l_party_id := l_value;
182 l_customer_id := l_value;
183 ELSIF l_name = 'CustomerID' THEN
184 l_customer_id := l_value;
185 ELSIF l_name = 'CustomerNum' THEN
186 p_action_param := p_action_param||'uwq_object_num="'||l_value||'"';
187 ELSIF l_name = 'ContactNum' THEN
188 p_action_param := p_action_param||'uwq_subject_num="'||l_value||'"';
189 ELSIF l_name = 'AccountCode' THEN
190 p_action_param := p_action_param||'uwq_account_number="'||l_value||'"';
191 ELSIF l_name = 'ContractNum' THEN
192 l_contract_num := l_value;
193 ELSIF l_name = 'InvoiceNum' THEN
194 l_invoice_num := l_value;
195 ELSIF l_name = 'OrderNum' THEN
196 l_order_num := l_value;
197 ELSIF l_name = 'SSN' THEN
198 l_ssn := l_value;
199 ELSIF l_name = 'SocialSecurityNumber' THEN
200 l_ssn := l_value;
201 ELSIF l_name = 'RMANum' THEN
202 l_rma_num := l_value;
203 ELSIF l_name = 'SerialNum' THEN
204 l_serial_num := l_value;
205 ELSIF l_name = 'ServiceRequestNum' THEN
206 l_service_request_num := l_value;
207 ELSIF l_name = 'InstanceName' THEN
208 l_instance_name := l_value;
209 ELSIF l_name = 'InstanceNum' THEN
210 l_instance_num := l_value;
211 ELSIF l_name = 'TagNumber' THEN
212 l_tag_num := l_value;
213 ELSIF l_name = 'SystemName' THEN
214 l_system_name := l_value;
215 ELSIF l_name = 'CountryCode' THEN
216 l_phone_passed_flag := 'Y';
217 l_ccode := l_value;
218 ELSIF l_name = 'AreaCode' THEN
219 l_phone_passed_flag := 'Y';
220 l_acode := l_value;
221 ELSIF l_name = 'PhoneNumber' THEN
222 l_phone_passed_flag := 'Y';
223 l_phone_number := l_value;
224 ELSIF l_name = 'occtScreenPopAction' THEN
225 l_call_reason := l_value;
226 ELSIF l_name = 'UWQ_BLOCK_MODE' THEN
227 l_block_mode_flag := l_value;
228 ELSIF l_name = 'UWQ_CONTINUOUS_MODE' THEN
229 l_cont_mode_flag := l_value;
230 ELSIF l_name = 'occtANI' THEN
231 p_action_param := p_action_param||'uwq_ani="'||l_value||'"';
232 ELSIF l_name = 'occtMediaItemID' THEN
233 p_action_param := p_action_param||'uwq_media_item_id="'||l_value||'"';
234 ELSIF l_name = 'occtEventName' THEN
235 p_action_param := p_action_param||'uwq_event="'||l_value||'"';
236 ELSIF l_name = 'occtAgentID' THEN
237 p_action_param := p_action_param||'uwq_agent="'||l_value||'"';
238 ELSIF l_name = 'occtDNIS' THEN
239 p_action_param := p_action_param||'uwq_dnis="'||l_value||'"';
240 ELSIF l_name = 'workItemID' THEN
241 p_action_param := p_action_param||'uwq_work_item_id="'||l_value||'"';
242 ELSIF l_name = 'occtMediaType' THEN
243 p_action_param := p_action_param||'uwq_media_type="'||l_value||'"';
244 ELSIF l_name = 'occtCallID' THEN
245 p_action_param := p_action_param||'uwq_call_id="'||l_value||'"';
246 ELSIF l_name = 'oiemSenderName' and l_value is not null THEN
247 l_email_cust_id := csc_routing_utl.Get_Customer_from_Email(l_value);
248 ELSIF l_name = 'CustomerProductID' THEN
249 p_action_param := p_action_param||'uwq_cust_prod_id="'||l_value||'"';
250 ELSIF l_name = 'InventoryItemID' THEN
251 p_action_param := p_action_param||'uwq_inventory_id="'||l_value||'"';
252 ELSIF l_name = 'employeeID' THEN
253 l_employee_id := l_value;
254 --p_action_param := p_action_param||'uwq_employee_id="'||l_value||'"';
255 ELSIF l_name = 'LotNum' THEN
256 p_action_param := p_action_param||'uwq_lot_num="'||l_value||'"';
257 ELSIF l_name = 'PurchaseOrderNum' THEN
258 p_action_param := p_action_param||'uwq_purchase_order_num="'||l_value||'"';
259 ELSIF l_name = 'QuoteNum' THEN
260 p_action_param := p_action_param||'uwq_quote_num="'||l_value||'"';
261 --
262 --New Parameters for R12
263 --
264 ELSIF l_name = 'WhichIVR' THEN
265 l_whichIVR := l_value;
266 ELSIF l_name = 'MatchFlag' THEN
267 l_match := l_value;
268 ELSIF l_name = 'FromEBC' THEN
269 l_fromEBC := l_value;
270 ELSIF l_name = 'SKEY_cust_phone_id' THEN
271 l_skey_cust_phone_id := l_value;
272 ELSIF l_name = 'SKEY_account_id' THEN
273 l_skey_account_id := l_value;
274 ELSIF l_name = 'SKEY_rel_party_id' THEN
275 l_skey_rel_party_id := l_value;
276 ELSIF l_name = 'SKEY_rel_phone_id' THEN
277 l_skey_rel_phone_id := l_value;
278 ELSIF l_name = 'SKEY_per_party_id' THEN
279 l_skey_per_party_id := l_value;
280 ELSIF l_name = 'SKEY_rel_email_id' THEN
281 l_skey_rel_email_id := l_value;
282 ELSIF l_name = 'SKEY_cust_email_id' THEN
283 l_skey_cust_email_id := l_value;
284 ELSIF l_name = 'ProcessedFlag' THEN
285 l_processed_flag := l_value;
286 ELSIF l_name = 'uwq_phone_id' THEN
287 l_uwq_phone_id := l_value;
288 ELSIF l_name = 'uwq_reverse_ani' THEN
289 l_uwq_reverse_ani := l_value;
290 ELSIF l_name = 'uwq_multi_record_match' THEN
291 l_uwq_multi_record_match2 := l_value;
292 ELSIF l_name = 'uwq_stripped_reverse_ani' THEN
293 l_uwq_stripped_rev_ani2 := l_value;
294 ELSIF l_name = 'service_key_id' THEN
295 --bug 5640146
296 --Needs to look for service_key_id for ordernum,system,instance name and intance number
297 l_service_key_id := l_value;
298 ELSIF l_name = 'uwq_skey_org_id' THEN
299 --Needs to look for uwq_skey_org_id for ordernum,rma and invoice number
300 l_uwq_skey_org_id := l_value;
301 END IF;
302 --end of bug 5640146
303 END LOOP;
304
305 --Check if a new instance of CC needs to be opened or existing instance has to be refreshed
306 --If call is not from the queue (unsolicited call) then open new instance of CC
307 IF (l_block_mode_flag = 'T' and l_cont_mode_flag ='T') OR
308 (l_block_mode_flag = 'T' and l_cont_mode_flag ='F') OR
309 (l_block_mode_flag = 'F' and l_cont_mode_flag ='F') THEN
310 l_open_new_CC := 'Y';
311 --Else refresh existing instance of CC
312 ELSIF (l_block_mode_flag = 'F' and l_cont_mode_flag ='T') THEN
313 l_open_new_CC := 'N';
314 END IF;
315
316 --IF new instance of CC has to be opened, pass fnd_function.execute in p_action_type
317 IF (l_open_new_CC = 'Y') THEN
318 p_action_type := 2;
319 --Else if existing instance of CC has to be refreshed, pass app_navigate in p_action_type
320 ELSIF (l_open_new_CC = 'N') THEN
321 p_action_type := 1;
322 END IF;
323
324 --At this point, if it is a transfer or conference call, then all the data required to
325 --populate contact center form is available, hence return to calling procedure.
326 --But, if it is a transfer or conference call, and if no cust_party_id is passed
327 --and no service key value is passed and no party id is passed, then it implies that the
328 --transfer or conference was made without selecting a record.
329 --In that case, further code to determine party id from inbound call data must be executed.
330 IF (l_trans_conf_flag = 'Y') THEN
331 --This IF condition is added to fix bug 3773311. It is specific to xfr between
332 --telesales and cc.
333 IF (l_xfer_cust_party_id is not null) AND
334 (l_xfer_rel_party_id is not null) AND
335 (l_xfer_cust_party_id = l_xfer_rel_party_id) AND
336 (l_xfer_cust_party_id <> nvl(l_xfer_per_party_id,-1)) THEN
337
338 --since this is a xfr call from ebc, all search keys would needed to be initialized
339 --to avoid any confusion.
340 l_service_request_num := null;
341 l_instance_name := null;
342 l_instance_num := null;
343 l_serial_num := null;
344 l_tag_num := null;
345 l_system_name := null;
346 l_rma_num := null;
347 l_order_num := null;
348 l_ssn := null;
349 l_contract_num := null;
350 l_invoice_num := null;
351 --make sure employee_id is null because ebc does not pass this
352 l_employee_id := null;
353 p_action_param := p_action_param||'uwq_service_key_name=""';
354 p_action_param := p_action_param||'uwq_service_key_value=""';
355 ELSE
356 p_action_param := p_action_param||'uwq_action_id="'||l_xfer_action_id||'"';
357 p_action_param := p_action_param||'uwq_interaction_id="'||l_xfer_interaction_id||'"';
358 p_action_param := p_action_param||'uwq_cust_party_id="'||l_xfer_cust_party_id||'"';
359 p_action_param := p_action_param||'uwq_rel_party_id="'||l_xfer_rel_party_id||'"';
360 p_action_param := p_action_param||'uwq_per_party_id="'||l_xfer_per_party_id||'"';
361 p_action_param := p_action_param||'uwq_cust_phone_id="'||l_xfer_cust_phone_id||'"';
362 p_action_param := p_action_param||'uwq_cust_email_id="'||l_xfer_cust_email_id||'"';
363 p_action_param := p_action_param||'uwq_rel_phone_id="'||l_xfer_rel_phone_id||'"';
364 p_action_param := p_action_param||'uwq_rel_email_id="'||l_xfer_rel_email_id||'"';
365
366 if (l_xfer_cust_account_id is not null) then
367 p_action_param := p_action_param||'uwq_cust_account_id="'||l_xfer_cust_account_id||'"';
368 end if;
369
370 if (l_xfer_service_key_value is not null) then
371 p_action_param := p_action_param||'uwq_service_key_value="'||l_xfer_service_key_value||'"';
372 end if;
373
374 if (l_xfer_service_key_name is not null) then
375 p_action_param := p_action_param||'uwq_service_key_name="'||l_xfer_service_key_name||'"';
376 end if;
377
378 if (l_xfer_call_reason is not null) then
379 p_action_param := p_action_param||'uwq_call_reason="'||l_xfer_call_reason||'"';
380 end if;
381
382 /*if (l_party_id is not null) then
383 p_action_param := p_action_param||'uwq_party_id="'||l_party_id||'"';
384 end if;*/
385
386 IF (l_xfer_cust_party_id is not null) OR
387 (l_xfer_service_key_name is not null and l_xfer_service_key_value is not null) THEN
388
389 IF (l_service_key_id is not null) THEN
390 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
391 END IF;
392
393 RETURN;
394 END IF;
395 END IF;
396 END IF;
397
398 --hbchung
399 --
400 --New R12 logic
401 --
402 IF l_processed_flag = 'Y' THEN
403 p_action_param := p_action_param||'uwq_lookup_flag="'||l_processed_flag||'"';
404 p_action_param := p_action_param||'uwq_which_ivr="'||l_whichIVR||'"';
405 p_action_param := p_action_param||'uwq_match_flag="'||l_match||'"';
406
407 --Need to set call reason
408 IF l_call_reason is not null then
409 p_action_param := p_action_param||'uwq_call_reason="'||l_call_reason||'"';
410 END IF;
411
412 IF l_whichIVR = 'PartyID' THEN
413 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
414 RETURN;
415 ELSIF l_whichIVR = 'ANI' THEN
416 IF (l_match = '1') THEN
417 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'"';
418 p_action_param := p_action_param||'uwq_phone_id="'||l_uwq_phone_id ||'"';
419 ELSIF (l_match = 'M') THEN
420 p_action_param := p_action_param||'uwq_stripped_reverse_ani="'|| l_uwq_stripped_rev_ani2 ||'"';
421 END IF;
422
423 p_action_param := p_action_param||'uwq_multi_record_match="'||l_uwq_multi_record_match2||'"';
424 p_action_param := p_action_param||'uwq_reverse_ani="'||l_uwq_reverse_ani ||'"';
425 RETURN;
426 ELSIF l_whichIVR = 'AccountCode' THEN
427 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'"';
428 p_action_param := p_action_param||'uwq_cust_account_id="'||l_uwq_cust_acct_id ||'"';
429 p_action_param := p_action_param||'uwq_last_update_date="'||l_uwq_acct_last_upd_date ||'"';
430 RETURN;
431 ELSIF l_whichIVR = 'SR' THEN
432 IF (l_match = '1') THEN
433 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
434 p_action_param := p_action_param||'uwq_skey_cust_phone_id="'||l_skey_cust_phone_id||'"';
435 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
436 p_action_param := p_action_param||'uwq_skey_rel_party_id="'||l_skey_rel_party_id||'"';
437 p_action_param := p_action_param||'uwq_skey_rel_phone_id="'||l_skey_rel_phone_id||'"';
438 p_action_param := p_action_param||'uwq_skey_per_party_id="'||l_skey_per_party_id||'"';
439 p_action_param := p_action_param||'uwq_skey_rel_email_id="'||l_skey_rel_email_id||'"';
440 p_action_param := p_action_param||'uwq_skey_cust_email_id="'||l_skey_cust_email_id||'"';
441
442 --bug 5640146
443 IF l_service_key_id IS NOT NULL THEN
444 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
445 END IF;
446
447 IF l_uwq_skey_org_id IS NOT NULL THEN
448 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
449 END IF;
450 --end of bug 5640146
451
452 --for case where SR belongs to employee
453 if l_employee_id is not null THEN
454 p_action_param := p_action_param||'uwq_employee_id="'||l_employee_id||'"';
455 end if;
456 END IF;
457
458 p_action_param := p_action_param||'uwq_service_key_name="'||'SERVICE_REQUEST_NUMBER'||'"';
459 p_action_param := p_action_param||'uwq_service_key_value="'||l_service_request_num||'"';
460 RETURN;
461 --New HelpDesk logic to handle Employee Id
462 ELSIF l_whichIVR = 'Employee' THEN
463 p_action_param := p_action_param||'uwq_employee_id="'||l_employee_id||'"';
464 RETURN;
465 ELSIF l_whichIVR = 'InstanceName' THEN
466 IF (l_match = '1') THEN
467 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
468 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
469
470 --bug 5640146
471 IF l_service_key_id IS NOT NULL THEN
472 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
473 END IF;
474
475 IF l_uwq_skey_org_id IS NOT NULL THEN
476 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
477 END IF;
478 --end of bug 5640146
479 END IF;
480
481 p_action_param := p_action_param||'uwq_service_key_name="'||'INSTANCE_NAME'||'"';
482 p_action_param := p_action_param||'uwq_service_key_value="'||l_instance_name||'"';
483 RETURN;
484 ELSIF l_whichIVR = 'InstanceNum' THEN
485 IF (l_match = '1') THEN
486 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
487 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
488
489 --bug 5640146
490 IF l_service_key_id IS NOT NULL THEN
491 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
492 END IF;
493
494 IF l_uwq_skey_org_id IS NOT NULL THEN
495 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
496 END IF;
497 --end of bug 5640146
498 END IF;
499
500 p_action_param := p_action_param||'uwq_service_key_name="'||'INSTANCE_NUMBER'||'"';
501 p_action_param := p_action_param||'uwq_service_key_value="'||l_instance_num||'"';
502 RETURN;
503 ELSIF l_whichIVR = 'SerialNum' THEN
504 IF (l_match = '1') THEN
505 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
506 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
507
508 --bug 5640146
509 IF l_service_key_id IS NOT NULL THEN
510 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
511 END IF;
512
513 IF l_uwq_skey_org_id IS NOT NULL THEN
514 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
515 END IF;
516 --end of bug 5640146
517 END IF;
518
519 p_action_param := p_action_param||'uwq_service_key_name="'||'SERIAL_NUMBER'||'"';
520 p_action_param := p_action_param||'uwq_service_key_value="'||l_serial_num||'"';
521 RETURN;
522 ELSIF l_whichIVR = 'TagNum' THEN
523 IF (l_match = '1') THEN
524 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
525 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
526
527 --bug 5640146
528 IF l_service_key_id IS NOT NULL THEN
529 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
530 END IF;
531
532 IF l_uwq_skey_org_id IS NOT NULL THEN
533 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
534 END IF;
535 --end of bug 5640146
536 END IF;
537
538 p_action_param := p_action_param||'uwq_service_key_name="'||'EXTERNAL_REFERENCE'||'"';
539 p_action_param := p_action_param||'uwq_service_key_value="'||l_tag_num||'"';
540 RETURN;
541 ELSIF l_whichIVR = 'SystemName' THEN
542 IF (l_match = '1') THEN
543 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
544 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
545
546 --bug 5640146
547 IF l_service_key_id IS NOT NULL THEN
548 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
549 END IF;
550
551 IF l_uwq_skey_org_id IS NOT NULL THEN
552 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
553 END IF;
554 --end of bug 5640146
555 END IF;
556
557 p_action_param := p_action_param||'uwq_service_key_name="'||'SYSTEM_NUMBER'||'"';
558 p_action_param := p_action_param||'uwq_service_key_value="'||l_system_name||'"';
559 RETURN;
560 ELSIF l_whichIVR = 'RMANum' THEN
561 IF (l_match = '1') THEN
562 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
563 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
564
565 --bug 5640146
566 IF l_service_key_id IS NOT NULL THEN
567 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
568 END IF;
569
570 IF l_uwq_skey_org_id IS NOT NULL THEN
571 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
572 END IF;
573 --end of bug 5640146
574 END IF;
575
576 p_action_param := p_action_param||'uwq_service_key_name="'||'RMA_NUMBER'||'"';
577 p_action_param := p_action_param||'uwq_service_key_value="'||l_rma_num||'"';
578 RETURN;
579 ELSIF l_whichIVR = 'OrderNum' THEN
580 IF (l_match = '1') THEN
581 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
582 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
583
584 --bug 5640146
585 IF l_service_key_id IS NOT NULL THEN
586 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
587 END IF;
588
589 IF l_uwq_skey_org_id IS NOT NULL THEN
590 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
591 END IF;
592 --end of bug 5640146
593 END IF;
594
595 p_action_param := p_action_param||'uwq_service_key_name="'||'ORDER_NUMBER'||'"';
596 p_action_param := p_action_param||'uwq_service_key_value="'||l_order_num||'"';
597 RETURN;
598 ELSIF l_whichIVR = 'SSN' THEN
599 IF (l_match = '1') THEN
600 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
601
602 --bug 5640146
603 IF l_service_key_id IS NOT NULL THEN
604 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
605 END IF;
606
607 IF l_uwq_skey_org_id IS NOT NULL THEN
608 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
609 END IF;
610 --end of bug 5640146
611 END IF;
612
613 p_action_param := p_action_param||'uwq_service_key_name="'||'SSN'||'"';
614 p_action_param := p_action_param||'uwq_service_key_value="'||l_ssn||'"';
615 RETURN;
616 ELSIF l_whichIVR = 'ContractNum' THEN
617 IF (l_match = '1') THEN
618 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
619
620 --bug 5640146
621 IF l_service_key_id IS NOT NULL THEN
622 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
623 END IF;
624
625 IF l_uwq_skey_org_id IS NOT NULL THEN
626 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
627 END IF;
628 --end of bug 5640146
629 END IF;
630
631 p_action_param := p_action_param||'uwq_service_key_name="'||'CONTRACT_NUMBER'||'"';
632 p_action_param := p_action_param||'uwq_service_key_value="'||l_contract_num||'"';
633 RETURN;
634 ELSIF l_whichIVR = 'InvoiceNum' THEN
635 IF (l_match = '1') THEN
636 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
637 p_action_param := p_action_param||'uwq_skey_account_id="'||l_skey_account_id||'"';
638
639 --bug 5640146
640 IF l_service_key_id IS NOT NULL THEN
641 p_action_param := p_action_param||'service_key_id="'||l_service_key_id||'"';
642 END IF;
643
644 IF l_uwq_skey_org_id IS NOT NULL THEN
645 p_action_param := p_action_param||'uwq_skey_org_id="'||l_uwq_skey_org_id||'"';
646 END IF;
647 --end of bug 5640146
648 END IF;
649
650 p_action_param := p_action_param||'uwq_service_key_name="'||'INVOICE_NUMBER'||'"';
651 p_action_param := p_action_param||'uwq_service_key_value="'||l_invoice_num||'"';
652 RETURN;
653 ELSIF l_whichIVR = 'CompletePhone' THEN
654 IF (l_match = '1') THEN
655 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'"';
656 p_action_param := p_action_param||'uwq_phone_id="'||l_uwq_phone_id ||'"';
657 ELSIF (l_match = 'M') THEN
658 p_action_param := p_action_param||'uwq_stripped_reverse_ani="'|| l_uwq_stripped_rev_ani2 ||'"';
659 END IF;
660
661 p_action_param := p_action_param||'uwq_complete_phone_num="'||l_uwq_complete_phone_num ||'"';
662 p_action_param := p_action_param||'uwq_reverse_ani="'||l_uwq_reverse_ani ||'"';
663 p_action_param := p_action_param||'uwq_multi_record_match="'||l_uwq_multi_record_match2||'"';
664 RETURN;
665 END IF;
666 ------------------------------------
667 ELSE --Did not go thru CC customer lookup
668 -----------------------------------
669 --At this point, it is either an inbound call or a transfer/conference call for which
670 --insufficient data has been passed. In both cases, need to process inbound call data.
671 --First preference is given to user hook.
672 IF JTF_USR_HKS.OK_TO_EXECUTE('CSC_UWQ_FORM_ROUTE', 'CSC_UWQ_FORM_OBJ', 'B', 'C') THEN
673 l_customer_id := NULL;
674 l_cust_account_id := NULL;
675 l_phone_id := NULL;
676
677 CSC_UWQ_FORM_ROUTE_CUHK.CSC_UWQ_FORM_OBJ_PRE(p_ieu_media_data => p_ieu_media_data,
678 x_party_id => l_customer_id,
679 x_cust_account_id => l_cust_account_id,
680 x_phone_id => l_phone_id);
681
682 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
683 If l_cust_account_id is not null Then
684 p_action_param := p_action_param||'uwq_cust_account_id="'||l_cust_account_id ||'"';
685 End If;
686 If l_phone_id is not null Then
687 p_action_param := p_action_param||'uwq_phone_id="'||l_phone_id ||'"';
688 End If;
689 RETURN;
690
691 --If user hook is not executed, then process inbound call data
692 ELSE
693
694 if l_call_reason is not null then
695 p_action_param := p_action_param||'uwq_call_reason="'||l_call_reason||'"';
696 end if;
697
698 --Set service key name and value based on the hierarchy specified in SRD
699 if l_customer_id is not null then
700 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id||'"';
701 --this fix is for user who uses default or custom lookup, even when l_customer_id is not null
702 --it will still check for service key value pairs
703 if (l_service_request_num is null) AND
704 (l_instance_name is null) AND
705 (l_instance_num is null) AND
706 (l_serial_num is null) AND
707 (l_tag_num is null) AND
708 (l_system_name is null) AND
709 (l_rma_num is null) AND
710 (l_order_num is null) AND
711 (l_ssn is null) AND
712 (l_contract_num is null) AND
713 (l_invoice_num is null) THEN
714 RETURN;
715 end if;
716 end if;
717
718 if l_service_request_num is not null then
719 p_action_param := p_action_param||'uwq_service_key_name="'||'SERVICE_REQUEST_NUMBER'||'"';
720 p_action_param := p_action_param||'uwq_service_key_value="'||l_service_request_num||'"';
721 RETURN;
722 elsif l_employee_id is not null then
723 p_action_param := p_action_param||'uwq_employee_id="'||l_employee_id||'"';
724 RETURN;
725 elsif l_instance_name is not null then
726 p_action_param := p_action_param||'uwq_service_key_name="'||'INSTANCE_NAME'||'"';
727 p_action_param := p_action_param||'uwq_service_key_value="'||l_instance_name||'"';
728 RETURN;
729 elsif l_instance_num is not null then
730 p_action_param := p_action_param||'uwq_service_key_name="'||'INSTANCE_NUMBER'||'"';
731 p_action_param := p_action_param||'uwq_service_key_value="'||l_instance_num||'"';
732 RETURN;
733 elsif l_serial_num is not null then
734 p_action_param := p_action_param||'uwq_service_key_name="'||'SERIAL_NUMBER'||'"';
735 p_action_param := p_action_param||'uwq_service_key_value="'||l_serial_num||'"';
736 RETURN;
737 elsif l_tag_num is not null then
738 p_action_param := p_action_param||'uwq_service_key_name="'||'EXTERNAL_REFERENCE'||'"';
739 p_action_param := p_action_param||'uwq_service_key_value="'||l_tag_num||'"';
740 RETURN;
741 elsif l_system_name is not null then
742 p_action_param := p_action_param||'uwq_service_key_name="'||'SYSTEM_NUMBER'||'"';
743 p_action_param := p_action_param||'uwq_service_key_value="'||l_system_name||'"';
744 RETURN;
745 elsif l_rma_num is not null then
746 p_action_param := p_action_param||'uwq_service_key_name="'||'RMA_NUMBER'||'"';
747 p_action_param := p_action_param||'uwq_service_key_value="'||l_rma_num||'"';
748 RETURN;
749 elsif l_order_num is not null then
750 p_action_param := p_action_param||'uwq_service_key_name="'||'ORDER_NUMBER'||'"';
751 p_action_param := p_action_param||'uwq_service_key_value="'||l_order_num||'"';
752 RETURN;
753 elsif l_ssn is not null then
754 p_action_param := p_action_param||'uwq_service_key_name="'||'SSN'||'"';
755 p_action_param := p_action_param||'uwq_service_key_value="'||l_ssn||'"';
756 RETURN;
757 elsif l_contract_num is not null then
758 p_action_param := p_action_param||'uwq_service_key_name="'||'CONTRACT_NUMBER'||'"';
759 p_action_param := p_action_param||'uwq_service_key_value="'||l_contract_num||'"';
760 RETURN;
761 elsif l_invoice_num is not null then
762 p_action_param := p_action_param||'uwq_service_key_name="'||'INVOICE_NUMBER'||'"';
763 p_action_param := p_action_param||'uwq_service_key_value="'||l_invoice_num||'"';
764 RETURN;
765 end if;
766
767 --If the control comes to this part of the code, then that means the party_id may or may not
768 --be available to be passed to CC. Therefore, need to go through the extra logic to derive
769 --the party_id to be passed to CC.
770 --Process each of the uwq supplied IVR params to try and resolve the party_id
771 --Quit when an id is located. The order processed is significant as it attempts
772 --to process the most likely IVR values to yield a party id
773
774 if l_email_cust_id is not null then
775 p_action_param := p_action_param||'uwq_party_id="'||l_email_cust_id||'"';
776 RETURN;
777 end if;
778
779 l_value := get_media_value('CustomerNum');
780 IF l_value IS NOT NULL THEN
781 l_customer_id := null;
782 l_customer_id := CSC_ROUTING_UTL.Get_Customer_From_CustomerNum(p_party_number => l_value);
783 IF l_customer_id IS NOT NULL THEN
784 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'"';
785 RETURN;
786 END IF;
787 END IF;
788
789 --Preserve account number and get account id and last update date
790 --so that it does not have to be re-queried on the client side
791 l_value := get_media_value('AccountCode');
792 IF l_value IS NOT NULL THEN
793 l_customer_id := null;
794 CSC_ROUTING_UTL.Get_Cust_Acct_From_Account_Num( p_cust_acct_number => l_value,
795 x_party_id => l_customer_id,
796 x_cust_account_id => l_cust_account_id,
797 x_last_update_date => l_acct_last_update_date);
798 IF l_customer_id IS NOT NULL THEN
799 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'"';
800 p_action_param := p_action_param||'uwq_cust_account_id="'||l_cust_account_id ||'"';
801 p_action_param := p_action_param||'uwq_last_update_date="'||l_acct_last_update_date ||'"';
802 RETURN;
803 END IF;
804 END IF;
805
806 --Check for phone number
807 if l_phone_passed_flag = 'Y' then
808 l_complete_phone_num := l_ccode || l_acode || l_phone_number;
809 IF l_complete_phone_num IS NOT NULL THEN
810 l_customer_id := NULL;
811 l_rphone := HZ_PHONE_NUMBER_PKG.transpose(l_complete_phone_num);
812 l_customer_id := CSC_ROUTING_UTL.get_customer_from_reverse_ANI(l_rphone,
813 l_uwq_multi_record_match,
814 l_phone_id);
815 IF l_customer_id IS NOT NULL THEN
816 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'" uwq_multi_record_match='
817 ||NVL(l_uwq_multi_record_match,'""')||' uwq_reverse_ani="'||l_rphone||'"';
818 p_action_param := p_action_param||'uwq_phone_id="'||l_phone_id ||'"';
819 p_action_param := p_action_param||'uwq_complete_phone_num="'||l_complete_phone_num ||'"';
820 RETURN;
821 END IF;
822 END IF;
823 end if;
824
825 l_value := get_media_value('ContactNum');
826 IF l_value IS NOT NULL THEN
827 l_customer_id := null;
828 l_customer_id := CSC_ROUTING_UTL.Get_Customer_From_CustomerNum( p_party_number => l_value);
829 IF l_customer_id IS NOT NULL THEN
830 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'"';
831 RETURN;
832 END IF;
833 END IF;
834
835 --Since none of the other data worked, use the ANI phone number which is always present
836 l_value := get_media_value('occtANI');
837 IF l_value IS NOT NULL THEN
838
839 --Note: standard REVERSE function cannot be used in PL-SQL as it is a reserved word in PL-SQL
840 --to replace this function REVERSE_NUMBER function was created
841 l_customer_id := NULL;
842 l_rphone := HZ_PHONE_NUMBER_PKG.transpose(l_value);
843 l_customer_id := CSC_ROUTING_UTL.get_customer_from_reverse_ANI(l_rphone,
844 l_uwq_multi_record_match,
845 l_phone_id);
846 p_action_param := p_action_param||'uwq_party_id="'||l_customer_id ||'" uwq_multi_record_match='
847 ||NVL(l_uwq_multi_record_match,'""')||' uwq_reverse_ani="'||l_rphone||'"';
848 p_action_param := p_action_param||'uwq_phone_id="'||l_phone_id ||'"';
849 RETURN;
850 END IF;
851 p_action_param := p_action_param||'uwq_party_id="'||l_dummy_customer_id ||'"';
852
853 END IF;
854 END IF; --new R12 end if
855 EXCEPTION
856 WHEN OTHERS THEN
857 p_action_param := p_action_param||'uwq_party_id="'||l_dummy_customer_id ||'"';
858
859 END CSC_UWQ_FORM_OBJ;
860
861 END CSC_UWQ_FORM_ROUTE;
[Home] [Help]