eBS R12 Web Service Invocation Framework

The best source of information for WSIF in the Oracle blogosphere is the eBusiness Suite Integration blog. 
There's a great post by Vijay Shanmugam on invoking web services from Oracle Workflow with sample code. It's a great way to get started on WSIF. You may want to change the web service being invoked to William Shakespeare's quotes, because the movie finder web service in the sample code returns nothing. 


Happy WSIFing!

Upside down printing in BI Publisher using RTF template

A while back I came across this query in the BI Publisher forum.


"I have to print an address upside down in order for it to come out right side up when folded and put into an an envelope. I see in MS Word that you can select the print direction of your text, but it only allows you to chose left, right or the standard printing."


You can follow the forum thread here.


All you really have to do is add an XSL-FO directive before the first element in the cell that you want to be upside-down. 
<xsl:attribute xdofo:ctx="block-container" name="reference-orientation">180</xsl:attribute>

That's all it takes...

BI Publisher University from STR Software

Here's some free BIP related resources - check it out.

BI Publisher University is a Free Collection of Resources from STR Software To Help You Master BI Publisher.

ISG, JavaScript and Mobile Web Browsers

After getting ISG to work with SSL enabled for eBusiness Suite R12, the challenge was to get the JavaScript to work with all desktop and mobile browsers. It took me a while to figure this out and I had to ping some of my web development guru friends for help, but it works now!

If you look at the stub JS script for FND_PROFILE, you will find the function FND_PROFILE_Port_GET(_NAME) which basically creates the SOAP envelope and body and the calls the web service using XMLHttpRequest or ActiveXObject("Microsoft.XMLHTTP") for Internet Explorer. The script uses XMLDOM ( document.implementation.createDocument or ActiveXObject("Microsoft.XMLDOM")) to create a new XML document. To create elements and attributes, it uses createElement, createElementNS. 

Well, createElementNS works differently on Firefox than it does on IE, Chrome or Safari in that it uses the namespace to create an element with the namespace attribute.  For example the output from

var envelope = createElementNS(xmlDoc, 'http://schemas.xmlsoap.org/soap/envelope/', 'soapenv:Envelope'); 

in Firefox is <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">. In the other desktop browsers the output is <soapenv:Envelope>.

Modifying all createElementNS calls to createElement, I got this working on all desktop and almost all mobile browsers - BlackBerry just refused to comply. I must have gone through over a 100 BlackBerry and JavaScript links that resulted from a Google search but I'd always end up with the same result - no luck on BlackBerry browsers. 

I then changed my approach - instead of using createDocument and createElement, I created the XML as a string and loaded the string into an XML document. Not a very elegant solution, but it gets the job done. 

if (window.DOMParser)

{

parser=new DOMParser();

xmlDoc=parser.parseFromString(xmlString,"text/xml");

}

else // Internet Explorer

{

xmlDoc=new ActiveXObject("Microsoft.XMLDOM");

xmlDoc.async="false";

xmlDoc.loadXML(xmlString); 

} 

DOMParser is only available on BlackBerry versions 4.7.1 and up.

Integrated SOA Gateway and SSL

In the previous post I shared the ISG based solution for processing inbound email notifications. As I mentioned in that post, this solution only worked when the approvers were connected to the internal network. The customer required this functionality on mobile devices such as iPhone, iPad, Android, and BlackBerry. The obvious solution was to enable SSL for eBS. The eBS external URL changed to https://<ebs.companyname.com> without a port number. 
With this change in place, I tried to regenerate the WSDL for the custom procedure. After a long wait all I got was an error - something to the effect of "SOAProvider is not accessible. Please check with your system administrator". I set up statement level logging and found that the error was caused by the function call oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer. The value for SOA_SERVER_URL was  https://<ebs.companyname.com>:<port>. The program was sending an HTTP request to a non-existent URL. So, I changed the oc4j.properties template files in $FND_TOP/admin/template -  oc4j_properties_1013.tmp and oafm_oc4j_properties_1013.tmp - and ran autoconfig. The value for SOA_SERVER_URL was now https://<ebs.companyname.com>. I tried to regenerate the WSDL once more and this time I got a numeric value exception. I decompiled $JAVA_TOP/oracle/apps/fnd/soa/provider/util/ServerAccess.class and saw this piece of code :

       int k = s3.lastIndexOf(":");
  s3 contains the value of SOA_SERVER_URL. This code assumes, rightly or wrongly, that the SOA server URL will always have a port number. Well not so in this case. I could have created an SR with Oracle Support but it was easier to get the web guys to create an alias for https://<ebs.companyname.com>:<port> to point to  https://<ebs.companyname.com>. I reverted to the original templates for oc4j.properties, ran autoconfig and retried the WSDL generation. Worked as expected.


In the next post I share how I got the solution to work on mobile browsers after grappling with various levels of JavaScript support.

