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