Query

[Former Member]

Hai

i want to add CRD 7 .[TaxId0] below this query how do i will add? below query some place PAN number not appearing ?

SELECT DISTINCT T0.[CardName], T2.[TaxId0] as "PAN No.",T1.[WTCode], T3.[WTName], T3.[OffclCode] as Section, T1.[TdsBAmt] as BASIC, T1.[Rate], T1.[TdsAmnt] as Tax , T0.[DocDate] as Date FROM OPCH T0 INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry INNER JOIN PCH12 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN OWHT T3 ON T1.WTCode = T3.WTCode INNER JOIN PCH1 T4 ON T0.DocEntry = T4.DocEntry WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] AND T4.[TargetType] <>'19' order by T3.[OffclCode],T0.[CardName]

[Former Member]

Hi,

Try to search existing solution first: http://forums.sdn.sap.com/search.jspa?threadID=&q=CRD7&objID=c44&dateRange=thisyear&numResults=15&ra...

Thanks,

Gordon

[Former Member]

i want Pan no from BP but above this i gave the pan no from AP invoice Some Pan No is missing So i will bring from CRD7 table How do i will create??

[Former Member]

Try:

SELECT DISTINCT T0.CardName, IsNull(T2.TaxId0,T5.TaxId0) as "PAN No.",T1.WTCode, T3.WTName, T3.OffclCode as Section, T1.TdsBAmt as BASIC, T1.Rate, T1.TdsAmnt as Tax , T0.DocDate as Date

FROM OPCH T0

INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry

INNER JOIN PCH12 T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN OWHT T3 ON T1.WTCode = T3.WTCode

INNER JOIN PCH1 T4 ON T0.DocEntry = T4.DocEntry

LEFT JOHN CRD7 T5 ON T5.CardCode=T0.CardCode

WHERE T0.DocDate >=[%0\] and T0.DocDate <=[%1\] AND T4.TargetType !='19'

order by T3.OffclCode,T0.CardName

[vasileiosfasolis]

Hi rakan

have yout tried the isnull() function?

[jitin_chawla]

Hi,

Check this query :

SELECT DISTINCT T0.CardName, T2.TaxId0 as "PAN No.", t6.TaxId0 as 'PAN Number',
T1.WTCode, T3.WTName, T3.OffclCode as Section, T1.TdsBAmt as BASIC, 
T1.Rate, T1.TdsAmnt as Tax , T0.DocDate as Date FROM OPCH T0
INNER JOIN CRD7 T6 on t6.CardCode = t0.CardCode 
INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry 
INNER JOIN PCH12 T2 ON T0.DocEntry = T2.DocEntry 
INNER JOIN OWHT T3 ON T1.WTCode = T3.WTCode 
INNER JOIN PCH1 T4 ON T0.DocEntry = T4.DocEntry 
WHERE T0.DocDate >= '2011.01.01' and T0.DocDate <= '2011.12.31' AND T4.TargetType !='19' 
order by T3.OffclCode,T0.CardName 
 

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hai

Below this query i need CRD7 Pan number Sub Query , How do i willl create

SELECT DISTINCT T0.[CardName], T2.[TaxId0] as "PAN No.",T1.[WTCode], T3.[WTName], T3.[OffclCode] as Section, T1.[TdsBAmt] as BASIC, T1.[Rate], T1.[TdsAmnt] as Tax , T0.[DocDate] as Date FROM OPCH T0 INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry INNER JOIN PCH12 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN OWHT T3 ON T1.WTCode = T3.WTCode INNER JOIN PCH1 T4 ON T0.DocEntry = T4.DocEntry WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] AND T4.[TargetType] <>'19' order by T3.[OffclCode],T0.[CardName]