[Home] [Help]
4: --------------------------------------------------------------------------------
5: --PACKAGE CONSTANTS
6: --------------------------------------------------------------------------------
7:
8: g_ret_sts_success CONSTANT varchar2(1) := okl_api.g_ret_sts_success;
9: g_ret_sts_unexp_error CONSTANT varchar2(1) := okl_api.g_ret_sts_unexp_error;
10: g_ret_sts_error CONSTANT varchar2(1) := okl_api.g_ret_sts_error;
11: g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12: g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
5: --PACKAGE CONSTANTS
6: --------------------------------------------------------------------------------
7:
8: g_ret_sts_success CONSTANT varchar2(1) := okl_api.g_ret_sts_success;
9: g_ret_sts_unexp_error CONSTANT varchar2(1) := okl_api.g_ret_sts_unexp_error;
10: g_ret_sts_error CONSTANT varchar2(1) := okl_api.g_ret_sts_error;
11: g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12: g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
13: g_miss_char CONSTANT varchar2(1) := okl_api.g_miss_char;
6: --------------------------------------------------------------------------------
7:
8: g_ret_sts_success CONSTANT varchar2(1) := okl_api.g_ret_sts_success;
9: g_ret_sts_unexp_error CONSTANT varchar2(1) := okl_api.g_ret_sts_unexp_error;
10: g_ret_sts_error CONSTANT varchar2(1) := okl_api.g_ret_sts_error;
11: g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12: g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
13: g_miss_char CONSTANT varchar2(1) := okl_api.g_miss_char;
14: g_miss_num CONSTANT number := okl_api.g_miss_num;
9: g_ret_sts_unexp_error CONSTANT varchar2(1) := okl_api.g_ret_sts_unexp_error;
10: g_ret_sts_error CONSTANT varchar2(1) := okl_api.g_ret_sts_error;
11: g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12: g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
13: g_miss_char CONSTANT varchar2(1) := okl_api.g_miss_char;
14: g_miss_num CONSTANT number := okl_api.g_miss_num;
15: g_miss_date CONSTANT date := okl_api.g_miss_date;
16: g_no_parent_record CONSTANT varchar2(200) := 'OKC_NO_PARENT_RECORD';
17: g_unexpected_error CONSTANT varchar2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
10: g_ret_sts_error CONSTANT varchar2(1) := okl_api.g_ret_sts_error;
11: g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12: g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
13: g_miss_char CONSTANT varchar2(1) := okl_api.g_miss_char;
14: g_miss_num CONSTANT number := okl_api.g_miss_num;
15: g_miss_date CONSTANT date := okl_api.g_miss_date;
16: g_no_parent_record CONSTANT varchar2(200) := 'OKC_NO_PARENT_RECORD';
17: g_unexpected_error CONSTANT varchar2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
18: g_sqlerrm_token CONSTANT varchar2(200) := 'SQLerrm';
11: g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12: g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
13: g_miss_char CONSTANT varchar2(1) := okl_api.g_miss_char;
14: g_miss_num CONSTANT number := okl_api.g_miss_num;
15: g_miss_date CONSTANT date := okl_api.g_miss_date;
16: g_no_parent_record CONSTANT varchar2(200) := 'OKC_NO_PARENT_RECORD';
17: g_unexpected_error CONSTANT varchar2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
18: g_sqlerrm_token CONSTANT varchar2(200) := 'SQLerrm';
19: g_sqlcode_token CONSTANT varchar2(200) := 'SQLcode';
56: FROM okl_fe_criterion_values
57: WHERE criterion_value_id = p_ecv_rec.criterion_value_id;
58: l_api_version CONSTANT number := 1;
59: l_api_name CONSTANT varchar2(30) := 'B_lock_row';
60: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
61: l_object_version_number okl_fe_criterion_values.object_version_number%TYPE;
62: lc_object_version_number okl_fe_criterion_values.object_version_number%TYPE;
63: l_row_notfound boolean := false;
64: lc_row_notfound boolean := false;
63: l_row_notfound boolean := false;
64: lc_row_notfound boolean := false;
65:
66: BEGIN
67: l_return_status := okl_api.start_activity(l_api_name
68: ,p_init_msg_list
69: ,'_PVT'
70: ,x_return_status);
71:
68: ,p_init_msg_list
69: ,'_PVT'
70: ,x_return_status);
71:
72: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
73: RAISE okl_api.g_exception_unexpected_error;
74: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
75: RAISE okl_api.g_exception_error;
76: END IF;
69: ,'_PVT'
70: ,x_return_status);
71:
72: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
73: RAISE okl_api.g_exception_unexpected_error;
74: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
75: RAISE okl_api.g_exception_error;
76: END IF;
77:
70: ,x_return_status);
71:
72: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
73: RAISE okl_api.g_exception_unexpected_error;
74: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
75: RAISE okl_api.g_exception_error;
76: END IF;
77:
78: BEGIN
71:
72: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
73: RAISE okl_api.g_exception_unexpected_error;
74: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
75: RAISE okl_api.g_exception_error;
76: END IF;
77:
78: BEGIN
79: OPEN lock_csr(p_ecv_rec);
85:
86: IF (lock_csr%ISOPEN) THEN
87: CLOSE lock_csr;
88: END IF;
89: okl_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
90: RAISE app_exceptions.record_lock_exception;
91: END;
92:
93: IF (l_row_notfound) THEN
97: CLOSE lchk_csr;
98: END IF;
99:
100: IF (lc_row_notfound) THEN
101: okl_api.set_message(g_fnd_app, g_form_record_deleted);
102: RAISE okl_api.g_exception_error;
103: ELSIF lc_object_version_number > p_ecv_rec.object_version_number THEN
104: okl_api.set_message(g_fnd_app, g_form_record_changed);
105: RAISE okl_api.g_exception_error;
98: END IF;
99:
100: IF (lc_row_notfound) THEN
101: okl_api.set_message(g_fnd_app, g_form_record_deleted);
102: RAISE okl_api.g_exception_error;
103: ELSIF lc_object_version_number > p_ecv_rec.object_version_number THEN
104: okl_api.set_message(g_fnd_app, g_form_record_changed);
105: RAISE okl_api.g_exception_error;
106: ELSIF lc_object_version_number <> p_ecv_rec.object_version_number THEN
100: IF (lc_row_notfound) THEN
101: okl_api.set_message(g_fnd_app, g_form_record_deleted);
102: RAISE okl_api.g_exception_error;
103: ELSIF lc_object_version_number > p_ecv_rec.object_version_number THEN
104: okl_api.set_message(g_fnd_app, g_form_record_changed);
105: RAISE okl_api.g_exception_error;
106: ELSIF lc_object_version_number <> p_ecv_rec.object_version_number THEN
107: okl_api.set_message(g_fnd_app, g_form_record_changed);
108: RAISE okl_api.g_exception_error;
101: okl_api.set_message(g_fnd_app, g_form_record_deleted);
102: RAISE okl_api.g_exception_error;
103: ELSIF lc_object_version_number > p_ecv_rec.object_version_number THEN
104: okl_api.set_message(g_fnd_app, g_form_record_changed);
105: RAISE okl_api.g_exception_error;
106: ELSIF lc_object_version_number <> p_ecv_rec.object_version_number THEN
107: okl_api.set_message(g_fnd_app, g_form_record_changed);
108: RAISE okl_api.g_exception_error;
109: ELSIF lc_object_version_number = - 1 THEN
103: ELSIF lc_object_version_number > p_ecv_rec.object_version_number THEN
104: okl_api.set_message(g_fnd_app, g_form_record_changed);
105: RAISE okl_api.g_exception_error;
106: ELSIF lc_object_version_number <> p_ecv_rec.object_version_number THEN
107: okl_api.set_message(g_fnd_app, g_form_record_changed);
108: RAISE okl_api.g_exception_error;
109: ELSIF lc_object_version_number = - 1 THEN
110: okl_api.set_message(g_app_name, g_record_logically_deleted);
111: RAISE okl_api.g_exception_error;
104: okl_api.set_message(g_fnd_app, g_form_record_changed);
105: RAISE okl_api.g_exception_error;
106: ELSIF lc_object_version_number <> p_ecv_rec.object_version_number THEN
107: okl_api.set_message(g_fnd_app, g_form_record_changed);
108: RAISE okl_api.g_exception_error;
109: ELSIF lc_object_version_number = - 1 THEN
110: okl_api.set_message(g_app_name, g_record_logically_deleted);
111: RAISE okl_api.g_exception_error;
112: END IF;
106: ELSIF lc_object_version_number <> p_ecv_rec.object_version_number THEN
107: okl_api.set_message(g_fnd_app, g_form_record_changed);
108: RAISE okl_api.g_exception_error;
109: ELSIF lc_object_version_number = - 1 THEN
110: okl_api.set_message(g_app_name, g_record_logically_deleted);
111: RAISE okl_api.g_exception_error;
112: END IF;
113: okl_api.end_activity(x_msg_count, x_msg_data);
114: EXCEPTION
107: okl_api.set_message(g_fnd_app, g_form_record_changed);
108: RAISE okl_api.g_exception_error;
109: ELSIF lc_object_version_number = - 1 THEN
110: okl_api.set_message(g_app_name, g_record_logically_deleted);
111: RAISE okl_api.g_exception_error;
112: END IF;
113: okl_api.end_activity(x_msg_count, x_msg_data);
114: EXCEPTION
115: WHEN g_exception_halt_validation THEN
109: ELSIF lc_object_version_number = - 1 THEN
110: okl_api.set_message(g_app_name, g_record_logically_deleted);
111: RAISE okl_api.g_exception_error;
112: END IF;
113: okl_api.end_activity(x_msg_count, x_msg_data);
114: EXCEPTION
115: WHEN g_exception_halt_validation THEN
116: x_return_status := okl_api.handle_exceptions(l_api_name
117: ,g_pkg_name
112: END IF;
113: okl_api.end_activity(x_msg_count, x_msg_data);
114: EXCEPTION
115: WHEN g_exception_halt_validation THEN
116: x_return_status := okl_api.handle_exceptions(l_api_name
117: ,g_pkg_name
118: ,'OKL_API.G_RET_STS_ERROR'
119: ,x_msg_count
120: ,x_msg_data
114: EXCEPTION
115: WHEN g_exception_halt_validation THEN
116: x_return_status := okl_api.handle_exceptions(l_api_name
117: ,g_pkg_name
118: ,'OKL_API.G_RET_STS_ERROR'
119: ,x_msg_count
120: ,x_msg_data
121: ,'_PVT');
122: WHEN okl_api.g_exception_error THEN
118: ,'OKL_API.G_RET_STS_ERROR'
119: ,x_msg_count
120: ,x_msg_data
121: ,'_PVT');
122: WHEN okl_api.g_exception_error THEN
123: x_return_status := okl_api.handle_exceptions(l_api_name
124: ,g_pkg_name
125: ,'OKL_API.G_RET_STS_ERROR'
126: ,x_msg_count
119: ,x_msg_count
120: ,x_msg_data
121: ,'_PVT');
122: WHEN okl_api.g_exception_error THEN
123: x_return_status := okl_api.handle_exceptions(l_api_name
124: ,g_pkg_name
125: ,'OKL_API.G_RET_STS_ERROR'
126: ,x_msg_count
127: ,x_msg_data
121: ,'_PVT');
122: WHEN okl_api.g_exception_error THEN
123: x_return_status := okl_api.handle_exceptions(l_api_name
124: ,g_pkg_name
125: ,'OKL_API.G_RET_STS_ERROR'
126: ,x_msg_count
127: ,x_msg_data
128: ,'_PVT');
129: WHEN okl_api.g_exception_unexpected_error THEN
125: ,'OKL_API.G_RET_STS_ERROR'
126: ,x_msg_count
127: ,x_msg_data
128: ,'_PVT');
129: WHEN okl_api.g_exception_unexpected_error THEN
130: x_return_status := okl_api.handle_exceptions(l_api_name
131: ,g_pkg_name
132: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
133: ,x_msg_count
126: ,x_msg_count
127: ,x_msg_data
128: ,'_PVT');
129: WHEN okl_api.g_exception_unexpected_error THEN
130: x_return_status := okl_api.handle_exceptions(l_api_name
131: ,g_pkg_name
132: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
133: ,x_msg_count
134: ,x_msg_data
128: ,'_PVT');
129: WHEN okl_api.g_exception_unexpected_error THEN
130: x_return_status := okl_api.handle_exceptions(l_api_name
131: ,g_pkg_name
132: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
133: ,x_msg_count
134: ,x_msg_data
135: ,'_PVT');
136: WHEN OTHERS THEN
133: ,x_msg_count
134: ,x_msg_data
135: ,'_PVT');
136: WHEN OTHERS THEN
137: x_return_status := okl_api.handle_exceptions(l_api_name
138: ,g_pkg_name
139: ,'OTHERS'
140: ,x_msg_count
141: ,x_msg_data
153: ,x_msg_data OUT NOCOPY varchar2
154: ,p_ecv_tbl IN okl_ecv_tbl) IS
155: l_api_version CONSTANT number := 1;
156: l_api_name CONSTANT varchar2(30) := 'tbl_lock_row';
157: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
158:
159: -- Begin Post-Generation Change
160: -- overall error status
161:
158:
159: -- Begin Post-Generation Change
160: -- overall error status
161:
162: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
163:
164: -- End Post-Generation Change
165:
166: i number := 0;
165:
166: i number := 0;
167:
168: BEGIN
169: okl_api.init_msg_list(p_init_msg_list);
170:
171: -- Make sure PL/SQL table has records in it before passing
172:
173: IF (p_ecv_tbl.COUNT > 0) THEN
173: IF (p_ecv_tbl.COUNT > 0) THEN
174: i := p_ecv_tbl.FIRST;
175:
176: LOOP
177: lock_row(p_init_msg_list => okl_api.g_false
178: ,x_return_status => x_return_status
179: ,x_msg_count => x_msg_count
180: ,x_msg_data => x_msg_data
181: ,p_ecv_rec => p_ecv_tbl(i));
182:
183: -- Begin Post-Generation Change
184: -- store the highest degree of error
185:
186: IF x_return_status <> okl_api.g_ret_sts_success THEN
187: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
188: l_overall_status := x_return_status;
189: END IF;
190: END IF;
183: -- Begin Post-Generation Change
184: -- store the highest degree of error
185:
186: IF x_return_status <> okl_api.g_ret_sts_success THEN
187: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
188: l_overall_status := x_return_status;
189: END IF;
190: END IF;
191:
205: END IF;
206:
207: EXCEPTION
208: WHEN g_exception_halt_validation THEN
209: x_return_status := okl_api.handle_exceptions(l_api_name
210: ,g_pkg_name
211: ,'OKL_API.G_RET_STS_ERROR'
212: ,x_msg_count
213: ,x_msg_data
207: EXCEPTION
208: WHEN g_exception_halt_validation THEN
209: x_return_status := okl_api.handle_exceptions(l_api_name
210: ,g_pkg_name
211: ,'OKL_API.G_RET_STS_ERROR'
212: ,x_msg_count
213: ,x_msg_data
214: ,'_PVT');
215: WHEN okl_api.g_exception_error THEN
211: ,'OKL_API.G_RET_STS_ERROR'
212: ,x_msg_count
213: ,x_msg_data
214: ,'_PVT');
215: WHEN okl_api.g_exception_error THEN
216: x_return_status := okl_api.handle_exceptions(l_api_name
217: ,g_pkg_name
218: ,'OKL_API.G_RET_STS_ERROR'
219: ,x_msg_count
212: ,x_msg_count
213: ,x_msg_data
214: ,'_PVT');
215: WHEN okl_api.g_exception_error THEN
216: x_return_status := okl_api.handle_exceptions(l_api_name
217: ,g_pkg_name
218: ,'OKL_API.G_RET_STS_ERROR'
219: ,x_msg_count
220: ,x_msg_data
214: ,'_PVT');
215: WHEN okl_api.g_exception_error THEN
216: x_return_status := okl_api.handle_exceptions(l_api_name
217: ,g_pkg_name
218: ,'OKL_API.G_RET_STS_ERROR'
219: ,x_msg_count
220: ,x_msg_data
221: ,'_PVT');
222: WHEN okl_api.g_exception_unexpected_error THEN
218: ,'OKL_API.G_RET_STS_ERROR'
219: ,x_msg_count
220: ,x_msg_data
221: ,'_PVT');
222: WHEN okl_api.g_exception_unexpected_error THEN
223: x_return_status := okl_api.handle_exceptions(l_api_name
224: ,g_pkg_name
225: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
226: ,x_msg_count
219: ,x_msg_count
220: ,x_msg_data
221: ,'_PVT');
222: WHEN okl_api.g_exception_unexpected_error THEN
223: x_return_status := okl_api.handle_exceptions(l_api_name
224: ,g_pkg_name
225: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
226: ,x_msg_count
227: ,x_msg_data
221: ,'_PVT');
222: WHEN okl_api.g_exception_unexpected_error THEN
223: x_return_status := okl_api.handle_exceptions(l_api_name
224: ,g_pkg_name
225: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
226: ,x_msg_count
227: ,x_msg_data
228: ,'_PVT');
229: WHEN OTHERS THEN
226: ,x_msg_count
227: ,x_msg_data
228: ,'_PVT');
229: WHEN OTHERS THEN
230: x_return_status := okl_api.handle_exceptions(l_api_name
231: ,g_pkg_name
232: ,'OTHERS'
233: ,x_msg_count
234: ,x_msg_data
246:
247: --
248: -- data is required
249:
250: IF (p_criterion_value_id IS NULL) OR (p_criterion_value_id = okl_api.g_miss_num) THEN
251: okl_api.set_message(p_app_name => g_app_name
252: ,p_msg_name => g_required_value
253: ,p_token1 => g_col_name_token
254: ,p_token1_value => 'CRITERION_VALUE_ID');
247: --
248: -- data is required
249:
250: IF (p_criterion_value_id IS NULL) OR (p_criterion_value_id = okl_api.g_miss_num) THEN
251: okl_api.set_message(p_app_name => g_app_name
252: ,p_msg_name => g_required_value
253: ,p_token1 => g_col_name_token
254: ,p_token1_value => 'CRITERION_VALUE_ID');
255: RAISE okl_api.g_exception_error;
251: okl_api.set_message(p_app_name => g_app_name
252: ,p_msg_name => g_required_value
253: ,p_token1 => g_col_name_token
254: ,p_token1_value => 'CRITERION_VALUE_ID');
255: RAISE okl_api.g_exception_error;
256: END IF;
257: RETURN g_ret_sts_success;
258: EXCEPTION
259: WHEN okl_api.g_exception_error THEN
255: RAISE okl_api.g_exception_error;
256: END IF;
257: RETURN g_ret_sts_success;
258: EXCEPTION
259: WHEN okl_api.g_exception_error THEN
260: RETURN g_ret_sts_error;
261: WHEN okl_api.g_exception_unexpected_error THEN
262: RETURN g_ret_sts_unexp_error;
263: WHEN OTHERS THEN
257: RETURN g_ret_sts_success;
258: EXCEPTION
259: WHEN okl_api.g_exception_error THEN
260: RETURN g_ret_sts_error;
261: WHEN okl_api.g_exception_unexpected_error THEN
262: RETURN g_ret_sts_unexp_error;
263: WHEN OTHERS THEN
264: okl_api.set_message(p_app_name => g_app_name
265: ,p_msg_name => g_db_error
260: RETURN g_ret_sts_error;
261: WHEN okl_api.g_exception_unexpected_error THEN
262: RETURN g_ret_sts_unexp_error;
263: WHEN OTHERS THEN
264: okl_api.set_message(p_app_name => g_app_name
265: ,p_msg_name => g_db_error
266: ,p_token1 => g_prog_name_token
267: ,p_token1_value => l_api_name
268: ,p_token2 => 'SQLCODE'
281:
282: BEGIN
283:
284: IF (p_object_version_number IS NULL) OR (p_object_version_number = g_miss_num) THEN
285: okl_api.set_message(p_app_name => g_app_name
286: ,p_msg_name => g_required_value
287: ,p_token1 => g_col_name_token
288: ,p_token1_value => 'object_version_number');
289: RAISE okl_api.g_exception_error;
285: okl_api.set_message(p_app_name => g_app_name
286: ,p_msg_name => g_required_value
287: ,p_token1 => g_col_name_token
288: ,p_token1_value => 'object_version_number');
289: RAISE okl_api.g_exception_error;
290: END IF;
291: RETURN g_ret_sts_success;
292: EXCEPTION
293: WHEN okl_api.g_exception_error THEN
289: RAISE okl_api.g_exception_error;
290: END IF;
291: RETURN g_ret_sts_success;
292: EXCEPTION
293: WHEN okl_api.g_exception_error THEN
294: RETURN g_ret_sts_error;
295: WHEN okl_api.g_exception_unexpected_error THEN
296: RETURN g_ret_sts_unexp_error;
297: WHEN OTHERS THEN
291: RETURN g_ret_sts_success;
292: EXCEPTION
293: WHEN okl_api.g_exception_error THEN
294: RETURN g_ret_sts_error;
295: WHEN okl_api.g_exception_unexpected_error THEN
296: RETURN g_ret_sts_unexp_error;
297: WHEN OTHERS THEN
298: okl_api.set_message(p_app_name => g_app_name
299: ,p_msg_name => g_db_error
294: RETURN g_ret_sts_error;
295: WHEN okl_api.g_exception_unexpected_error THEN
296: RETURN g_ret_sts_unexp_error;
297: WHEN OTHERS THEN
298: okl_api.set_message(p_app_name => g_app_name
299: ,p_msg_name => g_db_error
300: ,p_token1 => g_prog_name_token
301: ,p_token1_value => l_api_name
302: ,p_token2 => 'SQLCODE'
325:
326: --
327: -- data is required
328:
329: IF (p_criteria_id IS NULL) OR (p_criteria_id = okl_api.g_miss_num) THEN
330: okl_api.set_message(p_app_name => g_app_name
331: ,p_msg_name => g_required_value
332: ,p_token1 => g_col_name_token
333: ,p_token1_value => 'CRITERIA_ID');
326: --
327: -- data is required
328:
329: IF (p_criteria_id IS NULL) OR (p_criteria_id = okl_api.g_miss_num) THEN
330: okl_api.set_message(p_app_name => g_app_name
331: ,p_msg_name => g_required_value
332: ,p_token1 => g_col_name_token
333: ,p_token1_value => 'CRITERIA_ID');
334: RAISE okl_api.g_exception_error;
330: okl_api.set_message(p_app_name => g_app_name
331: ,p_msg_name => g_required_value
332: ,p_token1 => g_col_name_token
333: ,p_token1_value => 'CRITERIA_ID');
334: RAISE okl_api.g_exception_error;
335: END IF;
336:
337: -- enforce foreign key
338:
342:
343: -- if l_dummy_var is still set to default, data was not found
344:
345: IF (l_dummy_var = '?') THEN
346: okl_api.set_message(p_app_name => g_app_name
347: ,p_msg_name => g_no_parent_record
348: ,p_token1 => g_col_name_token
349: ,p_token1_value => 'CRITERIA_ID'
350: ,p_token2 => g_child_table_token
350: ,p_token2 => g_child_table_token
351: ,p_token2_value => 'OKL_FE_CRITERION_VALUES'
352: ,p_token3 => g_parent_table_token
353: ,p_token3_value => 'OKL_FE_CRITERIA');
354: RAISE okl_api.g_exception_error;
355: END IF;
356: RETURN g_ret_sts_success;
357: EXCEPTION
358: WHEN okl_api.g_exception_error THEN
354: RAISE okl_api.g_exception_error;
355: END IF;
356: RETURN g_ret_sts_success;
357: EXCEPTION
358: WHEN okl_api.g_exception_error THEN
359:
360: -- verify that cursor was closed
361:
362: IF l_ecl_csr%ISOPEN THEN
362: IF l_ecl_csr%ISOPEN THEN
363: CLOSE l_ecl_csr;
364: END IF;
365: RETURN g_ret_sts_error;
366: WHEN okl_api.g_exception_unexpected_error THEN
367:
368: -- verify that cursor was closed
369:
370: IF l_ecl_csr%ISOPEN THEN
377:
378: IF l_ecl_csr%ISOPEN THEN
379: CLOSE l_ecl_csr;
380: END IF;
381: okl_api.set_message(p_app_name => g_app_name
382: ,p_msg_name => g_db_error
383: ,p_token1 => g_prog_name_token
384: ,p_token1_value => l_api_name
385: ,p_token2 => 'SQLCODE'
480: l_ecv_rec okl_ecv_rec := p_ecv_rec;
481:
482: BEGIN
483:
484: IF (l_ecv_rec.criterion_value_id = okl_api.g_miss_num) THEN
485: l_ecv_rec.criterion_value_id := NULL;
486: END IF;
487:
488: IF (l_ecv_rec.object_version_number = okl_api.g_miss_num) THEN
484: IF (l_ecv_rec.criterion_value_id = okl_api.g_miss_num) THEN
485: l_ecv_rec.criterion_value_id := NULL;
486: END IF;
487:
488: IF (l_ecv_rec.object_version_number = okl_api.g_miss_num) THEN
489: l_ecv_rec.object_version_number := NULL;
490: END IF;
491:
492: IF (l_ecv_rec.criteria_id = okl_api.g_miss_num) THEN
488: IF (l_ecv_rec.object_version_number = okl_api.g_miss_num) THEN
489: l_ecv_rec.object_version_number := NULL;
490: END IF;
491:
492: IF (l_ecv_rec.criteria_id = okl_api.g_miss_num) THEN
493: l_ecv_rec.criteria_id := NULL;
494: END IF;
495:
496: IF (l_ecv_rec.operator_code = okl_api.g_miss_char) THEN
492: IF (l_ecv_rec.criteria_id = okl_api.g_miss_num) THEN
493: l_ecv_rec.criteria_id := NULL;
494: END IF;
495:
496: IF (l_ecv_rec.operator_code = okl_api.g_miss_char) THEN
497: l_ecv_rec.operator_code := NULL;
498: END IF;
499:
500: IF (l_ecv_rec.crit_cat_value2 = okl_api.g_miss_char) THEN
496: IF (l_ecv_rec.operator_code = okl_api.g_miss_char) THEN
497: l_ecv_rec.operator_code := NULL;
498: END IF;
499:
500: IF (l_ecv_rec.crit_cat_value2 = okl_api.g_miss_char) THEN
501: l_ecv_rec.crit_cat_value2 := NULL;
502: END IF;
503:
504: IF (l_ecv_rec.crit_cat_value1 = okl_api.g_miss_char) THEN
500: IF (l_ecv_rec.crit_cat_value2 = okl_api.g_miss_char) THEN
501: l_ecv_rec.crit_cat_value2 := NULL;
502: END IF;
503:
504: IF (l_ecv_rec.crit_cat_value1 = okl_api.g_miss_char) THEN
505: l_ecv_rec.crit_cat_value1 := NULL;
506: END IF;
507:
508: IF (l_ecv_rec.adjustment_factor = okl_api.g_miss_num) THEN
504: IF (l_ecv_rec.crit_cat_value1 = okl_api.g_miss_char) THEN
505: l_ecv_rec.crit_cat_value1 := NULL;
506: END IF;
507:
508: IF (l_ecv_rec.adjustment_factor = okl_api.g_miss_num) THEN
509: l_ecv_rec.adjustment_factor := NULL;
510: END IF;
511:
512: IF (l_ecv_rec.created_by = okl_api.g_miss_num) THEN
508: IF (l_ecv_rec.adjustment_factor = okl_api.g_miss_num) THEN
509: l_ecv_rec.adjustment_factor := NULL;
510: END IF;
511:
512: IF (l_ecv_rec.created_by = okl_api.g_miss_num) THEN
513: l_ecv_rec.created_by := NULL;
514: END IF;
515:
516: IF (l_ecv_rec.creation_date = okl_api.g_miss_date) THEN
512: IF (l_ecv_rec.created_by = okl_api.g_miss_num) THEN
513: l_ecv_rec.created_by := NULL;
514: END IF;
515:
516: IF (l_ecv_rec.creation_date = okl_api.g_miss_date) THEN
517: l_ecv_rec.creation_date := NULL;
518: END IF;
519:
520: IF (l_ecv_rec.last_updated_by = okl_api.g_miss_num) THEN
516: IF (l_ecv_rec.creation_date = okl_api.g_miss_date) THEN
517: l_ecv_rec.creation_date := NULL;
518: END IF;
519:
520: IF (l_ecv_rec.last_updated_by = okl_api.g_miss_num) THEN
521: l_ecv_rec.last_updated_by := NULL;
522: END IF;
523:
524: IF (l_ecv_rec.last_update_date = okl_api.g_miss_date) THEN
520: IF (l_ecv_rec.last_updated_by = okl_api.g_miss_num) THEN
521: l_ecv_rec.last_updated_by := NULL;
522: END IF;
523:
524: IF (l_ecv_rec.last_update_date = okl_api.g_miss_date) THEN
525: l_ecv_rec.last_update_date := NULL;
526: END IF;
527:
528: IF (l_ecv_rec.last_update_login = okl_api.g_miss_num) THEN
524: IF (l_ecv_rec.last_update_date = okl_api.g_miss_date) THEN
525: l_ecv_rec.last_update_date := NULL;
526: END IF;
527:
528: IF (l_ecv_rec.last_update_login = okl_api.g_miss_num) THEN
529: l_ecv_rec.last_update_login := NULL;
530: END IF;
531:
532: IF (l_ecv_rec.attribute_category = g_miss_char) THEN
601: RETURN(okc_p_util.raw_to_number(sys_guid()));
602: END get_seq_id;
603:
604: FUNCTION validate_attributes(p_ecv_rec IN okl_ecv_rec) RETURN varchar2 IS
605: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
606: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
607: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
608:
609: BEGIN
602: END get_seq_id;
603:
604: FUNCTION validate_attributes(p_ecv_rec IN okl_ecv_rec) RETURN varchar2 IS
605: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
606: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
607: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
608:
609: BEGIN
610:
615:
616: l_return_status := validate_criterion_value_id(p_ecv_rec.criterion_value_id);
617:
618: IF (l_return_status = g_ret_sts_unexp_error) THEN
619: RAISE okl_api.g_exception_unexpected_error;
620: ELSIF (l_return_status = g_ret_sts_error) THEN
621: RAISE okl_api.g_exception_error;
622: END IF;
623:
617:
618: IF (l_return_status = g_ret_sts_unexp_error) THEN
619: RAISE okl_api.g_exception_unexpected_error;
620: ELSIF (l_return_status = g_ret_sts_error) THEN
621: RAISE okl_api.g_exception_error;
622: END IF;
623:
624: --validate object version number
625:
625:
626: l_return_status := validate_object_version_number(p_ecv_rec.object_version_number);
627:
628: IF (l_return_status = g_ret_sts_unexp_error) THEN
629: RAISE okl_api.g_exception_unexpected_error;
630: ELSIF (l_return_status = g_ret_sts_error) THEN
631: RAISE okl_api.g_exception_error;
632: END IF;
633:
627:
628: IF (l_return_status = g_ret_sts_unexp_error) THEN
629: RAISE okl_api.g_exception_unexpected_error;
630: ELSIF (l_return_status = g_ret_sts_error) THEN
631: RAISE okl_api.g_exception_error;
632: END IF;
633:
634: --validate CRITERIA_ID
635:
635:
636: l_return_status := validate_criteria_id(p_ecv_rec.criteria_id);
637:
638: IF (l_return_status = g_ret_sts_unexp_error) THEN
639: RAISE okl_api.g_exception_unexpected_error;
640: ELSIF (l_return_status = g_ret_sts_error) THEN
641: RAISE okl_api.g_exception_error;
642: END IF;
643: RETURN(x_return_status);
637:
638: IF (l_return_status = g_ret_sts_unexp_error) THEN
639: RAISE okl_api.g_exception_unexpected_error;
640: ELSIF (l_return_status = g_ret_sts_error) THEN
641: RAISE okl_api.g_exception_error;
642: END IF;
643: RETURN(x_return_status);
644: EXCEPTION
645: WHEN okl_api.g_exception_error THEN
641: RAISE okl_api.g_exception_error;
642: END IF;
643: RETURN(x_return_status);
644: EXCEPTION
645: WHEN okl_api.g_exception_error THEN
646: RETURN g_ret_sts_error;
647: WHEN okl_api.g_exception_unexpected_error THEN
648: RETURN g_ret_sts_unexp_error;
649: WHEN OTHERS THEN
643: RETURN(x_return_status);
644: EXCEPTION
645: WHEN okl_api.g_exception_error THEN
646: RETURN g_ret_sts_error;
647: WHEN okl_api.g_exception_unexpected_error THEN
648: RETURN g_ret_sts_unexp_error;
649: WHEN OTHERS THEN
650: okl_api.set_message(p_app_name => g_app_name
651: ,p_msg_name => g_db_error
646: RETURN g_ret_sts_error;
647: WHEN okl_api.g_exception_unexpected_error THEN
648: RETURN g_ret_sts_unexp_error;
649: WHEN OTHERS THEN
650: okl_api.set_message(p_app_name => g_app_name
651: ,p_msg_name => g_db_error
652: ,p_token1 => g_prog_name_token
653: ,p_token1_value => l_api_name
654: ,p_token2 => 'SQLCODE'
658: RETURN g_ret_sts_unexp_error;
659: END validate_attributes;
660:
661: FUNCTION validate_record(p_ecv_rec IN OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
662: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
663: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
664: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
665: i number;
666: d date;
659: END validate_attributes;
660:
661: FUNCTION validate_record(p_ecv_rec IN OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
662: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
663: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
664: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
665: i number;
666: d date;
667:
672: --CRIT_CAT_DATEVAL will not be present. so no need to the validations
673:
674: IF p_ecv_rec.validate_record = 'Y' THEN
675: IF p_ecv_rec.data_type_code = 'NUMBER' THEN
676: IF p_ecv_rec.crit_cat_numval1 IS NULL OR p_ecv_rec.crit_cat_numval1 = okl_api.g_miss_num THEN
677: okl_api.set_message(p_app_name => g_app_name
678: ,p_msg_name => g_required_value
679: ,p_token1 => g_col_name_token
680: ,p_token1_value => 'CRIT_CAT_NUMVAL1');
673:
674: IF p_ecv_rec.validate_record = 'Y' THEN
675: IF p_ecv_rec.data_type_code = 'NUMBER' THEN
676: IF p_ecv_rec.crit_cat_numval1 IS NULL OR p_ecv_rec.crit_cat_numval1 = okl_api.g_miss_num THEN
677: okl_api.set_message(p_app_name => g_app_name
678: ,p_msg_name => g_required_value
679: ,p_token1 => g_col_name_token
680: ,p_token1_value => 'CRIT_CAT_NUMVAL1');
681: RAISE okl_api.g_exception_error;
677: okl_api.set_message(p_app_name => g_app_name
678: ,p_msg_name => g_required_value
679: ,p_token1 => g_col_name_token
680: ,p_token1_value => 'CRIT_CAT_NUMVAL1');
681: RAISE okl_api.g_exception_error;
682: ELSE
683: p_ecv_rec.crit_cat_value1 := fnd_number.number_to_canonical(p_ecv_rec.crit_cat_numval1);
684: IF p_ecv_rec.crit_cat_numval2 IS NOT NULL AND NOT p_ecv_rec.crit_cat_numval2 = g_miss_num THEN
685: p_ecv_rec.crit_cat_value2 := fnd_number.number_to_canonical(p_ecv_rec.crit_cat_numval2);
687: p_ecv_rec.crit_cat_value2 := NULL;
688: END IF;
689: END IF;
690: ELSIF p_ecv_rec.data_type_code = 'DATE' THEN
691: IF p_ecv_rec.crit_cat_dateval1 IS NULL OR p_ecv_rec.crit_cat_dateval1 = okl_api.g_miss_date THEN
692: okl_api.set_message(p_app_name => g_app_name
693: ,p_msg_name => g_required_value
694: ,p_token1 => g_col_name_token
695: ,p_token1_value => 'CRIT_CAT_DATEVAL1');
688: END IF;
689: END IF;
690: ELSIF p_ecv_rec.data_type_code = 'DATE' THEN
691: IF p_ecv_rec.crit_cat_dateval1 IS NULL OR p_ecv_rec.crit_cat_dateval1 = okl_api.g_miss_date THEN
692: okl_api.set_message(p_app_name => g_app_name
693: ,p_msg_name => g_required_value
694: ,p_token1 => g_col_name_token
695: ,p_token1_value => 'CRIT_CAT_DATEVAL1');
696: RAISE okl_api.g_exception_error;
692: okl_api.set_message(p_app_name => g_app_name
693: ,p_msg_name => g_required_value
694: ,p_token1 => g_col_name_token
695: ,p_token1_value => 'CRIT_CAT_DATEVAL1');
696: RAISE okl_api.g_exception_error;
697: ELSE
698: p_ecv_rec.crit_cat_value1 := fnd_date.date_to_canonical(p_ecv_rec.crit_cat_dateval1);
699: IF p_ecv_rec.crit_cat_dateval2 IS NOT NULL AND NOT p_ecv_rec.crit_cat_dateval2 = g_miss_date THEN
700: p_ecv_rec.crit_cat_value2 := fnd_date.date_to_canonical(p_ecv_rec.crit_cat_dateval2);
702: p_ecv_rec.crit_cat_value2 := NULL;
703: END IF;
704: END IF;
705: ELSIF p_ecv_rec.data_type_code = 'VARCHAR2' THEN
706: IF p_ecv_rec.crit_cat_value1 IS NULL OR p_ecv_rec.crit_cat_value1 = okl_api.g_miss_char THEN
707: okl_api.set_message(p_app_name => g_app_name
708: ,p_msg_name => g_required_value
709: ,p_token1 => g_col_name_token
710: ,p_token1_value => 'CRIT_CAT_VALUE1');
703: END IF;
704: END IF;
705: ELSIF p_ecv_rec.data_type_code = 'VARCHAR2' THEN
706: IF p_ecv_rec.crit_cat_value1 IS NULL OR p_ecv_rec.crit_cat_value1 = okl_api.g_miss_char THEN
707: okl_api.set_message(p_app_name => g_app_name
708: ,p_msg_name => g_required_value
709: ,p_token1 => g_col_name_token
710: ,p_token1_value => 'CRIT_CAT_VALUE1');
711: RAISE okl_api.g_exception_error;
707: okl_api.set_message(p_app_name => g_app_name
708: ,p_msg_name => g_required_value
709: ,p_token1 => g_col_name_token
710: ,p_token1_value => 'CRIT_CAT_VALUE1');
711: RAISE okl_api.g_exception_error;
712: END IF;
713: END IF;
714:
715: --if value type code is RANGE then min should be < than max
719:
720: --CRIT_CAT_NUMVAL2 is required for Range and Number
721:
722: IF p_ecv_rec.crit_cat_numval2 IS NULL OR p_ecv_rec.crit_cat_numval2 = g_miss_num THEN
723: okl_api.set_message(p_app_name => g_app_name
724: ,p_msg_name => g_required_value
725: ,p_token1 => g_col_name_token
726: ,p_token1_value => 'CRIT_CAT_NUMVAL2');
727: RAISE okl_api.g_exception_error;
723: okl_api.set_message(p_app_name => g_app_name
724: ,p_msg_name => g_required_value
725: ,p_token1 => g_col_name_token
726: ,p_token1_value => 'CRIT_CAT_NUMVAL2');
727: RAISE okl_api.g_exception_error;
728: END IF;
729: IF p_ecv_rec.crit_cat_numval1 > p_ecv_rec.crit_cat_numval2 THEN
730: okl_api.set_message(p_app_name => g_app_name
731: ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
726: ,p_token1_value => 'CRIT_CAT_NUMVAL2');
727: RAISE okl_api.g_exception_error;
728: END IF;
729: IF p_ecv_rec.crit_cat_numval1 > p_ecv_rec.crit_cat_numval2 THEN
730: okl_api.set_message(p_app_name => g_app_name
731: ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
732: RAISE okl_api.g_exception_error;
733: END IF;
734: ELSIF p_ecv_rec.data_type_code = 'DATE' THEN
728: END IF;
729: IF p_ecv_rec.crit_cat_numval1 > p_ecv_rec.crit_cat_numval2 THEN
730: okl_api.set_message(p_app_name => g_app_name
731: ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
732: RAISE okl_api.g_exception_error;
733: END IF;
734: ELSIF p_ecv_rec.data_type_code = 'DATE' THEN
735:
736: --CRIT_CAT_DATEVAL2 is required for Range and Number
735:
736: --CRIT_CAT_DATEVAL2 is required for Range and Number
737:
738: IF p_ecv_rec.crit_cat_dateval2 IS NULL OR p_ecv_rec.crit_cat_dateval2 = g_miss_date THEN
739: okl_api.set_message(p_app_name => g_app_name
740: ,p_msg_name => g_required_value
741: ,p_token1 => g_col_name_token
742: ,p_token1_value => 'CRIT_CAT_DATEVAL2');
743: RAISE okl_api.g_exception_error;
739: okl_api.set_message(p_app_name => g_app_name
740: ,p_msg_name => g_required_value
741: ,p_token1 => g_col_name_token
742: ,p_token1_value => 'CRIT_CAT_DATEVAL2');
743: RAISE okl_api.g_exception_error;
744: END IF;
745: IF p_ecv_rec.crit_cat_dateval1 > p_ecv_rec.crit_cat_dateval2 THEN
746: okl_api.set_message(p_app_name => g_app_name
747: ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
742: ,p_token1_value => 'CRIT_CAT_DATEVAL2');
743: RAISE okl_api.g_exception_error;
744: END IF;
745: IF p_ecv_rec.crit_cat_dateval1 > p_ecv_rec.crit_cat_dateval2 THEN
746: okl_api.set_message(p_app_name => g_app_name
747: ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
748: RAISE okl_api.g_exception_error;
749: END IF;
750: END IF;
744: END IF;
745: IF p_ecv_rec.crit_cat_dateval1 > p_ecv_rec.crit_cat_dateval2 THEN
746: okl_api.set_message(p_app_name => g_app_name
747: ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
748: RAISE okl_api.g_exception_error;
749: END IF;
750: END IF;
751: END IF;
752:
754:
755: IF p_ecv_rec.data_type_code = 'VARCHAR2' THEN
756: IF p_ecv_rec.value_type_code = 'SINGLE' THEN
757: IF p_ecv_rec.operator_code NOT IN('EQ', 'NE') THEN
758: okl_api.set_message(p_app_name => g_app_name
759: ,p_msg_name => 'OKL_INVALID_OP_SINGLE_VARCHAR2');
760: RAISE okl_api.g_exception_error;
761: END IF;
762: END IF;
756: IF p_ecv_rec.value_type_code = 'SINGLE' THEN
757: IF p_ecv_rec.operator_code NOT IN('EQ', 'NE') THEN
758: okl_api.set_message(p_app_name => g_app_name
759: ,p_msg_name => 'OKL_INVALID_OP_SINGLE_VARCHAR2');
760: RAISE okl_api.g_exception_error;
761: END IF;
762: END IF;
763: END IF;
764: END IF;
769: p_ecv_rec.crit_cat_value2 := p_ecv_rec.crit_cat_value1;
770: END IF;
771: RETURN(x_return_status);
772: EXCEPTION
773: WHEN okl_api.g_exception_error THEN
774: RETURN g_ret_sts_error;
775: WHEN okl_api.g_exception_unexpected_error THEN
776: RETURN g_ret_sts_unexp_error;
777: WHEN OTHERS THEN
771: RETURN(x_return_status);
772: EXCEPTION
773: WHEN okl_api.g_exception_error THEN
774: RETURN g_ret_sts_error;
775: WHEN okl_api.g_exception_unexpected_error THEN
776: RETURN g_ret_sts_unexp_error;
777: WHEN OTHERS THEN
778: okl_api.set_message(p_app_name => g_app_name
779: ,p_msg_name => g_db_error
774: RETURN g_ret_sts_error;
775: WHEN okl_api.g_exception_unexpected_error THEN
776: RETURN g_ret_sts_unexp_error;
777: WHEN OTHERS THEN
778: okl_api.set_message(p_app_name => g_app_name
779: ,p_msg_name => g_db_error
780: ,p_token1 => g_prog_name_token
781: ,p_token1_value => l_api_name
782: ,p_token2 => 'SQLCODE'
790: -- Procedure insert_row
791: --------------------------------------------------------------------------------
792:
793: PROCEDURE insert_row(p_api_version IN number
794: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
795: ,x_return_status OUT NOCOPY varchar2
796: ,x_msg_count OUT NOCOPY number
797: ,x_msg_data OUT NOCOPY varchar2
798: ,p_ecv_rec IN okl_ecv_rec
798: ,p_ecv_rec IN okl_ecv_rec
799: ,x_ecv_rec OUT NOCOPY okl_ecv_rec) IS
800: l_api_version CONSTANT number := 1;
801: l_api_name CONSTANT varchar2(30) := 'insert_row';
802: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
803: l_ecv_rec okl_ecv_rec;
804: l_def_ecv_rec okl_ecv_rec;
805:
806: FUNCTION fill_who_columns(p_ecv_rec IN okl_ecv_rec) RETURN okl_ecv_rec IS
816: END fill_who_columns;
817:
818: FUNCTION set_attributes(p_ecv_rec IN okl_ecv_rec
819: ,x_ecv_rec OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
820: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
821:
822: BEGIN
823: x_ecv_rec := p_ecv_rec;
824: x_ecv_rec.object_version_number := 1;
829: RETURN(l_return_status);
830: END set_attributes;
831:
832: BEGIN
833: l_return_status := okl_api.start_activity(l_api_name
834: ,g_pkg_name
835: ,p_init_msg_list
836: ,l_api_version
837: ,p_api_version
837: ,p_api_version
838: ,'_PVT'
839: ,x_return_status);
840:
841: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
842: RAISE okl_api.g_exception_unexpected_error;
843: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
844: RAISE okl_api.g_exception_error;
845: END IF;
838: ,'_PVT'
839: ,x_return_status);
840:
841: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
842: RAISE okl_api.g_exception_unexpected_error;
843: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
844: RAISE okl_api.g_exception_error;
845: END IF;
846:
839: ,x_return_status);
840:
841: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
842: RAISE okl_api.g_exception_unexpected_error;
843: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
844: RAISE okl_api.g_exception_error;
845: END IF;
846:
847: --null out defaults
840:
841: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
842: RAISE okl_api.g_exception_unexpected_error;
843: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
844: RAISE okl_api.g_exception_error;
845: END IF;
846:
847: --null out defaults
848:
851: --Setting Item Attributes
852:
853: l_return_status := set_attributes(l_ecv_rec, l_def_ecv_rec);
854:
855: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
856: RAISE okl_api.g_exception_unexpected_error;
857: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
858: RAISE okl_api.g_exception_error;
859: END IF;
852:
853: l_return_status := set_attributes(l_ecv_rec, l_def_ecv_rec);
854:
855: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
856: RAISE okl_api.g_exception_unexpected_error;
857: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
858: RAISE okl_api.g_exception_error;
859: END IF;
860:
853: l_return_status := set_attributes(l_ecv_rec, l_def_ecv_rec);
854:
855: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
856: RAISE okl_api.g_exception_unexpected_error;
857: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
858: RAISE okl_api.g_exception_error;
859: END IF;
860:
861: --fill who columns
854:
855: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
856: RAISE okl_api.g_exception_unexpected_error;
857: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
858: RAISE okl_api.g_exception_error;
859: END IF;
860:
861: --fill who columns
862:
868: l_return_status := validate_attributes(l_def_ecv_rec);
869:
870: --
871:
872: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
873: RAISE okl_api.g_exception_unexpected_error;
874: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
875: RAISE okl_api.g_exception_error;
876: END IF;
869:
870: --
871:
872: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
873: RAISE okl_api.g_exception_unexpected_error;
874: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
875: RAISE okl_api.g_exception_error;
876: END IF;
877:
870: --
871:
872: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
873: RAISE okl_api.g_exception_unexpected_error;
874: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
875: RAISE okl_api.g_exception_error;
876: END IF;
877:
878: --validate record
871:
872: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
873: RAISE okl_api.g_exception_unexpected_error;
874: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
875: RAISE okl_api.g_exception_error;
876: END IF;
877:
878: --validate record
879: --
879: --
880:
881: l_return_status := validate_record(l_def_ecv_rec);
882:
883: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
884: RAISE okl_api.g_exception_unexpected_error;
885: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
886: RAISE okl_api.g_exception_error;
887: END IF; --insert into table
880:
881: l_return_status := validate_record(l_def_ecv_rec);
882:
883: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
884: RAISE okl_api.g_exception_unexpected_error;
885: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
886: RAISE okl_api.g_exception_error;
887: END IF; --insert into table
888:
881: l_return_status := validate_record(l_def_ecv_rec);
882:
883: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
884: RAISE okl_api.g_exception_unexpected_error;
885: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
886: RAISE okl_api.g_exception_error;
887: END IF; --insert into table
888:
889: INSERT INTO okl_fe_criterion_values
882:
883: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
884: RAISE okl_api.g_exception_unexpected_error;
885: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
886: RAISE okl_api.g_exception_error;
887: END IF; --insert into table
888:
889: INSERT INTO okl_fe_criterion_values
890: (criterion_value_id
947: --Set OUT Values
948:
949: x_ecv_rec := l_def_ecv_rec;
950: x_return_status := l_return_status;
951: okl_api.end_activity(x_msg_count, x_msg_data);
952: EXCEPTION
953: WHEN g_exception_halt_validation THEN
954:
955: -- No action necessary. Validation can continue to next attribute/column
954:
955: -- No action necessary. Validation can continue to next attribute/column
956:
957: NULL;
958: WHEN okl_api.g_exception_error THEN
959: x_return_status := okl_api.handle_exceptions(l_api_name
960: ,g_pkg_name
961: ,'OKL_API.G_RET_STS_ERROR'
962: ,x_msg_count
955: -- No action necessary. Validation can continue to next attribute/column
956:
957: NULL;
958: WHEN okl_api.g_exception_error THEN
959: x_return_status := okl_api.handle_exceptions(l_api_name
960: ,g_pkg_name
961: ,'OKL_API.G_RET_STS_ERROR'
962: ,x_msg_count
963: ,x_msg_data
957: NULL;
958: WHEN okl_api.g_exception_error THEN
959: x_return_status := okl_api.handle_exceptions(l_api_name
960: ,g_pkg_name
961: ,'OKL_API.G_RET_STS_ERROR'
962: ,x_msg_count
963: ,x_msg_data
964: ,'_PVT');
965: WHEN okl_api.g_exception_unexpected_error THEN
961: ,'OKL_API.G_RET_STS_ERROR'
962: ,x_msg_count
963: ,x_msg_data
964: ,'_PVT');
965: WHEN okl_api.g_exception_unexpected_error THEN
966: x_return_status := okl_api.handle_exceptions(l_api_name
967: ,g_pkg_name
968: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
969: ,x_msg_count
962: ,x_msg_count
963: ,x_msg_data
964: ,'_PVT');
965: WHEN okl_api.g_exception_unexpected_error THEN
966: x_return_status := okl_api.handle_exceptions(l_api_name
967: ,g_pkg_name
968: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
969: ,x_msg_count
970: ,x_msg_data
964: ,'_PVT');
965: WHEN okl_api.g_exception_unexpected_error THEN
966: x_return_status := okl_api.handle_exceptions(l_api_name
967: ,g_pkg_name
968: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
969: ,x_msg_count
970: ,x_msg_data
971: ,'_PVT');
972: WHEN OTHERS THEN
969: ,x_msg_count
970: ,x_msg_data
971: ,'_PVT');
972: WHEN OTHERS THEN
973: x_return_status := okl_api.handle_exceptions(l_api_name
974: ,g_pkg_name
975: ,'OTHERS'
976: ,x_msg_count
977: ,x_msg_data
982: -- Procedure insert_row_tbl
983: --------------------------------------------------------------------------------
984:
985: PROCEDURE insert_row(p_api_version IN number
986: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
987: ,x_return_status OUT NOCOPY varchar2
988: ,x_msg_count OUT NOCOPY number
989: ,x_msg_data OUT NOCOPY varchar2
990: ,p_ecv_tbl IN okl_ecv_tbl
990: ,p_ecv_tbl IN okl_ecv_tbl
991: ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
992: l_api_version CONSTANT number := 1;
993: l_api_name CONSTANT varchar2(30) := 'insert_row_tbl';
994: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
995: i number := 0;
996: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
997:
998: BEGIN
992: l_api_version CONSTANT number := 1;
993: l_api_name CONSTANT varchar2(30) := 'insert_row_tbl';
994: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
995: i number := 0;
996: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
997:
998: BEGIN
999: okl_api.init_msg_list(p_init_msg_list);
1000:
995: i number := 0;
996: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
997:
998: BEGIN
999: okl_api.init_msg_list(p_init_msg_list);
1000:
1001: -- Make sure PL/SQL table has records in it before passing
1002:
1003: IF (p_ecv_tbl.COUNT > 0) THEN
1004: i := p_ecv_tbl.FIRST;
1005:
1006: LOOP
1007: insert_row(p_api_version => p_api_version
1008: ,p_init_msg_list => okl_api.g_false
1009: ,x_return_status => x_return_status
1010: ,x_msg_count => x_msg_count
1011: ,x_msg_data => x_msg_data
1012: ,p_ecv_rec => p_ecv_tbl(i)
1010: ,x_msg_count => x_msg_count
1011: ,x_msg_data => x_msg_data
1012: ,p_ecv_rec => p_ecv_tbl(i)
1013: ,x_ecv_rec => x_ecv_tbl(i));
1014: IF x_return_status <> okl_api.g_ret_sts_success THEN
1015: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1016: l_overall_status := x_return_status;
1017: END IF;
1018: END IF;
1011: ,x_msg_data => x_msg_data
1012: ,p_ecv_rec => p_ecv_tbl(i)
1013: ,x_ecv_rec => x_ecv_tbl(i));
1014: IF x_return_status <> okl_api.g_ret_sts_success THEN
1015: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1016: l_overall_status := x_return_status;
1017: END IF;
1018: END IF;
1019: EXIT WHEN(i = p_ecv_tbl.LAST);
1027:
1028: -- No action necessary. Validation can continue to next attribute/column
1029:
1030: NULL;
1031: WHEN okl_api.g_exception_error THEN
1032: x_return_status := okl_api.handle_exceptions(l_api_name
1033: ,g_pkg_name
1034: ,'OKL_API.G_RET_STS_ERROR'
1035: ,x_msg_count
1028: -- No action necessary. Validation can continue to next attribute/column
1029:
1030: NULL;
1031: WHEN okl_api.g_exception_error THEN
1032: x_return_status := okl_api.handle_exceptions(l_api_name
1033: ,g_pkg_name
1034: ,'OKL_API.G_RET_STS_ERROR'
1035: ,x_msg_count
1036: ,x_msg_data
1030: NULL;
1031: WHEN okl_api.g_exception_error THEN
1032: x_return_status := okl_api.handle_exceptions(l_api_name
1033: ,g_pkg_name
1034: ,'OKL_API.G_RET_STS_ERROR'
1035: ,x_msg_count
1036: ,x_msg_data
1037: ,'_PVT');
1038: WHEN okl_api.g_exception_unexpected_error THEN
1034: ,'OKL_API.G_RET_STS_ERROR'
1035: ,x_msg_count
1036: ,x_msg_data
1037: ,'_PVT');
1038: WHEN okl_api.g_exception_unexpected_error THEN
1039: x_return_status := okl_api.handle_exceptions(l_api_name
1040: ,g_pkg_name
1041: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1042: ,x_msg_count
1035: ,x_msg_count
1036: ,x_msg_data
1037: ,'_PVT');
1038: WHEN okl_api.g_exception_unexpected_error THEN
1039: x_return_status := okl_api.handle_exceptions(l_api_name
1040: ,g_pkg_name
1041: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1042: ,x_msg_count
1043: ,x_msg_data
1037: ,'_PVT');
1038: WHEN okl_api.g_exception_unexpected_error THEN
1039: x_return_status := okl_api.handle_exceptions(l_api_name
1040: ,g_pkg_name
1041: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1042: ,x_msg_count
1043: ,x_msg_data
1044: ,'_PVT');
1045: WHEN OTHERS THEN
1042: ,x_msg_count
1043: ,x_msg_data
1044: ,'_PVT');
1045: WHEN OTHERS THEN
1046: x_return_status := okl_api.handle_exceptions(l_api_name
1047: ,g_pkg_name
1048: ,'OTHERS'
1049: ,x_msg_count
1050: ,x_msg_data
1055: -- Procedure update_row
1056: --------------------------------------------------------------------------------
1057:
1058: PROCEDURE update_row(p_api_version IN number
1059: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1060: ,x_return_status OUT NOCOPY varchar2
1061: ,x_msg_count OUT NOCOPY number
1062: ,x_msg_data OUT NOCOPY varchar2
1063: ,p_ecv_rec IN okl_ecv_rec
1063: ,p_ecv_rec IN okl_ecv_rec
1064: ,x_ecv_rec OUT NOCOPY okl_ecv_rec) IS
1065: l_api_version CONSTANT number := 1;
1066: l_api_name CONSTANT varchar2(30) := 'update_row';
1067: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1068: l_ecv_rec okl_ecv_rec := p_ecv_rec;
1069: l_def_ecv_rec okl_ecv_rec;
1070: l_row_notfound boolean := true;
1071:
1082: FUNCTION populate_new_record(p_ecv_rec IN okl_ecv_rec
1083: ,x_ecv_rec OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
1084: l_ecv_rec okl_ecv_rec;
1085: l_row_notfound boolean := true;
1086: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1087:
1088: BEGIN
1089: x_ecv_rec := p_ecv_rec;
1090:
1092:
1093: l_ecv_rec := get_rec(p_ecv_rec, l_row_notfound);
1094:
1095: IF (l_row_notfound) THEN
1096: okl_api.set_message(g_fnd_app, g_form_record_deleted);
1097: l_return_status := okl_api.g_ret_sts_unexp_error;
1098: END IF;
1099:
1100: IF (x_ecv_rec.criterion_value_id IS NULL) THEN
1093: l_ecv_rec := get_rec(p_ecv_rec, l_row_notfound);
1094:
1095: IF (l_row_notfound) THEN
1096: okl_api.set_message(g_fnd_app, g_form_record_deleted);
1097: l_return_status := okl_api.g_ret_sts_unexp_error;
1098: END IF;
1099:
1100: IF (x_ecv_rec.criterion_value_id IS NULL) THEN
1101: x_ecv_rec.criterion_value_id := l_ecv_rec.criterion_value_id;
1196: END populate_new_record;
1197:
1198: FUNCTION set_attributes(p_ecv_rec IN okl_ecv_rec
1199: ,x_ecv_rec OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
1200: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1201:
1202: BEGIN
1203: x_ecv_rec := p_ecv_rec;
1204:
1207: RETURN(l_return_status);
1208: END set_attributes;
1209:
1210: BEGIN
1211: l_return_status := okl_api.start_activity(l_api_name
1212: ,g_pkg_name
1213: ,p_init_msg_list
1214: ,l_api_version
1215: ,p_api_version
1215: ,p_api_version
1216: ,'_PVT'
1217: ,x_return_status);
1218:
1219: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1220: RAISE okl_api.g_exception_unexpected_error;
1221: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1222: RAISE okl_api.g_exception_error;
1223: END IF;
1216: ,'_PVT'
1217: ,x_return_status);
1218:
1219: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1220: RAISE okl_api.g_exception_unexpected_error;
1221: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1222: RAISE okl_api.g_exception_error;
1223: END IF;
1224:
1217: ,x_return_status);
1218:
1219: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1220: RAISE okl_api.g_exception_unexpected_error;
1221: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1222: RAISE okl_api.g_exception_error;
1223: END IF;
1224:
1225: --Setting Item Attributes
1218:
1219: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1220: RAISE okl_api.g_exception_unexpected_error;
1221: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1222: RAISE okl_api.g_exception_error;
1223: END IF;
1224:
1225: --Setting Item Attributes
1226:
1225: --Setting Item Attributes
1226:
1227: l_return_status := set_attributes(p_ecv_rec, l_ecv_rec);
1228:
1229: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1230: RAISE okl_api.g_exception_unexpected_error;
1231: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1232: RAISE okl_api.g_exception_error;
1233: END IF;
1226:
1227: l_return_status := set_attributes(p_ecv_rec, l_ecv_rec);
1228:
1229: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1230: RAISE okl_api.g_exception_unexpected_error;
1231: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1232: RAISE okl_api.g_exception_error;
1233: END IF;
1234:
1227: l_return_status := set_attributes(p_ecv_rec, l_ecv_rec);
1228:
1229: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1230: RAISE okl_api.g_exception_unexpected_error;
1231: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1232: RAISE okl_api.g_exception_error;
1233: END IF;
1234:
1235: --populate new record
1228:
1229: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1230: RAISE okl_api.g_exception_unexpected_error;
1231: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1232: RAISE okl_api.g_exception_error;
1233: END IF;
1234:
1235: --populate new record
1236:
1235: --populate new record
1236:
1237: l_return_status := populate_new_record(l_ecv_rec, l_def_ecv_rec);
1238:
1239: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1240: RAISE okl_api.g_exception_unexpected_error;
1241: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1242: RAISE okl_api.g_exception_error;
1243: END IF;
1236:
1237: l_return_status := populate_new_record(l_ecv_rec, l_def_ecv_rec);
1238:
1239: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1240: RAISE okl_api.g_exception_unexpected_error;
1241: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1242: RAISE okl_api.g_exception_error;
1243: END IF;
1244:
1237: l_return_status := populate_new_record(l_ecv_rec, l_def_ecv_rec);
1238:
1239: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1240: RAISE okl_api.g_exception_unexpected_error;
1241: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1242: RAISE okl_api.g_exception_error;
1243: END IF;
1244:
1245: --null out g_miss_values
1238:
1239: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1240: RAISE okl_api.g_exception_unexpected_error;
1241: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1242: RAISE okl_api.g_exception_error;
1243: END IF;
1244:
1245: --null out g_miss_values
1246:
1255: --validate attributes
1256:
1257: l_return_status := validate_attributes(l_def_ecv_rec);
1258:
1259: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1260: RAISE okl_api.g_exception_unexpected_error;
1261: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1262: RAISE okl_api.g_exception_error;
1263: END IF;
1256:
1257: l_return_status := validate_attributes(l_def_ecv_rec);
1258:
1259: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1260: RAISE okl_api.g_exception_unexpected_error;
1261: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1262: RAISE okl_api.g_exception_error;
1263: END IF;
1264:
1257: l_return_status := validate_attributes(l_def_ecv_rec);
1258:
1259: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1260: RAISE okl_api.g_exception_unexpected_error;
1261: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1262: RAISE okl_api.g_exception_error;
1263: END IF;
1264:
1265: --
1258:
1259: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1260: RAISE okl_api.g_exception_unexpected_error;
1261: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1262: RAISE okl_api.g_exception_error;
1263: END IF;
1264:
1265: --
1266: --validate record
1269: l_return_status := validate_record(l_def_ecv_rec);
1270:
1271: --
1272:
1273: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1274: RAISE okl_api.g_exception_unexpected_error;
1275: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1276: RAISE okl_api.g_exception_error;
1277: END IF;
1270:
1271: --
1272:
1273: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1274: RAISE okl_api.g_exception_unexpected_error;
1275: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1276: RAISE okl_api.g_exception_error;
1277: END IF;
1278:
1271: --
1272:
1273: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1274: RAISE okl_api.g_exception_unexpected_error;
1275: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1276: RAISE okl_api.g_exception_error;
1277: END IF;
1278:
1279: --lock the row
1272:
1273: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1274: RAISE okl_api.g_exception_unexpected_error;
1275: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1276: RAISE okl_api.g_exception_error;
1277: END IF;
1278:
1279: --lock the row
1280:
1277: END IF;
1278:
1279: --lock the row
1280:
1281: lock_row(p_init_msg_list => okl_api.g_false
1282: ,x_return_status => l_return_status
1283: ,x_msg_count => x_msg_count
1284: ,x_msg_data => x_msg_data
1285: ,p_ecv_rec => l_def_ecv_rec);
1283: ,x_msg_count => x_msg_count
1284: ,x_msg_data => x_msg_data
1285: ,p_ecv_rec => l_def_ecv_rec);
1286:
1287: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1288: RAISE okl_api.g_exception_unexpected_error;
1289: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1290: RAISE okl_api.g_exception_error;
1291: END IF;
1284: ,x_msg_data => x_msg_data
1285: ,p_ecv_rec => l_def_ecv_rec);
1286:
1287: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1288: RAISE okl_api.g_exception_unexpected_error;
1289: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1290: RAISE okl_api.g_exception_error;
1291: END IF;
1292:
1285: ,p_ecv_rec => l_def_ecv_rec);
1286:
1287: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1288: RAISE okl_api.g_exception_unexpected_error;
1289: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1290: RAISE okl_api.g_exception_error;
1291: END IF;
1292:
1293: --
1286:
1287: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1288: RAISE okl_api.g_exception_unexpected_error;
1289: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1290: RAISE okl_api.g_exception_error;
1291: END IF;
1292:
1293: --
1294: --update the record
1327: --Set OUT Values
1328:
1329: x_ecv_rec := l_def_ecv_rec;
1330: x_return_status := l_return_status;
1331: okl_api.end_activity(x_msg_count, x_msg_data);
1332: EXCEPTION
1333: WHEN g_exception_halt_validation THEN
1334:
1335: -- No action necessary. Validation can continue to next attribute/column
1334:
1335: -- No action necessary. Validation can continue to next attribute/column
1336:
1337: NULL;
1338: WHEN okl_api.g_exception_error THEN
1339: x_return_status := okl_api.handle_exceptions(l_api_name
1340: ,g_pkg_name
1341: ,'OKL_API.G_RET_STS_ERROR'
1342: ,x_msg_count
1335: -- No action necessary. Validation can continue to next attribute/column
1336:
1337: NULL;
1338: WHEN okl_api.g_exception_error THEN
1339: x_return_status := okl_api.handle_exceptions(l_api_name
1340: ,g_pkg_name
1341: ,'OKL_API.G_RET_STS_ERROR'
1342: ,x_msg_count
1343: ,x_msg_data
1337: NULL;
1338: WHEN okl_api.g_exception_error THEN
1339: x_return_status := okl_api.handle_exceptions(l_api_name
1340: ,g_pkg_name
1341: ,'OKL_API.G_RET_STS_ERROR'
1342: ,x_msg_count
1343: ,x_msg_data
1344: ,'_PVT');
1345: WHEN okl_api.g_exception_unexpected_error THEN
1341: ,'OKL_API.G_RET_STS_ERROR'
1342: ,x_msg_count
1343: ,x_msg_data
1344: ,'_PVT');
1345: WHEN okl_api.g_exception_unexpected_error THEN
1346: x_return_status := okl_api.handle_exceptions(l_api_name
1347: ,g_pkg_name
1348: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1349: ,x_msg_count
1342: ,x_msg_count
1343: ,x_msg_data
1344: ,'_PVT');
1345: WHEN okl_api.g_exception_unexpected_error THEN
1346: x_return_status := okl_api.handle_exceptions(l_api_name
1347: ,g_pkg_name
1348: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1349: ,x_msg_count
1350: ,x_msg_data
1344: ,'_PVT');
1345: WHEN okl_api.g_exception_unexpected_error THEN
1346: x_return_status := okl_api.handle_exceptions(l_api_name
1347: ,g_pkg_name
1348: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1349: ,x_msg_count
1350: ,x_msg_data
1351: ,'_PVT');
1352: WHEN OTHERS THEN
1349: ,x_msg_count
1350: ,x_msg_data
1351: ,'_PVT');
1352: WHEN OTHERS THEN
1353: x_return_status := okl_api.handle_exceptions(l_api_name
1354: ,g_pkg_name
1355: ,'OTHERS'
1356: ,x_msg_count
1357: ,x_msg_data
1362: -- Procedure update_row_tbl
1363: --------------------------------------------------------------------------------
1364:
1365: PROCEDURE update_row(p_api_version IN number
1366: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1367: ,x_return_status OUT NOCOPY varchar2
1368: ,x_msg_count OUT NOCOPY number
1369: ,x_msg_data OUT NOCOPY varchar2
1370: ,p_ecv_tbl IN okl_ecv_tbl
1370: ,p_ecv_tbl IN okl_ecv_tbl
1371: ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
1372: l_api_version CONSTANT number := 1;
1373: l_api_name CONSTANT varchar2(30) := 'update_row_tbl';
1374: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1375: i number := 0;
1376: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1377:
1378: BEGIN
1372: l_api_version CONSTANT number := 1;
1373: l_api_name CONSTANT varchar2(30) := 'update_row_tbl';
1374: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1375: i number := 0;
1376: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1377:
1378: BEGIN
1379: okl_api.init_msg_list(p_init_msg_list);
1380:
1375: i number := 0;
1376: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1377:
1378: BEGIN
1379: okl_api.init_msg_list(p_init_msg_list);
1380:
1381: -- Make sure PL/SQL table has records in it before passing
1382:
1383: IF (p_ecv_tbl.COUNT > 0) THEN
1384: i := p_ecv_tbl.FIRST;
1385:
1386: LOOP
1387: update_row(p_api_version => p_api_version
1388: ,p_init_msg_list => okl_api.g_false
1389: ,x_return_status => x_return_status
1390: ,x_msg_count => x_msg_count
1391: ,x_msg_data => x_msg_data
1392: ,p_ecv_rec => p_ecv_tbl(i)
1390: ,x_msg_count => x_msg_count
1391: ,x_msg_data => x_msg_data
1392: ,p_ecv_rec => p_ecv_tbl(i)
1393: ,x_ecv_rec => x_ecv_tbl(i));
1394: IF x_return_status <> okl_api.g_ret_sts_success THEN
1395: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1396: l_overall_status := x_return_status;
1397: END IF;
1398: END IF;
1391: ,x_msg_data => x_msg_data
1392: ,p_ecv_rec => p_ecv_tbl(i)
1393: ,x_ecv_rec => x_ecv_tbl(i));
1394: IF x_return_status <> okl_api.g_ret_sts_success THEN
1395: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1396: l_overall_status := x_return_status;
1397: END IF;
1398: END IF;
1399: EXIT WHEN(i = p_ecv_tbl.LAST);
1407:
1408: -- No action necessary. Validation can continue to next attribute/column
1409:
1410: NULL;
1411: WHEN okl_api.g_exception_error THEN
1412: x_return_status := okl_api.handle_exceptions(l_api_name
1413: ,g_pkg_name
1414: ,'OKL_API.G_RET_STS_ERROR'
1415: ,x_msg_count
1408: -- No action necessary. Validation can continue to next attribute/column
1409:
1410: NULL;
1411: WHEN okl_api.g_exception_error THEN
1412: x_return_status := okl_api.handle_exceptions(l_api_name
1413: ,g_pkg_name
1414: ,'OKL_API.G_RET_STS_ERROR'
1415: ,x_msg_count
1416: ,x_msg_data
1410: NULL;
1411: WHEN okl_api.g_exception_error THEN
1412: x_return_status := okl_api.handle_exceptions(l_api_name
1413: ,g_pkg_name
1414: ,'OKL_API.G_RET_STS_ERROR'
1415: ,x_msg_count
1416: ,x_msg_data
1417: ,'_PVT');
1418: WHEN okl_api.g_exception_unexpected_error THEN
1414: ,'OKL_API.G_RET_STS_ERROR'
1415: ,x_msg_count
1416: ,x_msg_data
1417: ,'_PVT');
1418: WHEN okl_api.g_exception_unexpected_error THEN
1419: x_return_status := okl_api.handle_exceptions(l_api_name
1420: ,g_pkg_name
1421: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1422: ,x_msg_count
1415: ,x_msg_count
1416: ,x_msg_data
1417: ,'_PVT');
1418: WHEN okl_api.g_exception_unexpected_error THEN
1419: x_return_status := okl_api.handle_exceptions(l_api_name
1420: ,g_pkg_name
1421: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1422: ,x_msg_count
1423: ,x_msg_data
1417: ,'_PVT');
1418: WHEN okl_api.g_exception_unexpected_error THEN
1419: x_return_status := okl_api.handle_exceptions(l_api_name
1420: ,g_pkg_name
1421: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1422: ,x_msg_count
1423: ,x_msg_data
1424: ,'_PVT');
1425: WHEN OTHERS THEN
1422: ,x_msg_count
1423: ,x_msg_data
1424: ,'_PVT');
1425: WHEN OTHERS THEN
1426: x_return_status := okl_api.handle_exceptions(l_api_name
1427: ,g_pkg_name
1428: ,'OTHERS'
1429: ,x_msg_count
1430: ,x_msg_data
1435: -- Procedure delete_row
1436: --------------------------------------------------------------------------------
1437:
1438: PROCEDURE delete_row(p_api_version IN number
1439: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1440: ,x_return_status OUT NOCOPY varchar2
1441: ,x_msg_count OUT NOCOPY number
1442: ,x_msg_data OUT NOCOPY varchar2
1443: ,p_ecv_rec IN okl_ecv_rec) IS
1442: ,x_msg_data OUT NOCOPY varchar2
1443: ,p_ecv_rec IN okl_ecv_rec) IS
1444: l_api_version CONSTANT number := 1;
1445: l_api_name CONSTANT varchar2(30) := 'delete_row';
1446: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1447: l_ecv_rec okl_ecv_rec := p_ecv_rec;
1448: l_row_notfound boolean := true;
1449:
1450: BEGIN
1447: l_ecv_rec okl_ecv_rec := p_ecv_rec;
1448: l_row_notfound boolean := true;
1449:
1450: BEGIN
1451: l_return_status := okl_api.start_activity(l_api_name
1452: ,g_pkg_name
1453: ,p_init_msg_list
1454: ,l_api_version
1455: ,p_api_version
1455: ,p_api_version
1456: ,'_PVT'
1457: ,x_return_status);
1458:
1459: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1460: RAISE okl_api.g_exception_unexpected_error;
1461: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1462: RAISE okl_api.g_exception_error;
1463: END IF;
1456: ,'_PVT'
1457: ,x_return_status);
1458:
1459: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1460: RAISE okl_api.g_exception_unexpected_error;
1461: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1462: RAISE okl_api.g_exception_error;
1463: END IF;
1464:
1457: ,x_return_status);
1458:
1459: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1460: RAISE okl_api.g_exception_unexpected_error;
1461: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1462: RAISE okl_api.g_exception_error;
1463: END IF;
1464:
1465: DELETE FROM okl_fe_criterion_values
1458:
1459: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1460: RAISE okl_api.g_exception_unexpected_error;
1461: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1462: RAISE okl_api.g_exception_error;
1463: END IF;
1464:
1465: DELETE FROM okl_fe_criterion_values
1466: WHERE criterion_value_id = l_ecv_rec.criterion_value_id;
1464:
1465: DELETE FROM okl_fe_criterion_values
1466: WHERE criterion_value_id = l_ecv_rec.criterion_value_id;
1467: x_return_status := l_return_status;
1468: okl_api.end_activity(x_msg_count, x_msg_data);
1469: EXCEPTION
1470: WHEN g_exception_halt_validation THEN
1471:
1472: -- No action necessary. Validation can continue to next attribute/column
1471:
1472: -- No action necessary. Validation can continue to next attribute/column
1473:
1474: NULL;
1475: WHEN okl_api.g_exception_error THEN
1476: x_return_status := okl_api.handle_exceptions(l_api_name
1477: ,g_pkg_name
1478: ,'OKL_API.G_RET_STS_ERROR'
1479: ,x_msg_count
1472: -- No action necessary. Validation can continue to next attribute/column
1473:
1474: NULL;
1475: WHEN okl_api.g_exception_error THEN
1476: x_return_status := okl_api.handle_exceptions(l_api_name
1477: ,g_pkg_name
1478: ,'OKL_API.G_RET_STS_ERROR'
1479: ,x_msg_count
1480: ,x_msg_data
1474: NULL;
1475: WHEN okl_api.g_exception_error THEN
1476: x_return_status := okl_api.handle_exceptions(l_api_name
1477: ,g_pkg_name
1478: ,'OKL_API.G_RET_STS_ERROR'
1479: ,x_msg_count
1480: ,x_msg_data
1481: ,'_PVT');
1482: WHEN okl_api.g_exception_unexpected_error THEN
1478: ,'OKL_API.G_RET_STS_ERROR'
1479: ,x_msg_count
1480: ,x_msg_data
1481: ,'_PVT');
1482: WHEN okl_api.g_exception_unexpected_error THEN
1483: x_return_status := okl_api.handle_exceptions(l_api_name
1484: ,g_pkg_name
1485: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1486: ,x_msg_count
1479: ,x_msg_count
1480: ,x_msg_data
1481: ,'_PVT');
1482: WHEN okl_api.g_exception_unexpected_error THEN
1483: x_return_status := okl_api.handle_exceptions(l_api_name
1484: ,g_pkg_name
1485: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1486: ,x_msg_count
1487: ,x_msg_data
1481: ,'_PVT');
1482: WHEN okl_api.g_exception_unexpected_error THEN
1483: x_return_status := okl_api.handle_exceptions(l_api_name
1484: ,g_pkg_name
1485: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1486: ,x_msg_count
1487: ,x_msg_data
1488: ,'_PVT');
1489: WHEN OTHERS THEN
1486: ,x_msg_count
1487: ,x_msg_data
1488: ,'_PVT');
1489: WHEN OTHERS THEN
1490: x_return_status := okl_api.handle_exceptions(l_api_name
1491: ,g_pkg_name
1492: ,'OTHERS'
1493: ,x_msg_count
1494: ,x_msg_data
1499: -- Procedure delete_row_tbl
1500: --------------------------------------------------------------------------------
1501:
1502: PROCEDURE delete_row(p_api_version IN number
1503: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1504: ,x_return_status OUT NOCOPY varchar2
1505: ,x_msg_count OUT NOCOPY number
1506: ,x_msg_data OUT NOCOPY varchar2
1507: ,p_ecv_tbl IN okl_ecv_tbl) IS
1506: ,x_msg_data OUT NOCOPY varchar2
1507: ,p_ecv_tbl IN okl_ecv_tbl) IS
1508: l_api_version CONSTANT number := 1;
1509: l_api_name CONSTANT varchar2(30) := 'v_delete_row';
1510: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1511: i number := 0;
1512: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1513:
1514: BEGIN
1508: l_api_version CONSTANT number := 1;
1509: l_api_name CONSTANT varchar2(30) := 'v_delete_row';
1510: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1511: i number := 0;
1512: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1513:
1514: BEGIN
1515: okl_api.init_msg_list(p_init_msg_list);
1516:
1511: i number := 0;
1512: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1513:
1514: BEGIN
1515: okl_api.init_msg_list(p_init_msg_list);
1516:
1517: -- Make sure PL/SQL table has records in it before passing
1518:
1519: IF (p_ecv_tbl.COUNT > 0) THEN
1520: i := p_ecv_tbl.FIRST;
1521:
1522: LOOP
1523: delete_row(p_api_version => p_api_version
1524: ,p_init_msg_list => okl_api.g_false
1525: ,x_return_status => x_return_status
1526: ,x_msg_count => x_msg_count
1527: ,x_msg_data => x_msg_data
1528: ,p_ecv_rec => p_ecv_tbl(i));
1525: ,x_return_status => x_return_status
1526: ,x_msg_count => x_msg_count
1527: ,x_msg_data => x_msg_data
1528: ,p_ecv_rec => p_ecv_tbl(i));
1529: IF x_return_status <> okl_api.g_ret_sts_success THEN
1530: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1531: l_overall_status := x_return_status;
1532: END IF;
1533: END IF;
1526: ,x_msg_count => x_msg_count
1527: ,x_msg_data => x_msg_data
1528: ,p_ecv_rec => p_ecv_tbl(i));
1529: IF x_return_status <> okl_api.g_ret_sts_success THEN
1530: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1531: l_overall_status := x_return_status;
1532: END IF;
1533: END IF;
1534: EXIT WHEN(i = p_ecv_tbl.LAST);
1542:
1543: -- No action necessary. Validation can continue to next attribute/column
1544:
1545: NULL;
1546: WHEN okl_api.g_exception_error THEN
1547: x_return_status := okl_api.handle_exceptions(l_api_name
1548: ,g_pkg_name
1549: ,'OKL_API.G_RET_STS_ERROR'
1550: ,x_msg_count
1543: -- No action necessary. Validation can continue to next attribute/column
1544:
1545: NULL;
1546: WHEN okl_api.g_exception_error THEN
1547: x_return_status := okl_api.handle_exceptions(l_api_name
1548: ,g_pkg_name
1549: ,'OKL_API.G_RET_STS_ERROR'
1550: ,x_msg_count
1551: ,x_msg_data
1545: NULL;
1546: WHEN okl_api.g_exception_error THEN
1547: x_return_status := okl_api.handle_exceptions(l_api_name
1548: ,g_pkg_name
1549: ,'OKL_API.G_RET_STS_ERROR'
1550: ,x_msg_count
1551: ,x_msg_data
1552: ,'_PVT');
1553: WHEN okl_api.g_exception_unexpected_error THEN
1549: ,'OKL_API.G_RET_STS_ERROR'
1550: ,x_msg_count
1551: ,x_msg_data
1552: ,'_PVT');
1553: WHEN okl_api.g_exception_unexpected_error THEN
1554: x_return_status := okl_api.handle_exceptions(l_api_name
1555: ,g_pkg_name
1556: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1557: ,x_msg_count
1550: ,x_msg_count
1551: ,x_msg_data
1552: ,'_PVT');
1553: WHEN okl_api.g_exception_unexpected_error THEN
1554: x_return_status := okl_api.handle_exceptions(l_api_name
1555: ,g_pkg_name
1556: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1557: ,x_msg_count
1558: ,x_msg_data
1552: ,'_PVT');
1553: WHEN okl_api.g_exception_unexpected_error THEN
1554: x_return_status := okl_api.handle_exceptions(l_api_name
1555: ,g_pkg_name
1556: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1557: ,x_msg_count
1558: ,x_msg_data
1559: ,'_PVT');
1560: WHEN OTHERS THEN
1557: ,x_msg_count
1558: ,x_msg_data
1559: ,'_PVT');
1560: WHEN OTHERS THEN
1561: x_return_status := okl_api.handle_exceptions(l_api_name
1562: ,g_pkg_name
1563: ,'OTHERS'
1564: ,x_msg_count
1565: ,x_msg_data