OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » Query to fetch employee details and processed pay elements

Home » Applications » Oracle Fusion Apps & E-Business Suite » Query to fetch employee details and processed pay elements (Oracle HRMS/Payroll 11i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to fetch employee details and processed pay elements [message #504631]

Tue, 26 April 2011 23:48

guptasum
Messages: 14
Registered: March 2011

Junior Member

Hello,

Below query (related to Oracle HRMS/Payroll) is used to pick up the employee details along with the payroll elements, processed on him/her that feed to a given balance (WORKCOVER) in the given date range (start_date and end_date).

However, it is picking up the elements processed (reduduncy comes into picture for the elements) in all the pay periods (start_date to end_date), and records are getting repeated with the same element names. Can someone help me in modifying this query to pick up the processed pay elements on the employee only once?

It would be great if someone can direct me in modifying the below query.


 SELECT papf.per_information2          Pay_Tax_State
        ,papf.full_name                Full_Name
        ,papf.employee_number          Emp_Num
        ,paaf.assignment_id            Asg_Id
        ,hla.location_code             Location
        ,paf.payroll_name              Payroll_Name
        ,haou.name                     Organization
        ,:l_end_date                   Effective_Date
        ,hapf.name                     Position_Name
        ,pj.name                       Job_Name
        ,ppos.date_start               Original_Hire_Date
        ,ppos.actual_termination_date  Actual_Termination_Date
        ,petf.element_name             Element_Name
        ,paaf.normal_hours             Normal_Hours
   FROM per_all_people_f            papf
        ,per_all_assignments_f      paaf
        ,pay_all_payrolls_f         paf
        ,hr_all_organization_units  haou
        ,hr_locations_all           hla
        ,hr_all_positions_f         hapf
        ,per_jobs                   pj
        ,per_periods_of_service     ppos 
        ,pay_element_types_f        petf
        ,pay_input_values_f         pivf
        ,pay_balance_feeds_f        pbf
        ,pay_balance_types          pbt
        ,pay_payroll_actions        ppa
        ,pay_assignment_actions     paa
        ,pay_run_results            prr
  WHERE papf.person_id                  = paaf.person_id
    AND paf.payroll_id                  = paaf.payroll_id
    AND haou.organization_id            = paaf.organization_id
    AND hla.location_id                 = paaf.location_id
    AND hapf.position_id                = paaf.position_id
    AND pj.job_id                       = paaf.job_id
    AND ppos.person_id                  = papf.person_id
    AND paaf.assignment_status_type_id  = 1
    AND pbf.balance_type_id             = pbt.balance_type_id
    AND pivf.input_value_id             = pbf.input_value_id
    AND petf.element_type_id            = pivf.element_type_id
    AND ppa.payroll_action_id           = paa.payroll_action_id
    AND paa.assignment_id               = paaf.assignment_id
    AND paa.assignment_action_id        = prr.assignment_action_id
    AND prr.element_type_id             = petf.element_type_id
    AND :l_end_date BETWEEN pbf.effective_start_date AND pbf.effective_end_date
    AND :l_end_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
    AND :l_end_date BETWEEN petf.effective_start_date AND petf.effective_end_date
    AND pbt.balance_name = 'WORKCOVER'
    AND ppa.action_type IN ('R','Q','B','V')
    AND ppa.action_status = 'C'
    AND ppa.effective_date BETWEEN :l_start_date AND :l_end_date
    AND paf.payroll_id IN (81, 82)
    AND :l_end_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND :l_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
    AND :l_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND :l_end_date BETWEEN hapf.effective_start_date AND hapf.effective_end_date
    AND papf.employee_number IN ('384062', '214734', '320095', '913871')
ORDER BY papf.employee_number

Kindly help me in modifying the query.

Thanks!

Report message to a moderator

Re: Query to fetch employee details and processed pay elements [message #504632 is a reply to message #504631]

Tue, 26 April 2011 23:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

without tables & data how can we test what you have & what you desire?

Report message to a moderator

Re: Query to fetch employee details and processed pay elements [message #504835 is a reply to message #504632]

Wed, 27 April 2011 23:13

guptasum
Messages: 14
Registered: March 2011

Junior Member

Hello,

Please find the data extract of the given query, attached. Here the element name (column) has all the elements processed on that employee.

Element names are picked up for each pay period in the given date range. However, I wish I get only unique set of elements (withou repetition) and only those corresponding number of records for that particular employee in this extract.

Kindly let me know in case I need to explain it further and help me in modifying or restructing this query to meet the requirement.

Thanks!

Attachment: Emp_Ele_Details.csv
(Size: 44.58KB, Downloaded 1452 times)

Report message to a moderator

Re: Query to fetch employee details and processed pay elements [message #504994 is a reply to message #504835]

Thu, 28 April 2011 13:16

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

guptasum wrote on Thu, 28 April 2011 00:13

Hello,

Please find the data extract of the given query, attached.

How does giving output help? We need input; all of it.

Report message to a moderator

Re: Query to fetch employee details and processed pay elements [message #505008 is a reply to message #504994]

Thu, 28 April 2011 15:13

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I may be very wrong, but this kind of title information ("Oracle HRMS/Payroll 11i") taught me that only people who actually use Oracle Apps. are capable of answering such questions. They *know* what kind of information is stored within those tables. Many tables. I, for example, have no idea how these tables are to be joined. Query is too complex (I mean, contains too many tables) to provide a simple test case.

So, in my opinion, this should probably be moved into a forum I mentioned earlier. Hopefully, someone (such as Vamsi Kasina) will see it and provide assistance.

Report message to a moderator

Re: Query to fetch employee details and processed pay elements [message #505322 is a reply to message #505008]

Mon, 02 May 2011 07:54

guptasum
Messages: 14
Registered: March 2011

Junior Member

Thank you very much for the attempt and guidance provided.

Thanks,
Suman

Report message to a moderator

Previous Topic:	Receiving & ITS error's (5 threads merged by bb)
Next Topic:	Receipt error APP-PO-14094: No records meet your search criteria

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:03:16 CDT 2024