2010 How to Create Position Hierarchy by Using APIs Oracle utilizes position as road map to determine how and where documents will be routed once the approval process has been initiated. It’s always very difficult for functional consultant or application super user to create Position hierarchy manually specially for organization where they have more number of positions. This document contains the all script required to create position hierarchy in oracle application to be used by Purchasing and HRMS system . Ahmad Bilal 1/25/2010 Email: [email protected] I. Create Temporary Table to Load Data CREATE TABLE RAW_POSITION_H_DATA(SLNO NUMBER, PARENT_POSITION_ID NUMBER, CHILD_POSITION_ID NUMBER) II. UPLOAD DATA INTO RAW_POSITION_H_DATA BY USING SQLLDR DATA FORMAT WILL BE LIKE THIS Sl No 1 2 3 III. PARENT_POSITION_ID 21165 21163 21164 CHILD_POSITION_ID 21164 21162 21163 Verify the Data in table SELECT * FROM RAW_POSITION_H_DATA H START WITH H.PARENT_POSITION_ID = 21165 -- (TOP NODE POSITION ID LIKE CHAIRMAN OR C.E.O)) CONNECT BY PRIOR H.CHILD_POSITION_ID = H.PARENT_POSITION_ID; Data will show in hierarchical order IV. Alter Table in Order to Capture the New Id’s and exceptions ALTER TABLE APPS_APPLMGR.RAW_POSITION_H_DATA ADD (NEW_PARENT_POSITION_ID NUMBER, NEW_SUBORDINATE_POSITION_ID NUMBER, ERROR_MESSAGE VARCHAR2 (2000), STATUS VARCHAR2(3), OBJECT_VERSION_NUMBER NUMBER, POS_STRUCTURE_ELEMENT_ID NUMBER); V. Create Procedure to Define Position Hierarchy Parent Record http://oracleebusinesssuite.wordpress.com/ Page 2 CREATE OR REPLACE PROCEDURE CREATE_POSITION_H IS P_VALIDATE BOOLEAN := SY S.D IUT IL .INT_TO _ BOOL (0); P_POSITION_STRUCTURE_ID NUMBER; P_OBJECT_VERSION_NUMBER NUMBER; P_EFFECTIVE_DATE DATE := TO_DATE ('01-01-1990', 'DD-MM-YYYY'); /* THIS DATE SHOULD BE AFTER THE ALL POSITION EFFECTIVE START DATE */ P_NAME VARCHAR2 (240) := 'Position Hierarchy By API'; BEGIN PER_POSITION_STRUCTURE_API.CREATE_POSITION_STRUCTURE (P_VALIDATE => P_VALIDATE, P_EFFECTIVE_DATE => P_EFFECTIVE_DATE, P_NAME => P_NAME, P_BUSINESS_GROUP_ID => 0, P_COMMENTS => NULL, P_PRIMARY_POSITION_FLAG => 'N', P_REQUEST_ID => NULL, P_PROGRAM_APPLICATION_ID => NULL, P_PROGRAM_ID => NULL, P_PROGRAM_UPDATE_DATE => NULL, P_ATTRIBUTE_CATEGORY => NULL, P_ATTRIBUTE1 => NULL, P_ATTRIBUTE2 => NULL, P_ATTRIBUTE3 => NULL, P_ATTRIBUTE4 => NULL, P_ATTRIBUTE5 => NULL, P_ATTRIBUTE6 => NULL, P_ATTRIBUTE7 => NULL, P_ATTRIBUTE8 => NULL, P_ATTRIBUTE9 => NULL, P_ATTRIBUTE10 => NULL, P_ATTRIBUTE11 => NULL, P_ATTRIBUTE12 => NULL, P_ATTRIBUTE13 => NULL, P_ATTRIBUTE14 => NULL, P_ATTRIBUTE15 => NULL, P_ATTRIBUTE16 => NULL, P_ATTRIBUTE17 => NULL, P_ATTRIBUTE18 => NULL, P_ATTRIBUTE19 => NULL, P_ATTRIBUTE20 => NULL, P_POSITION_STRUCTURE_ID => P_POSITION_STRUCTURE_ID, P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ); COMMIT; END; / VI. Execute Procedure BEGIN CREATE_POSITION_H; COMMIT; END; USE FOLLOWING QUERY TO VERIFY THAT POSITION HEADER IS CREATED SELECT POSITION_STRUCTURE_ID http://oracleebusinesssuite.wordpress.com/ Page 3 FROM PER_POSITION_STRUCTURES PPS WHERE PPS.NAME = 'Position Hierarchy By API'; VII. Create Procedure to Define Hierarchy version CREATE OR REPLACE PROCEDURE CREATE_POSITION_H_VERSION IS P_VALIDATE BOOLEAN := SYS.DIUTIL.INT_TO_BOOL (0); P_GAP_WARNING BOOLEAN; P_POS_STRUCTURE_VERSION_ID NUMBER; P_OBJECT_VERSION_NUMBER NUMBER; P_DATE_FROM DATE := TO_DATE ('01-01-1990', 'DD-MM-YYYY'); /* THIS DATE SHOULD BE AFTER THE ALL POSITION EFFECTIVE START DATE */ P_EFFECTIVE_DATE DATE := TO_DATE ('01-01-1990', 'DD-MM-YYYY'); /* THIS DATE SHOULD BE AFTER THE ALL POSITION EFFECTIVE START DATE */ P_POSITION_STRUCTURE_ID NUMBER; P_VERSION_NUMBER NUMBER := 1; BEGIN SELECT POSITION_STRUCTURE_ID INTO P_POSITION_STRUCTURE_ID FROM PER_POSITION_STRUCTURES PPS WHERE PPS.NAME = 'Position Hierarchy By API'; PER_POS_STRUCTURE_VERSION_API.CREATE_POS_STRUCTURE_VERSION (P_VALIDATE => P_VALIDATE, P_EFFECTIVE_DATE => P_EFFECTIVE_DATE, P_POSITION_STRUCTURE_ID => P_POSITION_STRUCTURE_ID, P_DATE_FROM => P_DATE_FROM, P_VERSION_NUMBER => P_VERSION_NUMBER, P_COPY_STRUCTURE_VERSION_ID => NULL, P_DATE_TO => NULL, P_REQUEST_ID => NULL, P_PROGRAM_APPLICATION_ID => NULL, P_PROGRAM_ID => NULL, P_PROGRAM_UPDATE_DATE => NULL, P_POS_STRUCTURE_VERSION_ID => P_POS_STRUCTURE_VERSION_ID, P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER, P_GAP_WARNING => P_GAP_WARNING ); COMMIT; END; / VIII. Execute Procedure BEGIN CREATE_POSITION_H_VERSION; COMMIT; END; Use Following Query to verify the Version Created SELECT POS_STRUCTURE_VERSION_ID INTO P_POS_STRUCTURE_VERSION_ID FROM PER_POSITION_STRUCTURES PPS, PER_POS_STRUCTURE_VERSIONS PSV WHERE PPS.NAME = 'Position Hierarchy By API' AND PPS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID; IX. Create Procedure to Load Elements of Position hierarchy CREATE OR REPLACE PROCEDURE CREATE_POSITION_HIERARCHY_PRC http://oracleebusinesssuite.wordpress.com/ Page 4 IS P_VALIDATE BOOLEAN := SYS.DIUTIL.INT_TO_BOOL (0); P_POS_STRUCTURE_ELEMENT_ID NUMBER; P_POS_STRUCTURE_VERSION_ID NUMBER; P_OBJECT_VERSION_NUMBER NUMBER; P_EFFECTIVE_DATE DATE := TO_DATE ('01-01-1990', 'DD-MM-YYYY'); /* THIS DATE SHOULD BE AFTER THE ALL POSITION EFFECTIVE START DATE */ P_ERROR VARCHAR2 (2000); VNEW_SUBORDINATE_POSITION_ID NUMBER; /* EXTRACT THE NEWHIERARCHY */ CURSOR CUR IS SELECT * FROM RAW_POSITION_H_DATA H START WITH H.PARENT_POSITION_ID = 21165 -- (TOP NODE POSITION ID LIKE CHAIRMAN OR C.E.O)) CONNECT BY PRIOR H.CHILD_POSITION_ID = H.PARENT_POSITION_ID; BEGIN -- GET STURCTURE VERSION ID SELECT POS_STRUCTURE_VERSION_ID INTO P_POS_STRUCTURE_VERSION_ID FROM PER_POSITION_STRUCTURES PPS, PER_POS_STRUCTURE_VERSIONS PSV WHERE PPS.NAME = 'Position Hierarchy By API' AND PPS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID; FOR POS IN CUR LOOP BEGIN HR_POS_HIERARCHY_ELE_API.CREATE_POS_HIERARCHY_ELE (P_VALIDATE => P_VALIDATE, P_PARENT_POSITION_ID => POS.PARENT_POSITION_ID, P_POS_STRUCTURE_VERSION_ID => P_POS_STRUCTURE_VERSION_ID, P_SUBORDINATE_POSITION_ID => POS.CHILD_POSITION_ID, P_BUSINESS_GROUP_ID => 0, P_HR_INSTALLED => NULL, P_EFFECTIVE_DATE => P_EFFECTIVE_DATE, P_POS_STRUCTURE_ELEMENT_ID => P_POS_STRUCTURE_ELEMENT_ID, P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ); UPDATE RAW_POSITION_H_DATA H SET H.POS_STRUCTURE_ELEMENT_ID = P_POS_STRUCTURE_ELEMENT_ID, H.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER, STATUS = 'I' WHERE H.SLNO = POS.SLNO; COMMIT; EXCEPTION WHEN OTHERS THEN P_ERROR := SQLERRM; UPDATE RAW_POSITION_H_DATA H SET H.STATUS = 'E', ERROR_MESSAGE = P_ERROR WHERE H.SLNO = POS.SLNO; COMMIT; END; END LOOP; END; / X. Execute Procedure http://oracleebusinesssuite.wordpress.com/ Page 5 BEGIN CREATE_POSITION_HIERARCHY_PRC; COMMIT; END; Verify Position Hierarchy by using following query 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 = 0 AND HAP.POSITION_ID = PSE.PARENT_POSITION_ID AND HAS.POSITION_ID = PSE.SUBORDINATE_POSITION_ID AND PSE.POS_STRUCTURE_VERSION_ID IN ( SELECT POS_STRUCTURE_VERSION_ID FROM PER_POSITION_STRUCTURES PPS, PER_POS_STRUCTURE_VERSIONS PSV WHERE PPS.NAME = 'Position Hierarchy By API' AND PPS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID) START WITH PSE.PARENT_POSITION_ID = 21165 -- (TOP NODE POSITION ID LIKE CHAIRMAN OR C.E.O)) 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 http://oracleebusinesssuite.wordpress.com/ Page 6
© Copyright 2024