[Home] [Help]
PACKAGE BODY: APPS.OKC_NUMBER_SCHEME_DTL_PVT
Source
1 PACKAGE BODY OKC_NUMBER_SCHEME_DTL_PVT AS
2 /* $Header: OKCVNSDB.pls 120.1 2005/11/03 01:46:21 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 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
11 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
12 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
13 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
16 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
17 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
18 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_NSD_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32
33 ------------------------------------------------------------------------------
34 -- GLOBAL CONSTANTS
35 ------------------------------------------------------------------------------
36 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
37 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
38 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
39 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
40 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
47 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
48 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
49
50 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
51 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
52 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
53
54 ------------------------------------------------------------------------------
55 -- GLOBAL EXCEPTION
56 ------------------------------------------------------------------------------
57 E_Resource_Busy EXCEPTION;
58 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
59
60 ---------------------------------------------------------------------------
61 -- FUNCTION get_rec for: OKC_NUMBER_SCHEME_DTLS
62 ---------------------------------------------------------------------------
63 FUNCTION Get_Rec (
64 p_num_scheme_id IN NUMBER,
65 p_num_sequence_code IN VARCHAR2,
66 p_sequence_level IN NUMBER,
67
68 x_concatenation_yn OUT NOCOPY VARCHAR2,
69 x_end_character OUT NOCOPY VARCHAR2,
70 x_object_version_number OUT NOCOPY NUMBER,
71 x_created_by OUT NOCOPY NUMBER,
72 x_creation_date OUT NOCOPY DATE,
73 x_last_updated_by OUT NOCOPY NUMBER,
74 x_last_update_login OUT NOCOPY NUMBER,
75 x_last_update_date OUT NOCOPY DATE
76
77 ) RETURN VARCHAR2 IS
78 CURSOR OKC_NUMBER_SCHEME_DTLS_pk_csr (cp_num_scheme_id IN NUMBER,cp_num_sequence_code IN VARCHAR2,cp_sequence_level IN NUMBER) IS
79 SELECT
80 CONCATENATION_YN,
81 END_CHARACTER,
82 OBJECT_VERSION_NUMBER,
83 CREATED_BY,
84 CREATION_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_LOGIN,
87 LAST_UPDATE_DATE
88 FROM OKC_NUMBER_SCHEME_DTLS t
89 WHERE t.NUM_SCHEME_ID = cp_num_scheme_id and
90 t.NUM_SEQUENCE_CODE = cp_num_sequence_code and
91 t.SEQUENCE_LEVEL = cp_sequence_level;
92 BEGIN
93
94 /*IF (l_debug = 'Y') THEN
95 Okc_Debug.Log('400: Entered get_rec', 2);
96 END IF;*/
97
98 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
99 FND_LOG.STRING(G_PROC_LEVEL,
100 G_PKG_NAME, '400: Entered get_rec' );
101 END IF;
102
103 -- Get current database values
104 OPEN OKC_NUMBER_SCHEME_DTLS_pk_csr (p_num_scheme_id, p_num_sequence_code, p_sequence_level);
105 FETCH OKC_NUMBER_SCHEME_DTLS_pk_csr INTO
106 x_concatenation_yn,
107 x_end_character,
108 x_object_version_number,
109 x_created_by,
110 x_creation_date,
111 x_last_updated_by,
112 x_last_update_login,
113 x_last_update_date;
114 IF OKC_NUMBER_SCHEME_DTLS_pk_csr%NOTFOUND THEN
115 RAISE NO_DATA_FOUND;
116 END IF;
117 CLOSE OKC_NUMBER_SCHEME_DTLS_pk_csr;
118
119 /*IF (l_debug = 'Y') THEN
120 Okc_Debug.Log('500: Leaving get_rec ', 2);
121 END IF;*/
122
123 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
124 FND_LOG.STRING(G_PROC_LEVEL,
125 G_PKG_NAME, '500: Leaving get_rec ' );
126 END IF;
127
128 RETURN G_RET_STS_SUCCESS ;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132
133 /*IF (l_debug = 'Y') THEN
134 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
135 END IF;*/
136
137 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
138 FND_LOG.STRING(G_EXCP_LEVEL,
139 G_PKG_NAME, '600: Leaving get_rec because of EXCEPTION: '||sqlerrm );
140 END IF;
141
142 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
143 p_msg_name => G_UNEXPECTED_ERROR,
144 p_token1 => G_SQLCODE_TOKEN,
145 p_token1_value => sqlcode,
146 p_token2 => G_SQLERRM_TOKEN,
147 p_token2_value => sqlerrm);
148
149 IF OKC_NUMBER_SCHEME_DTLS_pk_csr%ISOPEN THEN
150 CLOSE OKC_NUMBER_SCHEME_DTLS_pk_csr;
151 END IF;
152
153 RETURN G_RET_STS_UNEXP_ERROR ;
154
155 END Get_Rec;
156
157 -----------------------------------------
158 -- Set_Attributes for:OKC_NUMBER_SCHEME_DTLS --
159 -----------------------------------------
160 FUNCTION Set_Attributes(
161 p_num_scheme_id IN NUMBER,
162 p_num_sequence_code IN VARCHAR2,
163 p_sequence_level IN NUMBER,
164 p_concatenation_yn IN VARCHAR2,
165 p_end_character IN VARCHAR2,
166 p_object_version_number IN NUMBER,
167
168 x_concatenation_yn OUT NOCOPY VARCHAR2,
169 x_object_version_number OUT NOCOPY VARCHAR2,
170 x_end_character OUT NOCOPY VARCHAR2
171 ) RETURN VARCHAR2 IS
172 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
173 l_object_version_number OKC_NUMBER_SCHEME_DTLS.OBJECT_VERSION_NUMBER%TYPE;
174 l_created_by OKC_NUMBER_SCHEME_DTLS.CREATED_BY%TYPE;
175 l_creation_date OKC_NUMBER_SCHEME_DTLS.CREATION_DATE%TYPE;
176 l_last_updated_by OKC_NUMBER_SCHEME_DTLS.LAST_UPDATED_BY%TYPE;
177 l_last_update_login OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_LOGIN%TYPE;
178 l_last_update_date OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_DATE%TYPE;
179 BEGIN
180 /*IF (l_debug = 'Y') THEN
181 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
182 END IF;*/
183
184 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
185 FND_LOG.STRING(G_PROC_LEVEL,
186 G_PKG_NAME, '700: Entered Set_Attributes ' );
187 END IF;
188
189 IF( p_num_scheme_id IS NOT NULL AND p_num_sequence_code IS NOT NULL AND p_sequence_level IS NOT NULL ) THEN
190 -- Get current database values
191 l_return_status := Get_Rec(
192 p_num_scheme_id => p_num_scheme_id,
193 p_num_sequence_code => p_num_sequence_code,
194 p_sequence_level => p_sequence_level,
195 x_concatenation_yn => x_concatenation_yn,
196 x_end_character => x_end_character,
197 x_object_version_number => x_object_version_number,
198 x_created_by => l_created_by,
199 x_creation_date => l_creation_date,
200 x_last_updated_by => l_last_updated_by,
201 x_last_update_login => l_last_update_login,
202 x_last_update_date => l_last_update_date
203 );
204 --- If any errors happen abort API
205 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
207 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 --- Reversing G_MISS/NULL values logic
212
213 IF (p_concatenation_yn = G_MISS_CHAR) THEN
214 x_concatenation_yn := 'N';
215 ELSIF (p_concatenation_yn IS NOT NULL) THEN
216 x_concatenation_yn := Upper( Nvl( p_concatenation_yn, 'N' ) );
217 END IF;
218
219 IF (p_end_character = G_MISS_CHAR) THEN
220 x_end_character := NULL;
221 ELSIF (p_end_character IS NOT NULL) THEN
222 x_end_character := p_end_character;
223 END IF;
224
225 END IF;
226
227 /*IF (l_debug = 'Y') THEN
228 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
229 END IF;*/
230
231 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
232 FND_LOG.STRING(G_PROC_LEVEL,
233 G_PKG_NAME, '800: Leaving Set_Attributes ' );
234 END IF;
235
236 RETURN G_RET_STS_SUCCESS ;
237 EXCEPTION
238 WHEN FND_API.G_EXC_ERROR THEN
239 /*IF (l_debug = 'Y') THEN
240 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
241 END IF;*/
242
243 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
244 FND_LOG.STRING(G_EXCP_LEVEL,
245 G_PKG_NAME, '900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception' );
246 END IF;
247 RETURN G_RET_STS_ERROR;
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250 /*IF (l_debug = 'Y') THEN
251 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
252 END IF;*/
253
254 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
255 FND_LOG.STRING(G_EXCP_LEVEL,
256 G_PKG_NAME, '1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
257 END IF;
258 RETURN G_RET_STS_UNEXP_ERROR;
259
260 WHEN OTHERS THEN
261 /*IF (l_debug = 'Y') THEN
262 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
263 END IF;*/
264
265 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
266 FND_LOG.STRING(G_EXCP_LEVEL,
267 G_PKG_NAME, '1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm );
268 END IF;
269 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
270 p_msg_name => G_UNEXPECTED_ERROR,
271 p_token1 => G_SQLCODE_TOKEN,
272 p_token1_value => sqlcode,
273 p_token2 => G_SQLERRM_TOKEN,
274 p_token2_value => sqlerrm);
275 RETURN G_RET_STS_UNEXP_ERROR;
276
277 END Set_Attributes ;
281 ----------------------------------------------
278
279 ----------------------------------------------
280 -- Validate_Attributes for: OKC_NUMBER_SCHEME_DTLS --
282 FUNCTION Validate_Attributes (
283 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
284
285 p_num_scheme_id IN NUMBER,
286 p_num_sequence_code IN VARCHAR2,
287 p_sequence_level IN NUMBER,
288 p_concatenation_yn IN VARCHAR2,
289 p_end_character IN VARCHAR2
290 ) RETURN VARCHAR2 IS
291 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
292 l_dummy_var VARCHAR2(1) := '?';
293
294 CURSOR l_num_scheme_id_csr is
295 SELECT '!'
296 FROM OKC_NUMBER_SCHEMES_B
297 WHERE NUM_SCHEME_ID = p_num_scheme_id;
298
299 BEGIN
300
301 /*IF (l_debug = 'Y') THEN
302 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
303 END IF;*/
304
305 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
306 FND_LOG.STRING(G_PROC_LEVEL,
307 G_PKG_NAME, '1200: Entered Validate_Attributes' );
308 END IF;
309
310 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
311 /*IF (l_debug = 'Y') THEN
312 Okc_Debug.Log('1300: required values validation', 2);
313 END IF;*/
314
315 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
316 FND_LOG.STRING(G_PROC_LEVEL,
317 G_PKG_NAME, '1300: required values validation' );
318 END IF;
319
320 /*IF (l_debug = 'Y') THEN
321 Okc_Debug.Log('1400: - attribute NUM_SCHEME_ID ', 2);
322 END IF;*/
323
324 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
325 FND_LOG.STRING(G_PROC_LEVEL,
326 G_PKG_NAME, '1400: - attribute NUM_SCHEME_ID ' );
327 END IF;
328
329 IF ( p_num_scheme_id IS NULL) THEN
330 /*IF (l_debug = 'Y') THEN
331 Okc_Debug.Log('1500: - attribute NUM_SCHEME_ID is invalid', 2);
332 END IF;*/
333
334 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
335 FND_LOG.STRING(G_PROC_LEVEL,
336 G_PKG_NAME, '1500: - attribute NUM_SCHEME_ID is invalid' );
337 END IF;
338
339 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'NUM_SCHEME_ID');
340 l_return_status := G_RET_STS_ERROR;
341 END IF;
342
343 /*IF (l_debug = 'Y') THEN
344 Okc_Debug.Log('1400: - attribute NUM_SEQUENCE_CODE ', 2);
345 END IF;*/
346
347 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
348 FND_LOG.STRING(G_PROC_LEVEL,
349 G_PKG_NAME, '1400: - attribute NUM_SEQUENCE_CODE ' );
350 END IF;
351
352 IF ( p_num_sequence_code IS NULL) THEN
353 /*IF (l_debug = 'Y') THEN
354 Okc_Debug.Log('1500: - attribute NUM_SEQUENCE_CODE is invalid', 2);
355 END IF;*/
356
357 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
358 FND_LOG.STRING(G_PROC_LEVEL,
359 G_PKG_NAME, '1500: - attribute NUM_SEQUENCE_CODE is invalid' );
360 END IF;
361
362 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'NUM_SEQUENCE_CODE');
363 l_return_status := G_RET_STS_ERROR;
364 END IF;
365
366 /*IF (l_debug = 'Y') THEN
367 Okc_Debug.Log('1400: - attribute SEQUENCE_LEVEL ', 2);
368 END IF;*/
369
370 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
371 FND_LOG.STRING(G_PROC_LEVEL,
372 G_PKG_NAME, '1400: - attribute SEQUENCE_LEVEL ' );
373 END IF;
374
375 IF ( p_sequence_level IS NULL) THEN
376 /*IF (l_debug = 'Y') THEN
377 Okc_Debug.Log('1500: - attribute SEQUENCE_LEVEL is invalid', 2);
378 END IF;*/
379
380 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
381 FND_LOG.STRING(G_PROC_LEVEL,
382 G_PKG_NAME, '1500: - attribute SEQUENCE_LEVEL is invalid' );
383 END IF;
384
385 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'SEQUENCE_LEVEL');
386 l_return_status := G_RET_STS_ERROR;
387 END IF;
388
389 /*IF (l_debug = 'Y') THEN
390 Okc_Debug.Log('1400: - attribute CONCATENATION_YN ', 2);
391 END IF;*/
392
393 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
394 FND_LOG.STRING(G_PROC_LEVEL,
395 G_PKG_NAME, '1400: - attribute CONCATENATION_YN ' );
396 END IF;
397
398 IF ( p_concatenation_yn IS NULL) THEN
399 /*IF (l_debug = 'Y') THEN
400 Okc_Debug.Log('1500: - attribute CONCATENATION_YN is invalid', 2);
401 END IF;*/
402
403 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
404 FND_LOG.STRING(G_PROC_LEVEL,
405 G_PKG_NAME, '1500: - attribute CONCATENATION_YN is invalid' );
406 END IF;
407
408 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CONCATENATION_YN');
409 l_return_status := G_RET_STS_ERROR;
410 END IF;
411
412 END IF;
413
414 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
415 /*IF (l_debug = 'Y') THEN
416 Okc_Debug.Log('1600: static values and range validation', 2);
417 END IF;*/
418
419 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
420 FND_LOG.STRING(G_PROC_LEVEL,
421 G_PKG_NAME, '1600: static values and range validation' );
422 END IF;
423
424 /*IF (l_debug = 'Y') THEN
425 Okc_Debug.Log('1700: - attribute CONCATENATION_YN ', 2);
426 END IF;*/
427
428 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
432
429 FND_LOG.STRING(G_PROC_LEVEL,
430 G_PKG_NAME, '1700: - attribute CONCATENATION_YN ' );
431 END IF;
433 IF ( p_concatenation_yn NOT IN ('Y','N') AND p_concatenation_yn IS NOT NULL) THEN
434 /*IF (l_debug = 'Y') THEN
435 Okc_Debug.Log('1800: - attribute CONCATENATION_YN is invalid', 2);
436 END IF;*/
437
438 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
439 FND_LOG.STRING(G_PROC_LEVEL,
440 G_PKG_NAME, '1800: - attribute CONCATENATION_YN is invalid' );
441 END IF;
442
443 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CONCATENATION_YN');
444 l_return_status := G_RET_STS_ERROR;
445 END IF;
446
447 END IF;
448
449 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
450 /*IF (l_debug = 'Y') THEN
451 Okc_Debug.Log('1900: lookup codes validation', 2);
452 END IF;*/
453
454 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
455 FND_LOG.STRING(G_PROC_LEVEL,
456 G_PKG_NAME, '1900: lookup codes validation' );
457 END IF;
458
459 /*IF (l_debug = 'Y') THEN
460 Okc_Debug.Log('2000: - attribute NUM_SEQUENCE_CODE ', 2);
461 END IF;*/
462
463 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
464 FND_LOG.STRING(G_PROC_LEVEL,
465 G_PKG_NAME, '2000: - attribute NUM_SEQUENCE_CODE ' );
466 END IF;
467
468 IF p_num_sequence_code IS NOT NULL THEN
469 l_return_status := Okc_Util.Check_Lookup_Code('OKC_NUMBER_SEQUENCE',p_num_sequence_code);
470 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
471 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'NUM_SEQUENCE_CODE');
472 l_return_status := G_RET_STS_ERROR;
473 END IF;
474 END IF;
475
476 END IF;
477
478 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
479 /*IF (l_debug = 'Y') THEN
480 Okc_Debug.Log('2100: foreigh keys validation ', 2);
481 END IF;*/
482
483 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
484 FND_LOG.STRING(G_PROC_LEVEL,
485 G_PKG_NAME, '2100: foreigh keys validation ' );
486 END IF;
487
488 /*IF (l_debug = 'Y') THEN
489 Okc_Debug.Log('2100: foreigh keys validation ', 2);
490 END IF;*/
491
492 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
493 FND_LOG.STRING(G_PROC_LEVEL,
494 G_PKG_NAME, '2100: foreigh keys validation ' );
495 END IF;
496
497 IF p_num_scheme_id IS NOT NULL THEN
498 l_dummy_var := '?';
499 OPEN l_num_scheme_id_csr;
500 FETCH l_num_scheme_id_csr INTO l_dummy_var;
501 CLOSE l_num_scheme_id_csr;
502 IF (l_dummy_var = '?') THEN
503 /*IF (l_debug = 'Y') THEN
504 Okc_Debug.Log('2300: - attribute NUM_SCHEME_ID is invalid', 2);
505 END IF;*/
506
507 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
508 FND_LOG.STRING(G_PROC_LEVEL,
509 G_PKG_NAME, '2300: - attribute NUM_SCHEME_ID is invalid' );
510 END IF;
511 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'NUM_SCHEME_ID');
512 l_return_status := G_RET_STS_ERROR;
513 END IF;
514 END IF;
515
516 END IF;
517
518
519 /*IF (l_debug = 'Y') THEN
520 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
521 END IF;*/
522
523 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
524 FND_LOG.STRING(G_PROC_LEVEL,
525 G_PKG_NAME, '2400: Leaving Validate_Attributes ' );
526 END IF;
527
528 RETURN l_return_status;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 --Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
533
534 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
535 FND_LOG.STRING(G_EXCP_LEVEL,
536 G_PKG_NAME, '2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm );
537 END IF;
538 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
539 p_msg_name => G_UNEXPECTED_ERROR,
540 p_token1 => G_SQLCODE_TOKEN,
541 p_token1_value => sqlcode,
542 p_token2 => G_SQLERRM_TOKEN,
543 p_token2_value => sqlerrm);
544
545 IF l_num_scheme_id_csr%ISOPEN THEN
546 CLOSE l_num_scheme_id_csr;
547 END IF;
548
549 RETURN G_RET_STS_UNEXP_ERROR;
550
551 END Validate_Attributes;
552
553
554 ---------------------------------------------------------------------------
555 -- PROCEDURE Validate_Record
556 -- It calls Item Level Validations and then makes Record Level Validations
557 ---------------------------------------------------------------------------
558 ------------------------------------------
559 -- Validate_Record for:OKC_NUMBER_SCHEME_DTLS --
560 ------------------------------------------
561 FUNCTION Validate_Record (
562 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
563
564 p_num_scheme_id IN NUMBER,
565 p_num_sequence_code IN VARCHAR2,
566 p_sequence_level IN NUMBER,
567 p_concatenation_yn IN VARCHAR2,
568 p_end_character IN VARCHAR2
569 ) RETURN VARCHAR2 IS
570 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
571 BEGIN
572
573 /*IF (l_debug = 'Y') THEN
574 Okc_Debug.Log('2600: Entered Validate_Record', 2);
575 END IF;*/
576
580 END IF;
577 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
578 FND_LOG.STRING(G_PROC_LEVEL,
579 G_PKG_NAME, '2600: Entered Validate_Record' );
581
582 --- Validate all non-missing attributes (Item Level Validation)
583 l_return_status := Validate_Attributes(
584 p_validation_level => p_validation_level,
585
586 p_num_scheme_id => p_num_scheme_id,
587 p_num_sequence_code => p_num_sequence_code,
588 p_sequence_level => p_sequence_level,
589 p_concatenation_yn => p_concatenation_yn,
590 p_end_character => p_end_character
591 );
592 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
593 /*IF (l_debug = 'Y') THEN
594 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
595 END IF;*/
596
597 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
598 FND_LOG.STRING(G_PROC_LEVEL,
599 G_PKG_NAME, '2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm );
600 END IF;
601 RETURN G_RET_STS_UNEXP_ERROR;
602 END IF;
603
604 --- Record Level Validation
605 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
606 /*IF (l_debug = 'Y') THEN
607 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
608 END IF;*/
609
610 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
611 FND_LOG.STRING(G_PROC_LEVEL,
612 G_PKG_NAME, '2800: Entered Record Level Validations' );
613 END IF;
614 /*+++++++++++++start of hand code +++++++++++++++++++*/
615 -- ?? manual coding for Record Level Validations if required ??
616 /*+++++++++++++End of hand code +++++++++++++++++++*/
617 END IF;
618
619 /*IF (l_debug = 'Y') THEN
620 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
621 END IF;*/
622
623 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
624 FND_LOG.STRING(G_PROC_LEVEL,
625 G_PKG_NAME, '2900: Leaving Validate_Record : '||sqlerrm );
626 END IF;
627 RETURN l_return_status ;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631
632 /*IF (l_debug = 'Y') THEN
633 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
634 END IF;*/
635
636 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
637 FND_LOG.STRING(G_EXCP_LEVEL,
638 G_PKG_NAME, '3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm );
639 END IF;
640
641 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
642 p_msg_name => G_UNEXPECTED_ERROR,
643 p_token1 => G_SQLCODE_TOKEN,
644 p_token1_value => sqlcode,
645 p_token2 => G_SQLERRM_TOKEN,
646 p_token2_value => sqlerrm);
647 RETURN G_RET_STS_UNEXP_ERROR ;
648
649 END Validate_Record;
650
651 ---------------------------------------------------------------------------
652 -- PROCEDURE validate_row
653 ---------------------------------------------------------------------------
654 ---------------------------------------
655 -- validate_row for:OKC_NUMBER_SCHEME_DTLS --
656 ---------------------------------------
657 PROCEDURE validate_row(
658 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
659
660 x_return_status OUT NOCOPY VARCHAR2,
661
662 p_num_scheme_id IN NUMBER,
663 p_num_sequence_code IN VARCHAR2,
664 p_sequence_level IN NUMBER,
665 p_concatenation_yn IN VARCHAR2,
666 p_end_character IN VARCHAR2,
667
668
669
670 p_object_version_number IN NUMBER
671 ) IS
672 l_concatenation_yn OKC_NUMBER_SCHEME_DTLS.CONCATENATION_YN%TYPE;
673 l_end_character OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE;
674 l_object_version_number OKC_NUMBER_SCHEME_DTLS.OBJECT_VERSION_NUMBER%TYPE;
675 l_created_by OKC_NUMBER_SCHEME_DTLS.CREATED_BY%TYPE;
676 l_creation_date OKC_NUMBER_SCHEME_DTLS.CREATION_DATE%TYPE;
677 l_last_updated_by OKC_NUMBER_SCHEME_DTLS.LAST_UPDATED_BY%TYPE;
678 l_last_update_login OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_LOGIN%TYPE;
679 l_last_update_date OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_DATE%TYPE;
680 BEGIN
681
682 /*IF (l_debug = 'Y') THEN
683 Okc_Debug.Log('3100: Entered validate_row', 2);
684 END IF;*/
685
686 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
687 FND_LOG.STRING(G_PROC_LEVEL,
688 G_PKG_NAME, '3100: Entered validate_row' );
689 END IF;
690
691 -- Setting attributes
692 x_return_status := Set_Attributes(
693 p_num_scheme_id => p_num_scheme_id,
694 p_num_sequence_code => p_num_sequence_code,
695 p_sequence_level => p_sequence_level,
696 p_concatenation_yn => p_concatenation_yn,
697 p_end_character => p_end_character,
698 p_object_version_number => p_object_version_number,
699 x_concatenation_yn => l_concatenation_yn,
700 x_object_version_number => l_object_version_number,
701 x_end_character => l_end_character
702 );
703 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
705 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
706 RAISE FND_API.G_EXC_ERROR;
707 END IF;
708
709 -- Validate all non-missing attributes (Item Level Validation)
710 x_return_status := Validate_Record(
711 p_validation_level => p_validation_level,
712 p_num_scheme_id => p_num_scheme_id,
716 p_end_character => l_end_character
713 p_num_sequence_code => p_num_sequence_code,
714 p_sequence_level => p_sequence_level,
715 p_concatenation_yn => l_concatenation_yn,
717 );
718
719 /*IF (l_debug = 'Y') THEN
720 Okc_Debug.Log('3200: Leaving validate_row', 2);
721 END IF;*/
722
723 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
724 FND_LOG.STRING(G_PROC_LEVEL,
725 G_PKG_NAME, '3200: Leaving validate_row' );
726 END IF;
727
728 EXCEPTION
729 WHEN FND_API.G_EXC_ERROR THEN
730 /*IF (l_debug = 'Y') THEN
731 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
732 END IF;*/
733
734 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
735 FND_LOG.STRING(G_EXCP_LEVEL,
736 G_PKG_NAME, '3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception' );
737 END IF;
738 x_return_status := G_RET_STS_ERROR;
739
740 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
741 /*IF (l_debug = 'Y') THEN
742 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
743 END IF;*/
744
745 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
746 FND_LOG.STRING(G_EXCP_LEVEL,
747 G_PKG_NAME, '3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
748 END IF;
749 x_return_status := G_RET_STS_UNEXP_ERROR;
750
751 WHEN OTHERS THEN
752 /*IF (l_debug = 'Y') THEN
753 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
754 END IF;*/
755
756 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
757 FND_LOG.STRING(G_EXCP_LEVEL,
758 G_PKG_NAME, '3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm );
759 END IF;
760 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
761 p_msg_name => G_UNEXPECTED_ERROR,
762 p_token1 => G_SQLCODE_TOKEN,
763 p_token1_value => sqlcode,
764 p_token2 => G_SQLERRM_TOKEN,
765 p_token2_value => sqlerrm);
766 x_return_status := G_RET_STS_UNEXP_ERROR;
767
768 END Validate_Row;
769
770 ---------------------------------------------------------------------------
771 -- PROCEDURE Insert_Row
772 ---------------------------------------------------------------------------
773 -------------------------------------
774 -- Insert_Row for:OKC_NUMBER_SCHEME_DTLS --
775 -------------------------------------
776 FUNCTION Insert_Row(
777 p_num_scheme_id IN NUMBER,
778 p_num_sequence_code IN VARCHAR2,
779 p_sequence_level IN NUMBER,
780 p_concatenation_yn IN VARCHAR2,
781 p_end_character IN VARCHAR2,
782 p_object_version_number IN NUMBER,
783 p_created_by IN NUMBER,
784 p_creation_date IN DATE,
785 p_last_updated_by IN NUMBER,
786 p_last_update_login IN NUMBER,
787 p_last_update_date IN DATE
788
789 ) RETURN VARCHAR2 IS
790
791 BEGIN
792
793 /*IF (l_debug = 'Y') THEN
794 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
795 END IF;*/
796
797 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
798 FND_LOG.STRING(G_PROC_LEVEL,
799 G_PKG_NAME, '3600: Entered Insert_Row function' );
800 END IF;
801
802 INSERT INTO OKC_NUMBER_SCHEME_DTLS(
803 NUM_SCHEME_ID,
804 NUM_SEQUENCE_CODE,
805 SEQUENCE_LEVEL,
806 CONCATENATION_YN,
807 END_CHARACTER,
808 OBJECT_VERSION_NUMBER,
809 CREATED_BY,
810 CREATION_DATE,
811 LAST_UPDATED_BY,
812 LAST_UPDATE_LOGIN,
813 LAST_UPDATE_DATE)
814 VALUES (
815 p_num_scheme_id,
816 p_num_sequence_code,
817 p_sequence_level,
818 p_concatenation_yn,
819 p_end_character,
820 p_object_version_number,
821 p_created_by,
822 p_creation_date,
823 p_last_updated_by,
824 p_last_update_login,
825 p_last_update_date);
826
827 /*IF (l_debug = 'Y') THEN
828 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
829 END IF;*/
830
831 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
832 FND_LOG.STRING(G_PROC_LEVEL,
833 G_PKG_NAME, '3700: Leaving Insert_Row' );
834 END IF;
835
836 RETURN( G_RET_STS_SUCCESS );
837
838 EXCEPTION
839 WHEN OTHERS THEN
840
841 /*IF (l_debug = 'Y') THEN
842 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
843 END IF;*/
844
845 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
846 FND_LOG.STRING(G_EXCP_LEVEL,
847 G_PKG_NAME, '3800: Leaving Insert_Row:OTHERS Exception' );
848 END IF;
849
850 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
851 p_msg_name => G_UNEXPECTED_ERROR,
852 p_token1 => G_SQLCODE_TOKEN,
853 p_token1_value => sqlcode,
854 p_token2 => G_SQLERRM_TOKEN,
855 p_token2_value => sqlerrm);
856
857 RETURN( G_RET_STS_UNEXP_ERROR );
858
859 END Insert_Row;
860
861
862 -------------------------------------
863 -- Insert_Row for:OKC_NUMBER_SCHEME_DTLS --
864 -------------------------------------
865 PROCEDURE Insert_Row(
866 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
870 p_num_sequence_code IN VARCHAR2,
867 x_return_status OUT NOCOPY VARCHAR2,
868
869 p_num_scheme_id IN NUMBER,
871 p_sequence_level IN NUMBER,
872 p_concatenation_yn IN VARCHAR2,
873 p_end_character IN VARCHAR2,
874
875
876
877 x_num_scheme_id OUT NOCOPY NUMBER,
878 x_num_sequence_code OUT NOCOPY VARCHAR2,
879 x_sequence_level OUT NOCOPY NUMBER
880
881 ) IS
882
883 l_object_version_number OKC_NUMBER_SCHEME_DTLS.OBJECT_VERSION_NUMBER%TYPE;
884 l_created_by OKC_NUMBER_SCHEME_DTLS.CREATED_BY%TYPE;
885 l_creation_date OKC_NUMBER_SCHEME_DTLS.CREATION_DATE%TYPE;
886 l_last_updated_by OKC_NUMBER_SCHEME_DTLS.LAST_UPDATED_BY%TYPE;
887 l_last_update_login OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_LOGIN%TYPE;
888 l_last_update_date OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_DATE%TYPE;
889 BEGIN
890
891 x_return_status := G_RET_STS_SUCCESS;
892
893 /*IF (l_debug = 'Y') THEN
894 Okc_Debug.Log('4200: Entered Insert_Row', 2);
895 END IF;*/
896
897 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
898 FND_LOG.STRING(G_PROC_LEVEL,
899 G_PKG_NAME, '4200: Entered Insert_Row' );
900 END IF;
901
902 --- Setting item attributes
903 -- Set primary key value
904 x_num_scheme_id := p_num_scheme_id;
905 x_num_sequence_code := p_num_sequence_code;
906 x_sequence_level := p_sequence_level;
907 -- Set Internal columns
908 l_object_version_number := 1;
909 l_creation_date := Sysdate;
910 l_created_by := Fnd_Global.User_Id;
911 l_last_update_date := l_creation_date;
912 l_last_updated_by := l_created_by;
913 l_last_update_login := Fnd_Global.Login_Id;
914
915
916 --- Validate all non-missing attributes
917 x_return_status := Validate_Record(
918 p_validation_level => p_validation_level,
919 p_num_scheme_id => x_num_scheme_id,
920 p_num_sequence_code => x_num_sequence_code,
921 p_sequence_level => x_sequence_level,
922 p_concatenation_yn => p_concatenation_yn,
923 p_end_character => p_end_character
924 );
925 --- If any errors happen abort API
926 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
928 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
929 RAISE FND_API.G_EXC_ERROR;
930 END IF;
931
932 --------------------------------------------
933 -- Call the internal Insert_Row for each child record
934 --------------------------------------------
935 /*IF (l_debug = 'Y') THEN
936 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
937 END IF;*/
938
939 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
940 FND_LOG.STRING(G_PROC_LEVEL,
941 G_PKG_NAME, '4300: Call the internal Insert_Row for Base Table' );
942 END IF;
943
944 x_return_status := Insert_Row(
945 p_num_scheme_id => x_num_scheme_id,
946 p_num_sequence_code => x_num_sequence_code,
947 p_sequence_level => x_sequence_level,
948 p_concatenation_yn => p_concatenation_yn,
949 p_end_character => p_end_character,
950 p_object_version_number => l_object_version_number,
951 p_created_by => l_created_by,
952 p_creation_date => l_creation_date,
953 p_last_updated_by => l_last_updated_by,
954 p_last_update_login => l_last_update_login,
955 p_last_update_date => l_last_update_date
956 );
957 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
959 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
960 RAISE FND_API.G_EXC_ERROR;
961 END IF;
962
963
964
965 /*IF (l_debug = 'Y') THEN
966 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
967 END IF;*/
968
969 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
970 FND_LOG.STRING(G_PROC_LEVEL,
971 G_PKG_NAME, '4500: Leaving Insert_Row' );
972 END IF;
973
974 EXCEPTION
975 WHEN FND_API.G_EXC_ERROR THEN
976 /*IF (l_debug = 'Y') THEN
977 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
978 END IF;*/
979
980 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
981 FND_LOG.STRING(G_EXCP_LEVEL,
982 G_PKG_NAME, '4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception' );
983 END IF;
984 x_return_status := G_RET_STS_ERROR;
985
986 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
987 /*IF (l_debug = 'Y') THEN
988 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
989 END IF;*/
990
991 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
992 FND_LOG.STRING(G_EXCP_LEVEL,
993 G_PKG_NAME, '4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
994 END IF;
995 x_return_status := G_RET_STS_UNEXP_ERROR;
996
997 WHEN OTHERS THEN
998 /*IF (l_debug = 'Y') THEN
999 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1000 END IF;*/
1001
1002 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1003 FND_LOG.STRING(G_EXCP_LEVEL,
1004 G_PKG_NAME, '4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm );
1005 END IF;
1006 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1007 p_msg_name => G_UNEXPECTED_ERROR,
1008 p_token1 => G_SQLCODE_TOKEN,
1012 x_return_status := G_RET_STS_UNEXP_ERROR;
1009 p_token1_value => sqlcode,
1010 p_token2 => G_SQLERRM_TOKEN,
1011 p_token2_value => sqlerrm);
1013
1014 END Insert_Row;
1015 ---------------------------------------------------------------------------
1016 -- PROCEDURE Lock_Row
1017 ---------------------------------------------------------------------------
1018 -----------------------------------
1019 -- Lock_Row for:OKC_NUMBER_SCHEME_DTLS --
1020 -----------------------------------
1021 FUNCTION Lock_Row(
1022 p_num_scheme_id IN NUMBER,
1023 p_num_sequence_code IN VARCHAR2,
1024 p_sequence_level IN NUMBER,
1025 p_object_version_number IN NUMBER
1026 ) RETURN VARCHAR2 IS
1027
1028 CURSOR lock_csr (cp_num_scheme_id NUMBER, cp_num_sequence_code VARCHAR2, cp_sequence_level NUMBER, cp_object_version_number NUMBER) IS
1029 SELECT object_version_number
1030 FROM OKC_NUMBER_SCHEME_DTLS
1031 WHERE NUM_SCHEME_ID = cp_num_scheme_id AND NUM_SEQUENCE_CODE = cp_num_sequence_code AND SEQUENCE_LEVEL = cp_sequence_level
1032 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1033 FOR UPDATE OF object_version_number NOWAIT;
1034
1035 CURSOR lchk_csr (cp_num_scheme_id NUMBER, cp_num_sequence_code VARCHAR2, cp_sequence_level NUMBER) IS
1036 SELECT object_version_number
1037 FROM OKC_NUMBER_SCHEME_DTLS
1038 WHERE NUM_SCHEME_ID = cp_num_scheme_id AND NUM_SEQUENCE_CODE = cp_num_sequence_code AND SEQUENCE_LEVEL = cp_sequence_level;
1039
1040 l_return_status VARCHAR2(1);
1041
1042 l_object_version_number OKC_NUMBER_SCHEME_DTLS.OBJECT_VERSION_NUMBER%TYPE;
1043
1044 l_row_notfound BOOLEAN := FALSE;
1045 BEGIN
1046
1047 /*IF (l_debug = 'Y') THEN
1048 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1049 END IF;*/
1050
1051 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1052 FND_LOG.STRING(G_PROC_LEVEL,
1053 G_PKG_NAME, '4900: Entered Lock_Row' );
1054 END IF;
1055
1056 BEGIN
1057
1058 OPEN lock_csr( p_num_scheme_id, p_num_sequence_code, p_sequence_level, p_object_version_number );
1059 FETCH lock_csr INTO l_object_version_number;
1060 l_row_notfound := lock_csr%NOTFOUND;
1061 CLOSE lock_csr;
1062
1063 EXCEPTION
1064 WHEN E_Resource_Busy THEN
1065
1066 /*IF (l_debug = 'Y') THEN
1067 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1068 END IF;*/
1069
1070 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1071 FND_LOG.STRING(G_PROC_LEVEL,
1072 G_PKG_NAME, '5000: Leaving Lock_Row:E_Resource_Busy Exception' );
1073 END IF;
1074
1075 IF (lock_csr%ISOPEN) THEN
1076 CLOSE lock_csr;
1077 END IF;
1078 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1079 RETURN( G_RET_STS_ERROR );
1080 END;
1081
1082 IF ( l_row_notfound ) THEN
1083 l_return_status := G_RET_STS_ERROR;
1084
1085 OPEN lchk_csr(p_num_scheme_id, p_num_sequence_code, p_sequence_level);
1086 FETCH lchk_csr INTO l_object_version_number;
1087 l_row_notfound := lchk_csr%NOTFOUND;
1088 CLOSE lchk_csr;
1089
1090 IF (l_row_notfound) THEN
1091 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
1092 ELSIF l_object_version_number > p_object_version_number THEN
1093 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1094 ELSIF l_object_version_number = -1 THEN
1095 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1096 ELSE -- it can be the only above condition. It can happen after restore version
1097 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1098 END IF;
1099 ELSE
1100 l_return_status := G_RET_STS_SUCCESS;
1101 END IF;
1102
1103 /*IF (l_debug = 'Y') THEN
1104 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1105 END IF;*/
1106
1107 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1108 FND_LOG.STRING(G_PROC_LEVEL,
1109 G_PKG_NAME, '5100: Leaving Lock_Row' );
1110 END IF;
1111
1112 RETURN( l_return_status );
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116
1117 IF (lock_csr%ISOPEN) THEN
1118 CLOSE lock_csr;
1119 END IF;
1120 IF (lchk_csr%ISOPEN) THEN
1121 CLOSE lchk_csr;
1122 END IF;
1123
1124 /*IF (l_debug = 'Y') THEN
1125 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1126 END IF;*/
1127
1128 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1129 FND_LOG.STRING(G_EXCP_LEVEL,
1130 G_PKG_NAME, '5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
1131 END IF;
1132
1133 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1134 p_msg_name => G_UNEXPECTED_ERROR,
1135 p_token1 => G_SQLCODE_TOKEN,
1136 p_token1_value => sqlcode,
1137 p_token2 => G_SQLERRM_TOKEN,
1138 p_token2_value => sqlerrm);
1139
1140 RETURN( G_RET_STS_UNEXP_ERROR );
1141 END Lock_Row;
1142
1143 -----------------------------------
1144 -- Lock_Row for:OKC_NUMBER_SCHEME_DTLS --
1145 -----------------------------------
1146 PROCEDURE Lock_Row(
1147 x_return_status OUT NOCOPY VARCHAR2,
1148
1149 p_num_scheme_id IN NUMBER,
1150 p_num_sequence_code IN VARCHAR2,
1151 p_sequence_level IN NUMBER,
1152 p_object_version_number IN NUMBER
1153 ) IS
1154 BEGIN
1155
1159 END IF;*/
1156 /*IF (l_debug = 'Y') THEN
1157 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1158 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1160
1161 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1162 FND_LOG.STRING(G_PROC_LEVEL,
1163 G_PKG_NAME, '5700: Entered Lock_Row');
1164 FND_LOG.STRING(G_PROC_LEVEL,
1165 G_PKG_NAME, '5800: Locking Row for Base Table');
1166 END IF;
1167
1168 --------------------------------------------
1169 -- Call the LOCK_ROW for each _B child record
1170 --------------------------------------------
1171 x_return_status := Lock_Row(
1172 p_num_scheme_id => p_num_scheme_id,
1173 p_num_sequence_code => p_num_sequence_code,
1174 p_sequence_level => p_sequence_level,
1175 p_object_version_number => p_object_version_number
1176 );
1177 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1179 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1180 RAISE FND_API.G_EXC_ERROR;
1181 END IF;
1182
1183
1184
1185 /*IF (l_debug = 'Y') THEN
1186 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1187 END IF;*/
1188
1189 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1190 FND_LOG.STRING(G_PROC_LEVEL,
1191 G_PKG_NAME, '6000: Leaving Lock_Row' );
1192 END IF;
1193
1194 EXCEPTION
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 /*IF (l_debug = 'Y') THEN
1197 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1198 END IF;*/
1199
1200 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1201 FND_LOG.STRING(G_EXCP_LEVEL,
1202 G_PKG_NAME, '6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception' );
1203 END IF;
1204 x_return_status := G_RET_STS_ERROR;
1205
1206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1207 /*IF (l_debug = 'Y') THEN
1208 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1209 END IF;*/
1210
1211 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1212 FND_LOG.STRING(G_EXCP_LEVEL,
1213 G_PKG_NAME, '6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1214 END IF;
1215 x_return_status := G_RET_STS_UNEXP_ERROR;
1216
1217 WHEN OTHERS THEN
1218 /*IF (l_debug = 'Y') THEN
1219 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1220 END IF;*/
1221
1222 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1223 FND_LOG.STRING(G_EXCP_LEVEL,
1224 G_PKG_NAME, '6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
1225 END IF;
1226 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1227 p_msg_name => G_UNEXPECTED_ERROR,
1228 p_token1 => G_SQLCODE_TOKEN,
1229 p_token1_value => sqlcode,
1230 p_token2 => G_SQLERRM_TOKEN,
1231 p_token2_value => sqlerrm);
1232 x_return_status := G_RET_STS_UNEXP_ERROR;
1233
1234 END Lock_Row;
1235 ---------------------------------------------------------------------------
1236 -- PROCEDURE Update_Row
1237 ---------------------------------------------------------------------------
1238 -------------------------------------
1239 -- Update_Row for:OKC_NUMBER_SCHEME_DTLS --
1240 -------------------------------------
1241 FUNCTION Update_Row(
1242 p_num_scheme_id IN NUMBER,
1243 p_num_sequence_code IN VARCHAR2,
1244 p_sequence_level IN NUMBER,
1245 p_concatenation_yn IN VARCHAR2,
1246 p_end_character IN VARCHAR2,
1247 p_object_version_number IN NUMBER,
1248 p_last_updated_by IN NUMBER,
1249 p_last_update_login IN NUMBER,
1250 p_last_update_date IN DATE
1251 ) RETURN VARCHAR2 IS
1252
1253 BEGIN
1254
1255 /*IF (l_debug = 'Y') THEN
1256 Okc_Debug.Log('6400: Entered Update_Row', 2);
1257 END IF;*/
1258
1259 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1260 FND_LOG.STRING(G_PROC_LEVEL,
1261 G_PKG_NAME, '6400: Entered Update_Row' );
1262 END IF;
1263
1264 UPDATE OKC_NUMBER_SCHEME_DTLS
1265 SET CONCATENATION_YN = p_concatenation_yn,
1266 END_CHARACTER = p_end_character,
1267 OBJECT_VERSION_NUMBER = p_object_version_number,
1268 LAST_UPDATED_BY = p_last_updated_by,
1269 LAST_UPDATE_LOGIN = p_last_update_login,
1270 LAST_UPDATE_DATE = p_last_update_date
1271 WHERE NUM_SCHEME_ID = p_num_scheme_id AND NUM_SEQUENCE_CODE = p_num_sequence_code AND SEQUENCE_LEVEL = p_sequence_level;
1272
1273 /*IF (l_debug = 'Y') THEN
1274 Okc_Debug.Log('6500: Leaving Update_Row', 2);
1275 END IF;*/
1276
1277 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1278 FND_LOG.STRING(G_PROC_LEVEL,
1279 G_PKG_NAME, '6500: Leaving Update_Row' );
1280 END IF;
1281
1282 RETURN G_RET_STS_SUCCESS ;
1283
1284 EXCEPTION
1285 WHEN OTHERS THEN
1286
1287 /*IF (l_debug = 'Y') THEN
1288 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1289 END IF;*/
1290
1291 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1292 FND_LOG.STRING(G_EXCP_LEVEL,
1293 G_PKG_NAME, '6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
1294 END IF;
1295
1296 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1297 p_msg_name => G_UNEXPECTED_ERROR,
1298 p_token1 => G_SQLCODE_TOKEN,
1299 p_token1_value => sqlcode,
1303 RETURN G_RET_STS_UNEXP_ERROR ;
1300 p_token2 => G_SQLERRM_TOKEN,
1301 p_token2_value => sqlerrm);
1302
1304
1305 END Update_Row;
1306
1307 -------------------------------------
1308 -- Update_Row for:OKC_NUMBER_SCHEME_DTLS --
1309 -------------------------------------
1310 PROCEDURE Update_Row(
1311 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1312
1313 x_return_status OUT NOCOPY VARCHAR2,
1314
1315 p_num_scheme_id IN NUMBER,
1316 p_num_sequence_code IN VARCHAR2,
1317 p_sequence_level IN NUMBER,
1318 p_concatenation_yn IN VARCHAR2 := NULL,
1319 p_end_character IN VARCHAR2 := NULL,
1320
1321 p_object_version_number IN NUMBER
1322
1323 ) IS
1324
1325 l_concatenation_yn OKC_NUMBER_SCHEME_DTLS.CONCATENATION_YN%TYPE;
1326 l_end_character OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE;
1327 l_object_version_number OKC_NUMBER_SCHEME_DTLS.OBJECT_VERSION_NUMBER%TYPE;
1328 l_last_updated_by OKC_NUMBER_SCHEME_DTLS.LAST_UPDATED_BY%TYPE;
1329 l_last_update_login OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_LOGIN%TYPE;
1330 l_last_update_date OKC_NUMBER_SCHEME_DTLS.LAST_UPDATE_DATE%TYPE;
1331
1332 BEGIN
1333
1334 /*IF (l_debug = 'Y') THEN
1335 Okc_Debug.Log('7000: Entered Update_Row', 2);
1336 Okc_Debug.Log('7100: Locking _B row', 2);
1337 END IF;*/
1338
1339 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1340 FND_LOG.STRING(G_PROC_LEVEL,
1341 G_PKG_NAME, '7000: Entered Update_Row');
1342 FND_LOG.STRING(G_PROC_LEVEL,
1343 G_PKG_NAME, '7100: Locking _B row');
1344 END IF;
1345
1346 x_return_status := Lock_row(
1347 p_num_scheme_id => p_num_scheme_id,
1348 p_num_sequence_code => p_num_sequence_code,
1349 p_sequence_level => p_sequence_level,
1350 p_object_version_number => p_object_version_number
1351 );
1352 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1354 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1355 RAISE FND_API.G_EXC_ERROR;
1356 END IF;
1357
1358
1359 /*IF (l_debug = 'Y') THEN
1360 Okc_Debug.Log('7300: Setting attributes', 2);
1361 END IF;*/
1362
1363 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1364 FND_LOG.STRING(G_PROC_LEVEL,
1365 G_PKG_NAME, '7300: Setting attributes');
1366 END IF;
1367
1368 x_return_status := Set_Attributes(
1369 p_num_scheme_id => p_num_scheme_id,
1370 p_num_sequence_code => p_num_sequence_code,
1371 p_sequence_level => p_sequence_level,
1372 p_concatenation_yn => p_concatenation_yn,
1373 p_end_character => p_end_character,
1374 p_object_version_number => p_object_version_number,
1375 x_concatenation_yn => l_concatenation_yn,
1376 x_object_version_number => l_object_version_number,
1377 x_end_character => l_end_character
1378 );
1379 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1381 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384
1385 /*IF (l_debug = 'Y') THEN
1386 Okc_Debug.Log('7400: Record Validation', 2);
1387 END IF;*/
1388
1389 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1390 FND_LOG.STRING(G_PROC_LEVEL,
1391 G_PKG_NAME, '7400: Record Validation');
1392 END IF;
1393
1394 --- Validate all non-missing attributes
1395 x_return_status := Validate_Record(
1396 p_validation_level => p_validation_level,
1397 p_num_scheme_id => p_num_scheme_id,
1398 p_num_sequence_code => p_num_sequence_code,
1399 p_sequence_level => p_sequence_level,
1400 p_concatenation_yn => l_concatenation_yn,
1401 p_end_character => l_end_character
1402 );
1403 --- If any errors happen abort API
1404 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1407 RAISE FND_API.G_EXC_ERROR;
1408 END IF;
1409
1410 /*IF (l_debug = 'Y') THEN
1411 Okc_Debug.Log('7500: Filling WHO columns', 2);
1412 END IF;*/
1413
1414 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1415 FND_LOG.STRING(G_PROC_LEVEL,
1416 G_PKG_NAME, '7500: Filling WHO columns');
1417 END IF;
1418
1419 -- Filling who columns
1420 l_last_update_date := SYSDATE;
1421 l_last_updated_by := FND_GLOBAL.USER_ID;
1422 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1423
1424 -- Object version increment
1425 IF Nvl(l_object_version_number, 0) >= 0 THEN
1426 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
1427 END IF;
1428
1429 --------------------------------------------
1430 -- Call the Update_Row for each child record
1431 --------------------------------------------
1432 /*IF (l_debug = 'Y') THEN
1433 Okc_Debug.Log('7600: Updating Row', 2);
1434 END IF;*/
1435
1436 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1437 FND_LOG.STRING(G_PROC_LEVEL,
1438 G_PKG_NAME, '7600: Updating Row');
1439 END IF;
1440
1441 x_return_status := Update_Row(
1442 p_num_scheme_id => p_num_scheme_id,
1443 p_num_sequence_code => p_num_sequence_code,
1444 p_sequence_level => p_sequence_level,
1445 p_concatenation_yn => l_concatenation_yn,
1446 p_end_character => l_end_character,
1450 p_last_update_date => l_last_update_date
1447 p_object_version_number => l_object_version_number,
1448 p_last_updated_by => l_last_updated_by,
1449 p_last_update_login => l_last_update_login,
1451 );
1452 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1454 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1455 RAISE FND_API.G_EXC_ERROR;
1456 END IF;
1457
1458
1459 /*IF (l_debug = 'Y') THEN
1460 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1461 END IF;*/
1462
1463 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1464 FND_LOG.STRING(G_PROC_LEVEL,
1465 G_PKG_NAME, '7800: Leaving Update_Row');
1466 END IF;
1467
1468 EXCEPTION
1469 WHEN FND_API.G_EXC_ERROR THEN
1470 /*IF (l_debug = 'Y') THEN
1471 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1472 END IF;*/
1473
1474 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1475 FND_LOG.STRING(G_EXCP_LEVEL,
1476 G_PKG_NAME, '7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception' );
1477 END IF;
1478 x_return_status := G_RET_STS_ERROR;
1479
1480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1481 /*IF (l_debug = 'Y') THEN
1482 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1483 END IF;*/
1484
1485 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1486 FND_LOG.STRING(G_EXCP_LEVEL,
1487 G_PKG_NAME, '8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1488 END IF;
1489 x_return_status := G_RET_STS_UNEXP_ERROR;
1490
1491 WHEN OTHERS THEN
1492 /*IF (l_debug = 'Y') THEN
1493 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1494 END IF;*/
1495
1496 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1497 FND_LOG.STRING(G_EXCP_LEVEL,
1498 G_PKG_NAME, '8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
1499 END IF;
1500 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1501 p_msg_name => G_UNEXPECTED_ERROR,
1502 p_token1 => G_SQLCODE_TOKEN,
1503 p_token1_value => sqlcode,
1504 p_token2 => G_SQLERRM_TOKEN,
1505 p_token2_value => sqlerrm);
1506 x_return_status := G_RET_STS_UNEXP_ERROR;
1507
1508 END Update_Row;
1509
1510 ---------------------------------------------------------------------------
1511 -- PROCEDURE Delete_Row
1512 ---------------------------------------------------------------------------
1513 -------------------------------------
1514 -- Delete_Row for:OKC_NUMBER_SCHEME_DTLS --
1515 -------------------------------------
1516 FUNCTION Delete_Row(
1517 p_num_scheme_id IN NUMBER,
1518 p_num_sequence_code IN VARCHAR2,
1519 p_sequence_level IN NUMBER
1520 ) RETURN VARCHAR2 IS
1521
1522 BEGIN
1523
1524 /*IF (l_debug = 'Y') THEN
1525 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1526 END IF;*/
1527
1528 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1529 FND_LOG.STRING(G_PROC_LEVEL,
1530 G_PKG_NAME, '8200: Entered Delete_Row' );
1531 END IF;
1532
1533 DELETE FROM OKC_NUMBER_SCHEME_DTLS
1534 WHERE NUM_SCHEME_ID = p_NUM_SCHEME_ID AND NUM_SEQUENCE_CODE = p_NUM_SEQUENCE_CODE AND SEQUENCE_LEVEL = p_SEQUENCE_LEVEL;
1535
1536 /*IF (l_debug = 'Y') THEN
1537 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1538 END IF;*/
1539
1540 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1541 FND_LOG.STRING(G_PROC_LEVEL,
1542 G_PKG_NAME, '8300: Leaving Delete_Row' );
1543 END IF;
1544
1545 RETURN( G_RET_STS_SUCCESS );
1546
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549
1550 /*IF (l_debug = 'Y') THEN
1551 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1552 END IF;*/
1553
1554 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1555 FND_LOG.STRING(G_EXCP_LEVEL,
1556 G_PKG_NAME, '8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
1557 END IF;
1558
1559 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1560 p_msg_name => G_UNEXPECTED_ERROR,
1561 p_token1 => G_SQLCODE_TOKEN,
1562 p_token1_value => sqlcode,
1563 p_token2 => G_SQLERRM_TOKEN,
1564 p_token2_value => sqlerrm);
1565
1566 RETURN( G_RET_STS_UNEXP_ERROR );
1567
1568 END Delete_Row;
1569
1570 -------------------------------------
1571 -- Delete_Row for:OKC_NUMBER_SCHEME_DTLS --
1572 -------------------------------------
1573 PROCEDURE Delete_Row(
1574 x_return_status OUT NOCOPY VARCHAR2,
1575 p_num_scheme_id IN NUMBER,
1576 p_num_sequence_code IN VARCHAR2,
1577 p_sequence_level IN NUMBER,
1578 p_object_version_number IN NUMBER
1579 ) IS
1580 BEGIN
1581
1582 /*IF (l_debug = 'Y') THEN
1583 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1584 Okc_Debug.Log('8900: Locking _B row', 2);
1585 END IF;*/
1586
1587 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1588 FND_LOG.STRING(G_PROC_LEVEL,
1589 G_PKG_NAME, '8800: Entered Delete_Row');
1590 FND_LOG.STRING(G_PROC_LEVEL,
1591 G_PKG_NAME, '8900: Locking _B row');
1592 END IF;
1593
1594 x_return_status := Lock_row(
1595 p_num_scheme_id => p_num_scheme_id,
1599 );
1596 p_num_sequence_code => p_num_sequence_code,
1597 p_sequence_level => p_sequence_level,
1598 p_object_version_number => p_object_version_number
1600 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1602 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF;
1605
1606
1607 /*IF (l_debug = 'Y') THEN
1608 Okc_Debug.Log('9100: Removing _B row', 2);
1609 END IF;*/
1610
1611 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1612 FND_LOG.STRING(G_PROC_LEVEL,
1613 G_PKG_NAME, '9100: Removing _B row' );
1614 END IF;
1615 x_return_status := Delete_Row( p_num_scheme_id => p_num_scheme_id,p_num_sequence_code => p_num_sequence_code,p_sequence_level => p_sequence_level );
1616 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1618 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1619 RAISE FND_API.G_EXC_ERROR;
1620 END IF;
1621
1622 /*IF (l_debug = 'Y') THEN
1623 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1624 END IF;*/
1625
1626 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1627 FND_LOG.STRING(G_PROC_LEVEL,
1628 G_PKG_NAME, '9300: Leaving Delete_Row' );
1629 END IF;
1630
1631 EXCEPTION
1632 WHEN FND_API.G_EXC_ERROR THEN
1633 /*IF (l_debug = 'Y') THEN
1634 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1635 END IF;*/
1636
1637 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1638 FND_LOG.STRING(G_EXCP_LEVEL,
1639 G_PKG_NAME, '9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception' );
1640 END IF;
1641 x_return_status := G_RET_STS_ERROR;
1642
1643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1644 /*IF (l_debug = 'Y') THEN
1645 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1646 END IF;*/
1647
1648 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1649 FND_LOG.STRING(G_EXCP_LEVEL,
1650 G_PKG_NAME, '9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1651 END IF;
1652 x_return_status := G_RET_STS_UNEXP_ERROR;
1653
1654 WHEN OTHERS THEN
1655 /*IF (l_debug = 'Y') THEN
1656 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1657 END IF;*/
1658
1659 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1660 FND_LOG.STRING(G_EXCP_LEVEL,
1661 G_PKG_NAME, '9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
1662 END IF;
1663
1664 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1665 p_msg_name => G_UNEXPECTED_ERROR,
1666 p_token1 => G_SQLCODE_TOKEN,
1667 p_token1_value => sqlcode,
1668 p_token2 => G_SQLERRM_TOKEN,
1669 p_token2_value => sqlerrm);
1670 x_return_status := G_RET_STS_UNEXP_ERROR;
1671
1672 END Delete_Row;
1673
1674 PROCEDURE delete_set(
1675 x_return_status OUT NOCOPY VARCHAR2,
1676
1677 p_num_scheme_id IN NUMBER
1678 ) IS
1679 CURSOR lock_csr IS
1680 SELECT rowid
1681 FROM OKC_NUMBER_SCHEME_DTLS
1682 WHERE NUM_SCHEME_ID = p_num_scheme_id
1683 FOR UPDATE NOWAIT;
1684
1685 BEGIN
1686 /*IF (l_debug = 'Y') THEN
1687 Okc_Debug.Log('9700: Entered Delete_Set', 2);
1688 Okc_Debug.Log('9800: Locking the Set', 2);
1689 END IF;*/
1690
1691 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1692 FND_LOG.STRING(G_PROC_LEVEL,
1693 G_PKG_NAME, '9700: Entered Delete_Set');
1694 FND_LOG.STRING(G_PROC_LEVEL,
1695 G_PKG_NAME, '9800: Locking the Set');
1696 END IF;
1697
1698 -- making OPEN/CLOSE cursor to lock records
1699 OPEN lock_csr;
1700 CLOSE lock_csr;
1701
1702 /*IF (l_debug = 'Y') THEN
1703 Okc_Debug.Log('9900: Deleting the Set', 2);
1704 END IF;*/
1705
1706 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1707 FND_LOG.STRING(G_PROC_LEVEL,
1708 G_PKG_NAME, '9900: Deleting the Set' );
1709 END IF;
1710
1711 DELETE FROM OKC_NUMBER_SCHEME_DTLS
1712 WHERE NUM_SCHEME_ID = p_NUM_SCHEME_ID ;
1713
1714 /*IF (l_debug = 'Y') THEN
1715 Okc_Debug.Log('10000: Leaving Delete_set', 2);
1716 END IF;*/
1717
1718 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1719 FND_LOG.STRING(G_PROC_LEVEL,
1720 G_PKG_NAME, '10000: Leaving Delete_set' );
1721 END IF;
1722 EXCEPTION
1723 WHEN E_Resource_Busy THEN
1724 /*IF (l_debug = 'Y') THEN
1725 Okc_Debug.Log('11000: Leaving Delete_set:E_Resource_Busy Exception', 2);
1726 END IF;*/
1727
1728 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1729 FND_LOG.STRING(G_EXCP_LEVEL,
1730 G_PKG_NAME, '11000: Leaving Delete_set:E_Resource_Busy Exception' );
1731 END IF;
1732
1733 IF (lock_csr%ISOPEN) THEN
1734 CLOSE lock_csr;
1735 END IF;
1736 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
1737 x_return_status := G_RET_STS_ERROR ;
1738
1739 WHEN FND_API.G_EXC_ERROR THEN
1740 /*IF (l_debug = 'Y') THEN
1741 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
1742 END IF;*/
1743
1744 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1745 FND_LOG.STRING(G_EXCP_LEVEL,
1746 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
1747 END IF;
1748 x_return_status := G_RET_STS_ERROR;
1752 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1749
1750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1751 /*IF (l_debug = 'Y') THEN
1753 END IF;*/
1754
1755 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1756 FND_LOG.STRING(G_EXCP_LEVEL,
1757 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1758 END IF;
1759 x_return_status := G_RET_STS_UNEXP_ERROR;
1760
1761 WHEN OTHERS THEN
1762 /*IF (l_debug = 'Y') THEN
1763 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
1764 END IF;*/
1765
1766 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1767 FND_LOG.STRING(G_EXCP_LEVEL,
1768 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
1769 END IF;
1770
1771 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1772 p_msg_name => G_UNEXPECTED_ERROR,
1773 p_token1 => G_SQLCODE_TOKEN,
1774 p_token1_value => sqlcode,
1775 p_token2 => G_SQLERRM_TOKEN,
1776 p_token2_value => sqlerrm);
1777 x_return_status := G_RET_STS_UNEXP_ERROR;
1778
1779 END Delete_Set;
1780
1781 END OKC_NUMBER_SCHEME_DTL_PVT;