create or replace PROCEDURE CONSISTE_ORCAMENTO(
    P_CDFILDEST   IN AUTOFORNE.CDFILDEST%TYPE,
    P_CDFILLANC   IN AUTOFORNE.CDFILLANC%TYPE,
    P_NRAUTOFORN  IN AUTOFORNE.NRAUTOFORN%TYPE,
    P_RETORNO     OUT VARCHAR2)
AS
    V_MAXVERSAO    VARCHAR2(4);
    V_VRORCAMENTO  FLOAT;
    V_NRSEQ        VARCHAR2(6);  
    V_VRPRODCONS   FLOAT;  
    V_VRPRODDESP   FLOAT;
    V_VRPROD       FLOAT;
    V_IDITEMORCAMENTO VARCHAR2(1);
    V_DESCERRO     VARCHAR2(2000);
    V_EXISTEORCAMENTO VARCHAR2(10);
    V_DTPROGENTAF  DATE; 
    V_CDDESPPREV   VARCHAR2(10);
    V_CDARVPROD    VARCHAR2(20);
    V_CDARVGERAL   VARCHAR2(20);
    V_IDTIPOORCAOP VARCHAR2(1);
BEGIN
  V_VRORCAMENTO     := 0;
  V_VRPROD          := 0;
  V_IDITEMORCAMENTO := NULL; 

   FOR C_FILIAL IN  
     (SELECT IDCONSAFORCFIL,VRPEORCBLOQFIL
        FROM FILIAL  
       WHERE CDFILIAL = P_CDFILDEST
     ) LOOP
      IF C_FILIAL.IDCONSAFORCFIL = 'S' THEN 
        FOR C_AUTOFORNE IN(
            SELECT DISTINCT LAST_DAY(DTPROGENTAF) AS DTPROGENTAF
              FROM ITEMAUTF AF  
             WHERE AF.CDFILDEST  = P_CDFILDEST  
               AND AF.CDFILLANC  = P_CDFILLANC
               AND AF.NRAUTOFORN = P_NRAUTOFORN ) LOOP
            SELECT TO_CHAR(MAX(CDVERSAOORCA)) CDVERSAOORCA  
              INTO V_MAXVERSAO
              FROM VERSAOORCA
             WHERE CDANOVERORCA = TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'YYYY');  
    
            BEGIN       
               SELECT SUM(NVL(DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'01', VRORCAMENTOJAN,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'02', VRORCAMENTOFEV,  
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'03', VRORCAMENTOMAR,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'04', VRORCAMENTOABR,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'05', VRORCAMENTOMAI,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'06', VRORCAMENTOJUN,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'07', VRORCAMENTOJUL,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'08', VRORCAMENTOAGO,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'09', VRORCAMENTOSET,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'10', VRORCAMENTOOUT,
                     DECODE(TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM'),'11', VRORCAMENTONOV, VRORCAMENTODEZ))))))))))),0)) AS VRORCAMENTO,
                     IDITEMORCAMENTO AS IDITEMORCAMENTO 
                     INTO V_VRORCAMENTO , V_IDITEMORCAMENTO 
                FROM ITEMORCAOP  
               WHERE CDFILIAL     = P_CDFILDEST  
                 AND NRANOORCAOP  = TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'YYYY')
                 AND CDVERSORCAOP = V_MAXVERSAO  
                 AND IDTIPOORCAOP = 'C' 
                 AND IDITEMORCAMENTO = 'A' 
           GROUP  BY IDITEMORCAMENTO; 
            EXCEPTION
                WHEN NO_DATA_FOUND THEN 
                  V_VRORCAMENTO     := 0; 
                  V_IDITEMORCAMENTO := 'N'; 
            END;
            
            DELETE FROM ERROCONSORCA
             WHERE CDFILLANC  = P_CDFILLANC AND
                   CDFILDEST  = P_CDFILDEST AND
                   NRAUTOFORN = P_NRAUTOFORN;
                   
           FOR C_ITEMAUF IN (  
               SELECT SUM((NVL(VRPREUNIAF,0) * NVL(QTPROGENTAF,0))) AS VRPROD
                 FROM ITEMAUTF,PRODFILI,PRODUTO,AUTOFORNE  
                WHERE ITEMAUTF.CDFILDEST                      = P_CDFILDEST  
                  AND ITEMAUTF.CDFILLANC                      = P_CDFILLANC  
                  AND ITEMAUTF.CDPRODUTO                      = PRODFILI.CDPRODUTO
                  AND ITEMAUTF.CDFILDEST                      = PRODFILI.CDFILIAL  
                  AND ITEMAUTF.CDPRODUTO                      = PRODUTO.CDPRODUTO  
                  AND ITEMAUTF.CDFILDEST                      = AUTOFORNE.CDFILDEST  
                  AND ITEMAUTF.CDFILLANC                      = AUTOFORNE.CDFILLANC  
                  AND ITEMAUTF.NRAUTOFORN                     = AUTOFORNE.NRAUTOFORN
                  AND NVL(AUTOFORNE.IDBAIXAAF, '1') NOT IN ('4','5')
                  AND TO_CHAR(ITEMAUTF.DTPROGENTAF,'MM/YYYY') = TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM/YYYY')  
                  AND PRODFILI.IDCNTRESTOQ                    = 'S'  
                  AND ((AUTOFORNE.IDSITUATUAAF IS NULL AND IDAFFORALIM = 'N') OR (AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN))
            ) LOOP 
              IF C_FILIAL.VRPEORCBLOQFIL <> 0 THEN  
                  V_VRPRODCONS := ROUND(V_VRORCAMENTO,2) + ((ROUND(V_VRORCAMENTO,2)*C_FILIAL.VRPEORCBLOQFIL)/100);  
              ELSE  
                  V_VRPRODCONS := ROUND(V_VRORCAMENTO,2);  
              END IF;
              IF ROUND(V_VRPRODCONS,2) < ROUND(C_ITEMAUF.VRPROD,2) THEN  
              
                  FOR C_PRODUTOS IN (  
                     SELECT DISTINCT PRODUTO.CDPRODUTO,DTPROGENTAF  
                       FROM ITEMAUTF,PRODFILI,PRODUTO,AUTOFORNE
                      WHERE ITEMAUTF.CDFILDEST                      = P_CDFILDEST
                        AND ITEMAUTF.CDFILLANC                      = P_CDFILLANC  
                        AND ITEMAUTF.NRAUTOFORN                     = P_NRAUTOFORN  
                        AND TO_CHAR(ITEMAUTF.DTPROGENTAF,'MM/YYYY') = TO_CHAR(C_AUTOFORNE.DTPROGENTAF,'MM/YYYY')  
                        AND ITEMAUTF.CDPRODUTO                      = PRODFILI.CDPRODUTO  
                        AND ITEMAUTF.CDFILDEST                      = PRODFILI.CDFILIAL
                        AND ITEMAUTF.CDPRODUTO                      = PRODUTO.CDPRODUTO  
                        AND ITEMAUTF.CDFILDEST                      = AUTOFORNE.CDFILDEST  
                        AND ITEMAUTF.CDFILLANC                      = AUTOFORNE.CDFILLANC  
                        AND ITEMAUTF.NRAUTOFORN                     = AUTOFORNE.NRAUTOFORN
                        AND NVL(AUTOFORNE.IDBAIXAAF, '1') NOT IN ('4','5')
                        AND PRODFILI.IDCNTRESTOQ                    = 'S'  
                        AND AUTOFORNE.IDSITUATUAAF IS NULL) LOOP  
                      BEGIN  
                         SELECT LPAD(TO_CHAR( MAX(TO_NUMBER(NRSEQERCONSORCA)) + 1),6,'0')  
                           INTO V_NRSEQ
                           FROM ERROCONSORCA  
                          WHERE CDFILDEST  = P_CDFILDEST  
                            AND CDFILLANC  = P_CDFILLANC  
                            AND NRAUTOFORN = P_NRAUTOFORN  
                            AND CDPRODUTO  = C_PRODUTOS.CDPRODUTO;
                      EXCEPTION  
                        WHEN NO_DATA_FOUND THEN  
                          V_NRSEQ := '000001';  
                      END;  
                      IF V_NRSEQ IS NULL THEN
                         V_NRSEQ := '000001';  
                      END IF;  
                      INSERT INTO ERROCONSORCA  
                        (CDFILDEST,CDFILLANC,NRAUTOFORN,NRSEQERCONSORCA,CDPRODUTO,DSERROCONSORCA,DTPROGENTAF)  
                      VALUES
                        (P_CDFILDEST,P_CDFILLANC,P_NRAUTOFORN,V_NRSEQ,C_PRODUTOS.CDPRODUTO,'Oramento da filial para consumo ultrapassado. Valor Orado:'||  
                         REPLACE(TO_CHAR(ROUND(V_VRORCAMENTO,2)),'.',',')||'.'||'Valor Realizado com a AF: '||REPLACE(TO_CHAR(ROUND(C_ITEMAUF.VRPROD,2)),'.',',')||'.',C_AUTOFORNE.DTPROGENTAF);  
                      P_RETORNO := 'N';  
                  END LOOP; 
              END IF;
           END LOOP; 
           /*Inicio Despesas*/
           /*Retorna todos os produtos vinculados a AF que esta sendo validada*/
           FOR C_PRODUTOS IN(
                SELECT PRODUTO.CDARVPROD,PRODUTO.CDPRODUTO,ITEMAUTF.DTPROGENTAF
                 FROM ITEMAUTF,PRODFILI,PRODUTO,AUTOFORNE
                WHERE ITEMAUTF.CDFILDEST   = P_CDFILDEST 
                  AND ITEMAUTF.CDFILLANC   = P_CDFILLANC 
                  AND ITEMAUTF.NRAUTOFORN  = P_NRAUTOFORN 
                  AND ITEMAUTF.CDPRODUTO   = PRODFILI.CDPRODUTO 
                  AND ITEMAUTF.CDFILDEST   = PRODFILI.CDFILIAL 
                  AND ITEMAUTF.CDPRODUTO   = PRODUTO.CDPRODUTO 
                  AND ITEMAUTF.CDFILDEST   = AUTOFORNE.CDFILDEST 
                  AND ITEMAUTF.CDFILLANC   = AUTOFORNE.CDFILLANC 
                  AND ITEMAUTF.NRAUTOFORN  = AUTOFORNE.NRAUTOFORN 
                  AND AUTOFORNE.IDSITUATUAAF IS NULL               
           )LOOP 
           /*Para cada produto encontrado na AF deve-se verificar :
            - Prioritariamente se o produto esta vinculado a uma despesa ligada a um ANO,VERSAO e Filial especfiica. Tabelas  PRODDESPPREV -> PRODITEMORCAOP
            - Ou se a despesa por produto possui um vinculo GERAL, independente dos fatores citados acima. Tablelas DESPPREV -> PRODDESPPREV */
              
              /*Prioritario - Vinculo do produto com DESPESA - ANO - VERSAO e FILIAL*/
              BEGIN
                SELECT I.CDDESPPREV,NVL(PROD.CDARVPRODDESPOP,'N')
                  INTO V_CDDESPPREV, V_CDARVPROD   
                  FROM ITEMORCAOP I,
                       PRODUTO,
                       PRODITEMORCAOP PROD
                 WHERE I.CDFILIAL        = P_CDFILDEST 
                   AND I.NRANOORCAOP     = TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'YYYY')
                   AND I.CDVERSORCAOP    = V_MAXVERSAO 
                   AND I.CDFILIAL        = PROD.CDFILIAL
                   AND I.NRANOORCAOP     = PROD.NRANOORCAOP
                   AND I.CDVERSORCAOP    = PROD.CDVERSORCAOP
                   AND I.NRSEQITEMORCA   = PROD.NRSEQITEMORCA
                   AND PRODUTO.CDPRODUTO = PROD.CDPRODUTO
                   AND PROD.CDPRODUTO    = C_PRODUTOS.CDPRODUTO;
              EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    V_CDDESPPREV := 'N';
                    V_CDARVPROD  := 'N';                    
                END;
                
              /*Opcao geral-Caso nao haja resultado no select prioritario, o vinclo de despesa por produto sera geral */
              IF V_CDARVPROD = 'N' THEN
                BEGIN
                 SELECT D.CDDESPPREV,PROD.CDPRODUTO
                   INTO V_CDDESPPREV,V_CDARVPROD
                   FROM DESPPREV D,PRODDESPPREV PROD,PRODUTO P
                  WHERE D.CDDESPPREV  = PROD.CDDESPPREV 
                    AND P.CDPRODUTO   = C_PRODUTOS.CDPRODUTO
                    AND D.IDATIVO     = 'S'
                    AND P.CDARVPROD LIKE PROD.CDPRODUTO||'%';
                EXCEPTION 
                  WHEN NO_DATA_FOUND THEN
                    V_CDDESPPREV := 'N';
                    V_CDARVPROD  := 'N';
                  END;
              END IF;
              
              IF V_CDARVPROD <> 'N' THEN
                BEGIN
                  SELECT SUM((NVL(NVL(VRPREUNIAF,0),0) * NVL(QTPROGENTAF,0))) AS VRPROD
                    INTO V_VRPROD
                    FROM ITEMAUTF,PRODFILI,AUTOFORNE,PRODUTO
                   WHERE ITEMAUTF.CDFILDEST                      = P_CDFILDEST  
                     AND ITEMAUTF.CDFILLANC                      = P_CDFILLANC  
                     AND ITEMAUTF.CDPRODUTO                      = PRODFILI.CDPRODUTO  
                     AND ITEMAUTF.CDFILDEST                      = PRODFILI.CDFILIAL  
                     AND ITEMAUTF.CDFILDEST                      = AUTOFORNE.CDFILDEST
                     AND ITEMAUTF.CDFILLANC                      = AUTOFORNE.CDFILLANC  
                     AND ITEMAUTF.NRAUTOFORN                     = AUTOFORNE.NRAUTOFORN
                     AND NVL(AUTOFORNE.IDBAIXAAF, '1') NOT IN ('4','5')
                     AND TO_CHAR(ITEMAUTF.DTPROGENTAF,'MM/YYYY') = TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM/YYYY') 
                     AND PRODUTO.CDPRODUTO                       = ITEMAUTF.CDPRODUTO 
                     AND PRODUTO.CDARVPROD                       LIKE V_CDARVPROD||'%' 
                     AND ((AUTOFORNE.IDSITUATUAAF IS NULL AND IDAFFORALIM = 'N') OR (AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN));
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                       V_VRPROD := 0;       
                END;
                
                FOR C_ITEMAUF IN (
                  SELECT PRODUTO.CDARVPROD AS CDPRODUTO 
                    FROM ITEMAUTF,PRODFILI,AUTOFORNE,PRODUTO
                   WHERE ITEMAUTF.CDFILDEST                      = P_CDFILDEST  
                     AND ITEMAUTF.CDFILLANC                      = P_CDFILLANC  
                     AND ITEMAUTF.CDPRODUTO                      = PRODFILI.CDPRODUTO  
                     AND ITEMAUTF.CDFILDEST                      = PRODFILI.CDFILIAL  
                     AND ITEMAUTF.CDFILDEST                      = AUTOFORNE.CDFILDEST
                     AND ITEMAUTF.CDFILLANC                      = AUTOFORNE.CDFILLANC  
                     AND ITEMAUTF.NRAUTOFORN                     = AUTOFORNE.NRAUTOFORN
                     AND NVL(AUTOFORNE.IDBAIXAAF,'1') NOT IN ('4','5')
                     AND TO_CHAR(ITEMAUTF.DTPROGENTAF,'MM/YYYY') = TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM/YYYY') 
                     AND PRODUTO.CDPRODUTO                       = ITEMAUTF.CDPRODUTO 
                     AND PRODUTO.CDARVPROD                       LIKE V_CDARVPROD||'%' 
                     AND ((AUTOFORNE.IDSITUATUAAF IS NULL AND IDAFFORALIM = 'N') OR (AUTOFORNE.NRAUTOFORN = P_NRAUTOFORN)) 
                )LOOP  
                  BEGIN    
                    SELECT SUM(NVL(DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'01', VRORCAMENTOJAN, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'02', VRORCAMENTOFEV, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'03', VRORCAMENTOMAR, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'04', VRORCAMENTOABR, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'05', VRORCAMENTOMAI, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'06', VRORCAMENTOJUN, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'07', VRORCAMENTOJUL, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'08', VRORCAMENTOAGO, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'09', VRORCAMENTOSET, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'10', VRORCAMENTOOUT, 
                                   DECODE(TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'MM'),'11', VRORCAMENTONOV,
                                   VRORCAMENTODEZ))))))))))),0)) VRORCAMENTOE,IDITEMORCAMENTO ,'SIM',IDTIPOORCAOP 
                                            INTO V_VRORCAMENTO,V_IDITEMORCAMENTO,V_EXISTEORCAMENTO,V_IDTIPOORCAOP 
                                            FROM ITEMORCAOP,DESPPREV
                                           WHERE CDFILIAL        = P_CDFILDEST 
                                             AND NRANOORCAOP     = TO_CHAR(TO_DATE(C_AUTOFORNE.DTPROGENTAF),'YYYY') 
                                             AND CDVERSORCAOP    = V_MAXVERSAO 
                                             AND IDITEMORCAMENTO = 'A'
                                             AND ITEMORCAOP.CDDESPPREV = DESPPREV.CDDESPPREV
                                             AND ITEMORCAOP.CDDESPPREV = V_CDDESPPREV
                                          GROUP BY IDITEMORCAMENTO,IDTIPOORCAOP;
                  EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                       V_VRORCAMENTO     := 0;
                       V_EXISTEORCAMENTO := 'NAO';
                       V_IDTIPOORCAOP    := 'E';
                  END; 
                  
                  IF V_IDTIPOORCAOP = 'E' THEN  
                    IF C_FILIAL.VRPEORCBLOQFIL <> 0 THEN  
                      V_VRPRODDESP := ROUND(V_VRORCAMENTO,2) + ((ROUND(V_VRORCAMENTO,2)*C_FILIAL.VRPEORCBLOQFIL)/100);  
                    ELSE
                      V_VRPRODDESP := ROUND(V_VRORCAMENTO,2);  
                    END IF;
                    
                    IF ((ROUND(V_VRPRODDESP,2) < ROUND(NVL(V_VRPROD,0),2))) THEN                     
                      FOR C_PRODUTOAF IN(  
                          SELECT DISTINCT PRODUTO.CDPRODUTO, ITEMAUTF.DTPROGENTAF
                            FROM ITEMAUTF,PRODFILI,PRODUTO,AUTOFORNE
                           WHERE ITEMAUTF.CDFILDEST   = P_CDFILDEST  
                             AND ITEMAUTF.CDFILLANC   = P_CDFILLANC  
                             AND ITEMAUTF.NRAUTOFORN  = P_NRAUTOFORN  
                             AND ITEMAUTF.CDPRODUTO   = PRODFILI.CDPRODUTO
                             AND ITEMAUTF.CDFILDEST   = PRODFILI.CDFILIAL  
                             AND ITEMAUTF.CDPRODUTO   = PRODUTO.CDPRODUTO  
                             AND ITEMAUTF.CDFILDEST   = AUTOFORNE.CDFILDEST  
                             AND ITEMAUTF.CDFILLANC   = AUTOFORNE.CDFILLANC  
                             AND ITEMAUTF.NRAUTOFORN  = AUTOFORNE.NRAUTOFORN
                             AND NVL(AUTOFORNE.IDBAIXAAF, '1') NOT IN ('4','5')
                             AND PRODUTO.CDARVPROD    = C_PRODUTOS.CDARVPROD
                             AND AUTOFORNE.IDSITUATUAAF IS NULL) LOOP  
                          BEGIN  
                            SELECT LPAD(TO_CHAR(MAX(TO_NUMBER(NRSEQERCONSORCA)) + 1),6,'0')
                              INTO V_NRSEQ  
                              FROM ERROCONSORCA  
                             WHERE CDFILDEST  = P_CDFILDEST  
                               AND CDFILLANC  = P_CDFILLANC  
                               AND NRAUTOFORN = P_NRAUTOFORN
                               AND CDPRODUTO  = C_PRODUTOAF.CDPRODUTO;  
                          EXCEPTION  
                             WHEN NO_DATA_FOUND THEN  
                               V_NRSEQ := '000001';  
                          END;
                          IF V_NRSEQ IS NULL THEN  
                             V_NRSEQ := '000001';  
                          END IF; 
                          
                          V_DESCERRO :='Oramento da filial para despesa ultrapassado. Valor Orado: '||ROUND(V_VRORCAMENTO,2)||'.'||'Valor Realizado com a AF: '||ROUND(V_VRPROD,2)||'.';
                          INSERT INTO ERROCONSORCA  
                            (CDFILDEST,CDFILLANC,NRAUTOFORN,NRSEQERCONSORCA,CDPRODUTO,DTPROGENTAF,DSERROCONSORCA)
                          VALUES  
                            (P_CDFILDEST,P_CDFILLANC,P_NRAUTOFORN,V_NRSEQ,C_PRODUTOAF.CDPRODUTO,C_PRODUTOAF.DTPROGENTAF,V_DESCERRO);  
                          P_RETORNO := 'N';                              
                      END LOOP;                
                    END IF;
                  END IF; 
                END LOOP;
             END IF;
           END LOOP; 
        END LOOP;    
      END IF; 
   END LOOP;  
END;
/