Fast Formula - Update error (APP-PAY-06153)

While trying to update a Fast Formula (Accrual Carryover) and encountering the below error.

APP-PAY-06153: System Error: Procedure ff_formulas_f_pkg.update_row at step 1.

This error is not limited to Accrual Carryover Formulas rather for all types of Fast Formulas.

Solution:

Please make sure you have FND Logging turned off (either at the Site or User Level)

FND: Log Enabled - No
FND: Log Level - Blank
FND: Log Module - Blank

And then Verify the Fast Formula again

Create Contact(Depandant) API Script

Create Contact(Depandant) API Script 

DECLARE
   l_validate_mode             BOOLEAN         := FALSE;
   l_datetrack_update_mode     VARCHAR2 (30);
   l_row_id                    ROWID;
   l_business_group_id         NUMBER;
   l_contact_type              VARCHAR2 (30);
   l_contact_title             VARCHAR2 (30);
   l_primary_flag              VARCHAR2 (30);
   l_attribute16               VARCHAR2 (150);
   l_contact_full_name         VARCHAR2 (240);
   l_person_type_id            NUMBER;
   l_sex                       VARCHAR2 (30);
   l_per_start_date            DATE;
   l_per_end_date              DATE;
   l_per_comment_id            NUMBER;
   l_name_comb_warning         BOOLEAN;
   l_contact_relationship_id   NUMBER;
   l_contact_rel_ovn           NUMBER;
   l_contact_person_id         NUMBER;
   l_contact_person_ovn        NUMBER;
   l_errors                    VARCHAR2 (100);
   l_orig_hire_warning         BOOLEAN;
   l_person_id                 NUMBER;
   l_start_date                DATE;
   l_martial_status            VARCHAR2 (10);
   l_nationality               VARCHAR2 (25);
   l_error                     VARCHAR2 (2500);

   CURSOR cur_ee
   IS
      SELECT *
        FROM xxbtm_contact_stg_v1
       WHERE record_status IS NULL AND sno <= 40;
BEGIN
   FOR c1_ee_rec IN cur_ee
   LOOP
      l_person_id := NULL;
      l_start_date := NULL;
      l_martial_status := NULL;
      l_nationality := NULL;
      l_business_group_id := NULL;
      l_contact_rel_ovn:=null;
      l_contact_person_id:=null;
      l_contact_person_ovn:=null;
      l_per_start_date:=null;
      l_per_end_date:=null;
      l_contact_full_name:=null;
      l_per_comment_id:=null;
      l_name_comb_warning:=null;
      l_orig_hire_warning:=null;

      BEGIN
         SELECT person_id, effective_start_date, business_group_id
           INTO l_person_id, l_start_date, l_business_group_id
           FROM per_all_people_f
          WHERE attribute6 = c1_ee_rec.legacy_emp_num
            AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                    AND effective_end_date;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := 'Invalid Emp Details' || SQLERRM;
      END;

      IF l_start_date < c1_ee_rec.date_of_birth
      THEN
         l_start_date := c1_ee_rec.date_of_birth;
      ELSE
         l_start_date := l_start_date;
      END IF;

      BEGIN
         SELECT TRIM (lookup_code)
           INTO l_martial_status
           FROM hr_lookups
          WHERE lookup_type = 'MAR_STATUS'
            AND UPPER (meaning) = UPPER (c1_ee_rec.martial_status);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := 'Invalid MARTIAL_STATUS' || SQLERRM;
      END;

      BEGIN
         SELECT TRIM (lookup_code)
           INTO l_nationality
           FROM hr_lookups
          WHERE lookup_type = 'NATIONALITY'
            AND UPPER (meaning) = UPPER (c1_ee_rec.nationality);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := 'Invalid NATIONALITY' || SQLERRM;
      END;

      IF TRIM (l_error) IS NULL
      THEN
         BEGIN
            hr_contact_rel_api.create_contact
                     (p_validate                       => FALSE,
                      p_date_start                     => l_start_date,
                      p_start_date                     => l_start_date,
                      p_personal_flag                  => 'Y',
                      p_business_group_id              => l_business_group_id,
                      p_person_id                      => l_person_id,
                      p_contact_type                   => 'C',
                      p_last_name                      => c1_ee_rec.last_name,
                      p_sex                            => SUBSTR
                                                             (c1_ee_rec.gender,
                                                              1,
                                                              1
                                                             ),
                      p_person_type_id                 => 1122,
                      p_date_of_birth                  => c1_ee_rec.date_of_birth,
                      p_first_name                     => c1_ee_rec.first_name,
                      p_marital_status                 => l_martial_status,
                      p_nationality                    => l_nationality,
                      p_title                          => UPPER
                                                              (c1_ee_rec.title),
                      p_contact_relationship_id        => l_contact_relationship_id,
                      p_ctr_object_version_number      => l_contact_rel_ovn,
                      p_per_person_id                  => l_contact_person_id,
                      p_per_object_version_number      => l_contact_person_ovn,
                      p_per_effective_start_date       => l_per_start_date,
                      p_per_effective_end_date         => l_per_end_date,
                      p_full_name                      => l_contact_full_name,
                      p_per_comment_id                 => l_per_comment_id,
                      p_name_combination_warning       => l_name_comb_warning,
                      p_orig_hire_warning              => l_orig_hire_warning
                     );

            UPDATE xxbtm_contact_stg_v1
               SET record_status = 'S'
             WHERE sno = c1_ee_rec.sno;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM;

               UPDATE xxbtm_contact_stg_v1
                  SET record_status = 'E',
                      error_message = l_error
                WHERE sno = c1_ee_rec.sno;
         END;
      ELSE
         UPDATE xxbtm_contact_stg_v1
            SET record_status = 'E',
                error_message = l_error
          WHERE sno = c1_ee_rec.sno;
      END IF;
   END LOOP;

   COMMIT;
