oracle, procedure, table with params

Question

I want to create a procedure in Oracle PL/SQL that allows to analyze cash flows based on parameters.

create table cfl_param (
contract_type varchar2(20),
new_cfl_month number,
cfl_percentage number
);

create table cfl_table (
contract_id varchar2(20),
contract_type varchar2(20),
cfl_analyze varchar2(20),
cfl_date date,
cfl_value number
);


insert into cfl_param values
('A', 6, 0.4);

insert into cfl_param values
('A', 9, 0.3);

insert into cfl_table values
('A_1', 'A', 'N', date '2020-07-31', 1000);

Table cfl_param has parameters for every contract type such as new cash flows date and percentage of new cash flow. In my example contract_type A, has got two dates of new cash flows first one in 6 months (40% of cfl_value), second one in 9 months (30% of cfl_value).

Table 'cfl_table' has data about cash flows, originally every contract_id has got value 'N' in column cfl_analyze.

For contract_type with value 'A' the procedure has to insert 3 new rows: 2 with new dates and 30% and 40% of cfl_value and another one with 30% of value but with orignally cfl_date. I'd like to obtain something like that:

Table with results of the procedure

Answer 1

You don't really need a procedure. You can generate the extra values with a query like:

select ct.contract_id, ct.contract_type, 'Y' as cfl_analyze,
  ct.cfl_date, 0.3 * ct.cfl_value as cfl_value
from cfl_table ct
where ct.contract_id = 'A_1'
union all
select ct.contract_id, ct.contract_type, 'Y' as cfl_analyze,
  add_months(ct.cfl_date, cp.new_cfl_month) as cfl_date,
  ct.cfl_value * cp.cfl_percentage as cfl_value
from cfl_table ct
join cfl_param cp on cp.contract_type = ct.contract_type
where ct.contract_id = 'A_1';

CONTRACT_ID          CONTRACT_TYPE        C CFL_DATE    CFL_VALUE
-------------------- -------------------- - ---------- ----------
A_1                  A                    Y 2020-07-31        300
A_1                  A                    Y 2021-01-31        400
A_1                  A                    Y 2021-04-30        300

The first branch of the union gets the original date with a fixed 30% value; the second branch of the union calculates the other dates and values based on the parameters.

Then you can use that in an insert statement:

insert into cfl_table (contract_id, contract_type, cfl_analyze, cfl_date, cfl_value)
select ct.contract_id, ct.contract_type, 'Y', ct.cfl_date, 0.3 * ct.cfl_value
from cfl_table ct
where ct.contract_id = 'A_1'
union all
select ct.contract_id, ct.contract_type, 'Y',
  add_months(ct.cfl_date, cp.new_cfl_month),
  ct.cfl_value * cp.cfl_percentage
from cfl_table ct
join cfl_param cp on cp.contract_type = ct.contract_type
where ct.contract_id = 'A_1';

3 rows inserted.

select * from cfl_table;

CONTRACT_ID          CONTRACT_TYPE        CFL_ANALYZE          CFL_DATE    CFL_VALUE
-------------------- -------------------- -------------------- ---------- ----------
A_1                  A                    N                    2020-07-31       1000
A_1                  A                    Y                    2020-07-31        300
A_1                  A                    Y                    2021-01-31        400
A_1                  A                    Y                    2021-04-30        300

db<>fiddle

You can of course wrap that in a procedure if you want or need to.

It would be a bit simpler if you added the 30% as an extra parameter:

insert into cfl_param values
('A', 0, 0.3);

Then you wouldn't need the union:

insert into cfl_table (contract_id, contract_type, cfl_analyze, cfl_date, cfl_value)
select ct.contract_id, ct.contract_type, 'Y',
  add_months(ct.cfl_date, cp.new_cfl_month),
  ct.cfl_value * cp.cfl_percentage
from cfl_table ct
join cfl_param cp on cp.contract_type = ct.contract_type
where ct.contract_id = 'A_1';

db<>fiddle