Untitled

-- disparador que actualiza el stock de un producto en la tabla PRODUCTO
-- TRIGGER
CREATE OR REPLACE TRIGGER tr_stock
AFTER INSERT OR DELETE OR UPDATE OF cantidad ON detalle_boleta
FOR EACH ROW
DECLARE
BEGIN
   IF INSERTING THEN
      UPDATE producto
          SET totalstock = totalstock - :NEW.cantidad
      WHERE codproducto = :NEW.codproducto;
   ELSIF DELETING THEN
      UPDATE producto
          SET totalstock = totalstock + :OLD.cantidad
      WHERE codproducto = :OLD.codproducto;
   ELSE
      UPDATE producto
      SET totalstock = totalstock - (:NEW.cantidad - :OLD.cantidad)
      WHERE codproducto = :NEW.codproducto;   
   END IF;
END;
/

-- consultamos el estado de los datos antes de las operaciones
select *
from detalle_boleta
where numboleta = 1200;

SELECT CODPRODUCTO, TOTALSTOCK FROM PRODUCTO WHERE CODPRODUCTO IN (10, 7, 20, 3);

-- bloque anonimo con las transacciones
BEGIN
    INSERT INTO DETALLE_BOLETA
    VALUES (1200, 10, (SELECT VUNITARIO FROM PRODUCTO WHERE CODPRODUCTO = 10),
            4, (SELECT VUNITARIO FROM PRODUCTO WHERE CODPRODUCTO = 10) * 4);
    
    DELETE FROM DETALLE_BOLETA 
    WHERE numboleta = 1200 AND CODPRODUCTO = 7; 
    
    UPDATE DETALLE_BOLETA
       SET cantidad = 2,
           totallinea = 5000
    where numboleta = 1200 and codproducto = 20;
    
    
    UPDATE DETALLE_BOLETA
       SET cantidad = 15,
           totallinea = 150000
    where numboleta = 1200 and codproducto = 3;
END;
/

-- consultamos el estado de los datos despu�s de las operaciones
select *
from detalle_boleta
where numboleta = 1200;

SELECT CODPRODUCTO, TOTALSTOCK FROM PRODUCTO WHERE CODPRODUCTO IN (10, 7, 20, 3);

CREATE OR REPLACE PACKAGE pkg_admelect AS
  vp_ventas NUMBER(8) := 0;
  FUNCTION fn_ventas (p_run VARCHAR2, p_fec VARCHAR2) RETURN NUMBER;
END pkg_admelect;
/

--PACKAGE
CREATE OR REPLACE PACKAGE BODY pkg_admelect AS
  FUNCTION fn_ventas (
    p_run VARCHAR2, p_fec VARCHAR2 
  ) RETURN NUMBER
  AS
     v_montoventas NUMBER(8); 
  BEGIN
     SELECT SUM(db.totallinea)
     INTO v_montoventas
     FROM boleta bo JOIN detalle_boleta db
     ON bo.numboleta = db.numboleta
     WHERE TO_CHAR(bo.fecha, 'yyyymm') = p_fec
     AND bo.run_empleado = p_run;
     RETURN v_montoventas;
  END fn_ventas;
END pkg_admelect;
/

EXEC dbms_output.put_line(pkg_admelect.fn_ventas('12456778-1', '202104'));

--FUNCION
CREATE OR REPLACE FUNCTION fn_asigantiguedad (
  p_anti NUMBER, p_ventas NUMBER
) RETURN NUMBER
AS
  v_pct NUMBER;
  v_sql VARCHAR2(300);
  v_msg VARCHAR2(300);
BEGIN
  BEGIN 
      EXECUTE IMMEDIATE 'SELECT porc_antiguedad / 100
                         FROM porc_antiguedad_empleado
                         WHERE :1 BETWEEN annos_antiguedad_inf AND annos_antiguedad_sup'
      INTO v_pct USING p_anti;
  EXCEPTION
     WHEN OTHERS THEN
        v_msg := sqlerrm;
        v_pct := 0;
        INSERT INTO error_calc VALUES (seq_error.NEXTVAL, 'Error en la funcion FN_anti al recuperar el porcentaje asociado a '
                                       || p_anti || ' anios de antiguedad ', v_msg);
  END;      
  RETURN round(p_ventas * v_pct); 
