Pull Open Invoices data with a SQL query as of an 07/31/2016

(0) Share

Report

Posted on by Community Member

Im trying to create a query to pull information from the AR module. I need the open invoices as of an X date. I know that I have a table with the open invoices, but that table change every day.

I need an SQL query where I can adjust the As Of date for the open invoices information?

Thanks you

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (0)

André Arnaud de Cal... 301,196 Super User 2025 Season 2 on at

Like (0)

Report

Hi Arieljw,

You can run the Customer aging report which is able to provide the information about invoice as of a certain date.

Accounts Receivable > Reports > Status.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank Andre. But I need it from SQL as I need to feed an Essbase cube

Was this reply helpful? Yes No
Sohaib Cheema 49,443 User Group Leader on at

Like (0)

Report

what is your definition for "Open Invoices"?

Does that means unpaid or partially paid yet? or something else?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Customer invoices unpaid and partially paid as of an X date.

Was this reply helpful? Yes No
Sohaib Cheema 49,443 User Group Leader on at

Like (0)

Report
This refers to concept that you want Open customer invoices i.e. Unpaid or Partially paid.

Any customer invoice which is created goes into CustTrans Table as a specific transaction Type. The transaction Type is defined as column in CustTrans Table.

As long as a transaction is open there are corresponding records for in CustTransOpen Table for each CustTrans Record. There is checkbox named as closed in CustTrans which refers that transactions has been fully paid.

Here you go with SQL

SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount FROM VENDTRANS where VENDTRANS.closed = 0 AND ((VendTrans.TransType = 36) OR (VendTrans.TransType = 3) OR (VendTrans.TransType = 14)) AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1)) --AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Sohaib.

In your query you are filtering by custrans.closed =0, and it will be 0 at the moment I run the query. But I want to run a query where I can see all invoices that were open for example on 07/31/2016.

I was reviewing the custrans table, and the table sumarize all the payments, doesnt bring the detail for each payment. So Im having problems to identify when a customers does a partial payment.

Was this reply helpful? Yes No
Sohaib Cheema 49,443 User Group Leader on at

Like (0)

Report
Hi In this case we need to use table VendSettlement

whenever you do a payment a settlement is created in table VendSettlement

This mean that standing today, if you want to know whether a transaction was open in past date, you need to calculate balance of invoice till that date. To do this you need get sum of VendSettlementas till that date.

Then subtract TotalInvoiceAmount minus(-) SumOfSettlementmentsAsOfThatDate.

Have a look at below code, which is x++

//getting sum of amounts paid for an invoice as of specific date (Amount paid till a specific date)

select sum(SettleAmountMST) from VendSettlement where VendSettlement.TransCompany == VendTrans.dataAreaId && VendSettlement.TransRecId == VendTrans.RecId && VendSettlement.AccountNum == VendTrans.AccountNum && VendSettlement.TransDate <= YourDate;

Now do the subtraction part

BalanceOfInvoiceAsOfSpecificDate = VendTrans.AmountMST(InvoieAmount)-sum(SettleAmountMST)

Note that VendTrans.AmountMST is your invoice total amount

Using same logic you can write your SQL

Was this reply helpful? Yes No
Sohaib Cheema 49,443 User Group Leader on at

Like (0)

Report

Hi Arieljw,

Have you been able to develop SQL query?

Do let me know if you want me to post exact SQL query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sohaib

I couldn't do it yet. I wil be really apreceited if you can post the query.

Thanks

Was this reply helpful? Yes No
Sohaib Cheema 49,443 User Group Leader on at

Like (1)

Report
Here we go. Do let me know if you need explanation of any line. Note that you have to assign company id and your date, as of which you want to see open transactions in sql variables of query

/*Needed variable*/ Declare @myDate date; Declare @CompanyId VarChar(4); /*End variable declaration*/ /*set/initialize variables: set values as per your need and system.*/ set @myDate = GETDATE(); set @CompanyId = 'USMF' /*end setting variable values*/ /*Start Query*/ SELECT VendTrans.Invoice,VENDTRANS.AccountNum,DirPartyTable.NAME,VendTrans.TransDate,VendTrans.DueDate,VendTrans.PaymMode as MethodOfPayment,VendTrans.CurrencyCode, (VendTrans.AmountCur)-( Select ISNULL(sum(SettleAmountMST),0) from VendSettlement where VendSettlement.DATAAREAID = VENDTRANS.DATAAREAID AND VendSettlement.ACCOUNTNUM = VENDTRANS.ACCOUNTNUM AND VendSettlement.TRANSRECID = VENDTRANS.RECID AND VendSettlement.TransDate <= @myDate ) AS AmountNotSettled ,VendTrans.AmountCur AS InvoiceAmount ,(Select ISNULL(sum(SettleAmountMST),0) from VendSettlement where VendSettlement.DATAAREAID = VENDTRANS.DATAAREAID AND VendSettlement.ACCOUNTNUM = VENDTRANS.ACCOUNTNUM AND VendSettlement.TRANSRECID = VENDTRANS.RECID AND VendSettlement.TransDate <= @myDate)*-1 AS PaidOrSettledAmount, VendTrans.VOUCHER FROM VENDTRANS Left outer join VendTable On VendTable.ACCOUNTNUM = VendTrans.ACCOUNTNUM AND VendTable.DATAAREAID = VendTrans.DATAAREAID Left outer join DirPartyTable On DirPartyTable.RECID = VendTable.PARTY where VENDTRANS.closed = 0 AND ((VendTrans.TransType = 36) OR (VendTrans.TransType = 3) OR (VendTrans.TransType = 14)) AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1)) --AND VENDTRANS.ACCOUNTNUM = '1001' --/*You can filter query for specific vendor account*/ AND VENDTRANS.DATAAREAID = @CompanyId Order by VendTrans.ACCOUNTNUM, VendTrans.TRANSDATE /*End Query*/

Was this reply helpful? Yes No