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