required itemcode in query

[vinayak_chavan]

Declare @FromDate Datetime 
Declare @ToDate Datetime 
select @FromDate =min(S0.Docdate) from dbo.OINV S0 where S0.Docdate >= '[%0]' 
select @ToDate =max(S1.Docdate) from dbo.OINV s1 where S1.Docdate <='[%1]'   


SELECT 
'ARINV' [Type],
T0.DocEntry,
T1.SeriesName,T0.DocNum,T0.DocDate,ocr.CardName,crg.GroupName,
(select sum(Quantity)  from inv1 where docentry=t0.DocEntry) as 'Quantity',
(select sum(linetotal)  from inv1 where docentry=t0.DocEntry) as 'AssesebleValue',




(select sum(TotalSumSy) from INV3 where ExpnsCode=2 and docentry=t0.docentry ) [Freight (D)],
(select sum(TotalSumSy) from INV3 where ExpnsCode=3 and docentry=t0.docentry) [Freight (E)],
(select sum(TotalSumSy) from INV3 where ExpnsCode=5 and docentry=t0.docentry) [Packing chrs (E)],
(select sum(TotalSumSy) from INV3 where ExpnsCode=7 and docentry=t0.docentry) [Packing Chrs (D)],
(select sum(TotalSumSy) from INV3 where ExpnsCode<>'-1' and docentry=t0.docentry) [Total_Freight],




(isnull((SELECT SUM((case when t4.staType='-120'  then (T4.TaxSum) else 0 end)) FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry and t4.RelateType<>11),0)) "IGST Amount", 
(isnull((SELECT SUM((case when t4.staType='-100'  then (T4.TaxSum) else 0 end)) FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry and t4.RelateType<>11),0)) "CGST Amount", 
(isnull((SELECT SUM((case when t4.staType='-110'  then (T4.TaxSum) else 0 end)) FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry and t4.RelateType<>11),0)) "SGST Amount", 


case when DpmAmnt<>0 then DpmAmnt+DpmVat+t0.DocTotal else DocTotal end [Invoice Total],
T0.U_GST_SALES_TYPE, scr.GSTRegnNo [GSTIN],
'' AS 'EXTRA FILEDS',


t0.docrate [ROE],t0.DocCur [Currency],
case when DpmAmntFC<>0 then DpmAmntFC+DpmVatFc else DocTotalFC end [Invoice Total FC],
t0.DiscSum,t0.RoundDif ,
(select sum(taxsum) from INV4 where ExpnsCode<>'-1' and docentry=t0.docentry) [Freight],
t2.baseref [Delivery No],concat(T1.Remark,T0.docnum) as 'Invoice No' ,
t0.CardCode,T0.TaxDate as 'Bill Date', T_OCHP.chapterid,
(select location from olct where code=t2.LocCode)[Location],
scr.Country [Country],


(select SUM(TAXSUM) from INV4 where ExpnsCode=2 and docentry=t0.docentry) [TAX Freight (D)],
(select sum(TAXSUM) from INV4 where ExpnsCode=3 and docentry=t0.docentry) [TAX Freight (E)],
(select sum(TAXSUM) from INV4 where ExpnsCode=5 and docentry=t0.docentry) [TAX Packing chrs (E)],
(select sum(TAXSUM) from INV4 where ExpnsCode=7 and docentry=t0.docentry) [TAX Packing Chrs (D)],
t2.vatsum as 'Taxable Value'




 FROM OINV T0 inner join INV1 t2 on t0.docentry=t2.docentry  and VisOrder=0
 Left Outer join nnm1 T1 on T1.series=T0.series 
  LEFT JOIN OCRD ocr ON ocr.CardCode=T0.CardCode
       INNER JOIN OCRG crg ON ocr.[GroupCode] = crg.[GroupCode]
  LEFT JOIN crd1 AS bcr on bcr.CardCode =ocr.cardcode and bcr.AdresType='B' --and bcr.Address=t0.paytocode
  LEFT JOIN crd1 AS scr on scr.CardCode =ocr.cardcode and scr.AdresType='S' --and scr.Address=t0.shiptocode
   
LEFT OUTER JOIN OSAC T_OSAC on (t2.SacEntry = T_OSAC.AbsEntry)
LEFT OUTER JOIN OITM T_OITM on (T_OITM.ItemCode = t2.ItemCode) --AND T_ODOC."DocType" = 'I')
LEFT OUTER JOIN OCHP T_OCHP on (T_OCHP.AbsEntry = T_OITM.ChapterID)
  WHERE  ( t0.docdate >= @FromDate and t0.docdate <= @ToDate )
and t0.CANCELED<>'Y' and t0.CANCELED<>'C'






union all 


SELECT 
'ARCN' [Type],
T0.DocEntry,
T1.SeriesName,T0.DocNum,T0.DocDate,ocr.CardName,crg.GroupName,
(select sum(Quantity)  from inv1 where docentry=t0.DocEntry) as 'Quantity',
(select sum(linetotal)  from inv1 where docentry=t0.DocEntry) as 'AssesebleValue',




