Following is the code of my SP which is long but very simple...
procedure apptrack_ware_funding_retrieve
(
al_seller_id IN NUMBER,
al_julian_id_num IN NUMBER,
as_pgm_type_cde_uw_out OUT VARCHAR2,
as_trust_acct_ind_out OUT VARCHAR2,
af_tran_amt_sum_out OUT NUMBER,
as_updt_dt_tm OUT VARCHAR2,
al_traunch_use_out OUT NUMBER,
al_whse_line_lmt_out OUT NUMBER,
as_rpt_end_dt_out OUT VARCHAR2,
al_pay_down_amt_out OUT NUMBER,
al_tot_trust_acct_adva_out OUT NUMBER, /* Date:07/21/2000 .Added By Sunil */
al_tot_trust_acct_reversal_out OUT NUMBER, /* Date:07/21/2000 .Added By Sunil */
al_trust_reversal_amt_out OUT NUMBER
)
AS
sql_err NUMBER;
err_msg VARCHAR2(300);
li_row_count NUMBER;
BEGIN
select count(1) into li_row_count
from TRUST_ACCT_ACTIVITY
where SEQ_NUM = (select max(SEQ_NUM) from TRUST_ACCT_ACTIVITY
where SELLER_ID = al_seller_id and
JULIAN_ID_NUM = al_julian_id_num) and TRAN_CDE = 'R';
if li_row_count = 1 then
select TRAN_AMT into al_trust_reversal_amt_out
from TRUST_ACCT_ACTIVITY
where SEQ_NUM = (select max(SEQ_NUM) from TRUST_ACCT_ACTIVITY
where SELLER_ID = al_seller_id and
JULIAN_ID_NUM = al_julian_id_num) and TRAN_CDE = 'R';
else
al_trust_reversal_amt_out := 0;
end if;
/* Anthony Start 07/06/2000.*/
select count(1) into li_row_count
from seller_pay_down
where (seller_id = al_seller_id and
julian_id_num = al_julian_id_num );
if li_row_count > 0 then
select sum(pay_down_amt) into al_pay_down_amt_out
from seller_pay_down
where (seller_id = al_seller_id and julian_id_num = al_julian_id_num);
else
al_pay_down_amt_out := 0;
end if;
/* Anthony End. 07/06/2000.*/
/* Dhawal changes begin. 06/22/2000.*/
select count(1) into li_row_count
from seller
where seller_id = al_seller_id;
if li_row_count = 1 then
select to_char(max(rpt_end_dt),'MM/DD/YYYY') into as_rpt_end_dt_out
from seller_settlement_summary
where seller_id = al_seller_id;
end if;
/* Dhawal changes end. 06/22/2000.*/
select count(1)
into li_row_count
from seller_traunch
where seller_id = al_seller_id;
if li_row_count > 0 then
select sum(traunch_use_lmt) into al_traunch_use_out from seller_traunch
where seller_id = al_seller_id and
traunch_type||eff_dt in (select traunch_type||max(eff_dt)
from (select * from seller_traunch where seller_id = al_seller_id)
group by traunch_type);
end if;
select count(1)
into li_row_count
from seller
where seller_id = al_seller_id;
if li_row_count > 0 then
select whse_line_lmt
into al_whse_line_lmt_out
from seller
where seller_id = al_seller_id;
end if;
select count(1) into li_row_count
from underwriting_info
where (julian_id_num = al_julian_id_num
and loan_type = '08'
and preflite_ind ='P');
if li_row_count = 1 then
select pgm_type_cde_uw into
as_pgm_type_cde_uw_out
from underwriting_info
where (julian_id_num = al_julian_id_num
and loan_type = '08'
and preflite_ind ='P');
end if;
select count(1) into li_row_count
from seller
where seller_id = al_seller_id;
if li_row_count = 1 then
select trust_acct_ind into
as_trust_acct_ind_out
from seller
where seller_id = al_seller_id;
end if;
select nvl(sum(tran_amt),0) into
af_tran_amt_sum_out
from trust_acct_activity
where seller_id = al_seller_id;
/* Date:07/21/2000 .Added By Sunil to calculate total of all trust account advances for seller id */
select nvl(sum(tran_amt),0) into
al_tot_trust_acct_adva_out
from trust_acct_activity
where seller_id =al_seller_id
and julian_id_num = al_julian_id_num
and tran_cde = 'F' ;
/* End of Adddition -------- Sunil. */
/* Date:07/21/2000 .Added By Sunil to calculate total of all trust account reversals for seller id */
select nvl(sum(tran_amt),0) into
al_tot_trust_acct_reversal_out
from trust_acct_activity
where seller_id =al_seller_id
and julian_id_num = al_julian_id_num
and tran_cde = 'R';
/* End of Adddition -------- Sunil. */
select count(1) into li_row_count
from trust_acct_activity
where seller_id = al_seller_id
and julian_id_num = al_julian_id_num
and tran_cde = 'F'
and seq_num = (select max(seq_num) from trust_acct_activity
where seller_id = al_seller_id
and julian_id_num = al_julian_id_num
and tran_cde = 'F');
if li_row_count = 1 then
select to_char(updt_dt_tm,'MM/DD/YYYY HH24:MI:SS') into as_updt_dt_tm
from trust_acct_activity
where seller_id = al_seller_id
and julian_id_num = al_julian_id_num
and tran_cde = 'F'
and seq_num = (select max(seq_num) from trust_acct_activity
where seller_id = al_seller_id
and julian_id_num = al_julian_id_num
and tran_cde = 'F');
end if;
dbms_output.put_line ('program Type Code UW = ' || as_pgm_type_cde_uw_out);
dbms_output.put_line ('Trust Account Indicator = ' || as_trust_acct_ind_out);
dbms_output.put_line ('Trust Account Current Balance = ' || af_tran_amt_sum_out);
dbms_output.put_line ('Report End Date = ' || as_rpt_end_dt_out);
EXCEPTION
when others then
sql_err := sqlcode;
err_msg := sqlerrm;
DBMS_OUTPUT.PUT_LINE (sql_err);
DBMS_OUTPUT.PUT_line (err_msg);
APPTRACK_UPDATE_ERRLOG('OTHERS', SQLCODE, SQLERRM, 'apptrack_ware_funding_retrieve', 'JULIAN_ID_NUM='||TO_CHAR(NVL(al_julian_id_num, 0)));
raise_application_error(-20011,'Database Error - Database Error code = '||sql_err||', Database Error Text = '||err_msg);
END apptrack_ware_funding_retrieve;
it give me following error:
ORA-06553: PLS-707: unsupported construct or internal error [2601]
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
After some R&D i found out that this SP gets invalid(or something else i don't know what) after some specific time say 10 min or 15 min...
it seems the error is dependent on time... Its Strange but.. i don't know maybe some settings or something is wrong....
