Sunday, September 8, 2019

API to update user Mail preference (FND_PREFERENCE.put) (Table: FND_USER_PREFERENCS)













In this post, we have given you an API to update User preferences. This helps you update any preferences of User like country, Language, Display name, Mail preference etc.



Script:

BEGIN
  FND_PREFERENCE.put('SHAREORCLEAPPS', 'WF', 'MAILTYPE', 'DISABLED');
END;

Table which stores user preferences information


Name
Req
Comments
USER_NAME
Yes
Internal name of user who owns this preference setting
MODULE_NAME
Yes
Module that references this preference
PREFERENCE_NAME
Yes
Preference name
PREFERENCE_VALUE

Preference value



Hope it helps!!

Sunday, September 8, 2019 by Team search · 0

Saturday, August 31, 2019

API to update Employee Details - HR_PERSON_API.UPDATE_PERSON












                   In this post, we have shared a sample script to update Employee details.

API: HR_PERSON_API.UPDATE_PERSON

Script: 


  DECLARE
    lb_validate                    BOOLEAN DEFAULT FALSE;
    ld_effective_date              DATE;
    lv_datetrack_update_mode       VARCHAR2(100) DEFAULT hr_api.g_update;
    ln_person_id                   NUMBER;
    ln_object_version_number       NUMBER;
    ln_person_type_id              NUMBER;
    lv_last_name                   VARCHAR2(100);
    lv_applicant_number            VARCHAR2(100);
    lv_comments                    VARCHAR2(100);
    ld_date_employee_data_verified DATE;
    ld_date_of_birth               DATE;
    lv_email_address               VARCHAR2(100);
    lv_employee_number             VARCHAR2(100);
    lv_expense_check_sendto_addres VARCHAR2(100);
    lv_first_name                  VARCHAR2(100);
    lv_known_as                    VARCHAR2(100);
    lv_marital_status              VARCHAR2(100);
    lv_middle_names                VARCHAR2(100);
    lv_nationality                 VARCHAR2(100);
    lv_national_identifier         VARCHAR2(100);
    lv_previous_last_name          VARCHAR2(100);
    lv_registered_disabled_flag    VARCHAR2(100);
    lv_sex                         VARCHAR2(100);
    lv_title                       VARCHAR2(100);
    ln_vendor_id                   NUMBER;
    lv_work_telephone              VARCHAR2(100);
    lv_attribute_category          VARCHAR2(100);
    lv_attribute1                  VARCHAR2(100);
    lv_attribute2                  VARCHAR2(100);
    lv_attribute3                  VARCHAR2(100);
    lv_attribute4                  VARCHAR2(100);
    lv_attribute5                  VARCHAR2(100);
    lv_attribute6                  VARCHAR2(100);
    lv_attribute7                  VARCHAR2(100);
    lv_attribute8                  VARCHAR2(100);
    lv_attribute9                  VARCHAR2(100);
    lv_attribute10                 VARCHAR2(100);
    lv_attribute11                 VARCHAR2(100);
    lv_attribute12                 VARCHAR2(100);
    lv_attribute13                 VARCHAR2(100);
    lv_attribute14                 VARCHAR2(100);
    lv_attribute15                 VARCHAR2(100);
    lv_attribute16                 VARCHAR2(100);
    lv_attribute17                 VARCHAR2(100);
    lv_attribute18                 VARCHAR2(100);
    lv_attribute19                 VARCHAR2(100);
    lv_attribute20                 VARCHAR2(100);
    lv_attribute21                 VARCHAR2(100);
    lv_attribute22                 VARCHAR2(100);
    lv_attribute23                 VARCHAR2(100);
    lv_attribute24                 VARCHAR2(100);
    lv_attribute25                 VARCHAR2(100);
    lv_attribute26                 VARCHAR2(100);
    lv_attribute27                 VARCHAR2(100);
    lv_attribute28                 VARCHAR2(100);
    lv_attribute29                 VARCHAR2(100);
    lv_attribute30                 VARCHAR2(100);
    lv_per_information_category    VARCHAR2(100);
    lv_per_information1            VARCHAR2(100);
    lv_per_information2            VARCHAR2(100);
    lv_per_information3            VARCHAR2(100);
    lv_per_information4            VARCHAR2(100);
    lv_per_information5            VARCHAR2(100);
    lv_per_information6            VARCHAR2(100);
    lv_per_information7            VARCHAR2(100);
    lv_per_information8            VARCHAR2(100);
    lv_per_information9            VARCHAR2(100);
    lv_per_information10           VARCHAR2(100);
    lv_per_information11           VARCHAR2(100);
    lv_per_information12           VARCHAR2(100);
    lv_per_information13           VARCHAR2(100);
    lv_per_information14           VARCHAR2(100);
    lv_per_information15           VARCHAR2(100);
    lv_per_information16           VARCHAR2(100);
    lv_per_information17           VARCHAR2(100);
    lv_per_information18           VARCHAR2(100);
    lv_per_information19           VARCHAR2(100);
    lv_per_information20           VARCHAR2(100);
    lv_per_information21           VARCHAR2(100);
    lv_per_information22           VARCHAR2(100);
    lv_per_information23           VARCHAR2(100);
    lv_per_information24           VARCHAR2(100);
    lv_per_information25           VARCHAR2(100);
    lv_per_information26           VARCHAR2(100);
    lv_per_information27           VARCHAR2(100);
    lv_per_information28           VARCHAR2(100);
    lv_per_information29           VARCHAR2(100);
    lv_per_information30           VARCHAR2(100);
    ld_date_of_death               DATE;
    lv_background_check_status     VARCHAR2(100);
    ld_background_date_check       DATE;
    lv_blood_type                  VARCHAR2(100);
    lv_correspondence_language     VARCHAR2(100);
    lv_fast_path_employee          VARCHAR2(100);
    ln_fte_capacity                NUMBER;
    ld_hold_applicant_date_until   DATE;
    lv_honors                      VARCHAR2(100);
    lv_internal_location           VARCHAR2(100);
    lv_last_medical_test_by        VARCHAR2(100);
    ld_last_medical_test_date      DATE;
    lv_mailstop                    VARCHAR2(100);
    lv_office_number               VARCHAR2(100);
    lv_on_military_service         VARCHAR2(100);
    lv_pre_name_adjunct            VARCHAR2(100);
    ld_projected_start_date        DATE;
    lv_rehire_authorizor           VARCHAR2(100);
    lv_rehire_recommendation       VARCHAR2(100);
    lv_resume_exists               VARCHAR2(100);
    ld_resume_last_updated         DATE;
    lv_second_passport_exists      VARCHAR2(100);
    lv_student_status              VARCHAR2(100);
    lv_work_schedule               VARCHAR2(100);
    lv_rehire_reason               VARCHAR2(100);
    lv_suffix                      VARCHAR2(100);
    ln_benefit_group_id            NUMBER;
    ld_receipt_of_death_cert_date  DATE;
    lv_coord_ben_med_pln_no        VARCHAR2(100);
    lv_coord_ben_no_cvg_flag       VARCHAR2(100);
    lv_coord_ben_med_ext_er        VARCHAR2(100);
    lv_coord_ben_med_pl_name       VARCHAR2(100);
    lv_coord_ben_med_insr_crr_name VARCHAR2(100);
    lv_coord_benmed_insr_crr_ident VARCHAR2(100);
    ld_coord_ben_med_cvg_strt_dt   DATE;
    ld_coord_ben_med_cvg_end_dt    DATE;
    lv_uses_tobacco_flag           VARCHAR2(100);
    ld_dpdnt_adoption_date         DATE;
    lv_dpdnt_vlntry_svce_flag      VARCHAR2(100);
    ld_original_date_of_hire       DATE;
    ld_adjusted_svc_date           DATE;
    lv_town_of_birth               VARCHAR2(100);
    lv_region_of_birth             VARCHAR2(100);
    lv_country_of_birth            VARCHAR2(100);
    lv_global_person_id            VARCHAR2(100);
    ln_party_id                    NUMBER;
    lv_npw_number                  VARCHAR2(100);
    ld_effective_start_date        DATE;
    ld_effective_end_date          DATE;
    lv_full_name                   VARCHAR2(150);   
    ln_comment_id                  NUMBER;
    lb_name_combination_warning    BOOLEAN;
    lb_assign_payroll_warning      BOOLEAN;
    lb_orig_hire_warning           BOOLEAN;
    lv_unhand_err_msg              VARCHAR2(2000);
        position_                     
 
 
  BEGIN
 
      lv_employee_number := 'TESTEMPLOYEE';

      SELECT MAX(object_version_number),
                 MIN(person_id)
        INTO ln_object_version_number,
                    ln_person_id
        FROM per_all_people_f
       WHERE employee_number = lv_employee_number
         AND (TRUNC(SYSDATE) BETWEEN effective_start_date AND nvl(effective_end_date, TRUNC(SYSDATE))
              OR effective_start_date > SYSDATE
             ); 
     
      position_ := 940;
      lv_datetrack_update_mode := hr_api.g_correction; 
      ld_effective_date        := TRUNC(SYSDATE);   
      lv_last_name             := 'TEST2';
      lv_email_address         := 'TEST2@XYZ.COM';
      lv_sex                   := 'M';
       
      position_ := 945;
      HR_PERSON_API.UPDATE_PERSON
                (p_validate                     => lb_validate,
             p_effective_date               => ld_effective_date,
             p_datetrack_update_mode        => lv_datetrack_update_mode,
             p_person_id                    => ln_person_id,
             p_object_version_number        => ln_object_version_number,
             p_person_type_id               => ln_person_type_id,
             p_last_name                    => lv_last_name,
             p_applicant_number             => lv_applicant_number,
             p_comments                     => lv_comments,
             p_date_employee_data_verified  => ld_date_employee_data_verified,
             p_date_of_birth                => ld_date_of_birth,
             p_email_address                => lv_email_address,
             p_employee_number              => lv_employee_number,
             p_expense_check_send_to_addres => lv_expense_check_sendto_addres,
             p_first_name                   => lv_first_name,
             p_known_as                     => lv_known_as,
             p_marital_status               => lv_marital_status,
             p_middle_names                 => lv_middle_names,
             p_nationality                  => lv_nationality,
             p_national_identifier          => lv_national_identifier,
             p_previous_last_name           => lv_previous_last_name,
             p_registered_disabled_flag     => lv_registered_disabled_flag,
             p_sex                          => lv_sex,
             p_title                        => lv_title,
             p_vendor_id                    => ln_vendor_id,
             p_work_telephone               => lv_work_telephone,
             p_attribute_category           => lv_attribute_category,
             p_attribute1                   => lv_attribute1,
             p_attribute2                   => lv_attribute2,
             p_attribute3                   => lv_attribute3,
             p_attribute4                   => lv_attribute4,
             p_attribute5                   => lv_attribute5,
             p_attribute6                   => lv_attribute6,
             p_attribute7                   => lv_attribute7,
             p_attribute8                   => lv_attribute8,
             p_attribute9                   => lv_attribute9,
             p_attribute10                  => lv_attribute10,
             p_attribute11                  => lv_attribute11,
             p_attribute12                  => lv_attribute12,
             p_attribute13                  => lv_attribute13,
             p_attribute14                  => lv_attribute14,
             p_attribute15                  => lv_attribute15,
             p_attribute16                  => lv_attribute16,
             p_attribute17                  => lv_attribute17,
             p_attribute18                  => lv_attribute18,
             p_attribute19                  => lv_attribute19,
             p_attribute20                  => lv_attribute20,
             p_attribute21                  => lv_attribute21,
             p_attribute22                  => lv_attribute22,
             p_attribute23                  => lv_attribute23,
             p_attribute24                  => lv_attribute24,
             p_attribute25                  => lv_attribute25,
             p_attribute26                  => lv_attribute26,
             p_attribute27                  => lv_attribute27,
             p_attribute28                  => lv_attribute28,
             p_attribute29                  => lv_attribute29,
             p_attribute30                  => lv_attribute30,
             p_per_information_category     => lv_per_information_category,
             p_per_information1             => lv_per_information1,
             p_per_information2             => lv_per_information2,
             p_per_information3             => lv_per_information3,
             p_per_information4             => lv_per_information4,
             p_per_information5             => lv_per_information5,
             p_per_information6             => lv_per_information6,
             p_per_information7             => lv_per_information7,
             p_per_information8             => lv_per_information8,
             p_per_information9             => lv_per_information9,
             p_per_information10            => lv_per_information10,
             p_per_information11            => lv_per_information11,
             p_per_information12            => lv_per_information12,
             p_per_information13            => lv_per_information13,
             p_per_information14            => lv_per_information14,
             p_per_information15            => lv_per_information15,
             p_per_information16            => lv_per_information16,
             p_per_information17            => lv_per_information17,
             p_per_information18            => lv_per_information18,
             p_per_information19            => lv_per_information19,
             p_per_information20            => lv_per_information20,
             p_per_information21            => lv_per_information21,
             p_per_information22            => lv_per_information22,
             p_per_information23            => lv_per_information23,
             p_per_information24            => lv_per_information24,
             p_per_information25            => lv_per_information25,
             p_per_information26            => lv_per_information26,
             p_per_information27            => lv_per_information27,
             p_per_information28            => lv_per_information28,
             p_per_information29            => lv_per_information29,
             p_per_information30            => lv_per_information30,
             p_date_of_death                => ld_date_of_death,
             p_background_check_status      => lv_background_check_status,
             p_background_date_check        => ld_background_date_check,
             p_blood_type                   => lv_blood_type,
             p_correspondence_language      => lv_correspondence_language,
             p_fast_path_employee           => lv_fast_path_employee,
             p_fte_capacity                 => ln_fte_capacity,
             p_hold_applicant_date_until    => ld_hold_applicant_date_until,
             p_honors                       => lv_honors,
             p_internal_location            => lv_internal_location,
             p_last_medical_test_by         => lv_last_medical_test_by,
             p_last_medical_test_date       => ld_last_medical_test_date,
             p_mailstop                     => lv_mailstop,
             p_office_number                => lv_office_number,
             p_on_military_service          => lv_on_military_service,
             p_pre_name_adjunct             => lv_pre_name_adjunct,
             p_projected_start_date         => ld_projected_start_date,
             p_rehire_authorizor            => lv_rehire_authorizor,
             p_rehire_recommendation        => lv_rehire_recommendation,
             p_resume_exists                => lv_resume_exists,
             p_resume_last_updated          => ld_resume_last_updated,
             p_second_passport_exists       => lv_second_passport_exists,
             p_student_status               => lv_student_status,
             p_work_schedule                => lv_work_schedule,
             p_rehire_reason                => lv_rehire_reason,
             p_suffix                       => lv_suffix,
             p_benefit_group_id             => ln_benefit_group_id,
             p_receipt_of_death_cert_date   => ld_receipt_of_death_cert_date,
             p_coord_ben_med_pln_no         => lv_coord_ben_med_pln_no,
             p_coord_ben_no_cvg_flag        => lv_coord_ben_no_cvg_flag,
             p_coord_ben_med_ext_er         => lv_coord_ben_med_ext_er,
             p_coord_ben_med_pl_name        => lv_coord_ben_med_pl_name,
             p_coord_ben_med_insr_crr_name  => lv_coord_ben_med_insr_crr_name,
             p_coord_ben_med_insr_crr_ident => lv_coord_benmed_insr_crr_ident,
             p_coord_ben_med_cvg_strt_dt    => ld_coord_ben_med_cvg_strt_dt,
             p_coord_ben_med_cvg_end_dt     => ld_coord_ben_med_cvg_end_dt,
             p_uses_tobacco_flag            => lv_uses_tobacco_flag,
             p_dpdnt_adoption_date          => ld_dpdnt_adoption_date,
             p_dpdnt_vlntry_svce_flag       => lv_dpdnt_vlntry_svce_flag,
             p_original_date_of_hire        => ld_original_date_of_hire,
             p_adjusted_svc_date            => ld_adjusted_svc_date,
             p_town_of_birth                => lv_town_of_birth,
             p_region_of_birth              => lv_region_of_birth,
             p_country_of_birth             => lv_country_of_birth,
             p_global_person_id             => lv_global_person_id,
             p_party_id                     => ln_party_id,
             p_npw_number                   => lv_npw_number,
             -- Out parameters
             p_effective_start_date     => ld_effective_start_date,
             p_effective_end_date       => ld_effective_end_date,
             p_full_name                => lv_full_name,
             p_comment_id               => ln_comment_id,
             p_name_combination_warning => lb_name_combination_warning,
             p_assign_payroll_warning   => lb_assign_payroll_warning,
             p_orig_hire_warning        => lb_orig_hire_warning);
         
     
      COMMIT;
      position_ := 960;
     
    EXCEPTION
      WHEN OTHERS THEN
        lv_unhand_err_msg := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('Error in API : HR_PERSON_API.UPDATE_PERSON: intf_file_id : ' SQLERRM:  ' || SQLERRM);
  END;



Before Update:




Execution




After Update:




Hope it helps!!



Saturday, August 31, 2019 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.