[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 12-MAI-2003 K.Boussema Updated Message code XLA_AP_CODE_COMBINATION |
| 13-MAI-2003 K.Boussema Renamed temporary table xla_je_lines_gt by |
| xla_ae_lines_gt |
| 27-MAI-2003 K.Boussema Renamed code_combination_status by |
| code_combination_status_flag |
| 17-JUL-2003 K.Boussema Reviewd the code |
| 24-JUL-2003 K.Boussema Updated the error messages |
| 29-JUL-2003 K.Boussema Renamed XLA_AP_INVALID_CODE_COMBINATION |
| message code by XLA_AP_INV_CODE_COMBINATION |
| 30-JUL-2003 K.Boussema Reviewed the procedure create_ccid() |
| 28-AUG-2003 K.boussema Reviewed GetCCid to fix bug 3103575 |
| 01-SEP-2003 K.boussema Reviewed call to build_message, bug 3099988 |
| 27-SEP-2003 K.Boussema Added the error message XLA_AP_COA_INVALID |
| 13-NOV-2003 K.Boussema Changed to fix issue in bug3252058 |
| 26-NOV-2003 K.Boussema Added the cache of GL mapping information |
| 28-NOV-2003 K.Boussema Changed create_ccid call by create_ccidV2 |
| 12-DEC-2003 K.Boussema Renamed target_coa_id in xla_ae_lines_gt |
| by ccid_coa_id |
| 18-DEC-2003 K.Boussema Changed to fix bug 3042840,3307761,3268940 |
| 3310291 and 3320689 |
| 20-JAN-2004 K.Boussema Updated the message error XLA_AP_COA_INVALID |
| 03-FEB-2004 K.Boussema Reviewed get_flexfield_description in order |
| to retrieve segment value description instead|
| of the segment description |
| 16-FEB-2004 K.Boussema Made changes for the FND_LOG. |
| renamed create_ccidV2 by create_ccid |
| 03-MAR-2004 K.Boussema Changed to set GL_ACCOUNTS_MAP_GRP debug param.|
| 22-MAR-2004 K.Boussema Added a parameter p_module to the TRACE calls|
| and the procedure. |
| 25-MAR-2004 K.Boussema Changed MapCcid to insert the coa_mapping_id |
| into gl_accounts_map_interface_gt GT GL table |
| 11-MAY-2004 K.Boussema Removed the call to XLA trace routine from |
| trace() procedure |
| Revised update of journal entry status defined |
| in BuildCcids() function |
| 03-JUN-2004 K.Boussema Added the validaton of the CCIDs passed through|
| extract, refer to bug 3656297 |
| 23-JUN-2004 K.Boussema Removed the validation of CCIDs, changed error |
| message XLA_AP_INVALID_CCID |
| by XLA_AP_CCID_NOT_EXISTS |
| 23-Sep-2004 S.Singhania Minor changes due to bulk performance in calls |
| to xla_accrounting_err_pkg.build_message. |
| 28-Feb-2005 K.boussema Renamed GT table: gl_accounts_map_interface_gt |
| => gl_accts_map_int_gt |
| 03-MAR-2005 K.Boussema Reviewed MapCCid() function to fix bug 4197942 |
| 06-MAR-2005 W. Shen Ledger Currency Project. |
| maintain two ccids in line table |
| 14-Mar-2005 K.Boussema Changed for ADR-enhancements. |
| 11-APR-2005 K.Boussema Reviewed the code to don't process Dummy lines |
| 21-APR-2005 Shishir J. Renamed gl_accounts_map_bsv_int_gt to |
| gl_accts_map_bsv_gt |
| 19-MAI-2005 K.Boussema Reviewed cache_combination_id to fix bug4304098 |
| 23-MAY-2005 W.Chan Fix bug4388150 in create_ccid |
| 08-Aug-2005 W.Chan Fix bug4542460 in map_ccid |
| 19-Aug-2005 W.Chan Fix bug4564062 in map_ccid |
| 26-May-2006 M.Asada Merge updates in create_ccid and create_new_ccid|
| 18-Mar-2011 VGOPISET Bug11727459 Added filter CALCULATE_G_L_AMTS_FLAG|
| ='Y' in Create_New_CCID for Update on ALT_CCID |
| Since ALT_CCID is used only when XLA calculates |
| the Gain/Loss Amounts. |
| 04-Apr-2011 NMIKKILI Changed source from which segments are fetched |
| in gl_accts_map_int_gt for bug 13735405 |
+===========================================================================*/
/*-------------------------------------------------------------------+
| |
| PL/SQL constants |
| |
+-------------------------------------------------------------------*/
-- accounting CCID status
C_NOT_PROCESSED CONSTANT VARCHAR2(30) := 'NOT_PROCESSED';
g_cache_dynamic_inserts xla_ae_journal_entry_pkg.t_array_V1L;
SELECT application_name
INTO l_name
FROM fnd_application_vl fnd
WHERE fnd.application_id = p_flex_application_id
;
SELECT id_flex_structure_name
INTO l_name
FROM fnd_id_flex_structures_vl fnd
WHERE fnd.application_id = p_flex_application_id
AND fnd.id_flex_code = p_id_flex_code
AND fnd.id_flex_num = l_id_flex_num
;
l_null_dynamic_inserts xla_ae_journal_entry_pkg.t_array_V1L;
g_cache_dynamic_inserts := l_null_dynamic_inserts;
FOR flex_cur IN ( SELECT fsav.segment_attribute_type segment_qualifier
, fsav.application_column_name segment_code
FROM fnd_segment_attribute_values fsav
WHERE fsav.application_id = p_flex_application_id
AND fsav.id_flex_code = p_id_flex_code
AND fsav.id_flex_num = p_id_flex_num
AND fsav.attribute_value = 'Y'
GROUP BY fsav.application_column_name, fsav.segment_attribute_type
)
LOOP
g_array_key_flexfield(p_position).segment_qualifier(flex_cur.segment_qualifier):= flex_cur.segment_code;
SELECT upper(fifs.application_column_name) segment_name
, fifs.segment_num segment_num
FROM fnd_id_flex_segments fifs
WHERE fifs.application_id = p_flex_application_id
AND fifs.id_flex_code = p_id_flex_code
AND fifs.id_flex_num = p_id_flex_num
AND fifs.enabled_flag = 'Y'
ORDER BY fifs.segment_num
)
LOOP
g_array_key_flexfield(p_position).segment_num(flex_cur.segment_num):= flex_cur.segment_name;
, p_dynamic_inserts_flag IN VARCHAR2
)
IS
--
Idx BINARY_INTEGER;
' - p_dynamic_inserts_flag = '|| p_dynamic_inserts_flag
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
g_cache_dynamic_inserts(p_sla_coa_mapping_id) := SUBSTR(p_dynamic_inserts_flag,1,1);
(p_msg => 'SQL - Select from gl_code_combinations '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT
gcc.code_combination_id
, p_flex_application_id
, p_id_flex_code
, gcc.chart_of_accounts_id
, gcc.segment1
, gcc.segment2
, gcc.segment3
, gcc.segment4
, gcc.segment5
, gcc.segment6
, gcc.segment7
, gcc.segment8
, gcc.segment9
, gcc.segment10
, gcc.segment11
, gcc.segment12
, gcc.segment13
, gcc.segment14
, gcc.segment15
, gcc.segment16
, gcc.segment17
, gcc.segment18
, gcc.segment19
, gcc.segment20
, gcc.segment21
, gcc.segment22
, gcc.segment23
, gcc.segment24
, gcc.segment25
, gcc.segment26
, gcc.segment27
, gcc.segment28
, gcc.segment29
, gcc.segment30
, 'Y'
INTO
g_array_combination_id(l_position).combination_id
, g_array_combination_id(l_position).flexfield_application_id
, g_array_combination_id(l_position).id_flex_code
, g_array_combination_id(l_position).id_flex_num
, g_array_combination_id(l_position).segment1
, g_array_combination_id(l_position).segment2
, g_array_combination_id(l_position).segment3
, g_array_combination_id(l_position).segment4
, g_array_combination_id(l_position).segment5
, g_array_combination_id(l_position).segment6
, g_array_combination_id(l_position).segment7
, g_array_combination_id(l_position).segment8
, g_array_combination_id(l_position).segment9
, g_array_combination_id(l_position).segment10
, g_array_combination_id(l_position).segment11
, g_array_combination_id(l_position).segment12
, g_array_combination_id(l_position).segment13
, g_array_combination_id(l_position).segment14
, g_array_combination_id(l_position).segment15
, g_array_combination_id(l_position).segment16
, g_array_combination_id(l_position).segment17
, g_array_combination_id(l_position).segment18
, g_array_combination_id(l_position).segment19
, g_array_combination_id(l_position).segment20
, g_array_combination_id(l_position).segment21
, g_array_combination_id(l_position).segment22
, g_array_combination_id(l_position).segment23
, g_array_combination_id(l_position).segment24
, g_array_combination_id(l_position).segment25
, g_array_combination_id(l_position).segment26
, g_array_combination_id(l_position).segment27
, g_array_combination_id(l_position).segment28
, g_array_combination_id(l_position).segment29
, g_array_combination_id(l_position).segment30
, g_array_combination_id(l_position).combination_status
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_combination_id
AND gcc.chart_of_accounts_id = l_id_flex_num
AND gcc.template_id IS NULL
;
(p_msg => 'SQL - Select from fnd_flex_values_tl'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT ffvt.description
INTO l_segment_description
FROM fnd_flex_values_tl ffvt
, fnd_flex_values ffv
, fnd_id_flex_segments fifs
WHERE ffvt.flex_value_meaning = ffv.flex_value
AND ffvt.flex_value_id = ffv.flex_value_id
AND ffvt.language = l_desc_language
AND ffv.flex_value = l_segment_value
AND ffv.flex_value_set_id = fifs.flex_value_set_id
AND fifs.application_id = p_flex_application_id
AND fifs.id_flex_code = p_id_flex_code
AND fifs.id_flex_num = l_id_flex_num
AND fifs.application_column_name = l_segment_code
;
SELECT DISTINCT
hdr.event_id event_id,
hdr.entity_id entity_id,
hdr.ledger_id ledger_id,
hdr.ae_header_id ae_header_id,
temp.ae_line_num ae_line_num
FROM xla_ae_lines_gt temp,
xla_ae_headers_gt hdr
WHERE temp.ae_header_id = hdr.ae_header_id
AND temp.ccid_coa_id = p_chart_of_accounts_id
AND nvl(temp.segment1 ,'#') = nvl(p_segment1,'#')
AND nvl(temp.segment2 ,'#') = nvl(p_segment2,'#')
AND nvl(temp.segment3 ,'#') = nvl(p_segment3,'#')
AND nvl(temp.segment4 ,'#') = nvl(p_segment4,'#')
AND nvl(temp.segment5 ,'#') = nvl(p_segment5,'#')
AND nvl(temp.segment6 ,'#') = nvl(p_segment6,'#')
AND nvl(temp.segment7 ,'#') = nvl(p_segment7,'#')
AND nvl(temp.segment8 ,'#') = nvl(p_segment8,'#')
AND nvl(temp.segment9 ,'#') = nvl(p_segment9,'#')
AND nvl(temp.segment10,'#') = nvl(p_segment10,'#')
AND nvl(temp.segment11,'#') = nvl(p_segment11,'#')
AND nvl(temp.segment12,'#') = nvl(p_segment12,'#')
AND nvl(temp.segment13,'#') = nvl(p_segment13,'#')
AND nvl(temp.segment14,'#') = nvl(p_segment14,'#')
AND nvl(temp.segment15,'#') = nvl(p_segment15,'#')
AND nvl(temp.segment16,'#') = nvl(p_segment16,'#')
AND nvl(temp.segment17,'#') = nvl(p_segment17,'#')
AND nvl(temp.segment18,'#') = nvl(p_segment18,'#')
AND nvl(temp.segment19,'#') = nvl(p_segment19,'#')
AND nvl(temp.segment20,'#') = nvl(p_segment20,'#')
AND nvl(temp.segment21,'#') = nvl(p_segment21,'#')
AND nvl(temp.segment22,'#') = nvl(p_segment22,'#')
AND nvl(temp.segment23,'#') = nvl(p_segment23,'#')
AND nvl(temp.segment24,'#') = nvl(p_segment24,'#')
AND nvl(temp.segment25,'#') = nvl(p_segment25,'#')
AND nvl(temp.segment26,'#') = nvl(p_segment26,'#')
AND nvl(temp.segment27,'#') = nvl(p_segment27,'#')
AND nvl(temp.segment28,'#') = nvl(p_segment28,'#')
AND nvl(temp.segment29,'#') = nvl(p_segment29,'#')
AND nvl(temp.segment30,'#') = nvl(p_segment30,'#')
AND temp.code_combination_id = -1
AND temp.code_combination_status_code = C_CREATED
AND temp.balance_type_code <> 'X'
AND hdr.entity_id IS NOT NULL
AND hdr.event_id IS NOT NULL
AND hdr.ledger_id IS NOT NULL
)
LOOP
xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
SELECT DISTINCT
temp.segment1 segment1,
temp.segment2 segment2,
temp.segment3 segment3,
temp.segment4 segment4,
temp.segment5 segment5,
temp.segment6 segment6,
temp.segment7 segment7,
temp.segment8 segment8,
temp.segment9 segment9,
temp.segment10 segment10,
temp.segment11 segment11,
temp.segment12 segment12,
temp.segment13 segment13,
temp.segment14 segment14,
temp.segment15 segment15,
temp.segment16 segment16,
temp.segment17 segment17,
temp.segment18 segment18,
temp.segment19 segment19,
temp.segment20 segment20,
temp.segment21 segment21,
temp.segment22 segment22,
temp.segment23 segment23,
temp.segment24 segment24,
temp.segment25 segment25,
temp.segment26 segment26,
temp.segment27 segment27,
temp.segment28 segment28,
temp.segment29 segment29,
temp.segment30 segment30,
temp.ccid_coa_id coa_id
FROM xla_ae_lines_gt temp
WHERE temp.code_combination_id = -1
AND temp.code_combination_status_code = C_CREATED
AND temp.balance_type_code <> 'X'
)
LOOP
l_SegmentArray := init_SegmentArray(
p_segment1 => ccid_rec.segment1
, p_segment2 => ccid_rec.segment2
, p_segment3 => ccid_rec.segment3
, p_segment4 => ccid_rec.segment4
, p_segment5 => ccid_rec.segment5
, p_segment6 => ccid_rec.segment6
, p_segment7 => ccid_rec.segment7
, p_segment8 => ccid_rec.segment8
, p_segment9 => ccid_rec.segment9
, p_segment10 => ccid_rec.segment10
, p_segment11 => ccid_rec.segment11
, p_segment12 => ccid_rec.segment12
, p_segment13 => ccid_rec.segment13
, p_segment14 => ccid_rec.segment14
, p_segment15 => ccid_rec.segment15
, p_segment16 => ccid_rec.segment16
, p_segment17 => ccid_rec.segment17
, p_segment18 => ccid_rec.segment18
, p_segment19 => ccid_rec.segment19
, p_segment20 => ccid_rec.segment20
, p_segment21 => ccid_rec.segment21
, p_segment22 => ccid_rec.segment22
, p_segment23 => ccid_rec.segment23
, p_segment24 => ccid_rec.segment24
, p_segment25 => ccid_rec.segment25
, p_segment26 => ccid_rec.segment26
, p_segment27 => ccid_rec.segment27
, p_segment28 => ccid_rec.segment28
, p_segment29 => ccid_rec.segment29
, p_segment30 => ccid_rec.segment30
, p_flex_application_id => 101
, p_application_short_name => 'SQLGL'
, p_id_flex_code =>'GL#'
, p_id_flex_num => ccid_rec.coa_id
);
| It returns the number of rows updated |
| |
+------------------------------------------------------------------*/
FUNCTION validate_source_ccid
RETURN NUMBER
IS
l_log_module VARCHAR2(240);
(p_msg => 'Validate the accounting ccids: SQL - Update xla_ae_lines_gt '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_ae_lines_gt temp
SET code_combination_status_code =
CASE
WHEN temp.code_combination_id IS NOT NULL
AND temp.code_combination_status_code = C_NOT_PROCESSED
AND temp.code_combination_id <> -1
AND temp.balance_type_code <> 'X'
AND NOT EXISTS
(SELECT 'x'
FROM gl_code_combinations gl
WHERE gl.code_combination_id = temp.code_combination_id
AND gl.chart_of_accounts_id = temp.ccid_coa_id
AND gl.template_id IS NULL)
THEN C_INVALID
ELSE code_combination_status_code
END
,alt_ccid_status_code =
CASE
WHEN temp.alt_code_combination_id IS NOT NULL
AND temp.alt_ccid_status_code = C_NOT_PROCESSED
AND temp.alt_code_combination_id <> -1
AND temp.balance_type_code <> 'X'
AND NOT EXISTS
(SELECT 'x'
FROM gl_code_combinations gl
WHERE gl.code_combination_id = temp.alt_code_combination_id
AND gl.chart_of_accounts_id = temp.ccid_coa_id
AND gl.template_id IS NULL)
THEN C_INVALID
ELSE alt_ccid_status_code
END
WHERE
(temp.code_combination_id IS NOT NULL
AND temp.code_combination_status_code = C_NOT_PROCESSED
AND temp.code_combination_id <> -1
AND temp.balance_type_code <> 'X'
AND NOT EXISTS (SELECT 'x'
FROM gl_code_combinations gl
WHERE gl.code_combination_id = temp.code_combination_id
AND gl.chart_of_accounts_id = temp.ccid_coa_id
AND gl.template_id IS NULL
))
OR
(temp.alt_code_combination_id IS NOT NULL
AND temp.alt_ccid_status_code = C_NOT_PROCESSED
AND temp.alt_code_combination_id <> -1
AND temp.balance_type_code <> 'X'
AND NOT EXISTS (SELECT 'x'
FROM gl_code_combinations gl
WHERE gl.code_combination_id = temp.alt_code_combination_id
AND gl.chart_of_accounts_id = temp.ccid_coa_id
AND gl.template_id IS NULL
))
;
(p_msg => '# rows Updated in xla_ae_lines_gt (ccid + ALT ccid) ='||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT event_id
,entity_id
,ledger_id
,ae_header_id
,ccid
FROM (
--accounting ccid
SELECT DISTINCT
hdr.event_id event_id,
hdr.entity_id entity_id,
hdr.ledger_id ledger_id,
hdr.ae_header_id ae_header_id,
lns.code_combination_id ccid
FROM xla_ae_lines_gt lns,
xla_ae_headers_gt hdr
WHERE lns.ae_header_id = hdr.ae_header_id
AND lns.code_combination_id <> -1
AND lns.code_combination_status_code = C_INVALID
AND lns.balance_type_code <> 'X'
AND hdr.entity_id IS NOT NULL
AND hdr.event_id IS NOT NULL
AND hdr.ledger_id IS NOT NULL
UNION
--accounting ALT ccid
SELECT DISTINCT
hdr.event_id event_id,
hdr.entity_id entity_id,
hdr.ledger_id ledger_id,
hdr.ae_header_id ae_header_id,
lns.alt_code_combination_id ccid
FROM xla_ae_lines_gt lns,
xla_ae_headers_gt hdr
WHERE lns.ae_header_id = hdr.ae_header_id
AND lns.alt_code_combination_id <> -1
AND lns.alt_ccid_status_code = C_INVALID
AND lns.balance_type_code <> 'X'
AND hdr.entity_id IS NOT NULL
AND hdr.event_id IS NOT NULL
AND hdr.ledger_id IS NOT NULL
)
)
LOOP
xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
| updated. |
| |
+------------------------------------------------------------------*/
FUNCTION override_ccid
RETURN NUMBER
IS
l_log_module VARCHAR2(240);
(p_msg => 'Override the accounting ccid: SQL - Update xla_ae_lines_gt '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_ae_lines_gt temp
SET
( segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, segment7
, segment8
, segment9
, segment10
, segment11
, segment12
, segment13
, segment14
, segment15
, segment16
, segment17
, segment18
, segment19
, segment20
, segment21
, segment22
, segment23
, segment24
, segment25
, segment26
, segment27
, segment28
, segment29
, segment30
, code_combination_status_code
)
= (
SELECT
nvl(temp.segment1 , gl.segment1)
, nvl(temp.segment2 , gl.segment2)
, nvl(temp.segment3 , gl.segment3)
, nvl(temp.segment4 , gl.segment4)
, nvl(temp.segment5 , gl.segment5)
, nvl(temp.segment6 , gl.segment6)
, nvl(temp.segment7 , gl.segment7)
, nvl(temp.segment8 , gl.segment8)
, nvl(temp.segment9 , gl.segment9)
, nvl(temp.segment10 , gl.segment10)
, nvl(temp.segment11 , gl.segment11)
, nvl(temp.segment12 , gl.segment12)
, nvl(temp.segment13 , gl.segment13)
, nvl(temp.segment14 , gl.segment14)
, nvl(temp.segment15 , gl.segment15)
, nvl(temp.segment16 , gl.segment16)
, nvl(temp.segment17 , gl.segment17)
, nvl(temp.segment18 , gl.segment18)
, nvl(temp.segment19 , gl.segment19)
, nvl(temp.segment20 , gl.segment20)
, nvl(temp.segment21 , gl.segment21)
, nvl(temp.segment22 , gl.segment22)
, nvl(temp.segment23 , gl.segment23)
, nvl(temp.segment24 , gl.segment24)
, nvl(temp.segment25 , gl.segment25)
, nvl(temp.segment26 , gl.segment26)
, nvl(temp.segment27 , gl.segment27)
, nvl(temp.segment28 , gl.segment28)
, nvl(temp.segment29 , gl.segment29)
, nvl(temp.segment30 , gl.segment30)
, C_PROCESSING
FROM gl_code_combinations gl
WHERE gl.code_combination_id = temp.code_combination_id
AND gl.chart_of_accounts_id = temp.ccid_coa_id
AND gl.template_id IS NULL
)
WHERE temp.code_combination_id IS NOT NULL
AND temp.code_combination_status_code = C_NOT_PROCESSED
AND temp.code_combination_id <> -1
;
(p_msg => '# rows updates = '||TO_CHAR(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'Override the accounting ALT ccid: SQL - Update xla_ae_lines_gt '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_ae_lines_gt temp
SET
( alt_segment1
, alt_segment2
, alt_segment3
, alt_segment4
, alt_segment5
, alt_segment6
, alt_segment7
, alt_segment8
, alt_segment9
, alt_segment10
, alt_segment11
, alt_segment12
, alt_segment13
, alt_segment14
, alt_segment15
, alt_segment16
, alt_segment17
, alt_segment18
, alt_segment19
, alt_segment20
, alt_segment21
, alt_segment22
, alt_segment23
, alt_segment24
, alt_segment25
, alt_segment26
, alt_segment27
, alt_segment28
, alt_segment29
, alt_segment30
, alt_ccid_status_code
)
= (
SELECT
nvl(temp.alt_segment1 , gl.segment1)
, nvl(temp.alt_segment2 , gl.segment2)
, nvl(temp.alt_segment3 , gl.segment3)
, nvl(temp.alt_segment4 , gl.segment4)
, nvl(temp.alt_segment5 , gl.segment5)
, nvl(temp.alt_segment6 , gl.segment6)
, nvl(temp.alt_segment7 , gl.segment7)
, nvl(temp.alt_segment8 , gl.segment8)
, nvl(temp.alt_segment9 , gl.segment9)
, nvl(temp.alt_segment10 , gl.segment10)
, nvl(temp.alt_segment11 , gl.segment11)
, nvl(temp.alt_segment12 , gl.segment12)
, nvl(temp.alt_segment13 , gl.segment13)
, nvl(temp.alt_segment14 , gl.segment14)
, nvl(temp.alt_segment15 , gl.segment15)
, nvl(temp.alt_segment16 , gl.segment16)
, nvl(temp.alt_segment17 , gl.segment17)
, nvl(temp.alt_segment18 , gl.segment18)
, nvl(temp.alt_segment19 , gl.segment19)
, nvl(temp.alt_segment20 , gl.segment20)
, nvl(temp.alt_segment21 , gl.segment21)
, nvl(temp.alt_segment22 , gl.segment22)
, nvl(temp.alt_segment23 , gl.segment23)
, nvl(temp.alt_segment24 , gl.segment24)
, nvl(temp.alt_segment25 , gl.segment25)
, nvl(temp.alt_segment26 , gl.segment26)
, nvl(temp.alt_segment27 , gl.segment27)
, nvl(temp.alt_segment28 , gl.segment28)
, nvl(temp.alt_segment29 , gl.segment29)
, nvl(temp.alt_segment30 , gl.segment30)
, C_PROCESSING
FROM gl_code_combinations gl
WHERE gl.code_combination_id = temp.alt_code_combination_id
AND gl.chart_of_accounts_id = temp.ccid_coa_id
AND gl.template_id IS NULL
)
WHERE temp.alt_code_combination_id IS NOT NULL
AND temp.alt_ccid_status_code = C_NOT_PROCESSED
AND temp.alt_code_combination_id <> -1
;
(p_msg => '# rows updates = '||TO_CHAR(l_rowcount)||
' - return value. = '||to_char(l_return)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
| gl_code_combinations table. It returns the number of rows updated |
| |
+-----------------------------------------------------------------------*/
FUNCTION create_ccid
RETURN NUMBER
IS
l_upd_stmt VARCHAR2(20000);
l_upd_stmt := 'UPDATE xla_ae_lines_gt temp
SET code_combination_id =
CASE
WHEN temp.ccid_coa_id = :1
AND temp.code_combination_status_code = :2
AND temp.balance_type_code <> ''X''
THEN
(SELECT glc.code_combination_id
FROM gl_code_combinations glc
WHERE glc.chart_of_accounts_id = temp.ccid_coa_id
AND temp.ccid_coa_id = :3
AND glc.template_id IS NULL
' || l_sql_stmt || ' )
ELSE
code_combination_id
END
,code_combination_status_code =
CASE
WHEN temp.ccid_coa_id = :4
AND temp.code_combination_status_code = :5
AND temp.balance_type_code <> ''X''
THEN :6
ELSE
code_combination_status_code
END
,alt_code_combination_id =
CASE
WHEN temp.ccid_coa_id = :7
AND temp.alt_ccid_status_code = :8
AND temp.balance_type_code <> ''X''
THEN
(SELECT glc.code_combination_id
FROM gl_code_combinations glc
WHERE glc.chart_of_accounts_id = temp.ccid_coa_id
AND temp.ccid_coa_id = :9
AND glc.template_id IS NULL
' || l_alt_sql_stmt || ' )
ELSE
alt_code_combination_id
END
,alt_ccid_status_code =
CASE
WHEN temp.ccid_coa_id = :10
AND temp.alt_ccid_status_code = :11
AND temp.balance_type_code <> ''X''
THEN :12
ELSE
alt_ccid_status_code
END
WHERE temp.ccid_coa_id = :13
AND temp.balance_type_code <> ''X''
AND
(temp.code_combination_status_code = :14
OR temp.alt_ccid_status_code = :15) ';
(p_msg => '# rows updated (ccid + alt ccid) = '||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
| number of rows updated |
| |
+---------------------------------------------------------------*/
FUNCTION create_new_ccid
RETURN NUMBER
IS
l_log_module VARCHAR2(240);
UPDATE xla_ae_lines_gt temp
SET code_combination_id =
CASE
WHEN temp.code_combination_id IS NULL
AND temp.balance_type_code <> 'X'
THEN xla_ae_code_combination_pkg.GetCcid(
temp.segment1
,temp.segment2
,temp.segment3
,temp.segment4
,temp.segment5
,temp.segment6
,temp.segment7
,temp.segment8
,temp.segment9
,temp.segment10
,temp.segment11
,temp.segment12
,temp.segment13
,temp.segment14
,temp.segment15
,temp.segment16
,temp.segment17
,temp.segment18
,temp.segment19
,temp.segment20
,temp.segment21
,temp.segment22
,temp.segment23
,temp.segment24
,temp.segment25
,temp.segment26
,temp.segment27
,temp.segment28
,temp.segment29
,temp.segment30
,temp.ccid_coa_id
)
ELSE code_combination_id
END
,code_combination_status_code =
CASE
WHEN temp.code_combination_id IS NULL
AND temp.balance_type_code <> 'X'
THEN C_CREATED
ELSE code_combination_status_code
END
,alt_code_combination_id =
CASE
WHEN temp.alt_code_combination_id IS NULL
AND temp.balance_type_code <> 'X'
AND temp.gain_or_loss_flag = 'Y'
AND temp.CALCULATE_G_L_AMTS_FLAG = 'Y' --added for bug11727459
THEN xla_ae_code_combination_pkg.GetCcid(
temp.alt_segment1
,temp.alt_segment2
,temp.alt_segment3
,temp.alt_segment4
,temp.alt_segment5
,temp.alt_segment6
,temp.alt_segment7
,temp.alt_segment8
,temp.alt_segment9
,temp.alt_segment10
,temp.alt_segment11
,temp.alt_segment12
,temp.alt_segment13
,temp.alt_segment14
,temp.alt_segment15
,temp.alt_segment16
,temp.alt_segment17
,temp.alt_segment18
,temp.alt_segment19
,temp.alt_segment20
,temp.alt_segment21
,temp.alt_segment22
,temp.alt_segment23
,temp.alt_segment24
,temp.alt_segment25
,temp.alt_segment26
,temp.alt_segment27
,temp.alt_segment28
,temp.alt_segment29
,temp.alt_segment30
,temp.ccid_coa_id
)
ELSE alt_code_combination_id
END
,alt_ccid_status_code =
CASE
WHEN temp.alt_code_combination_id IS NULL
AND temp.balance_type_code <> 'X'
AND temp.gain_or_loss_flag = 'Y'
AND temp.CALCULATE_G_L_AMTS_FLAG = 'Y' --added for bug11727459
THEN C_CREATED
ELSE alt_ccid_status_code
END
WHERE temp.balance_type_code <> 'X'
AND (
(temp.code_combination_id IS NULL)
OR
(temp.alt_code_combination_id IS NULL
AND temp.gain_or_loss_flag = 'Y'
AND temp.CALCULATE_G_L_AMTS_FLAG = 'Y' ) --added for bug11727459
);
(p_msg => '# rows updated (ccid + ALT ccid)='||l_rowcount
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM gl_accts_map_int_gt; -- bug 4564062
INSERT INTO gl_accts_map_int_gt
(
from_ccid
, coa_mapping_id
)
SELECT code_combination_id
, sl_coa_mapping_id
FROM xla_transaction_accts_gt
WHERE code_combination_id IS NOT NULL
AND processing_status_code IN (C_MAP_CCID , C_MAP_SEGMENT)
AND sl_coa_mapping_id = p_gl_coa_mapping_id
GROUP BY code_combination_id, sl_coa_mapping_id
;
(p_msg => '# rows inserted into gl_accts_map_int_gt(ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
, create_ccid => ( NVL(g_cache_dynamic_inserts(p_gl_coa_mapping_id),'N') ='Y' )
, debug => g_log_enabled
);
UPDATE /*+ dynamic_sampling(1) */ xla_ae_lines_gt temp
SET ( temp.code_combination_id
,temp.segment1
,temp.segment2
,temp.segment3
,temp.segment4
,temp.segment5
,temp.segment6
,temp.segment7
,temp.segment8
,temp.segment9
,temp.segment10
,temp.segment11
,temp.segment12
,temp.segment13
,temp.segment14
,temp.segment15
,temp.segment16
,temp.segment17
,temp.segment18
,temp.segment19
,temp.segment20
,temp.segment21
,temp.segment22
,temp.segment23
,temp.segment24
,temp.segment25
,temp.segment26
,temp.segment27
,temp.segment28
,temp.segment29
,temp.segment30
,temp.code_combination_status_code) =
(
SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
DISTINCT
DECODE(gami.error_code, NULL, gami.to_ccid, -1)
, nvl(temp.segment1 , gami.to_segment1)
, nvl(temp.segment2 , gami.to_segment2)
, nvl(temp.segment3 , gami.to_segment3)
, nvl(temp.segment4 , gami.to_segment4)
, nvl(temp.segment5 , gami.to_segment5)
, nvl(temp.segment6 , gami.to_segment6)
, nvl(temp.segment7 , gami.to_segment7)
, nvl(temp.segment8 , gami.to_segment8)
, nvl(temp.segment9 , gami.to_segment9)
, nvl(temp.segment10, gami.to_segment10)
, nvl(temp.segment11, gami.to_segment11)
, nvl(temp.segment12, gami.to_segment12)
, nvl(temp.segment13, gami.to_segment13)
, nvl(temp.segment14, gami.to_segment14)
, nvl(temp.segment15, gami.to_segment15)
, nvl(temp.segment16, gami.to_segment16)
, nvl(temp.segment17, gami.to_segment17)
, nvl(temp.segment18, gami.to_segment18)
, nvl(temp.segment19, gami.to_segment19)
, nvl(temp.segment20, gami.to_segment20)
, nvl(temp.segment21, gami.to_segment21)
, nvl(temp.segment22, gami.to_segment22)
, nvl(temp.segment23, gami.to_segment23)
, nvl(temp.segment24, gami.to_segment24)
, nvl(temp.segment25, gami.to_segment25)
, nvl(temp.segment26, gami.to_segment26)
, nvl(temp.segment27, gami.to_segment27)
, nvl(temp.segment28, gami.to_segment28)
, nvl(temp.segment29, gami.to_segment29)
, nvl(temp.segment30, gami.to_segment30)
, CASE WHEN gami.error_code IS NULL
THEN CASE temp.code_combination_status_code
WHEN C_INVALID THEN C_CREATED
WHEN C_PROCESSING THEN C_NOT_PROCESSED
ELSE temp.code_combination_status_code
END
ELSE C_INVALID
END
FROM gl_accts_map_int_gt gami
, xla_transaction_accts_gt xta
WHERE xta.ae_header_id = temp.ae_header_id
AND xta.temp_line_num = temp.temp_line_num
AND xta.ledger_id = temp.ledger_id
AND xta.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND gami.from_ccid = xta.code_combination_id
AND gami.coa_mapping_id = xta.sl_coa_mapping_id
AND xta.processing_status_code = 'MAP_CCID'
AND xta.side_code IN ('ALL','CREDIT','NA')
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
AND temp.code_combination_id IS NULL
)
WHERE temp.code_combination_id IS NULL
AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
AND temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --added bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.processing_status_code = 'MAP_CCID'
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
)
;
(p_msg => '# of rows updated into xla_ae_lines_gt(ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE /*+ dynamic_sampling(1) */ xla_ae_lines_gt temp
SET ( temp.alt_code_combination_id
,temp.alt_segment1
,temp.alt_segment2
,temp.alt_segment3
,temp.alt_segment4
,temp.alt_segment5
,temp.alt_segment6
,temp.alt_segment7
,temp.alt_segment8
,temp.alt_segment9
,temp.alt_segment10
,temp.alt_segment11
,temp.alt_segment12
,temp.alt_segment13
,temp.alt_segment14
,temp.alt_segment15
,temp.alt_segment16
,temp.alt_segment17
,temp.alt_segment18
,temp.alt_segment19
,temp.alt_segment20
,temp.alt_segment21
,temp.alt_segment22
,temp.alt_segment23
,temp.alt_segment24
,temp.alt_segment25
,temp.alt_segment26
,temp.alt_segment27
,temp.alt_segment28
,temp.alt_segment29
,temp.alt_segment30
,temp.alt_ccid_status_code) =
(
SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
DISTINCT
DECODE(gami.error_code, NULL, gami.to_ccid, -1)
, nvl(temp.alt_segment1 , gami.to_segment1)
, nvl(temp.alt_segment2 , gami.to_segment2)
, nvl(temp.alt_segment3 , gami.to_segment3)
, nvl(temp.alt_segment4 , gami.to_segment4)
, nvl(temp.alt_segment5 , gami.to_segment5)
, nvl(temp.alt_segment6 , gami.to_segment6)
, nvl(temp.alt_segment7 , gami.to_segment7)
, nvl(temp.alt_segment8 , gami.to_segment8)
, nvl(temp.alt_segment9 , gami.to_segment9)
, nvl(temp.alt_segment10, gami.to_segment10)
, nvl(temp.alt_segment11, gami.to_segment11)
, nvl(temp.alt_segment12, gami.to_segment12)
, nvl(temp.alt_segment13, gami.to_segment13)
, nvl(temp.alt_segment14, gami.to_segment14)
, nvl(temp.alt_segment15, gami.to_segment15)
, nvl(temp.alt_segment16, gami.to_segment16)
, nvl(temp.alt_segment17, gami.to_segment17)
, nvl(temp.alt_segment18, gami.to_segment18)
, nvl(temp.alt_segment19, gami.to_segment19)
, nvl(temp.alt_segment20, gami.to_segment20)
, nvl(temp.alt_segment21, gami.to_segment21)
, nvl(temp.alt_segment22, gami.to_segment22)
, nvl(temp.alt_segment23, gami.to_segment23)
, nvl(temp.alt_segment24, gami.to_segment24)
, nvl(temp.alt_segment25, gami.to_segment25)
, nvl(temp.alt_segment26, gami.to_segment26)
, nvl(temp.alt_segment27, gami.to_segment27)
, nvl(temp.alt_segment28, gami.to_segment28)
, nvl(temp.alt_segment29, gami.to_segment29)
, nvl(temp.alt_segment30, gami.to_segment30)
, CASE WHEN gami.error_code IS NULL
THEN CASE temp.alt_ccid_status_code
WHEN C_INVALID THEN C_CREATED
WHEN C_PROCESSING THEN C_NOT_PROCESSED
ELSE temp.alt_ccid_status_code
END
ELSE C_INVALID
END
FROM gl_accts_map_int_gt gami
, xla_transaction_accts_gt xta
WHERE xta.ae_header_id = temp.ae_header_id
AND xta.temp_line_num = temp.temp_line_num
AND xta.ledger_id = temp.ledger_id
AND xta.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND gami.from_ccid = xta.code_combination_id
AND gami.coa_mapping_id = xta.sl_coa_mapping_id
AND xta.processing_status_code = 'MAP_CCID'
AND xta.side_code IN ('ALL','DEBIT')
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
AND temp.alt_code_combination_id IS NULL
)
WHERE temp.alt_code_combination_id IS NULL
AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
AND temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --added bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.processing_status_code = 'MAP_CCID'
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
)
;
(p_msg => '# of rows updated into xla_ae_lines_gt(ALT ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT coa_mapping_id,
from_coa_id,
to_coa_id,
start_date_active,
end_date_active
INTO l_coa_mapping_id,
l_from_coa_id,
l_to_coa_id,
l_start_date_active,
l_end_date_active
FROM gl_coa_mappings
WHERE name = p_gl_coa_mapping_name
AND coa_mapping_id=p_gl_coa_mapping_id;
SELECT DISTINCT
xta.ae_header_id ae_header_id
, xta.temp_line_num temp_line_num
, xta.ledger_id ledger_id
, xta.sl_coa_mapping_id sl_coa_mapping_id
, DECODE (gami.code_combination_id,NULL,C_INVALID,C_CREATED) gl_map_status
, xta.processing_status_code processing_status_code
, xta.to_segment_code to_segment_code
, NVL(gami.code_combination_id,-1) code_combination_id
, CASE xta.from_segment_code
WHEN 'SEGMENT1' THEN gami.segment1
WHEN 'SEGMENT2' THEN gami.segment2
WHEN 'SEGMENT3' THEN gami.segment3
WHEN 'SEGMENT4' THEN gami.segment4
WHEN 'SEGMENT5' THEN gami.segment5
WHEN 'SEGMENT6' THEN gami.segment6
WHEN 'SEGMENT7' THEN gami.segment7
WHEN 'SEGMENT8' THEN gami.segment8
WHEN 'SEGMENT9' THEN gami.segment9
WHEN 'SEGMENT10' THEN gami.segment10
WHEN 'SEGMENT11' THEN gami.segment11
WHEN 'SEGMENT12' THEN gami.segment12
WHEN 'SEGMENT13' THEN gami.segment13
WHEN 'SEGMENT14' THEN gami.segment14
WHEN 'SEGMENT15' THEN gami.segment15
WHEN 'SEGMENT16' THEN gami.segment16
WHEN 'SEGMENT17' THEN gami.segment17
WHEN 'SEGMENT18' THEN gami.segment18
WHEN 'SEGMENT19' THEN gami.segment19
WHEN 'SEGMENT20' THEN gami.segment20
WHEN 'SEGMENT21' THEN gami.segment21
WHEN 'SEGMENT22' THEN gami.segment22
WHEN 'SEGMENT23' THEN gami.segment23
WHEN 'SEGMENT24' THEN gami.segment24
WHEN 'SEGMENT25' THEN gami.segment25
WHEN 'SEGMENT26' THEN gami.segment26
WHEN 'SEGMENT27' THEN gami.segment27
WHEN 'SEGMENT28' THEN gami.segment28
WHEN 'SEGMENT29' THEN gami.segment29
WHEN 'SEGMENT30' THEN gami.segment30
END segment_value
BULK COLLECT INTO
l_array_header_id
, l_array_temp_line_num
, l_array_ledger_id
, l_array_coa_mapping_id
, l_array_gl_map_status
, l_array_processing_status_code
, l_array_to_segment_code
, l_array_ccid
, l_array_segment_value
FROM gl_code_combinations gami
, xla_transaction_accts_gt xta
WHERE gami.code_combination_id = xta.code_combination_id
AND gami.chart_of_accounts_id = l_from_coa_id
AND xta.code_combination_id IS NOT NULL
AND xta.from_segment_code IS NOT NULL
AND xta.to_segment_code IS NOT NULL
AND xta.processing_status_code = 'MAP_SEGMENT'
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xta.side_code IN ('ALL','CREDIT','NA');
SELECT DISTINCT
xta.ae_header_id ae_header_id
, xta.temp_line_num temp_line_num
, xta.ledger_id ledger_id
, xta.sl_coa_mapping_id sl_coa_mapping_id
, DECODE (gami.error_code ,NULL,C_CREATED,C_INVALID) gl_map_status
, xta.processing_status_code processing_status_code
, xta.to_segment_code to_segment_code
, DECODE(gami.error_code ,NULL,gami.to_ccid,-1) code_combination_id
, CASE xta.to_segment_code --xta.from_segment_code -- changed for bug 13735405
WHEN 'SEGMENT1' THEN gami.to_segment1
WHEN 'SEGMENT2' THEN gami.to_segment2
WHEN 'SEGMENT3' THEN gami.to_segment3
WHEN 'SEGMENT4' THEN gami.to_segment4
WHEN 'SEGMENT5' THEN gami.to_segment5
WHEN 'SEGMENT6' THEN gami.to_segment6
WHEN 'SEGMENT7' THEN gami.to_segment7
WHEN 'SEGMENT8' THEN gami.to_segment8
WHEN 'SEGMENT9' THEN gami.to_segment9
WHEN 'SEGMENT10' THEN gami.to_segment10
WHEN 'SEGMENT11' THEN gami.to_segment11
WHEN 'SEGMENT12' THEN gami.to_segment12
WHEN 'SEGMENT13' THEN gami.to_segment13
WHEN 'SEGMENT14' THEN gami.to_segment14
WHEN 'SEGMENT15' THEN gami.to_segment15
WHEN 'SEGMENT16' THEN gami.to_segment16
WHEN 'SEGMENT17' THEN gami.to_segment17
WHEN 'SEGMENT18' THEN gami.to_segment18
WHEN 'SEGMENT19' THEN gami.to_segment19
WHEN 'SEGMENT20' THEN gami.to_segment20
WHEN 'SEGMENT21' THEN gami.to_segment21
WHEN 'SEGMENT22' THEN gami.to_segment22
WHEN 'SEGMENT23' THEN gami.to_segment23
WHEN 'SEGMENT24' THEN gami.to_segment24
WHEN 'SEGMENT25' THEN gami.to_segment25
WHEN 'SEGMENT26' THEN gami.to_segment26
WHEN 'SEGMENT27' THEN gami.to_segment27
WHEN 'SEGMENT28' THEN gami.to_segment28
WHEN 'SEGMENT29' THEN gami.to_segment29
WHEN 'SEGMENT30' THEN gami.to_segment30
END segment_value
BULK COLLECT INTO
l_array_header_id
, l_array_temp_line_num
, l_array_ledger_id
, l_array_coa_mapping_id
, l_array_gl_map_status
, l_array_processing_status_code
, l_array_to_segment_code
, l_array_ccid
, l_array_segment_value
FROM gl_accts_map_int_gt gami
, xla_transaction_accts_gt xta
WHERE gami.from_ccid = xta.code_combination_id
AND gami.coa_mapping_id = xta.sl_coa_mapping_id
AND xta.code_combination_id IS NOT NULL
AND xta.from_segment_code IS NOT NULL
AND xta.to_segment_code IS NOT NULL
AND xta.processing_status_code = 'MAP_SEGMENT'
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xta.side_code IN ('ALL','CREDIT','NA');
UPDATE xla_ae_lines_gt temp
SET ( temp.code_combination_id
,temp.segment1
,temp.segment2
,temp.segment3
,temp.segment4
,temp.segment5
,temp.segment6
,temp.segment7
,temp.segment8
,temp.segment9
,temp.segment10
,temp.segment11
,temp.segment12
,temp.segment13
,temp.segment14
,temp.segment15
,temp.segment16
,temp.segment17
,temp.segment18
,temp.segment19
,temp.segment20
,temp.segment21
,temp.segment22
,temp.segment23
,temp.segment24
,temp.segment25
,temp.segment26
,temp.segment27
,temp.segment28
,temp.segment29
,temp.segment30
,temp.code_combination_status_code) =
(
SELECT /*+ INDEX(SEG XLA_TRANSACTION_ACCTS_GT_N1) */ DISTINCT --bug7673701
DECODE(l_array_gl_map_status(i), C_INVALID, -1, temp.code_combination_id)
, DECODE(seg.to_segment_code,'SEGMENT1' ,l_array_segment_value(i), temp.segment1)
, DECODE(seg.to_segment_code,'SEGMENT2' ,l_array_segment_value(i), temp.segment2)
, DECODE(seg.to_segment_code,'SEGMENT3' ,l_array_segment_value(i), temp.segment3)
, DECODE(seg.to_segment_code,'SEGMENT4' ,l_array_segment_value(i), temp.segment4)
, DECODE(seg.to_segment_code,'SEGMENT5' ,l_array_segment_value(i), temp.segment5)
, DECODE(seg.to_segment_code,'SEGMENT6' ,l_array_segment_value(i), temp.segment6)
, DECODE(seg.to_segment_code,'SEGMENT7' ,l_array_segment_value(i), temp.segment7)
, DECODE(seg.to_segment_code,'SEGMENT8' ,l_array_segment_value(i), temp.segment8)
, DECODE(seg.to_segment_code,'SEGMENT9' ,l_array_segment_value(i), temp.segment9)
, DECODE(seg.to_segment_code,'SEGMENT10',l_array_segment_value(i), temp.segment10)
, DECODE(seg.to_segment_code,'SEGMENT11',l_array_segment_value(i), temp.segment11)
, DECODE(seg.to_segment_code,'SEGMENT12',l_array_segment_value(i), temp.segment12)
, DECODE(seg.to_segment_code,'SEGMENT13',l_array_segment_value(i), temp.segment13)
, DECODE(seg.to_segment_code,'SEGMENT14',l_array_segment_value(i), temp.segment14)
, DECODE(seg.to_segment_code,'SEGMENT15',l_array_segment_value(i), temp.segment15)
, DECODE(seg.to_segment_code,'SEGMENT16',l_array_segment_value(i), temp.segment16)
, DECODE(seg.to_segment_code,'SEGMENT17',l_array_segment_value(i), temp.segment17)
, DECODE(seg.to_segment_code,'SEGMENT18',l_array_segment_value(i), temp.segment18)
, DECODE(seg.to_segment_code,'SEGMENT19',l_array_segment_value(i), temp.segment19)
, DECODE(seg.to_segment_code,'SEGMENT20',l_array_segment_value(i), temp.segment20)
, DECODE(seg.to_segment_code,'SEGMENT21',l_array_segment_value(i), temp.segment21)
, DECODE(seg.to_segment_code,'SEGMENT22',l_array_segment_value(i), temp.segment22)
, DECODE(seg.to_segment_code,'SEGMENT23',l_array_segment_value(i), temp.segment23)
, DECODE(seg.to_segment_code,'SEGMENT24',l_array_segment_value(i), temp.segment24)
, DECODE(seg.to_segment_code,'SEGMENT25',l_array_segment_value(i), temp.segment25)
, DECODE(seg.to_segment_code,'SEGMENT26',l_array_segment_value(i), temp.segment26)
, DECODE(seg.to_segment_code,'SEGMENT27',l_array_segment_value(i), temp.segment27)
, DECODE(seg.to_segment_code,'SEGMENT28',l_array_segment_value(i), temp.segment28)
, DECODE(seg.to_segment_code,'SEGMENT29',l_array_segment_value(i), temp.segment29)
, DECODE(seg.to_segment_code,'SEGMENT30',l_array_segment_value(i), temp.segment30)
, CASE l_array_gl_map_status(i)
WHEN C_INVALID THEN C_INVALID
ELSE CASE temp.code_combination_status_code
WHEN C_INVALID THEN C_PROCESSING
WHEN C_CREATED THEN C_NOT_PROCESSED
ELSE temp.code_combination_status_code
END
END
FROM xla_transaction_accts_gt seg
WHERE seg.ae_header_id = temp.ae_header_id
AND seg.temp_line_num = temp.temp_line_num
AND seg.ledger_id = temp.ledger_id
AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND seg.ae_header_id = l_array_header_id(i)
AND seg.temp_line_num = l_array_temp_line_num(i)
AND seg.ledger_id = l_array_ledger_id(i)
AND seg.sl_coa_mapping_id = l_array_coa_mapping_id(i)
AND seg.to_segment_code = l_array_to_segment_code(i) --added 6660472 suggested by Kaouther
AND seg.processing_status_code = l_array_processing_status_code(i)--added for bug6314762 to avoid single row subquery returns more than one row error
)
WHERE temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX(t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.processing_status_code = 'MAP_SEGMENT'
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
AND t.ae_header_id = l_array_header_id(i)
AND t.temp_line_num = l_array_temp_line_num(i)
AND t.ledger_id = l_array_ledger_id(i)
AND t.to_segment_code = l_array_to_segment_code(i) --added 6660472 suggested by Kaouther
AND t.sl_coa_mapping_id = l_array_coa_mapping_id(i))
;
(p_msg => '# of rows updated into xla_ae_lines_gt(ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT DISTINCT
xta.ae_header_id ae_header_id
, xta.temp_line_num temp_line_num
, xta.ledger_id ledger_id
, xta.sl_coa_mapping_id sl_coa_mapping_id
, DECODE (gami.error_code ,NULL,C_CREATED,C_INVALID) gl_map_status
, xta.processing_status_code processing_status_code
, xta.to_segment_code to_segment_code
, DECODE(gami.error_code ,NULL,gami.to_ccid,-1) code_combination_id
, CASE xta.to_segment_code -- xta.from_segment_code -- changed for bug 13735405
WHEN 'SEGMENT1' THEN gami.to_segment1
WHEN 'SEGMENT2' THEN gami.to_segment2
WHEN 'SEGMENT3' THEN gami.to_segment3
WHEN 'SEGMENT4' THEN gami.to_segment4
WHEN 'SEGMENT5' THEN gami.to_segment5
WHEN 'SEGMENT6' THEN gami.to_segment6
WHEN 'SEGMENT7' THEN gami.to_segment7
WHEN 'SEGMENT8' THEN gami.to_segment8
WHEN 'SEGMENT9' THEN gami.to_segment9
WHEN 'SEGMENT10' THEN gami.to_segment10
WHEN 'SEGMENT11' THEN gami.to_segment11
WHEN 'SEGMENT12' THEN gami.to_segment12
WHEN 'SEGMENT13' THEN gami.to_segment13
WHEN 'SEGMENT14' THEN gami.to_segment14
WHEN 'SEGMENT15' THEN gami.to_segment15
WHEN 'SEGMENT16' THEN gami.to_segment16
WHEN 'SEGMENT17' THEN gami.to_segment17
WHEN 'SEGMENT18' THEN gami.to_segment18
WHEN 'SEGMENT19' THEN gami.to_segment19
WHEN 'SEGMENT20' THEN gami.to_segment20
WHEN 'SEGMENT21' THEN gami.to_segment21
WHEN 'SEGMENT22' THEN gami.to_segment22
WHEN 'SEGMENT23' THEN gami.to_segment23
WHEN 'SEGMENT24' THEN gami.to_segment24
WHEN 'SEGMENT25' THEN gami.to_segment25
WHEN 'SEGMENT26' THEN gami.to_segment26
WHEN 'SEGMENT27' THEN gami.to_segment27
WHEN 'SEGMENT28' THEN gami.to_segment28
WHEN 'SEGMENT29' THEN gami.to_segment29
WHEN 'SEGMENT30' THEN gami.to_segment30
END segment_value
BULK COLLECT INTO
l_array_alt_header_id
, l_array_alt_temp_line_num
, l_array_alt_ledger_id
, l_array_alt_coa_mapping_id
, l_array_alt_gl_map_status
, l_array_alt_proc_status_code
, l_array_alt_to_segment_code
, l_array_alt_ccid
, l_array_alt_segment_value
FROM gl_accts_map_int_gt gami
, xla_transaction_accts_gt xta
WHERE gami.from_ccid = xta.code_combination_id
AND gami.coa_mapping_id = xta.sl_coa_mapping_id
AND xta.code_combination_id IS NOT NULL
AND xta.from_segment_code IS NOT NULL
AND xta.to_segment_code IS NOT NULL
AND xta.processing_status_code = 'MAP_SEGMENT'
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xta.side_code IN ('ALL','DEBIT');
UPDATE xla_ae_lines_gt temp
SET ( temp.alt_code_combination_id
,temp.alt_segment1
,temp.alt_segment2
,temp.alt_segment3
,temp.alt_segment4
,temp.alt_segment5
,temp.alt_segment6
,temp.alt_segment7
,temp.alt_segment8
,temp.alt_segment9
,temp.alt_segment10
,temp.alt_segment11
,temp.alt_segment12
,temp.alt_segment13
,temp.alt_segment14
,temp.alt_segment15
,temp.alt_segment16
,temp.alt_segment17
,temp.alt_segment18
,temp.alt_segment19
,temp.alt_segment20
,temp.alt_segment21
,temp.alt_segment22
,temp.alt_segment23
,temp.alt_segment24
,temp.alt_segment25
,temp.alt_segment26
,temp.alt_segment27
,temp.alt_segment28
,temp.alt_segment29
,temp.alt_segment30
,temp.alt_ccid_status_code) =
(
SELECT /*+ INDEX(SEG XLA_TRANSACTION_ACCTS_GT_N1) */ DISTINCT --bug7673701
DECODE(l_array_alt_gl_map_status(i), C_INVALID, -1, temp.alt_code_combination_id)
, DECODE(seg.to_segment_code,'SEGMENT1' ,l_array_alt_segment_value(i), temp.alt_segment1)
, DECODE(seg.to_segment_code,'SEGMENT2' ,l_array_alt_segment_value(i), temp.alt_segment2)
, DECODE(seg.to_segment_code,'SEGMENT3' ,l_array_alt_segment_value(i), temp.alt_segment3)
, DECODE(seg.to_segment_code,'SEGMENT4' ,l_array_alt_segment_value(i), temp.alt_segment4)
, DECODE(seg.to_segment_code,'SEGMENT5' ,l_array_alt_segment_value(i), temp.alt_segment5)
, DECODE(seg.to_segment_code,'SEGMENT6' ,l_array_alt_segment_value(i), temp.alt_segment6)
, DECODE(seg.to_segment_code,'SEGMENT7' ,l_array_alt_segment_value(i), temp.alt_segment7)
, DECODE(seg.to_segment_code,'SEGMENT8' ,l_array_alt_segment_value(i), temp.alt_segment8)
, DECODE(seg.to_segment_code,'SEGMENT9' ,l_array_alt_segment_value(i), temp.alt_segment9)
, DECODE(seg.to_segment_code,'SEGMENT10',l_array_alt_segment_value(i), temp.alt_segment10)
, DECODE(seg.to_segment_code,'SEGMENT11',l_array_alt_segment_value(i), temp.alt_segment11)
, DECODE(seg.to_segment_code,'SEGMENT12',l_array_alt_segment_value(i), temp.alt_segment12)
, DECODE(seg.to_segment_code,'SEGMENT13',l_array_alt_segment_value(i), temp.alt_segment13)
, DECODE(seg.to_segment_code,'SEGMENT14',l_array_alt_segment_value(i), temp.alt_segment14)
, DECODE(seg.to_segment_code,'SEGMENT15',l_array_alt_segment_value(i), temp.alt_segment15)
, DECODE(seg.to_segment_code,'SEGMENT16',l_array_alt_segment_value(i), temp.alt_segment16)
, DECODE(seg.to_segment_code,'SEGMENT17',l_array_alt_segment_value(i), temp.alt_segment17)
, DECODE(seg.to_segment_code,'SEGMENT18',l_array_alt_segment_value(i), temp.alt_segment18)
, DECODE(seg.to_segment_code,'SEGMENT19',l_array_alt_segment_value(i), temp.alt_segment19)
, DECODE(seg.to_segment_code,'SEGMENT20',l_array_alt_segment_value(i), temp.alt_segment20)
, DECODE(seg.to_segment_code,'SEGMENT21',l_array_alt_segment_value(i), temp.alt_segment21)
, DECODE(seg.to_segment_code,'SEGMENT22',l_array_alt_segment_value(i), temp.alt_segment22)
, DECODE(seg.to_segment_code,'SEGMENT23',l_array_alt_segment_value(i), temp.alt_segment23)
, DECODE(seg.to_segment_code,'SEGMENT24',l_array_alt_segment_value(i), temp.alt_segment24)
, DECODE(seg.to_segment_code,'SEGMENT25',l_array_alt_segment_value(i), temp.alt_segment25)
, DECODE(seg.to_segment_code,'SEGMENT26',l_array_alt_segment_value(i), temp.alt_segment26)
, DECODE(seg.to_segment_code,'SEGMENT27',l_array_alt_segment_value(i), temp.alt_segment27)
, DECODE(seg.to_segment_code,'SEGMENT28',l_array_alt_segment_value(i), temp.alt_segment28)
, DECODE(seg.to_segment_code,'SEGMENT29',l_array_alt_segment_value(i), temp.alt_segment29)
, DECODE(seg.to_segment_code,'SEGMENT30',l_array_alt_segment_value(i), temp.alt_segment30)
, CASE l_array_alt_gl_map_status(i)
WHEN C_INVALID THEN C_INVALID
ELSE CASE temp.alt_ccid_status_code
WHEN C_INVALID THEN C_PROCESSING
WHEN C_CREATED THEN C_NOT_PROCESSED
ELSE temp.alt_ccid_status_code
END
END
FROM xla_transaction_accts_gt seg
WHERE seg.ae_header_id = temp.ae_header_id
AND seg.temp_line_num = temp.temp_line_num
AND seg.ledger_id = temp.ledger_id
AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND seg.ae_header_id = l_array_alt_header_id(i)
AND seg.temp_line_num = l_array_alt_temp_line_num(i)
AND seg.ledger_id = l_array_alt_ledger_id(i)
AND seg.sl_coa_mapping_id = l_array_alt_coa_mapping_id(i)
AND seg.to_segment_code = l_array_alt_to_segment_code(i) --added 6660472 suggested by Kaouther
--AND seg.processing_status_code = l_array_processing_status_code(i) --added by for bug6314762 to avoid single row subquery returns more than one row error
AND seg.processing_status_code = l_array_alt_proc_status_code(i) --corrected bug 8757043
)
WHERE temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX(t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.processing_status_code = 'MAP_SEGMENT'
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
AND t.ae_header_id = l_array_alt_header_id(i)
AND t.temp_line_num = l_array_alt_temp_line_num(i)
AND t.ledger_id = l_array_alt_ledger_id(i)
AND t.to_segment_code = l_array_alt_to_segment_code(i) --added 6660472 suggested by Kaouther
AND t.sl_coa_mapping_id = l_array_alt_coa_mapping_id(i) )
;
(p_msg => '# of rows updated into xla_ae_lines_gt(ALT ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
SELECT error_code
,event_id
,ledger_id
,entity_id
,from_ccid
,ccid_coa_id
FROM (SELECT /*+ dynamic_sampling(1) INDEX (XTAG XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTAG,GAMI) */ DISTINCT
gami.error_code error_code
,xjlg.event_id event_id
,xjlg.ledger_id ledger_id
,xjlg.entity_id entity_id
,gami.from_ccid from_ccid
,xjlg.ccid_coa_id ccid_coa_id
FROM gl_accts_map_int_gt gami
,xla_transaction_accts_gt xtag
,xla_ae_lines_gt xjlg
WHERE xjlg.ae_header_id = xtag.ae_header_id
AND xjlg.temp_line_num = xtag.temp_line_num
AND xjlg.ledger_id = xtag.ledger_id
AND xjlg.sl_coa_mapping_id = xtag.sl_coa_mapping_id
AND gami.from_ccid = xtag.code_combination_id
AND gami.coa_mapping_id = xtag.sl_coa_mapping_id
AND xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xtag.processing_status_code IN ('MAP_CCID','MAP_SEGMENT')
AND xtag.side_code IN ('ALL','CREDIT','NA')
AND gami.error_code IS NOT NULL
AND xjlg.code_combination_id = -1
AND xjlg.balance_type_code <> 'X'
UNION
SELECT /*+ dynamic_sampling(1) INDEX (XTAG XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTAG,GAMI) */ DISTINCT
gami.error_code error_code
,xjlg.event_id event_id
,xjlg.ledger_id ledger_id
,xjlg.entity_id entity_id
,gami.from_ccid from_ccid
, xjlg.ccid_coa_id coa_id
FROM gl_accts_map_int_gt gami
,xla_transaction_accts_gt xtag
,xla_ae_lines_gt xjlg
WHERE xjlg.ae_header_id = xtag.ae_header_id
AND xjlg.temp_line_num = xtag.temp_line_num
AND xjlg.ledger_id = xtag.ledger_id
AND xjlg.sl_coa_mapping_id = xtag.sl_coa_mapping_id
AND gami.from_ccid = xtag.code_combination_id
AND gami.coa_mapping_id = xtag.sl_coa_mapping_id
AND xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xtag.processing_status_code IN ('MAP_CCID','MAP_SEGMENT')
AND xtag.side_code IN ('ALL','DEBIT')
AND gami.error_code IS NOT NULL
AND xjlg.alt_code_combination_id = -1
AND xjlg.balance_type_code <> 'X'
)
)
LOOP
/*
--added bug 6666983,account value should be displayed in error message even if ccid is invalid
SELECT concatenated_segments
INTO l_ConcatKey
FROM gl_code_combinations_kfv
WHERE code_combination_id = error_rec.from_ccid;
FOR qualifier_rec IN (SELECT xtag.from_segment_code qualifier
FROM xla_transaction_accts_gt xtag
WHERE xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xtag.processing_status_code = 'MAP_QUALIFIER'
GROUP BY xtag.from_segment_code )
LOOP
-- reset the GT table
DELETE from gl_accts_map_bsv_gt;
INSERT INTO gl_accts_map_bsv_gt
( SOURCE_BSV )
SELECT segment
FROM xla_transaction_accts_gt xtag
WHERE xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
AND xtag.processing_status_code = 'MAP_QUALIFIER'
AND xtag.from_segment_code = qualifier_rec.qualifier
GROUP BY segment
;
UPDATE xla_ae_lines_gt temp
SET temp.code_combination_id = -1
,temp.code_combination_status_code = C_INVALID
WHERE temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.from_segment_code = qualifier_rec.qualifier
AND t.processing_status_code = 'MAP_QUALIFIER'
AND t.side_code IN ('ALL','CREDIT','NA')
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
)
RETURNING entity_id, event_id, ledger_id BULK COLLECT
INTO l_array_entity_id, l_array_event_id, l_array_ledger_id
;
(p_msg => '# of rows updated into xla_ae_lines_gt(error) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
l_array_entity_id.DELETE ;
l_array_event_id.DELETE;
l_array_ledger_id.DELETE;
UPDATE xla_ae_lines_gt temp
SET temp.alt_code_combination_id = -1
,temp.alt_ccid_status_code = C_INVALID
WHERE temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.from_segment_code = qualifier_rec.qualifier
AND t.processing_status_code = 'MAP_QUALIFIER'
AND t.side_code IN ('ALL','DEBIT')
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
)
RETURNING entity_id, event_id, ledger_id BULK COLLECT
INTO l_array_entity_id, l_array_event_id, l_array_ledger_id
;
(p_msg => '# of rows updated into xla_ae_lines_gt(error) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_ae_lines_gt temp
SET ( temp.segment1
,temp.segment2
,temp.segment3
,temp.segment4
,temp.segment5
,temp.segment6
,temp.segment7
,temp.segment8
,temp.segment9
,temp.segment10
,temp.segment11
,temp.segment12
,temp.segment13
,temp.segment14
,temp.segment15
,temp.segment16
,temp.segment17
,temp.segment18
,temp.segment19
,temp.segment20
,temp.segment21
,temp.segment22
,temp.segment23
,temp.segment24
,temp.segment25
,temp.segment26
,temp.segment27
,temp.segment28
,temp.segment29
,temp.segment30
,temp.code_combination_status_code) =
(
SELECT DISTINCT
DECODE(seg.to_segment_code,'SEGMENT1' ,seg.target_value, temp.segment1)
, DECODE(seg.to_segment_code,'SEGMENT2' ,seg.target_value, temp.segment2)
, DECODE(seg.to_segment_code,'SEGMENT3' ,seg.target_value, temp.segment3)
, DECODE(seg.to_segment_code,'SEGMENT4' ,seg.target_value, temp.segment4)
, DECODE(seg.to_segment_code,'SEGMENT5' ,seg.target_value, temp.segment5)
, DECODE(seg.to_segment_code,'SEGMENT6' ,seg.target_value, temp.segment6)
, DECODE(seg.to_segment_code,'SEGMENT7' ,seg.target_value, temp.segment7)
, DECODE(seg.to_segment_code,'SEGMENT8' ,seg.target_value, temp.segment8)
, DECODE(seg.to_segment_code,'SEGMENT9' ,seg.target_value, temp.segment9)
, DECODE(seg.to_segment_code,'SEGMENT10',seg.target_value, temp.segment10)
, DECODE(seg.to_segment_code,'SEGMENT11',seg.target_value, temp.segment11)
, DECODE(seg.to_segment_code,'SEGMENT12',seg.target_value, temp.segment12)
, DECODE(seg.to_segment_code,'SEGMENT13',seg.target_value, temp.segment13)
, DECODE(seg.to_segment_code,'SEGMENT14',seg.target_value, temp.segment14)
, DECODE(seg.to_segment_code,'SEGMENT15',seg.target_value, temp.segment15)
, DECODE(seg.to_segment_code,'SEGMENT16',seg.target_value, temp.segment16)
, DECODE(seg.to_segment_code,'SEGMENT17',seg.target_value, temp.segment17)
, DECODE(seg.to_segment_code,'SEGMENT18',seg.target_value, temp.segment18)
, DECODE(seg.to_segment_code,'SEGMENT19',seg.target_value, temp.segment19)
, DECODE(seg.to_segment_code,'SEGMENT20',seg.target_value, temp.segment20)
, DECODE(seg.to_segment_code,'SEGMENT21',seg.target_value, temp.segment21)
, DECODE(seg.to_segment_code,'SEGMENT22',seg.target_value, temp.segment22)
, DECODE(seg.to_segment_code,'SEGMENT23',seg.target_value, temp.segment23)
, DECODE(seg.to_segment_code,'SEGMENT24',seg.target_value, temp.segment24)
, DECODE(seg.to_segment_code,'SEGMENT25',seg.target_value, temp.segment25)
, DECODE(seg.to_segment_code,'SEGMENT26',seg.target_value, temp.segment26)
, DECODE(seg.to_segment_code,'SEGMENT27',seg.target_value, temp.segment27)
, DECODE(seg.to_segment_code,'SEGMENT28',seg.target_value, temp.segment28)
, DECODE(seg.to_segment_code,'SEGMENT29',seg.target_value, temp.segment29)
, DECODE(seg.to_segment_code,'SEGMENT30',seg.target_value, temp.segment30)
, CASE temp.code_combination_status_code
WHEN C_INVALID THEN C_PROCESSING
WHEN C_CREATED THEN C_NOT_PROCESSED
ELSE temp.code_combination_status_code
END
FROM (
SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */ DISTINCT --bug7673701
xta.ae_header_id ae_header_id
, xta.temp_line_num temp_line_num
, xta.ledger_id ledger_id
, xta.sl_coa_mapping_id sl_coa_mapping_id
, xta.to_segment_code to_segment_code
, gami.target_bsv target_value
FROM gl_accts_map_bsv_gt gami
, xla_transaction_accts_gt xta
WHERE gami.source_bsv = xta.segment
AND xta.from_segment_code = qualifier_rec.qualifier
AND xta.processing_status_code = 'MAP_QUALIFIER'
AND xta.side_code IN ('ALL','CREDIT','NA')
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
) seg
WHERE seg.ae_header_id = temp.ae_header_id
AND seg.temp_line_num = temp.temp_line_num
AND seg.ledger_id = temp.ledger_id
AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
)
WHERE temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.from_segment_code = qualifier_rec.qualifier
AND t.processing_status_code = 'MAP_QUALIFIER'
AND t.side_code IN ('ALL','CREDIT','NA')
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id)
;
(p_msg => '# of rows updated into xla_ae_lines_gt(ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_ae_lines_gt temp
SET ( temp.alt_segment1
,temp.alt_segment2
,temp.alt_segment3
,temp.alt_segment4
,temp.alt_segment5
,temp.alt_segment6
,temp.alt_segment7
,temp.alt_segment8
,temp.alt_segment9
,temp.alt_segment10
,temp.alt_segment11
,temp.alt_segment12
,temp.alt_segment13
,temp.alt_segment14
,temp.alt_segment15
,temp.alt_segment16
,temp.alt_segment17
,temp.alt_segment18
,temp.alt_segment19
,temp.alt_segment20
,temp.alt_segment21
,temp.alt_segment22
,temp.alt_segment23
,temp.alt_segment24
,temp.alt_segment25
,temp.alt_segment26
,temp.alt_segment27
,temp.alt_segment28
,temp.alt_segment29
,temp.alt_segment30
,temp.alt_ccid_status_code) =
(
SELECT DISTINCT
DECODE(seg.to_segment_code,'SEGMENT1' ,seg.target_value, temp.alt_segment1)
, DECODE(seg.to_segment_code,'SEGMENT2' ,seg.target_value, temp.alt_segment2)
, DECODE(seg.to_segment_code,'SEGMENT3' ,seg.target_value, temp.alt_segment3)
, DECODE(seg.to_segment_code,'SEGMENT4' ,seg.target_value, temp.alt_segment4)
, DECODE(seg.to_segment_code,'SEGMENT5' ,seg.target_value, temp.alt_segment5)
, DECODE(seg.to_segment_code,'SEGMENT6' ,seg.target_value, temp.alt_segment6)
, DECODE(seg.to_segment_code,'SEGMENT7' ,seg.target_value, temp.alt_segment7)
, DECODE(seg.to_segment_code,'SEGMENT8' ,seg.target_value, temp.alt_segment8)
, DECODE(seg.to_segment_code,'SEGMENT9' ,seg.target_value, temp.alt_segment9)
, DECODE(seg.to_segment_code,'SEGMENT10',seg.target_value, temp.alt_segment10)
, DECODE(seg.to_segment_code,'SEGMENT11',seg.target_value, temp.alt_segment11)
, DECODE(seg.to_segment_code,'SEGMENT12',seg.target_value, temp.alt_segment12)
, DECODE(seg.to_segment_code,'SEGMENT13',seg.target_value, temp.alt_segment13)
, DECODE(seg.to_segment_code,'SEGMENT14',seg.target_value, temp.alt_segment14)
, DECODE(seg.to_segment_code,'SEGMENT15',seg.target_value, temp.alt_segment15)
, DECODE(seg.to_segment_code,'SEGMENT16',seg.target_value, temp.alt_segment16)
, DECODE(seg.to_segment_code,'SEGMENT17',seg.target_value, temp.alt_segment17)
, DECODE(seg.to_segment_code,'SEGMENT18',seg.target_value, temp.alt_segment18)
, DECODE(seg.to_segment_code,'SEGMENT19',seg.target_value, temp.alt_segment19)
, DECODE(seg.to_segment_code,'SEGMENT20',seg.target_value, temp.alt_segment20)
, DECODE(seg.to_segment_code,'SEGMENT21',seg.target_value, temp.alt_segment21)
, DECODE(seg.to_segment_code,'SEGMENT22',seg.target_value, temp.alt_segment22)
, DECODE(seg.to_segment_code,'SEGMENT23',seg.target_value, temp.alt_segment23)
, DECODE(seg.to_segment_code,'SEGMENT24',seg.target_value, temp.alt_segment24)
, DECODE(seg.to_segment_code,'SEGMENT25',seg.target_value, temp.alt_segment25)
, DECODE(seg.to_segment_code,'SEGMENT26',seg.target_value, temp.alt_segment26)
, DECODE(seg.to_segment_code,'SEGMENT27',seg.target_value, temp.alt_segment27)
, DECODE(seg.to_segment_code,'SEGMENT28',seg.target_value, temp.alt_segment28)
, DECODE(seg.to_segment_code,'SEGMENT29',seg.target_value, temp.alt_segment29)
, DECODE(seg.to_segment_code,'SEGMENT30',seg.target_value, temp.alt_segment30)
, CASE temp.alt_ccid_status_code
WHEN C_INVALID THEN C_PROCESSING
WHEN C_CREATED THEN C_NOT_PROCESSED
ELSE temp.alt_ccid_status_code
END
FROM (
SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */ DISTINCT --bug7673701
xta.ae_header_id ae_header_id
, xta.temp_line_num temp_line_num
, xta.ledger_id ledger_id
, xta.sl_coa_mapping_id sl_coa_mapping_id
, xta.to_segment_code to_segment_code
, gami.target_bsv target_value
FROM gl_accts_map_bsv_gt gami
, xla_transaction_accts_gt xta
WHERE gami.source_bsv = xta.segment
AND xta.from_segment_code = qualifier_rec.qualifier
AND xta.processing_status_code = 'MAP_QUALIFIER'
AND xta.side_code IN ('ALL','DEBIT')
AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
) seg
WHERE seg.ae_header_id = temp.ae_header_id
AND seg.temp_line_num = temp.temp_line_num
AND seg.ledger_id = temp.ledger_id
AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
)
WHERE temp.balance_type_code <> 'X'
AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
FROM xla_transaction_accts_gt t
WHERE t.ae_header_id = temp.ae_header_id
AND t.temp_line_num = temp.temp_line_num
AND t.ledger_id = temp.ledger_id
AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
AND t.from_segment_code = qualifier_rec.qualifier
AND t.processing_status_code = 'MAP_QUALIFIER'
AND t.side_code IN ('ALL','DEBIT')
AND t.sl_coa_mapping_id = p_gl_coa_mapping_id)
;
(p_msg => '# of rows updated into xla_ae_lines_gt(ALT ccid) = '||to_char(l_rowcount)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
| builds the new accounting ccids. It returns the number of rows updated |
| |
+-----------------------------------------------------------------------*/
FUNCTION BuildCcids
RETURN NUMBER
IS
--
--
l_ccid_created NUMBER;
UPDATE xla_ae_headers_gt xahg
SET xahg.accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
WHERE xahg.ae_header_id IN (SELECT xalg.ae_header_id
FROM xla_ae_lines_gt xalg
WHERE xalg.balance_type_code <> 'X'
AND (xalg.code_combination_status_code <> C_CREATED
OR xalg.code_combination_id = -1) and (nvl(xalg.gain_or_loss_flag,'N') = 'N' or nvl(xalg.calculate_g_l_amts_flag,'N') = 'N')*/
/*
OR ((xalg.alt_code_combination_id = -1
OR xalg.alt_ccid_status_code <> C_CREATED)
AND xalg.gain_or_loss_flag = 'Y' and xalg.calculate_g_l_amts_flag = 'Y'))
*/
/* )
AND xahg.accounting_entry_status_code <> xla_ae_journal_entry_pkg.C_INVALID
; */
UPDATE xla_ae_headers_gt xahg
SET xahg.accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
WHERE (xahg.ae_header_id , NVL(xahg.header_num,0) , xahg.balance_type_code , xahg.ledger_id ) IN (
SELECT xalg.ae_header_id , NVL(xalg.header_num, 0) , xalg.balance_type_code , xalg.ledger_id
FROM xla_ae_lines_gt xalg
WHERE xalg.balance_type_code <> 'X'
AND (xalg.code_combination_status_code <> C_CREATED
OR xalg.code_combination_id = -1) and (nvl(xalg.gain_or_loss_flag,'N') = 'N' or nvl (xalg.calculate_g_l_amts_flag,'N') = 'N')
)
AND xahg.accounting_entry_status_code <> xla_ae_journal_entry_pkg.C_INVALID
;
(p_msg => 'SQL - Update xla_ae_headers_gt '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => '# of rows updated into xla_ae_headers_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);