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);
Thursday, 27 June 2013
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;
||'/'
||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(+);
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
(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%'
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;
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
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
Subscribe to:
Posts (Atom)