Thursday, March 20, 2014

How to set FND Profile Option Value programmatically

 
Sample script to set FND Profile value programmatically at the User Level:-
 
Note:- The profile value can as well be set at SITE/APPLICATION/RESPONSIBILITY level by changing the "X_LEVEL_NAME" parameter accordingly.
-------------------------------------------------------------------------------
SET SERVEROUTPUT ON
 
DECLARE
   x_val       BOOLEAN;
   x_profile   fnd_profile_options_tl.PROFILE_OPTION_NAME%TYPE;
BEGIN
   SELECT PROFILE_OPTION_NAME
     INTO x_profile
     FROM fnd_profile_options_tl
    WHERE user_profile_option_name = 'ABM: ABM Help';           -- Change here
 
  FOR rec_i IN (SELECT user_id FROM fnd_user WHERE user_name IN ( 'EBSPRO' -- Build List Here ))
 
     LOOP
      x_val :=  FND_PROFILE.SAVE
                  ( X_NAME        => x_profile
                  , X_VALUE       => 'Test123'    -- Change here
                  , X_LEVEL_NAME  => 'USER'
                  , X_LEVEL_VALUE => rec_i.user_id
                  );
      IF x_val THEN
        dbms_output.put_line('TRUE');
      ELSE
        dbms_output.put_line('FALSE');
      END IF;
    END LOOP;
END;
/
COMMIT;
/
---------------------------------------------------------------------------------

How to Update HR Assignment programmatically using HR_ASSIGNMENT_API

-- Following is the sample code to programmatically create Flex value for a particular Value set
--API Name: fnd_flex_val_api.create_independent_vset_value
-- Parameters:
--       P_DATE -- Effective Date
--       P_ASSIGNMENT_ID -- Assignment_Id
--       P_JOB_ID -- Job_ID
--       P_POSITION_ID -- Position_Id
 
DECLARE
   l_date                           DATE := TO_DATE ('&P_DATE', 'DD-MON-YYYY');
   l_true                           BOOLEAN := FALSE;
   l_assign_id                      NUMBER := &P_ASSIGNMENT_ID;
   l_obj                            NUMBER;
   l_datetrack_update_mode          VARCHAR2 (30);
   l_organization_id                NUMBER;
   l_special_ceiling_step_id        NUMBER;
   l_effective_start_date           DATE;
   l_effective_end_date             DATE;
   l_people_group_id                NUMBER;
   l_group_name                     VARCHAR2 (30);
   l_org_now_no_manager_warning     BOOLEAN;
   l_other_manager_warning          BOOLEAN;
   l_spp_delete_warning             BOOLEAN;
   l_entries_changed_warning        VARCHAR2 (30);
   l_tax_district_changed_warning   BOOLEAN;
   l_job_id                         NUMBER := &P_JOB_ID;
   l_position_id                    NUMBER := &P_POSITION_ID;
 
   CURSOR csr_ovn
   IS
      SELECT object_version_number
        FROM per_all_assignments_f
       WHERE     assignment_id = l_assign_id
             AND l_date BETWEEN effective_start_date AND effective_end_date;
 
BEGIN
   OPEN csr_ovn;
 
   FETCH csr_ovn INTO l_obj;
 
   CLOSE csr_ovn;
 
   hr_assignment_api.update_emp_asg_criteria (
      p_validate                       => l_true,
      p_effective_date                 => l_date,
      p_datetrack_update_mode          => 'UPDATE',
      p_assignment_id                  => l_assign_id,
      p_object_version_number          => l_obj,
      p_job_id                         => l_job_id,
      p_position_id                    => l_position_id,
      p_ass_attribute1                 => l_emp_job_imp_rec.mgr_level,
      p_ass_attribute2                 => l_emp_job_imp_rec.per_sal_type,
      p_special_ceiling_step_id        => l_special_ceiling_step_id,
      P_effective_start_date           => l_effective_start_date,
      p_effective_end_date             => l_effective_end_date,
      p_people_group_id                => l_people_group_id,
      p_group_name                     => l_group_name,
      p_org_now_no_manager_warning     => l_org_now_no_manager_warning,
      p_other_manager_warning          => l_other_manager_warning,
      p_spp_delete_warning             => l_spp_delete_warning,
      p_entries_changed_warning        => l_entries_changed_warning,
      p_tax_district_changed_warning   => l_tax_district_changed_warning);
 
   DBMS_OUTPUT.put_line ('Successfully updated job id to assignment ');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception=>' || SQLERRM);
END;