-- Creacion de tablas y snapshots del pos
-- Los usuarios en la PC portatil y la BD deben ser los mismos!

define prefijo='DESA.'
define nombrets='DATOS'
define sufijo='@vismar'
define cia='''001'''
define sucursal='''001'''
define caja='''002'''
define agente='''08'''
set verify off


-- Snapshots Fijos (No se refrescan casi nunca)
create snapshot LICENCIA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with rowid
   as select * from &prefijo.LICENCIA&sufijo;
create snapshot GEN_COMPANIA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_COMPANIA&sufijo;
create snapshot GEN_PARAMETRO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_PARAMETRO&sufijo;
create snapshot PREPARAMETRO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREPARAMETRO&sufijo;
create snapshot PREPARMONEDA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREPARMONEDA&sufijo;
create snapshot APRPARAMETROS
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.APRPARAMETROS&sufijo;
create snapshot INV_PARAMETRO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.INV_PARAMETRO&sufijo;
create snapshot CXC_PARAMETRO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXC_PARAMETRO&sufijo;
create snapshot GEN_SUCURSAL
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_SUCURSAL&sufijo;
create snapshot GENHISTIMP 
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GENHISTIMP&sufijo;

create snapshot GENTIPOBITACORA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GENTIPOBITACORA&sufijo;

create snapshot GENPREFSOBJ
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GENPREFSOBJ&sufijo;

create snapshot GENPREFSCLA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GENPREFSCLA&sufijo;

create snapshot GENPREFSVAL
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GENPREFSVAL&sufijo;


create snapshot PRELISTAMON
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.prelistamon&sufijo;

create snapshot BANTIP
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.BANTIP&sufijo;

--El modulo de cliente frecuente esta deshabilitado en la PC remotas
--create snapshot FREPARAMETRO
--   tablespace  &nombrets
--   storage (initial 10K next 10K)
--   refresh force next sysdate+1
--   as select * from &prefijo.FREPARAMETRO&sufijo;

-- Snapshots de ventas
--  VENCONSECUTIVO VENBLOQUES
--  Al trabajar OnLine se sincronizan automaticamente
create snapshot VENPARAMETROS
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENPARAMETROS&sufijo;
create snapshot venconsignatario
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   as select * from &prefijo.venconsignatario&sufijo;
create snapshot VENTIPODOC
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENTIPODOC&sufijo;
create snapshot VENCONCEPTOCOBRO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENCONCEPTOCOBRO&sufijo;
create snapshot VENCONCEPTOVAL
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENCONCEPTOVAL&sufijo;
create snapshot VENCONCEPTOMISC
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENCONCEPTOMISC&sufijo;
create snapshot VENCLAVALE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENCLAVALE&sufijo;
create snapshot VENDESCUENTOS
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENDESCUENTOS&sufijo;

-- Tablas Inventario
create table INV_ENCABMOVIMIENTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.INV_ENCABMOVIMIENTO&sufijo
      where 1=0;
create table INV_DETMOVIMIENTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.INV_DETMOVIMIENTO&sufijo
      where 1=0;
create table INV_ARTIC_LOCBOD
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.INV_ARTIC_LOCBOD&sufijo
      where 1=0;

-- Snapshots inventario
create snapshot INV_TIPINV
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   as select * from &prefijo.INV_TIPINV&sufijo;
create snapshot INV_LINEA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.INV_LINEA&sufijo;
create snapshot inv_marca
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   as select * from &prefijo.inv_marca&sufijo;
create snapshot inv_clase
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   as select * from &prefijo.inv_clase&sufijo;
create snapshot INV_COMB_LCM
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   as select * from &prefijo.INV_COMB_LCM&sufijo;

create snapshot INV_LOTE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.INV_LOTE&sufijo;
create snapshot INV_ARTICULOBOD
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.INV_ARTICULOBOD&sufijo;
create snapshot INV_LOTE_ARTIC_BODEGA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.INV_LOTE_ARTIC_BODEGA&sufijo;
create snapshot INV_ACUM_PERIODO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.INV_ACUM_PERIODO&sufijo;
create snapshot INV_MEDIDA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.INV_MEDIDA&sufijo;
create snapshot INV_BODEGA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.INV_BODEGA&sufijo;
create snapshot INV_DEPTOS
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.INV_DEPTOS&sufijo;
create snapshot INV_ARTICULO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.INV_ARTICULO&sufijo;
create snapshot INV_ARTIC_CODBAR
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.INV_ARTIC_CODBAR&sufijo;
create snapshot INV_ARTIC_MEDIDA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.INV_ARTIC_MEDIDA&sufijo;
create snapshot INV_CONCEPMOVTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.INV_CONCEPMOVTO&sufijo;
create snapshot INV_CONSEC_MOVTOS
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.INV_CONSEC_MOVTOS&sufijo;
create snapshot INV_TABLADESC
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.INV_TABLADESC&sufijo;
create snapshot inv_detalles
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.inv_detalles&sufijo;
create snapshot inv_artic_prov
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.inv_artic_prov&sufijo;

