Concurrent Programs in the same request set

At times there might be a requirement to spawn a concurrent request based on the output of another concurrent program in a request set.


However there are no api's to provide with the request set id.


There are ways to identify the request set because each request set launches a concurrent program which in turn launches the concurrent programs that are constituents of a request set.


For e.g if a request Set SET1 has the following programs that would fire in the order:
Program A
Program B
Program C


Launching the request set would launch the spawned process(request id 0) with the following requests for
Program A - request id 1 parent request id 0
Program B - request id 2 parent request id 0
Program C - request id 3 parent request id 0


Therefore the following code would help identify the request id of the programs that have executed as part of the same request set:


  FUNCTION get_peer_request_id
  (
    p_request_id                NUMBER,
    p_peer_request_program_name VARCHAR2
  ) RETURN NUMBER IS
    l_peer_request_id NUMBER;
  BEGIN
 
    SELECT parent_req.request_id
      INTO l_peer_request_id
      FROM fnd_concurrent_requests    parent_req,
           fnd_concurrent_programs_vl fp,
           fnd_concurrent_requests    child
     WHERE parent_req.concurrent_program_id = fp.concurrent_program_id
       AND parent_req.parent_request_id = child.parent_request_id
       AND parent_req.request_id != child.request_id
       AND parent_req.parent_request_id != -1
       AND fp.concurrent_program_name = p_peer_request_program_name
       AND child.request_id = p_request_id;
 
    RETURN l_peer_request_id;
 
  EXCEPTION
    WHEN OTHERS THEN
      l_peer_request_id := -1;
      RETURN l_peer_request_id;
  END get_peer_request_id;


Once we have the request id we can use the sql


select * from fnd_requests where request_id=p_request_id
to get information about the request.

Dunning report modification

Dunning Letters:


Introduction


Dunning Reports are used by Oracle Receivables to inform your customers of past due invoices, debit memos, and chargebacks. When dunning letters are printed, Oracle Receivables prints a copy of each invoice which has line items that are past due.


Dunning letters can be defined at various levels to change the tone each time.


There is flexibility within Oracle Receivables to define Dunning Letter Sets and Dunning Letters to correspond to that.


These dunning letter sets can then be defined against customer profiles to determine the dunning letter that would be sent to a given customer.


The default Oracle program only provides for Text based dunning letters and not RTF based letters.


Background
Dunning letters can be modified to look at RTF templates by understanding the following:


1. Dunning letters are generated using the concurrent program:
Dunning Letter Generate.(ARDLGP)
This can be run as a preliminary report where the invoices of customers are listed out without printing dunning letters or as a final report by setting the preliminary flag to No.


When the dunning report is run in the preliminary mode it launches the concurrent program:
Dunning Letters - Preliminary(ARXDUNP)


In Final mode it launches the program:
Dunning Letter Print from Dunning Letter Generate(ARDLP_NON_SRS)


Both the dunning letters programs are not standalone programs and would be launched by the spawned process Dunning Letter Generate.


The programs Dunning Letters - Preliminary and Dunning Letter Print from Dunning Letter Generate(ARDLP_NON_SRS) are both oracle reports.


Therefore these can both be modified to output xml as the output rather than text.


However these are launched by spawned processes and hence they cannot be launched standalone.


These need to have their templates set in the post-report trigger. This can be done by calling out to the concurrent program:
 
l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
p_request_id,
222, -- Receivables
'ARDLP_NON_SRS', -- Dunning Letter
'en-US', -- English
'N','RTF','PDF');
 
 
Similarly if we need to burst the xml output we can call out to the program:
 
l_burst_id :=fnd_request.submit_request('XDO','XDOBURSTREP','','',FALSE,l_req_id    ,'Y',chr(0),
'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','',
'','','' ,'','','','','','','');

BI Publisher Report in Oracle Applications

BI Publisher is an emerging reporting tool in Oracle Applications that is now being used extensively in place of Reports. BI Publisher provides for extremely configurable reports that can be published as documents RTF/PDF/Various other formats. This article is aimed at providing a simple coding example for a BI Publisher report using a data template. Following are the steps:

Assumption:
Example: Let us assume that we want to create a program that tracks changes to all supplier bank accounts. For this purpose assume that we have created a table xxxx_po_vendors_audit which captures changes to all bank accounts related to suppliers and has the following structure:
CREATE TABLE xxxx.xxxx_po_vendors_audit
(
vendor_id NUMBER,
old_bank_account_num VARCHAR2(30),
new_bank_account_num VARCHAR2(30),

vendor_name VARCHAR2(240),
creation_date DATE,
last_update_date DATE,
created_by NUMBER,
last_udated_by NUMBER,
last_update_login NUMBER,
last_update_user VARCHAR2(100),
vendor_site_code VARCHAR2(30)
);
/
CREATE SYNONYM xxxx_po_vendors_audit for xxxx.xxxx_po_vendors_audit;
/

