[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct entity_id
from xla_upg_errors
where error_level = 'A'
and application_id = 602
and upg_source_application_id = 602;
delete from xla_upg_errors
where application_id = 602
and upg_source_application_id = 602
and error_message_name IN ('XLA_UPG_APP_NOT_DEFINED'
,'XLA_APP_VERIFICATION_RECORD');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
(SELECT
xla_upg_errors_s.nextval
,602
,602
,sysdate
,-1
,sysdate
,-1
,-9999
, 'A'
,'XLA_UPG_APP_NOT_DEFINED'
,entity_id from(select entity_id
from xla_transaction_entities_upg xen
where NOT EXISTS (SELECT 1
FROM XLA_SUBLEDGERS XS
WHERE xen.application_id
= xs.application_id)));
UPDATE xla_transaction_entities_upg
set upg_valid_flag = 'A'
where entity_id = l_entity_id(i);
UPDATE xla_events
set upg_valid_flag = 'B'
where entity_id = l_entity_id(i);
UPDATE xla_ae_headers
set upg_valid_flag = 'C'
where entity_id = l_entity_id(i);
INSERT INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
values(
xla_upg_errors_s.nextval
,602
,602
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_APP_VERIFICATION_RECORD'
,l_rowcount);
select distinct entity_id
from xla_upg_errors
where error_level = 'N'
and application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id;
delete from xla_upg_errors
where application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id
and error_message_name in ('XLA_UPG_ENCODE_INVALID'
,'XLA_ENT_VERIFICATION_RECORD');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id,creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
(select xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
,'N'
,'XLA_UPG_ENCODE_INVALID'
,entity_id
from xla_transaction_entities_upg xen
where not exists (select 1 from xla_entity_types_b xent
where xen.entity_code = xent.entity_code
and xen.application_id = xent.application_id)
and xen.application_id = p_application_id
and xen.upg_source_application_id = p_upgrading_application_id);
UPDATE xla_transaction_entities_upg
set upg_valid_flag = 'D'
where entity_id = l_entity_id(i);
UPDATE xla_events
set upg_valid_flag = 'E'
where entity_id = l_entity_id(i);
UPDATE xla_ae_headers
set upg_valid_flag = 'F'
where entity_id = l_entity_id(i);
INSERT INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
,created_by, last_update_date, last_updated_by, upg_batch_id
,error_level, error_message_name,entity_id)
values(
xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_ENT_VERIFICATION_RECORD'
,l_rowcount);
select distinct event_id
from xla_upg_errors
where error_level = 'E'
and application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id;
delete from xla_upg_errors
where application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id
and error_message_name in ('XLA_UPG_EVT_NO_ENTITY'
,'XLA_UPG_EVT_INV_ENTITY'
,'XLA_UPG_EVTYP_INVALID'
,'XLA_UPG_EVSTCODE_INVALID'
,'XLA_UPG_PROCSTCODE_INVALID'
,'XLA_UPG_EVNO_INVALID'
,'XLA_UPG_EVTCODE_INVALID'
,'XLA_UPG_ACC_CLASS_INVALID'
,'XLA_EVT_VERIFICATION_RECORD');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name, event_id)
(select xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'E'
,decode(grm.multiplier,1,'XLA_UPG_EVT_NO_ENTITY'
,2,'XLA_UPG_EVT_INV_ENTITY'
,3,'XLA_UPG_EVTYP_INVALID'
,4,'XLA_UPG_EVSTCODE_INVALID'
,5,'XLA_UPG_PROCSTCODE_INVALID'
,6,'XLA_UPG_EVNO_INVALID'
,7,'XLA_UPG_EVTCODE_INVALID'
,'XLA_UPG_ACC_CLASS_INVALID')
,event_id
from (select distinct event_id
,CASE when xen.entity_code IS NULL THEN 'Y'
ELSE 'N' END event_error1-- Event exists without entity
,CASE when xent.entity_code IS NULL THEN 'Y'
ELSE 'N' END event_error2 -- Event attached to invalid entity
,CASE when xevt.event_type_code IS NULL THEN 'Y'
ELSE 'N' END event_error3-- Event Type is Invalid
,CASE when xe.EVENT_STATUS_CODE NOT IN ('I','N','P','U') THEN 'Y'
ELSE 'N' END event_error4-- Invalid event status Code.
,CASE when xe.PROCESS_STATUS_CODE NOT IN ('D','E','I','P','R','U')
THEN 'Y'
ELSE 'N' END event_error5-- Invalid Process status code
,CASE when xe.event_number < 0 THEN 'Y'
ELSE 'N' END event_error6-- Invalid Event Number
,CASE when xevt.event_class_code IS NULL THEN 'Y'
ELSE 'N' END event_error7-- Event Class Code is Invalid
,CASE when xalb.accounting_class_code IS NULL THEN 'Y'
ELSE 'N' END event_error8
from xla_events xe
, xla_transaction_entities_upg xen
, xla_event_types_b xevt
, xla_acct_line_types_b xalb
, xla_entity_types_b xent
where xen.entity_id(+) = xe.entity_id
and xevt.event_type_code(+) = xe.event_type_code
and xevt.application_id(+) = xe.application_id
and xalb.application_id = xevt.application_id
and xalb.entity_code = xevt.entity_code
and xalb.event_class_code = xevt.event_class_code
and xen.entity_code = xent.entity_code(+)
and xen.application_id = xent.application_id(+)
and (xen.entity_code IS NULL OR
xevt.event_type_code IS NULL OR
xe.EVENT_STATUS_CODE NOT IN ('I','N','P','U') OR
xe.PROCESS_STATUS_CODE NOT IN ('D','E','I','P','R','U') OR
xe.event_number < 0)
and xe.application_id = p_application_id
and xe.upg_source_Application_id = p_upgrading_application_id) xe
,gl_row_multipliers grm
where grm.multiplier < 9
and decode(grm.multiplier,
1,event_error1,
2,event_error2,
3,event_error3,
4,event_error4,
5,event_error5,
6,event_error6,
7,event_error7
,event_error8) = 'Y');
UPDATE xla_events
set upg_valid_flag = CASE upg_valid_flag
WHEN 'E' THEN 'G'
ELSE 'H'
END
where event_id = l_event_id(i);
UPDATE xla_ae_headers
set upg_valid_flag = CASE upg_valid_flag
WHEN 'F' THEN 'I'
ELSE 'J'
END
where event_id = l_event_id(i);
INSERT INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
values( xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_EVT_VERIFICATION_RECORD'
,l_rowcount);
select distinct ae_header_id
from xla_upg_errors
where error_level = 'H'
and application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id;
delete from xla_upg_errors
where application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id
and error_message_name IN ('XLA_UPG_LEDGER_INVALID'
,'XLA_UPG_NO_BUDGET_VER'
,'XLA_UPG_NO_ENC_TYPE'
,'XLA_UPG_BALTYP_INVALID'
,'XLA_UPG_HDR_WO_EVT'
,'XLA_UPG_UNBAL_ACCAMT'
,'XLA_UPG_UNBAL_ENTRAMT'
,'XLA_UPG_HDR_WO_LINES'
,'XLA_HDR_VERIFICATION_RECORD');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name, ae_header_id)
(select
xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'H'
,decode(grm.multiplier,1,'XLA_UPG_LEDGER_INVALID'
,2,'XLA_UPG_NO_BUDGET_VER'
,3,'XLA_UPG_NO_ENC_TYPE'
,4,'XLA_UPG_BALTYP_INVALID'
,'XLA_UPG_HDR_WO_EVT')
,ae_header_id
from ( select ae_header_id
,CASE when gll.ledger_id IS NULL THEN 'Y'
ELSE 'N' END header_error1-- Ledger Id is Invalid
,CASE when xah.BALANCE_TYPE_CODE = 'B'
and xah.BUDGET_VERSION_ID IS NULL THEN 'Y'
ELSE 'N' END header_error2-- No Budget Version
,CASE when xah.BALANCE_TYPE_CODE = 'E'
and xah.ENCUMBRANCE_TYPE_ID IS NULL THEN 'Y'
ELSE 'N' END header_error3-- No Enc Type
,CASE when xah.BALANCE_TYPE_CODE NOT IN ('A','B','E')
THEN 'Y'
ELSE 'N' END header_error4-- Balance type code invalid
,CASE when xe.event_id IS NULL THEN 'Y'
ELSE 'N' END header_error5-- Header without valid event
from xla_ae_headers xah
,gl_ledgers gll
,xla_events xe
where gll.ledger_id (+) = xah.ledger_id
and xe.event_id (+) = xah.event_id
and (gll.ledger_id IS NULL OR
(xah.BALANCE_TYPE_CODE = 'B' AND
xah.BUDGET_VERSION_ID IS NULL) OR
(xah.BALANCE_TYPE_CODE = 'E' AND
xah.ENCUMBRANCE_TYPE_ID IS NULL) OR
xah.BALANCE_TYPE_CODE NOT IN ('A','B','E') OR
xe.event_id IS NULL)
and xah.application_id = p_application_id
and xah.upg_source_application_id = p_upgrading_application_id) xah
,gl_row_multipliers grm
where grm.multiplier < 6
and decode(grm.multiplier,
1,header_error1,
2,header_error2,
3,header_error3,
4,header_error4,
header_error5) = 'Y');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name, ae_header_id)
(select
xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'H'
,decode(grm.multiplier,1,'XLA_UPG_UNBAL_ACCAMT'
,'XLA_UPG_UNBAL_ENTRAMT')
,ae_header_id
from (select /*+ no_merge */ xal.ae_header_id,
case when nvl(sum(accounted_dr), 0) <> nvl(sum(accounted_cr), 0)
then 'Y' else 'N' end header_error1, -- amts not balanced,
case when nvl(sum(entered_dr), 0) <> nvl(sum(entered_cr), 0)
then 'Y' else 'N' end header_error2 -- entered amts not balanced
from xla_ae_lines xal
where xal.application_id = p_application_id
and xal.currency_code <> 'STAT'
and xal.ledger_id in (select gll.ledger_id
from gl_ledgers gll
where gll.suspense_allowed_flag = 'N')
group by xal.ae_header_id
having nvl(sum(accounted_dr), 0)
<> nvl(sum(accounted_cr), 0)
or nvl(sum(entered_dr), 0)
<> nvl(sum(entered_cr), 0)) xal,
gl_row_multipliers grm
where xal.ae_header_id in ( select /*+ use_hash(xah) swap_join_inputs(xah) */
xah.ae_header_id
from xla_ae_headers xah
where xah.application_id = p_application_id
and xah.upg_source_application_id
= p_upgrading_application_id
and xah.balance_type_code <> 'B')
and grm.multiplier < 3
and decode(grm.multiplier, 1, header_error1, header_error2) = 'Y');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id,creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, ae_header_id, error_message_name)
(select xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'H'
,ae_header_id
,'XLA_UPG_HDR_WO_LINES'
from (select xah.ae_header_id
from xla_ae_headers xah
where NOT EXISTS (SELECT xal.ae_header_id
from xla_ae_lines xal
where xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xal.application_id = p_application_id)
and application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id));
UPDATE xla_ae_headers
set upg_valid_flag = CASE upg_valid_flag
WHEN 'F' THEN 'L'
WHEN 'J' THEN 'M'
WHEN 'I' THEN 'N'
ELSE 'K'
END
where ae_header_id = l_header_id(i);
INSERT INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
values(
xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_HDR_VERIFICATION_RECORD'
,l_rowcount);
select distinct ae_header_id
from xla_upg_errors
where error_level = 'L'
and application_id = p_application_id
and upg_Source_application_id = p_upgrading_application_id;
delete from xla_upg_errors
where application_id = p_application_id
and upg_source_application_id = p_upgrading_application_id
and error_message_name IN ('XLA_UPG_CCID_INVALID'
,'XLA_UPG_CCID_SUMACCT'
,'XLA_UPG_CCID_NOBUDGET'
,'XLA_UPG_PARTY_TYP_INVALID'
,'XLA_UPG_DRCR_NULL'
,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
,'XLA_UPG_LINE_NO_HDR'
,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
,'XLA_UPG_PARTY_ID_INVALID'
,'XLA_UPG_PARTY_SITE_INVALID'
,'XLA_LINE_VERIFICATION_RECORD');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, ae_header_id, ae_line_num,error_message_name)
(select
xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'L'
,ae_header_id
,ae_line_num
,decode(grm.multiplier,1,'XLA_UPG_CCID_INVALID'
,2,'XLA_UPG_CCID_SUMACCT'
,3,'XLA_UPG_CCID_NOBUDGET'
,4,'XLA_UPG_PARTY_TYP_INVALID'
,5,'XLA_UPG_DRCR_NULL'
,6,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
,7,'XLA_UPG_LINE_NO_HDR'
,8,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
,9,'XLA_UPG_PARTY_ID_INVALID'
,'XLA_UPG_PARTY_SITE_INVALID')
from ( select xal.ae_header_id
, ae_line_num
, CASE when glcc.CHART_OF_ACCOUNTS_ID IS NULL THEN 'Y'
ELSE 'N' END line_error1-- Invalid Code Combination Id
, CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
and glcc.SUMMARY_FLAG = 'Y' THEN 'Y'
ELSE 'N' END line_error2-- CCID not a Summary Account
, CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
and xah.APPLICATION_ID IS NOT NULL
and xah.BALANCE_TYPE_CODE = 'B'
and glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y' THEN 'Y'
ELSE 'N' END line_error3-- Budgeting not allowed
, CASE when xal.PARTY_TYPE_CODE IS NOT NULL
and xal.PARTY_TYPE_CODE NOT IN ('C','S') THEN 'Y'
ELSE 'N' END line_error4-- Invalid Party Type Code
, CASE when (xal.accounted_dr is NULL AND xal.accounted_cr is NULL)
or (xal.entered_dr is NULL AND xal.entered_cr is NULL)
or (xal.accounted_dr is NOT NULL
AND xal.accounted_cr is NOT NULL)
or (xal.entered_dr is NOT NULL
AND xal.entered_cr is NOT NULL)
THEN 'Y'
ELSE 'N' END line_error5
, CASE when gll.currency_code IS NOT NULL
and xal.currency_code = gll.currency_code
and (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0)
or nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))
THEN 'Y'
ELSE 'N' END line_error6
, CASE when xah.application_id IS NULL THEN 'Y'
ELSE 'N' END line_error7-- Orphan Line.
, CASE when (xal.accounted_dr is NOT NULL and
xal.entered_cr is NOT NULL) or
(xal.accounted_cr is NOT NULL and
xal.entered_dr is NOT NULL) THEN 'Y'
ELSE 'N' END line_error8
,CASE when xal.party_id IS NULL THEN 'Y'
ELSE 'N' END line_error9
, CASE when xal.party_site_id IS NULL
and xal.party_id IS NULL then 'Y'
ELSE 'N' END line_error10
FROM xla_ae_headers xah
, xla_ae_lines xal
, gl_code_combinations glcc
, gl_ledgers gll
, hz_parties hz
, hz_party_sites hps
WHERE glcc.code_combination_id(+) = xal.code_combination_id
AND xah.ae_header_id(+) = xal.ae_header_id
AND gll.ledger_id(+) = xah.ledger_id
AND xal.party_id(+) = hz.party_id
AND xal.party_site_id = hps.party_site_id
AND (glcc.CHART_OF_ACCOUNTS_ID IS NULL OR
(glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
glcc.SUMMARY_FLAG = 'Y' ) OR
(glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
xah.APPLICATION_ID IS NOT NULL AND
xah.BALANCE_TYPE_CODE = 'B' AND
glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y') OR
(xal.PARTY_TYPE_CODE IS NOT NULL AND
xal.PARTY_TYPE_CODE NOT IN ('C','S') ) OR
(xal.accounted_dr is NULL AND xal.accounted_cr is NULL) OR
(xal.entered_dr is NULL AND xal.entered_cr is NULL) OR
(xal.accounted_dr is NOT NULL AND xal.accounted_cr is NOT NULL) OR
(xal.entered_dr is NOT NULL AND xal.entered_cr is NOT NULL) OR
(gll.currency_code IS NOT NULL AND
xal.currency_code = gll.currency_code AND
(nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0) OR
nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))) OR
((xal.accounted_dr is NOT NULL and xal.entered_cr is NOT NULL) OR
(xal.accounted_cr is NOT NULL and xal.entered_dr is NOT NULL)) OR
(xah.application_id IS NULL))
and xal.application_id = p_application_id
and xah.upg_source_application_id = p_upgrading_application_id) xal
,gl_row_multipliers grm
where grm.multiplier < 11
and decode (grm.multiplier,1,line_error1
,2,line_error2
,3,line_error3
,4,line_error4
,5,line_error5
,6,line_error6
,7,line_error7
,8,line_error8
,9,line_error9
,line_error10) = 'Y');
UPDATE xla_ae_headers
set upg_valid_flag = CASE upg_valid_flag
WHEN 'F' THEN 'P'
WHEN 'J' THEN 'Q'
WHEN 'I' THEN 'R'
WHEN 'L' THEN 'S'
WHEN 'M' THEN 'T'
WHEN 'N' THEN 'U'
ELSE 'O'
END
where ae_header_id = l_header_id(i)
and application_id = p_application_id
and UPG_SOURCE_APPLICATION_ID = p_upgrading_application_id;
INSERT INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
values(
xla_upg_errors_s.nextval
,g_application_id
,g_source_application_id
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_LINE_VERIFICATION_RECORD'
,l_rowcount);
select distinct ae_header_id
from xla_upg_errors
where error_level = 'D'
and application_id = p_application_id;
delete from xla_upg_errors
where application_id = p_application_id
and error_message_name IN ('XLA_UPG_LINK_NO_LINE'
,'XLA_DIST_VERIFICATION_RECORD');
INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id,creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, ae_header_id, ae_line_num, temp_line_num
, error_message_name)
(select
xla_upg_errors_s.nextval
,g_application_id
,602
,sysdate
,-1
,sysdate
,-1
,-9999
, 'D'
,ae_header_id
,ae_line_num
,temp_line_num
,'XLA_UPG_LINK_NO_LINE'
from (select xdl.ae_header_id, xdl.ae_line_num,xdl.temp_line_num
from xla_distribution_links xdl
where not exists (SELECT xal.ae_header_id, xal.ae_line_num
from xla_ae_lines xal
where xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xal.application_id = p_application_id)
and xdl.application_id = p_application_id));
UPDATE xla_ae_headers
set upg_valid_flag = CASE upg_valid_flag
WHEN 'P' THEN 'W'
WHEN 'Q' THEN 'X'
WHEN 'R' THEN 'Y'
WHEN 'F' THEN 'Z'
WHEN 'J' THEN '1'
WHEN 'I' THEN '2'
WHEN 'L' THEN '3'
WHEN 'M' THEN '4'
WHEN 'N' THEN '5'
WHEN 'S' THEN '6'
WHEN 'T' THEN '7'
WHEN 'U' THEN '8'
ELSE 'V'
END
where ae_header_id = l_header_id(i)
and application_id = p_application_id;
INSERT INTO XLA_UPG_ERRORS
(upg_error_id, application_id, upg_source_application_id, creation_date
, created_by, last_update_date, last_updated_by, upg_batch_id
, error_level, error_message_name,entity_id)
values(
xla_upg_errors_s.nextval
,g_application_id
,602
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_DIST_VERIFICATION_RECORD'
,l_rowcount);
select xal.ae_header_id, decode(gll.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), count(*)
from xla_ae_lines xal,
xla_ae_headers xah,
gl_ledgers gll,
gl_code_combinations ccid
where gll.ledger_id = xah.ledger_id
and xah.application_id = p_application_id
and xah.ae_header_id = xal.ae_header_id
and xal.application_id = p_application_id
and ccid.code_combination_id = xal.code_combination_id
GROUP BY xal.ae_header_id, decode(gll.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);
select xal.ae_header_id, decode(gll.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), count(*)
from xla_ae_lines xal,
xla_ae_headers xah,
gl_ledgers gll,
gl_code_combinations ccid
where gll.ledger_id = xah.ledger_id
and xah.application_id = p_application_id
and xah.ae_header_id = xal.ae_header_id
and xal.application_id = p_application_id
and ccid.code_combination_id = xal.code_combination_id
and gll.mgt_seg_column_name IS NOT NULL
GROUP BY xal.ae_header_id, decode(gll.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);
INSERT INTO xla_ae_segment_values
(ae_header_id, segment_type_code, segment_value, ae_lines_count,
upg_batch_id)
values (
l_header_id(i)
,C_BAL_SEGMENT
,l_seg_value(i)
,l_line_count(i)
,-9999);
INSERT INTO xla_ae_segment_values
(ae_header_id, segment_type_code, segment_value, ae_lines_count,
upg_batch_id)
values (
l_header_id(i)
,C_MGT_SEGMENT
,l_seg_value(i)
,l_line_count(i)
,-9999);