CREATE OR REPLACE PROCEDURE xxx_emp_upd
as
CURSOR CC IS SELECT substr(a.cmp_no,1,6) COMP_NO, substr(a.old_emp_no,1,6) OLD_EMP_NO, SUBSTR(a.new_emp_no,1,6) NEW_EMP_NO
FROM rf_temp55 a; --where old_emp_no = '007189';
-- where
-- APPS.DB_EMP.COMP_NO=:COMP;
-- AND
-- APPS.DB_EMP.OLD_EMP_NO='049772' AND
-- APPS.DB_EMP.NEW_EMP_NO='060085';
BEGIN
DBMS_OUTPUT.PUT_LINE('111111');
FOR X IN CC LOOP
DBMS_OUTPUT.PUT_LINE('222');
UPDATE APPLSYS.FND_FLEX_VALUES
SET APPLSYS.FND_FLEX_VALUES.FLEX_VALUE=X.NEW_EMP_NO,
APPLSYS.FND_FLEX_VALUES.ATTRIBUTE10=X.OLD_EMP_NO
WHERE
APPLSYS.FND_FLEX_VALUES.FLEX_VALUE_SET_ID=1007709 AND
APPLSYS.FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW=X.COMP_NO AND
APPLSYS.FND_FLEX_VALUES.FLEX_VALUE=X.OLD_EMP_NO;
UPDATE GL.GL_CODE_COMBINATIONS
SET GL.GL_CODE_COMBINATIONS.SEGMENT5=X.NEW_EMP_NO,
GL.GL_CODE_COMBINATIONS.ATTRIBUTE10=X.OLD_EMP_NO
WHERE
GL.GL_CODE_COMBINATIONS.SEGMENT1=X.COMP_NO AND
GL.GL_CODE_COMBINATIONS.SEGMENT5=X.OLD_EMP_NO;
END LOOP;
DBMS_OUTPUT.PUT_LINE('33333');
COMMIT;
DBMS_OUTPUT.PUT_LINE('4444444');
END;
