Search This Blog

Thursday, November 26, 2009

Special Actions

There are certain action in HRMS which can make the Inactive rows in the Job data active. Identification of these Actions will be helpful while development phase. Below is the query to identify those actions.


 

SELECT

A.ACTION

, A.ACTION_DESCR

FROM

PS_ACTION_TBL A

, PS_ACTION_STAT_TBL B

WHERE A.ACTION=B.ACTION

AND A.EFF_STATUS='A'

AND A.EFFDT= (SELECT
MAX (C.EFFDT)

FROM PS_ACTION_TBL C
WHERE A.ACTION=C.ACTION)

AND B.HR_STATUS_VALID='I'

The setting is done under
Setup HRMS
Product related
Workforce Administration
Actions

Here under the Organizational Relationship scroll, there is field
'Valid if Previous HR Status is: '. Providing Inactive will make
the action capable of
changing the inactive HR status active

Wednesday, November 25, 2009

CI Development FAQ

This FAQ is to assist in troubleshooting problems you might have while creating and programming PeopleCode with component interfaces.

Remember the most important tip with component interfaces: Component Interfaces mimic component logic.

Errors in the component will be replicated in the component interface. So you need to test using the underlying component. The majority of component interface development issues revolve around not understanding how the underlying component works.

When should I be using a component interface?

If you need to insert/update/delete data through PeopleCode, and your PeopleCode will require replicating a lot of existing business logic that already exists in a component then a component interface is the best approach. Once you learn how to use them, they will be the fastest and most robust solution for such cases.

If you are performing simple changes, then using a CI becomes overkill as there is an overhead to using a CI on both the system and on development time. Similarly, large amounts of batch processing may be too much for a CI to handle or may be considerably slower using a CI.

When I create my component interface, why can't I see any Getkeys?

The search record for your component does not have any search keys defined. Go back and define search keys as these are used as Getkeys.

Why are my Findkeys missing certain fields?

Findkeys are derived from search and alternate search keys on the component search record. If you have a key but it is neither a search key or alternate search key, then it will not be available as a Findkey.

When I test my component interface, why do I get a "Not Authorized" error?

Check that your component interface is included in a permission list that you have access to. Also check that you have access to all possible methods (Get, Save, Create, Cancel, Find). If you delete a component interface through application designer, it is also removed from any associated permission lists so this may be another reason why your new component interface (with the same name) is not authorized any more.

Also, make sure you are not trying to do something in your code that the CI can't do. For example, if your CI doesn't have a create method, you can't set security for it, so you will get a not authorized error if you have code that attempts to perform a create. This is because the authorization error is usually the first error to be displayed.

When automatically building a component interface, why are some fields missing?

Sometimes application designer gets it wrong. For instance, if the same field exists in scroll 1 and scroll 2, it may put the field in just one scroll level. It may also have problems with certain key/search key/alternate search key fields between the search record and the scroll level records. Normally you just need to check your CI and add any missing fields.

Why does my component interface not have a Create method?

Create is only available if the underlying component allows Add mode. If it doesn't then you can't create anything in the component, and thus you can't create anything using the component interface. The create method will be disabled in the component interface properties.

Can I override my Component Interface methods?

Yes, you can write additional PeopleCode for your component interface methods. Simply go to the method (Cancel, Find, Get, Save, Create), right-click and select view PeopleCode.

My PeopleCode didn't copy any data to a collection. Why not?

Make sure that all the fields that you are copying to in a collection actually exist in the component interface! If any are missing you can get unexpected results or none at all.

My PeopleCode didn't update the value of a field. Why not?

Check that the field is actually in your component interface. Also make sure that the field properties are not set to Read Only Access. (double click on the field in the component interface).

The same field is in different scrolls but they do not have the same value

For example, at scroll 0 you might have the field EFFDT set to 01/01/2009. You want the EFFDT field to get the same value in scroll 1, so you write code to set it from the value in level 0 but it isn't working. If this is the case, check the field name in the component interface, you may find that PeopleSoft has automatically renamed it. For example, it might be EFFDT at level 0 but something like EFFDT_1 in level 1 of the component interface.

What underlying tables define security access to Component Interfaces?

The association between a permission list (CLASSID) and a component interface (BCNAME) is stored in the PeopleTools table PSAUTHBUSCOMP.

What delivered role / permission gives access to all the delivered CIs?

The role is Standard Non-Page Permissions. The permission list is HCSPCMPINT.

I get the error No Rows Exist for Specified Keys

