[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT order_flag
INTO seq_in_order
FROM dba_sequences
WHERE sequence_owner = schema
AND sequence_name = 'GL_JE_BATCHES_S';
SELECT decode(min(chart_of_accounts_id), null, 'N', 'Y'),
min(mgt_seg_column_name),
min(assign_complete_flag)
INTO rerun_flag, l_mgt_seg_column_name, l_assign_complete_flag
FROM GL_MGT_SEG_UPGRADE_H
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
SELECT GL_JE_BATCHES_S.nextval
INTO new_max_batch_id
FROM dual;
INSERT INTO GL_MGT_SEG_UPGRADE_H
(chart_of_accounts_id,
mgt_seg_column_name,
assign_complete_flag,
max_processed_batch_id,
max_batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(X_Chart_Of_Accounts_Id,
X_Mgt_Seg_Column_Name,
'N',
null,
new_max_batch_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id);
UPDATE GL_MGT_SEG_UPGRADE_H
SET max_batch_id = new_max_batch_id
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
SELECT min(assign_complete_flag),
min(mgt_seg_column_name),
nvl(min(max_processed_batch_id), 0),
min(max_batch_id)
INTO l_assign_complete_flag, l_mgt_seg_column_name,
l_max_proc_batch_id, l_max_batch_id
FROM GL_MGT_SEG_UPGRADE_H
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
INSERT INTO GL_MGT_SEG_UPGRADE_GT
(je_batch_id)
SELECT msu.je_batch_id
FROM GL_MGT_SEG_UPGRADE MSU,
GL_JE_BATCHES B
WHERE msu.chart_of_accounts_id = X_Chart_Of_Accounts_Id
AND b.je_batch_id = msu.je_batch_id
AND b.status || '' = 'P';
INSERT INTO GL_JE_SEGMENT_VALUES
(je_header_id, segment_type_code, segment_value, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT l.je_header_id, 'M',
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30),
sysdate, l_user_id, sysdate, l_user_id, l_login_id
FROM GL_MGT_SEG_UPGRADE_GT GT,
GL_JE_HEADERS H,
GL_JE_LINES L,
GL_CODE_COMBINATIONS CC
WHERE h.je_batch_id = gt.je_batch_id
AND l.je_header_id = h.je_header_id
AND cc.code_combination_id = l.code_combination_id
GROUP BY
l.je_header_id,
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30);
DELETE FROM GL_MGT_SEG_UPGRADE MSU
WHERE MSU.chart_of_accounts_id = X_Chart_Of_Accounts_Id
AND MSU.je_batch_id IN (SELECT gt.je_batch_id
FROM GL_MGT_SEG_UPGRADE_GT gt);
INSERT INTO GL_MGT_SEG_UPGRADE
(chart_of_accounts_id, je_batch_id, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT b.chart_of_accounts_id, b.je_batch_id,
sysdate, l_user_id, sysdate, l_user_id, l_login_id
FROM GL_JE_BATCHES B
WHERE b.je_batch_id BETWEEN low_batch_id AND high_batch_id
AND b.status <> 'P'
AND b.chart_of_accounts_id = X_Chart_Of_Accounts_Id;
INSERT INTO GL_JE_SEGMENT_VALUES
(je_header_id, segment_type_code, segment_value, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT l.je_header_id, 'M',
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30),
sysdate, l_user_id, sysdate, l_user_id, l_login_id
FROM GL_JE_BATCHES B,
GL_JE_HEADERS H,
GL_JE_LINES L,
GL_CODE_COMBINATIONS CC
WHERE b.je_batch_id BETWEEN low_batch_id AND high_batch_id
AND b.status || '' = 'P'
AND b.chart_of_accounts_id = X_Chart_Of_Accounts_Id
AND NOT EXISTS
(SELECT msu.je_batch_id
FROM GL_MGT_SEG_UPGRADE MSU
WHERE msu.chart_of_accounts_id = X_Chart_Of_Accounts_Id
AND msu.je_batch_id = b.je_batch_id)
AND h.je_batch_id = b.je_batch_id
AND l.je_header_id = h.je_header_id
AND cc.code_combination_id = l.code_combination_id
GROUP BY
l.je_header_id,
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30);
UPDATE GL_MGT_SEG_UPGRADE_H
SET max_processed_batch_id = high_batch_id
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
SELECT min(mgt_seg_column_name),
min(assign_complete_flag),
min(max_processed_batch_id)
INTO l_mgt_seg_column_name, l_assign_complete_flag, l_max_proc_batch_id
FROM GL_MGT_SEG_UPGRADE_H
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_id;
INSERT INTO GL_JE_SEGMENT_VALUES
(je_header_id, segment_type_code, segment_value, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT l.je_header_id, 'M',
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30),
sysdate, l_user_id, sysdate, l_user_id, l_login_id
FROM GL_MGT_SEG_UPGRADE MSU,
GL_JE_HEADERS H,
GL_JE_LINES L,
GL_CODE_COMBINATIONS CC
WHERE msu.chart_of_accounts_id = X_Chart_Of_Accounts_Id
AND h.je_batch_id = msu.je_batch_id
AND l.je_header_id = h.je_header_id
AND cc.code_combination_id = l.code_combination_id
GROUP BY
l.je_header_id,
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30);
INSERT INTO GL_JE_SEGMENT_VALUES
(je_header_id, segment_type_code, segment_value, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT l.je_header_id, 'M',
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30),
sysdate, l_user_id, sysdate, l_user_id, l_login_id
FROM GL_JE_BATCHES B,
GL_JE_HEADERS H,
GL_JE_LINES L,
GL_CODE_COMBINATIONS CC
WHERE b.je_batch_id > l_max_proc_batch_id
AND b.chart_of_accounts_id = X_Chart_Of_Accounts_Id
AND h.je_batch_id = b.je_batch_id
AND l.je_header_id = h.je_header_id
AND cc.code_combination_id = l.code_combination_id
GROUP BY
l.je_header_id,
decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
'SEGMENT2', cc.segment2,
'SEGMENT3', cc.segment3,
'SEGMENT4', cc.segment4,
'SEGMENT5', cc.segment5,
'SEGMENT6', cc.segment6,
'SEGMENT7', cc.segment7,
'SEGMENT8', cc.segment8,
'SEGMENT9', cc.segment9,
'SEGMENT10', cc.segment10,
'SEGMENT11', cc.segment11,
'SEGMENT12', cc.segment12,
'SEGMENT13', cc.segment13,
'SEGMENT14', cc.segment14,
'SEGMENT15', cc.segment15,
'SEGMENT16', cc.segment16,
'SEGMENT17', cc.segment17,
'SEGMENT18', cc.segment18,
'SEGMENT19', cc.segment19,
'SEGMENT20', cc.segment20,
'SEGMENT21', cc.segment21,
'SEGMENT22', cc.segment22,
'SEGMENT23', cc.segment23,
'SEGMENT24', cc.segment24,
'SEGMENT25', cc.segment25,
'SEGMENT26', cc.segment26,
'SEGMENT27', cc.segment27,
'SEGMENT28', cc.segment28,
'SEGMENT29', cc.segment29,
'SEGMENT30', cc.segment30);
SELECT st.id_flex_structure_code, sg.segment_name
INTO struct_code, seg_name
FROM FND_ID_FLEX_STRUCTURES ST,
FND_ID_FLEX_SEGMENTS SG
WHERE st.application_id = 101
AND st.id_flex_code = 'GL#'
AND st.id_flex_num = X_Chart_Of_Accounts_Id
AND sg.application_id = 101
AND sg.id_flex_code = 'GL#'
AND sg.id_flex_num = X_Chart_Of_Accounts_Id
AND sg.application_column_name = l_mgt_seg_column_name;
UPDATE GL_LEDGERS
SET mgt_seg_column_name = l_mgt_seg_column_name,
mgt_seg_value_set_id = segment.value_set_id
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
UPDATE GL_MGT_SEG_UPGRADE_H
SET assign_complete_flag = 'Y',
max_processed_batch_id = null,
max_batch_id = null
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
DELETE FROM GL_MGT_SEG_UPGRADE
WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;