[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 10/09/2003 W Chan Fix bug 3175882 - call security API on update |
| 10/09/2003 W Chan Fix bug 3175319 - create table partition when |
| register application |
| |
+======================================================================*/
-------------------------------------------------------------------------------
-- declaring private constants
-------------------------------------------------------------------------------
C_MANUAL CONSTANT VARCHAR2(10) := 'MANUAL';
| NAME: update_one_partition |
| Description: The procedure updates the partition to one partitioined |
| table. |
| |
+======================================================================*/
PROCEDURE update_one_partition
(p_app_id IN INTEGER
,p_app_short_name IN VARCHAR2
,p_schema IN VARCHAR2
,p_table IN VARCHAR2
,p_action IN VARCHAR2)
IS
--partition_exists EXCEPTION;
l_log_module := C_DEFAULT_MODULE||'.update_one_partition';
trace(p_msg => 'BEGIN of procedure update_one_partition',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
trace(p_msg => 'END of procedure update_one_partition',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
| NAME: update_partitions |
| Description: The procedure updates the partition to all partitioined |
| tables. |
| |
+======================================================================*/
PROCEDURE update_partitions
( p_app_id IN INTEGER
,p_action IN VARCHAR2
) IS
CURSOR c IS
SELECT application_short_name
FROM fnd_application
WHERE application_id = p_app_id;
l_log_module := C_DEFAULT_MODULE||'.update_partitions';
trace(p_msg => 'BEGIN of procedure update_partitions',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
update_one_partition
(p_app_id => p_app_id
,p_app_short_name => l_app_short_name
,p_schema => l_schema
,p_table => 'XLA_TRANSACTION_ENTITIES'
,p_action => p_action);
update_one_partition
(p_app_id => p_app_id
,p_app_short_name => l_app_short_name
,p_schema => l_schema
,p_table => 'XLA_EVENTS'
,p_action => p_action);
update_one_partition
(p_app_id => p_app_id
,p_app_short_name => l_app_short_name
,p_schema => l_schema
,p_table => 'XLA_AE_HEADERS'
,p_action => p_action);
update_one_partition
(p_app_id => p_app_id
,p_app_short_name => l_app_short_name
,p_schema => l_schema
,p_table => 'XLA_AE_LINES'
,p_action => p_action);
update_one_partition
(p_app_id => p_app_id
,p_app_short_name => l_app_short_name
,p_schema => l_schema
,p_table => 'XLA_DISTRIBUTION_LINKS'
,p_action => p_action);
trace(p_msg => 'END of procedure update_partitions',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
| NAME: insert_tpm |
| Description: The procedure inserts special event information for |
| third party merge. |
| |
+======================================================================*/
PROCEDURE insert_tpm
(p_application_id IN NUMBER
,p_creation_date IN DATE
,p_created_by IN NUMBER
,p_last_update_date IN DATE
,p_last_updated_by IN NUMBER
,p_last_update_login IN NUMBER)IS
l_row_id VARCHAR2(80);
l_log_module := C_DEFAULT_MODULE||'.update_one_partition';
trace(p_msg => 'BEGIN of procedure update_one_partition',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_entity_types_f_pkg.insert_row(
x_rowid => l_row_id
,x_application_id => p_application_id
,x_entity_code => C_TPM
,x_enabled_flag => 'Y'
,x_enable_gapless_events_flag => 'N'
,x_name => 'Third Party Merge'
,x_description => 'Special event entity created for third party merge entry'
,x_creation_date => p_creation_date
,x_created_by => p_created_by
,x_last_update_date => p_last_update_date
,x_last_updated_by => p_last_updated_by
,x_last_update_login => p_last_update_login);
INSERT INTO xla_entity_id_mappings
(application_id
,entity_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,C_TPM
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login);
xla_event_classes_f_pkg.insert_row(
x_rowid => l_row_id
,x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM
,x_enabled_flag => 'Y'
,x_name => 'Third Party Merge'
,x_description => 'Special event class created for third party merge entry'
,x_creation_date => p_creation_date
,x_created_by => p_created_by
,x_last_update_date => p_last_update_date
,x_last_updated_by => p_last_updated_by
,x_last_update_login => p_last_update_login);
xla_event_types_f_pkg.insert_row(
x_rowid => l_row_id
,x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM
,x_event_type_code => 'FULL_MERGE'
,x_accounting_flag => 'Y'
,x_tax_flag => 'N'
,x_enabled_flag => 'Y'
,x_name => 'Full Merge'
,x_description => 'Special event type created for third party merge entry'
,x_creation_date => p_creation_date
,x_created_by => p_created_by
,x_last_update_date => p_last_update_date
,x_last_updated_by => p_last_updated_by
,x_last_update_login => p_last_update_login);
xla_event_types_f_pkg.insert_row(
x_rowid => l_row_id
,x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM
,x_event_type_code => 'PARTIAL_MERGE'
,x_accounting_flag => 'Y'
,x_tax_flag => 'N'
,x_enabled_flag => 'Y'
,x_name => 'Partial Merge'
,x_description => 'Special event type created for third party merge entry'
,x_creation_date => p_creation_date
,x_created_by => p_created_by
,x_last_update_date => p_last_update_date
,x_last_updated_by => p_last_updated_by
,x_last_update_login => p_last_update_login);
xla_event_class_grps_f_pkg.insert_row(
x_rowid => l_row_id
,x_application_id => p_application_id
,x_event_class_group_code => C_TPM
,x_enabled_flag => 'Y'
,x_name => 'Third Party Merge'
,x_description => 'Special event class group created for third party merge entry'
,x_creation_date => p_creation_date
,x_created_by => p_created_by
,x_last_update_date => p_last_update_date
,x_last_updated_by => p_last_updated_by
,x_last_update_login => p_last_update_login);
xla_event_class_attrs_f_pkg.insert_row(
x_rowid => l_row_id
,x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM
,x_event_class_group_code => C_TPM
,x_je_category_name => 'Other'
,x_reporting_view_name => null
,x_allow_actuals_flag => 'Y'
,x_allow_budgets_flag => 'N'
,x_allow_encumbrance_flag => 'N'
,x_calculate_acctd_amts_flag => 'N'
,x_calculate_g_l_amts_flag => 'N'
,x_creation_date => p_creation_date
,x_created_by => p_created_by
,x_last_update_date => p_last_update_date
,x_last_updated_by => p_last_updated_by
,x_last_update_login => p_last_update_login);
trace(p_msg => 'END of procedure insert_tpm',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
END insert_tpm;
| NAME: insert_tpm |
| Description: The procedure deletes special event information for |
| third party merge. |
| |
+======================================================================*/
PROCEDURE delete_tpm
(p_application_id IN NUMBER)
IS
BEGIN
xla_event_class_grps_f_pkg.delete_row
(x_application_id => p_application_id
,x_event_class_group_code => C_TPM);
xla_event_class_attrs_f_pkg.delete_row
(x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM);
xla_event_types_f_pkg.delete_row
(x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM
,x_event_type_code => 'FULL_MERGE');
xla_event_types_f_pkg.delete_row
(x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM
,x_event_type_code => 'PARTIAL_MERGE');
DELETE FROM xla_entity_id_mappings
WHERE application_id = p_application_id
AND entity_code = C_TPM
;
xla_event_classes_f_pkg.delete_row
(x_application_id => p_application_id
,x_entity_code => C_TPM
,x_event_class_code => C_TPM);
xla_entity_types_f_pkg.delete_row
(x_application_id => p_application_id
,x_entity_code => C_TPM);
END delete_tpm;
| Procedure insert_row |
| |
+======================================================================*/
PROCEDURE insert_row
(x_rowid IN OUT NOCOPY VARCHAR2
,x_application_id IN NUMBER
,x_application_type_code IN VARCHAR2
,x_je_source_name IN VARCHAR2
,x_valuation_method_flag IN VARCHAR2
,x_drilldown_procedure_name IN VARCHAR2
,x_security_function_name IN VARCHAR2
,x_control_account_type_code IN VARCHAR2
,x_alc_enabled_flag IN VARCHAR2
,x_creation_date IN DATE
,x_created_by IN NUMBER
,x_last_update_date IN DATE
,x_last_updated_by IN NUMBER
,x_last_update_login IN NUMBER)
IS
CURSOR c IS
SELECT rowid
FROM xla_subledgers
WHERE application_id = x_application_id;
SELECT rowid
FROM xla_entity_id_mappings
WHERE application_id = x_application_id AND
entity_code = C_MANUAL;
SELECT application_short_name
FROM fnd_application
WHERE application_id = x_application_id;
l_log_module := C_DEFAULT_MODULE||'.insert_row';
trace(p_msg => 'BEGIN of procedure insert_row',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
INSERT INTO xla_subledgers
(creation_date
,created_by
,application_id
,application_type_code
,je_source_name
,valuation_method_flag
,drilldown_procedure_name
,security_function_name
,control_account_type_code
,alc_enabled_flag
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(x_creation_date
,x_created_by
,x_application_id
,x_application_type_code
,x_je_source_name
,x_valuation_method_flag
,x_drilldown_procedure_name
,x_security_function_name
,x_control_account_type_code
,x_alc_enabled_flag
,x_last_update_date
,x_last_updated_by
,x_last_update_login);
xla_entity_types_f_pkg.insert_row(
x_rowid => l_event_entity_row_id
,x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_enabled_flag => 'Y'
,x_enable_gapless_events_flag => 'N'
,x_name => 'Manual'
,x_description => 'Special event entity created for manual entry'
,x_creation_date => x_creation_date
,x_created_by => x_created_by
,x_last_update_date => x_last_update_date
,x_last_updated_by => x_last_updated_by
,x_last_update_login => x_last_update_login);
INSERT INTO xla_entity_id_mappings
(application_id
,entity_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(x_application_id
,C_MANUAL
,x_creation_date
,x_created_by
,x_last_update_date
,x_last_updated_by
,x_last_update_login);
xla_event_classes_f_pkg.insert_row(
x_rowid => l_event_class_row_id
,x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_event_class_code => C_MANUAL
,x_enabled_flag => 'Y'
,x_name => 'Manual'
,x_description => 'Special event class created for manual entry'
,x_creation_date => x_creation_date
,x_created_by => x_created_by
,x_last_update_date => x_last_update_date
,x_last_updated_by => x_last_updated_by
,x_last_update_login => x_last_update_login);
xla_event_types_f_pkg.insert_row(
x_rowid => l_event_type_row_id
,x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_event_class_code => C_MANUAL
,x_event_type_code => C_MANUAL
,x_accounting_flag => 'Y'
,x_tax_flag => 'Y'
,x_enabled_flag => 'Y'
,x_name => 'Manual'
,x_description => 'Special event type created for manual entry'
,x_creation_date => x_creation_date
,x_created_by => x_created_by
,x_last_update_date => x_last_update_date
,x_last_updated_by => x_last_updated_by
,x_last_update_login => x_last_update_login);
xla_event_class_grps_f_pkg.insert_row(
x_rowid => l_event_class_grps_row_id
,x_application_id => x_application_id
,x_event_class_group_code => C_MANUAL
,x_enabled_flag => 'Y'
,x_name => 'Manual'
,x_description => 'Special event class group created for manual entry'
,x_creation_date => x_creation_date
,x_created_by => x_created_by
,x_last_update_date => x_last_update_date
,x_last_updated_by => x_last_updated_by
,x_last_update_login => x_last_update_login);
xla_event_class_attrs_f_pkg.insert_row(
x_rowid => l_event_class_attr_row_id
,x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_event_class_code => C_MANUAL
,x_event_class_group_code => C_MANUAL
,x_je_category_name => 'Other'
,x_reporting_view_name => null
,x_allow_actuals_flag => 'Y'
,x_allow_budgets_flag => 'Y'
,x_allow_encumbrance_flag => 'Y'
,x_calculate_acctd_amts_flag => 'N'
,x_calculate_g_l_amts_flag => 'N'
,x_creation_date => x_creation_date
,x_created_by => x_created_by
,x_last_update_date => x_last_update_date
,x_last_updated_by => x_last_updated_by
,x_last_update_login => x_last_update_login);
insert_tpm(
p_application_id => x_application_id
,p_creation_date => x_creation_date
,p_created_by => x_created_by
,p_last_update_date => x_last_update_date
,p_last_updated_by => x_last_updated_by
,p_last_update_login => x_last_update_login);
update_partitions(p_app_id => x_application_id
,p_action => 'ADD');
trace(p_msg => 'END of procedure insert_row',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_subledgers_f_pkg.insert_row');
END insert_row;
SELECT application_id
,application_type_code
,je_source_name
,valuation_method_flag
,drilldown_procedure_name
,security_function_name
,control_account_type_code
,alc_enabled_flag
FROM xla_subledgers
WHERE application_id = x_application_id
FOR UPDATE OF application_id NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
| Procedure update_row |
| |
+======================================================================*/
PROCEDURE update_row
(x_application_id IN NUMBER
,x_je_source_name IN VARCHAR2
,x_valuation_method_flag IN VARCHAR2
,x_drilldown_procedure_name IN VARCHAR2
,x_security_function_name IN VARCHAR2
,x_control_account_type_code IN VARCHAR2
,x_alc_enabled_flag IN VARCHAR2
,x_last_update_date IN DATE
,x_last_updated_by IN NUMBER
,x_last_update_login IN NUMBER)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_row';
trace(p_msg => 'BEGIN of procedure update_row',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
UPDATE xla_subledgers
SET
last_update_date = x_last_update_date
,application_id = x_application_id
,je_source_name = x_je_source_name
,valuation_method_flag = x_valuation_method_flag
,drilldown_procedure_name = x_drilldown_procedure_name
,security_function_name = x_security_function_name
,control_account_type_code = x_control_account_type_code
,alc_enabled_flag = x_alc_enabled_flag
,last_updated_by = x_last_updated_by
,last_update_login = x_last_update_login
WHERE application_id = x_application_id
;
update_partitions(p_app_id => x_application_id
,p_action => 'ADD');
trace(p_msg => 'END of procedure update_row',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
END update_row;
| Procedure delete_row |
| |
+======================================================================*/
PROCEDURE delete_row
(x_application_id IN NUMBER)
IS
CURSOR c2 IS
SELECT application_short_name
FROM fnd_application
WHERE application_id = x_application_id;
l_log_module := C_DEFAULT_MODULE||'.delete_row';
trace(p_msg => 'BEGIN of procedure delete_row',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
xla_event_class_grps_f_pkg.delete_row
(x_application_id => x_application_id
,x_event_class_group_code => C_MANUAL);
xla_event_class_attrs_f_pkg.delete_row
(x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_event_class_code => C_MANUAL);
xla_event_types_f_pkg.delete_row
(x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_event_class_code => C_MANUAL
,x_event_type_code => C_MANUAL);
DELETE FROM xla_entity_id_mappings
WHERE application_id = x_application_id
AND entity_code = C_MANUAL
;
xla_event_classes_f_pkg.delete_row
(x_application_id => x_application_id
,x_entity_code => C_MANUAL
,x_event_class_code => C_MANUAL);
xla_entity_types_f_pkg.delete_row
(x_application_id => x_application_id
,x_entity_code => C_MANUAL);
delete_tpm
(p_application_id => x_application_id);
DELETE FROM xla_subledgers
WHERE application_id = x_application_id;
update_partitions(p_app_id => x_application_id
,p_action => 'DROP');
trace(p_msg => 'END of procedure delete_row',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_subledgers_f_pkg.delete_row');
END delete_row;
,p_last_update_date IN VARCHAR2)
IS
CURSOR c_app_id IS
SELECT application_id
FROM fnd_application
WHERE application_short_name = p_application_short_name;
SELECT je_source_name
FROM gl_je_sources
WHERE je_source_key = p_je_source_name;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT last_updated_by, last_update_date
INTO db_luby, db_ludate
FROM xla_subledgers
WHERE application_id = l_application_id;
xla_subledgers_f_pkg.update_row
(x_application_id => l_application_id
,x_je_source_name => l_je_source_name
,x_valuation_method_flag => p_valuation_method_flag
,x_drilldown_procedure_name => p_drilldown_procedure_name
,x_security_function_name => p_security_function_name
,x_control_account_type_code => p_control_account_type_code
,x_alc_enabled_flag => p_alc_enabled_flag
,x_last_update_date => f_ludate
,x_last_updated_by => f_luby
,x_last_update_login => 0);
xla_subledgers_f_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_application_id
,x_application_type_code => p_application_type_code
,x_je_source_name => l_je_source_name
,x_valuation_method_flag => p_valuation_method_flag
,x_drilldown_procedure_name => p_drilldown_procedure_name
,x_security_function_name => p_security_function_name
,x_control_account_type_code => p_control_account_type_code
,x_alc_enabled_flag => p_alc_enabled_flag
,x_creation_date => f_ludate
,x_created_by => f_luby
,x_last_update_date => f_ludate
,x_last_updated_by => f_luby
,x_last_update_login => 0);
UPDATE xla_product_rules_b
SET compile_status_code = 'N'
WHERE application_id = l_application_id;
UPDATE xla_prod_acct_headers
SET validation_status_code = 'N'
WHERE application_id = l_application_id;
UPDATE xla_line_definitions_b
SET validation_status_code = 'N'
WHERE application_id = l_application_id;