DROP FUNCTION ORCAREALPROD_GOP
/

DROP TYPE ORCAREALPROD_GOP_TABLE
/

DROP TYPE ORCAREALPROD_GOP_TYPE
/

CREATE TYPE ORCAREALPROD_GOP_TYPE AS
OBJECT (CDPRODUTO         VARCHAR2(10),
        CDARVPROD         VARCHAR2(20),
        NMPRODUTO         VARCHAR2(50),
        DTORCAMENTO       DATE,
        VRORCADO          FLOAT(126),
        VRREALIZADO       FLOAT(126),
        VRSALDO           FLOAT(126),
        VRTOTAITEM        FLOAT(126),
        VRACUMULADO       FLOAT(126),
        DSCATEGORIA       VARCHAR2(20),
        NRORG             NUMBER)
/

CREATE TYPE ORCAREALPROD_GOP_TABLE AS
TABLE OF ORCAREALPROD_GOP_TYPE
/

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  
  V_VRORCAMENTO    FLOAT(126);
  V_REALIZADO      FLOAT(126);
  V_VRSOLICITADOAF FLOAT(126);
  V_VRENTRADANF    FLOAT(126);

  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';

  OUT_RECORD ORCAREALPROD_GOP_TYPE := ORCAREALPROD_GOP_TYPE(NULL, NULL, NULL, NULL, NULL, NULL,
                                                            NULL, NULL, NULL, NULL, NULL);
BEGIN   
   FOR C_ITEMNF IN (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) LOOP

      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
                                              AND A.NRORG      = I.NRORG
                         INNER JOIN PRODUTO P ON P.CDPRODUTO = I.CDPRODUTO
                                             AND P.NRORG = I.NRORG
       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;
END;
/