first address of my BP

[vasileiosfasolis]

hi there

i have some BPs that have more than one address assigned to their page

it means that they have two factorys for example

how can i get only the first address(only the country) from my BP;s contact card?

SELECT T0.[DocNum]'A/R inv',

T1.[visorder] InvLine,

p.Docnum'A/P inv',

pp.CardName,c.name,C2.name,

T1.[ItemCode],p.NumAtCard,

T2.U_IntCode

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

left outer join PCH1 pr on pr.ItemCode=T2.ItemCode and not exists

(select 1 from pch1 w where w.docentry>pr.docentry and

w.itemcode=pr.itemcode)

left outer join opch p on pr.docentry=p.docentry

left outer join ocrd pp on pp.CardCode=coalesce(p.CardCode,T2.Cardcode)

left outer join crd1 s1 on s1.CardCode=pp.CardCode and s1.Adrestype='B'

and s1.Linenum=(select min(w.linenum) from crd1 w

where w.CardCode=pp.CardCode and w.Adrestype='B'

and w.country is not null)

left outer join ocry c on c.code=s1.country

left outer join crd1 s2 on s2.CardCode=T2.CardCode and s2.Adrestype='B'

and s2.Linenum=(select min(w.linenum) from crd1 w

where w.CardCode=T2.CardCode and w.Adrestype='B'

and w.country is not null)

left outer join ocry c2 on c2.code=s2.country

WHERE T0.DocNum = '1501244'

how should i edit the above code?

thanks in advance

[former_member204969 · Accepted solution]

The above query gets the firstly defined bill to address of the partners. If you want to get the ship to address, you should replace the two Adrestype='B' to Adrestype='S'

[vasileiosfasolis · Accepted solution]

i actually have 3billing addresses...how can i get the first one only?

by adding the following bold is it right?

left outer join crd1 s1 on s1.CardCode=pp.CardCode and s1.Adrestype='B'

and s1.Linenum=(select min(w.linenum) from crd1 w

where w.CardCode=pp.CardCode and s1.linenum='0' and w.Adrestype='B'

and w.country is not null)

Edited by: Fasolis Vasilios on Sep 20, 2011 2:25 PM

[former_member204969 · Accepted solution]

In the BP master data Addresses tab the system shows the addresses ordered by the Address id. So the one you first see can retrieved by this:

SELECT T0.[DocNum]'A/R inv',
 T1.[visorder] InvLine,
 p.Docnum'A/P inv',
 pp.CardName,c.name,C2.name,
 T1.[ItemCode],p.NumAtCard--,
-- T2.U_IntCode 
FROM OINV T0
  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
  INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
  left outer join PCH1 pr on pr.ItemCode=T2.ItemCode and not exists
	(select 1 from pch1 w where w.docentry>pr.docentry and 
	  w.itemcode=pr.itemcode)
  left outer join opch p on pr.docentry=p.docentry
  left outer join ocrd pp on pp.CardCode=coalesce(p.CardCode,T2.Cardcode)
  left outer join crd1 s1 on s1.CardCode=pp.CardCode and s1.Adrestype='B'
    	and s1.Linenum=(select top 1 w.linenum from crd1 w
	where w.CardCode=pp.CardCode and w.Adrestype='B'
	 and w.country is not null order by w.address)
  left outer join ocry c on c.code=s1.country
  left outer join crd1 s2 on s2.CardCode=T2.CardCode and s2.Adrestype='B'
    	and s2.Linenum=(select top 1 w.linenum from crd1 w
	where w.CardCode=pp.CardCode and w.Adrestype='B'
	 and w.country is not null order by w.address)
  left outer join ocry c2 on c2.code=s2.country

[Former Member]

Hi,

Have you set up default bill-to address? If yes, it will be much simpler.

Thanks,

Gordon

[vasileiosfasolis]

Hi Gordon

I have done it

[Former Member]

Hi Vasilios......

You can not get the direct First Address of BP from CRD1 untill and unless you have select Address Reference in Marketing Documnet while Transaction.....

Then you can have inner or outer join with Bill To Address code of OPCH with Bill To Address code of CRD1

Regards,

Rahul