[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. |
| 22-JUL-2009 VGOPISET Bug 8717476 Enabled Procedures SET_STATUS_CODE |
| and added procedures: UPDATE_UPG_REQUEST_STATUS|
| and RESET_PERIOD_STATUSES. |
| 24-AUG-2009 VGOPISET Bug 8834301 Resetting the Periods to NULL from |
| PENDING when EXCEPTION is raised by Product API|
| 10-Mar-2011 11854401 Change the Last_Updated_By from |
| -601 to 2 for Downtime |
| -602 to 3 for Hotpatch in GL_PERIOD_STATUSES |
+===========================================================================*/
--=============================================================================
-- **************** declarations ********************
--=============================================================================
-------------------------------------------------------------------------------
-- declaring global variables
-------------------------------------------------------------------------------
g_batch_id INTEGER ;
PROCEDURE Update_upg_request_status
(p_application_id IN NUMBER,
p_status_code IN VARCHAR2)
IS
l_log_module VARCHAR2(240);
||'.update_upg_request_status';
Trace('update_upg_request_status.Begin',c_level_statement,
l_log_module);
Trace('Status being Updated for Application: '
||p_application_id
||' is: '
||p_status_code,c_level_statement,l_log_module);
UPDATE xla_upgrade_requests
SET status_code = p_status_code,
last_update_date = SYSDATE
WHERE application_id = p_application_id
AND program_code = 'ONDEMAND UPGRADE'
AND status_code <> C_SUCCESS_STATUS;
xla_exceptions_pkg.Raise_message(p_location => 'XLA_UPGRADE_PUB.update_upg_request_status');
END update_upg_request_status;
UPDATE gl_period_statuses gps
SET migration_status_code = NULL
WHERE gps.migration_status_code = 'P'
AND gps.application_id IN (275, 8721)
AND gps.adjustment_period_flag = 'N'
-- AND gps.closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
AND gps.ledger_id = g_array_ledger_id(i) ;
fnd_file.put_line(fnd_file.log, '*Migration status code Updated to NULL for ledger_id : '|| g_array_ledger_id(i)
|| ' are : '|| to_char(SQL%ROWCOUNT));
UPDATE gl_period_statuses gps
SET migration_status_code = NULL
WHERE gps.migration_status_code = 'P'
AND gps.application_id = p_application_id
AND gps.adjustment_period_flag = 'N'
-- AND gps.closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
AND gps.ledger_id = g_array_ledger_id(i);
fnd_file.Put_line(fnd_file.LOG,'*Migration status code Updated to NULL for ledger_id : '
||G_array_ledger_id(i)
||' are : '
||To_char(SQL%ROWCOUNT));
| 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_errbuf 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;
SELECT gps.ledger_id ledger_id,
Min(gps.start_date) last_date
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'U'
AND gps.application_id IN (275,8721)
AND gps.ledger_id IN (SELECT l.ledger_id
FROM gl_ledgers l
WHERE l.ledger_id IN (SELECT DISTINCT target_ledger_id
FROM gl_ledger_relationships glr
WHERE glr.primary_ledger_id = i_ledger_id
AND glr.application_id = 101
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
AND glr.relationship_type_code = 'SUBLEDGER')
OR (glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE')))
AND Nvl(l.complete_flag,'Y') = 'Y')
GROUP BY gps.ledger_id;
SELECT gps.ledger_id ledger_id,
Min(start_date) last_date
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'U'
AND gps.application_id = i_application_id
AND gps.ledger_id IN (SELECT l.ledger_id
FROM gl_ledgers l
WHERE l.ledger_id IN (SELECT DISTINCT target_ledger_id
FROM gl_ledger_relationships glr
WHERE glr.primary_ledger_id = i_ledger_id
AND glr.application_id = 101
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
AND glr.relationship_type_code = 'SUBLEDGER')
OR (glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE')))
AND Nvl(l.complete_flag,'Y') = 'Y')
GROUP BY gps.ledger_id;
SELECT application_name
INTO l_application_name
FROM fnd_application_vl v
WHERE v.application_id = p_application_id;
SELECT Count(1)
INTO l_program_running
FROM fnd_concurrent_requests fcr
WHERE (fcr.program_application_id,fcr.concurrent_program_id) IN (SELECT fcp.application_id,
fcp.concurrent_program_id
FROM fnd_concurrent_programs fcp
WHERE fcp.application_id = 602
AND fcp.concurrent_program_name = 'XLAONDEUPG')
AND fcr.phase_code = 'R';
SELECT status_code
INTO l_prev_run_status
FROM xla_upgrade_requests
WHERE application_id = p_application_id
AND program_code = 'ONDEMAND UPGRADE';
SELECT Count(1)
INTO l_hotpatch_running
FROM xla_upgrade_requests
WHERE application_id = 602
AND status_code IN (C_INITIAL_STATUS , C_PROGRESS_STATUS) ;
SELECT target_ledger_id
BULK COLLECT INTO g_array_ledger_id
FROM gl_ledger_relationships glr
WHERE glr.application_id = 101
AND glr.primary_ledger_id = p_ledger_id
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
AND glr.relationship_type_code = 'SUBLEDGER')
OR (glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE'));
SELECT gps.start_date
INTO l_start_date
FROM gl_period_statuses gps
WHERE gps.application_id IN (275, 8721)
AND gps.ledger_id = p_ledger_id
AND gps.period_name = p_period_name;
SELECT min(gps.start_date)
INTO l_end_date
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'U'
AND gps.ledger_id = p_ledger_id
AND gps.application_id IN (275, 8721) ;
select distinct gps.period_name
into l_upgraded_period_name
from gl_period_statuses gps
WHERE gps.migration_status_code = 'U'
AND gps.ledger_id = p_ledger_id
AND gps.start_date = l_end_date
AND gps.application_id IN (275, 8721) ;
SELECT count(*)
INTO l_pending_periods
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'P'
AND gps.application_id IN (275, 8721)
AND gps.ledger_id IN ( SELECT l.ledger_id
FROM gl_ledgers l
WHERE l.ledger_id IN (SELECT DISTINCT glr.target_ledger_id
FROM gl_ledger_relationships glr
WHERE glr.primary_ledger_id = p_ledger_id
AND glr.application_id = 101
AND (( glr.target_ledger_category_code IN ('SECONDARY' , 'ALC')
AND glr.relationship_type_code = 'SUBLEDGER' )
OR
( glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE' )
)
)
AND nvl(l.complete_flag,'Y') = 'Y' ) ;
SELECT gps.start_date
INTO l_start_date
FROM gl_period_statuses gps
WHERE gps.application_id = l_application_id
AND gps.ledger_id = p_ledger_id
AND gps.period_name = p_period_name;
SELECT Min(gps.start_date)
INTO l_end_date
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'U'
AND gps.ledger_id = p_ledger_id
AND gps.application_id = l_application_id;
select gps.period_name
into l_upgraded_period_name
from gl_period_statuses gps
WHERE gps.migration_status_code = 'U'
AND gps.ledger_id = p_ledger_id
AND gps.start_date = l_end_date
AND gps.application_id = l_application_id ;
SELECT Count(*)
INTO l_pending_periods
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'P'
AND gps.application_id = l_application_id
AND gps.ledger_id IN (SELECT l.ledger_id
FROM gl_ledgers l
WHERE l.ledger_id IN (SELECT DISTINCT glr.target_ledger_id
FROM gl_ledger_relationships glr
WHERE glr.primary_ledger_id = p_ledger_id
AND glr.application_id = 101
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
AND glr.relationship_type_code = 'SUBLEDGER')
OR (glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE')))
AND Nvl(l.complete_flag,'Y') = 'Y');
SELECT xur.ledger_id,
xur.start_date,
xur.end_date,
xur.workers_num,
xur.batch_size,
xur.period_name
INTO l_upg_ledger_id,
l_upg_start_date,
l_upg_end_date,
l_upg_number_of_workers,
l_upg_batch_size,
l_upg_period_name
FROM xla_upgrade_requests xur
WHERE xur.application_id = p_application_id
AND xur.program_code = 'ONDEMAND UPGRADE';
SELECT l.name
INTO l_upg_ledger_name
FROM gl_ledgers l
WHERE l.ledger_id = l_upg_ledger_id ;
INSERT INTO xla_upgrade_requests
(application_id,
request_control_id,
status_code,
phase_num,
ledger_id,
order_num,
creation_date,
created_by,
last_update_date,
last_updated_by,
program_code)
VALUES (p_application_id,
0,
C_INITIAL_STATUS ,
p_application_id,
p_ledger_id,
p_application_id,
SYSDATE,
-169,
SYSDATE,
-169,
'ONDEMAND UPGRADE');
Trace('Inserted a row into XLA_UPGRADE_REQUESTS for application: '||p_application_id,c_level_statement,l_log_module);
SELECT Count(1)
INTO l_program_running
FROM fnd_concurrent_requests fcr
WHERE (fcr.program_application_id,fcr.concurrent_program_id) IN (SELECT fcp.application_id,
fcp.concurrent_program_id
FROM fnd_concurrent_programs fcp
WHERE fcp.application_id = 602
AND fcp.concurrent_program_name = 'XLAONDEUPG')
AND fcr.phase_code = 'R';
/* Update the data for the current run */
UPDATE xla_upgrade_requests
SET status_code = C_PROGRESS_STATUS,
request_control_id = xla_upgrade_requests_s.nextval,
batch_size = p_batch_size,
workers_num = p_number_of_workers,
period_name = p_period_name,
start_date = l_start_date,
end_date = l_end_date - 1,
ledger_id = p_ledger_id,
last_update_date = SYSDATE ,
last_updated_by = -169
WHERE application_id = p_application_id
AND program_code = 'ONDEMAND UPGRADE';
UPDATE gl_period_statuses
SET migration_status_code = 'P'
,last_update_date = SYSDATE
,last_updated_by = 3 -- -169 changed to 3 for bug11854401
-- ,last_update_login = 3 -- -169 changed to 3 for bug11854401
WHERE ledger_id = i_ledger_periods.ledger_id
AND ( end_date >= l_start_date
and end_date < i_ledger_periods.last_date)
AND application_id IN (275, 8721)
AND adjustment_period_flag = 'N'
-- AND closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
AND migration_status_code IS NULL;
fnd_file.put_line(fnd_file.log, '*Periods updated to P for ledger_id: '
|| i_ledger_periods.ledger_id || ' are : '|| to_char(SQL%ROWCOUNT));
UPDATE gl_period_statuses
SET migration_status_code = 'P'
,last_update_date = SYSDATE
,last_updated_by = 3 -- -169 changed to 3 for bug11854401
-- ,last_update_login = 3 -- -169 changed to 3 for bug11854401
WHERE application_id = l_application_id
AND ledger_id = i_ledger_periods.ledger_id
AND (end_date >= l_start_date
AND end_date < i_ledger_periods.last_date)
AND adjustment_period_flag = 'N'
-- AND closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
AND migration_status_code IS NULL;
fnd_file.Put_line(fnd_file.LOG,'*Periods updated to P for ledger_id: ' ||i_ledger_periods.ledger_id
||' are : ' ||To_char(SQL%ROWCOUNT));
DELETE FROM xla_upgrade_dates;
INSERT INTO xla_upgrade_dates
(ledger_id
,start_date
,end_date)
SELECT gps.ledger_id
,min(start_date)
,max(end_date)
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'P'
AND gps.application_id IN (275, 8721)
AND gps.ledger_id = v_array_ledger_id(i)
GROUP BY gps.ledger_id ;
INSERT INTO xla_upgrade_dates
(ledger_id,
start_date,
end_date)
SELECT gps.ledger_id,
Min(start_date),
Max(end_date)
FROM gl_period_statuses gps
WHERE gps.migration_status_code = 'P'
AND gps.application_id = l_application_id
AND gps.ledger_id = G_array_ledger_id(i)
GROUP BY gps.ledger_id;
zx_on_demand_trx_upgrade_pkg.Zx_trx_update_mgr(
x_errbuf => l_error_buf,
x_retcode => l_retcode,
x_batch_size => l_batch_size,
x_num_workers => l_number_of_workers,
p_application_id => l_application_id,
p_ledger_id => l_ledger_id ,
p_period_name => l_period_name);
zx_on_demand_trx_upgrade_pkg.Zx_trx_update_mgr(
x_errbuf => l_error_buf,
x_retcode => l_retcode,
x_batch_size => l_batch_size,
x_num_workers => l_number_of_workers,
p_application_id => l_application_id,
p_ledger_id => l_ledger_id ,
p_period_name => l_period_name );
CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper (
X_errbuf => l_error_buf ,
X_retcode => l_retcode_char ,
X_batch_size => l_batch_size ,
X_Num_Workers => l_number_of_workers ,
X_ledger_id => p_ledger_id ,
X_application_id => l_application_id ) ;
zx_on_demand_trx_upgrade_pkg.Zx_trx_update_mgr( x_errbuf => l_error_buf,
x_retcode => l_retcode,
x_batch_size => l_batch_size,
x_num_workers => l_number_of_workers,
p_application_id => l_application_id,
p_ledger_id => l_ledger_id ,
p_period_name => l_period_name);
CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper (
X_errbuf => l_error_buf ,
X_retcode => l_retcode_char ,
X_batch_size => l_batch_size ,
X_Num_Workers => l_number_of_workers ,
X_ledger_id => p_ledger_id ,
X_application_id => l_application_id ) ;
Update_upg_request_status(p_application_id,C_SUCCESS_STATUS );
SELECT je_source_name
INTO l_source_name
FROM xla_subledgers
WHERE application_id = p_application_id;
UPDATE gl_je_headers a
SET a.je_from_sla_flag = decode(a.reversed_je_header_id,null,'U','N') ,
a.je_source = Decode(a.je_source,'Inventory','Cost Management',
'Purchasing','Cost Management',
je_source),
a.last_update_date = SYSDATE,
a.last_updated_by = 3 -- -169, changed to 3 for bug11854401
-- ,a.last_update_login = 3 -- -169 changed to 3 for bug11854401
WHERE (Decode(a.je_source,'Receivables',222,
'Assets',101,
'Inventory',401,
'Purchasing',201,
-101),ledger_id,period_name) IN (SELECT gps.application_id,
gps.ledger_id,
gps.period_name
FROM gl_period_statuses gps
WHERE gps.end_date >= l_start_date
AND gps.end_date < l_end_date
AND gps.ledger_id = G_array_ledger_id(i)
AND gps.application_id = l_application_id
AND gps.migration_status_code = 'U')
AND a.je_from_sla_flag IS NULL
AND a.je_source <> 'Project Accounting'
AND a.actual_flag = 'A'
AND EXISTS (SELECT 1
FROM xla_subledgers xsu
WHERE xsu.je_source_name = a.je_source);
fnd_file.Put_line(fnd_file.LOG,'*Flags updated to U : '||To_char(SQL%ROWCOUNT));
Trace('Updated gl_je_headers',c_level_statement,l_log_module);
Update_upg_request_status(p_application_id,C_ERROR_STATUS);
Update_upg_request_status(p_application_id,C_ERROR_STATUS);
Update_upg_request_status(p_application_id,C_ERROR_STATUS);
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);