OLM-Oracle Learning Management List of APIs

Conference Server OTA_CONFERENCE_SERVER_API Learning Management PL/SQL Oracle Active This package contains the Conference server APIs.
Course Prerequisite OTA_COURSE_PREREQUISITE_API Learning Management PL/SQL Oracle Active This package contains course prerequisite APIs.
Create Delegate Booking/update/Delete
Enrollment OTA_DELEGATE_BOOKING_API Learning Management PL/SQL Oracle Active This package manages a learner enrollment in a class.
Announcement OTA_ANNOUNCEMENT_API Learning Management PL/SQL Oracle Active This package contains the Announcement APIs.

Course Category OTA_ACTIVITY_CATEGORY_API Learning Management PL/SQL Oracle Active This package contains the Course Category API.
Category Usage OTA_CATEGORY_USAGE_API Learning Management PL/SQL Oracle Active This package contains the category usage APIs that create or update a Category or Delivery Mode.

Learning Certification
Certification Category Inclusion OTA_CERT_CATEGORY_API Learning Management PL/SQL Oracle Active This package contains Certification Category Inclusion APIs.
Certification Enrollment OTA_CERT_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains Learning Certification Enrollment APIs.
Certification Member OTA_CERT_MEMBER_API Learning Management PL/SQL Oracle Active This package contains learning certification member APIs.
Certification Member Enrollment OTA_CERT_MBR_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains Certification Member Enrollment APIs.
Certification Period Enrollment OTA_CERT_PRD_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains Learning Certification Period Enrollment APIs.
Learning Certification OTA_CERTIFICATION_API Learning Management PL/SQL Oracle Active This package contains learning certification APIs.
OTA_TRAINING_RECORD OTA_TRAINING_RECORD Learning Management PL/SQL Oracle Active This is the source file to query certification and course details

Learning Chat
Chat Inclusion APIs OTA_CHAT_OBJ_INCLUSION_API Learning Management PL/SQL Oracle Active This package contains Category Chat and Class Chat association-related APIs.
Chat Message OTA_CHAT_MESSAGE_API Learning Management PL/SQL Oracle Active This package contains chat messages related APIs.
Chat Users APIs OTA_CHAT_USER_API Learning Management PL/SQL Oracle Active This package contains chat user-related APIs.
Chats OTA_CHAT_API Learning Management PL/SQL Oracle Active This package contains chat related APIs.

Learning Class
Class OTA_EVENT_API Learning Management PL/SQL Oracle Active This package contains the class APIs.

Learning Courses
Course OTA_ACTIVITY_VERSION_API Learning Management PL/SQL Oracle Active This package contains the course APIs.
Course Other Information OTA_SKILL_PROVISION_API Learning Management PL/SQL Oracle Active This package contains the Course Other Information APIs.

Learning Cross Charge Setup
Cross Charge OTA_TCC_API Learning Management PL/SQL Oracle Active This package contains the APIs to create and update a cross charge.
General Ledger Flexfield OTA_THG_API Learning Management PL/SQL Oracle Active This API maintains the detail mappings between Oracle General Ledger's Chart of Account keyflex segments and Oracle Human Resources Cost Allocation keyflex segments.

Learning Enrollment Justification
Booking Justification OTA_BKNG_JUSTIFICATION_API Learning Management PL/SQL Oracle Active This package contains Booking Justification APIs.

Learning Enrollment Status Type
Enrollment Status OTA_BOOKING_STATUS_TYPE_API Learning Management PL/SQL Oracle Active This package contains Enrollment status APIs.

Learning External Record
External Learning OTA_NHS_API Learning Management PL/SQL Oracle Active This package creates and updates a user's external learning.

Learning Finance Header
Finance Header OTA_FINANCE_HEADER_API Learning Management PL/SQL Oracle Active This package contains finance header APIs.
Learning Finance line
Finance Lines. OTA_FINANCE_LINE_API Learning Management PL/SQL Oracle Active This package contains Finance Lines APIs.

Learning Forum
Forum OTA_FORUM_API Learning Management PL/SQL Oracle Active This package contains forum-related APIs.
Forum Inclusion APIs OTA_FRM_OBJ_INCLUSION_API Learning Management PL/SQL Oracle Active This package contains Category Forum and Class Forum association-related APIs.
Forum Messages OTA_FORUM_MESSAGE_API Learning Management PL/SQL Oracle Active This package contains forum message-related APIs.
Forum Notification Subscribers OTA_FRM_NOTIF_SUBSCRIBER_API Learning Management PL/SQL Oracle Active This package contains forum notification subscriber-related APIs.
Forum Threads OTA_FORUM_THREAD_API Learning Management PL/SQL Oracle Active This package contains forum thread-related APIs.
Open Forum Chat Enrollments OTA_OPEN_FC_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains category-forum and category-chat enrollments related APIs.
Private Forum Thread Users OTA_PVT_FRM_THREAD_USERS_API Learning Management PL/SQL Oracle Active This package contains APIs related to private forum thread users.

Learning Offer
Offering OTA_OFFERING_API Learning Management PL/SQL Oracle Active This package contains the offering APIs.

Learning Offering Resource Checklist
Resource Usage OTA_RESOURCE_USAGE_API Learning Management PL/SQL Oracle Active This package creates, updates, and deletes resource associations at the offering level.

Learning Paths
Learning Path OTA_LEARNING_PATH_API Learning Management PL/SQL Oracle Active This package contains the Learning Path APIs.
Learning Path Section OTA_LP_SECTION_API Learning Management PL/SQL Oracle Active This package contains learning path section APIs.

Learning Path Category
Learning Path Category Inclusion OTA_LP_CATEGORY_API Learning Management PL/SQL Oracle Active This package contains Learning Path Category Inclusion APIs.

Learning Path Component
Learning Path Component OTA_LP_MEMBER_API Learning Management PL/SQL Oracle Active This package contains Learning Path Component APIs.

Learning Path Component
Learning Path Component Enrollment OTA_LP_MEMBER_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains learning path component enrollment APIs.
Learning Path Enrollment OTA_LP_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains learning path enrollment APIs.

Learning Resource
Resource Definition OTA_RESOURCE_DEFINITION_API Learning Management PL/SQL Oracle Active This package contains Resource definition section APIs.

Learning Resource Booking
Resource Booking OTA_RESOURCE_BOOKING_API Learning Management PL/SQL Oracle Active This package contains the Resource Booking APIs.

Training Plan
Measurement Type OTA_TMT_API Learning Management PL/SQL Oracle Active This package contains the measurement type APIs for use by Organization Training Plans.
Training Plan OTA_TPS_API Learning Management PL/SQL Oracle Active The APIs in this package create, update, and delete personal or organization training plans.
Training Plan Component OTA_TPM_API Learning Management PL/SQL Oracle Active The APIs in this package create, update, and delete Personal and Organization Training Plan components.
Training Plan Cost OTA_TPC_API Learning Management PL/SQL Oracle Active This package contains the Organization Training Plan Cost APIs.

FND-Query To get Fnd Attched Documents

SELECT fl.file_name, fl.file_data
  FROM fnd_lobs fl, fnd_attached_documents fad, fnd_documents_tl fdl
  WHERE fad.pk1_value = TO_CHAR (:l_document_id)
   AND fad.pk2_value = TO_CHAR (l_revision_number)
   AND fdl.document_id = fad.document_id
   AND fdl.media_id = fl.file_id
   AND fad.entity_name = :l_entity_name;

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

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;

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;