Thursday, January 24, 2008

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!

96 comments:

Ike Wiggins said...

Gareth,

I've had to do exactly what your saying here, a long time ago, before the add_layout and default format templates features were added....

Did you try using FND_REQUEST.ADD_LAYOUT?

I would try puting it in afterParameterForm or beforeReport Trigger. The advantage being you won't need an extra concurrent request I think.

Let me know if you decide to try it or not...

Ike Wiggins
http://bipublisher.blogspot.com

Gareth said...

Hi Ike,
Yup, tried that, didn't work for some reason - I might go back and double check. The extra concurrent request option is a more generic solution and can be reused.
Gareth

prabu said...

Hi Gareth,

I have tried all the steps in this post but the XML Report Publisher Request is not running automatically once the Statement Generator Program Successfully Completed,Even there is no error in the (ARXSGP).

please let me know how to resolve this issue?

Regards
Prabu

Gareth said...

Hi Prabu,

If you have modified the report correctly, in the log file of the Statement Request you should see a 20002 message either saying the request was submitted or failed.

Drop me an email if you're still having problems.

Gareth

prabu said...

Hi Gareth,

I sent my log file to your mail id.
I could not find 20002 this code in the log file.

could you give reply for my mail.

Prabu

Ike Wiggins said...

Prabu,

I used do what gareth suggest in this post when xml publisher 4.5-5.0 was released because it wouldn't default the template for you automatically like it does today in 5.6.3

I can attest to the fact that the solution he has presented should work.

The lack of an error to me means three things:

Make sure you are submitting the bi publisher request in your beforeReport trigger.

In the beforeReport Trigger make sure you submit the concurrent request "before" the return statement.

Verfiy the bi publisher config.

prabu said...

Hi Gareth,

I written the correct code in the after report trigger in the ARXSGPO.rdf but still the XML Report Publisher is not starting automatically once the ARXSGP request completed succefully.I sent the RDF to you for more clarification.

Give me update and your advice!...


prabu

Saravanan said...

Hi,

Thank you for your post, it was very useful.

Is there any way, we can avoid changing the oracle standard RDF report ARXSGPO.rdf.

I tried copying the rdf ARXSGPO.rdf to customer XXARXSGPO.rdf, but it has a parameter for parent concurrent request. Could you pleae help!!

Gareth said...

Hi Saravanan,
Easiest is to copy AR_TOP/reports/US/ARXSGPO.rdf to XX_TOP/reports/US/XXARXSGPO.rdf and modify as required. Then move ARXSGPO.rdf and create softlink from AR_TOP/reports/US/ARXSGPO.rdf to XX_TOP/reports/US/XXARXSGPO.rdf

Regards,
Gareth

Saravanan said...

When i tried to open the file ARXSGPO.rdf in report builder 6i, getting warning message: "opening a report saved with a newer version of report builder functionality may be lost "

I did the changes in ARXSGPO.rdf file in report builder 6i, after saving the file size has reduced by 50%.

Do i need to use report builder 10g ?

Gareth said...

Hi Saravanan,

I guess you're Release 11i in which case Reports 6i is fine, should be patched to same level of Developer 6i on the server, but shouldn't be a problem - just make sure you test it!

Regards,
Gareth

Saravanan said...

Thanks Gareth,

Iam getting the error
Oracle XML Publisher 5.6.3
java.lang.NumberFormatException: For input string: "ARXSGP"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:447)
at java.lang.Integer.parseInt(Integer.java:497)

Please help

regards
saravanan

Gareth said...

Hi Saravanan,

Make sure all your numbers are actually numbers and do not contain character strings.

Regards,
Gareth

Saravanan said...

Hi Gareth,

Do i need to create a template with this name 'ARXSGP'.

The "Statement Generation Program" completes successfully, but in the diagnostic button - View XML button is disabled. How i can get the xml for the AR statement to create a template ?

Thank you

regards
saravanan

Gareth said...

Change output of ARXSGP and ARXSGPO to XML. Create the template.

snbollineni said...

Thanks,


Very helpful

Tomek & Renata said...

This is great help, thanks.
I have somehow related question - Is there a way to schedule the AR Statement process to run weekly?

Gareth said...

Hi Tomek & Renata,

