Thursday, August 21, 2014

Dunning Letter XML File for Template Creation

Below URL shows all the steps we need to do generate custom dunning letter.
Dunning letter setup and usage in Oracle r12
http://oraclemaniac.com/2014/03/11/dunning-letter-setup-and-usage-in-oracle-r12/

Only missing piece is where we can get the XML file to create custom RTF template.
Once you run concurrent program IEX: Send Dunnings for Delinquent Customers  iex_xml_request_histories table gets populated.

Step 1: Goto Collections Administrator --> Concurrent Requests --> View Requests
Step 2: Click on Details icon next to Oracle Collections Delivery XML Process (IEX: Bulk XML Delivery Manager)
Step 3: Click on View Log button at right hand corner. You can see below query in the log.

XMLDATA column stores XML file used by BI Publisher to generate dunning letter.

SELECT *
  FROM iex_xml_request_histories
 WHERE 1 = 1 
   AND status IN ('XMLDATA', 'XMLDOC','SUCCESSFUL', 'OTHER PROCESSING FAILURE'
   AND conc_request_id = :1

Pass Conc Req ID of "IEX: Send Dunnings for Delinquent Customers"
Same Conc Req id is also parameter of "Oracle Collections Delivery XML Process (IEX: Bulk XML Delivery Manager)"