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