CREATE TABLE PAGOS_EN_LINEA2
   (NUMPIECE INTEGER NOT NULL,
    NUMABONNE INTEGER NOT NULL,
    CODIGO_PAGO INTEGER NOT NULL,
    RECAUDADOR VARCHAR(10),
    MONTO PRICE,
    TIPO VARCHAR(1),
    FECHA_PAGO DATE NOT NULL,
    CONSTRAINT PK1_PAGOS_EN_LINEA2 PRIMARY KEY (CODIGO_PAGO),
    CONSTRAINT CHECHKTIPO1 CHECK (TIPO IN ('R','P'))
);

ALTER TABLE ABONNE
DROP VENCELGT,
DROP FECHA_GESTION,
ADD VENCELGT DATE,
ADD FECHA_GESTION DATE
;

ALTER TABLE ABONNEPPV
DROP DTFACT,
ADD DTFACT DATE
;

ALTER TABLE AVERINTERNET
DROP FECHAINI,
DROP FECHAFINAL,
ADD FECHAINI DATE,
ADD FECHAFINAL DATE
;

ALTER TABLE BITACORA_NODOS
DROP FECHA,
ADD FECHA DATE NOT NULL
;

ALTER TABLE CABLEMODEMSERV
DROP FECHAVENCIM,
ADD FECHAVENCIM DATE
;

/* Hoja de Control de diario para consultas externas  */
ALTER TABLE CDDTRCONDI
DROP FECHA,
ADD FECHA DATE
;

/* Almacena los lotes de desconexiones del día  */
ALTER TABLE DECOLUNES
DROP DTSAISIE,
ADD DTSAISIE DATE
;

ALTER TABLE DEPOSITOS
DROP FECHA,
ADD FECHA DATE
;

ALTER TABLE DICO
DROP CREATEDATE,
DROP LASTUPDATE,
ADD CREATEDATE DATE,
ADD LASTUPDATE DATE
;

/* Contiene lineas de contabilidad  */
ALTER TABLE ECRITCPT
DROP FECHA_DESDE,
DROP FECHA_HASTA,
ADD FECHA_DESDE DATE,
ADD FECHA_HASTA DATE
;

ALTER TABLE ESTCTA_031
DROP ULT_PAGO,
ADD ULT_PAGO DATE
;

/* Contiene los cheques rechazados  */
ALTER TABLE IMPAYE
DROP DTPAIE,
ADD DTPAIE DATE
;

/* Contiene informacion de Internet para el cliente  */
ALTER TABLE INTERNET
DROP FECHAVENCE,
ADD FECHAVENCE DATE
;

/* Contiene informacion acerca de vendedores, tecnicos y cobradores  */
ALTER TABLE INTERVENANT
ADD CLAVE VARCHAR(10),
ADD COMISION FLOAT,
ADD COBRA DOM_BOOL DEFAULT 'F',
ADD ENLINEA DOM_BOOL DEFAULT 'F'
;

/* Contiene información acerca de ordenes de trabajo  */
ALTER TABLE INTERVENTION
DROP DTOTAVERIA,
DROP ROLLINICIAL,
DROP ROLLFINAL,
DROP FECH_CHG_GRUPO,
ADD DTOTAVERIA DATE,
ADD ROLLINICIAL DATE,
ADD ROLLFINAL DATE,
ADD FECH_CHG_GRUPO DATE
;

ALTER TABLE LOGRELOJ
DROP FECHA,
ADD FECHA DATE
;

ALTER TABLE LOTE_DESC_RACSA
DROP FECHA_CARGA,
DROP FECHA_DESCO,
ADD FECHA_CARGA DATE,
ADD FECHA_DESCO DATE
;

ALTER TABLE LOTE_RECO_RACSA
DROP FECHA_CARGA,
DROP FECHA_RECO,
ADD FECHA_CARGA DATE,
ADD FECHA_RECO DATE
;

/*    */
ALTER TABLE NODE
DROP FECHA_INIC,
DROP FECHA_FINC,
DROP FECHA_ENLACE,
ADD FECHA_INIC DATE,
ADD FECHA_FINC DATE,
ADD FECHA_ENLACE DATE
;

ALTER TABLE PAGOS_EN_LINEA
DROP FECHA_PAGO,
ADD FECHA_PAGO DATE NOT NULL
;

