REM Connectivity to Asterisk server. ;
REM Assumes that &user has been granted access to Asterisk Manager API;
Local string &host = "192.168.1.200";
Local string &user = "asterisk_user_id";
Local string &pswd = "asterisk_password";

REM Asterisk information for how we will be originating phone calls;
Local String &voip_provider_acct = "123456789</code><code>";
Local string &channel = "IAX2/" | </code><code>&voip_provider_acct</code><code>;
Local string &context = "challenge-psft-user";
Local string &exten = "7189";

REM The phone number that we will be calling;
REM This would normally come out of the user's profile;
Local string &phone = "15105551212";

REM The PeopleSoft userID of the person to call;
Local string &userID = "PTDMO";

REM This PIN could be autogenerated or stored in the DB;
Local string &pinCode = "4554";


REM ******** No more user variables below here *********** ;
Local string &base = "org.asteriskjava.manager.";
Local JavaObject &connectFactory = CreateJavaObject(&base | "ManagerConnectionFactory", &host, &user, &pswd);

Local JavaObject &connection = &connectFactory.createManagerConnection();
&connection.login();

Local JavaObject &originateAction = CreateJavaObject(&base | "action.OriginateAction");
Local JavaObject &vars = CreateJavaObject("java.util.HashMap");
&vars.put("PIN", &pinCode);
&vars.put("PSFTUSERID", &userID);
&originateAction.setChannel(&channel | "/" | &phone);
&originateAction.setContext(&context);
&originateAction.setVariables(&vars);
&originateAction.setExten(&exten);
&originateAction.setPriority(CreateJavaObject("java.lang.Integer", 1));

Local JavaObject &originateResponse = &connection.sendAction(&originateAction, 30000);
Warning (&originateResponse.getResponse());

  == Parsing '/etc/asterisk/manager.conf': Found
== Manager 'asterisk_user_id' logged on from 192.168.1.11
-- Call accepted by 64.85.163.184 (format ulaw)
-- Format for call is ulaw

      > Channel IAX2/123456789-2 was answered.

[challenge-psft-user]
exten => 7189,1,Answer()
exten => 7189,2,Playback(gs-demo-login)
exten => 7189,3,NoOp(Authenticating user ${PSFTUSERID})
exten => 7189,4,Authenticate(${PIN},j)
exten => 7189,5,NoOp(Successful login ${PSFTUSERID})
exten => 7189,6,Hangup()
exten => 7189,105,NoOp(Unsuccessful login ${PSFTUSERID})
exten => 7189,106,Hangup()

    -- Executing [7189@challenge-psft-user:1] Answer("IAX2/123456789-2", "") in new stack
 -- Executing [7189@challenge-psft-user:2] Playback("IAX2/123456789-2", "gs-demo-login") in new stack
 --  Playing 'gs-demo-login' (language 'en')
== Manager 'asterisk_user_id' logged off from 192.168.1.11
 -- Executing [7189@challenge-psft-user:3] NoOp("IAX2/123456789-2", "Authenticating user PTDMO") in new stack
 -- Executing [7189@challenge-psft-user:4] Authenticate("IAX2/123456789-2", "4554|j") in new stack
 --  Playing 'agent-pass' (language 'en')
 --  Playing 'auth-incorrect' (language 'en')
 --  Playing 'auth-incorrect' (language 'en')
 -- Executing [7189@challenge-psft-user:105] NoOp("IAX2/123456789-2", "Unsuccessful login PTDMO") in new stack
 -- Executing [7189@challenge-psft-user:106] Hangup("IAX2/123456789-2", "") in new stack
== Spawn extension (challenge-psft-user, 7189, 106) exited non-zero on 'IAX2/123456789-2'
 -- Hungup 'IAX2/123456789-2'

    --  Playing 'agent-pass' (language 'en')
  --  Playing 'auth-thankyou' (language 'en')
  -- Executing [7189@challenge-psft-user:5] NoOp("IAX2/123456789-3", "Successful login PTDMO") in new stack
  -- Executing [7189@challenge-psft-user:6] Hangup("IAX2/123456789-3", "") in new stack

