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