Thursday, 27 June 2013

IREC-Query to get List of all interview schedules and which are not cancelled

 SELECT PerEventsEO.EVENT_ID,
       PerEventsEO.ASSIGNMENT_ID,
       papf.full_name Applicant_name,
       paaf.person_id,
       paaf.vacancy_id,
       pav.name Vacancy_Name,
       haou.name Org_name,
       pj.name Job_name,
       paaf.effective_start_date,
       paaf.effective_end_date,
       PerEventsEO.DATE_START,
       VL.MEANING TYPE,
       PerEventsEO.DATE_END,
       PerEventsEO.EVENT_OR_INTERVIEW,
       PerEventsEO.TIME_END,
       PerEventsEO.TIME_START
       --pav.*    
  FROM PER_EVENTS PerEventsEO,
  per_all_assignments_f paaf,
  per_all_vacancies pav,
  per_all_people_f papf,
  hr_all_organization_units haou,
  per_jobs pj,
  FND_LOOKUP_TYPES_VL tl,
  FND_LOOKUP_VALUES_VL vl
  where --paaf.ASSIGNMENT_ID=C_AssignID
  paaf.person_id =(Select Distinct papf.person_id
  from per_all_assignments_f paaf,  per_all_people_f papf
  where
  papf.person_id=paaf.person_id
  and sysdate between paaf.effective_start_date and paaf.effective_end_date
  and sysdate between papf.effective_start_date and papf.effective_end_date
  and assignment_id=C_AssignID)
  and paaf.assignment_id=PerEventsEO.assignment_id
  and papf.person_id=paaf.person_id
  and pav.vacancy_id=paaf.vacancy_id
  and haou.ORGANIZATION_ID=pav.ORGANIZATION_ID
  and pj.job_id=pav.job_id
  AND TL.LOOKUP_TYPE=VL.LOOKUP_TYPE
  and tl.LOOKUP_TYPE='IRC_INTERVIEW_TYPE'
  AND PerEventsEO.TYPE=VL.LOOKUP_CODE
  and sysdate between paaf.effective_start_date and paaf.effective_end_date
  and sysdate between papf.effective_start_date and papf.effective_end_date
  and PerEventsEO.event_id not in (
  select event_id from IRC_INTERVIEW_DETAILS a where STATUS='CANCELLED' and a.event_id=PerEventsEO.event_id)
  and PerEventsEO.event_id not in (C_event_id);
 

VALUE SET DYNAMIC-Query To create Dynamic List of year and months

SELECT a.months
  ||'/'
  ||b.years credit_card_expiration_date
FROM
  (SELECT TO_CHAR(add_months(TRUNC(SYSDATE, 'YYYY'), LEVEL - 1), 'MM') months
  FROM dual
    CONNECT BY LEVEL <= 12
  ) a,
  (SELECT SUBSTR(EXTRACT(YEAR FROM SYSDATE) + (LEVEL-1),3) years
  FROM dual
    CONNECT BY LEVEL <=10
  ) b
WHERE to_date(a.months
  ||'/'
  ||b.years,'mm/yy')>=SYSDATE
ORDER BY b.years,
  a.months;

OTL-Query To get assignment time information

SELECT aai.ROWID row_id, aai.ID, aai.effective_start_date,
       aai.effective_end_date, peo.full_name, aai.assignment_id,
       asg.assignment_number, asg.business_group_id asg_bus_grp_id,
       aai.autogen_hours_yn, hrlk.meaning autogen_hours_yn_meaning,
       aai.rotation_plan, rot.NAME rotation_plan_name, aai.earning_policy,
       erp.NAME earning_policy_name, aai.shift_differential_policy,
       sdp.NAME shift_differential_policy_name, aai.hour_deduction_policy,
       hdp.NAME hour_deduction_policy_name, aai.created_by, aai.creation_date,
       aai.last_update_date, aai.last_update_login, aai.last_updated_by,
       aai.attribute_category, aai.attribute1, aai.attribute2, aai.attribute3,
       aai.attribute4, aai.attribute5, aai.attribute6, aai.attribute7,
       aai.attribute8, aai.attribute9, aai.attribute10, aai.attribute11,
       aai.attribute12, aai.attribute13, aai.attribute14, aai.attribute15,
       aai.attribute16, aai.attribute17, aai.attribute18, aai.attribute19,
       aai.attribute20, aai.attribute21, aai.attribute22, aai.attribute23,
       aai.attribute24, aai.attribute25, aai.attribute26, aai.attribute27,
       aai.attribute28, aai.attribute29, aai.attribute30
  FROM hxt_hour_deduct_policies hdp,
       hxt_shift_diff_policies sdp,
       hxt_earning_policies erp,
       hxt_rotation_plans rot,
       per_people_f peo,
       per_assignments_f asg,
       hr_lookups hrlk,
       hxt_add_assign_info_f aai,
       fnd_sessions fss
 WHERE fss.session_id = USERENV ('SESSIONID')
   AND fss.effective_date BETWEEN aai.effective_start_date
                              AND aai.effective_end_date
   AND aai.assignment_id = asg.assignment_id
   AND fss.effective_date BETWEEN asg.effective_start_date
                              AND asg.effective_end_date
   AND asg.person_id = peo.person_id
   AND fss.effective_date BETWEEN peo.effective_start_date
                              AND peo.effective_end_date
   AND aai.autogen_hours_yn = hrlk.lookup_code
   AND hrlk.application_id = 800
   AND hrlk.lookup_type = 'YES_NO'
   AND aai.rotation_plan = rot.ID(+)
   AND aai.earning_policy = erp.ID
   AND aai.shift_differential_policy = sdp.ID(+)
   AND aai.hour_deduction_policy = hdp.ID(+);

