SCHEMA-POS.sql

/*CONFIGURACION DEL ESQUEMA DE LA BASE DE DATOS
PARA EL SISTEMA DE PUNTO DE VENTA 
(EJECUTAR CON SYSTEM O SYS)*/
--CREACION DE LOS TABLESPACES
CREATE TABLESPACE SISTEMA_VENTAS
DATAFILE 'C:\oraclexe\oradata\XE\SISTEMAVENTAS1.DBF'
SIZE 500M;

CREATE TEMPORARY TABLESPACE TEMP_SISTEMA_VENTAS
TEMPFILE 'C:\oraclexe\oradata\XE\TEMPSISTEMAVENTAS1.DBF'
SIZE 50M AUTOEXTEND ON;
--CREACION DE ESQUEMA
CREATE USER ROOT
IDENTIFIED BY "ROOT@1234"
DEFAULT TABLESPACE SISTEMA_VENTAS
TEMPORARY TABLESPACE TEMP_SISTEMA_VENTAS
PROFILE DEFAULT;
--ASIGNACION DE ROLES
GRANT CONNECT TO ROOT WITH ADMIN OPTION;
GRANT RESOURCE TO ROOT WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO ROOT WITH ADMIN OPTION;
GRANT CREATE USER, ALTER USER, DROP USER TO ROOT;
GRANT CREATE ANY DIRECTORY TO ROOT;
GRANT EXECUTE ON UTL_FILE TO ROOT;
GRANT CREATE ROLE TO ROOT;
GRANT CREATE VIEW TO ROOT;
--INICIAR SESION EN ROOT
--CREACION DE TABLAS
CREATE TABLE PERMISOS (
    permisos_id NUMBER,
    acceso_ventas NUMBER(1) NOT NULL,
    acceso_inventario NUMBER(1) NOT NULL,
    acceso_reportes NUMBER(1) NOT NULL,
    acceso_clientes NUMBER(1) NOT NULL,
    acceso_proveedores NUMBER(1) NOT NULL,
    acceso_usuarios NUMBER(1) NOT NULL,
    acceso_configuracion NUMBER(1) NOT NULL,
    CONSTRAINT PK_PERMISOS_ID PRIMARY KEY (permisos_id)
);
CREATE TABLE USUARIO (
    usuario_id NUMBER,
    nombre VARCHAR2(100) NOT NULL,
    fecha_registro DATE NOT NULL,
    disponibilidad NUMBER(1) NOT NULL,
    estado_eliminacion NUMBER(1) NOT NULL,
    permisos_id NUMBER NOT NULL,
    CONSTRAINT PK_USUARIO_ID PRIMARY KEY (usuario_id),
    CONSTRAINT FK_USUARIO_PERMISOS_ID FOREIGN KEY (permisos_id) REFERENCES PERMISOS(permisos_id)
);
CREATE TABLE CLIENTE (
    cliente_id NUMBER,
    nombre VARCHAR2(100) NOT NULL,
    telefono NUMBER(9) NOT NULL,
    correo VARCHAR2(100) NOT NULL,
    fecha_registro DATE NOT NULL,
    estado_eliminacion NUMBER(1) NOT NULL,
    CONSTRAINT PK_CLIENTE_ID PRIMARY KEY (cliente_id)
);
CREATE TABLE VENTA (
    venta_id NUMBER,
    total_impuesto NUMBER(8,2) NOT NULL,
    total_descuento NUMBER(8,2) NOT NULL,
    pago_cliente NUMBER(8,2) NOT NULL,
    fecha_registro DATE NOT NULL,
    cliente_id NUMBER,
    usuario_id NUMBER NOT NULL,
    CONSTRAINT PK_VENTA_ID PRIMARY KEY (venta_id),
    CONSTRAINT FK_VENTA_CLIENTE_ID FOREIGN KEY (cliente_id) REFERENCES CLIENTE(cliente_id),
    CONSTRAINT FK_VENTA_USUARIO_ID FOREIGN KEY (usuario_id) REFERENCES USUARIO(usuario_id)
);

CREATE TABLE DEPARTAMENTO (
    departamento_id NUMBER,
    nombre VARCHAR2(100) NOT NULL,
    fecha_registro DATE NOT NULL,
    estado_eliminacion NUMBER(1) NOT NULL,
    CONSTRAINT PK_DEPARTAMENTO_ID PRIMARY KEY (departamento_id)
);

CREATE TABLE PRODUCTO (
    producto_id NUMBER,
    nombre VARCHAR2(100) NOT NULL,
    precio NUMBER(8,2) NOT NULL,
    costo NUMBER(8,2) NOT NULL,
    stock NUMBER NOT NULL,
    precio_variable NUMBER(1) NOT NULL,
    permitir_descuentos NUMBER(1) NOT NULL,
    visibilidad_en_caja NUMBER(1) NOT NULL,
    fecha_registro DATE NOT NULL,
    estado_eliminacion NUMBER(1) NOT NULL,
    DEPARTAMENTO_ID NUMBER,
    CONSTRAINT PK_PRODUCTO_ID PRIMARY KEY (producto_id),
    CONSTRAINT PK_PRODUCTO_DEPARTAMENTO_ID FOREIGN KEY (DEPARTAMENTO_ID) REFERENCES DEPARTAMENTO(DEPARTAMENTO_ID)
);

