Question

Printing attachment using pl/sql or reporting rule.

  • 16 August 2021
  • 9 replies
  • 1600 views

Userlevel 6
Badge +14

Hi all,

Some time ago we figured out who to batch print work instruction (using pl/sql).

Batch print Work Instructions for Shop Orders, IFS9

The solution has been working great. And now we would like to print the attachments too. 

We have our files with connection to routing head. But they will not automatically be printed because they have to be on routing guidelines. We solved that with a custom OCT to routing head.

 

When using the IFS RMB for printing, it is working:

 

However, it is not working from pl/sql when I add this parameter:

client_sys.Add_To_Attr('PRINT_MEDIA_ITEMS', 'Yes', parameter_attr_);

It is obvious why it is not working (we are on the server). But does anyone have an idea, on how to get the attachment printed without the pop-ups?

 

 


This topic has been closed for comments

9 replies

Userlevel 7
Badge +16

Hi @Hans Andersen ,

 

Can you explain what you are doing further ? 

Are you going to write pl/sql logic to set a default value to ‘Print Attached Media Item’ ?

You have mentioned that you want to avoid the pop up ? Is that the ‘work instruction option’ pop up. If that is the pop up you want to avoid then what about the other items that need to be added in the same pop up dialog ? Do you have a way to handle that ?

 

If you want to set the default value to ‘Print Attached Media Item’  to that then please try below.

client_sys.Add_To_Attr('PRINT_MEDIA_ITEMS', 'Y', parameter_attr_);

 

Userlevel 6
Badge +14

Can you explain what you are doing further ? 

Of course @Dumeesha Wanigarathna ,

After the shop orders have been created, we have a custom RMB that release the shop order and print out work instructions. Without any popups for confirmation/warning or print dialog. It saves us a lot of time.

We are in the process of moving our production files from a file server to docman. One advantage is that we can then print the drawings together with the work instructions. Safer and more time saved.

The challenge is that we can print the work instructions through pl/sql, because the report goes through the IFS print agent. The attachments are handled by the client (check-out and printed by the associated application).  It will almost certain not work with pl/sql (I did try anyway).

The good news is that we only wants to print the pdf-files. I was thinking if something clever can be done with the print agent or a pdf printer.

Userlevel 7
Badge +18

I have made a customization a couple of years ago that calls an external system which responds with a PDF (BASE64 encoded in the response) and I print the PDF via a remote print agent.

To print the non-report PDF I create a record in report archive (With a certain hardcoded IFS STD report id.) and then I add the PDF from external system in the PDF archive. When the print logic then prints it uses the PDF I inserted.

It is a bit of coding to do which has to bypass some steps in the report logic so I can’t describe it in detail here.

So yes it probably is doable if you have only PDF’s and you have them stored in a database repository in Docman.

Userlevel 6
Badge +14

Thanks @Tomas Ruderfelt ,

You solution sounds complex and something we should stay away from unless it is official supported by IFS.

We are not going develop a solution ourselves either. Too many unknows and no clear path to a solution.

I think we will work with what we have for now.

 

...and there an error in my description above. OCT will not be printed with the work instructions if check in “Print Attached Media Items”

Userlevel 7
Badge +20

I’ve once created a solution to attach all attachments in a record along with the report and email.

You can read more about the setup here:

Include additional attachments in IFS e-mail report – DSJ's Blog (dsj23.me)

 

May be this could be use for your scenario as well. You can email the Work Instructions report along with attachments and print manually or create a MS power automate or local VB script solution to automate the printing, or if you are lucky your printer could directly connected to an email ;)

 

Hope it helps!

Damith

Userlevel 6
Badge +14

Send an email to the printer? - As you say, we could get lucky. Easy to test also.

I might have a go at it.

:slight_smile:

Userlevel 6
Badge +14

@dsj , @Dumeesha Wanigarathna , @Tomas Ruderfelt ,

I have looked into this “email to print”. It turns out that it is a common feature. Canon, Epson, HP supports it.

I tested it on one of our HP- printers. It is working. It prints out the attachment - together with the email itself. That cannot be turned off. A first I was annoyed by this, but it can maybe be used for printing important information for the production. Related POs, stick notes, stock situation, etc.

Then there is security and GDPR...

Userlevel 6
Badge +14

@dsj , @Dumeesha Wanigarathna , @Tomas Ruderfelt 

I did eventually find a way of doing this without to much code. I am pretty sure I am not suppose to do this, but nothing has exploded yet. 

The trick is to print the work instruction multiple times to NO_PRINTOUT, then replace the pdf with pdf from docMan. Afterwards change the printer and print again.

If someone has a reason why we should not do this, please let me know.

Our attachments has connections to routing head, but they could be from anywhere.

code:

