Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Query Check

Dear Experts..

Here I create a query for Total Sales Report within date range with BOM details.

SELECT T0.[DocNum], T1.[ItemCode], T1.[Dscription],T1.[Quantity], T1.[Price], T2.[Code], T4.[ItemName], T2.[Quantity] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry, ITT1 T2 INNER JOIN OITT T3 ON T2.Father = T3.Code INNER JOIN OITM T4 ON T2.Code = T4.ItemCode WHERE T1.ITEMCODE = T2.FATHER AND T0.DOCDATE >=[%0] AND T0.DOCDATE <=[%1]

BOM level goes upto 2 to 3 level, but in this I acn able to get only first level report.

i.e. BOM Code = ABCD

Component = A,B,C,D

Sub Component = A1,B1,C1, D1

In above report I can only able to get first level BOM report instead I need full BOM report by sales within date range like Parent Code, Component, Sub Component.

Plz let me know needful changes in Report.

Thanks in Advance,

Regards,

Ravi

Former Member
Former Member replied

Hi Ravi,

try this if it helps:

declare @temp AS INTEGER
 
DECLARE @productionOrders TABLE (
                  RowID INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
				  InvoiceDocNum NVARCHAR(MAX),
                  GrandParentItemCode NVARCHAR(20),               
 
                  ParentItemCode NVARCHAR(20),
 
                  ParentItemName NVARCHAR(100),
 
                  ChildItemCode NVARCHAR(20),
 
                  ChildItemName NVARCHAR(MAX),
 
                  ChildLevel INTEGER,
 
                  InStock NUMERIC(38, 6),                       
 
                  TreeType NVARCHAR(1),
 
                   PlannedQty NUMERIC(38, 6)
)
/*SELECT FROM [dbo].[OITT] T0*/
declare  @oworItemCodeFr as Nvarchar(20)
/* WHERE */
set @oworItemCodeFr = /* T0.Code */ '[%0]'
 
/*SELECT FROM [dbo].[OITT] T1*/
declare  @oworItemCodeTo as nvarchar(20)
/* WHERE */
set @oworItemCodeTo = /* T0.Code */ '[%1]'
 
;WITH PARENT_OITT AS 
 
      (SELECT  t5.DocNum as InvoiceDocNum ,'' AS GrandParentItemCode, T1.Code  AS ParentItemCode, T4.ItemName AS ParentItemName,
 
                  T0.Code AS ChildItemCode,  T3.ItemName AS ChildItemName, 0 AS ChildLevel, 
 
                  T3.OnHand AS InStock,T3.TreeType, T1.Qauntity  AS PlannedQty
 
            FROM ITT1 T0
 
                  INNER JOIN OITT T1 ON T1.Code  = T0.Father 
 
                  INNER JOIN OITM T3 ON T3.ItemCode = T0.Code 
 
                  INNER JOIN OITM T4 ON T4.ItemCode = T1.Code 
                  
                  INNER JOIN INV1 T2 on T2.ItemCode =t0.Father 
                  
                  INNER JOIN OINV T5 on T5.DocEntry =T2.DocEntry 
 
            WHERE  T1.Code  BETWEEN @oworItemCodeFr AND @oworItemCodeTo
          
      UNION ALL
 
      SELECT  -1 as InvoiceDocNum, '' AS GrandParentItemCode, T0.ChildItemCode AS ParentItemCode, T5.ItemName AS ParentItemName,
 
                  T2.Code  AS ChildItemCode, T4.ItemName AS ChildItemName, T0.ChildLevel + 1 AS ChildLevel, 
 
                  T4.OnHand AS InStock,T4.TreeType, T2.Quantity AS PlannedQty
 
            FROM PARENT_OITT T0
 
                  INNER JOIN OITT T1 ON T1.Code  = T0.ChildItemCode
 
                  INNER JOIN ITT1 T2 ON T2.Father   = T1.Code 
 
                  INNER JOIN OITM T4 ON T4.ItemCode = T2.Code 
 
                  INNER JOIN OITM T5 ON T5.ItemCode = T1.Code 
)      
 
INSERT INTO @productionOrders SELECT InvoiceDocNum,GrandParentItemCode, ParentItemCode, ParentItemName, ChildItemCode, ChildItemName, MAX(ChildLevel) AS ChildLevel , 
 
            MAX(InStock) AS InStock,   MAX(TreeType) AS TreeType,MAX(PlannedQty) AS PlannedQty
 
      FROM PARENT_OITT T0
 
      GROUP BY T0.GrandParentItemCode, T0.ParentItemCode, T0.ParentItemName, T0.ChildItemCode, T0.ChildItemName ,T0.InvoiceDocNum
 
SELECT      InvoiceDocNum as [Invoice No.],
ParentItemCode AS [Parent Product No.],
 
            ParentItemName AS [Parent Product Description],
 
            ChildLevel AS [Child Level],
 
            ChildItemCode AS [Child Product No.],
 
            ChildItemName AS [Child Product Description],
 
            PlannedQty AS [Planned Qty]      
 
FROM @productionOrders ORDER BY RowID desc


Thanks,

Neetu

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question