Preface

Readers should note that this post is about PeopleTools 9, not PeopleSoft 9 applications. PeopleSoft 9 has shipped to customers. PeopleSoft 9 applications are based on the PeopleTools 8.4x codeline.

Take One

When I was reading Larry’s post about the history of Tree Manager I said

“Hey Larry, late 1998 wasn’t PeopleTools 8. “

“It wasn’t?”

“No, it was PeopleTools 9. “

When work first started in late 1998 on what is now known as PeopleTools 8, the code base was originally named PeopleTools 9, but was later renamed to PeopleTools 8.

Part of the problem was that the PeopleTools group was ready to start working on that release before there was much of an applications plan in place. The general thinking at the time was that the release would be too large to have it just be a “.5” release (1).

There were already designs / proof of concept code in place for things like

* Application Messaging (using this new fangled thing called XML)
* Component Interfaces (these were originally known as Business Components, which is why all of the Component Interface tables are prefaced with BC)
* Business Interlinks
* Unicode
* Java in PeopleCode
* LDAP / Roles / Signon PeopleCode

That’s quite a bit of stuff for one release, and this is even before the whole “no code on the client” stuff appeared.

As proof of how much work went into the release prior to going completely browser-based, look no further than the “Insert” menu in Application Designer.

Notice that option for inserting ActiveX controls? That was actually built for the Windows client, not the browser. Technically it is still supported with the 8.1x tools, but I would strongly recommend against using it. The menu option should have been removed when 8.4 came out (code cruft is a topic for a separate blog post some day).

I don’t remember how the decision to rename it as PeopleTools 8 happened though. There was lots of turmoil inside PeopleSoft then (falling sales, layoffs, new CEO, Vantive acquisition, etc), so it’s hard to say. I do know that build managers just love writing scripts to rename everything though. Not!

Take Two

So when did PeopleTools 9 next show up? Believe it or not, in late 2001. We were planning on having a small new version of PeopleTools to clean up a few things in the 8.1x codeline that we wished we had been smart enough to think about before shipping it. This would have been similar in scope to 7.5 PeopleTools; a follow on release after a major technology shift that served to be a long term stable base, while new development continued on. There was even talk of just calling it PeopleTools 8.2 and making it a mandatory release for customers. While that was going on we began working on the Next Big Thing for PeopleTools 9.

A couple of things conspired against that go around for PeopleTools 9. One was that the name “PeopleSoft 8” had some off the chart brand recognition (at least according to our marketing folks), so there was some senior management push to not lose that name right away.

The other was that customers were still wrestling with a lot of the details of running large scale PeopleSoft 8 deployments and there was quite a bit of pressure to make the follow on release deal with a lot of those issues.

So work on PeopleTools 9 was (mostly) halted to put all hands onto what ended up as PeopleTools 8.4. Why 8.4? Mainly to leave room for a potential 8.8 release. That ended up happening for the applications, along with 8.9 releases. Heck, the CRM group almost did an 8.95 release. Talk about asymptotically approaching 9 🙂

Take Three

I’m not sure which customer managed to finally convince PeopleSoft senior management that doing some releases that were focused on making current stuff work instead of The Next Big Thing, but I remember being in several corporate visits where (due to scheduling issues) I ended up sitting through Craig Conway or Ram Gupta’s sessions and get to hear the same earful that they were getting.

It’s wasn’t unusual to hear from people in charge of actually running PeopleSoft at a customer site about things that we could do to improve operational efficiencies. That’s one of the main reasons people used to come to Pleasanton for a corporate visit – to get a chance to talk with product strategy and development about their needs. Historically though you wouldn’t see the “C-level” discussions get into this sort of thing though.

