1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| CREATE VIEW V_ETAT_BNK (NATURE,STATUT, CMP_CODE, ACC_CODE, FLOW_CODE, FLOW_CODE_DESCRIPTION,LIBELLE_BANCAIRE,
BOOK_DATE, VALUE_DATE, ABK_CUR_CODE, TRN_CUR, ABK_CUR_AMOUNT, TRN_AMOUNT, SOLDE_CTRV, SOLDE_TRN, SENSE_FLAG,
GL_ACC_CODE, ACCOUNT_ID, RECONCILIATION_ID, REC_MVT_ID, REC_DATE ) AS
SELECT 'B'as NATURE ,'R' as STATUT,c.CMP_CODE, a.ACC_CODE, a.BANK_FLOW_CODE, b.description "FLOW_DESCRIPTION",
a.DESCRIPTION "GL_DESCRIPTION", BOOK_DATE, VALUE_DATE, a.BANK_CUR_CODE, TRN_CUR, ABK_CUR_AMOUNT, TRN_AMOUNT, '' AS SOLDE_CTRV,
'' AS SOLDE_TRN, SENSE_FLAG, c.GL_ACC_CODE, c.ACCOUNT_ID, RECONCILIATION_ID, REC_BANK_MVT_ID, REC_DATE
FROM REC_BANK a WITH (INDEX (IF1_REC_BANK)) INNER JOIN IBC_CODES b ON a.BANK_FLOW_CODE = b.IBC_CODE
INNER JOIN ACCOUNTS c ON a.ACC_CODE = c.ACC_CODE
INNER JOIN COMPANIES d ON c.CMP_CODE = d.CMP_CODE
UNION ALL
SELECT 'B'as NATURE ,'R' as STATUT,c.CMP_CODE, a.ACC_CODE, a.BANK_FLOW_CODE, b.description "FLOW_DESCRIPTION",
a.DESCRIPTION "GL_DESCRIPTION", BOOK_DATE, VALUE_DATE, a.BANK_CUR_CODE, TRN_CUR, ABK_CUR_AMOUNT, TRN_AMOUNT, '' AS SOLDE_CTRV,
'' AS SOLDE_TRN, SENSE_FLAG, c.GL_ACC_CODE, c.ACCOUNT_ID, PRE_REC_ID, NREC_BANK_MVT_ID, UNREC_DATE
FROM NREC_BANK a WITH (INDEX (IF1_NREC_BANK)) INNER JOIN IBC_CODES b ON a.BANK_FLOW_CODE = b.IBC_CODE
INNER JOIN ACCOUNTS c ON a.ACC_CODE = c.ACC_CODE
INNER JOIN COMPANIES d ON c.CMP_CODE = d.CMP_CODE
FROM REC_BANK_ARH a WITH (INDEX (IF1_REC_BANK_ARH)) INNER JOIN IBC_CODES b ON a.BANK_FLOW_CODE = b.IBC_CODE
INNER JOIN COMPANIES d ON c.CMP_CODE = d.CMP_CODE |
Partager