Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need major help - mutli-block canvas

Posted on 2003-12-10
26
Medium Priority
?
1,227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 12
26 Comments
 
LVL 5

Expert Comment

by:FBIAGENT
ID: 9914369
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
ID: 9914421
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
ID: 9914473
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:rbgerman
ID: 9914544
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
ID: 9914580
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
ID: 9914606
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
ID: 9914621
my apology
0
 
LVL 1

Author Comment

by:rbgerman
ID: 9914641
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
ID: 9917626
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
ID: 9918964
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
ID: 9921895
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
ID: 9922888
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
ID: 9923166
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
 
LVL 5

Expert Comment

by:FBIAGENT
ID: 9923198
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
ID: 9923217
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
ID: 9923238
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
ID: 9923394
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
ID: 9923395
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
ID: 9923508
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
ID: 9923725
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
ID: 9923806
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
ID: 9923900
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 200 total points
ID: 9923983
"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
ID: 9924049
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
ID: 9924101
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
ID: 9924117
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

604 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