You should be able to schedule statements - its just a concurrent request. It doesn't have a control table like GL Journal Import, so go ahead and schedule - possibly set your concurrent requests "Hold" profile option on, then Submit statements, then go to your on hold concurrent request, click the Schedule button - set as you require including parameter increments, and then release the Hold (and profile option). If you're having trouble with parameters needing to be updated consider Request Set or PL/SQL fnd_request.submit_request program.

Gareth

tao said...

How does the concurrent request id :p_conc_request_id populated? Is it populated automatically or I have to get it some where?
Thank you very much for your help.

Gareth said...

Hi Tao,

Standard apps reports populate parameter p_conc_request_id as part of user exit FND SRWINIT, since we are modifying a standard report here, it will be populated.

Regards,
Gareth

tao said...

Thank you Gareth.
The implementation in my current system is using a table trigger to sumbit XML publisher concurrent program on FND_CONCURRENT_REQUESTS table instead of using after report trigger. The solution is working. But I am wondering if this table triiger solution has negative impact to our system. I am planing to modify to after report trigger. But I want to make sure the benefit is worthing the effort and time. How are your options? Thank you.

Gareth said...

Hi Tao,

Ideally you should be using the Output and Format on Request Submission to link the request to its BI Publisher RTF template, but the problem of course with spawned reports such as Statements and Dunning Letters is that you don't get the chance to specify the output format and hence RTF template.
After report trigger is a much better idea than db trigger on fnd_concurrent_requests. I remember reading somewhere in Applications Developers guide or similar that you're not supposed to put triggers on FND_CONCURRENT_REQUESTS. However as long as you are 100% sure of your code and are okay with giving up your support in that area, go for it! Concurrent processing is a pretty major area though!

Regards,
Gareth

tao said...

Thank you very much Gareth

Maria Imelda said...

in R12 RUP6 there's no need to customize this. it is now in the setup for defining template, there's a default output that you have to provide.

fyi.

Gareth said...

Hi Maria,

Yes, this post is specific to 11i.
I'll update the content to note this.

Regards,
Gareth

Paul Sheats said...

Hi,

I have a need to either print or email statements. Will this solution work in combination with uploading a burster control file on the data definition? I guess what I'm asking is does XDOREPPB handle bursting?

I tried the solution in Metalink, changing the ARXSUPST.pll to add the layout, and also adding the submit request in after report trigger, but now am getting an error in the statement generation program, "Unable to find an Output Post Processor service to post-process request..."

I can get it to work for printing, but it doesn't seem that XDOREPPB is looking at my control file for emailing.

Any ideas?

Thanks.
Paul

Gareth said...

Hi Paul,
If bursting doesn't fire call bursting program after the Report Publisher call. See this post for code:
http://garethroberts.blogspot.com/2008/03/bi-publisher-ebs-bursting-101.html

Regards,
Gareth

Paul Sheats said...

Gareth,

Thanks, for the quick reply! That worked! Now I just have to figure out why I'm still getting that error about the OPP. I had our DBA stop and restart it, but that didn't help.

Paul

DubyaJay said...

Hello,

Has anyone gotten this message?

Oracle XML Publisher 5.6.3
Updating request description
Waiting for XML request
Retrieving XML request information
Preparing parameters
Process template
--XDOException
java.sql.SQLException: No corresponding LOB data found :SELECT L.FILE_DATA FILE_DATA,DBMS_LOB.GETLENGTH(L.FILE_DATA) FILE_LENGTH, L.LANGUAGE LANGUAGE, L.TERRITORY TERRITORY, B.DEFAULT_LANGUAGE DEFAULT_LANGUAGE, B.DEFAULT_TERRITORY DEFAULT_TERRITORY,B.TEMPLATE_TYPE_CODE TEMPLATE_TYPE_CODE, B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.START_DATE START_DATE, B.END_DATE END_DATE, B.TEMPLATE_STATUS TEMPLATE_STATUS, B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.DS_APP_SHORT_NAME DS_APP_SHORT_NAME, B.DATA_SOURCE_CODE DATA_SOURCE_CODE, L.LOB_TYPE LOB_TYPE FROM XDO_LOBS L, XDO_TEMPLATES_B B WHERE L.APPLICATION_SHORT_NAME= :1 AND L.LOB_CODE = :2 AND L.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME AND L.LOB_CODE = B.TEMPLATE_CODE AND (L.LOB_TYPE = 'TEMPLATE' OR L.LOB_TYPE = 'MLS_TEMPLATE') AND ( (L.LANGUAGE = :3 AND L.TERRITORY = :4) OR (L.LANGUAGE = :5 AND L.TERRITORY = :6) OR (L.LANGUAGE= B.DEFAULT_LANGUAGE AND L.TERRITORY= B.DEFAULT_TERRITORY ))
at oracle.apps.xdo.oa.schema.server.TemplateInputStream.initStream(TemplateInputStream.java:402)
at oracle.apps.xdo.oa.schema.server.TemplateInputStream.(TemplateInputStream.java:235)
at oracle.apps.xdo.oa.schema.server.TemplateHelper.getTemplateFile(TemplateHelper.java:1159)
at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3430)
at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3527)
at oracle.apps.xdo.oa.cp.JCP4XMLPublisher.runProgram(JCP4XMLPublisher.java:683)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)

