[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_interface_rows;
PROCEDURE insert_tracking_data(p_status_code VARCHAR2);
PROCEDURE update_transfer_tables;
SELECT nvl(sum(1),0)
INTO l_dummy
FROM gl_xfr_tracking
WHERE xfer_request_id = g_request_id
AND system_id = g_system_id
AND status_code NOT IN ('SUCCESS', 'FIRST_PERIOD_CHANGED');
UPDATE gl_xfr_interface
SET status = 'PROCESSED'
WHERE system_id = g_system_id
AND group_id = g_group_id;
gl_fusion_transfer_pkg.insert_interface_rows;
gl_fusion_transfer_pkg.insert_tracking_data('NO_DATA');
gl_fusion_transfer_pkg.insert_tracking_data('FIRST_PERIOD_CHANGED');
gl_fusion_transfer_pkg.insert_tracking_data('SUCCESS');
gl_fusion_transfer_pkg.update_transfer_tables;
DELETE gl_xfr_interface
WHERE status = 'PROCESSED'
AND system_id = g_system_id;
SELECT nvl(max_delta_run_id + 1, 0)
INTO g_low_delta_run_id
FROM gl_xfr_ledger_mappings
WHERE ledger_mapping_id = g_lgr_mapping_id;
SELECT nvl(max(delta_run_id), 0)
into g_high_delta_run_id
FROM GL_BALANCES_DELTA;
PROCEDURE insert_interface_rows IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_interface_rows';
UPDATE gl_xfr_led_map_periods lmper
SET temp_date = (SELECT least(greatest(trunc(sysdate),ps.start_date),ps.end_date)
FROM gl_period_statuses ps
WHERE ps.application_id = 101
AND ps.ledger_id = g_from_ledger_id
AND ps.period_name = lmper.from_period_name)
WHERE ledger_mapping_id = g_lgr_mapping_id;
UPDATE gl_xfr_led_map_periods lmper
SET temp_date = (SELECT least(greatest(trunc(sysdate),per.start_date),per.end_date)
FROM gl_xfr_period_mappings map,
gl_xfr_ledgers lgr,
gl_xfr_periods per
WHERE map.calendar_mapping_id = g_cal_mapping_id
AND map.from_period_name = lmper.from_period_name
AND lgr.ledger_id = g_to_ledger_id
AND per.period_set_name = lgr.period_set_name
AND per.period_name = map.to_period_name)
WHERE ledger_mapping_id = g_lgr_mapping_id;
SELECT to_number(to_char(least(greatest(trunc(sysdate),ps.start_date),ps.end_date),'J'))
INTO l_eff_date
FROM gl_period_statuses ps
WHERE ps.application_id = 101
AND ps.ledger_id = g_from_ledger_id
AND ps.period_name = g_from_period;
SELECT to_number(to_char(least(greatest(trunc(sysdate),per.start_date),per.end_date),'J'))
INTO l_eff_date
FROM gl_xfr_period_mappings map,
gl_xfr_ledgers lgr,
gl_xfr_periods per
WHERE map.calendar_mapping_id = g_cal_mapping_id
AND map.from_period_name = g_from_period
AND lgr.ledger_id = g_to_ledger_id
AND per.period_set_name = lgr.period_set_name
AND per.period_name = map.to_period_name;
SELECT gl_interface_control_s.NEXTVAL
INTO g_group_id
FROM DUAL;
SELECT period_name
INTO g_original_first_period
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = g_from_ledger_id
AND effective_period_num = g_first_eff_per_num;
'INSERT INTO ' || g_interface_table_name || '
(status, ledger_id, set_of_books_id,
user_je_source_name, user_je_category_name,
accounting_date, currency_code,
date_created, actual_flag, je_header_id,
created_by, period_name, reference1, reference4,
reference10,
entered_dr, entered_cr, accounted_dr, accounted_cr,
code_combination_id, request_id, group_id,
row_number, system_id,
reference21, reference22, reference23, reference24 ';
SELECT ';
FROM (SELECT
bal.currency_code currency_code,
bal.ledger_id from_ledger_id,
bal.code_combination_id from_code_combination_id,
bal.period_name from_period_name, ';
print_logfile('SQL Statement to insert interface rows: ');
END insert_interface_rows;
'INSERT INTO gl_accts_map_int_gt
(from_ccid, coa_mapping_id)
SELECT DISTINCT int.code_combination_id, :coa_map
FROM ' || g_interface_table_name || ' int
WHERE int.request_id = :request_id
AND int.code_combination_id IS NOT NULL
AND int.status = ''MAP'' ';
print_logfile('SQL Statement to insert into gl_accounts_map_int_gt: ');
gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_INVALID_RULES');
gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_OUT_OF_DATE');
gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_UNEXPECTED_ERROR');
SELECT 1
INTO l_dummy
FROM dual
WHERE EXISTS
(SELECT 'has errors'
FROM gl_accts_map_int_gt
WHERE error_code IS NOT NULL);
gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_UNMAPPED');
'INSERT into gl_xfr_ccid_mappings
(ledger_mapping_id, from_ccid, to_ccid, period_name,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
SELECT DISTINCT :g_lgr_mapping_id, map.from_ccid, map.to_ccid, int.reference23,
sysdate, :user_id, sysdate, :user_id, :login_id
FROM gl_accts_map_int_gt map,
'|| g_interface_table_name || ' int
WHERE int.request_id = :request_id
AND int.code_combination_id = map.from_ccid
AND int.status = ''MAP''
AND map.coa_mapping_id = :coa_map_id ';
print_logfile('SQL Statement to insert into gl_xfr_ccid_mappings');
DELETE gl_xfr_ccid_mappings
WHERE ledger_mapping_id = g_lgr_mapping_id
AND period_name = g_from_period;
INSERT into gl_xfr_ccid_mappings
(ledger_mapping_id, from_ccid, to_ccid, period_name,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
SELECT g_lgr_mapping_id, from_ccid, to_ccid, g_from_period,
sysdate, g_user_id, sysdate, g_user_id, g_login_id
FROM gl_accts_map_int_gt map;
'UPDATE '|| g_interface_table_name || ' int
SET (code_combination_id ';
) = (SELECT NULL ';
print_logfile('SQL Statement to update '|| g_interface_table_name);
PROCEDURE insert_tracking_data (p_status_code VARCHAR2) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_tracking_data';
INSERT INTO gl_xfr_tracking
(xfer_request_id, je_source_key, group_id,
system_id, interface_table_name, ledger_id, status_code,
original_first_period,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
VALUES (g_request_id, nvl(g_je_source_key, ' '), nvl(g_group_id, -1),
g_system_id, g_interface_table_name,
g_to_ledger_id, p_status_code,
g_original_first_period,
sysdate, g_user_id, sysdate, g_user_id, g_login_id);
END insert_tracking_data;
PROCEDURE update_transfer_tables IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_transfer_tables';
UPDATE gl_xfr_ledger_mappings
SET max_delta_run_id = g_high_delta_run_id,
last_update_date = sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
WHERE ledger_mapping_id = g_lgr_mapping_id;
UPDATE gl_xfr_ledger_mappings
SET first_eff_per_num = g_from_eff_per_num,
last_update_date = sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
WHERE ledger_mapping_id = g_lgr_mapping_id;
INSERT INTO gl_xfr_led_map_periods
(ledger_mapping_id, from_period_name,
from_eff_per_num, full_transfer_done_flag,
max_delta_run_id,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT g_lgr_mapping_id, g_from_period,
g_from_eff_per_num, 'Y',
gl_balances_delta_s.nextval,
sysdate, g_user_id,
sysdate, g_user_id,
g_login_id
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM gl_xfr_led_map_periods
WHERE ledger_mapping_id = g_lgr_mapping_id
AND from_period_name = g_from_period);
UPDATE gl_xfr_led_map_periods
SET full_transfer_done_flag = 'Y',
max_delta_run_id = gl_balances_delta_s.nextval,
last_update_date = sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
WHERE ledger_mapping_id = g_lgr_mapping_id
AND from_period_name = g_from_period;
INSERT INTO gl_track_delta_balances
(ledger_id, program_code, period_name, actual_flag,
extract_level_code, currency_type_code,
enabled_flag, last_update_date, last_updated_by,
creation_date, created_by, last_update_login)
SELECT
g_from_ledger_id, 'FUSIONTRANSFER', g_from_period, 'A',
'DTL', 'B',
'Y', sysdate, g_user_id,
sysdate, g_user_id, g_login_id
FROM dual
WHERE NOT EXISTS (
SELECT 'row exists'
FROM gl_track_delta_balances t2
WHERE t2.ledger_id = g_from_ledger_id
AND t2.program_code = 'FUSIONTRANSFER'
AND t2.period_name = g_from_period
AND t2.actual_flag = 'A'
AND t2.extract_level_code = 'DTL');
END update_transfer_tables;
SELECT lgr.to_ledger_id, lgr.ledger_mapping_id,
map.name, map.coa_mapping_id,
nvl(lgr.first_eff_per_num, 99999999),
map.to_coa_id, lgr.calendar_mapping_id,
tlgr.name
INTO g_to_ledger_id, g_lgr_mapping_id,
g_coa_mapping_name, g_coa_mapping_id,
g_first_eff_per_num, g_to_coa_id,
g_cal_mapping_id, g_to_ledger_name
FROM gl_xfr_ledger_mappings lgr,
gl_coa_mappings map,
gl_xfr_ledgers tlgr
WHERE lgr.from_ledger_id = g_from_ledger_id
AND lgr.from_ledger_currency = g_funct_curr
AND map.coa_mapping_id(+) = lgr.coa_mapping_id
AND tlgr.ledger_id = lgr.to_ledger_id;
SELECT effective_period_num
INTO g_from_eff_per_num
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = g_from_ledger_id
AND period_name = g_from_period;
SELECT to_period_name
INTO g_to_period
FROM gl_xfr_period_mappings
WHERE calendar_mapping_id = g_cal_mapping_id
AND from_period_name = g_from_period;
SELECT period_name
INTO g_to_period
FROM gl_xfr_ledgers lgr, gl_xfr_periods per
WHERE lgr.ledger_id = g_to_ledger_id
AND per.period_set_name = lgr.period_set_name
AND per.period_type = lgr.accounted_period_type
AND per.period_name = g_from_period;
gl_fusion_transfer_pkg.insert_tracking_data('BAD_PERIOD');
SELECT rel.target_ledger_name, lgr.chart_of_accounts_id, rel.target_currency_code,
decode(rel.target_ledger_category_code,
'ALC', decode(rel.relationship_type_code, 'BALANCE', 'Y', 'N'), 'N')
INTO p_ledger_name, p_coa_id, p_curr_code, l_dummy
FROM gl_ledger_relationships rel, gl_ledgers lgr
WHERE rel.target_ledger_short_name = p_ledger_shortname
AND rel.application_id = 101
AND rel.source_ledger_id = rel.target_ledger_id
AND lgr.ledger_id = rel.target_ledger_id;
SELECT nvl(efb_upgrade_flag, 'N')
INTO l_dummy
FROM gl_system_usages;
SELECT seg.segment_num, seg.application_column_name
BULK COLLECT INTO l_segnums, l_applcols
FROM fnd_id_flex_segments seg
WHERE seg.application_id = 101
AND seg.id_flex_code = 'GL#'
AND seg.id_flex_num = p_coa_id
ORDER BY segment_num;
SELECT je_source_key, system_map_id
INTO g_je_source_key, g_system_map_id
FROM gl_xfr_source_systems
WHERE system_id = g_system_id;
gl_fusion_transfer_pkg.insert_tracking_data('SYSTEM_MISSING');
SELECT bsv_assignment_type
INTO g_bsv_assignment_type
FROM gl_xfr_system_ledgers
WHERE system_map_id = g_system_map_id
AND ledger_id = g_to_ledger_id;
gl_fusion_transfer_pkg.insert_tracking_data('LEDGER_REJECTED');
SELECT user_profile_option_name
INTO l_profile_name
FROM fnd_profile_options_tl
WHERE profile_option_name = 'GL_AU_FUSION_USER'
AND language = userenv('LANG');
SELECT access_privilege_code
INTO l_access_level
FROM gl_access_set_ledgers
WHERE access_set_id = g_access_set_id
AND ledger_id = g_from_ledger_id;
SELECT bal_seg_column_name
INTO l_bsv_column
FROM gl_xfr_ledgers
WHERE ledger_id = g_to_ledger_id;
'INSERT INTO gl_xfr_rejected_bsvs_gt
(balancing_segment_value)
SELECT bsvs.bal_seg_val
FROM
(SELECT int.' || l_bsv_column || ' bal_seg_val
FROM ' || g_interface_table_name || ' int
WHERE int.system_id = :g_system_id
AND int.group_id = :g_group_id
GROUP BY int.'|| l_bsv_column || ') bsvs,
gl_xfr_ledger_bsv_maps map
WHERE map.system_map_id(+) = :g_system_map_id
AND map.ledger_id(+) = :g_to_ledger_id
AND map.balancing_segment_value(+) = bsvs.bal_seg_val
AND map.rowid IS NULL';
print_logfile('SQL Statement to update '|| g_interface_table_name);
gl_fusion_transfer_pkg.insert_tracking_data('BSVS_REJECTED');