Customer Interface Performance Issue

Customer Interface (RACUST) for a large number of records run extremely slow. Please do the followings, hopefully performance should improve.

1) Run "Gather Schema Statistics"/"Gather Table Statistics" program for AR from System Administrator Responsibility.
   > You can use fnd_stats/dbms_stats to gather schema/table statistics if you want to do it from back end.

2) Set HZ: Gather Table Stats profile value to 'Yes' at site level
   > Navigation: System Administrator > Profile > System
                       > query for profile 'HZ: Gather Table Stats'

3) Set the profile option 'HZ: Number of Workers Used by Customer Interface'  to a high value like 6 at the Site level.

4) Run "Customer Interface Master Conc Program" (RACUSTMA) instead of "Customers Interface".

New Customers/Customer changes not visible from application(R12)

Sometimes when you use open interface (Customer Interface) or HZ APIs to create or amend Customers, it is not visible from front end, though you can see the changes from back end.

DQM Synchronization program (DQM Serial Sync Index Program (DQM Serial Sync Index Program)) is submitted automatically when a user updates any customer information.

This program is for Synchronizing the party records from the HZ tables into the DQM indexed tables which is what you query against when you search in the application.

DQM indexed tables are:
HZ_PARTIES = HZ_STAGED_PARTIES
HZ_PARTY_SITES = HZ_STAGED_PARTY_SITES
HZ_CONTACTS = HZ_STAGED_CONTACTS
HZ_CONTACT_POINTS = HZ_STAGED_CONTACT_POINTS

In R12, the DQM Synchronization can be set to: Automatic/Batch/Disabled

> Navigation: Customers Online Data Librarian Super User/Trading Community Manager > Administration Tab > DQM tab > DQM Synchronization Method

> Further more, when one sync concurrent request is in pending status, no other sync concurrent request would even be submitted. If a party is created while a sync concurrent program is already running, the DQM Serial Sync Index Program was not run for the newly created party.

To overcome this, you can set the profile option at site level:
HZ: Ignore Concurrent Request Limits for DQM Synchronization
If it is set to yes,it will run the DQM Serial Sync Index Program irrespective of the other sync concurrent programs.

Ref: Metalink Note [ID 465993.1]

Even after above changes, if it does not run automatically,

1> Clear the Global Cache
  >> Navigation: Functional Administrator > Core Services > Caching Framework
                         > Global Configuration > Clear All Cache > Ignore warning message.

2> Please run the "DQM Synchronization Program" manually from "Trading Community Manager" responsibility.

Script to get profile option values at different levels

>> Please provide the User Profile Option Name as an Input.

------------------------
-----------
select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
        , fpot.description
        , fpo.start_date_active "Start Active Date"
        , fpo.end_date_active "End Date Active"
        , fpo.creation_date "Creation Date"
        , fu.user_name "Created By"
        , 'Site' "Level"
        , 'SITE' "Level Value"
        , fpov.profile_option_value "Profile Option Value"
    from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
   where fpot.user_profile_option_name like '%'||:profile_name||'%'
     and fpot.profile_option_name = fpo.profile_option_name
     and fpo.application_id = fpov.application_id
     and fpo.profile_option_id = fpov.profile_option_id
     and fpo.created_by = fu.user_id
     and fpot.language = userenv('Lang')
     and fpov.level_id = 10001 /* site level */
union all
   select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
        , fpot.description
        , fpo.start_date_active "Start Active Date"
        , fpo.end_date_active "End Date Active"
        , fpo.creation_date "Creation Date"
        , fu.user_name "Created By"
        , 'Appl' "Level"
        , fa.application_name "Level Value"
        , fpov.profile_option_value "Profile Option Value"
   from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
       , fnd_application_tl fa
  where fpot.user_profile_option_name like '%'||:profile_name||'%'
    and fpot.profile_option_name = fpo.profile_option_name
    and fpo.profile_option_id = fpov.profile_option_id
    and fpo.created_by = fu.user_id
    and fpot.language = userenv('Lang')
    and fpov.level_id = 10002 /* application level */
    and fpov.level_value = fa.application_id
