DROP TYPE VALIDA_ORCAMENTO_TABLE
/

DROP TYPE VALIDA_ORCAMENTO_TYPE
/

CREATE TYPE VALIDA_ORCAMENTO_TYPE AS
OBJECT(CDFILDEST          VARCHAR2(4),
       CDFILLANC          VARCHAR2(4),
       NRDEMANDA          VARCHAR2(10),
       IDTPDEMANDA        VARCHAR2(30),
       DTDEMANDA          DATE,
       VRORCAMENTO        FLOAT,
       VRSOLICITADO       FLOAT,
       DSERROCONSORCA     VARCHAR2(200))
/

CREATE TYPE VALIDA_ORCAMENTO_TABLE AS TABLE OF VALIDA_ORCAMENTO_TYPE
/

CREATE OR REPLACE FUNCTION VALIDA_ORCAMENTO(P_CDFILDEST   IN VARCHAR2,
                                            P_CDFILLANC   IN VARCHAR2,
                                            P_NRDEMANDA   IN VARCHAR2
                                            ) RETURN VALIDA_ORCAMENTO_TABLE PIPELINED
IS
  V_VRJASOLICITADOAF   FLOAT;
  V_VRSOLICITADOAF     FLOAT;
  V_VRJASOLICITADOSO   FLOAT;
  V_VRSOLICITADOSO     FLOAT;
  V_VRJASOLICITADOPE   FLOAT;
  V_VRSOLICITADOPE     FLOAT;

  V_NRCONSISTORCAMENTO NUMBER;
  V_VRCUSTOPROD        FLOAT;

  V_DESC               VARCHAR2(200);

  V_DATAINI            DATE;
  V_DATAFIM            DATE;

  V_NRPERIODOORCAGOP   NUMBER;
  V_IDPERIODOORCA      VARCHAR2(30);

  W_N                  CHAR(1):= 'N';
  W_S                  CHAR(1):= 'S';
  W_0                  CHAR(1):= '0';
  W_1                  CHAR(1):= '1';
  W_2                  CHAR(1):= '2';
  W_3                  CHAR(1):= '3';
  W_4                  CHAR(1):= '4';
  W_5                  CHAR(1):= '5';
  W_6                  CHAR(1):= '6';

  V_TIPOITEM           VARCHAR2(100);

  OUT_RECORD VALIDA_ORCAMENTO_TYPE := VALIDA_ORCAMENTO_TYPE (NULL, NULL, NULL,
                                                             NULL, NULL, NULL,
                                                             NULL, NULL);