END fn_asigantiguedad;
/

EXEC dbms_output.put_line(fn_asigantiguedad(3, 200000));
/

--PROCEDIMIENTO
CREATE OR REPLACE PROCEDURE sp_procesaremun (
  p_fec VARCHAR2, p_colacion NUMBER, p_mov NUMBER, p_salud NUMBER
) 
AS
  -- cursor que recupera a todos los empleados 
  CURSOR c_empleados IS
    SELECT e.*, pa.porcafp / 100 PCTAFP
    FROM empleado e JOIN prevision_afp pa on e.codafp = pa.codafp
    ORDER BY paterno, materno, nombre;
   
  -- variables escalares
  v_nom VARCHAR2(50);
  v_mov NUMBER(8) := 0;
  v_asiespecial number(8) := 0;
  v_anti number(3) := 0;
  v_pctcomis porcentaje_comision_venta.porc_comision%TYPE;
  v_comisventas number(8) := 0;
  v_imponible number(8) := 0;
  v_descuentos NUMBER(8) := 0;
  v_msg_oracle error_calc.descrip_error%TYPE;

BEGIN
   -- TRUNCAMOS LAS TABLAS 
   EXECUTE IMMEDIATE 'TRUNCATE TABLE detalle_pago_mensual';
   EXECUTE IMMEDIATE 'TRUNCATE TABLE error_calc';

  FOR r_emp IN c_empleados LOOP
     
     v_nom := r_emp.paterno || ' ' || r_emp.materno || ' ' || r_emp.nombre;
     v_anti := EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM r_emp.fecha_contrato);
     -- calculamos el monto adicional de movilizacion dependiendo de la comuna
     v_mov := CASE 
                  WHEN r_emp.codcomuna IN(1,7) THEN ROUND(r_emp.sueldo_base * 0.10)
                  WHEN r_emp.codcomuna IN(2,4,8) THEN ROUND(r_emp.sueldo_base * 0.12)
                  WHEN r_emp.codcomuna IN(3,5,6) THEN ROUND(r_emp.sueldo_base * 0.14)
                  ELSE ROUND(r_emp.sueldo_base * 0.16)
              END;
     v_mov := v_mov + p_mov;


     -- calculamos la asignacion especial usando la funcion almacenada y la funcion del package
     v_asiespecial := fn_asigantiguedad(v_anti, pkg_admelect.fn_ventas(r_emp.run_empleado, p_fec));
     
     -- recuperamos porcentaje de comision dependiendo de las ventas
     -- y calculamos comision por ventas
     BEGIN
        SELECT porc_comision / 100
        INTO v_pctcomis
        FROM porcentaje_comision_venta
        WHERE pkg_admelect.fn_ventas(r_emp.run_empleado, p_fec) BETWEEN venta_inf and venta_sup;

        v_comisventas := ROUND(pkg_admelect.fn_ventas(r_emp.run_empleado, p_fec) * v_pctcomis);
     EXCEPTION WHEN NO_DATA_FOUND THEN
        v_msg_oracle := SQLERRM;
        v_comisventas := 0;
        INSERT INTO error_calc VALUES (seq_error.NEXTVAL, 'Error al recuperar la comision por ventas', v_msg_oracle);     
     END;
     -- calculamos el sueldo imponible
     v_imponible := r_emp.sueldo_base + p_colacion + v_mov + v_asiespecial + v_comisventas;
     
     -- calculamos los descuentos
    v_descuentos := ROUND(v_imponible * r_emp.pctafp) + ROUND(v_imponible * p_salud / 100);
     
    -- Guarda los resultados en la tabla
    INSERT INTO detalle_pago_mensual
    VALUES(SUBSTR(p_fec,-2),SUBSTR(p_fec,1,4),r_emp.run_empleado,v_nom,r_emp.sueldo_base,p_colacion,v_mov,
                              v_asiespecial,v_comisventas,v_imponible,v_descuentos,(v_imponible - v_descuentos));
  END LOOP;
  -- salvamos los datos
  COMMIT;  
END sp_procesaremun;
/

BEGIN
 sp_procesaremun('202104', 75000, 60000, 7);
 EXECUTE IMMEDIATE 'DROP SEQUENCE seq_error';
 EXECUTE IMMEDIATE 'CREATE SEQUENCE seq_error';
END;