[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_USER_ROLES_TBH
Source
1 package body IGW_PROP_USER_ROLES_TBH as
2 /* $Header: igwtpurb.pls 115.4 2002/11/15 00:47:12 ashkumar ship $*/
3
4 PROCEDURE INSERT_ROW (
5 x_rowid out NOCOPY VARCHAR2,
6 p_proposal_id in NUMBER,
7 p_user_id in NUMBER,
8 p_role_id in NUMBER,
9 p_mode in VARCHAR2 default 'R',
10 x_return_status out NOCOPY VARCHAR2
11 ) is
12
13 cursor c is select ROWID from IGW_PROP_USER_ROLES
14 where proposal_id = p_proposal_id
15 and user_id = p_user_id
16 and role_id = p_role_id;
17
18 l_last_update_date DATE;
19 l_last_updated_by NUMBER;
20 l_last_update_login NUMBER;
21
22 BEGIN
23 x_return_status := FND_API.G_RET_STS_SUCCESS;
24
25 l_last_update_date := SYSDATE;
26
27 if(p_mode = 'I') then
28 l_last_updated_by := 1;
29 l_last_update_login := 0;
30 elsif (p_mode = 'R') then
31 l_last_updated_by := FND_GLOBAL.USER_ID;
32
33 if l_last_updated_by is NULL then
34 l_last_updated_by := -1;
35 end if;
36
37 l_last_update_login := FND_GLOBAL.LOGIN_ID;
38
39 if l_last_update_login is NULL then
40 l_last_update_login := -1;
41 end if;
42 else
43 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
44 app_exception.raise_exception;
45 end if;
46
47 insert into igw_prop_user_roles (
48 proposal_id,
49 user_id,
50 role_id,
51 last_update_date,
52 last_updated_by,
53 creation_date,
54 created_by,
55 last_update_login,
56 record_version_number
57 ) values (
58 p_proposal_id,
59 p_user_id,
60 p_role_id,
61 l_last_update_date,
62 l_last_updated_by,
63 l_last_update_date,
64 l_last_updated_by,
65 l_last_update_login,
66 1
67 );
68
69 open c;
70 fetch c into x_rowid;
71 if (c%notfound) then
72 close c;
73 raise no_data_found;
74 end if;
75 close c;
76
77 EXCEPTION
78 when others then
79 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_TBH',
80 p_procedure_name => 'INSERT_ROW',
81 p_error_text => SUBSTRB(SQLERRM, 1, 240));
82 x_return_status := fnd_api.g_ret_sts_unexp_error;
83 raise;
84
85 END INSERT_ROW;
86 ----------------------------------------------------------------------------------------------------
87 -------- Since the primary key is updateable on the front end rowid is REQUIRED for doing update ------
88 PROCEDURE UPDATE_ROW (
89 x_rowid in VARCHAR2,
90 p_proposal_id in NUMBER,
91 p_user_id in NUMBER,
92 p_role_id in NUMBER,
93 p_mode in VARCHAR2 default 'R',
94 p_record_version_number in NUMBER,
95 x_return_status out NOCOPY VARCHAR2
96 ) is
97
98 l_last_update_date DATE;
99 l_last_updated_by NUMBER;
100 l_last_update_login NUMBER;
101
102 BEGIN
103 x_return_status := fnd_api.g_ret_sts_success;
104
105
106 l_last_update_date := SYSDATE;
107 if(p_mode = 'I') then
108 l_last_updated_by := 1;
109 l_last_update_login := 0;
110 elsif (p_mode = 'R') then
111 l_last_updated_by := FND_GLOBAL.USER_ID;
112
113 if l_last_updated_by is NULL then
114 l_last_updated_by := -1;
115 end if;
116
117 l_last_update_login := FND_GLOBAL.LOGIN_ID;
118
119 if l_last_update_login is NULL then
120 l_last_update_login := -1;
121 end if;
122 else
123 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
124 app_exception.raise_exception;
125 end if;
126
127 update IGW_PROP_USER_ROLES set
128 proposal_id = p_proposal_id,
129 user_id = p_user_id,
130 role_id = p_role_id,
131 last_update_date = l_last_update_date,
132 last_updated_by = l_last_updated_by,
133 last_update_login = l_last_update_login,
134 record_version_number = record_version_number + 1
135 where rowid = x_rowid
136 and record_version_number = p_record_version_number;
137
138 if (sql%notfound) then
139 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
140 fnd_msg_pub.Add;
141 x_return_status := 'E';
142 end if;
143
144 EXCEPTION
145 when others then
146 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_TBH',
147 p_procedure_name => 'UPDATE_ROW',
148 p_error_text => SUBSTRB(SQLERRM, 1, 240));
149 x_return_status := fnd_api.g_ret_sts_unexp_error;
150 raise;
151
152 END UPDATE_ROW;
153 ----------------------------------------------------------------------------------------------------
154 -------- Since the primary key is updateable on the front end rowid is REQUIRED for doing delete ------
155 PROCEDURE DELETE_ROW (
156 x_rowid in VARCHAR2,
157 p_record_version_number in NUMBER,
158 x_return_status out NOCOPY VARCHAR2
159 ) is
160
161 BEGIN
162 x_return_status := fnd_api.g_ret_sts_success;
163
164 delete from IGW_PROP_USER_ROLES
165 where rowid = x_rowid
166 and record_version_number = p_record_version_number;
167
168 if (sql%notfound) then
169 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
170 fnd_msg_pub.Add;
171 x_return_status := 'E';
172 end if;
173
174
175 EXCEPTION
176 when others then
177 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_TBH',
178 p_procedure_name => 'DELETE_ROW',
179 p_error_text => SUBSTRB(SQLERRM, 1, 240));
180 x_return_status := fnd_api.g_ret_sts_unexp_error;
181 raise;
182
183 END DELETE_ROW;
184 ------------------------------------------------------------------------------------------------------
185
186
187 /* procedure ADD_ROW (
188 X_ROWID in out NOCOPY VARCHAR2,
189 X_RULE_ID in NUMBER,
190 X_RULE_SEQUENCE_NUMBER in NUMBER,
191 X_ORGANIZATION_ID in NUMBER,
192 X_RULE_NAME in VARCHAR2,
193 X_RULE_TYPE in VARCHAR2,
194 X_MAP_ID in NUMBER,
195 X_VALID_FLAG in VARCHAR2,
196 X_START_DATE_ACTIVE in DATE,
197 X_END_DATE_ACTIVE in DATE,
198 X_MODE in VARCHAR2 default 'R'
199 ) is
200 cursor c1 is select rowid from IGW_BUSINESS_RULES_ALL
201 where RULE_ID = X_RULE_ID
202 ;
203 dummy c1%rowtype;
204 begin
205 open c1;
206 fetch c1 into dummy;
207 if (c1%notfound) then
208 close c1;
209 INSERT_ROW (
210 X_ROWID,
211 X_RULE_ID,
212 X_RULE_SEQUENCE_NUMBER,
213 X_ORGANIZATION_ID,
214 X_RULE_NAME,
215 X_RULE_TYPE,
216 X_MAP_ID,
217 X_VALID_FLAG,
218 X_START_DATE_ACTIVE,
219 X_END_DATE_ACTIVE,
220 X_MODE);
221 return;
222 end if;
223 close c1;
224 UPDATE_ROW (
225 X_RULE_ID,
226 X_RULE_SEQUENCE_NUMBER,
227 X_ORGANIZATION_ID,
228 X_RULE_NAME,
229 X_RULE_TYPE,
230 X_MAP_ID,
231 X_VALID_FLAG,
232 X_START_DATE_ACTIVE,
233 X_END_DATE_ACTIVE,
234 X_MODE);
235 end ADD_ROW; */
236
237
238 /* ---------------------- WILL NOT BE USED IN SELF SERVICE -----------------------------------------
239 procedure LOCK_ROW (
240 X_ROWID in VARCHAR2,
241 X_RULE_ID in NUMBER,
242 X_RULE_SEQUENCE_NUMBER in NUMBER,
243 X_ORGANIZATION_ID in NUMBER,
244 X_RULE_NAME in VARCHAR2,
245 X_RULE_TYPE in VARCHAR2,
246 X_MAP_ID in NUMBER,
247 X_VALID_FLAG in VARCHAR2,
248 X_START_DATE_ACTIVE in DATE,
249 X_END_DATE_ACTIVE in DATE
250 ) is
251 cursor c1 is select *
252 from IGW_BUSINESS_RULES_ALL
253 where ROWID = X_ROWID
254 for update of RULE_ID nowait;
255 tlinfo c1%rowtype;
256
257 begin
258 open c1;
259 fetch c1 into tlinfo;
260 if (c1%notfound) then
261 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
262 app_exception.raise_exception;
263 close c1;
264 return;
265 end if;
266 close c1;
267
268 if (
269 (tlinfo.RULE_ID = X_RULE_ID)
270 AND ((tlinfo.RULE_SEQUENCE_NUMBER = X_RULE_SEQUENCE_NUMBER)
271 OR ((tlinfo.RULE_SEQUENCE_NUMBER is null)
272 AND (X_RULE_SEQUENCE_NUMBER is null)))
273 AND (tlinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
274 AND (tlinfo.RULE_NAME = X_RULE_NAME)
275 AND (tlinfo.RULE_TYPE = X_RULE_TYPE)
276 AND ((tlinfo.MAP_ID = X_MAP_ID)
277 OR ((tlinfo.MAP_ID is null)
278 AND (X_MAP_ID is null)))
279 AND ((tlinfo.VALID_FLAG = X_VALID_FLAG)
280 OR ((tlinfo.VALID_FLAG is null)
281 AND (X_VALID_FLAG is null)))
282 AND (tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
283 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
284 OR ((tlinfo.END_DATE_ACTIVE is null)
285 AND (X_END_DATE_ACTIVE is null)))
286 ) then
287 null;
288 else
289 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
290 app_exception.raise_exception;
291 end if;
292 return;
293 end LOCK_ROW;
294 */
295
296 END IGW_PROP_USER_ROLES_TBH;