CREATE OR REPLACE PROCEDURE IMPORTAORCAGOP (P_CDOPERADOR  IN VARCHAR2) IS

  V_LISTFILIAL      VARCHAR2(10);
  V_LISTOPERADOR    VARCHAR2(10);
  V_LOGERRO         NUMBER;
  V_ITEMORCAMENTO   VARCHAR2(20);

  V_IDORCDESPDIVERS VARCHAR2(01);
  V_IDORCADESPMATRI VARCHAR2(01);
  
  V_DTPROCLIMORC    DATE;
BEGIN
   SELECT NVL(MAX(IDORCDESPDIVERS),'P'), NVL(MAX(IDORCADESPMATRI),'P')
     INTO V_IDORCDESPDIVERS, V_IDORCADESPMATRI
     FROM PARAMORCAOPERAC;

   FOR C_UNIDADE IN (SELECT INT.NRINTGORCAGOP, INT.CDFILIAL, INT.NRANO, P.IDPERIODOORCA, P.NRPERIODO, P.DTINIPERIORCA, P.DTFIMPERIORCA
                       FROM (SELECT CDFILIAL, TO_CHAR(NRANO,'YYYY') NRANO, MES, NRINTGORCAGOP
                               FROM INTGORCAGOP
                            UNPIVOT ((VALOR)
                                FOR MES IN ((VRORCAJAN) AS '01',
                                            (VRORCAFEV) AS '02',
                                            (VRORCAMAR) AS '03',
                                            (VRORCAABR) AS '04',
                                            (VRORCAMAI) AS '05',
                                            (VRORCAJUN) AS '06',
                                            (VRORCAJUL) AS '07',
                                            (VRORCAAGO) AS '08',
                                            (VRORCASET) AS '09',
                                            (VRORCAOUT) AS '10',
                                            (VRORCANOV) AS '11',
                                            (VRORCADEZ) AS '12'))
                              WHERE NVL(IDORCAIMP,'N') = 'N'
                              ORDER BY CDFILIAL) INT, PERIODOORCAGOP P
                      WHERE ((P.CDFILIAL = INT.CDFILIAL) OR (P.CDFILIAL = 'P' AND NOT EXISTS (SELECT 1
                                                                                                FROM PERIODOORCAGOP
                                                                                               WHERE PERIODOORCAGOP.CDFILIAL = INT.CDFILIAL)))
                        --AND TO_NUMBER(INT.MES) BETWEEN TO_NUMBER(TO_CHAR(P.DTINIPERIORCA,'MM'))  AND TO_NUMBER(TO_CHAR(P.DTFIMPERIORCA,'MM'))
                      GROUP BY INT.NRINTGORCAGOP, INT.CDFILIAL, INT.NRANO, P.IDPERIODOORCA, P.NRPERIODO, P.DTINIPERIORCA, P.DTFIMPERIORCA) LOOP

      SELECT LPAD(ABS(ROUND(DBMS_RANDOM.VALUE(9, 9999999999))), 10, '0')
        INTO V_LISTFILIAL
        FROM DUAL;
        
      SELECT DECODE(P.IDUTILDTPROC, 'S', NVL(PF.DTPROCESSA, SYSDATE), SYSDATE)-PO.NRDIALIMITEORCA DTPROCLIMORC
        INTO V_DTPROCLIMORC
        FROM FILIAL F INNER JOIN PARAMFILIAL PF ON PF.CDFILIAL = F.CDFILIAL
                      CROSS JOIN PARAMGERAL P
                      CROSS JOIN PARAMORCAOPERAC PO
       WHERE F.CDFILIAL = C_UNIDADE.CDFILIAL;

      INSERT INTO LISTA (CDLISTA, NRLISTA)
           VALUES (C_UNIDADE.CDFILIAL, V_LISTFILIAL);

      SELECT LPAD(ABS(ROUND(DBMS_RANDOM.VALUE(9, 9999999999))), 10, '0')
        INTO V_LISTOPERADOR
        FROM DUAL;

      INSERT INTO LISTA (CDLISTA, NRLISTA)
           VALUES (P_CDOPERADOR, V_LISTOPERADOR);

      V_LOGERRO := 0;

      PREPARA_ORCAMENTO(C_UNIDADE.NRANO, C_UNIDADE.NRPERIODO, V_LISTOPERADOR, V_LISTFILIAL, 'N', 'N', 'N', 'S', 'SSSSSSSSSSSS', P_CDOPERADOR);

      SELECT NVL(MAX(NRANOORCAGOP),0)
        INTO V_LOGERRO
        FROM LOGPREPARAORCAGOP
       WHERE NRANOORCAGOP = C_UNIDADE.NRANO
         AND IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
         AND NRPERIODOORCA = C_UNIDADE.NRPERIODO
         AND CDFILIAL = C_UNIDADE.CDFILIAL;

      IF V_LOGERRO = 0 THEN
         FOR C_INTEGRAORCA IN (SELECT INT.CDITMORCA, INT.CDDESPDIV, INT.NIVELPROD, INT.NIVELCTBL, INT.MES, INT.VRORCAMENTO, P.IDPERIODOORCA, P.NRPERIODO
                                 FROM (SELECT CDFILIAL, CDITMORCA, CDDESPDIV, CDARVPROD NIVELPROD, CDCONTCTBL NIVELCTBL, MES, VALOR VRORCAMENTO
                                         FROM INTGORCAGOP
                                      UNPIVOT ((VALOR)
                                          FOR MES IN ((VRORCAJAN) AS TO_CHAR(TO_DATE('01','MM'),'MONTH'),
                                                      (VRORCAFEV) AS TO_CHAR(TO_DATE('02','MM'),'MONTH'),
                                                      (VRORCAMAR) AS TO_CHAR(TO_DATE('03','MM'),'MONTH'),
                                                      (VRORCAABR) AS TO_CHAR(TO_DATE('04','MM'),'MONTH'),
                                                      (VRORCAMAI) AS TO_CHAR(TO_DATE('05','MM'),'MONTH'),
                                                      (VRORCAJUN) AS TO_CHAR(TO_DATE('06','MM'),'MONTH'),
                                                      (VRORCAJUL) AS TO_CHAR(TO_DATE('07','MM'),'MONTH'),
                                                      (VRORCAAGO) AS TO_CHAR(TO_DATE('08','MM'),'MONTH'),
                                                      (VRORCASET) AS TO_CHAR(TO_DATE('09','MM'),'MONTH'),
                                                      (VRORCAOUT) AS TO_CHAR(TO_DATE('10','MM'),'MONTH'),
                                                      (VRORCANOV) AS TO_CHAR(TO_DATE('11','MM'),'MONTH'),
                                                      (VRORCADEZ) AS TO_CHAR(TO_DATE('12','MM'),'MONTH')))
                                        WHERE NVL(IDORCAIMP,'N') = 'N'
                                          AND CDFILIAL = C_UNIDADE.CDFILIAL
                                          AND TO_CHAR(NRANO,'YYYY') = TO_CHAR(C_UNIDADE.NRANO)) INT, PERIODOORCAGOP P
                                WHERE ((P.CDFILIAL = INT.CDFILIAL) OR
                                       (P.CDFILIAL = 'P' AND NOT EXISTS (SELECT 1
                                                                           FROM PERIODOORCAGOP
                                                                          WHERE PERIODOORCAGOP.CDFILIAL = INT.CDFILIAL)))
                                  --AND TO_NUMBER(INT.MES) BETWEEN TO_NUMBER(TO_CHAR(P.DTINIPERIORCA,'MM')) AND TO_NUMBER(TO_CHAR(P.DTFIMPERIORCA,'MM'))
                                  AND P.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                                  AND P.NRPERIODO = C_UNIDADE.NRPERIODO) LOOP
                                  
            IF TRUNC(V_DTPROCLIMORC,'MM') <= TO_DATE('01/'||TO_CHAR(TO_DATE(C_INTEGRAORCA.MES,'MONTH'),'MM')||'/'||C_UNIDADE.NRANO,'DD/MM/YYYY') THEN
               SELECT CASE C_INTEGRAORCA.CDITMORCA
                         WHEN 0  THEN 'FATURAMENTO'
                         WHEN 1  THEN 'IMPOSTO'
                         WHEN 2  THEN 'CONSUMO'
                         WHEN 3  THEN 'MAOOBRA'          
                         WHEN 4  THEN 'TAXAADM'    
                         WHEN 5  THEN 'DESPMATRIZ' 
                         WHEN 6  THEN 'DESPDIVERSA'      
                         WHEN 7  THEN 'DESPENTRADA'
                         WHEN 8  THEN 'IMPOSTOARECUPERAR'
                         WHEN 9  THEN 'IMPOSTOSOBLUCRO'
                         WHEN 10 THEN 'INVESTIMENTO'
                         WHEN 11 THEN 'DEPRECIACAO'
                       END
                 INTO V_ITEMORCAMENTO
                 FROM DUAL;

               IF (C_INTEGRAORCA.NIVELPROD IS NOT NULL) OR (C_INTEGRAORCA.NIVELCTBL IS NOT NULL) THEN
                  -- Atualiza Orcamento
                  IF (V_IDORCDESPDIVERS = 'C' AND V_ITEMORCAMENTO = 'DESPDIVERSA') OR (V_IDORCADESPMATRI = 'C' AND V_ITEMORCAMENTO = 'DESPMATRIZ') THEN
                     UPDATE (SELECT D.VRORCAMENTO, D.VRPERCENTUAL
                               FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON O.NRORCAMENTOGOP = I.NRORCAMENTOGOP
                                                                           AND O.CDFILIAL = I.CDFILIAL
                                                                           AND I.IDTIPOORCAOP = V_ITEMORCAMENTO
                                                   INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                                                   INNER JOIN ITDETALORCAMESGOP D ON D.NRITORCAMESGOP = M.NRITORCAMESGOP
                              WHERE O.NRANOORCAGOP = C_UNIDADE.NRANO
                                AND O.CDFILIAL = C_UNIDADE.CDFILIAL
                                AND O.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                                AND D.CDCONTCTBL = C_INTEGRAORCA.NIVELCTBL
                                AND TRIM(UPPER(TO_CHAR(M.DTORCAMENTO,'MONTH')))= TRIM(UPPER(C_INTEGRAORCA.MES))) X
                        SET X.VRORCAMENTO = C_INTEGRAORCA.VRORCAMENTO;
                  ELSE
                     UPDATE (SELECT D.VRORCAMENTO, D.VRPERCENTUAL
                               FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON O.NRORCAMENTOGOP = I.NRORCAMENTOGOP
                                                                           AND O.CDFILIAL = I.CDFILIAL
                                                                           AND I.IDTIPOORCAOP = V_ITEMORCAMENTO
                                                   INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                                                   INNER JOIN ITDETALORCAMESGOP D ON D.NRITORCAMESGOP = M.NRITORCAMESGOP
                              WHERE O.NRANOORCAGOP = C_UNIDADE.NRANO
                                AND O.CDFILIAL = C_UNIDADE.CDFILIAL
                                AND O.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                                AND (D.CDPRODUTO = (SELECT CDPRODUTO
                                                      FROM PRODUTO
                                                     WHERE CDARVPROD = C_INTEGRAORCA.NIVELPROD) 
                                 OR (D.CDORCAMENTOMO = LPAD(C_INTEGRAORCA.CDDESPDIV, 10, '0') AND D.CDDESPADMFAT = C_INTEGRAORCA.NIVELPROD))
                                AND TRIM(UPPER(TO_CHAR(M.DTORCAMENTO,'MONTH')))= TRIM(UPPER(C_INTEGRAORCA.MES))) X
                        SET X.VRORCAMENTO = C_INTEGRAORCA.VRORCAMENTO;
                  END IF;

                  -- Atualiza total do orcamento
                  UPDATE (SELECT M.VRORCAMENTO, M.NRITORCAMESGOP
                            FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON O.NRORCAMENTOGOP = I.NRORCAMENTOGOP
                                                                        AND O.CDFILIAL = I.CDFILIAL
                                                                        AND I.IDTIPOORCAOP = V_ITEMORCAMENTO
                                                INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                           WHERE O.NRANOORCAGOP = C_UNIDADE.NRANO
                             AND O.CDFILIAL = C_UNIDADE.CDFILIAL
                             AND O.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                             AND O.NRPERIODOORCA = C_UNIDADE.NRPERIODO
                             AND TRIM(UPPER(TO_CHAR(M.DTORCAMENTO,'MONTH')))= TRIM(UPPER(C_INTEGRAORCA.MES))) X
                     SET X.VRORCAMENTO = (SELECT SUM(D.VRORCAMENTO)
                                            FROM ITDETALORCAMESGOP D
                                           WHERE D.NRITORCAMESGOP = X.NRITORCAMESGOP);
               ELSE
                  -- Atualiza total do orcamento
                  UPDATE (SELECT M.VRORCAMENTO, M.NRITORCAMESGOP
                            FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON O.NRORCAMENTOGOP = I.NRORCAMENTOGOP
                                                                        AND O.CDFILIAL = I.CDFILIAL
                                                                        AND I.IDTIPOORCAOP = V_ITEMORCAMENTO
                                                                        AND ((I.CDDESPPREV = C_INTEGRAORCA.CDDESPDIV) OR 
                                                                             (I.CDIMPOSTO  = LPAD(C_INTEGRAORCA.CDDESPDIV,2,'0')) OR
                                                                             (I.CDINVEST  = LPAD(C_INTEGRAORCA.CDDESPDIV,10,'0')) OR
                                                                             (I.CDTIPOOPER  = LPAD(C_INTEGRAORCA.CDDESPDIV,2,'0') AND (I.IDENTRSAIDOP = 'E' )) OR
                                                                            (C_INTEGRAORCA.CDDESPDIV IS NULL) OR 
                                                                            (I.CDORCAMENTOMO = LPAD(C_INTEGRAORCA.CDDESPDIV,10,'0')))
                                                INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                           WHERE O.NRANOORCAGOP = C_UNIDADE.NRANO
                             AND O.CDFILIAL = C_UNIDADE.CDFILIAL
                             AND O.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                             AND O.NRPERIODOORCA = C_UNIDADE.NRPERIODO
                             AND TRIM(UPPER(TO_CHAR(M.DTORCAMENTO,'MONTH')))= TRIM(UPPER(C_INTEGRAORCA.MES))) X
                     SET X.VRORCAMENTO = C_INTEGRAORCA.VRORCAMENTO;
                  
                  UPDATE (SELECT D.VRORCAMENTO, D.VRPERCENTUAL, I.IDTIPOORCAOP, D.QTORCAMENTO, D.VRPRECOBASE,
                                 (SELECT COUNT(1) 
                                    FROM ITDETALORCAMESGOP C 
                                   WHERE M.NRITORCAMESGOP = C.NRITORCAMESGOP) QTDE, M.VRORCAMENTO VRORCATOTAL
                            FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON O.NRORCAMENTOGOP = I.NRORCAMENTOGOP
                                                                        AND O.CDFILIAL = I.CDFILIAL
                                                                        AND I.IDTIPOORCAOP IN ('FATURAMENTO', 'CONSUMO', 'MAOOBRA')
                                                                        AND I.IDTIPOORCAOP = V_ITEMORCAMENTO
                                                INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                                                INNER JOIN ITDETALORCAMESGOP D ON D.NRITORCAMESGOP = M.NRITORCAMESGOP
                           WHERE O.NRANOORCAGOP = C_UNIDADE.NRANO
                             AND O.CDFILIAL = C_UNIDADE.CDFILIAL
                             AND O.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                             AND TRIM(UPPER(TO_CHAR(M.DTORCAMENTO,'MONTH')))= TRIM(UPPER(C_INTEGRAORCA.MES))) X
                     SET X.VRORCAMENTO = X.VRORCATOTAL/CASE WHEN X.QTDE = 0 THEN 1 ELSE X.QTDE END,
                         X.QTORCAMENTO = X.VRORCATOTAL/CASE WHEN X.QTDE = 0 THEN 1 ELSE X.QTDE END,
                         X.VRPRECOBASE = CASE WHEN X.IDTIPOORCAOP = 'FATURAMENTO' THEN 1 ELSE NULL END;
               END IF;
            END IF;
         END LOOP;

         -- Atualiza percentual
         UPDATE (SELECT D.VRORCAMENTO, D.VRPERCENTUAL, DECODE(M.VRORCAMENTO,0,1, M.VRORCAMENTO) TOTAL
                   FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON O.NRORCAMENTOGOP = I.NRORCAMENTOGOP
                                                               AND O.CDFILIAL = I.CDFILIAL
                                       INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                                       INNER JOIN ITDETALORCAMESGOP D ON D.NRITORCAMESGOP = M.NRITORCAMESGOP
                  WHERE O.NRANOORCAGOP = C_UNIDADE.NRANO
                    AND O.CDFILIAL = C_UNIDADE.CDFILIAL
                    AND O.IDPERIODOORCA = C_UNIDADE.IDPERIODOORCA
                    AND O.NRPERIODOORCA = C_UNIDADE.NRPERIODO) X
            SET X.VRPERCENTUAL = (X.VRORCAMENTO*100)/X.TOTAL;
      END IF;

      --Atualiza o registro da integracao como importado
      UPDATE INTGORCAGOP
         SET IDORCAIMP = 'S'
       WHERE NRINTGORCAGOP = C_UNIDADE.NRINTGORCAGOP
         AND CDFILIAL = C_UNIDADE.CDFILIAL;

      COMMIT;
   END LOOP;
END;
/