111: l_pal_rec okl_pal_rec := p_pal_rec;
112:
113: BEGIN
114:
115: IF (l_pal_rec.adj_mat_version_id = okl_api.g_miss_num) THEN
116: l_pal_rec.adj_mat_version_id := NULL;
117: END IF;
118:
119: IF (l_pal_rec.object_version_number = okl_api.g_miss_num) THEN
115: IF (l_pal_rec.adj_mat_version_id = okl_api.g_miss_num) THEN
116: l_pal_rec.adj_mat_version_id := NULL;
117: END IF;
118:
119: IF (l_pal_rec.object_version_number = okl_api.g_miss_num) THEN
120: l_pal_rec.object_version_number := NULL;
121: END IF;
122:
123: IF (l_pal_rec.version_number = okl_api.g_miss_char) THEN
119: IF (l_pal_rec.object_version_number = okl_api.g_miss_num) THEN
120: l_pal_rec.object_version_number := NULL;
121: END IF;
122:
123: IF (l_pal_rec.version_number = okl_api.g_miss_char) THEN
124: l_pal_rec.version_number := NULL;
125: END IF;
126:
127: IF (l_pal_rec.adj_mat_id = okl_api.g_miss_num) THEN
123: IF (l_pal_rec.version_number = okl_api.g_miss_char) THEN
124: l_pal_rec.version_number := NULL;
125: END IF;
126:
127: IF (l_pal_rec.adj_mat_id = okl_api.g_miss_num) THEN
128: l_pal_rec.adj_mat_id := NULL;
129: END IF;
130:
131: IF (l_pal_rec.sts_code = okl_api.g_miss_char) THEN
127: IF (l_pal_rec.adj_mat_id = okl_api.g_miss_num) THEN
128: l_pal_rec.adj_mat_id := NULL;
129: END IF;
130:
131: IF (l_pal_rec.sts_code = okl_api.g_miss_char) THEN
132: l_pal_rec.sts_code := NULL;
133: END IF;
134:
135: IF (l_pal_rec.effective_from_date = okl_api.g_miss_date) THEN
131: IF (l_pal_rec.sts_code = okl_api.g_miss_char) THEN
132: l_pal_rec.sts_code := NULL;
133: END IF;
134:
135: IF (l_pal_rec.effective_from_date = okl_api.g_miss_date) THEN
136: l_pal_rec.effective_from_date := NULL;
137: END IF;
138:
139: IF (l_pal_rec.effective_to_date = okl_api.g_miss_date) THEN
135: IF (l_pal_rec.effective_from_date = okl_api.g_miss_date) THEN
136: l_pal_rec.effective_from_date := NULL;
137: END IF;
138:
139: IF (l_pal_rec.effective_to_date = okl_api.g_miss_date) THEN
140: l_pal_rec.effective_to_date := NULL;
141: END IF;
142:
143: IF (l_pal_rec.attribute_category = okl_api.g_miss_char) THEN
139: IF (l_pal_rec.effective_to_date = okl_api.g_miss_date) THEN
140: l_pal_rec.effective_to_date := NULL;
141: END IF;
142:
143: IF (l_pal_rec.attribute_category = okl_api.g_miss_char) THEN
144: l_pal_rec.attribute_category := NULL;
145: END IF;
146:
147: IF (l_pal_rec.attribute1 = okl_api.g_miss_char) THEN
143: IF (l_pal_rec.attribute_category = okl_api.g_miss_char) THEN
144: l_pal_rec.attribute_category := NULL;
145: END IF;
146:
147: IF (l_pal_rec.attribute1 = okl_api.g_miss_char) THEN
148: l_pal_rec.attribute1 := NULL;
149: END IF;
150:
151: IF (l_pal_rec.attribute2 = okl_api.g_miss_char) THEN
147: IF (l_pal_rec.attribute1 = okl_api.g_miss_char) THEN
148: l_pal_rec.attribute1 := NULL;
149: END IF;
150:
151: IF (l_pal_rec.attribute2 = okl_api.g_miss_char) THEN
152: l_pal_rec.attribute2 := NULL;
153: END IF;
154:
155: IF (l_pal_rec.attribute3 = okl_api.g_miss_char) THEN
151: IF (l_pal_rec.attribute2 = okl_api.g_miss_char) THEN
152: l_pal_rec.attribute2 := NULL;
153: END IF;
154:
155: IF (l_pal_rec.attribute3 = okl_api.g_miss_char) THEN
156: l_pal_rec.attribute3 := NULL;
157: END IF;
158:
159: IF (l_pal_rec.attribute4 = okl_api.g_miss_char) THEN
155: IF (l_pal_rec.attribute3 = okl_api.g_miss_char) THEN
156: l_pal_rec.attribute3 := NULL;
157: END IF;
158:
159: IF (l_pal_rec.attribute4 = okl_api.g_miss_char) THEN
160: l_pal_rec.attribute4 := NULL;
161: END IF;
162:
163: IF (l_pal_rec.attribute5 = okl_api.g_miss_char) THEN
159: IF (l_pal_rec.attribute4 = okl_api.g_miss_char) THEN
160: l_pal_rec.attribute4 := NULL;
161: END IF;
162:
163: IF (l_pal_rec.attribute5 = okl_api.g_miss_char) THEN
164: l_pal_rec.attribute5 := NULL;
165: END IF;
166:
167: IF (l_pal_rec.attribute6 = okl_api.g_miss_char) THEN
163: IF (l_pal_rec.attribute5 = okl_api.g_miss_char) THEN
164: l_pal_rec.attribute5 := NULL;
165: END IF;
166:
167: IF (l_pal_rec.attribute6 = okl_api.g_miss_char) THEN
168: l_pal_rec.attribute6 := NULL;
169: END IF;
170:
171: IF (l_pal_rec.attribute7 = okl_api.g_miss_char) THEN
167: IF (l_pal_rec.attribute6 = okl_api.g_miss_char) THEN
168: l_pal_rec.attribute6 := NULL;
169: END IF;
170:
171: IF (l_pal_rec.attribute7 = okl_api.g_miss_char) THEN
172: l_pal_rec.attribute7 := NULL;
173: END IF;
174:
175: IF (l_pal_rec.attribute8 = okl_api.g_miss_char) THEN
171: IF (l_pal_rec.attribute7 = okl_api.g_miss_char) THEN
172: l_pal_rec.attribute7 := NULL;
173: END IF;
174:
175: IF (l_pal_rec.attribute8 = okl_api.g_miss_char) THEN
176: l_pal_rec.attribute8 := NULL;
177: END IF;
178:
179: IF (l_pal_rec.attribute9 = okl_api.g_miss_char) THEN
175: IF (l_pal_rec.attribute8 = okl_api.g_miss_char) THEN
176: l_pal_rec.attribute8 := NULL;
177: END IF;
178:
179: IF (l_pal_rec.attribute9 = okl_api.g_miss_char) THEN
180: l_pal_rec.attribute9 := NULL;
181: END IF;
182:
183: IF (l_pal_rec.attribute10 = okl_api.g_miss_char) THEN
179: IF (l_pal_rec.attribute9 = okl_api.g_miss_char) THEN
180: l_pal_rec.attribute9 := NULL;
181: END IF;
182:
183: IF (l_pal_rec.attribute10 = okl_api.g_miss_char) THEN
184: l_pal_rec.attribute10 := NULL;
185: END IF;
186:
187: IF (l_pal_rec.attribute11 = okl_api.g_miss_char) THEN
183: IF (l_pal_rec.attribute10 = okl_api.g_miss_char) THEN
184: l_pal_rec.attribute10 := NULL;
185: END IF;
186:
187: IF (l_pal_rec.attribute11 = okl_api.g_miss_char) THEN
188: l_pal_rec.attribute11 := NULL;
189: END IF;
190:
191: IF (l_pal_rec.attribute12 = okl_api.g_miss_char) THEN
187: IF (l_pal_rec.attribute11 = okl_api.g_miss_char) THEN
188: l_pal_rec.attribute11 := NULL;
189: END IF;
190:
191: IF (l_pal_rec.attribute12 = okl_api.g_miss_char) THEN
192: l_pal_rec.attribute12 := NULL;
193: END IF;
194:
195: IF (l_pal_rec.attribute13 = okl_api.g_miss_char) THEN
191: IF (l_pal_rec.attribute12 = okl_api.g_miss_char) THEN
192: l_pal_rec.attribute12 := NULL;
193: END IF;
194:
195: IF (l_pal_rec.attribute13 = okl_api.g_miss_char) THEN
196: l_pal_rec.attribute13 := NULL;
197: END IF;
198:
199: IF (l_pal_rec.attribute14 = okl_api.g_miss_char) THEN
195: IF (l_pal_rec.attribute13 = okl_api.g_miss_char) THEN
196: l_pal_rec.attribute13 := NULL;
197: END IF;
198:
199: IF (l_pal_rec.attribute14 = okl_api.g_miss_char) THEN
200: l_pal_rec.attribute14 := NULL;
201: END IF;
202:
203: IF (l_pal_rec.attribute15 = okl_api.g_miss_char) THEN
199: IF (l_pal_rec.attribute14 = okl_api.g_miss_char) THEN
200: l_pal_rec.attribute14 := NULL;
201: END IF;
202:
203: IF (l_pal_rec.attribute15 = okl_api.g_miss_char) THEN
204: l_pal_rec.attribute15 := NULL;
205: END IF;
206:
207: IF (l_pal_rec.created_by = okl_api.g_miss_num) THEN
203: IF (l_pal_rec.attribute15 = okl_api.g_miss_char) THEN
204: l_pal_rec.attribute15 := NULL;
205: END IF;
206:
207: IF (l_pal_rec.created_by = okl_api.g_miss_num) THEN
208: l_pal_rec.created_by := NULL;
209: END IF;
210:
211: IF (l_pal_rec.creation_date = okl_api.g_miss_date) THEN
207: IF (l_pal_rec.created_by = okl_api.g_miss_num) THEN
208: l_pal_rec.created_by := NULL;
209: END IF;
210:
211: IF (l_pal_rec.creation_date = okl_api.g_miss_date) THEN
212: l_pal_rec.creation_date := NULL;
213: END IF;
214:
215: IF (l_pal_rec.last_updated_by = okl_api.g_miss_num) THEN
211: IF (l_pal_rec.creation_date = okl_api.g_miss_date) THEN
212: l_pal_rec.creation_date := NULL;
213: END IF;
214:
215: IF (l_pal_rec.last_updated_by = okl_api.g_miss_num) THEN
216: l_pal_rec.last_updated_by := NULL;
217: END IF;
218:
219: IF (l_pal_rec.last_update_date = okl_api.g_miss_date) THEN
215: IF (l_pal_rec.last_updated_by = okl_api.g_miss_num) THEN
216: l_pal_rec.last_updated_by := NULL;
217: END IF;
218:
219: IF (l_pal_rec.last_update_date = okl_api.g_miss_date) THEN
220: l_pal_rec.last_update_date := NULL;
221: END IF;
222:
223: IF (l_pal_rec.last_update_login = okl_api.g_miss_num) THEN
219: IF (l_pal_rec.last_update_date = okl_api.g_miss_date) THEN
220: l_pal_rec.last_update_date := NULL;
221: END IF;
222:
223: IF (l_pal_rec.last_update_login = okl_api.g_miss_num) THEN
224: l_pal_rec.last_update_login := NULL;
225: END IF;
226: RETURN(l_pal_rec);
227: END null_out_defaults;
235: FUNCTION validate_adj_mat_version_id(p_adj_mat_version_id IN NUMBER) RETURN VARCHAR2 IS
236:
237: -- initialize the return status
238:
239: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
240:
241: BEGIN
242:
243: -- Version Id is a required field
241: BEGIN
242:
243: -- Version Id is a required field
244:
245: IF (p_adj_mat_version_id IS NULL OR p_adj_mat_version_id = okl_api.g_miss_num) THEN
246: okl_api.set_message(p_app_name => g_app_name
247: ,p_msg_name => g_required_value
248: ,p_token1 => g_col_name_token
249: ,p_token1_value => 'ADJ_MAT_VERSION_ID');
242:
243: -- Version Id is a required field
244:
245: IF (p_adj_mat_version_id IS NULL OR p_adj_mat_version_id = okl_api.g_miss_num) THEN
246: okl_api.set_message(p_app_name => g_app_name
247: ,p_msg_name => g_required_value
248: ,p_token1 => g_col_name_token
249: ,p_token1_value => 'ADJ_MAT_VERSION_ID');
250:
249: ,p_token1_value => 'ADJ_MAT_VERSION_ID');
250:
251: -- notify caller of an error
252:
253: x_return_status := okl_api.g_ret_sts_error;
254:
255: -- halt further validation of this column
256:
257: RAISE g_exception_halt_validation;
266: WHEN OTHERS THEN
267:
268: -- store SQL error message on message stack for caller
269:
270: okl_api.set_message(p_app_name => g_app_name
271: ,p_msg_name => g_unexpected_error
272: ,p_token1 => g_sqlcode_token
273: ,p_token1_value => sqlcode
274: ,p_token2 => g_sqlerrm_token
275: ,p_token2_value => sqlerrm);
276:
277: -- notify caller of an UNEXPECTED error
278:
279: x_return_status := okl_api.g_ret_sts_unexp_error;
280: RETURN x_return_status;
281: END validate_adj_mat_version_id;
282:
283: FUNCTION validate_version_number(p_version_number IN VARCHAR2) RETURN VARCHAR2 IS
280: RETURN x_return_status;
281: END validate_adj_mat_version_id;
282:
283: FUNCTION validate_version_number(p_version_number IN VARCHAR2) RETURN VARCHAR2 IS
284: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
285:
286: BEGIN
287:
288: -- Version Number is a required field
286: BEGIN
287:
288: -- Version Number is a required field
289:
290: IF (p_version_number IS NULL OR p_version_number = okl_api.g_miss_char) THEN
291: okl_api.set_message(p_app_name => g_app_name
292: ,p_msg_name => g_required_value
293: ,p_token1 => g_col_name_token
294: ,p_token1_value => 'VERSION_NUMBER');
287:
288: -- Version Number is a required field
289:
290: IF (p_version_number IS NULL OR p_version_number = okl_api.g_miss_char) THEN
291: okl_api.set_message(p_app_name => g_app_name
292: ,p_msg_name => g_required_value
293: ,p_token1 => g_col_name_token
294: ,p_token1_value => 'VERSION_NUMBER');
295:
294: ,p_token1_value => 'VERSION_NUMBER');
295:
296: -- notify caller of an error
297:
298: x_return_status := okl_api.g_ret_sts_error;
299:
300: -- halt further validation of this column
301:
302: RAISE g_exception_halt_validation;
311: WHEN OTHERS THEN
312:
313: -- store SQL error message on message stack for caller
314:
315: okl_api.set_message(p_app_name => g_app_name
316: ,p_msg_name => g_unexpected_error
317: ,p_token1 => g_sqlcode_token
318: ,p_token1_value => sqlcode
319: ,p_token2 => g_sqlerrm_token
320: ,p_token2_value => sqlerrm);
321:
322: -- notify caller of an UNEXPECTED error
323:
324: x_return_status := okl_api.g_ret_sts_unexp_error;
325: RETURN x_return_status;
326: END validate_version_number;
327:
328: -- validation of Adjusment Matrix Id
327:
328: -- validation of Adjusment Matrix Id
329:
330: FUNCTION validate_adj_mat_id(p_adj_mat_id IN NUMBER) RETURN VARCHAR2 IS
331: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
332:
333: BEGIN
334:
335: -- ADJ_MAT_ID is a required field
333: BEGIN
334:
335: -- ADJ_MAT_ID is a required field
336:
337: IF (p_adj_mat_id IS NULL OR p_adj_mat_id = okl_api.g_miss_num) THEN
338: okl_api.set_message(p_app_name => g_app_name
339: ,p_msg_name => g_required_value
340: ,p_token1 => g_col_name_token
341: ,p_token1_value => 'ADJ_MAT_ID');
334:
335: -- ADJ_MAT_ID is a required field
336:
337: IF (p_adj_mat_id IS NULL OR p_adj_mat_id = okl_api.g_miss_num) THEN
338: okl_api.set_message(p_app_name => g_app_name
339: ,p_msg_name => g_required_value
340: ,p_token1 => g_col_name_token
341: ,p_token1_value => 'ADJ_MAT_ID');
342:
341: ,p_token1_value => 'ADJ_MAT_ID');
342:
343: -- notify caller of an error
344:
345: l_return_status := okl_api.g_ret_sts_error;
346:
347: -- halt further validation of this column
348:
349: RAISE g_exception_halt_validation;
353: WHEN g_exception_halt_validation THEN
354:
355: -- no proccessing required. Validation can continue with the next column
356:
357: RETURN okl_api.g_ret_sts_error;
358: WHEN OTHERS THEN
359:
360: -- store SQL error message on message stack for caller
361:
358: WHEN OTHERS THEN
359:
360: -- store SQL error message on message stack for caller
361:
362: okl_api.set_message(p_app_name => g_app_name
363: ,p_msg_name => g_unexpected_error
364: ,p_token1 => g_sqlcode_token
365: ,p_token1_value => sqlcode
366: ,p_token2 => g_sqlerrm_token
367: ,p_token2_value => sqlerrm);
368:
369: -- notify caller of an UNEXPECTED error
370:
371: l_return_status := okl_api.g_ret_sts_unexp_error;
372: RETURN(l_return_status);
373: END validate_adj_mat_id;
374:
375: -- validation of the status code
377: FUNCTION validate_sts_code(p_sts_code IN VARCHAR2) RETURN VARCHAR2 IS
378:
379: --initialize the Return Status
380:
381: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
382:
383: BEGIN
384:
385: -- Column is mandatory
383: BEGIN
384:
385: -- Column is mandatory
386:
387: IF (p_sts_code IS NULL OR p_sts_code = okl_api.g_miss_char) THEN
388: okl_api.set_message(p_app_name => g_app_name
389: ,p_msg_name => g_required_value
390: ,p_token1 => g_col_name_token
391: ,p_token1_value => 'sts_code');
384:
385: -- Column is mandatory
386:
387: IF (p_sts_code IS NULL OR p_sts_code = okl_api.g_miss_char) THEN
388: okl_api.set_message(p_app_name => g_app_name
389: ,p_msg_name => g_required_value
390: ,p_token1 => g_col_name_token
391: ,p_token1_value => 'sts_code');
392:
391: ,p_token1_value => 'sts_code');
392:
393: -- notify caller of an error
394:
395: x_return_status := okl_api.g_ret_sts_error;
396: RAISE g_exception_halt_validation;
397: END IF;
398:
399: -- Lookup Code Validation
400:
401: x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
402: ,p_lookup_code => p_sts_code);
403:
404: IF (x_return_status = okl_api.g_ret_sts_error) THEN
405: okl_api.set_message(p_app_name => g_app_name
406: ,p_msg_name => g_invalid_value
407: ,p_token1 => g_col_name_token
408: ,p_token1_value => 'sts_code'); -- notify caller of an error
401: x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
402: ,p_lookup_code => p_sts_code);
403:
404: IF (x_return_status = okl_api.g_ret_sts_error) THEN
405: okl_api.set_message(p_app_name => g_app_name
406: ,p_msg_name => g_invalid_value
407: ,p_token1 => g_col_name_token
408: ,p_token1_value => 'sts_code'); -- notify caller of an error
409: RAISE g_exception_halt_validation;
406: ,p_msg_name => g_invalid_value
407: ,p_token1 => g_col_name_token
408: ,p_token1_value => 'sts_code'); -- notify caller of an error
409: RAISE g_exception_halt_validation;
410: ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
411:
412: -- notify caller of an error
413:
414: x_return_status := okl_api.g_ret_sts_unexp_error;
410: ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
411:
412: -- notify caller of an error
413:
414: x_return_status := okl_api.g_ret_sts_unexp_error;
415: RAISE g_exception_halt_validation;
416: END IF;
417: RETURN x_return_status;
418: EXCEPTION
425: WHEN OTHERS THEN
426:
427: -- store SQL error message on message stack for caller
428:
429: okl_api.set_message(p_app_name => g_app_name
430: ,p_msg_name => g_unexpected_error
431: ,p_token1 => g_sqlcode_token
432: ,p_token1_value => sqlcode
433: ,p_token2 => g_sqlerrm_token
434: ,p_token2_value => sqlerrm);
435:
436: -- notify caller of an UNEXPECTED error
437:
438: x_return_status := okl_api.g_ret_sts_unexp_error;
439: RETURN x_return_status;
440: END validate_sts_code;
441:
442: -- validation of the effective from
444: FUNCTION validate_effective_from_date(p_effective_from_date IN DATE) RETURN VARCHAR2 IS
445:
446: -- initialize the return status
447:
448: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
449:
450: BEGIN
451:
452: IF (p_effective_from_date IS NULL OR p_effective_from_date = okl_api.g_miss_date) THEN
448: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
449:
450: BEGIN
451:
452: IF (p_effective_from_date IS NULL OR p_effective_from_date = okl_api.g_miss_date) THEN
453: okl_api.set_message(p_app_name => g_app_name
454: ,p_msg_name => g_required_value
455: ,p_token1 => g_col_name_token
456: ,p_token1_value => 'EFFECTIVE_FROM_DATE');
449:
450: BEGIN
451:
452: IF (p_effective_from_date IS NULL OR p_effective_from_date = okl_api.g_miss_date) THEN
453: okl_api.set_message(p_app_name => g_app_name
454: ,p_msg_name => g_required_value
455: ,p_token1 => g_col_name_token
456: ,p_token1_value => 'EFFECTIVE_FROM_DATE');
457:
456: ,p_token1_value => 'EFFECTIVE_FROM_DATE');
457:
458: -- notify caller of an error
459:
460: x_return_status := okl_api.g_ret_sts_error;
461: RAISE g_exception_halt_validation;
462: END IF;
463: RETURN x_return_status;
464: EXCEPTION
471: WHEN OTHERS THEN
472:
473: -- store SQL error message on message stack for caller
474:
475: okl_api.set_message(p_app_name => g_app_name
476: ,p_msg_name => g_unexpected_error
477: ,p_token1 => g_sqlcode_token
478: ,p_token1_value => sqlcode
479: ,p_token2 => g_sqlerrm_token
480: ,p_token2_value => sqlerrm);
481:
482: -- notify caller of an UNEXPECTED error
483:
484: x_return_status := okl_api.g_ret_sts_unexp_error;
485: RETURN x_return_status;
486: END validate_effective_from_date;
487:
488: FUNCTION validate_attributes(p_pal_rec IN okl_pal_rec) RETURN VARCHAR2 IS
485: RETURN x_return_status;
486: END validate_effective_from_date;
487:
488: FUNCTION validate_attributes(p_pal_rec IN okl_pal_rec) RETURN VARCHAR2 IS
489: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
490: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
491:
492: BEGIN
493:
486: END validate_effective_from_date;
487:
488: FUNCTION validate_attributes(p_pal_rec IN okl_pal_rec) RETURN VARCHAR2 IS
489: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
490: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
491:
492: BEGIN
493:
494: -- validate the adjustment matrix version id
496: l_return_status := validate_adj_mat_version_id(p_pal_rec.adj_mat_version_id);
497:
498: -- store the highest degree of error
499:
500: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
501: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
502: x_return_status := l_return_status;
503: END IF;
504: END IF;
497:
498: -- store the highest degree of error
499:
500: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
501: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
502: x_return_status := l_return_status;
503: END IF;
504: END IF;
505:
508: l_return_status := validate_adj_mat_id(p_pal_rec.adj_mat_id);
509:
510: -- store the highest degree of error
511:
512: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
513: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
514: x_return_status := l_return_status;
515: END IF;
516: END IF;
509:
510: -- store the highest degree of error
511:
512: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
513: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
514: x_return_status := l_return_status;
515: END IF;
516: END IF;
517:
520: l_return_status := validate_sts_code(p_pal_rec.sts_code);
521:
522: -- store the highest degree of error
523:
524: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
525: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
526: x_return_status := l_return_status;
527: END IF;
528: END IF;
521:
522: -- store the highest degree of error
523:
524: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
525: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
526: x_return_status := l_return_status;
527: END IF;
528: END IF;
529:
532: l_return_status := validate_effective_from_date(p_pal_rec.effective_from_date);
533:
534: -- store the highest degree of error
535:
536: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
537: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
538: x_return_status := l_return_status;
539: END IF;
540: END IF;
533:
534: -- store the highest degree of error
535:
536: IF (l_return_status <> okl_api.g_ret_sts_success) THEN
537: IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
538: x_return_status := l_return_status;
539: END IF;
540: END IF;
541: RETURN(x_return_status);
541: RETURN(x_return_status);
542: END validate_attributes;
543:
544: FUNCTION validate_record(p_pal_rec IN okl_pal_rec) RETURN VARCHAR2 IS
545: x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
546:
547: BEGIN
548:
549: IF (p_pal_rec.effective_to_date IS NOT NULL) THEN
547: BEGIN
548:
549: IF (p_pal_rec.effective_to_date IS NOT NULL) THEN
550: IF (p_pal_rec.effective_from_date > p_pal_rec.effective_to_date) THEN
551: okl_api.set_message(p_app_name => g_app_name
552: ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
553: x_return_status := okl_api.g_ret_sts_error;
554: RAISE g_exception_halt_validation;
555: END IF;
549: IF (p_pal_rec.effective_to_date IS NOT NULL) THEN
550: IF (p_pal_rec.effective_from_date > p_pal_rec.effective_to_date) THEN
551: okl_api.set_message(p_app_name => g_app_name
552: ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
553: x_return_status := okl_api.g_ret_sts_error;
554: RAISE g_exception_halt_validation;
555: END IF;
556: END IF;
557: RETURN x_return_status;
561: WHEN OTHERS THEN
562:
563: -- store SQL error message on message stack for caller
564:
565: okl_api.set_message(p_app_name => g_app_name
566: ,p_msg_name => g_unexpected_error
567: ,p_token1 => g_sqlcode_token
568: ,p_token1_value => sqlcode
569: ,p_token2 => g_sqlerrm_token
570: ,p_token2_value => sqlerrm);
571:
572: -- notify caller of an UNEXPECTED error
573:
574: x_return_status := okl_api.g_ret_sts_error;
575: RETURN(x_return_status);
576: END validate_record;
577:
578: -----------------
579: -- lock_row (REC)
580: -----------------
581:
582: PROCEDURE lock_row(p_api_version IN NUMBER
583: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
584: ,x_return_status OUT NOCOPY VARCHAR2
585: ,x_msg_count OUT NOCOPY NUMBER
586: ,x_msg_data OUT NOCOPY VARCHAR2
587: ,p_def_pal_rec IN okl_pal_rec) IS
600: CURSOR lchk_csr(p_def_pal_rec IN okl_pal_rec) IS
601: SELECT object_version_number
602: FROM okl_fe_adj_mat_versions
603: WHERE adj_mat_version_id = p_def_pal_rec.adj_mat_version_id;
604: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
605: l_object_version_number okl_fe_item_residual_all.object_version_number%TYPE;
606: lc_object_version_number okl_fe_item_residual_all.object_version_number%TYPE;
607: l_row_notfound BOOLEAN := false;
608: lc_row_notfound BOOLEAN := false;
619:
620: IF (lock_csr%ISOPEN) THEN
621: CLOSE lock_csr;
622: END IF;
623: okl_api.set_message(g_fnd_app
624: ,g_form_unable_to_reserve_rec);
625: RAISE app_exceptions.record_lock_exception;
626: END;
627:
632: CLOSE lchk_csr;
633: END IF;
634:
635: IF (lc_row_notfound) THEN
636: okl_api.set_message(g_fnd_app
637: ,g_form_record_deleted);
638: RAISE okl_api.g_exception_error;
639: ELSIF lc_object_version_number > p_def_pal_rec.object_version_number THEN
640: okl_api.set_message(g_fnd_app
634:
635: IF (lc_row_notfound) THEN
636: okl_api.set_message(g_fnd_app
637: ,g_form_record_deleted);
638: RAISE okl_api.g_exception_error;
639: ELSIF lc_object_version_number > p_def_pal_rec.object_version_number THEN
640: okl_api.set_message(g_fnd_app
641: ,g_form_record_changed);
642: RAISE okl_api.g_exception_error;
636: okl_api.set_message(g_fnd_app
637: ,g_form_record_deleted);
638: RAISE okl_api.g_exception_error;
639: ELSIF lc_object_version_number > p_def_pal_rec.object_version_number THEN
640: okl_api.set_message(g_fnd_app
641: ,g_form_record_changed);
642: RAISE okl_api.g_exception_error;
643: ELSIF lc_object_version_number <> p_def_pal_rec.object_version_number THEN
644: okl_api.set_message(g_fnd_app
638: RAISE okl_api.g_exception_error;
639: ELSIF lc_object_version_number > p_def_pal_rec.object_version_number THEN
640: okl_api.set_message(g_fnd_app
641: ,g_form_record_changed);
642: RAISE okl_api.g_exception_error;
643: ELSIF lc_object_version_number <> p_def_pal_rec.object_version_number THEN
644: okl_api.set_message(g_fnd_app
645: ,g_form_record_changed);
646: RAISE okl_api.g_exception_error;
640: okl_api.set_message(g_fnd_app
641: ,g_form_record_changed);
642: RAISE okl_api.g_exception_error;
643: ELSIF lc_object_version_number <> p_def_pal_rec.object_version_number THEN
644: okl_api.set_message(g_fnd_app
645: ,g_form_record_changed);
646: RAISE okl_api.g_exception_error;
647: ELSIF lc_object_version_number = - 1 THEN
648: okl_api.set_message(g_app_name
642: RAISE okl_api.g_exception_error;
643: ELSIF lc_object_version_number <> p_def_pal_rec.object_version_number THEN
644: okl_api.set_message(g_fnd_app
645: ,g_form_record_changed);
646: RAISE okl_api.g_exception_error;
647: ELSIF lc_object_version_number = - 1 THEN
648: okl_api.set_message(g_app_name
649: ,g_record_logically_deleted);
650: RAISE okl_api.g_exception_error;
644: okl_api.set_message(g_fnd_app
645: ,g_form_record_changed);
646: RAISE okl_api.g_exception_error;
647: ELSIF lc_object_version_number = - 1 THEN
648: okl_api.set_message(g_app_name
649: ,g_record_logically_deleted);
650: RAISE okl_api.g_exception_error;
651: END IF;
652: x_return_status := l_return_status;
646: RAISE okl_api.g_exception_error;
647: ELSIF lc_object_version_number = - 1 THEN
648: okl_api.set_message(g_app_name
649: ,g_record_logically_deleted);
650: RAISE okl_api.g_exception_error;
651: END IF;
652: x_return_status := l_return_status;
653: EXCEPTION
654: WHEN okl_api.g_exception_error THEN
650: RAISE okl_api.g_exception_error;
651: END IF;
652: x_return_status := l_return_status;
653: EXCEPTION
654: WHEN okl_api.g_exception_error THEN
655: x_return_status := g_ret_sts_error;
656: WHEN okl_api.g_exception_unexpected_error THEN
657: x_return_status := g_ret_sts_unexp_error;
658: WHEN OTHERS THEN
652: x_return_status := l_return_status;
653: EXCEPTION
654: WHEN okl_api.g_exception_error THEN
655: x_return_status := g_ret_sts_error;
656: WHEN okl_api.g_exception_unexpected_error THEN
657: x_return_status := g_ret_sts_unexp_error;
658: WHEN OTHERS THEN
659: okl_api.set_message(p_app_name => g_app_name
660: ,p_msg_name => g_db_error
655: x_return_status := g_ret_sts_error;
656: WHEN okl_api.g_exception_unexpected_error THEN
657: x_return_status := g_ret_sts_unexp_error;
658: WHEN OTHERS THEN
659: okl_api.set_message(p_app_name => g_app_name
660: ,p_msg_name => g_db_error
661: ,p_token1 => g_prog_name_token
662: ,p_token1_value => l_api_name
663: ,p_token2 => g_sqlcode_token
671: -- lock_row (TBL)
672: -----------------
673:
674: PROCEDURE lock_row(p_api_version IN NUMBER
675: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
676: ,x_return_status OUT NOCOPY VARCHAR2
677: ,x_msg_count OUT NOCOPY NUMBER
678: ,x_msg_data OUT NOCOPY VARCHAR2
679: ,okl_pal_tbl IN okl_pal_tbl) IS
694: ,x_msg_count => x_msg_count
695: ,x_msg_data => x_msg_data
696: ,p_def_pal_rec => okl_pal_tbl(i));
697: IF l_return_status = g_ret_sts_unexp_error THEN
698: RAISE okl_api.g_exception_unexpected_error;
699: ELSIF l_return_status = g_ret_sts_error THEN
700: RAISE okl_api.g_exception_error;
701: END IF;
702: EXIT WHEN(i = okl_pal_tbl.LAST);
696: ,p_def_pal_rec => okl_pal_tbl(i));
697: IF l_return_status = g_ret_sts_unexp_error THEN
698: RAISE okl_api.g_exception_unexpected_error;
699: ELSIF l_return_status = g_ret_sts_error THEN
700: RAISE okl_api.g_exception_error;
701: END IF;
702: EXIT WHEN(i = okl_pal_tbl.LAST);
703: i := okl_pal_tbl.next(i);
704: END IF;
706:
707: END IF;
708: x_return_status := l_return_status;
709: EXCEPTION
710: WHEN okl_api.g_exception_error THEN
711: x_return_status := g_ret_sts_error;
712: WHEN okl_api.g_exception_unexpected_error THEN
713: x_return_status := g_ret_sts_unexp_error;
714: WHEN OTHERS THEN
708: x_return_status := l_return_status;
709: EXCEPTION
710: WHEN okl_api.g_exception_error THEN
711: x_return_status := g_ret_sts_error;
712: WHEN okl_api.g_exception_unexpected_error THEN
713: x_return_status := g_ret_sts_unexp_error;
714: WHEN OTHERS THEN
715: okl_api.set_message(p_app_name => g_app_name
716: ,p_msg_name => g_db_error
711: x_return_status := g_ret_sts_error;
712: WHEN okl_api.g_exception_unexpected_error THEN
713: x_return_status := g_ret_sts_unexp_error;
714: WHEN OTHERS THEN
715: okl_api.set_message(p_app_name => g_app_name
716: ,p_msg_name => g_db_error
717: ,p_token1 => g_prog_name_token
718: ,p_token1_value => l_api_name
719: ,p_token2 => g_sqlcode_token
727: -- Procedure insert_row
728: --------------------------------------------------------------------------------
729:
730: PROCEDURE insert_row(p_api_version IN NUMBER
731: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
732: ,x_return_status OUT NOCOPY VARCHAR2
733: ,x_msg_count OUT NOCOPY NUMBER
734: ,x_msg_data OUT NOCOPY VARCHAR2
735: ,p_pal_rec IN okl_pal_rec
735: ,p_pal_rec IN okl_pal_rec
736: ,x_pal_rec OUT NOCOPY okl_pal_rec) IS
737: l_api_version CONSTANT NUMBER := 1;
738: l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
739: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
740: l_pal_rec okl_pal_rec;
741: lx_pal_rec okl_pal_rec;
742: l_def_pal_rec okl_pal_rec;
743:
754: END fill_who_columns;
755:
756: FUNCTION set_attributes(p_pal_rec IN okl_pal_rec
757: ,x_pal_rec OUT NOCOPY okl_pal_rec) RETURN VARCHAR2 IS
758: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
759:
760: BEGIN
761: x_pal_rec := p_pal_rec;
762: x_pal_rec.object_version_number := 1;
763: RETURN(l_return_status);
764: END set_attributes;
765:
766: BEGIN
767: l_return_status := okl_api.start_activity(l_api_name
768: ,g_pkg_name
769: ,p_init_msg_list
770: ,l_api_version
771: ,p_api_version
771: ,p_api_version
772: ,'_PVT'
773: ,x_return_status);
774:
775: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
776: RAISE okl_api.g_exception_unexpected_error;
777: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
778: RAISE okl_api.g_exception_error;
779: END IF;
772: ,'_PVT'
773: ,x_return_status);
774:
775: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
776: RAISE okl_api.g_exception_unexpected_error;
777: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
778: RAISE okl_api.g_exception_error;
779: END IF;
780: l_pal_rec := null_out_defaults(p_pal_rec);
773: ,x_return_status);
774:
775: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
776: RAISE okl_api.g_exception_unexpected_error;
777: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
778: RAISE okl_api.g_exception_error;
779: END IF;
780: l_pal_rec := null_out_defaults(p_pal_rec);
781:
774:
775: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
776: RAISE okl_api.g_exception_unexpected_error;
777: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
778: RAISE okl_api.g_exception_error;
779: END IF;
780: l_pal_rec := null_out_defaults(p_pal_rec);
781:
782: -- Set Primary key value
787:
788: l_return_status := set_attributes(l_pal_rec
789: ,l_def_pal_rec);
790:
791: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
792: RAISE okl_api.g_exception_unexpected_error;
793: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
794: RAISE okl_api.g_exception_error;
795: END IF;
788: l_return_status := set_attributes(l_pal_rec
789: ,l_def_pal_rec);
790:
791: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
792: RAISE okl_api.g_exception_unexpected_error;
793: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
794: RAISE okl_api.g_exception_error;
795: END IF;
796: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
789: ,l_def_pal_rec);
790:
791: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
792: RAISE okl_api.g_exception_unexpected_error;
793: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
794: RAISE okl_api.g_exception_error;
795: END IF;
796: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
797: l_return_status := validate_attributes(l_def_pal_rec);
790:
791: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
792: RAISE okl_api.g_exception_unexpected_error;
793: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
794: RAISE okl_api.g_exception_error;
795: END IF;
796: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
797: l_return_status := validate_attributes(l_def_pal_rec);
798:
795: END IF;
796: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
797: l_return_status := validate_attributes(l_def_pal_rec);
798:
799: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
800: RAISE okl_api.g_exception_unexpected_error;
801: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
802: RAISE okl_api.g_exception_error;
803: END IF;
796: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
797: l_return_status := validate_attributes(l_def_pal_rec);
798:
799: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
800: RAISE okl_api.g_exception_unexpected_error;
801: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
802: RAISE okl_api.g_exception_error;
803: END IF;
804: l_return_status := validate_record(l_def_pal_rec);
797: l_return_status := validate_attributes(l_def_pal_rec);
798:
799: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
800: RAISE okl_api.g_exception_unexpected_error;
801: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
802: RAISE okl_api.g_exception_error;
803: END IF;
804: l_return_status := validate_record(l_def_pal_rec);
805:
798:
799: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
800: RAISE okl_api.g_exception_unexpected_error;
801: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
802: RAISE okl_api.g_exception_error;
803: END IF;
804: l_return_status := validate_record(l_def_pal_rec);
805:
806: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
802: RAISE okl_api.g_exception_error;
803: END IF;
804: l_return_status := validate_record(l_def_pal_rec);
805:
806: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
807: RAISE okl_api.g_exception_unexpected_error;
808: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
809: RAISE okl_api.g_exception_error;
810: END IF;
803: END IF;
804: l_return_status := validate_record(l_def_pal_rec);
805:
806: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
807: RAISE okl_api.g_exception_unexpected_error;
808: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
809: RAISE okl_api.g_exception_error;
810: END IF;
811:
804: l_return_status := validate_record(l_def_pal_rec);
805:
806: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
807: RAISE okl_api.g_exception_unexpected_error;
808: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
809: RAISE okl_api.g_exception_error;
810: END IF;
811:
812: INSERT INTO okl_fe_adj_mat_versions
805:
806: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
807: RAISE okl_api.g_exception_unexpected_error;
808: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
809: RAISE okl_api.g_exception_error;
810: END IF;
811:
812: INSERT INTO okl_fe_adj_mat_versions
813: (adj_mat_version_id
866: ,l_def_pal_rec.last_updated_by
867: ,l_def_pal_rec.last_update_date
868: ,l_def_pal_rec.last_update_login);
869:
870: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
871: RAISE okl_api.g_exception_unexpected_error;
872: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
873: RAISE okl_api.g_exception_error;
874: END IF;
867: ,l_def_pal_rec.last_update_date
868: ,l_def_pal_rec.last_update_login);
869:
870: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
871: RAISE okl_api.g_exception_unexpected_error;
872: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
873: RAISE okl_api.g_exception_error;
874: END IF;
875:
868: ,l_def_pal_rec.last_update_login);
869:
870: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
871: RAISE okl_api.g_exception_unexpected_error;
872: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
873: RAISE okl_api.g_exception_error;
874: END IF;
875:
876: --Set OUT Values
869:
870: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
871: RAISE okl_api.g_exception_unexpected_error;
872: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
873: RAISE okl_api.g_exception_error;
874: END IF;
875:
876: --Set OUT Values
877:
875:
876: --Set OUT Values
877:
878: x_pal_rec := l_def_pal_rec;
879: okl_api.end_activity(x_msg_count
880: ,x_msg_data);
881: EXCEPTION
882: WHEN g_exception_halt_validation THEN
883:
883:
884: -- No action necessary. Validation can continue to next attribute/column
885:
886: NULL;
887: WHEN okl_api.g_exception_error THEN
888: x_return_status := okl_api.handle_exceptions(l_api_name
889: ,g_pkg_name
890: ,'OKL_API.G_RET_STS_ERROR'
891: ,x_msg_count
884: -- No action necessary. Validation can continue to next attribute/column
885:
886: NULL;
887: WHEN okl_api.g_exception_error THEN
888: x_return_status := okl_api.handle_exceptions(l_api_name
889: ,g_pkg_name
890: ,'OKL_API.G_RET_STS_ERROR'
891: ,x_msg_count
892: ,x_msg_data
886: NULL;
887: WHEN okl_api.g_exception_error THEN
888: x_return_status := okl_api.handle_exceptions(l_api_name
889: ,g_pkg_name
890: ,'OKL_API.G_RET_STS_ERROR'
891: ,x_msg_count
892: ,x_msg_data
893: ,'_PVT');
894: WHEN okl_api.g_exception_unexpected_error THEN
890: ,'OKL_API.G_RET_STS_ERROR'
891: ,x_msg_count
892: ,x_msg_data
893: ,'_PVT');
894: WHEN okl_api.g_exception_unexpected_error THEN
895: x_return_status := okl_api.handle_exceptions(l_api_name
896: ,g_pkg_name
897: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
898: ,x_msg_count
891: ,x_msg_count
892: ,x_msg_data
893: ,'_PVT');
894: WHEN okl_api.g_exception_unexpected_error THEN
895: x_return_status := okl_api.handle_exceptions(l_api_name
896: ,g_pkg_name
897: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
898: ,x_msg_count
899: ,x_msg_data
893: ,'_PVT');
894: WHEN okl_api.g_exception_unexpected_error THEN
895: x_return_status := okl_api.handle_exceptions(l_api_name
896: ,g_pkg_name
897: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
898: ,x_msg_count
899: ,x_msg_data
900: ,'_PVT');
901: WHEN OTHERS THEN
898: ,x_msg_count
899: ,x_msg_data
900: ,'_PVT');
901: WHEN OTHERS THEN
902: x_return_status := okl_api.handle_exceptions(l_api_name
903: ,g_pkg_name
904: ,'OTHERS'
905: ,x_msg_count
906: ,x_msg_data
911: -- Procedure insert_row_tbl
912: --------------------------------------------------------------------------------
913:
914: PROCEDURE insert_row(p_api_version IN NUMBER
915: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
916: ,x_return_status OUT NOCOPY VARCHAR2
917: ,x_msg_count OUT NOCOPY NUMBER
918: ,x_msg_data OUT NOCOPY VARCHAR2
919: ,p_pal_tbl IN okl_pal_tbl
919: ,p_pal_tbl IN okl_pal_tbl
920: ,x_pal_tbl OUT NOCOPY okl_pal_tbl) IS
921: l_api_version CONSTANT NUMBER := 1;
922: l_api_name CONSTANT VARCHAR2(30) := 'insert_row_tbl';
923: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
924: i NUMBER := 0;
925: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
926:
927: BEGIN
921: l_api_version CONSTANT NUMBER := 1;
922: l_api_name CONSTANT VARCHAR2(30) := 'insert_row_tbl';
923: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
924: i NUMBER := 0;
925: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
926:
927: BEGIN
928: okl_api.init_msg_list(p_init_msg_list);
929:
924: i NUMBER := 0;
925: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
926:
927: BEGIN
928: okl_api.init_msg_list(p_init_msg_list);
929:
930: -- Make sure PL/SQL table has records in it before passing
931:
932: IF (p_pal_tbl.COUNT > 0) THEN
933: i := p_pal_tbl.FIRST;
934:
935: LOOP
936: insert_row(p_api_version => p_api_version
937: ,p_init_msg_list => okl_api.g_false
938: ,x_return_status => x_return_status
939: ,x_msg_count => x_msg_count
940: ,x_msg_data => x_msg_data
941: ,p_pal_rec => p_pal_tbl(i)
939: ,x_msg_count => x_msg_count
940: ,x_msg_data => x_msg_data
941: ,p_pal_rec => p_pal_tbl(i)
942: ,x_pal_rec => x_pal_tbl(i));
943: IF x_return_status <> okl_api.g_ret_sts_success THEN
944: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
945: l_overall_status := x_return_status;
946: END IF;
947: END IF;
940: ,x_msg_data => x_msg_data
941: ,p_pal_rec => p_pal_tbl(i)
942: ,x_pal_rec => x_pal_tbl(i));
943: IF x_return_status <> okl_api.g_ret_sts_success THEN
944: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
945: l_overall_status := x_return_status;
946: END IF;
947: END IF;
948: EXIT WHEN(i = p_pal_tbl.LAST);
956:
957: -- No action necessary. Validation can continue to next attribute/column
958:
959: NULL;
960: WHEN okl_api.g_exception_error THEN
961: x_return_status := okl_api.handle_exceptions(l_api_name
962: ,g_pkg_name
963: ,'OKL_API.G_RET_STS_ERROR'
964: ,x_msg_count
957: -- No action necessary. Validation can continue to next attribute/column
958:
959: NULL;
960: WHEN okl_api.g_exception_error THEN
961: x_return_status := okl_api.handle_exceptions(l_api_name
962: ,g_pkg_name
963: ,'OKL_API.G_RET_STS_ERROR'
964: ,x_msg_count
965: ,x_msg_data
959: NULL;
960: WHEN okl_api.g_exception_error THEN
961: x_return_status := okl_api.handle_exceptions(l_api_name
962: ,g_pkg_name
963: ,'OKL_API.G_RET_STS_ERROR'
964: ,x_msg_count
965: ,x_msg_data
966: ,'_PVT');
967: WHEN okl_api.g_exception_unexpected_error THEN
963: ,'OKL_API.G_RET_STS_ERROR'
964: ,x_msg_count
965: ,x_msg_data
966: ,'_PVT');
967: WHEN okl_api.g_exception_unexpected_error THEN
968: x_return_status := okl_api.handle_exceptions(l_api_name
969: ,g_pkg_name
970: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
971: ,x_msg_count
964: ,x_msg_count
965: ,x_msg_data
966: ,'_PVT');
967: WHEN okl_api.g_exception_unexpected_error THEN
968: x_return_status := okl_api.handle_exceptions(l_api_name
969: ,g_pkg_name
970: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
971: ,x_msg_count
972: ,x_msg_data
966: ,'_PVT');
967: WHEN okl_api.g_exception_unexpected_error THEN
968: x_return_status := okl_api.handle_exceptions(l_api_name
969: ,g_pkg_name
970: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
971: ,x_msg_count
972: ,x_msg_data
973: ,'_PVT');
974: WHEN OTHERS THEN
971: ,x_msg_count
972: ,x_msg_data
973: ,'_PVT');
974: WHEN OTHERS THEN
975: x_return_status := okl_api.handle_exceptions(l_api_name
976: ,g_pkg_name
977: ,'OTHERS'
978: ,x_msg_count
979: ,x_msg_data
987: -- Procedure update_row
988: --------------------------------------------------------------------------------
989:
990: PROCEDURE update_row(p_api_version IN NUMBER
991: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
992: ,x_return_status OUT NOCOPY VARCHAR2
993: ,x_msg_count OUT NOCOPY NUMBER
994: ,x_msg_data OUT NOCOPY VARCHAR2
995: ,p_pal_rec IN okl_pal_rec
995: ,p_pal_rec IN okl_pal_rec
996: ,x_pal_rec OUT NOCOPY okl_pal_rec) IS
997: l_api_version CONSTANT NUMBER := 1;
998: l_api_name CONSTANT VARCHAR2(30) := 'update_row';
999: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1000: l_pal_rec okl_pal_rec := p_pal_rec;
1001: l_def_pal_rec okl_pal_rec;
1002: lx_pal_rec okl_pal_rec;
1003:
1014: FUNCTION populate_new_record(p_pal_rec IN okl_pal_rec
1015: ,x_pal_rec OUT NOCOPY okl_pal_rec) RETURN VARCHAR2 IS
1016: l_pal_rec okl_pal_rec;
1017: l_row_notfound BOOLEAN := true;
1018: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1019:
1020: BEGIN
1021: x_pal_rec := p_pal_rec;
1022:
1025: l_pal_rec := get_rec(p_pal_rec
1026: ,l_row_notfound);
1027:
1028: IF (l_row_notfound) THEN
1029: l_return_status := okl_api.g_ret_sts_unexp_error;
1030: END IF;
1031:
1032: IF (x_pal_rec.adj_mat_version_id IS NULL) THEN
1033: x_pal_rec.adj_mat_version_id := l_pal_rec.adj_mat_version_id;
1144: END populate_new_record;
1145:
1146: FUNCTION set_attributes(p_pal_rec IN okl_pal_rec
1147: ,x_pal_rec OUT NOCOPY okl_pal_rec) RETURN VARCHAR2 IS
1148: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1149:
1150: BEGIN
1151: x_pal_rec := p_pal_rec;
1152: RETURN(l_return_status);
1152: RETURN(l_return_status);
1153: END set_attributes;
1154:
1155: BEGIN
1156: l_return_status := okl_api.start_activity(l_api_name
1157: ,g_pkg_name
1158: ,p_init_msg_list
1159: ,l_api_version
1160: ,p_api_version
1160: ,p_api_version
1161: ,'_PVT'
1162: ,x_return_status);
1163:
1164: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1165: RAISE okl_api.g_exception_unexpected_error;
1166: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1167: RAISE okl_api.g_exception_error;
1168: END IF;
1161: ,'_PVT'
1162: ,x_return_status);
1163:
1164: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1165: RAISE okl_api.g_exception_unexpected_error;
1166: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1167: RAISE okl_api.g_exception_error;
1168: END IF;
1169:
1162: ,x_return_status);
1163:
1164: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1165: RAISE okl_api.g_exception_unexpected_error;
1166: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1167: RAISE okl_api.g_exception_error;
1168: END IF;
1169:
1170: --Setting Item Attributes
1163:
1164: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1165: RAISE okl_api.g_exception_unexpected_error;
1166: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1167: RAISE okl_api.g_exception_error;
1168: END IF;
1169:
1170: --Setting Item Attributes
1171:
1171:
1172: l_return_status := set_attributes(l_pal_rec
1173: ,lx_pal_rec);
1174:
1175: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1176: RAISE okl_api.g_exception_unexpected_error;
1177: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1178: RAISE okl_api.g_exception_error;
1179: END IF;
1172: l_return_status := set_attributes(l_pal_rec
1173: ,lx_pal_rec);
1174:
1175: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1176: RAISE okl_api.g_exception_unexpected_error;
1177: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1178: RAISE okl_api.g_exception_error;
1179: END IF;
1180: l_return_status := populate_new_record(lx_pal_rec
1173: ,lx_pal_rec);
1174:
1175: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1176: RAISE okl_api.g_exception_unexpected_error;
1177: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1178: RAISE okl_api.g_exception_error;
1179: END IF;
1180: l_return_status := populate_new_record(lx_pal_rec
1181: ,l_def_pal_rec);
1174:
1175: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1176: RAISE okl_api.g_exception_unexpected_error;
1177: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1178: RAISE okl_api.g_exception_error;
1179: END IF;
1180: l_return_status := populate_new_record(lx_pal_rec
1181: ,l_def_pal_rec);
1182:
1179: END IF;
1180: l_return_status := populate_new_record(lx_pal_rec
1181: ,l_def_pal_rec);
1182:
1183: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1184: RAISE okl_api.g_exception_unexpected_error;
1185: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1186: RAISE okl_api.g_exception_error;
1187: END IF;
1180: l_return_status := populate_new_record(lx_pal_rec
1181: ,l_def_pal_rec);
1182:
1183: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1184: RAISE okl_api.g_exception_unexpected_error;
1185: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1186: RAISE okl_api.g_exception_error;
1187: END IF;
1188: l_def_pal_rec := null_out_defaults(l_def_pal_rec);
1181: ,l_def_pal_rec);
1182:
1183: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1184: RAISE okl_api.g_exception_unexpected_error;
1185: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1186: RAISE okl_api.g_exception_error;
1187: END IF;
1188: l_def_pal_rec := null_out_defaults(l_def_pal_rec);
1189:
1182:
1183: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1184: RAISE okl_api.g_exception_unexpected_error;
1185: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1186: RAISE okl_api.g_exception_error;
1187: END IF;
1188: l_def_pal_rec := null_out_defaults(l_def_pal_rec);
1189:
1190: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1186: RAISE okl_api.g_exception_error;
1187: END IF;
1188: l_def_pal_rec := null_out_defaults(l_def_pal_rec);
1189:
1190: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1191: RAISE okl_api.g_exception_unexpected_error;
1192: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1193: RAISE okl_api.g_exception_error;
1194: END IF;
1187: END IF;
1188: l_def_pal_rec := null_out_defaults(l_def_pal_rec);
1189:
1190: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1191: RAISE okl_api.g_exception_unexpected_error;
1192: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1193: RAISE okl_api.g_exception_error;
1194: END IF;
1195: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
1188: l_def_pal_rec := null_out_defaults(l_def_pal_rec);
1189:
1190: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1191: RAISE okl_api.g_exception_unexpected_error;
1192: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1193: RAISE okl_api.g_exception_error;
1194: END IF;
1195: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
1196: l_return_status := validate_attributes(l_def_pal_rec);
1189:
1190: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1191: RAISE okl_api.g_exception_unexpected_error;
1192: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1193: RAISE okl_api.g_exception_error;
1194: END IF;
1195: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
1196: l_return_status := validate_attributes(l_def_pal_rec);
1197:
1194: END IF;
1195: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
1196: l_return_status := validate_attributes(l_def_pal_rec);
1197:
1198: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1199: RAISE okl_api.g_exception_unexpected_error;
1200: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1201: RAISE okl_api.g_exception_error;
1202: END IF;
1195: l_def_pal_rec := fill_who_columns(l_def_pal_rec);
1196: l_return_status := validate_attributes(l_def_pal_rec);
1197:
1198: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1199: RAISE okl_api.g_exception_unexpected_error;
1200: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1201: RAISE okl_api.g_exception_error;
1202: END IF;
1203: l_return_status := validate_record(l_def_pal_rec);
1196: l_return_status := validate_attributes(l_def_pal_rec);
1197:
1198: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1199: RAISE okl_api.g_exception_unexpected_error;
1200: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1201: RAISE okl_api.g_exception_error;
1202: END IF;
1203: l_return_status := validate_record(l_def_pal_rec);
1204:
1197:
1198: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1199: RAISE okl_api.g_exception_unexpected_error;
1200: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1201: RAISE okl_api.g_exception_error;
1202: END IF;
1203: l_return_status := validate_record(l_def_pal_rec);
1204:
1205: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1201: RAISE okl_api.g_exception_error;
1202: END IF;
1203: l_return_status := validate_record(l_def_pal_rec);
1204:
1205: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1206: RAISE okl_api.g_exception_unexpected_error;
1207: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1208: RAISE okl_api.g_exception_error;
1209: END IF;
1202: END IF;
1203: l_return_status := validate_record(l_def_pal_rec);
1204:
1205: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1206: RAISE okl_api.g_exception_unexpected_error;
1207: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1208: RAISE okl_api.g_exception_error;
1209: END IF;
1210:
1203: l_return_status := validate_record(l_def_pal_rec);
1204:
1205: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1206: RAISE okl_api.g_exception_unexpected_error;
1207: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1208: RAISE okl_api.g_exception_error;
1209: END IF;
1210:
1211: -- Lock the row before updating
1204:
1205: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1206: RAISE okl_api.g_exception_unexpected_error;
1207: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1208: RAISE okl_api.g_exception_error;
1209: END IF;
1210:
1211: -- Lock the row before updating
1212:
1217: ,x_msg_data => x_msg_data
1218: ,p_def_pal_rec => l_def_pal_rec);
1219:
1220: IF (l_return_status = g_ret_sts_unexp_error) THEN
1221: RAISE okl_api.g_exception_unexpected_error;
1222: ELSIF (l_return_status = g_ret_sts_error) THEN
1223: RAISE okl_api.g_exception_error;
1224: END IF;
1225:
1219:
1220: IF (l_return_status = g_ret_sts_unexp_error) THEN
1221: RAISE okl_api.g_exception_unexpected_error;
1222: ELSIF (l_return_status = g_ret_sts_error) THEN
1223: RAISE okl_api.g_exception_error;
1224: END IF;
1225:
1226: UPDATE okl_fe_adj_mat_versions
1227: SET adj_mat_version_id = l_def_pal_rec.adj_mat_version_id
1253: ,last_update_date = l_def_pal_rec.last_update_date
1254: ,last_update_login = l_def_pal_rec.last_update_login
1255: WHERE adj_mat_version_id = l_def_pal_rec.adj_mat_version_id;
1256:
1257: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1258: RAISE okl_api.g_exception_unexpected_error;
1259: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1260: RAISE okl_api.g_exception_error;
1261: END IF;
1254: ,last_update_login = l_def_pal_rec.last_update_login
1255: WHERE adj_mat_version_id = l_def_pal_rec.adj_mat_version_id;
1256:
1257: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1258: RAISE okl_api.g_exception_unexpected_error;
1259: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1260: RAISE okl_api.g_exception_error;
1261: END IF;
1262:
1255: WHERE adj_mat_version_id = l_def_pal_rec.adj_mat_version_id;
1256:
1257: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1258: RAISE okl_api.g_exception_unexpected_error;
1259: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1260: RAISE okl_api.g_exception_error;
1261: END IF;
1262:
1263: --Set OUT Values
1256:
1257: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1258: RAISE okl_api.g_exception_unexpected_error;
1259: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1260: RAISE okl_api.g_exception_error;
1261: END IF;
1262:
1263: --Set OUT Values
1264:
1262:
1263: --Set OUT Values
1264:
1265: x_pal_rec := l_def_pal_rec;
1266: okl_api.end_activity(x_msg_count
1267: ,x_msg_data);
1268: EXCEPTION
1269: WHEN g_exception_halt_validation THEN
1270:
1270:
1271: -- No action necessary. Validation can continue to next attribute/column
1272:
1273: NULL;
1274: WHEN okl_api.g_exception_error THEN
1275: x_return_status := okl_api.handle_exceptions(l_api_name
1276: ,g_pkg_name
1277: ,'OKL_API.G_RET_STS_ERROR'
1278: ,x_msg_count
1271: -- No action necessary. Validation can continue to next attribute/column
1272:
1273: NULL;
1274: WHEN okl_api.g_exception_error THEN
1275: x_return_status := okl_api.handle_exceptions(l_api_name
1276: ,g_pkg_name
1277: ,'OKL_API.G_RET_STS_ERROR'
1278: ,x_msg_count
1279: ,x_msg_data
1273: NULL;
1274: WHEN okl_api.g_exception_error THEN
1275: x_return_status := okl_api.handle_exceptions(l_api_name
1276: ,g_pkg_name
1277: ,'OKL_API.G_RET_STS_ERROR'
1278: ,x_msg_count
1279: ,x_msg_data
1280: ,'_PVT');
1281: WHEN okl_api.g_exception_unexpected_error THEN
1277: ,'OKL_API.G_RET_STS_ERROR'
1278: ,x_msg_count
1279: ,x_msg_data
1280: ,'_PVT');
1281: WHEN okl_api.g_exception_unexpected_error THEN
1282: x_return_status := okl_api.handle_exceptions(l_api_name
1283: ,g_pkg_name
1284: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1285: ,x_msg_count
1278: ,x_msg_count
1279: ,x_msg_data
1280: ,'_PVT');
1281: WHEN okl_api.g_exception_unexpected_error THEN
1282: x_return_status := okl_api.handle_exceptions(l_api_name
1283: ,g_pkg_name
1284: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1285: ,x_msg_count
1286: ,x_msg_data
1280: ,'_PVT');
1281: WHEN okl_api.g_exception_unexpected_error THEN
1282: x_return_status := okl_api.handle_exceptions(l_api_name
1283: ,g_pkg_name
1284: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1285: ,x_msg_count
1286: ,x_msg_data
1287: ,'_PVT');
1288: WHEN OTHERS THEN
1285: ,x_msg_count
1286: ,x_msg_data
1287: ,'_PVT');
1288: WHEN OTHERS THEN
1289: x_return_status := okl_api.handle_exceptions(l_api_name
1290: ,g_pkg_name
1291: ,'OTHERS'
1292: ,x_msg_count
1293: ,x_msg_data
1298: -- Procedure insert_row_tbl
1299: --------------------------------------------------------------------------------
1300:
1301: PROCEDURE update_row(p_api_version IN NUMBER
1302: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1303: ,x_return_status OUT NOCOPY VARCHAR2
1304: ,x_msg_count OUT NOCOPY NUMBER
1305: ,x_msg_data OUT NOCOPY VARCHAR2
1306: ,p_pal_tbl IN okl_pal_tbl
1306: ,p_pal_tbl IN okl_pal_tbl
1307: ,x_pal_tbl OUT NOCOPY okl_pal_tbl) IS
1308: l_api_version CONSTANT NUMBER := 1;
1309: l_api_name CONSTANT VARCHAR2(30) := 'update_row_tbl';
1310: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1311: i NUMBER := 0;
1312: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1313:
1314: BEGIN
1308: l_api_version CONSTANT NUMBER := 1;
1309: l_api_name CONSTANT VARCHAR2(30) := 'update_row_tbl';
1310: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1311: i NUMBER := 0;
1312: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1313:
1314: BEGIN
1315: okl_api.init_msg_list(p_init_msg_list);
1316:
1311: i NUMBER := 0;
1312: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1313:
1314: BEGIN
1315: okl_api.init_msg_list(p_init_msg_list);
1316:
1317: -- Make sure PL/SQL table has records in it before passing
1318:
1319: IF (p_pal_tbl.COUNT > 0) THEN
1320: i := p_pal_tbl.FIRST;
1321:
1322: LOOP
1323: update_row(p_api_version => p_api_version
1324: ,p_init_msg_list => okl_api.g_false
1325: ,x_return_status => x_return_status
1326: ,x_msg_count => x_msg_count
1327: ,x_msg_data => x_msg_data
1328: ,p_pal_rec => p_pal_tbl(i)
1326: ,x_msg_count => x_msg_count
1327: ,x_msg_data => x_msg_data
1328: ,p_pal_rec => p_pal_tbl(i)
1329: ,x_pal_rec => x_pal_tbl(i));
1330: IF x_return_status <> okl_api.g_ret_sts_success THEN
1331: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1332: l_overall_status := x_return_status;
1333: END IF;
1334: END IF;
1327: ,x_msg_data => x_msg_data
1328: ,p_pal_rec => p_pal_tbl(i)
1329: ,x_pal_rec => x_pal_tbl(i));
1330: IF x_return_status <> okl_api.g_ret_sts_success THEN
1331: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1332: l_overall_status := x_return_status;
1333: END IF;
1334: END IF;
1335: EXIT WHEN(i = p_pal_tbl.LAST);
1343:
1344: -- No action necessary. Validation can continue to next attribute/column
1345:
1346: NULL;
1347: WHEN okl_api.g_exception_error THEN
1348: x_return_status := okl_api.handle_exceptions(l_api_name
1349: ,g_pkg_name
1350: ,'OKL_API.G_RET_STS_ERROR'
1351: ,x_msg_count
1344: -- No action necessary. Validation can continue to next attribute/column
1345:
1346: NULL;
1347: WHEN okl_api.g_exception_error THEN
1348: x_return_status := okl_api.handle_exceptions(l_api_name
1349: ,g_pkg_name
1350: ,'OKL_API.G_RET_STS_ERROR'
1351: ,x_msg_count
1352: ,x_msg_data
1346: NULL;
1347: WHEN okl_api.g_exception_error THEN
1348: x_return_status := okl_api.handle_exceptions(l_api_name
1349: ,g_pkg_name
1350: ,'OKL_API.G_RET_STS_ERROR'
1351: ,x_msg_count
1352: ,x_msg_data
1353: ,'_PVT');
1354: WHEN okl_api.g_exception_unexpected_error THEN
1350: ,'OKL_API.G_RET_STS_ERROR'
1351: ,x_msg_count
1352: ,x_msg_data
1353: ,'_PVT');
1354: WHEN okl_api.g_exception_unexpected_error THEN
1355: x_return_status := okl_api.handle_exceptions(l_api_name
1356: ,g_pkg_name
1357: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1358: ,x_msg_count
1351: ,x_msg_count
1352: ,x_msg_data
1353: ,'_PVT');
1354: WHEN okl_api.g_exception_unexpected_error THEN
1355: x_return_status := okl_api.handle_exceptions(l_api_name
1356: ,g_pkg_name
1357: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1358: ,x_msg_count
1359: ,x_msg_data
1353: ,'_PVT');
1354: WHEN okl_api.g_exception_unexpected_error THEN
1355: x_return_status := okl_api.handle_exceptions(l_api_name
1356: ,g_pkg_name
1357: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1358: ,x_msg_count
1359: ,x_msg_data
1360: ,'_PVT');
1361: WHEN OTHERS THEN
1358: ,x_msg_count
1359: ,x_msg_data
1360: ,'_PVT');
1361: WHEN OTHERS THEN
1362: x_return_status := okl_api.handle_exceptions(l_api_name
1363: ,g_pkg_name
1364: ,'OTHERS'
1365: ,x_msg_count
1366: ,x_msg_data
1371: -- Procedure delete_row
1372: --------------------------------------------------------------------------------
1373:
1374: PROCEDURE delete_row(p_api_version IN NUMBER
1375: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1376: ,x_return_status OUT NOCOPY VARCHAR2
1377: ,x_msg_count OUT NOCOPY NUMBER
1378: ,x_msg_data OUT NOCOPY VARCHAR2
1379: ,p_pal_rec IN okl_pal_rec) IS
1378: ,x_msg_data OUT NOCOPY VARCHAR2
1379: ,p_pal_rec IN okl_pal_rec) IS
1380: l_api_version CONSTANT NUMBER := 1;
1381: l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1382: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1383: l_pal_rec okl_pal_rec := p_pal_rec;
1384: l_row_notfound BOOLEAN := true;
1385:
1386: BEGIN
1383: l_pal_rec okl_pal_rec := p_pal_rec;
1384: l_row_notfound BOOLEAN := true;
1385:
1386: BEGIN
1387: l_return_status := okl_api.start_activity(l_api_name
1388: ,g_pkg_name
1389: ,p_init_msg_list
1390: ,l_api_version
1391: ,p_api_version
1391: ,p_api_version
1392: ,'_PVT'
1393: ,x_return_status);
1394:
1395: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1396: RAISE okl_api.g_exception_unexpected_error;
1397: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1398: RAISE okl_api.g_exception_error;
1399: END IF;
1392: ,'_PVT'
1393: ,x_return_status);
1394:
1395: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1396: RAISE okl_api.g_exception_unexpected_error;
1397: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1398: RAISE okl_api.g_exception_error;
1399: END IF;
1400:
1393: ,x_return_status);
1394:
1395: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1396: RAISE okl_api.g_exception_unexpected_error;
1397: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1398: RAISE okl_api.g_exception_error;
1399: END IF;
1400:
1401: DELETE FROM okl_fe_adj_mat_versions
1394:
1395: IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1396: RAISE okl_api.g_exception_unexpected_error;
1397: ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1398: RAISE okl_api.g_exception_error;
1399: END IF;
1400:
1401: DELETE FROM okl_fe_adj_mat_versions
1402: WHERE adj_mat_version_id = l_pal_rec.adj_mat_version_id;
1406:
1407: -- No action necessary. Validation can continue to next attribute/column
1408:
1409: NULL;
1410: WHEN okl_api.g_exception_error THEN
1411: x_return_status := okl_api.handle_exceptions(l_api_name
1412: ,g_pkg_name
1413: ,'OKL_API.G_RET_STS_ERROR'
1414: ,x_msg_count
1407: -- No action necessary. Validation can continue to next attribute/column
1408:
1409: NULL;
1410: WHEN okl_api.g_exception_error THEN
1411: x_return_status := okl_api.handle_exceptions(l_api_name
1412: ,g_pkg_name
1413: ,'OKL_API.G_RET_STS_ERROR'
1414: ,x_msg_count
1415: ,x_msg_data
1409: NULL;
1410: WHEN okl_api.g_exception_error THEN
1411: x_return_status := okl_api.handle_exceptions(l_api_name
1412: ,g_pkg_name
1413: ,'OKL_API.G_RET_STS_ERROR'
1414: ,x_msg_count
1415: ,x_msg_data
1416: ,'_PVT');
1417: WHEN okl_api.g_exception_unexpected_error THEN
1413: ,'OKL_API.G_RET_STS_ERROR'
1414: ,x_msg_count
1415: ,x_msg_data
1416: ,'_PVT');
1417: WHEN okl_api.g_exception_unexpected_error THEN
1418: x_return_status := okl_api.handle_exceptions(l_api_name
1419: ,g_pkg_name
1420: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1421: ,x_msg_count
1414: ,x_msg_count
1415: ,x_msg_data
1416: ,'_PVT');
1417: WHEN okl_api.g_exception_unexpected_error THEN
1418: x_return_status := okl_api.handle_exceptions(l_api_name
1419: ,g_pkg_name
1420: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1421: ,x_msg_count
1422: ,x_msg_data
1416: ,'_PVT');
1417: WHEN okl_api.g_exception_unexpected_error THEN
1418: x_return_status := okl_api.handle_exceptions(l_api_name
1419: ,g_pkg_name
1420: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1421: ,x_msg_count
1422: ,x_msg_data
1423: ,'_PVT');
1424: WHEN OTHERS THEN
1421: ,x_msg_count
1422: ,x_msg_data
1423: ,'_PVT');
1424: WHEN OTHERS THEN
1425: x_return_status := okl_api.handle_exceptions(l_api_name
1426: ,g_pkg_name
1427: ,'OTHERS'
1428: ,x_msg_count
1429: ,x_msg_data
1434: -- Procedure delete_row_tbl
1435: --------------------------------------------------------------------------------
1436:
1437: PROCEDURE delete_row(p_api_version IN NUMBER
1438: ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1439: ,x_return_status OUT NOCOPY VARCHAR2
1440: ,x_msg_count OUT NOCOPY NUMBER
1441: ,x_msg_data OUT NOCOPY VARCHAR2
1442: ,p_pal_tbl IN okl_pal_tbl) IS
1441: ,x_msg_data OUT NOCOPY VARCHAR2
1442: ,p_pal_tbl IN okl_pal_tbl) IS
1443: l_api_version CONSTANT NUMBER := 1;
1444: l_api_name CONSTANT VARCHAR2(30) := 'delete_row_tbl';
1445: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1446: i NUMBER := 0;
1447: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1448:
1449: BEGIN
1443: l_api_version CONSTANT NUMBER := 1;
1444: l_api_name CONSTANT VARCHAR2(30) := 'delete_row_tbl';
1445: l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1446: i NUMBER := 0;
1447: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1448:
1449: BEGIN
1450: okl_api.init_msg_list(p_init_msg_list);
1451:
1446: i NUMBER := 0;
1447: l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1448:
1449: BEGIN
1450: okl_api.init_msg_list(p_init_msg_list);
1451:
1452: -- Make sure PL/SQL table has records in it before passing
1453:
1454: IF (p_pal_tbl.COUNT > 0) THEN
1455: i := p_pal_tbl.FIRST;
1456:
1457: LOOP
1458: delete_row(p_api_version => p_api_version
1459: ,p_init_msg_list => okl_api.g_false
1460: ,x_return_status => x_return_status
1461: ,x_msg_count => x_msg_count
1462: ,x_msg_data => x_msg_data
1463: ,p_pal_rec => p_pal_tbl(i));
1460: ,x_return_status => x_return_status
1461: ,x_msg_count => x_msg_count
1462: ,x_msg_data => x_msg_data
1463: ,p_pal_rec => p_pal_tbl(i));
1464: IF x_return_status <> okl_api.g_ret_sts_success THEN
1465: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1466: l_overall_status := x_return_status;
1467: END IF;
1468: END IF;
1461: ,x_msg_count => x_msg_count
1462: ,x_msg_data => x_msg_data
1463: ,p_pal_rec => p_pal_tbl(i));
1464: IF x_return_status <> okl_api.g_ret_sts_success THEN
1465: IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1466: l_overall_status := x_return_status;
1467: END IF;
1468: END IF;
1469: EXIT WHEN(i = p_pal_tbl.LAST);
1477:
1478: -- No action necessary. Validation can continue to next attribute/column
1479:
1480: NULL;
1481: WHEN okl_api.g_exception_error THEN
1482: x_return_status := okl_api.handle_exceptions(l_api_name
1483: ,g_pkg_name
1484: ,'OKL_API.G_RET_STS_ERROR'
1485: ,x_msg_count
1478: -- No action necessary. Validation can continue to next attribute/column
1479:
1480: NULL;
1481: WHEN okl_api.g_exception_error THEN
1482: x_return_status := okl_api.handle_exceptions(l_api_name
1483: ,g_pkg_name
1484: ,'OKL_API.G_RET_STS_ERROR'
1485: ,x_msg_count
1486: ,x_msg_data
1480: NULL;
1481: WHEN okl_api.g_exception_error THEN
1482: x_return_status := okl_api.handle_exceptions(l_api_name
1483: ,g_pkg_name
1484: ,'OKL_API.G_RET_STS_ERROR'
1485: ,x_msg_count
1486: ,x_msg_data
1487: ,'_PVT');
1488: WHEN okl_api.g_exception_unexpected_error THEN
1484: ,'OKL_API.G_RET_STS_ERROR'
1485: ,x_msg_count
1486: ,x_msg_data
1487: ,'_PVT');
1488: WHEN okl_api.g_exception_unexpected_error THEN
1489: x_return_status := okl_api.handle_exceptions(l_api_name
1490: ,g_pkg_name
1491: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1492: ,x_msg_count
1485: ,x_msg_count
1486: ,x_msg_data
1487: ,'_PVT');
1488: WHEN okl_api.g_exception_unexpected_error THEN
1489: x_return_status := okl_api.handle_exceptions(l_api_name
1490: ,g_pkg_name
1491: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1492: ,x_msg_count
1493: ,x_msg_data
1487: ,'_PVT');
1488: WHEN okl_api.g_exception_unexpected_error THEN
1489: x_return_status := okl_api.handle_exceptions(l_api_name
1490: ,g_pkg_name
1491: ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1492: ,x_msg_count
1493: ,x_msg_data
1494: ,'_PVT');
1495: WHEN OTHERS THEN
1492: ,x_msg_count
1493: ,x_msg_data
1494: ,'_PVT');
1495: WHEN OTHERS THEN
1496: x_return_status := okl_api.handle_exceptions(l_api_name
1497: ,g_pkg_name
1498: ,'OTHERS'
1499: ,x_msg_count
1500: ,x_msg_data