To Update Grade For a Position(per_valid_grade)

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)

);

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;