SQL & PL/SQL

Hi Friends,

I have one requirement from my client.

let me explain u the scenario..

if a person is apply for a loan . when he clicks on the submit button it goes to the approvers.. it means it will go tho the transacation tables.
my requirement is if a person applies for the same loan two times before any action taken place for the previous applied loan .. then it should stop the user moving forward.saying your previous loan is pending for approval.

Technical:-

i wrote one package for it.. and called it in a trigger for validation


create or replace package body xx_transcation_pkg is
function get_trans(p_person_id in number)
return varchar2
is
cursor c1 is
SELECT htr.transaction_id
FROM per_analysis_criteria pac,
hr_api_transactions htr,
hr_api_transaction_steps hts,
hr_api_transaction_values htrv
WHERE htr.transaction_id = hts.transaction_id
AND hts.transaction_step_id = htrv.transaction_step_id
AND htrv.NAME LIKE 'P_ANALYSIS_CRITERIA_ID%'
AND htr.process_name = 'VEHICLE_LOAN'
AND htr.selected_person_id = TO_NUMBER (p_person_id)
AND pac.analysis_criteria_id = htrv.number_value
AND htr.status = 'Y'
AND hts.transaction_step_id =
(SELECT hts.transaction_step_id
FROM hr_api_transaction_values htv,
hr_api_transaction_steps hts
WHERE htv.transaction_step_id = hts.transaction_step_id
AND hts.transaction_id = htr.transaction_id
AND htv.varchar2_value IN ('INSERT', 'UPDATE'))
GROUP BY htr.transaction_id;
--variable declaration
l_value number;
begin
for i in c1
loop
l_value:=i.transaction_id;
if
l_value is not null
then
return('Flag1');
else
return('Flag2');
end if;
end loop;
end;
end xx_transcation_pkg;


and my trigger is

CREATE OR REPLACE TRIGGER TEST_RL
BEFORE DELETE OR INSERT OR UPDATE
ON PER_ANALYSIS_CRITERIA REFERENCING NEW AS N OLD AS O
FOR EACH ROW
declare
p_person_id number;
v_id_flex_num number;
l_flag varchar2(100);
begin
v_id_flex_num := (:n.ID_FLEX_NUM);
if
v_id_flex_num=50430
then
p_person_id:=(:n.segment30);
begin
select xx_transcation_pkg.GET_TRANS(p_person_id) into
l_flag from dual ;
end;
if
l_flag='Flag1' then
raise_application_error(-20002,'Your Previous Loan Application is pending for Approval');
end if;
end if;
end TEST_RL;



Now my problem is

This Trigger is not working in the scenarios

i) suppose person applies for a loan today and once again applies the same loan without changing anydata in the fileds then trigger is not validating.. transacation is taking place..

eg:- person:- xx
loan amount:-200
click on submit it goes to approver

second time person:- xx
loan amount:- 200
click on submit it goes to approver


Trigger is working in the scenarios

eg :- person:- xx
loan amount :- 200
click on submt it goes to approver

second time person:- xx
loan amount :- 210
click on submit ( error message :- Your Previous Loan Application is pending for approval).


Can anyone help me out, how to handle this

Thanks in advnace