Oracle Forms - Hall Arranement - Even Seats

PROCEDURE HALL_O(
su_p number,
row1_p number,
c1_p varchar2,
x_p number,
sub1_p number,
c3_p number )IS

su number :=1;
sub number :=1;
row1 number :=1;
x number :=0;
c1 varchar2(100);

sub1 number;
c3 number;
c5 number;
    sub_total number:=0;
BEGIN
   
    :system.message_level := '20';
   
   
    delete from c_s_stage;
    delete from form_ex_stage;
    delete from form_ex_copy_temp;

--delete from form_ex_copy_temp;

 commit;

                sub1:=sub1_p;
              su:=su_p;
                row1 := 1;
                c1:= :block3.c1;
               
                     for i in (select * from compute_stage where rownum <=C1 order by sl_no) LOOP
                        :global.a:=:global.a+1;
                        sub_total:=sub_total+1;
                        x:=x+1;
                        sub1:=sub1+1;
                   
                        
                 insert into form_ex_copy (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P');
           
                     insert into theory_attendance (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent,s_branch,s_semester)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P',i.s_branch,i.s_semester);
                   
                   
                    insert into form_ex values (su,sub,sub1,i.regno,i.branch,i.sem,i.s_paper_name,
                  i.s_p_c,i.s_code,i.s_branch,i.s_semester,i.s_exam_date,i.s_sess_id,i.sname,NULL,'PHYSICS LAB');
          commit;
                                    end loop;
                                               
delete from compute_stage where regno=any(select regno from form_ex_copy);
commit;
           
                        
               
                sub:=3;
                sub1:=0;
                row1:=c1+1;
              c3:=c1+:block3.c1;
             
            for i in (select * from compute_stage where rownum <=C1 order by sl_no) LOOP
                     :global.a:=:global.a+1;
                            sub_total:=sub_total+1;
                        x:=x+1;
                        sub1:=sub1+1;
                   
                     insert into form_ex_copy_temp (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P');
                   
                 insert into form_ex_copy (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P');
           
                     insert into theory_attendance (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent,s_branch,s_semester)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P',i.s_branch,i.s_semester);
                       
                  insert into form_ex values (su,sub,sub1,i.regno,i.branch,i.sem,i.s_paper_name,
                  i.s_p_c,i.s_code,i.s_branch,i.s_semester,i.s_exam_date,i.s_sess_id,i.sname,NULL,'PHYSICS LAB');
                    commit;
                   
                end loop;
                           
delete from compute_stage where regno=any(select regno from form_ex_copy);
commit;
                   
                     /*

                    sub:=5;
                    sub1:=0;
                    row1:=c3+1;
                c5:=c3+:block3.c1;
       
            for i in (select * from (select a.*,rownum rnum from (select * from compute_stage ) a
                where rownum<=c5)
                where rnum >=row1 order by sl_no )loop
                               
                        :global.a:=:global.a+1;
                            sub_total:=sub_total+1;
                        x:=x+1;
                        sub1:=sub1+1;
               
                    insert into form_ex_copy_temp (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P');
               
                 insert into form_ex_copy_5 (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent)
                         values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P');
               
                     insert into theory_attendance (sl_no,a,b,regno,sname,branch,sem,paper_name,code,c_r,s_exam_date,s_sess_id,paper_category,signature,absent)
                        values (su,sub,sub1,i.regno,i.sname,i.branch,i.sem,i.s_paper_name,i.s_code,
                        :BLOCK3.A1,i.s_exam_date,i.s_sess_id,i.s_p_c,x,'P');
               
                        insert into form_ex values (su,sub,sub1,i.regno,i.branch,i.sem,i.s_paper_name,
                  i.s_p_c,i.s_code,i.s_branch,i.s_semester,i.s_exam_date,i.s_sess_id,i.sname,NULL,'PHYSICS LAB');
                commit;
               
                enD loop;
*/
               
                   
:block3.sql_statement := 'select * from compute_stage';
:block3.connected_as := 'nip@nip';
DYNAMIC.NEW_CONNECT;
DYNAMIC.EXECUTE_SQL;

   
           
           
       
declare
    qh number:=0;
begin
select count(*) into qh from compute_stage;
    :block3.qh := qh;
end;


declare
    qI number:=0;
begin
    select count(*) into qI from theory_attendance;
    :block3.qi := qi;
end;


declare
    x int:=0;
    k1 int:=0;
begin
   
   
hide_view('notice');
show_view('person');



for i in (SELECT C_R,
 ROW1||' '||COUNT(ROW1) X,
 ROW2||' '||COUNT(ROW2) Y,
 ROW3||' '||COUNT(ROW3) Z,
 ROW4||' '||COUNT(ROW4) P

    FROM (
  select C_R,
  max(decode(a,1,BRANCH)) ROW1,
  max(decode(a,2,BRANCH)) row2,
  max(decode(a,3,BRANCH)) row3,
  max(decode(a,4,BRANCH)) row4
 
  from
  (select * from form_ex_COPY where b<>0  )
  group by sl_no,b,c_r,s_exam_date,s_sess_id)
  group by C_R,row1,row2,row3,ROW4 ORDER BY X,Y,Z,P) loop
      x:=x+1;
      add_list_element ('block3.l',x,rpad(i.c_r,3)||' '||rpad(i.x,8)||' '||rpad(i.y,8)||' '||rpad(i.z,8)||' '||rpad(i.p,8),1);
  end loop;
 
 
 
 
  for k in (select distinct c_r from form_ex_copy) loop
      k1:=k1+1;
      add_list_element ('block3.item642',k1,k.c_r,k.c_r);
  end loop;
 
  :GLOBAL.B:=2;
 
 
  declare
    x int:=0;
    k1 int:=0;
    s int:=0;
    begin
clear_list ('block3.long');
for i in (SELECT C_R,
 ROW1||' '||COUNT(ROW1) X,
 ROW2||' '||COUNT(ROW2) Y,
 ROW3||' '||COUNT(ROW3) Z,
 ROW4||' '||COUNT(ROW4) P
   FROM (
  select substr(c_r,-3) c_r,
  max(decode(a,1,regno||' '||BRANCH)) ROW1,
  max(decode(a,2,regno||' '||BRANCH)) row2,
  max(decode(a,3,regno||' '||BRANCH)) row3,
  max(decode(a,4,regno||' '||BRANCH)) row4
  from
  (select * from form_ex_COPY where b<>0  and c_r = :block3.a1)
  group by sl_no,b,c_r,s_exam_date,s_sess_id)
  group by C_R,row1,row2,row3,ROW4 ORDER BY X,Y,Z,P) loop
      x:=x+1;
      add_list_element ('block3.long',x,rpad(i.c_r,3)||' '||rpad(i.x,17)||' '||rpad(i.y,17)||' '||rpad(i.z,17)||' '||rpad(i.p,17),1);
  end loop;
 
  select count(*) into s from form_ex_copy where c_r  = :block3.a1;
  x:=x+1;
      add_list_element ('block3.long',x,'Total Students : '||s,s);
 
  for k in (select distinct c_r from form_ex_copy where b<>0) loop
      k1:=k1+1;
      add_list_element ('block3.item642',k1,k.c_r,k.c_r);
  end loop;
 
end;

declare
    c int:=0;
begin
    for i in (select distinct c_r from form_ex_COPY where b<>0) loop
        c:=c+1;
            add_list_element ('block3.abs',c,i.c_r,i.c_r);
    end loop;
end;
end;


END;