[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE
FROM GL_SEG_VAL_NORM_HIERARCHY
WHERE status_code = l_status_flag
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
UPDATE GL_SEG_VAL_NORM_HIERARCHY
SET status_code = NULL
WHERE status_code ='D'
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
DELETE
FROM GL_SEG_VAL_HIERARCHIES
WHERE status_code = l_status_flag
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
UPDATE GL_SEG_VAL_HIERARCHIES
SET status_code = NULL
WHERE status_code ='D'
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
SELECT 1
INTO t_record_check_id
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM FND_ID_FLEX_SEGMENTS fifs
WHERE fifs.application_id = 101
AND fifs.id_flex_code = 'GL#'
AND fifs.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
OR EXISTS
(SELECT 1
FROM FND_FLEX_VALUE_NORM_HIERARCHY ffvnh
WHERE ffvnh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND ROWNUM = 1);
UPDATE GL_SEG_VAL_HIERARCHIES glsvh
SET glsvh.status_code = 'U',
glsvh.summary_flag =
(SELECT DISTINCT ffv.summary_flag
FROM FND_FLEX_VALUES ffv
WHERE ffv.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND ffv.flex_value = glsvh.child_flex_value)
WHERE glsvh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvh.parent_flex_value = 'T'
AND glsvh.child_flex_value IN
(SELECT ffv2.flex_value
FROM FND_FLEX_VALUES ffv2
WHERE ffv2.flex_value_set_id =
glsvh.flex_value_set_id
AND ffv2.flex_value = glsvh.child_flex_value
AND ffv2.summary_flag <> glsvh.summary_flag);
INSERT INTO GL_SEG_VAL_HIERARCHIES
(flex_value_set_id, parent_flex_value, child_flex_value,
summary_flag, status_code, created_by, creation_date,
last_updated_by, last_update_login, last_update_date)
(SELECT DISTINCT ffv.flex_value_set_id, 'T', ffv.flex_value,
ffv.summary_flag, 'I',
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
SYSDATE
FROM FND_FLEX_VALUES ffv
WHERE ffv.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND NOT EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh
WHERE glsvh.flex_value_set_id =
ffv.flex_value_set_id
AND glsvh.parent_flex_value = 'T'
AND glsvh.child_flex_value = ffv.flex_value
AND glsvh.summary_flag = ffv.summary_flag));
sqlbuf := 'INSERT INTO GL_SEG_VAL_HIERARCHIES
(flex_value_set_id, parent_flex_value,
child_flex_value, summary_flag,
status_code, created_by,
creation_date, last_updated_by,
last_update_login, last_update_date)
(SELECT DISTINCT :v_id,
''T'', tv.'||column_name||' ,
''N'', ''I'',
:user_id,
SYSDATE, :u_id,
:log_id,
SYSDATE
FROM ' ||add_table || ' tv
WHERE NOT EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh
WHERE glsvh.flex_value_set_id
= :vs_id
AND glsvh.parent_flex_value
= ''T''
AND glsvh.child_flex_value
= tv.'||column_name|| ' ))';
v2 =>'Inserting ''T'' records for table validated'
||' value set into the table '
||' GL_SEG_VAL_HIERARCHIES');
SELECT count(*) INTO l_no_rows
FROM
DUAL
WHERE EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES
WHERE status_code = 'I'
AND flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
UPDATE GL_SEG_VAL_NORM_HIERARCHY glsvnh
SET glsvnh.status_code = 'D'
WHERE glsvnh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND
(NOT EXISTS
(SELECT 1
FROM FND_FLEX_VALUE_NORM_HIERARCHY ffvnh
WHERE ffvnh.flex_value_set_id =
glsvnh.flex_value_set_id
AND ffvnh.range_attribute =
DECODE(glsvnh.summary_flag,'Y','P','N','C')
AND ffvnh.parent_flex_value =
glsvnh.parent_flex_value
AND glsvnh.child_flex_value
BETWEEN ffvnh.child_flex_value_low
AND ffvnh.child_flex_value_high)
OR
EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh
WHERE glsvh.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh.parent_flex_value = 'T'
AND glsvh.child_flex_value =
glsvnh.child_flex_value
AND glsvh.status_code = 'U'
AND glsvh.summary_flag <>
glsvnh.summary_flag));
v2 =>'Insert any new parent-child'
||' segment value mappings into'
||' the table'
||' GL_SEG_VAL_NORM_HIERARCHY');
INSERT INTO GL_SEG_VAL_NORM_HIERARCHY
(flex_value_set_id, parent_flex_value, child_flex_value,
summary_flag, status_code, created_by, creation_date,
last_updated_by, last_update_login, last_update_date)
(SELECT DISTINCT ffvnh.flex_value_set_id,
ffvnh.parent_flex_value,
glsvh.child_flex_value,
glsvh.summary_flag, 'I',
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
SYSDATE
FROM FND_FLEX_VALUE_NORM_HIERARCHY ffvnh,
GL_SEG_VAL_HIERARCHIES glsvh
WHERE ffvnh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvh.flex_value_set_id = ffvnh.flex_value_set_id
AND glsvh.parent_flex_value = 'T'
AND glsvh.summary_flag =
DECODE(ffvnh.range_attribute,
'P','Y', 'C','N')
AND glsvh.child_flex_value
BETWEEN ffvnh.child_flex_value_low
AND ffvnh.child_flex_value_high
AND glsvh.child_flex_value <> 'T'
AND NOT EXISTS
(SELECT 1
FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh2
WHERE glsvnh2.flex_value_set_id =
ffvnh.flex_value_set_id
AND glsvnh2.parent_flex_value =
ffvnh.parent_flex_value
AND glsvnh2.child_flex_value =
glsvh.child_flex_value
AND glsvnh2.summary_flag =
DECODE(ffvnh.range_attribute ,
'P','Y', 'C', 'N')));
UPDATE GL_SEG_VAL_HIERARCHIES GLSVH
SET status_code ='D'
WHERE NVL(glsvh.status_code,'X') <>'D'
AND (glsvh.flex_value_set_id , glsvh.parent_flex_value,
glsvh.child_flex_value, glsvh.summary_flag) IN
(SELECT DISTINCT glsvnh.flex_value_set_id,
glsvh1.parent_flex_value,
glsvnh.child_flex_value,
glsvnh.summary_flag
FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
GL_SEG_VAL_HIERARCHIES glsvh1
WHERE glsvnh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvnh.status_code ='D'
AND glsvh1.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh1.child_flex_value =
glsvnh.parent_flex_value
AND glsvh1.parent_flex_value <> 'T');
||' contain deleted child via'
||' deleted parent in the'
||' table GL_SEG_VAL_HIERARCHIES');
||' contain deleted parent child'
||' and its descendants in the'
||' table GL_SEG_VAL_HIERARCHIES');
UPDATE GL_SEG_VAL_HIERARCHIES GLSVH
SET status_code ='D'
WHERE NVL(glsvh.status_code,'X') <>'D'
AND (glsvh.flex_value_set_id , glsvh.parent_flex_value,
glsvh.child_flex_value, glsvh.summary_flag) IN
(SELECT DISTINCT glsvnh.flex_value_set_id,
glsvh1.parent_flex_value,
glsvh2.child_flex_value,
glsvh2.summary_flag
FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
GL_SEG_VAL_HIERARCHIES glsvh1,
GL_SEG_VAL_HIERARCHIES glsvh2
WHERE glsvnh.status_code ='D'
AND glsvnh.summary_flag = 'Y'
AND glsvnh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvh1.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh1.child_flex_value =
glsvnh.parent_flex_value
AND glsvh1.parent_flex_value <> 'T'
AND glsvh2.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh2.parent_flex_value =
glsvnh.child_flex_value);
||' any deleted child mapped to the'
||' parent through someother path');
UPDATE GL_SEG_VAL_HIERARCHIES glsvh1
SET glsvh1.status_code = NULL
WHERE glsvh1.status_code ='D'
AND glsvh1.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND (EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh2,
GL_SEG_VAL_HIERARCHIES glsvh3
WHERE glsvh2.flex_value_set_id =
glsvh1.flex_value_set_id
AND glsvh2.status_code IS NULL
AND glsvh2.child_flex_value =
glsvh1.child_flex_value
AND glsvh3.flex_value_set_id =
glsvh2.flex_value_set_id
AND glsvh3.status_code is NULL
AND glsvh3.parent_flex_value =
glsvh1.parent_flex_value
AND glsvh3.child_flex_value =
glsvh2.parent_flex_value)
OR EXISTS
(SELECT 1
FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh
WHERE glsvnh.flex_value_set_id = glsvh1.flex_value_set_id
AND glsvnh.parent_flex_value = glsvh1.parent_flex_value
AND glsvnh.child_flex_value = glsvh1.child_flex_value
AND glsvnh.status_code IS NULL
AND glsvnh.summary_flag = glsvh1.summary_flag)) ;
v2 =>'Inserting Parent segement value'
||' with itself as child into the table'
||' GL_SEG_VAL_HIERARCHIES');
INSERT INTO GL_SEG_VAL_HIERARCHIES
(flex_value_set_id, parent_flex_value, child_flex_value,
summary_flag, status_code, created_by, creation_date,
last_updated_by, last_update_login, last_update_date)
(SELECT DISTINCT glsvh.flex_value_set_id, glsvh.child_flex_value,
glsvh.child_flex_value, 'Y', 'I',
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
SYSDATE
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 IN ('I','U')
AND glsvh.summary_flag = 'Y'
AND NOT EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh1
WHERE glsvh1.flex_value_set_id =
glsvh.flex_value_set_id
AND glsvh1.parent_flex_value =
glsvh.child_flex_value
AND glsvh1.child_flex_value =
glsvh.child_flex_value
AND NVL(glsvh1.status_code,'X') <> 'D'
AND glsvh1.summary_flag = 'Y') );
v2 =>'Inserting deatail child mapping(s)'
||' into table GL_SEG_VAL_HIERARCHIES');
INSERT INTO GL_SEG_VAL_HIERARCHIES
(flex_value_set_id, parent_flex_value,
child_flex_value, summary_flag, status_code,
created_by, creation_date, last_updated_by,
last_update_login, last_update_date)
(SELECT DISTINCT glsvnh.flex_value_set_id,
glsvh.parent_flex_value,
glsvnh.child_flex_value,
glsvnh.summary_flag, 'I',
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
SYSDATE
FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
GL_SEG_VAL_HIERARCHIES glsvh
WHERE glsvnh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvnh.status_code = 'I'
AND glsvnh.summary_flag = 'N'
AND glsvh.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh.child_flex_value =
glsvnh.parent_flex_value
AND NVL(glsvh.status_code, 'X') <> 'D'
AND NOT EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh2
WHERE glsvh2.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh2.parent_flex_value =
glsvh.parent_flex_value
AND glsvh2.child_flex_value =
glsvnh.child_flex_value
AND glsvh2.summary_flag =
glsvnh.summary_flag
AND NVL(glsvh2.status_code,'X') <>'D'));
v2 =>'Inserting Parent child mapping(s)'
||'into table GL_SEG_VAL_HIERARCHIES');
INSERT INTO GL_SEG_VAL_HIERARCHIES
(flex_value_set_id, parent_flex_value,
child_flex_value, summary_flag, status_code,
created_by, creation_date, last_updated_by,
last_update_login, last_update_date)
(SELECT DISTINCT glsvnh.flex_value_set_id,
glsvh1.parent_flex_value,
glsvh2.child_flex_value,
glsvh2.summary_flag, 'I',
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
SYSDATE
FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
GL_SEG_VAL_HIERARCHIES glsvh1,
GL_SEG_VAL_HIERARCHIES glsvh2
WHERE glsvnh.flex_value_set_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND glsvnh.status_code = 'I'
AND glsvnh.summary_flag = 'Y'
AND glsvh1.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh1.child_flex_value =
glsvnh.parent_flex_value
AND NVL(glsvh1.status_code, 'X') <>'D'
AND glsvh2.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh2.parent_flex_value =
glsvnh.child_flex_value
AND NVL(glsvh2.status_code, 'X') <>'D'
AND NOT EXISTS
(SELECT 1
FROM GL_SEG_VAL_HIERARCHIES glsvh3
WHERE glsvh3.flex_value_set_id =
glsvnh.flex_value_set_id
AND glsvh3.parent_flex_value =
glsvh1.parent_flex_value
AND glsvh3.child_flex_value =
glsvh2.child_flex_value
AND glsvh3.summary_flag =
glsvh2.summary_flag
AND NVL(glsvh3.status_code,'X') <>'D'));
UPDATE GL_SEG_VAL_NORM_HIERARCHY
SET status_code = NULL
WHERE status_code = 'I'
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
UPDATE GL_SEG_VAL_HIERARCHIES
SET status_code = NULL
WHERE status_code IN ('I','U')
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
DELETE FROM GL_SEG_VAL_NORM_HIERARCHY
WHERE status_code = l_status
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
DELETE FROM GL_SEG_VAL_HIERARCHIES
WHERE status_code= l_status
AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;