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