END;
/

Create Element Input Values API Script

Create Element Input Values API Script

DECLARE
   l_effective_date          DATE;
   l_business_group_id       NUMBER         := 81;
   l_element_type_id         NUMBER         := NULL;
   l_input_value_name        VARCHAR2 (100);
   l_uom                     VARCHAR2 (10);                         -- Number
   l_element_name            VARCHAR2 (100);
   l_input_value_id          NUMBER;
   l_object_version_number   NUMBER;
   l_effective_start_date    DATE;
   l_effective_end_date      DATE;
   l_default_val_warning     BOOLEAN;
   l_min_max_warning         BOOLEAN;
   l_pay_basis_warning       BOOLEAN;
   l_formula_warning         BOOLEAN;
   l_assignment_id_warning   BOOLEAN;
   l_formula_message         VARCHAR2 (500) := NULL;
   l_error  varchar2(2500);

   CURSOR cur_ee
   IS
      SELECT *
        FROM XXTZS_ELE_INPUTVALUES_STG
       WHERE record_status IS NULL AND sno <= 300;
BEGIN
   FOR c1_ee_rec IN cur_ee
   LOOP
   l_effective_date :=NULL;
   l_element_type_id :=NULL;
   l_input_value_id :=NULL;
   l_object_version_number :=NULL;
 
   BEGIN
      SELECT element_type_id, effective_start_date, element_name
        INTO l_element_type_id, l_effective_date, l_element_name
        FROM pay_element_types_f petf
       WHERE TRUNC (SYSDATE) BETWEEN petf.effective_start_date
                                 AND petf.effective_end_date
         AND petf.business_group_id = l_business_group_id
         AND UPPER (petf.element_name) = UPPER (c1_ee_rec.element_name);
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error :='Invalid Element'|| SQLERRM;
         END;

      IF l_element_type_id IS NOT NULL
      THEN
         BEGIN
            pay_input_value_api.create_input_value
                         (p_validate                   => FALSE,
                          p_effective_date             => l_effective_date,--TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
                          p_element_type_id            => l_element_type_id,
                          p_name                       => c1_ee_rec.NAME,
                          p_uom                        => c1_ee_rec.UOM,
                          P_GENERATE_DB_ITEMS_FLAG     => c1_ee_rec.GENERATE_DB_ITEMS_FLAG,
                          P_DISPLAY_SEQUENCE           => c1_ee_rec.DISPLAY_SEQUENCE,
                          p_input_value_id             => l_input_value_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_default_val_warning        => l_default_val_warning,
                          p_min_max_warning            => l_min_max_warning,
                          p_pay_basis_warning          => l_pay_basis_warning,
                          p_formula_warning            => l_formula_warning,
                          p_assignment_id_warning      => l_assignment_id_warning,
                          p_formula_message            => l_formula_message
                         );

            UPDATE XXTZS_ELE_INPUTVALUES_STG
               SET record_status = 'S'
             WHERE sno = c1_ee_rec.sno;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM;

               UPDATE XXTZS_ELE_INPUTVALUES_STG
                  SET record_status = 'E',
                      error_message = l_error
                WHERE sno = c1_ee_rec.sno;
         END;
      ELSE
         UPDATE XXTZS_ELE_INPUTVALUES_STG
            SET record_status = 'E',
                error_message = 'INPUT VALUE ALREADY EXIST'
          WHERE sno = c1_ee_rec.sno;
      END IF;
   END LOOP;
   COMMIT;
