create or replace procdure test
(
ais_type in varchar2,
ais_subtype in varchar2,
ais_report_chdate in date,
ais_app_code in varchar2,
aos_status out varchar2
)
is
bad_view_name exception;
ls_putline_return varchar2(20);
ls_app_msg app_errors.app_msg%type;
ls_app_id app_errors.app_id%type;
ln_err_num app_errors.app_id%type;
ln_err_msg app_errors.err_msg%type;
ln_reload number:=0;
ls_month varchar2(2);
ls_year varchar2(4);
ls_rec_type mon_ftp.rec_type%type;
ls_prin_field varchar2(30);
ls_user mon_ftp.last_mod_by%type;
ld_sysdate mon_ftp.last_mod_date%type;
ls_sql_ins varchar2(5000);
ls_sql_upd varchar2(5000);
ls_sql_ins_off varchar2(5000);
ls_sql_upd_off varchar2(5000);
gs_proc_name varchar2(100);
gs_proc_args varchar2(100);
gs_app_id number;
gs_log_file varchar2(5000);
gn_restart_cntr number;
ln_recs number;
ln_rows number;
ln_putline_return number;
ln_sleep_cnt number:=0;
ln_sleep_time number:=0;
ls_ok_to_run_flag varchar2(1);
deadlock_detected exception;
pragma exception_init(deadlock detected,00060);
not_ok_to_run exception;
pragma exception_init(not_ok_to_run,00160);
begin
	gs_proc_name:='upd_mon_reg_4';
                 gs_proc_args:='('  ||
                 ais_type:=||','||
                 ais_subtype:=||','||
	aid_rept_date:=||','||
	ais_app_code||')';
                  gs_app_id:=substr(gs_proc_name||gs_proc_args,1,250)
                  IF gn_restart_cntr=0 then
                          ls_app_msg:='Beginning:'||gs_app_id;
                          gs_log_file:=gs_app_id;
                          gs_log_file:=gs_proc_name||'.'||ais_app_code||'.'||
                         to_char(sysdate,'yyyymmdd.hh24.mi')||'dog';
                 END IF;
               ls_app_msg:=selecting from app_contol where type in(ftp_sum_bal_sleep_time,
                                        ftp_sum_bal_max_sleeps);
                sp_log(gs_log_file,ls_app_msg);
               select max(decode(type,'ftp_sum_bal_sleep_time',to_number(app_value),0)),ly
                         max(decode(type,'ftp_sum_bal_max_sleeps',to_number(app_value),0))
		from app_control
                                        where type in('ftp_sum_bal_sleep_time','ftp_sum_bal_max_sleeps');

            if nvl(ln_sleep_time,0)=0 or nvl(ln_sleep_cnt,0)=0
                   then raise no_data_found;
            end if;
            if gn_restart_ctr=0 then
             sp_log(gs_log_file,ls_app_msg);
            end if;
             gn_restart_cntr=gn_restart_cntr+1;
             begin
                    select distinct decode(subtype,'s380_loan_extract',decode(status,'not_now','N','Y'),
                                            decode(status,'NOT now','N','Y'),
                                                                  s33_loan_extract',decode(status,'not_now','N','Y'),
                                            decode(status,'NOT now','N','Y')
                                           into ls_ok_to_run_flag
                                            from app_nightly_cycle
                                            where type='REG_EXTRACTS' and subtype in('S380_LOAN_EXTRACT',
                                                                    S337_LOAN_EXTRACT')                                                   
                                            exception when no_data_found then null;
               end;
                     if ls_ok_to_run_flag is null
                      then 
                      if ls_ok_to_run_flag:='Y';
                      end if;
                     if ls_ok_to_run_flag='N'
                     then raise not_ok_to_run;
                     end if;
                     exception
                       when deadlock_detected then
                             if(gn_restart_cntr<=ln_sleep_cnt)
                              then 
                                   ln_err_num:=sqlcode;
                             ls_app_msg:='sleep#''||to_char(gn_restart_cntr)||'of max' ||
                                                    to_char(ln_sleep_cnt);
                              spu_log_errors(ln_err_num,ls_err_msg,ls_app_id,ls_app_msg);
                               sp_log(gs_log_file,ls_app_msg);
                                dbms_lock.sleep(ln_sleep_time);
                                upd_mon_reg_4_extracts(ais_type,ais_subtype,ais_rep_date,
                                                                          ais_app_code,aos_status);
                                 end if;
                                  raise;
                              when not_ok_to_run then
                                      if(gn_restart_cntr<=ln_sleep_cnt)
                                      then
                                             ln_err_num=-20000;    
                                              ls_err_msg:='INFO ONLY:'||'IN user
                                               defined exception not ok_to_run';
                                               ls_app_msg:='sleep#'||to_char(gn_restart_cntr)||'of max'||
                                                                        to_char(ln_sleep_cnt);
                                                spu_log_errors(ln_err_num,ls_err_msg,ls_app_id,ls_app_msg);
                                                 sp_log(gs_log_file,ls_app_msg);
                                                  dbms_lock.sleep(ln_sleep_time);
                                                   test(ais_type,ais_subtype,ais_rept_date,ais_app_code,aos_status);
                                                    else
                                                        rollback;
                                                     ln_err_num:=-20000;
                                                      ls_err_msg:='user defined error not_ok_to_run:slept max number');
                                                      ls_app_msg:='Failure max sleeps reached while waiting for iteration to finish';
                                                       spu_log_errors(ln_err_num,ls_err_msg,ls_app_id,ls_app_msg);
                                                        sp_log(gs_log_file,ls_err_msg);
                                                        aos_status:=substr(ls_err_msg,1,100);
                                              end if;
                                            raise
                                            when others then rollback;
                                             ln_err_num:=sqlcode;
                                              ls_err_msg:=substr(sqlrrm,1,100);
                                              spu_log_errors(ln_err_num,ls_err_mesg,ls_app_id,ls_app_msg);
                                               aos_status:=substr(ls_err_msg,1,100);
                                            end if;
                                            raise;
                                            when others then
                                               rollback;
                                            ln_err_num:=sqlcode;
                                            ls_err_msg:=substr(sqlerrm,1,100);
                                            spu_log_errors(ln_err_num,ls_err_msg,ls_app_id,ls_app_msg);
                                             sp_log(gs_log_file,ls_err_msg);
                                              aos_status:=substr(ls_err_msg,1,100);
end  test;