Gareth said...

Hi DubyaJay,

Yes I've seen that before - manually reupload your template file.

Gareth

DubyaJay said...

Thanks that worked and I got a Dunning Print to work with XML Publisher using your example. My client and myself thank you for the post.

Along the same topic... I'm trying to get the invoices associated with dunning (RAXINV_NON_SRS) when invoices box is checked on dunning letter set config to print using an existing XMLP invoice (RAXINV_SEL) template. I created a new data definition, template, and attached RTF file. But I get no output message but the View XML under Diagnostics button shows XML data. I've quadruple checked my XMLP setup and don't see anything wrong. The layout option shows the proper XMLP template when submitting manually.

Has anyone else gotten this to work?

Gareth said...

Hi DubyaJay,
Good stuff. Re the invoices the key thing you mentioned is NON-SRS. If the concurrent request report is spawned from within another request, then the template probably won't get picked up and you need to apply a fix similar to what this post presents - either adding cdoe to add layout to the source report, or by triggering the XML Report Publisher request to run over the XML generated by the base concurrent request.

Regards,
Gareth

iamchandru said...

Hi Gareth,

I tried everything and it worked so well, but i want to do the sorting on one of the STATEMENT_DATE xml tag, but its coming as char datatype, but we need to do sorting on date field. so i tried to convert the datatype, but in vain.
do u have any clues, the problem statement is here
http://forums.oracle.com/forums/thread.jspa?threadID=898469

thx for ur help

Gareth said...

Answer for sorting by date format DD-MON-YY is on ForumsRegards,
Gareth

Trina said...

Hi Gareth

I managed to apply email bursting to the AR Statements by executing in the AFTERreport trigger of the ARSXGPO.rdf

-- Report Publisher
xmlp_pkg.submit_request_xmlp
and
-- Report Publisher Bursting
xmlp_burst_pkg.submit_request_burst

However, the attachment is not splitting up per customer when we execute a monthly statment for all customers. The entire statement is emailed to the first customer.

Any thoughts?
Thanks for a great tutorial.

Gareth said...

Hi Trina,

Double check the XML and make sure the structure hasn't change to make your XPATH in your control file incorrect.

Regards,
Gareth

Trina said...

Hi Gareth

Thanks, that worked.
Specified /ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/LIST_G_STATEMENT/G_STATEMENT in the XPATH

Bob Harrison said...

One or more post-processing actions failed. Consult the OPP service log for details.


did this - says thee was nothing in the out dir for this concurrent request.

Gareth said...

Hi Bob,
Make sure you've set the output to XML on concurrent program definition for Statement Generate/Print programs.
Regards,
Gareth

Bob Harrison said...

Thanks for the reply.
I did find a typo in the code, so yes it could find no output for that code.

Now I am getting
Caused by: oracle.xdo.parser.v2.XMLParseException: Start of root element expected.

Is this a problem with the XML or the template ?

Gareth said...

Hi Bob,
Make sure your XML file is valid - XML header present.
Regards,
Gareth

Shiva said...

I am referring to your post on AR Statements, which is very clear for printing the output in xml without having submitted twice.

I am working at a client, which has branches across the globe and their header logo on the statements change based on the operating unit. "ARXSGP" concurrent program though has operating unit as one of the parameters but not sure how can I call that as parameter in the template definition and I see that there are no parameters on the "Print Statements", which is a report (rdf) ARXSGPO.

Can you advise as how can I call the operating unit parameter in the template definition.

Any help is appreciated.

