Useful Queries

------------------------------------------------------------------------------------------
-- 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;