The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 11-Dec-02 K. Boussema Updated function GetBaseLedgerId |
| 19-Dec-02 S. Singhania Fixed the bug # 2701293. Added new sources to|
| the cache. Added set_process_cache. |
| 06-Jan-03 S. Singhania Made changes due to change in column names of|
| ledger view XLA_ALT_CURR_LEDGERS_V |
| 08-Jan-03 K. Boussema Update GetTranslatedValueChar to get |
| XLA_NLS_DESC_LANGUAGE and |
| XLA_ACCT_REVERSAL_OPTION values |
| 16-Jan-03 S. Singhania Made changes due to changes in the ledger |
| view XLA_ALT_CURR_LEDGERS_V |
| 21-Feb-03 S. Singhania Made changes for the new bulk approach of the|
| accounting program |
| - added 'p_max_event_date' param to |
| load_application_ledgers |
| - added procedure 'get_pad_info' |
| - removed datatypes to cache event |
| information |
| - merged ledger cahce structures |
| - formatting. |
| 04-Apr-03 S. Singhania rewrote the APIs and Modified the specs for: |
| - GetValueNum |
| - GetValueDate |
| - GetValueChar |
| Made changes due to amb_context_code and new |
| sources. Please refer to bug # 2887554 |
| 02-May-03 S. Singhania Added section to initilize variables under |
| LOAD_APPLICATION_LEDGERS |
| Added 'allow_intercompany_post_flag' to the |
| cache (bug # 2922615) |
| 03-May-03 S. Singhania Added more exception handlers for debugging |
| 07-May-03 S. Singhania Based on requirements from the 'Accounting |
| Engine' remodified the specifications for: |
| - GetValueNum |
| - GetValueDate |
| - GetValueChar |
| - load_application_ledgers |
| - GetAlcLedgers |
| Modified code to support new specifications |
| Renamed 'GetBaseLedgers' to 'GetLedgers' |
| Modified the structure of cache to handle ALC|
| as there will not be any ALC for secondary |
| Added local 'Trace' package |
| 08-May-03 S. Singhania Modified ALC cache structure and alc cursor |
| to include SLA_LEDGER_ID as attribute of |
| ALC ledgers.(bug # 2948635) |
| 12-Jun-03 S. Singhania Added trace messages in GET_PAD_INFO |
| Added correct calls to FND Messages (bug # |
| 3001156) |
| 25-Jun-03 S. Singhania Modified the package to use FND_LOG. |
| 16-Jul-03 S. Singhania Added following APIs: |
| - GetValueNum (Overloaded) |
| - GetValueDate (Overloaded) |
| - GetValueChar (Overloaded) |
| - GetSessionValueChar |
| - GetSessionValueChar (Overloaded) |
| - get_event_info |
| Added following internal routines |
| - load_system_sources |
| - is_source_valid |
| Modified specifications for: |
| - GetValueChar |
| - Get_PAD_info |
| Modified the cache structures. |
| 21-Jul-03 S. Singhania Added NVL in GET_PAD_INFO for date comparison|
| modified the where clause for csr_ledger_pad |
| to select all pads before event's max date |
| (bug # 3036628) |
| 25-Jul-03 S. Singhania Modified LOAD_APPLICATION_LEDGERS to reduce |
| code maintenance. |
| 01-Aug-03 S. Singhania Enabled the validation in IS_SOURCE_VALID to |
| make sure the system source code is defined|
| in AMB. |
| 11-Sep-03 S. Singhania Made changes to cache je_category (# 3109690)|
| - Modified the structures that store 'event|
| class' and 'event type' info. |
| - Modified CACHE_APPLICATION_SETUP to cache|
| je_categories for event_class/ledger. |
| - Added API GET_JE_CATEGORY |
| 21-Nov-03 S. Singhania Added new system source (bug # 3264446) |
| DYNAMIC_INSERTS_ALLOWED_FLAG. |
| 22-Dec-03 S. Singhania Made changes for the FND_LOG. |
| Added the condition in cursor csr_base_ledger|
| in LOAD_APPLICATION_LEDGERS to check if |
| relationship is enabled in configurations. |
| 06-Jan-04 S. Singhania Further FND_LOG changes. |
| 16-Feb-04 S. Singhania Bug 3443779. Cached ledger_category_code for |
| ALC ledgers. |
| 18-Mar-04 S. Singhania Added a parameter p_module to the TRACE calls|
| and the procedure. |
| 20-Sep-04 S. Singhania Added the following to support bulk changes |
| in the accounting engine |
| - Added API GetArrayPad |
| 01-NOV-04 S. Singhania Made changes for Valuation Method |
| .Enhancements: |
| - Modified LOAD_APPLICATION_LEDGERS |
| Fixed GSCC warning File.Sql.35 |
| 9-Mar-05 W. Shen Add the function BuildLedgerArray and |
| GetLedgerArray to support the calculation |
| of rounding |
| Add several field to the cache too |
| XLA_ALC_ENABLED_FLAG |
| XLA_ROUNDING_CCID |
| XLA_INHERIT_CONVERSION_TYPE |
| XLA_DEFAULT_CONV_RATE_TYPE |
| XLA_MAX_DAYS_ROLL_RATE |
| XLA_CURRENCY_MAU |
| XLA_ROUNDING_RULE_CODE |
| 26-May-05 W. Shen Add the function GetCurrencyMau |
| 27-May-05 W. Chan Fix bug 4161247 - Add following to cache: |
| 1. transaction_calendar_id |
| 2. enable_average_balances_flag |
| 3. effective_date_rule_code |
| 20-Jun-05 W. Shen Fix bug 4444191, add ledger name for alc |
| 5-Jul-05 W. Shen Fix bug 4476180, treat the flag |
| 'ALC_INHERIT_CONVERSION_TYPE' as 'Y' when |
| it is null |
| 17-Aug-05 V. Swapna Fix bug 4554935, modified |
| cursor csr_je_category |
| 01-Dec-05 S. Singhania Bug 4640689. Modified cursors: |
| csr_base_ledger and csr_alc_ledger |
| to get right value for sla_ledger_id |
| 24-Jan-06 V. Swapna Fix bug 4736579. Added an exception |
| in get_je_category procedure. |
| 02-Mar-06 V. Swapna Bug 5018098: Added an exception in |
| load_application_ledgers procedure. |
+===========================================================================*/
--=============================================================================
-- **************** declarations ********************
--=============================================================================
-------------------------------------------------------------------------------
-- declaring data types
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--
-------------------------------------------------------------------------------
TYPE t_array_char_value IS TABLE OF VARCHAR2(240) INDEX BY VARCHAR2(30);
(SELECT fat.application_name application_name
,fat.application_id application_id
,gjs.user_je_source_name user_je_source_name
,xso.je_source_name je_source_name
,xso.name ledger_name
,xso.ledger_id ledger_id
,fst.id_flex_structure_name ledger_coa_name
,fsv.id_flex_structure_name session_coa_name
,fsv.dynamic_inserts_allowed_flag dynamic_inserts_allowed_flag
,xso.chart_of_accounts_id coa_id
,amt.name ledger_slam_name
,amv.name session_slam_name
,xso.sla_accounting_method_code slam_code
,xso.sla_accounting_method_type slam_type
,xso.currency_code xla_currency_code
,NVL(xso.sla_description_language,SYS_CONTEXT('USERENV','LANG'))
xla_description_language
,NVL(fla.nls_language,SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE'))
xla_nls_desc_language
,xso.sla_entered_cur_bal_sus_ccid xla_entered_cur_bal_sus_ccid
,xso.res_encumb_code_combination_id res_encumb_code_combination_id
,xso.ledger_category_code ledger_category_code
,fcu.precision ledger_currency_precision
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
ledger_currency_mau
,xso.rounding_rule_code xla_rounding_rule_code
,xso.sl_coa_mapping_id coa_mapping_id
,gcm.name coa_mapping_name
,xso.bal_seg_column_name bal_seg_column_name
,xso.mgt_seg_column_name mgt_seg_column_name
,xso.sla_bal_by_ledger_curr_flag xla_ledger_cur_bal_flag
,xso.sla_ledger_cur_bal_sus_ccid xla_ledger_cur_bal_sus_ccid
,xso.rounding_code_combination_id xla_rounding_ccid
,xso.acct_reversal_option_code xla_acct_reversal_option_code
-- the following is modified for bug 4640689
,decode(xso.capture_event_flag
,'Y',xso.ledger_id
,xso.primary_ledger_id) sla_ledger_id
,xso.latest_encumbrance_year latest_encumbrance_year
,xso.bal_seg_value_option_code bal_seg_value_option_code
,xso.mgt_seg_value_option_code mgt_seg_value_option_code
,xso.allow_intercompany_post_flag allow_intercompany_post_flag
,nvl(xso.ALC_INHERIT_CONVERSION_TYPE, 'Y') ALC_INHERIT_CONVERSION_TYPE
,xso.ALC_DEFAULT_CONV_RATE_TYPE
,decode(xso.ALC_NO_RATE_ACTION_CODE, 'FIND_RATE', nvl(xso.ALC_MAX_DAYS_ROLL_RATE, -1), 0)
ALC_MAX_DAYS_ROLL_RATE
,xso.transaction_calendar_id transaction_calendar_id
,xso.enable_average_balances_flag enable_average_balances_flag
,gjs.effective_date_rule_code effective_date_rule_code
,xso.suspense_allowed_flag suspense_allowed_flag
FROM xla_subledger_options_v xso
,fnd_application_tl fat
,gl_je_sources_vl gjs
,fnd_id_flex_structures_tl fst
,fnd_id_flex_structures_vl fsv
,xla_acctg_methods_tl amt
,xla_acctg_methods_vl amv
,fnd_currencies fcu
,fnd_languages fla
,gl_coa_mappings gcm
WHERE xso.application_id = p_application_id
AND xso.relationship_enabled_flag = 'Y'
AND xso.sla_accounting_method_code IS NOT NULL
--
-- >> valuation method enhanacements
--
AND DECODE(x_event_ledger_category
,'PRIMARY',xso.primary_ledger_id
,xso.ledger_id) = p_event_ledger_id
AND DECODE(x_event_ledger_category
,'PRIMARY',DECODE(xso.ledger_category_code
,'PRIMARY','Y'
,'N')
,'Y') = xso.capture_event_flag
--
-- << valuation method enhanacements
--
AND xso.enabled_flag = 'Y'
AND fat.application_id = xso.application_id
AND fat.language =
NVL(xso.sla_description_language,SYS_CONTEXT('USERENV','LANG'))
AND gjs.je_source_name = xso.je_source_name
AND fst.application_id = 101
AND fst.id_flex_code = 'GL#'
AND fst.id_flex_num = xso.chart_of_accounts_id
AND fst.language =
NVL(xso.sla_description_language,SYS_CONTEXT('USERENV','LANG'))
AND fsv.application_id = 101
AND fsv.id_flex_code = 'GL#'
AND fsv.id_flex_num = xso.chart_of_accounts_id
AND amt.accounting_method_code = xso.sla_accounting_method_code
AND amt.accounting_method_type_code = xso.sla_accounting_method_type
AND amt.language =
NVL(xso.sla_description_language,SYS_CONTEXT('USERENV','LANG'))
AND amv.accounting_method_code = xso.sla_accounting_method_code
AND amv.accounting_method_type_code = xso.sla_accounting_method_type
AND fcu.currency_code = xso.currency_code
AND fla.language_code =
NVL(xso.sla_description_language,SYS_CONTEXT('USERENV','LANG'))
AND gcm.coa_mapping_id(+) = xso.sl_coa_mapping_id)
ORDER BY xso.ledger_category_code;
(SELECT xlr.target_ledger_id ledger_id
,xlr.name ledger_name
,xlr.currency_code ledger_currency
,fcu.precision ledger_currency_precision
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
ledger_currency_mau
-- the following is modified for bug 4640689
,decode(xsl.alc_enabled_flag
,'Y',p_base_ledger_id
,xlr.target_ledger_id) sla_ledger_id
,nvl(xlr.ALC_INHERIT_CONVERSION_TYPE, 'Y') ALC_INHERIT_CONVERSION_TYPE
,xlr.ALC_DEFAULT_CONV_RATE_TYPE
,decode(xlr.ALC_NO_RATE_ACTION_CODE, 'FIND_RATE', nvl(xlr.ALC_MAX_DAYS_ROLL_RATE, -1), 0)
ALC_MAX_DAYS_ROLL_RATE
FROM xla_ledger_relationships_v xlr
,fnd_currencies fcu
-- the following is added for bug 4640689
,xla_subledgers xsl
WHERE xlr.primary_ledger_id = p_base_ledger_id
AND xlr.relationship_enabled_flag = 'Y'
AND xlr.ledger_category_code = 'ALC'
AND fcu.currency_code = xlr.currency_code
AND xsl.application_id = p_application_id);
(SELECT xmr.acctg_method_rule_id rule_id
,xmr.amb_context_code amb_context_code
,xmr.product_rule_type_code pad_type
,xmr.product_rule_code pad_code
,prt.name ledger_pad_name
,prv.name session_pad_name
,xpr.compile_status_code compile_status
,xmr.start_date_active start_date
,xmr.end_date_active end_date
,xla_cmp_hash_pkg.BuildPackageName
(p_application_id
,xmr.product_rule_code
,xmr.product_rule_type_code
,xmr.amb_context_code) pad_package_name
FROM xla_acctg_method_rules xmr
,xla_product_rules_b xpr
,xla_product_rules_tl prt
,xla_product_rules_vl prv
WHERE xmr.application_id = p_application_id
AND xmr.accounting_method_type_code = p_accounting_method_type
AND xmr.accounting_method_code = p_accounting_method_code
AND xmr.amb_context_code = NVL(fnd_profile.value('XLA_AMB_CONTEXT'),'DEFAULT')
AND NVL(xmr.start_date_active
,NVL(p_max_event_date
,TRUNC(sysdate)
)
)
<= NVL(p_max_event_date,TRUNC(sysdate))
AND xpr.application_id = p_application_id
AND xpr.amb_context_code = xmr.amb_context_code
AND xpr.product_rule_type_code = xmr.product_rule_type_code
AND xpr.product_rule_code = xmr.product_rule_code
AND xpr.enabled_flag = 'Y'
AND prt.application_id = xpr.application_id
AND prt.amb_context_code = xpr.amb_context_code
AND prt.product_rule_type_code = xpr.product_rule_type_code
AND prt.product_rule_code = xpr.product_rule_code
AND prt.language = p_ledger_desc_language
AND prv.application_id = xpr.application_id
AND prv.amb_context_code = xpr.amb_context_code
AND prv.product_rule_type_code = xpr.product_rule_type_code
AND prv.product_rule_code = xpr.product_rule_code);
g_base_ledger_ids.DELETE;
g_alc_ledger_ids.DELETE;
g_array_ledger.DELETE;
g_array_sources.DELETE;
g_array_event_classes.DELETE;
g_array_event_types.DELETE;
g_array_ledger_attrs.array_ledger_id.DELETE;
g_array_ledger_attrs.array_ledger_type.DELETE;
g_array_ledger_attrs.array_ledger_currency_code.DELETE;
g_array_ledger_attrs.array_rounding_rule_code.DELETE;
g_array_ledger_attrs.array_rounding_offset.DELETE;
g_array_ledger_attrs.array_mau.DELETE;
SELECT TRUNC(sysdate)
,fnd_profile.value('USER_ID')
,xsl.application_id
,fav.application_short_name
,fav.application_name
,xsl.je_source_name
,jsv.user_je_source_name
,xsl.valuation_method_flag
,decode(nvl(xsl.control_account_type_code, 'N'), 'N', 'N', 'Y')
,xsl.alc_enabled_flag
INTO g_record_session.date_sources('XLA_CREATION_DATE')
,g_record_session.num_sources('XLA_ENTRY_CREATED_BY')
,g_record_session.num_sources('XLA_EVENT_APPL_ID')
,g_record_session.char_sources('XLA_EVENT_APPL_SHORT_NAME')
,g_record_session.char_sources_sl('XLA_EVENT_APPL_NAME')
,g_record_session.char_sources('XLA_JE_SOURCE_NAME')
,g_record_session.char_sources_sl('XLA_USER_JE_SOURCE_NAME')
,g_record_session.char_sources('VALUATION_METHOD_FLAG')
,g_record_session.char_sources('CONTROL_ACCOUNT_ENABLED_FLAG')
,g_record_session.char_sources('XLA_ALC_ENABLED_FLAG')
FROM xla_subledgers xsl
,fnd_application_vl fav
,gl_je_sources_vl jsv
WHERE xsl.application_id = p_application_id
AND fav.application_id = xsl.application_id
AND jsv.je_source_name = xsl.je_source_name;
SELECT ledger_category_code
INTO l_event_ledger_category
FROM gl_ledgers
WHERE ledger_id = p_event_ledger_id;
.char_sources('DYNAMIC_INSERTS_ALLOWED_FLAG') := c1.dynamic_inserts_allowed_flag;
(p_msg =>'dynamic_inserts_allowed_flag = '||c1.dynamic_inserts_allowed_flag
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
Please run Update Subledger Accounting Options program for your application.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_accounting_cache_pkg.load_application_ledgers');
'Please run Update Subledger Accounting Options program for your application.'
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_accounting_cache_pkg.get_je_category');
SELECT ect.event_class_code event_class_code
,ect.name ledger_event_class_name
,ecv.name session_event_class_name
,ect.language language
FROM xla_subledger_options_v xso
,xla_event_classes_tl ect
,xla_event_classes_vl ecv
WHERE xso.application_id = p_application_id
AND DECODE(xso.valuation_method_flag
,'N',xso.primary_ledger_id
,xso.ledger_id) = p_ledger_id
AND xso.enabled_flag = 'Y'
AND ect.application_id = p_application_id
AND ect.language IN
(NVL(xso.sla_description_language,USERENV('LANG'))
,USERENV('LANG'))
AND ecv.application_id = p_application_id
AND ecv.event_class_code = ect.event_class_code
GROUP BY ect.event_class_code
,ecv.name
,ect.language
,ect.name;
SELECT xjc.event_class_code event_class_code
,xjc.je_category_name je_category_name
,xso.ledger_id ledger_id
FROM xla_subledger_options_v xso
,xla_je_categories xjc
WHERE xso.application_id = p_application_id
AND xso.enabled_flag = 'Y'
AND xjc.application_id = p_application_id
AND xjc.ledger_id = xso.ledger_id
AND DECODE(x_event_ledger_category
,'PRIMARY',xso.primary_ledger_id
,xso.ledger_id) = p_ledger_id
AND DECODE(x_event_ledger_category
,'PRIMARY',DECODE(xso.ledger_category_code
,'PRIMARY','Y','N')
,'Y') = xso.capture_event_flag;
SELECT ett.event_type_code event_type_code
,ett.name ledger_event_type_name
,etv.name session_event_type_name
,ett.language language
FROM xla_subledger_options_v xso
,xla_event_types_tl ett
,xla_event_types_vl etv
WHERE xso.application_id = p_application_id
AND DECODE(xso.valuation_method_flag
,'N',xso.primary_ledger_id
,xso.ledger_id) = p_ledger_id
AND xso.enabled_flag = 'Y'
AND ett.application_id = p_application_id
AND ett.language IN
(NVL(xso.sla_description_language,USERENV('LANG'))
,USERENV('LANG'))
AND etv.application_id = p_application_id
AND etv.event_type_code = ett.event_type_code
GROUP BY ett.event_type_code
,etv.name
,ett.language
,ett.name;
SELECT source_code
,datatype_code
FROM xla_sources_b WHERE application_id = 602;
SELECT nvl(minimum_accountable_unit, power(10, -1* precision))
INTO l_entered_currency_mau.currency_mau
FROM FND_CURRENCIES
WHERE currency_code = p_currency_code;