Solved: 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

  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;

Open in new window

on the block where clause I have this code

 
WHERE  (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID)

Open in new window

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;

Open in new window

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;

Open in new window

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;

Open in new window

Help appreciated