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