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