cancel
Showing results for 
Search instead for 
Did you mean: 

Join Sales Order - Received payments and Business Partner

0 Kudos

Hi team,

Need to build a query with this tables from SAP B1:

ORDR - Sales Order

RDR1 - Sales Order - Rows

OCRD - Business Partner Data Master

ORCT - Payments Received

how can I join all these tables to use their fields?

I need to know the payments received for the items purchased by the customer

Regards.

former_member280812
Active Participant

Hi,

I don't think you can link ORCT without the OINV.

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Thanks for your Comment Abdul, your words give and idea and now i have the solution.

Thanks for give me light.

Regards.

Answers (2)

Answers (2)

0 Kudos

Hi Abdul,

Currently our sales cycle is as follows: Sales order - Payments on account of the Business Partner until he finishes paying the products (cause can be me more of one). This way is for a local taxes issue. What I need is a report that shows me how much the customer paid for each product until a certain date.

Attached example of a Business partner that buy two products (had two order sales) and had made Payments on account every month for each one, so the report has to shows this two order sales and sum all payments, (for example, since 2016/01/01 until 2019/07/08) and group this payments by Article or products.sales-cycle.jpg

sales-cycle.jpg (324.4 kB)

Abdul
Active Contributor

If user is mentioning Sales order ref in Incoming payment then you can join Sales order table with the ref field on incoming payment.

Here another question comes what about sales order for which you received partial payment ?/

Option 1: Divide total payments values to total sales order value and multiply with each line, it will proportionate the payment

Option 2: mention item code or line number of Sales order in incoming payment then develop report accordingly

Abdul
Active Contributor
0 Kudos

Please explain in detail what is your complete cycle in standard cycle

You have to join Sales order with delivery, then delivery with A/R invoice then A/R invoice with incoming payment.

0 Kudos

Hi Abdul,

Currently our sales cycle is as follows: Sales order - Payments on account of the Business Partner until he finishes paying the products (cause can be me more of one). This way is for a local taxes issue. What I need is a report that shows me how much the customer paid for each product until a certain date.

Attached example of a Business partner that buy two products (had two order sales) and had made Payments on account every month for each one, so the report has to shows this two order sales and sum all payments, (for example, since 2016/01/01 until 2019/07/08) and group this payments by Article or products.sales-cycle.jpg