CREATE TABLE PROVEEDOR (
    proveedor_id NUMBER,
    razon_social VARCHAR2(100) NOT NULL,
    telefono NUMBER(9) NOT NULL,
    correo VARCHAR2(100) NOT NULL,
    fecha_registro DATE NOT NULL,
    estado_eliminacion NUMBER(1) NOT NULL,
    CONSTRAINT PK_PROVEEDOR_ID PRIMARY KEY (proveedor_id)
);
CREATE TABLE VENTA_PRODUCTO (
    venta_id NUMBER NOT NULL,
    producto_id NUMBER NOT NULL,
    cantidad NUMBER NOT NULL,
    CONSTRAINT FK_VENTA_PRODUCTO_ID PRIMARY KEY (venta_id,producto_id),
    FOREIGN KEY (venta_id) REFERENCES VENTA(venta_id),
    FOREIGN KEY (producto_id) REFERENCES PRODUCTO(producto_id)
);
CREATE TABLE PROVEEDOR_PRODUCTO (
    proveedor_producto_id NUMBER,
    proveedor_id NUMBER NOT NULL,
    producto_id NUMBER NOT NULL,
    cantidad NUMBER NOT NULL,
    fecha_registro DATE NOT NULL,
    CONSTRAINT PK_PROVEEDOR_PRODUCTO_ID PRIMARY KEY (proveedor_producto_id),
    CONSTRAINT FK_PP_PROVEEDOR_ID FOREIGN KEY (proveedor_id) REFERENCES PROVEEDOR(proveedor_id),
    CONSTRAINT FK_PP_PRODUCTO_ID FOREIGN KEY (producto_id) REFERENCES PRODUCTO(producto_id)
);
CREATE TABLE SISTEMA(
    RUC VARCHAR(11),
    NUMERO_TERMINAL NUMBER,
    RAZON_SOCIAL VARCHAR(100),
    TELEFONO NUMBER(9),
    CODIGO_TIENDA VARCHAR(25),
    CIUDAD VARCHAR(45),
    PROVINCIA VARCHAR(45),
    DISTRITO VARCHAR(45),
    DIRECCION VARCHAR(100),
    CODIGO_POSTAL NUMBER
);
--CREACION DE ROLES
--ROL EMPLEADO
CREATE ROLE POS_EMPLEADO;
GRANT CONNECT TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.CLIENTE TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.VENTA TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.DEPARTAMENTO TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.PRODUCTO TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.PROVEEDOR TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.PROVEEDOR_PRODUCTO TO POS_EMPLEADO;
GRANT SELECT, INSERT, UPDATE ON ROOT.VENTA_PRODUCTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_AGREGAR_CLIENTE TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_MODIFICAR_CLIENTE TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_ELIMINAR_CLIENTE TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_AGREGAR_PROVEEDOR TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_MODIFICAR_PROVEEDOR TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_ELIMINAR_PROVEEDOR TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_AGREGAR_PRODUCTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_MODIFICAR_PRODUCTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_ELIMINAR_PRODUCTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_INGRESO_PRODUCTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_AGREGAR_DEPARTAMENTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_MODIFICAR_DEPARTAMENTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_ELIMINAR_DEPARTAMENTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_REGISTRO_VENTA TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.SP_EXPORTAR_VENTAS TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.ROOT_INVENTARIO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.ROOT_PROVEEDOR TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.ROOT_REPORTE TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.TYPE_VENTA_PRODUCTO TO POS_EMPLEADO;
GRANT EXECUTE ON ROOT.TYPE_TABLE_VENTA_PRODUCTO TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_BUSCAR_PRODUCTO TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_BUSQUEDA_VENTA_PRODUCTO TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_PRODUCTOS_EN_DEPARTAMENTO TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_VENTAS_PARA_REPORTE TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_PRODUCTOS_EN_DEP_CAJA TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_TOTAL_DEPARTAMENTOS TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_ULTIMA_ENTREGA TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_TOTAL_PROVEEDORES TO POS_EMPLEADO;
GRANT SELECT ON ROOT.VW_TOTAL_CLIENTES TO POS_EMPLEADO;
--ROL ADMINISTRADOR
CREATE ROLE POS_ADMIN;
GRANT SELECT, INSERT, UPDATE ON ROOT.USUARIO TO POS_ADMIN;
GRANT SELECT, INSERT, UPDATE ON ROOT.PERMISOS TO POS_ADMIN;
GRANT SELECT, INSERT, UPDATE ON ROOT.SISTEMA TO POS_ADMIN;
GRANT SELECT ON ROOT.VW_TOTAL_USUARIOS TO POS_ADMIN;
GRANT EXECUTE ON ROOT.F_OBTENER_USUARIO TO POS_ADMIN;
GRANT EXECUTE ON ROOT.SP_GUARDAR_CONFIGURACION TO POS_ADMIN;
GRANT EXECUTE ON ROOT.SP_AGREGAR_USUARIO TO POS_ADMIN;
GRANT EXECUTE ON ROOT.SP_MODIFICAR_USUARIO TO POS_ADMIN;
GRANT EXECUTE ON ROOT.SP_ELIMINAR_USUARIO TO POS_ADMIN;
GRANT POS_EMPLEADO TO POS_ADMIN;
--AGREGANDO LOS DOS TIPOS DE ROLES SEGUN LOS PERMISOS
--PERMISOS DE EMPLEADO
INSERT ALL
INTO PERMISOS VALUES 
(
100,
1,
1,
0,
1,
1,
0,
0
)
--PERMISOS DE ADMINISTRADOR
INTO PERMISOS VALUES 
(
101,
1,
1,
1,
1,
1,
1,
1
)
SELECT 1 FROM dual;
COMMIT;
-- INICIAR SESION EN ROOT
-- TIPOS 
CREATE OR REPLACE TYPE TYPE_VENTA_PRODUCTO
AS OBJECT
(
PRODUCTO_ID NUMBER,
CANTIDAD NUMBER
);

CREATE OR REPLACE TYPE TYPE_TABLE_VENTA_PRODUCTO 
AS TABLE OF TYPE_VENTA_PRODUCTO;
-- SECUENCIAS
CREATE SEQUENCE SQ_CLIENTE START WITH 1;
CREATE SEQUENCE SQ_PROVEEDOR START WITH 1;
CREATE SEQUENCE SQ_PROVEEDOR_PRODUCTO START WITH 1;
CREATE SEQUENCE SQ_DEPARTAMENTO START WITH 1;
CREATE SEQUENCE SQ_PRODUCTO START WITH 1;
CREATE SEQUENCE SQ_VENTA START WITH 1;
--PAQUETES
--PAQUETE REPORTES

CREATE OR REPLACE PACKAGE ROOT_REPORTE AS
   V_REPORTE_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE;
   V_REPORTE_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE;
   V_VENTA_ID_REPORTE ROOT.VENTA.VENTA_ID%TYPE;
   
   FUNCTION GET_VENTA_ID_REPORTE
    RETURN ROOT.VENTA.VENTA_ID%TYPE;
   
   FUNCTION GET_REPORTE_FECHA_INICIO
    RETURN ROOT.VENTA.FECHA_REGISTRO%TYPE;
   
   FUNCTION GET_REPORTE_FECHA_FIN
    RETURN ROOT.VENTA.FECHA_REGISTRO%TYPE;
   
   FUNCTION F_NUMERO_DE_VENTAS
    (
    P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
    P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
    )
    RETURN NUMBER;
    
    FUNCTION F_VALOR_MERCANCIA
    RETURN ROOT.PRODUCTO.COSTO%TYPE;
    
    FUNCTION F_PROMEDIO_VENTAS_X_DIA
    (
    P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
    P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
    )
    RETURN NUMBER;
    
    FUNCTION F_VALOR_ORDEN_PROMEDIO
    (
    P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
    P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
    )
    RETURN NUMBER;
    
    FUNCTION F_PRODUCTOS_VENDIDOS
    (
    P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
    P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
    )
    RETURN NUMBER;
    
    FUNCTION F_CLIENTES_REGISTRADOS
    (
    P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
    P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
    )
    RETURN NUMBER;
END ROOT_REPORTE;