-- Complete refreshes
create snapshot INV_ATRIBUTOS
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.INV_ATRIBUTOS&sufijo;
create snapshot INV_ENCABTABLACOMSN
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.INV_ENCABTABLACOMSN&sufijo;
create snapshot inv_tablabonific
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.inv_tablabonific&sufijo;

-- Snapshots generales
create snapshot GEN_IMPUESTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_IMPUESTO&sufijo;
create snapshot GEN_PERSONA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_PERSONA&sufijo;
create snapshot GEN_AGENTE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_AGENTE&sufijo;
create snapshot GEN_AGENTEAUT
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_AGENTEAUT&sufijo;
create snapshot GEN_FUNCIONES
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_FUNCIONES&sufijo;
create snapshot GEN_PERIODO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_PERIODO&sufijo;
create snapshot GEN_PERIODO_RANGO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_PERIODO_RANGO&sufijo;
create snapshot GEN_MONEDA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_MONEDA&sufijo;
create snapshot GEN_TIPO_CAMBIO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_TIPO_CAMBIO&sufijo;
create snapshot gen_cliente_clasif
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.gen_cliente_clasif&sufijo;
create snapshot gen_contclie
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.gen_contclie&sufijo;
create snapshot gen_cliente_accion
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.gen_cliente_accion&sufijo;
create snapshot gen_cliente_bitacora
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.gen_cliente_bitacora&sufijo;

--Complete refreshes
create snapshot gen_clase_cliente
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_clase_cliente&sufijo;
create snapshot gen_tipcontacto
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_tipcontacto&sufijo;
create snapshot gen_tipo_agente
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_tipo_agente&sufijo;
create snapshot gen_clasificacion
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_clasificacion&sufijo;
create snapshot gen_provincia
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_provincia&sufijo;
create snapshot gen_canton
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_canton&sufijo;
create snapshot gen_distrito
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_distrito&sufijo;
create snapshot gen_zona
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_zona&sufijo;
create snapshot gen_subzona
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh force
   with primary key
   as select * from &prefijo.gen_subzona&sufijo;

-- Tablas de cajas
create table MOVIMIENTOVENTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.MOVIMIENTOVENTA&sufijo
      where 1=0;
create table MOVIMIENTOCAJA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.MOVIMIENTOCAJA&sufijo
      where 1=0;
create table OPERACIONCAJA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.OPERACIONCAJA&sufijo
      where cia=&cia
        and caja=&caja
        and estado='A';

-- Snapshots de Caja
create snapshot CAJA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.CAJA&sufijo
      where cia=&cia
        and caja=&caja;
create snapshot CAJERO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.CAJERO&sufijo;
create snapshot TIPOCAJERO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.TIPOCAJERO&sufijo;
create snapshot CONCEPTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast 
   as select * from &prefijo.CONCEPTO&sufijo;
create snapshot CONCEPTOVENTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CONCEPTOVENTA&sufijo;


-- Tablas de Estadisticas
create table ESTDET
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.ESTDET&sufijo
      where 1=0;

-- Snapshots de Precios
create snapshot PREESPECIALES
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREESPECIALES&sufijo;
create snapshot PRELISTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PRELISTA&sufijo;
create snapshot PREDETLISTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREDETLISTA&sufijo;
create snapshot PREPROMOCION
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREPROMOCION&sufijo;
create snapshot PREDETPROMOCION
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREDETPROMOCION&sufijo;
create snapshot PREPRECIOPRO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREPRECIOPRO&sufijo;
create snapshot PREDESCCLASE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with rowid
   as select * from &prefijo.PREDESCCLASE&sufijo;
create snapshot PREDESCUSUARIO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.PREDESCUSUARIO&sufijo;

-- Snapshots de Cobros
create snapshot CXC_RANGOS_ANTSA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXC_RANGOS_ANTSA&sufijo;
create snapshot CXC_DOCUMENTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXC_DOCUMENTO&sufijo;
create snapshot CXC_POR_DESC
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXC_POR_DESC&sufijo;
create snapshot CXC_AUTORIZACION
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXC_AUTORIZACION&sufijo;
create snapshot CXC_LINEA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXC_LINEA&sufijo;
create snapshot CXCPERFIL
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXCPERFIL&sufijo;
create snapshot cxc_cliente_persona
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   as select * from &prefijo.cxc_cliente_persona&sufijo;

