Oracle Apps R12 HRMS Update API Position Status = 'Invalid'

---single position
declare
l_effective_date           date := sysdate ;
l_position_id              per_positions.position_id%type := 551441;
l_status                   varchar2(40) := 'INVALID';
l_eff_start_date           date;
l_eff_end_date             date;
l_validate_mode            boolean := false;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
l_pos_title_code           fnd_lookup_values.lookup_code%type;
l_datetrack_mode VARCHAR2(20);
l_valid_grades_changed_warning BOOLEAN;
--
begin
   ---   
   l_datetrack_mode         := 'UPDATE';
   l_position_definition_id := NULL;
   l_eff_start_date         := NULL;
   l_eff_end_date           := NULL;
   l_pos_name               := NULL;
   --l_effective_date         := trunc(sysdate);
   --l_pos_number             := '80304589.';
   --l_pos_title_code         := 'MECH_TECH';
   --l_object_version_number  := 3;

SELECT OBJECT_VERSION_NUMBER INTO l_object_version_number
FROM HR_POSITIONS_F
WHERE POSITION_ID = L_POSITION_ID
AND l_effective_date  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;

   --
   hr_position_api.update_position
                 ( p_validate                       => l_validate_mode
                  ,p_position_id                  => l_position_id
                  ,p_effective_start_date     => l_eff_start_date
                  ,p_effective_end_date      => l_eff_end_date
                  ,p_position_definition_id   => l_position_definition_id
                  ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
                  ,p_status                          => l_status       
                  ,p_name                          => l_pos_name
                  --,p_segment1                    => l_segment1
                  --,p_segment2                    => l_pos_title_code                     
                  ,p_object_version_number              => l_object_version_number
                  ,p_effective_date             => l_effective_date
                  ,p_datetrack_mode         => l_datetrack_mode
                 );
                      
     dbms_output.put_line('Update Successful : ');
     --Important: regenerate_position_name needs to be called when there is a name update
     --                     
     apps.hr_position_api.regenerate_position_name(l_position_id);
     --                 
     dbms_output.put_line('Regenerate Successful : ');
                    
   if l_object_version_number is null then
      dbms_output.put_line('hr_position_api.update_position API Error: '||sqlerrm);
      rollback;
   elsif l_valid_grades_changed_warning then
      dbms_output.put_line('Warning: Grade changed');
      rollback;   
   else
      commit;
   end if; 
--
exception
  when others then
     dbms_output.put_line('hr_position_api.update_position API failed with error :'||sqlerrm);
     rollback;
end;          


--with cursor

declare
l_effective_date           date := sysdate ;
--l_position_id              per_positions.position_id%type := 3649;
l_status                   varchar2(40) := 'INVALID';
l_eff_start_date           date;
l_eff_end_date             date;
l_validate_mode            boolean := false;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
l_pos_title_code           fnd_lookup_values.lookup_code%type;
l_datetrack_mode VARCHAR2(20);
l_valid_grades_changed_warning BOOLEAN;

CURSOR C1 IS

select POSITION_ID , OBJECT_VERSION_NUMBER , NAME, STATUS
from HR_POSITIONS_F
where ORGANIZATION_ID in (101112,144121)
;
--
begin
   ---
 
   FOR I IN C1 LOOP 
   l_datetrack_mode         := 'UPDATE';
   l_position_definition_id := NULL;
   l_eff_start_date         := NULL;
   l_eff_end_date           := NULL;
   l_pos_name               := NULL;
 


