Solved

Serial Journal

Posted on 2014-01-30
3
253 Views
Last Modified: 2014-03-23
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
0
Comment
Question by:NiceMan331
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:NiceMan331
ID: 39820355
here is an example of the requirement
Serial.xlsx
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 39827751
the purpose of them is to give each user a new serial . . .
If "give each user" is correct, then perhaps you may want to consider this:

1) Create a sequence for each user and use like this:
-- Create the sequence
CREATE SEQUENCE User_Id_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE NOCYCLE;
--
-- Create a function to use it:
--
CREATE FUNCTION Get_Next_Seq ( p_User_Id IN VARCHAR2) 
   RETURN NUMBER 
   IS v_Sq NUMBER;
   BEGIN 
     EXECUTE IMMEDIATE '
         SELECT '||p_User_Id||'_seq.NEXTVAL FROM DUAL' INTO V_Sq;
      RETURN V_sq; 
    END;
/

Open in new window


2) Modify your monthly_serial table:
CREATE TABLE Monthly_Serial
(
  User_Id       VARCHAR2(18)
, Year 		NUMBER
, Month		NUMBER
, Start_No	NUMBER
, Close_No	NUMBER
, Status_Flag 	VARCHAR2(1)
);

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 39828787
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

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;
    

Open in new window

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Procedure syntax 5 50
setting local variables in a cursor block 3 30
plsql job on oracle 18 79
Oracle Query to Find number of process consumed by a session 15 54
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question