OTL-List of Rotation Plan updates as on sysdate

SELECT NAME,
       (CASE
           WHEN TRUNC (SYSDATE) = TRUNC (last_update_date)
              THEN 'YES'
           ELSE 'NO'
        END) "CHANGE", TO_CHAR (last_update_date, 'DD-MON-RRRR') "Last Update Date"
  FROM hxt_rotation_plans
 WHERE 1 = 1
 ORDER BY 2 desc

OTL-Query to get details of Timekeeper,Timesheet organization by passing FACILITY name

select timekeepr_Group_Name,timekeeper_name ,Timekeeper_corporation_number,employee_corporation_Number, Employee_Name,a.name org_name
 from (select employee_number,npw_number,full_name,paaf.organization_id,haou.name
from per_all_people_f papf,per_all_assignments_f paaf,hr_all_organization_units haou
where paaf.person_id=papf.person_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and current_employee_flag='Y'
and haou.organization_id=paaf.organization_id
and  paaf.organization_id in
(SELECT
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND org.organization_id = pose.organization_id_child
START WITH
pose.organization_id_parent =(select organization_id from hr_all_organization_units where  type like 'FACILITY%' and name=nvl(:Fac_name,name))
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
))a,
(select tg.TK_GROUP_NAME timekeepr_Group_Name,papf2.full_name timekeeper_name ,papf2.employee_Number Timekeeper_corporation_number,
papf1.employee_Number employee_corporation_Number,papf1.full_name Employee_Name
from hxc_tk_groups tg,hxc_tk_group_queries tkgq,hxc_tk_group_query_criteria tc,per_all_people_f papf1,per_all_people_f papf2,
hxt_rotation_plans hrp
where tg.tk_group_id=tkgq.tk_group_id
and tc.TK_GROUP_QUERY_ID=tkgq.TK_GROUP_QUERY_ID
and tc.CRITERIA_ID=papf1.person_id
and tg.TK_RESOURCE_ID=papf2.person_id
and hrp.name(+)=papf1.employee_number
and sysdate between papf1.effective_start_date and papf1.effective_end_date
and sysdate between papf2.effective_start_date and papf2.effective_end_date)b
where nvl(a.employee_number,a.npw_number)=b.employee_corporation_Number;

select org name by using below query.
select * from hr_all_organization_units  where name like 'Womens%' and type like 'FACILITY%'

OTL-Query To get Employee TimeCard details with elements name.

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,hta.attribute_category,petf.element_name,(SUBSTR(hta.attribute_category,11,LENGTH(hta.attribute_category))) Element_ID,
                  hts.stop_time, hts.submission_date,
                  htb1.start_time each_day, hta.attribute1 project_id,
                  hta.attribute2 task_id, htb2.measure--, pt.task_name
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  --pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pay_element_types_f petf
                  --pa_tasks pt
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
              AND htb.date_to = hr_general.end_of_time
              AND htb.SCOPE = 'TIMECARD'
              AND htb1.SCOPE = 'DAY'
              AND htb1.date_to = hr_general.end_of_time
              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 = hr_general.end_of_time
              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 papa.project_id = hta.attribute1
              AND hts.start_time = htb.start_time
              AND hts.resource_id = htb.resource_id
              AND to_char(petf.element_type_id) =(SUBSTR(hta.attribute_category,11,LENGTH(hta.attribute_category)))
           --AND htb.resource_id = p_resource_id
              AND hts.timecard_id = :p_timecard_id
              --AND hta.attribute_category = 'PROJECTS'
            --  AND hts.approval_status = 'WORKING'
         --     AND hta.attribute2 = pt.task_id
           --   AND hta.attribute1 = pt.project_id
              --and hts.timecard_id=38673
             and  hta.attribute_category is not null
             and hta.attribute_category not like 'SEC%'
         ORDER BY htb1.start_time;

OTL-Query to get Timekeeper group name ,Timekeeper and Respective Employees

select tg.TK_GROUP_NAME timekeepr_Group_Name,papf2.full_name timekeeper_name ,papf2.employee_Number Timekeeper_corporation_number,
papf1.employee_Number employee_corporation_Number,papf1.full_name Employee_Name
from hxc_tk_groups tg,hxc_tk_group_queries tkgq,hxc_tk_group_query_criteria tc,per_all_people_f papf1,per_all_people_f papf2,
hxt_rotation_plans hrp
where tg.tk_group_id=tkgq.tk_group_id
and tc.TK_GROUP_QUERY_ID=tkgq.TK_GROUP_QUERY_ID
and tc.CRITERIA_ID=papf1.person_id
and tg.TK_RESOURCE_ID=papf2.person_id
and hrp.name(+)=papf1.employee_number
and sysdate between papf1.effective_start_date and papf1.effective_end_date
and sysdate between papf2.effective_start_date and papf2.effective_end_date
order by papf1.employee_number