Skip to Content
avatar image
Former Member

Query for Inventory with its item properties with item cost

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 30, 2017 at 07:16 AM

    Hi,

    Try SAP standard Inventory Audit report.

    Regards:

    Balaji.S

    Add comment
    10|10000 characters needed characters exceeded

    • 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
      
  • avatar image
    Former Member
    Jun 30, 2017 at 03:03 PM

    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

    Add comment
    10|10000 characters needed characters exceeded