· 6 years ago · Apr 04, 2019, 02:38 PM
1create or replace package pkLDD_CAMPAIGN is
2
3 -- Author : N. DePari
4 -- Created : 24/02/2009 21.13.07
5 -- Purpose : Gestione anagrafica delle leads in ELINK.
6 -- $Id: pkLDD_CAMPAIGN.sql,v 1.73 2019/04/01 15:13:11 abis Exp $
7 --
8 -- funzioni:
9 -- - findAllLeadByContact
10 -- - findAllOpenLeadByContact
11 -- - countAllOpenLeadByContact
12 -- Se NULL prende tutte le applicazioni
13
14 Type leadsTable is Table of ldd_lead.codcontact %Type index by binary_integer;
15
16 TYPE OBJTYPETABLE IS TABLE OF LDD_OBJTYPES.objtype%TYPE INDEX BY BINARY_INTEGER;
17
18 -- T13683 - Public find OBJTYPE
19 FUNCTION findObjType(p_codcampaignsource IN ldd_campaign_sources.codcampaignsource%TYPE,
20 p_codcampaigntype IN ldd_campaign_types.codcampaigntype%TYPE,
21 p_codmasterkey IN ldd_hub_masterkey.codmasterkey%TYPE) -- T13683
22 RETURN ldd_types_source_mba.objtype%TYPE;
23
24 -- Public findCampaign
25 function findCampaign(p_codCampaign in ldd_campaign.codcampaign%type)
26 return elinkTypes.ref_collection;
27
28 -- Public findCampaignDetail
29 function findCampaignDetail(p_codCampaignDetail in ldd_campaign_details.codcampaigndetail%type)
30 return elinkTypes.ref_collection;
31
32 -- Public findLeadByPrimaryKey
33 function findLeadByPrimaryKey(p_codLead in ldd_lead.codLead%type)
34 return elinkTypes.ref_collection;
35
36 -- Public findLeadByLeadExt
37 function findLeadByLeadExt(p_codleadext in ldd_lead.codleadext%type,
38 p_codmarket in ldd_campaign_details.codmarket%type,
39 p_codcampaignsource in ldd_campaign.codcampaignsource%type,
40 p_codcampaigntype in ldd_campaign.codcampaigntype%type,
41 p_codmaindealer in ldd_campaign_details.codmaindealer%type)
42 return elinkTypes.ref_collection;
43
44 -- Public findAllLeadByContact
45 function findAllLeadByContact(p_codContact in ldd_lead.codcontact%type,
46 p_codLanguage in dictionarylanguage.codlanguage%type,
47 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
48 return elinkTypes.ref_collection;
49
50 -- Public findAllOpenLeadByContact
51function findAllOpenLeadByContact
52 (p_codContact in ldd_lead.codcontact%type,
53 p_codLanguage in dictionarylanguage.codlanguage%type )
54 return elinkTypes.ref_collection;
55
56function findAllOpenLeadByContactWithDA
57 (p_codContact in ldd_lead.codcontact%type,
58 p_codLanguage in dictionarylanguage.codlanguage%type )
59 return elinkTypes.ref_collection;
60
61 -- Public countAllOpenLeadByContact
62function countAllOpenLeadByContact
63 (p_codContact in ldd_lead.codcontact%type )
64 return number;
65
66function countAllLeadByContact
67 (p_codContact in ldd_lead.codcontact%type )
68 return number;
69
70 -- Public getActiveCampaigns
71 function getActiveCampaigns(p_codbrand IN ldd_campaign_details.codbrand%type DEFAULT NULL,
72 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null,
73 p_coddealer IN ldd_campaign_details.coddealer%type default null,
74 p_iscentral IN ldd_campaign.iscentral%type default 0,
75 p_codmarket IN ldd_campaign_details.codmarket%type default null)
76 return elinkTypes.ref_collection;
77
78 -- Public getAllCampaignSources
79 function getAllCampaignSources return elinkTypes.ref_collection;
80
81 -- Public getAllCampaignTypes
82 function getAllCampaignTypes return elinkTypes.ref_collection;
83
84 -- Public getCampaignTypesBySource
85 function getCampaignTypesBySource(p_codcampaignsource in ldd_campaign_sources.codcampaignsource %type,
86 p_codLanguage in dictionarylanguage.codlanguage%type)
87 return elinkTypes.ref_collection;
88
89-- Public getCampaignSourceFiltered
90function getCampaignSourceFiltered
91 (p_codcampaign in ldd_campaign.codcampaign%type,
92 p_codmarket IN ldd_campaign_details.codmarket%type default null,
93 p_codbrand IN ldd_campaign_details.codbrand%type DEFAULT NULL,
94 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null )
95 return elinkTypes.ref_collection ;
96
97-- Public getCampaignTypesFiltered
98function getCampaignTypesFiltered
99 (p_codcampaign in ldd_campaign.codcampaign%type,
100 p_codmarket IN ldd_campaign_details.codmarket%type default null,
101 p_codbrand IN ldd_campaign_details.codbrand%type DEFAULT NULL,
102 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null )
103 return elinkTypes.ref_collection ;
104
105 /* update flag sent email */
106 function updateFlagSentEmail(p_codlead in ldd_lead.codlead%type,
107 p_codcontact in ldd_lead.codcontact%type)
108 return NUMBER;
109
110 /* prendo il brand dalla campaign detail */
111 function getBrandFromCampaign(p_codmaindealer in ldd_campaign_details.codmaindealer%type,
112 p_codmarket in ldd_campaign_details.codmarket%type,
113 p_codcampaign in ldd_campaign_details.codcampaigndetail%type)
114 return elinkTypes.ref_collection;
115 function setStatusOnLeadList(p_leadsList in leadsTable,
116 p_status in crm_configurationstatus.codstatus%type,
117 p_resultStatus in crm_negotiationresult.codnegotiationresult%type,
118 p_userGID crm_actioncontact.usergid%type,
119 p_datePurchaseDelayed in crm_negotiationnote.purchasedelay%type default null,
120 p_codbrandinfocar in ldd_leadnote.codbrandinfocar%type default null)
121
122 return number;
123
124--- T4789 - Procedura per l'estrazione dei dati relativi alle LEAD di un mercato la cui data di creazione
125--- e' relativa alla settimana COMPLETA precedente alla data fornita come parametro
126--- es: data = Giovedi' 20/01 ---> settimana : 10/01 - 16/01
127FUNCTION extractLeadsForODE
128 (P_CODMARKET IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE ,
129 p_startDate IN DATE DEFAULT SYSDATE )
130RETURN INTEGER ;
131
132FUNCTION getCampaignsBySource
133 (p_codcampaignsource in ldd_campaign.codcampaignsource%type,
134 p_codmarket IN ldd_campaign_details.codmarket%type default null,
135 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null)
136return elinkTypes.ref_collection;
137
138-- Public addCampaign
139 function addCampaign(p_codcampaignext in ldd_campaign.codcampaignext%type,
140 p_codcampaignsource in ldd_campaign.codcampaignsource%type,
141 p_codcampaigntype in ldd_campaign.codcampaigntype%type,
142 p_namecampaign in ldd_campaign.namecampaign%type,
143 p_description in ldd_campaign.description%type default null,
144 p_startdate in VARCHAR2 default null,
145 p_enddate in VARCHAR2 default null,
146 p_iscentral in ldd_campaign.iscentral%type default null)
147 return integer;
148
149-- Public addCampaignDetail
150--- T7223 - Controlla l'esistenza di una campaign detail con caratteristiche uguali alla richiesta
151--- I campi : codcampaign,codmarket,coddealer,coddealerlocation,codbrand,codcampaigndetailext
152--- costituiscono chiave univoca.
153--- se non esiste la si crea
154--- Return Value : CodCampaignDetail
155function addCampaignDetail
156 (p_codcampaign in ldd_campaign.codcampaign%type,
157 p_codmarket in ldd_campaign_details.codmarket%type,
158 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
159 p_codbrand in ldd_campaign_details.codbrand%type default null,
160 p_codcampaigndetailext in ldd_campaign_details.codcampaigndetailext%type default null,
161 p_coddealer in ldd_campaign_details.coddealer%type default null,
162 p_coddealerlocation in ldd_campaign_details.coddealerlocation%type default null,
163 p_creationusergid in ldd_campaign_details.creationusergid%type default null,
164 p_priority in ldd_campaign_details.priority%type default null)
165 return ldd_campaign_details.codcampaigndetail%TYPE ;
166
167-- Public updateCampaignDetailRedemption
168 procedure updateCampaignDetailRedemption(p_codcampaigndetailext in ldd_campaign_details.codcampaigndetailext%type ,
169 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
170 p_codmarket in ldd_campaign_details.codmarket%type,
171 p_redemption_tot in ldd_campaign_details.dbcare_redemption_tot%type default null,
172 p_redemption_pers in ldd_campaign_details.dbcare_redemption_pers%type default null,
173 p_redemption_lostcontact in ldd_campaign_details.dbcare_redemption_lostcontact%type default null,
174 p_redemption_totalcontact in ldd_campaign_details.dbcare_total_contact_num%type default null,
175 p_redemption_datawin in ldd_campaign_details.dbcare_data_win%type default null);
176
177 function getContactsbyCampaigndetail(p_codcampaign in ldd_campaign_details.codcampaign%type,
178 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
179 p_codmarket in ldd_campaign_details.codmarket%type)
180 return elinkTypes.ref_collection;
181--
182function getCampaignGroups (p_codmarket market.codmarket%type
183 ,p_codlanguage dictionarylanguage.codlanguage%type)
184 return elinkTypes.ref_collection;
185--
186function getCustomField (p_codmarket market.codmarket%type
187 ,p_idlead ldd_customfield.idlead%type
188 ,p_idfield ldd_customfield.idfield%type)
189 return ldd_customfield.fieldvalue%type;
190--
191function actionDeleteLead
192 ( p_codMarket IN MARKET.CODMARKET%TYPE DEFAULT '3109',
193 p_offsetDelete IN NUMBER DEFAULT NULL )
194 return number ;
195
196 function getSalesmanName(p_codmarket in ldd_campaign_details.codmarket%type,
197 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
198 p_codcontact in ldd_lead.codcontact%type,
199 p_codbrand in ldd_campaign_details.codbrand%type)
200 RETURN VARCHAR2;
201
202function findCampaignTypesAndSources
203 (p_codCampaignSource IN LDD_CAMPAIGN_TYPES_SOURCE.CODCAMPAIGNSOURCE%TYPE,
204 p_codCampaignType IN LDD_CAMPAIGN_TYPES_SOURCE.CODCAMPAIGNTYPE%TYPE,
205 p_application IN LINKAPPLTYPE.TYPE%TYPE )
206 return elinkTypes.ref_collection ;
207
208-- B39701
209-- Determina l'eventuale presenza di utenti con nomi campagna specifici e se tali nomi sono ancora
210-- attivi in data attuale
211FUNCTION findInternalUsergidCampaign(p_usergid in LDD_LEAD_USERGID_CAMPAIGN.USERGID%type)
212 RETURN elinkTypes.ref_collection;
213
214function findAllLeadByTypes
215 (p_codContact in ldd_lead.codcontact%type,
216 p_codLanguage in dictionarylanguage.codlanguage%type,
217 p_objtype in OBJTYPETABLE)
218 return elinkTypes.ref_collection;
219
220FUNCTION getLeadInfoBySurveyAnswer (p_codsurveyanswer IN surveyanswer.codsurveyanswer%TYPE)
221 RETURN elinkTypes.ref_collection;
222
223end pkLDD_CAMPAIGN;
224/
225create or replace package body pkLDD_CAMPAIGN is
226--- $Id: pkLDD_CAMPAIGN.sql,v 1.72 2019/03/25 16:17:14 abis Exp $
227
228--- Variabili globbali
229 x_sqlStmt VARCHAR2(4000);
230
231function getSalesmanName(p_codmarket in ldd_campaign_details.codmarket%type,
232 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
233 p_codcontact in ldd_lead.codcontact%type,
234 p_codbrand in ldd_campaign_details.codbrand%type)
235 RETURN VARCHAR2 IS
236 salesmanname varchar2(40);
237 begin
238
239 select eu.firstname||' '||eu.lastname into salesmanname
240 from offer o
241 join elinkuser eu on eu.loginname = o.usergid
242 join dealer dl on dl.codmarket = eu.codmarket and dl.coddealer = o.coddealer
243 join offerproduct op on (op.codoffer = o.codoffer)
244 where o.codmarket=p_codmarket
245 and o.offerstate = 1 --considera solo offerte stampate
246 and nvl(o.isfrombatch,0) = 0 --esclude offerte create da batch OCF
247 and nvl(o.isdirect,0) = 0 --esclude offerte create da OCF diretta
248 and eu.coddealer = dl.codmaindealer
249 and dl.codmaindealer = p_codmaindealer
250 and o.codcontact = p_codcontact
251 and nvl(eu.deleted,0) = 0 --esclude utenti non attivi
252 and op.codbrand = p_codbrand
253 and o.usergid is not null --esclude utenti non specificati
254 and o.codcontact is not null --esclude offerte anonime
255 and rownum < 2;
256 return salesmanname;
257
258 exception
259 when no_data_found then
260 return NULL;
261 when others then
262 return NULL;
263end;
264
265 -- T13683 - Public find OBJTYPE
266 FUNCTION findObjType(p_codcampaignsource IN ldd_campaign_sources.codcampaignsource%TYPE,
267 p_codcampaigntype IN ldd_campaign_types.codcampaigntype%TYPE,
268 p_codmasterkey IN ldd_hub_masterkey.codmasterkey%TYPE) -- T13683
269 RETURN ldd_types_source_mba.objtype%TYPE IS
270
271 v_objtype ldd_types_source_mba.objtype%TYPE;
272 v_businessArea ldd_hub_masterkey.business_area%TYPE;
273
274 BEGIN
275
276 -- T13683: per le lead orfane (senza MK)
277 BEGIN
278 SELECT lhm.business_area
279 INTO v_businessArea
280 FROM ldd_hub_masterkey lhm
281 WHERE lhm.codmasterkey=p_codmasterkey;
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 v_businessArea := '*';
285 END;
286
287 -- T13683: se SOURCE/TYPE non sono differenziati per MBA utilizzo OBJTYPE di default (*).
288 BEGIN
289 SELECT lcts.objtype
290 INTO v_objtype
291 FROM ldd_types_source_mba lcts
292 WHERE lcts.codcampaignsource = p_codcampaignsource
293 AND lcts.codcampaigntype = p_codcampaigntype
294 AND lcts.business_area = v_businessArea;
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297 SELECT lcts.objtype
298 INTO v_objtype
299 FROM ldd_types_source_mba lcts
300 WHERE lcts.codcampaignsource = p_codcampaignsource
301 AND lcts.codcampaigntype = p_codcampaigntype
302 AND lcts.business_area = '*';
303 END;
304
305 RETURN v_objtype;
306 END findObjType;
307
308-- Public findCampaign
309function findCampaign(p_codCampaign in ldd_campaign.codcampaign%type)
310 return elinkTypes.ref_collection is
311 myCursor elinkTypes.ref_collection;
312begin
313 open myCursor for
314 select lcmp.*
315 from ldd_campaign lcmp
316 where lcmp.codcampaign = p_codCampaign;
317
318 return myCursor;
319end findCampaign;
320
321-- Public findCampaignDetail
322function findCampaignDetail(p_codCampaignDetail in ldd_campaign_details.codcampaigndetail%type)
323 return elinkTypes.ref_collection is
324 myCursor elinkTypes.ref_collection;
325begin
326 open myCursor for
327 select lcmpdet.*
328 from ldd_campaign_details lcmpdet
329 where lcmpdet.codcampaigndetail = p_codCampaignDetail;
330
331 return myCursor;
332end findCampaignDetail;
333
334 -- Public findLeadByPrimaryKey
335 function findLeadByPrimaryKey(p_codLead in ldd_lead.codLead%type)
336 return elinkTypes.ref_collection is
337 myCursor elinkTypes.ref_collection;
338 begin
339 open myCursor for
340
341 SELECT *
342 FROM ldd_lead ll
343 left JOIN ldd_campaign_treatment lcmptrt
344 on lcmptrt.codcampaigndetail = ll.codcampaigndetail
345 left JOIN ldd_customercare lcc
346 on lcc.codlead = ll.codlead
347 left JOIN ldd_product lprod
348 on lprod.codlead = ll.codlead
349 left JOIN ldd_vehicle lvhcl
350 on lvhcl.codlead = ll.codlead
351 left JOIN ldd_action lact
352 on lact.codlead = ll.codlead
353 left JOIN ldd_agenda lag
354 on lag.codlead = ll.codlead
355 INNER JOIN ldd_campaign_details lcmpdet
356 on lcmpdet.codcampaigndetail = ll.codcampaigndetail
357 INNER JOIN ldd_campaign lcmp
358 on lcmp.codcampaign = lcmpdet.codcampaign
359 WHERE ll.codlead = p_codLead;
360
361 return myCursor;
362 end;
363
364 -- Public findLeadByLeadExt
365 function findLeadByLeadExt(p_codleadext in ldd_lead.codleadext%type,
366 p_codmarket in ldd_campaign_details.codmarket%type,
367 p_codcampaignsource in ldd_campaign.codcampaignsource%type,
368 p_codcampaigntype in ldd_campaign.codcampaigntype%type,
369 p_codmaindealer in ldd_campaign_details.codmaindealer%type)
370 return elinkTypes.ref_collection is
371 myCursor elinkTypes.ref_collection;
372 begin
373 open myCursor for
374 select ll.*,
375 to_char((ll.createddate AT TIME ZONE 'UTC'), 'YYYY-MM-DD HH24:MI:SSxFF TZD') AS CREATEDDATE_UTC,
376 to_char((ll.assigndate AT TIME ZONE 'UTC'), 'YYYY-MM-DD HH24:MI:SSxFF TZD') AS ASSIGNDATE_UTC
377 from ldd_lead ll
378 inner join ldd_campaign_details lc
379 on (lc.codcampaigndetail = ll.codcampaigndetail)
380 inner join ldd_campaign ldc
381 on (ldc.codcampaign = lc.codcampaign)
382 where ll.codleadext = p_codleadext
383 and lc.codmarket = p_codmarket
384 and ldc.codcampaignsource = p_codcampaignsource
385 and ldc.codcampaigntype = p_codcampaigntype
386 and lc.codmaindealer = p_codmaindealer;
387
388 return myCursor;
389
390 end findLeadByLeadExt;
391
392-- Public findAllLeadByContact
393--- B30267 - Calcolo del semaforo uguale a quello di PKLDD_LEADS ( compatibilita' )
394function findAllLeadByContact
395 (p_codContact in ldd_lead.codcontact%type,
396 p_codLanguage in dictionarylanguage.codlanguage%type,
397 p_objtype in LDD_OBJTYPES.objtype%type := NULL)
398 return elinkTypes.ref_collection is
399 myCursor elinkTypes.ref_collection;
400 x_checkDate CONSTANT DATE := SYSDATE;
401 v_checkMinutesPin NUMBER;
402BEGIN
403 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',(select c.codmarket from contact c where c.codcontact = p_codContact),1,0) into v_checkMinutesPin from dual;
404 open myCursor for
405 SELECT ll.codlead,
406 ( SELECT LRN.REMINDER_NOTE
407 FROM LDD_REMINDER_NOTES LRN
408 WHERE LRN.CODLEAD = LL.CODLEAD ) as OFFER,
409 lsrc.description as SOURCELEAD,
410 ltp.description as TYPELEAD,
411 lcmpdet.CODBRAND,
412 -- T11796 phone lead brand Unknown
413 CASE
414 WHEN NVL(p_objtype,'L') = 'P' THEN (SELECT mk.codbrand
415 FROM ldd_lead_hub_masterkey lhm
416 LEFT JOIN ldd_hub_masterkey mk
417 ON lhm.codmasterkey =
418 mk.codmasterkey
419 where lhm.codlead =
420 ll.codlead)
421 ELSE (select BR.BRANDNAME
422 from BRAND BR
423 where BR.CODBRAND = lcmpdet.CODBRAND)
424 END as BRAND,
425 LDP.DESCRPRODUCT AS COMMERCIALMODEL,
426 LDP.CODMODEL,
427--- T13231
428 LDP.CONFIGURATOR_ID,
429 TO_CHAR(ll.createddate,'YYYY-MM-DD HH24:MI:SS') as DATALEAD,
430 ll.codleadstatus,
431 ( SELECT ( SELECT pkdict.tran(ls.coddictionary, p_codLanguage) FROM DUAL )
432 FROM ldd_status ls
433 WHERE ls.codleadstatus = ll.codleadstatus ) as DESCRIPTIONSTATUS,
434 decode(ll.codleadstatus, 'A', 0, 1) as EDITABLE,
435--- B30267 - Semplificazione. Severitylevel ottenuto in base ai livelli calcolati
436--- al momento della creazione della lead/intervista
437 CASE WHEN LL.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
438 LL.FINAL_SEVERITYLEVELFIELD
439 ELSE
440 NVL( ( select MAX(LSR.SEVERITYLEVELFIELD) KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
441 FROM LDD_LEADS_SLARANGES LSR
442 WHERE LSR.CODLEAD = LL.CODLEAD
443 --AND LSR.CODSLAEVENT = 'L'
444 AND LSR.CODSLAEVENT = DECODE(NVL(LL.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
445 AND LSR.SLALIMIT > x_checkDate ) , 'R' )
446 END AS PRIORITY,
447 ll.sentmail AS SENTMAIL,
448 ll.codcampaigndetail AS CODCAMPAIGNDETAIL,
449--- T7839 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
450--- T7990 - In caso di lead da assegnare va sempre mostrato il numero oscurato da asterischi
451--- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
452 pkLDD_LEADS.getProperPhoneNumber(
453 lcmpdet.codmarket,
454 LL.CODLEADSTATUS,
455 LL.Insertiondate,
456 LL.MOBILEPHONE,
457 LL.PHONENUMBER,
458 TBK.CODLEAD,
459 TBK.DATE_SENT,
460 TBK.PIN_MOBILE,
461 TBK.PIN_PHONENUMBER,
462 TBK.TB_RESULT,
463 TBK.TB_STATUS,
464 v_checkMinutesPin,
465 ll.Communicationchannel,
466 CT.EMAIL_ADDRESS) AS PHONENUMBER,
467--- T7839 - Un flag indica se il numero di telefono riguarda un PIN
468-- T14290 - Refactoring del calcolo relativo al Pin. Ora il calcolo ? centralizzato
469 pkLDD_LEADS.checkIsPin(
470 lcmpdet.codmarket,
471 LL.CODLEADSTATUS,
472 LL.INSERTIONDATE,
473 NVL(TBK.CODLEAD,ll.codlead),
474 TBK.DATE_SENT,
475 TBK.TB_RESULT,
476 TBK.TB_STATUS,
477 v_checkMinutesPin,
478 ll.communicationchannel) AS ISPIN,
479--- T8261 - aggiunta campo untreated in output
480 ll.untreated,
481 ll.callcenter_callback,
482 pkLDD_LEADS.isUnmanagedToMask(
483 ll.codlead,
484 lcmpdet.codmarket) AS ISUNMANAGEDTOMASK, -- T16384
485--- T8979 - aggiunta campo nome campagna
486 lcmp.namecampaign,
487--- T9690- aggiunta campo objtype
488 ll.objtype,
489 ll.codleadext,
490 pkldd_dms.getDMSLeadStatus (ll.codlead, lcmpdet.codmarket) AS DMSSTATUS, -- T12367
491 (select pkdict.tran(
492 (select max(act.coddictionary) keep(dense_rank first order by ldh.creationdate desc, cac.codactioncontact desc)
493 from ldd_leadhistory ldh
494 join crm_actioncontact cac
495 on cac.codactioncontact = ldh.codactioncontact
496 join crm_action act
497 on act.codaction = cac.codaction
498 where ldh.codlead = ll.codlead
499 and ldh.codleadstatus = 'IG'
500 ),
501 p_codLanguage) from dual) AS MANAGEDACTION,
502 (select pkdict.tran(
503 (select max(act.coddictionary) keep(dense_rank first order by cac.planneddate asc, cac.codactioncontact asc)
504 from ldd_leadhistory ldh
505 join crm_actioncontact cac
506 on cac.codactioncontact = ldh.codactioncontact
507 join crm_action act
508 on act.codaction = cac.codaction
509 where ldh.codlead = ll.codlead
510 and ldh.codleadstatus = 'IG'
511 and cac.codactionstatus <> 'C'
512 and cac.codaction in (
513 'AP',
514 'CE',
515 'RC',
516 'SQ',
517 'FU',
518 'TD'
519 )
520 ),
521 p_codLanguage) from dual) AS FIRSTPLANNEDACTION,
522--- T13748 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
523 BUA.BUSINESS_AREA AS MBA,
524 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
525 DL.ADDRESS||' - '||DL.TOWN_NAME AS SITE, -- (la concatenazione dei campi ADDRESS e TOWN_NAME )
526--- T13748 -END-
527 ll.communicationchannel,
528 ll.leadscore AS LEAD_SCORE, -- TRK
529 hm.ishot,
530 --T16172
531 hm.level1,
532 hm.level2
533 FROM ldd_lead ll
534 JOIN ldd_campaign_details lcmpdet
535 on lcmpdet.codcampaigndetail = ll.codcampaigndetail
536 JOIN ldd_campaign lcmp
537 on lcmp.codcampaign = lcmpdet.codcampaign
538 JOIN ldd_campaign_sources lsrc
539 on lsrc.codcampaignsource = lcmp.codcampaignsource
540 JOIN ldd_campaign_types ltp
541 on ltp.codcampaigntype = lcmp.codcampaigntype
542 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LL.CODLEAD )
543 LEFT JOIN LDD_PRODUCT LDP ON ( LDP.CODLEAD = LL.CODLEAD )
544
545 LEFT JOIN DEALER D ON ( D.CODMARKET = lcmpdet.CODMARKET and D.CODDEALER = lcmpdet.CODDEALER )
546 LEFT JOIN DEALERLOCATION DL on ( DL.CODMARKET = D.CODMARKET and DL.CODDEALER = D.CODDEALER and DL.CODLOCATION = lcmpdet.CODDEALERLOCATION )
547
548--- T13748 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
549 left join (select ba.business_area as BUSINESS_AREA,
550 ba.description as BUSINESS_AREA_DESCRIPTION,
551 lh.codlead as BUSINESS_AREA_CODLEAD
552 from LDD_LEAD_HUB_MASTERKEY lh
553 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
554 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
555 on BUA.BUSINESS_AREA_CODLEAD = ll.CODLEAD
556--- T13748 -END-
557
558 LEFT join CONTACT CT on (CT.CODCONTACT = ll.CODCONTACT)
559 LEFT JOIN ldd_lead_hub_masterkey lhm
560 ON lhm.codlead = ll.codlead
561 LEFT JOIN ldd_hub_masterkey hm
562 ON lhm.codmasterkey = hm.codmasterkey
563
564 WHERE ll.codcontact = p_codContact
565 and ll.objtype = NVL(p_objtype,'L')
566 ORDER BY ll.codlead DESC;
567
568 return myCursor;
569
570end findAllLeadByContact;
571-- Public findAllOpenLeadByContact
572function findAllOpenLeadByContactWithDA
573 (p_codContact in ldd_lead.codcontact%type,
574 p_codLanguage in dictionarylanguage.codlanguage%type )
575 return elinkTypes.ref_collection is
576 myCursor elinkTypes.ref_collection;
577begin
578 open myCursor for
579 SELECT ll.codlead,
580 lsrc.description as SOURCELEAD,
581 ltp.description as TYPELEAD,
582 TO_CHAR(ll.createddate,'YYYY-MM-DD HH24:MI:SS') as DATALEAD,
583 ll.codleadstatus,
584 ( SELECT pkdict.tran(ls.coddictionary, p_codLanguage) FROM DUAL ) as DESCRIPTIONSTATUS,
585 decode(ll.codleadstatus, 'A', 0, 1) as EDITABLE,
586 ll.objtype as OBJTYPE,
587 t.pin_mobile,
588 t.pin_phonenumber
589 FROM ldd_lead ll
590 JOIN ldd_campaign_details lcmpdet on lcmpdet.codcampaigndetail = ll.codcampaigndetail
591 JOIN ldd_campaign lcmp on lcmp.codcampaign = lcmpdet.codcampaign
592 JOIN ldd_campaign_sources lsrc on lsrc.codcampaignsource = lcmp.codcampaignsource
593 JOIN ldd_campaign_types ltp on ltp.codcampaigntype = lcmp.codcampaigntype
594 JOIN ldd_campaign_types_source LTS
595 on (LTS.CODCAMPAIGNSOURCE = LSRC.CODCAMPAIGNSOURCE AND
596 LTS.CODCAMPAIGNTYPE = LTP.CODCAMPAIGNTYPE )
597 JOIN ldd_status ls on ls.codleadstatus = ll.codleadstatus
598 left join ldd_trackback t on ll.codlead=t.codlead
599 WHERE ll.codcontact = p_codContact
600 and ll.codleadstatus <> 'AR';
601
602 return myCursor;
603
604end findAllOpenLeadByContactWithDA;
605
606-- Public findAllOpenLeadByContact
607function findAllOpenLeadByContact
608 (p_codContact in ldd_lead.codcontact%type,
609 p_codLanguage in dictionarylanguage.codlanguage%type )
610 return elinkTypes.ref_collection is
611 myCursor elinkTypes.ref_collection;
612begin
613 open myCursor for
614 SELECT ll.codlead,
615 lsrc.description as SOURCELEAD,
616 ltp.description as TYPELEAD,
617 TO_CHAR(ll.createddate,'YYYY-MM-DD HH24:MI:SS') as DATALEAD,
618 ll.codleadstatus,
619 ( SELECT pkdict.tran(ls.coddictionary, p_codLanguage) FROM DUAL ) as DESCRIPTIONSTATUS,
620 decode(ll.codleadstatus, 'A', 0, 1) as EDITABLE
621 FROM ldd_lead ll
622 JOIN ldd_campaign_details lcmpdet on lcmpdet.codcampaigndetail = ll.codcampaigndetail
623 JOIN ldd_campaign lcmp on lcmp.codcampaign = lcmpdet.codcampaign
624 JOIN ldd_campaign_sources lsrc on lsrc.codcampaignsource = lcmp.codcampaignsource
625 JOIN ldd_campaign_types ltp on ltp.codcampaigntype = lcmp.codcampaigntype
626 JOIN ldd_campaign_types_source LTS
627 on (LTS.CODCAMPAIGNSOURCE = LSRC.CODCAMPAIGNSOURCE AND
628 LTS.CODCAMPAIGNTYPE = LTP.CODCAMPAIGNTYPE )
629 JOIN ldd_status ls on ls.codleadstatus = ll.codleadstatus
630 WHERE ll.codcontact = p_codContact
631 and ll.codleadstatus in ('DG', 'IG');
632
633 return myCursor;
634
635end findAllOpenLeadByContact;
636
637-- Public countAllOpenLeadByContact
638function countAllOpenLeadByContact
639 (p_codContact in ldd_lead.codcontact%type )
640 return number is
641 res number;
642begin
643
644 SELECT count(*)
645 into res
646 FROM ldd_lead ll
647 JOIN ldd_status ls on ls.codleadstatus = ll.codleadstatus
648 WHERE ll.codcontact = p_codContact
649 and ll.codleadstatus in ('DG', 'IG');
650
651 return res;
652
653end countAllOpenLeadByContact;
654
655-- Public countAllLeadByContact
656function countAllLeadByContact
657 (p_codContact in ldd_lead.codcontact%type )
658 return number is
659 res number;
660BEGIN
661 SELECT count(*)
662 into res
663 FROM ldd_lead ll
664 JOIN ldd_status ls on ls.codleadstatus = ll.codleadstatus
665 WHERE ll.codcontact = p_codContact;
666 return res;
667end countAllLeadByContact;
668
669-- Public getActiveCampaigns
670--- ... sarebbe da fare dinamica
671function getActiveCampaigns
672 (p_codbrand IN ldd_campaign_details.codbrand%type DEFAULT NULL,
673 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null,
674 p_coddealer IN ldd_campaign_details.coddealer%type default null,
675 p_iscentral IN ldd_campaign.iscentral%type default 0,
676 p_codmarket IN ldd_campaign_details.codmarket%type default null)
677 return elinkTypes.ref_collection is
678 myCursor elinkTypes.ref_collection;
679begin
680 open myCursor for
681 SELECT DISTINCT lcmp.codcampaign,
682 lcmp.namecampaign,
683 lcmp.description,
684 LCMP.CODCAMPAIGNSOURCE,
685 LCMP.CODCAMPAIGNTYPE
686 FROM ldd_campaign lcmp
687 INNER JOIN ldd_campaign_details lcmpdet
688 on lcmpdet.codcampaign = lcmp.codcampaign
689 WHERE 1 = 1
690 AND (lcmp.iscentral = p_iscentral OR p_iscentral IS NULL)
691 AND (lcmpdet.codmaindealer = p_codmaindealer or p_codmaindealer is null)
692 AND (lcmpdet.coddealer = p_coddealer OR p_coddealer IS NULL)
693 AND (lcmpdet.codbrand = p_codbrand OR p_codbrand IS NULL)
694 AND lcmpdet.codmarket = p_codmarket
695 AND ( -- se non ho un dealer/codmaindealer allora prendo le campagne DBCARE PUSH
696 (p_codmaindealer is not null or p_coddealer IS not NULL) OR
697 (p_codmaindealer is null and p_coddealer IS NULL and lcmp.codcampaigntype='PS' and lcmp.codcampaignsource='DC'))
698 ORDER BY lcmp.namecampaign;
699
700 return myCursor;
701end;
702
703-- Public getAllCampaignSources
704function getAllCampaignSources return elinkTypes.ref_collection is
705 myCursor elinkTypes.ref_collection;
706 begin
707 open myCursor for
708 SELECT * from ldd_campaign_sources lcs;
709
710 return myCursor;
711end;
712
713 -- Public getAllCampaignTypes
714 function getAllCampaignTypes return elinkTypes.ref_collection is
715 myCursor elinkTypes.ref_collection;
716 begin
717 open myCursor for
718
719 SELECT * from ldd_campaign_types lct;
720
721 return myCursor;
722 end;
723
724 -- Public getCampaignTypesBySource
725function getCampaignTypesBySource
726 (p_codcampaignsource in ldd_campaign_sources.codcampaignsource %type,
727 p_codLanguage in dictionarylanguage.codlanguage%type)
728 return elinkTypes.ref_collection is
729 myCursor elinkTypes.ref_collection;
730begin
731 open myCursor for
732 SELECT lct.codcampaigntype, description
733 from ldd_campaign_types lct
734 join ldd_campaign_types_source lcts on (lcts.codcampaigntype = lct.codcampaigntype)
735 where lcts.codcampaignsource = p_codcampaignsource;
736
737 return myCursor;
738end getCampaignTypesBySource;
739
740-- Public getCampaignSourceFiltered
741function getCampaignSourceFiltered
742 (p_codcampaign in ldd_campaign.codcampaign%type,
743 p_codmarket IN ldd_campaign_details.codmarket%type default null,
744 p_codbrand IN ldd_campaign_details.codbrand%type DEFAULT NULL,
745 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null )
746 return elinkTypes.ref_collection is
747 myCursor elinkTypes.ref_collection;
748BEGIN
749 x_sqlStmt := '
750select /* getCampaignSourceFiltered */ lcs.codcampaignsource, lcs.description
751 FROM ldd_campaign_sources lcs
752 WHERE 1 = 1
753 AND EXISTS
754( SELECT 1
755 FROM ldd_campaign_types_source LTS
756 WHERE LTS.codcampaignsource = lcs.codcampaignsource ) ';
757
758 IF p_codcampaign IS NOT NULL OR
759 p_codmarket IS NOT NULL OR
760 p_codbrand IS NOT NULL OR
761 p_codmaindealer IS NOT NULL THEN
762
763 x_sqlStmt := x_sqlStmt || ' AND EXISTS
764( SELECT 1
765 FROM ldd_campaign LCP
766 JOIN ldd_campaign_details lcd
767 ON ( lcd.codcampaign = LCP.codcampaign )
768 WHERE LCP.codcampaignsource = lcs.codcampaignsource ';
769 IF p_codcampaign IS NOT NULL THEN
770 x_sqlStmt := x_sqlStmt || ' AND LCP.CODCAMPAIGN = :p_codCampaign ';
771 ELSE
772 x_sqlStmt := x_sqlStmt || ' AND :p_codCampaign IS NULL ';
773 END IF;
774 IF p_codmarket IS NOT NULL THEN
775 x_sqlStmt := x_sqlStmt || ' AND LCD.CODMARKET = :p_codMarket ';
776 ELSE
777 x_sqlStmt := x_sqlStmt || ' AND :p_codMarket IS NULL ';
778 END IF;
779 IF p_codbrand IS NOT NULL THEN
780 x_sqlStmt := x_sqlStmt || ' AND LCD.CODBRAND = :p_codBrand ';
781 ELSE
782 x_sqlStmt := x_sqlStmt || ' AND :p_codBrand IS NULL ';
783 END IF;
784 IF p_codmaindealer IS NOT NULL THEN
785 x_sqlStmt := x_sqlStmt || ' AND LCD.CODMAINDEALER = :p_codMainDealer ';
786 ELSE
787 x_sqlStmt := x_sqlStmt || ' AND :p_codMainDealer IS NULL ';
788 END IF;
789 x_sqlStmt := x_sqlStmt || ' ) ';
790
791 ELSE
792 x_sqlStmt := x_sqlStmt || ' AND :p_codcampaign IS NULL
793AND :p_codmarket IS NULL
794AND :p_codbrand IS NULL
795AND :p_codmaindealer IS NULL ';
796 END IF;
797 x_sqlStmt := x_sqlStmt || ' ORDER BY 1 ';
798
799 open myCursor for x_sqlStmt USING p_codcampaign,
800 p_codmarket,
801 p_codbrand,
802 p_codmaindealer;
803 RETURN myCursor;
804
805end getCampaignSourceFiltered;
806
807-- Public getCampaignTypesFiltered
808function getCampaignTypesFiltered
809 (p_codcampaign in ldd_campaign.codcampaign%type,
810 p_codmarket IN ldd_campaign_details.codmarket%type default null,
811 p_codbrand IN ldd_campaign_details.codbrand%type DEFAULT NULL,
812 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null )
813 return elinkTypes.ref_collection is
814 myCursor elinkTypes.ref_collection;
815BEGIN
816 x_sqlStmt := '
817select /* getCampaignTypesFiltered */ lct.codcampaigntype, lct.description
818 FROM ldd_campaign_types lct
819 WHERE 1 = 1
820 AND EXISTS
821( SELECT 1
822 FROM ldd_campaign_types_source LTS
823 WHERE LTS.codcampaigntype = lct.codcampaigntype ) ';
824
825 IF p_codcampaign IS NOT NULL OR
826 p_codmarket IS NOT NULL OR
827 p_codbrand IS NOT NULL OR
828 p_codmaindealer IS NOT NULL THEN
829
830 x_sqlStmt := x_sqlStmt || ' AND EXISTS
831( SELECT 1
832 FROM ldd_campaign LCP
833 JOIN ldd_campaign_details lcd
834 ON ( lcd.codcampaign = LCP.codcampaign )
835 WHERE LCP.codcampaigntype = LCT.codcampaigntype ';
836 IF p_codcampaign IS NOT NULL THEN
837 x_sqlStmt := x_sqlStmt || ' AND LCP.CODCAMPAIGN = :p_codCampaign ';
838 ELSE
839 x_sqlStmt := x_sqlStmt || ' AND :p_codCampaign IS NULL ';
840 END IF;
841 IF p_codmarket IS NOT NULL THEN
842 x_sqlStmt := x_sqlStmt || ' AND LCD.CODMARKET = :p_codMarket ';
843 ELSE
844 x_sqlStmt := x_sqlStmt || ' AND :p_codMarket IS NULL ';
845 END IF;
846 IF p_codbrand IS NOT NULL THEN
847 x_sqlStmt := x_sqlStmt || ' AND LCD.CODBRAND = :p_codBrand ';
848 ELSE
849 x_sqlStmt := x_sqlStmt || ' AND :p_codBrand IS NULL ';
850 END IF;
851 IF p_codmaindealer IS NOT NULL THEN
852 x_sqlStmt := x_sqlStmt || ' AND LCD.CODMAINDEALER = :p_codMainDealer ';
853 ELSE
854 x_sqlStmt := x_sqlStmt || ' AND :p_codMainDealer IS NULL ';
855 END IF;
856 x_sqlStmt := x_sqlStmt || ' ) ';
857
858 ELSE
859 x_sqlStmt := x_sqlStmt || ' AND :p_codcampaign IS NULL
860AND :p_codmarket IS NULL
861AND :p_codbrand IS NULL
862AND :p_codmaindealer IS NULL ';
863 END IF;
864 x_sqlStmt := x_sqlStmt || ' ORDER BY 1 ';
865
866 open myCursor for x_sqlStmt USING p_codcampaign,
867 p_codmarket,
868 p_codbrand,
869 p_codmaindealer;
870 RETURN myCursor;
871
872end getCampaignTypesFiltered;
873 /* insert email action */
874 function updateFlagSentEmail(p_codlead in ldd_lead.codlead%type,
875 p_codcontact in ldd_lead.codcontact%type)
876 return NUMBER is
877 begin
878
879 UPDATE LDD_LEAD
880 SET SENTMAIL = 1
881 where CODLEAD = p_codlead
882 and CODCONTACT = p_codcontact;
883 RETURN SQL%RowCount;
884 EXCEPTION
885 WHEN OTHERS THEN
886 RETURN SQLCODE;
887 end updateFlagSentEmail;
888
889 /* prendo il brand dalla campaign detail */
890 function getBrandFromCampaign(p_codmaindealer in ldd_campaign_details.codmaindealer%type,
891 p_codmarket in ldd_campaign_details.codmarket%type,
892 p_codcampaign in ldd_campaign_details.codcampaigndetail%type)
893 return elinkTypes.ref_collection is
894 myCursor elinkTypes.ref_collection;
895 begin
896 open myCursor for
897
898 select lcd.codbrand, b.brandname
899 from ldd_campaign_details lcd
900 join brand b
901 on b.codbrand = lcd.codbrand
902 where lcd.codmaindealer = p_codmaindealer
903 and lcd.codmarket = p_codmarket
904 and lcd.codcampaigndetail = p_codcampaign;
905
906 return myCursor;
907 end getBrandFromCampaign;
908-- Modifica lo stato di un array di codAction
909-- Resituisce il numero di insuccessi... Se restituisce 0 allora tutto ok!!!
910function setStatusOnLeadList
911 (p_leadsList in leadsTable,
912 p_status in crm_configurationstatus.codstatus%type,
913 p_resultStatus in crm_negotiationresult.codnegotiationresult%type,
914 p_userGID crm_actioncontact.usergid%type,
915 p_datePurchaseDelayed in crm_negotiationnote.purchasedelay%type default null,
916 p_codbrandinfocar in ldd_leadnote.codbrandinfocar%type default null)
917 return number is
918 res number := 0;
919 v_insucces number := 0;
920 v_codcontact crm_actioncontact.codcontact%type;
921
922BEGIN
923
924 IF p_leadsList.count >= 1 THEN
925
926 for idxTable in p_leadsList.first .. p_leadsList.last loop
927 select l.codcontact
928 into v_codcontact
929 from ldd_lead l
930 where l.codlead = p_leadsList(idxTable);
931
932--- T9770 - Valorizzato p_codLead nella chiamata a pkCRMCORE
933 res := pkCRMCore.crmUpgradeStatus (p_codmarket => NULL,
934 p_codmaindealer => NULL,
935 p_usergid => p_usergid,
936 p_codaction => p_status,
937 p_codcontact => v_codcontact,
938 p_codactioncontact => NULL,
939 p_codactionstatus => NULL,
940 p_planneddate => SYSDATE,
941 p_codnegotiation => p_leadsList(idxTable),
942 p_namenegotiation => NULL,
943 p_codnegotiationresult => p_resultStatus,
944 p_note => NULL,
945 p_resultcomment => NULL,
946 p_codpurchasemode => NULL,
947 p_codevent => NULL,
948 p_datepurchasedelayed => p_datePurchaseDelayed,
949 p_codoffer => NULL,
950 p_codbrandinfocar => p_codbrandinfocar,
951 p_codbrand => NULL,
952 p_codlead => p_leadsList(idxTable) );
953
954 IF (res = 0) THEN
955 v_insucces := v_insucces + 1;
956 END IF;
957
958 end loop;
959
960 END IF;
961
962 return v_insucces;
963
964end setStatusOnLeadList;
965
966--- T4789 - Procedura per l'estrazione dei dati relativi alle LEAD di un mercato la cui data di creazione
967--- e' relativa alla settimana COMPLETA precedente alla data fornita come parametro
968--- es: data = Giovedi' 20/01 ---> settimana : 10/01 - 16/01
969FUNCTION extractLeadsForODE
970 (P_CODMARKET IN LDD_CAMPAIGN_DETAILS.CODMARKET%TYPE ,
971 p_startDate IN DATE DEFAULT SYSDATE )
972RETURN INTEGER IS
973
974 cursor c01 ( P1 IN DATE, P2 IN CHAR ) is
975 select cd.codmarket as COD_MARKET,
976 ca.namecampaign as CAMPAIGN_NAME,
977 ca.description as CAMPAIGN_OBJECT,
978 TO_CHAR(ca.startdate,'DD/MM/YYYY') as CAMPAIGN_DATE_START,
979 TO_CHAR(ca.enddate,'DD/MM/YYYY') as CAMPAIGN_DATE_END,
980 ca.codcampaignsource as SOURCE,
981 ca.codcampaigntype as TYPE,
982 ca.codcampaignext as CAMPAIGN_CODE_EXT,
983 cd.codcampaigndetailext as CAMPAIGN_DETAIL_CODE_EXT,
984 le.codlanguage as LANGUAGE,
985 cd.codbrand as BRAND,
986 pr.codmodel as MODEL,
987 cd.coddealer as DEALER,
988 cd.coddealerlocation as LOCATION,
989 le.codlead as CODLEAD,
990--- T8553 - estrapolazione delle reminder notes
991 ( SELECT LRN.REMINDER_NOTE
992 FROM LDD_REMINDER_NOTES LRN
993 WHERE LRN.CODLEAD = LE.CODLEAD ) as NOTES,
994 case
995 when pe.codperson is not null then
996 pe.firstname
997 else
998 comp.companyname
999 end as FIRST_NAME_COMPANY_NAME,
1000 pe.lastname as LAST_NAME,
1001 co.email_address as EMAIL,
1002 CO.ADDRESS1 as ADDRESS,
1003 co.pobox as POBOX,
1004 co.cap as ZIP,
1005 case
1006 when ci.coddictionary is not null then
1007 pkdict.tran(ci.coddictionary, le.codlanguage)
1008 else
1009 cast(ci.descr_city as NVARCHAR2(2000))
1010 end as CITY,
1011--- T9688 - Scalar Subqueries
1012 ( SELECT ( SELECT pkdict.tran(de.coddictionary, le.codlanguage) FROM DUAL )
1013 FROM department de
1014 WHERE co.coddepartment = de.coddepartment
1015 and de.codregion = co.codregion
1016 and de.codnation = co.codnation) as PROVINCE,
1017 ( SELECT ( SELECT pkdict.tran(re.coddictionary, le.codlanguage) FROM DUAL )
1018 FROM region re
1019 WHERE co.codregion = re.codregion
1020 and co.codnation = re.codnation ) as REGION,
1021 ( SELECT ( SELECT pkdict.tran(na.coddictionary, le.codlanguage) FROM DUAL )
1022 FROM nation na
1023 WHERE co.codnation = na.codnation) as NATION,
1024 co.telephone as PHONE_NUMBER,
1025 co.mobile_phone as MOBILE_PHONE,
1026 pe.dataprotection as PRIVACY,
1027 pe.codstudytitle as STUDY_TITLE_NAME,
1028 comp.codcompanytype as COMPANY_TYPE,
1029 pe.fiscalcode as FISCAL_CODE,
1030 comp.piva as P_IVA,
1031 '' as JOB,
1032 le.codleadstatus as LEAD_STATUS,
1033 '' as LEAD_RESULT,
1034 '' as IS_CENTRAL,
1035 TO_CHAR(co.createddate,'DD/MM/YYYY') as CONTACT_DATE,
1036--- T5661 estrazione della stringa di 400 chrs per Mida
1037 CASE WHEN NVL(P2,'N') = 'Y' THEN
1038 PKPRIVACYENGINE.GETPRIVACYFORMIDA(CO.CODCONTACT,CD.CODMARKET)
1039 ELSE
1040 RPAD(' ',407)
1041 END AS PRIVACYFORMIDA
1042 from ldd_lead le
1043 join ldd_campaign_details cd
1044 on (cd.codcampaigndetail = le.codcampaigndetail)
1045 join ldd_campaign ca
1046 on (ca.codcampaign = cd.codcampaign)
1047 join contact co
1048 on (co.codcontact = le.codcontact and co.codmarket = cd.codmarket)
1049 left join city ci
1050 on (co.codcity = ci.codcity and ci.coddepartment = co.coddepartment and
1051 ci.codregion = co.codregion and ci.codnation = co.codnation)
1052 left join ldd_product pr
1053 on (le.codlead = pr.codlead and cd.codmarket = pr.codmarket)
1054 left join person pe
1055 on (co.codcontact = pe.codperson)
1056 left join company comp
1057 on (comp.codcompany = co.codcontact)
1058 where cd.codmarket = p_codmarket
1059--- B29330 - Il confronto tra valori data di tipo diverso implica una trasformazione
1060--- automatica che permette il rapporto a parita' di time zone
1061 and le.CREATEDDATE between P1 and P1 + 7 - 1/86400
1062 and not exists (select 1 -- GDPR: il contatto non deve essere obliato
1063 from oblio_request rq
1064 where rq.codcontact = co.codcontact
1065 and rq.oblio_execdate is not null
1066 and rq.oblio_rollback = 0);
1067
1068 c01rec C01%Rowtype;
1069 v_fileHandle UTL_FILE.FILE_TYPE;
1070 v_nomeFile VARCHAR2(100);
1071--- Definizioni Globali
1072 x_logHandle LOGMASTER.SEQUENCEID%TYPE;
1073 x_sqlcode NUMBER;
1074 x_errDesc VARCHAR2(1024);
1075 v_address CONTACT.ADDRESS1%TYPE;
1076 v_notes LDD_REMINDER_NOTES.REMINDER_NOTE%TYPE;
1077 v_firstDay DATE := TRUNC(SYSDATE-7,'IW');
1078 v_procedureName CONSTANT VARCHAR2(50) := 'PKLEADCAMPAIGN.EXTRACTODE';
1079 v_flagNewPrivacy CHAR(1);
1080
1081BEGIN
1082
1083 x_logHandle := pkLOG.Insertmaster(p_codmarket,v_procedureName,NULL, '0' );
1084
1085--- calcolo del primo giorno relativo alla settimana completa precedente al parametro fornito in input
1086 v_firstDay := TRUNC(NVL(p_startDate,SYSDATE) - 7 , 'IW' );
1087
1088--- Il nome del file di output comprendera' il codmarket e il timestamp relativo all'estrazione
1089--- come richiesto del cliente.
1090 v_nomeFile := 'LINKSITELEAD_' || p_codmarket || '_' || to_char(sysdate, 'YYYYMMDDHH24MISS') || '.csv';
1091 v_fileHandle := UTL_FILE.FOPEN('LEAD', v_nomeFile, 'w',5120);
1092
1093--- LOG
1094 pklog.writedetail(vloghandle=>x_logHandle, llevel=>PKLOG.INFO,
1095 pprocedurename=>v_procedureName,
1096 description=>'File output : ',
1097 details=>'DIRECTORY : LEAD - File : '||v_nomeFile);
1098
1099 pklog.writedetail(vloghandle=>x_logHandle, llevel=>PKLOG.INFO,
1100 pprocedurename=>v_procedureName,
1101 description=>'Range : ',
1102 details=>'FROM '||TO_CHAR(v_firstDay,'DD/MM/YYYY HH24:MI')||' TO '||TO_CHAR(v_firstDay+ 7 - 1/86400,'DD/MM/YYYY HH24:MI') );
1103
1104--- T5661 - occorre sapere se per il mercato in questione c'e' la "nuova" gestione della privacy
1105 BEGIN
1106 SELECT 'Y' INTO v_flagNewPrivacy
1107 FROM marketparams mp
1108 WHERE mp.codmarket = p_codmarket
1109 AND mp.codmarketparameter = 'PRIVACY_UE_ENABLE'
1110 AND mp.mpvalue like '%1%';
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 v_flagNewPrivacy := 'N';
1114 END;
1115
1116 open c01 ( v_firstDay,v_flagNewPrivacy );
1117 loop
1118 fetch c01 into c01rec;
1119 exit when c01%notfound;
1120
1121--- Alcuni dati ( Address e Reminder_note ) devono essere "mondati" dei caretteri indesiderati :
1122--- - Chr(13) <CR>
1123--- - CHR(10) <LF>
1124--- - CHR(9) <TAB>
1125--- - ";" ... il carattere ";" viene utilizzato come separatore, la sua presenza all'interno dei campi fa casino
1126
1127 v_address := REPLACE(c01rec.address,CHR(10),' ');
1128 v_address := REPLACE(v_address,CHR(13),' ');
1129 v_address := REPLACE(v_address,CHR(9),' ');
1130 v_address := REPLACE(v_address,';',',');
1131
1132 v_notes := REPLACE(c01rec.notes,CHR(10),' ');
1133 v_notes := REPLACE(v_notes,CHR(13),' ');
1134 v_notes := REPLACE(v_notes,CHR(9),' ');
1135 v_notes := REPLACE(v_notes,';',',');
1136
1137 utl_file.put_line(v_filehandle,
1138 C01rec.cod_market || ';' || C01rec.CAMPAIGN_NAME || ';' ||
1139 C01rec.CAMPAIGN_OBJECT || ';' ||
1140 C01rec.CAMPAIGN_DATE_START || ';' ||
1141 C01rec.CAMPAIGN_DATE_END || ';' || C01rec.SOURCE || ';' ||
1142 C01rec.TYPE || ';' || C01rec.CAMPAIGN_CODE_EXT || ';' ||
1143 C01rec.CAMPAIGN_DETAIL_CODE_EXT || ';' ||
1144 C01rec.LANGUAGE || ';' || C01rec.BRAND || ';' ||
1145 C01rec.MODEL || ';' || C01rec.DEALER || ';' ||
1146 C01rec.LOCATION || ';' || C01rec.CODLEAD || ';' ||
1147 v_notes || ';' || C01rec.FIRST_NAME_COMPANY_NAME || ';' ||
1148 C01rec.LAST_NAME || ';' || C01rec.EMAIL || ';' ||
1149 v_address || ';' || C01rec.POBOX || ';' ||
1150 C01rec.ZIP || ';' || C01rec.CITY || ';' ||
1151 C01rec.PROVINCE || ';' || C01rec.REGION || ';' ||
1152 C01rec.NATION || ';' || C01rec.PHONE_NUMBER || ';' ||
1153 C01rec.MOBILE_PHONE || ';' || C01rec.PRIVACY || ';' ||
1154 C01rec.STUDY_TITLE_NAME || ';' || C01rec.COMPANY_TYPE || ';' ||
1155 C01rec.FISCAL_CODE || ';' || C01rec.P_IVA || ';' ||
1156 C01rec.JOB || ';' || C01rec.LEAD_STATUS || ';' ||
1157 C01rec.LEAD_RESULT || ';' || C01rec.IS_CENTRAL || ';' ||
1158 C01rec.CONTACT_DATE || ';' || C01rec.PRIVACYFORMIDA);
1159 end loop;
1160
1161--- LOG
1162 pklog.writedetail(vloghandle=>x_logHandle, llevel=>PKLOG.INFO,
1163 pprocedurename=>v_procedureName,
1164 description=>'records estratti : ',
1165 details=>to_char(C01%RowCount) );
1166 DBMS_OUTPUT.put_line ('records estratti : '||to_char(C01%RowCount));
1167
1168 close c01;
1169 utl_file.fclose(v_fileHandle);
1170
1171 PKLOG.UPDATEMASTER ( x_logHandle , 0 );
1172
1173 RETURN 0; -- OK
1174
1175EXCEPTION
1176 WHEN OTHERS THEN
1177 x_sqlCode := sqlcode;
1178 x_errDesc := DBMS_UTILITY.FORMAT_ERROR_STACK;
1179 pklog.writedetail(vloghandle=>x_logHandle, llevel=>PKLOG.WARNING,
1180 pprocedurename=>v_procedureName,
1181 description=>'ERR : '||to_char(x_sqlCode),
1182 details=>x_ErrDesc );
1183 PKLOG.UPDATEMASTER ( x_logHandle , x_sqlCode );
1184 if C01%ISOPEN Then
1185 CLOSE C01;
1186 End IF;
1187 UTL_FILE.FCLOSE_ALL;
1188
1189 RETURN 1; -- ERROR
1190
1191end extractLeadsForODE;
1192
1193-- Public getCampaignsBySource
1194--- ...non si puo' vedere!!!
1195function getCampaignsBySource
1196 (p_codcampaignsource in ldd_campaign.codcampaignsource%type,
1197 p_codmarket IN ldd_campaign_details.codmarket%type default null,
1198 p_codmaindealer IN ldd_campaign_details.codmaindealer%type default null)
1199 return elinkTypes.ref_collection is
1200 myCursor elinkTypes.ref_collection;
1201 begin
1202 open myCursor for
1203 select distinct * from (
1204 SELECT
1205 lc.codcampaign,
1206 lc.codcampaignext,
1207 lc.codcampaignsource,
1208 lc.codcampaigntype,
1209 to_char (lc.creationdate, 'DD/MM/YYYY') as creationdate,
1210 lc.description,
1211 to_char(lc.enddate, 'DD/MM/YYYY' ) as enddate,
1212 lc.iscentral,
1213 lc.namecampaign,
1214 to_char(lc.startdate, 'DD/MM/YYYY' ) as startdate,
1215 lcd.codcampaigndetailext,
1216 lct.description as campaigntypedesc,
1217 lcd.codbrand,
1218 (select count(*)
1219 from ldd_lead l1
1220 join ldd_campaign_details lcd1 on (l1.codcampaigndetail = lcd1.codcampaigndetail)
1221 left join ldd_campaign lc1 on (lc1.codcampaign = lcd1.codcampaign)
1222 where lcd1.codmaindealer=lcd.codmaindealer
1223 and lc1.codcampaignsource=lc.codcampaignsource and lc1.codcampaignext=lc.codcampaignext) as CONTACT_NUM
1224 FROM ldd_campaign lc
1225 JOIN ldd_campaign_details lcd on (lc.codcampaign = lcd.codcampaign)
1226 JOIN ldd_campaign_types lct on (lc.codcampaigntype = lct.codcampaigntype)
1227 WHERE 1 = 1
1228 AND lc.codcampaignsource=p_codcampaignsource
1229 AND (p_codmaindealer is null or lcd.codmaindealer=p_codmaindealer)
1230 AND (p_codmarket is null OR lcd.codmarket=p_codmarket)
1231 ORDER BY lc.startdate desc, lc.enddate desc
1232 );
1233 return myCursor;
1234end getCampaignsBySource;
1235
1236-- Public addCampaign
1237 function addCampaign(p_codcampaignext in ldd_campaign.codcampaignext%type,
1238 p_codcampaignsource in ldd_campaign.codcampaignsource%type,
1239 p_codcampaigntype in ldd_campaign.codcampaigntype%type,
1240 p_namecampaign in ldd_campaign.namecampaign%type,
1241 p_description in ldd_campaign.description%type default null,
1242 p_startdate in VARCHAR2 default null,
1243 p_enddate in VARCHAR2 default null,
1244 p_iscentral in ldd_campaign.iscentral%type default null)
1245 return integer is
1246 v_codcmp ldd_campaign.codcampaign%TYPE;
1247
1248 begin
1249
1250
1251 -- Creo ldd_campaign
1252 SELECT Q_CODCAMPAIGN.NEXTVAL INTO v_codcmp FROM DUAL;
1253 INSERT INTO ldd_campaign
1254 (codcampaign,
1255 codcampaignext,
1256 codcampaignsource,
1257 codcampaigntype,
1258 creationdate,
1259 description,
1260 enddate,
1261 iscentral,
1262 namecampaign,
1263 startdate)
1264 VALUES
1265 (v_codcmp,
1266 p_codcampaignext,
1267 p_codcampaignsource,
1268 p_codcampaigntype,
1269 sysdate,
1270 p_description,
1271 to_date(p_enddate,'DD/MM/YYYY'),
1272 p_iscentral,
1273 p_namecampaign,
1274 to_date(p_startdate,'DD/MM/YYYY')) ;
1275
1276 return v_codcmp;
1277EXCEPTION
1278 WHEN OTHERS THEN
1279 RETURN NULL;
1280end addCampaign;
1281
1282-- Public addCampaignDetail
1283--- T7223 - Controlla l'esistenza di una campaign detail con caratteristiche uguali alla richiesta
1284--- I campi : codcampaign,codmarket,coddealer,coddealerlocation,codbrand,codcampaigndetailext
1285--- costituiscono chiave univoca.
1286--- se non esiste la si crea
1287--- Return Value : CodCampaignDetail
1288function addCampaignDetail
1289 (p_codcampaign in ldd_campaign.codcampaign%type,
1290 p_codmarket in ldd_campaign_details.codmarket%type,
1291 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
1292 p_codbrand in ldd_campaign_details.codbrand%type default null,
1293 p_codcampaigndetailext in ldd_campaign_details.codcampaigndetailext%type default null,
1294 p_coddealer in ldd_campaign_details.coddealer%type default null,
1295 p_coddealerlocation in ldd_campaign_details.coddealerlocation%type default null,
1296 p_creationusergid in ldd_campaign_details.creationusergid%type default null,
1297 p_priority in ldd_campaign_details.priority%type default null)
1298 return ldd_campaign_details.codcampaigndetail%TYPE is
1299 v_codcampaigndetail ldd_campaign_details.codcampaigndetail%TYPE;
1300 v_codLocation ldd_campaign_details.coddealerlocation%type;
1301 v_codmaindealer ldd_campaign_details.codmaindealer%type;
1302--- gestione errore
1303 v_logHandle LOGMASTER.SEQUENCEID%TYPE;
1304 v_errCode NUMBER;
1305 v_errMsg LOGC.DETAILS%TYPE;
1306BEGIN
1307
1308--- p_coddealerlocation --> NULL , allora si prende la location piu' bassa per il dealer ( solitamente 000 )
1309--- spettacolo!
1310---0037577 anche nel caso di codlocation valorizzata ma non presente su Link o cancellata
1311 v_codLocation := NULL;
1312 BEGIN
1313 SELECT DLL.codlocation
1314 INTO v_codLocation
1315 FROM dealerlocation DLL
1316 WHERE DLL.codmarket = p_codMarket
1317 AND DLL.coddealer = p_codDealer
1318 AND DLL.codlocation = p_coddealerlocation
1319 AND DLL.deleted = 0;
1320 EXCEPTION
1321 WHEN NO_DATA_FOUND THEN
1322 SELECT MIN(DLL.codlocation)
1323 INTO v_codLocation
1324 FROM dealerlocation DLL
1325 WHERE DLL.codmarket = p_codMarket
1326 AND DLL.coddealer = p_codDealer
1327 AND DLL.deleted = 0;
1328 END;
1329
1330--- T7223 - I campi costituiti da :
1331--- codcampaign,codmarket,coddealer,coddealerlocation,codbrand,codcampaigndetailext
1332--- costituiscono chiave univoca.
1333 v_codmaindealer := NULL;
1334 IF p_codDealer is NOT NULL THEN
1335
1336 SELECT LCD.CODCAMPAIGNDETAIL, LCD.CODMAINDEALER
1337 INTO v_codcampaigndetail, v_codmaindealer
1338 FROM LDD_CAMPAIGN_DETAILS LCD
1339 WHERE lcd.codcampaign = p_codCampaign
1340 AND lcd.codmarket = p_codMarket
1341 AND lcd.coddealer = p_codDealer
1342 AND lcd.coddealerlocation = v_codLocation
1343 AND NVL(lcd.codbrand,'@@@') = nvl(p_codbrand,'@@@')
1344 AND NVL(lcd.codcampaigndetailext,'@@@') = NVL(p_codcampaigndetailext,'@@@');
1345
1346--B35028 : se main cambiato, aggiorno tutte le vecchie campagne dello stesso coddealer
1347 IF v_codmaindealer != p_codmaindealer THEN
1348 UPDATE ldd_campaign_details lcd
1349 SET lcd.codmaindealer = p_codmaindealer
1350 WHERE lcd.codmarket = p_codMarket
1351 AND lcd.coddealer = p_codDealer
1352 AND lcd.codmaindealer = v_codmaindealer;
1353 END IF;
1354
1355 ELSE
1356 SELECT LCD.CODCAMPAIGNDETAIL
1357 INTO v_codcampaigndetail
1358 FROM LDD_CAMPAIGN_DETAILS LCD
1359 WHERE lcd.codcampaign = p_codCampaign
1360 AND lcd.codmarket = p_codMarket
1361 AND NVL(lcd.coddealer,'@@@') = NVL(p_codDealer,'@@@')
1362 AND lcd.coddealerlocation = v_codLocation
1363 AND NVL(lcd.codbrand,'@@@') = nvl(p_codbrand,'@@@')
1364 AND NVL(lcd.codcampaigndetailext,'@@@') = NVL(p_codcampaigndetailext,'@@@');
1365
1366 END IF;
1367
1368 return v_codcampaigndetail;
1369
1370EXCEPTION
1371 WHEN NO_DATA_FOUND THEN
1372-- Creo la ldd_campaign_details
1373 BEGIN
1374 INSERT INTO ldd_campaign_details ( CODCAMPAIGNDETAIL ,
1375 CODCAMPAIGN ,
1376 CODMARKET ,
1377 CODMAINDEALER ,
1378 CODDEALER ,
1379 CODDEALERLOCATION ,
1380 CREATIONUSERGID ,
1381 CODBRAND ,
1382 CODCAMPAIGNDETAILEXT ,
1383 PRIORITY )
1384 VALUES ( Q_CODCAMPAIGNDETAIL.NEXTVAL,
1385 p_codcampaign,
1386 p_codmarket,
1387 p_codmaindealer,
1388 p_coddealer,
1389 v_codlocation,
1390 p_creationusergid,
1391 p_codbrand,
1392 p_codcampaigndetailext,
1393 p_priority )
1394 RETURNING CODCAMPAIGNDETAIL INTO v_codcampaigndetail;
1395
1396 return v_codcampaigndetail;
1397
1398 EXCEPTION
1399 WHEN DUP_VAL_ON_INDEX THEN
1400--- B37589 - a cause del grande numero di leads arrivate nello stesso momento e' possibile
1401--- che nel tempo trascorso tra la ricerca e l'inserimento un'altra sessione
1402--- abbia effettuato la creazione del record.
1403 SELECT LCD.CODCAMPAIGNDETAIL
1404 INTO v_codcampaigndetail
1405 FROM LDD_CAMPAIGN_DETAILS LCD
1406 WHERE lcd.codcampaign = p_codCampaign
1407 AND lcd.codmarket = p_codMarket
1408 AND NVL(lcd.coddealer,'@@@') = NVL(p_codDealer,'@@@')
1409 AND lcd.coddealerlocation = v_codLocation
1410 AND NVL(lcd.codbrand,'@@@') = nvl(p_codbrand,'@@@')
1411 AND NVL(lcd.codcampaigndetailext,'@@@') = NVL(p_codcampaigndetailext,'@@@');
1412
1413 return v_codcampaigndetail;
1414
1415 END;
1416
1417 WHEN OTHERS THEN
1418 v_errCode := Sqlcode;
1419 v_errMsg := DBMS_UTILITY.FORMAT_ERROR_STACK;
1420 v_logHandle := pkLOG.Insertmaster(NULL,'pkLDD_CAMPAIGN.addCampaignDetail',NULL, '0' );
1421 pklog.writedetail(vloghandle=>v_logHandle,
1422 llevel=>PKLOG.WARNING,
1423 pprocedurename=>'pkLDD_CAMPAIGN.addCampaignDetail',
1424 description=>'Error in read/insert campaign detail ',
1425 details=>v_errMsg );
1426 PKLOG.updateMaster(v_logHandle, v_errCode );
1427
1428 RETURN NULL;
1429
1430END addCampaignDetail;
1431-- Public updateCampaignDetailRedemption
1432procedure updateCampaignDetailRedemption
1433 (p_codcampaigndetailext in ldd_campaign_details.codcampaigndetailext%type ,
1434 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
1435 p_codmarket in ldd_campaign_details.codmarket%type,
1436 p_redemption_tot in ldd_campaign_details.dbcare_redemption_tot%type default null,
1437 p_redemption_pers in ldd_campaign_details.dbcare_redemption_pers%type default null,
1438 p_redemption_lostcontact in ldd_campaign_details.dbcare_redemption_lostcontact%type default null,
1439 p_redemption_totalcontact in ldd_campaign_details.dbcare_total_contact_num%type default null,
1440 p_redemption_datawin in ldd_campaign_details.dbcare_data_win%type default null) as
1441BEGIN
1442
1443 UPDATE ldd_campaign_details lcd
1444 SET dbcare_redemption_tot = p_redemption_tot,
1445 dbcare_redemption_pers = p_redemption_pers,
1446 dbcare_redemption_lostcontact = p_redemption_lostcontact,
1447 dbcare_total_contact_num = p_redemption_totalcontact,
1448 dbcare_data_win = p_redemption_datawin,
1449 dbcare_redemption_lastupdate = sysdate
1450 WHERE codmaindealer = p_codmaindealer
1451 and codcampaigndetailext = p_codcampaigndetailext
1452 and codmarket = p_codmarket
1453 and exists (select 1 from ldd_campaign lc
1454 where lc.codcampaign=lcd.codcampaign and lc.codcampaignsource='DC') ;
1455
1456end updateCampaignDetailRedemption;
1457
1458-- Public getContactsbyCampaigndetail
1459function getContactsbyCampaigndetail
1460 (p_codcampaign in ldd_campaign_details.codcampaign%type,
1461 p_codmaindealer in ldd_campaign_details.codmaindealer%type,
1462 p_codmarket in ldd_campaign_details.codmarket%type)
1463 return elinkTypes.ref_collection is
1464 myCursor elinkTypes.ref_collection;
1465begin
1466 open myCursor for
1467 SELECT c.codcontacttype,
1468 c.codcontact,-- t7029
1469 CASE WHEN c.codcontacttype = 'P' THEN
1470 p.firstname
1471 ELSE
1472 co.companyname
1473 END AS contactname,
1474 CASE WHEN c.codcontacttype = 'P' THEN
1475 p.lastname
1476 ELSE
1477 NULL
1478 END AS lastname,
1479 CASE WHEN c.codcontacttype = 'P' THEN
1480 p.fiscalcode
1481 ELSE
1482 NULL
1483 END AS fiscaldcode,
1484 CASE WHEN c.codcontacttype = 'C' THEN
1485 co.piva
1486 ELSE
1487 NULL
1488 END AS piva,
1489 CASE WHEN c.codcontacttype = 'P' THEN
1490 ( SELECT ( SELECT pkdict.tran(g.coddictionary,m.codlanguage) FROM DUAL )
1491 FROM gender g
1492 WHERE g.codgender = p.codgender )
1493 ELSE
1494 NULL
1495 END AS gender,
1496 CASE WHEN c.codcontacttype = 'P' THEN
1497 p.birthdate
1498 ELSE
1499 NULL
1500 END AS birthdate,
1501 cy.descr_city,
1502 ( SELECT ( SELECT pkdict.tran(dp.coddictionary,m.codlanguage) FROM DUAL )
1503 FROM department dp
1504 WHERE c.coddepartment = dp.coddepartment
1505 AND c.codregion = dp.codregion
1506 AND c.codnation = dp.codnation) as department,
1507 c.cap,
1508 c.address1,
1509 c.addressnumber,
1510 c.email_address,
1511 CASE WHEN c.telephone is null THEN
1512 c.home_phone
1513 ELSE
1514 c.telephone
1515 END AS home_phone,
1516 c.fax,
1517 c.home_phone as home_phone_org,
1518 c.mobile_phone,
1519 c.job_phone,
1520 c.telephone
1521 ----- t7029
1522 ,(select BR.BRANDNAME
1523 from BRAND BR
1524 where BR.CODBRAND = lcd.CODBRAND) as BRAND,
1525 pkcommercialvehicle.getcompletename(lcd.codmarket,
1526 lddv.codbrand,
1527 lddv.codmodel,
1528 lddv.codversion,
1529 lddv.codserie,nvl(lddv.codspecialserie,'000')
1530 ,'') as model
1531 ,lddv.registrationdate as registration
1532 ,pkldd_campaign.getCustomField (p_codmarket,l.codlead,3) as title
1533 ,pkldd_campaign.getCustomField (p_codmarket,l.codlead,4) as canton
1534 ,pkldd_campaign.getCustomField (p_codmarket,l.codlead,5) as NUMBER_CAR
1535 ,lddv.lastservicedate
1536 ,lddv.owntype
1537 ,pkldd_campaign.getSalesmanName(p_codmarket,p_codmaindealer,c.codcontact,lcd.codbrand) as SALESMAN
1538 FROM ldd_lead l
1539 join ldd_campaign_details lcd on lcd.codcampaigndetail=l.codcampaigndetail
1540 JOIN contact c ON (l.codcontact = c.codcontact)
1541 JOIN market m on (m.codmarket = p_codmarket)
1542 LEFT JOIN person p ON (p.codperson = c.codcontact)
1543 LEFT JOIN company co ON (co.codcompany = c.codcontact)
1544 LEFT JOIN city cy ON (c.codcity = cy.codcity AND
1545 c.coddepartment = cy.coddepartment AND
1546 c.codregion = cy.codregion AND
1547 c.codnation = cy.codnation)
1548 ---T7029
1549 left join ldd_vehicle lddv on (lddv.codlead = l.codlead)
1550 WHERE lcd.codcampaign = p_codcampaign
1551 and lcd.codmaindealer = p_codmaindealer
1552 and lcd.codmarket = p_codmarket;
1553 return myCursor;
1554end getContactsbyCampaigndetail;
1555--
1556-- restituisce l'elenco dei gruppi di source
1557function getCampaignGroups
1558 (p_codmarket market.codmarket%type
1559 ,p_codlanguage dictionarylanguage.codlanguage%type)
1560 return elinkTypes.ref_collection is
1561 myCursor elinkTypes.ref_collection;
1562BEGIN
1563 open myCursor for
1564 select cg.sourcegroup,
1565 pkdict.tran(cg.coddictionary,p_codlanguage) as DESCRIPTION
1566 FROM ldd_campaigngroups cg
1567 WHERE cg.sourcegroup IN ( SELECT cs.sourcegrouP
1568 FROM ldd_campaign_source_groups cs
1569 where cs.codmarket = p_codmarket )
1570 order by cg.sourcegroup;
1571 return myCursor;
1572end getCampaignGroups;
1573--
1574function getCustomField (p_codmarket market.codmarket%type
1575 ,p_idlead ldd_customfield.idlead%type
1576 ,p_idfield ldd_customfield.idfield%type)
1577 return ldd_customfield.fieldvalue%type is
1578 fieldvalue ldd_customfield.fieldvalue%type;
1579begin
1580 select lcf.fieldvalue into fieldvalue
1581 from ldd_customfield lcf
1582 join ldd_marketcustomdata lmk on lmk.idfield = lcf.idfield
1583 where lmk.market = p_codmarket
1584 and lcf.idlead = p_idlead
1585 and lcf.idfield = p_idfield;
1586
1587 return fieldvalue;
1588
1589exception
1590 when no_data_found then
1591 return NULL;
1592 when others then
1593 return NULL;
1594
1595end getCustomField;
1596
1597--********************************************
1598--- Funzione Batch
1599-- la funzione si occupa della cancellazione dei contatti associati ad una campagna
1600--- di lead relativa al noleggio contatti scaduta
1601--- al momento la funzione e' applicabile unicamente al mercato FRANCIA ( 3109 )
1602--- Codici di ritorno standard TIVOLI : 0=OK 1=Error 2=Warning
1603--********************************************
1604function actionDeleteLead
1605 ( p_codMarket IN MARKET.CODMARKET%TYPE DEFAULT '3109',
1606 p_offsetDelete IN NUMBER DEFAULT NULL )
1607 return number is
1608
1609 cursor C01 ( P1 IN DATE ) is
1610 SELECT LEA.CODLEAD,
1611 LEA.CODCONTACT,
1612 LCP.CODCAMPAIGN,
1613 CNT.CODMARKET,
1614 PRS.LASTNAME,
1615 PRS.FIRSTNAME,
1616 CPY.COMPANYNAME
1617 FROM LDD_LEAD LEA
1618 JOIN LDD_CAMPAIGN_DETAILS LCD
1619 ON ( LCD.CODCAMPAIGNDETAIL = LEA.CODCAMPAIGNDETAIL )
1620 JOIN LDD_CAMPAIGN LCP ON ( LCP.CODCAMPAIGN = LCD.CODCAMPAIGN )
1621 JOIN CONTACT CNT ON ( CNT.CODCONTACT = LEA.CODCONTACT )
1622 LEFT JOIN PERSON PRS ON ( PRS.CODPERSON = CNT.CODCONTACT )
1623 LEFT JOIN COMPANY CPY ON ( CPY.CODCOMPANY = CNT.CODCONTACT )
1624 WHERE LCP.codcampaignsource = 'PR'
1625 and LCP.codcampaigntype = 'CR'
1626 and CNT.createddate between trunc(LCP.startdate) and trunc(LCP.enddate)
1627 and LCP.enddate <= P1
1628 and LCD.CODMARKET = p_codMarket
1629--- Restrizioni per evitare cancellazioni avventate
1630--- - Contatto con stato diverso da "Q"
1631 AND CNT.CODCONTACTSTATUS != 'Q'
1632--- - non esistono commesse associate al contatto
1633 AND NOT EXISTS ( SELECT 1
1634 FROM SRV_REPAIRORDER SRO
1635 WHERE SRO.CODCONTACT = LEA.CODCONTACT )
1636 AND NOT EXISTS ( SELECT 1
1637 FROM SRV_REPAIRORDER SRO
1638 WHERE SRO.CODCONTACTVEHICLE IN
1639 ( SELECT SCV.CODCONTACTVEHICLE
1640 FROM SRV_CONTACTVEHICLE SCV
1641 WHERE SCV.CODCONTACT = LEA.CODCONTACT )
1642 )
1643--- - non esistono offerte stampate o ocf legate al contatto ( offerstate = 1 )
1644--- - non esistono ocf legate al contatto
1645 AND NOT EXISTS ( SELECT 1
1646 FROM OFFER OFF
1647 WHERE OFF.CODCONTACT = LEA.CODCONTACT
1648 AND ( OFF.OFFERSTATE = 1
1649 OR
1650 EXISTS ( SELECT 1
1651 FROM OCF
1652 WHERE OCF.CODOFFER = OFF.CODOFFER )
1653 )
1654 )
1655--- - non esistono certificati di garanzia legati al contatto
1656 AND NOT EXISTS ( SELECT 1
1657 FROM WRC_WARRANTYCERTIFICATE WRC
1658 WHERE WRC.CODCONTACT = LEA.CODCONTACT );
1659 C01REC C01%RowType;
1660
1661-- Local Variable Declarations
1662 v_logHandle LOGMASTER.SEQUENCEID%TYPE;
1663 v_procName LOGMASTER.LOADERNAME%TYPE := 'PKNOLEGGIOCONTATTI.actionDeleteLead';
1664 v_sqlcode NUMBER;
1665 v_errDesc VARCHAR2(1024);
1666 v_dateDelete DATE;
1667 v_delCnt NUMBER := 0;
1668 v_returnCode NUMBER;
1669
1670BEGIN
1671
1672 v_logHandle := PKLOG.Insertmaster(p_codMarket,v_procName,NULL, '0' );
1673--- Controllo parametri. La campagna deve essere chiusa da almeno 7 giorni
1674--- Non e' ammesso il parametro p_offsetDelete inferiore a 7 giorni
1675 IF p_offsetDelete IS NULL THEN
1676 v_dateDelete := TRUNC(SYSDATE-7);
1677 ELSE
1678 IF p_offsetDelete < 7 THEN
1679 v_dateDelete := TRUNC(SYSDATE-7);
1680 ELSE
1681 v_dateDelete := TRUNC(SYSDATE - p_offsetDelete);
1682 END IF;
1683 END IF;
1684
1685 Pklog.writeDetail(v_logHandle, Pklog.INFO, v_procName, 'Parameters : ',
1686 ' Codmarket : '||p_codMarket||' Date : '||to_char(v_dateDelete) );
1687
1688 OPEN C01 ( v_dateDelete );
1689 LOOP
1690 FETCH C01 INTO C01REC;
1691 EXIT WHEN C01%NotFound;
1692
1693 v_returnCode := pkContact.RemoveContactCascade( C01REc.codcontact );
1694--- Inserimento record di LOG nel caso in cui il contatto sia stato cancellato
1695--- Attenzione. La funzione pkContact.RemoveContactCascade effettua COMMIT
1696 IF v_returnCode >= 0 THEN
1697 INSERT INTO LOGNOLEGGIOCONTATTIREMOVE ( CODMARKET ,
1698 CODCONTACT ,
1699 DATEREMOVE ,
1700 LASTNAME ,
1701 FIRSTNAME ,
1702 COMPANYNAME ,
1703 CODCAMPAIGN )
1704 VALUES ( C01rec.CODMARKET ,
1705 C01rec.CODCONTACT ,
1706 SYSDATE ,
1707 C01rec.LASTNAME ,
1708 C01rec.FIRSTNAME ,
1709 C01rec.COMPANYNAME ,
1710 C01rec.CODCAMPAIGN );
1711 v_delCnt := v_delCnt + 1;
1712 END IF;
1713
1714 END LOOP;
1715 CLOSE C01;
1716
1717--- Pulizia tabella di LOG
1718--- I dati piu' vecchi di un mese saranno cancellati. Erano stati inseriti per
1719--- eventuali controlli dopo la cancellazione. Dopo un mese ormai sono vecchi
1720 DELETE FROM LOGNOLEGGIOCONTATTIREMOVE
1721 WHERE DATEREMOVE < ADD_MONTHS( SYSDATE , -1 );
1722 COMMIT;
1723
1724 Pklog.writeDetail(v_logHandle, Pklog.INFO, v_procName, 'Contact deleted : '||to_char(v_delCnt),NULL );
1725 PKLOG.UPDATEMASTER ( v_logHandle , 0 );
1726
1727 RETURN 0;
1728
1729EXCEPTION
1730 WHEN OTHERS THEN
1731 v_sqlCode := sqlcode;
1732 v_errDesc := DBMS_UTILITY.FORMAT_ERROR_STACK;
1733 pklog.writedetail(vloghandle=>v_logHandle, llevel=>PKLOG.SEVERE,
1734 pprocedurename=>v_procName,
1735 description=>'ERR : '||to_char(v_sqlCode),
1736 details=>v_ErrDesc );
1737 Pklog.writeDetail(v_logHandle, Pklog.INFO, v_procName, 'Contact deleted : '||to_char(v_delCnt),NULL );
1738 PKLOG.UPDATEMASTER ( v_logHandle , v_sqlCode );
1739 RETURN 1;
1740
1741end actionDeleteLead;
1742
1743function findCampaignTypesAndSources
1744 (p_codCampaignSource IN LDD_CAMPAIGN_TYPES_SOURCE.CODCAMPAIGNSOURCE%TYPE,
1745 p_codCampaignType IN LDD_CAMPAIGN_TYPES_SOURCE.CODCAMPAIGNTYPE%TYPE,
1746 p_application IN LINKAPPLTYPE.TYPE%TYPE )
1747 return elinkTypes.ref_collection is
1748 myCursor elinkTypes.ref_collection;
1749BEGIN
1750 x_sqlStmt := '
1751SELECT /* findCampaignTypesAndSources */
1752 LTS.CODCAMPAIGNSOURCE,
1753 ( SELECT LCS.DESCRIPTION
1754 FROM LDD_CAMPAIGN_SOURCES LCS
1755 WHERE LCS.CODCAMPAIGNSOURCE = LTS.CODCAMPAIGNSOURCE ) AS SOURCE_DESCRIPTION,
1756 LTS.CODCAMPAIGNTYPE,
1757 ( SELECT LCT.DESCRIPTION
1758 FROM LDD_CAMPAIGN_TYPES LCT
1759 WHERE LCT.CODCAMPAIGNTYPE = LTS.CODCAMPAIGNTYPE ) AS TYPE_DESCRIPTION,
1760 LTS.ISDELETED ,
1761 LTS.TOBECOUNTED
1762 FROM LDD_CAMPAIGN_TYPES_SOURCE LTS
1763 WHERE 1 = 1 ';
1764--- I parametri forniti in input sono tutti variabili. Le tabelle sono piccole, si potrebbe anche
1765--- fare tutto in full , ma noi siamo dei fighi!
1766 IF p_codCampaignSource IS NOT NULL THEN
1767 x_sqlStmt := x_sqlStmt || ' AND LTS.CODCAMPAIGNSOURCE = :p_codCampaignSource ';
1768 ELSE
1769 x_sqlStmt := x_sqlStmt || ' AND :p_codCampaignSource IS NULL ';
1770 END IF;
1771 IF p_codCampaignType IS NOT NULL THEN
1772 x_sqlStmt := x_sqlStmt || ' AND LTS.CODCAMPAIGNTYPE = :p_codCampaignType ';
1773 ELSE
1774 x_sqlStmt := x_sqlStmt || ' AND :p_codCampaignType IS NULL ';
1775 END IF;
1776 x_sqlStmt := x_sqlStmt || ' ORDER BY LTS.CODCAMPAIGNSOURCE,LTS.CODCAMPAIGNTYPE' ;
1777
1778 open myCursor for x_sqlStmt USING p_codCampaignSource,
1779 p_codCampaignType;
1780
1781 return myCursor;
1782
1783end findCampaignTypesAndSources;
1784
1785-- B39701
1786-- Determina l'eventuale presenza di utenti con nomi campagna specifici e se tali nomi sono ancora
1787-- attivi in data attuale
1788FUNCTION findInternalUsergidCampaign(p_usergid in LDD_LEAD_USERGID_CAMPAIGN.USERGID%type)
1789 RETURN elinkTypes.ref_collection is
1790 myCursor elinkTypes.ref_collection;
1791BEGIN
1792 open myCursor for
1793 SELECT LC.CODCAMPAIGN, LDUC.NAMECAMPAIGN
1794 FROM LDD_LEAD_USERGID_CAMPAIGN LDUC
1795 LEFT JOIN LDD_CAMPAIGN LC ON LDUC.NAMECAMPAIGN = LC.NAMECAMPAIGN
1796 WHERE LDUC.USERGID = p_usergid
1797 AND LDUC.DATESTART <= SYSDATE AND LDUC.DATEEND >= SYSDATE
1798 AND ROWNUM <= 1;
1799 return myCursor;
1800END findInternalUsergidCampaign;
1801
1802-- Resituisce l'elenco delle leads per contatto e objecttype (array).
1803function findAllLeadByTypes
1804 (p_codContact in ldd_lead.codcontact%type,
1805 p_codLanguage in dictionarylanguage.codlanguage%type,
1806 p_objtype in OBJTYPETABLE)
1807 return elinkTypes.ref_collection is
1808 myCursor elinkTypes.ref_collection;
1809 x_checkDate CONSTANT DATE := SYSDATE;
1810 v_checkMinutesPin NUMBER;
1811 v_objtypetable LISTOFVALUEC;
1812
1813BEGIN
1814 v_objtypetable := LISTOFVALUEC();
1815
1816 if p_objtype.count <> 0 THEN
1817 for i in p_objtype.FIRST .. p_objtype.LAST
1818 loop
1819 v_objtypetable.EXTEND;
1820 v_objtypetable(i) := p_objtype(i);
1821 end loop;
1822 end if;
1823
1824 select pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',(select c.codmarket from contact c where c.codcontact = p_codContact),1,0) into v_checkMinutesPin from dual;
1825 open myCursor for
1826 SELECT ll.codlead,
1827 ( SELECT LRN.REMINDER_NOTE
1828 FROM LDD_REMINDER_NOTES LRN
1829 WHERE LRN.CODLEAD = LL.CODLEAD ) as OFFER,
1830 lsrc.description as SOURCELEAD,
1831 ltp.description as TYPELEAD,
1832 lcmpdet.CODBRAND,
1833 -- T11796 phone lead brand Unknown
1834 CASE
1835 WHEN NVL(ll.objtype,'L') = 'P' THEN (SELECT mk.codbrand
1836 FROM ldd_lead_hub_masterkey lhm
1837 LEFT JOIN ldd_hub_masterkey mk
1838 ON lhm.codmasterkey =
1839 mk.codmasterkey
1840 where lhm.codlead =
1841 ll.codlead)
1842 ELSE (select BR.BRANDNAME
1843 from BRAND BR
1844 where BR.CODBRAND = lcmpdet.CODBRAND)
1845 END as BRAND,
1846 LDP.DESCRPRODUCT AS COMMERCIALMODEL,
1847 LDP.CODMODEL,
1848--- T13231
1849 LDP.CONFIGURATOR_ID,
1850 TO_CHAR(ll.createddate,'YYYY-MM-DD HH24:MI:SS') as DATALEAD,
1851 ll.codleadstatus,
1852 ( SELECT ( SELECT pkdict.tran(ls.coddictionary, p_codLanguage) FROM DUAL )
1853 FROM ldd_status ls
1854 WHERE ls.codleadstatus = ll.codleadstatus ) as DESCRIPTIONSTATUS,
1855 decode(ll.codleadstatus, 'A', 0, 1) as EDITABLE,
1856--- B30267 - Semplificazione. Severitylevel ottenuto in base ai livelli calcolati
1857--- al momento della creazione della lead/intervista
1858 CASE WHEN LL.FINAL_SEVERITYLEVELFIELD IS NOT NULL THEN
1859 LL.FINAL_SEVERITYLEVELFIELD
1860 ELSE
1861 NVL( ( select MAX(LSR.SEVERITYLEVELFIELD) KEEP(DENSE_RANK FIRST ORDER BY LSR.SLALIMIT)
1862 FROM LDD_LEADS_SLARANGES LSR
1863 WHERE LSR.CODLEAD = LL.CODLEAD
1864 --AND LSR.CODSLAEVENT = 'L'
1865 AND LSR.CODSLAEVENT = DECODE(NVL(LL.COMMUNICATIONCHANNEL,'T'),'T','L','X') -- T15301
1866 AND LSR.SLALIMIT > x_checkDate ) , 'R' )
1867 END AS PRIORITY,
1868 ll.sentmail AS SENTMAIL,
1869 ll.codcampaigndetail AS CODCAMPAIGNDETAIL,
1870--- T7839 - I numeri di telefono saranno prelevati da LDD_LEAD e LDD_TRACKBACK ( pins )
1871--- T7990 - In caso di lead da assegnare va sempre mostrato il numero oscurato da asterischi
1872--- T14290 - Refactoring del calcolo del Phone Number. Ora il calcolo ? centralizzato
1873 pkLDD_LEADS.getProperPhoneNumber(
1874 lcmpdet.codmarket,
1875 LL.CODLEADSTATUS,
1876 LL.Insertiondate,
1877 LL.MOBILEPHONE,
1878 LL.PHONENUMBER,
1879 TBK.CODLEAD,
1880 TBK.DATE_SENT,
1881 TBK.PIN_MOBILE,
1882 TBK.PIN_PHONENUMBER,
1883 TBK.TB_RESULT,
1884 TBK.TB_STATUS,
1885 v_checkMinutesPin,
1886 ll.communicationchannel,
1887 CT.EMAIL_ADDRESS) AS PHONENUMBER,
1888--- T7839 - Un flag indica se il numero di telefono riguarda un PIN
1889-- T14290 - Refactoring del calcolo relativo al Pin. Ora il calcolo ? centralizzato
1890 pkLDD_LEADS.checkIsPin(
1891 lcmpdet.codmarket,
1892 LL.CODLEADSTATUS,
1893 LL.INSERTIONDATE,
1894 nvl(TBK.CODLEAD,ll.codlead),
1895 TBK.DATE_SENT,
1896 TBK.TB_RESULT,
1897 TBK.TB_STATUS,
1898 v_checkMinutesPin,
1899 ll.communicationchannel) AS ISPIN,
1900--- T8261 - aggiunta campo untreated in output
1901 ll.untreated,
1902 ll.callcenter_callback,
1903--- T8979 - aggiunta campo nome campagna
1904 lcmp.namecampaign,
1905--- T9690- aggiunta campo objtype
1906 ll.objtype,
1907 ll.codleadext,
1908 pkldd_dms.getDMSLeadStatus (ll.codlead, lcmpdet.codmarket) AS DMSSTATUS, -- T12367
1909 (select pkdict.tran(
1910 (select max(act.coddictionary) keep(dense_rank first order by ldh.creationdate desc, cac.codactioncontact desc)
1911 from ldd_leadhistory ldh
1912 join crm_actioncontact cac
1913 on cac.codactioncontact = ldh.codactioncontact
1914 join crm_action act
1915 on act.codaction = cac.codaction
1916 where ldh.codlead = ll.codlead
1917 and ldh.codleadstatus = 'IG'
1918 ),
1919 p_codLanguage) from dual) AS MANAGEDACTION,
1920--- T13748 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
1921 BUA.BUSINESS_AREA AS MBA,
1922 BUA.BUSINESS_AREA_DESCRIPTION AS MBA_DESCR,
1923 DL.ADDRESS||' - '||DL.TOWN_NAME AS SITE -- (la concatenazione dei campi ADDRESS e TOWN_NAME )
1924--- T13748 -END-
1925 FROM ldd_lead ll
1926 JOIN ldd_campaign_details lcmpdet
1927 on lcmpdet.codcampaigndetail = ll.codcampaigndetail
1928 JOIN ldd_campaign lcmp
1929 on lcmp.codcampaign = lcmpdet.codcampaign
1930 JOIN ldd_campaign_sources lsrc
1931 on lsrc.codcampaignsource = lcmp.codcampaignsource
1932 JOIN ldd_campaign_types ltp
1933 on ltp.codcampaigntype = lcmp.codcampaigntype
1934 LEFT JOIN LDD_TRACKBACK TBK ON ( TBK.CODLEAD = LL.CODLEAD )
1935 LEFT JOIN LDD_PRODUCT LDP ON ( LDP.CODLEAD = LL.CODLEAD )
1936
1937 LEFT JOIN DEALER D ON ( D.CODMARKET = lcmpdet.CODMARKET and D.CODDEALER = lcmpdet.CODDEALER )
1938 LEFT JOIN DEALERLOCATION DL on ( DL.CODMARKET = D.CODMARKET and DL.CODDEALER = D.CODDEALER and DL.CODLOCATION = lcmpdet.CODDEALERLOCATION )
1939
1940--- T13748 -STR- Estraggo le info di BUSINESS_AREA ricavando la MASTERKEY associata alla LEAD
1941 left join (select ba.business_area as BUSINESS_AREA,
1942 ba.description as BUSINESS_AREA_DESCRIPTION,
1943 lh.codlead as BUSINESS_AREA_CODLEAD
1944 from LDD_LEAD_HUB_MASTERKEY lh
1945 join ldd_hub_masterkey h on lh.codmasterkey = h.codmasterkey
1946 join LDD_MK_BUSINESS_AREA ba on ba.business_area = h.business_area) BUA
1947 on BUA.BUSINESS_AREA_CODLEAD = ll.CODLEAD
1948--- T13748 -END-
1949
1950 LEFT join CONTACT CT on (CT.CODCONTACT = ll.CODCONTACT)
1951
1952 WHERE ll.codcontact = p_codContact
1953
1954 AND ll.objtype IN (SELECT *
1955 FROM TABLE (CAST (v_objtypetable as LISTOFVALUEC))
1956 )
1957 --and ll.objtype = NVL(p_objtype,'L')
1958 ORDER BY ll.codlead DESC;
1959
1960 return myCursor;
1961
1962end findAllLeadByTypes;
1963
1964FUNCTION getLeadInfoBySurveyAnswer(p_codsurveyanswer IN surveyanswer.codsurveyanswer%TYPE)
1965 RETURN elinkTypes.ref_collection IS
1966
1967 myCursor elinkTypes.ref_collection;
1968
1969BEGIN
1970 OPEN myCursor for
1971 SELECT lcd.codmarket,
1972 sa.loidleadcrm AS CODLEAD,
1973 ll.objtype AS CODLEADTYPE,
1974 lcd.codcampaigndetail AS CODCAMPAIGNDETAIL,
1975 0 AS MAILTOIG, -- Per ora ce ne freghiamo, per Olanda non ci sono Survey.
1976 lct.description AS TYPELEAD,
1977 CASE
1978 WHEN NVL(ll.objtype, 'L') = 'P' THEN
1979 (SELECT mk.codbrand
1980 FROM ldd_lead_hub_masterkey lhm
1981 LEFT JOIN ldd_hub_masterkey mk
1982 ON lhm.codmasterkey = mk.codmasterkey
1983 where lhm.codlead = ll.codlead)
1984 ELSE
1985 (select BR.BRANDNAME
1986 from BRAND BR
1987 where BR.CODBRAND = lcd.CODBRAND)
1988 END as BRAND,
1989 lp.descrproduct AS CODCOMMERCIALMODEL,
1990 ll.codcontact AS CODCONTACT,
1991 pkLDD_LEADS.checkIsPin(lcd.codmarket,
1992 LL.CODLEADSTATUS,
1993 LL.INSERTIONDATE,
1994 nvl(TBK.CODLEAD, LL.CODLEAD),
1995 TBK.DATE_SENT,
1996 TBK.TB_RESULT,
1997 TBK.TB_STATUS,
1998 pkmarketparams.findValueByPrimaryKey('CRM_PIN_TIMING',
1999 lcd.codmarket,
2000 1,
2001 0),
2002 LL.Communicationchannel) AS ISPIN,
2003 ll.codleadstatus,
2004 ll.objtype,
2005 decode(ll.codleadstatus, 'A', 0, 1) as EDITABLE
2006 FROM surveyanswer sa
2007 JOIN ldd_lead ll
2008 ON ll.codlead = sa.loidleadcrm
2009 JOIN ldd_campaign_details lcd
2010 ON lcd.codcampaigndetail = ll.codcampaigndetail
2011 JOIN ldd_campaign lc
2012 ON lc.codcampaign = lcd.codcampaign
2013 JOIN ldd_campaign_types lct
2014 ON lct.codcampaigntype = lc.codcampaigntype
2015 LEFT JOIN ldd_product lp
2016 ON lp.codlead = ll.codlead
2017 LEFT JOIN LDD_TRACKBACK TBK
2018 ON (TBK.CODLEAD = Ll.CODLEAD)
2019 WHERE sa.codsurveyanswer = p_codsurveyanswer;
2020
2021 RETURN myCursor;
2022
2023EXCEPTION
2024 WHEN OTHERS THEN
2025 RETURN NULL;
2026END getLeadInfoBySurveyAnswer;
2027
2028end pkldd_campaign;
2029/