So that’s what led to Total Ownership Experience (TOE). PeopleTools 8.44 was the big TOE release for PeopleTools. Performance Monitor, Change Assistant, etc were all big features, but not really “sexy”. Of course, they took a fair amount of development effort, which meant that there wasn’t really enough folks left to work on PeopleTools 9.

This was the go-around where there were actual PeopleTools 9 baseball hats created. I’ll have to talk Larry into posting a picture of his. I gave mine to a friend at a PeopleSoft customer in Germany awhile ago.

Take Four

The last stab at doing PeopleTools 9 was in the middle of the Oracle acquisition, so it was a little strange to be doing long term project planning. SAP was scoring sex and sizzle points with their NetWeaver story, which shaped some of the internal scoping debates.

This time around, PeopleTools 9 was not being called 9, but rather “X”. No one was ready to venture a guess as to what version number it would finally ship as. Which turned out to be rather smart since it didn’t ship 🙂

Postscript

As it turns out a lot of the ideas for PeopleTools 9 are things that Oracle had already been working on (or would soon acquire).

One was the whole idea of radically extending/re-writing the component processor to make things that are common customizations today into personalizations. Imagine the tab order personalization feature from PeopleTools 8.44 being extended to moving and hiding fields (while still honoring the business logic of course). Check out some of the ADF Faces work for some ideas on what Fusion applications will be capable of.

Another big feature was the idea of re-usable data objects. Oracle got that with the acquisition of Siebel, who in turn had acquired a company called nQuire. This now has the awkward name of Oracle Business Intelligence Enterprise Edition, but it is some really slick stuff. As Larry mentioned in a previous posting, we had brought nQuire in before Siebel bought them, but we didn’t move quick enough (historically PeopleSoft didn’t do acquisitions that well).

Applications Unlimited

One of the things that we did right after the acquisition was to survey some PeopleSoft customers about what they wanted to see from Oracle in relationship to PeopleTools. To a customer, every single one said in no uncertain terms that they didn’t want to see any major new things from PeopleTools – just focus on quality, performance, operational excellence, integration, etc.

This was prior to the announcement of Applications Unlimited,
but the sentiment remains.

As it turns out, folks that are looking for something radically new are starting to adopt Fusion Middleware. Oracle seems to be having a decent uptake of that within PeopleSoft customers.

(1) Funnily enough, the comedian that PeopleSoft hired for the PeopleSoft 8 launch party, Greg Proops, actually did some research before the event and made a comment to the effect of “All of this for going from 7.5 to 8? .5? “. Of course, he actually made it sound funny.

On a sad note, that launch party was held in the summer in 2000 at the Windows on the World restaurant, which was destroyed on September 11, 2001.

Labels: 2007, Fusion, History

Over the past 24 hours, I’ve had lots of folks wanting to learn more about the HCM reporting examples in yesterday’s post. I decided to record a flash demo that shows how one would use the queries as well as the nVision reports (and drills).

In order to simplify the navigation in the demo, I did use the nVision Drilling Snap-on (which is separately licensable, but is not required to use the queries and nVision objects in the project). However, it does make it much easier to find and use them together.

Click here to watch the HCM Reporting in action…

Labels: Drilling, HCM, nVision, PeopleSoft, Query, Tree_Manager

I haven’t written anything on the Java and PeopleCode series (part 1, part 2) recently, so I thought I’d whip something together this evening.

As previously discussed in the series, there are a few, um, quirks in the bindings between Java and PeopleCode. One typical workaround when you can’t cross between Java and PeopleCode successfully is to write some additional glue code on the Java side to provide an easier “target” to work with. This post will discuss a few tips and techniques for doing it all from the PeopleCode side.

Why would you want to avoid writing the Java glue code to simplify things? Well, it’s certainly not to avoid the complexity of Java (as the rest of this post will show). A more common reason is to avoid needing to distribute the compiled Java code out to each application server (which can be the source of various logistical difficulties).