Serendipity and Integrated SOA Gateway

Serendipity - the accidental discovery of something pleasant, valuable, or useful.


I am an avid reader of quite a few Oracle blogs such as Steven Chan's blog, eBS Integration Blog, Tom Kyte, BI Publisher and many, many more. There's so much information out there on so many topics that I read about and hope to implement, so when I do get a chance to use what I learned from these blogs, I feel ever so grateful to the kind folks in the Oracle blogosphere. So, thank you Steven Chan, Vijay Shanmugan, Gautam Satpathy and others.


And so it was that some time in late 2009 I read about ISG and Web Services Invocation Framework in R12 when I was still working on a R11i project. The integration options made available by using web services were awesome. I rolled off that project and found a R12 implementation and one of the requirements was to build a custom approval process. No big whoop - been there done that. However, while setting up Workflow Notification Mailer, I find out that corporate policy does not allow IMAP on their mail servers. So,my choices are to come up with a solution that does not involve Oracle Workfow or I use only the outbound portion of the mailer and use something else for inbound processing. 
Not using WF just because IMAP was not allowed didn't seem right, so I went back and re-read the blog posts and documentation on ISG and decided to give it a go.
I created a custom PL/SQL procedure that called WF_NOTIFICATION.RESPOND and added it to the Integration Repository, created the WSDL and exposed the procedure as a web service. 
I created a workflow notification that included a hyperlink to a web page residing on the applications tier. The web page uses server side JavaScript to invoke the web service using XMLHttpRequest. I had to modify the standard WFMAIL workflow item in order to suppress the standard features for inbound processing.
Why JavaScript? Let's take the FND_PROFILE web service for example. The WSDL would be http://<your ebs apps server:port>/webservices/SOAProvider/plsql/fnd_profile/?wsdl . If you entered http://<your ebs apps server:port>/webservices/SOAProvider/plsql/fnd_profile/ in the address bar of your browser, you would see something like this:

Download the JavaScript stub and you have a starting point. Edit the stub to send well formed XML to the URL for the web service. Here's a sample for FND_PROFILE.GET without the WSSE:Security part:



<?xml version = '1.0' encoding = 'UTF-8'?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<fnd:SOAHeader xmlns:fnd="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_profile/"> 
<fnd:Responsibility>FND_REP_APP</fnd:Responsibility> 
<fnd:RespApplication>FND</fnd:RespApplication> 
<fnd:SecurityGroup>STANDARD</fnd:SecurityGroup> 
<fnd:NLSLanguage>AMERICAN</fnd:NLSLanguage> 
<fnd:Org_id>100</fnd:Org_id> </fnd:SOAHeader>
</soapenv:Header>
<soapenv:Body>
<get:InputParameters xmlns:get="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_profile/get/"> 
<get:NAME>APPS_WEB_AGENT</get:NAME> 
</get:InputParameters>
</soapenv:Body>
</soapenv:Envelope>


There you have it. Thanks to the customer's policy on IMAP, I could implement a neat solution using ISG. The only hitch was that the approvers had to be on the network for this to work. 


Next up - getting this solution to work on mobile devices using SSL.

BI Publisher 11.1.1.5, eBusiness Suite Security and MOAC

Starting with BI Publisher Enterprise 11.1.1.5, there is much better integration between BIP and eBusiness Suite as detailed in Kan's blog post. 


Unfortunately, the integration does not accommodate the R12 MOAC design where org access is governed by security profiles assigned to responsibilities. One of the presentations at OOW does mention that multi-org support is planned, but until that happens you can achieve the same result by creating a custom profile option similar to MO:Security Profile, let's call it XX:Reports Security Profile.


When users log in to BIP Enterprise Edition using eBusiness Suite credentials, they are prompted/forced  to choose a responsibility from the list of assigned responsibilities. While this may be an asset when accessing core eBS data, it is quite limiting when you want to access analytical data that may be stored in eBS or in another data repository. Furthermore, not all BIP EE users are real eBS users - they are setup in eBS only for BIP EE access and more than likely have no responsibilities assigned.


With the custom profile set, you can restrict data access to only those orgs assigned to the security profile thusly:



select hr.name,   xvw.organization_id 
from fnd_user u, hr_operating_units hr, 
( 
select security_profile_id, organization_id 
from per_security_organizations 
union 
select 0, organization_id 
from hr_operating_units 
) xvw 
where u.user_name = upper(:xdo_user_name) 
and xvw.security_profile_id = fnd_profile.value_specific('XX_REPORTS_SECURITY_PROFILE_LEVEL',u.user_id,null,null)
and hr.organization_id = xvw.organization_id order by 1 ;

You could use this query in a LOV for a drop down list of available operating units for a user to choose from and limit data access  to other data sources as well. 

Multi-sheet Excel output with BI Publisher in eBusiness Suite R12