-- Snapshots de Cobros Actualizables
create snapshot CXC_CON_DOCUMENTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_CON_DOCUMENTO&sufijo;

-- Tablas y snapshots de pagos
create table CXP_DET_DESCU
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.CXP_DET_DESCU&sufijo;
create snapshot CXP_RANGOS_ANTSA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.CXP_RANGOS_ANTSA&sufijo;


-- Tablas de compras (proveedores)
create table PREC_PROVEEDOR
   tablespace  &nombrets
   storage (initial 10K next 10K)
   as select * from &prefijo.PREC_PROVEEDOR&sufijo;


-- SNAPSHOTS POR AGENTE
create snapshot GEN_CLIENTE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_CLIENTE&sufijo c
      Where c.cod_cia=&cia
        and c.sucursal=&sucursal;

create snapshot GEN_CLIE_AGENTE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_CLIE_AGENTE&sufijo
      where cod_cia=&cia
        and sucursal=&sucursal;

create snapshot GEN_SOBREG_CLIENTE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.GEN_SOBREG_CLIENTE&sufijo c
      Where c.cod_cia=&cia;

create snapshot CXC_ENC_PAGO_NOTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_ENC_PAGO_NOTA&sufijo c
      Where c.cod_cia=&cia
        and c.sucursal=&sucursal;

create snapshot CXC_DET_PAGO_NOTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_DET_PAGO_NOTA&sufijo m
      where m.cod_cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.cxc_enc_pago_nota&sufijo c
                    Where c.cod_cia=&cia
                      and c.sucursal=&sucursal
                      and c.tip_doc=m.tip_doc
                      and c.num_doc=m.num_doc);

create snapshot CXC_CHEQUES
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_CHEQUES&sufijo m
      where m.cod_cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.cxc_enc_pago_nota&sufijo c
                    Where c.cod_cia=&cia
                      and c.sucursal=&sucursal
                      and c.tip_doc=m.tip_doc
                      and c.num_doc=m.num_doc);

create snapshot CXC_ENC_MOVIMIENTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_ENC_MOVIMIENTO&sufijo c
      Where c.cod_cia=&cia
        and c.sucursal=&sucursal;

create snapshot CXC_OBLIGACION
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_OBLIGACION&sufijo m
      where m.cod_cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.cxc_enc_movimiento&sufijo c
                    Where c.cod_cia=&cia
                      and c.sucursal=&sucursal
                      and c.tip_doc=m.tip_doc
                      and c.num_doc=m.num_doc);

create snapshot CXC_CREDITO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.CXC_CREDITO&sufijo c
      Where c.cod_cia=&cia;

-- Transacciones del POS
create snapshot VENPEDIDO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENPEDIDO&sufijo c
      Where c.cia=&cia
        and c.sucursal=&sucursal;

create snapshot VENDETPEDIDO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENDETPEDIDO&sufijo m
      Where m.cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.VENPEDIDO&sufijo c
                    Where c.cia=&cia
                      and c.sucursal=&sucursal
                      and c.documento=m.documento);

create snapshot VENFACTURA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENFACTURA&sufijo c
      Where c.cia=&cia
        and c.sucursal=&sucursal;

create snapshot VENDETFACTURA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENDETFACTURA&sufijo m
      Where m.cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.VENFACTURA&sufijo c
                    Where c.cia=&cia
                      and c.sucursal=&sucursal
                      and c.documento=m.documento);

create snapshot VENDEVOLUCION
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENDEVOLUCION&sufijo c
      Where c.cia=&cia
        and c.sucursal=&sucursal;

create snapshot VENDETDEVOLUCION
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENDETDEVOLUCION&sufijo m
      Where m.cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.VENDEVOLUCION&sufijo c
                    Where c.cia=&cia
                      and c.sucursal=&sucursal
                      and c.documento=m.documento);

create snapshot VENNOTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENNOTA&sufijo c
      Where c.cia=&cia
        and c.sucursal=&sucursal;

create snapshot VENDETNOTA
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENDETNOTA&sufijo m
      Where m.cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.VENNOTA&sufijo c
                    Where c.cia=&cia
                      and c.sucursal=&sucursal
                      and c.documento=m.documento);

#Nuevos Snaps
create snapshot  VENPARAMPRESUP
    tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENPARAMPRESUP&sufijo m
      Where m.cia=&cia;

