how to get the invoice number of marked transaction in the settlement of payment journal

Finance and Operations/AX Developer Forum
1

Hi guys,

There is a existing question here…

but the question is not yet answered.

here’s my query:

--1st Query

select a.JOURNALNAME, a.JOURNALNUM, b.VOUCHER, b.TXT, b.BANKCHEQUENUM, b.AMOUNTCURDEBIT, c.INVOICE
  from LEDGERJOURNALTABLE a
inner join LEDGERJOURNALTRANS b on a.JOURNALNUM = b.JOURNALNUM
left join VENDTRANS c on b.VOUCHER = c.VOUCHER --b.VENDTRANSID = c.RECID
left join VENDTRANSOPEN d on c.ACCOUNTNUM = d.ACCOUNTNUM and c.RECID = d.REFRECID
where a.JOURNALNUM = '006004'



--2nd Query
select a.JOURNALNAME, a.JOURNALNUM, b.VOUCHER, b.TXT, b.BANKCHEQUENUM, b.AMOUNTCURDEBIT, c.INVOICE, b.INVOICE
  from LEDGERJOURNALTABLE a
inner join LEDGERJOURNALTRANS b on a.JOURNALNUM = b.JOURNALNUM
left join VENDTRANS c on b.VENDTRANSID = c.RECID 
left join SPECTRANS e on c.RECID = e.SPECRECID
left join VENDSETTLEMENT f on  c.RECID = f.TRANSRECID and c.RECID = f.OFFSETRECID
where a.JOURNALNUM = '006004'

my problem is , i can’t get the invoice number …

because i want get the invoice number of marked transaction in the settlement of payment journal

2

Help plsss…

3

Hi,

You can check the relation in spectrans , Vendtransopen table.
This is the sample code. I haven’t tested this.

LedgerJournalTrans ledgerjournaltrans = LedgerJournalTrans::findLedgerJournalId(‘Voucher’);
SpecTrans spectrans;
VendTransOpen vendtransopen;
VendTrans vendtrans;

while select spectrans
join vendtransopen
join vendtrans
where spectrans.SpecCompany == ledgerjournaltrans.DataAreaId &&
spectrans.SpecTableId == ledgerjournaltrans.TableId &&
spectrans.SpecRecId == ledgerjournaltrans.RecId &&
spectrans.RefCompany == vendtransopen.DataAreaId &&
spectrans.RefTableId == vendtransopen.TableId &&
spectrans.RefRecId == vendtransopen.RecId &&
vendtransopen.RefRecId == vendtrans.RecId &&
vendtransopen.AccountNum == vendtrans.AccountNum
{
info(strFmt(’%1’, vendtrans.Invoice));
}

4

This is x++ code. You can apply same logic in sql.

5

Tnx Saju,
this help me a lot not only in SQL and also in X++ :slight_smile:

6

Hi Saju,

Similar requirement i have. but i need the invoice numbers of settled transactions of payment journal instead of marked transactions, please help