/*SELECT OBJECT_VERSION_NUMBER, NAME INTO l_object_version_number, l_pos_name
FROM HR_POSITIONS_F
WHERE POSITION_ID = I.POSITION_ID
AND l_effective_date  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
*/
   --
   hr_position_api.update_position
                 ( p_validate                       => l_validate_mode
                  ,p_position_id                  => I.POSITION_ID--l_position_id
                  ,p_effective_start_date     => l_eff_start_date
                  ,p_effective_end_date      => l_eff_end_date
                  ,p_position_definition_id   => l_position_definition_id
                  ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
                  ,p_status                          => l_status     
                  ,p_name                          => I.NAME
                  --,p_segment1                    => l_segment1
                  --,p_segment2                    => l_pos_title_code                   
                  ,p_object_version_number              => I.OBJECT_VERSION_NUMBER--l_object_version_number
                  ,p_effective_date             => l_effective_date
                  ,p_datetrack_mode         => l_datetrack_mode
                 );
                     
     dbms_output.put_line('Update Successful : '||I.POSITION_ID);
     --Important: regenerate_position_name needs to be called when there is a name update
     --                   
     apps.hr_position_api.regenerate_position_name( I.POSITION_ID);--l_position_id);
     --               
     dbms_output.put_line('Regenerate Successful : '||I.POSITION_ID);
               
   if I.OBJECT_VERSION_NUMBER is null then
      dbms_output.put_line('hr_position_api.update_position API Error: '||sqlerrm||' '||I.POSITION_ID);
      rollback;
   elsif l_valid_grades_changed_warning then
      dbms_output.put_line('Warning: Grade changed'||I.POSITION_ID);
      rollback; 
   else
      commit;
   end if;
--
 END LOOP; 
exception
  when others then
     dbms_output.put_line('hr_position_api.update_position API failed with error :'||sqlerrm);
     rollback;
     
end;          

Oracle Apps R12 HRMS Create Position

--With Cursor

DECLARE

   L_BUSINESS_GROUP_ID            NUMBER := 81;

   /* Local Variables */

   L_POSITION_NAME                VARCHAR2 (100);

   L_POSITION_ID                  NUMBER;

   L_STATUS                       VARCHAR2 (5) := 'True';

   L_JOB_ID                       PER_JOBS.JOB_ID%TYPE;

   L_SEGMENT2                     VARCHAR2 (200);

   L_ORGANIZATION_ID              HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;

   L_ERROR_MESSAGE                VARCHAR2 (1000);

   L_VALIDATE_CNT                 NUMBER;

   /* Out Variables */

   L_EFFECTIVE_START_DATE         DATE;

   L_EFFECTIVE_END_DATE           DATE;

   OUT_P_POSITION_ID              NUMBER;

   OUT_P_OBJECT_VERSION_NUMBER    NUMBER;

   OUT_P_POSITION_DEFINITION_ID   NUMBER;

   OUT_P_NAME                     VARCHAR2 (250);

   L_TOTAL_RECORDS                NUMBER := 0;

   L_SUCCESS_RECORDS              NUMBER := 0;

   L_FAILURE_RECORDS              NUMBER := 0;

   CURSOR C1

   IS

        

        SELECT ROWID ROWIDS,

               TRIM (POSITION_NAME) POSITION_NAME,SEGMENT3,

               (SELECT PJ.JOB_ID

                  FROM PER_JOBS PJ

                 WHERE 1 = 1 AND UPPER(PJ.NAME) = UPPER(TRIM (A.JOB_NAME))

                 AND BUSINESS_GROUP_ID =81

                 AND ROWNUM =1

                 )

                  JOB_ID,

               JOB_NAME,

               (SELECT ORGANIZATION_ID

                  FROM HR_ORGANIZATION_UNITS

                 WHERE 1 = 1 AND UPPER (NAME) = UPPER (TRIM (A.DEPARTMENT_NAME))

                 AND ROWNUM =1

                 )

                  ORG_ID,

               DEPARTMENT_NAME

          FROM TESTA A

         WHERE 1 = 1

               AND NOT EXISTS

                          (SELECT *

                             FROM PER_POSITIONS PP,

                                  PER_POSITION_DEFINITIONS PPD

                            WHERE 1 = 1

                                  AND PP.POSITION_DEFINITION_ID =

                                         PPD.POSITION_DEFINITION_ID

                                  AND UPPER(NAME) = UPPER(TRIM( (A.POSITION_NAME))))

--                                  AND TRIM(UPPER(POSITION_NAME)) NOT IN ('QUALITY MANAGER',UPPER('A/C Technician'),'EMERGENCY MEDIC TECHNICIAN', UPPER('EXECUTIVE SECRETARY'), UPPER('FOREMAN'),

--                                  UPPER('GROUND EQPT OPERATOR') , UPPER('PAX SERVICES/AGENT'))

--                                  AND 'FIA.'||UPPER(POSITION_NAME)||'.'||SEGMENT3 NOT IN ('FIA.SECRETARY.3')

                                  AND STATUS IS  NULL --OR CHECK ANY DUPLICATES POSITIONS EVEN WITH DIFF ORGS WILL THROW ERROR

