Actualizar Saldos Contables

De CidesaWiki

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.


SET SEARCH_PATH TO "SIMA002";

UPDATE CONTABB
SET SALANT=SALANT*-1 WHERE DEBCRE='C'

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

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

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

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

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

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

update contabb1
set salact=salact+totdeb-totcre;

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

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

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

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

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

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

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

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

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

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

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