Query to pull out Vendor details with AP invoice

[Former Member]

Hi all

I have made the following query to pull out the following data

House Bank Account, Customer/Vendor Name,Payment Method code,Default Account,Default Branch,Default bank Internal id,

Document NUmber,Customer/Vendor Ref No,Row Total,Item/Service description,Branch,Street

SELECT T0.[HousBnkAct], T1.[CardName], T0.[PymCode], T0.[DflAccount],T0.[DflBranch], T0.[BankCtlKey], T1.[DocNum],T1.[NumAtCard], T2.[LineTotal], T2.[Dscription], T3.[Branch], T3.[Street] FROM OCRD T0 INNER JOIN OPCH T1 ON T0.CardCode = T1.CardCode INNER JOIN PCH1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN DSC1 T3 ON T0.HousActKey = T3.AbsEntry WHERE T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1] AND T1.[DocStatus] = 'o' AND T0.[PymCode] = 'EFT'

But i have observed that i am not getting all the customers in this query where the invoice is open, it displays only few records.

Regards

Farheen

[Former Member · Accepted solution]

Hi Farheen,

Try:

SELECT DISTINCT T0.HousBnkAct, T0.CardName, T0.PymCode, T0.DflAccount,T0.DflBranch, T0.BankCtlKey, T1.DocNum,T1.NumAtCard, T2.LineTotal, T2.Dscription, T3.Branch, T3.Street

FROM OCRD T0 INNER JOIN OPCH T1 ON T0.CardCode = T1.CardCode AND T1.DocDate >=[%0\] AND T1.DocDate <=[%1\] AND T1.DocStatus = 'o'

INNER JOIN PCH1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN DSC1 T3 ON T0.HousActKey = T3.AbsEntry

Thanks,

Gordon

[former_member541807]

hi Manikonda,

try this

SELECT T1.[HousBnkAct], T1.[CardCode], T1.[CardName], T1.[PymCode], T1.[DflBranch], T1.[BankCtlKey], T2.[NumAtCard], T3.[LineTotal], T3.[Dscription], T0.[Branch], T0.[Street] 

FROM DSC1 T0  INNER JOIN OCRD T1 ON T0.AbsEntry = T1.HousActKey INNER JOIN OPCH T2 ON T1.CardCode = T2.CardCode INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry 

WHERE T1.DocDate >=%0 AND T1.DocDate <=%1 AND T1.DocStatus = 'O' AND T0.PymCode = 'EFT'

regards,

Fidel

[Former Member]

Hi Farheen......

Please try this........

SELECT T0.HousBnkAct, T1.CardName, T0.PymCode, T0.DflAccount,
T0.DflBranch, T0.BankCtlKey, T1.DocNum,T1.NumAtCard, T2.LineTotal, T2.Dscription, 
T3.Branch, T3.Street FROM OCRD T0 LEFT JOIN OPCH T1 ON T0.CardCode = T1.CardCode 
LEFT JOIN PCH1 T2 ON T1.DocEntry = T2.DocEntry LEFT JOIN DSC1 T3 ON 
T0.HousActKey = T3.AbsEntry WHERE T1.DocDate >='[%0]' AND T1.DocDate <='[%1]' AND T1.DocStatus = 'o' AND T0.PymCode = 'EFT'

Regards,

Rahul