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;

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 CERTIFIED SOLUTION
flow01
IT-specialist

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

Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros