RANGE UNBOUNDED

select
gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
ehrs_cum,
gpa_hrs gpa_hrs1,
nod_gpahrs GPA_HRS2,
to_char(avg(GPA),'fm99990.00') GPA,
to_char(nvl(avg(pts),0),'fm999999990.00') pts,
to_char(avg(GPA2),'fm999990.00') GPA2,
shrttrm_astd_code_end_of_term,
to_char(SUM(ehrs_cum) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') ehrs_cum_ext,
to_char(SUM(gpa_hrs) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') gpa_hrs_ext,
to_char(SUM(avg(GPA)) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') gpa_ext,
to_char(SUM(avg(pts)) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') pts_ext,
shrttrm_astd_code_dl
from (
select
 distinct gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
to_char(round(ehrs_cum),'fm999990.00') ehrs_cum,
to_char(round(gpa_hrs),'fm999990.00') gpa_hrs,
to_char(gpa,'fm999990.00') gpa,
pts pts,
to_char(round(nod_gpahrs),'fm999990.00')  nod_gpahrs,
tqpts,
to_char(GPA2,'fm999990.00')  GPA2,
shrttrm_astd_code_end_of_term,
shrttrm_astd_code_dl

from
(
select
gzrbipr_char_3 gwid,
gzrbipr_char_4 spidm,
gzrbipr_char_77 ctrm ,
SUBSTR(gzrbipr_char_77,instr(gzrbipr_char_77,' ',-1,1)+1) ctrm_yr,
(case when SUBSTR(gzrbipr_char_77,1,instr(gzrbipr_char_77,' ',-1,1)-1)='Spring' then 'a'
when SUBSTR(gzrbipr_char_77,1,instr(gzrbipr_char_77,' ',-1,1)-1)='Summer' then 'b'
when SUBSTR(gzrbipr_char_77,1,instr(gzrbipr_char_77,' ',-1,1)-1)='Fall' then 'c'
else null
end ) ctrm_sort,
gzrbipr_char_31 gacol ,
gzrbipr_char_36 gamaj11 ,
gzrbipr_char_88 ehrs_cum ,
nvl(gzrbipr_char_91,0)  gpa_hrs ,
nvl(gzrbipr_char_165,0) gpa ,
( case when gzrbipr_char_167='Y' THEN (case when gzrbipr_char_69 < '199303' then nvl(gzrbipr_char_166,0)  end)
  else nvl(gzrbipr_char_166,0)
  end)  pts,
nvl(gzrbipr_char_91,0) nod_gpahrs,
nvl(gzrbipr_char_166,0) tqpts
,
(case when gzrbipr_char_91>0 then gzrbipr_char_166/gzrbipr_char_91 end)GPA2,
    gzrbipr_char_82 shrttrm_astd_code_end_of_term,
  gzrbipr_char_83 shrttrm_astd_code_dl
from gzrbipr
)

group by
gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
ehrs_cum,
gpa_hrs,
gpa,
pts,
nod_gpahrs,
tqpts,
shrttrm_astd_code_end_of_term,
shrttrm_astd_code_dl
,
GPA2
)
where gwid='G14759230'
Group by
gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
ehrs_cum,
gpa_hrs ,
nod_gpahrs,
shrttrm_astd_code_end_of_term,
shrttrm_astd_code_dl
order by ctrm_yr,ctrm_sort

UTL_FILE another Query

declare
             fileptr    utl_file.file_type;
             buffer    varchar2(2048);
             line_num number(10):=0;
             loc_no  integer;
             string_var varchar2(4000);
     begin
             fileptr:=utl_file.fopen('G_USER_DIR','sp_details.txt','R');
             loop
                     line_num:=line_num+1;
                     utl_file.get_line(fileptr,buffer);
                     --dbms_output.put_line(line_num||' '||buffer);
                     string_var:=buffer;
                     dbms_output.put_line(string_var);
                    
             end loop;
     exception
             when no_data_found then
                     utl_file.fclose(fileptr);
                     dbms_output.put_line('Number of lines parsed ='||line_num);
             when value_error then
                     dbms_output.put_line('value error');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_path  then
                     dbms_output.put_line('invalid path');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_mode  then
                     dbms_output.put_line('invalid_mode');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_filehandle  then
                     dbms_output.put_line('invalid_filehandle');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_operation  then
                     dbms_output.put_line('invalid_operation');
                     raise_application_error(-20100,'file error');
             when utl_file.read_error  then
                     dbms_output.put_line('read_error');
                     raise_application_error(-20100,'file error');
             when utl_file.write_error  then
                     dbms_output.put_line('write_error');
                     raise_application_error(-20100,'file error');
             when utl_file.internal_error  then
                     dbms_output.put_line('internal_error');
                     raise_application_error(-20100,'file error');
             when others then
                     dbms_output.put_line('un-handled');
                     raise_application_error(-20100,'file error');
     end;


-------------------------------------------------------------------

DECLARE
file1   UTL_FILE.file_type;
p_line VARCHAR2(4000);
BEGIN
file1 := UTL_FILE.fopen ('G_USER_DIR', 'sp_details.txt', 'w');
p_line := '19320418'||','||'18649335'||','||'18016235'||','||'14032419'||','||'10383346'||','||'13194228'||','||'18882026'||','||'11073020'||','||'19539731'||','||'16712789'||','||'10808330'||','||'14903288'||','||'17118027'||','||'15928693'||','||'11532136'||','||'12627057'||','||'19961984'||','||'19138862'||','||'11398161'||','||'16115467'||','||'14890139'||','||'11560049'||','||'14105816'||','||'17121334'||','||'19542285'||','||'11384858'||','||'17418279'||','||'13293065'||','||'14692220'||','||'11362967'||','||'10436234'||','||'19341379'||','||'16134493'||','||'15493206'||','||'15745525'||','||'15135785'||','||'16573746'||','||'13439175'||','||'16272382'||','||'11229845'||','||'12836326'||','||'11402844'||','||'13419723'||','||'19343730'||','||'11310078'||','||'18766875'||','||'10249762'||','||'10849323'||','||'16495481'||','||'15412637';
UTL_FILE.put_line (file1, p_line);
UTL_FILE.fclose (file1);
END;

UTL File

declare
             fileptr    utl_file.file_type;
             v_buffer    varchar2(2048);
             line_num number(10):=0;
             loc_no  integer;
             string_var varchar2(4000);
              CURSOR process_unix_ssn_c(unix_ssn VARCHAR2)
              IS
              SELECT sp ,
              sp s_id3
              FROM sa.sp
              WHERE sp_id        =unix_ssn
              AND sp_change_ind IS NULL;
              v_process_unix_ssn process_unix_ssn_c%rowtype ;
             
            
            
     begin
             fileptr:=utl_file.fopen('G_USER_DIR','sp_details.txt','R');
             loop
                     line_num:=line_num+1;
                     utl_file.get_line(fileptr,v_buffer);
                     --dbms_output.put_line(line_num||' '||buffer);
                     string_var:=v_buffer;
                     dbms_output.put_line(string_var);
                    
                 
                  OPEN process_unix_ssn_c(v_buffer);
                  LOOP
                  FETCH process_unix_ssn_c INTO v_process_unix_ssn;
                  EXIT
                  WHEN process_unix_ssn_c%notfound;
                 
                  dbms_output.put_line('s_pidm3: '||v_process_unix_ssn.s_pidm3||' s_id3 : '||v_process_unix_ssn.s_id3);
                  END LOOP;
                  CLOSE process_unix_ssn_c;
                    
             end loop;
     exception
             when no_data_found then
                     utl_file.fclose(fileptr);
                     dbms_output.put_line('Number of lines parsed ='||line_num);
             when value_error then
                     dbms_output.put_line('value error');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_path  then
                     dbms_output.put_line('invalid path');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_mode  then
                     dbms_output.put_line('invalid_mode');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_filehandle  then
                     dbms_output.put_line('invalid_filehandle');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_operation  then
                     dbms_output.put_line('invalid_operation');
                     raise_application_error(-20100,'file error');
             when utl_file.read_error  then
                     dbms_output.put_line('read_error');
                     raise_application_error(-20100,'file error');
             when utl_file.write_error  then
                     dbms_output.put_line('write_error');
                     raise_application_error(-20100,'file error');
             when utl_file.internal_error  then
                     dbms_output.put_line('internal_error');
                     raise_application_error(-20100,'file error');
             when others then
                     dbms_output.put_line('un-handled');
                     raise_application_error(-20100,'file error');
     end;
/

-------------------------------------------------------------------

DECLARE
file1   UTL_FILE.file_type;
p_line VARCHAR2(4000);
BEGIN
file1 := UTL_FILE.fopen ('GWU_USER_DIR', 'sp_details.txt', 'w');
--p_line := '19320418'||','||'18649335'||','||'18016235'||','||'14032419'||','||'10383346'||','||'13194228'||','||'18882026'||','||'11073020'||','||'19539731'||','||'16712789'||','||'10808330'||','||'14903288'||','||'17118027'||','||'15928693'||','||'11532136'||','||'12627057'||','||'19961984'||','||'19138862'||','||'11398161'||','||'16115467'||','||'14890139'||','||'11560049'||','||'14105816'||','||'17121334'||','||'19542285'||','||'11384858'||','||'17418279'||','||'13293065'||','||'14692220'||','||'11362967'||','||'10436234'||','||'19341379'||','||'16134493'||','||'15493206'||','||'15745525'||','||'15135785'||','||'16573746'||','||'13439175'||','||'16272382'||','||'11229845'||','||'12836326'||','||'11402844'||','||'13419723'||','||'19343730'||','||'11310078'||','||'18766875'||','||'10249762'||','||'10849323'||','||'16495481'||','||'15412637';
p_line :=   '
19320418
18649335
18016235
14032419
10383346
13194228
18882026
11073020
19539731
16712789
10808330
14903288
17118027
15928693
11532136
12627057
19961984
19138862
11398161
16115467
14890139
11560049
14105816
17121334
19542285
11384858
17418279
13293065
14692220
11362967
10436234
19341379
16134493
15493206
15745525
15135785
16573746
13439175
16272382
11229845
12836326
11402844
13419723
19343730
11310078
18766875
10249762
10849323
16495481
15412637';

UTL_FILE.put_line (file1, p_line);
UTL_FILE.fclose (file1);
END;

Cummulative process in rtf using set and get variable

Declare varable:
<?xdoxslt:set_variable($_XDOCTX, ‘c1’, 0)?>


Set variable(In for loop to repected loop) :

<?xdoxslt:set_variable($_XDOCTX, ‘c1’, xdoxslt:get_variable($_XDOCTX, ‘c1’) + EHRS_CUM)?>


Get Variable:
<?xdoxslt:get_variable($_XDOCTX, ‘c1’)?>

Date parameter in Standed Date in app

<?xdofx:to_date(replace(substr(P_FROM_DATE,1,10),’/’,’-’)||’T00:00:00.000-06:00’,'DD-MON-YYYY')?>

To Get text from Data source

select *
from user_source
where upper(text) like upper('%Enter_text_hear%');

Set Views in Obiee

BEGIN
  GLR03300_PKG.set_criteria (to_number(NULL),to_number(NULL),to_number(NULL),to_number(NULL),to_number(NULL), 'ALL123456789012345',NULL,NULL,NULL,'A',to_number(NULL), NULL,'ALL123456789012345');
END;