[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_tot_rows_inserted IN NUMBER
, p_tot_rows_valid IN NUMBER
, p_tot_rows_posted IN NUMBER);
v_num_rows_inserted NUMBER;
v_num_rows_deleted NUMBER;
v_tot_rows_inserted NUMBER;
SELECT TO_CHAR(REQUEST_ID) REQUEST_ID,
PERIOD_NAME,
TO_CHAR(CAL_PERIOD_ID) CAL_PERIOD_ID,
LOAD_METHOD_CODE
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE REQUEST_ID IS NOT NULL
AND NUM_OF_ROWS_POSTED > 0;
v_num_rows_inserted := 0;
v_tot_rows_inserted := 0;
x_num_rows_inserted => v_num_rows_inserted,
p_bsv_range_low => v_bsv_range_low,
p_bsv_range_high => v_bsv_range_high,
p_maintain_qtd => FEM_GL_POST_PROCESS_PKG.pv_maintain_qtd_flag);
p_value1 => 'v_num_rows_inserted',
p_token2 => 'VAR_VAL',
p_value2 => v_num_rows_inserted);
v_tot_rows_inserted := v_num_rows_inserted;
p_value1 => 'v_tot_rows_inserted',
p_token2 => 'VAR_VAL',
p_value2 => v_tot_rows_inserted);
x_num_rows_inserted => v_num_rows_inserted,
p_effective_date => v_effective_date,
p_bsv_range_low => v_bsv_range_low,
p_bsv_range_high => v_bsv_range_high);
p_value1 => 'v_num_rows_inserted',
p_token2 => 'VAR_VAL',
p_value2 => v_num_rows_inserted);
v_tot_rows_inserted := v_tot_rows_inserted + v_num_rows_inserted;
p_value1 => 'v_tot_rows_inserted',
p_token2 => 'VAR_VAL',
p_value2 => v_tot_rows_inserted);
DELETE FROM fem_bal_post_interim_gt
WHERE nvl(xtd_balance_e,0) = 0
AND nvl(xtd_balance_f,0) = 0
AND nvl(ytd_balance_e,0) = 0
AND nvl(ytd_balance_f,0) = 0
AND nvl(qtd_balance_e,0) = 0
AND nvl(qtd_balance_f,0) = 0
AND nvl(ptd_debit_balance_e,0) = 0
AND nvl(ptd_credit_balance_e,0) = 0
AND nvl(ytd_debit_balance_e,0) = 0
AND nvl(ytd_credit_balance_e,0) = 0;
v_num_rows_deleted := SQL%ROWCOUNT;
p_msg_text => 'Removed ' || TO_CHAR(v_num_rows_deleted) ||
' zero-balance rows from FEM_BAL_POST_INTERIM_GT');
v_tot_rows_inserted := v_tot_rows_inserted - v_num_rows_deleted;
IF (v_tot_rows_inserted = 0)
THEN
-- There are no rows inserted from OGL into the posting interim table,
-- so set the number of rows selected and balances selected for each
-- valid execution parameter to 0
UPDATE FEM_INTG_EXEC_PARAMS_GT
SET NUM_OF_ROWS_SELECTED = 0
, SELECTED_PTD_DR_BAL = 0
, SELECTED_PTD_CR_BAL = 0
WHERE ERROR_CODE IS NULL
AND REQUEST_ID IS NOT NULL;
-- At least one row is inserted from OGL into the posting interim table,
-- so find the number of rows selected and balances selected for each
-- valid execution parameter
UPDATE FEM_INTG_EXEC_PARAMS_GT param
SET ( NUM_OF_ROWS_SELECTED
, SELECTED_PTD_DR_BAL
, SELECTED_PTD_CR_BAL) =
(SELECT COUNT(*)
, SUM(NVL(bpi.PTD_DEBIT_BALANCE_E, 0))
, SUM(NVL(bpi.PTD_CREDIT_BALANCE_E, 0))
FROM FEM_BAL_POST_INTERIM_GT bpi
WHERE bpi.DATASET_CODE = param.OUTPUT_DATASET_CODE
AND bpi.CAL_PERIOD_ID = param.CAL_PERIOD_ID)
WHERE param.ERROR_CODE IS NULL
AND param.REQUEST_ID IS NOT NULL;
SELECT COUNT(*)
INTO v_tot_rows_valid
FROM FEM_BAL_POST_INTERIM_GT
WHERE POSTING_ERROR_FLAG = 'N';
ELSIF (v_tot_rows_inserted > v_tot_rows_valid)
THEN
-- There is at least one CCIDs not properly mapped
-- Log the error messags
FEM_ENGINES_PKG.Tech_Message
(p_severity => pc_log_level_error,
p_module => v_module,
p_app_name => 'FEM',
p_msg_name => 'FEM_INTG_BAL_SNAP_CCID_ERR',
p_token1 => 'COA_NAME',
p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
-- Set up a save point before inserting/updating tables other than the
-- global temporary tables such that we can roll back to here as needed
SAVEPOINT OGLEngSavePt;
IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type = 'INSERT')
THEN
FEM_ENGINES_PKG.Tech_Message
(p_severity => pc_log_level_statement,
p_module => v_module,
p_app_name => 'FEM',
p_msg_name => 'Posting in Snapshot mode');
END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type = 'INSERT')
p_tot_rows_inserted => v_tot_rows_inserted,
p_tot_rows_valid => v_tot_rows_valid,
p_tot_rows_posted => v_tot_rows_posted);
v_param_list.DELETE;
p_tot_rows_inserted => v_tot_rows_inserted,
p_tot_rows_valid => v_tot_rows_valid,
p_tot_rows_posted => 0);
p_tot_rows_inserted => v_tot_rows_inserted,
p_tot_rows_valid => v_tot_rows_valid,
p_tot_rows_posted => v_tot_rows_posted);
IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type = 'INSERT') THEN
-- Bug fix 4330346: Changed to raise warning even when it is a pure
-- snapshot load i.e. pv_stmt_type = 'INSERT'
-- Perform post-process logging with a warning message
FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
(p_exec_status => 'SUCCESS',
p_final_message_name => 'FEM_GL_POST_206');
, p_tot_rows_inserted IN NUMBER
, p_tot_rows_valid IN NUMBER
, p_tot_rows_posted IN NUMBER) IS
v_module VARCHAR2(100);
SELECT DISTINCT
RPAD(PERIOD_NAME, 17, ' ')
|| DECODE(ERROR_CODE
, 'INVALID_PERIOD_STATUS', p_errText1
|| DECODE(CAL_PERIOD_ID
, -1, ', ' || p_errText2
, '')
, 'PERIOD_NOT_MAPPED' , p_errText2
, 'OTHER_DS_LOADED' , p_errText3)
, EFFECTIVE_PERIOD_NUM
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IN
('INVALID_PERIOD_STATUS', 'PERIOD_NOT_MAPPED', 'OTHER_DS_LOADED')
ORDER BY EFFECTIVE_PERIOD_NUM;
SELECT ds.DATASET_CODE,
p_ds_dim_name || ' ' || ds.DATASET_NAME
FROM FEM_DATASETS_TL ds
WHERE ds.DATASET_CODE IN (SELECT DISTINCT OUTPUT_DATASET_CODE
FROM FEM_INTG_EXEC_PARAMS_GT)
AND ds.LANGUAGE = USERENV('LANG');
SELECT ' ' || RPAD(gt.PERIOD_NAME, 17, ' ')
|| DECODE(gt.ERROR_CODE, NULL, p_succText
, 'PERIOD_GAP_EXISTS', p_errText1
, 'EXEC_LOCK_EXISTS' , p_errText2)
, DECODE(gt.ERROR_CODE, NULL, 'Y', 'N')
FROM FEM_INTG_EXEC_PARAMS_GT gt
WHERE (gt.REQUEST_ID IS NOT NULL
OR gt.ERROR_CODE IN ('PERIOD_GAP_EXISTS', 'EXEC_LOCK_EXISTS'))
AND gt.OUTPUT_DATASET_CODE = p_ds_code
ORDER BY gt.EFFECTIVE_PERIOD_NUM;
SELECT ' ' || RPAD(gt.PERIOD_NAME, 17, ' ')
|| LPAD(TO_CHAR(NVL(gt.SELECTED_PTD_DR_BAL, '')), 16) || ' '
|| LPAD(TO_CHAR(NVL(gt.SELECTED_PTD_CR_BAL, '')), 16) || ' '
|| LPAD(TO_CHAR(NVL(gt.POSTED_PTD_DR_BAL, '')), 16) || ' '
|| LPAD(TO_CHAR(NVL(gt.POSTED_PTD_CR_BAL, '')), 16)
FROM FEM_INTG_EXEC_PARAMS_GT gt
WHERE gt.REQUEST_ID IS NOT NULL
AND gt.ERROR_CODE IS NULL
AND gt.OUTPUT_DATASET_CODE = p_ds_code
ORDER BY gt.EFFECTIVE_PERIOD_NUM;
SELECT SUBSTR(NVL(FND_FLEX_EXT.Get_Segs
('SQLGL', 'GL#', FEM_GL_POST_PROCESS_PKG.pv_coa_id,
errAcct.CODE_COMBINATION_ID),
errAcct.CODE_COMBINATION_ID), 1, 100)
FROM (SELECT DISTINCT CODE_COMBINATION_ID
FROM FEM_BAL_POST_INTERIM_GT gt
WHERE gt.POSTING_ERROR_FLAG = 'Y') errAcct;
SELECT ' ' || RPAD(gt.PERIOD_NAME, 17, ' ')
FROM FEM_INTG_EXEC_PARAMS_GT gt
WHERE gt.REQUEST_ID IS NOT NULL
AND gt.ERROR_CODE IS NULL
AND gt.OUTPUT_DATASET_CODE = p_ds_code
AND gt.NUM_OF_ROWS_SELECTED = 0
AND gt.LOAD_METHOD_CODE = 'S'
ORDER BY gt.EFFECTIVE_PERIOD_NUM;
p_value1 => 'p_tot_rows_inserted',
p_token2 => 'VAR_VAL',
p_value2 => TO_CHAR(p_tot_rows_inserted));
UPDATE FEM_INTG_EXEC_PARAMS_GT param
SET ( NUM_OF_ROWS_POSTED
, POSTED_PTD_DR_BAL
, POSTED_PTD_CR_BAL) =
(SELECT COUNT(*)
, SUM(NVL(bpi.PTD_DEBIT_BALANCE_E, 0))
, SUM(NVL(bpi.PTD_CREDIT_BALANCE_E, 0))
FROM FEM_BAL_POST_INTERIM_GT bpi
WHERE bpi.DATASET_CODE = param.OUTPUT_DATASET_CODE
AND bpi.CAL_PERIOD_ID = param.CAL_PERIOD_ID
AND bpi.POSTING_ERROR_FLAG = 'N'
AND NOT EXISTS
(SELECT 'Invalid Delta Load'
FROM FEM_INTG_DELTA_LOADS dl
WHERE dl.LEDGER_ID = bpi.LEDGER_ID
AND dl.DATASET_CODE = bpi.DATASET_CODE
AND dl.CAL_PERIOD_ID = bpi.CAL_PERIOD_ID
AND dl.DELTA_RUN_ID = bpi.DELTA_RUN_ID
AND dl.LOADED_FLAG = 'N'))
WHERE param.ERROR_CODE IS NULL
AND param.REQUEST_ID IS NOT NULL;
IF (p_tot_rows_inserted = 0)
THEN
-- No data are selected for all datasets/periods, so it is no need to
-- perform further checks and print message FEM_INTG_BAL_NO_DATA
Write_New_Line;
-- Some data are selected, so we need to perform further checks
-- ------------------------------------------------------------------
-- 7.2 Check if there are any no-data-found datasets/snapshot periods
-- ------------------------------------------------------------------
BEGIN
SELECT 'No-data-found dataset/snapshot period exists'
INTO v_line_text
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL
AND REQUEST_ID IS NOT NULL
AND NUM_OF_ROWS_SELECTED = 0
AND LOAD_METHOD_CODE = 'S');
IF (p_tot_rows_inserted > p_tot_rows_valid)
THEN
-- At least one unmapped account exists
-- Print 2 blanks line
Write_New_Line;
END IF; -- IF (p_tot_rows_inserted > p_tot_rows_valid)
END IF; -- IF (p_tot_rows_inserted = 0)
IF (p_tot_rows_inserted > 0)
THEN
-- Print the No-Data-Found Snapshot Periods list prompt
Write_New_Line;
END IF; -- IF (p_tot_rows_inserted > 0)