[Home] [Help]
PACKAGE BODY: APPS.PSB_ENTITY_ASSIGNMENT_PVT
Source
1 PACKAGE BODY PSB_ENTITY_ASSIGNMENT_PVT AS
2 /* $Header: PSBVEAPB.pls 115.4 2002/11/18 10:57:05 msuram ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_ENTITY_ASSIGNMENT_PVT';
5
6 procedure INSERT_ROW(
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE,
10 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
11 p_return_status OUT NOCOPY VARCHAR2,
12 p_msg_count OUT NOCOPY NUMBER,
13 p_msg_data OUT NOCOPY VARCHAR2,
14 --
15 P_ROWID in OUT NOCOPY VARCHAR2,
16 P_ENTITY_SET_ID in NUMBER,
17 P_ENTITY_ID in NUMBER,
18 P_PRIORITY in NUMBER,
19 P_SEVERITY_LEVEL in NUMBER,
20 P_EFFECTIVE_START_DATE in DATE,
21 P_EFFECTIVE_END_DATE in DATE,
22 p_Last_Update_Date DATE,
23 p_Last_Updated_By NUMBER,
24 p_Last_Update_Login NUMBER,
25 p_Created_By NUMBER,
26 p_Creation_Date DATE
27 ) is
28 --
29 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
30 l_api_version CONSTANT NUMBER := 1.0;
31 --
32 cursor C is select ROWID from PSB_ENTITY_ASSIGNMENT
33 where ENTITY_SET_ID = P_ENTITY_SET_ID
34 and ENTITY_ID = P_ENTITY_ID;
35 BEGIN
36 --
37 SAVEPOINT Insert_Row_Pvt ;
38 --
39 IF NOT FND_API.Compatible_API_Call ( l_api_version,
40 p_api_version,
41 l_api_name,
42 G_PKG_NAME )
43 THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
45 END IF;
46 --
47
48 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
49 FND_MSG_PUB.initialize ;
50 END IF;
51 --
52 p_return_status := FND_API.G_RET_STS_SUCCESS ;
53 --
54
55 insert into PSB_ENTITY_ASSIGNMENT (
56 ENTITY_SET_ID,
57 ENTITY_ID,
58 PRIORITY,
59 SEVERITY_LEVEL,
60 EFFECTIVE_START_DATE,
61 EFFECTIVE_END_DATE,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 P_ENTITY_SET_ID,
69 P_ENTITY_ID,
70 P_PRIORITY,
71 P_SEVERITY_LEVEL,
72 P_EFFECTIVE_START_DATE,
73 P_EFFECTIVE_END_DATE,
74 P_LAST_UPDATE_DATE,
75 P_LAST_UPDATED_BY,
76 P_LAST_UPDATE_DATE,
77 P_LAST_UPDATED_BY,
78 P_LAST_UPDATE_LOGIN
79 );
80
81 open c;
82 fetch c into P_ROWID;
83 if (c%notfound) then
84 close c;
85 raise no_data_found;
86 end if;
87 close c;
88
89 --
90
91 --
92 IF FND_API.To_Boolean ( p_commit ) THEN
93 COMMIT WORK;
94 END iF;
95 --
96 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
97 p_data => p_msg_data );
98 --
99 EXCEPTION
100 --
101 WHEN FND_API.G_EXC_ERROR THEN
102 --
103 ROLLBACK TO Insert_Row_Pvt ;
104 p_return_status := FND_API.G_RET_STS_ERROR;
105 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
106 p_data => p_msg_data );
107 --
108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
109 --
110 ROLLBACK TO Insert_Row_Pvt ;
111 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
112 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
113 p_data => p_msg_data );
114 --
115 WHEN OTHERS THEN
116 --
117 ROLLBACK TO Insert_Row_Pvt ;
118 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 --
120 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
121 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
122 l_api_name);
123 END if;
124 --
125 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
126 p_data => p_msg_data );
127 --
128 END Insert_Row;
129
130 procedure LOCK_ROW(
131 p_api_version IN NUMBER,
132 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
134 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
135 p_return_status OUT NOCOPY VARCHAR2,
136 p_msg_count OUT NOCOPY NUMBER,
137 p_msg_data OUT NOCOPY VARCHAR2,
138 p_lock_row OUT NOCOPY VARCHAR2,
139 --
140 P_ENTITY_SET_ID in NUMBER,
141 P_ENTITY_ID in NUMBER,
142 P_PRIORITY in NUMBER,
143 P_SEVERITY_LEVEL in NUMBER,
144 P_EFFECTIVE_START_DATE in DATE,
145 P_EFFECTIVE_END_DATE in DATE
146 ) is
147 --
148 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
149 l_api_version CONSTANT NUMBER := 1.0;
150 --
151 Counter NUMBER;
152 cursor c1 is select
153 PRIORITY,
154 SEVERITY_LEVEL,
155 EFFECTIVE_START_DATE,
156 EFFECTIVE_END_DATE
157 from PSB_ENTITY_ASSIGNMENT
158 where ENTITY_SET_ID = P_ENTITY_SET_ID
159 and ENTITY_ID = P_ENTITY_ID
160 for update of ENTITY_SET_ID nowait;
161 tlinfo c1%rowtype;
162
163 BEGIN
164 --
165 SAVEPOINT Lock_Row_Pvt ;
166 --
167 IF NOT FND_API.Compatible_API_Call ( l_api_version,
168 p_api_version,
169 l_api_name,
170 G_PKG_NAME )
171 THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
173 END IF;
174 --
175
176 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
177 FND_MSG_PUB.initialize ;
178 END IF;
179 --
180 p_return_status := FND_API.G_RET_STS_SUCCESS ;
181 --
182 open c1;
183
184 fetch c1 into tlinfo;
185 if (c1%notfound) then
186 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
187 app_exception.raise_exception;
188 close c1;
189 return;
190 end if;
191 close c1;
192
193 if ( ((tlinfo.PRIORITY = P_PRIORITY)
194 OR ((tlinfo.PRIORITY is null)
195 AND (P_PRIORITY is null)))
196 AND ((tlinfo.SEVERITY_LEVEL = P_SEVERITY_LEVEL)
197 OR ((tlinfo.SEVERITY_LEVEL is null)
198 AND (P_SEVERITY_LEVEL is null)))
199 AND (tlinfo.EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE)
200 AND ((tlinfo.EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE)
201 OR ((tlinfo.EFFECTIVE_END_DATE is null)
202 AND (P_EFFECTIVE_END_DATE is null)))
203 )
204 THEN
205 p_lock_row := FND_API.G_TRUE;
206 ELSE
207 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
208 FND_MSG_PUB.Add;
209 RAISE FND_API.G_EXC_ERROR ;
210 END IF;
211
212 --
213 IF FND_API.To_Boolean ( p_commit ) THEN
214 COMMIT WORK;
215 END iF;
216 --
217 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
218 p_data => p_msg_data );
219 --
220 EXCEPTION
221 --
222 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
223 --
224 ROLLBACK TO Lock_Row_Pvt ;
225 p_lock_row := FND_API.G_FALSE;
226 p_return_status := FND_API.G_RET_STS_ERROR;
227 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
228 p_data => p_msg_data );
229 --
230 WHEN FND_API.G_EXC_ERROR THEN
231 --
232 ROLLBACK TO Lock_Row_Pvt ;
233 p_lock_row := FND_API.G_FALSE;
234 p_return_status := FND_API.G_RET_STS_ERROR;
235 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
236 p_data => p_msg_data );
237 --
238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
239 --
240 ROLLBACK TO Lock_Row_Pvt ;
241 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
243 p_data => p_msg_data );
244 --
245 WHEN OTHERS THEN
246 --
247 ROLLBACK TO Lock_Row_Pvt ;
248 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 --
250 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
251 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
252 l_api_name);
253 END if;
254 --
255 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
256 p_data => p_msg_data );
257 --
258 END Lock_Row;
259
260 procedure UPDATE_ROW(
261 p_api_version IN NUMBER,
262 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
263 p_commit IN VARCHAR2 := FND_API.G_FALSE,
264 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
265 p_return_status OUT NOCOPY VARCHAR2,
266 p_msg_count OUT NOCOPY NUMBER,
267 p_msg_data OUT NOCOPY VARCHAR2,
268 --
269 P_ENTITY_SET_ID in NUMBER,
270 P_ENTITY_ID in NUMBER,
271 P_PRIORITY in NUMBER,
272 P_SEVERITY_LEVEL in NUMBER,
273 P_EFFECTIVE_START_DATE in DATE,
274 P_EFFECTIVE_END_DATE in DATE,
275 p_Last_Update_Date DATE,
276 p_Last_Updated_By NUMBER,
277 p_Last_Update_Login NUMBER
278 ) is
279 --
280 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
281 l_api_version CONSTANT NUMBER := 1.0;
282 --
283 BEGIN
284 --
285 SAVEPOINT Update_Row_Pvt ;
286 --
287 IF NOT FND_API.Compatible_API_Call ( l_api_version,
288 p_api_version,
289 l_api_name,
290 G_PKG_NAME )
291 THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
293 END IF;
294 --
295
296 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
297 FND_MSG_PUB.initialize ;
298 END IF;
299 --
300 p_return_status := FND_API.G_RET_STS_SUCCESS ;
301 --
302 update PSB_ENTITY_ASSIGNMENT set
303 PRIORITY = P_PRIORITY,
304 SEVERITY_LEVEL = P_SEVERITY_LEVEL,
305 EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE,
306 EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE,
307 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
308 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
309 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
310 where ENTITY_SET_ID = P_ENTITY_SET_ID
311 and ENTITY_ID = P_ENTITY_ID
312 ;
313 IF (SQL%NOTFOUND) THEN
314 RAISE NO_DATA_FOUND ;
315 END IF;
316
317 --
318 IF FND_API.To_Boolean ( p_commit ) THEN
319 COMMIT WORK;
320 END iF;
321 --
322 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
323 p_data => p_msg_data );
324 --
325 EXCEPTION
326 --
327 WHEN FND_API.G_EXC_ERROR THEN
328 --
329 ROLLBACK TO Update_Row_Pvt ;
330 p_return_status := FND_API.G_RET_STS_ERROR;
331 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
332 p_data => p_msg_data );
333 --
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
335 --
336 ROLLBACK TO Update_Row_Pvt ;
337 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
339 p_data => p_msg_data );
340 --
341 WHEN OTHERS THEN
342 --
343 ROLLBACK TO Update_Row_Pvt ;
344 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 --
346 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
347 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
348 l_api_name);
349 END if;
350 --
351 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
352 p_data => p_msg_data );
353 --
354 END Update_Row;
355
356
357 procedure DELETE_ROW (
358 p_api_version IN NUMBER,
359 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
360 p_commit IN VARCHAR2 := FND_API.G_FALSE,
361 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
362 p_return_status OUT NOCOPY VARCHAR2,
363 p_msg_count OUT NOCOPY NUMBER,
364 p_msg_data OUT NOCOPY VARCHAR2,
365 --
366 P_ENTITY_SET_ID in NUMBER,
367 P_ENTITY_ID in NUMBER
368 ) is
369 --
370 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
371 l_api_version CONSTANT NUMBER := 1.0;
372 --
373 BEGIN
374 --
375 SAVEPOINT Delete_Row_Pvt ;
376 --
377 IF NOT FND_API.Compatible_API_Call ( l_api_version,
378 p_api_version,
379 l_api_name,
380 G_PKG_NAME )
381 THEN
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
383 END IF;
384 --
385
386 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
387 FND_MSG_PUB.initialize ;
388 END IF;
389 --
390 p_return_status := FND_API.G_RET_STS_SUCCESS ;
391 --
392 delete from PSB_ENTITY_ASSIGNMENT
393 where ENTITY_SET_ID = P_ENTITY_SET_ID
394 and ENTITY_ID = P_ENTITY_ID;
395
396 IF (SQL%NOTFOUND) THEN
397 RAISE NO_DATA_FOUND ;
398 END IF;
399
400 --
401 IF FND_API.To_Boolean ( p_commit ) THEN
402 COMMIT WORK;
403 END iF;
404 --
405 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
406 p_data => p_msg_data );
407
408 EXCEPTION
409 --
410 WHEN FND_API.G_EXC_ERROR THEN
411 --
412 ROLLBACK TO Delete_Row_Pvt ;
413 p_return_status := FND_API.G_RET_STS_ERROR;
414 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
415 p_data => p_msg_data );
416 --
417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
418 --
419 ROLLBACK TO Delete_Row_Pvt ;
420 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
422 p_data => p_msg_data );
423 --
424 WHEN OTHERS THEN
425 --
426 ROLLBACK TO Delete_Row_Pvt ;
427 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 --
429 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
430 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
431 l_api_name);
432 END if;
433 --
434 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
435 p_data => p_msg_data );
436 --
437 END Delete_Row;
438
439 End PSB_ENTITY_ASSIGNMENT_PVT;