/* Contiene lista de Servicios que pueden ser facturados  */
ALTER TABLE PRODUIT
DROP DTDEB,
DROP DTFIN,
ADD DTDEB DATE,
ADD DTFIN DATE
;

ALTER TABLE SALDOS_PENDIENTES
DROP FECHA,
ADD FECHA DATE NOT NULL
;

/* Contiene calendario de las peliculas PPV  */
ALTER TABLE SCHEDULEPPV
DROP SCHEDSTARTDT,
DROP DTSAISIE,
ADD SCHEDSTARTDT DATE,
ADD DTSAISIE DATE
;

ALTER TABLE SOLICITUD_CABIO_PRECIO
DROP FECHA_SOLICITUD,
DROP FEHA_APLICACION,
DROP FECHA_APROBACION,
ADD FECHA_SOLICITUD DATE NOT NULL,
ADD FEHA_APLICACION DATE,
ADD FECHA_APROBACION DATE
;

ALTER TABLE TEMPCOMPARAINTERNET
DROP FECHA,
ADD FECHA DATE
;

/* Contiene informacion de terminales (premium y DLA)  */
ALTER TABLE TERMINAL
DROP FECHAVENCE,
ADD FECHAVENCE DATE,
ADD HUB CHAR(1)
;

/* Detalle de películas disponibles en PPV  */
ALTER TABLE TITLEPPV
DROP DTSAISIE,
ADD DTSAISIE DATE
;

/* Tabla de Personas con acceso al programa  */
ALTER TABLE UTILISATEUR
DROP FECHASALIDA,
ADD FECHASALIDA DATE
;

/* Guarda Los registros de venta para los clientes. Se actualiza en la fecha con cada cambio que se haga.  */
ALTER TABLE VENTE
DROP DTRESI2,
ADD DTRESI2 DATE
;

COMMIT work;


CREATE GENERATOR SOLICITUD_CABIO_PRECIO_GEN;

CREATE GENERATOR GEN_PL;

CREATE INDEX BLOCNOTEINDEX2 ON BLOCNOTE(DTSAISIE);

COMMIT WORK;
SET term ^;
CREATE PROCEDURE INC_PL  
RETURNS  (NUM_PAGO INTEGER)
AS
begin
  NUM_PAGO = Gen_Id (GEN_PL,1);
  suspend;
  exit;
end^

CREATE or raplace PROCEDURE INC_SOLI_CAMB_PREC(IDSOLICITUD OUT NUMBER) IS
BEGIN
   Select SOLICITUD_CABIO_PRECIO_GEN.nextval into IDSOLICITUD
   from dual;
END;
/

CREATE PROCEDURE INSERT_PAGOS_LINEA (NUMPIECE NUMBER,NUMABONNE NUMBER,RECAUDADOR VARCHAR2,MONTO NUMBER, FECHA DATE, TIPO VARCHAR2, CODIGO_PAGO OUT NUMBER) IS
BEGIN
   INC_PL(codigo_pago);
   insert into PAGOS_EN_LINEA2
   (numpiece,numabonne,codigo_pago,recaudador,monto,fecha_pago,tipo)
   values (numpiece,numabonne,codigo_pago,recaudador,monto,fecha,tipo);
END;
/

CREATE PROCEDURE INSERT_REVERSION (NUMABONNE NUMBER,PERIODE CHAR,DTCPT DATE,DTSAISIE DATE,NUMJRNL CHAR,LIB VARCHAR2,NUMFACT NUMBER,MTTDEB NUMBER,MTTCRED NUMBER,
                                   MDRGLT CHAR,CODOPE VARCHAR2,CODINSEE CHAR,TYPEC CHAR,CBOR CHAR,RESUMANU NUMBER,NUMLOTE VARCHAR2, NUMRGT OUT NUMBER) IS
begin
   incEC(numrgt);

   insert into ecritcpt
     (numpiece, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
     mttcred, mdrglt, reconcil, motifimp,codope, encaisse, reste, codinsee, typec, cbor, resumanu,
     numlote)
   values
    (numrgt, numabonne, periode, dtcpt, dtsaisie, numjrnl, lib, numfact, mttdeb,
     mttcred, mdrglt, 'F', '', codope, 0 ,mttdeb, codinsee, typec, cbor, resumanu,
     numlote);
end;
/


