[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_je(
p_application_id IN INTEGER
, p_event_id IN INTEGER);
SELECT DISTINCT opt.LEDGER_ID
FROM XLA_LEDGER_OPTIONS opt,
XLA_LEDGER_RELATIONSHIPS_V rs,
gl_ledgers gl
WHERE (p_ledger_id IS NULL OR opt.LEDGER_ID = p_ledger_id)
AND opt.APPLICATION_ID = p_application_id
AND opt.ENABLED_FLAG = 'Y'
AND rs.LEDGER_ID = opt.LEDGER_ID
AND ( rs.LEDGER_CATEGORY_CODE = 'PRIMARY'
OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY'
AND v_valuation_method_flag = 'Y'
AND opt.CAPTURE_EVENT_FLAG = 'Y'))
AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gl.ledger_id
AND gl.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL;
SELECT f.APPLICATION_NAME, s.VALUATION_METHOD_FLAG
INTO v_application_name, v_valuation_method_flag
FROM XLA_SUBLEDGERS s, FND_APPLICATION_VL f
WHERE s.APPLICATION_ID = f.APPLICATION_ID
AND s.APPLICATION_ID = p_application_id;
SELECT 'X'
INTO v_dummy
FROM XLA_LEDGER_OPTIONS opt,
XLA_LEDGER_RELATIONSHIPS_V rs,
gl_ledgers gl
WHERE opt.LEDGER_ID = p_ledger_id
AND opt.APPLICATION_ID = p_application_id
AND opt.ENABLED_FLAG = 'Y'
AND rs.LEDGER_ID = opt.LEDGER_ID
AND ( rs.LEDGER_CATEGORY_CODE = 'PRIMARY'
OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY'
AND v_valuation_method_flag = 'Y'
AND opt.CAPTURE_EVENT_FLAG = 'Y'))
AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gl.ledger_id
AND gl.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL;
SELECT 'X'
INTO v_dummy
FROM XLA_THIRD_PARTIES_V
WHERE THIRD_PARTY_ID = p_original_third_party_id
AND THIRD_PARTY_TYPE = p_third_party_type;
SELECT 'X'
INTO v_dummy
FROM XLA_THIRD_PARTIES_V
WHERE THIRD_PARTY_ID = p_new_third_party_id
AND THIRD_PARTY_TYPE = p_third_party_type;
SELECT 'X'
INTO v_dummy
FROM XLA_THIRD_PARTY_SITES_V
WHERE THIRD_PARTY_ID = p_original_third_party_id
AND THIRD_PARTY_SITE_ID = p_original_site_id
AND THIRD_PARTY_TYPE = p_third_party_type
AND ROWNUM = 1; -- May return multiple sites (e.g. different ship tos)
SELECT 'X'
INTO v_dummy
FROM XLA_THIRD_PARTY_SITES_V
WHERE THIRD_PARTY_ID = p_new_third_party_id
AND THIRD_PARTY_SITE_ID = p_new_site_id
AND THIRD_PARTY_TYPE = p_third_party_type
AND ROWNUM = 1; -- May return multiple sites (e.g. different ship tos)
SELECT 'Y'
INTO v_dummy
FROM XLA_MERGE_SEG_MAPS_GT
HAVING COUNT(DISTINCT SEGMENT_CODE) > 1
GROUP BY APPLICATION_ID, LEDGER_ID, CHART_OF_ACCOUNTS_ID;
SELECT ENTITY_ID
INTO v_entity_id
FROM XLA_TRANSACTION_ENTITIES
WHERE APPLICATION_ID = p_application_id
AND LEDGER_ID = v_ledger_id
AND ENTITY_CODE = 'THIRD_PARTY_MERGE';
INSERT INTO XLA_TRANSACTION_ENTITIES
( ENTITY_ID, APPLICATION_ID, LEDGER_ID, ENTITY_CODE,
SOURCE_APPLICATION_ID, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(XLA_TRANSACTION_ENTITIES_S.nextval,
p_application_id,
v_ledger_id,
'THIRD_PARTY_MERGE',
NVL(p_source_application_id, p_application_id),
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
XLA_ENVIRONMENT_PKG.g_login_id)
RETURNING ENTITY_ID INTO v_entity_id;
SELECT max(EVENT_NUMBER)
INTO v_max_event_number
FROM XLA_EVENTS
WHERE ENTITY_ID = v_entity_id;
SELECT 'Y'
INTO v_mapping_flag
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM XLA_LEDGER_RELATIONSHIPS_V rs,
XLA_MERGE_SEG_MAPS_GT gt,
gl_ledgers gld
WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND DECODE(v_valuation_method_flag
, 'N', rs.PRIMARY_LEDGER_ID, rs.LEDGER_ID) = v_ledger_id
AND rs.LEDGER_CATEGORY_CODE IN ('PRIMARY', 'SECONDARY')
AND gt.APPLICATION_ID = p_application_id
AND gt.LEDGER_ID = rs.LEDGER_ID);
INSERT INTO XLA_EVENTS
( EVENT_ID, APPLICATION_ID, ENTITY_ID, EVENT_NUMBER,
EVENT_TYPE_CODE, EVENT_DATE, EVENT_STATUS_CODE,
PROCESS_STATUS_CODE, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_UPDATE_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID, REFERENCE_NUM_1, REFERENCE_NUM_2, REFERENCE_NUM_3,
REFERENCE_NUM_4, REFERENCE_CHAR_1, REFERENCE_CHAR_2,
MERGE_EVENT_SET_ID, ON_HOLD_FLAG,
TRANSACTION_DATE)
VALUES
( XLA_EVENTS_S.nextval,
p_application_id,
v_entity_id,
v_max_event_number + 1,
p_type_of_third_party_merge||'_MERGE',
p_third_party_merge_date,
'U',
'U',
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
XLA_ENVIRONMENT_PKG.g_login_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_prog_appl_id,
XLA_ENVIRONMENT_PKG.g_prog_id,
XLA_ENVIRONMENT_PKG.g_req_id,
p_original_third_party_id,
p_original_site_id,
p_new_third_party_id,
p_new_site_id,
p_third_party_type,
v_mapping_flag,
DECODE(v_event_count
, 0, NULL, TO_CHAR(v_merge_event_set_id)),
'N',
p_third_party_merge_date)
RETURNING EVENT_ID INTO v_event_id;
trace( p_msg => 'Insert mapping rows'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO XLA_MERGE_SEG_MAPS
( APPLICATION_ID, LEDGER_ID, SEGMENT_CODE, FROM_VALUE,
TO_VALUE, EVENT_ID, CHART_OF_ACCOUNTS_ID, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID)
SELECT gt.APPLICATION_ID,
gt.LEDGER_ID,
gt.SEGMENT_CODE,
gt.FROM_VALUE,
gt.TO_VALUE,
v_event_id,
gt.CHART_OF_ACCOUNTS_ID,
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
XLA_ENVIRONMENT_PKG.g_login_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_prog_appl_id
FROM XLA_LEDGER_RELATIONSHIPS_V rs,
XLA_MERGE_SEG_MAPS_GT gt,
GL_LEDGERS gl
WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gl.ledger_id
AND gl.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND DECODE(v_valuation_method_flag
, 'N', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = v_ledger_id
AND rs.LEDGER_CATEGORY_CODE IN ('PRIMARY', 'SECONDARY')
AND gt.APPLICATION_ID = p_application_id
AND gt.LEDGER_ID = rs.LEDGER_ID;
trace( p_msg => 'Insert ' || SQL%ROWCOUNT
|| ' rows into XLA_MERGE_SEG_MAPS'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'Insert partial transactions'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO XLA_PARTIAL_MERGE_TXNS
( APPLICATION_ID, MERGE_EVENT_ID, ENTITY_ID, ENTITY_CODE,
SOURCE_ID_INT_1, SOURCE_ID_INT_2, SOURCE_ID_INT_3,
SOURCE_ID_INT_4, SOURCE_ID_CHAR_1, SOURCE_ID_CHAR_2,
SOURCE_ID_CHAR_3, SOURCE_ID_CHAR_4, VALUATION_METHOD,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID, PROGRAM_ID, REQUEST_ID)
SELECT DISTINCT
gt.APPLICATION_ID,
v_event_id,
ent.ENTITY_ID,
gt.ENTITY_CODE,
gt.SOURCE_ID_INT_1,
gt.SOURCE_ID_INT_2,
gt.SOURCE_ID_INT_3,
gt.SOURCE_ID_INT_4,
gt.SOURCE_ID_CHAR_1,
gt.SOURCE_ID_CHAR_2,
gt.SOURCE_ID_CHAR_3,
gt.SOURCE_ID_CHAR_4,
gt.VALUATION_METHOD,
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_usr_id,
XLA_ENVIRONMENT_PKG.g_login_id,
sysdate,
XLA_ENVIRONMENT_PKG.g_prog_appl_id,
XLA_ENVIRONMENT_PKG.g_prog_id,
XLA_ENVIRONMENT_PKG.g_req_id
FROM XLA_EVENTS_GT gt,
XLA_TRANSACTION_ENTITIES ent
WHERE gt.APPLICATION_ID = p_application_id
AND gt.LEDGER_ID = v_ledger_id
AND ent.APPLICATION_ID = gt.APPLICATION_ID
AND ent.LEDGER_ID = gt.LEDGER_ID
AND ent.ENTITY_CODE = gt.ENTITY_CODE
AND NVL(ent.VALUATION_METHOD,' ') = NVL(gt.VALUATION_METHOD,' ')
AND NVL(ent.SOURCE_ID_INT_1,-99) = NVL(gt.SOURCE_ID_INT_1,-99)
AND NVL(ent.SOURCE_ID_INT_2,-99) = NVL(gt.SOURCE_ID_INT_2,-99)
AND NVL(ent.SOURCE_ID_INT_3,-99) = NVL(gt.SOURCE_ID_INT_3,-99)
AND NVL(ent.SOURCE_ID_INT_4,-99) = NVL(gt.SOURCE_ID_INT_4,-99)
AND NVL(ent.SOURCE_ID_CHAR_1,' ') = NVL(gt.SOURCE_ID_CHAR_1,' ')
AND NVL(ent.SOURCE_ID_CHAR_2,' ') = NVL(gt.SOURCE_ID_CHAR_2,' ')
AND NVL(ent.SOURCE_ID_CHAR_3,' ') = NVL(gt.SOURCE_ID_CHAR_3,' ')
AND NVL(ent.SOURCE_ID_CHAR_4,' ') = NVL(gt.SOURCE_ID_CHAR_4,' ');
trace( p_msg => 'Insert ' || to_char(v_row_count)
|| ' rows into XLA_PARTIAL_MERGE_TXNS'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
SELECT 'Y'
INTO v_dummy
FROM DUAL
WHERE EXISTS
(SELECT 'Ledger without access'
FROM XLA_LEDGER_OPTIONS opt,
XLA_LEDGER_RELATIONSHIPS_V rs,
GL_LEDGERS gld
WHERE opt.APPLICATION_ID = p_application_id
AND opt.ENABLED_FLAG = 'Y'
AND opt.MERGE_ACCT_OPTION_CODE <> 'NONE'
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = opt.LEDGER_ID
AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND DECODE(v_valuation_method_flag
, 'N', rs.PRIMARY_LEDGER_ID
, DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID)) = v_ledger_id
AND rs.LEDGER_ID NOT IN
(SELECT asa.LEDGER_ID
FROM GL_ACCESS_SET_ASSIGNMENTS asa
WHERE asa.ACCESS_SET_ID
IN (g_access_set_id,
g_sec_access_set_id)));
UPDATE XLA_EVENTS
SET MERGE_EVENT_SET_ID = TO_CHAR(v_merge_event_set_id)
WHERE EVENT_ID = v_merge_event_set_id;
select rowid, dense_rank() over (partition by ae_header_id
order by line_hash_num, merge_index) ae_line_num
from xla_ae_lines_gt;
SELECT max(xalg.rowid)
,rounding_class_code
,document_rounding_level
,NVL(SUM(unrounded_accounted_cr), 0)
- NVL(SUM(unrounded_accounted_dr), 0) unrounded_amount
,ledger_id
,ae_header_id
,NVL(SUM(unrounded_entered_cr), 0)
- NVL(SUM(unrounded_entered_dr), 0) unrounded_entered_amount
,entered_currency_mau
FROM xla_ae_lines_gt xalg
WHERE temp_line_num <> 0
GROUP BY ledger_id, event_id, ae_header_id,
rounding_class_code, document_rounding_level, ae_line_num
,entered_currency_mau
HAVING document_rounding_level is not null
AND rounding_class_code is not null
ORDER BY document_rounding_level, rounding_class_code;
UPDATE xla_ae_lines_gt ael
set line_hash_num =
DBMS_UTILITY.GET_HASH_VALUE
(ae_header_id
||accounting_class_code
||rounding_class_code
||document_rounding_level
||currency_code
||currency_conversion_type
||currency_conversion_date
||currency_conversion_rate
||party_id
||party_site_id
||party_type_code
||code_combination_id
||description
||jgzz_recon_ref
||ussgl_transaction_code
||merge_duplicate_code
||encumbrance_type_id,
1,
1073741824)
,merge_index = CASE merge_duplicate_code
WHEN 'A' THEN
CASE switch_side_flag
WHEN 'Y' THEN -1
ELSE
CASE
WHEN accounted_cr is null THEN -2
ELSE -3
END
END
WHEN 'W' THEN
CASE
WHEN accounted_cr is null THEN -2
ELSE -3
END
WHEN 'N' THEN temp_line_num
END;
UPDATE xla_ae_lines_gt
SET ae_line_num = l_array_ae_line_num(i)
WHERE rowid = l_array_rowid(i);
(p_msg => 'SQL - Update xla_ae_lines_gt 3'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'SQL - Update xla_ae_lines_gt 6'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
update xla_ae_lines_gt
set doc_rounding_acctd_amt = l_array_doc_rounding_amt1(i)
,doc_rounding_entered_amt = l_array_rounding_entd_amt1(i)
where rowid = l_array_rowid1(i);
(p_msg => 'SQL - Update xla_ae_lines_gt 7, j='||to_char(j)
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
update xla_ae_lines_gt
set doc_rounding_acctd_amt = l_array_doc_rounding_amt1(i)
,doc_rounding_entered_amt = l_array_rounding_entd_amt1(i)
where rowid = l_array_rowid1(i);
(p_msg => 'ERROR: XLA_AP_CANNOT_INSERT_JE ='||sqlerrm
,p_level => C_LEVEL_EXCEPTION
,p_module => l_log_module);
,p_msg_name => 'XLA_AP_CANNOT_INSERT_JE'
,p_token_1 => 'ERROR'
,p_value_1 => sqlerrm
);
PROCEDURE insert_headers(
p_batch_id IN NUMBER
,p_application_id IN NUMBER
,p_event_id IN NUMBER
,p_accounting_mode IN VARCHAR2)
IS
v_function VARCHAR2(240);
v_function := 'xla_third_party_merge.insert_headers';
v_module := C_DEFAULT_MODULE||'.insert_headers';
INSERT INTO xla_ae_headers
(
ae_header_id
, application_id
, ledger_id
, entity_id
, event_id
, event_type_code
, accounting_date
, gl_transfer_status_code
, je_category_name
, accounting_entry_status_code
, accounting_entry_type_code
, product_rule_type_code
, product_rule_code
, product_rule_version
, description
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, doc_sequence_id
, doc_sequence_value
, doc_category_code
, program_update_date
, program_application_id
, program_id
, request_id
, budget_version_id
, balance_type_code
, completed_date
, period_name
, accounting_batch_id
, amb_context_code
, zero_amount_flag
, parent_ae_header_id -- 4262811
, parent_ae_line_num -- 4262811
, accrual_reversal_flag -- 4262811
, merge_event_id
)
SELECT
hed.ae_header_id
, p_application_id
, hed.ledger_id
, hed.entity_id
, hed.event_id
, hed.event_type_code
, hed.accounting_date
, hed.gl_transfer_status_code
, hed.je_category_name
, hed.accounting_entry_status_code
, hed.accounting_entry_type_code
, hed.product_rule_type_code
, hed.product_rule_code
, hed.product_rule_version
, hed.description
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, hed.doc_sequence_id
, hed.doc_sequence_value
, hed.doc_category_code
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_req_Id
, CASE hed.balance_type_code
WHEN 'B' THEN hed.budget_version_id
ELSE NULL
END
, hed.balance_type_code
, sysdate
, hed.period_name
, p_batch_id
, hed.amb_context_code
, 'N'
, hed.parent_header_id -- 4262811
, hed.parent_ae_line_num -- 4262811
, hed.accrual_reversal_flag -- 4262811
, p_event_id
FROM xla_ae_headers_gt hed;
END insert_headers;
PROCEDURE insert_links(p_application_id IN NUMBER)
IS
v_function VARCHAR2(240);
v_function := 'xla_third_party_merge.insert_links';
v_module := C_DEFAULT_MODULE||'.insert_links';
INSERT INTO xla_distribution_links
(
application_id
, event_id
, source_distribution_id_char_1
, source_distribution_id_char_2
, source_distribution_id_char_3
, source_distribution_id_char_4
, source_distribution_id_char_5
, source_distribution_id_num_1
, source_distribution_id_num_2
, source_distribution_id_num_3
, source_distribution_id_num_4
, source_distribution_id_num_5
, source_distribution_type
, unrounded_entered_cr
, unrounded_entered_dr
, unrounded_accounted_cr
, unrounded_accounted_dr
, ae_header_id
, ae_line_num
, temp_line_num
, tax_line_ref_id
, tax_summary_line_ref_id
, tax_rec_nrec_dist_ref_id
, statistical_amount
, event_class_code
, event_type_code
, line_definition_owner_code
, line_definition_code
, accounting_line_type_code
, accounting_line_code
, ref_event_id
, ref_ae_header_id
, ref_temp_line_num
, merge_duplicate_code
, calculate_acctd_amts_flag
, calculate_g_l_amts_flag
, rounding_class_code
, document_rounding_level
, doc_rounding_acctd_amt
, doc_rounding_entered_amt
)
SELECT
p_application_id
, event_id
, source_distribution_id_char_1
, source_distribution_id_char_2
, source_distribution_id_char_3
, source_distribution_id_char_4
, source_distribution_id_char_5
, source_distribution_id_num_1
, source_distribution_id_num_2
, source_distribution_id_num_3
, source_distribution_id_num_4
, source_distribution_id_num_5
, source_distribution_type
, unrounded_entered_cr
, unrounded_entered_dr
, unrounded_accounted_cr
, unrounded_accounted_dr
, ae_header_id
, ae_line_num
, temp_line_num
, tax_line_ref_id
, tax_summary_line_ref_id
, tax_rec_nrec_dist_ref_id
, statistical_amount
, event_class_code
, event_type_code
, line_definition_owner_code
, line_definition_code
, accounting_line_type_code
, accounting_line_code
, ref_event_id
, ref_ae_header_id
, ref_temp_line_num
, merge_duplicate_code
, calculate_acctd_amts_flag
, calculate_g_l_amts_flag
, rounding_class_code
, document_rounding_level
, doc_rounding_acctd_amt
, doc_rounding_entered_amt
FROM xla_ae_lines_gt;
END insert_links;
PROCEDURE insert_lines(p_application_id IN INTEGER
,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
,p_array_reversal_option IN t_varchar30_array
,p_array_mau IN t_number_array
,p_array_rounding_rule IN t_varchar30_array
) IS
l_count number;
v_function := 'xla_third_party_merge.insert_lines';
v_module := C_DEFAULT_MODULE||'.insert_lines';
INSERT INTO xla_ae_lines
(
ae_header_id
, ae_line_num
, displayed_line_number
, code_combination_id
, gl_transfer_mode_code
, creation_date
, accounted_cr
, accounted_dr
, unrounded_accounted_cr
, unrounded_accounted_dr
, gain_or_loss_flag
, accounting_class_code
, currency_code
, currency_conversion_date
, currency_conversion_rate
, currency_conversion_type
, description
, entered_cr
, entered_dr
, unrounded_entered_cr
, unrounded_entered_dr
, last_update_date
, last_update_login
, party_id
, party_site_id
, party_type_code
, statistical_amount
, ussgl_transaction_code
, created_by
, last_updated_by
, jgzz_recon_ref
, program_update_date
, program_application_id
, program_id
, application_id
, request_id
, gl_sl_link_table
, business_class_code -- 4336173
, mpa_accrual_entry_flag -- 4262811
, encumbrance_type_id -- 4458381 Public Sector Enh
, accounting_date
, ledger_id
, control_balance_flag
, gl_sl_link_id --5041325
)
(SELECT
ae_header_id
, ae_line_num
, displayed_line_number
, code_combination_id
, gl_transfer_mode_code
, creation_date
, accounted_cr
, accounted_dr
, unrounded_accounted_cr
, unrounded_accounted_dr
, gain_or_loss_flag
, accounting_class_code
, currency_code
, currency_conversion_date
, currency_conversion_rate
, currency_conversion_type
, description
, entered_cr
, entered_dr
, unrounded_entered_cr
, unrounded_entered_dr
, last_update_date
, last_update_login
, party_id
, party_site_id
, party_type_code
, statistical_amount
, ussgl_transaction_code
, created_by
, last_updated_by
, jgzz_recon_ref
, program_update_date
, program_application_id
, program_id
, application_id
, request_id
, gl_sl_link_table
, business_class_code -- 4336173
, mpa_accrual_entry_flag -- 4262811
, encumbrance_type_id -- 4458381 Public Sector Enh
, accounting_date
, ledger_id
, alt_segment1
, Decode(accounting_entry_status_code,'F',xla_gl_sl_link_id_s.nextval,NULL)
FROM
(SELECT
lin.ae_header_id ae_header_id
, ae_line_num
-- we always treat switch_side_flag as 'Y' since we can't get the original switch_side_flag any more
,
ROW_NUMBER()
over (PARTITION BY ae_header_id
order by
ABS (
NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)+
NVL(SUM(doc_rounding_acctd_amt), 0)
)
desc) displayed_line_number
, code_combination_id
, 'N' gl_transfer_mode_code
, sysdate creation_date
-- accounted_cr
-- no need to take care of the case that both accounted dr and cr are null.
-- this can't happen in third party merge
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(
NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)+
NVL(SUM(doc_rounding_acctd_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(unrounded_accounted_dr),0)
+ NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(unrounded_accounted_dr),0)
+ NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,ROUND((NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(unrounded_accounted_dr),0)
+ NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
)*p_array_mau(i)
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
NVL(SUM(doc_rounding_entered_amt), 0))
WHEN -1 THEN null
ELSE 0
END
END
ELSE
CASE SIGN(
NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)+
NVL(SUM(doc_rounding_acctd_amt), 0)
)
WHEN 1 THEN null
WHEN -1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(unrounded_accounted_dr),0)
+ NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(unrounded_accounted_dr),0)
+ NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,ROUND((NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(unrounded_accounted_dr),0)
+ NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
)*p_array_mau(i)
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
NVL(SUM(doc_rounding_entered_amt), 0))
WHEN 1 THEN null
ELSE 0
END
END
END
accounted_cr
-- accounted_dr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(
NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)-
NVL(SUM(doc_rounding_acctd_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_accounted_dr),0)
- NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_dr),0)
- NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,ROUND((NVL(SUM(unrounded_accounted_dr),0)
- NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
)*p_array_mau(i)
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
NVL(SUM(doc_rounding_entered_amt), 0))
WHEN 1 THEN 0
ELSE null
END
END
ELSE
CASE SIGN(
NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)-
NVL(SUM(doc_rounding_acctd_amt), 0)
)
WHEN 1 THEN null
WHEN -1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_accounted_dr),0)
- NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_dr),0)
- NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
,ROUND((NVL(SUM(unrounded_accounted_dr),0)
- NVL(SUM(unrounded_accounted_cr),0)
- NVL(SUM(doc_rounding_acctd_amt), 0))
/p_array_mau(i))
)*p_array_mau(i)
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
NVL(SUM(doc_rounding_entered_amt), 0))
WHEN -1 THEN 0
ELSE null
END
END
END
accounted_dr
-- unrounded_accounted_cr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
WHEN -1 THEN null
WHEN 1 THEN
NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
WHEN -1 THEN null
ELSE 0
END
END
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
WHEN 1 THEN null
WHEN -1 THEN
NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
WHEN 1 THEN null
ELSE 0
END
END
END
unrounded_accounted_cr
-- unrounded_accounted_dr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0))
WHEN 1 THEN
NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
WHEN -1 THEN null
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0))
WHEN 1 THEN 0
ELSE null
END
END
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0))
WHEN -1 THEN
NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
WHEN 1 THEN null
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0))
WHEN -1 THEN 0
ELSE null
END
END
END
unrounded_accounted_dr
, gain_or_loss_flag
, accounting_class_code
, currency_code
, currency_conversion_date
, currency_conversion_rate
, currency_conversion_type
, lin.description description
-- entered_cr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(
NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
NVL(SUM(doc_rounding_entered_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(unrounded_entered_dr),0)
+ NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,'DOWN', FLOOR((NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(unrounded_entered_dr),0)
+ NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,ROUND((NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(unrounded_entered_dr),0)
+ NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
)*entered_currency_mau
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
+NVL(SUM(doc_rounding_acctd_amt), 0))
WHEN -1 THEN null
ELSE 0
END
END
ELSE
CASE SIGN(
NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
NVL(SUM(doc_rounding_entered_amt), 0)
)
WHEN 1 THEN null
WHEN -1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(unrounded_entered_dr),0)
+ NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,'DOWN', FLOOR((NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(unrounded_entered_dr),0)
+ NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,ROUND((NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(unrounded_entered_dr),0)
+ NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
)*entered_currency_mau
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
+NVL(SUM(doc_rounding_acctd_amt), 0))
WHEN 1 THEN null
ELSE 0
END
END
END
entered_cr
-- entered_dr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(
NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
NVL(SUM(doc_rounding_entered_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_entered_dr),0)
- NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,'DOWN', FLOOR((NVL(SUM(unrounded_entered_dr),0)
- NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,ROUND((NVL(SUM(unrounded_entered_dr),0)
- NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
)*entered_currency_mau
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
-NVL(SUM(doc_rounding_acctd_amt), 0))
WHEN 1 THEN 0
ELSE null
END
END
ELSE
CASE SIGN(
NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
NVL(SUM(doc_rounding_entered_amt), 0)
)
WHEN 1 THEN null
WHEN -1 THEN
DECODE(p_array_rounding_rule(i)
,'UP', CEIL((NVL(SUM(unrounded_entered_dr),0)
- NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,'DOWN', FLOOR((NVL(SUM(unrounded_entered_dr),0)
- NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
,ROUND((NVL(SUM(unrounded_entered_dr),0)
- NVL(SUM(unrounded_entered_cr),0)
- NVL(SUM(doc_rounding_entered_amt), 0))
/entered_currency_mau)
)*entered_currency_mau
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
-NVL(SUM(doc_rounding_acctd_amt), 0))
WHEN -1 THEN 0
ELSE null
END
END
END
entered_dr
-- unrounded_entered_cr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
WHEN -1 THEN null
WHEN 1 THEN NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
WHEN -1 THEN null
ELSE 0
END
END
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
WHEN 1 THEN null
WHEN -1 THEN NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
WHEN 1 THEN null
ELSE 0
END
END
END
unrounded_entered_cr
-- unrounded_entered_dr
,
CASE p_array_reversal_option(i)
WHEN 'SIDE' THEN
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
WHEN 1 THEN null
WHEN -1 THEN NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
WHEN -1 THEN 0
ELSE null
END
END
ELSE
CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
WHEN -1 THEN null
WHEN 1 THEN NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)
ELSE
CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
WHEN 1 THEN 0
ELSE null
END
END
END unrounded_entered_dr
, sysdate last_update_date
, XLA_ENVIRONMENT_PKG.g_login_id last_update_login
, party_id
, party_site_id
, party_type_code
, sum(statistical_amount) statistical_amount
, ussgl_transaction_code
, XLA_ENVIRONMENT_PKG.g_login_id created_by
, XLA_ENVIRONMENT_PKG.g_login_id last_updated_by
, jgzz_recon_ref
, sysdate program_update_date
, XLA_ENVIRONMENT_PKG.g_prog_appl_id program_application_id
, XLA_ENVIRONMENT_PKG.g_prog_id program_id
, p_application_id application_id
, XLA_ENVIRONMENT_PKG.g_req_id request_id
, 'XLAJEL' gl_sl_link_table
, business_class_code -- 4336173
, mpa_accrual_entry_flag -- 4262811
, encumbrance_type_id -- 4458381 Public Sector Enh
, accounting_date
, ledger_id
, alt_segment1
, accounting_entry_status_code
FROM xla_ae_lines_gt lin
WHERE ledger_id = p_array_ledger_id(i)
AND ae_line_num is not NULL
GROUP BY lin.ae_header_id
, ae_line_num
, header_num -- 4262811c MPA reversal lines
, sysdate
, XLA_ENVIRONMENT_PKG.g_login_id
, XLA_ENVIRONMENT_PKG.g_prog_appl_id
, XLA_ENVIRONMENT_PKG.g_prog_id
, XLA_ENVIRONMENT_PKG.g_req_id
, p_application_id
, accounting_class_code
, event_class_code
, event_type_code
, line_definition_owner_code
, line_definition_code
, entered_currency_mau
, currency_code
, currency_conversion_type
, currency_conversion_date
, currency_conversion_rate
, party_id
, party_site_id
, party_type_code
, code_combination_id
, code_combination_status_code
, lin.description
, jgzz_recon_ref
, ussgl_transaction_code
, merge_duplicate_code
, switch_side_flag
, gain_or_loss_flag
, lin.business_class_code -- 4336173
, lin.mpa_accrual_entry_flag -- 4262811
, encumbrance_type_id -- 4458381 Public Sector Enh
, accounting_date
, ledger_id
, alt_segment1
, merge_index
,accounting_entry_status_code)
);
(p_msg => '# journal entry lines inserted into xla_ae_lines = '||to_char(l_count)
,p_level => C_LEVEL_EVENT
,p_module => v_module);
(p_msg => 'END of insert_lines'
,p_level => C_LEVEL_PROCEDURE
,p_module => v_module);
(p_location => 'XLA_AE_JOURNAL_ENTRY_PKG.insert_lines');
END insert_lines;
SELECT period_name, start_date, decode(closing_status, 'O', 'F', 'N', 'I', 'F', 'F')
INTO p_gl_period_name(i), p_gl_date(i), p_entry_status(i)
FROM gl_period_statuses
WHERE ledger_id = p_array_ledger_id(i)
AND application_id = 101
AND end_date >= p_merge_date
AND closing_status in ('O', 'F', 'N')
AND start_date =
(SELECT min(start_date)
FROM gl_period_statuses
WHERE ledger_id = p_array_ledger_id(i)
AND application_id = 101
AND end_date >= p_merge_date
AND closing_status in ('O', 'F', 'N'));
SELECT period_name, start_date, decode(closing_status, 'O', 'F', 'N', 'I', 'F', 'F')
INTO p_gl_period_name(i), p_gl_date(i), p_entry_status(i)
FROM gl_period_statuses
WHERE ledger_id = p_array_ledger_id(i)
AND application_id = 101
AND end_date >= p_merge_date
AND closing_status in ('O', 'F', 'N')
AND start_date =
(SELECT min(start_date)
FROM gl_period_statuses
WHERE ledger_id = p_array_ledger_id(i)
AND application_id = 101
AND end_date >= p_merge_date
AND closing_status in ('O', 'F', 'N'));
SELECT xtw.request_id
FROM XLA_TPM_WORKING_HDRS_T xtw
, fnd_concurrent_requests fcr
WHERE xtw.merge_event_id = p_event_id
AND xtw.process_type_flag in ('B', 'R')
AND xtw.request_id = fcr.request_id
AND fcr.phase_code IN ('R','P','I');
DELETE from XLA_TPM_WORKING_HDRS_T
WHERE merge_event_id = p_event_id;
INSERT INTO XLA_TPM_WORKING_HDRS_T
( request_id
,ae_header_id
,merge_event_id
,process_type_flag)
SELECT
p_request_id
,ae_header_id
,p_event_id
,'B'
FROM xla_ae_headers aeh
WHERE aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE <= p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
AND 'TRANSFER' = p_array_merge_option(i)
AND merge_event_id is null
AND ae_header_id in
(SELECT ael.ae_header_id
FROM xla_ae_lines ael
,XLA_PARTIAL_MERGE_TXNS pmt
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND ael.control_balance_flag in ('P', 'Y')
AND pmt.APPLICATION_ID = ael.application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID);
trace( p_msg => 'partial, balance, # inserted:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO XLA_TPM_WORKING_HDRS_T
( request_id
,ae_header_id
,merge_event_id
,process_type_flag)
SELECT
p_request_id
,ae_header_id
,p_event_id
,'R'
FROM xla_ae_headers aeh
WHERE aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE > p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
AND 'TRANSFER' = p_array_merge_option(i)
AND merge_event_id is null
AND ae_header_id in
(SELECT ael.ae_header_id
FROM xla_ae_lines ael
,XLA_PARTIAL_MERGE_TXNS pmt
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND pmt.APPLICATION_ID = ael.application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID);
trace( p_msg => 'partial, reverse and rebooking# inserted:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO XLA_TPM_WORKING_HDRS_T
( request_id
,ae_header_id
,merge_event_id
,process_type_flag)
SELECT
p_request_id
,ae_header_id
,p_event_id
,'B'
FROM xla_ae_headers aeh
WHERE aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE <= p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
AND 'TRANSFER' = p_array_merge_option(i)
AND merge_event_id is null
AND ae_header_id in
(SELECT ael.ae_header_id
FROM xla_ae_lines ael
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND ael.control_balance_flag in ('P', 'Y'));
trace( p_msg => 'full, balance transfer # inserted:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO XLA_TPM_WORKING_HDRS_T
( request_id
,ae_header_id
,merge_event_id
,process_type_flag)
SELECT
p_request_id
,ae_header_id
,p_event_id
,'R'
FROM xla_ae_headers aeh
WHERE aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE > p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
AND 'TRANSFER' = p_array_merge_option(i)
AND merge_event_id is null
AND ae_header_id in
(SELECT ael.ae_header_id
FROM xla_ae_lines ael
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID);
trace( p_msg => 'full, reverse and rebooking # inserted:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'before inserting reverse sql'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_lines_gt
(ae_header_id
,temp_line_num
,event_id
,ref_ae_header_id
,ref_ae_line_num
,ref_temp_line_num
,ref_event_id
,balance_type_code
,ledger_id
,accounting_class_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,code_combination_status_code
,code_combination_id
,description
,gl_transfer_mode_code
,merge_duplicate_code
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,calculate_acctd_amts_flag
,calculate_g_l_amts_flag
,gain_or_loss_flag
,rounding_class_code
,document_rounding_level
,doc_rounding_acctd_amt
,doc_rounding_entered_amt
,entered_currency_mau
,currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,statistical_amount
,party_id
,party_site_id
,party_type_code
,source_distribution_type
,ussgl_transaction_code
,jgzz_recon_ref
,analytical_balance_flag
,reversal_code
,accounting_entry_status_code
,inherit_desc_flag
,header_num -- 5100860 assign value to avoid using function index
,alt_segment1
,encumbrance_type_id)
SELECT
p_event_id
,rownum
,p_event_id
,ael.ae_header_id
,ael.ae_line_num
,xdl.temp_line_num
,xdl.event_id
,aeh.balance_type_code
,aeh.ledger_id
,ael.accounting_class_code
,'MERGE' --xdl.event_class_code
,p_merge_type
,null --xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,'CREATED'-- code combination id status
,ael.code_combination_id
,p_balance_desc
,'N' --gl_transfer_mode_code
,xdl.merge_duplicate_code
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_cr, 0 - xdl.unrounded_entered_dr)
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_dr, 0 - xdl.unrounded_entered_cr)
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_cr, 0 - xdl.unrounded_accounted_dr)
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_dr, 0 - xdl.unrounded_accounted_cr)
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,xdl.doc_rounding_acctd_amt
,xdl.doc_rounding_entered_amt
,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,nvl(ael.merge_party_id, ael.party_id)
,nvl(ael.merge_party_site_id, ael.party_site_id)
,ael.party_type_code
,xdl.source_distribution_type
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
,ael.analytical_balance_flag
,'REVERSE_BALANCE'
,'F'
,'N'
,0 -- 5100860 assign value to avoid using function index
,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
,ael.encumbrance_type_id
FROM
xla_ae_lines ael
,xla_ae_headers aeh
,xla_distribution_links xdl
,fnd_currencies fcu
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
and ael.currency_code = fcu.currency_code
AND aeh.ae_header_id = xdl.ae_header_id
AND ael.ae_line_num = xdl.ae_line_num
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = ael.application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE <= p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND aeh.ae_header_id in (
SELECT ae_header_id
FROM XLA_TPM_WORKING_HDRS_T xtwh
WHERE xtwh.merge_event_id = p_event_id
AND xtwh.process_type_flag= 'B'
AND rownum <= C_WORK_UNIT)
AND aeh.merge_event_id is null
/*
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links
WHERE ref_ae_header_id = xdl.ae_header_id
AND ref_temp_line_num = xdl.temp_line_num
-- means it is a third party merge line
And ref_ae_header_id <>ae_header_id
)
*/
AND ael.control_balance_flag in ('Y', 'P');
trace( p_msg => '# inserted:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'before inserting transfer sql'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_lines_gt
(ae_header_id
,temp_line_num
,event_id
,ref_ae_header_id
,ref_ae_line_num
,ref_temp_line_num
,ref_event_id
,balance_type_code
,ledger_id
,accounting_class_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,code_combination_status_code
,code_combination_id
,description
,gl_transfer_mode_code
,merge_duplicate_code
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,calculate_acctd_amts_flag
,calculate_g_l_amts_flag
,gain_or_loss_flag
,rounding_class_code
,document_rounding_level
,doc_rounding_acctd_amt
,doc_rounding_entered_amt
,entered_currency_mau
,currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,statistical_amount
,party_id
,party_site_id
,party_type_code
,source_distribution_type
,ussgl_transaction_code
,jgzz_recon_ref
,analytical_balance_flag
,reversal_code
,accounting_entry_status_code
,inherit_desc_flag
,header_num -- 5100860 assign value to avoid using function index
,alt_segment1
,encumbrance_type_id)
SELECT
p_event_id
,v_row_count+rownum
,p_event_id
,ael.ae_header_id
,ael.ae_line_num
,xdl.temp_line_num
,xdl.event_id
,aeh.balance_type_code
,aeh.ledger_id
,ael.accounting_class_code
,'MERGE' --xdl.event_class_code
,p_merge_type
,null --xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,'CREATED'-- code combination id status
,ael.code_combination_id
,p_balance_desc
,'N' --gl_transfer_mode_code
,xdl.merge_duplicate_code
,xdl.unrounded_entered_dr
,xdl.unrounded_entered_cr
,xdl.unrounded_accounted_dr
,xdl.unrounded_accounted_cr
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,xdl.doc_rounding_acctd_amt
,xdl.doc_rounding_entered_amt
,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,p_new_party_id
,p_new_site_id
,ael.party_type_code
,xdl.source_distribution_type
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
,ael.analytical_balance_flag
,'TRANSFER_BALANCE'
,'F'
,'N'
,0 -- 5100860 assign value to avoid using function index
,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
,ael.encumbrance_type_id
FROM
xla_ae_lines ael
,xla_ae_headers aeh
,xla_distribution_links xdl
,fnd_currencies fcu
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
and ael.currency_code = fcu.currency_code
AND aeh.ae_header_id = xdl.ae_header_id
AND ael.ae_line_num = xdl.ae_line_num
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = ael.application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE <= p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND aeh.ae_header_id in (
SELECT ae_header_id
FROM XLA_TPM_WORKING_HDRS_T xtwh
WHERE xtwh.merge_event_id = p_event_id
AND xtwh.process_type_flag= 'B'
AND rownum <= C_WORK_UNIT)
AND aeh.merge_event_id is null
/*
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links
WHERE ref_ae_header_id = xdl.ae_header_id
AND ref_temp_line_num = xdl.temp_line_num
-- means it is a third party merge line
And ref_ae_header_id <>ae_header_id
)
*/
AND ael.control_balance_flag in ('P', 'Y');
trace( p_msg => '# total rows inserted:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'before inserting header'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_headers_gt
( ae_header_id
, accounting_entry_status_code
, accounting_entry_type_code
, ledger_id
, entity_id
, event_id
, event_type_code
, accounting_date
, period_name
, description
, budget_version_id -- use this field to save merge_event_id
, balance_type_code
, amb_context_code
, gl_transfer_status_code
, je_category_name
)
select xla_ae_headers_s.nextval
,decode(p_accounting_mode, 'D', 'D', v_gl_entry_status(i))
,'MERGE'
,p_array_ledger_id(i)
,p_entity_id
,p_event_id
,p_merge_type
,v_gl_date(i)
,v_gl_period_name(i)
,p_balance_desc
,p_event_id
,'A'
,null
,'N'
,'Other'
from dual
where p_array_merge_option(i) = 'TRANSFER'
AND p_array_ledger_id(i) in
(select ledger_id from xla_ae_lines_gt);
trace( p_msg => 'Header inserted'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE xla_ae_lines_gt xal
set (ae_header_id, accounting_date) =(
select ae_header_id, accounting_date
from xla_ae_headers_gt xah
where xah.ledger_id = xal.ledger_id);
trace( p_msg => 'before inserting reverse sql'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_lines_gt
(ae_header_id
,temp_line_num
,event_id
,ref_ae_header_id
,ref_ae_line_num
,ref_temp_line_num
,ref_event_id
,balance_type_code
,ledger_id
,accounting_class_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,code_combination_status_code
,code_combination_id
,description
,gl_transfer_mode_code
,merge_duplicate_code
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,calculate_acctd_amts_flag
,calculate_g_l_amts_flag
,gain_or_loss_flag
,rounding_class_code
,document_rounding_level
,doc_rounding_acctd_amt
,doc_rounding_entered_amt
,entered_currency_mau
,currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,statistical_amount
,party_id
,party_site_id
,party_type_code
,ussgl_transaction_code
,jgzz_recon_ref
,source_distribution_id_char_1
,source_distribution_id_char_2
,source_distribution_id_char_3
,source_distribution_id_char_4
,source_distribution_id_char_5
,source_distribution_id_num_1
,source_distribution_id_num_2
,source_distribution_id_num_3
,source_distribution_id_num_4
,source_distribution_id_num_5
,source_distribution_type
,analytical_balance_flag
,reversal_code
,accounting_entry_status_code
,inherit_desc_flag
,header_num -- 5100860 assign value to avoid using function index
,alt_segment1
,encumbrance_type_id)
SELECT
p_event_id
,rank() over(partition by xdl.ae_header_id order by xdl.temp_line_num)
,aeh.event_id
,ael.ae_header_id
,ael.ae_line_num
,xdl.temp_line_num
,xdl.event_id
,aeh.balance_type_code
,aeh.ledger_id
,ael.accounting_class_code
,xdl.event_class_code
,aeh.event_type_code --'MERGE' --merge_event_type_code
,null --xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,'CREATED'-- code combination id status
,ael.code_combination_id
,ael.description || p_reverse_line_desc
,'N' --gl_transfer_mode_code
,xdl.merge_duplicate_code
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_cr, 0 - xdl.unrounded_entered_dr)
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_dr, 0 - xdl.unrounded_entered_cr)
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_cr, 0 - xdl.unrounded_accounted_dr)
,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_dr, 0 - xdl.unrounded_accounted_cr)
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,xdl.doc_rounding_acctd_amt
,xdl.doc_rounding_entered_amt
,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,nvl(ael.merge_party_id, ael.party_id)
,nvl(ael.merge_party_site_id, ael.party_site_id)
,ael.party_type_code
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
,xdl.source_distribution_id_char_1
,xdl.source_distribution_id_char_2
,xdl.source_distribution_id_char_3
,xdl.source_distribution_id_char_4
,xdl.source_distribution_id_char_5
,xdl.source_distribution_id_num_1
,xdl.source_distribution_id_num_2
,xdl.source_distribution_id_num_3
,xdl.source_distribution_id_num_4
,xdl.source_distribution_id_num_5
,xdl.source_distribution_type
,ael.analytical_balance_flag
,'REVERSE'
,'F'
,'N'
,0 -- 5100860 assign value to avoid using function index
,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
,ael.encumbrance_type_id
FROM
xla_ae_lines ael
,xla_ae_headers aeh
,xla_distribution_links xdl
,fnd_currencies fcu
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
and ael.currency_code = fcu.currency_code
AND aeh.ae_header_id = xdl.ae_header_id
AND ael.ae_line_num = xdl.ae_line_num
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = ael.application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE > p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND aeh.ae_header_id in (
SELECT ae_header_id
FROM XLA_TPM_WORKING_HDRS_T xtwh
WHERE xtwh.merge_event_id = p_event_id
AND xtwh.process_type_flag= 'R'
AND rownum <= C_WORK_UNIT)
AND aeh.merge_event_id is null
/*
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links
WHERE ref_ae_header_id = xdl.ae_header_id
AND ref_temp_line_num = xdl.temp_line_num
-- means it is a third party merge line
And ref_ae_header_id <>ae_header_id
)
*/
;
trace( p_msg => '# inserted:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'before inserting rebooking sql'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_lines_gt
(ae_header_id
,temp_line_num
,event_id
,ref_ae_header_id
,ref_ae_line_num
,ref_temp_line_num
,ref_event_id
,balance_type_code
,ledger_id
,accounting_class_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,code_combination_status_code
,code_combination_id
,description
,gl_transfer_mode_code
,merge_duplicate_code
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,calculate_acctd_amts_flag
,calculate_g_l_amts_flag
,gain_or_loss_flag
,rounding_class_code
,document_rounding_level
,doc_rounding_acctd_amt
,doc_rounding_entered_amt
,entered_currency_mau
,currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,statistical_amount
,party_id
,party_site_id
,party_type_code
,ussgl_transaction_code
,jgzz_recon_ref
,source_distribution_id_char_1
,source_distribution_id_char_2
,source_distribution_id_char_3
,source_distribution_id_char_4
,source_distribution_id_char_5
,source_distribution_id_num_1
,source_distribution_id_num_2
,source_distribution_id_num_3
,source_distribution_id_num_4
,source_distribution_id_num_5
,source_distribution_type
,analytical_balance_flag
,reversal_code
,accounting_entry_status_code
,inherit_desc_flag
,header_num -- 5100860 assign value to avoid using function index
,alt_segment1
,encumbrance_type_id)
SELECT
p_event_id
,count(*) over(partition by xdl.ae_header_id) + rank() over(partition by xdl.ae_header_id order by xdl.temp_line_num)
-- ,xdl.temp_line_num
,aeh.event_id
,ael.ae_header_id
,ael.ae_line_num
,xdl.temp_line_num
,xdl.event_id
,aeh.balance_type_code
,aeh.ledger_id
,ael.accounting_class_code
,xdl.event_class_code
,aeh.event_type_code --'MERGE' --merge_event_type_code
,null --xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,'CREATED'-- code combination id status
,ael.code_combination_id
,ael.description || p_rebooking_line_desc
,'N' --gl_transfer_mode_code
,xdl.merge_duplicate_code
,xdl.unrounded_entered_dr
,xdl.unrounded_entered_cr
,xdl.unrounded_accounted_dr
,xdl.unrounded_accounted_cr
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,xdl.doc_rounding_acctd_amt
,xdl.doc_rounding_entered_amt
,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,p_new_party_id
,p_new_site_id
,ael.party_type_code
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
,xdl.source_distribution_id_char_1
,xdl.source_distribution_id_char_2
,xdl.source_distribution_id_char_3
,xdl.source_distribution_id_char_4
,xdl.source_distribution_id_char_5
,xdl.source_distribution_id_num_1
,xdl.source_distribution_id_num_2
,xdl.source_distribution_id_num_3
,xdl.source_distribution_id_num_4
,xdl.source_distribution_id_num_5
,xdl.source_distribution_type
,ael.analytical_balance_flag
,'REBOOKING'
,'F'
,'N'
,0 -- 5100860 assign value to avoid using function index
,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
,ael.encumbrance_type_id
FROM
xla_ae_lines ael
,xla_ae_headers aeh
,xla_distribution_links xdl
,fnd_currencies fcu
WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
= nvl(p_old_site_id, -1)
and nvl(ael.party_type_code , p_party_type) = p_party_type
and ael.currency_code <> 'STAT'
and ael.currency_code = fcu.currency_code
AND aeh.ae_header_id = xdl.ae_header_id
AND ael.ae_line_num = xdl.ae_line_num
AND ael.APPLICATION_ID = p_application_id
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND aeh.BALANCE_TYPE_CODE = 'A'
AND aeh.APPLICATION_ID = ael.application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.ACCOUNTING_DATE > p_merge_date
AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND aeh.ae_header_id in (
SELECT ae_header_id
FROM XLA_TPM_WORKING_HDRS_T xtwh
WHERE xtwh.merge_event_id = p_event_id
AND xtwh.process_type_flag= 'R'
AND rownum <= C_WORK_UNIT)
AND aeh.merge_event_id is null
/*
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links
WHERE ref_ae_header_id = xdl.ae_header_id
AND ref_temp_line_num = xdl.temp_line_num
-- means it is a third party merge line
And ref_ae_header_id <>ae_header_id
)
*/
;
trace( p_msg => '# total rows inserted:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'before inserting header'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'return from generate_headers, Header inserted'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
insert_lines(
p_application_id => p_application_id
,p_array_ledger_id => v_array_ledger_id
,p_array_reversal_option => v_array_reversal_option
,p_array_mau => v_array_mau
,p_array_rounding_rule=> v_array_rounding_rule_code);
SELECT xla_accounting_batches_s.NEXTVAL INTO v_batch_id FROM DUAL;
insert_headers(
p_batch_id => v_batch_id
,p_application_id => p_application_id
,p_event_id => p_event_id
,p_accounting_mode => p_accounting_mode);
insert_links(
p_application_id => p_application_id);
UPDATE xla_ae_lines xal
SET (merge_party_id, merge_party_site_id, merge_code_combination_id)
= (select party_id, party_site_id, code_combination_id
from xla_ae_lines_gt xalg
where xalg.ref_ae_header_id = xal.ae_header_id
AND xalg.ref_ae_line_num = xal.ae_line_num
AND xalg.reversal_code in ('REBOOKING', 'TRANSFER_BALANCE')
and rownum = 1)
WHERE xal.application_id = p_application_id
AND (ae_header_id, ae_line_num) in
(select xlg.ref_ae_header_id, xlg.ref_ae_line_num
from xla_ae_lines_gt xlg
,xla_ae_headers xah
where xlg.reversal_code in ('REBOOKING', 'TRANSFER_BALANCE')
-- Bug 5103972 MPA / Reversal of incomplete JE
-- should not be stamped with merge party informtion
AND xlg.ref_ae_header_id = xah.ae_header_id
AND xah.application_id = p_application_id
AND (xah.parent_ae_header_id IS NULL
OR xah.accounting_entry_status_code <> 'N')
);
DELETE XLA_TPM_WORKING_HDRS_T xtwh
WHERE xtwh.merge_event_id = p_event_id
AND xtwh.process_type_flag= 'B'
AND rownum <= C_WORK_UNIT;
DELETE XLA_TPM_WORKING_HDRS_T xtwh
WHERE xtwh.merge_event_id = p_event_id
AND xtwh.process_type_flag= 'R'
AND rownum <= C_WORK_UNIT;
PROCEDURE update_journal_entries(
x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY VARCHAR2
,p_application_id IN NUMBER
,p_event_id IN NUMBER
,p_event_merge_option IN VARCHAR2
,p_entity_id IN NUMBER
,p_mapping_flag IN VARCHAR2
,p_event_ledger_id IN NUMBER
,p_merge_date IN DATE
,p_merge_type IN VARCHAR2
,p_old_site_id IN NUMBER
,p_old_party_id IN NUMBER
,p_new_site_id IN NUMBER
,p_new_party_id IN NUMBER
,p_party_type IN VARCHAR2
,p_line_desc IN VARCHAR2
,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
,p_array_ledger_category IN t_varchar30_array
,p_array_reversal_option IN t_varchar30_array
,p_array_merge_option IN t_varchar30_array
,p_array_submit_transfer IN t_varchar1_array)
is
v_function VARCHAR2(240);
v_function := 'xla_third_party_merge.update_journal_entries';
v_module := C_DEFAULT_MODULE||'.update_journal_entries';
UPDATE XLA_AE_HEADERS aeh
SET DESCRIPTION
= DECODE(DESCRIPTION
, NULL, v_aeh_desc
, SUBSTRB(DESCRIPTION, 0,
1995 - LENGTHB(v_aeh_desc))
|| ' ' || v_aeh_desc),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
WHERE aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND ((aeh.accounting_entry_status_code = 'F' AND
p_array_merge_option(i) = 'CHANGE')
OR
--
-- Bug 5103972
-- Should update party info for incomplete je for MPA
-- even when the merge option is 'TRANSFER'
--
(p_array_merge_option(i) = 'TRANSFER' AND
aeh.parent_ae_header_id IS NOT NULL AND
aeh.accounting_entry_status_code = 'N'
)
)
AND EXISTS
(SELECT 'X'
FROM XLA_AE_LINES ael
WHERE ael.PARTY_ID = p_old_party_id
AND ( p_old_site_id IS NULL
OR ael.PARTY_SITE_ID = p_old_site_id)
AND ael.PARTY_TYPE_CODE = p_party_type
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND aeh.APPLICATION_ID = ael.APPLICATION_ID)
AND EXISTS
(SELECT 'X'
FROM XLA_PARTIAL_MERGE_TXNS pmt
WHERE pmt.APPLICATION_ID = p_application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID);
UPDATE XLA_AE_HEADERS aeh
SET DESCRIPTION
= DECODE(DESCRIPTION
, NULL, v_aeh_desc
, SUBSTRB(DESCRIPTION, 0,
1995 - LENGTHB(v_aeh_desc))
|| ' ' || v_aeh_desc),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
WHERE aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND ((aeh.accounting_entry_status_code = 'F' AND
p_array_merge_option(i) = 'CHANGE')
OR
--
-- Bug 5103972
-- Should update party info for incomplete je for MPA
-- even when the merge option is 'TRANSFER'
--
(p_array_merge_option(i) = 'TRANSFER' AND
aeh.parent_ae_header_id IS NOT NULL AND
aeh.accounting_entry_status_code = 'N'
)
)
AND EXISTS
(SELECT 'X'
FROM XLA_AE_LINES ael
WHERE ael.PARTY_ID = p_old_party_id
AND ( p_old_site_id IS NULL
OR ael.PARTY_SITE_ID = p_old_site_id)
AND ael.PARTY_TYPE_CODE = p_party_type
AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
AND aeh.APPLICATION_ID = ael.APPLICATION_ID);
trace( p_msg => '# of headers updated:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
trace( p_msg => 'update the line next '
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE XLA_AE_LINES ael
SET PARTY_ID = p_new_party_id,
PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
DESCRIPTION
= DECODE(DESCRIPTION
, NULL, p_line_desc
, SUBSTRB(DESCRIPTION, 0,
1995 - LENGTHB(p_line_desc))
|| ' ' || p_line_desc),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
WHERE ael.PARTY_ID = p_old_party_id
AND ( p_old_site_id IS NULL
OR ael.PARTY_SITE_ID = p_old_site_id)
AND ael.PARTY_TYPE_CODE = p_party_type
AND EXISTS
(SELECT 'X'
FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'F'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND p_array_merge_option(i) = 'CHANGE'
AND pmt.APPLICATION_ID = p_application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID
UNION ALL
--
-- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
--
SELECT 'X'
FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'N'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.parent_ae_header_id IS NOT NULL
AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
AND pmt.APPLICATION_ID = p_application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID
);
trace( p_msg => '# of lines updated:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE XLA_TRIAL_BALANCES tb
SET PARTY_ID = p_new_party_id,
PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
WHERE tb.PARTY_ID = p_old_party_id
AND ( p_old_site_id IS NULL
OR tb.PARTY_SITE_ID = p_old_site_id)
AND tb.PARTY_TYPE_CODE = p_party_type
AND EXISTS
(SELECT 'X'
FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.accounting_entry_status_code = 'F'
AND p_array_merge_option(i) = 'CHANGE'
AND pmt.APPLICATION_ID = p_application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID
UNION ALL
--
-- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
--
SELECT 'X'
FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.parent_ae_header_id IS NOT NULL
AND aeh.accounting_entry_status_code = 'N'
AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
AND pmt.APPLICATION_ID = p_application_id
AND pmt.MERGE_EVENT_ID = p_event_id
AND pmt.ENTITY_ID = aeh.ENTITY_ID);
trace( p_msg => '# of rows in xla_trial_balances table updated:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE XLA_AE_LINES ael
SET PARTY_ID = p_new_party_id,
PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
DESCRIPTION
= DECODE(DESCRIPTION
, NULL, p_line_desc
, SUBSTRB(DESCRIPTION, 0,
1995 - LENGTHB(p_line_desc))
|| ' ' || p_line_desc),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
WHERE ael.PARTY_ID = p_old_party_id
AND ( p_old_site_id IS NULL
OR ael.PARTY_SITE_ID = p_old_site_id)
AND ael.PARTY_TYPE_CODE = p_party_type
AND EXISTS
(SELECT 'X'
FROM XLA_AE_HEADERS aeh
WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'F'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND p_array_merge_option(i) = 'CHANGE'
UNION ALL
--
-- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
--
SELECT 'X'
FROM XLA_AE_HEADERS aeh
WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.accounting_entry_status_code = 'N'
AND aeh.parent_ae_header_id IS NOT NULL
AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
)
;
trace( p_msg => '# of lines updated:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE XLA_TRIAL_BALANCES tb
SET PARTY_ID = p_new_party_id,
PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
WHERE tb.PARTY_ID = p_old_party_id
AND ( p_old_site_id IS NULL
OR tb.PARTY_SITE_ID = p_old_site_id)
AND tb.PARTY_TYPE_CODE = p_party_type
AND EXISTS
(SELECT 'X'
FROM XLA_AE_HEADERS aeh
WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'F'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND p_array_merge_option(i) = 'CHANGE'
UNION ALL
--
-- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
--
SELECT 'X'
FROM XLA_AE_HEADERS aeh
WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'N'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.parent_ae_header_id IS NOT NULL
AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
);
trace( p_msg => '# of rows in xla_trial_balances table updated:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
end update_journal_entries;
select 1
from xla_ae_lines_gt
where code_combination_id is null;
update xla_ae_lines_gt xalg
set code_combination_id =
(select code_combination_id
from gl_code_combinations gcc
where gcc.chart_of_accounts_id = xalg.ccid_coa_id
and gcc.template_id is null
and (gcc.segment1= xalg.segment1 or (gcc.segment1 is null and xalg.segment1 is null))
and (gcc.segment2= xalg.segment2 or (gcc.segment2 is null and xalg.segment2 is null))
and (gcc.segment3= xalg.segment3 or (gcc.segment3 is null and xalg.segment3 is null))
and (gcc.segment4= xalg.segment4 or (gcc.segment4 is null and xalg.segment4 is null))
and (gcc.segment5= xalg.segment5 or (gcc.segment5 is null and xalg.segment5 is null))
and (gcc.segment6= xalg.segment6 or (gcc.segment6 is null and xalg.segment6 is null))
and (gcc.segment7= xalg.segment7 or (gcc.segment7 is null and xalg.segment7 is null))
and (gcc.segment8= xalg.segment8 or (gcc.segment8 is null and xalg.segment8 is null))
and (gcc.segment9= xalg.segment9 or (gcc.segment9 is null and xalg.segment9 is null))
and (gcc.segment10= xalg.segment10 or (gcc.segment10 is null and xalg.segment10 is null))
and (gcc.segment11= xalg.segment11 or (gcc.segment11 is null and xalg.segment11 is null))
and (gcc.segment12= xalg.segment12 or (gcc.segment12 is null and xalg.segment12 is null))
and (gcc.segment13= xalg.segment13 or (gcc.segment13 is null and xalg.segment13 is null))
and (gcc.segment14= xalg.segment14 or (gcc.segment14 is null and xalg.segment14 is null))
and (gcc.segment15= xalg.segment15 or (gcc.segment15 is null and xalg.segment15 is null))
and (gcc.segment16= xalg.segment16 or (gcc.segment16 is null and xalg.segment16 is null))
and (gcc.segment17= xalg.segment17 or (gcc.segment17 is null and xalg.segment17 is null))
and (gcc.segment18= xalg.segment18 or (gcc.segment18 is null and xalg.segment18 is null))
and (gcc.segment19= xalg.segment19 or (gcc.segment19 is null and xalg.segment19 is null))
and (gcc.segment20= xalg.segment20 or (gcc.segment20 is null and xalg.segment20 is null))
and (gcc.segment21= xalg.segment21 or (gcc.segment21 is null and xalg.segment21 is null))
and (gcc.segment22= xalg.segment22 or (gcc.segment22 is null and xalg.segment22 is null))
and (gcc.segment23= xalg.segment23 or (gcc.segment23 is null and xalg.segment23 is null))
and (gcc.segment24= xalg.segment24 or (gcc.segment24 is null and xalg.segment24 is null))
and (gcc.segment25= xalg.segment25 or (gcc.segment25 is null and xalg.segment25 is null))
and (gcc.segment26= xalg.segment26 or (gcc.segment26 is null and xalg.segment26 is null))
and (gcc.segment27= xalg.segment27 or (gcc.segment27 is null and xalg.segment27 is null))
and (gcc.segment28= xalg.segment28 or (gcc.segment28 is null and xalg.segment28 is null))
and (gcc.segment29= xalg.segment29 or (gcc.segment29 is null and xalg.segment29 is null))
and (gcc.segment30= xalg.segment30 or (gcc.segment30 is null and xalg.segment30 is null)))
WHERE code_combination_id is null;
trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
update xla_ae_lines_gt temp
SET code_combination_id =
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
)
WHERE temp.code_combination_id IS NULL;
trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
select 1
from xla_ae_lines_gt
where code_combination_id is null;
update xla_ae_lines_gt xalg
set ( code_combination_id
,ccid_coa_id
,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) =
(select null
,gcc.chart_of_accounts_id
,decode(segment_code, 'SEGMENT1', to_value, gcc.segment1)
,decode(segment_code, 'SEGMENT2', to_value, gcc.segment2)
,decode(segment_code, 'SEGMENT3', to_value, gcc.segment3)
,decode(segment_code, 'SEGMENT4', to_value, gcc.segment4)
,decode(segment_code, 'SEGMENT5', to_value, gcc.segment5)
,decode(segment_code, 'SEGMENT6', to_value, gcc.segment6)
,decode(segment_code, 'SEGMENT7', to_value, gcc.segment7)
,decode(segment_code, 'SEGMENT8', to_value, gcc.segment8)
,decode(segment_code, 'SEGMENT9', to_value, gcc.segment9)
,decode(segment_code, 'SEGMENT10', to_value, gcc.segment10)
,decode(segment_code, 'SEGMENT11', to_value, gcc.segment11)
,decode(segment_code, 'SEGMENT12', to_value, gcc.segment12)
,decode(segment_code, 'SEGMENT13', to_value, gcc.segment13)
,decode(segment_code, 'SEGMENT14', to_value, gcc.segment14)
,decode(segment_code, 'SEGMENT15', to_value, gcc.segment15)
,decode(segment_code, 'SEGMENT16', to_value, gcc.segment16)
,decode(segment_code, 'SEGMENT17', to_value, gcc.segment17)
,decode(segment_code, 'SEGMENT18', to_value, gcc.segment18)
,decode(segment_code, 'SEGMENT19', to_value, gcc.segment19)
,decode(segment_code, 'SEGMENT20', to_value, gcc.segment20)
,decode(segment_code, 'SEGMENT21', to_value, gcc.segment21)
,decode(segment_code, 'SEGMENT22', to_value, gcc.segment22)
,decode(segment_code, 'SEGMENT23', to_value, gcc.segment23)
,decode(segment_code, 'SEGMENT24', to_value, gcc.segment24)
,decode(segment_code, 'SEGMENT25', to_value, gcc.segment25)
,decode(segment_code, 'SEGMENT26', to_value, gcc.segment26)
,decode(segment_code, 'SEGMENT27', to_value, gcc.segment27)
,decode(segment_code, 'SEGMENT28', to_value, gcc.segment28)
,decode(segment_code, 'SEGMENT29', to_value, gcc.segment29)
,decode(segment_code, 'SEGMENT30', to_value, gcc.segment30)
from xla_merge_seg_maps map
,gl_code_combinations gcc
,XLA_LEDGER_RELATIONSHIPS_V rs
,gl_ledgers gld
where map.application_id = p_application_id
and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND rs.ledger_id = xalg.ledger_id
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = map.ledger_id
and map.event_id = p_event_id
AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
,'SEGMENT2', gcc.segment2
,'SEGMENT3', gcc.segment3
,'SEGMENT4', gcc.segment4
,'SEGMENT5', gcc.segment5
,'SEGMENT6', gcc.segment6
,'SEGMENT7', gcc.segment7
,'SEGMENT8', gcc.segment8
,'SEGMENT9', gcc.segment9
,'SEGMENT10', gcc.segment10
,'SEGMENT11', gcc.segment11
,'SEGMENT12', gcc.segment12
,'SEGMENT13', gcc.segment13
,'SEGMENT14', gcc.segment14
,'SEGMENT15', gcc.segment15
,'SEGMENT16', gcc.segment16
,'SEGMENT17', gcc.segment17
,'SEGMENT18', gcc.segment18
,'SEGMENT19', gcc.segment19
,'SEGMENT20', gcc.segment20
,'SEGMENT21', gcc.segment21
,'SEGMENT22', gcc.segment22
,'SEGMENT23', gcc.segment23
,'SEGMENT24', gcc.segment24
,'SEGMENT25', gcc.segment25
,'SEGMENT26', gcc.segment26
,'SEGMENT27', gcc.segment27
,'SEGMENT28', gcc.segment28
,'SEGMENT29', gcc.segment29
,'SEGMENT30', gcc.segment30)
= map.FROM_VALUE
and gcc.code_combination_id = xalg.code_combination_id
)
where reversal_code in ('REBOOKING', 'TRANSFER_BALANCE')
AND exists
(select 1
from xla_merge_seg_maps map
,gl_code_combinations gcc
,XLA_LEDGER_RELATIONSHIPS_V rs
,gl_ledgers gld
where map.application_id = p_application_id
and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND rs.ledger_id = xalg.ledger_id
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = map.ledger_id
and map.event_id = p_event_id
AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
,'SEGMENT2', gcc.segment2
,'SEGMENT3', gcc.segment3
,'SEGMENT4', gcc.segment4
,'SEGMENT5', gcc.segment5
,'SEGMENT6', gcc.segment6
,'SEGMENT7', gcc.segment7
,'SEGMENT8', gcc.segment8
,'SEGMENT9', gcc.segment9
,'SEGMENT10', gcc.segment10
,'SEGMENT11', gcc.segment11
,'SEGMENT12', gcc.segment12
,'SEGMENT13', gcc.segment13
,'SEGMENT14', gcc.segment14
,'SEGMENT15', gcc.segment15
,'SEGMENT16', gcc.segment16
,'SEGMENT17', gcc.segment17
,'SEGMENT18', gcc.segment18
,'SEGMENT19', gcc.segment19
,'SEGMENT20', gcc.segment20
,'SEGMENT21', gcc.segment21
,'SEGMENT22', gcc.segment22
,'SEGMENT23', gcc.segment23
,'SEGMENT24', gcc.segment24
,'SEGMENT25', gcc.segment25
,'SEGMENT26', gcc.segment26
,'SEGMENT27', gcc.segment27
,'SEGMENT28', gcc.segment28
,'SEGMENT29', gcc.segment29
,'SEGMENT30', gcc.segment30)
= map.FROM_VALUE
and gcc.code_combination_id = xalg.code_combination_id);
trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_lines_gt (
ae_header_id
,ae_line_num
,temp_line_num
,inherit_desc_flag
,header_num
,ledger_id
,ref_ae_header_id
,ccid_coa_id
,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)
(SELECT
ael.ae_header_id
,ael.ae_line_num
,ael.ae_line_num
,'N'
,ael.ae_header_id
,ael.ledger_id
,ael.ae_header_id
,gcc.chart_of_accounts_id
,decode(segment_code, 'SEGMENT1', to_value, gcc.segment1)
,decode(segment_code, 'SEGMENT2', to_value, gcc.segment2)
,decode(segment_code, 'SEGMENT3', to_value, gcc.segment3)
,decode(segment_code, 'SEGMENT4', to_value, gcc.segment4)
,decode(segment_code, 'SEGMENT5', to_value, gcc.segment5)
,decode(segment_code, 'SEGMENT6', to_value, gcc.segment6)
,decode(segment_code, 'SEGMENT7', to_value, gcc.segment7)
,decode(segment_code, 'SEGMENT8', to_value, gcc.segment8)
,decode(segment_code, 'SEGMENT9', to_value, gcc.segment9)
,decode(segment_code, 'SEGMENT10', to_value, gcc.segment10)
,decode(segment_code, 'SEGMENT11', to_value, gcc.segment11)
,decode(segment_code, 'SEGMENT12', to_value, gcc.segment12)
,decode(segment_code, 'SEGMENT13', to_value, gcc.segment13)
,decode(segment_code, 'SEGMENT14', to_value, gcc.segment14)
,decode(segment_code, 'SEGMENT15', to_value, gcc.segment15)
,decode(segment_code, 'SEGMENT16', to_value, gcc.segment16)
,decode(segment_code, 'SEGMENT17', to_value, gcc.segment17)
,decode(segment_code, 'SEGMENT18', to_value, gcc.segment18)
,decode(segment_code, 'SEGMENT19', to_value, gcc.segment19)
,decode(segment_code, 'SEGMENT20', to_value, gcc.segment20)
,decode(segment_code, 'SEGMENT21', to_value, gcc.segment21)
,decode(segment_code, 'SEGMENT22', to_value, gcc.segment22)
,decode(segment_code, 'SEGMENT23', to_value, gcc.segment23)
,decode(segment_code, 'SEGMENT24', to_value, gcc.segment24)
,decode(segment_code, 'SEGMENT25', to_value, gcc.segment25)
,decode(segment_code, 'SEGMENT26', to_value, gcc.segment26)
,decode(segment_code, 'SEGMENT27', to_value, gcc.segment27)
,decode(segment_code, 'SEGMENT28', to_value, gcc.segment28)
,decode(segment_code, 'SEGMENT29', to_value, gcc.segment29)
,decode(segment_code, 'SEGMENT30', to_value, gcc.segment30)
FROM xla_merge_seg_maps map
,gl_code_combinations gcc
,xla_ae_lines ael
,xla_ae_headers aeh
,XLA_PARTIAL_MERGE_TXNS xpmt
,XLA_LEDGER_RELATIONSHIPS_V rs
,gl_ledgers gld
WHERE ael.PARTY_ID = p_old_party_id
AND (p_old_site_id IS NULL
OR ael.PARTY_SITE_ID = p_old_site_id)
AND ael.PARTY_TYPE_CODE = p_party_type
AND ael.APPLICATION_ID = aeh.APPLICATION_ID
AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'N'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.parent_ae_header_id IS NOT NULL
AND p_array_merge_option(i) = 'TRANSFER'
AND xpmt.APPLICATION_ID = p_application_id
AND xpmt.MERGE_EVENT_ID = p_event_id
AND xpmt.ENTITY_ID = aeh.ENTITY_ID
AND map.application_id = p_application_id
and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND rs.ledger_id = aeh.ledger_id
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = map.ledger_id
and map.event_id = p_event_id
AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
,'SEGMENT2', gcc.segment2
,'SEGMENT3', gcc.segment3
,'SEGMENT4', gcc.segment4
,'SEGMENT5', gcc.segment5
,'SEGMENT6', gcc.segment6
,'SEGMENT7', gcc.segment7
,'SEGMENT8', gcc.segment8
,'SEGMENT9', gcc.segment9
,'SEGMENT10', gcc.segment10
,'SEGMENT11', gcc.segment11
,'SEGMENT12', gcc.segment12
,'SEGMENT13', gcc.segment13
,'SEGMENT14', gcc.segment14
,'SEGMENT15', gcc.segment15
,'SEGMENT16', gcc.segment16
,'SEGMENT17', gcc.segment17
,'SEGMENT18', gcc.segment18
,'SEGMENT19', gcc.segment19
,'SEGMENT20', gcc.segment20
,'SEGMENT21', gcc.segment21
,'SEGMENT22', gcc.segment22
,'SEGMENT23', gcc.segment23
,'SEGMENT24', gcc.segment24
,'SEGMENT25', gcc.segment25
,'SEGMENT26', gcc.segment26
,'SEGMENT27', gcc.segment27
,'SEGMENT28', gcc.segment28
,'SEGMENT29', gcc.segment29
,'SEGMENT30', gcc.segment30)
= map.FROM_VALUE
and gcc.code_combination_id = ael.code_combination_id);
trace( p_msg => '# of lines inserted for mapping change:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_lines_gt (
ae_header_id
,ae_line_num
,temp_line_num
,inherit_desc_flag
,header_num
,ledger_id
,ref_ae_header_id
,ccid_coa_id
,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)
(SELECT
ael.ae_header_id
,ael.ae_line_num
,ael.ae_line_num
,'N'
,ael.ae_header_id
,ael.ledger_id
,ael.ae_header_id
,gcc.chart_of_accounts_id
,decode(segment_code, 'SEGMENT1', to_value, gcc.segment1)
,decode(segment_code, 'SEGMENT2', to_value, gcc.segment2)
,decode(segment_code, 'SEGMENT3', to_value, gcc.segment3)
,decode(segment_code, 'SEGMENT4', to_value, gcc.segment4)
,decode(segment_code, 'SEGMENT5', to_value, gcc.segment5)
,decode(segment_code, 'SEGMENT6', to_value, gcc.segment6)
,decode(segment_code, 'SEGMENT7', to_value, gcc.segment7)
,decode(segment_code, 'SEGMENT8', to_value, gcc.segment8)
,decode(segment_code, 'SEGMENT9', to_value, gcc.segment9)
,decode(segment_code, 'SEGMENT10', to_value, gcc.segment10)
,decode(segment_code, 'SEGMENT11', to_value, gcc.segment11)
,decode(segment_code, 'SEGMENT12', to_value, gcc.segment12)
,decode(segment_code, 'SEGMENT13', to_value, gcc.segment13)
,decode(segment_code, 'SEGMENT14', to_value, gcc.segment14)
,decode(segment_code, 'SEGMENT15', to_value, gcc.segment15)
,decode(segment_code, 'SEGMENT16', to_value, gcc.segment16)
,decode(segment_code, 'SEGMENT17', to_value, gcc.segment17)
,decode(segment_code, 'SEGMENT18', to_value, gcc.segment18)
,decode(segment_code, 'SEGMENT19', to_value, gcc.segment19)
,decode(segment_code, 'SEGMENT20', to_value, gcc.segment20)
,decode(segment_code, 'SEGMENT21', to_value, gcc.segment21)
,decode(segment_code, 'SEGMENT22', to_value, gcc.segment22)
,decode(segment_code, 'SEGMENT23', to_value, gcc.segment23)
,decode(segment_code, 'SEGMENT24', to_value, gcc.segment24)
,decode(segment_code, 'SEGMENT25', to_value, gcc.segment25)
,decode(segment_code, 'SEGMENT26', to_value, gcc.segment26)
,decode(segment_code, 'SEGMENT27', to_value, gcc.segment27)
,decode(segment_code, 'SEGMENT28', to_value, gcc.segment28)
,decode(segment_code, 'SEGMENT29', to_value, gcc.segment29)
,decode(segment_code, 'SEGMENT30', to_value, gcc.segment30)
FROM xla_merge_seg_maps map
,gl_code_combinations gcc
,xla_ae_lines ael
,xla_ae_headers aeh
,XLA_LEDGER_RELATIONSHIPS_V rs
,gl_ledgers gld
WHERE ael.PARTY_ID = p_old_party_id
AND (p_old_site_id IS NULL
OR ael.PARTY_SITE_ID = p_old_site_id)
AND ael.PARTY_TYPE_CODE = p_party_type
AND ael.APPLICATION_ID = aeh.APPLICATION_ID
AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
AND aeh.APPLICATION_ID = p_application_id
AND aeh.accounting_entry_status_code = 'N'
AND aeh.LEDGER_ID = p_array_ledger_id(i)
AND aeh.parent_ae_header_id IS NOT NULL
AND p_array_merge_option(i) = 'TRANSFER'
AND map.application_id = p_application_id
and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND rs.ledger_id = aeh.ledger_id
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = map.ledger_id
and map.event_id = p_event_id
AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
,'SEGMENT2', gcc.segment2
,'SEGMENT3', gcc.segment3
,'SEGMENT4', gcc.segment4
,'SEGMENT5', gcc.segment5
,'SEGMENT6', gcc.segment6
,'SEGMENT7', gcc.segment7
,'SEGMENT8', gcc.segment8
,'SEGMENT9', gcc.segment9
,'SEGMENT10', gcc.segment10
,'SEGMENT11', gcc.segment11
,'SEGMENT12', gcc.segment12
,'SEGMENT13', gcc.segment13
,'SEGMENT14', gcc.segment14
,'SEGMENT15', gcc.segment15
,'SEGMENT16', gcc.segment16
,'SEGMENT17', gcc.segment17
,'SEGMENT18', gcc.segment18
,'SEGMENT19', gcc.segment19
,'SEGMENT20', gcc.segment20
,'SEGMENT21', gcc.segment21
,'SEGMENT22', gcc.segment22
,'SEGMENT23', gcc.segment23
,'SEGMENT24', gcc.segment24
,'SEGMENT25', gcc.segment25
,'SEGMENT26', gcc.segment26
,'SEGMENT27', gcc.segment27
,'SEGMENT28', gcc.segment28
,'SEGMENT29', gcc.segment29
,'SEGMENT30', gcc.segment30)
= map.FROM_VALUE
and gcc.code_combination_id = ael.code_combination_id);
trace( p_msg => '# of lines inserted for mapping change:'||to_char(v_row_count)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
select 1
from xla_ae_lines_gt
where code_combination_id is null;
UPDATE
(SELECT xalg.code_combination_id
, xal.code_combination_id code_combination_id1
FROM xla_ae_lines_gt xalg
, xla_ae_lines xal
WHERE xalg.ae_header_id = xal.ae_header_id
AND xalg.ae_line_num = xal.ae_line_num
AND xal.application_id = p_application_id
AND xalg.temp_line_num = xal.ae_line_num
AND xalg.ref_ae_header_id = xal.ae_header_id
AND xalg.ledger_id = xal.ledger_id
AND xalg.header_num = xal.ae_header_id
AND xalg.inherit_desc_flag = 'N')
SET code_combination_id1 = code_combination_id;
trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
INSERT INTO xla_ae_headers_gt
( ae_header_id
, accounting_entry_status_code
, accounting_entry_type_code
, GL_TRANSFER_STATUS_CODE
, ledger_id
, entity_id
, event_id
, event_type_code
, accounting_date
, je_category_name
, period_name
, description
, balance_type_code
, amb_context_code
, budget_version_id
-- 5103972
-- Used at the end of this procedure to find ae header ids
-- to be stamped on xla_ae_lines_gt
, parent_header_id
)
(select xla_ae_headers_s.nextval
,p_accounting_mode
,'MERGE'
,'N'
,ledger_id
, entity_id
, event_id
, event_type_code
, accounting_date
, je_category_name
, period_name
, description || p_reverse_header_desc
, balance_type_code
, null
, ae_header_id
, ref_ae_header_id -- 5103972
from
(select distinct xah.ledger_id
, xah.entity_id
, xah.event_id
, xah.event_type_code
, xah.accounting_date
, xah.je_category_name
, xah.period_name
, xah.description
, xah.balance_type_code
, xal.ae_header_id
, xal.ref_ae_header_id -- 5103972
from xla_ae_headers xah
,xla_ae_lines_gt xal
where xah.application_id = p_application_id
and xah.ae_header_id =xal.ref_ae_header_id
and xal.reversal_code = 'REBOOKING'));
trace( p_msg => '# of header inserted:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE xla_ae_headers_gt xah
SET (accounting_date, period_name) =
(SELECT start_date, period_name
FROM gl_period_statuses
WHERE ledger_id = xah.ledger_id
AND application_id = 101
AND adjustment_period_flag = 'N'
AND closing_status in ('O', 'F')
AND start_date =
(SELECT min(gps.start_date)
FROM gl_period_statuses gps
WHERE ledger_id = xah.ledger_id
AND application_id = 101
AND adjustment_period_flag = 'N'
AND start_date > xah.accounting_date
AND closing_status in ('O', 'F')))
WHERE period_name in
(SELECT period_name
FROM gl_period_statuses gps2
WHERE gps2.ledger_id = xah.ledger_id
AND gps2.adjustment_period_flag = 'N'
AND gps2.closing_status = 'C'
AND gps2.period_name = xah.period_name);
trace( p_msg => '# of header have gl date updated:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE xla_ae_lines_gt xal
SET (ae_header_id, accounting_date) =
(SELECT ae_header_id, accounting_date
FROM xla_ae_headers_gt xah
WHERE xal.event_id = xah.event_id
AND xal.ledger_id = xah.ledger_id
-- 5103972
-- Without the following line, this SQL fails as one event_id
-- could have multiple ae headers (mpa).
AND xal.ref_ae_header_id = xah.parent_header_id);
trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
SELECT evt.event_id,
evt.event_type_code,
evt.event_date,
evt.process_status_code,
evt.reference_num_1 old_party_id,
evt.reference_num_2 old_site_id,
evt.reference_num_3 new_party_id,
evt.reference_num_4 new_site_id,
p1.third_party_number original_party_number,
s1.third_party_site_code original_site_code,
p2.third_party_number new_party_number,
s2.third_party_site_code new_site_code,
evt.reference_char_1 party_type,
evt.reference_char_2 mapping_flag,
ent.entity_id,
ent.source_application_id,
ent.ledger_id
FROM xla_events evt,
xla_third_parties_v p1,
xla_third_parties_v p2,
xla_third_party_sites_v s1,
xla_third_party_sites_v s2,
xla_transaction_entities ent,
xla_ledger_options lgopt,
xla_launch_options lnopt
WHERE (p_event_id IS NULL OR evt.EVENT_ID = p_event_id)
AND ( p_merge_event_set_id IS NULL
OR evt.MERGE_EVENT_SET_ID = p_merge_event_set_id)
AND evt.APPLICATION_ID = p_application_id
AND evt.EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE')
AND evt.PROCESS_STATUS_CODE <> 'P'
AND p1.THIRD_PARTY_ID = evt.REFERENCE_NUM_1
AND p1.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
AND p2.THIRD_PARTY_ID = evt.REFERENCE_NUM_3
AND p2.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
AND s1.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_1
AND s1.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_2
AND s1.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
AND s2.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_3
AND s2.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_4
AND s2.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
AND ent.APPLICATION_ID = evt.APPLICATION_ID
AND ent.ENTITY_ID = evt.ENTITY_ID
AND ent.ENTITY_CODE = 'THIRD_PARTY_MERGE'
AND lgopt.APPLICATION_ID = ent.APPLICATION_ID
AND lgopt.LEDGER_ID = ent.LEDGER_ID
AND lgopt.ENABLED_FLAG = 'Y'
AND lnopt.APPLICATION_ID = lgopt.APPLICATION_ID
AND lnopt.LEDGER_ID = lgopt.LEDGER_ID
AND ( lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = 'Y'
OR (lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = 'N'
AND lnopt.ACCOUNTING_MODE_CODE = p_accounting_mode))
AND ( lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = 'Y'
OR (lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = 'N'
AND lnopt.SUBMIT_TRANSFER_TO_GL_FLAG
= p_transfer_to_gl_flag))
AND ( lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = 'Y'
OR (lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = 'N'
AND lnopt.SUBMIT_GL_POST_FLAG = p_post_in_gl_flag))
AND ( g_use_ledger_security = 'N'
OR (g_use_ledger_security = 'Y'
AND NOT EXISTS
(SELECT 'Ledger without access'
FROM XLA_LEDGER_RELATIONSHIPS_V rs,
XLA_LEDGER_OPTIONS lgopt2,
gl_ledgers gld
WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND DECODE(v_valuation_method_flag
, 'N', rs.PRIMARY_LEDGER_ID
, DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID)) = lgopt.LEDGER_ID
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = lgopt2.LEDGER_ID
AND lgopt2.APPLICATION_ID = lgopt.APPLICATION_ID
AND lgopt2.ENABLED_FLAG = 'Y'
AND lgopt2.MERGE_ACCT_OPTION_CODE <> 'NONE'
AND rs.LEDGER_ID NOT IN
(SELECT asa.LEDGER_ID
FROM GL_ACCESS_SET_ASSIGNMENTS asa
WHERE asa.ACCESS_SET_ID
IN (g_access_set_id, g_sec_access_set_id)))))
ORDER BY evt.EVENT_DATE, evt.EVENT_ID;
SELECT f.APPLICATION_NAME, s.VALUATION_METHOD_FLAG
INTO v_application_name, v_valuation_method_flag
FROM XLA_SUBLEDGERS s, FND_APPLICATION_VL f
WHERE s.APPLICATION_ID = f.APPLICATION_ID
AND s.APPLICATION_ID = p_application_id;
SELECT 'X'
INTO v_dummy
FROM XLA_EVENTS
WHERE EVENT_ID = p_merge_event_set_id
AND EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE');
SELECT 'X'
INTO v_dummy
FROM XLA_EVENTS
WHERE EVENT_ID = p_event_id
AND EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE');
SELECT rs.LEDGER_ID,
rs.LEDGER_CATEGORY_CODE,
lgopt.ACCT_REVERSAL_OPTION_CODE,
nvl(lgopt.MERGE_ACCT_OPTION_CODE, 'NONE'),
lgopt.ROUNDING_RULE_CODE,
rs.CURRENCY_CODE,
nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)),
'N'
BULK COLLECT INTO
v_array_ledger_id,
v_array_ledger_category,
v_array_reversal_option,
v_array_merge_option,
v_array_rounding_rule_code,
v_array_currency_code,
v_array_mau,
v_array_submit_transfer
FROM XLA_LEDGER_RELATIONSHIPS_V rs,
XLA_LEDGER_OPTIONS lgopt,
FND_CURRENCIES fcu,
GL_LEDGERS gld
WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND rs.ledger_id = gld.ledger_id
AND gld.complete_flag = 'Y'
AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
AND DECODE(decode(v_valuation_method_flag, 'N', 'N', lgopt.capture_event_flag)
, 'N', rs.PRIMARY_LEDGER_ID
, DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID)) = v_event_ledger_id
AND DECODE(rs.LEDGER_CATEGORY_CODE
, 'ALC', rs.PRIMARY_LEDGER_ID
, rs.LEDGER_ID) = lgopt.LEDGER_ID
AND lgopt.APPLICATION_ID = p_application_id
AND lgopt.ENABLED_FLAG = 'Y'
and rs.currency_code = fcu.currency_code;
trace( p_msg => 'Delete draft entries'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
delete_je (
p_application_id => p_application_id
, p_event_id => v_event_id);
trace( p_msg => 'Start to update third party information'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
update_journal_entries(
x_errbuf => x_errbuf
,x_retcode => x_retcode
,p_application_id => p_application_id
,p_event_id => v_event_id
,p_event_merge_option => v_event_merge_option
,p_entity_id => v_entity_id
,p_mapping_flag => v_mapping_flag
,p_event_ledger_id => v_event_ledger_id
,p_merge_date => v_merge_date
,p_merge_type => v_merge_type
,p_old_site_id => v_old_site_id
,p_old_party_id => v_old_party_id
,p_new_site_id => v_new_site_id
,p_new_party_id => v_new_party_id
,p_party_type => v_party_type
,p_line_desc => v_ael_desc1
,p_array_ledger_id => v_array_ledger_id
,p_array_ledger_category => v_array_ledger_category
,p_array_reversal_option => v_array_reversal_option
,p_array_merge_option => v_array_merge_option
,p_array_submit_transfer => v_array_submit_transfer);
trace( p_msg => 'Update the current event status'
, p_level => C_LEVEL_STATEMENT
, p_module => v_module);
UPDATE XLA_EVENTS
SET EVENT_STATUS_CODE = DECODE(p_accounting_mode
, 'F', 'P', EVENT_STATUS_CODE),
PROCESS_STATUS_CODE = p_accounting_mode
WHERE EVENT_ID = v_event_id;
PROCEDURE delete_je(
p_application_id IN INTEGER
, p_event_id IN INTEGER) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_je';
(p_msg => 'BEGIN of procedure DELETE_JE'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
DELETE FROM xla_accounting_errors
WHERE event_id IN
(SELECT event_id FROM xla_events
WHERE application_id = g_application_id
AND request_id = g_report_request_id);
DELETE FROM xla_distribution_links
WHERE ae_header_id IN
(SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = p_application_id
AND merge_event_id = p_event_id);
(p_msg => 'Number of distribution links deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_segment_values
WHERE ae_header_id IN
(SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = p_application_id
AND merge_event_id = p_event_id);
(p_msg => 'Number of segment values deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_line_details
WHERE ae_header_id IN
(SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = p_application_id
AND merge_event_id = p_event_id);
(p_msg => 'Number of line details deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_header_details
WHERE ae_header_id IN
(SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = p_application_id
AND merge_event_id = p_event_id);
(p_msg => 'Number of header details deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_lines
WHERE application_id = p_application_id
AND ae_header_id IN
(SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = p_application_id
AND merge_event_id = p_event_id);
(p_msg => 'Number of ae lines deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
DELETE FROM xla_ae_headers
WHERE application_id = p_application_id
AND merge_event_id = p_event_id;
(p_msg => 'Number of ae headers deleted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of procedure DELETE_JE'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'xla_third_party_merge.delete_je');
END delete_je;