How to submit XML Report using a PL/SQL Script

How to submit XML Report using a PL/SQL Script

Generally we use FND_REQUEST.SUBMIT_REQUEST to submit a concurrent program using a PL/SQL script.But we can not attach a layout to the concurrent request using the above said API.

We can attach a layout to the concurrent request by using another procedure
ADD_LAYOUT which belongs to the same package FND_REQUEST

Below is the signature of the procedure ADD_LAYOUT :-

/* Formatted on 11/14/2016 1:36:29 PM (QP5 v5.114.809.3010) */

fnd_request.add_layout (
template_appl_name  => 'Template Application',
template_code       => 'Template Code',
template_language   => 'en', --Use language from template definition
template_territory  => 'US', --Use territory from template definition
output_format       => 'PDF' --Use output format from template definition
);

Note:
                                              ADD_LAYOUT procedure should be called only when there is a layout associated to a concurrent program   Session context should be set using FND_GLOBAL.APPS_INITIALIZE’ before calling the ADD_LAYOUT procedure

Example:-

/* Formatted on 11/14/2016 1:34:57 PM (QP5 v5.114.809.3010) */
DECLARE
   --
   l_responsibility_id   NUMBER;
   l_application_id      NUMBER;
   l_user_id                NUMBER;
   l_request_id          NUMBER;
   l_layout                 NUMBER;
--
BEGIN
   --
   SELECT   DISTINCT fr.responsibility_id, frx.application_id
     INTO   l_responsibility_id, l_application_id
     FROM   apps.fnd_responsibility frx, apps.fnd_responsibility_tl fr
    WHERE   fr.responsibility_id = frx.responsibility_id
            AND LOWER (fr.responsibility_name) LIKE LOWER ('XXTest Resp');

   --
   SELECT   user_id
     INTO   l_user_id
     FROM   fnd_user
    WHERE   user_name = 'ELANGO';

   --
   --To set environment context.
   --
   apps.fnd_global.apps_initialize (l_user_id,
                                    l_responsibility_id,
                                    l_application_id);
   --
   --Setting Layout for the Request
   --
   l_layout :=
      apps.fnd_request.add_layout (template_appl_name   => 'XXCUST',
                                   template_code        => 'XXEMP',
                                   template_language    => 'en',
                                   template_territory   => 'US',
                                   output_format        => 'EXCEL');
   --
   --Submitting Concurrent Request
   --
   l_request_id :=
      fnd_request.submit_request (application   => 'XXCUST',
                                  program       => 'XXEMP',
                                  description   => 'XXTest Employee Details',
                                  start_time    => SYSDATE,
                                  sub_request   => FALSE,
                                  argument1     => 'Smith');
   --
   COMMIT;
   --
   IF l_request_id = 0
   THEN
      dbms.output.put_line ('Concurrent request failed to submit');
   ELSE
      DBMS_OUTPUT.put_line ('Successfully Submitted the Concurrent Request');
   END IF;
--
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'Error While Submitting Concurrent Request '
                           || TO_CHAR (SQLCODE)
                           || '-'
                           || SQLERRM);
END;
/