-- Script necesarios en la Revaluacion
drop table xx;
create table xx as
select distinct cod_articulo,medida 
from inv_detmovimiento;

-- Existen algunas medidas diferentes y deben actualizarse en Carazo --
declare cursor xy is
select a.cod_articulo,a.medida medidaart,b.medida
from inv_articulo a,xx b
where a.cod_articulo=b.cod_articulo
and  a.medida<>b.medida;
begin
  for a in xy loop
  update inv_detmovimiento
  set medida=a.medidaart
  where cod_cia='001'
  and   cod_articulo=a.cod_articulo
  and   medida=a.medida;
  end loop;
end;
 
 create table inv_detmovimientodet
 as select * from inv_detmovimiento where 1=2;

 
 create table resinvarticbodega as
 select * from inv_artic_bodega;

 drop table resvenfactura;
 create table resvenfactura as 
 select * from venfactura;

 drop table resvendetfactura;
 create table resvendetfactura as
 select * from vendetfactura;


 create table resinvacumperiodo as
 select * from inv_acum_periodo;

update inv_parametro
set ind_ctllote='S',tip_valuac='UEPS';
commit;

update inv_encabmovimiento
set fec_movto=to_date('30092003','ddmmyyyy'),ano_fiscal=2003,per_proceso=12
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='03'
and num_docto='0';

-- Anula movimiento de inventario inicial de Noviembre 2003
update inv_encabmovimiento
set estado='A'
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='03'
and num_docto='22';

-- Activa movimiento traslado de inventario de Octubre 2003
update inv_encabmovimiento
set estado='D',ano_fiscal=2004,per_proceso=1
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='05'
and num_docto='0';

update inv_detmovimiento
set sub_total=1
where cod_cia='001'
and sucursal='001'
and tip_movto='S'
and cod_movto='88';
commit;


-- Recosteo de las ventas con el inventario inicial de Octubre 2003
declare cursor xx is 
select * from inv_detmovimiento
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='03'
and num_docto='0';
begin
  for a in xx loop
     update inv_detmovimiento
     set sub_total=(a.sub_total/a.can_articulo)*can_articulo
     where cod_cia='001'
     and sucursal='001'
     and tip_movto='S'
     and cod_movto='88'
     and cod_articulo=a.cod_articulo;
  end loop;
end;
/

declare cursor xx is 
select * from inv_detmovimiento
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='05'
and num_docto='0'
and cod_articulo in 
(select cod_articulo from inv_detmovimiento
where cod_cia='001'
     and sucursal='001'
     and tip_movto='S'
     and cod_movto='88'
     and sub_total=1);
begin
  for a in xx loop
     update inv_detmovimiento
     set sub_total=(a.sub_total/a.can_articulo)*can_articulo
     where cod_cia='001'
     and sucursal='001'
     and tip_movto='S'
     and cod_movto='88'
     and cod_articulo=a.cod_articulo;
  end loop;
end;
/
commit;

--Las ventas de la sucursal de las facturas debe costearse para dejarlas igual
--que en inventario
declare 
 cursor cmovinv is 
  select to_number(b.doc_referen) doc_referen,a.cod_articulo,(a.sub_total/a.can_articulo) costo,a.sucursal
  from inv_detmovimiento a,inv_encabmovimiento b
  where a.cod_cia     = '001'
  and   b.cod_cia     = '001'
  and   b.sucursal    = b.sucursal 
  and   a.tip_movto   = 'S'
  and   b.tip_movto   = 'S'  
  and   a.cod_movto   = b.cod_movto
  and   a.num_docto   = b.num_docto
  and   b.ind_externo = 'FACF'
  and   b.estado     = 'D'
  and   a.cod_movto='88'
  order by to_number(b.doc_referen),a.cod_articulo;
begin
   for a in cmovinv loop
    update vendetfactura
    set costo=a.costo
    where  cia       = '001'
    and    sucursal  = a.sucursal
    and    documento = a.doc_referen
    and    articulo  = a.cod_articulo;  
   end loop;
end;
/
commit;

