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