The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 24-JUL-2003 K.Boussema Updated the error messages |
| 26-AUG-2003 K.Boussema Reviewd the generation of the extract to |
| handle the use of line_number as source |
| 10-SEP-2003 K.Boussema Changed to fix bug3095206:Accounting Reversal|
| 17-SEP-2003 K.Boussema Updated to Get je_category from cache:3109690|
| 09-OCT-2003 K.Boussema Changed to accept AADs differents Extract |
| specifcations |
| 27-OCT-2003 K.Boussema Changed to raise Error when no data retrieved|
| by the extract, bug 3203657 |
| 04-NOV-2003 K.Boussema Added new accounting reversal indicator value|
| 01-DEC-2003 K.Boussema Added the cache of Extract errors in code |
| generated by the compiler |
| 12-DEC-2003 K.Boussema Reviewed for bug 3042840 |
| 18-DEC-2003 K.Boussema Changed to fix bug 3042840,3307761,3268940 |
| 3310291 and 3320689 |
| 22-DEC-2003 K.Boussema Replaced Extract Validations by a call to |
| Extract Integrity Checker routine |
| 02-FRB-2004 K.Boussema Changed code generated, refer to bug 3274707 |
| 12-FEB-2004 K.Boussema Changed to only display the extraxt error msg|
| of the alway_populated objects |
| 19-FEB-2004 K.Boussema Made changes for the FND_LOG. |
| 12-MAR-2004 K.Boussema Changed to incorporate the select of lookups |
| from the extract objects |
| 22-MAR-2004 K.Boussema Added a parameter p_module to the TRACE calls|
| and the procedure. |
| 11-MAY-2004 K.Boussema Removed the call to XLA trace routine from |
| trace() procedure |
| 17-MAY-2004 W.Shen Accounting Attribute Enhancement project |
| check the dld for changed made to the package|
| 26-MAY-2004 W.Shen change code for the error message |
| 26-Jul-2004 W.Shen bug 3786968. transaction reversal do not have|
| extract line. When call function |
| xla_accounting_engine_pkg.CacheExtractErrors |
| The transaction reversal flag is passed. |
| 23-Sep-2004 S.Singhania Made changes for the bulk peroformance. It has |
| changed the code at number of places. |
| 05-Oct-2004 S.Singhania Bug 3931752: Modified constants C_EVENT_BODY |
| C_BODY_LINES to set correct target_ledger_id.|
| 08-Oct-2004 S.Singhania Bug 3939231: Added code to C_EVENT_BODY to |
| initialize xla_ae_lines_pkg.g_temp_line_num. |
| 12-Oct-2004 K.Boussema Made changes for the Accounting Event Extract|
| Diagnostics feature. |
| 08-Dec-2004 K.Boussema Updated to add diagnostic framework datamodel |
| changes |
| 27-DEC-2004 K.Boussema Changed the VARCHAR2 type by CLOB to handle the |
| large main procedure |
| 15-Feb-2005 W.Shen change code ledger currency project |
| remove alc object. |
| add the two calculate accounted amts, gain/loss |
| flags |
| 07-Mar-2005 K.Boussema Changed for ADR-enhancements. |
| 29-Mar-2005 M.Asada Changed for Reference Objects. |
| 18-Jul-2005 W.Chan 4495733 - In GetEntityName, update length |
| of l_name from 30 to 90 |
| 21-JUL-2005 K.Boussema Reviewed to handle the two cases: |
| - no header Transaction objects and |
| - no line Transaction objects |
| 22-Sep-2005 S.Singhania Bug 4544725. Implemented Business Flows and |
| Reversals for Non-Upgraded JEs. |
| 18-Oct-2005 V. Kumar Removed code for Analytical Criteria |
| 20-Jan-2006 A. Wan 4884853 - |
| Modified GenerateAccRevBody to adjust Accrual |
| Reversal Date before PostAccountingEngine. |
| 10-Feb-2006 A. Wan 5019460 - redundant logic in C_EVENT_BODY |
| 08-Mar-2006 V. Kumar Modified C_MAIN_PROCEDURE |
| 09-May-2006 V. Kumar 5217187 Populating GL_DATE for Reversal |
| 09-May-2006 A.WAn 5202219 - prevent trx rev error out. |
| 19-May-2006 V. Kumar 5229264 Populating gl_date for Trx rev. case |
| 09-Nov-2006 A.Wan 5648433 - init l_event_id for line_cursor. |
+===========================================================================*/
--
--+==========================================================================+
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--| AAD templates/Global constants |
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--| |
--+==========================================================================+
--
--
--+========================================================
--| Template of the main function CreateHeadersAndLines
--+========================================================
--
C_MAIN_PROCEDURE CONSTANT VARCHAR2(10000) := '
--
--+============================================+
--| |
--| PRIVATE FUNCTION |
--| |
--+============================================+
--
FUNCTION CreateHeadersAndLines
(p_application_id IN NUMBER
,p_base_ledger_id IN NUMBER
,p_target_ledger_id IN NUMBER
,p_pad_start_date IN DATE
,p_pad_end_date IN DATE
,p_primary_ledger_id IN NUMBER)
RETURN BOOLEAN IS
l_created BOOLEAN:=FALSE;
g_array_event.DELETE;
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => hdr_idx);
l_result := XLA_AE_LINES_PKG.InsertLines;
l_result := XLA_AE_HEADER_PKG.InsertHeaders ;
l_result := XLA_AE_LINES_PKG.InsertLines ;
XLA_AE_JOURNAL_ENTRY_PKG.updateJournalEntryStatus (p_hdr_idx => g_last_hdr_idx);
C_INSERT_SOURCES_PROC CONSTANT CLOB:= '
---------------------------------------
--
-- PRIVATE PROCEDURE
-- insert_sources_$EVENT_INDEX$
--
----------------------------------------
--
PROCEDURE insert_sources_$EVENT_INDEX$(
p_target_ledger_id IN NUMBER
, p_language IN VARCHAR2
, p_sla_ledger_id IN NUMBER
, p_pad_start_date IN DATE
, p_pad_end_date IN DATE
)
IS
C_EVENT_TYPE_CODE CONSTANT VARCHAR2(30) := ''$event_type_code$'';
l_log_module := C_DEFAULT_MODULE||''.insert_sources_$EVENT_INDEX$'';
(p_msg => ''BEGIN of insert_sources_$EVENT_INDEX$''
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT oracle_username
INTO p_apps_owner
FROM fnd_oracle_userid
WHERE read_only_flag = ''U''
;
$insert_header_sources$
$insert_line_sources$
IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
trace
(p_msg => ''END of insert_sources_$EVENT_INDEX$''
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => ''$package_name$.insert_sources_$EVENT_INDEX$'');
END insert_sources_$EVENT_INDEX$;
SELECT DISTINCT
xpah.event_type_code
, xpah.event_class_code
, xpah.entity_code
, xeca.calculate_acctd_amts_flag
, xeca.calculate_g_l_amts_flag
FROM xla_prod_acct_headers xpah
, xla_event_types_b xetb
, xla_event_class_attrs xeca
WHERE xpah.product_rule_code = p_product_rule_code
AND xpah.product_rule_type_code = p_product_rule_type_code
AND xpah.application_id = p_application_id
AND xpah.amb_context_code = p_amb_context_code
AND xpah.application_id = xetb.application_id
AND xpah.entity_code = xetb.entity_code
AND xpah.event_class_code = xetb.event_class_code
AND (xetb.event_type_code = xpah.event_type_code
OR
xetb.event_type_code = xpah.event_class_code||'_ALL'
)
AND xetb.accounting_flag = xpah.accounting_required_flag
AND xpah.accounting_required_flag = 'Y'
AND xpah.validation_status_code = 'R' -- Running
AND xetb.enabled_flag = 'Y'
AND xeca.application_id = xetb.application_id
AND xeca.event_class_code = xetb.event_class_code
AND xeca.entity_code = xetb.entity_code
ORDER BY xpah.event_type_code
;
SELECT DISTINCT
xpah.event_type_code
, xpah.event_class_code
, xpah.entity_code
, xeca.calculate_acctd_amts_flag
, xeca.calculate_g_l_amts_flag
FROM xla_prod_acct_headers xpah
, xla_event_types_b xetb
, xla_event_class_attrs xeca
, xla_aad_line_defn_assgns xald
, xla_line_definitions_b xld
WHERE xpah.product_rule_code = p_product_rule_code
AND xpah.product_rule_type_code = p_product_rule_type_code
AND xpah.application_id = p_application_id
AND xpah.amb_context_code = p_amb_context_code
AND xpah.application_id = xetb.application_id
AND xpah.entity_code = xetb.entity_code
AND xpah.event_class_code = xetb.event_class_code
AND (xetb.event_type_code = xpah.event_type_code
OR
xetb.event_type_code = xpah.event_class_code||'_ALL'
)
AND xetb.accounting_flag = xpah.accounting_required_flag
AND xpah.accounting_required_flag = 'Y'
AND xpah.validation_status_code = 'R' -- Running
AND xetb.enabled_flag = 'Y'
AND xeca.application_id = xetb.application_id
AND xeca.event_class_code = xetb.event_class_code
AND xeca.entity_code = xetb.entity_code
AND xald.application_id = xpah.application_id
AND xald.amb_context_code = xpah.amb_context_code
AND xald.event_class_code = xpah.event_class_code
AND xald.event_type_code = xpah.event_type_code
AND xald.product_rule_type_code = xpah.product_rule_type_code
AND xald.product_rule_code = xpah.product_rule_code
AND xald.application_id = xld.application_id
AND xald.amb_context_code = xld.amb_context_code
AND xald.event_class_code = xld.event_class_code
AND xald.event_type_code = xld.event_type_code
AND xald.line_definition_owner_code = xld.line_definition_owner_code
AND xald.line_definition_code = xld.line_definition_code
AND xld.budgetary_control_flag = 'Y'
ORDER BY xpah.event_type_code;
(p_msg => 'SQL - Select from xla_prod_acct_headers '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT DISTINCT
xetb.event_type_code
FROM xla_prod_acct_headers xpah
, xla_event_types_b xetb
WHERE xpah.product_rule_code = p_product_rule_code
AND xpah.product_rule_type_code = p_product_rule_type_code
AND xpah.application_id = p_application_id
AND xpah.amb_context_code = p_amb_context_code
AND xpah.application_id = xetb.application_id
AND xpah.entity_code = xetb.entity_code
AND xpah.event_class_code = xetb.event_class_code
AND xetb.event_type_code = DECODE( xpah.event_type_code
, xpah.event_class_code ||'_ALL',xetb.event_type_code
, xpah.event_type_code
)
AND xetb.event_type_code <> xetb.event_class_code || '_ALL'
AND xetb.accounting_flag = 'Y'
AND xetb.enabled_flag = 'Y'
AND (xpah.accounting_required_flag = 'N'
OR
xpah.validation_status_code <> 'R') -- not Running
;
(p_msg => 'SQL - SELECT from xla_prod_acct_headers'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT DISTINCT
xetb.event_type_code
FROM xla_event_types_b xetb
WHERE xetb.entity_code = p_entity_code
AND xetb.event_class_code = p_event_class_code
AND xetb.application_id = p_application_id
AND xetb.accounting_flag = 'Y'
AND xetb.enabled_flag = 'Y'
AND xetb.event_type_code <> xetb.event_class_code || '_ALL'
ORDER BY xetb.event_type_code
;
(p_msg => 'SQL - SELECT from xla_event_types_b'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT REPLACE(xect.name , '''','''''')
INTO l_name
FROM xla_event_types_tl xect
WHERE xect.application_id = p_application_id
AND xect.entity_code = p_entity_code
AND xect.event_class_code = p_event_class_code
AND xect.event_type_code = p_event_type_code
AND xect.language = USERENV('LANG')
;
SELECT REPLACE(xett.name , '''','''''')
INTO l_name
FROM xla_entity_types_tl xett
WHERE xett.application_id = p_application_id
AND xett.entity_code = p_entity
AND xett.LANGUAGE = USERENV('LANG')
;
SELECT REPLACE(xect.name , '''','''''')
INTO l_name
FROM xla_event_classes_tl xect
WHERE xect.application_id = p_application_id
AND xect.entity_code = p_entity_code
AND xect.event_class_code = p_event_class_code
AND xect.language = USERENV('LANG')
;
SELECT eca.accounting_attribute_code
, eca.source_application_id
, eca.source_type_code
, eca.source_code
, DECODE(nvl(xes.level_code, 'H'),'H','H','L')
FROM xla_evt_class_acct_attrs eca
, xla_event_sources xes
, xla_acct_attributes_b xasb
, xla_sources_b xsb
WHERE eca.application_id = p_application_id
AND eca.event_class_code = p_event_class
AND xasb.accounting_attribute_code = eca.accounting_attribute_code
AND xasb.assignment_level_code = 'EVT_CLASS_ONLY'
AND xes.application_id(+) = p_application_id
AND xes.entity_code(+) = p_entity
AND xes.event_class_code(+) = p_event_class
AND xes.source_application_id(+) = eca.source_application_id
AND xes.source_type_code(+) = eca.source_type_code
AND xes.source_code(+) = eca.source_code
AND xasb.journal_entry_level_code = 'H'
AND xes.active_flag(+) = 'Y'
AND xsb.application_id = eca.source_application_id
AND xsb.source_type_code = eca.source_type_code
AND xsb.source_code = eca.source_code
UNION
SELECT aha.accounting_attribute_code
, aha.source_application_id
, aha.source_type_code
, aha.source_code
, DECODE(xes.level_code,'H','H','L')
FROM xla_aad_hdr_acct_attrs aha
, xla_event_sources xes
, xla_acct_attributes_b xasb
, xla_sources_b xsb
WHERE aha.application_id = p_application_id
AND aha.amb_context_code = p_amb_context_code
AND aha.product_rule_type_code = p_product_rule_type_code
AND aha.product_rule_code = p_product_rule_code
AND aha.event_class_code = p_event_class
AND aha.event_type_code = p_event_type
AND xasb.accounting_attribute_code = aha.accounting_attribute_code
AND xasb.assignment_level_code IN ('AAD_ONLY','EVT_CLASS_AAD')
AND xes.application_id(+) = p_application_id
AND xes.entity_code(+) = p_entity
AND xes.event_class_code(+) = p_event_class
AND xes.source_application_id(+) = aha.source_application_id
AND xes.source_type_code(+) = aha.source_type_code
AND xes.source_code(+) = aha.source_code
AND xes.active_flag (+) = 'Y'
AND xsb.application_id = aha.source_application_id
AND xsb.source_type_code = aha.source_type_code
AND xsb.source_code = aha.source_code
;
SELECT eca.accounting_attribute_code
, eca.source_application_id
, eca.source_type_code
, eca.source_code
, DECODE(nvl(xes.level_code, 'H'),'H','H','L')
FROM xla_evt_class_acct_attrs eca
, xla_event_sources xes
, xla_acct_attributes_b xasb
, xla_sources_b xsb
WHERE eca.application_id = p_application_id
AND eca.event_class_code = p_event_class
AND xasb.accounting_attribute_code = eca.accounting_attribute_code
AND xasb.assignment_level_code = 'EVT_CLASS_ONLY'
AND xes.application_id (+) = p_application_id
AND xes.entity_code (+) = p_entity
AND xes.event_class_code(+) = p_event_class
AND xes.source_application_id(+) = eca.source_application_id
AND xes.source_type_code(+) = eca.source_type_code
AND xes.source_code(+) = eca.source_code
AND xasb.journal_entry_level_code in ('L', 'C')
AND xes.active_flag(+) = 'Y'
AND xsb.application_id = eca.source_application_id
AND xsb.source_type_code = eca.source_type_code
AND xsb.source_code = eca.source_code
;
SELECT count(xes.accounting_attribute_code)
INTO l_number
FROM xla_evt_class_acct_attrs xes
WHERE xes.application_id = p_application_id
-- AND xes.entity_code = p_entity
AND xes.event_class_code = p_event_class
-- AND xes.active_flag = 'Y'
AND xes.accounting_attribute_code = 'ACCOUNTING_REVERSAL_OPTION'
;
(p_msg => 'SQL - SELECT from xla_event_sources and xla_acctg_sources_b'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT xpah.description_code
, xpah.description_type_code
INTO l_description_code
, l_description_type_code
FROM xla_prod_acct_headers xpah
WHERE xpah.product_rule_type_code = p_product_rule_type_code
AND xpah.product_rule_code = p_product_rule_code
AND xpah.entity_code = p_entity
AND xpah.event_class_code = p_event_class
AND xpah.event_type_code = p_event_type
AND xpah.application_id = p_application_id
AND xpah.amb_context_code = p_amb_context_code
AND xpah.accounting_required_flag = 'Y'
;
SELECT DISTINCT
xldj.accounting_line_code
, xldj.accounting_line_type_code
, xldj.line_definition_code
, xldj.line_definition_owner_code
FROM xla_aad_line_defn_assgns xald
, xla_line_defn_jlt_assgns xldj
, xla_prod_acct_headers xpah
, xla_line_definitions_b xld
WHERE xldj.line_definition_owner_code = xald.line_definition_owner_code
AND xldj.line_definition_code = xald.line_definition_code
AND xldj.event_class_code = xald.event_class_code
AND xldj.event_type_code = xald.event_type_code
AND xldj.application_id = xald.application_id
AND xldj.amb_context_code = xald.amb_context_code
AND xldj.active_flag = 'Y'
--
AND xld.line_definition_owner_code = xald.line_definition_owner_code
AND xld.line_definition_code = xald.line_definition_code
AND xld.event_class_code = xald.event_class_code
AND xld.event_type_code = xald.event_type_code
AND xld.application_id = xald.application_id
AND xld.amb_context_code = xald.amb_context_code
AND xld.budgetary_control_flag = XLA_CMP_PAD_PKG.g_bc_pkg_flag
--
AND xald.product_rule_type_code = xpah.product_rule_type_code
AND xald.product_rule_code = xpah.product_rule_code
AND xald.event_class_code = xpah.event_class_code
AND xald.event_type_code = xpah.event_type_code
AND xald.application_id = xpah.application_id
AND xald.amb_context_code = xpah.amb_context_code
--
AND xpah.product_rule_type_code = p_product_rule_type_code
AND xpah.product_rule_code = p_product_rule_code
AND xpah.entity_code = p_entity
AND xpah.event_class_code = p_event_class
AND xpah.event_type_code = p_event_type
AND xpah.application_id = p_application_id
AND xpah.amb_context_code = p_amb_context_code
AND xpah.accounting_required_flag = 'Y'
ORDER BY xldj.accounting_line_type_code, xldj.accounting_line_code
, xldj.line_definition_owner_code, xldj.line_definition_code
;
l_insert_sources CLOB;
l_array_insert_sources DBMS_SQL.VARCHAR2S;
l_insert_sources := C_INSERT_SOURCES_PROC;
l_insert_sources := xla_cmp_string_pkg.replace_token(l_insert_sources,'$event_class_code$', nvl(p_event_class,' ')); -- 4417664
l_insert_sources := xla_cmp_string_pkg.replace_token(l_insert_sources,'$event_type_code$',nvl(p_event_type,' ')); -- 4417664
l_insert_sources := xla_cmp_string_pkg.replace_token(l_insert_sources,'$insert_header_sources$',
nvl(xla_cmp_extract_pkg.GenerateInsertHdrSources (
--
p_array_table_name => l_array_table_name
, p_array_parent_table_index => l_array_parent_table_index
, p_array_table_hash => l_array_table_hash
, p_array_table_type => l_array_table_type
, p_array_populated_flag => l_array_populated_flag
--
, p_array_ref_obj_flag => l_array_ref_obj_flag
, p_array_join_condition => l_array_join_condition
--
, p_array_h_source_index => l_array_h_source_index
, p_array_h_table_index => l_array_h_table_index
--
, p_array_h_mls_source_index => l_array_h_mls_source_index
, p_array_h_mls_table_index => l_array_h_mls_table_index
--
, p_array_application_id => p_rec_sources.array_application_id
, p_array_source_code => p_rec_sources.array_source_code
, p_array_source_type_code => p_rec_sources.array_source_type_code
, p_array_flex_value_set_id => p_rec_sources.array_flex_value_set_id
, p_array_lookup_type => p_rec_sources.array_lookup_type
, p_array_view_application_id => p_rec_sources.array_view_application_id
--
, p_procedure => p_procedure
),' ')
);
l_insert_sources := xla_cmp_string_pkg.replace_token(l_insert_sources, '$insert_line_sources$',
nvl(xla_cmp_extract_pkg.GenerateInsertLineSources (
--
p_array_table_name => l_array_table_name
, p_array_parent_table_index => l_array_parent_table_index
, p_array_table_hash => l_array_table_hash
, p_array_table_type => l_array_table_type
, p_array_populated_flag => l_array_populated_flag
--
, p_array_ref_obj_flag => l_array_ref_obj_flag
, p_array_join_condition => l_array_join_condition
--
, p_array_l_source_index => l_array_l_source_index
, p_array_l_table_index => l_array_l_table_index
--
, p_array_l_mls_source_index => l_array_l_mls_source_index
, p_array_l_mls_table_index => l_array_l_mls_table_index
--
, p_array_application_id => p_rec_sources.array_application_id
, p_array_source_code => p_rec_sources.array_source_code
, p_array_source_type_code => p_rec_sources.array_source_type_code
, p_array_flex_value_set_id => p_rec_sources.array_flex_value_set_id
, p_array_lookup_type => p_rec_sources.array_lookup_type
, p_array_view_application_id => p_rec_sources.array_view_application_id
--
, p_procedure => p_procedure
) ,' ')
);
l_insert_sources:= xla_cmp_string_pkg.replace_token(l_insert_sources,'$EVENT_INDEX$' ,TO_CHAR(l_ObjectIndex)); -- 4417664
l_insert_sources:= xla_cmp_string_pkg.replace_token(l_insert_sources,'$package_name$' ,g_package_name); -- 4417664
p_package_text => l_insert_sources
,p_array_string => l_array_insert_sources
);
||l_array_insert_sources.COUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
p_array_string_1 => l_array_insert_sources
,p_array_string_2 => l_array_event
);
C_CALL_INSERT_EXT_SOURCES CONSTANT VARCHAR2(3000) := '
IF ( g_diagnostics_mode =''Y'' ) THEN
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
(p_msg => ''CALL Transaction Objects Diagnostics''
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
insert_sources_$Index$(
p_target_ledger_id => p_target_ledger_id
, p_language => l_language
, p_sla_ledger_id => l_sla_ledger_id
, p_pad_start_date => p_pad_start_date
, p_pad_end_date => p_pad_end_date
);
l_call_event := l_call_event || C_CALL_INSERT_EXT_SOURCES;
l_call_event := l_call_event ||C_CALL_INSERT_EXT_SOURCES;