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