How to Create Company Cost Center By Using API

To implement Oracle Daily Business Intelligence it’s required to implement Company Cost Center at Organization definition level. Oracle Provided a API for this, in this document I will share the sample script to update company cost center by executing this script.

Link To Download File

How to Create Position Hierarchy by Using APIs

How to Create Position Hierarchy by Using APIs

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 .

Extract Employee and Supervisor Based on Position Hierarchy

This SQL will give you employee and Supervisor as per position hierarchy .

/* Formatted on 2009/10/19 15:01 (Formatter Plus v4.8.8) */
SELECT LPAD (‘ ‘, 5 * LEVEL) || has.NAME hierarchy, LEVEL,
hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = 81
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
start with pse.parent_position_id = –40979 Base Position Id (like CEO etc)
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 4