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.