1 PACKAGE BODY OKL_AMORT_SCHED_PROCESS_PVT as
2 /* $Header: OKLRLAMB.pls 120.6 2008/02/12 18:52:01 srsreeni noship $ */
3 -- Start of comments
4 --
5 -- API name : insert_hdr
6 -- Pre-reqs : None
7 -- Function : This procedure inserts the Amortization Schedule generated
8 -- into the OKL_AMORT_SCHED_HDRS
9 -- Parameters :
10 -- IN : p_api_version - Standard input parameter
11 -- p_init_msg_list - Standard input parameter
12 -- p_trx_req_id - Request ID from okl_trx_requests table
13 -- p_summ_flag - Boolean value indicating whether summary is available
14 -- OUT : x_return_status - Standard output parameter for Output status
15 -- x_msg_count - Standard output parameter
16 -- x_msg_data - Standard output parameter
17 -- x_amor_hdr_id - The header ID record
18 -- Version : 1.0
19 -- History : srsreeni created.
20 procedure insert_hdr(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,
22 p_trx_req_id in okl_trx_requests.id%type,p_summ_flag in boolean,
23 x_amor_hdr_id out NOCOPY amort_hdr_id_tbl_type) as
24 l_amor_hdr_id amort_hdr_id_tbl_type;
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
34 select okl_amort_sched_hdr_s.nextval into l_hdr_id from dual;
35 if ins_count = 1 then
36 l_amor_hdr_id(l_indx).pri_det_id := l_hdr_id;
37 l_amor_type := G_PRINCIPAL_TYPE;
38 elsif ins_count = 2 then
39 l_amor_hdr_id(l_indx).int_det_id := l_hdr_id;
40 l_amor_type := G_INTEREST_TYPE;
41 elsif ins_count = 3 then
42 l_amor_hdr_id(l_indx).pri_bal_det_id := l_hdr_id;
43 l_amor_type := G_PRINCIPAL_BAL_TYPE;
44 elsif ins_count = 4 then
45 l_amor_hdr_id(l_indx).date_from_det_id := l_hdr_id;
46 l_amor_type := G_DATE_FROM;
47 elsif ins_count = 5 then
48 l_amor_hdr_id(l_indx).loan_pymnt_det_id := l_hdr_id;
49 l_amor_type := G_LOAN_PAYMENT;
50 elsif ins_count = 6 then
51 l_amor_hdr_id(l_indx).pastproj_det_id := l_hdr_id;
52 l_amor_type := G_PAST_PROJ;
53 elsif ins_count = 7 then
54 l_amor_hdr_id(l_indx).proj_interest_rate_id := l_hdr_id;
55 l_amor_type := G_PROJ_INTEREST_RATE;
56 else
57 l_amor_hdr_id(l_indx).order_by_det_id := l_hdr_id;
58 l_amor_type := G_ORDER_BY;
59 end if;
60 insert into OKL_AMORT_SCHED_HDRS (AMORT_HDR_ID,TRX_REQ_ID,AMORT_TYPE,AMORT_REPORT_FLAG,created_by,creation_date,
61 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
62 values(l_hdr_id,p_trx_req_id,l_amor_type,G_REPORT_TYPE_DETAIL_C,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
63 end loop;
64 -- dbms_output.put_line('After invoking insert detail');
65 --Insert for Summary only if data is available
66 if p_summ_flag then
67 -- dbms_output.put_line('Inside invoking insert summary');
68
69 --Insert 5 records into the header for detail for Principal, Interest, Principal Balance, Date from and Date to
70 for ins_count in 1 .. G_SUMM_COLUMNS
71 loop
72 select okl_amort_sched_hdr_s.nextval into l_hdr_id from dual;
73 if ins_count = 1 then
74 l_amor_hdr_id(l_indx).pri_summ_id := l_hdr_id;
75 l_amor_type := G_PRINCIPAL_TYPE;
76 elsif ins_count = 2 then
77 l_amor_hdr_id(l_indx).int_summ_id := l_hdr_id;
78 l_amor_type := G_INTEREST_TYPE;
79 elsif ins_count = 3 then
80 l_amor_hdr_id(l_indx).pri_bal_summ_id := l_hdr_id;
81 l_amor_type := G_PRINCIPAL_BAL_TYPE;
82 elsif ins_count = 4 then
83 l_amor_hdr_id(l_indx).date_from_summ_id := l_hdr_id;
84 l_amor_type := G_DATE_FROM;
85 elsif ins_count = 5 then
86 l_amor_hdr_id(l_indx).date_to_summ_id := l_hdr_id;
87 l_amor_type := G_DATE_TO;
88 elsif ins_count = 6 then
89 l_amor_hdr_id(l_indx).loan_pymnt_summ_id := l_hdr_id;
90 l_amor_type := G_LOAN_PAYMENT;
91 else
92 l_amor_hdr_id(l_indx).order_by_summ_id := l_hdr_id;
93 l_amor_type := G_ORDER_BY;
94 end if;
95 insert into OKL_AMORT_SCHED_HDRS (AMORT_HDR_ID,TRX_REQ_ID,AMORT_TYPE,AMORT_REPORT_FLAG,created_by,creation_date,
96 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
97 values(l_hdr_id,p_trx_req_id,l_amor_type,G_REPORT_TYPE_SUMMARY_C,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
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
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
116
117 -- Start of comments
118 --
119 -- API name : insert_lines
120 -- Pre-reqs : None
121 -- Function : This procedure inserts the Amortization Schedule generated
122 -- into the OKL_AMORT_SCHED_LINES
123 -- Parameters :
124 -- IN : p_api_version - Standard input parameter
125 -- p_init_msg_list - Standard input parameter
126 -- p_amort_sched_tbl - PL/SQL table for amor schedule
127 -- p_amor_line_id - PL/SQL table containing AMORT_LINE_ID
128 -- p_pri_id - Principal Header Id
129 -- p_int_id - Interest Header Id
130 -- p_pri_bal_id - Principal Balance Header Id
131 -- p_date_from_id - Date From Header Id
132 -- p_loan_pymnt_id - Loan Payment Header Id
133 -- p_pastproj_id - Past Projected Header Id
134 -- p_date_to_id - Date To Header Id
135 -- p_proj_interest_rate_id - Proj Interest Rate Header Id
136 -- p_summ_flag - Indicates Summary Report or not
137 -- p_proj_interest_rate - The interest rate used for
138 -- calculating projected schedule
139 -- p_order_by_id - The order by Header Id
140 -- OUT : x_return_status - Standard output parameter for Output status
141 -- x_msg_count - Standard output parameter
142 -- x_msg_data - Standard output parameter
143 -- Version : 1.0
144 -- History : srsreeni created.
145 procedure insert_lines(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,
147 p_amort_sched_tbl in OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type,p_amor_line_id in AMORT_LINE_ID,
148 p_pri_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type,
149 p_int_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type,p_pri_bal_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type,
150 p_date_from_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type,p_loan_pymnt_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type,
151 p_pastproj_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type default NULL,
152 p_date_to_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type default null,
153 p_proj_interest_rate_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type default null,
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
163 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
164 values (p_pri_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).principal,g_user_id,sysdate);
165 --Insert for Interest
166 forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
167 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
168 values (p_int_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).interest,g_user_id,sysdate);
169 --Insert for Principal Balance
170 forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
171 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
172 values (p_pri_bal_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).principal_balance,g_user_id,sysdate);
173 --Insert for Period
174 forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
175 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
176 values (p_date_from_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).start_date,g_user_id,sysdate);
177 --Insert for Past
178 forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
179 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
180 values (p_pastproj_id,p_amor_line_id(ins_count),p_past_proj,g_user_id,sysdate);
181
182 --Insert for Date To for Summary
183 if p_summ_flag then
184 forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
185 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
186 values (p_date_to_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).end_date,g_user_id,sysdate);
187 end if;
188 */
189 for ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
190 loop
191 --Insert for Principal
192 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,
193 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
194 values (p_pri_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).principal,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
195 --Insert for Interest
196 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,
197 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
198 values (p_int_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).interest,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
199 --Insert for Principal Balance
200 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,
201 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
202 values (p_pri_bal_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).principal_balance,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
203 --Insert for Loan Payment
204 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,
205 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
206 values (p_loan_pymnt_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).loan_payment,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
207 --Insert for Period
208 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,
209 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
210 values (p_date_from_id,p_amor_line_id(ins_count),to_char(p_amort_sched_tbl(ins_count).start_date,'dd-mon-yyyy'),g_user_id,sysdate,g_user_id,sysdate,g_user_id);
211 --Insert for Order BY
212 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,
216 --Insert for Date To for Summary
213 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
214 values (p_order_by_id,p_amor_line_id(ins_count),ins_count,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
215
217 if p_summ_flag then
218 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
219 values (p_date_to_id,p_amor_line_id(ins_count),to_char(p_amort_sched_tbl(ins_count).end_date,'dd-mon-yyyy'),g_user_id,sysdate,g_user_id,sysdate,g_user_id);
220 else
221 --Insert into Payment type only for detail
222 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
223 values (p_pastproj_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).payment_type,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
224 --Insert for Projected Interest rate only for detail and for the first record
225 if ins_count = 1 then
226 insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
227 values (p_proj_interest_rate_id,p_amor_line_id(ins_count),p_proj_interest_rate,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
228 end if;
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;
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;
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
254
255
256 -- Start of comments
257 --
258 -- API name : prepare_insert_lines
259 -- Pre-reqs : None
260 -- Function : This procedure prepares for inserting the Amortization Schedule generated
261 -- into the OKL_AMORT_SCHED_LINES
262 -- Parameters :
263 -- IN : p_api_version - Standard input parameter
264 -- p_init_msg_list - Standard input parameter
265 -- p_det_amort_sched_tbl - PL/SQL table for Detail amor schedule
266 -- p_summ_amort_sched_tbl - PL/SQL table for Summary amor schedule
267 -- p_amor_hdr_id - The header ID record
268 -- p_proj_interest_rate - The Projected interest
269 -- rate
270 -- OUT : x_return_status - Standard output parameter for Output status
271 -- x_msg_count - Standard output parameter
272 -- x_msg_data - Standard output parameter
273 -- Version : 1.0
274 -- History : srsreeni created.
275 procedure prepare_insert_lines(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,
277 p_det_amort_sched_tbl in OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type,
278 p_summ_amort_sched_tbl in OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type,
279 p_amor_hdr_id in amort_hdr_id_tbl_type,p_proj_interest_rate in number) as
280 l_det_amor_line_id AMORT_LINE_ID := AMORT_LINE_ID(-1);
281 l_summ_amor_line_id AMORT_LINE_ID := AMORT_LINE_ID(-1);
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;
291 --Populate actual line_id
292 if l_rec_count > 0 then
293 l_rec_count := l_rec_count - 1;
294 l_det_amor_line_id.extend(l_rec_count,1);
295 for i in 1 .. l_det_amor_line_id.count
296 loop
297 select okl_amort_sched_lines_s.nextval into l_det_amor_line_id(i) from dual;
298 end loop;
299 end if;
300 -- dbms_output.put_line('l_det_amor_line_id : ' || l_det_amor_line_id.count || ',p_det_amort_sched_tbl.count: ' || p_det_amort_sched_tbl.count);
301 l_rec_count := 0;
302 --Retrieve the count of line_id for summary to be inserted. Assign line_id from the detail
303 if p_summ_amort_sched_tbl is not null then
304 l_rec_count := p_summ_amort_sched_tbl.count;
305 end if;
306 --Populate actual line_id
307 if l_rec_count > 0 then
308 l_rec_count := l_rec_count - 1;
309 l_summ_amor_line_id.extend(l_rec_count,1);
310 --Logic to check if Detail Report has less records than Summary Report
311 if l_summ_amor_line_id.count > l_det_amor_line_id.count then
312 l_summ_det_cnt := l_det_amor_line_id.count;
313 else
314 l_summ_det_cnt := l_summ_amor_line_id.count;
315 end if;
316
317 for i in 1 .. l_summ_det_cnt
318 loop
319 l_summ_amor_line_id(i) := l_det_amor_line_id(i);
320 end loop;
321 if l_summ_amor_line_id.count > l_det_amor_line_id.count then
322 l_summ_det_cnt := l_summ_det_cnt + 1;
323 for i in l_summ_det_cnt .. l_summ_amor_line_id.count
324 loop
325 select okl_amort_sched_lines_s.nextval into l_summ_amor_line_id(i) from dual;
326 end loop;
327 end if;
328 end if;
329
330 if l_det_amor_line_id.first = -1 then
334 --Insert Detail Schedule
331 x_msg_data := 'Error querying data for Detail Report';
332 raise OKL_API.G_EXCEPTION_ERROR;
333 end if;
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,
337 x_return_status => x_return_status,x_msg_count => x_msg_count,
338 x_msg_data => x_msg_data,p_amort_sched_tbl => p_det_amort_sched_tbl,
339 p_amor_line_id => l_det_amor_line_id,
340 p_pri_id => p_amor_hdr_id(l_hdr_indx).pri_det_id,
341 p_int_id => p_amor_hdr_id(l_hdr_indx).int_det_id,
342 p_pri_bal_id => p_amor_hdr_id(l_hdr_indx).pri_bal_det_id,
343 p_date_from_id => p_amor_hdr_id(l_hdr_indx).date_from_det_id,
344 p_loan_pymnt_id => p_amor_hdr_id(l_hdr_indx).loan_pymnt_det_id,
345 p_pastproj_id => p_amor_hdr_id(l_hdr_indx).pastproj_det_id,
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;
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,
358 x_return_status => x_return_status,x_msg_count => x_msg_count,
359 x_msg_data => x_msg_data,p_amort_sched_tbl => p_summ_amort_sched_tbl,
360 p_amor_line_id => l_summ_amor_line_id,
361 p_pri_id => p_amor_hdr_id(l_hdr_indx).pri_summ_id,
362 p_int_id => p_amor_hdr_id(l_hdr_indx).int_summ_id,
363 p_pri_bal_id => p_amor_hdr_id(l_hdr_indx).pri_bal_summ_id,
364 p_date_from_id => p_amor_hdr_id(l_hdr_indx).date_from_summ_id,
365 p_loan_pymnt_id => p_amor_hdr_id(l_hdr_indx).loan_pymnt_summ_id,
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;
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;
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 --
392 -- API name : delete_old_sched
393 -- Pre-reqs : None
394 -- Function : This procedure deletes the Amortization Schedule generated
395 -- in the past for the contract.Deletes both Summary and Detail
396 -- Parameters :
397 -- IN : p_api_version - Standard input parameter
398 -- p_init_msg_list - Standard input parameter
399 -- p_chr_id - Contract ID
400 -- p_req_id - Request ID from okl_trx_requests table
401 -- OUT : x_return_status - Standard output parameter for Output status
402 -- x_msg_count - Standard output parameter
403 -- x_msg_data - Standard output parameter
404 -- Version : 1.0
405 -- History : srsreeni created.
406
407 procedure delete_old_sched(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,p_chr_id in okc_k_headers_b.id%type,
409 p_req_id in okl_trx_requests.id%type) as
410 cursor trx_req_csr(p_chr_id in okc_k_headers_b.id%type,p_req_id in okl_trx_requests.id%type) is
411 select nvl(max(trx_req_id),-1) trx_req_id from OKL_AMORT_SCHED_HDRS
412 where trx_req_id = (select nvl(max(tr.id),-1) from okl_trx_requests tr
413 where dnz_khr_id = p_chr_id and id < p_req_id
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);
423 FETCH trx_req_csr INTO l_old_trx_req_id;
424 loop
425 --If value returned from query is -1, then there is no report history for the contract
426 --Else there is a record, deletion need to be performed to maintain one copy each of Summary/Detail
427 --of the report for the contract
428 if l_old_trx_req_id is not null and l_old_trx_req_id <> -1 then
429 delete from OKL_AMORT_SCHED_LINES where AMORT_HDR_ID in(select AMORT_HDR_ID from OKL_AMORT_SCHED_HDRS
430 where trx_req_id=l_old_trx_req_id);
431 delete from OKL_AMORT_SCHED_HDRS where trx_req_id=l_old_trx_req_id;
432 end if;
433 --commit;
434 exit when trx_req_csr%notfound;
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;
444 x_return_status := OKL_API.G_RET_STS_UNEXP_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
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
453 -- Start of comments
454 --
455 -- API name : generate_amor_sched
456 -- Pre-reqs : None
457 -- Function : This procedure is invoked to generate the Amortization Schedule
458 -- Invoked after the request record is created from Lease Center
459 -- Parameters :
460 -- IN : p_chr_id - Contract ID
461 -- p_trx_req_id - Request ID from okl_trx_requests table
462 -- p_user_id - User ID requesting the schedule
463 -- OUT : x_return_status - Standard output parameter for Output status
464 -- x_msg_count - Standard output parameter
465 -- x_msg_data - Standard output parameter
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';
475 l_api_version CONSTANT NUMBER := 1.0;
476 l_amor_hdr_id amort_hdr_id_tbl_type;
477 l_summ_flag boolean := false;
478 l_det_amort_sched_tbl OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type;
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
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;
497
498 -- dbms_output.put_line('B4 invoking OKL_LOAN_AMORT_SCHEDULE_PVT - Detail');
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,
508 p_report_type => G_REPORT_TYPE_DETAIL,
509 x_proj_interest_rate => l_proj_interest_rate,
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,
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');
528 --Invoke procedure for deleting old reports for the contract if any
529 /*
530 delete_old_sched(p_api_version => p_api_version,
531 p_init_msg_list => p_init_msg_list,
532 x_return_status => x_return_status,
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;
542 */
543 if (l_summ_amort_sched_tbl is not null and l_summ_amort_sched_tbl.count > 0)
544 then
545 l_summ_flag := true;
546 end if;
547 -- dbms_output.put_line('B4 invoking insert_hdr');
548 --Insert records into the hdr tables for amor_sched and obtain header ID
549 insert_hdr(p_api_version => p_api_version,
550 p_init_msg_list => p_init_msg_list,
551 x_return_status => x_return_status,
552 x_msg_count => x_msg_count,
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;
562 -- dbms_output.put_line('B4 invoking prepare_insert_lines');
563 --Insert records into the hdr and detail tables for the amor_sched
564 /*
565 prepare_insert_lines(p_api_version => p_api_version,
566 p_init_msg_list => p_init_msg_list,
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data,
570 p_det_amort_sched_tbl => l_det_amort_sched_tbl,
571 p_det_amort_sched_proj_tbl => l_det_amort_sched_proj_tbl,
572 p_summ_amort_sched_tbl => l_summ_amort_sched_tbl,
573 p_summ_amort_sched_proj_tbl => l_summ_amort_sched_proj_tbl,
574 p_amor_hdr_id => l_amor_hdr_id);*/
575 prepare_insert_lines(p_api_version => p_api_version,
576 p_init_msg_list => p_init_msg_list,
577 x_return_status => x_return_status,
578 x_msg_count => x_msg_count,
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;
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,
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
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,
609 x_msg_data => x_msg_data,
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,
619 x_msg_data => x_msg_data,
620 p_api_type => '_PVT');
621 end generate_amor_sched;
622 end OKL_AMORT_SCHED_PROCESS_PVT;
[Home] [Help]