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