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