· 6 years ago · Apr 04, 2019, 02:40 PM
1create or replace package pkLDD_LEADS is
2 -- Author : A. Miglietta - N DePari
3 -- Created : 05/03/2009 10.21.09
4 -- Purpose : Gestione delle Leads To Assign per il CRM-W2
5 -- $Id: pkLDD_Leads.sql,v 1.263 2019/04/02 09:58:13 abis Exp $
6
7 Type objectTypeTable is
8 Table of LDD_OBJTYPES.OBJTYPE%Type index by binary_integer;
9
10 Type leadsTable is
11 Table of LDD_LEAD.CODLEAD%Type index by binary_integer;
12
13 Type CodCampaignSource is
14 Table of LDD_CAMPAIGN.CODCAMPAIGNSOURCE%Type index by binary_integer;
15
16function isLeadInterview ( p_codlead ldd_lead.codlead%type )
17 return number;
18
19function isLeadLF ( p_codlead ldd_lead.codlead%type )
20 return number;
21
22function isLeadSF ( p_codlead ldd_lead.codlead%type )
23 return number;
24
25function isLeadCare ( p_codlead ldd_lead.codlead%type default null,
26 p_source ldd_campaign.codcampaignsource%type default null)
27 return number;
28
29-- Public findLeadToAssignDetails
30function findLeadToAssignDetails
31 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
32 p_codMarket in ldd_campaign_details.codmarket%type,
33 p_codLanguage in language.codlanguage%type,
34 p_codcampaign in ldd_campaign.codcampaign%type DEFAULT NULL,
35 p_campaignSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
36 p_excludeCmpSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
37 p_objTypeArray in objectTypeTable )
38 return elinkTypes.ref_collection;
39
40function countLeadToAssignDetails
41 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
42 p_codMarket in ldd_campaign_details.codmarket%type,
43 p_codLanguage in language.codlanguage%type,
44 p_codcampaign in ldd_campaign.codcampaign%type DEFAULT NULL,
45 p_campaignSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
46 p_excludeCmpSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
47 p_objTypeArray in objectTypeTable )
48 return INTEGER;
49
50-- Public findDealerLeadDetails
51function findDealerLeadDetails
52 (
53 p_codDealer in ldd_campaign_details.coddealer%type,
54 p_coddealerAssigner in ldd_campaign_details.coddealer%type,
55 p_codMarket in ldd_campaign_details.codmarket%type,
56 p_codLanguage in language.codlanguage%type,
57 p_objtype in LDD_OBJTYPES.objtype%type := NULL
58 )
59
60 return elinkTypes.ref_collection;
61
62-- Public findMyLeadDetails
63function findMyLeadDetails
64 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
65 p_codMarket in ldd_campaign_details.codmarket%type,
66 p_codLanguage in language.codlanguage%type,
67 p_usergid in ldd_lead.usergid%type default null,
68 p_campaignSource in CodCampaignSource,
69 p_excludeCmpSource in CodCampaignSource,
70 p_objtype in LDD_OBJTYPES.objtype%type := NULL,
71 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE default NULL )
72 return elinkTypes.ref_collection ;
73
74-- findMyLeadDetailsTB
75function findMyLeadDetailsTB
76 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
77 p_codMarket in ldd_campaign_details.codmarket%type,
78 p_codLanguage in language.codlanguage%type,
79 p_usergid in ldd_lead.usergid%type default null,
80 p_campaignSource in CodCampaignSource,
81 p_excludeCmpSource in CodCampaignSource,
82 p_objtype in LDD_OBJTYPES.objtype%type := NULL,
83 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE default NULL )
84 return elinkTypes.ref_collection;
85
86 -- Public findLeadManageAllLogin
87 function findLeadManageAllLogin(p_codMainDealer in ldd_campaign_details.codmaindealer%type,
88 p_codMarket in ldd_campaign_details.codmarket%type,
89 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
90 return elinkTypes.ref_collection;
91-- finds all the users that can be lead manager/handler for Lead Management Sellers list
92 function findLeadPrivsLogin(p_codMainDealer in ldd_campaign_details.codmaindealer%type,
93 p_codMarket in ldd_campaign_details.codmarket%type)
94 return elinkTypes.ref_collection;
95
96
97 -- Public findLeadManageLogin
98 function findLeadManageLogin(p_codMainDealer in ldd_dwh_fieldcounter.codmaindealer%type,
99 p_codMarket in ldd_dwh_fieldcounter.codmarket%type,
100 p_leadApplType in ldd_objtypes.linkappltype%type := NULL)
101 return elinkTypes.ref_collection;
102
103 -- Public testCrmLeadHandler - T12683
104 function testCrmLeadHandler(p_codMarket in ldd_dwh_fieldcounter.CODMARKET%type,
105 p_loginName in v_elinkusers.LOGINNAME%type,
106 p_codMainDealer in ldd_dwh_fieldcounter.CODMAINDEALER%type,
107 p_leadApplType in ldd_objtypes.LINKAPPLTYPE%type)
108 return NUMBER;
109
110 -- Public countSalesmenCampaignLead
111 function countSalesmenCampaignLead(p_codMainDealer in ldd_campaign_details.codmaindealer%type,
112 p_codMarket in ldd_campaign_details.codmarket%type,
113 p_codcampaignsource in ldd_campaign.codcampaignsource%type,
114 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
115 return elinkTypes.ref_collection;
116
117-- Public assignLead
118function assignLead
119 ( p_codLead in ldd_lead.codlead%type,
120 p_usergid in ldd_lead.usergid%type,
121 p_usergidAssigner in ELINKUSER.LOGINNAME%TYPE DEFAULT NULL ,
122 p_forceWait in number default 0 ,
123 p_IsHot IN NUMBER DEFAULT NULL )
124 return NUMBER ;
125
126function leadDataDetails
127 ( p_codLead in ldd_lead.codlead%type,
128 p_codMarket in ldd_campaign_details.codmarket%type,
129 p_mainDealer in ldd_campaign_details.codmaindealer%type )
130 return elinkTypes.ref_collection;
131
132 -- Public leadCustomerCenterDetails
133 function leadCustomerCenterDetails(p_codLead in ldd_lead.codlead%type)
134 return elinkTypes.ref_collection;
135
136function leadNextBuyDetails
137 ( p_codLead in ldd_lead.codlead%type )
138 return elinkTypes.ref_collection;
139
140 -- Public leadCarDetails
141 function leadCarDetails(p_codLead in ldd_lead.codlead%type)
142 return elinkTypes.ref_collection;
143
144-- public resetLeadsAssignedStatus
145-- mette le lead presenti nell'array in input in stato da assegnare
146function resetLeadsAssignedStatus
147 ( p_leadsList in leadsTable )
148 return number ;
149
150 -- Public findDetailedLeadDataForXLS
151function findDetailedLeadDataForExport
152 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
153 p_codMarket in ldd_campaign_details.codmarket%type,
154 p_codLanguage in language.codlanguage%type,
155 p_usergid in ldd_lead.usergid%type default NULL,
156 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
157 return elinkTypes.ref_collection;
158
159 ---------------------------------------------------------------------------
160function getStatusLeadsOrInterview(p_objtype in LDD_OBJTYPES.objtype%type := NULL)
161 return ELINKtypes.ref_collection;
162
163--- createNewLead
164--- metodo centralizzato di creazione di una LEAD
165--- TUTTE le creazioni di leads DEVONO usare questo metodo
166--- ritorna : >0 Nuovo codice Lead / NULL ---> ERRORE ( check logmaster/logc )
167--- B38233 - associazione della lead alla masterkey al momento della creazione
168FUNCTION createNewLead
169 ( p_codCampaignDetail IN LDD_LEAD.CODCAMPAIGNDETAIL%TYPE,
170 p_codContact IN LDD_LEAD.CODCONTACT%TYPE,
171 p_usergid IN LDD_LEAD.USERGID%TYPE,
172 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
173 p_codLeadExt IN LDD_LEAD.CODLEADEXT%TYPE,
174 p_CodContactCare IN LDD_LEAD.CODCONTACT_CARE%TYPE,
175 p_CodLeadSource IN LDD_LEAD.CODLEADSOURCE%TYPE,
176 p_reminderNote IN LDD_REMINDER_NOTES.REMINDER_NOTE%TYPE,
177 p_codLanguage IN LDD_LEAD.CODLANGUAGE%TYPE,
178 p_createdDate IN LDD_LEAD.CREATEDDATE%TYPE,
179 p_lastModifiedDate IN LDD_LEAD.LASTMODIFIEDDATE%TYPE DEFAULT NULL,
180 p_insertionDate IN LDD_LEAD.INSERTIONDATE%TYPE DEFAULT SYSDATE,
181 p_callCenterCallBack IN LDD_LEAD.CALLCENTER_CALLBACK%TYPE DEFAULT 0,
182 p_unTreated IN LDD_LEAD.UNTREATED%TYPE Default 0,
183 p_assignDate IN LDD_LEAD.ASSIGNDATE%TYPE DEFAULT NULL,
184 p_sentMail IN LDD_LEAD.SENTMAIL%TYPE Default 0,
185 p_objType IN LDD_LEAD.OBJTYPE%TYPE Default 'L',
186 p_phoneNumber IN LDD_LEAD.PHONENUMBER%TYPE DEFAULT NULL,
187 p_mobilePhone IN LDD_LEAD.MOBILEPHONE%TYPE DEFAULT NULL,
188 p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE DEFAULT NULL ,
189 p_timeZone IN MARKET.TIMEZONE_REGION%TYPE DEFAULT NULL ,
190 p_codActionContact IN CRM_ACTIONCONTACT.CODACTIONCONTACT%TYPE DEFAULT NULL,
191 p_codleadHub IN ldd_lead.codleadhub%TYPE DEFAULT NULL, -- T9786
192 p_pl_provider_id IN ldd_lead.pl_provider_id%TYPE DEFAULT NULL, -- T13040
193 p_codMasterKey IN LDD_HUB_MASTERKEY.CODMASTERKEY%TYPE DEFAULT NULL,
194 p_sourcequerystring IN LDD_LEAD.SOURCEQUERYSTRING%TYPE DEFAULT NULL, -- T14065
195 p_communicationchannel IN LDD_LEAD.COMMUNICATIONCHANNEL%TYPE DEFAULT NULL,
196 p_leadscore IN ldd_lead.leadscore%TYPE DEFAULT NULL) -- T15175
197 RETURN LDD_LEAD.CODLEAD%TYPE;
198
199--- updateLeadStatus
200--- La funzione ha lo scopo di centralizzare la modifica dello stato della LEAD
201--- in quanto tale operazione provoca l'esecuzione in cascata di altre operazioni
202--- Codici di ritorno : 1-->OK / 0-->KO
203FUNCTION updateLeadStatus
204 ( p_codLead IN LDD_LEAD.CODLEAD%TYPE ,
205 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
206 p_usergid IN LDD_LEAD.USERGID%TYPE DEFAULT NULL,
207 p_lastModifiedDate IN LDD_LEAD.LASTMODIFIEDDATE%TYPE DEFAULT NUll,
208 p_assignDate IN LDD_LEAD.ASSIGNDATE%TYPE DEFAULT NULL,
209 p_codActionContact IN CRM_ACTIONCONTACT.CODACTIONCONTACT%TYPE DEFAULT NULL,
210 p_logHandle IN LOGMASTER.SEQUENCEID%TYPE default NULL)
211 RETURN NUMBER;
212
213FUNCTION updateReminderNote
214 ( p_codLead IN LDD_REMINDER_NOTES.CODLEAD%TYPE,
215 p_reminderNote IN LDD_REMINDER_NOTES.REMINDER_NOTE%TYPE)
216 RETURN LDD_REMINDER_NOTES.CODLEAD%TYPE;
217
218 --- getNotManagedLead
219 --- Restituisce un elenco di lead non gestite in un determinato intervallo di ore.
220 --- Le ore sono da intendere come ore di calendario e non come ore lavorative
221 --- ( si basano sulla creation date)
222FUNCTION getnotmanagedlead
223 (p_codmarket IN ldd_campaign_details.codmarket%TYPE,
224 p_codlanguage IN ldd_lead.codlanguage%TYPE,
225 p_leadage IN NUMBER,
226 p_codmaindealer IN ldd_campaign_details.codmaindealer%TYPE DEFAULT NULL,
227 p_codbrand IN ldd_campaign_details.codbrand%TYPE DEFAULT NULL,
228 p_codcampaigntype IN ldd_campaign.codcampaigntype%TYPE DEFAULT NULL,
229 p_codcampaignsource IN ldd_campaign.codcampaignsource%TYPE DEFAULT NULL)
230 RETURN elinktypes.ref_collection;
231
232 /* getManagedLead
233 Restituisce un elenco di lead gestite in un determinato intervallo di ore.
234 Le ore sono da intendere come ore di calendario e non come ore lavorative
235 ( si basano sulla creation date).
236 restituisce la prima azione inserita sulla lead dopo la sua creazione */
237 FUNCTION getmanagedlead(p_codmarket IN ldd_campaign_details.codmarket%TYPE,
238 p_codlanguage IN ldd_lead.codlanguage%TYPE,
239 p_leadage IN NUMBER,
240 p_codmaindealer IN ldd_campaign_details.codmaindealer%TYPE DEFAULT NULL,
241 p_codbrand IN ldd_campaign_details.codbrand%TYPE DEFAULT NULL,
242 p_codcampaigntype IN ldd_campaign.codcampaigntype%TYPE DEFAULT NULL,
243 p_codcampaignsource IN ldd_campaign.codcampaignsource%TYPE DEFAULT NULL)
244 RETURN elinktypes.ref_collection;
245
246/* isUnmanagedToMask
247Returns 1 if input codlead is to mask (pin number to be hidden) in the unmanaged process, 0 otherwise
248
249Conditions to be masked (AND logic):
2501) ldd_lead.treated = 1
2512) ldd_lead.callcenter_callback = 0
2523) (sysdate - ldd_lead.date_untreated)[in the same timezone] < pkmarketparams.findValueByPrimaryKey(
253 CRM_UNMANAGED_NOT_CONTACTED_BY_CC,
254 ldd_campaingdetails.codmarket,
255 1,
256 0)
257*/
258FUNCTION isUnmanagedToMask(
259 p_codlead IN ldd_lead.codlead%TYPE,
260 p_codmarket IN ldd_campaign_details.codmarket%TYPE)
261 RETURN number;
262
263 /* notifyLeadAssigner
264 T8508 - Inserisce nella tabella ALR_SERVICESQUEUE una riga per il servizio SMS
265 da inviare all'assegnatario della LEAD (USERGID)
266 */
267 FUNCTION notifyLeadAssigner(p_codlead IN ldd_lead.codlead%TYPE,
268 p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE,
269 p_usergid IN LDD_LEAD.USERGID%TYPE,
270 p_idAlrService IN ALR_SERVICES.Idalr_Service%TYPE)
271 return number;
272 /* notifyLeadSellers
273 T8508 - Inserisce nella tabella ALR_SERVICESQUEUE una riga per il servizio SMS
274 da inviare a ciascun venditore del DEALER della LEAD
275 */
276 FUNCTION notifyLeadSellers(p_codlead IN ldd_lead.codlead%TYPE,
277 p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE,
278 p_idAlrService IN ALR_SERVICES.Idalr_Service%TYPE)
279 return number;
280
281--- T9325 - Assegnazione del contatto ad una LEAD. In caso di phone-lead le leads
282--- possono nascere senza contatto
283--- Ritorna : OK-->codice Lead ( numero positivo )
284--- KO-->codice errore ( numero negativo )
285FUNCTION updateLeadAssignContact
286 ( p_codLead IN LDD_LEAD.CODLEAD%TYPE,
287 p_codContact IN LDD_LEAD.CODCONTACT%TYPE )
288 RETURN LDD_LEAD.CODLEAD%TYPE;
289
290--- Pulizia records obsoleti relativi a :
291--- - LDD_LEADS_SLARANGES
292--- - LDD_LEAD_PUSHTIME
293--- Ritorna : >0 ( ok, numero di cancellazioni ) / <0 errore
294FUNCTION cleanupLeads
295 ( p_logHandle IN LOGMASTER.SEQUENCEID%TYPE DEFAULT NULL )
296 RETURN NUMBER;
297--T12154 Phone Lead Management as Sales
298--returns all the Phone Lead that must be reassigned to the real seller
299function findPhoneLeadToAssignDetails(p_codMainDealer in ldd_campaign_details.codmaindealer%type,
300 p_codMarket in ldd_campaign_details.codmarket%type,
301 p_codLanguage in language.codlanguage%type,
302 p_objTypeArray in objectTypeTable)
303 return elinkTypes.ref_collection;
304--returns all the users that have a CRM_HANDLER or a CRM_HANDLER_AS privilege (with the relative priv field)
305function findLeadManageLoginPriv(p_codMainDealer in ldd_dwh_fieldcounter.codmaindealer%type,
306 p_codMarket in ldd_dwh_fieldcounter.codmarket%type)
307 return elinkTypes.ref_collection;
308 --reassign a Phone Lead to the real seller that has managed it; used from the Phone lead managed monitor
309function reassignWorkingPhoneLead(p_codLead in ldd_lead.codlead%type,
310 p_usergid in ldd_lead.usergid%type,
311 p_usergidAssigner in ELINKUSER.LOGINNAME%TYPE DEFAULT NULL)
312 return number;
313
314--- B35429
315--- findAgendaByLeads
316--- Elenco dei dati agenda per un'array di leads
317FUNCTION findAgendaByLeads
318 ( p_arrayLeads IN leadsTable )
319 RETURN elinktypes.ref_collection ;
320
321function findLeadByCodleadHub(p_codleadhub ldd_lead.codleadhub%TYPE)
322 return elinkTypes.ref_collection;
323
324-- T12683 - Tipo applicazione Link dell'objtype (della lead)
325FUNCTION getApplTypeForLeadObjtype(p_objtype IN LDD_OBJTYPES.OBJTYPE%TYPE)
326 RETURN LDD_OBJTYPES.LINKAPPLTYPE%TYPE ;
327
328FUNCTION countDealerFleetLeads (
329 p_codmarket in ldd_campaign_details.codmarket%type,
330 p_codDealer in ldd_campaign_details.codmaindealer%type
331 )
332 RETURN elinkTypes.ref_collection;
333
334-- B36457 - Conteggio lead da gestire/in gestione per utente divise in objtype
335FUNCTION countManageLeadsByObjtype(p_codmarket in ldd_campaign_details.codmarket%type,
336 p_usergid IN TRG_LDD_LEAD.USERGID%TYPE,
337 p_codMainDealer in ldd_campaign_details.codmaindealer%type)
338 RETURN elinkTypes.ref_collection;
339
340function waitPin(p_codlead ldd_lead.codlead%type) return number;
341
342---T13556 Origine Creazione Lead interne
343FUNCTION insertLddLeadOrigin
344 ( P_CODLEAD IN LDD_LEAD_ORIGIN.CODLEAD%TYPE,
345 P_CODLEADORIGIN IN LDD_LEAD_ORIGIN.CODLEADORIGIN%TYPE )
346 RETURN NUMBER;
347
348FUNCTION getProperPhoneNumber(p_codMarket IN MARKET.CODMARKET%TYPE,
349 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
350 p_insertionDate IN LDD_LEAD.INSERTIONDATE%TYPE,
351 p_mobilePhone IN LDD_LEAD.MOBILEPHONE%TYPE,
352 p_phoneNumber IN LDD_LEAD.PHONENUMBER%TYPE,
353 p_codLead IN LDD_TRACKBACK.CODLEAD%TYPE,
354 p_dateSent IN LDD_TRACKBACK.DATE_SENT%TYPE,
355 p_pinMobile IN LDD_TRACKBACK.PIN_MOBILE%TYPE,
356 p_pinPhoneNumber IN LDD_TRACKBACK.PIN_PHONENUMBER%TYPE,
357 p_trackBackResult IN LDD_TRACKBACK.TB_RESULT%TYPE,
358 p_trackBackStatus IN LDD_TRACKBACK.TB_STATUS%TYPE,
359 p_checkMinutesPin NUMBER,
360 p_communicationchannel IN LDD_LEAD.COMMUNICATIONCHANNEL%TYPE DEFAULT null,
361 p_email IN CONTACT.EMAIL_ADDRESS%TYPE DEFAULT null) RETURN VARCHAR2;
362
363FUNCTION checkIsPin(p_codMarket IN MARKET.CODMARKET%TYPE,
364 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
365 p_insertionDate IN LDD_LEAD.INSERTIONDATE%TYPE,
366 p_codLead IN LDD_TRACKBACK.CODLEAD%TYPE,
367 p_dateSent IN LDD_TRACKBACK.DATE_SENT%TYPE,
368 p_trackBackResult IN LDD_TRACKBACK.TB_RESULT%TYPE,
369 p_trackBackStatus IN LDD_TRACKBACK.TB_STATUS%TYPE,
370 p_checkMinutesPin NUMBER,
371 p_communicationchannel IN LDD_LEAD.COMMUNICATIONCHANNEL%TYPE DEFAULT NULL) RETURN NUMBER;
372
373FUNCTION findBusinessAreaFromLead(p_codLead IN LDD_TRACKBACK.CODLEAD%TYPE)
374 RETURN elinktypes.ref_collection;
375
376-- T14379 - iLinkLeadGuests Lead iLink Welcome - Guests List
377FUNCTION findiLinkLeadGuests
378 ( p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE,
379 p_codmaindealer IN LDD_CAMPAIGN_DETAILS.CODMAINDEALER %TYPE,
380 p_codcampaigntype IN LDD_CAMPAIGN.CODCAMPAIGNTYPE%TYPE ,
381 p_namecampaign IN LDD_CAMPAIGN.NAMECAMPAIGN%TYPE,
382 p_dateFrom IN date,
383 p_dateTo IN date DEFAULT NULL,
384 p_codLanguage IN LANGUAGE.CODLANGUAGE%TYPE DEFAULT NULL )
385 RETURN ELINKtypes.ref_collection;
386
387
388FUNCTION setLeadAttendanceStatus
389 ( p_codlead IN LDD_EVENT_ATTENDANCE.CODLEAD%TYPE,
390 p_usergid IN LDD_EVENT_ATTENDANCE.USERGID%TYPE,
391 p_status_invitation IN LDD_EVENT_ATTENDANCE.STATUS_INVITATION%TYPE Default '0', --> 1 = invitato , 0 = non invitato
392 p_status_attendance IN LDD_EVENT_ATTENDANCE.STATUS_ATTENDANCE%TYPE Default '0' --> 1 = partecipa, 0 = non partecipa
393 )
394 RETURN NUMBER;
395
396 FUNCTION getLeadPromoDetails
397 (p_codLead in ldd_lead.codlead%type)
398 RETURN ELINKtypes.ref_collection;
399end pkLDD_LEADS;
400/
401create or replace package body pkLDD_LEADS is
402--- $Id: pkLDD_Leads.sql,v 1.263 2019/04/02 09:58:13 abis Exp $
403--- TRK_MANUAL_VERSION: v3
404 -----------------------------------------------------------------------------
405 --- variabili globbali
406 -----------------------------------------------------------------------------
407 x_sqlStmt VARCHAR2(4000);
408 x_logHandle LOGMASTER.SEQUENCEID%TYPE;
409 x_procedureName LOGMASTER.LOADERNAME%TYPE;
410 x_errCode NUMBER;
411 x_errMsg VARCHAR2(1024);
412
413------------------------------------------------------
414--- routines interne, NON PUBBLICATE
415------------------------------------------------------
416-- startLogManagement
417--- Se il log non e' aperto si apre una nuova sessione
418PROCEDURE startLogManagement IS
419BEGIN
420 IF x_logHandle IS NULL THEN
421 x_logHandle := PKLOG.Insertmaster(NULL,x_procedureName,NULL, '0' );
422 END IF;
423END startLogManagement;
424
425/* Toppa per i vecchi sistemi che gestiscono le lead
426 * senza distinguere tra lead e interviste SF.
427 * 1 : true
428 * 0 : false
429 */
430function isLeadInterview ( p_codlead ldd_lead.codlead%type )
431 return number is
432 v_exists NUMBER;
433BEGIN
434 SELECT 1
435 INTO v_exists
436 FROM LDD_LEAD LEA
437 WHERE LEA.CODLEAD = p_codlead
438 --CR5604 - nuove interviste LF
439 and LEA.OBJTYPE = 'I';
440 /* AND EXISTS ( SELECT 1
441 FROM ldd_campaign_details lcd
442 JOIN ldd_campaign lcp ON LCP.CODCAMPAIGN = lcd.codcampaign
443 WHERE LCD.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL
444 AND LCP.CODCAMPAIGNSOURCE = 'SF' );*/
445 RETURN v_exists;
446EXCEPTION
447 WHEN OTHERS THEN
448 return 0;
449end isLeadInterview;
450
451function isLeadLF ( p_codlead ldd_lead.codlead%type )
452 return number is
453 v_exists NUMBER;
454BEGIN
455 SELECT 1
456 INTO v_exists
457 FROM LDD_LEAD LL
458 LEFT JOIN ldd_lead_hub_masterkey lhm on lhm.codlead = ll.codlead
459 LEFT JOIN ldd_hub_masterkey mk on lhm.codmasterkey = mk.codmasterkey
460 WHERE ll.CODLEAD = p_codlead
461 and ll.OBJTYPE = 'I'
462 and mk.level1 <> 'SHOPPER INDECISI';
463 RETURN v_exists;
464EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 BEGIN
467 SELECT 1
468 INTO v_exists
469 from ldd_lead l
470 join ldd_campaign_details d
471 on l.CODCAMPAIGNDETAIL = d.CODCAMPAIGNDETAIL
472 join ldd_campaign c
473 on c.CODCAMPAIGN = d.CODCAMPAIGN
474 where l.codlead = p_codlead
475 and l.OBJTYPE = 'I'
476 and c.CODCAMPAIGNSOURCE = 'LF';
477 RETURN v_exists;
478 EXCEPTION
479 WHEN OTHERS THEN
480 return 0;
481 END;
482 WHEN OTHERS THEN
483 return 0;
484end isLeadLF;
485
486function isLeadSF(p_codlead ldd_lead.codlead%type) return number is
487 v_exists NUMBER;
488BEGIN
489 SELECT 1
490 INTO v_exists
491 FROM LDD_LEAD LL
492 LEFT JOIN ldd_lead_hub_masterkey lhm
493 on lhm.codlead = ll.codlead
494 LEFT JOIN ldd_hub_masterkey mk
495 on lhm.codmasterkey = mk.codmasterkey
496 WHERE ll.CODLEAD = p_codlead
497 and ll.OBJTYPE = 'I'
498 and mk.level1 = 'SHOPPER INDECISI';
499 RETURN v_exists;
500EXCEPTION
501 WHEN NO_DATA_FOUND THEN
502 BEGIN
503 SELECT 1
504 INTO v_exists
505 from ldd_lead l
506 join ldd_campaign_details d
507 on l.CODCAMPAIGNDETAIL = d.CODCAMPAIGNDETAIL
508 join ldd_campaign c
509 on c.CODCAMPAIGN = d.CODCAMPAIGN
510 where l.codlead = p_codlead
511 and l.OBJTYPE = 'I'
512 and c.CODCAMPAIGNSOURCE = 'SF';
513 RETURN v_exists;
514 EXCEPTION
515 WHEN OTHERS THEN
516 return 0;
517 END;
518 WHEN OTHERS THEN
519 return 0;
520end isLeadSF;
521
522/*
523 * Function that checks if the lead is DBCare.
524 * It can be passed both p_lead or p_source.
525 * If it's p_lead, we search if the campaign's source has
526 * the flag ISDBCARE set to 1.
527 * If it's p_source, then we ensure that its record inside
528 * LDD_CAMPAIGN_SOURCES has the flag ISDBCARE set to 1.
529 */
530function isLeadCare ( p_codlead ldd_lead.codlead%type default null,
531 p_source ldd_campaign.codcampaignsource%type default null)
532 return number is
533 v_exists NUMBER := 0;
534
535BEGIN
536 IF p_codlead IS NOT NULL THEN
537 SELECT 1
538 INTO v_exists
539 FROM LDD_LEAD LEA
540 WHERE LEA.CODLEAD = p_codlead
541 AND EXISTS (
542 SELECT 1
543 FROM LDD_CAMPAIGN_DETAILS LCD
544 JOIN LDD_CAMPAIGN LCP
545 ON LCP.CODCAMPAIGN = LCD.CODCAMPAIGN
546 JOIN LDD_CAMPAIGN_SOURCES LCS
547 ON LCP.CODCAMPAIGNSOURCE = LCS.CODCAMPAIGNSOURCE
548 WHERE LCD.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL
549 AND LCS.ISDBCARE = 1
550 );
551 RETURN v_exists;
552 ELSIF p_source IS NOT NULL THEN
553 SELECT 1
554 INTO v_exists
555 FROM LDD_CAMPAIGN_SOURCES LCS
556 WHERE LCS.CODCAMPAIGNSOURCE = p_source
557 AND LCS.ISDBCARE = 1;
558 RETURN v_exists;
559 ELSE
560 RETURN 0;
561 END IF;
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 RETURN 0;
566
567 END;
568
569 /* Privata, rinvia l'invio dell'sms per le lead in assegnazione automatica,
570 al momento dell'upload del PIN su trackback,
571 che altrimenti non viene avvalorato.
572 1 : true
573 0 : false */
574 function waitPin(p_codlead ldd_lead.codlead%type) return number is
575 v_wait NUMBER;
576 BEGIN
577
578 SELECT 1
579 INTO v_wait
580 FROM ldd_trackback tb
581 JOIN ldd_lead lea
582 ON lea.codlead = tb.codlead
583 WHERE lea.codlead = p_codlead
584 AND lea.codleadstatus = 'DG'
585 AND lea.usergid is not null
586 AND tb.pin_phonenumber is null
587 AND tb.pin_mobile is null;
588 RETURN v_wait;
589 EXCEPTION
590 WHEN OTHERS THEN
591 return 0;
592 end waitPin;
593
594-- Public findLeadToAssignDetails
595--
596-- Restituisce i dati di tutte le Leads del MainDelaer che sono
597-- ancora da assegnare (per il box Leads To Assign del CRM)
598function findLeadToAssignDetails
599 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
600 p_codMarket in ldd_campaign_details.codmarket%type,
601 p_codLanguage in language.codlanguage%type,
602 p_codcampaign in ldd_campaign.codcampaign%type DEFAULT NULL,
603 p_campaignSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
604 p_excludeCmpSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
605 p_objTypeArray in objectTypeTable)
606 return elinkTypes.ref_collection is
607 myCursor elinkTypes.ref_collection;
608 x_checkDate CONSTANT DATE := SYSDATE;
609 v_objTypeArray LISTOFVALUEC;
610 v_objTypeCount NUMBER;
611 v_codNation NATION.CODNATION%TYPE;
612BEGIN
613--- T9688 - ObjectTypes Array
614 v_objTypeArray := LISTOFVALUEC();
615 IF p_objTypeArray IS NOT NULL AND p_objTypeArray.count > 0 THEN
616 for idx in p_objTypeArray.first .. p_objTypeArray.last
617 loop
618 v_objTypeArray.EXTEND;
619 v_objTypeArray(idx) := p_objTypeArray(idx);
620 end loop;
621 v_objTypeCount := v_objTypeArray.count;
622 ELSE
623 v_objTypeCount := 0;
624 END IF;
625
626--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
627 v_codNation := PKNATION.getNationForUser(p_codmarket,p_codMainDealer);
628
629--- T12222 - Utilizzo TRG_LDD_LEAD. Tabella "snella" popolata via trigger
630 open myCursor for
631 select LEA.CODLEAD,
632--- T8781 - calcolo del livello di severity in base ai ranges presenti in LDD_LEADS_SLARANGES
633--- rapportati alla data attuale ( x_checkDate )
634 CASE WHEN LEA.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
635 LEA.FINAL_SEVERITYLEVELFIELD
636 ELSE
637 NVL( ( select MAX(LSR.SEVERITYLEVELFIELD) KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
638 FROM LDD_LEADS_SLARANGES LSR
639 WHERE LSR.CODLEAD = LEA.CODLEAD
640--- B29894 - Sia Leads che interviste vengono conteggiate secondo lo sla "L"
641 --AND LSR.CODSLAEVENT = 'L'
642 AND LSR.CODSLAEVENT = DECODE(NVL(LEA.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
643 AND LSR.SLALIMIT > x_checkDate ) , 'R' )
644 END AS STATUS,
645--- T12363 - la funzione getworkinghours e' lenta per differenze di tempo elevate
646--- se piu' di un mese si torna -1
647 CASE WHEN LEA.CREATEDDATE < ADD_MONTHS(SYSDATE,-1) THEN
648 -1
649 ELSE
650--- T12851 - Gestione orari di lavoro differenti per AfterSales ('A')
651 ROUND ( ( SELECT pkholidays.getworkinghours
652 ( CAST(LEA.CREATEDDATE as DATE),
653 CAST(SYSTIMESTAMP AT TIME ZONE TO_CHAR(LEA.CREATEDDATE,'TZR') AS DATE),
654 p_codMarket,
655 CASE WHEN LEA.OBJTYPE = 'A' THEN 'A' ELSE 'L' END ,
656 v_codNation
657 ) FROM DUAL ) , 2 )
658 END as HOURS,
659 TO_CHAR(LEA.LASTMODIFIEDDATE,'YYYY-MM-DD HH24:MI:SS') as LASTMODIFIEDDATE,
660 LDT.PRIORITY,
661 LCS.DESCRIPTION as LEAD_SOURCE,
662 TO_CHAR(LEA.CREATEDDATE,'YYYY-MM-DD HH24:MI:SS') as CREATEDDATE,
663 ( SELECT LTY.DESCRIPTION
664 FROM LDD_CAMPAIGN_TYPES LTY
665 WHERE LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE ) as TYPE_DESC,
666 LDT.CODBRAND,
667 CASE WHEN LEA.Objtype = 'P' THEN
668 ( SELECT CODBRAND FROM LDD_HUB_MASTERKEY WHERE CODMASTERKEY = (SELECT CODMASTERKEY from LDD_LEAD_HUB_MASTERKEY WHERE CODLEAD = LEA.CODLEAD))
669 ELSE
670 ( SELECT BND.BRANDNAME
671 FROM brand BND
672 WHERE BND.codbrand = nvl(LDT.codbrand,'99') )
673 END AS BRAND,
674 ( SELECT LDP.DESCRPRODUCT
675 FROM LDD_PRODUCT LDP
676 WHERE LDP.CODLEAD = LEA.CODLEAD ) as MODEL,
677 LEA.CODCONTACT,
678 CASE WHEN CT.CODCONTACTTYPE IN ('P', 'R') THEN
679 ( SELECT PRS.LASTNAME || ' ' || PRS.FIRSTNAME
680 FROM PERSON PRS
681 WHERE PRS.CODPERSON = CT.CODCONTACT )
682 ELSE
683 ( SELECT CPY.COMPANYNAME
684 FROM COMPANY CPY
685 WHERE CPY.CODCOMPANY = CT.CODCONTACT )
686 END AS CONTACT,
687 ( SELECT CTY.DESCR_CITY
688 FROM CITY CTY
689 WHERE CTY.CODCITY = CT.CODCITY
690 AND CTY.CODDEPARTMENT = CT.CODDEPARTMENT
691 AND CTY.CODREGION = CT.CODREGION
692 AND CTY.CODNATION = CT.CODNATION) as CITY ,
693 CASE WHEN LCP.CODCAMPAIGNSOURCE not in ('DC') THEN
694 LCP.NAMECAMPAIGN
695 ELSE
696 LCS.DESCRIPTION||': '||LCP.NAMECAMPAIGN
697 END as CAMPAIGN_NAME,
698 LTS.TOBECOUNTED,
699 LEA.OBJTYPE,--T13729
700 ( SELECT DLL.ADDRESS ||' - '||DLL.ZIP_NAME||' - '||DLL.TOWN_NAME
701 FROM DEALERLOCATION DLL
702 WHERE DLL.CODMARKET = LDT.CODMARKET
703 AND DLL.CODDEALER = LDT.CODDEALER
704 AND DLL.CODLOCATION = LDT.CODDEALERLOCATION ) AS DEALERADDRESS,
705 LEA.UNTREATED,
706 LEA.CALLCENTER_CALLBACK,
707--- T13733 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
708 BUA.BUSINESS_AREA AS MBA,
709 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
710--- T13733 -END-
711 LEA.LEADSCORE,
712 BUA.MK_ISHOT as ISHOT, -- TRK campo ishot per mercato turchia
713 MSTK.LVL1 as LEVEL1,
714 MSTK.LVL2 as LEVEL2
715 from TRG_LDD_LEAD TLL
716 join LDD_CAMPAIGN_DETAILS LDT on (LDT.CODCAMPAIGNDETAIL = TLL.CODCAMPAIGNDETAIL)
717 JOIN ldd_lead LEA ON TLL.CODLEAD = LEA.CODLEAD -- T15301
718 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
719 join LDD_CAMPAIGN_TYPES_SOURCE LTS
720 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
721 LTS.CODCAMPAIGNTYPE = LCP.CODCAMPAIGNTYPE)
722 join LDD_CAMPAIGN_SOURCES LCS on (LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE)
723 left join CONTACT CT on (CT.CODCONTACT = LEA.CODCONTACT)
724--- T13733 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
725 left join (select ba.business_area as BUSINESS_AREA,
726 ba.description as BUSINESS_AREA_DESCRIPTION,
727 lh.codlead as BUSINESS_AREA_CODLEAD,
728 h.ishot as MK_ISHOT
729 from LDD_LEAD_HUB_MASTERKEY lh
730 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
731 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
732 on BUA.BUSINESS_AREA_CODLEAD = TLL.CODLEAD
733--- T13733 -END-
734 left join (select
735 h.level2 as LVL2,
736 h.level1 as LVL1,
737 lh.codlead as CODLEADMSTK
738 from ldd_hub_masterkey h
739 left join LDD_LEAD_HUB_MASTERKEY lh
740 on lh.codmasterkey = h.codmasterkey) MSTK
741 on MSTK.CODLEADMSTK = TLL.CODLEAD
742 where TLL.USERGID is NULL
743 AND TLL.CODLEADSTATUS in ('DA','DL')
744--- T9688 - Se non viene passato alcun objtype si prendono tutti, altrimenti quelli passati
745 AND ( v_objTypeCount = 0
746 OR
747 LEA.OBJTYPE IN ( SELECT *
748 FROM TABLE ( CAST (v_objtypeArray as LISTOFVALUEC)))
749 )
750 AND LDT.CODMAINDEALER = p_codMainDealer
751 AND LDT.CODMARKET = p_codMarket
752 AND LTS.ISDELETED=0
753 AND (p_campaignSource IS NULL OR p_campaignSource = LCP.codcampaignsource) AND
754-- voglio tutte lead TRANNE quelle di una certa sorgente #t5550
755 (p_excludeCmpSource IS NULL OR p_excludeCmpSource <> LCP.codcampaignsource) AND
756 (p_codcampaign IS NULL OR p_codcampaign = LCP.CODCAMPAIGN)
757 order by LEA.CREATEDDATE desc;
758
759 return myCursor;
760
761end findLeadToAssignDetails;
762
763--- T9688 - Il parametro objtype ora e' un'array di tipi
764function countLeadToAssignDetails
765 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
766 p_codMarket in ldd_campaign_details.codmarket%type,
767 p_codLanguage in language.codlanguage%type,
768 p_codcampaign in ldd_campaign.codcampaign%type DEFAULT NULL,
769 p_campaignSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
770 p_excludeCmpSource in ldd_campaign.codcampaignsource%type DEFAULT NULL,
771 p_objTypeArray in objectTypeTable )
772 return INTEGER is
773 v_tot INTEGER;
774 v_objTypeArray LISTOFVALUEC;
775 v_objTypeCount NUMBER;
776BEGIN
777--- T9688 - ObjectTypes Array
778 v_objTypeArray := LISTOFVALUEC();
779 IF p_objTypeArray IS NOT NULL AND p_objTypeArray.count > 0 THEN
780 for idx in p_objTypeArray.first .. p_objTypeArray.last
781 loop
782 v_objTypeArray.EXTEND;
783 v_objTypeArray(idx) := p_objTypeArray(idx);
784 end loop;
785 v_objTypeCount := v_objTypeArray.count;
786 ELSE
787 v_objTypeCount := 0;
788 END IF;
789
790--- T8730 - Select trasfromata in dynamic sql. Molto usata, occorre sfruttare a pieno
791--- i parametri passati dal chiamante
792
793 x_sqlStmt := '
794select /* pkLDD_LEADS.countLeadToAssignDetails */ count(*)
795 from ldd_lead LEA
796 join LDD_CAMPAIGN_DETAILS LDT on (LDT.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL)
797 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
798 join LDD_CAMPAIGN_TYPES_SOURCE LTS
799 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
800 LTS.CODCAMPAIGNTYPE = LCP.CODCAMPAIGNTYPE )
801 join LDD_CAMPAIGN_SOURCES LCS on (LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE)
802 where LEA.USERGID is NULL
803 AND LEA.CODLEADSTATUS = ''DA''
804 AND LEA.CODCONTACT IS NOT NULL
805 AND LTS.ISDELETED=0
806 AND LDT.CODMARKET = :p_codMarket
807 AND LDT.CODMAINDEALER = :p_codMainDealer
808 AND ( :v_objTypeCount = 0
809 OR
810 LEA.OBJTYPE IN ( SELECT *
811 FROM TABLE ( CAST (:v_objtypeArray as LISTOFVALUEC)))
812 ) ';
813--- Parametri opzionali
814 IF p_codcampaign IS NOT NULL THEN
815 x_sqlStmt := x_sqlStmt || ' AND LCP.CODCAMPAIGN = :p_codcampaign ';
816 ELSE
817 x_sqlStmt := x_sqlStmt || ' AND :p_codcampaign IS NULL ';
818 END IF;
819 IF p_campaignSource IS NOT NULL THEN
820 x_sqlStmt := x_sqlStmt || ' AND LCS.codcampaignsource = :p_campaignSource ';
821 ELSE
822 x_sqlStmt := x_sqlStmt || ' AND :p_campaignSource IS NULL ';
823 END IF;
824 IF p_excludeCmpSource IS NOT NULL THEN
825 x_sqlStmt := x_sqlStmt || ' AND LCS.codcampaignsource != :p_excludeCmpSource ';
826 ELSE
827 x_sqlStmt := x_sqlStmt || ' AND :p_excludeCmpSource IS NULL ';
828 END IF;
829
830 EXECUTE IMMEDIATE x_sqlStmt
831 INTO v_tot
832 USING p_codMarket,
833 p_codMainDealer,
834 v_objTypeCount,
835 v_objtypeArray,
836 p_codcampaign,
837 p_campaignSource,
838 p_excludeCmpSource;
839
840 return v_tot;
841
842end countLeadToAssignDetails;
843
844function findDealerLeadDetails
845 (
846 p_codDealer in ldd_campaign_details.coddealer%type,
847 p_coddealerAssigner in ldd_campaign_details.coddealer%type,
848 p_codMarket in ldd_campaign_details.codmarket%type,
849 p_codLanguage in language.codlanguage%type,
850 p_objtype in LDD_OBJTYPES.objtype%type := NULL
851 )
852 return elinkTypes.ref_collection is
853
854 myCursor elinkTypes.ref_collection;
855 x_checkDate CONSTANT DATE := SYSDATE;
856 v_checkMinutesPin NUMBER;
857 v_codNation NATION.CODNATION%TYPE;
858
859BEGIN
860
861--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
862 v_codNation := PKNATION.getNationForUser(p_codmarket,p_codDealer);
863
864 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',p_codMarket,1,0) into v_checkMinutesPin from dual;
865--- T9258 - Le leads sono visibili senza aspettare che "CREATEDDATE>SYSDATE"
866 open myCursor for
867 select LEA.CODLEAD,
868 LEA.USERGID,
869 LEA.CODLEADSTATUS,
870 LEA.LEADSCORE,
871--- T8781 - calcolo del livello di severity in base ai ranges presenti in LDD_LEADS_SLARANGES
872 CASE WHEN LEA.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
873 LEA.FINAL_SEVERITYLEVELFIELD
874 ELSE
875 NVL( ( select MAX(LSR.SEVERITYLEVELFIELD) KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
876 FROM LDD_LEADS_SLARANGES LSR
877 WHERE LSR.CODLEAD = LEA.CODLEAD
878--- B29894 - Sia Leads che interviste vengono conteggiate secondo lo sla "L"
879 --AND LSR.CODSLAEVENT = 'L'
880 AND LSR.CODSLAEVENT = DECODE(NVL(LEA.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
881 AND LSR.SLALIMIT > x_checkDate ) , 'R' )
882 END AS STATUS,
883--- T12363 - la funzione getworkinghours e' lenta per differenze di tempo elevate
884--- se piu' di un mese si torna -1
885 CASE WHEN LEA.CREATEDDATE < ADD_MONTHS(SYSDATE,-1) THEN
886 -1
887 ELSE
888--- T12851 - Gestione orari di lavoro differenti per AfterSales ('A')
889 ROUND ( ( SELECT pkholidays.getworkinghours
890 ( CAST(LEA.CREATEDDATE as DATE),
891 CAST(SYSTIMESTAMP AT TIME ZONE TO_CHAR(LEA.CREATEDDATE,'TZR') AS DATE),
892 p_codMarket,
893 CASE WHEN LEA.OBJTYPE = 'A' THEN 'A' ELSE 'L' END,
894 v_codNation
895 ) FROM DUAL ) , 2 )
896 END as HOURS,
897--- T8300 - per i campi di tipo TIMESTAMP, occorre una conversione
898 TO_CHAR(LEA.LASTMODIFIEDDATE,'YYYY-MM-DD HH24:MI:SS') as LASTMODIFIEDDATE,
899 TO_CHAR(LEA.CREATEDDATE,'YYYY-MM-DD HH24:MI:SS') as CREATEDDATE,
900 TO_CHAR(LEA.INSERTIONDATE,'YYYY-MM-DD HH24:MI:SS') as INSERTIONDATE,
901 ( SELECT LCS.DESCRIPTION
902 FROM LDD_CAMPAIGN_SOURCES LCS
903 WHERE LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE) as LEAD_SOURCE,
904 LDT.PRIORITY,
905 ( SELECT LTY.DESCRIPTION
906 FROM LDD_CAMPAIGN_TYPES LTY
907 WHERE LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE) as TYPE_DESC,
908 LDT.CODBRAND,
909 LCP.ISCENTRAL,
910 -- T11796 phone lead brand Unknown
911 CASE
912 WHEN NVL(p_objtype,'L') = 'P' THEN (SELECT mk.codbrand
913 FROM ldd_lead_hub_masterkey lhm
914 LEFT JOIN ldd_hub_masterkey mk
915 ON lhm.codmasterkey = mk.codmasterkey
916 where lhm.codlead = lea.codlead)
917 ELSE (select BR.BRANDNAME
918 from BRAND BR
919 where BR.CODBRAND = LDT.CODBRAND)
920 END as BRAND,
921 ( SELECT LDP.DESCRPRODUCT
922 FROM LDD_PRODUCT LDP
923 WHERE LDP.CODLEAD = LEA.CODLEAD ) AS COMMERCIALMODEL,
924 LEA.CODCONTACT,
925 CASE WHEN CT.CODCONTACTTYPE IN ('P', 'R') THEN
926
927 ( SELECT PRS.LASTNAME || ' ' || PRS.FIRSTNAME
928 FROM PERSON PRS
929 WHERE PRS.CODPERSON = CT.CODCONTACT )
930
931 WHEN CT.CODCONTACTTYPE IS NOT NULL THEN
932
933 ( SELECT CPY.COMPANYNAME
934 FROM COMPANY CPY
935 WHERE CPY.CODCOMPANY = CT.CODCONTACT )
936 ELSE
937 NULL --- Contatto Nullo --> Puo' succedere per Phone lead
938 END AS CONTACT,
939 ( SELECT CTY.DESCR_CITY
940 FROM CITY CTY
941 WHERE CTY.CODCITY = CT.CODCITY
942 AND CTY.CODDEPARTMENT = CT.CODDEPARTMENT
943 AND CTY.CODREGION = CT.CODREGION
944 AND CTY.CODNATION = CT.CODNATION) as CITY,
945-- T7839 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
946-- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
947 pkLDD_LEADS.getProperPhoneNumber(
948 p_codMarket,
949 LEA.CODLEADSTATUS,
950 LEA.INSERTIONDATE,
951 LEA.MOBILEPHONE,
952 LEA.PHONENUMBER,
953 TBK.CODLEAD,
954 TBK.DATE_SENT,
955 TBK.PIN_MOBILE,
956 TBK.PIN_PHONENUMBER,
957 TBK.TB_RESULT,
958 TBK.TB_STATUS,
959 v_checkMinutesPin,
960 LEA.COMMUNICATIONCHANNEL,
961 CT.EMAIL_ADDRESS) AS PHONENUMBER,
962-- T7839 - Un flag indica se il numero di telefono riguarda un PIN
963-- T14290 - Refactoring del calcolo relativo al Pin. Ora il calcolo ? centralizzato
964 pkLDD_LEADS.checkIsPin(
965 p_codMarket,
966 LEA.CODLEADSTATUS,
967 LEA.INSERTIONDATE,
968 nvl(TBK.CODLEAD,LEA.CODLEAD),
969 TBK.DATE_SENT,
970 TBK.TB_RESULT,
971 TBK.TB_STATUS,
972 v_checkMinutesPin,
973 LEA.COMMUNICATIONCHANNEL) AS ISPIN,
974 dloc.town_name as SITE,
975 dloc.address as address,
976 LCP.CODCAMPAIGN ,
977 LCP.CODCAMPAIGNSOURCE ,
978 LCP.CODCAMPAIGNTYPE ,
979 LCP.NAMECAMPAIGN ,
980 LCP.STARTDATE ,
981 LCP.ENDDATE ,
982 LCP.CODCAMPAIGNEXT ,
983 LCP.ISCENTRAL ,
984 LCP.CREATIONDATE ,
985 ( SELECT ELU.FIRSTNAME ||' '|| ELU.LASTNAME
986 FROM ELINKUSER ELU
987 WHERE ELU.LOGINNAME = LEA.USERGID ) as USERGID_NAME,
988 LEA.UNTREATED,
989 LEA.CALLCENTER_CALLBACK,
990 LUC.ICON,
991 LEA.OBJTYPE,
992 LUC.DESCRIPTION,
993 LUC.CODUNDECIDEDCATEGORY,
994 TBK.TB_RESULT,
995 CT.EMAIL_ADDRESS,
996 -- T11421 - Numero di trackback letto da tabella NATIONLANGUAGE (anziche' MARKETLANGUAGE)
997 -- Lingua = lingua della lead, se specificata e configurata per la nazione, altrimenti si
998 -- utilizza la lingua principale del mercato.
999 -- Nazione = nazione del dealer, se specificata, altrimenti si utilizza la
1000 -- nazione del principale del mercato.
1001 ( SELECT NL.LDD_TRACKBACK_NUMBER
1002 FROM NATIONLANGUAGE NL
1003 WHERE NL.CODNATION = NVL ((SELECT d.codnation FROM dealer d
1004 WHERE d.codmarket = ldt.codmarket
1005 AND d.coddealer=ldt.coddealer),
1006 (SELECT mn.codnation FROM marketnation mn
1007 WHERE mn.codmarket = ldt.codmarket
1008 AND mn.ismain = 1) )
1009 AND NL.CODLANGUAGE = NVL ((SELECT nl.codlanguage FROM nationlanguage nl
1010 WHERE nl.codlanguage=LEA.CODLANGUAGE
1011 AND nl.codnation = NVL ((SELECT d.codnation FROM dealer d
1012 WHERE d.codmarket = ldt.codmarket
1013 AND d.coddealer=ldt.coddealer),
1014 (SELECT mn.codnation FROM marketnation mn
1015 WHERE mn.codmarket = ldt.codmarket
1016 AND mn.ismain = 1) ) ),
1017 (SELECT m.codlanguage FROM market m
1018 WHERE m.codmarket = ldt.codmarket) ) ) AS LDD_TRACKBACK_NUMBER,
1019 LEA.CODLEADEXT,
1020 (select pkdict.tran(
1021 (select max(act.coddictionary) keep(dense_rank first order by ldh.creationdate desc, cac.codactioncontact desc)
1022 from ldd_leadhistory ldh
1023 join crm_actioncontact cac
1024 on cac.codactioncontact = ldh.codactioncontact
1025 join crm_action act
1026 on act.codaction = cac.codaction
1027 where ldh.codlead = LEA.codlead
1028 and ldh.codleadstatus = 'IG'
1029 ),
1030 p_codLanguage) from dual) AS MANAGEDACTION,
1031 BUA.BUSINESS_AREA as MBA,
1032 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
1033 LEA.COMMUNICATIONCHANNEL,
1034 BUA.MK_ISHOT as ISHOT -- TRK campo ishot per mercato turchia
1035 from LDD_CAMPAIGN_DETAILS LDT
1036--- T12222 - sfruttiamo la tabella TRG_LDD_LEAD, popolata con trigger
1037 JOIN TRG_LDD_LEAD TLL ON TLL.CODCAMPAIGNDETAIL = LDT.CODCAMPAIGNDETAIL
1038 join ldd_lead LEA on (LEA.CODLEAD = TLL.CODLEAD)
1039 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
1040 join LDD_CAMPAIGN_TYPES_SOURCE LTS
1041 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
1042 LTS.CODCAMPAIGNTYPE=LCP.CODCAMPAIGNTYPE )
1043 LEFT join CONTACT CT on (CT.CODCONTACT = LEA.CODCONTACT)
1044 left join dealerlocation dloc
1045 on (ldt.codmarket = dloc.codmarket and
1046 ldt.coddealer = dloc.coddealer and
1047 ldt.coddealerlocation = dloc.codlocation)
1048 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LEA.CODLEAD )
1049 LEFT JOIN LDD_UNDECIDED_CATEGORIES LUC ON (LUC.CODCAMPAINGSOURCE = LTS.CODCAMPAIGNSOURCE
1050 AND LUC.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE)
1051 left join (select ba.business_area as BUSINESS_AREA,
1052 ba.description as BUSINESS_AREA_DESCRIPTION,
1053 lh.codlead as BUSINESS_AREA_CODLEAD,
1054 h.ishot as MK_ISHOT
1055 from LDD_LEAD_HUB_MASTERKEY lh
1056 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
1057 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
1058 on BUA.BUSINESS_AREA_CODLEAD = TLL.CODLEAD
1059 join LDD_LEAD_HQ_ASSIGNED LHQ
1060 ON LHQ.CODLEAD = LEA.CODLEAD
1061 AND LHQ.ASSIGNER_DEALER = p_coddealerAssigner
1062 where LDT.CODDEALER = p_codDealer
1063 AND LDT.CODMARKET = p_codMarket
1064 AND LEA.OBJTYPE = p_objtype
1065 AND LTS.ISDELETED=0
1066 AND TLL.CODLEADSTATUS in ('DA')
1067 -- check if lead has been assigned from the input p_coddealerAssigner (that should be the designed dealer HQ)
1068 AND LHQ.OPERATION = 'A'
1069 order by HOURS DESC;
1070
1071 return myCursor;
1072
1073end findDealerLeadDetails;
1074
1075-- Public findMyLeadDetails
1076--
1077-- Restituisce i dati di tutte le Leads del MainDelaer che sono
1078-- ancora da assegnare (per il box Leads To Assign del CRM)
1079-- RR - aggiunto parametro application per separare le lead
1080-- in funzione dell'applicazione. Se NULL prende tutto
1081-- B31249 - INSERTIONDATE in output
1082function findMyLeadDetails
1083 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
1084 p_codMarket in ldd_campaign_details.codmarket%type,
1085 p_codLanguage in language.codlanguage%type,
1086 p_usergid in ldd_lead.usergid%type default null,
1087 p_campaignSource in CodCampaignSource,
1088 p_excludeCmpSource in CodCampaignSource,
1089 p_objtype in LDD_OBJTYPES.objtype%type := NULL,
1090 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE default NULL )
1091 return elinkTypes.ref_collection is
1092 myCursor elinkTypes.ref_collection;
1093 v_includeCodCampaignTable ListOfValueC ;
1094 v_excludeCodCampaignTable ListOfValueC ;
1095 v_noFilterInclude NUMBER;
1096 v_noFilterExclude NUMBER;
1097 x_checkDate CONSTANT DATE := SYSDATE;
1098 v_checkMinutesPin NUMBER;
1099 v_codNation NATION.CODNATION%TYPE;
1100BEGIN
1101-- ListOfValueC per l'array di campagne da Includere
1102 BEGIN
1103 v_IncludeCodCampaignTable := LISTOFVALUEC();
1104 for idx in p_campaignSource.first .. p_campaignSource.last
1105 loop
1106 v_IncludeCodCampaignTable.EXTEND;
1107 v_IncludeCodCampaignTable(idx) := p_campaignSource(idx);
1108 end loop;
1109 v_noFilterInclude := 0;
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 v_noFilterInclude := 1;
1114 END;
1115
1116-- ListOfValueC per l'array di campagne da Escludere
1117 BEGIN
1118 v_excludeCodCampaignTable := LISTOFVALUEC();
1119
1120 for idx in p_excludeCmpSource.first .. p_excludeCmpSource.last
1121 loop
1122 v_excludeCodCampaignTable.EXTEND;
1123 v_excludeCodCampaignTable(idx) := p_excludeCmpSource(idx);
1124 end loop;
1125 v_noFilterExclude := 0;
1126
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 v_noFilterExclude := 1;
1130 END;
1131
1132--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
1133 v_codNation := PKNATION.getNationForUser(p_codmarket,p_codMainDealer);
1134
1135 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',p_codMarket,1,0) into v_checkMinutesPin from dual;
1136--- T9258 - Le leads sono visibili senza aspettare che "CREATEDDATE>SYSDATE"
1137 open myCursor for
1138 select LEA.CODLEAD,
1139 LEA.USERGID,
1140 LEA.CODLEADSTATUS,
1141 LEA.LEADSCORE,
1142--- T8781 - calcolo del livello di severity in base ai ranges presenti in LDD_LEADS_SLARANGES
1143 CASE WHEN LEA.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
1144 LEA.FINAL_SEVERITYLEVELFIELD
1145 ELSE
1146 NVL( ( select MAX(LSR.SEVERITYLEVELFIELD) KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
1147 FROM LDD_LEADS_SLARANGES LSR
1148 WHERE LSR.CODLEAD = LEA.CODLEAD
1149--- B29894 - Sia Leads che interviste vengono conteggiate secondo lo sla "L"
1150 --AND LSR.CODSLAEVENT = 'L'
1151 AND LSR.CODSLAEVENT = DECODE(NVL(LEA.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
1152 AND LSR.SLALIMIT > x_checkDate ) , 'R' )
1153 END AS STATUS,
1154--- T12363 - la funzione getworkinghours e' lenta per differenze di tempo elevate
1155--- se piu' di un mese si torna -1
1156 CASE WHEN LEA.CREATEDDATE < ADD_MONTHS(SYSDATE,-1) THEN
1157 -1
1158 ELSE
1159--- T12851 - Gestione orari di lavoro differenti per AfterSales ('A')
1160 ROUND ( ( SELECT pkholidays.getworkinghours
1161 ( CAST(LEA.CREATEDDATE as DATE),
1162 CAST(SYSTIMESTAMP AT TIME ZONE TO_CHAR(LEA.CREATEDDATE,'TZR') AS DATE),
1163 p_codMarket,
1164 CASE WHEN LEA.OBJTYPE = 'A' THEN 'A' ELSE 'L' END,
1165 v_codNation
1166 ) FROM DUAL ) , 2 )
1167 END as HOURS,
1168--- T8300 - per i campi di tipo TIMESTAMP, occorre una conversione
1169 TO_CHAR(LEA.LASTMODIFIEDDATE,'YYYY-MM-DD HH24:MI:SS') as LASTMODIFIEDDATE,
1170 TO_CHAR(LEA.CREATEDDATE,'YYYY-MM-DD HH24:MI:SS') as CREATEDDATE,
1171 TO_CHAR(LEA.INSERTIONDATE,'YYYY-MM-DD HH24:MI:SS') as INSERTIONDATE,
1172 ( SELECT LCS.DESCRIPTION
1173 FROM LDD_CAMPAIGN_SOURCES LCS
1174 WHERE LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE) as LEAD_SOURCE,
1175 LDT.PRIORITY,
1176 ( SELECT LTY.DESCRIPTION
1177 FROM LDD_CAMPAIGN_TYPES LTY
1178 WHERE LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE) as TYPE_DESC,
1179 LDT.CODBRAND,
1180 LCP.ISCENTRAL,
1181 -- T11796 phone lead brand Unknown
1182 CASE
1183 WHEN NVL(p_objtype,'L') = 'P' THEN (SELECT mk.codbrand
1184 FROM ldd_lead_hub_masterkey lhm
1185 LEFT JOIN ldd_hub_masterkey mk
1186 ON lhm.codmasterkey = mk.codmasterkey
1187 where lhm.codlead = lea.codlead)
1188 ELSE (select BR.BRANDNAME
1189 from BRAND BR
1190 where BR.CODBRAND = LDT.CODBRAND)
1191 END as BRAND,
1192 ( SELECT LDP.DESCRPRODUCT
1193 FROM LDD_PRODUCT LDP
1194 WHERE LDP.CODLEAD = LEA.CODLEAD ) AS COMMERCIALMODEL,
1195 LEA.CODCONTACT,
1196 CASE WHEN CT.CODCONTACTTYPE IN ('P', 'R') THEN
1197
1198 ( SELECT PRS.LASTNAME || ' ' || PRS.FIRSTNAME
1199 FROM PERSON PRS
1200 WHERE PRS.CODPERSON = CT.CODCONTACT )
1201
1202 WHEN CT.CODCONTACTTYPE IS NOT NULL THEN
1203
1204 ( SELECT CPY.COMPANYNAME
1205 FROM COMPANY CPY
1206 WHERE CPY.CODCOMPANY = CT.CODCONTACT )
1207 ELSE
1208 NULL --- Contatto Nullo --> Puo' succedere per Phone lead
1209 END AS CONTACT,
1210 ( SELECT CTY.DESCR_CITY
1211 FROM CITY CTY
1212 WHERE CTY.CODCITY = CT.CODCITY
1213 AND CTY.CODDEPARTMENT = CT.CODDEPARTMENT
1214 AND CTY.CODREGION = CT.CODREGION
1215 AND CTY.CODNATION = CT.CODNATION) as CITY,
1216-- T7839 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
1217-- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
1218 pkLDD_LEADS.getProperPhoneNumber(
1219 p_codMarket,
1220 LEA.CODLEADSTATUS,
1221 LEA.INSERTIONDATE,
1222 LEA.MOBILEPHONE,
1223 LEA.PHONENUMBER,
1224 TBK.CODLEAD,
1225 TBK.DATE_SENT,
1226 TBK.PIN_MOBILE,
1227 TBK.PIN_PHONENUMBER,
1228 TBK.TB_RESULT,
1229 TBK.TB_STATUS,
1230 v_checkMinutesPin,
1231 LEA.COMMUNICATIONCHANNEL,
1232 CT.EMAIL_ADDRESS) AS PHONENUMBER,
1233-- T7839 - Un flag indica se il numero di telefono riguarda un PIN
1234-- T14290 - Refactoring del calcolo relativo al Pin. Ora il calcolo ? centralizzato
1235 pkLDD_LEADS.checkIsPin(
1236 p_codMarket,
1237 LEA.CODLEADSTATUS,
1238 LEA.INSERTIONDATE,
1239 nvl(TBK.CODLEAD,LEA.CODLEAD),
1240 TBK.DATE_SENT,
1241 TBK.TB_RESULT,
1242 TBK.TB_STATUS,
1243 v_checkMinutesPin,
1244 LEA.COMMUNICATIONCHANNEL) AS ISPIN,
1245 dloc.town_name as SITE,
1246 dloc.address as address,
1247 LCP.CODCAMPAIGN ,
1248 LCP.CODCAMPAIGNSOURCE ,
1249 LCP.CODCAMPAIGNTYPE ,
1250 LCP.NAMECAMPAIGN ,
1251 LCP.STARTDATE ,
1252 LCP.ENDDATE ,
1253 LCP.CODCAMPAIGNEXT ,
1254 LCP.ISCENTRAL ,
1255 LCP.CREATIONDATE ,
1256 ( SELECT ELU.FIRSTNAME ||' '|| ELU.LASTNAME
1257 FROM ELINKUSER ELU
1258 WHERE ELU.LOGINNAME = LEA.USERGID ) as USERGID_NAME,
1259 LEA.UNTREATED,
1260 LEA.CALLCENTER_CALLBACK,
1261 pkLDD_LEADS.isUnmanagedToMask(
1262 LEA.CODLEAD,
1263 LDT.CODMARKET
1264 ) AS ISUNMANAGEDTOMASK,
1265 LUC.ICON,
1266 LEA.OBJTYPE,
1267 LUC.DESCRIPTION,
1268 LUC.CODUNDECIDEDCATEGORY,
1269 TBK.TB_RESULT,
1270 CT.EMAIL_ADDRESS,
1271 -- T11421 - Numero di trackback letto da tabella NATIONLANGUAGE (anziche' MARKETLANGUAGE)
1272 -- Lingua = lingua della lead, se specificata e configurata per la nazione, altrimenti si
1273 -- utilizza la lingua principale del mercato.
1274 -- Nazione = nazione del dealer, se specificata, altrimenti si utilizza la
1275 -- nazione del principale del mercato.
1276 ( SELECT NL.LDD_TRACKBACK_NUMBER
1277 FROM NATIONLANGUAGE NL
1278 WHERE NL.CODNATION = NVL ((SELECT d.codnation FROM dealer d
1279 WHERE d.codmarket = ldt.codmarket
1280 AND d.coddealer=ldt.coddealer),
1281 (SELECT mn.codnation FROM marketnation mn
1282 WHERE mn.codmarket = ldt.codmarket
1283 AND mn.ismain = 1) )
1284 AND NL.CODLANGUAGE = NVL ((SELECT nl.codlanguage FROM nationlanguage nl
1285 WHERE nl.codlanguage=LEA.CODLANGUAGE
1286 AND nl.codnation = NVL ((SELECT d.codnation FROM dealer d
1287 WHERE d.codmarket = ldt.codmarket
1288 AND d.coddealer=ldt.coddealer),
1289 (SELECT mn.codnation FROM marketnation mn
1290 WHERE mn.codmarket = ldt.codmarket
1291 AND mn.ismain = 1) ) ),
1292 (SELECT m.codlanguage FROM market m
1293 WHERE m.codmarket = ldt.codmarket) ) ) AS LDD_TRACKBACK_NUMBER,
1294 LEA.CODLEADEXT,
1295 (select pkdict.tran(
1296 (select max(act.coddictionary) keep(dense_rank first order by ldh.creationdate desc, cac.codactioncontact desc)
1297 from ldd_leadhistory ldh
1298 join crm_actioncontact cac
1299 on cac.codactioncontact = ldh.codactioncontact
1300 join crm_action act
1301 on act.codaction = cac.codaction
1302 where ldh.codlead = LEA.codlead
1303 and ldh.codleadstatus = 'IG'
1304 ),
1305 p_codLanguage) from dual) AS MANAGEDACTION,
1306 (select pkdict.tran(
1307 (select max(act.coddictionary) keep(dense_rank first order by cac.planneddate asc, cac.codactioncontact asc)
1308 from ldd_leadhistory ldh
1309 join crm_actioncontact cac
1310 on cac.codactioncontact = ldh.codactioncontact
1311 join crm_action act
1312 on act.codaction = cac.codaction
1313 where ldh.codlead = LEA.codlead
1314 and ldh.codleadstatus = 'IG'
1315 and cac.codactionstatus <> 'C'
1316 and cac.codaction in (
1317 'AP',
1318 'CE',
1319 'RC',
1320 'SQ',
1321 'FU',
1322 'TD'
1323 )
1324 ),
1325 p_codLanguage) from dual) AS FIRSTPLANNEDACTION,
1326 BUA.BUSINESS_AREA as MBA,
1327 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
1328 LEA.COMMUNICATIONCHANNEL,
1329 BUA.MK_ISHOT as ISHOT, -- TRK campo ishot per mercato turchia
1330 PRO.PROMOTITLE as PROMODESC,
1331 MSTK.LVL1 as LEVEL1,
1332 MSTK.LVL2 as LEVEL2
1333 from LDD_CAMPAIGN_DETAILS LDT
1334--- T12222 - sfruttiamo la tabella TRG_LDD_LEAD, popolata con trigger
1335 JOIN TRG_LDD_LEAD TLL ON TLL.CODCAMPAIGNDETAIL = LDT.CODCAMPAIGNDETAIL
1336 join ldd_lead LEA on (LEA.CODLEAD = TLL.CODLEAD)
1337 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
1338 join LDD_CAMPAIGN_TYPES_SOURCE LTS
1339 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
1340 LTS.CODCAMPAIGNTYPE=LCP.CODCAMPAIGNTYPE )
1341 LEFT join CONTACT CT on (CT.CODCONTACT = LEA.CODCONTACT)
1342 left join dealerlocation dloc
1343 on (ldt.codmarket = dloc.codmarket and
1344 ldt.coddealer = dloc.coddealer and
1345 ldt.coddealerlocation = dloc.codlocation)
1346 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LEA.CODLEAD )
1347 LEFT JOIN LDD_UNDECIDED_CATEGORIES LUC ON (LUC.CODCAMPAINGSOURCE = LTS.CODCAMPAIGNSOURCE
1348 AND LUC.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE)
1349 left join (select ba.business_area as BUSINESS_AREA,
1350 ba.description as BUSINESS_AREA_DESCRIPTION,
1351 lh.codlead as BUSINESS_AREA_CODLEAD,
1352 h.ishot as MK_ISHOT
1353 from LDD_LEAD_HUB_MASTERKEY lh
1354 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
1355 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
1356 on BUA.BUSINESS_AREA_CODLEAD = TLL.CODLEAD
1357 --T16172
1358 LEFT JOIN LDD_PROMO PRO ON PRO.CODLEAD = LEA.CODLEAD
1359 left join (select
1360 h.level2 as LVL2,
1361 h.level1 as LVL1,
1362 lh.codlead as CODLEADMSTK
1363 from ldd_hub_masterkey h
1364 left join LDD_LEAD_HUB_MASTERKEY lh
1365 on lh.codmasterkey = h.codmasterkey) MSTK
1366 on MSTK.CODLEADMSTK = LEA.CODLEAD
1367 where LDT.CODMAINDEALER = p_codMainDealer
1368 AND LDT.CODMARKET = p_codMarket
1369 AND ( -- T12152
1370 (LEA.OBJTYPE = NVL(p_objtype,'L') AND p_objtype <> 'P') --- Objtype di default => 'L'
1371 OR
1372 (LEA.objtype = p_objtype AND p_objtype = 'P' AND tbk.mngd_only_on_tb='N')
1373 )
1374-- B29149 SF campaigns sometimes don't have CODDEALERLOCATION defined, 'cause they don't need it.
1375-- So we put dealerlocation in left and strech the join only if objtype = L (Lead)
1376 AND (
1377 (NVL(p_objtype,'L') = 'L' AND LDT.CODDEALERLOCATION is NOT NULL)
1378 OR
1379 NVL(p_objtype,'L') != 'L'
1380 )
1381 AND ( LEA.USERGID = p_usergid OR p_usergid is NULL )
1382 AND LTS.ISDELETED=0
1383 AND ( p_codLeadStatus IS NULL OR TLL.CODLEADSTATUS = p_codLeadStatus )
1384 AND TLL.CODLEADSTATUS in ('IG', 'DG')
1385--- Solo per un set di Campagne? O per tutte?
1386 AND ( v_noFilterInclude = 1
1387 OR
1388 LCP.CODCAMPAIGNSOURCE in
1389 ( SELECT * FROM TABLE ( CAST (v_IncludeCodCampaignTable as LISTOFVALUEC)))
1390 )
1391--- Devo escludere un set di Campagne?
1392 AND ( v_noFilterExclude = 1
1393 OR
1394 LCP.CODCAMPAIGNSOURCE not in
1395 ( SELECT * FROM TABLE ( CAST (v_excludeCodCampaignTable as LISTOFVALUEC)))
1396 )
1397 order by HOURS DESC;
1398
1399 return myCursor;
1400
1401end findMyLeadDetails;
1402
1403-- findMyLeadDetailsTB
1404-- B31249 - INSERTIONDATE in output
1405function findMyLeadDetailsTB
1406 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
1407 p_codMarket in ldd_campaign_details.codmarket%type,
1408 p_codLanguage in language.codlanguage%type,
1409 p_usergid in ldd_lead.usergid%type default null,
1410 p_campaignSource in CodCampaignSource,
1411 p_excludeCmpSource in CodCampaignSource,
1412 p_objtype in LDD_OBJTYPES.objtype%type := NULL,
1413 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE default NULL )
1414 return elinkTypes.ref_collection is
1415 myCursor elinkTypes.ref_collection;
1416 v_includeCodCampaignTable ListOfValueC ;
1417 v_excludeCodCampaignTable ListOfValueC ;
1418 v_noFilterInclude NUMBER;
1419 v_noFilterExclude NUMBER;
1420 x_checkDate CONSTANT DATE := SYSDATE;
1421 v_checkMinutesPin NUMBER;
1422 v_codNation NATION.CODNATION%TYPE;
1423BEGIN
1424-- ListOfValueC per l'array di campagne da Includere
1425 BEGIN
1426 v_IncludeCodCampaignTable := LISTOFVALUEC();
1427 for idx in p_campaignSource.first .. p_campaignSource.last
1428 loop
1429 v_IncludeCodCampaignTable.EXTEND;
1430 v_IncludeCodCampaignTable(idx) := p_campaignSource(idx);
1431 end loop;
1432 v_noFilterInclude := 0;
1433
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 v_noFilterInclude := 1;
1437 END;
1438
1439-- ListOfValueC per l'array di campagne da Escludere
1440 BEGIN
1441 v_excludeCodCampaignTable := LISTOFVALUEC();
1442
1443 for idx in p_excludeCmpSource.first .. p_excludeCmpSource.last
1444 loop
1445 v_excludeCodCampaignTable.EXTEND;
1446 v_excludeCodCampaignTable(idx) := p_excludeCmpSource(idx);
1447 end loop;
1448 v_noFilterExclude := 0;
1449
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452 v_noFilterExclude := 1;
1453 END;
1454
1455--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
1456 v_codNation := PKNATION.getNationForUser(p_codmarket,p_codMainDealer);
1457
1458 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',p_codMarket,1,0) into v_checkMinutesPin from dual;
1459 open myCursor for
1460 SELECT SEL001.CODLEAD,
1461 SEL001.USERGID,
1462 SEL001.CODLEADSTATUS,
1463 SEL001.STATUS,
1464 SEL001.CREATIONDATE,
1465 TO_CHAR(SEL001.LASTMODIFIEDDATE,'YYYY-MM-DD HH24:MI:SS') as LASTMODIFIEDDATE,
1466 TO_CHAR(SEL001.CREATEDDATE,'YYYY-MM-DD HH24:MI:SS') as CREATEDDATE,
1467 TO_CHAR(SEL001.INSERTIONDATE,'YYYY-MM-DD HH24:MI:SS') as INSERTIONDATE,
1468 SEL001.LEAD_SOURCE,
1469 SEL001.PRIORITY,
1470 SEL001.TYPE_DESC,
1471 SEL001.CODBRAND,
1472 SEL001.BRAND,
1473 SEL001.COMMERCIALMODEL,
1474 SEL001.CODCONTACT,
1475 SEL001.CONTACT,
1476 SEL001.CITY,
1477 SEL001.PHONENUMBER,
1478 SEL001.ISPIN,
1479 SEL001.SITE,
1480 SEL001.ADDRESS,
1481 SEL001.CODCAMPAIGN,
1482 SEL001.CODCAMPAIGNSOURCE,
1483 SEL001.CODCAMPAIGNTYPE,
1484 SEL001.NAMECAMPAIGN,
1485 SEL001.DESCRIPTION,
1486 SEL001.CODMODEL,
1487 SEL001.STARTDATE,
1488 SEL001.ENDDATE,
1489 SEL001.CODCAMPAIGNEXT,
1490 SEL001.ISCENTRAL,
1491 SEL001.USERGID_NAME,
1492 SEL001.UNTREATED,
1493 SEL001.CALLCENTER_CALLBACK,
1494 SEL001.ISUNMANAGEDTOMASK,
1495 SEL001.ICON,
1496 SEL001.OBJTYPE,
1497 SEL001.DESCRIPTION,
1498 SEL001.CODUNDECIDEDCATEGORY,
1499 SEL001.TB_RESULT,
1500 SEL001.HOURS,
1501--- T9115 - quanto manca al prossimo semafero ( che non sia rosso, tanto per fare casino )
1502 CASE WHEN SEL001.STATUS != 'R' THEN
1503 CASE WHEN SEL001.OBJTYPE != 'I' THEN
1504 (SELECT MIN(LSR.SLALIMIT)
1505 FROM LDD_LEADS_SLARANGES LSR
1506 WHERE LSR.CODLEAD = SEL001.CODLEAD
1507 --AND LSR.CODSLAEVENT = 'L'
1508 AND LSR.CODSLAEVENT = DECODE(NVL(SEL001.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
1509 AND LSR.SLALIMIT > SYSTIMESTAMP) - SYSTIMESTAMP
1510 ELSE
1511--- T9115 - per le interviste ci serve solo il semafero "O" ( pensa un po'... )
1512 (SELECT MIN(LSR.SLALIMIT)
1513 FROM LDD_LEADS_SLARANGES LSR
1514 WHERE LSR.CODLEAD = SEL001.CODLEAD
1515 --AND LSR.CODSLAEVENT = 'L'
1516 AND LSR.CODSLAEVENT = DECODE(NVL(SEL001.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
1517 AND LSR.SEVERITYLEVELFIELD = 'O'
1518 AND LSR.SLALIMIT > SYSTIMESTAMP) - SYSTIMESTAMP
1519 END
1520 ELSE
1521 NULL
1522 END AS DIFF_TO_NEXT_LIMIT,
1523 SEL001.EMAIL_ADDRESS,
1524 SEL001.LDD_TRACKBACK_NUMBER,
1525 SEL001.CODLEADEXT,
1526 SEL001.MANAGEDACTION,
1527 SEL001.FIRSTPLANNEDACTION,
1528 SEL001.MBA, SEL001.MBA_DESCR,
1529 SEL001.COMMUNICATIONCHANNEL,
1530 SEL001.ISHOT,
1531 SEL001.PROMODESC,
1532 SEL001.LEVEL1,
1533 SEL001.LEVEL2
1534 FROM ( select LEA.CODLEAD,
1535 LEA.USERGID,
1536 LEA.CODLEADSTATUS,
1537 CASE WHEN LEA.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
1538 LEA.FINAL_SEVERITYLEVELFIELD
1539 ELSE
1540 NVL( ( select MAX(LSR.SEVERITYLEVELFIELD)
1541 KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
1542 FROM LDD_LEADS_SLARANGES LSR
1543 WHERE LSR.CODLEAD = LEA.CODLEAD
1544 --AND LSR.CODSLAEVENT = 'L'
1545 AND LSR.CODSLAEVENT = DECODE(NVL(LEA.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
1546 AND LSR.SLALIMIT > x_checkDate ) , 'R' )
1547 END AS STATUS,
1548 LEA.LASTMODIFIEDDATE,
1549 LEA.CREATEDDATE,
1550 LEA.INSERTIONDATE,
1551 LEA.LEADSCORE,
1552 ( SELECT LCS.DESCRIPTION
1553 FROM LDD_CAMPAIGN_SOURCES LCS
1554 WHERE LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE) as LEAD_SOURCE,
1555 LDT.PRIORITY,
1556 ( SELECT LTY.DESCRIPTION
1557 FROM LDD_CAMPAIGN_TYPES LTY
1558 WHERE LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE ) as TYPE_DESC,
1559 LDT.CODBRAND,
1560 -- T11796 phone lead brand Unknown
1561 CASE
1562 WHEN NVL(p_objtype,'L') = 'P' THEN (SELECT mk.codbrand
1563 FROM ldd_lead_hub_masterkey lhm
1564 LEFT JOIN ldd_hub_masterkey mk
1565 ON lhm.codmasterkey =
1566 mk.codmasterkey
1567 where lhm.codlead =
1568 lea.codlead)
1569 ELSE (select BR.BRANDNAME
1570 from BRAND BR
1571 where BR.CODBRAND = LDT.CODBRAND)
1572 END as BRAND,
1573 LDP.DESCRPRODUCT AS COMMERCIALMODEL,
1574 LDP.CODMODEL,
1575 LEA.CODCONTACT,
1576 CASE WHEN CT.CODCONTACTTYPE IN ('P', 'R') THEN
1577
1578 ( SELECT PRS.LASTNAME || ' ' || PRS.FIRSTNAME
1579 FROM PERSON PRS
1580 WHERE PRS.CODPERSON = CT.CODCONTACT )
1581
1582 WHEN CT.CODCONTACTTYPE IS NOT NULL THEN
1583
1584 ( SELECT CPY.COMPANYNAME
1585 FROM COMPANY CPY
1586 WHERE CPY.CODCOMPANY = CT.CODCONTACT )
1587 ELSE
1588 NULL --- Contatto Nullo --> Puo' succedere per Phone lead
1589 END AS CONTACT,
1590 CASE WHEN CT.CODCITY IS NOT NULL THEN
1591 ( SELECT CTY.DESCR_CITY
1592 FROM CITY CTY
1593 WHERE CTY.CODCITY = CT.CODCITY
1594 AND CTY.CODDEPARTMENT = CT.CODDEPARTMENT
1595 AND CTY.CODREGION = CT.CODREGION
1596 AND CTY.CODNATION = CT.CODNATION)
1597 ELSE
1598 NULL
1599 END as CITY,
1600-- T7839 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
1601-- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
1602 pkLDD_LEADS.getProperPhoneNumber(
1603 p_codMarket,
1604 LEA.CODLEADSTATUS,
1605 LEA.INSERTIONDATE,
1606 LEA.MOBILEPHONE,
1607 LEA.PHONENUMBER,
1608 TBK.CODLEAD,
1609 TBK.DATE_SENT,
1610 TBK.PIN_MOBILE,
1611 TBK.PIN_PHONENUMBER,
1612 TBK.TB_RESULT,
1613 TBK.TB_STATUS,
1614 v_checkMinutesPin,
1615 LEA.COMMUNICATIONCHANNEL,
1616 CT.EMAIL_ADDRESS) AS PHONENUMBER,
1617 -- T7839 - Un flag indica se il numero di telefono riguarda un PIN
1618-- T14290 - Refactoring del calcolo relativo al Pin. Ora il calcolo ? centralizzato
1619 pkLDD_LEADS.checkIsPin(
1620 p_codMarket,
1621 LEA.CODLEADSTATUS,
1622 LEA.INSERTIONDATE,
1623 nvl(TBK.CODLEAD,LEA.CODLEAD),
1624 TBK.DATE_SENT,
1625 TBK.TB_RESULT,
1626 TBK.TB_STATUS,
1627 v_checkMinutesPin,
1628 LEA.COMMUNICATIONCHANNEL) AS ISPIN,
1629 ( SELECT dloc.town_name
1630 FROM dealerlocation dloc
1631 WHERE ldt.codmarket = dloc.codmarket
1632 and ldt.coddealer = dloc.coddealer
1633 and ldt.coddealerlocation = dloc.codlocation) as SITE,
1634 ( SELECT dloc.address
1635 FROM dealerlocation dloc
1636 WHERE ldt.codmarket = dloc.codmarket
1637 and ldt.coddealer = dloc.coddealer
1638 and ldt.coddealerlocation = dloc.codlocation) as address,
1639 LCP.CODCAMPAIGN ,
1640 LCP.CODCAMPAIGNSOURCE ,
1641 LCP.CODCAMPAIGNTYPE ,
1642 LCP.NAMECAMPAIGN ,
1643 LCP.STARTDATE ,
1644 LCP.ENDDATE ,
1645 LCP.CODCAMPAIGNEXT ,
1646 LCP.ISCENTRAL ,
1647 LCP.CREATIONDATE ,
1648 ( SELECT ELU.FIRSTNAME || ' ' || ELU.LASTNAME
1649 FROM ELINKUSER ELU
1650 WHERE ELU.LOGINNAME = LEA.USERGID ) as USERGID_NAME,
1651 LEA.UNTREATED,
1652 LEA.CALLCENTER_CALLBACK,
1653 pkLDD_LEADS.isUnmanagedToMask(
1654 LEA.CODLEAD,
1655 LDT.CODMARKET
1656 ) AS ISUNMANAGEDTOMASK,
1657 LUC.ICON,
1658 LEA.OBJTYPE, --T13729
1659 LUC.DESCRIPTION,
1660 LUC.CODUNDECIDEDCATEGORY,
1661 TBK.TB_RESULT,
1662--- T12363 - la funzione getworkinghours e' lenta per differenze di tempo elevate
1663--- se piu' di un mese si torna -1
1664 CASE WHEN LEA.CREATEDDATE < ADD_MONTHS(SYSDATE,-1) THEN
1665 -1
1666 ELSE
1667 ROUND ( ( SELECT pkholidays.getworkinghours
1668 ( CAST(LEA.CREATEDDATE as DATE),
1669 CAST(SYSTIMESTAMP AT TIME ZONE TO_CHAR(LEA.CREATEDDATE,'TZR') AS DATE),
1670 p_codMarket,
1671 CASE WHEN LEA.OBJTYPE = 'A' THEN 'A' ELSE 'L' END,
1672 v_codNation
1673 ) FROM DUAL ) , 2 )
1674 END as HOURS,
1675 CT.EMAIL_ADDRESS,
1676 ( SELECT pknationlanguage.gettrackbacknumber ( ldt.codmarket,
1677 ldt.coddealer,
1678 LEA.CODLANGUAGE) from DUAL ) AS LDD_TRACKBACK_NUMBER,
1679 LEA.CODLEADEXT,
1680 (select pkdict.tran(
1681 (select max(act.coddictionary) keep(dense_rank first order by ldh.creationdate desc, cac.codactioncontact desc)
1682 from ldd_leadhistory ldh
1683 join crm_actioncontact cac
1684 on cac.codactioncontact = ldh.codactioncontact
1685 join crm_action act
1686 on act.codaction = cac.codaction
1687 where ldh.codlead = LEA.codlead
1688 and ldh.codleadstatus = 'IG'
1689 ),
1690 p_codLanguage) from dual) AS MANAGEDACTION,
1691 (select pkdict.tran(
1692 (select max(act.coddictionary) keep(dense_rank first order by cac.planneddate asc, cac.codactioncontact asc)
1693 from ldd_leadhistory ldh
1694 join crm_actioncontact cac
1695 on cac.codactioncontact = ldh.codactioncontact
1696 join crm_action act
1697 on act.codaction = cac.codaction
1698 where ldh.codlead = LEA.codlead
1699 and ldh.codleadstatus = 'IG'
1700 and cac.codactionstatus <> 'C'
1701 and cac.codaction in (
1702 'AP',
1703 'CE',
1704 'RC',
1705 'SQ',
1706 'FU',
1707 'TD'
1708 )
1709 ),
1710 p_codLanguage) from dual) AS FIRSTPLANNEDACTION,
1711 BUA.BUSINESS_AREA as MBA,
1712 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
1713 LEA.COMMUNICATIONCHANNEL,
1714 BUA.MK_ISHOT as ISHOT, -- TRK campo ishot per mercato turchia
1715 PRO.PROMOTITLE as PROMODESC,
1716 MSTK.LVL1 as LEVEL1,
1717 MSTK.LVL2 as LEVEL2
1718 from LDD_CAMPAIGN_DETAILS LDT
1719 join ldd_lead LEA on (LDT.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL)
1720 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
1721 join LDD_CAMPAIGN_TYPES_SOURCE LTS
1722 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
1723 LTS.CODCAMPAIGNTYPE=LCP.CODCAMPAIGNTYPE )
1724 LEFT join CONTACT CT on (CT.CODCONTACT = LEA.CODCONTACT)
1725 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LEA.CODLEAD )
1726 LEFT JOIN LDD_UNDECIDED_CATEGORIES LUC
1727 ON (LUC.CODCAMPAINGSOURCE = LTS.CODCAMPAIGNSOURCE AND
1728 LUC.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE)
1729 LEFT JOIN LDD_PRODUCT LDP ON LDP.CODLEAD = LEA.CODLEAD
1730 left join (select ba.business_area as BUSINESS_AREA,
1731 ba.description as BUSINESS_AREA_DESCRIPTION,
1732 lh.codlead as BUSINESS_AREA_CODLEAD,
1733 h.ishot as MK_ISHOT
1734 from LDD_LEAD_HUB_MASTERKEY lh
1735 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
1736 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
1737 on BUA.BUSINESS_AREA_CODLEAD = LEA.CODLEAD
1738 --T16172
1739 LEFT JOIN LDD_PROMO PRO ON PRO.CODLEAD = LEA.CODLEAD
1740 left join (select
1741 h.level2 as LVL2,
1742 h.level1 as LVL1,
1743 lh.codlead as CODLEADMSTK
1744 from ldd_hub_masterkey h
1745 left join LDD_LEAD_HUB_MASTERKEY lh
1746 on lh.codmasterkey = h.codmasterkey) MSTK
1747 on MSTK.CODLEADMSTK = LEA.CODLEAD
1748 where LDT.CODMAINDEALER = p_codMainDealer
1749 AND LDT.CODMARKET = p_codMarket
1750 AND ( -- T12152
1751 (LEA.OBJTYPE = NVL(p_objtype,'L') AND p_objtype <> 'P') --- Objtype di default => 'L'
1752 OR
1753 (LEA.objtype = p_objtype AND p_objtype = 'P' AND tbk.mngd_only_on_tb='N')
1754 )
1755-- B29149 SF campaigns sometimes don't have CODDEALERLOCATION defined, 'cause they don't need it.
1756-- So we put dealerlocation in left and strech the join only if objtype = L (Lead)
1757 AND (
1758 (NVL(p_objtype,'L') = 'L' AND LDT.CODDEALERLOCATION is NOT NULL)
1759 OR
1760 NVL(p_objtype,'L') != 'L'
1761 )
1762 AND ( LEA.USERGID = p_usergid OR p_usergid is NULL )
1763 AND LTS.ISDELETED=0
1764 AND ( p_codLeadStatus IS NULL OR LEA.CODLEADSTATUS = p_codLeadStatus )
1765 AND LEA.CODLEADSTATUS in ('IG', 'DG')
1766--- Gestion set dei campagne ( da includere o da escludere )
1767 AND ( v_noFilterInclude = 1
1768 OR
1769 LCP.CODCAMPAIGNSOURCE in
1770 ( SELECT * FROM TABLE ( CAST (v_IncludeCodCampaignTable as LISTOFVALUEC)))
1771 )
1772 AND ( v_noFilterExclude = 1
1773 OR
1774 LCP.CODCAMPAIGNSOURCE not in
1775 ( SELECT * FROM TABLE ( CAST (v_excludeCodCampaignTable as LISTOFVALUEC)))
1776 )
1777 ) SEL001
1778 ORDER BY DIFF_TO_NEXT_LIMIT ASC NULLS LAST ,
1779 SEL001.INSERTIONDATE ;
1780
1781 return myCursor;
1782
1783end findMyLeadDetailsTB;
1784
1785-- Public findLeadManageAllLogin
1786--- B35425 - performances
1787--
1788-- Restituisce Login, Nome ed il numero delle Leads assegnate
1789-- delle utenze del MainDealer cui posso assegnare delle lead
1790-- (per il box di gestione dei PRIVILEGI del CRM)
1791function findLeadManageAllLogin
1792 (p_codMainDealer in ldd_campaign_details.codmaindealer%type,
1793 p_codMarket in ldd_campaign_details.codmarket%type,
1794 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
1795 return elinkTypes.ref_collection is
1796 myCursor elinkTypes.ref_collection;
1797BEGIN
1798 open myCursor for
1799 select elu.loginname as LOGIN,
1800 elu.firstname || ' ' || elu.lastname as SALESMAN,
1801 elu.lastname,
1802 (select count(ll.codlead)
1803 from LDD_LEAD ll
1804 join ldd_campaign_details cd on (ll.codcampaigndetail = cd.codcampaigndetail)
1805 where cd.codmarket = p_codMarket
1806 and cd.codmaindealer = p_codMainDealer
1807 and ll.objtype = NVL(p_objtype,'L')
1808 and ll.codleadstatus = 'DG'
1809 and ll.usergid = elu.loginname) as ASSIGNED_LEAD
1810 from elinkuser elu
1811 WHERE ELU.usertype = 1
1812 and NVL(elu.deleted, 0) = 0
1813 and elu.lastname is not null
1814 AND ELU.LOGINNAME IN
1815--- B35425 - la ricerca viene interamente effettuata su elinkuserlinkmarket
1816 ( SELECT ELM.LOGINNAME
1817 FROM elinkuserlinkmarket elm
1818 where elM.codmarket = p_codMarket
1819 and elm.codlink > 0
1820 and NVL(elm.isdeleted, 0) = 0
1821--- Simplified? login - lo user potrebbe servire piu' padroni
1822 and exists (select 1
1823 from v_elinkusers veu
1824 where veu.loginname = elm.loginname
1825 and veu.codmarket = p_codMarket
1826 and veu.CODMAINDEALER = p_codMainDealer)
1827 and ( ( SELECT pkcrm_availability.findRoleParam(p_codMarket, elm.rolecode, 'LEAD_MANAGER')
1828 FROM DUAL ) = 1
1829--eliminare post 3.0
1830 or elm.rolecode = 'LINKCRM_USER'
1831 )
1832 )
1833 order by elu.lastname,elu.firstname ASC;
1834
1835 return myCursor;
1836
1837end findLeadManageAllLogin;
1838function findLeadPrivsLogin(p_codMainDealer in ldd_campaign_details.codmaindealer%type,
1839 p_codMarket in ldd_campaign_details.codmarket%type)
1840 return elinkTypes.ref_collection is
1841 myCursor elinkTypes.ref_collection;
1842BEGIN
1843
1844 IF p_codmarket = '3239' THEN
1845 open myCursor for
1846 select distinct elu.loginname as LOGIN,
1847 elu.firstname || ' ' || elu.lastname as SALESMAN,
1848 elu.lastname,
1849 (select pkcrm_availability.findLoginParam(p_codMarket,
1850 elu.loginname,
1851 'CRM_LEAD_HANDLER',
1852 p_codMainDealer) from dual) as CAN_HND_SALES,
1853 (select pkcrm_availability.findLoginParam(p_codMarket,
1854 elu.loginname,
1855 'CRM_LEAD_HANDLER_AS',
1856 p_codMainDealer)from dual) as CAN_HND_ASALES,
1857 (select pkcrm_availability.findLoginParam(p_codMarket,
1858 elu.loginname,
1859 'CRM_LEAD_MANAGER',
1860 p_codMainDealer) from dual) as CAN_MNG_SALES,
1861 (select pkcrm_availability.findLoginParam(p_codMarket,
1862 elu.loginname,
1863 'CRM_LEAD_MANAGER_AS',
1864 p_codMainDealer)from dual) as CAN_MNG_ASALES,
1865 (select count(ll.codlead)
1866 from ldd_lead ll
1867 join ldd_campaign_details cd
1868 on (ll.codcampaigndetail = cd.codcampaigndetail)
1869 where cd.codmarket = p_codMarket
1870 and cd.codmaindealer = p_codMainDealer
1871 and ll.objtype in ('A')
1872 and ll.codleadstatus = 'DG'
1873 and ll.usergid = elu.loginname) as ASSIGNED_LEAD_AFTERSALES,
1874 (select count(ll.codlead)
1875 from ldd_lead ll
1876 join ldd_campaign_details cd
1877 on (ll.codcampaigndetail = cd.codcampaigndetail)
1878 where cd.codmarket = p_codMarket
1879 and cd.codmaindealer = p_codMainDealer
1880 and ll.objtype in ('L','P','C')
1881 and ll.codleadstatus = 'DG'
1882 and ll.usergid = elu.loginname) as ASSIGNED_LEAD_SALES
1883 from elinkuser elu
1884 join elinkuserlinkmarket elm
1885 on elu.loginname = elm.loginname
1886 AND elu.codmarket = elm.codmarket
1887 join link l
1888 on l.codlink = elm.codlink
1889 where elu.codmarket = p_codMarket
1890 and elu.usertype = 1
1891 and NVL(elu.deleted,
1892 0) = 0
1893 --- Simplified? login - lo user potrebbe servire piu' padroni
1894 and exists (select 1
1895 from v_elinkusers veu
1896 where veu.loginname = elu.loginname
1897 and veu.codmarket = p_codMarket
1898 and veu.CODMAINDEALER = p_codMainDealer)
1899 and elm.codlink > 0
1900 and l.name = 'CRM' -- per Turchia bsata che abbia l'applicazione CRM
1901 and NVL(elm.isdeleted,
1902 0) = 0
1903 and elu.lastname is not null
1904 order by elu.lastname ASC;
1905 ELSE
1906 open myCursor for
1907 select distinct elu.loginname as LOGIN,
1908 elu.firstname || ' ' || elu.lastname as SALESMAN,
1909 elu.lastname,
1910 (select pkcrm_availability.findLoginParam(p_codMarket,
1911 elu.loginname,
1912 'CRM_LEAD_HANDLER',
1913 p_codMainDealer) from dual) as CAN_HND_SALES,
1914 (select pkcrm_availability.findLoginParam(p_codMarket,
1915 elu.loginname,
1916 'CRM_LEAD_HANDLER_AS',
1917 p_codMainDealer)from dual) as CAN_HND_ASALES,
1918 (select pkcrm_availability.findLoginParam(p_codMarket,
1919 elu.loginname,
1920 'CRM_LEAD_MANAGER',
1921 p_codMainDealer) from dual) as CAN_MNG_SALES,
1922 (select pkcrm_availability.findLoginParam(p_codMarket,
1923 elu.loginname,
1924 'CRM_LEAD_MANAGER_AS',
1925 p_codMainDealer)from dual) as CAN_MNG_ASALES,
1926 (select count(ll.codlead)
1927 from ldd_lead ll
1928 join ldd_campaign_details cd
1929 on (ll.codcampaigndetail = cd.codcampaigndetail)
1930 where cd.codmarket = p_codMarket
1931 and cd.codmaindealer = p_codMainDealer
1932 and ll.objtype in ('A')
1933 and ll.codleadstatus = 'DG'
1934 and ll.usergid = elu.loginname) as ASSIGNED_LEAD_AFTERSALES,
1935 (select count(ll.codlead)
1936 from ldd_lead ll
1937 join ldd_campaign_details cd
1938 on (ll.codcampaigndetail = cd.codcampaigndetail)
1939 where cd.codmarket = p_codMarket
1940 and cd.codmaindealer = p_codMainDealer
1941 and ll.objtype in ('L','P','C')
1942 and ll.codleadstatus = 'DG'
1943 and ll.usergid = elu.loginname) as ASSIGNED_LEAD_SALES
1944 from elinkuser elu
1945 join elinkuserlinkmarket elm
1946 on elu.loginname = elm.loginname
1947 AND elu.codmarket = elm.codmarket
1948 where elu.codmarket = p_codMarket
1949 and elu.usertype = 1
1950 and NVL(elu.deleted,
1951 0) = 0
1952 --- Simplified? login - lo user potrebbe servire piu' padroni
1953 and exists (select 1
1954 from v_elinkusers veu
1955 where veu.loginname = elu.loginname
1956 and veu.codmarket = p_codMarket
1957 and veu.CODMAINDEALER = p_codMainDealer)
1958 and (pkcrm_availability.findRoleParam(p_codMarket,
1959 elm.rolecode,
1960 'LEAD_MANAGER') = 1
1961 --eliminare post 3.0
1962 or elm.rolecode = 'LINKCRM_USER'
1963 )
1964 and elm.codlink > 0
1965 and NVL(elm.isdeleted,
1966 0) = 0
1967 and elu.lastname is not null
1968 order by elu.lastname ASC;
1969 END IF;
1970
1971 return myCursor;
1972
1973end findLeadPrivsLogin;
1974
1975-- Restituisce Login, Nome ed il numero delle Leads assegnate
1976-- delle utenze del MainDealer cui posso assegnare delle lead
1977-- (per il box Leads To Assign del CRM)
1978function findLeadManageLogin
1979 ( p_codMainDealer in ldd_dwh_fieldcounter.codmaindealer%type,
1980 p_codMarket in ldd_dwh_fieldcounter.codmarket%type,
1981 p_leadApplType in ldd_objtypes.linkappltype%type := NULL)
1982 return elinkTypes.ref_collection is
1983 myCursor elinkTypes.ref_collection;
1984BEGIN
1985 open myCursor for
1986 select velu.loginname as LOGIN,
1987 velu.firstname || ' ' || velu.lastname as SALESMAN,
1988 ( select sum(ldfc.counter) as LEAD
1989 from ldd_dwh_fieldcounter ldfc
1990--- B35746 - aggiunto Contatore fca capital ("Q")
1991---t13681 aggiungo contatore lead flotte
1992 where ldfc.codfieldcount IN ( 'M', 'Q', 'G' )
1993 and ldfc.codmarket = p_codMarket
1994 and ldfc.codmaindealer = p_codMainDealer
1995 and ldfc.usergid = velu.loginname ) as ASSIGNED_LEAD,
1996 ( select sum(ldfc.counter) as INTERVIEW_SF
1997 from ldd_dwh_fieldcounter ldfc
1998 where ldfc.codfieldcount = 'S'
1999 and ldfc.codmarket = p_codMarket
2000 and ldfc.codmaindealer = p_codMainDealer
2001 and ldfc.usergid = velu.loginname ) as ASSIGNED_INTERVIEW_SF
2002 FROM
2003 --CR4721 simplified Login:
2004 v_elinkusers velu
2005 where NVL(velu.deleted,0)=0
2006 and velu.CODMAINDEALER = p_codMainDealer
2007 and velu.codmarket = p_codMarket
2008 --- T10021 - controllo anche abilitazione aftersales
2009 AND (testCrmLeadHandler(p_codMarket, velu.loginname, p_codMainDealer, p_leadApplType) = 1)
2010 ORDER BY 2,1;
2011
2012 return myCursor;
2013end findLeadManageLogin;
2014
2015-- Verifica se l'utente puo' gestire esales, aftersales o entrambi,
2016-- in base al leadApplType fornito.
2017--
2018-- T12683
2019function testCrmLeadHandler(p_codMarket in ldd_dwh_fieldcounter.CODMARKET%type,
2020 p_loginName in v_elinkusers.LOGINNAME%type,
2021 p_codMainDealer in ldd_dwh_fieldcounter.CODMAINDEALER%type,
2022 p_leadApplType in ldd_objtypes.LINKAPPLTYPE%type)
2023 return NUMBER is
2024 v_result NUMBER;
2025BEGIN
2026 v_result := 0;
2027
2028 -- per mantenere compatibilita' con CRM base
2029 IF (p_leadApplType IS NULL AND
2030 (pkcrm_availability.findLoginParam(p_codMarket,p_loginName,'CRM_LEAD_HANDLER',p_codMainDealer) = 1
2031 OR pkcrm_availability.findLoginParam(p_codMarket,p_loginName,'CRM_LEAD_HANDLER_AS',p_codMainDealer) = 1))
2032 THEN
2033 v_result := 1;
2034
2035 -- p_leadApplType di esales
2036 ELSIF (p_leadApplType = 1 AND
2037 (pkcrm_availability.findLoginParam(p_codMarket,p_loginName,'CRM_LEAD_HANDLER',p_codMainDealer) = 1))
2038 THEN
2039 v_result := 1;
2040
2041 -- p_leadApplType di aftersales
2042 ELSIF p_leadApplType = 2 AND
2043 (pkcrm_availability.findLoginParam(p_codMarket,p_loginName,'CRM_LEAD_HANDLER_AS',p_codMainDealer) = 1)
2044 THEN
2045 v_result := 1;
2046 END IF;
2047
2048 return v_result;
2049end testCrmLeadHandler;
2050
2051 -- Public countSalesmenCampaignLead
2052 --
2053 -- Restituisce Login, Nome ed il numero delle Leads assegnate
2054 -- delle utenze del MainDealer cui posso assegnare delle lead
2055 -- (per il box Leads To Assign del CRM)
2056 function countSalesmenCampaignLead( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
2057 p_codMarket in ldd_campaign_details.codmarket%type,
2058 p_codcampaignsource in ldd_campaign.codcampaignsource%type,
2059 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
2060 return elinkTypes.ref_collection is
2061 myCursor elinkTypes.ref_collection;
2062 begin
2063 open myCursor for
2064
2065 select count(codlead)as tot_lead, usergid
2066 from ldd_lead l
2067 join ldd_campaign_details lcd on (l.codcampaigndetail=lcd.codcampaigndetail)
2068 join ldd_campaign lc on (lcd.codcampaign=lc.codcampaign)
2069 where lc.codcampaignsource= p_codcampaignsource
2070 and lcd.codmaindealer = p_codMainDealer
2071 and lcd.codmarket = p_codmarket
2072 AND l.objtype = NVL(p_objtype,'L') /*EXISTS ( SELECT 1 FROM LDD_CAMPAIGN_TYPES_SOURCE LTS
2073 WHERE LTS.CODCAMPAIGNSOURCE = LC.CODCAMPAIGNSOURCE
2074 AND LTS.CODCAMPAIGNTYPE = LC.CODCAMPAIGNTYPE
2075 AND )*/
2076 and l.usergid is not null
2077 and l.codleadstatus in ('IG','DG')
2078 group by usergid;
2079
2080 return myCursor;
2081 end countSalesmenCampaignLead;
2082
2083-- Public assignLead
2084-- Assegna una lead ad una login, aggiornando anche il campo LASTMODIFIEDDATE
2085--- B29107 - La data di assegnazione di una lead, se non nulla, deve essere > createddate
2086function assignLead
2087 ( p_codLead in ldd_lead.codlead%type,
2088 p_usergid in ldd_lead.usergid%type,
2089 p_usergidAssigner in ELINKUSER.LOGINNAME%TYPE DEFAULT NULL,
2090 p_forceWait in number default 0,
2091 p_IsHot IN NUMBER DEFAULT NULL )
2092 return NUMBER is
2093 v_result number := 0;
2094 v_resultMail number := 0;
2095 v_idAlrService ALR_SERVICES.IDALR_SERVICE%type;
2096 v_leadCodMarket LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE;
2097 v_createdDate LDD_LEAD.CREATEDDATE%TYPE;
2098 v_sms number;
2099 v_codLeadStatus LDD_LEAD.CODLEADSTATUS%TYPE;
2100 v_ret number;
2101 v_waitPin number;
2102 v_currStatus LDD_LEAD.CODLEADSTATUS%TYPE := 'DA';
2103-- B32256
2104 v_alr_service alr_services.service_code%TYPE;
2105--B37160
2106 v_objType ldd_lead.objtype%TYPE;
2107BEGIN
2108 x_procedureName := 'pkLDD_LEADS.assignlead';
2109--- get Lead Informations. Se va male, la lead non esiste e si va in eccezione
2110 SELECT LCD.CODMARKET,
2111 LEA.CREATEDDATE,
2112 LEA.CODLEADSTATUS,
2113 CASE WHEN lea.objtype = 'A' THEN
2114 'NUOVALEAD_DG_AS'
2115 WHEN lea.objtype = 'P' THEN
2116 'NEWPLTOMANAGE'
2117 ELSE
2118 'NUOVALEADDAGESTIRE'
2119 END AS ALR_SERVICE,
2120 CASE WHEN lea.objtype = 'A' THEN
2121 ( SELECT IDalr_service
2122 FROM alr_services
2123 WHERE service_code = 'NUOVALEAD_DG_AS' )
2124 WHEN lea.objtype = 'P' THEN
2125 ( SELECT IDalr_service
2126 FROM alr_services
2127 WHERE service_code = 'NEWPLTOMANAGE' )
2128 ELSE
2129 ( SELECT IDalr_service
2130 FROM alr_services
2131 WHERE service_code = 'NUOVALEADDAGESTIRE' )
2132 END AS IDALR_SERVICE,
2133 LEA.OBJTYPE
2134 INTO v_leadCodMarket,
2135 v_createddate,
2136 v_currStatus,
2137 v_alr_service,
2138 v_idAlrService,
2139 v_objType
2140 FROM LDD_LEAD LEA
2141 JOIN LDD_CAMPAIGN_DETAILS LCD ON LCD.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL
2142 WHERE LEA.CODLEAD = p_codLead;
2143
2144--- cerco l'ultimo stato della LEAD che non sia 'DA' o 'DL' ( da assegnare o da leggere )
2145--- Se la lead non e' mai stata assegnata non trova nulla
2146--- se e' stata resettata, trova l'ultimo stato prima del reset ( DG o IG )
2147 BEGIN
2148 SELECT SEL001.CODLEADSTATUS
2149 INTO v_codLeadStatus
2150 FROM ( SELECT LDH.CODLEADSTATUS
2151 FROM LDD_LEADHISTORY LDH
2152 WHERE LDH.CODLEAD = p_codlead
2153 AND LDH.CODLEADSTATUS NOT IN ( 'DA','DL' )
2154 ORDER BY LDH.CREATIONDATE DESC
2155 ) SEL001
2156 WHERE ROWNUM < 2;
2157 EXCEPTION
2158 WHEN NO_DATA_FOUND THEN
2159 v_codLeadStatus := 'DG';
2160 END;
2161
2162--- B32182 - Nel caso di riassegnazione di una lead in stato 'IG' occorre passare prims dallo stato 'DG'
2163--- altrimenti la nuova user non viene inviata ad FGA-Capital.
2164 IF v_codLeadStatus = 'IG' THEN
2165 v_ret := pkldd_leads.updateleadstatus
2166 ( p_codlead => p_codlead,
2167 p_codleadstatus => 'DG',
2168 p_usergid => p_usergid,
2169 p_assignDate => GREATEST(SYSTIMESTAMP AT TIME ZONE to_char(v_createdDate,'TZR'),
2170 v_createdDate+NUMTODSINTERVAL(1,'second') ),
2171 p_codActionContact => NULL,
2172 p_logHandle => NULL);
2173 ELSE
2174 v_ret := 1;
2175 END IF;
2176
2177 --- T8957 - modifica "centralizzata" dello stato della lead
2178 --- p_lastModifiedDate => NULL vuol dire systimestamp
2179 IF v_ret = 1 THEN
2180 v_ret := pkldd_leads.updateleadstatus
2181 (p_codlead => p_codlead,
2182 p_codleadstatus => v_codLeadStatus,
2183 p_usergid => p_usergid,
2184 p_assignDate => GREATEST(SYSTIMESTAMP AT TIME ZONE to_char(v_createdDate,'TZR'),
2185 v_createdDate+NUMTODSINTERVAL(1,'second') ),
2186 p_codActionContact => NULL,
2187 p_logHandle => NULL);
2188
2189 END IF;
2190
2191--- Se la modifica dello stato e' fallita, di conseguenza fallisce l'assegnazione
2192--- Su logmaster/logc ci sono informazioni relative ad eventuali errori oracle
2193 IF v_ret != 1 THEN
2194 RETURN 0;
2195 END IF;
2196
2197 -- Nel caso di Phone Lead non risposte non assegnate in automatico, l'azione DA
2198 -- non pu? essere assegnata a nessuna User reale e si utilizza quindi il fittizio 'NA'
2199 -- Quando viene assegnata tramite assegnazione manuale viene associata la User corretta.
2200 -- Il contatto verr? associato al momento della qualificazione della Lead.
2201 UPDATE crm_actioncontact cac
2202 SET cac.usergid = p_usergid
2203 WHERE cac.codlead = p_codlead
2204 AND cac.usergid = 'NA'
2205 AND cac.codaction = 'DA';
2206
2207--- T12222 - in caso di avvenuta assegnazione si salva un record che indica
2208--- l'assegnatore
2209 IF p_usergidAssigner IS NOT NULL THEN
2210 BEGIN
2211 INSERT INTO LDD_LEAD_MANUALASSIGNED ( CODLEAD ,
2212 ASSIGNER_USERGID ,
2213 CREATIONDATE )
2214 VALUES ( p_codLead,
2215 p_usergidAssigner,
2216 SYSDATE );
2217 EXCEPTION
2218 WHEN DUP_VAL_ON_INDEX THEN
2219 UPDATE LDD_LEAD_MANUALASSIGNED MAS
2220 SET MAS.ASSIGNER_USERGID = p_usergidAssigner,
2221 MAS.CREATIONDATE = SYSDATE
2222 WHERE MAS.CODLEAD = p_codLead;
2223 END;
2224 END IF;
2225
2226-- Controlli post assegnazione per stabilire se inviare SMS
2227--forziamo attesa in caso di parametro esterno, esempio phone lead
2228if p_forceWait <> 0 then
2229 v_waitPin:=p_forceWait;
2230else
2231 v_waitPin := waitPin(p_codLead);
2232end if;
2233/* If pin hasn't been inserted yet, we leave the schedule of sms
2234* to the pkLDD_TRACKBACK package. B30318
2235* If the previous status of the lead was IG, we won't send it. B30755
2236*/
2237
2238 IF v_waitPin <> 1 AND v_currStatus <> 'IG' THEN
2239 BEGIN
2240/* -- T16016 - Disattivazione servizio SMS per Spagna tramite Artico
2241--- CR3934 - SMS per Spagna
2242--- Solo per Spagna eventuale inserimento in ALR_SERVICESQUEUE
2243 IF v_leadCodMarket = '3136' THEN
2244 v_sms := pkLDD_LEADS.notifyLeadAssigner(p_codLead,
2245 v_leadCodMarket,
2246 p_usergid,
2247 v_idAlrService);
2248 ELSE
2249 v_sms := pkSMSMANAGER.sendSms(p_codLead, v_alr_service);
2250 END IF;
2251*/
2252 v_sms := pkSMSMANAGER.sendSms(p_codLead, v_alr_service);
2253 EXCEPTION
2254 WHEN OTHERS THEN
2255 x_errCode := sqlcode;
2256 x_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
2257 PKLDD_LEADS.startLogManagement;
2258 pklog.writedetail(vloghandle => x_logHandle,
2259 llevel => PKLOG.WARNING,
2260 pprocedurename => x_procedureName,
2261 description => 'Non-blocking error',
2262 details => 'Error calling pkSMSMANAGER.sendSms for codlead ' ||
2263 p_codLead || ':' ||
2264 to_char(x_errCode));
2265 v_result := 1; -- update effettuato ma sms non mandato
2266 END;
2267 ELSE
2268 v_result := 1; -- Lead SF non si invia l'SMS
2269 END IF;
2270
2271/* If the pin hasn't been inserted yet, we leave the schedule of the email
2272* to the pkLDD_TRACKBACK package. B30318
2273* If the previous status of the lead was IG, we won't send it. B30755
2274*/
2275 IF v_waitPin <> 1 AND v_currStatus <> 'IG' THEN
2276--CR3851
2277 BEGIN
2278 -- T15175
2279 IF v_leadCodMarket <> '3239' THEN
2280 v_resultMail := pkALRSEND.sendMail(p_codLead, v_idAlrService);
2281 ELSE
2282 IF p_IsHot = 1 THEN
2283 v_resultMail := pkALRSEND.sendMail(p_codLead, v_idAlrService);
2284 END IF;
2285 END IF;
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 NULL;
2289 END;
2290 END IF;
2291
2292--we must return the assign result...not the alert result...
2293 RETURN 1;
2294
2295EXCEPTION
2296 WHEN OTHERS THEN
2297 x_errCode := sqlcode;
2298 x_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
2299 PKLDD_LEADS.startLogManagement;
2300 pklog.writedetail(vloghandle => x_logHandle,
2301 llevel => PKLOG.WARNING,
2302 pprocedurename => x_procedureName,
2303 description => 'Error : ' || to_char(x_errCode),
2304 details => x_errMsg);
2305--- perche' raise e return??? boh
2306 raise;
2307 RETURN 0;
2308
2309end assignLead;
2310
2311 -- Public leadDataLeads
2312 --
2313 -- Restituisce Dettagli della lead che saranno visualizzati nel box Lead
2314 -- Della popup del dettaglio della Lead
2315function leadDataDetails
2316 ( p_codLead in ldd_lead.codlead%type,
2317 p_codMarket in ldd_campaign_details.codmarket%type,
2318 p_mainDealer in ldd_campaign_details.codmaindealer%type )
2319 return elinkTypes.ref_collection is
2320 myCursor elinkTypes.ref_collection;
2321 v_checkMinutesPin NUMBER;
2322BEGIN
2323 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',p_codMarket,1,0) into v_checkMinutesPin from dual;
2324 open myCursor for
2325 select LTY.DESCRIPTION as TIPOLOGIA,
2326 LCS.DESCRIPTION as SOURCE,
2327 LCP.NAMECAMPAIGN as NOMECAMPAGNA,
2328 LEADSTAT.DESCRIPTION as STATUS,
2329 LEADSTAT.CODLEADSTATUS as CODLEADSTATUS,
2330--- T8300 - per i campi di tipo TIMESTAMP, occorre una conversione
2331 TO_CHAR(LLD.CREATEDDATE,'YYYY-MM-DD HH24:MI:SS') as DATACREAZIONE,
2332 TO_CHAR(LLD.LASTMODIFIEDDATE,'YYYY-MM-DD HH24:MI:SS') as LASTMODIFIEDDATE,
2333 ( SELECT ( SELECT pkdict.tran(LEADR.CODDICTIONARY, 1) FROM DUAL )
2334 FROM ldd_leadresult LEADR
2335 WHERE LDN.codleadresult = LEADR.CODLEADRESULT ) as CAUSAARCHIVIAZIONE,
2336 LDN.LEADNOTEDATE as DATAARCHIVIAZIONE,
2337--- T8553 - estrapolazione Note
2338 ( SELECT LRN.REMINDER_NOTE
2339 FROM LDD_REMINDER_NOTES LRN
2340 WHERE LRN.CODLEAD = LLD.CODLEAD ) as REMAINDER,
2341--- T7839 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
2342--- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
2343 pkLDD_LEADS.getProperPhoneNumber(
2344 p_codMarket,
2345 LLD.CODLEADSTATUS,
2346 LLD.Insertiondate,
2347 LLD.MOBILEPHONE,
2348 LLD.PHONENUMBER,
2349 TBK.CODLEAD,
2350 TBK.DATE_SENT,
2351 TBK.PIN_MOBILE,
2352 TBK.PIN_PHONENUMBER,
2353 TBK.TB_RESULT,
2354 TBK.TB_STATUS,
2355 v_checkMinutesPin,
2356 LLD.COMMUNICATIONCHANNEL,
2357 CT.EMAIL_ADDRESS) AS PHONENUMBER,
2358--- T7839 - Un flag indica se il numero di telefono riguarda un PIN
2359-- T14290 - Refactoring del calcolo relativo al Pin. Ora il calcolo ? centralizzato
2360 pkLDD_LEADS.checkIsPin(
2361 p_codMarket,
2362 LLD.CODLEADSTATUS,
2363 LLD.INSERTIONDATE,
2364 nvl(TBK.CODLEAD,LLD.CODLEAD),
2365 TBK.DATE_SENT,
2366 TBK.TB_RESULT,
2367 TBK.TB_STATUS,
2368 v_checkMinutesPin,
2369 LLD.COMMUNICATIONCHANNEL) AS ISPIN,
2370 ( SELECT DLL.ADDRESS ||' - '||DLL.ZIP_NAME||' - '||DLL.TOWN_NAME
2371 FROM DEALERLOCATION DLL
2372 WHERE DLL.CODMARKET = LCD.CODMARKET
2373 AND DLL.CODDEALER = LCD.CODDEALER
2374 AND DLL.CODLOCATION = LCD.CODDEALERLOCATION ) AS DEALERADDRESS,
2375--- T13748 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
2376 BUA.BUSINESS_AREA AS MBA,
2377 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
2378--- T13748 -STR- Estraggo le info del venditore (se presente) assegnato alla LEAD
2379 CASE WHEN (LLD.USERGID is not null)
2380 THEN ELU.FIRSTNAME || ' ' || ELU.LASTNAME || ' ' || '(' || ELU.LOGINNAME || ')'
2381 ELSE null END AS "SALESMAN_USERGID",
2382--- T13748 -END-
2383 PROMO.PAGEURL,
2384 LLD.COMMUNICATIONCHANNEL,
2385 EMAIL_ADDRESS,
2386 LLD.UNTREATED,
2387 LLD.CALLCENTER_CALLBACK,
2388 pkLDD_LEADS.isUnmanagedToMask(
2389 LLD.CODLEAD,
2390 LCD.CODMARKET
2391 ) AS ISUNMANAGEDTOMASK,
2392 --T16172
2393 MSTK.LVL1 as LEVEL1,
2394 MSTK.LVL2 as LEVEL2
2395 from LDD_CAMPAIGN_DETAILS LCD
2396 join ldd_lead LLD on (LCD.CODCAMPAIGNDETAIL = LLD.CODCAMPAIGNDETAIL )
2397 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LCD.CODCAMPAIGN)
2398 join LDD_CAMPAIGN_TYPES_SOURCE LTS
2399 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
2400 LTS.CODCAMPAIGNTYPE=LCP.CODCAMPAIGNTYPE)
2401 join LDD_CAMPAIGN_SOURCES LCS on (LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE)
2402 join LDD_CAMPAIGN_TYPES LTY on (LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE)
2403 join LDD_STATUS LEADSTAT on (LLD.CODLEADSTATUS = LEADSTAT.CODLEADSTATUS)
2404 left join ldd_leadnote LDN on (LDN.CODLEAD = LLD.CODLEAD )
2405 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LLD.CODLEAD )
2406--- T13748 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
2407 left join (select ba.business_area as BUSINESS_AREA,
2408 ba.description as BUSINESS_AREA_DESCRIPTION,
2409 lh.codlead as BUSINESS_AREA_CODLEAD
2410 from LDD_LEAD_HUB_MASTERKEY lh
2411 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
2412 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
2413 on BUA.BUSINESS_AREA_CODLEAD = LLD.CODLEAD
2414--- T13748 -STR- Estraggo le info del venditore (se presente) assegnato alla LEAD
2415 left join elinkuser ELU on ELU.LOGINNAME = LLD.USERGID
2416--- T13748 -END-
2417 LEFT JOIN LDD_PROMO PROMO ON LLD.CODLEAD=PROMO.CODLEAD
2418
2419 LEFT JOIN CONTACT CT ON CT.CODCONTACT = LLD.CODCONTACT
2420
2421 --T16172
2422 left join (select
2423 h.level2 as LVL2,
2424 h.level1 as LVL1,
2425 lh.codlead as CODLEADMSTK
2426 from ldd_hub_masterkey h
2427 left join LDD_LEAD_HUB_MASTERKEY lh
2428 on lh.codmasterkey = h.codmasterkey) MSTK
2429 on MSTK.CODLEADMSTK = LLD.CODLEAD
2430
2431 where LLD.CODLEAD = p_codLead
2432 AND LCD.CODMARKET = p_codMarket
2433 and LCD.CODMAINDEALER = p_mainDealer;
2434
2435 return myCursor;
2436
2437end leadDataDetails;
2438
2439
2440
2441 -- Public leadDataLeads
2442 --
2443 -- Restituisce Dettagli della lead che saranno visualizzati nel box Lead
2444 -- Della popup del dettaglio della Lead
2445function leadCustomerCenterDetails
2446 ( p_codLead in ldd_lead.codlead%type)
2447 return elinkTypes.ref_collection is
2448 myCursor elinkTypes.ref_collection;
2449 BEGIN
2450 open myCursor for
2451 select LLD.CODLEAD,
2452 (SELECT LCC.CUSTOMERCARENOTE
2453 FROM LDD_CUSTOMERCARE LCC
2454 WHERE LCC.CODLEAD = LLD.CODLEAD) AS CUSTOMERCARENOTE,
2455 LAG.AGENDAAPPOINTMENTDATE,
2456 LAG.AGENDASUBJECT,
2457 LAG.AGENDANOTES,
2458 LAG.AGENDANOTEDEALER,
2459 LAG.AGENDATESTDRIVEMODEL,
2460 LAG.AGENDATESTDRIVEVERSION,
2461 LAG.AGENDAMODPROXACQ as MODALITA,
2462 LAG.AGENDADATEACQ as DATA,
2463 (SELECT LVH.INTERESTPARTEXCHANGE
2464 FROM LDD_VEHICLE LVH
2465 WHERE LVH.CODLEAD = LLD.CODLEAD) as PERMUTA
2466 from ldd_lead LLD
2467 left join LDD_AGENDA LAG on (LAG.CODLEAD = LLD.CODLEAD)
2468 where LLD.CODLEAD = p_codLead;
2469
2470 return myCursor;
2471
2472 end leadCustomerCenterDetails;
2473
2474 -- Public leadNextBuyDetails
2475 --
2476 -- Restituisce Dettagli della vettura interessata
2477 --- T8582 - La descrizione viene presa dalla tabella LDD_PRODUCT, e basta!
2478function leadNextBuyDetails
2479 ( p_codLead in ldd_lead.codlead%type )
2480 return elinkTypes.ref_collection is
2481 myCursor elinkTypes.ref_collection;
2482 BEGIN
2483 open myCursor for
2484 select (SELECT BR.BRANDNAME
2485 FROM BRAND BR
2486 WHERE BR.CODBRAND = LDDP.CODBRAND) as BRAND,
2487 LDDP.DESCRPRODUCT as MODELLO,
2488 LDDAG.AGENDAMODPROXACQ as MODALITA,
2489 LDDAG.AGENDADATEACQ as DATANEXTBUY,
2490 LDDAG.SALES_ADVISOR_SELECTED,
2491 LDDP.VIN,
2492 LDDP.PRICETOTAL,
2493 LDDP.PRODUCTTYPE as USEDVEHICLE_TYPE,
2494 LDDP.PRODUCTADDRESS as USEDVEHICLE_ADDRESS,
2495 LDDP.PRODUCTCITY as USEDVEHICLE_CITY,
2496 LDDP.PRODUCTPROVINCE as USEDVEHICLE_PROVINCE,
2497 LDDP.CONFIGURATOR_ID,
2498 LDDP.CODMODEL,
2499 LDDP.IDVEHICLE
2500 from LDD_PRODUCT LDDP
2501 left join ldd_agenda LDDAG on (LDDAG.CODLEAD = LDDP.CODLEAD)
2502 where LDDP.CODLEAD = p_codlead;
2503
2504 return myCursor;
2505
2506 end leadNextBuyDetails;
2507
2508 -- Public leadDataLeads
2509 --
2510 -- Restituisce Dettagli della lead che saranno visualizzati nel box Lead
2511 -- Della popup del dettaglio della Lead
2512 function leadCarDetails(p_codLead in ldd_lead.codlead%type)
2513 return elinkTypes.ref_collection is
2514 myCursor elinkTypes.ref_collection;
2515 BEGIN
2516 open myCursor for
2517 select LLD.CODLEAD,
2518 LVH.DESCRVEHICLE as MODEL,
2519 LVH.DESCREXTERNALCOLOR as COLOR,
2520 LVH.CODFUELTYPE as FUEL,
2521 LVH.DESCRBRAND as DESCRBRAND,
2522 LVH.KM,
2523 LVH.CAPACITY,
2524 LVH.PLATENUMBER,
2525 LVH.REGISTRATIONDATE
2526 from ldd_lead LLD
2527 left join LDD_VEHICLE LVH on (LVH.CODLEAD = LLD.CODLEAD)
2528 where LLD.CODLEAD = p_codLead;
2529
2530 return myCursor;
2531
2532 end leadCarDetails;
2533
2534 -- public resetLeadsAssignedStatus
2535 -- mette le lead presenti nell'array in input in stato da assegnare
2536function resetLeadsAssignedStatus
2537 ( p_leadsList in leadsTable )
2538 return number is
2539 res number;
2540 v_insucces number;
2541 v_creationdate LDD_LEAD.CREATEDDATE%TYPE;
2542 v_tobecounted LDD_CAMPAIGN_TYPES_SOURCE.TOBECOUNTED%TYPE;
2543 v_codleadstatus LDD_LEAD.CODLEADSTATUS%TYPE;
2544 v_codResetStatus LDD_LEAD.CODLEADSTATUS%TYPE;
2545 v_codObjType LDD_LEAD.OBJTYPE%TYPE;
2546 v_codMarket LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE;
2547 v_codMainDealer LDD_CAMPAIGN_DETAILS.CODMAINDEALER%TYPE;
2548 v_userGid LDD_LEAD.USERGID%TYPE;
2549 v_ret number;
2550 v_alrServiceCode Alr_Services.Service_Code%TYPE;
2551
2552 BEGIN
2553 x_procedureName := 'pkLDD_LEADS.resetleadsassignedstatus';
2554 v_insucces := 0;
2555 IF p_leadsList.count >=1 THEN
2556 for idxTable in p_leadsList.first .. p_leadsList.last
2557 loop
2558
2559 BEGIN
2560 SELECT LCD.CREATEDDATE,
2561 LCTS.Tobecounted,
2562 LCD.CODLEADSTATUS,
2563 --- Le leads si resettano a DA, le interviste a DL
2564 CASE WHEN LCD.OBJTYPE = 'I' THEN
2565 'DL'
2566 ELSE
2567 'DA'
2568 END as CODRESETSTATUS,
2569 LCD.OBJTYPE,
2570 CDS.CODMARKET,
2571 CDS.CODMAINDEALER,
2572 LCD.USERGID
2573 INTO v_creationdate,
2574 v_tobecounted,
2575 v_codleadstatus,
2576 v_codResetStatus,
2577 v_codObjType,
2578 v_codMarket,
2579 v_codMainDealer,
2580 v_userGid
2581 FROM LDD_LEAD LCD
2582 JOIN LDD_CAMPAIGN_DETAILS CDS
2583 ON (CDS.CODCAMPAIGNDETAIL = LCD.CODCAMPAIGNDETAIL)
2584 JOIN LDD_CAMPAIGN LCA ON (LCA.CODCAMPAIGN = CDS.CODCAMPAIGN)
2585 JOIN LDD_CAMPAIGN_TYPES_SOURCE LCTS
2586 ON (lcts.codcampaignsource = lca.codcampaignsource and
2587 lcts.codcampaigntype = lca.codcampaigntype)
2588 WHERE LCD.CODLEAD = p_leadsList(idxTable);
2589
2590--- T8957 - modifica "centralizzata" dello stato della lead
2591--- p_lastModifiedDate => NULL vuol dire systimestamp
2592 v_ret := pkldd_leads.updateleadstatus
2593 (p_codlead => p_leadsList(idxTable),
2594 p_codleadstatus => v_codResetStatus,
2595 p_usergid => NULL,
2596 p_assignDate => NULL,
2597 p_codActionContact => NULL,
2598 p_logHandle => NULL);
2599 IF v_ret = 1 THEN
2600
2601 -- Update andata a buon fine.... sistemo i contatori
2602 if v_tobecounted = 1 then
2603 if v_codObjType != 'I' then
2604 -- campagne che vengono contate nei contatori
2605 res := Pkldd_Dwhcounter.incrementCounter('M',null,v_codMarket,v_codMainDealer,v_userGid, -1, v_creationdate);
2606 res := Pkldd_Dwhcounter.incrementCounter('L',null,v_codMarket,v_codMainDealer,null, +1, v_creationdate);
2607 else
2608 res := Pkldd_Dwhcounter.incrementCounter('S',null,v_codMarket,v_codMainDealer,v_userGid, -1, v_creationdate);
2609 res := Pkldd_Dwhcounter.incrementCounter('Z',null,v_codMarket,v_codMainDealer,null, +1, v_creationdate);
2610 end if;
2611 else
2612 -- Lead che non devono essere decrementate (Es. DBCARE)
2613 v_insucces := v_insucces + 1;
2614 end if;
2615 ELSE --- IF v_ret=1
2616 --- Modifica dello stato della lead non riuscito
2617 v_insucces := v_insucces + 1;
2618 END IF;
2619
2620 BEGIN
2621 select DECODE(v_codObjType,
2622 'A',
2623 'NUOVALEAD_DA_AS',
2624 'P',
2625 'NEWPLTOASSIGN',
2626 'NUOVALEADDAASSEGNARE')
2627 into v_alrServiceCode
2628 from dual;
2629 -- invio di SMS se hanno sottoscritto il servizio
2630 --spagna non c'era..verifichiamo se inserirla.
2631 --if (v_codMarket <> '3136') -- T16016 - Disattivazione servizio SMS per Spagna tramite Artico
2632 --then
2633 res := pkSMSMANAGER.sendSms(p_leadsList(idxTable), v_alrServiceCode);
2634 --end if;
2635 EXCEPTION
2636 WHEN OTHERS THEN
2637 x_errCode := sqlcode;
2638 x_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
2639 PKLDD_LEADS.startLogManagement;
2640 pklog.writedetail(vloghandle => x_logHandle,
2641 llevel => PKLOG.WARNING,
2642 pprocedurename => x_procedureName,
2643 description => 'Non-blocking error',
2644 details => 'Error calling pkSMSMANAGER.sendSms for codlead ' ||
2645 p_leadsList(idxTable));
2646 pklog.writedetail(vloghandle => x_logHandle,
2647 llevel => PKLOG.WARNING,
2648 pprocedurename => x_procedureName,
2649 description=>'Error : '||to_char(x_errCode),
2650 details => x_errMsg);
2651 END;
2652 -- END IF;
2653
2654 COMMIT;
2655
2656 EXCEPTION
2657 WHEN NO_DATA_FOUND THEN
2658 v_insucces := v_insucces + 1;
2659 END;
2660
2661 end loop;
2662
2663 END IF;
2664
2665 return v_insucces;
2666
2667 end resetLeadsAssignedStatus;
2668
2669 -- Public findDetailedLeadDataForXLS
2670 --
2671 -- Restituisce i dati di tutte le Leads dell'utente
2672 -- per l'export (avanzato) in excel
2673function findDetailedLeadDataForExport
2674 ( p_codMainDealer in ldd_campaign_details.codmaindealer%type,
2675 p_codMarket in ldd_campaign_details.codmarket%type,
2676 p_codLanguage in language.codlanguage%type,
2677 p_usergid in ldd_lead.usergid%type default NULL,
2678 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
2679 return elinkTypes.ref_collection is
2680 myCursor elinkTypes.ref_collection;
2681 v_checkMinutesPin NUMBER;
2682BEGIN
2683 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',p_codMarket,1,0) into v_checkMinutesPin from dual;
2684 open myCursor for
2685 select LCD.CODLEAD,
2686 LCS.DESCRIPTION as LEAD_SOURCE,
2687 ( SELECT ( SELECT pkdict.tran(LST.CODDICTIONARY, p_codlanguage) FROM DUAL )
2688 FROM LDD_STATUS LST
2689 WHERE LST.CODLEADSTATUS = LCD.CODLEADSTATUS) as TIPOLEAD,
2690--dati riguardo vettura di interesse
2691 ( SELECT BR.BRANDNAME
2692 FROM BRAND BR
2693 WHERE BR.CODBRAND = LDP.CODBRAND) as NEXTBUYBRAND,
2694 LDP.DESCRPRODUCT AS NEXTBUYMODEL,
2695 -- T14461 - start
2696 LDP.VIN AS NEXTBUYVIN,
2697 LDP.PRICETOTAL AS NEXTBUYPRICETOTAL,
2698 LDP.PRODUCTTYPE as NEXTBUYUSEDVEHICLE_TYPE,
2699 LDP.PRODUCTADDRESS as NEXTBUYUSEDVEHICLE_ADDRESS,
2700 LDP.PRODUCTCITY as NEXTBUYUSEDVEHICLE_CITY,
2701 LDP.PRODUCTPROVINCE as NEXTBUYUSEDVEHICLE_PROVINCE,
2702 -- T14461 - end
2703 TO_CHAR(LCD.CREATEDDATE,'YYYY-MM-DD HH24:MI:SS') as CREATEDDATE,
2704 LTY.DESCRIPTION as TYPE_DESC,
2705 elu.firstname || ' ' || elu.lastname as SALESMAN,
2706 LCD.CODCONTACT,
2707--- T9325 - outer join con CONTACT. Grazie a Phone-Lead il contatto e' facoltativo
2708 CASE WHEN CT.CODCONTACTTYPE IN ('P', 'R') THEN
2709 ( SELECT PE.LASTNAME || ' ' || PE.FIRSTNAME
2710 FROM PERSON PE
2711 WHERE CT.CODCONTACT = PE.CODPERSON)
2712 WHEN CT.CODCONTACTTYPE IN ('C') THEN
2713 ( SELECT CO.COMPANYNAME
2714 FROM COMPANY CO
2715 WHERE CO.CODCOMPANY = CT.CODCONTACT)
2716 ELSE
2717 NULL
2718 END AS CONTACT,
2719 ( SELECT ( SELECT pkdict.tran(CRC.CODDICTIONARY, p_codLanguage) FROM DUAL )
2720 FROM CRM_CONTACTSTATUS CRC
2721 WHERE CRC.CODCONTACTSTATUS = CT.CODCONTACTSTATUS) as CONTACT_STATUS,
2722 CT.ADDRESS1 || ' ' || CT.ADDRESS2 || ' ' || CT.ADDRESS3 || ', ' || CT.ADDRESSNUMBER as INDIRIZZO,
2723 CT.CAP,
2724 ( SELECT CTY.DESCR_CITY
2725 FROM CITY CTY
2726 WHERE CTY.CODCITY = CT.CODCITY
2727 AND CTY.CODDEPARTMENT = CT.CODDEPARTMENT
2728 AND CTY.CODREGION = CT.CODREGION
2729 AND CTY.CODNATION = CT.CODNATION)as CITY,
2730 CT.CODDEPARTMENT as CODDEPARTMENT,
2731 CT.TELEPHONE as TELEPHONE,
2732 pkcrm_negotiation.findallnegocontactasxmltext(LCD.CODCONTACT, p_codLanguage) as NEGOTIATIONS_DETAILS,
2733 pkcrm_negotiationaction.findallactioncontactasxmltext(LCD.CODCONTACT, p_codLanguage) as ACTIONS_DETAILS,
2734 --pkcrm_feedback.getcustomerfeedbackasxmltext(LCD.CODCONTACT, p_codLanguage) as NOTESDETAILS,
2735 dloc.town_name as SITE,
2736 dloc.address as address,
2737 LCD.OBJTYPE,
2738--- B28708 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
2739--- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
2740 pkLDD_LEADS.getProperPhoneNumber(
2741 p_codMarket,
2742 LCD.CODLEADSTATUS,
2743 LCD.INSERTIONDATE,
2744 LCD.MOBILEPHONE,
2745 LCD.PHONENUMBER,
2746 TBK.CODLEAD,
2747 TBK.DATE_SENT,
2748 TBK.PIN_MOBILE,
2749 TBK.PIN_PHONENUMBER,
2750 TBK.TB_RESULT,
2751 TBK.TB_STATUS,
2752 v_checkMinutesPin,
2753 LCD.COMMUNICATIONCHANNEL,
2754 cT.Email_Address) AS PHONENUMBER,
2755--- B28708 - Un flag indica se il numero di telefono riguarda un PIN
2756 CASE WHEN TBK.CODLEAD IS NULL THEN
2757 NVL(LCD.MOBILEPHONE,LCD.PHONENUMBER)
2758 WHEN LCD.CODLEADSTATUS in ('IG','AR') THEN
2759 NVL(LCD.MOBILEPHONE,LCD.PHONENUMBER)
2760 ELSE
2761 CASE WHEN TBK.TB_STATUS IN ( 'DA','SD','PD','PE' ) THEN
2762 NULL
2763 WHEN TBK.TB_STATUS IN ( 'RP' ) THEN
2764 NVL(TBK.PIN_MOBILE,TBK.PIN_PHONENUMBER)
2765 WHEN TBK.TB_STATUS IN ( 'RE' ) THEN
2766
2767 CASE WHEN TBK.TB_RESULT = 'CC' THEN --T16128
2768 NULL
2769 WHEN TBK.TB_RESULT IN ('CA','CF') THEN -- T16128
2770 NVL(TBK.PIN_MOBILE,TBK.PIN_PHONENUMBER)
2771 ELSE
2772 NULL
2773 END
2774
2775 ELSE
2776 NULL
2777 END
2778 END AS PIN,
2779 (select pkdict.tran(
2780 (select max(act.coddictionary) keep(dense_rank first order by ldh.creationdate desc, cac.codactioncontact desc)
2781 from ldd_leadhistory ldh
2782 join crm_actioncontact cac
2783 on cac.codactioncontact = ldh.codactioncontact
2784 join crm_action act
2785 on act.codaction = cac.codaction
2786 where ldh.codlead = LCD.codlead
2787 and ldh.codleadstatus = 'IG'
2788 ),
2789 p_codLanguage) from dual) AS MANAGEDACTION,
2790--- T13733 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
2791 BUA.BUSINESS_AREA AS MBA,
2792 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
2793 --EXPORT PROMO DATA
2794 PROMO.MEDIACAMPAIGNID,
2795 PROMO.PROMOCAMPAIGNID,
2796 PROMO.PROMOOFFERINCENTIVEID,
2797 PROMO.PROMOTITLE,
2798 PROMO.LASTREFERRER,
2799 PROMO.DEVICEUSED,
2800 PROMO.PAGEURL,
2801 PROMO.VISITORID
2802--- T13733 -END-
2803 from LDD_CAMPAIGN_DETAILS LDT
2804 join ldd_lead LCD on (LDT.CODCAMPAIGNDETAIL = LCD.CODCAMPAIGNDETAIL)
2805 join LDD_CAMPAIGN LCP on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
2806 join LDD_CAMPAIGN_TYPES_SOURCE LTS
2807 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
2808 LTS.CODCAMPAIGNTYPE=LCP.CODCAMPAIGNTYPE )
2809 join LDD_CAMPAIGN_SOURCES LCS on (LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE)
2810 join LDD_CAMPAIGN_TYPES LTY on (LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE)
2811 join elinkuser elu on (LCD.Usergid = elu.loginname)
2812 join dealerlocation dloc on (ldt.codmarket = dloc.codmarket and
2813 ldt.coddealer = dloc.coddealer and
2814 ldt.coddealerlocation = dloc.codlocation)
2815 LEFT join CONTACT CT on (CT.CODCONTACT = LCD.CODCONTACT)
2816 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LCD.CODLEAD )
2817 LEFT JOIN LDD_PRODUCT LDP ON ( LDP.CODLEAD = LCD.CODLEAD )
2818--- T13733 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
2819 left join (select ba.business_area as BUSINESS_AREA,
2820 ba.description as BUSINESS_AREA_DESCRIPTION,
2821 lh.codlead as BUSINESS_AREA_CODLEAD
2822 from LDD_LEAD_HUB_MASTERKEY lh
2823 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
2824 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
2825 on BUA.BUSINESS_AREA_CODLEAD = LCD.CODLEAD
2826 LEFT JOIN LDD_PROMO PROMO ON (PROMO.CODLEAD = LCD.CODLEAD)
2827--- T13733 -END-
2828 where LDT.CODMAINDEALER = p_codMainDealer
2829 AND LDT.CODMARKET = p_codMarket
2830 AND LCD.OBJTYPE = NVL(p_objtype,'L')
2831 AND (LCD.USERGID = p_usergid OR p_usergid is null)
2832 AND LTS.ISDELETED=0
2833 AND LCD.CODLEADSTATUS in ('IG', 'DG','AR')
2834 AND NVL(elu.deleted,0)=0
2835 order by LCD.CODLEAD, LCD.CODCONTACT desc;
2836
2837 return myCursor;
2838
2839end findDetailedLeadDataForExport;
2840-----------------------------------------------------------------------------------
2841-- Bug 27603 - rifatta la funzione
2842function getStatusLeadsOrInterview
2843 (p_objtype in LDD_OBJTYPES.objtype%type := NULL)
2844 return ELINKtypes.ref_collection is
2845 my_cursor ELINKtypes.ref_collection;
2846 err varchar2(2000);
2847 v_objtype char;
2848BEGIN
2849 v_objtype := nvl(p_objtype,'L');
2850 OPEN my_cursor FOR
2851 SELECT LS.CODLEADSTATUS ,LS.DESCRIPTION
2852 FROM LDD_STATUS LS
2853 WHERE ( (v_objtype = 'L' and LS.CODLEADSTATUS in ('IG','DG','DA'))
2854 OR
2855 (v_objtype = 'I' and LS.CODLEADSTATUS in ('DL','DG','IG')))
2856 AND Ls.CODLEADSTATUS IS NOT NULL
2857 order by LS.CODLEADSTATUS asc;
2858
2859 RETURN my_cursor;
2860
2861exception
2862 when others then
2863 err := DBMS_UTILITY.FORMAT_ERROR_STACK;
2864 OPEN my_cursor FOR
2865 select err
2866 from dual;
2867
2868 return my_cursor;
2869
2870end getStatusLeadsOrInterview;
2871
2872--- createNewLead
2873--- metodo centralizzato di creazione di una LEAD
2874--- TUTTE le creazioni di leads DEVONO usare questo metodo
2875--- ritorna : >0 Nuovo codice Lead / NULL ---> ERRORE ( check logmaster/logc )
2876--- B38233 - associazione della lead alla masterkey al momento della creazione
2877FUNCTION createNewLead
2878 ( p_codCampaignDetail IN LDD_LEAD.CODCAMPAIGNDETAIL%TYPE,
2879 p_codContact IN LDD_LEAD.CODCONTACT%TYPE,
2880 p_usergid IN LDD_LEAD.USERGID%TYPE,
2881 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
2882 p_codLeadExt IN LDD_LEAD.CODLEADEXT%TYPE,
2883 p_CodContactCare IN LDD_LEAD.CODCONTACT_CARE%TYPE,
2884 p_CodLeadSource IN LDD_LEAD.CODLEADSOURCE%TYPE,
2885 p_reminderNote IN LDD_REMINDER_NOTES.REMINDER_NOTE%TYPE,
2886 p_codLanguage IN LDD_LEAD.CODLANGUAGE%TYPE,
2887 p_createdDate IN LDD_LEAD.CREATEDDATE%TYPE,
2888 p_lastModifiedDate IN LDD_LEAD.LASTMODIFIEDDATE%TYPE DEFAULT NULL,
2889 p_insertionDate IN LDD_LEAD.INSERTIONDATE%TYPE DEFAULT SYSDATE,
2890 p_callCenterCallBack IN LDD_LEAD.CALLCENTER_CALLBACK%TYPE DEFAULT 0,
2891 p_unTreated IN LDD_LEAD.UNTREATED%TYPE Default 0,
2892 p_assignDate IN LDD_LEAD.ASSIGNDATE%TYPE DEFAULT NULL,
2893 p_sentMail IN LDD_LEAD.SENTMAIL%TYPE Default 0,
2894 p_objType IN LDD_LEAD.OBJTYPE%TYPE Default 'L',
2895 p_phoneNumber IN LDD_LEAD.PHONENUMBER%TYPE DEFAULT NULL,
2896 p_mobilePhone IN LDD_LEAD.MOBILEPHONE%TYPE DEFAULT NULL,
2897 p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE DEFAULT NULL ,
2898 p_timeZone IN MARKET.TIMEZONE_REGION%TYPE DEFAULT NULL ,
2899 p_codActionContact IN CRM_ACTIONCONTACT.CODACTIONCONTACT%TYPE DEFAULT NULL,
2900 p_codleadHub IN ldd_lead.codleadhub%TYPE DEFAULT NULL, -- T9786
2901 p_pl_provider_id IN ldd_lead.pl_provider_id%TYPE DEFAULT NULL, -- T13040
2902 p_codMasterKey IN LDD_HUB_MASTERKEY.CODMASTERKEY%TYPE DEFAULT NULL,
2903 p_sourcequerystring IN LDD_LEAD.SOURCEQUERYSTRING%TYPE DEFAULT NULL, -- T14065
2904 p_communicationchannel IN LDD_LEAD.COMMUNICATIONCHANNEL%TYPE DEFAULT NULL,
2905 p_leadscore IN ldd_lead.leadscore%TYPE DEFAULT NULL) -- T15175
2906 RETURN LDD_LEAD.CODLEAD%TYPE IS
2907 v_codLead LDD_LEAD.CODLEAD%TYPE;
2908 v_codReminderLead LDD_LEAD.CODLEAD%TYPE;
2909 v_codMarket LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE;
2910 v_timeZoneName MARKET.TIMEZONE_REGION%TYPE;
2911 v_firstDate TIMESTAMP;
2912 v_createdTstp LDD_LEAD.CREATEDDATE%TYPE;
2913 v_assignTstp LDD_LEAD.ASSIGNDATE%TYPE;
2914 v_lastModifiedTstp LDD_LEAD.LASTMODIFIEDDATE%TYPE;
2915 v_codNation NATION.CODNATION%TYPE;
2916 v_ret number;
2917BEGIN
2918--- T8300 - i valori di mercato e time zone se non valorizzati devono essere reperiti
2919--- attraverso la CAMPAGNA di LEAD o attraverso il contatto
2920--- Il timeZone ha la preferenza se associato alla location. Mercatoni tipo
2921--- russia possono avere piu' time zones
2922--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
2923 IF p_codMarket IS NULL OR p_timeZone IS NULL THEN
2924 BEGIN
2925 SELECT LCD.CODMARKET,
2926 PKTIMEZONEMANAGEMENT.getTimeZoneRegionByLocation (p_codmarket => LCD.CODMARKET,
2927 p_coddealer => LCD.CODDEALER,
2928 p_codlocation => LCD.CODDEALERLOCATION ),
2929 PKNATION.getNationForUser(LCD.CODMARKET,LCD.CODDEALER) AS CODNATION
2930
2931 INTO v_codMarket,
2932 v_timeZoneName,
2933 v_codNation
2934 FROM LDD_CAMPAIGN_DETAILS LCD
2935 JOIN MARKET MKT ON MKT.CODMARKET = LCD.CODMARKET
2936 LEFT JOIN DEALERLOCATION DLL
2937 ON ( DLL.CODMARKET = LCD.CODMARKET AND
2938 DLL.CODDEALER = LCD.CODDEALER AND
2939 DLL.CODLOCATION = LCD.CODDEALERLOCATION )
2940 WHERE LCD.CODCAMPAIGNDETAIL = p_codCampaignDetail;
2941 EXCEPTION
2942 WHEN NO_DATA_FOUND THEN
2943 SELECT CNT.CODMARKET,
2944 PKTIMEZONEMANAGEMENT.getTimeZoneRegionByContact ( p_codContact => CNT.CODCONTACT ),
2945 PKNATION.getNationForUser(CNT.CODMARKET,CNT.CODDEALERMAIN) AS CODNATION
2946 INTO v_codMarket,v_timeZoneName,v_codNation
2947 FROM CONTACT CNT
2948 WHERE CNT.CODCONTACT = p_codContact;
2949 END;
2950 ELSE
2951 SELECT PKNATION.getNationForUser(LCD.CODMARKET,LCD.CODDEALER) AS CODNATION
2952 INTO v_codNation
2953 FROM LDD_CAMPAIGN_DETAILS LCD
2954 WHERE LCD.CODCAMPAIGNDETAIL = p_codCampaignDetail;
2955 v_codMarket := p_codMarket;
2956 END IF;
2957
2958--- T8300 - La data di creazione, fornita esternamente o data attuale del DB,
2959--- deve essere rapportata al time zone corretto
2960 v_createdTstp := NVL(p_createdDate,SYSTIMESTAMP )
2961 at Time Zone NVL(v_timeZoneName,sessiontimezone);
2962
2963--- A questo punto occorre verificare se l'ora localizzata e' compresa all'interno
2964--- dell'orario di lavoro di gestione delle leads ( CODICE "L" ). Occorre quindi
2965--- controllare la data/ora senza considerare il TimeZone ( cast to DATE )
2966--- ottenendo in output il primo momento lavorativo utile disponibile
2967--- T12851 - Orario di lavoro differente per afterSales
2968 v_firstDate := PKHOLIDAYS.getnearestworkingdaytime
2969 (p_codmarket => v_codMarket,
2970 p_checkdate => CAST(v_createdTstp AS DATE),
2971 p_flagdayafter => 'Y',
2972 p_codtimetabletype => CASE WHEN p_objType IN ('A') THEN 'A' ELSE 'L' END ,
2973 p_codnation => v_codNation );
2974
2975--- T8300 - Il valore data/ora contenuto all'interno di v_firstDate adesso deve essere
2976--- corredato di time zone per indicare dove la Lead e' stata processata
2977 BEGIN
2978 v_createdTstp := FROM_TZ(v_firstDate , NVL(v_timeZoneName,sessiontimezone) );
2979 EXCEPTION
2980 WHEN OTHERS THEN
2981--- Nome del time zone errato
2982 v_createdTstp := FROM_TZ (v_firstDate , sessionTimeZone );
2983 END;
2984
2985 v_lastModifiedTstp := NVL(p_lastModifiedDate,SYSTIMESTAMP )
2986 at Time Zone NVL(v_timeZoneName,sessiontimezone) ;
2987
2988--- B29107 - La data di assegnazione di una lead, se non nulla, deve essere > createddate
2989 IF p_assignDate IS NOT NULL THEN
2990 v_assignTstp := GREATEST ( v_createdTstp + NUMTODSINTERVAL(1,'second') ,
2991 p_assignDate
2992 at Time Zone NVL(v_timeZoneName,sessiontimezone) );
2993
2994 ELSE
2995 v_assignTstp := NULL;
2996 END IF;
2997
2998 INSERT INTO ldd_lead ( CODLEAD,
2999 CODCAMPAIGNDETAIL ,
3000 CODCONTACT ,
3001 USERGID ,
3002 CODLEADSTATUS ,
3003 CREATEDDATE ,
3004 LASTMODIFIEDDATE ,
3005 CODLEADEXT ,
3006 CODLEADHUB , -- T9786
3007 CODCONTACT_CARE ,
3008 CODLEADSOURCE ,
3009 CODLANGUAGE ,
3010 CALLCENTER_CALLBACK ,
3011 UNTREATED ,
3012 ASSIGNDATE ,
3013 SENTMAIL ,
3014 OBJTYPE ,
3015 PHONENUMBER ,
3016 MOBILEPHONE ,
3017 INSERTIONDATE,
3018 PL_PROVIDER_ID, -- T13040
3019 SOURCEQUERYSTRING,
3020 COMMUNICATIONCHANNEL, --T14065
3021 leadscore) --T15175
3022 VALUES ( Q_CODLEAD.NEXTVAL,
3023 p_codCampaignDetail,
3024 p_codContact,
3025 p_usergid,
3026 p_codLeadStatus,
3027 v_createdTstp,
3028 v_lastModifiedTstp,
3029 p_codLeadExt,
3030 p_codleadhub, -- T9786
3031 p_CodContactCare,
3032 p_CodLeadSource,
3033 p_codLanguage,
3034 p_callCenterCallBack,
3035 p_unTreated,
3036 v_assignTstp,
3037 p_sentMail,
3038 p_objType,
3039 p_phoneNumber,
3040 p_mobilePhone,
3041 NVL(p_insertionDate,p_createdDate),
3042 p_pl_provider_id, -- T13040
3043 p_sourcequerystring,
3044 p_communicationchannel, --T14065
3045 p_leadscore) -- T15175
3046 RETURNING CODLEAD INTO v_codLead;
3047--- T8952 - Occorre conoscere il codactioncontact responsabile del cambio di stato ( a saperlo )
3048 v_ret := pklead_status_history.insertleadstatusfromlead
3049 (v_codLead,p_codLeadStatus,p_codActionContact);
3050--- T8553 - Estrapolazione reminder_note
3051 IF p_reminderNote IS NOT NULL THEN
3052 v_codReminderLead := updateReminderNote ( v_codLead , p_reminderNote );
3053 END IF;
3054
3055--- B38233 - Eventuale associazione lead/Masterkey
3056 IF p_codMasterKey IS NOT NULL THEN
3057 BEGIN
3058 INSERT INTO LDD_LEAD_HUB_MASTERKEY ( CODMASTERKEY , CODLEAD )
3059 VALUES ( p_codMasterKey, v_codLead );
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 NULL;
3063 END;
3064 END IF;
3065
3066--- T8730 - Inserimento livelli di sla per la LEAD
3067 v_ret := PKLDD_SLA.updateLeadSlaRanges ( v_codLead );
3068
3069 RETURN v_codLead;
3070
3071EXCEPTION
3072 WHEN OTHERS THEN
3073 x_errCode := sqlcode;
3074 x_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
3075 x_procedureName := 'PKLDD_LEADS.createNewLead';
3076 PKLDD_LEADS.startLogManagement;
3077 pklog.writedetail(vloghandle => x_logHandle,
3078 llevel => PKLOG.SEVERE,
3079 pprocedurename => x_procedureName,
3080 description => 'Error '||x_errCode||' while creating lead ',
3081 details => x_errMsg);
3082 RETURN NULL;
3083END createNewLead;
3084
3085--- updateLeadStatus
3086--- La funzione ha lo scopo di centralizzare la modifica dello stato della LEAD
3087--- in quanto tale operazione provoca l'esecuzione in cascata di altre operazioni
3088--- Codici di ritorno : 1-->OK / 0-->KO
3089FUNCTION updateLeadStatus
3090 ( p_codLead IN LDD_LEAD.CODLEAD%TYPE ,
3091 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
3092 p_usergid IN LDD_LEAD.USERGID%TYPE DEFAULT NULL,
3093 p_lastModifiedDate IN LDD_LEAD.LASTMODIFIEDDATE%TYPE DEFAULT NUll,
3094 p_assignDate IN LDD_LEAD.ASSIGNDATE%TYPE DEFAULT NULL ,
3095 p_codActionContact IN CRM_ACTIONCONTACT.CODACTIONCONTACT%TYPE DEFAULT NULL,
3096 p_logHandle IN LOGMASTER.SEQUENCEID%TYPE default NULL )
3097 RETURN NUMBER IS
3098
3099 v_returnCode NUMBER;
3100 v_finalSeverityLevelField LDD_LEAD.FINAL_SEVERITYLEVELFIELD%TYPE;
3101 v_codCampaignDetail LDD_LEAD.CODCAMPAIGNDETAIL%TYPE;
3102 v_codMarket MARKET.CODMARKET%TYPE;
3103 v_timeZoneRegion MARKET.TIMEZONE_REGION%TYPE;
3104 v_pushDate DATE;
3105 v_plannedPushDate DATE;
3106
3107--- variabili per gestione dell'errore. Il valore di HANDLE puo' provenire dall'esterno
3108--- i LOG emessi da questa procedura possono essere accodati a quelli del chiamante
3109 v_logHandle LOGMASTER.SEQUENCEID%TYPE;
3110
3111 --T12428 Integrazione con Engage.
3112 v_codcampaign LDD_CAMPAIGN.CODCAMPAIGN%TYPE;
3113 v_codmaindealer DEALER.CODMAINDEALER%TYPE;
3114 v_codbrand BRAND.CODBRAND%TYPE;
3115 v_usergid LDD_LEAD.USERGID%TYPE;
3116 v_codcontact LDD_LEAD.CODCONTACT%TYPE;
3117 v_objType LDD_LEAD.OBJTYPE%TYPE;
3118 v_ret NUMBER;
3119 v_codleadstatus ldd_lead.codleadstatus%TYPE;
3120 v_codNation NATION.CODNATION%TYPE;
3121
3122BEGIN
3123 SAVEPOINT SP001;
3124
3125--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
3126 SELECT lcd.codmarket,
3127 ll.codleadstatus,
3128 PKNATION.getNationForUser(lcd.codmarket,lcd.coddealer) AS CODNATION
3129 INTO v_codMarket,
3130 v_codleadstatus,
3131 v_codNation
3132 FROM ldd_lead ll
3133 JOIN ldd_campaign_details lcd
3134 ON lcd.codcampaigndetail = ll.codcampaigndetail
3135 WHERE ll.codlead = p_codLead;
3136
3137 IF p_codLeadStatus IN ( 'DA','DL' ) THEN
3138
3139--- La lead ritorna in stato 'DA' o 'DL', da assegnare. Reset data di assegnazione e usergid se
3140--- non specificato diversamente
3141 UPDATE LDD_LEAD LEA
3142 SET LEA.USERGID = CASE WHEN p_usergid IS NOT NULL THEN
3143 p_userGid
3144 ELSE
3145 NULL
3146 END,
3147 LEA.CODLEADSTATUS = p_codLeadStatus,
3148 LEA.LASTMODIFIEDDATE = SYSTIMESTAMP AT TIME ZONE
3149 NVL(TO_CHAR(LEA.CREATEDDATE,'TZR'),SESSIONTIMEZONE) ,
3150 LEA.ASSIGNDATE = CASE WHEN p_assignDate IS NOT NULL THEN
3151 p_assignDate
3152 ELSE
3153 NULL
3154 END
3155 WHERE LEA.CODLEAD = p_codLead;
3156--- Accade anche che si voglia solo modificare lastmodifieddate
3157--- AND LEA.CODLEADSTATUS != p_codLeadStatus ;
3158
3159 ELSE
3160
3161 UPDATE LDD_LEAD LEA
3162 SET LEA.USERGID = NVL(p_usergid,LEA.USERGID),
3163 LEA.CODLEADSTATUS = p_codLeadStatus,
3164 LEA.LASTMODIFIEDDATE = SYSTIMESTAMP AT TIME ZONE
3165 NVL(TO_CHAR(LEA.CREATEDDATE,'TZR'),SESSIONTIMEZONE) ,
3166 LEA.ASSIGNDATE = NVL(p_assignDate,LEA.ASSIGNDATE)
3167 WHERE LEA.CODLEAD = p_codLead
3168 RETURNING LEA.CODCAMPAIGNDETAIL ,
3169 TO_CHAR(LEA.CREATEDDATE,'TZR')
3170 INTO v_codCampaignDetail,
3171 v_timeZoneRegion ;
3172--- Accade anche che si voglia solo modificare lastmodifieddate
3173--- AND LEA.CODLEADSTATUS != p_codLeadStatus ;
3174
3175 END IF;
3176
3177--- Se c'e' stata realmente una modifica ( SQL%ROWCOUNT > 0 ) il cambio di stato viene storicizzato
3178--- T8952 - La storicizzazione deve comprendere , eventualmente, l'azione che ha portato al
3179--- campbio di stato della lead
3180 IF SQL%ROWCOUNT > 0 THEN
3181 v_returnCode := pklead_status_history.insertleadstatusfromlead(p_codLead,
3182 p_codLeadStatus,
3183 p_codActionContact);
3184
3185--- T8730 - se lo stato della lead e' passato ad 'AR' allora occorre "cristallizzare" il
3186--- valore del livello di severita' al momento del passaggio di stato ( SYSDATE )
3187 IF p_codLeadStatus = 'AR' THEN
3188 BEGIN
3189 SELECT SEL001.SEVERITYLEVELFIELD
3190 INTO v_finalSeverityLevelField
3191 FROM ( SELECT LSR.SEVERITYLEVELFIELD,
3192 ll.communicationchannel
3193 FROM LDD_LEADS_SLARANGES LSR
3194 JOIN ldd_lead ll
3195 ON ll.codlead = lsr.codlead
3196 WHERE LSR.CODLEAD = p_codLead
3197 --AND LSR.CODSLAEVENT = 'L'
3198 AND LSR.CODSLAEVENT = DECODE(NVL(ll.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
3199 AND LSR.SLALIMIT > SYSDATE
3200 ORDER BY LSR.SLALIMIT
3201 ) SEL001
3202 WHERE ROWNUM < 2;
3203 EXCEPTION
3204--- record non trovato --> RED
3205 WHEN NO_DATA_FOUND THEN
3206 v_finalSeverityLevelField := 'R';
3207 END;
3208
3209 UPDATE LDD_LEAD LEA
3210 SET LEA.FINAL_SEVERITYLEVELFIELD = v_finalSeverityLevelField
3211 WHERE LEA.CODLEAD = p_codLead;
3212
3213--- Una volta archiviata si buttano i RANGES "L" ( si possono ricreare
3214--- con PKLDD_SLA.updateLeadSlaRanges ) e I dati relativi alle PUSH
3215 BEGIN
3216 DELETE FROM LDD_LEADS_SLARANGES LSR
3217 WHERE LSR.CODSLAEVENT in ('L','X') -- T15301
3218 AND LSR.CODLEAD = p_codLead;
3219 DELETE FROM LDD_LEAD_PUSHTIME LLP
3220 WHERE LLP.CODLEAD = p_codLead;
3221 DELETE FROM LDD_LEAD_PUSHUSERGID LPU
3222 WHERE LPU.CODLEAD = p_codLead;
3223 EXCEPTION
3224 WHEN OTHERS THEN
3225 NULL;
3226 END;
3227
3228 ELSIF p_codLeadStatus = 'DG' THEN
3229
3230--- Assegnazione della lead.
3231
3232 BEGIN
3233--T12428 Integrazione con Engage.
3234 SELECT LCD.CODMARKET,
3235 LCD.CODCAMPAIGN,
3236 LCD.CODMAINDEALER,
3237 LCD.CODBRAND,
3238 L.USERGID,
3239 L.CODCONTACT,
3240 L.OBJTYPE
3241 INTO v_codMarket,
3242 v_codcampaign,
3243 v_codmaindealer,
3244 v_codbrand,
3245 v_usergid,
3246 v_codcontact,
3247 v_objType
3248 FROM LDD_CAMPAIGN_DETAILS LCD
3249 JOIN LDD_LEAD L
3250 ON L.CODCAMPAIGNDETAIL = LCD.CODCAMPAIGNDETAIL
3251 WHERE LCD.CODCAMPAIGNDETAIL = v_codCampaignDetail
3252 AND L.CODLEAD = p_codLead;
3253
3254 --T12428 Integrazione con Engage.
3255 IF pkLEADLOADER_UK.sendToElms(v_codCampaign, v_codMarket, 'A') = 1 THEN
3256 v_ret := pkCRMCORE.crmUpgradeStatus(p_codMarket => v_codmarket,
3257 p_codMainDealer => v_codmaindealer,
3258 p_userGID => v_usergid,
3259 p_codAction => 'E1',
3260 p_codContact => v_codcontact,
3261 p_codActionContact => NULL,
3262 p_codActionStatus => 'A',
3263 p_plannedDate => NULL,
3264 p_codNegotiation => NULL,
3265 p_nameNegotiation => NULL,
3266 p_codNegotiationResult => NULL,
3267 p_note => 'Action generated from lead in status DG',
3268 p_resultComment => NULL,
3269 p_codpurchasemode => NULL,
3270 p_codevent => NULL,
3271 p_datePurchaseDelayed => NULL,
3272 p_codOffer => NULL,
3273 p_codbrandinfocar => NULL,
3274 p_codBrand => v_codbrand,
3275 p_codLead => p_codLead,
3276 p_completeddate => NULL);
3277 IF v_ret = 0 THEN
3278 x_procedureName := 'PKLDD_LEADS.updateLeadStatus';
3279 IF p_logHandle IS NULL THEN
3280 v_logHandle := pkLOG.Insertmaster(NULL,x_procedureName,NULL,'0');
3281 ELSE
3282 v_logHandle := p_logHandle;
3283 END IF;
3284 pklog.writedetail(vloghandle=>v_logHandle,
3285 llevel=>PKLOG.WARNING,
3286 pprocedurename=>x_procedureName,
3287 description=>'error : '||to_char(x_errCode)||' in pkCRMCORE.crmUpgradeStatus',
3288 details=>x_errMsg );
3289 END IF;
3290 END IF;
3291--- T12222 - Gestione del parametro PUSH_WORKINGTIME
3292--- Se settato allora l'avviso tramite SMS puo' avvenire solo in
3293--- orario lavorativo
3294 IF ( pkavailability.crmcheckavailability(p_codmarket => v_codMarket,
3295 p_parameter => 'PUSH_WORKINGTIME' ) = 'Y' ) THEN
3296
3297 v_pushDate := CAST(systimestamp at time zone NVL(v_timeZoneRegion,'Europe/Rome') AS DATE);
3298
3299--- T12851 - Orario di lavoro differente per afterSales
3300 v_plannedPushDate := PKHOLIDAYS.getnearestworkingdaytime
3301 (p_codmarket => v_codMarket,
3302 p_checkdate => v_pushDate ,
3303 p_flagdayafter => 'Y',
3304 p_codtimetabletype => CASE WHEN v_objType IN ('A') THEN 'A' ELSE 'L' END ,
3305 p_codnation => v_codNation );
3306
3307--- si controlla la differenza tra la data corrente e pushdate. Differenze minime
3308--- sono fisiologiche ( tempo di esecuzione della funzione )--> push immediata
3309--- altrimenti plan push in the future all'interno della tabella LDD_LEAD_PUSHTIME
3310
3311 IF v_plannedPushDate - v_pushDate > 5/1440 THEN
3312 BEGIN
3313 INSERT INTO LDD_LEAD_PUSHTIME ( CODLEAD,PLANNEDPUSHTIME )
3314 VALUES ( p_codlead,
3315 FROM_TZ( cast(v_plannedPushDate as timestamp),
3316 NVL(v_timeZoneRegion,'Europe/Rome') ) );
3317
3318 EXCEPTION
3319 WHEN DUP_VAL_ON_INDEX THEN
3320 UPDATE LDD_LEAD_PUSHTIME LPH
3321 SET LPH.PLANNEDPUSHTIME = FROM_TZ( cast(v_plannedPushDate as timestamp),
3322 NVL(v_timeZoneRegion,'Europe/Rome') )
3323 WHERE LPH.CODLEAD = p_codlead;
3324 END;
3325 END IF;
3326 END IF;
3327 EXCEPTION
3328 WHEN OTHERS THEN
3329--- log dell'errore senza interrompere il flusso ( al massimo non arriva un sms )
3330 x_procedureName := 'PKLDD_LEADS.updateLeadStatus';
3331 IF p_logHandle IS NULL THEN
3332 v_logHandle := pkLOG.Insertmaster(NULL,x_procedureName,NULL,'0');
3333 ELSE
3334 v_logHandle := p_logHandle;
3335 END IF;
3336 pklog.writedetail(vloghandle=>v_logHandle,
3337 llevel=>PKLOG.WARNING,
3338 pprocedurename=>x_procedureName,
3339 description=>'error : '||to_char(x_errCode)||' in managing PUSH TIME',
3340 details=>x_errMsg );
3341 END; --- fine gestione PUSH_WORKINGTIME
3342
3343 END IF; --- if p_codLeadStatus = 'AR'
3344
3345 END IF;
3346
3347 RETURN 1;
3348EXCEPTION
3349 WHEN OTHERS THEN
3350 x_errCode := sqlcode;
3351 x_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
3352 ROLLBACK TO SP001;
3353--- log dell'errore
3354 x_procedureName := 'PKLDD_LEADS.updateLeadStatus';
3355 IF p_logHandle IS NULL THEN
3356 v_logHandle := pkLOG.Insertmaster(NULL,x_procedureName,NULL,'0');
3357 ELSE
3358 v_logHandle := p_logHandle;
3359 END IF;
3360 pklog.writedetail(vloghandle=>v_logHandle,
3361 llevel=>PKLOG.WARNING,
3362 pprocedurename=>x_procedureName,
3363 description=>'error : '||to_char(x_errCode)||' in updating lead',
3364 details=>x_errMsg );
3365 RETURN 0;
3366END updateLeadStatus;
3367
3368--- Gestione della tabella LDD_REMINDER_NOTE
3369--- Se la nota e' nulla si effettua la cancellazione del dato
3370--- La funzione ritorna NULL se ha avuto esito negativo , altrimenti il codice LEAD
3371FUNCTION updateReminderNote
3372 ( p_codLead IN LDD_REMINDER_NOTES.CODLEAD%TYPE,
3373 p_reminderNote IN LDD_REMINDER_NOTES.REMINDER_NOTE%TYPE )
3374 RETURN LDD_REMINDER_NOTES.CODLEAD%TYPE IS
3375BEGIN
3376 IF p_reminderNote IS NULL THEN
3377 DELETE LDD_REMINDER_NOTES LRN
3378 WHERE LRN.CODLEAD = p_codLead;
3379 ELSE
3380 BEGIN
3381 INSERT INTO LDD_REMINDER_NOTES ( CODLEAD, REMINDER_NOTE )
3382 VALUES ( p_codLead, p_reminderNote );
3383 EXCEPTION
3384 WHEN DUP_VAL_ON_INDEX THEN
3385 UPDATE LDD_REMINDER_NOTES LRN
3386 SET LRN.REMINDER_NOTE = p_reminderNote
3387 WHERE LRN.CODLEAD = p_codLead
3388 AND LRN.REMINDER_NOTE != p_reminderNote;
3389 END;
3390
3391 END IF;
3392 RETURN p_codLead;
3393
3394EXCEPTION
3395 WHEN OTHERS THEN
3396 RETURN NULL;
3397END updateReminderNote;
3398
3399--- getNotManagedLead
3400--- Restituisce un elenco di lead non gestite in un determinato intervallo di ore.
3401--- Le ore sono da intendere come ore di calendario e non come ore lavorative
3402--- ( si basano sulla creation date)
3403FUNCTION getNotManagedLead
3404 (p_codmarket IN ldd_campaign_details.codmarket%TYPE,
3405 p_codlanguage IN ldd_lead.codlanguage%TYPE,
3406 p_leadage IN NUMBER,
3407 p_codmaindealer IN ldd_campaign_details.codmaindealer%TYPE DEFAULT NULL,
3408 p_codbrand IN ldd_campaign_details.codbrand%TYPE DEFAULT NULL,
3409 p_codcampaigntype IN ldd_campaign.codcampaigntype%TYPE DEFAULT NULL,
3410 p_codcampaignsource IN ldd_campaign.codcampaignsource%TYPE DEFAULT NULL)
3411 RETURN elinktypes.ref_collection IS
3412 mycursor elinktypes.ref_collection;
3413 v_codcampaignsource ldd_campaign.codcampaignsource%TYPE;
3414 v_codcampaigntype ldd_campaign.codcampaigntype%TYPE;
3415 --v_codleadext ldd_lead.codleadext%TYPE;
3416 --v_createddate ldd_lead.createddate%TYPE;
3417 --v_insertiondate ldd_lead.insertiondate%TYPE;
3418 v_leadids LISTOFVALUEN := LISTOFVALUEN();
3419 v_timezone MARKET.TIMEZONE_REGION%TYPE;
3420BEGIN
3421
3422 --- B30111 check if codcammpaignsource exists
3423 IF p_codcampaignsource IS NOT NULL THEN
3424 BEGIN
3425 SELECT ls.codcampaignsource
3426 INTO v_codcampaignsource
3427 --FROM ldd_campaign_types_source lts
3428 FROM ldd_campaign_sources ls
3429 WHERE ls.codcampaignsource = p_codcampaignsource;
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 OPEN mycursor FOR
3433 SELECT 1 AS error FROM DUAL;
3434 RETURN mycursor;
3435 END;
3436 END IF;
3437
3438 --- B30111 check if codcammpaigntype exists
3439 IF p_codcampaigntype IS NOT NULL THEN
3440 BEGIN
3441 SELECT lt.codcampaigntype
3442 INTO v_codcampaigntype
3443 --FROM ldd_campaign_types_source lts
3444 FROM ldd_campaign_types lt
3445 WHERE lt.codcampaigntype = p_codcampaigntype;
3446 EXCEPTION
3447 WHEN OTHERS THEN
3448 OPEN mycursor FOR
3449 SELECT 1 AS error FROM DUAL;
3450 RETURN mycursor;
3451 END;
3452 END IF;
3453
3454--- B29330 - Il confronto tra valori data di tipo diverso implica una trasformazione
3455--- automatica che permette il rapporto a parita' di time zone
3456--- In questo caso : ll.createddate > (SYSDATE - p_leadage / 24)
3457 IF p_codmaindealer IS NOT NULL THEN
3458
3459 SELECT ll.codlead BULK COLLECT
3460 INTO v_leadids
3461 FROM ldd_lead ll
3462 JOIN ldd_campaign_details lcd ON lcd.codcampaigndetail = ll.codcampaigndetail
3463 JOIN ldd_campaign lca ON lcd.codcampaign = lca.codcampaign
3464 WHERE ll.codleadstatus IN ('DA', 'DG')
3465 AND ll.untreated = 0
3466 AND lcd.codmarket = p_codmarket
3467 AND ll.codlanguage = p_codlanguage
3468 --B29510 AND ll.createddate > (SYSDATE - p_leadage / 24)
3469 AND ll.createddate < (SYSDATE - p_leadage / 24)
3470 AND lcd.codmaindealer = p_codmaindealer
3471 AND (p_codbrand IS NULL OR lcd.codbrand = p_codbrand)
3472 AND (p_codcampaigntype IS NULL OR lca.codcampaigntype = p_codcampaigntype)
3473 AND (p_codcampaignsource IS NULL OR lca.codcampaignsource = p_codcampaignsource)
3474 AND ll.objtype in ('L','A') --B34549
3475 and nvl(ll.communicationchannel, 'T') <> 'M'; --T16384
3476
3477 ELSE
3478
3479 SELECT ll.codlead BULK COLLECT
3480 INTO v_leadids
3481 FROM ldd_lead ll
3482 JOIN ldd_campaign_details lcd ON lcd.codcampaigndetail = ll.codcampaigndetail
3483 JOIN ldd_campaign lca ON lcd.codcampaign = lca.codcampaign
3484 WHERE ll.codleadstatus IN ('DA', 'DG')
3485 AND ll.untreated = 0
3486 AND lcd.codmarket = p_codmarket
3487 AND ll.codlanguage = p_codlanguage
3488 --B29510 AND ll.createddate > (SYSDATE - p_leadage / 24)
3489 AND ll.createddate < (SYSDATE - p_leadage / 24)
3490 AND (p_codmaindealer IS NULL OR lcd.codmaindealer = p_codmaindealer)
3491 AND (p_codbrand IS NULL OR lcd.codbrand = p_codbrand)
3492 AND (p_codcampaigntype IS NULL OR lca.codcampaigntype = p_codcampaigntype)
3493 AND (p_codcampaignsource IS NULL OR lca.codcampaignsource = p_codcampaignsource)
3494 AND ll.objtype in ('L','A') --B34549
3495 and nvl(ll.communicationchannel, 'T') <> 'M'; --T16384
3496
3497 END IF; --- end IF p_codmaindealer IS NOT NULL
3498
3499 FOR l_row IN 1 .. v_leadids.COUNT LOOP
3500 v_timezone := pktimezonemanagement.getTimeZoneRegionByCodLead(v_leadids(l_row));
3501
3502 UPDATE ldd_lead ll
3503 SET ll.untreated = 1,
3504 ll.date_untreated = systimestamp at time zone upper(v_timezone)
3505 WHERE ll.codlead = v_leadids(l_row);
3506 END LOOP;
3507 COMMIT;
3508
3509 OPEN mycursor FOR
3510 SELECT ll.codlead AS leadid,
3511 --ll.codleadext AS leadidext,
3512 ll.codleadhub AS leadidext, -- B43199
3513 ll.createddate AS creation_date,
3514 ll.insertiondate AS insertion_date
3515 FROM ldd_lead ll
3516 WHERE ll.codlead IN (SELECT COLUMN_VALUE FROM TABLE(v_leadids));
3517
3518 RETURN mycursor;
3519
3520EXCEPTION
3521 WHEN OTHERS THEN
3522 return null;
3523END getNotManagedLead;
3524
3525/* getManagedLead
3526 Restituisce un elenco di lead gestite in un determinato intervallo di ore.
3527 Le ore sono da intendere come ore di calendario e non come ore lavorative
3528( si basano sulla creation date).
3529restituisce la prima azione inserita sulla lead dopo la sua creazione e il lead result*/
3530FUNCTION getmanagedlead
3531 (p_codmarket IN ldd_campaign_details.codmarket%TYPE,
3532 p_codlanguage IN ldd_lead.codlanguage%TYPE,
3533 p_leadage IN NUMBER,
3534 p_codmaindealer IN ldd_campaign_details.codmaindealer%TYPE DEFAULT NULL,
3535 p_codbrand IN ldd_campaign_details.codbrand%TYPE DEFAULT NULL,
3536 p_codcampaigntype IN ldd_campaign.codcampaigntype%TYPE DEFAULT NULL,
3537 p_codcampaignsource IN ldd_campaign.codcampaignsource%TYPE DEFAULT NULL)
3538 RETURN elinktypes.ref_collection IS
3539 mycursor elinktypes.ref_collection;
3540 v_codcampaignsource ldd_campaign.codcampaignsource%TYPE;
3541 v_codcampaigntype ldd_campaign.codcampaigntype%TYPE;
3542 --v_codleadext ldd_lead.codleadext%TYPE;
3543 --v_createddate ldd_lead.createddate%TYPE;
3544 --v_insertiondate ldd_lead.insertiondate%TYPE;
3545 v_leadids LISTOFVALUEN := LISTOFVALUEN();
3546BEGIN
3547
3548 --- B30111 check if codcammpaignsource exists
3549 IF p_codcampaignsource IS NOT NULL THEN
3550 BEGIN
3551 SELECT ls.codcampaignsource
3552 INTO v_codcampaignsource
3553 --FROM ldd_campaign_types_source lts
3554 FROM ldd_campaign_sources ls
3555 WHERE ls.codcampaignsource = p_codcampaignsource;
3556 EXCEPTION
3557 WHEN OTHERS THEN
3558 OPEN mycursor FOR
3559 SELECT 1 AS error FROM DUAL;
3560 RETURN mycursor;
3561 END;
3562 END IF;
3563
3564 --- B30111 check if codcammpaigntype exists
3565 IF p_codcampaigntype IS NOT NULL THEN
3566 BEGIN
3567 SELECT lt.codcampaigntype
3568 INTO v_codcampaigntype
3569 FROM ldd_campaign_types lt
3570 WHERE lt.codcampaigntype = p_codcampaigntype;
3571 EXCEPTION
3572 WHEN OTHERS THEN
3573 OPEN mycursor FOR
3574 SELECT 1 AS error FROM DUAL;
3575 RETURN mycursor;
3576 END;
3577 END IF;
3578
3579--- B29330 - Il confronto tra valori data di tipo diverso implica una trasformazione
3580--- automatica che permette il rapporto a parita' di time zone
3581--- In questo caso : ll.createddate > (SYSDATE - p_leadage / 24)
3582--- Per motivi di perfromances la select si differenzia a seconda della presenza
3583--- del parametro p_codMainDealer
3584 IF p_codMainDealer IS NOT NULL THEN
3585
3586 OPEN mycursor FOR
3587 SELECT ll.codlead AS leadid,
3588 ll.codleadstatus,
3589 --ll.codleadext AS leadidext,
3590 ll.codleadhub AS leadidext, -- B43199
3591 ll.createddate AS creation_date,
3592 ll.insertiondate AS insertion_date,
3593--- T8514 - I campi data della tabella CRM_ACTIONCONTACT diventano TIMESTAMP with time zone
3594 TO_CHAR(act.creationdate,'YYYY-MM-DD HH24:MI:SS') AS first_action_date,
3595 TO_CHAR(act.completedate,'YYYY-MM-DD HH24:MI:SS') AS closure_date,
3596 (SELECT ldn.codleadresult
3597 FROM ldd_leadnote ldn
3598 WHERE ldn.codlead = ll.codlead
3599 AND ll.codleadstatus = 'AR') AS codleadresult
3600 FROM ldd_lead ll
3601 JOIN crm_actioncontact act
3602 ON ACT.CODCONTACT = LL.CODCONTACT
3603 JOIN ldd_campaign_details lcd
3604 ON lcd.codcampaigndetail = ll.codcampaigndetail
3605 JOIN ldd_campaign lca
3606 ON lcd.codcampaign = lca.codcampaign
3607 WHERE ll.codleadstatus IN ('IG', 'AR')
3608 AND lcd.codmarket = p_codmarket
3609 AND ll.codlanguage = p_codlanguage
3610 AND ll.createddate > (SYSDATE - p_leadage / 24)
3611 AND act.CREATIONDATE = ( SELECT MIN(AC2.CREATIONDATE)
3612 FROM crm_actioncontact AC2
3613 WHERE AC2.CODCONTACT = ACT.CODCONTACT )
3614 AND lcd.codmaindealer = p_codmaindealer
3615 AND (p_codbrand IS NULL OR lcd.codbrand = p_codbrand)
3616 AND (p_codcampaigntype IS NULL OR
3617 lca.codcampaigntype = p_codcampaigntype)
3618 AND (p_codcampaignsource IS NULL OR
3619 lca.codcampaignsource = p_codcampaignsource)
3620 ORDER BY LL.CODLEAD;
3621
3622 ELSE
3623
3624 OPEN mycursor FOR
3625 SELECT ll.codlead AS leadid,
3626 ll.codleadstatus,
3627 --ll.codleadext AS leadidext,
3628 ll.codleadhub AS leadidext, -- B43199
3629 ll.createddate AS creation_date,
3630 ll.insertiondate AS insertion_date,
3631--- T8514 - I campi data della tabella CRM_ACTIONCONTACT diventano TIMESTAMP with time zone
3632 TO_CHAR(act.creationdate,'YYYY-MM-DD HH24:MI:SS') AS first_action_date,
3633 TO_CHAR(act.completedate,'YYYY-MM-DD HH24:MI:SS') AS closure_date,
3634 (SELECT ldn.codleadresult
3635 FROM ldd_leadnote ldn
3636 WHERE ldn.codlead = ll.codlead
3637 AND ll.codleadstatus = 'AR') AS codleadresult
3638 FROM ldd_lead ll
3639 JOIN crm_actioncontact act
3640 ON ACT.CODCONTACT = LL.CODCONTACT
3641 JOIN ldd_campaign_details lcd
3642 ON lcd.codcampaigndetail = ll.codcampaigndetail
3643 JOIN ldd_campaign lca
3644 ON lcd.codcampaign = lca.codcampaign
3645 WHERE ll.codleadstatus IN ('IG', 'AR')
3646 AND lcd.codmarket = p_codmarket
3647 AND ll.codlanguage = p_codlanguage
3648 AND ll.createddate > (SYSDATE - p_leadage / 24)
3649 AND act.CREATIONDATE = ( SELECT MIN(AC2.CREATIONDATE)
3650 FROM crm_actioncontact AC2
3651 WHERE AC2.CODCONTACT = ACT.CODCONTACT )
3652 AND (p_codmaindealer IS NULL OR lcd.codmaindealer = p_codmaindealer)
3653 AND (p_codbrand IS NULL OR lcd.codbrand = p_codbrand)
3654 AND (p_codcampaigntype IS NULL OR
3655 lca.codcampaigntype = p_codcampaigntype)
3656 AND (p_codcampaignsource IS NULL OR
3657 lca.codcampaignsource = p_codcampaignsource)
3658 ORDER BY LL.CODLEAD;
3659
3660 END IF; --- end IF p_codMainDealer IS NOT NULL
3661
3662 RETURN mycursor;
3663EXCEPTION
3664 WHEN OTHERS THEN
3665 return null;
3666END getmanagedlead;
3667
3668/* isUnmanagedToMask
3669Returns 1 if input codlead is to mask (pin number to be hidden) in the unmanaged process, 0 otherwise
3670
3671Conditions to be masked (AND logic):
36721) ldd_lead.treated = 1
36732) ldd_lead.callcenter_callback = 0
36743) (sysdate - ldd_lead.date_untreated)[in the same timezone] < pkmarketparams.findValueByPrimaryKey(
3675 CRM_UNMANAGED_NOT_CONTACTED_BY_CC,
3676 ldd_campaingdetails.codmarket,
3677 1,
3678 0)
3679*/
3680FUNCTION isUnmanagedToMask(
3681 p_codlead IN ldd_lead.codlead%TYPE,
3682 p_codmarket IN ldd_campaign_details.codmarket%TYPE)
3683 RETURN number is
3684 isToMask NUMBER;
3685
3686 v_hoursAfterUntreated marketparams.mpvalue%TYPE;
3687
3688BEGIN
3689 v_hoursAfterUntreated := pkmarketparams.findValueByPrimaryKey(
3690 'CRM_UNMANAGED_NOT_CONTACTED_BY_CC',
3691 p_codmarket,
3692 1,
3693 0);
3694
3695 isToMask := 0;
3696
3697 select 1
3698 into isToMask
3699 from ldd_lead l
3700 where l.codlead = p_codlead
3701 and l.codleadstatus in ('DA', 'DG')
3702 and l.untreated = 1
3703 and l.callcenter_callback = 0
3704 and trunc(24*((cast(systimestamp at time zone extract(TIMEZONE_REGION from l.createddate) as date)) - (cast(l.date_untreated as date))), 4) < v_hoursAfterUntreated;
3705
3706 return isToMask;
3707EXCEPTION
3708 WHEN NO_DATA_FOUND THEN
3709 return 0;
3710 WHEN OTHERS THEN
3711 return 0;
3712END isUnmanagedToMask;
3713
3714--notifyLeadAssigner
3715-- T8508 - Inserisce nella tabella ALR_SERVICESQUEUE una riga per il servizio SMS
3716-- da inviare all'assegnatario della LEAD (USERGID)
3717-- T8552 -- SMS spagna..per evitare l'uso della ptl_mail_appl
3718-- T10534 invio in orari lavorativi e condizionamento parametri di invio
3719FUNCTION notifyLeadAssigner
3720 (p_codlead IN ldd_lead.codlead%TYPE,
3721 p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE,
3722 p_usergid IN LDD_LEAD.USERGID%TYPE,
3723 p_idAlrService IN ALR_SERVICES.Idalr_Service%TYPE)
3724 RETURN number is
3725 LEAREC LDD_LEAD%RowType;
3726 v_usergid LDD_LEAD.USERGID%TYPE;
3727 v_isDeferred ALR_MARKETAVAILABILITY.Is_Deferred%TYPE;
3728 v_smsCreateDate TIMESTAMP := SYSDATE;
3729 v_codNation NATION.CODNATION%TYPE;
3730
3731BEGIN
3732 -- verifichiamo se la lead ? soggetta a notifiche
3733 SELECT LEAD.*
3734 INTO LEAREC
3735 FROM LDD_LEAD LEAD
3736 join LDD_CAMPAIGN_DETAILS LCD
3737 on LEAD.CODCAMPAIGNDETAIL = LCD.Codcampaigndetail
3738 join LDD_CAMPAIGN LCP
3739 on (LCP.CODCAMPAIGN = LCD.CODCAMPAIGN)
3740 --to avoid filtering on sms activation for lead source/type
3741 LEFT JOIN LDD_LEAD_PROCESS_PARAMS params
3742 on params.codmarket = LCD.Codmarket
3743 and params.codbrand = LCD.Codbrand
3744 and params.codcampaignsource = LCP.Codcampaignsource
3745 and params.codcampaigntype = LCP.Codcampaigntype
3746 WHERE LEAD.Codlead = p_codlead
3747 --we have two different parameter for each status
3748 and (LEAD.CODLEADSTATUS = 'DG' and params.send_sms_dg = '1');
3749
3750 --se la lead ? stata assegnata verifichiamo che l'utente sia iscritto alle notifiche
3751 select distinct elu.loginname
3752 into v_usergid
3753 from elinkuser elu
3754 join ALR_SERVICESUBSCRIPTIONS subscr
3755 on subscr.LOGINNAME = elu.loginname
3756 and subscr.IDALR_SERVICE = p_idAlrService
3757 where elu.loginname = LEAREC.Usergid
3758 and elu.deleted = 0
3759 and subscr.CODALR_CHANNEL = 'S';
3760-- se ha deciso di non ricevere segnalazioni...non lo inseriamo
3761 if (v_usergid is not null) then
3762
3763--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
3764 SELECT PKNATION.getNationForUser(LCD.codmarket,LCD.codDealer)
3765 INTO v_codNation
3766 FROM LDD_CAMPAIGN_DETAILS LCD
3767 WHERE LCD.CODCAMPAIGNDETAIL = LEAREC.CODCAMPAIGNDETAIL;
3768
3769 --coerente con il default
3770 select nvl(is_deferred,
3771 '0')
3772 into v_isDeferred
3773 from alr_marketavailability alrmkt
3774 where alrmkt.codmarket = p_codMarket
3775 and alrmkt.idalr_service = p_idAlrService
3776 and alrmkt.codalr_channel = 'S';
3777 --T10534 if not working hour and deferred...uses lead create_date
3778 --seams to be duplicated code, as create_date for lead is already calculated...
3779 --but better 2 checks...
3780--- T12851 - Le leads after sales usano l'apposito calendario 'A'
3781 IF ( Pkholidays.isWorkingHour(p_codMarket,
3782 SYSDATE,
3783 CASE WHEN LEAREC.OBJTYPE IN ('A') THEN 'A' ELSE 'L' END ) = 0 AND v_isDeferred = '1')
3784 then
3785 v_smsCreateDate := Pkholidays.getNearestWorkingDayTime
3786 (p_codMarket,
3787 p_checkDate => sysdate,
3788 p_flagDayAfter => 'Y',
3789 p_codTimeTableType => CASE WHEN LEAREC.OBJTYPE IN ('A') THEN 'A' ELSE 'L' END,
3790 p_codNation => v_codNation );
3791 end if;
3792
3793 Insert into ALR_SERVICESQUEUE
3794 (CODLEAD,
3795 CODMARKET,
3796 USERGID,
3797 IDALR_SERVICE,
3798 CREATE_DATE)
3799 Values
3800 (p_codlead,
3801 p_codMarket,
3802 v_usergid,
3803 p_idAlrService,
3804 v_smsCreateDate);
3805 return 1;
3806 else
3807 return 0;
3808 end if;
3809 return - 1;
3810 EXCEPTION
3811 WHEN NO_DATA_FOUND THEN
3812 return 0;
3813 WHEN OTHERS THEN
3814 return - 1;
3815 END notifyLeadAssigner;
3816
3817FUNCTION notifyLeadSellers(p_codlead IN ldd_lead.codlead%TYPE,
3818 p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE,
3819 p_idAlrService IN ALR_SERVICES.Idalr_Service%TYPE)
3820 RETURN number is
3821 LEAREC LDD_LEAD%RowType;
3822 v_isDeferred ALR_MARKETAVAILABILITY.Is_Deferred%TYPE;
3823 v_smsCreateDate TIMESTAMP := SYSDATE;
3824 v_codNation NATION.CODNATION%TYPE;
3825
3826 CURSOR v_sellers is
3827 select distinct elu.loginname
3828 from ldd_lead ll
3829 join ldd_campaign_details lcd
3830 on ll.codcampaigndetail = lcd.codcampaigndetail
3831 join elinkuser elu
3832 on elu.codmarket = lcd.codmarket
3833 and elu.coddealer = lcd.codmaindealer
3834 join ALR_SERVICESUBSCRIPTIONS subscr
3835 on subscr.LOGINNAME = elu.loginname
3836 and subscr.IDALR_SERVICE = p_idAlrService
3837 where ll.codlead = p_codlead
3838 and elu.deleted = 0
3839 and subscr.CODALR_CHANNEL = 'S';
3840
3841BEGIN
3842 -- verifichiamo se la lead ? soggetta a notifiche
3843 SELECT LEAD.*
3844 INTO LEAREC
3845 FROM LDD_LEAD LEAD
3846 join LDD_CAMPAIGN_DETAILS LCD
3847 on LEAD.CODCAMPAIGNDETAIL = LCD.Codcampaigndetail
3848 join LDD_CAMPAIGN LCP
3849 on (LCP.CODCAMPAIGN = LCD.CODCAMPAIGN)
3850 --to avoid filtering on sms activation for lead source/type
3851 LEFT JOIN LDD_LEAD_PROCESS_PARAMS params
3852 on params.codmarket = LCD.Codmarket
3853 and params.codbrand = LCD.Codbrand
3854 and params.codcampaignsource = LCP.Codcampaignsource
3855 and params.codcampaigntype = LCP.Codcampaigntype
3856 WHERE LEAD.Codlead = p_codlead
3857 --we have two different parameter for each status
3858 and (LEAD.CODLEADSTATUS = 'DA' and params.send_sms_da = '1');
3859
3860--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
3861 SELECT PKNATION.getNationForUser(LCD.codmarket,LCD.codDealer)
3862 INTO v_codNation
3863 FROM LDD_CAMPAIGN_DETAILS LCD
3864 WHERE LCD.CODCAMPAIGNDETAIL = LEAREC.CODCAMPAIGNDETAIL;
3865
3866 --coerente con il default
3867 select nvl(is_deferred,
3868 '0')
3869 into v_isDeferred
3870 from alr_marketavailability alrmkt
3871 where alrmkt.codmarket = p_codMarket
3872 and alrmkt.idalr_service = p_idAlrService
3873 and alrmkt.codalr_channel = 'S';
3874 --T10534 if not working hour and deferred...uses lead create_date
3875 --seams to be duplicated code, as create_date for lead is already calculated...
3876 --but better 2 checks...
3877--- T12851 - Le leads after sales usano l'apposito calendario 'A'
3878 IF (Pkholidays.isWorkingHour(p_codMarket,
3879 SYSDATE,
3880 CASE WHEN LEAREC.OBJTYPE IN ('A') THEN 'A' ELSE 'L' END ) = 0 AND v_isDeferred = '1')
3881 then
3882 v_smsCreateDate := Pkholidays.getNearestWorkingDayTime
3883 (p_codMarket,
3884 p_checkDate => sysdate,
3885 p_flagDayAfter => 'Y',
3886 p_codTimeTableType => CASE WHEN LEAREC.OBJTYPE IN ('A') THEN 'A' ELSE 'L' END,
3887 p_codNation => v_codNation );
3888 end if;
3889
3890 FOR rc IN v_sellers LOOP
3891 Insert into ALR_SERVICESQUEUE
3892 (CODLEAD,
3893 CODMARKET,
3894 USERGID,
3895 IDALR_SERVICE,
3896 CREATE_DATE)
3897 Values
3898 (p_codlead,
3899 p_codMarket,
3900 rc.loginname,
3901 p_idAlrService,
3902 v_smsCreateDate);
3903 end loop;
3904
3905 return 1;
3906 exception
3907 WHEN NO_DATA_FOUND THEN
3908 return 0;
3909 when others then
3910 --tanto per ora non viene utilizzato dai chiamanti
3911
3912 return - 1;
3913 END notifyLeadSellers;
3914
3915--- T9325 - Assegnazione del contatto ad una LEAD. In caso di phone-lead le leads
3916--- possono nascere senza contatto
3917--- Riotorna : OK-->codice Lead ( numero positivo )
3918--- KO-->codice errore ( numero negativo )
3919FUNCTION updateLeadAssignContact
3920 ( p_codLead IN LDD_LEAD.CODLEAD%TYPE,
3921 p_codContact IN LDD_LEAD.CODCONTACT%TYPE )
3922 RETURN LDD_LEAD.CODLEAD%TYPE IS
3923BEGIN
3924--- Non ha senso che il parametro p_codContact sia NULL
3925 UPDATE LDD_LEAD LEA
3926 SET LEA.CODCONTACT = NVL(p_codContact,LEA.CODCONTACT)
3927 WHERE LEA.CODLEAD = p_codLead;
3928
3929--- T9322 - Eventuali azioni CRM relative alla lead vengono associate
3930 UPDATE CRM_ACTIONCONTACT CAC
3931 SET CAC.CODCONTACT = p_codContact
3932 WHERE CAC.CODLEAD = p_codLead
3933 AND CAC.CODCONTACT IS NULL;
3934
3935--- La modifica del contatto potra' in seguito essere spostata su PKCONTACT
3936 UPDATE CONTACT CNT
3937 SET CNT.CODCONTACTSTATUS = 'O'
3938 WHERE CNT.CODCONTACT = p_codContact;
3939
3940 RETURN p_codLead;
3941EXCEPTION
3942 WHEN OTHERS THEN
3943 RETURN sqlcode;
3944END updateLeadAssignContact;
3945
3946--- Pulizia records obsoleti relativi a :
3947--- - LDD_LEADS_SLARANGES
3948--- - LDD_LEAD_PUSHTIME
3949--- Ritorna : >0 ( ok, numero di cancellazioni ) / <0 errore
3950FUNCTION cleanupLeads
3951 ( p_logHandle IN LOGMASTER.SEQUENCEID%TYPE DEFAULT NULL )
3952 RETURN NUMBER IS
3953
3954 CURSOR C01 IS
3955 SELECT ROWID as ROWID#
3956 FROM LDD_LEADS_SLARANGES LSR
3957 WHERE EXISTS ( SELECT 1
3958 FROM LDD_LEAD LEA
3959 WHERE LEA.CODLEADSTATUS = 'AR'
3960 AND LEA.CODLEAD = LSR.CODLEAD )
3961 AND LSR.CODSLAEVENT IN ('L','X'); -- T15301
3962
3963 TYPE C01TYP IS TABLE OF C01%RowType;
3964 C01TAB C01TYP;
3965 v_idxBulk PLS_INTEGER;
3966
3967 v_logHandle LOGMASTER.SEQUENCEID%TYPE;
3968 v_procedureName CONSTANT LOGMASTER.LOADERNAME%TYPE := 'PKLDD_LEADS.cleanupLeads';
3969 v_errCode NUMBER;
3970 v_errMsg VARCHAR2(1024);
3971 v_delCnt NUMBER := 0;
3972 v_dellph NUMBER := 0;
3973
3974BEGIN
3975
3976 IF p_logHandle IS NOT NULL THEN
3977 v_logHandle := p_logHandle;
3978 ELSE
3979 v_logHandle := PKLOG.Insertmaster(NULL,v_procedureName,NULL, '0' );
3980 END IF;
3981
3982 pklog.writedetail(vloghandle=>v_logHandle,
3983 llevel=>PKLOG.INFO,
3984 pprocedurename=>v_procedureName,
3985 description=>'cleanup LDD_LEADS_SLARANGES ( event in L, X )'); -- t15301
3986
3987--- Pulizia dei record relativi alle soglie di SLA per eventtype L
3988--- per leads archiviate ( il valore del semaforo e' all'interno della
3989--- lead stessa nel campo FINAL_SEVERITYLEVELFIELD
3990--- eventualmente si possono ricalcolare con pkldd_sla.updateslarange
3991 OPEN C01;
3992 LOOP
3993 FETCH C01 BULK COLLECT INTO C01TAB LIMIT 10000;
3994 EXIT WHEN C01TAB.COUNT = 0 ;
3995
3996 BEGIN
3997 FORALL v_idxBulk IN C01TAB.FIRST .. C01TAB.LAST SAVE EXCEPTIONS
3998 DELETE LDD_LEADS_SLARANGES WHERE ROWID = C01TAB(v_idxBulk).ROWID#;
3999 v_delCnt := v_delCnt + sql%RowCount;
4000 EXCEPTION
4001--- when DML_ERRORS then
4002--- FOR v_idxbulk in 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
4003--- pklog.writedetail(vloghandle=>v_logHandle,
4004--- llevel=>PKLOG.SEVERE,
4005--- pprocedurename=>v_procedureName,
4006--- description=>'error : '||SQL%BULK_EXCEPTIONS(v_idxBulk).ERROR_CODE );
4007--- END LOOP;
4008
4009 WHEN OTHERS then
4010 v_ErrCode := sqlCode;
4011 v_ErrMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
4012 pklog.writedetail(vloghandle=>v_logHandle,
4013 llevel=>PKLOG.SEVERE,
4014 pprocedurename=>v_procedureName,
4015 description=>'Errore : '||to_char(v_ErrCode),
4016 details=>v_errMsg );
4017
4018 END;
4019 COMMIT;
4020 END LOOP;
4021 CLOSE C01;
4022
4023 pklog.writedetail(vloghandle=>v_logHandle,
4024 llevel=>PKLOG.INFO,
4025 pprocedurename=>v_procedureName,
4026 description=>'delete LDD_LEADS_SLARANGES : '||v_delCnt );
4027
4028--- Pulizia dei record relativi alla tabella dei PUSHTIME per leads
4029--- archiviate ( poca roba )
4030 DELETE LDD_LEAD_PUSHUSERGID PUS
4031 WHERE EXISTS ( SELECT 1
4032 FROM LDD_LEAD LEA
4033 WHERE LEA.CODLEADSTATUS = 'AR'
4034 AND LEA.CODLEAD = PUS.CODLEAD ) ;
4035
4036 DELETE LDD_LEAD_PUSHTIME LPH
4037 WHERE EXISTS ( SELECT 1
4038 FROM LDD_LEAD LEA
4039 WHERE LEA.CODLEADSTATUS = 'AR'
4040 AND LEA.CODLEAD = LPH.CODLEAD ) ;
4041
4042 v_dellph := sql%RowCount;
4043 pklog.writedetail(vloghandle=>v_logHandle,
4044 llevel=>PKLOG.INFO,
4045 pprocedurename=>v_procedureName,
4046 description=>'delete LDD_LEAD_PUSHTIME : '||v_dellph );
4047
4048 IF p_logHandle IS NULL THEN
4049 PKLOG.updateMaster(v_logHandle, 0);
4050 END IF;
4051
4052 RETURN v_delCnt + v_dellph;
4053
4054EXCEPTION
4055 WHEN OTHERS then
4056 v_ErrCode := sqlCode;
4057 v_ErrMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
4058 pklog.writedetail(vloghandle=>v_logHandle,
4059 llevel=>PKLOG.SEVERE,
4060 pprocedurename=>v_procedureName,
4061 description=>'Errore : '||to_char(v_ErrCode),
4062 details=>v_errMsg );
4063 ROLLBACK;
4064 RETURN v_ErrCode;
4065
4066END cleanupLeads;
4067
4068--T12154 Phone Lead Management as Sales
4069
4070function findPhoneLeadToAssignDetails
4071 (p_codMainDealer in ldd_campaign_details.codmaindealer%type,
4072 p_codMarket in ldd_campaign_details.codmarket%type,
4073 p_codLanguage in language.codlanguage%type,
4074 p_objTypeArray in objectTypeTable)
4075 return elinkTypes.ref_collection is
4076 myCursor elinkTypes.ref_collection;
4077 x_checkDate CONSTANT DATE := SYSDATE;
4078 v_objTypeArray LISTOFVALUEC;
4079 v_objTypeCount NUMBER;
4080 v_codNation NATION.CODNATION%TYPE;
4081BEGIN
4082 v_objTypeArray := LISTOFVALUEC();
4083 IF p_objTypeArray IS NOT NULL AND p_objTypeArray.count > 0
4084 THEN
4085 for idx in p_objTypeArray.first .. p_objTypeArray.last loop
4086 v_objTypeArray.EXTEND;
4087 v_objTypeArray(idx) := p_objTypeArray(idx);
4088 end loop;
4089 v_objTypeCount := v_objTypeArray.count;
4090 ELSE
4091 v_objTypeCount := 0;
4092 END IF;
4093
4094--- T16217 - Le festivita' sono suddivise per nazione. Nel calcolo degli sla La nazione e' Mandatory!!!
4095 v_codNation := PKNATION.getNationForUser(p_codmarket,p_codMainDealer);
4096
4097 open myCursor for
4098 select LEA.CODLEAD, LEA.Mobilephone as TELEPHONE,
4099 CASE
4100 WHEN LEA.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
4101 LEA.FINAL_SEVERITYLEVELFIELD
4102 ELSE
4103 NVL((select MAX(LSR.SEVERITYLEVELFIELD) KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
4104 FROM LDD_LEADS_SLARANGES LSR
4105 WHERE LSR.CODLEAD = LEA.CODLEAD
4106 --AND LSR.CODSLAEVENT = 'L'
4107 AND LSR.CODSLAEVENT = DECODE(NVL(lea.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
4108 AND LSR.SLALIMIT > x_checkDate),
4109 'R')
4110 END AS STATUS,
4111--- T12363 - la funzione getworkinghours e' lenta per differenze di tempo elevate
4112--- se piu' di un mese si torna -1
4113 CASE WHEN LEA.CREATEDDATE < ADD_MONTHS(SYSDATE,-1) THEN
4114 -1
4115 ELSE
4116--- T12851 - Gestione orari di lavoro differenti per AfterSales ('A')
4117 ROUND ( ( SELECT pkholidays.getworkinghours
4118 (CAST(LEA.CREATEDDATE as DATE),
4119 CAST(SYSTIMESTAMP AT TIME ZONE TO_CHAR(LEA.CREATEDDATE, 'TZR') AS DATE),
4120 p_codMarket,
4121 CASE WHEN LEA.OBJTYPE = 'A' THEN 'A' ELSE 'L' END,
4122 v_codNation )
4123 FROM DUAL) , 2 )
4124 END as HOURS,
4125 TO_CHAR(LEA.LASTMODIFIEDDATE, 'YYYY-MM-DD HH24:MI:SS') as LASTMODIFIEDDATE,
4126 LDT.PRIORITY, LCS.DESCRIPTION as LEAD_SOURCE,
4127 TO_CHAR(LEA.CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS') as CREATEDDATE,
4128 (SELECT LTY.DESCRIPTION
4129 FROM LDD_CAMPAIGN_TYPES LTY
4130 WHERE LTY.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE) as TYPE_DESC,
4131 LDT.CODBRAND,
4132 (SELECT CODBRAND
4133 FROM LDD_HUB_MASTERKEY
4134 WHERE CODMASTERKEY =
4135 (SELECT CODMASTERKEY
4136 from LDD_LEAD_HUB_MASTERKEY
4137 WHERE CODLEAD = LEA.CODLEAD)) as BRAND,
4138 (SELECT LDP.DESCRPRODUCT
4139 FROM LDD_PRODUCT LDP
4140 WHERE LDP.CODLEAD = LEA.CODLEAD) as MODEL, LEA.CODCONTACT,
4141 CASE
4142 WHEN CT.CODCONTACTTYPE IN ('P', 'R') THEN
4143 (SELECT PRS.LASTNAME || ' ' || PRS.FIRSTNAME
4144 FROM PERSON PRS
4145 WHERE PRS.CODPERSON = CT.CODCONTACT)
4146 ELSE
4147 (SELECT CPY.COMPANYNAME
4148 FROM COMPANY CPY
4149 WHERE CPY.CODCOMPANY = CT.CODCONTACT)
4150 END AS CONTACT,
4151 (SELECT CTY.DESCR_CITY
4152 FROM CITY CTY
4153 WHERE CTY.CODCITY = CT.CODCITY
4154 AND CTY.CODDEPARTMENT = CT.CODDEPARTMENT
4155 AND CTY.CODREGION = CT.CODREGION
4156 AND CTY.CODNATION = CT.CODNATION) as CITY,
4157 CASE
4158 WHEN LCP.CODCAMPAIGNSOURCE not in ('DC') THEN
4159 LCP.NAMECAMPAIGN
4160 ELSE
4161 LCS.DESCRIPTION || ': ' || LCP.NAMECAMPAIGN
4162 END as CAMPAIGN_NAME, LTS.TOBECOUNTED, LEA.OBJTYPE,
4163 (SELECT DLL.ADDRESS || ' - ' || DLL.ZIP_NAME || ' - ' ||
4164 DLL.TOWN_NAME
4165 FROM DEALERLOCATION DLL
4166 WHERE DLL.CODMARKET = LDT.CODMARKET
4167 AND DLL.CODDEALER = LDT.CODDEALER
4168 AND DLL.CODLOCATION = LDT.CODDEALERLOCATION) AS DEALERADDRESS,
4169 LEA.UNTREATED, LEA.CALLCENTER_CALLBACK
4170 from TRG_LDD_LEAD TLL
4171 join LDD_CAMPAIGN_DETAILS LDT
4172 on (LDT.CODCAMPAIGNDETAIL = TLL.CODCAMPAIGNDETAIL)
4173 JOIN ldd_lead LEA
4174 ON TLL.CODLEAD = LEA.CODLEAD
4175 join LDD_CAMPAIGN LCP
4176 on (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
4177 join LDD_CAMPAIGN_TYPES_SOURCE LTS
4178 on (LTS.CODCAMPAIGNSOURCE = LCP.CODCAMPAIGNSOURCE AND
4179 LTS.CODCAMPAIGNTYPE = LCP.CODCAMPAIGNTYPE)
4180 join LDD_CAMPAIGN_SOURCES LCS
4181 on (LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE)
4182 join ldd_trackback LTB
4183 on TLL.CODLEAD = ltb.codlead
4184 left join CONTACT CT
4185 on (CT.CODCONTACT = LEA.CODCONTACT)
4186 where TLL.CODLEADSTATUS in ('IG')
4187 AND ltb.mngd_only_on_tb = 'Y'
4188 AND (v_objTypeCount = 0 OR LEA.OBJTYPE IN ('P'))
4189 AND LDT.CODMAINDEALER = p_codMainDealer
4190 AND LDT.CODMARKET = p_codMarket
4191 AND LTS.ISDELETED = 0
4192 order by LEA.CREATEDDATE desc;
4193
4194 return myCursor;
4195
4196end findPhoneLeadToAssignDetails;
4197
4198function findLeadManageLoginPriv(p_codMainDealer in ldd_dwh_fieldcounter.codmaindealer%type,
4199 p_codMarket in ldd_dwh_fieldcounter.codmarket%type)
4200 return elinkTypes.ref_collection is
4201 myCursor elinkTypes.ref_collection;
4202BEGIN
4203 open myCursor for
4204 select velu.loginname as LOGIN,
4205 velu.firstname || ' ' || velu.lastname as SALESMAN,
4206 --all leads!if the seller has an interview...it is still a lead!!!
4207 (select sum(ldfc.counter) as LEAD
4208 from ldd_dwh_fieldcounter ldfc
4209--- B35746 - aggiunto Contatore fca capital ("Q")
4210 where ldfc.codfieldcount in ('M', 'S', 'Q' )
4211 and ldfc.codmarket = p_codMarket
4212 and ldfc.codmaindealer = p_codMainDealer
4213 and ldfc.usergid = velu.loginname) as lead,
4214 (select pkcrm_availability.findLoginParam(p_codMarket,
4215 velu.loginname,
4216 'CRM_LEAD_HANDLER',
4217 p_codMainDealer) from dual) as CAN_MNG_SALES,
4218 (select pkcrm_availability.findLoginParam(p_codMarket,
4219 velu.loginname,
4220 'CRM_LEAD_HANDLER_AS',
4221 p_codMainDealer)from dual) as CAN_MNG_ASALES
4222 FROM --CR4721 simplified Login:
4223 v_elinkusers velu
4224 where NVL(velu.deleted, 0) = 0
4225 and velu.CODMAINDEALER = p_codMainDealer
4226 and velu.codmarket = p_codMarket
4227 --- T10021 - controllo anche abilitazione aftersales
4228 AND ((select pkcrm_availability.findLoginParam(p_codMarket,
4229 velu.loginname,
4230 'CRM_LEAD_HANDLER',
4231 p_codMainDealer) from dual) = 1 or
4232 (select pkcrm_availability.findLoginParam(p_codMarket,
4233 velu.loginname,
4234 'CRM_LEAD_HANDLER_AS',
4235 p_codMainDealer) from dual) = 1)
4236 ORDER BY 2, 1;
4237
4238 return myCursor;
4239end findLeadManageLoginPriv;
4240
4241function reassignWorkingPhoneLead(p_codLead in ldd_lead.codlead%type,
4242 p_usergid in ldd_lead.usergid%type,
4243 p_usergidAssigner in ELINKUSER.LOGINNAME%TYPE DEFAULT NULL)
4244 return number is
4245 v_result number := -1; --generic error
4246 v_status ldd_lead.codleadstatus%type;
4247
4248begin
4249 --only leads in 'IG' status and that have not yet been reassigned can be modified
4250 select codleadstatus
4251 into v_status
4252 from ldd_lead l
4253 join ldd_trackback t
4254 on l.codlead = t.codlead
4255 where l.codlead = p_codLead
4256 and l.codleadstatus = 'IG'
4257 and t.mngd_only_on_tb = 'Y';
4258 v_result := pkldd_leads.updateleadstatus(p_codlead => p_codlead,
4259 p_codleadstatus => v_status, --always IG, already get if lead is in the correct status
4260 p_usergid => p_usergid,
4261 p_assignDate => sysdate,
4262 p_codActionContact => NULL,
4263 p_logHandle => NULL);
4264
4265 --- Se la modifica dello stato e' fallita, di conseguenza fallisce l'assegnazione
4266 --- Su logmaster/logc ci sono informazioni relative ad eventuali errori oracle
4267 IF v_result != 1
4268 THEN
4269 v_result := -1;
4270 ELSE
4271 begin
4272 update ldd_trackback t
4273 set t.mngd_only_on_tb = 'N'
4274 where t.codlead = p_codLead;
4275
4276 UPDATE crm_actioncontact cac
4277 SET cac.usergid = p_usergid
4278 WHERE cac.codlead = p_codlead
4279 AND cac.codaction = 'DM';
4280
4281 --- T12222 - in caso di avvenuta assegnazione si salva un record che indica
4282 --- l'assegnatore
4283 IF p_usergidAssigner IS NOT NULL
4284 THEN
4285 BEGIN
4286 INSERT INTO LDD_LEAD_MANUALASSIGNED
4287 (CODLEAD, ASSIGNER_USERGID, CREATIONDATE)
4288 VALUES
4289 (p_codLead, p_usergidAssigner, SYSDATE);
4290 EXCEPTION
4291 WHEN DUP_VAL_ON_INDEX THEN
4292 UPDATE LDD_LEAD_MANUALASSIGNED MAS
4293 SET MAS.ASSIGNER_USERGID = p_usergidAssigner,
4294 MAS.CREATIONDATE = SYSDATE
4295 WHERE MAS.CODLEAD = p_codLead;
4296 END;
4297 END IF;
4298 exception
4299 when others then
4300 v_result := -1;
4301 end;
4302 END IF;
4303 return v_result;
4304exception
4305 when NO_DATA_FOUND then
4306 v_result := -1;
4307 return v_result;
4308 when others then
4309 v_result := -2;
4310 return v_result; --no more to do!
4311
4312end reassignWorkingPhoneLead;
4313
4314--- B35429
4315--- findAgendaByLeads
4316--- Elenco dei dati agenda per un'array di leads
4317FUNCTION findAgendaByLeads
4318 ( p_arrayLeads IN leadsTable )
4319 RETURN elinktypes.ref_collection IS
4320 myCursor elinktypes.ref_collection;
4321 v_arrayLeads LISTOFVALUEN;
4322 v_idx PLS_INTEGER;
4323 v_codLead LDD_AGENDA.CODLEAD%TYPE;
4324 v_leadsCount NUMBER;
4325BEGIN
4326 v_arrayLeads := LISTOFVALUEN ();
4327 FOR v_idx IN p_arrayLeads.FIRST .. p_arrayLeads.LAST LOOP
4328 v_arrayLeads.extend;
4329 v_arrayLeads(v_idx) := p_arrayLeads(v_idx);
4330 END LOOP;
4331
4332 IF v_arrayLeads.COUNT = 1 THEN
4333
4334 v_codLead := v_arrayLeads(1) ;
4335
4336 OPEN myCursor FOR
4337 SELECT AGE.CODLEAD,
4338 AGE.CODAGENDALINK,
4339 AGE.AGENDAAPPOINTMENTDATE,
4340 AGE.AGENDASUBJECT,
4341 AGE.AGENDANOTES,
4342 AGE.AGENDAMODPROXACQ,
4343 AGE.AGENDADATEACQ,
4344 AGE.AGENDANOTEDEALER,
4345 AGE.AGENDATESTDRIVEMODEL,
4346 AGE.AGENDATESTDRIVEVERSION
4347 FROM LDD_AGENDA AGE
4348 WHERE AGE.CODLEAD = v_codLead;
4349
4350 ELSE
4351
4352 v_leadsCount := v_arrayLeads.COUNT;
4353
4354 OPEN myCursor FOR
4355 SELECT AGE.CODLEAD,
4356 AGE.CODAGENDALINK,
4357 AGE.AGENDAAPPOINTMENTDATE,
4358 AGE.AGENDASUBJECT,
4359 AGE.AGENDANOTES,
4360 AGE.AGENDAMODPROXACQ,
4361 AGE.AGENDADATEACQ,
4362 AGE.AGENDANOTEDEALER,
4363 AGE.AGENDATESTDRIVEMODEL,
4364 AGE.AGENDATESTDRIVEVERSION
4365 FROM LDD_AGENDA AGE
4366 WHERE AGE.CODLEAD IN (
4367 ( SELECT * FROM TABLE ( CAST (v_arrayLeads as LISTOFVALUEN) ) WHERE ROWNUM <= v_leadsCount )
4368 )
4369 ORDER BY AGE.CODLEAD;
4370
4371 END IF;
4372
4373 RETURN myCursor;
4374
4375END findAgendaByLeads;
4376
4377function findLeadByCodleadHub(p_codleadhub ldd_lead.codleadhub%TYPE)
4378 return elinkTypes.ref_collection is
4379 myCursor elinktypes.ref_collection;
4380begin
4381 open mycursor for
4382 select codlead
4383 from ldd_lead l
4384 where l.codleadhub = p_codleadhub;
4385 return myCursor;
4386end findLeadByCodleadHub;
4387
4388-- T12683 - Tipo applicazione Link dell'objtype (della lead)
4389FUNCTION getApplTypeForLeadObjtype(p_objtype IN LDD_OBJTYPES.OBJTYPE%TYPE)
4390 RETURN LDD_OBJTYPES.LINKAPPLTYPE%TYPE IS
4391 linkApplType LDD_OBJTYPES.LINKAPPLTYPE%TYPE;
4392BEGIN
4393 SELECT LO.LINKAPPLTYPE
4394 INTO linkApplType
4395 FROM LDD_OBJTYPES LO
4396 WHERE OBJTYPE = p_objtype;
4397
4398 RETURN linkApplType;
4399END getApplTypeForLeadObjtype;
4400
4401FUNCTION countDealerFleetLeads (
4402 p_codmarket in ldd_campaign_details.codmarket%type,
4403 p_codDealer in ldd_campaign_details.codmaindealer%type
4404 )
4405 RETURN elinkTypes.ref_collection is
4406
4407 myCursor elinktypes.ref_collection;
4408
4409BEGIN
4410 open mycursor for
4411 SELECT COUNT(*) AS QUANTITY
4412 FROM TRG_LDD_LEAD l
4413 JOIN LDD_CAMPAIGN_DETAILS D
4414 ON L.CODCAMPAIGNDETAIL = D.CODCAMPAIGNDETAIL
4415 LEFT JOIN LDD_TRACKBACK LTB
4416 ON L.CODLEAD = LTB.CODLEAD
4417 WHERE l.CODLEADSTATUS IN ('DA', 'DG', 'IG')
4418 AND D.CODDEALER = p_codDealer
4419 AND D.CODMARKET = p_codmarket
4420 AND L.OBJTYPE = 'C'; -- Fleet Leads
4421
4422 return myCursor;
4423END countDealerFleetLeads;
4424
4425-- B36457 - Conteggio lead da gestire/in gestione per utente divise in objtype
4426FUNCTION countManageLeadsByObjtype(p_codmarket in ldd_campaign_details.codmarket%type,
4427 p_usergid IN TRG_LDD_LEAD.USERGID%TYPE,
4428 p_codMainDealer in ldd_campaign_details.codmaindealer%type)
4429 RETURN elinkTypes.ref_collection is
4430 myCursor elinktypes.ref_collection;
4431BEGIN
4432
4433 open mycursor for
4434 SELECT l.OBJTYPE, l.CODLEADSTATUS, COUNT(*) AS QUANTITY
4435 FROM TRG_LDD_LEAD l
4436 JOIN LDD_CAMPAIGN_DETAILS D
4437 ON L.CODCAMPAIGNDETAIL = D.CODCAMPAIGNDETAIL
4438 LEFT JOIN LDD_TRACKBACK LTB
4439 ON L.CODLEAD = LTB.CODLEAD
4440 WHERE l.CODLEADSTATUS IN ('DG', 'IG')
4441 -- B38210 - in caso seleziono ALL da elenco salesman
4442 and l.USERGID = (CASE
4443 WHEN p_usergid is not null THEN
4444 p_usergid
4445 WHEN p_usergid is null or p_usergid = '' THEN
4446 l.USERGID
4447 ELSE
4448 l.USERGID
4449 END)
4450 --and l.USERGID = p_usergid
4451 -- B38210
4452 AND D.CODMAINDEALER = p_codMainDealer
4453 AND D.CODMARKET = p_codmarket
4454 AND (
4455 (L.OBJTYPE <> 'P')
4456 OR
4457 (L.objtype = 'P' AND LTB.MNGD_ONLY_ON_TB = 'N')
4458 )
4459 AND (
4460 (L.OBJTYPE = 'L' AND D.CODDEALERLOCATION IS NOT NULL)
4461 OR
4462 (L.OBJTYPE <> 'L')
4463 )
4464 GROUP BY l.objtype, l.codleadstatus;
4465
4466 return myCursor;
4467END countManageLeadsByObjtype;
4468
4469---T13556 Origine Creazione Lead interne
4470FUNCTION insertLddLeadOrigin
4471 ( P_CODLEAD IN LDD_LEAD_ORIGIN.CODLEAD%TYPE,
4472 P_CODLEADORIGIN IN LDD_LEAD_ORIGIN.CODLEADORIGIN%TYPE )
4473 RETURN NUMBER IS
4474 v_UserGid LDD_LEAD.USERGID%TYPE;
4475 BEGIN
4476 SELECT LEA.USERGID
4477 INTO v_UserGid
4478 FROM LDD_LEAD LEA
4479 WHERE LEA.CODLEAD = P_CODLEAD ;
4480
4481 INSERT INTO LDD_LEAD_ORIGIN ( CODLEAD, CODLEADORIGIN, USERGID)
4482 VALUES ( p_CODLEAD, p_CODLEADORIGIN, v_UserGid );
4483 RETURN sql%RowCount;
4484
4485 EXCEPTION
4486 WHEN OTHERS THEN
4487 RETURN NULL;
4488 END insertLddLeadOrigin;
4489
4490FUNCTION getProperPhoneNumber(p_codMarket IN MARKET.CODMARKET%TYPE,
4491 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
4492 p_insertionDate IN LDD_LEAD.INSERTIONDATE%TYPE,
4493 p_mobilePhone IN LDD_LEAD.MOBILEPHONE%TYPE,
4494 p_phoneNumber IN LDD_LEAD.PHONENUMBER%TYPE,
4495 p_codLead IN LDD_TRACKBACK.CODLEAD%TYPE,
4496 p_dateSent IN LDD_TRACKBACK.DATE_SENT%TYPE,
4497 p_pinMobile IN LDD_TRACKBACK.PIN_MOBILE%TYPE,
4498 p_pinPhoneNumber IN LDD_TRACKBACK.PIN_PHONENUMBER%TYPE,
4499 p_trackBackResult IN LDD_TRACKBACK.TB_RESULT%TYPE,
4500 p_trackBackStatus IN LDD_TRACKBACK.TB_STATUS%TYPE,
4501 p_checkMinutesPin NUMBER,
4502 p_communicationchannel IN LDD_LEAD.COMMUNICATIONCHANNEL%TYPE DEFAULT null,
4503 p_email IN CONTACT.EMAIL_ADDRESS%TYPE DEFAULT null) RETURN VARCHAR2 AS
4504
4505 v_PhoneNumber VARCHAR2(200);
4506
4507BEGIN
4508
4509 v_PhoneNumber := '**********';
4510
4511 CASE
4512 /***************************************************
4513 * OVERRIDE MERCATO GERMANIA
4514 * Questo override ? richiesto esplicitamente per il
4515 * mercato 3110
4516 ***************************************************/
4517 WHEN p_codMarket = '3110' THEN
4518 CASE
4519 WHEN p_codLeadStatus LIKE 'DA' THEN
4520 v_PhoneNumber := '**********';
4521 ELSE
4522 CASE
4523 WHEN p_codLead IS NULL THEN
4524 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4525 INTO v_PhoneNumber
4526 FROM dual;
4527 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4528/* WHEN p_codLeadStatus in ('IG', 'AR') THEN
4529 v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);*/
4530 ELSE
4531 CASE
4532 WHEN (/*p_codLeadStatus = 'DG' AND*/
4533 p_trackBackStatus IN ('DA') AND
4534 (round((sysdate - NVL(p_dateSent, p_insertionDate)) * 24 * 60,
4535 0) < p_checkMinutesPin)) THEN
4536 v_PhoneNumber := '**********';
4537 WHEN p_trackBackStatus IN ('DA', 'SD', 'PD', 'PE') THEN
4538 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4539 INTO v_PhoneNumber
4540 FROM dual;
4541 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4542 WHEN p_trackBackStatus IN ('RP') THEN
4543 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_pinMobile, p_PinPhoneNumber))
4544 INTO v_PhoneNumber
4545 FROM dual;
4546 --v_PhoneNumber := NVL(p_pinMobile, p_PinPhoneNumber);
4547 WHEN p_trackBackStatus IN ('RE') THEN
4548 CASE
4549 WHEN p_trackBackResult IN ('CC') THEN
4550 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4551 INTO v_PhoneNumber
4552 FROM dual;
4553 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4554 WHEN p_trackBackResult IN ('CA','CF') THEN
4555 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_pinMobile, p_PinPhoneNumber))
4556 INTO v_PhoneNumber
4557 FROM dual;
4558 --v_PhoneNumber := NVL(p_pinMobile, p_PinPhoneNumber);
4559 ELSE
4560 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4561 INTO v_PhoneNumber
4562 FROM dual;
4563 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4564 END CASE;
4565 ELSE
4566 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4567 INTO v_PhoneNumber
4568 FROM dual;
4569 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4570 END CASE;
4571 END CASE;
4572 END CASE;
4573 /***************************************************
4574 * COMPORTAMENTO DI DEFAULT
4575 * Questo ? il comportamento di default da applicare
4576 * quando si calcola il valore di ritorno del
4577 * Phone Number / PIN
4578 ***************************************************/
4579 ELSE
4580 CASE
4581 WHEN p_codLeadStatus LIKE 'DA' THEN
4582 v_PhoneNumber := '**********';
4583 ELSE
4584 CASE
4585 WHEN p_codLead IS NULL THEN
4586 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4587 INTO v_PhoneNumber
4588 FROM dual;
4589 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4590 WHEN p_codLeadStatus in ('IG', 'AR') THEN
4591 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4592 INTO v_PhoneNumber
4593 FROM dual;
4594 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4595 ELSE
4596 CASE
4597 WHEN (p_codLeadStatus = 'DG' AND
4598 p_trackBackStatus IN ('DA') AND
4599 (round((sysdate - NVL(p_dateSent, p_insertionDate)) * 24 * 60,
4600 0) < p_checkMinutesPin)) THEN
4601 v_PhoneNumber := '**********';
4602 WHEN p_trackBackStatus IN ('DA', 'SD', 'PD', 'PE') THEN
4603 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4604 INTO v_PhoneNumber
4605 FROM dual;
4606 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4607 WHEN p_trackBackStatus IN ('RP') THEN
4608 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_pinMobile, p_PinPhoneNumber))
4609 INTO v_PhoneNumber
4610 FROm dual;
4611 --v_PhoneNumber := NVL(p_pinMobile, p_PinPhoneNumber);
4612 WHEN p_trackBackStatus IN ('RE') THEN
4613 CASE
4614 WHEN p_trackBackResult = 'CC' THEN -- T16128
4615 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4616 INTO v_PhoneNumber
4617 FROM dual;
4618 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4619 WHEN p_trackBackResult IN ('CA','CF') THEN -- T16128
4620 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_pinMobile, p_PinPhoneNumber))
4621 INTO v_PhoneNumber
4622 FROM dual;
4623 --v_PhoneNumber := NVL(p_pinMobile, p_PinPhoneNumber);
4624 ELSE
4625 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4626 INTO v_PhoneNumber
4627 FROM dual;
4628 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4629 END CASE;
4630 ELSE
4631 SELECT decode(nvl(p_communicationchannel,'T'), 'M', p_email, NVL(p_mobilePhone, p_phoneNumber))
4632 INTO v_PhoneNumber
4633 FROM dual;
4634 --v_PhoneNumber := NVL(p_mobilePhone, p_phoneNumber);
4635 END CASE;
4636 END CASE;
4637 END CASE;
4638 END CASE;
4639
4640 RETURN v_PhoneNumber;
4641
4642END getProperPhoneNumber;
4643
4644FUNCTION checkIsPin(p_codMarket IN MARKET.CODMARKET%TYPE,
4645 p_codLeadStatus IN LDD_LEAD.CODLEADSTATUS%TYPE,
4646 p_insertionDate IN LDD_LEAD.INSERTIONDATE%TYPE,
4647 p_codLead IN LDD_TRACKBACK.CODLEAD%TYPE,
4648 p_dateSent IN LDD_TRACKBACK.DATE_SENT%TYPE,
4649 p_trackBackResult IN LDD_TRACKBACK.TB_RESULT%TYPE,
4650 p_trackBackStatus IN LDD_TRACKBACK.TB_STATUS%TYPE,
4651 p_checkMinutesPin NUMBER,
4652 p_communicationchannel IN LDD_LEAD.COMMUNICATIONCHANNEL%TYPE DEFAULT NULL) RETURN NUMBER AS
4653
4654 v_isPin NUMBER;
4655
4656BEGIN
4657
4658 v_isPin := 0;
4659
4660 CASE
4661 /***************************************************
4662 * Gestione communicationchannel = M
4663 ***************************************************/
4664 WHEN nvl(p_communicationchannel,'N') = 'M' THEN
4665 IF pkLEADLOADER.chk_TB_active(p_codLead) AND p_codLeadStatus = 'DG' THEN
4666 v_isPin := 1;
4667 ELSE
4668 v_isPin := 0;
4669 END IF;
4670 /***************************************************
4671 * OVERRIDE MERCATO GERMANIA
4672 * Questo override ? richiesto esplicitamente per il
4673 * mercato 3110
4674 ***************************************************/
4675 WHEN p_codMarket = '3110' THEN
4676 CASE
4677 WHEN p_codLead IS NULL THEN
4678 v_isPin := 0;
4679/* WHEN p_codLeadStatus in ('IG', 'AR') THEN
4680 v_isPin := 0;*/
4681 ELSE
4682 CASE
4683 WHEN (/*p_codLeadStatus = 'DG' AND*/ p_trackBackStatus IN ('DA') AND
4684 (round((sysdate - NVL(p_dateSent, p_insertionDate)) * 24 * 60,
4685 0) < p_checkMinutesPin)) THEN
4686 v_isPin := 1;
4687 WHEN p_trackBackStatus IN ('DA', 'SD', 'PD', 'PE') THEN
4688 v_isPin := 0;
4689 WHEN p_trackBackStatus IN ('RP') THEN
4690 v_isPin := 1;
4691 WHEN p_trackBackStatus IN ('RE') THEN
4692 CASE
4693 WHEN p_trackBackResult IN (/*'CF', */'CC') THEN
4694 v_isPin := 0;
4695 WHEN p_trackBackResult IN ('CA','CF') THEN
4696 v_isPin := 1;
4697 ELSE
4698 v_isPin := 0;
4699 END CASE;
4700 ELSE
4701 v_isPin := 0;
4702 END CASE;
4703 END CASE;
4704 /***************************************************
4705 * COMPORTAMENTO DI DEFAULT
4706 * Questo ? il comportamento di default da applicare
4707 * quando si calcola il valore di ritorno del
4708 * Phone Number / PIN
4709 ***************************************************/
4710 ELSE
4711 CASE
4712 WHEN p_codLead IS NULL THEN
4713 v_isPin := 0;
4714 WHEN p_codLeadStatus in ('IG', 'AR') THEN
4715 v_isPin := 0;
4716 ELSE
4717 CASE
4718 WHEN (p_codLeadStatus = 'DG' AND p_trackBackStatus IN ('DA') AND
4719 (round((sysdate - NVL(p_dateSent, p_insertionDate)) * 24 * 60,
4720 0) < p_checkMinutesPin)) THEN
4721 v_isPin := 1;
4722 WHEN p_trackBackStatus IN ('DA', 'SD', 'PD', 'PE') THEN
4723 v_isPin := 0;
4724 WHEN p_trackBackStatus IN ('RP') THEN
4725 v_isPin := 1;
4726 WHEN p_trackBackStatus IN ('RE') THEN
4727 CASE
4728 WHEN p_trackBackResult = 'CC' THEN -- T16128
4729 v_isPin := 0;
4730 WHEN p_trackBackResult IN ('CA','CF') THEN --T16128
4731 v_isPin := 1;
4732 ELSE
4733 v_isPin := 0;
4734 END CASE;
4735 ELSE
4736 v_isPin := 0;
4737 END CASE;
4738 END CASE;
4739 END CASE;
4740
4741 RETURN v_isPin;
4742
4743END checkIsPin;
4744
4745FUNCTION findBusinessAreaFromLead(p_codLead IN LDD_TRACKBACK.CODLEAD%TYPE)
4746 RETURN elinktypes.ref_collection IS
4747
4748 myCursor elinktypes.ref_collection;
4749
4750BEGIN
4751
4752 OPEN myCursor FOR
4753 SELECT NVL(LH.BUSINESS_AREA,'1') AS BUSINESS_AREA
4754 FROM LDD_LEAD LDL
4755 LEFT JOIN LDD_LEAD_HUB_MASTERKEY LHM
4756 ON LHM.CODLEAD = LDL.CODLEAD
4757 LEFT JOIN LDD_HUB_MASTERKEY LH
4758 ON LH.CODMASTERKEY = LHM.CODMASTERKEY
4759 WHERE LDL.CODLEAD = p_codLead;
4760
4761 RETURN myCursor;
4762
4763END findBusinessAreaFromLead;
4764
4765-- T14379 - iLinkLeadGuests Lead iLink Welcome - Guests List
4766FUNCTION findiLinkLeadGuests
4767 ( p_codMarket IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE,
4768 p_codmaindealer IN LDD_CAMPAIGN_DETAILS.CODMAINDEALER %TYPE,
4769 p_codcampaigntype IN LDD_CAMPAIGN.CODCAMPAIGNTYPE%TYPE ,
4770 p_namecampaign IN LDD_CAMPAIGN.NAMECAMPAIGN%TYPE,
4771 p_dateFrom IN date,
4772 p_dateTo IN date DEFAULT NULL,
4773 p_codLanguage IN LANGUAGE.CODLANGUAGE%TYPE DEFAULT NULL )
4774 RETURN ELINKtypes.ref_collection IS
4775 myCursor elinktypes.ref_collection;
4776BEGIN
4777 OPEN myCursor FOR
4778 SELECT /* PKILINKREPORTING.findiLinkLeadGuests */
4779 LEA.codlead
4780 ,LDT.codmarket
4781 ,LDT.coddealer
4782 ,LDT.CODMAINDEALER,
4783 D.NAME as DESC_DEALER
4784 ,LDT.CODDEALERLOCATION
4785 ,DL.ADDRESS||' - '||DL.TOWN_NAME AS DESCRLOCATION -- (la concatenazione dei campi ADDRESS e TOWN_NAME )
4786 ,DL.OUTLET_IDCODE
4787 ,LEA.INSERTIONDATE
4788 ,C.CODCONTACT
4789 , case when PE.CODPERSON IS NOT NULL THEN
4790 upper(pe.LASTNAME ||' '|| pe.FIRSTNAME)
4791 else
4792 upper(co.COMPANYNAME)
4793 end AS CONTACTNAME --contactname (cognome + nome // companyname)
4794 ,LEA.CODLEADSTATUS -- leadstatus
4795 ,LCP.CODCAMPAIGNTYPE
4796 ,LCP.NAMECAMPAIGN
4797 ,LTP.DESCRIPTION as DESCAMPAIGNTYPE
4798 ,case when LED.CODLEAD IS NOT NULL AND LED.STATUS_INVITATION ='1' THEN
4799 'Y'
4800 else
4801 'N'
4802 end AS EVENT_INVITATION
4803 ,case when LED.CODLEAD IS NOT NULL AND LED.STATUS_ATTENDANCE ='1' THEN
4804 'Y'
4805 else
4806 'N'
4807 end AS EVENT_ATTENDANCE
4808 FROM LDD_LEAD LEA
4809 JOIN LDD_CAMPAIGN_DETAILS LDT ON (LDT.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL)
4810 JOIN LDD_CAMPAIGN LCP ON (LCP.CODCAMPAIGN = LDT.CODCAMPAIGN)
4811 LEFT JOIN LDD_CAMPAIGN_TYPES LTP ON LTP.CODCAMPAIGNTYPE = LCP.CODCAMPAIGNTYPE
4812 LEFT JOIN CONTACT C ON (C.CODCONTACT = LEA.CODCONTACT)
4813 LEFT JOIN COMPANY CO ON (CO.CODCOMPANY = C.CODCONTACT)
4814 LEFT JOIN PERSON PE ON (C.CODCONTACT = PE.CODPERSON)
4815 LEFT JOIN DEALER D ON ( D.CODMARKET = LDT.CODMARKET and D.CODDEALER = LDT.CODDEALER )
4816 LEFT JOIN DEALERLOCATION DL on ( DL.CODMARKET = D.CODMARKET and DL.CODDEALER = D.CODDEALER and DL.CODLOCATION = LDT.CODDEALERLOCATION )
4817 LEFT JOIN LDD_EVENT_ATTENDANCE LED on ( LED.CODLEAD = LEA.CODLEAD )
4818 WHERE LDT.CODMARKET = p_codmarket
4819 AND LDT.CODMAINDEALER = p_codmaindealer
4820 AND ( p_codcampaigntype IS NULL OR LCP.CODCAMPAIGNTYPE = p_codcampaigntype )
4821 AND ( p_namecampaign IS NULL OR LCP.NAMECAMPAIGN = p_namecampaign )
4822 AND LEA.INSERTIONDATE >= p_dateFrom
4823 and LEA.INSERTIONDATE< NVL( p_dateTo , TO_DATE('31/12/3999','DD/MM/YYYY'))
4824 ORDER BY LEA.INSERTIONDATE DESC
4825 ;
4826
4827
4828 RETURN myCursor;
4829
4830END findiLinkLeadGuests;
4831
4832
4833FUNCTION setLeadAttendanceStatus
4834 ( p_codlead IN LDD_EVENT_ATTENDANCE.CODLEAD%TYPE,
4835 p_usergid IN LDD_EVENT_ATTENDANCE.USERGID%TYPE,
4836 p_status_invitation IN LDD_EVENT_ATTENDANCE.STATUS_INVITATION%TYPE Default '0', --> 1 = invitato , 0 = non invitato
4837 p_status_attendance IN LDD_EVENT_ATTENDANCE.STATUS_ATTENDANCE%TYPE Default '0' --> 1 = partecipa, 0 = non partecipa
4838 )
4839 RETURN NUMBER IS
4840
4841BEGIN
4842 IF ( p_status_invitation= '0' and p_status_attendance ='0' ) then -- 0 = non partecipa/non invitato
4843 DELETE FROM LDD_EVENT_ATTENDANCE WHERE CODLEAD = p_codlead ;
4844 IF SQL%ROWCOUNT > 0 THEN
4845 INSERT INTO LDD_EVENT_ATTENDANCE_H ( CODLEAD, USERGID, CREATION_DATE, STATUS_INVITATION, STATUS_ATTENDANCE )
4846 VALUES ( p_codlead,p_usergid, SYSDATE, p_status_invitation,p_status_attendance ) ;
4847 END IF;
4848 ELSIF ( p_status_invitation= '1' or p_status_attendance ='1' ) then
4849 BEGIN
4850 INSERT INTO LDD_EVENT_ATTENDANCE ( CODLEAD, USERGID, CREATION_DATE, STATUS_INVITATION,STATUS_ATTENDANCE )
4851 VALUES ( p_codlead,p_usergid, SYSDATE , p_status_invitation,p_status_attendance ) ;
4852 INSERT INTO LDD_EVENT_ATTENDANCE_H ( CODLEAD, USERGID, CREATION_DATE, STATUS_INVITATION,STATUS_ATTENDANCE )
4853 VALUES ( p_codlead,p_usergid, SYSDATE , p_status_invitation,p_status_attendance ) ;
4854 EXCEPTION
4855 WHEN DUP_VAL_ON_INDEX THEN
4856 UPDATE LDD_EVENT_ATTENDANCE
4857 SET STATUS_INVITATION =p_status_invitation,
4858 STATUS_ATTENDANCE =p_status_attendance,
4859 USERGID = p_usergid,
4860 CREATION_DATE = SYSDATE
4861 WHERE CODLEAD = p_codlead
4862 AND ( STATUS_INVITATION !=p_status_invitation or STATUS_ATTENDANCE !=p_status_attendance );
4863 IF SQL%ROWCOUNT > 0 THEN
4864 INSERT INTO LDD_EVENT_ATTENDANCE_H ( CODLEAD, USERGID, CREATION_DATE, STATUS_INVITATION, STATUS_ATTENDANCE )
4865 VALUES ( p_codlead,p_usergid, SYSDATE, p_status_invitation,p_status_attendance ) ;
4866 END IF;
4867 END;
4868 END IF;
4869
4870 RETURN 1;
4871
4872EXCEPTION
4873 WHEN OTHERS THEN
4874 x_errCode := sqlcode;
4875 x_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
4876 x_procedureName := 'pkLDD_LEADS.setLeadAttendanceStatus';
4877 x_logHandle := pkLOG.Insertmaster(NULL,x_procedureName,NULL, '0' );
4878 pklog.writedetail(vloghandle=>x_logHandle,
4879 llevel=>PKLOG.SEVERE,
4880 pprocedurename=>x_procedureName,
4881 description=>'Error in setLeadAttendanceStatus for Laed:'|| p_codlead,
4882 details=>x_errMsg );
4883 PKLOG.UPDATEMASTER ( x_logHandle , x_errCode );
4884 RETURN x_errCode;
4885
4886END setLeadAttendanceStatus;
4887
4888 FUNCTION getLeadPromoDetails
4889 (p_codLead in ldd_lead.codlead%type)
4890 RETURN ELINKtypes.ref_collection is
4891 myCursor elinkTypes.ref_collection;
4892 BEGIN
4893 open myCursor for
4894 SELECT * FROM LDD_PROMO PR WHERE PR.CODLEAD=p_codLead;
4895 return myCursor;
4896 END getLeadPromoDetails;
4897
4898end pkLDD_LEADS;
4899/