[Home] [Help]
PACKAGE BODY: APPS.OKL_SPM_PVT
Source
1 PACKAGE BODY OKL_SPM_PVT AS
2 /* $Header: OKLSSPMB.pls 120.3 2005/10/30 03:18:10 appldev noship $*/
3
4 ---------------------------------------------------------------------------
5 -- FUNCTION get_seq_id
6 ---------------------------------------------------------------------------
7 FUNCTION get_seq_id RETURN NUMBER IS
8 BEGIN
9 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
10 END get_seq_id;
11
12 ---------------------------------------------------------------------------
13 -- FUNCTION get_rec for: OKL_SIF_PRICING_PARAMS
14 ---------------------------------------------------------------------------
15 FUNCTION get_rec (
16 p_spm_rec IN spm_rec_type,
17 x_no_data_found OUT NOCOPY BOOLEAN
18 ) RETURN spm_rec_type IS
19 CURSOR okl_sif_pricing_params_pk_csr (p_id IN NUMBER) IS
20 SELECT ID
21 ,OBJECT_VERSION_NUMBER
22 ,SIF_ID
23 ,khr_id
24 ,NAME
25 ,DISPLAY_YN
26 ,UPDATE_YN
27 ,DEFAULT_VALUE
28 ,PARAMETER_VALUE
29 ,PRC_ENG_IDENT
30 ,DESCRIPTION
31 ,CREATED_BY
32 ,CREATION_DATE
33 ,LAST_UPDATED_BY
34 ,LAST_UPDATE_DATE
35 ,LAST_UPDATE_LOGIN
36 FROM OKL_SIF_PRICING_PARAMS
37 WHERE OKL_SIF_PRICING_PARAMS.id = p_id;
38
39 l_okl_sif_pricing_params_pk okl_sif_pricing_params_pk_csr%ROWTYPE;
40 l_spm_rec spm_rec_type;
41 BEGIN
42 x_no_data_found := TRUE;
43
44 -- Get current database values
45 OPEN okl_sif_pricing_params_pk_csr (p_spm_rec.id);
46
47 FETCH okl_sif_pricing_params_pk_csr INTO
48 l_spm_rec.id
49 ,l_spm_rec.object_version_number
50 ,l_spm_rec.sif_id
51 ,l_spm_rec.khr_id
52 ,l_spm_rec.name
53 ,l_spm_rec.display_yn
54 ,l_spm_rec.update_yn
55 ,l_spm_rec.default_value
56 ,l_spm_rec.parameter_value
57 ,l_spm_rec.prc_eng_ident
58 ,l_spm_rec.description
59 ,l_spm_rec.created_by
60 ,l_spm_rec.creation_date
61 ,l_spm_rec.last_updated_by
62 ,l_spm_rec.last_update_date
63 ,l_spm_rec.last_update_login;
64
65 x_no_data_found := okl_sif_pricing_params_pk_csr%NOTFOUND;
66 CLOSE okl_sif_pricing_params_pk_csr;
67
68 RETURN(l_spm_rec);
69
70 END get_rec;
71
72 FUNCTION get_rec (
73 p_spm_rec IN spm_rec_type
74 ) RETURN spm_rec_type IS
75 l_row_notfound BOOLEAN := TRUE;
76 BEGIN
77 RETURN(get_rec(p_spm_rec, l_row_notfound));
78 END get_rec;
79 ---------------------------------------------------------------------------
80 -- FUNCTION get_rec for: OKL_SIF_PRICING_PARAMS_V
81 ---------------------------------------------------------------------------
82 FUNCTION get_rec (
83 p_spmv_rec IN spmv_rec_type,
84 x_no_data_found OUT NOCOPY BOOLEAN
85 ) RETURN spmv_rec_type IS
86 CURSOR okl_spmv_pk_csr (p_id IN NUMBER) IS
87 SELECT ID
88 ,OBJECT_VERSION_NUMBER
89 ,SIF_ID
90 ,KHR_ID
91 ,NAME
92 ,DISPLAY_YN
93 ,UPDATE_YN
94
95 ,DEFAULT_VALUE
96 ,PARAMETER_VALUE
97 ,PRC_ENG_IDENT
98 ,DESCRIPTION
99 ,CREATED_BY
100 ,CREATION_DATE
101 ,LAST_UPDATED_BY
102 ,LAST_UPDATE_DATE
103 ,LAST_UPDATE_LOGIN
104 FROM OKL_SIF_PRICING_PARAMS_V
105 WHERE OKL_SIF_PRICING_PARAMS_V.ID = p_id;
106
107 l_okl_spmv_pk okl_spmv_pk_csr%ROWTYPE;
108 l_spmv_rec spmv_rec_type;
109 BEGIN
110 x_no_data_found := TRUE;
111 -- Get current database values
112 OPEN okl_spmv_pk_csr (p_spmv_rec.id);
113 FETCH okl_spmv_pk_csr INTO
114 l_spmv_rec.id
115 ,l_spmv_rec.object_version_number
116 ,l_spmv_rec.sif_id
117 ,l_spmv_rec.khr_id
118 ,l_spmv_rec.name
119 ,l_spmv_rec.display_yn
120 ,l_spmv_rec.update_yn
121 ,l_spmv_rec.default_value
122 ,l_spmv_rec.parameter_value
123 ,l_spmv_rec.prc_eng_ident
124 ,l_spmv_rec.description
125 ,l_spmv_rec.created_by
126 ,l_spmv_rec.creation_date
127 ,l_spmv_rec.last_updated_by
128 ,l_spmv_rec.last_update_date
129 ,l_spmv_rec.last_update_login;
130
131 x_no_data_found := okl_spmv_pk_csr%NOTFOUND;
132 CLOSE okl_spmv_pk_csr;
133 RETURN(l_spmv_rec);
134 END get_rec;
135
136 FUNCTION get_rec (
137 p_spmv_rec IN spmv_rec_type
138 ) RETURN spmv_rec_type IS
139 l_row_notfound BOOLEAN := TRUE;
140 BEGIN
141 RETURN(get_rec(p_spmv_rec, l_row_notfound));
142 END get_rec;
143
144 ---------------------------------------------------------------------------
145 -- PROCEDURE qc
146 ---------------------------------------------------------------------------
147 PROCEDURE qc IS
148 BEGIN
149 NULL;
150 END qc;
151
152 ---------------------------------------------------------------------------
153 -- PROCEDURE change_version
154 ---------------------------------------------------------------------------
155 PROCEDURE change_version IS
156 BEGIN
157 NULL;
158 END change_version;
159
160 ---------------------------------------------------------------------------
161 -- PROCEDURE api_copy
162 ---------------------------------------------------------------------------
163 PROCEDURE api_copy IS
164 BEGIN
165 NULL;
166 END api_copy;
167
168 ----------------------------------------------
169 -- validate_record for: OKL_ST_GEN_TMPT_LNS_V --
170 ----------------------------------------------
171 FUNCTION validate_record (
172 p_spmv_rec IN spmv_rec_type
173 ) RETURN VARCHAR2 IS
174 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
175 BEGIN
176 RETURN (l_return_status);
177 END validate_record;
178
179 ---------------------------------------------------------------------------
180 -- PROCEDURE validate_id
181 ---------------------------------------------------------------------------
182 -- Start of comments
183 --
184 -- Procedure Name : Validate_Id
185 -- Description :
186 -- Business Rules :
187 -- Parameters :
188 -- Version : 1.0
189 -- End of comments
190 ---------------------------------------------------------------------------
191 PROCEDURE Validate_Id(
192 p_spmv_rec IN spmv_rec_type,
193 x_return_status OUT NOCOPY VARCHAR2
194 ) IS
195
196
197 BEGIN
198 -- initialize return status
199 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
200
201 IF p_spmv_rec.id = Okl_Api.G_MISS_NUM OR
202 p_spmv_rec.id IS NULL
203 THEN
204 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
205 x_return_status := Okl_Api.G_RET_STS_ERROR;
206 RAISE G_EXCEPTION_HALT_VALIDATION;
207 END IF;
208
209 EXCEPTION
210 WHEN G_EXCEPTION_HALT_VALIDATION THEN
211 -- no processing necessary; validation can continue
212 -- with the next column
213 NULL;
214
215 WHEN OTHERS THEN
216 -- store SQL error message on message stack for caller
217 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME
218 ,p_msg_name => G_UNEXPECTED_ERROR
219 ,p_token1 => G_SQLCODE_TOKEN
220 ,p_token1_value => SQLCODE
221 ,p_token2 => G_SQLERRM_TOKEN
222 ,p_token2_value => SQLERRM);
223
224 -- notify caller of an UNEXPECTED error
225 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
226
227 END Validate_Id;
228
229 ---------------------------------------------------------------------------
230 -- PROCEDURE validate_object_version_number
231 ---------------------------------------------------------------------------
232 -- Start of comments
233 --
234 -- Procedure Name : validate_object_version_number
235 -- Description :
236 -- Business Rules :
237 -- Parameters :
238 -- Version : 1.0
239 -- End of comments
240 ---------------------------------------------------------------------------
241 PROCEDURE Validate_Object_Version_Number(
242 p_spmv_rec IN spmv_rec_type,
243 x_return_status OUT NOCOPY VARCHAR2
244 ) IS
245
246 BEGIN
247
248 -- initialize return status
249 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
250
251 IF p_spmv_rec.object_version_number = Okl_Api.G_MISS_NUM OR
252 p_spmv_rec.object_version_number IS NULL
253 THEN
254 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
255 x_return_status := Okl_Api.G_RET_STS_ERROR;
256 RAISE G_EXCEPTION_HALT_VALIDATION;
257 END IF;
258
259 EXCEPTION
260 WHEN G_EXCEPTION_HALT_VALIDATION THEN
261 -- no processing necessary; validation can continue
262 -- with the next column
263 NULL;
264
265 WHEN OTHERS THEN
266 -- store SQL error message on message stack for caller
267 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME
268 ,p_msg_name => G_UNEXPECTED_ERROR
269 ,p_token1 => G_SQLCODE_TOKEN
270 ,p_token1_value => SQLCODE
271 ,p_token2 => G_SQLERRM_TOKEN
272 ,p_token2_value => SQLERRM);
273
274 -- notify caller of an UNEXPECTED error
275 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
276
277 END Validate_Object_Version_Number;
278
279 ---------------------------------------------------------------------------
280 -- PROCEDURE validate_khr_id
281 ---------------------------------------------------------------------------
282 -- Start of comments
283 --
284 -- Procedure Name : validate_khr_id
285 -- Description :
286 -- Business Rules :
287 -- Parameters :
288 -- Version : 1.0
289 -- End of comments
290 ---------------------------------------------------------------------------
291 PROCEDURE validate_khr_id(
292 p_spmv_rec IN spmv_rec_type,
293 x_return_status OUT NOCOPY VARCHAR2
294 ) IS
295
296 BEGIN
297
298 -- initialize return status
299 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
300
301 IF p_spmv_rec.khr_id = Okl_Api.G_MISS_NUM OR
302 p_spmv_rec.khr_id IS NULL
303 THEN
304 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'khr_id');
305 x_return_status := Okl_Api.G_RET_STS_ERROR;
306 RAISE G_EXCEPTION_HALT_VALIDATION;
307 END IF;
308
309 EXCEPTION
310 WHEN G_EXCEPTION_HALT_VALIDATION THEN
311 -- no processing necessary; validation can continue
312 -- with the next column
313 NULL;
314
315 WHEN OTHERS THEN
316 -- store SQL error message on message stack for caller
317 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME
318 ,p_msg_name => G_UNEXPECTED_ERROR
319 ,p_token1 => G_SQLCODE_TOKEN
320 ,p_token1_value => SQLCODE
321 ,p_token2 => G_SQLERRM_TOKEN
322 ,p_token2_value => SQLERRM);
323
324 -- notify caller of an UNEXPECTED error
325 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
326
327 END validate_khr_id;
328
329 ---------------------------------------------------------------------------
330 -- FUNCTION Validate_Attributes
331 ---------------------------------------------------------------------------
332 -- Start of comments
333 --
334 -- Procedure Name : Validate_Attributes
335 -- Description :
336 -- Business Rules :
337 -- Parameters :
338 -- Version : 1.0
339 -- End of comments
340 ---------------------------------------------------------------------------
341
342 FUNCTION Validate_Attributes (
343 p_spmv_rec IN spmv_rec_type
344 ) RETURN VARCHAR2 IS
345
346 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
347 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
348 BEGIN
349 -- call each column-level validation
350 -- Validate_Id
351 Validate_Id(p_spmv_rec, x_return_status);
352 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
353 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
354 -- need to exit
355 l_return_status := x_return_status;
356 RAISE G_EXCEPTION_HALT_VALIDATION;
357 ELSE
358 -- there was an error
359 l_return_status := x_return_status;
360 END IF;
361 END IF;
362
363 -- Validate_Object_Version_Number
364 validate_object_version_number(p_spmv_rec, x_return_status);
365 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
366 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
367 -- need to exit
368 l_return_status := x_return_status;
369 RAISE G_EXCEPTION_HALT_VALIDATION;
370 ELSE
371 -- there was an error
372 l_return_status := x_return_status;
373 END IF;
374 END IF;
375
376 -- Validate_khr_id
377 validate_khr_id(p_spmv_rec, x_return_status);
378 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
379 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
380 -- need to exit
381 l_return_status := x_return_status;
382 RAISE G_EXCEPTION_HALT_VALIDATION;
383 ELSE
384 -- there was an error
385 l_return_status := x_return_status;
386 END IF;
387 END IF;
388
389 RETURN(l_return_status);
390 END; --Validate_Attributes
391
392 ---------------------------------------------------------------------------
393 -- FUNCTION null_out_defaults
394 ---------------------------------------------------------------------------
395 -- Start of comments
396 --
397 -- Function Name : null_out_defaults
398 -- Description : nulling out the defaults
399 -- Business Rules :
400 -- Parameters :
401 -- Version : 1.0
402 -- End of comments
403 ---------------------------------------------------------------------------
404 FUNCTION null_out_defaults (
405 p_spmv_rec IN spmv_rec_type
406 ) RETURN spmv_rec_type IS
407 l_spmv_rec spmv_rec_type := p_spmv_rec;
408 BEGIN
409 IF (l_spmv_rec.id = Okl_Api.G_MISS_NUM) THEN
410 l_spmv_rec.id := NULL;
411 END IF;
412 IF (l_spmv_rec.object_version_number = Okl_Api.G_MISS_NUM) THEN
413 l_spmv_rec.object_version_number := NULL;
414 END IF;
415 IF (l_spmv_rec.khr_id = Okl_Api.G_MISS_NUM) THEN
416 l_spmv_rec.khr_id := NULL;
417 END IF;
418 IF (l_spmv_rec.sif_id = Okl_Api.G_MISS_NUM) THEN
419 l_spmv_rec.sif_id := NULL;
420 END IF;
421 IF (l_spmv_rec.name = Okl_Api.G_MISS_CHAR) THEN
422 l_spmv_rec.name := NULL;
423 END IF;
424 IF (l_spmv_rec.display_yn = Okl_Api.G_MISS_CHAR) THEN
425 l_spmv_rec.display_yn := NULL;
426 END IF;
427 IF (l_spmv_rec.update_yn = Okl_Api.G_MISS_CHAR) THEN
428 l_spmv_rec.update_yn := NULL;
429 END IF;
430 IF (l_spmv_rec.default_value = Okl_Api.G_MISS_CHAR) THEN
431 l_spmv_rec.default_value := NULL;
432 END IF;
433 IF (l_spmv_rec.parameter_value = Okl_Api.G_MISS_CHAR) THEN
434 l_spmv_rec.parameter_value := NULL;
435 END IF;
436 IF (l_spmv_rec.prc_eng_ident = Okl_Api.G_MISS_CHAR) THEN
437 l_spmv_rec.prc_eng_ident := NULL;
438 END IF;
439 IF (l_spmv_rec.description = Okl_Api.G_MISS_CHAR) THEN
440 l_spmv_rec.description := NULL;
441 END IF;
442 IF (l_spmv_rec.created_by = Okl_Api.G_MISS_NUM) THEN
443 l_spmv_rec.created_by := NULL;
444 END IF;
445 IF (l_spmv_rec.creation_date = Okl_Api.G_MISS_DATE) THEN
446 l_spmv_rec.creation_date := NULL;
447 END IF;
448 IF (l_spmv_rec.last_updated_by = Okl_Api.G_MISS_NUM) THEN
449 l_spmv_rec.last_updated_by := NULL;
450 END IF;
451 IF (l_spmv_rec.last_update_date = Okl_Api.G_MISS_DATE) THEN
452 l_spmv_rec.last_update_date := NULL;
453 END IF;
454 IF (l_spmv_rec.last_update_login = Okl_Api.G_MISS_NUM) THEN
455 l_spmv_rec.last_update_login := NULL;
456 END IF;
457
458 RETURN(l_spmv_rec);
459 END null_out_defaults;
460
461
462 ---------------------------------------------------------------------------
463 -- PROCEDURE Migrate
464 ---------------------------------------------------------------------------
465 PROCEDURE migrate (
466 p_from IN spm_rec_type,
467 p_to IN OUT NOCOPY spmv_rec_type
468 ) IS
469 BEGIN
470 p_to.id := p_from.id;
471 p_to.object_version_number := p_from.object_version_number;
472 p_to.khr_id := p_from.khr_id;
473 p_to.sif_id := p_from.sif_id;
474 p_to.name := p_from.name;
475 p_to.display_yn := p_from.display_yn;
476 p_to.update_yn := p_from.update_yn;
477 p_to.default_value := p_from.default_value;
478 p_to.parameter_value := p_from.parameter_value;
479 p_to.prc_eng_ident := p_from.prc_eng_ident;
480 p_to.description := p_from.description;
481 p_to.created_by := p_from.created_by;
482 p_to.creation_date := p_from.creation_date;
483 p_to.last_updated_by := p_from.last_updated_by;
484 p_to.last_update_date := p_from.last_update_date;
485 p_to.last_update_login := p_from.last_update_login;
486 END;
487
488 ---------------------------------------------------------------------------
489 -- PROCEDURE Migrate
490 ---------------------------------------------------------------------------
491 PROCEDURE migrate (
492 p_from IN spmv_rec_type,
493 p_to IN OUT NOCOPY spm_rec_type
494 ) IS
495 BEGIN
496 p_to.id := p_from.id;
497 p_to.object_version_number := p_from.object_version_number;
498 p_to.khr_id := p_from.khr_id;
499 p_to.sif_id := p_from.sif_id;
500 p_to.name := p_from.name;
501 p_to.display_yn := p_from.display_yn;
502 p_to.update_yn := p_from.update_yn;
503 p_to.default_value := p_from.default_value;
504 p_to.parameter_value := p_from.parameter_value;
505 p_to.prc_eng_ident := p_from.prc_eng_ident;
506 p_to.description := p_from.description;
507 p_to.created_by := p_from.created_by;
508 p_to.creation_date := p_from.creation_date;
509 p_to.last_updated_by := p_from.last_updated_by;
510 p_to.last_update_date := p_from.last_update_date;
511 p_to.last_update_login := p_from.last_update_login;
512 END;
513
514 ---------------------------------------------------------------------------
515 -- PROCEDURE insert_row
516 ---------------------------------------------------------------------------
517 -- Start of comments
518 --
519 -- Procedure Name : insert_row
520 -- Description : procedure for inserting the records in
521 -- table OKL_SIF_PRICING_PARAMS
522 -- Business Rules :
523 -- Parameters :
524 -- Version : 1.0
525 -- End of comments
526 ---------------------------------------------------------------------------
527 PROCEDURE insert_row(
528 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_msg_count OUT NOCOPY NUMBER,
531 x_msg_data OUT NOCOPY VARCHAR2,
532 p_spm_rec IN spm_rec_type) AS
533
534 -- Local Variables within the function
535 l_api_version CONSTANT NUMBER := 1;
536 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
537 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
538 l_spm_rec spm_rec_type := p_spm_rec;
539 l_def_spm_rec spm_rec_type;
540
541 ---------------------------------------------
542 -- Set_Attributes for:OKL_SIF_PRICING_PARAMS --
543 ---------------------------------------------
544 FUNCTION Set_Attributes (
545 p_spm_rec IN spm_rec_type,
546 x_spm_rec OUT NOCOPY spm_rec_type
547 ) RETURN VARCHAR2 IS
548 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
549 BEGIN
550 x_spm_rec := p_spm_rec;
551 RETURN(l_return_status);
552 END Set_Attributes;
553
554 BEGIN
555 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
556 p_init_msg_list,
557 '_PVT',
558 x_return_status);
559 --- If any errors happen abort API
560 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
561 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
562
563 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
564 RAISE Okl_Api.G_EXCEPTION_ERROR;
565 END IF;
566
567 --- Setting item attributes
568 l_return_status := Set_Attributes(
569 p_spm_rec, -- IN
570 l_spm_rec -- OUT
571 );
572 --- If any errors happen abort API
573 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
574 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
575 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
576 RAISE Okl_Api.G_EXCEPTION_ERROR;
577 END IF;
578
579 INSERT INTO OKL_SIF_PRICING_PARAMS(
580 ID
581 ,OBJECT_VERSION_NUMBER
582 ,SIF_ID
583 ,KHR_ID
584 ,NAME
585 ,DISPLAY_YN
586 ,UPDATE_YN
587 ,DEFAULT_VALUE
588 ,PARAMETER_VALUE
589 ,PRC_ENG_IDENT
590 ,DESCRIPTION
591 ,CREATED_BY
592 ,CREATION_DATE
593 ,LAST_UPDATED_BY
594 ,LAST_UPDATE_DATE
595 ,LAST_UPDATE_LOGIN
596 )
597 VALUES (
598 l_spm_rec.id
599 ,l_spm_rec.object_version_number
600 ,l_spm_rec.sif_id
601 ,l_spm_rec.khr_id
602 ,l_spm_rec.name
603 ,l_spm_rec.display_yn
604 ,l_spm_rec.update_yn
605 ,l_spm_rec.default_value
606 ,l_spm_rec.parameter_value
607 ,l_spm_rec.prc_eng_ident
608 ,l_spm_rec.description
609 ,l_spm_rec.created_by
610 ,l_spm_rec.creation_date
611 ,l_spm_rec.last_updated_by
612 ,l_spm_rec.last_update_date
613 ,l_spm_rec.last_update_login
614 );
615
616 -- Set OUT values
617 --x_spm_rec := l_spm_rec;
618 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
619
620 EXCEPTION
621 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
622 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
623 (
624 l_api_name,
625 G_PKG_NAME,
626 'Okl_Api.G_RET_STS_ERROR',
627 x_msg_count,
628 x_msg_data,
629 '_PVT'
630 );
631 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
632 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
633 (
634 l_api_name,
635 G_PKG_NAME,
636 'Okl_Api.G_RET_STS_UNEXP_ERROR',
637 x_msg_count,
638 x_msg_data,
639 '_PVT'
640 );
641 WHEN OTHERS THEN
642 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
643 (
644 l_api_name,
645 G_PKG_NAME,
646 'OTHERS',
647 x_msg_count,
648 x_msg_data,
649 '_PVT'
650 );
651 END insert_row;
652
653 ----------------------------------------
654 -- PL/SQL TBL insert_row for: SPMV_REC --
655 ----------------------------------------
656 PROCEDURE insert_row(
657 p_api_version IN NUMBER,
658 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
659 x_return_status OUT NOCOPY VARCHAR2,
660 x_msg_count OUT NOCOPY NUMBER,
661 x_msg_data OUT NOCOPY VARCHAR2,
662 p_spmv_rec IN spmv_rec_type) AS
663
664
665 -- Local Variables within the function
666 l_api_version CONSTANT NUMBER := 1;
667 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
668 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
669 l_spmv_rec spmv_rec_type;
670 l_def_spmv_rec spmv_rec_type;
671 l_spm_rec spm_rec_type;
672 lx_spm_rec spm_rec_type;
673 -------------------------------
674 -- FUNCTION fill_who_columns --
675 -------------------------------
676 FUNCTION fill_who_columns (
677 p_spmv_rec IN spmv_rec_type
678 ) RETURN spmv_rec_type IS
679 l_spmv_rec spmv_rec_type := p_spmv_rec;
680 BEGIN
681 l_spmv_rec.CREATION_DATE := SYSDATE;
682 l_spmv_rec.CREATED_BY := Fnd_Global.USER_ID;
683 l_spmv_rec.LAST_UPDATE_DATE := SYSDATE;
684 l_spmv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
685 l_spmv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
686 RETURN(l_spmv_rec);
687 END fill_who_columns;
688
689 -----------------------------------------------
690 -- Set_Attributes for:OKL_SIF_PRICING_PARAMS_V --
691 -----------------------------------------------
692 FUNCTION Set_Attributes (
693 p_spmv_rec IN spmv_rec_type,
694 x_spmv_rec OUT NOCOPY spmv_rec_type
695 ) RETURN VARCHAR2 IS
696 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
697 BEGIN
698 x_spmv_rec := p_spmv_rec;
699 x_spmv_rec.OBJECT_VERSION_NUMBER := 1;
700
701 RETURN(l_return_status);
702 END Set_Attributes;
703
704 BEGIN
705
706 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
707 G_PKG_NAME,
708 p_init_msg_list,
709 l_api_version,
710 p_api_version,
711 '_PVT',
712 x_return_status);
713 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
714 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
715 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
716 RAISE Okl_Api.G_EXCEPTION_ERROR;
717 END IF;
718
719 l_spmv_rec := null_out_defaults(p_spmv_rec);
720
721 -- Set primary key value
722 l_spmv_rec.ID := get_seq_id;
723
724 --- Setting item attributes
725 l_return_status := Set_Attributes(
726 l_spmv_rec, -- IN
727 l_def_spmv_rec); -- OUT
728
729
730 --- If any errors happen abort API
731 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
732 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
733 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
734 RAISE Okl_Api.G_EXCEPTION_ERROR;
735 END IF;
736
737 -- fill who columns for the l_def_spmv_rec
738 l_def_spmv_rec := fill_who_columns(l_def_spmv_rec);
739
740 --- Validate all non-missing attributes (Item Level Validation)
741 l_return_status := Validate_Attributes(l_def_spmv_rec);
742 --- If any errors happen abort API
743 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
744 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
745 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
746 RAISE Okl_Api.G_EXCEPTION_ERROR;
747 END IF;
748
749 -- Perfrom all row level validations
750 l_return_status := validate_record(l_def_spmv_rec);
751 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
752 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
753 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
754 RAISE Okl_Api.G_EXCEPTION_ERROR;
755 END IF;
756
757 --------------------------------------
758 -- Move VIEW record to "Child" records
759 --------------------------------------
760 migrate(l_def_spmv_rec, l_spm_rec);
761 --------------------------------------------
762 -- Call the INSERT_ROW for each child record
763 --------------------------------------------
764
765
766 insert_row (
767 p_init_msg_list => p_init_msg_list
768 ,x_return_status => x_return_status
769 ,x_msg_count => x_msg_count
770 ,x_msg_data => x_msg_data
771 ,p_spm_rec => l_spm_rec
772 );
773 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
774 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
775 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
776 RAISE Okl_Api.G_EXCEPTION_ERROR;
777 END IF;
778
779 migrate(lx_spm_rec, l_def_spmv_rec);
780
781 -- Set OUT values
782 --x_spmv_rec := l_def_spmv_rec;
783 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
784
785 EXCEPTION
786 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
787 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
788 (
789 l_api_name,
790 G_PKG_NAME,
791 'Okl_Api.G_RET_STS_ERROR',
792 x_msg_count,
793 x_msg_data,
794 '_PVT'
795 );
796 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
797 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
798 (
799 l_api_name,
800 G_PKG_NAME,
801 'Okl_Api.G_RET_STS_UNEXP_ERROR',
802 x_msg_count,
803 x_msg_data,
804 '_PVT'
805 );
806 WHEN OTHERS THEN
807 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
808 (
809 l_api_name,
810 G_PKG_NAME,
811 'OTHERS',
812 x_msg_count,
813 x_msg_data,
814 '_PVT'
815 );
816 END; -- insert_row
817
818 ----------------------------------------
819 -- PL/SQL TBL insert_row for: SPMV_TBL --
820 ----------------------------------------
821 PROCEDURE insert_row(
822 p_api_version IN NUMBER,
823 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
824 x_return_status OUT NOCOPY VARCHAR2,
825 x_msg_count OUT NOCOPY NUMBER,
826 x_msg_data OUT NOCOPY VARCHAR2,
827 p_spmv_tbl IN spmv_tbl_type) IS
828
829 l_api_version CONSTANT NUMBER := 1;
830 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
831 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
832 i NUMBER := 0;
833 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
834 BEGIN
835 Okl_Api.init_msg_list(p_init_msg_list);
836
837 -- Making sure PL/SQL table has records in it before passing
838 IF (p_spmv_tbl.COUNT > 0) THEN
839 i := p_spmv_tbl.FIRST;
840 LOOP
841
842 insert_row (
843 p_api_version => l_api_version,
844 p_init_msg_list => Okl_Api.G_FALSE,
845 x_return_status => x_return_status,
846 x_msg_count => x_msg_count,
847 x_msg_data => x_msg_data,
848 p_spmv_rec => p_spmv_tbl(i));
849
850 -- store the highest degree of error
851 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
852 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
853 l_overall_status := x_return_status;
854 END IF;
855 END IF;
856 EXIT WHEN (i = p_spmv_tbl.LAST);
857 i := p_spmv_tbl.NEXT(i);
858 END LOOP;
859
860 -- return overall status
861 x_return_status := l_overall_status;
862 END IF;
863 EXCEPTION
864
865 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
866 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
867 (
868 l_api_name,
869 G_PKG_NAME,
870 'Okl_Api.G_RET_STS_ERROR',
871 x_msg_count,
872 x_msg_data,
873 '_PVT'
874 );
875 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
876 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
877 (
878 l_api_name,
879 G_PKG_NAME,
880 'Okl_Api.G_RET_STS_UNEXP_ERROR',
881 x_msg_count,
882 x_msg_data,
883 '_PVT'
884 );
885 WHEN OTHERS THEN
886 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
887 (
888 l_api_name,
889 G_PKG_NAME,
890 'OTHERS',
891 x_msg_count,
892 x_msg_data,
893 '_PVT'
894 );
895 END insert_row;
896
897 ---------------------------------------------------------------------------
898 -- PROCEDURE update_row
899 ---------------------------------------------------------------------------
900 -- Start of comments
901 --
902 -- Procedure Name : update_row
903 -- Description : procedure for updating the records in
904 -- table OKL_SIF_PRICING_PARAMS
905 -- Business Rules :
906 -- Parameters :
907 -- Version : 1.0
908 -- End of comments
909 --------------------------------------------------------------------------
910 PROCEDURE update_row(
911 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
912 x_return_status OUT NOCOPY VARCHAR2,
913 x_msg_count OUT NOCOPY NUMBER,
914 x_msg_data OUT NOCOPY VARCHAR2,
915 p_spm_rec IN spm_rec_type) IS
916
917 l_api_version CONSTANT NUMBER := 1;
918 l_api_name CONSTANT VARCHAR2(30) := 'OPTS_update_row';
919 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
920 l_spm_rec spm_rec_type := p_spm_rec;
921 l_def_spm_rec spm_rec_type;
922 l_row_notfound BOOLEAN := TRUE;
923 ----------------------------------
924 -- FUNCTION populate_new_record --
925 ----------------------------------
926 FUNCTION populate_new_record (
927 p_spm_rec IN spm_rec_type,
928 x_spm_rec OUT NOCOPY spm_rec_type
929 ) RETURN VARCHAR2 IS
930 l_spm_rec spm_rec_type;
931 l_row_notfound BOOLEAN := TRUE;
932 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
933 BEGIN
934 x_spm_rec := p_spm_rec;
935
936 -- Get current database values
937 l_spm_rec := get_rec( p_spm_rec, l_row_notfound);
938
939 IF (l_row_notfound) THEN
940 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
941 END IF;
942 IF (x_spm_rec.id = Okl_Api.G_MISS_NUM)
943 THEN
944 x_spm_rec.id := l_spm_rec.id;
945 END IF;
946 IF (x_spm_rec.object_version_number = Okl_Api.G_MISS_NUM)
947 THEN
948 x_spm_rec.object_version_number := l_spm_rec.object_version_number;
949 END IF;
950 IF (x_spm_rec.khr_id = Okl_Api.G_MISS_NUM)
951 THEN
952 x_spm_rec.khr_id := l_spm_rec.khr_id;
953 END IF;
954 IF (x_spm_rec.sif_id = Okl_Api.G_MISS_NUM)
955 THEN
956 x_spm_rec.sif_id := l_spm_rec.sif_id;
957 END IF;
958 IF (x_spm_rec.name = Okl_Api.G_MISS_CHAR) THEN
959 x_spm_rec.name := l_spm_rec.name;
960 END IF;
961 IF (x_spm_rec.display_yn = Okl_Api.G_MISS_CHAR) THEN
962 x_spm_rec.display_yn := l_spm_rec.display_yn;
963 END IF;
964 IF (x_spm_rec.update_yn = Okl_Api.G_MISS_CHAR) THEN
965 x_spm_rec.update_yn := l_spm_rec.update_yn;
966 END IF;
967 IF (x_spm_rec.default_value = Okl_Api.G_MISS_CHAR) THEN
968
969 x_spm_rec.default_value := l_spm_rec.default_value;
970 END IF;
971 IF (x_spm_rec.parameter_value = Okl_Api.G_MISS_CHAR) THEN
972 x_spm_rec.parameter_value := l_spm_rec.parameter_value;
973 END IF;
974 IF (x_spm_rec.prc_eng_ident = Okl_Api.G_MISS_CHAR) THEN
975 x_spm_rec.prc_eng_ident := l_spm_rec.prc_eng_ident;
976 END IF;
977 IF (x_spm_rec.description = Okl_Api.G_MISS_CHAR) THEN
978 x_spm_rec.description := l_spm_rec.description;
979 END IF;
980 IF (x_spm_rec.created_by = Okl_Api.G_MISS_NUM)
981 THEN
982 x_spm_rec.created_by := l_spm_rec.created_by;
983 END IF;
984 IF (x_spm_rec.creation_date = Okl_Api.G_MISS_DATE)
985 THEN
986 x_spm_rec.creation_date := l_spm_rec.creation_date;
987 END IF;
988 IF (x_spm_rec.last_updated_by = Okl_Api.G_MISS_NUM)
989 THEN
990 x_spm_rec.last_updated_by := l_spm_rec.last_updated_by;
991 END IF;
992 IF (x_spm_rec.last_update_date = Okl_Api.G_MISS_DATE)
993 THEN
994 x_spm_rec.last_update_date := l_spm_rec.last_update_date;
995 END IF;
996 IF (x_spm_rec.last_update_login = Okl_Api.G_MISS_NUM)
997 THEN
998 x_spm_rec.last_update_login := l_spm_rec.last_update_login;
999 END IF;
1000 RETURN(l_return_status);
1001 END populate_new_record;
1002
1003 ---------------------------------------------
1004 -- Set_Attributes for:OKL_SIF_PRICING_PARAMS --
1005 ---------------------------------------------
1006 FUNCTION Set_Attributes (
1007 p_spm_rec IN spm_rec_type,
1008 x_spm_rec OUT NOCOPY spm_rec_type
1009 ) RETURN VARCHAR2 IS
1010 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1011 BEGIN
1012 x_spm_rec := p_spm_rec;
1013 RETURN(l_return_status);
1014 END Set_Attributes;
1015
1016 BEGIN
1017 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1018 p_init_msg_list,
1019 '_PVT',
1020 x_return_status);
1021 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1022 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1023 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1024 RAISE Okl_Api.G_EXCEPTION_ERROR;
1025 END IF;
1026
1027 --- Setting item attributes
1028 l_return_status := Set_Attributes(
1029 p_spm_rec, -- IN
1030 l_spm_rec); -- OUT
1031
1032 --- If any errors happen abort API
1033 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1034 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1035 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1036 RAISE Okl_Api.G_EXCEPTION_ERROR;
1037 END IF;
1038
1039 l_return_status := populate_new_record(l_spm_rec, l_def_spm_rec);
1040 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1041 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1042 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1043 RAISE Okl_Api.G_EXCEPTION_ERROR;
1044 END IF;
1045
1046 UPDATE OKL_SIF_PRICING_PARAMS
1047 SET ID = l_def_spm_rec.id
1048 ,OBJECT_VERSION_NUMBER = l_def_spm_rec.object_version_number
1049 ,SIF_ID = l_def_spm_rec.sif_id
1050 ,KHR_ID = l_def_spm_rec.khr_id
1051 ,NAME = l_def_spm_rec.name
1052 ,DISPLAY_YN = l_def_spm_rec.display_yn
1053 ,UPDATE_YN = l_def_spm_rec.update_yn
1054 ,DEFAULT_VALUE = l_def_spm_rec.default_value
1055 ,PARAMETER_VALUE = l_def_spm_rec.parameter_value
1056 ,PRC_ENG_IDENT = l_def_spm_rec.prc_eng_ident
1057 ,DESCRIPTION = l_def_spm_rec.description
1058 ,CREATED_BY = l_def_spm_rec.created_by
1059 ,CREATION_DATE = l_def_spm_rec.creation_date
1060 ,LAST_UPDATED_BY = l_def_spm_rec.last_updated_by
1061 ,LAST_UPDATE_DATE = l_def_spm_rec.last_update_date
1062 ,LAST_UPDATE_LOGIN = l_def_spm_rec.last_update_login
1063 WHERE ID = l_def_spm_rec.id;
1064
1065 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1066 EXCEPTION
1067 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1068 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1069
1070 (
1071 l_api_name,
1072 G_PKG_NAME,
1073 'Okl_Api.G_RET_STS_ERROR',
1074 x_msg_count,
1075 x_msg_data,
1076 '_PVT'
1077 );
1078 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1079 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1080 (
1081 l_api_name,
1082 G_PKG_NAME,
1083 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1084 x_msg_count,
1085 x_msg_data,
1086 '_PVT'
1087 );
1088 WHEN OTHERS THEN
1089 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1090 (
1091 l_api_name,
1092 G_PKG_NAME,
1093 'OTHERS',
1094 x_msg_count,
1095 x_msg_data,
1096 '_PVT'
1097 );
1098 END update_row;
1099
1100 -------------------------------------------------------
1101 -- PL/SQL TBL update_row for: OKL_SIF_PRICING_PARAMS --
1102 -------------------------------------------------------
1103 PROCEDURE update_row(
1104 p_api_version IN NUMBER,
1105 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1106 x_return_status OUT NOCOPY VARCHAR2,
1107 x_msg_count OUT NOCOPY NUMBER,
1108 x_msg_data OUT NOCOPY VARCHAR2,
1109 p_spmv_rec IN spmv_rec_type) IS
1110
1111 l_api_version CONSTANT NUMBER := 1;
1112 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1113 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1114 l_spmv_rec spmv_rec_type := p_spmv_rec;
1115 l_def_spmv_rec spmv_rec_type;
1116 l_spm_rec spm_rec_type;
1117 lx_spm_rec spm_rec_type;
1118 -------------------------------
1119 -- FUNCTION fill_who_columns --
1120 -------------------------------
1121 FUNCTION fill_who_columns (
1122 p_spmv_rec IN spmv_rec_type
1123 ) RETURN spmv_rec_type IS
1124 l_spmv_rec spmv_rec_type := p_spmv_rec;
1125 BEGIN
1126 l_spmv_rec.LAST_UPDATE_DATE := SYSDATE;
1127 l_spmv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1128 l_spmv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1129 RETURN(l_spmv_rec);
1130 END fill_who_columns;
1131 ----------------------------------
1132 -- FUNCTION populate_new_record --
1133 ----------------------------------
1134 FUNCTION populate_new_record (
1135 p_spmv_rec IN spmv_rec_type,
1136 x_spmv_rec OUT NOCOPY spmv_rec_type
1137 ) RETURN VARCHAR2 IS
1138 l_spmv_rec spmv_rec_type;
1139 l_row_notfound BOOLEAN := TRUE;
1140 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1141 BEGIN
1142 x_spmv_rec := p_spmv_rec;
1143
1144 -- Get current database values
1145 l_spmv_rec := get_rec(p_spmv_rec, l_row_notfound);
1146
1147 IF (l_row_notfound) THEN
1148 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1149 END IF;
1150 IF (x_spmv_rec.id = Okl_Api.G_MISS_NUM)
1151 THEN
1152 x_spmv_rec.id := l_spm_rec.id;
1153 END IF;
1154 IF (x_spmv_rec.object_version_number = Okl_Api.G_MISS_NUM)
1155 THEN
1156 x_spmv_rec.object_version_number := l_spmv_rec.object_version_number;
1157 END IF;
1158 IF (x_spmv_rec.khr_id = Okl_Api.G_MISS_NUM)
1159 THEN
1160 x_spmv_rec.khr_id := l_spmv_rec.khr_id;
1161 END IF;
1162 IF (x_spmv_rec.sif_id = Okl_Api.G_MISS_NUM)
1163 THEN
1164 x_spmv_rec.sif_id := l_spm_rec.sif_id;
1165 END IF;
1166 IF (x_spmv_rec.name = Okl_Api.G_MISS_CHAR) THEN
1167 x_spmv_rec.name := l_spmv_rec.name;
1168 END IF;
1169 IF (x_spmv_rec.display_yn = Okl_Api.G_MISS_CHAR) THEN
1170 x_spmv_rec.display_yn := l_spmv_rec.display_yn;
1171 END IF;
1172 IF (x_spmv_rec.update_yn = Okl_Api.G_MISS_CHAR) THEN
1173
1174 x_spmv_rec.update_yn := l_spmv_rec.update_yn;
1175 END IF;
1176 IF (x_spmv_rec.default_value = Okl_Api.G_MISS_CHAR) THEN
1177 x_spmv_rec.default_value := l_spmv_rec.default_value;
1178 END IF;
1179 IF (x_spmv_rec.parameter_value = Okl_Api.G_MISS_CHAR) THEN
1180 x_spmv_rec.parameter_value := l_spmv_rec.parameter_value;
1181 END IF;
1182 IF (x_spmv_rec.prc_eng_ident = Okl_Api.G_MISS_CHAR) THEN
1183 x_spmv_rec.prc_eng_ident := l_spmv_rec.prc_eng_ident;
1184 END IF;
1185 IF (x_spmv_rec.description = Okl_Api.G_MISS_CHAR) THEN
1186 x_spmv_rec.description := l_spmv_rec.description;
1187 END IF;
1188 IF (x_spmv_rec.created_by = Okl_Api.G_MISS_NUM)
1189 THEN
1190 x_spmv_rec.created_by := l_spmv_rec.created_by;
1191 END IF;
1192 IF (x_spmv_rec.creation_date = Okl_Api.G_MISS_DATE)
1193 THEN
1194 x_spmv_rec.creation_date := l_spmv_rec.creation_date;
1195 END IF;
1196 IF (x_spmv_rec.last_updated_by = Okl_Api.G_MISS_NUM)
1197 THEN
1198 x_spmv_rec.last_updated_by := l_spmv_rec.last_updated_by;
1199 END IF;
1200 IF (x_spmv_rec.last_update_date = Okl_Api.G_MISS_DATE)
1201 THEN
1202 x_spmv_rec.last_update_date := l_spmv_rec.last_update_date;
1203 END IF;
1204 IF (x_spmv_rec.last_update_login = Okl_Api.G_MISS_NUM)
1205 THEN
1206 x_spmv_rec.last_update_login := l_spmv_rec.last_update_login;
1207 END IF;
1208 RETURN(l_return_status);
1209 END populate_new_record;
1210 ----------------------------------------------
1211 -- Set_Attributes for: OKL_ST_GEN_PRC_PARAMS_v --
1212 ----------------------------------------------
1213 FUNCTION Set_Attributes (
1214 p_spmv_rec IN spmv_rec_type,
1215 x_spmv_rec OUT NOCOPY spmv_rec_type
1216 ) RETURN VARCHAR2 IS
1217 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1218 BEGIN
1219 x_spmv_rec := p_spmv_rec;
1220 RETURN(l_return_status);
1221 END Set_Attributes;
1222 BEGIN
1223 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1224 G_PKG_NAME,
1225 p_init_msg_list,
1226 l_api_version,
1227 p_api_version,
1228 '_PVT',
1229 x_return_status);
1230 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1231 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1232 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1233 RAISE Okl_Api.G_EXCEPTION_ERROR;
1234 END IF;
1235 --- Setting item attributes
1236 l_return_status := Set_Attributes(
1237 p_spmv_rec, -- IN
1238 l_spmv_rec); -- OUT
1239 --- If any errors happen abort API
1240 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1241 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1242 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1243 RAISE Okl_Api.G_EXCEPTION_ERROR;
1244 END IF;
1245
1246 l_return_status := populate_new_record(l_spmv_rec, l_def_spmv_rec);
1247 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1248 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1249 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1250 RAISE Okl_Api.G_EXCEPTION_ERROR;
1251 END IF;
1252 l_def_spmv_rec := fill_who_columns(l_def_spmv_rec);
1253
1254 --- Validate all non-missing attributes (Item Level Validation)
1255 l_return_status := Validate_Attributes(l_def_spmv_rec);
1256
1257 --- If any errors happen abort API
1258 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1259 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1260 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1261 RAISE Okl_Api.G_EXCEPTION_ERROR;
1262 END IF;
1263
1264 l_return_status := Validate_Record(l_def_spmv_rec);
1265 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1266 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1267 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1268 RAISE Okl_Api.G_EXCEPTION_ERROR;
1269 END IF;
1270
1271 --------------------------------------
1272 -- Move VIEW record to "Child" records
1273
1274 --------------------------------------
1275 migrate(l_def_spmv_rec, l_spm_rec);
1276 --------------------------------------------
1277 -- Call the UPDATE_ROW for each child record
1278 --------------------------------------------
1279 update_row(
1280 p_init_msg_list => p_init_msg_list,
1281 x_return_status => x_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_spm_rec => l_spm_rec
1285 );
1286 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1287 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1288 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1289 RAISE Okl_Api.G_EXCEPTION_ERROR;
1290 END IF;
1291
1292 migrate(lx_spm_rec, l_def_spmv_rec);
1293
1294 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1295 EXCEPTION
1296 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1297 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1298 (
1299 l_api_name,
1300 G_PKG_NAME,
1301 'Okl_Api.G_RET_STS_ERROR',
1302 x_msg_count,
1303 x_msg_data,
1304 '_PVT'
1305 );
1306 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1307 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1308 (
1309 l_api_name,
1310 G_PKG_NAME,
1311 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1312 x_msg_count,
1313 x_msg_data,
1314 '_PVT'
1315 );
1316 WHEN OTHERS THEN
1317 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1318 (
1319 l_api_name,
1320 G_PKG_NAME,
1321 'OTHERS',
1322 x_msg_count,
1323 x_msg_data,
1324 '_PVT'
1325 );
1326 END;
1327
1328 -------------------------------------------------------
1329 -- PL/SQL TBL update_row for:OKL_SIF_PRICING_PARAMS_V --
1330 -------------------------------------------------------
1331 PROCEDURE update_row(
1332 p_api_version IN NUMBER,
1333 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 x_msg_count OUT NOCOPY NUMBER,
1336 x_msg_data OUT NOCOPY VARCHAR2,
1337 p_spmv_tbl IN spmv_tbl_type) IS
1338
1339 l_api_version CONSTANT NUMBER := 1;
1340 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1341 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1342 i NUMBER := 0;
1343
1344 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1345 BEGIN
1346 Okl_Api.init_msg_list(p_init_msg_list);
1347 -- Make sure PL/SQL table has records in it before passing
1348 IF (p_spmv_tbl.COUNT > 0) THEN
1349 i := p_spmv_tbl.FIRST;
1350 LOOP
1351 update_row (
1352 p_api_version => l_api_version,
1353 p_init_msg_list => Okl_Api.G_FALSE,
1354 x_return_status => x_return_status,
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_spmv_rec => p_spmv_tbl(i));
1358
1359 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1360 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1361 l_overall_status := x_return_status;
1362 END IF;
1363 END IF;
1364
1365 EXIT WHEN (i = p_spmv_tbl.LAST);
1366 i := p_spmv_tbl.NEXT(i);
1367 END LOOP;
1368
1369 x_return_status := l_overall_status;
1370
1371 END IF;
1372 EXCEPTION
1373
1374 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1375 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1376 (
1377 l_api_name,
1378 G_PKG_NAME,
1379 'Okl_Api.G_RET_STS_ERROR',
1380 x_msg_count,
1381 x_msg_data,
1382 '_PVT'
1383 );
1384 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1385 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1386 (
1387 l_api_name,
1388 G_PKG_NAME,
1389 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1390 x_msg_count,
1391 x_msg_data,
1392 '_PVT'
1393 );
1394 WHEN OTHERS THEN
1395 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1396 (
1397 l_api_name,
1398 G_PKG_NAME,
1399 'OTHERS',
1400 x_msg_count,
1401 x_msg_data,
1402 '_PVT'
1403 );
1404 END update_row;
1405
1406 END; -- Package OKL_spm_PVT