declare 
layout_name_ varchar(2000):= 'ShopOrdWiRep.rdl';
report_id_ varchar2(2000):= 'SHOP_ORD_WI_REP';
report_attr_ varchar2(2000);
parameter_attr_ varchar2(2000);
distribution_list_ varchar2(2000):= '';
instance_attr_ varchar2(2000);
print_job_id_ varchar2(2000);
print_attr_ varchar2(2000):= '';
result_key_ varchar2(2000);
attr_ varchar2(2000);

doc_class_ varchar2(100):= 'TEGNING';
order_no_ varchar2(2000):= '4489';
release_no_ varchar2(2000):= '*';
sequence_no_ varchar2(2000):= '*';
no_printer_id_ varchar2(2000):='NO_PRINTOUT';
printer_id_ varchar2(2000):='KONTOR';
objid_ rowid;
objversion_ varchar(32000);
info_ varchar2(32000);
n_ number:= 0;
print_job_status_ varchar2(100):= 'empty';
wait_sec_ number:= 1;
max_wait_ number:= 10;
elapsed_ number:= 0;

cursor c is
select edm.file_data
from shop_ord so
inner join doc_reference_object obj
on
Client_SYS.Get_Key_Reference_Value(obj.key_ref, 'PART_NO')= so.part_no
and Client_SYS.Get_Key_Reference_Value(obj.key_ref, 'BOM_TYPE_DB')=so.used_rout_bom_type_db
and Client_SYS.Get_Key_Reference_Value(obj.key_ref, 'ROUTING_REVISION')= so.routing_revision
and Client_SYS.Get_Key_Reference_Value(obj.key_ref, 'CONTRACT')= so.contract
inner join edm_file_storage edm
on obj.doc_no = edm.doc_no and obj.doc_sheet = edm.doc_sheet and obj.doc_rev = edm.doc_rev and obj.doc_class= edm.doc_class
where obj.lu_name in ('RoutingHead') and so.order_no = order_no_ and so.release_no= release_no_ and so.sequence_no=sequence_no_
and obj.doc_class = doc_class_ and edm.doc_type ='VIEW';

begin

--Create the print job as no_print_out
report_attr_:= 'REPORT_ID'||chr(31)||report_id_||chr(30)||'LAYOUT_NAME'||chr(31)||layout_name_||chr(30);
parameter_attr_:= 'ORDER_NO'||chr(31)||order_no_||chr(30)||'RELEASE_NO'||chr(31)||release_no_||chr(30)||'SEQUENCE_NO'||chr(31)||sequence_no_||chr(30);

client_sys.Add_To_Attr('OPERATION_WORK_TYPES', 'NORMAL', parameter_attr_);

archive_api.New_Client_Report(result_key_, report_attr_, parameter_attr_, distribution_list_, print_attr_);
archive_api.Get_Info(instance_attr_,parameter_attr_,result_key_);

attr_:= 'PRINTER_ID'||chr(31)||no_printer_id_||chr(30);
print_job_api.New(print_job_id_,attr_);

client_sys.Add_To_Attr('PRINT_JOB_ID',print_job_id_,instance_attr_);
client_sys.Add_To_Attr('RESULT_KEY',result_key_,instance_attr_);

print_job_contents_api.New_Instance(instance_attr_);

print_Job_API.Print(print_job_id_);

--for all pdf in loop, print job to no_print_out, wait for complete, replace pdf in print job
for rec_ in c loop

--wait for a second to make different objversions
DBMS_LOCK.Sleep(1);

if (n_>0) then --not first file
print_Job_API.Print(print_job_id_);
end if;

commit;

--wait for print job complete or timeout
elapsed_:=0;
print_job_status_:='empty';
while (elapsed_<max_wait_ and nvl(print_job_status_,'empty')!='COMPLETE')
loop
print_job_status_:=print_job_api.Get_Status_Db(print_job_id_);
elapsed_:= elapsed_ + wait_sec_;
DBMS_LOCK.Sleep(wait_sec_);
end loop;

--still not complete, abort
if (print_job_status_!= 'COMPLETE') then
dbms_output.put_line('timeout');
print_job_api.Remove(print_job_id_);
return;
end if;

--get objid now created
select arc.objid, arc.objversion into objid_, objversion_ from pdf_archive arc where arc.result_key = result_key_
order by arc.created desc
fetch first row only;

--replace pdf
pdf_archive_api.Write_Pdf__(objversion_, objid_, rec_.file_data);

n_:= n_+1;
end loop; --cursor

--change printer and print
print_job_api.Set_Printer_Id(print_job_id_,printer_id_);
print_Job_API.Print(print_job_id_);

commit;

end;

 

 

Userlevel 7
Badge +20

Awesome work @Hans Andersen ! ❤️ this one. Thanks for sharing the code.