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