Thanks
Ram

Gareth said...

Hi Ram,

If its not there already you could put the org id or organization name into the data itself by adding field to ARXSGPO.rdf and use that.

In general to get parameter values into the output you put the following at the beginning in the RTF template e.g. if you have parameter P_ORG_ID:
< ?param@begin:P_ORG_ID? >
and then you can display/reference via:
< ?$P_ORG_ID? >

Regards,
Gareth

Shiva said...

Hi Gareth,

Thanks for the quick response. I already tried that but whatever the change I do, it is giving me the error, which says, "ORA-24323: value not allowed", not able make changes in any of the groups. Can you advise?

Thanks
Shiva.

swhite44 said...

Hi Gareth,

I'm trying to devise a way of running XML Publisher against the Dunning output without customizing a program.

Defined a Request Set to run Dunning letter generate, which spawns Print Dunning automatically. Then XML Pub has to process that output.

I guess I could select the max request_id for a Print Dunning, and pass that into XML Pub as a parameter, but would be nicer to get eth actual request ID of the first step of the request set and then find the child request_id for the Print and pass THAT into XML Publisher.

But is passing request set parameters worked out during execution possible? Or are the parameter values all worked out ON SUBMISSION?

thanks,
Steve
NL

Gareth said...

Hi Steve,

You're on the right track, just give your ideas a go - as long as you set incompatibility on your custom program, selecting max request id of Dunning will work fine.

Regards,
Gareth

swhite44 said...

Wow that was quick Gareth, thanks.

I guess I'll crack it today, how to pass the request_id of the first request set stage to the next stage.

Or else yeah like you say, set dunning incompatible to itself and then select max(print dunning req_id) for the current org_id - a wee bit dirty though if it happens to format someone else's dunning output. And I'm not sure if the (select max) would be done when the whole request set is called, or at the start of the stage.
We'll see..

Omprakash said...

Hi
THanks for your blog,
Why I am getting folowing error
Please let me know

XML/BI Publisher Version : 5.6.3
Updating request description
Retrieving XML request information
Preparing parameters
--Exception
null
java.lang.NullPointerException
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getControlFile(JCP4XDOBurstingEngine.java:401)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:237)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)

Omprakash said...

Why I am getting this error
Please let me know
Thanks
Omp

XML/BI Publisher Version : 5.6.3
Updating request description
Retrieving XML request information
Preparing parameters
--Exception
null
java.lang.NullPointerException
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getControlFile(JCP4XDOBurstingEngine.java:401)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:237)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)

Gareth said...

Hi Omprakash,

Please make sure the syntax of your bursting control file is correct, and you have uploaded it successfully to the Data Definition.

Regards,
Gareth

swhite44 said...

Hi Gareth,
the users had a request that I think is impossible, can you confirm please?

To email the dunning letters instead of printing and posting them. Each customer can have an email address, but i can't see how the report output could be split up and each letter emailed to a different customer, can you?

Gareth said...

Hi swhite44,
Definitely possible. If the XML doesn't suit your purposes and assuming you're 11i you could modify ARDLP.rdf to match requirements. Send me an email with sample standard XML if you want to confirm. ggr 888 [at] gmail [d0t] com.

Regards,
Gareth

handedongel said...

Hi Gareth,
Thanks for your post. I tried it for dunning letter and it works properly.

chesna said...

I have a small requirement on AR Statement Report of BI Publisher:

I developed the template (RTF) file according to the requirement but i am not able to get the output. I have checked the following programs also
a) Print Statements of type RDF
b) Statement Generation Program of type spwaned
c) AR Customer Balance Statement Letter of type Java Concurrent Program

Please let me know the process in order to get the following:

1. I need to print in PDF format
2. I need to add few attribute values also

In which report i have to modify or do changes....

Gareth said...

Hi Chesna,

If you are using 11i you can follow the above steps in this blog post.

Regards,
Gareth

chesna said...

No, Its not 11i, It is R12

Raj said...

create or replace package body XX01_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 = 'XX01_WMSLPNCONTENT' then
l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','XML Report Publisher',NULL,FALSE,
p_request_id,
20003, -- XX01
'XX01_WMSLPNCONTENT_EN',
'en', -- English
'N','RTF','PDF');

end if;
return l_req_id;
end submit_request_xmlp;

