[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKL_ILS_PVT
Source
1 PACKAGE BODY OKL_ILS_PVT AS
2 /* $Header: OKLSILSB.pls 120.3.12020000.2 2012/08/07 13:23:35 rpillay ship $ */
3
4
5 -- Checking Unique Key
6 FUNCTION IS_UNIQUE (p_ilsv_rec ilsv_rec_type) RETURN VARCHAR2
7 IS
8 CURSOR l_ils_csr IS
9 SELECT strm.name, invf.name
10 FROM OKL_INVC_FRMT_STRMS invstr , OKL_STRM_TYPE_TL strm ,OKL_INVOICE_FORMATS_TL invf
11 WHERE invstr.sty_id = p_ilsv_rec.sty_id
12 AND invstr.inf_id = p_ilsv_rec.inf_id
13 AND invstr.id <> NVL(p_ilsv_rec.id,-9999)
14 AND invstr.sty_id = strm.id
15 AND invstr.inf_id = invf.id
16 AND strm.language = USERENV('LANG')
17 AND invf.language = USERENV('LANG');
18
19 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
20 l_strm_name VARCHAR2(50);
21 l_inv_name VARCHAR2(50);
22 l_found BOOLEAN;
23 BEGIN
24
25 -- check for unique product and location
26 OPEN l_ils_csr;
27 FETCH l_ils_csr INTO l_strm_name, l_inv_name;
28 l_found := l_ils_csr%FOUND;
29 CLOSE l_ils_csr;
30
31 IF (l_found) THEN
32 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
33 p_msg_name => 'OKL_ILS_Exists',
34 p_token1 => 'STRM_NAME',
35 p_token1_value => l_strm_name,
36 p_token2 => 'INV_NAME',
37 p_token2_value => l_inv_name);
38 -- notify caller of an error
39 l_return_status := Okl_Api.G_RET_STS_ERROR;
40 END IF;
41 RETURN (l_return_status);
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN (l_return_status);
46 END IS_UNIQUE;
47
48
49 -- Validate not null value for ID
50
51 PROCEDURE validate_id(x_return_status OUT NOCOPY VARCHAR2,
52 p_ilsv_rec IN ilsv_rec_type) IS
53
54 BEGIN
55 -- initialize return status
56 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
57
58 -- data is required
59 IF(p_ilsv_rec.id IS NULL) OR (p_ilsv_rec.id = Okl_Api.G_MISS_NUM) THEN
60 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
61 p_msg_name => g_required_value,
62 p_token1 => g_col_name_token,
63 p_token1_value => 'id');
64
65 -- notify caller of an error
66 x_return_status := Okl_Api.G_RET_STS_ERROR;
67
68 -- halt further validation of this column
69 -- RAISE G_EXCEPTION_HALT_VALIDATION;
70 END IF;
71
72 END validate_id;
73
74
75 -- Validate not null value for Version Number
76
77 PROCEDURE validate_object_version_number(x_return_status OUT NOCOPY VARCHAR2,
78 p_ilsv_rec IN ilsv_rec_type) IS
79
80 BEGIN
81 -- initialize return status
82 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
83
84 -- data is required
85 IF(p_ilsv_rec.object_version_number IS NULL) OR (p_ilsv_rec.object_version_number = Okl_Api.G_MISS_NUM) THEN
86 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
87 p_msg_name => g_required_value,
88 p_token1 => g_col_name_token,
89 p_token1_value => 'object_version_number');
90
91 -- notify caller of an error
92 x_return_status := Okl_Api.G_RET_STS_ERROR;
93
94 -- halt further validation of this column
95 RAISE G_EXCEPTION_HALT_VALIDATION;
96 END IF;
97
98 END validate_object_version_number;
99
100
101
102
103 PROCEDURE validate_ilt_id(x_return_status OUT NOCOPY VARCHAR2,
104 p_ilsv_rec IN ilsv_rec_type) IS
105
106 l_dummy_var VARCHAR2(1) := '?';
107 CURSOR l_ilsv_csr IS
108 SELECT 'x'
109 FROM OKL_INVC_LINE_TYPES_V
110 WHERE id = p_ilsv_rec.ilt_id;
111
112 BEGIN
113 -- initialize return status
114 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
115
116 -- data is required
117 IF(p_ilsv_rec.ilt_id IS NULL) OR (p_ilsv_rec.ilt_id = Okl_Api.G_MISS_NUM) THEN
118 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
119 p_msg_name => g_required_value,
120 p_token1 => g_col_name_token,
121 p_token1_value => 'ilt_id');
122
123 -- notify caller of an error
124 x_return_status := Okl_Api.G_RET_STS_ERROR;
125
126 -- halt further validation of this column
127 -- RAISE G_EXCEPTION_HALT_VALIDATION;
128 END IF;
129
130 -- enforce foreign key
131 OPEN l_ilsv_csr;
132 FETCH l_ilsv_csr INTO l_dummy_var;
133 CLOSE l_ilsv_csr;
134
135 -- if l_dummy_var is still set to default, data was not found
136 IF(l_dummy_var = '?') THEN
137 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
138 p_msg_name => g_no_parent_record,
139 p_token1 => g_col_name_token,
140 p_token1_value => 'ilt_id',
141 p_token2 => g_child_table_token,
142 p_token2_value => 'OKL_INVC_FRMT_STRMS_V',
143 p_token3 => g_parent_table_token,
144 p_token3_value => 'OKL_INVC_LINE_TYPES_V');
145
146 -- notify caller of an error
147 x_return_status := Okl_Api.G_RET_STS_ERROR;
148 END IF;
149
150 EXCEPTION
151 WHEN G_EXCEPTION_HALT_VALIDATION THEN
152 -- no processing necessary validation can continue with the next column
153 NULL;
154
155 WHEN OTHERS THEN
156 -- store SQL error message on message stack for caller
157 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
158 p_msg_name => g_unexpected_error,
159 p_token1 => g_sqlcode_token,
160 p_token1_value => SQLCODE,
161 p_token2 => g_sqlerrm_token,
162 p_token2_value => SQLERRM);
163
164
165 -- notify caller of an error
166 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
167
168 -- verfiy that cursor was closed
169 IF l_ilsv_csr%ISOPEN THEN
170 CLOSE l_ilsv_csr;
171 END IF;
172
173 END validate_ilt_id;
174
175
176 PROCEDURE validate_inf_id(x_return_status OUT NOCOPY VARCHAR2,
177 p_ilsv_rec IN ilsv_rec_type) IS
178
179 l_dummy_var VARCHAR2(1) := '?';
180 CURSOR l_ilsv_csr IS
181 SELECT 'x'
182 FROM OKL_INVOICE_FORMATS_V
183 WHERE id = p_ilsv_rec.inf_id;
184
185 BEGIN
186 -- initialize return status
187 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
188
189 -- data is required
190 IF(p_ilsv_rec.inf_id IS NULL) OR (p_ilsv_rec.inf_id = Okl_Api.G_MISS_NUM) THEN
191 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
192 p_msg_name => g_required_value,
193 p_token1 => g_col_name_token,
194 p_token1_value => 'inf_id');
195
196 -- notify caller of an error
197 x_return_status := Okl_Api.G_RET_STS_ERROR;
198
199 -- halt further validation of this column
200 -- RAISE G_EXCEPTION_HALT_VALIDATION;
201 END IF;
202
203 -- enforce foreign key
204 OPEN l_ilsv_csr;
205 FETCH l_ilsv_csr INTO l_dummy_var;
206 CLOSE l_ilsv_csr;
207
208 -- if l_dummy_var is still set to default, data was not found
209 IF(l_dummy_var = '?') THEN
210 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
211 p_msg_name => g_no_parent_record,
212 p_token1 => g_col_name_token,
213 p_token1_value => 'inf_id',
214 p_token2 => g_child_table_token,
215 p_token2_value => 'OKL_INVC_FRMT_STRMS_V',
216 p_token3 => g_parent_table_token,
217 p_token3_value => 'OKL_INVOICE_FORMATS_V');
218
219 -- notify caller of an error
220 x_return_status := Okl_Api.G_RET_STS_ERROR;
221 END IF;
222
223 EXCEPTION
224 WHEN G_EXCEPTION_HALT_VALIDATION THEN
225 -- no processing necessary validation can continue with the next column
226 NULL;
227
228 WHEN OTHERS THEN
229 -- store SQL error message on message stack for caller
230 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
231 p_msg_name => g_unexpected_error,
232 p_token1 => g_sqlcode_token,
233 p_token1_value => SQLCODE,
234 p_token2 => g_sqlerrm_token,
235 p_token2_value => SQLERRM);
236
237
238 -- notify caller of an error
239 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
240
241 -- verfiy that cursor was closed
242 IF l_ilsv_csr%ISOPEN THEN
243 CLOSE l_ilsv_csr;
244 END IF;
245
246 END validate_inf_id;
247
248
249
250 PROCEDURE validate_sty_id(x_return_status OUT NOCOPY VARCHAR2,
251 p_ilsv_rec IN ilsv_rec_type) IS
252
253 l_dummy_var VARCHAR2(1) := '?';
254
255 CURSOR l_ilsv_csr IS
256 SELECT 'x'
257 FROM OKL_STRM_TYPE_V
258 WHERE id = p_ilsv_rec.sty_id;
259
260 BEGIN
261 -- initialize return status
262 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
263
264 -- data is required
265 IF(p_ilsv_rec.sty_id IS NULL) OR (p_ilsv_rec.sty_id = Okl_Api.G_MISS_NUM) THEN
266 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
267 p_msg_name => g_required_value,
268 p_token1 => g_col_name_token,
269 p_token1_value => 'Stream Name');
270
271 -- notify caller of an error
272 x_return_status := Okl_Api.G_RET_STS_ERROR;
273
274 -- halt further validation of this column
275 --RAISE G_EXCEPTION_HALT_VALIDATION;
276 END IF;
277
278 -- enforce foreign key
279
280 OPEN l_ilsv_csr;
281 FETCH l_ilsv_csr INTO l_dummy_var;
282 CLOSE l_ilsv_csr;
283
284 -- if l_dummy_var is still set to default, data was not found
285
286 IF(l_dummy_var = '?') THEN
287 Okl_Api.SET_MESSAGE(p_app_name => 'OKC',
288 p_msg_name => g_required_value,
289 p_token1 => g_col_name_token,
290 p_token1_value => 'Stream Name');
291
292 -- notify caller of an error
293 x_return_status := Okl_Api.G_RET_STS_ERROR;
294 END IF;
295
296 EXCEPTION
297 WHEN G_EXCEPTION_HALT_VALIDATION THEN
298 -- no processing necessary validation can continue with the next column
299 NULL;
300
301 WHEN OTHERS THEN
302 -- store SQL error message on message stack for caller
303 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
304 p_msg_name => g_unexpected_error,
305 p_token1 => g_sqlcode_token,
306 p_token1_value => SQLCODE,
307 p_token2 => g_sqlerrm_token,
308 p_token2_value => SQLERRM);
309
310
311 -- notify caller of an error
312 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
313
314 -- verfiy that cursor was closed
315
316 IF l_ilsv_csr%ISOPEN THEN
317 CLOSE l_ilsv_csr;
318
319 END IF;
320
321 END validate_sty_id;
322
323
324
325 ---------------------------------------------------------------------------
326 -- FUNCTION get_seq_id
327 ---------------------------------------------------------------------------
328 FUNCTION get_seq_id RETURN NUMBER IS
329 BEGIN
330 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
331 END get_seq_id;
332
333 ---------------------------------------------------------------------------
334 -- PROCEDURE qc
335 ---------------------------------------------------------------------------
336 PROCEDURE qc IS
337 BEGIN
338 NULL;
339 END qc;
340
341 ---------------------------------------------------------------------------
342 -- PROCEDURE change_version
343 ---------------------------------------------------------------------------
344 PROCEDURE change_version IS
345 BEGIN
346 NULL;
347 END change_version;
348
349 ---------------------------------------------------------------------------
350 -- PROCEDURE api_copy
351 ---------------------------------------------------------------------------
352 PROCEDURE api_copy IS
353 BEGIN
354 NULL;
355 END api_copy;
356
357 ---------------------------------------------------------------------------
358 -- FUNCTION get_rec for: OKL_INVC_FRMT_STRMS
359 ---------------------------------------------------------------------------
360 FUNCTION get_rec (
361 p_ils_rec IN ils_rec_type,
362 x_no_data_found OUT NOCOPY BOOLEAN
363 ) RETURN ils_rec_type IS
364 CURSOR ils_pk_csr (p_id IN NUMBER) IS
365 SELECT
366 ID,
367 STY_ID,
368 inf_id,
369 ILT_ID,
370 OBJECT_VERSION_NUMBER,
371 ATTRIBUTE_CATEGORY,
372 ATTRIBUTE1,
373 ATTRIBUTE2,
374 ATTRIBUTE3,
375 ATTRIBUTE4,
376 ATTRIBUTE5,
377 ATTRIBUTE6,
378 ATTRIBUTE7,
379 ATTRIBUTE8,
380 ATTRIBUTE9,
381 ATTRIBUTE10,
382 ATTRIBUTE11,
383 ATTRIBUTE12,
384 ATTRIBUTE13,
385 ATTRIBUTE14,
386 ATTRIBUTE15,
387 CREATED_BY,
388 CREATION_DATE,
389 LAST_UPDATED_BY,
390 LAST_UPDATE_DATE,
391 LAST_UPDATE_LOGIN
392 FROM OKL_INVC_FRMT_STRMS
393 WHERE OKL_INVC_FRMT_STRMS.id = p_id;
394 l_ils_pk ils_pk_csr%ROWTYPE;
395 l_ils_rec ils_rec_type;
396 BEGIN
397 x_no_data_found := TRUE;
398 -- Get current database values
399 OPEN ils_pk_csr (p_ils_rec.id);
400 FETCH ils_pk_csr INTO
401 l_ils_rec.ID,
402 l_ils_rec.STY_ID,
403 l_ils_rec.inf_id,
404 l_ils_rec.ILT_ID,
405 l_ils_rec.OBJECT_VERSION_NUMBER,
406 l_ils_rec.ATTRIBUTE_CATEGORY,
407 l_ils_rec.ATTRIBUTE1,
408 l_ils_rec.ATTRIBUTE2,
409 l_ils_rec.ATTRIBUTE3,
410 l_ils_rec.ATTRIBUTE4,
411 l_ils_rec.ATTRIBUTE5,
412 l_ils_rec.ATTRIBUTE6,
413 l_ils_rec.ATTRIBUTE7,
414 l_ils_rec.ATTRIBUTE8,
415 l_ils_rec.ATTRIBUTE9,
416 l_ils_rec.ATTRIBUTE10,
417 l_ils_rec.ATTRIBUTE11,
418 l_ils_rec.ATTRIBUTE12,
419 l_ils_rec.ATTRIBUTE13,
420 l_ils_rec.ATTRIBUTE14,
421 l_ils_rec.ATTRIBUTE15,
422 l_ils_rec.CREATED_BY,
423 l_ils_rec.CREATION_DATE,
424 l_ils_rec.LAST_UPDATED_BY,
425 l_ils_rec.LAST_UPDATE_DATE,
426 l_ils_rec.LAST_UPDATE_LOGIN;
427 x_no_data_found := ils_pk_csr%NOTFOUND;
428 CLOSE ils_pk_csr;
429 RETURN(l_ils_rec);
430 END get_rec;
431
432 FUNCTION get_rec (
433 p_ils_rec IN ils_rec_type
434 ) RETURN ils_rec_type IS
435 l_row_notfound BOOLEAN := TRUE;
436 BEGIN
437 RETURN(get_rec(p_ils_rec, l_row_notfound));
438 END get_rec;
439 ---------------------------------------------------------------------------
440 -- FUNCTION get_rec for: OKL_INVC_FRMT_STRMS_V
441 ---------------------------------------------------------------------------
442 FUNCTION get_rec (
443 p_ilsv_rec IN ilsv_rec_type,
444 x_no_data_found OUT NOCOPY BOOLEAN
445 ) RETURN ilsv_rec_type IS
446 CURSOR okl_ilsv_pk_csr (p_id IN NUMBER) IS
447 SELECT
448 ID,
449 OBJECT_VERSION_NUMBER,
450 STY_ID,
451 inf_id,
452 ILT_ID,
453 ATTRIBUTE_CATEGORY,
454 ATTRIBUTE1,
455 ATTRIBUTE2,
456 ATTRIBUTE3,
457 ATTRIBUTE4,
458 ATTRIBUTE5,
459 ATTRIBUTE6,
460 ATTRIBUTE7,
461 ATTRIBUTE8,
462 ATTRIBUTE9,
463 ATTRIBUTE10,
464 ATTRIBUTE11,
465 ATTRIBUTE12,
466 ATTRIBUTE13,
467 ATTRIBUTE14,
468 ATTRIBUTE15,
469 CREATED_BY,
470 CREATION_DATE,
471 LAST_UPDATED_BY,
472 LAST_UPDATE_DATE,
473 LAST_UPDATE_LOGIN
474 FROM OKL_INVC_FRMT_STRMS_V
475 WHERE OKL_INVC_FRMT_STRMS_v.id = p_id;
476 l_okl_ilsv_pk okl_ilsv_pk_csr%ROWTYPE;
477 l_ilsv_rec ilsv_rec_type;
478 BEGIN
479 x_no_data_found := TRUE;
480 -- Get current database values
481 OPEN okl_ilsv_pk_csr (p_ilsv_rec.id);
482 FETCH okl_ilsv_pk_csr INTO
483 l_ilsv_rec.ID,
484 l_ilsv_rec.OBJECT_VERSION_NUMBER,
485 l_ilsv_rec.STY_ID,
486 l_ilsv_rec.inf_id,
487 l_ilsv_rec.ILT_ID,
488 l_ilsv_rec.ATTRIBUTE_CATEGORY,
489 l_ilsv_rec.ATTRIBUTE1,
490 l_ilsv_rec.ATTRIBUTE2,
491 l_ilsv_rec.ATTRIBUTE3,
492 l_ilsv_rec.ATTRIBUTE4,
493 l_ilsv_rec.ATTRIBUTE5,
494 l_ilsv_rec.ATTRIBUTE6,
495 l_ilsv_rec.ATTRIBUTE7,
496 l_ilsv_rec.ATTRIBUTE8,
497 l_ilsv_rec.ATTRIBUTE9,
498 l_ilsv_rec.ATTRIBUTE10,
499 l_ilsv_rec.ATTRIBUTE11,
500 l_ilsv_rec.ATTRIBUTE12,
501 l_ilsv_rec.ATTRIBUTE13,
502 l_ilsv_rec.ATTRIBUTE14,
503 l_ilsv_rec.ATTRIBUTE15,
504 l_ilsv_rec.CREATED_BY,
505 l_ilsv_rec.CREATION_DATE,
506 l_ilsv_rec.LAST_UPDATED_BY,
507 l_ilsv_rec.LAST_UPDATE_DATE,
508 l_ilsv_rec.LAST_UPDATE_LOGIN;
509 x_no_data_found := okl_ilsv_pk_csr%NOTFOUND;
510 CLOSE okl_ilsv_pk_csr;
511 RETURN(l_ilsv_rec);
512 END get_rec;
513
514 FUNCTION get_rec (
515 p_ilsv_rec IN ilsv_rec_type
516 ) RETURN ilsv_rec_type IS
517 l_row_notfound BOOLEAN := TRUE;
518 BEGIN
519 RETURN(get_rec(p_ilsv_rec, l_row_notfound));
520 END get_rec;
521
522 -----------------------------------------------------------
523 -- FUNCTION null_out_defaults for: OKL_INVC_FRMT_STRMS_V --
524 -----------------------------------------------------------
525 FUNCTION null_out_defaults (
526 p_ilsv_rec IN ilsv_rec_type
527 ) RETURN ilsv_rec_type IS
528 l_ilsv_rec ilsv_rec_type := p_ilsv_rec;
529 BEGIN
530 IF (l_ilsv_rec.object_version_number = Okl_Api.G_MISS_NUM) THEN
531 l_ilsv_rec.object_version_number := NULL;
532 END IF;
533 IF (l_ilsv_rec.sty_id = Okl_Api.G_MISS_NUM) THEN
534 l_ilsv_rec.sty_id := NULL;
535 END IF;
536 IF (l_ilsv_rec.inf_id = Okl_Api.G_MISS_NUM) THEN
537 l_ilsv_rec.inf_id := NULL;
538 END IF;
539 IF (l_ilsv_rec.ilt_id = Okl_Api.G_MISS_NUM) THEN
540 l_ilsv_rec.ilt_id := NULL;
541 END IF;
542 IF (l_ilsv_rec.attribute_category = Okl_Api.G_MISS_CHAR) THEN
543 l_ilsv_rec.attribute_category := NULL;
544 END IF;
545 IF (l_ilsv_rec.attribute1 = Okl_Api.G_MISS_CHAR) THEN
546 l_ilsv_rec.attribute1 := NULL;
547 END IF;
548 IF (l_ilsv_rec.attribute2 = Okl_Api.G_MISS_CHAR) THEN
549 l_ilsv_rec.attribute2 := NULL;
550 END IF;
551 IF (l_ilsv_rec.attribute3 = Okl_Api.G_MISS_CHAR) THEN
552 l_ilsv_rec.attribute3 := NULL;
553 END IF;
554 IF (l_ilsv_rec.attribute4 = Okl_Api.G_MISS_CHAR) THEN
555 l_ilsv_rec.attribute4 := NULL;
556 END IF;
557 IF (l_ilsv_rec.attribute5 = Okl_Api.G_MISS_CHAR) THEN
558 l_ilsv_rec.attribute5 := NULL;
559 END IF;
560 IF (l_ilsv_rec.attribute6 = Okl_Api.G_MISS_CHAR) THEN
561 l_ilsv_rec.attribute6 := NULL;
562 END IF;
563 IF (l_ilsv_rec.attribute7 = Okl_Api.G_MISS_CHAR) THEN
564 l_ilsv_rec.attribute7 := NULL;
565 END IF;
566 IF (l_ilsv_rec.attribute8 = Okl_Api.G_MISS_CHAR) THEN
567 l_ilsv_rec.attribute8 := NULL;
568 END IF;
569 IF (l_ilsv_rec.attribute9 = Okl_Api.G_MISS_CHAR) THEN
570 l_ilsv_rec.attribute9 := NULL;
571 END IF;
572 IF (l_ilsv_rec.attribute10 = Okl_Api.G_MISS_CHAR) THEN
573 l_ilsv_rec.attribute10 := NULL;
574 END IF;
575 IF (l_ilsv_rec.attribute11 = Okl_Api.G_MISS_CHAR) THEN
576 l_ilsv_rec.attribute11 := NULL;
577 END IF;
578 IF (l_ilsv_rec.attribute12 = Okl_Api.G_MISS_CHAR) THEN
579 l_ilsv_rec.attribute12 := NULL;
580 END IF;
581 IF (l_ilsv_rec.attribute13 = Okl_Api.G_MISS_CHAR) THEN
582 l_ilsv_rec.attribute13 := NULL;
583 END IF;
584 IF (l_ilsv_rec.attribute14 = Okl_Api.G_MISS_CHAR) THEN
585 l_ilsv_rec.attribute14 := NULL;
586 END IF;
587 IF (l_ilsv_rec.attribute15 = Okl_Api.G_MISS_CHAR) THEN
588 l_ilsv_rec.attribute15 := NULL;
589 END IF;
590 IF (l_ilsv_rec.created_by = Okl_Api.G_MISS_NUM) THEN
591 l_ilsv_rec.created_by := NULL;
592 END IF;
593 IF (l_ilsv_rec.creation_date = Okl_Api.G_MISS_DATE) THEN
594 l_ilsv_rec.creation_date := NULL;
595 END IF;
596 IF (l_ilsv_rec.last_updated_by = Okl_Api.G_MISS_NUM) THEN
597 l_ilsv_rec.last_updated_by := NULL;
598 END IF;
599 IF (l_ilsv_rec.last_update_date = Okl_Api.G_MISS_DATE) THEN
600 l_ilsv_rec.last_update_date := NULL;
601 END IF;
602 IF (l_ilsv_rec.last_update_login = Okl_Api.G_MISS_NUM) THEN
603 l_ilsv_rec.last_update_login := NULL;
604 END IF;
605 RETURN(l_ilsv_rec);
606 END null_out_defaults;
607 ---------------------------------------------------------------------------
608 -- PROCEDURE Validate_Attributes
609 ---------------------------------------------------------------------------
610 ---------------------------------------------------
611 -- Validate_Attributes for:OKL_INVC_FRMT_STRMS_V --
612 ---------------------------------------------------
613 FUNCTION Validate_Attributes (
614 p_ilsv_rec IN ilsv_rec_type
615 ) RETURN VARCHAR2 IS
616 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
617 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
618 BEGIN
619 -- Call each column level validation
620
621 validate_id(x_return_status => l_return_status,
622 p_ilsv_rec => p_ilsv_rec);
623
624 -- Store the highest degree of error
625 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
626 IF(x_return_status <> Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
627 x_return_status := l_return_status;
628 END IF;
629 END IF;
630
631 validate_object_version_number(x_return_status => l_return_status,
632 p_ilsv_rec => p_ilsv_rec);
633
634 -- Store the highest degree of error
635 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
636 IF(x_return_status <> Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
637 x_return_status := l_return_status;
638 END IF;
639 END IF;
640
641 validate_sty_id(x_return_status => l_return_status,
642 p_ilsv_rec => p_ilsv_rec);
643
644 -- Store the highest degree of error
645 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
646 IF(x_return_status <> Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
647 x_return_status := l_return_status;
648 END IF;
649 END IF;
650
651 validate_inf_id(x_return_status => l_return_status,
652 p_ilsv_rec => p_ilsv_rec);
653
654 -- Store the highest degree of error
655 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
656 IF(x_return_status <> Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
657 x_return_status := l_return_status;
658 END IF;
659 END IF;
660
661 validate_ilt_id(x_return_status => l_return_status,
662 p_ilsv_rec => p_ilsv_rec);
663
664 -- Store the highest degree of error
665 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
666 IF(x_return_status <> Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
667 x_return_status := l_return_status;
668 END IF;
669 END IF;
670
671 RETURN(l_return_status);
672 END Validate_Attributes;
673
674 ---------------------------------------------------------------------------
675 -- PROCEDURE Validate_Record
676 ---------------------------------------------------------------------------
677 -----------------------------------------------
678 -- Validate_Record for:OKL_INVC_FRMT_STRMS_V --
679 -----------------------------------------------
680 FUNCTION Validate_Record (
681 p_ilsv_rec IN ilsv_rec_type
682 ) RETURN VARCHAR2 IS
683 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
684 BEGIN
685 l_return_status := IS_UNIQUE(p_ilsv_rec);
686 RETURN (l_return_status);
687 END Validate_Record;
688
689 ---------------------------------------------------------------------------
690 -- PROCEDURE Migrate
691 ---------------------------------------------------------------------------
692 PROCEDURE migrate (
693 p_from IN ilsv_rec_type,
694 p_to OUT NOCOPY ils_rec_type
695 ) IS
696 BEGIN
697 p_to.id := p_from.id;
698 p_to.sty_id := p_from.sty_id;
699 p_to.inf_id := p_from.inf_id;
700 p_to.ilt_id := p_from.ilt_id;
701 p_to.object_version_number := p_from.object_version_number;
702 p_to.attribute_category := p_from.attribute_category;
703 p_to.attribute1 := p_from.attribute1;
704 p_to.attribute2 := p_from.attribute2;
705 p_to.attribute3 := p_from.attribute3;
706 p_to.attribute4 := p_from.attribute4;
707 p_to.attribute5 := p_from.attribute5;
708 p_to.attribute6 := p_from.attribute6;
709 p_to.attribute7 := p_from.attribute7;
710 p_to.attribute8 := p_from.attribute8;
711 p_to.attribute9 := p_from.attribute9;
712 p_to.attribute10 := p_from.attribute10;
713 p_to.attribute11 := p_from.attribute11;
714 p_to.attribute12 := p_from.attribute12;
715 p_to.attribute13 := p_from.attribute13;
716 p_to.attribute14 := p_from.attribute14;
717 p_to.attribute15 := p_from.attribute15;
718 p_to.created_by := p_from.created_by;
719 p_to.creation_date := p_from.creation_date;
720 p_to.last_updated_by := p_from.last_updated_by;
721 p_to.last_update_date := p_from.last_update_date;
722 p_to.last_update_login := p_from.last_update_login;
723 END migrate;
724 PROCEDURE migrate (
725 p_from IN ils_rec_type,
726 p_to OUT NOCOPY ilsv_rec_type
727 ) IS
728 BEGIN
729 p_to.id := p_from.id;
730 p_to.sty_id := p_from.sty_id;
731 p_to.inf_id := p_from.inf_id;
732 p_to.ilt_id := p_from.ilt_id;
733 p_to.object_version_number := p_from.object_version_number;
734 p_to.attribute_category := p_from.attribute_category;
735 p_to.attribute1 := p_from.attribute1;
736 p_to.attribute2 := p_from.attribute2;
737 p_to.attribute3 := p_from.attribute3;
738 p_to.attribute4 := p_from.attribute4;
739 p_to.attribute5 := p_from.attribute5;
740 p_to.attribute6 := p_from.attribute6;
741 p_to.attribute7 := p_from.attribute7;
742 p_to.attribute8 := p_from.attribute8;
743 p_to.attribute9 := p_from.attribute9;
744 p_to.attribute10 := p_from.attribute10;
745 p_to.attribute11 := p_from.attribute11;
746 p_to.attribute12 := p_from.attribute12;
747 p_to.attribute13 := p_from.attribute13;
748 p_to.attribute14 := p_from.attribute14;
749 p_to.attribute15 := p_from.attribute15;
750 p_to.created_by := p_from.created_by;
751 p_to.creation_date := p_from.creation_date;
752 p_to.last_updated_by := p_from.last_updated_by;
753 p_to.last_update_date := p_from.last_update_date;
754 p_to.last_update_login := p_from.last_update_login;
755 END migrate;
756
757 ---------------------------------------------------------------------------
758 -- PROCEDURE validate_row
759 ---------------------------------------------------------------------------
760 --------------------------------------------
761 -- validate_row for:OKL_INVC_FRMT_STRMS_V --
762 --------------------------------------------
763 PROCEDURE validate_row(
764 p_api_version IN NUMBER,
765 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2,
769 p_ilsv_rec IN ilsv_rec_type) IS
770
771 l_api_version CONSTANT NUMBER := 1;
772 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
773 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
774 l_ilsv_rec ilsv_rec_type := p_ilsv_rec;
775 l_ils_rec ils_rec_type;
776 BEGIN
777 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
778 G_PKG_NAME,
779 p_init_msg_list,
780 l_api_version,
781 p_api_version,
782 '_PVT',
783 x_return_status);
784 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
785 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
786 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
787 RAISE Okl_Api.G_EXCEPTION_ERROR;
788 END IF;
789 --- Validate all non-missing attributes (Item Level Validation)
790 l_return_status := Validate_Attributes(l_ilsv_rec);
791 --- If any errors happen abort API
792 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
793 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
794 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
795 RAISE Okl_Api.G_EXCEPTION_ERROR;
796 END IF;
797 l_return_status := Validate_Record(l_ilsv_rec);
798 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
799 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
800 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
801 RAISE Okl_Api.G_EXCEPTION_ERROR;
802 END IF;
803 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
804 EXCEPTION
805 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
806 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
807 (
808 l_api_name,
809 G_PKG_NAME,
810 'okl_api.G_RET_STS_ERROR',
811 x_msg_count,
812 x_msg_data,
813 '_PVT'
814 );
815 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
816 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
817 (
818 l_api_name,
819 G_PKG_NAME,
820 'okl_api.G_RET_STS_UNEXP_ERROR',
821 x_msg_count,
822 x_msg_data,
823 '_PVT'
824 );
825 WHEN OTHERS THEN
826 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
827 (
828 l_api_name,
829 G_PKG_NAME,
830 'OTHERS',
831 x_msg_count,
832 x_msg_data,
833 '_PVT'
834 );
835 END validate_row;
836 ------------------------------------------
837 -- PL/SQL TBL validate_row for:ILSV_TBL --
838 ------------------------------------------
839 PROCEDURE validate_row(
840 p_api_version IN NUMBER,
841 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845 p_ilsv_tbl IN ilsv_tbl_type) IS
846
847 l_api_version CONSTANT NUMBER := 1;
848 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
849 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
850 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
851 i NUMBER := 0;
852 BEGIN
853 Okl_Api.init_msg_list(p_init_msg_list);
854 -- Make sure PL/SQL table has records in it before passing
855 IF (p_ilsv_tbl.COUNT > 0) THEN
856 i := p_ilsv_tbl.FIRST;
857 LOOP
858 validate_row (
859 p_api_version => p_api_version,
860 p_init_msg_list => Okl_Api.G_FALSE,
861 x_return_status => x_return_status,
862 x_msg_count => x_msg_count,
863 x_msg_data => x_msg_data,
864 p_ilsv_rec => p_ilsv_tbl(i));
865 -- Store the highest degree of error
866 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
867 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
868 l_overall_status := x_return_status;
869 END IF;
870 END IF;
871
872 EXIT WHEN (i = p_ilsv_tbl.LAST OR x_return_status <> OKL_API.G_RET_STS_SUCCESS); -- added OR condition for bug 9482832
873 i := p_ilsv_tbl.NEXT(i);
874 END LOOP;
875 -- return overall status
876 x_return_status := l_overall_status;
877 END IF;
878 EXCEPTION
879 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
880 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
881 (
882 l_api_name,
883 G_PKG_NAME,
884 'okl_api.G_RET_STS_ERROR',
885 x_msg_count,
886 x_msg_data,
887 '_PVT'
888 );
889 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
890 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
891 (
892 l_api_name,
893 G_PKG_NAME,
894 'okl_api.G_RET_STS_UNEXP_ERROR',
895 x_msg_count,
896 x_msg_data,
897 '_PVT'
898 );
899 WHEN OTHERS THEN
900 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
901 (
902 l_api_name,
903 G_PKG_NAME,
904 'OTHERS',
905 x_msg_count,
906 x_msg_data,
907 '_PVT'
908 );
909 END validate_row;
910
911 ---------------------------------------------------------------------------
912 -- PROCEDURE insert_row
913 ---------------------------------------------------------------------------
914 ----------------------------------------
915 -- insert_row for:OKL_INVC_FRMT_STRMS --
916 ----------------------------------------
917 PROCEDURE insert_row(
918 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
919 x_return_status OUT NOCOPY VARCHAR2,
920 x_msg_count OUT NOCOPY NUMBER,
921 x_msg_data OUT NOCOPY VARCHAR2,
922 p_ils_rec IN ils_rec_type,
923 x_ils_rec OUT NOCOPY ils_rec_type) IS
924
925 l_api_version CONSTANT NUMBER := 1;
926 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_insert_row';
927 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
928 l_ils_rec ils_rec_type := p_ils_rec;
929 l_def_ils_rec ils_rec_type;
930 --------------------------------------------
931 -- Set_Attributes for:OKL_INVC_FRMT_STRMS --
932 --------------------------------------------
933 FUNCTION Set_Attributes (
934 p_ils_rec IN ils_rec_type,
935 x_ils_rec OUT NOCOPY ils_rec_type
936 ) RETURN VARCHAR2 IS
937 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
938 BEGIN
939 x_ils_rec := p_ils_rec;
940 RETURN(l_return_status);
941 END Set_Attributes;
942 BEGIN
943 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
944 p_init_msg_list,
945 '_PVT',
946 x_return_status);
947 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
948 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
949 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
950 RAISE Okl_Api.G_EXCEPTION_ERROR;
951 END IF;
952 --- Setting item attributes
953 l_return_status := Set_Attributes(
954 p_ils_rec, -- IN
955 l_ils_rec); -- OUT
956 --- If any errors happen abort API
957 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
958 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
959 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
960 RAISE Okl_Api.G_EXCEPTION_ERROR;
961 END IF;
962 INSERT INTO OKL_INVC_FRMT_STRMS(
963 id,
964 sty_id,
965 inf_id,
966 ilt_id,
967 object_version_number,
968 attribute_category,
969 attribute1,
970 attribute2,
971 attribute3,
972 attribute4,
973 attribute5,
974 attribute6,
975 attribute7,
976 attribute8,
977 attribute9,
978 attribute10,
979 attribute11,
980 attribute12,
981 attribute13,
982 attribute14,
983 attribute15,
984 created_by,
985 creation_date,
986 last_updated_by,
987 last_update_date,
988 last_update_login)
989 VALUES (
990 l_ils_rec.id,
991 l_ils_rec.sty_id,
992 l_ils_rec.inf_id,
993 l_ils_rec.ilt_id,
994 l_ils_rec.object_version_number,
995 l_ils_rec.attribute_category,
996 l_ils_rec.attribute1,
997 l_ils_rec.attribute2,
998 l_ils_rec.attribute3,
999 l_ils_rec.attribute4,
1000 l_ils_rec.attribute5,
1001 l_ils_rec.attribute6,
1002 l_ils_rec.attribute7,
1003 l_ils_rec.attribute8,
1004 l_ils_rec.attribute9,
1005 l_ils_rec.attribute10,
1006 l_ils_rec.attribute11,
1007 l_ils_rec.attribute12,
1008 l_ils_rec.attribute13,
1009 l_ils_rec.attribute14,
1010 l_ils_rec.attribute15,
1011 l_ils_rec.created_by,
1012 l_ils_rec.creation_date,
1013 l_ils_rec.last_updated_by,
1014 l_ils_rec.last_update_date,
1015 l_ils_rec.last_update_login);
1016 -- Set OUT values
1017 x_ils_rec := l_ils_rec;
1018 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1019 EXCEPTION
1020 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1021 x_return_status := 'U';
1022 /*
1023 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1024 (
1025 l_api_name,
1026 G_PKG_NAME,
1027 'okl_api.G_RET_STS_ERROR',
1028 x_msg_count,
1029 x_msg_data,
1030 '_PVT'
1031 );
1032 */
1033 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1034 x_return_status := 'U';
1035 /*
1036 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1037 (
1038 l_api_name,
1039 G_PKG_NAME,
1040 'okl_api.G_RET_STS_UNEXP_ERROR',
1041 x_msg_count,
1042 x_msg_data,
1043 '_PVT'
1044 );
1045 */
1046 WHEN OTHERS THEN
1047 x_return_status := 'U';
1048 /*
1049 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1050 (
1051 l_api_name,
1052 G_PKG_NAME,
1053 'OTHERS',
1054 x_msg_count,
1055 x_msg_data,
1056 '_PVT'
1057 );
1058 */
1059 END insert_row;
1060 ------------------------------------------
1061 -- insert_row for:OKL_INVC_FRMT_STRMS_V --
1062 ------------------------------------------
1063 PROCEDURE insert_row(
1064 p_api_version IN NUMBER,
1065 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1066 x_return_status OUT NOCOPY VARCHAR2,
1067 x_msg_count OUT NOCOPY NUMBER,
1068 x_msg_data OUT NOCOPY VARCHAR2,
1069 p_ilsv_rec IN ilsv_rec_type,
1070 x_ilsv_rec OUT NOCOPY ilsv_rec_type) IS
1071
1072 l_api_version CONSTANT NUMBER := 1;
1073 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1074 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1075 l_ilsv_rec ilsv_rec_type;
1076 l_def_ilsv_rec ilsv_rec_type;
1077 l_ils_rec ils_rec_type;
1078 lx_ils_rec ils_rec_type;
1079 -------------------------------
1080 -- FUNCTION fill_who_columns --
1081 -------------------------------
1082 FUNCTION fill_who_columns (
1083 p_ilsv_rec IN ilsv_rec_type
1084 ) RETURN ilsv_rec_type IS
1085 l_ilsv_rec ilsv_rec_type := p_ilsv_rec;
1086 BEGIN
1087 l_ilsv_rec.CREATION_DATE := SYSDATE;
1088 l_ilsv_rec.CREATED_BY := Fnd_Global.USER_ID;
1089 l_ilsv_rec.LAST_UPDATE_DATE := SYSDATE;
1090 l_ilsv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1091 l_ilsv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1092 RETURN(l_ilsv_rec);
1093 END fill_who_columns;
1094 ----------------------------------------------
1095 -- Set_Attributes for:OKL_INVC_FRMT_STRMS_V --
1096 ----------------------------------------------
1097 FUNCTION Set_Attributes (
1098 p_ilsv_rec IN ilsv_rec_type,
1099 x_ilsv_rec OUT NOCOPY ilsv_rec_type
1100 ) RETURN VARCHAR2 IS
1101 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1102 BEGIN
1103 x_ilsv_rec := p_ilsv_rec;
1104 x_ilsv_rec.OBJECT_VERSION_NUMBER := 1;
1105 RETURN(l_return_status);
1106 END Set_Attributes;
1107 BEGIN
1108 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1109 G_PKG_NAME,
1110 p_init_msg_list,
1111 l_api_version,
1112 p_api_version,
1113 '_PVT',
1114 x_return_status);
1115 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1117 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1118 RAISE Okl_Api.G_EXCEPTION_ERROR;
1119 END IF;
1120 l_ilsv_rec := null_out_defaults(p_ilsv_rec);
1121 -- Set primary key value
1122 l_ilsv_rec.ID := get_seq_id;
1123 --- Setting item attributes
1124 l_return_status := Set_Attributes(
1125 l_ilsv_rec, -- IN
1126 l_def_ilsv_rec); -- OUT
1127 --- If any errors happen abort API
1128 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1129 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1130 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1131 RAISE Okl_Api.G_EXCEPTION_ERROR;
1132 END IF;
1133 l_def_ilsv_rec := fill_who_columns(l_def_ilsv_rec);
1134 --- Validate all non-missing attributes (Item Level Validation)
1135 l_return_status := Validate_Attributes(l_def_ilsv_rec);
1136 --- If any errors happen abort API
1137 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1138 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1139 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1140 RAISE Okl_Api.G_EXCEPTION_ERROR;
1141 END IF;
1142 l_return_status := Validate_Record(l_def_ilsv_rec);
1143 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1144 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1145 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1146 RAISE Okl_Api.G_EXCEPTION_ERROR;
1147 END IF;
1148 --------------------------------------
1149 -- Move VIEW record to "Child" records
1150 --------------------------------------
1151 migrate(l_def_ilsv_rec, l_ils_rec);
1152 --------------------------------------------
1153 -- Call the INSERT_ROW for each child record
1154 --------------------------------------------
1155 insert_row(
1156 p_init_msg_list,
1157 x_return_status,
1158 x_msg_count,
1159 x_msg_data,
1160 l_ils_rec,
1161 lx_ils_rec
1162 );
1163 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1164 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1165 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1166 RAISE Okl_Api.G_EXCEPTION_ERROR;
1167 END IF;
1168 migrate(lx_ils_rec, l_def_ilsv_rec);
1169 -- Set OUT values
1170 x_ilsv_rec := l_def_ilsv_rec;
1171 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1172 EXCEPTION
1173 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1174 x_return_status := 'U';
1175 /*
1176 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1177 (
1178 l_api_name,
1179 G_PKG_NAME,
1180 'okl_api.G_RET_STS_ERROR',
1181 x_msg_count,
1182 x_msg_data,
1183 '_PVT'
1184 );
1185 */
1186 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1187 x_return_status := 'U';
1188 /*
1189 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1190 (
1191 l_api_name,
1192 G_PKG_NAME,
1193 'okl_api.G_RET_STS_UNEXP_ERROR',
1194 x_msg_count,
1195 x_msg_data,
1196 '_PVT'
1197 );
1198 */
1199 WHEN OTHERS THEN
1200 x_return_status := 'U';
1201 /*
1202 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1203 (
1204 l_api_name,
1205 G_PKG_NAME,
1206 'OTHERS',
1207 x_msg_count,
1208 x_msg_data,
1209 '_PVT'
1210 );
1211 */
1212 END insert_row;
1213 ----------------------------------------
1214 -- PL/SQL TBL insert_row for:ILSV_TBL --
1215 ----------------------------------------
1216 PROCEDURE insert_row(
1217 p_api_version IN NUMBER,
1218 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1219 x_return_status OUT NOCOPY VARCHAR2,
1220 x_msg_count OUT NOCOPY NUMBER,
1221 x_msg_data OUT NOCOPY VARCHAR2,
1222 p_ilsv_tbl IN ilsv_tbl_type,
1223 x_ilsv_tbl OUT NOCOPY ilsv_tbl_type) IS
1224
1225 l_api_version CONSTANT NUMBER := 1;
1226 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1227 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1228 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1229 i NUMBER := 0;
1230 BEGIN
1231 Okl_Api.init_msg_list(p_init_msg_list);
1232 -- Make sure PL/SQL table has records in it before passing
1233 IF (p_ilsv_tbl.COUNT > 0) THEN
1234 i := p_ilsv_tbl.FIRST;
1235 LOOP
1236 insert_row (
1237 p_api_version => p_api_version,
1238 p_init_msg_list => Okl_Api.G_FALSE,
1239 x_return_status => x_return_status,
1240 x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data,
1242 p_ilsv_rec => p_ilsv_tbl(i),
1243 x_ilsv_rec => x_ilsv_tbl(i));
1244 -- Store the highest degree of error
1245 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1246 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1247 l_overall_status := x_return_status;
1248 END IF;
1249 END IF;
1250 EXIT WHEN (i = p_ilsv_tbl.LAST OR x_return_status <> Okl_Api.G_RET_STS_SUCCESS); -- Added for bug 9482832
1251 i := p_ilsv_tbl.NEXT(i);
1252 END LOOP;
1253 -- return overall status
1254 x_return_status := l_overall_status;
1255 END IF;
1256 EXCEPTION
1257 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1258 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1259 (
1260 l_api_name,
1261 G_PKG_NAME,
1262 'okl_api.G_RET_STS_ERROR',
1263 x_msg_count,
1264 x_msg_data,
1265 '_PVT'
1266 );
1267 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1268 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1269 (
1270 l_api_name,
1271 G_PKG_NAME,
1272 'okl_api.G_RET_STS_UNEXP_ERROR',
1273 x_msg_count,
1274 x_msg_data,
1275 '_PVT'
1276 );
1277 WHEN OTHERS THEN
1278 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1279 (
1280 l_api_name,
1281 G_PKG_NAME,
1282 'OTHERS',
1283 x_msg_count,
1284 x_msg_data,
1285 '_PVT'
1286 );
1287 END insert_row;
1288
1289 ---------------------------------------------------------------------------
1290 -- PROCEDURE lock_row
1291 ---------------------------------------------------------------------------
1292 --------------------------------------
1293 -- lock_row for:OKL_INVC_FRMT_STRMS --
1294 --------------------------------------
1295 PROCEDURE lock_row(
1296 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_msg_count OUT NOCOPY NUMBER,
1299 x_msg_data OUT NOCOPY VARCHAR2,
1300 p_ils_rec IN ils_rec_type) IS
1301
1302 E_Resource_Busy EXCEPTION;
1303 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1304 CURSOR lock_csr (p_ils_rec IN ils_rec_type) IS
1305 SELECT OBJECT_VERSION_NUMBER
1306 FROM OKL_INVC_FRMT_STRMS
1307 WHERE ID = p_ils_rec.id
1308 AND OBJECT_VERSION_NUMBER = p_ils_rec.object_version_number
1309 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1310
1311 CURSOR lchk_csr (p_ils_rec IN ils_rec_type) IS
1312 SELECT OBJECT_VERSION_NUMBER
1313 FROM OKL_INVC_FRMT_STRMS
1314 WHERE ID = p_ils_rec.id;
1315 l_api_version CONSTANT NUMBER := 1;
1316 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_lock_row';
1317 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1318 l_object_version_number OKL_INVC_FRMT_STRMS.OBJECT_VERSION_NUMBER%TYPE;
1319 lc_object_version_number OKL_INVC_FRMT_STRMS.OBJECT_VERSION_NUMBER%TYPE;
1320 l_row_notfound BOOLEAN := FALSE;
1321 lc_row_notfound BOOLEAN := FALSE;
1322 BEGIN
1323 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1324 p_init_msg_list,
1325 '_PVT',
1326 x_return_status);
1327 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1330 RAISE Okl_Api.G_EXCEPTION_ERROR;
1331 END IF;
1332 BEGIN
1333 OPEN lock_csr(p_ils_rec);
1334 FETCH lock_csr INTO l_object_version_number;
1335 l_row_notfound := lock_csr%NOTFOUND;
1336 CLOSE lock_csr;
1337 EXCEPTION
1338 WHEN E_Resource_Busy THEN
1339 IF (lock_csr%ISOPEN) THEN
1340 CLOSE lock_csr;
1341 END IF;
1342 Okl_Api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1343 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
1344 END;
1345
1346 IF ( l_row_notfound ) THEN
1347 OPEN lchk_csr(p_ils_rec);
1348 FETCH lchk_csr INTO lc_object_version_number;
1349 lc_row_notfound := lchk_csr%NOTFOUND;
1350 CLOSE lchk_csr;
1351 END IF;
1352 IF (lc_row_notfound) THEN
1353 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1354 RAISE Okl_Api.G_EXCEPTION_ERROR;
1355 ELSIF lc_object_version_number > p_ils_rec.object_version_number THEN
1356 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1357 RAISE Okl_Api.G_EXCEPTION_ERROR;
1358 ELSIF lc_object_version_number <> p_ils_rec.object_version_number THEN
1359 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1360 RAISE Okl_Api.G_EXCEPTION_ERROR;
1361 ELSIF lc_object_version_number = -1 THEN
1362 Okl_Api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1363 RAISE Okl_Api.G_EXCEPTION_ERROR;
1364 END IF;
1365 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1366 EXCEPTION
1367 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1368 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1369 (
1370 l_api_name,
1371 G_PKG_NAME,
1372 'okl_api.G_RET_STS_ERROR',
1373 x_msg_count,
1374 x_msg_data,
1375 '_PVT'
1376 );
1377 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1378 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1379 (
1380 l_api_name,
1381 G_PKG_NAME,
1382 'okl_api.G_RET_STS_UNEXP_ERROR',
1383 x_msg_count,
1384 x_msg_data,
1385 '_PVT'
1386 );
1387 WHEN OTHERS THEN
1388 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1389 (
1390 l_api_name,
1391 G_PKG_NAME,
1392 'OTHERS',
1393 x_msg_count,
1394 x_msg_data,
1395 '_PVT'
1396 );
1397 END lock_row;
1398 ----------------------------------------
1399 -- lock_row for:OKL_INVC_FRMT_STRMS_V --
1400 ----------------------------------------
1401 PROCEDURE lock_row(
1402 p_api_version IN NUMBER,
1403 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1404 x_return_status OUT NOCOPY VARCHAR2,
1405 x_msg_count OUT NOCOPY NUMBER,
1406 x_msg_data OUT NOCOPY VARCHAR2,
1407 p_ilsv_rec IN ilsv_rec_type) IS
1408
1409 l_api_version CONSTANT NUMBER := 1;
1410 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1411 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1412 l_ils_rec ils_rec_type;
1413 BEGIN
1414 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1415 G_PKG_NAME,
1416 p_init_msg_list,
1417 l_api_version,
1418 p_api_version,
1419 '_PVT',
1420 x_return_status);
1421 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1422 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1423 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1424 RAISE Okl_Api.G_EXCEPTION_ERROR;
1425 END IF;
1426 --------------------------------------
1427 -- Move VIEW record to "Child" records
1428 --------------------------------------
1429 migrate(p_ilsv_rec, l_ils_rec);
1430 --------------------------------------------
1431 -- Call the LOCK_ROW for each child record
1432 --------------------------------------------
1433 lock_row(
1434 p_init_msg_list,
1435 x_return_status,
1436 x_msg_count,
1437 x_msg_data,
1438 l_ils_rec
1439 );
1440 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1441 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1442 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1443 RAISE Okl_Api.G_EXCEPTION_ERROR;
1444 END IF;
1445 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1446 EXCEPTION
1447 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1448 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1449 (
1450 l_api_name,
1451 G_PKG_NAME,
1452 'okl_api.G_RET_STS_ERROR',
1453 x_msg_count,
1454 x_msg_data,
1455 '_PVT'
1456 );
1457 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1458 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1459 (
1460 l_api_name,
1461 G_PKG_NAME,
1462 'okl_api.G_RET_STS_UNEXP_ERROR',
1463 x_msg_count,
1464 x_msg_data,
1465 '_PVT'
1466 );
1467 WHEN OTHERS THEN
1468 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1469 (
1470 l_api_name,
1471 G_PKG_NAME,
1472 'OTHERS',
1473 x_msg_count,
1474 x_msg_data,
1475 '_PVT'
1476 );
1477 END lock_row;
1478 --------------------------------------
1479 -- PL/SQL TBL lock_row for:ILSV_TBL --
1480 --------------------------------------
1481 PROCEDURE lock_row(
1482 p_api_version IN NUMBER,
1483 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1484 x_return_status OUT NOCOPY VARCHAR2,
1485 x_msg_count OUT NOCOPY NUMBER,
1486 x_msg_data OUT NOCOPY VARCHAR2,
1487 p_ilsv_tbl IN ilsv_tbl_type) IS
1488
1489 l_api_version CONSTANT NUMBER := 1;
1490 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1491 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1492 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1493 i NUMBER := 0;
1494 BEGIN
1495 Okl_Api.init_msg_list(p_init_msg_list);
1496 -- Make sure PL/SQL table has records in it before passing
1497 IF (p_ilsv_tbl.COUNT > 0) THEN
1498 i := p_ilsv_tbl.FIRST;
1499 LOOP
1500 lock_row (
1501 p_api_version => p_api_version,
1502 p_init_msg_list => Okl_Api.G_FALSE,
1503 x_return_status => x_return_status,
1504 x_msg_count => x_msg_count,
1505 x_msg_data => x_msg_data,
1506 p_ilsv_rec => p_ilsv_tbl(i));
1507 -- Store the highest degree of error
1508 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1509 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1510 l_overall_status := x_return_status;
1511 END IF;
1512 END IF;
1513 EXIT WHEN (i = p_ilsv_tbl.LAST);
1514 i := p_ilsv_tbl.NEXT(i);
1515 END LOOP;
1516 -- return overall status
1517 x_return_status := l_overall_status;
1518 END IF;
1519 EXCEPTION
1520 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1521 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1522 (
1523 l_api_name,
1524 G_PKG_NAME,
1525 'okl_api.G_RET_STS_ERROR',
1526 x_msg_count,
1527 x_msg_data,
1528 '_PVT'
1529 );
1530 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1531 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1532 (
1533 l_api_name,
1534 G_PKG_NAME,
1535 'okl_api.G_RET_STS_UNEXP_ERROR',
1536 x_msg_count,
1537 x_msg_data,
1538 '_PVT'
1539 );
1540 WHEN OTHERS THEN
1541 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1542 (
1543 l_api_name,
1544 G_PKG_NAME,
1545 'OTHERS',
1546 x_msg_count,
1547 x_msg_data,
1548 '_PVT'
1549 );
1550 END lock_row;
1551
1552 ---------------------------------------------------------------------------
1553 -- PROCEDURE update_row
1554 ---------------------------------------------------------------------------
1555 ----------------------------------------
1556 -- update_row for:OKL_INVC_FRMT_STRMS --
1557 ----------------------------------------
1558 PROCEDURE update_row(
1559 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1560 x_return_status OUT NOCOPY VARCHAR2,
1561 x_msg_count OUT NOCOPY NUMBER,
1562 x_msg_data OUT NOCOPY VARCHAR2,
1563 p_ils_rec IN ils_rec_type,
1564 x_ils_rec OUT NOCOPY ils_rec_type) IS
1565
1566 l_api_version CONSTANT NUMBER := 1;
1567 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_update_row';
1568 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1569 l_ils_rec ils_rec_type := p_ils_rec;
1570 l_def_ils_rec ils_rec_type;
1571 l_row_notfound BOOLEAN := TRUE;
1572 ----------------------------------
1573 -- FUNCTION populate_new_record --
1574 ----------------------------------
1575 FUNCTION populate_new_record (
1576 p_ils_rec IN ils_rec_type,
1577 x_ils_rec OUT NOCOPY ils_rec_type
1578 ) RETURN VARCHAR2 IS
1579 l_ils_rec ils_rec_type;
1580 l_row_notfound BOOLEAN := TRUE;
1581 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1582 BEGIN
1583 x_ils_rec := p_ils_rec;
1584 -- Get current database values
1585 l_ils_rec := get_rec(p_ils_rec, l_row_notfound);
1586 IF (l_row_notfound) THEN
1587 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1588 END IF;
1589 IF (x_ils_rec.id = Okl_Api.G_MISS_NUM)
1590 THEN
1591 x_ils_rec.id := l_ils_rec.id;
1592 END IF;
1593 IF (x_ils_rec.sty_id = Okl_Api.G_MISS_NUM)
1594 THEN
1595 x_ils_rec.sty_id := l_ils_rec.sty_id;
1596 END IF;
1597 IF (x_ils_rec.inf_id = Okl_Api.G_MISS_NUM)
1598 THEN
1599 x_ils_rec.inf_id := l_ils_rec.inf_id;
1600 END IF;
1601 IF (x_ils_rec.ilt_id = Okl_Api.G_MISS_NUM)
1602 THEN
1603 x_ils_rec.ilt_id := l_ils_rec.ilt_id;
1604 END IF;
1605 IF (x_ils_rec.object_version_number = Okl_Api.G_MISS_NUM)
1606 THEN
1607 x_ils_rec.object_version_number := l_ils_rec.object_version_number;
1608 END IF;
1609 IF (x_ils_rec.attribute_category = Okl_Api.G_MISS_CHAR)
1610 THEN
1611 x_ils_rec.attribute_category := l_ils_rec.attribute_category;
1612 END IF;
1613 IF (x_ils_rec.attribute1 = Okl_Api.G_MISS_CHAR)
1614 THEN
1615 x_ils_rec.attribute1 := l_ils_rec.attribute1;
1616 END IF;
1617 IF (x_ils_rec.attribute2 = Okl_Api.G_MISS_CHAR)
1618 THEN
1619 x_ils_rec.attribute2 := l_ils_rec.attribute2;
1620 END IF;
1621 IF (x_ils_rec.attribute3 = Okl_Api.G_MISS_CHAR)
1622 THEN
1623 x_ils_rec.attribute3 := l_ils_rec.attribute3;
1624 END IF;
1625 IF (x_ils_rec.attribute4 = Okl_Api.G_MISS_CHAR)
1626 THEN
1627 x_ils_rec.attribute4 := l_ils_rec.attribute4;
1628 END IF;
1629 IF (x_ils_rec.attribute5 = Okl_Api.G_MISS_CHAR)
1630 THEN
1631 x_ils_rec.attribute5 := l_ils_rec.attribute5;
1632 END IF;
1633 IF (x_ils_rec.attribute6 = Okl_Api.G_MISS_CHAR)
1634 THEN
1635 x_ils_rec.attribute6 := l_ils_rec.attribute6;
1636 END IF;
1637 IF (x_ils_rec.attribute7 = Okl_Api.G_MISS_CHAR)
1638 THEN
1639 x_ils_rec.attribute7 := l_ils_rec.attribute7;
1640 END IF;
1641 IF (x_ils_rec.attribute8 = Okl_Api.G_MISS_CHAR)
1642 THEN
1643 x_ils_rec.attribute8 := l_ils_rec.attribute8;
1644 END IF;
1645 IF (x_ils_rec.attribute9 = Okl_Api.G_MISS_CHAR)
1646 THEN
1647 x_ils_rec.attribute9 := l_ils_rec.attribute9;
1648 END IF;
1649 IF (x_ils_rec.attribute10 = Okl_Api.G_MISS_CHAR)
1650 THEN
1651 x_ils_rec.attribute10 := l_ils_rec.attribute10;
1652 END IF;
1653 IF (x_ils_rec.attribute11 = Okl_Api.G_MISS_CHAR)
1654 THEN
1655 x_ils_rec.attribute11 := l_ils_rec.attribute11;
1656 END IF;
1657 IF (x_ils_rec.attribute12 = Okl_Api.G_MISS_CHAR)
1658 THEN
1659 x_ils_rec.attribute12 := l_ils_rec.attribute12;
1660 END IF;
1661 IF (x_ils_rec.attribute13 = Okl_Api.G_MISS_CHAR)
1662 THEN
1663 x_ils_rec.attribute13 := l_ils_rec.attribute13;
1664 END IF;
1665 IF (x_ils_rec.attribute14 = Okl_Api.G_MISS_CHAR)
1666 THEN
1667 x_ils_rec.attribute14 := l_ils_rec.attribute14;
1668 END IF;
1669 IF (x_ils_rec.attribute15 = Okl_Api.G_MISS_CHAR)
1670 THEN
1671 x_ils_rec.attribute15 := l_ils_rec.attribute15;
1672 END IF;
1673 IF (x_ils_rec.created_by = Okl_Api.G_MISS_NUM)
1674 THEN
1675 x_ils_rec.created_by := l_ils_rec.created_by;
1676 END IF;
1677 IF (x_ils_rec.creation_date = Okl_Api.G_MISS_DATE)
1678 THEN
1679 x_ils_rec.creation_date := l_ils_rec.creation_date;
1680 END IF;
1681 IF (x_ils_rec.last_updated_by = Okl_Api.G_MISS_NUM)
1682 THEN
1683 x_ils_rec.last_updated_by := l_ils_rec.last_updated_by;
1684 END IF;
1685 IF (x_ils_rec.last_update_date = Okl_Api.G_MISS_DATE)
1686 THEN
1687 x_ils_rec.last_update_date := l_ils_rec.last_update_date;
1688 END IF;
1689 IF (x_ils_rec.last_update_login = Okl_Api.G_MISS_NUM)
1690 THEN
1691 x_ils_rec.last_update_login := l_ils_rec.last_update_login;
1692 END IF;
1693 RETURN(l_return_status);
1694 END populate_new_record;
1695 --------------------------------------------
1696 -- Set_Attributes for:OKL_INVC_FRMT_STRMS --
1697 --------------------------------------------
1698 FUNCTION Set_Attributes (
1699 p_ils_rec IN ils_rec_type,
1700 x_ils_rec OUT NOCOPY ils_rec_type
1701 ) RETURN VARCHAR2 IS
1702 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1703 BEGIN
1704 x_ils_rec := p_ils_rec;
1705 RETURN(l_return_status);
1706 END Set_Attributes;
1707 BEGIN
1708 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1709 p_init_msg_list,
1710 '_PVT',
1711 x_return_status);
1712 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1713 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1714 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1715 RAISE Okl_Api.G_EXCEPTION_ERROR;
1716 END IF;
1717 --- Setting item attributes
1718 l_return_status := Set_Attributes(
1719 p_ils_rec, -- IN
1720 l_ils_rec); -- OUT
1721 --- If any errors happen abort API
1722 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1723 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1724 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1725 RAISE Okl_Api.G_EXCEPTION_ERROR;
1726 END IF;
1727 l_return_status := populate_new_record(l_ils_rec, l_def_ils_rec);
1728 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1729 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1730 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1731 RAISE Okl_Api.G_EXCEPTION_ERROR;
1732 END IF;
1733 UPDATE OKL_INVC_FRMT_STRMS
1734 SET STY_ID = l_def_ils_rec.sty_id,
1735 inf_id = l_def_ils_rec.inf_id,
1736 ILT_ID = l_def_ils_rec.ilt_id,
1737 OBJECT_VERSION_NUMBER = l_def_ils_rec.object_version_number,
1738 ATTRIBUTE_CATEGORY = l_def_ils_rec.attribute_category,
1739 ATTRIBUTE1 = l_def_ils_rec.attribute1,
1740 ATTRIBUTE2 = l_def_ils_rec.attribute2,
1741 ATTRIBUTE3 = l_def_ils_rec.attribute3,
1742 ATTRIBUTE4 = l_def_ils_rec.attribute4,
1743 ATTRIBUTE5 = l_def_ils_rec.attribute5,
1744 ATTRIBUTE6 = l_def_ils_rec.attribute6,
1745 ATTRIBUTE7 = l_def_ils_rec.attribute7,
1746 ATTRIBUTE8 = l_def_ils_rec.attribute8,
1747 ATTRIBUTE9 = l_def_ils_rec.attribute9,
1748 ATTRIBUTE10 = l_def_ils_rec.attribute10,
1749 ATTRIBUTE11 = l_def_ils_rec.attribute11,
1750 ATTRIBUTE12 = l_def_ils_rec.attribute12,
1751 ATTRIBUTE13 = l_def_ils_rec.attribute13,
1752 ATTRIBUTE14 = l_def_ils_rec.attribute14,
1753 ATTRIBUTE15 = l_def_ils_rec.attribute15,
1754 CREATED_BY = l_def_ils_rec.created_by,
1755 CREATION_DATE = l_def_ils_rec.creation_date,
1756 LAST_UPDATED_BY = l_def_ils_rec.last_updated_by,
1757 LAST_UPDATE_DATE = l_def_ils_rec.last_update_date,
1758 LAST_UPDATE_LOGIN = l_def_ils_rec.last_update_login
1759 WHERE ID = l_def_ils_rec.id;
1760
1761 x_ils_rec := l_def_ils_rec;
1762 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1763 EXCEPTION
1764 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1765 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1766 (
1767 l_api_name,
1768 G_PKG_NAME,
1769 'okl_api.G_RET_STS_ERROR',
1770 x_msg_count,
1771 x_msg_data,
1772 '_PVT'
1773 );
1774 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1775 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1776 (
1777 l_api_name,
1778 G_PKG_NAME,
1779 'okl_api.G_RET_STS_UNEXP_ERROR',
1780 x_msg_count,
1781 x_msg_data,
1782 '_PVT'
1783 );
1784 WHEN OTHERS THEN
1785 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1786 (
1787 l_api_name,
1788 G_PKG_NAME,
1789 'OTHERS',
1790 x_msg_count,
1791 x_msg_data,
1792 '_PVT'
1793 );
1794 END update_row;
1795 ------------------------------------------
1796 -- update_row for:OKL_INVC_FRMT_STRMS_V --
1797 ------------------------------------------
1798 PROCEDURE update_row(
1799 p_api_version IN NUMBER,
1800 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1801 x_return_status OUT NOCOPY VARCHAR2,
1802 x_msg_count OUT NOCOPY NUMBER,
1803 x_msg_data OUT NOCOPY VARCHAR2,
1804 p_ilsv_rec IN ilsv_rec_type,
1805 x_ilsv_rec OUT NOCOPY ilsv_rec_type) IS
1806
1807 l_api_version CONSTANT NUMBER := 1;
1808 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1809 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1810 l_ilsv_rec ilsv_rec_type := p_ilsv_rec;
1811 l_def_ilsv_rec ilsv_rec_type;
1812 l_ils_rec ils_rec_type;
1813 lx_ils_rec ils_rec_type;
1814 -------------------------------
1815 -- FUNCTION fill_who_columns --
1816 -------------------------------
1817 FUNCTION fill_who_columns (
1818 p_ilsv_rec IN ilsv_rec_type
1819 ) RETURN ilsv_rec_type IS
1820 l_ilsv_rec ilsv_rec_type := p_ilsv_rec;
1821 BEGIN
1822 l_ilsv_rec.LAST_UPDATE_DATE := SYSDATE;
1823 l_ilsv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1824 l_ilsv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1825 RETURN(l_ilsv_rec);
1826 END fill_who_columns;
1827 ----------------------------------
1828 -- FUNCTION populate_new_record --
1829 ----------------------------------
1830 FUNCTION populate_new_record (
1831 p_ilsv_rec IN ilsv_rec_type,
1832 x_ilsv_rec OUT NOCOPY ilsv_rec_type
1833 ) RETURN VARCHAR2 IS
1834 l_ilsv_rec ilsv_rec_type;
1835 l_row_notfound BOOLEAN := TRUE;
1836 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1837 BEGIN
1838 x_ilsv_rec := p_ilsv_rec;
1839 -- Get current database values
1840 l_ilsv_rec := get_rec(p_ilsv_rec, l_row_notfound);
1841 IF (l_row_notfound) THEN
1842 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1843 END IF;
1844 IF (x_ilsv_rec.id = Okl_Api.G_MISS_NUM)
1845 THEN
1846 x_ilsv_rec.id := l_ilsv_rec.id;
1847 END IF;
1848 IF (x_ilsv_rec.object_version_number = Okl_Api.G_MISS_NUM)
1849 THEN
1850 x_ilsv_rec.object_version_number := l_ilsv_rec.object_version_number;
1851 END IF;
1852 IF (x_ilsv_rec.sty_id = Okl_Api.G_MISS_NUM)
1853 THEN
1854 x_ilsv_rec.sty_id := l_ilsv_rec.sty_id;
1855 END IF;
1856 IF (x_ilsv_rec.inf_id = Okl_Api.G_MISS_NUM)
1857 THEN
1858 x_ilsv_rec.inf_id := l_ilsv_rec.inf_id;
1859 END IF;
1860 IF (x_ilsv_rec.ilt_id = Okl_Api.G_MISS_NUM)
1861 THEN
1862 x_ilsv_rec.ilt_id := l_ilsv_rec.ilt_id;
1863 END IF;
1864 IF (x_ilsv_rec.attribute_category = Okl_Api.G_MISS_CHAR)
1865 THEN
1866 x_ilsv_rec.attribute_category := l_ilsv_rec.attribute_category;
1867 END IF;
1868 IF (x_ilsv_rec.attribute1 = Okl_Api.G_MISS_CHAR)
1869 THEN
1870 x_ilsv_rec.attribute1 := l_ilsv_rec.attribute1;
1871 END IF;
1872 IF (x_ilsv_rec.attribute2 = Okl_Api.G_MISS_CHAR)
1873 THEN
1874 x_ilsv_rec.attribute2 := l_ilsv_rec.attribute2;
1875 END IF;
1876 IF (x_ilsv_rec.attribute3 = Okl_Api.G_MISS_CHAR)
1877 THEN
1878 x_ilsv_rec.attribute3 := l_ilsv_rec.attribute3;
1879 END IF;
1880 IF (x_ilsv_rec.attribute4 = Okl_Api.G_MISS_CHAR)
1881 THEN
1882 x_ilsv_rec.attribute4 := l_ilsv_rec.attribute4;
1883 END IF;
1884 IF (x_ilsv_rec.attribute5 = Okl_Api.G_MISS_CHAR)
1885 THEN
1886 x_ilsv_rec.attribute5 := l_ilsv_rec.attribute5;
1887 END IF;
1888 IF (x_ilsv_rec.attribute6 = Okl_Api.G_MISS_CHAR)
1889 THEN
1890 x_ilsv_rec.attribute6 := l_ilsv_rec.attribute6;
1891 END IF;
1892 IF (x_ilsv_rec.attribute7 = Okl_Api.G_MISS_CHAR)
1893 THEN
1894 x_ilsv_rec.attribute7 := l_ilsv_rec.attribute7;
1895 END IF;
1896 IF (x_ilsv_rec.attribute8 = Okl_Api.G_MISS_CHAR)
1897 THEN
1898 x_ilsv_rec.attribute8 := l_ilsv_rec.attribute8;
1899 END IF;
1900 IF (x_ilsv_rec.attribute9 = Okl_Api.G_MISS_CHAR)
1901 THEN
1902 x_ilsv_rec.attribute9 := l_ilsv_rec.attribute9;
1903 END IF;
1904 IF (x_ilsv_rec.attribute10 = Okl_Api.G_MISS_CHAR)
1905 THEN
1906 x_ilsv_rec.attribute10 := l_ilsv_rec.attribute10;
1907 END IF;
1908 IF (x_ilsv_rec.attribute11 = Okl_Api.G_MISS_CHAR)
1909 THEN
1910 x_ilsv_rec.attribute11 := l_ilsv_rec.attribute11;
1911 END IF;
1912 IF (x_ilsv_rec.attribute12 = Okl_Api.G_MISS_CHAR)
1913 THEN
1914 x_ilsv_rec.attribute12 := l_ilsv_rec.attribute12;
1915 END IF;
1916 IF (x_ilsv_rec.attribute13 = Okl_Api.G_MISS_CHAR)
1917 THEN
1918 x_ilsv_rec.attribute13 := l_ilsv_rec.attribute13;
1919 END IF;
1920 IF (x_ilsv_rec.attribute14 = Okl_Api.G_MISS_CHAR)
1921 THEN
1922 x_ilsv_rec.attribute14 := l_ilsv_rec.attribute14;
1923 END IF;
1924 IF (x_ilsv_rec.attribute15 = Okl_Api.G_MISS_CHAR)
1925 THEN
1926 x_ilsv_rec.attribute15 := l_ilsv_rec.attribute15;
1927 END IF;
1928 IF (x_ilsv_rec.created_by = Okl_Api.G_MISS_NUM)
1929 THEN
1930 x_ilsv_rec.created_by := l_ilsv_rec.created_by;
1931 END IF;
1932 IF (x_ilsv_rec.creation_date = Okl_Api.G_MISS_DATE)
1933 THEN
1934 x_ilsv_rec.creation_date := l_ilsv_rec.creation_date;
1935 END IF;
1936 IF (x_ilsv_rec.last_updated_by = Okl_Api.G_MISS_NUM)
1937 THEN
1938 x_ilsv_rec.last_updated_by := l_ilsv_rec.last_updated_by;
1939 END IF;
1940 IF (x_ilsv_rec.last_update_date = Okl_Api.G_MISS_DATE)
1941 THEN
1942 x_ilsv_rec.last_update_date := l_ilsv_rec.last_update_date;
1943 END IF;
1944 IF (x_ilsv_rec.last_update_login = Okl_Api.G_MISS_NUM)
1945 THEN
1946 x_ilsv_rec.last_update_login := l_ilsv_rec.last_update_login;
1947 END IF;
1948 RETURN(l_return_status);
1949 END populate_new_record;
1950 ----------------------------------------------
1951 -- Set_Attributes for:OKL_INVC_FRMT_STRMS_V --
1952 ----------------------------------------------
1953 FUNCTION Set_Attributes (
1954 p_ilsv_rec IN ilsv_rec_type,
1955 x_ilsv_rec OUT NOCOPY ilsv_rec_type
1956 ) RETURN VARCHAR2 IS
1957 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1958 BEGIN
1959 x_ilsv_rec := p_ilsv_rec;
1960 x_ilsv_rec.OBJECT_VERSION_NUMBER := NVL(x_ilsv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1961 RETURN(l_return_status);
1962 END Set_Attributes;
1963 BEGIN
1964 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1965 G_PKG_NAME,
1966 p_init_msg_list,
1967 l_api_version,
1968 p_api_version,
1969 '_PVT',
1970 x_return_status);
1971 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1972 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1973 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1974 RAISE Okl_Api.G_EXCEPTION_ERROR;
1975 END IF;
1976 --- Setting item attributes
1977 l_return_status := Set_Attributes(
1978 p_ilsv_rec, -- IN
1979 l_ilsv_rec); -- OUT
1980 --- If any errors happen abort API
1981 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1982 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1983 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1984 RAISE Okl_Api.G_EXCEPTION_ERROR;
1985 END IF;
1986 l_return_status := populate_new_record(l_ilsv_rec, l_def_ilsv_rec);
1987 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1988 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1989 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1990 RAISE Okl_Api.G_EXCEPTION_ERROR;
1991 END IF;
1992 l_def_ilsv_rec := fill_who_columns(l_def_ilsv_rec);
1993 --- Validate all non-missing attributes (Item Level Validation)
1994 l_return_status := Validate_Attributes(l_def_ilsv_rec);
1995 --- If any errors happen abort API
1996 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1997 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1998 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1999 RAISE Okl_Api.G_EXCEPTION_ERROR;
2000 END IF;
2001 l_return_status := Validate_Record(l_def_ilsv_rec);
2002 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2003 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2004 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2005 RAISE Okl_Api.G_EXCEPTION_ERROR;
2006 END IF;
2007
2008 --------------------------------------
2009 -- Move VIEW record to "Child" records
2010 --------------------------------------
2011 migrate(l_def_ilsv_rec, l_ils_rec);
2012 --------------------------------------------
2013 -- Call the UPDATE_ROW for each child record
2014 --------------------------------------------
2015 update_row(
2016 p_init_msg_list,
2017 x_return_status,
2018 x_msg_count,
2019 x_msg_data,
2020 l_ils_rec,
2021 lx_ils_rec
2022 );
2023 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2024 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2025 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2026 RAISE Okl_Api.G_EXCEPTION_ERROR;
2027 END IF;
2028 migrate(lx_ils_rec, l_def_ilsv_rec);
2029 x_ilsv_rec := l_def_ilsv_rec;
2030 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2031 EXCEPTION
2032 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2033 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2034 (
2035 l_api_name,
2036 G_PKG_NAME,
2037 'okl_api.G_RET_STS_ERROR',
2038 x_msg_count,
2039 x_msg_data,
2040 '_PVT'
2041 );
2042 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2043 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2044 (
2045 l_api_name,
2046 G_PKG_NAME,
2047 'okl_api.G_RET_STS_UNEXP_ERROR',
2048 x_msg_count,
2049 x_msg_data,
2050 '_PVT'
2051 );
2052 WHEN OTHERS THEN
2053 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2054 (
2055 l_api_name,
2056 G_PKG_NAME,
2057 'OTHERS',
2058 x_msg_count,
2059 x_msg_data,
2060 '_PVT'
2061 );
2062 END update_row;
2063 ----------------------------------------
2064 -- PL/SQL TBL update_row for:ILSV_TBL --
2065 ----------------------------------------
2066 PROCEDURE update_row(
2067 p_api_version IN NUMBER,
2068 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2069 x_return_status OUT NOCOPY VARCHAR2,
2070 x_msg_count OUT NOCOPY NUMBER,
2071 x_msg_data OUT NOCOPY VARCHAR2,
2072 p_ilsv_tbl IN ilsv_tbl_type,
2073 x_ilsv_tbl OUT NOCOPY ilsv_tbl_type) IS
2074
2075 l_api_version CONSTANT NUMBER := 1;
2076 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2077 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2078 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2079 i NUMBER := 0;
2080 BEGIN
2081 Okl_Api.init_msg_list(p_init_msg_list);
2082 -- Make sure PL/SQL table has records in it before passing
2083 IF (p_ilsv_tbl.COUNT > 0) THEN
2084 i := p_ilsv_tbl.FIRST;
2085 LOOP
2086 update_row (
2087 p_api_version => p_api_version,
2088 p_init_msg_list => Okl_Api.G_FALSE,
2089 x_return_status => x_return_status,
2090 x_msg_count => x_msg_count,
2091 x_msg_data => x_msg_data,
2092 p_ilsv_rec => p_ilsv_tbl(i),
2093 x_ilsv_rec => x_ilsv_tbl(i));
2094 -- Store the highest degree of error
2095 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2096 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2097 l_overall_status := x_return_status;
2098 END IF;
2099 END IF;
2100 EXIT WHEN (i = p_ilsv_tbl.LAST OR x_return_status <> Okl_Api.G_RET_STS_SUCCESS ); -- Added or conditon for bug 9482832
2101 i := p_ilsv_tbl.NEXT(i);
2102 END LOOP;
2103 -- return overall status
2104 x_return_status := l_overall_status;
2105 END IF;
2106 EXCEPTION
2107 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2108 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2109 (
2110 l_api_name,
2111 G_PKG_NAME,
2112 'okl_api.G_RET_STS_ERROR',
2113 x_msg_count,
2114 x_msg_data,
2115 '_PVT'
2116 );
2117 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2118 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2119 (
2120 l_api_name,
2121 G_PKG_NAME,
2122 'okl_api.G_RET_STS_UNEXP_ERROR',
2123 x_msg_count,
2124 x_msg_data,
2125 '_PVT'
2126 );
2127 WHEN OTHERS THEN
2128 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2129 (
2130 l_api_name,
2131 G_PKG_NAME,
2132 'OTHERS',
2133 x_msg_count,
2134 x_msg_data,
2135 '_PVT'
2136 );
2137 END update_row;
2138
2139 ---------------------------------------------------------------------------
2140 -- PROCEDURE delete_row
2141 ---------------------------------------------------------------------------
2142 ----------------------------------------
2143 -- delete_row for:OKL_INVC_FRMT_STRMS --
2144 ----------------------------------------
2145 PROCEDURE delete_row(
2146 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2147 x_return_status OUT NOCOPY VARCHAR2,
2148 x_msg_count OUT NOCOPY NUMBER,
2149 x_msg_data OUT NOCOPY VARCHAR2,
2150 p_ils_rec IN ils_rec_type) IS
2151
2152 l_api_version CONSTANT NUMBER := 1;
2153 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_delete_row';
2154 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2155 l_ils_rec ils_rec_type:= p_ils_rec;
2156 l_row_notfound BOOLEAN := TRUE;
2157 BEGIN
2158 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2159 p_init_msg_list,
2160 '_PVT',
2161 x_return_status);
2162 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2163 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2164 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2165 RAISE Okl_Api.G_EXCEPTION_ERROR;
2166 END IF;
2167 DELETE FROM OKL_INVC_FRMT_STRMS
2168 WHERE ID = l_ils_rec.id;
2169
2170
2171 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2172 EXCEPTION
2173 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2174 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2175 (
2176 l_api_name,
2177 G_PKG_NAME,
2178 'okl_api.G_RET_STS_ERROR',
2179 x_msg_count,
2180 x_msg_data,
2181 '_PVT'
2182 );
2183 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2184 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2185 (
2186 l_api_name,
2187 G_PKG_NAME,
2188 'okl_api.G_RET_STS_UNEXP_ERROR',
2189 x_msg_count,
2190 x_msg_data,
2191 '_PVT'
2192 );
2193 WHEN OTHERS THEN
2194 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2195 (
2196 l_api_name,
2197 G_PKG_NAME,
2198 'OTHERS',
2199 x_msg_count,
2200 x_msg_data,
2201 '_PVT'
2202 );
2203 END delete_row;
2204 ------------------------------------------
2205 -- delete_row for:OKL_INVC_FRMT_STRMS_V --
2206 ------------------------------------------
2207 PROCEDURE delete_row(
2208 p_api_version IN NUMBER,
2209 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2210 x_return_status OUT NOCOPY VARCHAR2,
2211 x_msg_count OUT NOCOPY NUMBER,
2212 x_msg_data OUT NOCOPY VARCHAR2,
2213 p_ilsv_rec IN ilsv_rec_type) IS
2214
2215 l_api_version CONSTANT NUMBER := 1;
2216 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2217 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2218 l_ilsv_rec ilsv_rec_type := p_ilsv_rec;
2219 l_ils_rec ils_rec_type;
2220 BEGIN
2221 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2222 G_PKG_NAME,
2223 p_init_msg_list,
2224 l_api_version,
2225 p_api_version,
2226 '_PVT',
2227 x_return_status);
2228 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2229 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2230 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2231 RAISE Okl_Api.G_EXCEPTION_ERROR;
2232 END IF;
2233 --------------------------------------
2234 -- Move VIEW record to "Child" records
2235 --------------------------------------
2236 migrate(l_ilsv_rec, l_ils_rec);
2237 --------------------------------------------
2238 -- Call the DELETE_ROW for each child record
2239 --------------------------------------------
2240 delete_row(
2241 p_init_msg_list,
2242 x_return_status,
2243 x_msg_count,
2244 x_msg_data,
2245 l_ils_rec
2246 );
2247 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2248 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2249 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2250 RAISE Okl_Api.G_EXCEPTION_ERROR;
2251 END IF;
2252 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2253 EXCEPTION
2254 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2255 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2256 (
2257 l_api_name,
2258 G_PKG_NAME,
2259 'okl_api.G_RET_STS_ERROR',
2260 x_msg_count,
2261 x_msg_data,
2262 '_PVT'
2263 );
2264 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2265 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2266 (
2267 l_api_name,
2268 G_PKG_NAME,
2269 'okl_api.G_RET_STS_UNEXP_ERROR',
2270 x_msg_count,
2271 x_msg_data,
2272 '_PVT'
2273 );
2274 WHEN OTHERS THEN
2275 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2276 (
2277 l_api_name,
2278 G_PKG_NAME,
2279 'OTHERS',
2280 x_msg_count,
2281 x_msg_data,
2282 '_PVT'
2283 );
2284 END delete_row;
2285 ----------------------------------------
2286 -- PL/SQL TBL delete_row for:ILSV_TBL --
2287 ----------------------------------------
2288 PROCEDURE delete_row(
2289 p_api_version IN NUMBER,
2290 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2291 x_return_status OUT NOCOPY VARCHAR2,
2292 x_msg_count OUT NOCOPY NUMBER,
2293 x_msg_data OUT NOCOPY VARCHAR2,
2294 p_ilsv_tbl IN ilsv_tbl_type) IS
2295
2296 l_api_version CONSTANT NUMBER := 1;
2297 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2298 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2299 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2300 i NUMBER := 0;
2301 BEGIN
2302 Okl_Api.init_msg_list(p_init_msg_list);
2303 -- Make sure PL/SQL table has records in it before passing
2304 IF (p_ilsv_tbl.COUNT > 0) THEN
2305 i := p_ilsv_tbl.FIRST;
2306 LOOP
2307 delete_row (
2308 p_api_version => p_api_version,
2309 p_init_msg_list => Okl_Api.G_FALSE,
2310 x_return_status => x_return_status,
2311 x_msg_count => x_msg_count,
2312 x_msg_data => x_msg_data,
2313 p_ilsv_rec => p_ilsv_tbl(i));
2314 -- Store the highest degree of error
2315 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2316 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2317 l_overall_status := x_return_status;
2318 END IF;
2319 END IF;
2320 EXIT WHEN (i = p_ilsv_tbl.LAST);
2321 i := p_ilsv_tbl.NEXT(i);
2322 END LOOP;
2323 -- return overall status
2324 x_return_status := l_overall_status;
2325 END IF;
2326 EXCEPTION
2327 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2328 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2329 (
2330 l_api_name,
2331 G_PKG_NAME,
2332 'okl_api.G_RET_STS_ERROR',
2333 x_msg_count,
2334 x_msg_data,
2335 '_PVT'
2336 );
2337 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2338 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2339 (
2340 l_api_name,
2341 G_PKG_NAME,
2342 'okl_api.G_RET_STS_UNEXP_ERROR',
2343 x_msg_count,
2344 x_msg_data,
2345 '_PVT'
2346 );
2347 WHEN OTHERS THEN
2348 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2349 (
2350 l_api_name,
2351 G_PKG_NAME,
2352 'OTHERS',
2353 x_msg_count,
2354 x_msg_data,
2355 '_PVT'
2356 );
2357 END delete_row;
2358 END Okl_Ils_Pvt;