Skip to Content
0
Former Member
Dec 13, 2022 at 06:38 AM

GSTin Number and Mobile Number field to add in existing purchase register query

144 Views

Hello Experts,

I have created a purchase register query for the list of all AP invoice where I need GST number and Mobile Number.

GST number data is fetching but sap takes field of Bill to where as I needed data from Ship To or Ship from.

I saw both field's name is same so here i am getting a bit confused.. Please Help me..

Query is below :

SELECT T0.Docnum 'Invoice No.',T0.DocEntry AS 'DocEntry', T2.SeriesName,

T0.U_NewDocDate 'New Document Date',

T0.TaxDate as 'DocumentDate',

T0.DocDate,

T0.cardcode as 'VendorCode',

T0.cardname as 'VendorName',

T9.E_Mail as 'Email Id',

T0.NumAtCard as 'BillNo',

T1.TaxId2 as 'VAT No.',

(T0.DocTotal+ISNULL(T8.WTAmnt,0)) as 'Amount Before WTAX',

ISNULL(T8.WTAmnt,0) as 'TDS',

T0.Doctotal as 'AP Total',

ISNULL(T6.Doctotal,0) as 'APCR Total',

ISNULL((ISNULL(T0.PaidToDate,0)-ISNULL(T6.DocTotal,0)),0) as 'Paid Amount',

((T0.Doctotal-T0.PaidToDate)) as 'Net Payable Amount',

T0.U_Pymnt_Date,

cast(T0.U_Pymnt_Remarks as nvarchar(max)) as 'Payment Remarks',

T10.GSTRegnNo, T10.GSTType, T10.U_GSTINB

FROM OPCH T0

LEFT OUTER JOIN PCH12 T1 ON T0.DocEntry = T1.DocEntry

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

LEFT OUTER JOIN PCH4 T3 ON T3.DocEntry=T0.DocEntry

LEFT OUTER JOIN RPC1 T5 ON T5.BaseEntry = T0.DocEntry

LEFT OUTER JOIN ORPC T6 ON T6.DocEntry = T5.DocEntry

LEFT OUTER JOIN OFPR T7 ON T0.FinncPriod = T7.AbsEntry

LEFT OUTER JOIN PCH5 T8 ON T8.AbsEntry = T0.DocEntry

LEFT OUTER JOIN OCRD T9 ON T0.CardCode = T9.CardCode

LEFT OUTER JOIN CRD1 T10 ON T9.CardCode = T10.CardCode AND T10.AdresType = 'B'

WHERE T0.TaxDate >= [%0] And T0.TaxDate<= [%1]

And T0.PIndicator<>'FY2013-14'

And T0.DocTotal<>T0.PaidToDate

and T0.DocStatus<>'C'

GROUP BY T0.Docnum ,T2.SeriesName,T0.TaxDate,

T0.cardcode,

T0.cardname,

T0.NumAtCard,

T1.TaxId2,

T0.DocTotal, T0.VatSum, T0.TotalExpns,T0.RoundDif, T6.DocTotal, T6.VatSum, T6.TotalExpns,T0.RoundDif,T7.Indicator,T0.PaidToDate,

T0.TotalExpns,T0.DocTotal, t0.DocEntry, T6.RoundDif, T6.DocEntry, T8.WTAmnt, T9.E_Mail, T0.DocDate, T0.U_Pymnt_Date,

cast(T0.U_Pymnt_Remarks as nvarchar(max)), T0.U_NewDocDate, T10.GSTRegnNo, T10.GSTType, T10.U_GSTINB

Regards

Malay