-- Generacion del movimiento de Inventario Acumulado
drop table xx;
create table xx as 
select  a.cod_cia,a.sucursal,a.cod_bodega,a.cod_articulo,b.ano_fiscal,b.per_proceso,
c.clase_movto,sum(a.can_articulo) cantidad,sum(a.SUB_TOTAL) monto
from inv_detmovimiento a,inv_encabmovimiento b,inv_concepmovto c
where a.cod_cia   = b.cod_cia
and   a.sucursal  = b.sucursal
and   a.tip_movto = b.tip_movto
and   a.cod_movto = b.cod_movto
and   a.num_docto = b.num_docto
and   a.cod_cia   = c.cod_cia
and   a.tip_movto = c.tip_Movto
and   a.cod_movto = c.cod_movto
and   b.estado    = 'D'
and   a.cod_bodega='002'
and   trunc(b.fec_movto) between to_date('01/10/2003','dd/mm/yyyy')
and   to_date('31/10/2003','dd/mm/yyyy')
group by a.cod_cia,a.sucursal,a.cod_bodega,a.cod_articulo,
b.ano_fiscal,b.per_proceso,c.clase_movto;

insert into inv_acum_periodo
select * from xx;
commit;

---   PRocesos de ayuda ---
---                     ---


 select sum(a.sub_total) sub_total,sum(a.can_articulo) can_articulo,count(*) cantidad
  from inv_detmovimiento a,inv_encabmovimiento b,inv_concepmovto c
  where a.cod_cia    = '001'
  and   a.sucursal   = b.sucursal
  and   b.cod_cia    = '001'
  and   c.cod_cia    = '001'
  and   a.tip_movto  = b.tip_movto  
  and   a.cod_movto  = b.cod_movto
  and   a.num_docto  = b.num_docto
  and   a.tip_movto  = c.tip_movto  
  and   a.cod_movto  = c.cod_movto 
  and   a.cod_bodega='001'
  and   b.estado     = 'D'
  and   c.clase_movto = 'ST'
  and   trunc(b.fec_movto)<=to_date('30/06/2004','dd/mm/yyyy');


  select sum(a.sub_total) sub_total,sum(a.can_articulo) can_articulo,count(*) cantidad
  from inv_detmovimiento a,inv_encabmovimiento b,inv_concepmovto c
  where a.cod_cia    = '001'
  and   a.sucursal   = b.sucursal
  and   b.cod_cia    = '001'
  and   c.cod_cia    = '001'
  and   a.tip_movto  = b.tip_movto  
  and   a.cod_movto  = b.cod_movto
  and   a.num_docto  = b.num_docto
  and   a.tip_movto  = c.tip_movto  
  and   a.cod_movto  = c.cod_movto 
  and   a.cod_bodega='002'
  and   b.estado     = 'D'
  and   c.clase_movto = 'ET'
  and   trunc(b.fec_movto)<=to_date('30/06/2004','dd/mm/yyyy');


  select sum(a.sub_total) sub_total,sum(can_articulo) can_articulo,count(*)
  from inv_detmovimiento a,inv_encabmovimiento b,inv_concepmovto c
  where a.cod_cia    = '001'
  and   a.sucursal   = '001'
  and   b.cod_cia    = '001'
  and   b.sucursal   = '001'
  and   c.cod_cia    = '001'
  and   a.tip_movto  = b.tip_movto  
  and   a.cod_movto  = b.cod_movto
  and   a.num_docto  = b.num_docto
  and   a.tip_movto  = c.tip_movto  
  and   a.cod_movto  = c.cod_movto 
  and   a.tip_movto  = 'E'
  and   c.clase_movto<>'ET'
  and   b.estado     = 'D';


  select b.tip_movto,b.fec_movto,b.cod_movto,a.cod_bodega,a.cod_articulo,
  a.can_articulo,a.sub_total/a.can_articulo cosunidad
  from inv_detmovimiento a,inv_encabmovimiento b,inv_concepmovto c
  where a.cod_cia='001'
  and   b.cod_cia='001'
  and   c.cod_cia='001'
  and   a.sucursal=b.sucursal
  and   a.tip_movto=b.tip_movto
  and   a.num_docto=b.num_docto 
  and   a.tip_movto=c.tip_movto
  and   a.cod_movto=c.cod_movto
  and   b.tip_movto=c.tip_movto
  and   b.cod_movto=c.cod_movto
  and   b.estado='D'
  and   a.tip_movto='E'
  and   c.clase_movto <> 'ET'
  and   a.cod_articulo='105070'
  order by  a.cod_articulo,b.fec_movto;


 select sum(a.sub_total) sub_total,sum(can_articulo) can_articulo,count(*)
 from inv_detmovimiento a,inv_encabmovimiento b,inv_concepmovto c
 where a.cod_cia    = '001'
 and   a.sucursal   = '001'
 and   b.cod_cia    = '001'
 and   b.sucursal   = '001'
 and   c.cod_cia    = '001'
 and   a.tip_movto  = b.tip_movto  
 and   a.cod_movto  = b.cod_movto
 and   a.num_docto  = b.num_docto
 and   a.tip_movto  = c.tip_movto  
 and   a.cod_movto  = c.cod_movto 
 and   a.tip_movto  = 'E'
 and   c.clase_movto<>'ET'
 and   b.estado     = 'D';