On with the code. The use case here is to take an image and modify it so that we can stamp some text on it. The example comes from an article that shows how to use the Java Advanced Imaging libraries that are part of the standard Java environment as of Java 1.4.

The actual working code is below. Let’s start by looking at the first line of code that causes problems.

&jBufImage = &jImageIO.read(CreateJavaObject("java.io.File", &sSourceFileName));

This line of code will trigger the infamous “more than 1 overload matches” PeopleCode error. If you look at the relevant Javadoc, you’ll see that there are indeed multiple versions of the read method. Java can tell these apart by the type of the parameters being sent in, but PeopleCode only uses the number of parameters to differentiate among methods in a Java class with the same name.

In order to call this method from PeopleCode, we’ll need to use reflection. Reflection is how Java lets you determine class metadata (such as what methods it has and what parameters they take) at runtime.

Here’s what it looks like in action. This is broken into separate lines for clarity, but as you’ll see in the code, you can combine these where it makes sense.

&jReadArgTypes = CreateJavaObject("java.lang.Class[]", &jIOFileClass);
&jReadMethod = &jImageIOClass.getDeclaredMethod("read", &jReadArgTypes);
&jReadArgs = CreateJavaObject("java.lang.Object[]", CreateJavaObject("java.io.File", &sSourceFileName));
&jBufImage = &jReadMethod.invoke(&jImageIO, &jReadArgs);

This is easier to explain working from the bottom up. In order to call a method via reflection, we need to have the correct Method classinstance and use it’s invoke method. That’s what the 4th line is doing. The first parameter, &jImageIO, is the same object that we were trying to use before, and the second parameter is an array of parameters that invoke() will pass along to the “real” method.

Getting that parameter array is what line 3 does. When we have all of the values that are ever going to be in the array, then using CreateJavaObject with the braces, [], at the end of the class name is nicer than using the CreateJavaArray PeopleCode function. Mainly because we can pass all of the values in at once instead of setting them one by one as CreateJavaArray forces you to do.

We also needed to have the actual Method object. That’s what line 2 is doing. We call the getDeclaredMethod() method of the underlying Class object (this is the actual Java class that defines what a Java class is; chicken, meet egg) and pass it the name of the method that we want, along with array of the parameter types (not the parameter values!) that the method expects.

You can get the underlying Class object for any Java object by calling the getClass() method (there are examples in the code below). But when you have a JavaObject in PeopleCode that you obtained via GetJavaClass (instead of CreateJavaObject), then you actually have a PeopleCode reference to the class and not an instance of java.lang.Class. The PeopleCode reference allows you to call static methods on the class, but if you call getClass() on it, you’ll get an error. The secret to getting to a java.lang.Class instance for a particular class when you don’t have any instances of that class is to do something like this.

&jImageIOClass = GetJavaClass("java.lang.Class").forName("javax.imageio.ImageIO");

Now &jImageIOClass is an actual java.lang.Class instance, suitable for the reflection work that we’re doing.

Finishing things off, in line 1, we created the array of parameter types that we needed for the call to getDeclaredMethod(). The parameter types are specified by using their underlying java class, so you definitely want to be sure that you understand the difference between a java class and the java.lang.Class object which describes that java class.

Whew! That’s a lot of explaining to do just because PeopleCode doesn’t resolve all Java methods properly. What’s worse is that we’re not done yet. We now have another problem.

In the original line of PeopleCode, we called a method (“read”) that returns a Java object. Specifically an object of type java.awt.image.Bufferedimage. But we can’t use it as a BufferedImage object, because PeopleTools looks at the return type for invoke() and sees that it returns java.lang.Object, which is the base class for everything in Java. If you try to do something useful with &jBufImage (like get the dimensions of the image), PeopleTools will give you a “method not found” error.

