1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Query to fetch Element entry names and Values for employees for a given superviso

Discussion in 'Oracle HRMS & Payroll' started by manavi2@yahoo.com, Mar 26, 2014.

  1. manavi2@yahoo.com

    manavi2@yahoo.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hi

    I am looking for a sql query where I can fetch Element entry names and Values for employees for a given supervisor for a given pa_start_date and Pay_end_date .

    for example : I need to pass the parameter: Supervisor, Pay Start date, pay end date Pay date

    Any help would be appreciated

    Thanks
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: SQL Query to fetch Element entry names and Values for employees for a given super

    Hi Manavi,

    SELECT PETF.ELEMENT_NAME "Element Entry Name" ,
    PApF3.full_name supervisor,
    PAPF.FULL_NAME "Employee Name" ,
    PEEVF.EFFECTIVE_START_DATE "Entry Start Date" ,
    PEEVF.EFFECTIVE_END_DATE "Entry End Date" ,
    PEEVF.SCREEN_ENTRY_VALUE "Entry Values"
    FROM PAY_ELEMENT_TYPES_F PETF ,
    PAY_ELEMENT_LINKS_F PELF ,
    PAY_ELEMENT_ENTRIES_F PEEF ,
    PER_ALL_ASSIGNMENTS_F PAAF ,
    PER_ALL_PEOPLE_F PAPF ,
    PAY_ELEMENT_ENTRY_VALUES_F PEEVF ,
    PAY_INPUT_VALUES_F PIVF
    ,per_all_people_f papf2
    ,per_all_people_f papf3
    WHERE 1 = 1
    --AND PETF.ELEMENT_NAME = '&Entry_Name'
    AND PELF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
    AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
    AND TRUNC(SYSDATE) BETWEEN TRUNC(PEEF.EFFECTIVE_START_DATE) AND TRUNC(PEEF.EFFECTIVE_END_DATE)
    AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
    AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
    AND PAPF.PERSON_ID = PAAF.PERSON_ID
    AND PAPF.CURRENT_EMP_OR_APL_FLAG = 'Y'
    AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
    AND PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
    AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID
    and papf2.person_id=paaf.person_id
    AND paaf.primary_flag = 'Y'
    AND paaf.assignment_type = 'E'
    AND paaf.supervisor_id = papf3.person_id
    AND papf3.current_employee_flag = 'Y'
    AND papf2.business_group_id = paaf.business_group_id
    AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date
    AND SYSDATE BETWEEN papf3.effective_start_date AND papf3.effective_end_date
    ;

    Hope this will give an Idea...
     
    manavi2@yahoo.com likes this.
  3. manavi2@yahoo.com

    manavi2@yahoo.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Re: SQL Query to fetch Element entry names and Values for employees for a given super

    HI

    I need to pass parameters PAY PERIOD, PAY START DATE,PAY END DATE and MAnager - only employees belonging to the manager needs to be displayed..

    Please advise

    Thanks
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: SQL Query to fetch Element entry names and Values for employees for a given super

    Hi,

    Just add these 3 conditions to that query...

    AND PApF3.full_name=nvl:)P_SUPERVISOR,PApF3.full_name)
    AND PEEVF.EFFECTIVE_START_DATE=nvl:)P_PAY_START_DATE,PEEVF.EFFECTIVE_START_DATE)
    AND PEEVF.EFFECTIVE_END_DATE=nvl:)P_PAY_END_DATE,PEEVF.EFFECTIVE_END_DATE)
     
  5. manavi2@yahoo.com

    manavi2@yahoo.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Re: SQL Query to fetch Element entry names and Values for employees for a given super

    HI

    My requirement is actually this

    I need to create a report to display these elements on the basis of parameters

    PAY PERIOD
    MANAGER
    PAY PERIOD START DATE

    PAY PERIOD END DATE

    Step Grade Job Title GL Costing String Job Family Job Level Assignment Status Assignment Category Salary Mid Point for Band Salary Currency Standard Hours Weekly Hour Base LOA Expected Return Date Genesee Site Premium Site Premium Site Premium 48 Hour Ticket Prem 2nd Ticket Bonus Ticket Prem 1st Ticket Prem 3rd Ticket Premium Remote Site Premium Remote Site Prj Prem Site Work Week OT Bank 1.0 (in Hours) OT Bank 2.0 (in Hours) OT 1.0 (in Hours) OT 1.5 (in Hours) OT 2.0 (in Hours)


    Also I need to display SUM of these columns - OT Bank 1.0 (in Hours) OT Bank 2.0 (in Hours) OT 1.0 (in Hours) OT 1.5 (in Hours) OT 2.0 (in Hours)

    as TOTAL OT HRS



    Thanks for all your help. Please advice

    manavi