create or alter procedure IP_ENTRADA_DEVOLUCION_GRABAR (
E integer,
S integer,
TIPO char(2),
NUMERO integer)
as
declare variable EDOCR integer;
declare variable SDOCR integer;
declare variable TPDOCR char(2);
declare variable NODOCR integer;
declare variable TOTALITEMS numeric(15,2);
declare variable LOCATION char(3);
declare variable QTY numeric(15,5);
declare variable ITEM varchar(15);
declare variable CONTADOR integer;
declare variable SALDO numeric(15,2);
declare variable DOCTIPDOC char(2);
declare variable DOCUMENTO char(2);
declare variable COSTO numeric(15,2);
declare variable TOTPARCIAL numeric(15,2);
declare variable ENTMC varchar(5);
declare variable ID_N bigint;
declare variable FECHA date;
declare variable CONTEO integer;
declare variable COST numeric(15,2);
declare variable QTYB numeric(15,2);
declare variable LOTE char(20);
declare variable PORCDCT numeric(15,2);
declare variable FECHA_VENCIMIENTO date;
declare variable COLOR char(25);
declare variable COD_TALLA char(5);
declare variable ICO numeric(15,2);
declare variable CONTEO1 integer;
declare variable FCH_VNCMNTO date;
declare variable ID_LOTE integer;
declare variable CONTEO_IA integer;
declare variable CONT integer;
BEGIN
SELECT
IP.ENTMC,
IP.ID_N,
IP.FECHA,T.TIPO FROM
IP, TIPDOC T
WHERE
IP.TIPO=:TIPO AND
IP.NUMBER=:NUMERO AND
IP.E=:E AND
IP.S=:S
AND
IP.E=T.E AND
IP.S=T.S AND
IP.TIPO=T.CLASE
INTO :ENTMC, :ID_N, :FECHA, :DOCUMENTO;
if ((:ENTMC = '') OR (:ENTMC IS Null)) then
ENTMC = 'False';
/*****************************************************************************/
/*** Actualizar transacciones del detalle ***/
/*****************************************************************************/
-- first we delete the register in ItemACT from what was deleted in ipdet
DELETE FROM ItemACT IA WHERE IA.E=:E AND IA.S=:S AND IA.Tipo=:Tipo and IA.Batch=:Numero
AND NOT EXISTS (SELECT * FROM ipdet ipd WHERE ipd.conteo_ia=ia.conteo);
FOR SELECT D.CONTEO,D.ITEM,D.LOCATION,D.COST,D.QTY,
D.QTYB, D.LOTE,D.PORCDCT, D.NODOCR,D.TPDOCR,D.EDOCR,
D.SDOCR,D.FECHA_VENCIMIENTO,D.color,D.cod_talla,D.ico,
T.TIPO, fecha_vencimiento, D.Conteo_IA
FROM IPDET D
LEFT JOIN TIPDOC T ON (D.EDOCR=T.E AND D.SDOCR=T.S AND D.TPDOCR=T.CLASE)
WHERE D.E=:E AND D.S=:S AND D.TIPO=:TIPO AND D.NUMBER=:NUMERO
INTO :CONTEO, :ITEM, :LOCATION, :COST, :QTY,
:QTYB, : LOTE,:PORCDCT, :NODOCR,:TPDOCR, :EDOCR,
:SDOCR, :FECHA_VENCIMIENTO,:color,:cod_talla,:ico,
:DOCTIPDOC, :FCH_VNCMNTO, :Conteo_IA
DO BEGIN
costo = :cost - ((cost * porcdct)/100);
IF (:documento = '
DP') THEN BEGIN
qty = qty *-1;
qtyb = qtyb *-1;
end
IF (:Conteo_IA IS NULL) THEN
Conteo_IA = 0;
if (:qty = 0 ) then
totparcial = costo;
else
totparcial = costo * qty;
ID_Lote = -1;
/*Actualizar lotes de inventario*/
IF ((:LOTE<>'') AND NOT (:LOTE is null)) THEN BEGIN
SELECT ID FROM LOTES_INVENTARIO
WHERE CODIGO=:LOTE AND ITEM=:ITEM
AND FECHAVENCIMIENTO=:FECHA_VENCIMIENTO
INTO :ID_Lote;
IF (:ID_Lote=-1) THEN BEGIN
INSERT INTO LOTES_INVENTARIO(CODIGO,FECHAVENCIMIENTO,ITEM)
VALUES(:LOTE,:FECHA_VENCIMIENTO,:ITEM) RETURNING ID INTO :ID_LOTE;
END
END
IF (:entmc = 'False') THEN BEGIN
-- now we insert the ones that are newly added or update the existing ones
SELECT Count(Conteo) FROM ItemACT WHERE Conteo=:Conteo_IA INTO :Cont;
IF (:Cont = 0) THEN BEGIN
INSERT INTO ItemACT (Conteo, item, location, fecha, qty, qtyb, tipo, valunit,id_n,batch,valcdct,valcfle,e,s,totparcial,color,cod_talla,lote,fch_vncmnto, ID_Lote)
VALUES (:Conteo_IA, :Item, :Location,:fecha,:qty,:qtyb,:TIPO,:costo,:id_n,:numero,0,0,:e,:s,:totparcial,:color,:cod_talla,:lote,:fch_vncmnto, :ID_LOTE) Returning Conteo INTO :Conteo_IA;
UPDATE IPDet SET Conteo_IA=:Conteo_IA WHERE E=:E AND S=:S AND Tipo=:Tipo AND Number=:Numero AND Conteo=:Conteo;
END ELSE BEGIN
UPDATE ItemACT SET Item=:Item, Location=:Location, Fecha=:Fecha, Qty=:Qty, QtyB=:QtyB, Tipo=:Tipo, ValUnit=:Costo,
ID_N=:ID_N, Batch=:Numero, ValcDct=0, Valcfle=0, E=:E, S=:S, TotParcial=:Totparcial, Color=:Color,
Cod_Talla=:Cod_Talla, Lote=:Lote, Fch_Vncmnto=:Fch_Vncmnto, ID_Lote=:ID_Lote
WHERE Conteo=:Conteo_IA;
SELECT Qty FROM ItemACT WHERE Conteo=:Conteo_IA INTO :Qty;
END
END ELSE BEGIN
update invmecD set cantleg=cantleg+:QTY
where e=:EDOCR and s=:SDOCR and tipo=:TPDOCR and numero=:NODOCR
and item=:ITEM and location=:LOCATION;
update itemdet set consignacion=consignacion-:QTY
where item=:ITEM and location=:LOCATION;
SELECT COUNT(*) FROM MCEA
WHERE EMPRESAEA=:E AND SUCURSALEA=:S AND TIPOEA=:TIPO AND NUMEROEA=:NUMERO
AND NUMEROMC=:NODOCR AND TIPOMC=:TPDOCR AND EMPRESAMC=:EDOCR AND SUCURSALMC=:SDOCR
INTO :CONTEO1;
IF ((:CONTEO1=0) OR (:CONTEO1 IS NULL)) THEN
BEGIN
INSERT INTO MCEA(NUMEROMC,TIPOMC,EMPRESAMC,SUCURSALMC,EMPRESAEA,SUCURSALEA,NUMEROEA,TIPOEA)
VALUES(:NODOCR,:TPDOCR,:EDOCR,:SDOCR,:E,:S,:NUMERO,:TIPO);
END
end
/*actualizar cantidades en ordenes de compra*/
IF (:DOCTIPDOC='OC') THEN
BEGIN
SELECT COUNT(*) FROM OC_IP
WHERE E=:E AND S=:S AND TYPEIP=:TIPO AND NUMBERIP=:NUMERO
INTO :CONTADOR;
IF ((:CONTADOR IS NULL) OR (:CONTADOR=0)) THEN
BEGIN
INSERT INTO OC_IP(TYPEOC,NUMBEROC,TYPEIP,NUMBERIP,E,S)
VALUES(:TPDOCR,:NODOCR,:TIPO,:NUMERO,:E,:S);
END
END
/*Actualizar informacion del producto*/
UPDATE ITEM SET COSTU=:COSTO,INDATE=:FECHA
WHERE ITEM=:ITEM;
UPDATE ITEM SET IMPSTOCONSUMO=:ICO
WHERE ITEM=:ITEM AND IMPSTOCONSUMO <:ICO AND USAICO='S';
/*Actualizar la lista de proveedores del item*/
SELECT COUNT(*) FROM ITEM_PROVEEDOR
WHERE ITEM=:ITEM AND ID_N=:ID_N
INTO :CONTADOR;
IF ((:CONTADOR IS NULL) OR (:CONTADOR=0)) THEN
BEGIN
INSERT INTO ITEM_PROVEEDOR(ITEM,ID_N,COSTO,FECHA)
VALUES(:ITEM,:ID_N,:COSTO,:FECHA);
END
EXECUTE PROCEDURE COSTOPROMEDIO(:ITEM);
END
/*****************************************************************************/
/*** Actualizar documentos de respaldo de la entrada de almacen ***/
/*****************************************************************************/
/*Actualizar estado de la orden de compra*/
FOR SELECT O.TYPEOC,O.NUMBEROC,SUM(D.QTY-D.RECIBIDO)
FROM OC_IP O, IPOCD D
WHERE O.E=:E AND O.S=:S AND O.TYPEIP=:TIPO AND O.NUMBERIP=:NUMERO
AND O.E=D.E AND O.S=D.S AND O.TYPEOC=D.TIPO AND O.NUMBEROC=D.NUMBER
GROUP BY O.TYPEOC,O.NUMBEROC
INTO :TPDOCR,:NODOCR,:SALDO
DO
BEGIN
IF (:SALDO>0) THEN
BEGIN
UPDATE IPOCE SET ESTADO='PARCIAL'
WHERE E=:E AND S=:S AND TIPO=:TPDOCR AND NUMBER=:NODOCR;
END
ELSE IF (:SALDO=0) THEN
BEGIN
UPDATE IPOCE SET ESTADO='CERRADO'
WHERE E=:E AND S=:S AND TIPO=:TPDOCR AND NUMBER=:NODOCR;
END
END
/*Actualizar el costo total de la entrada o devolucion*/
TOTALITEMS=0;
SELECT SUM(EXTEND) FROM IPDET
WHERE E=:E AND S=:S AND TIPO=:TIPO AND NUMBER=:NUMERO
INTO :TOTALITEMS;
IF (:TOTALITEMS IS NULL) THEN
TOTALITEMS=0;
IF (:DOCUMENTO='EA') THEN
BEGIN
UPDATE
IP SET TOTALITEMS=:TOTALITEMS
WHERE E=:E AND S=:S AND TIPO=:TIPO AND NUMBER=:NUMERO;
END
ELSE
BEGIN
UPDATE
IP SET TOTALITEMS=:TOTALITEMS
WHERE E=:E AND S=:S AND TIPO=:TIPO AND NUMBER=:NUMERO;
END
END^
SET TERM ; ^