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