[Home] [Help]
PACKAGE BODY: APPS.OKL_UWQ_SEL_PKG
Source
1 Package BODY Okl_Uwq_Sel_Pkg AS
2 /* $Header: OKLRUWQB.pls 120.5 2006/08/11 10:47:52 gboomina noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 ------------------------------------------------------------------------------
9 -- Procedure : setDefaults
10 -- Usage :
11 -- Description : This procedure sets the default every time
12 --- this package gets executed
13 l_eventName VARCHAR2(60);
14 l_partyId NUMBER;
15 l_partyType VARCHAR2(30);
16 l_customerNumber NUMBER;
17 l_contactId NUMBER;
18 l_contactNumber NUMBER;
19 l_eventConfCode VARCHAR2(30);
20 l_eventId NUMBER;
21 l_collateralReqNum VARCHAR2(30) ;
22 l_collateralId NUMBER;
23 l_campaignCode VARCHAR2(30);
24 l_campaignId NUMBER;
25 l_campaignCodeID NUMBER;
26 l_CampScheduleID NUMBER;
27 l_dnis VARCHAR2(30);
28 l_callId VARCHAR2(80);
29 l_ani VARCHAR2(40);
30 l_accountCode NUMBER;
31 l_usage VARCHAR2(60);
32 l_agentID VARCHAR2(60);
33 l_mediaType VARCHAR2(80);
34 l_mediaItemID VARCHAR2(60);
35 l_workitemID VARCHAR2(60);
36 l_sendername VARCHAR2(60);
37 l_subject VARCHAR2(60);
38 l_messageID VARCHAR2(60);
39 l_MoreAniMatch VARCHAR2(10);
40
41 PROCEDURE setDefaults IS
42 BEGIN
43 l_eventName := '';
44 l_partyId := 0;
45 l_partyType := '';
46 l_customerNumber := 0;
47 l_contactId := 0;
48 l_contactNumber := 0;
49 l_eventConfCode := '';
50 l_eventId := 0;
51 l_collateralReqNum := '';
52 l_collateralId := 0;
53 l_campaignCode := '';
54 l_campaignId := 0;
55 l_campScheduleID := 0;
56 l_campaignCodeID := 0;
57 l_dnis := '';
58 l_callId := '';
59 l_ani := '';
60 l_accountcode := 0;
61 l_usage := '';
62 l_agentID := '';
63 l_mediaType := '';
64 l_mediaItemID := '';
65 l_workitemID := '';
66 l_sendername := '';
67 l_subject := '';
68 l_messageID := '';
69 l_MoreAniMatch := 'N';
70 END setDefaults;
71 -- private package to return the param type value
72 PROCEDURE getCallData(p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST) IS
73 BEGIN
74 FOR i IN 1 .. p_mediaTable.COUNT LOOP
75 IF ( UPPER(p_mediaTable(i).param_name) = 'OCCTEVENTNAME' ) THEN
76 l_eventname := p_mediaTable(i).param_value;
77 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'IEU_AMS_CAMPAIGN_ID' ) THEN
78 l_CampaignID := p_mediaTable(i).param_value;
79 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'CAMPAIGN_SCHEDULE_ID' ) THEN
80 l_CampScheduleID := p_mediaTable(i).param_value;
81 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'CustomerID' ) THEN
82 l_partyID := p_mediaTable(i).param_value;
83 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'CustomerNumber' ) THEN
84 l_customerNumber := p_mediaTable(i).param_value;
85 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'CUSTOMERID' ) THEN
86 l_partyID := p_mediaTable(i).param_value;
87 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'CUSTOMERNUM' ) THEN
88 l_customerNumber := p_mediaTable(i).param_value;
89 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'CONTACTNUM' ) THEN
90 l_contactnumber := p_mediaTable(i).param_value;
91 ELSIF ( UPPER(p_mediatable(i).param_name)='EVENTCONFCODE') THEN
92 l_eventConfCode := p_mediaTable(i).param_value;
93 ELSIF ( UPPER(p_mediatable(i).param_name)='COLREQNUM') THEN
94 l_collateralReqNum := p_mediaTable(i).param_value;
95 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'PROMOTIONCODE' ) THEN
96 l_campaignCode := p_mediaTable(i).param_value;
97 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OCCTDNIS' ) THEN
98 l_dnis := p_mediaTable(i).param_value;
99 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OCCTCALLID' ) THEN
100 l_callId := p_mediaTable(i).param_value;
101 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OCCTANI' ) THEN
102 l_ani := p_mediaTable(i).param_value;
103 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'ACCOUNTCODE' ) THEN
104 l_accountCode := p_mediaTable(i).param_value;
105 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OCCTAGENTID' ) THEN
106 l_agentID := p_mediaTable(i).param_value;
107 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OCCTMEDIATYPE' ) THEN
108 l_mediaType := p_mediaTable(i).param_value;
109 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OIEMMESSAGEID' ) THEN
110 l_messageID := p_mediaTable(i).param_value;
111 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OIEMSUBJECT' ) THEN
112 l_subject := p_mediaTable(i).param_value;
113 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OIEMSENDERNAME' ) THEN
114 l_sendername := p_mediaTable(i).param_value;
115 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'WORKITEMID' ) THEN
116 l_workitemID := p_mediaTable(i).param_value;
117 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'OCCTMEDIAITEMID' ) THEN
118 l_mediaItemID := p_mediaTable(i).param_value;
119 ELSIF ( UPPER(p_mediaTable(i).param_name) = 'PARTY_ID' ) THEN
120 l_partyId := TO_NUMBER(p_mediaTable(i).param_value);
121 END IF;
122 END LOOP;
123 END getCallData ;
124 -- procedure to construct the paramlist to be passed to the form
125 FUNCTION constructparam RETURN VARCHAR2 IS
126 l_paramlist VARCHAR2(3000);
127 BEGIN
128 l_paramlist := '';
129 IF ( l_partyId <> 0 ) THEN
130 l_paramlist := l_paramlist || 'PARTY_ID' || '="' || l_partyId ||'" ';
131 END IF;
132 IF ( l_partyType IS NOT NULL ) THEN
133 l_paramlist := l_paramlist || 'PARTY_TYPE' || '="' || l_partyType || '" ';
134 END IF;
135 IF ( l_contactid <> 0 ) THEN
136 l_paramlist := l_paramlist || 'PARTY_CONTACT_ID' || '="' || l_contactId ||'" ';
137 END IF;
138 IF ( l_eventid <> 0 ) THEN
139 l_paramlist := l_paramlist || 'EVENT_REG_ID' || '="' || l_eventId ||'" ';
140 END IF;
141 IF ( l_collateralId <> 0 ) THEN
142 l_paramlist := l_paramlist || 'COLL_REQ_ID' || '="' || l_collateralId ||'" ';
143 END IF;
144 IF (l_campaignId <> 0 ) THEN
145 l_paramlist := l_paramlist || 'CAMPAIGN_ID' || '="' || l_campaignId ||'" ';
146 END IF;
147 IF (l_CampaignCodeID <> 0) THEN
148 l_paramlist := l_paramlist || 'SOURCE_CAMPAIGN_ID' || '="' || l_campaignCodeId ||'"
149
150 ';
151 END IF;
152 IF (l_CampaignCode <> 0) THEN
153 l_paramlist := l_paramlist || 'CAMPAIGN_SOURCE_CODE' || '="' || l_campaignCode ||'"
154
155 ';
156 END IF;
157 IF (l_CampScheduleID <> 0) THEN
158 l_paramlist := l_paramlist || 'CAMPAIGN_SCHEDULE_ID' || '="' || l_campScheduleId ||'"
159
160 ';
161 END IF;
162 IF ( l_callID IS NOT NULL ) THEN
163 l_paramlist := l_paramlist || 'TM_CALL_ID' || '="' || l_callID ||'" ';
164 END IF;
165 IF ( l_accountcode <> 0 ) THEN
166 l_paramlist := l_paramlist || 'CUST_ACCOUNT_ID' || '="' || l_accountCode ||'" ';
167 END IF;
168 IF ( l_eventname IS NOT NULL ) THEN
169 l_paramlist := l_paramlist || 'UWQ_EVENTNAME' || '="' || l_eventname ||'" ';
170 END IF;
171 IF ( l_dnis IS NOT NULL ) THEN
172 l_paramlist := l_paramlist || 'TM_DNIS' || '="' || l_dnis ||'" ';
173 END IF;
174 IF ( l_ani IS NOT NULL) THEN
175 l_paramlist := l_paramlist || 'TM_ANI' || '="' || l_ani ||'" ';
176 END IF;
177 IF ( l_mediaType IS NOT NULL ) THEN
178 l_paramlist := l_paramlist || 'UWQ_MEDIATYPE' || '="' || l_mediatype ||'" ';
179 END IF;
180 IF ( l_mediaItemID IS NOT NULL) THEN
181 l_paramlist := l_paramlist || 'UWQ_MEDIAITEM_ID' || '="' || l_mediaItemID || '" ';
182 END IF;
183 IF ( l_workitemID IS NOT NULL ) THEN
184 l_paramlist := l_paramlist || 'UWQ_WORKITEM_ID' || '="' || l_workitemID ||'" ';
185 END IF;
186 IF ( l_sendername IS NOT NULL ) THEN
187 l_paramlist := l_paramlist || 'EM_SENDERNAME' || '="' || l_sendername ||'" ';
188 END IF;
189 IF ( l_subject IS NOT NULL ) THEN
190 l_paramlist := l_paramlist || 'EM_SUBJECT' || '="' || l_subject ||'" ';
191 END IF;
192 IF ( l_messageID IS NOT NULL) THEN
193 l_paramlist := l_paramlist || 'EM_MESSAGE_ID' || '="' || l_messageID ||'" ';
194 END IF;
195 IF (l_MoreAniMatch = 'Y') THEN
196 l_Usage := 'QUERY_ANI';
197 END IF;
198 IF ((l_usage IS NULL) AND (l_partyId = 0)) THEN
199 l_usage := 'QUERY_LKP';
200 END IF;
201 -- append the usage parameter
202 l_paramlist := l_paramlist || ' CALLED_FROM = "UWQ" ';
203 IF (l_usage IS NOT NULL) THEN
204 l_paramlist := l_paramlist || ' USAGE' || '="' || l_usage ||'" ';
205 END IF;
206 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
207 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Param List Before Return ' || l_paramList);
208 END IF;
209 RETURN l_paramlist;
210 EXCEPTION
211 WHEN OTHERS THEN
212 l_paramlist := ' CALLED_FROM = "UWQ" ';
213 RETURN l_paramlist;
214 END constructparam;
215 -- procedure get details from event confirmation number
216 PROCEDURE getDtlsFromEvent IS
217 CURSOR C_GetEventDetails(x_eventConfCode VARCHAR2) IS
218 SELECT registrant_party_Id,registrant_contact_Id,event_offer_Id
219 FROM ams_event_registrations_v
220 WHERE confirmation_code = x_eventConfCode;
221 BEGIN
222 OPEN C_GetEventDetails(l_eventConfCode);
223 FETCH C_GetEventDetails INTO l_partyId, l_contactId, l_eventId;
224 IF ( c_geteventdetails%NOTFOUND) THEN
225 l_partyId:=0;
226 l_contactid := 0;
227 l_eventid := 0;
228 END IF;
229 CLOSE C_GetEventDetails;
230 END getDtlsFromEvent;
231 PROCEDURE Getcampaigncode IS
232 /*
233 SELECT campaign_id
234 FROM ams_campaigns_all_b
235 WHERE translate(inbound_phone_no,'0123456789()/\-. ','0123456789')=x_inbound_phone
236 AND status_code ='ACTIVE';
237 CURSOR c_campaignId(x_inbound_phone VARCHAR2) IS
238 SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.SOURCE_CODE_FOR_ID
239 FROM aMS_SOURCE_CODES SOC, AMS_ACT_CONTACT_POINTS AACP
240 WHERE SOC.SOURCE_CODE_FOR_ID = AACP.ACT_CONTACT_USED_BY_ID AND
241 SOC.ARC_SOURCE_CODE_FOR = AACP.ARC_CONTACT_USED_BY AND
242 SOC.ACTIVE_FLAG = 'Y' AND AACP.CONTACT_POINT_TYPE = 'PHONE'
243 AND AACP.CONTACT_POINT_VALUE = x_inbound_phone;
244 CURSOR c_campaigncode(x_campaigncode VARCHAR2) IS
245 SELECT source_code_id, source_code_for_id
246 FROM ams_source_codes
247 WHERE source_code = x_campaignCode
248 AND active_flag = 'Y';
249 lx_CampaignCode varchar2(100);
250 lx_CampaignCodeId number;
251 lx_CampaignId number;
252 */
253 BEGIN
254 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
255 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside Campaign Code Match');
256 END IF;
257 /*
258 IF ( l_campaigncode IS NOT NULL ) THEN
259 OPEN c_campaigncode(l_CampaignCode);
260 FETCH c_campaigncode INTO l_campaignCodeId, l_campaignid;
261 CLOSE c_campaigncode;
262 ELSIF ( l_dnis IS NOT NULL ) THEN
263 LogMessage('Inside DNIS Code Match ' || l_dnis);
264 OPEN c_campaignid(l_dnis);
265 FETCH c_campaignid INTO l_campaignCodeId, l_CampaignCode, l_CampaignID;
266 IF ( c_campaignid%found) THEN
267 LogMessage('Inside DNIS Code Match Success : Source Code ' || l_CampaignCode
268 || ' Source Code Id ' || l_CampaignCodeID);
269 FETCH c_campaignid INTO lx_campaignCodeId, lx_CampaignCode, lx_CampaignID;
270 IF (c_CampaignId%FOUND) then
271 LogMessage('MoreCampaign Code Match. Will be using default is set ');
272 lx_campaignCode := FND_PROFILE.VALUE('AST_DEFAULT_SOURCE_CODE');
273 if lx_CampaignCode is not NULL then
274 OPEN c_campaigncode(lx_CampaignCode);
275 FETCH c_campaigncode INTO l_campaignCodeId, l_campaignid;
276 IF c_CampaignCode%FOUND THEN
277 l_CampaignCode := lx_CampaignCode;
278 END IF;
279 CLOSE c_campaigncode;
280 END IF;
281 END IF;
282 END IF;
283 CLOSE c_campaignid;
284 END IF;
285 */
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
289 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'GetCampaignCode Exception Occurred');
290 END IF;
291 RETURN;
292 END;
293 -- procedure to get the collateral request Id from a collateral request confirmation
294 -- number
295 PROCEDURE getDtlsFromColReq IS
296 CURSOR C_getDtlsFromColReq(x_collateralReqNum NUMBER ) IS
297 SELECT quote_header_id,party_id,cust_account_id,l_contactid
298 FROM ASO_QUOTE_HEADERS_ALL
299 WHERE quote_number = x_collateralReqNum;
300 BEGIN
301 OPEN C_getDtlsFromColReq(TO_NUMBER(l_collateralreqnum));
302 FETCH C_getDtlsFromColReq INTO l_collateralId,l_partyId, l_accountCode,l_contactId;
303 IF (c_getdtlsfromcolreq%NOTFOUND )THEN
304 l_collateralid := 0;
305 END IF;
306 CLOSE C_getDtlsFromColReq;
307 END getDtlsFromColReq;
308 -- private procedure to get the partyId
309 PROCEDURE GetDtlsFromConNum IS
310 CURSOR C_getDtlsFromConNum(x_contactNum VARCHAR2 ) IS
311 SELECT p.party_id, rel.object_id
312 FROM hz_parties p, hz_relationships_v rel
313 WHERE p.party_number = x_contactNum
314 AND p.party_id = rel.party_id;
315 BEGIN
316 OPEN C_getDtlsFromConNum (l_contactNumber);
317 FETCH C_getDtlsFromConNum INTO l_contactId, l_partyId ;
318 CLOSE C_getDtlsFromConNum;
319 END GetDtlsFromConNum;
320 PROCEDURE GetDtlsFromCustNum IS
321 CURSOR C_GetCustId(x_custnum VARCHAR2) IS
322 SELECT party_id
323 FROM hz_parties
324 WHERE party_number = x_custnum;
325 BEGIN
326 OPEN c_getcustid(l_customernumber);
327 FETCH c_getcustid INTO l_partyId;
328 CLOSE c_getcustid;
329 END GetDtlsFromCustNum;
330 PROCEDURE GetDtlsFromAccountNum IS
331 CURSOR C_GetCustId(x_account_num VARCHAR2) IS
332 SELECT party_id
333 FROM hz_cust_accounts
334 WHERE account_number = x_account_num;
335 BEGIN
336 OPEN c_getcustid(l_accountCode);
337 FETCH c_getcustid INTO l_partyId;
338 CLOSE c_getcustid;
339 END GetDtlsFromAccountNum;
340 PROCEDURE GetDtlsFromPhoneNum IS
341 /*
342 CURSOR C_getDtlsFromPhoneNum(x_phonenumber VARCHAR2 ) IS
343 SELECT party_id, party_type
344 FROM JTF_CONTACT_POINTS_V
345 WHERE phone_number = x_phonenumber AND status = 'A' ;
346 CURSOR C_getDtlsFromAreaPhoneNum(x_phonenumber VARCHAR2 ) IS
347 SELECT party_id, party_type
348 FROM JTF_CONTACT_POINTS_V
349 WHERE (AREA_CODE||PHONE_NUMBER) = x_phonenumber AND status = 'A';
350 CURSOR C_GetSubObj(p_rel_partyid NUMBER) IS
351 SELECT object_id, subject_id FROM
352 HZ_PARTY_RELATIONSHIPS WHERE PARTY_ID = p_rel_partyid;
353 l_rel_partyid NUMBER := NULL;
354 l_partyIDNext NUMBER;
355 l_partyTypeNext VARCHAR2(150);
356 */
357 BEGIN
358 IF (G_DEBUG_ENABLED = 'Y') THEN
359 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
360 END IF;
361 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
362 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside Phone Number Match, l_Ani: ' || l_Ani || ' l_NoAreaCodeMatch = '
363
364 || l_NoAreaCodeMatch );
365
366 END IF;
367 /*
368 IF (l_NoAreaCodeMatch = 'Y') THEN
369 BEGIN
370 OPEN C_getDtlsFromPhoneNum (l_ani);
371 FETCH C_getDtlsFromPhoneNum INTO l_partyId, l_partyType;
372 IF C_getDtlsFromPhoneNum%FOUND THEN
373 LogMessage('First ANI Matched. Party ID ' || l_PartyID || '
374
375 l_PartyType= ' || l_PartyType );
376 FETCH C_getDtlsFromPhoneNum INTO l_partyIDNext, l_partyTypeNext;
377 IF C_GetDtlsFromPhoneNum%FOUND THEN
378 l_MoreAniMatch := 'Y';
379 LogMessage('More ANI Matched. Party ID ' || l_PartyID || '
380
381 l_PartyType= ' || l_PartyType );
382 END IF;
383 END IF;
384 CLOSE C_getDtlsFromPhoneNum;
385 EXCEPTION
386 WHEN OTHERS THEN
387 LogMessage('Cursor GetDtlsFromPhoneNum Exception Occurred');
388 CLOSE C_GetDtlsFromPhoneNum;
389 END;
390 ELSE
391 BEGIN
392 OPEN C_getDtlsFromAreaPhoneNum(l_ani);
393 FETCH C_getDtlsFromAreaPhoneNum INTO l_partyId, l_partyType;
394 IF C_getDtlsFromAreaPhoneNum%FOUND THEN
395 LogMessage('First ANI Matched. Party ID ' || l_PartyID || ' l_PartyType= '
396
397 || l_PartyType );
398 FETCH C_getDtlsFromAreaPhoneNum INTO l_partyIDNext, l_partyTypeNext;
399 IF C_GetDtlsFromAreaPhoneNum%FOUND THEN
400 l_MoreAniMatch := 'Y';
401 LogMessage('More ANI Matched. Party ID ' || l_PartyID || ' l_PartyType=
402
403 ' || l_PartyType );
404 END IF;
405 END IF;
406 CLOSE C_getDtlsFromAreaPhoneNum;
407 EXCEPTION
408 WHEN OTHERS THEN
409 LogMessage('Cursor GetDtlsFromAreaPhoneNum Exception occurred ');
410 CLOSE C_getDtlsFromAreaPhoneNum;
411 END;
412 END IF;
413 IF l_PartyType = 'PARTY_RELATIONSHIP' THEN
414 BEGIN
415 l_Usage := 'QUERY_CON';
416 OPEN c_GetSubObj(l_partyid);
417 FETCH c_GetSubObj INTO l_partyID, l_ContactID;
418 IF (l_MoreAniMatch <> 'Y') THEN
419 l_PartyType := 'ORGANIZATION';
420 END IF;
421 CLOSE C_GetSubObj;
422 EXCEPTION
423 WHEN OTHERS THEN
424 LogMessage(' Cursor GetSubObj Exception occurred ');
425 CLOSE C_GetSubObj;
426 END;
427 ELSIF l_partyType = 'PERSON' THEN
428 l_Usage := 'QUERY_CONSUMER';
429 ELSIF l_partyType = 'ORGANIZATION' THEN
430 l_Usage := 'QUERY_ORG';
431 END IF;
432 LogMessage('GetDtlsFromPhoneNum done');
433 */
434 EXCEPTION
435 WHEN OTHERS THEN
436 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
437 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'GetDtlsFromPhoneNum Exception Occurred');
438 END IF;
439 RETURN;
440 END GetDtlsFromPhoneNum;
441 PROCEDURE handleOTSInbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
442 p_action_type OUT NOCOPY NUMBER,
443 p_action_name OUT NOCOPY VARCHAR2,
444 p_action_param OUT NOCOPY VARCHAR2) IS
445 foo_action_param VARCHAR2(3000);
446 BEGIN
447 IF (G_DEBUG_ENABLED = 'Y') THEN
448 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
449 END IF;
450 -- setCurrentForm('ASTTMPOP');
451 p_action_type :=1;
452 p_action_name := G_CurrentForm;
453 p_action_param := '';
454 setDefaults;
455 getCallData(p_mediaTable);
456 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
457 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside Foo function ');
458 END IF;
459 IF (l_CampaignID = 0) AND (l_profile = 'Y') THEN
460 getCampaignCode;
461 END IF;
462 IF ( l_eventConfCode <> '' ) THEN
463 getDtlsFromEvent;
464 IF ( l_eventid <> 0 ) THEN
465 -- we found the match, hence we can query the event details
466 l_usage := 'QUERY_EVENT';
467 -- open the form using app_navigate.execute
468 p_action_param := constructParam;
469 RETURN;
470 END IF;
471 END IF;
472 IF ( l_collateralReqNum <> '' ) THEN
473 getDtlsFromColReq;
474 IF (l_collateralid <> 0 ) THEN
475 l_usage := 'QUERY_COL';
476 p_action_param := constructParam;
477 RETURN;
478 END IF;
479 END IF;
480 IF ( l_AccountCode <> '' ) THEN
481 getDtlsFromAccountNum;
482 IF ( l_partyId <> 0 ) THEN
483 l_usage := 'QUERY_CUST';
484 p_action_param := constructparam;
485 RETURN;
486 END IF;
487 END IF;
488 IF ( l_contactNumber <> '' ) THEN
489 getDtlsFromConNum;
490 IF ( l_contactid <> 0 OR l_partyId <> 0 ) THEN
491 l_usage := 'QUERY_CON';
492 p_action_param := constructparam;
493 RETURN;
494 END IF;
495 END IF;
496 IF ( l_customerNumber <> '' ) THEN
497 getDtlsFromCustNum;
498 IF ( l_partyId <> 0 ) THEN
499 l_usage := 'QUERY_CUST';
500 p_action_param := constructparam;
501 RETURN;
502 END IF;
503 END IF ;
504 IF (( l_partyId = 0) AND (l_ani IS NOT NULL )) THEN
505 GetDtlsFromPhoneNum;
506 foo_action_param := constructparam;
507 p_action_param := foo_action_param;
508 ELSE
509 foo_action_param := constructparam;
510 p_action_param := foo_action_param;
511 END IF;
512 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
513 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Existing OTS Inbound Foo Function');
514 END IF;
515 END handleOTSInbound;
516 PROCEDURE handleEmail (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
517 p_action_type OUT NOCOPY NUMBER,
518 p_action_name OUT NOCOPY VARCHAR2,
519 p_action_param OUT NOCOPY VARCHAR2) IS
520 BEGIN
521 handleOTSInbound(p_mediaTable, p_action_type, p_action_name, p_action_param);
522 END;
523 PROCEDURE handleOTSOutbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
524 p_action_type OUT NOCOPY NUMBER,
525 p_action_name OUT NOCOPY VARCHAR2,
526 p_action_param OUT NOCOPY VARCHAR2) IS
527 BEGIN
528 IF (G_DEBUG_ENABLED = 'Y') THEN
529 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
530 END IF;
531 p_action_type :=1;
532 p_action_name := G_Currentform;
533 p_action_param := '';
534 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
535 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside OTS outbound Foo function');
536 END IF;
537 setDefaults;
538 getCallData(p_mediaTable);
539 p_action_param := constructparam;
540 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
541 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Exiting OTS Outbound Foo function');
542 END IF;
543 END handleOTSOutbound ;
544 PROCEDURE handleOCInbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
545 p_action_type OUT NOCOPY NUMBER,
546 p_action_name OUT NOCOPY VARCHAR2,
547 p_action_param OUT NOCOPY VARCHAR2) IS
548 BEGIN
549 handleOTSInbound(p_mediaTable, p_action_type, p_action_name, p_action_param);
550 p_action_name := 'IEXRCALL';
551 p_action_type := 1;
552 END;
553 PROCEDURE handleOCOutbound (p_mediaTable IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
554 p_action_type OUT NOCOPY NUMBER,
555 p_action_name OUT NOCOPY VARCHAR2,
556 p_action_param OUT NOCOPY VARCHAR2) IS
557 BEGIN
558 handleOTSOutbound(p_mediaTable, p_action_type, p_action_name, p_action_param);
559 p_action_name := 'IEXRCALL';
560 p_action_type := 1;
561 END;
562 PROCEDURE setCurrentForm(p_formName VARCHAR2) IS
563 BEGIN
564 G_CurrentForm := UPPER(p_formName);
565 END setCurrentForm ;
566 BEGIN
567 l_Profile := NVL(Fnd_Profile.VALUE('AST_MATCH_CAMP_DNIS'), 'N');
568 l_DumpData := NVL(Fnd_Profile.VALUE('AST_DUMP_PARAMS'), 'N');
569 l_NoAreaCodeMatch := NVL(Fnd_Profile.value('AST_ANI_WITHOUT_AREACODE'), 'N');
570 END Okl_Uwq_Sel_Pkg;