Query for Payment means and G/L accounst it hits.

[former_member634100]

Hello Experts, Could you please help me build a query that shows all payment means and the G/L account it hits. I also would like to be able to select a date range. Please see picture to see how it should look like.

[msundararaja_perumal]

Do post the query, so community members could modify them instead of starting from scratch. If you don't wish to, then you could try by using date parameters in the query.

Thanks.

[former_member634100]

This is what I have since I am not sure where to pull and link outoing payments.

Select

OACT.Segment_0 as 'G/L Account',

OACT.AcctName as 'Account Name',

From ????

Where T0.DocDueDate >= [%0] and T0.DocDueDate <= [%1]

GROUP BY OACT.AcctName, OACT.Segment_0

[former_member634100]

Hello jhozz All lines come up as Accounts Payable.

[msundararaja_perumal]

Your requirement is conflicting from your previous input, unless you clearly state the scenario with sample screenshot and what is impact of the Journal Entry, I am afraid I don't see the light at the end of the tunnel.

Thanks.

[former_member634100]

msundararaja.perumal As I stated earlier, this how it should like like, but instead all show up as Account payable.

[msundararaja_perumal]

What I mean is how are your transactions posted, share those screenshots and the JE impact. To build the logic, you need to show what have you done.

Thanks.

[former_member634100]

msundararaja.perumal I am sorry for not explaining well. I attached a screenshot from A/P Invoice and its Outgoing payment Journal Entry. What I actually need here is the G/L account of the A/P that was paid. Just a reminder, One A/P invoice might have posting more than one G/L account.

[jhozz]

Hi krkz270, to show only journal entrys genetated by a outgoing payments try using this condition in the where:

T2."TransType" = '46'

Rgrds.
Jhosser.

[msundararaja_perumal]

Hello,

You may fine try out this and fine tune if required

Select * From (
Select DocNum,CardCode,CardName,
CashSum AS Amount, TransId,(Select Account=(Select FormatCode From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLCode,
(Select Account=(Select AcctName From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLName
From OVPM Where CashSum!=0
Union All
Select DocNum,CardCode,CardName,
CheckSum, TransId,(Select Account=(Select MAX(FormatCode) From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLCode,
(Select Account=(Select AcctName From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLName
From OVPM Where CheckSum!=0
Union All
Select DocNum,CardCode,CardName,
TrsfrSum, TransId,(Select Account=(Select MAX(FormatCode) From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLCode,
(Select Account=(Select AcctName From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLName
From OVPM Where TrsfrSum!=0
)A Order By DocNum

[former_member634100]

Hello, msundararaja.perumal, this query does not have G/L accounts. How do I add G/L account to this query? It is too complicated for me to figure out. If I could just have the G/L account. It seems that it will serve the purpose.

Thanks in advance

[msundararaja_perumal]

From your sample query, it looked like you were using Account Segmentation, try the below one:

Select * From

(Select DocNum,CardCode,CardName,CashSum AS Amount, TransId,(Select Account=(Select AcctCode From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLCode,(Select Account=(Select AcctName From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLNameFrom OVPM Where CashSum!=0

Union All

Select DocNum,CardCode,CardName,CheckSum, TransId,(Select Account=(Select MAX(AcctCode) From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLCode,(Select Account=(Select AcctName From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLNameFrom OVPM Where CheckSum!=0

Union All

Select DocNum,CardCode,CardName,TrsfrSum, TransId,(Select Account=(Select MAX(AcctCode) From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLCode,(Select Account=(Select AcctName From OACT Where OACT.AcctCode=JDT1.Account) From JDT1 Where JDT1.TransId=OVPM.TransId And Line_ID=0) AS GLNameFrom OVPM Where TrsfrSum!=0)A

Order By DocNum

[former_member634100]

Hello msundararaja.perumal ,

All lines show up as Account Payable (_SYS00029), I wasnt to see if they are Prepaid expenses, rent expenses, freight, insurance, depreciation, etc.

[msundararaja_perumal]

Thanks for the sample screenshots, I have further queries.

For the outgoing payment the following scenarios are possible:

• Multiple AP Invoices could be chosen in the outgoing payment
• Multiple documents(like AP Invoices,AP Credit Notes, Journal Entries) could be chosen in the outgoing payment
• And each document could have more than 1 row details
• Payment on account done and later reconciled with the applicable document

If the above scenarios are applicable for you then may need to hire someone to completely study all the possibilities from the historical transaction and create the query accordingly.

Hope you understand where I am coming from.

[jhozz]

Hi krkz270, this query could work for you:

SELECT
	T0."AcctCode", T0."AcctName", T1."Debit" 
FROM OACT T0  
INNER JOIN JDT1 T1 ON T0."AcctCode" = T1."Account" 
INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId" 
WHERE T1."Debit" <> 0 AND  T2."RefDate" BETWEEN '[%0]' AND '[%1]'

If you use SQL Server then you'll need replace the " character with '[' or ']'

Regards.

[former_member634100]

Hello jhozz , I want to see the the ones that are generated by outgoing payments only. This brings up every entry.