CREATE OR REPLACE FUNCTION ORCAREALPROD_GOP
 (P_CDFILIAL      IN VARCHAR2,
  P_NRLANCTONF    IN VARCHAR2,
  P_CDPRODUTO     IN VARCHAR2 DEFAULT NULL) RETURN ORCAREALPROD_GOP_TABLE PIPELINED
IS
  C_ITEMNF SYS_REFCURSOR;
  
  V_VRORCAMENTO         FLOAT(126);
  V_REALIZADO           FLOAT(126);
  V_VRSOLICITADOAF      FLOAT(126);
  V_VRENTRADANF         FLOAT(126);
  
  C_ITEMNF_DTORCAMENTO  DATE;
  C_ITEMNF_VRORCAMENTO  FLOAT(126);
  C_ITEMNF_IDTIPOORCAOP VARCHAR2(30);
  C_ITEMNF_CDPRODUTO    VARCHAR2(10);
  C_ITEMNF_CDARVPROD    VARCHAR2(20);
  C_ITEMNF_NMPRODUTO    VARCHAR2(50);
  C_ITEMNF_DTENTRSAID   DATE;
  C_ITEMNF_CDFILIRECE   VARCHAR2(04);
  C_ITEMNF_CDCONTCTBL   VARCHAR2(20);
  C_ITEMNF_VRTOTAITEM   FLOAT(126);
  C_ITEMNF_NRORG        NUMBER;
  V_CDFILISUBGR        VARCHAR2(04);
  V_CDGRUPPROD         VARCHAR2(10);

  W_N              CHAR(1):= 'N';
  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_NEXT           VARCHAR2(01);
  
  OUT_RECORD ORCAREALPROD_GOP_TYPE := ORCAREALPROD_GOP_TYPE(NULL, NULL, NULL, NULL, NULL, NULL,
                                                            NULL, NULL, NULL, NULL, NULL);