--                                  AND UPPER(POSITION_NAME) <> UPPER('DRIVER')

      ORDER BY POSITION_NAME;

      

      

BEGIN

   fnd_global.apps_initialize (0, 50419, 800);        -- 0 sysadmin user id

   FOR C1_REC1 IN C1

   LOOP

   DBMS_OUTPUT.PUT_LINE(C1_REC1.POSITION_NAME||'.'||C1_REC1.SEGMENT3);

   BEGIN

      OUT_P_POSITION_ID := NULL;

      OUT_P_OBJECT_VERSION_NUMBER := NULL;

      OUT_P_POSITION_DEFINITION_ID := NULL;

      OUT_P_NAME := NULL;

      L_EFFECTIVE_START_DATE := NULL;

      L_EFFECTIVE_END_DATE := NULL;

      HR_POSITION_API.CREATE_POSITION (

         P_EFFECTIVE_DATE           => TO_DATE('01-JAN-1950'),

         P_JOB_ID                   => C1_REC1.JOB_ID,

         P_ORGANIZATION_ID          => C1_REC1.ORG_ID,

         P_DATE_EFFECTIVE           => TO_DATE('01-JAN-1950'),

         P_SEGMENT1                 => 'FIA', --,p_segment2 => c1_rec1.job_title

         P_SEGMENT2                 => Initcap(C1_REC1.POSITION_NAME), 

         P_SEGMENT3                 => C1_REC1.SEGMENT3,                                         

         P_FTE                      => NULL,

         P_MAX_PERSONS              => NULL,

         P_POSITION_ID              => OUT_P_POSITION_ID,

         P_OBJECT_VERSION_NUMBER    => OUT_P_OBJECT_VERSION_NUMBER,

         P_POSITION_DEFINITION_ID   => OUT_P_POSITION_DEFINITION_ID,

         P_NAME                     => OUT_P_NAME,

         P_EFFECTIVE_START_DATE     => L_EFFECTIVE_START_DATE,

         P_EFFECTIVE_END_DATE       => L_EFFECTIVE_END_DATE);

      UPDATE TESTA

         SET STATUS = 'SUCCESS'

       WHERE ROWID = C1_REC1.ROWIDS;

      COMMIT;

      DBMS_OUTPUT.PUT_LINE (

         '#############################################################');

      DBMS_OUTPUT.PUT_LINE (' out_p_position_id : ' || OUT_P_POSITION_ID);

      DBMS_OUTPUT.PUT_LINE ('out_p_name : ' || OUT_P_NAME);

      DBMS_OUTPUT.PUT_LINE (

         ' l_effective_start_date: ' || L_EFFECTIVE_START_DATE);

      DBMS_OUTPUT.PUT_LINE (

         '#############################################################');

  exception when others then

                                dbms_output.put_line(sqlerrm);

                                end;     

                      end loop;

   EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

declare
--need to pass parameters
l_business_group_id        number := 81;        
l_job_id                   per_positions.job_id%type := 5198; --job_id
l_pos_org_id               per_positions.position_id%type := 108; --org_id
l_segment1                 varchar2(20) := 'ح ك'; --our segment1 captures valueset code of organizations
l_segment2                 varchar2(20) := '12345'; --our segment2 captures name of position
l_segment3                 varchar2(20) := '1';        -- segment3 i guess constant 1
l_effective_date           date := sysdate;            --effective date
-----------------------------------------------------
l_eff_start_date           date;
l_eff_end_date             date;
l_date_end                 date := hr_general.end_of_time;
l_validate_mode            boolean := false;
l_pos_type                 fnd_lookup_values.lookup_code%type;
l_avail_id                 per_shared_types.shared_type_id%type;
l_fte                      number;
l_max_persons              number;
l_probation_period         number;
l_probation_unit_desc      varchar2(500);
l_orcl_pos_title_code      fnd_lookup_values.lookup_code%type;
l_attribute8               hr_all_positions_f.attribute8%type;
l_attribute9               hr_all_positions_f.attribute9%type;
l_grade_id                 hr_all_positions_f.entry_grade_id%type;
l_barg_unit_cd             hr_all_positions_f.bargaining_unit_cd%type;
l_attribute6               hr_all_positions_f.attribute6%type;
l_working_hours            hr_all_positions_f.working_hours%type;
l_frequency                hr_all_positions_f.frequency%type;
l_position_id              per_positions.position_id%type;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
--
begin
   ---   
    --l_pos_org_id               := 108;
