Untitled

-- EURECA.VWSEARCHFBICLIENT source
CREATE OR REPLACE FORCE EDITIONABLE VIEW "EURECA"."VWSEARCHFBICLIENT" ("CLIENTID", "CLIENTCENTERID", "STATUSID", "STATUSNAME", "CENTERNAME", "CENTERNAMEREPLACE", "SHORTNAME", "SHORTNAMEREPLACE", "CLISHORTNAME", "CLISHORTNAMEREPLACE", "KBCLIE", "PCOMPANYID", "PUSERID", "CLIENTFISCALNAME", "CLIENTFISCALNAMEREPLACE", "CLIENTFISCALNUMBER", "CLIENTGROUPID", "CLIENTKEYACCOUNTCHK", "PBRANCHID", "BRANCHNAME", "BRANCHNAMEREPLACE", "BRANCHLEVEL1ID", "BRANCHCOMPANYID", "EMPLOYEEALLNAME", "EMPLOYEEALLNAMEREPLACE", "IUSER", "IDATE", "ICOMMENTS", "UUSER", "UDATE", "UCOMMENTS", "CLIENTGROUPNAME", "SEGMENTID", "SEGMENTDESC", "MARKETINGACTIONID", "MARKETINGACTIONDESC", "AGREEMENTID", "OWNERACTIVECHK", "INTERLOCUTORBRANCH", "TEMPDATE", "ADDRESS", "BLOCKEDID", "PUNITID", "BRANDID", "MONTHSCERTEXCEP", 
	 CONSTRAINT "PKVWSEARCHFBICLIENT" PRIMARY KEY ("CLIENTID", "CLIENTCENTERID") DISABLE) AS 