CREATE PROCEDURE NOMBRE_ABONADO (SOCIO number,NOMBRE out VARCHAR2) IS
begin
   SELECT NOM into Nombre
   FROM ABONNE
   WHERE NUMABONNE=SOCIO;
end;

CREATE PROCEDURE SP_DETALLE_PAGO (SOCIO number,NUMPIECE number,MONTO NUMBER, Q_SERVICIO OUT NUMBER) IS
   c number(4);
   i number(4);
   monto_detec    number;
   monto_relativo number;
   monto_total    number;
begin
   i = 1;
   c = 0;
   monto_relativo = monto;
   Monto_total = 0;
   select sum(v.mtt)
      into monto_total
   from vente V, PRODUIT P 
   where v.CODPROD = p.CODPROD
     and v.DTRESI >= SYSDATE
     and v.STATUT = 'A'
     and v.NUMABONNE =SOCIO;

  if (monto <= monto_total) then
     FOR reg in (select v.codprod,p.CODTVA,p.JRNLBKE,p.JRNLVTE,sum(vente.mtt) total
                 from vente v, PRODUIT p
                 where v.CODPROD = p.CODPROD
                   and v.DTRESI >= SYSDATE
                   and vente.STATUT = 'A'
                   and vente.NUMABONNE =SOCIO
                 GROUP BY VENTE.CODPROD,produit.CODTVA,produit.JRNLBKE,produit.JRNLVTE)
     loop
        if (monto_relativo >= reg.total) then
            monto_detec := reg.total;
        else
           if (monto_relativo > 0) then
              monto_detec := monto_relativo;
           else
              monto_detec := 0;
           end if;
           insert into DETEC(NUMPIECE,CODPROD,MTT,CODTVA,NUMJRNL,CANTIDAD)
              VALUES (NUMPIECE,reg.codprod,monto_detec,reg.codtva,reg.jrnlvte,1);
           monto_detec := 0;
           c := c +1;
           monto_relativo := monto_relativo - reg.total;
        end if;
     end loop;
  else /*si el monto es mayor a la suma de los servicios */
     FOR reg in (select v.codprod,p.CODTVA,p.JRNLBKE,p.JRNLVTE,sum(vente.mtt) total
                 from vente v, PRODUIT p
                 where v.CODPROD = p.CODPROD
                   and v.DTRESI >= SYSDATE
                   and v.STATUT = 'A'
                   and v.NUMABONNE =SOCIO
                 GROUP BY VENTE.CODPROD,produit.CODTVA,produit.JRNLBKE,produit.JRNLVTE)
     loop
        if (i = 1) then
           monto_detec = (monto - monto_total) + reg.total;
           i := i +1;
        else
           if (monto_relativo >= reg.total) then
              monto_detec := reg.total;
           else
              if (monto_relativo > 0) then
                 monto_detec := monto_relativo;
              else
                 monto_detec = 0;
              end if;
           end if;
           insert into DETEC(NUMPIECE,CODPROD,MTT,CODTVA,NUMJRNL,CANTIDAD)
              VALUES (NUMPIECE,reg.codprod,monto_detec,reg.codtva,reg.jrnlvte,1);
           monto_detec := 0;
           c := c +1;
           monto_relativo = monto_relativo - reg.total;
        end if;
     end loop;
  end if;
  Q_SERVICIO = c;
end;
/

CREATE PROCEDURE VALIDA_REVERSION2 (NUMPIECE number, MONTO OUT NUMBER) IS
   M   number;
   VNP pagos_en_linea2.numpiece%type:=NUMPIECE;
/*
Valida que existe un pago para aplicar la correspondiente reversion
si Resultado = P , entoces si se acepta la reversión de lo contrario no
*/
BEGIN
   SELECT monto INTO M
   FROM PAGOS_EN_LINEA2
   WHERE NUMPIECE = VNP
   AND TIPO = 'P';
   MONTO := M;
END;
/

ALTER PROCEDURE INSERT_BLOC_NOTE (NUMABONNE INTEGER,LIB VARCHAR(50),DTSAISIE DATE,CODOPE VARCHAR(10))
AS
declare variable numbn integer;

begin

execute procedure incbn returning_values :numbn ;
 
insert into Blocnote 
(numBN, numabonne,lib, dtsaisie,codope) 
values 
(:numbn, :numabonne, :lib, :dtsaisie,:codope);

end^

