Tuesday 30 August 2011

Oracle Alerts Distribution List with Custom Application

 

   “Oracle Alerts” has an ability to send emails to predefined list of recipients called “Distribution List”. However if you try to create this list under custom application you will not find this application under the list of values.

The reason is because LOV shows only applications defined in FND_PRODUCT_INSTALLATIONS

select * from fnd_application_vl where application_short_name='XXPCK'; select application_name, application_id from fnd_application_vl fa where exists (select 'X' from fnd_product_installations p where p.application_id = fa.application_id and p.status in ('I', 'L', 'S')) order by fa.application_name

Not a problem. Go to “Oracle Alerts Responsibility”->”System”->”Installation”


You will get a simple form that asks to enter Application Name and Oracle username


image


Application name is simpe. It is just the name of your custom application. But what is Oracle Username? If you think that it is Oracle Username that you use to login into EBS, you think wrong.


Go to “System Administrator”->”Security”->”ORACLE ( Don’t ask me why it is in upper case)”-> ”Register”


Inside “Database User Name” and “Password” enter you custom schema credentials. In my case it is XXDM/XXDM


image


No you can go back to “Define Application Installations” and enter your custom application and Oracle username


image


Now you can access “Distribution List” form and choose your custom application


image

Using oracle.xml.parser.v2.XMLDocument to create new XML Document based on the existing one

 

In this post I will demonstrate how to use XMLDocument  class provied by Oracle to parse existing XML document and create a new one.

Source XML

<REPOSITORY>
<PROJECT>
<QA_TESTING NAME="Order Management">
<OWNER>SYSADMIN</OWNER>
<QA_TESTING>
<MANAGEMENT NAME="Inventory">
<END_DATE>SYSADMIN</END_DATE>
<MANAGEMENT>
</PROJECT>
</REPOSITORY>

Our goal is to create new XML document for each one of the projects


We want 2 documents.


First:



<REPOSITORY>
<PROJECT>
<QA_TESTING NAME="Order Management">
<OWNER>SYSADMIN</OWNER>
<QA_TESTING>
</PROJECT>
</REPOSITORY>
And second


<REPOSITORY>
<PROJECT>
<MANAGEMENT NAME="Inventory">
<END_DATE>SYSADMIN</END_DATE>
<MANAGEMENT>
</PROJECT>
</REPOSITORY>

We will start by loading existing XML document



String fileName = "c:/source.xml";
// Get an instance of the parser
DOMParser parser = new DOMParser();

// Generate a URL from the filename.
URL url = createURL(fileName);

// Set various parser options: validation on,
// warnings shown, error stream set to stderr.
parser.setErrorStream(System.err);
parser.showWarnings(
true);

// Parse the document.
parser.parse(url);
// Obtain the document.
XMLDocument doc = parser.getDocument();


So we load c:/source.xml and parse it to create XMLDocument instance. DOMParser knows how to work with URL only so we need to convert the file path to URL representation. We will use the function bellow for this



static URL createURL(String fileName)
{
URL url
= null;
try
{
url
= new URL(fileName);
}
catch (MalformedURLException ex)
{
File f
= new File(fileName);
try
{
String path
= f.getAbsolutePath();
String fs
= System.getProperty("file.separator");
if (fs.length() == 1)
{
char sep = fs.charAt(0);
if (sep != '/')
path
= path.replace(sep, '/');
if (path.charAt(0) != '/')
path
= '/' + path;
}
path
= "file://" + path;
url
= new URL(path);
}
catch (MalformedURLException e)
{
System.out.println(
"Cannot create url for: "+fileName);
System.exit(
0);
}
}
return url;
}
df


Now we need to  create new, empty XML Document


//create new,empty document
XDocumentBuilderFactory factory = (JXDocumentBuilderFactory)
JXDocumentBuilderFactory.newInstance();
JXDocumentBuilder documentBuilder
= (JXDocumentBuilder)
factory.newDocumentBuilder();
XMLDocument xmlDocument
= (XMLDocument) documentBuilder.newDocument();
xmlDocument.setVersion(
"1.0");
xmlDocument.setEncoding(
"UTF-8");

Next, extract “repository” element from the source XML. Likely for us, XMLDocument object supports using “XPath” expressions, so we will use it


XMLNode repositoryNode=(XMLNode) doc.selectSingleNode("//REPOSITORY");

Now we will append it as the first (root) element in the new XML document. However we cannot just take the element from existing XML , we need to “import” it first. Only after this, we can use “append” function


The following command imports “repository” element and appends it as a first child


xmlDocument.appendChild( xmlDocument.importNode(repositoryNode,false)) ;

Note “false” parameter. If you don’t specify it “false” , all children of “repository” elements will be appended. Since “repository” is root element it will be equal to appending the whole document. This is not an option for us, since we want to split the document


By the same approach we will add also “projects” element


xmlDocument.getFirstChild().appendChild(xmlDocument.importNode(doc.selectSingleNode("//REPOSITORY/PROJECT"),false));

Now we need to take all first children of “project” element only. So we will use more advanced XPath expression. We want to take the first child of “project” (QA_TESTING) and all his siblings (MANAGEMENT)


XMLNodeList appletNodes= (XMLNodeList) doc.selectNodes("/REPOSITORY/PROJECT/*[1]/following-sibling::* | /REPOSITORY/PROJECT/*[1]");

Note the difference between “selectNodes” and “selectSingleNode”. We want to get ALL first children of “project” element, so we need to bring a set. This is why we use “selectNodes” function that returns a set of nodes.


Next piece of code  will walkthrough the set we extracted. Remember that we already have some target XML with “repository” as root and “project” as “repository” child. So all we need to do it append “project” child from source XML to “project” in the target XML , write it to the file and remove immediatelly, because we want that target XML will have always only one child for “project”. Note “NAME” attribute. We will use it as a name of our target file


//all firtst children
for (int i=0; i<appletNodes.getLength(); i++ )
{
//get first child
XMLNode toRemove = (XMLNode) xmlDocument.importNode(appletNodes.item(i),true) ;

String targetFileName
= "";
//output file name will be as the name of NAME attribute
for (int j=0; j<toRemove.getAttributes().getLength(); j++)
{
if ( toRemove.getAttributes().item(j).getLocalName()!=null && toRemove.getAttributes().item(j).getLocalName().equals("NAME") )
{
targetFileName
= toRemove.getAttributes().item(j).getTextContent();
break;
}

}
//remove all blanks
targetFileName = targetFileName.replaceAll(" ", "_");

//append to target XML
xmlDocument.getFirstChild().getFirstChild().appendChild(toRemove );

//write to file
OutputStream output = new FileOutputStream(new File( "S:/"+targetFileName+".xml"));
XMLPrintDriver xmlPrintDriver
= new XMLPrintDriver(new PrintWriter(output));
xmlPrintDriver.printDocument(xmlDocument);
xmlPrintDriver.flush();
xmlPrintDriver.close();

//delete it from the target XML

xmlDocument.getFirstChild().getFirstChild().removeChild(toRemove);

}
//end for

 


What's it.