Approval Hierarchy is skipping a approver while the position is in approval hierarchy

There can be two reasons for this problem.

i. Oracle user can be inactivated in application.
ii. if the oracle application user is active then there can be two assignments of an employee and both are active and other assignment is in other organization with the same position.

The above problem i encountered in R12.1.1 and R12.1.3

HRMS Batch Element Entries API R12.1.3

---------------BATCH HEADER UPLOADING-------------------------------
DECLARE
   l_batch_id                NUMBER := NULL;
   l_object_version_number   NUMBER := NULL;
BEGIN
   pay_batch_element_entry_api.create_batch_header (
      p_session_date            => TRUNC (SYSDATE),
      p_batch_name              => 'BATCH_THROUGH_API',
      p_business_group_id       => 81, --relevant business group id
      p_action_if_exists        => 'I',
      p_batch_id                => l_batch_id,
      p_object_version_number   => l_object_version_number
   );
   COMMIT;
   DBMS_OUTPUT.put_line (l_batch_id || ': SUCCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
---------------BATCH HEADER UPLOADING-------------------------------

-- To Create Batch Line (Batch Element Entry)-----------------------
DECLARE
   v_eff_start_date                DATE := NULL;
   v_eff_end_date                  DATE := NULL;
   v_input_value_id                NUMBER := NULL;
   v_element_link_id               NUMBER := NULL;
   v_element_name                  VARCHAR2 (500) := NULL;
   v_element_type_id               NUMBER := NULL;
   v_batch_id                      NUMBER := NULL;
   v_batch_line_id                 NUMBER := NULL;
   v_batch_object_version_number   NUMBER := NULL;

CURSOR emp_details
is
Select '3601' assignment_number, '1919' assignment_id
from dual; -- table where your data is uploaded.
--You can use in above statement you relevant data for all the records you need to enter in batch entry

BEGIN
 
   fnd_global.APPS_INITIALIZE(user_id => 1741, resp_id => 50678, resp_appl_id => 800); --non mandetory
 
   -- BLOCK to Get Batch ID
   BEGIN
      SELECT   batch_id
        INTO   v_batch_id
        FROM   pay_batch_headers pbh
       WHERE   TRIM (pbh.batch_name) = 'BATCH_THROUGH_API'; --Same as used above
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;
 
    -- Get Element Details as per your Element
   BEGIN
      SELECT   pivf.input_value_id,
               pelf.element_link_id,
               petf.element_name,
               petf.element_type_id
        INTO   v_input_value_id,
               v_element_link_id,
               v_element_name,
               v_element_type_id
        FROM   pay_element_types_f petf,
               pay_element_links_f pelf,
               pay_input_values_f pivf
       WHERE   pelf.element_type_id = petf.element_type_id
               AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date
                                       AND  petf.effective_end_date
               AND TRUNC (SYSDATE) BETWEEN pelf.effective_start_date
                                       AND  pelf.effective_end_date
               AND pelf.payroll_id = 12 --<<Your payroll id>>
               AND pivf.element_type_id = pivf.element_type_id
               AND pivf.NAME = 'Hours'--<<Name of the Input value>>
               AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date
                                       AND  pivf.effective_end_date
               AND pivf.element_type_id = pelf.element_type_id
               AND petf.element_name = 'ELEMENT NAME'; --<<Name of the Element>>
   EXCEPTION
      WHEN OTHERS
      THEN
         v_input_value_id := NULL;
         v_element_link_id := NULL;
   END;
FOR i IN emp_details
LOOP
BEGIN
   -- API to create ELEMENT
   pay_batch_element_entry_api.create_batch_line (
      p_session_date            => TRUNC (SYSDATE),
      p_batch_id                => v_batch_id,
      p_assignment_id           => i.assignment_id,
      p_assignment_number       => i.assignment_number,
      p_date_earned             => TRUNC (SYSDATE),
      p_effective_date          => TRUNC (SYSDATE),
      p_effective_start_date    => v_eff_start_date,
      p_effective_end_date      => v_eff_end_date,
      p_element_name            => v_element_name,
      p_element_type_id         => v_element_type_id,
      p_value_2                 => 3,
      p_batch_line_id           => v_batch_line_id,
      p_object_version_number   => v_batch_object_version_number
   );
   COMMIT;
   DBMS_OUTPUT.put_line (v_batch_id || ': SUCCESS');
   DBMS_OUTPUT.put_line (v_batch_line_id || ': SUCCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception for '||i.assignment_number||' : '||SQLERRM);
END;
END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
-- To Create Batch Line (Batch Element Entry)-----------------------

HRMS Absence API in oracle applications R12.1.3

DECLARE
   l_absence_attendance_id       NUMBER;
   l_object_version_number       NUMBER;
   l_occurrence                  NUMBER;
   l_dur_dys_less_warning        BOOLEAN;
   l_dur_hrs_less_warning        BOOLEAN;
   l_exceeds_pto_entit_warning   BOOLEAN;
   l_exceeds_run_total_warning   BOOLEAN;
   l_abs_overlap_warning         BOOLEAN;
   l_abs_day_after_warning       BOOLEAN;
   l_dur_overwritten_warning     BOOLEAN;
   l_absence_days                NUMBER := NULL;
   l_absence_hours               NUMBER := NULL;
   error_msg                     VARCHAR2(500);
BEGIN
   fnd_global.APPS_INITIALIZE(user_id => 1741, resp_id => 50678, resp_appl_id => 800);

   HR_PERSON_ABSENCE_API.create_person_absence (
      p_validate                     => FALSE,
      p_effective_date               => SYSDATE,
      p_person_id                    => 2009,
      p_business_group_id            => 81,
      p_absence_attendance_type_id   => 1063,
      p_abs_attendance_reason_id     => NULL,
      p_comments                     => 'Uploaded through API',
      p_date_notification            => TO_DATE ('01-MAR-2017', 'DD-MON-YYYY'),
      p_date_start                   => TO_DATE ('02-MAR-2017', 'DD-MON-YYYY'),
      p_date_end                     => TO_DATE ('03-MAR-2017', 'DD-MON-YYYY'),
      p_absence_days                 => l_absence_days,
      p_absence_hours                => l_absence_hours,
      p_attribute_category           => 'Emergency Leave', -- Absence Type
      p_absence_attendance_id        => l_absence_attendance_id,
      p_object_version_number        => l_object_version_number,
      p_occurrence                   => l_occurrence,
      p_dur_dys_less_warning         => l_dur_dys_less_warning,
      p_dur_hrs_less_warning         => l_dur_hrs_less_warning,
      p_exceeds_pto_entit_warning    => l_exceeds_pto_entit_warning,
      p_exceeds_run_total_warning    => l_exceeds_run_total_warning,
      p_abs_overlap_warning          => l_abs_overlap_warning,
      p_abs_day_after_warning        => l_abs_day_after_warning,
      p_dur_overwritten_warning      => l_dur_overwritten_warning
   );
    COMMIT;
    dbms_output.put_line('p_absence_attendance_id Message: '||l_absence_attendance_id);
    dbms_output.put_line('p_occurrence: '||l_occurrence);
    dbms_output.put_line('p_absence_days: '||l_absence_days);
   exception when others then
    error_msg := SUBSTR (SQLERRM, 1, 99);
   dbms_output.put_line('Error Message: '||error_msg);
END;

How to run concurrent request through PL/SQL

declare
lv_ret Number;
RESULT Boolean;
lv_msg VARCHAR2(1000);
begin

FND_GLOBAL.APPS_INITIALIZE(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);

        --FND_GLOBAL.Apps_Initialize(1741, 20420, 1);

        RESULT := APPS.fnd_request.set_mode (TRUE);
        lv_ret := FND_REQUEST.SUBMIT_REQUEST(application => 'PER', --Relevant application short name
                                             program     => 'XX_TICKET_BALANCE', --Your program short Name
                                             description => 'XX TICKET BALANCE', --Any Description
                                             start_time  => sysdate, --standard
                                             sub_request => false, --standard
                                             argument1 => '3890', --Relevant Paramter in sequence
                                             argument2 => '01-Jan-2015',
                                             argument3 => NULL,
                                             argument4 => '31-Dec-2015');
                                           
                                           
                                             dbms_output.put_line('1st Message: '||lv_ret);
                                             FND_MESSAGE.RETRIEVE(MSGOUT => lv_msg);
                                             dbms_output.put_line('2nd Message: ' || lv_msg);
          commit;
end;

Difference between RR, YY and RRRR, YYYY in oracle database

'RR' format was designed to complement 'YY' format; it does the same sort of thing inside a different window of years.

In 'YY' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx00 and ending with the next xx99.
In 'RR' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx50 and ending with the next xx49. That is, the window of possible dates is shifted by 50 years.
Currently, and until until 2049 (inclusive), 'RR' years will always be in the range 1950 through 2049, inclusive. That is, the years returned by TO_DATE (x, 'RR') will be between 1950 and 2049.
Starting in 2050, 'RR' years will always be in the range 2050 through 2149, inclusive.

Both 'YY' and 'RR' format are bad. You should not be using 2-digit years. Always use 4-digit years. The small amount of extra typing that users have to do whenever they enter a 4-digit year is very little compared to the work needed to correct errors that always happen when you use 2-digit years.

Hence in case of RRRR it does not matter if you use RR or RRRR it will return the same result.

Example: 
select to_date('20-Mar-59','DD-MON-RR') test FROM DUAL
03/20/1959

select to_date('20-Mar-59','DD-MON-RRRR') test FROM DUAL
03/20/1959

select to_date('20-Mar-49','DD-MON-RR') test FROM DUAL
03/20/2049

select to_date('20-Mar-49','DD-MON-RRRR') test FROM DUAL
03/20/2049

NOTE:
For YY & YYYY formats it does matter.
select to_date('20-Mar-49','DD-MON-YY') test FROM DUAL
03/20/2049

select to_date('20-Mar-49','DD-MON-YYYY') test FROM DUAL
03/20/0049

select to_date('20-Mar-49','DD-MON-YY') test FROM DUAL
03/20/2049

select to_date('20-Mar-59','DD-MON-YY') test FROM DUAL
03/20/2059


select to_date('20-Mar-59','DD-MON-YYYY') test FROM DUAL

03/20/0059

Reference: http://www.club-oracle.com/resources/difference-between-%E2%80%98yyyy%E2%80%99-and-%E2%80%98rrrr%E2%80%99-in-date-format.10872/
https://community.oracle.com/thread/974281?start=0&tstart=0

Landed Cost Management R12

Attached is the database schema of LCM and its integration with purchasing, receiving, inventory and costing.

View password of application user in Oracle E-Business Suite R12

Login to Apps user

Step 1:

--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

Step 2:
--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

Step 3:
Query to get password for apps user.

SELECT
(SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password
 FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL))
Step 4:
 --Query for finding any application user
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';




---REFERENCE
http://muthunagaraj.blogspot.com/2012/07/steps-for-finding-password-of-user-in.html