Skip to Content

Problem in AR Invoice Batch report

Hi All,

I Have written following view, it contains AR Invoice and its batch data. I took around 200 hundred print out and Print Out is OK but one of Invoice contains 11 line data but batch Line in SAP B1 is 7 I wanna to Show 7 lines in report.

Please Reply me any feasible answer..

ALTER VIEW [dbo].[VEPL_VIEW_AR_TAX_INVOICE]

AS

Select OINV.Docentry, NM.SERIESNAME''NM.BeginStr''cast( OINV.docnum AS char(20)) AS DocNo, OINV.docnum,

NM.SERIESNAME' / 'cast( OINV.docnum AS char(20)) AS docnumber1,

NM.SERIESNAME, OINV.SeqCode , OINV.Serial , OINV.U_Delivery,

OINV.docdate, OINV.excrmvtime, OINV.Numatcard, OINV.ExcRefDate,

(select top 1 isnull(n1.seriesname' ' convert (varchar (10),n1.[Beginstr]),'') '-' convert (varchar (10),DOCNUM )

from ORDR t10 inner join RDR1 t12 on t10.docentry=t12.docentry Left OUTER JOIN NNM1 n1 ON t10.Series = n1.Series

where t12.trgetentry =(select top 1 t13.docentry from odln t13 inner join dln1 t14 on t13.docentry=t14.docentry

where t14.trgetentry=INV1.docentry)) as 'Sales Order',

(select top 1 name from OCRD K0 INNER JOIN OCPR K1 ON K0.CardCode = K1.CardCode where K0.CardCode = OINV.CardCode) Contact_person,

(select top 1 Cellolar from OCRD K0 INNER JOIN OCPR K1 ON K0.CardCode = K1.CardCode where K0.CardCode = OINV.CardCode) Mobile,

--n1.seriesname' ' T1.Baseref as 'challan Number',

INV1.Baseref as 'challan Number',

(select top 1 t13.taxdate from odln t13 inner join dln1 t14 on t13.docentry=t14.docentry

where t14.trgetentry=INV1.docentry) as 'challan Date',

--OINV.cardcode, OINV.cardname , insu.LineTotal AS 'Insu.Amt',

--Frght.LineTotal AS 'FrghtAmt',pknfrd.LineTotal AS 'PknfrdAmt', Oth.LineTotal OtherCharges, T0.RoundDif,

--OCRN.CurrCode,OCRN.CurrName AS Currencyname,OCRN.F100Name AS Hundredthname,

OINV.Doctotal, --oshp.TrnspName,

Left(OINV.DocTime,2) + ':' + Right(OINV.DocTime,2) AS TimeOfIssueOfInv, /INV2.LineTotal PackFwd,/

octg.pymntgroup,

OINV.ShipToCode, OINV.PayToCode, OINV.Address2, OINV.Address, OINV.[DocTotal]-OINV.[VatSum] 'BeforeL', OINV.[DocTotalFC]-OINV.[VatSumFC] 'BeforeF',

OINV.[DocTotalSy]-OINV.[VatSumSy] 'BeforeS', OINV.[DocTotal]'Document Total',OINV.[DocTotalFC],OINV.[DocTotalSy],

OINV.U_LRN, OINV.U_LRD , OINV.U_TRANS_1 , OINV.U_MOT , OINV.U_pn, OINV.U_FN, OINV.U_DESPATCHDT, OINV.CreateDate, OINV.U_VE ,

INV1.Itemcode, INV1.Dscription, INV1.Quantity, INV1.TaxCode, INV1.Price, Inv1.DiscPrcnt,inv1.LineTotal, inv1.PriceBefDi ,

INV1.[U_SR], INV1.[U_POQ], INV1.[U_EREM], INV1.[U_IREM],

INV1.[U_MT], INV1.[U_CC], INV1.[U_CN], INV1.[U_PB], INV1.[U_CD], INV1.[U_DC], INV1.[U_SQ], INV1.[U_AV], INV1.[U_MQ],