end XX01_XMLP_PKG;
/
Call to submit_request_xmlp is returning 0 because it fails to execute call to submit_request_xmlp.

Can anyone point me what's wrong in this code ? Appreciate your kindness in advance.

Gareth said...

Hi Raj,
Make sure that your apps session is initialized via fnd_global.apps_initialize and the XML Report Publisher program is assigned to the request group on your responsibility.

PS. Not related to the request submission issue, you might need to put a territory on your template ie. 'en' to 'en-US' or maybe 'en-??'.

Regards,
Gareth

Ramesh said...

Hi Gareth ,
Could you please let me know what needs to be done in R12.

Gareth said...

Hi Ramesh,

You can use the same methods for R12.

Regards,
Gareth

Nagaraj said...

Hi Gareth,

Thanks for this blog. It is a real nice one.

Nagaraj.s

Swapnali said...

Hi Gareth,
I followed the steps you mentioned and its running fine. But I want to send the statements to the respective customers.
Please help.

Swapnali

Gareth said...

Hi Swapnali,
Read my other post on Bursting 101.

Regards,
Gareth

Swapnali said...

Gareth,

Can you give me few steps to start with. I am new to xml publisher and i am not understanding how to apply bursting to AR Statement.
Presently I have seeded R12 AR statement programs with customisation of ARXSGPO.rdf to run XML Report publisher in AFter Report Trigger.

Thanks, Swapnali

Gareth said...

Hi Swapnali.

Just give it a try! http://garethroberts.blogspot.com/2008/03/bi-publisher-ebs-bursting-101.html

Regards,
Gareth

Michael said...

It looks like there is a mismatch between application_id and short_name in the submit_request:

l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
p_request_id,
/* the next two lines should be switched...*/ 222, -- Receivables
'ARXSGP', -- Statement Generate

'en-US', -- English
'N','RTF','PDF');

Gareth said...

Hi Michael,

I'm not seeing that order in R11i parameters although there may have been a change depending on your version. However, in Release 12 there is an extra parameter "Dummy for Data Security" on concurrent program XML Report Publisher (XDOREPPB) that you'll need to take into account.

Thanks for the comment.

Regards,
Gareth

defdames said...

Gareth when you mean"Manually upload your template" do you mean through XML administrator on through FTP?

Thanks, I just can't get rid of the LOB file not found error.

Gareth said...

I mean through XML Publisher Administrator as opposed to using XDOLOAD.

Regards,
Gareth

seanmark said...

Hi Gareth,

I'm currently working on running the XML report publisher after the statement generation program in R12.
Below is the code I've added on the after report trigger.
function AfterReport return boolean is
l_req_id NUMBER;
--xml_layout Boolean ;
l_template_id NUMBER;
begin

SELECT template_id
INTO l_template_id
FROM XDO_TEMPLATES_B
WHERE template_code = 'ARXSGP';
--xml_layout := FND_REQUEST.ADD_LAYOUT('AR','ARXSGP','en','US','PDF');

l_req_id := fnd_request.submit_request ('XDO'
,'XDOREPPB'
,NULL
,NULL
,FALSE
,:P_CONC_REQUEST_ID -- Request ID of statement program
,222 -- Receivables
,l_template_id -- Template ID
,'en-US' -- English
,'N'
,'RTF'
,'PDF'
);
if l_req_id > 0 then
srw.message(20002, 'Submitted request_id ' || l_req_id);
else
srw.message(20002, 'Failed to Submit Request');
end if;

SRW.USER_EXIT('FND SRWEXIT'); return (TRUE);
end;

but when I run the program I'm getting an error on the XML Report Publisher.

Oracle XML Publisher 5.6.3
Exception in thread "main" java.lang.NullPointerException
at oracle.apps.xdo.oa.cp.JCP4XMLPublisher.runProgram(JCP4XMLPublisher.java:231)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)


Can you please help me out.
Thanks.


Sean Mark

Gareth said...

Hi Sean Mark,

Try running the XML Report Publisher manually and see what the parameters are then adjust them in your code to match. Note I use the code rather than the ID of the template.

Regards,
Gareth

seanmark said...

Thanks for the quick response Gareth.

shyam said...

After doing the same steps Specifed by you getting below error

Oracle XML Publisher 5.6.3
java.lang.NumberFormatException: For input string: "ARXSGP"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:447)
at java.lang.Integer.parseInt(Integer.java:497)
at oracle.apps.xdo.oa.cp.JCP4XMLPublisher.runProgram(JCP4XMLPublisher.java:211)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

