Customer / Supplier Invoice unpaid Items at period end - API

Hi,

When you say you do not want a report, you mean you do not want a PDF as an output I guess ? Because anyway all output are just a conversion of JSON, XML or CSV file.

There are several ways to get the data you expect in JSON format. The complexity depends on the type of queries you need to perform on the source. In your case there is an end point related to customer invoice analysis, then you can filter your invoice with an ODATA query. If a criteria is missing, like a specific status on this table, you can create a custom field that fetch this status and you can then add this criteria to your odata query.

But if your request is really complex and actually requires to combine output of several tables, then the easiest way is to make a quick report where you write your complex SQL query, then you just connect to the end point of the Quick Report projection, you ll get a JSON file of the output of your complex query.

Hey! 

Yes, no PDF and not to create specific reports by using QueryDesigner or likewise. Want to use “standard” endpoints so its generic in that sense. 

The part I have trouble to find a solution for is to get the right status for an invoice at a specific date. In my case I want to create a table with unpaid invoices at 31st of December listed invoice by invoice. 

ok, in your post you seem to use IFS Cloud, we are using Aurena, but i think it s similar.

The best way to start if you re not familiar is to simply go to your page, make a filter and copy the end point form there:
 

you will get your projection:

CustomerInvoicesAnalysis.svc/OutgoingInvoiceSet?

Select statement (= the columns you want)

$select=Identity,SeriesId,InvoiceNo,GroupId,SelfBillingRef,InvoiceVersion,AdvInv,CorrectionInvoice,CorrectionExists,NcfReference,Objstate,InvoiceType,ReasonCodeExist,VoucherDateRef,VoucherTypeRef,VoucherNoRef,InvoiceDate

Expand Statement (= sub queries)

&$expand=CurrencyCodeRef($select=ParallelAccCurrency),InvoiceTypeRef($select=Description)

Your filter with the required terminology

&$filter=(Company eq 'XXX') and (Objstate%20eq%20IfsApp.CustomerInvoicesAnalysis.InvoiceState'PostedAuth')&$orderby=InvoiceDate&$format=json

As you can see the state of an invoice is not defined as other columns.

But the right way if you do not want to guess is to use your API explorer:

and read the documentation.

Here for example, you can access the quick report end point that fetch JSON file from any quick report SQL query you made. That pretty powerful.

If you focus only on the Invoice projection, then search for this projection here (CustomerInvoicesAnalysis) and read the API documentation associated.

Just to give you another option, there exists standard functionality to create a table overview of open items (customer/supplier)

for a specific date - even for multiple companies:

Hi David,

we created a FACT_OpenItem on the MS SQL Server DWH.
With OpenAmount <> 0 you should get the invoices you want to see.

CU
Michael