Define Valid Grades for Position in Oracle HRMS

Define Valid Grades for Positions
Oracle HRMS lets you define Valid Grades for Positions. These definitions will be used to provide warning messages in the Assignment window when you enter Position and Grade information.
Navigate to Position Definitation Form
Query the position and click on the Valid Grades button
Enter and save the valid grades for each position. You can enter a single grade, or a set of grades.

Define Valid Grades for Positions By Using API
/* Formatted on 2010/01/17 08:29 (Formatter Plus v4.8.8) */
create table grade_raw_data(slno number,
position_name varchar2(1000),
grade varchar2(1000))

Upload Data into grade_raw_data table either by using SQLLDR or Some other tool

ALTER TABLE APPS_APPLMGR.GRADE_RAW_DATA
ADD (Status VARCHAR2(3 BYTE),
error_message varchar2(1000)
);

/* Formatted on 2010/01/17 08:40 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE xx_position_valid_grade
IS
CURSOR cur
IS
SELECT g.slno, g.position_name, g.grade, g.status, g.error_message
FROM grade_raw_data g
WHERE status = ‘E’ or Status is Null;

x_rowid VARCHAR2 (200);
x_valid_grade_id NUMBER;
x_business_group_id NUMBER;
x_grade_id NUMBER;
x_date_from DATE;
x_comments VARCHAR2 (200);
x_date_to DATE;
x_job_id NUMBER;
x_position_id NUMBER;
x_attribute_category VARCHAR2 (200);
x_attribute1 VARCHAR2 (200);
v_msg VARCHAR2 (1000) := NULL;
x_attribute2 VARCHAR2 (200);
x_attribute3 VARCHAR2 (200);
x_attribute4 VARCHAR2 (200);
x_attribute5 VARCHAR2 (200);
x_attribute6 VARCHAR2 (200);
x_attribute7 VARCHAR2 (200);
x_attribute8 VARCHAR2 (200);
x_attribute9 VARCHAR2 (200);
x_attribute10 VARCHAR2 (200);
x_attribute11 VARCHAR2 (200);
x_attribute12 VARCHAR2 (200);
x_attribute13 VARCHAR2 (200);
x_attribute14 VARCHAR2 (200);
x_attribute15 VARCHAR2 (200);
x_attribute16 VARCHAR2 (200);
x_attribute17 VARCHAR2 (200);
x_attribute18 VARCHAR2 (200);
x_attribute19 VARCHAR2 (200);
x_attribute20 VARCHAR2 (200);
x_end_of_time DATE;
x_pst1_date_end DATE;
x_pst1_date_effective DATE := TO_DATE (’03-01-2010′, ‘DD-MM-YYYY’);
BEGIN
FOR pos IN cur
LOOP
BEGIN
x_rowid := NULL;
x_valid_grade_id := dbms_random.random;
x_business_group_id := 0;

SELECT grade_id
INTO x_grade_id
FROM per_grades
WHERE UPPER (NAME) = UPPER (pos.grade);

x_date_from := ’03-jan-2010′;

SELECT position_id
INTO x_position_id
FROM hr_all_positions_f
WHERE UPPER (NAME) = UPPER (pos.position_name);
per_valid_grades_pkg2.insert_row (x_rowid,
x_valid_grade_id,
x_business_group_id,
x_grade_id,
x_date_from,
x_comments,
x_date_to,
x_job_id,
x_position_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_end_of_time,
x_pst1_date_end,
x_pst1_date_effective
);

UPDATE grade_raw_data p
SET p.status = ‘I’
WHERE slno = pos.slno;
x_valid_grade_id := NULL;
X_GRADE_ID := null;
x_position_id := null;
EXCEPTION
WHEN OTHERS
THEN
v_msg := SQLERRM;
UPDATE grade_raw_data p
SET p.status = ‘E’,
p.error_message = v_msg
WHERE slno = pos.slno;
COMMIT;
END;
END LOOP;
END;
/

Next Post will be about Position Hierarchy Upload by Using API .

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s