Skip to Content
0

Query for Inventory with its item properties with item cost

Jun 30, 2017 at 03:45 AM

90

avatar image
Former Member

Hi Guys how can I build a Query that can generate inventory with item properties and cost like this

ItemCode | ItemName | On Stock | Properties | ItemCost

thank you very much looking forward to your help thanks

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Balaji Selvaraj Jun 30, 2017 at 07:16 AM
0

Hi,

Try SAP standard Inventory Audit report.

Regards:

Balaji.S

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

yes that works but it need a query that looks like that so i can make it in the crystal report hmmm

0

Hi,

Below query will result for individual warehouse wise for getting item cost. If same item have more than properites it will repeat

You can enter remaining properties with Union All

SELECT Distinct  T0.[ItemCode], T0.[ItemName],T2.[OnHand], T2.[AvgPrice], T1.ItmsGrpNam FROM OITM T0 
CROSS JOIN OITG T1   INNER JOIN OITW T2 ON T0.[ItemCode] = T2.[ItemCode] WHERE T0.QryGroup1 = 'Y' and
T1.ItmsTypCod = 1
UNION all
SELECT Distinct  T0.[ItemCode], T0.[ItemName],T2.[OnHand], T2.[AvgPrice], T1.ItmsGrpNam FROM OITM T0 
CROSS JOIN OITG T1   INNER JOIN OITW T2 ON T0.[ItemCode] = T2.[ItemCode] WHERE T0.QryGroup2 = 'Y' and
T1.ItmsTypCod = 2
UNION all
SELECT Distinct  T0.[ItemCode], T0.[ItemName],T2.[OnHand], T2.[AvgPrice], T1.ItmsGrpNam FROM OITM T0 
CROSS JOIN OITG T1   INNER JOIN OITW T2 ON T0.[ItemCode] = T2.[ItemCode] WHERE T0.QryGroup2 = 'Y' and
T1.ItmsTypCod = 3

0
avatar image
Former Member Jun 30, 2017 at 03:03 PM
0

Hi Socrates,

Is this query what you were looking for?

Properties will have to be named individually. properties table does not link back to OITM table unfortunately.

e.g.

T0."QryGroup1 as 'example1', T0."QryGroup2 as 'example2'

SELECT T0."ItemCode", T0."ItemName", T0."QryGroup1", T0."QryGroup2", T0."QryGroup3", T0."QryGroup4", T0."QryGroup5", T0."QryGroup6", T0."QryGroup7", T0."QryGroup8", T0."QryGroup9", T0."QryGroup10", T0."QryGroup11", T0."QryGroup12", T0."QryGroup13", T0."QryGroup14", T0."QryGroup15", T0."QryGroup16", T0."QryGroup17", T0."QryGroup18", T0."QryGroup19", T0."QryGroup20", T0."QryGroup21", T0."QryGroup22", T0."QryGroup23", T0."QryGroup24", T0."QryGroup25", T0."QryGroup26", T0."QryGroup27", T0."QryGroup28", T0."QryGroup29", T0."QryGroup30", T0."QryGroup31", T0."QryGroup32", T0."QryGroup33", T0."QryGroup34", T0."QryGroup35", T0."QryGroup36", T0."QryGroup37", T0."QryGroup38", T0."QryGroup39", T0."QryGroup40", T0."QryGroup41", T0."QryGroup42", T0."QryGroup43", T0."QryGroup44", T0."QryGroup45", T0."QryGroup46", T0."QryGroup47", T0."QryGroup48", T0."QryGroup49", T0."QryGroup50", T0."QryGroup51", T0."QryGroup52", T0."QryGroup53", T0."QryGroup54", T0."QryGroup55", T0."QryGroup56", T0."QryGroup57", T0."QryGroup58", T0."QryGroup59", T0."QryGroup60", T0."QryGroup61", T0."QryGroup62", T0."QryGroup63", T0."QryGroup64", T0."AvgPrice" FROM OITM T0

I hope this helps.

Kind regards

-Luke

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

hi sorry but this is not it.

0