Oracle Form trigger to filter detail block

hi
i have form has , master & detail blocks
both of them based on : select from clause query
i tried to manually build a elationship between them
but when i try to run the form it sent me error and open the trigger of relationship that is incorrect
then , i design this procedure and call it on : when_mouse_click of the master

PROCEDURE Upd_Pur IS
sql_string varchar2(8000);
bEGIN
           
go_block('pur_det');
clear_block;

sql_string := '(SELECT  h2002.pur_invh.inv_no,h2002.pur_invh.inv_no2,h2002.pur_invh.inv_date, h2002.pur_invh.discount, h2002.pur_invh.net_amount, h2002.pur_invh.sup_no, h2002.pur_invd.exp_code, h2002.pur_invd.cost_center, h2002.pur_invh.INV_DESC, h2002.pur_invd.qty, h2002.PUR_EXP.acc_no  '     
            || ' ,h2002.pur_invd.cpur_price, (h2002.pur_invd.qty *  h2002.pur_invd.cpur_price ) amount, h2002.pur_invd.discount sub_discount FROM h2002.pur_invh , h2002.pur_invd ,h2002.PUR_EXP WHERE h2002.pur_invh.inv_date = h2002.pur_invd.inv_date  AND h2002.pur_invh.inv_no = h2002.pur_invd.inv_no and h2002.pur_invd.exp_code = h2002.PUR_EXP.exp_code and '
       --      || ' h2002.pur_invh.inv_date >= to_date(''' || to_char(:BASIC_BLOCK.D_from,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and h2002.pur_invh.inv_date <= to_date(''' || to_char(:BASIC_BLOCK.D_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and h2002.PUR_EXP.acc_no = '|| :pur_summary.acc_no || ' and h2002.pur_invd.cost_center between  ' ||:BASIC_BLOCK.cntr_frm|| ' and ' ||:BASIC_BLOCK.cntr_to|| ' and h2002.pur_invh.sup_no = ' ||:pur_summary.sup_no|| ')';

 || ' h2002.pur_invh.inv_date >= to_date(''' || to_char(:BASIC_BLOCK.D_from,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and h2002.pur_invh.inv_date <= to_date(''' || to_char(:BASIC_BLOCK.D_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and h2002.PUR_EXP.acc_no = '|| :pur_summary.acc_no || ' and h2002.pur_invd.cost_center = ' ||:pur_summary.cost_center|| ' and h2002.pur_invh.sup_no = ' ||:pur_summary.sup_no|| ')';
 
set_block_property('pur_det',QUERY_DATA_SOURCE_NAME,sql_string);
execute_query;
go_block('pur_summary');
END;

Open in new window


pur_det is the detail
pur-summary is the master
and it works well
but when i call it on : key_up & key_down , the key not works
any one have a solution for it , or i should open a new topic ?
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
the key not works ?
is the key_down giving an error (what error)
is the key_down calling the procedure
is the key_down trigger succesfully executed but
   the detailblock shows no records
   or
   the detailblock still shows the records belonging to the first record (and what happens if you go to the 3e masterrecord)

and (maybe i missed the question) why not correct  the trigger of relationship if that is incorrect ?
0
NiceMan331Author Commented:
No , the master block not able to navigate between its records using key up or down
0
NiceMan331Author Commented:
When I removed the trigger , it continue navigating but without filtering
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

flow01Commented:
What happens if you move the procedure call to the when_new_record_instance trigger of the master block ?
key-down  will by default call the next_record  build-in and that is a restricted procedure.
In a restricted procedure some actions are not permitted (for example new navigation).
I would expect an error message, but maybe the trigger just fails.
Add a
message('start upd_pur',acknowledge);
message('end upd_pur',acknowledge);
at the beginning and end of upd_pur to verify the procedure runs
and/or add
exception when form_trigger_failure then
   message('fail:' || error_text || message_text,acknowledge);
exception when others then
   message('others:' || error_text || message_text || sqlerrm,acknowledge);
to debug the reason the procedure does not end
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
What happens if you move the procedure call to the when_new_record_instance trigger of the master block ?
yes , now it is working correct  , i thought when_new_record_instance means when going to insert new record , i didn't know that it means also next existing record
and (maybe i missed the question) why not correct  the trigger of relationship if that is incorrect ?
this trigger created by the form once i created the relationship manually , and i don't know what is wrong with it or how to correct it
0
NiceMan331Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.