Tuesday, 31 January 2017

Update SIT API Script

Update SIT API Script



DECLARE
   p_person_id                   NUMBER          := NULL;
   p_business_group_id           NUMBER;
   p_id_flex_num                 NUMBER;
------------------
   p_analysis_criteria_id        NUMBER          := NULL;
   p_person_analysis_id          NUMBER          := NULL;
   p_per_object_version_number   NUMBER          := NULL;
   v_err                         VARCHAR2 (1000) := NULL;s
   driving_license_number                  VARCHAR2 (100)  := NULL;
 
   CURSOR c1
      IS
         SELECT
           FROM xx_hrms_sit_upload
          WHERE status is not null;
 
BEGIN
   FOR i IN c1
   LOOP
      p_id_flex_num := 50530;
      BEGIN
         SELECT   NVL (sit.object_version_number, 1),
                  sit.analysis_criteria_id, MAX (sit.person_analysis_id)
             INTO p_per_object_version_number,
                  p_analysis_criteria_id, p_person_analysis_id
             FROM fnd_id_flex_structures_tl sttl,
                  fnd_id_flex_structures st,
                  per_person_analyses sit,
                  per_analysis_criteria sv
            WHERE sttl.id_flex_structure_name = 'Driving License'
              AND sttl.LANGUAGE = USERENV ('LANG')
              AND st.id_flex_code = sttl.id_flex_code
              AND st.id_flex_num = sttl.id_flex_num
              AND st.id_flex_num = sit.id_flex_num
              AND st.id_flex_num = sv.id_flex_num
              AND sit.analysis_criteria_id = sv.analysis_criteria_id
              AND sit.person_id = i.person_id
         --and sv.SEGMENT1 = '4602001140'
         GROUP BY sv.segment1,
                  NVL (sit.object_version_number, 1),
                  sit.analysis_criteria_id;
      END;
      BEGIN
           
            hr_sit_api.update_sit
                 (p_validate                       => FALSE,
                  p_person_analysis_id             => p_person_analysis_id,
                  p_pea_object_version_number      => p_per_object_version_number,
                  p_date_from                      => TRUNC (SYSDATE),
                  p_segment1                       => i.driving_license_number,
                  p_analysis_criteria_id           => p_analysis_criteria_id
                 );
       
         UPDATE xx_hrms_sit_upload xx
            SET xx.status = 'S'
          WHERE xx.sno=i.sno;
      EXCEPTION
         WHEN OTHERS
         THEN
           
            v_err := SQLERRM;
            UPDATE xx_hrms_sit_upload xx
               SET xx.status = 'E',xx.error_msg = v_err
             WHERE sno=i.sno;
      END;
   END LOOP;
   COMMIT;
END;

Monday, 19 September 2016

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


Thursday, 30 June 2016

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;

Monday, 6 June 2016

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

Tuesday, 3 May 2016

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;