Open Invoices on Specific Date in SAP B1

[Former Member]

Dear All,

I need to create a report with the list of open invoices on a specific date in the past..

For example, I need to know the total amount of open invoices for customer X on January 1st 2014, February 1st 2014..December 1st 2014.

Can you pls help me in finding the corresponding tool?

I am using SAP Business One 8.82.

Thanks in advance,

Sabina.

[former_member212181]

hi Sabina,

Please have a try below query and give me feedback.

Declare @FDate Datetime

Declare @TDate Datetime

Select @FDate = Min(X.DocDate) from OINV X where X.DocDate>='[%0]'

Select @TDate = Max(Y.DocDate) from OINV Y where Y.DocDate<='[%1]'

;WITH Reco AS

(

SELECT T1.SrcObjAbs[RecoEntry]

  , Max(T0.[ReconDate])[LastAdjDate]

  ,Sum(T1.[ReconSum])[AdjAmt]

FROM OITR T0

  INNER JOIN ITR1 T1 ON T0.[ReconNum] = T1.[ReconNum]

WHERE   T1.[SrcObjTyp] =13 and T1.IsCredit='D'

  and  T0.[ReconDate] >=@FDate and  T0.[ReconDate] <=@TDate

GROUP BY T1.[SrcObjAbs]

)

---

Select A.DocEntry, A.DocNum,A.DocStatus[Current Status], A.DocDate, A.CardCode, B.CardName, A.DocTotal, C.LastAdjDate,  A.DocTotal-isnull(C.AdjAmt,0)[Due]

From OINV A

  Inner Join OCRD B on A.CardCode = B.CardCode

  Left Outer Join Reco C on A.DocEntry = C.RecoEntry

Where A.Canceled = 'N' and  A.DocDate>=@FDate and  A.DocDate<=@TDate

  and A.DocTotal-isnull(C.AdjAmt,0) <>0

Thanks

Unnikrishnan

[Former Member]

Dear Sabina

Try this I was also looking for the same issue. I have developed a query which solved this issue

declare @DocDate DateTime

Set @DocDate='04-30-2015'

select * from OINV T0

where T0.DocDate<=@DocDate

and ReceiptNum is not null

and T0.DocEntry not in

(select D0.DocEntry from RCT2 D0 inner join ORcT D1 on D0.DocNum=D1.DocNum where InvType=13 and D1.DocDate<=@DocDate

union all

select isnull(BaseEntry,0) from RIN1

)

union all

select * from OINV T0

where  T0.DocDate<=@DocDate

and ReceiptNum is  null

and T0.DocStatus='O'

and T0.DocEntry not in

(select D0.DocEntry from RCT2 D0 inner join ORcT D1 on D0.DocNum=D1.DocNum where InvType=13 and D1.DocDate<=@DocDate

union all

select isnull(BaseEntry,0) from RIN1

)

order by 1

Regards

Ehsan

[Former Member]

Dear Sabina

make query in take the fields creation date ,porting date ,open status by these fields your problem will be solved

Regards

Ramandeep Sharma

[former_member184146]

Hi,

you can achieve this by using open item list report under sales report ,you can filter your report by posting date , document numbers , customers , document date , document type etc. using equal to , in range , <=,>= etc parameters .

check the below screen shot

--Manish

[frank_wang6]

Guys, all above answers are not the original intention.

Basically Sabina wants to have a back dated Open Invoices report.

Lets today is 3/29, but if he wants to know what invoice was open back on 1/1/2015, what kind of query can give him that result.

I used to write one in Oracle EBS, but I havent tried for SAP B1. The query should be based on the process from incoming payment transactions applied to invoices. It should be a very complicated one.

The flow should be something like this. For each of the invoices, basically the query needs to check all incoming payments and see which one is related to it, and decide if the payment date is before the required date or not, and then decide whether that payment should be applied or not. After loop all invoices and sub loop all incoming payments, basically you can get a list of invoices that are open.

Frank

[Former Member]

Dear Frank,

Thank you for your reply.

This is exactly what I need, the problem is that I do not know the technique of creating such a query..

Thanks,

Sabina.

[Former Member]

Hi Manish,

Thanks for your reply. Unfortunately the invoices that I need are not located in the open item list due to the fact that they are already closed. The thing is that I need to know that invoices were open let's say on January 1st 2014. Most of them are obviously closed now.

Thanks,

Sabina.

[Former Member]

Hi Sabina,

This may give you a start:

select T0.Docdate, T0.DocNum, T0.Docstatus from OINV T0 where

                 T0.docdate <= DATEADD(MONTH,DATEDIFF(MONTH,-1,CONVERT(VARCHAR(10),GETDATE(),110))-1, -1)

You may check with the ADOC table to see if it gives you the desired results.

ObjType would be 13 for Invoices.

Thanks,

Joseph

[frank_wang6]

Did some research again, actually the standard SAP Customer Aging Report can give you that, but just with two options checked in order to provide correct answer. (Display Customers with Zero Balance, and Display Reconciled Transactions.)

Frank

[Former Member]

Hi Sabina,

Try this.

Go to Tools > Query Generator > Click Execute.

Paste this query in the blank provided and click Execute. This query will ask for the customer that you want to run the report as well as the inclusive dates.

select

a.DocEntry,

a.CardCode,

a.CardName,

a.DocDate,

a.doctotal,

a.DocStatus

from oinv a

inner join inv1 b on a.docentry = b.docentry

where a.DocStatus = 'O' and a.docdate >= [%0] and a.docdate <= [%1]

Regards,

Arlene

[Former Member]

Hi Arlene,

I got a feeling that your answer might really help me out.

However, here's the mistake that appears when I am clicking Execute.

Could you kidnly tell me if there's anything that I am doing wrong?

Thanks,

Sabina.

[former_member188586]

hi

try with bellow query

SELECT T0.[DocNum],T0.[CardName],t0.[DocDate],T0.[TaxDate],T0.[DocDueDate],T0.[Project], (T0.[DocTotal]-T0.[PaidToDate]) "Balance" FROM OPCH T0 WHERE T0.[DocStatus] ='O' and T0.[CardName] =[%0]

Regards

AKR

[Former Member]

Hi Sabina,

I think you forgot to include the word 'Select' at the top of the query.

Regards,

Arlene

[Former Member]

Hi,

Can't you use Open item list from sales report and select A/R invoice.

You can sort on any field you like.

[Former Member]

Hi Chintan,

Thanks for your reply. Unfortunately the invoices that I need are not located in the open item list due to the fact that they are already closed. The thing is that I need to know that invoices were open let's say on January 1st 2014. Most of them are obviously closed now.

Thanks,

Sabina.