Report Archive API for displaying .pdf invoices in external application

@JLACSPEAR I also had this requirement for one of my customers. What we did in that case was to create a quick report to filter for the invoice data that was supposed to be exposed to 3rd party app. The nice thing is that each Quick Report is a Projection also.

I’m leaving below the SQL code used to get the invoice info and the result key + pdf_id which is required by the PdfArchiveSet to get the PDF.

Also, one thing to consider, is that the service user that will get the PDF needs to be granted ADMINISTRATOR system priviledge. By default, each user could see their own reports, but with ADMINISTRATOR priviledge, the service user could extract any PDF from PDF Archive.

--getting project invoices
select i.company
       , i.identity as Customer_ID
       , ifsapp.customer_info_api.get_name(i.identity) as Customer_Name
       , ifsapp.sub_project_api.Get_C_Equipment_Object_Db(pii.project_id, pii.sub_project_id) as Functional_Object_ID
       , i.invoice_id
       , i.series_id || '-' || i.invoice_no as Invoice_No
       , pii.item_id as Invoice_Item_ID
       , i.objstate as Invoice_Status
       , i.invoice_date as Invoice_Date
       , to_char(i.invoice_date, 'YYYY') as "Year"
       , to_char(i.invoice_date, 'MM') as "Month"
       , i.due_date as Invoice_Due_Date
       , i.currency as Currency_Code
       , pii.net_curr_amount as Net_Amount_Currency
       , pii.vat_curr_amount as VAT_Amount_Currency
       , pii.gross_curr_amount as Gross_Amount_Currency
 --original result key not populated in Invoice Tab for project invoices. INSTR on PDF archive is necessary on layout name     
       , (select max(pa.result_key) from ifsapp.pdf_archive pa where pa.layout_name like 'C_ProjectInvoice%' and INSTR(pa.notes, i.series_id || i.invoice_no) > 0) as PDF_Result_Key
       , (select max(pa.id) from ifsapp.pdf_archive pa where pa.layout_name like 'C_ProjectInvoice%' and INSTR(pa.notes, i.series_id || i.invoice_no) > 0 and pa.result_key = (select max(pa1.result_key) from ifsapp.pdf_archive pa1 where pa1.layout_name like 'C_ProjectInvoice%' and INSTR(pa1.notes, i.series_id || i.invoice_no) > 0)) as "PDF_ID"
from invoice i, ifsapp.project_invoice_item pii
where 1=1
and i.company = pii.company
and i.invoice_id = pii.invoice_id
and i.party_type_db = 'CUSTOMER'
and i.creator = 'PROJECT_INVOICE_API'
and i.objstate not in ('Preliminary', 'Cancelled')
and ifsapp.sub_project_api.Get_C_Equipment_Object_Db(pii.project_id, pii.sub_project_id) is not null
and ifsapp.report_sys.parse_parameter(i.company,'&[-C--L]COMPANY_ID')='TRUE'
and ifsapp.report_sys.parse_parameter(i.identity,'&[-C--L]CUSTOMER_NO')='TRUE'
and ifsapp.report_sys.parse_parameter(ifsapp.sub_project_api.Get_C_Equipment_Object_Db(pii.project_id, pii.sub_project_id),'&[-C--L]Functional_Object_ID')='TRUE'

Many thanks both for the quick and informative responses.

For our use case the solution provided by Charith is most suitable but I do have a follow on question regarding how you identified the “PdfArchiveSet” endpoint.

The API documentation is reasonably good but it seem to me only when you know the endpoint that is most suitable as it does not contain a description of what each endpoint does. The debugger and log window can help with identifying the endpoints but if Aurena doesn’t log it through them is there any other way I could have identified the PdfArchiveSet as the correct one to use?

Hi @JLACSPEAR,

For the most part, it is a matter of inferring the correct endpoint by looking at the endpoint names and response/request bodies and path parameters.

I normally start the same way you did by looking at the Debug Console to identify the relevant projection first. Once the projection is identified, I looked at the available endpoints and their response bodies. Looking at the available endpoints, ArchiveDocumentSet and PdfArchiveSet seemed like good candidates. When looking at the sample response body of those endpoints in API Doc, both responses contained an attribute called ‘Pdf’.

Since you had already confirmed ArchiveDocumentSet does not work for your requirement, I invoked PdfArchiveSet in Postman to see what kind of a response I get and noticed it returned a base64 encoded string for the value of ‘Pdf’. 

Hope this helps!

Thanks @Charith Epitawatta for all your help

Hi,

We are wanting to do something similar, how are you identifying your invoice within the ReportArchive API? 

It looks like we can use the notes property, but I was hoping that there would be a more explicit way to link an Invoice number / ID with an item within the ReportArchvie.

Hi Mark

We didn’t spend much time on this to work out if there was a better way than using the notes field. I guess there maybe but we just used the following SQL as we were going to hold the data outside of IFS and could just pass it into the API so it worked for us:

select RESULT_KEY, ID, LAYOUT_NAME, Substr(NOTES, REGEXP_INSTR(NOTES, 'Customer No:') +13,6) as CUSTOMER_NO,
Substr(NOTES, REGEXP_INSTR(NOTES, 'Invoice No:') +12,11) as INVOICE_NO 
from IFSAPP.PDF_ARCHIVE
where trunc(CREATED) = '19-APR-23'
 

Hope that helps

Chris

Ok thanks for the information. That’s all that I could find that would link the entries within the Customer Invoice, I was just a bit concerned that the notes field wouldn’t always match.

Thanks for the reply!

For what it’s worth you can get the ResultKey from the CustomerInvoiceAnalysis APY as shown in this thread: 

Might be a better way to do it instead of using the notes, however the ability to get the ResultKey doesn’t seem to be available in all entities.