Oracle Support released note 1343225.1 (How to add Microsoft Excel as Type to the Create Template List of Values in BI Publisher). The note refers to a couple of patches that are required for eBusiness Suite to enable using Microsoft Excel templates with the embedded version of BI Publisher.


The details on using MS Excel templates are in the chapter titled "Creating Excel Templates" in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher Release 11g (11.1.1) Part Number E13881-02.


I tried it out within 2 days of the note being published and I haven't been able to get legible output when running a concurrent program that uses a MS Excel template. The BIP development team at Oracle is working on bug 13002685 to provide a solution.


Once the solution is in place, not only do you have more options to create MS Excel output, you can create multi-sheet Excel output. Take a look at support note: 1352000.1 : BI Publisher: How to create Multisheet in Single Excel file using Excel Template.

Territory Manager for Oracle Incentive Compensation - Part Cuatro

Creating custom matching attributes (CMA)

Oracle Territory Manager allows you to create custom matching attributes based on any APPS schema table column attribute or based on any custom table column attribute.

The seeded matching attributes and data types can be found in Page 2-16 through 2-19 of Oracle Incentive Compensation Implementation Guide (Part No. B25388-03).

The steps and scripts for creating custom matching attributes can be found in the Oracle Territory Manager Implementation Guide Release 12 (Part Number E13505-04).

The steps are:

1. Obtain script number 1 - you will find this in the TM Implementation guide as well as Oracle Support note 743060.1, and modify as defined in note 551590.1.

Script 1 creates script 2.

2. Modify the query assigned to the variable l_batch_dea_sql to include the attributes you want to use. Make a note of the attributes and their aliases. You will need this for script number 3.

When script 2 is executed, it creates the table JTF_TAE_1001_SC_DEA_TRANS using the modified SQL from l_batch_dea_sql i.e. a CREATE TABLE AS .... is executed.

3. Now, create the custom matching attributes by modifying script 3 which you will find in the TM Implementation guide as well. 

The basis of this script is to populate all the necessary information in the following tables:

JTF_SEEDED_QUAL_ALL_B
JTF_SEEDED_QUAL_ALL_TL
JTF_QUAL_USGS_ALL


Run script 3 to create the CMA. Enable and associate the CMA to territory types to create assignment rules.




It is cumbersome, yes, but it's quite straightforward.


Once you create a new CMA, the only way to completely remove it is to delete it from JTF_SEEDED_QUAL_ALL_B
JTF_SEEDED_QUAL_ALL_TL
JTF_QUAL_USGS_ALL.

Territory Manager for Oracle Incentive Compensation - Part 3

TM4OIC part 3 walks through the setup steps required to access features within Territory Manager.

1. Setup Territory Resource Descriptive Flexfield (JTF_TERR_RSC_ALL_DFV)
Query DFF titled "Territory Resource FF" for application "CRM Foundation" in the Descriptive Flexfield Segments form. With context set to JTY_SALES, add flexfield attribute columns that will be used to capture Split Percentage and Revenue Type.

2. Set up profile option values for credit allocation






The three profile options - OIC: Territory Manager Split Percentage Flex Field, OIC: Territory Manager Revenue Type Flex Field and OIC: Skip Credit Allocation must be set so that the credit allocation engine is invoked and allocates credit based on the values in the DFF on the resource.

In a multi-org environment, if some operating units do not use credit allocation, the profile OIC: Skip Credit Allocation will need to be set to Yes at the responsibility level for the OU.

3. Territory Manager Access
In addition to granting the user Territory Manager responsibilities, the user must be assigned the following roles ( navigate to User Management responsibility to assign roles) :

- Sales Administrator
- Sales Territory Administrator
- Sales Territory Maintenance
- Matching Attributes Enabling

Matching Attributes Enabling allows you to enable custom matching attributes which is the topic for TM4OIC part 4.

Territory Manager for Oracle Incentive Compensation - Part Deux

TM4OIC part 2 provides an overview of the concurrent processes that must be run so that sales credit is allocated appropriately based on the assignment rules.


STAR - Synchronize Territory Assignment Rules:
STAR denormalizes the assignment rules and stores all the matching qualifiers in
JTY_1001_DENORM_DEA_VALUES, JTY_DENORM_DEA_RULES_ALL and JTF_TERR_QUAL_ALL. The SQL
for matching is stored in the BATCH_DEA_MATCH_SQL column of JTY_DEA_ATTR_PRODUCTS_SQL table.

Territory Assignment Engine Credit Allocation:
When the Credit Allocation concurrent program runs it kicks off a number of children processes.
1. Collect Transaction Batch Process - CN_SCATM_COLLECT_TRANS_BATCH
2. SCA Process Winners Batch Process (MATCH/POPULATE) -
CN_SCATM_PROCESS_WINNERS_BATCH
3. SCA Process Winners Batch Process (WINNER/POPULATE) -
CN_SCATM_PROCESS_WINNERS_BATCH
4. Credit Allocation Transaction Batch Process - CN_SCATM_CRED_ALLOC_TXN_BATCH

