[Home] [Help]
PACKAGE BODY: APPS.OKL_PSY_PVT
Source
1 PACKAGE BODY OKL_PSY_PVT AS
2 /* $Header: OKLSPSYB.pls 120.1.12010000.2 2008/10/17 06:42:15 gboomina ship $ */
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) := OKC_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 <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_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 -- FUNCTION get_rec for: OKL_PROD_STRM_TYPES_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_psyv_rec IN psyv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN psyv_rec_type IS
100 CURSOR okl_psyb_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 FROM_DATE,
104 TO_DATE,
105 OBJECT_VERSION_NUMBER,
106 ACCRUAL_YN,
107 STY_ID,
108 PDT_ID,
109 CREATED_BY,
110 CREATION_DATE,
111 LAST_UPDATE_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN
114 FROM Okl_Prod_Strm_Types_V
115 WHERE okl_prod_strm_types_v.id = p_id;
116 l_okl_psyb_pk okl_psyb_pk_csr%ROWTYPE;
117 l_psyv_rec psyv_rec_type;
118 BEGIN
119 x_no_data_found := TRUE;
120 -- Get current database values
121 OPEN okl_psyb_pk_csr (p_psyv_rec.id);
122 FETCH okl_psyb_pk_csr INTO
123 l_psyv_rec.id,
124 l_psyv_rec.from_date,
125 l_psyv_rec.TO_DATE,
126 l_psyv_rec.object_version_number,
127 l_psyv_rec.accrual_yn,
128 l_psyv_rec.sty_id,
129 l_psyv_rec.pdt_id,
130 l_psyv_rec.created_by,
131 l_psyv_rec.creation_date,
132 l_psyv_rec.last_update_date,
133 l_psyv_rec.last_updated_by,
134 l_psyv_rec.last_update_login;
135 x_no_data_found := okl_psyb_pk_csr%NOTFOUND;
136 CLOSE okl_psyb_pk_csr;
137 RETURN(l_psyv_rec);
138 END get_rec;
139
140 ------------------------------------------------------------------
141 -- This version of get_rec sets error messages if no data found --
142 ------------------------------------------------------------------
143 FUNCTION get_rec (
144 p_psyv_rec IN psyv_rec_type,
145 x_return_status OUT NOCOPY VARCHAR2
146 ) RETURN psyv_rec_type IS
147 l_psyv_rec psyv_rec_type;
148 l_row_notfound BOOLEAN := TRUE;
149 BEGIN
150 x_return_status := OKC_API.G_RET_STS_SUCCESS;
151 l_psyv_rec := get_rec(p_psyv_rec, l_row_notfound);
152 IF (l_row_notfound) THEN
153 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
154 x_return_status := OKC_API.G_RET_STS_ERROR;
155 END IF;
156 RETURN(l_psyv_rec);
157 END get_rec;
158 -----------------------------------------------------------
159 -- So we don't have to pass an "l_row_notfound" variable --
160 -----------------------------------------------------------
161 FUNCTION get_rec (
162 p_psyv_rec IN psyv_rec_type
163 ) RETURN psyv_rec_type IS
164 l_row_not_found BOOLEAN := TRUE;
165 BEGIN
166 RETURN(get_rec(p_psyv_rec, l_row_not_found));
167 END get_rec;
168 ---------------------------------------------------------------------------
169 -- FUNCTION get_rec for: OKL_PROD_STRM_TYPES
170 ---------------------------------------------------------------------------
171 FUNCTION get_rec (
172 p_psy_rec IN psy_rec_type,
173 x_no_data_found OUT NOCOPY BOOLEAN
174 ) RETURN psy_rec_type IS
175 CURSOR psy_pk_csr (p_id IN NUMBER) IS
176 SELECT
177 ID,
178 TO_DATE,
179 ACCRUAL_YN,
180 FROM_DATE,
181 STY_ID,
182 PDT_ID,
183 OBJECT_VERSION_NUMBER,
184 CREATED_BY,
185 CREATION_DATE,
186 LAST_UPDATED_BY,
187 LAST_UPDATE_DATE,
188 LAST_UPDATE_LOGIN
189 FROM Okl_Prod_Strm_Types
190 WHERE okl_prod_strm_types.id = p_id;
191 l_psy_pk psy_pk_csr%ROWTYPE;
192 l_psy_rec psy_rec_type;
193 BEGIN
194 x_no_data_found := TRUE;
195 -- Get current database values
196 OPEN psy_pk_csr (p_psy_rec.id);
197 FETCH psy_pk_csr INTO
198 l_psy_rec.id,
199 l_psy_rec.TO_DATE,
200 l_psy_rec.accrual_yn,
201 l_psy_rec.from_date,
202 l_psy_rec.sty_id,
203 l_psy_rec.pdt_id,
204 l_psy_rec.object_version_number,
205 l_psy_rec.created_by,
206 l_psy_rec.creation_date,
207 l_psy_rec.last_updated_by,
208 l_psy_rec.last_update_date,
209 l_psy_rec.last_update_login;
210 x_no_data_found := psy_pk_csr%NOTFOUND;
211 CLOSE psy_pk_csr;
212 RETURN(l_psy_rec);
213 END get_rec;
214
215 ------------------------------------------------------------------
216 -- This version of get_rec sets error messages if no data found --
217 ------------------------------------------------------------------
218 FUNCTION get_rec (
219 p_psy_rec IN psy_rec_type,
220 x_return_status OUT NOCOPY VARCHAR2
221 ) RETURN psy_rec_type IS
222 l_psy_rec psy_rec_type;
223 l_row_notfound BOOLEAN := TRUE;
224 BEGIN
225 x_return_status := OKC_API.G_RET_STS_SUCCESS;
226 l_psy_rec := get_rec(p_psy_rec, l_row_notfound);
227 IF (l_row_notfound) THEN
228 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
229 x_return_status := OKC_API.G_RET_STS_ERROR;
230 END IF;
231 RETURN(l_psy_rec);
232 END get_rec;
233 -----------------------------------------------------------
234 -- So we don't have to pass an "l_row_notfound" variable --
235 -----------------------------------------------------------
236 FUNCTION get_rec (
237 p_psy_rec IN psy_rec_type
238 ) RETURN psy_rec_type IS
239 l_row_not_found BOOLEAN := TRUE;
240 BEGIN
241 RETURN(get_rec(p_psy_rec, l_row_not_found));
242 END get_rec;
243 ---------------------------------------------------------------------------
244 -- FUNCTION null_out_defaults for: OKL_PROD_STRM_TYPES_V
245 ---------------------------------------------------------------------------
246 FUNCTION null_out_defaults (
247 p_psyv_rec IN psyv_rec_type
248 ) RETURN psyv_rec_type IS
249 l_psyv_rec psyv_rec_type := p_psyv_rec;
250 BEGIN
251 IF (l_psyv_rec.id = OKC_API.G_MISS_NUM ) THEN
252 l_psyv_rec.id := NULL;
253 END IF;
254 IF (l_psyv_rec.from_date = OKC_API.G_MISS_DATE ) THEN
255 l_psyv_rec.from_date := NULL;
256 END IF;
257 IF (l_psyv_rec.TO_DATE = OKC_API.G_MISS_DATE ) THEN
258 l_psyv_rec.TO_DATE := NULL;
259 END IF;
260 IF (l_psyv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
261 l_psyv_rec.object_version_number := NULL;
262 END IF;
263 IF (l_psyv_rec.accrual_yn = OKC_API.G_MISS_CHAR ) THEN
264 l_psyv_rec.accrual_yn := NULL;
265 END IF;
266 IF (l_psyv_rec.sty_id = OKC_API.G_MISS_NUM ) THEN
267 l_psyv_rec.sty_id := NULL;
268 END IF;
269 IF (l_psyv_rec.pdt_id = OKC_API.G_MISS_NUM ) THEN
270 l_psyv_rec.pdt_id := NULL;
271 END IF;
272 IF (l_psyv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
273 l_psyv_rec.created_by := NULL;
274 END IF;
275 IF (l_psyv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
276 l_psyv_rec.creation_date := NULL;
277 END IF;
278 IF (l_psyv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
279 l_psyv_rec.last_update_date := NULL;
280 END IF;
281 IF (l_psyv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
282 l_psyv_rec.last_updated_by := NULL;
283 END IF;
284 IF (l_psyv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
285 l_psyv_rec.last_update_login := NULL;
286 END IF;
287 RETURN(l_psyv_rec);
288 END null_out_defaults;
289 ---------------------------------
290 -- Validate_Attributes for: ID --
291 ---------------------------------
292 PROCEDURE validate_id(
293 x_return_status OUT NOCOPY VARCHAR2,
294 p_id IN NUMBER) IS
295 BEGIN
296 x_return_status := OKC_API.G_RET_STS_SUCCESS;
297 IF (p_id = OKC_API.G_MISS_NUM OR
298 p_id IS NULL)
299 THEN
300 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
301 x_return_status := OKC_API.G_RET_STS_ERROR;
302 RAISE G_EXCEPTION_HALT_VALIDATION;
303 END IF;
304 EXCEPTION
305 WHEN G_EXCEPTION_HALT_VALIDATION THEN
306 NULL;
307 WHEN OTHERS THEN
308 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
309 ,p_msg_name => G_UNEXPECTED_ERROR
310 ,p_token1 => G_SQLCODE_TOKEN
311 ,p_token1_value => SQLCODE
312 ,p_token2 => G_SQLERRM_TOKEN
313 ,p_token2_value => SQLERRM);
314 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
315 END validate_id;
316 ----------------------------------------
317 -- Validate_Attributes for: FROM_DATE --
318 ----------------------------------------
319 PROCEDURE validate_from_date(
320 x_return_status OUT NOCOPY VARCHAR2,
321 p_from_date IN DATE) IS
322 BEGIN
323 x_return_status := OKC_API.G_RET_STS_SUCCESS;
324 IF (p_from_date = OKC_API.G_MISS_DATE OR
325 p_from_date IS NULL)
326 THEN
327 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'from_date');
328 x_return_status := OKC_API.G_RET_STS_ERROR;
329 RAISE G_EXCEPTION_HALT_VALIDATION;
330 END IF;
331 EXCEPTION
332 WHEN G_EXCEPTION_HALT_VALIDATION THEN
333 NULL;
334 WHEN OTHERS THEN
335 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
336 ,p_msg_name => G_UNEXPECTED_ERROR
337 ,p_token1 => G_SQLCODE_TOKEN
338 ,p_token1_value => SQLCODE
339 ,p_token2 => G_SQLERRM_TOKEN
340 ,p_token2_value => SQLERRM);
341 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
342 END validate_from_date;
343 ----------------------------------------------------
344 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
345 ----------------------------------------------------
346 PROCEDURE validate_object_version_number(
347 x_return_status OUT NOCOPY VARCHAR2,
348 p_object_version_number IN NUMBER) IS
349 BEGIN
350 x_return_status := OKC_API.G_RET_STS_SUCCESS;
351 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
352 p_object_version_number IS NULL)
353 THEN
354 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
355 x_return_status := OKC_API.G_RET_STS_ERROR;
356 RAISE G_EXCEPTION_HALT_VALIDATION;
357 END IF;
358 EXCEPTION
359 WHEN G_EXCEPTION_HALT_VALIDATION THEN
360 NULL;
361 WHEN OTHERS THEN
362 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
363 ,p_msg_name => G_UNEXPECTED_ERROR
364 ,p_token1 => G_SQLCODE_TOKEN
365 ,p_token1_value => SQLCODE
366 ,p_token2 => G_SQLERRM_TOKEN
367 ,p_token2_value => SQLERRM);
368 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
369 END validate_object_version_number;
370 -----------------------------------------
371 -- Validate_Attributes for: ACCRUAL_YN --
372 -----------------------------------------
373 PROCEDURE validate_accrual_yn(
374 x_return_status OUT NOCOPY VARCHAR2,
375 p_accrual_yn IN VARCHAR2) IS
376 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
377 BEGIN
378 x_return_status := OKC_API.G_RET_STS_SUCCESS;
379 IF (p_accrual_yn = OKC_API.G_MISS_CHAR OR
380 p_accrual_yn IS NULL)
381 THEN
382 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'accrual_yn');
383 x_return_status := OKC_API.G_RET_STS_ERROR;
384 RAISE G_EXCEPTION_HALT_VALIDATION;
385 END IF;
386
387 -- check from domain values using the generic
388 l_return_status := OKL_ACCOUNTING_UTIL.validate_lookup_code('YES_NO',p_accrual_yn,0,0);
389
390 IF l_return_status = OKC_API.G_FALSE THEN
391 l_return_status := OKC_API.G_RET_STS_ERROR;
392 END IF;
393
394 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
395 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
396 p_msg_name => g_invalid_value,
397 p_token1 => g_col_name_token,
398 p_token1_value => 'Accrual_yn');
399
400 -- notify caller of an error
401 x_return_status := OKC_API.G_RET_STS_ERROR;
402
403 END IF;
404
405 EXCEPTION
406 WHEN G_EXCEPTION_HALT_VALIDATION THEN
407 NULL;
408 WHEN OTHERS THEN
409 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
410 ,p_msg_name => G_UNEXPECTED_ERROR
411 ,p_token1 => G_SQLCODE_TOKEN
412 ,p_token1_value => SQLCODE
413 ,p_token2 => G_SQLERRM_TOKEN
414 ,p_token2_value => SQLERRM);
415 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
416 END validate_accrual_yn;
417 -------------------------------------
418 -- Validate_Attributes for: STY_ID --
419 -------------------------------------
420 PROCEDURE validate_sty_id(x_return_status OUT NOCOPY VARCHAR2,
421 p_sty_id IN NUMBER)
422 IS
423 CURSOR okl_styv_pk_csr (p_id IN NUMBER) IS
424 SELECT '1'
425 FROM okl_strm_type_v
426 WHERE okl_strm_type_v.id = p_id;
427
428 l_sty_status VARCHAR2(1);
429 l_row_notfound BOOLEAN := TRUE;
430
431 BEGIN
432 x_return_status := OKC_API.G_RET_STS_SUCCESS;
433 IF (p_sty_id = OKC_API.G_MISS_NUM OR
434 p_sty_id IS NULL)
435 THEN
436 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'STY_ID');
437 x_return_status := OKC_API.G_RET_STS_ERROR;
438 RAISE G_EXCEPTION_HALT_VALIDATION;
439 END IF;
440
441 IF (p_sty_id IS NOT NULL)
442 THEN
443 OPEN okl_styv_pk_csr(p_sty_id);
444 FETCH okl_styv_pk_csr INTO l_sty_status;
445 l_row_notfound := okl_styv_pk_csr%NOTFOUND;
446 CLOSE okl_styv_pk_csr;
447 IF (l_row_notfound) THEN
448 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'STY_ID');
449 RAISE G_ITEM_NOT_FOUND_ERROR;
450 END IF;
451 END IF;
452
453 EXCEPTION
454 WHEN G_EXCEPTION_HALT_VALIDATION THEN
455 NULL;
456 WHEN OTHERS THEN
457 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
458 ,p_msg_name => G_UNEXPECTED_ERROR
459 ,p_token1 => G_SQLCODE_TOKEN
460 ,p_token1_value => SQLCODE
461 ,p_token2 => G_SQLERRM_TOKEN
462 ,p_token2_value => SQLERRM);
463 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
464 END validate_sty_id;
465 -------------------------------------
466 -- Validate_Attributes for: PDT_ID --
467 -------------------------------------
468 PROCEDURE validate_pdt_id(x_return_status OUT NOCOPY VARCHAR2,
469 p_pdt_id IN NUMBER)
470 IS
471
472 CURSOR okl_pdtv_pk_csr (p_id IN NUMBER) IS
473 SELECT '1'
474 FROM okl_products_v
475 WHERE okl_products_v.id = p_id;
476
477 l_pdt_status VARCHAR2(1);
478 l_row_notfound BOOLEAN := TRUE;
479
480 BEGIN
481 x_return_status := OKC_API.G_RET_STS_SUCCESS;
482 IF (p_pdt_id = OKC_API.G_MISS_NUM OR
483 p_pdt_id IS NULL)
484 THEN
485 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'pdt_id');
486 x_return_status := OKC_API.G_RET_STS_ERROR;
487 RAISE G_EXCEPTION_HALT_VALIDATION;
488 END IF;
489
490 IF (p_pdt_id IS NOT NULL)
491 THEN
492 OPEN okl_pdtv_pk_csr(p_pdt_id);
493 FETCH okl_pdtv_pk_csr INTO l_pdt_status;
494 l_row_notfound := okl_pdtv_pk_csr%NOTFOUND;
495 CLOSE okl_pdtv_pk_csr;
496 IF (l_row_notfound) THEN
497 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PDT_ID');
498 RAISE G_ITEM_NOT_FOUND_ERROR;
499 END IF;
500 END IF;
501
502 EXCEPTION
503 WHEN G_EXCEPTION_HALT_VALIDATION THEN
504 NULL;
505 WHEN G_ITEM_NOT_FOUND_ERROR THEN
506 x_return_status := OKC_API.G_RET_STS_ERROR;
507 WHEN OTHERS THEN
508 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
509 ,p_msg_name => G_UNEXPECTED_ERROR
510 ,p_token1 => G_SQLCODE_TOKEN
511 ,p_token1_value => SQLCODE
512 ,p_token2 => G_SQLERRM_TOKEN
513 ,p_token2_value => SQLERRM);
514 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
515 END validate_pdt_id;
516
517 ---------------------------------------------------------------------------
518 -- PROCEDURE Validate_To_Date
519 ---------------------------------------------------------------------------
520 -- Start of comments
521 --
522 -- Procedure Name : Validate_To_Date
523 -- Description :
524 -- Business Rules :
525 -- Parameters :
526 -- Version : 1.0
527 -- End of comments
528 ---------------------------------------------------------------------------
529 PROCEDURE Validate_To_Date(p_psyv_rec IN psyv_rec_type
530 ,x_return_status OUT NOCOPY VARCHAR2)
531 IS
532
533 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
534
535 BEGIN
536 -- initialize return status
537 x_return_status := OKC_API.G_RET_STS_SUCCESS;
538
539 -- check for data before processing
540 IF (p_psyv_rec.TO_DATE IS NOT NULL) AND
541 (p_psyv_rec.TO_DATE < p_psyv_rec.from_date) THEN
542 OKC_API.SET_MESSAGE(p_app_name => 'OKL'
543 ,p_msg_name => g_to_date_error
544 ,p_token1 => g_col_name_token
545 ,p_token1_value => 'TO_DATE');
546 x_return_status := OKC_API.G_RET_STS_ERROR;
547 RAISE G_EXCEPTION_HALT_VALIDATION;
548 END IF;
549
550 EXCEPTION
551 WHEN G_EXCEPTION_HALT_VALIDATION THEN
552 -- no processing necessary; validation can continue
553 -- with the next column
554 NULL;
555
556 WHEN OTHERS THEN
557 -- store SQL error message on message stack for caller
558 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
559 p_msg_name => g_unexpected_error,
560 p_token1 => g_sqlcode_token,
561 p_token1_value => SQLCODE,
562 p_token2 => g_sqlerrm_token,
563 p_token2_value => SQLERRM);
564
565 -- notify caller of an UNEXPECTED error
566 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
567
568 END Validate_To_Date;
569
570 ---------------------------------------------------------------------------
571 -- FUNCTION Validate_Attributes
572 ---------------------------------------------------------------------------
573 ---------------------------------------------------
574 -- Validate_Attributes for:OKL_PROD_STRM_TYPES_V --
575 ---------------------------------------------------
576 FUNCTION Validate_Attributes (
577 p_psyv_rec IN psyv_rec_type
578 ) RETURN VARCHAR2 IS
579 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
580 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
581 BEGIN
582 -----------------------------
583 -- Column Level Validation --
584 -----------------------------
585 -- ***
586 -- id
587 -- ***
588 validate_id(x_return_status, p_psyv_rec.id);
589 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
590 l_return_status := x_return_status;
591 RAISE G_EXCEPTION_HALT_VALIDATION;
592 END IF;
593
594 -- ***
595 -- from_date
596 -- ***
597 validate_from_date(x_return_status, p_psyv_rec.from_date);
598 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
599 l_return_status := x_return_status;
600 RAISE G_EXCEPTION_HALT_VALIDATION;
601 END IF;
602
603 -- ***
604 -- object_version_number
605 -- ***
606 validate_object_version_number(x_return_status, p_psyv_rec.object_version_number);
607 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
608 l_return_status := x_return_status;
609 RAISE G_EXCEPTION_HALT_VALIDATION;
610 END IF;
611 -- ***
612 -- accrual_yn
613 -- ***
614 validate_accrual_yn(x_return_status, p_psyv_rec.accrual_yn);
615 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
616 l_return_status := x_return_status;
617 RAISE G_EXCEPTION_HALT_VALIDATION;
618 END IF;
619
620 -- ***
621 -- sty_id
622 -- ***
623 validate_sty_id(x_return_status, p_psyv_rec.sty_id);
624 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
625 l_return_status := x_return_status;
626 RAISE G_EXCEPTION_HALT_VALIDATION;
627 END IF;
628
629 -- ***
630 -- pdt_id
631 -- ***
632 validate_pdt_id(x_return_status, p_psyv_rec.pdt_id);
633 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
634 l_return_status := x_return_status;
635 RAISE G_EXCEPTION_HALT_VALIDATION;
636 END IF;
637
638 RETURN(l_return_status);
639 EXCEPTION
640 WHEN G_EXCEPTION_HALT_VALIDATION THEN
641 RETURN(l_return_status);
642 WHEN OTHERS THEN
643 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
644 ,p_msg_name => G_UNEXPECTED_ERROR
645 ,p_token1 => G_SQLCODE_TOKEN
646 ,p_token1_value => SQLCODE
647 ,p_token2 => G_SQLERRM_TOKEN
648 ,p_token2_value => SQLERRM);
649 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
650 RETURN(l_return_status);
651 END Validate_Attributes;
652
653 ---------------------------------------------------------------------------
654 -- PROCEDURE Validate_Unique_Psy_Record
655 ---------------------------------------------------------------------------
656 -- Start of comments
657 --
658 -- Procedure Name : Validate_Unique_Psy_Record
659 -- Description :
660 -- Business Rules :
661 -- Parameters :
662 -- Version : 1.0
663 -- End of comments
664 ---------------------------------------------------------------------------
665 PROCEDURE Validate_Unique_Psy_Record(p_psyv_rec IN psyv_rec_type
666 ,x_return_status OUT NOCOPY VARCHAR2)
667 IS
668
669 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
670 l_unq_tbl OKC_UTIL.unq_tbl_type;
671 l_psy_status VARCHAR2(1);
672 l_row_found BOOLEAN := FALSE;
673 CURSOR c1(p_pdt_id okl_prod_strm_types_v.pdt_id%TYPE,
674 p_sty_id okl_prod_strm_types_v.sty_id%TYPE) IS
675 SELECT '1'
676 FROM okl_prod_strm_types_v
677 WHERE pdt_id = p_pdt_id
678 AND sty_id = p_sty_id
679 AND id <> NVL(p_psyv_rec.id,-9999);
680
681 BEGIN
682
683 -- initialize return status
684 x_return_status := OKC_API.G_RET_STS_SUCCESS;
685 OPEN c1(p_psyv_rec.pdt_id,
686 p_psyv_rec.sty_id);
687 FETCH c1 INTO l_psy_status;
688 l_row_found := c1%FOUND;
689 CLOSE c1;
690 IF l_row_found THEN
691 OKC_API.set_message('OKL',G_UNQS,G_TABLE_TOKEN, 'Okl_Prod_Strm_Types_V'); ---CHG001
692 x_return_status := OKC_API.G_RET_STS_ERROR;
693 END IF;
694 EXCEPTION
695 WHEN G_EXCEPTION_HALT_VALIDATION THEN
696 -- no processing necessary; validation can continue
697 -- with the next column
698 NULL;
699
700 WHEN OTHERS THEN
701 -- store SQL error message on message stack for caller
702 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
703 p_msg_name => g_unexpected_error,
704 p_token1 => g_sqlcode_token,
705 p_token1_value => SQLCODE,
706 p_token2 => g_sqlerrm_token,
707 p_token2_value => SQLERRM);
708
709 -- notify caller of an UNEXPECTED error
710 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
711
712 END Validate_Unique_Psy_Record;
713
714 ---------------------------------------------------------------------------
715 -- PROCEDURE Validate_Record
716 ---------------------------------------------------------------------------
717 -----------------------------------------------
718 -- Validate Record for:OKL_PROD_STRM_TYPES_V --
719 -----------------------------------------------
720 /*FUNCTION Validate_Record (
721 p_psyv_rec IN psyv_rec_type,
722 p_db_psyv_rec IN psyv_rec_type
723 ) RETURN VARCHAR2 IS
724 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
725 l_db_psyv_rec psyv_rec_type := get_rec(p_psyv_rec);
726 BEGIN
727 l_return_status := Validate_Record(p_psyv_rec);
728
729
730 RETURN (l_return_status);
731 EXCEPTION
732 WHEN OTHERS THEN
733 -- store SQL error message on message stack for caller
734 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
735 p_msg_name => g_unexpected_error,
736 p_token1 => g_sqlcode_token,
737 p_token1_value => SQLCODE,
738 p_token2 => g_sqlerrm_token,
739 p_token2_value => SQLERRM);
740 -- notify caller of an UNEXPECTED error
741 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
742 RETURN(l_return_status);
743 END Validate_Record;
744 */
745
746 ---------------------------------------------------------------------------
747 -- PROCEDURE Validate_Record
748 ---------------------------------------------------------------------------
749
750 FUNCTION Validate_Record (
751 p_psyv_rec IN psyv_rec_type
752 ) RETURN VARCHAR2 IS
753 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
754 l_db_psyv_rec psyv_rec_type := get_rec(p_psyv_rec);
755 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
756 BEGIN
757 -- Validate_To_Date
758 Validate_To_Date(p_psyv_rec,x_return_status);
759 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
760 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
761 -- need to leave
762 l_return_status := x_return_status;
763 RAISE G_EXCEPTION_HALT_VALIDATION;
764 ELSE
765 -- record that there was an error
766 l_return_status := x_return_status;
767 END IF;
768 END IF;
769
770 -- Validate_Unique_Pqv_Record
771 Validate_Unique_Psy_Record(p_psyv_rec,x_return_status);
772 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
773 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
774 -- need to leave
775 l_return_status := x_return_status;
776 RAISE G_EXCEPTION_HALT_VALIDATION;
777 ELSE
778 -- record that there was an error
779 l_return_status := x_return_status;
780 END IF;
781 END IF;
782 RETURN (l_return_status);
783
784 EXCEPTION
785 WHEN G_EXCEPTION_HALT_VALIDATION THEN
786 -- just come out with return status
787 NULL;
788 RETURN (l_return_status);
789
790 WHEN OTHERS THEN
791 -- store SQL error message on message stack for caller
792 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
793 p_msg_name => g_unexpected_error,
794 p_token1 => g_sqlcode_token,
795 p_token1_value => SQLCODE,
796 p_token2 => g_sqlerrm_token,
797 p_token2_value => SQLERRM);
798 -- notify caller of an UNEXPECTED error
799 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
800 RETURN(l_return_status);
801 END Validate_Record;
802
803 ---------------------------------------------------------------------------
804 -- PROCEDURE Migrate
805 ---------------------------------------------------------------------------
806 PROCEDURE migrate (
807 p_from IN psyv_rec_type,
808 p_to IN OUT NOCOPY psy_rec_type
809 ) IS
810 BEGIN
811 p_to.id := p_from.id;
812 p_to.TO_DATE := p_from.TO_DATE;
813 p_to.accrual_yn := p_from.accrual_yn;
814 p_to.from_date := p_from.from_date;
815 p_to.sty_id := p_from.sty_id;
816 p_to.pdt_id := p_from.pdt_id;
817 p_to.object_version_number := p_from.object_version_number;
818 p_to.created_by := p_from.created_by;
819 p_to.creation_date := p_from.creation_date;
820 p_to.last_updated_by := p_from.last_updated_by;
821 p_to.last_update_date := p_from.last_update_date;
822 p_to.last_update_login := p_from.last_update_login;
823 END migrate;
824 PROCEDURE migrate (
825 p_from IN psy_rec_type,
826 p_to IN OUT NOCOPY psyv_rec_type
827 ) IS
828 BEGIN
829 p_to.id := p_from.id;
830 p_to.from_date := p_from.from_date;
831 p_to.TO_DATE := p_from.TO_DATE;
832 p_to.object_version_number := p_from.object_version_number;
833 p_to.accrual_yn := p_from.accrual_yn;
834 p_to.sty_id := p_from.sty_id;
835 p_to.pdt_id := p_from.pdt_id;
836 p_to.created_by := p_from.created_by;
837 p_to.creation_date := p_from.creation_date;
838 p_to.last_update_date := p_from.last_update_date;
839 p_to.last_updated_by := p_from.last_updated_by;
840 p_to.last_update_login := p_from.last_update_login;
841 END migrate;
842 ---------------------------------------------------------------------------
843 -- PROCEDURE validate_row
844 ---------------------------------------------------------------------------
845 --------------------------------------------
846 -- validate_row for:OKL_PROD_STRM_TYPES_V --
847 --------------------------------------------
848 PROCEDURE validate_row(
849 p_api_version IN NUMBER,
850 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
851 x_return_status OUT NOCOPY VARCHAR2,
852 x_msg_count OUT NOCOPY NUMBER,
853 x_msg_data OUT NOCOPY VARCHAR2,
854 p_psyv_rec IN psyv_rec_type) IS
855
856 l_api_version CONSTANT NUMBER := 1;
857 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
858 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
859 l_psyv_rec psyv_rec_type := p_psyv_rec;
860 l_psy_rec psy_rec_type;
861 l_psy_rec psy_rec_type;
862 BEGIN
863 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
864 G_PKG_NAME,
865 p_init_msg_list,
866 l_api_version,
867 p_api_version,
868 '_PVT',
869 x_return_status);
870 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
871 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
872 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
873 RAISE OKC_API.G_EXCEPTION_ERROR;
874 END IF;
875 --- Validate all non-missing attributes (Item Level Validation)
876 l_return_status := Validate_Attributes(l_psyv_rec);
877 --- If any errors happen abort API
878 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
879 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
880 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
881 RAISE OKC_API.G_EXCEPTION_ERROR;
882 END IF;
883 l_return_status := Validate_Record(l_psyv_rec);
884 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
885 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
886 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
887 RAISE OKC_API.G_EXCEPTION_ERROR;
888 END IF;
889 x_return_status := l_return_status;
890 EXCEPTION
891 WHEN OKC_API.G_EXCEPTION_ERROR THEN
892 x_return_status := OKC_API.HANDLE_EXCEPTIONS
893 (
894 l_api_name,
895 G_PKG_NAME,
896 'OKC_API.G_RET_STS_ERROR',
897 x_msg_count,
898 x_msg_data,
899 '_PVT'
900 );
901 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
902 x_return_status := OKC_API.HANDLE_EXCEPTIONS
903 (
904 l_api_name,
905 G_PKG_NAME,
906 'OKC_API.G_RET_STS_UNEXP_ERROR',
907 x_msg_count,
908 x_msg_data,
909 '_PVT'
910 );
911 WHEN OTHERS THEN
912 x_return_status := OKC_API.HANDLE_EXCEPTIONS
913 (
914 l_api_name,
915 G_PKG_NAME,
916 'OTHERS',
917 x_msg_count,
918 x_msg_data,
919 '_PVT'
920 );
921 END validate_row;
922 -------------------------------------------------------
923 -- PL/SQL TBL validate_row for:OKL_PROD_STRM_TYPES_V --
924 -------------------------------------------------------
925 PROCEDURE validate_row(
926 p_api_version IN NUMBER,
927 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
928 x_return_status OUT NOCOPY VARCHAR2,
929 x_msg_count OUT NOCOPY NUMBER,
930 x_msg_data OUT NOCOPY VARCHAR2,
931 p_psyv_tbl IN psyv_tbl_type,
932 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
933
934 l_api_version CONSTANT NUMBER := 1;
935 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
936 i NUMBER := 0;
937 BEGIN
938 OKC_API.init_msg_list(p_init_msg_list);
939 -- Make sure PL/SQL table has records in it before passing
940 IF (p_psyv_tbl.COUNT > 0) THEN
941 i := p_psyv_tbl.FIRST;
942 LOOP
943 DECLARE
944 l_error_rec OKL_API.ERROR_REC_TYPE;
945 BEGIN
946 l_error_rec.api_name := l_api_name;
947 l_error_rec.api_package := G_PKG_NAME;
948 l_error_rec.idx := i;
949 validate_row (
950 p_api_version => p_api_version,
951 p_init_msg_list => OKC_API.G_FALSE,
952 x_return_status => l_error_rec.error_type,
953 x_msg_count => l_error_rec.msg_count,
954 x_msg_data => l_error_rec.msg_data,
955 p_psyv_rec => p_psyv_tbl(i));
956 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
957 l_error_rec.SQLCODE := SQLCODE;
958 load_error_tbl(l_error_rec, px_error_tbl);
959 ELSE
960 x_msg_count := l_error_rec.msg_count;
961 x_msg_data := l_error_rec.msg_data;
962 END IF;
963 EXCEPTION
964 WHEN OKC_API.G_EXCEPTION_ERROR THEN
965 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
966 l_error_rec.SQLCODE := SQLCODE;
967 load_error_tbl(l_error_rec, px_error_tbl);
968 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
969 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
970 l_error_rec.SQLCODE := SQLCODE;
971 load_error_tbl(l_error_rec, px_error_tbl);
972 WHEN OTHERS THEN
973 l_error_rec.error_type := 'OTHERS';
974 l_error_rec.SQLCODE := SQLCODE;
975 load_error_tbl(l_error_rec, px_error_tbl);
976 END;
977 EXIT WHEN (i = p_psyv_tbl.LAST);
978 i := p_psyv_tbl.NEXT(i);
979 END LOOP;
980 END IF;
981 -- Loop through the error_tbl to find the error with the highest severity
982 -- and return it.
983 x_return_status := find_highest_exception(px_error_tbl);
984 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
985 EXCEPTION
986 WHEN OKC_API.G_EXCEPTION_ERROR THEN
987 x_return_status := OKC_API.HANDLE_EXCEPTIONS
988 (
989 l_api_name,
990 G_PKG_NAME,
991 'OKC_API.G_RET_STS_ERROR',
992 x_msg_count,
993 x_msg_data,
994 '_PVT'
995 );
996 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
997 x_return_status := OKC_API.HANDLE_EXCEPTIONS
998 (
999 l_api_name,
1000 G_PKG_NAME,
1001 'OKC_API.G_RET_STS_UNEXP_ERROR',
1002 x_msg_count,
1003 x_msg_data,
1004 '_PVT'
1005 );
1006 WHEN OTHERS THEN
1007 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1008 (
1009 l_api_name,
1010 G_PKG_NAME,
1011 'OTHERS',
1012 x_msg_count,
1013 x_msg_data,
1014 '_PVT'
1015 );
1016 END validate_row;
1017
1018 -------------------------------------------------------
1019 -- PL/SQL TBL validate_row for:OKL_PROD_STRM_TYPES_V --
1020 -------------------------------------------------------
1021 PROCEDURE validate_row(
1022 p_api_version IN NUMBER,
1023 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1024 x_return_status OUT NOCOPY VARCHAR2,
1025 x_msg_count OUT NOCOPY NUMBER,
1026 x_msg_data OUT NOCOPY VARCHAR2,
1027 p_psyv_tbl IN psyv_tbl_type) IS
1028
1029 l_api_version CONSTANT NUMBER := 1;
1030 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1031 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1032 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1033 BEGIN
1034 OKC_API.init_msg_list(p_init_msg_list);
1035 -- Make sure PL/SQL table has records in it before passing
1036 IF (p_psyv_tbl.COUNT > 0) THEN
1037 validate_row (
1038 p_api_version => p_api_version,
1039 p_init_msg_list => OKC_API.G_FALSE,
1040 x_return_status => x_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_psyv_tbl => p_psyv_tbl,
1044 px_error_tbl => l_error_tbl);
1045 END IF;
1046 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1047 EXCEPTION
1048 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1049 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1050 (
1051 l_api_name,
1052 G_PKG_NAME,
1053 'OKC_API.G_RET_STS_ERROR',
1054 x_msg_count,
1055 x_msg_data,
1056 '_PVT'
1057 );
1058 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1059 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1060 (
1061 l_api_name,
1062 G_PKG_NAME,
1063 'OKC_API.G_RET_STS_UNEXP_ERROR',
1064 x_msg_count,
1065 x_msg_data,
1066 '_PVT'
1067 );
1068 WHEN OTHERS THEN
1069 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1070 (
1071 l_api_name,
1072 G_PKG_NAME,
1073 'OTHERS',
1074 x_msg_count,
1075 x_msg_data,
1076 '_PVT'
1077 );
1078 END validate_row;
1079
1080 ---------------------------------------------------------------------------
1081 -- PROCEDURE insert_row
1082 ---------------------------------------------------------------------------
1083 ----------------------------------------
1084 -- insert_row for:OKL_PROD_STRM_TYPES --
1085 ----------------------------------------
1086 PROCEDURE insert_row(
1087 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1088 x_return_status OUT NOCOPY VARCHAR2,
1089 x_msg_count OUT NOCOPY NUMBER,
1090 x_msg_data OUT NOCOPY VARCHAR2,
1091 p_psy_rec IN psy_rec_type,
1092 x_psy_rec OUT NOCOPY psy_rec_type) IS
1093
1094 l_api_version CONSTANT NUMBER := 1;
1095 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1096 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1097 l_psy_rec psy_rec_type := p_psy_rec;
1098 l_def_psy_rec psy_rec_type;
1099 --------------------------------------------
1100 -- Set_Attributes for:OKL_PROD_STRM_TYPES --
1101 --------------------------------------------
1102 FUNCTION Set_Attributes (
1103 p_psy_rec IN psy_rec_type,
1104 x_psy_rec OUT NOCOPY psy_rec_type
1105 ) RETURN VARCHAR2 IS
1106 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1107 BEGIN
1108 x_psy_rec := p_psy_rec;
1109 RETURN(l_return_status);
1110 END Set_Attributes;
1111 BEGIN
1112 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1113 p_init_msg_list,
1114 '_PVT',
1115 x_return_status);
1116 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1117 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1118 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1119 RAISE OKC_API.G_EXCEPTION_ERROR;
1120 END IF;
1121 --- Setting item atributes
1122 l_return_status := Set_Attributes(
1123 p_psy_rec, -- IN
1124 l_psy_rec); -- OUT
1125 --- If any errors happen abort API
1126 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1127 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1128 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1129 RAISE OKC_API.G_EXCEPTION_ERROR;
1130 END IF;
1131 INSERT INTO OKL_PROD_STRM_TYPES(
1132 id,
1133 TO_DATE,
1134 accrual_yn,
1135 from_date,
1136 sty_id,
1137 pdt_id,
1138 object_version_number,
1139 created_by,
1140 creation_date,
1141 last_updated_by,
1142 last_update_date,
1143 last_update_login)
1144 VALUES (
1145 l_psy_rec.id,
1146 l_psy_rec.TO_DATE,
1147 l_psy_rec.accrual_yn,
1148 l_psy_rec.from_date,
1149 l_psy_rec.sty_id,
1150 l_psy_rec.pdt_id,
1151 l_psy_rec.object_version_number,
1152 l_psy_rec.created_by,
1153 l_psy_rec.creation_date,
1154 l_psy_rec.last_updated_by,
1155 l_psy_rec.last_update_date,
1156 l_psy_rec.last_update_login);
1157 -- Set OUT values
1158 x_psy_rec := l_psy_rec;
1159 x_return_status := l_return_status;
1160 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1161 EXCEPTION
1162 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1163 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 G_PKG_NAME,
1167 'OKC_API.G_RET_STS_ERROR',
1168 x_msg_count,
1169 x_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OKC_API.G_RET_STS_UNEXP_ERROR',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT'
1181 );
1182 WHEN OTHERS THEN
1183 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1184 (
1185 l_api_name,
1186 G_PKG_NAME,
1187 'OTHERS',
1188 x_msg_count,
1189 x_msg_data,
1190 '_PVT'
1191 );
1192 END insert_row;
1193 -------------------------------------------
1194 -- insert_row for :OKL_PROD_STRM_TYPES_V --
1195 -------------------------------------------
1196 PROCEDURE insert_row(
1197 p_api_version IN NUMBER,
1198 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1199 x_return_status OUT NOCOPY VARCHAR2,
1200 x_msg_count OUT NOCOPY NUMBER,
1201 x_msg_data OUT NOCOPY VARCHAR2,
1202 p_psyv_rec IN psyv_rec_type,
1203 x_psyv_rec OUT NOCOPY psyv_rec_type) IS
1204
1205 l_api_version CONSTANT NUMBER := 1;
1206 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1207 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1208 l_psyv_rec psyv_rec_type := p_psyv_rec;
1209 l_def_psyv_rec psyv_rec_type;
1210 l_psy_rec psy_rec_type;
1211 lx_psy_rec psy_rec_type;
1212 -------------------------------
1213 -- FUNCTION fill_who_columns --
1214 -------------------------------
1215 FUNCTION fill_who_columns (
1216 p_psyv_rec IN psyv_rec_type
1217 ) RETURN psyv_rec_type IS
1218 l_psyv_rec psyv_rec_type := p_psyv_rec;
1219 BEGIN
1220 l_psyv_rec.CREATION_DATE := SYSDATE;
1221 l_psyv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1222 l_psyv_rec.LAST_UPDATE_DATE := l_psyv_rec.CREATION_DATE;
1223 l_psyv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1224 l_psyv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1225 RETURN(l_psyv_rec);
1226 END fill_who_columns;
1227 ----------------------------------------------
1228 -- Set_Attributes for:OKL_PROD_STRM_TYPES_V --
1229 ----------------------------------------------
1230 FUNCTION Set_Attributes (
1231 p_psyv_rec IN psyv_rec_type,
1232 x_psyv_rec OUT NOCOPY psyv_rec_type
1233 ) RETURN VARCHAR2 IS
1234 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1235 BEGIN
1236 x_psyv_rec := p_psyv_rec;
1237 x_psyv_rec.OBJECT_VERSION_NUMBER := 1;
1238 RETURN(l_return_status);
1239 END Set_Attributes;
1240 BEGIN
1241 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1242 G_PKG_NAME,
1243 p_init_msg_list,
1244 l_api_version,
1245 p_api_version,
1246 '_PVT',
1247 x_return_status);
1248 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1249 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1250 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1251 RAISE OKC_API.G_EXCEPTION_ERROR;
1252 END IF;
1253 l_psyv_rec := null_out_defaults(p_psyv_rec);
1254 -- Set primary key value
1255 l_psyv_rec.ID := get_seq_id;
1256 -- Setting item attributes
1257 l_return_Status := Set_Attributes(
1258 l_psyv_rec, -- IN
1259 l_def_psyv_rec); -- OUT
1260 --- If any errors happen abort API
1261 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1262 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1263 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1264 RAISE OKC_API.G_EXCEPTION_ERROR;
1265 END IF;
1266 l_def_psyv_rec := fill_who_columns(l_def_psyv_rec);
1267 --- Validate all non-missing attributes (Item Level Validation)
1268 l_return_status := Validate_Attributes(l_def_psyv_rec);
1269 --- If any errors happen abort API
1270 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1272 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1273 RAISE OKC_API.G_EXCEPTION_ERROR;
1274 END IF;
1275 l_return_status := Validate_Record(l_def_psyv_rec);
1276 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1277 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1278 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1279 RAISE OKC_API.G_EXCEPTION_ERROR;
1280 END IF;
1281 -----------------------------------------
1282 -- Move VIEW record to "Child" records --
1283 -----------------------------------------
1284 migrate(l_def_psyv_rec, l_psy_rec);
1285 -----------------------------------------------
1286 -- Call the INSERT_ROW for each child record --
1287 -----------------------------------------------
1288 insert_row(
1289 p_init_msg_list,
1290 l_return_status,
1291 x_msg_count,
1292 x_msg_data,
1293 l_psy_rec,
1294 lx_psy_rec
1295 );
1296 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1297 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1298 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1299 RAISE OKC_API.G_EXCEPTION_ERROR;
1300 END IF;
1301 migrate(lx_psy_rec, l_def_psyv_rec);
1302 -- Set OUT values
1303 x_psyv_rec := l_def_psyv_rec;
1304 x_return_status := l_return_status;
1305 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1306 EXCEPTION
1307 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1308 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1309 (
1310 l_api_name,
1311 G_PKG_NAME,
1312 'OKC_API.G_RET_STS_ERROR',
1313 x_msg_count,
1314 x_msg_data,
1315 '_PVT'
1316 );
1317 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1318 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1319 (
1320 l_api_name,
1321 G_PKG_NAME,
1322 'OKC_API.G_RET_STS_UNEXP_ERROR',
1323 x_msg_count,
1324 x_msg_data,
1325 '_PVT'
1326 );
1327 WHEN OTHERS THEN
1328 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1329 (
1330 l_api_name,
1331 G_PKG_NAME,
1332 'OTHERS',
1333 x_msg_count,
1334 x_msg_data,
1335 '_PVT'
1336 );
1337 END insert_row;
1338 ----------------------------------------
1339 -- PL/SQL TBL insert_row for:PSYV_TBL --
1340 ----------------------------------------
1341 PROCEDURE insert_row(
1342 p_api_version IN NUMBER,
1343 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1344 x_return_status OUT NOCOPY VARCHAR2,
1345 x_msg_count OUT NOCOPY NUMBER,
1346 x_msg_data OUT NOCOPY VARCHAR2,
1347 p_psyv_tbl IN psyv_tbl_type,
1348 x_psyv_tbl OUT NOCOPY psyv_tbl_type,
1349 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1350
1351 l_api_version CONSTANT NUMBER := 1;
1352 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1353 i NUMBER := 0;
1354 BEGIN
1355 OKC_API.init_msg_list(p_init_msg_list);
1356 -- Make sure PL/SQL table has records in it before passing
1357 IF (p_psyv_tbl.COUNT > 0) THEN
1358 i := p_psyv_tbl.FIRST;
1359 LOOP
1360 DECLARE
1361 l_error_rec OKL_API.ERROR_REC_TYPE;
1362 BEGIN
1363 l_error_rec.api_name := l_api_name;
1364 l_error_rec.api_package := G_PKG_NAME;
1365 l_error_rec.idx := i;
1366 insert_row (
1367 p_api_version => p_api_version,
1368 p_init_msg_list => OKC_API.G_FALSE,
1369 x_return_status => l_error_rec.error_type,
1370 x_msg_count => l_error_rec.msg_count,
1371 x_msg_data => l_error_rec.msg_data,
1372 p_psyv_rec => p_psyv_tbl(i),
1373 x_psyv_rec => x_psyv_tbl(i));
1374 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1375 l_error_rec.SQLCODE := SQLCODE;
1376 load_error_tbl(l_error_rec, px_error_tbl);
1377 ELSE
1378 x_msg_count := l_error_rec.msg_count;
1379 x_msg_data := l_error_rec.msg_data;
1380 END IF;
1381 EXCEPTION
1382 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1383 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1384 l_error_rec.SQLCODE := SQLCODE;
1385 load_error_tbl(l_error_rec, px_error_tbl);
1386 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1387 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1388 l_error_rec.SQLCODE := SQLCODE;
1389 load_error_tbl(l_error_rec, px_error_tbl);
1390 WHEN OTHERS THEN
1391 l_error_rec.error_type := 'OTHERS';
1392 l_error_rec.SQLCODE := SQLCODE;
1393 load_error_tbl(l_error_rec, px_error_tbl);
1394 END;
1395 EXIT WHEN (i = p_psyv_tbl.LAST);
1396 i := p_psyv_tbl.NEXT(i);
1397 END LOOP;
1398 END IF;
1399 -- Loop through the error_tbl to find the error with the highest severity
1400 -- and return it.
1401 x_return_status := find_highest_exception(px_error_tbl);
1402 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1403 EXCEPTION
1404 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1405 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1406 (
1407 l_api_name,
1408 G_PKG_NAME,
1409 'OKC_API.G_RET_STS_ERROR',
1410 x_msg_count,
1411 x_msg_data,
1412 '_PVT'
1413 );
1414 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1415 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1416 (
1417 l_api_name,
1418 G_PKG_NAME,
1419 'OKC_API.G_RET_STS_UNEXP_ERROR',
1420 x_msg_count,
1421 x_msg_data,
1422 '_PVT'
1423 );
1424 WHEN OTHERS THEN
1425 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1426 (
1427 l_api_name,
1428 G_PKG_NAME,
1429 'OTHERS',
1430 x_msg_count,
1431 x_msg_data,
1432 '_PVT'
1433 );
1434 END insert_row;
1435
1436 ----------------------------------------
1437 -- PL/SQL TBL insert_row for:PSYV_TBL --
1438 ----------------------------------------
1439 PROCEDURE insert_row(
1440 p_api_version IN NUMBER,
1441 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 x_msg_count OUT NOCOPY NUMBER,
1444 x_msg_data OUT NOCOPY VARCHAR2,
1445 p_psyv_tbl IN psyv_tbl_type,
1446 x_psyv_tbl OUT NOCOPY psyv_tbl_type) IS
1447
1448 l_api_version CONSTANT NUMBER := 1;
1449 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1450 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1451 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1452 BEGIN
1453 OKC_API.init_msg_list(p_init_msg_list);
1454 -- Make sure PL/SQL table has records in it before passing
1455 IF (p_psyv_tbl.COUNT > 0) THEN
1456 insert_row (
1457 p_api_version => p_api_version,
1458 p_init_msg_list => OKC_API.G_FALSE,
1459 x_return_status => x_return_status,
1460 x_msg_count => x_msg_count,
1461 x_msg_data => x_msg_data,
1462 p_psyv_tbl => p_psyv_tbl,
1463 x_psyv_tbl => x_psyv_tbl,
1464 px_error_tbl => l_error_tbl);
1465 END IF;
1466 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1467 EXCEPTION
1468 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1469 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1470 (
1471 l_api_name,
1472 G_PKG_NAME,
1473 'OKC_API.G_RET_STS_ERROR',
1474 x_msg_count,
1475 x_msg_data,
1476 '_PVT'
1477 );
1478 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1479 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1480 (
1481 l_api_name,
1482 G_PKG_NAME,
1483 'OKC_API.G_RET_STS_UNEXP_ERROR',
1484 x_msg_count,
1485 x_msg_data,
1486 '_PVT'
1487 );
1488 WHEN OTHERS THEN
1489 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1490 (
1491 l_api_name,
1492 G_PKG_NAME,
1493 'OTHERS',
1494 x_msg_count,
1495 x_msg_data,
1496 '_PVT'
1497 );
1498 END insert_row;
1499
1500 ---------------------------------------------------------------------------
1501 -- PROCEDURE lock_row
1502 ---------------------------------------------------------------------------
1503 --------------------------------------
1504 -- lock_row for:OKL_PROD_STRM_TYPES --
1505 --------------------------------------
1506 PROCEDURE lock_row(
1507 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1508 x_return_status OUT NOCOPY VARCHAR2,
1509 x_msg_count OUT NOCOPY NUMBER,
1510 x_msg_data OUT NOCOPY VARCHAR2,
1511 p_psy_rec IN psy_rec_type) IS
1512
1513 E_Resource_Busy EXCEPTION;
1514 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1515 CURSOR lock_csr (p_psy_rec IN psy_rec_type) IS
1516 SELECT OBJECT_VERSION_NUMBER
1517 FROM OKL_PROD_STRM_TYPES
1518 WHERE ID = p_psy_rec.id
1519 AND OBJECT_VERSION_NUMBER = p_psy_rec.object_version_number
1520 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1521
1522 CURSOR lchk_csr (p_psy_rec IN psy_rec_type) IS
1523 SELECT OBJECT_VERSION_NUMBER
1524 FROM OKL_PROD_STRM_TYPES
1525 WHERE ID = p_psy_rec.id;
1526 l_api_version CONSTANT NUMBER := 1;
1527 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1528 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1529 l_object_version_number OKL_PROD_STRM_TYPES.OBJECT_VERSION_NUMBER%TYPE;
1530 lc_object_version_number OKL_PROD_STRM_TYPES.OBJECT_VERSION_NUMBER%TYPE;
1531 l_row_notfound BOOLEAN := FALSE;
1532 lc_row_notfound BOOLEAN := FALSE;
1533 BEGIN
1534 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1535 p_init_msg_list,
1536 '_PVT',
1537 x_return_status);
1538 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1540 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1541 RAISE OKC_API.G_EXCEPTION_ERROR;
1542 END IF;
1543 BEGIN
1544 OPEN lock_csr(p_psy_rec);
1545 FETCH lock_csr INTO l_object_version_number;
1546 l_row_notfound := lock_csr%NOTFOUND;
1547 CLOSE lock_csr;
1548 EXCEPTION
1549 WHEN E_Resource_Busy THEN
1550 IF (lock_csr%ISOPEN) THEN
1551 CLOSE lock_csr;
1552 END IF;
1553 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1554 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1555 END;
1556
1557 IF ( l_row_notfound ) THEN
1558 OPEN lchk_csr(p_psy_rec);
1559 FETCH lchk_csr INTO lc_object_version_number;
1560 lc_row_notfound := lchk_csr%NOTFOUND;
1561 CLOSE lchk_csr;
1562 END IF;
1563 IF (lc_row_notfound) THEN
1564 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1565 RAISE OKC_API.G_EXCEPTION_ERROR;
1566 ELSIF lc_object_version_number > p_psy_rec.object_version_number THEN
1567 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1568 RAISE OKC_API.G_EXCEPTION_ERROR;
1569 ELSIF lc_object_version_number <> p_psy_rec.object_version_number THEN
1570 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1571 RAISE OKC_API.G_EXCEPTION_ERROR;
1572 ELSIF lc_object_version_number = -1 THEN
1573 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1574 RAISE OKC_API.G_EXCEPTION_ERROR;
1575 END IF;
1576 x_return_status := l_return_status;
1577 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1578 EXCEPTION
1579 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1580 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1581 (
1582 l_api_name,
1583 G_PKG_NAME,
1584 'OKC_API.G_RET_STS_ERROR',
1585 x_msg_count,
1586 x_msg_data,
1587 '_PVT'
1588 );
1589 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1590 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1591 (
1592 l_api_name,
1593 G_PKG_NAME,
1594 'OKC_API.G_RET_STS_UNEXP_ERROR',
1595 x_msg_count,
1596 x_msg_data,
1597 '_PVT'
1598 );
1599 WHEN OTHERS THEN
1600 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1601 (
1602 l_api_name,
1603 G_PKG_NAME,
1604 'OTHERS',
1605 x_msg_count,
1606 x_msg_data,
1607 '_PVT'
1608 );
1609 END lock_row;
1610 -----------------------------------------
1611 -- lock_row for: OKL_PROD_STRM_TYPES_V --
1612 -----------------------------------------
1613 PROCEDURE lock_row(
1614 p_api_version IN NUMBER,
1615 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1616 x_return_status OUT NOCOPY VARCHAR2,
1617 x_msg_count OUT NOCOPY NUMBER,
1618 x_msg_data OUT NOCOPY VARCHAR2,
1619 p_psyv_rec IN psyv_rec_type) IS
1620
1621 l_api_version CONSTANT NUMBER := 1;
1622 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1624 l_psy_rec psy_rec_type;
1625 BEGIN
1626 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1627 G_PKG_NAME,
1628 p_init_msg_list,
1629 l_api_version,
1630 p_api_version,
1631 '_PVT',
1632 x_return_status);
1633 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1634 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1635 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1636 RAISE OKC_API.G_EXCEPTION_ERROR;
1637 END IF;
1638 -----------------------------------------
1639 -- Move VIEW record to "Child" records --
1640 -----------------------------------------
1641 migrate(p_psyv_rec, l_psy_rec);
1642 ---------------------------------------------
1643 -- Call the LOCK_ROW for each child record --
1644 ---------------------------------------------
1645 lock_row(
1646 p_init_msg_list,
1647 l_return_status,
1648 x_msg_count,
1649 x_msg_data,
1650 l_psy_rec
1651 );
1652 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1653 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1654 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1655 RAISE OKC_API.G_EXCEPTION_ERROR;
1656 END IF;
1657 x_return_status := l_return_status;
1658 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1659 EXCEPTION
1660 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1661 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1662 (
1663 l_api_name,
1664 G_PKG_NAME,
1665 'OKC_API.G_RET_STS_ERROR',
1666 x_msg_count,
1667 x_msg_data,
1668 '_PVT'
1669 );
1670 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1671 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1672 (
1673 l_api_name,
1674 G_PKG_NAME,
1675 'OKC_API.G_RET_STS_UNEXP_ERROR',
1676 x_msg_count,
1677 x_msg_data,
1678 '_PVT'
1679 );
1680 WHEN OTHERS THEN
1681 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1682 (
1683 l_api_name,
1684 G_PKG_NAME,
1685 'OTHERS',
1686 x_msg_count,
1687 x_msg_data,
1688 '_PVT'
1689 );
1690 END lock_row;
1691 --------------------------------------
1692 -- PL/SQL TBL lock_row for:PSYV_TBL --
1693 --------------------------------------
1694 PROCEDURE lock_row(
1695 p_api_version IN NUMBER,
1696 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1697 x_return_status OUT NOCOPY VARCHAR2,
1698 x_msg_count OUT NOCOPY NUMBER,
1699 x_msg_data OUT NOCOPY VARCHAR2,
1700 p_psyv_tbl IN psyv_tbl_type,
1701 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1702
1703 l_api_version CONSTANT NUMBER := 1;
1704 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1705 i NUMBER := 0;
1706 BEGIN
1707 OKC_API.init_msg_list(p_init_msg_list);
1708 -- Make sure PL/SQL table has recrods in it before passing
1709 IF (p_psyv_tbl.COUNT > 0) THEN
1710 i := p_psyv_tbl.FIRST;
1711 LOOP
1712 DECLARE
1713 l_error_rec OKL_API.ERROR_REC_TYPE;
1714 BEGIN
1715 l_error_rec.api_name := l_api_name;
1716 l_error_rec.api_package := G_PKG_NAME;
1717 l_error_rec.idx := i;
1718 lock_row(
1719 p_api_version => p_api_version,
1720 p_init_msg_list => OKC_API.G_FALSE,
1721 x_return_status => l_error_rec.error_type,
1722 x_msg_count => l_error_rec.msg_count,
1723 x_msg_data => l_error_rec.msg_data,
1724 p_psyv_rec => p_psyv_tbl(i));
1725 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1726 l_error_rec.SQLCODE := SQLCODE;
1727 load_error_tbl(l_error_rec, px_error_tbl);
1728 ELSE
1729 x_msg_count := l_error_rec.msg_count;
1730 x_msg_data := l_error_rec.msg_data;
1731 END IF;
1732 EXCEPTION
1733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1734 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1735 l_error_rec.SQLCODE := SQLCODE;
1736 load_error_tbl(l_error_rec, px_error_tbl);
1737 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1738 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1739 l_error_rec.SQLCODE := SQLCODE;
1740 load_error_tbl(l_error_rec, px_error_tbl);
1741 WHEN OTHERS THEN
1742 l_error_rec.error_type := 'OTHERS';
1743 l_error_rec.SQLCODE := SQLCODE;
1744 load_error_tbl(l_error_rec, px_error_tbl);
1745 END;
1746 EXIT WHEN (i = p_psyv_tbl.LAST);
1747 i := p_psyv_tbl.NEXT(i);
1748 END LOOP;
1749 END IF;
1750 -- Loop through the error_tbl to find the error with the highest severity
1751 -- and return it.
1752 x_return_status := find_highest_exception(px_error_tbl);
1753 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1754 EXCEPTION
1755 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1756 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1757 (
1758 l_api_name,
1759 G_PKG_NAME,
1760 'OKC_API.G_RET_STS_ERROR',
1761 x_msg_count,
1762 x_msg_data,
1763 '_PVT'
1764 );
1765 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1766 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1767 (
1768 l_api_name,
1769 G_PKG_NAME,
1770 'OKC_API.G_RET_STS_UNEXP_ERROR',
1771 x_msg_count,
1772 x_msg_data,
1773 '_PVT'
1774 );
1775 WHEN OTHERS THEN
1776 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1777 (
1778 l_api_name,
1779 G_PKG_NAME,
1780 'OTHERS',
1781 x_msg_count,
1782 x_msg_data,
1783 '_PVT'
1784 );
1785 END lock_row;
1786 --------------------------------------
1787 -- PL/SQL TBL lock_row for:PSYV_TBL --
1788 --------------------------------------
1789 PROCEDURE lock_row(
1790 p_api_version IN NUMBER,
1791 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1792 x_return_status OUT NOCOPY VARCHAR2,
1793 x_msg_count OUT NOCOPY NUMBER,
1794 x_msg_data OUT NOCOPY VARCHAR2,
1795 p_psyv_tbl IN psyv_tbl_type) IS
1796
1797 l_api_version CONSTANT NUMBER := 1;
1798 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1799 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1800 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1801 BEGIN
1802 OKC_API.init_msg_list(p_init_msg_list);
1803 -- Make sure PL/SQL table has recrods in it before passing
1804 IF (p_psyv_tbl.COUNT > 0) THEN
1805 lock_row(
1806 p_api_version => p_api_version,
1807 p_init_msg_list => OKC_API.G_FALSE,
1808 x_return_status => x_return_status,
1809 x_msg_count => x_msg_count,
1810 x_msg_data => x_msg_data,
1811 p_psyv_tbl => p_psyv_tbl,
1812 px_error_tbl => l_error_tbl);
1813 END IF;
1814 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1815 EXCEPTION
1816 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1817 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1818 (
1819 l_api_name,
1820 G_PKG_NAME,
1821 'OKC_API.G_RET_STS_ERROR',
1822 x_msg_count,
1823 x_msg_data,
1824 '_PVT'
1825 );
1826 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1827 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1828 (
1829 l_api_name,
1830 G_PKG_NAME,
1831 'OKC_API.G_RET_STS_UNEXP_ERROR',
1832 x_msg_count,
1833 x_msg_data,
1834 '_PVT'
1835 );
1836 WHEN OTHERS THEN
1837 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1838 (
1839 l_api_name,
1840 G_PKG_NAME,
1841 'OTHERS',
1842 x_msg_count,
1843 x_msg_data,
1844 '_PVT'
1845 );
1846 END lock_row;
1847 ---------------------------------------------------------------------------
1848 -- PROCEDURE update_row
1849 ---------------------------------------------------------------------------
1850 ----------------------------------------
1851 -- update_row for:OKL_PROD_STRM_TYPES --
1852 ----------------------------------------
1853 PROCEDURE update_row(
1854 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1855 x_return_status OUT NOCOPY VARCHAR2,
1856 x_msg_count OUT NOCOPY NUMBER,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 p_psy_rec IN psy_rec_type,
1859 x_psy_rec OUT NOCOPY psy_rec_type) IS
1860
1861 l_api_version CONSTANT NUMBER := 1;
1862 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1863 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1864 l_psy_rec psy_rec_type := p_psy_rec;
1865 l_def_psy_rec psy_rec_type;
1866 l_row_notfound BOOLEAN := TRUE;
1867 ----------------------------------
1868 -- FUNCTION populate_new_record --
1869 ----------------------------------
1870 FUNCTION populate_new_record (
1871 p_psy_rec IN psy_rec_type,
1872 x_psy_rec OUT NOCOPY psy_rec_type
1873 ) RETURN VARCHAR2 IS
1874 l_psy_rec psy_rec_type;
1875 l_row_notfound BOOLEAN := TRUE;
1876 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1877 BEGIN
1878 x_psy_rec := p_psy_rec;
1879 -- Get current database values
1880 l_psy_rec := get_rec(p_psy_rec, l_return_status);
1881 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1882 IF (x_psy_rec.id = OKC_API.G_MISS_NUM)
1883 THEN
1884 x_psy_rec.id := l_psy_rec.id;
1885 END IF;
1886 IF (x_psy_rec.TO_DATE = OKC_API.G_MISS_DATE)
1887 THEN
1888 x_psy_rec.TO_DATE := l_psy_rec.TO_DATE;
1889 END IF;
1890 IF (x_psy_rec.accrual_yn = OKC_API.G_MISS_CHAR)
1891 THEN
1892 x_psy_rec.accrual_yn := l_psy_rec.accrual_yn;
1893 END IF;
1894 IF (x_psy_rec.from_date = OKC_API.G_MISS_DATE)
1895 THEN
1896 x_psy_rec.from_date := l_psy_rec.from_date;
1897 END IF;
1898 IF (x_psy_rec.sty_id = OKC_API.G_MISS_NUM)
1899 THEN
1900 x_psy_rec.sty_id := l_psy_rec.sty_id;
1901 END IF;
1902 IF (x_psy_rec.pdt_id = OKC_API.G_MISS_NUM)
1903 THEN
1904 x_psy_rec.pdt_id := l_psy_rec.pdt_id;
1905 END IF;
1906 IF (x_psy_rec.object_version_number = OKC_API.G_MISS_NUM)
1907 THEN
1908 x_psy_rec.object_version_number := l_psy_rec.object_version_number;
1909 END IF;
1910 IF (x_psy_rec.created_by = OKC_API.G_MISS_NUM)
1911 THEN
1912 x_psy_rec.created_by := l_psy_rec.created_by;
1913 END IF;
1914 IF (x_psy_rec.creation_date = OKC_API.G_MISS_DATE)
1915 THEN
1916 x_psy_rec.creation_date := l_psy_rec.creation_date;
1917 END IF;
1918 IF (x_psy_rec.last_updated_by = OKC_API.G_MISS_NUM)
1919 THEN
1920 x_psy_rec.last_updated_by := l_psy_rec.last_updated_by;
1921 END IF;
1922 IF (x_psy_rec.last_update_date = OKC_API.G_MISS_DATE)
1923 THEN
1924 x_psy_rec.last_update_date := l_psy_rec.last_update_date;
1925 END IF;
1926 IF (x_psy_rec.last_update_login = OKC_API.G_MISS_NUM)
1927 THEN
1928 x_psy_rec.last_update_login := l_psy_rec.last_update_login;
1929 END IF;
1930 END IF;
1931 RETURN(l_return_status);
1932 END populate_new_record;
1933 --------------------------------------------
1934 -- Set_Attributes for:OKL_PROD_STRM_TYPES --
1935 --------------------------------------------
1936 FUNCTION Set_Attributes (
1937 p_psy_rec IN psy_rec_type,
1938 x_psy_rec OUT NOCOPY psy_rec_type
1939 ) RETURN VARCHAR2 IS
1940 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1941 BEGIN
1942 x_psy_rec := p_psy_rec;
1943 x_psy_rec.OBJECT_VERSION_NUMBER := p_psy_rec.OBJECT_VERSION_NUMBER + 1;
1944 RETURN(l_return_status);
1945 END Set_Attributes;
1946 BEGIN
1947 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1948 p_init_msg_list,
1949 '_PVT',
1950 x_return_status);
1951 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1952 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1953 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1954 RAISE OKC_API.G_EXCEPTION_ERROR;
1955 END IF;
1956 --- Setting item attributes
1957 l_return_status := Set_Attributes(
1958 p_psy_rec, -- IN
1959 l_psy_rec); -- OUT
1960 --- If any errors happen abort API
1961 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1962 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1963 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1964 RAISE OKC_API.G_EXCEPTION_ERROR;
1965 END IF;
1966 l_return_status := populate_new_record(l_psy_rec, l_def_psy_rec);
1967 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1969 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1970 RAISE OKC_API.G_EXCEPTION_ERROR;
1971 END IF;
1972 UPDATE OKL_PROD_STRM_TYPES
1973 SET TO_DATE = l_def_psy_rec.TO_DATE,
1974 ACCRUAL_YN = l_def_psy_rec.accrual_yn,
1975 FROM_DATE = l_def_psy_rec.from_date,
1976 STY_ID = l_def_psy_rec.sty_id,
1977 PDT_ID = l_def_psy_rec.pdt_id,
1978 OBJECT_VERSION_NUMBER = l_def_psy_rec.object_version_number,
1979 CREATED_BY = l_def_psy_rec.created_by,
1980 CREATION_DATE = l_def_psy_rec.creation_date,
1981 LAST_UPDATED_BY = l_def_psy_rec.last_updated_by,
1982 LAST_UPDATE_DATE = l_def_psy_rec.last_update_date,
1983 LAST_UPDATE_LOGIN = l_def_psy_rec.last_update_login
1984 WHERE ID = l_def_psy_rec.id;
1985
1986 x_psy_rec := l_psy_rec;
1987 x_return_status := l_return_status;
1988 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1989 EXCEPTION
1990 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1991 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1992 (
1993 l_api_name,
1994 G_PKG_NAME,
1995 'OKC_API.G_RET_STS_ERROR',
1996 x_msg_count,
1997 x_msg_data,
1998 '_PVT'
1999 );
2000 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2001 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2002 (
2003 l_api_name,
2004 G_PKG_NAME,
2005 'OKC_API.G_RET_STS_UNEXP_ERROR',
2006 x_msg_count,
2007 x_msg_data,
2008 '_PVT'
2009 );
2010 WHEN OTHERS THEN
2011 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2012 (
2013 l_api_name,
2014 G_PKG_NAME,
2015 'OTHERS',
2016 x_msg_count,
2017 x_msg_data,
2018 '_PVT'
2019 );
2020 END update_row;
2021 ------------------------------------------
2022 -- update_row for:OKL_PROD_STRM_TYPES_V --
2023 ------------------------------------------
2024 PROCEDURE update_row(
2025 p_api_version IN NUMBER,
2026 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2027 x_return_status OUT NOCOPY VARCHAR2,
2028 x_msg_count OUT NOCOPY NUMBER,
2029 x_msg_data OUT NOCOPY VARCHAR2,
2030 p_psyv_rec IN psyv_rec_type,
2031 x_psyv_rec OUT NOCOPY psyv_rec_type) IS
2032
2033 l_api_version CONSTANT NUMBER := 1;
2034 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2035 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2036 l_psyv_rec psyv_rec_type := p_psyv_rec;
2037 l_def_psyv_rec psyv_rec_type;
2038 l_db_psyv_rec psyv_rec_type;
2039 l_psy_rec psy_rec_type;
2040 lx_psy_rec psy_rec_type;
2041 -------------------------------
2042 -- FUNCTION fill_who_columns --
2043 -------------------------------
2044 FUNCTION fill_who_columns (
2045 p_psyv_rec IN psyv_rec_type
2046 ) RETURN psyv_rec_type IS
2047 l_psyv_rec psyv_rec_type := p_psyv_rec;
2048 BEGIN
2049 l_psyv_rec.LAST_UPDATE_DATE := SYSDATE;
2050 l_psyv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2051 l_psyv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2052 RETURN(l_psyv_rec);
2053 END fill_who_columns;
2054 ----------------------------------
2055 -- FUNCTION populate_new_record --
2056 ----------------------------------
2057 FUNCTION populate_new_record (
2058 p_psyv_rec IN psyv_rec_type,
2059 x_psyv_rec OUT NOCOPY psyv_rec_type
2060 ) RETURN VARCHAR2 IS
2061 l_row_notfound BOOLEAN := TRUE;
2062 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2063 BEGIN
2064 x_psyv_rec := p_psyv_rec;
2065 -- Get current database values
2066 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2067 -- so it may be verified through LOCK_ROW.
2068 l_db_psyv_rec := get_rec(p_psyv_rec, l_return_status);
2069 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2070 IF (x_psyv_rec.id = OKC_API.G_MISS_NUM)
2071 THEN
2072 x_psyv_rec.id := l_db_psyv_rec.id;
2073 END IF;
2074 IF (x_psyv_rec.from_date = OKC_API.G_MISS_DATE)
2075 THEN
2076 x_psyv_rec.from_date := l_db_psyv_rec.from_date;
2077 END IF;
2078 IF (x_psyv_rec.TO_DATE = OKC_API.G_MISS_DATE)
2079 THEN
2080 x_psyv_rec.TO_DATE := l_db_psyv_rec.TO_DATE;
2081 END IF;
2082 IF (x_psyv_rec.accrual_yn = OKC_API.G_MISS_CHAR)
2083 THEN
2084 x_psyv_rec.accrual_yn := l_db_psyv_rec.accrual_yn;
2085 END IF;
2086 IF (x_psyv_rec.sty_id = OKC_API.G_MISS_NUM)
2087 THEN
2088 x_psyv_rec.sty_id := l_db_psyv_rec.sty_id;
2089 END IF;
2090 IF (x_psyv_rec.pdt_id = OKC_API.G_MISS_NUM)
2091 THEN
2092 x_psyv_rec.pdt_id := l_db_psyv_rec.pdt_id;
2093 END IF;
2094 IF (x_psyv_rec.object_version_number = OKC_API.G_MISS_NUM)
2095 THEN
2096 x_psyv_rec.object_version_number := l_db_psyv_rec.object_version_number;
2097 END IF;
2098 IF (x_psyv_rec.created_by = OKC_API.G_MISS_NUM)
2099 THEN
2100 x_psyv_rec.created_by := l_db_psyv_rec.created_by;
2101 END IF;
2102 IF (x_psyv_rec.creation_date = OKC_API.G_MISS_DATE)
2103 THEN
2104 x_psyv_rec.creation_date := l_db_psyv_rec.creation_date;
2105 END IF;
2106 IF (x_psyv_rec.last_update_date = OKC_API.G_MISS_DATE)
2107 THEN
2108 x_psyv_rec.last_update_date := l_db_psyv_rec.last_update_date;
2109 END IF;
2110 IF (x_psyv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2111 THEN
2112 x_psyv_rec.last_updated_by := l_db_psyv_rec.last_updated_by;
2113 END IF;
2114 IF (x_psyv_rec.last_update_login = OKC_API.G_MISS_NUM)
2115 THEN
2116 x_psyv_rec.last_update_login := l_db_psyv_rec.last_update_login;
2117 END IF;
2118 END IF;
2119 RETURN(l_return_status);
2120 END populate_new_record;
2121 ----------------------------------------------
2122 -- Set_Attributes for:OKL_PROD_STRM_TYPES_V --
2123 ----------------------------------------------
2124 FUNCTION Set_Attributes (
2125 p_psyv_rec IN psyv_rec_type,
2126 x_psyv_rec OUT NOCOPY psyv_rec_type
2127 ) RETURN VARCHAR2 IS
2128 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2129 BEGIN
2130 x_psyv_rec := p_psyv_rec;
2131 RETURN(l_return_status);
2132 END Set_Attributes;
2133 BEGIN
2134 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2135 G_PKG_NAME,
2136 p_init_msg_list,
2137 l_api_version,
2138 p_api_version,
2139 '_PVT',
2140 x_return_status);
2141 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2142 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2143 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2144 RAISE OKC_API.G_EXCEPTION_ERROR;
2145 END IF;
2146 --- Setting item attributes
2147 l_return_status := Set_Attributes(
2148 p_psyv_rec, -- IN
2149 x_psyv_rec); -- OUT
2150 --- If any errors happen abort API
2151 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2152 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2153 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2154 RAISE OKC_API.G_EXCEPTION_ERROR;
2155 END IF;
2156 l_return_status := populate_new_record(l_psyv_rec, l_def_psyv_rec);
2157 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2159 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2160 RAISE OKC_API.G_EXCEPTION_ERROR;
2161 END IF;
2162 l_def_psyv_rec := fill_who_columns(l_def_psyv_rec);
2163 --- Validate all non-missing attributes (Item Level Validation)
2164 l_return_status := Validate_Attributes(l_def_psyv_rec);
2165 --- If any errors happen abort API
2166 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2167 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2168 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2169 RAISE OKC_API.G_EXCEPTION_ERROR;
2170 END IF;
2171 l_return_status := Validate_Record(l_def_psyv_rec);
2172 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2173 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2174 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2175 RAISE OKC_API.G_EXCEPTION_ERROR;
2176 END IF;
2177
2178 -- Lock the Record
2179 lock_row(
2180 p_api_version => p_api_version,
2181 p_init_msg_list => p_init_msg_list,
2182 x_return_status => l_return_status,
2183 x_msg_count => x_msg_count,
2184 x_msg_data => x_msg_data,
2185 p_psyv_rec => l_def_psyv_rec);
2186
2187 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2188 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2189 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2190 RAISE OKC_API.G_EXCEPTION_ERROR;
2191 END IF;
2192
2193 -----------------------------------------
2194 -- Move VIEW record to "Child" records --
2195 -----------------------------------------
2196 migrate(l_def_psyv_rec, l_psy_rec);
2197 -----------------------------------------------
2198 -- Call the UPDATE_ROW for each child record --
2199 -----------------------------------------------
2200
2201 update_row(
2202 p_init_msg_list,
2203 l_return_status,
2204 x_msg_count,
2205 x_msg_data,
2206 l_psy_rec,
2207 lx_psy_rec
2208 );
2209 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2210 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2211 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2212 RAISE OKC_API.G_EXCEPTION_ERROR;
2213 END IF;
2214 migrate(lx_psy_rec, l_def_psyv_rec);
2215 x_psyv_rec := l_def_psyv_rec;
2216 x_return_status := l_return_status;
2217 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2218 EXCEPTION
2219 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2220 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2221 (
2222 l_api_name,
2223 G_PKG_NAME,
2224 'OKC_API.G_RET_STS_ERROR',
2225 x_msg_count,
2226 x_msg_data,
2227 '_PVT'
2228 );
2229 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2230 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2231 (
2232 l_api_name,
2233 G_PKG_NAME,
2234 'OKC_API.G_RET_STS_UNEXP_ERROR',
2235 x_msg_count,
2236 x_msg_data,
2237 '_PVT'
2238 );
2239 WHEN OTHERS THEN
2240 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2241 (
2242 l_api_name,
2243 G_PKG_NAME,
2244 'OTHERS',
2245 x_msg_count,
2246 x_msg_data,
2247 '_PVT'
2248 );
2249 END update_row;
2250 ----------------------------------------
2251 -- PL/SQL TBL update_row for:psyv_tbl --
2252 ----------------------------------------
2253 PROCEDURE update_row(
2254 p_api_version IN NUMBER,
2255 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2256 x_return_status OUT NOCOPY VARCHAR2,
2257 x_msg_count OUT NOCOPY NUMBER,
2258 x_msg_data OUT NOCOPY VARCHAR2,
2259 p_psyv_tbl IN psyv_tbl_type,
2260 x_psyv_tbl OUT NOCOPY psyv_tbl_type,
2261 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2262
2263 l_api_version CONSTANT NUMBER := 1;
2264 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2265 i NUMBER := 0;
2266 BEGIN
2267 OKC_API.init_msg_list(p_init_msg_list);
2268 -- Make sure PL/SQL table has records in it before passing
2269 IF (p_psyv_tbl.COUNT > 0) THEN
2270 i := p_psyv_tbl.FIRST;
2271 LOOP
2272 DECLARE
2273 l_error_rec OKL_API.ERROR_REC_TYPE;
2274 BEGIN
2275 l_error_rec.api_name := l_api_name;
2276 l_error_rec.api_package := G_PKG_NAME;
2277 l_error_rec.idx := i;
2278 update_row (
2279 p_api_version => p_api_version,
2280 p_init_msg_list => OKC_API.G_FALSE,
2281 x_return_status => l_error_rec.error_type,
2282 x_msg_count => l_error_rec.msg_count,
2283 x_msg_data => l_error_rec.msg_data,
2284 p_psyv_rec => p_psyv_tbl(i),
2285 x_psyv_rec => x_psyv_tbl(i));
2286 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2287 l_error_rec.SQLCODE := SQLCODE;
2288 load_error_tbl(l_error_rec, px_error_tbl);
2289 ELSE
2290 x_msg_count := l_error_rec.msg_count;
2291 x_msg_data := l_error_rec.msg_data;
2292 END IF;
2293 EXCEPTION
2294 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2295 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2296 l_error_rec.SQLCODE := SQLCODE;
2297 load_error_tbl(l_error_rec, px_error_tbl);
2298 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2299 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2300 l_error_rec.SQLCODE := SQLCODE;
2301 load_error_tbl(l_error_rec, px_error_tbl);
2302 WHEN OTHERS THEN
2303 l_error_rec.error_type := 'OTHERS';
2304 l_error_rec.SQLCODE := SQLCODE;
2305 load_error_tbl(l_error_rec, px_error_tbl);
2306 END;
2307 EXIT WHEN (i = p_psyv_tbl.LAST);
2308 i := p_psyv_tbl.NEXT(i);
2309 END LOOP;
2310 END IF;
2311 -- Loop through the error_tbl to find the error with the highest severity
2312 -- and return it.
2313 x_return_status := find_highest_exception(px_error_tbl);
2314 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2315 EXCEPTION
2316 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2317 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2318 (
2319 l_api_name,
2320 G_PKG_NAME,
2321 'OKC_API.G_RET_STS_ERROR',
2322 x_msg_count,
2323 x_msg_data,
2324 '_PVT'
2325 );
2326 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2327 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2328 (
2329 l_api_name,
2330 G_PKG_NAME,
2331 'OKC_API.G_RET_STS_UNEXP_ERROR',
2332 x_msg_count,
2333 x_msg_data,
2334 '_PVT'
2335 );
2336 WHEN OTHERS THEN
2337 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2338 (
2339 l_api_name,
2340 G_PKG_NAME,
2341 'OTHERS',
2342 x_msg_count,
2343 x_msg_data,
2344 '_PVT'
2345 );
2346 END update_row;
2347
2348 ----------------------------------------
2349 -- PL/SQL TBL update_row for:PSYV_TBL --
2350 ----------------------------------------
2351 PROCEDURE update_row(
2352 p_api_version IN NUMBER,
2353 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2354 x_return_status OUT NOCOPY VARCHAR2,
2355 x_msg_count OUT NOCOPY NUMBER,
2356 x_msg_data OUT NOCOPY VARCHAR2,
2357 p_psyv_tbl IN psyv_tbl_type,
2358 x_psyv_tbl OUT NOCOPY psyv_tbl_type) IS
2359
2360 l_api_version CONSTANT NUMBER := 1;
2361 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2362 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2363 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2364 BEGIN
2365 OKC_API.init_msg_list(p_init_msg_list);
2366 -- Make sure PL/SQL table has records in it before passing
2367 IF (p_psyv_tbl.COUNT > 0) THEN
2368 update_row (
2369 p_api_version => p_api_version,
2370 p_init_msg_list => OKC_API.G_FALSE,
2371 x_return_status => x_return_status,
2372 x_msg_count => x_msg_count,
2373 x_msg_data => x_msg_data,
2374 p_psyv_tbl => p_psyv_tbl,
2375 x_psyv_tbl => x_psyv_tbl,
2376 px_error_tbl => l_error_tbl);
2377 END IF;
2378 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2379 EXCEPTION
2380 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2381 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2382 (
2383 l_api_name,
2384 G_PKG_NAME,
2385 'OKC_API.G_RET_STS_ERROR',
2386 x_msg_count,
2387 x_msg_data,
2388 '_PVT'
2389 );
2390 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2391 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2392 (
2393 l_api_name,
2394 G_PKG_NAME,
2395 'OKC_API.G_RET_STS_UNEXP_ERROR',
2396 x_msg_count,
2397 x_msg_data,
2398 '_PVT'
2399 );
2400 WHEN OTHERS THEN
2401 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2402 (
2403 l_api_name,
2404 G_PKG_NAME,
2405 'OTHERS',
2406 x_msg_count,
2407 x_msg_data,
2408 '_PVT'
2409 );
2410 END update_row;
2411
2412 ---------------------------------------------------------------------------
2413 -- PROCEDURE delete_row
2414 ---------------------------------------------------------------------------
2415 ----------------------------------------
2416 -- delete_row for:OKL_PROD_STRM_TYPES --
2417 ----------------------------------------
2418 PROCEDURE delete_row(
2419 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2420 x_return_status OUT NOCOPY VARCHAR2,
2421 x_msg_count OUT NOCOPY NUMBER,
2422 x_msg_data OUT NOCOPY VARCHAR2,
2423 p_psy_rec IN psy_rec_type) IS
2424
2425 l_api_version CONSTANT NUMBER := 1;
2426 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2427 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2428 l_psy_rec psy_rec_type := p_psy_rec;
2429 l_row_notfound BOOLEAN := TRUE;
2430 BEGIN
2431 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2432 p_init_msg_list,
2433 '_PVT',
2434 x_return_status);
2435 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2436 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2437 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2438 RAISE OKC_API.G_EXCEPTION_ERROR;
2439 END IF;
2440
2441 DELETE FROM OKL_PROD_STRM_TYPES
2442 WHERE ID = p_psy_rec.id;
2443
2444 x_return_status := l_return_status;
2445 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2446 EXCEPTION
2447 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2448 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2449 (
2450 l_api_name,
2451 G_PKG_NAME,
2452 'OKC_API.G_RET_STS_ERROR',
2453 x_msg_count,
2454 x_msg_data,
2455 '_PVT'
2456 );
2457 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2458 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2459 (
2460 l_api_name,
2461 G_PKG_NAME,
2462 'OKC_API.G_RET_STS_UNEXP_ERROR',
2463 x_msg_count,
2464 x_msg_data,
2465 '_PVT'
2466 );
2467 WHEN OTHERS THEN
2468 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2469 (
2470 l_api_name,
2471 G_PKG_NAME,
2472 'OTHERS',
2473 x_msg_count,
2474 x_msg_data,
2475 '_PVT'
2476 );
2477 END delete_row;
2478 ------------------------------------------
2479 -- delete_row for:OKL_PROD_STRM_TYPES_V --
2480 ------------------------------------------
2481 PROCEDURE delete_row(
2482 p_api_version IN NUMBER,
2483 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2484 x_return_status OUT NOCOPY VARCHAR2,
2485 x_msg_count OUT NOCOPY NUMBER,
2486 x_msg_data OUT NOCOPY VARCHAR2,
2487 p_psyv_rec IN psyv_rec_type) IS
2488
2489 l_api_version CONSTANT NUMBER := 1;
2490 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2491 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2492 l_psyv_rec psyv_rec_type := p_psyv_rec;
2493 l_psy_rec psy_rec_type;
2494 BEGIN
2495 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2496 G_PKG_NAME,
2497 p_init_msg_list,
2498 l_api_version,
2499 p_api_version,
2500 '_PVT',
2501 x_return_status);
2502 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2503 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2504 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2505 RAISE OKC_API.G_EXCEPTION_ERROR;
2506 END IF;
2507 -----------------------------------------
2508 -- Move VIEW record to "Child" records --
2509 -----------------------------------------
2510 migrate(l_psyv_rec, l_psy_rec);
2511 -----------------------------------------------
2512 -- Call the DELETE_ROW for each child record --
2513 -----------------------------------------------
2514 delete_row(
2515 p_init_msg_list,
2516 l_return_status,
2517 x_msg_count,
2518 x_msg_data,
2519 l_psy_rec
2520 );
2521 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2522 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2523 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2524 RAISE OKC_API.G_EXCEPTION_ERROR;
2525 END IF;
2526 x_return_status := l_return_status;
2527 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2528 EXCEPTION
2529 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2530 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2531 (
2532 l_api_name,
2533 G_PKG_NAME,
2534 'OKC_API.G_RET_STS_ERROR',
2535 x_msg_count,
2536 x_msg_data,
2537 '_PVT'
2538 );
2539 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2540 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2541 (
2542 l_api_name,
2543 G_PKG_NAME,
2544 'OKC_API.G_RET_STS_UNEXP_ERROR',
2545 x_msg_count,
2546 x_msg_data,
2547 '_PVT'
2548 );
2549 WHEN OTHERS THEN
2550 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2551 (
2552 l_api_name,
2553 G_PKG_NAME,
2554 'OTHERS',
2555 x_msg_count,
2556 x_msg_data,
2557 '_PVT'
2558 );
2559 END delete_row;
2560 -----------------------------------------------------
2561 -- PL/SQL TBL delete_row for:OKL_PROD_STRM_TYPES_V --
2562 -----------------------------------------------------
2563 PROCEDURE delete_row(
2564 p_api_version IN NUMBER,
2565 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2566 x_return_status OUT NOCOPY VARCHAR2,
2567 x_msg_count OUT NOCOPY NUMBER,
2568 x_msg_data OUT NOCOPY VARCHAR2,
2569 p_psyv_tbl IN psyv_tbl_type,
2570 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2571
2572 l_api_version CONSTANT NUMBER := 1;
2573 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2574 i NUMBER := 0;
2575 BEGIN
2576 OKC_API.init_msg_list(p_init_msg_list);
2577 -- Make sure PL/SQL table has records in it before passing
2578 IF (p_psyv_tbl.COUNT > 0) THEN
2579 i := p_psyv_tbl.FIRST;
2580 LOOP
2581 DECLARE
2582 l_error_rec OKL_API.ERROR_REC_TYPE;
2583 BEGIN
2584 l_error_rec.api_name := l_api_name;
2585 l_error_rec.api_package := G_PKG_NAME;
2586 l_error_rec.idx := i;
2587 delete_row (
2588 p_api_version => p_api_version,
2589 p_init_msg_list => OKC_API.G_FALSE,
2590 x_return_status => l_error_rec.error_type,
2591 x_msg_count => l_error_rec.msg_count,
2592 x_msg_data => l_error_rec.msg_data,
2593 p_psyv_rec => p_psyv_tbl(i));
2594 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2595 l_error_rec.SQLCODE := SQLCODE;
2596 load_error_tbl(l_error_rec, px_error_tbl);
2597 ELSE
2598 x_msg_count := l_error_rec.msg_count;
2599 x_msg_data := l_error_rec.msg_data;
2600 END IF;
2601 EXCEPTION
2602 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2603 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2604 l_error_rec.SQLCODE := SQLCODE;
2605 load_error_tbl(l_error_rec, px_error_tbl);
2606 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2607 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2608 l_error_rec.SQLCODE := SQLCODE;
2609 load_error_tbl(l_error_rec, px_error_tbl);
2610 WHEN OTHERS THEN
2611 l_error_rec.error_type := 'OTHERS';
2612 l_error_rec.SQLCODE := SQLCODE;
2613 load_error_tbl(l_error_rec, px_error_tbl);
2614 END;
2615 EXIT WHEN (i = p_psyv_tbl.LAST);
2616 i := p_psyv_tbl.NEXT(i);
2617 END LOOP;
2618 END IF;
2619 -- Loop through the error_tbl to find the error with the highest severity
2620 -- and return it.
2621 x_return_status := find_highest_exception(px_error_tbl);
2622 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2623 EXCEPTION
2624 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2625 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2626 (
2627 l_api_name,
2628 G_PKG_NAME,
2629 'OKC_API.G_RET_STS_ERROR',
2630 x_msg_count,
2631 x_msg_data,
2632 '_PVT'
2633 );
2634 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2635 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2636 (
2637 l_api_name,
2638 G_PKG_NAME,
2639 'OKC_API.G_RET_STS_UNEXP_ERROR',
2640 x_msg_count,
2641 x_msg_data,
2642 '_PVT'
2643 );
2644 WHEN OTHERS THEN
2645 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2646 (
2647 l_api_name,
2648 G_PKG_NAME,
2649 'OTHERS',
2650 x_msg_count,
2651 x_msg_data,
2652 '_PVT'
2653 );
2654 END delete_row;
2655
2656 -----------------------------------------------------
2657 -- PL/SQL TBL delete_row for:OKL_PROD_STRM_TYPES_V --
2658 -----------------------------------------------------
2659 PROCEDURE delete_row(
2660 p_api_version IN NUMBER,
2661 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2662 x_return_status OUT NOCOPY VARCHAR2,
2663 x_msg_count OUT NOCOPY NUMBER,
2664 x_msg_data OUT NOCOPY VARCHAR2,
2665 p_psyv_tbl IN psyv_tbl_type) IS
2666
2667 l_api_version CONSTANT NUMBER := 1;
2668 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2669 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2670 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2671 BEGIN
2672 OKC_API.init_msg_list(p_init_msg_list);
2673 -- Make sure PL/SQL table has records in it before passing
2674 IF (p_psyv_tbl.COUNT > 0) THEN
2675 delete_row (
2676 p_api_version => p_api_version,
2677 p_init_msg_list => OKC_API.G_FALSE,
2678 x_return_status => x_return_status,
2679 x_msg_count => x_msg_count,
2680 x_msg_data => x_msg_data,
2681 p_psyv_tbl => p_psyv_tbl,
2682 px_error_tbl => l_error_tbl);
2683 END IF;
2684 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2685 EXCEPTION
2686 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2687 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2688 (
2689 l_api_name,
2690 G_PKG_NAME,
2691 'OKC_API.G_RET_STS_ERROR',
2692 x_msg_count,
2693 x_msg_data,
2694 '_PVT'
2695 );
2696 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2697 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2698 (
2699 l_api_name,
2700 G_PKG_NAME,
2701 'OKC_API.G_RET_STS_UNEXP_ERROR',
2702 x_msg_count,
2703 x_msg_data,
2704 '_PVT'
2705 );
2706 WHEN OTHERS THEN
2707 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2708 (
2709 l_api_name,
2710 G_PKG_NAME,
2711 'OTHERS',
2712 x_msg_count,
2713 x_msg_data,
2714 '_PVT'
2715 );
2716 END delete_row;
2717
2718 END OKL_PSY_PVT;