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
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
Use Order By T2.[PriceList] in the query.
If you double click on price list column it will get sorted
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
