[Home] [Help]
PACKAGE BODY: APPS.OKC_K_ART_VARIABLES_PVT
Source
1 PACKAGE BODY OKC_K_ART_VARIABLES_PVT AS
2 /* $Header: OKCVVARB.pls 120.1 2005/11/06 23:01:01 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_K_ART_VARIABLES_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 E_Resource_Busy EXCEPTION;
55 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
56
57 ---------------------------------------------------------------------------
58 -- FUNCTION get_rec for: OKC_K_ART_VARIABLES
59 ---------------------------------------------------------------------------
60 FUNCTION Get_Rec (
61 p_cat_id IN NUMBER,
62 p_variable_code IN VARCHAR2,
63 x_variable_type OUT NOCOPY VARCHAR2,
64 x_external_yn OUT NOCOPY VARCHAR2,
65 x_variable_value_id OUT NOCOPY VARCHAR2,
66 x_variable_value OUT NOCOPY VARCHAR2,
67 x_attribute_value_set_id OUT NOCOPY NUMBER,
68 x_override_global_yn OUT NOCOPY VARCHAR2,
69 x_object_version_number OUT NOCOPY NUMBER,
70 x_created_by OUT NOCOPY NUMBER,
71 x_creation_date OUT NOCOPY DATE,
72 x_last_updated_by OUT NOCOPY NUMBER,
73 x_last_update_login OUT NOCOPY NUMBER,
74 x_last_update_date OUT NOCOPY DATE
75
76 ) RETURN VARCHAR2 IS
77 CURSOR OKC_K_ART_VARIABLES_pk_csr (cp_cat_id IN NUMBER,cp_variable_code IN VARCHAR2) IS
78 SELECT
79 VARIABLE_TYPE,
80 EXTERNAL_YN,
81 VARIABLE_VALUE_ID,
82 VARIABLE_VALUE,
83 ATTRIBUTE_VALUE_SET_ID,
84 OVERRiDE_GLOBAL_YN,
85 OBJECT_VERSION_NUMBER,
86 CREATED_BY,
87 CREATION_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN,
90 LAST_UPDATE_DATE
91 FROM OKC_K_ART_VARIABLES t
92 WHERE t.CAT_ID = cp_cat_id and
93 t.VARIABLE_CODE = cp_variable_code;
94 BEGIN
95
96 /*IF (l_debug = 'Y') THEN
97 Okc_Debug.Log('400: Entered get_rec', 2);
98 END IF;*/
99
100 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
101 FND_LOG.STRING(G_PROC_LEVEL,
102 G_PKG_NAME, '400: Entered get_rec' );
103 END IF;
104
105 -- Get current database values
106 OPEN OKC_K_ART_VARIABLES_pk_csr (p_cat_id, p_variable_code);
107 FETCH OKC_K_ART_VARIABLES_pk_csr INTO
108 x_variable_type,
109 x_external_yn,
110 x_variable_value_id,
111 x_variable_value,
112 x_attribute_value_set_id,
113 x_override_global_yn,
114 x_object_version_number,
115 x_created_by,
116 x_creation_date,
117 x_last_updated_by,
118 x_last_update_login,
119 x_last_update_date;
120 IF OKC_K_ART_VARIABLES_pk_csr%NOTFOUND THEN
121 RAISE NO_DATA_FOUND;
122 END IF;
123 CLOSE OKC_K_ART_VARIABLES_pk_csr;
124
125 /*IF (l_debug = 'Y') THEN
126 Okc_Debug.Log('500: Leaving get_rec ', 2);
127 END IF;*/
128
129 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
130 FND_LOG.STRING(G_PROC_LEVEL,
131 G_PKG_NAME, '500: Leaving get_rec ' );
132 END IF;
133
134 RETURN G_RET_STS_SUCCESS ;
135
136 EXCEPTION
137 WHEN OTHERS THEN
138
139 /*IF (l_debug = 'Y') THEN
140 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
141 END IF;*/
142
143 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
144 FND_LOG.STRING(G_PROC_LEVEL,
145 G_PKG_NAME, '600: Leaving get_rec because of EXCEPTION: '||sqlerrm );
146 END IF;
147
148 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
149 p_msg_name => G_UNEXPECTED_ERROR,
150 p_token1 => G_SQLCODE_TOKEN,
151 p_token1_value => sqlcode,
152 p_token2 => G_SQLERRM_TOKEN,
153 p_token2_value => sqlerrm);
154
155 IF OKC_K_ART_VARIABLES_pk_csr%ISOPEN THEN
156 CLOSE OKC_K_ART_VARIABLES_pk_csr;
157 END IF;
158
159 RETURN G_RET_STS_UNEXP_ERROR ;
160
161 END Get_Rec;
162
163 -----------------------------------------
164 -- Set_Attributes for:OKC_K_ART_VARIABLES --
165 -----------------------------------------
166 FUNCTION Set_Attributes(
167 p_cat_id IN NUMBER,
168 p_variable_code IN VARCHAR2,
169 p_variable_type IN VARCHAR2,
170 p_external_yn IN VARCHAR2,
171 p_variable_value_id IN VARCHAR2,
172 p_variable_value IN VARCHAR2,
173 p_attribute_value_set_id IN NUMBER,
174 p_override_global_yn IN VARCHAR2,
175 p_object_version_number IN NUMBER,
176
177 x_variable_type OUT NOCOPY VARCHAR2,
178 x_object_version_number OUT NOCOPY VARCHAR2,
179 x_external_yn OUT NOCOPY VARCHAR2,
180 x_variable_value_id OUT NOCOPY VARCHAR2,
181 x_variable_value OUT NOCOPY VARCHAR2,
182 x_attribute_value_set_id OUT NOCOPY NUMBER,
183 x_override_global_yn OUT NOCOPY VARCHAR2
184 ) RETURN VARCHAR2 IS
185 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
186 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
187 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
188 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
189 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
190 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
191 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
192 BEGIN
193 /*IF (l_debug = 'Y') THEN
194 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
195 END IF;*/
196
197 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
198 FND_LOG.STRING(G_PROC_LEVEL,
199 G_PKG_NAME, '700: Entered Set_Attributes ' );
200 END IF;
201
202 IF( p_cat_id IS NOT NULL AND p_variable_code IS NOT NULL ) THEN
203 -- Get current database values
204 l_return_status := Get_Rec(
205 p_cat_id => p_cat_id,
206 p_variable_code => p_variable_code,
207 x_variable_type => x_variable_type,
208 x_external_yn => x_external_yn,
209 x_variable_value_id => x_variable_value_id,
210 x_variable_value => x_variable_value,
211 x_attribute_value_set_id => x_attribute_value_set_id,
212 x_override_global_yn => x_override_global_yn,
213 x_object_version_number => x_object_version_number,
214 x_created_by => l_created_by,
215 x_creation_date => l_creation_date,
216 x_last_updated_by => l_last_updated_by,
217 x_last_update_login => l_last_update_login,
218 x_last_update_date => l_last_update_date
219 );
220 --- If any errors happen abort API
221 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 --- Reversing G_MISS/NULL values logic
228
229 IF (p_variable_type = G_MISS_CHAR) THEN
230 x_variable_type := NULL;
231 ELSIF (p_VARIABLE_TYPE IS NOT NULL) THEN
232 x_variable_type := p_variable_type;
233 END IF;
234
235 IF (p_external_yn = G_MISS_CHAR) THEN
236 x_external_yn := NULL;
237 ELSIF (p_EXTERNAL_YN IS NOT NULL) THEN
238 x_external_yn := p_external_yn;
239 x_external_yn := Upper( x_external_yn );
240 END IF;
241
242 IF (p_variable_value_id = G_MISS_CHAR) THEN
243 x_variable_value_id := NULL;
244 ELSIF (p_VARIABLE_VALUE_ID IS NOT NULL) THEN
245 x_variable_value_id := p_variable_value_id;
246 END IF;
247
248 IF (p_variable_value = G_MISS_CHAR) THEN
249 x_variable_value := NULL;
250 ELSIF (p_VARIABLE_VALUE IS NOT NULL) THEN
251 x_variable_value := p_variable_value;
252 END IF;
253
254 IF (p_attribute_value_set_id = G_MISS_NUM) THEN
255 x_attribute_value_set_id := NULL;
256 ELSIF (p_ATTRIBUTE_VALUE_SET_ID IS NOT NULL) THEN
257 x_attribute_value_set_id := p_attribute_value_set_id;
258 END IF;
259
260
261 IF (p_override_global_yn = G_MISS_CHAR) THEN
262 x_override_global_yn := NULL;
263 ELSIF (p_override_global_yn IS NOT NULL) THEN
264 x_override_global_yn := p_override_global_yn;
265 x_override_global_yn := upper(x_override_global_yn);
266 END IF;
267 END IF;
268
269 /*IF (l_debug = 'Y') THEN
270 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
271 END IF;*/
272
273 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
274 FND_LOG.STRING(G_PROC_LEVEL,
275 G_PKG_NAME, '800: Leaving Set_Attributes ' );
276 END IF;
277
278 RETURN G_RET_STS_SUCCESS ;
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 /*IF (l_debug = 'Y') THEN
282 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
283 END IF;*/
284
285 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
286 FND_LOG.STRING(G_PROC_LEVEL,
287 G_PKG_NAME, '900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception' );
288 END IF;
289 RETURN G_RET_STS_ERROR;
290
291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292 /*IF (l_debug = 'Y') THEN
293 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
294 END IF;*/
295
296 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
297 FND_LOG.STRING(G_PROC_LEVEL,
298 G_PKG_NAME, '1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
299 END IF;
300 RETURN G_RET_STS_UNEXP_ERROR;
301
302 WHEN OTHERS THEN
303 /*IF (l_debug = 'Y') THEN
304 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
305 END IF;*/
306
307 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
308 FND_LOG.STRING(G_PROC_LEVEL,
309 G_PKG_NAME, '1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm );
310 END IF;
311 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
312 p_msg_name => G_UNEXPECTED_ERROR,
313 p_token1 => G_SQLCODE_TOKEN,
314 p_token1_value => sqlcode,
315 p_token2 => G_SQLERRM_TOKEN,
316 p_token2_value => sqlerrm);
317 RETURN G_RET_STS_UNEXP_ERROR;
318
319 END Set_Attributes ;
320
321 ----------------------------------------------
322 -- Validate_Attributes for: OKC_K_ART_VARIABLES --
323 ----------------------------------------------
324 FUNCTION Validate_Attributes (
325 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
326
327 p_cat_id IN NUMBER,
328 p_variable_code IN VARCHAR2,
329 p_variable_type IN VARCHAR2,
330 p_external_yn IN VARCHAR2,
331 p_variable_value_id IN VARCHAR2,
332 p_variable_value IN VARCHAR2,
333 p_attribute_value_set_id IN NUMBER,
334 p_override_global_yn IN VARCHAR2
335 ) RETURN VARCHAR2 IS
336 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
337 l_dummy_var VARCHAR2(1) := '?';
338
339 CURSOR l_cat_id_csr is
340 SELECT '!'
341 FROM OKC_K_ARTICLES_B
342 WHERE ID = p_cat_id;
343
344 CURSOR l_attribute_value_set_id_csr is
345 SELECT '!'
346 FROM fnd_flex_value_sets
347 WHERE FLEX_VALUE_SET_ID = p_attribute_value_set_id;
348
349 CURSOR l_variable_code_csr is
350 SELECT '!'
351 FROM OKC_BUS_VARIABLES_B
352 WHERE VARIABLE_CODE = p_VARIABLE_CODE;
353 BEGIN
354
355 /*IF (l_debug = 'Y') THEN
356 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
357 END IF;*/
358
359 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
360 FND_LOG.STRING(G_PROC_LEVEL,
361 G_PKG_NAME, '1200: Entered Validate_Attributes' );
362 END IF;
363
364 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
365 /*IF (l_debug = 'Y') THEN
366 Okc_Debug.Log('1300: required values validation', 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, '1300: required values validation' );
372 END IF;
373
374 /*IF (l_debug = 'Y') THEN
375 Okc_Debug.Log('1400: - attribute CAT_ID ', 2);
376 END IF;*/
377
378 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
379 FND_LOG.STRING(G_PROC_LEVEL,
380 G_PKG_NAME, '1400: - attribute CAT_ID ' );
381 END IF;
382 IF ( p_cat_id IS NULL) THEN
383 /*IF (l_debug = 'Y') THEN
384 Okc_Debug.Log('1500: - attribute CAT_ID is invalid', 2);
385 END IF;*/
386
387 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
388 FND_LOG.STRING(G_PROC_LEVEL,
389 G_PKG_NAME, '1500: - attribute CAT_ID is invalid' );
390 END IF;
391 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CAT_ID');
392 l_return_status := G_RET_STS_ERROR;
393 END IF;
394
395 /*IF (l_debug = 'Y') THEN
396 Okc_Debug.Log('1400: - attribute VARIABLE_CODE ', 2);
397 END IF;*/
398
399 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
400 FND_LOG.STRING(G_PROC_LEVEL,
401 G_PKG_NAME, '1400: - attribute VARIABLE_CODE ' );
402 END IF;
403 IF ( p_variable_code IS NULL) THEN
404 /*IF (l_debug = 'Y') THEN
405 Okc_Debug.Log('1500: - attribute VARIABLE_CODE is invalid', 2);
406 END IF;*/
407
408 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
409 FND_LOG.STRING(G_PROC_LEVEL,
410 G_PKG_NAME, '1500: - attribute VARIABLE_CODE is invalid' );
411 END IF;
412 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'VARIABLE_CODE');
413 l_return_status := G_RET_STS_ERROR;
414 END IF;
415
416 /*IF (l_debug = 'Y') THEN
417 Okc_Debug.Log('1400: - attribute VARIABLE_TYPE ', 2);
418 END IF;*/
419
420 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
421 FND_LOG.STRING(G_PROC_LEVEL,
422 G_PKG_NAME, '1400: - attribute VARIABLE_TYPE ' );
423 END IF;
424 IF ( p_variable_type IS NULL) THEN
425 /*IF (l_debug = 'Y') THEN
426 Okc_Debug.Log('1500: - attribute VARIABLE_TYPE is invalid', 2);
427 END IF;*/
428
429 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
430 FND_LOG.STRING(G_PROC_LEVEL,
431 G_PKG_NAME, '1500: - attribute VARIABLE_TYPE is invalid' );
432 END IF;
433 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'VARIABLE_TYPE');
434 l_return_status := G_RET_STS_ERROR;
435 END IF;
436
437 /*IF (l_debug = 'Y') THEN
438 Okc_Debug.Log('1400: - attribute EXTERNAL_YN ', 2);
439 END IF;*/
440
441 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
442 FND_LOG.STRING(G_PROC_LEVEL,
443 G_PKG_NAME, '1400: - attribute EXTERNAL_YN ' );
444 END IF;
445 IF ( p_external_yn IS NULL) THEN
446 /*IF (l_debug = 'Y') THEN
447 Okc_Debug.Log('1500: - attribute EXTERNAL_YN is invalid', 2);
448 END IF;*/
449
450 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
451 FND_LOG.STRING(G_PROC_LEVEL,
452 G_PKG_NAME, '1500: - attribute EXTERNAL_YN is invalid' );
453 END IF;
454 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EXTERNAL_YN');
455 l_return_status := G_RET_STS_ERROR;
456 END IF;
457
458 END IF;
459
460 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
461 /*IF (l_debug = 'Y') THEN
462 Okc_Debug.Log('1600: static values and range validation', 2);
463 END IF;*/
464
465 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
466 FND_LOG.STRING(G_PROC_LEVEL,
467 G_PKG_NAME, '1600: static values and range validation' );
468 END IF;
469
470 /*IF (l_debug = 'Y') THEN
471 Okc_Debug.Log('1700: - attribute EXTERNAL_YN ', 2);
472 END IF;*/
473
474 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
475 FND_LOG.STRING(G_PROC_LEVEL,
476 G_PKG_NAME, '1700: - attribute EXTERNAL_YN ' );
477 END IF;
478
479 IF ( p_external_yn NOT IN ('Y','N') AND p_external_yn IS NOT NULL) THEN
480 /*IF (l_debug = 'Y') THEN
481 Okc_Debug.Log('1800: - attribute EXTERNAL_YN is invalid', 2);
482 END IF;*/
483
484 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
485 FND_LOG.STRING(G_PROC_LEVEL,
486 G_PKG_NAME, '1800: - attribute EXTERNAL_YN is invalid' );
487 END IF;
488 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'EXTERNAL_YN');
489 l_return_status := G_RET_STS_ERROR;
490 END IF;
491
492
493 /*IF (l_debug = 'Y') THEN
494 Okc_Debug.Log('1700: - attribute OVERRIDE_GLOBAL_YN ', 2);
495 END IF;*/
496
497 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
498 FND_LOG.STRING(G_PROC_LEVEL,
499 G_PKG_NAME, '1700: - attribute OVERRIDE_GLOBAL_YN ' );
500 END IF;
501
502 IF ( p_OVERRIDE_GLOBAL_YN NOT IN ('Y','N') AND p_OVERRIDE_GLOBAL_YN IS NOT NULL) THEN
503 /*IF (l_debug = 'Y') THEN
504 Okc_Debug.Log('1800: - attribute OVERRIDE_GLOBAL_YN 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, '1800: - attribute OVERRIDE_GLOBAL_YN is invalid' );
510 END IF;
511
512 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'OVERRIDE_GLOBAL_YN');
513 l_return_status := G_RET_STS_ERROR;
514 END IF;
515 END IF;
516
517 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
518 /*IF (l_debug = 'Y') THEN
519 Okc_Debug.Log('1900: lookup codes validation', 2);
520 END IF;*/
521
522 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
523 FND_LOG.STRING(G_PROC_LEVEL,
524 G_PKG_NAME, '1900: lookup codes validation' );
525 END IF;
526 END IF;
527
528 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
529 /*IF (l_debug = 'Y') THEN
530 Okc_Debug.Log('2100: foreign keys validation ', 2);
531 END IF;*/
532
533 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
534 FND_LOG.STRING(G_PROC_LEVEL,
535 G_PKG_NAME, '2100: foreign keys validation ' );
536 END IF;
537
538 /*IF (l_debug = 'Y') THEN
539 Okc_Debug.Log('2200: - attribute CAT_ID ', 2);
540 END IF;*/
541
542 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
543 FND_LOG.STRING(G_PROC_LEVEL,
544 G_PKG_NAME, '2200: - attribute CAT_ID ' );
545 END IF;
546
547 IF p_cat_id IS NOT NULL THEN
548 l_dummy_var := '?';
549 OPEN l_cat_id_csr;
550 FETCH l_cat_id_csr INTO l_dummy_var;
551 CLOSE l_cat_id_csr;
552 IF (l_dummy_var = '?') THEN
553 /*IF (l_debug = 'Y') THEN
554 Okc_Debug.Log('2300: - attribute CAT_ID is invalid', 2);
555 END IF;*/
556
557 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
558 FND_LOG.STRING(G_PROC_LEVEL,
559 G_PKG_NAME, '2300: - attribute CAT_ID is invalid' );
560 END IF;
561 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CAT_ID');
562 l_return_status := G_RET_STS_ERROR;
563 END IF;
564 END IF;
565 /*
566 -- IF (l_debug = 'Y') THEN
567 -- Okc_Debug.Log('2200: - attribute VARIABLE_VALUE_ID ', 2);
568 -- END IF;
569 -- IF p_variable_value_id IS NOT NULL THEN
570 -- l_dummy_var := '?';
571 -- OPEN l_variable_value_id_csr;
572 -- FETCH l_variable_value_id_csr INTO l_dummy_var;
573 -- CLOSE l_variable_value_id_csr;
574 -- IF (l_dummy_var = '?') THEN
575 -- IF (l_debug = 'Y') THEN
576 -- Okc_Debug.Log('2300: - attribute VARIABLE_VALUE_ID is invalid', 2);
577 -- END IF;
578 -- Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'VARIABLE_VALUE_ID');
579 -- l_return_status := G_RET_STS_ERROR;
580 -- END IF;
581 -- END IF;
582 */
583 /*IF (l_debug = 'Y') THEN
584 Okc_Debug.Log('2200: - attribute ATTRIBUTE_VALUE_SET_ID ', 2);
585 END IF;*/
586
587 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
588 FND_LOG.STRING(G_PROC_LEVEL,
589 G_PKG_NAME, '2200: - attribute ATTRIBUTE_VALUE_SET_ID ' );
590 END IF;
591 IF p_attribute_value_set_id IS NOT NULL THEN
592 l_dummy_var := '?';
593 OPEN l_attribute_value_set_id_csr;
594 FETCH l_attribute_value_set_id_csr INTO l_dummy_var;
595 CLOSE l_attribute_value_set_id_csr;
596 IF (l_dummy_var = '?') THEN
597 /*IF (l_debug = 'Y') THEN
598 Okc_Debug.Log('2300: - attribute ATTRIBUTE_VALUE_SET_ID is invalid', 2);
599 END IF;*/
600
601 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
602 FND_LOG.STRING(G_PROC_LEVEL,
603 G_PKG_NAME, '2300: - attribute ATTRIBUTE_VALUE_SET_ID is invalid' );
604 END IF;
605 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ATTRIBUTE_VALUE_SET_ID');
606 l_return_status := G_RET_STS_ERROR;
607 END IF;
608 END IF;
609
610 /*IF (l_debug = 'Y') THEN
611 Okc_Debug.Log('2200: - attribute VARIABLE_CODE ', 2);
612 END IF;*/
613
614 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
615 FND_LOG.STRING(G_PROC_LEVEL,
616 G_PKG_NAME, '2200: - attribute VARIABLE_CODE ' );
617 END IF;
618 IF p_variable_code IS NOT NULL THEN
619 l_dummy_var := '?';
620 OPEN l_variable_code_csr;
621 FETCH l_variable_code_csr INTO l_dummy_var;
622 CLOSE l_variable_code_csr;
623 IF (l_dummy_var = '?') THEN
624 /*IF (l_debug = 'Y') THEN
625 Okc_Debug.Log('2300: - attribute VARIABLE_CODE is invalid', 2);
626 END IF;*/
627
628 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
629 FND_LOG.STRING(G_PROC_LEVEL,
630 G_PKG_NAME, '2300: - attribute VARIABLE_CODE is invalid' );
631 END IF;
632 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'VARIABLE_CODE');
633 l_return_status := G_RET_STS_ERROR;
634 END IF;
635 END IF;
636 END IF;
637
638
639 /*IF (l_debug = 'Y') THEN
640 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
641 END IF;*/
642
643 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
644 FND_LOG.STRING(G_PROC_LEVEL,
645 G_PKG_NAME, '2400: Leaving Validate_Attributes ' );
646 END IF;
647 RETURN l_return_status;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 --Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
652 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
653 FND_LOG.STRING(G_EXCP_LEVEL,
654 G_PKG_NAME, '2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm );
655 END IF;
656 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
657 p_msg_name => G_UNEXPECTED_ERROR,
658 p_token1 => G_SQLCODE_TOKEN,
659 p_token1_value => sqlcode,
660 p_token2 => G_SQLERRM_TOKEN,
661 p_token2_value => sqlerrm);
662
663
664 IF l_cat_id_csr%ISOPEN THEN
665 CLOSE l_cat_id_csr;
666 END IF;
667
668
669 IF l_attribute_value_set_id_csr%ISOPEN THEN
670 CLOSE l_attribute_value_set_id_csr;
671 END IF;
672
673 IF l_variable_code_csr%ISOPEN THEN
674 CLOSE l_variable_code_csr;
675 END IF;
676
677 RETURN G_RET_STS_UNEXP_ERROR;
678
679 END Validate_Attributes;
680
681
682 ---------------------------------------------------------------------------
683 -- PROCEDURE Validate_Record
684 -- It calls Item Level Validations and then makes Record Level Validations
685 ---------------------------------------------------------------------------
686 ------------------------------------------
687 -- Validate_Record for:OKC_K_ART_VARIABLES --
688 ------------------------------------------
689 FUNCTION Validate_Record (
690 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
691
692 p_cat_id IN NUMBER,
693 p_variable_code IN VARCHAR2,
694 p_variable_type IN VARCHAR2,
695 p_external_yn IN VARCHAR2,
696 p_variable_value_id IN VARCHAR2,
697 p_variable_value IN VARCHAR2,
698 p_attribute_value_set_id IN NUMBER,
699 p_override_global_yn IN VARCHAR2
700 ) RETURN VARCHAR2 IS
701 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
702 BEGIN
703
704 /*IF (l_debug = 'Y') THEN
705 Okc_Debug.Log('2600: Entered Validate_Record', 2);
706 END IF;*/
707
708 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
709 FND_LOG.STRING(G_PROC_LEVEL,
710 G_PKG_NAME, '2600: Entered Validate_Record' );
711 END IF;
712 --- Validate all non-missing attributes (Item Level Validation)
713 l_return_status := Validate_Attributes(
714 p_validation_level => p_validation_level,
715
716 p_cat_id => p_cat_id,
717 p_variable_code => p_variable_code,
718 p_variable_type => p_variable_type,
719 p_external_yn => p_external_yn,
720 p_variable_value_id => p_variable_value_id,
721 p_variable_value => p_variable_value,
722 p_attribute_value_set_id => p_attribute_value_set_id,
723 p_override_global_yn => p_override_global_yn
724 );
725 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
726 /*IF (l_debug = 'Y') THEN
727 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
728 END IF;*/
729
730 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
731 FND_LOG.STRING(G_PROC_LEVEL,
732 G_PKG_NAME, '2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm );
733 END IF;
734 RETURN G_RET_STS_UNEXP_ERROR;
735 END IF;
736
737 --- Record Level Validation
738 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
739 /*IF (l_debug = 'Y') THEN
740 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
741 END IF;*/
742
743 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
744 FND_LOG.STRING(G_PROC_LEVEL,
745 G_PKG_NAME, '2800: Entered Record Level Validations' );
746 END IF;
747
748 IF P_ATTRIBUTE_VALUE_SET_ID IS NOT NULL AND
749 P_VARIABLE_VALUE_ID IS NOT NULL THEN
750 /* Need to put check here */
751 Null;
752 END IF;
753
754
755 END IF;
756
757 /*IF (l_debug = 'Y') THEN
758 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
759 END IF;*/
760
761 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
762 FND_LOG.STRING(G_PROC_LEVEL,
763 G_PKG_NAME, '2900: Leaving Validate_Record : '||sqlerrm );
764 END IF;
765
766 RETURN l_return_status ;
767
768 EXCEPTION
769 WHEN OTHERS THEN
770
771 /*IF (l_debug = 'Y') THEN
772 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
773 END IF;*/
774
775 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
776 FND_LOG.STRING(G_EXCP_LEVEL,
777 G_PKG_NAME, '3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm );
778 END IF;
779 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
780 p_msg_name => G_UNEXPECTED_ERROR,
781 p_token1 => G_SQLCODE_TOKEN,
782 p_token1_value => sqlcode,
783 p_token2 => G_SQLERRM_TOKEN,
784 p_token2_value => sqlerrm);
785 RETURN G_RET_STS_UNEXP_ERROR ;
786
787 END Validate_Record;
788
789 ---------------------------------------------------------------------------
790 -- PROCEDURE validate_row
791 ---------------------------------------------------------------------------
792 ---------------------------------------
793 -- validate_row for:OKC_K_ART_VARIABLES --
794 ---------------------------------------
795 PROCEDURE validate_row(
796 p_validation_level IN NUMBER,
797 x_return_status OUT NOCOPY VARCHAR2,
798 p_cat_id IN NUMBER,
799 p_variable_code IN VARCHAR2,
800 p_variable_type IN VARCHAR2,
801 p_external_yn IN VARCHAR2,
802 p_variable_value_id IN VARCHAR2,
803 p_variable_value IN VARCHAR2,
804 p_attribute_value_set_id IN NUMBER ,
805 p_override_global_yn IN VARCHAR2,
806
807 p_object_version_number IN NUMBER
808 ) IS
809 l_variable_type OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE;
810 l_external_yn OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE;
811 l_variable_value_id OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE;
812 l_variable_value OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE;
813 l_attribute_value_set_id OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE;
814 l_override_global_yn OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE;
815 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
816 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
817 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
818 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
819 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
820 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
821 BEGIN
822
823 /*IF (l_debug = 'Y') THEN
824 Okc_Debug.Log('3100: Entered validate_row', 2);
825 END IF;*/
826
827 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
828 FND_LOG.STRING(G_PROC_LEVEL,
829 G_PKG_NAME, '3100: Entered validate_row' );
830 END IF;
831
832 -- Setting attributes
833 x_return_status := Set_Attributes(
834 p_cat_id => p_cat_id,
835 p_variable_code => p_variable_code,
836 p_variable_type => p_variable_type,
837 p_external_yn => p_external_yn,
838 p_variable_value_id => p_variable_value_id,
839 p_variable_value => p_variable_value,
840 p_attribute_value_set_id => p_attribute_value_set_id,
841 p_override_global_yn => p_override_global_yn,
842 p_object_version_number => p_object_version_number,
843 x_variable_type => l_variable_type,
844 x_object_version_number => l_object_version_number,
845 x_external_yn => l_external_yn,
846 x_variable_value_id => l_variable_value_id,
847 x_variable_value => l_variable_value,
848 x_attribute_value_set_id => l_attribute_value_set_id,
849 x_override_global_yn => l_override_global_yn
850 );
851 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
854 RAISE FND_API.G_EXC_ERROR;
855 END IF;
856
857 -- Validate all non-missing attributes (Item Level Validation)
858 x_return_status := Validate_Record(
859 p_validation_level => p_validation_level,
860 p_cat_id => p_cat_id,
861 p_variable_code => p_variable_code,
862 p_variable_type => l_variable_type,
863 p_external_yn => l_external_yn,
864 p_variable_value_id => l_variable_value_id,
865 p_variable_value => l_variable_value,
866 p_attribute_value_set_id => l_attribute_value_set_id,
867 p_override_global_yn => l_override_global_yn
868 );
869
870 /*IF (l_debug = 'Y') THEN
871 Okc_Debug.Log('3200: Leaving validate_row', 2);
872 END IF;*/
873
874 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
875 FND_LOG.STRING(G_PROC_LEVEL,
876 G_PKG_NAME, '3200: Leaving validate_row' );
877 END IF;
878
879 EXCEPTION
880 WHEN FND_API.G_EXC_ERROR THEN
881 /*IF (l_debug = 'Y') THEN
882 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
883 END IF;*/
884
885 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
886 FND_LOG.STRING(G_EXCP_LEVEL,
887 G_PKG_NAME, '3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception' );
888 END IF;
889 x_return_status := G_RET_STS_ERROR;
890
891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892 /*IF (l_debug = 'Y') THEN
893 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
894 END IF;*/
895
896 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
897 FND_LOG.STRING(G_EXCP_LEVEL,
898 G_PKG_NAME, '3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
899 END IF;
900 x_return_status := G_RET_STS_UNEXP_ERROR;
901
902 WHEN OTHERS THEN
903 /*IF (l_debug = 'Y') THEN
904 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
905 END IF;*/
906
907 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
908 FND_LOG.STRING(G_EXCP_LEVEL,
909 G_PKG_NAME, '3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm );
910 END IF;
911 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
912 p_msg_name => G_UNEXPECTED_ERROR,
913 p_token1 => G_SQLCODE_TOKEN,
914 p_token1_value => sqlcode,
915 p_token2 => G_SQLERRM_TOKEN,
916 p_token2_value => sqlerrm);
917 x_return_status := G_RET_STS_UNEXP_ERROR;
918
919 END Validate_Row;
920
921 ---------------------------------------------------------------------------
922 -- PROCEDURE Insert_Row
923 ---------------------------------------------------------------------------
924 -------------------------------------
925 -- Insert_Row for:OKC_K_ART_VARIABLES --
926 -------------------------------------
927 FUNCTION Insert_Row(
928 p_cat_id IN NUMBER,
929 p_variable_code IN VARCHAR2,
930 p_variable_type IN VARCHAR2,
931 p_external_yn IN VARCHAR2,
932 p_variable_value_id IN VARCHAR2,
933 p_variable_value IN VARCHAR2,
934 p_attribute_value_set_id IN NUMBER,
935 p_override_global_yn IN VARCHAR2,
936 p_object_version_number IN NUMBER,
937 p_created_by IN NUMBER,
938 p_creation_date IN DATE,
939 p_last_updated_by IN NUMBER,
940 p_last_update_login IN NUMBER,
941 p_last_update_date IN DATE
942
943 ) RETURN VARCHAR2 IS
944
945 BEGIN
946
947 /*IF (l_debug = 'Y') THEN
948 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
949 END IF;*/
950
951 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
952 FND_LOG.STRING(G_PROC_LEVEL,
953 G_PKG_NAME, '3600: Entered Insert_Row function' );
954 END IF;
955 INSERT INTO OKC_K_ART_VARIABLES(
956 CAT_ID,
957 VARIABLE_CODE,
958 VARIABLE_TYPE,
959 EXTERNAL_YN,
960 VARIABLE_VALUE_ID,
961 VARIABLE_VALUE,
962 ATTRIBUTE_VALUE_SET_ID,
963 OVERRIDE_GLOBAL_YN,
964 OBJECT_VERSION_NUMBER,
965 CREATED_BY,
966 CREATION_DATE,
967 LAST_UPDATED_BY,
968 LAST_UPDATE_LOGIN,
969 LAST_UPDATE_DATE)
970 VALUES (
971 p_cat_id,
972 p_variable_code,
973 p_variable_type,
974 p_external_yn,
975 p_variable_value_id,
976 p_variable_value,
977 p_attribute_value_set_id,
978 p_override_global_yn,
979 p_object_version_number,
980 p_created_by,
981 p_creation_date,
982 p_last_updated_by,
983 p_last_update_login,
984 p_last_update_date);
985
986 /*IF (l_debug = 'Y') THEN
987 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
988 END IF;*/
989
990 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
991 FND_LOG.STRING(G_PROC_LEVEL,
992 G_PKG_NAME, '3700: Leaving Insert_Row' );
993 END IF;
994
995 RETURN( G_RET_STS_SUCCESS );
996
997 EXCEPTION
998 WHEN OTHERS THEN
999
1000 /*IF (l_debug = 'Y') THEN
1001 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
1002 END IF;*/
1003
1004 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1005 FND_LOG.STRING(G_EXCP_LEVEL,
1006 G_PKG_NAME, '3800: Leaving Insert_Row:OTHERS Exception' );
1007 END IF;
1008
1009 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1010 p_msg_name => G_UNEXPECTED_ERROR,
1011 p_token1 => G_SQLCODE_TOKEN,
1012 p_token1_value => sqlcode,
1013 p_token2 => G_SQLERRM_TOKEN,
1014 p_token2_value => sqlerrm);
1015
1016 RETURN( G_RET_STS_UNEXP_ERROR );
1017
1018 END Insert_Row;
1019
1020
1021 -------------------------------------
1022 -- Insert_Row for:OKC_K_ART_VARIABLES --
1023 -------------------------------------
1024 PROCEDURE Insert_Row(
1025 p_validation_level IN NUMBER,
1026 x_return_status OUT NOCOPY VARCHAR2,
1027 p_cat_id IN NUMBER,
1028 p_variable_code IN VARCHAR2,
1029 p_variable_type IN VARCHAR2,
1030 p_external_yn IN VARCHAR2,
1031 p_variable_value_id IN VARCHAR2,
1032 p_variable_value IN VARCHAR2,
1033 p_attribute_value_set_id IN NUMBER,
1034 p_override_global_yn IN VARCHAR2,
1035 x_cat_id OUT NOCOPY NUMBER,
1036 x_variable_code OUT NOCOPY VARCHAR2
1037
1038 ) IS
1039
1040 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
1041 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
1042 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
1043 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
1044 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
1045 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
1046 BEGIN
1047
1048 x_return_status := G_RET_STS_SUCCESS;
1049
1050 /*IF (l_debug = 'Y') THEN
1051 Okc_Debug.Log('4200: Entered Insert_Row', 2);
1052 END IF;*/
1053
1054 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1055 FND_LOG.STRING(G_PROC_LEVEL,
1056 G_PKG_NAME, '4200: Entered Insert_Row' );
1057 END IF;
1058
1059 -- Set Internal columns
1060 l_object_version_number := 1;
1061 l_creation_date := Sysdate;
1062 l_created_by := Fnd_Global.User_Id;
1063 l_last_update_date := l_creation_date;
1064 l_last_updated_by := l_created_by;
1065 l_last_update_login := Fnd_Global.Login_Id;
1066
1067
1068 --- Validate all non-missing attributes
1069 x_return_status := Validate_Record(
1070 p_validation_level => p_validation_level,
1071 p_cat_id => p_cat_id,
1072 p_variable_code => p_variable_code,
1073 p_variable_type => p_variable_type,
1074 p_external_yn => p_external_yn,
1075 p_variable_value_id => p_variable_value_id,
1076 p_variable_value => p_variable_value,
1077 p_attribute_value_set_id => p_attribute_value_set_id,
1078 p_override_global_yn => p_override_global_yn
1079 );
1080 --- If any errors happen abort API
1081 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1084 RAISE FND_API.G_EXC_ERROR;
1085 END IF;
1086
1087 --------------------------------------------
1088 -- Call the internal Insert_Row for each child record
1089 --------------------------------------------
1090 /*IF (l_debug = 'Y') THEN
1091 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
1092 END IF;*/
1093
1094 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1095 FND_LOG.STRING(G_PROC_LEVEL,
1096 G_PKG_NAME, '4300: Call the internal Insert_Row for Base Table' );
1097 END IF;
1098
1099 x_return_status := Insert_Row(
1100 p_cat_id => p_cat_id,
1101 p_variable_code => p_variable_code,
1102 p_variable_type => p_variable_type,
1103 p_external_yn => p_external_yn,
1104 p_variable_value_id => p_variable_value_id,
1105 p_variable_value => p_variable_value,
1106 p_attribute_value_set_id => p_attribute_value_set_id,
1107 p_override_global_yn => p_override_global_yn,
1108 p_object_version_number => l_object_version_number,
1109 p_created_by => l_created_by,
1110 p_creation_date => l_creation_date,
1111 p_last_updated_by => l_last_updated_by,
1112 p_last_update_login => l_last_update_login,
1113 p_last_update_date => l_last_update_date
1114 );
1115 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1118 RAISE FND_API.G_EXC_ERROR;
1119 END IF;
1120
1121
1122
1123 /*IF (l_debug = 'Y') THEN
1124 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
1125 END IF;*/
1126
1127 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1128 FND_LOG.STRING(G_PROC_LEVEL,
1129 G_PKG_NAME, '4500: Leaving Insert_Row' );
1130 END IF;
1131
1132 EXCEPTION
1133 WHEN FND_API.G_EXC_ERROR THEN
1134 /*IF (l_debug = 'Y') THEN
1135 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
1136 END IF;*/
1137
1138 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1139 FND_LOG.STRING(G_EXCP_LEVEL,
1140 G_PKG_NAME, '4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception' );
1141 END IF;
1142 x_return_status := G_RET_STS_ERROR;
1143
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 /*IF (l_debug = 'Y') THEN
1146 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1147 END IF;*/
1148
1149 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1150 FND_LOG.STRING(G_EXCP_LEVEL,
1151 G_PKG_NAME, '4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1152 END IF;
1153 x_return_status := G_RET_STS_UNEXP_ERROR;
1154
1155 WHEN OTHERS THEN
1156 /*IF (l_debug = 'Y') THEN
1157 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1158 END IF;*/
1159
1160 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1161 FND_LOG.STRING(G_EXCP_LEVEL,
1162 G_PKG_NAME, '4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm );
1163 END IF;
1164 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1165 p_msg_name => G_UNEXPECTED_ERROR,
1166 p_token1 => G_SQLCODE_TOKEN,
1167 p_token1_value => sqlcode,
1168 p_token2 => G_SQLERRM_TOKEN,
1169 p_token2_value => sqlerrm);
1170 x_return_status := G_RET_STS_UNEXP_ERROR;
1171
1172 END Insert_Row;
1173 ---------------------------------------------------------------------------
1174 -- PROCEDURE Lock_Row
1175 ---------------------------------------------------------------------------
1176 -----------------------------------
1177 -- Lock_Row for:OKC_K_ART_VARIABLES --
1178 -----------------------------------
1179 FUNCTION Lock_Row(
1180 p_cat_id IN NUMBER,
1181 p_variable_code IN VARCHAR2,
1182 p_object_version_number IN NUMBER
1183 ) RETURN VARCHAR2 IS
1184
1185
1186 CURSOR lock_csr (cp_cat_id NUMBER, cp_variable_code VARCHAR2, cp_object_version_number NUMBER) IS
1187 SELECT object_version_number
1188 FROM OKC_K_ART_VARIABLES
1189 WHERE CAT_ID = cp_cat_id AND VARIABLE_CODE = cp_variable_code
1190 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1191 FOR UPDATE OF object_version_number NOWAIT;
1192
1193 CURSOR lchk_csr (cp_cat_id NUMBER, cp_variable_code VARCHAR2) IS
1194 SELECT object_version_number
1195 FROM OKC_K_ART_VARIABLES
1196 WHERE CAT_ID = cp_cat_id AND VARIABLE_CODE = cp_variable_code;
1197
1198 l_return_status VARCHAR2(1);
1199
1200 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
1201
1202 l_row_notfound BOOLEAN := FALSE;
1203 BEGIN
1204
1205 /*IF (l_debug = 'Y') THEN
1206 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1207 END IF;*/
1208
1209 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1210 FND_LOG.STRING(G_PROC_LEVEL,
1211 G_PKG_NAME, '4900: Entered Lock_Row' );
1212 END IF;
1213
1214 BEGIN
1215
1216 OPEN lock_csr( p_cat_id, p_variable_code, p_object_version_number );
1217 FETCH lock_csr INTO l_object_version_number;
1218 l_row_notfound := lock_csr%NOTFOUND;
1219 CLOSE lock_csr;
1220
1221 EXCEPTION
1222 WHEN E_Resource_Busy THEN
1223
1224 /*IF (l_debug = 'Y') THEN
1225 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1226 END IF;*/
1227
1228 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1229 FND_LOG.STRING(G_EXCP_LEVEL,
1230 G_PKG_NAME, '5000: Leaving Lock_Row:E_Resource_Busy Exception' );
1231 END IF;
1232
1233 IF (lock_csr%ISOPEN) THEN
1234 CLOSE lock_csr;
1235 END IF;
1236 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1237 RETURN( G_RET_STS_ERROR );
1238 END;
1239
1240 IF ( l_row_notfound ) THEN
1241 l_return_status := G_RET_STS_ERROR;
1242
1243 OPEN lchk_csr(p_cat_id, p_variable_code);
1244 FETCH lchk_csr INTO l_object_version_number;
1245 l_row_notfound := lchk_csr%NOTFOUND;
1246 CLOSE lchk_csr;
1247
1248 IF (l_row_notfound) THEN
1249 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
1250 ELSIF l_object_version_number > p_object_version_number THEN
1251 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1252 ELSIF l_object_version_number = -1 THEN
1253 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1254 ELSE -- it can be the only above condition. It can happen after restore version
1255 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1256 END IF;
1257 ELSE
1258 l_return_status := G_RET_STS_SUCCESS;
1259 END IF;
1260
1261 /*IF (l_debug = 'Y') THEN
1262 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1263 END IF;*/
1264
1265 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1266 FND_LOG.STRING(G_PROC_LEVEL,
1267 G_PKG_NAME, '5100: Leaving Lock_Row' );
1268 END IF;
1269
1270 RETURN( l_return_status );
1271
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274
1275 IF (lock_csr%ISOPEN) THEN
1276 CLOSE lock_csr;
1277 END IF;
1278 IF (lchk_csr%ISOPEN) THEN
1279 CLOSE lchk_csr;
1280 END IF;
1281
1282 /*IF (l_debug = 'Y') THEN
1283 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1284 END IF;*/
1285
1286 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1287 FND_LOG.STRING(G_EXCP_LEVEL,
1288 G_PKG_NAME, '5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
1289 END IF;
1290
1291 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1292 p_msg_name => G_UNEXPECTED_ERROR,
1293 p_token1 => G_SQLCODE_TOKEN,
1294 p_token1_value => sqlcode,
1295 p_token2 => G_SQLERRM_TOKEN,
1296 p_token2_value => sqlerrm);
1297
1298 RETURN( G_RET_STS_UNEXP_ERROR );
1299 END Lock_Row;
1300
1301 -----------------------------------
1302 -- Lock_Row for:OKC_K_ART_VARIABLES --
1303 -----------------------------------
1304 PROCEDURE Lock_Row(
1305 x_return_status OUT NOCOPY VARCHAR2,
1306
1307 p_cat_id IN NUMBER,
1308 p_variable_code IN VARCHAR2,
1309 p_object_version_number IN NUMBER
1310 ) IS
1311 BEGIN
1312
1313 /*IF (l_debug = 'Y') THEN
1314 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1315 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1316 END IF;*/
1317
1318 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1319 FND_LOG.STRING(G_PROC_LEVEL,
1320 G_PKG_NAME, '5700: Entered Lock_Row');
1321 FND_LOG.STRING(G_PROC_LEVEL,
1322 G_PKG_NAME, '5800: Locking Row for Base Table');
1323 END IF;
1324
1325 --------------------------------------------
1326 -- Call the LOCK_ROW for each _B child record
1327 --------------------------------------------
1328 x_return_status := Lock_Row(
1329 p_cat_id => p_cat_id,
1330 p_variable_code => p_variable_code,
1331 p_object_version_number => p_object_version_number
1332 );
1333 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1336 RAISE FND_API.G_EXC_ERROR;
1337 END IF;
1338
1339
1340
1341 /*IF (l_debug = 'Y') THEN
1342 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1343 END IF;*/
1344
1345 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1346 FND_LOG.STRING(G_PROC_LEVEL,
1347 G_PKG_NAME, '6000: Leaving Lock_Row' );
1348 END IF;
1349
1350 EXCEPTION
1351 WHEN FND_API.G_EXC_ERROR THEN
1352 /*IF (l_debug = 'Y') THEN
1353 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1354 END IF;*/
1355
1356 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1357 FND_LOG.STRING(G_EXCP_LEVEL,
1358 G_PKG_NAME, '6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception' );
1359 END IF;
1360 x_return_status := G_RET_STS_ERROR;
1361
1362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1363 /*IF (l_debug = 'Y') THEN
1364 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1365 END IF;*/
1366
1367 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1368 FND_LOG.STRING(G_EXCP_LEVEL,
1369 G_PKG_NAME, '6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1370 END IF;
1371 x_return_status := G_RET_STS_UNEXP_ERROR;
1372
1373 WHEN OTHERS THEN
1374 /*IF (l_debug = 'Y') THEN
1375 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1376 END IF;*/
1377
1378 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1379 FND_LOG.STRING(G_EXCP_LEVEL,
1380 G_PKG_NAME, '6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
1381 END IF;
1382 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1383 p_msg_name => G_UNEXPECTED_ERROR,
1384 p_token1 => G_SQLCODE_TOKEN,
1385 p_token1_value => sqlcode,
1386 p_token2 => G_SQLERRM_TOKEN,
1387 p_token2_value => sqlerrm);
1388 x_return_status := G_RET_STS_UNEXP_ERROR;
1389
1390 END Lock_Row;
1391 ---------------------------------------------------------------------------
1392 -- PROCEDURE Update_Row
1393 ---------------------------------------------------------------------------
1394 -------------------------------------
1395 -- Update_Row for:OKC_K_ART_VARIABLES --
1396 -------------------------------------
1397 FUNCTION Update_Row(
1398 p_cat_id IN NUMBER,
1399 p_variable_code IN VARCHAR2,
1400 p_variable_type IN VARCHAR2,
1401 p_external_yn IN VARCHAR2,
1402 p_variable_value_id IN VARCHAR2,
1403 p_variable_value IN VARCHAR2,
1404 p_attribute_value_set_id IN NUMBER,
1405 p_override_global_yn IN VARCHAR2,
1406 p_object_version_number IN NUMBER,
1407 p_created_by IN NUMBER,
1408 p_creation_date IN DATE,
1409 p_last_updated_by IN NUMBER,
1410 p_last_update_login IN NUMBER,
1411 p_last_update_date IN DATE
1412 ) RETURN VARCHAR2 IS
1413
1414 BEGIN
1415
1416 /*IF (l_debug = 'Y') THEN
1417 Okc_Debug.Log('6400: Entered Update_Row', 2);
1418 END IF;*/
1419
1420 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1421 FND_LOG.STRING(G_PROC_LEVEL,
1422 G_PKG_NAME, '6400: Entered Update_Row' );
1423 END IF;
1424
1425 UPDATE OKC_K_ART_VARIABLES
1426 SET VARIABLE_TYPE = p_variable_type,
1427 EXTERNAL_YN = p_external_yn,
1428 VARIABLE_VALUE_ID = p_variable_value_id,
1429 VARIABLE_VALUE = p_variable_value,
1430 ATTRIBUTE_VALUE_SET_ID = p_attribute_value_set_id,
1431 OVERRIDE_GLOBAL_YN = p_override_global_yn,
1432 OBJECT_VERSION_NUMBER = p_object_version_number,
1433 LAST_UPDATED_BY = p_last_updated_by,
1434 LAST_UPDATE_LOGIN = p_last_update_login,
1435 LAST_UPDATE_DATE = p_last_update_date
1436 WHERE CAT_ID = p_cat_id
1437 AND VARIABLE_CODE = p_variable_code;
1438
1439 /*IF (l_debug = 'Y') THEN
1440 Okc_Debug.Log('6500: Leaving Update_Row', 2);
1441 END IF;*/
1442
1443 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1444 FND_LOG.STRING(G_PROC_LEVEL,
1445 G_PKG_NAME, '6500: Leaving Update_Row' );
1446 END IF;
1447
1448 RETURN G_RET_STS_SUCCESS ;
1449
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452
1453 /*IF (l_debug = 'Y') THEN
1454 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1455 END IF;*/
1456
1457 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1458 FND_LOG.STRING(G_EXCP_LEVEL,
1459 G_PKG_NAME, '6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
1460 END IF;
1461
1462 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1463 p_msg_name => G_UNEXPECTED_ERROR,
1464 p_token1 => G_SQLCODE_TOKEN,
1465 p_token1_value => sqlcode,
1466 p_token2 => G_SQLERRM_TOKEN,
1467 p_token2_value => sqlerrm);
1468
1469 RETURN G_RET_STS_UNEXP_ERROR ;
1470
1471 END Update_Row;
1472
1473 -------------------------------------
1474 -- Update_Row for:OKC_K_ART_VARIABLES --
1475 -------------------------------------
1476 PROCEDURE Update_Row(
1477 p_validation_level IN NUMBER,
1478 x_return_status OUT NOCOPY VARCHAR2,
1479 p_cat_id IN NUMBER,
1480 p_variable_code IN VARCHAR2,
1481 p_variable_type IN VARCHAR2,
1482 p_external_yn IN VARCHAR2,
1483 p_variable_value_id IN VARCHAR2,
1484 p_variable_value IN VARCHAR2,
1485
1486 p_attribute_value_set_id IN NUMBER := NULL,
1487 p_override_global_yn IN VARCHAR2 := NULL,
1488
1489 p_object_version_number IN NUMBER
1490
1491 ) IS
1492
1493 l_variable_type OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE;
1494 l_external_yn OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE;
1495 l_variable_value_id OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE;
1496 l_variable_value OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE;
1497 l_attribute_value_set_id OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE;
1498 l_override_global_yn OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE;
1499 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
1500 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
1501 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
1502 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
1503 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
1504 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
1505
1506 BEGIN
1507
1508 /*IF (l_debug = 'Y') THEN
1509 Okc_Debug.Log('7000: Entered Update_Row', 2);
1510 Okc_Debug.Log('7100: Locking _B row', 2);
1511 END IF;*/
1512
1513 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1514 FND_LOG.STRING(G_PROC_LEVEL,
1515 G_PKG_NAME, '7000: Entered Update_Row');
1516 FND_LOG.STRING(G_PROC_LEVEL,
1517 G_PKG_NAME, '7100: Locking _B row');
1518 END IF;
1519
1520 x_return_status := Lock_row(
1521 p_cat_id => p_cat_id,
1522 p_variable_code => p_variable_code,
1523 p_object_version_number => p_object_version_number
1524 );
1525 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1527 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1528 RAISE FND_API.G_EXC_ERROR;
1529 END IF;
1530
1531 /*IF (l_debug = 'Y') THEN
1532 Okc_Debug.Log('7300: Setting attributes', 2);
1533 END IF;*/
1534
1535 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1536 FND_LOG.STRING(G_PROC_LEVEL,
1537 G_PKG_NAME, '7300: Setting attributes' );
1538 END IF;
1539
1540 x_return_status := Set_Attributes(
1541 p_cat_id => p_cat_id,
1542 p_variable_code => p_variable_code,
1543 p_variable_type => p_variable_type,
1544 p_external_yn => p_external_yn,
1545 p_variable_value_id => p_variable_value_id,
1546 p_variable_value => p_variable_value,
1547 p_attribute_value_set_id => p_attribute_value_set_id,
1548 p_override_global_yn => p_override_global_yn,
1549 p_object_version_number => p_object_version_number,
1550 x_variable_type => l_variable_type,
1551 x_object_version_number => l_object_version_number,
1552 x_external_yn => l_external_yn,
1553 x_variable_value_id => l_variable_value_id,
1554 x_variable_value => l_variable_value,
1555 x_attribute_value_set_id => l_attribute_value_set_id,
1556 x_override_global_yn => l_override_global_yn
1557 );
1558 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1560 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1561 RAISE FND_API.G_EXC_ERROR;
1562 END IF;
1563
1564 /*IF (l_debug = 'Y') THEN
1565 Okc_Debug.Log('7400: Record Validation', 2);
1566 END IF;*/
1567
1568 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1569 FND_LOG.STRING(G_PROC_LEVEL,
1570 G_PKG_NAME, '7400: Record Validation' );
1571 END IF;
1572
1573 --- Validate all non-missing attributes
1574 x_return_status := Validate_Record(
1575 p_validation_level => p_validation_level,
1576 p_cat_id => p_cat_id,
1577 p_variable_code => p_variable_code,
1578 p_variable_type => l_variable_type,
1579 p_external_yn => l_external_yn,
1580 p_variable_value_id => l_variable_value_id,
1581 p_variable_value => l_variable_value,
1582 p_attribute_value_set_id => l_attribute_value_set_id,
1583 p_override_global_yn => l_override_global_yn
1584 );
1585 --- If any errors happen abort API
1586 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1588 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1589 RAISE FND_API.G_EXC_ERROR;
1590 END IF;
1591
1592 /*IF (l_debug = 'Y') THEN
1593 Okc_Debug.Log('7500: Filling WHO columns', 2);
1594 END IF;*/
1595
1596 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1597 FND_LOG.STRING(G_PROC_LEVEL,
1598 G_PKG_NAME, '7500: Filling WHO columns' );
1599 END IF;
1600
1601 -- Filling who columns
1602 l_last_update_date := SYSDATE;
1603 l_last_updated_by := FND_GLOBAL.USER_ID;
1604 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1605
1606 -- Object version increment
1607 IF Nvl(l_object_version_number, 0) >= 0 THEN
1608 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
1609 END IF;
1610
1611 --------------------------------------------
1612 -- Call the Update_Row for each child record
1613 --------------------------------------------
1614 /*IF (l_debug = 'Y') THEN
1615 Okc_Debug.Log('7600: Updating Row', 2);
1616 END IF;*/
1617
1618 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1619 FND_LOG.STRING(G_PROC_LEVEL,
1620 G_PKG_NAME, '7600: Updating Row' );
1621 END IF;
1622
1623 x_return_status := Update_Row(
1624 p_cat_id => p_cat_id,
1625 p_variable_code => p_variable_code,
1626 p_variable_type => l_variable_type,
1627 p_external_yn => l_external_yn,
1628 p_variable_value_id => l_variable_value_id,
1629 p_variable_value => l_variable_value,
1630 p_attribute_value_set_id => l_attribute_value_set_id,
1631 p_override_global_yn => l_override_global_yn,
1632 p_object_version_number => l_object_version_number,
1633 p_created_by => l_created_by,
1634 p_creation_date => l_creation_date,
1635 p_last_updated_by => l_last_updated_by,
1636 p_last_update_login => l_last_update_login,
1637 p_last_update_date => l_last_update_date
1638 );
1639 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1641 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1642 RAISE FND_API.G_EXC_ERROR;
1643 END IF;
1644
1645
1646 /*IF (l_debug = 'Y') THEN
1647 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1648 END IF;*/
1649
1650 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1651 FND_LOG.STRING(G_PROC_LEVEL,
1652 G_PKG_NAME, '7800: Leaving Update_Row' );
1653 END IF;
1654
1655 EXCEPTION
1656 WHEN FND_API.G_EXC_ERROR THEN
1657 /*IF (l_debug = 'Y') THEN
1658 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1659 END IF;*/
1660
1661 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1662 FND_LOG.STRING(G_EXCP_LEVEL,
1663 G_PKG_NAME, '7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception' );
1664 END IF;
1665
1666 x_return_status := G_RET_STS_ERROR;
1667
1668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1669 /*IF (l_debug = 'Y') THEN
1670 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1671 END IF;*/
1672
1673 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1674 FND_LOG.STRING(G_EXCP_LEVEL,
1675 G_PKG_NAME, '8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1676 END IF;
1677
1678 x_return_status := G_RET_STS_UNEXP_ERROR;
1679
1680 WHEN OTHERS THEN
1681 /*IF (l_debug = 'Y') THEN
1682 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1683 END IF;*/
1684
1685 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1686 FND_LOG.STRING(G_EXCP_LEVEL,
1687 G_PKG_NAME, '8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
1688 END IF;
1689
1690 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1691 p_msg_name => G_UNEXPECTED_ERROR,
1692 p_token1 => G_SQLCODE_TOKEN,
1693 p_token1_value => sqlcode,
1694 p_token2 => G_SQLERRM_TOKEN,
1695 p_token2_value => sqlerrm);
1696 x_return_status := G_RET_STS_UNEXP_ERROR;
1697
1698 END Update_Row;
1699
1700 ---------------------------------------------------------------------------
1701 -- PROCEDURE Delete_Row
1702 ---------------------------------------------------------------------------
1703 -------------------------------------
1704 -- Delete_Row for:OKC_K_ART_VARIABLES --
1705 -------------------------------------
1706 FUNCTION Delete_Row(
1707 p_cat_id IN NUMBER,
1708 p_variable_code IN VARCHAR2
1709 ) RETURN VARCHAR2 IS
1710
1711 BEGIN
1712
1713 /*IF (l_debug = 'Y') THEN
1714 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1715 END IF;*/
1716
1717 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1718 FND_LOG.STRING(G_PROC_LEVEL,
1719 G_PKG_NAME, '8200: Entered Delete_Row' );
1720 END IF;
1721
1722 DELETE FROM OKC_K_ART_VARIABLES WHERE CAT_ID = p_CAT_ID AND VARIABLE_CODE = p_VARIABLE_CODE;
1723
1724 /*IF (l_debug = 'Y') THEN
1725 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1726 END IF;*/
1727
1728 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1729 FND_LOG.STRING(G_PROC_LEVEL,
1730 G_PKG_NAME, '8300: Leaving Delete_Row' );
1731 END IF;
1732
1733 RETURN( G_RET_STS_SUCCESS );
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737
1738 /*IF (l_debug = 'Y') THEN
1739 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1740 END IF;*/
1741
1742 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1743 FND_LOG.STRING(G_EXCP_LEVEL,
1744 G_PKG_NAME, '8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
1745 END IF;
1746
1747 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1748 p_msg_name => G_UNEXPECTED_ERROR,
1749 p_token1 => G_SQLCODE_TOKEN,
1750 p_token1_value => sqlcode,
1751 p_token2 => G_SQLERRM_TOKEN,
1752 p_token2_value => sqlerrm);
1753
1754 RETURN( G_RET_STS_UNEXP_ERROR );
1755
1756 END Delete_Row;
1757
1758 -------------------------------------
1759 -- Delete_Row for:OKC_K_ART_VARIABLES --
1760 -------------------------------------
1761 PROCEDURE Delete_Row(
1762 x_return_status OUT NOCOPY VARCHAR2,
1763 p_cat_id IN NUMBER,
1764 p_variable_code IN VARCHAR2,
1765 p_object_version_number IN NUMBER
1766 ) IS
1767 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1768 BEGIN
1769
1770 /*IF (l_debug = 'Y') THEN
1771 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1772 Okc_Debug.Log('8900: Locking _B row', 2);
1773 END IF;*/
1774
1775 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1776 FND_LOG.STRING(G_PROC_LEVEL,
1777 G_PKG_NAME, '8800: Entered Delete_Row');
1778 FND_LOG.STRING(G_PROC_LEVEL,
1779 G_PKG_NAME, '8900: Locking _B row');
1780 END IF;
1781
1782 x_return_status := Lock_row(
1783 p_cat_id => p_cat_id,
1784 p_variable_code => p_variable_code,
1785 p_object_version_number => p_object_version_number
1786 );
1787 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1789 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1790 RAISE FND_API.G_EXC_ERROR;
1791 END IF;
1792
1793 /*IF (l_debug = 'Y') THEN
1794 Okc_Debug.Log('9100: Removing _B row', 2);
1795 END IF;*/
1796
1797 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1798 FND_LOG.STRING(G_PROC_LEVEL,
1799 G_PKG_NAME, '9100: Removing _B row' );
1800 END IF;
1801 x_return_status := Delete_Row( p_cat_id => p_cat_id,p_variable_code => p_variable_code );
1802 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1804 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1805 RAISE FND_API.G_EXC_ERROR;
1806 END IF;
1807
1808
1809 /*IF (l_debug = 'Y') THEN
1810 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1811 END IF;*/
1812
1813 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1814 FND_LOG.STRING(G_PROC_LEVEL,
1815 G_PKG_NAME, '9300: Leaving Delete_Row' );
1816 END IF;
1817
1818 EXCEPTION
1819 WHEN FND_API.G_EXC_ERROR THEN
1820 /*IF (l_debug = 'Y') THEN
1821 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1822 END IF;*/
1823
1824 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1825 FND_LOG.STRING(G_EXCP_LEVEL,
1826 G_PKG_NAME, '9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception' );
1827 END IF;
1828 x_return_status := G_RET_STS_ERROR;
1829
1830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1831 /*IF (l_debug = 'Y') THEN
1832 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1833 END IF;*/
1834
1835 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1836 FND_LOG.STRING(G_EXCP_LEVEL,
1837 G_PKG_NAME, '9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1838 END IF;
1839 x_return_status := G_RET_STS_UNEXP_ERROR;
1840
1841 WHEN OTHERS THEN
1842 /*IF (l_debug = 'Y') THEN
1843 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1844 END IF;*/
1845
1846 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1847 FND_LOG.STRING(G_EXCP_LEVEL,
1848 G_PKG_NAME, '9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
1849 END IF;
1850 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1851 p_msg_name => G_UNEXPECTED_ERROR,
1852 p_token1 => G_SQLCODE_TOKEN,
1853 p_token1_value => sqlcode,
1854 p_token2 => G_SQLERRM_TOKEN,
1855 p_token2_value => sqlerrm);
1856 x_return_status := G_RET_STS_UNEXP_ERROR;
1857
1858 END Delete_Row;
1859
1860 PROCEDURE delete_set(
1861 x_return_status OUT NOCOPY VARCHAR2,
1862 p_cat_id IN NUMBER
1863 )
1864 IS
1865 CURSOR lock_csr IS
1866 SELECT rowid
1867 FROM OKC_K_ART_VARIABLES
1868 WHERE cat_id = p_cat_id
1869 FOR UPDATE NOWAIT;
1870
1871 BEGIN
1872 /*IF (l_debug = 'Y') THEN
1873 Okc_Debug.Log('9700: Entered Delete_Set', 2);
1874 Okc_Debug.Log('9701: Locking Records', 2);
1875 END IF;*/
1876
1877 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1878 FND_LOG.STRING(G_PROC_LEVEL,
1879 G_PKG_NAME, '9700: Entered Delete_Set');
1880 FND_LOG.STRING(G_PROC_LEVEL,
1881 G_PKG_NAME, '9701: Locking Records');
1882 END IF;
1883
1884 -- making OPEN/CLOSE cursor to lock records
1885 OPEN lock_csr;
1886 CLOSE lock_csr;
1887
1888 DELETE FROM OKC_K_ART_VARIABLES
1889 WHERE cat_id = p_cat_id;
1890
1891 /*IF (l_debug = 'Y') THEN
1892 Okc_Debug.Log('11000: Leaving Delete_set', 2);
1893 END IF;*/
1894
1895 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1896 FND_LOG.STRING(G_PROC_LEVEL,
1897 G_PKG_NAME, '11000: Leaving Delete_set' );
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN E_Resource_Busy THEN
1902 /*IF (l_debug = 'Y') THEN
1903 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
1904 END IF;*/
1905
1906 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1907 FND_LOG.STRING(G_EXCP_LEVEL,
1908 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
1909 END IF;
1910
1911 IF (lock_csr%ISOPEN) THEN
1912 CLOSE lock_csr;
1913 END IF;
1914 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
1915 x_return_status := G_RET_STS_ERROR ;
1916
1917 WHEN FND_API.G_EXC_ERROR THEN
1918 /*IF (l_debug = 'Y') THEN
1919 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
1920 END IF;*/
1921
1922 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1923 FND_LOG.STRING(G_EXCP_LEVEL,
1924 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
1925 END IF;
1926
1927 IF (lock_csr%ISOPEN) THEN
1928 CLOSE lock_csr;
1929 END IF;
1930
1931 x_return_status := G_RET_STS_ERROR;
1932
1933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1934 /*IF (l_debug = 'Y') THEN
1935 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1936 END IF;*/
1937
1938 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1939 FND_LOG.STRING(G_EXCP_LEVEL,
1940 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1941 END IF;
1942
1943 IF (lock_csr%ISOPEN) THEN
1944 CLOSE lock_csr;
1945 END IF;
1946 x_return_status := G_RET_STS_UNEXP_ERROR;
1947
1948 WHEN OTHERS THEN
1949 /*IF (l_debug = 'Y') THEN
1950 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
1951 END IF;*/
1952
1953 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1954 FND_LOG.STRING(G_EXCP_LEVEL,
1955 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
1956 END IF;
1957
1958 IF (lock_csr%ISOPEN) THEN
1959 CLOSE lock_csr;
1960 END IF;
1961 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1962 p_msg_name => G_UNEXPECTED_ERROR,
1963 p_token1 => G_SQLCODE_TOKEN,
1964 p_token1_value => sqlcode,
1965 p_token2 => G_SQLERRM_TOKEN,
1966 p_token2_value => sqlerrm);
1967 x_return_status := G_RET_STS_UNEXP_ERROR;
1968
1969 END Delete_Set;
1970
1971 PROCEDURE delete_set(
1972 x_return_status OUT NOCOPY VARCHAR2,
1973 p_scn_id IN NUMBER
1974 )
1975 IS
1976 CURSOR lock_csr IS
1977 SELECT rowid
1978 FROM OKC_K_ART_VARIABLES
1979 WHERE CAT_ID IN (SELECT ID FROM OKC_K_ARTICLES_B
1980 WHERE SCN_ID=p_scn_id)
1981 FOR UPDATE NOWAIT;
1982
1983 BEGIN
1984 /*IF (l_debug = 'Y') THEN
1985 Okc_Debug.Log('9700: Entered Delete_Set', 2);
1986 Okc_Debug.Log('9700: Locking Records', 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, '9700: Entered Delete_Set');
1992 FND_LOG.STRING(G_PROC_LEVEL,
1993 G_PKG_NAME, '9700: Locking Records');
1994 END IF;
1995
1996 -- making OPEN/CLOSE cursor to lock records
1997 OPEN lock_csr;
1998 CLOSE lock_csr;
1999
2000 DELETE FROM OKC_K_ART_VARIABLES
2001 WHERE CAT_ID IN (SELECT ID FROM OKC_K_ARTICLES_B
2002 WHERE SCN_ID=p_scn_id);
2003
2004 /*IF (l_debug = 'Y') THEN
2005 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2006 END IF;*/
2007
2008 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2009 FND_LOG.STRING(G_PROC_LEVEL,
2010 G_PKG_NAME, '11000: Leaving Delete_set' );
2011 END IF;
2012
2013 EXCEPTION
2014 WHEN E_Resource_Busy THEN
2015 /*IF (l_debug = 'Y') THEN
2016 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2017 END IF;*/
2018
2019 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2020 FND_LOG.STRING(G_EXCP_LEVEL,
2021 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
2022 END IF;
2023
2024 IF (lock_csr%ISOPEN) THEN
2025 CLOSE lock_csr;
2026 END IF;
2027 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2028 x_return_status := G_RET_STS_ERROR ;
2029
2030 WHEN FND_API.G_EXC_ERROR THEN
2031 /*IF (l_debug = 'Y') THEN
2032 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
2033 END IF;*/
2034
2035 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2036 FND_LOG.STRING(G_EXCP_LEVEL,
2037 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
2038 END IF;
2039
2040 IF (lock_csr%ISOPEN) THEN
2041 CLOSE lock_csr;
2042 END IF;
2043 x_return_status := G_RET_STS_ERROR;
2044
2045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2046 /*IF (l_debug = 'Y') THEN
2047 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2048 END IF;*/
2049
2050 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2051 FND_LOG.STRING(G_EXCP_LEVEL,
2052 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2053 END IF;
2054
2055 IF (lock_csr%ISOPEN) THEN
2056 CLOSE lock_csr;
2057 END IF;
2058 x_return_status := G_RET_STS_UNEXP_ERROR;
2059
2060 WHEN OTHERS THEN
2061 /*IF (l_debug = 'Y') THEN
2062 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
2063 END IF;*/
2064
2065 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2066 FND_LOG.STRING(G_EXCP_LEVEL,
2067 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
2068 END IF;
2069
2070 IF (lock_csr%ISOPEN) THEN
2071 CLOSE lock_csr;
2072 END IF;
2073 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2074 p_msg_name => G_UNEXPECTED_ERROR,
2075 p_token1 => G_SQLCODE_TOKEN,
2076 p_token1_value => sqlcode,
2077 p_token2 => G_SQLERRM_TOKEN,
2078 p_token2_value => sqlerrm);
2079 x_return_status := G_RET_STS_UNEXP_ERROR;
2080
2081 END Delete_Set;
2082
2083
2084 PROCEDURE delete_set(
2085 x_return_status OUT NOCOPY VARCHAR2,
2086 p_doc_type IN VARCHAR2,
2087 p_doc_id IN NUMBER
2088 )
2089 IS
2090
2091 CURSOR lock_csr IS
2092 SELECT rowid
2093 FROM OKC_K_ART_VARIABLES
2094 WHERE cat_id IN (SELECT id FROM OKC_K_ARTICLES_B WHERE
2095 document_type=p_doc_type AND
2096 document_id = p_doc_id)
2097 FOR UPDATE NOWAIT;
2098
2099 BEGIN
2100 /*IF (l_debug = 'Y') THEN
2101 Okc_Debug.Log('9700: Entered Delete_Set', 2);
2102 Okc_Debug.Log('9710: Locking Records', 2);
2103 END IF;*/
2104
2105 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2106 FND_LOG.STRING(G_PROC_LEVEL,
2107 G_PKG_NAME, '9700: Entered Delete_Set');
2108 FND_LOG.STRING(G_PROC_LEVEL,
2109 G_PKG_NAME, '9710: Locking Records');
2110 END IF;
2111
2112 -- making OPEN/CLOSE cursor to lock records
2113 OPEN lock_csr;
2114 CLOSE lock_csr;
2115
2116 DELETE FROM OKC_K_ART_VARIABLES
2117 WHERE cat_id IN (SELECT id FROM OKC_K_ARTICLES_B WHERE
2118 document_type=p_doc_type AND
2119 document_id = p_doc_id);
2120
2121 /*IF (l_debug = 'Y') THEN
2122 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2123 END IF;*/
2124
2125 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2126 FND_LOG.STRING(G_PROC_LEVEL,
2127 G_PKG_NAME, '11000: Leaving Delete_set' );
2128 END IF;
2129
2130 EXCEPTION
2131 WHEN E_Resource_Busy THEN
2132 /*IF (l_debug = 'Y') THEN
2133 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2134 END IF;*/
2135
2136 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2137 FND_LOG.STRING(G_EXCP_LEVEL,
2138 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
2139 END IF;
2140
2141 IF (lock_csr%ISOPEN) THEN
2142 CLOSE lock_csr;
2143 END IF;
2144 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2145
2146 x_return_status := G_RET_STS_ERROR ;
2147 WHEN FND_API.G_EXC_ERROR THEN
2148 /*IF (l_debug = 'Y') THEN
2149 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
2150 END IF;*/
2151
2152 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2153 FND_LOG.STRING(G_EXCP_LEVEL,
2154 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
2155 END IF;
2156
2157 IF (lock_csr%ISOPEN) THEN
2158 CLOSE lock_csr;
2159 END IF;
2160 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2161 x_return_status := G_RET_STS_ERROR;
2162
2163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2164 /*IF (l_debug = 'Y') THEN
2165 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2166 END IF;*/
2167
2168 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2169 FND_LOG.STRING(G_EXCP_LEVEL,
2170 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2171 END IF;
2172
2173 IF (lock_csr%ISOPEN) THEN
2174 CLOSE lock_csr;
2175 END IF;
2176 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2177 x_return_status := G_RET_STS_UNEXP_ERROR;
2178
2179 WHEN OTHERS THEN
2180 /*IF (l_debug = 'Y') THEN
2181 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
2182 END IF;*/
2183
2184 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2185 FND_LOG.STRING(G_EXCP_LEVEL,
2186 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
2187 END IF;
2188
2189 IF (lock_csr%ISOPEN) THEN
2190 CLOSE lock_csr;
2191 END IF;
2192 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2193 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2194 p_msg_name => G_UNEXPECTED_ERROR,
2195 p_token1 => G_SQLCODE_TOKEN,
2196 p_token1_value => sqlcode,
2197 p_token2 => G_SQLERRM_TOKEN,
2198 p_token2_value => sqlerrm);
2199 x_return_status := G_RET_STS_UNEXP_ERROR;
2200
2201 END Delete_Set;
2202
2203 --This function is to be called from versioning API OKC_VERSION_PVT
2204 -- Location: Base Table API
2205 FUNCTION Create_Version(
2206 p_doc_type IN VARCHAR2,
2207 p_doc_id IN NUMBER,
2208 p_major_version IN NUMBER
2209 ) RETURN VARCHAR2 IS
2210 BEGIN
2211
2212 /*IF (l_debug = 'Y') THEN
2213 Okc_Debug.Log('9700: Entered create_version', 2);
2214 Okc_Debug.Log('9800: Saving Base Table', 2);
2215 END IF;*/
2216
2217 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2218 FND_LOG.STRING(G_PROC_LEVEL,
2219 G_PKG_NAME, '9700: Entered create_version');
2220 FND_LOG.STRING(G_PROC_LEVEL,
2221 G_PKG_NAME, '9800: Saving Base Table');
2222 END IF;
2223
2224 -----------------------------------------
2225 -- Saving Base Table
2226 -----------------------------------------
2227 INSERT INTO OKC_K_ART_VARIABLES_H (
2228 major_version,
2229 CAT_ID,
2230 VARIABLE_CODE,
2231 VARIABLE_TYPE,
2232 EXTERNAL_YN,
2233 VARIABLE_VALUE_ID,
2234 VARIABLE_VALUE,
2235 ATTRIBUTE_VALUE_SET_ID,
2236 override_global_yn,
2237 OBJECT_VERSION_NUMBER,
2238 CREATED_BY,
2239 CREATION_DATE,
2240 LAST_UPDATED_BY,
2241 LAST_UPDATE_LOGIN,
2242 LAST_UPDATE_DATE)
2243 SELECT
2244 p_major_version,
2245 CAT_ID,
2246 VARIABLE_CODE,
2247 VARIABLE_TYPE,
2248 EXTERNAL_YN,
2249 VARIABLE_VALUE_ID,
2250 VARIABLE_VALUE,
2251 ATTRIBUTE_VALUE_SET_ID,
2252 OVERRIDE_GLOBAL_YN,
2253 OBJECT_VERSION_NUMBER,
2254 CREATED_BY,
2255 CREATION_DATE,
2256 LAST_UPDATED_BY,
2257 LAST_UPDATE_LOGIN,
2258 LAST_UPDATE_DATE
2259 FROM OKC_K_ART_VARIABLES
2260 WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_B
2261 WHERE DOCUMENT_TYPE = P_DOC_TYPE
2262 AND DOCUMENT_ID = P_DOC_ID);
2263
2264 /*IF (l_debug = 'Y') THEN
2265 Okc_Debug.Log('10000: Leaving create_version', 2);
2266 END IF;*/
2267
2268 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2269 FND_LOG.STRING(G_PROC_LEVEL,
2270 G_PKG_NAME, '10000: Leaving create_version' );
2271 END IF;
2272
2273 RETURN( G_RET_STS_SUCCESS );
2274
2275 EXCEPTION
2276 WHEN OTHERS THEN
2277
2278 /*IF (l_debug = 'Y') THEN
2279 Okc_Debug.Log('10100: Leaving create_version because of EXCEPTION: '||sqlerrm, 2);
2280 END IF;*/
2281
2282 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2283 FND_LOG.STRING(G_EXCP_LEVEL,
2284 G_PKG_NAME, '10100: Leaving create_version because of EXCEPTION: '||sqlerrm );
2285 END IF;
2286
2287 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2288 p_msg_name => G_UNEXPECTED_ERROR,
2289 p_token1 => G_SQLCODE_TOKEN,
2290 p_token1_value => sqlcode,
2291 p_token2 => G_SQLERRM_TOKEN,
2292 p_token2_value => sqlerrm);
2293
2294 RETURN G_RET_STS_UNEXP_ERROR ;
2295
2296 END create_version;
2297
2298 --This Function is called from Versioning API OKC_VERSION_PVT
2299 -- Location:Base Table API
2300 --?? remove the function if the aPI doesn't need it
2301
2302 FUNCTION Restore_Version(
2303 p_doc_type IN VARCHAR2,
2304 p_doc_id IN NUMBER,
2305 p_major_version IN NUMBER
2306 ) RETURN VARCHAR2 IS
2307
2308 BEGIN
2309
2310 /*IF (l_debug = 'Y') THEN
2311 Okc_Debug.Log('10200: Entered restore_version', 2);
2312 Okc_Debug.Log('10300: Restoring Base Table', 2);
2313 END IF;*/
2314
2315 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2316 FND_LOG.STRING(G_PROC_LEVEL,
2317 G_PKG_NAME, '10200: Entered restore_version');
2318 FND_LOG.STRING(G_PROC_LEVEL,
2319 G_PKG_NAME, '10300: Restoring Base Table');
2320 END IF;
2321
2322 -----------------------------------------
2323 -- Restoring Base Table
2324 -----------------------------------------
2325 INSERT INTO OKC_K_ART_VARIABLES (
2326 CAT_ID,
2327 VARIABLE_CODE,
2328 VARIABLE_TYPE,
2329 EXTERNAL_YN,
2330 VARIABLE_VALUE_ID,
2331 VARIABLE_VALUE,
2332 ATTRIBUTE_VALUE_SET_ID,
2333 OVERRIDE_GLOBAL_YN,
2334 OBJECT_VERSION_NUMBER,
2335 CREATED_BY,
2336 CREATION_DATE,
2337 LAST_UPDATED_BY,
2338 LAST_UPDATE_LOGIN,
2339 LAST_UPDATE_DATE)
2340 SELECT
2341 CAT_ID,
2342 VARIABLE_CODE,
2343 VARIABLE_TYPE,
2344 EXTERNAL_YN,
2345 VARIABLE_VALUE_ID,
2346 VARIABLE_VALUE,
2347 ATTRIBUTE_VALUE_SET_ID,
2348 override_global_YN,
2349 OBJECT_VERSION_NUMBER,
2350 CREATED_BY,
2351 CREATION_DATE,
2352 LAST_UPDATED_BY,
2353 LAST_UPDATE_LOGIN,
2354 LAST_UPDATE_DATE
2355 FROM OKC_K_ART_VARIABLES_H
2356 WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_BH
2357 WHERE DOCUMENT_TYPE = P_DOC_TYPE
2358 AND DOCUMENT_ID = P_DOC_ID)
2359 AND major_version = p_major_version;
2360
2361 /*IF (l_debug = 'Y') THEN
2362 Okc_Debug.Log('10500: Leaving restore_version', 2);
2363 END IF;*/
2364
2365 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2366 FND_LOG.STRING(G_PROC_LEVEL,
2367 G_PKG_NAME, '10500: Leaving restore_version' );
2368 END IF;
2369
2370 RETURN( G_RET_STS_SUCCESS );
2371
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374
2375 /*IF (l_debug = 'Y') THEN
2376 Okc_Debug.Log('10600: Leaving restore_version because of EXCEPTION: '||sqlerrm, 2);
2377 END IF;*/
2378
2379 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2380 FND_LOG.STRING(G_EXCP_LEVEL,
2381 G_PKG_NAME, '10600: Leaving restore_version because of EXCEPTION: '||sqlerrm );
2382 END IF;
2383
2384 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2385 p_msg_name => G_UNEXPECTED_ERROR,
2386 p_token1 => G_SQLCODE_TOKEN,
2387 p_token1_value => sqlcode,
2388 p_token2 => G_SQLERRM_TOKEN,
2389 p_token2_value => sqlerrm);
2390
2391 RETURN G_RET_STS_UNEXP_ERROR ;
2392
2393 END restore_version;
2394
2395 FUNCTION Delete_Version(
2396 p_doc_type IN VARCHAR2,
2397 p_doc_id IN NUMBER,
2398 p_major_version IN NUMBER
2399 ) RETURN VARCHAR2 IS
2400
2401 BEGIN
2402
2403 /*IF (l_debug = 'Y') THEN
2404 Okc_Debug.Log('7200: Entered Delete_Version', 2);
2405 END IF;*/
2406
2407 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2408 FND_LOG.STRING(G_PROC_LEVEL,
2409 G_PKG_NAME, '7200: Entered Delete_Version' );
2410 END IF;
2411
2412 -----------------------------------------
2413 -- Restoring Base Table
2414 -----------------------------------------
2415 DELETE
2416 FROM OKC_K_ART_VARIABLES_H
2417 WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_BH
2418 WHERE document_type = p_doc_type and document_id = p_doc_id)
2419 AND major_version = p_major_version;
2420
2421 /*IF (l_debug = 'Y') THEN
2422 Okc_Debug.Log('7300: Leaving Delete_Version', 2);
2423 END IF;*/
2424
2425 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2426 FND_LOG.STRING(G_PROC_LEVEL,
2427 G_PKG_NAME, '7300: Leaving Delete_Version' );
2428 END IF;
2429
2430 RETURN( G_RET_STS_SUCCESS );
2431
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434
2435 /*IF (l_debug = 'Y') THEN
2436 Okc_Debug.Log('7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm, 2);
2437 END IF;*/
2438
2439 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2440 FND_LOG.STRING(G_EXCP_LEVEL,
2441 G_PKG_NAME, '7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm );
2442 END IF;
2443
2444 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2445 p_msg_name => G_UNEXPECTED_ERROR,
2446 p_token1 => G_SQLCODE_TOKEN,
2447 p_token1_value => sqlcode,
2448 p_token2 => G_SQLERRM_TOKEN,
2449 p_token2_value => sqlerrm);
2450
2451 RETURN G_RET_STS_UNEXP_ERROR ;
2452
2453 END Delete_Version;
2454
2455 END OKC_K_ART_VARIABLES_PVT;