Thankfully the underlying JVM still understands that this is a BufferedImage object and not just a java.lang.Object. So we can (read “have to”) use reflection again in order to use our BufferedImage. Of course, since we’re using reflection with BufferedImage, that means that any Java objects that we get back from reflected method calls are also misunderstood by PeopleTools (it will think that they are instances of java.lang.Object rather than whatever they really are).

So, once you fall into needing to use reflection within PeopleCode, you end up using a lot of it.
Believe it or not, it’s not so bad once you wrap your head around it. It took me longer to write this post than it took to write the code below since the extra work is essentially just some extra typing.

Obviously if you are doing a lot of Java/PeopleCode integration, then you’d be better off just writing a little bit of glue code on the Java side to avoid all of this, but when you’re just doing something quick (like using Java hashmaps instead of faking it with 2 dimensional arrays in PeopleCode), then this technique works well.

Finally, here is the actual code, along with the starting image (found in your PeopleTools directory) and the altered image.

Scroll box

Labels: PeopleCode, User

We had an interesting “Ask the Experts” question yesterday about how to disable the PeopleSoft page bar across the entire system. The page bar is what has the “New Window”, “Customize Page”, and “Copy URL to clipboard” links in it.

There is a personalization setting for the “Customize Page” link that can be defaulted completely off at the system level, but the other options can only be turned off by going into each component and changing these properties. Financials 8.9 has 6723 components in it. Not something that you want to do one at time, especially since changing these settings is technically a customization.

One potential workaround to this is to just use SQL and update the component properties manually, but you generally don’t want to muck around with making direct updates to the PeopleTools tables (and you’re still customizing a ton of objects – you’re just doing it faster).

The other potential workaround that can be used still involves a customization, but a less invasive one. It involves using a little CSS to hide the page bar. This one line of JavaScript will do the trick.

document.write("<style>#PAGEBAR { display: none; }</style>");

If you put this into one of the delivered JavaScript programs that is added to every page (which is a customization), then you’ll be set.

Note that you haven’t actually disabled this functionality, just hidden it. So if you’re really desperate to keep people from using the pagebar, then you will have to disable it on each component so that the backend knows that it’s disabled as well.

Labels: PeopleCode, Security, User

Application Engine can be fairly handy in a PeopleSoft developer’s toolchest. Aside from all of the useful batch processing things that it can do, it can also be useful for providing ways of running PeopleCode against a system directly from within Application Designer. This can be used for things like testing some PeopleCode or providing some developer productivity utilities. David Bain and I used to do some presentations on developer productivity for PeopleSoft developers, and taking advantage of Application Engine was one of the tips that we used to always mention.

An example utility is the version control work that we’ve been doing for our products at Grey Sparling. When you have a project that needs to be checked into the source code control system, the project needs to be split up from one large file into a multitude of separate files (the exact number of objects that you have in your project). We have an App Engine program that does this for us, but we needed a way to specify the exact project name to the program. In regular App Engine programs running on a server, you’d have some page for entering run control parameters and the program would look at these. But when you run the App Engine program directly from within Application Designer, you don’t have those facilities available to you.

So what we end up doing is a couple of things. First, we take advantage of the COM integration in PeopleCode and use that to have Internet Explorer provide a prompt with the list of projects in the database.

When you have an Application Engine program open in Application Designer, you can press the traffic light icon or select Edit->Run Program from the menu. You’ll get a prompt like

this.

I always turn on the save to log checkbox, and then press Enter or click OK. Once the program starts, Internet Explorer pops up the list of projects in the database and lets you select one. The project name that you select is then used by the rest of the Application Engine program to do it’s work.

Here’s what the code looks like:

SCROLL BOX

The IEPrompt function takes a title and a label and an array of choices and returns back the selected choice. We use a hidden form field as a flag for when the user has made their choice since we can’t actually catch COM events from within PeopleCode. The HTML that we generate from PeopleCode is not super fancy, but it gets the job done.

The other functions in the code are to assemble the list of projects from the database into a PeopleCode array and then to put the entire thing together.

