[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.6 2011/12/09 13:54:06 serukull ship $ */
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 PROCEDURE delete_mrv_uda_data(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2,p_major_version IN NUMBER);
58
59 FUNCTION isArtVariableMRV(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2) RETURN VARCHAR2;
60
61 PROCEDURE create_mrv_uda_data_version(p_cat_id IN NUMBER, p_major_version IN NUMBER);
62
63 ---------------------------------------------------------------------------
64 -- FUNCTION get_rec for: OKC_K_ART_VARIABLES
65 ---------------------------------------------------------------------------
66 FUNCTION Get_Rec (
67 p_cat_id IN NUMBER,
68 p_variable_code IN VARCHAR2,
69 x_variable_type OUT NOCOPY VARCHAR2,
70 x_external_yn OUT NOCOPY VARCHAR2,
71 x_variable_value_id OUT NOCOPY VARCHAR2,
72 x_variable_value OUT NOCOPY VARCHAR2,
73 x_attribute_value_set_id OUT NOCOPY NUMBER,
74 x_override_global_yn OUT NOCOPY VARCHAR2,
75 x_object_version_number OUT NOCOPY NUMBER,
76 x_created_by OUT NOCOPY NUMBER,
77 x_creation_date OUT NOCOPY DATE,
78 x_last_updated_by OUT NOCOPY NUMBER,
79 x_last_update_login OUT NOCOPY NUMBER,
80 x_last_update_date OUT NOCOPY DATE
81
82 ) RETURN VARCHAR2 IS
83 CURSOR OKC_K_ART_VARIABLES_pk_csr (cp_cat_id IN NUMBER,cp_variable_code IN VARCHAR2) IS
84 SELECT
85 VARIABLE_TYPE,
86 EXTERNAL_YN,
87 VARIABLE_VALUE_ID,
88 VARIABLE_VALUE,
89 ATTRIBUTE_VALUE_SET_ID,
90 OVERRiDE_GLOBAL_YN,
91 OBJECT_VERSION_NUMBER,
92 CREATED_BY,
93 CREATION_DATE,
94 LAST_UPDATED_BY,
95 LAST_UPDATE_LOGIN,
96 LAST_UPDATE_DATE
97 FROM OKC_K_ART_VARIABLES t
98 WHERE t.CAT_ID = cp_cat_id and
99 t.VARIABLE_CODE = cp_variable_code;
100 BEGIN
101
102 /*IF (l_debug = 'Y') THEN
103 Okc_Debug.Log('400: Entered get_rec', 2);
104 END IF;*/
105
106 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
107 FND_LOG.STRING(G_PROC_LEVEL,
108 G_PKG_NAME, '400: Entered get_rec' );
109 END IF;
110
111 -- Get current database values
112 OPEN OKC_K_ART_VARIABLES_pk_csr (p_cat_id, p_variable_code);
113 FETCH OKC_K_ART_VARIABLES_pk_csr INTO
114 x_variable_type,
115 x_external_yn,
116 x_variable_value_id,
117 x_variable_value,
118 x_attribute_value_set_id,
119 x_override_global_yn,
120 x_object_version_number,
121 x_created_by,
122 x_creation_date,
123 x_last_updated_by,
124 x_last_update_login,
125 x_last_update_date;
126 IF OKC_K_ART_VARIABLES_pk_csr%NOTFOUND THEN
127 RAISE NO_DATA_FOUND;
128 END IF;
129 CLOSE OKC_K_ART_VARIABLES_pk_csr;
130
131 /*IF (l_debug = 'Y') THEN
132 Okc_Debug.Log('500: Leaving get_rec ', 2);
133 END IF;*/
134
135 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
136 FND_LOG.STRING(G_PROC_LEVEL,
137 G_PKG_NAME, '500: Leaving get_rec ' );
138 END IF;
139
140 RETURN G_RET_STS_SUCCESS ;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144
145 /*IF (l_debug = 'Y') THEN
146 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
147 END IF;*/
148
149 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
150 FND_LOG.STRING(G_PROC_LEVEL,
151 G_PKG_NAME, '600: Leaving get_rec because of EXCEPTION: '||sqlerrm );
152 END IF;
153
154 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
155 p_msg_name => G_UNEXPECTED_ERROR,
156 p_token1 => G_SQLCODE_TOKEN,
157 p_token1_value => sqlcode,
158 p_token2 => G_SQLERRM_TOKEN,
159 p_token2_value => sqlerrm);
160
161 IF OKC_K_ART_VARIABLES_pk_csr%ISOPEN THEN
162 CLOSE OKC_K_ART_VARIABLES_pk_csr;
163 END IF;
164
165 RETURN G_RET_STS_UNEXP_ERROR ;
166
167 END Get_Rec;
168
169 -----------------------------------------
170 -- Set_Attributes for:OKC_K_ART_VARIABLES --
171 -----------------------------------------
172 FUNCTION Set_Attributes(
173 p_cat_id IN NUMBER,
174 p_variable_code IN VARCHAR2,
175 p_variable_type IN VARCHAR2,
176 p_external_yn IN VARCHAR2,
177 p_variable_value_id IN VARCHAR2,
178 p_variable_value IN VARCHAR2,
179 p_attribute_value_set_id IN NUMBER,
180 p_override_global_yn IN VARCHAR2,
181 p_object_version_number IN NUMBER,
182
183 x_variable_type OUT NOCOPY VARCHAR2,
184 x_object_version_number OUT NOCOPY VARCHAR2,
185 x_external_yn OUT NOCOPY VARCHAR2,
186 x_variable_value_id OUT NOCOPY VARCHAR2,
187 x_variable_value OUT NOCOPY VARCHAR2,
188 x_attribute_value_set_id OUT NOCOPY NUMBER,
189 x_override_global_yn OUT NOCOPY VARCHAR2
190 ) RETURN VARCHAR2 IS
191 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
192 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
193 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
194 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
195 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
196 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
197 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
198 BEGIN
199 /*IF (l_debug = 'Y') THEN
200 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
201 END IF;*/
202
203 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
204 FND_LOG.STRING(G_PROC_LEVEL,
205 G_PKG_NAME, '700: Entered Set_Attributes ' );
206 END IF;
207
208 IF( p_cat_id IS NOT NULL AND p_variable_code IS NOT NULL ) THEN
209 -- Get current database values
210 l_return_status := Get_Rec(
211 p_cat_id => p_cat_id,
212 p_variable_code => p_variable_code,
213 x_variable_type => x_variable_type,
214 x_external_yn => x_external_yn,
215 x_variable_value_id => x_variable_value_id,
216 x_variable_value => x_variable_value,
217 x_attribute_value_set_id => x_attribute_value_set_id,
218 x_override_global_yn => x_override_global_yn,
219 x_object_version_number => x_object_version_number,
220 x_created_by => l_created_by,
221 x_creation_date => l_creation_date,
222 x_last_updated_by => l_last_updated_by,
223 x_last_update_login => l_last_update_login,
224 x_last_update_date => l_last_update_date
225 );
226 --- If any errors happen abort API
227 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232
233 --- Reversing G_MISS/NULL values logic
234
235 IF (p_variable_type = G_MISS_CHAR) THEN
236 x_variable_type := NULL;
237 ELSIF (p_VARIABLE_TYPE IS NOT NULL) THEN
238 x_variable_type := p_variable_type;
239 END IF;
240
241 IF (p_external_yn = G_MISS_CHAR) THEN
242 x_external_yn := NULL;
243 ELSIF (p_EXTERNAL_YN IS NOT NULL) THEN
244 x_external_yn := p_external_yn;
245 x_external_yn := Upper( x_external_yn );
246 END IF;
247
248 IF (p_variable_value_id = G_MISS_CHAR) THEN
249 x_variable_value_id := NULL;
250 ELSIF (p_VARIABLE_VALUE_ID IS NOT NULL) THEN
251 x_variable_value_id := p_variable_value_id;
252 END IF;
253
254 IF (p_variable_value = G_MISS_CHAR) THEN
255 x_variable_value := NULL;
256 ELSIF (p_VARIABLE_VALUE IS NOT NULL) THEN
257 x_variable_value := p_variable_value;
258 END IF;
259
260 IF (p_attribute_value_set_id = G_MISS_NUM) THEN
261 x_attribute_value_set_id := NULL;
262 ELSIF (p_ATTRIBUTE_VALUE_SET_ID IS NOT NULL) THEN
263 x_attribute_value_set_id := p_attribute_value_set_id;
264 END IF;
265
266
267 IF (p_override_global_yn = G_MISS_CHAR) THEN
268 x_override_global_yn := NULL;
269 ELSIF (p_override_global_yn IS NOT NULL) THEN
270 x_override_global_yn := p_override_global_yn;
271 x_override_global_yn := upper(x_override_global_yn);
272 END IF;
273 END IF;
274
275 /*IF (l_debug = 'Y') THEN
276 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
277 END IF;*/
278
279 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
280 FND_LOG.STRING(G_PROC_LEVEL,
281 G_PKG_NAME, '800: Leaving Set_Attributes ' );
282 END IF;
283
284 RETURN G_RET_STS_SUCCESS ;
285 EXCEPTION
286 WHEN FND_API.G_EXC_ERROR THEN
287 /*IF (l_debug = 'Y') THEN
288 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
289 END IF;*/
290
291 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
292 FND_LOG.STRING(G_PROC_LEVEL,
293 G_PKG_NAME, '900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception' );
294 END IF;
295 RETURN G_RET_STS_ERROR;
296
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 /*IF (l_debug = 'Y') THEN
299 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
300 END IF;*/
301
302 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
303 FND_LOG.STRING(G_PROC_LEVEL,
304 G_PKG_NAME, '1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
305 END IF;
306 RETURN G_RET_STS_UNEXP_ERROR;
307
308 WHEN OTHERS THEN
309 /*IF (l_debug = 'Y') THEN
310 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
311 END IF;*/
312
313 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
314 FND_LOG.STRING(G_PROC_LEVEL,
315 G_PKG_NAME, '1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm );
316 END IF;
317 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
318 p_msg_name => G_UNEXPECTED_ERROR,
319 p_token1 => G_SQLCODE_TOKEN,
320 p_token1_value => sqlcode,
321 p_token2 => G_SQLERRM_TOKEN,
322 p_token2_value => sqlerrm);
323 RETURN G_RET_STS_UNEXP_ERROR;
324
325 END Set_Attributes ;
326
327 ----------------------------------------------
328 -- Validate_Attributes for: OKC_K_ART_VARIABLES --
329 ----------------------------------------------
330 FUNCTION Validate_Attributes (
331 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
332
333 p_cat_id IN NUMBER,
334 p_variable_code IN VARCHAR2,
335 p_variable_type IN VARCHAR2,
336 p_external_yn IN VARCHAR2,
337 p_variable_value_id IN VARCHAR2,
338 p_variable_value IN VARCHAR2,
339 p_attribute_value_set_id IN NUMBER,
340 p_override_global_yn IN VARCHAR2
341 ) RETURN VARCHAR2 IS
342 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
343 l_dummy_var VARCHAR2(1) := '?';
344
345 CURSOR l_cat_id_csr is
346 SELECT '!'
347 FROM OKC_K_ARTICLES_B
348 WHERE ID = p_cat_id;
349
350 CURSOR l_attribute_value_set_id_csr is
351 SELECT '!'
352 FROM fnd_flex_value_sets
353 WHERE FLEX_VALUE_SET_ID = p_attribute_value_set_id;
354
355 CURSOR l_variable_code_csr is
356 SELECT '!'
357 FROM OKC_BUS_VARIABLES_B
358 WHERE VARIABLE_CODE = p_VARIABLE_CODE;
359 BEGIN
360
361 /*IF (l_debug = 'Y') THEN
362 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
363 END IF;*/
364
365 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
366 FND_LOG.STRING(G_PROC_LEVEL,
367 G_PKG_NAME, '1200: Entered Validate_Attributes' );
368 END IF;
369
370 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
371 /*IF (l_debug = 'Y') THEN
372 Okc_Debug.Log('1300: required values validation', 2);
373 END IF;*/
374
375 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
376 FND_LOG.STRING(G_PROC_LEVEL,
377 G_PKG_NAME, '1300: required values validation' );
378 END IF;
379
380 /*IF (l_debug = 'Y') THEN
381 Okc_Debug.Log('1400: - attribute CAT_ID ', 2);
382 END IF;*/
383
384 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
385 FND_LOG.STRING(G_PROC_LEVEL,
386 G_PKG_NAME, '1400: - attribute CAT_ID ' );
387 END IF;
388 IF ( p_cat_id IS NULL) THEN
389 /*IF (l_debug = 'Y') THEN
390 Okc_Debug.Log('1500: - attribute CAT_ID is invalid', 2);
391 END IF;*/
392
393 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
394 FND_LOG.STRING(G_PROC_LEVEL,
395 G_PKG_NAME, '1500: - attribute CAT_ID is invalid' );
396 END IF;
397 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CAT_ID');
398 l_return_status := G_RET_STS_ERROR;
399 END IF;
400
401 /*IF (l_debug = 'Y') THEN
402 Okc_Debug.Log('1400: - attribute VARIABLE_CODE ', 2);
403 END IF;*/
404
405 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
406 FND_LOG.STRING(G_PROC_LEVEL,
407 G_PKG_NAME, '1400: - attribute VARIABLE_CODE ' );
408 END IF;
409 IF ( p_variable_code IS NULL) THEN
410 /*IF (l_debug = 'Y') THEN
411 Okc_Debug.Log('1500: - attribute VARIABLE_CODE is invalid', 2);
412 END IF;*/
413
414 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
415 FND_LOG.STRING(G_PROC_LEVEL,
416 G_PKG_NAME, '1500: - attribute VARIABLE_CODE is invalid' );
417 END IF;
418 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'VARIABLE_CODE');
419 l_return_status := G_RET_STS_ERROR;
420 END IF;
421
422 /*IF (l_debug = 'Y') THEN
423 Okc_Debug.Log('1400: - attribute VARIABLE_TYPE ', 2);
424 END IF;*/
425
426 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
427 FND_LOG.STRING(G_PROC_LEVEL,
428 G_PKG_NAME, '1400: - attribute VARIABLE_TYPE ' );
429 END IF;
430 IF ( p_variable_type IS NULL) THEN
431 /*IF (l_debug = 'Y') THEN
432 Okc_Debug.Log('1500: - attribute VARIABLE_TYPE is invalid', 2);
433 END IF;*/
434
435 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
436 FND_LOG.STRING(G_PROC_LEVEL,
437 G_PKG_NAME, '1500: - attribute VARIABLE_TYPE is invalid' );
438 END IF;
439 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'VARIABLE_TYPE');
440 l_return_status := G_RET_STS_ERROR;
441 END IF;
442
443 /*IF (l_debug = 'Y') THEN
444 Okc_Debug.Log('1400: - attribute EXTERNAL_YN ', 2);
445 END IF;*/
446
447 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
448 FND_LOG.STRING(G_PROC_LEVEL,
449 G_PKG_NAME, '1400: - attribute EXTERNAL_YN ' );
450 END IF;
451 IF ( p_external_yn IS NULL) THEN
452 /*IF (l_debug = 'Y') THEN
453 Okc_Debug.Log('1500: - attribute EXTERNAL_YN is invalid', 2);
454 END IF;*/
455
456 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
457 FND_LOG.STRING(G_PROC_LEVEL,
458 G_PKG_NAME, '1500: - attribute EXTERNAL_YN is invalid' );
459 END IF;
460 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EXTERNAL_YN');
461 l_return_status := G_RET_STS_ERROR;
462 END IF;
463
464 END IF;
465
466 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
467 /*IF (l_debug = 'Y') THEN
468 Okc_Debug.Log('1600: static values and range validation', 2);
469 END IF;*/
470
471 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
472 FND_LOG.STRING(G_PROC_LEVEL,
473 G_PKG_NAME, '1600: static values and range validation' );
474 END IF;
475
476 /*IF (l_debug = 'Y') THEN
477 Okc_Debug.Log('1700: - attribute EXTERNAL_YN ', 2);
478 END IF;*/
479
480 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
481 FND_LOG.STRING(G_PROC_LEVEL,
482 G_PKG_NAME, '1700: - attribute EXTERNAL_YN ' );
483 END IF;
484
485 IF ( p_external_yn NOT IN ('Y','N') AND p_external_yn IS NOT NULL) THEN
486 /*IF (l_debug = 'Y') THEN
487 Okc_Debug.Log('1800: - attribute EXTERNAL_YN is invalid', 2);
488 END IF;*/
489
490 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
491 FND_LOG.STRING(G_PROC_LEVEL,
492 G_PKG_NAME, '1800: - attribute EXTERNAL_YN is invalid' );
493 END IF;
494 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'EXTERNAL_YN');
495 l_return_status := G_RET_STS_ERROR;
496 END IF;
497
498
499 /*IF (l_debug = 'Y') THEN
500 Okc_Debug.Log('1700: - attribute OVERRIDE_GLOBAL_YN ', 2);
501 END IF;*/
502
503 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
504 FND_LOG.STRING(G_PROC_LEVEL,
505 G_PKG_NAME, '1700: - attribute OVERRIDE_GLOBAL_YN ' );
506 END IF;
507
508 IF ( p_OVERRIDE_GLOBAL_YN NOT IN ('Y','N') AND p_OVERRIDE_GLOBAL_YN IS NOT NULL) THEN
509 /*IF (l_debug = 'Y') THEN
510 Okc_Debug.Log('1800: - attribute OVERRIDE_GLOBAL_YN is invalid', 2);
511 END IF;*/
512
513 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
514 FND_LOG.STRING(G_PROC_LEVEL,
515 G_PKG_NAME, '1800: - attribute OVERRIDE_GLOBAL_YN is invalid' );
516 END IF;
517
518 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'OVERRIDE_GLOBAL_YN');
519 l_return_status := G_RET_STS_ERROR;
520 END IF;
521 END IF;
522
523 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
524 /*IF (l_debug = 'Y') THEN
525 Okc_Debug.Log('1900: lookup codes validation', 2);
526 END IF;*/
527
528 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
529 FND_LOG.STRING(G_PROC_LEVEL,
530 G_PKG_NAME, '1900: lookup codes validation' );
531 END IF;
532 END IF;
533
534 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
535 /*IF (l_debug = 'Y') THEN
536 Okc_Debug.Log('2100: foreign keys validation ', 2);
537 END IF;*/
538
539 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
540 FND_LOG.STRING(G_PROC_LEVEL,
541 G_PKG_NAME, '2100: foreign keys validation ' );
542 END IF;
543
544 /*IF (l_debug = 'Y') THEN
545 Okc_Debug.Log('2200: - attribute CAT_ID ', 2);
546 END IF;*/
547
548 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
549 FND_LOG.STRING(G_PROC_LEVEL,
550 G_PKG_NAME, '2200: - attribute CAT_ID ' );
551 END IF;
552
553 IF p_cat_id IS NOT NULL THEN
554 l_dummy_var := '?';
555 OPEN l_cat_id_csr;
556 FETCH l_cat_id_csr INTO l_dummy_var;
557 CLOSE l_cat_id_csr;
558 IF (l_dummy_var = '?') THEN
559 /*IF (l_debug = 'Y') THEN
560 Okc_Debug.Log('2300: - attribute CAT_ID is invalid', 2);
561 END IF;*/
562
563 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
564 FND_LOG.STRING(G_PROC_LEVEL,
565 G_PKG_NAME, '2300: - attribute CAT_ID is invalid' );
566 END IF;
567 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CAT_ID');
568 l_return_status := G_RET_STS_ERROR;
569 END IF;
570 END IF;
571 /*
572 -- IF (l_debug = 'Y') THEN
573 -- Okc_Debug.Log('2200: - attribute VARIABLE_VALUE_ID ', 2);
574 -- END IF;
575 -- IF p_variable_value_id IS NOT NULL THEN
576 -- l_dummy_var := '?';
577 -- OPEN l_variable_value_id_csr;
578 -- FETCH l_variable_value_id_csr INTO l_dummy_var;
579 -- CLOSE l_variable_value_id_csr;
580 -- IF (l_dummy_var = '?') THEN
581 -- IF (l_debug = 'Y') THEN
582 -- Okc_Debug.Log('2300: - attribute VARIABLE_VALUE_ID is invalid', 2);
583 -- END IF;
584 -- Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'VARIABLE_VALUE_ID');
585 -- l_return_status := G_RET_STS_ERROR;
586 -- END IF;
587 -- END IF;
588 */
589 /*IF (l_debug = 'Y') THEN
590 Okc_Debug.Log('2200: - attribute ATTRIBUTE_VALUE_SET_ID ', 2);
591 END IF;*/
592
593 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
594 FND_LOG.STRING(G_PROC_LEVEL,
595 G_PKG_NAME, '2200: - attribute ATTRIBUTE_VALUE_SET_ID ' );
596 END IF;
597 IF p_attribute_value_set_id IS NOT NULL THEN
598 l_dummy_var := '?';
599 OPEN l_attribute_value_set_id_csr;
600 FETCH l_attribute_value_set_id_csr INTO l_dummy_var;
601 CLOSE l_attribute_value_set_id_csr;
602 IF (l_dummy_var = '?') THEN
603 /*IF (l_debug = 'Y') THEN
604 Okc_Debug.Log('2300: - attribute ATTRIBUTE_VALUE_SET_ID is invalid', 2);
605 END IF;*/
606
607 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
608 FND_LOG.STRING(G_PROC_LEVEL,
609 G_PKG_NAME, '2300: - attribute ATTRIBUTE_VALUE_SET_ID is invalid' );
610 END IF;
611 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ATTRIBUTE_VALUE_SET_ID');
612 l_return_status := G_RET_STS_ERROR;
613 END IF;
614 END IF;
615
616 /*IF (l_debug = 'Y') THEN
617 Okc_Debug.Log('2200: - attribute VARIABLE_CODE ', 2);
618 END IF;*/
619
620 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
621 FND_LOG.STRING(G_PROC_LEVEL,
622 G_PKG_NAME, '2200: - attribute VARIABLE_CODE ' );
623 END IF;
624 IF p_variable_code IS NOT NULL THEN
625 l_dummy_var := '?';
626 OPEN l_variable_code_csr;
627 FETCH l_variable_code_csr INTO l_dummy_var;
628 CLOSE l_variable_code_csr;
629 IF (l_dummy_var = '?') THEN
630 /*IF (l_debug = 'Y') THEN
631 Okc_Debug.Log('2300: - attribute VARIABLE_CODE is invalid', 2);
632 END IF;*/
633
634 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
635 FND_LOG.STRING(G_PROC_LEVEL,
636 G_PKG_NAME, '2300: - attribute VARIABLE_CODE is invalid' );
637 END IF;
638 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'VARIABLE_CODE');
639 l_return_status := G_RET_STS_ERROR;
640 END IF;
641 END IF;
642 END IF;
643
644
645 /*IF (l_debug = 'Y') THEN
646 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
647 END IF;*/
648
649 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
650 FND_LOG.STRING(G_PROC_LEVEL,
651 G_PKG_NAME, '2400: Leaving Validate_Attributes ' );
652 END IF;
653 RETURN l_return_status;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 --Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
658 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
659 FND_LOG.STRING(G_EXCP_LEVEL,
660 G_PKG_NAME, '2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm );
661 END IF;
662 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
663 p_msg_name => G_UNEXPECTED_ERROR,
664 p_token1 => G_SQLCODE_TOKEN,
665 p_token1_value => sqlcode,
666 p_token2 => G_SQLERRM_TOKEN,
667 p_token2_value => sqlerrm);
668
669
670 IF l_cat_id_csr%ISOPEN THEN
671 CLOSE l_cat_id_csr;
672 END IF;
673
674
675 IF l_attribute_value_set_id_csr%ISOPEN THEN
676 CLOSE l_attribute_value_set_id_csr;
677 END IF;
678
679 IF l_variable_code_csr%ISOPEN THEN
680 CLOSE l_variable_code_csr;
681 END IF;
682
683 RETURN G_RET_STS_UNEXP_ERROR;
684
685 END Validate_Attributes;
686
687
688 ---------------------------------------------------------------------------
689 -- PROCEDURE Validate_Record
690 -- It calls Item Level Validations and then makes Record Level Validations
691 ---------------------------------------------------------------------------
692 ------------------------------------------
693 -- Validate_Record for:OKC_K_ART_VARIABLES --
694 ------------------------------------------
695 FUNCTION Validate_Record (
696 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
697
698 p_cat_id IN NUMBER,
699 p_variable_code IN VARCHAR2,
700 p_variable_type IN VARCHAR2,
701 p_external_yn IN VARCHAR2,
702 p_variable_value_id IN VARCHAR2,
703 p_variable_value IN VARCHAR2,
704 p_attribute_value_set_id IN NUMBER,
705 p_override_global_yn IN VARCHAR2
706 ) RETURN VARCHAR2 IS
707 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
708 BEGIN
709
710 /*IF (l_debug = 'Y') THEN
711 Okc_Debug.Log('2600: Entered Validate_Record', 2);
712 END IF;*/
713
714 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
715 FND_LOG.STRING(G_PROC_LEVEL,
716 G_PKG_NAME, '2600: Entered Validate_Record' );
717 END IF;
718 --- Validate all non-missing attributes (Item Level Validation)
719 l_return_status := Validate_Attributes(
720 p_validation_level => p_validation_level,
721
722 p_cat_id => p_cat_id,
723 p_variable_code => p_variable_code,
724 p_variable_type => p_variable_type,
725 p_external_yn => p_external_yn,
726 p_variable_value_id => p_variable_value_id,
727 p_variable_value => p_variable_value,
728 p_attribute_value_set_id => p_attribute_value_set_id,
729 p_override_global_yn => p_override_global_yn
730 );
731 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
732 /*IF (l_debug = 'Y') THEN
733 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
734 END IF;*/
735
736 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
737 FND_LOG.STRING(G_PROC_LEVEL,
738 G_PKG_NAME, '2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm );
739 END IF;
740 RETURN G_RET_STS_UNEXP_ERROR;
741 END IF;
742
743 --- Record Level Validation
744 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
745 /*IF (l_debug = 'Y') THEN
746 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
747 END IF;*/
748
749 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
750 FND_LOG.STRING(G_PROC_LEVEL,
751 G_PKG_NAME, '2800: Entered Record Level Validations' );
752 END IF;
753
754 IF P_ATTRIBUTE_VALUE_SET_ID IS NOT NULL AND
755 P_VARIABLE_VALUE_ID IS NOT NULL THEN
756 /* Need to put check here */
757 Null;
758 END IF;
759
760
761 END IF;
762
763 /*IF (l_debug = 'Y') THEN
764 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
765 END IF;*/
766
767 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
768 FND_LOG.STRING(G_PROC_LEVEL,
769 G_PKG_NAME, '2900: Leaving Validate_Record : '||sqlerrm );
770 END IF;
771
772 RETURN l_return_status ;
773
774 EXCEPTION
775 WHEN OTHERS THEN
776
777 /*IF (l_debug = 'Y') THEN
778 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
779 END IF;*/
780
781 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
782 FND_LOG.STRING(G_EXCP_LEVEL,
783 G_PKG_NAME, '3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm );
784 END IF;
785 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
786 p_msg_name => G_UNEXPECTED_ERROR,
787 p_token1 => G_SQLCODE_TOKEN,
788 p_token1_value => sqlcode,
789 p_token2 => G_SQLERRM_TOKEN,
790 p_token2_value => sqlerrm);
791 RETURN G_RET_STS_UNEXP_ERROR ;
792
793 END Validate_Record;
794
795 ---------------------------------------------------------------------------
796 -- PROCEDURE validate_row
797 ---------------------------------------------------------------------------
798 ---------------------------------------
799 -- validate_row for:OKC_K_ART_VARIABLES --
800 ---------------------------------------
801 PROCEDURE validate_row(
802 p_validation_level IN NUMBER,
803 x_return_status OUT NOCOPY VARCHAR2,
804 p_cat_id IN NUMBER,
805 p_variable_code IN VARCHAR2,
806 p_variable_type IN VARCHAR2,
807 p_external_yn IN VARCHAR2,
808 p_variable_value_id IN VARCHAR2,
809 p_variable_value IN VARCHAR2,
810 p_attribute_value_set_id IN NUMBER ,
811 p_override_global_yn IN VARCHAR2,
812
813 p_object_version_number IN NUMBER
814 ) IS
815 l_variable_type OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE;
816 l_external_yn OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE;
817 l_variable_value_id OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE;
818 l_variable_value OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE;
819 l_attribute_value_set_id OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE;
820 l_override_global_yn OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE;
821 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
822 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
823 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
824 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
825 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
826 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
827 BEGIN
828
829 /*IF (l_debug = 'Y') THEN
830 Okc_Debug.Log('3100: Entered validate_row', 2);
831 END IF;*/
832
833 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
834 FND_LOG.STRING(G_PROC_LEVEL,
835 G_PKG_NAME, '3100: Entered validate_row' );
836 END IF;
837
838 -- Setting attributes
839 x_return_status := Set_Attributes(
840 p_cat_id => p_cat_id,
841 p_variable_code => p_variable_code,
842 p_variable_type => p_variable_type,
843 p_external_yn => p_external_yn,
844 p_variable_value_id => p_variable_value_id,
845 p_variable_value => p_variable_value,
846 p_attribute_value_set_id => p_attribute_value_set_id,
847 p_override_global_yn => p_override_global_yn,
848 p_object_version_number => p_object_version_number,
849 x_variable_type => l_variable_type,
850 x_object_version_number => l_object_version_number,
851 x_external_yn => l_external_yn,
852 x_variable_value_id => l_variable_value_id,
853 x_variable_value => l_variable_value,
854 x_attribute_value_set_id => l_attribute_value_set_id,
855 x_override_global_yn => l_override_global_yn
856 );
857 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862
863 -- Validate all non-missing attributes (Item Level Validation)
864 x_return_status := Validate_Record(
865 p_validation_level => p_validation_level,
866 p_cat_id => p_cat_id,
867 p_variable_code => p_variable_code,
868 p_variable_type => l_variable_type,
869 p_external_yn => l_external_yn,
870 p_variable_value_id => l_variable_value_id,
871 p_variable_value => l_variable_value,
872 p_attribute_value_set_id => l_attribute_value_set_id,
873 p_override_global_yn => l_override_global_yn
874 );
875
876 /*IF (l_debug = 'Y') THEN
877 Okc_Debug.Log('3200: Leaving validate_row', 2);
878 END IF;*/
879
880 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
881 FND_LOG.STRING(G_PROC_LEVEL,
882 G_PKG_NAME, '3200: Leaving validate_row' );
883 END IF;
884
885 EXCEPTION
886 WHEN FND_API.G_EXC_ERROR THEN
887 /*IF (l_debug = 'Y') THEN
888 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
889 END IF;*/
890
891 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
892 FND_LOG.STRING(G_EXCP_LEVEL,
893 G_PKG_NAME, '3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception' );
894 END IF;
895 x_return_status := G_RET_STS_ERROR;
896
897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
898 /*IF (l_debug = 'Y') THEN
899 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
900 END IF;*/
901
902 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
903 FND_LOG.STRING(G_EXCP_LEVEL,
904 G_PKG_NAME, '3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
905 END IF;
906 x_return_status := G_RET_STS_UNEXP_ERROR;
907
908 WHEN OTHERS THEN
909 /*IF (l_debug = 'Y') THEN
910 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
911 END IF;*/
912
913 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
914 FND_LOG.STRING(G_EXCP_LEVEL,
915 G_PKG_NAME, '3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm );
916 END IF;
917 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
918 p_msg_name => G_UNEXPECTED_ERROR,
919 p_token1 => G_SQLCODE_TOKEN,
920 p_token1_value => sqlcode,
921 p_token2 => G_SQLERRM_TOKEN,
922 p_token2_value => sqlerrm);
923 x_return_status := G_RET_STS_UNEXP_ERROR;
924
925 END Validate_Row;
926
927 ---------------------------------------------------------------------------
928 -- PROCEDURE Insert_Row
929 ---------------------------------------------------------------------------
930 -------------------------------------
931 -- Insert_Row for:OKC_K_ART_VARIABLES --
932 -------------------------------------
933 FUNCTION Insert_Row(
934 p_cat_id IN NUMBER,
935 p_variable_code IN VARCHAR2,
936 p_variable_type IN VARCHAR2,
937 p_external_yn IN VARCHAR2,
938 p_variable_value_id IN VARCHAR2,
939 p_variable_value IN VARCHAR2,
940 p_attribute_value_set_id IN NUMBER,
941 p_override_global_yn IN VARCHAR2,
942 p_object_version_number IN NUMBER,
943 p_created_by IN NUMBER,
944 p_creation_date IN DATE,
945 p_last_updated_by IN NUMBER,
946 p_last_update_login IN NUMBER,
947 p_last_update_date IN DATE,
948 p_global_variable_value IN VARCHAR2 := NULL,
949 p_global_var_value_id IN NUMBER := NULL
950
951 ) RETURN VARCHAR2 IS
952
953 BEGIN
954
955 /*IF (l_debug = 'Y') THEN
956 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
957 END IF;*/
958
959 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
960 FND_LOG.STRING(G_PROC_LEVEL,
961 G_PKG_NAME, '3600: Entered Insert_Row function' );
962 END IF;
963 INSERT INTO OKC_K_ART_VARIABLES(
964 CAT_ID,
965 VARIABLE_CODE,
966 VARIABLE_TYPE,
967 EXTERNAL_YN,
968 VARIABLE_VALUE_ID,
969 VARIABLE_VALUE,
970 ATTRIBUTE_VALUE_SET_ID,
971 OVERRIDE_GLOBAL_YN,
972 OBJECT_VERSION_NUMBER,
973 CREATED_BY,
974 CREATION_DATE,
975 LAST_UPDATED_BY,
976 LAST_UPDATE_LOGIN,
977 LAST_UPDATE_DATE,
978 GLOBAL_VARIABLE_VALUE,
979 GLOBAL_VARIABLE_VALUE_ID)
980 VALUES (
981 p_cat_id,
982 p_variable_code,
983 p_variable_type,
984 p_external_yn,
985 p_variable_value_id,
986 p_variable_value,
987 p_attribute_value_set_id,
988 p_override_global_yn,
989 p_object_version_number,
990 p_created_by,
991 p_creation_date,
992 p_last_updated_by,
993 p_last_update_login,
994 p_last_update_date,
995 p_global_variable_value,
996 p_global_var_value_id);
997
998 /*IF (l_debug = 'Y') THEN
999 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
1000 END IF;*/
1001
1002 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1003 FND_LOG.STRING(G_PROC_LEVEL,
1004 G_PKG_NAME, '3700: Leaving Insert_Row' );
1005 END IF;
1006
1007 RETURN( G_RET_STS_SUCCESS );
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011
1012 /*IF (l_debug = 'Y') THEN
1013 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
1014 END IF;*/
1015
1016 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1017 FND_LOG.STRING(G_EXCP_LEVEL,
1018 G_PKG_NAME, '3800: Leaving Insert_Row:OTHERS Exception' );
1019 END IF;
1020
1021 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1022 p_msg_name => G_UNEXPECTED_ERROR,
1023 p_token1 => G_SQLCODE_TOKEN,
1024 p_token1_value => sqlcode,
1025 p_token2 => G_SQLERRM_TOKEN,
1026 p_token2_value => sqlerrm);
1027
1028 RETURN( G_RET_STS_UNEXP_ERROR );
1029
1030 END Insert_Row;
1031
1032
1033 -------------------------------------
1034 -- Insert_Row for:OKC_K_ART_VARIABLES --
1035 -------------------------------------
1036 PROCEDURE Insert_Row(
1037 p_validation_level IN NUMBER,
1038 x_return_status OUT NOCOPY VARCHAR2,
1039 p_cat_id IN NUMBER,
1040 p_variable_code IN VARCHAR2,
1041 p_variable_type IN VARCHAR2,
1042 p_external_yn IN VARCHAR2,
1043 p_variable_value_id IN VARCHAR2,
1044 p_variable_value IN VARCHAR2,
1045 p_attribute_value_set_id IN NUMBER,
1046 p_override_global_yn IN VARCHAR2,
1047 p_global_variable_value IN VARCHAR2 := NULL,
1048 p_global_var_value_id IN NUMBER := NULL,
1049 x_cat_id OUT NOCOPY NUMBER,
1050 x_variable_code OUT NOCOPY VARCHAR2
1051
1052 ) IS
1053
1054 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
1055 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
1056 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
1057 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
1058 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
1059 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
1060 BEGIN
1061
1062 x_return_status := G_RET_STS_SUCCESS;
1063
1064 /*IF (l_debug = 'Y') THEN
1065 Okc_Debug.Log('4200: Entered Insert_Row', 2);
1066 END IF;*/
1067
1068 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1069 FND_LOG.STRING(G_PROC_LEVEL,
1070 G_PKG_NAME, '4200: Entered Insert_Row' );
1071 END IF;
1072
1073 -- Set Internal columns
1074 l_object_version_number := 1;
1075 l_creation_date := Sysdate;
1076 l_created_by := Fnd_Global.User_Id;
1077 l_last_update_date := l_creation_date;
1078 l_last_updated_by := l_created_by;
1079 l_last_update_login := Fnd_Global.Login_Id;
1080
1081
1082 --- Validate all non-missing attributes
1083 x_return_status := Validate_Record(
1084 p_validation_level => p_validation_level,
1085 p_cat_id => p_cat_id,
1086 p_variable_code => p_variable_code,
1087 p_variable_type => p_variable_type,
1088 p_external_yn => p_external_yn,
1089 p_variable_value_id => p_variable_value_id,
1090 p_variable_value => p_variable_value,
1091 p_attribute_value_set_id => p_attribute_value_set_id,
1092 p_override_global_yn => p_override_global_yn
1093 );
1094 --- If any errors happen abort API
1095 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100
1101 --------------------------------------------
1102 -- Call the internal Insert_Row for each child record
1103 --------------------------------------------
1104 /*IF (l_debug = 'Y') THEN
1105 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
1106 END IF;*/
1107
1108 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1109 FND_LOG.STRING(G_PROC_LEVEL,
1110 G_PKG_NAME, '4300: Call the internal Insert_Row for Base Table' );
1111 END IF;
1112
1113 x_return_status := Insert_Row(
1114 p_cat_id => p_cat_id,
1115 p_variable_code => p_variable_code,
1116 p_variable_type => p_variable_type,
1117 p_external_yn => p_external_yn,
1118 p_variable_value_id => p_variable_value_id,
1119 p_variable_value => p_variable_value,
1120 p_attribute_value_set_id => p_attribute_value_set_id,
1121 p_override_global_yn => p_override_global_yn,
1122 p_object_version_number => l_object_version_number,
1123 p_created_by => l_created_by,
1124 p_creation_date => l_creation_date,
1125 p_last_updated_by => l_last_updated_by,
1126 p_last_update_login => l_last_update_login,
1127 p_last_update_date => l_last_update_date,
1128 p_global_variable_value => p_global_variable_value,
1129 p_global_var_value_id => p_global_var_value_id
1130 );
1131 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1133 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137
1138
1139 /*IF (l_debug = 'Y') THEN
1140 Okc_Debug.Log('4500: Leaving Insert_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, '4500: Leaving Insert_Row' );
1146 END IF;
1147
1148 EXCEPTION
1149 WHEN FND_API.G_EXC_ERROR THEN
1150 /*IF (l_debug = 'Y') THEN
1151 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
1152 END IF;*/
1153
1154 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1155 FND_LOG.STRING(G_EXCP_LEVEL,
1156 G_PKG_NAME, '4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception' );
1157 END IF;
1158 x_return_status := G_RET_STS_ERROR;
1159
1160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1161 /*IF (l_debug = 'Y') THEN
1162 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1163 END IF;*/
1164
1165 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1166 FND_LOG.STRING(G_EXCP_LEVEL,
1167 G_PKG_NAME, '4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1168 END IF;
1169 x_return_status := G_RET_STS_UNEXP_ERROR;
1170
1171 WHEN OTHERS THEN
1172 /*IF (l_debug = 'Y') THEN
1173 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1174 END IF;*/
1175
1176 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1177 FND_LOG.STRING(G_EXCP_LEVEL,
1178 G_PKG_NAME, '4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm );
1179 END IF;
1180 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1181 p_msg_name => G_UNEXPECTED_ERROR,
1182 p_token1 => G_SQLCODE_TOKEN,
1183 p_token1_value => sqlcode,
1184 p_token2 => G_SQLERRM_TOKEN,
1185 p_token2_value => sqlerrm);
1186 x_return_status := G_RET_STS_UNEXP_ERROR;
1187
1188 END Insert_Row;
1189 ---------------------------------------------------------------------------
1190 -- PROCEDURE Lock_Row
1191 ---------------------------------------------------------------------------
1192 -----------------------------------
1193 -- Lock_Row for:OKC_K_ART_VARIABLES --
1194 -----------------------------------
1195 FUNCTION Lock_Row(
1196 p_cat_id IN NUMBER,
1197 p_variable_code IN VARCHAR2,
1198 p_object_version_number IN NUMBER
1199 ) RETURN VARCHAR2 IS
1200
1201
1202 CURSOR lock_csr (cp_cat_id NUMBER, cp_variable_code VARCHAR2, cp_object_version_number NUMBER) IS
1203 SELECT object_version_number
1204 FROM OKC_K_ART_VARIABLES
1205 WHERE CAT_ID = cp_cat_id AND VARIABLE_CODE = cp_variable_code
1206 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1207 FOR UPDATE OF object_version_number NOWAIT;
1208
1209 CURSOR lchk_csr (cp_cat_id NUMBER, cp_variable_code VARCHAR2) IS
1210 SELECT object_version_number
1211 FROM OKC_K_ART_VARIABLES
1212 WHERE CAT_ID = cp_cat_id AND VARIABLE_CODE = cp_variable_code;
1213
1214 l_return_status VARCHAR2(1);
1215
1216 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
1217
1218 l_row_notfound BOOLEAN := FALSE;
1219 BEGIN
1220
1221 /*IF (l_debug = 'Y') THEN
1222 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1223 END IF;*/
1224
1225 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1226 FND_LOG.STRING(G_PROC_LEVEL,
1227 G_PKG_NAME, '4900: Entered Lock_Row' );
1228 END IF;
1229
1230 BEGIN
1231
1232 OPEN lock_csr( p_cat_id, p_variable_code, p_object_version_number );
1233 FETCH lock_csr INTO l_object_version_number;
1234 l_row_notfound := lock_csr%NOTFOUND;
1235 CLOSE lock_csr;
1236
1237 EXCEPTION
1238 WHEN E_Resource_Busy THEN
1239
1240 /*IF (l_debug = 'Y') THEN
1241 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1242 END IF;*/
1243
1244 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1245 FND_LOG.STRING(G_EXCP_LEVEL,
1246 G_PKG_NAME, '5000: Leaving Lock_Row:E_Resource_Busy Exception' );
1247 END IF;
1248
1249 IF (lock_csr%ISOPEN) THEN
1250 CLOSE lock_csr;
1251 END IF;
1252 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1253 RETURN( G_RET_STS_ERROR );
1254 END;
1255
1256 IF ( l_row_notfound ) THEN
1257 l_return_status := G_RET_STS_ERROR;
1258
1259 OPEN lchk_csr(p_cat_id, p_variable_code);
1260 FETCH lchk_csr INTO l_object_version_number;
1261 l_row_notfound := lchk_csr%NOTFOUND;
1262 CLOSE lchk_csr;
1263
1264 IF (l_row_notfound) THEN
1265 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
1266 ELSIF l_object_version_number > p_object_version_number THEN
1267 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1268 ELSIF l_object_version_number = -1 THEN
1269 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1270 ELSE -- it can be the only above condition. It can happen after restore version
1271 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1272 END IF;
1273 ELSE
1274 l_return_status := G_RET_STS_SUCCESS;
1275 END IF;
1276
1277 /*IF (l_debug = 'Y') THEN
1278 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1279 END IF;*/
1280
1281 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1282 FND_LOG.STRING(G_PROC_LEVEL,
1283 G_PKG_NAME, '5100: Leaving Lock_Row' );
1284 END IF;
1285
1286 RETURN( l_return_status );
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290
1291 IF (lock_csr%ISOPEN) THEN
1292 CLOSE lock_csr;
1293 END IF;
1294 IF (lchk_csr%ISOPEN) THEN
1295 CLOSE lchk_csr;
1296 END IF;
1297
1298 /*IF (l_debug = 'Y') THEN
1299 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1300 END IF;*/
1301
1302 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1303 FND_LOG.STRING(G_EXCP_LEVEL,
1304 G_PKG_NAME, '5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
1305 END IF;
1306
1307 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1308 p_msg_name => G_UNEXPECTED_ERROR,
1309 p_token1 => G_SQLCODE_TOKEN,
1310 p_token1_value => sqlcode,
1311 p_token2 => G_SQLERRM_TOKEN,
1312 p_token2_value => sqlerrm);
1313
1314 RETURN( G_RET_STS_UNEXP_ERROR );
1315 END Lock_Row;
1316
1317 -----------------------------------
1318 -- Lock_Row for:OKC_K_ART_VARIABLES --
1319 -----------------------------------
1320 PROCEDURE Lock_Row(
1321 x_return_status OUT NOCOPY VARCHAR2,
1322
1323 p_cat_id IN NUMBER,
1324 p_variable_code IN VARCHAR2,
1325 p_object_version_number IN NUMBER
1326 ) IS
1327 BEGIN
1328
1329 /*IF (l_debug = 'Y') THEN
1330 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1331 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1332 END IF;*/
1333
1334 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1335 FND_LOG.STRING(G_PROC_LEVEL,
1336 G_PKG_NAME, '5700: Entered Lock_Row');
1337 FND_LOG.STRING(G_PROC_LEVEL,
1338 G_PKG_NAME, '5800: Locking Row for Base Table');
1339 END IF;
1340
1341 --------------------------------------------
1342 -- Call the LOCK_ROW for each _B child record
1343 --------------------------------------------
1344 x_return_status := Lock_Row(
1345 p_cat_id => p_cat_id,
1346 p_variable_code => p_variable_code,
1347 p_object_version_number => p_object_version_number
1348 );
1349 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1351 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354
1355
1356
1357 /*IF (l_debug = 'Y') THEN
1358 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1359 END IF;*/
1360
1361 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1362 FND_LOG.STRING(G_PROC_LEVEL,
1363 G_PKG_NAME, '6000: Leaving Lock_Row' );
1364 END IF;
1365
1366 EXCEPTION
1367 WHEN FND_API.G_EXC_ERROR THEN
1368 /*IF (l_debug = 'Y') THEN
1369 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1370 END IF;*/
1371
1372 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1373 FND_LOG.STRING(G_EXCP_LEVEL,
1374 G_PKG_NAME, '6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception' );
1375 END IF;
1376 x_return_status := G_RET_STS_ERROR;
1377
1378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1379 /*IF (l_debug = 'Y') THEN
1380 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1381 END IF;*/
1382
1383 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1384 FND_LOG.STRING(G_EXCP_LEVEL,
1385 G_PKG_NAME, '6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1386 END IF;
1387 x_return_status := G_RET_STS_UNEXP_ERROR;
1388
1389 WHEN OTHERS THEN
1390 /*IF (l_debug = 'Y') THEN
1391 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1392 END IF;*/
1393
1394 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1395 FND_LOG.STRING(G_EXCP_LEVEL,
1396 G_PKG_NAME, '6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
1397 END IF;
1398 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1399 p_msg_name => G_UNEXPECTED_ERROR,
1400 p_token1 => G_SQLCODE_TOKEN,
1401 p_token1_value => sqlcode,
1402 p_token2 => G_SQLERRM_TOKEN,
1403 p_token2_value => sqlerrm);
1404 x_return_status := G_RET_STS_UNEXP_ERROR;
1405
1406 END Lock_Row;
1407 ---------------------------------------------------------------------------
1408 -- PROCEDURE Update_Row
1409 ---------------------------------------------------------------------------
1410 -------------------------------------
1411 -- Update_Row for:OKC_K_ART_VARIABLES --
1412 -------------------------------------
1413 FUNCTION Update_Row(
1414 p_cat_id IN NUMBER,
1415 p_variable_code IN VARCHAR2,
1416 p_variable_type IN VARCHAR2,
1417 p_external_yn IN VARCHAR2,
1418 p_variable_value_id IN VARCHAR2,
1419 p_variable_value IN VARCHAR2,
1420 p_attribute_value_set_id IN NUMBER,
1421 p_override_global_yn IN VARCHAR2,
1422 p_object_version_number IN NUMBER,
1423 p_created_by IN NUMBER,
1424 p_creation_date IN DATE,
1425 p_last_updated_by IN NUMBER,
1426 p_last_update_login IN NUMBER,
1427 p_last_update_date IN DATE
1428 ) RETURN VARCHAR2 IS
1429
1430 BEGIN
1431
1432 /*IF (l_debug = 'Y') THEN
1433 Okc_Debug.Log('6400: Entered Update_Row', 2);
1434 END IF;*/
1435
1436 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1437 FND_LOG.STRING(G_PROC_LEVEL,
1438 G_PKG_NAME, '6400: Entered Update_Row' );
1439 END IF;
1440
1441 UPDATE OKC_K_ART_VARIABLES
1442 SET VARIABLE_TYPE = p_variable_type,
1443 EXTERNAL_YN = p_external_yn,
1444 VARIABLE_VALUE_ID = p_variable_value_id,
1445 VARIABLE_VALUE = p_variable_value,
1446 ATTRIBUTE_VALUE_SET_ID = p_attribute_value_set_id,
1447 OVERRIDE_GLOBAL_YN = p_override_global_yn,
1448 OBJECT_VERSION_NUMBER = p_object_version_number,
1449 LAST_UPDATED_BY = p_last_updated_by,
1450 LAST_UPDATE_LOGIN = p_last_update_login,
1451 LAST_UPDATE_DATE = p_last_update_date
1452 WHERE CAT_ID = p_cat_id
1453 AND VARIABLE_CODE = p_variable_code;
1454
1455 /*IF (l_debug = 'Y') THEN
1456 Okc_Debug.Log('6500: Leaving Update_Row', 2);
1457 END IF;*/
1458
1459 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1460 FND_LOG.STRING(G_PROC_LEVEL,
1461 G_PKG_NAME, '6500: Leaving Update_Row' );
1462 END IF;
1463
1464 RETURN G_RET_STS_SUCCESS ;
1465
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468
1469 /*IF (l_debug = 'Y') THEN
1470 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1471 END IF;*/
1472
1473 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1474 FND_LOG.STRING(G_EXCP_LEVEL,
1475 G_PKG_NAME, '6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
1476 END IF;
1477
1478 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1479 p_msg_name => G_UNEXPECTED_ERROR,
1480 p_token1 => G_SQLCODE_TOKEN,
1481 p_token1_value => sqlcode,
1482 p_token2 => G_SQLERRM_TOKEN,
1483 p_token2_value => sqlerrm);
1484
1485 RETURN G_RET_STS_UNEXP_ERROR ;
1486
1487 END Update_Row;
1488
1489 -------------------------------------
1490 -- Update_Row for:OKC_K_ART_VARIABLES --
1491 -------------------------------------
1492 PROCEDURE Update_Row(
1493 p_validation_level IN NUMBER,
1494 x_return_status OUT NOCOPY VARCHAR2,
1495 p_cat_id IN NUMBER,
1496 p_variable_code IN VARCHAR2,
1497 p_variable_type IN VARCHAR2,
1498 p_external_yn IN VARCHAR2,
1499 p_variable_value_id IN VARCHAR2,
1500 p_variable_value IN VARCHAR2,
1501
1502 p_attribute_value_set_id IN NUMBER := NULL,
1503 p_override_global_yn IN VARCHAR2 := NULL,
1504
1505 p_object_version_number IN NUMBER
1506
1507 ) IS
1508
1509 l_variable_type OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE;
1510 l_external_yn OKC_K_ART_VARIABLES.EXTERNAL_YN%TYPE;
1511 l_variable_value_id OKC_K_ART_VARIABLES.VARIABLE_VALUE_ID%TYPE;
1512 l_variable_value OKC_K_ART_VARIABLES.VARIABLE_VALUE%TYPE;
1513 l_attribute_value_set_id OKC_K_ART_VARIABLES.ATTRIBUTE_VALUE_SET_ID%TYPE;
1514 l_override_global_yn OKC_K_ART_VARIABLES.OVERRIDE_GLOBAL_YN%TYPE;
1515 l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
1516 l_created_by OKC_K_ART_VARIABLES.CREATED_BY%TYPE;
1517 l_creation_date OKC_K_ART_VARIABLES.CREATION_DATE%TYPE;
1518 l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
1519 l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
1520 l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
1521
1522 BEGIN
1523
1524 /*IF (l_debug = 'Y') THEN
1525 Okc_Debug.Log('7000: Entered Update_Row', 2);
1526 Okc_Debug.Log('7100: Locking _B row', 2);
1527 END IF;*/
1528
1529 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1530 FND_LOG.STRING(G_PROC_LEVEL,
1531 G_PKG_NAME, '7000: Entered Update_Row');
1532 FND_LOG.STRING(G_PROC_LEVEL,
1533 G_PKG_NAME, '7100: Locking _B row');
1534 END IF;
1535
1536 x_return_status := Lock_row(
1537 p_cat_id => p_cat_id,
1538 p_variable_code => p_variable_code,
1539 p_object_version_number => p_object_version_number
1540 );
1541 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1543 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546
1547 /*IF (l_debug = 'Y') THEN
1548 Okc_Debug.Log('7300: Setting attributes', 2);
1549 END IF;*/
1550
1551 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1552 FND_LOG.STRING(G_PROC_LEVEL,
1553 G_PKG_NAME, '7300: Setting attributes' );
1554 END IF;
1555
1556 x_return_status := Set_Attributes(
1557 p_cat_id => p_cat_id,
1558 p_variable_code => p_variable_code,
1559 p_variable_type => p_variable_type,
1560 p_external_yn => p_external_yn,
1561 p_variable_value_id => p_variable_value_id,
1562 p_variable_value => p_variable_value,
1563 p_attribute_value_set_id => p_attribute_value_set_id,
1564 p_override_global_yn => p_override_global_yn,
1565 p_object_version_number => p_object_version_number,
1566 x_variable_type => l_variable_type,
1567 x_object_version_number => l_object_version_number,
1568 x_external_yn => l_external_yn,
1569 x_variable_value_id => l_variable_value_id,
1570 x_variable_value => l_variable_value,
1571 x_attribute_value_set_id => l_attribute_value_set_id,
1572 x_override_global_yn => l_override_global_yn
1573 );
1574 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1576 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1577 RAISE FND_API.G_EXC_ERROR;
1578 END IF;
1579
1580 /*IF (l_debug = 'Y') THEN
1581 Okc_Debug.Log('7400: Record Validation', 2);
1582 END IF;*/
1583
1584 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1585 FND_LOG.STRING(G_PROC_LEVEL,
1586 G_PKG_NAME, '7400: Record Validation' );
1587 END IF;
1588
1589 --- Validate all non-missing attributes
1590 x_return_status := Validate_Record(
1591 p_validation_level => p_validation_level,
1592 p_cat_id => p_cat_id,
1593 p_variable_code => p_variable_code,
1594 p_variable_type => l_variable_type,
1595 p_external_yn => l_external_yn,
1596 p_variable_value_id => l_variable_value_id,
1597 p_variable_value => l_variable_value,
1598 p_attribute_value_set_id => l_attribute_value_set_id,
1599 p_override_global_yn => l_override_global_yn
1600 );
1601 --- If any errors happen abort API
1602 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1604 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1605 RAISE FND_API.G_EXC_ERROR;
1606 END IF;
1607
1608 /*IF (l_debug = 'Y') THEN
1609 Okc_Debug.Log('7500: Filling WHO columns', 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, '7500: Filling WHO columns' );
1615 END IF;
1616
1617 -- Filling who columns
1618 l_last_update_date := SYSDATE;
1619 l_last_updated_by := FND_GLOBAL.USER_ID;
1620 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1621
1622 -- Object version increment
1623 IF Nvl(l_object_version_number, 0) >= 0 THEN
1624 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
1625 END IF;
1626
1627 --------------------------------------------
1628 -- Call the Update_Row for each child record
1629 --------------------------------------------
1630 /*IF (l_debug = 'Y') THEN
1631 Okc_Debug.Log('7600: Updating Row', 2);
1632 END IF;*/
1633
1634 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1635 FND_LOG.STRING(G_PROC_LEVEL,
1636 G_PKG_NAME, '7600: Updating Row' );
1637 END IF;
1638
1639 x_return_status := Update_Row(
1640 p_cat_id => p_cat_id,
1641 p_variable_code => p_variable_code,
1642 p_variable_type => l_variable_type,
1643 p_external_yn => l_external_yn,
1644 p_variable_value_id => l_variable_value_id,
1645 p_variable_value => l_variable_value,
1646 p_attribute_value_set_id => l_attribute_value_set_id,
1647 p_override_global_yn => l_override_global_yn,
1648 p_object_version_number => l_object_version_number,
1649 p_created_by => l_created_by,
1650 p_creation_date => l_creation_date,
1651 p_last_updated_by => l_last_updated_by,
1652 p_last_update_login => l_last_update_login,
1653 p_last_update_date => l_last_update_date
1654 );
1655 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1657 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660
1661
1662 /*IF (l_debug = 'Y') THEN
1663 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1664 END IF;*/
1665
1666 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1667 FND_LOG.STRING(G_PROC_LEVEL,
1668 G_PKG_NAME, '7800: Leaving Update_Row' );
1669 END IF;
1670
1671 EXCEPTION
1672 WHEN FND_API.G_EXC_ERROR THEN
1673 /*IF (l_debug = 'Y') THEN
1674 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1675 END IF;*/
1676
1677 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1678 FND_LOG.STRING(G_EXCP_LEVEL,
1679 G_PKG_NAME, '7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception' );
1680 END IF;
1681
1682 x_return_status := G_RET_STS_ERROR;
1683
1684 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1685 /*IF (l_debug = 'Y') THEN
1686 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1687 END IF;*/
1688
1689 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1690 FND_LOG.STRING(G_EXCP_LEVEL,
1691 G_PKG_NAME, '8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1692 END IF;
1693
1694 x_return_status := G_RET_STS_UNEXP_ERROR;
1695
1696 WHEN OTHERS THEN
1697 /*IF (l_debug = 'Y') THEN
1698 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1699 END IF;*/
1700
1701 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1702 FND_LOG.STRING(G_EXCP_LEVEL,
1703 G_PKG_NAME, '8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
1704 END IF;
1705
1706 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1707 p_msg_name => G_UNEXPECTED_ERROR,
1708 p_token1 => G_SQLCODE_TOKEN,
1709 p_token1_value => sqlcode,
1710 p_token2 => G_SQLERRM_TOKEN,
1711 p_token2_value => sqlerrm);
1712 x_return_status := G_RET_STS_UNEXP_ERROR;
1713
1714 END Update_Row;
1715
1716 ---------------------------------------------------------------------------
1717 -- PROCEDURE Delete_Row
1718 ---------------------------------------------------------------------------
1719 -------------------------------------
1720 -- Delete_Row for:OKC_K_ART_VARIABLES --
1721 -------------------------------------
1722 FUNCTION Delete_Row(
1723 p_cat_id IN NUMBER,
1724 p_variable_code IN VARCHAR2
1725 ) RETURN VARCHAR2 IS
1726
1727 BEGIN
1728
1729 /*IF (l_debug = 'Y') THEN
1730 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1731 END IF;*/
1732
1733 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1734 FND_LOG.STRING(G_PROC_LEVEL,
1735 G_PKG_NAME, '8200: Entered Delete_Row' );
1736 END IF;
1737
1738 IF isArtVariableMRV(p_cat_id =>p_CAT_ID, p_VARIABLE_CODE => p_VARIABLE_CODE) = 'Y' THEN
1739 delete_mrv_uda_data(p_cat_id =>p_CAT_ID, p_VARIABLE_CODE => p_VARIABLE_CODE, p_major_version => NULL);
1740 END IF;
1741
1742 DELETE FROM OKC_K_ART_VARIABLES WHERE CAT_ID = p_CAT_ID AND VARIABLE_CODE = p_VARIABLE_CODE;
1743
1744 /*IF (l_debug = 'Y') THEN
1745 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1746 END IF;*/
1747
1748 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1749 FND_LOG.STRING(G_PROC_LEVEL,
1750 G_PKG_NAME, '8300: Leaving Delete_Row' );
1751 END IF;
1752
1753 RETURN( G_RET_STS_SUCCESS );
1754
1755 EXCEPTION
1756 WHEN OTHERS THEN
1757
1758 /*IF (l_debug = 'Y') THEN
1759 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1760 END IF;*/
1761
1762 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1763 FND_LOG.STRING(G_EXCP_LEVEL,
1764 G_PKG_NAME, '8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
1765 END IF;
1766
1767 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1768 p_msg_name => G_UNEXPECTED_ERROR,
1769 p_token1 => G_SQLCODE_TOKEN,
1770 p_token1_value => sqlcode,
1771 p_token2 => G_SQLERRM_TOKEN,
1772 p_token2_value => sqlerrm);
1773
1774 RETURN( G_RET_STS_UNEXP_ERROR );
1775
1776 END Delete_Row;
1777
1778 -------------------------------------
1779 -- Delete_Row for:OKC_K_ART_VARIABLES --
1780 -------------------------------------
1781 PROCEDURE Delete_Row(
1782 x_return_status OUT NOCOPY VARCHAR2,
1783 p_cat_id IN NUMBER,
1784 p_variable_code IN VARCHAR2,
1785 p_object_version_number IN NUMBER
1786 ) IS
1787 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1788 BEGIN
1789
1790 /*IF (l_debug = 'Y') THEN
1791 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1792 Okc_Debug.Log('8900: Locking _B row', 2);
1793 END IF;*/
1794
1795 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1796 FND_LOG.STRING(G_PROC_LEVEL,
1797 G_PKG_NAME, '8800: Entered Delete_Row');
1798 FND_LOG.STRING(G_PROC_LEVEL,
1799 G_PKG_NAME, '8900: Locking _B row');
1800 END IF;
1801
1802 x_return_status := Lock_row(
1803 p_cat_id => p_cat_id,
1804 p_variable_code => p_variable_code,
1805 p_object_version_number => p_object_version_number
1806 );
1807 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1810 RAISE FND_API.G_EXC_ERROR;
1811 END IF;
1812
1813 /*IF (l_debug = 'Y') THEN
1814 Okc_Debug.Log('9100: Removing _B row', 2);
1815 END IF;*/
1816
1817 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1818 FND_LOG.STRING(G_PROC_LEVEL,
1819 G_PKG_NAME, '9100: Removing _B row' );
1820 END IF;
1821 x_return_status := Delete_Row( p_cat_id => p_cat_id,p_variable_code => p_variable_code );
1822 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1824 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1825 RAISE FND_API.G_EXC_ERROR;
1826 END IF;
1827
1828
1829 /*IF (l_debug = 'Y') THEN
1830 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1831 END IF;*/
1832
1833 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1834 FND_LOG.STRING(G_PROC_LEVEL,
1835 G_PKG_NAME, '9300: Leaving Delete_Row' );
1836 END IF;
1837
1838 EXCEPTION
1839 WHEN FND_API.G_EXC_ERROR THEN
1840 /*IF (l_debug = 'Y') THEN
1841 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1842 END IF;*/
1843
1844 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1845 FND_LOG.STRING(G_EXCP_LEVEL,
1846 G_PKG_NAME, '9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception' );
1847 END IF;
1848 x_return_status := G_RET_STS_ERROR;
1849
1850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1851 /*IF (l_debug = 'Y') THEN
1852 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1853 END IF;*/
1854
1855 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1856 FND_LOG.STRING(G_EXCP_LEVEL,
1857 G_PKG_NAME, '9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1858 END IF;
1859 x_return_status := G_RET_STS_UNEXP_ERROR;
1860
1861 WHEN OTHERS THEN
1862 /*IF (l_debug = 'Y') THEN
1863 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1864 END IF;*/
1865
1866 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1867 FND_LOG.STRING(G_EXCP_LEVEL,
1868 G_PKG_NAME, '9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
1869 END IF;
1870 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1871 p_msg_name => G_UNEXPECTED_ERROR,
1872 p_token1 => G_SQLCODE_TOKEN,
1873 p_token1_value => sqlcode,
1874 p_token2 => G_SQLERRM_TOKEN,
1875 p_token2_value => sqlerrm);
1876 x_return_status := G_RET_STS_UNEXP_ERROR;
1877
1878 END Delete_Row;
1879
1880 PROCEDURE delete_set(
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 p_cat_id IN NUMBER
1883 )
1884 IS
1885 CURSOR lock_csr IS
1886 SELECT rowid
1887 FROM OKC_K_ART_VARIABLES
1888 WHERE cat_id = p_cat_id
1889 FOR UPDATE NOWAIT;
1890
1891 BEGIN
1892 /*IF (l_debug = 'Y') THEN
1893 Okc_Debug.Log('9700: Entered Delete_Set', 2);
1894 Okc_Debug.Log('9701: Locking Records', 2);
1895 END IF;*/
1896
1897 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1898 FND_LOG.STRING(G_PROC_LEVEL,
1899 G_PKG_NAME, '9700: Entered Delete_Set');
1900 FND_LOG.STRING(G_PROC_LEVEL,
1901 G_PKG_NAME, '9701: Locking Records');
1902 END IF;
1903
1904 -- making OPEN/CLOSE cursor to lock records
1905 OPEN lock_csr;
1906 CLOSE lock_csr;
1907
1908 IF isArtVariableMRV(p_cat_id =>p_CAT_ID, p_VARIABLE_CODE => null) = 'Y' THEN
1909 delete_mrv_uda_data(p_cat_id =>p_CAT_ID, p_VARIABLE_CODE => null, p_major_version => NULL);
1910 END IF;
1911
1912 DELETE FROM OKC_K_ART_VARIABLES
1913 WHERE cat_id = p_cat_id;
1914
1915
1916 /*IF (l_debug = 'Y') THEN
1917 Okc_Debug.Log('11000: Leaving Delete_set', 2);
1918 END IF;*/
1919
1920 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1921 FND_LOG.STRING(G_PROC_LEVEL,
1922 G_PKG_NAME, '11000: Leaving Delete_set' );
1923 END IF;
1924
1925 EXCEPTION
1926 WHEN E_Resource_Busy THEN
1927 /*IF (l_debug = 'Y') THEN
1928 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
1929 END IF;*/
1930
1931 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1932 FND_LOG.STRING(G_EXCP_LEVEL,
1933 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
1934 END IF;
1935
1936 IF (lock_csr%ISOPEN) THEN
1937 CLOSE lock_csr;
1938 END IF;
1939 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
1940 x_return_status := G_RET_STS_ERROR ;
1941
1942 WHEN FND_API.G_EXC_ERROR THEN
1943 /*IF (l_debug = 'Y') THEN
1944 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
1945 END IF;*/
1946
1947 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1948 FND_LOG.STRING(G_EXCP_LEVEL,
1949 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
1950 END IF;
1951
1952 IF (lock_csr%ISOPEN) THEN
1953 CLOSE lock_csr;
1954 END IF;
1955
1956 x_return_status := G_RET_STS_ERROR;
1957
1958 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1959 /*IF (l_debug = 'Y') THEN
1960 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1961 END IF;*/
1962
1963 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1964 FND_LOG.STRING(G_EXCP_LEVEL,
1965 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1966 END IF;
1967
1968 IF (lock_csr%ISOPEN) THEN
1969 CLOSE lock_csr;
1970 END IF;
1971 x_return_status := G_RET_STS_UNEXP_ERROR;
1972
1973 WHEN OTHERS THEN
1974 /*IF (l_debug = 'Y') THEN
1975 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
1976 END IF;*/
1977
1978 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1979 FND_LOG.STRING(G_EXCP_LEVEL,
1980 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
1981 END IF;
1982
1983 IF (lock_csr%ISOPEN) THEN
1984 CLOSE lock_csr;
1985 END IF;
1986 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1987 p_msg_name => G_UNEXPECTED_ERROR,
1988 p_token1 => G_SQLCODE_TOKEN,
1989 p_token1_value => sqlcode,
1990 p_token2 => G_SQLERRM_TOKEN,
1991 p_token2_value => sqlerrm);
1992 x_return_status := G_RET_STS_UNEXP_ERROR;
1993
1994 END Delete_Set;
1995
1996 PROCEDURE delete_set(
1997 x_return_status OUT NOCOPY VARCHAR2,
1998 p_scn_id IN NUMBER
1999 )
2000 IS
2001 CURSOR lock_csr IS
2002 SELECT rowid
2003 FROM OKC_K_ART_VARIABLES
2004 WHERE CAT_ID IN (SELECT ID FROM OKC_K_ARTICLES_B
2005 WHERE SCN_ID=p_scn_id)
2006 FOR UPDATE NOWAIT;
2007
2008 CURSOR cat_mrv_csr
2009 IS
2010 SELECT kart.ID
2011 FROM OKC_K_ARTICLES_B KART
2012 , OKC_BUS_VARIABLES_B BUS_VAR
2013 , OKC_K_ART_VARIABLES KVAR
2014 WHERE kart.SCN_ID=p_scn_id
2015 AND KVAR.cat_id=kart.id
2016 AND KVAR.variable_code=BUS_VAR.variable_code
2017 AND BUS_VAR.MRV_FLAG='Y';
2018
2019 BEGIN
2020 /*IF (l_debug = 'Y') THEN
2021 Okc_Debug.Log('9700: Entered Delete_Set', 2);
2022 Okc_Debug.Log('9700: Locking Records', 2);
2023 END IF;*/
2024
2025 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2026 FND_LOG.STRING(G_PROC_LEVEL,
2027 G_PKG_NAME, '9700: Entered Delete_Set');
2028 FND_LOG.STRING(G_PROC_LEVEL,
2029 G_PKG_NAME, '9700: Locking Records');
2030 END IF;
2031
2032 -- making OPEN/CLOSE cursor to lock records
2033 OPEN lock_csr;
2034 CLOSE lock_csr;
2035
2036 FOR cat_rec IN cat_mrv_csr
2037 LOOP
2038 delete_mrv_uda_data(p_cat_id => cat_rec.ID, p_VARIABLE_CODE => null, p_major_version => NULL);
2039 END LOOP;
2040
2041
2042 DELETE FROM OKC_K_ART_VARIABLES
2043 WHERE CAT_ID IN (SELECT ID FROM OKC_K_ARTICLES_B
2044 WHERE SCN_ID=p_scn_id);
2045
2046 /*IF (l_debug = 'Y') THEN
2047 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2048 END IF;*/
2049
2050 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2051 FND_LOG.STRING(G_PROC_LEVEL,
2052 G_PKG_NAME, '11000: Leaving Delete_set' );
2053 END IF;
2054
2055 EXCEPTION
2056 WHEN E_Resource_Busy THEN
2057 /*IF (l_debug = 'Y') THEN
2058 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2059 END IF;*/
2060
2061 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2062 FND_LOG.STRING(G_EXCP_LEVEL,
2063 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
2064 END IF;
2065
2066 IF (lock_csr%ISOPEN) THEN
2067 CLOSE lock_csr;
2068 END IF;
2069 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2070 x_return_status := G_RET_STS_ERROR ;
2071
2072 WHEN FND_API.G_EXC_ERROR THEN
2073 /*IF (l_debug = 'Y') THEN
2074 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
2075 END IF;*/
2076
2077 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2078 FND_LOG.STRING(G_EXCP_LEVEL,
2079 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
2080 END IF;
2081
2082 IF (lock_csr%ISOPEN) THEN
2083 CLOSE lock_csr;
2084 END IF;
2085 x_return_status := G_RET_STS_ERROR;
2086
2087 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2088 /*IF (l_debug = 'Y') THEN
2089 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2090 END IF;*/
2091
2092 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2093 FND_LOG.STRING(G_EXCP_LEVEL,
2094 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2095 END IF;
2096
2097 IF (lock_csr%ISOPEN) THEN
2098 CLOSE lock_csr;
2099 END IF;
2100 x_return_status := G_RET_STS_UNEXP_ERROR;
2101
2102 WHEN OTHERS THEN
2103 /*IF (l_debug = 'Y') THEN
2104 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
2105 END IF;*/
2106
2107 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2108 FND_LOG.STRING(G_EXCP_LEVEL,
2109 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
2110 END IF;
2111
2112 IF (lock_csr%ISOPEN) THEN
2113 CLOSE lock_csr;
2114 END IF;
2115 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2116 p_msg_name => G_UNEXPECTED_ERROR,
2117 p_token1 => G_SQLCODE_TOKEN,
2118 p_token1_value => sqlcode,
2119 p_token2 => G_SQLERRM_TOKEN,
2120 p_token2_value => sqlerrm);
2121 x_return_status := G_RET_STS_UNEXP_ERROR;
2122
2123 END Delete_Set;
2124
2125
2126 PROCEDURE delete_set(
2127 x_return_status OUT NOCOPY VARCHAR2,
2128 p_doc_type IN VARCHAR2,
2129 p_doc_id IN NUMBER
2130 ,p_retain_lock_terms_yn IN VARCHAR2 := 'N'
2131 )
2132 IS
2133
2134 CURSOR lock_csr IS
2135 SELECT rowid
2136 FROM OKC_K_ART_VARIABLES
2137 WHERE cat_id IN (SELECT id FROM OKC_K_ARTICLES_B WHERE
2138 document_type= p_doc_type AND
2139 document_id = p_doc_id
2140 AND
2141 (( p_retain_lock_terms_yn = 'N')
2142 OR
2143 (p_retain_lock_terms_yn ='Y' AND amendment_operation_code IS NULL)
2144 )
2145 )
2146 FOR UPDATE NOWAIT;
2147
2148 CURSOR doc_mrv_csr
2149 IS
2150 SELECT kart.ID
2151 FROM OKC_K_ARTICLES_B KART
2152 , OKC_BUS_VARIABLES_B BUS_VAR
2153 , OKC_K_ART_VARIABLES KVAR
2154 WHERE kart.document_type=p_doc_type
2155 AND kart.document_id = p_doc_id
2156 AND KVAR.cat_id=kart.id
2157 AND KVAR.variable_code=BUS_VAR.variable_code
2158 AND BUS_VAR.MRV_FLAG='Y'
2159 AND
2160 (( p_retain_lock_terms_yn = 'N')
2161 OR
2162 (p_retain_lock_terms_yn ='Y' AND KART.amendment_operation_code IS NULL)
2163 );
2164
2165
2166 BEGIN
2167 /*IF (l_debug = 'Y') THEN
2168 Okc_Debug.Log('9700: Entered Delete_Set', 2);
2169 Okc_Debug.Log('9710: Locking Records', 2);
2170 END IF;*/
2171
2172 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2173 FND_LOG.STRING(G_PROC_LEVEL,
2174 G_PKG_NAME, '9700: Entered Delete_Set');
2175 FND_LOG.STRING(G_PROC_LEVEL,
2176 G_PKG_NAME, '9710: Locking Records');
2177 END IF;
2178
2179 -- making OPEN/CLOSE cursor to lock records
2180 OPEN lock_csr;
2181 CLOSE lock_csr;
2182
2183 FOR cat_rec IN doc_mrv_csr
2184 LOOP
2185 delete_mrv_uda_data(p_cat_id => cat_rec.ID, p_VARIABLE_CODE => null, p_major_version => NULL);
2186 END LOOP;
2187
2188
2189 DELETE FROM OKC_K_ART_VARIABLES
2190 WHERE cat_id IN (SELECT id FROM OKC_K_ARTICLES_B WHERE
2191 document_type=p_doc_type AND
2192 document_id = p_doc_id);
2193
2194
2195
2196
2197 /*IF (l_debug = 'Y') THEN
2198 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2199 END IF;*/
2200
2201 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2202 FND_LOG.STRING(G_PROC_LEVEL,
2203 G_PKG_NAME, '11000: Leaving Delete_set' );
2204 END IF;
2205
2206 EXCEPTION
2207 WHEN E_Resource_Busy THEN
2208 /*IF (l_debug = 'Y') THEN
2209 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2210 END IF;*/
2211
2212 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2213 FND_LOG.STRING(G_EXCP_LEVEL,
2214 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
2215 END IF;
2216
2217 IF (lock_csr%ISOPEN) THEN
2218 CLOSE lock_csr;
2219 END IF;
2220 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2221
2222 x_return_status := G_RET_STS_ERROR ;
2223 WHEN FND_API.G_EXC_ERROR THEN
2224 /*IF (l_debug = 'Y') THEN
2225 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
2226 END IF;*/
2227
2228 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2229 FND_LOG.STRING(G_EXCP_LEVEL,
2230 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
2231 END IF;
2232
2233 IF (lock_csr%ISOPEN) THEN
2234 CLOSE lock_csr;
2235 END IF;
2236 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2237 x_return_status := G_RET_STS_ERROR;
2238
2239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2240 /*IF (l_debug = 'Y') THEN
2241 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2242 END IF;*/
2243
2244 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2245 FND_LOG.STRING(G_EXCP_LEVEL,
2246 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2247 END IF;
2248
2249 IF (lock_csr%ISOPEN) THEN
2250 CLOSE lock_csr;
2251 END IF;
2252 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2253 x_return_status := G_RET_STS_UNEXP_ERROR;
2254
2255 WHEN OTHERS THEN
2256 /*IF (l_debug = 'Y') THEN
2257 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
2258 END IF;*/
2259
2260 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2261 FND_LOG.STRING(G_EXCP_LEVEL,
2262 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
2263 END IF;
2264
2265 IF (lock_csr%ISOPEN) THEN
2266 CLOSE lock_csr;
2267 END IF;
2268 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2269 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2270 p_msg_name => G_UNEXPECTED_ERROR,
2271 p_token1 => G_SQLCODE_TOKEN,
2272 p_token1_value => sqlcode,
2273 p_token2 => G_SQLERRM_TOKEN,
2274 p_token2_value => sqlerrm);
2275 x_return_status := G_RET_STS_UNEXP_ERROR;
2276
2277 END Delete_Set;
2278
2279 --This function is to be called from versioning API OKC_VERSION_PVT
2280 -- Location: Base Table API
2281 FUNCTION Create_Version(
2282 p_doc_type IN VARCHAR2,
2283 p_doc_id IN NUMBER,
2284 p_major_version IN NUMBER
2285 ) RETURN VARCHAR2 IS
2286
2287 CURSOR doc_mrv_csr
2288 IS
2289 SELECT kart.ID
2290 FROM OKC_K_ARTICLES_B KART
2291 , OKC_BUS_VARIABLES_B BUS_VAR
2292 , OKC_K_ART_VARIABLES KVAR
2293 WHERE kart.document_type=p_doc_type
2294 AND kart.document_id = p_doc_id
2295 AND KVAR.cat_id=kart.id
2296 AND KVAR.variable_code=BUS_VAR.variable_code
2297 AND BUS_VAR.MRV_FLAG='Y';
2298
2299 BEGIN
2300
2301 /*IF (l_debug = 'Y') THEN
2302 Okc_Debug.Log('9700: Entered create_version', 2);
2303 Okc_Debug.Log('9800: Saving Base Table', 2);
2304 END IF;*/
2305
2306 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2307 FND_LOG.STRING(G_PROC_LEVEL,
2308 G_PKG_NAME, '9700: Entered create_version');
2309 FND_LOG.STRING(G_PROC_LEVEL,
2310 G_PKG_NAME, '9800: Saving Base Table');
2311 END IF;
2312
2313 -----------------------------------------
2314 -- Saving Base Table
2315 -----------------------------------------
2316 INSERT INTO OKC_K_ART_VARIABLES_H (
2317 major_version,
2318 CAT_ID,
2319 VARIABLE_CODE,
2320 VARIABLE_TYPE,
2321 EXTERNAL_YN,
2322 VARIABLE_VALUE_ID,
2323 VARIABLE_VALUE,
2324 ATTRIBUTE_VALUE_SET_ID,
2325 override_global_yn,
2326 OBJECT_VERSION_NUMBER,
2327 CREATED_BY,
2328 CREATION_DATE,
2329 LAST_UPDATED_BY,
2330 LAST_UPDATE_LOGIN,
2331 LAST_UPDATE_DATE,
2332 mr_variable_html,
2333 mr_variable_xml)
2334 SELECT
2335 p_major_version,
2336 CAT_ID,
2337 VARIABLE_CODE,
2338 VARIABLE_TYPE,
2339 EXTERNAL_YN,
2340 VARIABLE_VALUE_ID,
2341 VARIABLE_VALUE,
2342 ATTRIBUTE_VALUE_SET_ID,
2343 OVERRIDE_GLOBAL_YN,
2344 OBJECT_VERSION_NUMBER,
2345 CREATED_BY,
2346 CREATION_DATE,
2347 LAST_UPDATED_BY,
2348 LAST_UPDATE_LOGIN,
2349 LAST_UPDATE_DATE,
2350 mr_variable_html,
2351 mr_variable_xml
2352 FROM OKC_K_ART_VARIABLES
2353 WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_B
2354 WHERE DOCUMENT_TYPE = P_DOC_TYPE
2355 AND DOCUMENT_ID = P_DOC_ID);
2356 /*IF (l_debug = 'Y') THEN
2357 Okc_Debug.Log('10000: Leaving create_version', 2);
2358 END IF;*/
2359
2360 FOR cat_rec IN doc_mrv_csr LOOP
2361 create_mrv_uda_data_version(p_cat_id=>cat_rec.id, p_major_version => p_major_version);
2362 END LOOP;
2363
2364
2365 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2366 FND_LOG.STRING(G_PROC_LEVEL,
2367 G_PKG_NAME, '10000: Leaving create_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('10100: Leaving create_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, '10100: Leaving create_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 create_version;
2394
2395 --This Function is called from Versioning API OKC_VERSION_PVT
2396 -- Location:Base Table API
2397 --?? remove the function if the aPI doesn't need it
2398
2399 FUNCTION Restore_Version(
2400 p_doc_type IN VARCHAR2,
2401 p_doc_id IN NUMBER,
2402 p_major_version IN NUMBER
2403 ) RETURN VARCHAR2
2404 IS
2405
2406 CURSOR doc_ver_mrv_csr
2407 IS
2408 SELECT kart.ID
2409 FROM OKC_K_ARTICLES_BH KART
2410 , OKC_BUS_VARIABLES_B BUS_VAR
2411 , OKC_K_ART_VARIABLES_H KVAR
2412 WHERE kart.document_type=p_doc_type
2413 AND kart.document_id = p_doc_id
2414 AND KVAR.cat_id=kart.id
2415 AND KVAR.variable_code=BUS_VAR.variable_code
2416 AND BUS_VAR.MRV_FLAG='Y'
2417 AND KART.major_version=p_major_version
2418 AND KVAR.major_version=p_major_version;
2419
2420 BEGIN
2421
2422 /*IF (l_debug = 'Y') THEN
2423 Okc_Debug.Log('10200: Entered restore_version', 2);
2424 Okc_Debug.Log('10300: Restoring Base Table', 2);
2425 END IF;*/
2426
2427 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2428 FND_LOG.STRING(G_PROC_LEVEL,
2429 G_PKG_NAME, '10200: Entered restore_version');
2430 FND_LOG.STRING(G_PROC_LEVEL,
2431 G_PKG_NAME, '10300: Restoring Base Table');
2432 END IF;
2433
2434 -----------------------------------------
2435 -- Restoring Base Table
2436 -----------------------------------------
2437 INSERT INTO OKC_K_ART_VARIABLES (
2438 CAT_ID,
2439 VARIABLE_CODE,
2440 VARIABLE_TYPE,
2441 EXTERNAL_YN,
2442 VARIABLE_VALUE_ID,
2443 VARIABLE_VALUE,
2444 ATTRIBUTE_VALUE_SET_ID,
2445 OVERRIDE_GLOBAL_YN,
2446 OBJECT_VERSION_NUMBER,
2447 CREATED_BY,
2448 CREATION_DATE,
2449 LAST_UPDATED_BY,
2450 LAST_UPDATE_LOGIN,
2451 LAST_UPDATE_DATE,
2452 mr_variable_html,
2453 mr_variable_xml)
2454 SELECT
2455 CAT_ID,
2456 VARIABLE_CODE,
2457 VARIABLE_TYPE,
2458 EXTERNAL_YN,
2459 VARIABLE_VALUE_ID,
2460 VARIABLE_VALUE,
2461 ATTRIBUTE_VALUE_SET_ID,
2462 override_global_YN,
2463 OBJECT_VERSION_NUMBER,
2464 CREATED_BY,
2465 CREATION_DATE,
2466 LAST_UPDATED_BY,
2467 LAST_UPDATE_LOGIN,
2468 LAST_UPDATE_DATE,
2469 mr_variable_html,
2470 mr_variable_xml
2471 FROM OKC_K_ART_VARIABLES_H
2472 WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_BH
2473 WHERE DOCUMENT_TYPE = P_DOC_TYPE
2474 AND DOCUMENT_ID = P_DOC_ID)
2475 AND major_version = p_major_version;
2476
2477 /*IF (l_debug = 'Y') THEN
2478 Okc_Debug.Log('10500: Leaving restore_version', 2);
2479 END IF;*/
2480 FOR cat_rec IN doc_ver_mrv_csr LOOP
2481 restore_mrv_uda_data_version(p_cat_id => cat_rec.id,p_major_version => p_major_version);
2482 END LOOP;
2483
2484 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2485 FND_LOG.STRING(G_PROC_LEVEL,
2486 G_PKG_NAME, '10500: Leaving restore_version' );
2487 END IF;
2488
2489 RETURN( G_RET_STS_SUCCESS );
2490
2491 EXCEPTION
2492 WHEN OTHERS THEN
2493
2494 /*IF (l_debug = 'Y') THEN
2495 Okc_Debug.Log('10600: Leaving restore_version because of EXCEPTION: '||sqlerrm, 2);
2496 END IF;*/
2497
2498 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2499 FND_LOG.STRING(G_EXCP_LEVEL,
2500 G_PKG_NAME, '10600: Leaving restore_version because of EXCEPTION: '||sqlerrm );
2501 END IF;
2502
2503 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2504 p_msg_name => G_UNEXPECTED_ERROR,
2505 p_token1 => G_SQLCODE_TOKEN,
2506 p_token1_value => sqlcode,
2507 p_token2 => G_SQLERRM_TOKEN,
2508 p_token2_value => sqlerrm);
2509
2510 RETURN G_RET_STS_UNEXP_ERROR ;
2511
2512 END restore_version;
2513
2514 FUNCTION Delete_Version(
2515 p_doc_type IN VARCHAR2,
2516 p_doc_id IN NUMBER,
2517 p_major_version IN NUMBER
2518 ) RETURN VARCHAR2
2519 IS
2520 CURSOR doc_ver_mrv_csr
2521 IS
2522 SELECT kart.ID
2523 FROM OKC_K_ARTICLES_BH KART
2524 , OKC_BUS_VARIABLES_B BUS_VAR
2525 , OKC_K_ART_VARIABLES_H KVAR
2526 WHERE kart.document_type=p_doc_type
2527 AND kart.document_id = p_doc_id
2528 AND KVAR.cat_id=kart.id
2529 AND KVAR.variable_code=BUS_VAR.variable_code
2530 AND BUS_VAR.MRV_FLAG='Y'
2531 AND KART.major_version=p_major_version
2532 AND KVAR.major_version=p_major_version;
2533 BEGIN
2534
2535 /*IF (l_debug = 'Y') THEN
2536 Okc_Debug.Log('7200: Entered Delete_Version', 2);
2537 END IF;*/
2538
2539 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2540 FND_LOG.STRING(G_PROC_LEVEL,
2541 G_PKG_NAME, '7200: Entered Delete_Version' );
2542 END IF;
2543
2544 -----------------------------------------
2545 -- Restoring Base Table
2546 -----------------------------------------
2547 FOR cat_rec IN doc_ver_mrv_csr LOOP
2548 delete_mrv_uda_data(p_cat_id => cat_rec.id,p_variable_code => NULL, p_major_version =>p_major_version);
2549 END LOOP;
2550
2551 DELETE
2552 FROM OKC_K_ART_VARIABLES_H
2553 WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_BH
2554 WHERE document_type = p_doc_type and document_id = p_doc_id)
2555 AND major_version = p_major_version;
2556
2557 /*IF (l_debug = 'Y') THEN
2558 Okc_Debug.Log('7300: Leaving Delete_Version', 2);
2559 END IF;*/
2560
2561 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2562 FND_LOG.STRING(G_PROC_LEVEL,
2563 G_PKG_NAME, '7300: Leaving Delete_Version' );
2564 END IF;
2565
2566 RETURN( G_RET_STS_SUCCESS );
2567
2568 EXCEPTION
2569 WHEN OTHERS THEN
2570
2571 /*IF (l_debug = 'Y') THEN
2572 Okc_Debug.Log('7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm, 2);
2573 END IF;*/
2574
2575 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2576 FND_LOG.STRING(G_EXCP_LEVEL,
2577 G_PKG_NAME, '7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm );
2578 END IF;
2579
2580 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2581 p_msg_name => G_UNEXPECTED_ERROR,
2582 p_token1 => G_SQLCODE_TOKEN,
2583 p_token1_value => sqlcode,
2584 p_token2 => G_SQLERRM_TOKEN,
2585 p_token2_value => sqlerrm);
2586
2587 RETURN G_RET_STS_UNEXP_ERROR ;
2588
2589 END Delete_Version;
2590
2591 FUNCTION isArtVariableMRV(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2)
2592 RETURN VARCHAR2
2593 IS
2594 l_mrv_flag VARCHAR2(1):= 'N';
2595 BEGIN
2596
2597 IF p_cat_id IS NOT NULL THEN
2598 SELECT 'Y'
2599 INTO l_mrv_flag
2600 FROM okc_k_art_variables kav
2601 WHERE cat_id = p_cat_id
2602 AND EXISTS (SELECT 1
2603 FROM okc_bus_variables_b var
2604 WHERE kav.variable_code=var.variable_code
2605 AND var.mrv_flag='Y');
2606 ELSE
2607 SELECT Nvl(mrv_flag,'N')
2608 INTO l_mrv_flag
2609 FROM okc_bus_variables_b
2610 WHERE variable_code=p_variable_code;
2611 END IF;
2612 RETURN l_mrv_flag;
2613 EXCEPTION
2614 WHEN OTHERS THEN
2615 RETURN 'N';
2616 END isArtVariableMRV;
2617
2618 PROCEDURE delete_mrv_uda_data(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2,p_major_version IN NUMBER)
2619 IS
2620 BEGIN
2621 if p_major_version IS NULL THEN
2622 IF p_cat_id IS NOT NULL THEN
2623 IF p_variable_code IS NOT NULL
2624 THEN
2625 DELETE FROM OKC_K_ART_VAR_EXT_B
2626 WHERE cat_id = p_cat_id
2627 AND variable_code = p_variable_code;
2628
2629 DELETE FROM OKC_K_ART_VAR_EXT_TL
2630 WHERE cat_id = p_cat_id
2631 AND variable_code = p_variable_code;
2632 ELSE
2633 DELETE FROM OKC_K_ART_VAR_EXT_B
2634 WHERE cat_id = p_cat_id;
2635
2636 DELETE FROM OKC_K_ART_VAR_EXT_TL
2637 WHERE cat_id = p_cat_id;
2638
2639 END IF;
2640 ELSE
2641 -- This should be an error condn;
2642 -- Cat id is a must
2643 NULL;
2644 END IF;
2645 ELSE -- p_major_version IS NOT NULL THEN
2646 IF p_cat_id IS NOT NULL THEN
2647 IF p_variable_code IS NOT NULL
2648 THEN
2649 DELETE FROM OKC_K_ART_VAR_EXT_BH
2650 WHERE cat_id = p_cat_id
2651 AND variable_code = p_variable_code
2652 AND major_version =p_major_version;
2653
2654 DELETE FROM OKC_K_ART_VAR_EXT_TLH
2655 WHERE cat_id = p_cat_id
2656 AND variable_code = p_variable_code
2657 AND major_version =p_major_version;
2658 ELSE
2659 DELETE FROM OKC_K_ART_VAR_EXT_BH
2660 WHERE cat_id = p_cat_id
2661 AND major_version =p_major_version;
2662
2663 DELETE FROM OKC_K_ART_VAR_EXT_TLH
2664 WHERE cat_id = p_cat_id
2665 AND major_version =p_major_version;
2666
2667 END IF;
2668 ELSE
2669 -- This should be an error condn;
2670 -- Cat id is a must
2671 NULL;
2672 END IF;
2673 END IF;
2674 EXCEPTION
2675 WHEN OTHERS THEN
2676 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2677 FND_LOG.STRING(G_EXCP_LEVEL,
2678 G_PKG_NAME, ' Leaving delete_mrv_uda_data because of EXCEPTION: '||sqlerrm );
2679 END IF;
2680
2681 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2682 p_msg_name => G_UNEXPECTED_ERROR,
2683 p_token1 => G_SQLCODE_TOKEN,
2684 p_token1_value => sqlcode,
2685 p_token2 => G_SQLERRM_TOKEN,
2686 p_token2_value => sqlerrm);
2687 END delete_mrv_uda_data;
2688
2689 PROCEDURE create_mrv_uda_data_version(p_cat_id IN NUMBER, p_major_version IN NUMBER)
2690 IS
2691 BEGIN
2692 INSERT INTO okc_k_art_var_ext_bh
2693 (
2694 EXTENSION_ID
2695 ,ATTR_GROUP_ID
2696 ,CAT_ID
2697 ,VARIABLE_CODE
2698 ,DATA_LEVEL_ID
2699 ,PK1_VALUE
2700 ,PK2_VALUE
2701 ,PK3_VALUE
2702 ,PK4_VALUE
2703 ,PK5_VALUE
2704 ,LAST_UPDATE_DATE
2705 ,LAST_UPDATED_BY
2706 ,LAST_UPDATE_LOGIN
2707 ,CREATED_BY
2708 ,CREATION_DATE
2709 ,C_EXT_ATTR1
2710 ,C_EXT_ATTR2
2711 ,C_EXT_ATTR3
2712 ,C_EXT_ATTR4
2713 ,C_EXT_ATTR5
2714 ,C_EXT_ATTR6
2715 ,C_EXT_ATTR7
2716 ,C_EXT_ATTR8
2717 ,C_EXT_ATTR9
2718 ,C_EXT_ATTR10
2719 ,C_EXT_ATTR11
2720 ,C_EXT_ATTR12
2721 ,C_EXT_ATTR13
2722 ,C_EXT_ATTR14
2723 ,C_EXT_ATTR15
2724 ,C_EXT_ATTR16
2725 ,C_EXT_ATTR17
2726 ,C_EXT_ATTR18
2727 ,C_EXT_ATTR19
2728 ,C_EXT_ATTR20
2729 ,C_EXT_ATTR21
2730 ,C_EXT_ATTR22
2731 ,C_EXT_ATTR23
2732 ,C_EXT_ATTR24
2733 ,C_EXT_ATTR25
2734 ,C_EXT_ATTR26
2735 ,C_EXT_ATTR27
2736 ,C_EXT_ATTR28
2737 ,C_EXT_ATTR29
2738 ,C_EXT_ATTR30
2739 ,C_EXT_ATTR31
2740 ,C_EXT_ATTR32
2741 ,C_EXT_ATTR33
2742 ,C_EXT_ATTR34
2743 ,C_EXT_ATTR35
2744 ,C_EXT_ATTR36
2745 ,C_EXT_ATTR37
2746 ,C_EXT_ATTR38
2747 ,C_EXT_ATTR39
2748 ,C_EXT_ATTR40
2749 ,N_EXT_ATTR1
2750 ,N_EXT_ATTR2
2751 ,N_EXT_ATTR3
2752 ,N_EXT_ATTR4
2753 ,N_EXT_ATTR5
2754 ,N_EXT_ATTR6
2755 ,N_EXT_ATTR7
2756 ,N_EXT_ATTR8
2757 ,N_EXT_ATTR9
2758 ,N_EXT_ATTR10
2759 ,N_EXT_ATTR11
2760 ,N_EXT_ATTR12
2761 ,N_EXT_ATTR13
2762 ,N_EXT_ATTR14
2763 ,N_EXT_ATTR15
2764 ,N_EXT_ATTR16
2765 ,N_EXT_ATTR17
2766 ,N_EXT_ATTR18
2767 ,N_EXT_ATTR19
2768 ,N_EXT_ATTR20
2769 ,UOM_EXT_ATTR1
2770 ,UOM_EXT_ATTR2
2771 ,UOM_EXT_ATTR3
2772 ,UOM_EXT_ATTR4
2773 ,UOM_EXT_ATTR5
2774 ,UOM_EXT_ATTR6
2775 ,UOM_EXT_ATTR7
2776 ,UOM_EXT_ATTR8
2777 ,UOM_EXT_ATTR9
2778 ,UOM_EXT_ATTR10
2779 ,UOM_EXT_ATTR11
2780 ,UOM_EXT_ATTR12
2781 ,UOM_EXT_ATTR13
2782 ,UOM_EXT_ATTR14
2783 ,UOM_EXT_ATTR15
2784 ,UOM_EXT_ATTR16
2785 ,UOM_EXT_ATTR17
2786 ,UOM_EXT_ATTR18
2787 ,UOM_EXT_ATTR19
2788 ,UOM_EXT_ATTR20
2789 ,D_EXT_ATTR1
2790 ,D_EXT_ATTR2
2791 ,D_EXT_ATTR3
2792 ,D_EXT_ATTR4
2793 ,D_EXT_ATTR5
2794 ,D_EXT_ATTR6
2795 ,D_EXT_ATTR7
2796 ,D_EXT_ATTR8
2797 ,D_EXT_ATTR9
2798 ,D_EXT_ATTR10
2799 ,MAJOR_VERSION
2800 )
2801 SELECT
2802 extension_id
2803 , ATTR_GROUP_ID
2804 , CAT_ID
2805 , VARIABLE_CODE
2806 , DATA_LEVEL_ID
2807 , PK1_VALUE
2808 , PK2_VALUE
2809 , PK3_VALUE
2810 , PK4_VALUE
2811 , PK5_VALUE
2812 , LAST_UPDATE_DATE
2813 , LAST_UPDATED_BY
2814 , LAST_UPDATE_LOGIN
2815 , CREATED_BY
2816 , CREATION_DATE
2817 , C_EXT_ATTR1
2818 , C_EXT_ATTR2
2819 , C_EXT_ATTR3
2820 , C_EXT_ATTR4
2821 , C_EXT_ATTR5
2822 , C_EXT_ATTR6
2823 , C_EXT_ATTR7
2824 , C_EXT_ATTR8
2825 , C_EXT_ATTR9
2826 , C_EXT_ATTR10
2827 , C_EXT_ATTR11
2828 , C_EXT_ATTR12
2829 , C_EXT_ATTR13
2830 , C_EXT_ATTR14
2831 , C_EXT_ATTR15
2832 , C_EXT_ATTR16
2833 , C_EXT_ATTR17
2834 , C_EXT_ATTR18
2835 , C_EXT_ATTR19
2836 , C_EXT_ATTR20
2837 , C_EXT_ATTR21
2838 , C_EXT_ATTR22
2839 , C_EXT_ATTR23
2840 , C_EXT_ATTR24
2841 , C_EXT_ATTR25
2842 , C_EXT_ATTR26
2843 , C_EXT_ATTR27
2844 , C_EXT_ATTR28
2845 , C_EXT_ATTR29
2846 , C_EXT_ATTR30
2847 , C_EXT_ATTR31
2848 , C_EXT_ATTR32
2849 , C_EXT_ATTR33
2850 , C_EXT_ATTR34
2851 , C_EXT_ATTR35
2852 , C_EXT_ATTR36
2853 , C_EXT_ATTR37
2854 , C_EXT_ATTR38
2855 , C_EXT_ATTR39
2856 , C_EXT_ATTR40
2857 , N_EXT_ATTR1
2858 , N_EXT_ATTR2
2859 , N_EXT_ATTR3
2860 , N_EXT_ATTR4
2861 , N_EXT_ATTR5
2862 , N_EXT_ATTR6
2863 , N_EXT_ATTR7
2864 , N_EXT_ATTR8
2865 , N_EXT_ATTR9
2866 , N_EXT_ATTR10
2867 , N_EXT_ATTR11
2868 , N_EXT_ATTR12
2869 , N_EXT_ATTR13
2870 , N_EXT_ATTR14
2871 , N_EXT_ATTR15
2872 , N_EXT_ATTR16
2873 , N_EXT_ATTR17
2874 , N_EXT_ATTR18
2875 , N_EXT_ATTR19
2876 , N_EXT_ATTR20
2877 , UOM_EXT_ATTR1
2878 , UOM_EXT_ATTR2
2879 , UOM_EXT_ATTR3
2880 , UOM_EXT_ATTR4
2881 , UOM_EXT_ATTR5
2882 , UOM_EXT_ATTR6
2883 , UOM_EXT_ATTR7
2884 , UOM_EXT_ATTR8
2885 , UOM_EXT_ATTR9
2886 , UOM_EXT_ATTR10
2887 , UOM_EXT_ATTR11
2888 , UOM_EXT_ATTR12
2889 , UOM_EXT_ATTR13
2890 , UOM_EXT_ATTR14
2891 , UOM_EXT_ATTR15
2892 , UOM_EXT_ATTR16
2893 , UOM_EXT_ATTR17
2894 , UOM_EXT_ATTR18
2895 , UOM_EXT_ATTR19
2896 , UOM_EXT_ATTR20
2897 , D_EXT_ATTR1
2898 , D_EXT_ATTR2
2899 , D_EXT_ATTR3
2900 , D_EXT_ATTR4
2901 , D_EXT_ATTR5
2902 , D_EXT_ATTR6
2903 , D_EXT_ATTR7
2904 , D_EXT_ATTR8
2905 , D_EXT_ATTR9
2906 , D_EXT_ATTR10
2907 ,p_major_version
2908 FROM OKC_K_ART_VAR_EXT_B
2909 WHERE cat_id=p_cat_id;
2910
2911 INSERT INTO OKC_K_ART_VAR_EXT_TLH
2912 (EXTENSION_ID
2913 , ATTR_GROUP_ID
2914 , CAT_ID
2915 , VARIABLE_CODE
2916 , DATA_LEVEL_ID
2917 , SOURCE_LANG
2918 , LANGUAGE
2919 , LAST_UPDATE_DATE
2920 , LAST_UPDATED_BY
2921 , LAST_UPDATE_LOGIN
2922 , CREATED_BY
2923 , CREATION_DATE
2924 , TL_EXT_ATTR1
2925 , TL_EXT_ATTR2
2926 , TL_EXT_ATTR3
2927 , TL_EXT_ATTR4
2928 , TL_EXT_ATTR5
2929 , TL_EXT_ATTR6
2930 , TL_EXT_ATTR7
2931 , TL_EXT_ATTR8
2932 , TL_EXT_ATTR9
2933 , TL_EXT_ATTR10
2934 , TL_EXT_ATTR11
2935 , TL_EXT_ATTR12
2936 , TL_EXT_ATTR13
2937 , TL_EXT_ATTR14
2938 , TL_EXT_ATTR15
2939 , TL_EXT_ATTR16
2940 , TL_EXT_ATTR17
2941 , TL_EXT_ATTR18
2942 , TL_EXT_ATTR19
2943 , TL_EXT_ATTR20
2944 , TL_EXT_ATTR21
2945 , TL_EXT_ATTR22
2946 , TL_EXT_ATTR23
2947 , TL_EXT_ATTR24
2948 , TL_EXT_ATTR25
2949 , TL_EXT_ATTR26
2950 , TL_EXT_ATTR27
2951 , TL_EXT_ATTR28
2952 , TL_EXT_ATTR29
2953 , TL_EXT_ATTR30
2954 , TL_EXT_ATTR31
2955 , TL_EXT_ATTR32
2956 , TL_EXT_ATTR33
2957 , TL_EXT_ATTR34
2958 , TL_EXT_ATTR35
2959 , TL_EXT_ATTR36
2960 , TL_EXT_ATTR37
2961 , TL_EXT_ATTR38
2962 , TL_EXT_ATTR39
2963 , TL_EXT_ATTR40
2964 , MAJOR_VERSION
2965 )
2966 SELECT
2967 EXTENSION_ID
2968 , ATTR_GROUP_ID
2969 , CAT_ID
2970 , VARIABLE_CODE
2971 , DATA_LEVEL_ID
2972 , SOURCE_LANG
2973 , LANGUAGE
2974 , LAST_UPDATE_DATE
2975 , LAST_UPDATED_BY
2976 , LAST_UPDATE_LOGIN
2977 , CREATED_BY
2978 , CREATION_DATE
2979 , TL_EXT_ATTR1
2980 , TL_EXT_ATTR2
2981 , TL_EXT_ATTR3
2982 , TL_EXT_ATTR4
2983 , TL_EXT_ATTR5
2984 , TL_EXT_ATTR6
2985 , TL_EXT_ATTR7
2986 , TL_EXT_ATTR8
2987 , TL_EXT_ATTR9
2988 , TL_EXT_ATTR10
2989 , TL_EXT_ATTR11
2990 , TL_EXT_ATTR12
2991 , TL_EXT_ATTR13
2992 , TL_EXT_ATTR14
2993 , TL_EXT_ATTR15
2994 , TL_EXT_ATTR16
2995 , TL_EXT_ATTR17
2996 , TL_EXT_ATTR18
2997 , TL_EXT_ATTR19
2998 , TL_EXT_ATTR20
2999 , TL_EXT_ATTR21
3000 , TL_EXT_ATTR22
3001 , TL_EXT_ATTR23
3002 , TL_EXT_ATTR24
3003 , TL_EXT_ATTR25
3004 , TL_EXT_ATTR26
3005 , TL_EXT_ATTR27
3006 , TL_EXT_ATTR28
3007 , TL_EXT_ATTR29
3008 , TL_EXT_ATTR30
3009 , TL_EXT_ATTR31
3010 , TL_EXT_ATTR32
3011 , TL_EXT_ATTR33
3012 , TL_EXT_ATTR34
3013 , TL_EXT_ATTR35
3014 , TL_EXT_ATTR36
3015 , TL_EXT_ATTR37
3016 , TL_EXT_ATTR38
3017 , TL_EXT_ATTR39
3018 , TL_EXT_ATTR40
3019 , P_MAJOR_VERSION
3020 FROM OKC_K_ART_VAR_EXT_TL
3021 WHERE cat_id=p_cat_id;
3022 END create_mrv_uda_data_version;
3023
3024 PROCEDURE restore_mrv_uda_data_version(p_cat_id IN NUMBER,p_major_version IN NUMBER)
3025 IS
3026 BEGIN
3027 INSERT INTO okc_k_art_var_ext_b
3028 (
3029 EXTENSION_ID
3030 ,ATTR_GROUP_ID
3031 ,CAT_ID
3032 ,VARIABLE_CODE
3033 ,DATA_LEVEL_ID
3034 ,PK1_VALUE
3035 ,PK2_VALUE
3036 ,PK3_VALUE
3037 ,PK4_VALUE
3038 ,PK5_VALUE
3039 ,LAST_UPDATE_DATE
3040 ,LAST_UPDATED_BY
3041 ,LAST_UPDATE_LOGIN
3042 ,CREATED_BY
3043 ,CREATION_DATE
3044 ,C_EXT_ATTR1
3045 ,C_EXT_ATTR2
3046 ,C_EXT_ATTR3
3047 ,C_EXT_ATTR4
3048 ,C_EXT_ATTR5
3049 ,C_EXT_ATTR6
3050 ,C_EXT_ATTR7
3051 ,C_EXT_ATTR8
3052 ,C_EXT_ATTR9
3053 ,C_EXT_ATTR10
3054 ,C_EXT_ATTR11
3055 ,C_EXT_ATTR12
3056 ,C_EXT_ATTR13
3057 ,C_EXT_ATTR14
3058 ,C_EXT_ATTR15
3059 ,C_EXT_ATTR16
3060 ,C_EXT_ATTR17
3061 ,C_EXT_ATTR18
3062 ,C_EXT_ATTR19
3063 ,C_EXT_ATTR20
3064 ,C_EXT_ATTR21
3065 ,C_EXT_ATTR22
3066 ,C_EXT_ATTR23
3067 ,C_EXT_ATTR24
3068 ,C_EXT_ATTR25
3069 ,C_EXT_ATTR26
3070 ,C_EXT_ATTR27
3071 ,C_EXT_ATTR28
3072 ,C_EXT_ATTR29
3073 ,C_EXT_ATTR30
3074 ,C_EXT_ATTR31
3075 ,C_EXT_ATTR32
3076 ,C_EXT_ATTR33
3077 ,C_EXT_ATTR34
3078 ,C_EXT_ATTR35
3079 ,C_EXT_ATTR36
3080 ,C_EXT_ATTR37
3081 ,C_EXT_ATTR38
3082 ,C_EXT_ATTR39
3083 ,C_EXT_ATTR40
3084 ,N_EXT_ATTR1
3085 ,N_EXT_ATTR2
3086 ,N_EXT_ATTR3
3087 ,N_EXT_ATTR4
3088 ,N_EXT_ATTR5
3089 ,N_EXT_ATTR6
3090 ,N_EXT_ATTR7
3091 ,N_EXT_ATTR8
3092 ,N_EXT_ATTR9
3093 ,N_EXT_ATTR10
3094 ,N_EXT_ATTR11
3095 ,N_EXT_ATTR12
3096 ,N_EXT_ATTR13
3097 ,N_EXT_ATTR14
3098 ,N_EXT_ATTR15
3099 ,N_EXT_ATTR16
3100 ,N_EXT_ATTR17
3101 ,N_EXT_ATTR18
3102 ,N_EXT_ATTR19
3103 ,N_EXT_ATTR20
3104 ,UOM_EXT_ATTR1
3105 ,UOM_EXT_ATTR2
3106 ,UOM_EXT_ATTR3
3107 ,UOM_EXT_ATTR4
3108 ,UOM_EXT_ATTR5
3109 ,UOM_EXT_ATTR6
3110 ,UOM_EXT_ATTR7
3111 ,UOM_EXT_ATTR8
3112 ,UOM_EXT_ATTR9
3113 ,UOM_EXT_ATTR10
3114 ,UOM_EXT_ATTR11
3115 ,UOM_EXT_ATTR12
3116 ,UOM_EXT_ATTR13
3117 ,UOM_EXT_ATTR14
3118 ,UOM_EXT_ATTR15
3119 ,UOM_EXT_ATTR16
3120 ,UOM_EXT_ATTR17
3121 ,UOM_EXT_ATTR18
3122 ,UOM_EXT_ATTR19
3123 ,UOM_EXT_ATTR20
3124 ,D_EXT_ATTR1
3125 ,D_EXT_ATTR2
3126 ,D_EXT_ATTR3
3127 ,D_EXT_ATTR4
3128 ,D_EXT_ATTR5
3129 ,D_EXT_ATTR6
3130 ,D_EXT_ATTR7
3131 ,D_EXT_ATTR8
3132 ,D_EXT_ATTR9
3133 ,D_EXT_ATTR10
3134 )
3135 SELECT
3136 EXTENSION_ID -- EGO_EXTFWK_S.NEXTVAL
3137 , ATTR_GROUP_ID
3138 , CAT_ID
3139 , VARIABLE_CODE
3140 , DATA_LEVEL_ID
3141 , PK1_VALUE
3142 , PK2_VALUE
3143 , PK3_VALUE
3144 , PK4_VALUE
3145 , PK5_VALUE
3146 , LAST_UPDATE_DATE
3147 , LAST_UPDATED_BY
3148 , LAST_UPDATE_LOGIN
3149 , CREATED_BY
3150 , CREATION_DATE
3151 , C_EXT_ATTR1
3152 , C_EXT_ATTR2
3153 , C_EXT_ATTR3
3154 , C_EXT_ATTR4
3155 , C_EXT_ATTR5
3156 , C_EXT_ATTR6
3157 , C_EXT_ATTR7
3158 , C_EXT_ATTR8
3159 , C_EXT_ATTR9
3160 , C_EXT_ATTR10
3161 , C_EXT_ATTR11
3162 , C_EXT_ATTR12
3163 , C_EXT_ATTR13
3164 , C_EXT_ATTR14
3165 , C_EXT_ATTR15
3166 , C_EXT_ATTR16
3167 , C_EXT_ATTR17
3168 , C_EXT_ATTR18
3169 , C_EXT_ATTR19
3170 , C_EXT_ATTR20
3171 , C_EXT_ATTR21
3172 , C_EXT_ATTR22
3173 , C_EXT_ATTR23
3174 , C_EXT_ATTR24
3175 , C_EXT_ATTR25
3176 , C_EXT_ATTR26
3177 , C_EXT_ATTR27
3178 , C_EXT_ATTR28
3179 , C_EXT_ATTR29
3180 , C_EXT_ATTR30
3181 , C_EXT_ATTR31
3182 , C_EXT_ATTR32
3183 , C_EXT_ATTR33
3184 , C_EXT_ATTR34
3185 , C_EXT_ATTR35
3186 , C_EXT_ATTR36
3187 , C_EXT_ATTR37
3188 , C_EXT_ATTR38
3189 , C_EXT_ATTR39
3190 , C_EXT_ATTR40
3191 , N_EXT_ATTR1
3192 , N_EXT_ATTR2
3193 , N_EXT_ATTR3
3194 , N_EXT_ATTR4
3195 , N_EXT_ATTR5
3196 , N_EXT_ATTR6
3197 , N_EXT_ATTR7
3198 , N_EXT_ATTR8
3199 , N_EXT_ATTR9
3200 , N_EXT_ATTR10
3201 , N_EXT_ATTR11
3202 , N_EXT_ATTR12
3203 , N_EXT_ATTR13
3204 , N_EXT_ATTR14
3205 , N_EXT_ATTR15
3206 , N_EXT_ATTR16
3207 , N_EXT_ATTR17
3208 , N_EXT_ATTR18
3209 , N_EXT_ATTR19
3210 , N_EXT_ATTR20
3211 , UOM_EXT_ATTR1
3212 , UOM_EXT_ATTR2
3213 , UOM_EXT_ATTR3
3214 , UOM_EXT_ATTR4
3215 , UOM_EXT_ATTR5
3216 , UOM_EXT_ATTR6
3217 , UOM_EXT_ATTR7
3218 , UOM_EXT_ATTR8
3219 , UOM_EXT_ATTR9
3220 , UOM_EXT_ATTR10
3221 , UOM_EXT_ATTR11
3222 , UOM_EXT_ATTR12
3223 , UOM_EXT_ATTR13
3224 , UOM_EXT_ATTR14
3225 , UOM_EXT_ATTR15
3226 , UOM_EXT_ATTR16
3227 , UOM_EXT_ATTR17
3228 , UOM_EXT_ATTR18
3229 , UOM_EXT_ATTR19
3230 , UOM_EXT_ATTR20
3231 , D_EXT_ATTR1
3232 , D_EXT_ATTR2
3233 , D_EXT_ATTR3
3234 , D_EXT_ATTR4
3235 , D_EXT_ATTR5
3236 , D_EXT_ATTR6
3237 , D_EXT_ATTR7
3238 , D_EXT_ATTR8
3239 , D_EXT_ATTR9
3240 , D_EXT_ATTR10
3241 FROM OKC_K_ART_VAR_EXT_BH
3242 WHERE cat_id=p_cat_id
3243 AND major_version =p_major_version;
3244
3245 INSERT INTO OKC_K_ART_VAR_EXT_TL
3246 (EXTENSION_ID
3247 , ATTR_GROUP_ID
3248 , CAT_ID
3249 , VARIABLE_CODE
3250 , DATA_LEVEL_ID
3251 , SOURCE_LANG
3252 , LANGUAGE
3253 , LAST_UPDATE_DATE
3254 , LAST_UPDATED_BY
3255 , LAST_UPDATE_LOGIN
3256 , CREATED_BY
3257 , CREATION_DATE
3258 , TL_EXT_ATTR1
3259 , TL_EXT_ATTR2
3260 , TL_EXT_ATTR3
3261 , TL_EXT_ATTR4
3262 , TL_EXT_ATTR5
3263 , TL_EXT_ATTR6
3264 , TL_EXT_ATTR7
3265 , TL_EXT_ATTR8
3266 , TL_EXT_ATTR9
3267 , TL_EXT_ATTR10
3268 , TL_EXT_ATTR11
3269 , TL_EXT_ATTR12
3270 , TL_EXT_ATTR13
3271 , TL_EXT_ATTR14
3272 , TL_EXT_ATTR15
3273 , TL_EXT_ATTR16
3274 , TL_EXT_ATTR17
3275 , TL_EXT_ATTR18
3276 , TL_EXT_ATTR19
3277 , TL_EXT_ATTR20
3278 , TL_EXT_ATTR21
3279 , TL_EXT_ATTR22
3280 , TL_EXT_ATTR23
3281 , TL_EXT_ATTR24
3282 , TL_EXT_ATTR25
3283 , TL_EXT_ATTR26
3284 , TL_EXT_ATTR27
3285 , TL_EXT_ATTR28
3286 , TL_EXT_ATTR29
3287 , TL_EXT_ATTR30
3288 , TL_EXT_ATTR31
3289 , TL_EXT_ATTR32
3290 , TL_EXT_ATTR33
3291 , TL_EXT_ATTR34
3292 , TL_EXT_ATTR35
3293 , TL_EXT_ATTR36
3294 , TL_EXT_ATTR37
3295 , TL_EXT_ATTR38
3296 , TL_EXT_ATTR39
3297 , TL_EXT_ATTR40
3298 )
3299 SELECT
3300 TLH.EXTENSION_ID
3301 , TLH.ATTR_GROUP_ID
3302 , TLH.CAT_ID
3303 , TLH.VARIABLE_CODE
3304 , TLH.DATA_LEVEL_ID
3305 , TLH.SOURCE_LANG
3306 , TLH.LANGUAGE
3307 , TLH.LAST_UPDATE_DATE
3308 , TLH.LAST_UPDATED_BY
3309 , TLH.LAST_UPDATE_LOGIN
3310 , TLH.CREATED_BY
3311 , TLH.CREATION_DATE
3312 , TLH.TL_EXT_ATTR1
3313 , TLH.TL_EXT_ATTR2
3314 , TLH.TL_EXT_ATTR3
3315 , TLH.TL_EXT_ATTR4
3316 , TLH.TL_EXT_ATTR5
3317 , TLH.TL_EXT_ATTR6
3318 , TLH.TL_EXT_ATTR7
3319 , TLH.TL_EXT_ATTR8
3320 , TLH.TL_EXT_ATTR9
3321 , TLH.TL_EXT_ATTR10
3322 , TLH.TL_EXT_ATTR11
3323 , TLH.TL_EXT_ATTR12
3324 , TLH.TL_EXT_ATTR13
3325 , TLH.TL_EXT_ATTR14
3326 , TLH.TL_EXT_ATTR15
3327 , TLH.TL_EXT_ATTR16
3328 , TLH.TL_EXT_ATTR17
3329 , TLH.TL_EXT_ATTR18
3330 , TLH.TL_EXT_ATTR19
3331 , TLH.TL_EXT_ATTR20
3332 , TLH.TL_EXT_ATTR21
3333 , TLH.TL_EXT_ATTR22
3334 , TLH.TL_EXT_ATTR23
3335 , TLH.TL_EXT_ATTR24
3336 , TLH.TL_EXT_ATTR25
3337 , TLH.TL_EXT_ATTR26
3338 , TLH.TL_EXT_ATTR27
3339 , TLH.TL_EXT_ATTR28
3340 , TLH.TL_EXT_ATTR29
3341 , TLH.TL_EXT_ATTR30
3342 , TLH.TL_EXT_ATTR31
3343 , TLH.TL_EXT_ATTR32
3344 , TLH.TL_EXT_ATTR33
3345 , TLH.TL_EXT_ATTR34
3346 , TLH.TL_EXT_ATTR35
3347 , TLH.TL_EXT_ATTR36
3348 , TLH.TL_EXT_ATTR37
3349 , TLH.TL_EXT_ATTR38
3350 , TLH.TL_EXT_ATTR39
3351 , TLH.TL_EXT_ATTR40
3352 FROM OKC_K_ART_VAR_EXT_TLH TLH
3353 WHERE 1=1
3354 AND TLH.cat_id=p_cat_id
3355 AND TLH.MAJOR_VERSION=p_major_version;
3356 END restore_mrv_uda_data_version;
3357
3358 END OKC_K_ART_VARIABLES_PVT;