[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_MC_DET_PF_PKG
Source
1 PACKAGE BODY IGC_CC_MC_DET_PF_PKG as
2 /* $Header: IGCCMCDB.pls 120.3.12000000.1 2007/08/20 12:12:56 mbremkum ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_MC_DET_PF_PKG';
5 g_debug_flag VARCHAR2(1) := 'N' ;
6
7 /* ================================================================================
8 PROCEDURE Insert_Row
9 ===============================================================================*/
10
11 PROCEDURE Insert_Row(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 X_return_status OUT NOCOPY VARCHAR2,
17 X_msg_count OUT NOCOPY NUMBER,
18 X_msg_data OUT NOCOPY VARCHAR2,
19 p_Rowid IN OUT NOCOPY VARCHAR2,
20 p_CC_DET_PF_Line_Id IGC_CC_MC_DET_PF.CC_DET_PF_Line_Id%TYPE,
21 p_Set_Of_Books_Id IGC_CC_MC_DET_PF.Set_Of_Books_Id%TYPE,
22 p_CC_DET_PF_Func_Amt IGC_CC_MC_DET_PF.CC_DET_PF_Func_Amt%TYPE,
23 p_CC_DET_PF_Encmbrnc_Amt IGC_CC_MC_DET_PF.CC_DET_PF_Encmbrnc_Amt%TYPE,
24 p_Conversion_Type IGC_CC_MC_DET_PF.Conversion_Type%TYPE,
25 p_Conversion_Date IGC_CC_MC_DET_PF.Conversion_Date%TYPE,
26 p_Conversion_Rate IGC_CC_MC_DET_PF.Conversion_Rate%TYPE
27
28 )
29
30 IS
31
32 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
33 l_api_version CONSTANT NUMBER := 1.0;
34
35 CURSOR C IS SELECT Rowid FROM IGC_CC_MC_DET_PF
36 WHERE CC_DET_PF_Line_Id = p_CC_DET_PF_Line_Id;
37
38 BEGIN
39
40 SAVEPOINT Insert_Row_Pvt ;
41
42 IF NOT FND_API.Compatible_API_Call ( l_api_version,
43 p_api_version,
44 l_api_name,
45 G_PKG_NAME )
46 THEN
47 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
48 END IF;
49
50
51 IF FND_API.to_Boolean (p_init_msg_list ) THEN
52 FND_MSG_PUB.initialize ;
53 END IF;
54
55 X_return_status := FND_API.G_RET_STS_SUCCESS ;
56
57 INSERT INTO IGC_CC_MC_DET_PF (
58 CC_DET_PF_Line_Id,
59 Set_Of_Books_Id,
60 CC_DET_PF_Func_Amt,
61 CC_DET_PF_Encmbrnc_Amt,
62 Conversion_Type,
63 Conversion_Date,
64 Conversion_Rate
65 ) VALUES (
66 p_CC_DET_PF_Line_Id,
67 p_Set_Of_Books_Id,
68 p_CC_DET_PF_Func_Amt,
69 p_CC_DET_PF_Encmbrnc_Amt,
70 p_Conversion_Type,
71 p_Conversion_Date,
72 p_Conversion_Rate
73 );
74
75 OPEN C;
76 FETCH C INTO p_Rowid;
77 if (C%NOTFOUND) then
78 CLOSE C;
79 RAISE FND_API.G_EXC_ERROR ;
80 end if;
81 CLOSE C;
82
83 IF FND_API.To_Boolean ( p_commit ) THEN
84 COMMIT WORK;
85 END iF;
86
87 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
88 p_data => X_msg_data );
89
90 EXCEPTION
91
92 WHEN FND_API.G_EXC_ERROR THEN
93
94 ROLLBACK TO Insert_Row_Pvt ;
95 X_return_status := FND_API.G_RET_STS_ERROR;
96 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
97 p_data => X_msg_data );
98
99 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
100
101 ROLLBACK TO Insert_Row_Pvt ;
102 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
103 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
104 p_data => X_msg_data );
105
106 WHEN OTHERS THEN
107
108 ROLLBACK TO Insert_Row_Pvt ;
109 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110
111 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
112 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
113 l_api_name);
114 END if;
115
116 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
117 p_data => X_msg_data );
118
119 END Insert_Row;
120
121 /* ================================================================================
122 PROCEDURE Lock_Row
123 ===============================================================================*/
124
125 PROCEDURE Lock_Row(
126 p_api_version IN NUMBER,
127 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
128 p_commit IN VARCHAR2 := FND_API.G_FALSE,
129 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
130 X_return_status OUT NOCOPY VARCHAR2,
131 X_msg_count OUT NOCOPY NUMBER,
132 X_msg_data OUT NOCOPY VARCHAR2,
133 p_Rowid IN OUT NOCOPY VARCHAR2,
134 p_CC_DET_PF_Line_Id IGC_CC_MC_DET_PF.CC_DET_PF_Line_Id%TYPE,
135 p_Set_Of_Books_Id IGC_CC_MC_DET_PF.Set_Of_Books_Id%TYPE,
136 p_CC_DET_PF_Func_Amt IGC_CC_MC_DET_PF.CC_DET_PF_Func_Amt%TYPE,
137 p_CC_DET_PF_Encmbrnc_Amt IGC_CC_MC_DET_PF.CC_DET_PF_Encmbrnc_Amt%TYPE,
138 p_Conversion_Type IGC_CC_MC_DET_PF.Conversion_Type%TYPE,
139 p_Conversion_Date IGC_CC_MC_DET_PF.Conversion_Date%TYPE,
140 p_Conversion_Rate IGC_CC_MC_DET_PF.Conversion_Rate%TYPE,
141 X_row_locked OUT NOCOPY VARCHAR2
142 ) IS
143 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
144 l_api_version CONSTANT NUMBER := 1.0;
145 Counter NUMBER;
146
147 CURSOR C IS
148 SELECT *
149 FROM IGC_CC_MC_DET_PF
150 WHERE rowid = p_Rowid
151 FOR UPDATE of CC_DET_PF_Line_Id NOWAIT;
152 Recinfo C%ROWTYPE;
153 BEGIN
154
155 SAVEPOINT Lock_Row_Pvt ;
156
157 IF NOT FND_API.Compatible_API_Call ( l_api_version,
158 p_api_version,
159 l_api_name,
160 G_PKG_NAME )
161 THEN
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
163 END IF;
164
165
166 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
167 FND_MSG_PUB.initialize ;
168 END IF;
169
170 X_return_status := FND_API.G_RET_STS_SUCCESS ;
171 X_row_locked := FND_API.G_TRUE ;
172
173 OPEN C;
174
175 FETCH C INTO Recinfo;
176 if (C%NOTFOUND) then
177 CLOSE C;
178 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
179 FND_MSG_PUB.Add;
180 RAISE FND_API.G_EXC_ERROR;
181 end if;
182 CLOSE C;
183 if (
184 (Recinfo.CC_DET_PF_Line_Id = p_CC_DET_PF_Line_Id)
185 AND ( (Recinfo.Set_Of_Books_Id = p_Set_Of_Books_Id)
186 OR ( (Recinfo.Set_Of_Books_Id IS NULL)
187 AND (p_Set_Of_Books_Id IS NULL)))
188 AND ( (Recinfo.CC_DET_PF_Func_Amt = p_CC_DET_PF_Func_Amt)
189 OR ( (Recinfo.CC_DET_PF_Func_Amt IS NULL)
190 AND (p_CC_DET_PF_Func_Amt IS NULL)))
191 AND ( (Recinfo.CC_DET_PF_Encmbrnc_Amt = p_CC_DET_PF_Encmbrnc_Amt)
192 OR ( (Recinfo.CC_DET_PF_Encmbrnc_Amt IS NULL)
193 AND (p_CC_DET_PF_Encmbrnc_Amt IS NULL)))
194 AND ( (Recinfo.Conversion_Type = p_Conversion_Type)
195 OR ( (Recinfo.Conversion_Type IS NULL)
196 AND (p_Conversion_Type IS NULL)))
197 AND ( (Recinfo.Conversion_Date = p_Conversion_Date)
198 OR ( (Recinfo.Conversion_Date IS NULL)
199 AND (p_Conversion_Date IS NULL)))
200 AND ( (Recinfo.Conversion_Rate = p_Conversion_Rate)
201 OR ( (Recinfo.Conversion_Rate IS NULL)
202 AND (p_Conversion_Rate IS NULL)))
203
204 ) then
205 null;
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 IF FND_API.To_Boolean ( p_commit ) THEN
213 COMMIT WORK;
214 END iF;
215
216 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
217 p_data => X_msg_data );
218
219 EXCEPTION
220
221 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
222
223 ROLLBACK TO Lock_Row_Pvt ;
224 X_row_locked := FND_API.G_FALSE;
225 X_return_status := FND_API.G_RET_STS_ERROR;
226 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
227 p_data => X_msg_data );
228
229 WHEN FND_API.G_EXC_ERROR THEN
230
231 ROLLBACK TO Lock_Row_Pvt ;
232 X_return_status := FND_API.G_RET_STS_ERROR;
233 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
234 p_data => X_msg_data );
235
236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
237
238 ROLLBACK TO Lock_Row_Pvt ;
239 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
241 p_data => X_msg_data );
242
243 WHEN OTHERS THEN
244
245 ROLLBACK TO Lock_Row_Pvt ;
246 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247
248 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
249 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
250 l_api_name);
251 END if;
252
253 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
254 p_data => X_msg_data );
255
256 END Lock_Row;
257 /* ================================================================================
258 PROCEDURE Update_Row
259 ===============================================================================*/
260
261 PROCEDURE Update_Row(
262 p_api_version IN NUMBER,
263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
264 p_commit IN VARCHAR2 := FND_API.G_FALSE,
265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
266 X_return_status OUT NOCOPY VARCHAR2,
267 X_msg_count OUT NOCOPY NUMBER,
268 X_msg_data OUT NOCOPY VARCHAR2,
269 p_Rowid IN OUT NOCOPY VARCHAR2,
270 p_CC_DET_PF_Line_Id IGC_CC_MC_DET_PF.CC_DET_PF_Line_Id%TYPE,
271 p_Set_Of_Books_Id IGC_CC_MC_DET_PF.Set_Of_Books_Id%TYPE,
272 p_CC_DET_PF_Func_Amt IGC_CC_MC_DET_PF.CC_DET_PF_Func_Amt%TYPE,
273 p_CC_DET_PF_Encmbrnc_Amt IGC_CC_MC_DET_PF.CC_DET_PF_Encmbrnc_Amt%TYPE,
274 p_Conversion_Type IGC_CC_MC_DET_PF.Conversion_Type%TYPE,
275 p_Conversion_Date IGC_CC_MC_DET_PF.Conversion_Date%TYPE,
276 p_Conversion_Rate IGC_CC_MC_DET_PF.Conversion_Rate%TYPE
277
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 X_return_status := FND_API.G_RET_STS_SUCCESS ;
301
302 UPDATE IGC_CC_MC_DET_PF
303 SET
304 CC_DET_PF_Line_Id = p_CC_DET_PF_Line_Id,
305 Set_Of_Books_Id = p_Set_Of_Books_Id,
306 CC_DET_PF_Func_Amt = p_CC_DET_PF_Func_Amt,
307 CC_DET_PF_Encmbrnc_Amt = p_CC_DET_PF_Encmbrnc_Amt,
308 Conversion_Type = p_Conversion_Type,
309 Conversion_Date = p_Conversion_Date,
310 Conversion_Rate = p_Conversion_Rate
311 WHERE rowid = p_Rowid;
312 if (SQL%NOTFOUND) then
313 Raise NO_DATA_FOUND;
314 end if;
315
316 IF FND_API.To_Boolean ( p_commit ) THEN
317 COMMIT WORK;
318 END iF;
319
320 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
321 p_data => X_msg_data );
322
323 EXCEPTION
324
325 WHEN FND_API.G_EXC_ERROR THEN
326
327 ROLLBACK TO Update_Row_Pvt ;
328 X_return_status := FND_API.G_RET_STS_ERROR;
329 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
330 p_data => X_msg_data );
331
332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
333
334 ROLLBACK TO Update_Row_Pvt ;
335 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
337 p_data => X_msg_data );
338
339 WHEN OTHERS THEN
340
341 ROLLBACK TO Update_Row_Pvt ;
342 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343
344 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
345 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
346 l_api_name);
347 END if;
348
349 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
350 p_data => X_msg_data );
351
352 END Update_Row;
353
354 /* ================================================================================
355 PROCEDURE Delete_Row
356 ===============================================================================*/
357
358 PROCEDURE Delete_Row(
359 p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361 p_commit IN VARCHAR2 := FND_API.G_FALSE,
362 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
363 X_return_status OUT NOCOPY VARCHAR2,
364 X_msg_count OUT NOCOPY NUMBER,
365 X_msg_data OUT NOCOPY VARCHAR2,
366 p_Rowid IN OUT NOCOPY VARCHAR2
367
368 ) IS
369
370 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
371 l_api_version CONSTANT NUMBER := 1.0;
372 l_return_status VARCHAR2(1) ;
373 l_msg_count NUMBER ;
374 l_msg_data VARCHAR2(2000) ;
375
376 BEGIN
377
378 SAVEPOINT Delete_Row_Pvt ;
379
380 IF NOT FND_API.Compatible_API_Call ( l_api_version,
381 p_api_version,
382 l_api_name,
383 G_PKG_NAME )
384 THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
386 END IF;
387
388
389 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
390 FND_MSG_PUB.initialize ;
391 END IF ;
392
393 X_return_status := FND_API.G_RET_STS_SUCCESS ;
394
395 DELETE FROM IGC_CC_MC_DET_PF
396 WHERE rowid = p_Rowid;
397
398 if (SQL%NOTFOUND) then
399 Raise NO_DATA_FOUND;
400 end if;
401
402 IF FND_API.To_Boolean ( p_commit ) THEN
403 COMMIT WORK;
404 END iF;
405
406 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
407 p_data => X_msg_data );
408
409 EXCEPTION
410
411 WHEN FND_API.G_EXC_ERROR THEN
412
413 ROLLBACK TO Delete_Row_Pvt ;
414 X_return_status := FND_API.G_RET_STS_ERROR;
415 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
416 p_data => X_msg_data );
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419
420 ROLLBACK TO Delete_Row_Pvt ;
421 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
423 p_data => X_msg_data );
424
425 WHEN OTHERS THEN
426
427 ROLLBACK TO Delete_Row_Pvt ;
428 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429
430 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
431 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
432 l_api_name);
433 END if;
434
435 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
436 p_data => X_msg_data );
437
438
439 END Delete_Row;
440
441
442
443
444 END IGC_CC_MC_DET_PF_PKG;