Skip to Content
avatar image
Former Member

Query for Price List With Item Properties

Jun 21, 2017 at 07:36 AM | 156 Views

Hi Guys,

Is It possible to create a query that can generate a price list with it's ITEM Properties?

Can you help me with this

Thanks,

Soc

SAP Business One
kennedy t | gurdon du | gurdon | nancy guo
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Balaji Selvaraj
    Jun 21, 2017 at 09:01 AM

    Hi Socrates,

    You can try standard report , " Price report " under Inventory Report. Where you can choose Price list based on your Item properties.

    Else

    Try this you need to add additional properties with Union all for remaining Item properties.It will result based on your number of price list

    select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup1 = 'Y' and T1.ItmsTypCod = 1

    Union all

    select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup2 = 'Y' and T1.ItmsTypCod = 2

    Union all

    select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup3 = 'Y' and T1.ItmsTypCod = 3

    Union all

    select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup4 = 'Y' and T1.ItmsTypCod = 4

    Regards:

    Balaji.S

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Johan Hakkesteegt
    Jun 21, 2017 at 08:12 AM

    Hi Soc,

    It is complicated, but possible. However, a price list contains one price per item, but one item can contain between zero and 64 properties.

    That means that the result of such a query would be very confusing to read, as some expected items may not show because they have no properties set, and others may be on the list multiple times, but varying per item.

    Also, the number of rows returned by such a query would potentially be between zero and 64 x [number of items with a price]. So if you have, let us say, 150 items you could end up with a list with 9600 rows. This assumes that you run the query for a single price list only.

    So I would recommend taking another look at the question / problem, and/or rethinking the reason you used item properties, and/or rethinking the query / report.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 24, 2017 at 02:00 AM

    Thanks for this

    Add comment
    10|10000 characters needed characters exceeded