How about if you wanted to supply the parameter yourself without getting prompted? Maybe you, as the developer, want to run this AE program as part of a bigger script. The answer is to just pass the parameter on the command line and use a little PeopleCode to parse out the values.

In order to do that, I ported over this C# based command line parser from The Code Project. It was easiest to port by utilizing Java from PeopleCode.

SCROLL BOX

The C# regular expression classes are fairly similar to what is available in Java. The only minor headache was that PeopleTools was having problems looking up one of the Java methods used (which we’ve seen before in previous blog entries), but that was fairly straightforward to get around. The workaround is to repeatedly compile one of the regular expressions instead of just once, but in this particular usage scenario, the overhead of that is so negligible that we don’t care.

As a side note : native regular expression support was added the 1.4 version Java Runtime Environment, so if you’re on an older version then you’d need to look at some extra libraries for adding regular expression support, or this code won’t work.

Labels: PeopleSoft

Since Java is the language of choice for the Oracle Fusion applications, I thought it would be nice to have some posts that show some good tips and tricks related to using Java within PeopleSoft today. As I mentioned in the previous post there are a few quirks in the way that Java and PeopleCode work together. Well, as Bill Cosby once said, “I told you that story so that I could tell you this one” 🙂

The quirk that we’ll cover today has to do with the way that the mapping between PeopleCode and Java datatypes works. I have a few quirks that I’ve known about for awhile, but I got bitten by this one just recently while working on a follow on to a previous blog entry about version control and PeopleTools. The idea was to show an example of using the JavaSVN java library from PeopleCode to be able to place application data under version control (application setup/configuration data, not something like Ledger entries).

The library works great directly from within Java code, but I hit some strange behaviour when trying it from PeopleCode, and it turned out that the fault is in the way that PeopleCode was passing Null into the Java side.

To simplify things, imagine that you have the following Java class that exposes these static methods.

package com.greysparling.demo;

public class JavaPeopleCode1 {

public static boolean IsObjectNull(Object test1) { if (test1 == null) { return true; } else { return false; } }

public static boolean IsStringNull(String test2) { if (test2 == null) { return true; } else { return false; } }

}

You’d think that these methods would each return True when you pass a Null from PeopleCode into them and False otherwise. When we call these from a short AppEngine program (side note: using AE is a great way to test out these sorts of quick test things) we see otherwise.

Local String &sClassName, &sPeopleCodeString;
Local JavaObject &demo;

&sClassName = “com.greysparling.demo.JavaPeopleCode1”; &demo = GetJavaClass(&sClassName); &sPeopleCodeString = “Testing”;

Warning (&demo.IsObjectNull(&sPeopleCodeString)); Warning (&demo.IsObjectNull( Null));

Warning (&demo.IsStringNull(&sPeopleCodeString)); Warning (&demo.IsStringNull( Null));

Here’s the output that we get from running this (miscellaneous junk from the log has been trimmed out).

False
True
False
False

Notice that last one? We would have expected that to return True since we’re passing Null. It turns out that any object type that PeopleCode has a direct mapping for (such as a PeopleCode string with java.lang.String), you can’t pass null. Or more accurately, if you pass Null from the PeopleCode side, you won’t actually get null on the Java side.

Annoying eh?

The workaround for this is to create some additional Java glue code and call that from the PeopleCode side.

Labels: PeopleCode

For something as fundamental as Setid indirection, it’s surprising how few people really understand how it works. Hopefully, this will shed some light on the subject.

Why, when, and how?

Setid indirection is the key to why PeopleSoft has been able to compete at the same level as Oracle and SAP. It allows complex organizations to use a single instance of the application, versus requiring multiple installs, each set up and configured differently. The Structure definition in Oracle Flexfields is intended to provide similar functionality (but less consistently).

Setid indirection was something introduced in PeopleTools 2, and to this day, amazes me that PeopleSoft was able to change something so fundamental in its products after the products were initially released. It’s also interesting that the approach for delivering it was to introduce it as a core component of the toolset, versus creating a standard around it.