INV1.[U_OPA], INV1.[U_ED]'INV1 ED', INV1.[U_EC]'INV1 EC', INV1.[U_HS] 'INV1HS',

--INV1.[U_CVD], INV1.[U_SAV], INV1.[U_PV], INV1.[U_IT], INV1.[U_AD],

--INV1.[U_OUP], INV1.[U_NUP], INV1.[U_OD], INV1.[U_ND], INV1.[U_OPAD], INV1.[U_NPAD], INV1.[U_OT], INV1.[U_NT],

--INV1.[U_QT], INV1.[U_DUP], INV1.[U_DD], INV1.[U_DPAD], INV1.[U_UOM], INV1.[U_MD], INV1.[U_MUP], INV1.[U_BP],

--INV1.[U_PL], INV1.[U_MAD], INV1.[U_CUD], INV1.[U_SPA], INV1.[U_DS], INV1.[U_ITMGRP], INV1.[U_SONUM], INV1.[U_GINo],

--INV1.[U_OItem], INV1.[U_Yes_or_No], INV1.[U_DelPeriod], INV1.[U_NLength], INV1.[U_BQty], INV1.[U_TQty], INV1.[U_EXPDT],

INV1.[U_DiscValid], INV1.[U_LastDiscount],OINV.TrnspCode,

--OCPR.Name CONtactPersON,

CRD1.U_BECC, CRD1.[U_BVAT] , CRD1.[U_BCST] , CRD1.[U_BTIN] , CRD1.[U_BPAN] , CRD1.[U_BTAN] ,

CRD1.[U_CERegNo] , CRD1.[U_CERange] , CRD1.[U_CEDivis] , CRD1.[U_CEComRate] , CRD1.[U_CP] ,

CRD1.[U_FAX] , CRD1.[U_EMAIL] , CRD1.[U_Type] , CRD1.[U_DelType] ,

CRD1.[U_TRPRT] , CRD1.[U_OCTROI],

OIBT.[ExpDate], OIBT.[InDate], OIBT.[U_SC], OIBT.Quantity 'OIBTQuantity',

OIBT.[U_SID], OIBT.[U_WH] 'OibtWareH', OIBT.[U_MC], OIBT.[U_MN], OIBT.[U_MIN], OIBT.[U_MID], OIBT.[U_MIQ], OIBT.[U_RGN], OIBT.[U_UAV],

OIBT.[U_STAV], OIBT.[U_MTAV], OIBT.[U_ED], OIBT.[U_EC], OIBT.[U_HS], OIBT.[U_CVD], OIBT.[U_STED], OIBT.[U_SUED], OIBT.[U_STEC],

OIBT.[U_SUEC], OIBT.[U_STHS], OIBT.[U_SUHS], OIBT.[U_STCVD], OIBT.[U_SUCVD], OIBT.[U_MTED], OIBT.[U_MUED], OIBT.[U_MTEC],

OIBT.[U_MUEC], OIBT.[U_MTHS], OIBT.[U_MUHS], OIBT.[U_MTCVD], OIBT.[U_MUCVD], OIBT.[U_DPU], OIBT.[U_MWH], OIBT.[U_CHID],

OIBT.[U_RG_No], OIBT.[U_SOS], OIBT.[U_DPCode], OIBT.[U_SerialNo], OIBT.[U_Batch], OIBT.[U_GRQty], OIBT.[U_GRPOQty],

IBT1.BatchNum,OIBT.BatchNum AS 'Batch', OIBT.WhsCode, ibt1.Quantity as 'Batch Qty', OIBT.PrdDate,

OINV.CardCode,OINV.CardName, oinv.GroupNum,CRD1.[U_FACode] , INV1.U_ITMGRP