But I have double checked that the key values being passed are correct and exist in the database. What's going on? This could be row level security at work. Don't forget that a CI uses the security implemented by the underlying component. Are you sure that the user running the CI has the appropriate row level security to view the data? Confirm by querying against the search record of the underlying component. Row level security may also be at work if you are getting errors entering valid values into a field where the prompt table relies on row level security.

Batch Scheduling

The People Tools process scheduler allows you to run jobs on a regular schedule. This wiki page goes through the two ways to set up batch scheduling using recurrences.

Batch Scheduling Terminology

However, before setting up batch scheduling, there's some terminology that you'll need to be aware of:

·       A job is a collection of one or more processes that run together.

·       A recurrence defines a regular event. For example a daily recurrence would be for every day of the week. A recurrence includes a start request date and time, an end request date and time, and repeat information (e.g. repeat every 1 hour for 24 hours).

·       A schedule job set definition is a job that has been scheduled to run on a regular basis (with a recurrence) using specific run control(s) and parameters.

The navigations you'll need to know are:

·       People Tools > Process Scheduler > Jobs

·       People Tools > Process Scheduler > Recurrences

·       People Tools > Process Scheduler > Schedule Job set Definitions

Recurrences through the Process Scheduler

The first (and simplest) way to schedule something to run on a regular basis is to select a recurrence when you schedule a process through the process scheduler. For example, if you wanted to run the system audit process through system process requests each day, navigate to People Tools > Process Scheduler > System Process Requests then select the system audit process to be run. At the process scheduler request page, you would then select a recurrence. Here's an example screenshot.

You can use this technique for any process you can run through the process scheduler and gives you access to this page first.

Now if you go to the process monitor you'll see your SYSAUDIT process running as per normal, but it now includes a green recurrence icon after the process name that looks like this:

Note that you will probably end up with two items in the process monitor. The first item is your request running today and the second will be the queued request for tomorrow (or sometime in the future depending on your recurrence). You can see this in the screenshot above. Process instance 2173557 was initiated on 05/01/2009 and has run to success and posted. Process instance 2173558 will run on 06/01/2009 at 12:02:00 AM and is currently set to queued.

You can also click on the green recurrence icon to manage the process further. This gives you:

·       The ability to hold (suspend) the recurrence. This will change the run status to Hold. The ability to stop the request recurring

·       The ability resume the recurrence if it has been stopped or set to hold.

·       A request list with a history of the process requests that have already run through this recurrence and if/when the process will recur next

Using this method works well for simple recurrences for a single process or jobs. If you want more advanced options such running a job with difference run controls for each process then you'll need to use a schedule job set definition.

Schedule Job set Definitions

A schedule job set definition needs to run on a job. So the first step might be to create a job with one or more processes in it. For this example, I’m using the delivered All Process Types ALLTYPES Sob. This runs a COBOL, a Crystal Report and a SQR report and is available through system process requests.

Once you have a job, navigate to the Schedule JobSet Definitions page (PeopleTools > Process Scheduler > Schedule Jobset Definitions) and select Add a New Value. For this example:

·       The schedule name will be All Types Example

·       The job name is ALLTYPES (and needs to exist first)

By default a new jobset definition has a status of inactive. You'll want to leave it inactive until you know it is working correctly - especially for more volatile processes. You'll need to also specify a run control ID. Note that this is user specific - the user that creates the schedule jobset is the one that the processes will be running under. For this reason, a lot of organizations create their own batch scheduling user (e.g. PSBATCH) for the specific purpose of running batch processes and jobs. All run controls, and schedule job set definitions are then configured using that user. In this example, I'm just using the default run control ID which is my operator ID as this will always exist.

The next step is to set a begin date and time. This will be the date and time from which the job set will begin. You'll also need select your recurrence (daily for this example). You can also specify a process scheduler server if specifically required. However, it might be better to let People Tools decide what server to use. In this example, the ALLTYPES job includes a crystal report that will need to run on an NT server, however, the other processes should run on a Unix server.

Next, you'll need to specify schedule job set items. This is effectively where you choose the run controls to be used by your job/processes. Use the left hand side tree view to expand your job (click on the icons) and then for each of the three processes, specify a run control ID (I’m using my operator ID). Note that these run control IDs need to already exist and be configured before you set them up in the job set items.

Also, for the ALLTYPES job, the crystal process (XRFAPFL) needs to have its server settings changed so that the server option is Specific Server and the server name is PSNT. This is because crystal reports can only run on Windows process schedulers servers.

