Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE P_CONSULTAR_SOCIOS (P_CRITERIO IN VARCHAR2,P_SOCIO IN VARCHAR2)
- IS
- V_SENT_SOCIO SOC_SOCIO%rowtype;
- V_DEUDA NUMBER;--total dept
- V_SALDO NUMBER;--available money
- capital_pagado NUMBER;--how much he has paid
- capital_a_pagar NUMBER;--how much he has to pay
- V_APORTES NUMBER;--total contributions
- BEGIN
- IF UPPER(P_CRITERIO) = 'ID' THEN
- SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE ID_SOCIO=P_SOCIO;
- ELSE IF UPPER(P_CRITERIO) = 'CEDULA' THEN
- SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE CEDULA=P_SOCIO;
- ELSE IF UPPER(P_CRITERIO) = 'APELLIDO' THEN
- SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE APELLIDO=P_SOCIO;
- END IF;
- --available money
- SELECT NVL(SALDO_DISPONIBLE,0) INTO V_SALDO
- FROM AHO_CUENTA_AHORRO
- WHERE ID_SOCIO = V_SENT_SOCIO.ID_SOCIO;
- --calculates if he has any loan active and the amount is stored in V_DEUDA
- select sum(capital_pagado) into capital_pagado from cre_prestamos where id_sol_cred=V_SENT_SOCIO.ID_SOCIO;
- 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';
- V_DEUDA := (capital_pagado - capital_a_pagar);
- --sum of total contributions
- select sum(nvl(sdo.monto,0)) into V_APORTES from soc_detalle_obligaciones sdo
- join soc_obligaciones o on sdo.id_obligacion = o.id_obligacion
- where o.ID_SOCIO=V_SENT_SOCIO.ID_SOCIO and o.tipo_obligacion = 'A';
- --prints result
- DBMS_OUTPUT.PUT_LINE('| '||V_SENT_SOCIO.ID_SOCIO||' | '||V_SENT_SOCIO.CEDULA||
- ' | '||V_SENT_SOCIO.NOMBRE_APELLIDO||' | '||' | '||V_SALDO||' | '||V_DEUDA||' | '||
- V_APORTES||' |');
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- DBMS_OUTPUT.PUT_LINE(-20032,'El socio no existe');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Ha ocurrido un error');
- END P_CONSULTAR_SOCIOS;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement