[Home] [Help]
PACKAGE BODY: APPS.OKL_PMR_PVT
Source
1 PACKAGE BODY OKL_PMR_PVT AS
2 /* $Header: OKLSPMRB.pls 120.5 2007/01/09 08:43:02 abhsaxen noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 null;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 null;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 null;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- PROCEDURE add_language
37 ---------------------------------------------------------------------------
38 PROCEDURE add_language IS
39 BEGIN
40 DELETE FROM OKL_PARAMETERS_TL T
41 WHERE NOT EXISTS (
42 SELECT NULL
43 FROM OKL_PARAMETERS_B B --fixed bug 3321017 by kmotepal
44 WHERE B.ID = T.ID
45 );
46
47 UPDATE OKL_PARAMETERS_TL T SET (
48 DESCRIPTION) = (SELECT
49 B.DESCRIPTION
50 FROM OKL_PARAMETERS_TL B
51 WHERE B.ID = T.ID
52 AND B.LANGUAGE = T.SOURCE_LANG)
53 WHERE (
54 T.ID,
55 T.LANGUAGE)
56 IN (SELECT
57 SUBT.ID,
58 SUBT.LANGUAGE
59 FROM OKL_PARAMETERS_TL SUBB, OKL_PARAMETERS_TL SUBT
60 WHERE SUBB.ID = SUBT.ID
61 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
62 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
63 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
64 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
65 ));
66
67 INSERT INTO OKL_PARAMETERS_TL (
68 ID,
69 LANGUAGE,
70 SOURCE_LANG,
71 SFWT_FLAG,
72 DESCRIPTION,
73 CREATED_BY,
74 CREATION_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_DATE,
77 LAST_UPDATE_LOGIN)
78 SELECT
79 B.ID,
80 L.LANGUAGE_CODE,
81 B.SOURCE_LANG,
82 B.SFWT_FLAG,
83 B.DESCRIPTION,
84 B.CREATED_BY,
85 B.CREATION_DATE,
86 B.LAST_UPDATED_BY,
87 B.LAST_UPDATE_DATE,
88 B.LAST_UPDATE_LOGIN
89 FROM OKL_PARAMETERS_TL B, FND_LANGUAGES L
90 WHERE L.INSTALLED_FLAG IN ('I', 'B')
91 AND B.LANGUAGE = USERENV('LANG')
92 AND NOT EXISTS(
93 SELECT NULL
94 FROM OKL_PARAMETERS_TL T
95 WHERE T.ID = B.ID
96 AND T.LANGUAGE = L.LANGUAGE_CODE
97 );
98
99 END add_language;
100
101 ---------------------------------------------------------------------------
102 -- FUNCTION get_rec for: OKL_PARAMETERS_B
103 ---------------------------------------------------------------------------
104 FUNCTION get_rec (
105 p_pmr_rec IN pmr_rec_type,
106 x_no_data_found OUT NOCOPY BOOLEAN
107 ) RETURN pmr_rec_type IS
108 CURSOR okl_parameters_b_pk_csr (p_id IN NUMBER) IS
109 SELECT
110 ID,
111 NAME,
112 CUSTOMIZATION_LEVEL,
113 DATATYPE,
114 OBJECT_VERSION_NUMBER,
115 CREATED_BY,
116 CREATION_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATE_LOGIN
120 FROM Okl_Parameters_B
121 WHERE okl_parameters_b.id = p_id;
122 l_okl_parameters_b_pk okl_parameters_b_pk_csr%ROWTYPE;
123 l_pmr_rec pmr_rec_type;
124 BEGIN
125 x_no_data_found := TRUE;
126 -- Get current database values
127 OPEN okl_parameters_b_pk_csr (p_pmr_rec.id);
128 FETCH okl_parameters_b_pk_csr INTO
129 l_pmr_rec.ID,
130 l_pmr_rec.NAME,
131 l_pmr_rec.DATATYPE,
132 l_pmr_rec.CUSTOMIZATION_LEVEL,
133 l_pmr_rec.OBJECT_VERSION_NUMBER,
134 l_pmr_rec.CREATED_BY,
135 l_pmr_rec.CREATION_DATE,
136 l_pmr_rec.LAST_UPDATED_BY,
137 l_pmr_rec.LAST_UPDATE_DATE,
138 l_pmr_rec.LAST_UPDATE_LOGIN;
139 x_no_data_found := okl_parameters_b_pk_csr%NOTFOUND;
140 CLOSE okl_parameters_b_pk_csr;
141 RETURN(l_pmr_rec);
142 END get_rec;
143
144 FUNCTION get_rec (
145 p_pmr_rec IN pmr_rec_type
146 ) RETURN pmr_rec_type IS
147 l_row_notfound BOOLEAN := TRUE;
148 BEGIN
149 RETURN(get_rec(p_pmr_rec, l_row_notfound));
150 END get_rec;
151 ---------------------------------------------------------------------------
152 -- FUNCTION get_rec for: OKL_PARAMETERS_TL
153 ---------------------------------------------------------------------------
154 FUNCTION get_rec (
155 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
156 x_no_data_found OUT NOCOPY BOOLEAN
157 ) RETURN okl_parameters_tl_rec_type IS
158 CURSOR okl_parameters_tl_pk_csr (p_id IN NUMBER,
159 p_language IN VARCHAR2) IS
160 SELECT
161 ID,
162 LANGUAGE,
163 SOURCE_LANG,
164 SFWT_FLAG,
165 DESCRIPTION,
166 CREATED_BY,
167 CREATION_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_DATE,
170 LAST_UPDATE_LOGIN
171 FROM Okl_Parameters_Tl
172 WHERE okl_parameters_tl.id = p_id
173 AND okl_parameters_tl.language = p_language;
174 l_okl_parameters_tl_pk okl_parameters_tl_pk_csr%ROWTYPE;
175 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
176 BEGIN
177 x_no_data_found := TRUE;
178 -- Get current database values
179 OPEN okl_parameters_tl_pk_csr (p_okl_parameters_tl_rec.id,
180 p_okl_parameters_tl_rec.language);
181 FETCH okl_parameters_tl_pk_csr INTO
182 l_okl_parameters_tl_rec.ID,
183 l_okl_parameters_tl_rec.LANGUAGE,
184 l_okl_parameters_tl_rec.SOURCE_LANG,
185 l_okl_parameters_tl_rec.SFWT_FLAG,
186 l_okl_parameters_tl_rec.DESCRIPTION,
187 l_okl_parameters_tl_rec.CREATED_BY,
188 l_okl_parameters_tl_rec.CREATION_DATE,
189 l_okl_parameters_tl_rec.LAST_UPDATED_BY,
190 l_okl_parameters_tl_rec.LAST_UPDATE_DATE,
191 l_okl_parameters_tl_rec.LAST_UPDATE_LOGIN;
192 x_no_data_found := okl_parameters_tl_pk_csr%NOTFOUND;
193 CLOSE okl_parameters_tl_pk_csr;
194 RETURN(l_okl_parameters_tl_rec);
195 END get_rec;
196
197 FUNCTION get_rec (
198 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type
199 ) RETURN okl_parameters_tl_rec_type IS
200 l_row_notfound BOOLEAN := TRUE;
201 BEGIN
202 RETURN(get_rec(p_okl_parameters_tl_rec, l_row_notfound));
203 END get_rec;
204 ---------------------------------------------------------------------------
205 -- FUNCTION get_rec for: OKL_PARAMETERS_V
206 ---------------------------------------------------------------------------
207 FUNCTION get_rec (
208 p_pmrv_rec IN pmrv_rec_type,
209 x_no_data_found OUT NOCOPY BOOLEAN
210 ) RETURN pmrv_rec_type IS
211 CURSOR okl_pmrv_pk_csr (p_id IN NUMBER) IS
212 SELECT
213 ID,
214 OBJECT_VERSION_NUMBER,
215 SFWT_FLAG,
216 NAME,
217 CUSTOMIZATION_LEVEL,
218 DATATYPE,
219 DESCRIPTION,
220 CREATED_BY,
221 CREATION_DATE,
222 LAST_UPDATED_BY,
223 LAST_UPDATE_DATE,
224 LAST_UPDATE_LOGIN
225 FROM Okl_Parameters_V
226 WHERE okl_parameters_v.id = p_id;
227 l_okl_pmrv_pk okl_pmrv_pk_csr%ROWTYPE;
228 l_pmrv_rec pmrv_rec_type;
229 BEGIN
230 x_no_data_found := TRUE;
231 -- Get current database values
232 OPEN okl_pmrv_pk_csr (p_pmrv_rec.id);
233 FETCH okl_pmrv_pk_csr INTO
234 l_pmrv_rec.ID,
235 l_pmrv_rec.OBJECT_VERSION_NUMBER,
236 l_pmrv_rec.SFWT_FLAG,
237 l_pmrv_rec.NAME,
238 l_pmrv_rec.CUSTOMIZATION_LEVEL,
239 l_pmrv_rec.DATATYPE,
240 l_pmrv_rec.DESCRIPTION,
241 l_pmrv_rec.CREATED_BY,
242 l_pmrv_rec.CREATION_DATE,
243 l_pmrv_rec.LAST_UPDATED_BY,
244 l_pmrv_rec.LAST_UPDATE_DATE,
245 l_pmrv_rec.LAST_UPDATE_LOGIN;
246 x_no_data_found := okl_pmrv_pk_csr%NOTFOUND;
247 CLOSE okl_pmrv_pk_csr;
248 RETURN(l_pmrv_rec);
249 END get_rec;
250
251 FUNCTION get_rec (
252 p_pmrv_rec IN pmrv_rec_type
253 ) RETURN pmrv_rec_type IS
254 l_row_notfound BOOLEAN := TRUE;
255 BEGIN
256 RETURN(get_rec(p_pmrv_rec, l_row_notfound));
257 END get_rec;
258
259 ------------------------------------------------------
260 -- FUNCTION null_out_defaults for: OKL_PARAMETERS_V --
261 ------------------------------------------------------
262 FUNCTION null_out_defaults (
263 p_pmrv_rec IN pmrv_rec_type
264 ) RETURN pmrv_rec_type IS
265 l_pmrv_rec pmrv_rec_type := p_pmrv_rec;
266 BEGIN
267 IF (l_pmrv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
268 l_pmrv_rec.object_version_number := NULL;
269 END IF;
270 IF (l_pmrv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
271 l_pmrv_rec.sfwt_flag := NULL;
272 END IF;
273 IF (l_pmrv_rec.name = OKC_API.G_MISS_CHAR) THEN
274 l_pmrv_rec.name := NULL;
275 END IF;
276 IF (l_pmrv_rec.CUSTOMIZATION_LEVEL = OKC_API.G_MISS_CHAR) THEN
277 l_pmrv_rec.CUSTOMIZATION_LEVEL := NULL;
278 END IF;
279 IF (l_pmrv_rec.datatype = OKC_API.G_MISS_CHAR) THEN
280 l_pmrv_rec.datatype := NULL;
281 END IF;
282 IF (l_pmrv_rec.description = OKC_API.G_MISS_CHAR) THEN
283 l_pmrv_rec.description := NULL;
284 END IF;
285 IF (l_pmrv_rec.created_by = OKC_API.G_MISS_NUM) THEN
286 l_pmrv_rec.created_by := NULL;
287 END IF;
288 IF (l_pmrv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
289 l_pmrv_rec.creation_date := NULL;
290 END IF;
291 IF (l_pmrv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
292 l_pmrv_rec.last_updated_by := NULL;
293 END IF;
294 IF (l_pmrv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
295 l_pmrv_rec.last_update_date := NULL;
296 END IF;
297 IF (l_pmrv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
298 l_pmrv_rec.last_update_login := NULL;
299 END IF;
300 RETURN(l_pmrv_rec);
301 END null_out_defaults;
302
303 /** Commented out nocopy generated code in favor of hand written code *** SBALASHA001 Start ***
304 ---------------------------------------------------------------------------
305 -- PROCEDURE Validate_Attributes
306 ---------------------------------------------------------------------------
307 ----------------------------------------------
308 -- Validate_Attributes for:OKL_PARAMETERS_V --
309 ----------------------------------------------
310 FUNCTION Validate_Attributes (
311 p_pmrv_rec IN pmrv_rec_type
312 ) RETURN VARCHAR2 IS
313 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
314 BEGIN
315 IF p_pmrv_rec.id = OKC_API.G_MISS_NUM OR
316 p_pmrv_rec.id IS NULL
317 THEN
318 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
319 l_return_status := OKC_API.G_RET_STS_ERROR;
320 ELSIF p_pmrv_rec.object_version_number = OKC_API.G_MISS_NUM OR
321 p_pmrv_rec.object_version_number IS NULL
322 THEN
323 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
324 l_return_status := OKC_API.G_RET_STS_ERROR;
325 ELSIF p_pmrv_rec.name = OKC_API.G_MISS_CHAR OR
326 p_pmrv_rec.name IS NULL
327 THEN
328 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'name');
329 l_return_status := OKC_API.G_RET_STS_ERROR;
330 ELSIF p_pmrv_rec.CUSTOMIZATION_LEVEL = OKC_API.G_MISS_CHAR OR
331 p_pmrv_rec.CUSTOMIZATION_LEVEL IS NULL
332 THEN
333 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'customization_level');
334 l_return_status := OKC_API.G_RET_STS_ERROR;
335 ELSIF p_pmrv_rec.datatype = OKC_API.G_MISS_CHAR OR
336 p_pmrv_rec.datatype IS NULL
337 THEN
338 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'datatype');
339 l_return_status := OKC_API.G_RET_STS_ERROR;
340 END IF;
341 RETURN(l_return_status);
342 END Validate_Attributes;
343
344 ---------------------------------------------------------------------------
345 -- PROCEDURE Validate_Record
346 ---------------------------------------------------------------------------
347 ------------------------------------------
348 -- Validate_Record for:OKL_PARAMETERS_V --
349 ------------------------------------------
350 FUNCTION Validate_Record (
351 p_pmrv_rec IN pmrv_rec_type
352 ) RETURN VARCHAR2 IS
353 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
354 BEGIN
355 RETURN (l_return_status);
356 END Validate_Record;
357
358 *** SBALASHA001 End *** **/
359
360
361
362
363 /** SBALASHA001 Start *** -
364 INFO: hand coded function related to validate_attribute **/
365
366 ---------------------------------------------------------------------------
367 -- PROCEDURE Validate_Object_Version_Number
368 ---------------------------------------------------------------------------
369 -- Start of comments
370 --
371 -- Procedure Name : Validate_Object_Version_Number
372 -- Description :
373 -- Business Rules :
374 -- Parameters :
375 -- Version : 1.0
376 -- End of comments
377 ---------------------------------------------------------------------------
378 PROCEDURE Validate_Object_Version_Number(x_return_status OUT NOCOPY VARCHAR2
379 ,p_pmrv_rec IN pmrv_rec_type)
380 IS
381
382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
383
384 BEGIN
385 -- initialize return status
386 x_return_status := OKC_API.G_RET_STS_SUCCESS;
387
388 -- check for data before processing
389 IF ( p_pmrv_rec.object_version_number IS NULL ) OR
390 ( p_pmrv_rec.object_version_Number = OKC_API.G_MISS_NUM ) THEN
391 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
392 p_msg_name => g_required_value,
393 p_token1 => g_col_name_token,
394 p_token1_value => 'object_version_number' );
395 x_return_status := OKC_API.G_RET_STS_ERROR;
396 RAISE G_EXCEPTION_HALT_VALIDATION;
397 END IF;
398
399 EXCEPTION
400 WHEN G_EXCEPTION_HALT_VALIDATION THEN
401 -- no processing necessary; validation can continue
402 -- with the next column
403 NULL;
404
405 WHEN OTHERS THEN
406 -- store SQL error message on message stack for caller
407 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
408 p_msg_name => g_unexpected_error,
409 p_token1 => g_sqlcode_token,
410 p_token1_value => sqlcode,
411 p_token2 => g_sqlerrm_token,
412 p_token2_value => sqlerrm );
413
414 -- notify caller of an UNEXPECTED error
415 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
416
417 END Validate_Object_Version_Number;
418
419 ---------------------------------------------------------------------------
420 -- PROCEDURE Validate_Sfwt_Flag
421 ---------------------------------------------------------------------------
422 -- Start of comments
423 --
424 -- Procedure Name : Validate_Sfwt_Flag
425 -- Description :
426 -- Business Rules :
427 -- Parameters :
428 -- Version : 1.0
429 -- End of comments
430 ---------------------------------------------------------------------------
431 PROCEDURE Validate_Sfwt_Flag(x_return_status OUT NOCOPY VARCHAR2,
432 p_pmrv_rec IN pmrv_rec_type)
433 IS
434
435 -- l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
436 l_return_status VARCHAR2(1) := OKL_API.G_TRUE;
437
438 BEGIN
439 -- initialize return status
440 x_return_status := OKC_API.G_RET_STS_SUCCESS;
441
442 -- check from domain values using the generic
443 -- l_return_status := OKL_UTIL.check_domain_yn(p_pmrv_rec.sfwt_flag);
444 l_return_status := OKL_ACCOUNTING_UTIL.validate_lookup_code('YES_NO',p_pmrv_rec.sfwt_flag,0,0);
445
446 -- IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
447 IF (l_return_status = OKL_API.G_FALSE) THEN
448 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
449 p_msg_name => g_required_value,
450 p_token1 => g_col_name_token,
451 p_token1_value => 'sfwt_flag' );
452 x_return_status := OKC_API.G_RET_STS_ERROR;
453 END IF;
454
455
456 EXCEPTION
457 WHEN G_EXCEPTION_HALT_VALIDATION THEN
458 -- no processing required ; validation can continue
459 -- with the next column
460 NULL;
461
462 WHEN OTHERS THEN
463 -- store SQL error message on message stack for caller
464 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
465 p_msg_name => g_unexpected_error,
466 p_token1 => g_sqlcode_token,
467 p_token1_value => sqlcode,
468 p_token2 => g_sqlerrm_token,
469 p_token2_value => sqlerrm );
470
471 -- notify caller of an UNEXPECTED error
472 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
473
474 END Validate_Sfwt_Flag;
475
476 ---------------------------------------------------------------------------
477 -- PROCEDURE Validate_Name
478 ---------------------------------------------------------------------------
479 -- Start of comments
480 --
481 -- Procedure Name : Validate_Name
482 -- Description :
483 -- Business Rules :
484 -- Parameters :
485 -- Version : 1.0
486 -- End of comments
487 ---------------------------------------------------------------------------
488 PROCEDURE Validate_Name(x_return_status OUT NOCOPY VARCHAR2,
489 p_pmrv_rec IN pmrv_rec_type)
490 IS
491
492 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
493
494 BEGIN
495 -- initialize return status
496 x_return_status := OKC_API.G_RET_STS_SUCCESS;
497
498 -- check for data before processing
499 IF (p_pmrv_rec.name IS NULL) OR
500 (p_pmrv_rec.name = OKC_API.G_MISS_CHAR) THEN
501 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
502 p_msg_name => g_required_value,
503 p_token1 => g_col_name_token,
504 p_token1_value => 'name' );
505 x_return_status := OKC_API.G_RET_STS_ERROR;
506 RAISE G_EXCEPTION_HALT_VALIDATION;
507 END IF;
508
509
510 EXCEPTION
511 WHEN G_EXCEPTION_HALT_VALIDATION THEN
512 -- no processing required ; validation can continue
513 -- with the next column
514 NULL;
515
516 WHEN OTHERS THEN
517 -- store SQL error message on message stack for caller
518 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
519 p_msg_name => g_unexpected_error,
520 p_token1 => g_sqlcode_token,
521 p_token1_value => sqlcode,
522 p_token2 => g_sqlerrm_token,
523 p_token2_value => sqlerrm );
524
525 -- notify caller of an UNEXPECTED error
526 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
527
528 END Validate_Name;
529
530
531 ---------------------------------------------------------------------------
532 -- PROCEDURE Validate_Data_Type
533 ---------------------------------------------------------------------------
534 -- Start of comments
535 --
536 -- Procedure Name : Validate_Data_Type
537 -- Description :
538 -- Business Rules :
539 -- Parameters :
540 -- Version : 1.0
541 -- End of comments
542 ---------------------------------------------------------------------------
543 PROCEDURE Validate_Data_Type(x_return_status OUT NOCOPY VARCHAR2
544 ,p_pmrv_rec IN pmrv_rec_type)
545 IS
546
547 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
548
549 BEGIN
550 -- initialize return status
551 x_return_status := OKC_API.G_RET_STS_SUCCESS;
552
553 -- check for data before processing
554 IF (p_pmrv_rec.datatype IS NULL) OR
555 (p_pmrv_rec.datatype = OKC_API.G_MISS_CHAR) THEN
556 OKC_API.SET_MESSAGE(p_app_name => g_app_name
557 ,p_msg_name => g_required_value
558 ,p_token1 => g_col_name_token
559 ,p_token1_value => 'datatype');
560 x_return_status := OKC_API.G_RET_STS_ERROR;
561 RAISE G_EXCEPTION_HALT_VALIDATION;
562 END IF;
563
564 -- verify that data is uppercase
565 IF (p_pmrv_rec.datatype) <> UPPER(p_pmrv_rec.datatype) THEN
566 OKC_API.SET_MESSAGE(p_app_name => g_app_name
567 ,p_msg_name => g_uppercase_required
568 ,p_token1 => g_col_name_token
569 ,p_token1_value => 'datatype');
570
571 -- notify caller of an error
572 x_return_status := OKC_API.G_RET_STS_ERROR;
573 -- halt further validation of this column
574 RAISE G_EXCEPTION_HALT_VALIDATION;
575 END IF;
576
577 -- check for valid data_type
578 IF (UPPER(p_pmrv_rec.datatype) NOT IN ('CHAR','NUMBER','DATE')) THEN
579 OKC_API.SET_MESSAGE(p_app_name => g_app_name
580 ,p_msg_name => g_invalid_value
581 ,p_token1 => g_col_name_token
582 ,p_token1_value => 'datatype');
583 x_return_status := OKC_API.G_RET_STS_ERROR;
584 RAISE G_EXCEPTION_HALT_VALIDATION;
585 END IF;
586
587 EXCEPTION
588 WHEN G_EXCEPTION_HALT_VALIDATION THEN
589 -- no processing neccessary; validation can continue
590 -- with the next column
591 NULL;
592
593 WHEN OTHERS THEN
594 -- store SQL error message on message stack for caller
595 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
596 p_msg_name => g_unexpected_error,
597 p_token1 => g_sqlcode_token,
598 p_token1_value => sqlcode,
599 p_token2 => g_sqlerrm_token,
600 p_token2_value => sqlerrm);
601
602 -- notify caller of an UNEXPECTED error
603 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
604
605 END Validate_Data_Type;
606
607 ---------------------------------------------------------------------------
608 -- PROCEDURE Validate_Customization_level
609 ---------------------------------------------------------------------------
610 -- Start of comments
611 --
612 -- Procedure Name : Validate_Customization_level
613 -- Description :
614 -- Business Rules :
615 -- Parameters :
616 -- Version : 1.0
617 -- End of comments
618 ---------------------------------------------------------------------------
619 PROCEDURE Validate_Customization_level(x_return_status OUT NOCOPY VARCHAR2
620 ,p_pmrv_rec IN pmrv_rec_type)
621 IS
622
623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
624
625 BEGIN
626 -- initialize return status
627 x_return_status := OKC_API.G_RET_STS_SUCCESS;
628
629 -- check for data before processing
630 IF (p_pmrv_rec.Customization_level IS NULL) OR
631 (p_pmrv_rec.Customization_level = OKC_API.G_MISS_CHAR) THEN
632 OKC_API.SET_MESSAGE(p_app_name => g_app_name
633 ,p_msg_name => g_required_value
634 ,p_token1 => g_col_name_token
635 ,p_token1_value => 'Customization_level');
636 x_return_status := OKC_API.G_RET_STS_ERROR;
637 RAISE G_EXCEPTION_HALT_VALIDATION;
638 END IF;
639
640 /*
641 -- verify that data is uppercase
642 IF (p_pmrv_rec.Customization_level) <> UPPER(p_pmrv_rec.Customization_level) THEN
643 OKC_API.SET_MESSAGE(p_app_name => g_app_name
644 ,p_msg_name => g_uppercase_required
645 ,p_token1 => g_col_name_token
646 ,p_token1_value => 'Customization_level');
647
648 -- notify caller of an error
649 x_return_status := OKC_API.G_RET_STS_ERROR;
650 -- halt further validation of this column
651 RAISE G_EXCEPTION_HALT_VALIDATION;
652 END IF;
653
654 -- check for valid data_type
655 IF (UPPER(p_pmrv_rec.Customization_level) NOT IN ('S','U')) THEN
656 OKC_API.SET_MESSAGE(p_app_name => g_app_name
657 ,p_msg_name => g_invalid_value
658 ,p_token1 => g_col_name_token
659 ,p_token1_value => 'Customization_level');
660 x_return_status := OKC_API.G_RET_STS_ERROR;
661 RAISE G_EXCEPTION_HALT_VALIDATION;
662 END IF;
663 */
664 EXCEPTION
665 WHEN G_EXCEPTION_HALT_VALIDATION THEN
666 -- no processing neccessary; validation can continue
667 -- with the next column
668 NULL;
669
670 WHEN OTHERS THEN
671 -- store SQL error message on message stack for caller
672 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
673 p_msg_name => g_unexpected_error,
674 p_token1 => g_sqlcode_token,
675 p_token1_value => sqlcode,
676 p_token2 => g_sqlerrm_token,
677 p_token2_value => sqlerrm);
678
679 -- notify caller of an UNEXPECTED error
680 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
681
682 END Validate_Customization_level;
683
684
685
686 ---------------------------------------------------------------------------
687 -- PROCEDURE Validate_Attributes
688 ---------------------------------------------------------------------------
689 -- Start of comments
690 --
691 -- Procedure Name : Validate_Attributes
692 -- Description :
693 -- Business Rules :
694 -- Parameters :
695 -- Version : 1.0
696 -- End of comments
697 ---------------------------------------------------------------------------
698
699 FUNCTION Validate_Attributes (
700 p_pmrv_rec IN pmrv_rec_type
701 ) RETURN VARCHAR2 IS
702 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
704 BEGIN
705
706 -- Validate Foreign Keys
707 -- INFO: There are no foreign keys for this table.
708
709 -- call each column-level validation
710
711 -- Validate ID
712 IF p_pmrv_rec.id = OKC_API.G_MISS_NUM OR
713 p_pmrv_rec.id IS NULL
714 THEN
715 OKC_API.set_message( G_APP_NAME,
716 G_REQUIRED_VALUE,
717 G_COL_NAME_TOKEN, 'id' );
718 l_return_status := OKC_API.G_RET_STS_ERROR;
719 END IF;
720
721 -- Valid object_version_number
722 IF ( p_pmrv_rec.object_version_number IS NOT NULL ) AND
723 ( p_pmrv_rec.object_version_number <> OKC_API.G_MISS_NUM ) THEN
724 Validate_Object_Version_Number( x_return_status, p_pmrv_rec );
725 IF ( x_return_status <> OKC_API.G_RET_STS_SUCCESS ) THEN
726 IF ( x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR ) THEN
727 -- need to leave
728 l_return_status := x_return_status;
729 RAISE G_EXCEPTION_HALT_VALIDATION;
730 ELSE
731 -- record that there was an error
732 l_return_status := x_return_status;
733 END IF;
734 END IF;
735 END IF;
736
737 -- Valid sfwt_flag
738 IF ( p_pmrv_rec.sfwt_flag IS NOT NULL ) AND
739 ( p_pmrv_rec.sfwt_flag <> OKC_API.G_MISS_CHAR ) THEN
740 Validate_Sfwt_Flag( x_return_status, p_pmrv_rec );
741 IF ( x_return_status <> OKC_API.G_RET_STS_SUCCESS ) THEN
742 IF ( x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR ) THEN
743 -- need to leave
744 l_return_status := x_return_status;
745 RAISE G_EXCEPTION_HALT_VALIDATION;
746 ELSE
747 -- record that there was an error
748 l_return_status := x_return_status;
749 END IF;
750 END IF;
751 END IF;
752
753
754 -- Valid name
755 IF ( p_pmrv_rec.name IS NOT NULL ) AND
756 ( p_pmrv_rec.name <> OKC_API.G_MISS_CHAR ) THEN
757 Validate_Name( x_return_status, p_pmrv_rec );
758 IF ( x_return_status <> OKC_API.G_RET_STS_SUCCESS ) THEN
759 IF ( x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR ) THEN
760 -- need to leave
761 l_return_status := x_return_status;
762 RAISE G_EXCEPTION_HALT_VALIDATION;
763 ELSE
764 -- record that there was an error
765 l_return_status := x_return_status;
766 END IF;
767 END IF;
768 END IF;
769
770 -- Valid datatype
771 IF ( p_pmrv_rec.datatype IS NOT NULL ) AND
772 ( p_pmrv_rec.datatype <> OKC_API.G_MISS_CHAR ) THEN
773 Validate_Data_Type( x_return_status, p_pmrv_rec );
774 IF ( x_return_status <> OKC_API.G_RET_STS_SUCCESS ) THEN
775 IF ( x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR ) THEN
776 -- need to leave
777 l_return_status := x_return_status;
778 RAISE G_EXCEPTION_HALT_VALIDATION;
779 ELSE
780 -- record that there was an error
781 l_return_status := x_return_status;
782 END IF;
783 END IF;
784 END IF;
785
786 -- Valid customization_level
787 IF ( p_pmrv_rec.customization_level IS NOT NULL ) AND
788 ( p_pmrv_rec.customization_level <> OKC_API.G_MISS_CHAR ) THEN
789 Validate_customization_level( x_return_status, p_pmrv_rec );
790 IF ( x_return_status <> OKC_API.G_RET_STS_SUCCESS ) THEN
791 IF ( x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR ) THEN
792 -- need to leave
793 l_return_status := x_return_status;
794 RAISE G_EXCEPTION_HALT_VALIDATION;
795 ELSE
796 -- record that there was an error
797 l_return_status := x_return_status;
798 END IF;
799 END IF;
800 END IF;
801
802 RETURN(l_return_status);
803 EXCEPTION
804 WHEN G_EXCEPTION_HALT_VALIDATION THEN
805 -- just come out with return status
806 NULL;
807 RETURN (l_return_status);
808
809 WHEN OTHERS THEN
810 -- store SQL error message on message stack for caller
811 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
812 p_msg_name => g_unexpected_error,
813 p_token1 => g_sqlcode_token,
814 p_token1_value => sqlcode,
815 p_token2 => g_sqlerrm_token,
816 p_token2_value => sqlerrm);
817 -- notify caller of an UNEXPECTED error
818 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
819 RETURN(l_return_status);
820
821 END Validate_Attributes;
822
823 /** *** SBALASHA001 End *** **/
824
825
826 /** *** SBALASHA002 Start *** -
827 INFO: hand coded function related to validate_record **/
828
829
830 ---------------------------------------------------------------------------
831 -- PROCEDURE Validate_Unique_Pmr_Record
832 ---------------------------------------------------------------------------
833 -- Start of comments
834 --
835 -- Procedure Name : Validate_Unique_Pmr_Record
836 -- Description :
837 -- Business Rules :
838 -- Parameters :
839 -- Version : 1.0
840 -- End of comments
841 ---------------------------------------------------------------------------
842 PROCEDURE Validate_Unique_Pmr_Record(
843 x_return_status OUT NOCOPY VARCHAR2,
844 p_pmrv_rec IN pmrv_rec_type)
845 IS
846
847 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
848 l_unq_tbl OKC_UTIL.unq_tbl_type;
849 l_dummy VARCHAR2(1);
850 l_row_found Boolean := False;
851 CURSOR c1( p_id okl_parameters_v.id%TYPE,
852 p_name okl_parameters_v.name%TYPE ) is
853 SELECT 1
854 FROM okl_parameters_v
855 WHERE id = p_id
856 AND name = p_name
857 AND id <> nvl( p_pmrv_rec.id, -9999 );
858
859 BEGIN
860
861 -- initialize return status
862 x_return_status := OKC_API.G_RET_STS_SUCCESS;
863
864 OPEN c1( p_pmrv_rec.id, p_pmrv_rec.name );
865 FETCH c1 into l_dummy;
866 l_row_found := c1%FOUND;
867 CLOSE c1;
868 /* RPOONUGA001: Changed the token name from G_TABLE_NAME to G_TABLE_TOKEN */
869 IF l_row_found then
870 OKC_API.set_message( G_APP_NAME, G_UNQS, G_TABLE_TOKEN, 'Okl_Parameters_V' );
871 x_return_status := OKC_API.G_RET_STS_ERROR;
872 END IF;
873
874 EXCEPTION
875 WHEN G_EXCEPTION_HALT_VALIDATION THEN
876 -- no processing necessary; validation can continue
877 -- with the next column
878 NULL;
879
880 WHEN OTHERS THEN
881 -- store SQL error message on message stack for caller
882 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
883 p_msg_name => g_unexpected_error,
884 p_token1 => g_sqlcode_token,
885 p_token1_value => sqlcode,
886 p_token2 => g_sqlerrm_token,
887 p_token2_value => sqlerrm );
888
889 -- notify caller of an UNEXPECTED error
890 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
891
892 END Validate_Unique_Pmr_Record;
893
894
895 ---------------------------------------------------------------------------
896 -- FUNCTION Validate_Record
897 ---------------------------------------------------------------------------
898 -- Start of comments
899 --
900 -- Procedure Name : Validate_Record
901 -- Description :
902 -- Business Rules :
903 -- Parameters :
904 -- Version : 1.0
905 -- End of comments
906 ---------------------------------------------------------------------------
907
908 FUNCTION Validate_Record (
909 p_pmrv_rec IN pmrv_rec_type
910 ) RETURN VARCHAR2 IS
911 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
912 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
913 BEGIN
914
915 -- Validate_Unique_Pmr_Record
916 Validate_Unique_Pmr_Record( x_return_status, p_pmrv_rec );
917 -- store the highest degree of error
918 IF ( x_return_status <> OKC_API.G_RET_STS_SUCCESS ) THEN
919 IF ( x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR ) THEN
920 -- need to leave
921 l_return_status := x_return_status;
922 RAISE G_EXCEPTION_HALT_VALIDATION;
923 ELSE
924 -- record that there was an error
925 l_return_status := x_return_status;
926 END IF;
927 END IF;
928 RETURN( l_return_status );
929
930 EXCEPTION
931 WHEN G_EXCEPTION_HALT_VALIDATION THEN
932 -- no processing necessary; validation can continue
933 -- with the next column
934 NULL;
935 RETURN ( l_return_status );
936
937 WHEN OTHERS THEN
938 -- store SQL error message on message stack for caller
939 OKC_API.SET_MESSAGE( p_app_name => g_app_name,
940 p_msg_name => g_unexpected_error,
941 p_token1 => g_sqlcode_token,
942 p_token1_value => sqlcode,
943 p_token2 => g_sqlerrm_token,
944 p_token2_value => sqlerrm );
945
946 -- notify caller of an UNEXPECTED error
947 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
948
949 END Validate_Record;
950
951
952 /** *** SBALASHA002 End *** **/
953
954
955
956
957
958 ---------------------------------------------------------------------------
959 -- PROCEDURE Migrate
960 ---------------------------------------------------------------------------
961 PROCEDURE migrate (
962 p_from IN pmrv_rec_type,
963 p_to IN OUT NOCOPY pmr_rec_type
964 ) IS
965 BEGIN
966 p_to.id := p_from.id;
967 p_to.name := p_from.name;
968 p_to.customization_level := p_from.customization_level;
969 p_to.datatype := p_from.datatype;
970 p_to.object_version_number := p_from.object_version_number;
971 p_to.created_by := p_from.created_by;
972 p_to.creation_date := p_from.creation_date;
973 p_to.last_updated_by := p_from.last_updated_by;
974 p_to.last_update_date := p_from.last_update_date;
975 p_to.last_update_login := p_from.last_update_login;
976 END migrate;
977 PROCEDURE migrate (
978 p_from IN pmr_rec_type,
979 p_to OUT NOCOPY pmrv_rec_type
980 ) IS
981 BEGIN
982 p_to.id := p_from.id;
983 p_to.name := p_from.name;
984 p_to.customization_level := p_from.customization_level;
985 p_to.datatype := p_from.datatype;
986 p_to.object_version_number := p_from.object_version_number;
987 p_to.created_by := p_from.created_by;
988 p_to.creation_date := p_from.creation_date;
989 p_to.last_updated_by := p_from.last_updated_by;
990 p_to.last_update_date := p_from.last_update_date;
991 p_to.last_update_login := p_from.last_update_login;
992 END migrate;
993 PROCEDURE migrate (
994 p_from IN pmrv_rec_type,
995 p_to OUT NOCOPY okl_parameters_tl_rec_type
996 ) IS
997 BEGIN
998 p_to.id := p_from.id;
999 p_to.sfwt_flag := p_from.sfwt_flag;
1000 p_to.description := p_from.description;
1001 p_to.created_by := p_from.created_by;
1002 p_to.creation_date := p_from.creation_date;
1003 p_to.last_updated_by := p_from.last_updated_by;
1004 p_to.last_update_date := p_from.last_update_date;
1005 p_to.last_update_login := p_from.last_update_login;
1006 END migrate;
1007 PROCEDURE migrate (
1008 p_from IN okl_parameters_tl_rec_type,
1009 p_to OUT NOCOPY pmrv_rec_type
1010 ) IS
1011 BEGIN
1012 p_to.id := p_from.id;
1013 p_to.sfwt_flag := p_from.sfwt_flag;
1014 p_to.description := p_from.description;
1015 p_to.created_by := p_from.created_by;
1016 p_to.creation_date := p_from.creation_date;
1017 p_to.last_updated_by := p_from.last_updated_by;
1018 p_to.last_update_date := p_from.last_update_date;
1019 p_to.last_update_login := p_from.last_update_login;
1020 END migrate;
1021
1022 ---------------------------------------------------------------------------
1023 -- PROCEDURE validate_row
1024 ---------------------------------------------------------------------------
1025 ---------------------------------------
1026 -- validate_row for:OKL_PARAMETERS_V --
1027 ---------------------------------------
1028 PROCEDURE validate_row(
1029 p_api_version IN NUMBER,
1030 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2,
1034 p_pmrv_rec IN pmrv_rec_type) IS
1035
1036 l_api_version CONSTANT NUMBER := 1;
1037 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1038 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1039 l_pmrv_rec pmrv_rec_type := p_pmrv_rec;
1040 l_pmr_rec pmr_rec_type;
1041 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
1042 BEGIN
1043 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1044 G_PKG_NAME,
1045 p_init_msg_list,
1046 l_api_version,
1047 p_api_version,
1048 '_PVT',
1049 x_return_status);
1050 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1051 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1052 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1053 RAISE OKC_API.G_EXCEPTION_ERROR;
1054 END IF;
1055 --- Validate all non-missing attributes (Item Level Validation)
1056 l_return_status := Validate_Attributes(l_pmrv_rec);
1057 --- If any errors happen abort API
1058 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1059 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1060 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1061 RAISE OKC_API.G_EXCEPTION_ERROR;
1062 END IF;
1063 l_return_status := Validate_Record(l_pmrv_rec);
1064 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1066 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1067 RAISE OKC_API.G_EXCEPTION_ERROR;
1068 END IF;
1069 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1070 EXCEPTION
1071 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1072 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1073 (
1074 l_api_name,
1075 G_PKG_NAME,
1076 'OKC_API.G_RET_STS_ERROR',
1077 x_msg_count,
1078 x_msg_data,
1079 '_PVT'
1080 );
1081 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1082 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1083 (
1084 l_api_name,
1085 G_PKG_NAME,
1086 'OKC_API.G_RET_STS_UNEXP_ERROR',
1087 x_msg_count,
1088 x_msg_data,
1089 '_PVT'
1090 );
1091 WHEN OTHERS THEN
1092 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1093 (
1094 l_api_name,
1095 G_PKG_NAME,
1096 'OTHERS',
1097 x_msg_count,
1098 x_msg_data,
1099 '_PVT'
1100 );
1101 END validate_row;
1102 ------------------------------------------
1103 -- PL/SQL TBL validate_row for:PMRV_TBL --
1104 ------------------------------------------
1105 PROCEDURE validate_row(
1106 p_api_version IN NUMBER,
1107 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1108 x_return_status OUT NOCOPY VARCHAR2,
1109 x_msg_count OUT NOCOPY NUMBER,
1110 x_msg_data OUT NOCOPY VARCHAR2,
1111 p_pmrv_tbl IN pmrv_tbl_type) IS
1112
1113 l_api_version CONSTANT NUMBER := 1;
1114 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1115 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1116 i NUMBER := 0;
1117 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1118
1119 BEGIN
1120 OKC_API.init_msg_list(p_init_msg_list);
1121 -- Make sure PL/SQL table has records in it before passing
1122 IF (p_pmrv_tbl.COUNT > 0) THEN
1123 i := p_pmrv_tbl.FIRST;
1124 LOOP
1125 validate_row (
1126 p_api_version => p_api_version,
1127 p_init_msg_list => OKC_API.G_FALSE,
1128 x_return_status => x_return_status,
1129 x_msg_count => x_msg_count,
1130 x_msg_data => x_msg_data,
1131 p_pmrv_rec => p_pmrv_tbl(i));
1132 -- store the highest degree of error
1133 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1134 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1135 l_overall_status := x_return_status;
1136 END IF;
1137 END IF;
1138
1139 EXIT WHEN (i = p_pmrv_tbl.LAST);
1140 i := p_pmrv_tbl.NEXT(i);
1141 END LOOP;
1142 -- return overall status
1143 x_return_status := l_overall_status;
1144
1145 END IF;
1146 EXCEPTION
1147 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1148 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1149 (
1150 l_api_name,
1151 G_PKG_NAME,
1152 'OKC_API.G_RET_STS_ERROR',
1153 x_msg_count,
1154 x_msg_data,
1155 '_PVT'
1156 );
1157 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1158 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1159 (
1160 l_api_name,
1161 G_PKG_NAME,
1162 'OKC_API.G_RET_STS_UNEXP_ERROR',
1163 x_msg_count,
1164 x_msg_data,
1165 '_PVT'
1166 );
1167 WHEN OTHERS THEN
1168 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1169 (
1170 l_api_name,
1171 G_PKG_NAME,
1172 'OTHERS',
1173 x_msg_count,
1174 x_msg_data,
1175 '_PVT'
1176 );
1177 END validate_row;
1178
1179 ---------------------------------------------------------------------------
1180 -- PROCEDURE insert_row
1181 ---------------------------------------------------------------------------
1182 -------------------------------------
1183 -- insert_row for:OKL_PARAMETERS_B --
1184 -------------------------------------
1185 PROCEDURE insert_row(
1186 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1187 x_return_status OUT NOCOPY VARCHAR2,
1188 x_msg_count OUT NOCOPY NUMBER,
1189 x_msg_data OUT NOCOPY VARCHAR2,
1190 p_pmr_rec IN pmr_rec_type,
1191 x_pmr_rec OUT NOCOPY pmr_rec_type) IS
1192
1193 l_api_version CONSTANT NUMBER := 1;
1194 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1195 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1196 l_pmr_rec pmr_rec_type := p_pmr_rec;
1197 l_def_pmr_rec pmr_rec_type;
1198 -----------------------------------------
1199 -- Set_Attributes for:OKL_PARAMETERS_B --
1200 -----------------------------------------
1201 FUNCTION Set_Attributes (
1202 p_pmr_rec IN pmr_rec_type,
1203 x_pmr_rec OUT NOCOPY pmr_rec_type
1204 ) RETURN VARCHAR2 IS
1205 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1206 BEGIN
1207 x_pmr_rec := p_pmr_rec;
1208 RETURN(l_return_status);
1209 END Set_Attributes;
1210 BEGIN
1211 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1212 p_init_msg_list,
1213 '_PVT',
1214 x_return_status);
1215 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1216 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1217 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1218 RAISE OKC_API.G_EXCEPTION_ERROR;
1219 END IF;
1220 --- Setting item attributes
1221 l_return_status := Set_Attributes(
1222 p_pmr_rec, -- IN
1223 l_pmr_rec); -- OUT
1224 --- If any errors happen abort API
1225 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1228 RAISE OKC_API.G_EXCEPTION_ERROR;
1229 END IF;
1230 INSERT INTO OKL_PARAMETERS_B(
1231 id,
1232 name,
1233 datatype,
1234 object_version_number,
1235 customization_level,
1236 created_by,
1237 creation_date,
1238 last_updated_by,
1239 last_update_date,
1240 last_update_login)
1241 VALUES (
1242 l_pmr_rec.id,
1243 l_pmr_rec.name,
1244 l_pmr_rec.datatype,
1245 l_pmr_rec.object_version_number,
1246 l_pmr_rec.customization_level,
1247 l_pmr_rec.created_by,
1248 l_pmr_rec.creation_date,
1249 l_pmr_rec.last_updated_by,
1250 l_pmr_rec.last_update_date,
1251 l_pmr_rec.last_update_login);
1252 -- Set OUT values
1253 x_pmr_rec := l_pmr_rec;
1254 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1255 EXCEPTION
1256 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1257 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1258 (
1259 l_api_name,
1260 G_PKG_NAME,
1261 'OKC_API.G_RET_STS_ERROR',
1262 x_msg_count,
1263 x_msg_data,
1264 '_PVT'
1265 );
1266 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1267 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1268 (
1269 l_api_name,
1270 G_PKG_NAME,
1271 'OKC_API.G_RET_STS_UNEXP_ERROR',
1272 x_msg_count,
1273 x_msg_data,
1274 '_PVT'
1275 );
1276 WHEN OTHERS THEN
1277 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1278 (
1279 l_api_name,
1280 G_PKG_NAME,
1281 'OTHERS',
1282 x_msg_count,
1283 x_msg_data,
1284 '_PVT'
1285 );
1286 END insert_row;
1287 --------------------------------------
1288 -- insert_row for:OKL_PARAMETERS_TL --
1289 --------------------------------------
1290 PROCEDURE insert_row(
1291 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2,
1295 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
1296 x_okl_parameters_tl_rec OUT NOCOPY okl_parameters_tl_rec_type) IS
1297
1298 l_api_version CONSTANT NUMBER := 1;
1299 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1300 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1301 l_okl_parameters_tl_rec okl_parameters_tl_rec_type := p_okl_parameters_tl_rec;
1302 l_def_okl_parameters_tl_rec okl_parameters_tl_rec_type;
1303 CURSOR get_languages IS
1304 SELECT *
1305 FROM FND_LANGUAGES
1306 WHERE INSTALLED_FLAG IN ('I', 'B');
1307 ------------------------------------------
1308 -- Set_Attributes for:OKL_PARAMETERS_TL --
1309 ------------------------------------------
1310 FUNCTION Set_Attributes (
1311 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
1312 x_okl_parameters_tl_rec OUT NOCOPY okl_parameters_tl_rec_type
1313 ) RETURN VARCHAR2 IS
1314 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1315 BEGIN
1316 x_okl_parameters_tl_rec := p_okl_parameters_tl_rec;
1317 x_okl_parameters_tl_rec.LANGUAGE := USERENV('LANG');
1318 x_okl_parameters_tl_rec.SOURCE_LANG := USERENV('LANG');
1319 RETURN(l_return_status);
1320 END Set_Attributes;
1321 BEGIN
1322 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1323 p_init_msg_list,
1324 '_PVT',
1325 x_return_status);
1326 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1327 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1328 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1329 RAISE OKC_API.G_EXCEPTION_ERROR;
1330 END IF;
1331 --- Setting item attributes
1332 l_return_status := Set_Attributes(
1333 p_okl_parameters_tl_rec, -- IN
1334 l_okl_parameters_tl_rec); -- OUT
1335 --- If any errors happen abort API
1336 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1337 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1338 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1339 RAISE OKC_API.G_EXCEPTION_ERROR;
1340 END IF;
1341 FOR l_lang_rec IN get_languages LOOP
1342 l_okl_parameters_tl_rec.language := l_lang_rec.language_code;
1343 INSERT INTO OKL_PARAMETERS_TL(
1344 id,
1345 language,
1346 source_lang,
1347 sfwt_flag,
1348 description,
1349 created_by,
1350 creation_date,
1351 last_updated_by,
1352 last_update_date,
1353 last_update_login)
1354 VALUES (
1355 l_okl_parameters_tl_rec.id,
1356 l_okl_parameters_tl_rec.language,
1357 l_okl_parameters_tl_rec.source_lang,
1358 l_okl_parameters_tl_rec.sfwt_flag,
1359 l_okl_parameters_tl_rec.description,
1360 l_okl_parameters_tl_rec.created_by,
1361 l_okl_parameters_tl_rec.creation_date,
1362 l_okl_parameters_tl_rec.last_updated_by,
1363 l_okl_parameters_tl_rec.last_update_date,
1364 l_okl_parameters_tl_rec.last_update_login);
1365 END LOOP;
1366 -- Set OUT values
1367 x_okl_parameters_tl_rec := l_okl_parameters_tl_rec;
1368 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1369 EXCEPTION
1370 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1371 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1372 (
1373 l_api_name,
1374 G_PKG_NAME,
1375 'OKC_API.G_RET_STS_ERROR',
1376 x_msg_count,
1377 x_msg_data,
1378 '_PVT'
1379 );
1380 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1381 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1382 (
1383 l_api_name,
1384 G_PKG_NAME,
1385 'OKC_API.G_RET_STS_UNEXP_ERROR',
1386 x_msg_count,
1387 x_msg_data,
1388 '_PVT'
1389 );
1390 WHEN OTHERS THEN
1391 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1392 (
1393 l_api_name,
1394 G_PKG_NAME,
1395 'OTHERS',
1396 x_msg_count,
1397 x_msg_data,
1398 '_PVT'
1399 );
1400 END insert_row;
1401 -------------------------------------
1402 -- insert_row for:OKL_PARAMETERS_V --
1403 -------------------------------------
1404 PROCEDURE insert_row(
1405 p_api_version IN NUMBER,
1406 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1407 x_return_status OUT NOCOPY VARCHAR2,
1408 x_msg_count OUT NOCOPY NUMBER,
1409 x_msg_data OUT NOCOPY VARCHAR2,
1410 p_pmrv_rec IN pmrv_rec_type,
1411 x_pmrv_rec OUT NOCOPY pmrv_rec_type) IS
1412
1413 l_api_version CONSTANT NUMBER := 1;
1414 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1415 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1416 l_pmrv_rec pmrv_rec_type;
1417 l_def_pmrv_rec pmrv_rec_type;
1418 l_pmr_rec pmr_rec_type;
1419 lx_pmr_rec pmr_rec_type;
1420 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
1421 lx_okl_parameters_tl_rec okl_parameters_tl_rec_type;
1422 -------------------------------
1423 -- FUNCTION fill_who_columns --
1424 -------------------------------
1425 FUNCTION fill_who_columns (
1426 p_pmrv_rec IN pmrv_rec_type
1427 ) RETURN pmrv_rec_type IS
1428 l_pmrv_rec pmrv_rec_type := p_pmrv_rec;
1429 BEGIN
1430 l_pmrv_rec.CREATION_DATE := SYSDATE;
1431 l_pmrv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1432 l_pmrv_rec.LAST_UPDATE_DATE := l_pmrv_rec.CREATION_DATE;
1433 l_pmrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1434 l_pmrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1435 RETURN(l_pmrv_rec);
1436 END fill_who_columns;
1437 -----------------------------------------
1438 -- Set_Attributes for:OKL_PARAMETERS_V --
1439 -----------------------------------------
1440 FUNCTION Set_Attributes (
1441 p_pmrv_rec IN pmrv_rec_type,
1442 x_pmrv_rec OUT NOCOPY pmrv_rec_type
1443 ) RETURN VARCHAR2 IS
1444 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1445 BEGIN
1446 x_pmrv_rec := p_pmrv_rec;
1447 x_pmrv_rec.OBJECT_VERSION_NUMBER := 1;
1448 x_pmrv_rec.SFWT_FLAG := 'N';
1449 RETURN(l_return_status);
1450 END Set_Attributes;
1451 BEGIN
1452 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1453 G_PKG_NAME,
1454 p_init_msg_list,
1455 l_api_version,
1456 p_api_version,
1457 '_PVT',
1458 x_return_status);
1459 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1460 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1461 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1462 RAISE OKC_API.G_EXCEPTION_ERROR;
1463 END IF;
1464 l_pmrv_rec := null_out_defaults(p_pmrv_rec);
1465 -- Set primary key value
1466 l_pmrv_rec.ID := get_seq_id;
1467 --- Setting item attributes
1468 l_return_status := Set_Attributes(
1469 l_pmrv_rec, -- IN
1470 l_def_pmrv_rec); -- OUT
1471 --- If any errors happen abort API
1472 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1473 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1474 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1475 RAISE OKC_API.G_EXCEPTION_ERROR;
1476 END IF;
1477 l_def_pmrv_rec := fill_who_columns(l_def_pmrv_rec);
1478 --- Validate all non-missing attributes (Item Level Validation)
1479 l_return_status := Validate_Attributes(l_def_pmrv_rec);
1480 --- If any errors happen abort API
1481 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_ERROR;
1485 END IF;
1486 l_return_status := Validate_Record(l_def_pmrv_rec);
1487 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1488 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1489 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1490 RAISE OKC_API.G_EXCEPTION_ERROR;
1491 END IF;
1492 --------------------------------------
1493 -- Move VIEW record to "Child" records
1494 --------------------------------------
1495 migrate(l_def_pmrv_rec, l_pmr_rec);
1496 migrate(l_def_pmrv_rec, l_okl_parameters_tl_rec);
1497 --------------------------------------------
1498 -- Call the INSERT_ROW for each child record
1499 --------------------------------------------
1500 insert_row(
1501 p_init_msg_list,
1502 x_return_status,
1503 x_msg_count,
1504 x_msg_data,
1505 l_pmr_rec,
1506 lx_pmr_rec
1507 );
1508 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1509 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1510 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1511 RAISE OKC_API.G_EXCEPTION_ERROR;
1512 END IF;
1513 migrate(lx_pmr_rec, l_def_pmrv_rec);
1514 insert_row(
1515 p_init_msg_list,
1516 x_return_status,
1517 x_msg_count,
1518 x_msg_data,
1519 l_okl_parameters_tl_rec,
1520 lx_okl_parameters_tl_rec
1521 );
1522 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1523 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1524 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1525 RAISE OKC_API.G_EXCEPTION_ERROR;
1526 END IF;
1527 migrate(lx_okl_parameters_tl_rec, l_def_pmrv_rec);
1528 -- Set OUT values
1529 x_pmrv_rec := l_def_pmrv_rec;
1530 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1531 EXCEPTION
1532 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1533 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1534 (
1535 l_api_name,
1536 G_PKG_NAME,
1537 'OKC_API.G_RET_STS_ERROR',
1538 x_msg_count,
1539 x_msg_data,
1540 '_PVT'
1541 );
1542 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1543 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1544 (
1545 l_api_name,
1546 G_PKG_NAME,
1547 'OKC_API.G_RET_STS_UNEXP_ERROR',
1548 x_msg_count,
1549 x_msg_data,
1550 '_PVT'
1551 );
1552 WHEN OTHERS THEN
1553 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1554 (
1555 l_api_name,
1556 G_PKG_NAME,
1557 'OTHERS',
1558 x_msg_count,
1559 x_msg_data,
1560 '_PVT'
1561 );
1562 END insert_row;
1563 ----------------------------------------
1564 -- PL/SQL TBL insert_row for:PMRV_TBL --
1565 ----------------------------------------
1566 PROCEDURE insert_row(
1567 p_api_version IN NUMBER,
1568 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1569 x_return_status OUT NOCOPY VARCHAR2,
1570 x_msg_count OUT NOCOPY NUMBER,
1571 x_msg_data OUT NOCOPY VARCHAR2,
1572 p_pmrv_tbl IN pmrv_tbl_type,
1573 x_pmrv_tbl OUT NOCOPY pmrv_tbl_type) IS
1574
1575 l_api_version CONSTANT NUMBER := 1;
1576 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1577 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1578 i NUMBER := 0;
1579 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1580
1581 BEGIN
1582 OKC_API.init_msg_list(p_init_msg_list);
1583 -- Make sure PL/SQL table has records in it before passing
1584 IF (p_pmrv_tbl.COUNT > 0) THEN
1585 i := p_pmrv_tbl.FIRST;
1586 LOOP
1587 insert_row (
1588 p_api_version => p_api_version,
1589 p_init_msg_list => OKC_API.G_FALSE,
1590 x_return_status => x_return_status,
1591 x_msg_count => x_msg_count,
1592 x_msg_data => x_msg_data,
1593 p_pmrv_rec => p_pmrv_tbl(i),
1594 x_pmrv_rec => x_pmrv_tbl(i));
1595 -- store the highest degree of error
1596 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1597 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1598 l_overall_status := x_return_status;
1599 END IF;
1600 END IF;
1601
1602 EXIT WHEN (i = p_pmrv_tbl.LAST);
1603 i := p_pmrv_tbl.NEXT(i);
1604 END LOOP;
1605 -- return overall status
1606 x_return_status := l_overall_status;
1607
1608 END IF;
1609 EXCEPTION
1610 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1611 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1612 (
1613 l_api_name,
1614 G_PKG_NAME,
1615 'OKC_API.G_RET_STS_ERROR',
1616 x_msg_count,
1617 x_msg_data,
1618 '_PVT'
1619 );
1620 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1621 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1622 (
1623 l_api_name,
1624 G_PKG_NAME,
1625 'OKC_API.G_RET_STS_UNEXP_ERROR',
1626 x_msg_count,
1627 x_msg_data,
1628 '_PVT'
1629 );
1630 WHEN OTHERS THEN
1631 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1632 (
1633 l_api_name,
1634 G_PKG_NAME,
1635 'OTHERS',
1636 x_msg_count,
1637 x_msg_data,
1638 '_PVT'
1639 );
1640 END insert_row;
1641
1642 ---------------------------------------------------------------------------
1643 -- PROCEDURE lock_row
1644 ---------------------------------------------------------------------------
1645 -----------------------------------
1646 -- lock_row for:OKL_PARAMETERS_B --
1647 -----------------------------------
1648 PROCEDURE lock_row(
1649 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1650 x_return_status OUT NOCOPY VARCHAR2,
1651 x_msg_count OUT NOCOPY NUMBER,
1652 x_msg_data OUT NOCOPY VARCHAR2,
1653 p_pmr_rec IN pmr_rec_type) IS
1654
1655 E_Resource_Busy EXCEPTION;
1656 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1657 CURSOR lock_csr (p_pmr_rec IN pmr_rec_type) IS
1658 SELECT OBJECT_VERSION_NUMBER
1659 FROM OKL_PARAMETERS_B
1660 WHERE ID = p_pmr_rec.id
1661 AND OBJECT_VERSION_NUMBER = p_pmr_rec.object_version_number
1662 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1663
1664 CURSOR lchk_csr (p_pmr_rec IN pmr_rec_type) IS
1665 SELECT OBJECT_VERSION_NUMBER
1666 FROM OKL_PARAMETERS_B
1667 WHERE ID = p_pmr_rec.id;
1668 l_api_version CONSTANT NUMBER := 1;
1669 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1670 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1671 l_object_version_number OKL_PARAMETERS_B.OBJECT_VERSION_NUMBER%TYPE;
1672 lc_object_version_number OKL_PARAMETERS_B.OBJECT_VERSION_NUMBER%TYPE;
1673 l_row_notfound BOOLEAN := FALSE;
1674 lc_row_notfound BOOLEAN := FALSE;
1675 BEGIN
1676 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1677 p_init_msg_list,
1678 '_PVT',
1679 x_return_status);
1680 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1681 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1682 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1683 RAISE OKC_API.G_EXCEPTION_ERROR;
1684 END IF;
1685 BEGIN
1686 OPEN lock_csr(p_pmr_rec);
1687 FETCH lock_csr INTO l_object_version_number;
1688 l_row_notfound := lock_csr%NOTFOUND;
1689 CLOSE lock_csr;
1690 EXCEPTION
1691 WHEN E_Resource_Busy THEN
1692 IF (lock_csr%ISOPEN) THEN
1693 CLOSE lock_csr;
1694 END IF;
1695 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1696 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1697 END;
1698
1699 IF ( l_row_notfound ) THEN
1700 OPEN lchk_csr(p_pmr_rec);
1701 FETCH lchk_csr INTO lc_object_version_number;
1702 lc_row_notfound := lchk_csr%NOTFOUND;
1703 CLOSE lchk_csr;
1704 END IF;
1705 IF (lc_row_notfound) THEN
1706 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1707 RAISE OKC_API.G_EXCEPTION_ERROR;
1708 ELSIF lc_object_version_number > p_pmr_rec.object_version_number THEN
1709 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1710 RAISE OKC_API.G_EXCEPTION_ERROR;
1711 ELSIF lc_object_version_number <> p_pmr_rec.object_version_number THEN
1712 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1713 RAISE OKC_API.G_EXCEPTION_ERROR;
1714 ELSIF lc_object_version_number = -1 THEN
1715 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1716 RAISE OKC_API.G_EXCEPTION_ERROR;
1717 END IF;
1718 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1719 EXCEPTION
1720 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1721 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1722 (
1723 l_api_name,
1724 G_PKG_NAME,
1725 'OKC_API.G_RET_STS_ERROR',
1726 x_msg_count,
1727 x_msg_data,
1728 '_PVT'
1729 );
1730 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1731 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1732 (
1733 l_api_name,
1734 G_PKG_NAME,
1735 'OKC_API.G_RET_STS_UNEXP_ERROR',
1736 x_msg_count,
1737 x_msg_data,
1738 '_PVT'
1739 );
1740 WHEN OTHERS THEN
1741 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1742 (
1743 l_api_name,
1744 G_PKG_NAME,
1745 'OTHERS',
1746 x_msg_count,
1747 x_msg_data,
1748 '_PVT'
1749 );
1750 END lock_row;
1751 ------------------------------------
1752 -- lock_row for:OKL_PARAMETERS_TL --
1753 ------------------------------------
1754 PROCEDURE lock_row(
1755 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1756 x_return_status OUT NOCOPY VARCHAR2,
1757 x_msg_count OUT NOCOPY NUMBER,
1758 x_msg_data OUT NOCOPY VARCHAR2,
1759 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type) IS
1760
1761 E_Resource_Busy EXCEPTION;
1762 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1763 CURSOR lock_csr (p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type) IS
1764 SELECT *
1765 FROM OKL_PARAMETERS_TL
1766 WHERE ID = p_okl_parameters_tl_rec.id
1767 FOR UPDATE NOWAIT;
1768
1769 l_api_version CONSTANT NUMBER := 1;
1770 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1771 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1772 l_lock_var lock_csr%ROWTYPE;
1773 l_row_notfound BOOLEAN := FALSE;
1774 lc_row_notfound BOOLEAN := FALSE;
1775 BEGIN
1776 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1777 p_init_msg_list,
1778 '_PVT',
1779 x_return_status);
1780 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1781 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1782 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1783 RAISE OKC_API.G_EXCEPTION_ERROR;
1784 END IF;
1785 BEGIN
1786 OPEN lock_csr(p_okl_parameters_tl_rec);
1787 FETCH lock_csr INTO l_lock_var;
1788 l_row_notfound := lock_csr%NOTFOUND;
1789 CLOSE lock_csr;
1790 EXCEPTION
1791 WHEN E_Resource_Busy THEN
1792 IF (lock_csr%ISOPEN) THEN
1793 CLOSE lock_csr;
1794 END IF;
1795 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1796 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1797 END;
1798
1799 IF ( l_row_notfound ) THEN
1800 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1801 RAISE OKC_API.G_EXCEPTION_ERROR;
1802 END IF;
1803 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1804 EXCEPTION
1805 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1806 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1807 (
1808 l_api_name,
1809 G_PKG_NAME,
1810 'OKC_API.G_RET_STS_ERROR',
1811 x_msg_count,
1812 x_msg_data,
1813 '_PVT'
1814 );
1815 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1816 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1817 (
1818 l_api_name,
1819 G_PKG_NAME,
1820 'OKC_API.G_RET_STS_UNEXP_ERROR',
1821 x_msg_count,
1822 x_msg_data,
1823 '_PVT'
1824 );
1825 WHEN OTHERS THEN
1826 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1827 (
1828 l_api_name,
1829 G_PKG_NAME,
1830 'OTHERS',
1831 x_msg_count,
1832 x_msg_data,
1833 '_PVT'
1834 );
1835 END lock_row;
1836 -----------------------------------
1837 -- lock_row for:OKL_PARAMETERS_V --
1838 -----------------------------------
1839 PROCEDURE lock_row(
1840 p_api_version IN NUMBER,
1841 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1842 x_return_status OUT NOCOPY VARCHAR2,
1843 x_msg_count OUT NOCOPY NUMBER,
1844 x_msg_data OUT NOCOPY VARCHAR2,
1845 p_pmrv_rec IN pmrv_rec_type) IS
1846
1847 l_api_version CONSTANT NUMBER := 1;
1848 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1849 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1850 l_pmr_rec pmr_rec_type;
1851 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
1852 BEGIN
1853 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1854 G_PKG_NAME,
1855 p_init_msg_list,
1856 l_api_version,
1857 p_api_version,
1858 '_PVT',
1859 x_return_status);
1860 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1861 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1862 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1863 RAISE OKC_API.G_EXCEPTION_ERROR;
1864 END IF;
1865 --------------------------------------
1866 -- Move VIEW record to "Child" records
1867 --------------------------------------
1868 migrate(p_pmrv_rec, l_pmr_rec);
1869 migrate(p_pmrv_rec, l_okl_parameters_tl_rec);
1870 --------------------------------------------
1871 -- Call the LOCK_ROW for each child record
1872 --------------------------------------------
1873 lock_row(
1874 p_init_msg_list,
1875 x_return_status,
1876 x_msg_count,
1877 x_msg_data,
1878 l_pmr_rec
1879 );
1880 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1881 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1882 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1883 RAISE OKC_API.G_EXCEPTION_ERROR;
1884 END IF;
1885 lock_row(
1886 p_init_msg_list,
1887 x_return_status,
1888 x_msg_count,
1889 x_msg_data,
1890 l_okl_parameters_tl_rec
1891 );
1892 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1893 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1894 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1895 RAISE OKC_API.G_EXCEPTION_ERROR;
1896 END IF;
1897 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1898 EXCEPTION
1899 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1900 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1901 (
1902 l_api_name,
1903 G_PKG_NAME,
1904 'OKC_API.G_RET_STS_ERROR',
1905 x_msg_count,
1906 x_msg_data,
1907 '_PVT'
1908 );
1909 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1910 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1911 (
1912 l_api_name,
1913 G_PKG_NAME,
1914 'OKC_API.G_RET_STS_UNEXP_ERROR',
1915 x_msg_count,
1916 x_msg_data,
1917 '_PVT'
1918 );
1919 WHEN OTHERS THEN
1920 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1921 (
1922 l_api_name,
1923 G_PKG_NAME,
1924 'OTHERS',
1925 x_msg_count,
1926 x_msg_data,
1927 '_PVT'
1928 );
1929 END lock_row;
1930 --------------------------------------
1931 -- PL/SQL TBL lock_row for:PMRV_TBL --
1932 --------------------------------------
1933 PROCEDURE lock_row(
1934 p_api_version IN NUMBER,
1935 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1936 x_return_status OUT NOCOPY VARCHAR2,
1937 x_msg_count OUT NOCOPY NUMBER,
1938 x_msg_data OUT NOCOPY VARCHAR2,
1939 p_pmrv_tbl IN pmrv_tbl_type) IS
1940
1941 l_api_version CONSTANT NUMBER := 1;
1942 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1943 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1944 i NUMBER := 0;
1945 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1946
1947 BEGIN
1948 OKC_API.init_msg_list(p_init_msg_list);
1949 -- Make sure PL/SQL table has records in it before passing
1950 IF (p_pmrv_tbl.COUNT > 0) THEN
1951 i := p_pmrv_tbl.FIRST;
1952 LOOP
1953 lock_row (
1954 p_api_version => p_api_version,
1955 p_init_msg_list => OKC_API.G_FALSE,
1956 x_return_status => x_return_status,
1957 x_msg_count => x_msg_count,
1958 x_msg_data => x_msg_data,
1959 p_pmrv_rec => p_pmrv_tbl(i));
1960 -- store the highest degree of error
1961 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1962 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1963 l_overall_status := x_return_status;
1964 END IF;
1965 END IF;
1966
1967 EXIT WHEN (i = p_pmrv_tbl.LAST);
1968 i := p_pmrv_tbl.NEXT(i);
1969 END LOOP;
1970 -- return overall status
1971 x_return_status := l_overall_status;
1972
1973 END IF;
1974 EXCEPTION
1975 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1976 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1977 (
1978 l_api_name,
1979 G_PKG_NAME,
1980 'OKC_API.G_RET_STS_ERROR',
1981 x_msg_count,
1982 x_msg_data,
1983 '_PVT'
1984 );
1985 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1986 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1987 (
1988 l_api_name,
1989 G_PKG_NAME,
1990 'OKC_API.G_RET_STS_UNEXP_ERROR',
1991 x_msg_count,
1992 x_msg_data,
1993 '_PVT'
1994 );
1995 WHEN OTHERS THEN
1996 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1997 (
1998 l_api_name,
1999 G_PKG_NAME,
2000 'OTHERS',
2001 x_msg_count,
2002 x_msg_data,
2003 '_PVT'
2004 );
2005 END lock_row;
2006
2007 ---------------------------------------------------------------------------
2008 -- PROCEDURE update_row
2009 ---------------------------------------------------------------------------
2010 -------------------------------------
2011 -- update_row for:OKL_PARAMETERS_B --
2012 -------------------------------------
2013 PROCEDURE update_row(
2014 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2015 x_return_status OUT NOCOPY VARCHAR2,
2016 x_msg_count OUT NOCOPY NUMBER,
2017 x_msg_data OUT NOCOPY VARCHAR2,
2018 p_pmr_rec IN pmr_rec_type,
2019 x_pmr_rec OUT NOCOPY pmr_rec_type) IS
2020
2021 l_api_version CONSTANT NUMBER := 1;
2022 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2023 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2024 l_pmr_rec pmr_rec_type := p_pmr_rec;
2025 l_def_pmr_rec pmr_rec_type;
2026 l_row_notfound BOOLEAN := TRUE;
2027 ----------------------------------
2028 -- FUNCTION populate_new_record --
2029 ----------------------------------
2030 FUNCTION populate_new_record (
2031 p_pmr_rec IN pmr_rec_type,
2032 x_pmr_rec OUT NOCOPY pmr_rec_type
2033 ) RETURN VARCHAR2 IS
2034 l_pmr_rec pmr_rec_type;
2035 l_row_notfound BOOLEAN := TRUE;
2036 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2037 BEGIN
2038 x_pmr_rec := p_pmr_rec;
2039 -- Get current database values
2040 l_pmr_rec := get_rec(p_pmr_rec, l_row_notfound);
2041 IF (l_row_notfound) THEN
2042 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2043 END IF;
2044 IF (x_pmr_rec.id = OKC_API.G_MISS_NUM)
2045 THEN
2046 x_pmr_rec.id := l_pmr_rec.id;
2047 END IF;
2048 IF (x_pmr_rec.name = OKC_API.G_MISS_CHAR)
2049 THEN
2050 x_pmr_rec.name := l_pmr_rec.name;
2051 END IF;
2052 IF (x_pmr_rec.datatype = OKC_API.G_MISS_CHAR)
2053 THEN
2054 x_pmr_rec.datatype := l_pmr_rec.datatype;
2055 END IF;
2056 IF (x_pmr_rec.customization_level = OKC_API.G_MISS_CHAR)
2057 THEN
2058 x_pmr_rec.customization_level := l_pmr_rec.customization_level;
2059 END IF;
2060 IF (x_pmr_rec.object_version_number = OKC_API.G_MISS_NUM)
2061 THEN
2062 x_pmr_rec.object_version_number := l_pmr_rec.object_version_number;
2063 END IF;
2064 IF (x_pmr_rec.created_by = OKC_API.G_MISS_NUM)
2065 THEN
2066 x_pmr_rec.created_by := l_pmr_rec.created_by;
2067 END IF;
2068 IF (x_pmr_rec.creation_date = OKC_API.G_MISS_DATE)
2069 THEN
2070 x_pmr_rec.creation_date := l_pmr_rec.creation_date;
2071 END IF;
2072 IF (x_pmr_rec.last_updated_by = OKC_API.G_MISS_NUM)
2073 THEN
2074 x_pmr_rec.last_updated_by := l_pmr_rec.last_updated_by;
2075 END IF;
2076 IF (x_pmr_rec.last_update_date = OKC_API.G_MISS_DATE)
2077 THEN
2078 x_pmr_rec.last_update_date := l_pmr_rec.last_update_date;
2079 END IF;
2080 IF (x_pmr_rec.last_update_login = OKC_API.G_MISS_NUM)
2081 THEN
2082 x_pmr_rec.last_update_login := l_pmr_rec.last_update_login;
2083 END IF;
2084 RETURN(l_return_status);
2085 END populate_new_record;
2086 -----------------------------------------
2087 -- Set_Attributes for:OKL_PARAMETERS_B --
2088 -----------------------------------------
2089 FUNCTION Set_Attributes (
2090 p_pmr_rec IN pmr_rec_type,
2091 x_pmr_rec OUT NOCOPY pmr_rec_type
2092 ) RETURN VARCHAR2 IS
2093 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2094 BEGIN
2095 x_pmr_rec := p_pmr_rec;
2096 RETURN(l_return_status);
2097 END Set_Attributes;
2098 BEGIN
2099 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2100 p_init_msg_list,
2101 '_PVT',
2102 x_return_status);
2103 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2104 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2105 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2106 RAISE OKC_API.G_EXCEPTION_ERROR;
2107 END IF;
2108 --- Setting item attributes
2109 l_return_status := Set_Attributes(
2110 p_pmr_rec, -- IN
2111 l_pmr_rec); -- OUT
2112 --- If any errors happen abort API
2113 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2114 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2115 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2116 RAISE OKC_API.G_EXCEPTION_ERROR;
2117 END IF;
2118 l_return_status := populate_new_record(l_pmr_rec, l_def_pmr_rec);
2119 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2120 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2121 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2122 RAISE OKC_API.G_EXCEPTION_ERROR;
2123 END IF;
2124 UPDATE OKL_PARAMETERS_B
2125 SET DATATYPE = l_def_pmr_rec.datatype,
2126 CUSTOMIZATION_LEVEL = l_def_pmr_rec.customization_level,
2127 NAME = l_def_pmr_rec.name,
2128 OBJECT_VERSION_NUMBER = l_def_pmr_rec.object_version_number,
2129 CREATED_BY = l_def_pmr_rec.created_by,
2130 CREATION_DATE = l_def_pmr_rec.creation_date,
2131 LAST_UPDATED_BY = l_def_pmr_rec.last_updated_by,
2132 LAST_UPDATE_DATE = l_def_pmr_rec.last_update_date,
2133 LAST_UPDATE_LOGIN = l_def_pmr_rec.last_update_login
2134 WHERE ID = l_def_pmr_rec.id;
2135
2136 x_pmr_rec := l_def_pmr_rec;
2137 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2138 EXCEPTION
2139 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2140 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2141 (
2142 l_api_name,
2143 G_PKG_NAME,
2144 'OKC_API.G_RET_STS_ERROR',
2145 x_msg_count,
2146 x_msg_data,
2147 '_PVT'
2148 );
2149 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2150 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2151 (
2152 l_api_name,
2153 G_PKG_NAME,
2154 'OKC_API.G_RET_STS_UNEXP_ERROR',
2155 x_msg_count,
2156 x_msg_data,
2157 '_PVT'
2158 );
2159 WHEN OTHERS THEN
2160 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2161 (
2162 l_api_name,
2163 G_PKG_NAME,
2164 'OTHERS',
2165 x_msg_count,
2166 x_msg_data,
2167 '_PVT'
2168 );
2169 END update_row;
2170 --------------------------------------
2171 -- update_row for:OKL_PARAMETERS_TL --
2172 --------------------------------------
2173 PROCEDURE update_row(
2174 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2175 x_return_status OUT NOCOPY VARCHAR2,
2176 x_msg_count OUT NOCOPY NUMBER,
2177 x_msg_data OUT NOCOPY VARCHAR2,
2178 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
2179 x_okl_parameters_tl_rec OUT NOCOPY okl_parameters_tl_rec_type) IS
2180
2181 l_api_version CONSTANT NUMBER := 1;
2182 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2183 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2184 l_okl_parameters_tl_rec okl_parameters_tl_rec_type := p_okl_parameters_tl_rec;
2185 l_def_okl_parameters_tl_rec okl_parameters_tl_rec_type;
2186 l_row_notfound BOOLEAN := TRUE;
2187 ----------------------------------
2188 -- FUNCTION populate_new_record --
2189 ----------------------------------
2190 FUNCTION populate_new_record (
2191 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
2192 x_okl_parameters_tl_rec OUT NOCOPY okl_parameters_tl_rec_type
2193 ) RETURN VARCHAR2 IS
2194 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
2195 l_row_notfound BOOLEAN := TRUE;
2196 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2197 BEGIN
2198 x_okl_parameters_tl_rec := p_okl_parameters_tl_rec;
2199 -- Get current database values
2200 l_okl_parameters_tl_rec := get_rec(p_okl_parameters_tl_rec, l_row_notfound);
2201 IF (l_row_notfound) THEN
2202 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2203 END IF;
2204 IF (x_okl_parameters_tl_rec.id = OKC_API.G_MISS_NUM)
2205 THEN
2206 x_okl_parameters_tl_rec.id := l_okl_parameters_tl_rec.id;
2207 END IF;
2208 IF (x_okl_parameters_tl_rec.language = OKC_API.G_MISS_CHAR)
2209 THEN
2210 x_okl_parameters_tl_rec.language := l_okl_parameters_tl_rec.language;
2211 END IF;
2212 IF (x_okl_parameters_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2213 THEN
2214 x_okl_parameters_tl_rec.source_lang := l_okl_parameters_tl_rec.source_lang;
2215 END IF;
2216 IF (x_okl_parameters_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2217 THEN
2218 x_okl_parameters_tl_rec.sfwt_flag := l_okl_parameters_tl_rec.sfwt_flag;
2219 END IF;
2220 IF (x_okl_parameters_tl_rec.description = OKC_API.G_MISS_CHAR)
2221 THEN
2222 x_okl_parameters_tl_rec.description := l_okl_parameters_tl_rec.description;
2223 END IF;
2224 IF (x_okl_parameters_tl_rec.created_by = OKC_API.G_MISS_NUM)
2225 THEN
2226 x_okl_parameters_tl_rec.created_by := l_okl_parameters_tl_rec.created_by;
2227 END IF;
2228 IF (x_okl_parameters_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2229 THEN
2230 x_okl_parameters_tl_rec.creation_date := l_okl_parameters_tl_rec.creation_date;
2231 END IF;
2232 IF (x_okl_parameters_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2233 THEN
2234 x_okl_parameters_tl_rec.last_updated_by := l_okl_parameters_tl_rec.last_updated_by;
2235 END IF;
2236 IF (x_okl_parameters_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2237 THEN
2238 x_okl_parameters_tl_rec.last_update_date := l_okl_parameters_tl_rec.last_update_date;
2239 END IF;
2240 IF (x_okl_parameters_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2241 THEN
2242 x_okl_parameters_tl_rec.last_update_login := l_okl_parameters_tl_rec.last_update_login;
2243 END IF;
2244 RETURN(l_return_status);
2245 END populate_new_record;
2246 ------------------------------------------
2247 -- Set_Attributes for:OKL_PARAMETERS_TL --
2248 ------------------------------------------
2249 FUNCTION Set_Attributes (
2250 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
2251 x_okl_parameters_tl_rec OUT NOCOPY okl_parameters_tl_rec_type
2252 ) RETURN VARCHAR2 IS
2253 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2254 BEGIN
2255 x_okl_parameters_tl_rec := p_okl_parameters_tl_rec;
2256 x_okl_parameters_tl_rec.LANGUAGE := USERENV('LANG');
2257 x_okl_parameters_tl_rec.SOURCE_LANG := USERENV('LANG');
2258 RETURN(l_return_status);
2259 END Set_Attributes;
2260 BEGIN
2261 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2262 p_init_msg_list,
2263 '_PVT',
2264 x_return_status);
2265 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2266 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2267 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2268 RAISE OKC_API.G_EXCEPTION_ERROR;
2269 END IF;
2270 --- Setting item attributes
2271 l_return_status := Set_Attributes(
2272 p_okl_parameters_tl_rec, -- IN
2273 l_okl_parameters_tl_rec); -- OUT
2274 --- If any errors happen abort API
2275 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2276 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2277 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2278 RAISE OKC_API.G_EXCEPTION_ERROR;
2279 END IF;
2280 l_return_status := populate_new_record(l_okl_parameters_tl_rec, l_def_okl_parameters_tl_rec);
2281 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2282 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2283 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2284 RAISE OKC_API.G_EXCEPTION_ERROR;
2285 END IF;
2286 UPDATE OKL_PARAMETERS_TL
2287 SET
2288 DESCRIPTION = l_def_okl_parameters_tl_rec.description,
2289 CREATED_BY = l_def_okl_parameters_tl_rec.created_by,
2290 SOURCE_LANG = l_def_okl_parameters_tl_rec.source_lang,
2291 CREATION_DATE = l_def_okl_parameters_tl_rec.creation_date,
2292 LAST_UPDATED_BY = l_def_okl_parameters_tl_rec.last_updated_by,
2293 LAST_UPDATE_DATE = l_def_okl_parameters_tl_rec.last_update_date,
2294 LAST_UPDATE_LOGIN = l_def_okl_parameters_tl_rec.last_update_login
2295 WHERE ID = l_def_okl_parameters_tl_rec.id
2296 AND USERENV('LANG') in (SOURCE_LANG, LANGUAGE);
2297 --AND SOURCE_LANG = USERENV('LANG');
2298
2299 UPDATE OKL_PARAMETERS_TL
2300 SET SFWT_FLAG = 'Y'
2301 WHERE ID = l_def_okl_parameters_tl_rec.id
2302 AND SOURCE_LANG <> USERENV('LANG');
2303
2304 x_okl_parameters_tl_rec := l_def_okl_parameters_tl_rec;
2305 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2306 EXCEPTION
2307 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2308 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2309 (
2310 l_api_name,
2311 G_PKG_NAME,
2312 'OKC_API.G_RET_STS_ERROR',
2313 x_msg_count,
2314 x_msg_data,
2315 '_PVT'
2316 );
2317 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2318 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2319 (
2320 l_api_name,
2321 G_PKG_NAME,
2322 'OKC_API.G_RET_STS_UNEXP_ERROR',
2323 x_msg_count,
2324 x_msg_data,
2325 '_PVT'
2326 );
2327 WHEN OTHERS THEN
2328 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2329 (
2330 l_api_name,
2331 G_PKG_NAME,
2332 'OTHERS',
2333 x_msg_count,
2334 x_msg_data,
2335 '_PVT'
2336 );
2337 END update_row;
2338 -------------------------------------
2339 -- update_row for:OKL_PARAMETERS_V --
2340 -------------------------------------
2341 PROCEDURE update_row(
2342 p_api_version IN NUMBER,
2343 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2344 x_return_status OUT NOCOPY VARCHAR2,
2345 x_msg_count OUT NOCOPY NUMBER,
2346 x_msg_data OUT NOCOPY VARCHAR2,
2347 p_pmrv_rec IN pmrv_rec_type,
2348 x_pmrv_rec OUT NOCOPY pmrv_rec_type) IS
2349
2350 l_api_version CONSTANT NUMBER := 1;
2351 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2352 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2353 l_pmrv_rec pmrv_rec_type := p_pmrv_rec;
2354 l_def_pmrv_rec pmrv_rec_type;
2355 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
2356 lx_okl_parameters_tl_rec okl_parameters_tl_rec_type;
2357 l_pmr_rec pmr_rec_type;
2358 lx_pmr_rec pmr_rec_type;
2359 -------------------------------
2360 -- FUNCTION fill_who_columns --
2361 -------------------------------
2362 FUNCTION fill_who_columns (
2363 p_pmrv_rec IN pmrv_rec_type
2364 ) RETURN pmrv_rec_type IS
2365 l_pmrv_rec pmrv_rec_type := p_pmrv_rec;
2366 BEGIN
2367 l_pmrv_rec.LAST_UPDATE_DATE := SYSDATE;
2368 l_pmrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2369 l_pmrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2370 RETURN(l_pmrv_rec);
2371 END fill_who_columns;
2372 ----------------------------------
2373 -- FUNCTION populate_new_record --
2374 ----------------------------------
2375 FUNCTION populate_new_record (
2376 p_pmrv_rec IN pmrv_rec_type,
2377 x_pmrv_rec OUT NOCOPY pmrv_rec_type
2378 ) RETURN VARCHAR2 IS
2379 l_pmrv_rec pmrv_rec_type;
2380 l_row_notfound BOOLEAN := TRUE;
2381 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2382 BEGIN
2383 x_pmrv_rec := p_pmrv_rec;
2384 -- Get current database values
2385 l_pmrv_rec := get_rec(p_pmrv_rec, l_row_notfound);
2386 IF (l_row_notfound) THEN
2387 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2388 END IF;
2389 IF (x_pmrv_rec.id = OKC_API.G_MISS_NUM)
2390 THEN
2391 x_pmrv_rec.id := l_pmrv_rec.id;
2392 END IF;
2393 IF (x_pmrv_rec.object_version_number = OKC_API.G_MISS_NUM)
2394 THEN
2395 x_pmrv_rec.object_version_number := l_pmrv_rec.object_version_number;
2396 END IF;
2397 IF (x_pmrv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2398 THEN
2399 x_pmrv_rec.sfwt_flag := l_pmrv_rec.sfwt_flag;
2400 END IF;
2401 IF (x_pmrv_rec.name = OKC_API.G_MISS_CHAR)
2402 THEN
2403 x_pmrv_rec.name := l_pmrv_rec.name;
2404 END IF;
2405 IF (x_pmrv_rec.datatype = OKC_API.G_MISS_CHAR)
2406 THEN
2407 x_pmrv_rec.datatype := l_pmrv_rec.datatype;
2408 END IF;
2409 IF (x_pmrv_rec.customization_level = OKC_API.G_MISS_CHAR)
2410 THEN
2411 x_pmrv_rec.customization_level := l_pmrv_rec.customization_level ;
2412 END IF;
2413 IF (x_pmrv_rec.description = OKC_API.G_MISS_CHAR)
2414 THEN
2415 x_pmrv_rec.description := l_pmrv_rec.description;
2416 END IF;
2417 IF (x_pmrv_rec.created_by = OKC_API.G_MISS_NUM)
2418 THEN
2419 x_pmrv_rec.created_by := l_pmrv_rec.created_by;
2420 END IF;
2421 IF (x_pmrv_rec.creation_date = OKC_API.G_MISS_DATE)
2422 THEN
2423 x_pmrv_rec.creation_date := l_pmrv_rec.creation_date;
2424 END IF;
2425 IF (x_pmrv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2426 THEN
2427 x_pmrv_rec.last_updated_by := l_pmrv_rec.last_updated_by;
2428 END IF;
2429 IF (x_pmrv_rec.last_update_date = OKC_API.G_MISS_DATE)
2430 THEN
2431 x_pmrv_rec.last_update_date := l_pmrv_rec.last_update_date;
2432 END IF;
2433 IF (x_pmrv_rec.last_update_login = OKC_API.G_MISS_NUM)
2434 THEN
2435 x_pmrv_rec.last_update_login := l_pmrv_rec.last_update_login;
2436 END IF;
2437 RETURN(l_return_status);
2438 END populate_new_record;
2439 -----------------------------------------
2440 -- Set_Attributes for:OKL_PARAMETERS_V --
2441 -----------------------------------------
2442 FUNCTION Set_Attributes (
2443 p_pmrv_rec IN pmrv_rec_type,
2444 x_pmrv_rec OUT NOCOPY pmrv_rec_type
2445 ) RETURN VARCHAR2 IS
2446 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2447 BEGIN
2448 x_pmrv_rec := p_pmrv_rec;
2449 x_pmrv_rec.OBJECT_VERSION_NUMBER := NVL(x_pmrv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2450 RETURN(l_return_status);
2451 END Set_Attributes;
2452 BEGIN
2453 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2454 G_PKG_NAME,
2455 p_init_msg_list,
2456 l_api_version,
2457 p_api_version,
2458 '_PVT',
2459 x_return_status);
2460 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2461 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2462 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2463 RAISE OKC_API.G_EXCEPTION_ERROR;
2464 END IF;
2465 --- Setting item attributes
2466 l_return_status := Set_Attributes(
2467 p_pmrv_rec, -- IN
2468 l_pmrv_rec); -- OUT
2469
2470 --- If any errors happen abort API
2471 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2472 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2473 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2474 RAISE OKC_API.G_EXCEPTION_ERROR;
2475 END IF;
2476
2477 l_return_status := populate_new_record(l_pmrv_rec, l_def_pmrv_rec);
2478
2479 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2480 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2481 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2482 RAISE OKC_API.G_EXCEPTION_ERROR;
2483 END IF;
2484
2485 l_def_pmrv_rec := fill_who_columns(l_def_pmrv_rec);
2486 --- Validate all non-missing attributes (Item Level Validation)
2487 l_return_status := Validate_Attributes(l_def_pmrv_rec);
2488 --- If any errors happen abort API
2489 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2490 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2491 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2492 RAISE OKC_API.G_EXCEPTION_ERROR;
2493 END IF;
2494 l_return_status := Validate_Record(l_def_pmrv_rec);
2495 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2496 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2497 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2498 RAISE OKC_API.G_EXCEPTION_ERROR;
2499 END IF;
2500
2501 --------------------------------------
2502 -- Move VIEW record to "Child" records
2503 --------------------------------------
2504 migrate(l_def_pmrv_rec, l_okl_parameters_tl_rec);
2505 migrate(l_def_pmrv_rec, l_pmr_rec);
2506 --------------------------------------------
2507 -- Call the UPDATE_ROW for each child record
2508 --------------------------------------------
2509 update_row(
2510 p_init_msg_list,
2511 x_return_status,
2512 x_msg_count,
2513 x_msg_data,
2514 l_okl_parameters_tl_rec,
2515 lx_okl_parameters_tl_rec
2516 );
2517 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2518 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2519 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2520 RAISE OKC_API.G_EXCEPTION_ERROR;
2521 END IF;
2522 migrate(lx_okl_parameters_tl_rec, l_def_pmrv_rec);
2523 update_row(
2524 p_init_msg_list,
2525 x_return_status,
2526 x_msg_count,
2527 x_msg_data,
2528 l_pmr_rec,
2529 lx_pmr_rec
2530 );
2531 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2532 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2533 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2534 RAISE OKC_API.G_EXCEPTION_ERROR;
2535 END IF;
2536 migrate(lx_pmr_rec, l_def_pmrv_rec);
2537 x_pmrv_rec := l_def_pmrv_rec;
2538 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2539 EXCEPTION
2540 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2541 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2542 (
2543 l_api_name,
2544 G_PKG_NAME,
2545 'OKC_API.G_RET_STS_ERROR',
2546 x_msg_count,
2547 x_msg_data,
2548 '_PVT'
2549 );
2550 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2551 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2552 (
2553 l_api_name,
2554 G_PKG_NAME,
2555 'OKC_API.G_RET_STS_UNEXP_ERROR',
2556 x_msg_count,
2557 x_msg_data,
2558 '_PVT'
2559 );
2560 WHEN OTHERS THEN
2561 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2562 (
2563 l_api_name,
2564 G_PKG_NAME,
2565 'OTHERS',
2566 x_msg_count,
2567 x_msg_data,
2568 '_PVT'
2569 );
2570 END update_row;
2571 ----------------------------------------
2572 -- PL/SQL TBL update_row for:PMRV_TBL --
2573 ----------------------------------------
2574 PROCEDURE update_row(
2575 p_api_version IN NUMBER,
2576 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2577 x_return_status OUT NOCOPY VARCHAR2,
2578 x_msg_count OUT NOCOPY NUMBER,
2579 x_msg_data OUT NOCOPY VARCHAR2,
2580 p_pmrv_tbl IN pmrv_tbl_type,
2581 x_pmrv_tbl OUT NOCOPY pmrv_tbl_type) IS
2582
2583 l_api_version CONSTANT NUMBER := 1;
2584 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2585 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2586 i NUMBER := 0;
2587 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2588 BEGIN
2589 OKC_API.init_msg_list(p_init_msg_list);
2590 -- Make sure PL/SQL table has records in it before passing
2591 IF (p_pmrv_tbl.COUNT > 0) THEN
2592 i := p_pmrv_tbl.FIRST;
2593 LOOP
2594 update_row (
2595 p_api_version => p_api_version,
2596 p_init_msg_list => OKC_API.G_FALSE,
2597 x_return_status => x_return_status,
2598 x_msg_count => x_msg_count,
2599 x_msg_data => x_msg_data,
2600 p_pmrv_rec => p_pmrv_tbl(i),
2601 x_pmrv_rec => x_pmrv_tbl(i));
2602 -- store the highest degree of error
2603 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2604 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2605 l_overall_status := x_return_status;
2606 END IF;
2607 END IF;
2608
2609 EXIT WHEN (i = p_pmrv_tbl.LAST);
2610 i := p_pmrv_tbl.NEXT(i);
2611 END LOOP;
2612 -- return overall status
2613 x_return_status := l_overall_status;
2614
2615 END IF;
2616 EXCEPTION
2617 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2618 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2619 (
2620 l_api_name,
2621 G_PKG_NAME,
2622 'OKC_API.G_RET_STS_ERROR',
2623 x_msg_count,
2624 x_msg_data,
2625 '_PVT'
2626 );
2627 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2628 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2629 (
2630 l_api_name,
2631 G_PKG_NAME,
2632 'OKC_API.G_RET_STS_UNEXP_ERROR',
2633 x_msg_count,
2634 x_msg_data,
2635 '_PVT'
2636 );
2637 WHEN OTHERS THEN
2638 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2639 (
2640 l_api_name,
2641 G_PKG_NAME,
2642 'OTHERS',
2643 x_msg_count,
2644 x_msg_data,
2645 '_PVT'
2646 );
2647 END update_row;
2648
2649 ---------------------------------------------------------------------------
2650 -- PROCEDURE delete_row
2651 ---------------------------------------------------------------------------
2652 -------------------------------------
2653 -- delete_row for:OKL_PARAMETERS_B --
2654 -------------------------------------
2655 PROCEDURE delete_row(
2656 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2657 x_return_status OUT NOCOPY VARCHAR2,
2658 x_msg_count OUT NOCOPY NUMBER,
2659 x_msg_data OUT NOCOPY VARCHAR2,
2660 p_pmr_rec IN pmr_rec_type) IS
2661
2662 l_api_version CONSTANT NUMBER := 1;
2663 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2664 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2665 l_pmr_rec pmr_rec_type:= p_pmr_rec;
2666 l_row_notfound BOOLEAN := TRUE;
2667 BEGIN
2668 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2669 p_init_msg_list,
2670 '_PVT',
2671 x_return_status);
2672 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2673 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2674 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2675 RAISE OKC_API.G_EXCEPTION_ERROR;
2676 END IF;
2677 DELETE FROM OKL_PARAMETERS_B
2678 WHERE ID = l_pmr_rec.id;
2679
2680 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2681 EXCEPTION
2682 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2683 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2684 (
2685 l_api_name,
2686 G_PKG_NAME,
2687 'OKC_API.G_RET_STS_ERROR',
2688 x_msg_count,
2689 x_msg_data,
2690 '_PVT'
2691 );
2692 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2693 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2694 (
2695 l_api_name,
2696 G_PKG_NAME,
2697 'OKC_API.G_RET_STS_UNEXP_ERROR',
2698 x_msg_count,
2699 x_msg_data,
2700 '_PVT'
2701 );
2702 WHEN OTHERS THEN
2703 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2704 (
2705 l_api_name,
2706 G_PKG_NAME,
2707 'OTHERS',
2708 x_msg_count,
2709 x_msg_data,
2710 '_PVT'
2711 );
2712 END delete_row;
2713 --------------------------------------
2714 -- delete_row for:OKL_PARAMETERS_TL --
2715 --------------------------------------
2716 PROCEDURE delete_row(
2717 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2718 x_return_status OUT NOCOPY VARCHAR2,
2719 x_msg_count OUT NOCOPY NUMBER,
2720 x_msg_data OUT NOCOPY VARCHAR2,
2721 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type) IS
2722
2723 l_api_version CONSTANT NUMBER := 1;
2724 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2725 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2726 l_okl_parameters_tl_rec okl_parameters_tl_rec_type:= p_okl_parameters_tl_rec;
2727 l_row_notfound BOOLEAN := TRUE;
2728 ------------------------------------------
2729 -- Set_Attributes for:OKL_PARAMETERS_TL --
2730 ------------------------------------------
2731 FUNCTION Set_Attributes (
2732 p_okl_parameters_tl_rec IN okl_parameters_tl_rec_type,
2733 x_okl_parameters_tl_rec OUT NOCOPY okl_parameters_tl_rec_type
2734 ) RETURN VARCHAR2 IS
2735 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2736 BEGIN
2737 x_okl_parameters_tl_rec := p_okl_parameters_tl_rec;
2738 x_okl_parameters_tl_rec.LANGUAGE := USERENV('LANG');
2739 RETURN(l_return_status);
2740 END Set_Attributes;
2741 BEGIN
2742 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2743 p_init_msg_list,
2744 '_PVT',
2745 x_return_status);
2746 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2747 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2748 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2749 RAISE OKC_API.G_EXCEPTION_ERROR;
2750 END IF;
2751 --- Setting item attributes
2752 l_return_status := Set_Attributes(
2753 p_okl_parameters_tl_rec, -- IN
2754 l_okl_parameters_tl_rec); -- OUT
2755 --- If any errors happen abort API
2756 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2757 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2758 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2759 RAISE OKC_API.G_EXCEPTION_ERROR;
2760 END IF;
2761 DELETE FROM OKL_PARAMETERS_TL
2762 WHERE ID = l_okl_parameters_tl_rec.id;
2763
2764 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2765 EXCEPTION
2766 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2767 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2768 (
2769 l_api_name,
2770 G_PKG_NAME,
2771 'OKC_API.G_RET_STS_ERROR',
2772 x_msg_count,
2773 x_msg_data,
2774 '_PVT'
2775 );
2776 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2777 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2778 (
2779 l_api_name,
2780 G_PKG_NAME,
2781 'OKC_API.G_RET_STS_UNEXP_ERROR',
2782 x_msg_count,
2783 x_msg_data,
2784 '_PVT'
2785 );
2786 WHEN OTHERS THEN
2787 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2788 (
2789 l_api_name,
2790 G_PKG_NAME,
2791 'OTHERS',
2792 x_msg_count,
2793 x_msg_data,
2794 '_PVT'
2795 );
2796 END delete_row;
2797 -------------------------------------
2798 -- delete_row for:OKL_PARAMETERS_V --
2799 -------------------------------------
2800 PROCEDURE delete_row(
2801 p_api_version IN NUMBER,
2802 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2803 x_return_status OUT NOCOPY VARCHAR2,
2804 x_msg_count OUT NOCOPY NUMBER,
2805 x_msg_data OUT NOCOPY VARCHAR2,
2806 p_pmrv_rec IN pmrv_rec_type) IS
2807
2808 l_api_version CONSTANT NUMBER := 1;
2809 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2810 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2811 l_pmrv_rec pmrv_rec_type := p_pmrv_rec;
2812 l_okl_parameters_tl_rec okl_parameters_tl_rec_type;
2813 l_pmr_rec pmr_rec_type;
2814 BEGIN
2815 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2816 G_PKG_NAME,
2817 p_init_msg_list,
2818 l_api_version,
2819 p_api_version,
2820 '_PVT',
2821 x_return_status);
2822 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2823 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2824 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2825 RAISE OKC_API.G_EXCEPTION_ERROR;
2826 END IF;
2827 --------------------------------------
2828 -- Move VIEW record to "Child" records
2829 --------------------------------------
2830 migrate(l_pmrv_rec, l_okl_parameters_tl_rec);
2831 migrate(l_pmrv_rec, l_pmr_rec);
2832 --------------------------------------------
2833 -- Call the DELETE_ROW for each child record
2834 --------------------------------------------
2835 delete_row(
2836 p_init_msg_list,
2837 x_return_status,
2838 x_msg_count,
2839 x_msg_data,
2840 l_okl_parameters_tl_rec
2841 );
2842 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2843 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2844 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2845 RAISE OKC_API.G_EXCEPTION_ERROR;
2846 END IF;
2847 delete_row(
2848 p_init_msg_list,
2849 x_return_status,
2850 x_msg_count,
2851 x_msg_data,
2852 l_pmr_rec
2853 );
2854 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2855 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2856 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2857 RAISE OKC_API.G_EXCEPTION_ERROR;
2858 END IF;
2859 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2860 EXCEPTION
2861 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2862 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2863 (
2864 l_api_name,
2865 G_PKG_NAME,
2866 'OKC_API.G_RET_STS_ERROR',
2867 x_msg_count,
2868 x_msg_data,
2869 '_PVT'
2870 );
2871 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2872 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2873 (
2874 l_api_name,
2875 G_PKG_NAME,
2876 'OKC_API.G_RET_STS_UNEXP_ERROR',
2877 x_msg_count,
2878 x_msg_data,
2879 '_PVT'
2880 );
2881 WHEN OTHERS THEN
2882 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2883 (
2884 l_api_name,
2885 G_PKG_NAME,
2886 'OTHERS',
2887 x_msg_count,
2888 x_msg_data,
2889 '_PVT'
2890 );
2891 END delete_row;
2892 ----------------------------------------
2893 -- PL/SQL TBL delete_row for:PMRV_TBL --
2894 ----------------------------------------
2895 PROCEDURE delete_row(
2896 p_api_version IN NUMBER,
2897 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2898 x_return_status OUT NOCOPY VARCHAR2,
2899 x_msg_count OUT NOCOPY NUMBER,
2900 x_msg_data OUT NOCOPY VARCHAR2,
2901 p_pmrv_tbl IN pmrv_tbl_type) IS
2902
2903 l_api_version CONSTANT NUMBER := 1;
2904 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2905 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2906 i NUMBER := 0;
2907 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2908
2909 BEGIN
2910 OKC_API.init_msg_list(p_init_msg_list);
2911 -- Make sure PL/SQL table has records in it before passing
2912 IF (p_pmrv_tbl.COUNT > 0) THEN
2913 i := p_pmrv_tbl.FIRST;
2914 LOOP
2915 delete_row (
2916 p_api_version => p_api_version,
2917 p_init_msg_list => OKC_API.G_FALSE,
2918 x_return_status => x_return_status,
2919 x_msg_count => x_msg_count,
2920 x_msg_data => x_msg_data,
2921 p_pmrv_rec => p_pmrv_tbl(i));
2922 -- store the highest degree of error
2923 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2924 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2925 l_overall_status := x_return_status;
2926 END IF;
2927 END IF;
2928
2929 EXIT WHEN (i = p_pmrv_tbl.LAST);
2930 i := p_pmrv_tbl.NEXT(i);
2931 END LOOP;
2932 -- return overall status
2933 x_return_status := l_overall_status;
2934
2935 END IF;
2936 EXCEPTION
2937 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2938 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2939 (
2940 l_api_name,
2941 G_PKG_NAME,
2942 'OKC_API.G_RET_STS_ERROR',
2943 x_msg_count,
2944 x_msg_data,
2945 '_PVT'
2946 );
2947 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2948 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2949 (
2950 l_api_name,
2951 G_PKG_NAME,
2952 'OKC_API.G_RET_STS_UNEXP_ERROR',
2953 x_msg_count,
2954 x_msg_data,
2955 '_PVT'
2956 );
2957 WHEN OTHERS THEN
2958 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2959 (
2960 l_api_name,
2961 G_PKG_NAME,
2962 'OTHERS',
2963 x_msg_count,
2964 x_msg_data,
2965 '_PVT'
2966 );
2967 END delete_row;
2968
2969 -------------------------------------------------------------------------------
2970 -- Procedure TRANSLATE_ROW
2971 -------------------------------------------------------------------------------
2972
2973 PROCEDURE TRANSLATE_ROW(p_pmrv_rec IN pmrv_rec_type,
2974 p_owner IN VARCHAR2,
2975 p_last_update_date IN VARCHAR2,
2976 x_return_status OUT NOCOPY VARCHAR2) IS
2977 f_luby NUMBER; -- entity owner in file
2978 f_ludate DATE; -- entity update date in file
2979 db_luby NUMBER; -- entity owner in db
2980 db_ludate DATE; -- entity update date in db
2981
2982 BEGIN
2983 -- Translate owner to file_last_updated_by
2984 f_luby := fnd_load_util.owner_id(p_owner);
2985
2986 -- Translate char last_update_date to date
2987 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
2988
2989 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
2990 INTO db_luby, db_ludate
2991 FROM OKL_PARAMETERS_TL
2992 where ID = to_number(p_pmrv_rec.id)
2993 and USERENV('LANG') =language;
2994
2995 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2996 db_ludate, '')) then
2997
2998 UPDATE OKL_PARAMETERS_TL
2999 SET DESCRIPTION = p_pmrv_rec.description,
3000 LAST_UPDATE_DATE = f_ludate,
3001 LAST_UPDATED_BY = f_luby,
3002 LAST_UPDATE_LOGIN = 0,
3003 SOURCE_LANG = USERENV('LANG')
3004 WHERE ID = to_number(p_pmrv_rec.id)
3005 AND USERENV('LANG') IN (language,source_lang);
3006 END IF;
3007 END TRANSLATE_ROW;
3008
3009 -------------------------------------------------------------------------------
3010 -- Procedure LOAD_ROW
3011 -------------------------------------------------------------------------------
3012
3013 PROCEDURE LOAD_ROW(p_pmrv_rec IN pmrv_rec_type,
3014 p_owner IN VARCHAR2,
3015 p_last_update_date IN VARCHAR2,
3016 x_return_status OUT NOCOPY VARCHAR2) IS
3017 id NUMBER;
3018 f_luby NUMBER; -- entity owner in file
3019 f_ludate DATE; -- entity update date in file
3020 db_luby NUMBER; -- entity owner in db
3021 db_ludate DATE; -- entity update date in db
3022
3023 BEGIN
3024 -- Translate owner to file_last_updated_by
3025 f_luby := fnd_load_util.owner_id(p_owner);
3026
3027 -- Translate char last_update_date to date
3028 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
3029
3030 BEGIN
3031 SELECT ID , LAST_UPDATED_BY, LAST_UPDATE_DATE
3032 INTO id, db_luby, db_ludate
3033 FROM OKL_PARAMETERS_B
3034 where ID = p_pmrv_rec.id;
3035
3036 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
3037 db_ludate, '')) then
3038 --Update _b
3039 UPDATE OKL_PARAMETERS_B
3040 SET
3041 DATATYPE = p_pmrv_rec.datatype,
3042 OBJECT_VERSION_NUMBER = TO_NUMBER(p_pmrv_rec.object_version_number),
3043 CUSTOMIZATION_LEVEL = p_pmrv_rec.customization_level,
3044 LAST_UPDATE_DATE = f_ludate,
3045 LAST_UPDATED_BY = f_luby,
3046 LAST_UPDATE_LOGIN = 0
3047 WHERE ID = to_number(p_pmrv_rec.id);
3048 --Update _TL
3049 UPDATE OKL_PARAMETERS_TL
3050 SET DESCRIPTION = p_pmrv_rec.description,
3051 LAST_UPDATE_DATE = f_ludate,
3052 LAST_UPDATED_BY = f_luby,
3053 LAST_UPDATE_LOGIN = 0,
3054 SOURCE_LANG = USERENV('LANG')
3055 WHERE ID = to_number(p_pmrv_rec.id)
3056 AND USERENV('LANG') IN (language,source_lang);
3057
3058 IF(sql%notfound) THEN
3059
3060 INSERT INTO OKL_PARAMETERS_TL
3061 (
3062 ID,
3063 LANGUAGE,
3064 SOURCE_LANG,
3065 SFWT_FLAG,
3066 DESCRIPTION,
3067 CREATED_BY,
3068 CREATION_DATE,
3069 LAST_UPDATED_BY,
3070 LAST_UPDATE_DATE,
3071 LAST_UPDATE_LOGIN
3072 )
3073 SELECT
3074 TO_NUMBER(p_pmrv_rec.id),
3075 L.LANGUAGE_CODE,
3076 userenv('LANG'),
3077 decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
3078 p_pmrv_rec.DESCRIPTION,
3079 f_luby,
3080 f_ludate,
3081 f_luby,
3082 f_ludate,
3083 0
3084 FROM FND_LANGUAGES L
3085 WHERE L.INSTALLED_FLAG IN ('I','B')
3086 AND NOT EXISTS
3087 (SELECT NULL
3088 FROM OKL_PARAMETERS_TL TL
3089 WHERE TL.ID = TO_NUMBER(p_pmrv_rec.id)
3090 AND TL.LANGUAGE = L.LANGUAGE_CODE);
3091 END IF;
3092 END IF;
3093
3094 END;
3095 EXCEPTION
3096 when no_data_found then
3097 INSERT INTO OKL_PARAMETERS_B
3098 (
3099 ID,
3100 NAME,
3101 DATATYPE,
3102 OBJECT_VERSION_NUMBER,
3103 CUSTOMIZATION_LEVEL,
3104 CREATED_BY,
3105 CREATION_DATE,
3106 LAST_UPDATED_BY,
3107 LAST_UPDATE_DATE,
3108 LAST_UPDATE_LOGIN
3109 )
3110 SELECT
3111 TO_NUMBER(p_pmrv_rec.id),
3112 p_pmrv_rec.name,
3113 p_pmrv_rec.datatype,
3114 TO_NUMBER(p_pmrv_rec.object_version_number),
3115 p_pmrv_rec.customization_level,
3116 f_luby,
3117 f_ludate,
3118 f_luby,
3119 f_ludate,
3120 0
3121 FROM DUAL
3122 WHERE NOT EXISTS (SELECT 1
3123 from OKL_PARAMETERS_B
3124 where ( ID = TO_NUMBER(p_pmrv_rec.id) OR (NAME = p_pmrv_rec.name)));
3125
3126 INSERT INTO OKL_PARAMETERS_TL
3127 (
3128 ID,
3129 LANGUAGE,
3130 SOURCE_LANG,
3131 SFWT_FLAG,
3132 DESCRIPTION,
3133 CREATED_BY,
3134 CREATION_DATE,
3135 LAST_UPDATED_BY,
3136 LAST_UPDATE_DATE,
3137 LAST_UPDATE_LOGIN
3138 )
3139 SELECT TO_NUMBER(p_pmrv_rec.id),
3140 L.LANGUAGE_CODE,
3141 userenv('LANG'),
3142 decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
3143 p_pmrv_rec.description,
3144 f_luby,
3145 f_ludate,
3146 f_luby,
3147 f_ludate,
3148 0
3149 FROM FND_LANGUAGES L
3150 WHERE L.INSTALLED_FLAG IN ('I','B')
3151 AND NOT EXISTS
3152 (SELECT NULL
3153 FROM OKL_PARAMETERS_TL TL
3154 WHERE TL.ID = TO_NUMBER(p_pmrv_rec.id)
3155 AND TL.LANGUAGE = L.LANGUAGE_CODE);
3156
3157 END LOAD_ROW;
3158
3159 -------------------------------------------------------------------------------
3160 -- Procedure LOAD_SEED_ROW
3161 -------------------------------------------------------------------------------
3162
3163 PROCEDURE LOAD_SEED_ROW(
3164 p_upload_mode IN VARCHAR2,
3165 p_parameter_id IN VARCHAR2,
3166 p_name IN VARCHAR2,
3167 p_datatype IN VARCHAR2,
3168 p_object_version_number IN VARCHAR2,
3169 p_customization_level IN VARCHAR2,
3170 p_description IN VARCHAR2,
3171 p_owner IN VARCHAR2,
3172 p_last_update_date IN VARCHAR2)IS
3173
3174 l_api_version CONSTANT number := 1;
3175 l_api_name CONSTANT varchar2(30) := 'LOAD_SEED_ROW';
3176 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
3177 l_msg_count number;
3178 l_msg_data varchar2(4000);
3179 l_init_msg_list VARCHAR2(1):= 'T';
3180 l_pmrv_rec pmrv_rec_type;
3181
3182 BEGIN
3183 --Prepare Record Structure for Insert/Update
3184 l_pmrv_rec.id := p_parameter_id;
3185 l_pmrv_rec.object_version_number := p_object_version_number;
3186 l_pmrv_rec.customization_level := p_customization_level;
3187 l_pmrv_rec.name := p_name;
3188 l_pmrv_rec.datatype := p_datatype;
3189 l_pmrv_rec.description := p_description;
3190
3191 IF(p_upload_mode = 'NLS') then
3192 OKL_PMR_PVT.TRANSLATE_ROW(p_pmrv_rec => l_pmrv_rec,
3193 p_owner => p_owner,
3194 p_last_update_date => p_last_update_date,
3195 x_return_status => l_return_status);
3196
3197 ELSE
3198 OKL_PMR_PVT.LOAD_ROW(p_pmrv_rec => l_pmrv_rec,
3199 p_owner => p_owner,
3200 p_last_update_date => p_last_update_date,
3201 x_return_status => l_return_status);
3202
3203 END IF;
3204 END LOAD_SEED_ROW;
3205
3206 END OKL_PMR_PVT;