Concurrent Program Report to RTF query

SELECT fcpv.user_concurrent_program_name "USER PROG NAME",
                fcpv.concurrent_program_name "PROG NAME",
                fcpv.concurrent_program_id "PROGRAM ID",
                fcpv.creation_date "PROG CREATION DATE",
                fcpv.created_by "PROG CREATED BY",
                xddv.application_short_name "DD APP SHORT NAME",
                xddv.data_source_name,
                xtv.application_short_name "DT APP SHORT NAME",
                xtv.template_code,
                xtv.template_name,
                fe.executable_name,
                fe.execution_method_code,
                fe.execution_file_name ,
                xl.file_name,
                fcpv.enabled_flag, xl.last_update_date
  FROM  apps.fnd_concurrent_programs_vl fcpv,
               apps.fnd_executables fe,
               apps.xdo_ds_definitions_vl xddv,
               apps.xdo_templates_vl xtv,
               apps.xdo_lobs xl
 WHERE xddv.data_source_code = xtv.data_source_code
 AND      xddv.data_source_code = fcpv.concurrent_program_name
 AND fe.executable_id  = fcpv.executable_id
 AND   UPPER(template_name) like '%PACK%'
 AND   fcpv.enabled_flag = 'Y'
 AND fe.execution_method_code = 'P'
 AND  xl.lob_type in ('TEMPLATE','TEMPLATE_SOURCE')
AND    lower(xl.file_name) like '%.rtf'
AND  xtv.template_code=xl.lob_code

Source 

We're going to:

1. Create a Bursting Control File to email Suppliers
2. Upload the control file to the Data Definition
3. Test it out by calling the XML Publisher Report Bursting Program
4. (Optional) Extend the Report to automatically submit the Bursting program

At this point please make sure you have done the following EBS bursting prerequisite steps:

• (Optional, but highly recommended) Upgrade to 11.5.10.2 / XMLP 5.6.3 or higher (ATG RUP5 or higher is nice)
• Apply 5968876 XDO:EBS-BURSTING INTEGRATION PATCH
• Restarted your applications processes - or the button to upload your bursting control file won't appear!
• Set the Temporary Directory under XML Publisher Administrator, Administration, General - to e.g. /tmp, or you'll get error message:

Bursting Control File

Next, lets get into the Bursting control file and look at it a bit closer:
1. Create Bursting Control File to email Suppliers custom Separate Remittance Advice 

subject="Virtuate - Remittance Advice for Payment ${C_CHECK_NUMBER}">
Please find attached Remittance Advice for payment ${C_CHECK_NUMBER}.

Regards,
The Payables Team
Virtuate Limited

  

Hmm, what does all this jargon in the control file do? Well, here's a pretty picture that explains a lot of it: 




Make sure it all works ... gotta make sure its the right flavor!
Navigate into Payables, Submit Request, XML Publisher Report Bursting Program, and specify the request from your last custom Separate Remittance Advice request. 



Add code to after report trigger to automate bursting

