Hello everyone, I need some help for my report (again).
I want to get a table of data like this:
However, the table I get is different from what I want where the balance column is not calculated correctly. It should be the sum of the previous balance and the current amount. But this is the data that i get
as you can see the balance is not calculated as i want but there are also data like this
where some data balance is correct but after that the data is wrong.
This is how my dp class look like. Can you tell me what I should do to fix these problems? Thank you for your help.
public void processReport()
{
VendTrans vendTransPurch, vendTransPaym;
LogisticsPostalAddress address;
DirPartyLocation partyLocation;
VendTable vendTable;
QueryRun qRun;
Query qr;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
Amount balanceAmount = 0, balanceAmountInv = 0;
Voucher lastSettleVcr;
VendSettlement vendSettle;
AccountNum accountNum;
;
contract = this.parmDataContract() as WCS_FIN_VendorStatement_v1ReportContract;
fromDate = contract.parmFromDate();
toDate = contract.parmToDate();
qr = this.parmQuery();
qbds = qr.dataSourceTable(tableNum(VendTrans));
//qbds.addSortField(fieldNum(VendTrans, TransType), SortOrder::Ascending);
if (fromDate == dateNull())
{
throw error("From date must be filled in");
}
else if (toDate == dateNull())
{
throw error("To date must be filled in");
}
else
{
qbr = qbds.addRange(fieldNum(VendTrans, TransDate));
qbr.value(queryRange(fromDate, toDate));
}
qRun = new QueryRun(qr);
while (qRun.next())
{
vendTable = qRun.getNo(1);
vendTransPurch = qRun.getNo(2);
select firstonly partyLocation
join address
where address.Location == partyLocation.Location
&& partyLocation.Party == vendTable.Party;
if (accountNum != vendTransPurch.AccountNum)
{
balanceAmountInv = this.getOpenTrans(vendTransPurch.AccountNum, fromDate, vendTable, address);
accountNum = vendTransPurch.AccountNum;
}
if (vendTransPurch.TransType == LedgerTransType::Purch
|| vendTransPurch.TransType == LedgerTransType::Vend
|| vendTransPurch.TransType == LedgerTransType::GeneralJournal)
{
vendorStmtTmp.clear();
vendorStmtTmp.VendAccount = vendTransPurch.AccountNum;
vendorStmtTmp.VendName = vendTable.name();
vendorStmtTmp.address = address.Address;
vendorStmtTmp.StatementDate = today();
vendorStmtTmp.PaymTermId = vendTable.PaymTermId;
vendorStmtTmp.Dates = vendTransPurch.TransDate;
vendorStmtTmp.Types = "IN";
vendorStmtTmp.Voucher = vendTransPurch.Voucher;
vendorStmtTmp.Desciptions = "Invoice";
//vendorStmtTmp.Desciptions = vendTransPurch.Invoice;
vendorStmtTmp.SettleAmountCurCr = abs(vendTransPurch.AmountMST);
vendorStmtTmp.TransType = vendTransPurch.TransType;
if (balanceAmountInv == 0)
{
balanceAmountInv = abs(vendTransPurch.AmountMST);
}
else
{
balanceAmountInv += abs(vendTransPurch.AmountMST);
}
vendorStmtTmp.BalanceAmount = balanceAmountInv;
vendorStmtTmp.insert();
while select vendSettle
where vendSettle.OffsetTransVoucher == vendTransPurch.Voucher
{
vendorStmtTmp.clear();
vendorStmtTmp.VendAccount = vendSettle.AccountNum;
vendorStmtTmp.VendName = vendTable.name();
vendorStmtTmp.address = address.Address;
vendorStmtTmp.StatementDate = today();
vendorStmtTmp.PaymTermId = vendTable.PaymTermId;
vendorStmtTmp.Dates = vendSettle.TransDate;
vendorStmtTmp.Types = "OR";
vendorStmtTmp.Voucher = VendTrans::find(vendSettle.TransRecId).Voucher;
vendorStmtTmp.Desciptions = vendSettle.OffsetTransVoucher;
vendorStmtTmp.SettleAmountCurDr = vendSettle.SettleAmountMST;
vendorStmtTmp.TransType = LedgerTransType::Payment;
if (balanceAmount == 0)
{
balanceAmount = abs(balanceAmountInv) - abs(vendSettle.SettleAmountMST);
}
else
{
balanceAmount -= abs(vendSettle.SettleAmountMST);
}
vendorStmtTmp.BalanceAmount = balanceAmount;
vendorStmtTmp.Days = date2Num(vendSettle.TransDate) - date2Num(vendTransPurch.TransDate);
vendorStmtTmp.insert();
}
}
else if(vendTransPurch.TransType == LedgerTransType::Payment)
{
select vendSettle
where vendSettle.OffsetTransVoucher == vendTransPurch.Voucher;
if (!vendSettle)
{
vendorStmtTmp.clear();
vendorStmtTmp.VendAccount = vendTransPurch.AccountNum;
vendorStmtTmp.VendName = vendTable.name();
vendorStmtTmp.address = address.Address;
vendorStmtTmp.StatementDate = today();
vendorStmtTmp.PaymTermId = vendTable.PaymTermId;
vendorStmtTmp.Dates = vendTransPurch.TransDate;
vendorStmtTmp.Types = "OR";
vendorStmtTmp.Voucher = vendTransPurch.Voucher;
vendorStmtTmp.Desciptions = "Payment";
vendorStmtTmp.SettleAmountCurCr = abs(vendTransPurch.AmountMST);
vendorStmtTmp.TransType = vendTransPurch.TransType;
if (balanceAmountInv == 0)
{
balanceAmountInv = abs(vendTransPurch.AmountMST);
}
else
{
balanceAmountInv += abs(vendTransPurch.AmountMST);
}
vendorStmtTmp.BalanceAmount = balanceAmountInv;
vendorStmtTmp.insert();
}
}
balanceAmountInv = balanceAmount;
balanceAmount = 0;
}
}
public Amount getOpenTrans(AccountNum _vend, FromDate _fromDate, VendTable _vendTable, LogisticsPostalAddress _address)
{
VendTransOpen vendTransOpen;
LogisticsPostalAddress address;
DirPartyLocation partyLocation;
VendTable vendTable;
Amount balanceAmount = 0;
;
vendorStmtTmp.clear();
select firstonly vendorStmtTmp
where vendorStmtTmp.VendAccount == _vend;
if (!vendorStmtTmp)
{
while select vendTransOpen
where vendTransOpen.AccountNum == _vend
&& vendTransOpen.TransDate < _fromDate
{
vendorStmtTmp.clear();
vendorStmtTmp.VendAccount = _vend;
vendorStmtTmp.VendName = _vendTable.name();
vendorStmtTmp.address = _address.Address;
vendorStmtTmp.StatementDate = today();
vendorStmtTmp.PaymTermId = _vendTable.PaymTermId;
vendorStmtTmp.Dates = vendTransOpen.TransDate;
vendorStmtTmp.Types = "IN";
vendorStmtTmp.Voucher = VendTrans::find(vendTransOpen.RefRecId).Voucher;
//vendorStmtTmp.Desciptions = "Invoice";
vendorStmtTmp.Desciptions = VendTrans::find(vendTransOpen.RefRecId).Invoice;
vendorStmtTmp.SettleAmountCurCr = abs(vendTransOpen.AmountMST);
vendorStmtTmp.TransType = VendTrans::find(vendTransOpen.RefRecId).TransType;
if (balanceAmount == 0 && vendorStmtTmp.SettleAmountCurCr > 0)
{
balanceAmount = abs(vendTransOpen.AmountMST);
}
else
{
balanceAmount += abs(vendTransOpen.AmountMST);
}
vendorStmtTmp.BalanceAmount = balanceAmount;
vendorStmtTmp.insert();
}
}
return balanceAmount;
}