ALTER PROCEDURE INSERT_BN_INTERNET (NUMLLAMADA INTEGER,NUMABONNE INTEGER,FECHAINI DATE,HORAINI VARCHAR(5),USUARIOINI VARCHAR(10),MOTIVO VARCHAR(4),RESPUESTA VARCHAR(4),OBSERVACION VARCHAR(4096),ESTADO CHAR(1),GRUPOLATE VARCHAR(4)) 
RETURNS  (NUMBINT INTEGER)
AS
declare variable numbnint integer;

begin

execute procedure incbnint returning_values :numbnint ;
 
insert into Averinternet 
(numllamada, numabonne, fechaini, horaini, usuarioini, motivo,respuesta,observacion,estado,grupolate) 
values 
(:numbnint, :numabonne,:fechaini,:horaini,:usuarioini,:motivo,:respuesta,:observacion,:estado,:grupolate);

numbint = numbnint;
SUSPEND;
end^

ALTER PROCEDURE INSERT_BN_INTERNETB (NUMLLAMADA INTEGER,NUMABONNE INTEGER,FECHAINI DATE,HORAINI VARCHAR(5),USUARIOINI VARCHAR(10),MOTIVO VARCHAR(4),RESPUESTA VARCHAR(4),OBSERVACION VARCHAR(4096),ESTADO CHAR(1),GRUPOLATE VARCHAR(4),PRIORIDAD INTEGER) 
RETURNS  (NUMBINT INTEGER)
AS
declare variable numbnint integer;
begin
execute procedure incbnint returning_values :numbnint;
insert into Averinternet
(numllamada, numabonne, fechaini, horaini, usuarioini, motivo,respuesta,observacion,estado,grupolate,prioridad)
values
(:numbnint,:numabonne,:fechaini,:horaini,:usuarioini,:motivo,:respuesta,:observacion,:estado,:grupolate,:prioridad);
numbint = numbnint;
SUSPEND;
end^

ALTER PROCEDURE INSERT_COLILLA (NUMABONNE INTEGER,NUMSOUSCRIPT INTEGER,NOM VARCHAR(50),DTSAISIE DATE,CODOPE VARCHAR(10))
AS
declare variable numcambio integer;

begin

execute procedure inccambio returning_values :numcambio ;

insert into CAMBIOCOLILLA
(numcambio, numabonne, numsouscript, nom, dtsaisie, codope)
values 
(:numcambio, :numabonne, :numsouscript, :nom, :dtsaisie, :codope);

END^

ALTER PROCEDURE INSERT_ECRITCPT_DETEC_F_A (NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,LIB VARCHAR(50),MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,CODOPE VARCHAR(10),MDRGLT CHAR(4),CODINSEE CHAR(5),NUMJRNL CHAR(3),RECONCIL CHAR(1),MOTIFIMP CHAR(4),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER,CODPROD CHAR(4),CODTVA CHAR(4),JRNLDET CHAR(3)) 
RETURNS  (NUMFACT INTEGER)
AS
declare variable numEC integer;
declare variable encaisse double precision;
declare variable reste double precision;
declare variable mtt double precision;


begin

execute procedure incEC returning_values :numEC ;

encaisse = 0;
 if (mttdeb = 0) then mtt=mttcred;
 if (mttcred =0) then mtt=mttdeb;
 if (typec ='FAC') then reste =mttdeb;
 if (typec ='AVR') then reste = mttCRED;

insert into ECRITCPT 
(numpiece, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, reconcil, motifimp, codope, encaisse, reste, codinsee,
 typec, cbor, resumanu) 
values 
(:numEC, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numEC, :mttdeb,
 :mttcred, :mdrglt, :reconcil, :motifimp, :codope, :encaisse, :reste, :codinsee,
 :typec, :cbor, :resumanu);




insert into DETEC 
(numpiece,codprod, mtt, codtva, numjrnl)
values 
(:numEC, :codprod, :mtt, :codtva, :jrnldet);

numfact = numec;
SUSPEND;
end^

ALTER PROCEDURE INSERT_ECRITCPT_F_A (NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,LIB VARCHAR(50),MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,CODOPE VARCHAR(10),MDRGLT CHAR(4),CODINSEE CHAR(5),NUMJRNL CHAR(3),RECONCIL CHAR(1),MOTIFIMP CHAR(4),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER) 
RETURNS  (NUMFACT INTEGER)
AS
declare variable numEC integer;
declare variable encaisse double precision;
declare variable reste double precision;


