å@EALTER TABLE ALMACEN ADD CONSTRAINT PK_ALMACEN PRIMARY KEY (COD_ALMA);CREATE GENERATOR ID_ALMSOB;vCREATE TRIGGER ID_ALMSOB FOR ALMSOB ACTIVE BEFORE INSERT POSITION 0 AS BEGIN new.id = gen_id(id_almsob,1); END5CREATE INDEX IDX_ALMSOB ON ALMSOB(COD_ALMA,COD_ITEM);)CREATE INDEX IDX_REGVEN2 ON REGVEN(NRUC);*CREATE INDEX IDX_REGVEN1 ON REGVEN(FECHA);?CREATE INDEX IDX_REGVEN ON REGVEN(TIP_DOC,SERIE_DOC,NUM_DOC); .CREATE INDEX IDX_PEDIDO ON PEDIDO(CODIGO_PED);¬CREATE PROCEDURE ADD_ALMA( VCOD INTEGER, VDETALLE CHAR(180) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select cod_alma from almacen where cod_alma = :vcod)) then begin vretorno = - 2; suspend; end else begin insert into almacen (cod_alma,detalle,direccion) values(:vcod,:vdetalle,:vdireccion); If (SQLCODE <> 0) then begin vretorno = -1; suspend; end else begin vretorno = 0; suspend; end end endéCREATE PROCEDURE MODI_ALMA( VCOD INTEGER, VDETALLE CHAR(180) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin Update almacen Set detalle = :vdetalle, direccion = :vdireccion Where cod_alma = :vcod; If (row_count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end endûCREATE PROCEDURE DEL_ALMA( COD INTEGER) RETURNS( VRETORNO SMALLINT) AS begin --delete from almven where cod_alma = :cod; --delete from almsal where cod_alma = :cod; --delete from almsob where cod_alma = :cod; delete from almacen where cod_alma = :cod; If (Row_Count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end endGALTER TABLE ARTICULO ADD CONSTRAINT PK_ARTICULO PRIMARY KEY (COD_ITEM);HCREATE PROCEDURE ADD_ART( VCOD INTEGER, VDETALLE CHAR(180) CHARACTER SET WIN1252, VUNID CHAR(10) CHARACTER SET WIN1252, VPESO NUMERIC(18, 3), VCODIGO CHAR(20) CHARACTER SET WIN1252, VBARRA CHAR(15) CHARACTER SET WIN1252, VGRUPAL CHAR(1) CHARACTER SET WIN1252, VCCLASE INTEGER, VCFAMILIA INTEGER, VCGRUPO INTEGER, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VIGV SMALLINT, VISC SMALLINT, VPERCEP SMALLINT, VEXON SMALLINT, VABREV CHAR(30) CHARACTER SET WIN1252, VESP CHAR(250) CHARACTER SET WIN1252, VOCULTA SMALLINT) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select cod_item from articulo where cod_item = :vcod)) then begin vretorno = - 2; suspend; end else begin insert Into articulo (cod_item,detalle,unidad,peso,codigo,barra,grupal,cod_clase,cod_familia,cod_grupo,comentario,igv,isc,percep,exon,abrev,esp,oculta) values(:vcod,:vdetalle,:vunid,:vpeso,:vcodigo,:vbarra,:vgrupal,:vcclase,:vcfamilia,:vcgrupo,:vcomentario,:vigv,:visc,:vpercep,:vexon,:vabrev,:vesp,:voculta); If (SQLCODE <> 0) then begin vretorno = -1; suspend; end else begin vretorno = 0; suspend; end end endBALTER TABLE CLASE ADD CONSTRAINT PK_CLASE PRIMARY KEY (COD_CLASE);BALTER TABLE GRUPO ADD CONSTRAINT PK_GRUPO PRIMARY KEY (COD_GRUPO);HALTER TABLE FAMILIA ADD CONSTRAINT PK_FAMILIA PRIMARY KEY (COD_FAMILIA);.CREATE PROCEDURE MODI_ART( VCOD INTEGER, VDETALLE CHAR(180) CHARACTER SET WIN1252, VUNID CHAR(10) CHARACTER SET WIN1252, VPESO NUMERIC(18, 3), VCODIGO CHAR(20) CHARACTER SET WIN1252, VBARRA CHAR(15) CHARACTER SET WIN1252, VGRUPAL CHAR(2) CHARACTER SET WIN1252, VCCLASE INTEGER, VCFAMILIA INTEGER, VCGRUPO INTEGER, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VIGV SMALLINT, VISC SMALLINT, VPERCEP SMALLINT, VEXON SMALLINT, VABREV CHAR(30) CHARACTER SET WIN1252, VESP CHAR(250) CHARACTER SET WIN1252, VOCULTA SMALLINT) RETURNS( VRETORNO SMALLINT) AS begin Update Articulo Set detalle = :vdetalle, unidad = :vunid, peso = :vpeso, codigo=:vcodigo, barra=:vbarra, grupal=:vgrupal, cod_clase=:vcclase, cod_familia=:vcfamilia, cod_grupo=:vcgrupo, comentario=:vcomentario, igv=:vigv, isc=:visc, percep=:vpercep, exon=:vexon, abrev=:vabrev, esp=:vesp, oculta=:voculta Where cod_item = :vcod; If (row_count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end end¯CREATE PROCEDURE DEL_ART( VCOD INTEGER) RETURNS( VRETORNO SMALLINT) AS begin If (exists(select cod_item from almven Where cod_item = :vcod)) then begin vretorno = -4; suspend; end else begin If (exists(select cod_item from almsal Where cod_item = :vcod and saldo>0)) then begin vretorno = -5; suspend; end else begin Delete From almsal Where cod_item = :vcod; Delete From pensal Where cod_item = :vcod; Delete From almmov Where cod_item = :vcod; Delete From Articulo Where cod_item = :vcod; If (SQLCODE <> 0) then Begin vretorno = -1; suspend; end else begin If (Row_Count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end end end end end7CREATE INDEX IDX_ALMSAL ON ALMSAL(COD_ALMA,COD_ITEM); C*CREATE INDEX IDX_ALMVEN1 ON ALMVEN(FECHA);5CREATE INDEX IDX_PENSAL ON PENSAL(COD_ALMA,COD_ITEM);7CREATE INDEX IDX_ALMMOV ON ALMMOV(COD_ALMA,COD_ITEM); C?ALTER TABLE DOI ADD CONSTRAINT PK_DOI PRIMARY KEY (CODIGO_DOI);EALTER TABLE TIPPER ADD CONSTRAINT PK_TIPPER PRIMARY KEY (CODIGO_TIP);GCREATE INDEX IDX_DOCFAC ON ALMMOV(NRUC,FTIP_DOC,FSERIE_DOC,FNUM_DOC); _DOCFAC ON ALMMOV(NRUC,FTIP_DOC,FSERIE_DOC,FNUM_DEALTER TABLE PROVEEDOR ADD CONSTRAINT PK_PROVEEDOR PRIMARY KEY (NRUC);-CREATE INDEX IDX_ALMSAL1 ON ALMSAL(COD_ITEM);?CREATE INDEX IDX_DOCMOV ON ALMMOV(TIP_DOC,SERIE_DOC,NUM_DOC); NDEX IDX_DOCFAC ON ALMMOV(NRUC,FTIP_DOC,FSERIE_DOC,FNUM_DAALTER TABLE CLIENTE ADD CONSTRAINT PK_CLIENTE PRIMARY KEY (NRUC);_DOI,NRUC);-CREATE INDEX IDX_FECHAM ON ALMMOV(FECHA_MOV);D_ITEM); C*CREATE INDEX IDX_FECHA ON ALMMOV(FECHA); ,COD_ITEM); C´CREATE PROCEDURE DEL_CLIE( VCOD CHAR(11) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin If (exists(select nruc from regven where nruc = :vcod)) then Begin vretorno = -4; End else begin Delete From cliente Where nruc = :vcod; If (row_count = 0) then vretorno = -2; else vretorno = 0; end suspend; end=CREATE INDEX IDX_ALMVEN ON ALMVEN(TIP_DOC,SERIE_DOC,NUM_DOC);ˆCREATE VIEW SALDOS( COD_ALMA, COD_ITEM, DETALLE, UNIDAD, CODIGO, PRVEN, PRVED, SALDO, COMENTARIO, UBICA, STOCKMIN, STOCKMAX, FECHA_VEN, FECHA_COM, FECHA_PRE, FECHA_PRE1, FECHA_PRE2, FECHA_PRE3, CANT_PEN, CANT_CON, CANT_SOB, CANT_RES, GRUPAL, PESO, BARRA, ABREV, COD_CLASE, COD_FAMILIA, COD_GRUPO, PRVEN1, PRVED1, PRVEN2, PRVED2, PRVEN3, PRVED3, INDICE, IGV, ISC, PERCEP, PRVEDO, PRVENO, PRVEDO1, PRVENO1, PRVEDO2, PRVENO2, PRVEDO3, PRVENO3, BLOQ, NUMOP, AVISO, CODPER, CODPER1, CODPER2, CODPER3, COMPER, COMPER1, COMPER2, COMPER3, FOTO, ESP, CODPERB, COMPERB, OCULTA) AS SELECT almsal.cod_alma, almsal.cod_item, articulo.detalle, articulo.unidad, articulo.codigo, almsal.prven, almsal.prved, almsal.saldo, articulo.comentario, almsal.ubica, almsal.stockmin, almsal.stockmax, almsal.fecha_ven, almsal.fecha_com, almsal.fecha_pre, almsal.fecha_pre1, almsal.fecha_pre2, almsal.fecha_pre3, almsal.cant_pen, almsal.cant_con, almsal.cant_sob, almsal.cant_res, articulo.grupal, articulo.peso, articulo.barra, articulo.abrev, articulo.cod_clase, articulo.cod_familia, articulo.cod_grupo, almsal.prven1, almsal.prved1, almsal.prven2, almsal.prved2, almsal.prven3, almsal.prved3, almsal.indice, articulo.igv, articulo.isc, articulo.percep,almsal.prvedo, almsal.prveno,almsal.prvedo1, almsal.prveno1, almsal.prvedo2, almsal.prveno2,almsal.prvedo3, almsal.prveno3, almsal.bloq, almsal.numop, almsal.aviso, almsal.codper, almsal.codper1, almsal.codper2, almsal.codper3, almsal.comper,almsal.comper1, almsal.comper2, almsal.comper3,articulo.foto,articulo.esp,almsal.codperb,almsal.comperb,articulo.oculta FROM almsal INNER JOIN articulo ON almsal.cod_item = articulo.cod_item ;3CREATE PROCEDURE ADD_CLIE( VNRUC CHAR(11) CHARACTER SET WIN1252, VRAZON CHAR(250) CHARACTER SET WIN1252, VAPATERNO CHAR(80) CHARACTER SET WIN1252, VAMATERNO CHAR(80) CHARACTER SET WIN1252, VNOMBRES CHAR(80) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252, VPERCEP SMALLINT, VRETEN SMALLINT, VEXON SMALLINT, VACTIVO SMALLINT, VEMAIL CHAR(200) CHARACTER SET WIN1252, VCELULAR CHAR(10) CHARACTER SET WIN1252, VTELEFONO CHAR(10) CHARACTER SET WIN1252, VOBS CHAR(250) CHARACTER SET WIN1252, VCODIGO_TIP CHAR(2) CHARACTER SET WIN1252, VCODIGO_DOI CHAR(2) CHARACTER SET WIN1252) RETURNS ( VRETORNO SMALLINT) AS begin If (vnruc Is Null or vcodigo_doi is Null) then begin vretorno = -3; suspend; end else begin if (exists(select nruc from cliente where codigo_doi=:vcodigo_doi and nruc=:vnruc)) then Begin vretorno = -2; suspend; End else begin Insert Into cliente (nruc,razon,apaterno,amaterno,nombres,direccion,percep,reten,exon,activo,email,celular,telefono,obs,codigo_tip,codigo_doi) Values(:vnruc,:vrazon,:vapaterno,:vamaterno,:vnombres,:vdireccion,:vpercep,:vreten,:vexon,:vactivo,:vemail,:vcelular,:vtelefono,:vobs,:vcodigo_tip,:vcodigo_doi); If (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; end endcCREATE PROCEDURE MODI_CLIE( VNRUC CHAR(11) CHARACTER SET WIN1252, VRAZON CHAR(250) CHARACTER SET WIN1252, VAPATERNO CHAR(80) CHARACTER SET WIN1252, VAMATERNO CHAR(80) CHARACTER SET WIN1252, VNOMBRES CHAR(80) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252, VPERCEP SMALLINT, VRETEN SMALLINT, VEXON SMALLINT, VACTIVO SMALLINT, VEMAIL CHAR(200) CHARACTER SET WIN1252, VCELULAR CHAR(10) CHARACTER SET WIN1252, VTELEFONO CHAR(10) CHARACTER SET WIN1252, VOBS CHAR(250) CHARACTER SET WIN1252, VCODIGO_TIP CHAR(2) CHARACTER SET WIN1252, VCODIGO_DOI CHAR(2) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin Update cliente Set razon = :vrazon, apaterno = :vapaterno, amaterno = :vamaterno, nombres = :vnombres, direccion=:vdireccion, percep=:vpercep, reten=:vreten, exon=:vexon, activo=:vactivo, email=:vemail, celular=:vcelular, telefono=:vtelefono, obs=:vobs, codigo_tip=:vcodigo_tip, codigo_doi=:vcodigo_doi Where nruc = :vnruc; If (row_count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end end suspend; 1CREATE PROCEDURE ADD_PROV( VNRUC CHAR(11) CHARACTER SET WIN1252, VRAZON CHAR(250) CHARACTER SET WIN1252, VAPATERNO CHAR(80) CHARACTER SET WIN1252, VAMATERNO CHAR(80) CHARACTER SET WIN1252, VNOMBRES CHAR(80) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252, VPERCEP SMALLINT, VRETEN SMALLINT, VBC SMALLINT, VACTIVO SMALLINT, VEMAIL CHAR(200) CHARACTER SET WIN1252, VCELULAR CHAR(10) CHARACTER SET WIN1252, VTELEFONO CHAR(10) CHARACTER SET WIN1252, VOBS CHAR(250) CHARACTER SET WIN1252, VCODIGO_TIP CHAR(2) CHARACTER SET WIN1252, VCODIGO_DOI CHAR(2) CHARACTER SET WIN1252) RETURNS ( VRETORNO SMALLINT) AS begin If (vnruc Is Null or vcodigo_doi is Null) then begin vretorno = -3; suspend; end else begin if (exists(select nruc from proveedor where codigo_doi=:vcodigo_doi and nruc=:vnruc)) then Begin vretorno = -2; suspend; End else begin Insert Into proveedor (nruc,razon,apaterno,amaterno,nombres,direccion,percep,reten,bc,activo,email,celular,telefono,obs,codigo_tip,codigo_doi) Values(:vnruc,:vrazon,:vapaterno,:vamaterno,:vnombres,:vdireccion,:vpercep,:vreten,:vbc,:vactivo,:vemail,:vcelular,:vtelefono,:vobs,:vcodigo_tip,:vcodigo_doi); If (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; end end_CREATE PROCEDURE MODI_PROV( VNRUC CHAR(11) CHARACTER SET WIN1252, VRAZON CHAR(250) CHARACTER SET WIN1252, VAPATERNO CHAR(80) CHARACTER SET WIN1252, VAMATERNO CHAR(80) CHARACTER SET WIN1252, VNOMBRES CHAR(80) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252, VPERCEP SMALLINT, VRETEN SMALLINT, VBC SMALLINT, VACTIVO SMALLINT, VEMAIL CHAR(200) CHARACTER SET WIN1252, VCELULAR CHAR(10) CHARACTER SET WIN1252, VTELEFONO CHAR(10) CHARACTER SET WIN1252, VOBS CHAR(250) CHARACTER SET WIN1252, VCODIGO_TIP CHAR(2) CHARACTER SET WIN1252, VCODIGO_DOI CHAR(2) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin Update proveedor Set razon = :vrazon, apaterno = :vapaterno, amaterno = :vamaterno, nombres = :vnombres, direccion=:vdireccion, percep=:vpercep, reten=:vreten, bc=:vbc, activo=:vactivo, email=:vemail, celular=:vcelular, telefono=:vtelefono, obs=:vobs, codigo_tip=:vcodigo_tip, codigo_doi=:vcodigo_doi Where nruc = :vnruc; If (row_count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end end¶CREATE PROCEDURE DEL_PROV( VCOD CHAR(11) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin If (exists(select nruc from regcom where nruc = :vcod)) then Begin vretorno = -4; End else begin Delete From proveedor Where nruc = :vcod; If (row_count = 0) then vretorno = -2; else vretorno = 0; end suspend; endgCREATE PROCEDURE GET_ALMA( VTIPO INTEGER, VCOD_ALMA INTEGER) RETURNS( VDATA VARCHAR(230) CHARACTER SET WIN1252) AS begin if (vtipo=2) then select detalle from almacen where cod_alma = :vcod_alma into :vdata; if (vtipo=3) then select direccion from almacen where cod_alma = :vcod_alma into :vdata; SUSPEND; END TCREATE PROCEDURE GET_SAL( VTIPO INTEGER, VCOD_ITEM INTEGER, VCOD_ALMA INTEGER) RETURNS( VDATA CHAR(15) CHARACTER SET WIN1252) AS begin if (vtipo=1) then select cast(iif(saldo+cant_pen-cant_res-cant_con>0,saldo+cant_pen-cant_res-cant_con,0) as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=2) then select cast(cant_pen as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=3) then select cast(cant_con as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=4) then select cast(cant_sob as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=5) then select cast(prven as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=6) then select cast(prven1 as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=7) then select cast(prven2 as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=8) then select cast(prven3 as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=9) then select cast(prved as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=10) then select cast(prved1 as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=11) then select cast(prved2 as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=12) then select cast(prved3 as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=13) then select ubica from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=14) then select cast(saldo as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=15) then select cast(numop as char(15)) from almsal where cod_item = :vcod_item and cod_alma=:vcod_alma into :vdata; if (vtipo=16) then select cast(sum(saldo+cant_pen) as char(15)) from almsal where cod_item = :vcod_item into :vdata; if (vtipo=17) then select cast(sum(cant) as char(15)) from almven where cod_item = :vcod_item and anulado <> 'A' into :vdata; SUSPEND; ENDNDCREATE PROCEDURE GET_ART( VTIPO INTEGER, VCOD_ITEM INTEGER) RETURNS( VDATA VARCHAR(65) CHARACTER SET WIN1252) AS begin if (vtipo=1) then select detalle from articulo where cod_item=:vcod_item into vdata; if (vtipo=2) then select unidad from articulo where cod_item=:vcod_item into vdata; if (vtipo=3) then select codigo from articulo where cod_item=:vcod_item into vdata; if (vtipo=4) then select cast (peso as char (10)) from articulo where cod_item=:vcod_item into vdata; if (vtipo=5) then select barra from articulo where cod_item=:vcod_item into vdata; if (vtipo=6) then select grupal from articulo where cod_item=:vcod_item into vdata; if (vtipo=7) then select cod_clase from articulo where cod_item=:vcod_item into vdata; if (vtipo=8) then select cod_familia from articulo where cod_item=:vcod_item into vdata; if (vtipo=9) then select cod_grupo from articulo where cod_item=:vcod_item into vdata; if (vtipo=10) then select cast(igv as char(1)) from articulo where cod_item=:vcod_item into vdata; if (vtipo=11) then select cast(isc as char(1)) from articulo where cod_item=:vcod_item into vdata; if (vtipo=12) then select cast(percep as char(1)) from articulo where cod_item=:vcod_item into vdata; --if (vtipo=13) then --select first 1 trim(cast(subtot/cant as char(65)))|| '|' ||inigv || '|' ||moneda || '|' ||trim(cast(cant as char(65)))|| '|' ||trim(cast(fecha as char(65))) from compras where cod_item=:vcod_item order by fecha desc into vdata; if (vtipo=14) then select cast(exon as char(1)) from articulo where cod_item=:vcod_item into vdata; SUSPEND; ENDKALTER TABLE LISPRECIO ADD CONSTRAINT PK_LISPRECIO PRIMARY KEY (CODIGO_LIS);ÒCREATE PROCEDURE ADD_PED( VCODIGO_PED INTEGER, VDETALLE CHAR(180) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VPU NUMERIC(18, 2), VDCTO NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VCODIGO CHAR(20) CHARACTER SET WIN1252, VIGV NUMERIC(18, 3), VISC NUMERIC(18, 3), VPERCEP NUMERIC(18, 3), VCOD_ITEM INTEGER, VPOS INTEGER, VUBICA CHAR(10) CHARACTER SET WIN1252, VEXON INTEGER, VTIPOIGV CHAR(2) CHARACTER SET WIN1252, VTIPOISC CHAR(2) CHARACTER SET WIN1252) RETURNS( VRETORNO INTEGER) AS begin insert into pedido (codigo_ped,detalle,cant,pu,dcto,total,unidad,codigo,cod_item,pos,igv,isc,percep,ubica,exon,tipoigv,tipoisc) values(:vcodigo_ped,:vdetalle,:vcant,:vpu,:vdcto,:vtotal,:vunidad,:vcodigo,:vcod_item,:vpos,:vigv,:visc,:vpercep,:vubica,:vexon,:vtipoigv,:vtipoisc); if (SQLCODE <> 0) then vretorno = -1; else vretorno = 0; suspend; endëCREATE PROCEDURE GEN_COD( VTIPO INTEGER) RETURNS( VDATA INTEGER) AS begin if (vtipo=1) then begin select max(cod_alma) from almacen into :vdata; if (vdata is Null) then vdata = 1; else vdata = vdata + 1; end if (vtipo=2) then begin select max(cod_item) from articulo into :vdata; if (vdata is Null) then vdata = 1; else vdata = vdata + 1; end if (vtipo=3) then begin select max(codigo_ped) from pedido into :vdata; if (vdata is Null) then vdata = 1; else vdata = vdata + 1; end SUSPEND; END;oCREATE PROCEDURE GET_CLIE( VTIPO INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252) RETURNS( VDATA VARCHAR(240) CHARACTER SET WIN1252) AS begin if (vtipo=1) then select nruc from cliente where nruc=:vnruc into :vdata; if (vtipo=2) then select razon from cliente where nruc=:vnruc into :vdata; if (vtipo=3) then select direccion from cliente where nruc=:vnruc into :vdata; if (vtipo=4) then select percep from cliente where nruc=:vnruc into :vdata; if (vtipo=5) then select reten from cliente where nruc=:vnruc into :vdata; if (vtipo=6) then select exon from cliente where nruc=:vnruc into :vdata; if (vtipo=7) then select nombres from cliente where nruc=:vnruc into :vdata; if (vtipo=8) then select apaterno from cliente where nruc=:vnruc into :vdata; if (vtipo=9) then select amaterno from cliente where nruc=:vnruc into :vdata; if (vtipo=10) then select cast(cliente as char(240) character set WIN1252) from cliente where nruc=:vnruc into :vdata; SUSPEND; END;LCREATE PROCEDURE GET_PER( VPASS CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO CHAR(3) CHARACTER SET WIN1252) AS declare variable vcodper char(3); begin select codper from personal where pass = :vpass into :vcodper; if (vcodper is Null) then vretorno =''; else vretorno = vcodper; SUSPEND; END ENDEALTER TABLE PERSONAL ADD CONSTRAINT PK_PERSONAL PRIMARY KEY (CODPER);RS);CREATE PROCEDURE FIND_DOCB( VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VESTACION CHAR(25) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select num_doc from regven where tip_doc = :vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc)) then vretorno = 1; else begin update spool set documento = :vtip_doc||'-'||:vserie_doc||'-'||:vnum_doc, estacion = :vestacion, fecha = cast(current_timestamp as char(25)); vretorno = 0; end suspend; END’CREATE PROCEDURE FIND_DOC( VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select num_doc from regven where tip_doc = :vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc)) then vretorno = 1; else vretorno = 0; suspend; ENDúCREATE PROCEDURE GET_SPL RETURNS( VRETORNO SMALLINT) AS begin if(exists(select documento from spool where trim(documento) = '0' or documento is Null or documento = ' ')) then vretorno = 1; else vretorno = 0; SUSPEND; ENDnCREATE PROCEDURE SP_REGVEN( VFECHA TIMESTAMP, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VNRUC CHAR(11) CHARACTER SET WIN1252, VCLIENTE CHAR(240) CHARACTER SET WIN1252, VVVENTA NUMERIC(18, 2), VIGV NUMERIC(18, 2), VPVENTA NUMERIC(18, 2), VTIPO CHAR(2) CHARACTER SET WIN1252, VDIAS SMALLINT, VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VDIRECCION CHAR(230) CHARACTER SET WIN1252, VNUMDEU CHAR(5) CHARACTER SET WIN1252, VPERCEP NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VCRETEN SMALLINT, VCEXON SMALLINT, VCPERCEP SMALLINT, VORDCOM CHAR(15) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252, VCONSIG SMALLINT, VTOTINF NUMERIC(18, 2), VTOTEXO NUMERIC(18, 2), VTOTDGL NUMERIC(18, 2), VTOTISC NUMERIC(18, 2), VTRGR NUMERIC(18, 2)) RETURNS( VRETORNO SMALLINT) AS begin if (vfecha is null) then vfecha = current_date; insert Into regven (fecha,tip_doc,serie_doc,num_doc,nruc,cliente,vventa,igv,pventa,percep,total,anulado,tipo,dias,pago,moneda,tc,direccion,numdeu,creten,cexon,cpercep,ordcom,codper,consig,vinaf,vexon,dctog,isc,vtrgr) values(:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vnruc,:vcliente,:vvventa,:vigv,:vpventa,:vpercep,:vtotal,' ',:vtipo,:vdias,0,:vmoneda,:vtc,:vdireccion,:vnumdeu,:vcreten,:vcexon,:vcpercep,:vordcom,:vcodper,:vconsig,:vtotinf,:vtotexo,:vtotdgl,:vtotisc,:vtrgr); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; End vretorno = 0; suspend; end ¯CREATE PROCEDURE SP_ALMVEN( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(150) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VPU NUMERIC(18, 3), VDCTO NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VTIPOIGV CHAR(2) CHARACTER SET WIN1252, VTIPOISC CHAR(2) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VIGV NUMERIC(18, 3), VISC NUMERIC(18, 3), VPERCEP NUMERIC(18, 3), VUBICA CHAR(10) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VCANK NUMERIC(18, 2); DECLARE VARIABLE VCANS NUMERIC(18, 2); Begin if (vfecha is null) then vfecha = current_date; if (vcod_item <> 0) then begin select saldo,numop from almsal where cod_item = :vcod_item and cod_alma = :vcod_alma into :vsaldo,:vnumop; if (vcant <= vsaldo) then begin vsaldo = vsaldo - vcant; vcank = vcant; vcans =0; end else begin vcank = vsaldo; vcans = vcant - vsaldo; if (vcod_item <> 0) then insert into almsob (cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_item,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo = 0; end if (vcank > 0) then vnumop = vnumop + 1 ; update almsal set saldo = :vsaldo, numop = :vnumop, fecha_ven = :vfecha, cant_sob = cant_sob + :vcans where cod_item = :vcod_item and cod_alma = :vcod_alma; if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,cod_user,tipmod,codper) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-VENTA',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'01',' ',:vnumop,' ',:vcod_user,'Ventas.Nuevo',:vcodper); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; end insert into almven (cod_alma ,cod_item,detalle,fecha,tip_doc,serie_doc,num_doc,cant,unidad,pu,dcto,total,moneda,tc,cant_pen,cant_dev,tipoigv,tipoisc,anulado,cod_user,pos,igv,isc,percep,ubica) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vpu,:vdcto,:vtotal,:vmoneda,:vtc,0,0,:vtipoigv,:vtipoisc,' ',:vcod_user,:vpos,:vigv,:visc,:vpercep,:vubica); if (SQLCODE <> 0 ) then Begin vretorno = -1; suspend; End vretorno = 0; suspend; endendCREATE VIEW VENTAS( COD_ALMA, COD_ITEM, UNIDAD, DETALLE, FECHA, TIP_DOC, SERIE_DOC, NUM_DOC, CANT, PU, DCTO, SUBTOT, MONEDA, TC, CANT_PEN, CANT_DEV, TIPOIGV, TIPOISC, ANULADO, COD_USER, POS, UBICA, IIGV, IISC, IPERCEP, IEXON, NRUC, CLIENTE, DIRECCION, NUMDEU, VVENTA, IGV, PVENTA, PERCEP, TOTAL, COD_CLASE, COD_FAMILIA, COD_GRUPO, PESO, REFER, COMENTARIO, MOTIVO, OPBANCO, ORDCOM, CODPER, CEXON, CPERCEP, CRETEN, CONSIG, TIPO, DETALLER, CODIGO, TIGV, TISC, DCTOG, ISC, VEXON, VINAF, VTRGR, RFECHA, RTIP_DOC, RSERIE_DOC, RNUM_DOC, SCOD1, SCOD2) AS SELECT almven.cod_alma, almven.cod_item, almven.unidad, almven.detalle, almven.fecha, almven.tip_doc, almven.serie_doc, almven.num_doc, almven.cant, almven.pu, almven.dcto, almven.total as subtot, almven.moneda, almven.tc, almven.cant_pen, almven.cant_dev, almven.tipoigv,almven.tipoisc,almven.anulado, almven.cod_user, almven.pos, almven.ubica, articulo.igv as iigv, articulo.isc as iisc, articulo.percep as ipercep, articulo.exon as iexon, regven.nruc, regven.cliente, regven.direccion, regven.numdeu, regven.vventa, regven.igv, regven.pventa, regven.percep, regven.total, articulo.cod_clase, articulo.cod_familia, articulo.cod_grupo,articulo.peso, regven.refer, regven.comentario,regven.motivo, regven.opbanco,regven.ordcom,regven.codper,regven.cexon,regven.cpercep,regven.creten,regven.consig,regven.tipo,articulo.detalle as detaller,articulo.codigo,almven.igv as tigv,almven.isc as tisc,regven.dctog,regven.isc,regven.vexon,regven.vinaf,regven.vtrgr,regven.rfecha,regven.rtip_doc,regven.rserie_doc,regven.rnum_doc,regven.scod1,regven.scod2 FROM almven LEFT OUTER JOIN articulo ON almven.cod_item = articulo.cod_item LEFT OUTER JOIN regven ON almven.tip_doc = regven.tip_doc AND almven.serie_doc = regven.serie_doc AND almven.num_doc = regven.num_doc ;QCREATE PROCEDURE SP_MODCON( VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VFLAG SMALLINT) RETURNS( VRETORNO INTEGER) AS declare vanulado char(1); declare vcod_alma integer; declare vcod_item integer; declare vcant numeric(10,2); declare vconsig smallint; declare curven cursor for (select cod_alma,cod_item,egreso from almmov where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc); BEGIN select consig,anulado from regven where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc into :vconsig,:vanulado; if (vanulado is null) then begin vretorno = -2; end else begin if (vanulado = 'A') then vretorno = -3; else begin if (vconsig = 1) then begin open curven; fetch curven into :vcod_alma,:vcod_item,:vcant; while (row_count <>0) do begin if (vflag = 1) then update almsal set cant_con = cant_con + :vcant where cod_item = :vcod_item and cod_alma = :vcod_alma; else update almsal set cant_con = cant_con - :vcant where cod_item = :vcod_item and cod_alma = :vcod_alma; fetch curven into :vcod_alma,:vcod_item,:vcant; end vretorno = 0; end end end SUSPEND; END fCREATE PROCEDURE SP_EGRALMS( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VCANK NUMERIC(18, 2); DECLARE VARIABLE VCANS NUMERIC(18, 2); Begin if (vfecha is null) then vfecha = current_date; if (vcod_item <> 0) then begin select saldo,numop from almsal where cod_item = :vcod_item and cod_alma = :vcod_alma into :vsaldo,:vnumop; if (vcant <= vsaldo) then begin vsaldo = vsaldo - vcant; vcank = vcant; vcans =0; end else begin vcank = vsaldo; vcans = vcant - vsaldo; if (vcod_item <> 0) then insert into almsob (cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_item,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo = 0; end if (vcank > 0) then vnumop = vnumop + 1 ; update almsal set saldo = :vsaldo, numop = :vnumop, fecha_ven = :vfecha, cant_sob = cant_sob + :vcans where cod_item = :vcod_item and cod_alma = :vcod_alma; if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-VENTA',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'01',' ',:vnumop,' '); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; exit; End end vretorno = 0; suspend; end endŒCREATE PROCEDURE SP_DELALM( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VINGRE NUMERIC(18, 2), VEGRE NUMERIC(18, 2), VNUMOPM INTEGER) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcant_sob numeric (10,2); declare vtip_docs char(2); declare vserie_docs char(4); declare vnum_docs char(7); declare vcants numeric(10,2); declare vfechas timestamp; declare vfecha_mov timestamp; declare vnrucs char(11); declare vprovclies char(65); declare vanulado char(1); declare vids int; declare vtipop char(2); declare cursob cursor for (select tip_doc,serie_doc,num_doc,cant,fecha,nruc,provclie,anulado,id from almsob where cod_item=:vcod_item and cod_alma=:vcod_alma order by id); Begin vfecha_mov = current_timestamp; select saldo,numop,cant_sob from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop,:vcant_sob; if (vingre > 0) then vsaldo = vsaldo - vingre; if (vegre > 0) then vsaldo = vsaldo + vegre; vnumop = vnumop - 1; if (vsaldo < 0) then begin vretorno = -3; suspend; exit; end select tipop from almmov where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm into :vtipop; delete from almmov where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm; update almmov set numop = numop - 1, saldo =saldo + :vegre - :vingre where cod_item=:vcod_item and cod_alma=:vcod_alma and numop>:vnumopm; update almsal set saldo=:vsaldo, numop=:vnumop where cod_item=:vcod_item and cod_alma=:vcod_alma; if (vtipop = '04') then begin if (vingre > 0) then update almsal set cant_con = cant_con - :vingre where cod_item=:vcod_item and cod_alma=:vcod_alma; if (vegre > 0) then update almsal set cant_con = cant_con + :vegre where cod_item=:vcod_item and cod_alma=:vcod_alma; end if (vcant_sob <> 0 and vsaldo > 0) then begin open cursob; fetch cursob into :vtip_docs,:vserie_docs,:vnum_docs,:vcants,:vfechas,:vnrucs,:vprovclies,:vanulado,:vids; while (row_count<>0) do begin if (vsaldo >= vcants) then begin vsaldo = :vsaldo - :vcants; vnumop = :vnumop + 1; vcant_sob = vcant_sob - vcants; insert Into almmov (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado) values(:vcod_alma,:vcod_item,:vfecha_mov,:vfechas,:vtip_docs,:vserie_docs,:vnum_docs,'EGRESO-VENTA',0,:vcants,:vsaldo,1,:vnrucs,:vprovclies,'01',' ',:vnumop,:vanulado); delete from almsob where id=:vids; end else begin update almsob set cant=:vcants-:vsaldo where id=:vids; vcants = vsaldo; vnumop = vnumop + 1; vcant_sob = vcant_sob - vcants; vsaldo = 0; insert into almmov (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado) values(:vcod_alma,:vcod_item,:vfecha_mov,:vfechas,:vtip_docs,:vserie_docs,:vnum_docs,'EGRESO-VENTA',0,:vcants,:vsaldo,1,:vnrucs,:vprovclies,'01',' ',:vnumop,:vanulado); leave; end fetch cursob into :vtip_docs,:vserie_docs,:vnum_docs,:vcants,:vfechas,:vnrucs,:vprovclies,:vanulado,:vids; end close cursob; update almsal set saldo=:vsaldo, numop=:vnumop, cant_sob=:vcant_sob where cod_item=:vcod_item and cod_alma=:vcod_alma; end vretorno = 0; suspend; endèCREATE PROCEDURE SP_DELPEN( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VINGRE NUMERIC(18, 2), VEGRE NUMERIC(18, 2), VNUMOPM INTEGER) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vtip_docs char(2); declare vserie_docs char(4); declare vnum_docs char(7); declare vcants numeric(10,2); declare vfechas timestamp; declare vfecha_mov timestamp; declare vnrucs char(11); declare vprovclies char(65); declare vanulado char(1); declare vids int; Begin vfecha_mov = current_timestamp; select saldo,numop from pensal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; if (vingre > 0) then vsaldo = vsaldo - vingre; if (vegre > 0) then vsaldo = vsaldo + vegre; vnumop = vnumop - 1; if (vsaldo>0) then begin vretorno = -3; suspend; exit; end select tip_doc,serie_doc,num_doc from almpen where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm into :vtip_docs,:vserie_docs,:vnum_docs; delete from almpen where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm; update almpen set numop = numop - 1, saldo =saldo + :vegre - :vingre where cod_item=:vcod_item and cod_alma=:vcod_alma and numop>:vnumopm; update pensal set saldo=:vsaldo, numop=:vnumop where cod_item=:vcod_item and cod_alma=:vcod_alma; update almsal set cant_pen = cant_pen + :vegre where cod_item=:vcod_item and cod_alma=:vcod_alma; update almven set cant_pen = cant_pen - :vegre where tip_doc=:vtip_docs and serie_doc=:vserie_docs and num_doc=:vnum_docs and cod_item = :vcod_item; vretorno = 0; suspend; endCREATE PROCEDURE SP_ANULDOC( VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO INTEGER) AS declare vcod_alma integer; declare vcod_item integer; declare vingre numeric(10,2); declare vegre numeric(10,2); declare vnumop integer; declare vret integer; declare vid integer; declare vfecha timestamp; declare vnruc char(11); declare vcliente char(65); declare curalm cursor for (select cod_alma,cod_item,ingreso,egreso,numop from almmov where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc); declare curpen cursor for (select cod_alma,cod_item,ingreso,egreso,numop from almpen where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc); declare cursob cursor for (select cod_alma,cod_item,cant,id from almsob where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc); declare curven cursor for (select fecha,cod_alma,cod_item,cant,nruc,cliente from ventas where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc); BEGIN if (exists(select anulado from almven where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc and anulado='A')) then begin select :vret from sp_modcon (:vtip_doc,:vserie_doc,:vnum_doc,2) into :vret; update regven set anulado='' where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc; update almven set anulado='' where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc; open curven; fetch curven into :vfecha,:vcod_alma,:vcod_item,:vegre,:vnruc,:vcliente; while (row_count <>0) do begin select :vret from sp_egralms (:vfecha,:vcod_alma,:vcod_item,:vtip_doc,:vserie_doc,:vnum_doc,:vegre,:vnruc,:vcliente) into :vret; fetch curven into :vfecha,:vcod_alma,:vcod_item,:vegre,:vnruc,:vcliente; end end else begin select :vret from sp_modcon (:vtip_doc,:vserie_doc,:vnum_doc,1) into :vret; update regven set anulado='A' where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc; update almven set anulado='A' where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc; open cursob; fetch cursob into :vcod_alma,:vcod_item,:vegre,:vid; while(row_count <> 0) do begin update almsal set cant_sob = cant_sob - :vegre where cod_alma = :vcod_alma and cod_item = :vcod_item; delete from almsob where id = :vid; fetch cursob into :vcod_alma,:vcod_item,:vegre,:vid; end open curalm; fetch curalm into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; while(row_count<>0) do begin select :vret from sp_delalm (:vcod_alma,:vcod_item,:vingre,:vegre,:vnumop) into :vret; fetch curalm into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; end /*cambiado*/ open curpen; fetch curpen into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; while(row_count<>0) do begin select :vret from sp_delpen (:vcod_alma,:vcod_item,:vingre,:vegre,:vnumop) into :vret; fetch curpen into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; end end vretorno = 0; suspend; ENDmCREATE PROCEDURE GET_PROV( VTIPO INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252) RETURNS( VDATA VARCHAR(240) CHARACTER SET WIN1252) AS begin if (vtipo=1) then select nruc from proveedor where nruc=:vnruc into :vdata; if (vtipo=2) then select razon from proveedor where nruc=:vnruc into :vdata; if (vtipo=3) then select direccion from proveedor where nruc=:vnruc into :vdata; if (vtipo=4) then select percep from proveedor where nruc=:vnruc into :vdata; if (vtipo=5) then select reten from proveedor where nruc=:vnruc into :vdata; if (vtipo=6) then select bc from proveedor where nruc=:vnruc into :vdata; if (vtipo=7) then select nombres from proveedor where nruc=:vnruc into :vdata; if (vtipo=8) then select apaterno from proveedor where nruc=:vnruc into :vdata; if (vtipo=9) then select amaterno from proveedor where nruc=:vnruc into :vdata; if (vtipo=10) then select cast(proveedor as char(240)) from proveedor where nruc=:vnruc into :vdata; SUSPEND; END;D;CREATE PROCEDURE SP_ADDALM( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VFECHA_MOV TIMESTAMP, VCU NUMERIC(9, 2), VTIPOP CHAR(2) CHARACTER SET WIN1252, VDOCUMENTO CHAR(15) CHARACTER SET WIN1252, VDESCRIPCION CHAR(50) CHARACTER SET WIN1252, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcant_sob numeric (10,2); declare vtip_docs char(2); declare vserie_docs char(4); declare vnum_docs char(7); declare vcants numeric(10,2); declare vfechas timestamp; declare vfecham timestamp; declare vnrucs char(11); declare vprovclies char(65) CHARACTER SET WIN1252; declare vids int; declare variable cursob cursor for (select tip_doc,serie_doc,num_doc,cant,fecha,nruc,provclie,id from almsob where cod_item=:vcod_item and cod_alma=:vcod_alma order by id); Begin if (exists(select cod_item from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma)) then begin select saldo,numop,cant_sob from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop,:vcant_sob; vsaldo = vsaldo + vcant; vnumop = vnumop + 1; insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,comentario) values(:vcod_alma,:vcod_item,:vfecha,:vfecha_mov,:vtip_doc,:vserie_doc,:vnum_doc,:vdescripcion,:vcant,0,:vsaldo,:vcu,:vnruc,:vprovclie,:vtipop,:vdocumento,:vnumop,' ',:vcomentario); vfecham =current_timestamp; if (vtipop = '02') then update almsal set saldo=:vsaldo, fecha_com = :vfecha_mov,numop=:vnumop where cod_item=:vcod_item and cod_alma=:vcod_alma; else update almsal set saldo=:vsaldo, numop=:vnumop where cod_item=:vcod_item and cod_alma=:vcod_alma; if (vcant_sob<>0) then begin open cursob; fetch cursob into :vtip_docs,:vserie_docs,:vnum_docs,:vcants,:vfechas,:vnrucs,:vprovclies,:vids; while (row_count <> 0) do begin if (vsaldo >= vcants) then begin vsaldo = vsaldo - vcants; vnumop = vnumop + 1; vcant_sob = vcant_sob - vcants; insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado) values(:vcod_alma,:vcod_item,:vfechas,:vfecham,:vtip_docs,:vserie_docs,:vnum_docs,'EGRESO-VENTA',0,:vcants,:vsaldo,1,:vnrucs,:vprovclies,'01',' ',:vnumop,' '); delete from almsob where id=:vids; end else begin update almsob set cant=:vcants-:vsaldo where id=:vids; vcants = vsaldo; vnumop = vnumop + 1; vcant_sob = vcant_sob - vcants; vsaldo = 0; insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado) values(:vcod_alma,:vcod_item,:vfechas,:vfecham,:vtip_docs,:vserie_docs,:vnum_docs,'EGRESO-VENTA',0,:vcants,:vsaldo,1,:vnrucs,:vprovclies,'01',' ',:vnumop,' '); leave; end fetch cursob into :vtip_docs,:vserie_docs,:vnum_docs,:vcants,:vfechas,:vnrucs,:vprovclies,:vids; end close cursob; update almsal set saldo=:vsaldo, numop=:vnumop, cant_sob=:vcant_sob where cod_item=:vcod_item and cod_alma=:vcod_alma; end vretorno = 0; suspend; end else begin vretorno = -2; suspend; end endÁCREATE PROCEDURE SP_EGRALM( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VFECHA_MOV TIMESTAMP, VCU NUMERIC(18, 2), VTIPOP CHAR(2) CHARACTER SET WIN1252, VDOCUMENTO CHAR(15) CHARACTER SET WIN1252, VDESCRIPCION CHAR(50) CHARACTER SET WIN1252, VREFER CHAR(50) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VCANT_SOB NUMERIC(18, 2); Begin if (exists(select cod_item from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma)) then begin select saldo, numop, cant_sob from almsal where cod_item = :vcod_item and cod_alma = :vcod_alma into :vsaldo,:vnumop,:vcant_sob; vsaldo = vsaldo - vcant; if (vsaldo < 0) then begin vretorno = -3; suspend; exit; end else begin vnumop = vnumop + 1; insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,refer) values(:vcod_alma,:vcod_item,:vfecha,:vfecha_mov,:vtip_doc,:vserie_doc,:vnum_doc,:vdescripcion,0,:vcant,:vsaldo,:vcu,:vnruc,:vprovclie,:vtipop,:vdocumento,:vnumop,' ',:vrefer); update almsal set saldo = :vsaldo, numop=:vnumop where cod_item = :vcod_item and cod_alma = :vcod_alma; if (vtipop = '04') then begin update almsal set cant_con = cant_con - :vcant where cod_item = :vcod_item and cod_alma = :vcod_alma; end vretorno = 0; suspend ; end end else begin vretorno = -2; suspend ; end endCREATE PROCEDURE SP_MODALM( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VINGRESO NUMERIC(18, 2), VEGRESO NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VFECHA_MOV TIMESTAMP, VCU NUMERIC(9, 2), VTIPOP CHAR(2) CHARACTER SET WIN1252, VDOCUMENTO CHAR(15) CHARACTER SET WIN1252, VDESCRIPCION CHAR(50) CHARACTER SET WIN1252, VNUMOPM INTEGER, VINGRESOA NUMERIC(18, 2), VEGRESOA NUMERIC(18, 2)) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcant_sob numeric (10,2); declare vtip_docs char(2); declare vserie_docs char(4); declare vnum_docs char(7); declare vcants numeric(10,2); declare vfechas timestamp; declare vfecham timestamp; declare vnrucs char(11); declare vprovclies char(65); declare vanulado char(1); declare vids int; declare cursob cursor for (select tip_doc,serie_doc,num_doc,cant,fecha,nruc,provclie,anulado,id from almsob where cod_item=:vcod_item and cod_alma=:vcod_alma order by id); Begin select saldo,numop,cant_sob from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop,:vcant_sob; vsaldo = vsaldo+((vingreso-vegreso)- (vingresoa-vegresoa)); vfecham = current_timestamp; if (vsaldo < 0) then begin vretorno = -3; suspend; exit; end else begin update almsal set saldo=:vsaldo where cod_item=:vcod_item and cod_alma=:vcod_alma; if (vtipop = '04') then begin update almsal set cant_con = cant_con + ((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where cod_item=:vcod_item and cod_alma=:vcod_alma; end update almmov set fecha=:vfecha,fecha_mov=:vfecha_mov, tip_doc=:vtip_doc, serie_doc=:vserie_doc, num_doc=:vnum_doc, descripcion=:vdescripcion, ingreso=:vingreso, egreso=:vegreso, saldo=saldo+((:vingreso-:vegreso)- (:vingresoa-:vegresoa)), cu=:vcu, nruc=:vnruc, provclie=:vprovclie, tipop=:vtipop, documento=:vdocumento where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm; update almmov set saldo=saldo+((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where cod_item=:vcod_item and cod_alma=:vcod_alma and numop>:vnumopm; if (vcant_sob<>0 and vsaldo>0) then begin open cursob; fetch cursob into :vtip_docs,:vserie_docs,:vnum_docs,:vcants,:vfechas,:vnrucs,:vprovclies,:vanulado,:vids; while (row_count <> 0) do begin if (vsaldo >= vcants) then begin vsaldo = vsaldo - vcants; vnumop = vnumop + 1; vcant_sob = vcant_sob - vcants; insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,ftip_doc,fserie_doc,fnum_doc) values(:vcod_alma,:vcod_item,:vfechas,:vfecham,:vtip_docs,:vserie_docs,:vnum_docs,'EGRESO-VENTA',0,:vcants,:vsaldo,1,:vnrucs,:vprovclies,'01',' ',:vnumop,:vanulado,'','',''); delete from almsob where id=:vids; end else begin update almsob set cant=:vcants-:vsaldo where id=:vids; vcants = vsaldo; vnumop = vnumop + 1; vcant_sob = vcant_sob - vcants; vsaldo = 0; insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,ftip_doc,fserie_doc,fnum_doc) values(:vcod_alma,:vcod_item,:vfechas,:vfecha_mov,:vtip_docs,:vserie_docs,:vnum_docs,'EGRESO-VENTA',0,:vcants,:vsaldo,1,:vnrucs,:vprovclies,'01',' ',:vnumop,:vanulado,'','',''); leave; end fetch cursob into :vtip_docs,:vserie_docs,:vnum_docs,:vcants,:vfechas,:vnrucs,:vprovclies,:vanulado,:vids; end close cursob; update almsal set saldo=:vsaldo, numop=:vnumop, cant_sob=:vcant_sob where cod_item=:vcod_item and cod_alma=:vcod_alma; end vretorno = 0; suspend; end end XCREATE PROCEDURE SP_EGRPEN( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VFECHA_MOV TIMESTAMP, VCU NUMERIC(18, 2), VTIPOP CHAR(2) CHARACTER SET WIN1252, VDOCUMENTO CHAR(15) CHARACTER SET WIN1252, VDESCRIPCION CHAR(50) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS DECLARE VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VSALPEN NUMERIC(18, 2); Begin if (exists(select cod_item from pensal where cod_item=:vcod_item and cod_alma=:vcod_alma)) then begin select saldo, numop from pensal where cod_item = :vcod_item and cod_alma = :vcod_alma into :vsaldo,:vnumop; vsaldo = vsaldo - vcant; select cant_pen from almsal where cod_item = :vcod_item and cod_alma = :vcod_alma into :vsalpen; vsalpen = vsalpen - vcant; vnumop = vnumop + 1; update almpen set egreso = egreso + :vcant where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc and cod_item = :vcod_item and cod_alma = :vcod_alma; if (row_count = 0) then begin insert into almpen (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,codper) values(:vcod_alma,:vcod_item,:vfecha,:vfecha_mov,:vtip_doc,:vserie_doc,:vnum_doc,:vdescripcion,0,:vcant,:vsaldo,:vcu,:vnruc,:vprovclie,:vtipop,:vdocumento,:vnumop,' ',:vcodper); update pensal set saldo = :vsaldo, numop=:vnumop where cod_item = :vcod_item and cod_alma = :vcod_alma; end else begin update pensal set saldo = :vsaldo where cod_item = :vcod_item and cod_alma = :vcod_alma; end update almsal set cant_pen = :vsalpen where cod_item = :vcod_item and cod_alma = :vcod_alma; update almven set cant_pen = cant_pen + :vcant where tip_doc=:vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc and cod_item = :vcod_item; vretorno = 0; suspend ; end else begin vretorno = -2; suspend ; end end5CREATE INDEX IDX_ALMCON ON ALMCON(COD_ALMA,COD_ITEM);DCREATE INDEX IDX_ALMCOM ON ALMCOM(NRUC,TIP_DOC,SERIE_DOC,NUM_DOC); *CREATE INDEX IDX_ALMCOM1 ON ALMCOM(FECHA);-CREATE INDEX IDX_ALMCOM2 ON ALMCOM(COD_ITEM);}CREATE VIEW COMPRAS( FECHA, TIP_DOC, SERIE_DOC, NUM_DOC, NRUC, PROVEEDOR, COD_ITEM, CANT, UNIDAD, DETALLE, PU, DCTO1, DCTO2, DCTO3, DCTO4, DCTO5, DCTO6, SUBTOT, CFLETE, COTR1, COTR2, CCU, POS, VVENTA, IGV, PVENTA, PERCEP, TOTAL, TIPO, MONEDA, TC, INIGV, COMENTARIO, PIGV, COD_FAMILIA, COD_CLASE) AS SELECT almcom.fecha,almcom.tip_doc,almcom.serie_doc,almcom.num_doc,almcom.nruc,regcom.proveedor, almcom.cod_item,almcom.cant,almcom.unidad,almcom.detalle,almcom.pu,almcom.dcto1,almcom.dcto2,almcom.dcto3, almcom.dcto4,almcom.dcto5,almcom.dcto6,almcom.total as subtot,almcom.cflete,almcom.cotr1,almcom.cotr2,almcom.ccu,almcom.pos, regcom.vventa,regcom.igv,regcom.pventa,regcom.percep,regcom.total,regcom.tipo,regcom.moneda,regcom.tc,regcom.inigv,regcom.comentario,regcom.pigv,articulo.cod_familia,articulo.cod_clase FROM almcom LEFT JOIN regcom ON almcom.nruc=regcom.nruc and almcom.tip_doc=regcom.tip_doc and almcom.serie_doc=regcom.serie_doc and almcom.num_doc=regcom.num_doc LEFT JOIN articulo on almcom.cod_item=articulo.cod_item ;BALTER TABLE COTCAB ADD CONSTRAINT PK_COTCAB PRIMARY KEY (NUM_COT);CREATE GENERATOR ID_PAGOS;sCREATE TRIGGER ID_PAGOS FOR PAGOS ACTIVE BEFORE INSERT POSITION 0 AS BEGIN new.id = gen_id(id_PAGOS,1); ENDENDÝCREATE PROCEDURE SP_DELSPL RETURNS( VRETORNO SMALLINT) AS begin update spool set documento='',estacion='',fecha=''; if(row_count=0) then vretorno = 1; else vretorno = 0; SUSPEND; END,CREATE INDEX IDX_ALMGRU ON ALMGRU(COD_ITEM);„CREATE PROCEDURE SP_ALMGRU( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(65) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VPU NUMERIC(18, 3), VDCTO NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VTIPOIGV CHAR(2) CHARACTER SET WIN1252, VTIPOISC CHAR(2) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VIGV NUMERIC(18, 3), VISC NUMERIC(18, 3), VPERCEP NUMERIC(18, 3), VUBICA CHAR(10) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS DECLARE VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VCANK NUMERIC(18, 2); DECLARE VARIABLE VCANS NUMERIC(18, 2); DECLARE VARIABLE VCOD_ITEMG INTEGER; DECLARE VARIABLE VCANG NUMERIC(18, 2); DECLARE curven cursor for (select cod_itemg,cant from almgru where cod_item = :vcod_item); Begin if (vfecha is null) then vfecha = current_date; if (vcod_item <> 0 and exists(select cod_item from articulo where cod_item=:vcod_item and grupal='S')) then begin open curven; fetch curven into :vcod_itemg,:vcang; while (row_count <>0) do begin select saldo,numop from almsal where cod_item = :vcod_itemg and cod_alma = :vcod_alma into :vsaldo,:vnumop; if (vcant*vcang <= vsaldo) then begin vsaldo = vsaldo - (vcant*vcang); vcank = (vcant*vcang); vcans =0; end else begin vcank = vsaldo; vcans = (vcant*vcang) - vsaldo; if (vcod_item <> 0) then insert into almsob (cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_itemg,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo = 0; end if (vcank > 0) then vnumop = vnumop + 1 ; update almsal set saldo = :vsaldo, numop = :vnumop, fecha_ven = :vfecha, cant_sob = cant_sob + :vcans where cod_item = :vcod_itemg and cod_alma = :vcod_alma; if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,cod_user,tipmod,codper) values(:vcod_alma,:vcod_itemg,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-VENTA',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'01',' ',:vnumop,' ',:vcod_user,'Ventas.Nuevo',:vcodper); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; fetch curven into :vcod_itemg,:vcang; end end insert into almven (cod_alma ,cod_item,detalle,fecha,tip_doc,serie_doc,num_doc,cant,unidad,pu,dcto,total,moneda,tc,cant_pen,cant_dev,tipoigv,tipoisc,anulado,cod_user,pos,igv,isc,percep,ubica) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vpu,:vdcto,:vtotal,:vmoneda,:vtc,0,0,:vtipoigv,:vtipoisc,' ',:vcod_user,:vpos,:vigv,:visc,:vpercep,:vubica); if (SQLCODE <> 0 ) then Begin vretorno = -1; suspend; End vretorno = 0; suspend; endêCREATE PROCEDURE SP_SOBKAR( VCOD_ALMAA INTEGER, VID INTEGER, VCOD_ITEM INTEGER, VCOD_ALMAC INTEGER) RETURNS( VRETORNO SMALLINT) AS DECLARE VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VFECHA TIMESTAMP; DECLARE VARIABLE VFECHA_MOV TIMESTAMP; DECLARE VARIABLE VCOD_ITEMS INTEGER; DECLARE VARIABLE VTIP_DOC CHAR(2); DECLARE VARIABLE VSERIE_DOC CHAR(4); DECLARE VARIABLE VNUM_DOC CHAR(7); DECLARE VARIABLE VCANT NUMERIC(18, 2); DECLARE VARIABLE VNRUC CHAR(11); DECLARE VARIABLE VPROVCLIE CHAR(65) CHARACTER SET WIN1252; DECLARE VARIABLE VANULADO CHAR(1); Begin if (not exists(select cod_item from almsal where cod_item=:vcod_item and cod_alma=:vcod_almac)) then begin vretorno = -3; suspend; exit; end vfecha_mov = current_timestamp; select saldo, numop from almsal where cod_item = :vcod_item and cod_alma = :vcod_almac into :vsaldo,:vnumop; select fecha,cod_item,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado from almsob where id= :vid into :vfecha, :vcod_items, :vtip_doc, :vserie_doc, :vnum_doc, :vcant, :vnruc, :vprovclie, :vanulado; if (vsaldo0) do begin select :vret from sp_addalm (:vcod_alma,:vcod_item,:vtip_doc,:vserie_doc,:vnum_doc,:vegre,:vnruc,:vcliente,:vfecha,:vfecha,:vcu,'05','','INGRESO-DEVOLUCION','') into :vret; fetch curven into :vfecha,:vcod_alma,:vcod_item,:vegre,:vnruc,:vcliente,:vcu; end open cursob; fetch cursob into :vcod_alma,:vcod_item,:vegre,:vid; while(row_count <> 0) do begin update almsal set cant_sob = cant_sob - :vegre where cod_alma = :vcod_alma and cod_item = :vcod_item; delete from almsob where id = :vid; fetch cursob into :vcod_alma,:vcod_item,:vegre,:vid; end end else begin select :vret from sp_modcon (:vtip_doc,:vserie_doc,:vnum_doc,1) into :vret; update regven set anulado='A' where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc; update almven set anulado='A' where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc; open curalm; fetch curalm into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; while(row_count<>0) do begin select :vret from sp_delalm (:vcod_alma,:vcod_item,:vingre,:vegre,:vnumop) into :vret; fetch curalm into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; end /*cambiado*/ open curpen; fetch curpen into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; while(row_count<>0) do begin select :vret from sp_delpen (:vcod_alma,:vcod_item,:vingre,:vegre,:vnumop) into :vret; fetch curpen into :vcod_alma,:vcod_item,:vingre,:vegre,:vnumop; end end vretorno = 0; suspend; END  CREATE PROCEDURE SP_VMOVSAL( VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vingre numeric(10,2); declare vcod_item integer; declare vcod_alma integer; declare curalm cursor for (select cod_alma,cod_item,ingreso from almmov where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc); BEGIN vretorno = 0; open curalm; fetch curalm into :vcod_alma,:vcod_item,:vingre; while(row_count<>0) do begin select saldo from almsal where cod_alma = :vcod_alma and cod_item = :vcod_item into :vsaldo; if (:vsaldo - :vingre < 0) then begin vretorno = 1; end fetch curalm into :vcod_alma,:vcod_item,:vingre; end /* Procedure body */ SUSPEND; ENDNCREATE VIEW RETENCION( FECHA, TIP_DOC, SERIE_DOC, NUM_DOC, NRUC, PROVEEDOR, OTIP_DOC, OSERIE_DOC, ONUM_DOC, OFECHA, OREFER, TMONEDA, PVENTA, NUMPAG, IMPPAG, IMPRET, TOTAL, TOTRET, MONEDA, TC, POS, ANULADO, TASA, SCOD1) AS SELECT regret.fecha, almret.tip_doc, almret.serie_doc, almret.num_doc, regret.nruc, regret.proveedor, almret.otip_doc, almret.oserie_doc, almret.onum_doc, almret.ofecha, almret.orefer, almret.moneda as tmoneda,almret.pventa,almret.numpag, almret.imppag, almret.impret, regret.total,regret.totret,regret.moneda,regret.tc,almret.pos,regret.anulado,regret.tasa,regret.scod1 FROM almret LEFT OUTER JOIN regret ON almret.tip_doc = regret.tip_doc AND almret.serie_doc = regret.serie_doc AND almret.num_doc = regret.num_doc ;ìCREATE PROCEDURE SP_REGRET( VFECHA TIMESTAMP, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVEEDOR CHAR(80) CHARACTER SET WIN1252, VTOTAL NUMERIC(18, 2), VTOTRET NUMERIC(18, 2), VANULADO CHAR(1) CHARACTER SET WIN1252, VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VCODPER CHAR(3) CHARACTER SET WIN1252, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VTASA NUMERIC(5, 2)) RETURNS( VRETORNO SMALLINT) AS begin If ((vtip_doc Is Null) or (vserie_doc Is Null) or (vnum_doc Is Null)) then begin vretorno = -3; suspend; end else begin if (exists(select tip_doc||serie_doc||num_doc from regret where tip_doc||serie_doc||num_doc=:vtip_doc||:vserie_doc||:vnum_doc)) then Begin vretorno = -2; suspend; End else begin insert into regret (fecha,tip_doc,serie_doc,num_doc,nruc,proveedor,total,totret,anulado,moneda,tc,codper,comentario,tasa) Values(:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vnruc,:vproveedor,:vtotal,:vtotret,:vanulado,:vmoneda,:vtc,:vcodper,:vcomentario,:vtasa); If (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; end end endÂCREATE PROCEDURE SP_ALMRET( VFECHA TIMESTAMP, VNRUC CHAR(11) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VOTIP_DOC CHAR(2) CHARACTER SET WIN1252, VOSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VONUM_DOC CHAR(7) CHARACTER SET WIN1252, VOFECHA TIMESTAMP, VOREFER CHAR(50) CHARACTER SET WIN1252, VPVENTA NUMERIC(18, 2), VIMPPAG NUMERIC(18, 2), VIMPRET NUMERIC(18, 4), VANULADO CHAR(1) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNUMPAG SMALLINT, VMONEDA CHAR(1) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (vfecha is null) then vfecha = current_timestamp; insert into almret (tip_doc,serie_doc,num_doc,otip_doc,oserie_doc,onum_doc,ofecha,orefer,pventa,imppag,impret,anulado,cod_user,pos,numpag,moneda) values(:vtip_doc,:vserie_doc,:vnum_doc,:votip_doc,:voserie_doc,:vonum_doc,:vofecha,:vorefer,:vpventa,:vimppag,:vimpret,:vanulado,:vcod_user,:vpos,:vnumpag,:vmoneda); if (SQLCODE <> 0) then begin vretorno = -1; suspend; end vretorno = 0; suspend; end end+CREATE INDEX IDX_ALMRET1 ON ALMRET(OFECHA);?CREATE INDEX IDX_ALMRET ON ALMRET(TIP_DOC,SERIE_DOC,NUM_DOC); )CREATE INDEX IDX_REGRET2 ON REGRET(NRUC);*CREATE INDEX IDX_REGRET1 ON REGRET(FECHA);=CREATE INDEX IDX_REGRET ON REGRET(TIP_DOC,SERIE_DOC,NUM_DOC);]ALTER TABLE COTDET ADD CONSTRAINT FK_COTDET FOREIGN KEY (NUM_COT) REFERENCES COTCAB(NUM_COT);éCREATE PROCEDURE ADD_COTCAB( VNUM_COT CHAR(7) CHARACTER SET WIN1252, VFECHA DATE, VNRUC CHAR(11) CHARACTER SET WIN1252, VCLIENTE CHAR(240) CHARACTER SET WIN1252, VPVENTA NUMERIC(18, 2), VPERCEP NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VTITULO CHAR(25) CHARACTER SET WIN1252, VDIRECCION CHAR(230) CHARACTER SET WIN1252, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VDIAS SMALLINT, VVALIDEZ DATE, VMONEDA CHAR(1) CHARACTER SET WIN1252, VATENCION CHAR(50) CHARACTER SET WIN1252, VCODPER CHAR(10) CHARACTER SET WIN1252) RETURNS( VRETORNO INTEGER) AS begin insert into cotcab (num_cot,fecha,nruc,cliente,pventa,percep,total,titulo,direccion,comentario,dias,validez,moneda,atencion,codper) values(:vnum_cot,:vfecha,:vnruc,:vcliente,:vpventa,:vpercep,:vtotal,:vtitulo,:vdireccion,:vcomentario,:vdias,:vvalidez,:vmoneda,:vatencion,:vcodper); if (SQLCODE <> 0) then vretorno = -1; else vretorno = 0; suspend; endICREATE PROCEDURE ADD_COTDET( VNUM_COT CHAR(7) CHARACTER SET WIN1252, VDETALLE CHAR(65) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VPU NUMERIC(18, 2), VDCTO NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VCODIGO CHAR(15) CHARACTER SET WIN1252, VIGV NUMERIC(18, 3), VISC NUMERIC(18, 3), VPERCEP NUMERIC(18, 3), VCOD_ITEM INTEGER, VPOS INTEGER, VUBICA CHAR(10) CHARACTER SET WIN1252) RETURNS( VRETORNO INTEGER) AS begin insert into cotdet (num_cot,detalle,cant,pu,dcto,total,unidad,codigo,cod_item,pos,igv,isc,percep,ubica) values(:vnum_cot,:vdetalle,:vcant,:vpu,:vdcto,:vtotal,:vunidad,:vcodigo,:vcod_item,:vpos,:vigv,:visc,:vpercep,:vubica); if (SQLCODE <> 0) then vretorno = -1; else vretorno = 0; suspend; endÁCREATE VIEW COTIZACION( NUM_COT, FECHA, TITULO, CLIENTE, NRUC, DIRE, COMENTARIO, DIAS, VALIDEZ, MONEDA, ATENCION, CODPER, PVENTA, PERCEP, TOTAL, DETALLE, CANT, PU, IGV, ISC, PERCEPD, DCTO, TOTALD, UNIDAD, CODIGO, COD_ITEM, POS, UBICA, PESO, SAL1, SAL2, SAL3) AS SELECT A.NUM_COT,A.FECHA,A.TITULO,A.CLIENTE,A.NRUC,A.DIRECCION,A.COMENTARIO,A.DIAS,A.VALIDEZ,A.MONEDA,A.ATENCION,A.CODPER,A.PVENTA,A.PERCEP,A.TOTAL, B.DETALLE,B.CANT,B.PU,B.IGV,B.ISC,B.PERCEP AS PERCEPD,B.DCTO,B.TOTAL AS TOTALD,B.UNIDAD,B.CODIGO,B.COD_ITEM,B.POS,B.UBICA,C.PESO,D.SAL1,D.SAL2,D.SAL3 FROM COTCAB A LEFT JOIN COTDET B ON A.NUM_COT=B.NUM_COT LEFT JOIN ARTICULO C ON B.COD_ITEM=C.COD_ITEM LEFT JOIN (SELECT COD_ITEM,SUM(IIF(COD_ALMA=1,SALDO,0)) AS SAL1,SUM(IIF(COD_ALMA=2,SALDO,0)) AS SAL2,SUM(IIF(COD_ALMA=3,SALDO,0)) AS SAL3 FROM ALMSAL GROUP BY COD_ITEM) D ON B.COD_ITEM=D.COD_ITEM ;ªCREATE PROCEDURE SP_REGCOM( VFECHA TIMESTAMP, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVEEDOR CHAR(65) CHARACTER SET WIN1252, VVVENTA NUMERIC(18, 2), VIGV NUMERIC(18, 2), VPVENTA NUMERIC(18, 2), VTIPO CHAR(1) CHARACTER SET WIN1252, VDIAS SMALLINT, VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VPERCEP NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VORDCOM CHAR(10) CHARACTER SET WIN1252, VINIGV CHAR(1) CHARACTER SET WIN1252, VCOMEN VARCHAR(250) CHARACTER SET WIN1252, VREDONDEO NUMERIC(18, 2), VCODIGO_PER CHAR(2) CHARACTER SET WIN1252, VASIENTO CHAR(7) CHARACTER SET WIN1252, VPAGO NUMERIC(18, 2)) RETURNS( VRETORNO SMALLINT) AS begin if (vfecha is null) then vfecha = current_date; if (exists(select nruc from regcom where nruc=:vnruc and tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc)) then begin vretorno = -2; suspend; end else begin insert into regcom (fecha,tip_doc,serie_doc,num_doc,nruc,proveedor,vventa,igv,pventa,tipo,dias,moneda,tc,percep,total,pago,ordcom,inigv,comentario,redondeo,codigo_per,asiento) values(:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vnruc,:vproveedor,:vvventa,:vigv,:vpventa,:vtipo,:vdias,:vmoneda,:vtc,:vpercep,:vtotal,:vpago,:vordcom,:vinigv,:vcomen,:vredondeo,:vcodigo_per,:vasiento); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; end endCREATE PROCEDURE SP_MODREGCOM( VANTDOC CHAR(24) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVEEDOR CHAR(65) CHARACTER SET WIN1252, VVVENTA NUMERIC(18, 2), VIGV NUMERIC(18, 2), VPVENTA NUMERIC(18, 2), VTIPO CHAR(1) CHARACTER SET WIN1252, VDIAS SMALLINT, VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VPERCEP NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VORDCOM CHAR(10) CHARACTER SET WIN1252, VINIGV CHAR(1) CHARACTER SET WIN1252, VCOMEN VARCHAR(250) CHARACTER SET WIN1252, VREDONDEO NUMERIC(18, 2), VCODIGO_PER CHAR(2) CHARACTER SET WIN1252, VASIENTO CHAR(7) CHARACTER SET WIN1252, VPAGO NUMERIC(18, 2)) RETURNS( VRETORNO SMALLINT) AS begin update regcom set fecha = :vfecha,tip_doc = :vtip_doc,serie_doc = :vserie_doc,num_doc = :vnum_doc,nruc = :vnruc,proveedor = :vproveedor, vventa = :vvventa,igv = :vigv,pventa = :vpventa,tipo = :vtipo,dias = :vdias,moneda = :vmoneda,tc = :vtc,percep = :vpercep,total = :vtotal, ordcom = :vordcom,inigv = :vinigv,comentario = :vcomen,redondeo = :vredondeo,codigo_per = :vcodigo_per, asiento = :vasiento, pago = :vpago where nruc||tip_doc||serie_doc||num_doc = :vantdoc; if (ROW_COUNT = 0) then begin vretorno = -2; suspend; end else begin vretorno = 0; suspend; end endYALTER TABLE PRODCAB ADD CONSTRAINT PK_PRODCAB PRIMARY KEY (TTIP_DOC,TSERIE_DOC,TNUM_DOC);BCREATE INDEX IDX_PRODDET ON PRODDET(PTIP_DOC,PSERIE_DOC,PNUM_DOC); éCREATE PROCEDURE SP_PROCALMPROD( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(65) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDESCRIPCIO CHAR(65) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcank numeric(10,2); declare vcans numeric(10,2); begin if (vfecha is null) then vfecha = current_timestamp; select saldo,numop from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; if (vcant<=vsaldo) then begin vsaldo = vsaldo - vcant; vcank = vcant; vcans = 0; end else begin vcank = vsaldo; vcans = vcant - vsaldo; if (vcod_item<>0) then insert into almsob(cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_item,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo=0; end if (vcank > 0) then vnumop =vnumop + 1; if (vcod_item<>0) then update almsal set saldo=:vsaldo, numop=:vnumop, fecha_ven=:vfecha, cant_sob= cant_sob+:vcans where cod_item=:vcod_item and cod_alma=:vcod_alma; insert into proddet (cod_alma ,cod_item,detalle,fecha,ptip_doc,pserie_doc,pnum_doc,cant,unidad,ttip_doc,tserie_doc,tnum_doc,anulado,cod_user,pos,descripcion) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vttip_doc,:vtserie_doc,:vtnum_doc,' ',:vcod_user,:vpos,:vdescripcio); if (SQLCODE <> 0) then begin vretorno = -1; exit; end if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-PRODUCCION',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'10',' ',:vnumop,' '); if (SQLCODE <> 0) then Begin vretorno = -1; exit; end end end jCREATE PROCEDURE SP_PROCALMPROD( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(65) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDESCRIPCIO CHAR(65) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcank numeric(10,2); declare vcans numeric(10,2); begin if (vfecha is null) then vfecha = current_timestamp; select saldo,numop from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; if (vcant<=vsaldo) then begin vsaldo = vsaldo - vcant; vcank = vcant; vcans = 0; end else begin vcank = vsaldo; vcans = vcant - vsaldo; if (vcod_item<>0) then insert into almsob(cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_item,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo=0; end if (vcank > 0) then vnumop =vnumop + 1; if (vcod_item<>0) then update almsal set saldo=:vsaldo, numop=:vnumop, fecha_ven=:vfecha, cant_sob= cant_sob+:vcans where cod_item=:vcod_item and cod_alma=:vcod_alma; insert into proddet (cod_alma ,cod_item,detalle,fecha,ptip_doc,pserie_doc,pnum_doc,cant,unidad,ttip_doc,tserie_doc,tnum_doc,anulado,cod_user,pos,descripcion,codper) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vttip_doc,:vtserie_doc,:vtnum_doc,' ',:vcod_user,:vpos,:vdescripcio,:vcodper); if (SQLCODE <> 0) then begin vretorno = -1; SUSPEND; exit; end if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,codper) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-PRODUCCION',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'10',' ',:vnumop,' ',:vcodper); if (SQLCODE <> 0) then Begin vretorno = -1; SUSPEND; exit; end end end;}CREATE PROCEDURE ADD_OTRA( VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDETALLE CHAR(80) CHARACTER SET WIN1252, VFECHA DATE, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select * from prodcab where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc)) then begin vretorno = -2; suspend; end else begin insert into prodcab (ttip_doc,tserie_doc,tnum_doc,cant,unidad,detalle,fecha,comentario,tip_doc,serie_doc,num_doc) values(:vttip_doc,:vtserie_doc,:vtnum_doc,:vcant,:vunidad,:vdetalle,:vfecha,:vcomentario,:vtip_doc,:vserie_doc,:vnum_doc); if (SQLCODE <> 0) then begin vretorno = -1; suspend; end end vretorno = 0; suspend; end.CREATE PROCEDURE MODI_OTRA( VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDETALLE CHAR(80) CHARACTER SET WIN1252, VFECHA DATE, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin update prodcab Set cant = :vcant, unidad = :vunidad, detalle = :vdetalle, fecha = :vfecha, comentario = :vcomentario, tip_doc=:vtip_doc, serie_doc=:vserie_doc, num_doc=:vnum_doc where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc; If (row_count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end end€CREATE PROCEDURE MODI_OTRA( VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDETALLE CHAR(80) CHARACTER SET WIN1252, VFECHA DATE, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCOD_PROD CHAR(2) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin update prodcab Set cant = :vcant, unidad = :vunidad, detalle = :vdetalle, fecha = :vfecha, comentario = :vcomentario, tip_doc=:vtip_doc, serie_doc=:vserie_doc, num_doc=:vnum_doc, cod_prod=:vcod_prod where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc; If (row_count = 0) then Begin vretorno = -2; suspend; End else begin vretorno = 0; suspend; end end¾CREATE PROCEDURE ADD_OTRA( VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDETALLE CHAR(80) CHARACTER SET WIN1252, VFECHA DATE, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCOD_PROD CHAR(2) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select * from prodcab where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc)) then begin vretorno = -2; suspend; end else begin insert into prodcab (ttip_doc,tserie_doc,tnum_doc,cant,unidad,detalle,fecha,comentario,tip_doc,serie_doc,num_doc,cod_prod) values(:vttip_doc,:vtserie_doc,:vtnum_doc,:vcant,:vunidad,:vdetalle,:vfecha,:vcomentario,:vtip_doc,:vserie_doc,:vnum_doc,:vcod_prod); if (SQLCODE <> 0) then begin vretorno = -1; suspend; end end vretorno = 0; suspend; endƒCREATE PROCEDURE GET_PROV( VTIPO INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252) RETURNS( VDATA VARCHAR(240) CHARACTER SET WIN1252) AS begin if (vtipo=1) then select nruc from proveedor where nruc=:vnruc into :vdata; if (vtipo=2) then select razon from proveedor where nruc=:vnruc into :vdata; if (vtipo=3) then select direccion from proveedor where nruc=:vnruc into :vdata; if (vtipo=4) then select percep from proveedor where nruc=:vnruc into :vdata; if (vtipo=5) then select reten from proveedor where nruc=:vnruc into :vdata; if (vtipo=6) then select bc from proveedor where nruc=:vnruc into :vdata; if (vtipo=7) then select nombres from proveedor where nruc=:vnruc into :vdata; if (vtipo=8) then select apaterno from proveedor where nruc=:vnruc into :vdata; if (vtipo=9) then select amaterno from proveedor where nruc=:vnruc into :vdata; if (vtipo=10) then select cast(proveedor as char(240) character set WIN1252) from proveedor where nruc=:vnruc into :vdata; SUSPEND; END; ±CREATE PROCEDURE SP_PROCALMPROD( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(65) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VCU NUMERIC(18, 2), VDESCRIPCIO CHAR(65) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcank numeric(10,2); declare vcans numeric(10,2); begin if (vfecha is null) then vfecha = current_timestamp; if (vcod_item = 0) then begin insert into proddet (cod_alma ,cod_item,detalle,fecha,ptip_doc,pserie_doc,pnum_doc,cant,unidad,ttip_doc,tserie_doc,tnum_doc,anulado,cod_user,pos,descripcion,codper) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vttip_doc,:vtserie_doc,:vtnum_doc,' ',:vcod_user,:vpos,:vdescripcio,:vcodper); if (SQLCODE <> 0) then begin vretorno = -1; SUSPEND; exit; end end select saldo,numop from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; if (vcant<=vsaldo) then begin vsaldo = vsaldo - vcant; vcank = vcant; vcans = 0; end else begin vcank = vsaldo; vcans = vcant - vsaldo; if (vcod_item<>0) then insert into almsob(cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_item,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo=0; end if (vcank > 0) then vnumop =vnumop + 1; if (vcod_item<>0) then begin update almsal set saldo=:vsaldo, numop=:vnumop, fecha_ven=:vfecha, cant_sob= cant_sob+:vcans where cod_item=:vcod_item and cod_alma=:vcod_alma; insert into proddet (cod_alma ,cod_item,detalle,fecha,ptip_doc,pserie_doc,pnum_doc,cant,unidad,ttip_doc,tserie_doc,tnum_doc,anulado,cod_user,pos,descripcion,cu,codper) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vttip_doc,:vtserie_doc,:vtnum_doc,' ',:vcod_user,:vpos,:vdescripcio,:vcu,:vcodper); if (SQLCODE <> 0) then begin vretorno = -1; SUSPEND; exit; end if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,codper) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-PRODUCCION',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'10',' ',:vnumop,' ',:vcodper); if (SQLCODE <> 0) then Begin vretorno = -1; SUSPEND; exit; end end end end ¶CREATE PROCEDURE SP_ALMPROD( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(65) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VCU NUMERIC(18, 2), VDESCRIPCIO CHAR(65) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vcank numeric(10,2); declare vcans numeric(10,2); begin if (vfecha is null) then vfecha = current_timestamp; if (vcod_item = 0) then begin insert into proddet (cod_alma ,cod_item,detalle,fecha,ptip_doc,pserie_doc,pnum_doc,cant,unidad,ttip_doc,tserie_doc,tnum_doc,anulado,cod_user,pos,descripcion,cu,codper) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vttip_doc,:vtserie_doc,:vtnum_doc,' ',:vcod_user,:vpos,:vdescripcio,:vcu,:vcodper); if (SQLCODE <> 0) then begin vretorno = -1; SUSPEND; exit; end end select saldo,numop from almsal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; if (vcant<=vsaldo) then begin vsaldo = vsaldo - vcant; vcank = vcant; vcans = 0; end else begin vcank = vsaldo; vcans = vcant - vsaldo; if (vcod_item<>0) then insert into almsob(cod_alma,cod_item,fecha,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado) values (:vcod_alma ,:vcod_item,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcans,:vnruc,:vprovclie,' '); vsaldo=0; end if (vcank > 0) then vnumop =vnumop + 1; if (vcod_item<>0) then begin update almsal set saldo=:vsaldo, numop=:vnumop, fecha_ven=:vfecha, cant_sob= cant_sob+:vcans where cod_item=:vcod_item and cod_alma=:vcod_alma; insert into proddet (cod_alma ,cod_item,detalle,fecha,ptip_doc,pserie_doc,pnum_doc,cant,unidad,ttip_doc,tserie_doc,tnum_doc,anulado,cod_user,pos,descripcion,cu,codper) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vttip_doc,:vtserie_doc,:vtnum_doc,' ',:vcod_user,:vpos,:vdescripcio,:vcu,:vcodper); if (SQLCODE <> 0) then begin vretorno = -1; SUSPEND; exit; end if (vcank > 0) then begin if (vcod_item <> 0) then insert Into almmov (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,codper) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-PRODUCCION',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'10',' ',:vnumop,' ',:vcodper); if (SQLCODE <> 0) then Begin vretorno = -1; SUSPEND; exit; end end end end;end; ‚CREATE PROCEDURE SP_SOBPEN( VCOD_ALMAA INTEGER, VID INTEGER, VCOD_ITEM INTEGER, VCOD_ALMAC INTEGER, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS DECLARE VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VFECHA TIMESTAMP; DECLARE VARIABLE VFECHA_MOV TIMESTAMP; DECLARE VARIABLE VCOD_ITEMS INTEGER; DECLARE VARIABLE VTIP_DOC CHAR(2); DECLARE VARIABLE VSERIE_DOC CHAR(4); DECLARE VARIABLE VNUM_DOC CHAR(7); DECLARE VARIABLE VCANT NUMERIC(18, 2); DECLARE VARIABLE VNRUC CHAR(11); DECLARE VARIABLE VPROVCLIE CHAR(65) CHARACTER SET WIN1252; DECLARE VARIABLE VANULADO CHAR(1); Begin if (not exists(select cod_item from almsal where cod_item=:vcod_item and cod_alma=:vcod_almac)) then begin vretorno = -3; suspend; exit; end vfecha_mov = current_timestamp; select saldo, numop from pensal where cod_item = :vcod_item and cod_alma = :vcod_almac into :vsaldo,:vnumop; select fecha,cod_item,tip_doc,serie_doc,num_doc,cant,nruc,provclie,anulado from almsob where id= :vid into :vfecha, :vcod_items, :vtip_doc, :vserie_doc, :vnum_doc, :vcant, :vnruc, :vprovclie, :vanulado; vsaldo = vsaldo - vcant; vnumop = vnumop + 1; update almsal set cant_sob= cant_sob - :vcant, cant_pen = cant_pen - :vcant where cod_item = :vcod_items and cod_alma = :vcod_almaa; update pensal set saldo = :vsaldo, numop = :vnumop where cod_item = :vcod_item and cod_alma = :vcod_almac; delete from almsob where id= :vid; update almpen set egreso = egreso + :vcant where tip_doc = :vtip_doc and serie_doc = :vserie_doc and num_doc = :vnum_doc and cod_item = :vcod_item; if (row_count = 0) then begin update pensal set saldo = :vsaldo, numop = :vnumop where cod_item = :vcod_item and cod_alma = :vcod_almac; insert Into almpen (cod_alma,cod_item,fecha_mov,fecha,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,codper) values( :vcod_almac, :vcod_item, :vfecha_mov, :vfecha, :vtip_doc, :vserie_doc, :vnum_doc,'EGRESO-VENTA',0, :vcant, :vsaldo,1, :vnruc, :vprovclie,'01',' ', :vnumop,' ',:vcodper); end else begin update pensal set saldo = :vsaldo where cod_item = :vcod_item and cod_alma = :vcod_almac; end update almven set cant_pen = cant_pen + :vcant where tip_doc=:vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc and cod_item = :vcod_item and cod_alma = :vcod_almaa; vretorno = 0; suspend; end; oCREATE PROCEDURE SP_ALMPEN( VFECHA TIMESTAMP, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VDETALLE CHAR(65) CHARACTER SET WIN1252, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCANT NUMERIC(18, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VPU NUMERIC(18, 3), VDCTO NUMERIC(18, 2), VTOTAL NUMERIC(18, 2), VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(18, 3), VTIPOIGV CHAR(2) CHARACTER SET WIN1252, VTIPOISC CHAR(2) CHARACTER SET WIN1252, VCOD_USER INTEGER, VPOS INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VIGV NUMERIC(18, 3), VISC NUMERIC(18, 3), VPERCEP NUMERIC(18, 3), VUBICA CHAR(10) CHARACTER SET WIN1252, VCODPER CHAR(3) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS declare VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VCANK NUMERIC(18, 2); DECLARE VARIABLE VCANS NUMERIC(18, 2); Begin if (vfecha is null) then vfecha = current_date; if (vcod_item <> 0) then begin select saldo,numop from pensal where cod_item = :vcod_item and cod_alma = :vcod_alma into :vsaldo,:vnumop; vsaldo = vsaldo - vcant; vcank = vcant; vcans =0; if (vcank > 0) then vnumop = vnumop + 1 ; update pensal set saldo = :vsaldo, numop = :vnumop where cod_item = :vcod_item and cod_alma = :vcod_alma; update almsal set cant_pen = cant_pen - :vcant, fecha_ven = :vfecha where cod_item = :vcod_item and cod_alma = :vcod_alma; if (vcank > 0) then begin if (vcod_item <> 0) then insert into almpen (cod_alma,cod_item,fecha,fecha_mov,tip_doc,serie_doc,num_doc,descripcion,ingreso,egreso,saldo,cu,nruc,provclie,tipop,documento,numop,anulado,codper) values(:vcod_alma,:vcod_item,:vfecha,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,'EGRESO-VENTA',0,:vcank,:vsaldo,1,:vnruc,:vprovclie,'01',' ',:vnumop,' ',:vcodper); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; End end vretorno = 0; suspend; end insert into almven (cod_alma ,cod_item,detalle,fecha,tip_doc,serie_doc,num_doc,cant,unidad,pu,dcto,total,moneda,tc,cant_pen,cant_dev,tipoigv,tipoisc,anulado,cod_user,pos,igv,isc,percep,ubica) values(:vcod_alma ,:vcod_item,:vdetalle,:vfecha,:vtip_doc,:vserie_doc,:vnum_doc,:vcant,:vunidad,:vpu,:vdcto,:vtotal,:vmoneda,:vtc,0,0,:vtipoigv,:vtipoisc,' ',:vcod_user,:vpos,:vigv,:visc,:vpercep,:vubica); if (SQLCODE <> 0 ) then Begin vretorno = -1; suspend; End update almven set cant_pen = cant_pen + :vcant where tip_doc=:vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc and cod_item = :vcod_item; vretorno = 0; suspend; endBCREATE INDEX IDX_REGGUIA ON REGGUIA(MTIP_DOC,MSERIE_DOC,MNUM_DOC);BCREATE INDEX IDX_ALMGUIA ON ALMGUIA(MTIP_DOC,MSERIE_DOC,MNUM_DOC);ÊCREATE PROCEDURE SP_REGGUIA( VMTIP_DOC CHAR(2) CHARACTER SET WIN1252, VMSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VMNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCOD_ALMA INTEGER, VFECHA TIMESTAMP, VMOTIVO INTEGER, VPPARTIDA CHAR(200) CHARACTER SET WIN1252, VPLLEGADA CHAR(230) CHARACTER SET WIN1252, VNRUC CHAR(11) CHARACTER SET WIN1252, VCLIENTE CHAR(65) CHARACTER SET WIN1252, VTRANRUC CHAR(11) CHARACTER SET WIN1252, VTRARAZON CHAR(65) CHARACTER SET WIN1252, VTRAPLA CHAR(30) CHARACTER SET WIN1252, VTRAMTC CHAR(20) CHARACTER SET WIN1252, VTRALIC CHAR(15) CHARACTER SET WIN1252, VGTIP_DOC CHAR(20) CHARACTER SET WIN1252, VGNUM_DOC CHAR(150) CHARACTER SET WIN1252, VPESO NUMERIC(18, 3), VORDCOM CHAR(20) CHARACTER SET WIN1252, VNUMDEU CHAR(5) CHARACTER SET WIN1252, VCOMENTARIO CHAR(250) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin if (exists(select mtip_doc,mserie_doc,mnum_doc from regguia where mtip_doc=:vmtip_doc and mserie_doc=:vmserie_doc and mnum_doc=:vmnum_doc)) then begin vretorno = -3; suspend; exit; end insert into regguia (mtip_doc,mserie_doc,mnum_doc,cod_alma,fecha,motivo,ppartida,pllegada,nruc,cliente,tranruc,trarazon,trapla,tramtc,tralic,gtip_doc,gnum_doc,peso,anulado,ordcom,numdeu,comentario) values(:vmtip_doc, :vmserie_doc, :vmnum_doc, :vcod_alma, :vfecha, :vmotivo, :vppartida, :vpllegada, :vnruc, :vcliente, :vtranruc, :vtrarazon, :vtrapla, :vtramtc, :vtralic, :vgtip_doc, :vgnum_doc, :vpeso,' ',:vordcom,:vnumdeu,:vcomentario); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; exit; End vretorno = 0; suspend; endvCREATE PROCEDURE SP_ALMGUIA( VMTIP_DOC CHAR(2) CHARACTER SET WIN1252, VMSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VMNUM_DOC CHAR(7) CHARACTER SET WIN1252, VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VCANT NUMERIC(10, 2), VUNIDAD CHAR(10) CHARACTER SET WIN1252, VDETALLE CHAR(65) CHARACTER SET WIN1252, VPU NUMERIC(8, 2), VTOTAL NUMERIC(8, 2), VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VMONEDA CHAR(1) CHARACTER SET WIN1252, VTC NUMERIC(4, 3), VCOD_USER INTEGER, VPOS INTEGER) RETURNS( VRETORNO SMALLINT) AS begin insert Into almguia (mtip_doc,mserie_doc,mnum_doc,cod_alma,cod_item,cant,unidad,detalle,pu,total,tip_doc,serie_doc,num_doc,moneda,tc,cod_user,pos,anulado) values(:vmtip_doc, :vmserie_doc, :vmnum_doc, :vcod_alma, :vcod_item, :vcant, :vunidad, :vdetalle,:vpu,:vtotal,:vtip_doc,:vserie_doc,:vnum_doc,:vmoneda,:vtc,:vcod_user,:vpos,' '); if (SQLCODE <> 0) then Begin vretorno = -1; suspend; exit; End vretorno = 0; suspend; endhCREATE VIEW GUIAS( COD_ALMA, COD_ITEM, FECHA, MTIP_DOC, MSERIE_DOC, MNUM_DOC, CANT, DETALLE, UNIDAD, TIP_DOC, SERIE_DOC, NUM_DOC, ANULADO, NRUC, CLIENTE, MOTIVO, REFER, COMENTARIO, ORDCOM, GTIP_DOC, GNUM_DOC) AS SELECT a.cod_alma,a.cod_item,b.fecha,a.mtip_doc,a.mserie_doc,a.mnum_doc,a.cant,a.detalle,a.unidad,a.tip_doc,a.serie_doc,a.num_doc,b.anulado,b.nruc,b.cliente,b.motivo,b.refer, b.comentario,b.ordcom,b.gtip_doc,b.gnum_doc FROM almguia a left join regguia b on a.mtip_doc=b.mtip_doc and a.mserie_doc=b.mserie_doc and a.mnum_doc=b.mnum_doc ;#CREATE PROCEDURE DEL_OTRA( VTTIP_DOC CHAR(2) CHARACTER SET WIN1252, VTSERIE_DOC CHAR(3) CHARACTER SET WIN1252, VTNUM_DOC CHAR(7) CHARACTER SET WIN1252) RETURNS( VRETORNO SMALLINT) AS begin If (exists(select tnum_doc from proddet where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc)) then Begin vretorno = -4; End else begin delete From prodcab where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc; update proddet set ttip_doc = '',tserie_doc='', tnum_doc='' where ttip_doc=:vttip_doc and tserie_doc=:vtserie_doc and tnum_doc=:vtnum_doc; If (row_count = 0) then vretorno = -2; else vretorno = 0; end suspend; end ÁCREATE PROCEDURE SP_MODPEN( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(3) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VINGRESO NUMERIC(18, 2), VEGRESO NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VFECHA_MOV TIMESTAMP, VCU NUMERIC(18, 2), VTIPOP CHAR(2) CHARACTER SET WIN1252, VDOCUMENTO CHAR(15) CHARACTER SET WIN1252, VDESCRIPCION CHAR(50) CHARACTER SET WIN1252, VNUMOPM INTEGER, VINGRESOA NUMERIC(18, 2), VEGRESOA NUMERIC(18, 2)) RETURNS( VRETORNO SMALLINT) AS declare vsaldo numeric(10,2); declare vnumop int; declare vtip_docs char(2); declare vserie_docs char(3); declare vnum_docs char(7); declare vcants numeric(10,2); declare vfechas timestamp; declare vfecham timestamp; declare vnrucs char(11); declare vprovclies char(65); declare vanulado char(1); declare vids int; Begin select saldo,numop from pensal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; vsaldo = vsaldo+((vingreso-vegreso)- (vingresoa-vegresoa)); vfecham = current_timestamp; if (vsaldo > 0) then begin vretorno = -3; suspend; exit; end else begin update pensal set saldo=:vsaldo where cod_item=:vcod_item and cod_alma=:vcod_alma; update almpen set fecha=:vfecha,fecha_mov=:vfecha_mov, tip_doc=:vtip_doc, serie_doc=:vserie_doc, num_doc=:vnum_doc, descripcion=:vdescripcion, ingreso=:vingreso, egreso=:vegreso, saldo=saldo+((:vingreso-:vegreso)- (:vingresoa-:vegresoa)), cu=:vcu, nruc=:vnruc, provclie=:vprovclie, tipop=:vtipop, documento=:vdocumento where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm; select tip_doc,serie_doc,num_doc from almpen where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm into :vtip_docs,:vserie_docs,:vnum_docs; update almpen set saldo=saldo+((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where cod_item=:vcod_item and cod_alma=:vcod_alma and numop>:vnumopm; update almsal set cant_pen = cant_pen +((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where cod_item=:vcod_item and cod_alma=:vcod_alma ; update almven set cant_pen = cant_pen - ((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where tip_doc=:vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc and cod_item = :vcod_item; vretorno = 0; suspend; end end CREATE PROCEDURE SP_MODPEN( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VTIP_DOC CHAR(2) CHARACTER SET WIN1252, VSERIE_DOC CHAR(4) CHARACTER SET WIN1252, VNUM_DOC CHAR(7) CHARACTER SET WIN1252, VINGRESO NUMERIC(18, 2), VEGRESO NUMERIC(18, 2), VNRUC CHAR(11) CHARACTER SET WIN1252, VPROVCLIE CHAR(65) CHARACTER SET WIN1252, VFECHA TIMESTAMP, VFECHA_MOV TIMESTAMP, VCU NUMERIC(18, 2), VTIPOP CHAR(2) CHARACTER SET WIN1252, VDOCUMENTO CHAR(15) CHARACTER SET WIN1252, VDESCRIPCION CHAR(50) CHARACTER SET WIN1252, VNUMOPM INTEGER, VINGRESOA NUMERIC(18, 2), VEGRESOA NUMERIC(18, 2)) RETURNS( VRETORNO SMALLINT) AS DECLARE VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VTIP_DOCS CHAR(2) CHARACTER SET WIN1252; DECLARE VARIABLE VSERIE_DOCS CHAR(4) CHARACTER SET WIN1252; DECLARE VARIABLE VNUM_DOCS CHAR(7) CHARACTER SET WIN1252; DECLARE VARIABLE VCANTS NUMERIC(18, 2); DECLARE VARIABLE VFECHAS TIMESTAMP; DECLARE VARIABLE VFECHAM TIMESTAMP; DECLARE VARIABLE VNRUCS CHAR(11); DECLARE VARIABLE VPROVCLIES CHAR(65) CHARACTER SET WIN1252; DECLARE VARIABLE VANULADO CHAR(1); DECLARE VARIABLE VIDS INTEGER; Begin select saldo,numop from pensal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; vsaldo = vsaldo+((vingreso-vegreso)- (vingresoa-vegresoa)); vfecham = current_timestamp; if (vsaldo > 0) then begin vretorno = -3; suspend; exit; end else begin update pensal set saldo=:vsaldo where cod_item=:vcod_item and cod_alma=:vcod_alma; update almpen set fecha=:vfecha,fecha_mov=:vfecha_mov, tip_doc=:vtip_doc, serie_doc=:vserie_doc, num_doc=:vnum_doc, descripcion=:vdescripcion, ingreso=:vingreso, egreso=:vegreso, saldo=saldo+((:vingreso-:vegreso)- (:vingresoa-:vegresoa)), cu=:vcu, nruc=:vnruc, provclie=:vprovclie, tipop=:vtipop, documento=:vdocumento where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm; select tip_doc,serie_doc,num_doc from almpen where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm into :vtip_docs,:vserie_docs,:vnum_docs; update almpen set saldo=saldo+((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where cod_item=:vcod_item and cod_alma=:vcod_alma and numop>:vnumopm; update almsal set cant_pen = cant_pen +((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where cod_item=:vcod_item and cod_alma=:vcod_alma ; update almven set cant_pen = cant_pen - ((:vingreso-:vegreso)- (:vingresoa-:vegresoa)) where tip_doc=:vtip_doc and serie_doc=:vserie_doc and num_doc=:vnum_doc and cod_item = :vcod_item; vretorno = 0; suspend; end endtCREATE PROCEDURE SP_DELPEN( VCOD_ALMA INTEGER, VCOD_ITEM INTEGER, VINGRE NUMERIC(18, 2), VEGRE NUMERIC(18, 2), VNUMOPM INTEGER) RETURNS( VRETORNO SMALLINT) AS DECLARE VARIABLE VSALDO NUMERIC(18, 2); DECLARE VARIABLE VNUMOP INTEGER; DECLARE VARIABLE VTIP_DOCS CHAR(2); DECLARE VARIABLE VSERIE_DOCS CHAR(4); DECLARE VARIABLE VNUM_DOCS CHAR(7); DECLARE VARIABLE VCANTS NUMERIC(18, 2); DECLARE VARIABLE VFECHAS TIMESTAMP; DECLARE VARIABLE VFECHA_MOV TIMESTAMP; DECLARE VARIABLE VNRUCS CHAR(11); DECLARE VARIABLE VPROVCLIES CHAR(65) CHARACTER SET WIN1252; DECLARE VARIABLE VANULADO CHAR(1); DECLARE VARIABLE VIDS INTEGER; Begin vfecha_mov = current_timestamp; select saldo,numop from pensal where cod_item=:vcod_item and cod_alma=:vcod_alma into :vsaldo,:vnumop; if (vingre > 0) then vsaldo = vsaldo - vingre; if (vegre > 0) then vsaldo = vsaldo + vegre; vnumop = vnumop - 1; if (vsaldo>0) then begin vretorno = -3; suspend; exit; end select tip_doc,serie_doc,num_doc from almpen where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm into :vtip_docs,:vserie_docs,:vnum_docs; delete from almpen where cod_item=:vcod_item and cod_alma=:vcod_alma and numop=:vnumopm; update almpen set numop = numop - 1, saldo =saldo + :vegre - :vingre where cod_item=:vcod_item and cod_alma=:vcod_alma and numop>:vnumopm; update pensal set saldo=:vsaldo, numop=:vnumop where cod_item=:vcod_item and cod_alma=:vcod_alma; update almsal set cant_pen = cant_pen + :vegre where cod_item=:vcod_item and cod_alma=:vcod_alma; update almven set cant_pen = cant_pen - :vegre where tip_doc=:vtip_docs and serie_doc=:vserie_docs and num_doc=:vnum_docs and cod_item = :vcod_item; vretorno = 0; suspend; end?CREATE INDEX IDX_PRODCAB ON PRODCAB(TIP_DOC,SERIE_DOC,NUM_DOC);CCREATE INDEX IDX_PRODDET1 ON PRODDET(TTIP_DOC,TSERIE_DOC,TNUM_DOC);ÊCREATE PROCEDURE GET_CLIE( VTIPO INTEGER, VNRUC CHAR(11) CHARACTER SET WIN1252) RETURNS( VDATA VARCHAR(240) CHARACTER SET WIN1252) AS begin if (vtipo=1) then select nruc from cliente where nruc=:vnruc into :vdata; if (vtipo=2) then select razon from cliente where nruc=:vnruc into :vdata; if (vtipo=3) then select direccion from cliente where nruc=:vnruc into :vdata; if (vtipo=4) then select percep from cliente where nruc=:vnruc into :vdata; if (vtipo=5) then select reten from cliente where nruc=:vnruc into :vdata; if (vtipo=6) then select exon from cliente where nruc=:vnruc into :vdata; if (vtipo=7) then select nombres from cliente where nruc=:vnruc into :vdata; if (vtipo=8) then select apaterno from cliente where nruc=:vnruc into :vdata; if (vtipo=9) then select amaterno from cliente where nruc=:vnruc into :vdata; if (vtipo=10) then select cast(cliente as char(240) character set WIN1252) from cliente where nruc=:vnruc into :vdata; if (vtipo=11) then select email from cliente where nruc=:vnruc into :vdata; SUSPEND; END;