Thursday 15 May 2014

Absence COnfiguration In SSHR



1) Define Function
FUnction           User Function name     Description                    Type
HR_LOA_SS   Absence Management  Absence Management  SSWA jsp function
Parameters-
 OAFunc=HR_LOA_SS&pAMETranType=SSHRMS&pAMEAppId=800&pProcessName=HR_GENERIC_APPROVAL_PRC&pItemType=HRSSA&pCalledFrom=HR_LOA_SS&pApprovalReqd=YD&pNtfSubMsg=HR_ABS_NTF_SUB_MSG&pConcAction=N
Html Call -
OA.jsp?page=/oracle/apps/per/selfservice/absence/webui/AbsenceHomePagePG&akRegionApplicationId=800

2)Define Menu -XX EMP SELF SERVICE  User menu name- Employee self service Menu type -Standard  Descrption-XX Employee Self Service
Seq-10  Promt- Absence Apply leave   Function-Absence Management

3)Define Responsibility -
Responsibility Name - A_Employee Self Service Application-Human Resources  Responsibility Key-A_EMPLOYEE SELF SERVICE  Descrption-A_Employee Self Service
Available From -Oracle Self Service Web Applications
Menu-Employee self service
Data Group -standard
Application -Human Resources

Setup Complete ...
**********************************
Raising Absence Request with Approval From PLSQL..

Friday 18 October 2013

AME Configuration In Oracle apps

AME  Configuration

Following are the steps to implement AME in R12 instance.
1. Login with SYSADMIN User.
2. Go to User Management Responsibility.
3. Click on Users and query for the username to which you want to assign AMEResponsibilities.
4. One you query the User, Update the User from search results region and then click on Assign Roles.
5. Assign following Roles to the User 
a) Approval Management Administrator
b) Approval Management System Administrator
c) Approval Management Process Owner
d) Approval Management System Viewer
e) Approval Management Business Analyst

Once finished with the Roles we will grant a permission set to the user from Functional Administrator Responsibility. Following are the steps for that

6. Go to Functional Administrator Responsibility => Security => Grants => Create Grant.
7. Enter Name of the "Grant" and effective start date. In Security Context select Grantee Type as Specific User and enter the User name whom you want to assign AMEResponsibilities (Same user to which we assigned AME Roles).
8. Select Object as AME Transaction Types and Press Next.
9. Select Data Context as All Rows and assign "AME Calling Applications" permission set and Save the Information.

We need to set the AME: Installed Profile option as well to make it work. Set this profile option to Yes at application level (Human Resources).

Try to login with the user and you can see the following responsibilities listed there
Approvals Management Administrator
Approvals Management Business Analyst

IRecruitement Interview status Update API

/***API to update the interview status  from PLANNED to CONFIRMED*****/

CREATE OR REPLACE PACKAGE BODY APPS.xx_irc_interview_update_pkg
IS
   PROCEDURE xx_irc_int_update_proc (Ntf_id VARCHAR2)
   IS
      l_inteerview_id           NUMBER;
      l_feedback                VARCHAR2 (300);
      l_notes                   VARCHAR2 (300);
      l_notes_to_candidate      VARCHAR2 (300);
      l_category                VARCHAR2 (100);
      l_result                  VARCHAR2 (100);
      l_event_id                NUMBER;
      l_start_date              DATE;
      l_end_date                DATE;
      l_object_version_number   irc_interview_details.object_version_number%TYPE;
      l_int_date                DATE;
      l_appl_name               VARCHAR2(100);
      l_applicant               VARCHAR2(100);
      l_assignment_id           NUMBER;
      l_int_type                VARCHAR2(30);

      CURSOR c1(p_event_id varchar2)
      IS
         SELECT *
           FROM irc_interview_details
          WHERE event_id = p_event_id AND status = 'PLANNED'
          and sysdate between start_date and end_date ;
   BEGIN
       BEGIN
        select text_value INTO l_event_id from wf_notification_attributes
        where name ='XX_INT_EVENT_ID' and notification_id =Ntf_id;
        EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Event Does not Exists');
           END;
             
       BEGIN
       l_object_version_number := '';

         SELECT object_version_number
           INTO l_object_version_number
           FROM irc_interview_details
          WHERE event_id = l_event_id AND status = 'PLANNED';
         EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Object version number Does not Exists');
           END;
       
      FOR c2 IN c1(l_event_id)
      LOOP
       

         irc_interview_details_api.update_irc_interview_details
                   (p_validate                      => FALSE,
                    p_interview_details_id          => c2.interview_details_id,
                    p_status                        => 'CONFIRMED',
                    p_feedback                      => c2.feedback,
                    p_notes                         => c2.notes,
                    p_notes_to_candidate            => c2.notes_to_candidate,
                    p_category                      => c2.CATEGORY,
                    p_result                        => c2.RESULT,
                    p_iid_information_category      => c2.iid_information_category,
                    p_iid_information1              => c2.iid_information1,
                    p_iid_information2              => c2.iid_information2,
                    p_iid_information3              => c2.iid_information3,
                    p_iid_information4              => c2.iid_information4,
                    p_iid_information5              => c2.iid_information5,
                    p_iid_information6              => c2.iid_information6,
                    p_iid_information7              => c2.iid_information7,
                    p_iid_information8              => c2.iid_information8,
                    p_iid_information9              => c2.iid_information9,
                    p_iid_information10             => c2.iid_information10,
                    p_iid_information11             => c2.iid_information11,
                    p_iid_information12             => c2.iid_information12,
                    p_iid_information13             => c2.iid_information13,
                    p_iid_information14             => c2.iid_information14,
                    p_iid_information15             => c2.iid_information15,
                    p_iid_information16             => c2.iid_information16,
                    p_iid_information17             => c2.iid_information17,
                    p_iid_information18             => c2.iid_information18,
                    p_iid_information19             => c2.iid_information19,
                    p_iid_information20             => c2.iid_information20,
                    p_event_id                      => c2.event_id,
                    p_object_version_number         => l_object_version_number,
                    p_start_date                    => l_start_date,
                    p_end_date                      => l_end_date
                   );
         DBMS_OUTPUT.put_line ('objectversion' || l_object_version_number);
      END LOOP;
   END xx_irc_int_update_proc;

   FUNCTION xx_get_message_type (ntf_id NUMBER)
      RETURN VARCHAR2
   IS
      l_message_type   VARCHAR2 (50);
   BEGIN
      BEGIN
         SELECT message_type
           INTO l_message_type
           FROM wf_notifications
          WHERE notification_id = ntf_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Notification Does not Exists');
      END;

      RETURN l_message_type;
   END xx_get_message_type;
END xx_irc_interview_update_pkg;
/

Tuesday 2 July 2013

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;

Thursday 27 June 2013

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;