The final tab, schedule job set requests, is for informational purposes. It tells which requests have already been processed and when the next one is queued to run.

Before you set the job set definition status to active, use the Run Now button to run the job set definition once to check it runs to success. Also note that when you save the schedule job set definition, it may clear the recurrence name - this appears to be a bug?

Go to the process monitor and you should see the ALLTYPES job running, and hopefully it runs to success for you. Also, you should see the job set definition calendar icon. Clicking on this icon tells gives you all the schedule job set requests.

Note that your job set definition may change status to completed. This indicates that the job set has finished running and there are no more recurrences for it to process. The job set will not run again with a status of completed. Change this back to active and ensure that your recurrence settings are correct.

Recurrences

In both examples, I've been using the Daily recurrence, which I believe is delivered with People Tools. However you might want to create your own recurrence with a different recurrence pattern, and request settings.

To do this, navigate to People Tools > Process Scheduler > Recurrences, add a new value and give it an informative name.

You'll need to choose between the options, schedule next recurrence when current request is initiated OR prior recurrence has completed. This essentially specifies whether to schedule the future recurrence before or after the current recurrence process request has completed. You can choose a regular recurrence pattern, or use the customize dates option to specify one or more values in the run on specific dates grid. For example if you have a process that runs on two specific dates in a given calendar year, you can set them up this way.

The start and end request options are straightforward. The repeat is used to repeat a process within the recurrence pattern. For example if you might want your process to run every day, however, on each day you may also want it to run every 6 hours for the 24 hours of the day. That is, run 4 times each day (24 hours in day / repeat every 6 hours = 4 repeats).

The do not schedule processes missed from the recurrence pattern prevents the process scheduler from playing catch up if the start request date/time is before the current date/time. If you want processes to play catch up, then leave this unchecked, otherwise, set it to checked.

Running Application Designer on Linux

One of the requests for enhancements in the PeopleTools Product Strategy group on Oracle Mix is for the ability to run Application Designer on Linux. This was actually discussed in the session that the PeopleTools Product Strategy group hosted at Oracle OpenWorld this year.

Although most people think it's not possible, read on and see that you
can have an Application Designer icon on your Linux desktop that works just like Windows. But first, a bit of relevant history.

Read More…

Thursday, November 19, 2009

DoModal and EndDoModal

It was an opportunity for me to understand how Do Modal and EndDoModal works in sync. The learning is, Do modal is like a function. When the DoModal peoplecode is confronted the control immediately flows to the page specified. Consider there is some code written below the Do Modal call. it won't get called instantly. Rather when the EndDoModal triggers, the control will come back to the page where DoModal is called and the successive codes will be fired. Though it may look simple, I learned it through a hard way.

Sometimes, simple concepts consume a lot of time to understand.  Hope this learning will help you guys too

Monday, November 16, 2009

PeopleCode Built-in Functions

ActiveRowCount()
ActiveRowCount returns the number of active (non-deleted) rows for a specified scroll area in the active page

FetchValue()
FetchValue returns the value of a buffer field in a specific row of a scroll level.

FieldChanged()
FieldChanged returns True if the referenced buffer field has been modified since being retrieved from the database either by a user or by a PeopleCode program. This is useful during SavePreChange or SavePostChange processing for checking whether to make related updates based on a change to a field.

PriorValue()
PriorValue is used in FieldEdit and FieldChange PeopleCode to obtain the prior value of a buffer field that the user just changed. It returns the value that was in the buffer field before the user changed it, not the value of the field the last time the component was saved.

SetDefault()
SetDefault sets a field to a null value, so that the next time default processing occurs, it is set to its default value: either a default specified in its record field definition or one set programmatically by PeopleCode located in a FieldDefault event. If neither of these defaults exist, the Component Processor leaves the field blank.

SetDefaultAll()
SetDefaultAll sets all occurrences of the specified recordname.fieldname within a scroll to a blank value, so that the next time default processing is run these fields will be set to their default value, as specified by the record definition, or one set programmatically by PeopleCode located in a FieldDefault event. If neither of these defaults exist, the Component Processor leaves the field blank

UpdateValue()
UpdateValue updates the value of a specified field with the value provided. The value must be of a data type compatible with the field.

DiscardRow()
DiscardRow prevents a row from being added to a page scroll during Row Select processing. This function is valid only in RowSelect PeopleCode. When DiscardRow is called during RowSelect processing, the current row is skipped (not added to the scroll). Processing then continues on the next row, unless StopFetching has also been called, in which case no more rows are added to the page