select cod_movto,num_docto from inv_detmovimiento 
where cod_lote='001' 
and (cod_cia,sucursal,tip_movto,cod_movto,num_docto) in 
(select cod_cia,sucursal,tip_movto,cod_movto,num_docto from 
inv_encabmovimiento where estado='D' and cod_movto<>'05' and tip_movto='E')


select sum(a.costo*a.cantidad) from resvendetfactura a
where exists
(select 1 from resvenfactura b
 where b.cia=a.cia
 and   b.sucursal=a.sucursal
 and   b.documento=a.documento
 and   b.estado='A');


select sum(a.costo*a.cantidad) from vendetfactura a
where exists
(select 1 from venfactura b
 where b.cia=a.cia
 and   b.sucursal=a.sucursal
 and   b.documento=a.documento
 and   b.estado='A'
 and   trunc(b.fecha) between to_date('01022004','ddmmyyyy') and 
 to_date('29022004','ddmmyyyy'));

-- Articulos y cantidades donde a la fecha no alcanzo la mercaderia del Lote
select cod_articulo from inv_detmovimiento
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='05'
and num_docto='0'
minus
select cod_articulo from inv_detmovimiento
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='03'
and num_docto='0';



create table xy as
select * 
from inv_encabmovimiento
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='03'
and num_docto='22';


update venfactura
set estado='A'
where cia='001'
and sucursal='001'
and documento between 73462 and 73562
and estado='P'
and caja='002';


update vendetfactura
set bodega='002'
where cia='001'
and sucursal='001'
and documento between 73462 and 73562;


-- El movimiento 22 debo sustituirlo por un documento 0
select cod_articulo from inv_detmovimiento
where cod_cia='001'
and sucursal='002'
and tip_movto='E'
and cod_movto='03'
and num_docto='22';




  --- Comparacion de Datos antes de Revaluar ---

drop table veninv;  
  create table veninv as
  select b.cod_cia cia,b.sucursal,to_number(b.doc_referen) documento,
         a.cod_articulo,(a.sub_total/a.can_articulo) costo,a.can_articulo cantidad
  from inv_detmovimiento a,inv_encabmovimiento b
  where a.cod_cia     = '001'
  and   b.cod_cia     = '001'
  and   b.sucursal    = b.sucursal 
  and   a.tip_movto   = 'S'
  and   b.tip_movto   = 'S'  
  and   a.cod_movto   = b.cod_movto
  and   a.num_docto   = b.num_docto
  and   b.ind_externo = 'FACF'
  and   b.estado     = 'D';



drop table venfac;
create table venfac as
select a.cia,a.sucursal,a.documento,a.articulo cod_articulo,avg(a.costo) costo,sum(a.cantidad) cantidad
from vendetfactura a,venfactura b
where a.cia=b.cia
and   a.sucursal=b.sucursal
and   a.documento=b.documento
and   b.estado='A'
group by a.cia,a.sucursal,a.documento,a.articulo;





select a.documento,a.cod_articulo,a.costo,b.costo costoinv
from venfac a,veninv b
where a.cia=b.cia
and a.sucursal=b.sucursal
and a.documento=b.documento
and a.cod_articulo=b.cod_articulo
and abs(a.costo-b.costo)>1;

-- Compara saldo inicial con saldo final de inventario
select a.cod_articulo,a.sub_total/a.can_articulo costoini,b.sub_total/b.can_articulo costofin 
from inv_detmovimiento a,inv_detmovimiento b
where a.cod_cia='001'
and a.sucursal='002'
and a.tip_movto='E'
and a.cod_movto='03'
and a.num_docto='0'
and b.cod_cia='001'
and b.sucursal='002'
and b.tip_movto='E'
and b.cod_movto='03'
and b.num_docto='22'
and a.cod_articulo=b.cod_articulo
and a.sub_total/a.can_articulo<>b.sub_total/b.can_articulo
order by a.cod_articulo;