passed Request_id as to_char(Request_id)

Gareth said...

Hi Shyam,

You are quite obviously trying to use string "ARXSGP" in an assignment to a number variable. Please check the target variables and ensure you have matched them up correctly.

Regards,
Gareth

shyam said...

Hi Gareth,
Here is my code which is in after report -still i am facing same above error
function AfterReport return boolean is
v_req_id number := 0;

begin
--v_req_id := xxv8_xmlp_pkg.submit_request_xmlp('ARXSGP',:p_conc_request_id);

v_req_id := FND_REQUEST.SUBMIT_REQUEST( 'XDO', 'XDOREPPB',
'XML
Report Publisher', SYSDATE,
FALSE,
to_char(:p_conc_request_id),'ARXSGP', 222,'en-US',
'N',
CHR(0),'','','','','','','','','','','','','','',' ','','','',
'','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','');

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;

SRW.USER_EXIT('FND SRWEXIT'); return (TRUE);
end;

shyam said...

l_req_id :=FND_REQUEST.SUBMIT_REQUEST (
'XDO',
'XDOREPPB',
NULL,
NULL,
FALSE,
'N',:P_CONC_REQUEST_ID,'222','ARXSGP',
'en-US', 'N', 'RTF', 'PDF','', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '');

this code was working fine

Al Bundy said...

Hi Gareth,

Excellent Blog. Some people have mentioned that they were able to apply email bursting to the AR Statements. My question is - where do you get the customer email addresses from - it's not there in any xml tag currently generated by ARXSGPO. We are on 11i.

Thanks for your help,
Mo.

Gareth said...

Hi Al Bundy,
Since ARXSGPO is already modified, add another field if it is not coming through as you require it.
Regards,
Gareth

jill said...

In the package where you submit XDOREPPB, I have been told I need to wait for the concurrent program to complete befor submitting XDOREPPB. Problem is, it will not be completed in this code because it called the package. Is there something I should do to verify the xml data is available when I call XDOREPPB?

thanks - jill

Gareth said...

Hi Jill,
After report trigger of Oracle Reports should have spooled output I believe, so should not have to wait. To be 100% sure set incompatibility on AR Report to XDOREPPB.

Regards,
Gareth

Mohammadi said...

Hi Gareth,
I am trying to submit the bursting program on the afterreport trigger. Below is the code snippet used.

function AfterReport return boolean is

v_request_id NUMBER;
l_req_id NUMBER;
lay BOOLEAN;
v_errorcode NUMBER;
v_errortext VARCHAR2(500);

begin

--srw.do_sql('alter session set sql_trace = false');

SELECT fnd_global.CONC_REQUEST_ID
INTO v_request_id
FROM fnd_user,
v$instance
WHERE user_id = fnd_global.user_id;

srw.message(20002, 'Request to be bursted ' || v_request_id);

lay := apps.fnd_request.add_layout('XDO','BURST_STATUS_REPORT','en','US','PDF');


IF :P_TRANSMIT = 'Y' THEN
l_req_id:= FND_REQUEST.SUBMIT_REQUEST(application => 'XDO',
program => 'XDOBURSTREP',
description => '',
start_time => '',
sub_request => FALSE,
argument1 => v_request_id,
argument2 => 'Y',
argument3 => chr(0));
END IF;

if l_req_id=0 then
srw.message(100,'Failed to submit bursting program');
end if;

SRW.USER_EXIT('FND SRWEXIT');
return (TRUE);

EXCEPTION
WHEN OTHERS THEN
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);

--begin
--SRW.USER_EXIT('FND SRWEXIT');return (TRUE);
end;

The Bursting program is submitted by the parent report program. But this program is submitted with only one paramter as 'Y'. The parent program requeset id is not getting set in the parameter of bursting program and hence it fails. Following is the error mesage.

+---------------------------------------------------------------------------+
XML Publisher: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

XDOBURSTREP module: XML Publisher Report Bursting Program
+---------------------------------------------------------------------------+

Current system time is 28-FEB-2012 17:41:05

+---------------------------------------------------------------------------+

XML/BI Publisher Version : 5.6.3
--Exception
For input string: "Y"
java.lang.NumberFormatException: For input string: "Y"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:449)
at java.lang.Integer.parseInt(Integer.java:499)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:142)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)