ScrollSelect()
ScrollSelect, like the related ScrollSelect functions (ScrollSelectNew, RowScrollSelect, and RowScrollSelectNew) reads data from database tables or views into a scroll area on the active page.

ScrollSelect automatically places child rows in the target scroll area under the correct parent row in the next highest scroll area. If it cannot match a child row to a parent row an error will occur.

SortScroll()
SortScroll programmatically sorts the rows in a scroll area on the active page. The rows can be sorted on one or more fields.

ScrollFlush()
ScrollFlush removes all rows inside the target scroll area and frees its associated buffer. Rows that are flushed are not deleted from the database. This function is often used to clear a work scroll before a call to ScrollSelect.

MsgGet()
The MsgGet function retrieves a message from the PeopleCode Message Catalog and substitutes in the values of the parameters into the text message.

MsgGetText()
The MsgGetText function retrieves a message from the PeopleCode Message Catalog and substitutes the values of the parameters in paramlist into the text message. It returns the resulting message text as a String data type.

MsgGetExplainText()
The MsgGetExplainText function retrieves the explain text of a message from the PeopleCode Message Catalog and substitutes the values of the parameters in paramlist into the explain text. It returns the resulting message explain text as a String data type.

Prompt()
Prompt displays a page prompting the user to insert values into one or more text boxes. If the user cancels the page, any entered values are discarded and the function returns False. When the prompt page is displayed, the text boxes are initially filled with default values from the fields in fieldlist.

WinExec()
WinExec executes a Windows executable file .(COM, .EXE, .BAT, or .PIF) specified in the command_line string. It is suitable for executing short batch-processing jobs that will complete execution and terminate, returning control to PeopleTools.

ScheduleProcess()
ScheduleProcess schedules a process or job, writing a row of data to the Process Request table (PSPRCSRQST).

RemoteCall()
RemoteCall is a PeopleCode API function that provides a means of calling a Tuxedo service from a PeopleSoft application.

CallAppEngine()
To call a particular Application Engine program synchronously from a page using PeopleCode, you need to use the CallAppEngine() function in your SavePreChange or SavePostChange PeopleCode.

ChDir()
ChDir, like the DOS ChDir command, changes the current directory on a drive. The drive and the directory are both specified in a path string.

GetCwd()
GetCwd is used to determine the current working directory on the client.

OnlyOne()
OnlyOne checks a list of fields and returns True if one and only one of the fields has a value. If all of the fields are empty, or if more than one of the fields has a value, OnlyOne returns False.

Private Cloud: A Natural Evolution for Enterprises


Cloud computing has been enjoying a splash of media attention. But what IS the cloud? And how can your enterprise get its benefits without the risks?

Cloud computing is a relatively new term for computing platforms that provide flexible, scalable resources that are accessed over a network. Cloud computing offers a powerful, highly efficient, and flexible service to business users, who can tailor it to their requirements without needing to know anything about the underlying technology.

There are many "public" cloud computing services, such as Google App Engine and Amazon EC2, that offer businesses a way to create and access applications with no infrastructure setup, minimal management, and low up-front costs. The challenges of these public cloud services can become evident, however, when you attempt to integrate them with other applications in your infrastructure, or when you need to keep tight control of your data or quality of service.

Cloud Inside the Firewall
With a long history of grid computing expertise and the industry's most complete and integrated middleware, Oracle offers IT departments the ability to provide the benefits of a cloud—elastic computing capacity and self-service deployment—as a private or internal service, avoiding some of the downsides of a public cloud.

"A private cloud is a natural, evolutionary step for the enterprise," says Alex Andrianopoulos, vice president of product marketing for Oracle Fusion Middleware, Oracle. "Using an integrated set of Oracle technologies, an IT team can provide the departments and divisions throughout an enterprise with a platform that delivers all the advantages  of cloud computing, but allows them greater freedom to integrate current resources and control quality and security."

