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!

HRMS / PAYROLL Element Entry Values fields (Data Fetching or Query)

Discussion in 'Oracle HRMS & Payroll' started by tariqbashir, Nov 20, 2013.

  1. tariqbashir

    tariqbashir Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    100
    Hi Folks

    In HRMS / PAYROLL we have an element value for Employee entered in Element Entry Values fields , How to get SQL Query to get these values. Please let me know if anyone knows the queries or Actual Table links. Navigation Path (People --> Enter & Maintain --> Assignment ---> Entries ---> Element Entries ---> Entry Values
    Appreciate your help in this regards.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Tariq,

    Use the below Query to retrieve the Element Entry information for the employees. Pass Element Entry Name as Parameter. Then we can see the results as all active employees will be pulled who are having that element entry in the current period.

    Code (Text):

    SELECT
      PETF.ELEMENT_NAME "Element Entry Name"
    , PAAF.PERSON_ID, PAPF.FULL_NAME "Employee Name"
    , PIVF.NAME "Input Fields"
    , 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

    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

     
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Tariq,

    Is that provided query works fine ?