SELECT glasna.access_set_id AS ACCESS_SET_ID, DECODE(gllsa.ledger_id, NULL, glasna.ledger_id, gllsa.ledger_id) AS LEDGER_ID, DECODE(MIN(DECODE(glasna.all_segment_value_flag, 'Y', DECODE(glasna.access_privilege_code, 'B', 1, 'R', 3), DECODE(glasna.access_privilege_code, 'B', 2, 'R', 3))), 1, 'F', 2, 'B', 3, 'R') AS ACCESS_PRIVILEGE_CODE, MAX(glasna.LAST_UPDATE_DATE) AS LAST_UPDATE_DATE, 0 AS LAST_UPDATED_BY, MAX(glasna.CREATION_DATE) AS CREATION_DATE, 0 AS CREATED_BY, 0 AS LAST_UPDATE_LOGIN, TO_DATE(NULL) AS START_DATE, TO_DATE(NULL) AS END_DATE FROM GL_ACCESS_SETS glas, GL_ACCESS_SET_NORM_ASSIGN glasna, GL_LEDGER_SET_ASSIGNMENTS gllsa WHERE glas.automatically_created_flag = 'N' AND glasna.access_set_id = glas.access_set_id AND NVL(glasna.status_code, 'X') <> 'I' AND gllsa.ledger_set_id (+) = glasna.ledger_id GROUP BY glasna.access_set_id, DECODE(gllsa.ledger_id, NULL, glasna.ledger_id, gllsa.ledger_id) UNION ALL Select glas.access_set_id AS ACCESS_SET_ID, DECODE(gll.object_type_code,'S',gllsa.ledger_id,glasna.ledger_id) AS LEDGER_ID, DECODE(MAX(DECODE(gll.object_type_code,'S',1,DECODE(gllsa.ledger_id,NULL, DECODE(glasna.all_segment_value_flag,'Y', DECODE(glasna.access_privilege_code,'R',3,'B',1), DECODE(glasna.access_privilege_code,'R',3,'B',2)), DECODE(glasna2.all_segment_value_flag,'Y', DECODE(glasna2.access_privilege_code,'R',3,'B',1), DECODE(glasna2.access_privilege_code,'R',3,'B',2))))), 1,'F',2,'B',3,'R') AS ACCESS_PRIVILEGE_CODE, MAX(glasna2.LAST_UPDATE_DATE) AS LAST_UPDATE_DATE, 0 AS LAST_UPDATED_BY, MAX(glasna2.CREATION_DATE) AS CREATION_DATE, 0 AS CREATED_BY, 0 AS LAST_UPDATE_LOGIN, TO_DATE(NULL) AS START_DATE, TO_DATE(NULL) AS END_DATE FROM gl_ledgers gll, gl_access_sets glas, gl_access_set_norm_assign glasna, gl_ledger_set_assignments gllsa, gl_access_set_norm_assign glasna2 WHERE glas.access_set_id = gll.implicit_access_set_id AND glas.automatically_created_flag = 'Y' AND glasna.access_set_id = glas.access_set_id AND NVL(glasna.status_code,'X') <> 'I' AND gllsa.ledger_set_id (+) = glasna.ledger_id AND NVL(gllsa.status_code (+),'X') <> 'I' AND glasna2.access_set_id = DECODE(gllsa.ledger_set_id , NULL, glasna.access_set_id, glasna.access_set_id) AND glasna2.ledger_id = DECODE(gll.object_type_code,'S',glasna.ledger_id,NVL(gllsa.ledger_id,glasna.ledger_id)) AND NVL(glasna2.status_code, 'X') <> 'I' GROUP BY glas.access_set_id, DECODE(gll.object_type_code,'S',gllsa.ledger_id,glasna.ledger_id)
[Home] [Help]