How to get Employee Supervisor Hierarchy?

I was recently asked this question.
The following script finds the Employee Supervisor Hierarchy:

SELECT paf.person_id, paf.supervisor_id
, LPAD (' ', 2 * LEVEL - 1)
|| SYS_CONNECT_BY_PATH (paf.person_id, '/') PATH
, LEVEL, paf.job_id -- used for linking to hr.per_jobs ,
, paf.effective_start_date, paf.effective_end_date
FROM per_all_assignments_f paf
START WITH paf.person_id = <:person_id>
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND assignment_status_type_id = 1
CONNECT BY PRIOR paf.person_id = paf.supervisor_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND assignment_status_type_id = 1

Note: In order to find the Person_id, select Person_id, Last_name, First_name from Per_all_people_f
The above sql will find topdown (Highest Level and follows down)
If you want to start from the lowest level and go all the way to CEO, then change the CONNECT BY clause as mentioned below:
CONNECT BY PRIOR paf.supervisor_id = paf.person_id

How to Capture invalid email address?

How can invalid or mal-formed email address (or so called bouncing emails) be captured?

As note: 456378.1 says, after enabling the event WF_MAIL.Disable_Recipient_Ntf_Pref, if a recipient's email address is invalid, an email notification is sent out the SYSADMIN that an email notification could not be sent.
Note:
1. However, this will work only on or after RUP5.
2. Notification preference for SYSADMIN should be MAILHTML or MAIL

to read the complete metalink note, click here

How To Find Out What Time A Timecard Was Submitted Using Wfstatus.sql

I was recently posed a question: To identify when a timecard was submited?
Easy: Use WFSTATUS.sql
Pass parameters the Internal Type of the Timecard and the Timecard Number (It is also the Expenditure_ID as well as the Item Key)
Reference: Metalink Note: 185820.1

Macroscope

I find Macroscope pretty useful tool in providing complete picture of the documentation - Easy to use, many quick links to navigate both horizontally and vertically within the product, it is a pleasant experience. Based on PMP Methodolgy. At a very high level, Macroscope documents are created based on Owner's view, User's view and Developer's View. To go to Macroscope home page Click here
I saw a pdf which provides more information on this,
Click here to read more about it.

It is interesting to note that Macroscope is considered as a as a Market leader by Gartner as shown in the picture below:

More on this latter.

How to display images in a seperate lines?

Pix 1:

I was trying to get two images displayed on a separate lines - although these are adjacent to each other; however to my exasperation, the images were displayed on the same lines. Have tried various tricks, including

. It is not consistent. For example these photos had to manually separated using text like this. Whereas, I was able to separate the technorati and LinkedIn icons. I will try to look at some examples later.
On a side note, these are the pictures of a bird that appeared outside our home, after I placed a toy bird - looks exactly like the one in the photo.

Pix 2:

Using Unix Find Command

Find:
find . -name "wfstatus.sql" -print
the above command will search all directories starting from current directory for any file containing wfstatus.sql.
Problem: find displays an error message for any directory I do not have permissions.
So how do I suppress these messages?
The following command will do the trick:

find . - name "wfstatus.sql " -print 2> /dev/null
will suppress cannot read dir or Permission denied messages and print only if it finds the file.
Output of this command:
/oracle/prodappl/fnd/11.5.0/sql/wfstatus.sql

explanation:
/dev/null is a virtual file in Unix; data written to is deleted.
I use the cheatsheet from Stanford.edu (to go to stanford clickhere )

In order to do a case-insensitive search, instead of "name", use "iname":
find . -iname "WFSTAtus.sql" -print 2>/dev/null

if you want the search to be restricted only to files, then use "-type f"
find . -type f -iname "WFSTAtus.sql"   -print 2>/dev/null
I have posted a list of commands that I use frequently in Linux. List of Commands I use frequently in Linux

GrandCentral

This is a surprise gift for me on the day I got the US Citizenship. Google has bought Grandcentral and it is a cool way to get connected, without providing your numbers. All you need to do is to add this button on your blog or Website and the system connects the caller to you.
It appears there is a delay to get a login to Grandcentral; However, if you are a blogger, you get it right away; You would need to setup which numbers to call and then embed the code into your blog. Any US phone can get connected by entering their number. Best part is your number is kept priviate.




Here is the note I read from article:

With GrandCentral, a free service from Google, you can receive phone calls and post voicemails right on your blog. Though GrandCentral is currently in a private beta test, bloggers can skip the wait and get a free account immediately. Sign up now

Update on Citizenship

