Query for Price List With Item Properties

[Former Member]

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

[former_member312729 · Accepted solution]

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

[Former Member · Accepted solution]

Thank This really works

[Former Member · Accepted solution]

but how can I sort it with specific price list only?

[former_member312729 · Accepted solution]

Use Order By T2.[PriceList] in the query.

If you double click on price list column it will get sorted

[Former Member]

Thanks for this

[Johan_H]

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