ENTITY_CODE     ENTITY_NAME

SR	        SR INFO SOLUTION
DP	        DIGITAL PLANET

NAME           ENTITY_CODE

KESHRI	        SR
EHTE	        DP
REZA	        SR DP

KESHRI   SR
EHTE     DP
REZA     SR
REZA     DP

CREATE OR REPLACE VIEW VIEW_CALL AS
SELECT DISTINCT
       ENTITY_MAST.ENTITY_CODE,
       USER_MAST.USER_CODE,
       USER_MAST.PASSWORD,
       ENTITY_MAST.ENTITY_NAME,
       USER_MAST.ENTITY,
       REPLACE(USER_MAST.ENTITY, ' ') AS "NO_SPACE"
  FROM USER_MAST,ENTITY_MAST

CREATE OR REPLACE VIEW VIEW_CALL1 AS
SELECT DISTINCT
       VIEW_CALL.ENTITY_CODE,
       VIEW_CALL.USER_CODE,
       VIEW_CALL.PASSWORD,
       VIEW_CALL.ENTITY_NAME,
       VIEW_CALL.ENTITY,
       VIEW_CALL.NO_SPACE,
       SUBSTR(VIEW_CALL.NO_SPACE,0,2) AS AAA,
       SUBSTR(VIEW_CALL.NO_SPACE,3,2) AS BBB,
       SUBSTR(VIEW_CALL.NO_SPACE,5,2) AS CCC
  FROM VIEW_CALL

DECLARE
		CURSOR GRP_SR IS
		SELECT DISTINCT ROWNUM RN_SR, VIEW_CALL1.ENTITY_CODE||' - '||VIEW_CALL1.ENTITY_NAME AS ENTITY
		FROM VIEW_CALL1
		WHERE :LOGIN_USER_CODE = VIEW_CALL1.USER_CODE
		AND :LOGIN_PASSWORD = VIEW_CALL1.PASSWORD
		AND VIEW_CALL1.AAA = VIEW_CALL1.ENTITY_CODE;
		
		CURSOR GRP_DP IS
		SELECT DISTINCT ROWNUM RN_DP, VIEW_CALL1.ENTITY_CODE||' - '||VIEW_CALL1.ENTITY_NAME AS ENTITY
		FROM VIEW_CALL1
		WHERE :LOGIN_USER_CODE = VIEW_CALL1.USER_CODE
		AND :LOGIN_PASSWORD = VIEW_CALL1.PASSWORD
		AND VIEW_CALL1.BBB = VIEW_CALL1.ENTITY_CODE;
		
		CURSOR GRP_XX IS
		SELECT DISTINCT ROWNUM RN_XX, VIEW_CALL1.ENTITY_CODE||' - '||VIEW_CALL1.ENTITY_NAME AS ENTITY
		FROM VIEW_CALL1
		WHERE :LOGIN_USER_CODE = VIEW_CALL1.USER_CODE
		AND :LOGIN_PASSWORD = VIEW_CALL1.PASSWORD
		AND VIEW_CALL1.CCC = VIEW_CALL1.ENTITY_CODE;
	BEGIN
		CLEAR_LIST ('LOGIN_ENTITY_LIST');
		FOR I_SR IN GRP_SR LOOP
			FOR I_DP IN GRP_DP LOOP
				FOR I_XX IN GRP_XX LOOP
					ADD_LIST_ELEMENT ('LOGIN_ENTITY_LIST', I_DP.RN_DP, I_DP.ENTITY, I_DP.ENTITY);
					ADD_LIST_ELEMENT ('LOGIN_ENTITY_LIST', I_SR.RN_SR, I_SR.ENTITY, I_SR.ENTITY);
					ADD_LIST_ELEMENT ('LOGIN_ENTITY_LIST', I_XX.RN_XX, I_XX.ENTITY, I_XX.ENTITY);
				END LOOP;
			END LOOP;
		END LOOP;
	END;

CREATE TABLE USER_MAST(
USER_CODE VARCHAR(8) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
ENTITY VARCHAR(50));

CREATE TABLE ENTITY_MAST(
ENTITY_CODE VARCHAR(2) NOT NULL,
ENTITY_NAME VARCHAR(50));

INSERT INTO USER_MAST VALUES ('MSRDBA','12345','SR DP XP');
INSERT INTO USER_MAST VALUES ('ACC-RN','123','SR');
INSERT INTO USER_MAST VALUES ('DP-KT','XYZ','DP XP');

INSERT INTO ENTITY_MAST VALUES ('SR','SR INFO SOLUTION');
INSERT INTO ENTITY_MAST VALUES ('DP','DIGITAL PLANET');
INSERT INTO ENTITY_MAST VALUES ('XP','XEROX POINT');
SELECT * FROM USER_MAST;
SELECT * FROM ENTITY_MAST;

(SELECT DISTINCT (SUBSTR(X, 3, 2))
       FROM (SELECT DISTINCT (REPLACE(ENTITY, ' ')) AS X
       FROM USER_MAST
       WHERE USER_CODE IN ('MSRDBA'))
       WHERE USER_CODE IN ('MSRDBA')) AS XXX

SELECT DISTINCT ROWNUM RN_SR, ENTITY_CODE ||' - '|| ENTITY_NAME AS ENTITY_SHOW
FROM ENTITY_MAST, USER_MAST
WHERE USER_CODE = :LOGIN_USER_CODE
AND :LOGIN_PASSWORD = PASSWORD
AND ENTITY_CODE = (SELECT DISTINCT (SUBSTR(SR, 0, 2))
FROM (SELECT DISTINCT (REPLACE(ENTITY, ' ')) AS SR
FROM USER_MAST
WHERE USER_CODE = :LOGIN_USER_CODE));

SELECT DISTINCT ROWNUM RN_SR, ENTITY_CODE ||' - '|| ENTITY_NAME AS ENTITY_SHOW
FROM ENTITY_MAST, USER_MAST
WHERE USER_CODE = :LOGIN_USER_CODE
AND :LOGIN_PASSWORD = PASSWORD
AND ENTITY_CODE = SUBSTR(REPLACE(ENTITY, ' '), 0, 2);