Please can anyone tell me how I can link the General Ledger Actual table (GENERALJOURNALACCOUNTENTRY) to either the Purchase Order (PURCHTABLE), Purchase Order Line (PURCHLINE) or another table so I can get the Purchase Order Number. I need to be able to get the purchase order number for GL records that have not been invoiced.
Many thanks
*This post is locked for comments
Hi,
You can either refer the post suggested by Vilmos (or) can use the following job to get the link.
This job helps to find the link for the invoice and product receipt transactions.
static void PurchTransactions(Args _args)
{
GeneralJournalAccountEntry journalAccountEntry;
GeneralJournalEntry generalJournalEntry;
PurchTable purchTable;
PurchLine purchLine;
VendTrans vendTrans;
VendInvoiceJour vendInvoiceJour;
VendPackingSlipJour vendPackingSlipJour;
VendPackingSlipVersion vendPackingSlipVersion;
SubledgerVoucherGeneralJournalEntry subledgerVoucherGeneralJournalEntry;
// Purchase order invoice transactions
while select journalAccountEntry
join generalJournalEntry
where journalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
join subledgerVoucherGeneralJournalEntry
where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
join vendTrans
where vendTrans.Voucher == subledgerVoucherGeneralJournalEntry.Voucher
&& vendTrans.transDate == subledgerVoucherGeneralJournalEntry.AccountingDate
&& vendTrans.Voucher == "xyz"
join vendInvoiceJour
where vendInvoiceJour.LedgerVoucher == vendTrans.Voucher
&& vendInvoiceJour.InvoiceAccount == vendTrans.AccountNum
&& vendInvoiceJour.InvoiceDate == vendTrans.TransDate
join purchTable
where purchTable.PurchId == vendInvoiceJour.PurchId
// join purchLine
// where purchLine.PurchId == purchTable.PurchId
{
info(strFmt("PurchId: %1", purchTable.PurchId));
}
// Purchase order product receipt transactions
while select journalAccountEntry
join generalJournalEntry
where journalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
join subledgerVoucherGeneralJournalEntry
where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
join vendPackingSlipVersion
where (vendPackingSlipVersion.LedgerVoucher == subledgerVoucherGeneralJournalEntry.Voucher
|| vendPackingSlipVersion.CostLedgerVoucher == subledgerVoucherGeneralJournalEntry.Voucher)
&& vendPackingSlipVersion.AccountingDate == subledgerVoucherGeneralJournalEntry.AccountingDate
//&& subledgerVoucherGeneralJournalEntry.Voucher == "xyz"
join vendPackingSlipJour
where vendPackingSlipJour.RecId == vendPackingSlipVersion.VendPackingSlipJour
join purchTable
where purchTable.PurchId == vendPackingSlipJour.PurchId
// join purchLine
// where purchLine.PurchId == purchTable.PurchId
{
info(strFmt("PurchId: %1", purchTable.PurchId));
}
}
If you feel your question is resolved, please mark the answer as verified so that it helps others.
Thanks,
Chaitanya Golla
Have a look at the following post on matching ledger postings with purchases:
#1
André Arnaud de Cal...
291,820
Super User 2024 Season 2
#2
Martin Dráb
230,514
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