CREATE OR REPLACE PACKAGE BODY ROOT_REPORTE AS
  
  FUNCTION GET_VENTA_ID_REPORTE
    RETURN ROOT.VENTA.VENTA_ID%TYPE
  IS
  BEGIN
    RETURN V_VENTA_ID_REPORTE;
  END;

  FUNCTION GET_REPORTE_FECHA_INICIO
    RETURN ROOT.VENTA.FECHA_REGISTRO%TYPE
  IS
  BEGIN
    RETURN V_REPORTE_FECHA_INICIO;
  END;
  
  FUNCTION GET_REPORTE_FECHA_FIN
    RETURN ROOT.VENTA.FECHA_REGISTRO%TYPE
  IS
  BEGIN
    RETURN V_REPORTE_FECHA_FIN;
  END;
  
  FUNCTION F_NUMERO_DE_VENTAS
  (
  P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
  P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
  )
  RETURN NUMBER
  IS
   V_NUMERO_VENTAS NUMBER;
  BEGIN
    V_NUMERO_VENTAS:=0;
    
    SELECT 
    COUNT(*)
    INTO V_NUMERO_VENTAS
    FROM ROOT.VENTA V
    WHERE V.FECHA_REGISTRO BETWEEN
    P_FECHA_INICIO AND P_FECHA_FIN+1;
    
    --RETORNANDO NUMERO DE VENTAS
    RETURN V_NUMERO_VENTAS;
  END;
  
  FUNCTION F_VALOR_MERCANCIA
  RETURN ROOT.PRODUCTO.COSTO%TYPE
  IS
   V_VALOR_MERCANCIA ROOT.PRODUCTO.COSTO%TYPE;
   CURSOR C_PRODUCTO_CURSOR IS
   SELECT * FROM ROOT.PRODUCTO;
   C_PRODUCTO_RECORD C_PRODUCTO_CURSOR%ROWTYPE;
  BEGIN
    V_VALOR_MERCANCIA:=0;
    OPEN C_PRODUCTO_CURSOR;
    LOOP
      FETCH C_PRODUCTO_CURSOR INTO C_PRODUCTO_RECORD;
      EXIT WHEN C_PRODUCTO_CURSOR%NOTFOUND;
      V_VALOR_MERCANCIA:=V_VALOR_MERCANCIA+C_PRODUCTO_RECORD.STOCK*C_PRODUCTO_RECORD.COSTO;
    END LOOP;
    --RETORNANDO VALOR DE MERCANCIA
    RETURN V_VALOR_MERCANCIA;
  END;
  
  FUNCTION F_PROMEDIO_VENTAS_X_DIA
  (
  P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
  P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
  )
  RETURN NUMBER
  IS
   V_CANTIDAD_DIAS NUMBER;
   V_NUMERO_VENTA NUMBER;
   V_PROMEDIO_VENTAS NUMBER;
  BEGIN
    V_PROMEDIO_VENTAS:=0;
    --OBTENIENDO NUMERO DE DIAS
    SELECT 
    TRUNC(P_FECHA_FIN+1) - TRUNC(P_FECHA_INICIO)
    INTO V_CANTIDAD_DIAS
    FROM DUAL;
    --OBTENIENDO EL NUMERO DE VENTAS
    SELECT 
    F_NUMERO_DE_VENTAS(P_FECHA_INICIO,P_FECHA_FIN)
    INTO V_NUMERO_VENTA
    FROM DUAL;
    --HALLANDO EL PROMEDIO DE VENTAS
    V_PROMEDIO_VENTAS:=V_NUMERO_VENTA/V_CANTIDAD_DIAS;
    --RETORNANDO PROMEDIO DE VENTAS
    RETURN V_PROMEDIO_VENTAS;
  END;
  
  FUNCTION F_VALOR_ORDEN_PROMEDIO
  (
  P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
  P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
  )
  RETURN NUMBER
  IS
   V_PROMEDIO_ORDEN NUMBER;
  BEGIN
    V_PROMEDIO_ORDEN:=0;
    --INICIALIZANDO VALORES DE FECHA
    ROOT.ROOT_REPORTE.V_REPORTE_FECHA_INICIO:=P_FECHA_INICIO;
    ROOT.ROOT_REPORTE.V_REPORTE_FECHA_FIN:=P_FECHA_FIN+1;
    --HALLANDO EL VALOR DE ORDEN PROMEDIO
    SELECT
    AVG(VPR.VENTA_BRUTA)
    INTO V_PROMEDIO_ORDEN
    FROM ROOT.VW_VENTAS_PARA_REPORTE VPR;
    --RETORNANDO VALOR PROMEDIO DE ORDEN
    RETURN V_PROMEDIO_ORDEN;
  END;
  
  FUNCTION F_PRODUCTOS_VENDIDOS
  (
  P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
  P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
  )
  RETURN NUMBER
  IS
   V_PRODUCTOS_VENDIDOS NUMBER;
  BEGIN
    V_PRODUCTOS_VENDIDOS:=0;
    
    SELECT
    SUM(VP.CANTIDAD)
    INTO V_PRODUCTOS_VENDIDOS
    FROM ROOT.VENTA V
    JOIN ROOT.VENTA_PRODUCTO VP
    ON V.VENTA_ID = VP.VENTA_ID
    WHERE V.FECHA_REGISTRO BETWEEN
    P_FECHA_INICIO AND P_FECHA_FIN+1;
    
    --RETORNANDO NUMERO DE PRODUCTOS VENDIDOS
    RETURN V_PRODUCTOS_VENDIDOS;
  END;
  
  FUNCTION F_CLIENTES_REGISTRADOS
  (
  P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
  P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE
  )
  RETURN NUMBER
  IS
   V_CLIENTES_REGISTRADOS NUMBER;
  BEGIN
    V_CLIENTES_REGISTRADOS:=0;
    
    SELECT
    COUNT(*)
    INTO V_CLIENTES_REGISTRADOS
    FROM ROOT.CLIENTE C
    WHERE C.FECHA_REGISTRO BETWEEN
    P_FECHA_INICIO AND P_FECHA_FIN+1;
    
    --RETORNANDO NUMERO DE CLIENTES REGISTRADOS
    RETURN V_CLIENTES_REGISTRADOS;
  END;
END ROOT_REPORTE;

-- PAQUETE CLIENTES
-- PAQUETE PROVEEDORES
CREATE OR REPLACE PACKAGE ROOT_PROVEEDOR AS
   V_PROVEEDOR_ID_ULT_ENT ROOT.PROVEEDOR.PROVEEDOR_ID%TYPE;
   FUNCTION GET_PROVEEDOR_ID_ULT_ENT
    RETURN ROOT.PROVEEDOR.PROVEEDOR_ID%TYPE;
END ROOT_PROVEEDOR;
CREATE OR REPLACE PACKAGE BODY ROOT_PROVEEDOR AS
  FUNCTION GET_PROVEEDOR_ID_ULT_ENT
    RETURN ROOT.PROVEEDOR.PROVEEDOR_ID%TYPE
  IS
  BEGIN
    RETURN V_PROVEEDOR_ID_ULT_ENT;
  END;
END ROOT_PROVEEDOR;
--PAQUETE VENTAS
CREATE OR REPLACE PACKAGE ROOT_VENTA AS
END ROOT_VENTA;

