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