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'

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!

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.

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!