This data is capture in a trigger defined on :
ap_bank_account_uses_all. The code for this would be:
CREATE OR REPLACE TRIGGER apps.po_vendors_nrc_xxxx AFTER INSERT OR UPDATE ON ap_bank_account_uses_all FOR EACH ROW
BEGIN
if(:new.external_bank_account_id <> :old.external_bank_account_id)THEN
IF(:NEW.VENDOR_SITE_id IS NOT NULL)THEN
insert into xxxx_po_vendors_audit
(
vendor_id,
old_bank_account_num,
new_bank_account_num,
vendor_name,
creation_date,
last_update_date,
created_by,
last_udated_by,
last_update_login,
last_update_user ,
VENDOR_SITE_CODE
)
select
:NEW.vendor_id,
aba_old.bank_account_num,
aba_new.bank_account_num,
pov.vendor_name,
sysdate,
:NEW.last_update_date,
:NEW.created_by,
:NEW.last_updated_by,
:NEW.last_update_login,
FU.USER_NAME ,
povs.vendor_site_code
from fnd_user fu,
AP_BANK_ACCOUNTS_ALL aba_new,
ap_bank_accounts_all aba_old ,
po_vendors pov ,
po_vendor_sites_all povs
where fu.user_id=:NEW.last_updated_by
AND aba_new.bank_account_id=:NEW.EXTERNAL_BANK_ACCOUNT_ID
AND aba_old.bank_account_id=:OLD.EXTERNAL_BANK_ACCOUNT_ID
AND pov.vendor_id = :NEW.VENDOR_ID
AND povs.vendor_id = :NEW.VENDOR_id
AND povs.vendor_site_id =:NEW.VENDOR_SITE_ID;
ELSE
insert into xxxx_po_vendors_audit
(
vendor_id,
old_bank_account_num,
new_bank_account_num,
vendor_name,
creation_date,
last_update_date,
created_by,
last_udated_by,
last_update_login,
last_update_user
)
select
:NEW.vendor_id,
aba_old.bank_account_num,
aba_new.bank_account_num,
pov.vendor_name,
sysdate,
:NEW.last_update_date,
:NEW.created_by,
:NEW.last_updated_by,
:NEW.last_update_login,
FU.USER_NAME
from fnd_user fu,
AP_BANK_ACCOUNTS_ALL aba_new,
ap_bank_accounts_all aba_old ,
po_vendors pov
where fu.user_id=:NEW.last_updated_by
AND aba_new.bank_account_id=:NEW.EXTERNAL_BANK_ACCOUNT_ID
AND aba_old.bank_account_id=:OLD.EXTERNAL_BANK_ACCOUNT_ID
AND pov.vendor_id = :NEW.VENDOR_ID;
END IF;
end if;
END;
/

Once this is done you would have data in the table xxxx_po_vendors_audit whenever you modify the supplier data. Based on this data we would want to create the xml publisher report:

1. Create a sample xml file:
2. Create an RTF template
Before creating an RTF template it is imperative that you have the XML Publisher word plugin.
Install the Oracle XML Publisher Desktop available via patch 5887917. Open the Microsoft word. You should be able to see the following menus and toolbars.

Once you install the XML Publisher Desktop you can upload the sample xml data using the menus:
Once you upload data you can then go about creating your RTF template.

First prepare your basic design for the reports by putting the columns/data cells in place.
Then associate data with the placeholders.
You can associate placeholders to elements from the xml file by using form fields. To add form fields enable the forms toolbar:

Add a new form field and add a default text for internal documentation
Now it is time to associate xml elements with placeholders so that data renders effectively:
1. We need to ensure that for parent node(row from the db table) in the xml file the data is rendered.
2. The right data is placed in the right placeholder.

Click on the help text field:
statement would loop throught all elements of type BANKACCOUNTVOROW
would then associate the VENDORNAME column with the placeholder.
Repeat the same set of steps for all other placeholder columns.
The end product should look something like this:


Once your data template is ready you can use the menu option Preview-> pdf/rtf to view it in the appropriate format.

3. Create a data definition template
Once the rtf template is ready we would need to create a data definition template that would act as the source for creating the xml data file that would be in the input source for RTF template.
This file is again defined in an xml format.
4. Create a data definition file using XML Publisher Admin




5. Create a template file using XML Publisher Admin and link it to the data definition
6. Create a new concurrent program with the same short name as the data definition file.
Once you get this done you can run the concurrent program and specify the template to see the output: