SHARE
TWEET

Untitled

a guest Jun 18th, 2019 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE P_CONSULTAR_SOCIOS (P_CRITERIO IN VARCHAR2,P_SOCIO IN VARCHAR2)
  2. IS  
  3.  
  4.     V_SENT_SOCIO SOC_SOCIO%rowtype;
  5.     V_DEUDA NUMBER;--total dept
  6.     V_SALDO NUMBER;--available money
  7.     capital_pagado NUMBER;--how much he has paid
  8.     capital_a_pagar NUMBER;--how much he has to pay
  9.     V_APORTES NUMBER;--total contributions
  10.  
  11. BEGIN
  12.  
  13.     IF UPPER(P_CRITERIO) = 'ID' THEN    
  14.         SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE ID_SOCIO=P_SOCIO;
  15.     ELSE IF UPPER(P_CRITERIO) = 'CEDULA' THEN      
  16.         SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE CEDULA=P_SOCIO;
  17.     ELSE IF UPPER(P_CRITERIO) = 'APELLIDO' THEN
  18.         SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE APELLIDO=P_SOCIO;
  19.     END IF;
  20.  
  21.     --available money          
  22.     SELECT NVL(SALDO_DISPONIBLE,0) INTO V_SALDO    
  23.     FROM AHO_CUENTA_AHORRO      
  24.     WHERE ID_SOCIO = V_SENT_SOCIO.ID_SOCIO;
  25.  
  26.     --calculates if he has any loan active and the amount is stored in V_DEUDA      
  27.     select sum(capital_pagado) into capital_pagado from cre_prestamos where id_sol_cred=V_SENT_SOCIO.ID_SOCIO;          
  28.     select sum(capital_a_pagar) into capital_a_pagar from cre_prestamos where id_sol_cred=V_SENT_SOCIO.ID_SOCIO and UPPER(estado)='A';          
  29.     V_DEUDA := (capital_pagado - capital_a_pagar);
  30.  
  31.  
  32.     --sum of total contributions
  33.     select sum(nvl(sdo.monto,0)) into V_APORTES from soc_detalle_obligaciones sdo
  34.     join soc_obligaciones o on sdo.id_obligacion = o.id_obligacion
  35.     where o.ID_SOCIO=V_SENT_SOCIO.ID_SOCIO and o.tipo_obligacion = 'A';
  36.  
  37.     --prints result
  38.     DBMS_OUTPUT.PUT_LINE('| '||V_SENT_SOCIO.ID_SOCIO||' | '||V_SENT_SOCIO.CEDULA||
  39.     ' | '||V_SENT_SOCIO.NOMBRE_APELLIDO||' | '||' | '||V_SALDO||' | '||V_DEUDA||' | '||
  40.     V_APORTES||' |');  
  41.  
  42.     EXCEPTION          
  43.         WHEN NO_DATA_FOUND THEN        
  44.             DBMS_OUTPUT.PUT_LINE(-20032,'El socio no existe');      
  45.         WHEN OTHERS THEN        
  46.             DBMS_OUTPUT.PUT_LINE('Ha ocurrido un error');
  47. END P_CONSULTAR_SOCIOS;
  48. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top