begin

execute procedure incEC returning_values :numEC ;

encaisse = 0;
 if (typec ='FAC') then reste =mttdeb;
 if (typec ='AVR') then reste = mttCRED;

insert into ECRITCPT 
(numpiece, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, reconcil, motifimp, codope, encaisse, reste, codinsee,
 typec, cbor, resumanu) 
values 
(:numEC, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numEC, :mttdeb,
 :mttcred, :mdrglt, :reconcil, :motifimp, :codope, :encaisse, :reste, :codinsee,
 :typec, :cbor, :resumanu);

numfact = numec;
SUSPEND;
end^

ALTER PROCEDURE INSERT_HISTERMINAL (NUMABONNE INTEGER,NUMSERIE CHAR(15),CODOPE CHAR(10),TIPOMOVIMIENTO CHAR(3),FECHAMOVI DATE,COMMENT VARCHAR(50))
AS
declare variable numHIST integer;

begin

execute procedure inchist returning_values :numhist ;
 
insert into HISTERMINAL
(numHIST, numabonne,numserie,fechamovi, codope,tipomovimiento,comment) 
values 
(:numHIST, :numabonne,:numserie,:fechamovi,:codope,:tipomovimiento,:comment);

end^

ALTER PROCEDURE INSERT_IMPAYE (NUMABONNE INTEGER,NUMPIECE INTEGER,MOTIF CHAR(4),LIB CHAR(25),ESTADO CHAR(1),MTT DOUBLE PRECISION,PERIODE DATE)
AS
declare variable numimp integer;

begin

execute procedure incimp returning_values :numimp ;
 
insert into impaye
(numimp, numabonne,periode,mtt,motif,estado,numpiece,lib)
values 
(:numimp, :numabonne,:periode, :mtt, :motif, :estado, :numpiece, :lib);


SUSPEND;
end^

ALTER PROCEDURE INSERT_INTERVENTION (NUMABONNE INTEGER,NUMRDV INTEGER,DTDEB DATE,HEURE CHAR(5),LIB VARCHAR(25),TIPO CHAR(4),ESTADO CHAR(1),MOTIF CHAR(4),REPONSE CHAR(4),DETMOTIF VARCHAR(50),DETREP VARCHAR(50),OPESAISIE VARCHAR(10),DTOTAVERIA DATE) 
RETURNS  (NUMITV INTEGER)
AS
declare variable numinter integer ;

begin

execute procedure incItv returning_values :numinter ;
 
insert into Intervention 
(numinter, numabonne,dtdeb, heure, lib, tipo,motif,reponse,
 opesaisie,numrdv,detmotif,detrep, dtotaveria, estado) 
values 
(:numinter, :numabonne, :dtdeb, :heure, :lib, :tipo, :motif, :reponse,
 :opesaisie, :numrdv, :detmotif, :detrep, :dtotaveria, :estado);


NUMITV = NUMINTER;
SUSPEND;
end^

ALTER PROCEDURE INSERT_INTERVENTION2 (NUMABONNE INTEGER,NUMRDV INTEGER,DTDEB DATE,HEURE CHAR(5),LIB VARCHAR(25),TIPO CHAR(4),ESTADO CHAR(1),MOTIF CHAR(4),REPONSE CHAR(4),DETMOTIF VARCHAR(50),DETREP VARCHAR(50),OPESAISIE VARCHAR(10),DTOTAVERIA DATE,GRUPOLATE VARCHAR(4)) 
RETURNS  (NUMITV INTEGER)
AS
declare variable numinter integer ;

begin

execute procedure incItv returning_values :numinter ;
 
insert into Intervention 
(numinter, numabonne,dtdeb, heure, lib, tipo,motif,reponse,
 opesaisie,numrdv,detmotif,detrep, dtotaveria, estado, grupolate) 
values 
(:numinter, :numabonne, :dtdeb, :heure, :lib, :tipo, :motif, :reponse,
 :opesaisie, :numrdv, :detmotif, :detrep, :dtotaveria, :estado, :grupolate);


NUMITV = NUMINTER;
SUSPEND;
end^

ALTER PROCEDURE INSERT_RGT_DIRECT (NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,NUMJRNL CHAR(3),LIB VARCHAR(50),NUMFACT INTEGER,MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,MDRGLT CHAR(4),CODOPE VARCHAR(10),CODINSEE CHAR(5),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER,NUMLOTE VARCHAR(10)) 
RETURNS  (NUMRGT INTEGER)
AS
declare variable numpiece integer;


