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!
Sarathk18

Basic HRMS and FND Queries 2016-06-19

Basic HRMS and FND Queries

  1. Sarathk18
    Query to find the DFF fields atached to a context_code
    ------------------------------------------------------
    select
    a.descriptive_flex_context_code context_code,
    a.end_user_column_name,
    a.application_column_name ,
    a.flex_value_set_id VS_id, b.flex_value_set_name,a.enabled_flag,
    a.required_flag,b.validation_type,a.display_flag,a.display_size
    from apps.fnd_descr_flex_col_usage_vl a, apps.fnd_flex_value_sets b
    where a.descriptive_flex_context_code='XXCHR_COMM_DRAWS'
    and a.descriptive_flexfield_name='Assignment Developer DF'
    and b.flex_value_set_id(+)=a.flex_value_set_id

    ******************************************************************************************************************************************************
    Query for getting Responsibility_Key
    -------------------------------------
    SELECT r.responsibility_id,
    SUBSTR(USER_NAME,1,25) "User Name",
    SUBSTR(APPLICATION_NAME,1,45) "Application Name",
    responsibility_key,
    SUBSTR(RESPONSIBILITY_NAME,1,60) "Responsibility Name",
    SECURITY_GROUP_NAME,
    GREATEST(U.START_DATE, UR.START_DATE, R.START_DATE) START_DATE,
    LEAST(NVL(U.END_DATE, NVL(UR.END_DATE, R.END_DATE)),
    NVL(UR.END_DATE, NVL(U.END_DATE, R.END_DATE)),
    NVL(R.END_DATE, NVL(U.END_DATE, UR.END_DATE))) END_DATE
    FROM
    FND_USER U,
    FND_USER_RESP_GROUPS UR,
    FND_RESPONSIBILITY_VL R,
    FND_APPLICATION_VL A,
    FND_SECURITY_GROUPS_VL S
    WHERE
    A.APPLICATION_ID = R.APPLICATION_ID
    AND U.USER_ID = UR.USER_ID
    AND R.APPLICATION_ID = UR.RESPONSIBILITY_APPLICATION_ID
    AND R.RESPONSIBILITY_ID = UR.RESPONSIBILITY_ID
    AND UR.START_DATE <= SYSDATE
    AND NVL(UR.END_DATE, SYSDATE + 1) > SYSDATE
    AND U.START_DATE <= SYSDATE
    AND NVL(U.END_DATE, SYSDATE + 1) > SYSDATE
    AND R.START_DATE <= SYSDATE
    AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
    AND UR.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID
    and user_name like 'ARMUTHUS'
    ORDER BY
    SUBSTR(RESPONSIBILITY_NAME,1,8),
    USER_NAME
    ******************************************************************************************************************************************************
    Query to get the MENU NAME
    ---------------------------
    SELECT DISTINCT fm.menu_id,fm.menu_name
    FROM
    fnd_menus fm,
    FND_MENU_ENTRIES fme,
    fnd_responsibility fr,
    fnd_responsibility_tl frt
    WHERE fm.menu_id = fme.menu_id
    AND fr.menu_id = fme.menu_id
    AND fr.responsibility_id = frt.responsibility_id
    AND fr.responsibility_id = frt.responsibility_id
    AND responsibility_name = '&Responsibillity' ;
    ***************************************************************************************************************************************************************************
    Query to check the Request Group of a concurrent program
    ---------------------------------------------------------
    select d.RESPONSIBILITY_NAME,a.REQUEST_GROUP_NAME, e.USER_CONCURRENT_PROGRAM_NAME
    from fnd_request_groups a,
    FND_REQUEST_GROUP_UNITS b,
    fnd_responsibility c,
    fnd_responsibility_tl d,
    FND_CONCURRENT_PROGRAMS_TL e
    where a.REQUEST_GROUP_ID=b.REQUEST_GROUP_ID
    and c.RESPONSIBILITY_ID=d.RESPONSIBILITY_ID
    and c.REQUEST_GROUP_ID=b.REQUEST_GROUP_ID
    and d.language='US'
    and e.language='US'
    and b.request_unit_id=e.concurrent_program_id
    and e.USER_CONCURRENT_PROGRAM_NAME like 'Cisco%JP Morgan%Inbound%'
    ******************************************************************************************************************************************************
    Query to check the Employees having particular elements in a given pay period
    ------------------------------------------------------------------------------
    SELECT DISTINCT papf.employee_number,
    papf.full_name,
    petf.element_name,
    peef.effective_start_date,
    pivf.name input_field,
    peevf.screen_entry_value input_value,
    peef.creation_date,
    peef.last_update_date
    from per_all_people_f papf,
    per_all_assignments_f paaf,
    pay_element_entries_f peef,
    pay_element_types_f petf,
    pay_element_entry_values_f peevf,
    pay_input_values_f pivf,
    xxchr_ib_jpm_401k_stage xs
    where papf.person_id = paaf.person_id
    and papf.business_group_id=113
    and papf.current_employee_flag = 'Y'
    and paaf.assignment_id = peef.assignment_id
    and peef.element_type_id = petf.element_type_id
    and peef.element_entry_id = peevf.element_entry_id
    and TO_DATE('GIVEN_DATE') between peevf.effective_start_date and peevf.effective_end_date
    and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
    and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
    and TO_DATE('GIVEN_DATE') between peef.effective_start_date and peef.effective_end_date
    and trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
    AND petf.element_type_id = pivf.element_type_id
    AND peef.element_type_id = pivf.element_type_id
    AND pivf.input_value_id = peevf.input_value_id
    AND trunc(sysdate) BETWEEN pivf.effective_start_date
    AND pivf.effective_end_date
    AND UPPER (pivf.NAME) IN ( 'AMOUNT', 'LOAN NUMBER', 'PERCENT')
    and REPLACE(papf.national_identifier,'-') = TRIM (xs.ssn)
    AND xs.status = 'P'
    AND petf.element_name IN (
    'Element Name1', 'Element Name2', 'Element Name3'
    )
    AND peevf.screen_entry_value IS NOT NULL
    AND (
    TRUNC(peef.creation_date) = TRUNC(SYSDATE)
    OR
    TRUNC(peef.last_update_date) = TRUNC(SYSDATE)
    )
    ORDER BY papf.employee_number
    ******************************************************************************************************************************************************
    Query to pick the employee # , his elements and the effective date of the element entry
    ----------------------------------------------------------------------------------------
    SELECT papf.employee_number, papf.full_name, petf.element_name, peef.effective_start_date
    FROM per_all_people_f papf, per_all_assignments_f paaf, pay_element_entries_f peef, pay_element_types_f petf, per_business_groups pb
    WHERE papf.person_id = paaf.person_id
    AND papf.business_group_id=pb.business_group_id
    AND papf.current_employee_flag = 'Y'
    AND paaf.assignment_id = peef.assignment_id
    AND peef.element_type_id = petf.element_type_id
    AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
    AND trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
    AND trunc(sysdate) between peef.effective_start_date and peef.effective_end_date
    AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
    AND petf.element_name IN (
    'Element Name1', 'Element Name2', 'Element Name3'
    )
    ******************************************************************************************************************************************************
    Query to pull values from the User Defined Tables (UDT)
    --------------------------------------------------------
    SELECT pui.VALUE,puc.user_column_name,pur.row_low_range_or_name
    FROM pay_user_tables put,
    pay_user_rows_f pur,
    pay_user_columns puc,
    pay_user_column_instances_f pui
    WHERE put.user_table_name = '<UDT_NAME>'
    AND pur.user_table_id = put.user_table_id
    AND pui.user_column_id = puc.user_column_id
    AND pui.user_row_id = pur.user_row_id
    AND SYSDATE BETWEEN pur.effective_start_date
    AND pur.effective_end_date
    AND SYSDATE BETWEEN pui.effective_start_date
    AND pui.effective_end_date
    AND puc.user_column_name = '<COLUMN_NAME>'
    AND puc.user_table_id = put.user_table_id
    ******************************************************************************************************************************************************
    Query to find the DFF fields atached to a context_code
    ------------------------------------------------------

    select
    a.descriptive_flex_context_code context_code,
    a.end_user_column_name,
    a.application_column_name ,
    a.flex_value_set_id VS_id, b.flex_value_set_name,a.enabled_flag,
    a.required_flag,b.validation_type,a.display_flag,a.display_size
    from apps.fnd_descr_flex_col_usage_vl a, apps.fnd_flex_value_sets b
    where a.descriptive_flex_context_code='XXCHR_COMM_DRAWS'
    and a.descriptive_flexfield_name='Assignment Developer DF'
    and b.flex_value_set_id(+)=a.flex_value_set_id


    ******************************************************************************************************************************************************

    Query for getting Responsibility_Key
    -------------------------------------

    SELECT r.responsibility_id,
    SUBSTR(USER_NAME,1,25) "User Name",
    SUBSTR(APPLICATION_NAME,1,45) "Application Name",
    responsibility_key,
    SUBSTR(RESPONSIBILITY_NAME,1,60) "Responsibility Name",
    SECURITY_GROUP_NAME,
    GREATEST(U.START_DATE, UR.START_DATE, R.START_DATE) START_DATE,
    LEAST(NVL(U.END_DATE, NVL(UR.END_DATE, R.END_DATE)),
    NVL(UR.END_DATE, NVL(U.END_DATE, R.END_DATE)),
    NVL(R.END_DATE, NVL(U.END_DATE, UR.END_DATE))) END_DATE
    FROM
    FND_USER U,
    FND_USER_RESP_GROUPS UR,
    FND_RESPONSIBILITY_VL R,
    FND_APPLICATION_VL A,
    FND_SECURITY_GROUPS_VL S
    WHERE
    A.APPLICATION_ID = R.APPLICATION_ID
    AND U.USER_ID = UR.USER_ID
    AND R.APPLICATION_ID = UR.RESPONSIBILITY_APPLICATION_ID
    AND R.RESPONSIBILITY_ID = UR.RESPONSIBILITY_ID
    AND UR.START_DATE <= SYSDATE
    AND NVL(UR.END_DATE, SYSDATE + 1) > SYSDATE
    AND U.START_DATE <= SYSDATE
    AND NVL(U.END_DATE, SYSDATE + 1) > SYSDATE
    AND R.START_DATE <= SYSDATE
    AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
    AND UR.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID
    and user_name like 'ARMUTHUS'
    ORDER BY
    SUBSTR(RESPONSIBILITY_NAME,1,8),
    USER_NAME

    ******************************************************************************************************************************************************

    Query to get the MENU NAME
    ---------------------------

    SELECT DISTINCT fm.menu_id,fm.menu_name
    FROM
    fnd_menus fm,
    FND_MENU_ENTRIES fme,
    fnd_responsibility fr,
    fnd_responsibility_tl frt
    WHERE fm.menu_id = fme.menu_id
    AND fr.menu_id = fme.menu_id
    AND fr.responsibility_id = frt.responsibility_id
    AND fr.responsibility_id = frt.responsibility_id
    AND responsibility_name = '&Responsibillity' ;

    ***************************************************************************************************************************************************************************

    Query to check the Request Group of a concurrent program
    ---------------------------------------------------------

    select d.RESPONSIBILITY_NAME,a.REQUEST_GROUP_NAME, e.USER_CONCURRENT_PROGRAM_NAME
    from fnd_request_groups a,
    FND_REQUEST_GROUP_UNITS b,
    fnd_responsibility c,
    fnd_responsibility_tl d,
    FND_CONCURRENT_PROGRAMS_TL e
    where a.REQUEST_GROUP_ID=b.REQUEST_GROUP_ID
    and c.RESPONSIBILITY_ID=d.RESPONSIBILITY_ID
    and c.REQUEST_GROUP_ID=b.REQUEST_GROUP_ID
    and d.language='US'
    and e.language='US'
    and b.request_unit_id=e.concurrent_program_id
    and e.USER_CONCURRENT_PROGRAM_NAME like 'Cisco%JP Morgan%Inbound%'

    ******************************************************************************************************************************************************

    Query to check the Employees having particular elements in a given pay period
    ------------------------------------------------------------------------------

    SELECT DISTINCT papf.employee_number,
    papf.full_name,
    petf.element_name,
    peef.effective_start_date,
    pivf.name input_field,
    peevf.screen_entry_value input_value,
    peef.creation_date,
    peef.last_update_date
    from per_all_people_f papf,
    per_all_assignments_f paaf,
    pay_element_entries_f peef,
    pay_element_types_f petf,
    pay_element_entry_values_f peevf,
    pay_input_values_f pivf,
    xxchr_ib_jpm_401k_stage xs
    where papf.person_id = paaf.person_id
    and papf.business_group_id=113
    and papf.current_employee_flag = 'Y'
    and paaf.assignment_id = peef.assignment_id
    and peef.element_type_id = petf.element_type_id
    and peef.element_entry_id = peevf.element_entry_id
    and TO_DATE('GIVEN_DATE') between peevf.effective_start_date and peevf.effective_end_date
    and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
    and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
    and TO_DATE('GIVEN_DATE') between peef.effective_start_date and peef.effective_end_date
    and trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
    AND petf.element_type_id = pivf.element_type_id
    AND peef.element_type_id = pivf.element_type_id
    AND pivf.input_value_id = peevf.input_value_id
    AND trunc(sysdate) BETWEEN pivf.effective_start_date
    AND pivf.effective_end_date
    AND UPPER (pivf.NAME) IN ( 'AMOUNT', 'LOAN NUMBER', 'PERCENT')
    and REPLACE(papf.national_identifier,'-') = TRIM (xs.ssn)
    AND xs.status = 'P'
    AND petf.element_name IN (
    'Element Name1', 'Element Name2', 'Element Name3'
    )
    AND peevf.screen_entry_value IS NOT NULL
    AND (
    TRUNC(peef.creation_date) = TRUNC(SYSDATE)
    OR
    TRUNC(peef.last_update_date) = TRUNC(SYSDATE)
    )
    ORDER BY papf.employee_number

    ******************************************************************************************************************************************************
    Query to pick the employee # , his elements and the effective date of the element entry
    ----------------------------------------------------------------------------------------

    SELECT papf.employee_number, papf.full_name, petf.element_name, peef.effective_start_date
    FROM per_all_people_f papf, per_all_assignments_f paaf, pay_element_entries_f peef, pay_element_types_f petf, per_business_groups pb
    WHERE papf.person_id = paaf.person_id
    AND papf.business_group_id=pb.business_group_id
    AND papf.current_employee_flag = 'Y'
    AND paaf.assignment_id = peef.assignment_id
    AND peef.element_type_id = petf.element_type_id
    AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
    AND trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
    AND trunc(sysdate) between peef.effective_start_date and peef.effective_end_date
    AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
    AND petf.element_name IN (
    'Element Name1', 'Element Name2', 'Element Name3'
    )

    ******************************************************************************************************************************************************


    Query to pull values from the User Defined Tables (UDT)
    --------------------------------------------------------

    SELECT pui.VALUE,puc.user_column_name,pur.row_low_range_or_name
    FROM pay_user_tables put,
    pay_user_rows_f pur,
    pay_user_columns puc,
    pay_user_column_instances_f pui
    WHERE put.user_table_name = '<UDT_NAME>'
    AND pur.user_table_id = put.user_table_id
    AND pui.user_column_id = puc.user_column_id
    AND pui.user_row_id = pur.user_row_id
    AND SYSDATE BETWEEN pur.effective_start_date
    AND pur.effective_end_date
    AND SYSDATE BETWEEN pui.effective_start_date
    AND pui.effective_end_date
    AND puc.user_column_name = '<COLUMN_NAME>'
    AND puc.user_table_id = put.user_table_id

    ******************************************************************************************************************************************************


    There are no artifacts for this solution


    There are no discussions for this solution


    This solution is not tagged to any SolutionPack.