DROP FUNCTION APURCONS_ROP
/

DROP TYPE APURCONS_ROP_TABLE
/

DROP TYPE APURCONS_ROP_TYPE
/

CREATE TYPE APURCONS_ROP_TYPE AS
OBJECT (NRAPURCONS_ROP      NUMBER,
        DSDESCCONS          VARCHAR2(50),
        CDGRUPPROD          VARCHAR2(10),
        NMGRUPPROD          VARCHAR2(40),
        CDSUBGRUPPROD       VARCHAR2(10),
        NMSUBGRPROD         VARCHAR2(100),
        VRESTOQINI          FLOAT(126),
        VRIMPLAEST          FLOAT(126),
        VRENTRCOMP          FLOAT(126),
        VROUTRENTR          FLOAT(126),
        VRCONTRANS          FLOAT(126),
        VRAJUSTES           FLOAT(126),
        VRSAIDDEVO          FLOAT(126),
        VROUTRSAID          FLOAT(126),
        VRRETIAJUS          FLOAT(126),
        VRESTOQFIN          FLOAT(126),
        VRCMV               FLOAT(126),        
        VRCONSUMO           FLOAT(126),
        VRBAIXESTO          FLOAT(126),
        VRDIFCMVCONS        FLOAT(126),        
        NRORG               NUMBER)
/

CREATE TYPE APURCONS_ROP_TABLE AS
TABLE OF APURCONS_ROP_TYPE
/

CREATE OR REPLACE FUNCTION APURCONS_ROP
 (P_CDFILIAL  IN VARCHAR2,
  P_DATAINI   IN DATE,
  P_DATAFIN   IN DATE) RETURN APURCONS_ROP_TABLE PIPELINED
IS
  V_CDFILIAL         VARCHAR2(04);
  V_NRORG            NUMBER;

  V_AUX              FLOAT;
  V_SEQ              FLOAT;
  V_VRESTOQINI       FLOAT;
  V_VRIMPLAEST       FLOAT;
  V_VRENTRCOMP       FLOAT;
  V_VROUTRENTR       FLOAT;
  V_VRCONTRANS       FLOAT;
  V_VRAJUSTES        FLOAT;
  V_VRSAIDDEVO       FLOAT;
  V_VROUTRSAID       FLOAT;
  V_VRRETIAJUS       FLOAT;
  V_VRESTOQFIN       FLOAT;
  V_VRCMV            FLOAT;
  V_CONSUMO          FLOAT;
  V_AUX_VRESTOQINI   FLOAT;
  V_AUX_VRIMPLAEST   FLOAT;
  V_AUX_VRENTRCOMP   FLOAT;
  V_AUX_VROUTRENTR   FLOAT;
  V_AUX_VRCONTRANS   FLOAT;
  V_AUX_VRAJUSTES    FLOAT;
  V_AUX_VRSAIDDEVO   FLOAT;
  V_AUX_VROUTRSAID   FLOAT;
  V_AUX_VRRETIAJUS   FLOAT;
  V_AUX_VRESTOQFIN   FLOAT;
  V_AUX_VRCMV        FLOAT;
  V_AUX_CONSUMO      FLOAT;
  V_AUX_SEMCUSTO     FLOAT;
  V_SEMCUSTO         FLOAT;
  V_IDRETIRADAROP    VARCHAR2(01);
  V_AUX_VRRETIPLA    FLOAT;
  V_VRRETIPLA        FLOAT;
  V_DESC             VARCHAR2(50);
  V_VRBAIXESTO       FLOAT;
  V_AUX_VRBAIXESTO   FLOAT;

  OUT_RECORD APURCONS_ROP_TYPE := APURCONS_ROP_TYPE(NULL, NULL, NULL, NULL, NULL, NULL,
                                                    NULL, NULL, NULL, NULL, NULL, NULL,
                                                    NULL, NULL, NULL, NULL, NULL, NULL,
                                                    NULL, NULL, NULL);
