1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| -- contrôle des soldes :
SELECT 'DETAILS' AS REC_TYPE, A.ACC_CODE, 0.0 AS ABK_SUMMA, 0.0 AS CMP_CUR_ABK_SUMMA, 0.0 AS BNK_SUMMA, A.CUR_CODE, A.ABK_CUR_CODE FROM ACCOUNTS A
where A.CMP_CODE = 'Ma_société' and A.acc_code = 'Mon_compte'
UNION ALL SELECT REC_TYPE, U.ACC_CODE, U.ABK_SUMMA, U.ABK_SUMMA AS CMP_CUR_ABK_SUMMA, U.BNK_SUMMA, A.CUR_CODE, A.ABK_CUR_CODE
FROM (SELECT 'NREC_ACC_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA
FROM NREC_ACC N WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE UNION ALL
SELECT 'NREC_ACC_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA
FROM NREC_ACC N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'}
GROUP BY ACC_CODE UNION ALL
SELECT 'NREC_BANK_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA
FROM NREC_BANK N
WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'}
GROUP BY ACC_CODE
UNION ALL SELECT 'NREC_BANK_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA FROM NREC_BANK N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE
UNION ALL SELECT 'REC_ACC_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA FROM REC_ACC N WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE
UNION ALL SELECT 'REC_ACC_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA FROM REC_ACC N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'} AND REC_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE
UNION ALL SELECT 'REC_BANK_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA FROM REC_BANK N WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE
UNION ALL SELECT 'REC_BANK_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA FROM REC_BANK N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'} AND REC_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE
UNION ALL SELECT 'BAL_INIT_BANK' AS REC_TYPE, ACC_CODE, SUM(ABK_BAL_AMOUNT) AS ABK_SUMMA, SUM(BANK_BAL_AMOUNT)AS BNK_SUMMA FROM SPE_RAP_ACCOUNTS GROUP BY ACC_CODE
UNION ALL SELECT 'BAL_INIT_GL' AS REC_TYPE, ACC_CODE, SUM(ABK_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA FROM ACCOUNT_ID GROUP BY ACC_CODE ) U, ACCOUNTS A, COMPANIES C WHERE A.ACC_CODE = U.ACC_CODE AND A.CMP_CODE = C.CMP_CODE
AND A.CMP_CODE = 'Ma_société' and A.acc_code = 'Mon_compte' |
Partager