declare
    v_req_id number := 0;
  begin
    if nvl(:p_burst,'N') = 'Y' then
      v_req_id := xxv8_xmlp_burst_pkg.submit_request_burst('XXV8_APXPBSRA',: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 if;
  end;

create or replace package body XXV8_XMLP_BURST_PKG AS
function submit_request_burst
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'XXV8_APXPBSRA' then
    l_req_id := fnd_request.submit_request('XDO','XDOBURSTREP',NULL,NULL,FALSE,
                                           p_request_id);
  end if;
  return l_req_id;
end submit_request_burst;

end XXV8_XMLP_BURST_PKG;
/

Generate XML from SQL

   FUNCTION  get_xml_data( p_sql_stmt IN VARCHAR2, p_row_set_tag IN VARCHAR2, p_row_tag IN VARCHAR2 )
   RETURN CLOB
   IS
      l_ctx    dbms_xmlgen.ctxHandle;
      l_xml    CLOB;
 
   BEGIN
 
 
      l_ctx := dbms_xmlgen.newContext(p_sql_stmt) ;
      dbms_xmlgen.setrowsettag(l_ctx, p_row_set_tag );
      dbms_xmlgen.setRowTag(l_ctx, p_row_tag );
      l_xml := dbms_xmlgen.getXML(l_ctx);
      DBMS_XMLGEN.closecontext ( l_ctx )  ;
 
      RETURN l_xml ;
 
   EXCEPTION
      WHEN OTHERS THEN
 
        RETURN NULL ;
     
   END  get_xml_data 

Read CLOB line by line

create table nrr_test_clob (c clob);

truncate table nrr_test_clob

insert INTO nrr_test_clob (c) values (
'<REPORT>
  <HEADER>
   <REPORT_NAME> WMS PICK TASK RELEASE</REPORT_NAME>
     <REQUEST_ID> 236560596</REQUEST_ID>
     <REQDATE> 02-MAR-2018 04:53:2525 PM</REQDATE>
     <REQUSER> RAYKARN</REQUSER>
     <FROM_DATE> 11-JAN-2018 01:00:0000 AM</FROM_DATE>
     <TO_DATE> 19-JAN-2018 09:00:0000 PM</TO_DATE>
     <FACILITY> WPC</FACILITY>
     <ORG> </ORG>
     </HEADER>
     <DATE_SUMMARY>
     <DATE_ROW>
     <COUNT_PREV>2447</COUNT_PREV>
     <COUNT_TODAY>0</COUNT_TODAY>
     <COUNT_TOMORROW>0</COUNT_TOMORROW>
     <COUNT_WEEK>644</COUNT_WEEK>
     <COUNT_MONTH>697</COUNT_MONTH>
     <COUNT_FUTURE>39</COUNT_FUTURE>
     </DATE_ROW>
     </DATE_SUMMARY>
     </REPORT>');
----------------------------------------------------------------------------------------------------------------------

select to_char(regexp_substr(test.c, '.+', 1, level)) pattern
from nrr_test_clob test
connect by level <= regexp_count(test.c, '.+');

----------------------------------------------------------------------------------------------------------------------


DECLARE

   l_clob   clob ;
   l_offset number := 1;
   l_amount number := 32767;
   l_length    number ;
   lc_buffer varchar2(32767);
   i   pls_integer := 1;
   idx pls_integer := 0;


   -- For UNIX type file - replace CHR(13) to NULL
   C_CR char := chr(13); -- carriage return
   --CR char := NULL;
   C_LF char := chr(10); --line feed
   
BEGIN


   SELECT c
   INTO   l_clob
   FROM   nrr_test_clob ;
 
   l_length := dbms_lob.getlength(l_clob);

   dbms_output.put_line('l_length => '||l_length) ;
 
   <<len_lbl1>>
   WHILE ( l_offset < l_length ) LOOP
 
      idx := idx + 1;
     
      dbms_output.put_line(idx||' l_offset => '||l_offset||' l_length => '||l_length) ;
 
      -- If no more newlines are found, read till end of CLOB
      IF (instr(l_clob, c_lf, l_offset) = 0) THEN
       
         l_amount := l_length - l_offset + 1;
   
      ELSE
   
         l_amount := instr(l_clob, c_lf, l_offset) - l_offset;
       
      END IF;
   
      dbms_output.put_line('l_amount => '||l_amount) ;
 
      -- This is to catch empty lines, otherwise we get a NULL error
      IF  ( l_amount = 0 ) THEN
   
         lc_buffer := '';
   
      ELSE
   
         dbms_lob.read(l_clob, l_amount, l_offset, lc_buffer);
   
      END IF;
   
      dbms_output.put_line('Line #'||i||':'||lc_buffer);


      -- This is to catch a newline on the last line with 0 characters behind it
   
   
      IF (instr(l_clob, c_lf, l_offset) = l_length) THEN
   
         lc_buffer := '';
   
         dbms_output.put_line('Line #'||idx||':'||lc_buffer);
   
      END IF;

      l_offset := l_offset + l_amount + 1;
 
   END LOOP len_lbl1 ;
 
 
END ; 

Parsing parent child XML structure using PL/SQL

Source


Doing some investigation about processing a XML stucture where we have a parent child type relationship where we want to get all the details for each child record and loop through them.
As part of looping through the child records we want the parent details to be available without the need to go back and read the XML again.

The following example displays each RaceLap tag and includes parent information. Including the case where there may be no child RaceLap entries.
The trick for me to understand was using the laptimes column extracted from the v_xml_example as an XMLTYPE as input into the second part of the FROM clause (laps) using the PASSING race.laptimes.


If there are any questions add a comment.


DECLARE

 v_xml_example                  SYS.xmltype := xmltype(

'<Races>
 <RaceResult>
  <Id>743845</Id>
  <PlateNo>420</PlateNo>
  <Completed_ind>N</Completed_ind>
  <Comments>DNF. No laps recorded</Comments>
 </RaceResult>
 <RaceResult>
  <Id>123145</Id>
  <PlateNo>233</PlateNo>
  <Completed_ind>Y</Completed_ind>
  <Comments>Finished after 3 laps</Comments>
  <RaceLap>
   <Lap>1</Lap>
   <Time>34.34</Time>
  </RaceLap>
  <RaceLap>
   <Lap>2</Lap>
   <Time>35.66</Time>
  </RaceLap>
  <RaceLap>
   <Lap>3</Lap>
   <Time>34.00</Time>
  </RaceLap>
 </RaceResult>
</Races>');


CURSOR c_race_laps IS
 SELECT race.id,
  race.plate_num,
  race.completed_ind,
  race.comments,
  laps.lap,
  laps.lap_time
 FROM    XMLTABLE('/Races/RaceResult'  -- XQuery string to get RaceResult tag
                PASSING v_xml_example
                COLUMNS
   id  VARCHAR2(100) PATH 'Id',
   plate_num NUMBER(10) PATH 'PlateNo',
   completed_ind VARCHAR2(1) PATH 'Completed_ind',
   comments VARCHAR2(100) PATH 'Comments',
   laptimes XMLTYPE  PATH 'RaceLap') race
  LEFT OUTER JOIN -- want parent with child nodes
  XMLTABLE('/RaceLap'  -- XQuery string to get RaceLap tag
                PASSING race.laptimes   -- the laptimes XMLTYPE output from the first xmltable containing the laptimes
                COLUMNS
                        lap  NUMBER  PATH 'Lap',
   lap_time NUMBER  PATH 'Time') laps
  ON (1 = 1); -- left outer join always join

BEGIN


 FOR v_race_laps_rec IN c_race_laps LOOP


  dbms_output.put_line('atr id:' || v_race_laps_rec.id ||
    ' plate_num:' || v_race_laps_rec.plate_num ||
    ' completed_ind:' || v_race_laps_rec.completed_ind ||
    ' Comments:' || v_race_laps_rec.comments ||
    ' Lap Number:' || v_race_laps_rec.lap ||
    ' Lap Time:' || v_race_laps_rec.lap_time);

 END LOOP;


END;

any concurrent request producing XML where you don't have control over the template/layout

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

Source


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;

XML from QUERY Directky

SELECT DBMS_XMLGEN.getxml(
'SELECT CURSOR(SELECT oha.order_number,
ola.ordered_item,
ola.ordered_quantity
FROM ont.oe_order_headers_all oha,
ont.oe_order_lines_all ola
WHERE oha.header_id = ola.header_id
and oha.order_number in (&order_number) order by ola.line_id) as order_detail,
CURSOR(SELECT ohd.name, ohs.hold_comment
FROM ont.oe_hold_sources_all ohs,
ont.oe_order_holds_all ohld,
ont.oe_hold_definitions ohd,
ont.oe_order_headers_all oha,
ont.oe_order_lines_all ola
WHERE oha.header_id = ola.header_id
AND ola.line_id = ohld.line_id
and ohld.hold_release_id is null
AND ohld.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND oha.order_number = &&order_number) as holds_detail
FROM DUAL')
FROM DUAL