SELECT CEN.CLIENTID
               CLIENTID,
           CEN.CLIENTCENTERID
               CLIENTCENTERID,
           CEN.STATUSID
               STATUSID,
		   ST.DESCRIPTION
			   STATUSNAME,
           CEN.CENTERNAME
               CENTERNAME,
           (TRANSLATE (UPPER (CEN.CENTERNAME),
                       'ÁÉÍÓÚÀÈÌÒÙÂÊÎÔÛÄËÏÖÜ',
                       'AEIOUAEIOUAEIOUAEIOU'))
               AS CENTERNAMEREPLACE,
           CEN.SHORTNAME
               SHORTNAME,
           (TRANSLATE (UPPER (CEN.SHORTNAME),
                       'ÁÉÍÓÚÀÈÌÒÙÂÊÎÔÛÄËÏÖÜ',
                       'AEIOUAEIOUAEIOUAEIOU'))
               AS SHORTNAMEREPLACE,               
            CLI.SHORTNAME CLISHORTNAME,
           (TRANSLATE (UPPER (CLI.SHORTNAME),
                       'ÁÉÍÓÚÀÈÌÒÙÂÊÎÔÛÄËÏÖÜ',
                       'AEIOUAEIOUAEIOUAEIOU'))
               AS CLISHORTNAMEREPLACE,               
           CEN.KBCLIE
               KBCLIE,
           CEN.PCOMPANYID
               PCOMPANYID,
           CEN.PUSERID
               PUSERID,
           CLI.FISCALNAME
               CLIENTFISCALNAME,
           (TRANSLATE (UPPER (CLI.FISCALNAME),
                       'ÁÉÍÓÚÀÈÌÒÙÂÊÎÔÛÄËÏÖÜ',
                       'AEIOUAEIOUAEIOUAEIOU'))
               AS CLIENTFISCALNAMEREPLACE,
           CLI.FISCALNUMBER
               CLIENTFISCALNUMBER,
           CLI.GROUPID
               CLIENTGROUPID,
           CLI.KEYACCOUNTCHK
               CLIENTKEYACCOUNTCHK,
           CEN.PBRANCHID
               PBRANCHID,
           B.NAME
               BRANCHNAME,
           (TRANSLATE (UPPER (B.NAME),
                       'ÁÉÍÓÚÀÈÌÒÙÂÊÎÔÛÄËÏÖÜ',
                       'AEIOUAEIOUAEIOUAEIOU'))
               AS BRANCHNAMEREPLACE,
           B.LEVEL1ID
               BRANCHLEVEL1ID,
           B.COMPANYID
               BRANCHCOMPANYID,
           EMP.ALLNAME
               EMPLOYEEALLNAME,
           (TRANSLATE (UPPER (EMP.ALLNAME),
                       'ÁÉÍÓÚÀÈÌÒÙÂÊÎÔÛÄËÏÖÜ',
                       'AEIOUAEIOUAEIOUAEIOU'))
               AS EMPLOYEEALLNAMEREPLACE,
           CEN.IUSER
               IUSER,
           CEN.IDATE
               IDATE,
           CEN.ICOMMENTS
               ICOMMENTS,
           CEN.UUSER
               UUSER,
           CEN.UDATE
               UDATE,
           CEN.UCOMMENTS
               UCOMMENTS,
           GR.NAME,
           CEN.SEGMENTID,
		   (
			CASE CEN.PCOMPANYID
				WHEN 'FUN' THEN (SELECT TYP.DESCRIPTION FROM EURECA.GTFUNRANDTYPES TYP WHERE CEN.SEGMENTID = TYP.TYPEID AND TYP.DELETEDID = '1')
				ELSE (SELECT SEG.DESCRIPTION FROM EURECA.GTCLIENTSEGMENTS SEG WHERE CEN.SEGMENTID = SEG.SEGMENTID AND SEG.DELETEDID = '1')
			END
		   ) SEGMENTDESC,
		   CASE MK.MARKETINGACTIONCOUNT WHEN 0 THEN NULL WHEN 1 THEN MARKETINGACTIONID ELSE -1 END MARKETINGACTIONID,
		   CASE MK.MARKETINGACTIONCOUNT WHEN 0 THEN NULL WHEN 1 THEN MESSAGE ELSE 'Varios' END MARKETINGACTIONDESC,
		   CEN.AGREEMENTID 
				AGREEMENTID,
		   US.ACTIVECHK
				OWNERACTIVECHK,
		   EMP.FISCALDOCNUMBER
				INTERLOCUTORBRANCH,
            NVL((SELECT TEMPDATE FROM ( 
                            SELECT NVL(TMP.UDATE, TMP.IDATE) TEMPDATE FROM HTCLIENTSCENTERSTEMPS TMP
                            WHERE TMP.CLIENTID = CEN.CLIENTID AND TMP.CLIENTCENTERID = CEN.CLIENTCENTERID ORDER BY TMP.LINESEQ DESC)
                    WHERE ROWNUM = 1), CEN.IDATE) TEMPDATE,				
		   DECODE(CB.CLIENTBRANCHID, NULL, CEN.ADDRESSSTREET || ', ' || CEN.ADDRESSNUMBER, CB.ADDRESSSTREET || ', ' || CB.ADDRESSNUMBER) ADDRESS,
		   CLI.BLOCKEDID,
		   CEN.PUNITID PUNITID,
		   B.BRANDID,
		   ROUND(MONTHS_BETWEEN(LIS.EXPIRYDATE,SYSDATE),2) MONTHSCERTEXCEP
      FROM MTCLIENTSCENTERS  CEN
           INNER JOIN EURECA.MTCLIENTS CLI ON CLI.CLIENTID = CEN.CLIENTID
           INNER JOIN EURECA.MTBRANCHES B ON CEN.PBRANCHID = B.BRANCHID
           INNER JOIN EURECA.MTUSERS US
               ON CEN.PUSERID = US.USERID
           INNER JOIN EURECA.MTEMPLOYEES EMP
               ON EMP.EMPLOYEEID = US.EMPLOYEEID
           LEFT JOIN EURECA.MTCLIENTSGROUPS GR ON GR.GROUPID = CLI.GROUPID
		   INNER JOIN EURECA.GTCLIENTSTATUS ST ON CEN.STATUSID = ST.STATUSID
		   LEFT JOIN EURECA.MTCLIENTBRANCHES CB ON CEN.CLIENTID = CB.CLIENTID AND CEN.CLIENTBRANCHID = CB.CLIENTBRANCHID
           LEFT JOIN EURECA.STCLIENTSCENTERSLISMI LIS 
                ON CEN.PCOMPANYID ='FUN' AND CEN.CLIENTID = LIS.CLIENTID  AND CEN.CLIENTCENTERID  = LIS.CLIENTCENTERID 
                AND LIS.DELETEDID  = '1' AND LIS.CERTEXCEPCHK = 1
           LEFT JOIN (
				SELECT MKT.CLIENTID, MKT.CLIENTCENTERID, MAX(MARKETINGACTIONID) MARKETINGACTIONID, MAX(MKT.MESSAGE) MESSAGE, COUNT(*) MARKETINGACTIONCOUNT
				FROM EURECA.VWFBIVISITSACTIONS MKT 
				WHERE MKT.CLIENTSELECTIONTYPEID IN ('1', '3')
				AND MKT.MARKETINGACTIONID NOT IN (
						SELECT MARKETINGACTIONID FROM MTVISITS
						WHERE CLIENTID = MKT.CLIENTID AND CLIENTCENTERID = MKT.CLIENTCENTERID AND DELETEDID = '1' AND MARKETINGACTIONID IS NOT NULL
					)
					AND MKT.MARKETINGACTIONID NOT IN (
						SELECT MARKETINGACTIONID2 FROM MTVISITS
						WHERE CLIENTID = MKT.CLIENTID AND CLIENTCENTERID = MKT.CLIENTCENTERID AND DELETEDID = '1' AND MARKETINGACTIONID2 IS NOT NULL
					)
					AND MKT.MARKETINGACTIONID NOT IN (
						SELECT MARKETINGACTIONID3 FROM MTVISITS
						WHERE CLIENTID = MKT.CLIENTID AND CLIENTCENTERID = MKT.CLIENTCENTERID AND DELETEDID = '1' AND MARKETINGACTIONID3 IS NOT NULL
					)
				GROUP BY MKT.CLIENTID, MKT.CLIENTCENTERID) MK ON MK.CLIENTID = CEN.CLIENTID AND MK.CLIENTCENTERID = CEN.CLIENTCENTERID 
     WHERE CEN.DELETEDID = '1';