Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision |
update_fxpaid [2016/12/13 10:53] – sweitmann | update_fxpaid [2016/12/13 11:19] – sweitmann |
---|
SELECT invnumber, amount, paid, fxamount, fxpaid FROM ap WHERE amount=paid AND fxamount <> | SELECT invnumber, amount, paid, fxamount, fxpaid FROM ap WHERE amount=paid AND fxamount <> |
fxpaid AND fxpaid <> 0 OR fxpaid is NULL; | fxpaid AND fxpaid <> 0 OR fxpaid is NULL; |
| |
| |
| |
UPDATE ap SET fxamount = (SELECT SUM(0-amount) FROM acc_trans WHERE | UPDATE ap SET fxamount = (SELECT SUM(0-amount) FROM acc_trans WHERE |
chart_id IN (SELECT id FROM chart WHERE link LIKE '%AP_amount%' OR | chart_id IN (SELECT id FROM chart WHERE link LIKE '%AP_amount%' OR |
link LIKE '%IC_expense%' OR link LIKE '%tax%') AND trans_id=ap.id and | link LIKE '%IC_expense%' OR link LIKE '%tax%') AND trans_id=ap.id and |
| NOT fx_transaction); |
| |
| UPDATE ar SET fxamount = (SELECT SUM(amount) FROM acc_trans WHERE |
| chart_id IN (SELECT id FROM chart WHERE link LIKE '%AR_amount%' OR |
| link LIKE '%IC_income%' OR link LIKE '%tax%') AND trans_id=ar.id and |
NOT fx_transaction); | NOT fx_transaction); |
| |
UPDATE ap SET fxpaid = paid WHERE fxpaid is NULL; | UPDATE ap SET fxpaid = paid WHERE fxpaid is NULL; |
| |
| UPDATE ar SET fxpaid = paid WHERE fxpaid is NULL; |
| |
UPDATE ap SET fxamount = fxpaid WHERE amount = paid; | UPDATE ap SET fxamount = fxpaid WHERE amount = paid; |
| |