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