troubleshooting Question

oracle forms question

Avatar of anumoses
anumosesFlag for United States of America asked on
Oracle Database
22 Comments1 Solution231 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
flow01
IT-specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 22 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 22 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004