25: l_hdr_id number;
26: l_indx number := 1;
27: l_amor_type varchar2(30) := G_PRINCIPAL_TYPE;
28: begin
29: x_return_status := OKL_API.G_RET_STS_SUCCESS;
30: --Insert for Detail
31: --Insert 4 records into the header for detail for Principal, Interest, Principal Balance, and Period
32: for ins_count in 1 .. G_DET_COLUMNS
33: loop
98: end loop;
99: end if;
100: x_amor_hdr_id := l_amor_hdr_id;
101: exception
102: WHEN OKL_API.G_EXCEPTION_ERROR Then
103: x_return_status := OKL_API.G_RET_STS_ERROR;
104: x_msg_data := substr(sqlerrm,1,255);
105: RAISE OKL_API.G_EXCEPTION_ERROR;
106: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
99: end if;
100: x_amor_hdr_id := l_amor_hdr_id;
101: exception
102: WHEN OKL_API.G_EXCEPTION_ERROR Then
103: x_return_status := OKL_API.G_RET_STS_ERROR;
104: x_msg_data := substr(sqlerrm,1,255);
105: RAISE OKL_API.G_EXCEPTION_ERROR;
106: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
101: exception
102: WHEN OKL_API.G_EXCEPTION_ERROR Then
103: x_return_status := OKL_API.G_RET_STS_ERROR;
104: x_msg_data := substr(sqlerrm,1,255);
105: RAISE OKL_API.G_EXCEPTION_ERROR;
106: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108: x_msg_data := substr(sqlerrm,1,255);
109: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
102: WHEN OKL_API.G_EXCEPTION_ERROR Then
103: x_return_status := OKL_API.G_RET_STS_ERROR;
104: x_msg_data := substr(sqlerrm,1,255);
105: RAISE OKL_API.G_EXCEPTION_ERROR;
106: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108: x_msg_data := substr(sqlerrm,1,255);
109: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
110: WHEN OTHERS THEN
103: x_return_status := OKL_API.G_RET_STS_ERROR;
104: x_msg_data := substr(sqlerrm,1,255);
105: RAISE OKL_API.G_EXCEPTION_ERROR;
106: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108: x_msg_data := substr(sqlerrm,1,255);
109: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
110: WHEN OTHERS THEN
111: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
105: RAISE OKL_API.G_EXCEPTION_ERROR;
106: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108: x_msg_data := substr(sqlerrm,1,255);
109: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
110: WHEN OTHERS THEN
111: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
112: x_msg_data := substr(sqlerrm,1,255);
113: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108: x_msg_data := substr(sqlerrm,1,255);
109: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
110: WHEN OTHERS THEN
111: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
112: x_msg_data := substr(sqlerrm,1,255);
113: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
114: end insert_hdr;
115:
109: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
110: WHEN OTHERS THEN
111: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
112: x_msg_data := substr(sqlerrm,1,255);
113: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
114: end insert_hdr;
115:
116:
117: -- Start of comments
154: p_summ_flag in boolean default false,
155: p_proj_interest_rate in number default NULL,p_order_by_id in
156: OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%TYPE) as
157: begin
158: x_return_status := OKL_API.G_RET_STS_SUCCESS;
159: --Following Code is performance oriented but commented since it is not supported in version prior to 11g.
160: /*
161: --Insert for Principal
162: forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
229: end if;
230: end loop;
231: exception
232: WHEN NO_DATA_FOUND then
233: x_return_status := OKL_API.G_RET_STS_ERROR;
234: x_msg_data := 'No data found in collection';
235: RAISE OKL_API.G_EXCEPTION_ERROR;
236: WHEN COLLECTION_IS_NULL then
237: x_return_status := OKL_API.G_RET_STS_ERROR;
231: exception
232: WHEN NO_DATA_FOUND then
233: x_return_status := OKL_API.G_RET_STS_ERROR;
234: x_msg_data := 'No data found in collection';
235: RAISE OKL_API.G_EXCEPTION_ERROR;
236: WHEN COLLECTION_IS_NULL then
237: x_return_status := OKL_API.G_RET_STS_ERROR;
238: x_msg_data := 'Collection is null';
239: RAISE OKL_API.G_EXCEPTION_ERROR;
233: x_return_status := OKL_API.G_RET_STS_ERROR;
234: x_msg_data := 'No data found in collection';
235: RAISE OKL_API.G_EXCEPTION_ERROR;
236: WHEN COLLECTION_IS_NULL then
237: x_return_status := OKL_API.G_RET_STS_ERROR;
238: x_msg_data := 'Collection is null';
239: RAISE OKL_API.G_EXCEPTION_ERROR;
240: WHEN OKL_API.G_EXCEPTION_ERROR Then
241: x_return_status := OKL_API.G_RET_STS_ERROR;
235: RAISE OKL_API.G_EXCEPTION_ERROR;
236: WHEN COLLECTION_IS_NULL then
237: x_return_status := OKL_API.G_RET_STS_ERROR;
238: x_msg_data := 'Collection is null';
239: RAISE OKL_API.G_EXCEPTION_ERROR;
240: WHEN OKL_API.G_EXCEPTION_ERROR Then
241: x_return_status := OKL_API.G_RET_STS_ERROR;
242: x_msg_data := substr(sqlerrm,1,255);
243: RAISE OKL_API.G_EXCEPTION_ERROR;
236: WHEN COLLECTION_IS_NULL then
237: x_return_status := OKL_API.G_RET_STS_ERROR;
238: x_msg_data := 'Collection is null';
239: RAISE OKL_API.G_EXCEPTION_ERROR;
240: WHEN OKL_API.G_EXCEPTION_ERROR Then
241: x_return_status := OKL_API.G_RET_STS_ERROR;
242: x_msg_data := substr(sqlerrm,1,255);
243: RAISE OKL_API.G_EXCEPTION_ERROR;
244: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
237: x_return_status := OKL_API.G_RET_STS_ERROR;
238: x_msg_data := 'Collection is null';
239: RAISE OKL_API.G_EXCEPTION_ERROR;
240: WHEN OKL_API.G_EXCEPTION_ERROR Then
241: x_return_status := OKL_API.G_RET_STS_ERROR;
242: x_msg_data := substr(sqlerrm,1,255);
243: RAISE OKL_API.G_EXCEPTION_ERROR;
244: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
245: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
239: RAISE OKL_API.G_EXCEPTION_ERROR;
240: WHEN OKL_API.G_EXCEPTION_ERROR Then
241: x_return_status := OKL_API.G_RET_STS_ERROR;
242: x_msg_data := substr(sqlerrm,1,255);
243: RAISE OKL_API.G_EXCEPTION_ERROR;
244: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
245: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
246: x_msg_data := substr(sqlerrm,1,255);
247: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
240: WHEN OKL_API.G_EXCEPTION_ERROR Then
241: x_return_status := OKL_API.G_RET_STS_ERROR;
242: x_msg_data := substr(sqlerrm,1,255);
243: RAISE OKL_API.G_EXCEPTION_ERROR;
244: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
245: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
246: x_msg_data := substr(sqlerrm,1,255);
247: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
248: WHEN OTHERS THEN
241: x_return_status := OKL_API.G_RET_STS_ERROR;
242: x_msg_data := substr(sqlerrm,1,255);
243: RAISE OKL_API.G_EXCEPTION_ERROR;
244: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
245: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
246: x_msg_data := substr(sqlerrm,1,255);
247: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
248: WHEN OTHERS THEN
249: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
243: RAISE OKL_API.G_EXCEPTION_ERROR;
244: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
245: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
246: x_msg_data := substr(sqlerrm,1,255);
247: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
248: WHEN OTHERS THEN
249: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
250: x_msg_data := substr(sqlerrm,1,255);
251: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
245: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
246: x_msg_data := substr(sqlerrm,1,255);
247: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
248: WHEN OTHERS THEN
249: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
250: x_msg_data := substr(sqlerrm,1,255);
251: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
252: end insert_lines;
253:
247: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
248: WHEN OTHERS THEN
249: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
250: x_msg_data := substr(sqlerrm,1,255);
251: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
252: end insert_lines;
253:
254:
255:
282: l_rec_count number := 0;
283: l_hdr_indx number := 1;
284: l_summ_det_cnt number := 0;
285: begin
286: x_return_status := OKL_API.G_RET_STS_SUCCESS;
287: --Retrieve the count of line_id for detail to be inserted. Retrieve the line_id from sequence
288: if p_det_amort_sched_tbl is not null then
289: l_rec_count := p_det_amort_sched_tbl.count;
290: end if;
328: end if;
329:
330: if l_det_amor_line_id.first = -1 then
331: x_msg_data := 'Error querying data for Detail Report';
332: raise OKL_API.G_EXCEPTION_ERROR;
333: end if;
334: --Insert Detail Schedule
335: if p_det_amort_sched_tbl is not null and p_det_amort_sched_tbl.count > 0 then
336: insert_lines(p_api_version => p_api_version,p_init_msg_list => p_init_msg_list,
346: p_proj_interest_rate_id => p_amor_hdr_id(l_hdr_indx).proj_interest_rate_id,
347: p_proj_interest_rate => p_proj_interest_rate,
348: p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_det_id);
349: end if;
350: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
351: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
352: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
353: RAISE Okl_Api.G_EXCEPTION_ERROR;
354: END IF;
347: p_proj_interest_rate => p_proj_interest_rate,
348: p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_det_id);
349: end if;
350: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
351: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
352: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
353: RAISE Okl_Api.G_EXCEPTION_ERROR;
354: END IF;
355: --Insert Summary Schedule
348: p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_det_id);
349: end if;
350: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
351: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
352: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
353: RAISE Okl_Api.G_EXCEPTION_ERROR;
354: END IF;
355: --Insert Summary Schedule
356: if p_summ_amort_sched_tbl is not null and p_summ_amort_sched_tbl.count > 0 then
349: end if;
350: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
351: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
352: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
353: RAISE Okl_Api.G_EXCEPTION_ERROR;
354: END IF;
355: --Insert Summary Schedule
356: if p_summ_amort_sched_tbl is not null and p_summ_amort_sched_tbl.count > 0 then
357: insert_lines(p_api_version => p_api_version,p_init_msg_list => p_init_msg_list,
366: p_date_to_id => p_amor_hdr_id(l_hdr_indx).date_to_summ_id,
367: p_summ_flag => true,
368: p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_summ_id);
369: end if;
370: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
371: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
372: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
373: RAISE Okl_Api.G_EXCEPTION_ERROR;
374: END IF;
367: p_summ_flag => true,
368: p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_summ_id);
369: end if;
370: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
371: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
372: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
373: RAISE Okl_Api.G_EXCEPTION_ERROR;
374: END IF;
375: exception
368: p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_summ_id);
369: end if;
370: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
371: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
372: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
373: RAISE Okl_Api.G_EXCEPTION_ERROR;
374: END IF;
375: exception
376: WHEN OKL_API.G_EXCEPTION_ERROR Then
369: end if;
370: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
371: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
372: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
373: RAISE Okl_Api.G_EXCEPTION_ERROR;
374: END IF;
375: exception
376: WHEN OKL_API.G_EXCEPTION_ERROR Then
377: x_return_status := OKL_API.G_RET_STS_ERROR;
372: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
373: RAISE Okl_Api.G_EXCEPTION_ERROR;
374: END IF;
375: exception
376: WHEN OKL_API.G_EXCEPTION_ERROR Then
377: x_return_status := OKL_API.G_RET_STS_ERROR;
378: x_msg_data := substr(sqlerrm,1,255);
379: RAISE Okl_Api.G_EXCEPTION_ERROR;
380: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
373: RAISE Okl_Api.G_EXCEPTION_ERROR;
374: END IF;
375: exception
376: WHEN OKL_API.G_EXCEPTION_ERROR Then
377: x_return_status := OKL_API.G_RET_STS_ERROR;
378: x_msg_data := substr(sqlerrm,1,255);
379: RAISE Okl_Api.G_EXCEPTION_ERROR;
380: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
381: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
375: exception
376: WHEN OKL_API.G_EXCEPTION_ERROR Then
377: x_return_status := OKL_API.G_RET_STS_ERROR;
378: x_msg_data := substr(sqlerrm,1,255);
379: RAISE Okl_Api.G_EXCEPTION_ERROR;
380: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
381: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
382: x_msg_data := substr(sqlerrm,1,255);
383: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
376: WHEN OKL_API.G_EXCEPTION_ERROR Then
377: x_return_status := OKL_API.G_RET_STS_ERROR;
378: x_msg_data := substr(sqlerrm,1,255);
379: RAISE Okl_Api.G_EXCEPTION_ERROR;
380: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
381: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
382: x_msg_data := substr(sqlerrm,1,255);
383: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
384: WHEN OTHERS THEN
377: x_return_status := OKL_API.G_RET_STS_ERROR;
378: x_msg_data := substr(sqlerrm,1,255);
379: RAISE Okl_Api.G_EXCEPTION_ERROR;
380: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
381: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
382: x_msg_data := substr(sqlerrm,1,255);
383: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
384: WHEN OTHERS THEN
385: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
379: RAISE Okl_Api.G_EXCEPTION_ERROR;
380: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
381: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
382: x_msg_data := substr(sqlerrm,1,255);
383: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
384: WHEN OTHERS THEN
385: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
386: x_msg_data := substr(sqlerrm,1,255);
387: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
381: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
382: x_msg_data := substr(sqlerrm,1,255);
383: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
384: WHEN OTHERS THEN
385: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
386: x_msg_data := substr(sqlerrm,1,255);
387: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
388: end prepare_insert_lines;
389:
383: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
384: WHEN OTHERS THEN
385: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
386: x_msg_data := substr(sqlerrm,1,255);
387: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
388: end prepare_insert_lines;
389:
390: -- Start of comments
391: --
414: and request_type_code='AMORITIZATION_SCHEDULE_CURRENT')
415: group by AMORT_REPORT_FLAG;
416: l_old_trx_req_id okl_trx_requests.id%type := -1;
417: begin
418: x_return_status := OKL_API.G_RET_STS_SUCCESS;
419: --Depending on the report type insert into the tables appropriately
420: --Check if request already existing and retrieve the maximum request number
421: --Delete the records from the hdr and detail tables
422: OPEN trx_req_csr(p_chr_id => p_chr_id,p_req_id => p_req_id);
435: fetch trx_req_csr INTO l_old_trx_req_id;
436: end loop;
437: CLOSE trx_req_csr;
438: exception
439: WHEN OKL_API.G_EXCEPTION_ERROR Then
440: x_return_status := OKL_API.G_RET_STS_ERROR;
441: x_msg_data := substr(sqlerrm,1,255);
442: RAISE OKL_API.G_EXCEPTION_ERROR;
443: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
436: end loop;
437: CLOSE trx_req_csr;
438: exception
439: WHEN OKL_API.G_EXCEPTION_ERROR Then
440: x_return_status := OKL_API.G_RET_STS_ERROR;
441: x_msg_data := substr(sqlerrm,1,255);
442: RAISE OKL_API.G_EXCEPTION_ERROR;
443: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
444: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
438: exception
439: WHEN OKL_API.G_EXCEPTION_ERROR Then
440: x_return_status := OKL_API.G_RET_STS_ERROR;
441: x_msg_data := substr(sqlerrm,1,255);
442: RAISE OKL_API.G_EXCEPTION_ERROR;
443: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
444: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
445: x_msg_data := substr(sqlerrm,1,255);
446: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
439: WHEN OKL_API.G_EXCEPTION_ERROR Then
440: x_return_status := OKL_API.G_RET_STS_ERROR;
441: x_msg_data := substr(sqlerrm,1,255);
442: RAISE OKL_API.G_EXCEPTION_ERROR;
443: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
444: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
445: x_msg_data := substr(sqlerrm,1,255);
446: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
447: WHEN OTHERS THEN
440: x_return_status := OKL_API.G_RET_STS_ERROR;
441: x_msg_data := substr(sqlerrm,1,255);
442: RAISE OKL_API.G_EXCEPTION_ERROR;
443: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
444: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
445: x_msg_data := substr(sqlerrm,1,255);
446: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
447: WHEN OTHERS THEN
448: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
442: RAISE OKL_API.G_EXCEPTION_ERROR;
443: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
444: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
445: x_msg_data := substr(sqlerrm,1,255);
446: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
447: WHEN OTHERS THEN
448: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
449: x_msg_data := substr(sqlerrm,1,255);
450: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
444: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
445: x_msg_data := substr(sqlerrm,1,255);
446: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
447: WHEN OTHERS THEN
448: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
449: x_msg_data := substr(sqlerrm,1,255);
450: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
451: end delete_old_sched;
452:
446: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
447: WHEN OTHERS THEN
448: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
449: x_msg_data := substr(sqlerrm,1,255);
450: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
451: end delete_old_sched;
452:
453: -- Start of comments
454: --
466: -- x_summ_flag - Boolean indicating whether Summary is available or not
467: -- Version : 1.0
468: -- History : srsreeni created.
469: procedure generate_amor_sched(p_chr_id in okc_k_headers_b.id%type,p_api_version IN NUMBER,
470: p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
471: p_trx_req_id in okl_trx_requests.id%type,p_user_id in number,
472: x_return_status OUT NOCOPY VARCHAR2,x_msg_count OUT NOCOPY NUMBER,
473: x_msg_data OUT NOCOPY VARCHAR2,x_summ_flag OUT boolean) as
474: l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_AMOR_SCHED';
479: l_summ_amort_sched_tbl OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type;
480: l_proj_interest_rate number;
481: begin
482: g_user_id := p_user_id;
483: x_return_status := OKL_API.G_RET_STS_SUCCESS;
484: -- Call start_activity to create savepoint, check compatibility
485: -- and initialize message list
486: x_return_status := OKL_API.START_ACTIVITY (
487: l_api_name
482: g_user_id := p_user_id;
483: x_return_status := OKL_API.G_RET_STS_SUCCESS;
484: -- Call start_activity to create savepoint, check compatibility
485: -- and initialize message list
486: x_return_status := OKL_API.START_ACTIVITY (
487: l_api_name
488: ,p_init_msg_list
489: ,'_PVT'
490: ,x_return_status);
488: ,p_init_msg_list
489: ,'_PVT'
490: ,x_return_status);
491: -- Check if activity started successfully
492: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
493: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
494: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
495: RAISE OKL_API.G_EXCEPTION_ERROR;
496: END IF;
489: ,'_PVT'
490: ,x_return_status);
491: -- Check if activity started successfully
492: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
493: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
494: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
495: RAISE OKL_API.G_EXCEPTION_ERROR;
496: END IF;
497:
490: ,x_return_status);
491: -- Check if activity started successfully
492: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
493: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
494: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
495: RAISE OKL_API.G_EXCEPTION_ERROR;
496: END IF;
497:
498: -- dbms_output.put_line('B4 invoking OKL_LOAN_AMORT_SCHEDULE_PVT - Detail');
491: -- Check if activity started successfully
492: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
493: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
494: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
495: RAISE OKL_API.G_EXCEPTION_ERROR;
496: END IF;
497:
498: -- dbms_output.put_line('B4 invoking OKL_LOAN_AMORT_SCHEDULE_PVT - Detail');
499: --Invoke the API to obtain the generated amoritization schedule
499: --Invoke the API to obtain the generated amoritization schedule
500: --Invoke with Summary and Detail to generate the reports
501: OKL_LOAN_AMORT_SCHEDULE_PVT.load_loan_amort_schedule(
502: p_api_version => p_api_version,
503: p_init_msg_list => OKL_API.G_TRUE,
504: x_return_status => x_return_status,
505: x_msg_count => x_msg_count,
506: x_msg_data => x_msg_data,
507: p_chr_id => p_chr_id,
510: x_amort_sched_tbl => l_det_amort_sched_tbl);
511: -- dbms_output.put_line('B4 invoking OKL_LOAN_AMORT_SCHEDULE_PVT - Summary');
512: OKL_LOAN_AMORT_SCHEDULE_PVT.load_loan_amort_schedule(
513: p_api_version => p_api_version,
514: p_init_msg_list => OKL_API.G_TRUE,
515: x_return_status => x_return_status,
516: x_msg_count => x_msg_count,
517: x_msg_data => x_msg_data,
518: p_chr_id => p_chr_id,
518: p_chr_id => p_chr_id,
519: p_report_type => G_REPORT_TYPE_SUMMARY,
520: x_proj_interest_rate => l_proj_interest_rate,
521: x_amort_sched_tbl => l_summ_amort_sched_tbl);
522: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
523: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
524: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
525: RAISE OKL_API.G_EXCEPTION_ERROR;
526: END IF;
519: p_report_type => G_REPORT_TYPE_SUMMARY,
520: x_proj_interest_rate => l_proj_interest_rate,
521: x_amort_sched_tbl => l_summ_amort_sched_tbl);
522: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
523: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
524: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
525: RAISE OKL_API.G_EXCEPTION_ERROR;
526: END IF;
527: -- dbms_output.put_line('B4 invoking delete_old_sched');
520: x_proj_interest_rate => l_proj_interest_rate,
521: x_amort_sched_tbl => l_summ_amort_sched_tbl);
522: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
523: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
524: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
525: RAISE OKL_API.G_EXCEPTION_ERROR;
526: END IF;
527: -- dbms_output.put_line('B4 invoking delete_old_sched');
528: --Invoke procedure for deleting old reports for the contract if any
521: x_amort_sched_tbl => l_summ_amort_sched_tbl);
522: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
523: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
524: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
525: RAISE OKL_API.G_EXCEPTION_ERROR;
526: END IF;
527: -- dbms_output.put_line('B4 invoking delete_old_sched');
528: --Invoke procedure for deleting old reports for the contract if any
529: /*
533: x_msg_count => x_msg_count,
534: x_msg_data => x_msg_data,
535: p_chr_id => p_chr_id,
536: p_req_id => p_trx_req_id);
537: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
540: RAISE Okl_Api.G_EXCEPTION_ERROR;
541: END IF;
534: x_msg_data => x_msg_data,
535: p_chr_id => p_chr_id,
536: p_req_id => p_trx_req_id);
537: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
540: RAISE Okl_Api.G_EXCEPTION_ERROR;
541: END IF;
542: */
535: p_chr_id => p_chr_id,
536: p_req_id => p_trx_req_id);
537: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
540: RAISE Okl_Api.G_EXCEPTION_ERROR;
541: END IF;
542: */
543: if (l_summ_amort_sched_tbl is not null and l_summ_amort_sched_tbl.count > 0)
536: p_req_id => p_trx_req_id);
537: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
538: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
540: RAISE Okl_Api.G_EXCEPTION_ERROR;
541: END IF;
542: */
543: if (l_summ_amort_sched_tbl is not null and l_summ_amort_sched_tbl.count > 0)
544: then
553: x_msg_data => x_msg_data,
554: p_trx_req_id => p_trx_req_id,
555: p_summ_flag => l_summ_flag,
556: x_amor_hdr_id => l_amor_hdr_id);
557: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
558: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
559: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
560: RAISE Okl_Api.G_EXCEPTION_ERROR;
561: END IF;
554: p_trx_req_id => p_trx_req_id,
555: p_summ_flag => l_summ_flag,
556: x_amor_hdr_id => l_amor_hdr_id);
557: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
558: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
559: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
560: RAISE Okl_Api.G_EXCEPTION_ERROR;
561: END IF;
562: -- dbms_output.put_line('B4 invoking prepare_insert_lines');
555: p_summ_flag => l_summ_flag,
556: x_amor_hdr_id => l_amor_hdr_id);
557: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
558: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
559: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
560: RAISE Okl_Api.G_EXCEPTION_ERROR;
561: END IF;
562: -- dbms_output.put_line('B4 invoking prepare_insert_lines');
563: --Insert records into the hdr and detail tables for the amor_sched
556: x_amor_hdr_id => l_amor_hdr_id);
557: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
558: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
559: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
560: RAISE Okl_Api.G_EXCEPTION_ERROR;
561: END IF;
562: -- dbms_output.put_line('B4 invoking prepare_insert_lines');
563: --Insert records into the hdr and detail tables for the amor_sched
564: /*
579: x_msg_data => x_msg_data,
580: p_det_amort_sched_tbl => l_det_amort_sched_tbl,
581: p_summ_amort_sched_tbl => l_summ_amort_sched_tbl,
582: p_amor_hdr_id => l_amor_hdr_id,p_proj_interest_rate => l_proj_interest_rate);
583: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
584: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
585: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
586: RAISE Okl_Api.G_EXCEPTION_ERROR;
587: END IF;
580: p_det_amort_sched_tbl => l_det_amort_sched_tbl,
581: p_summ_amort_sched_tbl => l_summ_amort_sched_tbl,
582: p_amor_hdr_id => l_amor_hdr_id,p_proj_interest_rate => l_proj_interest_rate);
583: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
584: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
585: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
586: RAISE Okl_Api.G_EXCEPTION_ERROR;
587: END IF;
588: OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
581: p_summ_amort_sched_tbl => l_summ_amort_sched_tbl,
582: p_amor_hdr_id => l_amor_hdr_id,p_proj_interest_rate => l_proj_interest_rate);
583: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
584: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
585: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
586: RAISE Okl_Api.G_EXCEPTION_ERROR;
587: END IF;
588: OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
589: --commit;
582: p_amor_hdr_id => l_amor_hdr_id,p_proj_interest_rate => l_proj_interest_rate);
583: IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
584: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
585: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
586: RAISE Okl_Api.G_EXCEPTION_ERROR;
587: END IF;
588: OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
589: --commit;
590: x_summ_flag := l_summ_flag;
584: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
585: ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
586: RAISE Okl_Api.G_EXCEPTION_ERROR;
587: END IF;
588: OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
589: --commit;
590: x_summ_flag := l_summ_flag;
591: Exception
592: WHEN OKL_API.G_EXCEPTION_ERROR THEN
588: OKL_API.END_ACTIVITY (x_msg_count,x_msg_data);
589: --commit;
590: x_summ_flag := l_summ_flag;
591: Exception
592: WHEN OKL_API.G_EXCEPTION_ERROR THEN
593: --rollback;
594: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
595: p_api_name => l_api_name,
596: p_pkg_name => G_PKG_NAME,
590: x_summ_flag := l_summ_flag;
591: Exception
592: WHEN OKL_API.G_EXCEPTION_ERROR THEN
593: --rollback;
594: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
595: p_api_name => l_api_name,
596: p_pkg_name => G_PKG_NAME,
597: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
598: x_msg_count => x_msg_count,
593: --rollback;
594: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
595: p_api_name => l_api_name,
596: p_pkg_name => G_PKG_NAME,
597: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
598: x_msg_count => x_msg_count,
599: x_msg_data => x_msg_data,
600: p_api_type => '_PVT');
601:
598: x_msg_count => x_msg_count,
599: x_msg_data => x_msg_data,
600: p_api_type => '_PVT');
601:
602: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
603: --rollback;
604: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
605: p_api_name => l_api_name,
606: p_pkg_name => G_PKG_NAME,
600: p_api_type => '_PVT');
601:
602: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
603: --rollback;
604: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
605: p_api_name => l_api_name,
606: p_pkg_name => G_PKG_NAME,
607: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
608: x_msg_count => x_msg_count,
603: --rollback;
604: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
605: p_api_name => l_api_name,
606: p_pkg_name => G_PKG_NAME,
607: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
608: x_msg_count => x_msg_count,
609: x_msg_data => x_msg_data,
610: p_api_type => '_PVT');
611:
610: p_api_type => '_PVT');
611:
612: WHEN OTHERS THEN
613: --rollback;
614: x_return_status := OKL_API.HANDLE_EXCEPTIONS (
615: p_api_name => l_api_name,
616: p_pkg_name => G_PKG_NAME,
617: p_exc_name => 'OTHERS',
618: x_msg_count => x_msg_count,