269 total views
In this article we will look into how to add a static PDF/conditional static pdf to the existing bursted PDF output.
There could be scenarios where notifications should be sent to employees along with an additional pdf content which would change frequently. Normally we will keep the entire content on the rtf template and generate pdf output with bursting query and send it to employees. However whenever there is a change, we will have to modify the template everytime.
- Sending New Hire Notification to employees. You can include a PDF with Induction, Do’s, Don’ts, Contact persons list which could change frequently.
- Sending New Year notification for all employees. You can include PDF with holiday list for that specific year and this holiday list will change every year.
- Sending Appraisal/Compensation Notifications to employees. You can include info of compensation raises across the organization for that particular year.
In all of the above cases, there is a content which will change by employee and that part is covered in the rtf template that is created. For the remaining part that will be changed once in a while, we can upload the PDF file directly to UCM and use the attachment feature of the bursting query to directly add that PDF file to the existing bursted PDF output.
As per Oracle’s documentation we can even attach PDFs of Invoice , “You may have a requirement to attach PDFs along with invoices for customers. You can now attach these documents as an attachment along with the invoice while bursting“. However I was not successful in achieving this part yet.
Adding External attachment feature works only for the PDF output and the existing bursting query should also send the PDF output only.
Let’s look into more details on this functionality with a sample use case.
I will take up an existing Person details report and will try to add a static sample pdf attachment to it using bursting query.
The output of the Person Details Report will look like below on the normal run of the report:
Now, let’s take a static PDF like below and try to add it to the output pdf:
First, we will have to upload the static pdf to UCM using Content Server URL – https://abcd-dev.fa.us2.oraclecloud.com/cs. Use the Security Group as UCM_Spaces, else the BI Report won’t be able to fetch the document.
Now, Let’s navigate to the Data Model and add dummy bursting query for dynamic file name
SELECT 'DUMMY' AS "KEY", 'Demo' TEMPLATE, 'PDF' OUTPUT_FORMAT, 'Bursted Output File' as OUTPUT_NAME FROM dual
With this bursting query, if we run the BI Report we would get the output as below:
Now, Let’s add the static PDF that we uploaded to UCM to the bursting section.
- Click the Attachment tab.
- Select the content server name from the Attachment Repository LOV. By default it would be FA_UCM_PROVISIONED.
- Define the SQL Query for the attachment in the Content Server.
We can fetch the document details of uploaded file using the REVISIONS table (This is a hidden table for which there isn’t any documentation available).
Query to fetch the Document ID for the uploaded file:
select 'DUMMY' Key, Max(did) as ATTACHMENT_ID FROM revisions WHERE did IN (SELECT did FROM revisions WHERE UPPER(ddoctitle) = 'DEMO')
The column alias names KEY and ATTACHMENT_ID are mandatory. Key should refer to the Split by and Deliver by Keys defined on the “Bursting Query” tab and should be present on the Data Model SQL Query as well.
We can see that the output PDF contains two pages now with the second page from the external pdf that we uploaded to UCM.
References: Oracle Documentation
Hope this demonstration was useful and will help you when you have similar requirements.
If you have any questions, please post them in comments section.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020