CREATE OR REPLACE PACKAGE BODY ROOT_VENTA AS
END ROOT_VENTA;
--PAQUETE INVENTARIO
CREATE OR REPLACE PACKAGE ROOT_INVENTARIO AS
    V_DEPARTAMENTO_ID_BUSQUEDA ROOT.DEPARTAMENTO.DEPARTAMENTO_ID%TYPE:=NULL;
    V_PRODUCTO_ID_BUSQUEDA ROOT.PRODUCTO.PRODUCTO_ID%TYPE:=NULL;
    FUNCTION GET_DEPARTAMENTO_ID_BUSQUEDA
    RETURN ROOT.DEPARTAMENTO.DEPARTAMENTO_ID%TYPE;
    FUNCTION GET_PRODUCTO_ID_BUSQUEDA
    RETURN ROOT.PRODUCTO.PRODUCTO_ID%TYPE;
END ROOT_INVENTARIO;

CREATE OR REPLACE PACKAGE BODY ROOT_INVENTARIO AS
    FUNCTION GET_DEPARTAMENTO_ID_BUSQUEDA
    RETURN ROOT.DEPARTAMENTO.DEPARTAMENTO_ID%TYPE
  IS
  BEGIN
    RETURN V_DEPARTAMENTO_ID_BUSQUEDA;
  END;
  
  FUNCTION GET_PRODUCTO_ID_BUSQUEDA
    RETURN ROOT.PRODUCTO.PRODUCTO_ID%TYPE
  IS
  BEGIN
    RETURN V_PRODUCTO_ID_BUSQUEDA;
  END;
END ROOT_INVENTARIO;
-- VISTAS
/*OBJETIVO: ESTA VISTA PERMITE CARGAR LOS USUARIOS
  QUE NO HAN SIDO DESACTIVADOS NI ELIMINADOS POR UN
  ADMINISTRADOR.
  USO: ESTE PROCEDIMIENTO SE UTILIZA AL INICIARSE
  EL SISTEMA Y AL CARGAR LA LISTA DE USUARIOS PARA
  INICIAR SESION.
 */
CREATE OR REPLACE VIEW VW_USUARIOS_ACTIVOS AS 
SELECT
U.USUARIO_ID,
U.NOMBRE,
U.DISPONIBILIDAD,
U.FECHA_REGISTRO
FROM ROOT.USUARIO U
WHERE U.ESTADO_ELIMINACION = 0
AND U.DISPONIBILIDAD = 1;

CREATE OR REPLACE VIEW VW_TOTAL_USUARIOS AS 
SELECT
U.USUARIO_ID,
U.NOMBRE,
U.FECHA_REGISTRO,
U.DISPONIBILIDAD,
U.PERMISOS_ID
FROM ROOT.USUARIO U
WHERE U.ESTADO_ELIMINACION = 0;

CREATE OR REPLACE VIEW VW_TOTAL_CLIENTES AS 
SELECT
C.CLIENTE_ID,
C.NOMBRE,
C.TELEFONO,
C.CORREO,
C.FECHA_REGISTRO
FROM ROOT.CLIENTE C
WHERE C.ESTADO_ELIMINACION = 0;

CREATE OR REPLACE VIEW VW_TOTAL_PROVEEDORES AS 
SELECT
P.PROVEEDOR_ID,
P.RAZON_SOCIAL,
P.TELEFONO,
P.CORREO,
P.FECHA_REGISTRO
FROM ROOT.PROVEEDOR P
WHERE P.ESTADO_ELIMINACION = 0;

CREATE OR REPLACE VIEW VW_TOTAL_DEPARTAMENTOS AS 
SELECT
D.DEPARTAMENTO_ID,
D.FECHA_REGISTRO,
D.NOMBRE,
COUNT(P.PRODUCTO_ID) AS CANTIDAD
FROM ROOT.DEPARTAMENTO D
LEFT JOIN ROOT.PRODUCTO P
ON D.DEPARTAMENTO_ID=P.DEPARTAMENTO_ID
WHERE D.ESTADO_ELIMINACION = 0
AND (P.ESTADO_ELIMINACION IS NULL OR P.ESTADO_ELIMINACION=0)
GROUP BY 
D.DEPARTAMENTO_ID,
D.FECHA_REGISTRO,
D.NOMBRE;

CREATE OR REPLACE VIEW VW_PRODUCTOS_EN_DEPARTAMENTO AS 
SELECT
P.PRODUCTO_ID,
P.NOMBRE,
P.PRECIO,
P.COSTO,
P.STOCK,
P.PRECIO_VARIABLE,
P.PERMITIR_DESCUENTOS,
P.VISIBILIDAD_EN_CAJA,
P.FECHA_REGISTRO
FROM ROOT.PRODUCTO P
LEFT JOIN ROOT.DEPARTAMENTO D
ON P.DEPARTAMENTO_ID=D.DEPARTAMENTO_ID
WHERE 
(ROOT_INVENTARIO.GET_DEPARTAMENTO_ID_BUSQUEDA IS NULL
OR P.DEPARTAMENTO_ID=ROOT_INVENTARIO.GET_DEPARTAMENTO_ID_BUSQUEDA)
AND P.ESTADO_ELIMINACION=0;

CREATE OR REPLACE VIEW VW_VENTAS_PARA_REPORTE AS 
SELECT
V.VENTA_ID,
V.FECHA_REGISTRO ,
SUM(P.PRECIO*VP.CANTIDAD) AS VENTA_BRUTA,
V.TOTAL_IMPUESTO,
V.TOTAL_DESCUENTO,
SUM(P.COSTO) AS TOTAL_COSTO,
V.PAGO_CLIENTE,
(V.PAGO_CLIENTE-SUM(P.PRECIO)) AS CAMBIO,
V.CLIENTE_ID,
V.USUARIO_ID
FROM ROOT.VENTA V
LEFT JOIN ROOT.VENTA_PRODUCTO VP
ON V.VENTA_ID=VP.VENTA_ID
LEFT JOIN ROOT.PRODUCTO P
ON VP.PRODUCTO_ID=P.PRODUCTO_ID
WHERE 
V.FECHA_REGISTRO BETWEEN ROOT.ROOT_REPORTE.GET_REPORTE_FECHA_INICIO
AND (ROOT.ROOT_REPORTE.GET_REPORTE_FECHA_FIN+1)
GROUP BY 
V.VENTA_ID,
V.FECHA_REGISTRO,
V.TOTAL_IMPUESTO,
V.TOTAL_DESCUENTO,
V.PAGO_CLIENTE,
V.CLIENTE_ID,
V.USUARIO_ID;

CREATE OR REPLACE VIEW VW_BUSCAR_PRODUCTO AS 
SELECT
P.PRODUCTO_ID,
P.NOMBRE,
P.PRECIO,
P.COSTO,
P.STOCK,
P.PRECIO_VARIABLE,
P.PERMITIR_DESCUENTOS,
P.VISIBILIDAD_EN_CAJA,
P.FECHA_REGISTRO
FROM ROOT.PRODUCTO P
WHERE 
P.PRODUCTO_ID = ROOT.ROOT_INVENTARIO.GET_PRODUCTO_ID_BUSQUEDA;

CREATE OR REPLACE VIEW VW_PRODUCTOS_EN_DEP_CAJA AS 
SELECT *
FROM VW_PRODUCTOS_EN_DEPARTAMENTO PED
WHERE 
PED.VISIBILIDAD_EN_CAJA=1;

