Oracle Technical Applications And Objects

Thursday, 27 June 2013

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

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;

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

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;

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%'

Create Element Entry and Create Blance Adjustment

API-Element Entry Creation

pay_element_entry_api.create_element_entry
(p_validate => l_validate_only,
p_business_group_id => l_bus_group_id,
p_entry_type => l_entry_type,
p_assignment_id => l_assignment_id,
p_input_value_id1 => l_input_value_id1,
p_input_value_id2 => l_input_value_id2,
p_input_value_id3 => l_input_value_id3,
p_input_value_id4 => l_input_value_id4,
p_input_value_id5 => l_input_value_id5,
p_input_value_id6 => l_input_value_id6,
-- p_entry_value1 => rec_elemnt.input_value1,
-- p_entry_value2 => to_char(to_date(rec_elemnt.input_value2,'MM/DD/YYYY'),'DD-Mon-YYYY'),
--rec_elemnt.input_value2,
p_entry_value3 => to_char(to_date(rec_elemnt.input_value3,'MM/DD/YYYY'),'DD-Mon-YYYY'),
p_entry_value4 => rec_elemnt.input_value4,
p_entry_value5 => rec_elemnt.input_value5,
p_entry_value6 => rec_elemnt.input_value6,
p_element_link_id => l_element_link_id,
-- p_personal_payment_method_id => r1.pay_method_id,
p_effective_date => TRUNC
(rec_elemnt.date_from
),
--TRUNC(l_a_effective_start_date),--to_date('04/01/2013','MM/DD/YYYY'),
p_element_entry_id => l_element_entry_id,
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_create_warning => l_create_warning
);

Create The adjustment API-

pay_balance_adjustment_api.create_adjustment
(p_validate => FALSE,
p_effective_date => to_date('04/01/2013','MM/DD/YYYY'), -- change as per requirement earlier it was sysdate
--TRUNC (rec_elemnt.date_from),
p_assignment_id => l_assignment_id,
p_consolidation_set_id => l_consolidation_set_id,
p_element_link_id => l_element_link_id,
p_input_value_id1 => l_input_value_id1,
p_input_value_id2 => l_input_value_id2,
p_input_value_id3 => l_input_value_id3,
p_input_value_id4 => l_input_value_id11,
p_input_value_id5 => l_input_value_id22,
p_entry_value1 => 0,--nvl(rec_elemnt.bal_input_value1,0),--input_value1
p_entry_value2 => 0,--nvl(rec_elemnt.bal_input_value2,0),--input_value2
--p_entry_value3 => 0,--nvl(rec_elemnt.bal_input_value3,0),--input_value3
p_entry_value4 => rec_elemnt.bal_input_value1,
p_entry_value5 => rec_elemnt.bal_input_value2,--nvl(rec_elemnt.bal_input_value4,0),--input_value4
p_element_entry_id => l_element_entry_id1,
p_effective_start_date => l_effective_start_date1,
p_effective_end_date => l_effective_end_date1,
p_object_version_number => l_object_version_number1,
p_create_warning => l_create_warning1
);

Delete Element and Respective Adjustment Deletion.

***************Delete all the Adjustment Elements**************************************

Declare

Cursor c1 Is select * from pay_element_entries_f where element_type_id in(
select element_type_id from pay_element_types_f where element_name like 'Staff Health Insurance%' )
and entry_type='B' and assignment_id=62;
begin

For v_rec in c1 loop
pay_balance_adjustment_api.DELETE_ADJUSTMENT(
p_validate =>false,
p_effective_date=>'01-Apr-2013',
p_element_entry_id=>v_rec.element_entry_id);
dbms_output.put_line('Done');
end loop;

Exception

When Others then
dbms_output.put_line('Error='||SQLERRM);
end;

Delete Element Entry after delting the Adjustment
**************************************************************************
Declare
l_element_entry_id NUMBER ;
l_ovn NUMBER ;
l_effstart_date DATE ;
v_count_succ NUMBER := 0;
v_count_err NUMBER := 0;
P_EFFECTIVE_START_DATE DATE ;
P_EFFECTIVE_END_DATE DATE ;
P_DELETE_WARNING BOOLEAN ;

CURSOR C1
IS
SELECT ent.element_entry_id,ent.object_version_number,ent.effective_start_date
--INTO l_element_entry_id ,l_ovn,l_effstart_date
FROM pay_element_entries_f ENT, pay_element_types_f ELE
WhERE ELE.business_group_id=81
AND ELE.element_type_id=ENT.element_type_id
AND ELE.element_name='Staff Health Insurance'
--AND ENT.ASSIGNMENT_ID in (123,154) ------need to be commented
AND trunc(sysdate) between ele.effective_start_date AND ele.effective_end_date
and ent.entry_type='E' and ent.assignment_id=62;

BEGIN
DBMS_OUTPUT.PUT_LINE('l_effstart_date For element entry=1'||l_effstart_date);
FOR i IN C1
LOOP

BEGIN
pay_element_entry_api.DELETE_ELEMENT_ENTRY (P_VALIDATE => FALSE
,P_DATETRACK_DELETE_MODE => 'ZAP'
,P_EFFECTIVE_DATE => i.effective_start_date
,P_ELEMENT_ENTRY_ID => i.element_entry_id
,P_OBJECT_VERSION_NUMBER => i.object_version_number
,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
,P_DELETE_WARNING => P_DELETE_WARNING
);
v_count_succ := v_count_succ+1;
DBMS_OUTPUT.PUT_LINE('Successful Count For element entry'||v_count_succ);
DBMS_OUTPUT.PUT_LINE('Successful Count For element entry'||l_element_entry_id);
EXCEPTION
WHEN OTHERS
THEN
v_count_err := v_count_err +1;
DBMS_OUTPUT.PUT_LINE('l_effstart_date For element entry'||l_effstart_date);
DBMS_OUTPUT.PUT_LINE('Unsuccessful CountFor element entry'||v_count_err);
DBMS_OUTPUT.PUT_LINE('Error For element entry'||l_element_entry_id);
DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace||'Exception : '||SQLERRM);
END;
END LOOP;
COMMIT;
END;