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