union all
  select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
        , fpot.description
        , fpo.start_date_active "Start Active Date"
        , fpo.end_date_active "End Date Active"
        , fpo.creation_date "Creation Date"
        , fu.user_name "Created By"
        , 'Resp' "Level"
        , frt.responsibility_name "Level Value"
        , fpov.profile_option_value "Profile Option Value"
   from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
       , fnd_responsibility_tl frt
  where fpot.user_profile_option_name like '%'||:profile_name||'%'
    and fpot.profile_option_name = fpo.profile_option_name
    and fpo.profile_option_id = fpov.profile_option_id
    and fpo.created_by = fu.user_id
    and frt.language = userenv('Lang')
    and fpot.language = userenv('Lang')
    and fpov.level_id = 10003 /*responsibility level */
    and fpov.level_value = frt.responsibility_id
    and fpov.level_value_application_id = frt.application_id
union all
select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
       , fpot.description
       , fpo.start_date_active "Start Active Date"
       , fpo.end_date_active "End Date Active"
       , fpo.creation_date "Creation Date"
       , fu.user_name "Created By"
       , 'User' "Level"
       , fu2.user_name "Level Value"
       , fpov.profile_option_value "Profile Option Value"
   from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
       , fnd_user fu2
  where fpot.user_profile_option_name like '%'||:profile_name||'%'
    and fpot.profile_option_name = fpo.profile_option_name
    and fpo.profile_option_id = fpov.profile_option_id
    and fpo.created_by = fu.user_id
    and fpov.level_id = 10004 /* user level */
    and fpov.level_value = fu2.user_id
    and fpot.language = userenv('Lang')
order by  "User Profile Option Name", "Level", "Level Value"

Script to set profile value at different levels

 Set profile value at SITE Level

declare 
   v_check            boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
   --exception
   --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'SITE'              
                                         , x_level_value              => null              
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

 Set profile value at Application Level

declare 
   v_check           boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_appl_name    varchar2(4)   := 'AR';
   v_appl_id         number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
   select application_id
     into v_appl_id
     from fnd_application
    where application_short_name = 'AR';
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'APPL'               
                                         , x_level_value              => v_appl_id              
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

 Set profile value at Responsibility Level

declare 
   v_check            boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_resp_name    varchar2(240)   := 'Purchasing Super User';
   v_resp_id         number;
   v_resp_app_id number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
    select responsibility_id      
         , application_id   
      into v_resp_id      
         , v_resp_app_id   
    from fnd_responsibility_tl  
    where responsibility_name = v_resp_name ;
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'RESP'               
                                         , x_level_value              => v_resp_id             
                                         , x_level_value_app_id   => v_resp_app_id) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;



 Set profile value at User Level

declare 
   v_check           boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_user_name    varchar2(240)   := 'XX1234';
   v_user_id         number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
    select user_id       
      into v_user_id       
      from fnd_user  
     where user_name = v_user_name ;
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'USER'               
                                         , x_level_value              => v_user_id             
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

Script To Delete Transaction(s)

Prerequisites :
 Check if 'Allow Transaction Deletion' flag is Yes, if no, check the flag for respective operating unit.
 Navigation : REceivables -> Setup -> System -> System Options
              >> Query for respective operating unit > Click on 'Trans and Customers' > Check for 'Allow Transaction Deletion' flag.
-------------------------------------------------------------------------------------------------------------
declare
   
    cursor cur_all_trx
    is            
    select to_number(hz.party_number) party_number,rct.org_id,rct.customer_trx_id.rct.trx_number
      from ra_customer_trx_all     rct
          ,hz_cust_accounts ha
          ,hz_parties hz
     where trx_number = 'I0061887'
       and ha.account_number = rct.interface_header_attribute4
       and hz.party_id = ha.party_id;
   
    v_msg_data      varchar2(4000)  := null;
    v_msg_count     number          := 0;
    v_msg_index     number          := 0;
    v_ret_status    varchar2(1)     := null;   
    v_message_tbl   arp_trx_validate.message_tbl_type;
    v_res           NUMBER := 1234 ;
    v_res_name      varchar2(240)  ;
    v_app           NUMBER := 5678 ;
    v_user          number := 9101 ;
