Faxing Solution approach in Oracle EBS

Oracle ERP application uses third party software RightFax (pop3 fax server) application for their faxing requirements. RightFax allows application to send fax directly to fax machines from Oracle application. To do this, Oracle Application has to send an email to Rightfax server in a specified format and faxing document as an attachement which then rightfax application sends document to specified fax number.

The email format  is: /name=/fax=[Fax Number]/<[RightFax  eMail address]>

Example: /name=/fax=123456789/<rightfax.eprod@false-usa.com>'

Requirement: Customer wants develop a batch/on demand job to send an email or fax to Dealers if any of their contracts are expired for given period.

Solution: BI Publisher bursting program will full this requirement in combination with RightFax application.

Bursting functionality in BI Publisher: Bursting engine in BI Publisher accepts the Data and Data Template then splits the data steam then generates the output files at runtime then deliveries output file based on the configuration mentioned in the bursting control file.

In the current requirement notification details has to be sent via email or fax depending on dealer ‘s preference.  eMail notifications will be sent to dealer via SMTP server mentioned in the configuration file. Fax notifications, first an email will be sent to Rightfax server in the above format then RightFax application extracts the fax number and attachment from the email then faxes the attachment to the fax number mentioned in the email address alias name.

Implementation approach with a sample program:

In following illustration customer wants to send an email or fax to all departments with their employee list.

Step 1) Define Data Definition 

Navigation: XML Publisher Administrator à Data Definition  à Create Data Definition 

a)      Create Data Definition

b)      Attach the Data Definition file

c)       Call the “XML Publisher Report Bursting Program” program in the after report trigger of data file.

Sample Call

fnd_request.submit_request

                        (‘XDO’

                        ,’ XDOBURSTREP’

                        ,NULL

                        ,NULL

                        ,FALSE

                        ,’Y’  ---xdo_cp_data_security_pkg.get_concurrent_request_ids

                        ,fnd_global.conc_request_id

                        ,'Y');

<dataTemplate name="DeptEmployee" description="Department Employee Details" defaultPackage="APPS.IKN_AR_INV_EXTRACT_PKG" Version="1.0"> 

  <dataQuery> 

  <sqlStatement name="Q_MASTER">

   <![CDATA[ SELECT

                               DEPTNO MDEPTNO,

                               DNAME,

                               LOC, email, fax, pref_del,

                               DECODE(PREF_DEL,'EMAIL',EMAIL,'/name=/fax='||FAX||'/<rightfax.eprod@falsericoh-usa.com>') PEMAIL

                               FROM DEPT

      ]]>

   </sqlStatement> 

  <sqlStatement name="Q_EMPLOYEE">

      <![CDATA[ SELECT empno eempno, ename, job, mgr, to_char(hiredate,'DD-MON-YYYY') chiredate, sal, comm

                                 FROM emp

                                 WHERE DEPTNO= :MDEPTNO

  ]]>

  </sqlStatement> 

  <sqlStatement name="Q_TEMPLATE">

      <![CDATA[ SELECT

                     (select 'xdo://'||

                       lt.application_short_name

                       || '.'

                       || lt.template_code

                       || '.'

                       || lt.default_language

                       || '.'

                       || lt.default_territory

                       || '/?getSource=true' TEMPLATE

                       from xdo_templates_b lt

                     where lt.template_code = 'DEPT_EMP_03') TEMPLATE,

                     FND_PROFILE.VALUE('IKN_FND_PROJCONNECT_SERVER') SMTPSERVER,

                     'kkillams@gmail.com' FROM_EMAIL,

                     'kirankumargupta@gmail.com' REPLY_EMAIL,

                     'killamsetty@gmail.com' CC_EMAIL

                 FROM DUAL

  ]]>

  </sqlStatement>   

  </dataQuery>

  <!-- ***************************************************************************************************   -->

  <!-- Data Structure                                                                                        -->

  <!-- ***************************************************************************************************   -->

  <dataStructure>

     <group name="DEPT_DETAILS" source="Q_MASTER">

           <element name="MDEPTNO" value="MDEPTNO" /> 

           <element name="DEPART_NAME" value="DNAME" />

           <element name="LOCATION" value="LOC" />

           <element name="EMAIL" value="email" /> 

            <element name="FAX" value="fax" />

            <element name="PEMAIL" value="PEMAIL" />

            <group name="SYS_PARAMETERS" source="Q_TEMPLATE">

                               <element name="TEMPLATE"               value="TEMPLATE" />

                               <element name="FROM_EMAIL"        value="FROM_EMAIL" />

                               <element name="REPLY_EMAIL"        value="REPLY_EMAIL" />

                               <element name="SMTPSERVER"         value="SMTPSERVER" />

                               <element name="CC_EMAIL"               value="CC_EMAIL" />

           </group>

           <group name="EMP_DETAILS" source="Q_EMPLOYEE">

              <element name="EMP_NO" value="eempno" />  

              <element name="EMP_NAME" value="ename" /> 

              <element name="JOB" value="job" /> 

              <element name="HIRE_DATE" value="chiredate" /> 

              <element name="SALARY" value="sal" /> 

              <element name="COMMISSION" value="comm" />  

           </group>     

     </group>

  </dataStructure>

   <dataTrigger name="afterReport" source="IKN_AR_INV_EXTRACT_PKG.SUBMIT_REQUEST_BURST('M')" />

</dataTemplate>

Q_MASTER : Fetches the Department Information

Q_EMPLOYEE: Fetches the Employee information

Q_TEMPLATE: Fetches the configuration information

Note : Ensure that Data structure arranged properly.

Refer following diagram to know more about data file.

d)      Attach the Bursting Configure file

<xapi:request select="/DEPTEMPLOYEE/LIST_DEPT_DETAILS/DEPT_DETAILS">

<xapi:delivery>

<xapi:email server="${SMTPSERVER}" port="25" from="${FROM_EMAIL}" reply-to="${REPLY_EMAIL}"> 

<xapi:message id="123" to="${PEMAIL}" cc="${CC_EMAIL}" attachment="true" subject="Employees Detail for Department: ${DEPART_NAME}">

Dear Sir,

Please review the attached document.

Report for Dept No: ${MDEPTNO}.

Regards 

Kiran Killamsetty

P.S. Type 3 program

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document output="DEPT${MDEPTNO}EMPLOYEE.pdf" output-type="pdf" delivery="123">

<xapi:template type="rtf" location="${TEMPLATE}">

</xapi:template>

</xapi:document>

</xapi:request>

</xapi:requestset>

Refer following diagram to know more about Configuration file.

Step 2) Define Data template.

Navigation: XML Publisher Administrator à Template  à Create Template

a)      Ensure that Data Definition should match with data definition which we have created in the previous step.

b)      Select the Default out type to PDF.

c)       Attach the template

Step 3) Define the new concurrent program in application developer

a)      Concurrent Program Short should match with Data Definition Code.

b)      Select executable name as XDODTEXE

c)       Select Out format name as XML

 Step 4) Attach the concurrent program to a request group then attach request group to a responsibility.

Known Oracle Bugs:

Unable to Generate E-Mails Via Bursting Using an Alias as an Email such as "Mr. Abc'def " And/Or a Quote in the Subject

Fix : Oracle has provided a patch 8594771  for this issue.

Reference(s)

1.    XML Publisher User guide.

2.     http://search.oracle.com/search/search?keyword=date&group=Blogs