END;

Create Element Types API Script

Create Element Types API Script

DECLARE
   l_classification_id             NUMBER := NULL;
   l_event_group_id                NUMBER := NULL;
   l_formula_id                    NUMBER := NULL;
   l_element_name                  VARCHAR2 (500) ;
   l_reporting_name                varchar2(250);
   l_description                   varchar2(500);
   l_element_type_id               NUMBER := NULL;
   l_effective_start_date          DATE := NULL;
   l_effective_end_date            DATE := NULL;
   l_object_version_number         NUMBER := NULL;
   l_comment_id                    NUMBER := NULL;
   l_processing_priority_warning   BOOLEAN := NULL;
   L_COUNT NUMBER;
   l_error  VARCHAR2(2500);
   CURSOR cur_ee
   IS
      SELECT *
        FROM XXTZS_ELEMENT_MOVE_STG
       WHERE record_status IS NULL AND sno <=55;
BEGIN
   FOR c1_ee_rec IN cur_ee
   LOOP
   l_element_type_id:=NULL;
       l_effective_start_date:=NULL;
      l_effective_end_date:=NULL;
      l_object_version_number:=NULL;
      l_comment_id:=NULL;
      l_processing_priority_warning:=NULL;
   BEGIN
   SELECT COUNT(*) INTO L_COUNT FROM PAY_ELEMENT_TYPES_F WHERE ELEMENT_NAME=c1_ee_rec.ELEMENT_NAME;
   END;
   IF L_COUNT=0 THEN
   BEGIN
   pay_element_types_api.create_element_type (
      p_validate                       => FALSE,
      p_effective_date                 => TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
      p_classification_id              => c1_ee_rec.CLASSIFICATION_ID,
      p_element_name                   => c1_ee_rec.ELEMENT_NAME,
      p_input_currency_code            => 'TZS',
      p_output_currency_code           => 'KES',
      p_multiple_entries_allowed_fla   => c1_ee_rec.MULTIPLE_ENTRIES_ALLOWED_FLAG,
      p_processing_type                => c1_ee_rec.PROCESSING_TYPE, --N -> Non Recurring R -> Recurring
      p_business_group_id              => 81,
--      p_legislation_code               => NULL,
      p_formula_id                     => c1_ee_rec.FORMULA_ID,
      p_reporting_name                 => c1_ee_rec.REPORTING_NAME,
      p_description                    => c1_ee_rec.DESCRIPTION,
      p_processing_priority            =>c1_ee_rec.PROCESSING_PRIORITY,
      p_post_termination_rule          =>NVL(c1_ee_rec.POST_TERMINATION_RULE,'L'),
      p_attribute_category             =>c1_ee_rec.CONTEXT_VALUE,
      p_attribute1                     =>c1_ee_rec.ATTRIBUTE1,
--      p_attribute1                     =>14,
--      p_recalc_event_group_id          => l_event_group_id,
      p_element_type_id                => l_element_type_id,
      p_effective_start_date           => l_effective_start_date,
      p_effective_end_date             => l_effective_end_date,
      p_object_version_number          => l_object_version_number,
      p_comment_id                     => l_comment_id,
      p_processing_priority_warning    => l_processing_priority_warning);
   
      UPDATE XXTZS_ELEMENT_MOVE_STG
               SET record_status = 'S'
             WHERE sno = c1_ee_rec.sno;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM;

               UPDATE XXTZS_ELEMENT_MOVE_STG
                  SET record_status = 'E',
                      error_message = l_error
                WHERE sno = c1_ee_rec.sno;
      END;
      ELSE
         UPDATE XXTZS_ELEMENT_MOVE_STG
            SET record_status = 'E',
                error_message = 'ELEMENT ALREADY EXIST'
          WHERE sno = c1_ee_rec.sno;
      END IF;
      END LOOP;
  COMMIT;