CREATE OR REPLACE VIEW VW_BUSQUEDA_VENTA_PRODUCTO AS 
SELECT 
P.PRODUCTO_ID,
P.NOMBRE,
P.PRECIO,
P.COSTO,
P.STOCK,
P.PRECIO_VARIABLE,
P.PERMITIR_DESCUENTOS,
P.VISIBILIDAD_EN_CAJA,
P.FECHA_REGISTRO,
VP.CANTIDAD
FROM ROOT.VENTA_PRODUCTO VP
JOIN ROOT.PRODUCTO P
ON VP.PRODUCTO_ID=P.PRODUCTO_ID
WHERE 
VP.VENTA_ID = ROOT.ROOT_REPORTE.GET_VENTA_ID_REPORTE;

CREATE OR REPLACE VIEW VW_ULTIMA_ENTREGA AS 
SELECT
PP.PROVEEDOR_ID,
PP.PRODUCTO_ID,
PP.CANTIDAD,
PP.FECHA_REGISTRO
FROM ROOT.PROVEEDOR_PRODUCTO PP
WHERE PP.PROVEEDOR_ID=ROOT_PROVEEDOR.GET_PROVEEDOR_ID_ULT_ENT
AND PP.FECHA_REGISTRO =
(SELECT MAX(PP.FECHA_REGISTRO)
FROM ROOT.PROVEEDOR_PRODUCTO PP
WHERE PP.PROVEEDOR_ID=ROOT_PROVEEDOR.GET_PROVEEDOR_ID_ULT_ENT
);

CREATE OR REPLACE VIEW VW_CONFIGURACION_SISTEMA AS 
SELECT
S.RUC,
S.NUMERO_TERMINAL,
S.RAZON_SOCIAL,
S.TELEFONO,
S.CODIGO_TIENDA,
S.CIUDAD,
S.PROVINCIA,
S.DISTRITO,
S.DIRECCION,
S.CODIGO_POSTAL
FROM ROOT.SISTEMA S
WHERE ROWNUM = 1;
-- PROCEDIMIENTOS
CREATE OR REPLACE PROCEDURE SP_GUARDAR_CONFIGURACION
(
P_RUC IN ROOT.SISTEMA.RUC%TYPE,
P_NUMERO_TERMINAL IN ROOT.SISTEMA.NUMERO_TERMINAL%TYPE,
P_RAZON_SOCIAL IN ROOT.SISTEMA.RAZON_SOCIAL%TYPE,
P_TELEFONO IN ROOT.SISTEMA.TELEFONO%TYPE,
P_CODIGO_TIENDA IN ROOT.SISTEMA.CODIGO_TIENDA%TYPE,
P_CIUDAD IN ROOT.SISTEMA.CIUDAD%TYPE,
P_PROVINCIA IN ROOT.SISTEMA.PROVINCIA%TYPE,
P_DISTRITO IN ROOT.SISTEMA.DISTRITO%TYPE,
P_DIRECCION IN ROOT.SISTEMA.DIRECCION%TYPE,
P_CODIGO_POSTAL IN ROOT.SISTEMA.CODIGO_POSTAL%TYPE
)
AS
  V_CANTIDAD_REGISTROS NUMBER;
BEGIN
  --BUSCANDO SI EXISTE UNA CONFIGURACION PREVIA EN LA TABLA SISTEMA
  SElECT COUNT(*)
  INTO V_CANTIDAD_REGISTROS
  FROM ROOT.SISTEMA;
  IF V_CANTIDAD_REGISTROS>0 THEN
     UPDATE ROOT.SISTEMA S
     SET
     S.RUC = P_RUC,
     S.NUMERO_TERMINAL = P_NUMERO_TERMINAL,
     S.RAZON_SOCIAL = P_RAZON_SOCIAL,
     S.TELEFONO = P_TELEFONO,
     S.CODIGO_TIENDA = P_CODIGO_TIENDA,
     S.CIUDAD = P_CIUDAD,
     S.PROVINCIA = P_PROVINCIA,
     S.DISTRITO = P_DISTRITO,
     S.DIRECCION = P_DIRECCION,
     S.CODIGO_POSTAL = P_CODIGO_POSTAL;
  ELSE
     INSERT INTO ROOT.SISTEMA
     VALUES
     (
      P_RUC,
      P_NUMERO_TERMINAL,
      P_RAZON_SOCIAL,
      P_TELEFONO,
      P_CODIGO_TIENDA,
      P_CIUDAD,
      P_PROVINCIA,
      P_DISTRITO,
      P_DIRECCION,
      P_CODIGO_POSTAL
     );
  END IF;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_AGREGAR_USUARIO
