AP Invoice (OPCH) Paid By AP Check Number (OCHO)

[zal_parchem2]

Hello fellow SAP B1ersu2026

I am looking to write a very simple query to be the basis for a formatted search (FMS) in SAP B1 2007A and am really stumped.

Requirement is to see the AP Check Number that was used to pay for an AP Invoice, so I thought a simple query to test before doing the FMS would look like this:

Col 1 u2013 AP Invoice Number

Col 2 u2013 Payment Number

Col 3 u2013 Check Number

I have looked at OPCH to PCH6, OVPM to VPM6, OCHO and bunches of CHOx. It looks like OPCH (AP Invoice Number) and OCHO (Payment and Check Numbers) have the information needed.

But how do you join OPCH to OCHO ?

I have searched the forum and the thread that comes closest is this one:

However, it has a little bit of a problem. At the most critical point of the JOINs of joining the AP Invoice to the Payments, it has DepositNum on OPCH, but there is no such field on table OPCH. And the one field that is even close called ReceiptNum (this is good for a chuckle) leads to Incoming Payments.

Maybe my eyes are crossing since I have looked at it for so long, but I sure would appreciate helpu2026

Thanks,

Zal

[Former Member]

Hello Zal,

If all your data related to XXX6 tables, then they are installments. It would be a tough query to build since I don't have any data for that.

Have your checked XXX2 tables?

Thanks,

Gordon

[zal_parchem2]

Hello Gordon...

Finally figured it out. In this instance it is the order of how you place the tables into the Query Generator and Query. This is a real detour around the corner to get to the data needed (5 tables). Will let you how it is done when I get it finalized as I am using index cards taped to the wall to plot this all out.

LOL - how do I give myself points???

Thanks Zal