cancel
Showing results for 
Search instead for 
Did you mean: 

Query Check

former_member209725
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member206488
Active Contributor
0 Kudos

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

former_member209725
Participant
0 Kudos

Hi Mam..

I really thankful & appreciating help from your side.

Thanks all for help.

I tried all codes given here but not found my desired data then I decided to do this in Excel by using some formulas to achieve target .

Thanks once again.

Regards,

Ravi

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Ravi,

Try this one....

SELECT T0.[Father] as 'Assembly',

[%1] as 'BuildQty',

T0.[code] as 'Component1', t10.[ItemName] 'Description1', T0.[Quantity] as 'Quantity1',

[%1] * t0.[Quantity] as 'ExtQty1',

t10.OnHand as 'OnHand1',

case when t10.OnHand - ([%1] * t0.[Quantity]) > 0 then 0 else

-(t10.OnHand - ([%1] * t0.[Quantity])) end as 'Shortage1',

T1.[Code] as 'Component2', t11.[ItemName] 'Description2', T1.[Quantity] as 'Quantity2',

[%1] * t0.[Quantity] * t1.[Quantity] as 'ExtQty2',

t11.OnHand as 'OnHand2',

case when t11.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity]) > 0 then 0 else

-(t11.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity])) end as 'Shortage2',

T2.[Code] as 'Component3', t12.[ItemName] 'Description3', T2.[Quantity] as 'Quantity3',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] as 'ExtQty3',

t12.OnHand as 'OnHand3',

case when t12.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity]) > 0 then 0 else

-(t12.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity])) end as 'Shortage3',

T3.[Code] as 'Component4', t13.[ItemName] 'Description4', T3.[Quantity] as 'Quantity4',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] as 'ExtQty4',

t13.OnHand as 'OnHand4',

case when t13.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity]) > 0 then 0 else

-(t13.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity])) end as 'Shortage4',

T4.[Code] as 'Component5', t14.[ItemName] 'Description5', T4.[Quantity] as 'Quantity5',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] as 'ExtQty5',

t14.OnHand as 'OnHand5',

case when t14.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity]) > 0 then 0 else

-(t14.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity])) end as 'Shortage5',

T5.[Code] as 'Component6', t15.[ItemName] 'Description6', T5.[Quantity] as 'Quantity6',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] * t5.[Quantity] as 'ExtQty6',

t15.OnHand as 'OnHand6',

case when t15.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] * t5.[Quantity]) > 0 then 0 else

-(t15.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] * t5.[Quantity])) end as 'Shortage6' FROM ITT1 T0 LEFT OUTER JOIN ITT1 T1 on T0.Code = T1.Father

LEFT OUTER JOIN ITT1 T2 on T1.Code = T2.Father

LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father

LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father

LEFT OUTER JOIN ITT1 T5 on T4.Code = T5.Father

LEFT OUTER JOIN ITT1 T6 on T5.Code = T6.Father

left outer join oitm t20 on t0.father = t20.itemcode

left outer join oitm t10 on t0.code = t10.itemcode

left outer join oitm t11 on t1.code = t11.itemcode

left outer join oitm t12 on t2.code = t12.itemcode

left outer join oitm t13 on t3.code = t13.itemcode

left outer join oitm t14 on t4.code = t14.itemcode

left outer join oitm t15 on t5.code = t15.itemcode WHERE T0.[Father] = [%0]

regards,

Vignesh

Former Member
0 Kudos

Hi Ravi,

Check this link.

Thanks,

Srujal Patel