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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
|
SELECT
ACCOUNTNUM,
TXT,
TRANSDATE,
LASTSETTLEDATE,
DUEDATE,
VOUCHER,
INVOICE,
DIMENSION7_,
DIMENSION6_,
DIMENSION,
CASE WHEN DOCUMENTDATE = '1900-01-01' THEN TRANSDATE ELSE DOCUMENTDATE END AS DATE_CALCUL,
DOCUMENTDATE,
CLOSED,
AMOUNTCUR,
CASE WHEN LASTSETTLEDATE <= GETDATE() THEN SETTLEAMOUNTCUR ELSE 0 END AS SETTLEAMOUNTCUR,
AMOUNTMST,
CASE WHEN LASTSETTLEDATE <= GETDATE() THEN SETTLEAMOUNTMST ELSE 0 END AS SETTLEAMOUNTMST,
CURRENCYCODE,
EXCHADJUSTMENT,
RECID,
CASE WHEN LASTSETTLEDATE <= GETDATE() THEN (AMOUNTMST - SETTLEAMOUNTMST + EXCHADJUSTMENT) ELSE AMOUNTMST END AS MT,
CASE WHEN LASTSETTLEDATE <= GETDATE() THEN (AMOUNTCUR - SETTLEAMOUNTCUR) ELSE AMOUNTCUR END AS MT_DEVISE,
CASE WHEN LASTSETTLEDATE <= GETDATE() THEN ABS(AMOUNTCUR - SETTLEAMOUNTCUR) ELSE ABS(AMOUNTCUR) END AS ABS_MT_DEVISE,
DIMENSION5_ as CODE_OM,
YLFHOUSEBILLNO as HB_NO
FROM
CUSTTRANS
WHERE
TRANSDATE <= GETDATE()
GROUP BY
ACCOUNTNUM,
TXT,
TRANSDATE,
LASTSETTLEDATE,
DUEDATE,
VOUCHER,
INVOICE,
DIMENSION7_,
DIMENSION6_,
DIMENSION,
DOCUMENTDATE,
CLOSED,
AMOUNTCUR,
SETTLEAMOUNTCUR,
CURRENCYCODE,
AMOUNTMST,
SETTLEAMOUNTMST,
EXCHADJUSTMENT,
RECID,
AMOUNTMST - SETTLEAMOUNTMST,
AMOUNTCUR - SETTLEAMOUNTCUR,
DIMENSION5_,
YLFHOUSEBILLNO
HAVING (SUM(AMOUNTCUR) - SUM(SETTLEAMOUNTCUR)<> 0) OR ((SUM(AMOUNTCUR) - SUM(SETTLEAMOUNTCUR)= 0) AND LASTSETTLEDATE > GETDATE())
order by ACCOUNTNUM, ABS_MT_DEVISE |
Partager