API TO CREATE BATCH PROCESS ELEMENT HEADER

Batch Element Enrty Creation API and Header And Line Creation


PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_HEADER(
   p_validate                     =>false
  ,p_session_date                  =>     SYSDATE
  ,p_batch_name                    =>     XXBATCHNAM||'-'||xxdate||'-'||XXSEQ
  ,p_batch_status                  =>    'U'
  ,p_business_group_id             =>     XXBG
  ,p_action_if_exists              =>     'R'
  ,p_batch_reference               =>     XXBATCHREF
  ,p_batch_source                  =>     XXBATCHREF
  ,p_comments                      =>     null
  ,p_date_effective_changes        =>     null
  ,p_purge_after_transfer          =>     'N'
  ,p_reject_if_future_changes      =>     'Y'
  ,p_batch_id                      =>    T1
  ,p_object_version_number         =>    T2
  ,p_reject_if_results_exists      =>    'Y'
  ,p_purge_after_rollback          =>     'N'
  ,p_batch_type                    =>     null
  ,p_REJECT_ENTRY_NOT_REMOVED      =>     'N'
  ,p_ROLLBACK_ENTRY_UPDATES        =>     'N'
             
 );
 commit;
dbms_output.put_line(p_batch_id );
FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_batch_id='||T1 );
dbms_output.put_line(T1 );
FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_object_version_number='||T2 );
dbms_output.put_line(T2 );
--------------------------------------------------------------------------------
for v_rec in c1 loop

select paaf.assignment_id,papf.employee_number
into xxassignid,xxempnum
from PER_ALL_ASSIGNMENTS_F paaf ,per_all_people_f papf
where papf.person_id=paaf.person_id
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and primary_flag='Y'
and papf.person_id=v_rec.person_id;
------------------------api to create batch line data---------------------------

  FND_FILE.PUT_LINE( FND_FILE.LOG, 'xxseqnum='||xxseqnum );
  FND_FILE.PUT_LINE( FND_FILE.LOG, 'xxassignid='||xxassignid );
  FND_FILE.PUT_LINE( FND_FILE.LOG, 'xxempnum='||xxempnum );
  dbms_output.put_line('xxseqnum='||xxseqnum );
  dbms_output.put_line('xxassignid='||xxassignid  );
  dbms_output.put_line('xxempnum='||xxempnum );

PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_LINE(
   p_validate                      =>FALSE    
  ,p_session_date                  =>SYSDATE    
  ,p_batch_id                      =>T1  
  ,p_batch_line_status             =>'U'
  ,p_assignment_id                 =>xxassignid  --assignment_id
  ,p_assignment_number             =>to_number(xxempnum)   --emp number
  ,p_element_type_id               =>xxeletypeid     ----element_type
  ,p_batch_sequence                =>xxseqnum
  ,p_element_name                  =>'Leave Credit Days' ---element_name
  ,p_canonical_date_format         =>'Y'  
  ,p_iv_all_internal_format        =>'N'
  ,p_batch_line_id                 =>T3  
  ,p_object_version_number         =>T4
             
 );

dbms_output.put_line(T3 );
dbms_output.put_line(T4 );
FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_object_version_number2='||T4 );
xxseqnum:=xxseqnum+1;
--------------------------------------------------------------------------------
commit;
end loop;

Call Concurrent Program with Parameters (Using PL/SQL)

                        fnd_global.APPS_INITIALIZE(0,50637,800);   -----HMC HRMS MANAGER ----------

                        dbms_output.put_line('Intialise Success'); 

                        REQ_ID := FND_REQUEST.SUBMIT_REQUEST (   application =>'PER',--'Human Resources',

                                                          program     =>'XX_HR_SIT_CREATEELEENTRY',--'HR Element Creattion From SIT', --'XX_HR_SIT_CREATEELEENTRY', 

                                                          description => NULL,

                                                          start_time  => sysdate, 

                                                          sub_request => FALSE, 

                                                          argument1  => 'Leave Advance',

                                                          argument2  => l_last_pay_run_date,

                                                          argument3   => l_business__id,

                                                          argument4   => V_Assignment_ID,

                                                          argument5  => v_element_link_id,

                                                          argument6  => v_input_value_id,

                                                          argument7   => V_Total,

                                                          argument8  => p_absence_attendance_id,

                                                          argument9  => v_input_value_id2,

                                                          argument10  => '',

                                                          argument11  => v_input_value_id3,

                                                          argument12  => ''

                                                        );

                                                        commit;

                        IF (REQ_ID = 0) THEN

                        l_err_message :='Error in Calling Concurrent Program';

                        dbms_output.put_line('Error In Creating Element Entry for Date='||l_last_pay_run_date||' and '||l_err_message||' ='||REQ_ID); 

                        else

                        dbms_output.put_line('Creating Element with Concurrent Program id='||REQ_ID);

                        END IF;

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(+);