@dsj on the ball as usual.
Here’s a bit of extra I currently employ if you want to use the same block to create the PDF file in the report document archive as well, rather than just the result set.
This is a generic procedure I created that can take up to 20 Parameters (9 in the NCR Report Above for instance), although it does require providing the language_code and layout_name (obviously it could also be modified to grab the default values for lang_code and layout_name if none are provided in the attribute).
It outputs the result key and the print job id, so that the call handler can then manipulate the report document archive entry based on those keys.
PROCEDURE report_new_instance_and_print(attr_ in varchar2, result_key_out_ out number, print_job_id_out_ out number) IS
result_key_ NUMBER;
job_attr_ VARCHAR2(2000);
job_contents_attr_ VARCHAR2(2000);
print_job_id_ VARCHAR2(25);
instance_attr_ VARCHAR2(32000);
real_report_attr_ varchar2(4000);
real_parameter_attr_ varchar2(4000);
language_code_ varchar2(3) := client_sys.get_item_value('LANGUAGE_CODE',
attr_);
layout_name_ varchar2(4000) := client_sys.get_item_value('LAYOUT_NAME',
attr_);
report_param_ varchar2(4000) := client_sys.get_item_value('REPORT_PARAM',
attr_);
report_param_value_ varchar2(4000) := client_sys.get_item_value('REPORT_PARAM_VALUE',
attr_);
param_1_ varchar2(4000) := client_sys.get_item_value('PARAM_1',
attr_);
param_1_value_ varchar2(4000) := client_sys.get_item_value('PARAM_1_VALUE',
attr_);
param_2_ varchar2(4000) := client_sys.get_item_value('PARAM_2',
attr_);
param_2_value_ varchar2(4000) := client_sys.get_item_value('PARAM_2_VALUE',
attr_);
param_3_ varchar2(4000) := client_sys.get_item_value('PARAM_3',
attr_);
param_3_value_ varchar2(4000) := client_sys.get_item_value('PARAM_3_VALUE',
attr_);
param_4_ varchar2(4000) := client_sys.get_item_value('PARAM_4',
attr_);
param_4_value_ varchar2(4000) := client_sys.get_item_value('PARAM_4_VALUE',
attr_);
param_5_ varchar2(4000) := client_sys.get_item_value('PARAM_5',
attr_);
param_5_value_ varchar2(4000) := client_sys.get_item_value('PARAM_5_VALUE',
attr_);
param_6_ varchar2(4000) := client_sys.get_item_value('PARAM_6',
attr_);
param_6_value_ varchar2(4000) := client_sys.get_item_value('PARAM_6_VALUE',
attr_);
param_7_ varchar2(4000) := client_sys.get_item_value('PARAM_7',
attr_);
param_7_value_ varchar2(4000) := client_sys.get_item_value('PARAM_7_VALUE',
attr_);
param_8_ varchar2(4000) := client_sys.get_item_value('PARAM_8',
attr_);
param_8_value_ varchar2(4000) := client_sys.get_item_value('PARAM_8_VALUE',
attr_);
param_9_ varchar2(4000) := client_sys.get_item_value('PARAM_9',
attr_);
param_9_value_ varchar2(4000) := client_sys.get_item_value('PARAM_9_VALUE',
attr_);
param_10_ varchar2(4000) := client_sys.get_item_value('PARAM_10',
attr_);
param_10_value_ varchar2(4000) := client_sys.get_item_value('PARAM_10_VALUE',
attr_);
param_11_ varchar2(4000) := client_sys.get_item_value('PARAM_11',
attr_);
param_11_value_ varchar2(4000) := client_sys.get_item_value('PARAM_11_VALUE',
attr_);
param_12_ varchar2(4000) := client_sys.get_item_value('PARAM_12',
attr_);
param_12_value_ varchar2(4000) := client_sys.get_item_value('PARAM_12_VALUE',
attr_);
param_13_ varchar2(4000) := client_sys.get_item_value('PARAM_13',
attr_);
param_13_value_ varchar2(4000) := client_sys.get_item_value('PARAM_13_VALUE',
attr_);
param_14_ varchar2(4000) := client_sys.get_item_value('PARAM_14',
attr_);
param_14_value_ varchar2(4000) := client_sys.get_item_value('PARAM_14_VALUE',
attr_);
param_15_ varchar2(4000) := client_sys.get_item_value('PARAM_15',
attr_);
param_15_value_ varchar2(4000) := client_sys.get_item_value('PARAM_15_VALUE',
attr_);
param_16_ varchar2(4000) := client_sys.get_item_value('PARAM_16',
attr_);
param_16_value_ varchar2(4000) := client_sys.get_item_value('PARAM_16_VALUE',
attr_);
param_17_ varchar2(4000) := client_sys.get_item_value('PARAM_17',
attr_);
param_17_value_ varchar2(4000) := client_sys.get_item_value('PARAM_17_VALUE',
attr_);
param_18_ varchar2(4000) := client_sys.get_item_value('PARAM_18',
attr_);
param_18_value_ varchar2(4000) := client_sys.get_item_value('PARAM_18_VALUE',
attr_);
param_19_ varchar2(4000) := client_sys.get_item_value('PARAM_19',
attr_);
param_19_value_ varchar2(4000) := client_sys.get_item_value('PARAM_19_VALUE',
attr_);
param_20_ varchar2(4000) := client_sys.get_item_value('PARAM_20',
attr_);
param_20_value_ varchar2(4000) := client_sys.get_item_value('PARAM_20_VALUE',
attr_);
begin
-- Create Report Attr
client_sys.clear_attr(real_report_attr_);
if report_param_ is null or report_param_value_ is null then
RETURN;
END IF;
client_sys.add_to_attr(report_param_,
report_param_value_,
real_report_attr_);
client_sys.clear_attr(real_parameter_attr_);
IF param_1_ is not null then
client_sys.add_to_attr(param_1_,
nvl(param_1_value_, ''),
real_parameter_attr_);
end if;
IF param_2_ is not null then
client_sys.add_to_attr(param_2_,
nvl(param_2_value_, ''),
real_parameter_attr_);
end if;
IF param_3_ is not null then
client_sys.add_to_attr(param_3_,
nvl(param_3_value_, ''),
real_parameter_attr_);
end if;
IF param_4_ is not null then
client_sys.add_to_attr(param_4_,
nvl(param_4_value_, ''),
real_parameter_attr_);
end if;
IF param_5_ is not null then
client_sys.add_to_attr(param_5_,
nvl(param_5_value_, ''),
real_parameter_attr_);
end if;
if param_6_ is not null then
client_sys.add_to_attr(param_6_,
nvl(param_6_value_, ''),
real_parameter_attr_);
end if;
IF param_7_ is not null then
client_sys.add_to_attr(param_7_,
nvl(param_7_value_, ''),
real_parameter_attr_);
end if;
IF param_8_ is not null then
client_sys.add_to_attr(param_8_,
nvl(param_8_value_, ''),
real_parameter_attr_);
end if;
IF param_9_ is not null then
client_sys.add_to_attr(param_9_,
nvl(param_9_value_, ''),
real_parameter_attr_);
end if;
IF param_10_ is not null then
client_sys.add_to_attr(param_10_,
nvl(param_10_value_, ''),
real_parameter_attr_);
end if;
IF param_11_ is not null then
client_sys.add_to_attr(param_11_,
nvl(param_11_value_, ''),
real_parameter_attr_);
end if;
IF param_12_ is not null then
client_sys.add_to_attr(param_12_,
nvl(param_12_value_, ''),
real_parameter_attr_);
end if;
IF param_13_ is not null then
client_sys.add_to_attr(param_13_,
nvl(param_13_value_, ''),
real_parameter_attr_);
end if;
IF param_14_ is not null then
client_sys.add_to_attr(param_14_,
nvl(param_14_value_, ''),
real_parameter_attr_);
end if;
IF param_15_ is not null then
client_sys.add_to_attr(param_15_,
nvl(param_15_value_, ''),
real_parameter_attr_);
end if;
IF param_16_ is not null then
client_sys.add_to_attr(param_16_,
nvl(param_16_value_, ''),
real_parameter_attr_);
end if;
IF param_17_ is not null then
client_sys.add_to_attr(param_17_,
nvl(param_17_value_, ''),
real_parameter_attr_);
end if;
IF param_18_ is not null then
client_sys.add_to_attr(param_18_,
nvl(param_18_value_, ''),
real_parameter_attr_);
end if;
IF param_19_ is not null then
client_sys.add_to_attr(param_19_,
nvl(param_19_value_, ''),
real_parameter_attr_);
end if;
IF param_20_ is not null then
client_sys.add_to_attr(param_20_,
nvl(param_20_value_, ''),
real_parameter_attr_);
end if;
Archive_API.New_Instance(result_key_,
real_report_attr_,
real_parameter_attr_);
Client_SYS.Clear_Attr(instance_attr_);
Client_SYS.Clear_Attr(real_parameter_attr_);
Archive_API.Get_Info(instance_attr_, real_parameter_attr_, result_key_);
Client_SYS.Clear_Attr(job_attr_);
Client_SYS.Clear_Attr(job_contents_attr_);
Client_SYS.Add_To_Attr('RESULT_KEY', result_key_, job_contents_attr_);
Client_SYS.Add_To_Attr('LANG_CODE', language_code_, job_contents_attr_);
Client_SYS.Add_To_Attr('LAYOUT_NAME', layout_name_, job_contents_attr_);
Print_Job_API.New_Print_Job(print_job_id_,
job_attr_,
job_contents_attr_);
Print_Job_API.Print(print_job_id_);
print_job_id_out_ := print_job_id_;
result_key_out_ := result_key_;
END report_new_instance_and_print;