[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT closing_status, period_name, period_type
FROM gl_period_statuses
WHERE application_id = C_GL_APPLICATION_ID
AND ledger_id = p_ledger_id
AND adjustment_period_flag = 'N'
AND p_accounting_date BETWEEN start_date AND end_date;
select user_profile_option_name
from fnd_profile_options_vl
where profile_option_name = l_profile_name;
SELECT nvl(control_account_type_code, 'N')
-- ,control_account_enabled_flag
,je_source_name
INTO g_app_ctl_acct_source_code
-- ,g_app_ctl_acct_enabled_flag
,g_app_je_source_name
FROM xla_subledgers
WHERE application_id = g_application_id;
UPDATE xla_ae_headers_gt h
SET (period_year,period_closing_status) =
(SELECT period_year,closing_status
FROM gl_period_statuses gl
WHERE gl.period_name = h.period_name
AND gl.ledger_id = h.ledger_id
AND gl.application_id = 101);
SELECT security_segment_code, chart_of_accounts_id
INTO g_pri_security_seg_code, g_pri_coa_id
FROM gl_access_sets
WHERE access_set_id = g_pri_access_set_id;
SELECT security_segment_code, chart_of_accounts_id
INTO g_sec_security_seg_code, g_sec_coa_id
FROM gl_access_sets
WHERE access_set_id = g_sec_access_set_id;
SELECT asa.ledger_id
FROM gl_access_set_assignments asa
WHERE asa.ledger_id = g_ledger_id
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.access_set_id = g_pri_access_set_id;
SELECT asa.ledger_id
FROM gl_access_set_assignments asa
WHERE asa.ledger_id = g_ledger_id
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.access_set_id in (g_pri_access_set_id, g_sec_access_set_id);
SELECT entity_id, event_id, ae_header_id
FROM xla_validation_lines_gt;
SELECT u.user_name, a.name
INTO g_user_name, g_access_set_name
FROM fnd_user u, gl_access_sets a
WHERE u.user_id = xla_environment_pkg.g_usr_id
AND a.access_set_id = g_pri_access_set_id;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,NULL segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
on asa.ledger_id = g_ledger_id
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.access_set_id = g_pri_access_set_id
LEFT OUTER JOIN gl_access_set_assignments asa2
on asa2.segment_value = t.bal_seg_value
AND asa2.ledger_id = g_ledger_id
AND asa2.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa2.access_set_id = g_sec_access_set_id
WHERE asa.access_set_id IS NULL
AND asa2.access_set_id IS NULL;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,NULL segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
on asa.ledger_id = g_ledger_id
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.access_set_id = g_pri_access_set_id
LEFT OUTER JOIN gl_access_set_assignments asa2
on asa2.segment_value = t.mgt_seg_value
AND asa2.ledger_id = g_ledger_id
AND asa2.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa2.access_set_id = g_sec_access_set_id
WHERE asa.access_set_id IS NULL
AND asa2.access_set_id IS NULL;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,t.bal_seg_value segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
ON asa.segment_value = t.bal_seg_value
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.ledger_id = g_ledger_id
AND asa.access_set_id = g_pri_access_set_id
WHERE asa.access_set_id IS NULL;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,t.bal_seg_value segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
ON asa.segment_value = t.bal_seg_value
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.ledger_id = g_ledger_id
AND asa.access_set_id in (g_pri_access_set_id, g_sec_access_set_id)
WHERE asa.access_set_id IS NULL;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,t.bal_seg_value segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
on asa.segment_value = t.bal_seg_value
AND asa.ledger_id = g_ledger_id
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.access_set_id = g_pri_access_set_id
LEFT OUTER JOIN gl_access_set_assignments asa2
on asa2.segment_value = t.mgt_seg_value
AND asa2.ledger_id = g_ledger_id
AND asa2.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa2.access_set_id = g_sec_access_set_id
WHERE asa.access_set_id IS NULL
AND asa2.access_set_id IS NULL;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,t.mgt_seg_value segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
ON asa.segment_value = t.mgt_seg_value
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.ledger_id = g_ledger_id
AND asa.access_set_id = g_pri_access_set_id
WHERE asa.access_set_id IS NULL;
SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
,t.mgt_seg_value segment_value
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_access_set_assignments asa
ON asa.segment_value = t.mgt_seg_value
AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
AND asa.ledger_id = g_ledger_id
AND asa.access_set_id in (g_pri_access_set_id, g_sec_access_set_id)
WHERE asa.access_set_id IS NULL;
SELECT u.user_name, a.name
INTO g_user_name, g_access_set_name
FROM fnd_user u, gl_access_sets a
WHERE u.user_id = xla_environment_pkg.g_usr_id
AND a.access_set_id = g_pri_access_set_id;
INSERT INTO xla_validation_lines_gt
(ae_header_id
,ae_line_num
,ledger_id
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,entity_id
,event_id
,balance_type_code
,budget_version_id
,encumbrance_type_id
,accounting_date
,je_category_name
,party_type_code
,party_id
,party_site_id
,entered_currency_code
,unrounded_entered_cr
,unrounded_entered_dr
,entered_cr
,entered_dr
,entered_currency_mau
,unrounded_accounted_cr
,unrounded_accounted_dr
,accounted_cr
,accounted_dr
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,code_combination_id
,accounting_class_code
,bal_seg_value
,mgt_seg_value
,cost_center_seg_value
,natural_account_seg_value
,ccid_coa_id
,ccid_enabled_flag
,ccid_summary_flag
,detail_posting_allowed_flag
,detail_budgeting_allowed_flag
,control_account_enabled_flag
,product_rule_type_code
,product_rule_code
,balancing_line_type
,error_flag
,substituted_ccid
,accounting_entry_status_code
,period_name
,gain_or_loss_flag
)
SELECT /*+ cardinality(h,1) index(l, XLA_AE_LINES_U1) use_nl(l) use_nl(ccid) */
h.ae_header_id
,l.ae_line_num
,h.ledger_id
,l.displayed_line_number
,max(l.ae_line_num) over (partition by l.ae_header_id)
,max(l.displayed_line_number) over (partition by l.ae_header_id)
,h.entity_id
,h.event_id
,h.balance_type_code
,h.budget_version_id
,l.encumbrance_type_id
,h.accounting_date
,h.je_category_name
,l.party_type_code
,l.party_id
,l.party_site_id
,l.currency_code
,l.unrounded_entered_cr
,l.unrounded_entered_dr
,l.entered_cr
,l.entered_dr
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,l.unrounded_accounted_cr
,l.unrounded_accounted_dr
,l.accounted_cr
,l.accounted_dr
,l.currency_conversion_type
,l.currency_conversion_date
,l.currency_conversion_rate
,l.code_combination_id
,l.accounting_class_code
,ccid.'||g_bal_seg_column_name||'
,'||CASE WHEN g_mgt_seg_column_name is NULL THEN 'NULL' ELSE 'ccid.'||g_mgt_seg_column_name END||'
,'||CASE WHEN g_cc_seg_column_name is NULL THEN 'NULL' ELSE 'ccid.'||g_cc_seg_column_name END||'
,'||CASE WHEN g_na_seg_column_name is NULL THEN 'NULL' ELSE 'ccid.'||g_na_seg_column_name END||'
,ccid.chart_of_accounts_id
-- ccid_enabled_flag
,CASE WHEN ccid.enabled_flag IS NULL THEN NULL
WHEN ccid.enabled_flag = ''N'' THEN ''N''
WHEN h.accounting_date < nvl(ccid.start_date_active, h.accounting_date) THEN ''D''
WHEN h.accounting_date > nvl(ccid.end_date_active, h.accounting_date) THEN ''D''
ELSE ''Y''
END
,CASE WHEN ccid.summary_flag = ''Y'' THEN ''Y'' ELSE ''N'' END
,ccid.detail_posting_allowed_flag
,ccid.detail_budgeting_allowed_flag
,nvl(ccid.reference3,''N'')
,h.product_rule_type_code
,h.product_rule_code
,'''||C_LINE_TYPE_PROCESS||'''
,CASE WHEN ccid.enabled_flag IS NULL
or (ccid.code_combination_id = -1 and nvl(l.gain_or_loss_flag, ''N'')=''Y'')
or l.accounting_class_code IS NULL
or ccid.enabled_flag = ''N''
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date)
or (ccid.summary_flag = ''Y'')
or (h.balance_type_code <> ''B'' AND ccid.detail_posting_allowed_flag = ''N'')
or (h.balance_type_code = ''B'' AND ccid.detail_budgeting_allowed_flag = ''N'')
or ('''||g_app_ctl_acct_source_code||''' <> ''Y'' AND
(nvl(ccid.reference3,''N'') NOT IN (''Y'', ''N'', '''||g_app_ctl_acct_source_code||''')))
or ('''||g_app_ctl_acct_source_code||''' = ''N'' AND nvl(ccid.reference3,''N'') <> ''N'')
or (nvl(ccid.reference3,''N'') <> ''N'' AND
(l.party_type_code IS NULL OR l.party_id IS NULL))
or (nvl(ccid.reference3,''N'') = ''CUSTOMER'' AND l.party_type_code <> ''C'')
or (nvl(ccid.reference3,''N'') = ''SUPPLIER'' AND l.party_type_code <> ''S'')
or (l.party_type_code IS NOT NULL AND l.party_type_code NOT IN (''C'', ''S''))
or ((l.party_id IS NOT NULL OR l.party_site_id IS NOT NULL) AND l.party_type_code IS NULL)
-- or ((l.party_site_id IS NOT NULL OR l.party_type_code IS NOT NULL) AND l.party_id IS NULL)
or (nvl(l.gain_or_loss_flag,''N'') = ''N'' AND l.entered_dr IS NULL AND l.entered_cr IS NULL)
or (l.entered_dr IS NOT NULL AND l.accounted_dr IS NULL)
or (l.entered_cr IS NOT NULL AND l.accounted_cr IS NULL)
or (nvl(l.gain_or_loss_flag, ''N'') = ''N'' and l.entered_dr IS NULL AND l.accounted_dr IS NOT NULL)
or (nvl(l.gain_or_loss_flag, ''N'') = ''N'' and l.entered_cr IS NULL AND l.accounted_cr IS NOT NULL)
or (NVL(l.entered_cr,0) > 0 AND NVL(l.accounted_cr,0) < 0)
or (NVL(l.entered_dr,0) > 0 AND NVL(l.accounted_dr,0) < 0)
or (NVL(l.entered_cr,0) < 0 AND NVL(l.accounted_cr,0) > 0)
or (NVL(l.entered_dr,0) < 0 AND NVL(l.accounted_dr,0) > 0)
or (:1 = l.currency_code AND nvl(l.gain_or_loss_flag, ''N'') = ''N'' AND
(nvl(l.unrounded_entered_dr,9E125) <> nvl(l.unrounded_accounted_dr,9E125) or
nvl(l.unrounded_entered_cr,9E125) <> nvl(l.unrounded_accounted_cr,9E125)))
or (:2 = l.currency_code AND
(l.currency_conversion_type IS NOT NULL or nvl(l.currency_conversion_rate,1) <> 1))
or (:3 <> l.currency_code AND
((l.currency_conversion_type = ''User'' AND l.currency_conversion_rate IS NULL) or
(nvl(l.currency_conversion_type,''User'') <> ''User'' AND l.currency_conversion_date IS NULL)))
or (:4 <> ccid.chart_of_accounts_id)
or (l.accounted_cr is NULL and l.accounted_dr is NULL and l.currency_conversion_rate is NULL)
THEN ''Y''
ELSE NULL
END
,NULL -- substituted_ccid
,h.accounting_entry_status_code
,h.period_name
,l.gain_or_loss_flag
FROM xla_ae_headers_gt h
,xla_ae_lines l
,gl_code_combinations ccid
,fnd_currencies fcu
WHERE ccid.code_combination_id(+) = l.code_combination_id
AND l.ae_header_id = h.ae_header_id
AND h.ledger_id = :5
AND l.currency_code = fcu.currency_code
AND l.application_id = '||g_application_id;
trace(p_msg => '# of rows inserted:'||to_char(SQL%ROWCOUNT),
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
INSERT INTO xla_validation_lines_gt
(ae_header_id
,ae_line_num
,ledger_id
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,entity_id
,event_id
,balance_type_code
,budget_version_id
,encumbrance_type_id
,accounting_date
,je_category_name
,party_type_code
,party_id
,party_site_id
,entered_currency_code
,unrounded_entered_cr
,unrounded_entered_dr
,entered_cr
,entered_dr
,entered_currency_mau
,unrounded_accounted_cr
,unrounded_accounted_dr
,accounted_cr
,accounted_dr
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,code_combination_id
,accounting_class_code
,bal_seg_value
,mgt_seg_value
,cost_center_seg_value
,natural_account_seg_value
,ccid_coa_id
,ccid_enabled_flag
,ccid_summary_flag
,detail_posting_allowed_flag
,detail_budgeting_allowed_flag
,control_account_enabled_flag
,product_rule_type_code
,product_rule_code
,balancing_line_type
,error_flag
,gain_or_loss_flag
,substituted_by_suspense_flag
,substituted_ccid
,suspense_code_combination_id
,accounting_entry_status_code
,period_name
)
SELECT /*+ leading(h) cardinality(h,1) index(l, XLA_AE_LINES_U1) use_nl(l) use_nl(ccid) */
h.ae_header_id
,l.ae_line_num
,h.ledger_id
,l.displayed_line_number
,max(l.ae_line_num) over (partition by l.ae_header_id)
,max(l.displayed_line_number) over (partition by l.ae_header_id)
,h.entity_id
,h.event_id
,h.balance_type_code
,h.budget_version_id
,l.encumbrance_type_id
,h.accounting_date
,h.je_category_name
,l.party_type_code
,l.party_id
,l.party_site_id
,l.currency_code
,l.unrounded_entered_cr
,l.unrounded_entered_dr
,l.entered_cr
,l.entered_dr
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,l.unrounded_accounted_cr
,l.unrounded_accounted_dr
,l.accounted_cr
,l.accounted_dr
,l.currency_conversion_type
,l.currency_conversion_date
,l.currency_conversion_rate
-- code_combination_id
,CASE
WHEN l.code_combination_id <> -1
and (ccid.enabled_flag = ''N''
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date))
and ccid.alternate_code_combination_id is not NULL
THEN -- ccid disabled or outdated, ccid1 defined
CASE
WHEN nvl(gsa.code_combination_id, nvl(gsa1.code_combination_id, nvl(gsa2.code_combination_id, gsa3.code_combination_id))) is not NULL
and (--ccid1.enabled_flag is NULL
ccid1.enabled_flag = ''N''
or h.accounting_date < nvl(ccid1.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid1.end_date_active, h.accounting_date)
--or ccid1.summary_flag = ''Y''
--or (ccid1.detail_posting_allowed_flag = ''N'' and h.balance_type_code <>''B'')
--or (ccid1.detail_budgeting_allowed_flag = ''N'' and h.balance_type_code = ''B'')
)
THEN nvl(gsa.code_combination_id, nvl(gsa1.code_combination_id, nvl(gsa2.code_combination_id, gsa3.code_combination_id)))
ELSE ccid.alternate_code_combination_id
END
WHEN l.code_combination_id <> -1
and nvl(gsa.code_combination_id, nvl(gsa1.code_combination_id, nvl(gsa2.code_combination_id, gsa3.code_combination_id))) is not NULL
and (--ccid.enabled_flag is NULL
ccid.enabled_flag = ''N''
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date)
--or ccid.summary_flag = ''Y''
--or (ccid.detail_posting_allowed_flag = ''N'' and h.balance_type_code <> ''B'')
--or (ccid.detail_budgeting_allowed_flag =''N'' and h.balance_type_code = ''B'')
)
THEN nvl(gsa.code_combination_id, nvl(gsa1.code_combination_id, nvl(gsa2.code_combination_id, gsa3.code_combination_id)))
ELSE l.code_combination_id
END
,l.accounting_class_code
,CASE WHEN l.code_combination_id <> -1
and (ccid.enabled_flag = ''N''
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date))
and ccid.alternate_code_combination_id is not NULL
THEN ccid1.'||g_bal_seg_column_name||'
ELSE ccid.'||g_bal_seg_column_name||'
END';
trace(p_msg => '# of rows inserted:'||to_char(SQL%ROWCOUNT),
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
'UPDATE xla_validation_lines_gt l
SET
( l.bal_seg_value
,l.mgt_seg_value
,l.cost_center_seg_value
,l.natural_account_seg_value
,l.ccid_enabled_flag
,l.ccid_summary_flag
,l.detail_posting_allowed_flag
,l.detail_budgeting_allowed_flag
,l.control_account_enabled_flag
,l.error_flag) =
( SELECT
ccid.' || g_bal_seg_column_name;
trace(p_msg => 'UPDATE sql:',
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
trace(p_msg => '# of rows updated:'||to_char(SQL%ROWCOUNT),
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
INSERT INTO xla_validation_lines_gt
(ae_header_id
,ae_line_num
,ledger_id
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,entity_id
,event_id
,balance_type_code
,budget_version_id
,encumbrance_type_id
,accounting_date
,je_category_name
,party_type_code
,party_id
,party_site_id
,entered_currency_code
,unrounded_entered_cr
,unrounded_entered_dr
,entered_cr
,entered_dr
,entered_currency_mau
,unrounded_accounted_cr
,unrounded_accounted_dr
,accounted_cr
,accounted_dr
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,code_combination_id
,accounting_class_code
,bal_seg_value
,mgt_seg_value
,cost_center_seg_value
,natural_account_seg_value
,ccid_coa_id
,ccid_enabled_flag
,ccid_summary_flag
,detail_posting_allowed_flag
,detail_budgeting_allowed_flag
,control_account_enabled_flag
,product_rule_type_code
,product_rule_code
,balancing_line_type
,error_flag
,substituted_ccid
,accounting_entry_status_code
,period_name
,gain_or_loss_flag
)
SELECT /*+ cardinality(h,1) index(l, XLA_AE_LINES_U1) use_nl(l) use_nl(ccid) */
h.ae_header_id
,l.ae_line_num
,h.ledger_id
,l.displayed_line_number
,max(l.ae_line_num) over (partition by l.ae_header_id)
,max(l.displayed_line_number) over (partition by l.ae_header_id)
,h.entity_id
,h.event_id
,h.balance_type_code
,h.budget_version_id
,l.encumbrance_type_id
,h.accounting_date
,h.je_category_name
,l.party_type_code
,l.party_id
,l.party_site_id
,l.currency_code
,l.unrounded_entered_cr
,l.unrounded_entered_dr
,l.entered_cr
,l.entered_dr
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,l.unrounded_accounted_cr
,l.unrounded_accounted_dr
,l.accounted_cr
,l.accounted_dr
,l.currency_conversion_type
,l.currency_conversion_date
,l.currency_conversion_rate
,CASE
WHEN l.code_combination_id <> -1
and (ccid.enabled_flag = ''N''
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date))
and ccid.alternate_code_combination_id is not NULL
THEN
ccid1.code_combination_id
ELSE l.code_combination_id
END
,l.accounting_class_code
,CASE
WHEN l.code_combination_id <> -1
and (ccid.enabled_flag = ''N''
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date))
and ccid.alternate_code_combination_id is not NULL
THEN
ccid1.'||g_bal_seg_column_name||'
ELSE ccid.'||g_bal_seg_column_name||'
END';
trace(p_msg => '# of rows inserted:'||to_char(SQL%ROWCOUNT),
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
UPDATE xla_ae_headers
SET zero_amount_flag = 'Y'
WHERE application_id = g_application_id and
ae_header_id in
(select ae_header_id
from xla_validation_lines_gt
group by ae_header_id
having sum(abs(accounted_cr)) = 0 and sum(abs(accounted_dr))=0);
UPDATE /*+ index(XAL,XLA_AE_LINES_U1)*/ xla_ae_lines xal -- 4769388
SET (code_combination_id, substituted_ccid)=
(SELECT code_combination_id, substituted_ccid
FROM xla_validation_lines_gt xvlg
WHERE xvlg.ae_header_id = xal.ae_header_id
AND xvlg.ae_line_num = xal.ae_line_num)
WHERE xal.application_id = g_application_id
AND (xal.ae_header_id, xal.ae_line_num) in
(select /*+ unnest cardinality(GT,10)*/ -- 4769388
ae_header_id, ae_line_num
from xla_validation_lines_gt GT -- 4769388
where substituted_ccid is not NULL);
trace(p_msg => '# of rows updated to xla_ae_lines:'||to_char(SQL%ROWCOUNT),
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
INSERT INTO xla_validation_lines_gt
(ae_header_id
,ae_line_num
,ledger_id
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,entity_id
,event_id
,balance_type_code
,budget_version_id
,encumbrance_type_id
,accounting_date
,je_category_name
,party_type_code
,party_id
,party_site_id
,entered_currency_code
,unrounded_entered_cr
,unrounded_entered_dr
,entered_cr
,entered_dr
,entered_currency_mau
,unrounded_accounted_cr
,unrounded_accounted_dr
,accounted_cr
,accounted_dr
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,code_combination_id
,accounting_class_code
,bal_seg_value
,mgt_seg_value
,cost_center_seg_value
,natural_account_seg_value
,ccid_coa_id
,ccid_enabled_flag
,ccid_summary_flag
,detail_posting_allowed_flag
,detail_budgeting_allowed_flag
,control_account_enabled_flag
,accounting_entry_status_code
,period_name
,balancing_line_type
,error_flag)
SELECT h.ae_header_id
,l.ae_line_num
,h.ledger_id
,l.displayed_line_number
,max(l.ae_line_num) over (partition by l.ae_header_id)
,max(l.displayed_line_number) over (partition by l.ae_header_id)
,h.entity_id
,h.event_id
,h.balance_type_code
,h.budget_version_id
,l.encumbrance_type_id
,h.accounting_date
,h.je_category_name
,l.party_type_code
,l.party_id
,l.party_site_id
,l.currency_code
,l.unrounded_entered_cr
,l.unrounded_entered_dr
,l.entered_cr
,l.entered_dr
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,l.unrounded_accounted_cr
,l.unrounded_accounted_dr
,l.accounted_cr
,l.accounted_dr
,l.currency_conversion_type
,l.currency_conversion_date
,l.currency_conversion_rate
,l.code_combination_id
,l.accounting_class_code
,decode(g_bal_seg_column_name,
'SEGMENT1', ccid.segment1,
'SEGMENT2', ccid.segment2,
'SEGMENT3', ccid.segment3,
'SEGMENT4', ccid.segment4,
'SEGMENT5', ccid.segment5,
'SEGMENT6', ccid.segment6,
'SEGMENT7', ccid.segment7,
'SEGMENT8', ccid.segment8,
'SEGMENT9', ccid.segment9,
'SEGMENT10', ccid.segment10,
'SEGMENT11', ccid.segment11,
'SEGMENT12', ccid.segment12,
'SEGMENT13', ccid.segment13,
'SEGMENT14', ccid.segment14,
'SEGMENT15', ccid.segment15,
'SEGMENT16', ccid.segment16,
'SEGMENT17', ccid.segment17,
'SEGMENT18', ccid.segment18,
'SEGMENT19', ccid.segment19,
'SEGMENT20', ccid.segment20,
'SEGMENT21', ccid.segment21,
'SEGMENT22', ccid.segment22,
'SEGMENT23', ccid.segment23,
'SEGMENT24', ccid.segment24,
'SEGMENT25', ccid.segment25,
'SEGMENT26', ccid.segment26,
'SEGMENT27', ccid.segment27,
'SEGMENT28', ccid.segment28,
'SEGMENT29', ccid.segment29,
'SEGMENT30', ccid.segment30,
NULL)
,decode(g_mgt_seg_column_name,
'SEGMENT1', ccid.segment1,
'SEGMENT2', ccid.segment2,
'SEGMENT3', ccid.segment3,
'SEGMENT4', ccid.segment4,
'SEGMENT5', ccid.segment5,
'SEGMENT6', ccid.segment6,
'SEGMENT7', ccid.segment7,
'SEGMENT8', ccid.segment8,
'SEGMENT9', ccid.segment9,
'SEGMENT10', ccid.segment10,
'SEGMENT11', ccid.segment11,
'SEGMENT12', ccid.segment12,
'SEGMENT13', ccid.segment13,
'SEGMENT14', ccid.segment14,
'SEGMENT15', ccid.segment15,
'SEGMENT16', ccid.segment16,
'SEGMENT17', ccid.segment17,
'SEGMENT18', ccid.segment18,
'SEGMENT19', ccid.segment19,
'SEGMENT20', ccid.segment20,
'SEGMENT21', ccid.segment21,
'SEGMENT22', ccid.segment22,
'SEGMENT23', ccid.segment23,
'SEGMENT24', ccid.segment24,
'SEGMENT25', ccid.segment25,
'SEGMENT26', ccid.segment26,
'SEGMENT27', ccid.segment27,
'SEGMENT28', ccid.segment28,
'SEGMENT29', ccid.segment29,
'SEGMENT30', ccid.segment30,
NULL)
,decode(g_cc_seg_column_name,
'SEGMENT1', ccid.segment1,
'SEGMENT2', ccid.segment2,
'SEGMENT3', ccid.segment3,
'SEGMENT4', ccid.segment4,
'SEGMENT5', ccid.segment5,
'SEGMENT6', ccid.segment6,
'SEGMENT7', ccid.segment7,
'SEGMENT8', ccid.segment8,
'SEGMENT9', ccid.segment9,
'SEGMENT10', ccid.segment10,
'SEGMENT11', ccid.segment11,
'SEGMENT12', ccid.segment12,
'SEGMENT13', ccid.segment13,
'SEGMENT14', ccid.segment14,
'SEGMENT15', ccid.segment15,
'SEGMENT16', ccid.segment16,
'SEGMENT17', ccid.segment17,
'SEGMENT18', ccid.segment18,
'SEGMENT19', ccid.segment19,
'SEGMENT20', ccid.segment20,
'SEGMENT21', ccid.segment21,
'SEGMENT22', ccid.segment22,
'SEGMENT23', ccid.segment23,
'SEGMENT24', ccid.segment24,
'SEGMENT25', ccid.segment25,
'SEGMENT26', ccid.segment26,
'SEGMENT27', ccid.segment27,
'SEGMENT28', ccid.segment28,
'SEGMENT29', ccid.segment29,
'SEGMENT30', ccid.segment30,
NULL)
,decode(g_na_seg_column_name,
'SEGMENT1', ccid.segment1,
'SEGMENT2', ccid.segment2,
'SEGMENT3', ccid.segment3,
'SEGMENT4', ccid.segment4,
'SEGMENT5', ccid.segment5,
'SEGMENT6', ccid.segment6,
'SEGMENT7', ccid.segment7,
'SEGMENT8', ccid.segment8,
'SEGMENT9', ccid.segment9,
'SEGMENT10', ccid.segment10,
'SEGMENT11', ccid.segment11,
'SEGMENT12', ccid.segment12,
'SEGMENT13', ccid.segment13,
'SEGMENT14', ccid.segment14,
'SEGMENT15', ccid.segment15,
'SEGMENT16', ccid.segment16,
'SEGMENT17', ccid.segment17,
'SEGMENT18', ccid.segment18,
'SEGMENT19', ccid.segment19,
'SEGMENT20', ccid.segment20,
'SEGMENT21', ccid.segment21,
'SEGMENT22', ccid.segment22,
'SEGMENT23', ccid.segment23,
'SEGMENT24', ccid.segment24,
'SEGMENT25', ccid.segment25,
'SEGMENT26', ccid.segment26,
'SEGMENT27', ccid.segment27,
'SEGMENT28', ccid.segment28,
'SEGMENT29', ccid.segment29,
'SEGMENT30', ccid.segment30,
NULL)
,ccid.chart_of_accounts_id
,CASE WHEN g_caller = C_CALLER_ACCT_PROGRAM AND l.code_combination_id = -1 THEN 'Y'
WHEN ccid.enabled_flag IS NULL THEN NULL
WHEN ccid.enabled_flag = 'N' THEN 'N'
WHEN h.accounting_date < nvl(ccid.start_date_active, h.accounting_date) THEN 'D'
WHEN h.accounting_date > nvl(ccid.end_date_active, h.accounting_date) THEN 'D'
ELSE 'Y' END
,CASE WHEN ccid.summary_flag = 'Y' THEN 'Y' ELSE 'N' END
,ccid.detail_posting_allowed_flag
,ccid.detail_budgeting_allowed_flag
,nvl(ccid.reference3,'N')
,h.accounting_entry_status_code
,h.period_name
,C_LINE_TYPE_PROCESS
,CASE WHEN ccid.enabled_flag IS NULL
or ccid.enabled_flag = 'N'
or l.accounting_class_code IS NULL
or h.accounting_date < nvl(ccid.start_date_active, h.accounting_date)
or h.accounting_date > nvl(ccid.end_date_active, h.accounting_date)
or (ccid.summary_flag = 'Y')
or (h.balance_type_code <> 'B' AND ccid.detail_posting_allowed_flag = 'N')
or (h.balance_type_code = 'B' AND ccid.detail_budgeting_allowed_flag = 'N')
or (g_app_ctl_acct_source_code <> 'Y'
AND (nvl(ccid.reference3,'N') NOT IN ('Y', 'N', g_app_ctl_acct_source_code)))
or (g_app_ctl_acct_source_code= 'N' AND nvl(ccid.reference3,'N') <> 'N')
or (nvl(ccid.reference3,'N') <> 'N' AND
(l.party_type_code IS NULL OR l.party_id IS NULL))
or (nvl(ccid.reference3,'N') = 'CUSTOMER' AND l.party_type_code <> 'C')
or (nvl(ccid.reference3,'N') = 'SUPPLIER' AND l.party_type_code <> 'S')
or (l.party_type_code IS NOT NULL AND l.party_type_code NOT IN ('C', 'S'))
or ((l.party_id IS NOT NULL OR l.party_site_id IS NOT NULL) AND l.party_type_code IS NULL)
-- or ((l.party_site_id IS NOT NULL OR l.party_type_code IS NOT NULL) AND l.party_id IS NULL)
or (l.entered_dr IS NULL AND l.entered_cr IS NULL)
or (l.entered_dr IS NOT NULL AND l.accounted_dr IS NULL)
or (l.entered_cr IS NOT NULL AND l.accounted_cr IS NULL)
or (l.entered_dr IS NULL AND l.accounted_dr IS NOT NULL)
or (l.entered_cr IS NULL AND l.accounted_cr IS NOT NULL)
or (NVL(l.entered_cr,0) > 0 AND NVL(l.accounted_cr,0) < 0)
or (NVL(l.entered_dr,0) > 0 AND NVL(l.accounted_dr,0) < 0)
or (NVL(l.entered_cr,0) < 0 AND NVL(l.accounted_cr,0) > 0)
or (NVL(l.entered_dr,0) < 0 AND NVL(l.accounted_dr,0) > 0)
or (g_ledger_currency_code = l.currency_code AND
(nvl(l.unrounded_entered_dr,C_NUM) <> nvl(l.unrounded_accounted_dr,C_NUM) or
nvl(l.unrounded_entered_cr,C_NUM) <> nvl(l.unrounded_accounted_cr,C_NUM)))
or (g_ledger_currency_code = l.currency_code AND
(l.currency_conversion_type IS NOT NULL or nvl(l.currency_conversion_rate,1) <> 1))
or (g_ledger_currency_code <> l.currency_code AND
((l.currency_conversion_type = 'User' AND l.currency_conversion_rate IS NULL) or
(nvl(l.currency_conversion_type,'User') <> 'User' AND l.currency_conversion_date IS NULL)))
or (g_ledger_coa_id <> ccid.chart_of_accounts_id)
THEN 'Y'
ELSE NULL
END
FROM xla_ae_headers h
,xla_ae_lines l
,gl_code_combinations ccid
,fnd_currencies fcu
WHERE ccid.code_combination_id(+) = l.code_combination_id
AND l.ae_header_id = h.ae_header_id
AND l.application_id = h.application_id
AND l.currency_code = fcu.currency_code
AND h.ledger_id = g_ledger_id
AND h.ae_header_id = g_ae_header_id
AND h.application_id = g_application_id;
trace(p_msg => '# lines inserted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_STATEMENT);
SELECT h.ae_header_id
,h.entity_id
,h.event_id
,h.doc_sequence_id
,h.doc_category_code
,CASE WHEN h.doc_category_code IS NOT NULL AND cat.code IS NULL
THEN 'N'
ELSE 'Y'
END doc_category_code_valid_flag
,CASE WHEN h.doc_sequence_id IS NOT NULL AND doc.doc_sequence_id IS NULL
THEN 'N'
ELSE 'Y'
END doc_sequence_id_valid_flag
FROM xla_ae_headers h
LEFT OUTER JOIN fnd_doc_sequence_categories cat
ON cat.code = h.doc_category_code
LEFT OUTER JOIN fnd_document_sequences doc
ON doc.doc_sequence_id = h.doc_sequence_id
WHERE h.ae_header_id = g_ae_header_id
AND h.application_id = g_application_id
AND ((h.doc_category_code IS NOT NULL AND cat.code IS NULL) OR
(h.doc_sequence_id IS NOT NULL AND doc.doc_sequence_id IS NULL));
SELECT h.ae_header_id
,h.entity_id
,h.event_id
,h.doc_sequence_id
,h.doc_category_code
,CASE WHEN h.doc_category_code IS NOT NULL AND cat.code IS NULL
THEN 'N'
ELSE 'Y'
END doc_category_code_valid_flag
,CASE WHEN h.doc_sequence_id IS NOT NULL AND doc.doc_sequence_id IS NULL
THEN 'N'
ELSE 'Y'
END doc_sequence_id_valid_flag
FROM xla_ae_headers_gt h
LEFT OUTER JOIN fnd_doc_sequence_categories cat
ON cat.code = h.doc_category_code
LEFT OUTER JOIN fnd_document_sequences doc
ON doc.doc_sequence_id = h.doc_sequence_id
WHERE h.ledger_id = g_ledger_id
AND h.accounting_date <= NVL(g_end_date, h.accounting_date) -- 4262811
AND ((h.doc_category_code IS NOT NULL AND cat.code IS NULL) OR
(h.doc_sequence_id IS NOT NULL AND doc.doc_sequence_id IS NULL));
SELECT application_name INTO l_app_name
FROM fnd_application_vl
WHERE application_id = g_application_id;
SELECT application_name INTO l_app_name
FROM fnd_application_vl
WHERE application_id = g_application_id;
SELECT h.ae_header_id
,h.ae_line_num -- 5522973
,h.entity_id
,h.event_id
,h.encumbrance_type_id
,e.encumbrance_type -- 5522973
,e.enabled_flag encum_type_enabled_flag
FROM xla_validation_lines_gt h
LEFT OUTER JOIN gl_encumbrance_types e
ON e.encumbrance_type_id = h.encumbrance_type_id
WHERE h.ledger_id = g_ledger_id
AND h.balance_type_code = 'E'
-- AND h.encumbrance_type_id IS NOT NULL -- 5522973 removed
AND nvl(e.enabled_flag,'N') = 'N';
SELECT h.ae_header_id
,h.entity_id
,h.event_id
,h.budget_version_id
,h.accounting_date
,bv.budget_name -- 5592776
,bv.status budget_version_status
,CASE WHEN h.balance_type_code = 'B' AND
gp.period_year > b.latest_opened_year
THEN 'N'
ELSE 'Y' END budget_period_valid_flag
FROM xla_ae_headers h
JOIN gl_period_statuses gp
ON gp.period_name = h.period_name
AND gp.ledger_id = g_ledger_id
AND gp.application_id = C_GL_APPLICATION_ID
LEFT OUTER JOIN gl_budget_versions bv
ON bv.budget_version_id = h.budget_version_id
LEFT OUTER JOIN gl_budgets b
ON b.budget_name = bv.budget_name
AND b.budget_type = bv.budget_type
WHERE h.ae_header_id = g_ae_header_id
AND h.application_id = g_application_id
AND h.balance_type_code = 'B'
AND h.budget_version_id IS NOT NULL
AND (bv.status IS NULL OR
nvl(bv.status,'I') in ('I', 'F') OR
gp.period_year > b.latest_opened_year);
SELECT h.ae_header_id
,h.entity_id
,h.event_id
,h.budget_version_id
,h.accounting_date
,bv.budget_name -- 5592776
,decode(nvl(b.ledger_id,h.ledger_id), h.ledger_id, bv.status , 'X') budget_version_status -- 5592776
,CASE WHEN h.balance_type_code = 'B' AND
h.period_year > b.latest_opened_year
THEN 'N'
ELSE 'Y' END budget_period_valid_flag
FROM xla_ae_headers_gt h
LEFT OUTER JOIN gl_budget_versions bv
ON bv.budget_version_id = h.budget_version_id
LEFT OUTER JOIN gl_budgets b
ON b.budget_name = bv.budget_name
AND b.budget_type = bv.budget_type
WHERE h.ledger_id = g_ledger_id
AND h.balance_type_code = 'B'
AND h.budget_version_id IS NOT NULL
AND (bv.status IS NULL OR
nvl(bv.status,'I') in ('I', 'F') OR
b.ledger_id <> h.ledger_id OR -- 5592776
h.period_year > b.latest_opened_year);
SELECT budget_name
INTO l_budget_name
FROM gl_budget_versions
WHERE budget_version_id = l_err.budget_version_id;
SELECT effective_date_rule_code
FROM gl_je_sources gjs
, xla_subledgers xs
WHERE gjs.je_source_name = xs.je_source_name
AND xs.application_id = g_application_id;
SELECT xah.ae_header_id
,xah.event_id
,xah.entity_id
,xah.accounting_date
FROM xla_ae_headers xah
, gl_transaction_dates gtd
WHERE xah.accounting_date = gtd.transaction_date
AND gtd.transaction_calendar_id = g_transaction_calendar_id
AND gtd.business_day_flag = 'N';
SELECT h.ae_header_id
,h.entity_id
,h.event_id
,h.accounting_date
,h.reference_date
,h.balance_type_code
,h.budget_version_id
,CASE WHEN h.balance_type_code = 'E' AND
gp.period_year > g.latest_encumbrance_year
THEN 'N'
ELSE 'Y' END encum_period_valid_flag
,CASE WHEN h.balance_type_code = 'A' AND
gp.closing_status not in ('O', 'F')
THEN 'N'
ELSE 'Y' END gl_date_valid_flag
,CASE WHEN h.reference_date IS NULL THEN 'Y'
WHEN nvl(rp.closing_status,'C') in ('O', 'F') THEN 'Y'
ELSE 'N' END reference_date_valid_flag
,NULL header_num -- 4262811
,NULL period_closing_status -- 4262811
,h.period_name period_name -- 5136994
,h.gl_transfer_status_code
FROM xla_ae_headers h
JOIN gl_ledgers g
ON g.ledger_id = h.ledger_id
JOIN gl_period_statuses gp
ON gp.period_name = h.period_name
AND gp.ledger_id = h.ledger_id
AND gp.application_id = C_GL_APPLICATION_ID
LEFT OUTER JOIN gl_period_statuses rp
ON rp.adjustment_period_flag = 'N'
AND h.reference_date BETWEEN rp.start_date AND rp.end_date
AND rp.ledger_id = h.ledger_id
AND rp.application_id = C_GL_APPLICATION_ID
WHERE h.ae_header_id = g_ae_header_id
AND h.application_id = g_application_id
AND ((h.balance_type_code = 'B' AND h.budget_version_id IS NULL) OR
(h.balance_type_code <> 'B' AND h.budget_version_id IS NOT NULL) OR
(h.balance_type_code NOT IN ('A', 'B', 'E')) OR
(h.balance_type_code = 'E' AND gp.period_year > g.latest_encumbrance_year) OR
(h.balance_type_code = 'A' AND gp.closing_status NOT IN ('O', 'F')) OR
(h.reference_date IS NOT NULL AND nvl(rp.closing_status,'C') NOT IN ('O', 'F')));
SELECT /*+ index(gp, GL_PERIOD_STATUSES_U3) */
h.ae_header_id
,h.entity_id
,h.event_id
,h.accounting_date
,NULL reference_date
,h.balance_type_code
,h.budget_version_id
,CASE WHEN h.balance_type_code = 'E' AND
--h.period_year > g_latest_encumbrance_year -- 5136994
NVL(h.period_year,g_latest_encumbrance_year+1) > g_latest_encumbrance_year -- 5136994
THEN 'N'
ELSE 'Y' END encum_period_valid_flag
,CASE WHEN h.balance_type_code = 'A' AND
--h.period_closing_status not in ('O', 'F')
NVL(h.period_closing_status,'X') not in ('O', 'F') -- 5136994
THEN 'N'
ELSE 'Y' END gl_date_valid_flag
,'Y' reference_date_valid_flag
,NVL(h.header_num,0) header_num -- 4262811
,h.period_closing_status period_closing_status -- 4262811
,h.period_name period_name -- 5136994
,h.gl_transfer_status_code
FROM xla_ae_headers_gt h
WHERE h.ledger_id = g_ledger_id
AND (h.accounting_date <= g_end_date OR h.period_closing_status IN ('P','C') -- 4262811
OR h.period_name IS NULL) -- 5136994
AND ((h.balance_type_code = 'B' AND h.budget_version_id IS NULL) OR
(h.balance_type_code <> 'B' AND h.budget_version_id IS NOT NULL) OR
(h.balance_type_code NOT IN ('A', 'B', 'E')) OR
-- 5136994
(h.balance_type_code = 'E' AND NVL(h.period_year,g_latest_encumbrance_year+1) > g_latest_encumbrance_year) OR
(h.balance_type_code = 'A' AND NVL(h.period_closing_status,'X') NOT IN ('O', 'F')));
SELECT t.*
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_ledger_segment_values s
ON s.segment_value = t.bal_seg_value
AND s.segment_type_code = C_BAL_SEGMENT
AND s.ledger_id = p_seg_ledger_id
AND t.accounting_date BETWEEN NVL(s.start_date, t.accounting_date)
AND NVL(s.end_date, t.accounting_date)
WHERE t.ccid_enabled_flag IS NOT NULL
AND s.ledger_id IS NULL
AND t.code_combination_id <> -1;
SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', l_err.ccid_coa_id, l_err.code_combination_id)
INTO l_account
FROM dual;
SELECT t.*
FROM xla_validation_lines_gt t
LEFT OUTER JOIN gl_ledger_segment_values s
ON s.segment_value = t.mgt_seg_value
AND s.segment_type_code = C_MGT_SEGMENT
AND s.ledger_id = p_seg_ledger_id
AND t.accounting_date BETWEEN NVL(s.start_date, t.accounting_date)
AND NVL(s.end_date, t.accounting_date)
WHERE t.ccid_enabled_flag IS NOT NULL
AND s.ledger_id IS NULL;
SELECT id_flex_structure_name
FROM fnd_id_flex_structures_vl
WHERE application_id = 101
AND id_flex_num = p_coa_id;
SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', l_err.ccid_coa_id, l_err.code_combination_id)
INTO l_account
FROM dual;
SELECT t.ae_header_id, t.ae_line_num, t.event_id, t.displayed_line_number,
t.entity_id,
t.party_type_code, t.party_id, t.party_site_id,
c.cust_account_id customer_id, ps.site_use_id customer_site_id,
s.vendor_id, ss.vendor_site_id
FROM xla_validation_lines_gt t
LEFT OUTER JOIN hz_cust_accounts_all c
ON c.cust_account_id = t.party_id
LEFT OUTER JOIN hz_cust_site_uses_all ps
ON ps.site_use_id = t.party_site_id
LEFT OUTER JOIN ap_supplier_sites_all ss
ON ss.vendor_site_id = t.party_site_id
LEFT OUTER JOIN ap_suppliers s
ON s.vendor_id = t.party_id
WHERE (t.party_type_code IS NULL
AND ((c.cust_account_id IS NOT NULL )OR (t.party_site_id IS NOT NULL AND ps.site_use_id IS NULL ))
)
OR (t.party_type_code = 'S'
AND ((s.vendor_id IS NULL) OR
(t.party_site_id IS NOT NULL AND ss.vendor_site_id IS NULL))) ;
SELECT t.ae_header_id
,t.ae_line_num
,t.event_id
,t.displayed_line_number
,t.entity_id
,t.accounting_date
,t.entered_currency_code
,curr.enabled_flag curr_enabled_flag
,curr.start_date_active curr_start_date_active
,curr.end_date_active curr_end_date_active
FROM xla_validation_lines_gt t
LEFT OUTER JOIN fnd_currencies curr
ON curr.currency_code = t.entered_currency_code
WHERE (curr.enabled_flag IS NULL) OR
(curr.enabled_flag = 'N') OR
(t.accounting_date < nvl(curr.start_date_active,t.accounting_date)) OR
(t.accounting_date > nvl(curr.end_date_active,t.accounting_date));
SELECT t.ae_header_id
,t.ae_line_num
,t.event_id
,t.displayed_line_number
,t.entity_id
,bud.budget_name
,fnd_flex_ext.get_segs('SQLGL', 'GL#', t.ccid_coa_id, t.code_combination_id) account
FROM xla_validation_lines_gt t
JOIN gl_budget_versions bud
ON bud.budget_version_id = t.budget_version_id
LEFT OUTER JOIN gl_budget_assignments b
ON b.currency_code = t.entered_currency_code
AND b.code_combination_id = t.code_combination_id
AND b.ledger_id = g_ledger_id
LEFT OUTER JOIN gl_budorg_bc_options bc
ON bc.range_id = b.range_id
AND t.budget_version_id = bc.funding_budget_version_id
WHERE t.balance_type_code = 'B'
AND t.budget_version_id IS NOT NULL
AND bc.funding_budget_version_id IS NULL;
SELECT t.ae_header_id
,t.ae_line_num
,t.event_id
,t.displayed_line_number
,t.entity_id
,t.accounting_class_code
FROM xla_validation_lines_gt t
LEFT OUTER JOIN xla_lookups lk
ON lk.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND lk.lookup_code = t.accounting_class_code
WHERE lk.lookup_code IS NULL
AND t.accounting_class_code IS NOT NULL;
SELECT *
FROM xla_validation_lines_gt
WHERE error_flag = 'Y';
SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', p_coa_id, p_code_combination_id)
FROM dual;
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'GL_CONTROL_ACCOUNT_SOURCES'
AND lookup_code = l_lookup_code;
SELECT name
FROM xla_product_rules_vl
WHERE product_rule_type_code = p_prod_rule_type_code
AND product_rule_code = p_prod_rule_code
AND application_id = g_application_id
AND amb_context_code = g_amb_context_code;
SELECT budget_name
FROM gl_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT gdct.user_conversion_type
FROM gl_daily_conversion_types gdct
WHERE gdct.conversion_type = p_conv_type;
SELECT application_name INTO l_app_name
FROM fnd_application_vl
WHERE application_id = g_application_id;
SELECT application_name INTO l_app_name
FROM fnd_application_vl
WHERE application_id = g_application_id;
SELECT gain_or_loss_flag INTO l_gain_or_loss_flag
FROM xla_ae_lines
WHERE application_id = g_application_id
AND ae_header_id = l_err.ae_header_id
AND ae_line_num = l_err.ae_line_num;
The following sql updates # of errors * # of lines.
FORALL i IN l_prev_err_count+1..g_err_count
UPDATE xla_validation_lines_gt
set balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = g_err_hdr_ids(i); */
UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
*/ XLA_VALIDATION_LINES_GT
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_distinct_hdr_ids(i);
SELECT bal_seg_value bal_seg_val
, entered_currency_code currency_code
, max_ae_line_num max_ae_line_num
, max_displayed_line_number max_disp_line_num
, sum(nvl(accounted_dr,0)) accted_dr
, sum(nvl(accounted_cr,0)) accted_cr
, sum(nvl(entered_dr,0)) entered_dr
, sum(nvl(entered_cr,0)) entered_cr
, accounting_date accounting_date
, party_type_code party_type_code
, party_id party_id
, party_site_id party_site_id
FROM xla_validation_lines_gt
WHERE ae_header_id = p_ae_header_id
GROUP BY bal_seg_value
, entered_currency_code
, max_ae_line_num
, max_displayed_line_number
, accounting_date
, party_type_code
, party_id
, party_site_id
HAVING sum(nvl(accounted_dr,0)) <> sum(nvl(accounted_cr,0));
SELECT chart_of_accounts_id
, decode(p_bal_seg_column,'SEGMENT1',p_bal_seg_val,t.segment1)
, decode(p_bal_seg_column,'SEGMENT2',p_bal_seg_val,t.segment2)
, decode(p_bal_seg_column,'SEGMENT3',p_bal_seg_val,t.segment3)
, decode(p_bal_seg_column,'SEGMENT4',p_bal_seg_val,t.segment4)
, decode(p_bal_seg_column,'SEGMENT5',p_bal_seg_val,t.segment5)
, decode(p_bal_seg_column,'SEGMENT6',p_bal_seg_val,t.segment6)
, decode(p_bal_seg_column,'SEGMENT7',p_bal_seg_val,t.segment7)
, decode(p_bal_seg_column,'SEGMENT8',p_bal_seg_val,t.segment8)
, decode(p_bal_seg_column,'SEGMENT9',p_bal_seg_val,t.segment9)
, decode(p_bal_seg_column,'SEGMENT10',p_bal_seg_val,t.segment10)
, decode(p_bal_seg_column,'SEGMENT11',p_bal_seg_val,t.segment11)
, decode(p_bal_seg_column,'SEGMENT12',p_bal_seg_val,t.segment12)
, decode(p_bal_seg_column,'SEGMENT13',p_bal_seg_val,t.segment13)
, decode(p_bal_seg_column,'SEGMENT14',p_bal_seg_val,t.segment14)
, decode(p_bal_seg_column,'SEGMENT15',p_bal_seg_val,t.segment15)
, decode(p_bal_seg_column,'SEGMENT16',p_bal_seg_val,t.segment16)
, decode(p_bal_seg_column,'SEGMENT17',p_bal_seg_val,t.segment17)
, decode(p_bal_seg_column,'SEGMENT18',p_bal_seg_val,t.segment18)
, decode(p_bal_seg_column,'SEGMENT19',p_bal_seg_val,t.segment19)
, decode(p_bal_seg_column,'SEGMENT20',p_bal_seg_val,t.segment20)
, decode(p_bal_seg_column,'SEGMENT21',p_bal_seg_val,t.segment21)
, decode(p_bal_seg_column,'SEGMENT22',p_bal_seg_val,t.segment22)
, decode(p_bal_seg_column,'SEGMENT23',p_bal_seg_val,t.segment23)
, decode(p_bal_seg_column,'SEGMENT24',p_bal_seg_val,t.segment24)
, decode(p_bal_seg_column,'SEGMENT25',p_bal_seg_val,t.segment25)
, decode(p_bal_seg_column,'SEGMENT26',p_bal_seg_val,t.segment26)
, decode(p_bal_seg_column,'SEGMENT27',p_bal_seg_val,t.segment27)
, decode(p_bal_seg_column,'SEGMENT28',p_bal_seg_val,t.segment28)
, decode(p_bal_seg_column,'SEGMENT29',p_bal_seg_val,t.segment29)
, decode(p_bal_seg_column,'SEGMENT30',p_bal_seg_val,t.segment30)
FROM gl_code_combinations t
WHERE t.code_combination_id = p_sus_ccid;
SELECT display_order
FROM (SELECT ROWNUM display_order, application_column_name
FROM ( SELECT application_column_name
FROM FND_ID_FLEX_SEGMENTS_VL
WHERE ID_FLEX_NUM = p_coa_id
AND ID_FLEX_CODE = 'GL#'
AND APPLICATION_ID = 101
order by decode(enabled_flag, 'Y', 1, 'N', 2), segment_num))
WHERE application_column_name = p_seg_col_name;
l_stmt := 'SELECT '||g_mgt_seg_column_name||'
FROM gl_code_combinations
WHERE code_combination_id = '||g_sla_ledger_cur_bal_sus_ccid;
l_stmt := 'SELECT code_combination_id, reference3 FROM gl_code_combinations '||
'WHERE chart_of_accounts_id = :1 ';
SELECT reference3 INTO l_ref3
FROM gl_code_combinations
WHERE code_combination_id = l_sus_ccid;
INSERT INTO xla_validation_lines_gt
(balancing_line_type
,ledger_id
,ae_header_id
,ae_line_num
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,event_id
,entity_id
,accounting_date
,entered_currency_code
,entered_cr
,entered_dr
,accounted_cr
,accounted_dr
,code_combination_id
,mgt_seg_value
,bal_seg_value
,control_account_enabled_flag
,party_type_code
,party_id
,party_site_id)
VALUES
(C_LINE_TYPE_LC_BALANCING
,g_ledger_id
,p_ae_header_id
,l_bal.max_ae_line_num
,l_bal.max_disp_line_num
,l_bal.max_ae_line_num
,l_bal.max_disp_line_num
,p_event_id
,p_entity_id
,l_bal.accounting_date
,l_bal.currency_code
,l_bal.entered_cr
,l_bal.entered_dr
,l_bal.accted_cr
,l_bal.accted_dr
,l_sus_ccid
,l_mgt_seg_val
,l_bal.bal_seg_val
,l_ref3
,l_bal.party_type_code
,l_bal.party_id
,l_bal.party_site_id );
SELECT ae_header_id
,entity_id
,event_id
FROM xla_validation_lines_gt
WHERE balance_type_code = 'A' -- <> 'B' -- 4458381
AND entered_currency_code <> 'STAT'
AND balancing_line_type = C_LINE_TYPE_PROCESS
GROUP BY ae_header_id, entity_id , event_id
HAVING ROUND(nvl(sum(unrounded_entered_dr/entered_currency_mau), 0)+p_rounding_offset) <>
ROUND(nvl(sum(unrounded_entered_cr/entered_currency_mau), 0)+p_rounding_offset)
AND count(distinct entered_currency_code) = 1;
SELECT xlo.rounding_rule_code
INTO l_rounding_rule_code
FROM xla_ledger_options xlo
WHERE xlo.application_id = g_application_id
AND xlo.ledger_id = g_trx_ledger_id;
UPDATE xla_validation_lines_gt
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_comp_hdr_ids(j)
AND balancing_line_type = C_LINE_TYPE_PROCESS;
SELECT ae_header_id
,entity_id
,event_id
FROM xla_validation_lines_gt
WHERE balance_type_code = 'A'
AND entered_currency_code <> 'STAT'
AND balancing_line_type = C_LINE_TYPE_PROCESS
GROUP BY ae_header_id, entity_id, event_id
HAVING sum(nvl(accounted_dr,0)) <> sum(nvl(accounted_cr,0))
AND ROUND( NVL(SUM(unrounded_accounted_dr),0) /p_mau+p_rounding_offset)
<> ROUND( NVL(SUM(unrounded_accounted_cr),0) /p_mau+p_rounding_offset);
SELECT pr.name, lk.meaning, ec.name, et.name
FROM xla_product_rules_tl pr
,xla_event_classes_tl ec
,xla_event_types_tl et
,xla_lookups lk
,xla_ae_headers h
WHERE lk.lookup_code = h.product_rule_type_code
AND lk.lookup_type = 'XLA_OWNER_TYPE'
AND pr.amb_context_code = g_amb_context_code
AND pr.application_id = g_application_id
AND pr.product_rule_type_code = h.product_rule_type_code
AND pr.product_rule_code = h.product_rule_code
AND pr.language = USERENV('LANG')
AND ec.application_id = et.application_id
AND ec.event_class_code = et.event_class_code
AND ec.language = USERENV('LANG')
AND et.application_id = h.application_id
AND et.event_type_code = h.event_type_code
AND et.language = USERENV('LANG')
AND h.ae_header_id = p_ae_header_id
AND h.application_id = g_application_id;
SELECT nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,xlo.rounding_rule_code
INTO l_mau, l_rounding_rule_code
FROM xla_ledger_options xlo
,gl_ledgers gl
,fnd_currencies fcu
WHERE xlo.application_id = g_application_id
AND xlo.ledger_id = g_trx_ledger_id
AND gl.ledger_id = g_ledger_id
AND fcu.currency_code = gl.currency_code;
UPDATE xla_validation_lines_gt
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_comp_hdr_ids(j)
AND balancing_line_type = C_LINE_TYPE_PROCESS;
SELECT bal_seg_value bal_seg_val
,entered_currency_code entered_currency_code
,nvl(sum(entered_dr), 0) entered_dr
,nvl(sum(entered_cr), 0) entered_cr
,nvl(sum(accounted_dr), 0) accted_dr
,nvl(sum(accounted_cr), 0) accted_cr
,ae_header_id ae_header_id
,max_ae_line_num max_ae_line_num
,max_displayed_line_number max_disp_line_num
,accounting_date accounting_date
,entity_id
,event_id
FROM XLA_VALIDATION_LINES_GT
WHERE balancing_line_type in (C_LINE_TYPE_PROCESS
,C_LINE_TYPE_IC_BAL_INTER
,C_LINE_TYPE_IC_BAL_INTRA
,C_LINE_TYPE_XLA_BALANCING
,C_LINE_TYPE_ENC_BALANCING) -- 4458381
AND balance_type_code <> 'B'
AND entered_currency_code <> 'STAT'
GROUP BY bal_seg_value
,entered_currency_code
,max_ae_line_num
,max_displayed_line_number
,ae_header_id
,entity_id
,event_id
,accounting_date
HAVING nvl(sum(accounted_dr), 0) <> nvl(sum(accounted_cr), 0)
or nvl(sum(entered_dr), 0) <> nvl(sum(entered_cr), 0);
SELECT chart_of_accounts_id
, decode(p_bal_seg_column,'SEGMENT1',p_bal_seg_val,t.segment1)
, decode(p_bal_seg_column,'SEGMENT2',p_bal_seg_val,t.segment2)
, decode(p_bal_seg_column,'SEGMENT3',p_bal_seg_val,t.segment3)
, decode(p_bal_seg_column,'SEGMENT4',p_bal_seg_val,t.segment4)
, decode(p_bal_seg_column,'SEGMENT5',p_bal_seg_val,t.segment5)
, decode(p_bal_seg_column,'SEGMENT6',p_bal_seg_val,t.segment6)
, decode(p_bal_seg_column,'SEGMENT7',p_bal_seg_val,t.segment7)
, decode(p_bal_seg_column,'SEGMENT8',p_bal_seg_val,t.segment8)
, decode(p_bal_seg_column,'SEGMENT9',p_bal_seg_val,t.segment9)
, decode(p_bal_seg_column,'SEGMENT10',p_bal_seg_val,t.segment10)
, decode(p_bal_seg_column,'SEGMENT11',p_bal_seg_val,t.segment11)
, decode(p_bal_seg_column,'SEGMENT12',p_bal_seg_val,t.segment12)
, decode(p_bal_seg_column,'SEGMENT13',p_bal_seg_val,t.segment13)
, decode(p_bal_seg_column,'SEGMENT14',p_bal_seg_val,t.segment14)
, decode(p_bal_seg_column,'SEGMENT15',p_bal_seg_val,t.segment15)
, decode(p_bal_seg_column,'SEGMENT16',p_bal_seg_val,t.segment16)
, decode(p_bal_seg_column,'SEGMENT17',p_bal_seg_val,t.segment17)
, decode(p_bal_seg_column,'SEGMENT18',p_bal_seg_val,t.segment18)
, decode(p_bal_seg_column,'SEGMENT19',p_bal_seg_val,t.segment19)
, decode(p_bal_seg_column,'SEGMENT20',p_bal_seg_val,t.segment20)
, decode(p_bal_seg_column,'SEGMENT21',p_bal_seg_val,t.segment21)
, decode(p_bal_seg_column,'SEGMENT22',p_bal_seg_val,t.segment22)
, decode(p_bal_seg_column,'SEGMENT23',p_bal_seg_val,t.segment23)
, decode(p_bal_seg_column,'SEGMENT24',p_bal_seg_val,t.segment24)
, decode(p_bal_seg_column,'SEGMENT25',p_bal_seg_val,t.segment25)
, decode(p_bal_seg_column,'SEGMENT26',p_bal_seg_val,t.segment26)
, decode(p_bal_seg_column,'SEGMENT27',p_bal_seg_val,t.segment27)
, decode(p_bal_seg_column,'SEGMENT28',p_bal_seg_val,t.segment28)
, decode(p_bal_seg_column,'SEGMENT29',p_bal_seg_val,t.segment29)
, decode(p_bal_seg_column,'SEGMENT30',p_bal_seg_val,t.segment30)
FROM gl_code_combinations t
WHERE t.code_combination_id = p_rounding_ccid;
SELECT display_order
FROM (SELECT ROWNUM display_order, application_column_name
FROM ( SELECT application_column_name
FROM FND_ID_FLEX_SEGMENTS_VL
WHERE ID_FLEX_NUM = p_coa_id
AND ID_FLEX_CODE = 'GL#'
AND APPLICATION_ID = 101
order by decode(enabled_flag, 'Y', 1, 'N', 2), segment_num))
WHERE application_column_name = p_seg_col_name;
l_stmt := 'SELECT '||g_mgt_seg_column_name||'
FROM gl_code_combinations
WHERE code_combination_id = '||g_sla_rounding_ccid;
l_stmt := 'SELECT code_combination_id, reference3 FROM gl_code_combinations '||
'WHERE chart_of_accounts_id = :1 ';
SELECT reference3
INTO l_ref3
FROM gl_code_combinations
WHERE code_combination_id = l_rounding_ccid;
INSERT INTO xla_validation_lines_gt
(balancing_line_type
,ledger_id
,ae_header_id
,ae_line_num
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,event_id
,entity_id
,accounting_date
,entered_currency_code
,entered_cr
,entered_dr
,accounted_cr
,accounted_dr
,code_combination_id
,control_account_enabled_flag
,mgt_seg_value
,bal_seg_value)
VALUES
(C_LINE_TYPE_RD_BALANCING
,g_ledger_id
,l_bal.ae_header_id
,l_bal.max_ae_line_num
,l_bal.max_disp_line_num
,l_bal.max_ae_line_num
,l_bal.max_disp_line_num
,l_bal.event_id
,l_bal.entity_id
,l_bal.accounting_date
,l_bal.entered_currency_code
,l_bal.entered_cr
,l_bal.entered_dr
,l_bal.accted_cr
,l_bal.accted_dr
,l_rounding_ccid
,l_ref3
,l_mgt_seg_val
,l_bal.bal_seg_val);
SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', g_ledger_coa_id, l_rounding_ccid)
INTO l_account
FROM dual;
SELECT distinct
err.error_code
, le2.name from_le_name
, le3.name to_le_name
, le1.name le_name
, err.ccid
, je.user_je_category_name je_category_name
, hdr.entity_id
, hdr.event_id
, hdr.ae_header_id
FROM fun_bal_errors_gt err
, xla_ae_headers_gt hdr
, gl_je_categories je
, xle_entity_profiles le1
, xle_entity_profiles le2
, xle_entity_profiles le3
WHERE err.group_id = hdr.ae_header_id
AND je.je_category_name(+) = hdr.je_category_name
AND le1.legal_entity_id(+) = err.le_id
AND le2.legal_entity_id(+) = err.from_le_id
AND le3.legal_entity_id(+) = err.to_le_id;
SELECT distinct
err.error_code
, le2.name from_le_name
, le3.name to_le_name
, le1.name le_name
, err.ccid
, je.user_je_category_name je_category_name
, hdr.entity_id
, hdr.event_id
, hdr.ae_header_id
FROM fun_bal_errors_gt err
, xla_ae_headers hdr
, gl_je_categories je
, xle_entity_profiles le1
, xle_entity_profiles le2
, xle_entity_profiles le3
WHERE err.group_id = hdr.ae_header_id
AND je.je_category_name(+) = hdr.je_category_name
AND le1.legal_entity_id(+) = err.le_id
AND le2.legal_entity_id(+) = err.from_le_id
AND le3.legal_entity_id(+) = err.to_le_id
AND hdr.application_id = g_application_id
AND hdr.ae_header_id = g_ae_header_id;
SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', g_ledger_coa_id, l_err.ccid)
INTO l_account
FROM dual;
SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', g_ledger_coa_id, l_err.ccid)
INTO l_account
FROM dual;
) IS SELECT ae_header_id
,je_category_name
,accounting_date
,event_id
,entity_id
FROM xla_validation_lines_gt l
WHERE balance_type_code = 'A'
AND entered_currency_code <> 'STAT'
AND balancing_line_type = C_LINE_TYPE_PROCESS
GROUP BY ae_header_id
,je_category_name
,accounting_date
,event_id
,entity_id
,bal_seg_value
--HAVING sum(nvl(accounted_dr,0)) <> sum(nvl(accounted_cr,0))
HAVING ROUND( NVL(SUM(unrounded_accounted_dr),0) /p_mau+p_rounding_offset)
<> ROUND( NVL(SUM(unrounded_accounted_cr),0) /p_mau+p_rounding_offset)
ORDER BY ae_header_id;
SELECT nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,xlo.rounding_rule_code
INTO l_mau, l_rounding_rule_code
FROM xla_ledger_options xlo
,gl_ledgers gl
,fnd_currencies fcu
WHERE xlo.application_id = g_application_id
AND xlo.ledger_id = g_trx_ledger_id
AND gl.ledger_id = g_ledger_id
AND fcu.currency_code = gl.currency_code;
INSERT INTO fun_bal_headers_gt (
group_id
,ledger_id
,je_source_name
,je_category_name
,gl_date
,status)
VALUES (
l_ae_header_ids(j)
,g_target_ledger_id
,g_app_je_source_name
,l_je_category_names(j)
,l_accounting_dates(j)
,'OK');
trace(p_msg => '# rows inserted into fun_bal_headers_gt: '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO fun_bal_lines_gt (
group_id
,bal_seg_val
,entered_amt_dr
,entered_amt_cr
,entered_currency_code
,exchange_date
,exchange_rate
,exchange_rate_type
,accounted_amt_dr
,accounted_amt_cr
,generated)
SELECT l.ae_header_id
,l.bal_seg_value
,l.unrounded_entered_dr
,l.unrounded_entered_cr
,l.entered_currency_code
,l.currency_conversion_date
,l.currency_conversion_rate
,l.currency_conversion_type
,l.unrounded_accounted_dr
,l.unrounded_accounted_cr
,'N'
FROM xla_validation_lines_gt l
,fun_bal_headers_gt h
WHERE l.balancing_line_type = C_LINE_TYPE_PROCESS
AND l.ae_header_id = h.group_id
AND EXISTS (select 1 from xla_distribution_links xdl
where xdl.ae_header_id= l.ae_header_id
and xdl.ae_line_num= l.ae_line_num
and (xdl.temp_line_num IS NULL or xdl.temp_line_num >0));
trace(p_msg => '# non-reversal rows inserted into fun_bal_lines_gt: '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO fun_bal_lines_gt (
group_id
,bal_seg_val
,entered_amt_dr
,entered_amt_cr
,entered_currency_code
,exchange_date
,exchange_rate
,exchange_rate_type
,accounted_amt_dr
,accounted_amt_cr
,generated)
SELECT l.ae_header_id
,l.bal_seg_value
,l.unrounded_entered_cr
,l.unrounded_entered_dr
,l.entered_currency_code
,l.currency_conversion_date
,l.currency_conversion_rate
,l.currency_conversion_type
,l.unrounded_accounted_cr
,l.unrounded_accounted_dr
,'N'
FROM xla_validation_lines_gt l
,fun_bal_headers_gt h
WHERE l.balancing_line_type = C_LINE_TYPE_PROCESS
AND l.ae_header_id = h.group_id
AND EXISTS (select 1 from xla_distribution_links xdl
where xdl.ae_header_id= l.ae_header_id
and xdl.ae_line_num= l.ae_line_num
and xdl.temp_line_num <0)
;
trace(p_msg => '# reversal rows inserted into fun_bal_lines_gt: '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_validation_lines_gt(
ae_header_id
,ae_line_num
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,ledger_id
,event_id
,entity_id
,accounting_date
,entered_currency_mau
,code_combination_id
,entered_currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,entered_cr
,entered_dr
,accounted_cr
,accounted_dr
,unrounded_entered_cr
,unrounded_entered_dr
,unrounded_accounted_cr
,unrounded_accounted_dr
,bal_seg_value
,mgt_seg_value
,balancing_line_type
,balance_type_code)
SELECT res.group_id
,l.max_ae_line_num
,l.max_displayed_line_number
,l.max_ae_line_num
,l.max_displayed_line_number
,g_ledger_id
,l.event_id
,l.entity_id
,l.accounting_date
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,res.ccid
,res.entered_currency_code
,res.exchange_date
,res.exchange_rate
,res.exchange_rate_type
,ROUND(res.entered_amt_cr /(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))+l_rounding_offset)*(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
,ROUND(res.entered_amt_dr /(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))+l_rounding_offset)*(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
,ROUND(res.accounted_amt_cr /l_mau+l_rounding_offset)*l_mau
,ROUND(res.accounted_amt_dr /l_mau+l_rounding_offset)*l_mau
,res.entered_amt_cr
,res.entered_amt_dr
,res.accounted_amt_cr
,res.accounted_amt_dr
,res.bal_seg_val
,decode(g_mgt_seg_column_name
,'SEGMENT1', ccid.segment1, 'SEGMENT2', ccid.segment2
,'SEGMENT3', ccid.segment3, 'SEGMENT4', ccid.segment4
,'SEGMENT5', ccid.segment5, 'SEGMENT6', ccid.segment6
,'SEGMENT7', ccid.segment7, 'SEGMENT8', ccid.segment8
,'SEGMENT9', ccid.segment9, 'SEGMENT10', ccid.segment10
,'SEGMENT11', ccid.segment11, 'SEGMENT12', ccid.segment12
,'SEGMENT13', ccid.segment13, 'SEGMENT14', ccid.segment14
,'SEGMENT15', ccid.segment15, 'SEGMENT16', ccid.segment16
,'SEGMENT17', ccid.segment17, 'SEGMENT18', ccid.segment18
,'SEGMENT19', ccid.segment19, 'SEGMENT20', ccid.segment20
,'SEGMENT21', ccid.segment21, 'SEGMENT22', ccid.segment22
,'SEGMENT23', ccid.segment23, 'SEGMENT24', ccid.segment24
,'SEGMENT25', ccid.segment25, 'SEGMENT26', ccid.segment26
,'SEGMENT27', ccid.segment27, 'SEGMENT28', ccid.segment28
,'SEGMENT29', ccid.segment29, 'SEGMENT30', ccid.segment30, NULL)
,decode(res.balancing_type, C_FUN_INTRA, C_LINE_TYPE_IC_BAL_INTRA,
C_LINE_TYPE_IC_BAL_INTER)
,l.balance_type_code
FROM fun_bal_results_gt res
,xla_validation_lines_gt l
,gl_code_combinations ccid
,fnd_currencies fcu
WHERE l.ae_line_num = l.max_ae_line_num
AND l.ae_header_id = res.group_id
AND ccid.code_combination_id= res.ccid
AND res.entered_currency_code = fcu.currency_code
AND EXISTS (select 1 from xla_distribution_links xdl
where xdl.ae_header_id= l.ae_header_id
and xdl.ae_line_num= l.ae_line_num
and (xdl.temp_line_num IS NULL or xdl.temp_line_num >0));
INSERT INTO xla_validation_lines_gt(
ae_header_id
,ae_line_num
,displayed_line_number
,max_ae_line_num
,max_displayed_line_number
,ledger_id
,event_id
,entity_id
,accounting_date
,entered_currency_mau
,code_combination_id
,entered_currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,entered_cr
,entered_dr
,accounted_cr
,accounted_dr
,unrounded_entered_cr
,unrounded_entered_dr
,unrounded_accounted_cr
,unrounded_accounted_dr
,bal_seg_value
,mgt_seg_value
,balancing_line_type
,balance_type_code)
SELECT res.group_id
,l.max_ae_line_num
,l.max_displayed_line_number
,l.max_ae_line_num
,l.max_displayed_line_number
,g_ledger_id
,l.event_id
,l.entity_id
,l.accounting_date
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,res.ccid
,res.entered_currency_code
,res.exchange_date
,res.exchange_rate
,res.exchange_rate_type
,ROUND(res.entered_amt_dr /(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))+l_rounding_offset)*(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
,ROUND(res.entered_amt_cr /(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))+l_rounding_offset)*(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
--,ROUND(res.entered_amt_dr /(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))+l_rounding_offset)*(nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
,ROUND(res.accounted_amt_dr /l_mau+l_rounding_offset)*l_mau
,ROUND(res.accounted_amt_cr /l_mau+l_rounding_offset)*l_mau
--,ROUND(res.accounted_amt_dr /l_mau+l_rounding_offset)*l_mau
,res.entered_amt_dr
,res.entered_amt_cr
--,res.entered_amt_dr
,res.accounted_amt_dr
,res.accounted_amt_cr
--,res.accounted_amt_dr
,res.bal_seg_val
,decode(g_mgt_seg_column_name
,'SEGMENT1', ccid.segment1, 'SEGMENT2', ccid.segment2
,'SEGMENT3', ccid.segment3, 'SEGMENT4', ccid.segment4
,'SEGMENT5', ccid.segment5, 'SEGMENT6', ccid.segment6
,'SEGMENT7', ccid.segment7, 'SEGMENT8', ccid.segment8
,'SEGMENT9', ccid.segment9, 'SEGMENT10', ccid.segment10
,'SEGMENT11', ccid.segment11, 'SEGMENT12', ccid.segment12
,'SEGMENT13', ccid.segment13, 'SEGMENT14', ccid.segment14
,'SEGMENT15', ccid.segment15, 'SEGMENT16', ccid.segment16
,'SEGMENT17', ccid.segment17, 'SEGMENT18', ccid.segment18
,'SEGMENT19', ccid.segment19, 'SEGMENT20', ccid.segment20
,'SEGMENT21', ccid.segment21, 'SEGMENT22', ccid.segment22
,'SEGMENT23', ccid.segment23, 'SEGMENT24', ccid.segment24
,'SEGMENT25', ccid.segment25, 'SEGMENT26', ccid.segment26
,'SEGMENT27', ccid.segment27, 'SEGMENT28', ccid.segment28
,'SEGMENT29', ccid.segment29, 'SEGMENT30', ccid.segment30, NULL)
,decode(res.balancing_type, C_FUN_INTRA, C_LINE_TYPE_IC_BAL_INTRA,
C_LINE_TYPE_IC_BAL_INTER)
,l.balance_type_code
FROM fun_bal_results_gt res
,xla_validation_lines_gt l
,gl_code_combinations ccid
,fnd_currencies fcu
WHERE l.ae_line_num = l.max_ae_line_num
AND l.ae_header_id = res.group_id
AND ccid.code_combination_id= res.ccid
AND res.entered_currency_code = fcu.currency_code
AND EXISTS (select 1 from xla_distribution_links xdl
where xdl.ae_header_id= l.ae_header_id
and xdl.ae_line_num= l.ae_line_num
and xdl.temp_line_num <0);
UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
XLA_VALIDATION_LINES_GT
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_err_hdr_ids(j); */
UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
*/ XLA_VALIDATION_LINES_GT
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_distinct_hdr_ids(i);
trace(p_msg => '# rows updated with C_LINE_TYPE_COMPLETE: '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
SELECT t.bal_seg_value
,t.balance_type_code -- 4458381
,min(t.accounting_date) accounting_date
FROM xla_validation_lines_gt t
WHERE balancing_line_type IN (C_LINE_TYPE_XLA_BALANCING
,C_LINE_TYPE_ENC_BALANCING)
AND t.code_combination_id IS NULL
AND ((g_res_encumb_ccid IS NOT NULL AND t.balance_type_code = 'E') OR
(g_sla_entered_cur_bal_sus_ccid IS NOT NULL AND t.balance_type_code = 'A'))
GROUP BY t.bal_seg_value, t.balance_type_code;
SELECT entity_id
,event_id
,ae_header_id
FROM xla_validation_lines_gt
WHERE balancing_line_type IN (C_LINE_TYPE_XLA_BALANCING
,C_LINE_TYPE_ENC_BALANCING)
AND code_combination_id < 0
GROUP BY entity_id, event_id, ae_header_id;
SELECT display_order
FROM (SELECT ROWNUM display_order, application_column_name
FROM ( SELECT application_column_name
FROM FND_ID_FLEX_SEGMENTS_VL
WHERE ID_FLEX_NUM = p_coa_id
AND ID_FLEX_CODE = 'GL#'
AND APPLICATION_ID = 101
order by decode(enabled_flag, 'Y', 1, 'N', 2), segment_num))
WHERE application_column_name = p_seg_col_name;
SELECT reference3
FROM gl_code_combinations
WHERE code_combination_id = p_ccid;
UPDATE xla_validation_lines_gt t
SET code_combination_id = l_ccids(i)
,control_account_enabled_flag = l_reference3s(i)
,mgt_seg_value = l_mgt_seg_values(i)
WHERE t.bal_seg_value = l_bal_seg_values(i)
AND t.balance_type_code = l_bal_type_codes(i) -- 4458381
AND t.code_combination_id IS NULL;
trace(p_msg => '# rows updated for filled for missing ccid = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
SELECT entity_id, event_id, ae_header_id, balance_type_code
FROM xla_validation_lines_gt
WHERE balancing_line_type = C_LINE_TYPE_XLA_BALANCING
AND balance_type_code = 'A'
GROUP BY entity_id, event_id, ae_header_id, balance_type_code;
SELECT nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,xlo.rounding_rule_code
INTO l_mau, l_rounding_rule_code
FROM xla_ledger_options xlo
,gl_ledgers gl
,fnd_currencies fcu
WHERE xlo.application_id = g_application_id
AND xlo.ledger_id = g_trx_ledger_id
AND gl.ledger_id = g_ledger_id
AND fcu.currency_code = gl.currency_code;
SELECT ae_header_id, bal_seg_value, entered_currency_code, encumbrance_type_id
BULK COLLECT INTO l_bal_hdr_ids, l_bal_bal_segs, l_bal_ent_currs, l_bal_enc_ids
FROM xla_validation_lines_gt t
WHERE balance_type_code not in('E','B')
AND entered_currency_code <> 'STAT'
AND balancing_line_type in (C_LINE_TYPE_PROCESS,
C_LINE_TYPE_IC_BAL_INTER,
C_LINE_TYPE_IC_BAL_INTRA)
GROUP BY ae_header_id, entered_currency_mau, bal_seg_value, entered_currency_code, encumbrance_type_id
HAVING decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(unrounded_entered_cr,0))/entered_currency_mau)
,'UP' ,CEIL(sum(nvl(unrounded_entered_cr,0))/entered_currency_mau)
,FLOOR(sum(nvl(unrounded_entered_cr,0))/entered_currency_mau)) <>
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(unrounded_entered_dr,0))/entered_currency_mau)
,'UP' ,CEIL(sum(nvl(unrounded_entered_dr,0))/entered_currency_mau)
,FLOOR(sum(nvl(unrounded_entered_dr,0))/entered_currency_mau));
SELECT DISTINCT xvl.ae_header_id, xvl.entered_currency_code
BULK COLLECT INTO l_already_bal_hdr_ids, l_already_bal_ent_currs
FROM xla_validation_lines_gt xvl
WHERE xvl.ae_header_id IN (
SELECT t.ae_header_id
FROM xla_validation_lines_gt t
WHERE t.balance_type_code not in('E','B')
AND t.entered_currency_code <> 'STAT'
AND t.balancing_line_type in (C_LINE_TYPE_PROCESS,
C_LINE_TYPE_IC_BAL_INTER,
C_LINE_TYPE_IC_BAL_INTRA)
GROUP BY t.ae_header_id, t.entered_currency_mau, t.bal_seg_value, t.entered_currency_code, t.encumbrance_type_id
HAVING decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(t.unrounded_entered_cr,0))/t.entered_currency_mau)
,'UP' ,CEIL(sum(nvl(t.unrounded_entered_cr,0))/t.entered_currency_mau)
,FLOOR(sum(nvl(t.unrounded_entered_cr,0))/t.entered_currency_mau)) <>
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(t.unrounded_entered_dr,0))/t.entered_currency_mau)
,'UP' ,CEIL(sum(nvl(t.unrounded_entered_dr,0))/t.entered_currency_mau)
,FLOOR(sum(nvl(t.unrounded_entered_dr,0))/t.entered_currency_mau))
)
GROUP BY xvl.ae_header_id, xvl.entered_currency_mau, xvl.entered_currency_code
HAVING decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(xvl.unrounded_entered_cr,0))/xvl.entered_currency_mau)
,'UP' ,CEIL(sum(nvl(xvl.unrounded_entered_cr,0))/xvl.entered_currency_mau)
,FLOOR(sum(nvl(xvl.unrounded_entered_cr,0))/xvl.entered_currency_mau)) =
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(xvl.unrounded_entered_dr,0))/xvl.entered_currency_mau)
,'UP' ,CEIL(sum(nvl(xvl.unrounded_entered_dr,0))/xvl.entered_currency_mau)
,FLOOR(sum(nvl(xvl.unrounded_entered_dr,0))/xvl.entered_currency_mau))
AND decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(xvl.unrounded_accounted_cr,0))/l_mau)*l_mau
,'UP' ,CEIL(sum(nvl(xvl.unrounded_accounted_cr,0))/l_mau)*l_mau
,FLOOR(sum(nvl(xvl.unrounded_accounted_cr,0))/l_mau)*l_mau) <>
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(xvl.unrounded_accounted_dr,0))/l_mau)*l_mau
,'UP',CEIL(sum(nvl(xvl.unrounded_accounted_dr,0))/l_mau)*l_mau
,FLOOR(sum(nvl(xvl.unrounded_accounted_dr,0))/l_mau)*l_mau);
INSERT INTO xla_validation_lines_gt
(balancing_line_type
,ledger_id
,ae_header_id
,max_ae_line_num
,max_displayed_line_number
,ae_line_num
,displayed_line_number
,event_id
,entity_id
,balance_type_code
,accounting_date
,entered_currency_code
,unrounded_entered_dr
,entered_dr
,unrounded_accounted_dr
,accounted_dr
,unrounded_entered_cr
,entered_cr
,unrounded_accounted_cr
,accounted_cr
,bal_seg_value
,code_combination_id
,encumbrance_type_id
,party_type_code
,party_id
,party_site_id
,error_flag)
SELECT C_LINE_TYPE_XLA_BALANCING
,g_ledger_id
,l_bal_hdr_ids(i)
,t.max_ae_line_num
,t.max_displayed_line_number
,t.max_ae_line_num
,t.max_displayed_line_number
,t.event_id
,t.entity_id
,t.balance_type_code
,t.accounting_date
,t.entered_currency_code
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
sum(nvl(unrounded_entered_cr,0))
END
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_entered_cr,0))/t.entered_currency_mau)*t.entered_currency_mau
,'UP'
,CEIL(sum(nvl(unrounded_entered_cr,0))/t.entered_currency_mau)*t.entered_currency_mau
,FLOOR(sum(nvl(unrounded_entered_cr,0))/t.entered_currency_mau)*t.entered_currency_mau)
END
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
sum(nvl(unrounded_accounted_cr,0))
END
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_accounted_cr,0))/l_mau)*l_mau
,'UP'
,CEIL(sum(nvl(unrounded_accounted_cr,0))/l_mau)*l_mau
,FLOOR(sum(nvl(unrounded_accounted_cr,0))/l_mau)*l_mau)
END
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
sum(nvl(unrounded_entered_dr,0))
END -- unrounded_entered_cr
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_entered_dr,0))/t.entered_currency_mau)*t.entered_currency_mau
,'UP'
,CEIL(sum(nvl(unrounded_entered_dr,0))/t.entered_currency_mau)*t.entered_currency_mau
,FLOOR(sum(nvl(unrounded_entered_dr,0))/t.entered_currency_mau)*t.entered_currency_mau)
END -- entered_cr
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
sum(nvl(unrounded_accounted_dr,0))
END -- unrounded_accounted_cr
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_accounted_dr,0))/l_mau)*l_mau
,'UP'
,CEIL(sum(nvl(unrounded_accounted_dr,0))/l_mau)*l_mau
,FLOOR(sum(nvl(unrounded_accounted_dr,0))/l_mau)*l_mau)
END -- accounted_cr
,t.bal_seg_value
,-1
,t.encumbrance_type_id
,t.party_type_code
,t.party_id
,t.party_site_id
,NULL
FROM xla_validation_lines_gt t
WHERE ae_header_id = l_bal_hdr_ids(i)
AND entered_currency_code = l_bal_ent_currs(i)
AND bal_seg_value = l_bal_bal_segs(i)
AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
AND balancing_line_type IN (C_LINE_TYPE_PROCESS
,C_LINE_TYPE_IC_BAL_INTER
,C_LINE_TYPE_IC_BAL_INTRA)
GROUP BY
t.max_ae_line_num
,t.max_displayed_line_number
,t.event_id
,t.entity_id
,t.balance_type_code
,t.bal_seg_value
,t.entered_currency_code
,t.accounting_date
,t.entered_currency_mau
,t.encumbrance_type_id
,t.party_type_code
,t.party_id
,t.party_site_id
--
-- This has been added to combine two insert statements
-- (for Debit and Credit) - Bug 5279912.
-- Without this, credit and debit lines are merged.
--
,DECODE(t.unrounded_entered_dr,NULL,'CR','DR')
HAVING sum(nvl(unrounded_accounted_cr,0)) <> 0
OR sum(nvl(unrounded_accounted_dr,0)) <> 0;
INSERT INTO xla_validation_lines_gt
(balancing_line_type
,ledger_id
,ae_header_id
,max_ae_line_num
,max_displayed_line_number
,ae_line_num
,displayed_line_number
,event_id
,entity_id
,balance_type_code
,accounting_date
,entered_currency_code
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,unrounded_entered_dr
,unrounded_entered_cr
,unrounded_accounted_dr
,unrounded_accounted_cr
,bal_seg_value
,code_combination_id
,encumbrance_type_id
,party_type_code
,party_id
,party_site_id
,error_flag)
SELECT C_LINE_TYPE_XLA_BALANCING
,g_ledger_id
,l_already_bal_hdr_ids(i)
,t.max_ae_line_num
,t.max_displayed_line_number
,t.max_ae_line_num
,t.max_displayed_line_number
,t.event_id
,t.entity_id
,t.balance_type_code
,t.accounting_date
,t.entered_currency_code
,t.entered_cr
,t.entered_dr
,t.accounted_cr
,t.accounted_dr
,t.unrounded_entered_cr
,t.unrounded_entered_dr
,t.unrounded_accounted_cr
,t.unrounded_accounted_dr
,t.bal_seg_value
,-1
,t.encumbrance_type_id
,t.party_type_code
,t.party_id
,t.party_site_id
,NULL
FROM xla_validation_lines_gt t
WHERE t.ae_header_id = l_already_bal_hdr_ids(i)
AND t.entered_currency_code = l_already_bal_ent_currs(i)
AND t.balancing_line_type IN (C_LINE_TYPE_PROCESS
,C_LINE_TYPE_IC_BAL_INTER
,C_LINE_TYPE_IC_BAL_INTRA);
UPDATE xla_validation_lines_gt t
SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
SELECT nc.code_combination_id
,nc.reference3
,decode(g_mgt_seg_column_name, g_bal_seg_column_name, t.bal_seg_value,
'SEGMENT1', sc.segment1, 'SEGMENT2', sc.segment2,
'SEGMENT3', sc.segment3, 'SEGMENT4', sc.segment4,
'SEGMENT5', sc.segment5, 'SEGMENT6', sc.segment6,
'SEGMENT7', sc.segment7, 'SEGMENT8', sc.segment8,
'SEGMENT9', sc.segment9, 'SEGMENT10', sc.segment10,
'SEGMENT11', sc.segment11, 'SEGMENT12', sc.segment12,
'SEGMENT13', sc.segment13, 'SEGMENT14', sc.segment14,
'SEGMENT15', sc.segment15, 'SEGMENT16', sc.segment16,
'SEGMENT17', sc.segment17, 'SEGMENT18', sc.segment18,
'SEGMENT19', sc.segment19, 'SEGMENT20', sc.segment20,
'SEGMENT21', sc.segment21, 'SEGMENT22', sc.segment22,
'SEGMENT23', sc.segment23, 'SEGMENT24', sc.segment24,
'SEGMENT25', sc.segment25, 'SEGMENT26', sc.segment26,
'SEGMENT27', sc.segment27, 'SEGMENT28', sc.segment28,
'SEGMENT29', sc.segment29, 'SEGMENT30', sc.segment30, NULL)
FROM gl_code_combinations nc,
gl_code_combinations sc
WHERE nvl(nc.segment1,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT1',
t.bal_seg_value,nvl(sc.segment1,C_CHAR))
AND nvl(nc.segment2,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT2',
t.bal_seg_value,nvl(sc.segment2,C_CHAR))
AND nvl(nc.segment3,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT3',
t.bal_seg_value,nvl(sc.segment3,C_CHAR))
AND nvl(nc.segment4,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT4',
t.bal_seg_value,nvl(sc.segment4,C_CHAR))
AND nvl(nc.segment5,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT5',
t.bal_seg_value,nvl(sc.segment5,C_CHAR))
AND nvl(nc.segment6,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT6',
t.bal_seg_value,nvl(sc.segment6,C_CHAR))
AND nvl(nc.segment7,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT7',
t.bal_seg_value,nvl(sc.segment7,C_CHAR))
AND nvl(nc.segment8,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT8',
t.bal_seg_value,nvl(sc.segment8,C_CHAR))
AND nvl(nc.segment9,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT9',
t.bal_seg_value,nvl(sc.segment9,C_CHAR))
AND nvl(nc.segment10,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT10',
t.bal_seg_value,nvl(sc.segment10,C_CHAR))
AND nvl(nc.segment11,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT11',
t.bal_seg_value,nvl(sc.segment11,C_CHAR))
AND nvl(nc.segment12,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT12',
t.bal_seg_value,nvl(sc.segment12,C_CHAR))
AND nvl(nc.segment13,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT13',
t.bal_seg_value,nvl(sc.segment13,C_CHAR))
AND nvl(nc.segment14,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT14',
t.bal_seg_value,nvl(sc.segment14,C_CHAR))
AND nvl(nc.segment15,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT15',
t.bal_seg_value,nvl(sc.segment15,C_CHAR))
AND nvl(nc.segment16,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT16',
t.bal_seg_value,nvl(sc.segment16,C_CHAR))
AND nvl(nc.segment17,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT17',
t.bal_seg_value,nvl(sc.segment17,C_CHAR))
AND nvl(nc.segment18,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT18',
t.bal_seg_value,nvl(sc.segment18,C_CHAR))
AND nvl(nc.segment19,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT19',
t.bal_seg_value,nvl(sc.segment19,C_CHAR))
AND nvl(nc.segment20,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT20',
t.bal_seg_value,nvl(sc.segment20,C_CHAR))
AND nvl(nc.segment21,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT21',
t.bal_seg_value,nvl(sc.segment21,C_CHAR))
AND nvl(nc.segment22,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT22',
t.bal_seg_value,nvl(sc.segment22,C_CHAR))
AND nvl(nc.segment23,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT23',
t.bal_seg_value,nvl(sc.segment23,C_CHAR))
AND nvl(nc.segment24,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT24',
t.bal_seg_value,nvl(sc.segment24,C_CHAR))
AND nvl(nc.segment25,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT25',
t.bal_seg_value,nvl(sc.segment25,C_CHAR))
AND nvl(nc.segment26,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT26',
t.bal_seg_value,nvl(sc.segment26,C_CHAR))
AND nvl(nc.segment27,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT27',
t.bal_seg_value,nvl(sc.segment27,C_CHAR))
AND nvl(nc.segment28,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT28',
t.bal_seg_value,nvl(sc.segment28,C_CHAR))
AND nvl(nc.segment29,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT29',
t.bal_seg_value,nvl(sc.segment29,C_CHAR))
AND nvl(nc.segment30,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT30',
t.bal_seg_value,nvl(sc.segment30,C_CHAR))
AND nc.chart_of_accounts_id = sc.chart_of_accounts_id
AND sc.code_combination_id = g_sla_entered_cur_bal_sus_ccid)
WHERE t.balancing_line_type = C_LINE_TYPE_XLA_BALANCING;
UPDATE xla_validation_lines_gt
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_err_hdr_ids(j)
AND balancing_line_type = C_LINE_TYPE_PROCESS;
SELECT entity_id, event_id, ae_header_id, balance_type_code
FROM xla_validation_lines_gt
WHERE balancing_line_type = C_LINE_TYPE_ENC_BALANCING
GROUP BY entity_id, event_id, ae_header_id, balance_type_code;
SELECT nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,xlo.rounding_rule_code
INTO l_mau, l_rounding_rule_code
FROM xla_ledger_options xlo
,gl_ledgers gl
,fnd_currencies fcu
WHERE xlo.application_id = g_application_id
AND xlo.ledger_id = g_trx_ledger_id
AND gl.ledger_id = g_ledger_id
AND fcu.currency_code = gl.currency_code;
SELECT ae_header_id, bal_seg_value, entered_currency_code, encumbrance_type_id
BULK COLLECT INTO l_bal_hdr_ids, l_bal_bal_segs, l_bal_ent_currs, l_bal_enc_ids
FROM xla_validation_lines_gt t
WHERE balance_type_code = 'E'
AND entered_currency_code <> 'STAT'
AND balancing_line_type in (C_LINE_TYPE_PROCESS,
C_LINE_TYPE_IC_BAL_INTER,
C_LINE_TYPE_IC_BAL_INTRA)
GROUP BY ae_header_id, entered_currency_mau, bal_seg_value, entered_currency_code, encumbrance_type_id
HAVING (
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(unrounded_entered_cr,0))/entered_currency_mau)
,'UP' ,CEIL(sum(nvl(unrounded_entered_cr,0))/entered_currency_mau)
,FLOOR(sum(nvl(unrounded_entered_cr,0))/entered_currency_mau)) <>
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(unrounded_entered_dr,0))/entered_currency_mau)
,'UP' ,CEIL(sum(nvl(unrounded_entered_dr,0))/entered_currency_mau)
,FLOOR(sum(nvl(unrounded_entered_dr,0))/entered_currency_mau))
)
OR
(
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(unrounded_accounted_cr,0))/l_mau)
,'UP' ,CEIL(sum(nvl(unrounded_accounted_cr,0))/l_mau)
,FLOOR(sum(nvl(unrounded_accounted_cr,0))/l_mau)) <>
decode(l_rounding_rule_code
,'NEAREST' ,ROUND(sum(nvl(unrounded_accounted_dr,0))/l_mau)
,'UP' ,CEIL(sum(nvl(unrounded_accounted_dr,0))/l_mau)
,FLOOR(sum(nvl(unrounded_accounted_dr,0))/l_mau))
);
INSERT INTO xla_validation_lines_gt
(balancing_line_type
,ledger_id
,ae_header_id
,max_ae_line_num
,max_displayed_line_number
,ae_line_num
,displayed_line_number
,event_id
,entity_id
,balance_type_code
,accounting_date
,entered_currency_code
,unrounded_entered_dr
,entered_dr
,unrounded_accounted_dr
,accounted_dr
,unrounded_entered_cr
,entered_cr
,unrounded_accounted_cr
,accounted_cr
,bal_seg_value
,code_combination_id
,encumbrance_type_id
,party_type_code
,party_id
,party_site_id
,error_flag)
SELECT C_LINE_TYPE_ENC_BALANCING
,g_ledger_id
,l_bal_hdr_ids(i)
,t.max_ae_line_num
,t.max_displayed_line_number
,t.max_ae_line_num
,t.max_displayed_line_number
,t.event_id
,t.entity_id
,t.balance_type_code
,t.accounting_date
,t.entered_currency_code
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
sum(nvl(unrounded_entered_cr,0))
END
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_entered_cr,0))/t.entered_currency_mau)*t.entered_currency_mau
,'UP'
,CEIL(sum(nvl(unrounded_entered_cr,0))/t.entered_currency_mau)*t.entered_currency_mau
,FLOOR(sum(nvl(unrounded_entered_cr,0))/t.entered_currency_mau)*t.entered_currency_mau)
END
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
sum(nvl(unrounded_accounted_cr,0))
END
,CASE
WHEN sum(nvl(unrounded_accounted_cr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_accounted_cr,0))/l_mau)*l_mau
,'UP'
,CEIL(sum(nvl(unrounded_accounted_cr,0))/l_mau)*l_mau
,FLOOR(sum(nvl(unrounded_accounted_cr,0))/l_mau)*l_mau)
END
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
sum(nvl(unrounded_entered_dr,0))
END -- unrounded_entered_cr
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_entered_dr,0))/t.entered_currency_mau)*t.entered_currency_mau
,'UP'
,CEIL(sum(nvl(unrounded_entered_dr,0))/t.entered_currency_mau)*t.entered_currency_mau
,FLOOR(sum(nvl(unrounded_entered_dr,0))/t.entered_currency_mau)*t.entered_currency_mau)
END -- entered_cr
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
sum(nvl(unrounded_accounted_dr,0))
END -- unrounded_accounted_cr
,CASE
WHEN sum(nvl(unrounded_accounted_dr,0)) <> 0 THEN
decode(l_rounding_rule_code
,'NEAREST'
,ROUND(sum(nvl(unrounded_accounted_dr,0))/l_mau)*l_mau
,'UP'
,CEIL(sum(nvl(unrounded_accounted_dr,0))/l_mau)*l_mau
,FLOOR(sum(nvl(unrounded_accounted_dr,0))/l_mau)*l_mau)
END -- accounted_cr
,t.bal_seg_value
,-1
,t.encumbrance_type_id
,t.party_type_code
,t.party_id
,t.party_site_id
,NULL
FROM xla_validation_lines_gt t
WHERE ae_header_id = l_bal_hdr_ids(i)
AND entered_currency_code = l_bal_ent_currs(i)
AND bal_seg_value = l_bal_bal_segs(i)
AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
AND balancing_line_type IN (C_LINE_TYPE_PROCESS
,C_LINE_TYPE_IC_BAL_INTER
,C_LINE_TYPE_IC_BAL_INTRA)
GROUP BY
t.max_ae_line_num
,t.max_displayed_line_number
,t.event_id
,t.entity_id
,t.balance_type_code
,t.bal_seg_value
,t.entered_currency_code
,t.accounting_date
,t.entered_currency_mau
,t.encumbrance_type_id
,t.party_type_code
,t.party_id
,t.party_site_id
--
-- This has been added to combine two insert statements
-- (for Debit and Credit) - Bug 5279912.
-- Without this, credit and debit lines are merged.
--
,DECODE(t.unrounded_entered_dr,NULL,'CR','DR')
HAVING sum(nvl(unrounded_accounted_cr,0)) <> 0
OR sum(nvl(unrounded_accounted_dr,0)) <> 0;
UPDATE xla_validation_lines_gt t
SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
SELECT nc.code_combination_id
,nc.reference3
,decode(g_mgt_seg_column_name, g_bal_seg_column_name, t.bal_seg_value,
'SEGMENT1', sc.segment1, 'SEGMENT2', sc.segment2,
'SEGMENT3', sc.segment3, 'SEGMENT4', sc.segment4,
'SEGMENT5', sc.segment5, 'SEGMENT6', sc.segment6,
'SEGMENT7', sc.segment7, 'SEGMENT8', sc.segment8,
'SEGMENT9', sc.segment9, 'SEGMENT10', sc.segment10,
'SEGMENT11', sc.segment11, 'SEGMENT12', sc.segment12,
'SEGMENT13', sc.segment13, 'SEGMENT14', sc.segment14,
'SEGMENT15', sc.segment15, 'SEGMENT16', sc.segment16,
'SEGMENT17', sc.segment17, 'SEGMENT18', sc.segment18,
'SEGMENT19', sc.segment19, 'SEGMENT20', sc.segment20,
'SEGMENT21', sc.segment21, 'SEGMENT22', sc.segment22,
'SEGMENT23', sc.segment23, 'SEGMENT24', sc.segment24,
'SEGMENT25', sc.segment25, 'SEGMENT26', sc.segment26,
'SEGMENT27', sc.segment27, 'SEGMENT28', sc.segment28,
'SEGMENT29', sc.segment29, 'SEGMENT30', sc.segment30, NULL)
FROM gl_code_combinations nc,
gl_code_combinations sc
WHERE nvl(nc.segment1,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT1',
t.bal_seg_value,nvl(sc.segment1,C_CHAR))
AND nvl(nc.segment2,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT2',
t.bal_seg_value,nvl(sc.segment2,C_CHAR))
AND nvl(nc.segment3,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT3',
t.bal_seg_value,nvl(sc.segment3,C_CHAR))
AND nvl(nc.segment4,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT4',
t.bal_seg_value,nvl(sc.segment4,C_CHAR))
AND nvl(nc.segment5,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT5',
t.bal_seg_value,nvl(sc.segment5,C_CHAR))
AND nvl(nc.segment6,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT6',
t.bal_seg_value,nvl(sc.segment6,C_CHAR))
AND nvl(nc.segment7,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT7',
t.bal_seg_value,nvl(sc.segment7,C_CHAR))
AND nvl(nc.segment8,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT8',
t.bal_seg_value,nvl(sc.segment8,C_CHAR))
AND nvl(nc.segment9,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT9',
t.bal_seg_value,nvl(sc.segment9,C_CHAR))
AND nvl(nc.segment10,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT10',
t.bal_seg_value,nvl(sc.segment10,C_CHAR))
AND nvl(nc.segment11,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT11',
t.bal_seg_value,nvl(sc.segment11,C_CHAR))
AND nvl(nc.segment12,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT12',
t.bal_seg_value,nvl(sc.segment12,C_CHAR))
AND nvl(nc.segment13,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT13',
t.bal_seg_value,nvl(sc.segment13,C_CHAR))
AND nvl(nc.segment14,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT14',
t.bal_seg_value,nvl(sc.segment14,C_CHAR))
AND nvl(nc.segment15,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT15',
t.bal_seg_value,nvl(sc.segment15,C_CHAR))
AND nvl(nc.segment16,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT16',
t.bal_seg_value,nvl(sc.segment16,C_CHAR))
AND nvl(nc.segment17,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT17',
t.bal_seg_value,nvl(sc.segment17,C_CHAR))
AND nvl(nc.segment18,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT18',
t.bal_seg_value,nvl(sc.segment18,C_CHAR))
AND nvl(nc.segment19,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT19',
t.bal_seg_value,nvl(sc.segment19,C_CHAR))
AND nvl(nc.segment20,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT20',
t.bal_seg_value,nvl(sc.segment20,C_CHAR))
AND nvl(nc.segment21,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT21',
t.bal_seg_value,nvl(sc.segment21,C_CHAR))
AND nvl(nc.segment22,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT22',
t.bal_seg_value,nvl(sc.segment22,C_CHAR))
AND nvl(nc.segment23,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT23',
t.bal_seg_value,nvl(sc.segment23,C_CHAR))
AND nvl(nc.segment24,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT24',
t.bal_seg_value,nvl(sc.segment24,C_CHAR))
AND nvl(nc.segment25,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT25',
t.bal_seg_value,nvl(sc.segment25,C_CHAR))
AND nvl(nc.segment26,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT26',
t.bal_seg_value,nvl(sc.segment26,C_CHAR))
AND nvl(nc.segment27,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT27',
t.bal_seg_value,nvl(sc.segment27,C_CHAR))
AND nvl(nc.segment28,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT28',
t.bal_seg_value,nvl(sc.segment28,C_CHAR))
AND nvl(nc.segment29,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT29',
t.bal_seg_value,nvl(sc.segment29,C_CHAR))
AND nvl(nc.segment30,C_CHAR) = DECODE(g_bal_seg_column_name,'SEGMENT30',
t.bal_seg_value,nvl(sc.segment30,C_CHAR))
AND nc.chart_of_accounts_id = sc.chart_of_accounts_id
AND sc.code_combination_id = DECODE(t.balance_type_code, 'E'
,g_res_encumb_ccid, g_sla_entered_cur_bal_sus_ccid))
WHERE t.balancing_line_type = C_LINE_TYPE_ENC_BALANCING;
UPDATE xla_validation_lines_gt
SET balancing_line_type = C_LINE_TYPE_COMPLETE
WHERE ae_header_id = l_err_hdr_ids(j)
AND balancing_line_type = C_LINE_TYPE_PROCESS;
SELECT transfer_to_gl_mode_code
FROM xla_ledger_options
WHERE ledger_id = g_ledger_id
AND application_id = g_application_id;
INSERT INTO xla_ae_lines
(ae_header_id
,ae_line_num
,displayed_line_number
,code_combination_id
,accounting_class_code
,application_id
,control_balance_flag
,analytical_balance_flag
,unrounded_accounted_cr
,unrounded_accounted_dr
,accounted_cr
,accounted_dr
,description -- added line for bug 6902085
,currency_code
,currency_conversion_date
,currency_conversion_type
,currency_conversion_rate
,unrounded_entered_cr
,unrounded_entered_dr
,entered_cr
,entered_dr
,gl_sl_link_table
,gl_sl_link_id
,gl_transfer_mode_code
,gain_or_loss_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
,ledger_id
,accounting_date
,encumbrance_type_id
,party_type_code
,party_id
,party_site_id
,mpa_accrual_entry_flag) -- 4262811
SELECT l.ae_header_id
,max_ae_line_num +
ROW_NUMBER() over (partition by l.ae_header_id
order by l.ae_line_num)
,max_displayed_line_number +
ROW_NUMBER() over (partition by l.ae_header_id
order by l.displayed_line_number)
,NVL(l.code_combination_id,-1)
,CASE l.balancing_line_type
WHEN C_LINE_TYPE_IC_BAL_INTRA THEN C_ACCT_CLASS_INTRA
WHEN C_LINE_TYPE_IC_BAL_INTER THEN C_ACCT_CLASS_INTER
WHEN C_LINE_TYPE_RD_BALANCING THEN C_ACCT_CLASS_ROUNDING
WHEN C_LINE_TYPE_ENC_BALANCING THEN C_ACCT_CLASS_RFE -- 4458381
WHEN C_LINE_TYPE_ENC_BAL_ERROR THEN C_ACCT_CLASS_RFE -- 4458381
ELSE C_ACCT_CLASS_BALANCE END
,g_application_id
-- control_balance_flag
,CASE l.balancing_line_type
WHEN C_LINE_TYPE_IC_BAL_INTRA THEN NULL
WHEN C_LINE_TYPE_IC_BAL_INTER THEN NULL
ELSE DECODE(NVL(ccid.reference3,'N'),'N',NULL,
DECODE(ccid.account_type, 'A', 'P'
, 'L', 'P'
, 'O', 'P'
, NULL)) END
,NULL
,l.unrounded_accounted_cr
,l.unrounded_accounted_dr
,l.accounted_cr
,l.accounted_dr
,xl.meaning -- added line for bug 6902085
,l.entered_currency_code
,decode(l.entered_currency_code,
g_ledger_currency_code, NULL,
l.accounting_date)
,decode(l.entered_currency_code, g_ledger_currency_code, NULL, 'User')
,decode(l.entered_currency_code, g_ledger_currency_code, NULL,
CASE WHEN l.accounted_dr IS NOT NULL AND l.entered_dr <> 0
THEN l.accounted_dr/l.entered_dr
WHEN l.accounted_dr IS NOT NULL
THEN 1
WHEN l.entered_cr <> 0
THEN l.accounted_cr/l.entered_cr
ELSE 1
END)
,l.unrounded_entered_cr
,l.unrounded_entered_dr
,l.entered_cr
,l.entered_dr
,'XLAJEL'
,decode(g_accounting_mode,'F',xla_gl_sl_link_id_s.nextval,NULL)
,decode(l_transfer_to_gl_mode_code,'D','D','S')
,'N'
,TRUNC(SYSDATE)
,xla_environment_pkg.g_usr_id
,TRUNC(SYSDATE)
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,TRUNC(SYSDATE)
,xla_environment_pkg.g_Prog_Appl_Id
,xla_environment_pkg.g_Prog_Id
,xla_environment_pkg.g_Req_Id
,l.ledger_id
,l.accounting_date
,l.encumbrance_type_id
,l.party_type_code
,l.party_id
,l.party_site_id
,'N' -- 4262811
FROM xla_validation_lines_gt l
,gl_code_combinations ccid
,xla_lookups xl -- added line for bug 6902085
WHERE l.balancing_line_type NOT IN (C_LINE_TYPE_PROCESS, C_LINE_TYPE_COMPLETE)
AND ccid.code_combination_id(+) = l.code_combination_id
AND xl.lookup_type = 'XLA_JE_VALD_LINE_DESC' -- added filter for bug 6902085
AND xl.lookup_code = decode(l.balancing_line_type -- added filter for bug 6902085
,C_LINE_TYPE_IC_BAL_INTRA
,'INTRA'
,C_LINE_TYPE_IC_BAL_INTER
,'INTER'
,C_LINE_TYPE_RD_BALANCING
,'ROUNDING'
,C_LINE_TYPE_ENC_BALANCING
,'RFE'
,C_LINE_TYPE_ENC_BAL_ERROR
,'RFE'
,'BALANCE');
trace(p_msg => '# xla_ae_lines inserted for balancing = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
DELETE FROM xla_ae_segment_values
WHERE ae_header_id in (SELECT /*+ UNNEST NO_SEMIJOIN */ ae_header_id -- 4752774
FROM xla_ae_headers_gt
WHERE ledger_id = g_ledger_id
AND accounting_date <= NVL(g_end_date, accounting_date)); -- 4262811
DELETE FROM xla_ae_segment_values
WHERE ae_header_id = g_ae_header_id;
INSERT INTO xla_ae_segment_values
(ae_header_id, segment_type_code, segment_value, ae_lines_count)
SELECT ae_header_id, C_BAL_SEGMENT, bal_seg_value, count(*)
FROM xla_validation_lines_gt
WHERE bal_seg_value IS NOT NULL
GROUP BY ae_header_id, bal_seg_value
UNION ALL
SELECT ae_header_id, C_MGT_SEGMENT, mgt_seg_value, count(*)
FROM xla_validation_lines_gt
WHERE mgt_seg_value IS NOT NULL
GROUP BY ae_header_id, mgt_seg_value
UNION ALL
SELECT ae_header_id, C_CC_SEGMENT, cost_center_seg_value, count(*)
FROM xla_validation_lines_gt
WHERE cost_center_seg_value IS NOT NULL
GROUP BY ae_header_id, cost_center_seg_value
UNION ALL
SELECT ae_header_id, C_NA_SEGMENT, natural_account_seg_value, count(*)
FROM xla_validation_lines_gt
WHERE natural_account_seg_value IS NOT NULL
GROUP BY ae_header_id, natural_account_seg_value;
trace(p_msg => '# xla_ae_segment_values inserted = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
SELECT *
FROM xla_validation_lines_gt
WHERE balancing_line_type IN (C_LINE_TYPE_LC_BALANCING
,C_LINE_TYPE_XLA_BALANCING
,C_LINE_TYPE_ENC_BALANCING)
AND control_account_enabled_flag <> 'N'
AND (party_type_code IS NULL OR party_id IS NULL);
UPDATE xla_validation_lines_gt
SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
WHERE ae_header_id = g_err_hdr_ids(i);
UPDATE xla_validation_lines_gt
SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_RELATED_INVALID
WHERE event_id = g_err_event_ids(i);
SELECT ae_header_id
, funds_status_code
, event_id
, entity_id
BULK COLLECT INTO
l_array_ae_header_id
, l_array_hdr_funds_status_code
, l_array_event_id
, l_array_entity_id
FROM xla_ae_headers_gt
WHERE ledger_id = g_ledger_id;
UPDATE xla_ae_headers
SET funds_status_code = l_array_hdr_funds_status_code(i)
, accounting_entry_status_code = CASE WHEN l_array_hdr_funds_status_code(i) = 'F' THEN
'I'
WHEN l_array_hdr_funds_status_code(i) = 'T' THEN
'I'
ELSE accounting_entry_status_code
END
-- Bug 5056632. updates group_id back to Null if je is invalid
, group_id = CASE WHEN l_array_hdr_funds_status_code(i) = 'F' THEN
NULL
WHEN l_array_hdr_funds_status_code(i) = 'T' THEN
NULL
ELSE group_id
END
WHERE application_id = g_application_id
AND ae_header_id = l_array_ae_header_id(i);
trace(p_msg => '# row updated in xla_ae_headers = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
SELECT xvl.ae_header_id
, xvl.ae_line_num
, xvl.event_id
, xvl.entity_id
, xvl.funds_status_code
, flv.meaning
, xah.funds_status_code
, xvl.entered_cr
, xvl.entered_dr
, xvl.accounted_cr
, xvl.accounted_dr
, xvl.unrounded_entered_cr
, xvl.unrounded_entered_dr
, xvl.unrounded_accounted_cr
, xvl.unrounded_accounted_dr
BULK COLLECT INTO
l_array_ae_header_id
, l_array_ae_line_num
, l_array_event_id
, l_array_entity_id
, l_array_ln_funds_status_code
, l_array_ln_funds_status
, l_array_hdr_funds_status_code
, l_array_entered_cr
, l_array_entered_dr
, l_array_accounted_cr
, l_array_accounted_dr
, l_array_unrounded_entered_cr
, l_array_unrounded_entered_dr
, l_array_unrounded_accounted_cr
, l_array_unrounded_accounted_dr
FROM xla_validation_lines_gt xvl
, xla_ae_headers xah
, fnd_lookup_values flv
WHERE xvl.ae_header_id = xah.ae_header_id
AND xvl.accounting_class_code <> 'RFE'
AND flv.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
AND flv.lookup_code = xvl.funds_status_code
AND flv.language = USERENV('LANG');
UPDATE xla_ae_lines
SET funds_status_code = l_array_ln_funds_status_code(i)
, entered_cr = CASE WHEN entered_cr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE entered_cr END
, entered_dr = CASE WHEN entered_dr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE entered_dr END
, accounted_cr = CASE WHEN accounted_cr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE accounted_cr END
, accounted_dr = CASE WHEN accounted_dr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE accounted_dr END
, unrounded_entered_cr
= CASE WHEN unrounded_entered_cr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE unrounded_entered_cr END
, unrounded_entered_dr
= CASE WHEN unrounded_entered_dr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE unrounded_entered_dr END
, unrounded_accounted_cr
= CASE WHEN unrounded_accounted_cr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE unrounded_accounted_cr END
, unrounded_accounted_dr
= CASE WHEN unrounded_accounted_dr IS NULL
THEN NULL
WHEN l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
THEN 0
ELSE unrounded_accounted_dr END
WHERE application_id = g_application_id
AND ae_header_id = l_array_ae_header_id(i)
AND ae_line_num = l_array_ae_line_num(i);
trace(p_msg => '# row updated in xla_ae_lines = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
UPDATE xla_ae_lines
SET entered_cr = CASE WHEN entered_cr IS NULL
THEN NULL
ELSE entered_cr - NVL(l_array_entered_dr(i),0)
END
, entered_dr = CASE WHEN entered_dr IS NULL
THEN NULL
ELSE entered_dr - NVL(l_array_entered_cr(i),0)
END
, accounted_cr = CASE WHEN accounted_cr IS NULL
THEN NULL
ELSE accounted_cr - NVL(l_array_accounted_dr(i),0)
END
, accounted_dr = CASE WHEN accounted_dr IS NULL
THEN NULL
ELSE accounted_dr - NVL(l_array_accounted_cr(i),0)
END
, unrounded_entered_cr
= CASE WHEN unrounded_entered_cr IS NULL
THEN NULL
ELSE unrounded_entered_cr - NVL(l_array_unrounded_entered_dr(i),0)
END
, unrounded_entered_dr
= CASE WHEN unrounded_entered_dr IS NULL
THEN NULL
ELSE unrounded_entered_dr - NVL(l_array_unrounded_entered_cr(i),0)
END
, unrounded_accounted_cr
= CASE WHEN unrounded_accounted_cr IS NULL
THEN NULL
ELSE unrounded_accounted_cr - NVL(l_array_unrounded_accounted_dr(i),0)
END
, unrounded_accounted_dr
= CASE WHEN unrounded_accounted_dr IS NULL
THEN NULL
ELSE unrounded_accounted_dr - NVL(l_array_unrounded_accounted_cr(i),0)
END
WHERE application_id = g_application_id
AND ae_header_id = l_array_ae_header_id(i)
AND l_array_hdr_funds_status_code(i) = 'P'
AND SUBSTR(l_array_ln_funds_status_code(i),1,1) = 'F'
AND accounting_class_code = 'RFE';
trace(p_msg => '# RFE row updated in xla_ae_lines = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
PROCEDURE update_error_status
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_error_status';
trace(p_msg => 'BEGIN of procedure update_error_status',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
UPDATE xla_ae_headers
SET accounting_entry_status_code = C_AE_STATUS_INVALID
-- Bug 5056632. updates group_id back to Null if je is invalid
,group_id = NULL
WHERE ae_header_id = g_err_hdr_ids(i)
AND application_id = g_application_id;
trace(p_msg => '# xla_ae_headers updated to C_AE_STATUS_INVALID = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
UPDATE xla_ae_headers
SET accounting_entry_status_code = C_AE_STATUS_RELATED
WHERE accounting_entry_status_code <> C_AE_STATUS_INVALID
AND event_id = g_err_event_ids(i)
AND application_id = g_application_id;
UPDATE xla_ae_headers xah1 -- 4262811a
SET accounting_entry_status_code = C_AE_STATUS_RELATED
-- Bug 5056632. updates group_id back to Null if je is invalid
,group_id = NULL
WHERE xah1.accounting_entry_status_code <> C_AE_STATUS_INVALID
AND xah1.event_id = g_err_event_ids(i)
AND xah1.application_id = g_application_id
AND xah1.parent_ae_line_num IS NULL -- 4262811a Existing logic, and this works for Accrual Reversal.
AND NOT EXISTS (SELECT 1 -- 4262811a Do not update MPA's original entry, it is set correctly above.
FROM xla_ae_headers xah2
WHERE xah2.event_id = xah1.event_id -- 5231063 g_err_event_ids(i)
AND xah2.application_id = xah1.application_id -- 5231063 g_application_id
AND xah2.ae_header_id = g_err_hdr_ids(i)
AND xah2.parent_ae_line_num IS NOT NULL);
trace(p_msg => '# xla_ae_headers updated to C_AE_STATUS_RELATED = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
UPDATE xla_ae_headers xah1
SET (accounting_entry_status_code, group_id) =
(SELECT DECODE(xah2.accounting_entry_status_code
,'D',xah1.accounting_entry_status_code
,'F',xah1.accounting_entry_status_code
,C_AE_STATUS_RELATED)
--
-- Bug 5056632. updates group_id back to Null if je is invalid
,NULL
FROM xla_ae_headers xah2
WHERE xah2.event_id = g_err_event_ids(i)
AND xah2.application_id = g_application_id
AND xah2.ae_header_id = xah1.parent_ae_header_id
AND xah2.parent_ae_line_num IS NULL)
WHERE xah1.event_id = g_err_event_ids(i)
AND xah1.application_id = g_application_id
AND xah1.parent_ae_line_num IS NOT NULL;
UPDATE xla_events_gt
SET process_status_code = 'I'
WHERE event_id = g_err_event_ids(i)
AND process_status_code <> 'E';
UPDATE xla_events_gt evt -- 4262811a
SET process_status_code =
(SELECT DECODE(xah2.parent_ae_line_num,NULL,'I' -- 4262811a Status of MPA rows does not affect event status
, evt.process_status_code)
FROM xla_ae_headers xah2
WHERE xah2.event_id = g_err_event_ids(i)
AND xah2.application_id = g_application_id
AND xah2.ae_header_id = g_err_hdr_ids(i))
WHERE event_id = g_err_event_ids(i)
AND process_status_code <> 'E';
trace(p_msg => '# xla_events_gt updated = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
trace(p_msg => 'End of procedure update_error_status',
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
(p_location => 'xla_je_validation_pkg.update_error_status');
SELECT distinct xah.ledger_id, xah.event_id
FROM xla_ae_headers xah
, xla_events_gt xeg
WHERE xeg.event_id = xah.event_id
AND xah.application_id = g_application_id
AND xeg.process_status_code = 'I';
SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = p_qualifier;
SELECT suspense_allowed_flag
INTO g_suspense_allowed_flag
FROM gl_ledgers
WHERE ledger_id = g_ledger_id;
SELECT name
,currency_code ledger_currency_code
,chart_of_accounts_id ledger_coa_id
,bal_seg_column_name
,mgt_seg_column_name
,allow_intercompany_post_flag
,bal_seg_value_option_code
,mgt_seg_value_option_code
,sla_bal_by_ledger_curr_flag
,sla_ledger_cur_bal_sus_ccid
,sla_entered_cur_bal_sus_ccid
,rounding_code_combination_id
,latest_encumbrance_year
,transaction_calendar_id
,enable_average_balances_flag
,res_encumb_code_combination_id
,ledger_category_code
,suspense_allowed_flag
INTO g_ledger_name,
g_ledger_currency_code,
g_ledger_coa_id,
g_bal_seg_column_name,
g_mgt_seg_column_name,
g_allow_intercompany_post_flag,
g_bal_seg_value_option_code,
g_mgt_seg_value_option_code,
g_sla_bal_by_ledger_curr_flag,
g_sla_ledger_cur_bal_sus_ccid,
g_sla_entered_cur_bal_sus_ccid,
g_sla_rounding_ccid,
g_latest_encumbrance_year,
g_transaction_calendar_id,
g_enable_average_balances_flag,
g_res_encumb_ccid,
g_ledger_category_code,
g_suspense_allowed_flag
FROM gl_ledgers
WHERE ledger_id = g_ledger_id;
SELECT nvl(valuation_method_flag,'N')
INTO l_valuation_method_flag
FROM XLA_SUBLEDGERS
WHERE application_id = g_application_id;
delete from xla_validation_lines_gt;
delete from xla_validation_lines_gt;
update_error_status;
delete from xla_validation_lines_gt;
delete from xla_validation_lines_gt;