Linking OINV to OCRD

[Former Member]

I am trying to write a quick query in B1 and am having difficulty linking the OINV table to the OCRD table. The reason I need to do this is that the city and state of the Ship-To address is in OCRD in individual fields but in OINV it is in a continuous string.

Any help would be much appreciated.

Regards,

Akil

[János_at_SAP]

Hey,

If you use B1 version >= 8.x you can use the address record. If your address is coming from BP Master data than you can try the table INV12 to check the adddress record. (every document table ends to 12 contains the addresses).

BTW,

1. OINV.CardCode = OCRD.CardCode links the Invoices with BP Master data.

2. CRD1 contains the addresses, with 2 types  B=Bill to and S = Ship-to

The issue that the B1 does not have and address key as integer. The address key is string, the address name, which is stored in every marketing document head table and crd1 table.

If you check the SDK Help page -> tables section, you can see the linking.

Since in the B1 the address can be renamed any time, there is no continous link between any marketing document and BP master data excep if you use address structure.

So Piere query is ok with those above i have added as a comment.

Also please note: You BP's address name is XYZ. Now you rename it to XYZA and you create a new address with XYZ, your query will fail.

So the correct solution is:

1. Create a stored procedure or a function which reads the BP country

2. Opens the country address definition

3. Extracts the fields from there and splits the address text

4. returns the country, street, etc

or use address structrure.

I hope it is clarifed and helps

János

[pierrecanali]

That's correct. Thank you.

[pierrecanali]

Hi Akil,

try with this

select
t1.*
from OINV t0
join CRD1 t1 on t0.CardCode = t1.CardCode and t0.ShipToCode = t1.Address and t1.AdresType = N'S'

This will give all shipping address fields related to an invoice.

Regards

Pierre