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