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