begin
    dbms_output.put_line('Detele Transaction...');
   
    for c_rec in cur_all_trx loop
        dbms_output.put_line('   Transaction No.: '||c_rec.trx_number);
        dbms_output.put_line('   Transaction ID : '||c_rec.customer_trx_id);
        dbms_output.put_line('   Org ID         : '||c_rec.org_id);
        ----------------------------------------------------------------------------
        ---- Setting the org context for the particular session
        apps.mo_global.set_policy_context('S', c_rec.org_id);
        -- apps.mo_global.init('AR');
       
        select application_id
              ,responsibility_id
          into v_app
              ,v_res
          from fnd_responsibility_tl
         where responsibility_name = v_res_name ;

        ---- Setting the oracle applications context for the particular session
        apps.fnd_global.apps_initialize(v_user,v_res,v_app);    
        ----------------------------------------------------------------------------       
        v_ret_status := null;
        v_msg_count  := null;
        v_msg_data   := null;
      
        --update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)
        update ar_system_parameters_all
           set invoice_deletion_flag ='Y'
         where  org_id = c_rec.org_id;

        ar_invoice_api_pub.delete_transaction(
                                                 p_api_name             => 'Delete_Transaction',
                                                 p_api_version          => 1.0,
                                                 p_init_msg_list        => fnd_api.g_true,
                                                 p_commit               => fnd_api.g_true,
                                                 p_validation_level     => fnd_api.g_valid_level_full,
                                                 p_customer_trx_id      => c_rec.customer_trx_id,
                                                 p_return_status        => v_ret_status,
                                                 p_msg_count            => v_msg_count,
                                                 p_msg_data             => v_msg_data,
                                                 p_errors               => v_message_tbl
                                             );

        if v_ret_status <> 'S' then
            dbms_output.put_line( '   Status: '||v_ret_status);
            for i in 1 .. v_msg_count loop
                apps.fnd_msg_pub.get( i, apps.fnd_api.g_false, v_msg_data, v_msg_index);
                dbms_output.put_line( '   Error : '||v_msg_data);
            end loop;
            dbms_output.put_line ('   '||v_msg_data);
        else
            dbms_output.put_line ('   Deleted.');
           
            -- Revert back to the original value for the deletion flag
            update ar_system_parameters_all
               set invoice_deletion_flag ='N'
             where  org_id = c_rec.org_id;

        end if;      
       
        dbms_output.put_line('--------------------');
        commit;       
    end loop;       
exception
    when others then
        dbms_output.put_line('Error : '||sqlerrm);
end; 
-----------------------------------------------------------------------------------------------------------

Note :- You cannot delete transactions that have activity against them (completed , Credit Notes which are applied against any Invoice)

View Other Users Request Output

R11

System Administrator à Profile à System

Search for profile à 'Concurrent: Report Access Level'

Level	Value	Result
Site	User	The user can only view the requests submitted by him
Responsibility	Responsibility	That user can also review the log and report output files from all requests submitted from the current responsibility
User	Responsibility	Any user of that responsibility can also view the log and report output files from all requests submitted by any other user of that responsibility

R12

Unfortunately in R12 profile 'Concurrent: Report Access Level' does not work, it is a bit more complex process to achieve this

In R12 this was replaced by Role Based Access Control. The UMX Role Based Access Control (RBAC) is to control who can view request output files.

Note :-  You should have SYSADMIN user access.

Functional Developer -> Core Services

 >> Search for 'Concurrent Requests'

Click on 'Concurrent Requests' and then on tab 'Object Instance Set'

Ø  Click Create Instance Set

 Ø  Give Name/Code/Description

Ø  For Predicate

è Enter below code if you want to see request output for all concurrent programs . 

&TABLE_ALIAS.request_id in(

select  cr.request_id

  from apps.fnd_concurrent_requests cr, apps.fnd_concurrent_programs cp

where cr.concurrent_program_id = cp.concurrent_program_id

     and cr.program_application_id = cp.application_id)

è Modify the above query based on your requirement.

n  Add and cp.concurrent_program_name  if you want to give access only for particular program name, etc.

Ø  'Apply' it and it will give the Confirmation with Code you have entered.

 >>> Now login as a SYSADMIN User.

Ø  Goto User Management > Role & Role Inheritance

è Click on 'Create Role'

      Ø  Category – Miscellaneous / Application – Application Object Library

      Ø  Role Code (Prefix UMX| will be added whatever you provide example : UMX|AKTEST)

Ø  Display Name/ Description

Ø  'Apply'

Ø  Click on 'Create Grant'

Ø  Provide Name / Description

Ø  Data Security ->  Object -> 'Concurrent Requests'

Ø  Click on 'Next'

Ø  Data Context Type -> 'Instance Set'

Ø  Instance Set  -> Instance you Created above, AKTEST

Ø  Click 'Next'

Ø  Set -> Set -> 'Request Operations'

 

Ø  Click on 'Next' -> Preview and then 'Finish'

Ø  It should give you the message for successful creation of Grant.

Known Issue

 