END;

API Creating Assignment sets and Inserting records into Assignment sets Creating Assignment set

API Creating Assignment sets and Inserting records into Assignment sets
Creating Assignment set:

 There is no api to create assignment sets, we need to insert as below.

1.
insert into HR_ASSIGNMENT_SETS
( ASSIGNMENT_SET_ID,
BUSINESS_GROUP_ID,
PAYROLL_ID,
ASSIGNMENT_SET_NAME,
FORMULA_ID)
values
(HR_ASSIGNMENT_SETS_S.nextval,
81,
106,
'DIEJUL20143',
NULL
)

/*
/* API TO Create Assignment Set */

DECLARE
   l_payroll_id            NUMBER := NULL;
   l_assignment_set_name   VARCHAR2 (500) := 'KING_SALMAN_BONUS_RIYADH';
   l_assignment_set_id     NUMBER := NULL;
BEGIN
   -- Get Payroll ID
   BEGIN
      SELECT payroll_id
        INTO l_payroll_id
        FROM pay_all_payrolls_f
       WHERE     payroll_name = 'Monthly Payroll'
             AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                     AND effective_end_date;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_payroll_id := NULL;
   END;

   BEGIN
      hr_jp_ast_utility_pkg.create_asg_set (
         p_assignment_set_name   => l_assignment_set_name,
         p_business_group_id     => 40,
         p_payroll_id            => l_payroll_id,
         p_assignment_set_id     => l_assignment_set_id);
      COMMIT;
      DBMS_OUTPUT.put_line (
         l_assignment_set_id || ' has been Created Successfully !!!');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
*/

2.Inserting row into assignment set using api...

HR_ASSIGNMENT_SET_AMDS_PKG.insert_row
(
p_rowid in out varchar2,
p_assignment_id in number,
p_assignment_set_id in number,
p_include_or_exclude in varchar2);

Example:

DECLARE
p_rowid varchar2(20);-- := null;
BEGIN
HR_ASSIGNMENT_SET_AMDS_PKG.insert_row
(
p_rowid=> p_rowid,
p_assignment_id => 7687,
p_assignment_set_id =>96118,
p_include_or_exclude =>'I');
END;


Other Queries:

SELECT * FROM HR_ASSIGNMENT_SETS
WHERE ASSIGNMENT_SET_NAME = 'DIEJUL20143'

SELECT * FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_SET_ID = 96118

 select HR_ASSIGNMENT_SETS_S.nextval  from dual

Oracle HRMS API – Delete Employee Element Entry

Oracle HRMS API – Delete Employee Element Entry


DECLARE
   l_effective_start_date   DATE := NULL;
   l_effective_end_date     DATE := TO_DATE ('31-MAR-2015');
   l_update_warning         BOOLEAN;
   l_element_type_id        NUMBER := NULL;
   l_input_stop_salary_id   NUMBER := NULL;
   l_business_group_id      NUMBER := 81;
   l_effective_date         DATE := TO_DATE ('31-MAR-2015');
   l_error                  VARCHAR2 (1000);
   l_delete_warning         BOOLEAN;

   CURSOR c1
   IS
      SELECT *
        FROM XX_ELE_ENTRY_DATE_STAG
       WHERE status_Flag IS NULL;
