Skip to Content
0
Sep 20, 2011 at 11:20 AM

first address of my BP

35 Views

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