Sometimes it will give below Error. Just try 3-4 times, it should be fine. Sorry didn't dig much into it to find the exact reason behind it. Error Page

You have encountered an unexpected error. Please contact the System Administrator for assistance.

Goto 'User tab 

Ø  Search for the user you want to assign the grant.

Ø  Click  'Update'

Ø  Click 'Assign Role'

Ø  Search for Role you have created above 'View All Concurrent Requests outputs' (Code - UMX|AKTEST)

Ø  Give Justification

Ø  You can put active date from past to view previous requests outputs.

   Ø  DONE... :)

* Now assigned user can see the output of other user's request from the responsibility from  which that request had been launched.

>> You can also use the below script to assign the grant to a user.

begin

     wf_local_synch.PropagateUserRole(p_user_name => 'USERNAME'

                                                               ,p_role_name => 'UMX|AKTEST ');

     commit;

End;

Script to Give Grant (Read Only) for all Objects to a particular Schema

-- Please give the User Name in v_user variable

declare
cursor cur_grants(p_user varchar2)
is
    select 'GRANT '
               || decode(db.object_type,'TABLE','SELECT',
               'VIEW','SELECT',
               'EXECUTE')
                || ' ON '
                || decode(db.owner,'PUBLIC','',db.owner || '.')
                || '"'
                || db.object_name
                || '"'
                || ' TO '||p_user sql_stmt
     from all_objects db
   where db.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE',  'VIEW','FUNCTION')
   union
    select 'GRANT '
              || decode (ao2.object_type, 'TABLE', 'SELECT',
              'VIEW', 'SELECT',
              'EXECUTE')
              || ' ON '
              || decode(ao.owner,'PUBLIC','',ao.owner || '.')
              || '"'
              || ao.object_name
              || '"'
              || ' TO '||p_user sql_stmt
    from all_objects ao
           , all_objects ao2
           , dba_synonyms ds
  where ao.object_type = 'SYNONYM'
      and ao.object_name = ds.synonym_name
      and ao2.object_name = ds.table_name
      and ao2.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE',  'VIEW','FUNCTION')
;

v_user varchar2(20) := 'USER_NAME';

begin

for i in cur_grants(v_user) loop

     execute immediate i.sql_stmt ;

     dbms_output.put_line('Command : '||i.sql_stmt);

end loop;

exception
     when others then
          dbms_output.put_line('Error: '||sqlerrm);
end;
/

Script to create Synonym for all Objects for a particular Schema

-- Please give the User Name in v_user variable

declare
cursor cur_synonym(p_user varchar2)
is
select 'CREATE SYNONYM '||p_user||'.'
           || db.object_name
           || ' FOR APPS.'
           || db.object_name sql_stmt
  from dba_objects db
where db.owner = 'APPS'
;

v_user varchar2(20) := 'USER_NAME';

begin

for i in cur_synonym(v_user) loop

     execute immediate i.sql_stmt ;

     dbms_output.put_line('Command : '||i.sql_stmt);

end loop;

exception
     when others then
          dbms_output.put_line('Error: '||sqlerrm);
end;
/

XML Publisher Report with PLSQL as Data Source and Known Issues

It is a very old topic but still find it interesting. Few days ago one of my good friend was asking about this, so I thought why not write an article on it?

I generally use this method when I have a single group for my report.

Step-by-step instruction to create a XML report using pl/sql package.

1> Prepare your query based on your business requirement.
2> Write a pl/sql packaged procedure to generate XML data.
3> Register the packaged procedure in Test application (Ex. Concurrent program short code : XXSAMXML).
4> Add to the respective request group.
5> Run the report and save the output as sample.xml
6> Create a rtf template (sample.rtf) on MS Word based on sample.xml (using BI Publisher Desktop)
 
BI Publisher Desktop, a client side tool which consits of a plugin to MS Word for building of RTF templates and the Template Viewer that can help you to test and debug all BI Publisher templates.

   > Check if BI Publisher Desktop patch has been installed on your machine, if not you can download it from here respective to you desktop machine

     http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html

   > After installing this patch a new option with BIpublisher/'Template Builder' will appear in MS Word toolbar.

KNOWN ISSUES:
     > Sometimes it does not, before MS 2010, what you can do is,
       Goto Tools > Templates --> Add-Ins
           > Check the TemplateBuilder.dot in Global templates and Add-ins
     > One more way to do
       Goto Start > All Programs > Oracle BI Publisher Desktop > Template Viewer
           > Working directory would be the directory where your xml file and template is there
             > Select the data and Template and 'start Processing'

