0014638: Non Mandatory Identifiers don't work

Description

If we mark a column as identifier which is optional/non-mandatory (i.e. it may contain null values), then some of the records of that table are not listed in another table, where it is being displayed in a TableDir combo. (the records which are not being shown in the combo contain a null value in any of the identifiers).

The results are same when tested on MS Windows XP (SP2) with other software being the same as listed in profile.

Steps To Reproduce

Here we have 3 tables.

The first one is CUS_MUQADUM in which we will maintain the muqadum master data. A MUQADUM can be used/reffered in new contract/work detail entry, if it is still operative (i.e. value for INOPERATIVESINCE must be NULL). Identifiers for CUS_MUQADUM table are - MQDMCODE, NAME

In CUS_MUQADUM_CONTRACT table the BROKER column has a reference type "table" & is optional/non mandatory, while CUS_MUQADUM_ID column has a reference type "tableDir" & is mandatory. Identifiers for CUS_MUQADUM_CONTRACT table are - DOCUMENTNO, CONTRACTDATE, CUS_MUQADUM_ID, QUANTITY, CONTRACTFORDAYS, BROKER

In CUS_WORK_DETAIL table we want to record the work details executed by the MUQADUM with reference to its CONTRACT (which is having tableDir reference type).

Here the actual problem arises --- if there is any CONTRACT which has NULL in the BROKER column, then that record is not being shown in the TableDir Combo for CUS_MUQADUM_CONTRACT_ID column.

Data structure:

CREATE TABLE CUS_MUQADUM
(
CUS_MUQADUM_ID VARCHAR2(32 BYTE),
AD_CLIENT_ID VARCHAR2(32 BYTE),
AD_ORG_ID VARCHAR2(32 BYTE),
ISACTIVE CHAR(1 BYTE) DEFAULT 'Y',
CREATEDBY VARCHAR2(32 BYTE),
CREATED DATE,
UPDATEDBY VARCHAR2(32 BYTE),
UPDATED DATE,
MQDMCODE VARCHAR2(10 BYTE), -- mandatory
NAME VARCHAR2(60 BYTE), -- mandatory
ISBROKER CHAR(1 BYTE) DEFAULT 'N',
OPERATIVESINCE DATE, -- mandatory
INOPERATIVESINCE DATE, -- optional/non mandatory
CONSTRAINT CUS_MUQADUM_KEY PRIMARY KEY (CUS_MUQADUM_ID) ENABLE,
CONSTRAINT CUS_MUQADUM_ISACTIVE_CHK CHECK (ISACTIVE IN ('Y','N')) ENABLE,
CONSTRAINT CUS_MUQADUM_ISBROKER CHECK (ISBROKER IN ('Y','N')) ENABLE,
CONSTRAINT CUS_MUQADUM_AD_CLIENT FOREIGN KEY (AD_CLIENT_ID) REFERENCES AD_CLIENT (AD_CLIENT_ID) ENABLE,
CONSTRAINT CUS_MUQADUM_AD_ORG FOREIGN KEY (AD_ORG_ID) REFERENCES AD_ORG (AD_ORG_ID) ENABLE
);