begin

execute procedure incEC returning_values :numpiece;

insert into ecritcpt
(numpiece, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, reconcil, motifimp,codope, encaisse, reste, codinsee, typec, cbor, resumanu,
 numlote) 
values 
(:numpiece, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numfact, :mttdeb,
 :mttcred, :mdrglt, 'F', '', :codope, :mttcred,0, :codinsee, :typec, :cbor, :resumanu,
 :numlote);

numrgt = numpiece;
SUSPEND;

end^

ALTER PROCEDURE INSERT_RGT_DIRECT2 (NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,NUMJRNL CHAR(3),LIB VARCHAR(50),NUMFACT INTEGER,MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,MDRGLT CHAR(4),CODOPE VARCHAR(10),CODINSEE CHAR(5),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER,NUMLOTE VARCHAR(10),CODIGODEPAGO INTEGER) 
RETURNS  (NUMRGT INTEGER)
AS
declare variable numpiece integer;


begin

execute procedure incEC returning_values :numpiece;

insert into ecritcpt
(numpiece, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, reconcil, motifimp,codope, encaisse, reste, codinsee, typec, cbor, resumanu,
 numlote,codigodepago) 
values 
(:numpiece, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numfact, :mttdeb,
 :mttcred, :mdrglt, 'F', '', :codope, :mttcred,0, :codinsee, :typec, :cbor, :resumanu,
 :numlote,:codigodepago);

numrgt = numpiece;
SUSPEND;

end^

ALTER PROCEDURE INSERT_RGT_FROM_TEMPEC (NUMPIECE INTEGER,NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,NUMJRNL CHAR(3),LIB VARCHAR(50),NUMFACT INTEGER,MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,MDRGLT CHAR(4),CODOPE VARCHAR(10),CODINSEE CHAR(5),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER,NUMLOTE VARCHAR(10),MOTIFIMP CHAR(4))
AS
begin

insert into ecritcpt
(numpiece, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, reconcil, motifimp,codope, encaisse, reste, codinsee, typec, cbor, 
resumanu, numlote) 
values 
(:numpiece, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numfact, :mttdeb,
 :mttcred, :mdrglt, 'F', :motifimp, :codope, :mttcred,0, :codinsee, :typec, :cbor, :resumanu,
 :numlote);

end^

ALTER PROCEDURE INSERT_ROL (NUMINTER INTEGER,TIPO CHAR(4),DTSAISIE DATE,HRSAISIE CHAR(5),OPESAISIE VARCHAR(10),FECHACITA DATE,HRCITA CHAR(5),OBSERVACION VARCHAR(40),STATUS CHAR(1),CODINT CHAR(4),MOVIL CHAR(6),SECTOR CHAR(1),PADRINO VARCHAR(10))
AS
declare variable numrole integer ;

begin

execute procedure incRole returning_values :numrole ;
 
insert into roles
(numrole, numinter, tipo,dtsaisie, hrsaisie, opesaisie, fechacita,hrcita,
 observacion,status, codint, movil, sector, padrino) 
values 
(:numrole,:numinter, :tipo,:dtsaisie, :hrsaisie, :opesaisie, :fechacita,:hrcita,
 :observacion,:status, :codint, :movil, :sector, :padrino); 

end^

ALTER PROCEDURE INSERT_STAT_RECO (NUMABONNE INTEGER,DTRECO DATE,DTRESI DATE)
AS
declare variable numstatreco integer;

begin

execute procedure incstatreco returning_values :numstatreco ;
 
insert into StatRECO
(numstatreco, numabonne,dtreco,dtresi) 
values 
(:numstatreco, :numabonne,:dtreco,:dtresi);

end^

ALTER PROCEDURE INSERT_TEMPEC (NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,NUMJRNL CHAR(3),LIB VARCHAR(50),NUMFACT INTEGER,MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,MDRGLT CHAR(4),CODOPE VARCHAR(10),CODINSEE CHAR(5),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER,NUMLOTE VARCHAR(10),MOTIFIMP CHAR(4),LIBNC VARCHAR(50),NUMCHQ CHAR(10),BANKCHQ CHAR(25)) 
RETURNS  (NUMRGT INTEGER)
AS
declare variable numEC integer;


