how to bring the BP's country of origin

[Former Member]

hello all

i have done the following query and the problem i have is that i cannot bring the BP's country of origin

SELECT

T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBόu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3ήu03C2',

T0.DOCDATE,

max(T1.[ItemCode]),

T0.NUMATCARD,

T2.U_INTCODE,

T1.[Quantity],

T1.[Price],

T1.[LineTotal],

SUM(T2.[BWeight1]*T1.[Quantity]) AS 'u0392άu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCήu03C2',

T3.[CardName]

FROM OINV T0

full outer JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

full outer JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

full outer JOIN OCRD T3 ON T2.CardCode = T3.CardCode

INNER JOIN CRD1 T4 ON T0.CardCode = T4.CardCode

inner join ocry t8 on t8.code=t4.country

WHERE T0.[DocNum] = '1403408'

GROUP BY

T3.[CardName],

T1.[ItemCode],

T1.[Quantity], T1.[Price], T1.[LineTotal],T0.DOCNUM,T0.DOCDATE,T2.U_INTCODE,t1.linenum,T0.NUMATCARD

ORDER BY T1.[ItemCode]

the actual problem is that some BP's have many addresses and some of them have adrresses in different countries.

so i want to get the last invoice's country of origin of the BP

i have done many efforts such as

...

...

Select s5.name from ocry s5 inner join ocrd s6 on s5.code=s6.country inner join opch s7 on s6.cardcode=s7.cardcode where s7.docentry=max(s2.docentry)) as 'u03A7ώu03C1u03B1 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4ή u03A4u03B9u03BCu03BFu03BBu03BFu03B3ίu03BFu03C5',

..

..

inner join ocry t8 on t8.code=t4.country

inner join pch1 s1 on t1.itemcode = s1.itemcode

inner join opch s2 on s1.docentry=s2.docentry

but the results are not the expected. it does not bring me all the recorns from the invoice

do you have any idea?

[Former Member · Accepted solution]

Hi.......

Try this......

SELECT
T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBu03CCu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3u03AEu03C2',
T0.DOCDATE,
max(T1.ItemCode),
T0.NUMATCARD,
T2.U_INTCODE,
T1.Quantity,
T1.Price,
T1.LineTotal,
SUM(T2.BWeight1*T1.Quantity) AS 'u0392u03ACu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCu03AEu03C2',
T3.CardName, T8.Name 'Country'
FROM OINV T0
full outer JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
full outer JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
full outer JOIN OCRD T3 ON T2.CardCode = T3.CardCode
LEFT JOIN INV12 T4 ON T0.DocEntry = T4.DocEntry
LEFT join ocry t8 on t8.code=t4.countryS
WHERE T0.DocNum = '1403408' 
GROUP BY
T3.CardName,
T1.ItemCode,
T1.Quantity, T1.Price, T1.LineTotal,T0.DOCNUM,T0.DOCDATE, t1.linenum,T0.NUMATCARD, T8.Name,T2.U_INTCODE
ORDER BY T1.ItemCode

Hope this will help you.....

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul again...

this makes an error with the column countryS

Msg 207, Level 16, State 1, Line 17

Invalid column name 'countryS'.

i changed the countryS to county and it does not bring me resul;ts

its says...'NULL'

Edited by: Vasilis Fasolis on Sep 15, 2011 2:41 PM

[Former Member · Accepted solution]

HI......

This Query is running is perfectly in my DB.

Please check in INV12 table whether this CountryS Field is present in your Localization or not.....

Else you have to use CRD1 Table instead of INV12 where CRD1.CardCode=OINV.CardCode and further have join with OCRY where Countrycode of CRD1 = Name of OCRY

Regards,

Rahul

[Former Member · Accepted solution]

unfortunatelly i dont have this field in my db as far as i see.

i have tried the replacement of inv12 with crd1 but still the results are duplicate

[jitin_chawla · Accepted solution]

Hi,

If your address format is having the country name appearing and the format is same for all the Business Partner :

say for example :

303, XYZ Street,

UK

then to get UK listed in the query as 'Country' from the bill-to address , try using the RIGHT function

RIGHT ('address', 2)

Check if you can incorporate the same in your query somehow and get the desired result.

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member · Accepted solution]

i brought succesfully the origin of the BP according to the last invoce

SELECT

T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBόu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3ήu03C2',

T3.CardName,

(Select s5.name from ocry s5 inner join ocrd s6 on s5.code=s6.country inner join opch s7 on s6.cardcode=s7.cardcode where s7.docentry=max(s2.docentry)) as 'u03A7ώu03C1u03B1 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4ή u03A4u03B9u03BCu03BFu03BBu03BFu03B3ίu03BFu03C5',

T0.DOCDATE,

T1.ItemCode,

(Select s2.numatcard from opch s2 where s2.docentry=max(s1.docentry)) as 'u0391u03C1u03B9u03B8u03BCόu03C2 u0391u03BDu03B1u03C6u03BFu03C1άu03C2 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4ή',

T2.U_INTCODE,

T1.Quantity,

T1.Price,

T1.LineTotal,

sum(T2.BWeight1*T1.Quantity) AS 'u0392άu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCήu03C2'

--case when

--(Select T2.CARDCODE from OITM t2 where t2.ITEMCODE=(t9.ITEMCODE)) is not null then

--(Select t2.CARDCODE from oITM t2 where t2.ITEMCODE=(t9.ITEMCODE)) else 0 end as '@@'

from oinv t0

full outer join inv1 t1 on T1.DocEntry = T0.DocEntry

full outer JOIN OITM T2 on T2.ItemCode = T1.ItemCode

inner JOIN ITM1 T9 on T9.ItemCode = T1.ItemCode

full outer JOIN OCRD T3 on T2.CardCode = T3.CardCode

INNER JOIN CRD1 T4 ON T0.CardCode = T4.CardCode

inner join ocry t8 on t8.code=t4.country

full outer join pch1 s1 on t1.itemcode = s1.itemcode

full outer join opch s2 on s1.docentry=s2.docentry

WHERE T0.DocNum = '1403408'

GROUP BY

T3.CardName,

T1.ItemCode,

T1.Quantity, T1.Price, T1.LineTotal,T0.DOCNUM,T0.DOCDATE, t1.linenum,T0.NUMATCARD, T8.Name,T2.U_INTCODE