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