Get total debit and credit against voucher in x++

(0) Share

Report

Posted on by Faran Baig

125

Hi any one please give me the query to get total debit and credit in X++ on the base of voucher #,not on the base of JounalNum,i am working on General ledger voucher report and i have to get debit and credit in single line and multiple line case,i am not getting one value say debit 100 and other value credit 0 in single line case from LedgerJournalTrans Table.(In Voucher credit is balanced by offset account).

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (2)

Verified answer

Rustem Galiamov 8,072 on at

Like (2)

Report
Hi Faran Baig!

Something like this?

static void Job203(Args _args) { GeneralJournalEntry generalJournalEntry; GeneralJournalAccountEntry generalJournalAccountEntryCredit, generalJournalAccountEntryDebit; while select generalJournalEntry where generalJournalEntry.SubledgerVoucher == 'UNC-00000111' { select sum(AccountingCurrencyAmount) from generalJournalAccountEntryDebit where generalJournalAccountEntryDebit.IsCredit == NoYes::No && generalJournalAccountEntryDebit.GeneralJournalEntry == generalJournalEntry.RecId; select sum(AccountingCurrencyAmount) from generalJournalAccountEntryCredit where generalJournalAccountEntryCredit.IsCredit == NoYes::Yes && generalJournalAccountEntryCredit.GeneralJournalEntry == generalJournalEntry.RecId; info(strFmt("Voucher: %1. Debit: %2. Credit: %3.", generalJournalEntry.SubledgerVoucher, generalJournalAccountEntryDebit.AccountingCurrencyAmount, generalJournalAccountEntryCredit.AccountingCurrencyAmount)); } }

Was this reply helpful? Yes No
Faran Baig 125 on at

Like (0)

Report

Thanks Rustem Galiamov ,it solved my problem

Was this reply helpful? Yes No
Faran Baig 125 on at

Like (0)

Report

Hi Rustem,i m facing problem again,query is working fine in case of posted vouchers,but in case of open vouchers,it is not working.It is not giving credit and debit.Any help will be appreciated.

Was this reply helpful? Yes No
Suggested answer

Rustem Galiamov 8,072 on at

Like (0)

Report

Hi Faran Baig!

You can use AmountCurDebit and AmountCurCredit fields from LedgerJournalTrans table.

Was this reply helpful? Yes No
Faran Baig 125 on at

Like (0)

Report

Issue is that,in single line case one of two amounts is zero either debit or credit,which is settled by offset account.I have already used it

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report

Sure. The records in GeneralJournalEntry and GeneralJournalAccountEntry tables are generates after voucher posting.

Was this reply helpful? Yes No
Faran Baig 125 on at

Like (0)

Report

so is there any way to get it before voucher posting?

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report

Can you provide some template/screenshot of your report?

Was this reply helpful? Yes No
Faran Baig 125 on at

Like (0)

Report

These are screenshots,first one is open voucher in General Ledger ,second is customized voucher report , i am not getting amount words field,because of open voucher.As in report showing debit and credit column,i tried,but not get pick the values from that column(issue in single and multi-line case also).

Was this reply helpful? Yes No
Verified answer

Rustem Galiamov 8,072 on at

Like (2)

Report
Try to use LedgerJournalEngine class:

static void Job203(Args _args) { LedgerJournalTrans ledgerJournalTrans; ledgerJournalEngine ledgerJournalEngine = new ledgerJournalEngine(); ledgerJournalTable ledgerJournalTable; while select ledgerJournalTrans where ledgerJournalTrans.Voucher == 'UNC-00051795' { ledgerJournalTable = ledgerJournalTable::find(ledgerJournalTrans.JournalNum); ledgerJournalEngine.newJournalActive(ledgerJournalTable,true); info(strFmt("Voucher:%1 Debit: %2 Credit: %3", ledgerJournalTrans.Voucher, ledgerJournalEngine.voucherDebit(ledgerJournalTrans), ledgerJournalEngine.voucherCredit(ledgerJournalTrans))); } }

Was this reply helpful? Yes No