Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Link Sales Order Payment to Customer Payment Journal

(0) ShareShare
ReportReport
Posted on by 1,204

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.

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,204 on at
    RE: Link Sales Order Payment to Customer Payment Journal

    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.

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,204 on at
    RE: Link Sales Order Payment to Customer Payment Journal

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans