select count(*)
from venconexionservicio s, vendesconexion d, otorden o
where s.estado='D'
  and s.cia=d.cia
  and s.numservicio=d.numservicio
  and d.fechareal between to_date('28072007','ddmmyyyy') and sysdate+1
  and d.tipo='SC'
  and o.ot=d.ot
  and o.cia=d.cia
  and o.tipo='TEXDE'
  and exists(select 1 from facdebito deb, facdebitodetalle det
             where deb.cia=det.cia
               and deb.documento=det.documento
               and deb.fecha >= to_date('01082007','ddmmyyyy')
               and det.numservicio=d.numservicio
               and deb.estado='A')
  and not exists(select 1 from faccredito deb, faccreditodetalle det
             where deb.cia=det.cia
               and deb.documento=det.documento
               and deb.fecha >= to_date('01082007','ddmmyyyy')
               and det.numservicio=d.numservicio
               and deb.estado='A');




create table xx_desconcobrados as
select s.conexion, s.numservicio, d.ot, s.estado
from venconexionservicio s, vendesconexion d, otorden o
where s.estado='D'
  and s.cia=d.cia
  and s.numservicio=d.numservicio
  and d.fechareal between to_date('28072007','ddmmyyyy') and sysdate+1
  and d.tipo='SC'
  and o.ot=d.ot
  and o.cia=d.cia
  and o.tipo='TEXDE'
  and exists(select 1 from facdebito deb, facdebitodetalle det
             where deb.cia=det.cia
               and deb.documento=det.documento
               and deb.fecha >= to_date('01082007','ddmmyyyy')
               and det.numservicio=d.numservicio
               and deb.estado='A')
  and not exists(select 1 from faccredito deb, faccreditodetalle det
             where deb.cia=det.cia
               and deb.documento=det.documento
               and deb.fecha >= to_date('01082007','ddmmyyyy')
               and det.numservicio=d.numservicio
               and deb.estado='A');

create table xx_servs as
select 


DROP TABLE XX_CLIENTEDOBLE;
CREATE TABLE XX_CLIENTEDOBLE AS
SELECT CLIENTE, COUNT(*) CONTEO
FROM FACDEBITO
WhERE USUARIO='IRAMOS'
  AND DESCRIPCION like 'Debitos de agosto%'
  AND CLIENTE IN (SELECT CLIENTE FROM OTORDEN WHERE TIPO='TEXDE')
  AND CLIENTE NOT IN (SELECT CLIENTE FROM FACCREDITO 
                      WHERE FECHA>=TO_DATE('01082007','DDMMYYYY'))
GROUP BY CLIENTE
HAVING COUNT(*)  >1;

DROP TABLE XX_SERVICIODOBLE;
CREATE TABLE XX_SERVICIODOBLE AS
SELECT D.CLIENTE, D.DOCUMENTO, S.SERVICIO, S.NUMSERVICIO, S.TOTALLINEA, C.ESTADO
FROM FACDEBITO D, FACDEBITODETALLE S, VENCONEXIONSERVICIO C
WHERE D.CIA=S.CIA
  AND D.DOCUMENTO = S.DOCUMENTO
  AND D.DESCRIPCION like 'Debitos de agosto%'
  AND D.CLIENTE IN (SELECT CLIENTE FROM XX_CLIENTEDOBLE)
  AND C.CIA=D.CIA
  AND C.NUMSERVICIO=S.NUMSERVICIO
  AND D.MONTO=D.SALDO
ORDER BY D.CLIENTE, D.DOCUMENTO, S.SERVICIO;

UPDATE XX_SERVICIODOBLE D
  SET D.ESTADO='D'
WHERE EXISTS (SELECT 1 FROM XX_SERVICIODOBLE X
              WHERE X.ESTADO='D'
                AND X.DOCUMENTO=D.DOCUMENTO);

SELECT ESTADO, COUNT(*), SUM(TOTALLINEA)
FROM XX_SERVICIODOBLE
GROUP BY ESTADO;

DELETE FACDEBITODETALLE D
WHERE EXISTS (SELECT 1 FROM XX_SERVICIODOBLE X
              WHERE X.DOCUMENTO=D.DOCUMENTO
                AND X.NUMSERVICIO=D.NUMSERVICIO
                AND X.ESTADO='D');



CREATE TABLE XX_DEBITOSEQMRE AS
SELECT D.CLIENTE, D.DOCUMENTO, S.SERVICIO, S.TOTALLINEA
FROM FACDEBITO D, FACDEBITODETALLE S
WHERE D.CIA=S.CIA
  AND D.DOCUMENTO = S.DOCUMENTO
  AND D.MONTO=D.SALDO
  AND S.NUMSERVICIO IS NULL
  AND S.SERVICIO='EQMRE'
  AND D.CLIENTE NOT IN (SELECT CLIENTE FROM FACCREDITO 
                      WHERE FECHA>=TO_DATE('01082007','DDMMYYYY'));

DELETE FACDEBITODETALLE D
WHERE EXISTS (SELECT 1 FROM XX_DEBITOSEQMRE X
              WHERE X.DOCUMENTO=D.DOCUMENTO
                AND X.SERVICIO=D.SERVICIO);

