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