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  |
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 |
|
|
|
|
|
|
|
| inserting into master-detail block [message #212292 is a reply to message #212081] |
Thu, 04 January 2007 09:38  |
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 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 15:25:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
