CREATE OR REPLACE PROCEDURE GERA_REL_FATPER(
   P_CDFILIAL         IN VARCHAR2,
   P_CDCLIENTE        IN VARCHAR2,
   P_CDLOCAFATCIA     IN VARCHAR2,
   P_DTINICIAL        IN DATE,
   P_DTFINAL          IN DATE,
   P_DTHRFATPER       IN DATE,
   P_NRFATUPERI OUT VARCHAR2
) AS
   V_DIA          VARCHAR2(02);
   V_UPDATE_TEXT  VARCHAR2(1000);
   C_UPDATE       INTEGER;
   V_RESULT       INTEGER;
   V_NRFATUPERI   VARCHAR2(20);
BEGIN
   NOVO_CODIGO('RELAT_FATUPER'||P_CDFILIAL,V_NRFATUPERI,1);
   V_NRFATUPERI := SUBSTR(V_NRFATUPERI,11,10);
   INSERT INTO RELAT_FATUPER
     (NRFATUPERI, CDFILIFATP, CDCLIEFATP, CDSERVFATP, CDLOCAFATP, DTEMISFATP,
      VRUNISERFATP, QTTOTSERFATP)
   (SELECT
       V_NRFATUPERI, A.CDFILIAL, A.CDCLIENTE, A.CDSERVICO, A.CDLOCAFATCIA, TO_DATE(P_DTHRFATPER,'DD/MM/YYYY HH24:MI:SS'),
       A.VRUNISERFATP,   SUM(QTSERVICO) QTSERVICO
    FROM
      (SELECT
           SP.CDFILIAL, C.CDCLIENTE,  SP.CDSERVICO, DE.CDLOCAFATCIA,
           DECODE(IDNACPRODUTO,1, NVL(SP.VRPRECMANUTSESP,  VRPRURESESP), NVL(SP.VRPRECMANUTSESP, VRPRUDOLSESP)) VRUNISERFATP,  (SP.QTSESBYUTSP + SP.QTSERFATSP) QTSERVICO
       FROM
           SERVPLANO SP, DELIVERY DE, COMPANHIA C, PRODUTO PR
       WHERE
          (DE.CDFILIAL     = P_CDFILIAL) AND
          ((C.CDCLIENTE   = P_CDCLIENTE ) OR (P_CDCLIENTE = 'T')) AND
          (DE.DTEMISDEL BETWEEN P_DTINICIAL AND P_DTFINAL) AND
          ((DE.CDLOCAFATCIA = P_CDLOCAFATCIA) OR (P_CDLOCAFATCIA = 'T')) AND
          (DE.NRDELIVERYFA IS NOT NULL    ) AND
          (DE.CDCIA        = C.CDCIA      ) AND
          (SP.CDFILIAL     = DE.CDFILIAL  ) AND
          (SP.NRDELIVERY   = DE.NRDELIVERY) AND
          (SP.CDSERVICO    = PR.CDPRODUTO )) A
    GROUP BY
      A.CDFILIAL, A.CDCLIENTE,  A.CDSERVICO, A.CDLOCAFATCIA,  A.VRUNISERFATP);
   FOR CURS IN (SELECT *
                FROM RELAT_FATUPER
                WHERE
                  (CDFILIFATP = P_CDFILIAL) AND
                  (NRFATUPERI = V_NRFATUPERI) AND
                  (DTEMISFATP = TO_DATE(P_DTHRFATPER,'DD/MM/YYYY HH24:MI:SS'))) LOOP
      FOR CURS2 IN (SELECT DE.DTEMISDEL, SUM(SP.QTSESBYUTSP + SP.QTSERFATSP) AS QTSER
                    FROM
                       SERVPLANO SP, DELIVERY DE, COMPANHIA C, PRODUTO PR
                    WHERE
                       (DE.CDFILIAL     = P_CDFILIAL) AND
                       (DE.DTEMISDEL BETWEEN P_DTINICIAL AND P_DTFINAL) AND
                       (C.CDCLIENTE    = CURS.CDCLIEFATP) AND
                       (NVL(DE.CDLOCAFATCIA, ' ') = NVL(CURS.CDLOCAFATP, ' ')) AND
                       (DE.NRDELIVERYFA IS NOT NULL    ) AND
                       (DE.CDCIA        = C.CDCIA        ) AND
                       (SP.CDFILIAL     = DE.CDFILIAL    ) AND
                       (SP.NRDELIVERY   = DE.NRDELIVERY  ) AND
                       (SP.CDSERVICO    = CURS.CDSERVFATP) AND
                       (SP.CDSERVICO    = PR.CDPRODUTO   ) AND
                       (CURS.VRUNISERFATP = DECODE(PR.IDNACPRODUTO,1, NVL(SP.VRPRECMANUTSESP, SP.VRPRURESESP), NVL(SP.VRPRECMANUTSESP, SP.VRPRUDOLSESP)))
                    GROUP BY
                       DE.DTEMISDEL) LOOP
         V_DIA := LPAD(TO_CHAR((CURS2.DTEMISDEL - P_DTINICIAL) + 1),2,'0');
         V_UPDATE_TEXT := '';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'UPDATE RELAT_FATUPER ';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'SET QTSERV'  || V_DIA || 'FATP = :X ';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'WHERE (CDFILIFATP = :A) AND';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'      (CDCLIEFATP = :B) AND';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'      (CDSERVFATP = :C) AND';
         IF CURS.CDLOCAFATP IS NULL THEN
            V_UPDATE_TEXT := V_UPDATE_TEXT ||'   (CDLOCAFATP IS NULL) AND';
         ELSE
            V_UPDATE_TEXT := V_UPDATE_TEXT ||'   (CDLOCAFATP = :D) AND';
         END IF;
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'      (DTEMISFATP = :E) AND';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'      (NRFATUPERI = :F) AND ';
         V_UPDATE_TEXT := V_UPDATE_TEXT ||'      (VRUNISERFATP = :G) ';
         C_UPDATE := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_UPDATE, V_UPDATE_TEXT, DBMS_SQL.V7);
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':X', CURS2.QTSER);
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':A', CURS.CDFILIFATP);
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':B', CURS.CDCLIEFATP);
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':C', CURS.CDSERVFATP);
         IF CURS.CDLOCAFATP IS NOT NULL THEN
            DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':D', CURS.CDLOCAFATP);
         END IF;
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':E', CURS.DTEMISFATP);
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':F', CURS.NRFATUPERI);
         DBMS_SQL.BIND_VARIABLE(C_UPDATE, ':G', CURS.VRUNISERFATP);
         V_RESULT := DBMS_SQL.EXECUTE(C_UPDATE);
         DBMS_SQL.CLOSE_CURSOR(C_UPDATE);
      END LOOP;
   END LOOP;
   P_NRFATUPERI := V_NRFATUPERI;
END;
/



-- VERSAO --
BEGIN ATUVEROBJ('GERA_REL_FATPER','5.01.009'); END;
/

