Monday, January 18, 2010

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;

1 comment:

oracle fusion hcm online Training said...

nice blog
Thanks for the statistics. I discovered your weblog enthusiastically ,our oracle hcm online training provider won the excessive common call through worldwide for its training.
Oracle fusion all modules education

for more information please check the site
online trainings