BEGIN
   V_CDFILIAL := P_CDFILIAL;

   SELECT MAX(NRORG)
     INTO V_NRORG
     FROM FILIAL
    WHERE CDFILIAL = V_CDFILIAL;

   V_AUX := 0;     
   V_SEQ := 0;
   V_VRESTOQINI := 0;
   V_VRIMPLAEST := 0;
   V_VRENTRCOMP := 0;
   V_VROUTRENTR := 0;
   V_VRCONTRANS := 0;
   V_VRAJUSTES  := 0;
   V_VRSAIDDEVO := 0;
   V_VROUTRSAID := 0;
   V_VRRETIAJUS := 0;
   V_VRESTOQFIN := 0;
   V_VRESTOQFIN := 0;
   V_VRBAIXESTO := 0;
   V_SEMCUSTO := 0;
   V_VRCMV := 0;
   V_CONSUMO := 0;

   FOR C_SUBGRUP IN (SELECT G.CDGRUPPROD, G.NMGRUPPROD, S.CDSUBGRPROD, S.NMSUBGRPROD
                       FROM SUBGRUPPROD S INNER JOIN PARFILIALROP P ON S.CDGRUPPROD = P.CDGRUPPROD
                                          INNER JOIN GRUPPROD G ON P.CDGRUPPROD = G.CDGRUPPROD 
                      WHERE P.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)) LOOP
                      
      V_SEQ := V_SEQ + 1;
      OUT_RECORD.NRAPURCONS_ROP := V_SEQ;
      OUT_RECORD.CDGRUPPROD := C_SUBGRUP.CDGRUPPROD;
      OUT_RECORD.NMGRUPPROD := C_SUBGRUP.NMGRUPPROD;
      OUT_RECORD.CDSUBGRUPPROD := C_SUBGRUP.CDSUBGRPROD;
      OUT_RECORD.NMSUBGRPROD := C_SUBGRUP.NMSUBGRPROD;

      SELECT NVL(SUM(VRITEMLANCROP),0)
        INTO V_AUX_VRESTOQINI
        FROM INVENTARIOROP
       WHERE CDFILIAL = V_CDFILIAL
         AND CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND IDTPLANCROP = 'INV_INICIAL'
         AND DTINVENTARIO = P_DATAINI;

      V_VRESTOQINI := V_VRESTOQINI + V_AUX_VRESTOQINI;
      OUT_RECORD.VRESTOQINI := V_AUX_VRESTOQINI;

      SELECT NVL(SUM(VRITEMLANCROP),0)
        INTO V_AUX
        FROM INVENTARIOROP
       WHERE CDFILIAL = V_CDFILIAL
         AND CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND IDTPLANCROP = 'IMPLANTACAO'
         AND DTINVENTARIO BETWEEN P_DATAINI AND P_DATAFIN;

      V_VRIMPLAEST := NVL(V_VRIMPLAEST,0) + V_AUX;
      OUT_RECORD.VRIMPLAEST := V_AUX;

      SELECT NVL(SUM(C.VRITEMLANCROP),0)
        INTO V_AUX_VRENTRCOMP
        FROM CONSUMOROP C INNER JOIN FILIALROP F ON C.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND C.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND C.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND C.IDTPLANCROP = 'CONSUMO_ENTRADA'
         AND EXISTS (SELECT 'A'
                       FROM TIPOOPERACROP T
                      WHERE C.CDTIPOOPERROP = T.CDTIPOOPER
                        AND T.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)
                        AND T.IDENTRSAIDOP = 'E'
                        AND T.IDTPOPERACROP = 'CC');

      V_VRENTRCOMP := V_VRENTRCOMP + V_AUX_VRENTRCOMP;
      OUT_RECORD.VRENTRCOMP := V_AUX_VRENTRCOMP;

      SELECT NVL(SUM(C.VRITEMLANCROP),0)
        INTO V_AUX_VROUTRENTR
        FROM CONSUMOROP C INNER JOIN FILIALROP F ON C.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND C.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND C.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND C.IDTPLANCROP = 'CONSUMO_ENTRADA'
         AND NOT EXISTS (SELECT 'A'
                           FROM TIPOOPERACROP T
                          WHERE C.CDTIPOOPERROP = T.CDTIPOOPER
                            AND T.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)
                            AND T.IDENTRSAIDOP = 'E'
                            AND T.IDTPOPERACROP = 'CC');

      V_VROUTRENTR := V_VROUTRENTR + V_AUX_VROUTRENTR;
      OUT_RECORD.VROUTRENTR := V_AUX_VROUTRENTR;

      SELECT NVL(SUM(RATEIO_CONHECIMENTO_TRANSPORTE(C.CDFILIALNFROP,C.NRLANCTONFROP,C.CDGRUPPROD,C.CDSUBGRPROD)),0)
        INTO V_AUX_VRCONTRANS
        FROM CONSUMOROP C INNER JOIN FILIALROP F ON C.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND C.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND C.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND C.IDTPLANCROP = 'CONSUMO_ENTRADA';

      V_VRCONTRANS := V_VRCONTRANS + V_AUX_VRCONTRANS;
      OUT_RECORD.VRCONTRANS := V_AUX_VRCONTRANS;

      SELECT NVL(SUM(I.VRITEMLANCROP),0)
        INTO V_AUX_VRAJUSTES
        FROM INVENTARIOROP I
       WHERE I.CDFILIAL = V_CDFILIAL
         AND I.IDTPLANCROP = 'AJUSTE'
         AND I.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND I.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND I.DTINVENTARIO BETWEEN P_DATAINI AND P_DATAFIN;

      V_VRAJUSTES := V_VRAJUSTES + V_AUX_VRAJUSTES;
      OUT_RECORD.VRAJUSTES := V_AUX_VRAJUSTES;

      SELECT NVL(SUM(C.VRITEMLANCROP),0)
        INTO V_AUX_VRSAIDDEVO
        FROM CONSUMOROP C INNER JOIN FILIALROP F ON C.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND C.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND C.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND C.IDTPLANCROP = 'CONSUMO_SAIDA'
         AND EXISTS (SELECT 'A'
                       FROM TIPOOPERACROP T
                      WHERE C.CDTIPOOPERROP = T.CDTIPOOPER
                        AND T.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)
                        AND T.IDENTRSAIDOP = 'S'
                        AND T.IDTPOPERACROP = 'CS');

      V_VRSAIDDEVO := V_VRSAIDDEVO + V_AUX_VRSAIDDEVO;
      OUT_RECORD.VRSAIDDEVO := V_AUX_VRSAIDDEVO;

      SELECT NVL(SUM(C.VRITEMLANCROP),0)
        INTO V_AUX_VROUTRSAID
        FROM CONSUMOROP C INNER JOIN FILIALROP F ON C.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND C.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND C.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND C.IDTPLANCROP = 'CONSUMO_SAIDA'
         AND NOT EXISTS (SELECT 'A'
                           FROM TIPOOPERACROP T
                          WHERE C.CDTIPOOPERROP = T.CDTIPOOPER
                            AND T.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)
                            AND T.IDENTRSAIDOP = 'S'
                            AND T.IDTPOPERACROP = 'CS');

      V_VROUTRSAID := V_VROUTRSAID + V_AUX_VROUTRSAID;
      OUT_RECORD.VROUTRSAID := V_AUX_VROUTRSAID;

      SELECT NVL(SUM(R.VRLANCTOESTQ),0)
        INTO V_AUX_VRRETIAJUS
        FROM RETIRADADRO R INNER JOIN FILIALROP F ON R.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND R.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND R.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND R.IDTPLANCROP = 'RETIRADA_ESTOQ';

      V_VRRETIAJUS := V_VRRETIAJUS + V_AUX_VRRETIAJUS;
      OUT_RECORD.VRRETIAJUS := V_AUX_VRRETIAJUS;

      SELECT NVL(SUM(VRITEMLANCROP),0)
        INTO V_AUX_VRESTOQFIN
        FROM INVENTARIOROP
       WHERE CDFILIAL = V_CDFILIAL
         AND CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND IDTPLANCROP = 'INV_FINAL'
         AND DTINVENTARIO = P_DATAFIN;

      V_VRESTOQFIN := V_VRESTOQFIN + V_AUX_VRESTOQFIN;
      OUT_RECORD.VRESTOQFIN := V_AUX_VRESTOQFIN;

      V_AUX_SEMCUSTO := 0;
      SELECT NVL(SUM(CASE WHEN O.IDENTRSAID = 'E' THEN C.VRITEMLANCROP*(-1)
                          ELSE C.VRITEMLANCROP
                     END),0)
        INTO V_AUX_SEMCUSTO
        FROM CONSUMOROP C INNER JOIN FILIALROP F ON C.NRFILIALROP = F.NRFILIALROP
                          INNER JOIN OPERSEMCUSTOROP O ON C.CDTIPOOPERROP = O.CDTIPOOPER
                                                      AND O.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND C.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND C.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND C.IDTPLANCROP IN ('CONSUMO_SAIDA','CONSUMO_ENTRADA')
         AND ((O.IDENTRSAID = 'S' AND C.IDTPLANCROP = 'CONSUMO_SAIDA') OR (O.IDENTRSAID = 'E' AND C.IDTPLANCROP = 'CONSUMO_ENTRADA'));

      V_SEMCUSTO := V_SEMCUSTO + V_AUX_SEMCUSTO;

      V_AUX_VRCMV := V_AUX_VRESTOQINI + V_AUX_VRENTRCOMP + V_AUX_VROUTRENTR + V_AUX_VRCONTRANS -
                     V_AUX_VRSAIDDEVO - V_AUX_VROUTRSAID - V_AUX_VRESTOQFIN + V_AUX_SEMCUSTO;

      V_VRCMV := V_VRCMV + V_AUX_VRCMV;
      OUT_RECORD.VRCMV := V_AUX_VRCMV;

      SELECT MAX(IDRETIRADAROP)
        INTO V_IDRETIRADAROP
        FROM PARFILIALROP
       WHERE CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL);

      SELECT NVL(SUM(R.VRLANCTOESTQ),0)
        INTO V_AUX_VRRETIPLA
        FROM RETIRADADRO R INNER JOIN FILIALROP F ON R.NRFILIALROP = F.NRFILIALROP
       WHERE F.CDFILIAL = V_CDFILIAL
         AND F.DTFILIALROP BETWEEN P_DATAINI AND P_DATAFIN
         AND R.CDGRUPPROD = C_SUBGRUP.CDGRUPPROD
         AND R.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD
         AND R.IDTPLANCROP = 'RETIRADA_PLA';

      V_VRRETIPLA := V_VRRETIPLA + V_AUX_VRRETIPLA;

      IF V_IDRETIRADAROP = 'C' THEN
         V_DESC := 'Compras NF';
         V_AUX_CONSUMO := V_AUX_VRENTRCOMP + V_AUX_VRCONTRANS + V_AUX_VROUTRENTR - V_AUX_VRSAIDDEVO + V_AUX_SEMCUSTO;
      ELSIF V_IDRETIRADAROP = 'R' THEN
         V_DESC := 'CMV';
         V_AUX_CONSUMO := V_AUX_VRESTOQINI + V_AUX_VRENTRCOMP + V_AUX_VROUTRENTR + V_AUX_VRCONTRANS -
                          V_AUX_VRSAIDDEVO - V_AUX_VROUTRSAID - V_AUX_VRESTOQFIN + V_AUX_SEMCUSTO;
      ELSIF V_IDRETIRADAROP = 'E' THEN
         V_DESC := 'Retiradas Estoque (-)';
         V_AUX_CONSUMO := V_AUX_VRRETIAJUS;
      ELSIF V_IDRETIRADAROP = 'P' THEN
         V_DESC := 'Retiradas Planejamento (-)';
         V_AUX_CONSUMO := V_AUX_VRRETIPLA;
      ELSE
         V_DESC := 'Retiradas Est + Pla (-)';
         V_VRRETIPLA := V_VRRETIPLA + V_AUX_VRRETIAJUS + V_AUX_VRRETIPLA;
         V_AUX_CONSUMO := V_AUX_VRRETIAJUS + V_AUX_VRRETIPLA;
      END IF;

      V_CONSUMO := V_CONSUMO + V_AUX_CONSUMO;
      OUT_RECORD.VRCONSUMO := V_AUX_CONSUMO;

      V_VRBAIXESTO := NVL(V_VRBAIXESTO,0) + NVL(V_AUX_VRRETIAJUS,0) + NVL(V_AUX_VRRETIPLA,0);
      OUT_RECORD.VRBAIXESTO := V_AUX_VRRETIAJUS + V_AUX_VRRETIPLA;

      OUT_RECORD.VRDIFCMVCONS := V_AUX_VRCMV - V_AUX_CONSUMO;
      OUT_RECORD.DSDESCCONS := V_DESC;
      OUT_RECORD.NRORG := V_NRORG;
      PIPE ROW (OUT_RECORD);
   END LOOP;

   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRAPURCONS_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'Valor Total';
   OUT_RECORD.VRESTOQINI := V_VRESTOQINI;
   OUT_RECORD.VRIMPLAEST := V_VRIMPLAEST;
   OUT_RECORD.VRENTRCOMP := V_VRENTRCOMP;
   OUT_RECORD.VROUTRENTR := V_VROUTRENTR;
   OUT_RECORD.VRCONTRANS := V_VRCONTRANS;
   OUT_RECORD.VRAJUSTES := V_VRAJUSTES;
   OUT_RECORD.VRSAIDDEVO := V_VRSAIDDEVO;
   OUT_RECORD.VROUTRSAID := V_VROUTRSAID;
   OUT_RECORD.VRRETIAJUS := V_VRRETIAJUS;
   OUT_RECORD.VRESTOQFIN := V_VRESTOQFIN;
   OUT_RECORD.VRCMV := V_VRCMV;
   OUT_RECORD.VRCONSUMO := V_CONSUMO;
   OUT_RECORD.VRBAIXESTO := V_VRBAIXESTO;
   OUT_RECORD.VRDIFCMVCONS := V_VRCMV - V_CONSUMO;
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);   
END;