[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| FND_APPLICATIONS_VL, and use it to select the lines to update in |
| GL_JE_HEADERS. |
| 17-Aug-2006 Jorge Larre Bug 5468416: Add a parameter of type VARCHAR2 |
| to call the Costing upgrade program. |
| 24-Aug-2006 Jorge Larre Bug 5473838: when calling the Costing upgrade |
| program, X_init_msg_list must be passed the value FND_API.G_FALSE. |
| 05-SEP-2006 Jorge Larre Bug 5484337: AR needs to store the calling |
| parameters in a new table (XLA_UPGRADE_REQUESTS). Add ledger_id and |
| period_name as calling parameters in set_status_code. |
| 07-NOV-2006 Jorge Larre Bug 5648571: Obsolete the procedure |
| set_status_code. This change is to be in sync with xlaugupg.pkh. |
| The code is left commented in case we decide to use it again. |
+===========================================================================*/
--=============================================================================
-- **************** declarations ********************
--=============================================================================
-------------------------------------------------------------------------------
-- declaring global variables
-------------------------------------------------------------------------------
g_batch_id INTEGER ;
| Insert_Line_Criteria |
| |
| This routine is called to insert line criteria. |
| |
+============================================================================*/
PROCEDURE Insert_Line_Criteria (
p_batch_id IN NUMBER
, p_batch_size IN NUMBER
, p_application_id IN NUMBER
, p_error_detected OUT NOCOPY BOOLEAN
, p_overwrite_flag IN BOOLEAN)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.Insert_Line_Criteria';
(p_msg => 'BEGIN of procedure Insert_Line_Criteria'
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
SAVEPOINT before_insert_criteria;
delete xla_ae_line_details xal
where (ae_header_id, ae_line_num) IN
(select xlgt.ae_header_id,ae_line_num
from xla_upg_line_criteria_gt xlgt
where xal.ae_header_id = xlgt.ae_header_id
and xal.ae_line_num = xlgt.ae_line_num);
update xla_upg_line_criteria_gt xlgt
set error_message_name = 'XLA_UPG_INVALID_CRITERIA'
where NOT EXISTS
(select 1
from xla_analytical_hdrs_b xanh
where xanh.amb_context_code = 'DEFAULT'
and xanh.analytical_criterion_code = xlgt.analytical_criterion_code
and xanh.analytical_criterion_type_code = xlgt.analytical_criterion_type_code);
INSERT INTO xla_analytical_dtl_vals
(
analytical_detail_value_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,analytical_detail_char_1
,analytical_detail_char_2
,analytical_detail_char_3
,analytical_detail_char_4
,analytical_detail_char_5
,analytical_detail_date_1
,analytical_detail_date_2
,analytical_detail_date_3
,analytical_detail_date_4
,analytical_detail_date_5
,analytical_detail_number_1
,analytical_detail_number_2
,analytical_detail_number_3
,analytical_detail_number_4
,analytical_detail_number_5
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
)
SELECT xla_analytical_dtl_vals_s.nextval
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,analytical_detail_char_1
,analytical_detail_char_2
,analytical_detail_char_3
,analytical_detail_char_4
,analytical_detail_char_5
,analytical_detail_date_1
,analytical_detail_date_2
,analytical_detail_date_3
,analytical_detail_date_4
,analytical_detail_date_5
,analytical_detail_number_1
,analytical_detail_number_2
,analytical_detail_number_3
,analytical_detail_number_4
,analytical_detail_number_5
,sysdate
,-1
,sysdate
,-1
,-1
FROM ( SELECT
DISTINCT
analytical_criterion_code
,analytical_criterion_type_code
,'DEFAULT' amb_context_code
,analytical_detail_char_1
,analytical_detail_char_2
,analytical_detail_char_3
,analytical_detail_char_4
,analytical_detail_char_5
,analytical_detail_date_1
,analytical_detail_date_2
,analytical_detail_date_3
,analytical_detail_date_4
,analytical_detail_date_5
,analytical_detail_number_1
,analytical_detail_number_2
,analytical_detail_number_3
,analytical_detail_number_4
,analytical_detail_number_5
FROM
XLA_UPG_LINE_CRITERIA_GT
WHERE ERROR_MESSAGE_NAME IS NOT NULL
) adv1
WHERE NOT exists ( SELECT 'x'
FROM xla_analytical_dtl_vals adv2
WHERE adv1.analytical_criterion_code = adv2.analytical_criterion_code
AND adv1.analytical_criterion_type_code = adv2.analytical_criterion_type_code
AND adv1.amb_context_code = adv2.amb_context_code
--Detail 1
AND NVL( adv1.analytical_detail_char_1
,NVL( TO_CHAR( adv1.analytical_detail_date_1
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv1.analytical_detail_number_1
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv2.analytical_detail_char_1
,NVL( TO_CHAR( adv2.analytical_detail_date_1
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv2.analytical_detail_number_1
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 2
AND NVL( adv1.analytical_detail_char_2
,NVL( TO_CHAR( adv1.analytical_detail_date_2
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv1.analytical_detail_number_2
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv2.analytical_detail_char_2
,NVL( TO_CHAR( adv2.analytical_detail_date_2
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv2.analytical_detail_number_2
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 3
AND NVL( adv1.analytical_detail_char_3
,NVL( TO_CHAR( adv1.analytical_detail_date_3
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv1.analytical_detail_number_3
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv2.analytical_detail_char_3
,NVL( TO_CHAR( adv2.analytical_detail_date_3
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv2.analytical_detail_number_3
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 4
AND NVL( adv1.analytical_detail_char_4
,NVL( TO_CHAR( adv1.analytical_detail_date_4
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv1.analytical_detail_number_4
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv2.analytical_detail_char_4
,NVL( TO_CHAR( adv2.analytical_detail_date_4
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv2.analytical_detail_number_4
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 5
AND NVL( adv1.analytical_detail_char_5
,NVL( TO_CHAR( adv1.analytical_detail_date_5
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv1.analytical_detail_number_5
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv2.analytical_detail_char_5
,NVL( TO_CHAR( adv2.analytical_detail_date_5
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv2.analytical_detail_number_5
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
);
INSERT INTO XLA_AE_LINE_DETAILS
(
ae_header_id
, ae_line_num
, analytical_detail_value_id
)
SELECT adv.analytical_detail_value_id
,alcg.ae_header_id
,alcg.ae_line_num
FROM
XLA_UPG_LINE_CRITERIA_GT alcg, xla_analytical_dtl_vals adv
WHERE --Detail 1
NVL( alcg.analytical_detail_char_1
,NVL( TO_CHAR( alcg.analytical_detail_date_1
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( alcg.analytical_detail_number_1
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv.analytical_detail_char_1
,NVL( TO_CHAR( adv.analytical_detail_date_1
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv.analytical_detail_number_1
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 2
AND NVL( alcg.analytical_detail_char_2
,NVL( TO_CHAR( alcg.analytical_detail_date_2
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( alcg.analytical_detail_number_2
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv.analytical_detail_char_2
,NVL( TO_CHAR( adv.analytical_detail_date_2
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv.analytical_detail_number_2
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 3
AND NVL( alcg.analytical_detail_char_3
,NVL( TO_CHAR( alcg.analytical_detail_date_3
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( alcg.analytical_detail_number_3
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv.analytical_detail_char_3
,NVL( TO_CHAR( adv.analytical_detail_date_3
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv.analytical_detail_number_3
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 4
AND NVL( alcg.analytical_detail_char_4
,NVL( TO_CHAR( alcg.analytical_detail_date_4
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( alcg.analytical_detail_number_4
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv.analytical_detail_char_4
,NVL( TO_CHAR( adv.analytical_detail_date_4
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv.analytical_detail_number_4
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 5
AND NVL( alcg.analytical_detail_char_5
,NVL( TO_CHAR( alcg.analytical_detail_date_5
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( alcg.analytical_detail_number_5
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( adv.analytical_detail_char_5
,NVL( TO_CHAR( adv.analytical_detail_date_5
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( adv.analytical_detail_number_5
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
);
(p_msg => 'END of procedure Insert_Line_Criteria'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
ROLLBACK to SAVEPOINT before_insert_criteria;
ROLLBACK to SAVEPOINT before_insert_criteria;
END Insert_Line_Criteria;
select entity_id
from xla_upg_errors
where error_level = 'N'
and upg_batch_id = g_batch_id;
select event_id
from xla_upg_errors
where error_level = 'E'
and upg_batch_id = g_batch_id;
select distinct ae_header_id
from xla_upg_errors
where error_level IN ('H','L','D')
and upg_batch_id = g_batch_id;
select upg_error_id
from xla_upg_errors
where upg_batch_id = g_batch_id;
select ae_header_id, segment_type_code
from xla_ae_segment_values
where upg_batch_id = g_batch_id;
update xla_transaction_entities_upg
set upg_valid_flag = null
where entity_id = l_entity_id(i);
update xla_events
set upg_valid_flag = null
where event_id = l_event_id(i);
update xla_ae_headers
set upg_valid_flag = null
where ae_header_id = l_header_id(i)
and application_id = g_application_id;
delete xla_upg_errors
where upg_error_id = l_error_id(i);
delete xla_ae_segment_values
where ae_header_id = l_header_id(i)
and segment_type_code = l_seg_type(i);
update gl_period_statuses
set migration_status_code = 'U'
where application_id = l_application_id
and migration_status_code = 'P';
update gl_period_statuses
set migration_status_code = 'U'
where period_year = l_period_year
and migration_status_code = 'P'
and application_id = l_application_id;
update gl_period_statuses
set migration_status_code = 'U'
where period_name = l_period_name
and migration_status_code = 'P'
and application_id = l_application_id;
update gl_period_statuses
set migration_status_code = 'U'
where period_year = l_period_year
and period_name = l_period_name
and migration_status_code = 'P'
and application_id = l_application_id;
update gl_period_statuses
set migration_status_code = 'U'
where application_id = l_application_id
and migration_status_code = 'P'
and ledger_id = l_set_of_books_id;
update gl_period_statuses
set migration_status_code = 'U'
where period_year = l_period_year
and migration_status_code = 'P'
and ledger_id = l_set_of_books_id
and application_id = l_application_id;
update gl_period_statuses
set migration_status_code = 'U'
where period_name = l_period_name
and migration_status_code = 'P'
and ledger_id = l_set_of_books_id
and application_id = l_application_id;
update gl_period_statuses
set migration_status_code = 'U'
where period_year = l_period_year
and period_name = l_period_name
and migration_status_code = 'P'
and ledger_id = l_set_of_books_id
and application_id = l_application_id;
| This procedure is called during the Upgrade On-Demand, to update the |
| status code, and also to call the product team hooks. |
+============================================================================*/
--PROCEDURE set_status_code
--(p_error_buf OUT NOCOPY VARCHAR2,
-- p_retcode OUT NOCOPY NUMBER,
-- p_application_id IN NUMBER,
-- p_ledger_id IN NUMBER,
-- p_period_name IN VARCHAR2,
-- p_number_of_workers IN NUMBER,
-- p_batch_size IN NUMBER) IS
--l_application_id NUMBER;
delete from xla_upg_errors
where application_id = p_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_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'
,'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
,-9999
,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.ae_header_id = p_header_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
,-9999
,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.ae_header_id = p_header_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.ae_header_id = p_header_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
,-9999
,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 xal.ae_header_id = p_header_id)
and application_id = p_application_id
and ae_header_id = p_header_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 = p_header_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
,-9999
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_HDR_VERIFICATION_RECORD'
,l_rowcount);
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
,-9999
,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 xal.ae_header_id = p_header_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 = p_header_id
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
,-9999
,sysdate
,-1
,sysdate
,-1
,-9999
, 'V'
,'XLA_LINE_VERIFICATION_RECORD'
,l_rowcount);
| This procedure is called during the Pre Upgrade phase, to update the |
| status code. |
+============================================================================*/
PROCEDURE pre_upgrade_set_status_code
(p_error_buf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_migrate_all_ledgers IN VARCHAR2,
p_dummy_parameter IN VARCHAR2,
p_ledger_id IN NUMBER DEFAULT NULL,
p_start_date IN VARCHAR2
) IS
CURSOR CUR_ALL_LEDGERS IS SELECT DISTINCT ledger_id
FROM gl_period_statuses;
SELECT min(start_date) - 1
INTO l_end_date
FROM gl_period_statuses
WHERE migration_status_code = 'P'
AND ledger_id = l_ledger_id
AND application_id in (200,222,275,201,401,101,8721);
SELECT max(end_date)
INTO l_end_date
FROM gl_period_statuses
WHERE ledger_id = l_ledger_id
AND application_id IN (200,222,275,201,401,101,8721);
UPDATE gl_period_statuses
SET migration_status_code = 'P'
WHERE ledger_id = l_ledger_id
AND (start_date >= l_start_date
and end_date <= l_end_date)
AND application_id in (200,222,275,201,401,101,8721)
AND adjustment_period_flag = 'N'
AND migration_status_code IS NULL;
SELECT min(start_date) - 1
INTO l_end_date
FROM gl_period_statuses
WHERE migration_status_code = 'P'
AND ledger_id = l_all_ledgers.ledger_id
AND application_id in (200,222,275,201,401,101,8721);
SELECT max(end_date)
INTO l_end_date
FROM gl_period_statuses
WHERE ledger_id = l_all_ledgers.ledger_id
AND application_id in (200,222,275,201,401,101,8721);
UPDATE gl_period_statuses
SET migration_status_code = 'P'
WHERE ledger_id = l_all_ledgers.ledger_id
AND (start_date >= l_start_date
and end_date <= l_end_date)
AND application_id in (200,222,275,201,401,101,8721)
AND adjustment_period_flag = 'N'
AND migration_status_code IS NULL;
trace('Updated gl_period_statuses.'
, C_LEVEL_STATEMENT, l_Log_module);