[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_HEADERS_PVT
Source
1 PACKAGE BODY CN_IMP_HEADERS_PVT AS
2 -- $Header: cnvimhrb.pls 115.7 2002/11/21 21:13:29 hlchen ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMP_HEADERS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimhrb.pls';
6
7 -- Start of comments
8 -- API name : Create_Imp_header
9 -- Type : Private.
10 -- Function :
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- IN : p_api_version IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2 Optional
15 -- Default = FND_API.G_FALSE
16 -- p_commit IN VARCHAR2 Optional
17 -- Default = FND_API.G_FALSE
18 -- p_validation_level IN NUMBER Optional
19 -- Default = FND_API.G_VALID_LEVEL_FULL
20 -- p_imp_header IN imp_header_rec_type
21 -- OUT : x_return_status OUT VARCHAR2(1)
22 -- x_msg_count OUT NUMBER
23 -- x_msg_data OUT VARCHAR2(2000)
24 -- x_imp_header_id OUT NUMBER
25 -- Version : Current version 1.0
26 --
27 --
28 --
29 -- Notes : Note text
30 --
31 -- End of comments
32
33 PROCEDURE Create_Imp_header
34 ( p_api_version IN NUMBER ,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 x_return_status OUT NOCOPY VARCHAR2 ,
39 x_msg_count OUT NOCOPY NUMBER ,
40 x_msg_data OUT NOCOPY VARCHAR2 ,
41 p_imp_header IN imp_headers_rec_type,
42 x_imp_header_id OUT NOCOPY NUMBER
43 ) IS
44
45 l_api_name CONSTANT VARCHAR2(30) := 'Create_Imp_header';
46 l_api_version CONSTANT NUMBER := 1.0;
47
48 l_imp_header imp_headers_rec_type;
49 l_temp NUMBER;
50
51 BEGIN
52 -- Standard Start of API savepoint
53 SAVEPOINT Create_Imp_header;
54 -- Standard call to check for call compatibility.
55 IF NOT FND_API.compatible_api_call
56 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
57 THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list ) THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64 -- Initialize API return status to success
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66 -- API body
67 -- Check Name uniqueness
68 BEGIN
69 SELECT 1
70 INTO l_temp
71 FROM cn_imp_headers
72 WHERE name = p_imp_header.name
73 ;
74 EXCEPTION
75 WHEN no_data_found THEN
76 NULL;
77 END;
78 IF l_temp = 1 THEN
79 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
80 THEN
81 FND_MESSAGE.SET_NAME ('CN','CN_ALREADY_EXISTS');
82 FND_MSG_PUB.Add;
83 END IF;
84 RAISE FND_API.G_EXC_ERROR ;
85 END IF;
86
87 -- Call API to create cn_imp_headers
88 l_imp_header := p_imp_header ;
89 l_imp_header.status_code := 'NEW';
90
91 SELECT cn_imp_headers_s.nextval
92 INTO x_imp_header_id
93 FROM sys.dual ;
94 l_imp_header.imp_header_id := x_imp_header_id ;
95
96 cn_imp_headers_pkg.insert_row
97 (p_imp_headers_rec => l_imp_header);
98
99 -- End of API body.
100
101 -- Standard check of p_commit.
102 IF FND_API.To_Boolean( p_commit ) THEN
103 COMMIT WORK;
104 END IF;
105 -- Standard call to get message count and if count is 1, get message info.
106 FND_MSG_PUB.Count_And_Get(
107 p_count => x_msg_count ,
108 p_data => x_msg_data ,
109 p_encoded => FND_API.G_FALSE
110 );
111
112 EXCEPTION
113 WHEN FND_API.G_EXC_ERROR THEN
114 ROLLBACK TO Create_Imp_header ;
115 x_return_status := FND_API.G_RET_STS_ERROR ;
116 FND_MSG_PUB.Count_And_Get(
117 p_count => x_msg_count ,
118 p_data => x_msg_data ,
119 p_encoded => FND_API.G_FALSE
120 );
121
122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
123 ROLLBACK TO Create_Imp_header ;
124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
125 FND_MSG_PUB.Count_And_Get(
126 p_count => x_msg_count ,
127 p_data => x_msg_data ,
128 p_encoded => FND_API.G_FALSE
129 );
130
131 WHEN OTHERS THEN
132 ROLLBACK TO Create_Imp_header ;
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
134 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
135 THEN
136 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
137 END IF;
138 FND_MSG_PUB.Count_And_Get
139 (
140 p_count => x_msg_count ,
141 p_data => x_msg_data ,
142 p_encoded => FND_API.G_FALSE
143 );
144 END Create_Imp_header;
145
146 -- Start of comments
147 -- API name : Update_Imp_header
148 -- Type : Private.
149 -- Function :
150 -- Pre-reqs : None.
151 -- Parameters :
152 -- IN : p_api_version IN NUMBER Required
153 -- p_init_msg_list IN VARCHAR2 Optional
154 -- Default = FND_API.G_FALSE
155 -- p_commit IN VARCHAR2 Optional
156 -- Default = FND_API.G_FALSE
157 -- p_validation_level IN NUMBER Optional
158 -- Default = FND_API.G_VALID_LEVEL_FULL
159 -- p_imp_header IN imp_header_rec_type
160 -- OUT : x_return_status OUT VARCHAR2(1)
161 -- x_msg_count OUT NUMBER
162 -- x_msg_data OUT VARCHAR2(2000)
163 -- Version : Current version 1.0
164 --
165 --
166 --
167 -- Notes : Note text
168 --
169 -- End of comments
170
171 PROCEDURE Update_Imp_header
172 ( p_api_version IN NUMBER ,
173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
174 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
175 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
176 x_return_status OUT NOCOPY VARCHAR2 ,
177 x_msg_count OUT NOCOPY NUMBER ,
178 x_msg_data OUT NOCOPY VARCHAR2 ,
179 p_imp_header IN imp_headers_rec_type
180 ) IS
181
182 l_api_name CONSTANT VARCHAR2(30) := 'Update_Imp_header';
183 l_api_version CONSTANT NUMBER := 1.0;
184
185 l_imp_header imp_headers_rec_type;
186 l_temp NUMBER;
187
188 BEGIN
189 -- Standard Start of API savepoint
190 SAVEPOINT Update_Imp_header;
191 -- Standard call to check for call compatibility.
192 IF NOT FND_API.compatible_api_call
193 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
194 THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197 -- Initialize message list if p_init_msg_list is set to TRUE.
198 IF FND_API.to_Boolean( p_init_msg_list ) THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201 -- Initialize API return status to success
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203 -- API body
204 l_imp_header := p_imp_header ;
205
206 -- Check Name uniqueness
207 BEGIN
208 SELECT 1
209 INTO l_temp
210 FROM cn_imp_headers
211 WHERE imp_header_id <> p_imp_header.imp_header_id
212 AND name = p_imp_header.name
213 ;
214 EXCEPTION
215 WHEN no_data_found THEN
216 NULL;
217 END;
218 IF l_temp = 1 THEN
219 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
220 THEN
221 FND_MESSAGE.SET_NAME ('CN','CN_ALREADY_EXISTS');
222 FND_MSG_PUB.Add;
223 END IF;
224 RAISE FND_API.G_EXC_ERROR ;
225 END IF;
226
227 -- Check if record exists
228 BEGIN
229 SELECT imp_header_id,object_version_number
230 INTO l_imp_header.imp_header_id, l_imp_header.object_version_number
231 FROM cn_imp_headers
232 WHERE imp_header_id = p_imp_header.imp_header_id
233 FOR UPDATE OF imp_header_id NOWAIT ;
234 EXCEPTION
235 WHEN no_data_found THEN
236 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
237 THEN
238 FND_MESSAGE.SET_NAME ('CN','CN_REC_NOT_EXISTS');
239 FND_MSG_PUB.Add;
240 END IF;
241 RAISE FND_API.G_EXC_ERROR ;
242 END ;
243
244 -- check locking mechanism
245 IF p_imp_header.object_version_number >
246 p_imp_header.object_version_number THEN
247 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
248 THEN
249 FND_MESSAGE.SET_NAME ('CN' , 'CN_RECORD_CHANGED');
250 FND_MSG_PUB.Add;
251 END IF;
252 RAISE FND_API.G_EXC_ERROR ;
253 END IF;
254
255 -- Call API to update cn_imp_headers
256 cn_imp_headers_pkg.update_row
257 (p_imp_headers_rec => l_imp_header);
258
259 -- End of API body.
260
261 -- Standard check of p_commit.
262 IF FND_API.To_Boolean( p_commit ) THEN
263 COMMIT WORK;
264 END IF;
265 -- Standard call to get message count and if count is 1, get message info.
266 FND_MSG_PUB.Count_And_Get(
267 p_count => x_msg_count ,
268 p_data => x_msg_data ,
269 p_encoded => FND_API.G_FALSE
270 );
271
272 EXCEPTION
273 WHEN FND_API.G_EXC_ERROR THEN
274 ROLLBACK TO Update_Imp_header ;
275 x_return_status := FND_API.G_RET_STS_ERROR ;
276 FND_MSG_PUB.Count_And_Get(
277 p_count => x_msg_count ,
278 p_data => x_msg_data ,
279 p_encoded => FND_API.G_FALSE
280 );
281
282 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
283 ROLLBACK TO Update_Imp_header ;
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
285 FND_MSG_PUB.Count_And_Get(
286 p_count => x_msg_count ,
287 p_data => x_msg_data ,
288 p_encoded => FND_API.G_FALSE
289 );
290
291 WHEN OTHERS THEN
292 ROLLBACK TO Update_Imp_header ;
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
294 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
295 THEN
296 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
297 END IF;
298 FND_MSG_PUB.Count_And_Get
299 (
300 p_count => x_msg_count ,
301 p_data => x_msg_data ,
302 p_encoded => FND_API.G_FALSE
303 );
304 END Update_Imp_header;
305
306 -- Start of comments
307 -- API name : Delete_Imp_header
308 -- Type : Private.
309 -- Function :
310 -- Pre-reqs : None.
311 -- Parameters :
312 -- IN : p_api_version IN NUMBER Required
313 -- p_init_msg_list IN VARCHAR2 Optional
314 -- Default = FND_API.G_FALSE
315 -- p_commit IN VARCHAR2 Optional
316 -- Default = FND_API.G_FALSE
317 -- p_validation_level IN NUMBER Optional
318 -- Default = FND_API.G_VALID_LEVEL_FULL
319 -- p_map_obj_num IN NUMBER,
320 -- p_imp_header IN imp_header_rec_type
321 -- OUT : x_return_status OUT VARCHAR2(1)
322 -- x_msg_count OUT NUMBER
323 -- x_msg_data OUT VARCHAR2(2000)
324 -- Version : Current version 1.0
325 --
326 --
327 --
328 -- Notes : Note text
329 --
330 -- End of comments
331
332 PROCEDURE Delete_Imp_header
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_map_obj_num IN NUMBER,
341 p_imp_header IN imp_headers_rec_type
342 ) IS
343
344 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Imp_header';
345 l_api_version CONSTANT NUMBER := 1.0;
346
347 l_imp_header imp_headers_rec_type;
348 l_temp NUMBER;
349 l_imp_map cn_imp_maps_pvt.imp_maps_rec_type :=
350 cn_imp_maps_pvt.G_MISS_IMP_MAPS_REC;
351
352 CURSOR c_pro_audit_csr(c_imp_header_id NUMBER) IS
353 SELECT process_audit_id
354 FROM cn_process_audits
355 WHERE parent_process_audit_id = c_imp_header_id
356 ;
357
358 BEGIN
359 -- Standard Start of API savepoint
360 SAVEPOINT Delete_Imp_header;
361 -- Standard call to check for call compatibility.
362 IF NOT FND_API.compatible_api_call
363 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
364 THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367 -- Initialize message list if p_init_msg_list is set to TRUE.
368 IF FND_API.to_Boolean( p_init_msg_list ) THEN
369 FND_MSG_PUB.initialize;
370 END IF;
371 -- Initialize API return status to success
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373 -- API body
374 l_imp_header := p_imp_header ;
375 -- Check if record exist
376 BEGIN
377 SELECT imp_header_id,status_code,object_version_number,imp_map_id
378 INTO l_imp_header.imp_header_id,l_imp_header.status_code,
379 l_imp_header.object_version_number,l_imp_header.imp_map_id
380 FROM cn_imp_headers
381 WHERE imp_header_id = p_imp_header.imp_header_id
382 ;
383 EXCEPTION
384 WHEN no_data_found THEN
385 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
386 THEN
387 FND_MESSAGE.SET_NAME ('CN','CN_REC_NOT_EXISTS');
388 FND_MSG_PUB.Add;
389 END IF;
390 RAISE FND_API.G_EXC_ERROR ;
391 END;
392
393 -- check locking mechanism
394 IF l_imp_header.object_version_number >
395 p_imp_header.object_version_number THEN
396 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
397 THEN
398 FND_MESSAGE.SET_NAME ('CN' , 'CN_RECORD_CHANGED');
399 FND_MSG_PUB.Add;
400 END IF;
401 RAISE FND_API.G_EXC_ERROR ;
402 END IF;
403
404 -- delete imp_maps and imp_map_fields
405 IF (l_imp_header.imp_map_id IS NOT NULL) THEN
406 -- get imp_map info
407 l_imp_map.imp_map_id := l_imp_header.imp_map_id;
408 -- if p_map_obj_num = null, called for DELETE ALL, skip checking
409 -- for object_version_number
410 SELECT Decode(p_map_obj_num,NULL,object_version_number,p_map_obj_num)
411 INTO l_imp_map.object_version_number
412 FROM cn_imp_maps
413 WHERE imp_map_id = l_imp_header.imp_map_id;
414 -- check if mapping used by other imp_headers
415 BEGIN
416 l_temp := 0;
417 SELECT COUNT(1) INTO l_temp
418 FROM cn_imp_headers
419 WHERE imp_map_id = l_imp_header.imp_map_id
420 AND imp_header_id <> l_imp_header.imp_header_id
421 ;
422 EXCEPTION
423 WHEN no_data_found THEN
424 NULL;
425 END;
426
427 -- delete imp_map if not used by other imp_headers
428 IF l_temp = 0 THEN
429 cn_imp_maps_pvt.Delete_Imp_Map
430 ( p_api_version => 1.0,
431 x_return_status => x_return_status,
432 x_msg_count => x_msg_count,
433 x_msg_data => x_msg_data,
434 p_imp_map => l_imp_map);
435 IF x_return_status <> FND_API.g_ret_sts_success THEN
436 RAISE FND_API.G_EXC_ERROR;
437 END IF;
438 END IF;
439 END IF;
440
441 -- delete imp_lines,process_audits, process_audit_liness
442 BEGIN
443 -- delete process_audits, process_audit_liness
444 FOR l_pro_audit_csr IN c_pro_audit_csr(l_imp_header.imp_header_id) LOOP
445 cn_message_pkg.purge
446 (x_process_audit_id => l_pro_audit_csr.process_audit_id,
447 x_creation_date => NULL);
448 END LOOP;
449
450 -- delete imp_lines
451 DELETE FROM cn_imp_lines
452 WHERE imp_header_id = l_imp_header.imp_header_id;
453
454 EXCEPTION
455 WHEN no_data_found THEN
456 NULL;
457 END;
458
459 -- Call API to delete cn_imp_headers
460 cn_imp_headers_pkg.delete_row
461 (p_imp_header_id => l_imp_header.imp_header_id);
462
463 -- End of API body.
464
465 -- Standard check of p_commit.
466 IF FND_API.To_Boolean( p_commit ) THEN
467 COMMIT WORK;
468 END IF;
469 -- Standard call to get message count and if count is 1, get message info.
470 FND_MSG_PUB.Count_And_Get(
471 p_count => x_msg_count ,
472 p_data => x_msg_data ,
473 p_encoded => FND_API.G_FALSE
474 );
475
476 EXCEPTION
477 WHEN FND_API.G_EXC_ERROR THEN
478 ROLLBACK TO Delete_Imp_header ;
479 x_return_status := FND_API.G_RET_STS_ERROR ;
480 FND_MSG_PUB.Count_And_Get(
481 p_count => x_msg_count ,
482 p_data => x_msg_data ,
483 p_encoded => FND_API.G_FALSE
484 );
485
486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
487 ROLLBACK TO Delete_Imp_header ;
488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
489 FND_MSG_PUB.Count_And_Get(
490 p_count => x_msg_count ,
491 p_data => x_msg_data ,
492 p_encoded => FND_API.G_FALSE
493 );
494
495 WHEN OTHERS THEN
496 ROLLBACK TO Delete_Imp_header ;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
498 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
499 THEN
500 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
501 END IF;
502 FND_MSG_PUB.Count_And_Get
503 (
504 p_count => x_msg_count ,
505 p_data => x_msg_data ,
506 p_encoded => FND_API.G_FALSE
507 );
508 END Delete_Imp_header;
509
510 -- Start of comments
511 -- API name : Get_Oerr_Msg
512 -- Type : Private.
513 -- Function :
514 -- Pre-reqs : None.
515 -- Parameters :
516 -- IN :
517 -- p_errcode IN VARCHAR2
518 -- OUT :
519 -- x_errmsg OUT VARCHAR2(2000)
520 -- Version : Current version 1.0
521 -- Notes : Note text
522 --
523 -- End of comments
524
525 PROCEDURE Get_Oerr_Msg
526 ( p_errcode IN VARCHAR2 := FND_API.G_FALSE ,
527 x_errmsg OUT NOCOPY VARCHAR2
528 ) IS
529
530 BEGIN
531
532 x_errmsg := SUBSTR (SQLERRM(p_errcode) , 1 , 2000);
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 x_errmsg := 'ORA' || p_errcode;
537
538 END Get_Oerr_Msg;
539
540
541 END CN_IMP_HEADERS_PVT;