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