Untitled

  SELECT CC.PCOMPANYID, VT.VISITID,
             CL.CLIENTID,
             CL.FISCALNAME,
             CC.CLIENTCENTERID,
             DECODE(CB.ADDRESSID, NULL, CC.CENTERNAME, CB.CENTERNAME),
             CC.FISCALNUMBER,
             CC.STATUSID
                 CLIENTSTATUSID,
             CS.DESCRIPTION
                 CLIENTSTATUSDESC,
             SG.SEGMENTID,
             SG.DESCRIPTION
                 SEGMENTDESC,
             CC.INTERIMNUMBER,
             CC.TEMPSNUMBER,
             CP.SURNAME2
                 INTERLOCUTORSURNAME2,
             CP.SURNAME1
                 INTERLOCUTORSURNAME1,
             CP.FIRSTNAME
                 INTERLOCUTORFIRSTNAME,
             CPR.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 = CPR.USERID))
                 PFISCALDOCNUMBER,
             CPR.USERID,
             (SELECT ALLNAME
                FROM EURECA.MTEMPLOYEES
               WHERE EMPLOYEEID = (SELECT EMPLOYEEID
                                     FROM EURECA.MTUSERS
                                    WHERE USERID = CPR.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'),
             SEMANA_FECHA (VT.VISITDATE),
             VT.EMAIL,
             MKT.DESCRIPTION,
             MKT2.DESCRIPTION,
			 CC.JOBBLOCKEDID
        FROM EURECA.MTVISITS VT
        	INNER JOIN EURECA.MTCLIENTSCENTERS CC
        		ON cc.CLIENTID = vt.CLIENTID AND cc.CLIENTCENTERID = vt.CLIENTCENTERID 
             INNER JOIN EURECA.STCONTACTPERSONSRELS CPR
				ON ((cpr.TYPEID = 3 AND cpr.CLIENTID = vt.CLIENTID AND cpr.CLIENTCENTERID = vt.CLIENTCENTERID AND cpr.CONTACTPERSONID = vt.CONTACTPERSONID AND cpr.ACTIVECHK = 1 AND cpr.DELETEDID = 1) OR 
					(cpr.TYPEID = 2 AND cpr.CLIENTID = vt.CLIENTID AND cpr.CONTACTPERSONID = vt.CONTACTPERSONID AND cpr.ACTIVECHK = 1 AND cpr.DELETEDID = 1))
			INNER JOIN EURECA.MTCONTACTPERSONS CP
				ON cp.CONTACTPERSONID = CPR.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 = CPR.POSITIONINTERLOCFBIID
                    AND BR.COMPANYID = PO.PCOMPANYID
             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
             INNER 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.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 =  CPR.MAILINGTYPEID
       WHERE VT.DELETEDID = '1' AND AT.DELETEDID = '1' AND CPR.ACTIVECHK = 1 --AND PO.DELETEDID = '1'
	   AND (PO.PRODUCTID = 	(CASE
									WHEN    BR.BRANDID = 'PRO'
									THEN
										'0007'
									ELSE
										'0009'
								END) OR PO.PRODUCTID IS NULL)
AND VT.PUSERID = '440510022'
    AND VISITDATE >= to_date('2023-06-5 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND VISITDATE <= to_date('2023-06-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND VT.ACTIVITYID = 1
ORDER BY VISITDATE DESC;