Hi,
when completing a retail sales order, we will need to insert the payment amount and payment method.
These payments are inside MCRCustPaymTable.
So, when submitting the sales order payment, a customer payment journal (LedgerJournalTrans) will be created and posted.
For each payment method, different journal will be created for the same sales order.
These customer payment journal will have related customer transactions (CustTrans) records.
I have a requirement to list out all sales order payments and display Sales order number, payment method, payment amount, voucher number, Retail channel, and whether the payment has been reversed in CustTrans.
In order to do this, I need to somehow link SalesTable - MCRCustPaymTable - LedgerJournalTrans - CustTrans in a single query.
Is there any way to link one-on-one between MCRCustPaymTable and LedgerJournalTrans? I cant seem to find the correct relation based on standard table relationships.
Thank You.
What do you mean?
Submitting the payment from sales order will automatically create and post the customer payment journal.
I checked the sales order Payments form, the payments are already posted.
But we never have the LedgerJournalTrans reference.
Hi Crispine,
I actually noticed this relation before. Supposedly they're linkable using MCRCustPaymTable.RefTableId and MCRCustPaymtable.RefRecId.
Unfortunately, all data in our MCRCustPaymTable only refers to SalesTable.
There's no data in our MCRCustPaymTable where RefTableId = LedgerJournalTrans.TableId even though the customer payment journal records are there.
Do you know why is this? Is there any setup we need to do or something?
Thank you.
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156