XML Bursting via Alerts

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

Once the Receipt is done alert is triggered
Concurrent based on PLSQL will be triggered in the Alert
Concurrent based PLSQL will invoke the Report Concurrent
Once Concurrent Report is completed then XML Bursting Concurrent will be invoked
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

PROCEDURE conc_execute (errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_receipt_number VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY APPS.XX_PO_RECPT_CONC

PROCEDURE conc_execute(errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_receipt_number VARCHAR2)

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;

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.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

<?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.sa/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.

Rajkamar Haja