[Home] [Help]
26: p_clhv_rec clhv_rec_type
27: ,p_mode VARCHAR2
28: ) RETURN VARCHAR2
29: IS
30: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
31: l_dummy number;
32:
33: l_row_not_found boolean := false;
34:
45: IF (p_mode = G_INSERT_MODE) THEN
46:
47: -- column is required:
48: IF (p_clhv_rec.status_code IS NULL) OR
49: (p_clhv_rec.status_code = OKL_API.G_MISS_CHAR)
50: THEN
51: OKL_API.Set_Message(p_app_name => G_APP_NAME,
52: p_msg_name => G_REQUIRED_VALUE,
53: p_token1 => G_COL_NAME_TOKEN,
47: -- column is required:
48: IF (p_clhv_rec.status_code IS NULL) OR
49: (p_clhv_rec.status_code = OKL_API.G_MISS_CHAR)
50: THEN
51: OKL_API.Set_Message(p_app_name => G_APP_NAME,
52: p_msg_name => G_REQUIRED_VALUE,
53: p_token1 => G_COL_NAME_TOKEN,
54: p_token1_value => 'OKL_CHECKLIST.STATUS_CODE');
55: RAISE G_EXCEPTION_HALT_VALIDATION;
60:
61: -- column is required:
62: IF (p_clhv_rec.status_code IS NULL)
63: THEN
64: OKL_API.Set_Message(p_app_name => G_APP_NAME,
65: p_msg_name => G_REQUIRED_VALUE,
66: p_token1 => G_COL_NAME_TOKEN,
67: p_token1_value => 'OKL_CHECKLIST.STATUS_CODE');
68: RAISE G_EXCEPTION_HALT_VALIDATION;
72:
73: -- FK check
74: -- check only if object exists
75: IF (p_clhv_rec.status_code IS NOT NULL AND
76: p_clhv_rec.status_code <> OKL_API.G_MISS_CHAR)
77: THEN
78:
79: OPEN c_lookup(p_clhv_rec.status_code);
80: FETCH c_lookup INTO l_dummy;
81: l_row_not_found := c_lookup%NOTFOUND;
82: CLOSE c_lookup;
83:
84: IF (l_row_not_found) THEN
85: OKL_API.Set_Message(p_app_name => G_APP_NAME,
86: p_msg_name => G_INVALID_VALUE,
87: p_token1 => G_COL_NAME_TOKEN,
88: p_token1_value => 'OKL_CHECKLIST.STATUS_CODE');
89:
95: RETURN l_return_status;
96:
97: EXCEPTION
98: WHEN G_EXCEPTION_HALT_VALIDATION THEN
99: l_return_status := OKL_API.G_RET_STS_ERROR;
100: RETURN l_return_status;
101: WHEN OTHERS THEN
102: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
103: OKL_API.Set_Message(p_app_name => G_APP_NAME,
98: WHEN G_EXCEPTION_HALT_VALIDATION THEN
99: l_return_status := OKL_API.G_RET_STS_ERROR;
100: RETURN l_return_status;
101: WHEN OTHERS THEN
102: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
103: OKL_API.Set_Message(p_app_name => G_APP_NAME,
104: p_msg_name => G_UNEXPECTED_ERROR,
105: p_token1 => G_SQLCODE_TOKEN,
106: p_token1_value => SQLCODE,
99: l_return_status := OKL_API.G_RET_STS_ERROR;
100: RETURN l_return_status;
101: WHEN OTHERS THEN
102: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
103: OKL_API.Set_Message(p_app_name => G_APP_NAME,
104: p_msg_name => G_UNEXPECTED_ERROR,
105: p_token1 => G_SQLCODE_TOKEN,
106: p_token1_value => SQLCODE,
107: p_token2 => G_SQLERRM_TOKEN,
115: p_clhv_rec clhv_rec_type
116: ,p_mode VARCHAR2
117: ) RETURN VARCHAR2
118: IS
119: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
120: l_dummy varchar2(80);
121:
122: l_row_not_found boolean := false;
123: l_org_purpose_code okl_checklists.CHECKLIST_PURPOSE_CODE%type;
150: IF (p_mode = G_INSERT_MODE) THEN
151:
152: -- column is required:
153: IF (p_clhv_rec.CHECKLIST_PURPOSE_CODE IS NULL) OR
154: (p_clhv_rec.CHECKLIST_PURPOSE_CODE = OKL_API.G_MISS_CHAR)
155: THEN
156: OKL_API.Set_Message(p_app_name => G_APP_NAME,
157: p_msg_name => G_REQUIRED_VALUE,
158: p_token1 => G_COL_NAME_TOKEN,
152: -- column is required:
153: IF (p_clhv_rec.CHECKLIST_PURPOSE_CODE IS NULL) OR
154: (p_clhv_rec.CHECKLIST_PURPOSE_CODE = OKL_API.G_MISS_CHAR)
155: THEN
156: OKL_API.Set_Message(p_app_name => G_APP_NAME,
157: p_msg_name => G_REQUIRED_VALUE,
158: p_token1 => G_COL_NAME_TOKEN,
159: p_token1_value => 'OKL_CHECKLIST.CHECKLIST_PURPOSE_CODE');
160: RAISE G_EXCEPTION_HALT_VALIDATION;
171: l_org_status_code_meaning;
172: CLOSE c_org_purpose;
173:
174: IF (p_clhv_rec.CHECKLIST_PURPOSE_CODE IS NOT NULL AND
175: p_clhv_rec.CHECKLIST_PURPOSE_CODE <> OKL_API.G_MISS_CHAR AND
176: p_clhv_rec.CHECKLIST_PURPOSE_CODE <> l_org_purpose_code)
177: THEN
178:
179: -- get the current status
177: THEN
178:
179: -- get the current status
180: IF (p_clhv_rec.STATUS_CODE IS NOT NULL AND
181: p_clhv_rec.STATUS_CODE <> OKL_API.G_MISS_CHAR AND
182: p_clhv_rec.STATUS_CODE <> l_org_status_code)
183: THEN
184:
185: OPEN c_lookup(p_clhv_rec.STATUS_CODE,'OKL_CHECKLIST_STATUS_CODE');
190: l_cur_status_code_meaning := l_org_status_code_meaning;
191:
192: END IF;
193:
194: OKL_API.Set_Message(p_app_name => G_APP_NAME,
195: p_msg_name => 'OKL_CHK_READONLY_COLUMN',
196: p_token1 => 'COL_NAME',
197: p_token1_value => 'Purpose',
198: p_token2 => 'STATUS',
204:
205: -- FK check
206: -- check only if object exists
207: IF (p_clhv_rec.CHECKLIST_PURPOSE_CODE IS NOT NULL AND
208: p_clhv_rec.CHECKLIST_PURPOSE_CODE <> OKL_API.G_MISS_CHAR)
209: THEN
210:
211: OPEN c_lookup(p_clhv_rec.CHECKLIST_PURPOSE_CODE,
212: 'OKL_CHECKLIST_PURPOSE_CODE');
214: l_row_not_found := c_lookup%NOTFOUND;
215: CLOSE c_lookup;
216:
217: IF (l_row_not_found) THEN
218: OKL_API.Set_Message(p_app_name => G_APP_NAME,
219: p_msg_name => G_INVALID_VALUE,
220: p_token1 => G_COL_NAME_TOKEN,
221: p_token1_value => 'OKL_CHECKLIST.CHECKLIST_PURPOSE_CODE');
222:
228: RETURN l_return_status;
229:
230: EXCEPTION
231: WHEN G_EXCEPTION_HALT_VALIDATION THEN
232: l_return_status := OKL_API.G_RET_STS_ERROR;
233: RETURN l_return_status;
234: WHEN OTHERS THEN
235: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
236: OKL_API.Set_Message(p_app_name => G_APP_NAME,
231: WHEN G_EXCEPTION_HALT_VALIDATION THEN
232: l_return_status := OKL_API.G_RET_STS_ERROR;
233: RETURN l_return_status;
234: WHEN OTHERS THEN
235: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
236: OKL_API.Set_Message(p_app_name => G_APP_NAME,
237: p_msg_name => G_UNEXPECTED_ERROR,
238: p_token1 => G_SQLCODE_TOKEN,
239: p_token1_value => SQLCODE,
232: l_return_status := OKL_API.G_RET_STS_ERROR;
233: RETURN l_return_status;
234: WHEN OTHERS THEN
235: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
236: OKL_API.Set_Message(p_app_name => G_APP_NAME,
237: p_msg_name => G_UNEXPECTED_ERROR,
238: p_token1 => G_SQLCODE_TOKEN,
239: p_token1_value => SQLCODE,
240: p_token2 => G_SQLERRM_TOKEN,
248: p_clhv_rec clhv_rec_type
249: ,p_mode VARCHAR2
250: ) RETURN VARCHAR2
251: IS
252: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
253: l_dummy number;
254:
255: l_row_not_found boolean := false;
256: l_purpose_code okl_checklists.CHECKLIST_PURPOSE_CODE%type;
271:
272: -- column is required:
273: IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
274: (p_clhv_rec.CHECKLIST_OBJ_ID IS NULL OR
275: p_clhv_rec.CHECKLIST_OBJ_ID = OKL_API.G_MISS_NUM)
276: THEN
277: OKL_API.Set_Message(p_app_name => G_APP_NAME,
278: p_msg_name => G_REQUIRED_VALUE,
279: p_token1 => G_COL_NAME_TOKEN,
273: IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
274: (p_clhv_rec.CHECKLIST_OBJ_ID IS NULL OR
275: p_clhv_rec.CHECKLIST_OBJ_ID = OKL_API.G_MISS_NUM)
276: THEN
277: OKL_API.Set_Message(p_app_name => G_APP_NAME,
278: p_msg_name => G_REQUIRED_VALUE,
279: p_token1 => G_COL_NAME_TOKEN,
280: p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_ID');
281: RAISE G_EXCEPTION_HALT_VALIDATION;
287: -- column is required:
288: IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
289: p_clhv_rec.CHECKLIST_OBJ_ID IS NULL
290: 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 => 'OKL_CHECKLIST.CHECKLIST_OBJ_ID');
295: RAISE G_EXCEPTION_HALT_VALIDATION;
300: RETURN l_return_status;
301:
302: EXCEPTION
303: WHEN G_EXCEPTION_HALT_VALIDATION THEN
304: l_return_status := OKL_API.G_RET_STS_ERROR;
305: RETURN l_return_status;
306: WHEN OTHERS THEN
307: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
308: OKL_API.Set_Message(p_app_name => G_APP_NAME,
303: WHEN G_EXCEPTION_HALT_VALIDATION THEN
304: l_return_status := OKL_API.G_RET_STS_ERROR;
305: RETURN l_return_status;
306: WHEN OTHERS THEN
307: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
308: OKL_API.Set_Message(p_app_name => G_APP_NAME,
309: p_msg_name => G_UNEXPECTED_ERROR,
310: p_token1 => G_SQLCODE_TOKEN,
311: p_token1_value => SQLCODE,
304: l_return_status := OKL_API.G_RET_STS_ERROR;
305: RETURN l_return_status;
306: WHEN OTHERS THEN
307: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
308: OKL_API.Set_Message(p_app_name => G_APP_NAME,
309: p_msg_name => G_UNEXPECTED_ERROR,
310: p_token1 => G_SQLCODE_TOKEN,
311: p_token1_value => SQLCODE,
312: p_token2 => G_SQLERRM_TOKEN,
321: p_clhv_rec clhv_rec_type
322: ,p_mode VARCHAR2
323: ) RETURN VARCHAR2
324: IS
325: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
326: l_dummy number;
327:
328: l_row_not_found boolean := false;
329: l_purpose_code okl_checklists.CHECKLIST_PURPOSE_CODE%type;
352:
353: -- column is required:
354: IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
355: (p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE IS NULL OR
356: p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE = OKL_API.G_MISS_CHAR)
357: THEN
358: OKL_API.Set_Message(p_app_name => G_APP_NAME,
359: p_msg_name => G_REQUIRED_VALUE,
360: p_token1 => G_COL_NAME_TOKEN,
354: IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
355: (p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE IS NULL OR
356: p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE = OKL_API.G_MISS_CHAR)
357: THEN
358: OKL_API.Set_Message(p_app_name => G_APP_NAME,
359: p_msg_name => G_REQUIRED_VALUE,
360: p_token1 => G_COL_NAME_TOKEN,
361: p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_TYPE_CODE');
362: RAISE G_EXCEPTION_HALT_VALIDATION;
368: -- column is required:
369: IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
370: p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE IS NULL
371: THEN
372: OKL_API.Set_Message(p_app_name => G_APP_NAME,
373: p_msg_name => G_REQUIRED_VALUE,
374: p_token1 => G_COL_NAME_TOKEN,
375: p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_TYPE_CODE');
376: RAISE G_EXCEPTION_HALT_VALIDATION;
380:
381: -- FK check
382: -- check only if object exists
383: IF (p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE IS NOT NULL AND
384: p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE <> OKL_API.G_MISS_CHAR)
385: THEN
386:
387: OPEN c_lookup(p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE);
388: FETCH c_lookup INTO l_dummy;
389: l_row_not_found := c_lookup%NOTFOUND;
390: CLOSE c_lookup;
391:
392: IF (l_row_not_found) THEN
393: OKL_API.Set_Message(p_app_name => G_APP_NAME,
394: p_msg_name => G_INVALID_VALUE,
395: p_token1 => G_COL_NAME_TOKEN,
396: p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_TYPE_CODE');
397:
403: RETURN l_return_status;
404:
405: EXCEPTION
406: WHEN G_EXCEPTION_HALT_VALIDATION THEN
407: l_return_status := OKL_API.G_RET_STS_ERROR;
408: RETURN l_return_status;
409: WHEN OTHERS THEN
410: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
411: OKL_API.Set_Message(p_app_name => G_APP_NAME,
406: WHEN G_EXCEPTION_HALT_VALIDATION THEN
407: l_return_status := OKL_API.G_RET_STS_ERROR;
408: RETURN l_return_status;
409: WHEN OTHERS THEN
410: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
411: OKL_API.Set_Message(p_app_name => G_APP_NAME,
412: p_msg_name => G_UNEXPECTED_ERROR,
413: p_token1 => G_SQLCODE_TOKEN,
414: p_token1_value => SQLCODE,
407: l_return_status := OKL_API.G_RET_STS_ERROR;
408: RETURN l_return_status;
409: WHEN OTHERS THEN
410: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
411: OKL_API.Set_Message(p_app_name => G_APP_NAME,
412: p_msg_name => G_UNEXPECTED_ERROR,
413: p_token1 => G_SQLCODE_TOKEN,
414: p_token1_value => SQLCODE,
415: p_token2 => G_SQLERRM_TOKEN,
423: p_clhv_rec clhv_rec_type
424: ,p_mode VARCHAR2
425: ) RETURN VARCHAR2
426: IS
427: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
428: l_dummy number;
429:
430: l_row_not_found boolean := false;
431:
442:
443: -- FK check
444: -- check only if object exists
445: IF (p_clhv_rec.CKL_ID IS NOT NULL AND
446: p_clhv_rec.CKL_ID <> OKL_API.G_MISS_NUM)
447: THEN
448:
449: OPEN c_parent_id(p_clhv_rec.CKL_ID);
450: FETCH c_parent_id INTO l_dummy;
451: l_row_not_found := c_parent_id%NOTFOUND;
452: CLOSE c_parent_id;
453:
454: IF (l_row_not_found) THEN
455: OKL_API.Set_Message(p_app_name => G_APP_NAME,
456: p_msg_name => G_INVALID_VALUE,
457: p_token1 => G_COL_NAME_TOKEN,
458: p_token1_value => 'OKL_CHECKLISTS.CKL_ID');
459:
462: END IF;
463:
464: -- Parent checklist is allowed only if checklist purpose is 'Checklist Template'
465: IF ((p_clhv_rec.CKL_ID IS NOT NULL AND
466: p_clhv_rec.CKL_ID <> OKL_API.G_MISS_NUM) and
467: (p_clhv_rec.CHECKLIST_PURPOSE_CODE <> 'CHECKLIST_TEMPLATE'))
468: THEN
469: OKL_API.Set_Message(p_app_name => G_APP_NAME,
470: p_msg_name => 'OKL_INVALID_PARENT_CHECKLIST');
465: IF ((p_clhv_rec.CKL_ID IS NOT NULL AND
466: p_clhv_rec.CKL_ID <> OKL_API.G_MISS_NUM) and
467: (p_clhv_rec.CHECKLIST_PURPOSE_CODE <> 'CHECKLIST_TEMPLATE'))
468: THEN
469: OKL_API.Set_Message(p_app_name => G_APP_NAME,
470: p_msg_name => 'OKL_INVALID_PARENT_CHECKLIST');
471:
472: RAISE G_EXCEPTION_HALT_VALIDATION;
473: END IF;
476: RETURN l_return_status;
477:
478: EXCEPTION
479: WHEN G_EXCEPTION_HALT_VALIDATION THEN
480: l_return_status := OKL_API.G_RET_STS_ERROR;
481: RETURN l_return_status;
482: WHEN OTHERS THEN
483: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
484: OKL_API.Set_Message(p_app_name => G_APP_NAME,
479: WHEN G_EXCEPTION_HALT_VALIDATION THEN
480: l_return_status := OKL_API.G_RET_STS_ERROR;
481: RETURN l_return_status;
482: WHEN OTHERS THEN
483: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
484: OKL_API.Set_Message(p_app_name => G_APP_NAME,
485: p_msg_name => G_UNEXPECTED_ERROR,
486: p_token1 => G_SQLCODE_TOKEN,
487: p_token1_value => SQLCODE,
480: l_return_status := OKL_API.G_RET_STS_ERROR;
481: RETURN l_return_status;
482: WHEN OTHERS THEN
483: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
484: OKL_API.Set_Message(p_app_name => G_APP_NAME,
485: p_msg_name => G_UNEXPECTED_ERROR,
486: p_token1 => G_SQLCODE_TOKEN,
487: p_token1_value => SQLCODE,
488: p_token2 => G_SQLERRM_TOKEN,
497: p_clhv_rec clhv_rec_type
498: ,p_mode VARCHAR2
499: ) RETURN VARCHAR2
500: IS
501: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
502:
503: BEGIN
504:
505: IF (p_mode = G_INSERT_MODE) THEN
505: IF (p_mode = G_INSERT_MODE) THEN
506:
507: -- column is required:
508: IF (p_clhv_rec.checklist_number IS NULL) OR
509: (p_clhv_rec.checklist_number = OKL_API.G_MISS_CHAR)
510: THEN
511: OKL_API.Set_Message(p_app_name => G_APP_NAME,
512: p_msg_name => G_REQUIRED_VALUE,
513: p_token1 => G_COL_NAME_TOKEN,
507: -- column is required:
508: IF (p_clhv_rec.checklist_number IS NULL) OR
509: (p_clhv_rec.checklist_number = OKL_API.G_MISS_CHAR)
510: THEN
511: OKL_API.Set_Message(p_app_name => G_APP_NAME,
512: p_msg_name => G_REQUIRED_VALUE,
513: p_token1 => G_COL_NAME_TOKEN,
514: p_token1_value => 'Checklist Name');
515: RAISE G_EXCEPTION_HALT_VALIDATION;
520:
521: -- column is required:
522: IF (p_clhv_rec.checklist_number IS NULL)
523: THEN
524: OKL_API.Set_Message(p_app_name => G_APP_NAME,
525: p_msg_name => G_REQUIRED_VALUE,
526: p_token1 => G_COL_NAME_TOKEN,
527: p_token1_value => 'Checklist Name');
528: RAISE G_EXCEPTION_HALT_VALIDATION;
533: RETURN l_return_status;
534:
535: EXCEPTION
536: WHEN G_EXCEPTION_HALT_VALIDATION THEN
537: l_return_status := OKL_API.G_RET_STS_ERROR;
538: RETURN l_return_status;
539: WHEN OTHERS THEN
540: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
541: OKL_API.Set_Message(p_app_name => G_APP_NAME,
536: WHEN G_EXCEPTION_HALT_VALIDATION THEN
537: l_return_status := OKL_API.G_RET_STS_ERROR;
538: RETURN l_return_status;
539: WHEN OTHERS THEN
540: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
541: OKL_API.Set_Message(p_app_name => G_APP_NAME,
542: p_msg_name => G_UNEXPECTED_ERROR,
543: p_token1 => G_SQLCODE_TOKEN,
544: p_token1_value => SQLCODE,
537: l_return_status := OKL_API.G_RET_STS_ERROR;
538: RETURN l_return_status;
539: WHEN OTHERS THEN
540: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
541: OKL_API.Set_Message(p_app_name => G_APP_NAME,
542: p_msg_name => G_UNEXPECTED_ERROR,
543: p_token1 => G_SQLCODE_TOKEN,
544: p_token1_value => SQLCODE,
545: p_token2 => G_SQLERRM_TOKEN,
554: p_clhv_rec clhv_rec_type
555: ,p_mode VARCHAR2
556: ) RETURN VARCHAR2
557: IS
558: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
559: l_dummy varchar2(80);
560:
561: l_row_not_found boolean := false;
562:
592: IF (p_mode = G_INSERT_MODE) THEN
593:
594: -- column is required:
595: IF (p_clhv_rec.checklist_type IS NULL) OR
596: (p_clhv_rec.checklist_type = OKL_API.G_MISS_CHAR)
597: THEN
598: OKL_API.Set_Message(p_app_name => G_APP_NAME,
599: p_msg_name => G_REQUIRED_VALUE,
600: p_token1 => G_COL_NAME_TOKEN,
594: -- column is required:
595: IF (p_clhv_rec.checklist_type IS NULL) OR
596: (p_clhv_rec.checklist_type = OKL_API.G_MISS_CHAR)
597: THEN
598: OKL_API.Set_Message(p_app_name => G_APP_NAME,
599: p_msg_name => G_REQUIRED_VALUE,
600: p_token1 => G_COL_NAME_TOKEN,
601: p_token1_value => 'Checklist Type');
602: RAISE G_EXCEPTION_HALT_VALIDATION;
611: l_org_status_code_meaning;
612: CLOSE c_org_checklist_type;
613:
614: IF (p_clhv_rec.CHECKLIST_TYPE IS NOT NULL AND
615: p_clhv_rec.CHECKLIST_TYPE <> OKL_API.G_MISS_CHAR AND
616: p_clhv_rec.CHECKLIST_TYPE <> l_org_checklist_type)
617: THEN
618:
619: -- get the current status
617: THEN
618:
619: -- get the current status
620: IF (p_clhv_rec.STATUS_CODE IS NOT NULL AND
621: p_clhv_rec.STATUS_CODE <> OKL_API.G_MISS_CHAR AND
622: p_clhv_rec.STATUS_CODE <> l_org_status_code)
623: THEN
624:
625: OPEN c_lookup(p_clhv_rec.STATUS_CODE,'OKL_CHECKLIST_STATUS_CODE');
630: l_cur_status_code_meaning := l_org_status_code_meaning;
631:
632: END IF;
633:
634: OKL_API.Set_Message(p_app_name => G_APP_NAME,
635: p_msg_name => 'OKL_CHK_READONLY_COLUMN',
636: p_token1 => 'COL_NAME',
637: p_token1_value => 'Checklist Type',
638: p_token2 => 'STATUS',
644:
645: -- FK check
646: -- check only if checklist type exists
647: IF (p_clhv_rec.checklist_type IS NOT NULL AND
648: p_clhv_rec.checklist_type <> OKL_API.G_MISS_CHAR)
649: THEN
650:
651: OPEN c_lookup(p_clhv_rec.checklist_type, G_CHECKLIST_TYPE_LOOKUP_TYPE);
652: FETCH c_lookup INTO l_dummy;
653: l_row_not_found := c_lookup%NOTFOUND;
654: CLOSE c_lookup;
655:
656: IF (l_row_not_found) THEN
657: OKL_API.Set_Message(p_app_name => G_APP_NAME,
658: p_msg_name => G_INVALID_VALUE,
659: p_token1 => G_COL_NAME_TOKEN,
660: p_token1_value => 'Checklist Type');
661:
666: RETURN l_return_status;
667:
668: EXCEPTION
669: WHEN G_EXCEPTION_HALT_VALIDATION THEN
670: l_return_status := OKL_API.G_RET_STS_ERROR;
671: RETURN l_return_status;
672: WHEN OTHERS THEN
673: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
674: OKL_API.Set_Message(p_app_name => G_APP_NAME,
669: WHEN G_EXCEPTION_HALT_VALIDATION THEN
670: l_return_status := OKL_API.G_RET_STS_ERROR;
671: RETURN l_return_status;
672: WHEN OTHERS THEN
673: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
674: OKL_API.Set_Message(p_app_name => G_APP_NAME,
675: p_msg_name => G_UNEXPECTED_ERROR,
676: p_token1 => G_SQLCODE_TOKEN,
677: p_token1_value => SQLCODE,
670: l_return_status := OKL_API.G_RET_STS_ERROR;
671: RETURN l_return_status;
672: WHEN OTHERS THEN
673: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
674: OKL_API.Set_Message(p_app_name => G_APP_NAME,
675: p_msg_name => G_UNEXPECTED_ERROR,
676: p_token1 => G_SQLCODE_TOKEN,
677: p_token1_value => SQLCODE,
678: p_token2 => G_SQLERRM_TOKEN,
687: p_clhv_rec clhv_rec_type
688: ,p_mode VARCHAR2
689: ) RETURN VARCHAR2
690: IS
691: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
692: BEGIN
693:
694: IF (p_clhv_rec.short_description IS NOT NULL AND
695: p_clhv_rec.short_description <> OKL_API.G_MISS_CHAR)
691: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
692: BEGIN
693:
694: IF (p_clhv_rec.short_description IS NOT NULL AND
695: p_clhv_rec.short_description <> OKL_API.G_MISS_CHAR)
696: THEN
697:
698: IF (length(p_clhv_rec.short_description) > 600) THEN
699:
696: THEN
697:
698: IF (length(p_clhv_rec.short_description) > 600) THEN
699:
700: OKL_API.Set_Message(p_app_name => G_APP_NAME,
701: p_msg_name => 'OKL_LLA_EXCEED_MAXIMUM_LENGTH',
702: p_token1 => 'MAX_CHARS',
703: p_token1_value => '600',
704: p_token2 => 'COL_NAME',
710:
711: RETURN l_return_status;
712: EXCEPTION
713: WHEN G_EXCEPTION_HALT_VALIDATION THEN
714: l_return_status := OKL_API.G_RET_STS_ERROR;
715: RETURN l_return_status;
716: WHEN OTHERS THEN
717: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
718: OKL_API.Set_Message(p_app_name => G_APP_NAME,
713: WHEN G_EXCEPTION_HALT_VALIDATION THEN
714: l_return_status := OKL_API.G_RET_STS_ERROR;
715: RETURN l_return_status;
716: WHEN OTHERS THEN
717: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
718: OKL_API.Set_Message(p_app_name => G_APP_NAME,
719: p_msg_name => G_UNEXPECTED_ERROR,
720: p_token1 => G_SQLCODE_TOKEN,
721: p_token1_value => SQLCODE,
714: l_return_status := OKL_API.G_RET_STS_ERROR;
715: RETURN l_return_status;
716: WHEN OTHERS THEN
717: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
718: OKL_API.Set_Message(p_app_name => G_APP_NAME,
719: p_msg_name => G_UNEXPECTED_ERROR,
720: p_token1 => G_SQLCODE_TOKEN,
721: p_token1_value => SQLCODE,
722: p_token2 => G_SQLERRM_TOKEN,
731: p_clhv_rec clhv_rec_type
732: ,p_mode VARCHAR2
733: ) RETURN VARCHAR2
734: IS
735: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
736: BEGIN
737:
738: IF (p_clhv_rec.description IS NOT NULL AND
739: p_clhv_rec.description <> OKL_API.G_MISS_CHAR)
735: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
736: BEGIN
737:
738: IF (p_clhv_rec.description IS NOT NULL AND
739: p_clhv_rec.description <> OKL_API.G_MISS_CHAR)
740: THEN
741:
742: IF (length(p_clhv_rec.description) > 1995) THEN
743:
740: THEN
741:
742: IF (length(p_clhv_rec.description) > 1995) THEN
743:
744: OKL_API.Set_Message(p_app_name => G_APP_NAME,
745: p_msg_name => 'OKL_LLA_EXCEED_MAXIMUM_LENGTH',
746: p_token1 => 'MAX_CHARS',
747: p_token1_value => '1995',
748: p_token2 => 'COL_NAME',
754:
755: RETURN l_return_status;
756: EXCEPTION
757: WHEN G_EXCEPTION_HALT_VALIDATION THEN
758: l_return_status := OKL_API.G_RET_STS_ERROR;
759: RETURN l_return_status;
760: WHEN OTHERS THEN
761: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
762: OKL_API.Set_Message(p_app_name => G_APP_NAME,
757: WHEN G_EXCEPTION_HALT_VALIDATION THEN
758: l_return_status := OKL_API.G_RET_STS_ERROR;
759: RETURN l_return_status;
760: WHEN OTHERS THEN
761: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
762: OKL_API.Set_Message(p_app_name => G_APP_NAME,
763: p_msg_name => G_UNEXPECTED_ERROR,
764: p_token1 => G_SQLCODE_TOKEN,
765: p_token1_value => SQLCODE,
758: l_return_status := OKL_API.G_RET_STS_ERROR;
759: RETURN l_return_status;
760: WHEN OTHERS THEN
761: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
762: OKL_API.Set_Message(p_app_name => G_APP_NAME,
763: p_msg_name => G_UNEXPECTED_ERROR,
764: p_token1 => G_SQLCODE_TOKEN,
765: p_token1_value => SQLCODE,
766: p_token2 => G_SQLERRM_TOKEN,
775: p_clhv_rec clhv_rec_type
776: ,p_mode VARCHAR2
777: ) RETURN VARCHAR2
778: IS
779: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
780: l_dummy number;
781:
782: BEGIN
783:
785: IF (p_mode = G_INSERT_MODE) THEN
786:
787: -- column is required:
788: IF (p_clhv_rec.end_date IS NULL) OR
789: (p_clhv_rec.end_date = OKL_API.G_MISS_DATE)
790: THEN
791: OKL_API.Set_Message(p_app_name => G_APP_NAME,
792: p_msg_name => G_REQUIRED_VALUE,
793: p_token1 => G_COL_NAME_TOKEN,
787: -- column is required:
788: IF (p_clhv_rec.end_date IS NULL) OR
789: (p_clhv_rec.end_date = OKL_API.G_MISS_DATE)
790: THEN
791: OKL_API.Set_Message(p_app_name => G_APP_NAME,
792: p_msg_name => G_REQUIRED_VALUE,
793: p_token1 => G_COL_NAME_TOKEN,
794: p_token1_value => 'Effective To');
795: RAISE G_EXCEPTION_HALT_VALIDATION;
800:
801: -- column is required:
802: IF (p_clhv_rec.end_date IS NULL)
803: THEN
804: OKL_API.Set_Message(p_app_name => G_APP_NAME,
805: p_msg_name => G_REQUIRED_VALUE,
806: p_token1 => G_COL_NAME_TOKEN,
807: p_token1_value => 'Effective To');
808: RAISE G_EXCEPTION_HALT_VALIDATION;
810: */
811: END IF;
812:
813: IF (p_clhv_rec.end_date IS NOT NULL) AND
814: (p_clhv_rec.end_date <> OKL_API.G_MISS_DATE)
815: THEN
816:
817: IF (trunc(p_clhv_rec.end_date) < trunc(sysdate))
818: THEN
815: THEN
816:
817: IF (trunc(p_clhv_rec.end_date) < trunc(sysdate))
818: THEN
819: OKL_API.Set_Message(p_app_name => G_APP_NAME,
820: p_msg_name => G_LLA_RANGE_CHECK,
821: p_token1 => 'COL_NAME1',
822: p_token1_value => 'Effective To',
823: p_token2 => 'COL_NAME2',
830: RETURN l_return_status;
831:
832: EXCEPTION
833: WHEN G_EXCEPTION_HALT_VALIDATION THEN
834: l_return_status := OKL_API.G_RET_STS_ERROR;
835: RETURN l_return_status;
836: WHEN OTHERS THEN
837: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
838: OKL_API.Set_Message(p_app_name => G_APP_NAME,
833: WHEN G_EXCEPTION_HALT_VALIDATION THEN
834: l_return_status := OKL_API.G_RET_STS_ERROR;
835: RETURN l_return_status;
836: WHEN OTHERS THEN
837: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
838: OKL_API.Set_Message(p_app_name => G_APP_NAME,
839: p_msg_name => G_UNEXPECTED_ERROR,
840: p_token1 => G_SQLCODE_TOKEN,
841: p_token1_value => SQLCODE,
834: l_return_status := OKL_API.G_RET_STS_ERROR;
835: RETURN l_return_status;
836: WHEN OTHERS THEN
837: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
838: OKL_API.Set_Message(p_app_name => G_APP_NAME,
839: p_msg_name => G_UNEXPECTED_ERROR,
840: p_token1 => G_SQLCODE_TOKEN,
841: p_token1_value => SQLCODE,
842: p_token2 => G_SQLERRM_TOKEN,
852: p_clhv_rec clhv_rec_type
853: ,p_mode VARCHAR2
854: ) RETURN VARCHAR2
855: IS
856: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
857: l_dummy number;
858:
859: l_row_not_found boolean := false;
860: l_purpose_code okl_checklists.CHECKLIST_PURPOSE_CODE%type;
916: -- check dates overlap between each other
917: IF (r_this_row.cld_START_DATE > r_this_row.grp_END_DATE or
918: r_this_row.grp_START_DATE > r_this_row.cld_END_DATE) THEN
919:
920: OKL_API.Set_Message(p_app_name => G_APP_NAME,
921: p_msg_name => 'OKL_CHK_CLIST_DATE_OVERLAPS');
922: --dbms_output.put_line('CHECKLIST_TEMPLATE_GROUP: case1');
923:
924: END IF;
925:
926: ELSE
927: -- chlid end date < group start date
928: IF r_this_row.cld_END_DATE < r_this_row.grp_START_DATE THEN
929: OKL_API.Set_Message(p_app_name => G_APP_NAME,
930: p_msg_name => 'OKL_CHK_CLIST_DATE_OVERLAPS');
931: --dbms_output.put_line('CHECKLIST_TEMPLATE_GROUP: case2');
932: END IF;
933:
938: IF r_this_row.cld_START_DATE IS NOT NULL THEN
939:
940: -- chlid start date > group end date
941: IF r_this_row.cld_START_DATE > r_this_row.grp_END_DATE THEN
942: OKL_API.Set_Message(p_app_name => G_APP_NAME,
943: p_msg_name => 'OKL_CHK_CLIST_DATE_OVERLAPS');
944: --dbms_output.put_line('CHECKLIST_TEMPLATE_GROUP: case3');
945: END IF;
946:
965: -- check dates overlap between each other
966: IF (r_this_row.cld_START_DATE > r_this_row.grp_END_DATE or
967: r_this_row.grp_START_DATE > r_this_row.cld_END_DATE) THEN
968:
969: OKL_API.Set_Message(p_app_name => G_APP_NAME,
970: p_msg_name => 'OKL_CHK_CLIST_DATE_OVERLAPS');
971: --dbms_output.put_line('CHECKLIST_TEMPLATE: case1');
972: END IF;
973:
973:
974: ELSE
975: -- chlid end date < group start date
976: IF r_this_row.cld_END_DATE < r_this_row.grp_START_DATE THEN
977: OKL_API.Set_Message(p_app_name => G_APP_NAME,
978: p_msg_name => 'OKL_CHK_CLIST_DATE_OVERLAPS');
979: --dbms_output.put_line('CHECKLIST_TEMPLATE: case2');
980: END IF;
981:
986: IF r_this_row.cld_START_DATE IS NOT NULL THEN
987:
988: -- chlid start date > group end date
989: IF r_this_row.cld_START_DATE > r_this_row.grp_END_DATE THEN
990: OKL_API.Set_Message(p_app_name => G_APP_NAME,
991: p_msg_name => 'OKL_CHK_CLIST_DATE_OVERLAPS');
992: --dbms_output.put_line('CHECKLIST_TEMPLATE: case3');
993: END IF;
994:
1004: RETURN l_return_status;
1005:
1006: EXCEPTION
1007: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1008: l_return_status := OKL_API.G_RET_STS_ERROR;
1009: RETURN l_return_status;
1010: WHEN OTHERS THEN
1011: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1012: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1007: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1008: l_return_status := OKL_API.G_RET_STS_ERROR;
1009: RETURN l_return_status;
1010: WHEN OTHERS THEN
1011: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1012: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1013: p_msg_name => G_UNEXPECTED_ERROR,
1014: p_token1 => G_SQLCODE_TOKEN,
1015: p_token1_value => SQLCODE,
1008: l_return_status := OKL_API.G_RET_STS_ERROR;
1009: RETURN l_return_status;
1010: WHEN OTHERS THEN
1011: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1012: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1013: p_msg_name => G_UNEXPECTED_ERROR,
1014: p_token1 => G_SQLCODE_TOKEN,
1015: p_token1_value => SQLCODE,
1016: p_token2 => G_SQLERRM_TOKEN,
1028: p_clhv_rec clhv_rec_type
1029: ,p_mode VARCHAR2
1030: ) RETURN VARCHAR2
1031: IS
1032: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1033: l_count number := 0;
1034: l_row_found boolean := false;
1035: l_dummy number;
1036:
1049: l_row_found := c_unq%FOUND;
1050: CLOSE c_unq;
1051:
1052: IF (l_count > 1) THEN
1053: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1054: p_msg_name => G_NOT_UNIQUE,
1055: p_token1 => G_COL_NAME_TOKEN,
1056: p_token1_value => 'The combinations of the Checklist Name and the Type');
1057:
1061: RETURN l_return_status;
1062:
1063: EXCEPTION
1064: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1065: l_return_status := OKL_API.G_RET_STS_ERROR;
1066: RETURN l_return_status;
1067: WHEN OTHERS THEN
1068: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1069: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1064: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1065: l_return_status := OKL_API.G_RET_STS_ERROR;
1066: RETURN l_return_status;
1067: WHEN OTHERS THEN
1068: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1069: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1070: p_msg_name => G_UNEXPECTED_ERROR,
1071: p_token1 => G_SQLCODE_TOKEN,
1072: p_token1_value => SQLCODE,
1065: l_return_status := OKL_API.G_RET_STS_ERROR;
1066: RETURN l_return_status;
1067: WHEN OTHERS THEN
1068: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1069: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1070: p_msg_name => G_UNEXPECTED_ERROR,
1071: p_token1 => G_SQLCODE_TOKEN,
1072: p_token1_value => SQLCODE,
1073: p_token2 => G_SQLERRM_TOKEN,
1082: p_clhv_rec clhv_rec_type
1083: ,p_mode VARCHAR2
1084: ) RETURN VARCHAR2
1085: IS
1086: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1087: l_dummy number;
1088: l_row_found boolean := false;
1089:
1090: cursor c_date(p_id number)
1102: l_row_found := c_date%FOUND;
1103: close c_date;
1104:
1105: IF (l_row_found) THEN
1106: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1107: p_msg_name => G_LLA_RANGE_CHECK,
1108: p_token1 => 'COL_NAME1',
1109: p_token1_value => 'Effective To',
1110: p_token2 => 'COL_NAME2',
1116: RETURN l_return_status;
1117:
1118: EXCEPTION
1119: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1120: l_return_status := OKL_API.G_RET_STS_ERROR;
1121: RETURN l_return_status;
1122: WHEN OTHERS THEN
1123: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1124: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1119: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1120: l_return_status := OKL_API.G_RET_STS_ERROR;
1121: RETURN l_return_status;
1122: WHEN OTHERS THEN
1123: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1124: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1125: p_msg_name => G_UNEXPECTED_ERROR,
1126: p_token1 => G_SQLCODE_TOKEN,
1127: p_token1_value => SQLCODE,
1120: l_return_status := OKL_API.G_RET_STS_ERROR;
1121: RETURN l_return_status;
1122: WHEN OTHERS THEN
1123: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1124: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1125: p_msg_name => G_UNEXPECTED_ERROR,
1126: p_token1 => G_SQLCODE_TOKEN,
1127: p_token1_value => SQLCODE,
1128: p_token2 => G_SQLERRM_TOKEN,
1138: p_cldv_rec cldv_rec_type
1139: ,p_mode VARCHAR2
1140: ) RETURN VARCHAR2
1141: IS
1142: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1143: l_count number := 0;
1144: l_row_found boolean := false;
1145: l_dummy number;
1146:
1180: l_row_found := c_dup%FOUND;
1181: CLOSE c_dup;
1182:
1183: IF (l_row_found) THEN
1184: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1185: p_msg_name => 'OKL_CHK_DUP_GRP_CLISTS');
1186:
1187: RAISE G_EXCEPTION_HALT_VALIDATION;
1188: END IF;
1190: RETURN l_return_status;
1191:
1192: EXCEPTION
1193: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1194: l_return_status := OKL_API.G_RET_STS_ERROR;
1195: RETURN l_return_status;
1196: WHEN OTHERS THEN
1197: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1198: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1193: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1194: l_return_status := OKL_API.G_RET_STS_ERROR;
1195: RETURN l_return_status;
1196: WHEN OTHERS THEN
1197: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1198: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1199: p_msg_name => G_UNEXPECTED_ERROR,
1200: p_token1 => G_SQLCODE_TOKEN,
1201: p_token1_value => SQLCODE,
1194: l_return_status := OKL_API.G_RET_STS_ERROR;
1195: RETURN l_return_status;
1196: WHEN OTHERS THEN
1197: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1198: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1199: p_msg_name => G_UNEXPECTED_ERROR,
1200: p_token1 => G_SQLCODE_TOKEN,
1201: p_token1_value => SQLCODE,
1202: p_token2 => G_SQLERRM_TOKEN,
1210: p_cldv_rec cldv_rec_type
1211: ,p_mode VARCHAR2
1212: ) RETURN VARCHAR2
1213: IS
1214: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1215: l_dummy number;
1216:
1217: l_row_not_found boolean := false;
1218:
1228:
1229: -- FK check
1230: -- check only if object exists
1231: IF (p_cldv_rec.MANDATORY_FLAG IS NOT NULL AND
1232: p_cldv_rec.MANDATORY_FLAG <> OKL_API.G_MISS_CHAR)
1233: THEN
1234:
1235: OPEN c_lookup(p_cldv_rec.MANDATORY_FLAG);
1236: FETCH c_lookup INTO l_dummy;
1237: l_row_not_found := c_lookup%NOTFOUND;
1238: CLOSE c_lookup;
1239:
1240: IF (l_row_not_found) THEN
1241: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1242: p_msg_name => G_INVALID_VALUE,
1243: p_token1 => G_COL_NAME_TOKEN,
1244: p_token1_value => 'OKL_CHECKLIST_DETAILS.MANDATORY_FLAG');
1245:
1250: RETURN l_return_status;
1251:
1252: EXCEPTION
1253: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1254: l_return_status := OKL_API.G_RET_STS_ERROR;
1255: RETURN l_return_status;
1256: WHEN OTHERS THEN
1257: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1258: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1253: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1254: l_return_status := OKL_API.G_RET_STS_ERROR;
1255: RETURN l_return_status;
1256: WHEN OTHERS THEN
1257: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1258: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1259: p_msg_name => G_UNEXPECTED_ERROR,
1260: p_token1 => G_SQLCODE_TOKEN,
1261: p_token1_value => SQLCODE,
1254: l_return_status := OKL_API.G_RET_STS_ERROR;
1255: RETURN l_return_status;
1256: WHEN OTHERS THEN
1257: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1258: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1259: p_msg_name => G_UNEXPECTED_ERROR,
1260: p_token1 => G_SQLCODE_TOKEN,
1261: p_token1_value => SQLCODE,
1262: p_token2 => G_SQLERRM_TOKEN,
1270: p_cldv_rec cldv_rec_type
1271: ,p_mode VARCHAR2
1272: ) RETURN VARCHAR2
1273: IS
1274: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1275: l_dummy number;
1276:
1277: l_row_not_found boolean := false;
1278:
1288:
1289: -- FK check
1290: -- check only if object exists
1291: IF (p_cldv_rec.USER_COMPLETE_FLAG IS NOT NULL AND
1292: p_cldv_rec.USER_COMPLETE_FLAG <> OKL_API.G_MISS_CHAR)
1293: THEN
1294:
1295: OPEN c_lookup(p_cldv_rec.USER_COMPLETE_FLAG);
1296: FETCH c_lookup INTO l_dummy;
1297: l_row_not_found := c_lookup%NOTFOUND;
1298: CLOSE c_lookup;
1299:
1300: IF (l_row_not_found) THEN
1301: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1302: p_msg_name => G_INVALID_VALUE,
1303: p_token1 => G_COL_NAME_TOKEN,
1304: p_token1_value => 'OKL_CHECKLIST_DETAILS.USER_COMPLETE_FLAG');
1305:
1311: RETURN l_return_status;
1312:
1313: EXCEPTION
1314: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1315: l_return_status := OKL_API.G_RET_STS_ERROR;
1316: RETURN l_return_status;
1317: WHEN OTHERS THEN
1318: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1319: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1314: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1315: l_return_status := OKL_API.G_RET_STS_ERROR;
1316: RETURN l_return_status;
1317: WHEN OTHERS THEN
1318: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1319: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1320: p_msg_name => G_UNEXPECTED_ERROR,
1321: p_token1 => G_SQLCODE_TOKEN,
1322: p_token1_value => SQLCODE,
1315: l_return_status := OKL_API.G_RET_STS_ERROR;
1316: RETURN l_return_status;
1317: WHEN OTHERS THEN
1318: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1319: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1320: p_msg_name => G_UNEXPECTED_ERROR,
1321: p_token1 => G_SQLCODE_TOKEN,
1322: p_token1_value => SQLCODE,
1323: p_token2 => G_SQLERRM_TOKEN,
1331: p_cldv_rec cldv_rec_type
1332: ,p_mode VARCHAR2
1333: ) RETURN VARCHAR2
1334: IS
1335: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1336: l_dummy number;
1337:
1338: l_row_not_found boolean := false;
1339:
1349:
1350: -- FK check
1351: -- check only if object exists
1352: IF (p_cldv_rec.FUNCTION_VALIDATE_RSTS IS NOT NULL AND
1353: p_cldv_rec.FUNCTION_VALIDATE_RSTS <> OKL_API.G_MISS_CHAR)
1354: THEN
1355:
1356: OPEN c_lookup(p_cldv_rec.FUNCTION_VALIDATE_RSTS);
1357: FETCH c_lookup INTO l_dummy;
1358: l_row_not_found := c_lookup%NOTFOUND;
1359: CLOSE c_lookup;
1360:
1361: IF (l_row_not_found) THEN
1362: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1363: p_msg_name => G_INVALID_VALUE,
1364: p_token1 => G_COL_NAME_TOKEN,
1365: p_token1_value => 'OKL_CHECKLIST_DETAILS.FUNCTION_VALIDATE_RSTS');
1366:
1371: RETURN l_return_status;
1372:
1373: EXCEPTION
1374: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1375: l_return_status := OKL_API.G_RET_STS_ERROR;
1376: RETURN l_return_status;
1377: WHEN OTHERS THEN
1378: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1379: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1374: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1375: l_return_status := OKL_API.G_RET_STS_ERROR;
1376: RETURN l_return_status;
1377: WHEN OTHERS THEN
1378: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1379: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1380: p_msg_name => G_UNEXPECTED_ERROR,
1381: p_token1 => G_SQLCODE_TOKEN,
1382: p_token1_value => SQLCODE,
1375: l_return_status := OKL_API.G_RET_STS_ERROR;
1376: RETURN l_return_status;
1377: WHEN OTHERS THEN
1378: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1379: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1380: p_msg_name => G_UNEXPECTED_ERROR,
1381: p_token1 => G_SQLCODE_TOKEN,
1382: p_token1_value => SQLCODE,
1383: p_token2 => G_SQLERRM_TOKEN,
1391: p_cldv_rec cldv_rec_type
1392: ,p_mode VARCHAR2
1393: ) RETURN VARCHAR2
1394: IS
1395: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1396: l_dummy number;
1397:
1398: l_row_not_found boolean := false;
1399:
1408:
1409: -- FK check
1410: -- check only if object exists
1411: IF (p_cldv_rec.FUNCTION_ID IS NOT NULL AND
1412: p_cldv_rec.FUNCTION_ID <> OKL_API.G_MISS_NUM)
1413: THEN
1414:
1415: OPEN c_fun(p_cldv_rec.FUNCTION_ID);
1416: FETCH c_fun INTO l_dummy;
1417: l_row_not_found := c_fun%NOTFOUND;
1418: CLOSE c_fun;
1419:
1420: IF (l_row_not_found) THEN
1421: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1422: p_msg_name => G_INVALID_VALUE,
1423: p_token1 => G_COL_NAME_TOKEN,
1424: p_token1_value => 'OKL_CHECKLIST_DETAILS.FUNCTION_ID');
1425:
1430: RETURN l_return_status;
1431:
1432: EXCEPTION
1433: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1434: l_return_status := OKL_API.G_RET_STS_ERROR;
1435: RETURN l_return_status;
1436: WHEN OTHERS THEN
1437: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1438: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1433: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1434: l_return_status := OKL_API.G_RET_STS_ERROR;
1435: RETURN l_return_status;
1436: WHEN OTHERS THEN
1437: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1438: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1439: p_msg_name => G_UNEXPECTED_ERROR,
1440: p_token1 => G_SQLCODE_TOKEN,
1441: p_token1_value => SQLCODE,
1434: l_return_status := OKL_API.G_RET_STS_ERROR;
1435: RETURN l_return_status;
1436: WHEN OTHERS THEN
1437: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1438: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1439: p_msg_name => G_UNEXPECTED_ERROR,
1440: p_token1 => G_SQLCODE_TOKEN,
1441: p_token1_value => SQLCODE,
1442: p_token2 => G_SQLERRM_TOKEN,
1450: p_cldv_rec cldv_rec_type
1451: ,p_mode VARCHAR2
1452: ) RETURN VARCHAR2
1453: IS
1454: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1455: l_count number := 0;
1456: l_row_found boolean := false;
1457: l_dummy number;
1458:
1473: l_row_found := c_grp_clist%FOUND;
1474: CLOSE c_grp_clist;
1475:
1476: IF (l_row_found) THEN
1477: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1478: p_msg_name => 'OKL_CHK_GRP_CLIST_ITEMS');
1479:
1480: RAISE G_EXCEPTION_HALT_VALIDATION;
1481: END IF;
1483: RETURN l_return_status;
1484:
1485: EXCEPTION
1486: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1487: l_return_status := OKL_API.G_RET_STS_ERROR;
1488: RETURN l_return_status;
1489: WHEN OTHERS THEN
1490: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1491: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1486: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1487: l_return_status := OKL_API.G_RET_STS_ERROR;
1488: RETURN l_return_status;
1489: WHEN OTHERS THEN
1490: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1491: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1492: p_msg_name => G_UNEXPECTED_ERROR,
1493: p_token1 => G_SQLCODE_TOKEN,
1494: p_token1_value => SQLCODE,
1487: l_return_status := OKL_API.G_RET_STS_ERROR;
1488: RETURN l_return_status;
1489: WHEN OTHERS THEN
1490: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1491: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1492: p_msg_name => G_UNEXPECTED_ERROR,
1493: p_token1 => G_SQLCODE_TOKEN,
1494: p_token1_value => SQLCODE,
1495: p_token2 => G_SQLERRM_TOKEN,
1503: p_cldv_rec cldv_rec_type
1504: ,p_mode VARCHAR2
1505: ) RETURN VARCHAR2
1506: IS
1507: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1508: l_dummy number;
1509: l_purpose okl_checklists.checklist_purpose_code%type;
1510:
1511: l_row_not_found boolean := false;
1537:
1538: -- column is required:
1539: IF l_purpose = 'CHECKLIST_INSTANCE' AND
1540: (p_cldv_rec.inst_checklist_type IS NULL OR
1541: p_cldv_rec.inst_checklist_type = OKL_API.G_MISS_CHAR)
1542: THEN
1543: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1544: p_msg_name => G_REQUIRED_VALUE,
1545: p_token1 => G_COL_NAME_TOKEN,
1539: IF l_purpose = 'CHECKLIST_INSTANCE' AND
1540: (p_cldv_rec.inst_checklist_type IS NULL OR
1541: p_cldv_rec.inst_checklist_type = OKL_API.G_MISS_CHAR)
1542: THEN
1543: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1544: p_msg_name => G_REQUIRED_VALUE,
1545: p_token1 => G_COL_NAME_TOKEN,
1546: p_token1_value => 'Checklist Type');
1547: RAISE G_EXCEPTION_HALT_VALIDATION;
1553:
1554: -- column is required:
1555: IF (l_purpose = 'CHECKLIST_INSTANCE' AND p_cldv_rec.inst_checklist_type IS NULL)
1556: THEN
1557: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1558: p_msg_name => G_REQUIRED_VALUE,
1559: p_token1 => G_COL_NAME_TOKEN,
1560: p_token1_value => 'Checklist Type');
1561: RAISE G_EXCEPTION_HALT_VALIDATION;
1567:
1568: -- FK check
1569: -- check only if checklist type exists
1570: IF (p_cldv_rec.inst_checklist_type IS NOT NULL AND
1571: p_cldv_rec.inst_checklist_type <> OKL_API.G_MISS_CHAR)
1572: THEN
1573:
1574: OPEN c_type(p_cldv_rec.inst_checklist_type);
1575: FETCH c_type INTO l_dummy;
1576: l_row_not_found := c_type%NOTFOUND;
1577: CLOSE c_type;
1578:
1579: IF (l_row_not_found) THEN
1580: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1581: p_msg_name => G_INVALID_VALUE,
1582: p_token1 => G_COL_NAME_TOKEN,
1583: p_token1_value => 'Checklist Type');
1584:
1589: RETURN l_return_status;
1590:
1591: EXCEPTION
1592: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1593: l_return_status := OKL_API.G_RET_STS_ERROR;
1594: RETURN l_return_status;
1595: WHEN OTHERS THEN
1596: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1597: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1592: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1593: l_return_status := OKL_API.G_RET_STS_ERROR;
1594: RETURN l_return_status;
1595: WHEN OTHERS THEN
1596: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1597: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1598: p_msg_name => G_UNEXPECTED_ERROR,
1599: p_token1 => G_SQLCODE_TOKEN,
1600: p_token1_value => SQLCODE,
1593: l_return_status := OKL_API.G_RET_STS_ERROR;
1594: RETURN l_return_status;
1595: WHEN OTHERS THEN
1596: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1597: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1598: p_msg_name => G_UNEXPECTED_ERROR,
1599: p_token1 => G_SQLCODE_TOKEN,
1600: p_token1_value => SQLCODE,
1601: p_token2 => G_SQLERRM_TOKEN,
1609: p_cldv_rec cldv_rec_type
1610: ,p_mode VARCHAR2
1611: ) RETURN VARCHAR2
1612: IS
1613: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1614: l_dummy number;
1615:
1616: l_row_not_found boolean := false;
1617:
1628: IF (p_mode = G_INSERT_MODE) THEN
1629:
1630: -- column is required:
1631: IF (p_cldv_rec.todo_item_code IS NULL) OR
1632: (p_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
1633: THEN
1634: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1635: p_msg_name => G_REQUIRED_VALUE,
1636: p_token1 => G_COL_NAME_TOKEN,
1630: -- column is required:
1631: IF (p_cldv_rec.todo_item_code IS NULL) OR
1632: (p_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
1633: THEN
1634: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1635: p_msg_name => G_REQUIRED_VALUE,
1636: p_token1 => G_COL_NAME_TOKEN,
1637: p_token1_value => 'Item Code');
1638: RAISE G_EXCEPTION_HALT_VALIDATION;
1643:
1644: -- column is required:
1645: IF (p_cldv_rec.todo_item_code IS NULL)
1646: THEN
1647: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1648: p_msg_name => G_REQUIRED_VALUE,
1649: p_token1 => G_COL_NAME_TOKEN,
1650: p_token1_value => 'Item Code');
1651: RAISE G_EXCEPTION_HALT_VALIDATION;
1655:
1656: -- FK check
1657: -- check only if column exists
1658: IF (p_cldv_rec.todo_item_code IS NOT NULL AND
1659: p_cldv_rec.todo_item_code <> OKL_API.G_MISS_CHAR)
1660: THEN
1661:
1662: OPEN c_todo (p_cldv_rec.todo_item_code);
1663: FETCH c_todo INTO l_dummy;
1664: l_row_not_found := c_todo%NOTFOUND;
1665: CLOSE c_todo;
1666:
1667: IF (l_row_not_found) THEN
1668: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1669: p_msg_name => G_INVALID_VALUE,
1670: p_token1 => G_COL_NAME_TOKEN,
1671: p_token1_value => 'Item Code');
1672:
1678: RETURN l_return_status;
1679:
1680: EXCEPTION
1681: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1682: l_return_status := OKL_API.G_RET_STS_ERROR;
1683: RETURN l_return_status;
1684: WHEN OTHERS THEN
1685: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1686: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1681: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1682: l_return_status := OKL_API.G_RET_STS_ERROR;
1683: RETURN l_return_status;
1684: WHEN OTHERS THEN
1685: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1686: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1687: p_msg_name => G_UNEXPECTED_ERROR,
1688: p_token1 => G_SQLCODE_TOKEN,
1689: p_token1_value => SQLCODE,
1682: l_return_status := OKL_API.G_RET_STS_ERROR;
1683: RETURN l_return_status;
1684: WHEN OTHERS THEN
1685: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1686: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1687: p_msg_name => G_UNEXPECTED_ERROR,
1688: p_token1 => G_SQLCODE_TOKEN,
1689: p_token1_value => SQLCODE,
1690: p_token2 => G_SQLERRM_TOKEN,
1700: p_cldv_rec cldv_rec_type
1701: ,p_mode VARCHAR2
1702: ) RETURN VARCHAR2
1703: IS
1704: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1705: l_dummy number;
1706: l_todo_start_date date;
1707: l_todo_end_date date;
1708: l_clh_start_date date;
1734: IF (p_mode = G_INSERT_MODE) THEN
1735:
1736: -- column is required:
1737: IF (p_cldv_rec.todo_item_code IS NULL) OR
1738: (p_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
1739: THEN
1740: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1741: p_msg_name => G_REQUIRED_VALUE,
1742: p_token1 => G_COL_NAME_TOKEN,
1736: -- column is required:
1737: IF (p_cldv_rec.todo_item_code IS NULL) OR
1738: (p_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
1739: THEN
1740: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1741: p_msg_name => G_REQUIRED_VALUE,
1742: p_token1 => G_COL_NAME_TOKEN,
1743: p_token1_value => 'Item Code');
1744: RAISE G_EXCEPTION_HALT_VALIDATION;
1748:
1749: -- column is required:
1750: IF (p_cldv_rec.todo_item_code IS NULL)
1751: THEN
1752: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1753: p_msg_name => G_REQUIRED_VALUE,
1754: p_token1 => G_COL_NAME_TOKEN,
1755: p_token1_value => 'Item Code');
1756: RAISE G_EXCEPTION_HALT_VALIDATION;
1760:
1761: -- FK check
1762: -- check only if column exists
1763: IF (p_cldv_rec.todo_item_code IS NOT NULL AND
1764: p_cldv_rec.todo_item_code <> OKL_API.G_MISS_CHAR)
1765: THEN
1766:
1767: OPEN c_todo (p_cldv_rec.todo_item_code, p_cldv_rec.ckl_id);
1768: FETCH c_todo INTO l_todo_start_date,
1772: l_row_not_found := c_todo%NOTFOUND;
1773: CLOSE c_todo;
1774:
1775: IF (l_row_not_found) THEN
1776: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1777: p_msg_name => G_INVALID_VALUE,
1778: p_token1 => G_COL_NAME_TOKEN,
1779: p_token1_value => 'Item Code');
1780:
1793: RETURN l_return_status;
1794:
1795: EXCEPTION
1796: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1797: l_return_status := OKL_API.G_RET_STS_ERROR;
1798: RETURN l_return_status;
1799: WHEN OTHERS THEN
1800: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1801: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1796: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1797: l_return_status := OKL_API.G_RET_STS_ERROR;
1798: RETURN l_return_status;
1799: WHEN OTHERS THEN
1800: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1801: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1802: p_msg_name => G_UNEXPECTED_ERROR,
1803: p_token1 => G_SQLCODE_TOKEN,
1804: p_token1_value => SQLCODE,
1797: l_return_status := OKL_API.G_RET_STS_ERROR;
1798: RETURN l_return_status;
1799: WHEN OTHERS THEN
1800: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1801: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1802: p_msg_name => G_UNEXPECTED_ERROR,
1803: p_token1 => G_SQLCODE_TOKEN,
1804: p_token1_value => SQLCODE,
1805: p_token2 => G_SQLERRM_TOKEN,
1815: p_cldv_rec cldv_rec_type
1816: ,p_mode VARCHAR2
1817: ) RETURN VARCHAR2
1818: IS
1819: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1820: l_dummy number;
1821:
1822: l_row_found boolean := false;
1823: l_checklist_number okl_checklists.checklist_number%type;
1871: l_row_found := c_todo_tmp_chk%FOUND;
1872: CLOSE c_todo_tmp_chk ;
1873:
1874: IF (l_row_found) THEN
1875: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1876: p_msg_name => 'OKL_CHECKLIST_ITEM_DATE_CHECK',
1877: p_token1 => 'ITEM',
1878: p_token1_value => l_todo_item_code,
1879: p_token2 => 'NAME',
1892: l_row_found := c_todo_grp_chk%FOUND;
1893: CLOSE c_todo_grp_chk ;
1894:
1895: IF (l_row_found) THEN
1896: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1897: p_msg_name => 'OKL_CHECKLIST_ITEM_DATE_CHECK2',
1898: p_token1 => 'ITEM',
1899: p_token1_value => l_todo_item_code,
1900: p_token2 => 'NAME',
1906: RETURN l_return_status;
1907:
1908: EXCEPTION
1909: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1910: l_return_status := OKL_API.G_RET_STS_ERROR;
1911: RETURN l_return_status;
1912: WHEN OTHERS THEN
1913: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1914: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1909: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1910: l_return_status := OKL_API.G_RET_STS_ERROR;
1911: RETURN l_return_status;
1912: WHEN OTHERS THEN
1913: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1914: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1915: p_msg_name => G_UNEXPECTED_ERROR,
1916: p_token1 => G_SQLCODE_TOKEN,
1917: p_token1_value => SQLCODE,
1910: l_return_status := OKL_API.G_RET_STS_ERROR;
1911: RETURN l_return_status;
1912: WHEN OTHERS THEN
1913: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1914: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1915: p_msg_name => G_UNEXPECTED_ERROR,
1916: p_token1 => G_SQLCODE_TOKEN,
1917: p_token1_value => SQLCODE,
1918: p_token2 => G_SQLERRM_TOKEN,
1929: p_clhv_rec clhv_rec_type
1930: ,p_mode VARCHAR2
1931: ) RETURN VARCHAR2
1932: IS
1933: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1934: l_dummy number;
1935:
1936: l_cldv_rec cldv_rec_type;
1937:
1959:
1960: l_cldv_rec.id := r_this_row.id;
1961: l_return_status := validate_item_effective_from(l_cldv_rec, G_UPDATE_MODE);
1962: --- Store the highest degree of error
1963: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1964: RAISE G_EXCEPTION_HALT_VALIDATION;
1965: END IF;
1966: END LOOP;
1967:
1968: RETURN l_return_status;
1969:
1970: EXCEPTION
1971: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1972: l_return_status := OKL_API.G_RET_STS_ERROR;
1973: RETURN l_return_status;
1974: WHEN OTHERS THEN
1975: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1976: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1971: WHEN G_EXCEPTION_HALT_VALIDATION THEN
1972: l_return_status := OKL_API.G_RET_STS_ERROR;
1973: RETURN l_return_status;
1974: WHEN OTHERS THEN
1975: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1976: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1977: p_msg_name => G_UNEXPECTED_ERROR,
1978: p_token1 => G_SQLCODE_TOKEN,
1979: p_token1_value => SQLCODE,
1972: l_return_status := OKL_API.G_RET_STS_ERROR;
1973: RETURN l_return_status;
1974: WHEN OTHERS THEN
1975: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1976: OKL_API.Set_Message(p_app_name => G_APP_NAME,
1977: p_msg_name => G_UNEXPECTED_ERROR,
1978: p_token1 => G_SQLCODE_TOKEN,
1979: p_token1_value => SQLCODE,
1980: p_token2 => G_SQLERRM_TOKEN,
1990: p_cldv_rec cldv_rec_type
1991: ,p_mode VARCHAR2
1992: ) RETURN VARCHAR2
1993: IS
1994: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1995: l_count number := 0;
1996: l_row_found boolean := false;
1997: l_dummy number;
1998:
2015: l_row_found := c_unq%FOUND;
2016: CLOSE c_unq;
2017:
2018: IF (l_row_found) THEN
2019: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2020: p_msg_name => G_NOT_UNIQUE,
2021: p_token1 => G_COL_NAME_TOKEN,
2022: -- START: 06-Jan-2006 cklee -- Fixed for instance checklist item duplication check |
2023: -- p_token1_value => 'Item Code and Function');
2030: RETURN l_return_status;
2031:
2032: EXCEPTION
2033: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2034: l_return_status := OKL_API.G_RET_STS_ERROR;
2035: RETURN l_return_status;
2036: WHEN OTHERS THEN
2037: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2038: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2033: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2034: l_return_status := OKL_API.G_RET_STS_ERROR;
2035: RETURN l_return_status;
2036: WHEN OTHERS THEN
2037: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2038: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2039: p_msg_name => G_UNEXPECTED_ERROR,
2040: p_token1 => G_SQLCODE_TOKEN,
2041: p_token1_value => SQLCODE,
2034: l_return_status := OKL_API.G_RET_STS_ERROR;
2035: RETURN l_return_status;
2036: WHEN OTHERS THEN
2037: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2038: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2039: p_msg_name => G_UNEXPECTED_ERROR,
2040: p_token1 => G_SQLCODE_TOKEN,
2041: p_token1_value => SQLCODE,
2042: p_token2 => G_SQLERRM_TOKEN,
2051: p_cldv_rec cldv_rec_type
2052: ,p_mode VARCHAR2
2053: ) RETURN VARCHAR2
2054: IS
2055: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2056: l_dummy NUMBER;
2057:
2058: l_row_not_found BOOLEAN := FALSE;
2059:
2065: AND lok.lookup_code = p_lookup_code;
2066: BEGIN
2067: -- check only if object exists
2068: IF (p_cldv_rec.APPEAL_FLAG IS NOT NULL AND
2069: p_cldv_rec.APPEAL_FLAG <> OKL_API.G_MISS_CHAR)
2070: THEN
2071: OPEN c_lookup(p_cldv_rec.APPEAL_FLAG);
2072: FETCH c_lookup INTO l_dummy;
2073: l_row_not_found := c_lookup%NOTFOUND;
2073: l_row_not_found := c_lookup%NOTFOUND;
2074: CLOSE c_lookup;
2075: IF (l_row_not_found)
2076: THEN
2077: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2078: p_msg_name => G_INVALID_VALUE,
2079: p_token1 => G_COL_NAME_TOKEN,
2080: p_token1_value => 'OKL_CHECKLIST_DETAILS.APPEAL_FLAG');
2081: RAISE G_EXCEPTION_HALT_VALIDATION;
2083: END IF;
2084: RETURN l_return_status;
2085: EXCEPTION
2086: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2087: l_return_status := OKL_API.G_RET_STS_ERROR;
2088: RETURN l_return_status;
2089: WHEN OTHERS THEN
2090: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2091: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2086: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2087: l_return_status := OKL_API.G_RET_STS_ERROR;
2088: RETURN l_return_status;
2089: WHEN OTHERS THEN
2090: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2091: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2092: p_msg_name => G_UNEXPECTED_ERROR,
2093: p_token1 => G_SQLCODE_TOKEN,
2094: p_token1_value => SQLCODE,
2087: l_return_status := OKL_API.G_RET_STS_ERROR;
2088: RETURN l_return_status;
2089: WHEN OTHERS THEN
2090: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2091: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2092: p_msg_name => G_UNEXPECTED_ERROR,
2093: p_token1 => G_SQLCODE_TOKEN,
2094: p_token1_value => SQLCODE,
2095: p_token2 => G_SQLERRM_TOKEN,
2102: p_clhv_rec clhv_rec_type
2103: ,p_mode VARCHAR2
2104: ) RETURN VARCHAR2
2105: IS
2106: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2107: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2108:
2109: BEGIN
2110:
2103: ,p_mode VARCHAR2
2104: ) RETURN VARCHAR2
2105: IS
2106: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2107: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2108:
2109: BEGIN
2110:
2111: -- Do formal attribute validation:
2111: -- Do formal attribute validation:
2112:
2113: l_return_status := validate_clh_number(p_clhv_rec, p_mode);
2114: --- Store the highest degree of error
2115: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2116: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2117: x_return_status := l_return_status;
2118: END IF;
2119: RAISE G_EXCEPTION_HALT_VALIDATION;
2112:
2113: l_return_status := validate_clh_number(p_clhv_rec, p_mode);
2114: --- Store the highest degree of error
2115: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2116: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2117: x_return_status := l_return_status;
2118: END IF;
2119: RAISE G_EXCEPTION_HALT_VALIDATION;
2120: END IF;
2120: END IF;
2121:
2122: l_return_status := validate_clh_type(p_clhv_rec, p_mode);
2123: --- Store the highest degree of error
2124: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2125: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2126: x_return_status := l_return_status;
2127: END IF;
2128: RAISE G_EXCEPTION_HALT_VALIDATION;
2121:
2122: l_return_status := validate_clh_type(p_clhv_rec, p_mode);
2123: --- Store the highest degree of error
2124: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2125: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2126: x_return_status := l_return_status;
2127: END IF;
2128: RAISE G_EXCEPTION_HALT_VALIDATION;
2129: END IF;
2129: END IF;
2130:
2131: l_return_status := validate_short_desc(p_clhv_rec, p_mode);
2132: --- Store the highest degree of error
2133: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2134: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2135: x_return_status := l_return_status;
2136: END IF;
2137: RAISE G_EXCEPTION_HALT_VALIDATION;
2130:
2131: l_return_status := validate_short_desc(p_clhv_rec, p_mode);
2132: --- Store the highest degree of error
2133: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2134: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2135: x_return_status := l_return_status;
2136: END IF;
2137: RAISE G_EXCEPTION_HALT_VALIDATION;
2138: END IF;
2138: END IF;
2139:
2140: l_return_status := validate_description(p_clhv_rec, p_mode);
2141: --- Store the highest degree of error
2142: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2143: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2144: x_return_status := l_return_status;
2145: END IF;
2146: RAISE G_EXCEPTION_HALT_VALIDATION;
2139:
2140: l_return_status := validate_description(p_clhv_rec, p_mode);
2141: --- Store the highest degree of error
2142: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2143: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2144: x_return_status := l_return_status;
2145: END IF;
2146: RAISE G_EXCEPTION_HALT_VALIDATION;
2147: END IF;
2147: END IF;
2148:
2149: l_return_status := validate_effective_to(p_clhv_rec, p_mode);
2150: --- Store the highest degree of error
2151: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2152: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2153: x_return_status := l_return_status;
2154: END IF;
2155: RAISE G_EXCEPTION_HALT_VALIDATION;
2148:
2149: l_return_status := validate_effective_to(p_clhv_rec, p_mode);
2150: --- Store the highest degree of error
2151: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2152: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2153: x_return_status := l_return_status;
2154: END IF;
2155: RAISE G_EXCEPTION_HALT_VALIDATION;
2156: END IF;
2157: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2158:
2159: l_return_status := validate_status_code(p_clhv_rec, p_mode);
2160: --- Store the highest degree of error
2161: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2162: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2163: x_return_status := l_return_status;
2164: END IF;
2165: RAISE G_EXCEPTION_HALT_VALIDATION;
2158:
2159: l_return_status := validate_status_code(p_clhv_rec, p_mode);
2160: --- Store the highest degree of error
2161: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2162: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2163: x_return_status := l_return_status;
2164: END IF;
2165: RAISE G_EXCEPTION_HALT_VALIDATION;
2166: END IF;
2166: END IF;
2167:
2168: l_return_status := validate_purpose_code(p_clhv_rec, p_mode);
2169: --- Store the highest degree of error
2170: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2171: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2172: x_return_status := l_return_status;
2173: END IF;
2174: RAISE G_EXCEPTION_HALT_VALIDATION;
2167:
2168: l_return_status := validate_purpose_code(p_clhv_rec, p_mode);
2169: --- Store the highest degree of error
2170: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2171: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2172: x_return_status := l_return_status;
2173: END IF;
2174: RAISE G_EXCEPTION_HALT_VALIDATION;
2175: END IF;
2175: END IF;
2176:
2177: l_return_status := validate_obj_ID(p_clhv_rec, p_mode);
2178: --- Store the highest degree of error
2179: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2180: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2181: x_return_status := l_return_status;
2182: END IF;
2183: RAISE G_EXCEPTION_HALT_VALIDATION;
2176:
2177: l_return_status := validate_obj_ID(p_clhv_rec, p_mode);
2178: --- Store the highest degree of error
2179: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2180: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2181: x_return_status := l_return_status;
2182: END IF;
2183: RAISE G_EXCEPTION_HALT_VALIDATION;
2184: END IF;
2184: END IF;
2185:
2186: l_return_status := validate_obj_type_code(p_clhv_rec, p_mode);
2187: --- Store the highest degree of error
2188: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2189: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2190: x_return_status := l_return_status;
2191: END IF;
2192: RAISE G_EXCEPTION_HALT_VALIDATION;
2185:
2186: l_return_status := validate_obj_type_code(p_clhv_rec, p_mode);
2187: --- Store the highest degree of error
2188: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2189: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2190: x_return_status := l_return_status;
2191: END IF;
2192: RAISE G_EXCEPTION_HALT_VALIDATION;
2193: END IF;
2193: END IF;
2194:
2195: l_return_status := validate_ckl_id(p_clhv_rec, p_mode);
2196: --- Store the highest degree of error
2197: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2198: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2199: x_return_status := l_return_status;
2200: END IF;
2201: RAISE G_EXCEPTION_HALT_VALIDATION;
2194:
2195: l_return_status := validate_ckl_id(p_clhv_rec, p_mode);
2196: --- Store the highest degree of error
2197: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2198: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2199: x_return_status := l_return_status;
2200: END IF;
2201: RAISE G_EXCEPTION_HALT_VALIDATION;
2202: END IF;
2206: EXCEPTION
2207: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2208: RETURN x_return_status;
2209: WHEN OTHERS THEN
2210: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2211: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2212: p_msg_name => G_UNEXPECTED_ERROR,
2213: p_token1 => G_SQLCODE_TOKEN,
2214: p_token1_value => SQLCODE,
2207: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2208: RETURN x_return_status;
2209: WHEN OTHERS THEN
2210: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2211: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2212: p_msg_name => G_UNEXPECTED_ERROR,
2213: p_token1 => G_SQLCODE_TOKEN,
2214: p_token1_value => SQLCODE,
2215: p_token2 => G_SQLERRM_TOKEN,
2223: p_clhv_rec clhv_rec_type
2224: ,p_mode VARCHAR2
2225: ) RETURN VARCHAR2
2226: IS
2227: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2228: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2229:
2230: BEGIN
2231:
2224: ,p_mode VARCHAR2
2225: ) RETURN VARCHAR2
2226: IS
2227: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2228: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2229:
2230: BEGIN
2231:
2232: -- Do formal attribute validation:
2231:
2232: -- Do formal attribute validation:
2233: l_return_status := validate_unq_clh_number(p_clhv_rec, p_mode);
2234: --- Store the highest degree of error
2235: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2236: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2237: x_return_status := l_return_status;
2238: END IF;
2239: RAISE G_EXCEPTION_HALT_VALIDATION;
2232: -- Do formal attribute validation:
2233: l_return_status := validate_unq_clh_number(p_clhv_rec, p_mode);
2234: --- Store the highest degree of error
2235: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2236: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2237: x_return_status := l_return_status;
2238: END IF;
2239: RAISE G_EXCEPTION_HALT_VALIDATION;
2240: END IF;
2240: END IF;
2241:
2242: l_return_status := validate_effective_date(p_clhv_rec, p_mode);
2243: --- Store the highest degree of error
2244: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2245: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2246: x_return_status := l_return_status;
2247: END IF;
2248: RAISE G_EXCEPTION_HALT_VALIDATION;
2241:
2242: l_return_status := validate_effective_date(p_clhv_rec, p_mode);
2243: --- Store the highest degree of error
2244: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2245: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2246: x_return_status := l_return_status;
2247: END IF;
2248: RAISE G_EXCEPTION_HALT_VALIDATION;
2249: END IF;
2250:
2251: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2252: l_return_status := validate_dates_overlap(p_clhv_rec, p_mode);
2253: --- Store the highest degree of error
2254: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2255: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2256: x_return_status := l_return_status;
2257: END IF;
2258: RAISE G_EXCEPTION_HALT_VALIDATION;
2251: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2252: l_return_status := validate_dates_overlap(p_clhv_rec, p_mode);
2253: --- Store the highest degree of error
2254: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2255: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2256: x_return_status := l_return_status;
2257: END IF;
2258: RAISE G_EXCEPTION_HALT_VALIDATION;
2259: END IF;
2262:
2263: --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
2264: l_return_status := validate_dates_w_item(p_clhv_rec, p_mode);
2265: --- Store the highest degree of error
2266: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2267: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2268: x_return_status := l_return_status;
2269: END IF;
2270: RAISE G_EXCEPTION_HALT_VALIDATION;
2263: --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
2264: l_return_status := validate_dates_w_item(p_clhv_rec, p_mode);
2265: --- Store the highest degree of error
2266: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2267: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2268: x_return_status := l_return_status;
2269: END IF;
2270: RAISE G_EXCEPTION_HALT_VALIDATION;
2271: END IF;
2275: EXCEPTION
2276: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2277: RETURN x_return_status;
2278: WHEN OTHERS THEN
2279: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2280: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2281: p_msg_name => G_UNEXPECTED_ERROR,
2282: p_token1 => G_SQLCODE_TOKEN,
2283: p_token1_value => SQLCODE,
2276: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2277: RETURN x_return_status;
2278: WHEN OTHERS THEN
2279: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2280: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2281: p_msg_name => G_UNEXPECTED_ERROR,
2282: p_token1 => G_SQLCODE_TOKEN,
2283: p_token1_value => SQLCODE,
2284: p_token2 => G_SQLERRM_TOKEN,
2294: p_cldv_rec cldv_rec_type
2295: ,p_mode VARCHAR2
2296: ) RETURN VARCHAR2
2297: IS
2298: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2299: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2300:
2301: BEGIN
2302:
2295: ,p_mode VARCHAR2
2296: ) RETURN VARCHAR2
2297: IS
2298: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2299: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2300:
2301: BEGIN
2302:
2303: -- Do formal attribute validation:
2302:
2303: -- Do formal attribute validation:
2304: l_return_status := validate_todo_item_code(p_cldv_rec, p_mode);
2305: --- Store the highest degree of error
2306: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2307: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2308: x_return_status := l_return_status;
2309: END IF;
2310: RAISE G_EXCEPTION_HALT_VALIDATION;
2303: -- Do formal attribute validation:
2304: l_return_status := validate_todo_item_code(p_cldv_rec, p_mode);
2305: --- Store the highest degree of error
2306: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2307: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2308: x_return_status := l_return_status;
2309: END IF;
2310: RAISE G_EXCEPTION_HALT_VALIDATION;
2311: END IF;
2312:
2313: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2314: l_return_status := validate_MANDATORY_FLAG(p_cldv_rec, p_mode);
2315: --- Store the highest degree of error
2316: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2317: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2318: x_return_status := l_return_status;
2319: END IF;
2320: RAISE G_EXCEPTION_HALT_VALIDATION;
2313: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2314: l_return_status := validate_MANDATORY_FLAG(p_cldv_rec, p_mode);
2315: --- Store the highest degree of error
2316: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2317: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2318: x_return_status := l_return_status;
2319: END IF;
2320: RAISE G_EXCEPTION_HALT_VALIDATION;
2321: END IF;
2321: END IF;
2322:
2323: l_return_status := validate_COMPLETE_FLAG(p_cldv_rec, p_mode);
2324: --- Store the highest degree of error
2325: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2326: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2327: x_return_status := l_return_status;
2328: END IF;
2329: RAISE G_EXCEPTION_HALT_VALIDATION;
2322:
2323: l_return_status := validate_COMPLETE_FLAG(p_cldv_rec, p_mode);
2324: --- Store the highest degree of error
2325: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2326: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2327: x_return_status := l_return_status;
2328: END IF;
2329: RAISE G_EXCEPTION_HALT_VALIDATION;
2330: END IF;
2330: END IF;
2331:
2332: l_return_status := validate_FUN_VALIDATE_RSTS(p_cldv_rec, p_mode);
2333: --- Store the highest degree of error
2334: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2335: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2336: x_return_status := l_return_status;
2337: END IF;
2338: RAISE G_EXCEPTION_HALT_VALIDATION;
2331:
2332: l_return_status := validate_FUN_VALIDATE_RSTS(p_cldv_rec, p_mode);
2333: --- Store the highest degree of error
2334: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2335: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2336: x_return_status := l_return_status;
2337: END IF;
2338: RAISE G_EXCEPTION_HALT_VALIDATION;
2339: END IF;
2339: END IF;
2340:
2341: l_return_status := validate_FUNCTION_ID(p_cldv_rec, p_mode);
2342: --- Store the highest degree of error
2343: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2344: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2345: x_return_status := l_return_status;
2346: END IF;
2347: RAISE G_EXCEPTION_HALT_VALIDATION;
2340:
2341: l_return_status := validate_FUNCTION_ID(p_cldv_rec, p_mode);
2342: --- Store the highest degree of error
2343: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2344: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2345: x_return_status := l_return_status;
2346: END IF;
2347: RAISE G_EXCEPTION_HALT_VALIDATION;
2348: END IF;
2348: END IF;
2349:
2350: l_return_status := validate_inst_clh_type(p_cldv_rec, p_mode);
2351: --- Store the highest degree of error
2352: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2353: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2354: x_return_status := l_return_status;
2355: END IF;
2356: RAISE G_EXCEPTION_HALT_VALIDATION;
2349:
2350: l_return_status := validate_inst_clh_type(p_cldv_rec, p_mode);
2351: --- Store the highest degree of error
2352: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2353: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2354: x_return_status := l_return_status;
2355: END IF;
2356: RAISE G_EXCEPTION_HALT_VALIDATION;
2357: END IF;
2358: -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2359: --PAGARG: Bug 4872271: call procedure to validate value of APPEAL_FLAG
2360: l_return_status := validate_APPEAL_FLAG(p_cldv_rec, p_mode);
2361: -- Store the highest degree of error
2362: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2363: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2364: x_return_status := l_return_status;
2365: END IF;
2366: RAISE G_EXCEPTION_HALT_VALIDATION;
2359: --PAGARG: Bug 4872271: call procedure to validate value of APPEAL_FLAG
2360: l_return_status := validate_APPEAL_FLAG(p_cldv_rec, p_mode);
2361: -- Store the highest degree of error
2362: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2363: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2364: x_return_status := l_return_status;
2365: END IF;
2366: RAISE G_EXCEPTION_HALT_VALIDATION;
2367: END IF;
2370: EXCEPTION
2371: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2372: RETURN x_return_status;
2373: WHEN OTHERS THEN
2374: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2375: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2376: p_msg_name => G_UNEXPECTED_ERROR,
2377: p_token1 => G_SQLCODE_TOKEN,
2378: p_token1_value => SQLCODE,
2371: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2372: RETURN x_return_status;
2373: WHEN OTHERS THEN
2374: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2375: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2376: p_msg_name => G_UNEXPECTED_ERROR,
2377: p_token1 => G_SQLCODE_TOKEN,
2378: p_token1_value => SQLCODE,
2379: p_token2 => G_SQLERRM_TOKEN,
2389: p_cldv_rec cldv_rec_type
2390: ,p_mode VARCHAR2
2391: ) RETURN VARCHAR2
2392: IS
2393: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2394: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2395:
2396: BEGIN
2397:
2390: ,p_mode VARCHAR2
2391: ) RETURN VARCHAR2
2392: IS
2393: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2394: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2395:
2396: BEGIN
2397:
2398: -- Do formal attribute validation:
2397:
2398: -- Do formal attribute validation:
2399: l_return_status := validate_unq_todo_item(p_cldv_rec, p_mode);
2400: --- Store the highest degree of error
2401: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2402: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2403: x_return_status := l_return_status;
2404: END IF;
2405: RAISE G_EXCEPTION_HALT_VALIDATION;
2398: -- Do formal attribute validation:
2399: l_return_status := validate_unq_todo_item(p_cldv_rec, p_mode);
2400: --- Store the highest degree of error
2401: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2402: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2403: x_return_status := l_return_status;
2404: END IF;
2405: RAISE G_EXCEPTION_HALT_VALIDATION;
2406: END IF;
2407:
2408: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2409: l_return_status := validate_purpose_code(p_cldv_rec, p_mode);
2410: --- Store the highest degree of error
2411: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2412: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2413: x_return_status := l_return_status;
2414: END IF;
2415: RAISE G_EXCEPTION_HALT_VALIDATION;
2408: -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2409: l_return_status := validate_purpose_code(p_cldv_rec, p_mode);
2410: --- Store the highest degree of error
2411: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2412: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2413: x_return_status := l_return_status;
2414: END IF;
2415: RAISE G_EXCEPTION_HALT_VALIDATION;
2416: END IF;
2416: END IF;
2417:
2418: l_return_status := validate_duplicated_keys(p_cldv_rec, p_mode);
2419: --- Store the highest degree of error
2420: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2421: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2422: x_return_status := l_return_status;
2423: END IF;
2424: RAISE G_EXCEPTION_HALT_VALIDATION;
2417:
2418: l_return_status := validate_duplicated_keys(p_cldv_rec, p_mode);
2419: --- Store the highest degree of error
2420: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2421: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2422: x_return_status := l_return_status;
2423: END IF;
2424: RAISE G_EXCEPTION_HALT_VALIDATION;
2425: END IF;
2427:
2428: --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
2429: l_return_status := validate_item_effective_from(p_cldv_rec, p_mode);
2430: --- Store the highest degree of error
2431: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2432: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2433: x_return_status := l_return_status;
2434: END IF;
2435: RAISE G_EXCEPTION_HALT_VALIDATION;
2428: --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
2429: l_return_status := validate_item_effective_from(p_cldv_rec, p_mode);
2430: --- Store the highest degree of error
2431: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2432: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2433: x_return_status := l_return_status;
2434: END IF;
2435: RAISE G_EXCEPTION_HALT_VALIDATION;
2436: END IF;
2440: EXCEPTION
2441: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2442: RETURN x_return_status;
2443: WHEN OTHERS THEN
2444: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2445: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2446: p_msg_name => G_UNEXPECTED_ERROR,
2447: p_token1 => G_SQLCODE_TOKEN,
2448: p_token1_value => SQLCODE,
2441: WHEN G_EXCEPTION_HALT_VALIDATION THEN
2442: RETURN x_return_status;
2443: WHEN OTHERS THEN
2444: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2445: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2446: p_msg_name => G_UNEXPECTED_ERROR,
2447: p_token1 => G_SQLCODE_TOKEN,
2448: p_token1_value => SQLCODE,
2449: p_token2 => G_SQLERRM_TOKEN,
2462: -- End of comments
2463: ----------------------------------------------------------------------------------
2464: PROCEDURE create_checklist_hdr(
2465: p_api_version IN NUMBER
2466: ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2467: ,x_return_status OUT NOCOPY VARCHAR2
2468: ,x_msg_count OUT NOCOPY NUMBER
2469: ,x_msg_data OUT NOCOPY VARCHAR2
2470: ,p_clhv_rec IN clhv_rec_type
2473: is
2474: l_api_name CONSTANT VARCHAR2(30) := 'create_checklist_hdr';
2475: l_api_version CONSTANT NUMBER := 1.0;
2476: i NUMBER;
2477: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2478: lp_clhv_rec clhv_rec_type := p_clhv_rec;
2479: -- lx_clhv_rec clhv_rec_type := x_clhv_rec;
2480:
2481: begin
2487: p_api_version,
2488: l_api_name,
2489: G_PKG_NAME ))
2490: THEN
2491: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2492: END IF;
2493:
2494: -- Initialize message list if requested
2495: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2496: FND_MSG_PUB.initialize;
2497: END IF;
2498:
2499: -- Initialize API status to success
2500: x_return_status := OKL_API.G_RET_STS_SUCCESS;
2501:
2502:
2503: /*** Begin API body ****************************************************/
2504:
2502:
2503: /*** Begin API body ****************************************************/
2504:
2505: -- start: cklee: 4/26/2005 set default
2506: IF lp_clhv_rec.STATUS_CODE IS NULL or lp_clhv_rec.STATUS_CODE = OKL_API.G_MISS_CHAR THEN
2507: lp_clhv_rec.STATUS_CODE := G_NEW_STS_CODE;
2508: END IF;
2509: IF lp_clhv_rec.CHECKLIST_PURPOSE_CODE in ('CHECKLIST_TEMPLATE_GROUP', 'CHECKLIST_INSTANCE') THEN
2510: lp_clhv_rec.CHECKLIST_TYPE := 'NONE';
2512: -- end: cklee: 4/26/2005 set default
2513:
2514: l_return_status := validate_header_attributes(lp_clhv_rec, G_INSERT_MODE);
2515: --- Store the highest degree of error
2516: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2517: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2518: x_return_status := l_return_status;
2519: END IF;
2520: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2513:
2514: l_return_status := validate_header_attributes(lp_clhv_rec, G_INSERT_MODE);
2515: --- Store the highest degree of error
2516: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2517: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2518: x_return_status := l_return_status;
2519: END IF;
2520: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2521: END IF;
2516: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2517: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2518: x_return_status := l_return_status;
2519: END IF;
2520: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2521: END IF;
2522:
2523: okl_clh_pvt.insert_row(
2524: p_api_version => p_api_version,
2538: lp_clhv_rec.id := x_clhv_rec.id;
2539:
2540: l_return_status := validate_hdr_attr_aftimg(lp_clhv_rec, G_INSERT_MODE);
2541: --- Store the highest degree of error
2542: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2543: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2544: x_return_status := l_return_status;
2545: END IF;
2546: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2539:
2540: l_return_status := validate_hdr_attr_aftimg(lp_clhv_rec, G_INSERT_MODE);
2541: --- Store the highest degree of error
2542: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2543: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2544: x_return_status := l_return_status;
2545: END IF;
2546: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2547: END IF;
2542: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2543: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2544: x_return_status := l_return_status;
2545: END IF;
2546: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2547: END IF;
2548:
2549: /*** End API body ******************************************************/
2550:
2553: (p_count => x_msg_count,
2554: p_data => x_msg_data);
2555:
2556: EXCEPTION
2557: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2558: ROLLBACK TO create_checklist_hdr;
2559: x_return_status := OKL_API.G_RET_STS_ERROR;
2560: FND_MSG_PUB.Count_And_Get
2561: (p_count => x_msg_count,
2555:
2556: EXCEPTION
2557: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2558: ROLLBACK TO create_checklist_hdr;
2559: x_return_status := OKL_API.G_RET_STS_ERROR;
2560: FND_MSG_PUB.Count_And_Get
2561: (p_count => x_msg_count,
2562: p_data => x_msg_data);
2563:
2560: FND_MSG_PUB.Count_And_Get
2561: (p_count => x_msg_count,
2562: p_data => x_msg_data);
2563:
2564: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2565: ROLLBACK TO create_checklist_hdr;
2566: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2567: FND_MSG_PUB.Count_And_Get
2568: (p_count => x_msg_count,
2562: p_data => x_msg_data);
2563:
2564: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2565: ROLLBACK TO create_checklist_hdr;
2566: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2567: FND_MSG_PUB.Count_And_Get
2568: (p_count => x_msg_count,
2569: p_data => x_msg_data);
2570:
2569: p_data => x_msg_data);
2570:
2571: WHEN OTHERS THEN
2572: ROLLBACK TO create_checklist_hdr;
2573: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2574: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2575: p_msg_name => G_UNEXPECTED_ERROR,
2576: p_token1 => G_SQLCODE_TOKEN,
2577: p_token1_value => SQLCODE,
2570:
2571: WHEN OTHERS THEN
2572: ROLLBACK TO create_checklist_hdr;
2573: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2574: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2575: p_msg_name => G_UNEXPECTED_ERROR,
2576: p_token1 => G_SQLCODE_TOKEN,
2577: p_token1_value => SQLCODE,
2578: p_token2 => G_SQLERRM_TOKEN,
2605: is
2606: l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_hdr';
2607: l_api_version CONSTANT NUMBER := 1.0;
2608: i NUMBER;
2609: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2610: lp_clhv_rec clhv_rec_type := p_clhv_rec;
2611: -- lx_clhv_rec clhv_rec_type := x_clhv_rec;
2612:
2613: begin
2619: p_api_version,
2620: l_api_name,
2621: G_PKG_NAME ))
2622: THEN
2623: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2624: END IF;
2625:
2626: -- Initialize message list if requested
2627: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2628: FND_MSG_PUB.initialize;
2629: END IF;
2630:
2631: -- Initialize API status to success
2632: x_return_status := OKL_API.G_RET_STS_SUCCESS;
2633:
2634:
2635: /*** Begin API body ****************************************************/
2636:
2635: /*** Begin API body ****************************************************/
2636:
2637: l_return_status := validate_header_attributes(lp_clhv_rec, G_UPDATE_MODE);
2638: --- Store the highest degree of error
2639: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2640: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2641: x_return_status := l_return_status;
2642: END IF;
2643: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2636:
2637: l_return_status := validate_header_attributes(lp_clhv_rec, G_UPDATE_MODE);
2638: --- Store the highest degree of error
2639: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2640: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2641: x_return_status := l_return_status;
2642: END IF;
2643: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2644: END IF;
2639: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2640: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2641: x_return_status := l_return_status;
2642: END IF;
2643: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2644: END IF;
2645:
2646: okl_clh_pvt.update_row(
2647: p_api_version => p_api_version,
2659: End If;
2660:
2661: l_return_status := validate_hdr_attr_aftimg(lp_clhv_rec, G_UPDATE_MODE);
2662: --- Store the highest degree of error
2663: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2664: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2665: x_return_status := l_return_status;
2666: END IF;
2667: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2660:
2661: l_return_status := validate_hdr_attr_aftimg(lp_clhv_rec, G_UPDATE_MODE);
2662: --- Store the highest degree of error
2663: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2664: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2665: x_return_status := l_return_status;
2666: END IF;
2667: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2668: END IF;
2663: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2664: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2665: x_return_status := l_return_status;
2666: END IF;
2667: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2668: END IF;
2669:
2670:
2671: /*** End API body ******************************************************/
2675: (p_count => x_msg_count,
2676: p_data => x_msg_data);
2677:
2678: EXCEPTION
2679: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2680: ROLLBACK TO update_checklist_hdr;
2681: x_return_status := OKL_API.G_RET_STS_ERROR;
2682: FND_MSG_PUB.Count_And_Get
2683: (p_count => x_msg_count,
2677:
2678: EXCEPTION
2679: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2680: ROLLBACK TO update_checklist_hdr;
2681: x_return_status := OKL_API.G_RET_STS_ERROR;
2682: FND_MSG_PUB.Count_And_Get
2683: (p_count => x_msg_count,
2684: p_data => x_msg_data);
2685:
2682: FND_MSG_PUB.Count_And_Get
2683: (p_count => x_msg_count,
2684: p_data => x_msg_data);
2685:
2686: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2687: ROLLBACK TO update_checklist_hdr;
2688: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2689: FND_MSG_PUB.Count_And_Get
2690: (p_count => x_msg_count,
2684: p_data => x_msg_data);
2685:
2686: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2687: ROLLBACK TO update_checklist_hdr;
2688: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2689: FND_MSG_PUB.Count_And_Get
2690: (p_count => x_msg_count,
2691: p_data => x_msg_data);
2692:
2691: p_data => x_msg_data);
2692:
2693: WHEN OTHERS THEN
2694: ROLLBACK TO update_checklist_hdr;
2695: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2696: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2697: p_msg_name => G_UNEXPECTED_ERROR,
2698: p_token1 => G_SQLCODE_TOKEN,
2699: p_token1_value => SQLCODE,
2692:
2693: WHEN OTHERS THEN
2694: ROLLBACK TO update_checklist_hdr;
2695: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2696: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2697: p_msg_name => G_UNEXPECTED_ERROR,
2698: p_token1 => G_SQLCODE_TOKEN,
2699: p_token1_value => SQLCODE,
2700: p_token2 => G_SQLERRM_TOKEN,
2716: -- End of comments
2717: ----------------------------------------------------------------------------------
2718: PROCEDURE delete_checklist_hdr(
2719: p_api_version IN NUMBER
2720: ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2721: ,x_return_status OUT NOCOPY VARCHAR2
2722: ,x_msg_count OUT NOCOPY NUMBER
2723: ,x_msg_data OUT NOCOPY VARCHAR2
2724: ,p_clhv_rec IN clhv_rec_type
2726: is
2727: l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_hdr';
2728: l_api_version CONSTANT NUMBER := 1.0;
2729: i NUMBER;
2730: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2731: lp_clhv_rec clhv_rec_type := p_clhv_rec;
2732: -- xp_clhv_rec clhv_rec_type := x_clhv_rec;
2733:
2734: begin
2740: p_api_version,
2741: l_api_name,
2742: G_PKG_NAME ))
2743: THEN
2744: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2745: END IF;
2746:
2747: -- Initialize message list if requested
2748: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2749: FND_MSG_PUB.initialize;
2750: END IF;
2751:
2752: -- Initialize API status to success
2753: x_return_status := OKL_API.G_RET_STS_SUCCESS;
2754:
2755:
2756: /*** Begin API body ****************************************************/
2757:
2776: (p_count => x_msg_count,
2777: p_data => x_msg_data);
2778:
2779: EXCEPTION
2780: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2781: ROLLBACK TO delete_checklist_hdr;
2782: x_return_status := OKL_API.G_RET_STS_ERROR;
2783: FND_MSG_PUB.Count_And_Get
2784: (p_count => x_msg_count,
2778:
2779: EXCEPTION
2780: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2781: ROLLBACK TO delete_checklist_hdr;
2782: x_return_status := OKL_API.G_RET_STS_ERROR;
2783: FND_MSG_PUB.Count_And_Get
2784: (p_count => x_msg_count,
2785: p_data => x_msg_data);
2786:
2783: FND_MSG_PUB.Count_And_Get
2784: (p_count => x_msg_count,
2785: p_data => x_msg_data);
2786:
2787: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2788: ROLLBACK TO delete_checklist_hdr;
2789: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2790: FND_MSG_PUB.Count_And_Get
2791: (p_count => x_msg_count,
2785: p_data => x_msg_data);
2786:
2787: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2788: ROLLBACK TO delete_checklist_hdr;
2789: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2790: FND_MSG_PUB.Count_And_Get
2791: (p_count => x_msg_count,
2792: p_data => x_msg_data);
2793:
2792: p_data => x_msg_data);
2793:
2794: WHEN OTHERS THEN
2795: ROLLBACK TO delete_checklist_hdr;
2796: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2797: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2798: p_msg_name => G_UNEXPECTED_ERROR,
2799: p_token1 => G_SQLCODE_TOKEN,
2800: p_token1_value => SQLCODE,
2793:
2794: WHEN OTHERS THEN
2795: ROLLBACK TO delete_checklist_hdr;
2796: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2797: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2798: p_msg_name => G_UNEXPECTED_ERROR,
2799: p_token1 => G_SQLCODE_TOKEN,
2800: p_token1_value => SQLCODE,
2801: p_token2 => G_SQLERRM_TOKEN,
2817: -- End of comments
2818: ----------------------------------------------------------------------------------
2819: PROCEDURE create_checklist_dtl(
2820: p_api_version IN NUMBER
2821: ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2822: ,x_return_status OUT NOCOPY VARCHAR2
2823: ,x_msg_count OUT NOCOPY NUMBER
2824: ,x_msg_data OUT NOCOPY VARCHAR2
2825: ,p_cldv_tbl IN cldv_tbl_type
2828: is
2829: l_api_name CONSTANT VARCHAR2(30) := 'create_checklist_dtl';
2830: l_api_version CONSTANT NUMBER := 1.0;
2831: i NUMBER;
2832: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2833: lp_cldv_tbl cldv_tbl_type := p_cldv_tbl;
2834: -- lx_cldv_tbl cldv_tbl_type := x_cldv_tbl;
2835:
2836: begin
2842: p_api_version,
2843: l_api_name,
2844: G_PKG_NAME ))
2845: THEN
2846: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2847: END IF;
2848:
2849: -- Initialize message list if requested
2850: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2851: FND_MSG_PUB.initialize;
2852: END IF;
2853:
2854: -- Initialize API status to success
2855: x_return_status := OKL_API.G_RET_STS_SUCCESS;
2856:
2857:
2858: /*** Begin API body ****************************************************/
2859:
2862: LOOP
2863:
2864: l_return_status := validate_line_attributes(lp_cldv_tbl(i),G_INSERT_MODE);
2865: --- Store the highest degree of error
2866: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2867: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2868: x_return_status := l_return_status;
2869: END IF;
2870: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2863:
2864: l_return_status := validate_line_attributes(lp_cldv_tbl(i),G_INSERT_MODE);
2865: --- Store the highest degree of error
2866: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2867: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2868: x_return_status := l_return_status;
2869: END IF;
2870: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2871: END IF;
2866: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2867: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2868: x_return_status := l_return_status;
2869: END IF;
2870: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2871: END IF;
2872:
2873: EXIT WHEN (i = lp_cldv_tbl.LAST);
2874: i := lp_cldv_tbl.NEXT(i);
2897: lp_cldv_tbl(i).id := x_cldv_tbl(i).id;
2898:
2899: l_return_status := validate_line_attr_aftimg(lp_cldv_tbl(i),G_INSERT_MODE);
2900: --- Store the highest degree of error
2901: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2902: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2903: x_return_status := l_return_status;
2904: END IF;
2905: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2898:
2899: l_return_status := validate_line_attr_aftimg(lp_cldv_tbl(i),G_INSERT_MODE);
2900: --- Store the highest degree of error
2901: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2902: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2903: x_return_status := l_return_status;
2904: END IF;
2905: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2906: END IF;
2901: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2902: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2903: x_return_status := l_return_status;
2904: END IF;
2905: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2906: END IF;
2907:
2908: EXIT WHEN (i = lp_cldv_tbl.LAST);
2909: i := lp_cldv_tbl.NEXT(i);
2917: (p_count => x_msg_count,
2918: p_data => x_msg_data);
2919:
2920: EXCEPTION
2921: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2922: ROLLBACK TO create_checklist_dtl;
2923: x_return_status := OKL_API.G_RET_STS_ERROR;
2924: FND_MSG_PUB.Count_And_Get
2925: (p_count => x_msg_count,
2919:
2920: EXCEPTION
2921: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2922: ROLLBACK TO create_checklist_dtl;
2923: x_return_status := OKL_API.G_RET_STS_ERROR;
2924: FND_MSG_PUB.Count_And_Get
2925: (p_count => x_msg_count,
2926: p_data => x_msg_data);
2927:
2924: FND_MSG_PUB.Count_And_Get
2925: (p_count => x_msg_count,
2926: p_data => x_msg_data);
2927:
2928: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2929: ROLLBACK TO create_checklist_dtl;
2930: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2931: FND_MSG_PUB.Count_And_Get
2932: (p_count => x_msg_count,
2926: p_data => x_msg_data);
2927:
2928: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2929: ROLLBACK TO create_checklist_dtl;
2930: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2931: FND_MSG_PUB.Count_And_Get
2932: (p_count => x_msg_count,
2933: p_data => x_msg_data);
2934:
2933: p_data => x_msg_data);
2934:
2935: WHEN OTHERS THEN
2936: ROLLBACK TO create_checklist_dtl;
2937: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2938: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2939: p_msg_name => G_UNEXPECTED_ERROR,
2940: p_token1 => G_SQLCODE_TOKEN,
2941: p_token1_value => SQLCODE,
2934:
2935: WHEN OTHERS THEN
2936: ROLLBACK TO create_checklist_dtl;
2937: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2938: OKL_API.Set_Message(p_app_name => G_APP_NAME,
2939: p_msg_name => G_UNEXPECTED_ERROR,
2940: p_token1 => G_SQLCODE_TOKEN,
2941: p_token1_value => SQLCODE,
2942: p_token2 => G_SQLERRM_TOKEN,
2969: is
2970: l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_dtl';
2971: l_api_version CONSTANT NUMBER := 1.0;
2972: i NUMBER;
2973: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2974: lp_cldv_tbl cldv_tbl_type := p_cldv_tbl;
2975: -- lx_cldv_tbl cldv_tbl_type := x_cldv_tbl;
2976:
2977: --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
2993: p_api_version,
2994: l_api_name,
2995: G_PKG_NAME ))
2996: THEN
2997: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2998: END IF;
2999:
3000: -- Initialize message list if requested
3001: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3002: FND_MSG_PUB.initialize;
3003: END IF;
3004:
3005: -- Initialize API status to success
3006: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3007:
3008:
3009: /*** Begin API body ****************************************************/
3010:
3028: --END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
3029:
3030: l_return_status := validate_line_attributes(lp_cldv_tbl(i),G_UPDATE_MODE);
3031: --- Store the highest degree of error
3032: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3033: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3034: x_return_status := l_return_status;
3035: END IF;
3036: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3029:
3030: l_return_status := validate_line_attributes(lp_cldv_tbl(i),G_UPDATE_MODE);
3031: --- Store the highest degree of error
3032: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3033: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3034: x_return_status := l_return_status;
3035: END IF;
3036: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3037: END IF;
3032: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3033: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3034: x_return_status := l_return_status;
3035: END IF;
3036: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3037: END IF;
3038:
3039: EXIT WHEN (i = lp_cldv_tbl.LAST);
3040: i := lp_cldv_tbl.NEXT(i);
3061: LOOP
3062:
3063: l_return_status := validate_line_attr_aftimg(lp_cldv_tbl(i),G_UPDATE_MODE);
3064: --- Store the highest degree of error
3065: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3066: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3067: x_return_status := l_return_status;
3068: END IF;
3069: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3062:
3063: l_return_status := validate_line_attr_aftimg(lp_cldv_tbl(i),G_UPDATE_MODE);
3064: --- Store the highest degree of error
3065: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3066: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3067: x_return_status := l_return_status;
3068: END IF;
3069: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3070: END IF;
3065: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3066: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3067: x_return_status := l_return_status;
3068: END IF;
3069: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3070: END IF;
3071:
3072: EXIT WHEN (i = lp_cldv_tbl.LAST);
3073: i := lp_cldv_tbl.NEXT(i);
3081: (p_count => x_msg_count,
3082: p_data => x_msg_data);
3083:
3084: EXCEPTION
3085: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3086: ROLLBACK TO update_checklist_dtl;
3087: x_return_status := OKL_API.G_RET_STS_ERROR;
3088: FND_MSG_PUB.Count_And_Get
3089: (p_count => x_msg_count,
3083:
3084: EXCEPTION
3085: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3086: ROLLBACK TO update_checklist_dtl;
3087: x_return_status := OKL_API.G_RET_STS_ERROR;
3088: FND_MSG_PUB.Count_And_Get
3089: (p_count => x_msg_count,
3090: p_data => x_msg_data);
3091:
3088: FND_MSG_PUB.Count_And_Get
3089: (p_count => x_msg_count,
3090: p_data => x_msg_data);
3091:
3092: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3093: ROLLBACK TO update_checklist_dtl;
3094: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3095: FND_MSG_PUB.Count_And_Get
3096: (p_count => x_msg_count,
3090: p_data => x_msg_data);
3091:
3092: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3093: ROLLBACK TO update_checklist_dtl;
3094: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3095: FND_MSG_PUB.Count_And_Get
3096: (p_count => x_msg_count,
3097: p_data => x_msg_data);
3098:
3097: p_data => x_msg_data);
3098:
3099: WHEN OTHERS THEN
3100: ROLLBACK TO update_checklist_dtl;
3101: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3102: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3103: p_msg_name => G_UNEXPECTED_ERROR,
3104: p_token1 => G_SQLCODE_TOKEN,
3105: p_token1_value => SQLCODE,
3098:
3099: WHEN OTHERS THEN
3100: ROLLBACK TO update_checklist_dtl;
3101: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3102: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3103: p_msg_name => G_UNEXPECTED_ERROR,
3104: p_token1 => G_SQLCODE_TOKEN,
3105: p_token1_value => SQLCODE,
3106: p_token2 => G_SQLERRM_TOKEN,
3132: is
3133: l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_dtl';
3134: l_api_version CONSTANT NUMBER := 1.0;
3135: i NUMBER;
3136: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3137: lp_cldv_tbl cldv_tbl_type := p_cldv_tbl;
3138: -- xp_cldv_tbl cldv_tbl_type := x_cldv_tbl;
3139:
3140: begin
3146: p_api_version,
3147: l_api_name,
3148: G_PKG_NAME ))
3149: THEN
3150: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3151: END IF;
3152:
3153: -- Initialize message list if requested
3154: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3155: FND_MSG_PUB.initialize;
3156: END IF;
3157:
3158: -- Initialize API status to success
3159: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3160:
3161:
3162: /*** Begin API body ****************************************************/
3163:
3182: (p_count => x_msg_count,
3183: p_data => x_msg_data);
3184:
3185: EXCEPTION
3186: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3187: ROLLBACK TO delete_checklist_dtl;
3188: x_return_status := OKL_API.G_RET_STS_ERROR;
3189: FND_MSG_PUB.Count_And_Get
3190: (p_count => x_msg_count,
3184:
3185: EXCEPTION
3186: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3187: ROLLBACK TO delete_checklist_dtl;
3188: x_return_status := OKL_API.G_RET_STS_ERROR;
3189: FND_MSG_PUB.Count_And_Get
3190: (p_count => x_msg_count,
3191: p_data => x_msg_data);
3192:
3189: FND_MSG_PUB.Count_And_Get
3190: (p_count => x_msg_count,
3191: p_data => x_msg_data);
3192:
3193: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3194: ROLLBACK TO delete_checklist_dtl;
3195: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3196: FND_MSG_PUB.Count_And_Get
3197: (p_count => x_msg_count,
3191: p_data => x_msg_data);
3192:
3193: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3194: ROLLBACK TO delete_checklist_dtl;
3195: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3196: FND_MSG_PUB.Count_And_Get
3197: (p_count => x_msg_count,
3198: p_data => x_msg_data);
3199:
3198: p_data => x_msg_data);
3199:
3200: WHEN OTHERS THEN
3201: ROLLBACK TO delete_checklist_dtl;
3202: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3203: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3204: p_msg_name => G_UNEXPECTED_ERROR,
3205: p_token1 => G_SQLCODE_TOKEN,
3206: p_token1_value => SQLCODE,
3199:
3200: WHEN OTHERS THEN
3201: ROLLBACK TO delete_checklist_dtl;
3202: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3203: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3204: p_msg_name => G_UNEXPECTED_ERROR,
3205: p_token1 => G_SQLCODE_TOKEN,
3206: p_token1_value => SQLCODE,
3207: p_token2 => G_SQLERRM_TOKEN,
3236: is
3237: l_api_name CONSTANT VARCHAR2(30) := 'submit_for_approval_pvt';
3238: l_api_version CONSTANT NUMBER := 1.0;
3239: i NUMBER;
3240: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3241: lp_clhv_rec clhv_rec_type;
3242: lx_clhv_rec clhv_rec_type;
3243: l_org_status_code okl_checklists.status_code%type;
3244:
3261: p_api_version,
3262: l_api_name,
3263: G_PKG_NAME ))
3264: THEN
3265: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3266: END IF;
3267:
3268: -- Initialize message list if requested
3269: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3270: FND_MSG_PUB.initialize;
3271: END IF;
3272:
3273: -- Initialize API status to success
3274: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3275:
3276:
3277: /*** Begin API body ****************************************************/
3278:
3348: (p_count => x_msg_count,
3349: p_data => x_msg_data);
3350:
3351: EXCEPTION
3352: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3353: ROLLBACK TO submit_for_approval_pvt;
3354: x_return_status := OKL_API.G_RET_STS_ERROR;
3355: x_status_code := l_org_status_code;
3356: FND_MSG_PUB.Count_And_Get
3350:
3351: EXCEPTION
3352: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3353: ROLLBACK TO submit_for_approval_pvt;
3354: x_return_status := OKL_API.G_RET_STS_ERROR;
3355: x_status_code := l_org_status_code;
3356: FND_MSG_PUB.Count_And_Get
3357: (p_count => x_msg_count,
3358: p_data => x_msg_data);
3356: FND_MSG_PUB.Count_And_Get
3357: (p_count => x_msg_count,
3358: p_data => x_msg_data);
3359:
3360: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3361: ROLLBACK TO submit_for_approval_pvt;
3362: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3363: x_status_code := l_org_status_code;
3364: FND_MSG_PUB.Count_And_Get
3358: p_data => x_msg_data);
3359:
3360: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3361: ROLLBACK TO submit_for_approval_pvt;
3362: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3363: x_status_code := l_org_status_code;
3364: FND_MSG_PUB.Count_And_Get
3365: (p_count => x_msg_count,
3366: p_data => x_msg_data);
3366: p_data => x_msg_data);
3367:
3368: WHEN OTHERS THEN
3369: ROLLBACK TO submit_for_approval_pvt;
3370: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3371: x_status_code := l_org_status_code;
3372: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3373: p_msg_name => G_UNEXPECTED_ERROR,
3374: p_token1 => G_SQLCODE_TOKEN,
3368: WHEN OTHERS THEN
3369: ROLLBACK TO submit_for_approval_pvt;
3370: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3371: x_status_code := l_org_status_code;
3372: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3373: p_msg_name => G_UNEXPECTED_ERROR,
3374: p_token1 => G_SQLCODE_TOKEN,
3375: p_token1_value => SQLCODE,
3376: p_token2 => G_SQLERRM_TOKEN,
3409: is
3410: l_api_name CONSTANT VARCHAR2(30) := 'create_checklist_inst_hdr';
3411: l_api_version CONSTANT NUMBER := 1.0;
3412: i NUMBER;
3413: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3414: lp_clhv_rec clhv_rec_type := p_clhv_rec;
3415: -- lx_clhv_rec clhv_rec_type := x_clhv_rec;
3416: l_seq NUMBER;
3417:
3428: p_api_version,
3429: l_api_name,
3430: G_PKG_NAME ))
3431: THEN
3432: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3433: END IF;
3434:
3435: -- Initialize message list if requested
3436: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3437: FND_MSG_PUB.initialize;
3438: END IF;
3439:
3440: -- Initialize API status to success
3441: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3442:
3443:
3444: /*** Begin API body ****************************************************/
3445:
3476: (p_count => x_msg_count,
3477: p_data => x_msg_data);
3478:
3479: EXCEPTION
3480: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3481: ROLLBACK TO create_checklist_inst_hdr;
3482: x_return_status := OKL_API.G_RET_STS_ERROR;
3483: FND_MSG_PUB.Count_And_Get
3484: (p_count => x_msg_count,
3478:
3479: EXCEPTION
3480: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3481: ROLLBACK TO create_checklist_inst_hdr;
3482: x_return_status := OKL_API.G_RET_STS_ERROR;
3483: FND_MSG_PUB.Count_And_Get
3484: (p_count => x_msg_count,
3485: p_data => x_msg_data);
3486:
3483: FND_MSG_PUB.Count_And_Get
3484: (p_count => x_msg_count,
3485: p_data => x_msg_data);
3486:
3487: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3488: ROLLBACK TO create_checklist_inst_hdr;
3489: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3490: FND_MSG_PUB.Count_And_Get
3491: (p_count => x_msg_count,
3485: p_data => x_msg_data);
3486:
3487: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3488: ROLLBACK TO create_checklist_inst_hdr;
3489: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3490: FND_MSG_PUB.Count_And_Get
3491: (p_count => x_msg_count,
3492: p_data => x_msg_data);
3493:
3492: p_data => x_msg_data);
3493:
3494: WHEN OTHERS THEN
3495: ROLLBACK TO create_checklist_inst_hdr;
3496: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3497: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3498: p_msg_name => G_UNEXPECTED_ERROR,
3499: p_token1 => G_SQLCODE_TOKEN,
3500: p_token1_value => SQLCODE,
3493:
3494: WHEN OTHERS THEN
3495: ROLLBACK TO create_checklist_inst_hdr;
3496: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3497: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3498: p_msg_name => G_UNEXPECTED_ERROR,
3499: p_token1 => G_SQLCODE_TOKEN,
3500: p_token1_value => SQLCODE,
3501: p_token2 => G_SQLERRM_TOKEN,
3532: is
3533: l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_inst_hdr';
3534: l_api_version CONSTANT NUMBER := 1.0;
3535: i NUMBER;
3536: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3537: lp_clhv_rec clhv_rec_type := p_clhv_rec;
3538: -- lx_clhv_rec clhv_rec_type := x_clhv_rec;
3539:
3540: begin
3546: p_api_version,
3547: l_api_name,
3548: G_PKG_NAME ))
3549: THEN
3550: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3551: END IF;
3552:
3553: -- Initialize message list if requested
3554: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3555: FND_MSG_PUB.initialize;
3556: END IF;
3557:
3558: -- Initialize API status to success
3559: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3560:
3561:
3562: /*** Begin API body ****************************************************/
3563:
3583: (p_count => x_msg_count,
3584: p_data => x_msg_data);
3585:
3586: EXCEPTION
3587: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3588: ROLLBACK TO update_checklist_inst_hdr;
3589: x_return_status := OKL_API.G_RET_STS_ERROR;
3590: FND_MSG_PUB.Count_And_Get
3591: (p_count => x_msg_count,
3585:
3586: EXCEPTION
3587: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3588: ROLLBACK TO update_checklist_inst_hdr;
3589: x_return_status := OKL_API.G_RET_STS_ERROR;
3590: FND_MSG_PUB.Count_And_Get
3591: (p_count => x_msg_count,
3592: p_data => x_msg_data);
3593:
3590: FND_MSG_PUB.Count_And_Get
3591: (p_count => x_msg_count,
3592: p_data => x_msg_data);
3593:
3594: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3595: ROLLBACK TO update_checklist_inst_hdr;
3596: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3597: FND_MSG_PUB.Count_And_Get
3598: (p_count => x_msg_count,
3592: p_data => x_msg_data);
3593:
3594: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3595: ROLLBACK TO update_checklist_inst_hdr;
3596: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3597: FND_MSG_PUB.Count_And_Get
3598: (p_count => x_msg_count,
3599: p_data => x_msg_data);
3600:
3599: p_data => x_msg_data);
3600:
3601: WHEN OTHERS THEN
3602: ROLLBACK TO update_checklist_inst_hdr;
3603: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3604: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3605: p_msg_name => G_UNEXPECTED_ERROR,
3606: p_token1 => G_SQLCODE_TOKEN,
3607: p_token1_value => SQLCODE,
3600:
3601: WHEN OTHERS THEN
3602: ROLLBACK TO update_checklist_inst_hdr;
3603: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3604: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3605: p_msg_name => G_UNEXPECTED_ERROR,
3606: p_token1 => G_SQLCODE_TOKEN,
3607: p_token1_value => SQLCODE,
3608: p_token2 => G_SQLERRM_TOKEN,
3634: is
3635: l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_inst_hdr';
3636: l_api_version CONSTANT NUMBER := 1.0;
3637: i NUMBER;
3638: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3639: lp_clhv_rec clhv_rec_type := p_clhv_rec;
3640: -- lx_clhv_rec clhv_rec_type := x_clhv_rec;
3641:
3642: begin
3648: p_api_version,
3649: l_api_name,
3650: G_PKG_NAME ))
3651: THEN
3652: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3653: END IF;
3654:
3655: -- Initialize message list if requested
3656: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3657: FND_MSG_PUB.initialize;
3658: END IF;
3659:
3660: -- Initialize API status to success
3661: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3662:
3663:
3664: /*** Begin API body ****************************************************/
3665:
3684: (p_count => x_msg_count,
3685: p_data => x_msg_data);
3686:
3687: EXCEPTION
3688: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3689: ROLLBACK TO delete_checklist_inst_hdr;
3690: x_return_status := OKL_API.G_RET_STS_ERROR;
3691: FND_MSG_PUB.Count_And_Get
3692: (p_count => x_msg_count,
3686:
3687: EXCEPTION
3688: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3689: ROLLBACK TO delete_checklist_inst_hdr;
3690: x_return_status := OKL_API.G_RET_STS_ERROR;
3691: FND_MSG_PUB.Count_And_Get
3692: (p_count => x_msg_count,
3693: p_data => x_msg_data);
3694:
3691: FND_MSG_PUB.Count_And_Get
3692: (p_count => x_msg_count,
3693: p_data => x_msg_data);
3694:
3695: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3696: ROLLBACK TO delete_checklist_inst_hdr;
3697: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3698: FND_MSG_PUB.Count_And_Get
3699: (p_count => x_msg_count,
3693: p_data => x_msg_data);
3694:
3695: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3696: ROLLBACK TO delete_checklist_inst_hdr;
3697: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3698: FND_MSG_PUB.Count_And_Get
3699: (p_count => x_msg_count,
3700: p_data => x_msg_data);
3701:
3700: p_data => x_msg_data);
3701:
3702: WHEN OTHERS THEN
3703: ROLLBACK TO delete_checklist_inst_hdr;
3704: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3705: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3706: p_msg_name => G_UNEXPECTED_ERROR,
3707: p_token1 => G_SQLCODE_TOKEN,
3708: p_token1_value => SQLCODE,
3701:
3702: WHEN OTHERS THEN
3703: ROLLBACK TO delete_checklist_inst_hdr;
3704: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3705: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3706: p_msg_name => G_UNEXPECTED_ERROR,
3707: p_token1 => G_SQLCODE_TOKEN,
3708: p_token1_value => SQLCODE,
3709: p_token2 => G_SQLERRM_TOKEN,
3753: is
3754: l_api_name CONSTANT VARCHAR2(30) := 'create_checklist_inst_dtl';
3755: l_api_version CONSTANT NUMBER := 1.0;
3756: i NUMBER;
3757: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3758: lp_cldv_tbl cldv_tbl_type := p_cldv_tbl;
3759: -- lx_cldv_tbl cldv_tbl_type := x_cldv_tbl;
3760: l_obj_id number;
3761:
3773: p_api_version,
3774: l_api_name,
3775: G_PKG_NAME ))
3776: THEN
3777: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3778: END IF;
3779:
3780: -- Initialize message list if requested
3781: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3782: FND_MSG_PUB.initialize;
3783: END IF;
3784:
3785: -- Initialize API status to success
3786: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3787:
3788:
3789: /*** Begin API body ****************************************************/
3790:
3799: lp_cldv_tbl(i).DNZ_CHECKLIST_OBJ_ID := l_obj_id;
3800:
3801: --START:| 05-Jan-2006 cklee -- Fixed bug#4930868 |
3802: IF (lp_cldv_tbl(i).MANDATORY_FLAG IS NULL or
3803: lp_cldv_tbl(i).MANDATORY_FLAG = OKL_API.G_MISS_CHAR) THEN
3804: -- set default for MANDATORY_FLAG
3805: lp_cldv_tbl(i).MANDATORY_FLAG := 'N';
3806: END IF;
3807: --END:| 05-Jan-2006 cklee -- Fixed bug#4930868 |
3806: END IF;
3807: --END:| 05-Jan-2006 cklee -- Fixed bug#4930868 |
3808: --PAGARG: Bug 4872271: Default the value of APPEAL_FLAG to N
3809: IF (lp_cldv_tbl(i).APPEAL_FLAG IS NULL OR
3810: lp_cldv_tbl(i).APPEAL_FLAG = OKL_API.G_MISS_CHAR) THEN
3811: -- set default for APPEAL_FLAG
3812: lp_cldv_tbl(i).APPEAL_FLAG := 'N';
3813: END IF;
3814:
3849: (p_count => x_msg_count,
3850: p_data => x_msg_data);
3851:
3852: EXCEPTION
3853: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3854: ROLLBACK TO create_checklist_inst_dtl;
3855: x_return_status := OKL_API.G_RET_STS_ERROR;
3856: FND_MSG_PUB.Count_And_Get
3857: (p_count => x_msg_count,
3851:
3852: EXCEPTION
3853: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3854: ROLLBACK TO create_checklist_inst_dtl;
3855: x_return_status := OKL_API.G_RET_STS_ERROR;
3856: FND_MSG_PUB.Count_And_Get
3857: (p_count => x_msg_count,
3858: p_data => x_msg_data);
3859:
3856: FND_MSG_PUB.Count_And_Get
3857: (p_count => x_msg_count,
3858: p_data => x_msg_data);
3859:
3860: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3861: ROLLBACK TO create_checklist_inst_dtl;
3862: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3863: FND_MSG_PUB.Count_And_Get
3864: (p_count => x_msg_count,
3858: p_data => x_msg_data);
3859:
3860: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3861: ROLLBACK TO create_checklist_inst_dtl;
3862: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3863: FND_MSG_PUB.Count_And_Get
3864: (p_count => x_msg_count,
3865: p_data => x_msg_data);
3866:
3865: p_data => x_msg_data);
3866:
3867: WHEN OTHERS THEN
3868: ROLLBACK TO create_checklist_inst_dtl;
3869: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3870: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3871: p_msg_name => G_UNEXPECTED_ERROR,
3872: p_token1 => G_SQLCODE_TOKEN,
3873: p_token1_value => SQLCODE,
3866:
3867: WHEN OTHERS THEN
3868: ROLLBACK TO create_checklist_inst_dtl;
3869: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3870: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3871: p_msg_name => G_UNEXPECTED_ERROR,
3872: p_token1 => G_SQLCODE_TOKEN,
3873: p_token1_value => SQLCODE,
3874: p_token2 => G_SQLERRM_TOKEN,
3906: is
3907: l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_inst_dtl';
3908: l_api_version CONSTANT NUMBER := 1.0;
3909: i NUMBER;
3910: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3911: lp_cldv_tbl cldv_tbl_type := p_cldv_tbl;
3912: -- lx_cldv_tbl cldv_tbl_type := x_cldv_tbl;
3913:
3914: begin
3920: p_api_version,
3921: l_api_name,
3922: G_PKG_NAME ))
3923: THEN
3924: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3925: END IF;
3926:
3927: -- Initialize message list if requested
3928: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3929: FND_MSG_PUB.initialize;
3930: END IF;
3931:
3932: -- Initialize API status to success
3933: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3934:
3935: /*** Begin API body ****************************************************/
3936:
3937: update_checklist_dtl(
3956: (p_count => x_msg_count,
3957: p_data => x_msg_data);
3958:
3959: EXCEPTION
3960: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3961: ROLLBACK TO update_checklist_inst_dtl;
3962: x_return_status := OKL_API.G_RET_STS_ERROR;
3963: FND_MSG_PUB.Count_And_Get
3964: (p_count => x_msg_count,
3958:
3959: EXCEPTION
3960: WHEN OKL_API.G_EXCEPTION_ERROR THEN
3961: ROLLBACK TO update_checklist_inst_dtl;
3962: x_return_status := OKL_API.G_RET_STS_ERROR;
3963: FND_MSG_PUB.Count_And_Get
3964: (p_count => x_msg_count,
3965: p_data => x_msg_data);
3966:
3963: FND_MSG_PUB.Count_And_Get
3964: (p_count => x_msg_count,
3965: p_data => x_msg_data);
3966:
3967: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3968: ROLLBACK TO update_checklist_inst_dtl;
3969: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3970: FND_MSG_PUB.Count_And_Get
3971: (p_count => x_msg_count,
3965: p_data => x_msg_data);
3966:
3967: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3968: ROLLBACK TO update_checklist_inst_dtl;
3969: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3970: FND_MSG_PUB.Count_And_Get
3971: (p_count => x_msg_count,
3972: p_data => x_msg_data);
3973:
3972: p_data => x_msg_data);
3973:
3974: WHEN OTHERS THEN
3975: ROLLBACK TO update_checklist_inst_dtl;
3976: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3977: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3978: p_msg_name => G_UNEXPECTED_ERROR,
3979: p_token1 => G_SQLCODE_TOKEN,
3980: p_token1_value => SQLCODE,
3973:
3974: WHEN OTHERS THEN
3975: ROLLBACK TO update_checklist_inst_dtl;
3976: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3977: OKL_API.Set_Message(p_app_name => G_APP_NAME,
3978: p_msg_name => G_UNEXPECTED_ERROR,
3979: p_token1 => G_SQLCODE_TOKEN,
3980: p_token1_value => SQLCODE,
3981: p_token2 => G_SQLERRM_TOKEN,
4008: is
4009: l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_inst_dtl';
4010: l_api_version CONSTANT NUMBER := 1.0;
4011: i NUMBER;
4012: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4013: lp_cldv_tbl cldv_tbl_type := p_cldv_tbl;
4014: -- lx_cldv_tbl cldv_tbl_type := x_cldv_tbl;
4015:
4016: begin
4022: p_api_version,
4023: l_api_name,
4024: G_PKG_NAME ))
4025: THEN
4026: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4027: END IF;
4028:
4029: -- Initialize message list if requested
4030: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4031: FND_MSG_PUB.initialize;
4032: END IF;
4033:
4034: -- Initialize API status to success
4035: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4036:
4037:
4038: /*** Begin API body ****************************************************/
4039:
4058: (p_count => x_msg_count,
4059: p_data => x_msg_data);
4060:
4061: EXCEPTION
4062: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4063: ROLLBACK TO delete_checklist_inst_dtl;
4064: x_return_status := OKL_API.G_RET_STS_ERROR;
4065: FND_MSG_PUB.Count_And_Get
4066: (p_count => x_msg_count,
4060:
4061: EXCEPTION
4062: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4063: ROLLBACK TO delete_checklist_inst_dtl;
4064: x_return_status := OKL_API.G_RET_STS_ERROR;
4065: FND_MSG_PUB.Count_And_Get
4066: (p_count => x_msg_count,
4067: p_data => x_msg_data);
4068:
4065: FND_MSG_PUB.Count_And_Get
4066: (p_count => x_msg_count,
4067: p_data => x_msg_data);
4068:
4069: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4070: ROLLBACK TO delete_checklist_inst_dtl;
4071: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4072: FND_MSG_PUB.Count_And_Get
4073: (p_count => x_msg_count,
4067: p_data => x_msg_data);
4068:
4069: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4070: ROLLBACK TO delete_checklist_inst_dtl;
4071: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4072: FND_MSG_PUB.Count_And_Get
4073: (p_count => x_msg_count,
4074: p_data => x_msg_data);
4075:
4074: p_data => x_msg_data);
4075:
4076: WHEN OTHERS THEN
4077: ROLLBACK TO delete_checklist_inst_dtl;
4078: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4079: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4080: p_msg_name => G_UNEXPECTED_ERROR,
4081: p_token1 => G_SQLCODE_TOKEN,
4082: p_token1_value => SQLCODE,
4075:
4076: WHEN OTHERS THEN
4077: ROLLBACK TO delete_checklist_inst_dtl;
4078: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4079: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4080: p_msg_name => G_UNEXPECTED_ERROR,
4081: p_token1 => G_SQLCODE_TOKEN,
4082: p_token1_value => SQLCODE,
4083: p_token2 => G_SQLERRM_TOKEN,
4116: is
4117: l_api_name CONSTANT VARCHAR2(30) := 'chk_eligible_for_approval';
4118: l_api_version CONSTANT NUMBER := 1.0;
4119: i NUMBER;
4120: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4121: l_dummy number;
4122:
4123: l_row_not_found boolean := false;
4124:
4173: p_api_version,
4174: l_api_name,
4175: G_PKG_NAME ))
4176: THEN
4177: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4178: END IF;
4179:
4180: -- Initialize message list if requested
4181: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4182: FND_MSG_PUB.initialize;
4183: END IF;
4184:
4185: -- Initialize API status to success
4186: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4187:
4188:
4189: /*** Begin API body ****************************************************/
4190: -- The following scenarios are not eligible for approval
4199: FOR r_this_row IN c_checklists (p_clh_id) LOOP
4200:
4201: -- 1 Checklist template (either group or individual) status is Active.
4202: IF r_this_row.status_code = G_ACTIVE_STS_CODE THEN
4203: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4204: p_msg_name => 'OKL_CHK_STATUS_4_APPROVAL');
4205:
4206: RAISE G_EXCEPTION_HALT_VALIDATION;
4207: END IF;
4217: l_row_not_found := c_checklist_grp%NOTFOUND;
4218: CLOSE c_checklist_grp;
4219:
4220: IF (l_row_not_found) THEN
4221: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4222: p_msg_name => 'OKL_CHK_CHILD_CLIST');
4223:
4224: RAISE G_EXCEPTION_HALT_VALIDATION;
4225:
4233: l_row_not_found := c_checklist_grp_items%NOTFOUND;
4234: CLOSE c_checklist_grp_items;
4235:
4236: IF (l_row_not_found) THEN
4237: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4238: p_msg_name => 'OKL_CHK_CHILD_CLIST_ITEMS');
4239:
4240: RAISE G_EXCEPTION_HALT_VALIDATION;
4241: END IF;
4250: ELSIF r_this_row.checklist_purpose_code = 'CHECKLIST_TEMPLATE' THEN
4251:
4252: -- 4 Checklist template does have group checklist assocaite with it (Has parent checklist).
4253: IF r_this_row.ckl_id IS NOT NULL THEN
4254: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4255: p_msg_name => 'OKL_CHK_PARENT_CLIST');
4256:
4257: RAISE G_EXCEPTION_HALT_VALIDATION;
4258: END IF;
4263: l_row_not_found := c_checklist_items%NOTFOUND;
4264: CLOSE c_checklist_items;
4265:
4266: IF (l_row_not_found) THEN
4267: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4268: p_msg_name => 'OKL_CHK_CLIST_ITEMS');
4269:
4270: RAISE G_EXCEPTION_HALT_VALIDATION;
4271: END IF;
4278: -- 6. Checklist item effetcive from date must within the checklist header's date range
4279: l_clhv_rec.id := p_clh_id;
4280: l_return_status := validate_dates_w_item(l_clhv_rec, G_UPDATE_MODE);
4281: --- Store the highest degree of error
4282: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
4283: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4284: x_return_status := l_return_status;
4285: END IF;
4286: RAISE G_EXCEPTION_HALT_VALIDATION;
4279: l_clhv_rec.id := p_clh_id;
4280: l_return_status := validate_dates_w_item(l_clhv_rec, G_UPDATE_MODE);
4281: --- Store the highest degree of error
4282: IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
4283: IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4284: x_return_status := l_return_status;
4285: END IF;
4286: RAISE G_EXCEPTION_HALT_VALIDATION;
4287: END IF;
4294: (p_count => x_msg_count,
4295: p_data => x_msg_data);
4296:
4297: EXCEPTION
4298: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4299: ROLLBACK TO chk_eligible_for_approval;
4300: x_return_status := OKL_API.G_RET_STS_ERROR;
4301: FND_MSG_PUB.Count_And_Get
4302: (p_count => x_msg_count,
4296:
4297: EXCEPTION
4298: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4299: ROLLBACK TO chk_eligible_for_approval;
4300: x_return_status := OKL_API.G_RET_STS_ERROR;
4301: FND_MSG_PUB.Count_And_Get
4302: (p_count => x_msg_count,
4303: p_data => x_msg_data);
4304:
4301: FND_MSG_PUB.Count_And_Get
4302: (p_count => x_msg_count,
4303: p_data => x_msg_data);
4304:
4305: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4306: ROLLBACK TO chk_eligible_for_approval;
4307: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4308: FND_MSG_PUB.Count_And_Get
4309: (p_count => x_msg_count,
4303: p_data => x_msg_data);
4304:
4305: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4306: ROLLBACK TO chk_eligible_for_approval;
4307: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4308: FND_MSG_PUB.Count_And_Get
4309: (p_count => x_msg_count,
4310: p_data => x_msg_data);
4311:
4311:
4312: --START: 06-Oct-2005 cklee -- Fixed dupliciated system error message |
4313: WHEN G_EXCEPTION_HALT_VALIDATION THEN
4314: ROLLBACK TO chk_eligible_for_approval;
4315: x_return_status := OKL_API.G_RET_STS_ERROR;
4316: FND_MSG_PUB.Count_And_Get
4317: (p_count => x_msg_count,
4318: p_data => x_msg_data);
4319: --END: 06-Oct-2005 cklee -- Fixed dupliciated system error message |
4319: --END: 06-Oct-2005 cklee -- Fixed dupliciated system error message |
4320:
4321: WHEN OTHERS THEN
4322: ROLLBACK TO chk_eligible_for_approval;
4323: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4324: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4325: p_msg_name => G_UNEXPECTED_ERROR,
4326: p_token1 => G_SQLCODE_TOKEN,
4327: p_token1_value => SQLCODE,
4320:
4321: WHEN OTHERS THEN
4322: ROLLBACK TO chk_eligible_for_approval;
4323: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4324: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4325: p_msg_name => G_UNEXPECTED_ERROR,
4326: p_token1 => G_SQLCODE_TOKEN,
4327: p_token1_value => SQLCODE,
4328: p_token2 => G_SQLERRM_TOKEN,
4345: -- End of comments
4346: ----------------------------------------------------------------------------------
4347: PROCEDURE update_checklist_function(
4348: p_api_version IN NUMBER
4349: ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
4350: ,x_return_status OUT NOCOPY VARCHAR2
4351: ,x_msg_count OUT NOCOPY NUMBER
4352: ,x_msg_data OUT NOCOPY VARCHAR2
4353: ,p_checklist_obj_id IN NUMBER
4354: ) is
4355: l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_function';
4356: l_api_version CONSTANT NUMBER := 1.0;
4357: i NUMBER;
4358: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4359: l_dummy number;
4360:
4361: l_row_not_found boolean := false;
4362:
4391: p_api_version,
4392: l_api_name,
4393: G_PKG_NAME ))
4394: THEN
4395: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4396: END IF;
4397:
4398: -- Initialize message list if requested
4399: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4400: FND_MSG_PUB.initialize;
4401: END IF;
4402:
4403: -- Initialize API status to success
4404: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4405:
4406:
4407: /*** Begin API body ****************************************************/
4408:
4427: lp_msg_data := r_this_row.FUNCTION_SOURCE || ' returns: ' || lp_return_status;
4428: END IF;
4429:
4430: EXCEPTION
4431: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4432: lp_fund_rst := 'ERROR';
4433: FND_MSG_PUB.Count_And_Get
4434: (p_count => x_msg_count,
4435: p_data => x_msg_data);
4434: (p_count => x_msg_count,
4435: p_data => x_msg_data);
4436: lp_msg_data := substr('Application error: ' || x_msg_data, 2000);
4437:
4438: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4439: lp_fund_rst := 'ERROR';
4440: FND_MSG_PUB.Count_And_Get
4441: (p_count => x_msg_count,
4442: p_data => x_msg_data);
4476: (p_count => x_msg_count,
4477: p_data => x_msg_data);
4478:
4479: EXCEPTION
4480: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4481: ROLLBACK TO update_checklist_function;
4482: x_return_status := OKL_API.G_RET_STS_ERROR;
4483: FND_MSG_PUB.Count_And_Get
4484: (p_count => x_msg_count,
4478:
4479: EXCEPTION
4480: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4481: ROLLBACK TO update_checklist_function;
4482: x_return_status := OKL_API.G_RET_STS_ERROR;
4483: FND_MSG_PUB.Count_And_Get
4484: (p_count => x_msg_count,
4485: p_data => x_msg_data);
4486:
4483: FND_MSG_PUB.Count_And_Get
4484: (p_count => x_msg_count,
4485: p_data => x_msg_data);
4486:
4487: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4488: ROLLBACK TO update_checklist_function;
4489: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4490: FND_MSG_PUB.Count_And_Get
4491: (p_count => x_msg_count,
4485: p_data => x_msg_data);
4486:
4487: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4488: ROLLBACK TO update_checklist_function;
4489: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4490: FND_MSG_PUB.Count_And_Get
4491: (p_count => x_msg_count,
4492: p_data => x_msg_data);
4493:
4493:
4494: WHEN OTHERS THEN
4495:
4496: ROLLBACK TO update_checklist_function;
4497: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4498: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4499: p_msg_name => G_UNEXPECTED_ERROR,
4500: p_token1 => G_SQLCODE_TOKEN,
4501: p_token1_value => SQLCODE,
4494: WHEN OTHERS THEN
4495:
4496: ROLLBACK TO update_checklist_function;
4497: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4498: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4499: p_msg_name => G_UNEXPECTED_ERROR,
4500: p_token1 => G_SQLCODE_TOKEN,
4501: p_token1_value => SQLCODE,
4502: p_token2 => G_SQLERRM_TOKEN,
4534: is
4535: l_api_name CONSTANT VARCHAR2(30) := 'create_contract_checklist';
4536: l_api_version CONSTANT NUMBER := 1.0;
4537: i NUMBER;
4538: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4539: lp_clhv_rec clhv_rec_type;
4540: lx_clhv_rec clhv_rec_type;
4541: lp_cldv_tbl cldv_tbl_type;
4542: lx_cldv_tbl cldv_tbl_type;
4600: p_api_version,
4601: l_api_name,
4602: G_PKG_NAME ))
4603: THEN
4604: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4605: END IF;
4606:
4607: -- Initialize message list if requested
4608: IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4609: FND_MSG_PUB.initialize;
4610: END IF;
4611:
4612: -- Initialize API status to success
4613: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4614:
4615: /*** Begin API body ****************************************************/
4616: -- start: 06-June-2005 cklee okl.h Lease App IA Authoring
4617: --------------------------------------------------------------------
4699: (p_count => x_msg_count,
4700: p_data => x_msg_data);
4701:
4702: EXCEPTION
4703: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4704: ROLLBACK TO create_contract_checklist;
4705: x_return_status := OKL_API.G_RET_STS_ERROR;
4706: FND_MSG_PUB.Count_And_Get
4707: (p_count => x_msg_count,
4701:
4702: EXCEPTION
4703: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4704: ROLLBACK TO create_contract_checklist;
4705: x_return_status := OKL_API.G_RET_STS_ERROR;
4706: FND_MSG_PUB.Count_And_Get
4707: (p_count => x_msg_count,
4708: p_data => x_msg_data);
4709:
4706: FND_MSG_PUB.Count_And_Get
4707: (p_count => x_msg_count,
4708: p_data => x_msg_data);
4709:
4710: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4711: ROLLBACK TO create_contract_checklist;
4712: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4713: FND_MSG_PUB.Count_And_Get
4714: (p_count => x_msg_count,
4708: p_data => x_msg_data);
4709:
4710: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4711: ROLLBACK TO create_contract_checklist;
4712: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4713: FND_MSG_PUB.Count_And_Get
4714: (p_count => x_msg_count,
4715: p_data => x_msg_data);
4716:
4715: p_data => x_msg_data);
4716:
4717: WHEN OTHERS THEN
4718: ROLLBACK TO create_contract_checklist;
4719: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4720: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4721: p_msg_name => G_UNEXPECTED_ERROR,
4722: p_token1 => G_SQLCODE_TOKEN,
4723: p_token1_value => SQLCODE,
4716:
4717: WHEN OTHERS THEN
4718: ROLLBACK TO create_contract_checklist;
4719: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4720: OKL_API.Set_Message(p_app_name => G_APP_NAME,
4721: p_msg_name => G_UNEXPECTED_ERROR,
4722: p_token1 => G_SQLCODE_TOKEN,
4723: p_token1_value => SQLCODE,
4724: p_token2 => G_SQLERRM_TOKEN,
4759: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'UPD_CHKLST_DTL_APL_FLAG';
4760: l_return_status VARCHAR2(1);
4761: i NUMBER;
4762: BEGIN
4763: l_return_status := OKL_API.G_RET_STS_SUCCESS;
4764: L_MODULE := 'OKL.PLSQL.OKL_CHECKLIST_PVT.UPD_CHKLST_DTL_APL_FLAG';
4765:
4766: -- check for logging on PROCEDURE level
4767: L_DEBUG_ENABLED := NVL(OKL_DEBUG_PUB.CHECK_LOG_ENABLED, 'N');
4770: IS_DEBUG_STATEMENT_ON := NVL(OKL_DEBUG_PUB.CHECK_LOG_ON(L_MODULE, FND_LOG.LEVEL_STATEMENT), FALSE);
4771:
4772: -- call START_ACTIVITY to create savepoint, check compatibility
4773: -- and initialize message list
4774: l_return_status := OKL_API.START_ACTIVITY(
4775: p_api_name => l_api_name
4776: ,p_pkg_name => G_PKG_NAME
4777: ,p_init_msg_list => p_init_msg_list
4778: ,l_api_version => l_api_version
4780: ,p_api_type => G_API_TYPE
4781: ,x_return_status => l_return_status);
4782:
4783: -- check if activity started successfully
4784: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4785: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4786: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4787: RAISE OKL_API.G_EXCEPTION_ERROR;
4788: END IF;
4781: ,x_return_status => l_return_status);
4782:
4783: -- check if activity started successfully
4784: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4785: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4786: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4787: RAISE OKL_API.G_EXCEPTION_ERROR;
4788: END IF;
4789:
4782:
4783: -- check if activity started successfully
4784: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4785: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4786: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4787: RAISE OKL_API.G_EXCEPTION_ERROR;
4788: END IF;
4789:
4790: IF (p_cldv_tbl.COUNT > 0)
4783: -- check if activity started successfully
4784: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4785: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4786: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4787: RAISE OKL_API.G_EXCEPTION_ERROR;
4788: END IF;
4789:
4790: IF (p_cldv_tbl.COUNT > 0)
4791: THEN
4792: i := p_cldv_tbl.FIRST;
4793: LOOP
4794: l_return_status := validate_appeal_flag(p_cldv_tbl(i), G_UPDATE_MODE);
4795: --- Store the highest degree of error
4796: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4797: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4798: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4799: RAISE OKL_API.G_EXCEPTION_ERROR;
4800: END IF;
4793: LOOP
4794: l_return_status := validate_appeal_flag(p_cldv_tbl(i), G_UPDATE_MODE);
4795: --- Store the highest degree of error
4796: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4797: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4798: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4799: RAISE OKL_API.G_EXCEPTION_ERROR;
4800: END IF;
4801: EXIT WHEN (i = p_cldv_tbl.LAST);
4794: l_return_status := validate_appeal_flag(p_cldv_tbl(i), G_UPDATE_MODE);
4795: --- Store the highest degree of error
4796: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4797: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4798: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4799: RAISE OKL_API.G_EXCEPTION_ERROR;
4800: END IF;
4801: EXIT WHEN (i = p_cldv_tbl.LAST);
4802: i := p_cldv_tbl.NEXT(i);
4795: --- Store the highest degree of error
4796: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4797: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4798: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4799: RAISE OKL_API.G_EXCEPTION_ERROR;
4800: END IF;
4801: EXIT WHEN (i = p_cldv_tbl.LAST);
4802: i := p_cldv_tbl.NEXT(i);
4803: END LOOP;
4812: END IF;
4813:
4814: OKL_CLD_PVT.UPDATE_ROW(
4815: p_api_version => p_api_version
4816: ,p_init_msg_list => OKL_API.G_FALSE
4817: ,x_return_status => l_return_status
4818: ,x_msg_count => x_msg_count
4819: ,x_msg_data => x_msg_data
4820: ,p_cldv_tbl => p_cldv_tbl
4835: ,L_MODULE || ' Result of OKL_CLD_PVT.UPDATE_ROW'
4836: ,'l_return_status ' || l_return_status);
4837: END IF; -- end of statement level debug
4838:
4839: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4840: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4841: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4842: RAISE OKL_API.G_EXCEPTION_ERROR;
4843: END IF;
4836: ,'l_return_status ' || l_return_status);
4837: END IF; -- end of statement level debug
4838:
4839: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4840: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4841: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4842: RAISE OKL_API.G_EXCEPTION_ERROR;
4843: END IF;
4844:
4837: END IF; -- end of statement level debug
4838:
4839: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4840: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4841: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4842: RAISE OKL_API.G_EXCEPTION_ERROR;
4843: END IF;
4844:
4845: x_return_status := l_return_status;
4838:
4839: IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
4840: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4841: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
4842: RAISE OKL_API.G_EXCEPTION_ERROR;
4843: END IF;
4844:
4845: x_return_status := l_return_status;
4846: OKL_API.END_ACTIVITY(
4842: RAISE OKL_API.G_EXCEPTION_ERROR;
4843: END IF;
4844:
4845: x_return_status := l_return_status;
4846: OKL_API.END_ACTIVITY(
4847: x_msg_count => x_msg_count
4848: ,x_msg_data => x_msg_data);
4849: EXCEPTION
4850: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4846: OKL_API.END_ACTIVITY(
4847: x_msg_count => x_msg_count
4848: ,x_msg_data => x_msg_data);
4849: EXCEPTION
4850: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4851: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4852: p_api_name => l_api_name,
4853: p_pkg_name => G_PKG_NAME,
4854: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4847: x_msg_count => x_msg_count
4848: ,x_msg_data => x_msg_data);
4849: EXCEPTION
4850: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4851: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4852: p_api_name => l_api_name,
4853: p_pkg_name => G_PKG_NAME,
4854: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4855: x_msg_count => x_msg_count,
4850: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4851: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4852: p_api_name => l_api_name,
4853: p_pkg_name => G_PKG_NAME,
4854: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4855: x_msg_count => x_msg_count,
4856: x_msg_data => x_msg_data,
4857: p_api_type => G_API_TYPE);
4858:
4855: x_msg_count => x_msg_count,
4856: x_msg_data => x_msg_data,
4857: p_api_type => G_API_TYPE);
4858:
4859: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4860: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4861: p_api_name => l_api_name,
4862: p_pkg_name => G_PKG_NAME,
4863: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4856: x_msg_data => x_msg_data,
4857: p_api_type => G_API_TYPE);
4858:
4859: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4860: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4861: p_api_name => l_api_name,
4862: p_pkg_name => G_PKG_NAME,
4863: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4864: x_msg_count => x_msg_count,
4859: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4860: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4861: p_api_name => l_api_name,
4862: p_pkg_name => G_PKG_NAME,
4863: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4864: x_msg_count => x_msg_count,
4865: x_msg_data => x_msg_data,
4866: p_api_type => G_API_TYPE);
4867: WHEN OTHERS THEN
4864: x_msg_count => x_msg_count,
4865: x_msg_data => x_msg_data,
4866: p_api_type => G_API_TYPE);
4867: WHEN OTHERS THEN
4868: x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4869: p_api_name => l_api_name,
4870: p_pkg_name => G_PKG_NAME,
4871: p_exc_name => 'OTHERS',
4872: x_msg_count => x_msg_count,