(
P_NOMBRE IN ROOT.USUARIO.NOMBRE%TYPE,
P_PIN IN NUMBER,
P_ROL IN NUMBER
)
AS
V_NOMBRE_USUARIO VARCHAR(20);
V_USUARIO_ID NUMBER;
V_PERMISOS_ID NUMBER;
BEGIN
  /*OBJETIVO: ESTE PROCEDIMIENTO PERMITE CREAR UN
  NUEVO USUARIO/ESQUEMA EN LA BASE DE DATOS.
  EL ROL QUE TENDRA ESTE USUARIO PUEDE SER ADMINISTRADOR
  O EMPLEADO, ESO DEPENDE DEL VALOR QUE TENGA EL PARAMETRO 
  "P_ROL" CUANDO EL PROCEDIMIENTO ES EJECUTADO. ESTE 
  PROCEDIMIENTO UTILIZA EL ID DEL USUARIO CREADO 
  PARA AGREGAR UN NUEVO REGISTRO EN LA TABLA USUARIO DE ROOT.
  USO: ESTE PROCEDIMIENTO SE UTILIZA EN EL MODULO
  DE USUARIOS, AL AGREGAR UN NUEVO USUARIO LUEGO
  DE LLENAR SUS DATOS Y PRESIONAR EL BOTON ACEPTAR.
 */
  --GENERANDO NUEVO NOMBRE DE USUARIO
  SELECT 
  'USER'||COUNT(*)
  INTO V_NOMBRE_USUARIO
  FROM ROOT.USUARIO;
  --CREANDO USUARIO
  EXECUTE IMMEDIATE 
  ' CREATE USER '||V_NOMBRE_USUARIO||
  ' IDENTIFIED BY "'||P_PIN||'" '||
  ' DEFAULT TABLESPACE SISTEMA_VENTAS'||
  ' TEMPORARY TABLESPACE TEMP_SISTEMA_VENTAS'||
  ' PROFILE DEFAULT';
  --ASIGNANDO ROLES A USUARIO DEPENDIENDO DEL ROL
  IF P_ROL>0 THEN
    EXECUTE IMMEDIATE 
  'GRANT POS_ADMIN TO '||V_NOMBRE_USUARIO;
      V_PERMISOS_ID:=101;
  ELSE
    EXECUTE IMMEDIATE 
  'GRANT POS_EMPLEADO TO '||V_NOMBRE_USUARIO;
      V_PERMISOS_ID:=100;
  END IF; 
  --OBTENIENDO ID DE USUARIO CREADO
  SELECT 
  AU.USER_ID
  INTO V_USUARIO_ID
  FROM ALL_USERS AU
  WHERE AU.USERNAME=V_NOMBRE_USUARIO;
  --INSERTANDO USUARIO A TABLA
  INSERT INTO ROOT.USUARIO
  VALUES
  (
  V_USUARIO_ID,
  P_NOMBRE,
  SYSDATE,
  1,
  0,
  V_PERMISOS_ID
  );
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_MODIFICAR_USUARIO
(
P_USUARIO_ID IN ROOT.USUARIO.USUARIO_ID%TYPE,
P_NOMBRE IN ROOT.USUARIO.NOMBRE%TYPE,
P_PIN IN NUMBER,
P_DISPONIBILIDAD IN ROOT.USUARIO.DISPONIBILIDAD%TYPE,
P_ROL IN NUMBER
)
AS
V_PERMISOS_NUEVOS_ID NUMBER;
V_PERMISOS_ANTERIORES_ID NUMBER;
V_NOMBRE_USUARIO VARCHAR(20);
BEGIN
  /*OBJETIVO: ESTE PROCEDIMIENTO PERMITE MODIFICAR
  UN USUARIO EXISTE, TANTO SU ESQUEMA EN LA BD
  COMO SUS DATOS DENTRO DE LA TABLA USUARIO EN ROOT.
  USO: ESTE PROCEDIMIENTO SE UTILIZA EN EL MODULO
  DE USUARIOS, AL MODIFICAR UN USUARIO LUEGO
  DE LLENAR SUS DATOS Y PRESIONAR EL BOTON ACEPTAR.
 */
  --OBTENIENDO NOMBRE DE USUARIO
  V_NOMBRE_USUARIO:=F_OBTENER_USUARIO(P_USUARIO_ID);
  --DEFINIENDO ANTERIORES PERMISOS DEL USUARIO
  SELECT U.PERMISOS_ID
  INTO V_PERMISOS_ANTERIORES_ID
  FROM ROOT.USUARIO U
  WHERE U.USUARIO_ID=P_USUARIO_ID;
  --DEFINIENDO NUEVOS PERMISOS DEL USUARIO
  IF P_ROL>0 THEN
    V_PERMISOS_NUEVOS_ID:=101;
  ELSE
    V_PERMISOS_NUEVOS_ID:=100;
  END IF;
  --MODIFICANDO CONTRASEÑA DE USUARIO
  IF P_PIN != -1 THEN
  EXECUTE IMMEDIATE 
  ' ALTER USER '||V_NOMBRE_USUARIO||
  ' IDENTIFIED BY "'||P_PIN||'" ';
  END IF;
  --ASIGNANDO ROLES A USUARIO DEPENDIENDO DEL ROL
  IF V_PERMISOS_NUEVOS_ID=101 AND V_PERMISOS_ANTERIORES_ID=100 THEN
    EXECUTE IMMEDIATE 
  'REVOKE POS_EMPLEADO FROM '||V_NOMBRE_USUARIO;
    EXECUTE IMMEDIATE 
  'GRANT POS_ADMIN TO '||V_NOMBRE_USUARIO;
  ELSIF V_PERMISOS_NUEVOS_ID=100 AND V_PERMISOS_ANTERIORES_ID=101 THEN
    EXECUTE IMMEDIATE 
  'REVOKE POS_ADMIN FROM '||V_NOMBRE_USUARIO;
    EXECUTE IMMEDIATE 
  'GRANT POS_EMPLEADO TO '||V_NOMBRE_USUARIO;
  END IF; 
  --ACTUALIZANDO USUARIO EN TABLA
  UPDATE ROOT.USUARIO U
  SET 
  U.NOMBRE = P_NOMBRE,
  U.DISPONIBILIDAD = P_DISPONIBILIDAD,
  U.PERMISOS_ID = V_PERMISOS_NUEVOS_ID
  WHERE U.USUARIO_ID = P_USUARIO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_ELIMINAR_USUARIO
(
P_USUARIO_ID IN ROOT.USUARIO.USUARIO_ID%TYPE
)
AS
V_NOMBRE_USUARIO VARCHAR(20);
BEGIN
  --OBTENIENDO NOMBRE DE USUARIO
  V_NOMBRE_USUARIO:=F_OBTENER_USUARIO(P_USUARIO_ID);
  --ELIMINANDO ESQUEMA DE LA BD
   EXECUTE IMMEDIATE 
  'DROP USER '||V_NOMBRE_USUARIO;
  --DESACTIVANDO USUARIO EN TABLA
  UPDATE ROOT.USUARIO U
  SET U.ESTADO_ELIMINACION=1
  WHERE U.USUARIO_ID=P_USUARIO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_AGREGAR_CLIENTE
(
P_NOMBRE IN ROOT.CLIENTE.NOMBRE%TYPE,
P_TELEFONO IN ROOT.CLIENTE.TELEFONO%TYPE,
P_CORREO IN ROOT.CLIENTE.CORREO%TYPE
)
AS
BEGIN
  INSERT INTO ROOT.CLIENTE
  VALUES
  (
  SQ_CLIENTE.NEXTVAL,
  P_NOMBRE,
  P_TELEFONO,
  P_CORREO,
  SYSDATE,
  0
  );
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_MODIFICAR_CLIENTE
(
P_CLIENTE_ID IN ROOT.CLIENTE.CLIENTE_ID%TYPE,
P_NOMBRE IN ROOT.CLIENTE.NOMBRE%TYPE,
P_TELEFONO IN ROOT.CLIENTE.TELEFONO%TYPE,
P_CORREO IN ROOT.CLIENTE.CORREO%TYPE
)
AS
BEGIN
  UPDATE ROOT.CLIENTE C
  SET C.NOMBRE=P_NOMBRE,
  C.TELEFONO=P_TELEFONO,
  C.CORREO=P_CORREO
  WHERE C.CLIENTE_ID=P_CLIENTE_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_ELIMINAR_CLIENTE
(
P_CLIENTE_ID IN ROOT.CLIENTE.CLIENTE_ID%TYPE
)
AS
BEGIN
  UPDATE ROOT.CLIENTE C
  SET C.ESTADO_ELIMINACION=1
  WHERE C.CLIENTE_ID=P_CLIENTE_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_AGREGAR_PROVEEDOR
