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