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