How to write a Query for BOM report with raw material quantity for each parent code?

[former_member440545]

Hi to Everyone ......

Actually i got the answer. if anybody know the simplest query better than this query means.. kindly share it.

Bill Of Materials(BOM) Report for Sales ********************************************* ............... View Creation for Bom **********************

Create view Ea_vw_BomReport as

select distinct T0.[DocNum],T0.[NumAtCard], T0.[DocDate] , T1.ItemCode,T1.Dscription, T1.[Quantity],T4.Quantity as qty,T1.[Price],T0.[DocTotal], T0.[CardName] from OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join oitm t2 on t2.ItemCode=t1.ItemCode inner join oitt t3 on t3.code=t2.ItemCode inner join itt1 t4 on t4.father=t3.Code where t4.Warehouse in ('rm-ex','Qc-ex') and t4.IssueMthd='m' and t1.Price!=0 union select distinct T0.[DocNum],T0.[NumAtCard], T0.[DocDate] , T1.ItemCode,T1.Dscription, T1.[Quantity],T6.Quantity as qty,T1.[Price],T0.[DocTotal], T0.[CardName] from OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join oitm t2 on t2.ItemCode=t1.ItemCode inner join oitt t3 on t3.code=t2.ItemCode inner join itt1 t4 on t4.father=t3.Code inner join ITT1 t5 on t5.Father=t4.code inner join itt1 t6 on t6.Father=t5.code and t5.code in (select code from oitt ) and t6.Warehouse in ('rm-ex','Qc-ex') and t1.Price!=0 and t6.IssueMthd='m' union select T0.[DocNum],T0.[NumAtCard], T0.[DocDate] , T1.ItemCode,T1.Dscription, T1.[Quantity],T7.Quantity as qty,T1.[Price],T0.[DocTotal], T0.[CardName] from OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join oitm t2 on t2.ItemCode=t1.ItemCode inner join oitt t3 on t3.code=t2.ItemCode inner join itt1 t4 on t4.father=t3.Code inner join ITT1 t5 on t5.Father=t4.code inner join itt1 t6 on t6.Father=t5.code inner join itt1 t7 on t7.Father=t6.code and t7.Warehouse in ('rm-ex','Qc-ex') and t7.IssueMthd='m' and t1.price!=0 union select T0.[DocNum],T0.[NumAtCard], T0.[DocDate] , T1.ItemCode,T1.Dscription, T1.[Quantity],T8.Quantity as qty,T1.[Price],T0.[DocTotal], T0.[CardName] from OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join oitm t2 on t2.ItemCode=t1.ItemCode inner join oitt t3 on t3.code=t2.ItemCode inner join itt1 t4 on t4.father=t3.Code inner join ITT1 t5 on t5.Father=t4.code inner join itt1 t6 on t6.Father=t5.code inner join itt1 t7 on t7.Father=t6.code inner join itt1 t8 on t8.father =t7.code where t8.Warehouse in ('rm-ex','Qc-ex') and t1.Price!=0 and t8.IssueMthd='m' union select T0.[DocNum],T0.[NumAtCard], T0.[DocDate] , T1.ItemCode,T1.Dscription, T1.[Quantity],T9.Quantity as qty,T1.[Price],T0.[DocTotal], T0.[CardName] from OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join oitm t2 on t2.ItemCode=t1.ItemCode inner join oitt t3 on t3.code=t2.ItemCode inner join itt1 t4 on t4.father=t3.Code inner join ITT1 t5 on t5.Father=t4.code inner join itt1 t6 on t6.Father=t5.code inner join itt1 t7 on t7.Father=t6.code inner join itt1 t8 on t8.father =t7.code inner join itt1 t9 on t9.father=t8.code where t9.Warehouse in ('rm-ex','Qc-ex') and t1.Price!=0 and t9.IssueMthd='m'

............................................................................................................................................................. Final Query For Query Generator

*******************************

I need a report like this.Actually i got the answer. if anybody know the simplest query better than this means.. kindly share it.

Select t0.DocNum as 'Inv no',t0.NumAtCard as 'So number',t0.DocDate as 'Posting Date' ,t0.ItemCode as 'Item no.',t0.Dscription as 'Item name' ,t0.Quantity as 'Inv Qty',t0.qty as 'Planned norms',(t0.Quantity*t0.qty) as 'Rm Qty',t0.Price as 'Unit price', t0.DocTotal as 'Inv total',t0.CardName as 'Party' from Ea_vw_BomReport t0 where (T0.docdate >= @start and T0.docdate <= @end) order by t0.docnum for browse