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