Automate IFS Printing via PL/SQL

Automatically printing an IFS report during a business transaction is one of the most common questions asked by customers. Many wants to avoid the print dialog since it adds few extra clicks to the process and therefore need to automate the printing. In some common documents like Customer Order, Purchase Order, Invoice, you have the option of automating the report but most of other reports are missing that.

You can use a custom event action or a custom menu of type PL/SQL to automate report printing. In this blog, I have listed down the main steps involves to print a Info Services report. By combining the available options in IFS, it is possible to design many varieties of reporting flows. For example, you can define only to email the report to the user, email and print both, bundle several reports together and print and so on.

On top of these settings, there are report rules where you can do much more stuff as well. With all its capabilities, IFS reporting framework has evolved as a powerful and versatile tool over the years.

Here I’ve described the basic PL/SQL logic which needs to be written to automate report printing without popping up the print dialog. It was mainly done for my own reference since this is something I often needed and thought of documenting properly. Before dig deep to coding, we’ll see the process of IFS report execution so it would give a better understanding about the logic which needs to follow in our code.

IFS Print request consist of 3 steps.

  • Print Job – Print job is the holder of what being printed. It has the information about the printer and other related settings
  • Archive – When you order a report with specific parameters, a record is created in the report archive. main outcome of the archive is the Result Key, which is the unique identifier of the executed report. Once you create a archive record, you can use that even later occasions to print the report until its cleaned up after the expiration date.
  • Print Job Contents – This tells which archive record(s) are being printed, and which layout and other formatting settings should be used.

One print job can contain many print job contents. Print job content is depend on archive and there’s one to one relationship between them.

Now we’ll see some coding on how to create each one of there pieces. Given below are some great examples found in the IFS Community Hats off and credit goes to the respective authors.

Simple Create and Print Report

Following code block shows the steps you need to create and print a report. Report ID can be found by searching the report in the Report definitions. In this example I’m using the Work Instruction Report.

report_id_ VARCHAR2(100) := 'SHOP_ORD_WI_REP'; Report ID
report_attr_ VARCHAR2(2000);
parameter_attr_ VARCHAR2(2000);
distribution_list_ VARCHAR2(2000):= '';
result_key_ NUMBER;
printer_id_ VARCHAR2(100);
print_job_attr_ VARCHAR2(2000);
job_contents_attr_ VARCHAR2(2000);
print_job_id_ NUMBER;
instance_attr_ VARCHAR2(32000);
lang_code_ VARCHAR2(2);
printer_id_list_ VARCHAR2(32000);
(1) Create Archive instance
Create the report attr
CLIENT_SYS.Add_To_Attr('REPORT_ID', report_id_, report_attr_);
Add report parameters
CLIENT_SYS.Add_To_Attr('ORDER_NO', 'SO001' , parameter_attr_);
CLIENT_SYS.Add_To_Attr('RELEASE_NO', '1' , parameter_attr_);
CLIENT_SYS.Add_To_Attr('SEQUENCE_NO', '1' , parameter_attr_);
Create Archive instance.
It will return the result key
Archive_API.New_Instance(result_key_, report_attr_, parameter_attr_);
(2) Create Print Job and contents
Get the user default printer.
printer_id_ := Printer_Connection_API.Get_Default_Printer(Fnd_Session_API.Get_Fnd_User, report_id_);
Client_SYS.Add_To_Attr('PRINTER_ID', printer_id_, print_job_attr_);
Create Print job
Print_Job_API.New(print_job_id_, print_job_attr_);
Client_SYS.Add_To_Attr('RESULT_KEY', result_key_, job_contents_attr_);
(3) Print!

Finding the report parameters is a tricky thing but following SQL will help you to find the parameters to retrieve the report.

  SELECT report_id, column_name, column_title, column_value
   FROM   report_column_definition
   WHERE  report_id = 'SHOP_ORD_WI_REP'
   AND column_query IS NOT NULL
   ORDER BY column_id;

Adding Attributes to use in PDF_REPORT_CREATED Event

PDF_REPORT_CREATED event is a very popular and easy way of configure emailing the report to others. We add our own values for the event attributes and then use them in the event action. These values can be added while creating the print job. Below is a sample code for that.

It’s possible to add 15 event parameters and preferred file name to be used in the attachment using PDF_FILE attribute.