7> Registering this template in application.
   > Create Data Definition
     > Go to 'XML Publisher Administrator' > Data Definitions > Create Data definition
       Imp:- Code must be same as the Concurrent Program Short name as XXSAMXML
   > Create Template
     > Click on 'Template' Tab and create the same.
       > Browse and Add the previously created rtf (sample.rtf) as Template File.

KNOWN ISSUES:
1> 'XML Publisher Administrator' not a valid responsibility for the current user. Please contact your System Administrator
        > Change your browser, IE to Mozila or Crome
        > Logon to Functional Administrator responsibility
        > Goto Core services > Caching Framework > Global configuration > Clear All Cache
          > It should solve your issue.

2> "You are trying to access a page that is no longer active. - The referring page may have come  from a previous session. Please select Home to proceed."
        > Avoid using TAB key and use the flashlight icon to open up the List of Values.

3> Excel issue while opening the output file.(Only if you have selected output format as Excel in Data Definition)
        "The file you are trying to open xxx.xlsx is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now?"
        > Goto Start > Run > regedit
        > In registry --> HKEY_CURRENT_USER > SOFTWARE > MICROSOFT > OFFICE > 12.0/14.0 > EXCEL > SECURITY
          > Right click in the right window and choose New -> DWORD with name 'ExtensionHardening' and Value '0'.

Sample Packaged Procedure

1> Package Specification

CREATE OR REPLACE package apps.xx_sample_pkg
as
--//============================================================================
--//
--// object name         :: xx_sample_pkg
--//
--// object type         :: package specification
--//
--// parameters          :: n/a
--//
--// object description  :: This package contains all the procedures and
--//                        functions used for demostrating xml pl/sql report
--//============================================================================
--// vers     author              date               description
--//============================================================================
--// 1.0      Abhay Kumar         24/10/2012         initial build
--//============================================================================


    procedure xx_sample_proc (
                                     p_errbuf    out varchar2
                                    ,p_retcode   out number
                                    ,p_due_date  in  varchar2
                                 );   

end xx_sample_pkg;
/

2> Package Body

CREATE OR REPLACE package body APPS.xx_sample_pkg
as
--//============================================================================
--//
--// object name         :: xx_sample_pkg
--//
--// object type         :: package Body
--//
--// parameters          :: n/a
--//
--// object description  :: This package contains all the procedures and
--//                        functions used for demostrating xml pl/sql report
--//============================================================================
--// vers     author              date               description
--//============================================================================
--// 1.0      Abhay Kumar         24/10/2012         initial build
--//============================================================================

-------------------------------------------------------------------------------------
-- procedure: log_message
-- write message to concurrent log file
-------------------------------------------------------------------------------------
procedure log_message (p_message_in in varchar2)
is
begin
  apps.fnd_file.put_line (apps.fnd_file.log, p_message_in || '.');
end log_message;

-- -------------------------------------------------
-- procedure generate_xml
-- procedure to generate xml from the data in a clob
-- field
-- -------------------------------------------------
procedure generate_xml
                        (
                            p_ref_cur                       in      sys_refcursor,
                            p_row_tag                       in      varchar2,
                            p_row_set_tag                   in      varchar2,
                            x_xml_data                      out     nocopy clob
                        )
is
    l_ctx   dbms_xmlgen.ctxhandle;
begin

    -- create a new context with the sql query
    l_ctx := dbms_xmlgen.newcontext (p_ref_cur);

    -- add tag names for rows and row sets
    dbms_xmlgen.setrowsettag(l_ctx, p_row_tag);
    dbms_xmlgen.setrowtag(l_ctx, p_row_set_tag);

    -- generate xml data
    x_xml_data := dbms_xmlgen.getxml (l_ctx);

    dbms_xmlgen.closecontext(l_ctx);

exception
    when others then
        log_message('Error generating XML ');
        log_message(sqlerrm);
end generate_xml;

-- ----------------------------------------------
-- procedure print_xml_data
-- procedure to print xml data
-- ----------------------------------------------
procedure print_xml_data (p_xml_data in clob) is

      l_amount                        number;
      l_offset                        number;
      l_length                        number;
      l_data                          varchar2(32767);

begin

    l_length := nvl(dbms_lob.getlength(p_xml_data),0);
    l_offset := 1;
    l_amount := 16000;

    loop

        exit when l_length <=0;

        dbms_lob.read(p_xml_data, l_amount, l_offset, l_data);

        apps.fnd_file.put(apps.fnd_file.output, l_data);

        l_length := l_length - l_amount;
        l_offset := l_offset + l_amount;

    end loop;