BEGIN
   SELECT CASE WHEN COUNT(1) = 0 THEN 'N' ELSE 'S' END
     INTO V_NEXT
     FROM ORCAMENTOGOP
    WHERE IDVERSORCAGOP = 'NEXT'
      AND ROWNUM = 1;
   
   IF V_NEXT = 'N' THEN
      OPEN C_ITEMNF FOR
          SELECT M.DTORCAMENTO, MAX(D.VRORCAMENTO) VRORCAMENTO, I.IDTIPOORCAOP, P.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO,
                ITNF.DTENTRSAID, ITNF.CDFILIRECE, CASE WHEN PO.IDORCDESPDIVERS = 'C' AND I.IDTIPOORCAOP = 'DESPDIVERSA' THEN ITNF.CDCONTCTBL ELSE NULL END CDCONTCTBL,
                SUM(ITNF.VRTOTAITEM) VRTOTAITEM, ITNF.NRORG
           FROM ORCAMENTOGOP O INNER JOIN ITEMORCAGOP I ON I.NRORCAMENTOGOP = O.NRORCAMENTOGOP
                                                       AND I.IDTIPOORCAOP IN ('CONSUMO','DESPDIVERSA')
                               INNER JOIN ITORCAMESGOP M ON M.NRITEMORCAOP = I.NRITEMORCAOP
                               INNER JOIN ITDETALORCAMESGOP D ON D.NRITORCAMESGOP = M.NRITORCAMESGOP
                               CROSS JOIN PARAMORCAOPERAC PO
                               CROSS JOIN (SELECT P.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO, N.DTENTRSAID, N.CDFILIRECE, P.NRORG, CTBL_PRODUTO(P.CDPRODUTO,'E') CDCONTCTBL, II.VRTOTAITEM
                                             FROM NOTAFISCAL N INNER JOIN ITEMPRODUTO II ON N.CDFILIAL = II.CDFILIAL
                                                                                        AND N.NRLANCTONF = II.NRLANCTONF
                                                               INNER JOIN PRODUTO P ON II.CDPRODUTO = P.CDPRODUTO
                                            WHERE N.CDFILIAL = P_CDFILIAL
                                              AND N.NRLANCTONF = P_NRLANCTONF
                                              AND II.CDPRODUTO = NVL(P_CDPRODUTO,II.CDPRODUTO)) ITNF
                               INNER JOIN PRODUTO P ON D.CDPRODUTO = P.CDPRODUTO
                                                   AND ITNF.CDARVPROD LIKE P.CDARVPROD||'%'
                                                   AND P.CDARVPROD IS NOT NULL
                                                   AND PO.IDORCDESPDIVERS = 'P'
          WHERE O.CDFILIAL = ITNF.CDFILIRECE
            AND O.NRANOORCAGOP = TO_CHAR(ITNF.DTENTRSAID,'YYYY')
            AND ITNF.DTENTRSAID BETWEEN M.DTORCAMENTO AND ADD_MONTHS(M.DTORCAMENTO,1)-1
            AND ((ITNF.CDCONTCTBL LIKE D.CDCONTCTBL||'%' AND PO.IDORCDESPDIVERS = 'C'
               AND (NOT EXISTS (SELECT PPR.CDFILIAL
                                  FROM PRODUTOROP PPR, PRODUTO PP
                                 WHERE PPR.IDTPPRODUTOROP = 'CO'
                                   AND PPR.CDFILIAL = TIPOPARAM_DRO(ITNF.CDFILIRECE)
                                   AND PPR.CDPRODUTO = PP.CDPRODUTO
                                   AND P.CDARVPROD LIKE PP.CDARVPROD||'%')
                     OR EXISTS (SELECT PPR.CDFILIAL
                                  FROM PRODUTOROP PPR, PRODUTO PP
                                 WHERE PPR.IDTPPRODUTOROP = 'OX'
                                   AND PPR.CDFILIAL = TIPOPARAM_DRO(ITNF.CDFILIRECE)
                                   AND PPR.CDPRODUTO = PP.CDPRODUTO
                                   AND P.CDARVPROD LIKE PP.CDARVPROD||'%'))) OR (PO.IDORCDESPDIVERS = 'P'))
          GROUP BY M.DTORCAMENTO, I.IDTIPOORCAOP, P.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO,
                ITNF.DTENTRSAID, ITNF.CDFILIRECE, CASE WHEN PO.IDORCDESPDIVERS = 'C' AND I.IDTIPOORCAOP = 'DESPDIVERSA' THEN ITNF.CDCONTCTBL ELSE NULL END,
                ITNF.NRORG;
   ELSE
      /* NEXT */
      OPEN C_ITEMNF FOR
         /* PRODUTO */
         SELECT M.DTORCAMENTO, MAX(D.VRORCAMENTO) VRORCAMENTO, P.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO,
                N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL,
                SUM(II.VRTOTAITEM) VRTOTAITEM, I.IDTIPOORCAOP, N.NRORG
           FROM HISTORCAMENTOGOP O INNER JOIN HISTITEMORCAGOP I ON I.NRHISTORCAMENTOGOP = O.NRHISTORCAMENTOGOP
                                                               AND I.IDTIPOORCAOP IN ('CONSUMO','DESPDIVERSA')
                                                               AND I.NRVERSAO = VERSAOORCA_GOP(I.NRHISTORCAMENTOGOP, I.IDTIPOORCAOP)
                                   INNER JOIN HISTITORCAMESGOP M ON M.NRHISTITEMORCAGOP = I.NRHISTITEMORCAGOP
                                   INNER JOIN HISTITDETALORCAMESGOP D ON D.NRHISTITORCAMESGOP = M.NRHISTITORCAMESGOP
                                   INNER JOIN NOTAFISCAL N ON O.CDFILIAL = N.CDFILIRECE
                                                          AND O.NRANOORCAGOP = TO_CHAR(N.DTENTRSAID,'YYYY')
                                   INNER JOIN ITEMPRODUTO II ON II.CDFILIAL = N.CDFILIAL
                                                            AND II.NRLANCTONF = N.NRLANCTONF
                                                            AND (P_CDPRODUTO IS NULL OR II.CDPRODUTO = P_CDPRODUTO)
                                   INNER JOIN PRODUTO PP ON II.CDPRODUTO = PP.CDPRODUTO
                                   INNER JOIN PRODUTO P ON D.CDPRODUTO = P.CDPRODUTO
                                                       AND PP.CDARVPROD LIKE P.CDARVPROD||'%'
                                                       AND P.CDARVPROD IS NOT NULL
          WHERE N.CDFILIAL = P_CDFILIAL
            AND N.NRLANCTONF = P_NRLANCTONF
            AND D.CDPRODUTO IS NOT NULL
            AND N.DTENTRSAID BETWEEN M.DTORCAMENTO AND ADD_MONTHS(M.DTORCAMENTO,1)-1
          GROUP BY M.DTORCAMENTO, I.IDTIPOORCAOP, P.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO,
                N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL,
                N.NRORG
          UNION
         /* CONTA CONTABIL */
         SELECT M.DTORCAMENTO, MAX(D.VRORCAMENTO) VRORCAMENTO, PP.CDPRODUTO, PP.CDARVPROD, PP.NMPRODUTO,
                N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL, SUM(II.VRTOTAITEM) VRTOTAITEM, I.IDTIPOORCAOP, N.NRORG
           FROM HISTORCAMENTOGOP O INNER JOIN HISTITEMORCAGOP I ON I.NRHISTORCAMENTOGOP = O.NRHISTORCAMENTOGOP
                                                               AND I.IDTIPOORCAOP IN ('CONSUMO','DESPDIVERSA')
                                                               AND I.NRVERSAO = VERSAOORCA_GOP(I.NRHISTORCAMENTOGOP, I.IDTIPOORCAOP)
                                   INNER JOIN HISTITORCAMESGOP M ON M.NRHISTITEMORCAGOP = I.NRHISTITEMORCAGOP
                                   INNER JOIN HISTITDETALORCAMESGOP D ON D.NRHISTITORCAMESGOP = M.NRHISTITORCAMESGOP
                                   INNER JOIN NOTAFISCAL N ON O.CDFILIAL = N.CDFILIRECE
                                                          AND O.NRANOORCAGOP = TO_CHAR(N.DTENTRSAID,'YYYY')
                                   INNER JOIN ITEMPRODUTO II ON II.CDFILIAL = N.CDFILIAL
                                                            AND II.NRLANCTONF = N.NRLANCTONF
                                                            AND (P_CDPRODUTO IS NULL OR II.CDPRODUTO = P_CDPRODUTO)
                                   INNER JOIN PRODUTO PP ON II.CDPRODUTO = PP.CDPRODUTO
          WHERE N.CDFILIAL = P_CDFILIAL
            AND N.NRLANCTONF = P_NRLANCTONF
            AND N.DTENTRSAID BETWEEN M.DTORCAMENTO AND ADD_MONTHS(M.DTORCAMENTO,1)-1            
            AND CTBL_PRODUTO(PP.CDPRODUTO,'E') LIKE D.CDCONTCTBL||'%'
            AND D.CDCONTCTBL IS NOT NULL
            AND (NOT EXISTS (SELECT PPR.CDFILIAL
                               FROM PRODUTOROP PPR, PRODUTO PPP
                              WHERE PPR.IDTPPRODUTOROP = 'CO'
                                AND PPR.CDFILIAL = TIPOPARAM_DRO(N.CDFILIRECE)
                                AND PPR.CDPRODUTO = PPP.CDPRODUTO
                                AND PP.CDARVPROD LIKE PPP.CDARVPROD||'%')
                  OR EXISTS (SELECT PPR.CDFILIAL
                               FROM PRODUTOROP PPR, PRODUTO PPP
                              WHERE PPR.IDTPPRODUTOROP = 'OX'
                                AND PPR.CDFILIAL = TIPOPARAM_DRO(N.CDFILIRECE)
                                AND PPR.CDPRODUTO = PPP.CDPRODUTO
                                AND PP.CDARVPROD LIKE PPP.CDARVPROD||'%'))
          GROUP BY M.DTORCAMENTO, I.IDTIPOORCAOP, PP.CDPRODUTO, PP.CDARVPROD, PP.NMPRODUTO,
                N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL, N.NRORG
          UNION
         /* CONTA A PAGAR */
         SELECT M.DTORCAMENTO, MAX(D.VRORCAMENTO) AS VRORCAMENTO, PP.CDPRODUTO, PP.CDARVPROD, PP.NMPRODUTO,
                N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL, SUM(II.VRTOTAITEM) AS VRTOTAITEM, I.IDTIPOORCAOP, N.NRORG
           FROM HISTORCAMENTOGOP O INNER JOIN HISTITEMORCAGOP I ON I.NRHISTORCAMENTOGOP = O.NRHISTORCAMENTOGOP
                                                               AND I.IDTIPOORCAOP IN ('CONSUMO', 'DESPDIVERSA')
                                                               AND I.NRVERSAO = VERSAOORCA_GOP(I.NRHISTORCAMENTOGOP, I.IDTIPOORCAOP)
                                   INNER JOIN HISTITORCAMESGOP M ON M.NRHISTITEMORCAGOP = I.NRHISTITEMORCAGOP
                                   INNER JOIN HISTITDETALORCAMESGOP D ON D.NRHISTITORCAMESGOP = M.NRHISTITORCAMESGOP
                                   INNER JOIN NOTAFISCAL N ON O.CDFILIAL = N.CDFILIRECE
                                                          AND O.NRANOORCAGOP = TO_CHAR(N.DTENTRSAID, 'YYYY')
                                   INNER JOIN ITEMPRODUTO II ON II.CDFILIAL = N.CDFILIAL
                                                            AND II.NRLANCTONF = N.NRLANCTONF
                                                            AND (P_CDPRODUTO IS NULL OR II.CDPRODUTO = P_CDPRODUTO)
                                   INNER JOIN PRODUTO PP ON II.CDPRODUTO = PP.CDPRODUTO
          WHERE N.CDFILIAL = P_CDFILIAL
            AND N.NRLANCTONF = P_NRLANCTONF
            AND N.DTENTRSAID BETWEEN M.DTORCAMENTO AND ADD_MONTHS(M.DTORCAMENTO, 1) - 1
            AND D.CDTPCTPAGAR IS NOT NULL
            AND (NOT EXISTS (SELECT 1
                               FROM PRODUTOROP PPR INNER JOIN PRODUTO PPP ON PPR.CDPRODUTO = PPP.CDPRODUTO
                              WHERE PPR.IDTPPRODUTOROP = 'CO'
                                AND PPR.CDFILIAL = TIPOPARAM_DRO(N.CDFILIRECE)
                                AND PP.CDARVPROD LIKE PPP.CDARVPROD || '%')
                OR EXISTS (SELECT 1
                             FROM PRODUTOROP PPR INNER JOIN PRODUTO PPP ON PPR.CDPRODUTO = PPP.CDPRODUTO
                            WHERE PPR.IDTPPRODUTOROP = 'OX'
                              AND PPR.CDFILIAL = TIPOPARAM_DRO(N.CDFILIRECE)
                              AND PP.CDARVPROD LIKE PPP.CDARVPROD || '%'))
            AND D.CDTPCTPAGAR = (SELECT X.CDTPCTPAGAR
                                   FROM (SELECT PCP.CDTPCTPAGAR
                                           FROM PRODUTO PCP
                                          WHERE PP.CDARVPROD LIKE PCP.CDARVPROD || '%'
                                            AND PCP.CDTPCTPAGAR IS NOT NULL
                                            AND PCP.CDARVPROD IS NOT NULL
                                          ORDER BY PCP.CDNVPRODUTO DESC) X
                                  WHERE ROWNUM = 1)
          GROUP BY M.DTORCAMENTO, I.IDTIPOORCAOP, PP.CDPRODUTO, PP.CDARVPROD, PP.NMPRODUTO, N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL, I.IDTIPOORCAOP, N.NRORG
          UNION
         /* SUBGRUPO DE PRODUTO */
         SELECT M.DTORCAMENTO, MAX(D.VRORCAMENTO) VRORCAMENTO, PP.CDPRODUTO, PP.CDARVPROD, PP.NMPRODUTO,
                N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL, SUM(II.VRTOTAITEM) VRTOTAITEM, I.IDTIPOORCAOP, N.NRORG
           FROM HISTORCAMENTOGOP O INNER JOIN HISTITEMORCAGOP I ON I.NRHISTORCAMENTOGOP = O.NRHISTORCAMENTOGOP
                                                               AND I.IDTIPOORCAOP IN ('CONSUMO','DESPDIVERSA')
                                                               AND I.NRVERSAO = VERSAOORCA_GOP(I.NRHISTORCAMENTOGOP, I.IDTIPOORCAOP)
                                   INNER JOIN HISTITORCAMESGOP M ON M.NRHISTITEMORCAGOP = I.NRHISTITEMORCAGOP
                                   INNER JOIN HISTITDETALORCAMESGOP D ON D.NRHISTITORCAMESGOP = M.NRHISTITORCAMESGOP
                                   INNER JOIN NOTAFISCAL N ON O.CDFILIAL = N.CDFILIRECE
                                                          AND O.NRANOORCAGOP = TO_CHAR(N.DTENTRSAID,'YYYY')
                                   INNER JOIN ITEMPRODUTO II ON II.CDFILIAL = N.CDFILIAL
                                                            AND II.NRLANCTONF = N.NRLANCTONF
                                                            AND (P_CDPRODUTO IS NULL OR II.CDPRODUTO = P_CDPRODUTO)
                                   INNER JOIN PRODUTO PP ON II.CDPRODUTO = PP.CDPRODUTO
                                   INNER JOIN PRODUTO PSG ON PP.CDARVPROD LIKE PSG.CDARVPROD||'%'
                                   INNER JOIN NVSUBGRPROD NS ON NS.CDPRODUTO = PSG.CDPRODUTO
                                                            AND NS.CDGRUPPROD = D.CDGRUPPROD
                                                            AND NS.CDSUBGRPROD = D.CDSUBGRPROD
                                   INNER JOIN PARFILIALROP PFR ON PFR.CDFILIAL = TIPOPARAM_DRO(N.CDFILIRECE)
                                   LEFT JOIN (SELECT X.CDGRUPPROD, X.CDFILIAL, X.CDFILIPADRAO
                                                FROM (SELECT DISTINCT CDFILISUBGR AS CDFILIAL, '' AS CDFILIPADRAO, CDGRUPPROD
                                                        FROM PRSUBGRPROD
                                                       UNION ALL
                                                      SELECT DISTINCT '' AS CDFILIAL, CDFILISUBGR AS CDFILIPADRAO, CDGRUPPROD
                                                        FROM PRSUBGRPROD) X) SUBGRUPO ON SUBGRUPO.CDGRUPPROD = PFR.CDGRUPPROD 
                                                                                     AND (SUBGRUPO.CDFILIAL = N.CDFILIRECE OR SUBGRUPO.CDFILIPADRAO = 'P')
          WHERE N.CDFILIAL = P_CDFILIAL
            AND N.NRLANCTONF = P_NRLANCTONF
            AND N.DTENTRSAID BETWEEN M.DTORCAMENTO AND ADD_MONTHS(M.DTORCAMENTO,1)-1
            AND NS.CDFILISUBGR = NVL(SUBGRUPO.CDFILIAL, SUBGRUPO.CDFILIPADRAO)
            AND NS.CDGRUPPROD  = PFR.CDGRUPPROD
            AND D.CDSUBGRPROD IS NOT NULL
          GROUP BY M.DTORCAMENTO, I.IDTIPOORCAOP, PP.CDPRODUTO, PP.CDARVPROD, PP.NMPRODUTO,
                   N.DTENTRSAID, N.CDFILIRECE, D.CDCONTCTBL, N.NRORG;
   END IF;
   
   LOOP
      FETCH C_ITEMNF INTO C_ITEMNF_DTORCAMENTO, C_ITEMNF_VRORCAMENTO, C_ITEMNF_IDTIPOORCAOP, C_ITEMNF_CDPRODUTO,
                          C_ITEMNF_CDARVPROD, C_ITEMNF_NMPRODUTO, C_ITEMNF_DTENTRSAID, C_ITEMNF_CDFILIRECE,
                          C_ITEMNF_CDCONTCTBL, C_ITEMNF_VRTOTAITEM, C_ITEMNF_NRORG;
      EXIT WHEN C_ITEMNF%NOTFOUND;
      
      SELECT SUM((NVL(I.VRPREUNIAF,0) * NVL(I.QTPROGENTAF,0)))
        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 NVL(A.IDBAIXAAF,W_1) NOT IN (W_3,W_4,W_5,W_6)
         AND NVL(I.IDBAIXITMAF,W_1) IN (W_1,W_2)
         AND I.DTPROGENTAF  BETWEEN C_ITEMNF_DTORCAMENTO AND ADD_MONTHS(C_ITEMNF_DTORCAMENTO,1)-1
         AND A.CDFILDEST = C_ITEMNF_CDFILIRECE
         AND P.CDARVPROD LIKE C_ITEMNF_CDARVPROD||'%'
         AND A.IDSITUATUAAF IS NULL
         AND IDAFFORALIM = W_N
         AND NVL(A.IDAFEXPIRADA, W_N) = W_N
         AND NVL(A.IDSITAFALCADA, 'PENDENTE') <> 'RECUSADA';

      SELECT NVL(SUM(I.VRTOTAITEM),0)
        INTO V_VRENTRADANF
        FROM NOTAFISCAL N INNER JOIN ITEMPRODUTO I ON N.CDFILIAL = I.CDFILIAL
                                                  AND N.NRLANCTONF = I.NRLANCTONF
                          INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
       WHERE N.CDFILIRECE = C_ITEMNF_CDFILIRECE
         AND N.DTENTRSAID BETWEEN C_ITEMNF_DTORCAMENTO AND ADD_MONTHS(C_ITEMNF_DTORCAMENTO,1)-1
         AND P.CDARVPROD LIKE C_ITEMNF_CDARVPROD||'%'
         AND NOT EXISTS (SELECT 1
                           FROM NFRELACAF NA
                          WHERE NA.CDFILIAL = N.CDFILIAL
                            AND NA.NRLANCTONF = N.NRLANCTONF
                            AND NA.CDPRODUTO = I.CDPRODUTO);

      V_REALIZADO := NVL(V_VRSOLICITADOAF,0) + NVL(V_VRENTRADANF,0);

      OUT_RECORD.CDPRODUTO    := C_ITEMNF_CDPRODUTO;
      OUT_RECORD.CDARVPROD    := C_ITEMNF_CDARVPROD;
      OUT_RECORD.NMPRODUTO    := C_ITEMNF_NMPRODUTO;
      OUT_RECORD.DTORCAMENTO  := C_ITEMNF_DTORCAMENTO;
      OUT_RECORD.VRORCADO     := C_ITEMNF_VRORCAMENTO;
      OUT_RECORD.VRREALIZADO  := V_REALIZADO;
      OUT_RECORD.VRSALDO      := C_ITEMNF_VRORCAMENTO - V_REALIZADO;
      OUT_RECORD.VRTOTAITEM   := C_ITEMNF_VRTOTAITEM;
      OUT_RECORD.VRACUMULADO  := NULL;
      OUT_RECORD.DSCATEGORIA  := C_ITEMNF_IDTIPOORCAOP;
      OUT_RECORD.NRORG        := C_ITEMNF_NRORG;

      PIPE ROW (OUT_RECORD);
   END LOOP;
   
   CLOSE C_ITEMNF;
END;
/