on 06-15-2011 9:50 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.