on 10-29-2010 6:24 AM
I have REport in this report i want one more column that is in stock item for corresponding warehouse that is sale order for item
SELECT T0.[DocNum] as 'SO No.', T1.[Project], T6.[PrjName] as 'Project Name' , T1.[CogsOcrCo2] as 'Branch',T1.[WhsCode] as 'Warehouse', T1.[Dscription],T1.[LineStatus] as 'Status',T1.[Quantity] as 'Qty.', T1.[OpenQty] as 'Bal Qty.',T0.[DocCur] as 'Sale Curr.', T1.[Price], T2.DocNum as 'PO No.', T2.[TaxDate] as 'PO Date',T3.[Dscription], T3.[Quantity] as 'Qty.',T2.[DocCur] as 'Pur. Curr.', T3.[PriceBefDi], T2.[U_OrderAkNo] as 'OA No.',T3.[ShipDate] as 'CDD', T0.[U_OrderStatus],T0.[U_OrderStatusas] FROM dbo.ORDR T0
INNER JOIN dbo.RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OPRJ T6 ON T1.Project = T6.PrjCode
LEFT JOIN dbo.POR1 T3 ON T1.Docentry = T3.Baseentry and T1.LineNum=T3.Baseline
LEFT JOIN dbo.OPOR T2 ON T3.Docentry = T2.Docentry
LEFT JOIN dbo.PDN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T3.Linenum
LEFT JOIN dbo.OPDN T5 ON T5.DocEntry = T4.DocEntry
WHERE T1.[OcrCode] >=[%0] AND T1.[OCRCODE ]<=[%1] and T0.[DocStatus] = 'O'
Hi Try This...........
SELECT T0.DocNum as 'SO No.', T1.Project, T6.PrjName as 'Project Name' , T1.CogsOcrCo2 as 'Branch',
T1.WhsCode as 'Warehouse', T1.Dscription,T1.LineStatus as 'Status',T1.Quantity as 'Qty.',
T1.OpenQty as 'Bal Qty.',T0.DocCur as 'Sale Curr.', T1.Price, T2.DocNum as 'PO No.', T2.TaxDate as 'PO Date',
T3.Dscription, T3.Quantity as 'Qty.',T2.DocCur as 'Pur. Curr.', T3.PriceBefDi, T2.U_OrderAkNo as 'OA No.',
T3.ShipDate as 'CDD', T0.U_OrderStatus,T0.U_OrderStatusas, T6.OnHand FROM dbo.ORDR T0
INNER JOIN dbo.RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OPRJ T6 ON T1.Project = T6.PrjCode
LEFT JOIN dbo.POR1 T3 ON T1.Docentry = T3.Baseentry and T1.LineNum=T3.Baseline
LEFT JOIN dbo.OPOR T2 ON T3.Docentry = T2.Docentry
LEFT JOIN dbo.PDN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T3.Linenum
LEFT JOIN dbo.OPDN T5 ON T5.DocEntry = T4.DocEntry Inner Join OITW T6 On T1.WhsCode=T6.WhsCode and T1.ItemCode=T6.ItemCode
WHERE T1.OcrCode >=%0 AND T1.OCRCODE <=%1 and T0.DocStatus = 'O'
Hope this will help you
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this.....
SELECT T0.DocNum as 'SO No.', T1.Project, T6.PrjName as 'Project Name' , T1.CogsOcrCo2 as 'Branch',
T1.WhsCode as 'Warehouse', T1.Dscription,T1.LineStatus as 'Status',T1.Quantity as 'Qty.',
T1.OpenQty as 'Bal Qty.',T0.DocCur as 'Sale Curr.', T1.Price, T2.DocNum as 'PO No.', T2.TaxDate as 'PO Date',
T3.Dscription, T3.Quantity as 'Qty.',T2.DocCur as 'Pur. Curr.', T3.PriceBefDi, T2.U_OrderAkNo as 'OA No.',
T3.ShipDate as 'CDD', T0.U_OrderStatus,T0.U_OrderStatusas, T9.OnHand FROM dbo.ORDR T0
INNER JOIN dbo.RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OPRJ T6 ON T1.Project = T6.PrjCode
LEFT JOIN dbo.POR1 T3 ON T1.Docentry = T3.Baseentry and T1.LineNum=T3.Baseline
LEFT JOIN dbo.OPOR T2 ON T3.Docentry = T2.Docentry
LEFT JOIN dbo.PDN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T3.Linenum
LEFT JOIN dbo.OPDN T5 ON T5.DocEntry = T4.DocEntry Inner Join OITW T9 On T1.WhsCode=T9.WhsCode and T1.ItemCode=T9.ItemCode
WHERE T1.OcrCode >=%0 AND T1.OCRCODE <=%1 and T0.DocStatus = 'O'
Regards,
Rahul
User | Count |
---|---|
100 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.