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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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);
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;
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');
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
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;
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,
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,
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
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);
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;
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
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;
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: /*
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',
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,
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,
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);
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;
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');
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
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;
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,
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,
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;
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;
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
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
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,
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;
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');
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;
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: /*
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 (
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;
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');
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;
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: */
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: ---------------------------------------------------------------------------
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
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
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;
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
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');
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;
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);
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
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
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
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
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
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
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
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
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
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
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
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,
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:
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:
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,
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,
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;
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)
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);
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
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;
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);
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
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;
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;
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);
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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));
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,
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',
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: );
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,
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',
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: );
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',
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 --
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;
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
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;
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
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(
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
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;
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(
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,
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,
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: (
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,
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',
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: );
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,
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',
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: );
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',
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;
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 *****
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,
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;
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);
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
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;
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;
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);
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)
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);
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;
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);
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
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;
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;
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: --------------------------------------
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
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: --------------------------------------
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;
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);
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
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;
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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),
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,
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',
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: );
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,
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',
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: );
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',
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;
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
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;
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
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);
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;
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
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;
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
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;
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
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;
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;
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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,
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,
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;
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: --------------------------------------
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
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: --------------------------------------
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;
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);
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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));
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,
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',
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: );
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,
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',
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: );
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',
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: ----------------------------------
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);
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;
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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);
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;
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
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;
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
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(
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
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;
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);
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
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;
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;
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
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,
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,
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: (
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,
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',
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: );
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,
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',
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: );
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',
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;
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);
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;
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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);
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);
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,
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;
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
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(
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
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;
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);
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
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;
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;
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);
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)
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);
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;
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);
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
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;
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;
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:
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: --------------------------------------
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
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;
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);
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;
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);
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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),
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,
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',
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: );
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,
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',
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: );
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',
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,
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
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;
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
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;
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:
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: (
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,
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',
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: );
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,
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',
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: );
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',
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,
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,
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;
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: --------------------------------------
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
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: --------------------------------------
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;
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);
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
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
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: (
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,
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',
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: );
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,
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',
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: );
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',
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
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
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));
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,
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',
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: );
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,
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',
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: );
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',