1 PACKAGE BODY POR_LOAD_FND_USER as
2 /* $Header: PORFNDUB.pls 115.5 2004/07/02 23:27:57 rwidjaja ship $ */
3
4 PROCEDURE insert_update_user_info (
5 x_employee_number IN VARCHAR2,
6 x_user_name IN VARCHAR2,
7 x_password IN VARCHAR2,
8 x_email_address IN VARCHAR2)
9 IS
10
11 l_return_status VARCHAR2(20);
12 l_msg_data VARCHAR2(2000);
13 l_user_id NUMBER;
14 l_msg_count NUMBER;
15 l_api_version_number NUMBER := 1.0;
16 l_ssp_resp_id NUMBER;
17 l_employee_id NUMBER;
18
19 BEGIN
20
21
22 l_employee_id := get_employee_exists(x_employee_number);
23
24
25 IF (NOT get_fnd_user_exists(x_user_name)) THEN
26
27 FND_User_PVT.Create_User(
28 p_api_version_number => l_api_version_number
29 ,p_return_status => l_return_status
30 ,p_msg_count => l_msg_count
31 ,p_msg_data => l_msg_data
32 ,p_email_address => x_email_address
33 ,p_language => 'US'
34 ,p_host_port => NULL
35 ,p_password => x_password
36 ,p_username => x_user_name
37 ,p_created_by => 0
38 ,p_creation_date => sysdate
39 ,p_last_updated_by => 0
40 ,p_last_update_date => sysdate
41 ,p_user_id => l_user_id
42 );
43
44 update_employee_id(l_employee_id, l_user_id);
45
46 get_default_resp_id('SELF_SERVICE_PURCHASING_5',l_ssp_resp_id);
47
48 IF l_user_id > 0 and
49 l_ssp_resp_id > 0 THEN
50
51
52 FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT (
53 USER_ID => l_user_id,
54 RESPONSIBILITY_ID => l_ssp_resp_id,
55 RESPONSIBILITY_APPLICATION_ID => 178,
56 START_DATE => sysdate,
57 END_DATE => NULL,
58 DESCRIPTION => 'Default'
59 );
60
61 END IF;
62
63 ELSE
64
65
66 FND_User_PVT.Update_user(
67 p_api_version_number => l_api_version_number
68 ,p_return_status => l_return_status
69 ,p_msg_count => l_msg_count
70 ,p_msg_data => l_msg_data
71 ,p_email_address => x_email_address
72 ,p_language => 'US'
73 ,p_host_port => NULL
74 ,p_last_updated_by => 0
75 ,p_last_update_date => sysdate
76 ,p_user_id => l_user_id
77 );
78
79
80 END IF;
81
82 EXCEPTION
83
84 WHEN NO_DATA_FOUND THEN
85 RETURN;
86
87 WHEN OTHERS THEN
88
89 RAISE;
90
91 END insert_update_user_info;
92
93
94 PROCEDURE get_default_resp_id (p_resp_key IN VARCHAR2, p_resp_id OUT NOCOPY NUMBER)
95 IS
96 BEGIN
97
98 SELECT responsibility_id INTO p_resp_id
99 FROM fnd_responsibility
100 WHERE responsibility_key = p_resp_key
101 AND application_id = 178;
102
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 RETURN;
106
107 END get_default_resp_id;
108
109 FUNCTION get_fnd_user_exists(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
110 l_fnd_user_exists NUMBER;
111 BEGIN
112
113 SELECT 1 INTO l_fnd_user_exists FROM fnd_user
114 WHERE user_name = upper(p_user_name);
115
116 RETURN true;
117
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120
121 RETURN false;
122
123 END get_fnd_user_exists;
124
125 FUNCTION get_employee_exists (p_employee_number IN VARCHAR2) RETURN NUMBER IS
126 l_person_id NUMBER;
127
128 BEGIN
129
130 SELECT person_id INTO l_person_id
131 FROM per_all_people_f
132 WHERE employee_number = p_employee_number;
133
134 RETURN l_person_id;
135
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 RETURN NULL;
139
140 END get_employee_exists;
141
142 PROCEDURE update_employee_id(p_employee_id IN NUMBER,p_user_id IN NUMBER)
143 IS
144 BEGIN
145
146
147 UPDATE fnd_user
148 SET employee_id = p_employee_id
149 WHERE user_id = p_user_id;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 RETURN;
154
155 END update_employee_id;
156
157 END POR_LOAD_FND_USER;
|
|
|