A platform-as-a-service (PaaS) private cloud is an architecture for centralized, shared resources, internal to the enterprise, that allows departments to rapidly deploy and easily manage applications. The following Oracle Fusion Middleware technologies make it possible:

  • Application grid is a foundation architecture that provides the dynamic resource adjustment that gives PaaS cloud computing its elastic qualities. Application grid leverages the capabilities of several Oracle Fusion Middleware components, including Oracle WebLogic Server, the world's No. 1 application server; Oracle Coherence, the industry's premier in-memory data grid; Oracle Tuxedo, the industry's No. 1 distributed transaction processing platform; and Oracle JRockit, the world's fastest Java runtime.
  • Oracle SOA Suite and Oracle Business Process Management Suite allow developers to build and orchestrate reusable services and components, so business users can quickly assemble and manage applications in the PaaS cloud. The integrated, best-of-breed Oracle SOA Suite helps developers rapidly design, deploy, and manage Java services, while the complete set of tools in Oracle Business Process Management Suite allows them to create, execute, and optimize business processes.
  • Oracle WebCenter Suite provides your PaaS private cloud with a self-service portal interface, and it gives business users the ability to quickly assemble user interface components into composite applications and tailor cloud resources to their needs. The suite offers a complete, open, manageable platform that empowers both developers and business users to build, deploy, and evolve next-generation composite applications, user interface components, and portals.
  • Oracle Identity Management allows business users to centrally manage identities and security for both building and using the various applications in a PaaS cloud. Oracle offers a complete, best-in-class suite of identity management solutions for controlling the end-to-end lifecycle of user identities across all enterprise resources, both within and beyond the firewall.

These technologies provide a way for your enterprise to adopt a PaaS private cloud incrementally by progressing through various levels, or degrees of "cloudness," such as self-service enablement, elastic capacity, and so on.

"Working toward the goal of a platform-as-a-service private cloud is a great way to begin integrating your current architecture so your business can have more flexible business processes," says Andrianopoulos. "It even makes it easier to bring public cloud services into the mix with consistent management and security."

Learn more about building your own platform-as-a-service private cloud:

Thursday, November 5, 2009

Understanding Row Level Security

I went through the post below from Grey Sparling Solutions Blog. This post explains the concept of row level security in a very understandable way. Go through the same.

Lot of the application groups within PeopleSoft provided their own row level security setup - HR with the security based on the department tree being a great example. The Financials group went beyond just one type of row level security "out of the box" - I forget how many choices they offered - there were several different common chart of account fields (business unit, department, account, etc.). With the application teams providing row level security in the vanilla install, many customers didn't realize that it is possible to handle row level security differently.

One thing that I always found helpful in customer meetings when this topic came up was to go through exactly how the PeopleTools component processor used search records to handle security. Once you understand that, then you can best decide if changing the row level security that the application groups provide makes sense from a cost/benefit perspective. And there definitely is a cost in the current PeopleTools - fixing row level security so that it was brain dead simple for customers to pick and choose how they wanted to implement it was one of the big ticket items planned for PeopleTools 9.

First, a quick side journey into the component processor (catch me at a conference or user group meeting sometime and I'll tell you the inside story on where the "component" name came from - it'll make you laugh). The component processor is truly the guts of PeopleTools - it is what handles the business logic, database commits, etc. for just about every page that you see in a PeopleSoft application. PeopleSoft components are parent/child hierarchies of database records that automatically get pulled together as one unit of work from the application developer's perspective.

There is one row of data at what is called Level 0. There can be up to 3 levels beyond that. Each level is required to have the same unique keys as the level above it, plus at least one additional unique key field to identify what makes the rows in that level unique.

For example, if a customer master record is at level 0 and has CUSTOMER_ID as it's unique key, you might have customer contacts at level 1 keyed by CUSTOMER_ID and CONTACT_ID. Customer contact phone numbers might be at level 2, keyed by CUSTOMER_ID, CONTACT_ID and PHONE_ID.

