User Tools

Site Tools


update_fxpaid

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
update_fxpaid [2016/10/04 09:47] sweitmannupdate_fxpaid [2016/12/13 11:57] (current) sweitmann
Line 1: Line 1:
-   UPDATE ap SET fxamount = amount WHERE fxamount = 0; +    
-   UPDATE ap SET fxpaid = paid WHERE fxpaid 0; +   SELECT invnumber, amount, paid, fxamount, fxpaid FROM ap WHERE amount=paid AND fxamount <>  
 +   fxpaid AND fxpaid <> 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);    NOT fx_transaction);
 +    
 +   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 ar SET fxamount = fxpaid WHERE amount = paid;    
 +    
 + Ignore below   
 +    
 +    
 +   UPDATE ap SET fxamount = amount WHERE fxamount = 0; 
 +   UPDATE ap SET fxpaid = paid WHERE fxpaid = 0; 
 +   
    UPDATE ap SET fxpaid = (SELECT SUM(amount) FROM acc_trans WHERE    UPDATE ap SET fxpaid = (SELECT SUM(amount) FROM acc_trans WHERE
    chart_id IN (SELECT id FROM chart WHERE link LIKE '%_paid%') AND    chart_id IN (SELECT id FROM chart WHERE link LIKE '%_paid%') AND
    trans_id=ap.id AND NOT fx_transaction);    trans_id=ap.id AND NOT fx_transaction);
  
-   UPDATE ar SET fxamount=round(fxamount), fxpaid=ROUND(fxpaid);+   UPDATE ap SET fxamount=round(fxamount), fxpaid=ROUND(fxpaid); 
 +    
 +   UPDATE ap SET fxamount = fxpaid WHERE amount = paid; 
  
-   SELECT invnumber, amount, paid, fxamount, fxpaid FROM ap WHERE amount=paid AND fxamount <>  
-   fxpaid AND fxpaid <> 0; 
update_fxpaid.1475574445.txt.gz · Last modified: 2016/10/04 09:47 by sweitmann