NiceMan331
asked on
Serial Journal
i have 2 tables
monthly_serial
year number
month number
start number
close number(1) flag
then we have another table
serial_trans as the following
year number
month number
serial number
the purpose of them is to give each user a new serial
1st we set initial value of monthly_serial.start for jan as 1
then user when he log to the form serial_trans
he will fill in the values of year and month
if he select month = jan , then
1st transaction in serial_trans.serial = 1 for jan
next transaction will take 2 , then 3 , and so on
if user select month = feb ,
here feb not yet has any transaction
the code should check the flag value of closing jan
if it is true , the 1st transaction of feb will be continuios value after last transaction of jan
if jan not yet closed , then it will ask for initial starting value of feb
for example will select 500
here users will stay select eighther jan or feb and max serial for each month will be given
until jan close , suppose last entry is 490 as closing of jan
here the code should fill back the gap of jan to feb
means the new number for feb should be 491 till 499
then it will continue the next of february
sorry for long description , but any idea about it
monthly_serial
year number
month number
start number
close number(1) flag
then we have another table
serial_trans as the following
year number
month number
serial number
the purpose of them is to give each user a new serial
1st we set initial value of monthly_serial.start for jan as 1
then user when he log to the form serial_trans
he will fill in the values of year and month
if he select month = jan , then
1st transaction in serial_trans.serial = 1 for jan
next transaction will take 2 , then 3 , and so on
if user select month = feb ,
here feb not yet has any transaction
the code should check the flag value of closing jan
if it is true , the 1st transaction of feb will be continuios value after last transaction of jan
if jan not yet closed , then it will ask for initial starting value of feb
for example will select 500
here users will stay select eighther jan or feb and max serial for each month will be given
until jan close , suppose last entry is 490 as closing of jan
here the code should fill back the gap of jan to feb
means the new number for feb should be 491 till 499
then it will continue the next of february
sorry for long description , but any idea about it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
as long as i get no answer , i already design my function to return the required number
it is working fine now , but i found it contain too many if conditions ,
so if you could smooth it to reduce the if
it is working fine now , but i found it contain too many if conditions ,
so if you could smooth it to reduce the if
create or replace FUnCTION Ser_Mon(yr number,mn number) return number is
cursor cm is
select * from serial where year = yr and month = mn order by serial;
v_cm cm%rowtype;
ser number(30);
strt number(30);
lin number(30);
beg number;
en number(30);
en_prev number(30);
rec_count number(30);
BEGIN
select SER_START into strt from month_close where year = yr and month = mn;
select min(serial) into beg from serial where year = yr and month = mn;
select max(serial) into en from serial where year = yr and month = mn;
if month_open(yr,mn)=0 then ( function checked if month open or not )
ser := strt;
else
if mn = 1 or month_clos(yr,mn-1) = 0 THEN
select count(*) into rec_count from serial where year = yr and month = mn;
if en > beg + rec_count -1 then
lin :=0;
open cm;
loop
FETCH cm INTO v_cm;
EXIT WHEN cm%NOTFOUND;
lin := lin +1;
if v_cm.serial > beg + lin -1 then
ser := beg + lin -1;
return ser;
end if;
end loop;
close cm;
else
ser := en + 1;
end if;
end if;
if mn > 1 and month_clos(yr,mn-1) = 1 THEN
select max(serial) into en_prev from serial where year = yr and month = mn-1;
if beg > en_prev + 1 then
ser := en_prev+1;
else
ser := en + 1;
end if;
end if;
end if;
return ser;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
end ser_mon;
ASKER
Serial.xlsx