The data at each level comes from the key values above it. If the CUSTOMER_ID at level 0 is 1234, then the component processor would automatically use 1234 for CUSTOMER_ID for selecting data into level 1, and so on, down to level 3 (there are ways to override this, but that's how the default behaviour works).

The important thing to keep in mind here from the perspective of row level security is that populating the key fields in the level 0 record drives everything else. This is what the seach dialogs are responsible for.

When you first enter a component via the browser you get a page asking you for whatever the key fields are. In this case it would just be CUSTOMER_ID, but if the level 0 record has multiple key fields, then you'd get prompted for those as well. If you don't know the values, you click Search and get presented with a list of valid values.

But where does that list come from? Each component has what is known as a search record. The component processor uses the search record to supply the values for the key fields of the level 0 record, which then flow down to the child levels.

Any rows of data that the search record returns to the user gives the user access to that data.

So if you wanted to limit someone to only look at active customers, then you could create a view that had the same key fields as the customer master record, but had a WHERE clause like " WHERE CUSTOMER_STATUS='A' ". The view would only return active customers, which means that no one could get into that component to look at an inactive customer.

The component processor recognizes a couple of "special" fields on search records - OPRID and OPRCLASS. If the field OPRID exists on the search record, it automatically gets filled in with the current logged in user's ID. If the field OPRCLASS exists on the search record, it gets filled in with the Row Level Security Class (Permission List) that is specified on the user's security profile.

And that's essentially it for how the component processor handles things for row level security. There are a couple of PeopleCode events that fire when a search dialog is initialized (SearchInit) and when the user clicks "Search" (SearchSave), but those are not really intended for row level security. I'll write more about that in the future.

So how does the department tree security in HR work then? The HR group created a setup page that lets you pick a security class (permission list) and a tree node from the department security tree. For each one of these combos that you select, you decide whether access is granted or denied. All of this data is stored in a table. The HR team then created a view that links this table, the tree manager tables, and the employee department data together. The component processor automatically plugs in the row level security class for the user when selecting from the view, which limits what the user can see.

Financials works in a similar fashion, although the performance of the trees in the view didn't work well enough for them so they ended up with flatter security structures. The HR team actually delivered a couple of de-normalizing utilities for larger customers to deal with this issue as well.

So, how do you change that delivered security?

You could clone the delivered security views (and the tables/pages that they use to maintain the data). I helped a customer once do this where they wanted to use the position tree instead of the department tree.

You can also create your own security structures that do not mimic the delivered ones. All that matters is that you have a table or view that will return the key values that a user has access to. All that matters is that the search record has the same key fields as the level 0 record in the component that you're securing and that you either have the OPRID or OPRCLASS there to provide security.

There are no restrictions about what data the search record looks at or how that data gets maintained. For example, you might have some batch job that updates the table based on security defined in some totally different system. Or maybe someone has to request access to look at some data and security administrator will update the table that the search record looks at. I know of one customer that wanted to have their call center employees only be able to look at customer data when a call came in. So right before the server would send the screen pop for the customer data down to the agent's desktop, the security tables would be updated for that user to have access. As soon as the call was over, then access was taken away.

So the development of a new search record/view is one cost. This includes not just the technical development, but the auditing to make sure that it works properly as well.

Another cost is that the search record is attached to the component definition. So if you update a whole bunch of components to point to different search records, then you have to keep track of that at upgrade time. This is less costly than doing things like changing delivered code since you just need to add one extra step in your upgrade - update any changed components again. Not really a lot of thinking/analysis involved. But, a lot of customers like to run as close to vanilla as they can, so it is worth keeping in mind.

Of course, you don't want to be so afraid of a small cost at upgrade time if your business requirements need different data level security. I talked with a customer once that had turned off row level security in their Purchasing implementation because the delivered methods didn't work for them. Their auditors were complaining (and this was even before all of the current SarbOx push) and they wanted to know when Purchasing would support what they wanted. Changing the row level security is not so hard/costly that you'd want to run without security.


 

Tuesday, November 3, 2009

PIA Styles


I like this post on different color schemes for PIA.Infact i tried for a good design and learned few chapters on how it cannot be done :) It was quite surprising and really appreciable fact that someone had done that creation and i could read it from the post below

http://peoplesofttipster.com/colour-schemes-v2/

A change is necessary. Check it out

My Tech Speak Quick Updates

Creating Tabbed Grid:

Creating and calling functions

Populate Data on a button click

Creating the CI in easier way and Save the develop...

Changing the Order of fields in a CI

Query Drilling URL Feature in People Tools 8.50

A small thought that everyone knows !

AJAX with PeopleCode

Oracle's Ellison: Fusion Will Dawn in 2010

Oracle openworld 2009 live stream


 

Problem with CSV file

Sometimes while CSV reports are created using people code, there will be a problem if there is comma in any of the fields. The second part of the field after the comma gets printed on the next column and the whole report will get messed up. While i had this problem i thought i need to write a separate code to handle this situation. Parsing through the field and get the comma, replace it by space and so on.

I didn't know that there was a delivered function Substitute to do this job for me.

Substitute (old_val, new_val)

The Substitute method replaces every occurrence of a value found in an array with a new value. To replace an element that occurs in a specific location in an array, use Replace.

If the array is one-dimensional, Substitute replaces every occurrence of the old_val in the array with new_val.

If the array is two-dimensional, Substitute replaces every sub array whose first element is equal to old_val, with the sub array given by new Val.

The following example changes the array &A to be ("John", "Jane", "Hamilton" ).

&A = CreateArray ();

&A[1] = "John";

&A[2] = "Jane";

&A[3] = "Henry";

&A. Substitute ("Henry", "Hamilton");

