Stored Procedure with cursor and conditions

[sud_plsql]

Hi Team,

I need to create a stored procedure and do find out the final amount for each fund_id.Expecting around 500 fund_id.Please find the below scenarios and help to see how i can achieve this.

Using oracle version 12.1.0
Create Table AVERAGE_BALANCE(
FUND_ID NUMBER
GL_CODE VARCHAR
TL_DT TIMESTAMP
PRIOR_DAY_AMT DOUBLE
TOTAL_ADJ_VAL DOUBLE
)

INSERT INTO AVERAGE_BALANCE(FUND_ID,GL_CODE,TL_DT,PRIOR_DAY_AMT,TOTAL_ADJ_VAL)VALUE
(10,’GE’,’14-JUN-21’,450,43),
(10,’GE’,’15-JUN-21’,350,53),
(10,’GE’,’30-MAY-21’,,110,5);

1.Find out MT_GL such a way that
Latest day value of PRIOR_DAY_AMT - last month recent value of TOTAL_ADJ_VAL
From above data
350 - 5 = 345 where
Recent day data is 350 and previous month TOTAL_ADJ_VAL is 5

2.
Create table TXN_HIST(
FUND_ID NUMBER,
SELL_DT TIMESTAMP,
TAX_CD varchar,
GAINLOSS DOUBLE
)

INSERT INTO TXN_HIST (FUND_ID,SELL_DT,TAX_CD,GAINLOSS) values(
10,’01-JUN-21’,’L’,11),
10,’02-JUN-21’,’L’,20),
10,’03-JUN-21’,’L’,23),
)
Find out all the GAINLOSS values for the fund_id where SELL_DT greater
Than prior period date (30-MAY-21)
In above example
11+20+23 = 54

3.
Create TABLE BASELINE(
FUND_ID NUMBER,
VERSION NUMBER,
SHORT_TERM_AMT DOUBLE,
LNG_TERM_AMT DOUBLE,
END_DT TIMESTAMP)
)
INSERT INTO BASELINE(FUND_ID,VERSION,SHORT_TERM_AMT,LNG_TERM_AMT,END_DT)
values(10,3,24,45,18-JUN-21),
(10,3,66,75,19-JUN-21);
FIND recent day data from baseline baseline =66

4.Calculate final amount

345+54+66 (from step1+step2+step3) =465
INSERT INTO OUTBOUND(FUND_ID,AMT) values (10,465);

 

[jhughes]

Also asked (and some feedback) athttps://community.oracle.com/tech/d...re-with-multiple-cursor-and-conditions#latest

 

[William Robertson]

This isn't really a question about the PL/SQL Developer product, is it?