--, isnull((Case When (SELECT distinct a.BaseType FROM [dbo].[IBT1] a WHERE a.Direction = 0 and a.ItemCode = inv1.ItemCode

-- and a.BatchNum = OIBT.BatchNum and a.BaseType = 20 ) = 20 Then (SELECT SUM(a.[Quantity]) FROM [dbo].[IBT1] a

-- WHERE a.Direction = 0 and a.BaseType = 20 and a.ItemCode = inv1.ItemCode and a.BatchNum = OIBT.BatchNum

-- Group By a.BatchNum,a.ItemCode )

-- When (SELECT distinct a.BaseType FROM [dbo].[IBT1] a WHERE a.Direction = 0 and a.ItemCode = inv1.ItemCode

-- and a.BatchNum = OIBT.BatchNum and a.BaseType = 59 ) = 59 Then (SELECT SUM(a.[Quantity]) FROM [dbo].[IBT1] a

-- WHERE a.Direction = 0 and a.BaseType = 59 and a.ItemCode = inv1.ItemCode and a.BatchNum = OIBT.BatchNum

-- Group By a.BatchNum,a.ItemCode ) END ),0) as TotalQuantityReceived

from OINV

inner join INV1 on OINV.docentry=INV1.docentry

inner join NNM1 NM on OINV.series=NM.series

Left join OITM on INV1.itemcode=OITM.itemcode

inner join OCRD on OINV.cardcode=OCRD.cardcode

left Join CRD1 on CRD1.cardcode=OCRD.cardcode and crd1.LineNum=2

LEFT OUTER JOIN OCRN ON OINV.DOCCUR = OCRN.CURRCODE

left Outer Join INV12 ON OINV.DocEntry =INV12.DocEntry

-


left outer join (select * from crd7 where address<>'' and addrtype='B')BillTo on OINV.cardcode=BillTo.cardcode and OINV.PayToCode=BillTo.address

left outer join OLCT on OLCT.Code =INV1.LocCode

LEFT OUTER JOIN ODSC ON OCRD.HouseBank = ODSC.BankCode

-


Left Join ibt1 oibt onINV1.DocEntry=ibt1.BaseEntry andINV1.LineNum=ibt1.BaseLinNum

--imp LEFT OUTER JOIN (select * from IBT1 where BaseType = 15) IBT1 ON INV1.baseentry=IBT1.baseentry and INV1.itemcode=IBT1.itemcode and INV1.linenum=IBT1.baselinnum AND IBT1.WhsCode =INV1.WhsCode

LEFT OUTER JOIN (select * from IBT1 where BaseType = 15) IBT1 ON INV1.baseentry=IBT1.baseentry and INV1.itemcode=IBT1.itemcode and IBT1.WhsCode =INV1.WhsCode

--imp

LEFT OUTER JOIN dbo.OIBT ON OIBT.ItemCode = IBT1.ItemCode AND OIBT.BatchNum = IBT1.BatchNum AND OIBT.WhsCode = IBT1.WhsCode and OIBT.ItemCode=inv1.ItemCode

--LEFT OUTER JOIN dbo.OIBT ON OIBT.ItemCode = IBT1.ItemCode AND OIBT.BatchNum = IBT1.BatchNum

Left JOIN dbo.OWHS ON dbo.INV1.WhsCode = dbo.OWHS.WhsCode

Left Join OCTG on oinv.GroupNum=octg.GroupNum

--Left Outer Join (Select * from crd1 where crd1.address<>'' and crd1.adrestype='S') CRD1Details on CRD1.cardcode=OINV.cardcode and CRD1.address = OINV.paytocode

--where OINV.docnum=12137176

--Select * from [VEPL_VIEW_AR_TAX_INVOICE] Where DocNum=12151452

--Select * from [VEPL_VIEW_AR_TAX_INVOICE] Where DocNum=12137220

Thanks and Regards,

Babu Terve

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Feb 01, 2012 at 09:41 AM

    Hi Suraj, the above mentioned code is full of comment marks, please post the right code in right way using the code section as follows -

    Select * from ...

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.