Solved

Need major help - mutli-block canvas

Posted on 2003-12-10
26
1,119 Views
Last Modified: 2010-05-18
OK---i posted this problem earlier but we could not get it fixed.  I will give as much detail as I can think to give.  

Problem:  When I go from my checkin canvas to my guest_bill canvas, the tabular data block (guest_trans2) does not fill with the room service items.

Setup:  When at checkin canvas...hit checkout...takes data and places it in the Guest_Bill canvas.  The guest_bill canvas has three data blocks on it (Guest_Bill, Checkin1, Guest_Trans2).  Checkin1 fields are fname, lname which are filled from the previous canvas (this works).  Guest_Bill fields are Bill_ID, emp_num, depart_date, room_num, room_total, trans_total, tax, total.  depart_date, emp_num, room_num fill from the previous canvas (this works).  Guest_Trans2 is tabular with fields room_num, description, trans_date, rate.  

This is merely a list of all transactions from that room_num.  This is not populating.  It needs to populate from the guest_bill.room_num.

All data_block are control blocks (I think - Property -> Database Data Block = Yes)

I figured I need something like changing one from a control block to a data block...adding a where clause...and have it execute somehow.

I really need some help here

rbg
0
Comment
Question by:rbgerman
  • 14
  • 12
26 Comments
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
a master-detail relation should be created between blocks Checkin1 (master) and Guest_Trans2 (detail) with the following condition

Guest_Trans.room_num = Checkin1.room_num

also, make sure that Defer Coordination should be set to false so that when block Checkin1 is queried, block Guest_Trans2 also get queried.
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
i think i need Guest_Bill as the master so it would be

GUEST_TRANS.room_num = Guest_Bill.room_num

do I need to change the Database Data Block properties in any of the data block...and i am not understanding what you mean by Defer Coordination.  

I will give it a try, but I was having trouble creating the relationship in the Data Block Wizard.  I do the Data Block Wizard to create the relationship in the Guest_Bill data block??????

rbg
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
my mistake and you're right.  the master block should be Guest_Bill.
to create a relation, just expand the block and put focus on the relation node. then click the (+) or create button to create a relation.  then (for the join condition) enter

GUEST_TRANS.room_num = Guest_Bill.room_num

that should be it.  by default, Defer Coordination should be set to false; so don't worry about it

0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
ok...i am at work and not school right now...but I sure hope this works.  i will find our around 5.  thanks again.  i will ask another question if i have one

rbg
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
by the way, you should not ask homework questions here; it's against the forum's policy.
i know i am not in any position to give advices, but you should try to do the homework yourself so that you would learn more that way.
good luck
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
it is not a HW question...it is December

semester is over

and i am graduating

i understand though
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
my apology
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
no problem...this week was finals and tonight is my last one

graduating and growing up

i try your suggestion when I get in front of my oracle server

rbg
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
i still have a problem.

I set the relation with the Where clause.  It gives me an error when trying to run the form:

FRM-30415: Relation's detail block is a control block
Relation: Guest_Bill_Guest_Trans2
Block: Guest_BIll
Form: Hotel
FRM-30085: Unable to adjust form for output

what is that

rbg
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
alright

i got it working with a master-detail block.  now my update table will not work.  is that because I had to change it to a control block from a database detail block???

what is the deal here