So, we’ve talked about the when and why. Let’s talk about the how. The best way to explain the purpose of Setid indirection is to look at a complex company as an example. One I like to use is Virgin. This is because it’s pretty clear that the company has multiple disparate lines of business (and helps explain how it works.

So, let’s look at the high level structure of Virgin:

• It has a recording label.
• It has a series of retail stores.
• It has an airline.

We could also complicate matters by looking at the different countries it operates in, and some of the requirements there as well, but let’s get to that later.

So, there are 3 different lines of business. In this example, there are definitely certain things that need to be different across the different lines of business:

• There are probably different suppliers with different rules of operation.
• There are definitely different products that are delivered.
• There are probably different jobs, jobcodes, and unions.

However, in any organization, there are certain things that should be the same (to provide consistency where needed).

• There needs to be a common chart of accounts for reporting financial information.
• There usually needs to be a common fiscal calendar (again to allow consistent reporting of financial information).

Therefore, there are aspects of the business that need to be kept separate between lines of business, and aspects that need to be shared across lines of business.

Now, let’s add back in the complication of operating in multiple countries. Because each country has its own regulations and currency, some of those business rules also need to be segregated between countries, but also need to be shared across countries.

Enter: the Setid. The Setid is the means by which sets of values are given an identifier. The Setid is generally the topmost key of any table in a PeopleSoft application that does not hold transactions. Setids are managed at the table level (or, actually, at the group of table level). So, in the example of Virgin, you could see the following:

• One common setid for the set of account numbers used to represent the financials.
• 3 different setids for the different products sold by each line of business.

One way to understand setids is to understand the entities in the application that use it (i.e. the entities where it makes sense to both segment and share definitions across lines of business). These are:

• Entities that would be dimensions in an OLAP cube. These are things like customers, vendors, employees, products, and departments.
• Business rules that govern how processing is to occur in the application. These are things like payment terms, pay cycles, allocation rules, calendars, and timespans.

The other main type of table in a PeopleSoft application is the transaction or document. These entities are keyed by a corollary field, called Business Unit. You could think of the business unit as a set of books in financials, or a discrete instance of a set of transactions that stand on their own for other applications. Examples of business unit keyed tables are Journals, Ledgers (which are just an aggregation of journals), vouchers, receivables, orders, returns, and cases). In the OLAP world, tables keyed by business unit are generally facts in the system.

So, how to business units and setids relate to each other? Good question. Remember, that transaction tables are keyed by business unit. One could also think of a transaction as any business event that happens in an ERP application that needs to be captured or measured. Therefore, the role of setids is to control either the set of values used for things captured in a given transaction, or the rules used for processing that transaction.

This means that there is a relationship between a business unit, and the sets of values that are used for valid data, or for controlling processing (which are stored in tables). This is done using the tableset controls (which is accessed in the PeopleTools menu, under the administration sub-menu). The mapping of setids to business units occur with the following levels:

• There’s a level that groups similar tables together into a functional group (called record groups). This means that if there are 5 tables for storing or processing journals, you don’t have to map those tables individually, they are assigned a group and the mapping is at the group level.
• There’s a level called record group controls, that actually performs the mapping. When setting this up, you first provide a business unit value, and then assign setids for each record group that would be used for that business unit. The sharing and segregation occurs when you pick different setids for business units (or the same across business units). This means the following:
• If you use a single “share” setid across all business units for your account tables, then all business units will use that common set of values.
• If you use a setid for “recording”, one for “retail”, and one for “airline” four your product tables, then whichever business units you assign the “airline” setid will share a common set of products (you may be in a sharing situation if you set up a different set of books, or business unit, per country the airline operates in).

So, what happens when I use the application?

Another good question. Let’s build on the previous examples by looking at what happens when you enter a journal (i.e. a transaction).

