[Home] [Help]
PACKAGE BODY: APPS.OKL_CHECKLIST_PVT
Source
1 PACKAGE BODY OKL_CHECKLIST_PVT AS
2 /* $Header: OKLRCKLB.pls 120.15 2006/07/11 09:43:04 dkagrawa noship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6 G_NEW_STS_CODE VARCHAR2(10) := 'NEW';
7 G_ACTIVE_STS_CODE VARCHAR2(10) := 'ACTIVE';
8 -- start: 06-June-2005 cklee okl.h Lease App IA Authoring
9 G_OKL_LEASE_APP CONSTANT VARCHAR2(30) := 'OKL_LEASE_APP';
10 G_CONTRACT CONSTANT VARCHAR2(30) := 'CONTRACT';
11 G_ACTIVE CONSTANT VARCHAR2(30) := 'ACTIVE';
12 -- end: 06-June-2005 cklee okl.h Lease App IA Authoring
13 L_MODULE FND_LOG_MESSAGES.MODULE%TYPE;
14 L_DEBUG_ENABLED VARCHAR2(10);
15 IS_DEBUG_PROCEDURE_ON BOOLEAN;
16 IS_DEBUG_STATEMENT_ON BOOLEAN;
17 ----------------------------------------------------------------------------
18 -- Procedures and Functions
19 ----------------------------------------------------------------------------
20
21 -- start: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
22 --------------------------------------------------------------------------
23 ----- Validate Status Code
24 --------------------------------------------------------------------------
25 FUNCTION validate_status_code(
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
35 CURSOR c_lookup (p_lookup_code VARCHAR2)
36 IS
37 SELECT 1
38 FROM fnd_lookups lok
39 WHERE lok.lookup_type = 'OKL_CHECKLIST_STATUS_CODE'
40 AND lok.lookup_code = p_lookup_code
41 ;
42
43 BEGIN
44
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,
54 p_token1_value => 'OKL_CHECKLIST.STATUS_CODE');
55 RAISE G_EXCEPTION_HALT_VALIDATION;
56 END IF;
57
58 -- There is no need if user doesn't want to update this column
59 /* ELSIF (p_mode = G_UPDATE_MODE) THEN
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;
69 END IF;
70 */
71 END IF;
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
90 RAISE G_EXCEPTION_HALT_VALIDATION;
91 END IF;
92 END IF;
93
94
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,
104 p_msg_name => G_UNEXPECTED_ERROR,
105 p_token1 => G_SQLCODE_TOKEN,
106 p_token1_value => SQLCODE,
107 p_token2 => G_SQLERRM_TOKEN,
108 p_token2_value => SQLERRM);
109 RETURN l_return_status;
110 END;
111 --------------------------------------------------------------------------
112 ----- Validate Purpose code
113 --------------------------------------------------------------------------
114 FUNCTION validate_purpose_code(
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;
124 l_org_status_code okl_checklists_uv.STATUS_CODE%type;
125 l_org_status_code_meaning okl_checklists_uv.STATUS_CODE_MEANING%type;
126
127 l_cur_status_code_meaning okl_checklists_uv.STATUS_CODE_MEANING%type;
128
129
130 CURSOR c_lookup (p_lookup_code VARCHAR2,
131 p_lookup_type VARCHAR2)
132 IS
133 SELECT lok.MEANING
134 FROM fnd_lookups lok
135 WHERE lok.lookup_type = p_lookup_type
136 AND lok.lookup_code = p_lookup_code
137 ;
138
139 CURSOR c_org_purpose (p_clh_id number)
140 IS
141 SELECT clh.CHECKLIST_PURPOSE_CODE,
142 clh.STATUS_CODE,
143 clh.STATUS_CODE_MEANING
144 FROM okl_checklists_uv clh
145 WHERE clh.id = p_clh_id
146 ;
147
148 BEGIN
149
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,
159 p_token1_value => 'OKL_CHECKLIST.CHECKLIST_PURPOSE_CODE');
160 RAISE G_EXCEPTION_HALT_VALIDATION;
161 END IF;
162
163 ELSIF (p_mode = G_UPDATE_MODE) THEN
164
165 ---------------------------------------------------------------
166 -- User are not allowed to modify the purpose code
167 ---------------------------------------------------------------
168 OPEN c_org_purpose(p_clhv_rec.id);
169 FETCH c_org_purpose INTO l_org_purpose_code,
170 l_org_status_code,
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
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');
186 FETCH c_lookup INTO l_cur_status_code_meaning;
187 CLOSE c_lookup;
188
189 ELSE
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',
199 p_token2_value => l_cur_status_code_meaning);
200 RAISE G_EXCEPTION_HALT_VALIDATION;
201 END IF;
202
203 END IF;
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');
213 FETCH c_lookup INTO l_dummy;
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
223 RAISE G_EXCEPTION_HALT_VALIDATION;
224 END IF;
225 END IF;
226
227
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,
237 p_msg_name => G_UNEXPECTED_ERROR,
238 p_token1 => G_SQLCODE_TOKEN,
239 p_token1_value => SQLCODE,
240 p_token2 => G_SQLERRM_TOKEN,
241 p_token2_value => SQLERRM);
242 RETURN l_return_status;
243 END;
244 --------------------------------------------------------------------------
245 ----- Validate Obj ID
246 --------------------------------------------------------------------------
247 FUNCTION validate_obj_ID(
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;
257
258 cursor c_purpose(p_clh_id number) is
259 select clh.CHECKLIST_PURPOSE_CODE
260 from okl_checklists clh
261 where clh.id = p_clh_id
262 ;
263
264 BEGIN
265
266 OPEN c_purpose(p_clhv_rec.id);
267 FETCH c_purpose INTO l_purpose_code;
268 CLOSE c_purpose;
269
270 IF (p_mode = G_INSERT_MODE) THEN
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,
280 p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_ID');
281 RAISE G_EXCEPTION_HALT_VALIDATION;
282 END IF;
283
284 -- There is no need if user doesn't want to update this column
285 /* ELSIF (p_mode = G_UPDATE_MODE) THEN
286
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;
296 END IF;
297 */
298 END IF;
299
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,
309 p_msg_name => G_UNEXPECTED_ERROR,
310 p_token1 => G_SQLCODE_TOKEN,
311 p_token1_value => SQLCODE,
312 p_token2 => G_SQLERRM_TOKEN,
313 p_token2_value => SQLERRM);
314 RETURN l_return_status;
315 END;
316
317 --------------------------------------------------------------------------
318 ----- Validate Obj type code
319 --------------------------------------------------------------------------
320 FUNCTION validate_obj_type_code(
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;
330
331 CURSOR c_lookup (p_lookup_code VARCHAR2)
332 IS
333 SELECT 1
334 FROM fnd_lookups lok
335 WHERE lok.lookup_type = 'OKL_CHECKLIST_OBJ_TYPE_CODE'
336 AND lok.lookup_code = p_lookup_code
337 ;
338
339 cursor c_purpose(p_clh_id number) is
340 select clh.CHECKLIST_PURPOSE_CODE
341 from okl_checklists clh
342 where clh.id = p_clh_id
343 ;
344
345 BEGIN
346
347 OPEN c_purpose(p_clhv_rec.id);
348 FETCH c_purpose INTO l_purpose_code;
349 CLOSE c_purpose;
350
351 IF (p_mode = G_INSERT_MODE) THEN
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,
361 p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_TYPE_CODE');
362 RAISE G_EXCEPTION_HALT_VALIDATION;
363 END IF;
364
365 -- There is no need if user doesn't want to update this column
366 /* ELSIF (p_mode = G_UPDATE_MODE) THEN
367
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;
377 END IF;
378 */
379 END IF;
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
398 RAISE G_EXCEPTION_HALT_VALIDATION;
399 END IF;
400 END IF;
401
402
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,
412 p_msg_name => G_UNEXPECTED_ERROR,
413 p_token1 => G_SQLCODE_TOKEN,
414 p_token1_value => SQLCODE,
415 p_token2 => G_SQLERRM_TOKEN,
416 p_token2_value => SQLERRM);
417 RETURN l_return_status;
418 END;
419 --------------------------------------------------------------------------
420 ----- Validate parent id
421 --------------------------------------------------------------------------
422 FUNCTION validate_ckl_id(
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
432 CURSOR c_parent_id (p_id NUMBER)
433 IS
434 SELECT 1
435 FROM okl_checklists ckl
436 WHERE ckl.CHECKLIST_PURPOSE_CODE = 'CHECKLIST_TEMPLATE_GROUP'
437 -- AND ckl.STATUS_CODE = 'NEW'
438 AND ckl.id = p_id
439 ;
440
441 BEGIN
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
460 RAISE G_EXCEPTION_HALT_VALIDATION;
461 END IF;
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');
471
472 RAISE G_EXCEPTION_HALT_VALIDATION;
473 END IF;
474
475
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,
485 p_msg_name => G_UNEXPECTED_ERROR,
486 p_token1 => G_SQLCODE_TOKEN,
487 p_token1_value => SQLCODE,
488 p_token2 => G_SQLERRM_TOKEN,
489 p_token2_value => SQLERRM);
490 RETURN l_return_status;
491 END;
492 -- end: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
493 --------------------------------------------------------------------------
494 ----- Validate Checklist Number
495 --------------------------------------------------------------------------
496 FUNCTION validate_clh_number(
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
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,
514 p_token1_value => 'Checklist Name');
515 RAISE G_EXCEPTION_HALT_VALIDATION;
516 END IF;
517
518 -- There is no need if user doesn't want to update this column
519 /* ELSIF (p_mode = G_UPDATE_MODE) THEN
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;
529 END IF;
530 */
531 END IF;
532
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,
542 p_msg_name => G_UNEXPECTED_ERROR,
543 p_token1 => G_SQLCODE_TOKEN,
544 p_token1_value => SQLCODE,
545 p_token2 => G_SQLERRM_TOKEN,
546 p_token2_value => SQLERRM);
547 RETURN l_return_status;
548 END;
549
550 --------------------------------------------------------------------------
551 ----- Validate Checklist Type
552 --------------------------------------------------------------------------
553 FUNCTION validate_clh_type(
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
563 l_org_checklist_type okl_checklists.CHECKLIST_TYPE%type;
564 l_org_status_code okl_checklists_uv.STATUS_CODE%type;
565 l_org_status_code_meaning okl_checklists_uv.STATUS_CODE_MEANING%type;
566
567 l_cur_status_code_meaning okl_checklists_uv.STATUS_CODE_MEANING%type;
568
569
570 CURSOR c_lookup (p_lookup_code VARCHAR2,
571 p_lookup_type VARCHAR2)
572 IS
573 SELECT lok.MEANING
574 FROM fnd_lookups lok
575 WHERE lok.lookup_type = p_lookup_type
576 AND lok.lookup_code = p_lookup_code
577 AND lok.enabled_flag = 'Y'
578 ;
579
580 CURSOR c_org_checklist_type (p_clh_id number)
581 IS
582 SELECT clh.CHECKLIST_TYPE,
583 clh.STATUS_CODE,
584 clh.STATUS_CODE_MEANING
585 FROM okl_checklists_uv clh
586 WHERE clh.id = p_clh_id
587 ;
588
589
590 BEGIN
591
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,
601 p_token1_value => 'Checklist Type');
602 RAISE G_EXCEPTION_HALT_VALIDATION;
603 END IF;
604
605 ---------------------------------------------------------------
606 -- User are not allowed to modify the checklist type
607 ---------------------------------------------------------------
608 OPEN c_org_checklist_type(p_clhv_rec.id);
609 FETCH c_org_checklist_type INTO l_org_checklist_type,
610 l_org_status_code,
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
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');
626 FETCH c_lookup INTO l_cur_status_code_meaning;
627 CLOSE c_lookup;
628
629 ELSE
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',
639 p_token2_value => l_cur_status_code_meaning);
640 RAISE G_EXCEPTION_HALT_VALIDATION;
641 END IF;
642
643 END IF;
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
662 RAISE G_EXCEPTION_HALT_VALIDATION;
663 END IF;
664 END IF;
665
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,
675 p_msg_name => G_UNEXPECTED_ERROR,
676 p_token1 => G_SQLCODE_TOKEN,
677 p_token1_value => SQLCODE,
678 p_token2 => G_SQLERRM_TOKEN,
679 p_token2_value => SQLERRM);
680 RETURN l_return_status;
681 END;
682
683 --------------------------------------------------------------------------
684 ----- Validate Short Description
685 --------------------------------------------------------------------------
686 FUNCTION validate_short_desc(
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)
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',
705 p_token2_value => 'Short Description');
706
707 RAISE G_EXCEPTION_HALT_VALIDATION;
708 END IF;
709 END IF;
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,
719 p_msg_name => G_UNEXPECTED_ERROR,
720 p_token1 => G_SQLCODE_TOKEN,
721 p_token1_value => SQLCODE,
722 p_token2 => G_SQLERRM_TOKEN,
723 p_token2_value => SQLERRM);
724 RETURN l_return_status;
725 END;
726
727 --------------------------------------------------------------------------
728 ----- Validate Description
729 --------------------------------------------------------------------------
730 FUNCTION validate_description(
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)
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',
749 p_token2_value => 'Description');
750
751 RAISE G_EXCEPTION_HALT_VALIDATION;
752 END IF;
753 END IF;
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,
763 p_msg_name => G_UNEXPECTED_ERROR,
764 p_token1 => G_SQLCODE_TOKEN,
765 p_token1_value => SQLCODE,
766 p_token2 => G_SQLERRM_TOKEN,
767 p_token2_value => SQLERRM);
768 RETURN l_return_status;
769 END;
770
771 --------------------------------------------------------------------------
772 ----- Validate Effective To
773 --------------------------------------------------------------------------
774 FUNCTION validate_effective_to(
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
784 -- fixed 10/22/03
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,
794 p_token1_value => 'Effective To');
795 RAISE G_EXCEPTION_HALT_VALIDATION;
796 END IF;
797
798 -- There is no need if user doesn't want to update this column
799 /* ELSIF (p_mode = G_UPDATE_MODE) THEN
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;
809 END IF;
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
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',
824 p_token2_value => 'today');
825
826 RAISE G_EXCEPTION_HALT_VALIDATION;
827 END IF;
828 END IF;
829
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,
839 p_msg_name => G_UNEXPECTED_ERROR,
840 p_token1 => G_SQLCODE_TOKEN,
841 p_token1_value => SQLCODE,
842 p_token2 => G_SQLERRM_TOKEN,
843 p_token2_value => SQLERRM);
844 RETURN l_return_status;
845 END;
846
847 -- start: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
848 --------------------------------------------------------------------------
849 ----- Validate date overlapping within group
850 --------------------------------------------------------------------------
851 FUNCTION validate_dates_overlap(
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;
861
862 CURSOR c_purpose (p_clh_id number)
863 IS
864 SELECT clh.CHECKLIST_PURPOSE_CODE
865 FROM okl_checklists clh
866 WHERE clh.id = p_clh_id
867 ;
868
869 -- get all rows by group id
870 CURSOR c_grp_dates (p_clh_id number)
871 IS
872 SELECT grp.START_DATE grp_START_DATE,
873 grp.END_DATE grp_END_DATE,
874 cld.START_DATE cld_START_DATE,
875 cld.END_DATE cld_END_DATE
876 FROM okl_checklists grp,
877 okl_checklists cld
878 WHERE grp.id = cld.ckl_id
879 AND grp.id = p_clh_id
880 ;
881
882 -- get row by checklist id
883 CURSOR c_checklist_dates (p_clh_id number)
884 IS
885 SELECT grp.START_DATE grp_START_DATE,
886 grp.END_DATE grp_END_DATE,
887 cld.START_DATE cld_START_DATE,
888 cld.END_DATE cld_END_DATE
889 FROM okl_checklists grp,
890 okl_checklists cld
891 WHERE grp.id = cld.ckl_id
892 AND cld.id = p_clh_id
893 ;
894
895 BEGIN
896
897 -- Notice that end date is required for checklist template. Assume that
898 -- system pass the end date check already.
899 OPEN c_purpose(p_clhv_rec.id);
900 FETCH c_purpose INTO l_purpose_code;
901 CLOSE c_purpose;
902
903 --dbms_output.put_line('l_purpose_code :'|| l_purpose_code);
904
905 ---------------------------------------------------------------------
906 -- Group checklist
907 ---------------------------------------------------------------------
908 IF l_purpose_code = 'CHECKLIST_TEMPLATE_GROUP' THEN
909
910 FOR r_this_row IN c_grp_dates (p_clhv_rec.id) LOOP
911
912 IF r_this_row.grp_START_DATE IS NOT NULL THEN
913
914 IF r_this_row.cld_START_DATE IS NOT NULL THEN
915
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
934 END IF;
935
936 ELSE -- group start date is null
937
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
947 -- We don't need to check overlap if both sart dates are null
948 END IF;
949
950 END IF;
951
952 END LOOP;
953
954 ---------------------------------------------------------------------
955 -- checklist
956 ---------------------------------------------------------------------
957 ELSIF l_purpose_code = 'CHECKLIST_TEMPLATE' THEN
958
959 FOR r_this_row IN c_checklist_dates (p_clhv_rec.id) LOOP
960
961 IF r_this_row.grp_START_DATE IS NOT NULL THEN
962
963 IF r_this_row.cld_START_DATE IS NOT NULL THEN
964
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
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
982 END IF;
983
984 ELSE -- group start date is null
985
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
995 -- We don't need to check overlap if both sart dates are null
996 END IF;
997
998 END IF;
999
1000 END LOOP;
1001
1002 END IF;
1003
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,
1013 p_msg_name => G_UNEXPECTED_ERROR,
1014 p_token1 => G_SQLCODE_TOKEN,
1015 p_token1_value => SQLCODE,
1016 p_token2 => G_SQLERRM_TOKEN,
1017 p_token2_value => SQLERRM);
1018 RETURN l_return_status;
1019 END;
1020
1021
1022 -- end: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
1023
1024 --------------------------------------------------------------------------
1025 ----- Validate Checklist Number uniqueness :after image
1026 --------------------------------------------------------------------------
1027 FUNCTION validate_unq_clh_number(
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
1037 CURSOR c_unq (p_checklist_number VARCHAR2, p_checklist_type VARCHAR2)
1038 IS
1039 SELECT count(1)
1040 FROM okl_checklists clh
1041 WHERE UPPER(clh.checklist_number) = UPPER(p_checklist_number)
1042 AND clh.checklist_type = p_checklist_type
1043 ;
1044
1045 BEGIN
1046
1047 OPEN c_unq(p_clhv_rec.checklist_number, p_clhv_rec.checklist_type);
1048 FETCH c_unq INTO l_count;
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
1058 RAISE G_EXCEPTION_HALT_VALIDATION;
1059 END IF;
1060
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,
1070 p_msg_name => G_UNEXPECTED_ERROR,
1071 p_token1 => G_SQLCODE_TOKEN,
1072 p_token1_value => SQLCODE,
1073 p_token2 => G_SQLERRM_TOKEN,
1074 p_token2_value => SQLERRM);
1075 RETURN l_return_status;
1076 END;
1077
1078 --------------------------------------------------------------------------
1079 ----- Validate Effective from and Effective To: after image
1080 --------------------------------------------------------------------------
1081 FUNCTION validate_effective_date(
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)
1091 is select 1
1092 from okl_checklists clh
1093 where clh.start_date is not null
1094 and trunc(clh.start_date) > trunc(clh.end_date)
1095 and clh.id = p_id
1096 ;
1097
1098 BEGIN
1099
1100 open c_date(p_clhv_rec.id);
1101 fetch c_date into l_dummy;
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',
1111 p_token2_value => 'Effective From');
1112
1113 RAISE G_EXCEPTION_HALT_VALIDATION;
1114 END IF;
1115
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,
1125 p_msg_name => G_UNEXPECTED_ERROR,
1126 p_token1 => G_SQLCODE_TOKEN,
1127 p_token1_value => SQLCODE,
1128 p_token2 => G_SQLERRM_TOKEN,
1129 p_token2_value => SQLERRM);
1130 RETURN l_return_status;
1131 END;
1132 -- start: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
1133 --------------------------------------------------------------------------
1134 ----- Validate duplicated type, item, and function across the checklist
1135 ----- templates in the same group
1136 --------------------------------------------------------------------------
1137 FUNCTION validate_duplicated_keys(
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
1147 l_ckl_id okl_checklist_details.ckl_id%type;
1148
1149 CURSOR c_ckl_id(p_cld_id number) is
1150 select cld.ckl_id
1151 from okl_checklist_details cld
1152 where cld.id = p_cld_id
1153 ;
1154
1155 CURSOR c_dup (p_clh_id number)
1156 IS
1157 SELECT 1
1158 FROM okl_checklist_details cld,
1159 okl_checklists clh
1160 where clh.id = cld.ckl_id -- get clh.checklist_type
1161 and clh.checklist_purpose_code = 'CHECKLIST_TEMPLATE'
1162 and cld.ckl_id in (select lst.id
1163 from okl_checklists lst,
1164 okl_checklists prt
1165 where lst.ckl_id = prt.ckl_id
1166 and prt.id = p_clh_id)
1167 GROUP BY cld.todo_item_code, cld.FUNCTION_ID, clh.checklist_type
1168 HAVING count(1) > 1
1169 ;
1170
1171
1172 BEGIN
1173
1174 OPEN c_ckl_id(p_cldv_rec.id);
1175 FETCH c_ckl_id INTO l_ckl_id;
1176 CLOSE c_ckl_id;
1177
1178 OPEN c_dup(l_ckl_id);
1179 FETCH c_dup INTO l_dummy;
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;
1189
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,
1199 p_msg_name => G_UNEXPECTED_ERROR,
1200 p_token1 => G_SQLCODE_TOKEN,
1201 p_token1_value => SQLCODE,
1202 p_token2 => G_SQLERRM_TOKEN,
1203 p_token2_value => SQLERRM);
1204 RETURN l_return_status;
1205 END;
1206 --------------------------------------------------------------------------
1207 ----- Validate MANDATORY FLAG
1208 --------------------------------------------------------------------------
1209 FUNCTION validate_MANDATORY_FLAG(
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
1219 CURSOR c_lookup (p_lookup_code VARCHAR2)
1220 IS
1221 SELECT 1
1222 FROM fnd_lookups lok
1223 WHERE lok.lookup_type = 'OKL_YES_NO'
1224 AND lok.lookup_code = p_lookup_code
1225 ;
1226
1227 BEGIN
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
1246 RAISE G_EXCEPTION_HALT_VALIDATION;
1247 END IF;
1248 END IF;
1249
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,
1259 p_msg_name => G_UNEXPECTED_ERROR,
1260 p_token1 => G_SQLCODE_TOKEN,
1261 p_token1_value => SQLCODE,
1262 p_token2 => G_SQLERRM_TOKEN,
1263 p_token2_value => SQLERRM);
1264 RETURN l_return_status;
1265 END;
1266 --------------------------------------------------------------------------
1267 ----- Validate USER_COMPLETE_FLAG
1268 --------------------------------------------------------------------------
1269 FUNCTION validate_COMPLETE_FLAG(
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
1279 CURSOR c_lookup (p_lookup_code VARCHAR2)
1280 IS
1281 SELECT 1
1282 FROM fnd_lookups lok
1283 WHERE lok.lookup_type = 'OKL_YES_NO'
1284 AND lok.lookup_code = p_lookup_code
1285 ;
1286
1287 BEGIN
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
1306 RAISE G_EXCEPTION_HALT_VALIDATION;
1307 END IF;
1308 END IF;
1309
1310
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,
1320 p_msg_name => G_UNEXPECTED_ERROR,
1321 p_token1 => G_SQLCODE_TOKEN,
1322 p_token1_value => SQLCODE,
1323 p_token2 => G_SQLERRM_TOKEN,
1324 p_token2_value => SQLERRM);
1325 RETURN l_return_status;
1326 END;
1327 --------------------------------------------------------------------------
1328 ----- Validate FUNCTION_VALIDATE_RSTS
1329 --------------------------------------------------------------------------
1330 FUNCTION validate_FUN_VALIDATE_RSTS(
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
1340 CURSOR c_lookup (p_lookup_code VARCHAR2)
1341 IS
1342 SELECT 1
1343 FROM fnd_lookups lok
1344 WHERE lok.lookup_type = 'OKL_FUN_VALIDATE_RSTS'
1345 AND lok.lookup_code = p_lookup_code
1346 ;
1347
1348 BEGIN
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
1367 RAISE G_EXCEPTION_HALT_VALIDATION;
1368 END IF;
1369 END IF;
1370
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,
1380 p_msg_name => G_UNEXPECTED_ERROR,
1381 p_token1 => G_SQLCODE_TOKEN,
1382 p_token1_value => SQLCODE,
1383 p_token2 => G_SQLERRM_TOKEN,
1384 p_token2_value => SQLERRM);
1385 RETURN l_return_status;
1386 END;
1387 --------------------------------------------------------------------------
1388 ----- Validate FUNCTION_ID
1389 --------------------------------------------------------------------------
1390 FUNCTION validate_FUNCTION_ID(
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
1400 CURSOR c_fun (p_id number)
1401 IS
1402 SELECT 1
1403 FROM OKL_DATA_SRC_FNCTNS_V fun
1404 WHERE fun.id = p_id
1405 ;
1406
1407 BEGIN
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
1426 RAISE G_EXCEPTION_HALT_VALIDATION;
1427 END IF;
1428 END IF;
1429
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,
1439 p_msg_name => G_UNEXPECTED_ERROR,
1440 p_token1 => G_SQLCODE_TOKEN,
1441 p_token1_value => SQLCODE,
1442 p_token2 => G_SQLERRM_TOKEN,
1443 p_token2_value => SQLERRM);
1444 RETURN l_return_status;
1445 END;
1446 --------------------------------------------------------------------------
1447 ----- Validate Checklist purpose
1448 --------------------------------------------------------------------------
1449 FUNCTION validate_purpose_code(
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
1459 CURSOR c_grp_clist (p_cld_id okl_checklist_details.id%type)
1460 IS
1461 SELECT 1
1462 FROM okl_checklists clh,
1463 okl_checklist_details cld
1464 WHERE clh.id = cld.ckl_id
1465 AND cld.id = p_cld_id
1466 AND clh.CHECKLIST_PURPOSE_CODE = 'CHECKLIST_TEMPLATE_GROUP'
1467 ;
1468
1469 BEGIN
1470
1471 OPEN c_grp_clist(p_cldv_rec.id);
1472 FETCH c_grp_clist INTO l_dummy;
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;
1482
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,
1492 p_msg_name => G_UNEXPECTED_ERROR,
1493 p_token1 => G_SQLCODE_TOKEN,
1494 p_token1_value => SQLCODE,
1495 p_token2 => G_SQLERRM_TOKEN,
1496 p_token2_value => SQLERRM);
1497 RETURN l_return_status;
1498 END;
1499 --------------------------------------------------------------------------
1500 ----- Validate inst Checklist Type
1501 --------------------------------------------------------------------------
1502 FUNCTION validate_inst_clh_type(
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;
1512
1513 CURSOR c_type (p_checklist_type VARCHAR2)
1514 IS
1515 SELECT 1
1516 FROM fnd_lookups lok
1517 WHERE lok.lookup_type = G_CHECKLIST_TYPE_LOOKUP_TYPE
1518 AND lok.lookup_code = p_checklist_type
1519 ;
1520
1521 CURSOR c_purpose (p_cld_id number)
1522 IS
1523 SELECT clh.checklist_purpose_code
1524 FROM okl_checklists clh,
1525 okl_checklist_details cld
1526 WHERE clh.id = cld.ckl_id
1527 AND cld.id = p_cld_id
1528 ;
1529
1530 BEGIN
1531
1532 OPEN c_purpose(p_cldv_rec.id);
1533 FETCH c_purpose INTO l_purpose;
1534 CLOSE c_purpose;
1535
1536 IF (p_mode = G_INSERT_MODE) THEN
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,
1546 p_token1_value => 'Checklist Type');
1547 RAISE G_EXCEPTION_HALT_VALIDATION;
1548 END IF;
1549
1550 --START| 19-Dec-2005 cklee -- Set INST_CHECKLIST_TYPE as a required column for |
1551 --| checklist instance record |
1552 ELSIF (p_mode = G_UPDATE_MODE) THEN
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;
1562 END IF;
1563
1564 --END| 19-Dec-2005 cklee -- Set INST_CHECKLIST_TYPE as a required column for |
1565 --| checklist instance record |
1566 END IF;
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
1585 RAISE G_EXCEPTION_HALT_VALIDATION;
1586 END IF;
1587 END IF;
1588
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,
1598 p_msg_name => G_UNEXPECTED_ERROR,
1599 p_token1 => G_SQLCODE_TOKEN,
1600 p_token1_value => SQLCODE,
1601 p_token2 => G_SQLERRM_TOKEN,
1602 p_token2_value => SQLERRM);
1603 RETURN l_return_status;
1604 END;
1605 --------------------------------------------------------------------------
1606 ----- Validate to do item code
1607 --------------------------------------------------------------------------
1608 FUNCTION validate_todo_item_code(
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
1618 CURSOR c_todo (p_todo_item_code okl_checklist_details.todo_item_code%type)
1619 IS
1620 SELECT 1
1621 FROM fnd_lookups lok
1622 WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
1623 AND lok.lookup_code = p_todo_item_code
1624 ;
1625
1626 BEGIN
1627
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,
1637 p_token1_value => 'Item Code');
1638 RAISE G_EXCEPTION_HALT_VALIDATION;
1639 END IF;
1640
1641 -- There is no need if user doesn't want to update this column
1642 /* ELSIF (p_mode = G_UPDATE_MODE) THEN
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;
1652 END IF;
1653 */
1654 END IF;
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
1673 RAISE G_EXCEPTION_HALT_VALIDATION;
1674 END IF;
1675
1676 END IF;
1677
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,
1687 p_msg_name => G_UNEXPECTED_ERROR,
1688 p_token1 => G_SQLCODE_TOKEN,
1689 p_token1_value => SQLCODE,
1690 p_token2 => G_SQLERRM_TOKEN,
1691 p_token2_value => SQLERRM);
1692 RETURN l_return_status;
1693 END;
1694 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
1695 /*
1696 --------------------------------------------------------------------------
1697 ----- Validate to do item code
1698 --------------------------------------------------------------------------
1699 FUNCTION validate_todo_item_code(
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;
1709 l_clh_end_date date;
1710
1711 l_row_not_found boolean := false;
1712
1713 CURSOR c_todo (p_todo_item_code okl_checklist_details.todo_item_code%type,
1714 p_ckl_id okl_checklists.id%type)
1715 IS
1716 SELECT lok.START_DATE_ACTIVE,
1717 lok.END_DATE_ACTIVE,
1718 clh.START_DATE,
1719 clh.END_DATE
1720 FROM fnd_lookups lok,
1721 okl_checklists clh
1722 -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
1723 -- WHERE decode(clh.checklist_type, 'CREDITLINE', 'OKL_TODO_CREDIT_CHKLST'
1724 -- , 'FUNDING_REQUEST', 'OKL_TODO_FUNDING_CHKLST')
1725 -- = lok.lookup_type
1726 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
1727 WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
1728 AND lok.lookup_code = p_todo_item_code
1729 AND clh.id = p_ckl_id
1730 ;
1731
1732 BEGIN
1733
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,
1743 p_token1_value => 'Item Code');
1744 RAISE G_EXCEPTION_HALT_VALIDATION;
1745 END IF;
1746
1747 ELSIF (p_mode = G_UPDATE_MODE) THEN
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;
1757 END IF;
1758
1759 END IF;
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,
1769 l_todo_end_date, -- can be null
1770 l_clh_start_date, -- can be null
1771 l_clh_end_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
1781 RAISE G_EXCEPTION_HALT_VALIDATION;
1782 END IF;
1783
1784 -- check vs header's start date, and end date
1785 -- todo start date NOT between checklist start, and end date
1786 -- 1. todo start date >= checklist's end date
1787 -- 2. todo start date <= checklist's start date
1788
1789 -- 4. todo end date >= checklist start date
1790
1791 END IF;
1792
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,
1802 p_msg_name => G_UNEXPECTED_ERROR,
1803 p_token1 => G_SQLCODE_TOKEN,
1804 p_token1_value => SQLCODE,
1805 p_token2 => G_SQLERRM_TOKEN,
1806 p_token2_value => SQLERRM);
1807 RETURN l_return_status;
1808 END;
1809 */
1810 --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
1811 --------------------------------------------------------------------------
1812 ----- Validate item code effective from vs header's effective dates
1813 --------------------------------------------------------------------------
1814 FUNCTION validate_item_effective_from(
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;
1824 l_todo_item_code okl_checklist_details.todo_item_code%type;
1825
1826 -- Check if todo item effective from date within the checklist header's effective dates range
1827 CURSOR c_todo_tmp_chk (p_cld_id okl_checklist_details.id%type)
1828 IS
1829 SELECT clh.checklist_number, cld.todo_item_code
1830 FROM fnd_lookups lok,
1831 okl_checklist_details cld,
1832 okl_checklists clh
1833 WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
1834 AND cld.id = p_cld_id
1835 AND lok.lookup_code = cld.todo_item_code
1836 AND cld.ckl_id = clh.id
1837 AND clh.checklist_purpose_code in ('CHECKLIST_TEMPLATE', 'CHECKLIST_INSTANCE')
1838 AND NOT (TRUNC(NVL(lok.START_DATE_ACTIVE, SYSDATE))
1839 BETWEEN TRUNC(NVL(clh.START_DATE, lok.START_DATE_ACTIVE)) AND
1840 NVL(TRUNC(clh.END_DATE),lok.START_DATE_ACTIVE))
1841 ;
1842
1843 -- Check if todo item effective from date within the checklist group header's effective dates range
1844 CURSOR c_todo_grp_chk (p_cld_id okl_checklist_details.id%type)
1845 IS
1846 SELECT clh_grp.checklist_number, cld.todo_item_code
1847 FROM fnd_lookups lok,
1848 okl_checklist_details cld,
1849 okl_checklists clh,
1850 okl_checklists clh_grp
1851 WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
1852 AND cld.id = p_cld_id
1853 AND lok.lookup_code = cld.todo_item_code
1854 AND cld.ckl_id = clh.id
1855 AND clh.ckl_id = clh_grp.id
1856 AND clh_grp.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
1857 AND NOT (TRUNC(NVL(lok.START_DATE_ACTIVE, SYSDATE))
1858 BETWEEN TRUNC(NVL(clh_grp.START_DATE, lok.START_DATE_ACTIVE)) AND
1859 NVL(TRUNC(clh_grp.END_DATE),lok.START_DATE_ACTIVE))
1860 ;
1861
1862 BEGIN
1863
1864 ------------------------------------------------------------------------------
1865 -- Check if todo itme effective from date within the checklist header's
1866 -- effective dates range
1867 ------------------------------------------------------------------------------
1868
1869 OPEN c_todo_tmp_chk (p_cldv_rec.id);
1870 FETCH c_todo_tmp_chk INTO l_checklist_number, l_todo_item_code;
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',
1880 p_token2_value => l_checklist_number);
1881
1882 RAISE G_EXCEPTION_HALT_VALIDATION;
1883 END IF;
1884
1885 ------------------------------------------------------------------------------
1886 -- Check if todo itme effective from date within the checklist header's group
1887 -- effective dates range
1888 ------------------------------------------------------------------------------
1889
1890 OPEN c_todo_grp_chk (p_cldv_rec.id);
1891 FETCH c_todo_grp_chk INTO l_checklist_number, l_todo_item_code;
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',
1901 p_token2_value => l_checklist_number);
1902
1903 RAISE G_EXCEPTION_HALT_VALIDATION;
1904 END IF;
1905
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,
1915 p_msg_name => G_UNEXPECTED_ERROR,
1916 p_token1 => G_SQLCODE_TOKEN,
1917 p_token1_value => SQLCODE,
1918 p_token2 => G_SQLERRM_TOKEN,
1919 p_token2_value => SQLERRM);
1920 RETURN l_return_status;
1921 END;
1922 --END:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
1923
1924 --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
1925 --------------------------------------------------------------------------
1926 ----- Validate Effective To vs item's date
1927 --------------------------------------------------------------------------
1928 FUNCTION validate_dates_w_item(
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
1938 -- existing checklist items for a checcklist or a grp checklist
1939 cursor c_item_date_range_chk (p_clh_id number) is
1940 select cld.id, cld.todo_item_code
1941 from okl_checklist_details cld,
1942 okl_checklists clh
1943 where cld.ckl_id = clh.id
1944 and cld.ckl_id = p_clh_id
1945 and clh.checklist_purpose_code in ('CHECKLIST_TEMPLATE', 'CHECKLIST_INSTANCE')
1946 union
1947 select cld.id, cld.todo_item_code
1948 from okl_checklist_details cld,
1949 okl_checklists clh,
1950 okl_checklists clh_grp
1951 where cld.ckl_id = clh.id
1952 and clh.ckl_id = clh_grp.id
1953 and clh_grp.id = p_clh_id
1954 and clh_grp.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
1955 ;
1956 BEGIN
1957
1958 FOR r_this_row IN c_item_date_range_chk (p_clhv_rec.id) LOOP
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,
1977 p_msg_name => G_UNEXPECTED_ERROR,
1978 p_token1 => G_SQLCODE_TOKEN,
1979 p_token1_value => SQLCODE,
1980 p_token2 => G_SQLERRM_TOKEN,
1981 p_token2_value => SQLERRM);
1982 RETURN l_return_status;
1983 END;
1984 --END:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
1985
1986 --------------------------------------------------------------------------
1987 ----- Validate Checklist to do item code uniqueness :after image
1988 --------------------------------------------------------------------------
1989 FUNCTION validate_unq_todo_item(
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
1999 CURSOR c_unq (p_ckl_id okl_checklists.id%type)
2000 IS
2001 SELECT 1
2002 FROM okl_checklist_details cld
2003 WHERE cld.ckl_id = p_ckl_id
2004 -- START: 06-Jan-2006 cklee -- Fixed for instance checklist item duplication check |
2005 -- GROUP BY cld.todo_item_code, cld.function_id
2006 GROUP BY cld.todo_item_code, cld.function_id, cld.INST_CHECKLIST_TYPE
2007 -- END: 06-Jan-2006 cklee -- Fixed for instance checklist item duplication check |
2008 HAVING count(1) > 1
2009 ;
2010
2011 BEGIN
2012
2013 OPEN c_unq(p_cldv_rec.ckl_id);
2014 FETCH c_unq INTO l_dummy;
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');
2024 p_token1_value => 'Item Code, Function, and Type');
2025 -- START: 06-Jan-2006 cklee -- Fixed for instance checklist item duplication check |
2026
2027 RAISE G_EXCEPTION_HALT_VALIDATION;
2028 END IF;
2029
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,
2039 p_msg_name => G_UNEXPECTED_ERROR,
2040 p_token1 => G_SQLCODE_TOKEN,
2041 p_token1_value => SQLCODE,
2042 p_token2 => G_SQLERRM_TOKEN,
2043 p_token2_value => SQLERRM);
2044 RETURN l_return_status;
2045 END;
2046 --PAGARG: Bug 4872271: Added procedure to validate value of APPEAL_FLAG
2047 --------------------------------------------------------------------------
2048 -- Validate APPEAL FLAG
2049 --------------------------------------------------------------------------
2050 FUNCTION validate_appeal_flag(
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
2060 CURSOR c_lookup(p_lookup_code VARCHAR2)
2061 IS
2062 SELECT 1
2063 FROM fnd_lookups lok
2064 WHERE lok.lookup_type = 'OKL_YES_NO'
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;
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;
2082 END IF;
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,
2092 p_msg_name => G_UNEXPECTED_ERROR,
2093 p_token1 => G_SQLCODE_TOKEN,
2094 p_token1_value => SQLCODE,
2095 p_token2 => G_SQLERRM_TOKEN,
2096 p_token2_value => SQLERRM);
2097 RETURN l_return_status;
2098 END validate_appeal_flag;
2099
2100 --------------------------------------------------------------------------
2101 FUNCTION validate_header_attributes(
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
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
2202 END IF;
2203 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2204
2205 RETURN x_return_status;
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,
2215 p_token2 => G_SQLERRM_TOKEN,
2216 p_token2_value => SQLERRM);
2217 RETURN l_return_status;
2218 END validate_header_attributes;
2219 -----------------------------------------------------------------------------
2220 --- validate attrs after image-----------------------------------------------
2221 -----------------------------------------------------------------------------
2222 FUNCTION validate_hdr_attr_aftimg(
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
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;
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;
2259 END IF;
2260 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
2261
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;
2271 END IF;
2272 --END:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
2273
2274 RETURN x_return_status;
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,
2284 p_token2 => G_SQLERRM_TOKEN,
2285 p_token2_value => SQLERRM);
2286 RETURN l_return_status;
2287 END validate_hdr_attr_aftimg;
2288
2289 -----------------------------------------------------------------------------
2290 -----------------------------------------------------------------------------
2291 -----------------------------------------------------------------------------
2292
2293 FUNCTION validate_line_attributes(
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
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;
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;
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;
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;
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;
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;
2367 END IF;
2368
2369 RETURN x_return_status;
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,
2379 p_token2 => G_SQLERRM_TOKEN,
2380 p_token2_value => SQLERRM);
2381 RETURN l_return_status;
2382 END validate_line_attributes;
2383
2384 -----------------------------------------------------------------------------
2385 --- validate attrs after image-----------------------------------------------
2386 -----------------------------------------------------------------------------
2387
2388 FUNCTION validate_line_attr_aftimg(
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
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;
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;
2425 END IF;
2426 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
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;
2436 END IF;
2437 --END:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
2438
2439 RETURN x_return_status;
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,
2449 p_token2 => G_SQLERRM_TOKEN,
2450 p_token2_value => SQLERRM);
2451 RETURN l_return_status;
2452 END validate_line_attr_aftimg;
2453
2454 ----------------------------------------------------------------------------------
2455 -- Start of comments
2456 --
2457 -- Procedure Name : create_checklist_hdr
2458 -- Description : wrapper api for create checklist template header
2459 -- Business Rules :
2460 -- Parameters :
2461 -- Version : 1.0
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
2471 ,x_clhv_rec OUT NOCOPY clhv_rec_type
2472 )
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
2482 -- Set API savepoint
2483 SAVEPOINT create_checklist_hdr;
2484
2485 -- Check for call compatibility
2486 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
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';
2511 END IF;
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;
2521 END IF;
2522
2523 okl_clh_pvt.insert_row(
2524 p_api_version => p_api_version,
2525 p_init_msg_list => p_init_msg_list,
2526 x_return_status => x_return_status,
2527 x_msg_count => x_msg_count,
2528 x_msg_data => x_msg_data,
2529 p_clhv_rec => lp_clhv_rec,
2530 x_clhv_rec => x_clhv_rec);
2531
2532 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2533 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2534 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2535 raise OKC_API.G_EXCEPTION_ERROR;
2536 End If;
2537
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;
2547 END IF;
2548
2549 /*** End API body ******************************************************/
2550
2551 -- Get message count and if count is 1, get message info
2552 FND_MSG_PUB.Count_And_Get
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,
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
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,
2579 p_token2_value => SQLERRM);
2580 FND_MSG_PUB.Count_And_Get
2581 (p_count => x_msg_count,
2582 p_data => x_msg_data);
2583
2584 end create_checklist_hdr;
2585
2586 ----------------------------------------------------------------------------------
2587 -- Start of comments
2588 --
2589 -- Procedure Name : update_checklist_hdr
2590 -- Description : wrapper api for update checklist template header
2591 -- Business Rules :
2592 -- Parameters :
2593 -- Version : 1.0
2594 -- End of comments
2595 ----------------------------------------------------------------------------------
2596 PROCEDURE update_checklist_hdr(
2597 p_api_version IN NUMBER
2598 ,p_init_msg_list IN VARCHAR2
2599 ,x_return_status OUT NOCOPY VARCHAR2
2600 ,x_msg_count OUT NOCOPY NUMBER
2601 ,x_msg_data OUT NOCOPY VARCHAR2
2602 ,p_clhv_rec IN clhv_rec_type
2603 ,x_clhv_rec OUT NOCOPY clhv_rec_type
2604 )
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
2614 -- Set API savepoint
2615 SAVEPOINT update_checklist_hdr;
2616
2617 -- Check for call compatibility
2618 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
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;
2645
2646 okl_clh_pvt.update_row(
2647 p_api_version => p_api_version,
2648 p_init_msg_list => p_init_msg_list,
2649 x_return_status => x_return_status,
2650 x_msg_count => x_msg_count,
2651 x_msg_data => x_msg_data,
2652 p_clhv_rec => lp_clhv_rec,
2653 x_clhv_rec => x_clhv_rec);
2654
2655 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2656 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2657 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2658 raise OKC_API.G_EXCEPTION_ERROR;
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;
2668 END IF;
2669
2670
2671 /*** End API body ******************************************************/
2672
2673 -- Get message count and if count is 1, get message info
2674 FND_MSG_PUB.Count_And_Get
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,
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
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,
2701 p_token2_value => SQLERRM);
2702 FND_MSG_PUB.Count_And_Get
2703 (p_count => x_msg_count,
2704 p_data => x_msg_data);
2705
2706 end update_checklist_hdr;
2707
2708 ----------------------------------------------------------------------------------
2709 -- Start of comments
2710 --
2711 -- Procedure Name : delete_checklist_hdr
2712 -- Description : wrapper api for delete checklist template header
2713 -- Business Rules :
2714 -- Parameters :
2715 -- Version : 1.0
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
2725 )
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
2735 -- Set API savepoint
2736 SAVEPOINT delete_checklist_hdr;
2737
2738 -- Check for call compatibility
2739 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
2758 okl_clh_pvt.delete_row(
2759 p_api_version => p_api_version,
2760 p_init_msg_list => p_init_msg_list,
2761 x_return_status => x_return_status,
2762 x_msg_count => x_msg_count,
2763 x_msg_data => x_msg_data,
2764 p_clhv_rec => lp_clhv_rec);
2765
2766 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2767 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2768 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2769 raise OKC_API.G_EXCEPTION_ERROR;
2770 End If;
2771
2772 /*** End API body ******************************************************/
2773
2774 -- Get message count and if count is 1, get message info
2775 FND_MSG_PUB.Count_And_Get
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,
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
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,
2802 p_token2_value => SQLERRM);
2803 FND_MSG_PUB.Count_And_Get
2804 (p_count => x_msg_count,
2805 p_data => x_msg_data);
2806
2807 end delete_checklist_hdr;
2808
2809 ----------------------------------------------------------------------------------
2810 -- Start of comments
2811 --
2812 -- Procedure Name : create_checklist_dtl
2813 -- Description : wrapper api for create checklist template details
2814 -- Business Rules :
2815 -- Parameters :
2816 -- Version : 1.0
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
2826 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
2827 )
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
2837 -- Set API savepoint
2838 SAVEPOINT create_checklist_dtl;
2839
2840 -- Check for call compatibility
2841 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
2860 IF (lp_cldv_tbl.COUNT > 0) THEN
2861 i := lp_cldv_tbl.FIRST;
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;
2871 END IF;
2872
2873 EXIT WHEN (i = lp_cldv_tbl.LAST);
2874 i := lp_cldv_tbl.NEXT(i);
2875 END LOOP;
2876 END IF;
2877
2878 okl_cld_pvt.insert_row(
2879 p_api_version => p_api_version,
2880 p_init_msg_list => p_init_msg_list,
2881 x_return_status => x_return_status,
2882 x_msg_count => x_msg_count,
2883 x_msg_data => x_msg_data,
2884 p_cldv_tbl => lp_cldv_tbl,
2885 x_cldv_tbl => x_cldv_tbl);
2886
2887 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2888 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2889 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2890 raise OKC_API.G_EXCEPTION_ERROR;
2891 End If;
2892
2893 IF (lp_cldv_tbl.COUNT > 0) THEN
2894 i := lp_cldv_tbl.FIRST;
2895 LOOP
2896
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;
2906 END IF;
2907
2908 EXIT WHEN (i = lp_cldv_tbl.LAST);
2909 i := lp_cldv_tbl.NEXT(i);
2910 END LOOP;
2911 END IF;
2912
2913 /*** End API body ******************************************************/
2914
2915 -- Get message count and if count is 1, get message info
2916 FND_MSG_PUB.Count_And_Get
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,
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
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,
2943 p_token2_value => SQLERRM);
2944 FND_MSG_PUB.Count_And_Get
2945 (p_count => x_msg_count,
2946 p_data => x_msg_data);
2947
2948 end create_checklist_dtl;
2949
2950 ----------------------------------------------------------------------------------
2951 -- Start of comments
2952 --
2953 -- Procedure Name : update_checklist_dtl
2954 -- Description : wrapper api for update checklist template details
2955 -- Business Rules :
2956 -- Parameters :
2957 -- Version : 1.0
2958 -- End of comments
2959 ----------------------------------------------------------------------------------
2960 PROCEDURE update_checklist_dtl(
2961 p_api_version IN NUMBER
2962 ,p_init_msg_list IN VARCHAR2
2963 ,x_return_status OUT NOCOPY VARCHAR2
2964 ,x_msg_count OUT NOCOPY NUMBER
2965 ,x_msg_data OUT NOCOPY VARCHAR2
2966 ,p_cldv_tbl IN cldv_tbl_type
2967 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
2968 )
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
2978 l_checklist_obj_id number;
2979 cursor c_dnz_object_id (p_dtl_id number) is
2980 select h.CHECKLIST_OBJ_ID
2981 from okl_checklists h,
2982 okl_checklist_details d
2983 where h.id = d.ckl_id
2984 and d.id = p_dtl_id;
2985 --END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
2986
2987 begin
2988 -- Set API savepoint
2989 SAVEPOINT update_checklist_dtl;
2990
2991 -- Check for call compatibility
2992 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3011 IF (lp_cldv_tbl.COUNT > 0) THEN
3012
3013 i := lp_cldv_tbl.FIRST;
3014
3015 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
3016 open c_dnz_object_id(lp_cldv_tbl(i).id);
3017 fetch c_dnz_object_id into l_checklist_obj_id;
3018 close c_dnz_object_id;
3019 --END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
3020
3021 LOOP
3022
3023 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
3024 -- always assign default in case the dnz_object_id is null
3025 IF l_checklist_obj_id IS NOT NULL THEN
3026 lp_cldv_tbl(i).dnz_checklist_obj_id := l_checklist_obj_id;
3027 END IF;
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;
3037 END IF;
3038
3039 EXIT WHEN (i = lp_cldv_tbl.LAST);
3040 i := lp_cldv_tbl.NEXT(i);
3041 END LOOP;
3042 END IF;
3043
3044 okl_cld_pvt.update_row(
3045 p_api_version => p_api_version,
3046 p_init_msg_list => p_init_msg_list,
3047 x_return_status => x_return_status,
3048 x_msg_count => x_msg_count,
3049 x_msg_data => x_msg_data,
3050 p_cldv_tbl => lp_cldv_tbl,
3051 x_cldv_tbl => x_cldv_tbl);
3052
3053 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3054 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3055 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3056 raise OKC_API.G_EXCEPTION_ERROR;
3057 End If;
3058
3059 IF (lp_cldv_tbl.COUNT > 0) THEN
3060 i := lp_cldv_tbl.FIRST;
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;
3070 END IF;
3071
3072 EXIT WHEN (i = lp_cldv_tbl.LAST);
3073 i := lp_cldv_tbl.NEXT(i);
3074 END LOOP;
3075 END IF;
3076
3077 /*** End API body ******************************************************/
3078
3079 -- Get message count and if count is 1, get message info
3080 FND_MSG_PUB.Count_And_Get
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,
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
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,
3107 p_token2_value => SQLERRM);
3108 FND_MSG_PUB.Count_And_Get
3109 (p_count => x_msg_count,
3110 p_data => x_msg_data);
3111
3112 end update_checklist_dtl;
3113
3114 ----------------------------------------------------------------------------------
3115 -- Start of comments
3116 --
3117 -- Procedure Name : delete_checklist_dtl
3118 -- Description : wrapper api for delete checklist template details
3119 -- Business Rules :
3120 -- Parameters :
3121 -- Version : 1.0
3122 -- End of comments
3123 ----------------------------------------------------------------------------------
3124 PROCEDURE delete_checklist_dtl(
3125 p_api_version IN NUMBER
3126 ,p_init_msg_list IN VARCHAR2
3127 ,x_return_status OUT NOCOPY VARCHAR2
3128 ,x_msg_count OUT NOCOPY NUMBER
3129 ,x_msg_data OUT NOCOPY VARCHAR2
3130 ,p_cldv_tbl IN cldv_tbl_type
3131 )
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
3141 -- Set API savepoint
3142 SAVEPOINT delete_checklist_dtl;
3143
3144 -- Check for call compatibility
3145 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3164 okl_cld_pvt.delete_row(
3165 p_api_version => p_api_version,
3166 p_init_msg_list => p_init_msg_list,
3167 x_return_status => x_return_status,
3168 x_msg_count => x_msg_count,
3169 x_msg_data => x_msg_data,
3170 p_cldv_tbl => lp_cldv_tbl);
3171
3172 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3173 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3174 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3175 raise OKC_API.G_EXCEPTION_ERROR;
3176 End If;
3177
3178 /*** End API body ******************************************************/
3179
3180 -- Get message count and if count is 1, get message info
3181 FND_MSG_PUB.Count_And_Get
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,
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
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,
3208 p_token2_value => SQLERRM);
3209 FND_MSG_PUB.Count_And_Get
3210 (p_count => x_msg_count,
3211 p_data => x_msg_data);
3212
3213 end delete_checklist_dtl;
3214
3215 -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
3216 ----------------------------------------------------------------------------------
3217 -- Start of comments
3218 --
3219 -- Procedure Name : submit_for_approval
3220 -- Description : Submit a checklst for approval
3221 -- Business Rules : 1. System will update status to 'Active' for object itself
3222 -- and all associate checklist if applicable.
3223 -- Parameters :
3224 -- Version : 1.0
3225 -- End of comments
3226 ----------------------------------------------------------------------------------
3227 PROCEDURE submit_for_approval(
3228 p_api_version IN NUMBER
3229 ,p_init_msg_list IN VARCHAR2
3230 ,x_return_status OUT NOCOPY VARCHAR2
3231 ,x_msg_count OUT NOCOPY NUMBER
3232 ,x_msg_data OUT NOCOPY VARCHAR2
3233 ,x_status_code OUT NOCOPY VARCHAR2
3234 ,p_clh_id IN NUMBER
3235 )
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
3245 cursor c_get_status (p_clh_id number) is
3246 select clh.status_code
3247 from okl_checklists clh
3248 where clh.id = p_clh_id;
3249
3250 cursor c_get_children (p_clh_id number) is
3251 select clh.id
3252 from okl_checklists clh
3253 where clh.ckl_id = p_clh_id;
3254
3255 begin
3256 -- Set API savepoint
3257 SAVEPOINT submit_for_approval_pvt;
3258
3259 -- Check for call compatibility
3260 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3279 OPEN c_get_status(p_clh_id);
3280 FETCH c_get_status INTO l_org_status_code;
3281 CLOSE c_get_status;
3282
3283 -- 1. check eligible for approval
3284 chk_eligible_for_approval(
3285 p_api_version => p_api_version,
3286 p_init_msg_list => p_init_msg_list,
3287 x_return_status => x_return_status,
3288 x_msg_count => x_msg_count,
3289 x_msg_data => x_msg_data,
3290 p_clh_id => p_clh_id);
3291
3292 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3293 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3294 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3295 raise OKC_API.G_EXCEPTION_ERROR;
3296 End If;
3297
3298 -- 2. update status to Active
3299 lp_clhv_rec.ID := p_clh_id;
3300 lp_clhv_rec.STATUS_CODE := G_ACTIVE_STS_CODE;
3301 lp_clhv_rec.DECISION_DATE := sysdate;
3302
3303 update_checklist_hdr(
3304 p_api_version => p_api_version,
3305 p_init_msg_list => p_init_msg_list,
3306 x_return_status => x_return_status,
3307 x_msg_count => x_msg_count,
3308 x_msg_data => x_msg_data,
3309 p_clhv_rec => lp_clhv_rec,
3310 x_clhv_rec => lx_clhv_rec);
3311
3312 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3313 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3314 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3315 raise OKC_API.G_EXCEPTION_ERROR;
3316 End If;
3317
3318 -- 3. Cascade to update all children's status if applicable
3319 FOR r_grp IN c_get_children (p_clh_id) LOOP
3320
3321 lp_clhv_rec.ID := r_grp.id;
3322 lp_clhv_rec.STATUS_CODE := G_ACTIVE_STS_CODE;
3323 lp_clhv_rec.DECISION_DATE := sysdate;
3324
3325 update_checklist_hdr(
3326 p_api_version => p_api_version,
3327 p_init_msg_list => p_init_msg_list,
3328 x_return_status => x_return_status,
3329 x_msg_count => x_msg_count,
3330 x_msg_data => x_msg_data,
3331 p_clhv_rec => lp_clhv_rec,
3332 x_clhv_rec => lx_clhv_rec);
3333
3334 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3335 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3336 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3337 raise OKC_API.G_EXCEPTION_ERROR;
3338 End If;
3339
3340 END LOOP;
3341
3342 x_status_code := G_ACTIVE_STS_CODE;
3343
3344 /*** End API body ******************************************************/
3345
3346 -- Get message count and if count is 1, get message info
3347 FND_MSG_PUB.Count_And_Get
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
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
3365 (p_count => x_msg_count,
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,
3375 p_token1_value => SQLCODE,
3376 p_token2 => G_SQLERRM_TOKEN,
3377 p_token2_value => SQLERRM);
3378 FND_MSG_PUB.Count_And_Get
3379 (p_count => x_msg_count,
3380 p_data => x_msg_data);
3381
3382 end submit_for_approval;
3383 ----------------------------------------------------------------------------------
3384 -- Start of comments
3385 --
3386 -- Procedure Name : create_checklist_inst_hdr
3387 -- Description : wrapper api for create checklist instance header
3388 -- Business Rules :
3389 -- 1. CHECKLIST_OBJ_ID and CHECKLIST_OBJ_TYPE_CODE are required
3390 -- 2. CHECKLIST_OBJ_TYPE_CODE is referring from fnd_lookups type
3391 -- = 'CHECKLIST_OBJ_TYPE_CODE'
3392 -- 3. CHECKLIST_TYPE will be defaulting to 'NONE'
3393 -- 4. CHECKLIST_NUMBER will be defaulting to 'CHECKLIST_INSTANCE'
3394 -- appending system generated sequence number
3395 -- 5. CHECKLIST_PURPOSE_CODE will be defaulting to 'CHECKLIST_INSTANCE'
3396 -- Parameters :
3397 -- Version : 1.0
3398 -- End of comments
3399 ----------------------------------------------------------------------------------
3400 PROCEDURE create_checklist_inst_hdr(
3401 p_api_version IN NUMBER
3402 ,p_init_msg_list IN VARCHAR2
3403 ,x_return_status OUT NOCOPY VARCHAR2
3404 ,x_msg_count OUT NOCOPY NUMBER
3405 ,x_msg_data OUT NOCOPY VARCHAR2
3406 ,p_clhv_rec IN clhv_rec_type
3407 ,x_clhv_rec OUT NOCOPY clhv_rec_type
3408 )
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
3418 cursor c_seq is
3419 select okl_inst_checklist_num_s.nextval
3420 from dual;
3421
3422 begin
3423 -- Set API savepoint
3424 SAVEPOINT create_checklist_inst_hdr;
3425
3426 -- Check for call compatibility
3427 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3446 -- set default:
3447 lp_clhv_rec.CHECKLIST_TYPE := 'NONE';
3448 lp_clhv_rec.CHECKLIST_PURPOSE_CODE := 'CHECKLIST_INSTANCE';
3449
3450 OPEN c_seq;
3451 FETCH c_seq INTO l_seq;
3452 CLOSE c_seq;
3453 lp_clhv_rec.CHECKLIST_NUMBER := 'CHECKLIST_INSTANCE' || TO_CHAR(l_seq);
3454
3455 create_checklist_hdr(
3456 p_api_version => p_api_version,
3457 p_init_msg_list => p_init_msg_list,
3458 x_return_status => x_return_status,
3459 x_msg_count => x_msg_count,
3460 x_msg_data => x_msg_data,
3461 p_clhv_rec => lp_clhv_rec,
3462 x_clhv_rec => x_clhv_rec);
3463
3464 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3465 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3466 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3467 raise OKC_API.G_EXCEPTION_ERROR;
3468 End If;
3469
3470 --dbms_output.put_line('create_checklist_inst_hdr->x_clhv_rec.id :'|| to_char(x_clhv_rec.ID));
3471
3472 /*** End API body ******************************************************/
3473
3474 -- Get message count and if count is 1, get message info
3475 FND_MSG_PUB.Count_And_Get
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,
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
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,
3502 p_token2_value => SQLERRM);
3503 FND_MSG_PUB.Count_And_Get
3504 (p_count => x_msg_count,
3505 p_data => x_msg_data);
3506
3507 end create_checklist_inst_hdr;
3508
3509 ----------------------------------------------------------------------------------
3510 -- Start of comments
3511 --
3512 -- Procedure Name : update_checklist_inst_hdr
3513 -- Description : wrapper api for update checklist instance header
3514 -- Business Rules :
3515 -- 1. System allows to update the following columns
3516 -- SHORT_DESCRIPTION, DESCRIPTION, START_DATE, END_DATE, STATUS_CODE
3517 -- DECISION_DATE, CHECKLIST_OBJ_ID, CHECKLIST_OBJ_TYPE_CODE
3518 --
3519 -- Parameters :
3520 -- Version : 1.0
3521 -- End of comments
3522 ----------------------------------------------------------------------------------
3523 PROCEDURE update_checklist_inst_hdr(
3524 p_api_version IN NUMBER
3525 ,p_init_msg_list IN VARCHAR2
3526 ,x_return_status OUT NOCOPY VARCHAR2
3527 ,x_msg_count OUT NOCOPY NUMBER
3528 ,x_msg_data OUT NOCOPY VARCHAR2
3529 ,p_clhv_rec IN clhv_rec_type
3530 ,x_clhv_rec OUT NOCOPY clhv_rec_type
3531 )
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
3541 -- Set API savepoint
3542 SAVEPOINT update_checklist_inst_hdr;
3543
3544 -- Check for call compatibility
3545 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3564 update_checklist_hdr(
3565 p_api_version => p_api_version,
3566 p_init_msg_list => p_init_msg_list,
3567 x_return_status => x_return_status,
3568 x_msg_count => x_msg_count,
3569 x_msg_data => x_msg_data,
3570 p_clhv_rec => lp_clhv_rec,
3571 x_clhv_rec => x_clhv_rec);
3572
3573 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3574 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3575 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3576 raise OKC_API.G_EXCEPTION_ERROR;
3577 End If;
3578
3579 /*** End API body ******************************************************/
3580
3581 -- Get message count and if count is 1, get message info
3582 FND_MSG_PUB.Count_And_Get
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,
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
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,
3609 p_token2_value => SQLERRM);
3610 FND_MSG_PUB.Count_And_Get
3611 (p_count => x_msg_count,
3612 p_data => x_msg_data);
3613
3614 end update_checklist_inst_hdr;
3615
3616 ----------------------------------------------------------------------------------
3617 -- Start of comments
3618 --
3619 -- Procedure Name : delete_checklist_inst_hdr
3620 -- Description : wrapper api for delete checklist instance header
3621 -- Business Rules :
3622 -- Parameters :
3623 -- Version : 1.0
3624 -- End of comments
3625 ----------------------------------------------------------------------------------
3626 PROCEDURE delete_checklist_inst_hdr(
3627 p_api_version IN NUMBER
3628 ,p_init_msg_list IN VARCHAR2
3629 ,x_return_status OUT NOCOPY VARCHAR2
3630 ,x_msg_count OUT NOCOPY NUMBER
3631 ,x_msg_data OUT NOCOPY VARCHAR2
3632 ,p_clhv_rec IN clhv_rec_type
3633 )
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
3643 -- Set API savepoint
3644 SAVEPOINT delete_checklist_inst_hdr;
3645
3646 -- Check for call compatibility
3647 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3666 delete_checklist_hdr(
3667 p_api_version => p_api_version,
3668 p_init_msg_list => p_init_msg_list,
3669 x_return_status => x_return_status,
3670 x_msg_count => x_msg_count,
3671 x_msg_data => x_msg_data,
3672 p_clhv_rec => lp_clhv_rec);
3673
3674 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3675 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3676 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3677 raise OKC_API.G_EXCEPTION_ERROR;
3678 End If;
3679
3680 /*** End API body ******************************************************/
3681
3682 -- Get message count and if count is 1, get message info
3683 FND_MSG_PUB.Count_And_Get
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,
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
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,
3710 p_token2_value => SQLERRM);
3711 FND_MSG_PUB.Count_And_Get
3712 (p_count => x_msg_count,
3713 p_data => x_msg_data);
3714
3715 end delete_checklist_inst_hdr;
3716
3717 ----------------------------------------------------------------------------------
3718 -- Start of comments
3719 --
3720 -- Procedure Name : create_checklist_inst_dtl
3721 -- Description : wrapper api for create checklist instance details
3722 -- Business Rules :
3723 -- 1. CKL_ID, TODO_ITEM_CODE, and INST_CHECKLIST_TYPE are required
3724 -- 2. CKL_ID is referring from okl_checklists.ID as FK
3725 -- 3. TODO_ITEM_CODE is referring from fnd_lookups type
3726 -- = 'OKL_TODO_ITEMS'
3727 -- 4. INST_CHECKLIST_TYPE is referring from fnd_lookups type
3728 -- = 'OKL_CHECKLIST_TYPE'
3729 -- 5. The following columns are referring from fnd_lookups type
3730 -- = 'OKL_YES_NO'
3731 -- MANDATORY_FLAG
3732 -- USER_COMPLETE_FLAG
3733 -- APPEAL_FLAG
3734 -- 6. FUNCTION_VALIDATE_RSTS is referring from fnd_lookups type
3735 -- = 'OKL_FUN_VALIDATE_RSTS'
3736 -- 7. System will defaulting DNZ_CHECKLIST_OBJ_ID from the
3737 -- corresponding okl_chekclists.CHECKLIST_OBJ_ID
3738 -- 8. FUNCTION_ID is referring from OKL_DATA_SRC_FNCTNS_V
3739 -- 9. MANDATORY_FLAG, USER_COMPLETE_FLAG and APPEAL_FLAG will defult to 'N'
3740 -- Parameters :
3741 -- Version : 1.0
3742 -- End of comments
3743 ----------------------------------------------------------------------------------
3744 PROCEDURE create_checklist_inst_dtl(
3745 p_api_version IN NUMBER
3746 ,p_init_msg_list IN VARCHAR2
3747 ,x_return_status OUT NOCOPY VARCHAR2
3748 ,x_msg_count OUT NOCOPY NUMBER
3749 ,x_msg_data OUT NOCOPY VARCHAR2
3750 ,p_cldv_tbl IN cldv_tbl_type
3751 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
3752 )
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
3762 cursor c_obj_id (p_clh_id number) is
3763 select clh.CHECKLIST_OBJ_ID
3764 from okl_checklists clh
3765 where clh.id = p_clh_id;
3766
3767 begin
3768 -- Set API savepoint
3769 SAVEPOINT create_checklist_inst_dtl;
3770
3771 -- Check for call compatibility
3772 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
3791 -- set default:
3792 OPEN c_obj_id (lp_cldv_tbl(lp_cldv_tbl.FIRST).ckl_id);
3793 FETCH c_obj_id INTO l_obj_id;
3794 CLOSE c_obj_id;
3795
3796 IF (lp_cldv_tbl.COUNT > 0) THEN
3797 i := lp_cldv_tbl.FIRST;
3798 LOOP
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 |
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
3815 -- set default for FUNCTION_VALIDATE_RSTS
3816 IF lp_cldv_tbl(i).FUNCTION_ID IS NOT NULL THEN
3817 lp_cldv_tbl(i).FUNCTION_VALIDATE_RSTS := 'UNDETERMINED';
3818 END IF;
3819
3820 -- set default for USER_COMPLETE_FLAG
3821 IF lp_cldv_tbl(i).FUNCTION_ID IS NULL THEN
3822 lp_cldv_tbl(i).USER_COMPLETE_FLAG := 'N';
3823 END IF;
3824
3825 EXIT WHEN (i = lp_cldv_tbl.LAST);
3826 i := lp_cldv_tbl.NEXT(i);
3827 END LOOP;
3828 END IF;
3829
3830 create_checklist_dtl(
3831 p_api_version => p_api_version,
3832 p_init_msg_list => p_init_msg_list,
3833 x_return_status => x_return_status,
3834 x_msg_count => x_msg_count,
3835 x_msg_data => x_msg_data,
3836 p_cldv_tbl => lp_cldv_tbl,
3837 x_cldv_tbl => x_cldv_tbl);
3838
3839 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3840 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3841 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3842 raise OKC_API.G_EXCEPTION_ERROR;
3843 End If;
3844
3845 /*** End API body ******************************************************/
3846
3847 -- Get message count and if count is 1, get message info
3848 FND_MSG_PUB.Count_And_Get
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,
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
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,
3875 p_token2_value => SQLERRM);
3876 FND_MSG_PUB.Count_And_Get
3877 (p_count => x_msg_count,
3878 p_data => x_msg_data);
3879
3880 end create_checklist_inst_dtl;
3881
3882 ----------------------------------------------------------------------------------
3883 -- Start of comments
3884 --
3885 -- Procedure Name : update_checklist_inst_hdr
3886 -- Description : wrapper api for update checklist instance details
3887 -- Business Rules :
3888 -- 1. System allows to update the following columns
3889 -- TODO_ITEM_CODE, MANDATORY_FLAG, USER_COMPLETE_FLAG
3890 -- ADMIN_NOTE, USER_NOTE, FUNCTION_ID, FUNCTION_VALIDATE_RSTS
3891 -- FUNCTION_VALIDATE_MSG, INST_CHECKLIST_TYPE and APPEAL_FLAG
3892 --
3893 -- Parameters :
3894 -- Version : 1.0
3895 -- End of comments
3896 ----------------------------------------------------------------------------------
3897 PROCEDURE update_checklist_inst_dtl(
3898 p_api_version IN NUMBER
3899 ,p_init_msg_list IN VARCHAR2
3900 ,x_return_status OUT NOCOPY VARCHAR2
3901 ,x_msg_count OUT NOCOPY NUMBER
3902 ,x_msg_data OUT NOCOPY VARCHAR2
3903 ,p_cldv_tbl IN cldv_tbl_type
3904 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
3905 )
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
3915 -- Set API savepoint
3916 SAVEPOINT update_checklist_inst_dtl;
3917
3918 -- Check for call compatibility
3919 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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(
3938 p_api_version => p_api_version,
3939 p_init_msg_list => p_init_msg_list,
3940 x_return_status => x_return_status,
3941 x_msg_count => x_msg_count,
3942 x_msg_data => x_msg_data,
3943 p_cldv_tbl => lp_cldv_tbl,
3944 x_cldv_tbl => x_cldv_tbl);
3945
3946 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3947 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3948 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3949 raise OKC_API.G_EXCEPTION_ERROR;
3950 End If;
3951
3952 /*** End API body ******************************************************/
3953
3954 -- Get message count and if count is 1, get message info
3955 FND_MSG_PUB.Count_And_Get
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,
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
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,
3982 p_token2_value => SQLERRM);
3983 FND_MSG_PUB.Count_And_Get
3984 (p_count => x_msg_count,
3985 p_data => x_msg_data);
3986
3987 end update_checklist_inst_dtl;
3988
3989
3990 ----------------------------------------------------------------------------------
3991 -- Start of comments
3992 --
3993 -- Procedure Name : delete_checklist_inst_dtl
3994 -- Description : wrapper api for delete checklist instance details
3995 -- Business Rules :
3996 -- Parameters :
3997 -- Version : 1.0
3998 -- End of comments
3999 ----------------------------------------------------------------------------------
4000 PROCEDURE delete_checklist_inst_dtl(
4001 p_api_version IN NUMBER
4002 ,p_init_msg_list IN VARCHAR2
4003 ,x_return_status OUT NOCOPY VARCHAR2
4004 ,x_msg_count OUT NOCOPY NUMBER
4005 ,x_msg_data OUT NOCOPY VARCHAR2
4006 ,p_cldv_tbl IN cldv_tbl_type
4007 )
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
4017 -- Set API savepoint
4018 SAVEPOINT delete_checklist_inst_dtl;
4019
4020 -- Check for call compatibility
4021 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
4040 delete_checklist_dtl(
4041 p_api_version => p_api_version,
4042 p_init_msg_list => p_init_msg_list,
4043 x_return_status => x_return_status,
4044 x_msg_count => x_msg_count,
4045 x_msg_data => x_msg_data,
4046 p_cldv_tbl => lp_cldv_tbl);
4047
4048 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4049 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4050 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4051 raise OKC_API.G_EXCEPTION_ERROR;
4052 End If;
4053
4054 /*** End API body ******************************************************/
4055
4056 -- Get message count and if count is 1, get message info
4057 FND_MSG_PUB.Count_And_Get
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,
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
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,
4084 p_token2_value => SQLERRM);
4085 FND_MSG_PUB.Count_And_Get
4086 (p_count => x_msg_count,
4087 p_data => x_msg_data);
4088
4089 end delete_checklist_inst_dtl;
4090
4091 ----------------------------------------------------------------------------------
4092 -- Start of comments
4093 --
4094 -- Procedure Name : chk_eligible_for_approval
4095 -- Description : Check if it's eligible for approval
4096 -- Business Rules :
4097 -- The following scenarios are not eligible for approval
4098 -- 1 Checklist template (either group or individual) status is Active.
4099 -- 2 Group checklist template doesn't have child checklist assocaite with it.
4100 -- 3 Group checklist template does have child checklist associate with it,
4101 -- but child checklist doesn't have items defined.
4102 -- 4 Checklist template does have group checklist assocaite with it (Has parent checklist).
4103 -- 5 Checklist template doesn't have items defined.
4104 -- Parameters :
4105 -- Version : 1.0
4106 -- End of comments
4107 ----------------------------------------------------------------------------------
4108 PROCEDURE chk_eligible_for_approval(
4109 p_api_version IN NUMBER
4110 ,p_init_msg_list IN VARCHAR2
4111 ,x_return_status OUT NOCOPY VARCHAR2
4112 ,x_msg_count OUT NOCOPY NUMBER
4113 ,x_msg_data OUT NOCOPY VARCHAR2
4114 ,p_clh_id IN NUMBER
4115 )
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
4125 --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
4126 l_clhv_rec clhv_rec_type;
4127 --END:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
4128
4129 -- get checklist template attributes
4130 cursor c_checklists (p_clh_id number) is
4131 select clh.status_code,
4132 clh.checklist_purpose_code,
4133 clh.ckl_id
4134 from okl_checklists clh
4135 where clh.id = p_clh_id
4136 ;
4137
4138 -- existing checklists for a group checklist
4139 cursor c_checklist_grp (p_clh_id number) is
4140 select 1
4141 from okl_checklists clh
4142 where clh.ckl_id = p_clh_id
4143 ;
4144
4145 -- existing checklist items for a group checklist
4146 cursor c_clist_ids_by_grp (p_clh_id number) is
4147 select clh.id
4148 from okl_checklists clh
4149 where clh.ckl_id = p_clh_id
4150 ;
4151
4152 cursor c_checklist_grp_items (p_clh_id number) is
4153 select 1
4154 from okl_checklist_details cld
4155 where cld.ckl_id = p_clh_id
4156 ;
4157
4158
4159 -- existing checklist items for a checcklist
4160 cursor c_checklist_items (p_clh_id number) is
4161 select 1
4162 from okl_checklist_details cld
4163 where cld.ckl_id = p_clh_id
4164 ;
4165
4166
4167 begin
4168 -- Set API savepoint
4169 SAVEPOINT chk_eligible_for_approval;
4170
4171 -- Check for call compatibility
4172 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
4191 -- 1 Checklist template (either group or individual) status is Active.
4192 -- 2 Group checklist template doesn't have child checklist assocaite with it.
4193 -- 3 Group checklist template does have child checklist associate with it,
4194 -- but child checklist doesn't have items defined.
4195 -- 4 Checklist template does have group checklist assocaite with it (Has parent checklist).
4196 -- 5 Checklist template doesn't have items defined.
4197 -- 6. Checklist item effetcive from date must with the checklist header's date range
4198
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;
4208
4209 -------------------------------------------------------------
4210 -- Group checklist
4211 -------------------------------------------------------------
4212 IF r_this_row.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP' THEN
4213
4214 -- 2 Group checklist template doesn't have child checklist assocaite with it.
4215 OPEN c_checklist_grp(p_clh_id);
4216 FETCH c_checklist_grp INTO l_dummy;
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
4226 ELSE
4227 -- 3 Group checklist template does have child checklist associate with it,
4228 -- but not all child checklists have items defined.
4229 FOR r_clist_row IN c_clist_ids_by_grp(p_clh_id) LOOP
4230
4231 OPEN c_checklist_grp_items(r_clist_row.id);
4232 FETCH c_checklist_grp_items INTO l_dummy;
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;
4242
4243 END LOOP;
4244
4245 END IF;
4246
4247 -------------------------------------------------------------
4248 -- Checklist
4249 -------------------------------------------------------------
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;
4259
4260 -- 5 Checklist template doesn't have items defined.
4261 OPEN c_checklist_items(p_clh_id);
4262 FETCH c_checklist_items INTO l_dummy;
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;
4272
4273 END IF;
4274
4275 END LOOP;
4276
4277 --START:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
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;
4287 END IF;
4288 --END:| 23-Feb-2006 cklee -- Fixed bug#5018561 |
4289
4290 /*** End API body ******************************************************/
4291
4292 -- Get message count and if count is 1, get message info
4293 FND_MSG_PUB.Count_And_Get
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,
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
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 |
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,
4329 p_token2_value => SQLERRM);
4330 FND_MSG_PUB.Count_And_Get
4331 (p_count => x_msg_count,
4332 p_data => x_msg_data);
4333
4334 end chk_eligible_for_approval;
4335
4336 ----------------------------------------------------------------------------------
4337 -- Start of comments
4338 --
4339 -- Procedure Name : update_checklist_function
4340 -- Description : This API will execute function for each item and
4341 -- update the execution results for the function.
4342 -- Business Rules :
4343 -- Parameters :
4344 -- Version : 1.0
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
4363 lp_cldv_rec cldv_rec_type;
4364 lx_cldv_rec cldv_rec_type;
4365 plsql_block VARCHAR2(500);
4366
4367 lp_return_status okl_checklist_details.FUNCTION_VALIDATE_RSTS%type;
4368 lp_fund_rst okl_checklist_details.FUNCTION_VALIDATE_RSTS%type;
4369 lp_msg_data okl_checklist_details.FUNCTION_VALIDATE_MSG%type;
4370
4371 -- get checklist template attributes
4372 cursor c_clist_funs (p_checklist_obj_id number) is
4373 select cld.FUNCTION_SOURCE,
4374 cld.ID
4375 from okl_checklist_details_uv cld
4376 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
4377 ,okl_checklists hdr
4378 -- where cld.DNZ_CHECKLIST_OBJ_ID = p_checklist_obj_id
4379 where cld.ckl_id = hdr.id
4380 and hdr.CHECKLIST_OBJ_ID = p_checklist_obj_id
4381 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
4382 and cld.FUNCTION_ID IS NOT NULL
4383 ;
4384
4385 begin
4386 -- Set API savepoint
4387 SAVEPOINT update_checklist_function;
4388
4389 -- Check for call compatibility
4390 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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
4409 ------------------------------------------------------------------------
4410 -- execute function for each to do item and save the return to each row
4411 ------------------------------------------------------------------------
4412 FOR r_this_row IN c_clist_funs (p_checklist_obj_id) LOOP
4413
4414 BEGIN
4415
4416 plsql_block := 'BEGIN :l_rtn := '|| r_this_row.FUNCTION_SOURCE ||'(:p_obj_id); END;';
4417 EXECUTE IMMEDIATE plsql_block USING OUT lp_return_status, p_checklist_obj_id;
4418
4419 IF lp_return_status = 'P' THEN
4420 lp_fund_rst := 'PASSED';
4421 lp_msg_data := 'Passed';
4422 ELSIF lp_return_status = 'F' THEN
4423 lp_fund_rst := 'FAILED';
4424 lp_msg_data := 'Failed';
4425 ELSE
4426 lp_fund_rst := 'ERROR';
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);
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);
4443 lp_msg_data := substr('Unexpected application error: ' || x_msg_data, 2000);
4444
4445 WHEN OTHERS THEN
4446 lp_fund_rst := 'ERROR';
4447 lp_msg_data := substr('Unexpected system error: ' || SQLERRM, 2000);
4448
4449 END;
4450
4451 lp_cldv_rec.ID := r_this_row.ID;
4452 lp_cldv_rec.FUNCTION_VALIDATE_RSTS := lp_fund_rst;
4453 lp_cldv_rec.FUNCTION_VALIDATE_MSG := lp_msg_data;
4454
4455 okl_cld_pvt.update_row(
4456 p_api_version => p_api_version,
4457 p_init_msg_list => p_init_msg_list,
4458 x_return_status => x_return_status,
4459 x_msg_count => x_msg_count,
4460 x_msg_data => x_msg_data,
4461 p_cldv_rec => lp_cldv_rec,
4462 x_cldv_rec => lx_cldv_rec);
4463
4464 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4465 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4466 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4467 raise OKC_API.G_EXCEPTION_ERROR;
4468 End If;
4469
4470 END LOOP;
4471
4472 /*** End API body ******************************************************/
4473
4474 -- Get message count and if count is 1, get message info
4475 FND_MSG_PUB.Count_And_Get
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,
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
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,
4503 p_token2_value => SQLERRM);
4504 FND_MSG_PUB.Count_And_Get
4505 (p_count => x_msg_count,
4506 p_data => x_msg_data);
4507
4508 end update_checklist_function;
4509 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
4510
4511 -- START: June 06, 2005 cklee: Modification for okl.h lease app enhancement
4512 ----------------------------------------------------------------------------------
4513 -- Start of comments
4514 --
4515 -- Procedure Name : create_contract_checklist
4516 -- Description : Wrapper API for creates a checklist instance header and detail,
4517 -- for which the checklists copy the corresponding lease application.
4518 -- Business Rules :
4519 -- 1. Create an instance of the checklist header for the contract.
4520 -- 2. Create the detail list items for the checklist header,
4521 -- for which the checklist copy corresponding lease application.
4522 -- Parameters :
4523 -- Version : 1.0
4524 -- End of comments
4525 ----------------------------------------------------------------------------------
4526 PROCEDURE create_contract_checklist(
4527 p_api_version IN NUMBER
4528 ,p_init_msg_list IN VARCHAR2
4529 ,x_return_status OUT NOCOPY VARCHAR2
4530 ,x_msg_count OUT NOCOPY NUMBER
4531 ,x_msg_data OUT NOCOPY VARCHAR2
4532 ,p_chr_id IN NUMBER
4533 )
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;
4543
4544 -- start: 06-June-2005 cklee okl.h Lease App IA Authoring
4545 l_dummy number;
4546 l_lease_app_id number;
4547 l_lease_app_found boolean;
4548 l_lease_app_list_found boolean;
4549
4550 ---------------------------------------------------------------------------------------------------------
4551 -- check if the contract was created from a lease application
4552 ---------------------------------------------------------------------------------------------------------
4553 CURSOR c_lease_app (p_chr_id okc_k_headers_b.id%type)
4554 IS
4555 select chr.ORIG_SYSTEM_ID1
4556 from okc_k_headers_b chr
4557 where ORIG_SYSTEM_SOURCE_CODE = G_OKL_LEASE_APP
4558 and chr.id = p_chr_id
4559 ;
4560
4561 ---------------------------------------------------------------------------------------------------
4562 -- Activation checklist refer from a Lease application
4563 ---------------------------------------------------------------------------------------------------
4564 cursor c_chk_lease_app (p_lease_app_id number) is
4565 select
4566 chk.TODO_ITEM_CODE,
4567 NVL(chk.MANDATORY_FLAG, 'N') MANDATORY_FLAG,
4568 chk.USER_NOTE,
4569 chk.FUNCTION_ID,
4570 chk.INST_CHECKLIST_TYPE
4571 from OKL_CHECKLIST_DETAILS chk
4572 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
4573 ,okl_checklists hdr
4574 where chk.ckl_id = hdr.id
4575 --where chk.DNZ_CHECKLIST_OBJ_ID = p_lease_app_id
4576 and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
4577 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
4578 and chk.INST_CHECKLIST_TYPE = 'ACTIVATION'
4579 ;
4580
4581 cursor c_lease_app_list_exists (p_lease_app_id number) is
4582 select 1
4583 from OKL_CHECKLIST_DETAILS chk
4584 --START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
4585 ,okl_checklists hdr
4586 where chk.ckl_id = hdr.id
4587 --where chk.DNZ_CHECKLIST_OBJ_ID = p_lease_app_id
4588 and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
4589 --END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
4590 and chk.INST_CHECKLIST_TYPE = 'ACTIVATION'
4591 ;
4592 -- end: 06-June-2005 cklee okl.h Lease App IA Authoring
4593
4594 begin
4595 -- Set API savepoint
4596 SAVEPOINT create_contract_checklist;
4597
4598 -- Check for call compatibility
4599 IF (NOT FND_API.Compatible_API_Call (l_api_version,
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 --------------------------------------------------------------------
4618 -- Check to see if the contract was copy from a lease app
4619 --------------------------------------------------------------------
4620 OPEN c_lease_app(p_chr_id);
4621 FETCH c_lease_app INTO l_lease_app_id;
4622 l_lease_app_found := c_lease_app%FOUND;
4623 CLOSE c_lease_app;
4624
4625 IF l_lease_app_id IS NOT NULL THEN
4626 --------------------------------------------------------------------
4627 -- Check to see if the lease app has checklist?
4628 --------------------------------------------------------------------
4629 OPEN c_lease_app_list_exists(l_lease_app_id);
4630 FETCH c_lease_app_list_exists INTO l_dummy;
4631 l_lease_app_list_found := c_lease_app_list_exists%FOUND;
4632 CLOSE c_lease_app_list_exists;
4633
4634 END IF;
4635
4636 IF l_lease_app_found AND l_lease_app_list_found THEN
4637 --------------------------------------------------------------------
4638 -- Create Checkist header
4639 --------------------------------------------------------------------
4640 lp_clhv_rec.CHECKLIST_OBJ_ID := p_chr_id;
4641 lp_clhv_rec.CHECKLIST_OBJ_TYPE_CODE := G_CONTRACT;
4642 -- set to Active directly if the object was copy from a Lease Application
4643 lp_clhv_rec.END_DATE := sysdate + 36500; -- set a big end date
4644 lp_clhv_rec.STATUS_CODE := G_ACTIVE;
4645
4646 create_checklist_inst_hdr(
4647 p_api_version => p_api_version,
4648 p_init_msg_list => p_init_msg_list,
4649 x_return_status => x_return_status,
4650 x_msg_count => x_msg_count,
4651 x_msg_data => x_msg_data,
4652 p_clhv_rec => lp_clhv_rec,
4653 x_clhv_rec => lx_clhv_rec);
4654
4655 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4656 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4657 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4658 raise OKC_API.G_EXCEPTION_ERROR;
4659 End If;
4660
4661 --------------------------------------------------------------------
4662 -- Create Checkist details
4663 --------------------------------------------------------------------
4664 i := 0;
4665 FOR r_this_row IN c_chk_lease_app(l_lease_app_id) LOOP
4666
4667 lp_cldv_tbl(i).CKL_ID := lx_clhv_rec.ID;
4668 lp_cldv_tbl(i).TODO_ITEM_CODE := r_this_row.todo_item_code;
4669 lp_cldv_tbl(i).MANDATORY_FLAG := r_this_row.mandatory_flag;
4670 -- lp_clhv_tbl(i).ADMIN_NOTE := r_this_row.todo_item_code;
4671 lp_cldv_tbl(i).USER_NOTE := r_this_row.user_note;
4672 -- lp_clhv_tbl(i).DNZ_CHECKLIST_OBJ_ID := p_chr_id;
4673 lp_cldv_tbl(i).FUNCTION_ID := r_this_row.function_id;
4674 lp_cldv_tbl(i).INST_CHECKLIST_TYPE := r_this_row.inst_checklist_type;
4675 i := i + 1;
4676 END LOOP;
4677
4678 create_checklist_inst_dtl(
4679 p_api_version => p_api_version,
4680 p_init_msg_list => p_init_msg_list,
4681 x_return_status => x_return_status,
4682 x_msg_count => x_msg_count,
4683 x_msg_data => x_msg_data,
4684 p_cldv_tbl => lp_cldv_tbl,
4685 x_cldv_tbl => lx_cldv_tbl);
4686
4687 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4688 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4689 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4690 raise OKC_API.G_EXCEPTION_ERROR;
4691 End If;
4692
4693 END IF;
4694
4695 /*** End API body ******************************************************/
4696
4697 -- Get message count and if count is 1, get message info
4698 FND_MSG_PUB.Count_And_Get
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,
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
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,
4725 p_token2_value => SQLERRM);
4726 FND_MSG_PUB.Count_And_Get
4727 (p_count => x_msg_count,
4728 p_data => x_msg_data);
4729
4730 end create_contract_checklist;
4731 -- END: June 06, 2005 cklee: Modification for okl.h lease app enhancement
4732
4733 ------------------------------------------------------------------------------
4734 -- PROCEDURE upd_chklst_dtl_apl_flag
4735 ------------------------------------------------------------------------------
4736 -- Start of comments
4737 --
4738 -- Procedure Name : upd_chklst_dtl_apl_flag
4739 -- Description : This procedure updates the appeal flag for the given
4740 -- table of checklist detail items
4741 -- Business Rules : This procedure updates the appeal flag for the given
4742 -- table of checklist detail itema
4743 -- Parameters :
4744 -- Version : 1.0
4745 -- History : 03-Apr-2006 PAGARG created Bug 4872271
4746 --
4747 -- End of comments
4748 PROCEDURE upd_chklst_dtl_apl_flag(
4749 p_api_version IN NUMBER,
4750 p_init_msg_list IN VARCHAR2,
4751 p_cldv_tbl IN CLDV_TBL_TYPE,
4752 x_cldv_tbl OUT NOCOPY CLDV_TBL_TYPE,
4753 x_return_status OUT NOCOPY VARCHAR2,
4754 x_msg_count OUT NOCOPY NUMBER,
4755 x_msg_data OUT NOCOPY VARCHAR2)
4756 IS
4757 -- Variables Declarations
4758 l_api_version CONSTANT NUMBER DEFAULT 1.0;
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');
4768 IS_DEBUG_PROCEDURE_ON := NVL(OKL_DEBUG_PUB.CHECK_LOG_ON(L_MODULE, FND_LOG.LEVEL_PROCEDURE), FALSE);
4769 -- check for logging on STATEMENT level
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
4779 ,p_api_version => p_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;
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;
4801 EXIT WHEN (i = p_cldv_tbl.LAST);
4802 i := p_cldv_tbl.NEXT(i);
4803 END LOOP;
4804 END IF;
4805
4806 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
4807 THEN
4808 OKL_DEBUG_PUB.LOG_DEBUG(
4809 FND_LOG.LEVEL_PROCEDURE
4810 ,L_MODULE
4811 ,'begin debug call OKL_CLD_PVT.UPDATE_ROW');
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
4821 ,x_cldv_tbl => x_cldv_tbl);
4822
4823 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_PROCEDURE_ON)
4824 THEN
4825 OKL_DEBUG_PUB.LOG_DEBUG(
4826 FND_LOG.LEVEL_PROCEDURE
4827 ,L_MODULE
4828 ,'end debug call OKL_CLD_PVT.UPDATE_ROW');
4829 END IF;
4830
4831 -- write to log
4832 IF(L_DEBUG_ENABLED = 'Y' AND IS_DEBUG_STATEMENT_ON) THEN
4833 OKL_DEBUG_PUB.LOG_DEBUG(
4834 FND_LOG.LEVEL_STATEMENT
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;
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
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
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
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,
4873 x_msg_data => x_msg_data,
4874 p_api_type => G_API_TYPE);
4875 END upd_chklst_dtl_apl_flag;
4876
4877 END OKL_CHECKLIST_PVT;