BEGIN
   FOR c1_ee_rec IN c1
   LOOP
      IF TRIM (l_error) IS NULL
      THEN
         BEGIN
            pay_element_entry_api.delete_element_entry (
               p_validate                => FALSE,
               p_datetrack_delete_mode   => 'DELETE',
               p_effective_date          => l_effective_date,
               p_element_entry_id        => c1_ee_rec.element_entry_id,
               p_object_version_number   => c1_ee_rec.object_version_number,
               p_effective_start_date    => l_effective_start_date,
               p_effective_end_date      => l_effective_end_date,
               p_delete_warning          => l_delete_warning);

            COMMIT;
            DBMS_OUTPUT.put_line (
               'Element Entry has been Updated: '
               || c1_ee_rec.element_entry_id);

            UPDATE XX_ELE_ENTRY_DATE_STAG
               SET status_flag = 'S'
             WHERE ID = c1_ee_rec.ID;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                     'Inner Exception: '
                  || SQLERRM
                  || ' - '
                  || c1_ee_rec.element_entry_id);

               UPDATE XX_ELE_ENTRY_DATE_STAG
                  SET status_flag = 'E', error_message = l_error
                WHERE ID = c1_ee_rec.ID;
         END;
      ELSE
         UPDATE XX_ELE_ENTRY_DATE_STAG
            SET status_flag = 'E', error_message = l_error
          WHERE ID = c1_ee_rec.ID;
      END IF;
   END LOOP;

   COMMIT;
END;

Query to fetch Responsibilities attached to a User

The below query will fetch the responsibilities assigned to a particular user.
SELECT
    fu.user_id,
    fu.user_name,
    fr.responsibility_name,
    fr.description,
    fa.application_name
FROM fnd_user fu,
     fnd_user_resp_groups g,
     fnd_application_tl fa,
     fnd_responsibility_tl fr
WHERE
     g.user_id(+) = fu.user_id
     AND g.responsibility_application_id = fa.application_id
     AND fa.application_id = fr.application_id
     AND g.responsibility_id = fr.responsibility_id
     AND fu.user_name  =UPPER('User_Name');

How To Add More Detailed Information In FYI Notifications for Users to See Transaction Details In Self Service

How can users see the Transactional Details in FYI notifications in Self Service?

SOLUTION

Notification recipients can view transaction changes on the FYI Notification page after transactions are either approved or rejected.
The system administrator must add the parameter &pNtfFyiDetails=Y  to the _SS functions.

1. System Administrator responsibility
2. Application > Function
3. Add &pNtfFyiDetails=Y into form parameters after the &pCalledFrom=<FUNCTION>.
4. Save

If this parameter is set to Y, then the FYI Notification page displays a Details region with the Proposed column and a Supporting Documents region.

If any attachments are added to a transaction's Review page, then these attachments appear as a link on the FYI Notification page.
For example, after a manager approves a leave of absence transaction, the recipient who receives the FYI notification can view the absence details that have been approved. The recipient need not navigate to the relevant self-service pages in the application to understand the changes that have been approved.

Create/Update Organization - HRMS APIs

Create/Update Organization - HRMS APIs

-----------------------------------
--Create Organization
-----------------------------------
declare
l_validate_mode          BOOLEAN := TRUE;
l_begin_date             DATE    := SYSDATE;
l_end_date               DATE    := hr_general.end_of_time;
l_business_group_id      NUMBER  := 101;
l_orgname                hr_all_organization_units.name%TYPE := 'Test Org';
l_intl_extl              fnd_lookup_values.meaning%TYPE;
l_organization_id        hr_all_organization_units.organization_id%TYPE;
l_object_version_number  hr_all_organization_units.object_version_number%TYPE;
l_duplicate_org_warning  BOOLEAN;
begin
  apps.hr_organization_api.create_organization
  (
     p_validate                    => l_validate_mode
    ,p_effective_date           => l_begin_date --Reference date for validating lookup values
    ,p_business_group_id    => l_business_group_id
    ,p_date_from                => l_begin_date --Date the organization takes effect
    ,p_name                        => l_orgname
    ,p_location_id                => null
    ,p_date_to                     => l_end_date
    ,p_internal_external_flag  => l_intl_extl--'INT' --Internal/External org flag
    ,p_internal_address_line  => null
    ,p_type                           => null  --Org type -- Lookup Type ORG_TYPE
    ,p_comments                   => null
    ,p_attribute_category       => null
    ,p_attribute1                    => null
    ,p_attribute2                    => 'Test Org'
    ,p_attribute3                    => null
    ,p_attribute4                    => null
    ,p_attribute5                    => null
    --Out Variables
    ,p_organization_id                 => l_organization_id
    ,p_object_version_number      => l_object_version_number
    ,p_duplicate_org_warning       => l_duplicate_org_warning
    );

   if l_organization_id is null or l_object_version_number is null then
      dbms_output.put_line('hr_organization_api.update_organization API Error: '||sqlerrm);
      rollback;
   elsif l_duplicate_org_warning then
      dbms_output.put_line('Warning: Duplicate Organization');
      rollback;
   else
      commit;
   end if;  
