Beautiful Statements in 1 Easy Step - Automatically submit XML Report Publisher request for Oracle Receivables Statements output

Update: This post is specifically for Release 11i. New functionality in Release 12 implements BI Publisher in many of the 3rd party facing documents.

This post is specific to Receivables Statements, but equally applies to Dunning Letters as well. It requires you having setup your Data Definition and Template, in the case of Statements codes ARXSGP/ARXSGPO, and changed your output to XML on the concurrent program setup for Statements/Statement Print.

As per Note:337740.1 and Note:429283.1, and as per Enhancement Request 4461071, there is currently a limitation with Statements/Dunning Letters that you can't automatically get BI Publisher/XML Publisher output from the concurrent request. A couple of attempts have been made to add layouts, submit requests to the after report trigger in ARXSGPO.rdf, but can end up with either:

A reports compilation error when you don't pass all 100 parameters to fnd_request.submit_request:

unsupport construct or internal error [2601]

or the XML Report Publisher request completing with a warning, when all 100 parameters passed to fnd_request.submit_request:

One or more post-processing actions failed. Consult the OPP service log for details.
Output Post Processor log shows [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

with Output Post Processor log showin

 [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

So what can we do? Here's one solution, and this applies equally for any concurrent request producing XML where you don't have control over the template/layout.

Create a package:

create or replace package XXV8_XMLP_PKG AUTHID CURRENT_USER AS
  function submit_request_xmlp
  ( p_code in varchar2
  , p_request_id in number
  ) return number;
end XXV8_XMLP_PKG;
/

create or replace package body XXV8_XMLP_PKG AS
function submit_request_xmlp
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'ARXSGP' then
    l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
                                           p_request_id,
                                           222, -- Receivables
                                           'ARXSGP', -- Statement Generate
                                           'en-US', -- English
                                           'N','RTF','PDF');
  end if;
  return l_req_id;
end submit_request_xmlp;

end XXV8_XMLP_PKG;
/

Add the following to the after report trigger in ARXSGPO.rdf:

  declare
    v_req_id number := 0;
  begin
    v_req_id := xxv8_xmlp_pkg.submit_request_xmlp('ARXSGP',:p_conc_request_id);
    if v_req_id > 0 then
      srw.message(20002, 'Submitted request_id ' || v_req_id);
      commit;
    else
      srw.message(20002, 'Failed to submit request');
    end if;
  end;

Run Statements and fingers crossed you'll have beautiful output in one easy step!

Update: Fixed mismatch between code passed to package by after report trigger and the "if" statement code, they must match ARXSGP=ARXSGP!