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%'
Thursday, 27 June 2013
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
Absence -API to Delete Specific Leaves from the month
DECLARE
CURSOR c1
IS
SELECT a.absence_attendance_id, a.object_version_number,
b.employee_number, b.npw_number,
DECODE (b.employee_number,
NULL, 'Contigent Worker',
'Employee'
) person_type,
full_name, NAME leave_type, date_start leave_start_date,
date_end leave_end_date
FROM (SELECT paa.absence_attendance_id, paa.object_version_number,
NAME, person_id,
TO_CHAR (date_start, 'dd-mon-yyyy') date_start,
TO_CHAR (paa.date_end, 'dd-mon-yyyy') date_end
FROM per_absence_attendances paa,
per_absence_attendance_types paat
WHERE paa.absence_attendance_type_id =
paat.absence_attendance_type_id
AND ( ( TRUNC (date_start) >=
TRUNC (TO_DATE ('01-Apr-2013', 'dd-Mon-yyyy'))
AND TRUNC (paa.date_end) <=
TRUNC (TO_DATE ('30-Apr-2013', 'dd-Mon-yyyy'))
)
OR (TO_DATE ('01-Apr-2013', 'dd-Mon-yyyy')
BETWEEN date_start
AND paa.date_end
)
OR (TO_DATE ('30-Apr-2013', 'dd-Mon-yyyy')
BETWEEN date_start
AND paa.date_end
)
)) a,
per_all_people_f b
WHERE a.person_id = b.person_id
AND SYSDATE BETWEEN b.effective_start_date AND b.effective_end_date;
l_count number := 0;
BEGIN
dbms_output.ENABLE(100000000); ----to remove the buffer overflow error on toad
fnd_global.apps_initialize(1110,50637,800); --for calling api on Toad initialise session
FOR v_rec IN c1
LOOP
begin
hr_person_absence_api.delete_person_absence
(p_validate => FALSE,
p_absence_attendance_id => v_rec.absence_attendance_id,
p_object_version_number => v_rec.object_version_number,
p_called_from => 800
);
l_count := l_count +1;
exception
when others
then
dbms_output.put_line('Error in for loop: absence_attendance_id: ' || v_rec.absence_attendance_id || ' object_version_number: '
|| v_rec.object_version_number || ' ' || sqlerrm);
end;
END LOOP;
dbms_output.put_line('Successful Count: ' || l_count);
EXCEPTION
when others
then
dbms_output.put_line('Error occured: ' || sqlerrm);
END;
CURSOR c1
IS
SELECT a.absence_attendance_id, a.object_version_number,
b.employee_number, b.npw_number,
DECODE (b.employee_number,
NULL, 'Contigent Worker',
'Employee'
) person_type,
full_name, NAME leave_type, date_start leave_start_date,
date_end leave_end_date
FROM (SELECT paa.absence_attendance_id, paa.object_version_number,
NAME, person_id,
TO_CHAR (date_start, 'dd-mon-yyyy') date_start,
TO_CHAR (paa.date_end, 'dd-mon-yyyy') date_end
FROM per_absence_attendances paa,
per_absence_attendance_types paat
WHERE paa.absence_attendance_type_id =
paat.absence_attendance_type_id
AND ( ( TRUNC (date_start) >=
TRUNC (TO_DATE ('01-Apr-2013', 'dd-Mon-yyyy'))
AND TRUNC (paa.date_end) <=
TRUNC (TO_DATE ('30-Apr-2013', 'dd-Mon-yyyy'))
)
OR (TO_DATE ('01-Apr-2013', 'dd-Mon-yyyy')
BETWEEN date_start
AND paa.date_end
)
OR (TO_DATE ('30-Apr-2013', 'dd-Mon-yyyy')
BETWEEN date_start
AND paa.date_end
)
)) a,
per_all_people_f b
WHERE a.person_id = b.person_id
AND SYSDATE BETWEEN b.effective_start_date AND b.effective_end_date;
l_count number := 0;
BEGIN
dbms_output.ENABLE(100000000); ----to remove the buffer overflow error on toad
fnd_global.apps_initialize(1110,50637,800); --for calling api on Toad initialise session
FOR v_rec IN c1
LOOP
begin
hr_person_absence_api.delete_person_absence
(p_validate => FALSE,
p_absence_attendance_id => v_rec.absence_attendance_id,
p_object_version_number => v_rec.object_version_number,
p_called_from => 800
);
l_count := l_count +1;
exception
when others
then
dbms_output.put_line('Error in for loop: absence_attendance_id: ' || v_rec.absence_attendance_id || ' object_version_number: '
|| v_rec.object_version_number || ' ' || sqlerrm);
end;
END LOOP;
dbms_output.put_line('Successful Count: ' || l_count);
EXCEPTION
when others
then
dbms_output.put_line('Error occured: ' || sqlerrm);
END;
Wednesday, 12 June 2013
API to get employee Type Directly
SELECT hr_person_type_usage_info.get_user_person_type (SYSDATE, &person_id)
FROM DUAL
FROM DUAL
UserHook For Element entry Screen
declare
l_api_hook_call_id number;
l_object_version_number number;
L_API_HOOK_ID NUMBER;
begin
select ahk.api_hook_id INTO L_API_HOOK_ID
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name like 'CREATE_PROCESS_EVENT'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'BP'
AND ahk.api_module_id = ahm.api_module_id;
hr_api_hook_call_api.create_api_hook_call (p_validate => false,
p_effective_date => to_date('01-JUL-1999','DD-MON-YYYY'),
p_api_hook_id => L_API_HOOK_ID ,
p_api_hook_call_type => 'PP',
p_sequence => 100,
p_enabled_flag => 'Y',
p_call_package => 'XX_HR_ELEMENT_VALID_PKG_DP',
p_call_procedure => 'ELEMENT_VALIDATION',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
DBMS_OUTPUT.PUT_LINE('The hook call id :'||l_api_hook_call_id);
end;
Declare
Begin
hr_api_hook_call_api.delete_api_hook_call
(p_validate => false,
p_api_hook_call_id => 1218,
p_object_version_number =>2
);
end;
select * from hr_api_hook_calls where CALL_PACKAGE LIKE 'XX_HR_ELEMENT_VALID_PKG%'
select * from hr_api_hook_calls
where sequence = '100'
PAY_PPE_BK1
select * from hr_api_hook_calls -- where api_hook_call_id=1141
where sequence = '3030'
select * from hr_api_hook_calls
where trunc(creation_date) = trunc(sysdate);
select * from hr_api_modules ahm
where trunc(creation_date) = trunc(sysdate);
SELECT * FROM XX_TEST WHERE X=10032013
select ahk.*,ahk.api_hook_id,ahm.*-- INTO L_API_HOOK_ID
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name like 'CREATE_PROCESS_EVENT'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id;
select * from hr_api_modules where module_name like 'CREATE_PROCESS_EVENT'
SELECT * FROM HR_API_HOOK_CALLS WHERE CALL_PACKAGE LIKE ''
select c.encoded_error
from hr_api_hook_calls c
where c.api_hook_call_id = 1102;
Run Preprocessor From Toad use Below Command
DECLARE
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module(1915); -- (Pass Application Module ID )
dbms_output.put_line('Pre-Processor Run Successfully');
exception
when others then
dbms_output.put_line(sqlerrm);
END;
l_api_hook_call_id number;
l_object_version_number number;
L_API_HOOK_ID NUMBER;
begin
select ahk.api_hook_id INTO L_API_HOOK_ID
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name like 'CREATE_PROCESS_EVENT'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'BP'
AND ahk.api_module_id = ahm.api_module_id;
hr_api_hook_call_api.create_api_hook_call (p_validate => false,
p_effective_date => to_date('01-JUL-1999','DD-MON-YYYY'),
p_api_hook_id => L_API_HOOK_ID ,
p_api_hook_call_type => 'PP',
p_sequence => 100,
p_enabled_flag => 'Y',
p_call_package => 'XX_HR_ELEMENT_VALID_PKG_DP',
p_call_procedure => 'ELEMENT_VALIDATION',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
DBMS_OUTPUT.PUT_LINE('The hook call id :'||l_api_hook_call_id);
end;
Declare
Begin
hr_api_hook_call_api.delete_api_hook_call
(p_validate => false,
p_api_hook_call_id => 1218,
p_object_version_number =>2
);
end;
select * from hr_api_hook_calls where CALL_PACKAGE LIKE 'XX_HR_ELEMENT_VALID_PKG%'
select * from hr_api_hook_calls
where sequence = '100'
PAY_PPE_BK1
select * from hr_api_hook_calls -- where api_hook_call_id=1141
where sequence = '3030'
select * from hr_api_hook_calls
where trunc(creation_date) = trunc(sysdate);
select * from hr_api_modules ahm
where trunc(creation_date) = trunc(sysdate);
SELECT * FROM XX_TEST WHERE X=10032013
select ahk.*,ahk.api_hook_id,ahm.*-- INTO L_API_HOOK_ID
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name like 'CREATE_PROCESS_EVENT'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id;
select * from hr_api_modules where module_name like 'CREATE_PROCESS_EVENT'
SELECT * FROM HR_API_HOOK_CALLS WHERE CALL_PACKAGE LIKE ''
select c.encoded_error
from hr_api_hook_calls c
where c.api_hook_call_id = 1102;
Run Preprocessor From Toad use Below Command
DECLARE
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module(1915); -- (Pass Application Module ID )
dbms_output.put_line('Pre-Processor Run Successfully');
exception
when others then
dbms_output.put_line(sqlerrm);
END;
Attach Responsibility To User API
Declare
BEGIN
fnd_user_pkg.addresp(username =>'ABC' ---Username
,resp_app =>'FND' --res application code
,resp_key =>'FND_REP_APP' --resonsibilty key
,security_group =>'STANDARD'
,description =>'Integration Repository Application' --res description
,start_date =>Sysdate
,end_date => Sysdate +100);
COMMIT;
END;
select * from fnd_responsibility_vl where responsibility_name like '%App%'
select * from fnd_application where application_id=0;
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = 'ABC'
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
AND fr.responsibility_name LIKE '%SOA%'
Subscribe to:
Posts (Atom)