Daily Archives: April 1, 2021

XML Bursting using Alerts in Oracle Apps

Apr 1

Posted by

Initially there was an alert in the system but due to alignment Issues we planned to go with trigger and XML Bursting. As there was already Alert in the system so we decided to use it instead of trigger.

Requirement

Once the Receipt is done then the Project members should receive Notification

Components Involved

  • Alert
  • Concurrent Program
  • XML Bursting

Steps Involved

  1. Once the Receipt is done alert is triggered
  2. Concurrent based on PLSQL will be triggered in the Alert
  3. Concurrent based PLSQL will invoke the Report Concurrent
  4. Once Concurrent Report is completed then XML Bursting Concurrent will be invoked
  5. Now email will be sent to Respective Users

Note: PLSQL Concurrent will have the script to invoke the Report Concurrent and XML Bursting Concurrent. XML bursting concurrent will have Report Concurrent Program Request ID as the Parameter.

Alert

Here as there was existing alert we dint change the Query so we just used it.

Once we give the details of the alert then click on Actions then give the required details as highlighted in screenshot above. Now Click on Action Details and then select Action Type as Concurrent Program.

We pass the Receipt Number as parameter from the below Query. From the below query we only require only Receipt Number rest can be ignored.

SELECT
  rsh.RECEIPT_NUM
INTO
  &RECEIPT_NUM
FROM rcv_shipment_headers rsh
WHERE 
rsh.shipment_header_id IN
  (SELECT rsh1.shipment_header_id
  FROM rcv_shipment_headers rsh1
  WHERE rsh1.rowid=:rowid
  )
AND TRUNC(rsh.creation_date)=TRUNC(SYSDATE)

Now we have created our alerts.

Concurrent Program

Create PLSQL Executable for Concurrent Program and parameter given as Receipt Number