select a.cod_articulo,a.sub_total/a.can_articulo costoini,b.sub_total/b.can_articulo costofin 
from inv_detmovimiento a,inv_detmovimiento b
where a.cod_cia='001'
and a.sucursal='002'
and a.tip_movto='E'
and a.cod_movto='03'
and a.num_docto='0'
and b.cod_cia='001'
and b.sucursal='001'
and b.tip_movto='E'
and b.cod_movto='05'
and b.num_docto='0'
and a.cod_articulo=b.cod_articulo
and a.sub_total/a.can_articulo<>b.sub_total/b.can_articulo
order by a.cod_articulo;

create table resvenfactura as select * from venfactura;
create table resvendetfactura as select * from vendetfactura;
create table resinvarticbodega as
select * from inv_artic_bodega;


drop table xx;
create table xx as
select sum(a.costo*a.cantidad) total
from vendetfactura a,venfactura b
where a.cia=b.cia
and   a.sucursal=b.sucursal
and   a.documento=b.documento
and   b.estado='A';

drop table xy;
create table xy as
select sum(a.costo*a.cantidad) total
from resvendetfactura a,resvenfactura b
where a.cia=b.cia
and   a.sucursal=b.sucursal
and   a.documento=b.documento
and   b.estado='A';


  drop table xx;
  create table xx as
  select to_char(b.fec_movto,'YYYYMM') fecha,sum(a.sub_total) sub_total,sum(a.can_articulo) can_articulo,count(*) cantidad
  from inv_detmovimientodet a,inv_encabmovimiento b,inv_concepmovto c
  where a.cod_cia    = '001'
  and   a.sucursal   = b.sucursal
  and   b.cod_cia    = '001'
  and   c.cod_cia    = '001'
  and   a.tip_movto  = b.tip_movto  
  and   a.cod_movto  = b.cod_movto
  and   a.num_docto  = b.num_docto
  and   a.tip_movto  = c.tip_movto  
  and   a.cod_movto  = c.cod_movto 
  and   b.estado     = 'D'
  and   c.clase_movto = 'SV';


  drop table xy;
  create table xy as
  select to_char(b.fec_movto,'YYYYMM') fecha,sum(a.sub_total) sub_total,sum(a.can_articulo) can_articulo,count(*) cantidad
  from carazo.inv_detmovimiento a,carazo.inv_encabmovimiento b,carazo.inv_concepmovto c
  where a.cod_cia    = '001'
  and   a.sucursal   = b.sucursal
  and   b.cod_cia    = '001'
  and   c.cod_cia    = '001'
  and   a.tip_movto  = b.tip_movto  
  and   a.cod_movto  = b.cod_movto
  and   a.num_docto  = b.num_docto
  and   a.tip_movto  = c.tip_movto  
  and   a.cod_movto  = c.cod_movto 
  and   b.estado     = 'D'
  and   c.clase_movto = 'SV';




drop table venfacr;
create table venfacr as
select to_char(b.fecha,'YYYYMM') fecha,sum(a.costo*a.cantidad) monto,sum(a.cantidad) cantidad
from vendetfactura a,venfactura b
where a.cia=b.cia
and   a.sucursal=b.sucursal
and   a.documento=b.documento
and   b.estado='A'
group by to_char(b.fecha,'YYYYMM');

drop table venfac;
create table venfac as
select to_char(b.fecha,'YYYYMM') fecha,sum(a.costo*a.cantidad) monto,sum(a.cantidad) cantidad
from carazo.vendetfactura a,carazo.venfactura b
where a.cia=b.cia
and   a.sucursal=b.sucursal
and   a.documento=b.documento
and   b.estado='A'
group by to_char(b.fecha,'YYYYMM');


select a.fecha,sum(a.monto) monreval,sum(b.monto) monto,sum(a.monto-b.monto) dife
from venfacr a,venfac b
where a.fecha=b.fecha
group by a.fecha;


select a.fecha,sum(a.monto) monreval,sum(b.monto) monto,sum(a.monto-b.monto) dife
from venfacr a,venfac b
where a.fecha=b.fecha
group by a.fecha



-- Ver el valor del inventario por lotes
select sum(a.can_existe*b.cos_unidad) total 
from inv_lote_artic_bodega a,inv_lote b
where a.cod_cia=b.cod_cia
and   a.cod_lote=b.cod_lote
and   a.cod_articulo=b.cod_articulo;


select sum(can_existe*cos_promedio) monto
from resinvarticbodega;

select cantidad,costo from resvendetfactura 
where articulo='155185'
order by documento;

select b.fecha,a.cantidad,a.costo 
from resvendetfactura a,resvenfactura b
where a.cia=b.cia
and   a.sucursal=b.sucursal
and   a.documento=b.documento
and   a.articulo='155185'
order by b.fecha;




order by cod_articulo