[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_MC_DET_PF_HST_PKG
Source
1 PACKAGE BODY IGC_CC_MC_DET_PF_HST_PKG as
2 /* $Header: IGCCMHDB.pls 120.3.12000000.1 2007/08/20 12:13:16 mbremkum ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_MC_DET_PF_HST_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_HISTORY.CC_DET_PF_Line_Id%TYPE,
21 p_Set_Of_Books_Id IGC_CC_MC_DET_PF_HISTORY.Set_Of_Books_Id%TYPE,
22 p_CC_DET_PF_Func_Amt IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Func_Amt%TYPE,
23 p_CC_DET_PF_Encmbrnc_Amt IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Encmbrnc_Amt%TYPE,
24 p_CC_DET_PF_version_num IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_version_num%TYPE,
25 p_CC_DET_PF_version_action IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_version_action%TYPE,
26 p_Conversion_Type IGC_CC_MC_DET_PF_HISTORY.Conversion_Type%TYPE,
27 p_Conversion_Date IGC_CC_MC_DET_PF_HISTORY.Conversion_Date%TYPE,
28 p_Conversion_Rate IGC_CC_MC_DET_PF_HISTORY.Conversion_Rate%TYPE
29
30 )
31
32 IS
33
34 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
35 l_api_version CONSTANT NUMBER := 1.0;
36
37 CURSOR c_det_pf_mrc_hst_rowid
38 IS SELECT Rowid
39 FROM IGC_CC_MC_DET_PF_HISTORY
40 WHERE CC_DET_PF_Line_Id = p_CC_DET_PF_Line_Id;
41
42 BEGIN
43
44 SAVEPOINT Insert_Row_Pvt ;
45 -- -----------------------------------------------------------------
46 -- Ensure that the version requested to be used is correct for
47 -- this API.
48 -- -----------------------------------------------------------------
49 IF NOT FND_API.Compatible_API_Call ( l_api_version,
50 p_api_version,
51 l_api_name,
52 G_PKG_NAME )
53 THEN
54 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
55 END IF;
56
57
58 IF FND_API.to_Boolean (p_init_msg_list ) THEN
59 FND_MSG_PUB.initialize ;
60 END IF;
61
62 X_return_status := FND_API.G_RET_STS_SUCCESS ;
63 -- -----------------------------------------------------------------
64 -- Insert the MRC DET PF line History record as requested.
65 -- -----------------------------------------------------------------
66 INSERT INTO IGC_CC_MC_DET_PF_HISTORY (
67 CC_DET_PF_Line_Id,
68 Set_Of_Books_Id,
69 CC_DET_PF_Func_Amt,
70 CC_DET_PF_Encmbrnc_Amt,
71 CC_DET_PF_version_num,
72 CC_DET_PF_version_action,
73 Conversion_Type,
74 Conversion_Date,
75 Conversion_Rate
76 ) VALUES (
77 p_CC_DET_PF_Line_Id,
78 p_Set_Of_Books_Id,
79 p_CC_DET_PF_Func_Amt,
80 p_CC_DET_PF_Encmbrnc_Amt,
81 p_CC_DET_PF_version_num,
82 p_CC_DET_PF_version_action,
83 p_Conversion_Type,
84 p_Conversion_Date,
85 p_Conversion_Rate
86 );
87 -- -------------------------------------------------------------------
88 -- Obtain the ROWID of the record that was just inserted to return
89 -- to the caller.
90 -- -------------------------------------------------------------------
91 OPEN c_det_pf_mrc_hst_rowid;
92 FETCH c_det_pf_mrc_hst_rowid
93 INTO p_Rowid;
94
95 if (c_det_pf_mrc_hst_rowid%NOTFOUND) then
96 CLOSE c_det_pf_mrc_hst_rowid;
97 RAISE FND_API.G_EXC_ERROR ;
98 end if;
99 CLOSE c_det_pf_mrc_hst_rowid;
100 -- -----------------------------------------------------------------
101 -- If the records are to be commited in this procedure then
102 -- commit the work now otherwise wait for the caller to do COMMIT.
103 -- -----------------------------------------------------------------
104 IF FND_API.To_Boolean ( p_commit ) THEN
105 COMMIT WORK;
106 END iF;
107
108 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
109 p_data => X_msg_data );
110 RETURN;
111
112 EXCEPTION
113
114 WHEN FND_API.G_EXC_ERROR THEN
115
116 ROLLBACK TO Insert_Row_Pvt ;
117 X_return_status := FND_API.G_RET_STS_ERROR;
118 IF (c_det_pf_mrc_hst_rowid%ISOPEN) THEN
119 CLOSE c_det_pf_mrc_hst_rowid;
120 END IF;
121
122 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
123 p_data => X_msg_data );
124
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126
127 ROLLBACK TO Insert_Row_Pvt ;
128 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 IF (c_det_pf_mrc_hst_rowid%ISOPEN) THEN
130 CLOSE c_det_pf_mrc_hst_rowid;
131 END IF;
132
133 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
134 p_data => X_msg_data );
135
136 WHEN OTHERS THEN
137
138 ROLLBACK TO Insert_Row_Pvt ;
139 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
140 IF (c_det_pf_mrc_hst_rowid%ISOPEN) THEN
141 CLOSE c_det_pf_mrc_hst_rowid;
142 END IF;
143 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
144 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
145 l_api_name);
146 END if;
147
148 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
149 p_data => X_msg_data );
150
151 END Insert_Row;
152
153 /* ================================================================================
154 PROCEDURE Lock_Row
155 ===============================================================================*/
156
157 PROCEDURE Lock_Row(
158 p_api_version IN NUMBER,
159 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
160 p_commit IN VARCHAR2 := FND_API.G_FALSE,
161 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
162 X_return_status OUT NOCOPY VARCHAR2,
163 X_msg_count OUT NOCOPY NUMBER,
164 X_msg_data OUT NOCOPY VARCHAR2,
165 p_Rowid IN OUT NOCOPY VARCHAR2,
166 p_CC_DET_PF_Line_Id IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Line_Id%TYPE,
167 p_Set_Of_Books_Id IGC_CC_MC_DET_PF_HISTORY.Set_Of_Books_Id%TYPE,
168 p_CC_DET_PF_Func_Amt IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Func_Amt%TYPE,
169 p_CC_DET_PF_Encmbrnc_Amt IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Encmbrnc_Amt%TYPE,
170 p_CC_DET_PF_version_num IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_version_num%TYPE,
171 p_CC_DET_PF_version_action IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_version_action%TYPE,
172 p_Conversion_Type IGC_CC_MC_DET_PF_HISTORY.Conversion_Type%TYPE,
173 p_Conversion_Date IGC_CC_MC_DET_PF_HISTORY.Conversion_Date%TYPE,
174 p_Conversion_Rate IGC_CC_MC_DET_PF_HISTORY.Conversion_Rate%TYPE,
175 X_row_locked OUT NOCOPY VARCHAR2
176 ) IS
177 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
178 l_api_version CONSTANT NUMBER := 1.0;
179 Counter NUMBER;
180
181 CURSOR C IS
182 SELECT *
183 FROM IGC_CC_MC_DET_PF_HISTORY
184 WHERE rowid = p_Rowid
185 FOR UPDATE of CC_DET_PF_Line_Id NOWAIT;
186 Recinfo C%ROWTYPE;
187 BEGIN
188
189 SAVEPOINT Lock_Row_Pvt ;
190
191 IF NOT FND_API.Compatible_API_Call ( l_api_version,
192 p_api_version,
193 l_api_name,
194 G_PKG_NAME )
195 THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
197 END IF;
198
199
200 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
201 FND_MSG_PUB.initialize ;
202 END IF;
203
204 X_return_status := FND_API.G_RET_STS_SUCCESS ;
205 X_row_locked := FND_API.G_TRUE ;
206
207 OPEN C;
208
209 FETCH C INTO Recinfo;
210 if (C%NOTFOUND) then
211 CLOSE C;
212 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
213 FND_MSG_PUB.Add;
214 RAISE FND_API.G_EXC_ERROR;
215 end if;
216 CLOSE C;
217 if (
218 (Recinfo.CC_DET_PF_Line_Id = p_CC_DET_PF_Line_Id)
219 AND ( (Recinfo.Set_Of_Books_Id = p_Set_Of_Books_Id)
220 OR ( (Recinfo.Set_Of_Books_Id IS NULL)
221 AND (p_Set_Of_Books_Id IS NULL)))
222 AND ( (Recinfo.CC_DET_PF_Func_Amt = p_CC_DET_PF_Func_Amt)
223 OR ( (Recinfo.CC_DET_PF_Func_Amt IS NULL)
224 AND (p_CC_DET_PF_Func_Amt IS NULL)))
225 AND ( (Recinfo.CC_DET_PF_Encmbrnc_Amt = p_CC_DET_PF_Encmbrnc_Amt)
226 OR ( (Recinfo.CC_DET_PF_Encmbrnc_Amt IS NULL)
227 AND (p_CC_DET_PF_Encmbrnc_Amt IS NULL)))
228 AND ( (Recinfo.CC_DET_PF_version_num = p_CC_DET_PF_version_num)
229 OR ( (Recinfo.CC_DET_PF_version_num IS NULL)
230 AND (p_CC_DET_PF_version_num IS NULL)))
231 AND ( (Recinfo.CC_DET_PF_version_action = p_CC_DET_PF_version_action)
232 OR ( (Recinfo.CC_DET_PF_version_action IS NULL)
233 AND (p_CC_DET_PF_version_action IS NULL)))
234 AND ( (Recinfo.Conversion_Type = p_Conversion_Type)
235 OR ( (Recinfo.Conversion_Type IS NULL)
236 AND (p_Conversion_Type IS NULL)))
237 AND ( (Recinfo.Conversion_Date = p_Conversion_Date)
238 OR ( (Recinfo.Conversion_Date IS NULL)
239 AND (p_Conversion_Date IS NULL)))
240 AND ( (Recinfo.Conversion_Rate = p_Conversion_Rate)
241 OR ( (Recinfo.Conversion_Rate IS NULL)
242 AND (p_Conversion_Rate IS NULL)))
243
244 ) then
245 null;
246 else
247 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
248 FND_MSG_PUB.Add;
249 RAISE FND_API.G_EXC_ERROR ;
250 end if;
251
252 IF FND_API.To_Boolean ( p_commit ) THEN
253 COMMIT WORK;
254 END iF;
255
256 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
257 p_data => X_msg_data );
258
259 EXCEPTION
260
261 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
262
263 ROLLBACK TO Lock_Row_Pvt ;
264 X_row_locked := FND_API.G_FALSE;
265 X_return_status := FND_API.G_RET_STS_ERROR;
266 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
267 p_data => X_msg_data );
268
269 WHEN FND_API.G_EXC_ERROR THEN
270
271 ROLLBACK TO Lock_Row_Pvt ;
272 X_return_status := FND_API.G_RET_STS_ERROR;
273 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
274 p_data => X_msg_data );
275
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277
278 ROLLBACK TO Lock_Row_Pvt ;
279 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
281 p_data => X_msg_data );
282
283 WHEN OTHERS THEN
284
285 ROLLBACK TO Lock_Row_Pvt ;
286 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287
288 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
289 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
290 l_api_name);
291 END if;
292
293 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
294 p_data => X_msg_data );
295
296 END Lock_Row;
297 /* ================================================================================
298 PROCEDURE Update_Row
299 ===============================================================================*/
300
301 PROCEDURE Update_Row(
302 p_api_version IN NUMBER,
303 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
304 p_commit IN VARCHAR2 := FND_API.G_FALSE,
305 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
306 X_return_status OUT NOCOPY VARCHAR2,
307 X_msg_count OUT NOCOPY NUMBER,
308 X_msg_data OUT NOCOPY VARCHAR2,
309 p_Rowid IN OUT NOCOPY VARCHAR2,
310 p_CC_DET_PF_Line_Id IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Line_Id%TYPE,
311 p_Set_Of_Books_Id IGC_CC_MC_DET_PF_HISTORY.Set_Of_Books_Id%TYPE,
312 p_CC_DET_PF_Func_Amt IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Func_Amt%TYPE,
313 p_CC_DET_PF_Encmbrnc_Amt IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_Encmbrnc_Amt%TYPE,
314 p_CC_DET_PF_version_num IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_version_num%TYPE,
315 p_CC_DET_PF_version_action IGC_CC_MC_DET_PF_HISTORY.CC_DET_PF_version_action%TYPE,
316 p_Conversion_Type IGC_CC_MC_DET_PF_HISTORY.Conversion_Type%TYPE,
317 p_Conversion_Date IGC_CC_MC_DET_PF_HISTORY.Conversion_Date%TYPE,
318 p_Conversion_Rate IGC_CC_MC_DET_PF_HISTORY.Conversion_Rate%TYPE
319
320 ) IS
321
322 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
323 l_api_version CONSTANT NUMBER := 1.0;
324
325 BEGIN
326
327 SAVEPOINT Update_Row_Pvt ;
328
329 IF NOT FND_API.Compatible_API_Call ( l_api_version,
330 p_api_version,
331 l_api_name,
332 G_PKG_NAME )
333 THEN
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
335 END IF;
336
337
338 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
339 FND_MSG_PUB.initialize ;
340 END IF;
341
342 X_return_status := FND_API.G_RET_STS_SUCCESS ;
343
344 UPDATE IGC_CC_MC_DET_PF_HISTORY
345 SET
346 CC_DET_PF_Line_Id = p_CC_DET_PF_Line_Id,
347 Set_Of_Books_Id = p_Set_Of_Books_Id,
348 CC_DET_PF_Func_Amt = p_CC_DET_PF_Func_Amt,
349 CC_DET_PF_Encmbrnc_Amt = p_CC_DET_PF_Encmbrnc_Amt,
350 CC_DET_PF_version_num = p_CC_DET_PF_version_num,
351 CC_DET_PF_version_action = p_CC_DET_PF_version_action,
352 Conversion_Type = p_Conversion_Type,
353 Conversion_Date = p_Conversion_Date,
354 Conversion_Rate = p_Conversion_Rate
355 WHERE rowid = p_Rowid;
356 if (SQL%NOTFOUND) then
357 Raise NO_DATA_FOUND;
358 end if;
359
360 IF FND_API.To_Boolean ( p_commit ) THEN
361 COMMIT WORK;
362 END iF;
363
364 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
365 p_data => X_msg_data );
366
367 EXCEPTION
368
369 WHEN FND_API.G_EXC_ERROR THEN
370
371 ROLLBACK TO Update_Row_Pvt ;
372 X_return_status := FND_API.G_RET_STS_ERROR;
373 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
374 p_data => X_msg_data );
375
376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
377
378 ROLLBACK TO Update_Row_Pvt ;
379 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
381 p_data => X_msg_data );
382
383 WHEN OTHERS THEN
384
385 ROLLBACK TO Update_Row_Pvt ;
386 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387
388 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
389 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
390 l_api_name);
391 END if;
392
393 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
394 p_data => X_msg_data );
395
396 END Update_Row;
397
398 /* ================================================================================
399 PROCEDURE Delete_Row
400 ===============================================================================*/
401
402 PROCEDURE Delete_Row(
403 p_api_version IN NUMBER,
404 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
405 p_commit IN VARCHAR2 := FND_API.G_FALSE,
406 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
407 X_return_status OUT NOCOPY VARCHAR2,
408 X_msg_count OUT NOCOPY NUMBER,
409 X_msg_data OUT NOCOPY VARCHAR2,
410 p_Rowid IN OUT NOCOPY VARCHAR2
411
412 ) IS
413
414 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
415 l_api_version CONSTANT NUMBER := 1.0;
416 l_return_status VARCHAR2(1) ;
417 l_msg_count NUMBER ;
418 l_msg_data VARCHAR2(2000) ;
419
420 BEGIN
421
422 SAVEPOINT Delete_Row_Pvt ;
423
424 IF NOT FND_API.Compatible_API_Call ( l_api_version,
425 p_api_version,
426 l_api_name,
427 G_PKG_NAME )
428 THEN
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
430 END IF;
431
432
433 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
434 FND_MSG_PUB.initialize ;
435 END IF ;
436
437 X_return_status := FND_API.G_RET_STS_SUCCESS ;
438
439 DELETE FROM IGC_CC_MC_DET_PF_HISTORY
440 WHERE rowid = p_Rowid;
441
442 if (SQL%NOTFOUND) then
443 Raise NO_DATA_FOUND;
444 end if;
445
446 IF FND_API.To_Boolean ( p_commit ) THEN
447 COMMIT WORK;
448 END iF;
449
450 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
451 p_data => X_msg_data );
452
453 EXCEPTION
454
455 WHEN FND_API.G_EXC_ERROR THEN
456
457 ROLLBACK TO Delete_Row_Pvt ;
458 X_return_status := FND_API.G_RET_STS_ERROR;
459 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
460 p_data => X_msg_data );
461
462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463
464 ROLLBACK TO Delete_Row_Pvt ;
465 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
467 p_data => X_msg_data );
468
469 WHEN OTHERS THEN
470
471 ROLLBACK TO Delete_Row_Pvt ;
472 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
473
474 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
475 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
476 l_api_name);
477 END if;
478
479 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
480 p_data => X_msg_data );
481
482
483 END Delete_Row;
484
485
486
487
488 END IGC_CC_MC_DET_PF_HST_PKG;