Can you please help to understand why the parent request id doesn't get along in parameter of bursting program?

Thx
MT

Gareth said...

Hi MT,
For R12 recent versions there is an additional parameter. Not too sure who on AOL team at Oracle made decision to insert new parameter at beginning of list - not smart :-(

Change your code to:

l_req_id:= FND_REQUEST.SUBMIT_REQUEST(application => 'XDO',
program => 'XDOBURSTREP',
description => '',
start_time => '',
sub_request => FALSE,
argument1 => 'Y',
argument2 => v_request_id,
argument3 => 'Y',
argument4 => chr(0));
END IF;

Regards,
Gareth

Sourav said...

Thanks a lot! I broke my head on this for a long time. I was missing the new parameter

Unknown said...

Thanks Gareth.. I was able to complete the AR statements long back and it's running in production now since quite sometime.

I have new problem now, please can you help?

For Australia (our's is a global instance), XML version AR Statements runs for a very long and finally doesn't produce any output in XML, though, first program produces the output. This could be because of the size of the output, it is beyond 7 MB.

Is it that XML unable to convert our header logos and output into XML because of the size?

Statement Generation Program - completed normal and produces 7 MB plus file

XML Report Publisher - completes in error and doesn't produce the error and log files says, it is disconnecting from Oracle.

Please can you advise, if you have come across with such issue?

Thanks
Shiva K.

Gareth said...

Hi Shiva,
I don't think it would be because of the size of the file. 7Mb is not large.

What is the exact error message? Perhaps you could search on My Oracle Support (MOS) or log an SR.

Regards,
Gareth

Unknown said...

What is the exact error message?

It disconnects from Oracle (SQL) after few hours, that's what the program log file displays.

tomorrow, I can give the exact error from the log. We archive our logs and don't the exact error but running it in test now to replicate the problem.

Perhaps you could search on My Oracle Support (MOS) or log an SR.

I created SR and oracle said, it's a customization.

I am searching for the related issue in MOS.

Thanks for all the advise
Shiva.

Gareth said...

Are you running RAC? If so, it could be that there was a RAC node outage and the concurrent manager Output Post Processor has gone down - this causes XML Publisher program to stall for a few hours.
Try stopping concurrent managers, run cmclean.sql the restart managers - ensure Output Post Processor comes up (Sys Admin, Concurrent, Manager Administer) and they try again.

Regards,
Gareth

Ricey said...

Is it possible to have multiple print statement programs? I need to have to distinct XML statements, one with English text the other with Spanish . Its fine for Invoices but I have tried copying ARXSGP and ARXSGPO and am getting nowhere.

Thanks

Ricey

Gareth said...

Hi Ricey,
There are a number of ways to provide for multiple languages including:
- IF statement in RTF switched based on language of customer.
- Multiple templates each with different language
- Use of XLIFF

Regards,
Gareth

Andy Noble said...

Hi Gareth,
I have to find and change on a previously modified RTF the statement logo. Looking at the RTF I see a logo but when I run the AR Customer Balance Statement Letter the logo is not the same. I've found from tests that it is from a file called 1.jpg but I cannot understand how/where it is chosen. Can you help?
Thanks in advance
Andy

Andy Noble said...

Hi Gareth,
I have to find and change on a previously modified RTF the statement logo. Looking at the RTF I see a logo but when I run the AR Customer Balance Statement Letter the logo is not the same. I've found from tests that it is from a file called 1.jpg but I cannot understand how/where it is chosen. Can you help?
Thanks in advance
Andy

Gareth said...

Hi Andy,
Take a look at the "Format Picture" (properties) of the image and see if there is a URL in the "Alt Text" text box.

Regards,
Gareth

Andy Noble said...

Hi Gareth

Top drawer! Without even seeing my RTF you've correctly identified my problem. Without doubt you really are an expert - many thanks!

Cheers
Andy

Unknown said...

--Exception
null
java.lang.NullPointerException
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getControlFile(JCP4XDOBurstingEngine.java:428)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:240)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b

Unknown said...

Hi Gareth,

I have added the exact code mentioned above and also modified the ARXSGPO.rdf for the after report trigger, but the XML publisher request is not getting launched.

Also, I cannot see the messages in the rdf report in the log file.

Can you please help.