[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_SEL_PKG
Source
1 PACKAGE BODY AST_UWQ_SEL_PKG AS
2 /* $Header: asttmslb.pls 120.1 2006/01/09 03:17:37 rkumares noship $ */
3 ------------------------------------------------------------------------------
4 -- Procedure : setDefaults
5 -- Usage :
6 -- Description : This procedure sets the default every time
7 --- this package gets executed
8
9 l_eventName varchar2(60);
10 l_partyId number;
11 l_partyType varchar2(30);
12 --l_customerNumber number;
13 l_customerNumber varchar2(30);
14 l_contactId number;
15 --l_contactNumber number;
16 l_contactNumber varchar2(30);
17 l_eventConfCode varchar2(30);
18 l_eventId number;
19 l_collateralReqNum VARCHAR2(30) ;
20 l_collateralId number;
21 l_campaignCode varchar2(30);
22 --l_campaignId number;
23 l_sourcecodeID number;
24 l_CampScheduleID number;
25 l_dnis varchar2(30);
26 l_callId varchar2(80);
27 l_ani varchar2(40);
28 l_accountCode varchar2(40);
29 l_accountId number;
30 l_usage varchar2(60);
31 l_agentID varchar2(60);
32 l_mediaType varchar2(80);
33 l_mediaItemID varchar2(60);
34 l_workitemID varchar2(60);
35 l_sendername varchar2(60);
36 l_subject varchar2(60);
37 l_messageID varchar2(60);
38 l_MoreAniMatch varchar2(10);
39 /** added by vimpi on 19th october */
40 l_task_id varchar2(60) ;
41 l_source_code varchar2(60) ;
42 l_source_code_id varchar2(60) ;
43 l_customer_trx_id number;
44 l_trx_view_by varchar2(60) ;
45 l_InvoiceNum varchar2(60) ;
46 l_AccountRolesExist VARCHAR2(10);
47
48 l_xfr_partyId number;
49 l_xfr_sourcecodeID number;
50
51 l_quoteNum number;
52 l_quoteID number;
53 l_orderNum number;
54 l_orderID number;
55 l_marketingPin varchar2(30);
56 l_ContractNum varchar2(120);
57 l_ContractNumMod varchar2(120);
58 l_ServiceKey varchar2(30);
59 l_SRNum varchar2(64);
60
61 -- kmahajan - added for bug 2695645
62 l_customerID number;
63
64 --for handlefootask function below..jraj..11/5/02
65 l_source_object_type varchar2(300);
66 l_source_campaign_id number;
67 l_nm_party_id number;
68
69 l_blocked boolean;
70
71 PROCEDURE LogMessage(l_Message varchar2) IS
72 BEGIN
73 if (l_DumpData = 'Y') then
74 ast_debug_pub.LogMessage(l_Message, -10, 'Y');
75 end if;
76 END;
77
78 PROCEDURE setDefaults IS
79 BEGIN
80
81 l_eventName := '';
82 l_partyId := 0;
83 l_partyType := '';
84 l_customerNumber := '';
85 l_contactId := 0;
86 l_contactNumber := '';
87 l_eventConfCode := '';
88 l_eventId := 0;
89 l_collateralReqNum := '';
90 l_collateralId := 0;
91 l_campaignCode := '';
92 --l_campaignId := 0;
93 l_campScheduleID := 0;
94 l_sourcecodeID := 0;
95 l_dnis := '';
96 l_callId := '';
97 l_ani := '';
98 l_accountCode := '';
99 l_accountId := 0;
100 l_usage := '';
101 l_agentID := '';
102 l_mediaType := '';
103 l_mediaItemID := '';
104 l_workitemID := '';
105 l_sendername := '';
106 l_subject := '';
107 l_messageID := '';
108 l_MoreAniMatch := 'N';
109 --added by vimpi on 11th feb/2001
110 l_customer_trx_id := 0;
111 l_trx_view_by := '' ;
112 l_AccountRolesExist := 'N';
113 l_xfr_partyId := 0;
114 l_xfr_sourcecodeID := 0;
115
116 l_quoteNum := 0;
117 l_quoteID := 0;
118 l_orderNum := 0;
119 l_orderID := 0;
120 l_marketingPin := '';
121 l_ContractNum := '';
122 l_ContractNumMod := '';
123 l_ServiceKey := '';
124 l_SRNum := '';
125
126 l_blocked := false;
127
128 -- kmahajan - added for bug 2695645
129 l_customerID := 0;
130
131 END setDefaults;
132
133 -- private package to return the param type value
134 PROCEDURE getCallData(p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST) IS
135 BEGIN
136
137 FOR i IN 1 .. p_mediaTable.COUNT LOOP
138 LogMessage('MediaData[' || i || ']: ' || p_mediaTable(i).param_name || ' = ' || p_mediaTable(i).param_value);
139 if ( upper(p_mediaTable(i).param_name) = 'OCCTEVENTNAME' ) then
140 l_eventname := p_mediaTable(i).param_value;
141 /* 3/13/3 - kmahajan - this is long obsolete
142 elsif ( upper(p_mediaTable(i).param_name) = 'IEU_AMS_CAMPAIGN_ID' ) then
143 l_CampaignID := p_mediaTable(i).param_value;
144 */
145 elsif ( upper(p_mediaTable(i).param_name) = 'CAMPAIGN_SCHEDULE_ID' ) then
146 l_CampScheduleID := p_mediaTable(i).param_value;
147 --added by vimpi on 11th feb 2002
148 elsif ( upper(p_mediaTable(i).param_name) = 'INVOICENUM' ) THEN
149
150 l_InvoiceNum := p_mediaTable(i).param_value;
151
152 elsif ( upper(p_mediaTable(i).param_name) = 'CUSTOMERNUMBER' ) then
153
154 l_customerNumber := p_mediaTable(i).param_value;
155
156 --elsif ( upper(p_mediaTable(i).param_name) = 'CUSTOMERID' ) THEN
157 elsif (p_mediaTable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_CUSTOMER_ID) THEN
158
159 l_customerID := to_number(p_mediaTable(i).param_value);
160
161 --elsif ( upper(p_mediaTable(i).param_name) = 'CUSTOMERNUM' ) then
162 elsif (p_mediaTable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_PARTY_NUMBER) then
163
164 l_customerNumber := p_mediaTable(i).param_value;
165
166 elsif ( upper(p_mediaTable(i).param_name) = 'CONTACTNUM' ) then
167
168 l_contactnumber := p_mediaTable(i).param_value;
169
170 ELSIF ( upper(p_mediatable(i).param_name)='EVENTCONFCODE') THEN
171
172 l_eventConfCode := p_mediaTable(i).param_value;
173
174 ELSIF ( upper(p_mediatable(i).param_name)='COLREQNUM') THEN
175
176 l_collateralReqNum := p_mediaTable(i).param_value;
177
178 elsif ( upper(p_mediaTable(i).param_name) = 'PROMOTIONCODE' ) then
179
180 l_campaignCode := p_mediaTable(i).param_value;
181
182 --elsif ( upper(p_mediaTable(i).param_name) = 'OCCTDNIS' ) then
183 elsif (p_mediaTable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_DNIS) then
184
185 l_dnis := p_mediaTable(i).param_value;
186
187 elsif ( upper(p_mediaTable(i).param_name) = 'OCCTCALLID' ) then
188
189 l_callId := p_mediaTable(i).param_value;
190
191 --elsif ( upper(p_mediaTable(i).param_name) = 'OCCTANI' ) then
192 elsif (p_mediaTable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_ANI) then
193
194 l_ani := p_mediaTable(i).param_value;
195
196 elsif ( upper(p_mediaTable(i).param_name) = 'ACCOUNTCODE' ) then
197
198 l_accountCode := p_mediaTable(i).param_value;
199
200 elsif ( upper(p_mediaTable(i).param_name) = 'OCCTAGENTID' ) THEN
201
202 l_agentID := p_mediaTable(i).param_value;
203
204 elsif ( upper(p_mediaTable(i).param_name) = 'OCCTMEDIATYPE' ) THEN
205
206 l_mediaType := p_mediaTable(i).param_value;
207
208 elsif ( upper(p_mediaTable(i).param_name) = 'OIEMMESSAGEID' ) THEN
209
210 l_messageID := p_mediaTable(i).param_value;
211
212 elsif ( upper(p_mediaTable(i).param_name) = 'OIEMSUBJECT' ) THEN
213
214 l_subject := p_mediaTable(i).param_value;
215
216 elsif ( upper(p_mediaTable(i).param_name) = 'OIEMSENDERNAME' ) THEN
217
218 l_sendername := p_mediaTable(i).param_value;
219
220 elsif ( upper(p_mediaTable(i).param_name) = 'WORKITEMID' ) THEN
221
222 l_workitemID := p_mediaTable(i).param_value;
223
224 elsif ( upper(p_mediaTable(i).param_name) = 'OCCTMEDIAITEMID' ) THEN
225
226 l_mediaItemID := p_mediaTable(i).param_value;
227
228 elsif ( upper(p_mediaTable(i).param_name) = 'PARTY_ID' ) THEN
229
230 l_customerID := to_number(p_mediaTable(i).param_value);
231
232 -- added for Warm Transfer functionality
233 elsif (upper(p_mediaTable(i).param_name) = 'XFR_PARTY_ID') THEN
234 l_xfr_partyId := to_number(p_mediaTable(i).param_value);
235 l_usage := 'QUERY_TRANSFER';
236 elsif (upper(p_mediaTable(i).param_name) = 'XFR_SOURCE_CODE_ID') THEN
237 l_xfr_sourcecodeID := p_mediaTable(i).param_value;
238
239 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_QUOTE_NUMBER) THEN
240 l_quoteNum := to_number(p_mediaTable(i).param_value);
241 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_ORDER_NUMBER) THEN
242 l_orderNum := to_number(p_mediaTable(i).param_value);
243 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_COLLATERAL_REQUEST_NUMBER) THEN
244 l_collateralReqNum := p_mediaTable(i).param_value;
245 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_ACCOUNT_NUMBER) THEN
246 l_accountCode := p_mediaTable(i).param_value;
247 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_EVENT_REGISTRATION_CODE) THEN
248 l_eventConfCode := p_mediaTable(i).param_value;
249 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_MARKETING_PIN) THEN
250 l_marketingPin := p_mediaTable(i).param_value;
251 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_CONTRACT_NUMBER) THEN
252 l_ContractNum := p_mediaTable(i).param_value;
253 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_CONTRACT_NUMBER_MODIFIER) THEN
254 l_ContractNumMod := p_mediaTable(i).param_value;
255 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_SERVICE_KEY) THEN
256 l_ServiceKey := p_mediaTable(i).param_value;
257 ELSIF (p_mediatable(i).param_name = CCT_INTERACTIONKEYS_PUB.KEY_SERVICE_REQUEST_NUMBER) THEN
258 l_SRNum := p_mediaTable(i).param_value;
259
260 -- added to check if the agent is already on a call (prereq: UWQ bug 2495619)
261 elsif (p_mediaTable(i).param_name = 'UWQ_BLOCK_MODE') THEN
262 if p_mediaTable(i).param_value = 'T' then
263 l_blocked := true;
264 LogMessage('UWQ_BLOCK_MODE = T');
265 else
266 l_blocked := false;
267 end if;
268
269 END IF;
270
271 END LOOP;
272
273 END getCallData ;
274
275 -- procedure to construct the paramlist to be passed to the form
276
277 FUNCTION constructparam RETURN VARCHAR2 IS
278 l_paramlist VARCHAR2(3000);
279 BEGIN
280
281 l_paramlist := '';
282 if ( l_xfr_partyId <> 0 ) THEN
283 l_paramlist := l_paramlist || 'PARTY_ID' || '="' || l_xfr_partyId ||'" ';
284 elsif ( l_partyId <> 0 ) THEN
285 l_paramlist := l_paramlist || 'PARTY_ID' || '="' || l_partyId ||'" ';
286 end if;
287
288 IF ( l_partyType is not NULL ) then
289 l_paramlist := l_paramlist || 'PARTY_TYPE' || '="' || l_partyType || '" ';
290 END IF;
291
292 IF ( l_contactid <> 0 ) THEN
293
294 l_paramlist := l_paramlist || 'PARTY_CONTACT_ID' || '="' || l_contactId ||'" ';
295
296 END IF;
297
298 IF ( l_eventid <> 0 ) THEN
299
300 l_paramlist := l_paramlist || 'EVENT_REG_ID' || '="' || l_eventId ||'" ';
301
302 END IF;
303
304 IF ( l_collateralId <> 0 ) THEN
305
306 l_paramlist := l_paramlist || 'COLL_REQ_ID' || '="' || l_collateralId ||'" ';
307
308 END IF;
309
310 /* 3/13/3 - kmahajan - this is long obsolete
311 IF (l_campaignId <> 0 ) THEN
312
313 l_paramlist := l_paramlist || 'CAMPAIGN_ID' || '="' || l_campaignId ||'" ';
314
315 END IF;
316 */
317
318 if (l_xfr_sourcecodeID <> 0) THEN
319 l_paramlist := l_paramlist || 'SOURCE_CAMPAIGN_ID' || '="' || l_xfr_sourcecodeID ||'" ';
320 elsif (l_sourcecodeID <> 0) THEN
321 l_paramlist := l_paramlist || 'SOURCE_CAMPAIGN_ID' || '="' || l_sourcecodeID ||'" ';
322 end if;
323
324 if (l_CampaignCode is not null) THEN
325
326 l_paramlist := l_paramlist || 'CAMPAIGN_SOURCE_CODE' || '="' || l_campaignCode ||'" ';
327
328 end if;
329
330 IF (l_CampScheduleID <> 0) THEN
331
332 l_paramlist := l_paramlist || 'CAMPAIGN_SCHEDULE_ID' || '="' || l_campScheduleId ||'" ';
333
334 END IF;
335
336 IF ( l_callID is not null ) THEN
337
338 l_paramlist := l_paramlist || 'TM_CALL_ID' || '="' || l_callID ||'" ';
339
340 END IF;
341
342 IF ( l_accountID <> 0 ) THEN
343
344 l_paramlist := l_paramlist || 'CUST_ACCOUNT_ID' || '="' || l_accountId ||'" ';
345
346 END IF;
347 --added by vimpi on 11thfeb 2002
348 IF ( l_customer_trx_id <> 0 ) THEN
349
350 l_paramlist := l_paramlist || 'Customer_TRX_ID' || '="' || l_customer_trx_id ||'" ';
351
352 END IF;
353 IF ( l_trx_view_by is not null ) THEN
354
355 l_paramlist := l_paramlist || 'TRX_VIEW_BY' || '="' || l_trx_view_by ||'" ';
356
357 END IF;
358
359 IF ( l_eventname is not null ) THEN
360
361 l_paramlist := l_paramlist || 'UWQ_EVENTNAME' || '="' || l_eventname ||'" ';
362
363 END IF;
364
365
366 IF ( l_dnis is not null ) THEN
367
368 l_paramlist := l_paramlist || 'TM_DNIS' || '="' || l_dnis ||'" ';
369
370
371 END IF;
372
373 IF ( l_ani is not null) THEN
374
375 l_paramlist := l_paramlist || 'TM_ANI' || '="' || l_ani ||'" ';
376
377 END IF;
378
379
380 IF ( l_mediaType is not null ) THEN
381
382 l_paramlist := l_paramlist || 'UWQ_MEDIATYPE' || '="' || l_mediatype ||'" ';
383
384 END IF;
385
386 IF ( l_mediaItemID is not null) THEN
387
388 l_paramlist := l_paramlist || 'UWQ_MEDIAITEM_ID' || '="' || l_mediaItemID || '" ';
389
390 END IF;
391
392 IF ( l_workitemID is not null ) THEN
393
394 l_paramlist := l_paramlist || 'UWQ_WORKITEM_ID' || '="' || l_workitemID ||'" ';
395
396 END IF;
397
398 IF ( l_sendername is not null ) THEN
399
400 l_paramlist := l_paramlist || 'EM_SENDERNAME' || '="' || l_sendername ||'" ';
401
402 END IF;
403
404 IF ( l_subject is not null ) THEN
405
406 l_paramlist := l_paramlist || 'EM_SUBJECT' || '="' || l_subject ||'" ';
407
408 END IF;
409
410 IF ( l_messageID is not null) THEN
411
412 l_paramlist := l_paramlist || 'EM_MESSAGE_ID' || '="' || l_messageID ||'" ';
413
414 END IF;
415
416 IF (l_MoreAniMatch = 'Y') then
417
418 l_Usage := 'QUERY_ANI';
419
420 END IF;
421
422 IF ((l_usage is null) and (l_partyId = 0) and (l_xfr_partyID = 0)) THEN
423
424 l_usage := 'QUERY_LKP';
425
426 END IF;
427
428 -- append the usage parameter
429 l_paramlist := l_paramlist || ' CALLED_FROM = "UWQ" ';
430
431 if (l_usage is not null) then
432 l_paramlist := l_paramlist || ' USAGE' || '="' || l_usage ||'" ';
433 end if;
434
435 LogMessage('Param List Before Return:' || l_paramList);
436 RETURN l_paramlist;
437
438 EXCEPTION
439 when others then
440 LogMessage('Exception in constructparam. Param List: ' || l_paramList);
441 LogMessage('SQLCODE: ' || SQLCODE);
442 LogMessage('SQLERRM: ' || SQLERRM);
443
444 l_paramlist := ' CALLED_FROM = "UWQ" ';
445 LogMessage('Param List reset to:' || l_paramList);
446
447 return l_paramlist;
448
449 END constructparam;
450
451 -- procedure get details from event confirmation number
452 PROCEDURE getDtlsFromEvent IS
453
454 CURSOR C_GetEventDetails(x_eventConfCode varchar2) IS
455 --SELECT registrant_party_Id,registrant_contact_Id,event_offer_Id
456 --FROM ams_event_registrations_v
457 SELECT registrant_party_Id,event_offer_Id
458 FROM ams_event_registrations
459 WHERE confirmation_code = x_eventConfCode;
460
461
462 BEGIN
463
464 OPEN C_GetEventDetails(l_eventConfCode);
465 --FETCH C_GetEventDetails INTO l_partyId, l_contactId, l_eventId;
466 FETCH C_GetEventDetails INTO l_partyId, l_eventId;
467 IF ( c_geteventdetails%NOTFOUND) THEN
468 l_partyId:=0;
469 --l_contactid := 0;
470 l_eventid := 0;
471 END IF;
472 CLOSE C_GetEventDetails;
473 END getDtlsFromEvent;
474
475 PROCEDURE Getcampaigncode IS
476
477 CURSOR c_campaignId(x_inbound_phone VARCHAR2) IS
478 SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE
479 FROM aMS_SOURCE_CODES SOC, AMS_ACT_CONTACT_POINTS AACP
480 WHERE SOC.SOURCE_CODE_FOR_ID = AACP.ACT_CONTACT_USED_BY_ID AND
481 SOC.ARC_SOURCE_CODE_FOR = AACP.ARC_CONTACT_USED_BY AND
482 SOC.ACTIVE_FLAG = 'Y' AND AACP.CONTACT_POINT_TYPE = 'PHONE'
483 AND AACP.CONTACT_POINT_VALUE = x_inbound_phone;
484
485 CURSOR c_campaigncode(x_campaigncode VARCHAR2) IS
486 SELECT source_code_id
487 FROM ams_source_codes
488 WHERE source_code = x_campaignCode
489 AND active_flag = 'Y';
490 lx_CampaignCode varchar2(100);
491 lx_sourcecodeID number;
492
493 BEGIN
494
495 LogMessage('Inside Campaign Code Match');
496 IF ( l_campaigncode IS NOT NULL ) THEN
497
498 OPEN c_campaigncode(l_CampaignCode);
499 FETCH c_campaigncode INTO l_sourcecodeID;
500 CLOSE c_campaigncode;
501
502 ELSIF ( l_dnis IS NOT NULL ) THEN
503 LogMessage('Inside DNIS Code Match ' || l_dnis);
504 OPEN c_campaignid(l_dnis);
505 FETCH c_campaignid INTO l_sourcecodeID, l_CampaignCode;
506 IF ( c_campaignid%found) THEN
507 LogMessage('Inside DNIS Code Match Success : Source Code ' || l_CampaignCode
508 || ' Source Code Id ' || l_sourcecodeID);
509 FETCH c_campaignid INTO lx_sourcecodeID, lx_CampaignCode;
510 IF (c_CampaignId%FOUND) then
511
512 LogMessage('MoreCampaign Code Match. Will be using default is set ');
513 lx_campaignCode := FND_PROFILE.VALUE('AST_DEFAULT_SOURCE_CODE');
514
515 if lx_CampaignCode is not NULL then
516 OPEN c_campaigncode(lx_CampaignCode);
517 FETCH c_campaigncode INTO l_sourcecodeID;
518 IF c_CampaignCode%FOUND THEN
519 l_CampaignCode := lx_CampaignCode;
520 END IF;
521 CLOSE c_campaigncode;
522 END IF;
523 END IF;
524 END IF;
525 CLOSE c_campaignid;
526 END IF;
527 EXCEPTION
528 WHEN OTHERS THEN
529 LogMessage('GetCampaignCode Exception Occurred');
530 return;
531
532 END;
533
534
535 -- procedure to get the collateral request Id from a collateral request confirmation
536 -- number
537 PROCEDURE getDtlsFromColReq IS
538
539 CURSOR C_getDtlsFromColReq(x_collateralReqNum number ) IS
540 --SELECT quote_header_id,party_id,cust_account_id,l_contactid
541 SELECT quote_header_id,party_id,cust_account_id
542 FROM ASO_QUOTE_HEADERS_ALL
543 WHERE quote_number = x_collateralReqNum;
544 BEGIN
545
546 OPEN C_getDtlsFromColReq(To_number(l_collateralreqnum));
547 --FETCH C_getDtlsFromColReq INTO l_collateralId,l_partyId, l_accountID,l_contactId;
548 FETCH C_getDtlsFromColReq INTO l_collateralId,l_partyId, l_accountID;
549 IF (c_getdtlsfromcolreq%Notfound )THEN
550 l_collateralid := 0;
551 l_partyID := 0;
552 l_accountID := 0;
553 END IF;
554 CLOSE C_getDtlsFromColReq;
555
556 END getDtlsFromColReq;
557
558 -- private procedure to get the party associated with a quote number
559 PROCEDURE getDtlsFromQuoteNum IS
560
561 CURSOR C_getDtlsFromQuoteNum(x_QuoteNum number ) IS
562 SELECT quote_header_id,party_id
563 FROM ASO_QUOTE_HEADERS_ALL
564 WHERE quote_number = x_QuoteNum;
565 -- and max_version_flag = 'Y';
566 BEGIN
567
568 LogMessage('In getDtlsFromQuoteNum');
569 OPEN C_getDtlsFromQuoteNum(l_quoteNum);
570 FETCH C_getDtlsFromQuoteNum INTO l_quoteID,l_partyId;
571 IF (c_getdtlsfromQuoteNum%Notfound )THEN
572 l_quoteID := 0;
573 l_partyID := 0;
574 END IF;
575 CLOSE C_getDtlsFromQuoteNum;
576
577 END getDtlsFromQuoteNum;
578
579 -- private procedure to get the party associated with an order number
580 PROCEDURE getDtlsFromOrderNum IS
581
582 CURSOR C_getDtlsFromOrderNum(x_OrderNum number ) IS
583 SELECT o.header_id, a.party_id
584 FROM oe_order_headers_all o, hz_cust_accounts a
585 where o.order_number = x_OrderNum
586 and a.cust_account_id = o.sold_to_org_id;
587 BEGIN
588
589 LogMessage('In getDtlsFromOrderNum');
590 OPEN C_getDtlsFromOrderNum(l_OrderNum);
591 FETCH C_getDtlsFromOrderNum INTO l_OrderID,l_partyId;
592 IF (c_getdtlsfromOrderNum%Notfound )THEN
593 l_OrderID := 0;
594 l_partyID := 0;
595 END IF;
596 CLOSE C_getDtlsFromOrderNum;
597
598 END getDtlsFromOrderNum;
599
600 -- private procedure to get the party and source code associated with a Marketing Pin
601 PROCEDURE getDtlsFromMPin IS
602
603 CURSOR C_getDtlsFromMPin(x_MPin number ) IS
604 SELECT s.source_code, s.source_code_id, nvl(l.party_id,0)
605 FROM ams_list_entries l, ams_source_codes s
606 where l.pin_code = x_MPin
607 and l.source_code = s.source_code;
608 BEGIN
609
610 LogMessage('In getDtlsFromMPin');
611 OPEN C_getDtlsFromMPin(l_MarketingPin);
612 FETCH C_getDtlsFromMPin INTO l_campaignCode, l_sourceCodeID, l_partyId;
613 IF (c_getdtlsfromMPin%Notfound )THEN
614 l_campaignCode := '';
615 l_sourcecodeID := 0;
616 l_partyID := 0;
617 END IF;
618 CLOSE C_getDtlsFromMPin;
619
620 END getDtlsFromMPin;
621
622 -- private procedure to get the party associated with a contract
623 PROCEDURE getDtlsFromContractNum IS
624
625 CURSOR C_getDtlsFromContractNum(x_ContractNum varchar2, x_ContractNumMod varchar2 default null) IS
626 SELECT to_number(p.object1_id1)
627 FROM okc_k_party_roles_b p , okc_k_headers_b k
628 where k.contract_number = x_ContractNum
629 and k.contract_number_modifier = nvl(x_ContractNumMod, k.contract_number_modifier)
630 and k.id = p.dnz_chr_id
631 and p.primary_yn = 'Y'
632 and p.jtot_object1_code = 'OKX_PARTY';
633 --and p.object1_id2 = '#';
634 BEGIN
635
636 LogMessage('In getDtlsFromContractNum');
637 if l_ContractNumMod = '' then
638 OPEN C_getDtlsFromContractNum(l_ContractNum, null);
639 else
640 OPEN C_getDtlsFromContractNum(l_ContractNum, l_ContractNumMod);
641 end if;
642 FETCH C_getDtlsFromContractNum INTO l_partyId;
643 IF (c_getdtlsfromContractNum%Notfound )THEN
644 l_partyID := 0;
645 END IF;
646 CLOSE C_getDtlsFromContractNum;
647
648 END getDtlsFromContractNum;
649
650 -- private procedure to get the party associated with a Service Key
651 PROCEDURE getDtlsFromServiceKey IS
652
653 CURSOR C_getDtlsFromServiceKey(x_ServiceKey varchar2 ) IS
654 SELECT owner_party_id
655 FROM csi_item_instances
656 where instance_number = x_ServiceKey
657 and owner_party_source_table = 'HZ_PARTIES';
658 BEGIN
659
660 LogMessage('In getDtlsFromServiceKey');
661 OPEN C_getDtlsFromServiceKey(l_ServiceKey);
662 FETCH C_getDtlsFromServiceKey INTO l_partyId;
663 IF (c_getdtlsfromServiceKey%Notfound )THEN
664 l_partyID := 0;
665 END IF;
666 CLOSE C_getDtlsFromServiceKey;
667
668 END getDtlsFromServiceKey;
669
670 -- private procedure to get the party associated with a Service Request
671 PROCEDURE getDtlsFromSRNum IS
672
673 CURSOR C_getDtlsFromSRNum(x_SRNum varchar2 ) IS
674 SELECT customer_id
675 FROM cs_incidents_all_b
676 where incident_number = x_SRNum;
677 BEGIN
678
679 LogMessage('In getDtlsFromSRNum');
680 OPEN C_getDtlsFromSRNum(l_SRNum);
681 FETCH C_getDtlsFromSRNum INTO l_partyId;
682 IF (c_getdtlsfromSRNum%Notfound )THEN
683 l_partyID := 0;
684 END IF;
685 CLOSE C_getDtlsFromSRNum;
686
687 END getDtlsFromSRNum;
688
689 -- private procedure to get the partyId
690 PROCEDURE GetDtlsFromConNum IS
691 /* kmahajan - just pass back the Party_id of the relationship instead
692 CURSOR C_getDtlsFromConNum(x_contactNum varchar2 ) IS
693 SELECT rel.subject_id, rel.object_id
694 FROM hz_parties p, hz_relationships rel
695 WHERE p.party_number = x_contactNum
696 AND p.party_id = rel.party_id
697 AND rel.subject_type = 'PERSON'
698 AND rel.object_table_name = 'HZ_PARTIES'
699 AND rel.subject_table_name = 'HZ_PARTIES'
700 AND rel.object_type = 'ORGANIZATION';
701 */
702 CURSOR C_getDtlsFromConNum(x_contactNum varchar2 ) IS
703 SELECT party_id, party_type
704 FROM hz_parties
705 WHERE party_number = x_contactNum
706 AND party_type = 'PARTY_RELATIONSHIP';
707 BEGIN
708 OPEN C_getDtlsFromConNum (l_contactNumber);
709 --FETCH C_getDtlsFromConNum INTO l_contactId, l_partyId ;
710 FETCH C_getDtlsFromConNum INTO l_partyId, l_partyType ;
711 IF (C_getDtlsFromConNum%Notfound )THEN
712 l_partyid := 0;
713 l_partytype := '';
714 --l_contactID := 0;
715 END IF;
716 CLOSE C_getDtlsFromConNum;
717 END GetDtlsFromConNum;
718
719 PROCEDURE GetDtlsFromCustNum IS
720 CURSOR C_GetCustId(x_custnum VARCHAR2) IS
721 SELECT party_id, party_type
722 --FROM jtf_parties_v
723 FROM hz_parties
724 WHERE party_number = x_custnum;
725 --AND party_type in ('ORGANIZATION','PERSON');
726
727 BEGIN
728 LogMessage('GetDtls from Customer Number');
729 OPEN c_getcustid(l_customernumber);
730 FETCH c_getcustid INTO l_partyId, l_partyType;
731 IF (c_getcustid%Notfound )THEN
732 l_partyid := 0;
733 l_partytype := '';
734 END IF;
735 CLOSE c_getcustid;
736 EXCEPTION
737 WHEN OTHERS THEN
738 LogMessage('Exception Occurred: GetDtlsFromCustNum ');
739
740 END GetDtlsFromCustNum;
741
742 -- added by rnori 07-APR-03, for bug# 2885131
743 PROCEDURE GetDtlsFromCustID IS
744 CURSOR C_GetCustId(x_custid NUMBER) IS
745 SELECT party_id,party_type
746 FROM hz_parties
747 WHERE party_id = x_custid;
748 BEGIN
749 LogMessage('GetDtls from Customer ID');
750 OPEN c_getcustid(l_customerID);
751 FETCH c_getcustid INTO l_partyId,l_partyType;
752 IF (c_getcustid%Notfound ) THEN
753 l_partyid := 0;
754 l_partytype := '';
755 END IF;
756 CLOSE c_getcustid;
757 EXCEPTION
758 WHEN OTHERS THEN
759 LogMessage('Exception Occured: GetDtlsFromCustID ');
760 END GetDtlsFromCustID;
761
762 PROCEDURE GetDtlsFromAccountNum IS
763
764 CURSOR C_GetCustId(x_account_num VARCHAR2) IS
765 SELECT hzp.party_id, hza.cust_account_id, hzp.party_type
766 FROM hz_cust_accounts hza, hz_parties hzp
767 --SELECT hza.party_id, hza.cust_account_id
768 --FROM hz_cust_accounts hza
769 WHERE hza.account_number = x_account_num and
770 hza.party_id = hzp.party_id;
771
772 CURSOR C_GetAccountRoles (x_account_num VARCHAR2) is
773 select a.cust_account_id
774 from hz_cust_accounts a, hz_cust_account_roles ar
775 where a.account_number = x_account_num
776 and a.cust_account_id = ar.cust_account_id;
777
778 BEGIN
779
780 LogMessage('Inside Account Number match ');
781 open c_getaccountroles(l_accountCode);
782 fetch c_getaccountroles into l_accountID;
783 if c_getaccountroles%FOUND then
784 l_AccountRolesExist := 'Y';
785 l_partyID := 0;
786 close c_getaccountroles;
787 return;
788 end if;
789 close c_getaccountroles;
790 OPEN c_getcustid(l_accountCode);
791 FETCH c_getcustid INTO l_partyId, l_accountID, l_partyType;
792 --FETCH c_getcustid INTO l_partyId, l_accountID;
793 CLOSE c_getcustid;
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 LogMessage('Exception Inside Account Number match ');
798
799 END GetDtlsFromAccountNum;
800
801 --added by vimpi on 11th feb 2002
802 PROCEDURE GetDtlsFromInvoiceNum IS
803
804 CURSOR C_GetCustId( InvoiceNum Number) IS
805 SELECT customer_trx_id
806 FROM ra_customer_trx
807 WHERE trx_number = to_char(InvoiceNum) ;
808
809 BEGIN
810
811 LogMessage('Inside Invoice Number match ');
812 OPEN c_getcustid(l_InvoiceNum);
813 FETCH c_getcustid INTO l_Customer_Trx_Id ;
814
815 CLOSE c_getcustid;
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 LogMessage('Exception Inside Invoice Number match ');
820
821 END GetDtlsFromInvoiceNum;
822
823
824 PROCEDURE GetDtlsFromPhoneNum IS
825
826 CURSOR C_getDtlsFromPhone(x_phonenumber varchar2 ) IS
827 SELECT cp.owner_table_id, p.party_type
828 FROM hz_contact_points cp, hz_parties p
829 WHERE cp.transposed_phone_number like x_phonenumber
830 AND cp.owner_table_name = 'HZ_PARTIES'
831 AND cp.status = 'A'
832 AND cp.owner_table_id = p.party_id;
833
834 /*
835 CURSOR C_getDtlsFromPhoneNum(x_phonenumber varchar2 ) IS
836 SELECT party_id, party_type
837 FROM JTF_CONTACT_POINTS_V
838 WHERE translate(phone_number,'0123456789()/\-. ','0123456789') = x_phonenumber and status = 'A' ;
839
840 CURSOR C_getDtlsFromAreaPhoneNum(x_phonenumber varchar2 ) IS
841 SELECT party_id, party_type
842 FROM JTF_CONTACT_POINTS_V
843 WHERE translate(area_code||phone_number,'0123456789()/\-. ','0123456789') = x_phonenumber and status = 'A';
844
845 CURSOR C_GetSubObj(p_rel_partyid number) is
846 SELECT object_id, subject_id from
847 HZ_PARTY_RELATIONSHIPS WHERE PARTY_ID = p_rel_partyid;
848 */
849 CURSOR C_GetSubObj(p_rel_partyid number) is
850 SELECT object_id, subject_id from
851 HZ_RELATIONSHIPS WHERE PARTY_ID = p_rel_partyid and
852 object_type = 'ORGANIZATION' and subject_type = 'PERSON';
853
854 l_rel_partyid number := NULL;
855 l_partyIDNext number;
856 l_partyTypeNext varchar2(150);
857
858 l_filtered_ANI VARCHAR2(60);
859 l_transposed_ANI VARCHAR2(60);
860
861 BEGIN
862 LogMessage('Inside Phone Number Match, l_Ani: ' || l_Ani || ' l_NoAreaCodeMatch = ' || l_NoAreaCodeMatch );
863
864 /* kmahajan - old code - commented out and replaced by the code following this
865
866 if (l_NoAreaCodeMatch = 'Y') then
867 BEGIN
868 OPEN C_getDtlsFromPhoneNum (l_ani);
869 FETCH C_getDtlsFromPhoneNum INTO l_partyId, l_partyType;
870 if C_getDtlsFromPhoneNum%FOUND then
871 LogMessage('First ANI Matched. Party ID ' || l_PartyID || ' l_PartyType= ' || l_PartyType );
872 FETCH C_getDtlsFromPhoneNum into l_partyIDNext, l_partyTypeNext;
873 IF C_GetDtlsFromPhoneNum%FOUND then
874 l_MoreAniMatch := 'Y';
875 LogMessage('More ANI Matched. Party ID ' || l_PartyID || ' l_PartyType= ' || l_PartyType );
876 End If;
877 end if;
878 CLOSE C_getDtlsFromPhoneNum;
879 EXCEPTION
880 When Others then
881 LogMessage('Cursor GetDtlsFromPhoneNum Exception Occurred');
882 Close C_GetDtlsFromPhoneNum;
883 END;
884
885 else
886 BEGIN
887 Open C_getDtlsFromAreaPhoneNum(l_ani);
888 fetch C_getDtlsFromAreaPhoneNum INTO l_partyId, l_partyType;
889 IF C_getDtlsFromAreaPhoneNum%FOUND then
890 LogMessage('First ANI Matched. Party ID ' || l_PartyID || ' l_PartyType= ' || l_PartyType );
891 FETCH C_getDtlsFromAreaPhoneNum into l_partyIDNext, l_partyTypeNext;
892 If C_GetDtlsFromAreaPhoneNum%FOUND then
893 l_MoreAniMatch := 'Y';
894 LogMessage('More ANI Matched. Party ID ' || l_PartyID || ' l_PartyType= ' || l_PartyType );
895 end if;
896 end if;
897 CLOSE C_getDtlsFromAreaPhoneNum;
898 EXCEPTION
899 when others then
900 LogMessage('Cursor GetDtlsFromAreaPhoneNum Exception occurred ');
901 CLOSE C_getDtlsFromAreaPhoneNum;
902 END;
903
904 end if;
905
906 --added this if condition for bug number 2096768 by vimpi
907 if ( (l_MoreAniMatch = 'Y') and ( l_partyIdNext = 'PARTY_RELATIONSHIP') and
908 ( l_PartyId = 'ORGANIZATION' or l_PartyID = 'PERSON')) then
909 l_PartyIdNext := l_partyId ;
910 l_partyTypeNext := l_partyType ;
911 end if ;
912
913 if l_PartyType = 'PARTY_RELATIONSHIP' Then
914 BEGIN
915
916 l_Usage := 'QUERY_CON';
917 open c_GetSubObj(l_partyid);
918 fetch c_GetSubObj into l_partyID, l_ContactID;
919 if (l_MoreAniMatch <> 'Y') then
920 l_PartyType := 'ORGANIZATION';
921 end if;
922 close C_GetSubObj;
923 EXCEPTION
924 When Others then
925 LogMessage(' Cursor GetSubObj Exception occurred ');
926 close C_GetSubObj;
927 END;
928 elsif l_partyType = 'PERSON' then
929
930 l_Usage := 'QUERY_CONSUMER';
931
932 elsif l_partyType = 'ORGANIZATION' then
933
934 l_Usage := 'QUERY_ORG';
935
936 end if;
937 */
938 l_filtered_ANI := translate(l_ani,
939 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .+''~`\/@#$^&*_,|}{[]?<>=";:%',
940 '0123456789');
941
942 l_transposed_ANI := null;
943 for c in reverse 1..length(l_filtered_ANI) loop
944 l_transposed_ANI := l_transposed_ANI || substr(l_filtered_ANI, c, 1);
945 end loop;
946 l_transposed_ANI := RTRIM(l_transposed_ANI, 0) || '%'; --Added for bug#4043234
947
948 OPEN C_getDtlsFromPhone(l_transposed_ANI);
949 FETCH C_getDtlsFromPhone INTO l_partyId, l_partyType;
950 if C_getDtlsFromPhone%FOUND then
951 LogMessage('First ANI Matched. Party ID ' || l_PartyID || ' l_PartyType= ' || l_PartyType );
952 FETCH C_getDtlsFromPhone into l_partyIDNext, l_partytypeNext;
953 IF C_GetDtlsFromPhone%FOUND then
954 l_MoreAniMatch := 'Y';
955 LogMessage('More ANI Matched. Party ID ' || l_PartyIDnext || ' l_PartyType= ' || l_PartyTypenext );
956 End If;
957 end if;
958 CLOSE C_getDtlsFromPhone;
959
960 if l_MoreAniMatch = 'Y' then
961 l_usage := 'QUERY_ANI';
962 elsif l_partytype = 'ORGANIZATION' then
963 l_usage := 'QUERY_ORG';
964 elsif l_partytype = 'PERSON' then
965 l_usage := 'QUERY_CONSUMER';
966 elsif l_partytype = 'PARTY_RELATIONSHIP' then
967 l_usage := 'QUERY_RELATIONSHIP';
968 /*
969 -- kmahajan - code below for backward compatibility for IEX
970 -- remove when IEX also uses multi-match UI
971 open c_GetSubObj(l_partyid);
972 fetch c_GetSubObj into l_partyID, l_ContactID;
973 if (l_MoreAniMatch <> 'Y') then
974 l_PartyType := 'ORGANIZATION';
975 end if;
976 close C_GetSubObj;
977 */
978 end if;
979
980 -- kmahajan - if-then-else below added for bug 2454762
981 if l_MoreAniMatch = 'Y' then
982 l_partyID := 0;
983 end if;
984
985 LogMessage('GetDtlsFromPhoneNum done');
986 EXCEPTION
987 WHEN OTHERS THEN
988 LogMessage('GetDtlsFromPhoneNum Exception Occurred');
989 return;
990
991 END GetDtlsFromPhoneNum;
992
993
994 PROCEDURE handleOTSInbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
995 p_action_type OUT NOCOPY NUMBER,
996 p_action_name OUT NOCOPY varchar2,
997 p_action_param OUT NOCOPY varchar2) is
998
999 foo_action_param varchar2(3000);
1000 BEGIN
1001
1002 p_action_type :=1;
1003 p_action_name := G_CurrentForm;
1004 p_action_param := '';
1005
1006 setDefaults;
1007 getCallData(p_mediaTable);
1008
1009 if l_blocked then
1010 p_action_type := 2;
1011 p_action_name := 'AST_MEDIA';
1012 p_action_param := 'BLOCKED="TRUE" ';
1013 end if;
1014
1015 Logmessage('Inside Foo function ');
1016
1017 if (l_xfr_partyID <> 0) then
1018 logmessage('Using transferred information');
1019 p_action_param := p_action_param || constructparam;
1020 return;
1021 end if;
1022
1023 if ((l_profile = 'Y') or (l_campaignCode is not null)) and (l_xfr_sourcecodeID = 0) then
1024 -- l_xfr_sourcecodeid = 0 indicates no source code has been passed
1025 -- via a warm transfer; if it has, it takes precedence
1026 getCampaignCode;
1027 end if;
1028
1029 if ( l_AccountCode is not null ) then
1030
1031 getDtlsFromAccountNum;
1032 IF ( l_partyId <> 0 ) THEN
1033 /* kmahajan - change usage when IEX uptakes the multi-match UI
1034 l_usage := 'QUERY_ACCOUNT';
1035 */
1036 if (l_PartyType = 'ORGANIZATION') then
1037 l_usage := 'QUERY_ORG';
1038 else
1039 l_usage := 'QUERY_CONSUMER';
1040 end if;
1041 p_action_param := p_action_param || constructparam;
1042 RETURN;
1043 END IF;
1044 if (l_AccountRolesExist = 'Y') then
1045 -- kmahajan - launch multi-match UI only for eBC - IEX needs to uptake mult-match UI
1046 l_usage := 'QUERY_ACCOUNT_ROLE';
1047 p_action_param := p_action_param || constructparam;
1048 if l_usage = 'QUERY_ACCOUNT_ROLE' then
1049 -- kmahajan - commented out line below as IEX also uses it
1050 -- and p_action_name = 'AST_RC_ALL' then
1051 p_action_name := 'AST_MEDIA';
1052 p_action_type := 2;
1053 end if;
1054 RETURN;
1055 end if;
1056 end if;
1057
1058 --added by vimpi on 11thfeb2002
1059 if (l_InvoiceNum is not NULL ) then
1060 GetDtlsFromInvoiceNum ;
1061 l_trx_view_by := 'Delinquency' ;
1062 l_usage := 'QUERY_TRANSACTION';
1063 p_action_param := p_action_param || constructparam;
1064 return;
1065 end if ;
1066
1067 if ( l_eventConfCode is not null ) THEN
1068 getDtlsFromEvent;
1069 IF ( l_eventid <> 0 ) THEN
1070 -- we found the match, hence we can query the event details
1071 l_usage := 'QUERY_EVENT';
1072 -- open the form using app_navigate.execute
1073 p_action_param := p_action_param || constructParam;
1074 return;
1075 END IF;
1076 END IF;
1077
1078 if ( l_collateralReqNum is not null ) then
1079 getDtlsFromColReq;
1080 IF (l_collateralid <> 0 ) then
1081 l_usage := 'QUERY_QUOTE';
1082 p_action_param := p_action_param || constructParam;
1083 return;
1084 END IF;
1085 END IF;
1086
1087 if ( l_contactNumber is not null ) then
1088 getDtlsFromConNum;
1089 --IF ( l_contactid <> 0 OR l_partyId <> 0 ) THEN
1090 IF ( l_partyId <> 0 ) THEN
1091 l_usage := 'QUERY_RELATIONSHIP';
1092 p_action_param := p_action_param || constructparam;
1093 RETURN;
1094 END IF;
1095 end if;
1096
1097 if ( l_customerNumber is not null ) then
1098 getDtlsFromCustNum;
1099 IF ( l_partyId <> 0 ) THEN
1100 if (l_PartyType = 'ORGANIZATION') then
1101 l_usage := 'QUERY_ORG';
1102 elsif (l_PartyType = 'PERSON') then
1103 l_usage := 'QUERY_CONSUMER';
1104 else
1105 l_usage := 'QUERY_RELATIONSHIP';
1106 end if;
1107 p_action_param := p_action_param || constructparam;
1108 RETURN;
1109 END IF;
1110 END IF ;
1111
1112 if ( l_quoteNum <> 0 ) then
1113 getDtlsFromQuoteNum;
1114 IF (l_quoteID <> 0 ) then
1115 l_usage := 'QUERY_QUOTE';
1116 p_action_param := p_action_param || constructParam;
1117 return;
1118 END IF;
1119 END IF;
1120
1121 if ( l_orderNum <> 0 ) then
1122 getDtlsFromOrderNum;
1123 IF (l_orderID <> 0 ) then
1124 l_usage := 'QUERY_ORDER';
1125 p_action_param := p_action_param || constructParam;
1126 return;
1127 END IF;
1128 END IF;
1129
1130 if ( l_marketingPin is not null ) then
1131 getDtlsFromMPin;
1132 IF (l_partyID <> 0 ) then
1133 l_usage := 'QUERY_MPIN';
1134 p_action_param := p_action_param || constructParam;
1135 return;
1136 END IF;
1137 END IF;
1138
1139 if ( l_contractNum is not null ) then
1140 getDtlsFromContractNum;
1141 IF (l_partyID <> 0 ) then
1142 l_usage := 'QUERY_CONTRACT';
1143 p_action_param := p_action_param || constructParam;
1144 return;
1145 END IF;
1146 END IF;
1147
1148 if ( l_serviceKey is not null ) then
1149 getDtlsFromServiceKey;
1150 IF (l_partyID <> 0 ) then
1151 l_usage := 'QUERY_SERVICE_KEY';
1152 p_action_param := p_action_param || constructParam;
1153 return;
1154 END IF;
1155 END IF;
1156
1157 if ( l_SRNum is not null ) then
1158 getDtlsFromSRNum;
1159 IF (l_partyID <> 0 ) then
1160 l_usage := 'QUERY_SERVICE_REQUEST';
1161 p_action_param := p_action_param || constructParam;
1162 return;
1163 END IF;
1164 END IF;
1165
1166 if ( l_customerID <> 0 ) then
1167 getDtlsFromCustID; -- added by rnori 07-APR-03 bug # 2885131
1168 IF (l_partyid <> 0 ) THEN
1169 if (l_PartyType = 'ORGANIZATION') then
1170 l_usage := 'QUERY_ORG';
1171 elsif (l_PartyType = 'PERSON') then
1172 l_usage := 'QUERY_CONSUMER';
1173 else
1174 l_usage := 'QUERY_RELATIONSHIP';
1175 end if;
1176 --l_partyID := l_customerID; -- commented old code
1177 --l_usage := 'QUERY_PARTY';
1178 p_action_param := p_action_param || constructParam;
1179 return;
1180 END IF;
1181 END IF;
1182
1183 IF (( l_partyId = 0) and (l_ani is not null )) THEN
1184 GetDtlsFromPhoneNum;
1185 foo_action_param := constructparam;
1186 p_action_param := p_action_param || foo_action_param;
1187 if l_usage = 'QUERY_ANI' then
1188 -- kmahajan - commented out line below as IEX also uses it now
1189 -- and p_action_name = 'AST_RC_ALL' then
1190 p_action_name := 'AST_MEDIA';
1191 p_action_type := 2;
1192 end if;
1193 Else
1194 foo_action_param := constructparam;
1195 p_action_param := p_action_param || foo_action_param;
1196 END IF;
1197
1198 LogMessage('Exiting OTS Inbound Foo Function');
1199
1200 END handleOTSInbound;
1201
1202 PROCEDURE handleEmail (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1203 p_action_type OUT NOCOPY NUMBER,
1204 p_action_name OUT NOCOPY varchar2,
1205 p_action_param OUT NOCOPY varchar2) is
1206
1207 BEGIN
1208 handleOTSInbound(p_mediaTable, p_action_type, p_action_name, p_action_param);
1209 END;
1210
1211 PROCEDURE handleOTSOutbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1212 p_action_type OUT NOCOPY NUMBER,
1213 p_action_name OUT NOCOPY varchar2,
1214 p_action_param OUT NOCOPY varchar2) IS
1215 BEGIN
1216
1217 p_action_type :=1;
1218 p_action_name := G_Currentform;
1219 p_action_param := '';
1220
1221 LogMessage('Inside OTS outbound Foo function');
1222 setDefaults;
1223 getCallData(p_mediaTable);
1224
1225 if l_blocked then
1226 p_action_type := 2;
1227 p_action_name := 'AST_MEDIA';
1228 p_action_param := 'BLOCKED="TRUE" ';
1229 end if;
1230
1231 l_partyID := l_customerID; -- added by rnori 14-Apr-03 bug # 2897623
1232 p_action_param := p_action_param || constructparam;
1233 LogMessage('Exiting OTS Outbound Foo function');
1234
1235 END handleOTSOutbound ;
1236
1237 PROCEDURE handleOCInbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1238 p_action_type OUT NOCOPY NUMBER,
1239 p_action_name OUT NOCOPY varchar2,
1240 p_action_param OUT NOCOPY varchar2) IS
1241
1242 BEGIN
1243 --added by vimpi on 11th Feb/2002 to incorporate IVR popup by invoice Number
1244 --getCallData(p_mediaTable);
1245 -- kmahajan - above line commented out as OTSinbound calls getcalldata also
1246 --changes ended
1247 handleOTSInbound(p_mediaTable, p_action_type, p_action_name, p_action_param);
1248 -- kmahajan - encapsulated this code in if-then-else to support AST_MEDIA for IEX
1249 if p_action_name <> 'AST_MEDIA' then
1250 p_action_name := 'IEX_RC_CALL';
1251 --kmahajan 10/07/2002 - commented line below
1252 --anyways, it was redundant; now it's incorrect after introducing l_block
1253 --p_action_type := 1;
1254 else
1255 p_action_param := p_action_param || ' LAUNCH_FUNCTION="IEX_RC_CALL" ';
1256 end if;
1257
1258 END;
1259
1260 PROCEDURE handleOCOutbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1261 p_action_type OUT NOCOPY NUMBER,
1262 p_action_name OUT NOCOPY varchar2,
1263 p_action_param OUT NOCOPY varchar2) IS
1264
1265 BEGIN
1266 handleOTSOutbound(p_mediaTable, p_action_type, p_action_name, p_action_param);
1267 -- kmahajan - encapsulated this code in if-then-else to support AST_MEDIA for IEX
1268 if p_action_name <> 'AST_MEDIA' then
1269 p_action_name := 'IEX_RC_CALL';
1270 --kmahajan 10/07/2002 - commented line below
1271 --anyways, it was redundant; now it's incorrect after introducing l_block
1272 --p_action_type := 1;
1273 else
1274 p_action_param := p_action_param || ' LAUNCH_FUNCTION="IEX_RC_CALL" ';
1275 end if;
1276
1277 END;
1278
1279
1280 Procedure setCurrentForm(p_formName varchar2) IS
1281 BEGIN
1282
1283 G_CurrentForm := upper(p_formName);
1284 END setCurrentForm ;
1285
1286 /*** added by vimpi on 18th october 20001*/
1287 --modified getFooData and handleFooTask to temp. handle Marketing list node
1288 --call to ebc with additional parameters. jraj 5th Nov. 2002.
1289 PROCEDURE getFooData(p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST) IS
1290 BEGIN
1291
1292 FOR i IN 1 .. p_mediaTable.COUNT LOOP
1293
1294
1295 if ( upper(p_mediaTable(i).param_name) = 'TASK_ID' ) then
1296 l_task_id := p_mediaTable(i).param_value;
1297
1298 elsif ( upper(p_mediaTable(i).param_name) = 'PARTY_ID' ) then
1299 l_nm_party_id := p_mediaTable(i).param_value;
1300
1301 elsif ( upper(p_mediaTable(i).param_name) = 'SOURCE_OBJECT_TYPE' ) then
1302 l_source_object_type := p_mediaTable(i).param_value;
1303
1304 elsif ( upper(p_mediaTable(i).param_name) = 'SOURCE_CODE_ID' ) then
1305 l_source_Code_id := p_mediaTable(i).param_value;
1306
1307 elsif ( upper(p_mediaTable(i).param_name) = 'SOURCE_CODE' ) then
1308 l_source_Code := p_mediaTable(i).param_value;
1309
1310 elsif ( upper(p_mediaTable(i).param_name) = 'SCHEDULE_ID' ) then
1311 l_source_campaign_id := p_mediaTable(i).param_value;
1312
1313 END IF;
1314
1315 END LOOP;
1316
1317 END getFooData ;
1318
1319 --modified getFooData and handleFooTask to temp. handle Marketing list node
1320 --call to ebc with additional parameters. jraj 5th Nov. 2002.
1321 PROCEDURE handleFooTask (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1322 p_action_type OUT NOCOPY NUMBER,
1323 p_action_name OUT NOCOPY varchar2,
1324 p_action_param OUT NOCOPY varchar2,
1325 p_msg_name OUT NOCOPY varchar2,
1326 p_msg_param OUT NOCOPY varchar2,
1327 p_dialog_style OUT NOCOPY NUMBER,
1328 p_msg_appl_short_name OUT NOCOPY varchar2) is
1329 cursor c_source_code_id(p_schedule_id number, p_source_code varchar2) is
1330 select source_code_id from ams_source_codes where
1331 --source_code_for_id = p_schedule_id and /* Commented for bug#4453994 */
1332 --arc_source_code_for = 'CSCH' and /* Commented for bug#4453994 */
1333 source_code = p_source_code;
1334
1335 cursor c2_source_code_id(p_source_code varchar2) is
1336 select source_code_id from ams_source_codes where
1337 source_code = p_source_code;
1338 BEGIN
1339
1340 logmessage('Responsibility Application id ' || fnd_profile.value('RESP_APPL_ID')) ;
1341
1342 l_task_id := '';
1343 l_source_code := '';
1344 l_source_code_id := '';
1345 l_source_campaign_id := 0;
1346 l_nm_party_id := 0;
1347
1348 getFooData(p_mediaTable);
1349
1350 If l_nm_party_id <> 0 then
1351 p_action_name := 'AST_RC_ALL';
1352
1353 if l_source_code is not null then
1354 if l_source_code_id is null and l_source_campaign_id is not null then
1355 open c_source_code_id(l_source_campaign_id, l_source_code);
1356 fetch c_source_code_id into l_source_code_id;
1357 close c_source_code_id;
1358 elsif l_source_code_id is null and l_source_campaign_id is null then
1359 open c2_source_code_id(l_source_code);
1360 fetch c2_source_code_id into l_source_code_id;
1361 close c2_source_code_id;
1362 end if;
1363 end if;
1364
1365 p_action_param := 'PARTY_ID=' || l_nm_PARTY_ID ||' SOURCE_CAMPAIGN_ID=' || l_source_code_id;
1366 p_action_type := 2;
1367
1368 elsif (l_source_object_type = 'Party') then
1369 if(fnd_profile.value('RESP_APPL_ID') = '695') then
1370 p_action_name := 'IEXRCALL' ;
1371 else
1372 p_action_name := 'AST_RC_ALL';
1373 end if ;
1374 p_action_param := 'USAGE=QUERY_TASK TASK_ID='|| l_Task_ID ;
1375 p_action_type := 1;
1376 else
1377 p_action_name := 'JTFTKMAN' ;
1378 p_action_param := 'TASK_ID=' || l_Task_ID;
1379 p_action_type := 2;
1380 end if ;
1381 p_msg_name := 'NULL' ;
1382 p_msg_param := 'NULL' ;
1383 p_dialog_style := 1; /* IEU_DS_CONSTS_PUB.G_DS_NONE ; */
1384 --p_msg_appl_short_name := 'AST' ;
1385 p_msg_appl_short_name := 'NULL' ;
1386 END handleFooTask ;
1387
1388
1389 BEGIN
1390 l_Profile := NVL(FND_PROFILE.VALUE('AST_MATCH_CAMP_DNIS'), 'N');
1391 l_DumpData := NVL(FND_PROFILE.VALUE('AST_DUMP_PARAMS'), 'N');
1392 l_NoAreaCodeMatch := NVL(fnd_profile.value('AST_ANI_WITHOUT_AREACODE'), 'N');
1393
1394 END AST_UWQ_SEL_PKG;