Home » Developer & Programmer » Forms » Data storing in multi record block (Oracle Forms 11g, windows 7)
Data storing in multi record block [message #592639] |
Sat, 10 August 2013 02:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mary.yujin
Messages: 26 Registered: June 2010
|
Junior Member |
|
|
Hi,
i have a form with 3 blocks in our oracle application. Block1 is a single record block. Block2 and block 3 are multi record blocks with no of records 5. Block1 and block2 are in same canvas1 of a window1. Block3 is in canvas2 of window2. There is a relation between block1 and block2.
Another relation with block2 and block3 is also available.
Now my issues is while creating a record, first i entered the data in block1 (which is a single record block). Then i entered data in five rows of second block. Then i entered data in block3(multi record block) by pressing the button in block2 (each button for each row). But before saving the entire form, when i checked the data in block3 by pressing the button of first record of block2, screen shows empty.
Code in button of block2 is as follows
show_window(WIN2');
Go_block('BLOCK3');
Code in button of block3 is as follows
hide_window('WIN2');
go_block('BLOCK2');
If post; is included in block3 button. then problem we will be solved. But we dont want to use post; as it lock the table.
Pls give some solution.
|
|
|
|
Re: Data storing in multi record block [message #592745 is a reply to message #592737] |
Mon, 12 August 2013 01:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mary.yujin
Messages: 26 Registered: June 2010
|
Junior Member |
|
|
While post; is using pre insert trigger of block1 is raising and ref no is generating. So at the same time , if another person also on working the same screen and post; is working , it will hang waiting to generate the ref no. That is why we are not prefering post;. But pls advise me how to save data in block2 and 3 without post;
Mary
|
|
|
Re: Data storing in multi record block [message #592766 is a reply to message #592745] |
Mon, 12 August 2013 03:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The only way to save data is to put it in the DB using post or commit.
It shouldn't just be going missing though, if oracle thinks it needs to clear the data in the datablocks it should ask if you want to save changes first. I suggest running the form in debug mode to see what triggers get fired when the data goes missing.
Also how are you generating ref no?
|
|
|
|
Re: Data storing in multi record block [message #592769 is a reply to message #592767] |
Mon, 12 August 2013 04:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Looking at your screen layout you have no choice but to fix the post problem.
Block 2 is master to block 3 right?
Forms will only hold data in block 3 that relates to the current selected record in block 2.
So when you change the record in block 2 forms has to clear data in block 3. It will not save it internally it has to be saved in the DB if you want to keep it.
Normally when that happens oracle will ask if you want to save changes. If you save yes it will be saved to the db, if you say no it'll just be wiped. I'm not sure why oracle isn't asking this, which is why I suggested running the form in debug mode.
Regardless you have to save data from block 3 in the DB before you can change record in block 2.
So you need to make post not lock up the table.
|
|
|
Re: Data storing in multi record block [message #592904 is a reply to message #592769] |
Tue, 13 August 2013 01:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mary.yujin
Messages: 26 Registered: June 2010
|
Junior Member |
|
|
Yes. Block2 is the master of block3. I am not changing data in block2..Once the data entered in block2 & 3, while viewing in block3 corresponding to first record of block2, block3 shows empty.
Can we advise me how to use post; without locking.
|
|
|
Re: Data storing in multi record block [message #592948 is a reply to message #592904] |
Tue, 13 August 2013 04:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mary.yujin wrote on Tue, 13 August 2013 07:13Yes. Block2 is the master of block3. I am not changing data in block2
You don't need to change the data in block 2 to have an issue, just click on a different record.
mary.yujin wrote on Tue, 13 August 2013 07:13
Can we advise me how to use post; without locking.
Like I say, post doesn't do that. Some code you're written that is kicked off by the post command does that.
I can't help you fix it unless I know what the code is.
|
|
|
Re: Data storing in multi record block [message #593126 is a reply to message #592948] |
Wed, 14 August 2013 07:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mary.yujin
Messages: 26 Registered: June 2010
|
Junior Member |
|
|
Please find the below code in Pre-insert trigger of BLOCK1
PROCEDURE NEW_PO_REF IS
v1 number;
v2 number;
vnote1 varchar2(15);
vissdate date;
zvalue varchar2(5);
LOC VARCHAR2(2):=' ';
loc_ref varchar2(10);
curr_year number;
begin
Select srs_loc_ref,to_char(:pur_lpomain.lpo_date,'yyyy') into loc_ref,curr_year from sec_reg_settings where srs_org_code = :global.orgcode;
if loc_ref is not null then
IF :GLOBAL.ORGCODE=2 THEN
LOC:='U';
Elsif :GLOBAL.ORGCODE=3 THEN
LOC:='Q';
Elsif :GLOBAL.ORGCODE=4 THEN
LOC:='S';
END IF;
if :pur_lpomain.location = 'UAE (Local)' then
vnote1 := substr(curr_year,3,4)||'LPO-U'||LOC||'-'||loc_ref||'-';
elsif :pur_lpomain.location = 'UAE (Overseas)' then
vnote1 := substr(curr_year,3,4)||'OPO-U'||LOC||'-'||loc_ref||'-';
elsif :pur_lpomain.location = 'Das (Local)' then
vnote1 := substr(curr_year,3,4)||'LPO-D'||LOC||'-'||loc_ref||'-';
end if;
If :global.orgcode != 3 then
if :pur_lpomain.sub_po_type = 23 then
vnote1 := substr(curr_year,3,4)||'LPO-ADM'||LOC||'-'||loc_ref||'-';
end if;
end if;
If substr(curr_year,3,4)IN ('08') then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and location = :pur_lpomain.location
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103 and lpo_ref like vnote1||'%';
:pur_lpomain.LOC_REF := null;
Else
:pur_lpomain.LOC_REF := loc_ref;
if :global.orgcode!=3 then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
and nvl(sub_po_type,0)!= 23 and lpo_ref like vnote1||'%';
else
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
and lpo_ref like vnote1||'%' ;
end if;
End if;
:pur_lpomain.old := 'N';
If :global.orgcode != 3 then
if :pur_lpomain.sub_po_type = 23 then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and nvl(sub_po_type,0) = 23 and lpo_ref like vnote1||'%';
end if;
End if;
v2 := (nvl(v1,0)+1);
:pur_lpomain.refnum := v2;
:pur_lpomain.type := 'PO';
if v2 >= 1 and v2 <= 9 then
:pur_lpomain.lpo_ref := vnote1||'000'||v2;
end if;
if v2 >= 10 and v2 <=99 then
:pur_lpomain.lpo_ref := vnote1||'00'||v2;
end if;
if v2 >= 100 and v2 <=999 then
:pur_lpomain.lpo_ref := vnote1||'0'||v2;
end if;
if v2 >999 then
:pur_lpomain.lpo_ref := vnote1||v2;
End if;
select sysdate into :pur_lpomain.lpo_date from dual;
:pur_lpomain.rev := '0';
if :pur_lpomain.sub_po_type = 23 or :pur_lpomain.po_type = 'D' then --checking whether it is travel PO
Set_item_property('pur_lpodet.sector_no',visible,property_true);
Set_item_property('pur_lpodet.sec_name',visible,property_true);
Set_item_property('pur_lpodet.empid_btn',visible,property_true);
Set_item_property('pur_lpodet.sec_btn',visible,property_true);
Set_item_property('pur_lpodet.sector_no',enabled,property_true);
Set_item_property('pur_lpodet.sec_name',enabled,property_true);
Set_item_property('pur_lpodet.empid_btn',enabled,property_true);
Set_item_property('pur_lpodet.sec_btn',enabled,property_true);
Set_item_property('pur_lpodet.thickness',visible,property_false);
Set_item_property('pur_lpodet.dimen',visible,property_false);
Set_item_property('pur_lpodet.density',visible,property_false);
else
Set_item_property('pur_lpodet.sector_no',visible,property_false);
Set_item_property('pur_lpodet.sec_name',visible,property_false);
Set_item_property('pur_lpodet.empid_btn',visible,property_false);
Set_item_property('pur_lpodet.sec_btn',visible,property_false);
Set_item_property('pur_lpodet.thickness',visible,property_true);
Set_item_property('pur_lpodet.dimen',visible,property_true);
Set_item_property('pur_lpodet.density',visible,property_true);
End if;
End if;
:pur_lpomain.new_lpo_ref := :pur_lpomain.lpo_ref;
END;
:Pur_lpomain means BLOCK1 and :pur_lpodet means BLOCK2.
In save button, code is commit_form;
Please advise me.
|
|
|
|
Re: Data storing in multi record block [message #593204 is a reply to message #593127] |
Wed, 14 August 2013 23:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mary.yujin
Messages: 26 Registered: June 2010
|
Junior Member |
|
|
In Pre insert trigger of Block1, calling a procedure
NEW_PO_REF;
which is showed below.
PROCEDURE NEW_PO_REF IS
v1 number;
v2 number;
vnote1 varchar2(15);
vissdate date;
zvalue varchar2(5);
LOC VARCHAR2(2):=' ';
loc_ref varchar2(10);
curr_year number;
begin
Select srs_loc_ref,to_char(:pur_lpomain.lpo_date,'yyyy') into loc_ref,curr_year from sec_reg_settings where srs_org_code = :global.orgcode;
if loc_ref is not null then
IF :GLOBAL.ORGCODE=2 THEN
LOC:='U';
Elsif :GLOBAL.ORGCODE=3 THEN
LOC:='Q';
Elsif :GLOBAL.ORGCODE=4 THEN
LOC:='S';
END IF;
if :pur_lpomain.location = 'UAE (Local)' then
vnote1 := substr(curr_year,3,4)||'LPO-U'||LOC||'-'||loc_ref||'-';
elsif :pur_lpomain.location = 'UAE (Overseas)' then
vnote1 := substr(curr_year,3,4)||'OPO-U'||LOC||'-'||loc_ref||'-';
elsif :pur_lpomain.location = 'Das (Local)' then
vnote1 := substr(curr_year,3,4)||'LPO-D'||LOC||'-'||loc_ref||'-';
end if;
If :global.orgcode != 3 then
if :pur_lpomain.sub_po_type = 23 then
vnote1 := substr(curr_year,3,4)||'LPO-ADM'||LOC||'-'||loc_ref||'-';
end if;
end if;
If substr(curr_year,3,4)IN ('08') then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and location = :pur_lpomain.location
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103 and lpo_ref like vnote1||'%';
:pur_lpomain.LOC_REF := null;
Else
:pur_lpomain.LOC_REF := loc_ref;
if :global.orgcode!=3 then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
and nvl(sub_po_type,0)!= 23 and lpo_ref like vnote1||'%';
else
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and upper(ltrim(rtrim(location))) = upper(ltrim(rtrim(:pur_lpomain.location)))
and lpo_ref like vnote1||'%' ;
end if;
End if;
:pur_lpomain.old := 'N';
If :global.orgcode != 3 then
if :pur_lpomain.sub_po_type = 23 then
select NVL(max(refnum),0) into v1 from pur_lpomain
where org_code = :global.orgcode and loc_ref = :pur_lpomain.loc_ref
and to_char(lpo_date,'yyyy')=to_char(:pur_lpomain.lpo_date,'yyyy') and transaction_type=103
and nvl(sub_po_type,0) = 23 and lpo_ref like vnote1||'%';
end if;
End if;
v2 := (nvl(v1,0)+1);
:pur_lpomain.refnum := v2;
:pur_lpomain.type := 'PO';
if v2 >= 1 and v2 <= 9 then
:pur_lpomain.lpo_ref := vnote1||'000'||v2;
end if;
if v2 >= 10 and v2 <=99 then
:pur_lpomain.lpo_ref := vnote1||'00'||v2;
end if;
if v2 >= 100 and v2 <=999 then
:pur_lpomain.lpo_ref := vnote1||'0'||v2;
end if;
if v2 >999 then
:pur_lpomain.lpo_ref := vnote1||v2;
End if;
select sysdate into :pur_lpomain.lpo_date from dual;
:pur_lpomain.rev := '0';
if :pur_lpomain.sub_po_type = 23 or :pur_lpomain.po_type = 'D' then --checking whether it is travel PO
Set_item_property('pur_lpodet.sector_no',visible,property_true);
Set_item_property('pur_lpodet.sec_name',visible,property_true);
Set_item_property('pur_lpodet.empid_btn',visible,property_true);
Set_item_property('pur_lpodet.sec_btn',visible,property_true);
Set_item_property('pur_lpodet.sector_no',enabled,property_true);
Set_item_property('pur_lpodet.sec_name',enabled,property_true);
Set_item_property('pur_lpodet.empid_btn',enabled,property_true);
Set_item_property('pur_lpodet.sec_btn',enabled,property_true);
Set_item_property('pur_lpodet.thickness',visible,property_false);
Set_item_property('pur_lpodet.dimen',visible,property_false);
Set_item_property('pur_lpodet.density',visible,property_false);
else
Set_item_property('pur_lpodet.sector_no',visible,property_false);
Set_item_property('pur_lpodet.sec_name',visible,property_false);
Set_item_property('pur_lpodet.empid_btn',visible,property_false);
Set_item_property('pur_lpodet.sec_btn',visible,property_false);
Set_item_property('pur_lpodet.thickness',visible,property_true);
Set_item_property('pur_lpodet.dimen',visible,property_true);
Set_item_property('pur_lpodet.density',visible,property_true);
End if;
End if;
:pur_lpomain.new_lpo_ref := :pur_lpomain.lpo_ref;
END;
:Pur_lpomain means BLOCK1 and :pur_lpodet means BLOCK2.
In save button, code is commit_form;
Please advise me.
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 20 13:58:56 CDT 2024
|