The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_hdr(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,
p_trx_req_id in okl_trx_requests.id%type,p_summ_flag in boolean,
x_amor_hdr_id out NOCOPY amort_hdr_id_tbl_type) as
l_amor_hdr_id amort_hdr_id_tbl_type;
select okl_amort_sched_hdr_s.nextval into l_hdr_id from dual;
insert into OKL_AMORT_SCHED_HDRS (AMORT_HDR_ID,TRX_REQ_ID,AMORT_TYPE,AMORT_REPORT_FLAG,created_by,creation_date,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
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);
select okl_amort_sched_hdr_s.nextval into l_hdr_id from dual;
insert into OKL_AMORT_SCHED_HDRS (AMORT_HDR_ID,TRX_REQ_ID,AMORT_TYPE,AMORT_REPORT_FLAG,created_by,creation_date,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
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);
end insert_hdr;
procedure insert_lines(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,
p_amort_sched_tbl in OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type,p_amor_line_id in AMORT_LINE_ID,
p_pri_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type,
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,
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,
p_pastproj_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type default NULL,
p_date_to_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type default null,
p_proj_interest_rate_id in OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%type default null,
p_summ_flag in boolean default false,
p_proj_interest_rate in number default NULL,p_order_by_id in
OKL_AMORT_SCHED_HDRS.AMORT_HDR_ID%TYPE) as
begin
x_return_status := OKL_API.G_RET_STS_SUCCESS;
--Insert for Principal
forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
values (p_pri_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).principal,g_user_id,sysdate);
--Insert for Interest
forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
values (p_int_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).interest,g_user_id,sysdate);
--Insert for Principal Balance
forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
values (p_pri_bal_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).principal_balance,g_user_id,sysdate);
--Insert for Period
forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
values (p_date_from_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).start_date,g_user_id,sysdate);
--Insert for Past
forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
values (p_pastproj_id,p_amor_line_id(ins_count),p_past_proj,g_user_id,sysdate);
--Insert for Date To for Summary
if p_summ_flag then
forall ins_count in p_amort_sched_tbl.first .. p_amort_sched_tbl.last
insert into OKL_AMORT_SCHED_LINES(AMORT_HDR_ID,AMORT_LINE_ID,AMORT_VALUE,created_by,creation_date)
values (p_date_to_id,p_amor_line_id(ins_count),p_amort_sched_tbl(ins_count).end_date,g_user_id,sysdate);
--Insert for Principal
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)
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);
--Insert for Interest
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)
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);
--Insert for Principal Balance
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)
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);
--Insert for Loan Payment
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)
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);
--Insert for Period
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)
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);
--Insert for Order BY
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)
values (p_order_by_id,p_amor_line_id(ins_count),ins_count,g_user_id,sysdate,g_user_id,sysdate,g_user_id);
--Insert for Date To for Summary
if p_summ_flag then
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)
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);
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)
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);
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)
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);
end insert_lines;
procedure prepare_insert_lines(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,
p_det_amort_sched_tbl in OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type,
p_summ_amort_sched_tbl in OKL_LOAN_AMORT_SCHEDULE_PVT.amort_sched_tbl_type,
p_amor_hdr_id in amort_hdr_id_tbl_type,p_proj_interest_rate in number) as
l_det_amor_line_id AMORT_LINE_ID := AMORT_LINE_ID(-1);
select okl_amort_sched_lines_s.nextval into l_det_amor_line_id(i) from dual;
select okl_amort_sched_lines_s.nextval into l_summ_amor_line_id(i) from dual;
insert_lines(p_api_version => p_api_version,p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,x_msg_count => x_msg_count,
x_msg_data => x_msg_data,p_amort_sched_tbl => p_det_amort_sched_tbl,
p_amor_line_id => l_det_amor_line_id,
p_pri_id => p_amor_hdr_id(l_hdr_indx).pri_det_id,
p_int_id => p_amor_hdr_id(l_hdr_indx).int_det_id,
p_pri_bal_id => p_amor_hdr_id(l_hdr_indx).pri_bal_det_id,
p_date_from_id => p_amor_hdr_id(l_hdr_indx).date_from_det_id,
p_loan_pymnt_id => p_amor_hdr_id(l_hdr_indx).loan_pymnt_det_id,
p_pastproj_id => p_amor_hdr_id(l_hdr_indx).pastproj_det_id,
p_proj_interest_rate_id => p_amor_hdr_id(l_hdr_indx).proj_interest_rate_id,
p_proj_interest_rate => p_proj_interest_rate,
p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_det_id);
insert_lines(p_api_version => p_api_version,p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,x_msg_count => x_msg_count,
x_msg_data => x_msg_data,p_amort_sched_tbl => p_summ_amort_sched_tbl,
p_amor_line_id => l_summ_amor_line_id,
p_pri_id => p_amor_hdr_id(l_hdr_indx).pri_summ_id,
p_int_id => p_amor_hdr_id(l_hdr_indx).int_summ_id,
p_pri_bal_id => p_amor_hdr_id(l_hdr_indx).pri_bal_summ_id,
p_date_from_id => p_amor_hdr_id(l_hdr_indx).date_from_summ_id,
p_loan_pymnt_id => p_amor_hdr_id(l_hdr_indx).loan_pymnt_summ_id,
p_date_to_id => p_amor_hdr_id(l_hdr_indx).date_to_summ_id,
p_summ_flag => true,
p_order_by_id => p_amor_hdr_id(l_hdr_indx).order_by_summ_id);
end prepare_insert_lines;
procedure delete_old_sched(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2,p_chr_id in okc_k_headers_b.id%type,
p_req_id in okl_trx_requests.id%type) as
cursor trx_req_csr(p_chr_id in okc_k_headers_b.id%type,p_req_id in okl_trx_requests.id%type) is
select nvl(max(trx_req_id),-1) trx_req_id from OKL_AMORT_SCHED_HDRS
where trx_req_id = (select nvl(max(tr.id),-1) from okl_trx_requests tr
where dnz_khr_id = p_chr_id and id < p_req_id
and request_type_code='AMORITIZATION_SCHEDULE_CURRENT')
group by AMORT_REPORT_FLAG;
delete from OKL_AMORT_SCHED_LINES where AMORT_HDR_ID in(select AMORT_HDR_ID from OKL_AMORT_SCHED_HDRS
where trx_req_id=l_old_trx_req_id);
delete from OKL_AMORT_SCHED_HDRS where trx_req_id=l_old_trx_req_id;
end delete_old_sched;
delete_old_sched(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => p_chr_id,
p_req_id => p_trx_req_id);
insert_hdr(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trx_req_id => p_trx_req_id,
p_summ_flag => l_summ_flag,
x_amor_hdr_id => l_amor_hdr_id);
prepare_insert_lines(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_det_amort_sched_tbl => l_det_amort_sched_tbl,
p_det_amort_sched_proj_tbl => l_det_amort_sched_proj_tbl,
p_summ_amort_sched_tbl => l_summ_amort_sched_tbl,
p_summ_amort_sched_proj_tbl => l_summ_amort_sched_proj_tbl,
p_amor_hdr_id => l_amor_hdr_id);*/
prepare_insert_lines(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_det_amort_sched_tbl => l_det_amort_sched_tbl,
p_summ_amort_sched_tbl => l_summ_amort_sched_tbl,
p_amor_hdr_id => l_amor_hdr_id,p_proj_interest_rate => l_proj_interest_rate);