oracle forms question
Forms [32 Bit] Version 11.1.1.4.0 (Production)
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Oracle Toolkit Version 11.1.1.4.0 (Production)
PL/SQL Version 11.1.0.7.0 (Production)
Oracle Procedure Builder V11.1.1.4.0 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 11.1.1.4.0 - Production
Oracle Virtual Graphics System Version 11.1.1.4.0 (Production)
Oracle Tools GUI Utilities Version 11.1.1.2.0 (Production)
Oracle Multimedia Version 11.1.1.4.0 (Production)
Oracle Tools Integration Version 11.1.1.2.0 (Production)
Oracle Tools Common Area Version 11.1.1.4.0
Oracle CORE 11.1.0.7.0 Production
I have a tab page form.
User Access Tab
has 2 tabs. 1. division, 2. customer
We are breaking some rules hre to make the relation work. I have created relations on the block.
1st block - usrdiv
2nd block - usrcust
both these will have user roles. above 2 blocks sit on top of roles block
If user clicks on either div or cust we will display user roles but the roles displayed will be based on division key or customer key.
the roles block is udr.
I have a code in pre query of udr block
on the block where clause I have this code
I am attaching screen prints. The div tab does not show roles, but customer tab shows. Its actually the other way around. The div tab has to show roles but not customer.
I need help in fixing and will appreciate the help.
table script
Help appreciated
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Oracle Toolkit Version 11.1.1.4.0 (Production)
PL/SQL Version 11.1.0.7.0 (Production)
Oracle Procedure Builder V11.1.1.4.0 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 11.1.1.4.0 - Production
Oracle Virtual Graphics System Version 11.1.1.4.0 (Production)
Oracle Tools GUI Utilities Version 11.1.1.2.0 (Production)
Oracle Multimedia Version 11.1.1.4.0 (Production)
Oracle Tools Integration Version 11.1.1.2.0 (Production)
Oracle Tools Common Area Version 11.1.1.4.0
Oracle CORE 11.1.0.7.0 Production
I have a tab page form.
User Access Tab
has 2 tabs. 1. division, 2. customer
We are breaking some rules hre to make the relation work. I have created relations on the block.
1st block - usrdiv
2nd block - usrcust
both these will have user roles. above 2 blocks sit on top of roles block
If user clicks on either div or cust we will display user roles but the roles displayed will be based on division key or customer key.
the roles block is udr.
I have a code in pre query of udr block
IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN
IF( :SYSTEM.TAB_NEW_PAGE = 'DIV' )THEN
:UDR.USRDIV_ID := :USRDIV.ID;
END IF;
END IF;
IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN
IF( :SYSTEM.TAB_NEW_PAGE = 'CUST' )THEN
:UDR.USRCUST_ID := :USRCUST.ID;
END IF;
END IF;on the block where clause I have this code
WHERE (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID)I am attaching screen prints. The div tab does not show roles, but customer tab shows. Its actually the other way around. The div tab has to show roles but not customer.
I need help in fixing and will appreciate the help.
table script
DROP TABLE DSS.TAB1 CASCADE CONSTRAINTS;
CREATE TABLE DSS.TAB1
(
ID NUMBER(38) NOT NULL,
DEFAULT_DIV VARCHAR2(1 BYTE) NOT NULL,
DIV_NO NUMBER(2) NOT NULL,
PERS_ID NUMBER(38) NOT NULL,
ACTIVE VARCHAR2(1 BYTE) NOT NULL,
NOTES VARCHAR2(240 BYTE)
)
TABLESPACE MISC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
SET DEFINE OFF;
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(501903, 'N', 11, 1711, 'A',
'x');
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(501905, 'N', 50, 1711, 'I',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(11423931738, 'N', 71, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(11750159949, 'N', 55, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(20310150, 'Y', 1, 1711, 'A',
'x');
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(22645048, 'N', 5, 1711, 'A',
'x');
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(23978631, 'N', 6, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(1080171145, 'N', 91, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(1080171156, 'N', 97, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(3339590005, 'N', 31, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(4199747257, 'N', 81, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(5051070062, 'N', 7, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(5336771059, 'N', 59, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(8331564946, 'N', 65, 1711, 'A',
NULL);
Insert into DSS.TAB1
(ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE,
NOTES)
Values
(9844406287, 'N', 57, 1711, 'A',
NULL);
COMMIT;DROP TABLE DSS.TAB2 CASCADE CONSTRAINTS;
CREATE TABLE DSS.TAB2
(
ID NUMBER(38) NOT NULL,
DEFAULT_CUST VARCHAR2(1 BYTE) NOT NULL,
CUST_ID NUMBER(38) NOT NULL,
PERS_ID NUMBER(38) NOT NULL,
ACTIVE VARCHAR2(1 BYTE) NOT NULL,
NOTES VARCHAR2(4000 BYTE)
)
TABLESPACE MISC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
SET DEFINE OFF;
Insert into DSS.TAB2
(ID, DEFAULT_CUST, CUST_ID, PERS_ID, ACTIVE,
NOTES)
Values
(12405285275, 'Y', 4851575509, 1711, 'A',
'x');
COMMIT;DROP TABLE DSS.TAB3 CASCADE CONSTRAINTS;
CREATE TABLE DSS.TAB3
(
ID NUMBER(38) NOT NULL,
ORACLE_ROLE VARCHAR2(30 BYTE) NOT NULL,
USRDIV_ID NUMBER(38),
ACTIVE VARCHAR2(1 BYTE) NOT NULL,
ROLE_SHORT_NAME VARCHAR2(10 BYTE),
NOTES VARCHAR2(240 BYTE)
)
TABLESPACE MISC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
SET DEFINE OFF;
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(1069946932, 'NORM_ALL', 20310150, 'I', 'ILS_RTBL',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(12399502686, 'NORM_ALL', 20310150, 'A', 'ILS_MNTVEN',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(12399502810, 'NORM_ALL', 20310150, 'A', 'ILS_QCSR',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(12399499473, 'NORM_ALL', 20310150, 'A', 'SECURITY',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(12399499475, 'NORM_ALL', 20310150, 'A', 'ACCTNG',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(12399499477, 'NORM_ALL', 20310150, 'A', 'CUST SRVC',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(12400771316, 'NORM_ALL', 20310150, 'A', 'ILS_RTBL',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(21492721, 'NORM_ALL', 20310150, 'A', 'IS_PRD_SUP',
'x');
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(20310151, 'NORM_ALL', 20310150, 'A', 'USR SECRTY',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(20310153, 'NORM_ALL', 20310150, 'A', 'INQ_ALL',
NULL);
Insert into DSS.TAB3
(ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME,
NOTES)
Values
(20310154, 'NORM_ALL', 20310150, 'I', 'IS_DEVELOP',
'x');
COMMIT;Help appreciated
ASKER
I have to get values on the division. But its not happening. When I click on customer tab then the values populate. Actually customer has no roles entered.
ASKER
--41178 Start
IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN
IF( :SYSTEM.TAB_NEW_PAGE = 'DIV' )THEN
:UDR.USRDIV_ID := :USRDIV.ID;
END IF;
END IF;
IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN
IF( :SYSTEM.TAB_NEW_PAGE = 'CUST' )THEN
:UDR.USRCUST_ID := :USRCUST.ID;
END IF;
END IF;Pre query is getting the id, but the focus is shifting and prequery fires when I click customer tab.
The message is meant to verify if after firing of the requery trigger the forms_items have the expected values.
And are both :USRDIV. ID :USRCUST_ID defined as query_allowed ?
Now I see:
Your prequery code wil not work : your are checking :SYSTEM.TAB_NEW_PAGE twice in the same condition for different values:
so both your combined conditions (line 2 and 3) and (line 8 and 9) will never be true and thus the items will not be set.
The values before the pre_query determine the result.
(consider also if you need to initialise :UDR.USRDIV_ID and :UDR.USRCUST_ID)
And are both :USRDIV. ID :USRCUST_ID defined as query_allowed ?
Now I see:
Your prequery code wil not work : your are checking :SYSTEM.TAB_NEW_PAGE twice in the same condition for different values:
so both your combined conditions (line 2 and 3) and (line 8 and 9) will never be true and thus the items will not be set.
The values before the pre_query determine the result.
(consider also if you need to initialise :UDR.USRDIV_ID and :UDR.USRCUST_ID)
ASKER
(consider also if you need to initialise :UDR.USRDIV_ID and :UDR.USRCUST_ID)
Initialization has to be done before pre-query?
Initialization has to be done before pre-query?
ASKER
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'DIV' THEN
:UDR.USRDIV_ID := :USRDIV.ID;
END IF;
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'CUST' THEN
:UDR.USRCUST_ID := :USRCUST.ID;
END IF;I have to use this code to display the top tab. But when I query or do a tab change, the data does not display. I have to go to user roles block and to execute query. The data is correctly displaying. In customer no data, which is correct for roles. Now I need to get data after I change tab.
ASKER
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'DIV' THEN
GO_BLOCK( 'UDR' );
Clear_Block(No_Validate);
IF :UDR.USRDIV_ID IS NOT NULL THEN
execute_query;
END IF;
END IF;
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'CUST' THEN
GO_BLOCK( 'UDR' );
Clear_Block(No_Validate);
IF :UDR.USRCUST_ID IS NOT NULL THEN
execute_query;
END IF;
END IF;When I call when-tab-page_change, can I use this code?
Yes, you can.
And you can probably avoid the pre_query trigger bij changing the where clause for the next execution depending on the current tab
Set_Block_Property('UDR', ONETIME_WHERE, 'USRDIV_ID = :USRDIV.ID;')
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID = :USRCUST.ID;')
after the clear_block of UDR :UDR.USRDIV_ID and :UDR.USRCUST_ID will always be null !!!
Set_Block_Property('UDR', ONETIME_WHERE, 'USRDIV_ID = :USRDIV.ID;')
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID = :USRCUST.ID;')
after the clear_block of UDR :UDR.USRDIV_ID and :UDR.USRCUST_ID will always be null !!!
ASKER
Should the code be
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID =' ||:USRCUST.ID);
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID =' ||:USRCUST.ID);
ASKER
Set_Block_Property('UDR', ONETIME_WHERE, 'USRDIV_ID = :USRDIV.ID;')
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID = :USRCUST.ID;')
I tried to use all help. But nothing is working. I dont get data for roles.
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID = :USRCUST.ID;')
I tried to use all help. But nothing is working. I dont get data for roles.
Apart from getting the rows (= execute_query) , you can also count the rows. Maybe you have a menu icon or you know the keys to activate that feature. At my work it's shift-F2. Try it on the roles : if there are rows counted > 0 then you have to check your post-query trigger, if it fails rows are not shown,
ASKER
declare
v_count number;
begin
select count(*) into v_count from dss.USER_DIV_ROLES
where usrdiv_id = :usrdiv.id;
message('count for div from roles = ' || v_count);pause;
end;But the data will not display automatically. On my relations I have Yes to query records. But I have to press execute query.
ASKER
For the 1st time when I query a person, I am getting the correct data in both DIV and Customer Tabs. 
So what needs to be done. In pre-query I have this code.
But still does not work.Help is appreciated.
So what needs to be done. In pre-query I have this code.
declare
v_where VARCHAR2(2000);
begin
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'DIV' THEN
:UDR.USRDIV_ID := :USRDIV.ID;
V_Where := 'udr.usrdiv_id = '||:usrdiv.id;
SET_BLOCK_PROPERTY ('UDR', DEFAULT_WHERE, V_Where);
ELSIF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'CUST' THEN
:UDR.USRCUST_ID := :USRCUST.ID;
V_Where := 'udr.uscust_id = '||:usrcust.id;
SET_BLOCK_PROPERTY ('UDR', DEFAULT_WHERE, V_Where);
END IF;
end;But still does not work.Help is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the options. One question - all options for when tab changed trigger? . Please confirm
ASKER
Create a when-tab-page_changed trigger
option 0 depend only on your default_where (WHERE (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID))
begin
GO_BLOCK( 'UDR' );
execute_query;
end;
This option worked the best.
option 0 depend only on your default_where (WHERE (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID))
begin
GO_BLOCK( 'UDR' );
execute_query;
end;
This option worked the best.
ASKER
IF( :SYSTEM.TAB_NEW_PAGE = 'DIV' )THEN
SHOW_VIEW('USER_ACCESS_TAB
GO_BLOCK( 'USRDIV' );
begin
GO_BLOCK( 'UDR' );
execute_query;
end;
Sometimes when the user is in the divisions block, they go to top people block and execute query (blind query or put a user name and then query). At that point udr block is not querying. I know at this point when-tab-page-changed does not fire. What is the good way to handle this?
SHOW_VIEW('USER_ACCESS_TAB
GO_BLOCK( 'USRDIV' );
begin
GO_BLOCK( 'UDR' );
execute_query;
end;
Sometimes when the user is in the divisions block, they go to top people block and execute query (blind query or put a user name and then query). At that point udr block is not querying. I know at this point when-tab-page-changed does not fire. What is the good way to handle this?
ASKER
remove the pre-query trigger
Create a when-tab-page_changed trigger
option 0 depend only on your default_where (WHERE (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID))
begin
GO_BLOCK( 'UDR' );
execute_query;
end;
default_where - what will this be set? (since I am removing all from prequery)
Create a when-tab-page_changed trigger
option 0 depend only on your default_where (WHERE (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID))
begin
GO_BLOCK( 'UDR' );
execute_query;
end;
default_where - what will this be set? (since I am removing all from prequery)
ASKER
default_where - where will this be set? (since I am removing all from pre-query)
I would use some trigger on "top people block" for setting default where of 'UDR' block- maybe it can be "top people block" pre-query trigger.
ASKER
thanks for all the help
add as last line of the prequery block
message(''usrdiv.id=' || :USRDIV.ID || '< usrcust_id=' || USRCUST_ID || '<' , acknowledge);
check if it explains the behaviour and/or check for extra spaces ...