Search This Blog

Monday, March 20, 2017

Oracle apps R12 creating fnd users from HR New Hired for SSHR users

We have 11 payrolls, and for each payroll different user name prefix and responsibility differs.
 so we created lookup in HR named FUJ_USER_PREFIX_PAYROLL and
captured
lookup_code payroll_id
Meaning payroll_Name
Description prefix for username
Tag Resp_key

Declare
v_description VARCHAR2 (100) := '';
 v_app_short_name VARCHAR2 (100);
 v_resp_key VARCHAR2 (100);
 V_ERROR VARCHAR2(2000);
 V_COUNT NUMBER ;
 V_COUNT_ALL NUMBER := 0;

 CURSOR get_emp is

SELECT DISTINCT PAPF.PERSON_ID PERSON_ID,
DECODE(PAAF.PAYROLL_ID, 61,XX_FUJ_ENTRIES_ALLOWANCES.DECODE_FND_LOOKUP_AR ('FUJ_USER_PREFIX','LOOKUP_CODE',PAAF.ORGANIZATION_ID,'DESCRIPTION','US')
, 82,''
,XX_FUJ_ENTRIES_ALLOWANCES.DECODE_FND_LOOKUP_AR ('FUJ_USER_PREFIX_PAYROLL','LOOKUP_CODE',PAAF.PAYROLL_ID,'DESCRIPTION','US')
)||PAPF.EMPLOYEE_NUMBER
USER_NAME
,XX_FUJ_ENTRIES_ALLOWANCES.DECODE_FND_LOOKUP_AR ('FUJ_USER_PREFIX_PAYROLL','LOOKUP_CODE',PAAF.PAYROLL_ID,'TAG','US') responsibility_key
--,'PER'
FROM
PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
--AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
--AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND TRUNC(PAPF.CREATION_DATE) = TRUNC(SYSDATE)
AND PAAF.PERSON_ID NOT IN
(
SELECT EMPLOYEE_ID FROM FND_USER
WHERE EMPLOYEE_ID IS NOT NULL
)
 ;

get_emp_rec get_emp%ROWTYPE; 
BEGIN
OPEN get_emp;
loop
 FETCH get_emp INTO get_emp_rec;
 EXIT WHEN get_emp%NOTFOUND;
 fnd_user_pkg.createuser
(x_user_name => get_emp_rec.user_name,
 x_owner => NULL,
 x_unencrypted_password => '12345',
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => get_emp_rec.PERSON_ID,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;

commit;
fnd_user_pkg.addresp(username => UPPER(get_emp_rec.user_name)
 ,resp_app => 'PER'
 ,resp_key => get_emp_rec.responsibility_key
 ,security_group => 'STANDARD'
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);

                --commit;
                V_COUNT_ALL := V_COUNT_ALL +1;
 
  END LOOP;
CLOSE get_emp;
DBMS_OUTPUT.PUT_LINE(V_COUNT_ALL);
EXCEPTION WHEN OTHERS THEN
V_ERROR := SQLERRM;


END;

1 comment: