/* Modificaciones en tabla de CLINICAS, para administrar SUCURSAL */
alter table clinica add vSucursal varchar(2) null

update clinica set vSucursal = '2'

alter table clinica modify vSucursal varchar(2) not null

create or replace procedure InsertaClinica(
       pID                  IN NUMBER,
       pNOMBRECLINICA       IN VARCHAR2, 
       pRUC                 IN VARCHAR2, 
       pEMAIL               IN VARCHAR2, 
       pTUBIGEO_DPTO        IN NUMBER, 
       pTUBIGEO_PROV        IN NUMBER, 
       pTUBIGEO_DIST        IN NUMBER,
       pCOD_PROVEEDOR_LP    IN NUMBER,
       p_n_trama_9          IN NUMBER,
       p_vSucursal          IN VARCHAR2)
as
begin
  Insert into Clinica (IDCLINICA, NOMBRECLINICA, RUC, EMAIL, 
  TUBIGEO_DPTO, TUBIGEO_PROV, TUBIGEO_DIST, COD_PROVEEDOR_LP, n_trama_9, vSucursal)
  values(pID, pNOMBRECLINICA, pRUC, 
  pEMAIL, pTUBIGEO_DPTO, pTUBIGEO_PROV, pTUBIGEO_DIST, pCOD_PROVEEDOR_LP, p_n_trama_9, p_vSucursal);
end InsertaClinica;

create or replace procedure ActualizaClinica(
       pID                  IN NUMBER,
       pNOMBRECLINICA       IN VARCHAR2, 
       pRUC                 IN VARCHAR2, 
       pEMAIL               IN VARCHAR2, 
       pTUBIGEO_DPTO        IN NUMBER, 
       pTUBIGEO_PROV        IN NUMBER, 
       pTUBIGEO_DIST        IN NUMBER,
       pCOD_PROVEEDOR_LP    IN NUMBER,
       p_n_trama_9          IN NUMBER,
       p_vSucursal          IN VARCHAR2)
as
begin
  Update Clinica 
  set NOMBRECLINICA = pNOMBRECLINICA,
  EMAIL = pEMAIL, 
  TUBIGEO_DPTO = pTUBIGEO_DPTO, 
  TUBIGEO_PROV = pTUBIGEO_PROV, 
  TUBIGEO_DIST = pTUBIGEO_DIST,
  COD_PROVEEDOR_LP = pCOD_PROVEEDOR_LP,
  n_trama_9 = p_n_trama_9,
  vSucursal = p_vSucursal
  where IDCLINICA = pID
  and RUC = pRUC; 
end ActualizaClinica;
