[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE
FROM GL_LEDGER_SEGMENT_VALUES
WHERE STATUS_CODE = l_status_flag
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE CHART_OF_ACCOUNTS_ID = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
UPDATE GL_LEDGER_SEGMENT_VALUES
SET status_code = NULL
WHERE status_code = 'D'
AND ledger_id IN
(SELECT ledger_id
FROM GL_LEDGERS
WHERE chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
UPDATE GL_LEDGER_SEGMENT_VALUES GLLSV
SET (GLLSV.START_DATE, GLLSV.END_DATE) =
(SELECT GLLNSV.START_DATE, GLLNSV.END_DATE
FROM GL_LEDGER_NORM_SEG_VALS GLLNSV
WHERE GLLNSV.RECORD_ID = GLLSV.PARENT_RECORD_ID)
WHERE GLLSV.PARENT_RECORD_ID IN
(SELECT GLLNSV2.RECORD_ID
FROM GL_LEDGERS GLL,
GL_LEDGER_NORM_SEG_VALS GLLNSV2
WHERE GLL.CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND GLLNSV2.STATUS_CODE = 'U'
AND GLLNSV2.REQUEST_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND GLLNSV2.LEDGER_ID = GLL.LEDGER_ID);
||' GL_LEDGER_SEGMENT_VALUES for delete');
UPDATE GL_LEDGER_SEGMENT_VALUES
SET STATUS_CODE = 'D'
WHERE PARENT_RECORD_ID IN
(SELECT RECORD_ID
FROM GL_LEDGERS GLL,
GL_LEDGER_NORM_SEG_VALS GLLNSV
WHERE GLL.CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND GLLNSV.STATUS_CODE = 'D'
AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID);
V2 =>'Inserting new record(S) Into'
||' GL_LEDGER_SEGMENT_VALUES'
||' for every record with status code I '
||' and segment_value_type_code of S'
||' in the table'
||' GL_LEDGER_NORM_SEG_VALS ');
INSERT INTO GL_LEDGER_SEGMENT_VALUES
(LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
END_DATE)
(SELECT GLLNSV.LEDGER_ID, GLLNSV.SEGMENT_TYPE_CODE,
GLLNSV.SEGMENT_VALUE, 'I', GLLNSV.RECORD_ID,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
GLLNSV.START_DATE, GLLNSV.END_DATE
FROM GL_LEDGERS GLL,
GL_LEDGER_NORM_SEG_VALS GLLNSV
WHERE GLL.CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
AND GLLNSV.STATUS_CODE = 'I'
AND GLL.IMPLICIT_ACCESS_SET_ID IS NOT NULL
AND GLLNSV.REQUEST_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'S');
V2 =>'INSERT NEW RECORD(S) INTO'
||' GL_LEDGER_SEGMENT_VALUES'
||' FOR EVERY RECORD WITH STATUS CODE I '
||' AND SEGMENT_VALUE_TYPE_CODE OF C'
||' IN THE TABLE'
||' GL_LEDGER_NORM_SEG_VALS ');
INSERT INTO GL_LEDGER_SEGMENT_VALUES
(LEDGER_ID, SEGMENT_TYPE_CODE,SEGMENT_VALUE, STATUS_CODE,
PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
END_DATE)
(SELECT GLLNSV.LEDGER_ID, GLLNSV.SEGMENT_TYPE_CODE,
GLSVH.CHILD_FLEX_VALUE, 'I', GLLNSV.RECORD_ID,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
GLLNSV.START_DATE, GLLNSV.END_DATE
FROM GL_LEDGERS GLL,
GL_LEDGER_NORM_SEG_VALS GLLNSV,
GL_SEG_VAL_HIERARCHIES GLSVH
WHERE GLL.CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
AND GLLNSV.STATUS_CODE = 'I'
AND GLLNSV.REQUEST_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'C'
AND GLSVH.FLEX_VALUE_SET_ID =
DECODE(GLLNSV.SEGMENT_TYPE_CODE,
'B',GLL.BAL_SEG_VALUE_SET_ID,
'M',GLL.MGT_SEG_VALUE_SET_ID)
AND GLSVH.PARENT_FLEX_VALUE = GLLNSV.SEGMENT_VALUE
AND GLSVH.STATUS_CODE IS NULL);
V2 =>'Inserting ALC ledger record(S) into'
||' GL_LEDGER_SEGMENT_VALUES'
||' for every source ledger '
||' in the '
||' GL_LEDGER_NORM_SEG_VALS table');
INSERT INTO GL_LEDGER_SEGMENT_VALUES
(LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
END_DATE)
(SELECT glr.target_ledger_id,gllsv.segment_type_code,
gllsv.segment_value, 'I', gllsv.parent_record_id,
sysdate,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
sysdate,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
gllsv.start_date, gllsv.end_date
FROM GL_LEDGERS gll
,GL_LEDGER_RELATIONSHIPS glr
,GL_LEDGER_SEGMENT_VALUES gllsv
WHERE gll.chart_of_accounts_id =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND (gll.bal_seg_value_option_code = 'I' OR
gll.mgt_seg_value_option_code = 'I')
AND gll.alc_ledger_type_code = 'TARGET'
AND glr.target_ledger_id = gll.ledger_id
AND glr.target_ledger_category_code = 'ALC'
AND glr.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
AND glr.application_id = 101
AND gllsv.ledger_id = glr.source_ledger_id
AND gllsv.segment_type_code IN
(DECODE(gll.bal_seg_value_option_code,'I','B',''),
DECODE(gll.mgt_seg_value_option_code,'I','M',''))
AND NVL(GLLSV.STATUS_CODE,'X') <> 'D'
AND NOT EXISTS
(SELECT 1
FROM GL_LEDGER_SEGMENT_VALUES gllsv2
WHERE gllsv2.ledger_id = glr.target_ledger_id
AND gllsv2.segment_type_code = gllsv.SEGMENT_TYPE_CODE
AND gllsv2.segment_value = gllsv.segment_value
AND NVL(gllsv2.start_date,
TO_DATE('01/01/1950','MM/DD/YYYY'))
= NVL(gllsv.start_date,
TO_DATE('01/01/1950','MM/DD/YYYY'))
AND NVL(gllsv2.end_date,
TO_DATE('12/31/9999','MM/DD/YYYY'))
= NVL(gllsv.end_date,
TO_DATE('12/31/9999','MM/DD/YYYY'))));
SELECT 1 INTO L_Check_Id
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM GL_LEDGERS GLL
WHERE GLL.BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
OR GLL.MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND ROWNUM = 1);
DELETE
FROM GL_LEDGER_SEGMENT_VALUES
WHERE STATUS_CODE = 'I'
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
OR MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
UPDATE GL_LEDGER_SEGMENT_VALUES
SET STATUS_CODE = NULL
WHERE STATUS_CODE = 'D'
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
OR MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
||' FOR DELETE');
UPDATE GL_LEDGER_SEGMENT_VALUES GLLSV
SET GLLSV.STATUS_CODE = 'D'
WHERE (GLLSV.LEDGER_ID, GLLSV.PARENT_RECORD_ID,
GLLSV.SEGMENT_VALUE) IN
(SELECT GLLNSV.LEDGER_ID, GLLNSV.RECORD_ID,
GLSVH.CHILD_FLEX_VALUE
FROM GL_SEG_VAL_HIERARCHIES GLSVH,
GL_LEDGER_NORM_SEG_VALS GLLNSV,
GL_LEDGERS GLL
WHERE GLSVH.FLEX_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND GLSVH.STATUS_CODE = 'D'
AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'C'
AND GLLNSV.STATUS_CODE IS NULL
AND GLLNSV.SEGMENT_VALUE =
GLSVH.PARENT_FLEX_VALUE
AND GLL.LEDGER_ID = GLLNSV.LEDGER_ID
AND (
( GLL.BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND GLLNSV.SEGMENT_TYPE_CODE = 'B')
OR
( GLL.MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND GLLNSV.SEGMENT_TYPE_CODE = 'M')));
V2 =>'Insert new segment values'
||' from segment value hierarchy'
||' into GL_LEDGER_SEGMENT_VALUES');
INSERT INTO GL_LEDGER_SEGMENT_VALUES
(LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
END_DATE)
(SELECT GLLNSV.LEDGER_ID, GLLNSV.SEGMENT_TYPE_CODE,
GLSVH.CHILD_FLEX_VALUE, 'I', GLLNSV.RECORD_ID,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
SYSDATE,
GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
GLLNSV.START_DATE, GLLNSV.END_DATE
FROM GL_SEG_VAL_HIERARCHIES GLSVH,
GL_LEDGER_NORM_SEG_VALS GLLNSV,
GL_LEDGERS GLL
WHERE GLSVH.FLEX_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND GLSVH.STATUS_CODE = 'I'
AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'C'
AND GLLNSV.STATUS_CODE IS NULL
AND GLLNSV.SEGMENT_VALUE = GLSVH.PARENT_FLEX_VALUE
AND GLL.LEDGER_ID = GLLNSV.LEDGER_ID
AND (
(GLL.BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND GLLNSV.SEGMENT_TYPE_CODE = 'B')
OR
(GLL.MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
AND GLLNSV.SEGMENT_TYPE_CODE = 'M')));
UPDATE GL_LEDGER_NORM_SEG_VALS
SET STATUS_CODE = NULL, request_id = NULL
WHERE STATUS_CODE IN ( 'I','U')
AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
AND LEDGER_ID IN (SELECT LEDGER_ID FROM GL_LEDGERS
WHERE CHART_OF_ACCOUNTS_ID = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND IMPLICIT_ACCESS_SET_ID IS NOT NULL);
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID); */
UPDATE GL_LEDGER_SEGMENT_VALUES
SET STATUS_CODE = NULL
WHERE STATUS_CODE = 'I'
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
DELETE
FROM GL_LEDGER_NORM_SEG_VALS
WHERE STATUS_CODE = l_status
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
DELETE
FROM GL_LEDGER_SEGMENT_VALUES
WHERE STATUS_CODE = l_status
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
UPDATE GL_LEDGER_SEGMENT_VALUES
SET STATUS_CODE = NULL
WHERE STATUS_CODE = 'I'
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
OR MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
DELETE
FROM GL_LEDGER_SEGMENT_VALUES
WHERE STATUS_CODE = 'D'
AND LEDGER_ID IN
(SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
OR MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
SELECT DISTINCT GLLSV1.LEDGER_ID, GLLSV2.SEGMENT_VALUE
FROM GL_LEDGERS GLL,
GL_LEDGER_SEGMENT_VALUES GLLSV1,
GL_LEDGER_SEGMENT_VALUES GLLSV2
WHERE GLL.CHART_OF_ACCOUNTS_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
AND GLLSV1.LEDGER_ID = GLL.LEDGER_ID
AND GLLSV1.LEDGER_ID = GLLSV2.LEDGER_ID
AND GLLSV1.SEGMENT_TYPE_CODE = GLLSV2.SEGMENT_TYPE_CODE
AND NVL(GLLSV1.STATUS_CODE,'X') <>'D'
AND NVL(GLLSV2.STATUS_CODE,'X') <>'D'
AND GLLSV1.SEGMENT_VALUE = GLLSV2.SEGMENT_VALUE
AND GLLSV1.ROWID <>GLLSV2.ROWID
AND ( NVL(GLLSV1.START_DATE,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
BETWEEN NVL(GLLSV2.START_DATE,
TO_DATE('01/01/1950','MM/DD/YYYY'))
AND NVL(GLLSV2.END_DATE,
TO_DATE('12/31/9999','MM/DD/YYYY'))
OR NVL(GLLSV1.END_DATE,
TO_DATE('12/31/9999','MM/DD/YYYY'))
BETWEEN NVL(GLLSV2.START_DATE,
TO_DATE('01/01/1950','MM/DD/YYYY'))
AND NVL(GLLSV2.END_DATE,
TO_DATE('12/31/9999','MM/DD/YYYY')));
SELECT NAME INTO L_LEDGER_NAME
FROM GL_LEDGERS
WHERE LEDGER_ID = L_LEDGER_ID;
SELECT GLLSV1.LEDGER_ID, GLLSV2.SEGMENT_VALUE
FROM GL_LEDGERS GLL,
GL_LEDGER_SEGMENT_VALUES GLLSV1,
GL_LEDGER_SEGMENT_VALUES GLLSV2
WHERE ( GLL.BAL_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
OR GLL.MGT_SEG_VALUE_SET_ID =
GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
AND GLLSV1.LEDGER_ID = GLL.LEDGER_ID
AND GLLSV1.LEDGER_ID = GLLSV2.LEDGER_ID
AND GLLSV1.SEGMENT_TYPE_CODE = GLLSV2.SEGMENT_TYPE_CODE
AND NVL(GLLSV1.STATUS_CODE,'X') <>'D'
AND NVL(GLLSV2.STATUS_CODE,'X') <>'D'
AND GLLSV1.SEGMENT_VALUE = GLLSV2.SEGMENT_VALUE
AND GLLSV1.ROWID <>GLLSV2.ROWID
AND ( NVL(GLLSV1.START_DATE,
TO_DATE('01/01/1950', 'MM/DD/YYYY'))
BETWEEN NVL(GLLSV2.START_DATE,
TO_DATE('01/01/1950','MM/DD/YYYY'))
AND NVL(GLLSV2.END_DATE,
TO_DATE('12/31/9999','MM/DD/YYYY'))
OR NVL(GLLSV1.END_DATE,
TO_DATE('12/31/9999','MM/DD/YYYY'))
BETWEEN NVL(GLLSV2.START_DATE,
TO_DATE('01/01/1950','MM/DD/YYYY'))
AND NVL(GLLSV2.END_DATE,
TO_DATE('12/31/9999','MM/DD/YYYY')));
SELECT NAME INTO L_LEDGER_NAME
FROM GL_LEDGERS
WHERE LEDGER_ID = L_LEDGER_ID;