begin

execute procedure incEC returning_values :numEC ;

insert into TEMPEC
(numtemp, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, codope, codinsee, typec, cbor, resumanu, numlote , motifimp, libnc,
 numchq, bankchq) 
values 
(:numEC, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numfact, :mttdeb,
 :mttcred, :mdrglt, :codope, :codinsee, :typec, :cbor, :resumanu, :numlote , :motifimp,
 :libnc,:numchq, :bankchq);

numrgt = numec;
SUSPEND;
end^

ALTER PROCEDURE INSERT_TEMPEC_TEMPDETEC (NUMABONNE INTEGER,PERIODE CHAR(7),DTCPT DATE,DTSAISIE DATE,NUMJRNL CHAR(3),LIB VARCHAR(50),NUMFACT INTEGER,MTTDEB DOUBLE PRECISION,MTTCRED DOUBLE PRECISION,MDRGLT CHAR(4),CODOPE VARCHAR(10),CODINSEE CHAR(5),TYPEC CHAR(4),CBOR CHAR(4),RESUMANU INTEGER,CODPROD CHAR(4),CODTVA CHAR(4),JRNLDET CHAR(3),NUMLOTE VARCHAR(10),MOTIFIMP CHAR(4),LIBNC VARCHAR(50),NUMCHQ CHAR(10),BANKCHQ CHAR(25)) 
RETURNS  (NUMRGT INTEGER)
AS
declare variable numEC integer;
declare variable mtt double precision;


begin

execute procedure incEC returning_values :numEC ;

if (mttdeb = 0) then mtt=mttcred;
if (mttcred =0) then mtt=mttdeb;

insert into TEMPEC
(numtemp, numabonne, periode, dtcpt,  dtsaisie,  numjrnl,  lib,  numfact,  mttdeb,
 mttcred, mdrglt, codope, codinsee, typec, cbor, resumanu, numlote , motifimp, libnc,
 numchq, bankchq) 
values 
(:numEC, :numabonne, :periode, :dtcpt, :dtsaisie, :numjrnl, :lib, :numfact, :mttdeb,
 :mttcred, :mdrglt, :codope, :codinsee, :typec, :cbor, :resumanu, :numlote , :motifimp,
 :libnc,:numchq, :bankchq);




insert into TEMPDETEC 
(numtemp, codprod, mtt, codtva, numjrnl)
values 
(:numEC, :codprod, :mtt, :codtva, :jrnldet);

numrgt = numec;
SUSPEND;
end^

ALTER PROCEDURE INSERT_TRANSACDLA (DTSAISIE DATE,HEURETRANSAC CHAR(8),OPETRANSAC CHAR(10),ENVOITRANSAC CHAR(250)) 
RETURNS  (NUMTRANSAC INTEGER)
AS
declare variable numtransacdla integer;

begin

execute procedure INCTRANSACDLA returning_values :numtransacdla;
 
insert into TRANSACDLA
(numtransacdla, dtsaisie,heureTransac,envoitransac,opetransac) 
values 
(:numtransacdla, :dtsaisie,:heureTransac,:envoitransac,:opetransac); 


NUMTRANSAC = NUMTRANSACDLA;
SUSPEND;
end^

ALTER PROCEDURE INSERT_VENTE (NUMABONNE INTEGER,CODPROD CHAR(4),DTSAISIE DATE,DTSIGN DATE,MTT DOUBLE PRECISION,MTTDOL DOUBLE PRECISION,CODPROV CHAR(4),CODVEND CHAR(4),CTINST DOUBLE PRECISION,CTREDUC DOUBLE PRECISION,QTEDMD SMALLINT,STATUT CHAR(1)) 
RETURNS  (ORDREVTE INTEGER)
AS
declare variable numordre integer;

begin

execute procedure incvente returning_values :numordre ;
 
insert into Vente 
(ordre, numabonne,codprod, dtsaisie, dtsign, mtt, mttdol, codprov, codvend,
 ctinst, ctreduc,qtedmd,statut) 
values 
(:numordre, :numabonne, :codprod, :dtsaisie, :dtsign, :mtt, :mttdol, :codprov,
 :codvend, :ctinst, :ctreduc,:qtedmd, :statut);

ORDREVTE = NUMORDRE;
SUSPEND;
end^

COMMIT WORK^