[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE from GL_ACCESS_SET_NORM_ASSIGN
WHERE status_code = 'I'
AND access_set_id IN
(SELECT access_set_id
FROM GL_ACCESS_SETS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND automatically_created_flag = 'Y');
UPDATE GL_ACCESS_SET_NORM_ASSIGN
SET status_code = NULL
WHERE status_code IN ('D', 'U')
AND access_set_id IN
(SELECT access_set_id
FROM GL_ACCESS_SETS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND automatically_created_flag = 'Y');
*** -- 2) Mark records in GL_ACCESS_SET_NORM_ASSIGN for delete
*** -- based on marked records in GL_LEDGER_LEDGERS
*** -- Again, different statements will be used to process
*** -- legal and management hierarchies.
*** -- 3) Update records in GL_ACCESS_SET_NORM_ASSIGN based
*** -- on updated records in GL_LEDGER___HIERARCHIES.
*** -- 4) Insert new records into GL_ACCESS_SET_NORM_ASSIGN based
*** -- on new records in GL_LEDGER_HIERARCHIES. Several
*** -- statements will be run to process legal and management
*** -- hierarchies. */
GL_MESSAGE.Func_Ent
(func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Norm_Table');
v2 => 'Inserting self mapping records and ALCs ' ||
'under its source ledger access set into ' ||
'GL_ACCESS_SET_NORM_ASSIGN ' ||
'for any new ledgers...');
INSERT into GL_ACCESS_SET_NORM_ASSIGN
(access_set_id, ledger_id, all_segment_value_flag,
segment_value_type_code, access_privilege_code, status_code,
record_id, last_update_date, last_updated_by, last_update_login,
creation_date, created_by, request_id, segment_value,
start_date, end_date, link_id)
(SELECT gll.implicit_access_set_id, glr.target_ledger_id, 'Y',
'S', 'B', 'I', GL_ACCESS_SET_NORM_ASSIGN_S.nextval,
SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID,
NULL, NULL, NULL, NULL
FROM GL_LEDGERS gll,
GL_LEDGER_RELATIONSHIPS glr
WHERE gll.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'L'
AND gll.implicit_access_set_id <>-1
AND glr.source_ledger_id = gll.ledger_id
AND glr.target_ledger_category_code IN ( 'ALC',
DECODE(gll.ledger_category_code,'PRIMARY','PRIMARY',''),
DECODE(gll.ledger_category_code,'SECONDARY', 'SECONDARY',''))
AND glr.relationship_type_code IN ('NONE','JOURNAL','SUBLEDGER')
AND glr.application_id = 101
AND NOT EXISTS
(SELECT 1
FROM GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE glasna.access_set_id = gll.implicit_access_set_id
AND glasna.ledger_id = glr.target_ledger_id
AND glasna.all_segment_value_flag = 'Y'
AND glasna.segment_value_type_code = 'S'
AND glasna.access_privilege_code = 'B'
AND glasna.segment_value is NULL
AND glasna.start_date is NULL
AND glasna.end_date is NULL
AND NVL(glasna.status_code, 'X') <> 'D'));
DELETE from GL_ACCESS_SET_ASSIGNMENTS glasa
WHERE (ABS(glasa.access_set_id), glasa.ledger_id,
glasa.segment_value, glasa.parent_record_id) IN
(SELECT glasai.access_set_id, glasai.ledger_id,
glasai.segment_value, glasai.parent_record_id
FROM GL_ACCESS_SET_ASSIGN_INT glasai
WHERE glasai.status_code = 'D');
SELECT count(*)
INTO row_count
FROM GL_ACCESS_SET_ASSIGN_INT;
v2 => 'Inserting records into GL_ACCESS_SET_ASSIGNMENTS...');
INSERT into GL_ACCESS_SET_ASSIGNMENTS
(access_set_id, ledger_id, segment_value, access_privilege_code,
parent_record_id, last_update_date, last_updated_by, last_update_login,
creation_date, created_by, start_date, end_date)
(SELECT glasai.access_set_id, glasai.ledger_id, glasai.segment_value,
glasai.access_privilege_code, glasai.parent_record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasai.start_date, glasai.end_date
FROM GL_ACCESS_SET_ASSIGN_INT glasai
WHERE glasai.status_code = 'I'
AND NOT EXISTS
(SELECT 1
FROM GL_ACCESS_SET_ASSIGNMENTS glasa
WHERE ( glasa.access_set_id = glasai.access_set_id
OR glasa.access_set_id = -glasai.access_set_id)
AND glasa.parent_record_id = glasai.parent_record_id
AND glasa.ledger_id = glasai.ledger_id
AND glasa.segment_value = glasai.segment_value
AND NVL(glasa.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY')) =
NVL(glasai.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
AND NVL(glasa.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY')) =
NVL(glasai.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY'))));
SELECT 1
INTO row_count
FROM GL_ACCESS_SETS glas,
GL_ACCESS_SET_ASSIGN_INT glasai,
GL_ACCESS_SET_ASSIGNMENTS glasa1,
GL_ACCESS_SET_ASSIGNMENTS glasa2
WHERE ( glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID
OR glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
AND glas.automatically_created_flag = 'Y'
AND glas.security_segment_code = 'M'
AND glasai.access_set_id = glas.access_set_id
AND glasa1.access_set_id = glasai.access_set_id
AND glasa1.ledger_id = glasai.ledger_id
AND glasa1.segment_value = glasai.segment_value
AND glasa2.access_set_id = glasa1.access_set_id
AND glasa2.ledger_id = glasa1.ledger_id
AND glasa2.segment_value = glasa1.segment_value
AND glasa2.rowid <> glasa1.rowid
AND ( NVL(glasa1.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
BETWEEN NVL(glasa2.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
AND NVL(glasa2.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY'))
OR NVL(glasa1.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY'))
BETWEEN NVL(glasa2.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
AND NVL(glasa2.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY')))
AND rownum <= 1;
SELECT distinct glas.name, gll.name, glasa1.segment_value
FROM GL_ACCESS_SETS glas,
GL_ACCESS_SET_ASSIGN_INT glasai,
GL_ACCESS_SET_ASSIGNMENTS glasa1,
GL_ACCESS_SET_ASSIGNMENTS glasa2,
GL_LEDGERS gll
WHERE ( glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID
OR glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
AND glas.automatically_created_flag = 'Y'
AND glas.security_segment_code = 'M'
AND glasai.access_set_id = glas.access_set_id
AND glasa1.access_set_id = glasai.access_set_id
AND glasa1.ledger_id = glasai.ledger_id
AND glasa1.segment_value = glasai.segment_value
AND glasa2.access_set_id = glasa1.access_set_id
AND glasa2.ledger_id = glasa1.ledger_id
AND glasa2.segment_value = glasa1.segment_value
AND glasa2.rowid <> glasa1.rowid
AND ( NVL(glasa1.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
BETWEEN NVL(glasa2.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
AND NVL(glasa2.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY'))
OR NVL(glasa1.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY'))
BETWEEN NVL(glasa2.start_date,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
AND NVL(glasa2.end_date,
TO_DATE('12/31/9999', 'MM/DD/YYYY')))
AND gll.ledger_id = glasa1.ledger_id;
SELECT 1
INTO row_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh
WHERE glsvh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvh.parent_flex_value = 'T'
AND glsvh.status_code = 'I');
SELECT 1
INTO row_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_ACCESS_SETS glas,
GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glas.security_segment_code <> 'F'
AND glasna.access_set_id = glas.access_set_id
AND glasna.all_segment_value_flag = 'Y');
SELECT 1
INTO row_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE glasna.ledger_id IN
(SELECT distinct gllsa.ledger_set_id
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_LEDGERS gll
WHERE gllsa.status_code IN ('I', 'D')
AND gll.ledger_id = gllsa.ledger_set_id
AND gll.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'S'
AND gll.automatically_created_flag = 'N'));
SELECT 1
INTO row_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_ACCESS_SETS glas,
GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE glas.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND glas.automatically_created_flag = 'N'
AND glasna.access_set_id = glas.access_set_id
AND glasna.request_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glasna.status_code IN ('I', 'D', 'U')
AND ( ( glasna.all_segment_value_flag = 'N'
AND glasna.segment_value_type_code = 'S')
OR (glas.security_segment_code = 'F')));
SELECT 1
INTO row_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_ACCESS_SETS glas,
GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE glas.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND glas.automatically_created_flag = 'N'
AND glasna.access_set_id = glas.access_set_id
AND glasna.request_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glasna.status_code IN ('I', 'D', 'U')
AND glasna.segment_value_type_code = 'C');
SELECT 1
INTO row_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_ACCESS_SETS glas,
GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE glas.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND glas.automatically_created_flag = 'N'
AND glas.security_segment_code <> 'F'
AND glasna.access_set_id = glas.access_set_id
AND glasna.request_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glasna.status_code IN ('I', 'D', 'U')
AND glasna.all_segment_value_flag = 'Y');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for segment ' ||
'value hierarchy changes...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id,
DECODE(gllsa.ledger_id,
NULL, glasna.ledger_id, gllsa.ledger_id),
glsvh.child_flex_value, glasna.access_privilege_code,
glsvh.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_SEG_VAL_HIERARCHIES glsvh,
GL_ACCESS_SETS glas,
GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_LEDGER_SET_ASSIGNMENTS gllsa
WHERE glsvh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvh.status_code in ('I', 'D')
AND glas.security_segment_code <> 'F'
AND glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glasna.access_set_id = glas.access_set_id
AND glasna.all_segment_value_flag = 'N'
AND glasna.segment_value_type_code = 'C'
AND glasna.segment_value = glsvh.parent_flex_value
AND NVL(glasna.status_code, 'X') <> 'I'
AND gllsa.ledger_set_id(+) = glasna.ledger_id
AND NVL(gllsa.status_code(+), 'X') <> 'I');
-- (Update is done with parent_record_id and thus will not
-- check for the integrity of the segment value assignments!
--
-- Also, records in gllsa will never have a status_code of U.
row_count := SQL%ROWCOUNT;
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for new ' ||
'segment values...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id,
DECODE(gllsa.ledger_id,
NULL, glasna.ledger_id, gllsa.ledger_id),
glsvh.child_flex_value, glasna.access_privilege_code,
'I', glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_SEG_VAL_HIERARCHIES glsvh,
GL_ACCESS_SETS glas,
GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_LEDGER_SET_ASSIGNMENTS gllsa
WHERE glsvh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvh.parent_flex_value = 'T'
AND glsvh.status_code = 'I'
AND glas.secured_seg_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glas.security_segment_code <> 'F'
AND glasna.access_set_id = glas.access_set_id
AND glasna.all_segment_value_flag = 'Y'
AND NVL(glasna.status_code, 'X') <> 'I'
AND gllsa.ledger_set_id(+) = glasna.ledger_id
AND NVL(gllsa.status_code(+), 'X') <> 'I');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for legal ' ||
'and management implicit access sets...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id, glasna.ledger_id,
NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
glasna.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_LEDGERS gll
WHERE glasna.status_code IN ('I')
AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND gll.implicit_access_set_id = glasna.access_set_id
AND gll.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'L');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
'ledger sets ' ||
'contained in full ledger type access sets ' ||
'or access assignment with a single segment '||
'value...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id, gllsa.ledger_id,
NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
gllsa.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_LEDGERS gll,
GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_ACCESS_SETS glas
WHERE gllsa.status_code IN ('I', 'D')
AND gll.ledger_id = gllsa.ledger_set_id
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'S'
AND gll.automatically_created_flag = 'N'
AND glasna.ledger_id = gllsa.ledger_set_id
AND NVL(glasna.status_code, 'X') NOT IN ('I', 'U')
AND glas.access_set_id = glasna.access_set_id
AND ( glas.security_segment_code = 'F'
OR ( glasna.segment_value_type_code = 'S'
AND glasna.all_segment_value_flag = 'N')));
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for access sets ' ||
'containing changed explicit ledger sets ' ||
'with all segment values assigned...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id, gllsa.ledger_id,
glsvh.child_flex_value, glasna.access_privilege_code,
gllsa.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_LEDGERS gll,
GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_ACCESS_SETS glas,
GL_SEG_VAL_HIERARCHIES glsvh
WHERE gllsa.status_code IN ('I', 'D')
AND gll.ledger_id = gllsa.ledger_set_id
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'S'
AND gll.automatically_created_flag = 'N'
AND glasna.ledger_id = gllsa.ledger_set_id
AND glasna.all_segment_value_flag = 'Y'
AND NVL(glasna.status_code, 'X') NOT IN ('I', 'U')
AND glas.access_set_id = glasna.access_set_id
AND glas.security_segment_code <> 'F'
AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
AND glsvh.parent_flex_value = 'T'
AND NVL(glsvh.status_code, 'X') <> 'I');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for access sets ' ||
'containing changed explicit ledger sets ' ||
'with parent segment values assigned...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id, gllsa.ledger_id,
glsvh.child_flex_value, glasna.access_privilege_code,
gllsa.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_LEDGERS gll,
GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_ACCESS_SETS glas,
GL_SEG_VAL_HIERARCHIES glsvh
WHERE gllsa.status_code IN ('I', 'D')
AND gll.ledger_id = gllsa.ledger_set_id
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'S'
AND gll.automatically_created_flag = 'N'
AND glasna.ledger_id = gllsa.ledger_set_id
AND glasna.all_segment_value_flag = 'N'
AND glasna.segment_value_type_code = 'C'
AND NVL(glasna.status_code, 'X') NOT IN ('I', 'U')
AND glas.access_set_id = glasna.access_set_id
AND glas.security_segment_code <> 'F'
AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
AND glsvh.parent_flex_value = glasna.segment_value
AND NVL(glsvh.status_code, 'X') <> 'I');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for implicit ' ||
'access sets associated with changed ' ||
'explicit ledger sets... ');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id, gllsa.ledger_id,
NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
gllsa.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_LEDGERS gll,
GL_ACCESS_SET_NORM_ASSIGN glasna
WHERE gllsa.status_code IN ('I', 'D')
AND gll.ledger_id = gllsa.ledger_set_id
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.automatically_created_flag = 'N'
AND gll.object_type_code = 'S'
AND glasna.access_set_id = gll.implicit_access_set_id
AND glasna.ledger_id = gllsa.ledger_set_id
AND glasna.status_code = 'I'
AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID);
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
'access sets having full ledger access or ' ||
'access assignments with single segment ' ||
'value assigned...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id,
DECODE(gllsa.ledger_id,
NULL, glasna.ledger_id, gllsa.ledger_id),
NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
glasna.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_ACCESS_SETS glas,
GL_LEDGER_SET_ASSIGNMENTS gllsa
WHERE glasna.status_code IN ('I', 'D')
AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glas.access_set_id = glasna.access_set_id
AND glas.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND glas.automatically_created_flag = 'N'
AND ( ( glasna.all_segment_value_flag = 'N'
AND glasna.segment_value_type_code = 'S')
OR glas.security_segment_code = 'F')
AND gllsa.ledger_set_id(+) = glasna.ledger_id
AND NVL(gllsa.status_code(+), 'X') <> 'I');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
'access sets having access assignments ' ||
'with all segment values assigned...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id,
DECODE(gllsa.ledger_id,
NULL, glasna.ledger_id, gllsa.ledger_id),
glsvh.child_flex_value, glasna.access_privilege_code,
glasna.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_ACCESS_SETS glas,
GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_SEG_VAL_HIERARCHIES glsvh
WHERE glasna.status_code IN ('I', 'D', 'U')
AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glasna.all_segment_value_flag = 'Y'
AND glas.access_set_id = glasna.access_set_id
AND glas.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND glas.automatically_created_flag = 'N'
AND glas.security_segment_code <> 'F'
AND gllsa.ledger_set_id(+) = glasna.ledger_id
AND NVL(gllsa.status_code(+), 'X') <> 'I'
AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
AND glsvh.parent_flex_value = 'T'
AND NVL(glsvh.status_code, 'X') <> 'I');
v2 => 'Inserting records into ' ||
'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
'access sets having parent segment values ' ||
'assigned...');
INSERT into GL_ACCESS_SET_ASSIGN_INT
(access_set_id, ledger_id, segment_value, access_privilege_code,
status_code, parent_record_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, start_date, end_date)
(SELECT glasna.access_set_id,
DECODE(gllsa.ledger_id,
NULL, glasna.ledger_id, gllsa.ledger_id),
glsvh.child_flex_value, glasna.access_privilege_code,
glasna.status_code, glasna.record_id, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
glasna.start_date, glasna.end_date
FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
GL_ACCESS_SETS glas,
GL_LEDGER_SET_ASSIGNMENTS gllsa,
GL_SEG_VAL_HIERARCHIES glsvh
WHERE glasna.status_code IN ('I', 'D', 'U')
AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glasna.all_segment_value_flag = 'N'
AND glasna.segment_value_type_code = 'C'
AND glas.access_set_id = glasna.access_set_id
AND glas.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND glas.automatically_created_flag = 'N'
AND glas.security_segment_code <> 'F'
AND gllsa.ledger_set_id(+) = glasna.ledger_id
AND NVL(gllsa.status_code(+), 'X') <> 'I'
AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
AND glsvh.parent_flex_value = glasna.segment_value
AND NVL(glsvh.status_code, 'X') <> 'I');
SELECT DISTINCT MIN(glasai.access_set_id),
MIN(glasai.ledger_id), MIN(glasai.segment_value)
FROM GL_ACCESS_SET_ASSIGN_INT glasai,
GL_ACCESS_SETS glas
WHERE glasai.status_code = 'I'
AND glasai.access_set_id > 0
AND glas.access_set_id = ABS(glasai.access_set_id)
AND glas.automatically_created_flag = 'N'
GROUP BY glasai.access_set_id, glasai.ledger_id,
glasai.segment_value
HAVING count(*) > 1;
UPDATE GL_ACCESS_SET_ASSIGNMENTS glasa1
SET glasa1.access_set_id = -glasa1.access_set_id
WHERE glasa1.rowid IN
(SELECT MIN(glasa2.rowid)
FROM GL_ACCESS_SET_ASSIGN_INT glasai,
GL_ACCESS_SET_ASSIGNMENTS glasa2,
GL_ACCESS_SET_ASSIGNMENTS glasa3
WHERE glasai.status_code = 'D'
AND glasa2.access_set_id = -glasai.access_set_id
AND glasa2.ledger_id = glasai.ledger_id
AND glasa2.segment_value = glasai.segment_value
AND glasa2.access_privilege_code = 'B'
AND glasa3.access_set_id(+) = glasai.access_set_id
AND glasa3.ledger_id(+) = glasai.ledger_id
AND glasa3.segment_value(+) = glasai.segment_value
AND glasa3.rowid is NULL
GROUP BY glasa2.access_set_id, glasa2.ledger_id,
glasa2.segment_value);
UPDATE GL_ACCESS_SET_ASSIGNMENTS glasa1
SET glasa1.access_set_id = -glasa1.access_set_id
WHERE glasa1.rowid IN
(SELECT MIN(glasa2.rowid)
FROM GL_ACCESS_SET_ASSIGN_INT glasai,
GL_ACCESS_SET_ASSIGNMENTS glasa2,
GL_ACCESS_SET_ASSIGNMENTS glasa3
WHERE glasai.status_code = 'D'
AND glasa2.access_set_id = -glasai.access_set_id
AND glasa2.ledger_id = glasai.ledger_id
AND glasa2.segment_value = glasai.segment_value
AND glasa2.access_privilege_code = 'R'
AND glasa3.access_set_id(+) = glasai.access_set_id
AND glasa3.ledger_id(+) = glasai.ledger_id
AND glasa3.segment_value(+) = glasai.segment_value
AND glasa3.rowid is NULL
GROUP BY glasa2.access_set_id, glasa2.ledger_id,
glasa2.segment_value);
UPDATE GL_ACCESS_SET_ASSIGN_INT glasai1
SET glasai1.access_set_id = -glasai1.access_set_id
WHERE glasai1.rowid IN
(SELECT glasai2.rowid
FROM GL_ACCESS_SET_ASSIGN_INT glasai2,
GL_ACCESS_SETS glas,
GL_ACCESS_SET_ASSIGNMENTS glasa
WHERE glasai2.status_code = 'I'
AND glasai2.access_set_id > 0
AND glas.access_set_id = glasai2.access_set_id
AND glas.automatically_created_flag = 'N'
AND glasa.access_set_id = glasai2.access_set_id
AND glasa.ledger_id = glasai2.ledger_id
AND glasa.segment_value = glasai2.segment_value
AND ( glasa.access_privilege_code = 'B'
OR ( glasa.access_privilege_code = 'R'
AND glasai2.access_privilege_code = 'R')));
UPDATE GL_ACCESS_SET_ASSIGN_INT glasai1
SET glasai1.access_set_id = -glasai1.access_set_id
WHERE glasai1.access_set_id = curr_as_id
AND glasai1.ledger_id = curr_ldg_id
AND glasai1.segment_value = curr_seg_val
AND glasai1.status_code = 'I'
AND EXISTS
(SELECT 1
FROM GL_ACCESS_SET_ASSIGN_INT glasai2,
GL_ACCESS_SETS glas
WHERE glasai2.status_code IN ('I', 'U')
AND glasai2.access_set_id = glasai1.access_set_id
AND glasai2.ledger_id = glasai1.ledger_id
AND glasai2.segment_value = glasai1.segment_value
AND glas.access_set_id = glasai1.access_set_id
AND glas.automatically_created_flag = 'N'
AND ( ( glasai2.access_privilege_code =
glasai1.access_privilege_code
AND glasai2.rowid < glasai1.rowid)
OR ( glasai2.access_privilege_code = 'B'
AND glasai1.access_privilege_code = 'R')));
UPDATE GL_ACCESS_SET_ASSIGNMENTS glasa
SET glasa.access_set_id = -glasa.access_set_id
WHERE glasa.access_privilege_code = 'R'
AND glasa.access_set_id > 0
AND (glasa.access_set_id, glasa.ledger_id,
glasa.segment_value) IN
(SELECT DISTINCT
glasai.access_set_id, glasai.ledger_id,
glasai.segment_value
FROM GL_ACCESS_SET_ASSIGN_INT glasai,
GL_ACCESS_SETS glas
WHERE glasai.status_code IN ('I', 'U')
AND glasai.access_privilege_code = 'B'
AND glasai.access_set_id > 0
AND glas.access_set_id = glasai.access_set_id
AND glas.automatically_created_flag = 'N');
DELETE from GL_ACCESS_SET_NORM_ASSIGN
WHERE status_code = 'D'
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND access_set_id IN
(SELECT access_set_id
FROM GL_ACCESS_SETS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND automatically_created_flag = 'N');
DELETE from GL_ACCESS_SET_NORM_ASSIGN
WHERE status_code = 'D'
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND access_set_id IN
(SELECT implicit_access_set_id
FROM GL_LEDGERS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND object_type_code = 'L');
UPDATE GL_ACCESS_SET_NORM_ASSIGN
SET status_code = NULL, request_id = NULL
WHERE status_code IN ('I', 'U')
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND access_set_id IN
(SELECT implicit_access_set_id
FROM GL_LEDGERS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND object_type_code = 'S');
UPDATE GL_ACCESS_SET_NORM_ASSIGN
SET status_code = NULL, request_id = NULL
WHERE status_code IN ('I', 'U')
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND access_set_id IN
(SELECT access_set_id
FROM GL_ACCESS_SETS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND automatically_created_flag = 'N');
UPDATE GL_ACCESS_SET_NORM_ASSIGN
SET status_code = NULL, request_id = NULL
WHERE status_code IN ('I', 'U')
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND access_set_id IN
(SELECT implicit_access_set_id
FROM GL_LEDGERS
WHERE chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND object_type_code = 'L');