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