cancel
Showing results for 
Search instead for 
Did you mean: 

SQL-Query - AR Invoice and related AR Down Payment Invoices

Former Member
0 Kudos

Hello everybody,

I want to show all AR Down Payment Invoices related to a given AR Invoice, i.e. a query approximately in this form:

SELECT

T0.DocNum AS 'Invoice',

T0.DocDate AS 'Invoice Date',

T0.DocTotal AS 'Open Amount',

T1.<<DocNum>> AS 'Down Payment Invoice',

T1.<<DocDate>> AS 'Down Payment Invoice Date',

T1.<<Amount>> AS 'Down Payment Invoice Amount'

FROM

OINV T0 INNER JOIN <<Down Payment Invoices>> T1

ON T0.DocEntry = T1.<<DocEntry>>

WHERE

T0.DocNum = xxxxx

I want to know if such a table <<Down Payment Invoices>> exists and what its name is.

Thanks for your help!

Regards,

Frank Romeni

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member

The solution for my question is INV9 (not INV11 as suggested! INV11 is empty, don't know, what it is good for!).

In INV9 all down payments related to an invoice. Here is an example query:

SELECT

T0.DocNum, T0.DocTotal, T1.DrawnSum

FROM

OINV T0 LEFT OUTER JOIN INV9 T1

ON T0.DocEntry = T1.DocEntry

WHERE

T0.DocNum = xxxxx AND

T1.ObjType = 203 -- down payment

Regards

Frank Romeni

former_member186095
Active Contributor
0 Kudos

Hello,

the table for down payment invoice are ODPI and DPI1. For more detail about it, just use SDK help files.

Rgds,

Former Member
0 Kudos

Sorry, but this doesn't help any further.

I already know ODPI/DPI1, but this table only provides me with all down payments added to the DB.

The question was "How can I find down payments related to a given invoice?".

Somehow this must be possible: When we look into an AR Invoice document, we see field "Total Down Payment" and in front of it the button to see a list of down payments related to the invoice.

Regards

Frank Romeni

former_member186095
Active Contributor
0 Kudos

Hello,

I think you could use what I have written (about SDK helpfiles). It is available in SDK helpfiles --> tab contents --> database table references. There is table name drawn down payments (INV11).

Anyway, about your question : "How can I find down payments related to a given invoice?" is really confusing to me since to find anything in SAP B1 is using helpfiles or maybe you wish to find it on the street ?

Rgds,

Former Member

Thanks for the INV11 - but this table seems to be empty even for invoices we have related down payments, e.g.

SELECT

T0.DocNum, T0.DocTotal, T1.<<whatever you want>>

FROM

OINV T0 LEFT OUTER JOIN INV11 T1

ON T0.DocEntry = T1.DocEntry

WHERE

T0.DocNum = xxxxx

That INV11 is always empty I could see with SELECT * FROM INV11

Regards

Frank Romeni

P.S.: I only post questions to this forum when I analysed my problems and only if I couldn't find any further information in the helpfiles. With regard to your first hint to ODPI there is no reference to INV11 in the helpfiles.

With regard to your comments I feel impelled to to say that if my question is confusing you, then it seems that nobody has ever taught you that meaning can be taken from the context. Regarding the "street", you asked me to find something: Do not waste your time with talking precociously ...

If you should feel annoyed by my comments, we'd better stop this conversation. Otherwise let us fall back to a strictly factual exchange of information, OK? I am not willing to discuss this any further!

F.R.