------------------------------------------------------------------------------------------
-- APPS
------------------------------------------------------------------------------------------
--Get responsibilities where a particular concurrent program is registered
SELECT resp.responsibility_name, cp.user_concurrent_program_name
FROM apps.FND_RESPONSIBILITY_VL resp,
apps.FND_REQUEST_GROUP_UNITS rgu,
apps.FND_CONCURRENT_PROGRAMS_VL cp
WHERE resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND UPPER (user_concurrent_program_name) LIKE UPPER('concurrent%program%name%');
------------------------------------------------------------------------------------------
-- HRMS
------------------------------------------------------------------------------------------
-- Looking for assignments with a particular element entry values
SELECT
paf.assignment_number,
petf.element_name,
pivf.name,
pevf.screen_entry_value
FROM
pay_element_entries_f peef ,
pay_element_entry_values_f pevf,
per_assignments_f paf,
pay_element_types_f petf,
pay_input_values_f pivf
WHERE
peef.assignment_id = paf.assignment_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_type_id = pivf.element_type_id
AND pivf.input_value_id = pevf.input_value_id
AND peef.element_entry_id = pevf.element_entry_id
AND petf.element_name IN ('ELEMENT1','ELEMENT2')
AND pivf.name = 'INPUT1'
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN peef.effective_start_date AND peef.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pevf.effective_start_date AND pevf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
-- To get earning policies, payroll and responsibilities attached to user
SELECT psa.employee_number,
psa.full_name,
psa.user_name,
psa.responsibility_name,
ep.name earning_policy,
ppf.payroll_name
FROM
apps.per_sec_profile_assignments_v psa,
apps.per_all_assignments_f paf,
apps.hxt_add_assign_info_f aif,
apps.hxt_earning_policies ep,
apps.pay_all_payrolls_f ppf
WHERE
psa.employee_id = paf.person_id
AND aif.assignment_id = paf.assignment_id
AND paf.payroll_id = ppf.payroll_id
AND aif.earning_policy = ep.id
AND TRUNC(SYSDATE) BETWEEN NVL(aif.effective_start_date,TRUNC(SYSDATE)) AND NVL(aif.effective_end_date,TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
ORDER BY 1;
------------------------------------------------------------------------------------------
-- OTL
------------------------------------------------------------------------------------------
--Getting time attributes data
select t.time_building_block_id, t.scope,t.start_time, t.stop_time,t.object_version_number,t.date_from, t.date_to,
a.*
from hxc.hxc_time_building_blocks t
, hxc.hxc_time_attribute_usages u
, hxc.hxc_time_attributes a
where t.resource_id = 2189
--AND t.scope = 'DETAIL'
and t.start_time between to_date('18-APR-2010','DD-MON-YYYY')
and to_date('24-APR-2010','DD-MON-YYYY')
and t.time_building_block_id = u.time_building_block_id
and u.time_attribute_id = a.time_attribute_id
AND t.object_version_number = u.time_building_block_ovn
AND SYSDATE BETWEEN t.date_from AND t.date_to
--AND attribute_category not LIKE 'SECURI%'
order by t.scope,t.start_time, t.object_version_number
-- The below query will show duplicate attribute rows in case there is a data corruption due to duplicate rows
select t.*,a.*
from hxc.hxc_time_building_blocks t
, hxc.hxc_time_attribute_usages u
, hxc.hxc_time_attributes a
where t.resource_id = 3169
AND t.scope = 'DETAIL'
and TRUNC(t.start_time) BETWEEN to_date('05-SEP-2010','DD-MON-YYYY') AND to_date('11-SEP-2010','DD-MON-YYYY')
and t.time_building_block_id = u.time_building_block_id
and u.time_attribute_id = a.time_attribute_id
AND t.object_version_number = u.time_building_block_ovn
AND SYSDATE BETWEEN t.date_from AND t.date_to
AND a.attribute_category = xxrpay_hxc_utils_pk.get_otl_context_dff('LMR','Timesheet Entry')
order by TRUNC(t.start_time),t.object_version_number;
--Below query will get time entries for each day for payroll type timecard
SELECT ppx.employee_number ,
ppx.full_name,
htb1.start_time day_worked,
elem.element_name,
TO_CHAR(htb2.start_time,'dd-MON-YYYY HH:MI AM') start_time,
TO_CHAR(htb2.stop_time,'dd-MON-YYYY HH:MI AM') stop_time,
NVL(htb2.measure,ROUND((htb2.stop_time - htb2.start_time)*24,2)) hours,
hts.recorded_hours total_timecard_hours,
hts.start_time tc_start_date,
hts.stop_time tc_end_date
FROM apps.hxc_time_building_blocks htb1,
apps.hxc_time_building_blocks htb2,
apps.hxc_time_attribute_usages htau,
apps.hxc_time_attributes hta,
apps.pay_element_types_f elem,
apps.per_people_x ppx,
apps.hxc_timecard_summary hts
WHERE htb1.SCOPE = 'DAY'
AND htb1.date_to = TO_DATE('31-DEC-4712')
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND htb2.date_to = TO_DATE('31-DEC-4712')
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND SUBSTR (hta.attribute_category,INSTR (hta.attribute_category, ' - ') + 3,3) LIKE elem.element_type_id
AND htb1.start_time BETWEEN elem.effective_start_date and elem.effective_end_date
AND ppx.person_id = htb1.resource_id
AND hts.resource_id = ppx.person_id
AND TRUNC(htb1.start_time) BETWEEN TRUNC(hts.start_time) AND TRUNC(hts.stop_time)
AND TRUNC(htb1.start_time) BETWEEN :p_start_date AND :p_end_date
AND ppx.employee_number = :p_emp_no
ORDER BY ppx.employee_number, htb1.start_time, htb2.start_time;
-- APPS
------------------------------------------------------------------------------------------
--Get responsibilities where a particular concurrent program is registered
SELECT resp.responsibility_name, cp.user_concurrent_program_name
FROM apps.FND_RESPONSIBILITY_VL resp,
apps.FND_REQUEST_GROUP_UNITS rgu,
apps.FND_CONCURRENT_PROGRAMS_VL cp
WHERE resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND UPPER (user_concurrent_program_name) LIKE UPPER('concurrent%program%name%');
------------------------------------------------------------------------------------------
-- HRMS
------------------------------------------------------------------------------------------
-- Looking for assignments with a particular element entry values
SELECT
paf.assignment_number,
petf.element_name,
pivf.name,
pevf.screen_entry_value
FROM
pay_element_entries_f peef ,
pay_element_entry_values_f pevf,
per_assignments_f paf,
pay_element_types_f petf,
pay_input_values_f pivf
WHERE
peef.assignment_id = paf.assignment_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_type_id = pivf.element_type_id
AND pivf.input_value_id = pevf.input_value_id
AND peef.element_entry_id = pevf.element_entry_id
AND petf.element_name IN ('ELEMENT1','ELEMENT2'
AND pivf.name = 'INPUT1'
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN peef.effective_start_date AND peef.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pevf.effective_start_date AND pevf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
-- To get earning policies, payroll and responsibilities attached to user
SELECT psa.employee_number,
psa.full_name,
psa.user_name,
psa.responsibility_name,
ep.name earning_policy,
ppf.payroll_name
FROM
apps.per_sec_profile_assignments_v psa,
apps.per_all_assignments_f paf,
apps.hxt_add_assign_info_f aif,
apps.hxt_earning_policies ep,
apps.pay_all_payrolls_f ppf
WHERE
psa.employee_id = paf.person_id
AND aif.assignment_id = paf.assignment_id
AND paf.payroll_id = ppf.payroll_id
AND aif.earning_policy = ep.id
AND TRUNC(SYSDATE) BETWEEN NVL(aif.effective_start_date,TRUNC(SYSDATE)) AND NVL(aif.effective_end_date,TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
ORDER BY 1;
------------------------------------------------------------------------------------------
-- OTL
------------------------------------------------------------------------------------------
--Getting time attributes data
select t.time_building_block_id, t.scope,t.start_time, t.stop_time,t.object_version_number,t.date_from, t.date_to,
a.*
from hxc.hxc_time_building_blocks t
, hxc.hxc_time_attribute_usages u
, hxc.hxc_time_attributes a
where t.resource_id = 2189
--AND t.scope = 'DETAIL'
and t.start_time between to_date('18-APR-2010','DD-MON-YYYY')
and to_date('24-APR-2010','DD-MON-YYYY')
and t.time_building_block_id = u.time_building_block_id
and u.time_attribute_id = a.time_attribute_id
AND t.object_version_number = u.time_building_block_ovn
AND SYSDATE BETWEEN t.date_from AND t.date_to
--AND attribute_category not LIKE 'SECURI%'
order by t.scope,t.start_time, t.object_version_number
-- The below query will show duplicate attribute rows in case there is a data corruption due to duplicate rows
select t.*,a.*
from hxc.hxc_time_building_blocks t
, hxc.hxc_time_attribute_usages u
, hxc.hxc_time_attributes a
where t.resource_id = 3169
AND t.scope = 'DETAIL'
and TRUNC(t.start_time) BETWEEN to_date('05-SEP-2010','DD-MON-YYYY') AND to_date('11-SEP-2010','DD-MON-YYYY')
and t.time_building_block_id = u.time_building_block_id
and u.time_attribute_id = a.time_attribute_id
AND t.object_version_number = u.time_building_block_ovn
AND SYSDATE BETWEEN t.date_from AND t.date_to
AND a.attribute_category = xxrpay_hxc_utils_pk.get_otl_context_dff('LMR','Timesheet Entry')
order by TRUNC(t.start_time),t.object_version_number;
SELECT ppx.employee_number ,
ppx.full_name,
htb1.start_time day_worked,
elem.element_name,
TO_CHAR(htb2.start_time,'dd-MON-YYYY HH:MI AM') start_time,
TO_CHAR(htb2.stop_time,'dd-MON-YYYY HH:MI AM') stop_time,
NVL(htb2.measure,ROUND((htb2.stop_time - htb2.start_time)*24,2)) hours,
hts.recorded_hours total_timecard_hours,
hts.start_time tc_start_date,
hts.stop_time tc_end_date
FROM apps.hxc_time_building_blocks htb1,
apps.hxc_time_building_blocks htb2,
apps.hxc_time_attribute_usages htau,
apps.hxc_time_attributes hta,
apps.pay_element_types_f elem,
apps.per_people_x ppx,
apps.hxc_timecard_summary hts
WHERE htb1.SCOPE = 'DAY'
AND htb1.date_to = TO_DATE('31-DEC-4712')
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND htb2.date_to = TO_DATE('31-DEC-4712')
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND SUBSTR (hta.attribute_category,INSTR (hta.attribute_category, ' - ') + 3,3) LIKE elem.element_type_id
AND htb1.start_time BETWEEN elem.effective_start_date and elem.effective_end_date
AND ppx.person_id = htb1.resource_id
AND hts.resource_id = ppx.person_id
AND TRUNC(htb1.start_time) BETWEEN TRUNC(hts.start_time) AND TRUNC(hts.stop_time)
AND TRUNC(htb1.start_time) BETWEEN :p_start_date AND :p_end_date
AND ppx.employee_number = :p_emp_no
ORDER BY ppx.employee_number, htb1.start_time, htb2.start_time;