(
P_RAZON_SOCIAL IN ROOT.PROVEEDOR.RAZON_SOCIAL%TYPE,
P_TELEFONO IN ROOT.PROVEEDOR.TELEFONO%TYPE,
P_CORREO IN ROOT.PROVEEDOR.CORREO%TYPE
)
AS
BEGIN
  INSERT INTO ROOT.PROVEEDOR
  VALUES
  (
  SQ_PROVEEDOR.NEXTVAL,
  P_RAZON_SOCIAL,
  P_TELEFONO,
  P_CORREO,
  SYSDATE,
  0
  );
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_MODIFICAR_PROVEEDOR
(
P_PROVEEDOR_ID IN ROOT.PROVEEDOR.PROVEEDOR_ID%TYPE,
P_RAZON_SOCIAL IN ROOT.PROVEEDOR.RAZON_SOCIAL%TYPE,
P_TELEFONO IN ROOT.PROVEEDOR.TELEFONO%TYPE,
P_CORREO IN ROOT.PROVEEDOR.CORREO%TYPE
)
AS
BEGIN
  UPDATE ROOT.PROVEEDOR P
  SET P.RAZON_SOCIAL=P_RAZON_SOCIAL,
  P.TELEFONO=P_TELEFONO,
  P.CORREO=P_CORREO
  WHERE P.PROVEEDOR_ID=P_PROVEEDOR_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_ELIMINAR_PROVEEDOR
(
P_PROVEEDOR_ID IN ROOT.PROVEEDOR.PROVEEDOR_ID%TYPE
)
AS
BEGIN
  UPDATE ROOT.PROVEEDOR P
  SET P.ESTADO_ELIMINACION=1
  WHERE P.PROVEEDOR_ID=P_PROVEEDOR_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_AGREGAR_PRODUCTO
(
P_NOMBRE IN ROOT.PRODUCTO.NOMBRE%TYPE,
P_PRECIO IN ROOT.PRODUCTO.PRECIO%TYPE,
P_COSTO IN ROOT.PRODUCTO.COSTO%TYPE,
P_STOCK IN ROOT.PRODUCTO.STOCK%TYPE,
P_PRECIO_VARIABLE IN ROOT.PRODUCTO.PRECIO_VARIABLE%TYPE,
P_PERMITIR_DESCUENTOS IN ROOT.PRODUCTO.PERMITIR_DESCUENTOS%TYPE,
P_VISIBILIDAD_EN_CAJA IN ROOT.PRODUCTO.VISIBILIDAD_EN_CAJA%TYPE,
P_DEPARTAMENTO_ID IN ROOT.PRODUCTO.DEPARTAMENTO_ID%TYPE
)
AS
BEGIN
  INSERT INTO ROOT.PRODUCTO
  VALUES
  (
  SQ_PRODUCTO.NEXTVAL,
  P_NOMBRE,
  P_PRECIO,
  P_COSTO,
  P_STOCK,
  P_PRECIO_VARIABLE,
  P_PERMITIR_DESCUENTOS,
  P_VISIBILIDAD_EN_CAJA,
  SYSDATE,
  0,
  P_DEPARTAMENTO_ID
  );
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_MODIFICAR_PRODUCTO
(
P_PRODUCTO_ID IN ROOT.PRODUCTO.PRODUCTO_ID%TYPE,
P_NOMBRE IN ROOT.PRODUCTO.NOMBRE%TYPE,
P_PRECIO IN ROOT.PRODUCTO.PRECIO%TYPE,
P_COSTO IN ROOT.PRODUCTO.COSTO%TYPE,
P_STOCK IN ROOT.PRODUCTO.STOCK%TYPE,
P_PRECIO_VARIABLE IN ROOT.PRODUCTO.PRECIO_VARIABLE%TYPE,
P_PERMITIR_DESCUENTOS IN ROOT.PRODUCTO.PERMITIR_DESCUENTOS%TYPE,
P_VISIBILIDAD_EN_CAJA IN ROOT.PRODUCTO.VISIBILIDAD_EN_CAJA%TYPE
)
AS
BEGIN
  UPDATE ROOT.PRODUCTO P
  SET 
  P.NOMBRE=P_NOMBRE,
  P.PRECIO=P_PRECIO,
  P.COSTO=P_COSTO,
  P.STOCK=P_STOCK,
  P.PRECIO_VARIABLE=P_PRECIO_VARIABLE,
  P.PERMITIR_DESCUENTOS=P_PERMITIR_DESCUENTOS,
  P.VISIBILIDAD_EN_CAJA=P_VISIBILIDAD_EN_CAJA
  WHERE P.PRODUCTO_ID=P_PRODUCTO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_ELIMINAR_PRODUCTO
(
P_PRODUCTO_ID IN ROOT.PRODUCTO.PRODUCTO_ID%TYPE
)
AS
BEGIN
  UPDATE ROOT.PRODUCTO P
  SET 
  P.ESTADO_ELIMINACION=1
  WHERE P.PRODUCTO_ID=P_PRODUCTO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_AGREGAR_DEPARTAMENTO
(
P_NOMBRE IN ROOT.DEPARTAMENTO.NOMBRE%TYPE
)
AS
BEGIN
  INSERT INTO ROOT.DEPARTAMENTO
  VALUES
  (
  SQ_DEPARTAMENTO.NEXTVAL,
  P_NOMBRE,
  SYSDATE,
  0
  );
  COMMIT;
END;


CREATE OR REPLACE PROCEDURE SP_MODIFICAR_DEPARTAMENTO
(
P_DEPARTAMENTO_ID IN ROOT.DEPARTAMENTO.DEPARTAMENTO_ID%TYPE,
P_NOMBRE IN ROOT.DEPARTAMENTO.NOMBRE%TYPE
)
AS
BEGIN
  UPDATE ROOT.DEPARTAMENTO D
  SET D.NOMBRE=P_NOMBRE
  WHERE D.DEPARTAMENTO_ID=P_DEPARTAMENTO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_ELIMINAR_DEPARTAMENTO
(
P_DEPARTAMENTO_ID IN ROOT.DEPARTAMENTO.DEPARTAMENTO_ID%TYPE
)
AS
  CURSOR C_PRODUCTO_CURSOR IS SElECT * FROM VW_PRODUCTOS_EN_DEPARTAMENTO;
  C_PRODUCTO_CURSOR_REC C_PRODUCTO_CURSOR%ROWTYPE;
BEGIN
  ROOT.ROOT_INVENTARIO.V_DEPARTAMENTO_ID_BUSQUEDA :=P_DEPARTAMENTO_ID;
  OPEN C_PRODUCTO_CURSOR;
  /*ELIMINANDO LOGICAMENTE TODOS LOS PRODUCTOS ASOCIADOS
  AL DEPARTAMENTO*/
  LOOP
    FETCH C_PRODUCTO_CURSOR INTO C_PRODUCTO_CURSOR_REC;
    EXIT WHEN C_PRODUCTO_CURSOR%NOTFOUND;
    ROOT.SP_ELIMINAR_PRODUCTO(C_PRODUCTO_CURSOR_REC.PRODUCTO_ID);
  END LOOP;
  --ELIMINANDO DEPARTAMENTO
  UPDATE ROOT.DEPARTAMENTO D
  SET 
  D.ESTADO_ELIMINACION=1
  WHERE D.DEPARTAMENTO_ID=P_DEPARTAMENTO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_EXPORTAR_VENTAS
