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