--
exception
  when others then
     dbms_output.put_line('hr_organization_api.create_organization API failed with error :'||sqlerrm);
     rollback;
end;

-----------------------------------
-----------------------------------
--Update Organization
-----------------------------------

declare
l_validate_mode          BOOLEAN := TRUE;
l_begin_date             DATE    := SYSDATE;
l_end_date               DATE    := hr_general.end_of_time;
l_organization_id        hr_all_organization_units.organization_id%TYPE := 104;
l_orgname                hr_all_organization_units.name%TYPE := 'Test Org';
l_object_version_number  hr_all_organization_units.object_version_number%TYPE;
l_duplicate_org_warning  BOOLEAN;
begin
   --
   hr_organization_api.update_organization
      (
        p_validate                  => l_validate_mode
       ,p_effective_date         => l_begin_date --Reference date for validating lookup values
       ,p_date_from               => l_begin_date --Date the organization takes effect
       ,p_date_to                   => l_end_date
       ,p_organization_id        => l_organization_id
       ,p_name                      => l_orgname
       ,p_attribute_category    => null
       ,p_attribute1                => null
       ,p_attribute2                => 'Test Org'
       --Out Variables
       ,p_object_version_number    => l_object_version_number
       ,p_duplicate_org_warning     => l_duplicate_org_warning
      );

   if l_object_version_number is null then
      dbms_output.put_line('hr_organization_api.update_organization API Error: '||sqlerrm);
      rollback;
   elsif l_duplicate_org_warning then
      dbms_output.put_line('Warning: Duplicate Organization');
      rollback;
   else
      commit;
   end if;
--
exception
  when others then
     dbms_output.put_line('hr_organization_api.update_organization API failed with error :'||sqlerrm);
     rollback;
end;

API to Delete Absence Attendances

API to Delete Absence Attendances

DECLARE
   CURSOR get_absence
   IS
      SELECT absence_attendance_id, object_version_number
        FROM per_absence_attendances
       WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
