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