Hi All,
I am new to oracle procedures and I have a scenario here that i need your kind help. I have two tables say A ( Detail table) and B (summary table).
Transactions in table A fall into various months within a year. Table B has some fields defined to correspond to a particular month ( as illustrated below). I want a procedure that will fetch records from Table A and update Table B such that the amounts for say month 1 will be summed up in YTDBAL_1, month 2 will be the sum of month 1 plus sum of month 2 (YTDBAL2)..and so on such that the consecutive month will hold cumulative balances from beginning of year to date (current month).
Sample Data in Table A
OFFCD GLHEAD YEAR MONTH AMOUNT
010 10101001 2014 01 25,000.00
010 10101001 2014 01 5,000.00
010 10101001 2014 02 8,000.00
010 10101001 2014 02 4,000.00
010 10101001 2014 03 10,000.00
010 10101001 2014 04 10,000.00
010 10101001 2014 05 -4,000.00
010 10101001 2014 05 3,000.00
..
..
010 10101001 2014 12 1,000.00
Expected data in Table B
OFFCD GLHEAD YEAR YTDBAL_1 YTDBAL_2 YTDBAL_3 YTDBAL_4 YTDBAL_5 ..... YTDBAL_12
010 10101001 2014 30.000 42,000 52,000 62,000 61,000 XXX
My procedure below was populating all fields with same amount. How do i make it such that i arrive at above expected result.
//**********Start of Precedure*****//
CREATE OR REPLACE PROCEDURE rectify_gl_bal(w_year Number)
IS
CURSOR gl_cur IS
Select * from nlmstdtl
where period_year=w_year;
TYPE gl_array IS TABLE OF gl_cur%ROWTYPE;
gldtl gl_array;
Begin
open gl_cur;
Loop
Fetch gl_cur bulk collect into gldtl limit 500;
EXIT WHEN gldtl.count = 0;
Forall x in 1..gldtl.count
update nlmst set
NLMST.YTDBAL_1 = NLMST.YTDBAL_1 + gldtl(x).amount,
NLMST.YTDBAL_2 = NLMST.YTDBAL_2 + gldtl(x).amount,
NLMST.YTDBAL_3 = NLMST.YTDBAL_3 + gldtl(x).amount,
NLMST.YTDBAL_4 = NLMST.YTDBAL_4 + gldtl(x).amount,
NLMST.YTDBAL_5 = NLMST.YTDBAL_5 + gldtl(x).amount,
NLMST.YTDBAL_6 = NLMST.YTDBAL_6 + gldtl(x).amount,
NLMST.YTDBAL_7 = NLMST.YTDBAL_7 + gldtl(x).amount,
NLMST.YTDBAL_8 = NLMST.YTDBAL_8 + gldtl(x).amount,
NLMST.YTDBAL_9 = NLMST.YTDBAL_9 + gldtl(x).amount,
NLMST.YTDBAL_10 = NLMST.YTDBAL_10 + gldtl(x).amount,
NLMST.YTDBAL_11 = NLMST.YTDBAL_11 + gldtl(x).amount,
NLMST.YTDBAL_12 = NLMST.YTDBAL_12 + gldtl(x).amount
where nlmst.offcd = gldtl(x).offcd and
nlmst.period_year = gldtl(x).period_year and
nlmst.glhead = gldtl(x).glhead;
End Loop;
commit;
close gl_cur;
End;
//****End of Procedure****//