[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_PERIODS_PKG
Source
1 PACKAGE BODY IGC_CC_PERIODS_PKG AS
2 /*$Header: IGCCCCPB.pls 120.3.12000000.2 2007/09/26 17:07:55 smannava ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_PERIODS_PKG';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_flag VARCHAR2(1) := 'N' ;
8
9
10 /*=======================================================================+
11 | PROCEDURE Insert_Row |
12 +=======================================================================*/
13 PROCEDURE Insert_Row
14 (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
19 p_return_status OUT NOCOPY VARCHAR2,
20 p_msg_count OUT NOCOPY NUMBER,
21 p_msg_data OUT NOCOPY VARCHAR2,
22
23 p_row_id IN OUT NOCOPY VARCHAR2,
24 p_org_id NUMBER,
25 p_period_set_name VARCHAR2,
26 p_period_name VARCHAR2,
27 p_cc_period_status VARCHAR2,
28 p_last_update_date DATE,
29 p_last_updated_by NUMBER,
30 p_last_update_login NUMBER,
31 p_created_by NUMBER,
32 p_creation_date DATE
33 )
34 IS
35
36 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
37 l_api_version CONSTANT NUMBER := 1.0;
38
39 CURSOR C IS SELECT ROWID FROM igc_cc_periods_all
40 WHERE org_id = p_org_id
41 AND period_set_name = p_period_set_name
42 AND period_name = p_period_name;
43 BEGIN
44
45 SAVEPOINT Insert_Row_Pvt ;
46
47 IF NOT FND_API.Compatible_API_Call ( l_api_version,
48 p_api_version,
49 l_api_name,
50 G_PKG_NAME )
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
53 END IF;
54
55
56 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
57 FND_MSG_PUB.initialize ;
58 END IF;
59
60 p_return_status := FND_API.G_RET_STS_SUCCESS ;
61
62 INSERT INTO igc_cc_periods_all
63 (
64 org_id,
65 period_set_name,
66 period_name,
67 cc_period_status,
68 last_update_date,
69 last_updated_by,
70 last_update_login,
71 created_by,
72 creation_date
73 )
74 VALUES
75 (
76 p_org_id,
77 p_period_set_name,
78 p_period_name,
79 p_cc_period_status,
80 p_last_update_date,
81 p_last_updated_by,
82 p_last_update_login,
83 p_created_by,
84 p_creation_date
85 );
86 OPEN C;
87 FETCH C INTO p_row_id;
88 IF (C%NOTFOUND) THEN
89 CLOSE C;
90 RAISE FND_API.G_EXC_ERROR ;
91 END IF;
92 CLOSE C;
93
94
95 IF FND_API.To_Boolean ( p_commit ) THEN
96 COMMIT WORK;
97 END iF;
98
99 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
100 p_data => p_msg_data );
101
102 EXCEPTION
103
104 WHEN FND_API.G_EXC_ERROR THEN
105
106 ROLLBACK TO Insert_Row_Pvt ;
107 p_return_status := FND_API.G_RET_STS_ERROR;
108 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
109 p_data => p_msg_data );
110
111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
112
113 ROLLBACK TO Insert_Row_Pvt ;
114 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
116 p_data => p_msg_data );
117
118 WHEN OTHERS THEN
119
120 ROLLBACK TO Insert_Row_Pvt ;
121 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122
123 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
124 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
125 l_api_name);
126 END if;
127
128 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
129 p_data => p_msg_data );
130
131 END Insert_Row;
132 /*-------------------------------------------------------------------------*/
133
134
135
136 /*==========================================================================+
137 | PROCEDURE Lock_Row |
138 +==========================================================================*/
139 PROCEDURE Lock_Row
140 (
141 p_api_version IN NUMBER,
142 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
143 p_commit IN VARCHAR2 := FND_API.G_FALSE,
144 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
145 p_return_status OUT NOCOPY VARCHAR2,
146 p_msg_count OUT NOCOPY NUMBER,
147 p_msg_data OUT NOCOPY VARCHAR2,
148
149 p_row_id IN OUT NOCOPY VARCHAR2,
150 p_org_id NUMBER,
151 p_period_set_name VARCHAR2,
152 p_period_name VARCHAR2,
153 p_cc_period_status VARCHAR2,
154
155 p_row_locked OUT NOCOPY VARCHAR2
156
157 )
158 IS
159
160 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
161 l_api_version CONSTANT NUMBER := 1.0;
162
163 Counter NUMBER;
164 CURSOR C IS
165 SELECT * FROM igc_cc_periods_all
166 WHERE rowid = p_row_id
167 FOR UPDATE NOWAIT;
168 Recinfo C%ROWTYPE;
169
170 BEGIN
171
172 SAVEPOINT Lock_Row_Pvt ;
173
174 IF NOT FND_API.Compatible_API_Call ( l_api_version,
175 p_api_version,
176 l_api_name,
177 G_PKG_NAME )
178 THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
180 END IF;
181
182
183 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
184 FND_MSG_PUB.initialize ;
185 END IF;
186
187 p_return_status := FND_API.G_RET_STS_SUCCESS ;
188 p_row_locked := FND_API.G_TRUE ;
189
190 OPEN C;
191
192 FETCH C INTO Recinfo;
193 IF (C%NOTFOUND) then
194 CLOSE C;
195 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
196 FND_MSG_PUB.Add;
197 RAISE FND_API.G_EXC_ERROR ;
198 END IF;
199 CLOSE C;
200 IF ( (Recinfo.org_id = p_org_id)
201 AND (Recinfo.period_set_name = p_period_set_name)
202 AND (Recinfo.period_name = p_period_name)
203 AND ( (Recinfo.cc_period_status = p_cc_period_status)
204 OR ( (Recinfo.cc_period_status IS NULL)
205 AND (p_cc_period_status IS NULL)))
206 ) THEN
207 Null;
208 ELSE
209 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
210 FND_MSG_PUB.Add;
211 RAISE FND_API.G_EXC_ERROR ;
212 END IF;
213
214
215 IF FND_API.To_Boolean ( p_commit ) THEN
216 COMMIT WORK;
217 END iF;
218
219 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
220 p_data => p_msg_data );
221
222 EXCEPTION
223
224 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
225
226 ROLLBACK TO Lock_Row_Pvt ;
227 p_row_locked := FND_API.G_FALSE;
228 p_return_status := FND_API.G_RET_STS_ERROR;
229 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
230 p_data => p_msg_data );
231
232 WHEN FND_API.G_EXC_ERROR THEN
233
234 ROLLBACK TO Lock_Row_Pvt ;
235 p_return_status := FND_API.G_RET_STS_ERROR;
236 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
237 p_data => p_msg_data );
238
239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
240
241 ROLLBACK TO Lock_Row_Pvt ;
242 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
244 p_data => p_msg_data );
245
246 WHEN OTHERS THEN
247
248 ROLLBACK TO Lock_Row_Pvt ;
249 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250
251 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
252 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
253 l_api_name);
254 END if;
255
256 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
257 p_data => p_msg_data );
258
259 END Lock_Row;
260 /* ----------------------------------------------------------------------- */
261
262
263
264
265 /*==========================================================================+
266 | PROCEDURE Update_Row |
267 +==========================================================================*/
268 PROCEDURE Update_Row
269 (
270 p_api_version IN NUMBER,
271 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
272 p_commit IN VARCHAR2 := FND_API.G_FALSE,
273 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
274 p_return_status OUT NOCOPY VARCHAR2,
275 p_msg_count OUT NOCOPY NUMBER,
276 p_msg_data OUT NOCOPY VARCHAR2,
277
278 p_row_id IN OUT NOCOPY VARCHAR2,
279 p_org_id NUMBER,
280 p_period_set_name VARCHAR2,
281 p_period_name VARCHAR2,
282 p_cc_period_status VARCHAR2,
283 p_last_update_date DATE,
284 p_last_updated_by NUMBER,
285 p_last_update_login NUMBER
286 )
287 IS
288
289 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
290 l_api_version CONSTANT NUMBER := 1.0;
291
292 BEGIN
293
294 SAVEPOINT Update_Row_Pvt ;
295
296 IF NOT FND_API.Compatible_API_Call ( l_api_version,
297 p_api_version,
298 l_api_name,
299 G_PKG_NAME )
300 THEN
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
302 END IF;
303
304
305 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
306 FND_MSG_PUB.initialize ;
307 END IF;
308
309 p_return_status := FND_API.G_RET_STS_SUCCESS ;
310
311
312 UPDATE igc_cc_periods_all
313 SET
314 org_id = p_org_id,
315 cc_period_status = p_cc_period_status,
316 last_update_date = p_last_update_date ,
317 last_updated_by = p_last_updated_by ,
318 last_update_login = p_last_update_login
319 WHERE rowid = p_row_id;
320
321 IF (SQL%NOTFOUND) THEN
322 RAISE NO_DATA_FOUND ;
323 END IF;
324
325
326 IF FND_API.To_Boolean ( p_commit ) THEN
327 COMMIT WORK;
328 END iF;
329
330 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
331 p_data => p_msg_data );
332
333 EXCEPTION
334
335 WHEN FND_API.G_EXC_ERROR THEN
336
337 ROLLBACK TO Update_Row_Pvt ;
338 p_return_status := FND_API.G_RET_STS_ERROR;
339 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
340 p_data => p_msg_data );
341
342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
343
344 ROLLBACK TO Update_Row_Pvt ;
345 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
347 p_data => p_msg_data );
348
349 WHEN OTHERS THEN
350
351 ROLLBACK TO Update_Row_Pvt ;
352 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353
354 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
355 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
356 l_api_name);
357 END if;
358
359 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
360 p_data => p_msg_data );
361
362 END Update_Row;
363 /* ----------------------------------------------------------------------- */
364
365
366
367
368 /*==========================================================================+
369 | PROCEDURE Delete_Row |
370 +==========================================================================*/
371 PROCEDURE Delete_Row
372 (
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
375 p_commit IN VARCHAR2 := FND_API.G_FALSE,
376 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
377 p_return_status OUT NOCOPY VARCHAR2,
378 p_msg_count OUT NOCOPY NUMBER,
379 p_msg_data OUT NOCOPY VARCHAR2,
380
381 p_row_id IN VARCHAR2
382 )
383 IS
384
385 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
386 l_api_version CONSTANT NUMBER := 1.0;
387
388 l_return_status VARCHAR2(1) ;
389 l_msg_count NUMBER ;
390 l_msg_data VARCHAR2(2000) ;
391
392 BEGIN
393
394 SAVEPOINT Delete_Row_Pvt ;
395
396 IF NOT FND_API.Compatible_API_Call ( l_api_version,
397 p_api_version,
398 l_api_name,
399 G_PKG_NAME )
400 THEN
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
402 END IF;
403
404
405 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
406 FND_MSG_PUB.initialize ;
407 END IF ;
408
409 p_return_status := FND_API.G_RET_STS_SUCCESS ;
410
411 -- Deleting the record in igc_cc_periods.
412
413 DELETE FROM igc_cc_periods_all
414 WHERE rowid = p_row_id;
415
416
417 IF (SQL%NOTFOUND) THEN
418 RAISE NO_DATA_FOUND ;
419 END IF;
420
421
422 IF FND_API.To_Boolean ( p_commit ) THEN
423 COMMIT WORK;
424 END iF;
425
426 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
427 p_data => p_msg_data );
428
429 EXCEPTION
430
431 WHEN FND_API.G_EXC_ERROR THEN
432
433 ROLLBACK TO Delete_Row_Pvt ;
434 p_return_status := FND_API.G_RET_STS_ERROR;
435 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
436 p_data => p_msg_data );
437
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439
440 ROLLBACK TO Delete_Row_Pvt ;
441 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
442 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
443 p_data => p_msg_data );
444
445 WHEN OTHERS THEN
446
447 ROLLBACK TO Delete_Row_Pvt ;
448 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449
450 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
451 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
452 l_api_name);
453 END if;
454
455 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
456 p_data => p_msg_data );
457
458 END Delete_Row;
459
460
461 /*==========================================================================+
462 | PROCEDURE Check_Unique |
463 +==========================================================================*/
464 PROCEDURE Check_Unique
465 (
466 p_api_version IN NUMBER,
467 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
468 p_commit IN VARCHAR2 := FND_API.G_FALSE,
469 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
470 p_return_status OUT NOCOPY VARCHAR2,
471 p_msg_count OUT NOCOPY NUMBER,
472 p_msg_data OUT NOCOPY VARCHAR2,
473
474 p_row_id IN OUT NOCOPY VARCHAR2,
475 p_org_id NUMBER,
476 p_period_set_name VARCHAR2,
477 p_period_name VARCHAR2,
478
479 p_return_value IN OUT NOCOPY VARCHAR2
480 )
481 IS
482
483 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
484 l_api_version CONSTANT NUMBER := 1.0;
485
486 l_tmp VARCHAR2(1);
487
488 CURSOR c IS
489 SELECT '1'
490 FROM igc_cc_periods_all
491 WHERE org_id = p_org_id
492 AND period_set_name = p_period_set_name
493 AND period_name = p_period_name
494 AND (
495 p_row_id IS NULL
496 OR
497 rowid <> p_row_id
498 );
499
500 BEGIN
501
502 SAVEPOINT Check_Unique_Pvt ;
503
504 IF NOT FND_API.Compatible_API_Call ( l_api_version,
505 p_api_version,
506 l_api_name,
507 G_PKG_NAME )
508 THEN
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
510 END IF;
511
512
513 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
514 FND_MSG_PUB.initialize ;
515 END IF;
516
517 p_return_status := FND_API.G_RET_STS_SUCCESS ;
518
519 -- Checking the igc_cc_periods table for uniqueness.
520 OPEN c;
521 FETCH c INTO l_tmp;
522
523 -- p_Return_Value specifies whether unique value exists or not.
524 IF l_tmp IS NULL THEN
525 p_Return_Value := 'FALSE';
526 ELSE
527 p_Return_Value := 'TRUE';
528 END IF;
529
530 CLOSE c;
531
532 IF FND_API.To_Boolean ( p_commit ) THEN
533 COMMIT WORK;
534 END iF;
535
536 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
537 p_data => p_msg_data );
538
539 EXCEPTION
540
541 WHEN FND_API.G_EXC_ERROR THEN
542
543 ROLLBACK TO Check_Unique_Pvt ;
544 p_return_status := FND_API.G_RET_STS_ERROR;
545 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
546 p_data => p_msg_data );
547
548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
549
550 ROLLBACK TO Check_Unique_Pvt ;
551 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
553 p_data => p_msg_data );
554
555 WHEN OTHERS THEN
556
557 ROLLBACK TO Check_Unique_Pvt ;
558 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559
560 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
561 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
562 l_api_name);
563 END if;
564
565 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
566 p_data => p_msg_data );
567
568 END Check_Unique;
569 /* ----------------------------------------------------------------------- */
570
571 END IGC_CC_PERIODS_PKG;