Actualizar Saldos Contables - Historial de revisiones

Vescalona: New page: Este script trabaja con las vistas CONBB1, CONBB2, CONBB3, ... , CONBB12 y SALDOS_DEBCRE.
Se debe cambiar los SELECT segun la estructura de la cuenta contable del cliente.

2009-08-04T20:43:38Z

New page: Este script trabaja con las vistas CONBB1, CONBB2, CONBB3, ... , CONBB12 y SALDOS_DEBCRE.<br> Se debe cambiar los SELECT segun la estructura de la cuenta contable del cliente. <br> <br> <b...

Página nueva
Este script trabaja con las vistas CONBB1, CONBB2, CONBB3, ... , CONBB12 y SALDOS_DEBCRE.<br>
Se debe cambiar los SELECT segun la estructura de la cuenta contable del cliente. <br>
<br>
<br>
SET SEARCH_PATH TO "SIMA002";<br>

UPDATE CONTABB <br>
SET SALANT=SALANT-1 WHERE DEBCRE='C'<br>

CREATE OR REPLACE VIEW SALDOS AS (<br>
SELECT SUBSTR(CODCTA,1,1) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,1)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,3) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,3)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,5) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,5)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,8) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,8)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,11) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,11)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,14) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,14)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,17) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,17)<br>
UNION<br>
SELECT SUBSTR(CODCTA,1,20) AS CUENTA,SUM(SALANT) AS SALDO<br>
FROM CONTABB<br>
WHERE CARGAB='C'<br>
GROUP BY SUBSTR(CODCTA,1,20));<br>

CREATE OR REPLACE VIEW CONBB1 AS SELECT FROM CONTABB1 WHERE PEREJE='01';<br>
CREATE OR REPLACE VIEW CONBB2 AS SELECT * FROM CONTABB1 WHERE PEREJE='02';<br>
CREATE OR REPLACE VIEW CONBB3 AS SELECT * FROM CONTABB1 WHERE PEREJE='03';<br>
CREATE OR REPLACE VIEW CONBB4 AS SELECT * FROM CONTABB1 WHERE PEREJE='04';<br>
CREATE OR REPLACE VIEW CONBB5 AS SELECT * FROM CONTABB1 WHERE PEREJE='05';<br>
CREATE OR REPLACE VIEW CONBB6 AS SELECT * FROM CONTABB1 WHERE PEREJE='06';<br>
CREATE OR REPLACE VIEW CONBB7 AS SELECT * FROM CONTABB1 WHERE PEREJE='07';<br>
CREATE OR REPLACE VIEW CONBB8 AS SELECT * FROM CONTABB1 WHERE PEREJE='08';<br>
CREATE OR REPLACE VIEW CONBB9 AS SELECT * FROM CONTABB1 WHERE PEREJE='09';<br>
CREATE OR REPLACE VIEW CONBB10 AS SELECT * FROM CONTABB1 WHERE PEREJE='10';<br>
CREATE OR REPLACE VIEW CONBB11 AS SELECT * FROM CONTABB1 WHERE PEREJE='11';<br>
CREATE OR REPLACE VIEW CONBB12 AS SELECT * FROM CONTABB1 WHERE PEREJE='12';<br>

CREATE OR REPLACE VIEW SALDOS_DEBCRE AS (<br>
SELECT SUBSTR(A.CODCTA,1,1) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,1),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,3) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,3),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,5) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,5),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,8) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,8),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,11) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,11),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,14) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,14),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,17) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,17),TO_CHAR(A.FECCOM,'MM')<br>
UNION<br>
SELECT SUBSTR(A.CODCTA,1,20) AS CUENTA,TO_CHAR(A.FECCOM,'MM') AS PERIODO,SUM(CASE A.DEBCRE WHEN 'D' THEN A.MONASI ELSE 0 END) AS<br> DEBITO,SUM(CASE A.DEBCRE WHEN 'C' THEN A.MONASI ELSE 0 END) AS CREDITO<br>
FROM CONTABC1 A,CONTABC B<br>
WHERE B.STACOM='A'AND<br>
A.NUMCOM=B.NUMCOM AND<br>
A.FECCOM=B.FECCOM<br>
GROUP BY SUBSTR(CODCTA,1,20),TO_CHAR(A.FECCOM,'MM'));<br>

UPDATE CONTABB<br>
SET SALANT=SALDOS.SALDO<br>
FROM SALDOS<br>
WHERE CUENTA=RTRIM(CONTABB.CODCTA);<br>

UPDATE CONTABB1<br>
SET SALACT=CONTABB.SALANT<br>
FROM CONTABB<br>
WHERE CONTABB.CODCTA=CONTABB1.CODCTA;<br>

UPDATE CONTABB1<br>
SET TOTDEB=SALDOS_DEBCRE.DEBITO,<br>
TOTCRE=SALDOS_DEBCRE.CREDITO<br>
FROM SALDOS_DEBCRE<br>
WHERE SALDOS_DEBCRE.Cuenta=RTRIM(CONTABB1.CODCTA) AND<br>
SALDOS_DEBCRE.PERIODO=CONTABB1.PEREJE;<br>

update contabb1<br>
set salact=salact+totdeb-totcre;<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB1.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB1<br>
WHERE CONBB1.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='02';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB2.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB2<br>
WHERE CONBB2.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='03';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB3.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB3<br>
WHERE CONBB3.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='04';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB4.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB4<br>
WHERE CONBB4.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='05';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB5.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB5<br>
WHERE CONBB5.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='06';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB6.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB6<br>
WHERE CONBB6.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='07';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB7.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB7<br>
WHERE CONBB7.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='08';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB8.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB8<br>
WHERE CONBB8.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='09';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB9.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB9<br>
WHERE CONBB9.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='10';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB10.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB10<br>
WHERE CONBB10.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='11';<br>

UPDATE CONTABB1<br>
SET SALACT=CONBB11.SALACT+CONTABB1.TOTDEB-CONTABB1.TOTCRE<br>
FROM CONBB11<br>
WHERE CONBB11.CODCTA=CONTABB1.CODCTA AND<br>
CONTABB1.PEREJE='12';<br>