BEGIN
   FOR C_FILIAL IN (SELECT IDCONSAFORCFIL, VRPEORCBLOQFIL, NVL(P.IDVERIORCFILI,'NAO_VALIDA') IDVERIORCFILI,
                           NVL(F.NRDIAINIORCAGOP,PO.NRDIAINIORCA) DIAINIORCA, NVL(PO.CDTIPOCUSTO,'03') CDTIPOCUSTO
                      FROM FILIAL F, PARASUPRI P, PARAMORCAOPERAC PO
                     WHERE F.CDFILIAL = P_CDFILDEST
                       AND P.CDFILIAL = NVL(F.CDFILCENTCMP, F.CDFILIAL)) LOOP

      FOR C_AUTOFORNE IN (SELECT DTPROGENTAF
                            FROM ITEMAUTF AF
                           WHERE AF.CDFILDEST = P_CDFILDEST
                             AND AF.CDFILLANC = P_CDFILLANC
                             AND AF.NRAUTOFORN = P_NRDEMANDA
                           GROUP BY DTPROGENTAF) LOOP
          
         /* Consumo e Despesa (Entrada) */
         IF C_FILIAL.IDVERIORCFILI IN ('VALIDA_SOL_AF_PED', 'VALIDA_SOL_AF', 'VALIDA_SOL_PED', 'VALIDA_AF_PED', 'VALIDA_SOL', 'VALIDA_PED', 'VALIDA_AF', 'NAO_VALIDA') THEN

            SELECT TO_DATE(CASE WHEN TO_NUMBER(C_FILIAL.DIAINIORCA) > TO_NUMBER(TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD'))
                               THEN TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD')
                               ELSE LPAD(C_FILIAL.DIAINIORCA,2,W_0)
                          END || TO_CHAR(P.DTMES,'MM/YYYY'),'DD/MM/YYYY')
             INTO V_DATAINI
             FROM DATA P
            WHERE C_AUTOFORNE.DTPROGENTAF BETWEEN TO_DATE(CASE WHEN TO_NUMBER(C_FILIAL.DIAINIORCA) > TO_NUMBER(TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD'))
                                              THEN TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD')
                                              ELSE LPAD(C_FILIAL.DIAINIORCA,2,W_0)
                                         END || TO_CHAR(P.DTMES,'MM/YYYY'),'DD/MM/YYYY')
                             AND ADD_MONTHS(TO_DATE(CASE WHEN TO_NUMBER(C_FILIAL.DIAINIORCA) > TO_NUMBER(TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD'))
                                                         THEN TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD')
                                                         ELSE LPAD(C_FILIAL.DIAINIORCA,2,W_0)
                                                    END || TO_CHAR(P.DTMES,'MM/YYYY'),'DD/MM/YYYY'),1)-1
            GROUP BY TO_DATE(CASE WHEN TO_NUMBER(C_FILIAL.DIAINIORCA) > TO_NUMBER(TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD'))
                               THEN TO_CHAR(LAST_DAY(C_AUTOFORNE.DTPROGENTAF),'DD')
                               ELSE LPAD(C_FILIAL.DIAINIORCA,2,W_0)
                          END || TO_CHAR(P.DTMES,'MM/YYYY'),'DD/MM/YYYY');
                          
            V_DATAFIM := ADD_MONTHS(V_DATAINI,1)-1;

            SELECT MAX(NRPERIODO), MAX(IDPERIODOORCA)
              INTO V_NRPERIODOORCAGOP, V_IDPERIODOORCA
              FROM PERIODOORCAGOP P
             WHERE CDFILIAL = 'P'
               AND V_DATAINI BETWEEN TO_DATE(CASE WHEN TO_NUMBER(C_FILIAL.DIAINIORCA) > TO_NUMBER(TO_CHAR(LAST_DAY(V_DATAINI),'DD'))
                                                  THEN TO_CHAR(LAST_DAY(V_DATAINI),'DD')
                                                  ELSE LPAD(C_FILIAL.DIAINIORCA,2,W_0)
                                             END || TO_CHAR(P.DTINIPERIORCA,'MM') || TO_CHAR(V_DATAINI, 'YYYY'),'DD/MM/YYYY')
                              AND ADD_MONTHS(TO_DATE(CASE WHEN TO_NUMBER(C_FILIAL.DIAINIORCA) > TO_NUMBER(TO_CHAR(LAST_DAY(V_DATAINI),'DD'))
                                                          THEN TO_CHAR(LAST_DAY(V_DATAINI),'DD')
                                                          ELSE LPAD(C_FILIAL.DIAINIORCA,2,W_0)
                                                     END || TO_CHAR(P.DTFIMPERIORCA,'MM') || TO_CHAR(V_DATAINI, 'YYYY'),'DD/MM/YYYY'),1)-1;




            FOR C_ITEMAUTF IN (SELECT DISTINCT PRODUTO.CDARVPROD
                                 FROM ITEMAUTF, PRODUTO
                                WHERE ITEMAUTF.CDPRODUTO   = PRODUTO.CDPRODUTO
                                  AND ITEMAUTF.CDFILDEST   = P_CDFILDEST
                                  AND ITEMAUTF.CDFILLANC   = P_CDFILLANC
                                  AND ITEMAUTF.NRAUTOFORN  = P_NRDEMANDA
                                  AND ITEMAUTF.DTPROGENTAF = C_AUTOFORNE.DTPROGENTAF
                                  ORDER BY PRODUTO.CDARVPROD DESC)
            LOOP
 


               FOR C_ORCAMENTO IN (SELECT PROD.CDARVPROD, IM.DTORCAMENTO, IM.VRORCAMENTO VRORCACONSDESP, IDP.VRORCAMENTO VRORCAITEM,
                                          I.IDTIPOORCAOP, I.CDDESPPREV
                                     FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON I.NRORCAMENTOGOP = O.NRORCAMENTOGOP
                                                                                 AND I.IDTIPOORCAOP IN ('CONSUMO','DESPENTRADA','DESPDIVERSA')
                                                                                 AND I.NRORG = O.NRORG
                                                         INNER JOIN APROVAORCAGOP A ON A.NRORCAMENTOGOP = O.NRORCAMENTOGOP
                                                                                   AND A.NRORG = O.NRORG
                                                         INNER JOIN ITORCAMESGOP IM ON IM.NRITEMORCAOP = I.NRITEMORCAOP
                                                                                   AND IM.NRORG = I.NRORG
                                                         INNER JOIN ITDETALORCAMESGOP IDP ON IDP.NRITORCAMESGOP = IM.NRITORCAMESGOP
                                                                                         AND IDP.NRORG = IM.NRORG
                                                         INNER JOIN PRODUTO PROD ON PROD.CDPRODUTO = IDP.CDPRODUTO
                                                                                AND PROD.NRORG = IDP.NRORG
                                    WHERE O.CDFILIAL = P_CDFILDEST
                                      AND O.IDPERIODOORCA = V_IDPERIODOORCA
                                      AND O.NRPERIODOORCA = V_NRPERIODOORCAGOP
                                      AND O.NRANOORCAGOP = TO_CHAR(V_DATAINI,'YYYY')
                               --       AND 'GOP' = 'DRO'
                                      AND C_AUTOFORNE.DTPROGENTAF BETWEEN V_DATAINI AND V_DATAFIM
                                      AND TO_NUMBER(TO_CHAR(V_DATAINI,'MM')) = TO_NUMBER(TO_CHAR(IM.DTORCAMENTO,'MM'))
                                      AND IDP.VRORCAMENTO IS NOT NULL
                                      AND EXISTS (SELECT 1
                                                    FROM ITEMAUTF AF INNER JOIN PRODUTO P ON AF.CDPRODUTO = P.CDPRODUTO
                                                   WHERE AF.CDFILDEST = P_CDFILDEST
                                                     AND AF.CDFILLANC = P_CDFILLANC
                                                     AND AF.NRAUTOFORN = P_NRDEMANDA
                                                     AND P.CDARVPROD LIKE PROD.CDARVPROD||'%'
                                                   UNION
                                                  SELECT 1
                                                    FROM ITEMPEDEXOL I INNER JOIN PRODUTO P ON I.CDPRODUTO = P.CDPRODUTO
                                                   WHERE I.CDFILDEST = P_CDFILDEST
                                                     AND I.CDFILLANC = P_CDFILLANC
                                                     AND I.NRPEDEXOL = P_NRDEMANDA
                                                     AND P.CDARVPROD LIKE PROD.CDARVPROD||'%'
                                                   UNION
                                                  SELECT 1
                                                    FROM ITEMSOLI I INNER JOIN PRODUTO P ON I.CDPRODUTO = P.CDPRODUTO
                                                   WHERE I.CDFILSOLI = P_CDFILDEST
                                                     AND I.CDFILLAN = P_CDFILLANC
                                                     AND I.NRSOLICMP = P_NRDEMANDA
                                                     AND P.CDARVPROD LIKE PROD.CDARVPROD||'%')
                                      AND INSTR(C_ITEMAUTF.CDARVPROD, PROD.CDARVPROD) = 1
                                    ORDER BY PROD.CDARVPROD DESC) LOOP
                  V_VRJASOLICITADOAF := 0;
                  V_VRSOLICITADOAF   := 0;
                  V_VRJASOLICITADOSO := 0;
                  V_VRSOLICITADOSO   := 0;
                  V_VRJASOLICITADOPE := 0;
                  V_VRSOLICITADOPE   := 0;

                  IF C_ORCAMENTO.IDTIPOORCAOP = 'CONSUMO' THEN
                     V_TIPOITEM := 'consumo';
                  ELSE
                     V_TIPOITEM := 'despesa de entrada';
                  END IF;

                  /* Autorização de Fornecimento */
                  IF (C_FILIAL.IDCONSAFORCFIL = W_S) OR C_FILIAL.IDVERIORCFILI IN ('VALIDA_SOL_AF_PED', 'VALIDA_SOL_AF', 'VALIDA_AF_PED', 'VALIDA_AF') THEN
                     SELECT SUM((NVL(I.VRPREUNIAF,0) * NVL(I.QTPROGENTAF,0)))
                       INTO V_VRJASOLICITADOAF
                       FROM AUTOFORNE A INNER JOIN ITEMAUTF I ON A.CDFILDEST = I.CDFILDEST
                                                             AND A.CDFILLANC = I.CDFILLANC
                                                             AND A.NRAUTOFORN = I.NRAUTOFORN
                                        INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                      WHERE NVL(A.IDBAIXAAF,W_1) NOT IN (W_4,W_5)
                        AND NVL(I.IDBAIXITMAF,W_1) IN (W_1,W_2)
                        AND A.CDFILDEST = P_CDFILDEST
                        AND I.DTPROGENTAF BETWEEN V_DATAINI AND V_DATAFIM
                        AND P.CDARVPROD LIKE C_ORCAMENTO.CDARVPROD||'%'
                        AND A.IDSITUATUAAF IS NULL
                        AND IDAFFORALIM = W_N
                        AND NOT EXISTS (SELECT 1
                                          FROM DUAL
                                         WHERE A.CDFILDEST = P_CDFILDEST
                                           AND A.CDFILLANC = P_CDFILLANC
                                           AND A.NRAUTOFORN = P_NRDEMANDA);

                     SELECT SUM((NVL(I.VRPREUNIAF,0) * NVL(I.QTPROGENTAF,0))) VRPRODUTO
                       INTO V_VRSOLICITADOAF
                       FROM AUTOFORNE A INNER JOIN ITEMAUTF I ON A.CDFILDEST = I.CDFILDEST
                                                             AND A.CDFILLANC = I.CDFILLANC
                                                             AND A.NRAUTOFORN = I.NRAUTOFORN
                                        INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                      WHERE A.CDFILDEST = P_CDFILDEST
                        AND A.CDFILLANC = P_CDFILLANC
                        AND A.NRAUTOFORN = P_NRDEMANDA
                        AND I.DTPROGENTAF BETWEEN V_DATAINI AND V_DATAFIM
                        AND P.CDARVPROD LIKE C_ORCAMENTO.CDARVPROD||'%';
                  END IF;

                  /* Pedido */
                  IF C_FILIAL.IDVERIORCFILI IN ('VALIDA_SOL_AF_PED', 'VALIDA_SOL_PED', 'VALIDA_AF_PED', 'VALIDA_PED') THEN
                     SELECT SUM(NVL(I.QTPROGENPEOL,0)*NVL(CUSTO_PRODUTO(E.CDFILDEST, P.CDPRODUTO, C_FILIAL.CDTIPOCUSTO, I.DTPROGENPEOL, SYSDATE),0))
                       INTO V_VRJASOLICITADOPE
                       FROM PEDIDOEXOL E INNER JOIN ITEMPEDEXOL I ON E.CDFILDEST = I.CDFILDEST
                                                                 AND E.CDFILLANC = I.CDFILLANC
                                                                 AND E.NRPEDEXOL = I.NRPEDEXOL
                                         INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                      WHERE E.CDFILDEST = P_CDFILDEST
                        AND (I.IDBAIXENPEOL IS NULL OR I.IDBAIXENPEOL = W_1)
                        AND NVL(I.IDITEMAJUSTE, W_N) = W_N
                        AND NVL(E.IDBAIXEXOL, W_0) NOT IN (W_4, W_5)
                        AND NVL(E.IDBAIXEXOL, W_1) = W_1
                        AND I.DTPROGENPEOL BETWEEN V_DATAINI AND V_DATAFIM
                        AND P.CDARVPROD LIKE C_ORCAMENTO.CDARVPROD||'%'
                        AND I.DTPROGENPEOL IN (SELECT CALECOMP.DTENTREGA
                                                 FROM PERFILPROD, CALECOMP, PRODUTO
                                                WHERE PERFILPROD.CDPRODUTO = PRODUTO.CDPRODUTO
                                                  AND PERFILPROD.CDFILIAL = E.CDFILDEST
                                                  AND CALECOMP.CDFILIAL = PERFILPROD.CDFILIAL
                                                  AND CALECOMP.CDPERIENTR = PERFILPROD.CDPERIENTR
                                                  AND PRODUTO.CDARVPROD = P.CDARVPROD
                                                  AND CALECOMP.DTENTREGA BETWEEN V_DATAINI AND V_DATAFIM)
                        AND NOT EXISTS (SELECT 1
                                          FROM DUAL
                                         WHERE E.CDFILDEST = P_CDFILDEST
                                           AND E.CDFILLANC = P_CDFILLANC
                                           AND E.NRPEDEXOL = P_NRDEMANDA);

                     SELECT SUM(NVL(I.QTPROGENPEOL,0)*NVL(CUSTO_PRODUTO(E.CDFILDEST, P.CDPRODUTO, C_FILIAL.CDTIPOCUSTO, I.DTPROGENPEOL, SYSDATE),0))
                       INTO V_VRSOLICITADOPE
                       FROM PEDIDOEXOL E INNER JOIN ITEMPEDEXOL I ON E.CDFILDEST = I.CDFILDEST
                                                                 AND E.CDFILLANC = I.CDFILLANC
                                                                 AND E.NRPEDEXOL = I.NRPEDEXOL
                                         INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                      WHERE E.CDFILDEST = P_CDFILDEST
                        AND E.CDFILLANC = P_CDFILLANC
                        AND E.NRPEDEXOL = P_NRDEMANDA
                        AND I.DTPROGENPEOL BETWEEN V_DATAINI AND V_DATAFIM
                        AND P.CDARVPROD LIKE C_ORCAMENTO.CDARVPROD||'%';
                  END IF;

                  /* Solicitação */
                  IF C_FILIAL.IDVERIORCFILI IN ('VALIDA_SOL_AF_PED', 'VALIDA_SOL_AF', 'VALIDA_SOL_PED', 'VALIDA_SOL') THEN
                     SELECT SUM(NVL(I.QTSOLIPROD,0)*NVL(CUSTO_PRODUTO(S.CDFILSOLI, P.CDPRODUTO, C_FILIAL.CDTIPOCUSTO, I.DTPROGENTR, SYSDATE),0))
                       INTO V_VRJASOLICITADOSO
                       FROM SOLICITA S INNER JOIN ITEMSOLI I ON S.CDFILLANCA = I.CDFILLAN
                                                            AND S.CDFILSOLI = I.CDFILSOLI
                                                            AND S.NRSOLICMP = I.NRSOLICMP
                                       INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                      WHERE S.CDFILSOLI = P_CDFILDEST
                        AND I.DTPROGENTR BETWEEN V_DATAINI AND V_DATAFIM
                        AND P.CDARVPROD LIKE C_ORCAMENTO.CDARVPROD||'%'
                        AND NVL(S.IDSOLEXTRA, W_N) = W_N
                        AND NOT EXISTS (SELECT 1
                                          FROM DUAL
                                         WHERE S.CDFILSOLI = P_CDFILDEST
                                           AND S.CDFILLANCA = P_CDFILLANC
                                           AND S.NRSOLICMP = P_NRDEMANDA)
                        AND NOT EXISTS (SELECT 1
                                          FROM ITEMSOLI
                                         WHERE ITEMSOLI.DTPROGENCONS = I.DTPROGENTR
                                           AND ITEMSOLI.CDPRODUTCONS = I.CDPRODUTO
                                           AND ITEMSOLI.CDFILSOLCONS = I.CDFILSOLI
                                           AND ITEMSOLI.CDFILLANCONS = I.CDFILLAN
                                           AND ITEMSOLI.NRSOLCMPCONS = I.NRSOLICMP);

                     SELECT SUM(NVL(I.QTSOLIPROD,0)*NVL(CUSTO_PRODUTO(S.CDFILSOLI, P.CDPRODUTO, C_FILIAL.CDTIPOCUSTO, I.DTPROGENTR, SYSDATE),0))
                       INTO V_VRSOLICITADOSO
                       FROM SOLICITA S INNER JOIN ITEMSOLI I ON S.CDFILLANCA = I.CDFILLAN
                                                            AND S.CDFILSOLI = I.CDFILSOLI
                                                            AND S.NRSOLICMP = I.NRSOLICMP
                                       INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                      WHERE S.CDFILSOLI = P_CDFILDEST
                        AND S.CDFILLANCA = P_CDFILLANC
                        AND S.NRSOLICMP = P_NRDEMANDA
                        AND NVL(S.IDSOLEXTRA, W_N) = W_N
                        AND I.DTPROGENTR BETWEEN V_DATAINI AND V_DATAFIM
                        AND P.CDARVPROD LIKE C_ORCAMENTO.CDARVPROD||'%';
                  END IF;

                  V_DESC := NULL;
                  IF (C_ORCAMENTO.VRORCAITEM < (V_VRJASOLICITADOAF+V_VRSOLICITADOAF+V_VRJASOLICITADOPE+V_VRSOLICITADOPE+V_VRJASOLICITADOSO+V_VRSOLICITADOSO)) THEN
                     V_DESC := 'Orçamento do nível '||C_ORCAMENTO.CDARVPROD||' de '||V_TIPOITEM||' foi ultrapassado. Periodo: '||TO_CHAR(C_ORCAMENTO.DTORCAMENTO,'MM/YYYY');
                  END IF;

                  IF V_DESC IS NULL AND
                     (C_ORCAMENTO.VRORCACONSDESP < (V_VRJASOLICITADOAF+V_VRSOLICITADOAF+V_VRJASOLICITADOPE+V_VRSOLICITADOPE+V_VRJASOLICITADOSO+V_VRSOLICITADOSO)) THEN
                     V_DESC := 'Orçamento de '||V_TIPOITEM||' foi ultrapassado. Periodo: '||TO_CHAR(C_ORCAMENTO.DTORCAMENTO,'MM/YYYY');
                  END IF;

                  OUT_RECORD.CDFILDEST      := P_CDFILDEST;
                  OUT_RECORD.CDFILLANC      := P_CDFILLANC;
                  OUT_RECORD.NRDEMANDA      := P_NRDEMANDA;
                  OUT_RECORD.IDTPDEMANDA    := 'AF';
                  OUT_RECORD.DTDEMANDA      := C_ORCAMENTO.DTORCAMENTO;
                  OUT_RECORD.VRORCAMENTO    := C_ORCAMENTO.VRORCAITEM;
                  OUT_RECORD.VRSOLICITADO   := V_VRJASOLICITADOAF;--+V_VRJASOLICITADOPE+V_VRJASOLICITADOSO;
                  OUT_RECORD.DSERROCONSORCA := V_DESC;
                  EXIT;
               END LOOP;
               EXIT;
            END LOOP;
         END IF; 
      END LOOP;
   END LOOP;
   PIPE ROW (OUT_RECORD);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('VALIDA_ORCAMENTO','6.29.xxx'); END;
/