Hey, Now I am an US Citizen! Preparing for the test,little more than a day; the questions were just a basic test on your knowledge on US Civics and history.

However, many things at the USCIS center, left me thinking, that it should be organized better. Like when you are waiting after submitting your documents, there no knowledge how much it will take for your time for interview or when they will call you for Oath Taking ceremony.
While the appointment mentions clearly not to come early, however, it appears more like a first-come, first-served basis. We should have a system of tokens/sign boards, to tell which case is being considered, the expected wait time.

Although, there is no mention about NOT bringing children, however, it various from Officer to Officer. The person who interviewed my wife, was pretty rude and obnoxious; there should be a clear policy on this. If infants and children are not allowed, either these should be communicated in the Appointment letter or they should ensure a Child-care is available.

When I was called for Interview, I was pleasantly surprised to find that the officer who interviewed me, was very friendly. It was altogether a different experience. Anyway, time to celebrate!

1-800-GOOG-411

Awesome. Google has come up with a free service to find /call business.
Thanks google :-)

US Citizenship Exam

I was initially under the impression that I need to go to the Library and read about US History or take a Civcs lesson something like that. It was heartening to note that in US Citizenship exam we will be asked only 96 questions that are found in the Book. Ah, both the questions and the answers are known. Do people still flunk this? I am really not sure. Most probably they do not have time to prepare (hmm... I do not intend to take more than a day to prepare.... I will find out whether this is sufficient).

There are certain questions, I do not know - why did the Pilgrims came to America? basic beliefs of the Declaration of Independence?; but most of them, are just very basic. Like...Who was the First President of United States? Who is the President of United States - come on, how many can live in a country and still not know who the chief executive is :-) ?

I also saw an interesting movie at USCIS.gov - A promise of Freedom - An introduction US History and Civics for Immigrants. There are other material available like Flash cards on the Exam.


Will update on the status later.

Designing adhoc roles

I recently came across a code which creates adhoc roles for sending Missing Timecard notifications to Supervisors.
Requirement: Every timecard has to approved by the Project Manager of the Project. If a timecard is not submitted then a notification has to be sent to the Employee and to the PM of that Project.
Issue: The code I saw created one role for every timecard submitted.
Problem is an Employee will work in various projects, may be even during the same time. There is only one approver for each project. So how can you get this resolved?
The Code I saw, creates an adhoc role for every Timecard submitted. Obviously, this will create millions of rows in wf_local_user_roles, since each role created will have at least 2 persons to notify.
My preferred solution: What I would do is either create one role for every project the Employee works for and append the Project Managers to this; or, create a PLSQL table of persons to whom the notifications has to be sent and in the approval workflow, loop till this list is exhausted.

How to disable reversal button in OIT timecard entry screen?

Requirement: To show Historical OIT timecards, but disallow Copy and Reverse Buttons.
Synopsis:
We are doing an Upgrade OTL and one of the requirement was to prevent users from Copying and/or Reversing any of the historical OIT timecards.

Steps:
1. Ensure the profile option - at responsibility level - "PA Time: Allow Reversals" in self service time is set to Yes.
2. Goto to AK Developer Responsibility
3. select Define Regions and search for PA_WEB_TIMECARD_HISTORY Region ID
4. Click the Region Items Buttions
5.a) For preventing Reversal: Uncheck Node Display checkbox for the attributes PA_WEB_REVERSE and PA_WEB_REVERSE_URL
5. b) For preventing Copying: Uncheck Node Display checkbox for the attributes PA_WEB_COPY and PA_WEB_COPY_URL
Save and Close.
This will remove the Copy and Reverse Buttons from OIT Screens.

Data Integrator

We are presently working on integration of heterogeneous systems. I was searching for a tool which will meet our requirements. Of course, the entire work can be done in SQLLoader too. But still I had to satisfy myself that SQLLOADER is THE answer.
One of the tools I came across was the Oracle Data Integrator.
This appears to be a regular ETL tool with oracle-specific features built-in. I have decided to explore further about this product.
Features I like about this product (of course I would probably find many of the features in the competitors too, But still it is worth exploring):

• Automatic code generation (reduces learning curve)
• User/Developer specifies the rules to apply to the integration processes.
• Hooks to SOA
  
• Library of Knowledge Modules (including one for Salesforce.com)

Getting the right data in the right place at the right time is the key to a successful data integration strategy. Are I going have a hourly/periodic updates from the source; if so, how complex will it be to maintain the program generated?
Will be posting more details later.

Thought of the day

"If you want to be irreplaceable, you have to be different."
Gabrielle 'Coco' Chanel