Skip to Content
0
Former Member
Aug 04, 2016 at 04:56 AM

Include CRD1.Address & INV1.Tax code to the query?

185 Views

Hi , How to include CRD1.address (Bill To Address) & INV1.Taxcode to this crystal report code.

DECLARE @sVAT NVARCHAR(max) DECLARE @sCess NVARCHAR(max) DECLARE @sCST NVARCHAR(max) DECLARE @nDocentry INT SET @sVAT='1' SET @sCess='7' SET @sCST ='4' SELECT DocEntry ,DocDate,VehicleNo,Driver,NumAtCard ,Building,Block,Street,City,District,State,Country ,Series,DocNum ,BTinNo,BCstNo,BCeRegNo,BPanNo,BCeRange,BCeComRate,BCeDivision,BEccNo ,Type ,CardName,[Delivery Addr] ,ECCNo,CERange,CERegNo,CEDivis,CEComRate ,PAN,CST,STN ,[Deliver At] ,LineNum ,Dscription,HSNumber,Quantity,Rate,LineTotal,Discount,Vat [VAT],Cess [Cess],Total,GTotal ,TotalExpns ,MfgName,MFGAddress ,MFGBuilding,MFGBlock,MFGStreet,MFGDistrict,MFGCity ,MCERegNo,MCERange,MCEDivis,MCEComRate,MPAN,MCST,MSTN ,SupName ,SUPBuilding,SUPBlock,SUPStreet,SUPDistrict,SUPCity ,SCERegNo,SCERange,SCEDivis,SCEComRate,SPAN,SCST,SSTN , (select substring((select upper(name)+',' from OUBR where isnull(U_SeriesGrp,'')<>'' order by Code FOR XML PATH ('')),1,LEN((select upper(name)+',' from OUBR order by Code FOR XML PATH ('')))-1))[Branches] FROM ( SELECT /*OBTN.DistNumber*/ INV1.DocEntry ,OINV.DocDate,OINV.U_VehicleNo VehicleNo,OINV.U_Driver Driver,OINV.U_SupRefNo NumAtCard ,CAST(OLCT.Building AS VARCHAR(255))Building,OLCT.Block,OLCT.Street,OLCT.City,OLCT.County+' - '+OLCT.ZipCode[District],OCST.Name State,OCRY.Name Country , NNM1.SeriesName [Series], OINV.DocNum ,OLCT.TinNo [BTinNo],OLCT.CstNo [BCstNo],OLCT.CeRegNo [BCeRegNo],OLCT.PanNo [BPanNo],OLCT.CeRange [BCeRange] ,OLCT.CeComRate [BCeComRate],OLCT.CeDivision [BCeDivision],OLCT.EccNo [BEccNo] ,OBTN.U_InvType Type ,OINV.CardName,OINV.Address2[Delivery Addr] ,CE_CRD7.ECCNo,CE_CRD7.CERegNo,CE_CRD7.CERange,CE_CRD7.CEDivis,CE_CRD7.CEComRate ,CRD7.TaxId0[PAN],CRD7.TaxId1[CST],CRD7.TaxId11 [STN] ,OINV.U_Address [Deliver At] ,INV1.LineNum,INV1.ItemCode ,INV1.Dscription,OITM.SWW [HSNumber] ,INV1.Quantity,INV1.PriceBefDi Price,INV1.LineTotal,INV1.Price Rate,(INV1.PriceBefDi-INV1.Price)*INV1.Quantity Discount ,INV4.Vat ,INV4.Cess ,INV1.LineTotal+INV1.VatSum Total ,OINV.DocTotal GTotal ,OINV.TotalExpns ,OBTN.U_MfgName MfgName ,convert(nvarchar(250),MFG_CRD1.Building) MFGBuilding,MFG_CRD1.Address MFGAddress,MFG_CRD1.Block MFGBlock,MFG_CRD1.Street MFGStreet,MFG_CRD1.City MFGCity,MFG_CRD1.ZipCode[MFGDistrict] ,OBTN.U_MCERegNo MCERegNo,OBTN.U_MCERange MCERange,OBTN.U_MCEDivis MCEDivis,OBTN.U_MCEComRate MCEComRate ,OBTN.U_MPAN MPAN,OBTN.U_MCST MCST,OBTN.U_MSTN MSTN ,OBTN.U_SupName SupName ,convert(nvarchar(250),SUP_CRD1.Building) SUPBuilding,SUP_CRD1.Block SUPBlock,SUP_CRD1.Street SUPStreet,SUP_CRD1.City SUPCity,SUP_CRD1.ZipCode[SUPDistrict] ,OBTN.U_SCERegNo SCERegNo,OBTN.U_SCERange SCERange,OBTN.U_SCEDivis SCEDivis,OBTN.U_SCEComRate SCEComRate ,OBTN.U_SPAN SPAN,OBTN.U_SCST SCST,OBTN.U_SSTN SSTN FROM OINV INNER JOIN INV1 ON OINV.DocEntry=INV1.DocEntry INNER JOIN OITM ON INV1.ItemCode=OITM.ItemCode INNER JOIN ( select INV4.DocEntry,INV4.LineNum ,CASE WHEN INV4.staType IN (@sVAT,@sCST) THEN sum(INV4.TaxSum) ELSE 0 END Vat ,CASE WHEN INV4.staType=@sCess THEN sum(INV4.TaxSum) ELSE 0 END Cess from INV4 where INV4.DocEntry={?DocKey@} and INV4.RelateType=1 group by INV4.DocEntry,INV4.LineNum,INV4.staType )INV4 ON INV1.DocEntry=INV4.DocEntry AND INV1.LineNum=INV4.LineNum INNER JOIN OLCT ON INV1.LocCode=OLCT.Code INNER JOIN OCST ON OLCT.State=OCST.Code INNER JOIN OCRY ON OLCT.Country=OCRY.Code and OCST.Country=OCRY.Code INNER JOIN INV12 ON OINV.DocEntry=INV12.DocEntry INNER JOIN OITL ON INV1.BaseType=OITL.ApplyType AND INV1.BaseEntry=OITL.ApplyEntry AND INV1.BaseLine=OITL.ApplyLine INNER JOIN ITL1 ON OITL.LogEntry=ITL1.LogEntry INNER JOIN OBTN ON ITL1.MdAbsEntry=OBTN.AbsEntry and ITL1.SysNumber=OBTN.SysNumber and ITL1.ItemCode=OBTN.ItemCode LEFT JOIN OCRD MFG_OCRD ON MFG_OCRD.CardCode=OBTN.U_MfgCode LEFT JOIN CRD1 MFG_CRD1 ON MFG_OCRD.CardCode=MFG_CRD1.CardCode AND MFG_OCRD.BillToDef=MFG_CRD1.Address and MFG_CRD1.AdresType='B' LEFT JOIN OCRD SUP_OCRD ON SUP_OCRD.CardCode=OBTN.U_SupCode LEFT JOIN CRD1 SUP_CRD1 ON SUP_OCRD.CardCode=SUP_CRD1.CardCode AND SUP_OCRD.BillToDef=SUP_CRD1.Address and SUP_CRD1.AdresType='B' LEFT JOIN NNM1 ON OINV.Series=NNM1.Series LEFT JOIN CRD7 ON OINV.CardCode=CRD7.CardCode AND CRD7.Address='' AND CRD7.AddrType='S' --Tax Details LEFT JOIN CRD7 CE_CRD7 ON OINV.CardCode=CE_CRD7.CardCode AND OINV.ShipToCode=CE_CRD7.Address AND CE_CRD7.AddrType='S' -- Central Excise Details WHERE INV1.DocEntry={?DocKey@} )INVOICE GROUP BY DocEntry ,DocDate,VehicleNo,Driver,NumAtCard ,Building,Block,Street,City,District,State,Country ,Series,DocNum ,BTinNo,BCstNo,BCeRegNo,BPanNo,BCeRange,BCeComRate,BCeDivision,BEccNo ,Type ,CardName,[Delivery Addr] ,ECCNo,CERange,CERegNo,CEDivis,CEComRate ,PAN,CST,STN ,[Deliver At] ,LineNum ,Dscription,HSNumber,Quantity,Rate,LineTotal,Discount,Vat,Cess,Total,GTotal ,TotalExpns ,MfgName,MFGAddress ,MFGBuilding,MFGBlock,MFGStreet,MFGDistrict,MFGCity ,MCERegNo,MCERange,MCEDivis,MCEComRate,MPAN,MCST,MSTN ,SupName ,SUPBuilding,SUPBlock,SUPStreet,SUPDistrict,SUPCity ,SCERegNo,SCERange,SCEDivis,SCEComRate,SPAN,SCST,SSTN