When you go into the journal page, the first thing it does is ask you what business unit the journal is for. When you pick this value, you have identified what set of record group controls will be used for entering that journal. The first place you will se that is in prompting in the page. Pretty much any field, with the exception of the numeric fields, will use the setid indirection (i.e. the record group controls) to identify what values to use. One example may be the following:

• When you prompt on the journal source, the setid for sources may be mapped to “share”.
• When you prompt on the account, the setid for account tables may be mapped to “share”.
• When you prompt on the department, the setid for the department tables may be mapped to “retail”, and so on.

When you run any batch program, the first thing you need to supply in the run control is the business unit for the transactions to be processed. This will identify the business rules to be used for that process (such as balancing rules for posting journals).

Okay, what about reporting?

Another good question, especially for nVision. PeopleSoft reporting tools definitely need to understand about setid indirection, so that the appropriate values are returned when the report joins transaction data to the attributes of the trasaction (i.e. dimensional information).

However, reporting has two extra levels of complexity:

• It is possible, and quite often desirable, to show data across multiple business unites in a single report (a consolidated balance sheet is a good example).
• Trees are very important for aggregating data, and need to be shared in a manner similar to the detail values (for example, a tree rolling up department values only makes sense for the setid of those values).

So, from a reporting perspective, here is how setid indirection works:

• Query will automatically perform setid indirection when joining between a transaction table and a valid value (dimension) table, if that table is set as a prompt table in application designer.
• Query will perform setid indirection for cascading prompts, as long as the business unit prompt has a sequence prior to the prompts that depend on it.
• nVision will only do setid indirection for the business unit specified in the report request (even if the report displays multiple business units). This is very important to know, because it is the key to solving setid indirection issues in nVision).

Now, for trees. Trees are mapped separately from tables. This means that you have the ability to map the setid for each tree differently for each business unit. However, each business unit has a default setid, and if the default setid for the business unit is correct for the tree, you don’t have to explicitly map it.

More on nVision: in design mode of nVision, setids can become even more complicated. This is because an nVision report layout can be run for different business units (which are supplied at runtime on the report request). This means that in design mode, you don’t know what business unit to use for setid indirection (and that is why in the “layout options” dialog in nVision, you can pick the setid to use for prompting of values when designing the report. In a scenario with multiple setids, this is important to know, because you may have to change the setid when adding criteria for different fields.

Applying this to the real world

Probably very few of you will be designing the business unit and setid structure of your PeopleSoft application. However, many of you will need to put this to use when writing reports, creating trees, or even maintaining the list of vendors, customers, accounts, and departments.

The first thing to know is that it’s very easy to be tripped up by setid indirection. I’ve handled lots of calls from customers who don’t get any data in their reports and think it’s a problem with the reporting tool. In the end, it’s usually either a setid indirection issue (i.e. the business unit resolves to a setid without any data for a given field), or an effective dating issue (i.e. the as-of-date resolves to a date before the first effective date).

The second is figuring out how to work around limitations in setid indirection. Many organizations will use setid indirection for security purposes (i.e. ensure that only a certain set of departments can be entered for a given business unit). Unfortunately, if you want to build a tree across the departments, the tree can only refer to a single setid (and, yes, because the tree is built on the department, which has its own definition for tableset mapping, the tree is resolved independently of the table it is built on). The only way to build a tree across multiple setids, is to create a single setid with all the values in it (assuming that the department values mean the same thing across setids). I call this “creating a super-setid”. This can be done through PeopleCode (either in an app engine program if batch is acceptable, or in SavePostChg, if it needs to be done immediately). Probably the easiest way to accomplish this is to create a view that gives a common setid for all values. You can then build the tree on the super-setid. In nVision, to get to the super-setid, you can create a super-business unit that has all the appropriate tableset sharing options for the super-setids. Because the business unit filtering in nVision can be different than that of the report request, this is possible.

Labels: Financials, PeopleSoft