Home » Applications » Oracle Fusion Apps & E-Business Suite » Problem joining PER_PAY_PROPOSALS table to see who initiated a pay change. (App: 11.5.10.2, DB: 9.2.0.8, OS: AIX 5.3)
icon5.gif  Problem joining PER_PAY_PROPOSALS table to see who initiated a pay change. [message #501100] Fri, 25 March 2011 15:27 Go to next message
bberger
Messages: 3
Registered: March 2011
Location: Lafayette, LA
Junior Member
We have a custom PL/SQL report that runs against the PER_PAY_PROPOSALS table to provide information about pay changes that occur in our instance. This report uses the LAST_UPDATED_BY column to look up the User ID to see who updated the record. Unfortunately this column always returns the User ID of the last HR person that approved the work flow. Our HR VP wants to know who initiated the work flow (ie which supervisor, manger, director, etc) but this information is stored in the WF_XXXX and HR_API_XXXX tables. After reviewing ER Diagrams for work flow and HR_API tables, searching the ALL_TAB_COLUMNS table for ID or KEY columns, checking the referential tab in Toad for tables that reference or are referenced by other tables, I've been unable to find any way to connect or join these tables. PER_PAY_PROPOSALS only has a few ID columns like PAY_PROPOSAL_ID, EVENT_ID (this column is always NULL), and PERFORMANCE_REVIEW_ID. Each one of these ID's that I searched, resulted in a dead end before I could get to the work flow or HR_API tables. I filed an SR with My Oracle Support and they responded that this type of question fell under consulting services and closed my ticket.

If anyone has any ideas or suggestions on how I can join the information in the WF or HR_API tables to PER_PAY_PROPOSALS, I would appreciate any help you can provide. If it's not possible , please let me know too.

Thanks,
Brian B.
Re: Problem joining PER_PAY_PROPOSALS table to see who initiated a pay change. [message #501101 is a reply to message #501100] Fri, 25 March 2011 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
Please realize & understand that we speak SQL here; so please post DDL for all tables & indexes involved.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Problem joining PER_PAY_PROPOSALS table to see who initiated a pay change. [message #501103 is a reply to message #501101] Fri, 25 March 2011 15:52 Go to previous messageGo to next message
bberger
Messages: 3
Registered: March 2011
Location: Lafayette, LA
Junior Member
My apologies, I did not realize I already violated the posting guidelines. I ran a forum search for PER_PAY_PROPOSALS, I read the guidelines before posting and provided the application, DB and OS versions for my system and did my best to ensure I used proper grammar, spelling, punctuation, etc.

I will collect the information for the tables involved and post that.
Re: Problem joining PER_PAY_PROPOSALS table to see who initiated a pay change. [message #501127 is a reply to message #501103] Sat, 26 March 2011 02:15 Go to previous messageGo to next message
Littlefoot
Messages: 19614
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Brian,

there are only a VERY few OraFAQ Forum members who can answer questions about Oracle Apps. Unfortunately, I'm not among them (and, as far as I can tell (based on my experience gathered during previous years here, on the forum), Black Swan isn't among them either). If you stay here for a while, you'll also notice that some forum members use the same "signature" in most of their messages (regardless they are meaningful to people who actually read it or not, such as "It's very hot today" and you *know* it's snowing out there), so the fact that you've been warned to follow guidelines (which are quite wide) and - from my point of view - if you aren't told what EXACTLY you didn't follow - it is difficult to guess what might be wrong).

I also learned that principles that are valid in a "common" (PL/)SQL world aren't always meaningful in Oracle Apps. world. I understand that product you use is very wide, has numerous database objects and, although you have spent so much time trying to figure that out, I'm not sure you'll find what you are looking for. Perhaps there is a way - I can't tell. Wait a little bit longer, until someone who really knows how Apps. work sees your question. Hopefully, you'll get the answer.
Re: Problem joining PER_PAY_PROPOSALS table to see who initiated a pay change. [message #501384 is a reply to message #501127] Mon, 28 March 2011 13:49 Go to previous message
bberger
Messages: 3
Registered: March 2011
Location: Lafayette, LA
Junior Member
Littlefoot,

Thanks for the information, I appreciate it.
Previous Topic: Quality Module, Collection Plan
Next Topic: Oracle Apps AP and AR Migration from R11i to R12
Goto Forum:
  


Current Time: Fri Sep 19 02:53:51 CDT 2014

Total time taken to generate the page: 0.11107 seconds