BEGIN
   FOR fetch_absence IN get_absence
   LOOP
      BEGIN
         hr_person_absence_api.
          delete_person_absence (
            p_validate                => FALSE,
            p_absence_attendance_id   => fetch_absence.absence_attendance_id,
            p_object_version_number   => fetch_absence.object_version_number);
         COMMIT;
         DBMS_OUTPUT.
          put_line (
            fetch_absence.absence_attendance_id
            || ' has been deleted Successfully !!!');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.
             put_line (
                  'Inner Exception: '
               || fetch_absence.absence_attendance_id
               || ' - - '
               || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

Cost Allocation Create/Update - HRMS APIs

Cost Allocation Create/Update - HRMS APIs

Cost Allocation Create/Update - HRMS APIs
--
declare
  --
  l_action            varchar2(10) := 'CREATE';--'UPDATE'
  --
  --- DECLARE variables for PAY_COST_ALLOCATION_API
  --- IN variables
  l_assignment_id     number := 123456;
  l_business_group_id number := 101;
  l_proportion  pay_cost_allocations_f.proportion%type := 1;
  l_cost_code   varchar2(20) := '14';
  l_company     varchar2(20) := 'AK';
  l_business    varchar2(20) := 'GRT';
  l_budget_code varchar2(20) := '99999';
  l_account     varchar2(20) := '0000';
  --- OUT variables
  l_combination_name          varchar2(100);
  l_cost_allocation_id        number;
  l_cost_effective_start_date date;
  l_cost_effective_end_date   date;
  l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%type;
  l_cost_obj_version_number   number;
  --
begin
   /*
    * This API creates/updates cost allocations.
    * Requires a valid assignment.
  */
      
   if l_action = 'CREATE' then
       --
       l_cost_allocation_id := null;
       --
       pay_cost_allocation_api.create_cost_allocation (p_validate => false
                        ,p_effective_date       => trunc(sysdate)    
                        ,p_assignment_id        => l_assignment_id  
                        ,p_proportion           => l_proportion
                        ,p_business_group_id    => l_business_group_id
                        ,p_segment1             => l_company
                        ,p_segment2             => l_business
                        ,p_segment3             => l_cost_code   
                        ,p_segment4             => l_budget_code
                        ,p_segment5             => l_account
                        -- Out    
                        ,p_combination_name     => l_combination_name
                        ,p_cost_allocation_id   => l_cost_allocation_id 
                        ,p_effective_start_date => l_cost_effective_start_date  
                        ,p_effective_end_date   => l_cost_effective_end_date              
                        ,p_object_version_number => l_cost_obj_version_number 
                        -- In / Out
                        ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id  
                        );
         
       if l_cost_obj_version_number is null then
         dbms_output.put_line('Cost Allocation creation failed '||sqlerrm);
         rollback;
       else
         dbms_output.put_line('Cost Allocation created');
         commit;
       end if;
                                  
   else
        --  
        l_cost_obj_version_number := 3;
        l_cost_allocation_id      := 345612;
        --                      
        pay_cost_allocation_api.update_cost_allocation
                                 ( p_validate                    =>  false
                                 , p_effective_date              =>  trunc(sysdate)
                                 , p_datetrack_update_mode       =>  'UPDATE'
                                 , p_cost_allocation_id          =>  l_cost_allocation_id
                                 , p_proportion                  =>  l_proportion
                                 , p_segment1                    =>  l_company
                                 , p_segment2                    =>  l_business
                                 , p_segment3                    =>  l_cost_code
                                 , p_segment4                    =>  l_budget_code
                                 , p_segment5                    =>  l_account
                                 -- In / Out
                                 , p_cost_allocation_keyflex_id  =>  l_cost_allocation_keyflex_id
                                 , p_object_version_number       =>  l_cost_obj_version_number                               
                                 -- Out
                                 , p_effective_start_date        =>  l_cost_effective_start_date
                                 , p_effective_end_date          =>  l_cost_effective_end_date
                                 , p_combination_name            =>  l_combination_name
                                 );
        
        if l_cost_obj_version_number > 3  then
           dbms_output.put_line('Cost Allocation updated');
           commit;
        else
           dbms_output.put_line('Cost Allocation updation failed '||sqlerrm);
           rollback;
        end if;        
   end if;                                  
--
exception
  when others then
     dbms_output.put_line('pay_cost_allocation_api API failed with error :'||sqlerrm);
     rollback;
end;
--

How To Restrict Absence Type List of Values in Oracle Self-Service Human Resources

 How To Restrict Absence Type List of Values in Oracle Self-Service Human Resources

Introduction

Oracle Self-Service HR enables users to apply for absences. When applying for an absence, users enter thenabsence date or time, absence type, absence status, and absence reason. The absence types (for example,Vacation, Sick Leave etc.) are defined in Oracle HRMS application. Currently, all the absence types defined in Oracle HRMS application are displayed to the self-service user. The user may not be eligible for all the absence types displayed in the self-service application. This leads to users applying for ineligible absence types. Approvers are forced to verify if the absence type is valid for the employee.

With Release 12.1.3, Oracle Self-Service HR introduces a custom package that enables customers to write the logic for deriving the absence type values based on their business requirements. The absence type list of values can be based on parameters such as: Gender, Business Group, Organization, Responsibility, Grade, and Payroll. Customers can use user hooks within the custom package to derive a restricted list of values for absence type in Absence Management function in Oracle Self-Service Human Resources.

Setup for Restricting Absence Type LOV 

Use the following the sample steps to derive a restricted list of values for absence type LOV in Absence
Management. The custom logic has to be written in the New Package function
HR_ABSENCE_RESTRICTED.ABSENCES_RESTRICTED. Login person id and selected person id
will be passed by default – using these two parameters the rest of the data or parameters can be queried
from the tables.

Package Name	HR_ABSENCE_RESTRICTED
Procedure Name	ABSENCES_RESTRICTED
New Parameters	1) selected_person_id 2) login_person_id

The above procedure will be called from the AbsenceTypeVOImpl java file with the
proper input parameters and the return value will be appended to the AbsenceTypeVO

Sample Code

1. For the purpose of this document, the following example illustrates restricting absence type based
on gender.

1. How to restrict Maternity Absence type to employees of gender ‘Female’ only
--------------------Based on Gender------------
 if to_number(selected_person_id) is not null then
 select sex into l_sex from per_all_people_f where person_id= to_number(selected_person_id)
 and sysdate between effective_start_date and effective_end_date;
 if l_sex <>'F' THEN
 return '36050';
 END IF;
