I have problem with SupplierBalsAtPeriodEnd.php

[kelo]

Hi, i have problem with script SupplierBalsAtPeriodEnd.php at module payable->SupplierBalsAtPeriodEnd

this is my dump supptrans file for supplier id=SPL-00045 => http://palucity.com/suptrans.xlsx

and base on that, i am expected the result of balance is 869,000 from supplier no SPL-00045
because there is only 1 row in year 2012 in the database.

when i enter this value

From Supplier Code : 1
To Supplier Code : zzzzzz
Balances As At : December 2012,

the result for supplier SPL-0045 is
SPL-0045 = 1,738,000.00

what's wrong? please help....

[phil]

Not sure? looks like there must be some transactions before this date for this supplier that perhaps you deleted from the DB?
Don't forget that this script is backing out allocations (suppallocs) to get the balance at a previous month end.

[kelo]

Hi phill,
thanks for reply..

i am confuse because there is no connection to suppallocs when i look at the sql script at SupplierBalsAtPeriodEnd.php

Code:

SELECT suppliers.supplierid,
                    suppliers.suppname,
                      currencies.currency,
                      currencies.decimalplaces AS currdecimalplaces,
                    SUM((supptrans.ovamount + supptrans.ovgst - supptrans.alloc)/supptrans.rate) AS balance,
                    SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS fxbalance,
                    SUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "' THEN
            (supptrans.ovamount + supptrans.ovgst)/supptrans.rate ELSE 0 END) AS afterdatetrans,
                    SUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "'
                        AND (supptrans.type=22 OR supptrans.type=21) THEN
                        supptrans.diffonexch ELSE 0 END) AS afterdatediffonexch,
                    SUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "' THEN
                        supptrans.ovamount + supptrans.ovgst ELSE 0 END) AS fxafterdatetrans
            FROM suppliers INNER JOIN currencies
            ON suppliers.currcode = currencies.currabrev
            INNER JOIN supptrans
            ON suppliers.supplierid = supptrans.supplierno
            WHERE suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'
            AND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'
            GROUP BY suppliers.supplierid,
                suppliers.suppname,
                currencies.currency,
                currencies.decimalplaces

[phil]

My error sorry - forgot how it worked.