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