The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE from GL_LEDGER_SET_ASSIGNMENTS
WHERE status_code = 'I'
AND ledger_set_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
UPDATE GL_LEDGER_SET_ASSIGNMENTS
SET status_code = NULL
WHERE status_code = 'D'
AND ledger_set_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
DELETE from GL_ACCESS_SET_NORM_ASSIGN
WHERE status_code = 'I'
AND access_set_id IN
(SELECT implicit_access_set_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
v2 => 'Insert access information into ' ||
'GL_ACCESS_SET_NORM_ASSIGN ' ||
'for new ledger sets...');
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, link_id, last_update_date, last_updated_by,
last_update_login, creation_date, created_by, request_id,
segment_value, start_date, end_date)
(SELECT distinct
gll.implicit_access_set_id, gllsna.ledger_set_id, 'Y',
'S', 'B', 'I', -1,
NULL, 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, gllsna.start_date, gllsna.end_date
FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
GL_LEDGERS gll
WHERE gllsna.status_code = 'I'
AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND gll.ledger_id = gllsna.ledger_set_id
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.automatically_created_flag = 'N'
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 = gllsna.ledger_set_id
AND glasna.access_privilege_code = 'B'
AND glasna.all_segment_value_flag = 'Y'
AND glasna.segment_value_type_code = 'S'
AND glasna.segment_value is NULL
AND NVL(glasna.status_code, 'X') <> 'D'));
UPDATE GL_ACCESS_SET_NORM_ASSIGN glasna
SET glasna.record_id = GL_ACCESS_SET_NORM_ASSIGN_S.nextval
WHERE glasna.status_code = 'I'
AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND glasna.record_id = -1
AND glasna.access_set_id IN
(SELECT gll.implicit_access_set_id
FROM GL_LEDGERS gll
WHERE gll.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.automatically_created_flag = 'N'
AND gll.object_type_code = 'S');
'in GL_LEDGER_SET_ASSIGNMENTS for delete...');
'deleted links ' ||
'for delete in GL_LEDGER_SET_ASSIGNMENTS...');
UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
SET gllsa1.status_code = 'D'
WHERE NVL(gllsa1.status_code, 'X') <> 'D'
AND (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
(SELECT distinct gllsa2.ledger_set_id, gllsna.ledger_id
FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
GL_LEDGERS gll,
GL_LEDGER_SET_ASSIGNMENTS gllsa2
WHERE gllsna.status_code = 'D'
AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND gll.ledger_id = gllsna.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 gllsa2.ledger_id = gllsna.ledger_set_id)
AND gllsa1.ledger_set_id <> gllsa1.ledger_id;
v2 => 'Second, mark descendants of deleted ledger sets ' ||
'for delete in GL_LEDGER_SET_ASSIGNMENTS...');
UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
SET gllsa1.status_code = 'D'
WHERE NVL(gllsa1.status_code, 'X') <> 'D'
AND (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
(SELECT distinct gllsa2.ledger_set_id, gllsa3.ledger_id
FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
GL_LEDGERS gll,
GL_LEDGER_SET_ASSIGNMENTS gllsa2,
GL_LEDGER_SET_ASSIGNMENTS gllsa3
WHERE gllsna.status_code = 'D'
AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND gll.ledger_id = gllsna.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 gllsa2.ledger_id = gllsna.ledger_set_id
AND gllsa3.ledger_set_id = gllsna.ledger_id)
AND gllsa1.ledger_set_id <> gllsa1.ledger_id;
v2 => 'Third, reconnect all deleted mappings in ' ||
'GL_LEDGER_SET_ASSIGNMENTS that are included via ' ||
'other effective paths...');
UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
SET gllsa1.status_code = NULL
WHERE gllsa1.status_code = 'D'
AND gllsa1.ledger_set_id IN
(SELECT gll.ledger_id
FROM GL_LEDGERS gll
WHERE 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 ( EXISTS
(SELECT 1
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa2,
GL_LEDGER_SET_ASSIGNMENTS gllsa3
WHERE gllsa2.status_code is NULL
AND gllsa2.ledger_id = gllsa1.ledger_id
AND gllsa3.status_code is NULL
AND gllsa3.ledger_set_id = gllsa1.ledger_set_id
AND gllsa3.ledger_id = gllsa2.ledger_set_id)
OR EXISTS
(SELECT 1
FROM GL_LEDGER_SET_NORM_ASSIGN gllsna
WHERE gllsna.ledger_set_id = gllsa1.ledger_set_id
AND gllsna.ledger_id = gllsa1.ledger_id
AND gllsna.status_code is NULL));
v2 => 'Inserting self mapping record for new ledger sets ' ||
'into GL_LEDGER_SET_ASSIGNMENTS');
INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
(ledger_set_id, ledger_id, status_code, last_update_date,
last_updated_by, last_update_login, creation_date,
created_by, start_date, end_date)
(SELECT distinct gll.ledger_id, gll.ledger_id, 'I', SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
NULL, NULL
FROM GL_LEDGERS gll
WHERE gll.object_type_code = 'S'
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.automatically_created_flag = 'N'
AND NOT EXISTS
(SELECT 1
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa
WHERE gllsa.ledger_set_id = gll.ledger_id
AND gllsa.ledger_id = gll.ledger_id
AND NVL(gllsa.status_code, 'X') <> 'D'));
v2 => 'Inserting new ledgers to the respective ' ||
'ledger sets into GL_LEDGER_SET_ASSIGNMENTS...');
INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
(ledger_set_id, ledger_id, status_code, last_update_date,
last_updated_by, last_update_login, creation_date,
created_by, start_date, end_date)
(SELECT distinct gllsa.ledger_set_id, gllsna.ledger_id, 'I', SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
NULL, NULL
FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
GL_LEDGERS gll,
GL_LEDGER_SET_ASSIGNMENTS gllsa
WHERE gllsna.status_code = 'I'
AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND gll.ledger_id = gllsna.ledger_id
AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND gll.object_type_code = 'L'
AND gllsa.ledger_id = gllsna.ledger_set_id
AND NVL(gllsa.status_code, 'X') <> 'D'
AND NOT EXISTS
(SELECT 1
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa2
WHERE gllsa2.ledger_set_id = gllsa.ledger_set_id
AND gllsa2.ledger_id = gllsna.ledger_id
AND NVL(gllsa2.status_code, 'X') <> 'D'));
v2 => 'Inserting all descendants of new ledger sets into ' ||
'GL_LEDGER_SET_ASSIGNMENTS...');
INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
(ledger_set_id, ledger_id, status_code, last_update_date,
last_updated_by, last_update_login, creation_date,
created_by, start_date, end_date)
(SELECT distinct gllsa1.ledger_set_id, gllsa2.ledger_id,
'I', SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
NULL, NULL
FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
GL_LEDGERS gll,
GL_LEDGER_SET_ASSIGNMENTS gllsa1,
GL_LEDGER_SET_ASSIGNMENTS gllsa2
WHERE gllsna.status_code = 'I'
AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND gll.ledger_id = gllsna.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 gllsa1.ledger_id = gllsna.ledger_set_id
AND NVL(gllsa1.status_code, 'X') <> 'D'
AND gllsa2.ledger_set_id = gllsna.ledger_id
AND NVL(gllsa2.status_code, 'X') <> 'D'
AND NOT EXISTS
(SELECT 1
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa3
WHERE gllsa3.ledger_set_id = gllsa1.ledger_set_id
AND gllsa3.ledger_id = gllsa2.ledger_id
AND NVL(gllsa3.status_code, 'X') <> 'D'));
SELECT 1
INTO loop_exists
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_LEDGER_SET_ASSIGNMENTS gllsa1,
GL_LEDGERS gll,
GL_LEDGER_SET_ASSIGNMENTS gllsa2
WHERE gllsa1.status_code = 'I'
AND gll.ledger_id = gllsa1.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 gllsa1.ledger_set_id <> gllsa1.ledger_id
AND NVL(gllsa2.status_code, 'X') <> 'D'
AND gllsa2.ledger_set_id = gllsa1.ledger_id
AND gllsa2.ledger_id = gllsa1.ledger_set_id);
DELETE from GL_LEDGER_SET_NORM_ASSIGN
WHERE status_code = 'D'
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND ledger_set_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
UPDATE GL_LEDGER_SET_NORM_ASSIGN
SET status_code = NULL, request_id = NULL
WHERE status_code = 'I'
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND ledger_set_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
DELETE from GL_LEDGER_SET_ASSIGNMENTS
WHERE status_code = 'D'
AND ledger_set_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
UPDATE GL_LEDGER_SET_ASSIGNMENTS
SET status_code = NULL
WHERE status_code = 'I'
AND ledger_set_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE object_type_code = 'S'
AND chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);