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;
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;
Excellent work !!
ReplyDelete