(
P_FECHA_INICIO ROOT.VENTA.FECHA_REGISTRO%TYPE,
P_FECHA_FIN ROOT.VENTA.FECHA_REGISTRO%TYPE,
P_DIR IN VARCHAR2,
P_NOMBRE_ARCHIVO IN VARCHAR2
)
IS
 CURSOR CUR_VENTAS IS 
 SELECT * FROM ROOT.VW_VENTAS_PARA_REPORTE;
 CUR_VENTAS_RECORD CUR_VENTAS%ROWTYPE;
 V_OUTPUT UTL_FILE.FILE_TYPE;
BEGIN
  ROOT.ROOT_REPORTE.V_REPORTE_FECHA_INICIO:=P_FECHA_INICIO;
  ROOT.ROOT_REPORTE.V_REPORTE_FECHA_FIN:=P_FECHA_FIN;
  --MODIFICANDO DIRECTORIO DONDE SE GUARDARA EL CSV
  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY CSV_DIRECTORY AS '''||P_DIR||'''';
  V_OUTPUT:=UTL_FILE.FOPEN('CSV_DIRECTORY',P_NOMBRE_ARCHIVO,'w');
  OPEN CUR_VENTAS;
  LOOP
    FETCH CUR_VENTAS INTO CUR_VENTAS_RECORD;
    EXIT WHEN CUR_VENTAS%NOTFOUND;
    UTL_FILE.PUT_LINE(V_OUTPUT,
    CUR_VENTAS_RECORD.VENTA_ID||','||
    CUR_VENTAS_RECORD.FECHA_REGISTRO||','||
    CUR_VENTAS_RECORD.VENTA_BRUTA||','||
    CUR_VENTAS_RECORD.TOTAL_IMPUESTO||','||
    CUR_VENTAS_RECORD.TOTAL_DESCUENTO||','||
    CUR_VENTAS_RECORD.TOTAL_COSTO||','||
    CUR_VENTAS_RECORD.PAGO_CLIENTE||','||
    CUR_VENTAS_RECORD.CAMBIO||','||
    CUR_VENTAS_RECORD.CLIENTE_ID||','||
    CUR_VENTAS_RECORD.USUARIO_ID
    );
  END LOOP;
  UTL_FILE.FCLOSE(V_OUTPUT);
END;

CREATE OR REPLACE PROCEDURE SP_INGRESO_PRODUCTO
(
P_PRODUCTO_ID IN ROOT.PRODUCTO.PRODUCTO_ID%TYPE,
P_PROVEEDOR_ID IN ROOT.PROVEEDOR.PROVEEDOR_ID%TYPE,
P_CANTIDAD IN ROOT.PROVEEDOR_PRODUCTO.CANTIDAD%TYPE
)
AS
BEGIN
  --AGREGANDO REGISTRO DE ENTREGA A LA TABLA PROVEEDOR-PRODUCTO
  INSERT INTO ROOT.PROVEEDOR_PRODUCTO
  VALUES
  (
  SQ_PROVEEDOR_PRODUCTO.NEXTVAL,
  P_PROVEEDOR_ID,
  P_PRODUCTO_ID,
  P_CANTIDAD,
  SYSDATE
  );
  --ACTUALIZANDO STOCK DE PRODUCTO
  UPDATE ROOT.PRODUCTO P
  SET P.STOCK = P.STOCK+P_CANTIDAD
  WHERE P.PRODUCTO_ID = P_PRODUCTO_ID;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE SP_REGISTRO_VENTA
(
P_TOTAL_IMPUESTO IN ROOT.VENTA.TOTAL_IMPUESTO%TYPE,
P_TOTAL_DESCUENTO IN ROOT.VENTA.TOTAL_DESCUENTO%TYPE,
P_PAGO_CLIENTE IN ROOT.PRODUCTO.PRODUCTO_ID%TYPE,
P_CLIENTE_ID IN ROOT.PRODUCTO.PRODUCTO_ID%TYPE,
P_USUARIO_ID IN ROOT.PRODUCTO.PRODUCTO_ID%TYPE,
P_VENTA_PRODUCTO_TABLE IN TYPE_TABLE_VENTA_PRODUCTO
)
AS
BEGIN
  --AGREGANDO REGISTRO DE VENTA A SU RESPECTIVA TABLA
  INSERT INTO ROOT.VENTA
  VALUES
  (
  SQ_VENTA.NEXTVAL,
  P_TOTAL_IMPUESTO,
  P_TOTAL_DESCUENTO,
  P_PAGO_CLIENTE,
  SYSDATE,
  P_CLIENTE_ID,
  P_USUARIO_ID
  );
  COMMIT;
  /*RECORRIENDO LOS PRODUCTOS ASOCIADOS A LA VENTA
  Y AGREGANDOLOS A LA TABLA VENTA_PRODUCTO*/
  FOR i IN 1..P_VENTA_PRODUCTO_TABLE.COUNT LOOP
    INSERT INTO ROOT.VENTA_PRODUCTO
    VALUES
    (
    SQ_VENTA.CURRVAL,
    P_VENTA_PRODUCTO_TABLE(i).PRODUCTO_ID,
    P_VENTA_PRODUCTO_TABLE(i).CANTIDAD
    );
    --ACTUALIZANDO EL STOCK DE CADA PRODUCTO
    UPDATE ROOT.PRODUCTO P
    SET P.STOCK = P.STOCK-P_VENTA_PRODUCTO_TABLE(i).CANTIDAD
    WHERE P.PRODUCTO_ID = P_VENTA_PRODUCTO_TABLE(i).PRODUCTO_ID;
     COMMIT;
  END LOOP;
END;


-- FUNCIONES
CREATE OR REPLACE FUNCTION F_OBTENER_USUARIO
(
P_USUARIO_ID ROOT.USUARIO.USUARIO_ID%TYPE
)
RETURN VARCHAR2
IS
 V_NOMBRE_USUARIO ALL_USERS.USERNAME%TYPE;
BEGIN
 /*OBJETIVO: ESTA FUNCION TE PERMITE OBTENER EL
 NOMBRE DE UN USUARIO DEL SISTEMA (Y DE LA BD)
 USANDO SU ID
 USO: ESTA FUNCION SE USA CUANDO SE INTENTA INICIAR
 SESION EN EL SISTEMA DE PUNTO DE VENTA.
 */
  V_NOMBRE_USUARIO:='';
  --BUSCANDO NOMBRE DE USUARIO
  SELECT 
  AU.USERNAME
  INTO V_NOMBRE_USUARIO
  FROM ALL_USERS AU
  WHERE AU.USER_ID=P_USUARIO_ID;
  --RETORNANDO NOMBRE DE USUARIO
  RETURN V_NOMBRE_USUARIO;
END;

/*
--Limpiar BD
DELETE FROM ROOT.USUARIO;
DELETE FROM ROOT.CLIENTE;
DELETE FROM ROOT.PROVEEDOR;
DELETE FROM ROOT.PRODUCTO;
DELETE FROM ROOT.DEPARTAMENTO;
*/
--Creacion de usuario default para configuracion de sistema
BEGIN
  SP_AGREGAR_USUARIO('ADMINISTRADOR',1234,1);
END;
--Pruebas
/*
select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;
*/