Home » Developer & Programmer » Forms » Master - detail in same table / form and inserting into master-detail block (merged)
Master - detail in same table / form and inserting into master-detail block (merged) [message #212081] Wed, 03 January 2007 13:30 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi All, Need help in following. I have following form layout based on one table only... This is layout
Set Name        Set Code

        Period:   Period1  Period2  Period3 Period4
Value(Record1):    Value1   Value2   Value3  Value4
Value(Record2):    Value1   Value2   Value3  Value4
Value(Record3):    Value1   Value2   Value3  Value4
Value(Record4):    Value1   Value2   Value3  Value4
Value(Record5):    Value1   Value2   Value3  Value4

Period value is based on Set Name and will be constant but the value will be different so when the value in Set Name Change the Period values should change...

Following is table structure

Record 1: set name,set code,period1,value1,period2,value1,period3,value3,period4,value4,period5,value5
Record 2: set name,set code,period1,value1,period2,value1,period3,value3,period4,value4,period5,value5
Record 3: set name,set code,period1,value1,period2,value1,period3,value3,period4,value4,period5,value5
Record 4: set name,set code,period1,value1,period2,value1,period3,value3,period4,value4,period5,value5

So every record in table has period values but in form requirement is not to show period field values as for a set name period value will be same but value field values can differ so depending on set name i want to change label in period text field.

So created a view on distinct set name as master block and detail block as base table so i can see desired data view in form

Master block ( view) shows - set name,set type, period 1 to period 5
Detail block (base table) shows - value 1 to value 15.
for period 1-period5 in detail block i have given NULL in their canvas so it will not show up in form.

now i have a problem with insert/update. created a pre-insert / pre-update trigger on detail block but it does not insert period values in table only insert value field.

Need help on this
Thanks in advance

Upd-mod: Please use 'code' tags to help with the layout.

[Updated on: Thu, 04 January 2007 05:41] by Moderator

Report message to a moderator

Re: Master - detail in same table / form [message #212243 is a reply to message #212081] Thu, 04 January 2007 05:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Don't use the pre-insert and pre-update triggers, use the When-Create-Record trigger on the detail block to populate the 'period' values.

David
Re: Master - detail in same table / form [message #212261 is a reply to message #212243] Thu, 04 January 2007 06:38 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Thanks David for code comment.

next time will use that...

I will try the when-create-record

inserting into master-detail block [message #212292 is a reply to message #212081] Thu, 04 January 2007 09:38 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi All,

I have created a master-detail form following way.

Used when-create-record on detail block but it inserts NULL value for periods column?
[pre]

base table: periods
set name,period1,value1,period2,value2,period3,value3

view : period_v

create view period_v (set name,period1,period2,period3)
as
select distinct set name,period1 ,period2,period3 from period

master block is on period_v
detial_block is on periods
relation is set name


Triggers on Detail Block
[PRE-INSERT : FND_STANDARD.SET_WHO;
ON-INSERT: got the period value for base table for data_set_name into variable
written insert statement

declare
v_set_name varchar2;
v_period1 varchar2;
v_period2 varchar2;
v_period3 varchar2;
begin
begin
select set_name,period1,period2,period3
into
v_set_name ,  v_period1 ,  v_period2 ,v_period3 
from periods
where set_name := :period_v.set_name;
exception
when others than
null;
end;

insert into periods
values
(v_set_name,
v_period1,
:periods.value1,
v_period2,
:periods.value2,
v_period3,
:periods.value3);
end;
]



Triggers on Master Block
PRE-INSERT : FND_STANDARD.SET_WHO;
ON-INSERT: 
insert into periods
values
(:period_v.set_name,
:period_v.period1,
:periods.value1,
:period_v.period2,
:periods.value2,
:period_v.period3,
:periods.value3);
end;

Scenario 1: Insert a record on for existing master record that is only chile insert on details block. it does correct operation.

Scenario 2: Insert a fresh master-child record but it inserts two same record as the master-details is on same base table.

How to achive Scenario 1 Scenario 2? what kind of trigger i should use when i am using master-detail based on view-base table both reffering to same table.


Need help.

Thanks in advance


[/pre]
Re: Master - detail in same table / form [message #212393 is a reply to message #212261] Fri, 05 January 2007 02:16 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I received the following from navik_pathak by PM.
Hi,

need help on following

table structure:

Set Name , Set Code, Period 1 , Value 1 , Period 2 , Value 2 , Period 3 , Value 3

Table Data

Row 1 : X_Set , X_Code, P1, 1 , P2, 2, P3, 3
Row 2 : X_Set , X_Code, P1, 1 , P2, 3, P3, 3
Row 3 : X_Set , X_Code, P1, 2 , P2, 43, P3, 3
Row 3 : X_Set , X_Code, P1, 31 , P2, 43, P3, 63

Row 5 : X_Set1 , X_Code, P3, 1 , P4, 2, P5, 3
Row 6 : X_Set1 , X_Code, P3, 1 , P4, 3, P5, 3
Row 7 : X_Set1 , X_Code, P3, 2 , P4, 43, P5, 3
Row 8 : X_Set1 , X_Code, P3, 31 , P4, 43, P5, 63

so for particular set name period column value will be same but data in the value field will be different.

Are there any suggestion on how can i display this table data in Oracle Form following way:

Set Name : X_Set
Set Code : X_Code

Period P1 P2 P3
Row1 1 2 3
Row2 1 3 3
Row3 2 43 3
Row4 31 43 63


Set Name : X_Set1
Set Code : X_Code1

Period P3 P4 P5
Row5 1 2 3
Row6 1 3 3
Row7 2 43 3
Row8 31 43 63

i tried using master-detail form.
Master block is on view and detail block is on base table. But i am having difficulties on insert/update/delete on the master block as it is on view.

any other approach!!

Thanks in advance

Please DON'T PM a problem! Just add it to your thread.

David
Re: Master - detail in same table / form [message #212396 is a reply to message #212393] Fri, 05 January 2007 02:29 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You do not need to add entries in the master block because an entry in the detail block will be retrieved through the master block. I assume that the reason you want to insert through the master block is to add a new set name and periods.

Stop doing manual 'inserts' in the pre-insert. In my opinion, it should only be used for populating fields in the existing record and populating OTHER tables, not the same table as that referenced by the block.

I can think of no simple way to display the following structure on a single screen.
Set Name : X_Set
Set Code : X_Code

Period P1 P2 P3
Row1 1 2 3
Row2 1 3 3
Row3 2 43 3
Row4 31 43 63


Set Name : X_Set1
Set Code : X_Code1

Period P3 P4 P5
Row5 1 2 3
Row6 1 3 3
Row7 2 43 3
Row8 31 43 63

It might be possible to do it when there are an equal number of rows for both sets, but unless you use separately named fields for every item I know of no way that you can design a screen that had DIFFERENT numbers of rows in each 'prt' of the screen.

Remember forms will do all the inserting and updating you need to have done, just remember to populate the fields pre-insert trigger.

David
Re: Master - detail in same table / form and inserting into master-detail block (merged) [message #212459 is a reply to message #212081] Fri, 05 January 2007 06:44 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

I have found the solution and it is working now.

Created ON-LOCK/ON-UPDATE/ON-INSERT triggers which is now allowing me to insert/update/delete.

Thanks
Previous Topic: text highlighted when page up and down in multiple records block?
Next Topic: Quiet commit on a form
Goto Forum:
  


Current Time: Sat Dec 03 03:51:50 CST 2016

Total time taken to generate the page: 0.16719 seconds