[Home] [Help]
PACKAGE BODY: APPS.OKL_CLD_PVT
Source
1 PACKAGE BODY OKL_CLD_PVT AS
2 /* $Header: OKLSCLDB.pls 120.6 2006/07/07 10:45:17 adagur noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_CHECKLIST_DETAILS_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_cldv_rec IN cldv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN cldv_rec_type IS
100 CURSOR okl_cldv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 CKL_ID,
105 TODO_ITEM_CODE,
106 ATTRIBUTE_CATEGORY,
107 ATTRIBUTE1,
108 ATTRIBUTE2,
109 ATTRIBUTE3,
110 ATTRIBUTE4,
111 ATTRIBUTE5,
112 ATTRIBUTE6,
113 ATTRIBUTE7,
114 ATTRIBUTE8,
115 ATTRIBUTE9,
116 ATTRIBUTE10,
117 ATTRIBUTE11,
118 ATTRIBUTE12,
119 ATTRIBUTE13,
120 ATTRIBUTE14,
121 ATTRIBUTE15,
122 ORG_ID,
123 REQUEST_ID,
124 PROGRAM_APPLICATION_ID,
125 PROGRAM_ID,
126 PROGRAM_UPDATE_DATE,
127 CREATED_BY,
128 CREATION_DATE,
129 LAST_UPDATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATE_LOGIN,
132 -- start: Apr 25, 2005 cklee: Modification for okl.h
133 MANDATORY_FLAG,
134 USER_COMPLETE_FLAG,
135 ADMIN_NOTE,
136 USER_NOTE,
137 DNZ_CHECKLIST_OBJ_ID,
138 FUNCTION_ID,
139 FUNCTION_VALIDATE_RSTS,
140 FUNCTION_VALIDATE_MSG,
141 INST_CHECKLIST_TYPE
142 -- end: Apr 25, 2005 cklee: Modification for okl.h
143 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
144 ,APPEAL_FLAG
145 FROM OKL_CHECKLIST_DETAILS
146 WHERE OKL_CHECKLIST_DETAILS.id = p_id;
147 l_okl_cldv_pk okl_cldv_pk_csr%ROWTYPE;
148 l_cldv_rec cldv_rec_type;
149 BEGIN
150 x_no_data_found := TRUE;
151 -- Get current database values
152 OPEN okl_cldv_pk_csr (p_cldv_rec.id);
153 FETCH okl_cldv_pk_csr INTO
154 l_cldv_rec.id,
155 l_cldv_rec.object_version_number,
156 l_cldv_rec.ckl_id,
157 l_cldv_rec.todo_item_code,
158 l_cldv_rec.attribute_category,
159 l_cldv_rec.attribute1,
160 l_cldv_rec.attribute2,
161 l_cldv_rec.attribute3,
162 l_cldv_rec.attribute4,
163 l_cldv_rec.attribute5,
164 l_cldv_rec.attribute6,
165 l_cldv_rec.attribute7,
166 l_cldv_rec.attribute8,
167 l_cldv_rec.attribute9,
168 l_cldv_rec.attribute10,
169 l_cldv_rec.attribute11,
170 l_cldv_rec.attribute12,
171 l_cldv_rec.attribute13,
172 l_cldv_rec.attribute14,
173 l_cldv_rec.attribute15,
174 l_cldv_rec.org_id,
175 l_cldv_rec.request_id,
176 l_cldv_rec.program_application_id,
177 l_cldv_rec.program_id,
178 l_cldv_rec.program_update_date,
179 l_cldv_rec.created_by,
180 l_cldv_rec.creation_date,
181 l_cldv_rec.last_updated_by,
182 l_cldv_rec.last_update_date,
183 l_cldv_rec.last_update_login,
184 -- start: Apr 25, 2005 cklee: Modification for okl.h
185 l_cldv_rec.MANDATORY_FLAG,
186 l_cldv_rec.USER_COMPLETE_FLAG,
187 l_cldv_rec.ADMIN_NOTE,
188 l_cldv_rec.USER_NOTE,
189 l_cldv_rec.DNZ_CHECKLIST_OBJ_ID,
190 l_cldv_rec.FUNCTION_ID,
191 l_cldv_rec.FUNCTION_VALIDATE_RSTS,
192 l_cldv_rec.FUNCTION_VALIDATE_MSG,
193 l_cldv_rec.INST_CHECKLIST_TYPE
194 -- end: Apr 25, 2005 cklee: Modification for okl.h
195 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
196 ,l_cldv_rec.APPEAL_FLAG
197 ;
198 x_no_data_found := okl_cldv_pk_csr%NOTFOUND;
199 CLOSE okl_cldv_pk_csr;
200 RETURN(l_cldv_rec);
201 END get_rec;
202
203 ------------------------------------------------------------------
204 -- This version of get_rec sets error messages if no data found --
205 ------------------------------------------------------------------
206 FUNCTION get_rec (
207 p_cldv_rec IN cldv_rec_type,
208 x_return_status OUT NOCOPY VARCHAR2
209 ) RETURN cldv_rec_type IS
210 l_cldv_rec cldv_rec_type;
211 l_row_notfound BOOLEAN := TRUE;
212 BEGIN
213 x_return_status := OKL_API.G_RET_STS_SUCCESS;
214 l_cldv_rec := get_rec(p_cldv_rec, l_row_notfound);
215 IF (l_row_notfound) THEN
216 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
217 x_return_status := OKL_API.G_RET_STS_ERROR;
218 END IF;
219 RETURN(l_cldv_rec);
220 END get_rec;
221 -----------------------------------------------------------
222 -- So we don't have to pass an "l_row_notfound" variable --
223 -----------------------------------------------------------
224 FUNCTION get_rec (
225 p_cldv_rec IN cldv_rec_type
226 ) RETURN cldv_rec_type IS
227 l_row_not_found BOOLEAN := TRUE;
228 BEGIN
229 RETURN(get_rec(p_cldv_rec, l_row_not_found));
230 END get_rec;
231 ---------------------------------------------------------------------------
232 -- FUNCTION get_rec for: OKL_CHECKLIST_DETAILS
233 ---------------------------------------------------------------------------
234 FUNCTION get_rec (
235 p_cld_rec IN cld_rec_type,
236 x_no_data_found OUT NOCOPY BOOLEAN
237 ) RETURN cld_rec_type IS
238 CURSOR okl_checklist_details_pk_csr (p_id IN NUMBER) IS
239 SELECT
240 ID,
241 OBJECT_VERSION_NUMBER,
242 CKL_ID,
243 TODO_ITEM_CODE,
244 ATTRIBUTE_CATEGORY,
245 ATTRIBUTE1,
246 ATTRIBUTE2,
247 ATTRIBUTE3,
248 ATTRIBUTE4,
249 ATTRIBUTE5,
250 ATTRIBUTE6,
251 ATTRIBUTE7,
252 ATTRIBUTE8,
253 ATTRIBUTE9,
254 ATTRIBUTE10,
255 ATTRIBUTE11,
256 ATTRIBUTE12,
257 ATTRIBUTE13,
258 ATTRIBUTE14,
259 ATTRIBUTE15,
260 ORG_ID,
261 REQUEST_ID,
262 PROGRAM_APPLICATION_ID,
263 PROGRAM_ID,
264 PROGRAM_UPDATE_DATE,
265 CREATED_BY,
266 CREATION_DATE,
267 LAST_UPDATED_BY,
268 LAST_UPDATE_DATE,
269 LAST_UPDATE_LOGIN,
270 -- start: Apr 25, 2005 cklee: Modification for okl.h
271 MANDATORY_FLAG,
272 USER_COMPLETE_FLAG,
273 ADMIN_NOTE,
274 USER_NOTE,
275 DNZ_CHECKLIST_OBJ_ID,
276 FUNCTION_ID,
277 FUNCTION_VALIDATE_RSTS,
278 FUNCTION_VALIDATE_MSG,
279 INST_CHECKLIST_TYPE
280 -- end: Apr 25, 2005 cklee: Modification for okl.h
281 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
282 ,APPEAL_FLAG
283 FROM Okl_Checklist_Details
284 WHERE okl_checklist_details.id = p_id;
285 l_okl_checklist_details_pk okl_checklist_details_pk_csr%ROWTYPE;
286 l_cld_rec cld_rec_type;
287 BEGIN
288 x_no_data_found := TRUE;
289 -- Get current database values
290 OPEN okl_checklist_details_pk_csr (p_cld_rec.id);
291 FETCH okl_checklist_details_pk_csr INTO
292 l_cld_rec.id,
293 l_cld_rec.object_version_number,
294 l_cld_rec.ckl_id,
295 l_cld_rec.todo_item_code,
296 l_cld_rec.attribute_category,
297 l_cld_rec.attribute1,
298 l_cld_rec.attribute2,
299 l_cld_rec.attribute3,
300 l_cld_rec.attribute4,
301 l_cld_rec.attribute5,
302 l_cld_rec.attribute6,
303 l_cld_rec.attribute7,
304 l_cld_rec.attribute8,
305 l_cld_rec.attribute9,
306 l_cld_rec.attribute10,
307 l_cld_rec.attribute11,
308 l_cld_rec.attribute12,
309 l_cld_rec.attribute13,
310 l_cld_rec.attribute14,
311 l_cld_rec.attribute15,
312 l_cld_rec.org_id,
313 l_cld_rec.request_id,
314 l_cld_rec.program_application_id,
315 l_cld_rec.program_id,
316 l_cld_rec.program_update_date,
317 l_cld_rec.created_by,
318 l_cld_rec.creation_date,
319 l_cld_rec.last_updated_by,
320 l_cld_rec.last_update_date,
321 l_cld_rec.last_update_login,
322 -- start: Apr 25, 2005 cklee: Modification for okl.h
323 l_cld_rec.MANDATORY_FLAG,
324 l_cld_rec.USER_COMPLETE_FLAG,
325 l_cld_rec.ADMIN_NOTE,
326 l_cld_rec.USER_NOTE,
327 l_cld_rec.DNZ_CHECKLIST_OBJ_ID,
328 l_cld_rec.FUNCTION_ID,
329 l_cld_rec.FUNCTION_VALIDATE_RSTS,
330 l_cld_rec.FUNCTION_VALIDATE_MSG,
331 l_cld_rec.INST_CHECKLIST_TYPE
332 -- end: Apr 25, 2005 cklee: Modification for okl.h
333 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
334 ,l_cld_rec.APPEAL_FLAG
335 ;
336 x_no_data_found := okl_checklist_details_pk_csr%NOTFOUND;
337 CLOSE okl_checklist_details_pk_csr;
338 RETURN(l_cld_rec);
339 END get_rec;
340
341 ------------------------------------------------------------------
342 -- This version of get_rec sets error messages if no data found --
343 ------------------------------------------------------------------
344 FUNCTION get_rec (
345 p_cld_rec IN cld_rec_type,
346 x_return_status OUT NOCOPY VARCHAR2
347 ) RETURN cld_rec_type IS
348 l_cld_rec cld_rec_type;
349 l_row_notfound BOOLEAN := TRUE;
350 BEGIN
351 x_return_status := OKL_API.G_RET_STS_SUCCESS;
352 l_cld_rec := get_rec(p_cld_rec, l_row_notfound);
353 IF (l_row_notfound) THEN
354 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
355 x_return_status := OKL_API.G_RET_STS_ERROR;
356 END IF;
357 RETURN(l_cld_rec);
358 END get_rec;
359 -----------------------------------------------------------
360 -- So we don't have to pass an "l_row_notfound" variable --
361 -----------------------------------------------------------
362 FUNCTION get_rec (
363 p_cld_rec IN cld_rec_type
364 ) RETURN cld_rec_type IS
365 l_row_not_found BOOLEAN := TRUE;
366 BEGIN
367 RETURN(get_rec(p_cld_rec, l_row_not_found));
368 END get_rec;
369 ---------------------------------------------------------------------------
370 -- FUNCTION null_out_defaults for: OKL_CHECKLIST_DETAILS_V
371 ---------------------------------------------------------------------------
372 FUNCTION null_out_defaults (
373 p_cldv_rec IN cldv_rec_type
374 ) RETURN cldv_rec_type IS
375 l_cldv_rec cldv_rec_type := p_cldv_rec;
376 BEGIN
377 IF (l_cldv_rec.id = OKL_API.G_MISS_NUM ) THEN
378 l_cldv_rec.id := NULL;
379 END IF;
380 IF (l_cldv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
381 l_cldv_rec.object_version_number := NULL;
382 END IF;
383 IF (l_cldv_rec.ckl_id = OKL_API.G_MISS_NUM ) THEN
384 l_cldv_rec.ckl_id := NULL;
385 END IF;
386 IF (l_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR ) THEN
387 l_cldv_rec.todo_item_code := NULL;
388 END IF;
389 IF (l_cldv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
390 l_cldv_rec.attribute_category := NULL;
391 END IF;
392 IF (l_cldv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
393 l_cldv_rec.attribute1 := NULL;
394 END IF;
395 IF (l_cldv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
396 l_cldv_rec.attribute2 := NULL;
397 END IF;
398 IF (l_cldv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
399 l_cldv_rec.attribute3 := NULL;
400 END IF;
401 IF (l_cldv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
402 l_cldv_rec.attribute4 := NULL;
403 END IF;
404 IF (l_cldv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
405 l_cldv_rec.attribute5 := NULL;
406 END IF;
407 IF (l_cldv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
408 l_cldv_rec.attribute6 := NULL;
409 END IF;
410 IF (l_cldv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
411 l_cldv_rec.attribute7 := NULL;
412 END IF;
413 IF (l_cldv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
414 l_cldv_rec.attribute8 := NULL;
415 END IF;
416 IF (l_cldv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
417 l_cldv_rec.attribute9 := NULL;
418 END IF;
419 IF (l_cldv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
420 l_cldv_rec.attribute10 := NULL;
421 END IF;
422 IF (l_cldv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
423 l_cldv_rec.attribute11 := NULL;
424 END IF;
425 IF (l_cldv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
426 l_cldv_rec.attribute12 := NULL;
427 END IF;
428 IF (l_cldv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
429 l_cldv_rec.attribute13 := NULL;
430 END IF;
431 IF (l_cldv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
432 l_cldv_rec.attribute14 := NULL;
433 END IF;
434 IF (l_cldv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
435 l_cldv_rec.attribute15 := NULL;
436 END IF;
437 IF (l_cldv_rec.org_id = OKL_API.G_MISS_NUM ) THEN
438 l_cldv_rec.org_id := NULL;
439 END IF;
440 IF (l_cldv_rec.request_id = OKL_API.G_MISS_NUM ) THEN
441 l_cldv_rec.request_id := NULL;
442 END IF;
443 IF (l_cldv_rec.program_application_id = OKL_API.G_MISS_NUM ) THEN
444 l_cldv_rec.program_application_id := NULL;
445 END IF;
446 IF (l_cldv_rec.program_id = OKL_API.G_MISS_NUM ) THEN
447 l_cldv_rec.program_id := NULL;
448 END IF;
449 IF (l_cldv_rec.program_update_date = OKL_API.G_MISS_DATE ) THEN
450 l_cldv_rec.program_update_date := NULL;
451 END IF;
452 IF (l_cldv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
453 l_cldv_rec.created_by := NULL;
454 END IF;
455 IF (l_cldv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
456 l_cldv_rec.creation_date := NULL;
457 END IF;
458 IF (l_cldv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
459 l_cldv_rec.last_updated_by := NULL;
460 END IF;
461 IF (l_cldv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
462 l_cldv_rec.last_update_date := NULL;
463 END IF;
464 IF (l_cldv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
465 l_cldv_rec.last_update_login := NULL;
466 END IF;
467
468 -- start: Apr 25, 2005 cklee: Modification for okl.h
469 IF (l_cldv_rec.MANDATORY_FLAG = OKL_API.G_MISS_CHAR ) THEN
470 l_cldv_rec.MANDATORY_FLAG := NULL;
471 END IF;
472 IF (l_cldv_rec.USER_COMPLETE_FLAG = OKL_API.G_MISS_CHAR ) THEN
473 l_cldv_rec.USER_COMPLETE_FLAG := NULL;
474 END IF;
475 IF (l_cldv_rec.ADMIN_NOTE = OKL_API.G_MISS_CHAR ) THEN
476 l_cldv_rec.ADMIN_NOTE := NULL;
477 END IF;
478 IF (l_cldv_rec.USER_NOTE = OKL_API.G_MISS_CHAR ) THEN
479 l_cldv_rec.USER_NOTE := NULL;
480 END IF;
481 IF (l_cldv_rec.DNZ_CHECKLIST_OBJ_ID = OKL_API.G_MISS_NUM ) THEN
482 l_cldv_rec.DNZ_CHECKLIST_OBJ_ID := NULL;
483 END IF;
484 IF (l_cldv_rec.FUNCTION_ID = OKL_API.G_MISS_NUM ) THEN
485 l_cldv_rec.FUNCTION_ID := NULL;
486 END IF;
487 IF (l_cldv_rec.FUNCTION_VALIDATE_RSTS = OKL_API.G_MISS_CHAR ) THEN
488 l_cldv_rec.FUNCTION_VALIDATE_RSTS := NULL;
489 END IF;
490 IF (l_cldv_rec.FUNCTION_VALIDATE_MSG = OKL_API.G_MISS_CHAR ) THEN
491 l_cldv_rec.FUNCTION_VALIDATE_MSG := NULL;
492 END IF;
493 IF (l_cldv_rec.INST_CHECKLIST_TYPE = OKL_API.G_MISS_CHAR ) THEN
494 l_cldv_rec.INST_CHECKLIST_TYPE := NULL;
495 END IF;
496 -- end: Apr 25, 2005 cklee: Modification for okl.h
497 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
498 IF (l_cldv_rec.APPEAL_FLAG = OKL_API.G_MISS_CHAR ) THEN
499 l_cldv_rec.APPEAL_FLAG := NULL;
500 END IF;
501 RETURN(l_cldv_rec);
502 END null_out_defaults;
503 ---------------------------------
504 -- Validate_Attributes for: ID --
505 ---------------------------------
506 PROCEDURE validate_id(
507 x_return_status OUT NOCOPY VARCHAR2,
508 p_id IN NUMBER) IS
509 BEGIN
510 x_return_status := OKL_API.G_RET_STS_SUCCESS;
511 IF (p_id = OKL_API.G_MISS_NUM OR
512 p_id IS NULL)
513 THEN
514 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
515 x_return_status := OKL_API.G_RET_STS_ERROR;
516 RAISE G_EXCEPTION_HALT_VALIDATION;
517 END IF;
518 EXCEPTION
519 WHEN G_EXCEPTION_HALT_VALIDATION THEN
520 null;
521 WHEN OTHERS THEN
522 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
523 ,p_msg_name => G_UNEXPECTED_ERROR
524 ,p_token1 => G_SQLCODE_TOKEN
525 ,p_token1_value => SQLCODE
526 ,p_token2 => G_SQLERRM_TOKEN
527 ,p_token2_value => SQLERRM);
528 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
529 END validate_id;
530
531 ---------------------------------
532 -- Validate_Attributes for: ckl_id --
533 ---------------------------------
534 PROCEDURE validate_ckl_id(
535 x_return_status OUT NOCOPY VARCHAR2,
536 p_ckl_id IN NUMBER) IS
537
538 l_dummy number;
539
540 l_row_not_found boolean := false;
541
542 CURSOR c_fk (p_ckl_id okl_checklists.id%type)
543 IS
544 SELECT 1
545 FROM okl_checklists clh
546 WHERE clh.id = p_ckl_id
547 ;
548
549 BEGIN
550 x_return_status := OKL_API.G_RET_STS_SUCCESS;
551 IF (p_ckl_id = OKL_API.G_MISS_NUM OR
552 p_ckl_id IS NULL)
553 THEN
554 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CKL_ID');
555 x_return_status := OKL_API.G_RET_STS_ERROR;
556 RAISE G_EXCEPTION_HALT_VALIDATION;
557 END IF;
558
559 OPEN c_fk(p_ckl_id);
560 FETCH c_fk INTO l_dummy;
561 l_row_not_found := c_fk%NOTFOUND;
562 CLOSE c_fk;
563
564 IF (l_row_not_found) THEN
565 OKL_API.Set_Message(p_app_name => G_APP_NAME,
566 p_msg_name => G_INVALID_VALUE,
567 p_token1 => G_COL_NAME_TOKEN,
568 p_token1_value => 'CKL_ID');
569
570 RAISE G_EXCEPTION_HALT_VALIDATION;
571 END IF;
572
573 EXCEPTION
574 WHEN G_EXCEPTION_HALT_VALIDATION THEN
575 null;
576 WHEN OTHERS THEN
577 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
578 ,p_msg_name => G_UNEXPECTED_ERROR
579 ,p_token1 => G_SQLCODE_TOKEN
580 ,p_token1_value => SQLCODE
581 ,p_token2 => G_SQLERRM_TOKEN
582 ,p_token2_value => SQLERRM);
583 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
584 END validate_ckl_id;
585
586 ---------------------------------
587 -- Validate_Attributes for: TODO_ITEM_CODE --
588 ---------------------------------
589 PROCEDURE validate_todo_item_code(
590 x_return_status OUT NOCOPY VARCHAR2,
591 p_ckl_id IN number,
592 p_todo_item_code IN VARCHAR2) IS
593
594 l_dummy number;
595 l_row_not_found boolean := false;
596
597 CURSOR c_todo (p_todo_item_code okl_checklist_details.todo_item_code%type)
598 IS
599 SELECT 1
600 FROM fnd_lookups lok
601 WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
602 AND lok.lookup_code = p_todo_item_code
603 ;
604
605 BEGIN
606 x_return_status := OKL_API.G_RET_STS_SUCCESS;
607 IF (p_todo_item_code = OKL_API.G_MISS_CHAR OR
608 p_todo_item_code IS NULL)
609 THEN
610 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'TODO_ITEM_CODE');
611 x_return_status := OKL_API.G_RET_STS_ERROR;
612 RAISE G_EXCEPTION_HALT_VALIDATION;
613 END IF;
614
615 -- FK check
616 OPEN c_todo (p_todo_item_code);
617 FETCH c_todo INTO l_dummy;
618 l_row_not_found := c_todo%NOTFOUND;
619 CLOSE c_todo;
620
621 IF (l_row_not_found) THEN
622 OKL_API.Set_Message(p_app_name => G_APP_NAME,
623 p_msg_name => G_INVALID_VALUE,
624 p_token1 => G_COL_NAME_TOKEN,
625 p_token1_value => 'TODO_ITEM_CODE');
626
627 RAISE G_EXCEPTION_HALT_VALIDATION;
628 END IF;
629
630 EXCEPTION
631 WHEN G_EXCEPTION_HALT_VALIDATION THEN
632 null;
633 WHEN OTHERS THEN
634 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
635 ,p_msg_name => G_UNEXPECTED_ERROR
636 ,p_token1 => G_SQLCODE_TOKEN
637 ,p_token1_value => SQLCODE
638 ,p_token2 => G_SQLERRM_TOKEN
639 ,p_token2_value => SQLERRM);
640 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
641 END validate_todo_item_code;
642
643 ---------------------------------------------------------------------------
644 -- FUNCTION Validate_Attributes
645 ---------------------------------------------------------------------------
646 -----------------------------------------------------
647 -- Validate_Attributes for:OKL_CHECKLIST_DETAILS_V --
648 -----------------------------------------------------
649 FUNCTION Validate_Attributes (
650 p_cldv_rec IN cldv_rec_type
651 ) RETURN VARCHAR2 IS
652 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
653 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
654 BEGIN
655 -----------------------------
656 -- Column Level Validation --
657 -----------------------------
658 -- ***
659 -- id
660 -- ***
661 validate_id(x_return_status, p_cldv_rec.id);
662 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
663 l_return_status := x_return_status;
664 RAISE G_EXCEPTION_HALT_VALIDATION;
665 END IF;
666
667 -- ***
668 -- ckl_id
669 -- ***
670 validate_ckl_id(x_return_status, p_cldv_rec.ckl_id);
671 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
672 l_return_status := x_return_status;
673 RAISE G_EXCEPTION_HALT_VALIDATION;
674 END IF;
675
676 -- ***
677 -- todo_item_code
678 -- ***
679 validate_todo_item_code(x_return_status, p_cldv_rec.ckl_id, p_cldv_rec.todo_item_code);
680 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
681 l_return_status := x_return_status;
682 RAISE G_EXCEPTION_HALT_VALIDATION;
683 END IF;
684
685 RETURN(l_return_status);
686 EXCEPTION
687 WHEN G_EXCEPTION_HALT_VALIDATION THEN
688 RETURN(l_return_status);
689 WHEN OTHERS THEN
690 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
691 ,p_msg_name => G_UNEXPECTED_ERROR
692 ,p_token1 => G_SQLCODE_TOKEN
693 ,p_token1_value => SQLCODE
694 ,p_token2 => G_SQLERRM_TOKEN
695 ,p_token2_value => SQLERRM);
696 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
697 RETURN(l_return_status);
698 END Validate_Attributes;
699 ---------------------------------------------------------------------------
700 -- PROCEDURE Validate_Record
701 ---------------------------------------------------------------------------
702 -------------------------------------------------
703 -- Validate Record for:OKL_CHECKLIST_DETAILS_V --
704 -------------------------------------------------
705 FUNCTION Validate_Record (
706 p_cldv_rec IN cldv_rec_type,
707 p_db_cldv_rec IN cldv_rec_type
708 ) RETURN VARCHAR2 IS
709 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
710 BEGIN
711 RETURN (l_return_status);
712 END Validate_Record;
713 FUNCTION Validate_Record (
714 p_cldv_rec IN cldv_rec_type
715 ) RETURN VARCHAR2 IS
716 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
717 l_db_cldv_rec cldv_rec_type := get_rec(p_cldv_rec);
718 BEGIN
719 l_return_status := Validate_Record(p_cldv_rec => p_cldv_rec,
720 p_db_cldv_rec => l_db_cldv_rec);
721 RETURN (l_return_status);
722 END Validate_Record;
723
724 ---------------------------------------------------------------------------
725 -- PROCEDURE Migrate
726 ---------------------------------------------------------------------------
727 PROCEDURE migrate (
728 p_from IN cldv_rec_type,
729 p_to IN OUT NOCOPY cld_rec_type
730 ) IS
731 BEGIN
732 p_to.id := p_from.id;
733 p_to.object_version_number := p_from.object_version_number;
734 p_to.ckl_id := p_from.ckl_id;
735 p_to.todo_item_code := p_from.todo_item_code;
736 p_to.attribute_category := p_from.attribute_category;
737 p_to.attribute1 := p_from.attribute1;
738 p_to.attribute2 := p_from.attribute2;
739 p_to.attribute3 := p_from.attribute3;
740 p_to.attribute4 := p_from.attribute4;
741 p_to.attribute5 := p_from.attribute5;
742 p_to.attribute6 := p_from.attribute6;
743 p_to.attribute7 := p_from.attribute7;
744 p_to.attribute8 := p_from.attribute8;
745 p_to.attribute9 := p_from.attribute9;
746 p_to.attribute10 := p_from.attribute10;
747 p_to.attribute11 := p_from.attribute11;
748 p_to.attribute12 := p_from.attribute12;
749 p_to.attribute13 := p_from.attribute13;
750 p_to.attribute14 := p_from.attribute14;
751 p_to.attribute15 := p_from.attribute15;
752 p_to.org_id := p_from.org_id;
753 p_to.request_id := p_from.request_id;
754 p_to.program_application_id := p_from.program_application_id;
755 p_to.program_id := p_from.program_id;
756 p_to.program_update_date := p_from.program_update_date;
757 p_to.created_by := p_from.created_by;
758 p_to.creation_date := p_from.creation_date;
759 p_to.last_updated_by := p_from.last_updated_by;
760 p_to.last_update_date := p_from.last_update_date;
761 p_to.last_update_login := p_from.last_update_login;
762 -- start: Apr 25, 2005 cklee: Modification for okl.h
763 p_to.MANDATORY_FLAG := p_from.MANDATORY_FLAG;
764 p_to.USER_COMPLETE_FLAG := p_from.USER_COMPLETE_FLAG;
765 p_to.ADMIN_NOTE := p_from.ADMIN_NOTE;
766 p_to.USER_NOTE := p_from.USER_NOTE;
767 p_to.DNZ_CHECKLIST_OBJ_ID := p_from.DNZ_CHECKLIST_OBJ_ID;
768 p_to.FUNCTION_ID := p_from.FUNCTION_ID;
769 p_to.FUNCTION_VALIDATE_RSTS := p_from.FUNCTION_VALIDATE_RSTS;
770 p_to.FUNCTION_VALIDATE_MSG := p_from.FUNCTION_VALIDATE_MSG;
771 p_to.INST_CHECKLIST_TYPE := p_from.INST_CHECKLIST_TYPE;
772 -- end: Apr 25, 2005 cklee: Modification for okl.h
773 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
774 p_to.APPEAL_FLAG := p_from.APPEAL_FLAG;
775 END migrate;
776 PROCEDURE migrate (
777 p_from IN cld_rec_type,
778 p_to IN OUT NOCOPY cldv_rec_type
779 ) IS
780 BEGIN
781 p_to.id := p_from.id;
782 p_to.object_version_number := p_from.object_version_number;
783 p_to.ckl_id := p_from.ckl_id;
784 p_to.todo_item_code := p_from.todo_item_code;
785 p_to.attribute_category := p_from.attribute_category;
786 p_to.attribute1 := p_from.attribute1;
787 p_to.attribute2 := p_from.attribute2;
788 p_to.attribute3 := p_from.attribute3;
789 p_to.attribute4 := p_from.attribute4;
790 p_to.attribute5 := p_from.attribute5;
791 p_to.attribute6 := p_from.attribute6;
792 p_to.attribute7 := p_from.attribute7;
793 p_to.attribute8 := p_from.attribute8;
794 p_to.attribute9 := p_from.attribute9;
795 p_to.attribute10 := p_from.attribute10;
796 p_to.attribute11 := p_from.attribute11;
797 p_to.attribute12 := p_from.attribute12;
798 p_to.attribute13 := p_from.attribute13;
799 p_to.attribute14 := p_from.attribute14;
800 p_to.attribute15 := p_from.attribute15;
801 p_to.org_id := p_from.org_id;
802 p_to.request_id := p_from.request_id;
803 p_to.program_application_id := p_from.program_application_id;
804 p_to.program_id := p_from.program_id;
805 p_to.program_update_date := p_from.program_update_date;
806 p_to.created_by := p_from.created_by;
807 p_to.creation_date := p_from.creation_date;
808 p_to.last_updated_by := p_from.last_updated_by;
809 p_to.last_update_date := p_from.last_update_date;
810 p_to.last_update_login := p_from.last_update_login;
811 -- start: Apr 25, 2005 cklee: Modification for okl.h
812 p_to.MANDATORY_FLAG := p_from.MANDATORY_FLAG;
813 p_to.USER_COMPLETE_FLAG := p_from.USER_COMPLETE_FLAG;
814 p_to.ADMIN_NOTE := p_from.ADMIN_NOTE;
815 p_to.USER_NOTE := p_from.USER_NOTE;
816 p_to.DNZ_CHECKLIST_OBJ_ID := p_from.DNZ_CHECKLIST_OBJ_ID;
817 p_to.FUNCTION_ID := p_from.FUNCTION_ID;
818 p_to.FUNCTION_VALIDATE_RSTS := p_from.FUNCTION_VALIDATE_RSTS;
819 p_to.FUNCTION_VALIDATE_MSG := p_from.FUNCTION_VALIDATE_MSG;
820 p_to.INST_CHECKLIST_TYPE := p_from.INST_CHECKLIST_TYPE;
821 -- end: Apr 25, 2005 cklee: Modification for okl.h
822 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
823 p_to.APPEAL_FLAG := p_from.APPEAL_FLAG;
824 END migrate;
825 ---------------------------------------------------------------------------
826 -- PROCEDURE validate_row
827 ---------------------------------------------------------------------------
828 ----------------------------------------------
829 -- validate_row for:OKL_CHECKLIST_DETAILS_V --
830 ----------------------------------------------
831 PROCEDURE validate_row(
832 p_api_version IN NUMBER,
833 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2,
837 p_cldv_rec IN cldv_rec_type) IS
838
839 l_api_version CONSTANT NUMBER := 1;
840 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
841 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
842 l_cldv_rec cldv_rec_type := p_cldv_rec;
843 l_cld_rec cld_rec_type;
844 l_cld_rec cld_rec_type;
845 BEGIN
846 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
847 G_PKG_NAME,
848 p_init_msg_list,
849 l_api_version,
850 p_api_version,
851 '_PVT',
852 x_return_status);
853 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
854 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
856 RAISE OKL_API.G_EXCEPTION_ERROR;
857 END IF;
858 --- Validate all non-missing attributes (Item Level Validation)
859 l_return_status := Validate_Attributes(l_cldv_rec);
860 --- If any errors happen abort API
861 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
862 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
863 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
864 RAISE OKL_API.G_EXCEPTION_ERROR;
865 END IF;
866 l_return_status := Validate_Record(l_cldv_rec);
867 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
869 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
870 RAISE OKL_API.G_EXCEPTION_ERROR;
871 END IF;
872 x_return_status := l_return_status;
873 EXCEPTION
874 WHEN OKL_API.G_EXCEPTION_ERROR THEN
875 x_return_status := OKL_API.HANDLE_EXCEPTIONS
876 (
877 l_api_name,
878 G_PKG_NAME,
879 'OKL_API.G_RET_STS_ERROR',
880 x_msg_count,
881 x_msg_data,
882 '_PVT'
883 );
884 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
885 x_return_status := OKL_API.HANDLE_EXCEPTIONS
886 (
887 l_api_name,
888 G_PKG_NAME,
889 'OKL_API.G_RET_STS_UNEXP_ERROR',
890 x_msg_count,
891 x_msg_data,
892 '_PVT'
893 );
894 WHEN OTHERS THEN
895 x_return_status := OKL_API.HANDLE_EXCEPTIONS
896 (
897 l_api_name,
898 G_PKG_NAME,
899 'OTHERS',
900 x_msg_count,
901 x_msg_data,
902 '_PVT'
903 );
904 END validate_row;
905 ---------------------------------------------------------
906 -- PL/SQL TBL validate_row for:OKL_CHECKLIST_DETAILS_V --
907 ---------------------------------------------------------
908 PROCEDURE validate_row(
909 p_api_version IN NUMBER,
910 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
911 x_return_status OUT NOCOPY VARCHAR2,
912 x_msg_count OUT NOCOPY NUMBER,
913 x_msg_data OUT NOCOPY VARCHAR2,
914 p_cldv_tbl IN cldv_tbl_type,
915 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
916
917 l_api_version CONSTANT NUMBER := 1;
918 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
919 i NUMBER := 0;
920 BEGIN
921 OKL_API.init_msg_list(p_init_msg_list);
922 -- Make sure PL/SQL table has records in it before passing
923 IF (p_cldv_tbl.COUNT > 0) THEN
924 i := p_cldv_tbl.FIRST;
925 LOOP
926 DECLARE
927 l_error_rec OKL_API.ERROR_REC_TYPE;
928 BEGIN
929 l_error_rec.api_name := l_api_name;
930 l_error_rec.api_package := G_PKG_NAME;
931 l_error_rec.idx := i;
932 validate_row (
933 p_api_version => p_api_version,
934 p_init_msg_list => OKL_API.G_FALSE,
935 x_return_status => l_error_rec.error_type,
936 x_msg_count => l_error_rec.msg_count,
937 x_msg_data => l_error_rec.msg_data,
938 p_cldv_rec => p_cldv_tbl(i));
939 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
940 l_error_rec.sqlcode := SQLCODE;
941 load_error_tbl(l_error_rec, px_error_tbl);
942 ELSE
943 x_msg_count := l_error_rec.msg_count;
944 x_msg_data := l_error_rec.msg_data;
945 END IF;
946 EXCEPTION
947 WHEN OKL_API.G_EXCEPTION_ERROR THEN
948 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
949 l_error_rec.sqlcode := SQLCODE;
950 load_error_tbl(l_error_rec, px_error_tbl);
951 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
952 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
953 l_error_rec.sqlcode := SQLCODE;
954 load_error_tbl(l_error_rec, px_error_tbl);
955 WHEN OTHERS THEN
956 l_error_rec.error_type := 'OTHERS';
957 l_error_rec.sqlcode := SQLCODE;
958 load_error_tbl(l_error_rec, px_error_tbl);
959 END;
960 EXIT WHEN (i = p_cldv_tbl.LAST);
961 i := p_cldv_tbl.NEXT(i);
962 END LOOP;
963 END IF;
964 -- Loop through the error_tbl to find the error with the highest severity
965 -- and return it.
966 x_return_status := find_highest_exception(px_error_tbl);
967 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
968 EXCEPTION
969 WHEN OKL_API.G_EXCEPTION_ERROR THEN
970 x_return_status := OKL_API.HANDLE_EXCEPTIONS
971 (
972 l_api_name,
973 G_PKG_NAME,
974 'OKL_API.G_RET_STS_ERROR',
975 x_msg_count,
976 x_msg_data,
977 '_PVT'
978 );
979 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
980 x_return_status := OKL_API.HANDLE_EXCEPTIONS
981 (
982 l_api_name,
983 G_PKG_NAME,
984 'OKL_API.G_RET_STS_UNEXP_ERROR',
985 x_msg_count,
986 x_msg_data,
987 '_PVT'
988 );
989 WHEN OTHERS THEN
990 x_return_status := OKL_API.HANDLE_EXCEPTIONS
991 (
992 l_api_name,
993 G_PKG_NAME,
994 'OTHERS',
995 x_msg_count,
996 x_msg_data,
997 '_PVT'
998 );
999 END validate_row;
1000
1001 ---------------------------------------------------------
1002 -- PL/SQL TBL validate_row for:OKL_CHECKLIST_DETAILS_V --
1003 ---------------------------------------------------------
1004 PROCEDURE validate_row(
1005 p_api_version IN NUMBER,
1006 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1007 x_return_status OUT NOCOPY VARCHAR2,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2,
1010 p_cldv_tbl IN cldv_tbl_type) IS
1011
1012 l_api_version CONSTANT NUMBER := 1;
1013 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1014 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1015 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1016 BEGIN
1017 OKL_API.init_msg_list(p_init_msg_list);
1018 -- Make sure PL/SQL table has records in it before passing
1019 IF (p_cldv_tbl.COUNT > 0) THEN
1020 validate_row (
1021 p_api_version => p_api_version,
1022 p_init_msg_list => OKL_API.G_FALSE,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_cldv_tbl => p_cldv_tbl,
1027 px_error_tbl => l_error_tbl);
1028 END IF;
1029 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1030 EXCEPTION
1031 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1032 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1033 (
1034 l_api_name,
1035 G_PKG_NAME,
1036 'OKL_API.G_RET_STS_ERROR',
1037 x_msg_count,
1038 x_msg_data,
1039 '_PVT'
1040 );
1041 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1042 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1043 (
1044 l_api_name,
1045 G_PKG_NAME,
1046 'OKL_API.G_RET_STS_UNEXP_ERROR',
1047 x_msg_count,
1048 x_msg_data,
1049 '_PVT'
1050 );
1051 WHEN OTHERS THEN
1052 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1053 (
1054 l_api_name,
1055 G_PKG_NAME,
1056 'OTHERS',
1057 x_msg_count,
1058 x_msg_data,
1059 '_PVT'
1060 );
1061 END validate_row;
1062
1063 ---------------------------------------------------------------------------
1064 -- PROCEDURE insert_row
1065 ---------------------------------------------------------------------------
1066 ------------------------------------------
1067 -- insert_row for:OKL_CHECKLIST_DETAILS --
1068 ------------------------------------------
1069 PROCEDURE insert_row(
1070 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1071 x_return_status OUT NOCOPY VARCHAR2,
1072 x_msg_count OUT NOCOPY NUMBER,
1073 x_msg_data OUT NOCOPY VARCHAR2,
1074 p_cld_rec IN cld_rec_type,
1075 x_cld_rec OUT NOCOPY cld_rec_type) IS
1076
1077 l_api_version CONSTANT NUMBER := 1;
1078 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1079 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1080 l_cld_rec cld_rec_type := p_cld_rec;
1081 l_def_cld_rec cld_rec_type;
1082 ----------------------------------------------
1083 -- Set_Attributes for:OKL_CHECKLIST_DETAILS --
1084 ----------------------------------------------
1085 FUNCTION Set_Attributes (
1086 p_cld_rec IN cld_rec_type,
1087 x_cld_rec OUT NOCOPY cld_rec_type
1088 ) RETURN VARCHAR2 IS
1089 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1090 BEGIN
1091 x_cld_rec := p_cld_rec;
1092 RETURN(l_return_status);
1093 END Set_Attributes;
1094 BEGIN
1095 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1096 p_init_msg_list,
1097 '_PVT',
1098 x_return_status);
1099 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1101 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1102 RAISE OKL_API.G_EXCEPTION_ERROR;
1103 END IF;
1104 --- Setting item atributes
1105 l_return_status := Set_Attributes(
1106 p_cld_rec, -- IN
1107 l_cld_rec); -- OUT
1108 --- If any errors happen abort API
1109 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1110 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1111 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1112 RAISE OKL_API.G_EXCEPTION_ERROR;
1113 END IF;
1114 INSERT INTO OKL_CHECKLIST_DETAILS(
1115 id,
1116 object_version_number,
1117 ckl_id,
1118 todo_item_code,
1119 attribute_category,
1120 attribute1,
1121 attribute2,
1122 attribute3,
1123 attribute4,
1124 attribute5,
1125 attribute6,
1126 attribute7,
1127 attribute8,
1128 attribute9,
1129 attribute10,
1130 attribute11,
1131 attribute12,
1132 attribute13,
1133 attribute14,
1134 attribute15,
1135 org_id,
1136 request_id,
1137 program_application_id,
1138 program_id,
1139 program_update_date,
1140 created_by,
1141 creation_date,
1142 last_updated_by,
1143 last_update_date,
1144 last_update_login,
1145 -- start: Apr 25, 2005 cklee: Modification for okl.h
1146 MANDATORY_FLAG,
1147 USER_COMPLETE_FLAG,
1148 ADMIN_NOTE,
1149 USER_NOTE,
1150 DNZ_CHECKLIST_OBJ_ID,
1151 FUNCTION_ID,
1152 FUNCTION_VALIDATE_RSTS,
1153 FUNCTION_VALIDATE_MSG,
1154 INST_CHECKLIST_TYPE
1155 -- end: Apr 25, 2005 cklee: Modification for okl.h
1156 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
1157 ,APPEAL_FLAG
1158 )
1159 VALUES (
1160 l_cld_rec.id,
1161 l_cld_rec.object_version_number,
1162 l_cld_rec.ckl_id,
1163 l_cld_rec.todo_item_code,
1164 l_cld_rec.attribute_category,
1165 l_cld_rec.attribute1,
1166 l_cld_rec.attribute2,
1167 l_cld_rec.attribute3,
1168 l_cld_rec.attribute4,
1169 l_cld_rec.attribute5,
1170 l_cld_rec.attribute6,
1171 l_cld_rec.attribute7,
1172 l_cld_rec.attribute8,
1173 l_cld_rec.attribute9,
1174 l_cld_rec.attribute10,
1175 l_cld_rec.attribute11,
1176 l_cld_rec.attribute12,
1177 l_cld_rec.attribute13,
1178 l_cld_rec.attribute14,
1179 l_cld_rec.attribute15,
1180 l_cld_rec.org_id,
1181 l_cld_rec.request_id,
1182 l_cld_rec.program_application_id,
1183 l_cld_rec.program_id,
1184 l_cld_rec.program_update_date,
1185 l_cld_rec.created_by,
1186 l_cld_rec.creation_date,
1187 l_cld_rec.last_updated_by,
1188 l_cld_rec.last_update_date,
1189 l_cld_rec.last_update_login,
1190 -- start: Apr 25, 2005 cklee: Modification for okl.h
1191 l_cld_rec.MANDATORY_FLAG,
1192 l_cld_rec.USER_COMPLETE_FLAG,
1193 l_cld_rec.ADMIN_NOTE,
1194 l_cld_rec.USER_NOTE,
1195 l_cld_rec.DNZ_CHECKLIST_OBJ_ID,
1196 l_cld_rec.FUNCTION_ID,
1197 l_cld_rec.FUNCTION_VALIDATE_RSTS,
1198 l_cld_rec.FUNCTION_VALIDATE_MSG,
1199 l_cld_rec.INST_CHECKLIST_TYPE
1200 -- end: Apr 25, 2005 cklee: Modification for okl.h
1201 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
1202 ,l_cld_rec.APPEAL_FLAG
1203 );
1204 -- Set OUT values
1205 x_cld_rec := l_cld_rec;
1206 x_return_status := l_return_status;
1207 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1208 EXCEPTION
1209 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1210 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1211 (
1212 l_api_name,
1213 G_PKG_NAME,
1214 'OKL_API.G_RET_STS_ERROR',
1215 x_msg_count,
1216 x_msg_data,
1217 '_PVT'
1218 );
1219 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1220 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1221 (
1222 l_api_name,
1223 G_PKG_NAME,
1224 'OKL_API.G_RET_STS_UNEXP_ERROR',
1225 x_msg_count,
1226 x_msg_data,
1227 '_PVT'
1228 );
1229 WHEN OTHERS THEN
1230 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1231 (
1232 l_api_name,
1233 G_PKG_NAME,
1234 'OTHERS',
1235 x_msg_count,
1236 x_msg_data,
1237 '_PVT'
1238 );
1239 END insert_row;
1240 ---------------------------------------------
1241 -- insert_row for :OKL_CHECKLIST_DETAILS_V --
1242 ---------------------------------------------
1243 PROCEDURE insert_row(
1244 p_api_version IN NUMBER,
1245 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1246 x_return_status OUT NOCOPY VARCHAR2,
1247 x_msg_count OUT NOCOPY NUMBER,
1248 x_msg_data OUT NOCOPY VARCHAR2,
1249 p_cldv_rec IN cldv_rec_type,
1250 x_cldv_rec OUT NOCOPY cldv_rec_type) IS
1251
1252 l_api_version CONSTANT NUMBER := 1;
1253 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1254 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1255 l_cldv_rec cldv_rec_type := p_cldv_rec;
1256 l_def_cldv_rec cldv_rec_type;
1257 l_cld_rec cld_rec_type;
1258 lx_cld_rec cld_rec_type;
1259 -------------------------------
1260 -- FUNCTION fill_who_columns --
1261 -------------------------------
1262 FUNCTION fill_who_columns (
1263 p_cldv_rec IN cldv_rec_type
1264 ) RETURN cldv_rec_type IS
1265 l_cldv_rec cldv_rec_type := p_cldv_rec;
1266 BEGIN
1267 l_cldv_rec.CREATION_DATE := SYSDATE;
1268 l_cldv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1269 l_cldv_rec.LAST_UPDATE_DATE := l_cldv_rec.CREATION_DATE;
1270 l_cldv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1271 l_cldv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1272 RETURN(l_cldv_rec);
1273 END fill_who_columns;
1274 ------------------------------------------------
1275 -- Set_Attributes for:OKL_CHECKLIST_DETAILS_V --
1276 ------------------------------------------------
1277 FUNCTION Set_Attributes (
1278 p_cldv_rec IN cldv_rec_type,
1279 x_cldv_rec OUT NOCOPY cldv_rec_type
1280 ) RETURN VARCHAR2 IS
1281 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1282 BEGIN
1283 x_cldv_rec := p_cldv_rec;
1284 x_cldv_rec.OBJECT_VERSION_NUMBER := 1;
1285
1286 -- concurrent program columns
1287 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL,Fnd_Global.CONC_REQUEST_ID),
1288 DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL,Fnd_Global.PROG_APPL_ID),
1289 DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL,Fnd_Global.CONC_PROGRAM_ID),
1290 DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
1291 INTO x_cldv_rec.REQUEST_ID
1292 ,x_cldv_rec.PROGRAM_APPLICATION_ID
1293 ,x_cldv_rec.PROGRAM_ID
1294 ,x_cldv_rec.PROGRAM_UPDATE_DATE
1295 FROM DUAL;
1296 x_cldv_rec.org_id := mo_global.get_current_org_id();
1297
1298 RETURN(l_return_status);
1299 END Set_Attributes;
1300 BEGIN
1301 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1302 G_PKG_NAME,
1303 p_init_msg_list,
1304 l_api_version,
1305 p_api_version,
1306 '_PVT',
1307 x_return_status);
1308 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1309 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1310 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1311 RAISE OKL_API.G_EXCEPTION_ERROR;
1312 END IF;
1313 l_cldv_rec := null_out_defaults(p_cldv_rec);
1314 -- Set primary key value
1315 l_cldv_rec.ID := get_seq_id;
1316 -- Setting item attributes
1317 l_return_Status := Set_Attributes(
1318 l_cldv_rec, -- IN
1319 l_def_cldv_rec); -- OUT
1320 --- If any errors happen abort API
1321 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1322 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1323 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1324 RAISE OKL_API.G_EXCEPTION_ERROR;
1325 END IF;
1326 l_def_cldv_rec := fill_who_columns(l_def_cldv_rec);
1327 --- Validate all non-missing attributes (Item Level Validation)
1328 l_return_status := Validate_Attributes(l_def_cldv_rec);
1329 --- If any errors happen abort API
1330 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1331 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1332 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1333 RAISE OKL_API.G_EXCEPTION_ERROR;
1334 END IF;
1335 l_return_status := Validate_Record(l_def_cldv_rec);
1336 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1338 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1339 RAISE OKL_API.G_EXCEPTION_ERROR;
1340 END IF;
1341 -----------------------------------------
1342 -- Move VIEW record to "Child" records --
1343 -----------------------------------------
1344 migrate(l_def_cldv_rec, l_cld_rec);
1345 -----------------------------------------------
1346 -- Call the INSERT_ROW for each child record --
1347 -----------------------------------------------
1348 insert_row(
1349 p_init_msg_list,
1350 l_return_status,
1351 x_msg_count,
1352 x_msg_data,
1353 l_cld_rec,
1354 lx_cld_rec
1355 );
1356 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1357 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1358 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1359 RAISE OKL_API.G_EXCEPTION_ERROR;
1360 END IF;
1361 migrate(lx_cld_rec, l_def_cldv_rec);
1362 -- Set OUT values
1363 x_cldv_rec := l_def_cldv_rec;
1364 x_return_status := l_return_status;
1365 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1366 EXCEPTION
1367 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1368 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1369 (
1370 l_api_name,
1371 G_PKG_NAME,
1372 'OKL_API.G_RET_STS_ERROR',
1373 x_msg_count,
1374 x_msg_data,
1375 '_PVT'
1376 );
1377 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1378 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1379 (
1380 l_api_name,
1381 G_PKG_NAME,
1382 'OKL_API.G_RET_STS_UNEXP_ERROR',
1383 x_msg_count,
1384 x_msg_data,
1385 '_PVT'
1386 );
1387 WHEN OTHERS THEN
1388 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1389 (
1390 l_api_name,
1391 G_PKG_NAME,
1392 'OTHERS',
1393 x_msg_count,
1394 x_msg_data,
1395 '_PVT'
1396 );
1397 END insert_row;
1398 ----------------------------------------
1399 -- PL/SQL TBL insert_row for:CLDV_TBL --
1400 ----------------------------------------
1401 PROCEDURE insert_row(
1402 p_api_version IN NUMBER,
1403 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1404 x_return_status OUT NOCOPY VARCHAR2,
1405 x_msg_count OUT NOCOPY NUMBER,
1406 x_msg_data OUT NOCOPY VARCHAR2,
1407 p_cldv_tbl IN cldv_tbl_type,
1408 x_cldv_tbl OUT NOCOPY cldv_tbl_type,
1409 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1410
1411 l_api_version CONSTANT NUMBER := 1;
1412 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1413 i NUMBER := 0;
1414 BEGIN
1415 OKL_API.init_msg_list(p_init_msg_list);
1416 -- Make sure PL/SQL table has records in it before passing
1417 IF (p_cldv_tbl.COUNT > 0) THEN
1418 i := p_cldv_tbl.FIRST;
1419 LOOP
1420 DECLARE
1421 l_error_rec OKL_API.ERROR_REC_TYPE;
1422 BEGIN
1423 l_error_rec.api_name := l_api_name;
1424 l_error_rec.api_package := G_PKG_NAME;
1425 l_error_rec.idx := i;
1426 insert_row (
1427 p_api_version => p_api_version,
1428 p_init_msg_list => OKL_API.G_FALSE,
1429 x_return_status => l_error_rec.error_type,
1430 x_msg_count => l_error_rec.msg_count,
1431 x_msg_data => l_error_rec.msg_data,
1432 p_cldv_rec => p_cldv_tbl(i),
1433 x_cldv_rec => x_cldv_tbl(i));
1434 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1435 l_error_rec.sqlcode := SQLCODE;
1436 load_error_tbl(l_error_rec, px_error_tbl);
1437 ELSE
1438 x_msg_count := l_error_rec.msg_count;
1439 x_msg_data := l_error_rec.msg_data;
1440 END IF;
1441 EXCEPTION
1442 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1443 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1444 l_error_rec.sqlcode := SQLCODE;
1445 load_error_tbl(l_error_rec, px_error_tbl);
1446 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1447 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1448 l_error_rec.sqlcode := SQLCODE;
1449 load_error_tbl(l_error_rec, px_error_tbl);
1450 WHEN OTHERS THEN
1451 l_error_rec.error_type := 'OTHERS';
1452 l_error_rec.sqlcode := SQLCODE;
1453 load_error_tbl(l_error_rec, px_error_tbl);
1454 END;
1455 EXIT WHEN (i = p_cldv_tbl.LAST);
1456 i := p_cldv_tbl.NEXT(i);
1457 END LOOP;
1458 END IF;
1459 -- Loop through the error_tbl to find the error with the highest severity
1460 -- and return it.
1461 x_return_status := find_highest_exception(px_error_tbl);
1462 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1463 EXCEPTION
1464 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1465 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1466 (
1467 l_api_name,
1468 G_PKG_NAME,
1469 'OKL_API.G_RET_STS_ERROR',
1470 x_msg_count,
1471 x_msg_data,
1472 '_PVT'
1473 );
1474 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1475 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1476 (
1477 l_api_name,
1478 G_PKG_NAME,
1479 'OKL_API.G_RET_STS_UNEXP_ERROR',
1480 x_msg_count,
1481 x_msg_data,
1482 '_PVT'
1483 );
1484 WHEN OTHERS THEN
1485 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1486 (
1487 l_api_name,
1488 G_PKG_NAME,
1489 'OTHERS',
1490 x_msg_count,
1491 x_msg_data,
1492 '_PVT'
1493 );
1494 END insert_row;
1495
1496 ----------------------------------------
1497 -- PL/SQL TBL insert_row for:CLDV_TBL --
1498 ----------------------------------------
1499 PROCEDURE insert_row(
1500 p_api_version IN NUMBER,
1501 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1502 x_return_status OUT NOCOPY VARCHAR2,
1503 x_msg_count OUT NOCOPY NUMBER,
1504 x_msg_data OUT NOCOPY VARCHAR2,
1505 p_cldv_tbl IN cldv_tbl_type,
1506 x_cldv_tbl OUT NOCOPY cldv_tbl_type) IS
1507
1508 l_api_version CONSTANT NUMBER := 1;
1509 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1510 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1511 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1512 BEGIN
1513 OKL_API.init_msg_list(p_init_msg_list);
1514 -- Make sure PL/SQL table has records in it before passing
1515 IF (p_cldv_tbl.COUNT > 0) THEN
1516 insert_row (
1517 p_api_version => p_api_version,
1518 p_init_msg_list => OKL_API.G_FALSE,
1519 x_return_status => x_return_status,
1520 x_msg_count => x_msg_count,
1521 x_msg_data => x_msg_data,
1522 p_cldv_tbl => p_cldv_tbl,
1523 x_cldv_tbl => x_cldv_tbl,
1524 px_error_tbl => l_error_tbl);
1525 END IF;
1526 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1527 EXCEPTION
1528 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1529 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1530 (
1531 l_api_name,
1532 G_PKG_NAME,
1533 'OKL_API.G_RET_STS_ERROR',
1534 x_msg_count,
1535 x_msg_data,
1536 '_PVT'
1537 );
1538 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1539 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1540 (
1541 l_api_name,
1542 G_PKG_NAME,
1543 'OKL_API.G_RET_STS_UNEXP_ERROR',
1544 x_msg_count,
1545 x_msg_data,
1546 '_PVT'
1547 );
1548 WHEN OTHERS THEN
1549 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1550 (
1551 l_api_name,
1552 G_PKG_NAME,
1553 'OTHERS',
1554 x_msg_count,
1555 x_msg_data,
1556 '_PVT'
1557 );
1558 END insert_row;
1559
1560 ---------------------------------------------------------------------------
1561 -- PROCEDURE lock_row
1562 ---------------------------------------------------------------------------
1563 ----------------------------------------
1564 -- lock_row for:OKL_CHECKLIST_DETAILS --
1565 ----------------------------------------
1566 PROCEDURE lock_row(
1567 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1568 x_return_status OUT NOCOPY VARCHAR2,
1569 x_msg_count OUT NOCOPY NUMBER,
1570 x_msg_data OUT NOCOPY VARCHAR2,
1571 p_cld_rec IN cld_rec_type) IS
1572
1573 E_Resource_Busy EXCEPTION;
1574 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1575 CURSOR lock_csr (p_cld_rec IN cld_rec_type) IS
1576 SELECT OBJECT_VERSION_NUMBER
1577 FROM OKL_CHECKLIST_DETAILS
1578 WHERE ID = p_cld_rec.id
1579 AND OBJECT_VERSION_NUMBER = p_cld_rec.object_version_number
1580 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1581
1582 CURSOR lchk_csr (p_cld_rec IN cld_rec_type) IS
1583 SELECT OBJECT_VERSION_NUMBER
1584 FROM OKL_CHECKLIST_DETAILS
1585 WHERE ID = p_cld_rec.id;
1586 l_api_version CONSTANT NUMBER := 1;
1587 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1588 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1589 l_object_version_number OKL_CHECKLIST_DETAILS.OBJECT_VERSION_NUMBER%TYPE;
1590 lc_object_version_number OKL_CHECKLIST_DETAILS.OBJECT_VERSION_NUMBER%TYPE;
1591 l_row_notfound BOOLEAN := FALSE;
1592 lc_row_notfound BOOLEAN := FALSE;
1593 BEGIN
1594 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1595 p_init_msg_list,
1596 '_PVT',
1597 x_return_status);
1598 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1599 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1600 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1601 RAISE OKL_API.G_EXCEPTION_ERROR;
1602 END IF;
1603 BEGIN
1604 OPEN lock_csr(p_cld_rec);
1605 FETCH lock_csr INTO l_object_version_number;
1606 l_row_notfound := lock_csr%NOTFOUND;
1607 CLOSE lock_csr;
1608 EXCEPTION
1609 WHEN E_Resource_Busy THEN
1610 IF (lock_csr%ISOPEN) THEN
1611 CLOSE lock_csr;
1612 END IF;
1613 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1614 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1615 END;
1616
1617 IF ( l_row_notfound ) THEN
1618 OPEN lchk_csr(p_cld_rec);
1619 FETCH lchk_csr INTO lc_object_version_number;
1620 lc_row_notfound := lchk_csr%NOTFOUND;
1621 CLOSE lchk_csr;
1622 END IF;
1623 IF (lc_row_notfound) THEN
1624 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1625 RAISE OKL_API.G_EXCEPTION_ERROR;
1626 ELSIF lc_object_version_number > p_cld_rec.object_version_number THEN
1627 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1628 RAISE OKL_API.G_EXCEPTION_ERROR;
1629 ELSIF lc_object_version_number <> p_cld_rec.object_version_number THEN
1630 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1631 RAISE OKL_API.G_EXCEPTION_ERROR;
1632 ELSIF lc_object_version_number = -1 THEN
1633 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1634 RAISE OKL_API.G_EXCEPTION_ERROR;
1635 END IF;
1636 x_return_status := l_return_status;
1637 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1638 EXCEPTION
1639 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1640 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1641 (
1642 l_api_name,
1643 G_PKG_NAME,
1644 'OKL_API.G_RET_STS_ERROR',
1645 x_msg_count,
1646 x_msg_data,
1647 '_PVT'
1648 );
1649 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1650 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1651 (
1652 l_api_name,
1653 G_PKG_NAME,
1654 'OKL_API.G_RET_STS_UNEXP_ERROR',
1655 x_msg_count,
1656 x_msg_data,
1657 '_PVT'
1658 );
1659 WHEN OTHERS THEN
1660 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1661 (
1662 l_api_name,
1663 G_PKG_NAME,
1664 'OTHERS',
1665 x_msg_count,
1666 x_msg_data,
1667 '_PVT'
1668 );
1669 END lock_row;
1670 -------------------------------------------
1671 -- lock_row for: OKL_CHECKLIST_DETAILS_V --
1672 -------------------------------------------
1673 PROCEDURE lock_row(
1674 p_api_version IN NUMBER,
1675 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1676 x_return_status OUT NOCOPY VARCHAR2,
1677 x_msg_count OUT NOCOPY NUMBER,
1678 x_msg_data OUT NOCOPY VARCHAR2,
1679 p_cldv_rec IN cldv_rec_type) IS
1680
1681 l_api_version CONSTANT NUMBER := 1;
1682 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1683 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1684 l_cld_rec cld_rec_type;
1685 BEGIN
1686 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1687 G_PKG_NAME,
1688 p_init_msg_list,
1689 l_api_version,
1690 p_api_version,
1691 '_PVT',
1692 x_return_status);
1693 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1694 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1695 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1696 RAISE OKL_API.G_EXCEPTION_ERROR;
1697 END IF;
1698 -----------------------------------------
1699 -- Move VIEW record to "Child" records --
1700 -----------------------------------------
1701 migrate(p_cldv_rec, l_cld_rec);
1702 ---------------------------------------------
1703 -- Call the LOCK_ROW for each child record --
1704 ---------------------------------------------
1705 lock_row(
1706 p_init_msg_list,
1707 l_return_status,
1708 x_msg_count,
1709 x_msg_data,
1710 l_cld_rec
1711 );
1712 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1713 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1714 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1715 RAISE OKL_API.G_EXCEPTION_ERROR;
1716 END IF;
1717 x_return_status := l_return_status;
1718 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1719 EXCEPTION
1720 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1721 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1722 (
1723 l_api_name,
1724 G_PKG_NAME,
1725 'OKL_API.G_RET_STS_ERROR',
1726 x_msg_count,
1727 x_msg_data,
1728 '_PVT'
1729 );
1730 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1731 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1732 (
1733 l_api_name,
1734 G_PKG_NAME,
1735 'OKL_API.G_RET_STS_UNEXP_ERROR',
1736 x_msg_count,
1737 x_msg_data,
1738 '_PVT'
1739 );
1740 WHEN OTHERS THEN
1741 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1742 (
1743 l_api_name,
1744 G_PKG_NAME,
1745 'OTHERS',
1746 x_msg_count,
1747 x_msg_data,
1748 '_PVT'
1749 );
1750 END lock_row;
1751 --------------------------------------
1752 -- PL/SQL TBL lock_row for:CLDV_TBL --
1753 --------------------------------------
1754 PROCEDURE lock_row(
1755 p_api_version IN NUMBER,
1756 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1757 x_return_status OUT NOCOPY VARCHAR2,
1758 x_msg_count OUT NOCOPY NUMBER,
1759 x_msg_data OUT NOCOPY VARCHAR2,
1760 p_cldv_tbl IN cldv_tbl_type,
1761 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1762
1763 l_api_version CONSTANT NUMBER := 1;
1764 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1765 i NUMBER := 0;
1766 BEGIN
1767 OKL_API.init_msg_list(p_init_msg_list);
1768 -- Make sure PL/SQL table has recrods in it before passing
1769 IF (p_cldv_tbl.COUNT > 0) THEN
1770 i := p_cldv_tbl.FIRST;
1771 LOOP
1772 DECLARE
1773 l_error_rec OKL_API.ERROR_REC_TYPE;
1774 BEGIN
1775 l_error_rec.api_name := l_api_name;
1776 l_error_rec.api_package := G_PKG_NAME;
1777 l_error_rec.idx := i;
1778 lock_row(
1779 p_api_version => p_api_version,
1780 p_init_msg_list => OKL_API.G_FALSE,
1781 x_return_status => l_error_rec.error_type,
1782 x_msg_count => l_error_rec.msg_count,
1783 x_msg_data => l_error_rec.msg_data,
1784 p_cldv_rec => p_cldv_tbl(i));
1785 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1786 l_error_rec.sqlcode := SQLCODE;
1787 load_error_tbl(l_error_rec, px_error_tbl);
1788 ELSE
1789 x_msg_count := l_error_rec.msg_count;
1790 x_msg_data := l_error_rec.msg_data;
1791 END IF;
1792 EXCEPTION
1793 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1794 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1795 l_error_rec.sqlcode := SQLCODE;
1796 load_error_tbl(l_error_rec, px_error_tbl);
1797 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1798 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1799 l_error_rec.sqlcode := SQLCODE;
1800 load_error_tbl(l_error_rec, px_error_tbl);
1801 WHEN OTHERS THEN
1802 l_error_rec.error_type := 'OTHERS';
1803 l_error_rec.sqlcode := SQLCODE;
1804 load_error_tbl(l_error_rec, px_error_tbl);
1805 END;
1806 EXIT WHEN (i = p_cldv_tbl.LAST);
1807 i := p_cldv_tbl.NEXT(i);
1808 END LOOP;
1809 END IF;
1810 -- Loop through the error_tbl to find the error with the highest severity
1811 -- and return it.
1812 x_return_status := find_highest_exception(px_error_tbl);
1813 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1814 EXCEPTION
1815 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1816 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1817 (
1818 l_api_name,
1819 G_PKG_NAME,
1820 'OKL_API.G_RET_STS_ERROR',
1821 x_msg_count,
1822 x_msg_data,
1823 '_PVT'
1824 );
1825 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1826 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1827 (
1828 l_api_name,
1829 G_PKG_NAME,
1830 'OKL_API.G_RET_STS_UNEXP_ERROR',
1831 x_msg_count,
1832 x_msg_data,
1833 '_PVT'
1834 );
1835 WHEN OTHERS THEN
1836 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1837 (
1838 l_api_name,
1839 G_PKG_NAME,
1840 'OTHERS',
1841 x_msg_count,
1842 x_msg_data,
1843 '_PVT'
1844 );
1845 END lock_row;
1846 --------------------------------------
1847 -- PL/SQL TBL lock_row for:CLDV_TBL --
1848 --------------------------------------
1849 PROCEDURE lock_row(
1850 p_api_version IN NUMBER,
1851 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1852 x_return_status OUT NOCOPY VARCHAR2,
1853 x_msg_count OUT NOCOPY NUMBER,
1854 x_msg_data OUT NOCOPY VARCHAR2,
1855 p_cldv_tbl IN cldv_tbl_type) IS
1856
1857 l_api_version CONSTANT NUMBER := 1;
1858 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1859 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1860 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1861 BEGIN
1862 OKL_API.init_msg_list(p_init_msg_list);
1863 -- Make sure PL/SQL table has recrods in it before passing
1864 IF (p_cldv_tbl.COUNT > 0) THEN
1865 lock_row(
1866 p_api_version => p_api_version,
1867 p_init_msg_list => OKL_API.G_FALSE,
1868 x_return_status => x_return_status,
1869 x_msg_count => x_msg_count,
1870 x_msg_data => x_msg_data,
1871 p_cldv_tbl => p_cldv_tbl,
1872 px_error_tbl => l_error_tbl);
1873 END IF;
1874 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1875 EXCEPTION
1876 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1877 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1878 (
1879 l_api_name,
1880 G_PKG_NAME,
1881 'OKL_API.G_RET_STS_ERROR',
1882 x_msg_count,
1883 x_msg_data,
1884 '_PVT'
1885 );
1886 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1887 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1888 (
1889 l_api_name,
1890 G_PKG_NAME,
1891 'OKL_API.G_RET_STS_UNEXP_ERROR',
1892 x_msg_count,
1893 x_msg_data,
1894 '_PVT'
1895 );
1896 WHEN OTHERS THEN
1897 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1898 (
1899 l_api_name,
1900 G_PKG_NAME,
1901 'OTHERS',
1902 x_msg_count,
1903 x_msg_data,
1904 '_PVT'
1905 );
1906 END lock_row;
1907 ---------------------------------------------------------------------------
1908 -- PROCEDURE update_row
1909 ---------------------------------------------------------------------------
1910 ------------------------------------------
1911 -- update_row for:OKL_CHECKLIST_DETAILS --
1912 ------------------------------------------
1913 PROCEDURE update_row(
1914 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1915 x_return_status OUT NOCOPY VARCHAR2,
1916 x_msg_count OUT NOCOPY NUMBER,
1917 x_msg_data OUT NOCOPY VARCHAR2,
1918 p_cld_rec IN cld_rec_type,
1919 x_cld_rec OUT NOCOPY cld_rec_type) IS
1920
1921 l_api_version CONSTANT NUMBER := 1;
1922 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1923 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1924 l_cld_rec cld_rec_type := p_cld_rec;
1925 l_def_cld_rec cld_rec_type;
1926 l_row_notfound BOOLEAN := TRUE;
1927 ----------------------------------
1928 -- FUNCTION populate_new_record --
1929 ----------------------------------
1930 FUNCTION populate_new_record (
1931 p_cld_rec IN cld_rec_type,
1932 x_cld_rec OUT NOCOPY cld_rec_type
1933 ) RETURN VARCHAR2 IS
1934 l_cld_rec cld_rec_type;
1935 l_row_notfound BOOLEAN := TRUE;
1936 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1937 BEGIN
1938 x_cld_rec := p_cld_rec;
1939 -- Get current database values
1940 l_cld_rec := get_rec(p_cld_rec, l_return_status);
1941 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1942 IF (x_cld_rec.id = OKL_API.G_MISS_NUM)
1943 THEN
1944 x_cld_rec.id := l_cld_rec.id;
1945 END IF;
1946 IF (x_cld_rec.object_version_number = OKL_API.G_MISS_NUM)
1947 THEN
1948 x_cld_rec.object_version_number := l_cld_rec.object_version_number;
1949 END IF;
1950 IF (x_cld_rec.ckl_id = OKL_API.G_MISS_NUM)
1951 THEN
1952 x_cld_rec.ckl_id := l_cld_rec.ckl_id;
1953 END IF;
1954 IF (x_cld_rec.todo_item_code = OKL_API.G_MISS_CHAR)
1955 THEN
1956 x_cld_rec.todo_item_code := l_cld_rec.todo_item_code;
1957 END IF;
1958 IF (x_cld_rec.attribute_category = OKL_API.G_MISS_CHAR)
1959 THEN
1960 x_cld_rec.attribute_category := l_cld_rec.attribute_category;
1961 END IF;
1962 IF (x_cld_rec.attribute1 = OKL_API.G_MISS_CHAR)
1963 THEN
1964 x_cld_rec.attribute1 := l_cld_rec.attribute1;
1965 END IF;
1966 IF (x_cld_rec.attribute2 = OKL_API.G_MISS_CHAR)
1967 THEN
1968 x_cld_rec.attribute2 := l_cld_rec.attribute2;
1969 END IF;
1970 IF (x_cld_rec.attribute3 = OKL_API.G_MISS_CHAR)
1971 THEN
1972 x_cld_rec.attribute3 := l_cld_rec.attribute3;
1973 END IF;
1974 IF (x_cld_rec.attribute4 = OKL_API.G_MISS_CHAR)
1975 THEN
1976 x_cld_rec.attribute4 := l_cld_rec.attribute4;
1977 END IF;
1978 IF (x_cld_rec.attribute5 = OKL_API.G_MISS_CHAR)
1979 THEN
1980 x_cld_rec.attribute5 := l_cld_rec.attribute5;
1981 END IF;
1982 IF (x_cld_rec.attribute6 = OKL_API.G_MISS_CHAR)
1983 THEN
1984 x_cld_rec.attribute6 := l_cld_rec.attribute6;
1985 END IF;
1986 IF (x_cld_rec.attribute7 = OKL_API.G_MISS_CHAR)
1987 THEN
1988 x_cld_rec.attribute7 := l_cld_rec.attribute7;
1989 END IF;
1990 IF (x_cld_rec.attribute8 = OKL_API.G_MISS_CHAR)
1991 THEN
1992 x_cld_rec.attribute8 := l_cld_rec.attribute8;
1993 END IF;
1994 IF (x_cld_rec.attribute9 = OKL_API.G_MISS_CHAR)
1995 THEN
1996 x_cld_rec.attribute9 := l_cld_rec.attribute9;
1997 END IF;
1998 IF (x_cld_rec.attribute10 = OKL_API.G_MISS_CHAR)
1999 THEN
2000 x_cld_rec.attribute10 := l_cld_rec.attribute10;
2001 END IF;
2002 IF (x_cld_rec.attribute11 = OKL_API.G_MISS_CHAR)
2003 THEN
2004 x_cld_rec.attribute11 := l_cld_rec.attribute11;
2005 END IF;
2006 IF (x_cld_rec.attribute12 = OKL_API.G_MISS_CHAR)
2007 THEN
2008 x_cld_rec.attribute12 := l_cld_rec.attribute12;
2009 END IF;
2010 IF (x_cld_rec.attribute13 = OKL_API.G_MISS_CHAR)
2011 THEN
2012 x_cld_rec.attribute13 := l_cld_rec.attribute13;
2013 END IF;
2014 IF (x_cld_rec.attribute14 = OKL_API.G_MISS_CHAR)
2015 THEN
2016 x_cld_rec.attribute14 := l_cld_rec.attribute14;
2017 END IF;
2018 IF (x_cld_rec.attribute15 = OKL_API.G_MISS_CHAR)
2019 THEN
2020 x_cld_rec.attribute15 := l_cld_rec.attribute15;
2021 END IF;
2022 IF (x_cld_rec.org_id = OKL_API.G_MISS_NUM)
2023 THEN
2024 x_cld_rec.org_id := l_cld_rec.org_id;
2025 END IF;
2026 IF (x_cld_rec.request_id = OKL_API.G_MISS_NUM)
2027 THEN
2028 x_cld_rec.request_id := l_cld_rec.request_id;
2029 END IF;
2030 IF (x_cld_rec.program_application_id = OKL_API.G_MISS_NUM)
2031 THEN
2032 x_cld_rec.program_application_id := l_cld_rec.program_application_id;
2033 END IF;
2034 IF (x_cld_rec.program_id = OKL_API.G_MISS_NUM)
2035 THEN
2036 x_cld_rec.program_id := l_cld_rec.program_id;
2037 END IF;
2038 IF (x_cld_rec.program_update_date = OKL_API.G_MISS_DATE)
2039 THEN
2040 x_cld_rec.program_update_date := l_cld_rec.program_update_date;
2041 END IF;
2042 IF (x_cld_rec.created_by = OKL_API.G_MISS_NUM)
2043 THEN
2044 x_cld_rec.created_by := l_cld_rec.created_by;
2045 END IF;
2046 IF (x_cld_rec.creation_date = OKL_API.G_MISS_DATE)
2047 THEN
2048 x_cld_rec.creation_date := l_cld_rec.creation_date;
2049 END IF;
2050 IF (x_cld_rec.last_updated_by = OKL_API.G_MISS_NUM)
2051 THEN
2052 x_cld_rec.last_updated_by := l_cld_rec.last_updated_by;
2053 END IF;
2054 IF (x_cld_rec.last_update_date = OKL_API.G_MISS_DATE)
2055 THEN
2056 x_cld_rec.last_update_date := l_cld_rec.last_update_date;
2057 END IF;
2058 IF (x_cld_rec.last_update_login = OKL_API.G_MISS_NUM)
2059 THEN
2060 x_cld_rec.last_update_login := l_cld_rec.last_update_login;
2061 END IF;
2062
2063 -- start: Apr 25, 2005 cklee: Modification for okl.h
2064 IF (x_cld_rec.MANDATORY_FLAG = OKL_API.G_MISS_CHAR)
2065 THEN
2066 x_cld_rec.MANDATORY_FLAG := l_cld_rec.MANDATORY_FLAG;
2067 END IF;
2068 IF (x_cld_rec.USER_COMPLETE_FLAG = OKL_API.G_MISS_CHAR)
2069 THEN
2070 x_cld_rec.USER_COMPLETE_FLAG := l_cld_rec.USER_COMPLETE_FLAG;
2071 END IF;
2072 IF (x_cld_rec.ADMIN_NOTE = OKL_API.G_MISS_CHAR)
2073 THEN
2074 x_cld_rec.ADMIN_NOTE := l_cld_rec.ADMIN_NOTE;
2075 END IF;
2076 IF (x_cld_rec.USER_NOTE = OKL_API.G_MISS_CHAR)
2077 THEN
2078 x_cld_rec.USER_NOTE := l_cld_rec.USER_NOTE;
2079 END IF;
2080 IF (x_cld_rec.DNZ_CHECKLIST_OBJ_ID = OKL_API.G_MISS_NUM)
2081 THEN
2082 x_cld_rec.DNZ_CHECKLIST_OBJ_ID := l_cld_rec.DNZ_CHECKLIST_OBJ_ID;
2083 END IF;
2084 IF (x_cld_rec.FUNCTION_ID = OKL_API.G_MISS_NUM)
2085 THEN
2086 x_cld_rec.FUNCTION_ID := l_cld_rec.FUNCTION_ID;
2087 END IF;
2088 IF (x_cld_rec.FUNCTION_VALIDATE_RSTS = OKL_API.G_MISS_CHAR)
2089 THEN
2090 x_cld_rec.FUNCTION_VALIDATE_RSTS := l_cld_rec.FUNCTION_VALIDATE_RSTS;
2091 END IF;
2092 IF (x_cld_rec.FUNCTION_VALIDATE_MSG = OKL_API.G_MISS_CHAR)
2093 THEN
2094 x_cld_rec.FUNCTION_VALIDATE_MSG := l_cld_rec.FUNCTION_VALIDATE_MSG;
2095 END IF;
2096 IF (x_cld_rec.INST_CHECKLIST_TYPE = OKL_API.G_MISS_CHAR)
2097 THEN
2098 x_cld_rec.INST_CHECKLIST_TYPE := l_cld_rec.INST_CHECKLIST_TYPE;
2099 END IF;
2100 -- end: Apr 25, 2005 cklee: Modification for okl.h
2101 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
2102 IF (x_cld_rec.APPEAL_FLAG = OKL_API.G_MISS_CHAR)
2103 THEN
2104 x_cld_rec.APPEAL_FLAG := l_cld_rec.APPEAL_FLAG;
2105 END IF;
2106 END IF;
2107 RETURN(l_return_status);
2108 END populate_new_record;
2109 ----------------------------------------------
2110 -- Set_Attributes for:OKL_CHECKLIST_DETAILS --
2111 ----------------------------------------------
2112 FUNCTION Set_Attributes (
2113 p_cld_rec IN cld_rec_type,
2114 x_cld_rec OUT NOCOPY cld_rec_type
2115 ) RETURN VARCHAR2 IS
2116 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2117 BEGIN
2118 x_cld_rec := p_cld_rec;
2119 x_cld_rec.OBJECT_VERSION_NUMBER := p_cld_rec.OBJECT_VERSION_NUMBER + 1;
2120 RETURN(l_return_status);
2121 END Set_Attributes;
2122 BEGIN
2123 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2124 p_init_msg_list,
2125 '_PVT',
2126 x_return_status);
2127 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2128 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2129 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2130 RAISE OKL_API.G_EXCEPTION_ERROR;
2131 END IF;
2132 --- Setting item attributes
2133 l_return_status := Set_Attributes(
2134 p_cld_rec, -- IN
2135 l_cld_rec); -- OUT
2136 --- If any errors happen abort API
2137 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2138 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2139 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2140 RAISE OKL_API.G_EXCEPTION_ERROR;
2141 END IF;
2142 l_return_status := populate_new_record(l_cld_rec, l_def_cld_rec);
2143 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2144 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2145 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2146 RAISE OKL_API.G_EXCEPTION_ERROR;
2147 END IF;
2148 UPDATE OKL_CHECKLIST_DETAILS
2149 SET OBJECT_VERSION_NUMBER = l_def_cld_rec.object_version_number,
2150 CKL_ID = l_def_cld_rec.ckl_id,
2151 TODO_ITEM_CODE = l_def_cld_rec.todo_item_code,
2152 ATTRIBUTE_CATEGORY = l_def_cld_rec.attribute_category,
2153 ATTRIBUTE1 = l_def_cld_rec.attribute1,
2154 ATTRIBUTE2 = l_def_cld_rec.attribute2,
2155 ATTRIBUTE3 = l_def_cld_rec.attribute3,
2156 ATTRIBUTE4 = l_def_cld_rec.attribute4,
2157 ATTRIBUTE5 = l_def_cld_rec.attribute5,
2158 ATTRIBUTE6 = l_def_cld_rec.attribute6,
2159 ATTRIBUTE7 = l_def_cld_rec.attribute7,
2160 ATTRIBUTE8 = l_def_cld_rec.attribute8,
2161 ATTRIBUTE9 = l_def_cld_rec.attribute9,
2162 ATTRIBUTE10 = l_def_cld_rec.attribute10,
2163 ATTRIBUTE11 = l_def_cld_rec.attribute11,
2164 ATTRIBUTE12 = l_def_cld_rec.attribute12,
2165 ATTRIBUTE13 = l_def_cld_rec.attribute13,
2166 ATTRIBUTE14 = l_def_cld_rec.attribute14,
2167 ATTRIBUTE15 = l_def_cld_rec.attribute15,
2168 ORG_ID = l_def_cld_rec.org_id,
2169 REQUEST_ID = l_def_cld_rec.request_id,
2170 PROGRAM_APPLICATION_ID = l_def_cld_rec.program_application_id,
2171 PROGRAM_ID = l_def_cld_rec.program_id,
2172 PROGRAM_UPDATE_DATE = l_def_cld_rec.program_update_date,
2173 CREATED_BY = l_def_cld_rec.created_by,
2174 CREATION_DATE = l_def_cld_rec.creation_date,
2175 LAST_UPDATED_BY = l_def_cld_rec.last_updated_by,
2176 LAST_UPDATE_DATE = l_def_cld_rec.last_update_date,
2177 LAST_UPDATE_LOGIN = l_def_cld_rec.last_update_login,
2178 -- start: Apr 25, 2005 cklee: Modification for okl.h
2179 MANDATORY_FLAG = l_def_cld_rec.MANDATORY_FLAG,
2180 USER_COMPLETE_FLAG = l_def_cld_rec.USER_COMPLETE_FLAG,
2181 ADMIN_NOTE = l_def_cld_rec.ADMIN_NOTE,
2182 USER_NOTE = l_def_cld_rec.USER_NOTE,
2183 DNZ_CHECKLIST_OBJ_ID = l_def_cld_rec.DNZ_CHECKLIST_OBJ_ID,
2184 FUNCTION_ID = l_def_cld_rec.FUNCTION_ID,
2185 FUNCTION_VALIDATE_RSTS = l_def_cld_rec.FUNCTION_VALIDATE_RSTS,
2186 FUNCTION_VALIDATE_MSG = l_def_cld_rec.FUNCTION_VALIDATE_MSG,
2187 INST_CHECKLIST_TYPE = l_def_cld_rec.INST_CHECKLIST_TYPE
2188 -- end: Apr 25, 2005 cklee: Modification for okl.h
2189 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
2190 ,APPEAL_FLAG = l_def_cld_rec.APPEAL_FLAG
2191 WHERE ID = l_def_cld_rec.id;
2192
2193 x_cld_rec := l_cld_rec;
2194 x_return_status := l_return_status;
2195 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2196 EXCEPTION
2197 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2198 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2199 (
2200 l_api_name,
2201 G_PKG_NAME,
2202 'OKL_API.G_RET_STS_ERROR',
2203 x_msg_count,
2204 x_msg_data,
2205 '_PVT'
2206 );
2207 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2208 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2209 (
2210 l_api_name,
2211 G_PKG_NAME,
2212 'OKL_API.G_RET_STS_UNEXP_ERROR',
2213 x_msg_count,
2214 x_msg_data,
2215 '_PVT'
2216 );
2217 WHEN OTHERS THEN
2218 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2219 (
2220 l_api_name,
2221 G_PKG_NAME,
2222 'OTHERS',
2223 x_msg_count,
2224 x_msg_data,
2225 '_PVT'
2226 );
2227 END update_row;
2228 --------------------------------------------
2229 -- update_row for:OKL_CHECKLIST_DETAILS_V --
2230 --------------------------------------------
2231 PROCEDURE update_row(
2232 p_api_version IN NUMBER,
2233 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2234 x_return_status OUT NOCOPY VARCHAR2,
2235 x_msg_count OUT NOCOPY NUMBER,
2236 x_msg_data OUT NOCOPY VARCHAR2,
2237 p_cldv_rec IN cldv_rec_type,
2238 x_cldv_rec OUT NOCOPY cldv_rec_type) IS
2239
2240 l_api_version CONSTANT NUMBER := 1;
2241 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2242 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2243 l_cldv_rec cldv_rec_type := p_cldv_rec;
2244 l_def_cldv_rec cldv_rec_type;
2245 l_db_cldv_rec cldv_rec_type;
2246 l_cld_rec cld_rec_type;
2247 lx_cld_rec cld_rec_type;
2248 -------------------------------
2249 -- FUNCTION fill_who_columns --
2250 -------------------------------
2251 FUNCTION fill_who_columns (
2252 p_cldv_rec IN cldv_rec_type
2253 ) RETURN cldv_rec_type IS
2254 l_cldv_rec cldv_rec_type := p_cldv_rec;
2255 BEGIN
2256 l_cldv_rec.LAST_UPDATE_DATE := SYSDATE;
2257 l_cldv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2258 l_cldv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2259 RETURN(l_cldv_rec);
2260 END fill_who_columns;
2261 ----------------------------------
2262 -- FUNCTION populate_new_record --
2263 ----------------------------------
2264 FUNCTION populate_new_record (
2265 p_cldv_rec IN cldv_rec_type,
2266 x_cldv_rec OUT NOCOPY cldv_rec_type
2267 ) RETURN VARCHAR2 IS
2268 l_row_notfound BOOLEAN := TRUE;
2269 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2270 BEGIN
2271 x_cldv_rec := p_cldv_rec;
2272 -- Get current database values
2273 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2274 -- so it may be verified through LOCK_ROW.
2275 l_db_cldv_rec := get_rec(p_cldv_rec, l_return_status);
2276 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2277 IF (x_cldv_rec.id = OKL_API.G_MISS_NUM)
2278 THEN
2279 x_cldv_rec.id := l_db_cldv_rec.id;
2280 END IF;
2281 IF (x_cldv_rec.ckl_id = OKL_API.G_MISS_NUM)
2282 THEN
2283 x_cldv_rec.ckl_id := l_db_cldv_rec.ckl_id;
2284 END IF;
2285 IF (x_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
2286 THEN
2287 x_cldv_rec.todo_item_code := l_db_cldv_rec.todo_item_code;
2288 END IF;
2289 IF (x_cldv_rec.attribute_category = OKL_API.G_MISS_CHAR)
2290 THEN
2291 x_cldv_rec.attribute_category := l_db_cldv_rec.attribute_category;
2292 END IF;
2293 IF (x_cldv_rec.attribute1 = OKL_API.G_MISS_CHAR)
2294 THEN
2295 x_cldv_rec.attribute1 := l_db_cldv_rec.attribute1;
2296 END IF;
2297 IF (x_cldv_rec.attribute2 = OKL_API.G_MISS_CHAR)
2298 THEN
2299 x_cldv_rec.attribute2 := l_db_cldv_rec.attribute2;
2300 END IF;
2301 IF (x_cldv_rec.attribute3 = OKL_API.G_MISS_CHAR)
2302 THEN
2303 x_cldv_rec.attribute3 := l_db_cldv_rec.attribute3;
2304 END IF;
2305 IF (x_cldv_rec.attribute4 = OKL_API.G_MISS_CHAR)
2306 THEN
2307 x_cldv_rec.attribute4 := l_db_cldv_rec.attribute4;
2308 END IF;
2309 IF (x_cldv_rec.attribute5 = OKL_API.G_MISS_CHAR)
2310 THEN
2311 x_cldv_rec.attribute5 := l_db_cldv_rec.attribute5;
2312 END IF;
2313 IF (x_cldv_rec.attribute6 = OKL_API.G_MISS_CHAR)
2314 THEN
2315 x_cldv_rec.attribute6 := l_db_cldv_rec.attribute6;
2316 END IF;
2317 IF (x_cldv_rec.attribute7 = OKL_API.G_MISS_CHAR)
2318 THEN
2319 x_cldv_rec.attribute7 := l_db_cldv_rec.attribute7;
2320 END IF;
2321 IF (x_cldv_rec.attribute8 = OKL_API.G_MISS_CHAR)
2322 THEN
2323 x_cldv_rec.attribute8 := l_db_cldv_rec.attribute8;
2324 END IF;
2325 IF (x_cldv_rec.attribute9 = OKL_API.G_MISS_CHAR)
2326 THEN
2327 x_cldv_rec.attribute9 := l_db_cldv_rec.attribute9;
2328 END IF;
2329 IF (x_cldv_rec.attribute10 = OKL_API.G_MISS_CHAR)
2330 THEN
2331 x_cldv_rec.attribute10 := l_db_cldv_rec.attribute10;
2332 END IF;
2333 IF (x_cldv_rec.attribute11 = OKL_API.G_MISS_CHAR)
2334 THEN
2335 x_cldv_rec.attribute11 := l_db_cldv_rec.attribute11;
2336 END IF;
2337 IF (x_cldv_rec.attribute12 = OKL_API.G_MISS_CHAR)
2338 THEN
2339 x_cldv_rec.attribute12 := l_db_cldv_rec.attribute12;
2340 END IF;
2341 IF (x_cldv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2342 THEN
2343 x_cldv_rec.attribute13 := l_db_cldv_rec.attribute13;
2344 END IF;
2345 IF (x_cldv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2346 THEN
2347 x_cldv_rec.attribute14 := l_db_cldv_rec.attribute14;
2348 END IF;
2349 IF (x_cldv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2350 THEN
2351 x_cldv_rec.attribute15 := l_db_cldv_rec.attribute15;
2352 END IF;
2353 IF (x_cldv_rec.org_id = OKL_API.G_MISS_NUM)
2354 THEN
2355 x_cldv_rec.org_id := l_db_cldv_rec.org_id;
2356 END IF;
2357 IF (x_cldv_rec.request_id = OKL_API.G_MISS_NUM)
2358 THEN
2359 x_cldv_rec.request_id := l_db_cldv_rec.request_id;
2360 END IF;
2361 IF (x_cldv_rec.program_application_id = OKL_API.G_MISS_NUM)
2362 THEN
2363 x_cldv_rec.program_application_id := l_db_cldv_rec.program_application_id;
2364 END IF;
2365 IF (x_cldv_rec.program_id = OKL_API.G_MISS_NUM)
2366 THEN
2367 x_cldv_rec.program_id := l_db_cldv_rec.program_id;
2368 END IF;
2369 IF (x_cldv_rec.program_update_date = OKL_API.G_MISS_DATE)
2370 THEN
2371 x_cldv_rec.program_update_date := l_db_cldv_rec.program_update_date;
2372 END IF;
2373 IF (x_cldv_rec.created_by = OKL_API.G_MISS_NUM)
2374 THEN
2375 x_cldv_rec.created_by := l_db_cldv_rec.created_by;
2376 END IF;
2377 IF (x_cldv_rec.creation_date = OKL_API.G_MISS_DATE)
2378 THEN
2379 x_cldv_rec.creation_date := l_db_cldv_rec.creation_date;
2380 END IF;
2381 IF (x_cldv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2382 THEN
2383 x_cldv_rec.last_updated_by := l_db_cldv_rec.last_updated_by;
2384 END IF;
2385 IF (x_cldv_rec.last_update_date = OKL_API.G_MISS_DATE)
2386 THEN
2387 x_cldv_rec.last_update_date := l_db_cldv_rec.last_update_date;
2388 END IF;
2389 IF (x_cldv_rec.last_update_login = OKL_API.G_MISS_NUM)
2390 THEN
2391 x_cldv_rec.last_update_login := l_db_cldv_rec.last_update_login;
2392 END IF;
2393
2394 -- start: Apr 25, 2005 cklee: Modification for okl.h
2395 IF (x_cldv_rec.MANDATORY_FLAG = OKL_API.G_MISS_CHAR)
2396 THEN
2397 x_cldv_rec.MANDATORY_FLAG := l_db_cldv_rec.MANDATORY_FLAG;
2398 END IF;
2399 IF (x_cldv_rec.USER_COMPLETE_FLAG = OKL_API.G_MISS_CHAR)
2400 THEN
2401 x_cldv_rec.USER_COMPLETE_FLAG := l_db_cldv_rec.USER_COMPLETE_FLAG;
2402 END IF;
2403 IF (x_cldv_rec.ADMIN_NOTE = OKL_API.G_MISS_CHAR)
2404 THEN
2405 x_cldv_rec.ADMIN_NOTE := l_db_cldv_rec.ADMIN_NOTE;
2406 END IF;
2407 IF (x_cldv_rec.USER_NOTE = OKL_API.G_MISS_CHAR)
2408 THEN
2409 x_cldv_rec.USER_NOTE := l_db_cldv_rec.USER_NOTE;
2410 END IF;
2411 IF (x_cldv_rec.DNZ_CHECKLIST_OBJ_ID = OKL_API.G_MISS_NUM)
2412 THEN
2413 x_cldv_rec.DNZ_CHECKLIST_OBJ_ID := l_db_cldv_rec.DNZ_CHECKLIST_OBJ_ID;
2414 END IF;
2415 IF (x_cldv_rec.FUNCTION_ID = OKL_API.G_MISS_NUM)
2416 THEN
2417 x_cldv_rec.FUNCTION_ID := l_db_cldv_rec.FUNCTION_ID;
2418 END IF;
2419 IF (x_cldv_rec.FUNCTION_VALIDATE_RSTS = OKL_API.G_MISS_CHAR)
2420 THEN
2421 x_cldv_rec.FUNCTION_VALIDATE_RSTS := l_db_cldv_rec.FUNCTION_VALIDATE_RSTS;
2422 END IF;
2423 IF (x_cldv_rec.FUNCTION_VALIDATE_MSG = OKL_API.G_MISS_CHAR)
2424 THEN
2425 x_cldv_rec.FUNCTION_VALIDATE_MSG := l_db_cldv_rec.FUNCTION_VALIDATE_MSG;
2426 END IF;
2427 IF (x_cldv_rec.INST_CHECKLIST_TYPE = OKL_API.G_MISS_CHAR)
2428 THEN
2429 x_cldv_rec.INST_CHECKLIST_TYPE := l_db_cldv_rec.INST_CHECKLIST_TYPE;
2430 END IF;
2431 -- end: Apr 25, 2005 cklee: Modification for okl.h
2432 --Bug 4872271 PAGARG Appeal flag column is added to store the marking for Appeal
2433 IF (x_cldv_rec.APPEAL_FLAG = OKL_API.G_MISS_CHAR)
2434 THEN
2435 x_cldv_rec.APPEAL_FLAG := l_db_cldv_rec.APPEAL_FLAG;
2436 END IF;
2437 END IF;
2438 RETURN(l_return_status);
2439 END populate_new_record;
2440 ------------------------------------------------
2441 -- Set_Attributes for:OKL_CHECKLIST_DETAILS_V --
2442 ------------------------------------------------
2443 FUNCTION Set_Attributes (
2444 p_cldv_rec IN cldv_rec_type,
2445 x_cldv_rec OUT NOCOPY cldv_rec_type
2446 ) RETURN VARCHAR2 IS
2447 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2448 BEGIN
2449 x_cldv_rec := p_cldv_rec;
2450 RETURN(l_return_status);
2451 END Set_Attributes;
2452 BEGIN
2453 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2454 G_PKG_NAME,
2455 p_init_msg_list,
2456 l_api_version,
2457 p_api_version,
2458 '_PVT',
2459 x_return_status);
2460 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2461 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2462 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2463 RAISE OKL_API.G_EXCEPTION_ERROR;
2464 END IF;
2465 --- Setting item attributes
2466 l_return_status := Set_Attributes(
2467 p_cldv_rec, -- IN
2468 x_cldv_rec); -- OUT
2469 --- If any errors happen abort API
2470 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2471 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2472 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2473 RAISE OKL_API.G_EXCEPTION_ERROR;
2474 END IF;
2475 l_return_status := populate_new_record(l_cldv_rec, l_def_cldv_rec);
2476 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2477 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2479 RAISE OKL_API.G_EXCEPTION_ERROR;
2480 END IF;
2481 l_def_cldv_rec := fill_who_columns(l_def_cldv_rec);
2482 --- Validate all non-missing attributes (Item Level Validation)
2483 l_return_status := Validate_Attributes(l_def_cldv_rec);
2484 --- If any errors happen abort API
2485 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2486 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2487 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2488 RAISE OKL_API.G_EXCEPTION_ERROR;
2489 END IF;
2490 l_return_status := Validate_Record(l_def_cldv_rec, l_db_cldv_rec);
2491 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2492 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2493 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2494 RAISE OKL_API.G_EXCEPTION_ERROR;
2495 END IF;
2496 /*** cklee comment
2497 -- Lock the Record
2498 lock_row(
2499 p_api_version => p_api_version,
2500 p_init_msg_list => p_init_msg_list,
2501 x_return_status => l_return_status,
2502 x_msg_count => x_msg_count,
2503 x_msg_data => x_msg_data,
2504 p_cldv_rec => p_cldv_rec);
2505 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2506 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2507 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2508 RAISE OKL_API.G_EXCEPTION_ERROR;
2509 END IF;
2510 ***/
2511 -----------------------------------------
2512 -- Move VIEW record to "Child" records --
2513 -----------------------------------------
2514 migrate(l_def_cldv_rec, l_cld_rec);
2515 -----------------------------------------------
2516 -- Call the UPDATE_ROW for each child record --
2517 -----------------------------------------------
2518 update_row(
2519 p_init_msg_list,
2520 l_return_status,
2521 x_msg_count,
2522 x_msg_data,
2523 l_cld_rec,
2524 lx_cld_rec
2525 );
2526 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2527 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2529 RAISE OKL_API.G_EXCEPTION_ERROR;
2530 END IF;
2531 migrate(lx_cld_rec, l_def_cldv_rec);
2532 x_cldv_rec := l_def_cldv_rec;
2533 x_return_status := l_return_status;
2534 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2535 EXCEPTION
2536 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2537 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2538 (
2539 l_api_name,
2540 G_PKG_NAME,
2541 'OKL_API.G_RET_STS_ERROR',
2542 x_msg_count,
2543 x_msg_data,
2544 '_PVT'
2545 );
2546 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2547 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2548 (
2549 l_api_name,
2550 G_PKG_NAME,
2551 'OKL_API.G_RET_STS_UNEXP_ERROR',
2552 x_msg_count,
2553 x_msg_data,
2554 '_PVT'
2555 );
2556 WHEN OTHERS THEN
2557 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2558 (
2559 l_api_name,
2560 G_PKG_NAME,
2561 'OTHERS',
2562 x_msg_count,
2563 x_msg_data,
2564 '_PVT'
2565 );
2566 END update_row;
2567 ----------------------------------------
2568 -- PL/SQL TBL update_row for:cldv_tbl --
2569 ----------------------------------------
2570 PROCEDURE update_row(
2571 p_api_version IN NUMBER,
2572 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2573 x_return_status OUT NOCOPY VARCHAR2,
2574 x_msg_count OUT NOCOPY NUMBER,
2575 x_msg_data OUT NOCOPY VARCHAR2,
2576 p_cldv_tbl IN cldv_tbl_type,
2577 x_cldv_tbl OUT NOCOPY cldv_tbl_type,
2578 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2579
2580 l_api_version CONSTANT NUMBER := 1;
2581 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2582 i NUMBER := 0;
2583 BEGIN
2584 OKL_API.init_msg_list(p_init_msg_list);
2585 -- Make sure PL/SQL table has records in it before passing
2586 IF (p_cldv_tbl.COUNT > 0) THEN
2587 i := p_cldv_tbl.FIRST;
2588 LOOP
2589 DECLARE
2590 l_error_rec OKL_API.ERROR_REC_TYPE;
2591 BEGIN
2592 l_error_rec.api_name := l_api_name;
2593 l_error_rec.api_package := G_PKG_NAME;
2594 l_error_rec.idx := i;
2595 update_row (
2596 p_api_version => p_api_version,
2597 p_init_msg_list => OKL_API.G_FALSE,
2598 x_return_status => l_error_rec.error_type,
2599 x_msg_count => l_error_rec.msg_count,
2600 x_msg_data => l_error_rec.msg_data,
2601 p_cldv_rec => p_cldv_tbl(i),
2602 x_cldv_rec => x_cldv_tbl(i));
2603 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2604 l_error_rec.sqlcode := SQLCODE;
2605 load_error_tbl(l_error_rec, px_error_tbl);
2606 ELSE
2607 x_msg_count := l_error_rec.msg_count;
2608 x_msg_data := l_error_rec.msg_data;
2609 END IF;
2610 EXCEPTION
2611 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2612 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2613 l_error_rec.sqlcode := SQLCODE;
2614 load_error_tbl(l_error_rec, px_error_tbl);
2615 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2616 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2617 l_error_rec.sqlcode := SQLCODE;
2618 load_error_tbl(l_error_rec, px_error_tbl);
2619 WHEN OTHERS THEN
2620 l_error_rec.error_type := 'OTHERS';
2621 l_error_rec.sqlcode := SQLCODE;
2622 load_error_tbl(l_error_rec, px_error_tbl);
2623 END;
2624 EXIT WHEN (i = p_cldv_tbl.LAST);
2625 i := p_cldv_tbl.NEXT(i);
2626 END LOOP;
2627 END IF;
2628 -- Loop through the error_tbl to find the error with the highest severity
2629 -- and return it.
2630 x_return_status := find_highest_exception(px_error_tbl);
2631 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2632 EXCEPTION
2633 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2634 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2635 (
2636 l_api_name,
2637 G_PKG_NAME,
2638 'OKL_API.G_RET_STS_ERROR',
2639 x_msg_count,
2640 x_msg_data,
2641 '_PVT'
2642 );
2643 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2644 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2645 (
2646 l_api_name,
2647 G_PKG_NAME,
2648 'OKL_API.G_RET_STS_UNEXP_ERROR',
2649 x_msg_count,
2650 x_msg_data,
2651 '_PVT'
2652 );
2653 WHEN OTHERS THEN
2654 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2655 (
2656 l_api_name,
2657 G_PKG_NAME,
2658 'OTHERS',
2659 x_msg_count,
2660 x_msg_data,
2661 '_PVT'
2662 );
2663 END update_row;
2664
2665 ----------------------------------------
2666 -- PL/SQL TBL update_row for:CLDV_TBL --
2667 ----------------------------------------
2668 PROCEDURE update_row(
2669 p_api_version IN NUMBER,
2670 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2671 x_return_status OUT NOCOPY VARCHAR2,
2672 x_msg_count OUT NOCOPY NUMBER,
2673 x_msg_data OUT NOCOPY VARCHAR2,
2674 p_cldv_tbl IN cldv_tbl_type,
2675 x_cldv_tbl OUT NOCOPY cldv_tbl_type) IS
2676
2677 l_api_version CONSTANT NUMBER := 1;
2678 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2679 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2680 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2681 BEGIN
2682 OKL_API.init_msg_list(p_init_msg_list);
2683 -- Make sure PL/SQL table has records in it before passing
2684 IF (p_cldv_tbl.COUNT > 0) THEN
2685 update_row (
2686 p_api_version => p_api_version,
2687 p_init_msg_list => OKL_API.G_FALSE,
2688 x_return_status => x_return_status,
2689 x_msg_count => x_msg_count,
2690 x_msg_data => x_msg_data,
2691 p_cldv_tbl => p_cldv_tbl,
2692 x_cldv_tbl => x_cldv_tbl,
2693 px_error_tbl => l_error_tbl);
2694 END IF;
2695 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2696 EXCEPTION
2697 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2698 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2699 (
2700 l_api_name,
2701 G_PKG_NAME,
2702 'OKL_API.G_RET_STS_ERROR',
2703 x_msg_count,
2704 x_msg_data,
2705 '_PVT'
2706 );
2707 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2708 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2709 (
2710 l_api_name,
2711 G_PKG_NAME,
2712 'OKL_API.G_RET_STS_UNEXP_ERROR',
2713 x_msg_count,
2714 x_msg_data,
2715 '_PVT'
2716 );
2717 WHEN OTHERS THEN
2718 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2719 (
2720 l_api_name,
2721 G_PKG_NAME,
2722 'OTHERS',
2723 x_msg_count,
2724 x_msg_data,
2725 '_PVT'
2726 );
2727 END update_row;
2728
2729 ---------------------------------------------------------------------------
2730 -- PROCEDURE delete_row
2731 ---------------------------------------------------------------------------
2732 ------------------------------------------
2733 -- delete_row for:OKL_CHECKLIST_DETAILS --
2734 ------------------------------------------
2735 PROCEDURE delete_row(
2736 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2737 x_return_status OUT NOCOPY VARCHAR2,
2738 x_msg_count OUT NOCOPY NUMBER,
2739 x_msg_data OUT NOCOPY VARCHAR2,
2740 p_cld_rec IN cld_rec_type) IS
2741
2742 l_api_version CONSTANT NUMBER := 1;
2743 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2744 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2745 l_cld_rec cld_rec_type := p_cld_rec;
2746 l_row_notfound BOOLEAN := TRUE;
2747 BEGIN
2748 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2749 p_init_msg_list,
2750 '_PVT',
2751 x_return_status);
2752 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2753 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2754 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2755 RAISE OKL_API.G_EXCEPTION_ERROR;
2756 END IF;
2757
2758 DELETE FROM OKL_CHECKLIST_DETAILS
2759 WHERE ID = p_cld_rec.id;
2760
2761 x_return_status := l_return_status;
2762 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2763 EXCEPTION
2764 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2765 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2766 (
2767 l_api_name,
2768 G_PKG_NAME,
2769 'OKL_API.G_RET_STS_ERROR',
2770 x_msg_count,
2771 x_msg_data,
2772 '_PVT'
2773 );
2774 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2775 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2776 (
2777 l_api_name,
2778 G_PKG_NAME,
2779 'OKL_API.G_RET_STS_UNEXP_ERROR',
2780 x_msg_count,
2781 x_msg_data,
2782 '_PVT'
2783 );
2784 WHEN OTHERS THEN
2785 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2786 (
2787 l_api_name,
2788 G_PKG_NAME,
2789 'OTHERS',
2790 x_msg_count,
2791 x_msg_data,
2792 '_PVT'
2793 );
2794 END delete_row;
2795 --------------------------------------------
2796 -- delete_row for:OKL_CHECKLIST_DETAILS_V --
2797 --------------------------------------------
2798 PROCEDURE delete_row(
2799 p_api_version IN NUMBER,
2800 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2801 x_return_status OUT NOCOPY VARCHAR2,
2802 x_msg_count OUT NOCOPY NUMBER,
2803 x_msg_data OUT NOCOPY VARCHAR2,
2804 p_cldv_rec IN cldv_rec_type) IS
2805
2806 l_api_version CONSTANT NUMBER := 1;
2807 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2808 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2809 l_cldv_rec cldv_rec_type := p_cldv_rec;
2810 l_cld_rec cld_rec_type;
2811 BEGIN
2812 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2813 G_PKG_NAME,
2814 p_init_msg_list,
2815 l_api_version,
2816 p_api_version,
2817 '_PVT',
2818 x_return_status);
2819 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2820 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2821 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2822 RAISE OKL_API.G_EXCEPTION_ERROR;
2823 END IF;
2824 -----------------------------------------
2825 -- Move VIEW record to "Child" records --
2826 -----------------------------------------
2827 migrate(l_cldv_rec, l_cld_rec);
2828 -----------------------------------------------
2829 -- Call the DELETE_ROW for each child record --
2830 -----------------------------------------------
2831 delete_row(
2832 p_init_msg_list,
2833 l_return_status,
2834 x_msg_count,
2835 x_msg_data,
2836 l_cld_rec
2837 );
2838 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2839 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2840 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2841 RAISE OKL_API.G_EXCEPTION_ERROR;
2842 END IF;
2843 x_return_status := l_return_status;
2844 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2845 EXCEPTION
2846 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2847 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2848 (
2849 l_api_name,
2850 G_PKG_NAME,
2851 'OKL_API.G_RET_STS_ERROR',
2852 x_msg_count,
2853 x_msg_data,
2854 '_PVT'
2855 );
2856 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2857 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2858 (
2859 l_api_name,
2860 G_PKG_NAME,
2861 'OKL_API.G_RET_STS_UNEXP_ERROR',
2862 x_msg_count,
2863 x_msg_data,
2864 '_PVT'
2865 );
2866 WHEN OTHERS THEN
2867 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2868 (
2869 l_api_name,
2870 G_PKG_NAME,
2871 'OTHERS',
2872 x_msg_count,
2873 x_msg_data,
2874 '_PVT'
2875 );
2876 END delete_row;
2877 -------------------------------------------------------
2878 -- PL/SQL TBL delete_row for:OKL_CHECKLIST_DETAILS_V --
2879 -------------------------------------------------------
2880 PROCEDURE delete_row(
2881 p_api_version IN NUMBER,
2882 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2883 x_return_status OUT NOCOPY VARCHAR2,
2884 x_msg_count OUT NOCOPY NUMBER,
2885 x_msg_data OUT NOCOPY VARCHAR2,
2886 p_cldv_tbl IN cldv_tbl_type,
2887 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2888
2889 l_api_version CONSTANT NUMBER := 1;
2890 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2891 i NUMBER := 0;
2892 BEGIN
2893 OKL_API.init_msg_list(p_init_msg_list);
2894 -- Make sure PL/SQL table has records in it before passing
2895 IF (p_cldv_tbl.COUNT > 0) THEN
2896 i := p_cldv_tbl.FIRST;
2897 LOOP
2898 DECLARE
2899 l_error_rec OKL_API.ERROR_REC_TYPE;
2900 BEGIN
2901 l_error_rec.api_name := l_api_name;
2902 l_error_rec.api_package := G_PKG_NAME;
2903 l_error_rec.idx := i;
2904 delete_row (
2905 p_api_version => p_api_version,
2906 p_init_msg_list => OKL_API.G_FALSE,
2907 x_return_status => l_error_rec.error_type,
2908 x_msg_count => l_error_rec.msg_count,
2909 x_msg_data => l_error_rec.msg_data,
2910 p_cldv_rec => p_cldv_tbl(i));
2911 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2912 l_error_rec.sqlcode := SQLCODE;
2913 load_error_tbl(l_error_rec, px_error_tbl);
2914 ELSE
2915 x_msg_count := l_error_rec.msg_count;
2916 x_msg_data := l_error_rec.msg_data;
2917 END IF;
2918 EXCEPTION
2919 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2920 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2921 l_error_rec.sqlcode := SQLCODE;
2922 load_error_tbl(l_error_rec, px_error_tbl);
2923 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2924 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2925 l_error_rec.sqlcode := SQLCODE;
2926 load_error_tbl(l_error_rec, px_error_tbl);
2927 WHEN OTHERS THEN
2928 l_error_rec.error_type := 'OTHERS';
2929 l_error_rec.sqlcode := SQLCODE;
2930 load_error_tbl(l_error_rec, px_error_tbl);
2931 END;
2932 EXIT WHEN (i = p_cldv_tbl.LAST);
2933 i := p_cldv_tbl.NEXT(i);
2934 END LOOP;
2935 END IF;
2936 -- Loop through the error_tbl to find the error with the highest severity
2937 -- and return it.
2938 x_return_status := find_highest_exception(px_error_tbl);
2939 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2940 EXCEPTION
2941 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2942 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2943 (
2944 l_api_name,
2945 G_PKG_NAME,
2946 'OKL_API.G_RET_STS_ERROR',
2947 x_msg_count,
2948 x_msg_data,
2949 '_PVT'
2950 );
2951 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2952 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2953 (
2954 l_api_name,
2955 G_PKG_NAME,
2956 'OKL_API.G_RET_STS_UNEXP_ERROR',
2957 x_msg_count,
2958 x_msg_data,
2959 '_PVT'
2960 );
2961 WHEN OTHERS THEN
2962 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2963 (
2964 l_api_name,
2965 G_PKG_NAME,
2966 'OTHERS',
2967 x_msg_count,
2968 x_msg_data,
2969 '_PVT'
2970 );
2971 END delete_row;
2972
2973 -------------------------------------------------------
2974 -- PL/SQL TBL delete_row for:OKL_CHECKLIST_DETAILS_V --
2975 -------------------------------------------------------
2976 PROCEDURE delete_row(
2977 p_api_version IN NUMBER,
2978 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2979 x_return_status OUT NOCOPY VARCHAR2,
2980 x_msg_count OUT NOCOPY NUMBER,
2981 x_msg_data OUT NOCOPY VARCHAR2,
2982 p_cldv_tbl IN cldv_tbl_type) IS
2983
2984 l_api_version CONSTANT NUMBER := 1;
2985 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2986 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2987 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2988 BEGIN
2989 OKL_API.init_msg_list(p_init_msg_list);
2990 -- Make sure PL/SQL table has records in it before passing
2991 IF (p_cldv_tbl.COUNT > 0) THEN
2992 delete_row (
2993 p_api_version => p_api_version,
2994 p_init_msg_list => OKL_API.G_FALSE,
2995 x_return_status => x_return_status,
2996 x_msg_count => x_msg_count,
2997 x_msg_data => x_msg_data,
2998 p_cldv_tbl => p_cldv_tbl,
2999 px_error_tbl => l_error_tbl);
3000 END IF;
3001 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3002 EXCEPTION
3003 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3004 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3005 (
3006 l_api_name,
3007 G_PKG_NAME,
3008 'OKL_API.G_RET_STS_ERROR',
3009 x_msg_count,
3010 x_msg_data,
3011 '_PVT'
3012 );
3013 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3014 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3015 (
3016 l_api_name,
3017 G_PKG_NAME,
3018 'OKL_API.G_RET_STS_UNEXP_ERROR',
3019 x_msg_count,
3020 x_msg_data,
3021 '_PVT'
3022 );
3023 WHEN OTHERS THEN
3024 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3025 (
3026 l_api_name,
3027 G_PKG_NAME,
3028 'OTHERS',
3029 x_msg_count,
3030 x_msg_data,
3031 '_PVT'
3032 );
3033 END delete_row;
3034
3035 END OKL_CLD_PVT;