[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT ledger.chart_of_accounts_id -- glcd.object_id,
FROM gl_ledger_config_details glcd, gl_ledgers ledger
WHERE glcd.configuration_id = x_config_id
AND glcd.setup_step_code = 'NONE'
AND ledger.ledger_id = glcd.object_id;
SELECT ledger_id,ledger_category_code
FROM gl_ledgers
WHERE configuration_id = x_config_id;
SELECT haou.organization_id
, haou.date_from
,haou.name
,haou.type
,haou.internal_external_flag
,haou.location_id
,hou.set_of_books_id
,null usable_flag
,hou.short_code
,hou.default_legal_context_id
,haou.object_version_number
FROM hr_operating_units hou,
hr_all_organization_units haou
WHERE set_of_books_id = p_ledger_id
AND haou.organization_id = hou.organization_id;
x_update_prim_ledger_warning BOOLEAN;
SELECT ledger_id, NAME, chart_of_accounts_id, description,
period_set_name, accounted_period_type,
implicit_access_set_id
INTO x_ledger_id, x_name, x_chart_of_accounts_id, x_description,
x_period_set_name, x_accounted_period_type,
x_access_set_id
FROM gl_ledgers
WHERE ledger_id = v_ledgers.ledger_id;
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_creation_date => SYSDATE,
x_description => x_description);
UPDATE gl_ledgers
SET implicit_access_set_id = x_access_set_id
WHERE ledger_id = x_ledger_id;
* Update Operating unit's descriptive flexfiled org_information6 so
* that operating unit can be selected for entering transaction against it
*/
for v_orgs in c_org_curr (pri_ledger_id)
loop
hr_organization_api.update_operating_unit
(
p_organization_id => v_orgs.organization_id
,p_effective_date => v_orgs.date_from
,p_usable_flag => v_orgs.usable_flag
,p_object_version_number => v_orgs.object_version_number
,p_update_prim_ledger_warning => x_update_prim_ledger_warning
,p_duplicate_org_warning => x_duplicate_org_warning
);
PROCEDURE insert_row(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validate_only IN VARCHAR2 := fnd_api.g_true,
p_record_version_number IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_rowid IN OUT NOCOPY VARCHAR2,
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_currency_code VARCHAR2,
x_period_set_name VARCHAR2,
x_accounted_period_type VARCHAR2,
x_first_ledger_period_name VARCHAR2,
x_ret_earn_code_combination_id NUMBER,
x_suspense_allowed_flag VARCHAR2,
x_suspense_ccid NUMBER,
x_allow_intercompany_post_flag VARCHAR2,
x_enable_avgbal_flag VARCHAR2,
x_enable_budgetary_control_f VARCHAR2,
x_require_budget_journals_flag VARCHAR2,
x_enable_je_approval_flag VARCHAR2,
x_enable_automatic_tax_flag VARCHAR2,
x_consolidation_ledger_flag VARCHAR2,
x_translate_eod_flag VARCHAR2,
x_translate_qatd_flag VARCHAR2,
x_translate_yatd_flag VARCHAR2,
x_automatically_created_flag VARCHAR2,
x_track_rounding_imbalance_f VARCHAR2,
--x_mrc_ledger_type_code VARCHAR2,
x_le_ledger_type_code VARCHAR2,
x_bal_seg_value_option_code VARCHAR2,
x_mgt_seg_value_option_code VARCHAR2,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_description VARCHAR2,
x_future_enterable_periods_lmt NUMBER,
x_latest_opened_period_name VARCHAR2,
x_latest_encumbrance_year NUMBER,
x_cum_trans_ccid NUMBER,
x_res_encumb_ccid NUMBER,
x_net_income_ccid NUMBER,
x_balancing_segment VARCHAR2,
x_rounding_ccid NUMBER,
x_transaction_calendar_id NUMBER,
x_daily_translation_rate_type VARCHAR2,
x_period_average_rate_type VARCHAR2,
x_period_end_rate_type VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_set_manual_flag VARCHAR2,
--x_child_ledger_access_code VARCHAR2,
x_ledger_category_code VARCHAR2,
x_configuration_id NUMBER,
x_sla_accounting_method_code VARCHAR2,
x_sla_accounting_method_type VARCHAR2,
x_sla_description_language VARCHAR2,
x_sla_entered_cur_bal_sus_ccid NUMBER,
x_sla_bal_by_ledger_curr_flag VARCHAR2,
x_sla_ledger_cur_bal_sus_ccid NUMBER,
x_alc_ledger_type_code VARCHAR2,
x_criteria_set_id NUMBER,
x_enable_secondary_track_flag VARCHAR2 DEFAULT 'N',
x_enable_reval_ss_track_flag VARCHAR2 DEFAULT 'N',
x_enable_reconciliation_flag VARCHAR2 DEFAULT 'N',
x_sla_ledger_cash_basis_flag VARCHAR2 DEFAULT 'N',
x_create_je_flag VARCHAR2 DEFAULT 'Y',
x_commitment_budget_flag VARCHAR2 DEFAULT NULL,
x_net_closing_bal_flag VARCHAR2 DEFAULT 'N',
x_auto_jrnl_rev_flag VARCHAR2 DEFAULT 'N') IS
CURSOR c IS
SELECT ROWID
FROM gl_ledgers
WHERE NAME = x_name;
l_api_name CONSTANT VARCHAR(30) := 'insert_row';
select period_name
from gl_periods
where period_set_name = x_period_set_name
and period_type = x_accounted_period_type
order by period_year, period_num;
select completion_status_code
from gl_ledger_configurations
where configuration_id = x_configuration_id;
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login,
x_creation_date => x_creation_date,
x_description => x_description); */
'SELECT ' || x_bal_seg_column_name
|| ' FROM gl_code_combinations WHERE chart_of_accounts_id = :1 '
|| ' AND code_combination_id = :2 ';
SELECT conversion_type
INTO t_period_average_rate_type
FROM gl_daily_conversion_types_v
WHERE conversion_type <> 'User'
AND conversion_type <> 'EMU FIXED'
AND conversion_type = x_period_average_rate_type;
SELECT conversion_type
INTO t_period_end_rate_type
FROM gl_daily_conversion_types_v
WHERE conversion_type <> 'User'
AND conversion_type <> 'EMU FIXED'
AND conversion_type = x_period_end_rate_type;
SELECT language_code
INTO t_sla_description_language
FROM fnd_languages_vl
WHERE (installed_flag = 'I' OR installed_flag = 'B')
AND language_code = x_sla_description_language;
SELECT criteria_set_id
INTO t_criteria_set_id
FROM gl_autorev_criteria_sets
WHERE criteria_set_id = x_criteria_set_id;
INSERT INTO gl_ledgers
(ledger_id, NAME, short_name, chart_of_accounts_id,
currency_code, period_set_name,
accounted_period_type, first_ledger_period_name,
ret_earn_code_combination_id, suspense_allowed_flag,
allow_intercompany_post_flag,
enable_average_balances_flag,
enable_budgetary_control_flag,
require_budget_journals_flag, enable_je_approval_flag,
enable_automatic_tax_flag, consolidation_ledger_flag,
translate_eod_flag, translate_qatd_flag,
translate_yatd_flag, automatically_created_flag,
track_rounding_imbalance_flag,-- mrc_ledger_type_code,
object_type_code, le_ledger_type_code,
bal_seg_value_option_code, bal_seg_column_name,
bal_seg_value_set_id, mgt_seg_value_option_code,
mgt_seg_column_name, mgt_seg_value_set_id,
implicit_access_set_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
description, future_enterable_periods_limit,
ledger_attributes, latest_opened_period_name,
latest_encumbrance_year, cum_trans_code_combination_id,
res_encumb_code_combination_id,
net_income_code_combination_id,
rounding_code_combination_id, transaction_calendar_id,
daily_translation_rate_type, period_average_rate_type,
period_end_rate_type, CONTEXT, attribute1,
attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15,
--child_ledger_access_code,
ledger_category_code, configuration_id,
sla_accounting_method_code,
sla_accounting_method_type, sla_description_language,
sla_entered_cur_bal_sus_ccid,
sla_bal_by_ledger_curr_flag,
sla_ledger_cur_bal_sus_ccid, alc_ledger_type_code,
criteria_set_id,enable_secondary_track_flag,
enable_reval_ss_track_flag, enable_reconciliation_flag,
sla_ledger_cash_basis_flag, create_je_flag, complete_flag,commitment_budget_flag,net_closing_bal_flag,automate_sec_jrnl_rev_flag)
VALUES (x_ledger_id, x_name, x_short_name, x_chart_of_accounts_id,
x_currency_code, x_period_set_name,
x_accounted_period_type, decode(x_ledger_category_code,'ALC',
x_first_ledger_period_name,v_first_ledger_period_name),
x_ret_earn_code_combination_id, x_suspense_allowed_flag,
x_allow_intercompany_post_flag,
x_enable_avgbal_flag,
x_enable_budgetary_control_f,
x_require_budget_journals_flag, x_enable_je_approval_flag,
x_enable_automatic_tax_flag, x_consolidation_ledger_flag,
x_translate_eod_flag, x_translate_qatd_flag,
x_translate_yatd_flag, x_automatically_created_flag,
x_track_rounding_imbalance_f, --x_mrc_ledger_type_code,
'L', x_le_ledger_type_code,
x_bal_seg_value_option_code, x_bal_seg_column_name,
x_bal_seg_value_set_id, x_mgt_seg_value_option_code,
x_mgt_seg_column_name, x_mgt_seg_value_set_id,
NULL, x_last_update_date, x_last_updated_by,
x_creation_date, x_created_by, x_last_update_login,
x_description, x_future_enterable_periods_lmt,
'L',
-- 'Y' || fnd_global.newline || 'Y' || fnd_global.newline || 'L',
x_latest_opened_period_name,
x_latest_encumbrance_year, x_cum_trans_ccid,
x_res_encumb_ccid,
x_net_income_ccid,
x_rounding_ccid, x_transaction_calendar_id,
x_daily_translation_rate_type, x_period_average_rate_type,
x_period_end_rate_type, x_context, x_attribute1,
x_attribute2, x_attribute3, x_attribute4, x_attribute5,
x_attribute6, x_attribute7, x_attribute8, x_attribute9,
x_attribute10, x_attribute11, x_attribute12,
x_attribute13, x_attribute14, x_attribute15,
--x_child_ledger_access_code,
x_ledger_category_code, x_configuration_id,
x_sla_accounting_method_code,
x_sla_accounting_method_type, x_sla_description_language,
x_sla_entered_cur_bal_sus_ccid,
x_sla_bal_by_ledger_curr_flag,
x_sla_ledger_cur_bal_sus_ccid, x_alc_ledger_type_code,
x_criteria_set_id, x_enable_secondary_track_flag,
x_enable_reval_ss_track_flag, x_enable_reconciliation_flag,
x_sla_ledger_cash_basis_flag, x_create_je_flag, l_complete_flag,x_commitment_budget_flag,x_net_closing_bal_flag,x_auto_jrnl_rev_flag);
gl_conc_control_pkg.insert_conc_ledger(x_ledger_id, x_last_update_date,
x_last_updated_by,
x_creation_date, x_created_by,
x_last_update_login);
gl_period_statuses_pkg.insert_led_ps(x_ledger_id, x_period_set_name,
x_accounted_period_type,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_creation_date, x_created_by);
SELECT code_combination_id
INTO x_suspense_ccid_temp
FROM gl_code_combinations
WHERE code_combination_id = x_suspense_ccid;
gl_suspense_accounts_pkg.insert_ledger_suspense
(x_ledger_id,
x_suspense_ccid_temp,
x_last_update_date,
x_last_updated_by);
gl_ledgers_pkg.update_gl_system_usages(x_consolidation_ledger_flag);
gl_ledgers_pkg.insert_gl_net_income_accounts(x_ledger_id,
v_balancing_segment,
--x_balancing_segment,
x_net_income_ccid,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
'', '', '', '');
END insert_row;
PROCEDURE update_row(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validate_only IN VARCHAR2 := fnd_api.g_true,
p_record_version_number IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_rowid VARCHAR2,
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_currency_code VARCHAR2,
x_period_set_name VARCHAR2,
x_accounted_period_type VARCHAR2,
x_first_ledger_period_name VARCHAR2,
x_ret_earn_code_combination_id NUMBER,
x_suspense_allowed_flag VARCHAR2,
x_suspense_ccid NUMBER,
x_allow_intercompany_post_flag VARCHAR2,
x_enable_avgbal_flag VARCHAR2,
x_enable_budgetary_control_f VARCHAR2,
x_require_budget_journals_flag VARCHAR2,
x_enable_je_approval_flag VARCHAR2,
x_enable_automatic_tax_flag VARCHAR2,
x_consolidation_ledger_flag VARCHAR2,
x_translate_eod_flag VARCHAR2,
x_translate_qatd_flag VARCHAR2,
x_translate_yatd_flag VARCHAR2,
x_automatically_created_flag VARCHAR2,
x_track_rounding_imbalance_f VARCHAR2,
--x_mrc_ledger_type_code VARCHAR2,
x_le_ledger_type_code VARCHAR2,
x_bal_seg_value_option_code VARCHAR2,
x_mgt_seg_value_option_code VARCHAR2,
x_implicit_access_set_id NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_last_update_login NUMBER,
x_description VARCHAR2,
x_future_enterable_periods_lmt NUMBER,
x_latest_opened_period_name VARCHAR2,
x_latest_encumbrance_year NUMBER,
x_cum_trans_ccid NUMBER,
x_res_encumb_ccid NUMBER,
x_net_income_ccid NUMBER,
x_balancing_segment VARCHAR2,
x_rounding_ccid NUMBER,
x_transaction_calendar_id NUMBER,
x_daily_translation_rate_type VARCHAR2,
x_period_average_rate_type VARCHAR2,
x_period_end_rate_type VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_set_manual_flag VARCHAR2,
--x_child_ledger_access_code VARCHAR2,
x_ledger_category_code VARCHAR2,
x_configuration_id NUMBER,
x_sla_accounting_method_code VARCHAR2,
x_sla_accounting_method_type VARCHAR2,
x_sla_description_language VARCHAR2,
x_sla_entered_cur_bal_sus_ccid NUMBER,
x_sla_bal_by_ledger_curr_flag VARCHAR2,
x_sla_ledger_cur_bal_sus_ccid NUMBER,
x_alc_ledger_type_code VARCHAR2,
x_criteria_set_id NUMBER,
x_enable_secondary_track_flag VARCHAR2 DEFAULT 'N',
x_enable_reval_ss_track_flag VARCHAR2 DEFAULT 'N',
x_enable_reconciliation_flag VARCHAR2 DEFAULT 'N',
x_sla_ledger_cash_basis_flag VARCHAR2 DEFAULT 'N',
x_create_je_flag VARCHAR2 DEFAULT 'Y',
x_commitment_budget_flag VARCHAR2 DEFAULT NULL,
x_net_closing_bal_flag VARCHAR2 DEFAULT 'N',
x_auto_jrnl_rev_flag VARCHAR2 DEFAULT 'N') IS
l_api_version NUMBER := p_api_version;
l_api_name CONSTANT VARCHAR(30) := 'update_row';
SELECT DISTINCT object_id
FROM gl_ledger_config_details
WHERE object_type_code = 'PRIMARY'
AND setup_step_code = 'NONE'
AND configuration_id IN(
SELECT configuration_id
FROM gl_ledger_config_details
WHERE object_type_code = 'SECONDARY'
AND setup_step_code = 'NONE'
AND object_id = x_ledger_id);
SELECT DISTINCT rs.target_ledger_id
FROM gl_ledger_relationships rs, gl_ledgers lg
WHERE rs.source_ledger_id = x_ledger_id
AND rs.application_id = 101
AND rs.target_ledger_category_code = 'ALC'
AND rs.relationship_type_code in ('JOURNAL', 'SUBLEDGER')
AND lg.ledger_id = rs.target_ledger_id;
SELECT *
FROM gl_ledgers
WHERE ledger_id = x_ledger_id;
SELECT conversion_type
INTO t_period_average_rate_type
FROM gl_daily_conversion_types_v
WHERE conversion_type <> 'User'
AND conversion_type <> 'EMU FIXED'
AND conversion_type = x_period_average_rate_type;
SELECT conversion_type
INTO t_period_end_rate_type
FROM gl_daily_conversion_types_v
WHERE conversion_type <> 'User'
AND conversion_type <> 'EMU FIXED'
AND conversion_type = x_period_end_rate_type;
SELECT language_code
INTO t_sla_description_language
FROM fnd_languages_vl
WHERE (installed_flag = 'I' OR installed_flag = 'B')
AND language_code = x_sla_description_language;
SELECT criteria_set_id
INTO t_criteria_set_id
FROM gl_autorev_criteria_sets
WHERE criteria_set_id = x_criteria_set_id;
SELECT NAME, short_name
INTO x_current_name, x_current_short_name
FROM gl_ledgers
WHERE ledger_id = x_ledger_id;
UPDATE gl_ledger_relationships
SET target_ledger_name = x_name
WHERE target_ledger_id = x_ledger_id
AND ( target_ledger_category_code <> 'ALC'
OR ( target_ledger_category_code = 'ALC'
AND relationship_type_code <> 'BALANCE'));
UPDATE gl_ledger_relationships
SET target_ledger_short_name = x_short_name
WHERE target_ledger_id = x_ledger_id
AND ( target_ledger_category_code <> 'ALC'
OR ( target_ledger_category_code = 'ALC'
AND relationship_type_code <> 'BALANCE'));
UPDATE gl_ledger_config_details
SET object_name = x_name
WHERE configuration_id = x_configuration_id
AND object_id = x_ledger_id
AND object_type_code = x_ledger_category_code;
SELECT bal_seg_value_option_code, sla_accounting_method_code,
sla_accounting_method_type, allow_intercompany_post_flag
INTO x_current_bsv_option_code, x_current_sla_actg_method_code,
x_current_sla_actg_method_type, x_current_allow_intercom_flag
FROM gl_ledgers
WHERE ledger_id = x_ledger_id;
INSERT INTO gl_ledger_config_details
(configuration_id, object_type_code,
object_id, object_name, setup_step_code,
next_action_code, status_code, created_by,
last_update_login, last_update_date,
last_updated_by, creation_date)
VALUES (x_configuration_id, x_ledger_category_code,
x_ledger_id, x_name, 'BSV_ASSIGNMENTS',
'ASSIGN_BSV', 'NOT_STARTED', x_last_update_login,
x_last_update_login, x_last_update_date,
x_last_updated_by, x_last_update_date);
UPDATE gl_ledgers
SET bal_seg_value_option_code = x_bal_seg_value_option_code
WHERE ledger_id IN(
SELECT DISTINCT target_ledger_id
FROM gl_ledger_relationships
WHERE source_ledger_id = x_ledger_id
AND target_ledger_category_code = 'ALC');
INSERT INTO gl_ledger_config_details
(configuration_id, object_type_code,
object_id, object_name, setup_step_code,
next_action_code, status_code,
created_by, last_update_login,
last_update_date, last_updated_by,
creation_date)
VALUES (x_configuration_id, x_ledger_category_code,
x_ledger_id, x_name, 'SLAM_SETUP',
'REVIEW_DEFAULTS', 'CONFIRMED',
x_last_update_login, x_last_update_login,
x_last_update_date, x_last_updated_by,
x_last_update_date);
/* move to after update ledger
IF (x_ledger_category_code = 'PRIMARY') THEN
xla_acct_setup_pub_pkg.setup_ledger_options(x_ledger_id,
x_ledger_id);
DELETE FROM gl_ledger_config_details
WHERE configuration_id = x_configuration_id
AND object_type_code = x_ledger_category_code
AND object_id = x_ledger_id
AND object_name = x_name
AND setup_step_code = 'SLAM_SETUP';
UPDATE gl_ledger_config_details
SET next_action_code = 'REVIEW_DEFAULTS',
status_code = 'CONFIRMED',
last_update_login = x_last_update_login,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by
WHERE configuration_id = x_configuration_id
AND object_type_code = x_ledger_category_code
AND object_id = x_ledger_id
AND object_name = x_name
AND setup_step_code = 'SLAM_SETUP';
/* UPDATE gl_ledgers
SET future_enterable_periods_limit =
x_future_enterable_periods_lmt
WHERE ledger_id IN(
SELECT DISTINCT target_ledger_id
FROM gl_ledger_relationships
WHERE source_ledger_id = x_ledger_id
--AND target_ledger_id <> source_ledger_id
AND target_ledger_category_code = 'ALC'); */
INSERT INTO gl_ledger_config_details
(configuration_id, object_type_code,
object_id, object_name, setup_step_code,
next_action_code, status_code, created_by,
last_update_login, last_update_date,
last_updated_by, creation_date)
VALUES (x_configuration_id, x_ledger_category_code,
x_ledger_id, x_name, 'INTRA_BAL',
'DEFINE_RULES', 'NOT_STARTED', x_last_update_login,
x_last_update_login, x_last_update_date,
x_last_updated_by, x_last_update_date);
DELETE gl_ledger_config_details
WHERE configuration_id = x_configuration_id
AND object_id = x_ledger_id
AND setup_step_code = 'INTRA_BAL';
SELECT code_combination_id
INTO x_cum_trans_ccid_temp
FROM gl_code_combinations
WHERE code_combination_id = x_cum_trans_ccid;
SELECT code_combination_id
INTO x_res_encumb_ccid_temp
FROM gl_code_combinations
WHERE code_combination_id = x_res_encumb_ccid;
SELECT code_combination_id
INTO x_net_income_ccid_temp
FROM gl_code_combinations
WHERE code_combination_id = x_net_income_ccid;
SELECT code_combination_id
INTO x_rounding_ccid_temp
FROM gl_code_combinations
WHERE code_combination_id = x_rounding_ccid;
UPDATE gl_ledgers
SET --ledger_id = x_ledger_id,
NAME = x_name,
short_name = x_short_name,
--chart_of_accounts_id = x_chart_of_accounts_id,
--currency_code = x_currency_code,
--period_set_name = x_period_set_name,
--accounted_period_type = x_period_type,
first_ledger_period_name = x_first_ledger_period_name,
ret_earn_code_combination_id = x_ret_earn_code_combination_id,
suspense_allowed_flag = x_suspense_allowed_flag,
allow_intercompany_post_flag = x_allow_intercompany_post_flag,
enable_average_balances_flag = x_enable_avgbal_flag,
enable_budgetary_control_flag = x_enable_budgetary_control_f,
require_budget_journals_flag = x_require_budget_journals_flag,
enable_je_approval_flag = x_enable_je_approval_flag,
enable_automatic_tax_flag = x_enable_automatic_tax_flag,
consolidation_ledger_flag = x_consolidation_ledger_flag,
translate_eod_flag = x_translate_eod_flag,
translate_qatd_flag = x_translate_qatd_flag,
translate_yatd_flag = x_translate_yatd_flag,
automatically_created_flag = x_automatically_created_flag,
track_rounding_imbalance_flag = x_track_rounding_imbalance_f,
-- mrc_ledger_type_code = x_mrc_ledger_type_code,
le_ledger_type_code = x_le_ledger_type_code,
bal_seg_value_option_code = x_bal_seg_value_option_code,
--bal_seg_column_name = x_bal_seg_column_name,
--bal_seg_value_set_id = x_bal_seg_value_set_id,
mgt_seg_value_option_code = x_mgt_seg_value_option_code,
--mgt_seg_column_name = x_mgt_seg_column_name,
--mgt_seg_value_set_id = x_mgt_seg_value_set_id,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
description = x_description,
future_enterable_periods_limit = x_future_enterable_periods_lmt,
latest_opened_period_name = x_latest_opened_period_name,
--latest_encumbrance_year = x_latest_encumbrance_year,
cum_trans_code_combination_id = x_cum_trans_ccid_temp,
res_encumb_code_combination_id = x_res_encumb_ccid_temp,
net_income_code_combination_id = x_net_income_ccid_temp,
rounding_code_combination_id = x_rounding_ccid_temp,
transaction_calendar_id = x_transaction_calendar_id,
daily_translation_rate_type = x_daily_translation_rate_type,
period_average_rate_type = x_period_average_rate_type,
period_end_rate_type = x_period_end_rate_type,
CONTEXT = x_context,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15,
--child_ledger_access_code = x_child_ledger_access_code,
ledger_category_code = x_ledger_category_code,
configuration_id = x_configuration_id,
--association_level_code = x_association_level_code,
sla_accounting_method_code = p_sla_accounting_method_code,
sla_accounting_method_type = p_sla_accounting_method_type,
sla_description_language = p_sla_description_language,
sla_entered_cur_bal_sus_ccid = p_sla_entered_cur_bal_sus_ccid,
sla_bal_by_ledger_curr_flag = p_sla_bal_by_ledger_curr_flag,
sla_ledger_cur_bal_sus_ccid = p_sla_ledger_cur_bal_sus_ccid,
sla_sequencing_flag =
DECODE(p_sla_sequencing_flag,
'N', NULL,
sla_sequencing_flag),
alc_ledger_type_code = x_alc_ledger_type_code,
criteria_set_id = x_criteria_set_id,
enable_secondary_track_flag = x_enable_secondary_track_flag,
enable_reval_ss_track_flag = x_enable_reval_ss_track_flag,
enable_reconciliation_flag = x_enable_reconciliation_flag,
sla_ledger_cash_basis_flag = x_sla_ledger_cash_basis_flag,
create_je_flag = x_create_je_flag,
commitment_budget_flag = x_commitment_budget_flag,
net_closing_bal_flag =x_net_closing_bal_flag,
automate_sec_jrnl_rev_flag = x_auto_jrnl_rev_flag
WHERE ledger_id = x_ledger_id;
SELECT completion_status_code
INTO x_completion_status
FROM gl_ledger_configurations
WHERE configuration_id = x_configuration_id;
UPDATE gl_ledgers alclg
SET (future_enterable_periods_limit, suspense_allowed_flag,
allow_intercompany_post_flag, bal_seg_value_option_code,
mgt_seg_value_option_code, sla_accounting_method_code,
sla_accounting_method_type, sla_description_language,
sla_bal_by_ledger_curr_flag, sla_sequencing_flag,
sla_entered_cur_bal_sus_ccid, sla_ledger_cur_bal_sus_ccid,
last_update_date, last_updated_by, last_update_login,
first_ledger_period_name, ret_earn_code_combination_id,
track_rounding_imbalance_flag, enable_average_balances_flag,
cum_trans_code_combination_id, res_encumb_code_combination_id,
net_income_code_combination_id, rounding_code_combination_id,
enable_automatic_tax_flag, consolidation_ledger_flag,
translate_eod_flag, translate_qatd_flag, translate_yatd_flag,
transaction_calendar_id, daily_translation_rate_type,
criteria_set_id, period_average_rate_type,
period_end_rate_type, enable_secondary_track_flag,
enable_reval_ss_track_flag, enable_reconciliation_flag,
sla_ledger_cash_basis_flag, context, attribute1,
attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15) =
(SELECT srclg.future_enterable_periods_limit,
decode(l_suspense_changed, 'Y',
srclg.suspense_allowed_flag,
alclg.suspense_allowed_flag),
DECODE(l_intercom_changed, 'Y',
srclg.allow_intercompany_post_flag,
alclg.allow_intercompany_post_flag),
srclg.bal_seg_value_option_code,
srclg.mgt_seg_value_option_code,
srclg.sla_accounting_method_code,
srclg.sla_accounting_method_type,
srclg.sla_description_language,
srclg.sla_bal_by_ledger_curr_flag,
srclg.sla_sequencing_flag,
-- SLA sus CCIDs of ALC must be same as its source's
srclg.sla_entered_cur_bal_sus_ccid,
srclg.sla_ledger_cur_bal_sus_ccid,
srclg.last_update_date, srclg.last_updated_by,
srclg.last_update_login,
decode(srclg.complete_flag, 'Y',
alclg.first_ledger_period_name,
srclg.first_ledger_period_name),
decode(l_ret_changed, 'Y',
srclg.ret_earn_code_combination_id,
alclg.ret_earn_code_combination_id),
decode(srclg.track_rounding_imbalance_flag,'Y',
'Y', alclg.track_rounding_imbalance_flag),
decode(srclg.complete_flag, 'Y',
alclg.enable_average_balances_flag,
srclg.enable_average_balances_flag),
decode(l_cta_changed, 'Y',
srclg.cum_trans_code_combination_id,
alclg.cum_trans_code_combination_id),
decode(l_reserv_encum_changed, 'Y',
srclg.res_encumb_code_combination_id,
alclg.res_encumb_code_combination_id),
decode(srclg.complete_flag, 'Y',
alclg.net_income_code_combination_id,
srclg.net_income_code_combination_id),
decode(alclg.rounding_code_combination_id, null,
srclg.rounding_code_combination_id, -1,
srclg.rounding_code_combination_id,
alclg.rounding_code_combination_id),
decode(l_autotax_changed, 'Y',
srclg.enable_automatic_tax_flag,
alclg.enable_automatic_tax_flag),
decode(srclg.complete_flag, 'Y',
alclg.consolidation_ledger_flag,
srclg.consolidation_ledger_flag),
decode(l_trans_eod_changed, 'Y',
srclg.translate_eod_flag,
alclg.translate_eod_flag),
decode(l_trans_qatd_changed, 'Y',
srclg.translate_qatd_flag,
alclg.translate_qatd_flag),
decode(l_trans_yatd_changed, 'Y',
srclg.translate_yatd_flag,
alclg.translate_yatd_flag),
decode(srclg.complete_flag, 'Y',
alclg.transaction_calendar_id,
srclg.transaction_calendar_id),
decode(srclg.complete_flag, 'Y',
alclg.daily_translation_rate_type,
srclg.daily_translation_rate_type),
srclg.criteria_set_id,
decode(l_period_avg_rt_changed, 'Y',
srclg.period_average_rate_type,
alclg.period_average_rate_type),
decode(l_period_end_rt_changed, 'Y',
srclg.period_end_rate_type,
alclg.period_end_rate_type),
decode(srclg.complete_flag, 'Y',
alclg.enable_secondary_track_flag,
srclg.enable_secondary_track_flag),
decode(srclg.complete_flag, 'Y',
alclg.enable_reval_ss_track_flag,
srclg.enable_reval_ss_track_flag),
srclg.enable_reconciliation_flag,
srclg.sla_ledger_cash_basis_flag,
srclg.context,
srclg.attribute1,
srclg.attribute2,
srclg.attribute3,
srclg.attribute4,
srclg.attribute5,
srclg.attribute6,
srclg.attribute7,
srclg.attribute8,
srclg.attribute9,
srclg.attribute10,
srclg.attribute11,
srclg.attribute12,
srclg.attribute13,
srclg.attribute14,
srclg.attribute15
FROM gl_ledgers srclg
WHERE ledger_id = x_ledger_id)
WHERE ledger_id IN(
SELECT target_ledger_id
FROM gl_ledger_relationships
WHERE source_ledger_id = x_ledger_id
AND target_ledger_category_code = 'ALC'
AND relationship_type_code IN('SUBLEDGER', 'JOURNAL'));
gl_access_sets_pkg.update_implicit_access_set
(x_access_set_id => x_implicit_access_set_id,
x_name => x_name,
x_last_update_date => x_last_update_date,
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login);
update gl_ledger_configurations
set name = x_name
where configuration_id =
(select configuration_id
from gl_ledger_config_details
where object_id = x_ledger_id
and object_type_code = 'PRIMARY'
and setup_step_code = 'NONE');
'SELECT ' || x_bal_seg_column_name
|| ' FROM gl_code_combinations WHERE chart_of_accounts_id = :1 '
|| ' AND code_combination_id = :2 ';
gl_ledgers_pkg.update_gl_system_usages(x_consolidation_ledger_flag);
gl_ledgers_pkg.insert_gl_net_income_accounts(x_ledger_id,
v_balancing_segment,
--x_balancing_segment,
x_net_income_ccid,
x_last_update_date,
x_last_updated_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
'', '', '', '');
SELECT code_combination_id
INTO x_suspense_ccid_temp
FROM gl_code_combinations
WHERE code_combination_id = x_suspense_ccid;
gl_ledgers_pkg.led_update_other_tables(x_ledger_id,
x_last_update_date,
x_last_updated_by,
x_suspense_ccid_temp);
gl_ledgers_pkg.led_update_other_tables(v_alc.target_ledger_id,
x_last_update_date,
x_last_updated_by,
x_suspense_ccid_temp);
END update_row;
SELECT 'Duplicate'
FROM gl_ledgers l
WHERE l.NAME = x_name AND(x_rowid IS NULL OR l.ROWID <> x_rowid);
SELECT 'Duplicate'
FROM gl_access_sets a
WHERE a.NAME = x_name AND a.automatically_created_flag <> 'Y';
SELECT 'Duplicate'
FROM gl_ledgers l
WHERE l.short_name = x_short_name
AND (x_rowid IS NULL OR l.ROWID <> x_rowid);
SELECT gl_ledgers_s.NEXTVAL
FROM DUAL;
SELECT 'X'
FROM fnd_id_flex_structures s
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = x_chart_of_accounts_id
AND s.freeze_flex_definition_flag = 'Y';
SELECT s.application_column_name, s.flex_value_set_id
FROM fnd_id_flex_segments s, fnd_segment_attribute_values v
WHERE s.application_id = v.application_id
AND s.id_flex_code = v.id_flex_code
AND s.id_flex_num = v.id_flex_num
AND s.application_column_name = v.application_column_name
AND v.application_id = 101
AND v.id_flex_code = 'GL#'
AND v.id_flex_num = x_chart_of_accounts_id
AND v.segment_attribute_type = 'GL_BALANCING'
AND v.attribute_value = 'Y';
SELECT s.application_column_name, s.flex_value_set_id
FROM fnd_id_flex_segments s, fnd_segment_attribute_values v
WHERE s.application_id = v.application_id
AND s.id_flex_code = v.id_flex_code
AND s.id_flex_num = v.id_flex_num
AND s.application_column_name = v.application_column_name
AND v.application_id = 101
AND v.id_flex_code = 'GL#'
AND v.id_flex_num = x_chart_of_accounts_id
AND v.segment_attribute_type = 'GL_MANAGEMENT'
AND v.attribute_value = 'Y';
PROCEDURE insert_row(
x_rowid IN OUT NOCOPY VARCHAR2,
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_chart_of_accounts_name VARCHAR2,
x_currency_code VARCHAR2,
x_period_set_name VARCHAR2,
x_user_period_type VARCHAR2,
x_accounted_period_type VARCHAR2,
x_first_ledger_period_name VARCHAR2,
x_ret_earn_code_combination_id NUMBER,
x_suspense_allowed_flag VARCHAR2,
x_suspense_ccid NUMBER,
x_allow_intercompany_post_flag VARCHAR2,
x_enable_avgbal_flag VARCHAR2,
x_enable_budgetary_control_f VARCHAR2,
x_require_budget_journals_flag VARCHAR2,
x_enable_je_approval_flag VARCHAR2,
x_enable_automatic_tax_flag VARCHAR2,
x_consolidation_ledger_flag VARCHAR2,
x_translate_eod_flag VARCHAR2,
x_translate_qatd_flag VARCHAR2,
x_translate_yatd_flag VARCHAR2,
x_automatically_created_flag VARCHAR2,
x_track_rounding_imbalance_f VARCHAR2,
x_alc_ledger_type_code VARCHAR2,
x_le_ledger_type_code VARCHAR2,
x_bal_seg_value_option_code VARCHAR2,
x_bal_seg_column_name VARCHAR2,
x_bal_seg_value_set_id NUMBER,
x_mgt_seg_value_option_code VARCHAR2,
x_mgt_seg_column_name VARCHAR2,
x_mgt_seg_value_set_id NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_description VARCHAR2,
x_future_enterable_periods_lmt NUMBER,
x_latest_opened_period_name VARCHAR2,
x_latest_encumbrance_year NUMBER,
x_cum_trans_ccid NUMBER,
x_res_encumb_ccid NUMBER,
x_net_income_ccid NUMBER,
x_balancing_segment VARCHAR2,
x_rounding_ccid NUMBER,
x_transaction_calendar_id NUMBER,
x_transaction_calendar_name VARCHAR2,
x_daily_translation_rate_type VARCHAR2,
x_daily_user_translation_type VARCHAR2,
x_period_average_rate_type VARCHAR2,
x_period_avg_user_rate_type VARCHAR2,
x_period_end_rate_type VARCHAR2,
x_period_end_user_rate_type VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_set_manual_flag VARCHAR2) IS
CURSOR c IS
SELECT ROWID
FROM gl_ledgers
WHERE NAME = x_name;
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login,
x_creation_date => x_creation_date,
x_description => x_description);
INSERT INTO gl_ledgers
(ledger_id, NAME, short_name,
chart_of_accounts_id, currency_code,
period_set_name, accounted_period_type,
first_ledger_period_name,
ret_earn_code_combination_id, suspense_allowed_flag,
allow_intercompany_post_flag,
enable_average_balances_flag,
enable_budgetary_control_flag,
require_budget_journals_flag,
enable_je_approval_flag, enable_automatic_tax_flag,
consolidation_ledger_flag, translate_eod_flag,
translate_qatd_flag, translate_yatd_flag,
automatically_created_flag,
track_rounding_imbalance_flag, alc_ledger_type_code,
ledger_category_code, object_type_code,
le_ledger_type_code, bal_seg_value_option_code,
bal_seg_column_name, bal_seg_value_set_id,
mgt_seg_value_option_code, mgt_seg_column_name,
mgt_seg_value_set_id, implicit_access_set_id,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description,
future_enterable_periods_limit, ledger_attributes,
latest_opened_period_name, latest_encumbrance_year,
cum_trans_code_combination_id,
res_encumb_code_combination_id,
net_income_code_combination_id,
rounding_code_combination_id, transaction_calendar_id,
daily_translation_rate_type,
period_average_rate_type, period_end_rate_type,
CONTEXT, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14, attribute15
,net_closing_bal_flag,automate_sec_jrnl_rev_flag)--Added the net closing bal flag for bug 8612291
VALUES (x_ledger_id, x_name, x_short_name,
x_chart_of_accounts_id, x_currency_code,
x_period_set_name, x_accounted_period_type,
x_first_ledger_period_name,
x_ret_earn_code_combination_id, x_suspense_allowed_flag,
x_allow_intercompany_post_flag,
x_enable_avgbal_flag,
x_enable_budgetary_control_f,
x_require_budget_journals_flag,
x_enable_je_approval_flag, x_enable_automatic_tax_flag,
x_consolidation_ledger_flag, x_translate_eod_flag,
x_translate_qatd_flag, x_translate_yatd_flag,
x_automatically_created_flag,
x_track_rounding_imbalance_f, x_alc_ledger_type_code,
'NONE', 'L',
x_le_ledger_type_code, x_bal_seg_value_option_code,
x_bal_seg_column_name, x_bal_seg_value_set_id,
x_mgt_seg_value_option_code, x_mgt_seg_column_name,
x_mgt_seg_value_set_id, x_access_set_id,
x_last_update_date, x_last_updated_by, x_creation_date,
x_created_by, x_last_update_login, x_description,
x_future_enterable_periods_lmt, 'L',
x_latest_opened_period_name, x_latest_encumbrance_year,
x_cum_trans_ccid,
x_res_encumb_ccid,
x_net_income_ccid,
x_rounding_ccid, x_transaction_calendar_id,
x_daily_translation_rate_type,
x_period_average_rate_type, x_period_end_rate_type,
x_context, x_attribute1, x_attribute2, x_attribute3,
x_attribute4, x_attribute5, x_attribute6, x_attribute7,
x_attribute8, x_attribute9, x_attribute10, x_attribute11,
x_attribute12, x_attribute13, x_attribute14, x_attribute15,
'N','N');--Added the default value for net closing bal flag for bug 8612291
gl_conc_control_pkg.insert_conc_ledger(x_ledger_id, x_last_update_date,
x_last_updated_by,
x_creation_date, x_created_by,
x_last_update_login);
gl_period_statuses_pkg.insert_led_ps(x_ledger_id, x_period_set_name,
x_accounted_period_type,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_creation_date, x_created_by);
gl_suspense_accounts_pkg.insert_ledger_suspense(x_ledger_id,
x_suspense_ccid,
x_last_update_date,
x_last_updated_by);
gl_ledgers_pkg.update_gl_system_usages(x_consolidation_ledger_flag);
gl_ledgers_pkg.insert_gl_net_income_accounts(x_ledger_id,
x_balancing_segment,
x_net_income_ccid,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
'', '', '', '');
END insert_row;
SELECT *
FROM gl_ledgers
WHERE ROWID = x_rowid
FOR UPDATE OF NAME NOWAIT;
PROCEDURE update_row(
x_rowid VARCHAR2,
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_chart_of_accounts_name VARCHAR2,
x_currency_code VARCHAR2,
x_period_set_name VARCHAR2,
x_user_period_type VARCHAR2,
x_accounted_period_type VARCHAR2,
x_first_ledger_period_name VARCHAR2,
x_ret_earn_code_combination_id NUMBER,
x_suspense_allowed_flag VARCHAR2,
x_suspense_ccid NUMBER,
x_allow_intercompany_post_flag VARCHAR2,
x_enable_avgbal_flag VARCHAR2,
x_enable_budgetary_control_f VARCHAR2,
x_require_budget_journals_flag VARCHAR2,
x_enable_je_approval_flag VARCHAR2,
x_enable_automatic_tax_flag VARCHAR2,
x_consolidation_ledger_flag VARCHAR2,
x_translate_eod_flag VARCHAR2,
x_translate_qatd_flag VARCHAR2,
x_translate_yatd_flag VARCHAR2,
x_automatically_created_flag VARCHAR2,
x_track_rounding_imbalance_f VARCHAR2,
x_alc_ledger_type_code VARCHAR2,
x_le_ledger_type_code VARCHAR2,
x_bal_seg_value_option_code VARCHAR2,
x_bal_seg_column_name VARCHAR2,
x_bal_seg_value_set_id NUMBER,
x_mgt_seg_value_option_code VARCHAR2,
x_mgt_seg_column_name VARCHAR2,
x_mgt_seg_value_set_id NUMBER,
x_implicit_access_set_id NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_last_update_login NUMBER,
x_description VARCHAR2,
x_future_enterable_periods_lmt NUMBER,
x_latest_opened_period_name VARCHAR2,
x_latest_encumbrance_year NUMBER,
x_cum_trans_ccid NUMBER,
x_res_encumb_ccid NUMBER,
x_net_income_ccid NUMBER,
x_balancing_segment VARCHAR2,
x_rounding_ccid NUMBER,
x_transaction_calendar_id NUMBER,
x_transaction_calendar_name VARCHAR2,
x_daily_translation_rate_type VARCHAR2,
x_daily_user_translation_type VARCHAR2,
x_period_average_rate_type VARCHAR2,
x_period_avg_user_rate_type VARCHAR2,
x_period_end_rate_type VARCHAR2,
x_period_end_user_rate_type VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_set_manual_flag VARCHAR2) IS
BEGIN
UPDATE gl_ledgers
SET ledger_id = x_ledger_id,
NAME = x_name,
short_name = x_short_name,
chart_of_accounts_id = x_chart_of_accounts_id,
currency_code = x_currency_code,
period_set_name = x_period_set_name,
accounted_period_type = x_accounted_period_type,
first_ledger_period_name = x_first_ledger_period_name,
ret_earn_code_combination_id = x_ret_earn_code_combination_id,
suspense_allowed_flag = x_suspense_allowed_flag,
allow_intercompany_post_flag = x_allow_intercompany_post_flag,
enable_average_balances_flag = x_enable_avgbal_flag,
enable_budgetary_control_flag = x_enable_budgetary_control_f,
require_budget_journals_flag = x_require_budget_journals_flag,
enable_je_approval_flag = x_enable_je_approval_flag,
enable_automatic_tax_flag = x_enable_automatic_tax_flag,
consolidation_ledger_flag = x_consolidation_ledger_flag,
translate_eod_flag = x_translate_eod_flag,
translate_qatd_flag = x_translate_qatd_flag,
translate_yatd_flag = x_translate_yatd_flag,
automatically_created_flag = x_automatically_created_flag,
track_rounding_imbalance_flag = x_track_rounding_imbalance_f,
alc_ledger_type_code = x_alc_ledger_type_code,
le_ledger_type_code = x_le_ledger_type_code,
bal_seg_value_option_code = x_bal_seg_value_option_code,
bal_seg_column_name = x_bal_seg_column_name,
bal_seg_value_set_id = x_bal_seg_value_set_id,
mgt_seg_value_option_code = x_mgt_seg_value_option_code,
mgt_seg_column_name = x_mgt_seg_column_name,
mgt_seg_value_set_id = x_mgt_seg_value_set_id,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
description = x_description,
future_enterable_periods_limit = x_future_enterable_periods_lmt,
latest_opened_period_name = x_latest_opened_period_name,
latest_encumbrance_year = x_latest_encumbrance_year,
cum_trans_code_combination_id = x_cum_trans_ccid,
res_encumb_code_combination_id = x_res_encumb_ccid,
net_income_code_combination_id = x_net_income_ccid,
rounding_code_combination_id = x_rounding_ccid,
transaction_calendar_id = x_transaction_calendar_id,
daily_translation_rate_type = x_daily_translation_rate_type,
period_average_rate_type = x_period_average_rate_type,
period_end_rate_type = x_period_end_rate_type,
CONTEXT = x_context,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15
WHERE ROWID = x_rowid;
gl_access_sets_pkg.update_implicit_access_set
(x_access_set_id => x_implicit_access_set_id,
x_name => x_name,
x_last_update_date => x_last_update_date,
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login);
gl_ledgers_pkg.led_update_other_tables(x_ledger_id, x_last_update_date,
x_last_updated_by,
x_suspense_ccid);
END update_row;
PROCEDURE select_row(
recinfo IN OUT NOCOPY gl_ledgers%ROWTYPE) IS
BEGIN
SELECT *
INTO recinfo
FROM gl_ledgers
WHERE ledger_id = recinfo.ledger_id;
END select_row;
PROCEDURE select_columns(
x_ledger_id NUMBER,
x_name IN OUT NOCOPY VARCHAR2) IS
recinfo gl_ledgers%ROWTYPE;
select_row(recinfo);
END select_columns;
PROCEDURE update_gl_system_usages(
cons_lgr_flag VARCHAR2) IS
BEGIN
UPDATE gl_system_usages
SET average_balances_flag = 'Y',
consolidation_ledger_flag =
DECODE(cons_lgr_flag,
'Y', 'Y',
consolidation_ledger_flag)
WHERE EXISTS(
SELECT '1'
FROM gl_system_usages
WHERE average_balances_flag = 'N'
OR ( (cons_lgr_flag = 'Y')
AND consolidation_ledger_flag = 'N'));
END update_gl_system_usages;
PROCEDURE insert_gl_net_income_accounts(
x_ledger_id NUMBER,
x_balancing_segment VARCHAR2,
x_net_income_ccid NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_last_update_login NUMBER,
x_request_id NUMBER,
x_program_application_id NUMBER,
x_program_id NUMBER,
x_program_update_date DATE) IS
BEGIN
INSERT INTO gl_net_income_accounts
(ledger_id, bal_seg_value, code_combination_id,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, request_id,
program_application_id, program_id,
program_update_date)
VALUES (x_ledger_id, x_balancing_segment, x_net_income_ccid,
x_creation_date, x_created_by, x_last_update_date,
x_last_updated_by, x_last_update_login, x_request_id,
x_program_application_id, x_program_id,
x_program_update_date);
END insert_gl_net_income_accounts;
PROCEDURE led_update_other_tables(
x_ledger_id NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_suspense_ccid NUMBER) IS
FOUND BOOLEAN;
gl_suspense_accounts_pkg.update_ledger_suspense(x_ledger_id,
x_suspense_ccid,
x_last_update_date,
x_last_updated_by);
gl_suspense_accounts_pkg.insert_ledger_suspense
(x_ledger_id,
x_suspense_ccid,
x_last_update_date,
x_last_updated_by);
END led_update_other_tables;
SELECT 'avg translated'
FROM DUAL
WHERE EXISTS(
SELECT 'X'
FROM gl_translation_tracking
WHERE ledger_id = x_ledger_id
AND average_translation_flag = 'Y'
AND earliest_ever_period_name <>
earliest_never_period_name);
UPDATE gl_je_sources
SET journal_approval_flag = 'Y'
WHERE je_source_name = 'Manual';
PROCEDURE insert_set(
x_rowid IN OUT NOCOPY VARCHAR2,
x_access_set_id IN OUT NOCOPY NUMBER,
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_period_set_name VARCHAR2,
x_accounted_period_type VARCHAR2,
x_default_ledger_id NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_description VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2) IS
l_bal_seg_column_name VARCHAR2(25);
SELECT ROWID
FROM gl_ledgers
WHERE NAME = x_name;
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login,
x_creation_date => x_creation_date,
x_description => x_description);
INSERT INTO gl_ledgers
(ledger_id, NAME, short_name,
chart_of_accounts_id, currency_code, period_set_name,
accounted_period_type, first_ledger_period_name,
ret_earn_code_combination_id, suspense_allowed_flag,
allow_intercompany_post_flag,
track_rounding_imbalance_flag,
enable_average_balances_flag,
enable_budgetary_control_flag,
require_budget_journals_flag, enable_je_approval_flag,
enable_automatic_tax_flag, consolidation_ledger_flag,
translate_eod_flag, translate_qatd_flag,
translate_yatd_flag, automatically_created_flag,
alc_ledger_type_code, ledger_category_code,
object_type_code, le_ledger_type_code,
bal_seg_value_option_code, bal_seg_column_name,
mgt_seg_value_option_code, mgt_seg_column_name,
bal_seg_value_set_id, mgt_seg_value_set_id,
implicit_access_set_id, future_enterable_periods_limit,
ledger_attributes, enable_reconciliation_flag,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
description, CONTEXT, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, create_je_flag,net_closing_bal_flag,automate_sec_jrnl_rev_flag)--Added the net closing bal flag for bug 8612291
VALUES (x_ledger_id, x_name, x_short_name,
x_chart_of_accounts_id, 'X', -- currency_code
x_period_set_name,
x_accounted_period_type, 'X', -- first_ledger_period_name
-1, -- ret_earn_code_combination_id
'N', -- suspense_allowed_flag
'N', -- allow_intercompany_post_flag
'N', -- track_rounding_imbalance_flag
'N', -- enable_average_balances_flag
'N', -- enable_budgetary_control_flag
'N', -- require_budget_journals_flag
'N', -- enable_je_approval_flag
'N', -- enable_automatic_tax_flag
'N', -- consolidation_ledger_flag
'N', -- translate_eod_flag
'N', -- translate_qatd_flag
'N', -- translate_yatd_flag
'N', -- automatically_created_flag
'NONE', -- alc_ledger_type_code
'NONE', -- ledger_category_code
'S', -- object_type_code
'U', -- le_ledger_type_code
'I', -- bal_seg_value_option_code
l_bal_seg_column_name,
'I', -- mgt_seg_value_option_code
l_mgt_seg_column_name,
l_bal_seg_value_set_id, l_mgt_seg_value_set_id,
x_access_set_id, 0, -- future_enterable_periods_limit
'S', 'N', -- ledger_attributes, enable_reconciliation_flag
x_last_update_date, x_last_updated_by,
x_creation_date, x_created_by, x_last_update_login,
x_description, x_context, x_attribute1, x_attribute2,
x_attribute3, x_attribute4, x_attribute5, x_attribute6,
x_attribute7, x_attribute8, x_attribute9, x_attribute10,
x_attribute11, x_attribute12, x_attribute13,
x_attribute14, x_attribute15,
'N',-- create_je_flag
'N','N'); --Added the default value net closing bal flag for bug 8612291
END insert_set;
SELECT *
FROM gl_ledgers
WHERE ROWID = x_rowid
FOR UPDATE OF NAME NOWAIT;
PROCEDURE update_set(
x_rowid VARCHAR2,
x_access_set_id NUMBER,
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_period_set_name VARCHAR2,
x_accounted_period_type VARCHAR2,
x_default_ledger_id NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_last_update_login NUMBER,
x_description VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2) IS
BEGIN
UPDATE gl_ledgers
SET ledger_id = x_ledger_id,
NAME = x_name,
short_name = x_short_name,
chart_of_accounts_id = x_chart_of_accounts_id,
period_set_name = x_period_set_name,
accounted_period_type = x_accounted_period_type,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
description = x_description,
CONTEXT = x_context,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15
WHERE ROWID = x_rowid;
gl_access_sets_pkg.update_implicit_access_set
(x_access_set_id => x_access_set_id,
x_name => x_name,
x_last_update_date => x_last_update_date,
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login);
UPDATE gl_access_sets
SET default_ledger_id = x_default_ledger_id
WHERE access_set_id = x_access_set_id;
END update_set;
SELECT 1
FROM GL_LEDGER_SET_NORM_ASSIGN
WHERE ledger_set_id = x_ledger_set_id
AND ledger_id = x_default_ledger_id
AND (status_code <> 'D' OR status_code IS NULL)
AND rownum < 2;
updated_by NUMBER;
update_login NUMBER;
update_date DATE;
SELECT last_updated_by, last_update_login, last_update_date
INTO updated_by, update_login, update_date
FROM GL_LEDGERS
WHERE ledger_id = x_ledger_set_id;
GL_LEDGER_SET_NORM_ASSIGN_PKG.Insert_Row(
rowid,
x_ledger_set_id,
x_default_ledger_id,
'L', -- object_type_code
update_date, -- last_update_date
updated_by, -- last_updated_by
update_date, -- creation_date
updated_by, -- created_by
update_login, -- last_update_login
NULL, -- start_date
NULL, -- end_date
NULL, -- context
NULL, -- attribute1
NULL, -- attribute2
NULL, -- attribute3
NULL, -- attribute4
NULL, -- attribute5
NULL, -- attribute6
NULL, -- attribute7
NULL, -- attribute8
NULL, -- attribute9
NULL, -- attribute10
NULL, -- attribute11
NULL, -- attribute12
NULL, -- attribute13
NULL, -- attribute14
NULL, -- attribute15
NULL); -- request_id
PROCEDURE insert_set(
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_period_set_name VARCHAR2,
x_accounted_period_type VARCHAR2,
x_default_ledger_id NUMBER,
x_date DATE,
x_user_id NUMBER,
x_login_id NUMBER,
x_description VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2) IS
l_bal_seg_column_name VARCHAR2(25);
x_last_updated_by => x_user_id,
x_last_update_login => x_login_id,
x_creation_date => x_date,
x_description => x_description);
INSERT INTO GL_LEDGERS
(ledger_id, name, short_name, chart_of_accounts_id, currency_code,
period_set_name, accounted_period_type, first_ledger_period_name,
ret_earn_code_combination_id, suspense_allowed_flag,
allow_intercompany_post_flag, track_rounding_imbalance_flag,
enable_average_balances_flag, enable_budgetary_control_flag,
require_budget_journals_flag, enable_je_approval_flag,
enable_automatic_tax_flag, consolidation_ledger_flag,
translate_eod_flag, translate_qatd_flag,
translate_yatd_flag, automatically_created_flag,
alc_ledger_type_code, ledger_category_code,
object_type_code, le_ledger_type_code,
bal_seg_value_option_code, bal_seg_column_name,
mgt_seg_value_option_code, mgt_seg_column_name,
bal_seg_value_set_id, mgt_seg_value_set_id,
implicit_access_set_id, future_enterable_periods_limit,
ledger_attributes, enable_reconciliation_flag,
last_update_date, last_updated_by, last_update_login,
creation_date, created_by, description,
context, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14, attribute15,
create_je_flag,net_closing_bal_flag,automate_sec_jrnl_rev_flag)--Added the net closing bal flag for bug 8612291
VALUES
(x_ledger_id, x_name, x_short_name, x_chart_of_accounts_id, 'X',
x_period_set_name, x_accounted_period_type, 'X',
-1, 'N', -- ret_earn_code_combination_id, suspense_allowed_flag
'N','N', -- allow_intercompany_post_flag, track_rounding_imbalance_flag
'N','N', -- enable_average_balances_flag, enable_budgetary_control_flag
'N','N', -- require_budget_journals_flag, enable_je_approval_flag
'N','N', -- enable_automatic_tax_flag, consolidation_ledger_flag
'N','N', -- translate_eod_flag, translate_qatd_flag
'N','N', -- translate_yatd_flag, automatically_created_flag
'NONE','NONE', -- alc_ledger_type_code, ledger_category_code
'S','U', -- object_type_code, le_ledger_type_code
'I', -- bal_seg_value_option_code
l_bal_seg_column_name,
'I', -- mgt_seg_value_option_code
l_mgt_seg_column_name,
l_bal_seg_value_set_id, l_mgt_seg_value_set_id,
l_access_set_id, 0, -- future_enterable_periods_limit
'S', 'N', -- ledger_attributes, enable_reconciliation_flag
x_date, x_user_id, x_login_id,
x_date, x_user_id, x_description,
x_context, x_attribute1, x_attribute2, x_attribute3,
x_attribute4, x_attribute5, x_attribute6, x_attribute7,
x_attribute8, x_attribute9, x_attribute10, x_attribute11,
x_attribute12, x_attribute13, x_attribute14, x_attribute15,
'N', -- enable_reconciliation_flag, create_je_flag
'N','N');--Added the net closing bal flag for bug 8612291
END insert_set;
PROCEDURE update_set(
x_ledger_id NUMBER,
x_name VARCHAR2,
x_short_name VARCHAR2,
x_chart_of_accounts_id NUMBER,
x_period_set_name VARCHAR2,
x_accounted_period_type VARCHAR2,
x_default_ledger_id NUMBER,
x_date DATE,
x_user_id NUMBER,
x_login_id NUMBER,
x_description VARCHAR2,
x_context VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2) IS
l_access_set_id NUMBER(15);
UPDATE GL_LEDGERS
SET name = x_name,
short_name = x_short_name,
chart_of_accounts_id = x_chart_of_accounts_id,
period_set_name = x_period_set_name,
accounted_period_type = x_accounted_period_type,
last_update_date = x_date,
last_updated_by = x_user_id,
last_update_login = x_login_id,
description = x_description,
context = x_context,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15
WHERE ledger_id = x_ledger_id
RETURNING implicit_access_set_id INTO l_access_set_id;
GL_ACCESS_SETS_PKG.update_implicit_access_set
(x_access_set_id => l_access_set_id,
x_name => x_name,
x_last_update_date => x_date,
x_last_updated_by => x_user_id,
x_last_update_login => x_login_id);
UPDATE GL_ACCESS_SETS
SET default_ledger_id = x_default_ledger_id
WHERE access_set_id = l_access_set_id;
END update_set;
select distinct cg1.object_id ledger_id,cg1.object_type_code
from gl_ledger_config_details cg1, gl_ledger_config_details cg2
where cg2.object_id = x_le_id
and cg1.configuration_id = cg2.configuration_id
and cg1.object_type_code in ('PRIMARY','SECONDARY');
delete from gl_legal_entities_bsvs
where legal_entity_id = x_le_id;
delete from gl_ledger_norm_seg_vals
where legal_entity_id = x_le_id
and segment_type_code = 'B'
and segment_value_type_code = 'S';
select count(*) into BSVCount from
gl_ledger_norm_seg_vals where ledger_id = PLSL.ledger_id;
update gl_ledgers
set bal_seg_value_option_code = 'A'
where ledger_id = PLSL.ledger_id;
update gl_ledgers
set bal_seg_value_option_code = 'A'
where ledger_id in
(
select distinct target_ledger_id from gl_ledger_relationships
where source_ledger_id = PLSL.ledger_id
and target_ledger_category_code = 'ALC'
and relationship_type_code not in('NONE','BALANCE')
);
select l.ledger_id, cd2.configuration_id
from gl_ledgers l, gl_le_value_sets lv,
gl_ledger_config_details cd1, gl_ledger_config_details cd2
where lv.legal_entity_id = x_le_id
and lv.flex_value_set_id = x_value_set_id
and cd1.object_id = lv.legal_entity_id
and cd1.object_type_code = 'LEGAL_ENTITY'
and cd1.setup_step_code = 'NONE'
and cd2.configuration_id = cd1.configuration_id
and cd2.object_type_code in ('PRIMARY', 'SECONDARY')
and cd2.setup_step_code = 'NONE'
and l.bal_seg_value_set_id = lv.flex_value_set_id
and l.ledger_id = cd2.object_id;
select rowid
from gl_ledger_norm_seg_vals
where ledger_id = c_ledger_id
and segment_type_code = 'B'
and segment_value = x_bsv_value
and segment_value_type_code = 'S'
FOR UPDATE NOWAIT;
SELECT 'has_le_bsv'
FROM GL_LEDGER_NORM_SEG_VALS
WHERE ledger_id = c_ledger_id
AND legal_entity_id IS NOT NULL
AND segment_type_code = 'B'
AND segment_value_type_code = 'S'
AND rownum<2;
select completion_status_code
from gl_ledger_configurations
where configuration_id =
(select configuration_id
from gl_ledger_config_details
where object_id = x_le_id
and object_type_code = 'LEGAL_ENTITY');
GL_LEDGER_NORM_SEG_VALS_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Ledger_Id => l_ledger_id,
X_Segment_Type_Code => 'B',
X_Segment_Value => x_bsv_value,
X_Segment_Value_Type_Code => 'S',
X_Record_Id => GL_LEDGER_NORM_SEG_VALS_PKG.Get_Record_Id,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => fnd_global.user_id,
X_Creation_Date => sysdate,
X_Created_By => fnd_global.user_id,
X_Last_Update_Login => fnd_global.login_id,
X_Start_Date => x_start_date,
X_End_Date => x_end_date,
X_Context => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Request_Id => null);
update gl_ledger_norm_seg_vals
set legal_entity_id = x_le_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where ledger_id = l_ledger_id
and segment_type_code = 'B'
and segment_value = x_bsv_value
and segment_value_type_code = 'S';
insert into gl_ledger_config_details
(configuration_id,
object_type_code,
object_id,
object_name,
setup_step_code,
next_action_code,
status_code,
created_by,
last_update_login,
last_update_date,
last_updated_by,
creation_date)
select
configuration_id,
object_type_code,
object_id,
object_name,
'INTER_ASSG',
'ASSIGN_ACCTS',
'NOT_STARTED',
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
sysdate
from gl_ledger_config_details
where object_id = l_ledger_id
and object_type_code <> 'LEGAL_ENTITY'
and setup_step_code = 'NONE'
and configuration_id = l_config_id
and NOT EXISTS(select 1
from gl_ledger_config_details
where object_id = l_ledger_id
and object_type_code <> 'LEGAL_ENTITY'
and setup_step_code = 'INTER_ASSG');
update gl_ledgers
set bal_seg_value_option_code = 'I'
where ledger_id = l_ledger_id
and bal_seg_value_option_code = 'A';
update gl_ledgers
set bal_seg_value_option_code = 'I'
where ledger_id in
(select target_ledger_id
from gl_ledger_relationships
where source_ledger_id = l_ledger_id
and target_ledger_category_code = 'ALC'
and relationship_type_code in ('JOURNAL','SUBLEDGER') )
and bal_seg_value_option_code = 'A';
delete from gl_ledger_norm_seg_vals
where rowid = l_rowid;
delete gl_ledger_norm_seg_vals
where ledger_id = l_ledger_id
and segment_type_code = 'B'
and segment_value_type_code = 'S';
update gl_ledgers
set bal_seg_value_option_code = 'A'
where ledger_id = l_ledger_id
and bal_seg_value_option_code = 'I';
update gl_ledgers
set bal_seg_value_option_code = 'A'
where ledger_id in
(select target_ledger_id
from gl_ledger_relationships
where source_ledger_id = l_ledger_id
and target_ledger_category_code = 'ALC'
and relationship_type_code in ('JOURNAL','SUBLEDGER') )
and bal_seg_value_option_code = 'I';
GL_LEDGER_NORM_SEG_VALS_PKG.Delete_Row(
X_Rowid => l_rowid);
update gl_ledger_norm_seg_vals
set start_date = x_start_date,
end_date = x_end_date,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where rowid = l_rowid;
update gl_ledger_norm_seg_vals
set start_date = x_start_date,
end_date = x_end_date,
status_code = decode(status_code, 'I', 'I', 'U'),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where rowid = l_rowid;
select application_table_name, value_column_name,
nvl(meaning_column_name, 'null'), enabled_column_name,
start_date_column_name, end_date_column_name,
additional_where_clause
from fnd_flex_validation_tables
where flex_value_set_id = c_flex_value_set_id;
select BAL_SEG_VALUE_SET_ID
from gl_ledgers
where ledger_id = c_ledger_id;
sql_stmt := 'select '||l_meaning_col_name||' from '||l_table_name||
' where '||l_value_col_name||
' = :1 and '||l_enabled_col_name||' =''Y'''
||' and nvl('||l_start_date_col_name||',sysdate)<=sysdate'
||' and nvl('||l_end_date_col_name||',sysdate)>=sysdate';
SELECT min(start_date) begins, max(end_date) ends
FROM gl_periods
WHERE period_set_name = x_period_set_name
AND period_type = x_period_type;
SELECT accounting_date
FROM gl_date_period_map
WHERE period_name = not_assigned
AND period_set_name = x_period_set_name
AND period_type = x_period_type
AND accounting_date BETWEEN beginning AND ending;
SELECT max(accounting_date)
FROM gl_date_period_map
WHERE period_name <> not_assigned
AND period_set_name = x_period_set_name
AND period_type = x_period_type
AND accounting_date < gap_date;
SELECT min(accounting_date)
FROM gl_date_period_map
WHERE period_name <> not_assigned
AND period_set_name = x_period_set_name
AND period_type = x_period_type
AND accounting_date > gap_date;
select 'Duplicate'
from GL_LEDGERS LEDGERS
where LEDGERS.NAME = x_object_name
and LEDGERS.LEDGER_ID <> x_object_id
UNION
select 'Duplicate'
from GL_ACCESS_SETS ACCESS_SETS
where ACCESS_SETS.NAME = x_object_name
and ACCESS_SETS.AUTOMATICALLY_CREATED_FLAG <> 'Y'
UNION
select 'Duplicate'
from GL_LEDGER_RELATIONSHIPS
where target_ledger_category_code = 'ALC'
and relationship_type_code = 'BALANCE'
and target_ledger_name = x_object_name;
select 'Duplicate'
from GL_LEDGERS GL_LEDGERS
where GL_LEDGERS.SHORT_NAME = x_ledger_short_name
and GL_LEDGERS.LEDGER_ID <> x_ledger_id
UNION
select 'Duplicate'
from GL_LEDGER_RELATIONSHIPS
where target_ledger_category_code = 'ALC'
and relationship_type_code = 'BALANCE'
and target_ledger_short_name = x_ledger_short_name;
SELECT 'Duplicate'
FROM gl_ledger_relationships
WHERE application_id = 101
AND target_ledger_name = x_target_ledger_name
AND relationship_type_code <> 'NONE'
AND relationship_id <> x_relationship_id
AND target_ledger_id <> x_ledger_id
UNION
SELECT name
FROM GL_LEDGERS
WHERE name = x_target_ledger_name
AND ledger_id <> NVL(x_ledger_id,-1)
UNION
SELECT 'Duplicate'
FROM GL_ACCESS_SETS a
WHERE a.name = x_target_ledger_name
AND a.automatically_created_flag <> 'Y';
SELECT 'Duplicate'
FROM gl_ledger_relationships
WHERE application_id = 101
AND target_ledger_short_name = x_ledger_short_name
AND relationship_type_code <> 'NONE'
AND relationship_id <> x_relationship_id
AND target_ledger_id <> x_ledger_id
UNION
SELECT short_name
FROM gl_ledgers
WHERE short_name = x_ledger_short_name
AND ledger_id <> NVL(x_ledger_id,-1);
update gl_ledger_configurations
set completion_status_code = 'CONFIRMED'
where configuration_id = x_object_id;
update gl_ledgers
set complete_flag = 'Y'
where configuration_id = x_object_id
and ledger_category_code in ('PRIMARY', 'SECONDARY', 'ALC');
update gl_ledgers
set complete_flag = 'Y'
where ledger_id = x_object_id;
SELECT 'translated'
FROM DUAL
WHERE EXISTS(
SELECT 'X'
FROM gl_translation_tracking
WHERE ledger_id = x_ledger_id
AND actual_flag = 'A'
AND ((earliest_ever_period_year*10000)+earliest_ever_period_num) <
((earliest_never_period_year*10000)+earliest_never_period_num));
SELECT max(pr.end_date - pr.start_date)+1
FROM GL_PERIODS pr, GL_PERIOD_TYPES pty
WHERE pr.period_type = pty.period_type
AND ((pr.period_set_name, pr.period_type)
IN (SELECT period_set_name, accounted_period_type
FROM gl_ledgers where ledger_id = x_ledger_id))
GROUP BY pr.period_set_name, pty.user_period_type, pty.period_type
HAVING (max(pr.end_date - pr.start_date)+1) >=35;
SELECT object_id
FROM gl_ledger_config_details
WHERE configuration_id = x_config_id
AND object_type_code = 'LEGAL_ENTITY';
delete from gl_ledger_config_details
where configuration_id = x_config_id
and object_type_code = 'PRIMARY'
and setup_step_code = 'OU_SETUP';