[Home] [Help]
PACKAGE BODY: APPS.OKC_NUMBER_SCHEME_DTL_GRP
Source
1 PACKAGE BODY OKC_NUMBER_SCHEME_DTL_GRP AS
2 /* $Header: OKCGNSDB.pls 120.1 2005/11/03 02:29:42 ndoddi noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_NUMBER_SCHEME_DTL_GRP';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
22 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
23 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
24
25 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
26 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
27 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
28
29 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
30 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
31 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
32
33 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
34 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
36
37 ---------------------------------------
38 -- PROCEDURE Validate_Number_Scheme_Dtl --
39 ---------------------------------------
40 PROCEDURE Validate_Number_Scheme_Dtl(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2 ,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48
49 p_num_scheme_id IN NUMBER,
50 p_num_sequence_code IN VARCHAR2,
51 p_sequence_level IN NUMBER,
52 p_concatenation_yn IN VARCHAR2,
53 p_end_character IN VARCHAR2,
54
55
56
57 p_object_version_number IN NUMBER
58 ) IS
59 l_api_version CONSTANT NUMBER := 1;
60 l_api_name CONSTANT VARCHAR2(30) := 'g_Validate_Number_Scheme_Dtl';
61
62 BEGIN
63
64 /*IF (l_debug = 'Y') THEN
65 okc_debug.log('100: Entered Validate_Number_Scheme_Dtl', 2);
66 END IF;*/
67
68 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
69 FND_LOG.STRING(G_PROC_LEVEL,
70 G_PKG_NAME, '100: Entered Validate_Number_Scheme_Dtl' );
71 END IF;
72
73 -- Standard Start of API savepoint
74 SAVEPOINT g_Validate_Num_Scheme_Dtl_GRP;
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79 -- Initialize message list if p_init_msg_list is set to TRUE.
80 IF FND_API.to_Boolean( p_init_msg_list ) THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83 -- Initialize API return status to success
84 x_return_status := G_RET_STS_SUCCESS;
85
86 --------------------------------------------
87 -- Calling Simple API for Validation
88 --------------------------------------------
89 OKC_NUMBER_SCHEME_DTL_PVT.Validate_Row(
90 p_validation_level => p_validation_level,
91 x_return_status => x_return_status,
92 p_num_scheme_id => p_num_scheme_id,
93 p_num_sequence_code => p_num_sequence_code,
94 p_sequence_level => p_sequence_level,
95 p_concatenation_yn => p_concatenation_yn,
96 p_end_character => p_end_character,
97 p_object_version_number => p_object_version_number
98 );
99 --------------------------------------------
100 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
102 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
103 RAISE FND_API.G_EXC_ERROR ;
104 END IF;
105 --------------------------------------------
106
107 -- Standard call to get message count and if count is 1, get message info.
108 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
109
110 /*IF (l_debug = 'Y') THEN
111 okc_debug.log('200: Leaving Validate_Number_Scheme_Dtl', 2);
112 END IF;*/
113
114 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
115 FND_LOG.STRING(G_PROC_LEVEL,
116 G_PKG_NAME, '200: Leaving Validate_Number_Scheme_Dtl' );
117 END IF;
118
119 EXCEPTION
120 WHEN FND_API.G_EXC_ERROR THEN
121 /*IF (l_debug = 'Y') THEN
122 okc_debug.log('300: Leaving Validate_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception', 2);
123 END IF;*/
124
125 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
126 FND_LOG.STRING(G_EXCP_LEVEL,
127 G_PKG_NAME, '300: Leaving Validate_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception' );
128 END IF;
129 ROLLBACK TO g_Validate_Num_Scheme_Dtl_GRP;
130 x_return_status := G_RET_STS_ERROR ;
131 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
132
133 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
134 /*IF (l_debug = 'Y') THEN
135 okc_debug.log('400: Leaving Validate_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
136 END IF;*/
137
138 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
139 FND_LOG.STRING(G_EXCP_LEVEL,
140 G_PKG_NAME, '400: Leaving Validate_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
141 END IF;
142 ROLLBACK TO g_Validate_Num_Scheme_Dtl_GRP;
143 x_return_status := G_RET_STS_UNEXP_ERROR ;
144 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
145
146 WHEN OTHERS THEN
147 /*IF (l_debug = 'Y') THEN
148 okc_debug.log('500: Leaving Validate_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm, 2);
149 END IF;*/
150
151 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
152 FND_LOG.STRING(G_EXCP_LEVEL,
153 G_PKG_NAME, '500: Leaving Validate_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm );
154 END IF;
155 ROLLBACK TO g_Validate_Num_Scheme_Dtl_GRP;
156 x_return_status := G_RET_STS_UNEXP_ERROR ;
157 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
158 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
159 END IF;
160 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
161
162 END Validate_Number_Scheme_Dtl;
163
164 -------------------------------------
165 -- PROCEDURE Insert_Number_Scheme_Dtl
166 -------------------------------------
167 PROCEDURE Insert_Number_Scheme_Dtl(
168 p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 ,
170 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
171 p_commit IN VARCHAR2 := FND_API.G_FALSE,
172
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176
177 p_num_scheme_id IN NUMBER,
178 p_num_sequence_code IN VARCHAR2,
179 p_sequence_level IN NUMBER,
180 p_concatenation_yn IN VARCHAR2,
181 p_end_character IN VARCHAR2,
182
183
184
185 x_num_scheme_id OUT NOCOPY NUMBER,
186 x_num_sequence_code OUT NOCOPY VARCHAR2,
187 x_sequence_level OUT NOCOPY NUMBER
188
189 ) IS
190
191 l_api_version CONSTANT NUMBER := 1;
192 l_api_name CONSTANT VARCHAR2(30) := 'g_Lock_Number_Scheme_Dtl';
193 l_object_version_number OKC_NUMBER_SCHEME_DTLS.OBJECT_VERSION_NUMBER%TYPE := 1;
194 l_created_by OKC_NUMBER_SCHEME_DTLS.CREATED_BY%TYPE;
195 l_creation_date OKC_NUMBER_SCHEME_DTLS.CREATION_DATE%TYPE;
196 l_last_updated_by OKC_NUMBER_SCHEME_DTLS.LAST_UPDATED_BY%TYPE;
197 l_last_update_login OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_LOGIN%TYPE;
198 l_last_update_date OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_DATE%TYPE;
199 BEGIN
200
201 /*IF (l_debug = 'Y') THEN
202 okc_debug.log('600: Entered Insert_Number_Scheme_Dtl', 2);
203 END IF;*/
204
205 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
206 FND_LOG.STRING(G_PROC_LEVEL,
207 G_PKG_NAME, '600: Entered Insert_Number_Scheme_Dtl' );
208 END IF;
209
210 -- Standard Start of API savepoint
211 SAVEPOINT g_Insert_Number_Scheme_Dtl_GRP;
212 -- Standard call to check for call compatibility.
213 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 END IF;
216 -- Initialize message list if p_init_msg_list is set to TRUE.
217 IF FND_API.to_Boolean( p_init_msg_list ) THEN
218 FND_MSG_PUB.initialize;
219 END IF;
220 -- Initialize API return status to success
221 x_return_status := G_RET_STS_SUCCESS;
222
223 --------------------------------------------
224 -- Calling Simple API for Creating A Row
225 --------------------------------------------
226 OKC_NUMBER_SCHEME_DTL_PVT.Insert_Row(
227 p_validation_level => p_validation_level,
228 x_return_status => x_return_status,
229 p_num_scheme_id => p_num_scheme_id,
230 p_num_sequence_code => p_num_sequence_code,
231 p_sequence_level => p_sequence_level,
232 p_concatenation_yn => p_concatenation_yn,
233 p_end_character => p_end_character,
234 x_num_scheme_id => x_num_scheme_id,
235 x_num_sequence_code => x_num_sequence_code,
236 x_sequence_level => x_sequence_level
237 );
238 --------------------------------------------
239 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
241 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
242 RAISE FND_API.G_EXC_ERROR ;
243 END IF;
244 --------------------------------------------
245
246 -- Standard check of p_commit
247 IF FND_API.To_Boolean( p_commit ) THEN
248 COMMIT WORK;
249 END IF;
250 -- Standard call to get message count and if count is 1, get message info.
251 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
252
253 /*IF (l_debug = 'Y') THEN
254 okc_debug.log('700: Leaving Insert_Number_Scheme_Dtl', 2);
255 END IF;*/
256
257 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
258 FND_LOG.STRING(G_PROC_LEVEL,
259 G_PKG_NAME, '700: Leaving Insert_Number_Scheme_Dtl' );
260 END IF;
261
262 EXCEPTION
263 WHEN FND_API.G_EXC_ERROR THEN
264 /*IF (l_debug = 'Y') THEN
265 okc_debug.log('800: Leaving Insert_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception', 2);
266 END IF;*/
267
268 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
269 FND_LOG.STRING(G_EXCP_LEVEL,
270 G_PKG_NAME, '800: Leaving Insert_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception' );
271 END IF;
272 ROLLBACK TO g_Insert_Number_Scheme_Dtl_GRP;
273 x_return_status := G_RET_STS_ERROR ;
274 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
275
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277 /*IF (l_debug = 'Y') THEN
278 okc_debug.log('900: Leaving Insert_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
279 END IF;*/
280
281 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
282 FND_LOG.STRING(G_EXCP_LEVEL,
283 G_PKG_NAME, '900: Leaving Insert_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
284 END IF;
285 ROLLBACK TO g_Insert_Number_Scheme_Dtl_GRP;
286 x_return_status := G_RET_STS_UNEXP_ERROR ;
287 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
288
289 WHEN OTHERS THEN
290 /*IF (l_debug = 'Y') THEN
291 okc_debug.log('1000: Leaving Insert_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm, 2);
292 END IF;*/
293
294 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
295 FND_LOG.STRING(G_EXCP_LEVEL,
296 G_PKG_NAME, '1000: Leaving Insert_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm );
297 END IF;
298 ROLLBACK TO g_Insert_Number_Scheme_Dtl_GRP;
299 x_return_status := G_RET_STS_UNEXP_ERROR ;
300 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
301 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
302 END IF;
303 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
304
305 END Insert_Number_Scheme_Dtl;
306 ---------------------------------------------------------------------------
307 -- PROCEDURE Lock_Number_Scheme_Dtl
308 ---------------------------------------------------------------------------
309 PROCEDURE Lock_Number_Scheme_Dtl(
310 p_api_version IN NUMBER,
311 p_init_msg_list IN VARCHAR2 ,
312
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2,
316
317 p_num_scheme_id IN NUMBER,
318 p_num_sequence_code IN VARCHAR2,
319 p_sequence_level IN NUMBER,
320 p_object_version_number IN NUMBER
321 ) IS
322 l_api_version CONSTANT NUMBER := 1;
323 l_api_name CONSTANT VARCHAR2(30) := 'g_Lock_Number_Scheme_Dtl';
324 BEGIN
325
326 /*IF (l_debug = 'Y') THEN
327 okc_debug.log('1100: Entered Lock_Number_Scheme_Dtl', 2);
328 END IF;*/
329
330 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
331 FND_LOG.STRING(G_PROC_LEVEL,
332 G_PKG_NAME, '1100: Entered Lock_Number_Scheme_Dtl' );
333 END IF;
334
335 -- Standard Start of API savepoint
336 SAVEPOINT g_Lock_Number_Scheme_Dtl_GRP;
337 -- Standard call to check for call compatibility.
338 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 END IF;
341 -- Initialize message list if p_init_msg_list is set to TRUE.
342 IF FND_API.to_Boolean( p_init_msg_list ) THEN
343 FND_MSG_PUB.initialize;
344 END IF;
345 -- Initialize API return status to success
346 x_return_status := G_RET_STS_SUCCESS;
347
348 --------------------------------------------
349 -- Calling Simple API for Locking A Row
350 --------------------------------------------
351 OKC_NUMBER_SCHEME_DTL_PVT.lock_row(
352 x_return_status => x_return_status,
353 p_num_scheme_id => p_num_scheme_id,
354 p_num_sequence_code => p_num_sequence_code,
355 p_sequence_level => p_sequence_level,
356 p_object_version_number => p_object_version_number
357 );
358 --------------------------------------------
359 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
361 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
362 RAISE FND_API.G_EXC_ERROR ;
363 END IF;
364 --------------------------------------------
365
366 -- Standard call to get message count and if count is 1, get message info.
367 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
368
369 /*IF (l_debug = 'Y') THEN
370 okc_debug.log('1200: Leaving Lock_Number_Scheme_Dtl', 2);
371 END IF;*/
372
373 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
374 FND_LOG.STRING(G_PROC_LEVEL,
375 G_PKG_NAME, '1200: Leaving Lock_Number_Scheme_Dtl' );
376 END IF;
377
378 EXCEPTION
379 WHEN FND_API.G_EXC_ERROR THEN
380 /*IF (l_debug = 'Y') THEN
381 okc_debug.log('1300: Leaving Lock_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception', 2);
382 END IF;*/
383
384 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
385 FND_LOG.STRING(G_EXCP_LEVEL,
386 G_PKG_NAME, '1300: Leaving Lock_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception' );
387 END IF;
388 ROLLBACK TO g_Lock_Number_Scheme_Dtl_GRP;
389 x_return_status := G_RET_STS_ERROR ;
390 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
391
392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
393 /*IF (l_debug = 'Y') THEN
394 okc_debug.log('1400: Leaving Lock_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
395 END IF;*/
396
397 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
398 FND_LOG.STRING(G_EXCP_LEVEL,
399 G_PKG_NAME, '1400: Leaving Lock_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
400 END IF;
401 ROLLBACK TO g_Lock_Number_Scheme_Dtl_GRP;
402 x_return_status := G_RET_STS_UNEXP_ERROR ;
403 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
404
405 WHEN OTHERS THEN
406 /*IF (l_debug = 'Y') THEN
407 okc_debug.log('1500: Leaving Lock_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm, 2);
408 END IF;*/
409
410 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
411 FND_LOG.STRING(G_EXCP_LEVEL,
412 G_PKG_NAME, '1500: Leaving Lock_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm );
413 END IF;
414 ROLLBACK TO g_Lock_Number_Scheme_Dtl_GRP;
415 x_return_status := G_RET_STS_UNEXP_ERROR ;
416 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
417 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
418 END IF;
419 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
420
421 END Lock_Number_Scheme_Dtl;
422 ---------------------------------------------------------------------------
423 -- PROCEDURE Update_Number_Scheme_Dtl
424 ---------------------------------------------------------------------------
425 PROCEDURE Update_Number_Scheme_Dtl(
426 p_api_version IN NUMBER,
427 p_init_msg_list IN VARCHAR2 ,
428 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
429 p_commit IN VARCHAR2 := FND_API.G_FALSE,
430
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2,
434
435 p_num_scheme_id IN NUMBER,
436 p_num_sequence_code IN VARCHAR2,
437 p_sequence_level IN NUMBER,
438 p_concatenation_yn IN VARCHAR2,
439 p_end_character IN VARCHAR2,
440
441
442
443 p_object_version_number IN NUMBER
444
445 ) IS
446
447 l_api_version CONSTANT NUMBER := 1;
448 l_api_name CONSTANT VARCHAR2(30) := 'g_Update_Number_Scheme_Dtl';
449
450 BEGIN
451
452 /*IF (l_debug = 'Y') THEN
453 okc_debug.log('1600: Entered Update_Number_Scheme_Dtl', 2);
454 okc_debug.log('1700: Locking row', 2);
455 END IF;*/
456
457 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
458 FND_LOG.STRING(G_PROC_LEVEL,
459 G_PKG_NAME, '1600: Entered Update_Number_Scheme_Dtl');
460 FND_LOG.STRING(G_PROC_LEVEL,
461 G_PKG_NAME, '1700: Locking row');
462 END IF;
463
464 -- Standard Start of API savepoint
465 SAVEPOINT g_Update_Number_Scheme_Dtl_GRP;
466 -- Standard call to check for call compatibility.
467 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
469 END IF;
470 -- Initialize message list if p_init_msg_list is set to TRUE.
471 IF FND_API.to_Boolean( p_init_msg_list ) THEN
472 FND_MSG_PUB.initialize;
473 END IF;
474 -- Initialize API return status to success
475 x_return_status := G_RET_STS_SUCCESS;
476
477 --------------------------------------------
478 -- Calling Simple API for Updating A Row
479 --------------------------------------------
480 OKC_NUMBER_SCHEME_DTL_PVT.Update_Row(
481 p_validation_level => p_validation_level,
482 x_return_status => x_return_status,
483 p_num_scheme_id => p_num_scheme_id,
484 p_num_sequence_code => p_num_sequence_code,
485 p_sequence_level => p_sequence_level,
486 p_concatenation_yn => p_concatenation_yn,
487 p_end_character => p_end_character,
488 p_object_version_number => p_object_version_number
489 );
490 --------------------------------------------
491 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
493 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
494 RAISE FND_API.G_EXC_ERROR ;
495 END IF;
496 --------------------------------------------
497
498 -- Standard check of p_commit
499 IF FND_API.To_Boolean( p_commit ) THEN
500 COMMIT WORK;
501 END IF;
502 -- Standard call to get message count and if count is 1, get message info.
503 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
504
505 /*IF (l_debug = 'Y') THEN
506 okc_debug.log('1800: Leaving Update_Number_Scheme_Dtl', 2);
507 END IF;*/
508
509 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
510 FND_LOG.STRING(G_PROC_LEVEL,
511 G_PKG_NAME, '1800: Leaving Update_Number_Scheme_Dtl' );
512 END IF;
513
514 EXCEPTION
515 WHEN FND_API.G_EXC_ERROR THEN
516 /*IF (l_debug = 'Y') THEN
517 okc_debug.log('1900: Leaving Update_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception', 2);
518 END IF;*/
519
520 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
521 FND_LOG.STRING(G_EXCP_LEVEL,
522 G_PKG_NAME, '1900: Leaving Update_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception' );
523 END IF;
524 ROLLBACK TO g_Update_Number_Scheme_Dtl_GRP;
525 x_return_status := G_RET_STS_ERROR ;
526 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
527
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 /*IF (l_debug = 'Y') THEN
530 okc_debug.log('2000: Leaving Update_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
531 END IF;*/
532
533 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
534 FND_LOG.STRING(G_EXCP_LEVEL,
535 G_PKG_NAME, '2000: Leaving Update_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
536 END IF;
537 ROLLBACK TO g_Update_Number_Scheme_Dtl_GRP;
538 x_return_status := G_RET_STS_UNEXP_ERROR ;
539 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
540
541 WHEN OTHERS THEN
542 /*IF (l_debug = 'Y') THEN
543 okc_debug.log('2100: Leaving Update_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm, 2);
544 END IF;*/
545
546 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
547 FND_LOG.STRING(G_EXCP_LEVEL,
548 G_PKG_NAME, '2100: Leaving Update_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm );
549 END IF;
550 ROLLBACK TO g_Update_Number_Scheme_Dtl_GRP;
551 x_return_status := G_RET_STS_UNEXP_ERROR ;
552 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
553 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
554 END IF;
555 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
556
557 END Update_Number_Scheme_Dtl;
558
559 ---------------------------------------------------------------------------
560 -- PROCEDURE Delete_Number_Scheme_Dtl
561 ---------------------------------------------------------------------------
562 PROCEDURE Delete_Number_Scheme_Dtl(
563 p_api_version IN NUMBER,
564 p_init_msg_list IN VARCHAR2 ,
565 p_commit IN VARCHAR2 := FND_API.G_FALSE,
566
567 x_return_status OUT NOCOPY VARCHAR2,
568 x_msg_count OUT NOCOPY NUMBER,
569 x_msg_data OUT NOCOPY VARCHAR2,
570
571 p_num_scheme_id IN NUMBER,
572 p_num_sequence_code IN VARCHAR2,
573 p_sequence_level IN NUMBER,
574 p_object_version_number IN NUMBER
575 ) IS
576 l_api_version CONSTANT NUMBER := 1;
577 l_api_name CONSTANT VARCHAR2(30) := 'g_Delete_Number_Scheme_Dtl';
578 BEGIN
579
580 /*IF (l_debug = 'Y') THEN
581 okc_debug.log('2200: Entered Delete_Number_Scheme_Dtl', 2);
582 END IF;*/
583
584 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
585 FND_LOG.STRING(G_PROC_LEVEL,
586 G_PKG_NAME, '2200: Entered Delete_Number_Scheme_Dtl' );
587 END IF;
588
589 -- Standard Start of API savepoint
590 SAVEPOINT g_Delete_Number_Scheme_Dtl_GRP;
591 -- Standard call to check for call compatibility.
592 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595 -- Initialize message list if p_init_msg_list is set to TRUE.
596 IF FND_API.to_Boolean( p_init_msg_list ) THEN
597 FND_MSG_PUB.initialize;
598 END IF;
599 -- Initialize API return status to success
600 x_return_status := G_RET_STS_SUCCESS;
601
602 --------------------------------------------
603 -- Calling Simple API for Deleting A Row
604 --------------------------------------------
605 OKC_NUMBER_SCHEME_DTL_PVT.Delete_Row(
606 x_return_status => x_return_status,
607 p_num_scheme_id => p_num_scheme_id,
608 p_num_sequence_code => p_num_sequence_code,
609 p_sequence_level => p_sequence_level,
610 p_object_version_number => p_object_version_number
611 );
612 --------------------------------------------
613 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
615 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
616 RAISE FND_API.G_EXC_ERROR ;
617 END IF;
618 --------------------------------------------
619
620 -- Standard check of p_commit
621 IF FND_API.To_Boolean( p_commit ) THEN
622 COMMIT WORK;
623 END IF;
624 -- Standard call to get message count and if count is 1, get message info.
625 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
626
627 /*IF (l_debug = 'Y') THEN
628 okc_debug.log('2300: Leaving Delete_Number_Scheme_Dtl', 2);
629 END IF;*/
630
631 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
632 FND_LOG.STRING(G_PROC_LEVEL,
633 G_PKG_NAME, '2300: Leaving Delete_Number_Scheme_Dtl' );
634 END IF;
635
636 EXCEPTION
637 WHEN FND_API.G_EXC_ERROR THEN
638 /*IF (l_debug = 'Y') THEN
639 okc_debug.log('2400: Leaving Delete_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception', 2);
640 END IF;*/
641
642 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
643 FND_LOG.STRING(G_EXCP_LEVEL,
644 G_PKG_NAME, '2400: Leaving Delete_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_ERROR Exception' );
645 END IF;
646 ROLLBACK TO g_Delete_Number_Scheme_Dtl_GRP;
647 x_return_status := G_RET_STS_ERROR ;
648 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
649
650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
651 /*IF (l_debug = 'Y') THEN
652 okc_debug.log('2500: Leaving Delete_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
653 END IF;*/
654
655 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
656 FND_LOG.STRING(G_EXCP_LEVEL,
657 G_PKG_NAME, '2500: Leaving Delete_Number_Scheme_Dtl: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
658 END IF;
659 ROLLBACK TO g_Delete_Number_Scheme_Dtl_GRP;
660 x_return_status := G_RET_STS_UNEXP_ERROR ;
661 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
662
663 WHEN OTHERS THEN
664 /*IF (l_debug = 'Y') THEN
665 okc_debug.log('2600: Leaving Delete_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm, 2);
666 END IF;*/
667
668 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
669 FND_LOG.STRING(G_EXCP_LEVEL,
670 G_PKG_NAME, '2600: Leaving Delete_Number_Scheme_Dtl because of EXCEPTION: '||sqlerrm );
671 END IF;
672 ROLLBACK TO g_Delete_Number_Scheme_Dtl_GRP;
673 x_return_status := G_RET_STS_UNEXP_ERROR ;
674 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
675 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
676 END IF;
677 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
678
679 END Delete_Number_Scheme_Dtl;
680
681 END OKC_NUMBER_SCHEME_DTL_GRP;