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