[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| INSERT_LEDGER_OPTIONS to set correct default |
| for the option 'General Ledger Journal Entry |
| Summarization' |
| 28-Sep-03 S. Singhania Made changes for enhancing the package to |
| include APIs for event_class setups (3151792)|
| - Added global variables and the API |
| SET_DEFAULT_VALUES |
| - Added APIs PERFORM_EVENT_CLASS_SETUP and |
| DELETE_EVENT_CLASS_SETUP |
| - Modified following procedures: |
| INSERT_JE_CATEGORY, SETUP_OPTIONS |
| Minor changes in following procedures: |
| SETUP_LEDGER_OPTIONS, INSERT_LAUNCH_OPTIONS, |
| INSERT_LEDGER_OPTIONS |
| 18-Nov-03 S. Singhania Changed the default values for 'g_porcesses' |
| and 'g_processing_unit_size' to 1 and 1000 |
| respectively in routine SET_DEFAULT_VALUES. |
| (Bug # 3259247). |
| 10-Dec-03 S. Singhania Added the API PERFORM_APPLICATION_SETUP_CP for |
| the concurrent program. (Bug 3229146). |
| 17-Jun-04 S. Singhania Added UPGRADE_LEDGER_OPTIONS API for AX upgrade|
| 17-JUN-04 S. Singhania Fixed GSCC warnings for File.Sql.35 |
| 18-JUN-04 S. Singhania Added more validations to the API |
| UPGRADE_LEDGER_OPTIONS |
| 01-NOV-04 S. Singhania Made changes for Valuation Method Enhancements:|
| - Added g_capture_event_flag |
| - Modified SET_DEFAULT_VALUES, SETUP_OPTIONS,|
| INSERT_LEDGER_OPTIONS and |
| PERFORM_APPLICATION_SETUP_CP |
| 19-Aug-05 V.Swapna Removed alc_enabled_flag(bug #4364830) |
| 24-JUL-2007 Jorge Larre Bug 5582560 |
| The program loops on applications and sets the global variable |
| g_capture_event_flag based on the valuation method. This is done in a |
| simple IF with no else clause for the other case, so after the first |
| application meets the condition, the global variable remains set for |
| all the other applications. Solution: add an ELSE clause to the IF. |
+===========================================================================*/
--=============================================================================
-- **************** declaraions ********************
--=============================================================================
g_accounting_mode_code VARCHAR2(1);
PROCEDURE insert_launch_options
(p_ledger_id IN NUMBER
,p_application_id IN NUMBER);
PROCEDURE insert_ledger_options
(p_ledger_id IN NUMBER
,p_application_id IN NUMBER);
PROCEDURE insert_je_category
(p_ledger_id IN NUMBER
,p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2);
SELECT application_id
,valuation_method_flag
FROM xla_subledgers;
SELECT chart_of_accounts_id, sla_accounting_method_code, sla_accounting_method_type
FROM xla_gl_ledgers_v
WHERE ledger_id = p_ledger_id;
SELECT transaction_coa_id
FROM xla_acctg_methods_b
WHERE accounting_method_type_code = l_ledger.sla_accounting_method_type
AND accounting_method_code = l_ledger.sla_accounting_method_code;
SELECT chart_of_accounts_id
FROM xla_gl_ledgers_v
WHERE ledger_id = p_primary_ledger_id;
SELECT xlr.ledger_id ledger_id
,xlr.primary_ledger_id primary_ledger_id
,DECODE(xlo.ledger_id,NULL,'N','Y') ledger_setup_flag
FROM xla_ledger_relationships_v xlr
,xla_ledger_options xlo
WHERE xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
AND xlr.sla_accounting_method_code IS NOT NULL
AND xlo.application_id(+) = p_application_id
AND xlo.ledger_id (+) = xlr.ledger_id
ORDER BY xlr.ledger_category_code;
SELECT valuation_method_flag
INTO l_valutation_method_flag
FROM xla_subledgers
WHERE application_id = p_application_id;
insert_je_category
(p_ledger_id => c1.ledger_id
,p_application_id => p_application_id
,p_event_class_code => p_event_class_code);
PROCEDURE delete_event_class_setup
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2) IS
BEGIN
trace('> xla_acct_setup_pkg.delete_event_class_setup' , 10);
DELETE FROM xla_je_categories
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code;
trace('Number of rows deleted = '||SQL%ROWCOUNT , 40);
trace('< xla_acct_setup_pkg.delete_event_class_setup' , 10);
(p_location => 'xla_acct_setup_pkg.delete_event_class_setup');
END delete_event_class_setup;
SELECT application_id
,application_name
FROM xla_subledgers_fvl
WHERE application_id = NVL(p_application_id,application_id);
DELETE
FROM xla_je_categories xjc
WHERE application_id = c1.application_id
AND NOT EXISTS
(SELECT 1
FROM xla_event_classes_b
WHERE application_id = xjc.application_id
AND event_class_code = xjc.event_class_code);
trace('Number of rows deleted = '||SQL%ROWCOUNT,30);
trace('Inserting rows in xla_launch_options for the ledgers '||
'that are not already setup',20);
INSERT INTO xla_launch_options
(application_id
,ledger_id
,accounting_mode_code
,accounting_mode_override_flag
,summary_report_flag
,summary_report_override_flag
,submit_transfer_to_gl_flag
,submit_transfer_override_flag
,submit_gl_post_flag
,submit_gl_post_override_flag
,error_limit
,processes
,processing_unit_size
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
(SELECT DISTINCT
c1.application_id
,xlr.ledger_id
,g_accounting_mode_code
,g_acctg_mode_override_flag
,g_summary_report_flag
,g_summary_report_override_flag
,g_submit_transfer_to_gl_flag
,g_submit_xfer_override_flag
,g_submit_gl_post_flag
,g_submit_gl_post_override_flag
,g_error_limit
,g_processes
,g_processing_unit_size
,l_sysdate
,xla_environment_pkg.g_usr_id
,l_sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
FROM
xla_ledger_relationships_v xlr
,xla_subledgers xsl
,xla_acctg_methods_b xam
,gl_ledgers gll
WHERE
xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
AND xlr.sla_accounting_method_code IS NOT NULL
AND xsl.application_id = c1.application_id
AND xlr.ledger_category_code = DECODE(xsl.valuation_method_flag
,'N','PRIMARY'
,'Y',xlr.ledger_category_code)
AND xam.accounting_method_code = xlr.sla_accounting_method_code
AND xam.accounting_method_type_code = xlr.sla_accounting_method_type
AND gll.ledger_id = xlr.primary_ledger_id
AND NVL(xam.transaction_coa_id
,gll.chart_of_accounts_id) = gll.chart_of_accounts_id
AND NOT EXISTS (SELECT 1
FROM xla_launch_options
WHERE ledger_id = xlr.ledger_id
AND application_id = xsl.application_id));
trace('Number of rows inserted = '||SQL%ROWCOUNT,30);
trace('Inserting rows in xla_ledger_options for the ledgers '||
'that are not already setup',20);
INSERT INTO xla_ledger_options
(application_id
,ledger_id
,transfer_to_gl_mode_code
,acct_reversal_option_code
,capture_event_flag
,rounding_rule_code
,enabled_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
--,merge_acct_option_code
)
(SELECT DISTINCT
c1.application_id
,xlr.ledger_id
,decode(gl2.net_income_code_combination_id,NULL,'P','A')
,g_acct_reversal_option_code
,DECODE(xsl.valuation_method_flag
,'Y','Y'
,DECODE(xlr.ledger_category_code
,'PRIMARY', 'Y'
,'N')
)
,'NEAREST'
,g_enabled_flag
,l_sysdate
,xla_environment_pkg.g_usr_id
,l_sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
--,'NONE'
FROM
xla_ledger_relationships_v xlr
,xla_subledgers xsl
,xla_acctg_methods_b xam
,gl_ledgers gll
,gl_ledgers gl2
WHERE
xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
AND xlr.sla_accounting_method_code IS NOT NULL
AND xsl.application_id = c1.application_id
AND xam.accounting_method_code = xlr.sla_accounting_method_code
AND xam.accounting_method_type_code = xlr.sla_accounting_method_type
AND gll.ledger_id = xlr.primary_ledger_id
AND gl2.ledger_id = xlr.ledger_id
AND NVL(xam.transaction_coa_id
,gll.chart_of_accounts_id) = gll.chart_of_accounts_id
AND NOT EXISTS (SELECT 1
FROM xla_ledger_options
WHERE ledger_id = xlr.ledger_id
AND application_id = xsl.application_id));
trace('Number of rows inserted = '||SQL%ROWCOUNT,30);
trace('Inserting rows in xla_je_categories for the event classes and ledgers '||
'that are not already setup',20);
INSERT INTO xla_je_categories
(application_id
,ledger_id
,entity_code
,event_class_code
,je_category_name
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
(SELECT DISTINCT
c1.application_id
,xlr.ledger_id
,xec.entity_code
,xec.event_class_code
,xec.je_category_name
,l_sysdate
,xla_environment_pkg.g_usr_id
,l_sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
FROM
xla_ledger_relationships_v xlr
,xla_subledgers xsl
,xla_acctg_methods_b xam
,gl_ledgers gll
,xla_event_class_attrs xec
WHERE
xlr.ledger_category_code IN ('PRIMARY','SECONDARY')
AND xlr.sla_accounting_method_code IS NOT NULL
AND xsl.application_id = c1.application_id
AND xam.accounting_method_code = xlr.sla_accounting_method_code
AND xam.accounting_method_type_code = xlr.sla_accounting_method_type
AND gll.ledger_id = xlr.primary_ledger_id
AND NVL(xam.transaction_coa_id
,gll.chart_of_accounts_id) = gll.chart_of_accounts_id
AND xec.application_id = xsl.application_id
AND NOT EXISTS (SELECT 1
FROM xla_je_categories
WHERE application_id = xsl.application_id
AND ledger_id = xlr.ledger_id
AND entity_code = xec.entity_code
AND event_class_code = xec.event_class_code));
trace('Number of rows inserted = '||SQL%ROWCOUNT,30);
PROCEDURE insert_launch_options
(p_ledger_id IN NUMBER
,p_application_id IN NUMBER) IS
l_exist VARCHAR2(1);
SELECT 'x'
FROM xla_launch_options
WHERE application_id = p_application_id
AND ledger_id = p_ledger_id;
trace('> xla_acct_setup_pkg.insert_launch_options' , 10);
INSERT INTO xla_launch_options
(application_id
,ledger_id
,accounting_mode_code
,accounting_mode_override_flag
,summary_report_flag
,summary_report_override_flag
,submit_transfer_to_gl_flag
,submit_transfer_override_flag
,submit_gl_post_flag
,submit_gl_post_override_flag
,error_limit
,processes
,processing_unit_size
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,p_ledger_id
,g_accounting_mode_code
,g_acctg_mode_override_flag
,g_summary_report_flag
,g_summary_report_override_flag
,g_submit_transfer_to_gl_flag
,g_submit_xfer_override_flag
,g_submit_gl_post_flag
,g_submit_gl_post_override_flag
,g_error_limit
,g_processes
,g_processing_unit_size
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id);
trace('< xla_acct_setup_pkg.insert_launch_options' , 10);
(p_location => 'xla_acct_setup_pkg.insert_launch_options');
END insert_launch_options;
PROCEDURE insert_ledger_options
(p_ledger_id IN NUMBER
,p_application_id IN NUMBER) IS
l_exist VARCHAR2(1);
SELECT 'x'
FROM xla_ledger_options
WHERE application_id = p_application_id
AND ledger_id = p_ledger_id;
trace('> xla_acct_setup_pkg.insert_ledger_options' , 10);
SELECT decode(net_income_code_combination_id,NULL,'P','A')
INTO g_transfer_to_gl_mode_code
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
INSERT INTO xla_ledger_options
(application_id
,ledger_id
,transfer_to_gl_mode_code
,acct_reversal_option_code
,capture_event_flag
,rounding_rule_code
,enabled_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
--,merge_acct_option_code
)
VALUES
(p_application_id
,p_ledger_id
,g_transfer_to_gl_mode_code
,g_acct_reversal_option_code
,g_capture_event_flag
,'NEAREST'
,g_enabled_flag
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
--,'NONE'
);
trace('< xla_acct_setup_pkg.insert_ledger_options' , 10);
(p_location => 'xla_acct_setup_pkg.insert_ledger_options');
END insert_ledger_options;
PROCEDURE insert_je_category
(p_ledger_id IN NUMBER
,p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2) IS
CURSOR csr_event_classes IS
SELECT xeca.entity_code
,xeca.event_class_code
,xeca.je_category_name
FROM xla_event_class_attrs xeca
WHERE xeca.application_id = p_application_id
AND xeca.event_class_code = NVL(p_event_class_code,xeca.event_class_code)
AND xeca.event_class_code NOT IN
(SELECT event_class_code
FROM xla_je_categories xjc
WHERE xjc.application_id = p_application_id
AND xjc.ledger_id = p_ledger_id);
trace('> xla_acct_setup_pkg.insert_je_category' , 10);
INSERT INTO xla_je_categories
(application_id
,ledger_id
,entity_code
,event_class_code
,je_category_name
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,p_ledger_id
,c1.entity_code
,c1.event_class_code
,c1.je_category_name
,sysdate
,xla_environment_pkg.g_usr_id
,sysdate
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id);
trace('< xla_acct_setup_pkg.insert_je_category' , 10);
(p_location => 'xla_acct_setup_pkg.insert_je_category');
END insert_je_category;
SELECT 'x'
FROM xla_launch_options
WHERE application_id = p_application_id
AND ledger_id = p_primary_ledger_id;
SELECT currency_code
FROM xla_gl_ledgers_v
WHERE ledger_id = p_ledger_id;
insert_launch_options
(p_ledger_id => p_ledger_id
,p_application_id => p_application_id);
insert_ledger_options
(p_ledger_id => p_ledger_id
,p_application_id => p_application_id);
insert_je_category
(p_ledger_id => p_ledger_id
,p_application_id => p_application_id
,p_event_class_code => p_event_class_code);
insert_launch_options
(p_ledger_id => p_ledger_id
,p_application_id => p_application_id);
insert_ledger_options
(p_ledger_id => p_ledger_id
,p_application_id => p_application_id);
insert_je_category
(p_ledger_id => p_ledger_id
,p_application_id => p_application_id
,p_event_class_code => p_event_class_code);
UPDATE xla_launch_options SET
accounting_mode_code = NVL(p_acct_mode_code, accounting_mode_code)
,accounting_mode_override_flag = NVL(p_acct_mode_override_flag, accounting_mode_override_flag)
,summary_report_flag = NVL(p_summary_report_flag, summary_report_flag)
,summary_report_override_flag = NVL(p_summary_report_override_flag, summary_report_override_flag)
,submit_transfer_to_gl_flag = NVL(p_submit_xfer_to_gl_flag, submit_transfer_to_gl_flag)
,submit_transfer_override_flag = NVL(p_submit_xfer_override_flag, submit_transfer_override_flag)
,submit_gl_post_flag = NVL(p_submit_gl_post_flag, submit_gl_post_flag)
,submit_gl_post_override_flag = NVL(p_submit_gl_post_override_flag,submit_gl_post_override_flag)
,error_limit = DECODE(p_stop_on_error, null, error_limit, p_error_limit)
,processes = NVL(p_processes, processes)
,processing_unit_size = NVL(p_processing_unit_size, processing_unit_size)
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
WHERE ledger_id = p_ledger_id
AND application_id = p_application_id;
UPDATE xla_ledger_options SET
transfer_to_gl_mode_code = NVL(p_transfer_to_gl_mode_code, transfer_to_gl_mode_code)
,acct_reversal_option_code = NVL(p_acct_reversal_option_code, acct_reversal_option_code)
,last_update_date = sysdate
,last_updated_by = xla_environment_pkg.g_usr_id
,last_update_login = xla_environment_pkg.g_login_id
WHERE ledger_id = p_ledger_id
AND application_id = p_application_id;