[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_DISTRIBUTIONS_PVT
Source
1 PACKAGE BODY PSB_WS_Distributions_PVT AS
2 /* $Header: PSBVWDTB.pls 120.2 2005/07/13 11:31:01 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_Distributions_PVT';
5
6
7 /*=======================================================================+
8 | PROCEDURE Insert_Row |
9 +=======================================================================*/
10
11 PROCEDURE Insert_Row
12 (
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE,
16 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
17 p_return_status OUT NOCOPY VARCHAR2,
18 p_msg_count OUT NOCOPY NUMBER,
19 p_msg_data OUT NOCOPY VARCHAR2,
20 --
21 p_Row_Id IN OUT NOCOPY VARCHAR2,
22 p_Distribution_Id IN OUT NOCOPY NUMBER,
23 p_Distribution_Rule_Id IN NUMBER,
24 p_Worksheet_Id IN NUMBER,
25 p_Distribution_Date IN DATE,
26 p_Distributed_Flag IN VARCHAR2,
27 p_Last_Update_Date IN DATE,
28 p_Last_Updated_By IN NUMBER,
29 p_Last_Update_Login IN NUMBER,
30 p_Created_By IN NUMBER,
31 p_Creation_Date IN DATE
32 )
33 IS
34 --
35 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
36 l_api_version CONSTANT NUMBER := 1.0;
37 --
38 CURSOR C IS
39 SELECT rowid
40 FROM psb_ws_distributions
41 WHERE distribution_id = p_distribution_id ;
42
43 CURSOR C2 IS
44 SELECT psb_ws_distributions_s.nextval
45 FROM dual;
46 BEGIN
47 --
48 SAVEPOINT Insert_Row_Pvt ;
49 --
50 IF NOT FND_API.Compatible_API_Call ( l_api_version,
51 p_api_version,
52 l_api_name,
53 G_PKG_NAME )
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
56 END IF;
57 --
58
59 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
60 FND_MSG_PUB.initialize ;
61 END IF;
62 --
63 p_return_status := FND_API.G_RET_STS_SUCCESS ;
64 --
65
66 IF ( p_distribution_id is NULL ) THEN
67 OPEN C2;
68
69 FETCH C2 INTO p_distribution_id;
70 CLOSE C2;
71 END IF;
72
73 INSERT INTO psb_ws_distributions
74 (
75 Distribution_Id,
76 Distribution_Rule_Id,
77 Worksheet_Id,
78 Distribution_Date,
79 Distributed_Flag,
80 last_update_date,
81 last_updated_by,
82 last_update_login,
83 created_by,
84 creation_date
85 )
86 VALUES
87 (
88 p_Distribution_Id,
89 p_Distribution_Rule_Id,
90 p_Worksheet_Id,
91 p_Distribution_Date,
92 p_Distributed_Flag,
93 p_Last_Update_Date,
94 p_Last_Updated_By,
95 p_Last_Update_Login,
96 p_Created_By,
97 p_Creation_Date
98 );
99 OPEN C;
100 FETCH C INTO p_Row_Id;
101 IF (C%NOTFOUND) THEN
102 CLOSE C;
103 RAISE FND_API.G_EXC_ERROR ;
104 END IF;
105 CLOSE C;
106 --
107
108 --
109 IF FND_API.To_Boolean ( p_commit ) THEN
110 COMMIT WORK;
111 END iF;
112 --
113 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
114 p_data => p_msg_data );
115 --
116 EXCEPTION
117 --
118 WHEN FND_API.G_EXC_ERROR THEN
119 --
120 ROLLBACK TO Insert_Row_Pvt ;
121 p_return_status := FND_API.G_RET_STS_ERROR;
122 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
123 p_data => p_msg_data );
124 --
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 --
127 ROLLBACK TO Insert_Row_Pvt ;
128 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
130 p_data => p_msg_data );
131 --
132 WHEN OTHERS THEN
133 --
134 ROLLBACK TO Insert_Row_Pvt ;
135 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 --
137 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
138 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
139 l_api_name);
140 END if;
141 --
142 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
143 p_data => p_msg_data );
144 --
145 END Insert_Row;
146 /*-------------------------------------------------------------------------*/
147
148
149
150 /*==========================================================================+
151 | PROCEDURE Lock_Row |
152 +==========================================================================*/
153
154 PROCEDURE Lock_Row
155 (
156 p_api_version IN NUMBER,
157 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
158 p_commit IN VARCHAR2 := FND_API.G_FALSE,
159 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
160 p_return_status OUT NOCOPY VARCHAR2,
161 p_msg_count OUT NOCOPY NUMBER,
162 p_msg_data OUT NOCOPY VARCHAR2,
163 --
164 p_Row_Id IN VARCHAR2,
165 p_Distribution_Id IN NUMBER,
166 p_Distribution_Rule_Id IN NUMBER,
167 p_Worksheet_Id IN NUMBER,
168 p_Distribution_Date IN DATE,
169 p_Distributed_Flag IN VARCHAR2,
170 --
171 p_row_locked OUT NOCOPY VARCHAR2
172 )
173 IS
174 --
175 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
176 l_api_version CONSTANT NUMBER := 1.0;
177 --
178 Counter NUMBER;
179 CURSOR C IS
180 SELECT Distribution_Id,
181 Distribution_Rule_Id,
182 Worksheet_Id,
183 Distribution_Date,
184 Distributed_Flag
185 FROM psb_ws_distributions
186 WHERE rowid = p_Row_Id
187 FOR UPDATE of Distribution_Id NOWAIT;
188 Recinfo C%ROWTYPE;
189
190 BEGIN
191 --
192 SAVEPOINT Lock_Row_Pvt ;
193 --
194 IF NOT FND_API.Compatible_API_Call ( l_api_version,
195 p_api_version,
196 l_api_name,
197 G_PKG_NAME )
198 THEN
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
200 END IF;
201 --
202
203 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
204 FND_MSG_PUB.initialize ;
205 END IF;
206 --
207 p_return_status := FND_API.G_RET_STS_SUCCESS ;
208 p_row_locked := FND_API.G_TRUE ;
209 --
210 OPEN C;
211 --
212 FETCH C INTO Recinfo;
213 IF (C%NOTFOUND) then
214 CLOSE C;
215 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
216 FND_MSG_PUB.Add;
217 RAISE FND_API.G_EXC_ERROR ;
218 END IF;
219 CLOSE C;
220 IF
221 (
222 ( Recinfo.distribution_id = p_distribution_id )
223
224 AND ( Recinfo.distribution_rule_id = p_distribution_rule_id )
225
226 AND ( Recinfo.worksheet_id = p_worksheet_id )
227
228 AND ( (Recinfo.distribution_date = p_distribution_date)
229 OR ( (Recinfo.distribution_date IS NULL)
230 AND (p_distribution_date IS NULL)))
231
232 AND ( (Recinfo.distributed_flag = p_distributed_flag)
233 OR ( (Recinfo.distributed_flag IS NULL)
234 AND (p_distributed_flag IS NULL)))
235 )
236 THEN
237 NULL ;
238 ELSE
239 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED') ;
240 FND_MSG_PUB.Add ;
241 RAISE FND_API.G_EXC_ERROR ;
242 END IF;
243
244 --
245 IF FND_API.To_Boolean ( p_commit ) THEN
246 COMMIT WORK;
247 END iF;
248 --
249 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
250 p_data => p_msg_data );
251 --
252 EXCEPTION
253 --
254 WHEN App_Exception.Record_Lock_Exception THEN
255 --
256 ROLLBACK TO Lock_Row_Pvt ;
257 p_row_locked := FND_API.G_FALSE ;
258 p_return_status := FND_API.G_RET_STS_ERROR ;
259 --
260 WHEN FND_API.G_EXC_ERROR THEN
261 --
262 ROLLBACK TO Lock_Row_Pvt ;
263 p_return_status := FND_API.G_RET_STS_ERROR;
264 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
265 p_data => p_msg_data );
266 --
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 --
269 ROLLBACK TO Lock_Row_Pvt ;
270 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
272 p_data => p_msg_data );
273 --
274 WHEN OTHERS THEN
275 --
276 ROLLBACK TO Lock_Row_Pvt ;
277 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 --
279 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
280 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
281 l_api_name);
282 END if;
283 --
284 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
285 p_data => p_msg_data );
286 --
287 END Lock_Row;
288 /* ----------------------------------------------------------------------- */
289
290
291
292
293 /*==========================================================================+
294 | PROCEDURE Update_Row |
295 +==========================================================================*/
296
297 PROCEDURE Update_Row
298 (
299 p_api_version IN NUMBER,
300 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
301 p_commit IN VARCHAR2 := FND_API.G_FALSE,
302 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
303 p_return_status OUT NOCOPY VARCHAR2,
304 p_msg_count OUT NOCOPY NUMBER,
305 p_msg_data OUT NOCOPY VARCHAR2,
306 --
307 p_Row_Id IN VARCHAR2,
308 p_Distribution_Id IN NUMBER,
309 p_Distribution_Rule_Id IN NUMBER,
310 p_Worksheet_Id IN NUMBER,
311 p_Distribution_Date IN DATE,
312 p_Distributed_Flag IN VARCHAR2,
313 p_Last_Update_Date IN DATE,
314 p_Last_Updated_By IN NUMBER,
315 p_Last_Update_Login IN NUMBER
316 )
317 IS
318 --
319 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
320 l_api_version CONSTANT NUMBER := 1.0;
321 --
322 BEGIN
323 --
324 SAVEPOINT Update_Row_Pvt ;
325 --
326 IF NOT FND_API.Compatible_API_Call ( l_api_version,
327 p_api_version,
328 l_api_name,
329 G_PKG_NAME )
330 THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
332 END IF;
333 --
334
335 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
336 FND_MSG_PUB.initialize ;
337 END IF;
338 --
339 p_return_status := FND_API.G_RET_STS_SUCCESS ;
340 --
341
342 UPDATE psb_ws_distributions
343 SET
344 distribution_id = p_distribution_id,
345 distribution_rule_id = p_distribution_rule_id,
346 worksheet_id = p_worksheet_id,
347 distribution_date = p_distribution_date,
348 distributed_flag = p_distributed_flag,
349 last_update_date = p_Last_Update_Date,
350 last_updated_by = p_Last_Updated_By,
351 last_update_login = p_Last_Update_Login
352 WHERE rowid = p_Row_Id;
353
354 IF (SQL%NOTFOUND) THEN
355 RAISE NO_DATA_FOUND ;
356 END IF;
357
358 --
359 IF FND_API.To_Boolean ( p_commit ) THEN
360 COMMIT WORK;
361 END iF;
362 --
363 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
364 p_data => p_msg_data );
365 --
366 EXCEPTION
367 --
368 WHEN FND_API.G_EXC_ERROR THEN
369 --
370 ROLLBACK TO Update_Row_Pvt ;
371 p_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
373 p_data => p_msg_data );
374 --
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376 --
377 ROLLBACK TO Update_Row_Pvt ;
378 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
380 p_data => p_msg_data );
381 --
382 WHEN OTHERS THEN
383 --
384 ROLLBACK TO Update_Row_Pvt ;
385 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 --
387 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
388 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
389 l_api_name);
390 END if;
391 --
392 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
393 p_data => p_msg_data );
394 --
395 END Update_Row;
396 /* ----------------------------------------------------------------------- */
397
398
399
400
401 /*==========================================================================+
402 | PROCEDURE Delete_Row |
403 +==========================================================================*/
404
405 PROCEDURE Delete_Row
406 (
407 p_api_version IN NUMBER,
408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
409 p_commit IN VARCHAR2 := FND_API.G_FALSE,
410 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
411 p_return_status OUT NOCOPY VARCHAR2,
412 p_msg_count OUT NOCOPY NUMBER,
413 p_msg_data OUT NOCOPY VARCHAR2,
414 --
415 p_Row_Id IN VARCHAR2
416 )
417 IS
418 --
419 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
420 l_api_version CONSTANT NUMBER := 1.0;
421 --
422 l_distribution_rule_id
423 psb_ws_distributions.distribution_rule_id%TYPE;
424 --
425 BEGIN
426 --
427 SAVEPOINT Delete_Row_Pvt ;
428 --
429 IF NOT FND_API.Compatible_API_Call ( l_api_version,
430 p_api_version,
431 l_api_name,
432 G_PKG_NAME )
433 THEN
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
435 END IF;
436 --
437
438 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
439 FND_MSG_PUB.initialize ;
440 END IF;
441 --
442 p_return_status := FND_API.G_RET_STS_SUCCESS ;
443 --
444
445 --
446 -- Deleting the record in psb_ws_distributions.
447 --
448 DELETE psb_ws_distributions
449 WHERE rowid = p_Row_Id;
450
451 IF (SQL%NOTFOUND) THEN
452 RAISE NO_DATA_FOUND ;
453 END IF;
454
455 --
456 IF FND_API.To_Boolean ( p_commit ) THEN
457 COMMIT WORK;
458 END iF;
459 --
460 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
461 p_data => p_msg_data );
462
463 EXCEPTION
464 --
465 WHEN FND_API.G_EXC_ERROR THEN
466 --
467 ROLLBACK TO Delete_Row_Pvt ;
468 p_return_status := FND_API.G_RET_STS_ERROR;
469 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
470 p_data => p_msg_data );
471 --
472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
473 --
474 ROLLBACK TO Delete_Row_Pvt ;
475 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
477 p_data => p_msg_data );
478 --
479 WHEN OTHERS THEN
480 --
481 ROLLBACK TO Delete_Row_Pvt ;
482 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483 --
484 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
485 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
486 l_api_name);
487 END if;
488 --
489 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
490 p_data => p_msg_data );
491 --
492 END Delete_Row;
493 /* ----------------------------------------------------------------------- */
494
495
496 END PSB_WS_Distributions_PVT;