[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_criteria_set
57: WHERE criteria_set_id = p_ech_rec.criteria_set_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_criteria_set.object_version_number%TYPE;
62: lc_object_version_number okl_fe_criteria_set.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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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_ech_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: x_return_status := l_return_status;
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: x_return_status := l_return_status;
115:
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: x_return_status := l_return_status;
115:
116: --
117:
116: --
117:
118: EXCEPTION
119: WHEN g_exception_halt_validation THEN
120: x_return_status := okl_api.handle_exceptions(l_api_name
121: ,g_pkg_name
122: ,'OKL_API.G_RET_STS_ERROR'
123: ,x_msg_count
124: ,x_msg_data
118: EXCEPTION
119: WHEN g_exception_halt_validation THEN
120: x_return_status := okl_api.handle_exceptions(l_api_name
121: ,g_pkg_name
122: ,'OKL_API.G_RET_STS_ERROR'
123: ,x_msg_count
124: ,x_msg_data
125: ,'_PVT');
126: WHEN okl_api.g_exception_error THEN
122: ,'OKL_API.G_RET_STS_ERROR'
123: ,x_msg_count
124: ,x_msg_data
125: ,'_PVT');
126: WHEN okl_api.g_exception_error THEN
127: x_return_status := okl_api.handle_exceptions(l_api_name
128: ,g_pkg_name
129: ,'OKL_API.G_RET_STS_ERROR'
130: ,x_msg_count
123: ,x_msg_count
124: ,x_msg_data
125: ,'_PVT');
126: WHEN okl_api.g_exception_error THEN
127: x_return_status := okl_api.handle_exceptions(l_api_name
128: ,g_pkg_name
129: ,'OKL_API.G_RET_STS_ERROR'
130: ,x_msg_count
131: ,x_msg_data
125: ,'_PVT');
126: WHEN okl_api.g_exception_error THEN
127: x_return_status := okl_api.handle_exceptions(l_api_name
128: ,g_pkg_name
129: ,'OKL_API.G_RET_STS_ERROR'
130: ,x_msg_count
131: ,x_msg_data
132: ,'_PVT');
133: WHEN okl_api.g_exception_unexpected_error THEN
129: ,'OKL_API.G_RET_STS_ERROR'
130: ,x_msg_count
131: ,x_msg_data
132: ,'_PVT');
133: WHEN okl_api.g_exception_unexpected_error THEN
134: x_return_status := okl_api.handle_exceptions(l_api_name
135: ,g_pkg_name
136: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
137: ,x_msg_count
130: ,x_msg_count
131: ,x_msg_data
132: ,'_PVT');
133: WHEN okl_api.g_exception_unexpected_error THEN
134: x_return_status := okl_api.handle_exceptions(l_api_name
135: ,g_pkg_name
136: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
137: ,x_msg_count
138: ,x_msg_data
132: ,'_PVT');
133: WHEN okl_api.g_exception_unexpected_error THEN
134: x_return_status := okl_api.handle_exceptions(l_api_name
135: ,g_pkg_name
136: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
137: ,x_msg_count
138: ,x_msg_data
139: ,'_PVT');
140: WHEN OTHERS THEN
137: ,x_msg_count
138: ,x_msg_data
139: ,'_PVT');
140: WHEN OTHERS THEN
141: x_return_status := okl_api.handle_exceptions(l_api_name
142: ,g_pkg_name
143: ,'OTHERS'
144: ,x_msg_count
145: ,x_msg_data
157: ,x_msg_data OUT NOCOPY varchar2
158: ,p_ech_tbl IN okl_ech_tbl) IS
159: l_api_version CONSTANT number := 1;
160: l_api_name CONSTANT varchar2(30) := 'tbl_lock_row';
161: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
162:
163: -- Begin Post-Generation Change
164: -- overall error status
165:
162:
163: -- Begin Post-Generation Change
164: -- overall error status
165:
166: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
167:
168: -- End Post-Generation Change
169:
170: i number := 0;
169:
170: i number := 0;
171:
172: BEGIN
173: okl_api.init_msg_list(p_init_msg_list);
174:
175: -- Make sure PL/SQL table has records in it before passing
176:
177: IF (p_ech_tbl.COUNT > 0) THEN
177: IF (p_ech_tbl.COUNT > 0) THEN
178: i := p_ech_tbl.FIRST;
179:
180: LOOP
181: lock_row(p_init_msg_list => okl_api.g_false
182: ,x_return_status => x_return_status
183: ,x_msg_count => x_msg_count
184: ,x_msg_data => x_msg_data
185: ,p_ech_rec => p_ech_tbl(i));
186:
187: -- Begin Post-Generation Change
188: -- store the highest degree of error
189:
190: IF x_return_status <> okl_api.g_ret_sts_success THEN
191: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
192: l_overall_status := x_return_status;
193: END IF;
194: END IF;
187: -- Begin Post-Generation Change
188: -- store the highest degree of error
189:
190: IF x_return_status <> okl_api.g_ret_sts_success THEN
191: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
192: l_overall_status := x_return_status;
193: END IF;
194: END IF;
195:
209: END IF;
210:
211: EXCEPTION
212: WHEN g_exception_halt_validation THEN
213: x_return_status := okl_api.handle_exceptions(l_api_name
214: ,g_pkg_name
215: ,'OKL_API.G_RET_STS_ERROR'
216: ,x_msg_count
217: ,x_msg_data
211: EXCEPTION
212: WHEN g_exception_halt_validation THEN
213: x_return_status := okl_api.handle_exceptions(l_api_name
214: ,g_pkg_name
215: ,'OKL_API.G_RET_STS_ERROR'
216: ,x_msg_count
217: ,x_msg_data
218: ,'_PVT');
219: WHEN okl_api.g_exception_error THEN
215: ,'OKL_API.G_RET_STS_ERROR'
216: ,x_msg_count
217: ,x_msg_data
218: ,'_PVT');
219: WHEN okl_api.g_exception_error THEN
220: x_return_status := okl_api.handle_exceptions(l_api_name
221: ,g_pkg_name
222: ,'OKL_API.G_RET_STS_ERROR'
223: ,x_msg_count
216: ,x_msg_count
217: ,x_msg_data
218: ,'_PVT');
219: WHEN okl_api.g_exception_error THEN
220: x_return_status := okl_api.handle_exceptions(l_api_name
221: ,g_pkg_name
222: ,'OKL_API.G_RET_STS_ERROR'
223: ,x_msg_count
224: ,x_msg_data
218: ,'_PVT');
219: WHEN okl_api.g_exception_error THEN
220: x_return_status := okl_api.handle_exceptions(l_api_name
221: ,g_pkg_name
222: ,'OKL_API.G_RET_STS_ERROR'
223: ,x_msg_count
224: ,x_msg_data
225: ,'_PVT');
226: WHEN okl_api.g_exception_unexpected_error THEN
222: ,'OKL_API.G_RET_STS_ERROR'
223: ,x_msg_count
224: ,x_msg_data
225: ,'_PVT');
226: WHEN okl_api.g_exception_unexpected_error THEN
227: x_return_status := okl_api.handle_exceptions(l_api_name
228: ,g_pkg_name
229: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
230: ,x_msg_count
223: ,x_msg_count
224: ,x_msg_data
225: ,'_PVT');
226: WHEN okl_api.g_exception_unexpected_error THEN
227: x_return_status := okl_api.handle_exceptions(l_api_name
228: ,g_pkg_name
229: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
230: ,x_msg_count
231: ,x_msg_data
225: ,'_PVT');
226: WHEN okl_api.g_exception_unexpected_error THEN
227: x_return_status := okl_api.handle_exceptions(l_api_name
228: ,g_pkg_name
229: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
230: ,x_msg_count
231: ,x_msg_data
232: ,'_PVT');
233: WHEN OTHERS THEN
230: ,x_msg_count
231: ,x_msg_data
232: ,'_PVT');
233: WHEN OTHERS THEN
234: x_return_status := okl_api.handle_exceptions(l_api_name
235: ,g_pkg_name
236: ,'OTHERS'
237: ,x_msg_count
238: ,x_msg_data
250:
251: --
252: -- data is required
253:
254: IF (p_id IS NULL) OR (p_id = okl_api.g_miss_num) THEN
255: okl_api.set_message(p_app_name => g_app_name
256: ,p_msg_name => g_required_value
257: ,p_token1 => g_col_name_token
258: ,p_token1_value => 'id');
251: --
252: -- data is required
253:
254: IF (p_id IS NULL) OR (p_id = okl_api.g_miss_num) THEN
255: okl_api.set_message(p_app_name => g_app_name
256: ,p_msg_name => g_required_value
257: ,p_token1 => g_col_name_token
258: ,p_token1_value => 'id');
259: RAISE okl_api.g_exception_error;
255: okl_api.set_message(p_app_name => g_app_name
256: ,p_msg_name => g_required_value
257: ,p_token1 => g_col_name_token
258: ,p_token1_value => 'id');
259: RAISE okl_api.g_exception_error;
260: END IF;
261: RETURN g_ret_sts_success;
262: EXCEPTION
263: WHEN okl_api.g_exception_error THEN
259: RAISE okl_api.g_exception_error;
260: END IF;
261: RETURN g_ret_sts_success;
262: EXCEPTION
263: WHEN okl_api.g_exception_error THEN
264: RETURN g_ret_sts_error;
265: WHEN okl_api.g_exception_unexpected_error THEN
266: RETURN g_ret_sts_unexp_error;
267: WHEN OTHERS THEN
261: RETURN g_ret_sts_success;
262: EXCEPTION
263: WHEN okl_api.g_exception_error THEN
264: RETURN g_ret_sts_error;
265: WHEN okl_api.g_exception_unexpected_error THEN
266: RETURN g_ret_sts_unexp_error;
267: WHEN OTHERS THEN
268: okl_api.set_message(p_app_name => g_app_name
269: ,p_msg_name => g_db_error
264: RETURN g_ret_sts_error;
265: WHEN okl_api.g_exception_unexpected_error THEN
266: RETURN g_ret_sts_unexp_error;
267: WHEN OTHERS THEN
268: okl_api.set_message(p_app_name => g_app_name
269: ,p_msg_name => g_db_error
270: ,p_token1 => g_prog_name_token
271: ,p_token1_value => l_api_name
272: ,p_token2 => 'SQLCODE'
287:
288: --
289: -- data is required
290:
291: IF (p_object_version_number IS NULL) OR (p_object_version_number = okl_api.g_miss_num) THEN
292: okl_api.set_message(p_app_name => g_app_name
293: ,p_msg_name => g_required_value
294: ,p_token1 => g_col_name_token
295: ,p_token1_value => 'object_version_number');
288: --
289: -- data is required
290:
291: IF (p_object_version_number IS NULL) OR (p_object_version_number = okl_api.g_miss_num) THEN
292: okl_api.set_message(p_app_name => g_app_name
293: ,p_msg_name => g_required_value
294: ,p_token1 => g_col_name_token
295: ,p_token1_value => 'object_version_number');
296: RAISE okl_api.g_exception_error;
292: okl_api.set_message(p_app_name => g_app_name
293: ,p_msg_name => g_required_value
294: ,p_token1 => g_col_name_token
295: ,p_token1_value => 'object_version_number');
296: RAISE okl_api.g_exception_error;
297: END IF;
298: RETURN g_ret_sts_success;
299: EXCEPTION
300: WHEN okl_api.g_exception_error THEN
296: RAISE okl_api.g_exception_error;
297: END IF;
298: RETURN g_ret_sts_success;
299: EXCEPTION
300: WHEN okl_api.g_exception_error THEN
301: RETURN g_ret_sts_error;
302: WHEN okl_api.g_exception_unexpected_error THEN
303: RETURN g_ret_sts_unexp_error;
304: WHEN OTHERS THEN
298: RETURN g_ret_sts_success;
299: EXCEPTION
300: WHEN okl_api.g_exception_error THEN
301: RETURN g_ret_sts_error;
302: WHEN okl_api.g_exception_unexpected_error THEN
303: RETURN g_ret_sts_unexp_error;
304: WHEN OTHERS THEN
305: okl_api.set_message(p_app_name => g_app_name
306: ,p_msg_name => g_db_error
301: RETURN g_ret_sts_error;
302: WHEN okl_api.g_exception_unexpected_error THEN
303: RETURN g_ret_sts_unexp_error;
304: WHEN OTHERS THEN
305: okl_api.set_message(p_app_name => g_app_name
306: ,p_msg_name => g_db_error
307: ,p_token1 => g_prog_name_token
308: ,p_token1_value => l_api_name
309: ,p_token2 => 'SQLCODE'
324:
325: --
326: -- data is required
327:
328: IF (p_source_id IS NULL) OR (p_source_id = okl_api.g_miss_num) THEN
329: okl_api.set_message(p_app_name => g_app_name
330: ,p_msg_name => g_required_value
331: ,p_token1 => g_col_name_token
332: ,p_token1_value => 'SOURCE_ID');
325: --
326: -- data is required
327:
328: IF (p_source_id IS NULL) OR (p_source_id = okl_api.g_miss_num) THEN
329: okl_api.set_message(p_app_name => g_app_name
330: ,p_msg_name => g_required_value
331: ,p_token1 => g_col_name_token
332: ,p_token1_value => 'SOURCE_ID');
333: RAISE okl_api.g_exception_error;
329: okl_api.set_message(p_app_name => g_app_name
330: ,p_msg_name => g_required_value
331: ,p_token1 => g_col_name_token
332: ,p_token1_value => 'SOURCE_ID');
333: RAISE okl_api.g_exception_error;
334: END IF;
335: RETURN g_ret_sts_success;
336: EXCEPTION
337: WHEN okl_api.g_exception_error THEN
333: RAISE okl_api.g_exception_error;
334: END IF;
335: RETURN g_ret_sts_success;
336: EXCEPTION
337: WHEN okl_api.g_exception_error THEN
338: RETURN g_ret_sts_error;
339: WHEN okl_api.g_exception_unexpected_error THEN
340: RETURN g_ret_sts_unexp_error;
341: WHEN OTHERS THEN
335: RETURN g_ret_sts_success;
336: EXCEPTION
337: WHEN okl_api.g_exception_error THEN
338: RETURN g_ret_sts_error;
339: WHEN okl_api.g_exception_unexpected_error THEN
340: RETURN g_ret_sts_unexp_error;
341: WHEN OTHERS THEN
342: okl_api.set_message(p_app_name => g_app_name
343: ,p_msg_name => g_db_error
338: RETURN g_ret_sts_error;
339: WHEN okl_api.g_exception_unexpected_error THEN
340: RETURN g_ret_sts_unexp_error;
341: WHEN OTHERS THEN
342: okl_api.set_message(p_app_name => g_app_name
343: ,p_msg_name => g_db_error
344: ,p_token1 => g_prog_name_token
345: ,p_token1_value => l_api_name
346: ,p_token2 => 'SQLCODE'
354: -- Function Name : validate_SOURCE_OBJECT_CODE
355: ------------------------------------------------
356:
357: FUNCTION validate_source_object_code(p_source_object_code IN varchar2) RETURN varchar2 IS
358: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
359: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_SOURCE_OBJECT_CODE';
360:
361: BEGIN
362:
362:
363: --
364: -- data is required
365:
366: IF (p_source_object_code IS NULL) OR (p_source_object_code = okl_api.g_miss_char) THEN
367: okl_api.set_message(p_app_name => g_app_name
368: ,p_msg_name => g_required_value
369: ,p_token1 => g_col_name_token
370: ,p_token1_value => 'SOURCE_OBJECT_CODE');
363: --
364: -- data is required
365:
366: IF (p_source_object_code IS NULL) OR (p_source_object_code = okl_api.g_miss_char) THEN
367: okl_api.set_message(p_app_name => g_app_name
368: ,p_msg_name => g_required_value
369: ,p_token1 => g_col_name_token
370: ,p_token1_value => 'SOURCE_OBJECT_CODE');
371: RAISE okl_api.g_exception_error;
367: okl_api.set_message(p_app_name => g_app_name
368: ,p_msg_name => g_required_value
369: ,p_token1 => g_col_name_token
370: ,p_token1_value => 'SOURCE_OBJECT_CODE');
371: RAISE okl_api.g_exception_error;
372: END IF;
373:
374: --if source object is not Adjustment MAtrix then it should belong to lookup OKL_ECC_OBJECT_CLASSES
375:
375:
376: IF p_source_object_code <> 'PAM' THEN
377: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_ECC_OBJECT_CLASSES'
378: ,p_lookup_code => p_source_object_code);
379: IF (l_return_status = okl_api.g_ret_sts_error) THEN
380: okl_api.set_message(p_app_name => g_app_name
381: ,p_msg_name => g_invalid_value
382: ,p_token1 => g_col_name_token
383: ,p_token1_value => 'SOURCE_OBJECT_CODE');
376: IF p_source_object_code <> 'PAM' THEN
377: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_ECC_OBJECT_CLASSES'
378: ,p_lookup_code => p_source_object_code);
379: IF (l_return_status = okl_api.g_ret_sts_error) THEN
380: okl_api.set_message(p_app_name => g_app_name
381: ,p_msg_name => g_invalid_value
382: ,p_token1 => g_col_name_token
383: ,p_token1_value => 'SOURCE_OBJECT_CODE');
384: RAISE okl_api.g_exception_error;
380: okl_api.set_message(p_app_name => g_app_name
381: ,p_msg_name => g_invalid_value
382: ,p_token1 => g_col_name_token
383: ,p_token1_value => 'SOURCE_OBJECT_CODE');
384: RAISE okl_api.g_exception_error;
385: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
386: RAISE okl_api.g_exception_unexpected_error;
387: END IF;
388: END IF;
381: ,p_msg_name => g_invalid_value
382: ,p_token1 => g_col_name_token
383: ,p_token1_value => 'SOURCE_OBJECT_CODE');
384: RAISE okl_api.g_exception_error;
385: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
386: RAISE okl_api.g_exception_unexpected_error;
387: END IF;
388: END IF;
389: RETURN g_ret_sts_success;
382: ,p_token1 => g_col_name_token
383: ,p_token1_value => 'SOURCE_OBJECT_CODE');
384: RAISE okl_api.g_exception_error;
385: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
386: RAISE okl_api.g_exception_unexpected_error;
387: END IF;
388: END IF;
389: RETURN g_ret_sts_success;
390: EXCEPTION
387: END IF;
388: END IF;
389: RETURN g_ret_sts_success;
390: EXCEPTION
391: WHEN okl_api.g_exception_error THEN
392: RETURN g_ret_sts_error;
393: WHEN okl_api.g_exception_unexpected_error THEN
394: RETURN g_ret_sts_unexp_error;
395: WHEN OTHERS THEN
389: RETURN g_ret_sts_success;
390: EXCEPTION
391: WHEN okl_api.g_exception_error THEN
392: RETURN g_ret_sts_error;
393: WHEN okl_api.g_exception_unexpected_error THEN
394: RETURN g_ret_sts_unexp_error;
395: WHEN OTHERS THEN
396: okl_api.set_message(p_app_name => g_app_name
397: ,p_msg_name => g_db_error
392: RETURN g_ret_sts_error;
393: WHEN okl_api.g_exception_unexpected_error THEN
394: RETURN g_ret_sts_unexp_error;
395: WHEN OTHERS THEN
396: okl_api.set_message(p_app_name => g_app_name
397: ,p_msg_name => g_db_error
398: ,p_token1 => g_prog_name_token
399: ,p_token1_value => l_api_name
400: ,p_token2 => 'SQLCODE'
461: l_ech_rec okl_ech_rec := p_ech_rec;
462:
463: BEGIN
464:
465: IF (l_ech_rec.criteria_set_id = okl_api.g_miss_num) THEN
466: l_ech_rec.criteria_set_id := NULL;
467: END IF;
468:
469: IF (l_ech_rec.object_version_number = okl_api.g_miss_num) THEN
465: IF (l_ech_rec.criteria_set_id = okl_api.g_miss_num) THEN
466: l_ech_rec.criteria_set_id := NULL;
467: END IF;
468:
469: IF (l_ech_rec.object_version_number = okl_api.g_miss_num) THEN
470: l_ech_rec.object_version_number := NULL;
471: END IF;
472:
473: IF (l_ech_rec.source_id = okl_api.g_miss_num) THEN
469: IF (l_ech_rec.object_version_number = okl_api.g_miss_num) THEN
470: l_ech_rec.object_version_number := NULL;
471: END IF;
472:
473: IF (l_ech_rec.source_id = okl_api.g_miss_num) THEN
474: l_ech_rec.source_id := NULL;
475: END IF;
476:
477: IF (l_ech_rec.source_object_code = okl_api.g_miss_char) THEN
473: IF (l_ech_rec.source_id = okl_api.g_miss_num) THEN
474: l_ech_rec.source_id := NULL;
475: END IF;
476:
477: IF (l_ech_rec.source_object_code = okl_api.g_miss_char) THEN
478: l_ech_rec.source_object_code := NULL;
479: END IF;
480:
481: IF (l_ech_rec.match_criteria_code = okl_api.g_miss_char) THEN
477: IF (l_ech_rec.source_object_code = okl_api.g_miss_char) THEN
478: l_ech_rec.source_object_code := NULL;
479: END IF;
480:
481: IF (l_ech_rec.match_criteria_code = okl_api.g_miss_char) THEN
482: l_ech_rec.match_criteria_code := NULL;
483: END IF;
484:
485: IF (l_ech_rec.validation_code = okl_api.g_miss_char) THEN
481: IF (l_ech_rec.match_criteria_code = okl_api.g_miss_char) THEN
482: l_ech_rec.match_criteria_code := NULL;
483: END IF;
484:
485: IF (l_ech_rec.validation_code = okl_api.g_miss_char) THEN
486: l_ech_rec.validation_code := NULL;
487: END IF;
488:
489: IF (l_ech_rec.created_by = okl_api.g_miss_num) THEN
485: IF (l_ech_rec.validation_code = okl_api.g_miss_char) THEN
486: l_ech_rec.validation_code := NULL;
487: END IF;
488:
489: IF (l_ech_rec.created_by = okl_api.g_miss_num) THEN
490: l_ech_rec.created_by := NULL;
491: END IF;
492:
493: IF (l_ech_rec.creation_date = okl_api.g_miss_date) THEN
489: IF (l_ech_rec.created_by = okl_api.g_miss_num) THEN
490: l_ech_rec.created_by := NULL;
491: END IF;
492:
493: IF (l_ech_rec.creation_date = okl_api.g_miss_date) THEN
494: l_ech_rec.creation_date := NULL;
495: END IF;
496:
497: IF (l_ech_rec.last_updated_by = okl_api.g_miss_num) THEN
493: IF (l_ech_rec.creation_date = okl_api.g_miss_date) THEN
494: l_ech_rec.creation_date := NULL;
495: END IF;
496:
497: IF (l_ech_rec.last_updated_by = okl_api.g_miss_num) THEN
498: l_ech_rec.last_updated_by := NULL;
499: END IF;
500:
501: IF (l_ech_rec.last_update_date = okl_api.g_miss_date) THEN
497: IF (l_ech_rec.last_updated_by = okl_api.g_miss_num) THEN
498: l_ech_rec.last_updated_by := NULL;
499: END IF;
500:
501: IF (l_ech_rec.last_update_date = okl_api.g_miss_date) THEN
502: l_ech_rec.last_update_date := NULL;
503: END IF;
504:
505: IF (l_ech_rec.last_update_login = okl_api.g_miss_num) THEN
501: IF (l_ech_rec.last_update_date = okl_api.g_miss_date) THEN
502: l_ech_rec.last_update_date := NULL;
503: END IF;
504:
505: IF (l_ech_rec.last_update_login = okl_api.g_miss_num) THEN
506: l_ech_rec.last_update_login := NULL;
507: END IF;
508: RETURN(l_ech_rec);
509: END null_out_defaults;
514: RETURN(okc_p_util.raw_to_number(sys_guid()));
515: END get_seq_id;
516:
517: FUNCTION validate_attributes(p_ech_rec IN okl_ech_rec) RETURN varchar2 IS
518: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
519: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
520: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
521:
522: BEGIN
515: END get_seq_id;
516:
517: FUNCTION validate_attributes(p_ech_rec IN okl_ech_rec) RETURN varchar2 IS
518: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
519: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
520: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
521:
522: BEGIN
523:
525:
526: l_return_status := validate_criteria_set_id(p_ech_rec.criteria_set_id);
527:
528: IF (l_return_status = g_ret_sts_unexp_error) THEN
529: RAISE okl_api.g_exception_unexpected_error;
530: ELSIF (l_return_status = g_ret_sts_error) THEN
531: RAISE okl_api.g_exception_error;
532: END IF;
533: l_return_status := validate_object_version_number(p_ech_rec.object_version_number);
527:
528: IF (l_return_status = g_ret_sts_unexp_error) THEN
529: RAISE okl_api.g_exception_unexpected_error;
530: ELSIF (l_return_status = g_ret_sts_error) THEN
531: RAISE okl_api.g_exception_error;
532: END IF;
533: l_return_status := validate_object_version_number(p_ech_rec.object_version_number);
534:
535: IF (l_return_status = g_ret_sts_unexp_error) THEN
532: END IF;
533: l_return_status := validate_object_version_number(p_ech_rec.object_version_number);
534:
535: IF (l_return_status = g_ret_sts_unexp_error) THEN
536: RAISE okl_api.g_exception_unexpected_error;
537: ELSIF (l_return_status = g_ret_sts_error) THEN
538: RAISE okl_api.g_exception_error;
539: END IF;
540: l_return_status := validate_source_id(p_ech_rec.source_id);
534:
535: IF (l_return_status = g_ret_sts_unexp_error) THEN
536: RAISE okl_api.g_exception_unexpected_error;
537: ELSIF (l_return_status = g_ret_sts_error) THEN
538: RAISE okl_api.g_exception_error;
539: END IF;
540: l_return_status := validate_source_id(p_ech_rec.source_id);
541:
542: IF (l_return_status = g_ret_sts_unexp_error) THEN
539: END IF;
540: l_return_status := validate_source_id(p_ech_rec.source_id);
541:
542: IF (l_return_status = g_ret_sts_unexp_error) THEN
543: RAISE okl_api.g_exception_unexpected_error;
544: ELSIF (l_return_status = g_ret_sts_error) THEN
545: RAISE okl_api.g_exception_error;
546: END IF;
547: l_return_status := validate_source_object_code(p_ech_rec.source_object_code);
541:
542: IF (l_return_status = g_ret_sts_unexp_error) THEN
543: RAISE okl_api.g_exception_unexpected_error;
544: ELSIF (l_return_status = g_ret_sts_error) THEN
545: RAISE okl_api.g_exception_error;
546: END IF;
547: l_return_status := validate_source_object_code(p_ech_rec.source_object_code);
548:
549: IF (l_return_status = g_ret_sts_unexp_error) THEN
546: END IF;
547: l_return_status := validate_source_object_code(p_ech_rec.source_object_code);
548:
549: IF (l_return_status = g_ret_sts_unexp_error) THEN
550: RAISE okl_api.g_exception_unexpected_error;
551: ELSIF (l_return_status = g_ret_sts_error) THEN
552: RAISE okl_api.g_exception_error;
553: END IF;
554: RETURN(x_return_status);
548:
549: IF (l_return_status = g_ret_sts_unexp_error) THEN
550: RAISE okl_api.g_exception_unexpected_error;
551: ELSIF (l_return_status = g_ret_sts_error) THEN
552: RAISE okl_api.g_exception_error;
553: END IF;
554: RETURN(x_return_status);
555: EXCEPTION
556: WHEN okl_api.g_exception_error THEN
552: RAISE okl_api.g_exception_error;
553: END IF;
554: RETURN(x_return_status);
555: EXCEPTION
556: WHEN okl_api.g_exception_error THEN
557: RETURN g_ret_sts_error;
558: WHEN okl_api.g_exception_unexpected_error THEN
559: RETURN g_ret_sts_unexp_error;
560: WHEN OTHERS THEN
554: RETURN(x_return_status);
555: EXCEPTION
556: WHEN okl_api.g_exception_error THEN
557: RETURN g_ret_sts_error;
558: WHEN okl_api.g_exception_unexpected_error THEN
559: RETURN g_ret_sts_unexp_error;
560: WHEN OTHERS THEN
561: okl_api.set_message(p_app_name => g_app_name
562: ,p_msg_name => g_db_error
557: RETURN g_ret_sts_error;
558: WHEN okl_api.g_exception_unexpected_error THEN
559: RETURN g_ret_sts_unexp_error;
560: WHEN OTHERS THEN
561: okl_api.set_message(p_app_name => g_app_name
562: ,p_msg_name => g_db_error
563: ,p_token1 => g_prog_name_token
564: ,p_token1_value => l_api_name
565: ,p_token2 => 'SQLCODE'
569: RETURN g_ret_sts_unexp_error;
570: END validate_attributes;
571:
572: FUNCTION validate_record(p_ech_rec IN okl_ech_rec) RETURN varchar2 IS
573: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
574: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
575: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'Validate_Record';
576:
577: BEGIN
570: END validate_attributes;
571:
572: FUNCTION validate_record(p_ech_rec IN okl_ech_rec) RETURN varchar2 IS
573: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
574: x_return_status varchar2(1) := okl_api.g_ret_sts_success;
575: l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'Validate_Record';
576:
577: BEGIN
578:
579: IF p_ech_rec.source_object_code <> 'PAM' THEN
580:
581: --validate match_criteria_code
582:
583: IF (p_ech_rec.match_criteria_code IS NULL) OR (p_ech_rec.match_criteria_code = okl_api.g_miss_char) THEN
584: okl_api.set_message(p_app_name => g_app_name
585: ,p_msg_name => g_required_value
586: ,p_token1 => g_col_name_token
587: ,p_token1_value => 'MATCH_CRITERIA_CODE');
580:
581: --validate match_criteria_code
582:
583: IF (p_ech_rec.match_criteria_code IS NULL) OR (p_ech_rec.match_criteria_code = okl_api.g_miss_char) THEN
584: okl_api.set_message(p_app_name => g_app_name
585: ,p_msg_name => g_required_value
586: ,p_token1 => g_col_name_token
587: ,p_token1_value => 'MATCH_CRITERIA_CODE');
588: RAISE okl_api.g_exception_error;
584: okl_api.set_message(p_app_name => g_app_name
585: ,p_msg_name => g_required_value
586: ,p_token1 => g_col_name_token
587: ,p_token1_value => 'MATCH_CRITERIA_CODE');
588: RAISE okl_api.g_exception_error;
589: END IF;
590: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_EC_MATCH_CRITERIA'
591: ,p_lookup_code => p_ech_rec.match_criteria_code);
592: IF (l_return_status = okl_api.g_ret_sts_error) THEN
588: RAISE okl_api.g_exception_error;
589: END IF;
590: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_EC_MATCH_CRITERIA'
591: ,p_lookup_code => p_ech_rec.match_criteria_code);
592: IF (l_return_status = okl_api.g_ret_sts_error) THEN
593: okl_api.set_message(p_app_name => g_app_name
594: ,p_msg_name => g_invalid_value
595: ,p_token1 => g_col_name_token
596: ,p_token1_value => 'MATCH_CRITERIA_CODE');
589: END IF;
590: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_EC_MATCH_CRITERIA'
591: ,p_lookup_code => p_ech_rec.match_criteria_code);
592: IF (l_return_status = okl_api.g_ret_sts_error) THEN
593: okl_api.set_message(p_app_name => g_app_name
594: ,p_msg_name => g_invalid_value
595: ,p_token1 => g_col_name_token
596: ,p_token1_value => 'MATCH_CRITERIA_CODE');
597: RAISE okl_api.g_exception_error;
593: okl_api.set_message(p_app_name => g_app_name
594: ,p_msg_name => g_invalid_value
595: ,p_token1 => g_col_name_token
596: ,p_token1_value => 'MATCH_CRITERIA_CODE');
597: RAISE okl_api.g_exception_error;
598: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
599: RAISE okl_api.g_exception_unexpected_error;
600: END IF;
601:
594: ,p_msg_name => g_invalid_value
595: ,p_token1 => g_col_name_token
596: ,p_token1_value => 'MATCH_CRITERIA_CODE');
597: RAISE okl_api.g_exception_error;
598: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
599: RAISE okl_api.g_exception_unexpected_error;
600: END IF;
601:
602: --validate validation_code
595: ,p_token1 => g_col_name_token
596: ,p_token1_value => 'MATCH_CRITERIA_CODE');
597: RAISE okl_api.g_exception_error;
598: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
599: RAISE okl_api.g_exception_unexpected_error;
600: END IF;
601:
602: --validate validation_code
603:
600: END IF;
601:
602: --validate validation_code
603:
604: IF (p_ech_rec.validation_code IS NULL) OR (p_ech_rec.validation_code = okl_api.g_miss_char) THEN
605: okl_api.set_message(p_app_name => g_app_name
606: ,p_msg_name => g_required_value
607: ,p_token1 => g_col_name_token
608: ,p_token1_value => 'validation');
601:
602: --validate validation_code
603:
604: IF (p_ech_rec.validation_code IS NULL) OR (p_ech_rec.validation_code = okl_api.g_miss_char) THEN
605: okl_api.set_message(p_app_name => g_app_name
606: ,p_msg_name => g_required_value
607: ,p_token1 => g_col_name_token
608: ,p_token1_value => 'validation');
609: RAISE okl_api.g_exception_error;
605: okl_api.set_message(p_app_name => g_app_name
606: ,p_msg_name => g_required_value
607: ,p_token1 => g_col_name_token
608: ,p_token1_value => 'validation');
609: RAISE okl_api.g_exception_error;
610: END IF;
611: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_EC_VALIDATIONS'
612: ,p_lookup_code => p_ech_rec.validation_code);
613: IF (l_return_status = okl_api.g_ret_sts_error) THEN
609: RAISE okl_api.g_exception_error;
610: END IF;
611: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_EC_VALIDATIONS'
612: ,p_lookup_code => p_ech_rec.validation_code);
613: IF (l_return_status = okl_api.g_ret_sts_error) THEN
614: okl_api.set_message(p_app_name => g_app_name
615: ,p_msg_name => g_invalid_value
616: ,p_token1 => g_col_name_token
617: ,p_token1_value => 'validation');
610: END IF;
611: l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_EC_VALIDATIONS'
612: ,p_lookup_code => p_ech_rec.validation_code);
613: IF (l_return_status = okl_api.g_ret_sts_error) THEN
614: okl_api.set_message(p_app_name => g_app_name
615: ,p_msg_name => g_invalid_value
616: ,p_token1 => g_col_name_token
617: ,p_token1_value => 'validation');
618: RAISE okl_api.g_exception_error;
614: okl_api.set_message(p_app_name => g_app_name
615: ,p_msg_name => g_invalid_value
616: ,p_token1 => g_col_name_token
617: ,p_token1_value => 'validation');
618: RAISE okl_api.g_exception_error;
619: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
620: RAISE okl_api.g_exception_unexpected_error;
621: END IF;
622: END IF;
615: ,p_msg_name => g_invalid_value
616: ,p_token1 => g_col_name_token
617: ,p_token1_value => 'validation');
618: RAISE okl_api.g_exception_error;
619: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
620: RAISE okl_api.g_exception_unexpected_error;
621: END IF;
622: END IF;
623: RETURN(x_return_status);
616: ,p_token1 => g_col_name_token
617: ,p_token1_value => 'validation');
618: RAISE okl_api.g_exception_error;
619: ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
620: RAISE okl_api.g_exception_unexpected_error;
621: END IF;
622: END IF;
623: RETURN(x_return_status);
624: RETURN g_ret_sts_success;
622: END IF;
623: RETURN(x_return_status);
624: RETURN g_ret_sts_success;
625: EXCEPTION
626: WHEN okl_api.g_exception_error THEN
627: RETURN g_ret_sts_error;
628: WHEN okl_api.g_exception_unexpected_error THEN
629: RETURN g_ret_sts_unexp_error;
630: WHEN OTHERS THEN
624: RETURN g_ret_sts_success;
625: EXCEPTION
626: WHEN okl_api.g_exception_error THEN
627: RETURN g_ret_sts_error;
628: WHEN okl_api.g_exception_unexpected_error THEN
629: RETURN g_ret_sts_unexp_error;
630: WHEN OTHERS THEN
631: okl_api.set_message(p_app_name => g_app_name
632: ,p_msg_name => g_db_error
627: RETURN g_ret_sts_error;
628: WHEN okl_api.g_exception_unexpected_error THEN
629: RETURN g_ret_sts_unexp_error;
630: WHEN OTHERS THEN
631: okl_api.set_message(p_app_name => g_app_name
632: ,p_msg_name => g_db_error
633: ,p_token1 => g_prog_name_token
634: ,p_token1_value => l_api_name
635: ,p_token2 => 'SQLCODE'
643: -- Procedure insert_row
644: --------------------------------------------------------------------------------
645:
646: PROCEDURE insert_row(p_api_version IN number
647: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
648: ,x_return_status OUT NOCOPY varchar2
649: ,x_msg_count OUT NOCOPY number
650: ,x_msg_data OUT NOCOPY varchar2
651: ,p_ech_rec IN okl_ech_rec
651: ,p_ech_rec IN okl_ech_rec
652: ,x_ech_rec OUT NOCOPY okl_ech_rec) IS
653: l_api_version CONSTANT number := 1;
654: l_api_name CONSTANT varchar2(30) := 'insert_row';
655: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
656: l_ech_rec okl_ech_rec;
657: l_def_ech_rec okl_ech_rec;
658:
659: FUNCTION fill_who_columns(p_ech_rec IN okl_ech_rec) RETURN okl_ech_rec IS
669: END fill_who_columns;
670:
671: FUNCTION set_attributes(p_ech_rec IN okl_ech_rec
672: ,x_ech_rec OUT NOCOPY okl_ech_rec) RETURN varchar2 IS
673: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
674:
675: BEGIN
676: x_ech_rec := p_ech_rec;
677: x_ech_rec.object_version_number := 1;
685: BEGIN
686:
687: --
688:
689: l_return_status := okl_api.start_activity(l_api_name
690: ,g_pkg_name
691: ,p_init_msg_list
692: ,l_api_version
693: ,p_api_version
693: ,p_api_version
694: ,'_PVT'
695: ,x_return_status);
696:
697: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
698: RAISE okl_api.g_exception_unexpected_error;
699: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
700: RAISE okl_api.g_exception_error;
701: END IF;
694: ,'_PVT'
695: ,x_return_status);
696:
697: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
698: RAISE okl_api.g_exception_unexpected_error;
699: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
700: RAISE okl_api.g_exception_error;
701: END IF;
702:
695: ,x_return_status);
696:
697: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
698: RAISE okl_api.g_exception_unexpected_error;
699: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
700: RAISE okl_api.g_exception_error;
701: END IF;
702:
703: --
696:
697: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
698: RAISE okl_api.g_exception_unexpected_error;
699: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
700: RAISE okl_api.g_exception_error;
701: END IF;
702:
703: --
704: --null out defaults
709: --Setting Item Attributes
710:
711: l_return_status := set_attributes(l_ech_rec, l_def_ech_rec);
712:
713: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
714: RAISE okl_api.g_exception_unexpected_error;
715: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
716: RAISE okl_api.g_exception_error;
717: END IF;
710:
711: l_return_status := set_attributes(l_ech_rec, l_def_ech_rec);
712:
713: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
714: RAISE okl_api.g_exception_unexpected_error;
715: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
716: RAISE okl_api.g_exception_error;
717: END IF;
718:
711: l_return_status := set_attributes(l_ech_rec, l_def_ech_rec);
712:
713: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
714: RAISE okl_api.g_exception_unexpected_error;
715: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
716: RAISE okl_api.g_exception_error;
717: END IF;
718:
719: --
712:
713: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
714: RAISE okl_api.g_exception_unexpected_error;
715: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
716: RAISE okl_api.g_exception_error;
717: END IF;
718:
719: --
720: --fill who columns
727: l_return_status := validate_attributes(l_def_ech_rec);
728:
729: --
730:
731: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
732: RAISE okl_api.g_exception_unexpected_error;
733: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
734: RAISE okl_api.g_exception_error;
735: END IF;
728:
729: --
730:
731: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
732: RAISE okl_api.g_exception_unexpected_error;
733: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
734: RAISE okl_api.g_exception_error;
735: END IF;
736:
729: --
730:
731: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
732: RAISE okl_api.g_exception_unexpected_error;
733: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
734: RAISE okl_api.g_exception_error;
735: END IF;
736:
737: --validate record
730:
731: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
732: RAISE okl_api.g_exception_unexpected_error;
733: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
734: RAISE okl_api.g_exception_error;
735: END IF;
736:
737: --validate record
738: --
738: --
739:
740: l_return_status := validate_record(l_def_ech_rec);
741:
742: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
743: RAISE okl_api.g_exception_unexpected_error;
744: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
745: RAISE okl_api.g_exception_error;
746: END IF; --insert into table
739:
740: l_return_status := validate_record(l_def_ech_rec);
741:
742: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
743: RAISE okl_api.g_exception_unexpected_error;
744: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
745: RAISE okl_api.g_exception_error;
746: END IF; --insert into table
747:
740: l_return_status := validate_record(l_def_ech_rec);
741:
742: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
743: RAISE okl_api.g_exception_unexpected_error;
744: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
745: RAISE okl_api.g_exception_error;
746: END IF; --insert into table
747:
748: INSERT INTO okl_fe_criteria_set
741:
742: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
743: RAISE okl_api.g_exception_unexpected_error;
744: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
745: RAISE okl_api.g_exception_error;
746: END IF; --insert into table
747:
748: INSERT INTO okl_fe_criteria_set
749: (criteria_set_id
772: --Set OUT Values
773:
774: x_ech_rec := l_def_ech_rec;
775: x_return_status := l_return_status;
776: okl_api.end_activity(x_msg_count, x_msg_data);
777: EXCEPTION
778: WHEN g_exception_halt_validation THEN
779:
780: -- No action necessary. Validation can continue to next attribute/column
779:
780: -- No action necessary. Validation can continue to next attribute/column
781:
782: NULL;
783: WHEN okl_api.g_exception_error THEN
784: x_return_status := okl_api.handle_exceptions(l_api_name
785: ,g_pkg_name
786: ,'OKL_API.G_RET_STS_ERROR'
787: ,x_msg_count
780: -- No action necessary. Validation can continue to next attribute/column
781:
782: NULL;
783: WHEN okl_api.g_exception_error THEN
784: x_return_status := okl_api.handle_exceptions(l_api_name
785: ,g_pkg_name
786: ,'OKL_API.G_RET_STS_ERROR'
787: ,x_msg_count
788: ,x_msg_data
782: NULL;
783: WHEN okl_api.g_exception_error THEN
784: x_return_status := okl_api.handle_exceptions(l_api_name
785: ,g_pkg_name
786: ,'OKL_API.G_RET_STS_ERROR'
787: ,x_msg_count
788: ,x_msg_data
789: ,'_PVT');
790: WHEN okl_api.g_exception_unexpected_error THEN
786: ,'OKL_API.G_RET_STS_ERROR'
787: ,x_msg_count
788: ,x_msg_data
789: ,'_PVT');
790: WHEN okl_api.g_exception_unexpected_error THEN
791: x_return_status := okl_api.handle_exceptions(l_api_name
792: ,g_pkg_name
793: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
794: ,x_msg_count
787: ,x_msg_count
788: ,x_msg_data
789: ,'_PVT');
790: WHEN okl_api.g_exception_unexpected_error THEN
791: x_return_status := okl_api.handle_exceptions(l_api_name
792: ,g_pkg_name
793: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
794: ,x_msg_count
795: ,x_msg_data
789: ,'_PVT');
790: WHEN okl_api.g_exception_unexpected_error THEN
791: x_return_status := okl_api.handle_exceptions(l_api_name
792: ,g_pkg_name
793: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
794: ,x_msg_count
795: ,x_msg_data
796: ,'_PVT');
797: WHEN OTHERS THEN
794: ,x_msg_count
795: ,x_msg_data
796: ,'_PVT');
797: WHEN OTHERS THEN
798: x_return_status := okl_api.handle_exceptions(l_api_name
799: ,g_pkg_name
800: ,'OTHERS'
801: ,x_msg_count
802: ,x_msg_data
807: -- Procedure insert_row_tbl
808: --------------------------------------------------------------------------------
809:
810: PROCEDURE insert_row(p_api_version IN number
811: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
812: ,x_return_status OUT NOCOPY varchar2
813: ,x_msg_count OUT NOCOPY number
814: ,x_msg_data OUT NOCOPY varchar2
815: ,p_ech_tbl IN okl_ech_tbl
815: ,p_ech_tbl IN okl_ech_tbl
816: ,x_ech_tbl OUT NOCOPY okl_ech_tbl) IS
817: l_api_version CONSTANT number := 1;
818: l_api_name CONSTANT varchar2(30) := 'insert_row_tbl';
819: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
820: i number := 0;
821: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
822:
823: BEGIN
817: l_api_version CONSTANT number := 1;
818: l_api_name CONSTANT varchar2(30) := 'insert_row_tbl';
819: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
820: i number := 0;
821: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
822:
823: BEGIN
824: okl_api.init_msg_list(p_init_msg_list);
825:
820: i number := 0;
821: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
822:
823: BEGIN
824: okl_api.init_msg_list(p_init_msg_list);
825:
826: -- Make sure PL/SQL table has records in it before passing
827:
828: IF (p_ech_tbl.COUNT > 0) THEN
829: i := p_ech_tbl.FIRST;
830:
831: LOOP
832: insert_row(p_api_version => p_api_version
833: ,p_init_msg_list => okl_api.g_false
834: ,x_return_status => x_return_status
835: ,x_msg_count => x_msg_count
836: ,x_msg_data => x_msg_data
837: ,p_ech_rec => p_ech_tbl(i)
835: ,x_msg_count => x_msg_count
836: ,x_msg_data => x_msg_data
837: ,p_ech_rec => p_ech_tbl(i)
838: ,x_ech_rec => x_ech_tbl(i));
839: IF x_return_status <> okl_api.g_ret_sts_success THEN
840: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
841: l_overall_status := x_return_status;
842: END IF;
843: END IF;
836: ,x_msg_data => x_msg_data
837: ,p_ech_rec => p_ech_tbl(i)
838: ,x_ech_rec => x_ech_tbl(i));
839: IF x_return_status <> okl_api.g_ret_sts_success THEN
840: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
841: l_overall_status := x_return_status;
842: END IF;
843: END IF;
844: EXIT WHEN(i = p_ech_tbl.LAST);
852:
853: -- No action necessary. Validation can continue to next attribute/column
854:
855: NULL;
856: WHEN okl_api.g_exception_error THEN
857: x_return_status := okl_api.handle_exceptions(l_api_name
858: ,g_pkg_name
859: ,'OKL_API.G_RET_STS_ERROR'
860: ,x_msg_count
853: -- No action necessary. Validation can continue to next attribute/column
854:
855: NULL;
856: WHEN okl_api.g_exception_error THEN
857: x_return_status := okl_api.handle_exceptions(l_api_name
858: ,g_pkg_name
859: ,'OKL_API.G_RET_STS_ERROR'
860: ,x_msg_count
861: ,x_msg_data
855: NULL;
856: WHEN okl_api.g_exception_error THEN
857: x_return_status := okl_api.handle_exceptions(l_api_name
858: ,g_pkg_name
859: ,'OKL_API.G_RET_STS_ERROR'
860: ,x_msg_count
861: ,x_msg_data
862: ,'_PVT');
863: WHEN okl_api.g_exception_unexpected_error THEN
859: ,'OKL_API.G_RET_STS_ERROR'
860: ,x_msg_count
861: ,x_msg_data
862: ,'_PVT');
863: WHEN okl_api.g_exception_unexpected_error THEN
864: x_return_status := okl_api.handle_exceptions(l_api_name
865: ,g_pkg_name
866: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
867: ,x_msg_count
860: ,x_msg_count
861: ,x_msg_data
862: ,'_PVT');
863: WHEN okl_api.g_exception_unexpected_error THEN
864: x_return_status := okl_api.handle_exceptions(l_api_name
865: ,g_pkg_name
866: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
867: ,x_msg_count
868: ,x_msg_data
862: ,'_PVT');
863: WHEN okl_api.g_exception_unexpected_error THEN
864: x_return_status := okl_api.handle_exceptions(l_api_name
865: ,g_pkg_name
866: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
867: ,x_msg_count
868: ,x_msg_data
869: ,'_PVT');
870: WHEN OTHERS THEN
867: ,x_msg_count
868: ,x_msg_data
869: ,'_PVT');
870: WHEN OTHERS THEN
871: x_return_status := okl_api.handle_exceptions(l_api_name
872: ,g_pkg_name
873: ,'OTHERS'
874: ,x_msg_count
875: ,x_msg_data
880: -- Procedure update_row
881: --------------------------------------------------------------------------------
882:
883: PROCEDURE update_row(p_api_version IN number
884: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
885: ,x_return_status OUT NOCOPY varchar2
886: ,x_msg_count OUT NOCOPY number
887: ,x_msg_data OUT NOCOPY varchar2
888: ,p_ech_rec IN okl_ech_rec
888: ,p_ech_rec IN okl_ech_rec
889: ,x_ech_rec OUT NOCOPY okl_ech_rec) IS
890: l_api_version CONSTANT number := 1;
891: l_api_name CONSTANT varchar2(30) := 'update_row';
892: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
893: l_ech_rec okl_ech_rec := p_ech_rec;
894: l_def_ech_rec okl_ech_rec;
895: l_row_notfound boolean := true;
896:
907: FUNCTION populate_new_record(p_ech_rec IN okl_ech_rec
908: ,x_ech_rec OUT NOCOPY okl_ech_rec) RETURN varchar2 IS
909: l_ech_rec okl_ech_rec;
910: l_row_notfound boolean := true;
911: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
912:
913: BEGIN
914: x_ech_rec := p_ech_rec;
915:
917:
918: l_ech_rec := get_rec(p_ech_rec, l_row_notfound);
919:
920: IF (l_row_notfound) THEN
921: l_return_status := okl_api.g_ret_sts_unexp_error;
922: END IF;
923:
924: IF (x_ech_rec.criteria_set_id IS NULL) THEN
925: x_ech_rec.criteria_set_id := l_ech_rec.criteria_set_id;
952: END populate_new_record;
953:
954: FUNCTION set_attributes(p_ech_rec IN okl_ech_rec
955: ,x_ech_rec OUT NOCOPY okl_ech_rec) RETURN varchar2 IS
956: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
957:
958: BEGIN
959: x_ech_rec := p_ech_rec;
960: RETURN(l_return_status);
960: RETURN(l_return_status);
961: END set_attributes;
962:
963: BEGIN
964: l_return_status := okl_api.start_activity(l_api_name
965: ,g_pkg_name
966: ,p_init_msg_list
967: ,l_api_version
968: ,p_api_version
968: ,p_api_version
969: ,'_PVT'
970: ,x_return_status);
971:
972: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
973: RAISE okl_api.g_exception_unexpected_error;
974: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
975: RAISE okl_api.g_exception_error;
976: END IF;
969: ,'_PVT'
970: ,x_return_status);
971:
972: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
973: RAISE okl_api.g_exception_unexpected_error;
974: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
975: RAISE okl_api.g_exception_error;
976: END IF;
977:
970: ,x_return_status);
971:
972: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
973: RAISE okl_api.g_exception_unexpected_error;
974: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
975: RAISE okl_api.g_exception_error;
976: END IF;
977:
978:
971:
972: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
973: RAISE okl_api.g_exception_unexpected_error;
974: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
975: RAISE okl_api.g_exception_error;
976: END IF;
977:
978:
979: --Setting Item Attributes
979: --Setting Item Attributes
980:
981: l_return_status := set_attributes(p_ech_rec, l_ech_rec);
982:
983: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
984: RAISE okl_api.g_exception_unexpected_error;
985: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
986: RAISE okl_api.g_exception_error;
987: END IF;
980:
981: l_return_status := set_attributes(p_ech_rec, l_ech_rec);
982:
983: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
984: RAISE okl_api.g_exception_unexpected_error;
985: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
986: RAISE okl_api.g_exception_error;
987: END IF;
988:
981: l_return_status := set_attributes(p_ech_rec, l_ech_rec);
982:
983: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
984: RAISE okl_api.g_exception_unexpected_error;
985: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
986: RAISE okl_api.g_exception_error;
987: END IF;
988:
989: l_return_status := populate_new_record(l_ech_rec, l_def_ech_rec);
982:
983: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
984: RAISE okl_api.g_exception_unexpected_error;
985: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
986: RAISE okl_api.g_exception_error;
987: END IF;
988:
989: l_return_status := populate_new_record(l_ech_rec, l_def_ech_rec);
990:
987: END IF;
988:
989: l_return_status := populate_new_record(l_ech_rec, l_def_ech_rec);
990:
991: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
992: RAISE okl_api.g_exception_unexpected_error;
993: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
994: RAISE okl_api.g_exception_error;
995: END IF;
988:
989: l_return_status := populate_new_record(l_ech_rec, l_def_ech_rec);
990:
991: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
992: RAISE okl_api.g_exception_unexpected_error;
993: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
994: RAISE okl_api.g_exception_error;
995: END IF;
996:
989: l_return_status := populate_new_record(l_ech_rec, l_def_ech_rec);
990:
991: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
992: RAISE okl_api.g_exception_unexpected_error;
993: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
994: RAISE okl_api.g_exception_error;
995: END IF;
996:
997: --null out G miss values
990:
991: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
992: RAISE okl_api.g_exception_unexpected_error;
993: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
994: RAISE okl_api.g_exception_error;
995: END IF;
996:
997: --null out G miss values
998:
1007: --validate attributes
1008:
1009: l_return_status := validate_attributes(l_def_ech_rec);
1010:
1011: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1012: RAISE okl_api.g_exception_unexpected_error;
1013: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1014: RAISE okl_api.g_exception_error;
1015: END IF;
1008:
1009: l_return_status := validate_attributes(l_def_ech_rec);
1010:
1011: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1012: RAISE okl_api.g_exception_unexpected_error;
1013: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1014: RAISE okl_api.g_exception_error;
1015: END IF;
1016:
1009: l_return_status := validate_attributes(l_def_ech_rec);
1010:
1011: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1012: RAISE okl_api.g_exception_unexpected_error;
1013: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1014: RAISE okl_api.g_exception_error;
1015: END IF;
1016:
1017:
1010:
1011: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1012: RAISE okl_api.g_exception_unexpected_error;
1013: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1014: RAISE okl_api.g_exception_error;
1015: END IF;
1016:
1017:
1018: --validate record
1018: --validate record
1019:
1020: l_return_status := validate_record(l_def_ech_rec);
1021:
1022: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1023: RAISE okl_api.g_exception_unexpected_error;
1024: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1025: RAISE okl_api.g_exception_error;
1026: END IF;
1019:
1020: l_return_status := validate_record(l_def_ech_rec);
1021:
1022: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1023: RAISE okl_api.g_exception_unexpected_error;
1024: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1025: RAISE okl_api.g_exception_error;
1026: END IF;
1027:
1020: l_return_status := validate_record(l_def_ech_rec);
1021:
1022: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1023: RAISE okl_api.g_exception_unexpected_error;
1024: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1025: RAISE okl_api.g_exception_error;
1026: END IF;
1027:
1028:
1021:
1022: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1023: RAISE okl_api.g_exception_unexpected_error;
1024: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1025: RAISE okl_api.g_exception_error;
1026: END IF;
1027:
1028:
1029: --lock the row
1027:
1028:
1029: --lock the row
1030:
1031: lock_row(p_init_msg_list => okl_api.g_false
1032: ,x_return_status => l_return_status
1033: ,x_msg_count => x_msg_count
1034: ,x_msg_data => x_msg_data
1035: ,p_ech_rec => l_def_ech_rec);
1033: ,x_msg_count => x_msg_count
1034: ,x_msg_data => x_msg_data
1035: ,p_ech_rec => l_def_ech_rec);
1036:
1037: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1038: RAISE okl_api.g_exception_unexpected_error;
1039: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1040: RAISE okl_api.g_exception_error;
1041: END IF;
1034: ,x_msg_data => x_msg_data
1035: ,p_ech_rec => l_def_ech_rec);
1036:
1037: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1038: RAISE okl_api.g_exception_unexpected_error;
1039: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1040: RAISE okl_api.g_exception_error;
1041: END IF;
1042:
1035: ,p_ech_rec => l_def_ech_rec);
1036:
1037: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1038: RAISE okl_api.g_exception_unexpected_error;
1039: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1040: RAISE okl_api.g_exception_error;
1041: END IF;
1042:
1043: --update the record
1036:
1037: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1038: RAISE okl_api.g_exception_unexpected_error;
1039: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1040: RAISE okl_api.g_exception_error;
1041: END IF;
1042:
1043: --update the record
1044:
1062: --Set OUT Values
1063:
1064: x_ech_rec := l_def_ech_rec;
1065: x_return_status := l_return_status;
1066: okl_api.end_activity(x_msg_count, x_msg_data);
1067: EXCEPTION
1068: WHEN g_exception_halt_validation THEN
1069:
1070: -- No action necessary. Validation can continue to next attribute/column
1069:
1070: -- No action necessary. Validation can continue to next attribute/column
1071:
1072: NULL;
1073: WHEN okl_api.g_exception_error THEN
1074: x_return_status := okl_api.handle_exceptions(l_api_name
1075: ,g_pkg_name
1076: ,'OKL_API.G_RET_STS_ERROR'
1077: ,x_msg_count
1070: -- No action necessary. Validation can continue to next attribute/column
1071:
1072: NULL;
1073: WHEN okl_api.g_exception_error THEN
1074: x_return_status := okl_api.handle_exceptions(l_api_name
1075: ,g_pkg_name
1076: ,'OKL_API.G_RET_STS_ERROR'
1077: ,x_msg_count
1078: ,x_msg_data
1072: NULL;
1073: WHEN okl_api.g_exception_error THEN
1074: x_return_status := okl_api.handle_exceptions(l_api_name
1075: ,g_pkg_name
1076: ,'OKL_API.G_RET_STS_ERROR'
1077: ,x_msg_count
1078: ,x_msg_data
1079: ,'_PVT');
1080: WHEN okl_api.g_exception_unexpected_error THEN
1076: ,'OKL_API.G_RET_STS_ERROR'
1077: ,x_msg_count
1078: ,x_msg_data
1079: ,'_PVT');
1080: WHEN okl_api.g_exception_unexpected_error THEN
1081: x_return_status := okl_api.handle_exceptions(l_api_name
1082: ,g_pkg_name
1083: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1084: ,x_msg_count
1077: ,x_msg_count
1078: ,x_msg_data
1079: ,'_PVT');
1080: WHEN okl_api.g_exception_unexpected_error THEN
1081: x_return_status := okl_api.handle_exceptions(l_api_name
1082: ,g_pkg_name
1083: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1084: ,x_msg_count
1085: ,x_msg_data
1079: ,'_PVT');
1080: WHEN okl_api.g_exception_unexpected_error THEN
1081: x_return_status := okl_api.handle_exceptions(l_api_name
1082: ,g_pkg_name
1083: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1084: ,x_msg_count
1085: ,x_msg_data
1086: ,'_PVT');
1087: WHEN OTHERS THEN
1084: ,x_msg_count
1085: ,x_msg_data
1086: ,'_PVT');
1087: WHEN OTHERS THEN
1088: x_return_status := okl_api.handle_exceptions(l_api_name
1089: ,g_pkg_name
1090: ,'OTHERS'
1091: ,x_msg_count
1092: ,x_msg_data
1097: -- Procedure update_row_tbl
1098: --------------------------------------------------------------------------------
1099:
1100: PROCEDURE update_row(p_api_version IN number
1101: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1102: ,x_return_status OUT NOCOPY varchar2
1103: ,x_msg_count OUT NOCOPY number
1104: ,x_msg_data OUT NOCOPY varchar2
1105: ,p_ech_tbl IN okl_ech_tbl
1105: ,p_ech_tbl IN okl_ech_tbl
1106: ,x_ech_tbl OUT NOCOPY okl_ech_tbl) IS
1107: l_api_version CONSTANT number := 1;
1108: l_api_name CONSTANT varchar2(30) := 'update_row_tbl';
1109: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1110: i number := 0;
1111: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1112:
1113: BEGIN
1107: l_api_version CONSTANT number := 1;
1108: l_api_name CONSTANT varchar2(30) := 'update_row_tbl';
1109: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1110: i number := 0;
1111: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1112:
1113: BEGIN
1114: okl_api.init_msg_list(p_init_msg_list);
1115:
1110: i number := 0;
1111: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1112:
1113: BEGIN
1114: okl_api.init_msg_list(p_init_msg_list);
1115:
1116: -- Make sure PL/SQL table has records in it before passing
1117:
1118: IF (p_ech_tbl.COUNT > 0) THEN
1119: i := p_ech_tbl.FIRST;
1120:
1121: LOOP
1122: update_row(p_api_version => p_api_version
1123: ,p_init_msg_list => okl_api.g_false
1124: ,x_return_status => x_return_status
1125: ,x_msg_count => x_msg_count
1126: ,x_msg_data => x_msg_data
1127: ,p_ech_rec => p_ech_tbl(i)
1125: ,x_msg_count => x_msg_count
1126: ,x_msg_data => x_msg_data
1127: ,p_ech_rec => p_ech_tbl(i)
1128: ,x_ech_rec => x_ech_tbl(i));
1129: IF x_return_status <> okl_api.g_ret_sts_success THEN
1130: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1131: l_overall_status := x_return_status;
1132: END IF;
1133: END IF;
1126: ,x_msg_data => x_msg_data
1127: ,p_ech_rec => p_ech_tbl(i)
1128: ,x_ech_rec => x_ech_tbl(i));
1129: IF x_return_status <> okl_api.g_ret_sts_success THEN
1130: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1131: l_overall_status := x_return_status;
1132: END IF;
1133: END IF;
1134: EXIT WHEN(i = p_ech_tbl.LAST);
1142:
1143: -- No action necessary. Validation can continue to next attribute/column
1144:
1145: NULL;
1146: WHEN okl_api.g_exception_error THEN
1147: x_return_status := okl_api.handle_exceptions(l_api_name
1148: ,g_pkg_name
1149: ,'OKL_API.G_RET_STS_ERROR'
1150: ,x_msg_count
1143: -- No action necessary. Validation can continue to next attribute/column
1144:
1145: NULL;
1146: WHEN okl_api.g_exception_error THEN
1147: x_return_status := okl_api.handle_exceptions(l_api_name
1148: ,g_pkg_name
1149: ,'OKL_API.G_RET_STS_ERROR'
1150: ,x_msg_count
1151: ,x_msg_data
1145: NULL;
1146: WHEN okl_api.g_exception_error THEN
1147: x_return_status := okl_api.handle_exceptions(l_api_name
1148: ,g_pkg_name
1149: ,'OKL_API.G_RET_STS_ERROR'
1150: ,x_msg_count
1151: ,x_msg_data
1152: ,'_PVT');
1153: WHEN okl_api.g_exception_unexpected_error THEN
1149: ,'OKL_API.G_RET_STS_ERROR'
1150: ,x_msg_count
1151: ,x_msg_data
1152: ,'_PVT');
1153: WHEN okl_api.g_exception_unexpected_error THEN
1154: x_return_status := okl_api.handle_exceptions(l_api_name
1155: ,g_pkg_name
1156: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1157: ,x_msg_count
1150: ,x_msg_count
1151: ,x_msg_data
1152: ,'_PVT');
1153: WHEN okl_api.g_exception_unexpected_error THEN
1154: x_return_status := okl_api.handle_exceptions(l_api_name
1155: ,g_pkg_name
1156: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1157: ,x_msg_count
1158: ,x_msg_data
1152: ,'_PVT');
1153: WHEN okl_api.g_exception_unexpected_error THEN
1154: x_return_status := okl_api.handle_exceptions(l_api_name
1155: ,g_pkg_name
1156: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1157: ,x_msg_count
1158: ,x_msg_data
1159: ,'_PVT');
1160: WHEN OTHERS THEN
1157: ,x_msg_count
1158: ,x_msg_data
1159: ,'_PVT');
1160: WHEN OTHERS THEN
1161: x_return_status := okl_api.handle_exceptions(l_api_name
1162: ,g_pkg_name
1163: ,'OTHERS'
1164: ,x_msg_count
1165: ,x_msg_data
1170: -- Procedure delete_row
1171: --------------------------------------------------------------------------------
1172:
1173: PROCEDURE delete_row(p_api_version IN number
1174: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1175: ,x_return_status OUT NOCOPY varchar2
1176: ,x_msg_count OUT NOCOPY number
1177: ,x_msg_data OUT NOCOPY varchar2
1178: ,p_ech_rec IN okl_ech_rec) IS
1177: ,x_msg_data OUT NOCOPY varchar2
1178: ,p_ech_rec IN okl_ech_rec) IS
1179: l_api_version CONSTANT number := 1;
1180: l_api_name CONSTANT varchar2(30) := 'delete_row';
1181: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1182: l_ech_rec okl_ech_rec := p_ech_rec;
1183: l_row_notfound boolean := true;
1184:
1185: BEGIN
1182: l_ech_rec okl_ech_rec := p_ech_rec;
1183: l_row_notfound boolean := true;
1184:
1185: BEGIN
1186: l_return_status := okl_api.start_activity(l_api_name
1187: ,g_pkg_name
1188: ,p_init_msg_list
1189: ,l_api_version
1190: ,p_api_version
1190: ,p_api_version
1191: ,'_PVT'
1192: ,x_return_status);
1193:
1194: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1195: RAISE okl_api.g_exception_unexpected_error;
1196: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1197: RAISE okl_api.g_exception_error;
1198: END IF;
1191: ,'_PVT'
1192: ,x_return_status);
1193:
1194: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1195: RAISE okl_api.g_exception_unexpected_error;
1196: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1197: RAISE okl_api.g_exception_error;
1198: END IF;
1199:
1192: ,x_return_status);
1193:
1194: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1195: RAISE okl_api.g_exception_unexpected_error;
1196: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1197: RAISE okl_api.g_exception_error;
1198: END IF;
1199:
1200: DELETE FROM okl_fe_criteria_set
1193:
1194: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1195: RAISE okl_api.g_exception_unexpected_error;
1196: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1197: RAISE okl_api.g_exception_error;
1198: END IF;
1199:
1200: DELETE FROM okl_fe_criteria_set
1201: WHERE criteria_set_id = l_ech_rec.criteria_set_id;
1199:
1200: DELETE FROM okl_fe_criteria_set
1201: WHERE criteria_set_id = l_ech_rec.criteria_set_id;
1202: x_return_status := l_return_status;
1203: okl_api.end_activity(x_msg_count, x_msg_data);
1204: EXCEPTION
1205: WHEN g_exception_halt_validation THEN
1206:
1207: -- No action necessary. Validation can continue to next attribute/column
1206:
1207: -- No action necessary. Validation can continue to next attribute/column
1208:
1209: NULL;
1210: WHEN okl_api.g_exception_error THEN
1211: x_return_status := okl_api.handle_exceptions(l_api_name
1212: ,g_pkg_name
1213: ,'OKL_API.G_RET_STS_ERROR'
1214: ,x_msg_count
1207: -- No action necessary. Validation can continue to next attribute/column
1208:
1209: NULL;
1210: WHEN okl_api.g_exception_error THEN
1211: x_return_status := okl_api.handle_exceptions(l_api_name
1212: ,g_pkg_name
1213: ,'OKL_API.G_RET_STS_ERROR'
1214: ,x_msg_count
1215: ,x_msg_data
1209: NULL;
1210: WHEN okl_api.g_exception_error THEN
1211: x_return_status := okl_api.handle_exceptions(l_api_name
1212: ,g_pkg_name
1213: ,'OKL_API.G_RET_STS_ERROR'
1214: ,x_msg_count
1215: ,x_msg_data
1216: ,'_PVT');
1217: WHEN okl_api.g_exception_unexpected_error THEN
1213: ,'OKL_API.G_RET_STS_ERROR'
1214: ,x_msg_count
1215: ,x_msg_data
1216: ,'_PVT');
1217: WHEN okl_api.g_exception_unexpected_error THEN
1218: x_return_status := okl_api.handle_exceptions(l_api_name
1219: ,g_pkg_name
1220: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1221: ,x_msg_count
1214: ,x_msg_count
1215: ,x_msg_data
1216: ,'_PVT');
1217: WHEN okl_api.g_exception_unexpected_error THEN
1218: x_return_status := okl_api.handle_exceptions(l_api_name
1219: ,g_pkg_name
1220: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1221: ,x_msg_count
1222: ,x_msg_data
1216: ,'_PVT');
1217: WHEN okl_api.g_exception_unexpected_error THEN
1218: x_return_status := okl_api.handle_exceptions(l_api_name
1219: ,g_pkg_name
1220: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1221: ,x_msg_count
1222: ,x_msg_data
1223: ,'_PVT');
1224: WHEN OTHERS THEN
1221: ,x_msg_count
1222: ,x_msg_data
1223: ,'_PVT');
1224: WHEN OTHERS THEN
1225: x_return_status := okl_api.handle_exceptions(l_api_name
1226: ,g_pkg_name
1227: ,'OTHERS'
1228: ,x_msg_count
1229: ,x_msg_data
1234: -- Procedure delete_row_tbl
1235: --------------------------------------------------------------------------------
1236:
1237: PROCEDURE delete_row(p_api_version IN number
1238: ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1239: ,x_return_status OUT NOCOPY varchar2
1240: ,x_msg_count OUT NOCOPY number
1241: ,x_msg_data OUT NOCOPY varchar2
1242: ,p_ech_tbl IN okl_ech_tbl) IS
1241: ,x_msg_data OUT NOCOPY varchar2
1242: ,p_ech_tbl IN okl_ech_tbl) IS
1243: l_api_version CONSTANT number := 1;
1244: l_api_name CONSTANT varchar2(30) := 'delete_row_tbl';
1245: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1246: i number := 0;
1247: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1248:
1249: BEGIN
1243: l_api_version CONSTANT number := 1;
1244: l_api_name CONSTANT varchar2(30) := 'delete_row_tbl';
1245: l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1246: i number := 0;
1247: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1248:
1249: BEGIN
1250: okl_api.init_msg_list(p_init_msg_list);
1251:
1246: i number := 0;
1247: l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1248:
1249: BEGIN
1250: okl_api.init_msg_list(p_init_msg_list);
1251:
1252: -- Make sure PL/SQL table has records in it before passing
1253:
1254: IF (p_ech_tbl.COUNT > 0) THEN
1255: i := p_ech_tbl.FIRST;
1256:
1257: LOOP
1258: delete_row(p_api_version => p_api_version
1259: ,p_init_msg_list => okl_api.g_false
1260: ,x_return_status => x_return_status
1261: ,x_msg_count => x_msg_count
1262: ,x_msg_data => x_msg_data
1263: ,p_ech_rec => p_ech_tbl(i));
1260: ,x_return_status => x_return_status
1261: ,x_msg_count => x_msg_count
1262: ,x_msg_data => x_msg_data
1263: ,p_ech_rec => p_ech_tbl(i));
1264: IF x_return_status <> okl_api.g_ret_sts_success THEN
1265: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1266: l_overall_status := x_return_status;
1267: END IF;
1268: END IF;
1261: ,x_msg_count => x_msg_count
1262: ,x_msg_data => x_msg_data
1263: ,p_ech_rec => p_ech_tbl(i));
1264: IF x_return_status <> okl_api.g_ret_sts_success THEN
1265: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1266: l_overall_status := x_return_status;
1267: END IF;
1268: END IF;
1269: EXIT WHEN(i = p_ech_tbl.LAST);
1277:
1278: -- No action necessary. Validation can continue to next attribute/column
1279:
1280: NULL;
1281: WHEN okl_api.g_exception_error THEN
1282: x_return_status := okl_api.handle_exceptions(l_api_name
1283: ,g_pkg_name
1284: ,'OKL_API.G_RET_STS_ERROR'
1285: ,x_msg_count
1278: -- No action necessary. Validation can continue to next attribute/column
1279:
1280: NULL;
1281: WHEN okl_api.g_exception_error THEN
1282: x_return_status := okl_api.handle_exceptions(l_api_name
1283: ,g_pkg_name
1284: ,'OKL_API.G_RET_STS_ERROR'
1285: ,x_msg_count
1286: ,x_msg_data
1280: NULL;
1281: WHEN okl_api.g_exception_error THEN
1282: x_return_status := okl_api.handle_exceptions(l_api_name
1283: ,g_pkg_name
1284: ,'OKL_API.G_RET_STS_ERROR'
1285: ,x_msg_count
1286: ,x_msg_data
1287: ,'_PVT');
1288: WHEN okl_api.g_exception_unexpected_error THEN
1284: ,'OKL_API.G_RET_STS_ERROR'
1285: ,x_msg_count
1286: ,x_msg_data
1287: ,'_PVT');
1288: WHEN okl_api.g_exception_unexpected_error THEN
1289: x_return_status := okl_api.handle_exceptions(l_api_name
1290: ,g_pkg_name
1291: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1292: ,x_msg_count
1285: ,x_msg_count
1286: ,x_msg_data
1287: ,'_PVT');
1288: WHEN okl_api.g_exception_unexpected_error THEN
1289: x_return_status := okl_api.handle_exceptions(l_api_name
1290: ,g_pkg_name
1291: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1292: ,x_msg_count
1293: ,x_msg_data
1287: ,'_PVT');
1288: WHEN okl_api.g_exception_unexpected_error THEN
1289: x_return_status := okl_api.handle_exceptions(l_api_name
1290: ,g_pkg_name
1291: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1292: ,x_msg_count
1293: ,x_msg_data
1294: ,'_PVT');
1295: WHEN OTHERS THEN
1292: ,x_msg_count
1293: ,x_msg_data
1294: ,'_PVT');
1295: WHEN OTHERS THEN
1296: x_return_status := okl_api.handle_exceptions(l_api_name
1297: ,g_pkg_name
1298: ,'OTHERS'
1299: ,x_msg_count
1300: ,x_msg_data