Nice function right?

Monday, November 2, 2009

Load Lookup Arrays in SQR

How to improve the performance of the SQR using Load Lookup Arrays?

For Simple database related validation, we will fire a SQL to check the sanity of data. If this data validation is to be fired for each and every row of data, then it is better to go for Load-Lookup Arrays. This is like Prompt table validation in the case of Online Pages.

In this way, we can reduce the database hits and thereby increasing the Performance of the SQR Program.

For Eg: If we want to validate the Earnings Codes present in the file, we can use these arrays to validate the Earning Code data.

Generally we will use the below SQL to validate the ERNCD data

SELECT PET.ERNCD, PET.EFFECT_ON_FLSA, PET.ADD_GROSS

FROM PS_EARNINGS_TBL PET

WHERE PET.EFFDT= (SELECT MAX (EFFDT) FROM PS_EARNINGS_TBL

                                  WHERE PET.ERNCD = ERNCD

                                   AND EFFDT <=(SYSDATE)

AND PET.EFF_STATUS =”A”

AND PET.ERNCD = $Erncd

In order to avoid these database hits for each and every row of data, we can the below code to load all the valid Earning Codes at once from the Database to Load Lookup Array Name EARN and then use the array to validate the input ERNCD data.

 

! Lookup array for EARNINGS CODES

Let $where = ‘PET.EFFDT         = (SELECT MAX (EFFDT) ‘||

‘FROM PS_EARNINGS_TBL ‘||

‘WHERE PET.ERNCD = ERNCD ‘||

‘AND EFFDT       <=SYSDATE) ‘||

            ‘AND EFF_STATUS   =”A”’

 LOAD-LOOKUP

      NAME                     = EARN

      TABLE                    = ‘PS_EARNINGS_TBL PET’

      KEY                       = ERNCD

      RETURN_VALUE     = ERNCD||’-'||EFFECT_ON_FLSA||’-'||ADD_GROSS

      WHERE                  = $where  


      QUIET

 

In this case, KEY (This is nothing but Input Data value) is ERNCD and RETURN_VALUE (Output data value) is Combination of ERNCD, EFFECT_ON_FLSA and ADD_GROSS database field’s value.

! Checking Load lookup array for data validation

Lookup EARN $input_data $output_data

Note: It is good to initialize these Load Lookup Arrays in the SETUP Section related procedures.

Getting Current and Previous EFFDTed Rows

Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.

SELECT

 A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT

FROM    PS_JOB A, PS_JOB B

WHERE

 B.EFFDT =(SELECT MAX(A_ED1.EFFDT) FROM PS_JOB A_ED1

WHERE B.EMPLID = A_ED1.EMPLID

AND B.EMPL_RCD = A_ED1.EMPL_RCD

AND A_ED1.EFFDT < A.EFFDT)

AND B.EFFSEQ =(SELECT MAX(A_ES1.EFFSEQ) FROM PS_JOB A_ES1

WHERE B.EMPLID = A_ES1.EMPLID

   AND B.EMPL_RCD = A_ES1.EMPL_RCD                            

AND B.EFFDT = A_ES1.EFFDT)

AND A.EMPLID = B.EMPLID

  AND A.EMPLID='KA0002′

Is this the only way to get the results?????

Here is the Simple and Advanced way to achieve the same results without using Self Join

SELECT


A.EMPLID, A.EFFDT CURR_EFFDT, LAG (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) PREV_EFFDT

FROM     PS_JOB A

WHERE      A.EMPLID='KA0002′

Like this, we can also get the Current and Following EFFDTed rows by using LEAD Function

SELECT

A.EMPLID, A.EFFDT CURR_EFFDT, LEAD (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) LEFFDT

FROM PS_JOB A

WHERE A.EMPLID='KA0002′

Note: Above SQL holds good for Oracle DB. In Oracle, The Function LAG/LEAD is called Analytic Function, which is there right from Oracle 8i onwards.

Generating Email URL

&TempURL = &TempURL | GetURLString(”<COMPONENT NAME>”, “<MARKET>”, “<PAGE NAME>”);

&TempURL = &TempURL | “/?EMPLID=” | &emplid;

&TempURL = &TempURL | “&EMPL_RCD=” | &empl_rcd;

&TempURL = &TempURL | “&ACTION_DT_SS=” | &act_dt;

Try the above snyntax for generating the URL. Replace all the <> with relevant values.

The EMPLID, EMPL_RCD and ACTION_DT_SS are the search keys to the component given in the above statment.

Provide your search keys with a /? for the first key and & for the others.

Strategic Structure Alignment

Found the following post interesting from Grey Sparling solutions blog.

Strategic Structure Alignment... pretty important sounding term (that probably doesn't tell you that much). It's something that probably every customer struggles with, but doesn't realize what it is.

Strategic Structure Alignment is all about making sure that the important organizational constructs are aligned across different applications and domains. In 90% of the circumstances, this is looking at how the HR responsibility structure maps to roles that people play in other systems. Throughout my years of experience with PeopleSoft applications, I've had many discussions with PeopleSoft customers about how the department chartfield in financials can be aligned with the HR employee structure (in fact, one of the remaining baby bells has a whole system dedicated to this, and we've put in enhancements to tree manager to help them handle distributed nature of this structure alignment).

There are other areas, however, where this can be applied:

  1. Row/Role level security in reporting
  2. Sarbanes Oxley compliance delegation
  3. Responsibility definition in front-line applications (such as call center and sales force automation

Basically, any place where the official reporting structure is needed to be used to drive use and functionality in an application. This is different from a knowledge network, which was discussed by Bob Stambaugh at the Northern California RUG on friday.

So, how do you align strategic structures? There are several approaches:

  1. Create one MASTER structure that gets replicated to other systems. This is the approach that the baby bell took. All information was maintained in the HR org tree, and then the tree was replicated to the other database for use in reporting. They, then made sure that the chartfield used by the tree had the appropriate values for the tree to link to (which could be a challenge). Another limitation is that you must put entries in the HR org tree that don't have any HR function in order to use them in financials.
  2. Another option is to replicate the departments in HR to financials, but not the hierarchy itself. This allows a financial organization structure to exist that does not have to exactly match the HR one (but ensures that all departments are represented financially).
  3. A third option is to synchronize the maintenance of trees between the two applications using ERI. In other words, as the user is making changes to the org tree in HR, he can drill into the appropriate tree in Financials and determine how that should be reflected there.

Because of the new requirements of Sarbanes Oxley, it is my suggestion that customers do a combination of the three approaches above. This is because the HR responsibility hierarchy will be the key to ensuring Sarbanes Oxley compliance. A good example is managing expense accounts. If you can drill down the HR responsibility hierarchy to see which people are responsibile for how much of that expense, it provides accountability and visibility into the accuracy of the financials. To accomplish this, one merely needs to provide a means for linking the HR org structure to the department chartfield in financials and drilling in nVision into a drilldown layout that uses tree nPlosion to the HR org tree.

http://blog.greysparling.com/2005/07/strategic-structure-alignment-part-1.html

PeopleTools 8.50 RSS Feed Publishing Framework

PeopleTools 8.50 RSS Feed Publishing Framework allows us to create RSS feeds against tables and queries.

Why is RSS So Important?

Put quite simply, RSS solves probably the most important problem that users want solved in reporting (and it is not to be shown fancy charts with drag and drop). With all the information that they have to wade through every day, users just want to be told when something that's important to them changed. With PeopleTools 8.5, your users how have the ability to subscribe to your application data and get told when something they care about changed.

Read more about the feature from grey sparling solutions blog

Recruitment tables

HRS_LOCATION --INTERVIEW LOCATION DETAILS

HRS_CONTACT --COMPANY CONTACT DETAILS

HRS_JO_RCTR_RL ---RECRUITER ROLE

HRS_JO_DSC_TYP --POSTING DESCRIPTIONS

HRS_JO_PST_LIB --POSTING DESCRIPTIONS LIBRARY

HRS_JOB_OPENING ---JOB OPENING

HRS_STS_JO_I --JOB OPENING STATUS CODE

HRS_APPLICANT --APPLICANT DETAILS

HRS_RCMNT --RECRUITMENT SUMMARY

HRS_STS_REC_I --RECRUITMENT STATUS

HRS_APP_RTE --ROUTE

HRS_RCM_INT --]INTERVIEW DETAILS

HRS_STS_INT_I --]INTERVIEW STATUS CODES

HRS_OFFER --OFFER DETAILS

HRS_STS_OFF_I --OFFER STATUS

HRS_TEAM --TEAM DETAILS

SQL Alt+F1 key

I was doing some ADHOC queries in SQL Server 2005 the other day and ran across something pretty neat. If you forget the parameters for a stored procedure like sp_colums, you can press ALT+F1 and receive a grid with information about that stored procedure including the parameters.

 

Same can be used for displaying the fields of a table too. Try it out. Very handy key combination