(select sum(TotalSumSy) from INV3 where ExpnsCode=2 and docentry=t0.docentry ) [Freight (D)],
(select sum(TotalSumSy) from INV3 where ExpnsCode=3 and docentry=t0.docentry) [Freight (E)],
(select sum(TotalSumSy) from INV3 where ExpnsCode=5 and docentry=t0.docentry) [Packing chrs (E)],
(select sum(TotalSumSy) from INV3 where ExpnsCode=7 and docentry=t0.docentry) [Packing Chrs (D)],
(select sum(TotalSumSy) from INV3 where ExpnsCode<>'-1' and docentry=t0.docentry) [Total_Freight],




(isnull((SELECT SUM((case when t4.staType='-120'  then (T4.TaxSum) else 0 end)) FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry and t4.RelateType<>11),0)) "IGST Amount", 
(isnull((SELECT SUM((case when t4.staType='-100'  then (T4.TaxSum) else 0 end)) FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry and t4.RelateType<>11),0)) "CGST Amount", 
(isnull((SELECT SUM((case when t4.staType='-110'  then (T4.TaxSum) else 0 end)) FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry and t4.RelateType<>11),0)) "SGST Amount", 


case when DpmAmnt<>0 then DpmAmnt+DpmVat+t0.DocTotal else DocTotal end [Invoice Total],
T0.U_GST_SALES_TYPE, scr.GSTRegnNo [GSTIN],
'' AS 'EXTRA FILEDS',


t0.docrate [ROE],t0.DocCur [Currency],
case when DpmAmntFC<>0 then DpmAmntFC+DpmVatFc else DocTotalFC end [Invoice Total FC],
t0.DiscSum,t0.RoundDif ,
(select sum(taxsum) from INV4 where ExpnsCode<>'-1' and docentry=t0.docentry) [Freight],
t2.baseref [Delivery No],concat(T1.Remark,T0.docnum) as 'Invoice No' ,
t0.CardCode,T0.TaxDate as 'Bill Date', T_OCHP.chapterid,
(select location from olct where code=t2.LocCode)[Location],
scr.Country [Country],


(select SUM(TAXSUM) from INV4 where ExpnsCode=2 and docentry=t0.docentry) [TAX Freight (D)],
(select sum(TAXSUM) from INV4 where ExpnsCode=3 and docentry=t0.docentry) [TAX Freight (E)],
(select sum(TAXSUM) from INV4 where ExpnsCode=5 and docentry=t0.docentry) [TAX Packing chrs (E)],
(select sum(TAXSUM) from INV4 where ExpnsCode=7 and docentry=t0.docentry) [TAX Packing Chrs (D)],
t2.vatsum as 'Taxable Value'








 FROM ORIN T0 inner join RIN1 t2 on t0.docentry=t2.docentry  and VisOrder=0
 Left Outer join nnm1 T1 on T1.series=T0.series 
  LEFT JOIN OCRD ocr ON ocr.CardCode=T0.CardCode
       INNER JOIN OCRG crg ON ocr.[GroupCode] = crg.[GroupCode]
  LEFT JOIN crd1 AS bcr on bcr.CardCode =ocr.cardcode and bcr.AdresType='B' --and bcr.Address=t0.paytocode
  LEFT JOIN crd1 AS scr on scr.CardCode =ocr.cardcode and scr.AdresType='S' --and scr.Address=t0.shiptocode


LEFT OUTER JOIN OSAC T_OSAC on (t2.SacEntry = T_OSAC.AbsEntry)
LEFT OUTER JOIN OITM T_OITM on (T_OITM.ItemCode = t2.ItemCode) --AND T_ODOC."DocType" = 'I')
LEFT OUTER JOIN OCHP T_OCHP on (T_OCHP.AbsEntry = T_OITM.ChapterID)  
  WHERE  ( t0.docdate >= @FromDate and t0.docdate <= @ToDate ) 
  and t0.CANCELED<>'Y' and t0.CANCELED<>'C'
 GROUP BY
 T0.DocEntry, T1.SeriesName,T0.DocNum,T0.DocDate,ocr.CardName,
T0.U_GST_SALES_TYPE,T_OCHP.chapterid,
t2.vatsum ,
 
 T0.cardcode,T0.TaxDate,T0.address,T0.Doctotal, crg.GroupName,numatcard,T0.DocDate,ocr.CardName ,T0.DocNum ,T1.Remark,t2.dscription,t0.docrate,t0.doccur,DpmAmnt,DpmAmntFC,
 t2.LocCode,t0.Series ,t2.baseref,T0.DocTotalFC,t0.DiscSum,t0.RoundDif,DpmVatFc,DpmVat,t0.shiptocode,scr.GSTRegnNo,scr.country,t0.docnum
order by TYPE

[kothandaraman_nagarajan]

Hi,

Any error message after running your query with T2.Itemcode? I am not receiving any error message and working.

Regards,

Nagarajan

[vinayak_chavan]

Hi Nagrajan,
There is no error but item code coming double for single docnum.

i have used distinct also for docnum.

[azizelmir]

Hi Chavan,

Add t2.Itemcode,

after "T1.SeriesName,T0.DocNum,T0.DocDate,ocr.CardName,crg.GroupName,"

on both select .....

Thank you,

Aziz

[vinayak_chavan]

not working . i have already tried it