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