Untitled
unknown
plain_text
2 years ago
9.0 kB
6
Indexable
CREATE OR REPLACE FORCE EDITIONABLE VIEW "EURECA"."VWFBIACTIVITYCPREPORT" ("VISITID", "CLIENTID", "FISCALNAME", "CLIENTCENTERID", "CENTERNAME", "FISCALNUMBER", "CLIENTSTATUSID", "CLIENTSTATUSDESC", "SEGMENTID", "SEGMENTDESC", "INTERIMNUMBER", "TEMPSNUMBER", "INTERLOCUTORSURNAME2", "INTERLOCUTORSURNAME1", "INTERLOCUTORFIRSTNAME", "MAILINGTYPEID", "MAILINGTYPEDESC", "POSITIONID", "POSITIONDESC", "FUNCTIONID", "FUNCTIONDESC", "DETAILFUNCTIONS", "PFISCALDOCNUMBER", "PUSERID", "PUSERNAME", "USERID", "USERNAME", "USRACTIVECHK", "VISITEMPLOYEEID", "VISITEMPLOYEENAME", "EMPINACTIVECHK", "PRODUCTID", "COMPANYID", "COMPANYNAME", "LEVEL1ID", "LEVEL1NAME", "LEVEL2ID", "LEVEL2NAME", "BRANCHID", "BRANCHNAME", "VISITDATE", "ACTIVITYID", "ACTIVITY", "ACTIVITYTYPEID", "ACTIVITYTYPE", "ACTIVITYSUBTYPE", "STATUSID", "STATUS", "SUBJECT", "COMMENTS", "YEAR", "WEEK", "EMAIL", "MKT1", "MKT2", "JOBBLOCKEDID") AS
SELECT VT.VISITID,
CL.CLIENTID,
CL.FISCALNAME,
CC.CLIENTCENTERID,
DECODE (CB.ADDRESSID, NULL, CC.CENTERNAME, CB.CENTERNAME)
CENTERNAME,
CC.FISCALNUMBER,
CC.STATUSID
CLIENTSTATUSID,
CS.DESCRIPTION
CLIENTSTATUSDESC,
SG.SEGMENTID,
SG.DESCRIPTION
SEGMENTDESC,
CC.INTERIMNUMBER,
CC.TEMPSNUMBER,
NVL (CP.SURNAME2, ' ')
INTERLOCUTORSURNAME2,
CP.SURNAME1
INTERLOCUTORSURNAME1,
CP.FIRSTNAME
INTERLOCUTORFIRSTNAME,
CI.MAILINGTYPEID,
MT.DESCRIPTION
MAILINGTYPEDESC,
PO.POSITIONID,
PO.DESCRIPTION
POSITIONDESC,
FU.FUNCTIONID,
FU.DESCRIPTION
FUNCTIONDESC,
CP.DETAILFUNCTIONS,
(SELECT FISCALDOCNUMBER
FROM EURECA.MTEMPLOYEES
WHERE EMPLOYEEID = (SELECT EMPLOYEEID
FROM EURECA.MTUSERS
WHERE USERID = CI.USERID))
PFISCALDOCNUMBER,
CI.USERID,
(SELECT ALLNAME
FROM EURECA.MTEMPLOYEES
WHERE EMPLOYEEID = (SELECT EMPLOYEEID
FROM EURECA.MTUSERS
WHERE USERID = CI.USERID))
PUSERNAME_INTERLOC,
US.USERID,
(SELECT ALLNAME
FROM EURECA.MTEMPLOYEES
WHERE EMPLOYEEID = (SELECT EMPLOYEEID
FROM EURECA.MTUSERS
WHERE USERID = CC.PUSERID))
USERNAME,
US.ACTIVECHK
USRACTIVECHK,
VT.VISITEMPLOYEEID,
EM.ALLNAME
VISITEMPLOYEENAME,
EM.INACTIVECHK
EMPINACTIVECHK,
AT.PRODUCTID,
CO.COMPANYID,
CO.FISCALNAME
COMPANYNAME,
L1.LEVEL1ID,
L1.NAME
LEVEL1NAME,
L2.LEVEL2ID,
L2.NAME
LEVEL2NAME,
BR.BRANCHID,
BR.NAME
BRANCHNAME,
VT.VISITDATE,
AC.ACTIVITYID
ACTIVITYID,
AC.DESCRIPTION
ACTIVITY,
AT.ACTIVITYTYPEID
ACTIVITYTYPEID,
AT.DESCRIPTION
ACTIVITYTYPE,
SU.DESCRIPTION
ACTIVITYSUBTYPE,
VS.STATUSID
STATUSID,
VS.DESCRIPTION
STATUS,
VT.SUBJECT,
VT.COMMENTS,
TO_CHAR (VT.VISITDATE, 'YYYY')
YEAR,
SEMANA_FECHA (VT.VISITDATE)
WEAK,
VT.EMAIL,
MKT.DESCRIPTION,
MKT2.DESCRIPTION,
CC.JOBBLOCKEDID
FROM EURECA.MTVISITS VT
INNER JOIN EURECA.STCONTACTPERSONSRELS CI
ON ( CI.TYPEID = 3
AND VT.CLIENTID = CI.CLIENTID
AND VT.CLIENTCENTERID = CI.CLIENTCENTERID
AND VT.CONTACTPERSONID = CI.CONTACTPERSONID AND CI.DELETEDID=1 AND CI.ACTIVECHK=1)
OR ( CI.TYPEID = 2
AND VT.CLIENTID = CI.CLIENTID
AND VT.CONTACTPERSONID = CI.CONTACTPERSONID AND CI.DELETEDID=1 AND CI.ACTIVECHK=1)
INNER JOIN EURECA.MTCONTACTPERSONS CP
ON CI.CONTACTPERSONID = CP.CONTACTPERSONID
LEFT OUTER JOIN EURECA.GTCLIENTINTERLOCUTORFUNCTIONS FU
ON FU.FUNCTIONID = CP.FUNCTIONID
INNER JOIN EURECA.MTUSERS US ON VT.PUSERID = US.USERID
INNER JOIN EURECA.MTEMPLOYEES EM
ON EM.EMPLOYEEID = VT.VISITEMPLOYEEID
LEFT OUTER JOIN EURECA.MTBRANCHES BR
ON US.BRANCHID = BR.BRANCHID
AND ( (BR.COMPANYID = 'RCO' AND BR.level1id = '31')
OR (BR.COMPANYID = 'EXP'))
AND BR.ACTIVECHK = 1
LEFT OUTER JOIN EURECA.GTPOSITIONINTERLOCFBI PO
ON PO.POSITIONID = CI.POSITIONINTERLOCFBIID
AND BR.COMPANYID = (CASE WHEN BR.BRANDID = 'PRO' THEN '0007' ELSE '0009' END)
INNER JOIN EURECA.GTACTIVITYTYPESFBI AT
ON VT.VISITTYPEID = AT.ACTIVITYTYPEID
AND AT.PCOMPANYID = CC.PCOMPANYID
AND AT.PRODUCTID = (CASE WHEN BR.BRANDID = 'PRO' THEN '0007' ELSE '0009' END)
AND AT.ACTIVITYID =
(CASE
WHEN VT.ACTIVITYID IS NULL
THEN
(SELECT ACTIVITYID
FROM ( SELECT ACTIVITYID, ROWNUM RNUM
FROM EURECA.GTACTIVITYTYPESFBI
WHERE VT.VISITTYPEID =
ACTIVITYTYPEID
AND PCOMPANYID = CC.PCOMPANYID
AND PRODUCTID = (CASE WHEN BR.BRANDID = 'PRO' THEN '0007' ELSE '0009' END)
ORDER BY ACTIVITYID)
WHERE RNUM = 1)
ELSE
VT.ACTIVITYID
END)
LEFT OUTER JOIN EURECA.GTVISITSTATUS VS
ON VT.STATUSID = VS.STATUSID
LEFT JOIN EURECA.GTACTIVITIESFBI AC
ON AT.ACTIVITYID = AC.ACTIVITYID
AND AT.PCOMPANYID = AC.PCOMPANYID
AND AT.PRODUCTID = AC.PRODUCTID
LEFT OUTER JOIN GTACTIVITYSUBTYPESFBI SU
ON VT.VISITSUBTYPEID = SU.ACTIVITYSUBTYPEID
AND AT.ACTIVITYTYPEID = SU.ACTIVITYTYPEID
AND AC.ACTIVITYID = SU.ACTIVITYID
AND SU.PCOMPANYID = AC.PCOMPANYID
AND SU.PRODUCTID = AC.PRODUCTID
INNER JOIN EURECA.MTCLIENTS CL ON VT.CLIENTID = CL.CLIENTID
INNER JOIN EURECA.MTCLIENTSCENTERS CC
ON VT.CLIENTID = CC.CLIENTID
AND VT.CLIENTCENTERID = CC.CLIENTCENTERID
INNER JOIN EURECA.GTCLIENTSEGMENTS SG
ON CC.SEGMENTID = SG.SEGMENTID
LEFT JOIN EURECA.MTCLIENTBRANCHES CB
ON CB.CLIENTBRANCHID = CC.CLIENTBRANCHID
AND CB.CLIENTID = CC.CLIENTID
INNER JOIN EURECA.GTCLIENTSTATUS CS ON CC.STATUSID = CS.STATUSID
INNER JOIN EURECA.MTCOMPANYS CO ON CC.PCOMPANYID = CO.COMPANYID
INNER JOIN EURECA.MTLEVEL1 L1 ON L1.LEVEL1ID = BR.LEVEL1ID
INNER JOIN EURECA.MTLEVEL2 L2 ON L2.LEVEL2ID = BR.LEVEL2ID
LEFT JOIN EURECA.MTMARKETINGACTIONS MKT
ON MKT.MARKETINGACTIONID = VT.MARKETINGACTIONID
LEFT JOIN EURECA.MTMARKETINGACTIONS MKT2
ON MKT2.MARKETINGACTIONID = VT.MARKETINGACTIONID2
LEFT JOIN GTMAILINGTYPES MT ON MT.MAILINGTYPEID = CI.MAILINGTYPEID
WHERE VT.DELETEDID = '1'
AND (AT.DELETEDID = '1' OR AT.DELETEDID IS NULL)
AND (PO.DELETEDID = '1' OR PO.DELETEDID IS NULL)
AND ( PO.PRODUCTID =
(CASE WHEN BR.BRANDID = 'PRO' THEN '0007' ELSE '0009' END)
OR PO.PRODUCTID IS NULL)
ORDER BY VT.VISITDATE DESC;Editor is loading...