exception
    when others then
        log_message('Unexpected Error printing XML Output ');
        log_message(sqlerrm);

end print_xml_data;

-------------------------------------------------------------------------------------
-- procedure: xx_sample_proc
-------------------------------------------------------------------------------------
procedure xx_sample_proc (
                                 p_errbuf    out varchar2
                                ,p_retcode   out number
                                ,p_due_date  in  varchar2
                             )
is

    ----------------------------------------------------------------------------------
    -- local variables
    ----------------------------------------------------------------------------------

    v_due_date        date;
    lv_date_to        date;
    lv_xml_data       clob;
    l_data_qry_cur    sys_refcursor;
    l_row_tag         varchar2(4000);
    l_row_set_tag     varchar2(4000);
    l_task_from       varchar2(3);
    l_task_to         varchar2(3);

begin
        l_data_qry_cur := null;

        ---------------------------------------------------------------
        if p_due_date is not null then
           begin           
              select to_date(p_due_date,'YYYY/MM/DD HH24:MI:SS')
                into v_due_date
                from dual;
           exception
              when others then
                 log_message('Error while converting Dates. '||sqlerrm);
           end;
        end if;
       
        log_message('From Date : '||v_due_date);
       
        --------------------------------------------------------------------------------------------------------------
        -- Data Query
        ---------------------------------------------------------------------------------------------------------------
        open l_data_qry_cur for
                              'select invoice_num invoice_number,vendor_name supplier_name,segment1 supplier_number,vendor_site_code supplier_site,name operating_unit, invoice_date, due_date,cancelled_date,
                                      invoice_amount,amount_paid,amount_remaining, invoice_amount,amount_remaining,Message
                                 from
                                     (select i.invoice_num,v.vendor_name,v.segment1,vs.vendor_site_code,hu.name,to_char(i.invoice_date,''DD/MM/YYYY'') invoice_date,to_char(ps.due_date,''DD/MM/YYYY'') due_date,to_char(i.cancelled_date,''DD/MM/YYYY'') cancelled_date,
                                             i.invoice_amount,i.amount_paid,ps.amount_remaining, SUM(i.invoice_amount),sum(ps.amount_remaining),''Bank Details Are Missing On Invoice'' Message
                                        from ap_payment_schedules_all ps,
                                              ap_invoices_all i,
                                              ap_suppliers v,
                                              ap_supplier_sites_all vs,
                                              hr_operating_units hu
                                       where  1=1
                                         and  i.invoice_id = ps.invoice_id
                                         and  i.vendor_id = v.vendor_id
                                         and  i.vendor_site_id = vs.vendor_site_id
                                         and  i.payment_status_flag =''N''
                                         and  hu.organization_id = vs.org_id
                                         and  (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
                                         and  trunc(due_date) <= trunc(nvl(:v_due_date,due_date))
                                         and  i.external_bank_account_id is null
                                         and  exists (select ''Account Exixts''
                                                       from apps.iby_account_owners ao
                                                      where ao.account_owner_party_id = v.party_id)
                                       group by v.vendor_name,v.segment1,vs.vendor_site_code,hu.name,i.invoice_num,i.invoice_date,ps.due_date,i.invoice_amount,i.amount_paid,ps.amount_remaining,i.cancelled_date  
                                       )
                                    order by due_date'
                                using  v_due_date,v_due_date,v_due_date,v_due_date ;

        -------------------------------------------------------------------
       
        l_row_tag := 'XXAPINVOICE';           --> Main Group Name
        l_row_set_tag := 'XXNOBANK_DETAILS';  --> Inner Group Name

        log_message('Call generate_xml procedure');

        generate_xml(l_data_qry_cur, l_row_tag, l_row_set_tag, lv_xml_data);

        log_message('Call print_xml_data procedure');

        print_xml_data (lv_xml_data);

        log_message('End of Processing');

        p_errbuf := 'Program has completed successfully';
        p_retcode := 0;
        -------------------------------------------------------------------
exception
    when others then
        log_message('Error in ap_no_bank_report. '||sqlerrm);
        p_errbuf := substr (ltrim (sqlerrm), 1, 254 );
        p_retcode := 2;
end xx_sample_proc;
  
end xx_sample_pkg;
/

You may like another related post XML Publisher Report with Data Template as Data Source and Known Issues

>> Biru it is for you :)