31: WHERE okl_pdt_opt_vals_v.id = p_id;
32: l_okl_povv_pk okl_povv_pk_csr%ROWTYPE;
33: l_povv_rec povv_rec_type;
34: BEGIN
35: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
36: x_no_data_found := TRUE;
37: -- Get current database values
38: OPEN okl_povv_pk_csr (p_povv_rec.id);
39: FETCH okl_povv_pk_csr INTO
54: x_povv_rec := l_povv_rec;
55: EXCEPTION
56: WHEN OTHERS THEN
57: -- store SQL error message on message stack
58: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
59: p_msg_name => G_UNEXPECTED_ERROR,
60: p_token1 => G_SQLCODE_TOKEN,
61: p_token1_value => SQLCODE,
62: p_token2 => G_SQLERRM_TOKEN,
61: p_token1_value => SQLCODE,
62: p_token2 => G_SQLERRM_TOKEN,
63: p_token2_value => SQLERRM);
64: -- notify UNEXPECTED error for calling API.
65: x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
66:
67: IF (okl_povv_pk_csr%ISOPEN) THEN
68: CLOSE okl_povv_pk_csr;
69: END IF;
87: WHERE ponv.id = p_pon_id;
88: l_okl_ponv_pk okl_ponv_pk_csr%ROWTYPE;
89: l_ponv_rec ponv_rec_type;
90: BEGIN
91: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
92: x_no_data_found := TRUE;
93: -- Get current database values
94: OPEN okl_ponv_pk_csr (p_povv_rec.pon_id);
95: FETCH okl_ponv_pk_csr INTO
100: x_ponv_rec := l_ponv_rec;
101: EXCEPTION
102: WHEN OTHERS THEN
103: -- store SQL error message on message stack
104: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
105: p_msg_name => G_UNEXPECTED_ERROR,
106:
107: p_token1 => G_SQLCODE_TOKEN,
108: p_token1_value => SQLCODE,
108: p_token1_value => SQLCODE,
109: p_token2 => G_SQLERRM_TOKEN,
110: p_token2_value => SQLERRM);
111: -- notify UNEXPECTED error for calling API.
112: x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
113:
114: IF (okl_ponv_pk_csr%ISOPEN) THEN
115: CLOSE okl_ponv_pk_csr;
116: END IF;
122: -----------------------------------------------------------------------------
123:
124: PROCEDURE Check_Constraints (
125: p_api_version IN NUMBER,
126: p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
127: p_povv_rec IN povv_rec_type,
128: x_return_status OUT NOCOPY VARCHAR2,
129: x_msg_count OUT NOCOPY NUMBER,
130: x_msg_data OUT NOCOPY VARCHAR2,
178: l_token_6 VARCHAR2(1999);
179:
180: BEGIN
181: x_valid := TRUE;
182: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
183:
184: l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_OPT_VAL_SUMRY',
185: p_attribute_code => 'OKL_PRODUCT_OPTION_VALUES');
186:
205: l_row_not_found := okl_povv_chk%NOTFOUND;
206: CLOSE okl_povv_chk;
207:
208: IF l_row_not_found = FALSE THEN
209: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
210: p_msg_name => G_IN_USE,
211: p_token1 => G_PARENT_TABLE_TOKEN,
212: p_token1_value => l_token_1,
213: p_token2 => G_CHILD_TABLE_TOKEN,
212: p_token1_value => l_token_1,
213: p_token2 => G_CHILD_TABLE_TOKEN,
214: p_token2_value => l_token_3);
215: x_valid := FALSE;
216: x_return_status := Okl_Api.G_RET_STS_ERROR;
217: RAISE G_EXCEPTION_HALT_PROCESSING;
218: END IF;
219:
220: --CHECK FOR UNIQUENESS
217: RAISE G_EXCEPTION_HALT_PROCESSING;
218: END IF;
219:
220: --CHECK FOR UNIQUENESS
221: IF p_povv_rec.id = Okl_Api.G_MISS_NUM THEN
222: OPEN okl_pdt_opt_vals_unique (p_povv_rec.ove_id, p_povv_rec.pon_id);
223: FETCH okl_pdt_opt_vals_unique INTO l_unique_key;
224: IF okl_pdt_opt_vals_unique%FOUND THEN
225: --Okl_Api.set_message(G_APP_NAME,G_UNQS, G_TABLE_TOKEN,l_token_1);
221: IF p_povv_rec.id = Okl_Api.G_MISS_NUM THEN
222: OPEN okl_pdt_opt_vals_unique (p_povv_rec.ove_id, p_povv_rec.pon_id);
223: FETCH okl_pdt_opt_vals_unique INTO l_unique_key;
224: IF okl_pdt_opt_vals_unique%FOUND THEN
225: --Okl_Api.set_message(G_APP_NAME,G_UNQS, G_TABLE_TOKEN,l_token_1);
226: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
227: p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
228: p_token1 => G_TABLE_TOKEN,
229: p_token1_value => l_token_1,
222: OPEN okl_pdt_opt_vals_unique (p_povv_rec.ove_id, p_povv_rec.pon_id);
223: FETCH okl_pdt_opt_vals_unique INTO l_unique_key;
224: IF okl_pdt_opt_vals_unique%FOUND THEN
225: --Okl_Api.set_message(G_APP_NAME,G_UNQS, G_TABLE_TOKEN,l_token_1);
226: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
227: p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
228: p_token1 => G_TABLE_TOKEN,
229: p_token1_value => l_token_1,
230: p_token2 => G_COLUMN_TOKEN,
229: p_token1_value => l_token_1,
230: p_token2 => G_COLUMN_TOKEN,
231: p_token2_value => l_token_6);
232: x_valid := FALSE;
233: x_return_status := Okl_Api.G_RET_STS_ERROR;
234: RAISE G_EXCEPTION_HALT_PROCESSING;
235: ELSE
236: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
237: END IF;
232: x_valid := FALSE;
233: x_return_status := Okl_Api.G_RET_STS_ERROR;
234: RAISE G_EXCEPTION_HALT_PROCESSING;
235: ELSE
236: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
237: END IF;
238: CLOSE okl_pdt_opt_vals_unique;
239: END IF;
240:
246: l_row_not_found := okl_pov_pdt_fk_csr%NOTFOUND;
247: CLOSE okl_pov_pdt_fk_csr;
248:
249: IF l_row_not_found = FALSE THEN
250: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
251: p_msg_name => G_PAST_RECORDS);
252: x_valid := FALSE;
253: x_return_status := Okl_Api.G_RET_STS_ERROR;
254: RAISE G_EXCEPTION_HALT_PROCESSING;
249: IF l_row_not_found = FALSE THEN
250: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
251: p_msg_name => G_PAST_RECORDS);
252: x_valid := FALSE;
253: x_return_status := Okl_Api.G_RET_STS_ERROR;
254: RAISE G_EXCEPTION_HALT_PROCESSING;
255: END IF;
256: */
257:
255: END IF;
256: */
257:
258: -- Check for constraints dates
259: IF p_povv_rec.id = Okl_Api.G_MISS_NUM THEN
260: OPEN okl_pov_constraints_csr (p_povv_rec.ove_id,
261: p_povv_rec.from_date,
262: p_povv_rec.TO_DATE);
263: FETCH okl_pov_constraints_csr INTO l_check;
264: l_row_not_found := okl_pov_constraints_csr%NOTFOUND;
265: CLOSE okl_pov_constraints_csr;
266:
267: IF l_row_not_found = FALSE THEN
268: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
269: p_msg_name => G_DATES_MISMATCH,
270: p_token1 => G_PARENT_TABLE_TOKEN,
271: p_token1_value => l_token_5,
272: p_token2 => G_CHILD_TABLE_TOKEN,
271: p_token1_value => l_token_5,
272: p_token2 => G_CHILD_TABLE_TOKEN,
273: p_token2_value => l_token_4);
274: x_valid := FALSE;
275: x_return_status := Okl_Api.G_RET_STS_ERROR;
276: RAISE G_EXCEPTION_HALT_PROCESSING;
277: END IF;
278: END IF;
279:
283: -- with the next column
284: NULL;
285: WHEN OTHERS THEN
286: -- store SQL error message on message stack
287: Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
288: p_msg_name => G_UNEXPECTED_ERROR,
289: p_token1 => G_SQLCODE_TOKEN,
290: p_token1_value => SQLCODE,
291: p_token2 => G_SQLERRM_TOKEN,
290: p_token1_value => SQLCODE,
291: p_token2 => G_SQLERRM_TOKEN,
292: p_token2_value => SQLERRM);
293: x_valid := FALSE;
294: x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
295:
296: IF (okl_povv_chk%ISOPEN) THEN
297: CLOSE okl_povv_chk;
298: END IF;
327: PROCEDURE Validate_Ove_Id (
328: p_povv_rec IN povv_rec_type,
329: x_return_status OUT NOCOPY VARCHAR2
330: ) IS
331: l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
332: CURSOR okl_pdt_opt_vals_foreign1 (p_foreign OKL_PDT_OPT_VALS.OVE_ID%TYPE) IS
333: SELECT ID
334: FROM OKL_OPT_VALUES_V
335: WHERE OKL_OPT_VALUES_V.ID = p_foreign;
339:
340:
341: BEGIN
342: -- initialize return status
343: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
344:
345: l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_CRUPD',
346: p_attribute_code => 'OKL_OPTION_VALUE');
347:
345: l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_CRUPD',
346: p_attribute_code => 'OKL_OPTION_VALUE');
347:
348:
349: IF p_povv_rec.ove_id = Okl_Api.G_MISS_NUM OR
350: p_povv_rec.ove_id IS NULL
351: THEN
352: Okc_Api.set_message(Okl_Pov_Pvt.G_APP_NAME, Okl_Pov_Pvt.G_REQUIRED_VALUE,Okl_Pov_Pvt.G_COL_NAME_TOKEN,l_token_1);
353: x_return_status := Okl_Api.G_RET_STS_ERROR;
349: IF p_povv_rec.ove_id = Okl_Api.G_MISS_NUM OR
350: p_povv_rec.ove_id IS NULL
351: THEN
352: Okc_Api.set_message(Okl_Pov_Pvt.G_APP_NAME, Okl_Pov_Pvt.G_REQUIRED_VALUE,Okl_Pov_Pvt.G_COL_NAME_TOKEN,l_token_1);
353: x_return_status := Okl_Api.G_RET_STS_ERROR;
354: RAISE G_ITEM_NOT_FOUND_ERROR;
355: END IF;
356:
357: IF p_povv_rec.ove_id IS NOT NULL THEN
358: OPEN okl_pdt_opt_vals_foreign1 (p_povv_rec.ove_id);
359: FETCH okl_pdt_opt_vals_foreign1 INTO l_foreign_key;
360: IF okl_pdt_opt_vals_foreign1%NOTFOUND THEN
361: Okc_Api.set_message(Okl_Pov_Pvt.G_APP_NAME, Okl_Pov_Pvt.G_INVALID_KEY,Okl_Pov_Pvt.G_COL_NAME_TOKEN,l_token_1);
362: x_return_status := Okl_Api.G_RET_STS_ERROR;
363: RAISE G_ITEM_NOT_FOUND_ERROR;
364: ELSE
365: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
366: END IF;
361: Okc_Api.set_message(Okl_Pov_Pvt.G_APP_NAME, Okl_Pov_Pvt.G_INVALID_KEY,Okl_Pov_Pvt.G_COL_NAME_TOKEN,l_token_1);
362: x_return_status := Okl_Api.G_RET_STS_ERROR;
363: RAISE G_ITEM_NOT_FOUND_ERROR;
364: ELSE
365: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
366: END IF;
367: CLOSE okl_pdt_opt_vals_foreign1;
368: END IF;
369: EXCEPTION
369: EXCEPTION
370: WHEN G_ITEM_NOT_FOUND_ERROR THEN
371: NULL;
372: WHEN OTHERS THEN
373: Okl_Api.set_message(p_app_name =>Okl_Pov_Pvt.G_APP_NAME,
374: p_msg_name =>Okl_Pov_Pvt.G_UNEXPECTED_ERROR,
375: p_token1 =>Okl_Pov_Pvt.G_SQL_SQLCODE_TOKEN,
376: p_token1_value =>SQLCODE,
377: p_token2 =>Okl_Pov_Pvt.G_SQL_SQLERRM_TOKEN,
375: p_token1 =>Okl_Pov_Pvt.G_SQL_SQLCODE_TOKEN,
376: p_token1_value =>SQLCODE,
377: p_token2 =>Okl_Pov_Pvt.G_SQL_SQLERRM_TOKEN,
378: p_token2_value =>SQLERRM);
379: x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
380:
381: END Validate_Ove_Id;
382: ------end of Validate_Ove_Id-----------------------------------
383:
395: ---------------------------------------------------------------------------
396: FUNCTION Validate_Attributes(
397: p_povv_rec IN povv_rec_type
398: ) RETURN VARCHAR IS
399: x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
400: l_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
401:
402: BEGIN
403: -----CHECK FOR OVE_ID----------------------------
396: FUNCTION Validate_Attributes(
397: p_povv_rec IN povv_rec_type
398: ) RETURN VARCHAR IS
399: x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
400: l_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
401:
402: BEGIN
403: -----CHECK FOR OVE_ID----------------------------
404: Validate_Ove_Id (p_povv_rec,x_return_status);
401:
402: BEGIN
403: -----CHECK FOR OVE_ID----------------------------
404: Validate_Ove_Id (p_povv_rec,x_return_status);
405: IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
406: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
407: -- need to leave
408: l_return_status := x_return_status;
409: RAISE G_EXCEPTION_HALT_PROCESSING;
402: BEGIN
403: -----CHECK FOR OVE_ID----------------------------
404: Validate_Ove_Id (p_povv_rec,x_return_status);
405: IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
406: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
407: -- need to leave
408: l_return_status := x_return_status;
409: RAISE G_EXCEPTION_HALT_PROCESSING;
410: ELSE
418: NULL;
419: RETURN (l_return_status);
420:
421: WHEN OTHERS THEN
422: Okl_Api.set_message(p_app_name =>Okl_Pov_Pvt.G_APP_NAME,
423: p_msg_name =>Okl_Pov_Pvt.G_UNEXPECTED_ERROR,
424: p_token1 =>Okl_Pov_Pvt.G_SQL_SQLCODE_TOKEN,
425: p_token1_value =>SQLCODE,
426: p_token2 =>Okl_Pov_Pvt.G_SQL_SQLERRM_TOKEN,
424: p_token1 =>Okl_Pov_Pvt.G_SQL_SQLCODE_TOKEN,
425: p_token1_value =>SQLCODE,
426: p_token2 =>Okl_Pov_Pvt.G_SQL_SQLERRM_TOKEN,
427: p_token2_value =>SQLERRM);
428: l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
429: RETURN(l_return_status);
430:
431: END Validate_Attributes;
432:
437: -- PROCEDURE insert_povalues for: Okl_Pdt_opt_vals_V
438: ---------------------------------------------------------------------------
439:
440: PROCEDURE insert_povalues(p_api_version IN NUMBER,
441: p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
442: x_return_status OUT NOCOPY VARCHAR2,
443: x_msg_count OUT NOCOPY NUMBER,
444: x_msg_data OUT NOCOPY VARCHAR2,
445: p_pdtv_rec IN pdtv_rec_type,
448: x_povv_rec OUT NOCOPY povv_rec_type
449: ) IS
450: l_api_version CONSTANT NUMBER := 1;
451: l_api_name CONSTANT VARCHAR2(30) := 'insert_povalues';
452: l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
453: l_valid BOOLEAN;
454: l_povv_rec povv_rec_type;
455: l_ponv_rec ponv_rec_type;
456: l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
455: l_ponv_rec ponv_rec_type;
456: l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
457: l_row_notfound BOOLEAN := TRUE;
458: BEGIN
459: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
460:
461: l_povv_rec := p_povv_rec;
462:
463: l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
459: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
460:
461: l_povv_rec := p_povv_rec;
462:
463: l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
464: p_pkg_name => G_PKG_NAME,
465: p_init_msg_list => p_init_msg_list,
466: l_api_version => l_api_version,
467: p_api_version => p_api_version,
468: p_api_type => '_PVT',
469: x_return_status => l_return_status);
470:
471:
472: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
473: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
474: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
475: RAISE Okl_Api.G_EXCEPTION_ERROR;
476: END IF;
469: x_return_status => l_return_status);
470:
471:
472: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
473: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
474: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
475: RAISE Okl_Api.G_EXCEPTION_ERROR;
476: END IF;
477:
470:
471:
472: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
473: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
474: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
475: RAISE Okl_Api.G_EXCEPTION_ERROR;
476: END IF;
477:
478: --- Validate all non-missing attributes (Item Level Validation)
471:
472: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
473: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
474: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
475: RAISE Okl_Api.G_EXCEPTION_ERROR;
476: END IF;
477:
478: --- Validate all non-missing attributes (Item Level Validation)
479: l_return_status := Validate_Attributes(l_povv_rec);
477:
478: --- Validate all non-missing attributes (Item Level Validation)
479: l_return_status := Validate_Attributes(l_povv_rec);
480: --- If any errors happen abort API
481: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
482: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
483: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
484: RAISE Okl_Api.G_EXCEPTION_ERROR;
485: END IF;
478: --- Validate all non-missing attributes (Item Level Validation)
479: l_return_status := Validate_Attributes(l_povv_rec);
480: --- If any errors happen abort API
481: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
482: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
483: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
484: RAISE Okl_Api.G_EXCEPTION_ERROR;
485: END IF;
486:
479: l_return_status := Validate_Attributes(l_povv_rec);
480: --- If any errors happen abort API
481: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
482: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
483: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
484: RAISE Okl_Api.G_EXCEPTION_ERROR;
485: END IF;
486:
487:
480: --- If any errors happen abort API
481: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
482: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
483: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
484: RAISE Okl_Api.G_EXCEPTION_ERROR;
485: END IF;
486:
487:
488: get_parent_dates( p_povv_rec => l_povv_rec,
490: x_return_status => l_return_status,
491: x_ponv_rec => l_ponv_rec);
492:
493: IF (l_row_notfound) THEN
494: l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
495: ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
496: RAISE Okl_Api.G_EXCEPTION_ERROR;
497: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
498: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
491: x_ponv_rec => l_ponv_rec);
492:
493: IF (l_row_notfound) THEN
494: l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
495: ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
496: RAISE Okl_Api.G_EXCEPTION_ERROR;
497: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
498: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
499: END IF;
492:
493: IF (l_row_notfound) THEN
494: l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
495: ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
496: RAISE Okl_Api.G_EXCEPTION_ERROR;
497: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
498: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
499: END IF;
500:
493: IF (l_row_notfound) THEN
494: l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
495: ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
496: RAISE Okl_Api.G_EXCEPTION_ERROR;
497: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
498: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
499: END IF;
500:
501: --l_ptlv_rec := x_ptlv_rec;
494: l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
495: ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
496: RAISE Okl_Api.G_EXCEPTION_ERROR;
497: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
498: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
499: END IF;
500:
501: --l_ptlv_rec := x_ptlv_rec;
502: --assign parent dates.
512: x_msg_count => x_msg_count,
513: x_msg_data => x_msg_data,
514: x_valid => l_valid);
515:
516: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
517: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
518: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520: l_valid <> TRUE) THEN
513: x_msg_data => x_msg_data,
514: x_valid => l_valid);
515:
516: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
517: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
518: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520: l_valid <> TRUE) THEN
521: x_return_status := Okl_Api.G_RET_STS_ERROR;
514: x_valid => l_valid);
515:
516: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
517: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
518: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520: l_valid <> TRUE) THEN
521: x_return_status := Okl_Api.G_RET_STS_ERROR;
522: RAISE Okl_Api.G_EXCEPTION_ERROR;
515:
516: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
517: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
518: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520: l_valid <> TRUE) THEN
521: x_return_status := Okl_Api.G_RET_STS_ERROR;
522: RAISE Okl_Api.G_EXCEPTION_ERROR;
523: END IF;
517: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
518: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520: l_valid <> TRUE) THEN
521: x_return_status := Okl_Api.G_RET_STS_ERROR;
522: RAISE Okl_Api.G_EXCEPTION_ERROR;
523: END IF;
524:
525: /* public api to insert povalues */
518: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520: l_valid <> TRUE) THEN
521: x_return_status := Okl_Api.G_RET_STS_ERROR;
522: RAISE Okl_Api.G_EXCEPTION_ERROR;
523: END IF;
524:
525: /* public api to insert povalues */
526:
531: x_msg_data => x_msg_data,
532: p_povv_rec => l_povv_rec,
533: x_povv_rec => x_povv_rec);
534:
535: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
536: RAISE Okl_Api.G_EXCEPTION_ERROR;
537: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539:
532: p_povv_rec => l_povv_rec,
533: x_povv_rec => x_povv_rec);
534:
535: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
536: RAISE Okl_Api.G_EXCEPTION_ERROR;
537: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539:
540: END IF;
533: x_povv_rec => x_povv_rec);
534:
535: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
536: RAISE Okl_Api.G_EXCEPTION_ERROR;
537: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539:
540: END IF;
541:
534:
535: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
536: RAISE Okl_Api.G_EXCEPTION_ERROR;
537: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539:
540: END IF;
541:
542: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539:
540: END IF;
541:
542: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
543: x_msg_data => x_msg_data);
544: EXCEPTION
545: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
546: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
541:
542: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
543: x_msg_data => x_msg_data);
544: EXCEPTION
545: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
546: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
547: p_pkg_name => G_PKG_NAME,
548: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
549: x_msg_count => x_msg_count,
542: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
543: x_msg_data => x_msg_data);
544: EXCEPTION
545: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
546: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
547: p_pkg_name => G_PKG_NAME,
548: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
549: x_msg_count => x_msg_count,
550: x_msg_data => x_msg_data,
544: EXCEPTION
545: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
546: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
547: p_pkg_name => G_PKG_NAME,
548: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
549: x_msg_count => x_msg_count,
550: x_msg_data => x_msg_data,
551: p_api_type => '_PVT');
552: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
548: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
549: x_msg_count => x_msg_count,
550: x_msg_data => x_msg_data,
551: p_api_type => '_PVT');
552: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
553: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
554: p_pkg_name => G_PKG_NAME,
555: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
556: x_msg_count => x_msg_count,
549: x_msg_count => x_msg_count,
550: x_msg_data => x_msg_data,
551: p_api_type => '_PVT');
552: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
553: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
554: p_pkg_name => G_PKG_NAME,
555: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
556: x_msg_count => x_msg_count,
557: x_msg_data => x_msg_data,
551: p_api_type => '_PVT');
552: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
553: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
554: p_pkg_name => G_PKG_NAME,
555: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
556: x_msg_count => x_msg_count,
557: x_msg_data => x_msg_data,
558: p_api_type => '_PVT');
559: WHEN OTHERS THEN
556: x_msg_count => x_msg_count,
557: x_msg_data => x_msg_data,
558: p_api_type => '_PVT');
559: WHEN OTHERS THEN
560: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
561: p_pkg_name => G_PKG_NAME,
562: p_exc_name => 'OTHERS',
563: x_msg_count => x_msg_count,
564: x_msg_data => x_msg_data,
583: l_api_version CONSTANT NUMBER := 1;
584: l_povv_tbl povv_tbl_type;
585: l_api_name CONSTANT VARCHAR2(30) := 'delete_povalues';
586: l_povv_rec povv_rec_type;
587: l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
588: l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
589: i NUMBER;
590: l_valid BOOLEAN;
591:
584: l_povv_tbl povv_tbl_type;
585: l_api_name CONSTANT VARCHAR2(30) := 'delete_povalues';
586: l_povv_rec povv_rec_type;
587: l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
588: l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
589: i NUMBER;
590: l_valid BOOLEAN;
591:
592: BEGIN
590: l_valid BOOLEAN;
591:
592: BEGIN
593:
594: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
595:
596: l_povv_tbl := p_povv_tbl;
597:
598: l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
594: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
595:
596: l_povv_tbl := p_povv_tbl;
597:
598: l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
599: p_pkg_name => G_PKG_NAME,
600: p_init_msg_list => p_init_msg_list,
601: l_api_version => l_api_version,
602: p_api_version => p_api_version,
602: p_api_version => p_api_version,
603: p_api_type => '_PVT',
604: x_return_status => l_return_status);
605:
606: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
607: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
608: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
609: RAISE Okl_Api.G_EXCEPTION_ERROR;
610: END IF;
603: p_api_type => '_PVT',
604: x_return_status => l_return_status);
605:
606: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
607: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
608: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
609: RAISE Okl_Api.G_EXCEPTION_ERROR;
610: END IF;
611:
604: x_return_status => l_return_status);
605:
606: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
607: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
608: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
609: RAISE Okl_Api.G_EXCEPTION_ERROR;
610: END IF;
611:
612: /* check if the product asked to delete is used by contracts if yes halt the process*/
605:
606: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
607: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
608: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
609: RAISE Okl_Api.G_EXCEPTION_ERROR;
610: END IF;
611:
612: /* check if the product asked to delete is used by contracts if yes halt the process*/
613:
622: x_msg_count => x_msg_count,
623: x_msg_data => x_msg_data,
624: x_valid => l_valid);
625:
626: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
627: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
628: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630: l_valid <> TRUE) THEN
623: x_msg_data => x_msg_data,
624: x_valid => l_valid);
625:
626: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
627: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
628: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630: l_valid <> TRUE) THEN
631: x_return_status := Okl_Api.G_RET_STS_ERROR;
624: x_valid => l_valid);
625:
626: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
627: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
628: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630: l_valid <> TRUE) THEN
631: x_return_status := Okl_Api.G_RET_STS_ERROR;
632: RAISE Okl_Api.G_EXCEPTION_ERROR;
625:
626: IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
627: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
628: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630: l_valid <> TRUE) THEN
631: x_return_status := Okl_Api.G_RET_STS_ERROR;
632: RAISE Okl_Api.G_EXCEPTION_ERROR;
633: END IF;
627: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
628: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630: l_valid <> TRUE) THEN
631: x_return_status := Okl_Api.G_RET_STS_ERROR;
632: RAISE Okl_Api.G_EXCEPTION_ERROR;
633: END IF;
634:
635: EXIT WHEN (i = l_povv_tbl.LAST);
628: ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629: (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630: l_valid <> TRUE) THEN
631: x_return_status := Okl_Api.G_RET_STS_ERROR;
632: RAISE Okl_Api.G_EXCEPTION_ERROR;
633: END IF;
634:
635: EXIT WHEN (i = l_povv_tbl.LAST);
636:
646: x_msg_count => x_msg_count,
647: x_msg_data => x_msg_data,
648: p_povv_tbl => l_povv_tbl);
649:
650: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
651: RAISE Okl_Api.G_EXCEPTION_ERROR;
652: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
653: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654: END IF;
647: x_msg_data => x_msg_data,
648: p_povv_tbl => l_povv_tbl);
649:
650: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
651: RAISE Okl_Api.G_EXCEPTION_ERROR;
652: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
653: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654: END IF;
655:
648: p_povv_tbl => l_povv_tbl);
649:
650: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
651: RAISE Okl_Api.G_EXCEPTION_ERROR;
652: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
653: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654: END IF;
655:
656: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
649:
650: IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
651: RAISE Okl_Api.G_EXCEPTION_ERROR;
652: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
653: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654: END IF;
655:
656: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
657: x_msg_data => x_msg_data);
652: ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
653: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654: END IF;
655:
656: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
657: x_msg_data => x_msg_data);
658: EXCEPTION
659: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
660: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
655:
656: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
657: x_msg_data => x_msg_data);
658: EXCEPTION
659: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
660: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
661: p_pkg_name => G_PKG_NAME,
662: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
663: x_msg_count => x_msg_count,
656: Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
657: x_msg_data => x_msg_data);
658: EXCEPTION
659: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
660: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
661: p_pkg_name => G_PKG_NAME,
662: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
663: x_msg_count => x_msg_count,
664: x_msg_data => x_msg_data,
658: EXCEPTION
659: WHEN Okl_Api.G_EXCEPTION_ERROR THEN
660: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
661: p_pkg_name => G_PKG_NAME,
662: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
663: x_msg_count => x_msg_count,
664: x_msg_data => x_msg_data,
665: p_api_type => '_PVT');
666: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
662: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
663: x_msg_count => x_msg_count,
664: x_msg_data => x_msg_data,
665: p_api_type => '_PVT');
666: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
667: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
668: p_pkg_name => G_PKG_NAME,
669:
670: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
663: x_msg_count => x_msg_count,
664: x_msg_data => x_msg_data,
665: p_api_type => '_PVT');
666: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
667: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
668: p_pkg_name => G_PKG_NAME,
669:
670: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
671: x_msg_count => x_msg_count,
666: WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
667: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
668: p_pkg_name => G_PKG_NAME,
669:
670: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
671: x_msg_count => x_msg_count,
672: x_msg_data => x_msg_data,
673: p_api_type => '_PVT');
674: WHEN OTHERS THEN
671: x_msg_count => x_msg_count,
672: x_msg_data => x_msg_data,
673: p_api_type => '_PVT');
674: WHEN OTHERS THEN
675: x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
676: p_pkg_name => G_PKG_NAME,
677: p_exc_name => 'OTHERS',
678: x_msg_count => x_msg_count,
679: x_msg_data => x_msg_data,