need OIGN DOCDATE

[vinayak_chavan]

SELECT distinct T0.DocNum as 'SaleOrder',T2.DocNum  as 'jobcard',T2.PostDate,  T1.ItemCode,T1.Quantity,T1.OpenQty,T2.CmpltQty, case when T1.OpenQty=0 then (T1.quantity-T2.cmpltqty) else (T2.CmpltQty-(T1.Quantity-T1.OpenQty)) end as 'BR Stock',T4.ChapterID,dbo.OHEM.firstName,

((T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty)))) as  Value,(T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty))*Rate)as 'ExportValue',((T1.Quantity-T1.OpenQty)*T1.Price) as 'Subtototal',T5.Docdate

FROM ORDR T0  INNER JOIN

RDR1 T1 ON T0.DocEntry = T1.DocEntry

left outer join OWOR T2 on T0.DocNum=T2.OriginNum and T1.Itemcode=T2.Itemcode and T1.U_jobNo=cast(T2.DocNum as nvarchar)

LEFT OUTER JOIN dbo.OHEM ON T0.OwnerCode = dbo.OHEM.empID LEFT OUTER JOIN NNM1 ON T0.Series=NNM1.Series

inner join OITM T3 on T1.ItemCode=T3.ItemCode

left outer join  OCHP T4 ON T3.ChapterID = T4.AbsEntry

Left outer join OIGE T5 On T0.Docentry = T5.Docentry

where  T0.CANCELED='N'and T1.Price<>0  and T2.DocNum<>'' and (T2.Status <> 'L') AND (T2.Status <> 'C') and (T2.Status<>'P')

and (T2.CmpltQty-(T1.Quantity-T1.OpenQty))<>0 and T2.CmpltQty<>0 and T4.ChapterID<>'' and T2.warehouse='BR'

[KennedyT21 · Accepted solution]

Try This

SELECT distinct T0.DocNum as 'SaleOrder',T2.DocNum  as 'jobcard',T2.PostDate,  T1.ItemCode,T1.Quantity,T1.OpenQty,T2.CmpltQty, case when T1.OpenQty=0 then (T1.quantity-T2.cmpltqty) else (T2.CmpltQty-(T1.Quantity-T1.OpenQty)) end as 'BR Stock',T4.ChapterID,dbo.OHEM.firstName,

((T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty)))) as  Value,(T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty))*t1.Rate)

as 'ExportValue',((T1.Quantity-T1.OpenQty)*T1.Price) as 'Subtototal',T5.Docdate, T7.DocDate

FROM ORDR T0  INNER JOIN

RDR1 T1 ON T0.DocEntry = T1.DocEntry

left outer join OWOR T2 on T0.DocNum=T2.OriginNum and T1.Itemcode=T2.Itemcode and T1.U_jobNo=cast(T2.DocNum as nvarchar)

LEFT OUTER JOIN dbo.OHEM ON T0.OwnerCode = dbo.OHEM.empID LEFT OUTER JOIN NNM1 ON T0.Series=NNM1.Series

inner join OITM T3 on T1.ItemCode=T3.ItemCode

left outer join  OCHP T4 ON T3.ChapterID = T4.AbsEntry

Left outer join OIGE T5 On T0.Docentry = T5.Docentry

Left outer join ign1 T6 On T6.BaseRef=t2.DocNum and t6.LineNum='0'

Inner join OIGN t7 on t7.docentry=t6.DocEntry

where  T0.CANCELED='N'and T1.Price<>0  and T2.DocNum<>'' and (T2.Status <> 'L') AND (T2.Status <> 'C') and (T2.Status<>'P')

and (T2.CmpltQty-(T1.Quantity-T1.OpenQty))<>0 and T2.CmpltQty<>0 and T4.ChapterID<>'' and T2.warehouse='BR'

Regards

Kennedy

[vinayak_chavan · Accepted solution]

jobcard coming double it must be single

[KennedyT21 · Accepted solution]

SELECT distinct T0.DocNum as 'SaleOrder',T2.DocNum  as 'jobcard',T2.PostDate,  T1.ItemCode,T1.Quantity,T1.OpenQty,T2.CmpltQty, case when T1.OpenQty=0 then (T1.quantity-T2.cmpltqty) else (T2.CmpltQty-(T1.Quantity-T1.OpenQty)) end as 'BR Stock',T4.ChapterID,dbo.OHEM.firstName,

((T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty)))) as  Value,(T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty))*t1.Rate)

as 'ExportValue',((T1.Quantity-T1.OpenQty)*T1.Price) as 'Subtototal',T5.Docdate, T7.DocDate

FROM ORDR T0  INNER JOIN

RDR1 T1 ON T0.DocEntry = T1.DocEntry

left outer join OWOR T2 on T0.DocNum=T2.OriginNum   and T1.U_jobNo=cast(T2.DocNum as nvarchar)

LEFT OUTER JOIN dbo.OHEM ON T0.OwnerCode = dbo.OHEM.empID

LEFT OUTER JOIN NNM1 ON T0.Series=NNM1.Series

inner join OITM T3 on T1.ItemCode=T3.ItemCode

left outer join  OCHP T4 ON T3.ChapterID = T4.AbsEntry

Left outer join OIGE T5 On T0.Docentry = T5.Docentry