CREATE TABLE CUS_MUQADUM_CONTRACT
(
CUS_MUQADUM_CONTRACT_ID VARCHAR2(32 BYTE),
AD_CLIENT_ID VARCHAR2(32 BYTE),
AD_ORG_ID VARCHAR2(32 BYTE),
ISACTIVE CHAR(1 BYTE) DEFAULT 'Y',
CREATEDBY VARCHAR2(32 BYTE),
CREATED DATE,
UPDATEDBY VARCHAR2(32 BYTE),
UPDATED DATE,
DOCUMENTNO VARCHAR2(32 BYTE), -- mandatory/auto
CONTRACTDATE DATE, -- mandatory
CUS_MUQADUM_ID VARCHAR2(32 BYTE), --tableDir reference with validation "WHERE INOPERATIVESINCE IS NULL" -- mandatory
CONTRACTRATE NUMBER(10,2), -- mandatory
QUANTITY NUMBER(10,0), -- mandatory
BROKER VARCHAR2(32 BYTE), --table reference with search filter "WHERE ISBROKER='Y' AND INOPERATIVESINCE IS NULL" -- optional/non mandatory
IS_TOOLS_OWNER CHAR(1 BYTE),
CONTRACTFORDAYS NUMBER(3,0), -- mandatory
REMARKS VARCHAR2(255 BYTE),
CONSTRAINT CUS_MUQADUM_CONTRACT_KEY PRIMARY KEY (CUS_MUQADUM_CONTRACT_ID) ENABLE,
CONSTRAINT CUS_MUQADUM_CONTRACT_ISACTIVE_CHK CHECK (ISACTIVE IN ('Y','N')) ENABLE,
CONSTRAINT CUS_MUQADUM_CONTRACT_AD_CLIENT FOREIGN KEY (AD_CLIENT_ID) REFERENCES AD_CLIENT (AD_CLIENT_ID) ENABLE,
CONSTRAINT CUS_MUQADUM_CONTRACT_AD_ORG FOREIGN KEY (AD_ORG_ID) REFERENCES AD_ORG (AD_ORG_ID) ENABLE,
CONSTRAINT CUS_MUQADUM_CONTRACT_TOOLOWN CHECK (IS_TOOLS_OWNER IN ('Y','N')) ENABLE,
CONSTRAINT CUS_MUQADUM_CONTRACT_MUQADUM FOREIGN KEY (CUS_MUQADUM_ID) REFERENCES CUS_MUQADUM (CUS_MUQADUM_ID) ENABLE,
CONSTRAINT CUS_MUQADUM_CONTRACT_BROKER FOREIGN KEY (BROKER) REFERENCES CUS_MUQADUM (CUS_MUQADUM_ID) ENABLE
);

CREATE TABLE CUS_WORK_DETAIL
(
CUS_WORK_DETAIL_ID VARCHAR2(32 BYTE),
AD_CLIENT_ID VARCHAR2(32 BYTE),
AD_ORG_ID VARCHAR2(32 BYTE),
ISACTIVE CHAR(1 BYTE) DEFAULT 'Y',
CREATEDBY VARCHAR2(32 BYTE),
CREATED DATE,
UPDATEDBY VARCHAR2(32 BYTE),
UPDATED DATE,
DOCUMENTNO VARCHAR2(32 BYTE), -- mandatory/auto
WORKDATE DATE, -- mandatory
CUS_MUQADUM_ID VARCHAR2(32 BYTE), --tableDir reference -- mandatory
CUS_MUQADUM_CONTRACT_ID VARCHAR2(32 BYTE), --tableDir reference *** -- mandatory
QUANTITY NUMBER(10,0), -- mandatory
TOTALWORKERS NUMBER(10,0), -- mandatory
REMARKS VARCHAR2(255 BYTE),
CONSTRAINT CUS_WORK_DETAIL_KEY PRIMARY KEY (CUS_WORK_DETAIL_ID) ENABLE,
CONSTRAINT CUS_WORK_DETAIL_ISACTIVE_CHK CHECK (ISACTIVE IN ('Y','N')) ENABLE,
CONSTRAINT CUS_WORK_DETAIL_AD_CLIENT FOREIGN KEY (AD_CLIENT_ID) REFERENCES AD_CLIENT (AD_CLIENT_ID) ENABLE,
CONSTRAINT CUS_WORK_DETAIL_AD_ORG FOREIGN KEY (AD_ORG_ID) REFERENCES AD_ORG (AD_ORG_ID) ENABLE,
CONSTRAINT CUS_WORK_DETAIL_MUQADUM_CONT FOREIGN KEY (CUS_MUQADUM_CONTRACT_ID) REFERENCES CUS_MUQADUM_CONTRACT (CUS_MUQADUM_CONTRACT_ID) ENABLE,
CONSTRAINT CUS_WORK_DETAIL_CUS_MUQADUM FOREIGN KEY (CUS_MUQADUM_ID) REFERENCES CUS_MUQADUM (CUS_MUQADUM_ID) ENABLE
);

Proposed Solution

We request to amend the core so that, if a value in column that is set as identifier is not available, then also it should not restrict that record from being listed.

Issue History
Date Modified	Username	Field	Change
2010-09-21 17:29	Rareworld	New Issue
2010-09-21 17:29	Rareworld	Assigned To	=> alostale
2010-09-27 08:47	alostale	Status	new => scheduled
2010-09-27 08:47	alostale	fix_in_branch	=> pi
2012-04-26 17:11	alostale	Type	defect => design defect
2012-04-26 17:11	alostale	fix_in_branch	pi =>
2017-03-31 14:36	alostale	Status	scheduled => acknowledged
2017-04-10 14:34	alostale	Assigned To	alostale => platform
2022-02-01 08:08	alostale	Assigned To	platform => Triage Platform Base

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.