create snapshot  VENPRESUPUESTO
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.VENPRESUPUESTO&sufijo m
      Where m.cia=&cia;

create snapshot  gen_mas_comision
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.gen_mas_comision&sufijo m
      Where m.cod_cia=&cia;

create snapshot  cxc_det_comision
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   as select * from &prefijo.cxc_det_comision&sufijo m
      Where m.cod_cia=&cia;

#Pendiente eliminar el filtro por agente!!!
create snapshot  VENPRESCLIENTE
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.VENPRESCLIENTE&sufijo m
      Where m.cia=&cia
        and m.sucursal=&sucursal
        and exists (select 'x' from &prefijo.gen_clie_agente&sufijo a
                                  where a.cod_cia=&cia
                                  and a.sucursal=&sucursal
                                  and a.cod_agente=&agente
                                  and a.tipo_agente='VN'
                                  and a.cod_cliente=m.cliente));

create snapshot  cxc_clicomision
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.cxc_clicomision&sufijo m
      Where m.cod_cia=&cia
        and exists (select 'x' from &prefijo.gen_clie_agente&sufijo a
                                  where a.cod_cia=&cia
                                  and a.sucursal=&sucursal
                                  and a.cod_agente=&agente
                                  and a.tipo_agente='VN'
                                  and a.cod_cliente=m.cod_cliente));

create snapshot  vendetpresupuesto
   tablespace  &nombrets
   storage (initial 10K next 10K)
   refresh fast
   with primary key
   for update 
   as select * from &prefijo.vendetpresupuesto&sufijo m
      Where m.cia=&cia
        AND sucursal=&sucursal
        and exists (select 'x' from &prefijo.gen_clie_agente&sufijo a
                                  where a.cod_cia=&cia
                                  and a.sucursal=&sucursal
                                  and a.cod_agente=&agente
                                  and a.tipo_agente='VN'
                                  and a.cod_cliente=m.valor2));

#VIEWS
create or replace view inv_exist_bodega as
select COD_CIA, COD_BODEGA, COD_ARTICULO,
       sum(CAN_EXISTE) CAN_EXISTE, sum(CAN_APARTADO) CAN_APARTADO,
       sum(COS_TOTAL) COS_TOTAL,
       DECODE(sum(can_existe), 0, 0, sum(COS_TOTAL)/sum(CAN_EXISTE)) COS_PROMEDIO
from inv_lote_artic_bodega
group by COD_CIA, COD_BODEGA, COD_ARTICULO;

# Creo no es necesaria?
CREATE OR REPLACE VIEW PREDETLISTAV AS
SELECT D.COD_CIA, L.COD_LISTA_PRECIO, L.DES_LISTA, D.COD_MONEDA, D.COD_ARTICULO, D.COSTO,
       pre_par.PrecioNuevo(D.COD_CIA, D.PRECIO_UNITARIO, porc_utilidad, descuento, PORCENTAJE) PRECIO_UNITARIO,
       CASE WHEN L.PORCENTAJE>0 THEN PORCENTAJE ELSE 0 END PORC_UTILIDAD,
       CASE WHEN L.PORCENTAJE<0 THEN -PORCENTAJE ELSE 0 END DESCUENTO,
       D.DOCUMENTO
FROM PREDETLISTA D, PRELISTA L
WHERE D.COD_CIA=L.COD_CIA
  AND D.COD_LISTA_PRECIO=L.LISTA_MADRE
  AND L.PORCENTAJE IS NOT NULL
  AND L.LISTA_MADRE IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM PREDETLISTA O
                  WHERE O.COD_CIA=D.COD_CIA
                    AND O.COD_LISTA_PRECIO=L.COD_LISTA_PRECIO
                    AND O.COD_ARTICULO=D.COD_ARTICULO
                    AND O.COD_MONEDA=D.COD_MONEDA)
UNION
SELECT D.COD_CIA, L.COD_LISTA_PRECIO, L.DES_LISTA, D.COD_MONEDA, D.COD_ARTICULO, D.COSTO,
       CASE WHEN LISTA_MADRE IS NULL THEN
          D.PRECIO_UNITARIO
       ELSE
          Precios.PrecioDerDesc(D.COD_CIA, L.LISTA_MADRE, D.COD_ARTICULO, D.COD_MONEDA, PORC_UTILIDAD, DESCUENTO)
       END PRECIO_UNITARIO,
       D.PORC_UTILIDAD, D.DESCUENTO, D.DOCUMENTO
FROM PREDETLISTA D, PRELISTA L
WHERE D.COD_CIA=L.COD_CIA
  AND D.COD_LISTA_PRECIO=L.COD_LISTA_PRECIO;