rbg
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
what do you mean by "my update table will not work"?
which block is your "update table"? can you be more specific or explan on what you were trying to do?
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
ok--this is where I am now.  I have moved my two command buttons from the guest_bill data block because it's property for Database Data Block was set to Yes b/c it was the master block; therefore, it would not update the tables.  The first button calculate works and updates, but the second table does not.  Now, I moved them to the Checkin1 data block...see if you can find some error in my code.  i will write down my errors when I run my form again and post them.  here is my code:

      --update guest_bill table
      insert into guest_bill values
      (:guest_bill.bill_id,
      :guest_bill.checkin_num,
      :guest_bill.bill_date,
      :guest_bill.room_num,
      :guest_bill.trans_total,
      :guest_bill.room_total,
      :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
      
      --delete from checkin table
      delete from checkin
  where checkin_num = :guest_bill.checkin_num;
      
      --delete guest from guest table
      delete from guests
      where fname = :checkin1.fname
      and lname = :checkin1.lname;

commit;

display_update_alert;
clear_guest_bill_form;

i have tried them individually and none of them work.

thanks rbg
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
here is my error for all three actions (insert and two deletes)

FRM-40657: REcord changed or delete by another user

it shows it three times;

rbg
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
why do you have to move the buttons to a different block?  Making the block as a base table block does not affect the trigger associated to the buttons.
what do you mean by "therefore, it would not update the tables.  The first button calculate works and updates, but the second table does not"????  which table and which block that "does not" work?
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
ok. let me try to understand your form.

"Setup:  When at checkin canvas...hit checkout...takes data and places it in the Guest_Bill canvas.  The guest_bill canvas has three data blocks on it (Guest_Bill, Checkin1, Guest_Trans2).  Checkin1 fields are fname, lname which are filled from the previous canvas (this works).  Guest_Bill fields are Bill_ID, emp_num, depart_date, room_num, room_total, trans_total, tax, total.  depart_date, emp_num, room_num fill from the previous canvas (this works).  Guest_Trans2 is tabular with fields room_num, description, trans_date, rate. "

what do you have in the when-button-pressed trigger when the user "hit checkout"?  How do you "takes data and places it in the Guest_Bill canv"?  
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
if :checkin.checkin_num is not null then
      go_item('guest_bill.emp_num');
      list_values;
      
      --insert values in fields
      select fname, lname, checkin_num, room_num
      into :checkin1.fname, :checkin1.lname, :guest_bill.checkin_num, :guest_bill.room_num
      from checkin
      where checkin_num = :checkin.checkin_num;
      
      go_block('GUEST_TRANS1');
      execute_query;
      
      go_item('guest_bill.bill_date');

else
      clear_checkin_form;
      go_item('checkin.checkin_num');
      list_values;
end if;
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
this is what will not work for some reason unknown to me...

      --update guest_bill table
      insert into guest_bill values
      (:guest_bill.bill_id,
      :guest_bill.checkin_num,
      :guest_bill.bill_date,
      :guest_bill.room_num,
      :guest_bill.trans_total,
      :guest_bill.room_total,
      :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
      
      --delete from checkin table
      delete from checkin
  where checkin_num = :guest_bill.checkin_num;
      
      --delete guest from guest table
      delete from guests
      where fname = :checkin1.fname
      and lname = :checkin1.lname;

commit;

display_update_alert;
clear_guest_bill_form;


i dont know if constraints or something are holding it up...not sure

please help..that is all I need to finish this

rbg
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
first of all, once you make block GUEST_BILL a base table block, you don't have to manually insert data into table guest_bill using INSERT INTO statement.  All you have to do is commit_form; and Oracle Forms will save whatever populated in the block into the database.

Second, why and what are you trying to do this?

     
     --delete from checkin table
     delete from checkin
  where checkin_num = :guest_bill.checkin_num;
     
     --delete guest from guest table
     delete from guests
     where fname = :checkin1.fname
     and lname = :checkin1.lname;


did you post; or commit; the changes on canvas CHECKIN prior to navigating to GUEST_BILL canvas?

you can replace both delete statements with,

go_block('checkin'); delete_record;
go_block('checkin1'); delete_record;


0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
i guess you just have to replace the following

"
     --update guest_bill table
     insert into guest_bill values
     (:guest_bill.bill_id,
     :guest_bill.checkin_num,
     :guest_bill.bill_date,
     :guest_bill.room_num,
     :guest_bill.trans_total,
     :guest_bill.room_total,
     :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
     
     --delete from checkin table
     delete from checkin
  where checkin_num = :guest_bill.checkin_num;
     
     --delete guest from guest table
     delete from guests
     where fname = :checkin1.fname
     and lname = :checkin1.lname;

commit;

display_update_alert;
clear_guest_bill_form;

"

with

"

     --update guest_bill table
     -- automatic with forms
   
     --delete from checkin table
     go_block('checkin'); delete_record;
     
     --delete guest from guest table
     go_block('checkin1'); delete_record;

commit;

display_update_alert;
clear_guest_bill_form;

"
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
i read this
FRM-40657:  Record changed or deleted by another user.

 Cause:      Another user has deleted the record since the query was executed.

Action:      You can clear this record from your screen, but you cannot update or delete it since it no longer exists in the database.

Level:      20

Type:      Informative

also...i do not see why at least this would work

      --update guest_bill table
      insert into guest_bill values
      (:guest_bill.bill_id,
      :guest_bill.checkin_num,
      :guest_bill.bill_date,
      :guest_bill.room_num,
      :guest_bill.trans_total,
      :guest_bill.room_total,
      :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);

commit;

display_update_alert;
clear_guest_bill_form;

0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
do you have primary key on table guest_bill?  i assume that bill_id is the primary key and it is populated in the form automatically via sequence?
i don't know why

"     --update guest_bill table
     insert into guest_bill values
     (:guest_bill.bill_id,
     :guest_bill.checkin_num,
     :guest_bill.bill_date,
     :guest_bill.room_num,
     :guest_bill.trans_total,
     :guest_bill.room_total,
     :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
"
works now.  i thought that it did not work before.  if it worked, make sure that you don't insert two (2) records into the database; one by the form, the other by your insert statement.

honestly, i don't know what you are trying to do with the two delete statement.
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
i think it may be trying to insert it twice....how does it do it from the form automatically and how can i stop that.  

it's a hotel system....so when you check_out...you should be deleted from the checkin_table and from the guests_table
0
 
LVL 5

Accepted Solution

by:
FBIAGENT earned 50 total points
Comment Utility
"how does it do it from the form automatically and how can i stop that"

create on-insert trigger in the block that you do not want oracle forms to insert automatically
in the on-insert trigger, put insert_record; if you want to perform insertion; otherwise, just put null;
in the on-insert trigger.

"it's a hotel system....so when you check_out...you should be deleted from the checkin_table and from the guests_table "

i don't know who design the database.  but when a customer check out, i would not want to delete records from that table.  why?  just to keep track of history of the customer; like how often, and when the customer had been in the hotel, etc.

but if you want to delete the record, navigate to the block and issue delete_record; instead of using delete statement.

it looks like you are trying to do many of the default functionalities that inherently comes with Oracle Forms.
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
ok...i think we are good here.  i want it to delete from checkin...there is no check_out

the history stays in the guest_bill table.

now..to finish...get my reports to run from the form....error on Report Server property name...not sure if i am typing it in correctly.  the book says

Report Server    localrepserver

i am not sure what my report server is called.......my host string is xxxxx.xxx.xxxxxx.xxx

is it asking for that

thanks

i'll increase the points

rbg
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
RBG,
by the way, i'm not interested in points.
anyway, what Oracle forms and reports version are you running?
i am not fully understand your report problem.  can you explain some more?
when do you get the error? how do you run your report from form; pls show your code.
0
 
LVL 1

Author Comment

by:rbgerman
Comment Utility
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now