-- pdf_info_ VARCHAR2(32000); define pdf_info_ as varchar2
pdf_info_ := Message_SYS.Construct('PDF');
Message_SYS.Add_Attribute(pdf_info_, 'PDF_EVENT_PARAM_1', 'YOUR_VALUE1');
Message_SYS.Add_Attribute(pdf_info_, 'PDF_EVENT_PARAM_2', 'YOUR_VALUE2');
Message_SYS.Add_Attribute(pdf_info_, 'PDF_EVENT_PARAM_3', 'YOUR_VALUE3');
Message_SYS.Add_Attribute(pdf_info_, 'PDF_EVENT_PARAM_15', 'YOUR_VALUE15');
Message_SYS.Add_Attribute(pdf_info_, 'PDF_FILE_NAME', 'YOUR_VALUE15');

Client_SYS.Add_To_Attr('SETTINGS', pdf_info_, print_job_attr_);
-- Create Print job  
Print_Job_API.New(print_job_id_, print_job_attr_);

Scheduling Reports

We need to use report scheduling due to several scenarios

  • Reduce the system impact if a large report runs during working hours.
  • We might have to send report to users on an agreed date and time
  • Avoid table mutations which might happen when report is created using custom events based on same table which report is gathering data from.

Using Archive_API.Create_And_Print_Report__ we can perform both create and print functionality which we discussed above. below code is the same as above but runs as scheduled task.

result_key_ NUMBER;
report_attr_ VARCHAR2(32000);
parameter_attr_ VARCHAR2(32000);
message_attr_ VARCHAR2(2000);
archiving_attr_ VARCHAR2(2000);
distribution_list_ VARCHAR2(32000);
print_attr_ VARCHAR2(32000);
next_execution_date_ DATE;
schedule_name_ VARCHAR2(200);
schedule_id_ NUMBER;
seq_no_ NUMBER;
start_date_ DATE := SYSDATE;
report_id_ VARCHAR2(100) := 'SHOP_ORD_WI_REP';
pdf_archiving_ VARCHAR2(5) := 'FALSE';
printer_id_ VARCHAR2(1000);
CLIENT_SYS.Add_to_Attr('REPORT_ID', report_id_, report_attr_);
CLIENT_SYS.Add_To_Attr('ORDER_NO', 'SO001' , parameter_attr_);
CLIENT_SYS.Add_To_Attr('RELEASE_NO', '1' , parameter_attr_);
CLIENT_SYS.Add_To_Attr('SEQUENCE_NO', '1' , parameter_attr_);
schedule_name_ := Report_Definition_API.Get_Translated_Report_Title(report_id_);
printer_id_ := Printer_Connection_API.Get_Report_User_Printer(Fnd_Session_Api.Get_Fnd_User, report_id_, Fnd_Session_API.Get_Language);
Client_SYS.Add_To_Attr('MESSAGE_TYPE', 'PRINTER', message_attr_);
Client_SYS.Add_To_Attr('PRINTER_ID', printer_id_, message_attr_);
Client_SYS.Add_To_Attr('PDF_ARCHIVING', pdf_archiving_, archiving_attr_);
Create the new scheduled report.
'ASAP', any valid schedule expression can be given here
Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, 'REPORT_ATTR', report_attr_);
Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, 'PARAMETER_ATTR', parameter_attr_);
Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, 'MESSAGE_ATTR', message_attr_);
Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, 'ARCHIVING_ATTR', archiving_attr_);
Batch_SYS.New_Batch_Schedule_Param(seq_no_, schedule_id_, 'DISTRIBUTION_LIST', distribution_list_);

As I mentioned in the beginning, IFS reporting framework is a powerful tool with tons of capabilities and hope you’ve figured out one or two tricks from this post. Please leave your thoughts and share this if you think it could save someone’s day!


2 thoughts on “Automate IFS Printing via PL/SQL

Add yours

  1. Great Post! Can you schedule these type of reports in IFS Cloud?
    As no option at the moment to create custom commands where you can link this code to?

    Eg. Batch print sales orders of today?

    1. Hi K,
      Unfortunately in IFS cloud, it’s not possible to execute a PL SQL code from a custom command or execute as a schedule job. If you have developer access, then it’s possible to create a database package and procedure to call from a custom command or execute as a scheduled job.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Powered by

Up ↑

%d bloggers like this: