Open invoices in the past

[Former Member]

Dear people,

Our management wants to compare the total open purchase invoices from a certain period to the actual period. So, how much money did we need to pay in for example 01-06-2008 until 30-06-2008 and how much money do we need to pay now from 01-06-2009 until 30-06-2009.

I found out that in table OPCH I can find the invoices, but can not select them based on a status (paid / unpaid) which was in the past.

Could somebody please give me a hint or maybe a solution to this?

Thank you in advance.

Jozua Lagendijk

[former_member187989 · Accepted solution]

hi,

Try this query

SELECT T0.DocNum, 
T0.DocDate, 
T0.CardName, 
T0.DocTotal, 
T0.PaidToDate,
(T0.DocTotal-T0.PaidToDate) as 'Need to Pay'
FROM OPCH T0 
WHERE T0.DocDate >=[%0] AND  T0.DocDate <=[%1]

Jeyakanthan

[Former Member · Accepted solution]

Dear Jeyakanthan,

thank you very much for this solution. If I'm correct, when I executed this statement it showed all the invoices we paid in a certain period (if I interpret the result correctly). This a good start of solving my problem, thanks for this.

The next step for my question is then to figure out which invoices were still open and needed to be paid.

Looking forward for your help.

Thanks in advance,

Jozua

[Former Member · Accepted solution]

Hai!

Try this, i little modified Mr. Jey's Query

Declare @a datetime
Declare @b datetime
set @a = (select min(M0.DocDate) from OPCH M0 where M0.DocDate >= '[%0]')
set @b = (select max(M1.DocDate) from OPCH M1 where M1.DocDate <= '[%1]')
Select
* from (
SELECT T0.DocNum, 
T0.DocDate, 
T0.CardName, 
T0.DocTotal, 
T0.PaidToDate,
(T0.DocTotal-T0.PaidToDate) as Balance
From OPCH T0
WHERE T0.DocDate >=@a AND  T0.DocDate <=@b and
T0.DocStatus = 'O')a
Where
a.Balance <> 0

Regards,

Thanga Raj.K

[Former Member · Accepted solution]

Dear Thanga,

thanks for the input.

Your query works for documents with docstatus O, but when I change this to 'C', then the result is 0 invoices.

Maybe the following example helps some more for what I need.

Paid To be paid

June 2008 u20AC 50.000 u20AC 75.000

June 2009 u20AC 15.000 u20AC 125.000

(the layout got mixed up, 'Paid' and 'To be paid' are 2 collum headings for the amount)

Is it actually possible to see when the DocStatus changed from 'O' (unpaid) to 'C' (paid) ?

Thanks people for all you help!

Best regards,

Jozua

Edited by: Jozua Lagendijk on Jun 15, 2009 9:40 AM

[Former Member]

Your management required report is one of the most difficult report to get. That is because the payment information in B1 could only reflect up to date info. If you need to know what was paid as of the historical dates, you have to recreate all transaction history with exactly data range one by one.

The easy solution would be just looking the AP Invoice total for last year instead of historical payments. If that is not acceptable, you have to create temporary table and a complicated query to get the report.

Thanks,

Gordon

[Former Member]

Hello Gordon,

thank you for the explanation.... I'll go with this to my management and inform them what is possible and what not...

Everybody thanks for the quick and good replies.

Best regards,

Jozua

[former_member187989]

hi,

This query will give open invoices,Refer DocStatus field for document status.

SELECT T0.DocNum, T0.DocStatus,
T0.DocDate, 
T0.CardName, 
T0.DocTotal, 
T0.PaidToDate,
(T0.DocTotal-T0.PaidToDate) as 'Need to Pay'
FROM OPCH T0 
WHERE T0.DocDate >=[%0] AND  T0.DocDate <=[%1] AND T0.DocStatus = 'O'

Jeyakanthan

[Former Member]

Hai!

This is default Report in SAP B1.

Modules>Purchase AP > Open Item List.

Select A/P Invoice. Use Filter option to get for particular period.

Regards,

Thanga Raj.K

[Former Member]

Dear Thanga,

thank you for you quick reply.

Your solution is not what I'm looking for, I'm afraid. In your given solution you can only filter those who are still open / unpaid. You cannot go back (for example) 1 year to see which invoices where unpaid back them.

If I'm not correct, please let me know.

Best regards,

Jozua

[Former Member]

Hai!

If your requirement is to see All Open Invoices (Unpaid), then Open Item List is Best. This is not Depends on Fiscal Year. It will show Open Invoices even it is in previous fiscal year.

What the Problem u have faced in this report? wheather any record it's not showing as u said?

And, What mean Go Back for 1 Year ?

Regards,

Thanga Raj.K

Edited by: Thanga Raj K on Jun 15, 2009 9:02 AM

Edited by: Thanga Raj K on Jun 15, 2009 9:03 AM

[Former Member]

Dear Thanga,

basically my management want this:

June 2008= how much money did we need to pay to our suppliers?

June 2009= how much money do we need to pay to our suppliers?

They want to compare these numbers to see if their financial position has improved in 1 year (so less suppliers / money to be paid compared to last year).

Maybe this information is usefull for you.

Thanks in advance.

Jozua