How to Connect OITW, OPOR, POR1

[Former Member]

Hello Everyone,

I am trying to create a reports where it gives the Quantity On Hand, Backorderd Quantity, and Quantity on Order and Purchase Order date.

The only problem I have here is the Purchase Order date.

Pseudocode for my formula is below:

If Quantity on Hand  < 30 and OITW.OnOrder > 0 Then

     Bring the Open Purchase Order's Due Date

I have tried the formula but it brings some crazy dates and it looks like these tables are not connected in a proper way.

What I need to know is:

- How to connect these tables

- If I am doing anything wrong with the formula

Thanks.

[János_at_SAP · Accepted solution]

Hello,

It is pending on the version of SAP B1 you are using. In newer versions (9.x, 8.82).  and the customized logic such as product subsitutions, product trees, and of course the WH management logic, and etc. Here is a simple logic

You may do the following.

1. Open CRD and use SAP Business One Data connector or OLEBD to connect to MS SQL database

2. You need tables from OPOR, POR1, OITW, OITM (this is not really needed, just for safeness)

You can join OITM to POR1 on OITM.ItemCode = POR1.ItemCode and OITM.ItemCode = OITW.ItemCode

also OITW.ItemCode = POR1.ItemCode and OITW.WhsCode = POR1.WhsCode

and the last POR1.DocEntry = OPOR.DocEntry

So there is and issue: if the product comes more than 1 lines in PO, than you will retreive duplicate rows, so this is not possible.

BTW there is a Bult in ATP check in SAP B1 since 8.81 and it is always getting approved version by version.

Cheers

János

[Former Member · Accepted solution]

I did the table connections as you said but still the dates are pretty unrelated.

[Former Member · Accepted solution]

I have handled this in a very interesting way. I have added a Running Total Fields with some formulas on it so that it shows me the PO that is placed on that item.

So far it seems like it is working.