Left outer join ign1 T6 On T6.BaseRef=t2.DocNum and t6.LineNum='0'

Inner join OIGN t7 on t7.docentry=t6.DocEntry

where  T0.CANCELED='N'and T1.Price<>0  and T2.DocNum<>'' and (T2.Status <> 'L') AND (T2.Status <> 'C') and (T2.Status<>'P')

and (T2.CmpltQty-(T1.Quantity-T1.OpenQty))<>0 and T2.CmpltQty<>0 and T4.ChapterID<>'' and T2.warehouse='BR'

[KennedyT21 · Accepted solution]

Vinayak,

I have a doubt if i am not wrong The production order to be based upon the sales order and if a single sales order has multiple production order then you might have double job card for the single sales order am i right, correct me if am wrong,

Regards

Kennedt

[Johan_H]

Hi Vinayak,

The OIGE table can not be joined to the ORDR table, because Orders and Goods Issues have nothing to do with each other.

What information are you trying to get from this query ?

Regards,

Johan

[KennedyT21]

HI Vinayak,

Try This

SELECT distinct T0.DocNum as 'SaleOrder',T2.DocNum  as 'jobcard',T2.PostDate,  T1.ItemCode,T1.Quantity,T1.OpenQty,T2.CmpltQty, case when T1.OpenQty=0 then (T1.quantity-T2.cmpltqty) else (T2.CmpltQty-(T1.Quantity-T1.OpenQty)) end as 'BR Stock',T4.ChapterID,dbo.OHEM.firstName,

((T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty)))) as  Value,(T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty))*Rate)

as 'ExportValue',((T1.Quantity-T1.OpenQty)*T1.Price) as 'Subtototal',T5.Docdate, T7.DocDate

FROM ORDR T0  INNER JOIN

RDR1 T1 ON T0.DocEntry = T1.DocEntry

left outer join OWOR T2 on T0.DocNum=T2.OriginNum and T1.Itemcode=T2.Itemcode and T1.U_jobNo=cast(T2.DocNum as nvarchar)

LEFT OUTER JOIN dbo.OHEM ON T0.OwnerCode = dbo.OHEM.empID LEFT OUTER JOIN NNM1 ON T0.Series=NNM1.Series

inner join OITM T3 on T1.ItemCode=T3.ItemCode

left outer join  OCHP T4 ON T3.ChapterID = T4.AbsEntry

Left outer join OIGE T5 On T0.Docentry = T5.Docentry

Left outer join ign1 T6 On T6.BaseRef=t2.DocNum

Inner join OIGN t7 on t7.docentry=t6.DocEntry

where  T0.CANCELED='N'and T1.Price<>0  and T2.DocNum<>'' and (T2.Status <> 'L') AND (T2.Status <> 'C') and (T2.Status<>'P')

and (T2.CmpltQty-(T1.Quantity-T1.OpenQty))<>0 and T2.CmpltQty<>0 and T4.ChapterID<>'' and T2.warehouse='BR'

Regards

Kennedy

[vinayak_chavan]

AMBIGIOUS COLUMN NAME RATE

[KennedyT21]

Try This

SELECT distinct T0.DocNum as 'SaleOrder',T2.DocNum  as 'jobcard',T2.PostDate,  T1.ItemCode,T1.Quantity,T1.OpenQty,T2.CmpltQty, case when T1.OpenQty=0 then (T1.quantity-T2.cmpltqty) else (T2.CmpltQty-(T1.Quantity-T1.OpenQty)) end as 'BR Stock',T4.ChapterID,dbo.OHEM.firstName,

((T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty)))) as  Value,(T1.Price*(T2.CmpltQty-(T1.Quantity-T1.OpenQty))*t1.Rate)

as 'ExportValue',((T1.Quantity-T1.OpenQty)*T1.Price) as 'Subtototal',T5.Docdate, T7.DocDate

FROM ORDR T0  INNER JOIN

RDR1 T1 ON T0.DocEntry = T1.DocEntry

left outer join OWOR T2 on T0.DocNum=T2.OriginNum and T1.Itemcode=T2.Itemcode and T1.U_jobNo=cast(T2.DocNum as nvarchar)

LEFT OUTER JOIN dbo.OHEM ON T0.OwnerCode = dbo.OHEM.empID LEFT OUTER JOIN NNM1 ON T0.Series=NNM1.Series

inner join OITM T3 on T1.ItemCode=T3.ItemCode

left outer join  OCHP T4 ON T3.ChapterID = T4.AbsEntry

Left outer join OIGE T5 On T0.Docentry = T5.Docentry

Left outer join ign1 T6 On T6.BaseRef=t2.DocNum

Inner join OIGN t7 on t7.docentry=t6.DocEntry

where  T0.CANCELED='N'and T1.Price<>0  and T2.DocNum<>'' and (T2.Status <> 'L') AND (T2.Status <> 'C') and (T2.Status<>'P')

and (T2.CmpltQty-(T1.Quantity-T1.OpenQty))<>0 and T2.CmpltQty<>0 and T4.ChapterID<>'' and T2.warehouse='BR'

Kennedy

[vinayak_chavan]

records coming double

[Former Member]

Hi,

Document Date is T0.[TaxDate]

Regards,

Sudhir