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
No comments:
Post a Comment