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