Thursday, 27 June 2013

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

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