Note: In this example, ‘36050’ is the id of the absence type, which has to restricted

2. How to restrict Absence type based on organization
--------------------Based on Organization------------
if to_number(selected_person_id) is not null then

 select organization_id into l_orgid
 from per_all_assignments_f
 where person_id= to_number(selected_person_id)
 and sysdate between effective_start_date and effective_end_date;
 if l_orgid =7545 then
 return '36046';
 end if;

end if;
Note: In this example, ‘36046’ is the id of the absence type which has to restricted

3. How to return more than one Absence Type
/* Example code logic
 if to_number(selected_person_id) = 36003 then
 return '31044,31045';
 end if;
*/

API to Purge/Delete Employee from HRMS application

API to Purge/Delete Employee from HRMS application

You can use API HR_PERSON_API.DELETE_PERSON to delete an employee(person) and the associated person related records.

Example:

DECLARE
  -- Input Variables
  l_validate BOOLEAN    := FALSE;
  l_effective_date DATE := sysdate;
  l_person_id                 NUMBER    := 123048;
  l_perform_predel_validation BOOLEAN   := FALSE;
  -- Output Variables
  l_person_org_manager_warning VARCHAR2(2000);
BEGIN
  --  Calling API HR_PERSON_API.DELETE_PERSON
  hr_person_api.delete_person(p_validate                   => l_validate ,
                              p_effective_date             => l_effective_date ,
                              p_person_id                  => l_person_id ,
                              p_perform_predel_validation  => l_perform_predel_validation ,
                              p_person_org_manager_warning => l_person_org_manager_warning );
  --
  dbms_output.put_line('Employee deleted successfully');
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error : ' || sqlerrm);
END;
/
Some more information on the API parameters
p_perform_predel_validation:
When this parameter is set to TRUE, the API performs a check to see if any data in addition to that set up by default, e.g. data in per_all_people_f, per_all_assignments_f, per_periods_of_service exists for the person. If no additional data exists, then the API cascade deletes the person data. If any additional data exists for this person in any non-HRMS tables, then this person will not be deleted irrespective of whether only default data exists. When this parameter is set to FALSE, the API cascade deletes all data held in HRMS tables for this person, provided no additional data exists in any non-HRMS tables.

p_person_org_manager_warning:
If the person being deleted is an organization manager, then a warning message will be returned otherwise no value will be returned.

Prerequisites:
Employee and fnd_user link must be removed by removing the person in users form
No Payroll should be processed on employee till-date
Note:
If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from ‘Employee’ to ‘Applicant’ and then use the above API again to purge the record.

Costing in Oracle Payroll: Concept

This is one of those topics which has often eluded us from understanding it in a better way than any other part of Oracle Payroll. But, this is one of the most important and vital feature of Oracle Payroll and HRMS on the whole.

It’s through the Costing that Human Resources interact with the Oracle Financials (GL) out of the box.

Let take an insight into this concept of Costing.

Costing as a concept is to account the cost incurred on an employee in terms of compensation provided to him/her by the organization. Oracle achieves this through three processes;


Costing
Costing of Payment
Transfer to Ledger(GL/SLA)
 We'll see each of these briefly;

Costing
Generates accounting entries for elements processed in Run/Quick Pay
Debits the Costing account and Credits the Clearing/Control account.
Accounting Entries are determined by the values of Cost Allocation KFF at different levels.
Whether to transfer to GL or not is determined by the “Transfer to GL” checkbox at Element Link level.
 Costing of Payments
Generates accounting entries based on the Prepayments. If no prepayments, no Costing of Payment for the employee.
Debits the Clearing/Control Account and Credits the Cash Clearing Account.
Accounting Entries are determined by the values provided at the Payment Method level.
Whether to transfer to GL or not is determined by the “Transfer to GL” checkbox at Payment Method level.
Transfer to Ledger (GL/SLA)
Based on whether the “Transfer to GL” checkbox at Element Link and Payment Method Level are checked.
Will transfer all the entries generated by the Costing Process to the GL_INTERFACE table.
Will transfer only those entries generated by Payment of Costing for whom there is a payment process (Cash, Cheque, Direct Deposit, etc) to the GL_INTERFACE table.