--    l_effective_date           := trunc(sysdate);
    l_date_end                 := hr_general.end_of_time;
    --l_job_id                   := 5198;
    l_pos_type                 := 'SHARED';
    l_avail_id                 := 1;
    l_fte                      := 1;
    l_max_persons              := 100;
    l_probation_period         := 6;
    l_probation_unit_desc      := 'M';
    l_frequency                := 'W';
    l_working_hours            := 37; 
    l_grade_id                 := 77;
    l_barg_unit_cd             := 'EWP';
    l_attribute6               := NULL;
    l_orcl_pos_title_code      := 'MECH_TECH';
    --
    l_position_id              := NULL;
    l_object_version_number    := NULL;
    l_eff_start_date           := NULL;
    l_eff_end_date             := NULL;
    l_position_definition_id   := NULL;
    l_pos_name                 := NULL;

    --
    hr_position_api.create_position
            (
              p_job_id                      => l_job_id             --The job for the position
             ,p_organization_id             => l_pos_org_id
             ,p_effective_date              => l_effective_date 
             ,p_date_effective              => l_effective_date     --The date on which the position becomes active
             --,p_date_end                    => l_date_end
             ,p_validate                    => l_validate_mode
             ,p_position_type               => l_pos_type
             --,p_availability_status_id      => l_avail_id
             ,p_fte                         => l_fte
             ,p_max_persons                 => l_max_persons
             --,p_probation_period            => l_probation_period
             --,p_probation_period_unit_cd    => l_probation_unit_desc
             ,p_business_group_id           => l_business_group_id
             ,p_segment1                    => l_segment1
             ,p_segment2                    => l_segment2
             ,p_segment3                    => l_segment3
             --,p_segment2                    => l_orcl_pos_title_code
             --,p_attribute8                  => l_attribute8
             --,p_attribute9                  => l_attribute9
             --,p_entry_grade_id              => l_grade_id
             --,p_bargaining_unit_cd          => l_barg_unit_cd
             --,p_attribute6                  => l_attribute6
             --
             --,p_working_hours               => l_working_hours
             --,p_frequency                   => l_frequency
             --OUT
             ,p_position_id                 => l_position_id
             ,p_object_version_number       => l_object_version_number
             ,p_effective_start_date        => l_eff_start_date
             ,p_effective_end_date          => l_eff_end_date
             --IN/OUT                     
             ,p_position_definition_id      => l_position_definition_id
             ,p_name                        => l_pos_name
           );
                    
   if l_position_id is null or l_object_version_number is null then
      dbms_output.put_line('hr_position_api.create_position API Error: '||sqlerrm);
      rollback;
   else
      commit;
   end if; 
--
exception
  when others then
     dbms_output.put_line('hr_position_api.create_position API failed with error :'||sqlerrm);
     rollback;
end;  

Oracle forms 10g to restrict the Query in the Form based on column value

DECLARE
  v_def_where   VARCHAR2(2000) := Get_Block_Property('XXFUJ_PM_DETAILS',DEFAULT_WHERE);
  v_new_where   VARCHAR2(2000);
  --v_condition varchar2(100) := 'EMPLOYEE';
 
BEGIN
  v_new_where := 'CATEGORY = ''MANAGER''';
  Set_Block_Property('XXFUJ_PM_DETAILS',DEFAULT_WHERE, v_new_where);
  Execute_Query;
  -- Now reset the DEFAULT_WHERE back to it's original value.
  --Set_Block_Property('TRANS',DEFAULT_WHERE, v_def_where);
END;

---------------

CATEGORY = ''MANAGER''            --two single quotes

<column_name> = ''<TEXT>''

Converting XML report of excel output format text columns into numbers

Cause: Space at the end of the number fields.
Action:
Exporting data of number format as text, and spaces are suffixed, to remove it.

=LEFT(A1,LEN(A1)-1)
COPY THE VALUE IN TO THE OTHER COLUMN AS VALUES
THEN IT WIL POPUP TO CONVERT AS NUMBER, CLICK IT.

ITS DONE.