CREATE OR REPLACE PACKAGE APPS.XX_PO_RECPT_CONC
IS
    PROCEDURE conc_execute (errbuf             OUT VARCHAR2,
                            retcode            OUT NUMBER,
                            p_receipt_number       VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY APPS.XX_PO_RECPT_CONC
is
    PROCEDURE conc_execute(errbuf OUT VARCHAR2,
                           retcode OUT NUMBER,
                           p_receipt_number VARCHAR2)
    is 
        ln_resp_id 	NUMBER;
        ln_appl_id    	NUMBER;
        ln_user_id            	NUMBER;
        ln_con_req_id            NUMBER;
        ln_conc_id     NUMBER;
        g_request_id  NUMBER;
    BEGIN

        SELECT user_id INTO ln_user_id  
        FROM fnd_user WHERE user_name = 'SYSADMIN';

        SELECT DISTINCT fr.responsibility_id,
            frx.application_id
        INTO ln_resp_id,
            ln_appl_id
        FROM apps.fnd_responsibility frx,
             apps.fnd_responsibility_tl fr
        WHERE fr.responsibility_id = frx.responsibility_id
             AND LOWER (fr.responsibility_name) LIKE LOWER('XX Purchasing Super User');

        apps.fnd_global.apps_initialize (ln_user_id ,ln_resp_id,ln_appl_id);  
          
        ln_con_req_id := fnd_request.submit_request ( 
                                application   =>'XXCUS', 
                                program       =>'XX_PO_RECEIPT_CONC', 
                                description   =>'PO Receipt Alert Testing', 
                                start_time    =>sysdate, 
                                sub_request   =>FALSE,
                                argument1     =>p_receipt_number --  PARAMETER OF THIS CONCURRENT PROGRAM--
        );
         
        COMMIT;
        IF ln_con_req_id = 0
        THEN
            dbms_output.put_line ('Concurrent Program failed to Call from plsql');
        ELSE
            dbms_output.put_line('Concurrent Program Sucessfully Call from plsql');
            ln_conc_id :=  fnd_request.submit_request(
                 application   => 'XDO'
                ,program       => 'XDOBURSTREP'
                ,description   => NULL
                ,start_time    => SYSDATE
                ,sub_request   => FALSE
                ,argument1     => NULL         
                ,argument2     => ln_con_req_id  
                ,argument3     => 'N');
            COMMIT;
        END IF;
    EXCEPTION
    WHEN others then null;
    END;
END;
/

The above code will be invoked by the Alert when triggered. Now the Report Concurrent will be XX_PO_RECEIPT_CONC executed. As the Report Concurrent is Oracle Reports and the Output Format is set to XML. With the generated XML the XML bursting will be done.

The PLSQL block Executes the XML Publisher Report Bursting Program once the Report Program is completed. As the XML Publisher Report Bursting Program will have Concurrent progam request id as the parameter. Once it is given then the mail will be sent.

XML bursting

Below is the XML Data file for which the mails has to be sent. This XML data comes from the output of the Report Concurrent.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<XX_PO_RECEIPT_CONC>
  <LIST_G_ORG>
    <G_ORG>
      <EMAIL>testing@invalid.com</EMAIL>
      <ORG>ORG01</ORG>
      <HEADER>XX_PO_RECEIPT_NOTIF</HEADER>
      <RECEIPT_NUM>23344</RECEIPT_NUM>
      <PO_NUMBER>22548</PO_NUMBER>
      <VENDOR_NAME>Vendor001</VENDOR_NAME>
      <PROJECT_OU_GRP>Random OU</PROJECT_OU_GRP>
      <PROJECT_MEMBER>Tester01</PROJECT_MEMBER>
      <PROJECT_EMAIL_ADD>tester01@invalid.com</PROJECT_EMAIL_ADD>
      <RECEIPT_DATE>16-FEB-21</RECEIPT_DATE>
      <LIST_G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1027790</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Random OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4008-9101 | FA PANEL ADDRESSABLE RED 120VAC</ITEM_DESCRIPTION>
          <ITEM_CODE>0000001</ITEM_CODE>
          <PROJECT_NAME>Project-1</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1027788</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Random OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4008-9102 | ADDRESSABLE SMALL FA BEIGE</ITEM_DESCRIPTION>
          <ITEM_CODE>0000002</ITEM_CODE>
          <PROJECT_NAME>Project-2</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1027844</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Random OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4010-9201 | FACP 250PT 4NAC 4A 240V</ITEM_DESCRIPTION>
          <ITEM_CODE>0000003</ITEM_CODE>
          <PROJECT_NAME>Project-3</PROJECT_NAME>
        </G_PROJECT_OU>
      </LIST_G_PROJECT_OU>
    </G_ORG>
    <G_ORG>
      <EMAIL>testing@invalid.com</EMAIL>
      <ORG>ORG01</ORG>
      <HEADER>XX_PO_RECEIPT_NOTIF</HEADER>
      <RECEIPT_NUM>23344</RECEIPT_NUM>
      <PO_NUMBER>22548</PO_NUMBER>
      <VENDOR_NAME>Vendor001</VENDOR_NAME>
      <PROJECT_OU_GRP> Random OU</PROJECT_OU_GRP>
      <PROJECT_MEMBER>Tester02</PROJECT_MEMBER>
      <PROJECT_EMAIL_ADD>tester02@invalid.com</PROJECT_EMAIL_ADD>
      <RECEIPT_DATE>16-FEB-21</RECEIPT_DATE>
      <LIST_G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1027790</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Random OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4008-9101 | FA PANEL ADDRESSABLE RED 120VAC</ITEM_DESCRIPTION>
          <ITEM_CODE>0000001</ITEM_CODE>
          <PROJECT_NAME>Project-1</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1027788</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Random OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4008-9102 | ADDRESSABLE SMALL FA BEIGE</ITEM_DESCRIPTION>
          <ITEM_CODE>0000002</ITEM_CODE>
          <PROJECT_NAME>Project-2</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1027844</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Random OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4010-9201 | FACP 250PT 4NAC 4A 240V</ITEM_DESCRIPTION>
          <ITEM_CODE>0000003</ITEM_CODE>
          <PROJECT_NAME>Project-3</PROJECT_NAME>
        </G_PROJECT_OU>
      </LIST_G_PROJECT_OU>
    </G_ORG>
    <G_ORG>
      <EMAIL>testing@invalid.com</EMAIL>
      <ORG>ORG01</ORG>
      <HEADER>XX_PO_RECEIPT_NOTIF</HEADER>
      <RECEIPT_NUM>23344</RECEIPT_NUM>
      <PO_NUMBER>22548</PO_NUMBER>
      <VENDOR_NAME>Vendor001</VENDOR_NAME>
      <PROJECT_OU_GRP> Randomness OU</PROJECT_OU_GRP>
      <PROJECT_MEMBER>Tester01</PROJECT_MEMBER>
      <PROJECT_EMAIL_ADD>tester01@invalid.com</PROJECT_EMAIL_ADD>
      <RECEIPT_DATE>16-FEB-21</RECEIPT_DATE>
      <LIST_G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1025676</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Randomness OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4100-9211 | MASTER CONTROLLER 220/240V</ITEM_DESCRIPTION>
          <ITEM_CODE>0000004</ITEM_CODE>
          <PROJECT_NAME>Project-01</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1024088</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Randomness OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4100-9212 | INFOALM MSTRCNTLR INTL 220/240</ITEM_DESCRIPTION>
          <ITEM_CODE>0000005</ITEM_CODE>
          <PROJECT_NAME>Project-02</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1023084</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Randomness OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4100-9241 | NDU CE COMPLAINT</ITEM_DESCRIPTION>
          <ITEM_CODE>0000006</ITEM_CODE>
          <PROJECT_NAME>Project-03</PROJECT_NAME>
        </G_PROJECT_OU>
      </LIST_G_PROJECT_OU>
    </G_ORG>
    <G_ORG>
      <EMAIL>testing@invalid.com</EMAIL>
      <ORG>ORG01</ORG>
      <HEADER>XX_PO_RECEIPT_NOTIF</HEADER>
      <RECEIPT_NUM>23344</RECEIPT_NUM>
      <PO_NUMBER>22548</PO_NUMBER>
      <VENDOR_NAME>Vendor001</VENDOR_NAME>
      <PROJECT_OU_GRP> Randomness OU</PROJECT_OU_GRP>
      <PROJECT_MEMBER>Tester02</PROJECT_MEMBER>
      <PROJECT_EMAIL_ADD>tester02@invalid.com</PROJECT_EMAIL_ADD>
      <RECEIPT_DATE>16-FEB-21</RECEIPT_DATE>
      <LIST_G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1025676</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Randomness OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4100-9211 | MASTER CONTROLLER 220/240V</ITEM_DESCRIPTION>
          <ITEM_CODE>0000004</ITEM_CODE>
          <PROJECT_NAME>Project-01</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1024088</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Randomness OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4100-9212 | INFOALM MSTRCNTLR INTL 220/240</ITEM_DESCRIPTION>
          <ITEM_CODE>0000005</ITEM_CODE>
          <PROJECT_NAME>Project-02</PROJECT_NAME>
        </G_PROJECT_OU>
        <G_PROJECT_OU>
          <PROJECT_NO>1023084</PROJECT_NO>
          <UOM>Nos</UOM>
          <QUANTITY_RECEIVED>10</QUANTITY_RECEIVED>
          <PROJECT_OU> Randomness OU</PROJECT_OU>
          <HISTORICAL_RECEIPT>10</HISTORICAL_RECEIPT>
          <PO_QUANTITY>100</PO_QUANTITY>
          <ITEM_DESCRIPTION>4100-9241 | NDU CE COMPLAINT</ITEM_DESCRIPTION>
          <ITEM_CODE>0000006</ITEM_CODE>
          <PROJECT_NAME>Project-03</PROJECT_NAME>
        </G_PROJECT_OU>
      </LIST_G_PROJECT_OU>
    </G_ORG>
  </LIST_G_ORG>
</XX_PO_RECEIPT_CONC>

Create a bursting control file with .xml extension like the below one. This bursting file will act as the configuration for sending mails. Now if you notice in the below code <xapi:request select it tells the data from where it should be taken. The ${<DataElement>} tag will be taken from XML output of the Report and be given as input in the Bursting Control file.

<?xml version="1.0" encoding="utf-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
	<xapi:globalData location="stream" />
	<xapi:request select="/XX_PO_RECEIPT_CONC/LIST_G_ORG/G_ORG/LIST_G_PROJECT_OU">
		<xapi:delivery>
			<xapi:email server="10.138.16.77" port="25" from="test.erp.alert@invalid.com"  reply-to ="testing@invalid.com">
				<xapi:message id="123"  subject="Details of new PO Receipt ${RECEIPT_NUM}" attachment="true" to="${EMAIL}, testing@invalid.com">
				Dear Team,

				Good Day!!

				User to ${PROJECT_MEMBER} : Email Address ${PROJECT_EMAIL_ADD}

				Please find attached Receipt ${RECEIPT_NUM} against PO Number ${PO_NUMBER}.

				Regards,
				Purchasing Team

				Note: This is autogenerated email, Please do not reply this email.
				</xapi:message>
			</xapi:email>
		</xapi:delivery>
		<xapi:document output="PO Receipt ${RECEIPT_NUM}" output-type="pdf" delivery="123"> 
			<xapi:template type="rtf" location="/u01/ERPDEV/fs2/EBSapps/appl/xxcus/12.0.0/out/XX_PO_RECEIPT_CONC.rtf" filter="">
			</xapi:template>
		</xapi:document>
	</xapi:request>
</xapi:requestset>

Save the above code in the file with XML format and upload in Data Definition –> Bursting Control file. Only if the mail server is configured then we will receive mails.

Posted in oracle apps, Reports - RDF & RTF, SQL/PLSQL

1 Comment

Tags: , , , , , , , , , , , , , , ,

Design a site like this with WordPress.com
Get started