DROP FUNCTION TAB_INVENT_STOCK
/

DROP TYPE INV_STOCK
/

CREATE OR REPLACE
       TYPE IT_INV_ST AS OBJECT(Site                  VARCHAR2(4),
                                Nombre                VARCHAR2(30),
                                Fch_del_Invent        DATE,
                                Tipo_de_Costo         VARCHAR2(50),
                                Fch_de_Procesamiento  DATE,
                                Codigo                VARCHAR2(20),
                                Nombre_del            VARCHAR2(50),
                                Lote                  VARCHAR2(20),
                                UN                    VARCHAR2(5),
                                Cant                  FLOAT,
                                Vr_Unitario           FLOAT,
                                Vr_Total              FLOAT)
/

CREATE OR REPLACE TYPE INV_STOCK IS TABLE OF IT_INV_ST
/

CREATE OR REPLACE FUNCTION TAB_INVENT_STOCK(P_PRODINI   VARCHAR2, 
                                            P_PRODFIN   VARCHAR2,
                                            P_DTPOSICAO DATE,
                                            P_TIPOCUSTO VARCHAR2) RETURN INV_STOCK PIPELINED
IS
   v_Site                  VARCHAR2(4);
   v_Nombre                VARCHAR2(30);
   v_Fch_del_Invent        DATE;
   v_Tipo_de_Costo         VARCHAR2(50);
   v_Fch_de_Procesamiento  DATE;
   v_Codigo                VARCHAR2(20);
   v_Nombre_del            VARCHAR2(50);
   v_Lote                  VARCHAR2(20);
   v_UN                    VARCHAR2(5);
   v_Cant                  FLOAT;
   v_Vr_Unitario           FLOAT;
   v_Vr_Total              FLOAT;
   
   OUT_RECORD IT_INV_ST := IT_INV_ST(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
   
   CURSOR C_INVENT_STOCK(C_PRODINI IN VARCHAR2, C_PRODFIN IN VARCHAR2, C_DTPOSICAO IN DATE, C_TIPOCUSTO IN VARCHAR2) IS
      SELECT B.CDFILIAL AS Site, F.NMFILIAL AS Nombre, T.NMTIPOCUSTO AS Tipo_de_Costo,
             P.DTPROCESSA AS Fch_de_Procesamiento, A.CDARVPROD AS Codigo, A.NMPRODUTO AS Nombre_del, B.NRLOTEESTQ AS Lote,
             A.SGUNIDADE AS UN, B.QTESTOQDIA AS Cant,
             NVL(CUSTO_PRODUTO_LCEST(B.CDFILIAL, B.CDPRODUTO, T.CDTIPOCUSTO, NVL(P.DTPROCESSA - 1, TO_DATE(C_DTPOSICAO)), SYSDATE,
                                     B.CDALMOXARIFE, B.CDLOCALESTOQ, B.NRLOTEESTQ, B.NRSUBLOTE),0) AS Vr_Unitario
        FROM POSESTDIA B, FILIAL F, PARAMFILIAL P, TIPOCUSTO T,
             (SELECT D.CDPRODUTO, D.CDARVPROD, D.NMPRODUTO, D.SGUNIDADE
                FROM PRODUTO D
               WHERE D.CDARVPROD BETWEEN C_PRODINI AND  RPAD(C_PRODFIN, 20, 'Z')
                 AND D.CDNVPRODUTO = (SELECT MAX(CDNVPRODUTO) FROM PRODUTO)
                 AND D.CDPRODUTO = D.CDPRODESTO) A       
       WHERE B.CDFILIAL = F.CDFILIAL
         AND P.CDFILIAL = F.CDFILIAL   
         AND ABS(B.QTESTOQDIA) > 0.00001
         AND B.CDPRODUTO  = A.CDPRODUTO
         AND B.DTPOSIESTQ = (SELECT MAX(P.DTPOSIESTQ)
                               FROM POSESTDIA P
                              WHERE P.CDFILIAL     = B.CDFILIAL
                                AND P.CDPRODUTO    = B.CDPRODUTO
                                AND P.CDALMOXARIFE = B.CDALMOXARIFE
                                AND P.CDLOCALESTOQ = B.CDLOCALESTOQ
                                AND P.NRLOTEESTQ   = B.NRLOTEESTQ
                                AND P.NRSUBLOTE    = B.NRSUBLOTE
                                AND P.DTPOSIESTQ   <= NVL(P.DTPROCESSA, TO_DATE(C_DTPOSICAO) + 1))
         AND T.CDTIPOCUSTO = TO_NUMBER(C_TIPOCUSTO)
       ORDER BY B.CDFILIAL, A.CDARVPROD;
BEGIN
   OPEN C_INVENT_STOCK(P_PRODINI, P_PRODFIN, P_DTPOSICAO, P_TIPOCUSTO);
   LOOP
      FETCH C_INVENT_STOCK INTO v_Site, v_Nombre, v_Tipo_de_Costo,
                                v_Fch_de_Procesamiento, v_Codigo, v_Nombre_del, v_Lote,
                                v_UN, v_Cant,
                                v_Vr_Unitario;
      EXIT WHEN C_INVENT_STOCK%NOTFOUND;
      
      v_Fch_del_Invent := P_DTPOSICAO;      
      v_Vr_Total := v_Cant * v_Vr_Unitario;
      
      OUT_RECORD.Site    	           := v_Site;
      OUT_RECORD.Nombre               := v_Nombre;	
      OUT_RECORD.Fch_del_Invent       := v_Fch_del_Invent;
      OUT_RECORD.Tipo_de_Costo        := v_Tipo_de_Costo; 
      OUT_RECORD.Fch_de_Procesamiento := v_Fch_de_Procesamiento;
      OUT_RECORD.Codigo               := v_Codigo;
      OUT_RECORD.Nombre_del           := v_Nombre_del;
      OUT_RECORD.Lote                 := v_Lote;
      OUT_RECORD.UN                   := v_UN;
      OUT_RECORD.Cant                 := v_Cant;
      OUT_RECORD.Vr_Unitario          := v_Vr_Unitario;
      OUT_RECORD.Vr_Total             := v_Vr_Total;
      
      /*grava registro*/
      PIPE ROW (OUT_RECORD);
   END LOOP;
   CLOSE C_INVENT_STOCK;
   
   RETURN;
EXCEPTION
   WHEN OTHERS THEN
      CLOSE C_INVENT_STOCK;
END;
/

DROP TYPE CURVA
/

CREATE OR REPLACE
       TYPE CUR_TYPE AS OBJECT(Site          VARCHAR2(4),
                               Nombre        VARCHAR2(30),
                               Fecha_inicial DATE,
                               Fecha_Final   DATE,
                               Curva         VARCHAR2(10),
                               RUT_CUI       VARCHAR2(20),
                               Proveedor     VARCHAR2(100),
                               Valor_Total   FLOAT,
                               Porcentual    FLOAT)
/

CREATE OR REPLACE TYPE CURVA IS TABLE OF CUR_TYPE
/

CREATE OR REPLACE FUNCTION TAB_CURVA(P_DTINI DATE,
                                     P_DTFIN DATE) RETURN CURVA PIPELINED
IS
   v_Site          VARCHAR2(4);
   v_Nombre        VARCHAR2(30);
   v_Fecha_inicial DATE;
   v_Fecha_Final   DATE;
   v_Curva         VARCHAR2(10);
   v_RUT_CUI       VARCHAR2(20);
   v_Proveedor     VARCHAR2(100);
   v_Valor_Total   FLOAT;
   v_Porcentual    FLOAT;
   
   OUT_RECORD CUR_TYPE := CUR_TYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
   
   V_CURVAA FLOAT;
   V_CURVAB FLOAT;
   V_CURVAC FLOAT;
   
   V_CDFILIAL     VARCHAR2(4);
   V_TOTALFILIAL  FLOAT;
   V_VALOR_CURVA  FLOAT;
   
   CURSOR C_CURVA(C_DTINI IN DATE, C_DTFIN IN DATE) IS
      SELECT FI.CDFILIAL, FI.NMFILIAL, F.NRINSJURFORN, F.NMRAZSOCFORN, SUM(N.VRNOTAFISC) AS TOTALFORN
        FROM LANCTOESTOQ L, NOTAFISCAL N, FORNECEDOR F, FILIAL FI
       WHERE (FI.CDFILIAL  = L.CDFILIMOVI)
         AND (L.CDFILIAL   = N.CDFILIAL)
         AND (L.NRLANCTONF = N.NRLANCTONF)
         AND (F.CDFORNECED = N.CDFORNECED)
         AND (L.IDTPLANCTO IN ('2','E'))
         AND (L.DTLANCESTQ BETWEEN C_DTINI AND C_DTFIN)
       GROUP BY FI.CDFILIAL, FI.NMFILIAL, F.NRINSJURFORN, F.NMRAZSOCFORN
       ORDER BY CDFILIAL, TOTALFORN DESC;
BEGIN
   v_Fecha_inicial := P_DTINI;
   v_Fecha_Final   := P_DTFIN;
   V_CDFILIAL := ' ';
   
   SELECT PG.VRPECURVAA, PG.VRPECURVAB, PG.VRPECURVAC
     INTO V_CURVAA, V_CURVAB, V_CURVAC
     FROM PARAMGERAL PG
    WHERE CDPARGERAL = '0001';
   
   OPEN C_CURVA(v_Fecha_inicial, v_Fecha_Final);
   LOOP
      FETCH C_CURVA INTO v_Site, v_Nombre, v_RUT_CUI, v_Proveedor, v_Valor_Total;
      EXIT WHEN C_CURVA%NOTFOUND;
      
      IF V_CDFILIAL <> v_Site THEN
         V_CDFILIAL := v_Site;
         
         SELECT SUM(N.VRNOTAFISC) TOTAL_GERAL
           INTO V_TOTALFILIAL
           FROM LANCTOESTOQ L, NOTAFISCAL N
          WHERE (L.CDFILIAL   = N.CDFILIAL)
            AND (L.NRLANCTONF = N.NRLANCTONF)
            AND (L.IDTPLANCTO IN ('2','E'))
            AND (L.CDFILIMOVI = V_CDFILIAL)
            AND (L.DTLANCESTQ BETWEEN v_Fecha_inicial AND v_Fecha_Final);
      END IF;
      
      V_VALOR_CURVA := ((v_Valor_Total * 100)/ V_TOTALFILIAL);
      
      IF V_VALOR_CURVA >= V_CURVAA THEN
         V_CURVA := 'Curva A';
      ELSIF (V_VALOR_CURVA < V_CURVAA) AND (V_VALOR_CURVA >= V_CURVAB) THEN
         V_CURVA := 'Curva B';
      ELSE
         V_CURVA := 'Curva C';
      END IF;
      
      OUT_RECORD.Site          := v_Site;
      OUT_RECORD.Nombre        := v_Nombre;
      OUT_RECORD.Fecha_inicial := v_Fecha_inicial;
      OUT_RECORD.Fecha_Final   := v_Fecha_Final;
      OUT_RECORD.Curva         := V_CURVA;
      OUT_RECORD.RUT_CUI       := v_RUT_CUI;
      OUT_RECORD.Proveedor     := v_Proveedor;
      OUT_RECORD.Valor_Total   := v_Valor_Total;
      OUT_RECORD.Porcentual    := V_VALOR_CURVA;
      
      /*grava registro*/
      PIPE ROW (OUT_RECORD);
   END LOOP;
   CLOSE C_CURVA;
   
   RETURN;
EXCEPTION
   WHEN OTHERS THEN
      CLOSE C_CURVA;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('TAB_INVENT_STOCK','6.39.014'); END;
/