Collect Transaction Batch Process:
This concurrent program loads data into JTF_TAE_1001_SC_DEA_TRANS using SQL stored in the column
BATCH_DEA_SQL in the table JTY_TRANS_USG_PGM_SQL. The SQL in BATCH_DEA_SQL can be
customized using a script available in the Oracle Territory Manager Implementation Guide Release 12
pages A-1 through A-12. (The script in the document is not correct. The latest, correct version can be
obtained from Oracle Support note 743060.1, and modify as defined in note 551590.1)

SCA Process Winners Batch Process: (MATCH/POPULATE)
This concurrent program applies matching rules to the transactions in JTF_TAE_1001_SC_DEA_TRANS
and populates the matching salesperson for each transaction in the table JTF_TAE_1001_SC_MATCHES.
The SQL to apply matching rules is stored in the BATCH_DEA_MATCH_SQL column of
JTY_DEA_ATTR_PRODUCTS_SQL table.

SCA Process Winners Batch Process: (WINNER/POPULATE)
This concurrent program ranks the matched transactions in JTF_TAE_1001_SC_MATCHES and populates
the winning salesperson for each transaction in the table JTF_TAE_1001_SC_WINNERS.

Credit Allocation Transaction Batch Process:
This concurrent program gets the winning salesperson, split percentages and revenue types from the
table JTF_TAE_1001_SC_WINNERS and creates credited transactions in the table
CN_COMM_LINES_API_ALL.

Territory Manager for Oracle Incentive Compensation - Part I

Oracle Territory Manager has been around for a while and is very well documented in the implementation and user guides.This post is specific to TM's usage in Oracle Incentive Compensation - TM4OIC, if you will.

In part 1, I provide an overview of the product , in part 2 I cover the processes involved, in part 3, I go through the setup steps for TM and in part 4, I go through the steps to create and maintain custom matching attributes.


Why Territory Manager?
Oracle Territory Manager provides functionality with which you can create geographic territories,
account territories, and sales territories using predefined matching attributes to identify territories such
as the geographic matching attribute of country. You can also create territory hierarchies to make the
territory assignments and searches more efficient.

Oracle Incentive Compensation integrates with Oracle Territory Manager to determine who is a credit
receiver and uses the Sales Credit Allocation module within Oracle Incentive Compensation to
determine the credit percentage that each resource receives from a sales transaction.

Sales Credit allocation in Oracle Territory Manager is used to define and assign the Who (for example, a
resource in a role) on a transaction. Sales Credit Allocation in Oracle Incentive Compensation is used to
define and assign the How Much credit (for example, percentage credit splits) on a transaction for a
given resource in a role. If you don't use Sales Credit Allocation to change the allocation percent the
credit receivers get, then sales credit is split evenly (transaction amount divided by number of credit
receivers).

After territory rules are defined in Oracle Territory Manager, the Territory Assignment
Engine (TAE) applies these user-defined rules to provide transactions with credit receivers and their
roles with evenly split amounts. Next, the allocation rules set up in Oracle Incentive Compensation are
applied to the transactions created by the Oracle Territory Manager through the collection process to
create the allocation splits.

The assignment of resources, using TAE, and the determination of credit allocation, using the Sales
Credit Allocation Engine, primarily occurs after the transaction is collected into the Oracle Incentive
Compensation transaction API table CN_COMM_LINES_API during the Post Collection phase.


Setup:
The overall setup steps for using Oracle Territory Manager for Sales Credit Allocation are:
1. Create resources
2. Create roles
3. Assign roles and groups to resources
4. Create customers and/or other data relating to matching attributes on transactions
5. Enable Territory Manager access types if needed and not enabled
6. Create a territory and assign resources and roles using matching rules
7. Generate a territory package using concurrent manager – (STAR - Synchronize Territory Assignment
Rules)
8. Populate CN_COMM_LINES_API with transaction data
9. Run the concurrent program Credit Allocation to assign resources to transactions using Territory
Assignment Engine

Welcome to my blog!

As an IT consultant I have discovered many interesting things about technology, specifically Oracle technology and have often had "Wow!" moments as in "Wow! This is so cool!" or "Wow! Really? Is this for real?" Then I was told that "WOW" is an acronym for "Way Oracle Works".

I am a frequent visitor to a lot of really great blogs where I find a lot of useful information and I'm very grateful to those bloggers. About 3 years ago I considered creating my own blog but was just too lazy to get around to it. Well, here it is - my blog with experiences I'd like to share plus news, views, tips and tricks.

I hope you find the information in this blog to be useful.