CREATE OR REPLACE VIEW SUM_EFETIVOS (IDFATUPLAN, CDFILIAL,  CDCLIENTE,   CDTURNO,
                                     CDSERVFILI, DTFATURAM, CDGRSERVFAT, CDPRODUTO, CDPTOVNDFATUR,
                                     VRSERVICO,  QTEFETIVOS, VRSERVFATUDT, NMSERVICO, CDSERVPLAN, QTVENDAEFETCLI)
AS (SELECT 'P' IDFATUPLAN, EFETCLIE.CDFILIAL,   EFETCLIE.CDCLIENTE,
           '' AS CDTURNO,  DECODE(SERVCLIE.CDSERVFATU, NULL,EFETCLIE.CDSERVFILI, SERVCLIE.CDSERVFATU),
           EFETCLIE.DTEFETCLIE DTFATURAM,
           SERVCLIE.CDGRSERVFAT, SERVICO.CDPRODUTO, SERVCLIE.CDPTOVNDFATUR,
           UTIL_APURAC.CalcValrServ(EFETCLIE.CDFILIAL, EFETCLIE.CDCLIENTE, EFETCLIE.CDSERVFILI, DTEFETCLIE, NULL) VRSERVICO,
           EFETCLIE.QTCOMEFETCLI QTEFETIVOS, EFETCLIE.VRSERVFATUEFCLI AS VRSERVFATUDT,
           UTIL_APURAC.DsNmServicoTur(EFETCLIE.CDFILIAL, EFETCLIE.CDCLIENTE, EFETCLIE.CDSERVFILI, DTEFETCLIE, NULL) NMSERVICO,
           EFETCLIE.CDSERVPLAN, EFETCLIE.QTVENDAEFETCLI
    FROM EFETCLIE, SERVCLIE, SERVICO
    WHERE EFETCLIE.CDFILIAL    = SERVCLIE.CDFILIAL   AND
          EFETCLIE.CDCLIENTE   = SERVCLIE.CDCLIENTE  AND
          EFETCLIE.CDSERVFILI  = SERVCLIE.CDSERVFILI AND
          EFETCLIE.QTCOMEFETCLI <> 0                 AND
          SERVICO.CDFILIAL     = EFETCLIE.CDFILIAL   AND
          SERVICO.CDSERVFILI   = EFETCLIE.CDSERVFILI AND
          ((SERVICO.IDFATUSERV   <> 'N') OR (SERVICO.IDFATUSERV IS NULL)) AND
              NOT EXISTS (SELECT CDFILIAL, CDSERVFILI,CDCLIENTE, DTFATTURNO
                       FROM TURNOCLIE
                       WHERE TURNOCLIE.CDFILIAL     = EFETCLIE.CDFILIAL   AND
                             TURNOCLIE.CDSERVFILI   = EFETCLIE.CDSERVFILI AND
                             TURNOCLIE.CDCLIENTE    = EFETCLIE.CDCLIENTE  AND
                             TURNOCLIE.DTFATTURNO   = EFETCLIE.DTEFETCLIE)
    UNION

    SELECT 'P' IDFATUPLAN, TURNOCLIE.CDFILIAL,  TURNOCLIE.CDCLIENTE,
           TURNOCLIE.CDTURNO, TURNOCLIE.CDSERVFILI,
           TURNOCLIE.DTFATTURNO,
           '' AS CDGRSERVFAT,    SERVICO.CDPRODUTO, ' ' AS CDPTOVNDFATUR,
           UTIL_APURAC.CalcValrServTur(TURNOCLIE.CDFILIAL, TURNOCLIE.CDCLIENTE, TURNOCLIE.CDSERVFILI, TURNOCLIE.DTFATTURNO, TURNOCLIE.CDTURNO) VRSERVICO,
           TURNOCLIE.QTCOMTURNCLI QTEFETIVOS, 0 AS VRSERVFATUDT,
           UTIL_APURAC.DsNmServicoTur (TURNOCLIE.CDFILIAL, TURNOCLIE.CDCLIENTE, TURNOCLIE.CDSERVFILI, TURNOCLIE.DTFATTURNO, TURNOCLIE.CDTURNO) NMSERVICO,
           TURNOCLIE.CDSERVFILI AS CDSERVPLAN, 0 AS QTVENDAEFETCLI
    FROM TURNOCLIE, SERVICO
    WHERE TURNOCLIE.QTCOMTURNCLI  <> 0                    AND
          SERVICO.CDFILIAL     = TURNOCLIE.CDFILIAL       AND
          SERVICO.CDSERVFILI   = TURNOCLIE.CDSERVFILI     AND
          ((SERVICO.IDFATUSERV   <> 'N') OR (SERVICO.IDFATUSERV IS NULL))

    UNION

    SELECT 'F' IDFATUPLAN, EFECLIFAT.CDFILIAL,   EFECLIFAT.CDCLIENTE,
           '' AS CDTURNO,  DECODE(SERVCLIE.CDSERVFATU, NULL,EFECLIFAT.CDSERVFILI, SERVCLIE.CDSERVFATU),
           EFECLIFAT.DTFATURAM,
           SERVCLIE.CDGRSERVFAT, SERVICO.CDPRODUTO, SERVCLIE.CDPTOVNDFATUR,
           UTIL_APURAC.CalcValrServ(EFECLIFAT.CDFILIAL, EFECLIFAT.CDCLIENTE, EFECLIFAT.CDSERVFILI, DTFATURAM, NULL) VRSERVICO,
           EFECLIFAT.QTCOMEFATU QTEFETIVOS, NVL(EFECLIFAT.VRSERVFATUDT, 0) AS VRSERVFATUDT,
           UTIL_APURAC.DsNmServicoTur(EFECLIFAT.CDFILIAL, EFECLIFAT.CDCLIENTE, EFECLIFAT.CDSERVFILI, DTFATURAM, NULL) NMSERVICO,
           EFECLIFAT.CDSERVPLAN AS CDSERVPLAN, 0 AS QTVENDAEFETCLI
    FROM EFECLIFAT, SERVCLIE, SERVICO
    WHERE EFECLIFAT.CDFILIAL   = SERVCLIE.CDFILIAL    AND
          EFECLIFAT.CDCLIENTE  = SERVCLIE.CDCLIENTE   AND
          EFECLIFAT.CDSERVFILI = SERVCLIE.CDSERVFILI  AND
          EFECLIFAT.QTCOMEFATU <> 0                   AND
          SERVICO.CDFILIAL     = EFECLIFAT.CDFILIAL   AND
          SERVICO.CDSERVFILI   = EFECLIFAT.CDSERVFILI AND
          ((SERVICO.IDFATUSERV   <> 'N') OR (SERVICO.IDFATUSERV IS NULL)) AND
           NOT EXISTS (SELECT CDFILIAL, CDSERVFILI,CDCLIENTE, DTFATURAMTUR
                       FROM EFECLIFATTUR
                       WHERE EFECLIFATTUR.CDFILIAL     = EFECLIFAT.CDFILIAL   AND
                             EFECLIFATTUR.CDSERVFILI   = EFECLIFAT.CDSERVFILI AND
                             EFECLIFATTUR.CDCLIENTE    = EFECLIFAT.CDCLIENTE  AND
                             EFECLIFATTUR.DTFATURAMTUR = EFECLIFAT.DTFATURAM)
    UNION

    SELECT 'F' IDFATUPLAN,       EFECLIFATTUR.CDFILIAL,   EFECLIFATTUR.CDCLIENTE,
           EFECLIFATTUR.CDTURNO, EFECLIFATTUR.CDSERVFILI,
           EFECLIFATTUR.DTFATURAMTUR,
           '' AS CDGRSERVFAT,    SERVICO.CDPRODUTO, ' ' AS CDPTOVNDFATUR,
           UTIL_APURAC.CalcValrServTur(EFECLIFATTUR.CDFILIAL, EFECLIFATTUR.CDCLIENTE, EFECLIFATTUR.CDSERVFILI, EFECLIFATTUR.DTFATURAMTUR, EFECLIFATTUR.CDTURNO) VRSERVICO,
           EFECLIFATTUR.QTCOMFATUTUR QTEFETIVOS, 0 AS VRSERVFATUDT,
           UTIL_APURAC.DsNmServicoTur (EFECLIFATTUR.CDFILIAL, EFECLIFATTUR.CDCLIENTE, EFECLIFATTUR.CDSERVFILI, EFECLIFATTUR.DTFATURAMTUR, EFECLIFATTUR.CDTURNO) NMSERVICO,
           EFECLIFATTUR.CDSERVFILI AS CDSERVPLAN, 0 AS QTVENDAEFETCLI
    FROM EFECLIFATTUR, SERVICO
    WHERE EFECLIFATTUR.QTCOMFATUTUR  <> 0                    AND
          SERVICO.CDFILIAL     = EFECLIFATTUR.CDFILIAL       AND
          SERVICO.CDSERVFILI   = EFECLIFATTUR.CDSERVFILI     AND
          ((SERVICO.IDFATUSERV   <> 'N') OR (SERVICO.IDFATUSERV IS NULL)))
/

-- VERSAO --
BEGIN ATUVEROBJ('SUM_EFETIVOS','5.06.050'); END;
/
