[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 18-SEP-03 A. Quaglia insert_line_detail: delete dummy line |
| detail before inserting the detail |
| delete_line_detail: created. It all |
| details removed for a line, the dummy line|
| detail is inserted. |
| 27-JAN-03 A. Quaglia bug3402449: removed previous changes |
| delete_line_detail left since |
| the code is cleaner. |
| Changed trace handling as per Sandeep's |
| code. |
| 12-Feb-04 Shishir Joshi Replaced hh24miss with HH24MISS to improve|
| the performance. |
| 26-MAR-04 A.Quaglia Fixed debug changes issues: |
| -Replaced global variable for trace |
| with local one |
| -Fixed issue with SQL%ROWCOUNT which is |
| modified after calling debug proc |
| 14-APR-04 A.Quaglia Performance changes in get_detail_value_id|
| Removed hardcoded APPS in |
| build_criteria_view |
| 30-JUN-05 W.Chan Fix bug 4299125 - Modify compile_criteria |
| to use the same view_column for details of|
| the same amb context. Modify |
| build_criteria_views not to use amb |
| context when building the views |
| 01-SEP-05 W.Chan Fix bug 4583524 - Fix |
| get_view_column_number to ignore rows |
| NULL is in the view_column_num |
| |
+======================================================================*/
--
-- Private exceptions
--
le_resource_busy EXCEPTION;
FUNCTION insert_detail_value
( p_anacri_code IN VARCHAR2
,p_anacri_type_code IN VARCHAR2
,p_amb_context_code IN VARCHAR2
,p_detail_char_1 IN VARCHAR2 DEFAULT NULL
,p_detail_char_2 IN VARCHAR2 DEFAULT NULL
,p_detail_char_3 IN VARCHAR2 DEFAULT NULL
,p_detail_char_4 IN VARCHAR2 DEFAULT NULL
,p_detail_char_5 IN VARCHAR2 DEFAULT NULL
,p_detail_date_1 IN DATE DEFAULT NULL
,p_detail_date_2 IN DATE DEFAULT NULL
,p_detail_date_3 IN DATE DEFAULT NULL
,p_detail_date_4 IN DATE DEFAULT NULL
,p_detail_date_5 IN DATE DEFAULT NULL
,p_detail_number_1 IN NUMBER DEFAULT NULL
,p_detail_number_2 IN NUMBER DEFAULT NULL
,p_detail_number_3 IN NUMBER DEFAULT NULL
,p_detail_number_4 IN NUMBER DEFAULT NULL
,p_detail_number_5 IN NUMBER DEFAULT NULL
,p_detail_char_id IN INTEGER DEFAULT NULL
) RETURN INTEGER
IS
/*======================================================================+
| |
| Private Procedure |
| |
| Description |
| ----------- |
| Inserts a new record in the table xla_analytical_dtl_vals. |
| |
+======================================================================*/
l_detail_value_id INTEGER;
l_log_module := C_DEFAULT_MODULE||'.insert_detail_value';
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
)
VALUES
( xla_analytical_dtl_vals_s.nextval
,p_anacri_code
,p_anacri_type_code
,p_amb_context_code
,p_detail_char_1
,p_detail_char_2
,p_detail_char_3
,p_detail_char_4
,p_detail_char_5
,p_detail_date_1
,p_detail_date_2
,p_detail_date_3
,p_detail_date_4
,p_detail_date_5
,p_detail_number_1
,p_detail_number_2
,p_detail_number_3
,p_detail_number_4
,p_detail_number_5
,g_date
,g_user_id
,g_date
,g_user_id
,g_login_id
)
RETURNING analytical_detail_value_id
INTO l_detail_value_id;
' row(s) inserted into xla_analytical_dtl_vals '
,p_level => C_LEVEL_STATEMENT);
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
)
VALUES
( l_detail_value_id
,p_anacri_code
,p_anacri_type_code
,p_amb_context_code
,p_detail_char_1
,p_detail_char_2
,p_detail_char_3
,p_detail_char_4
,p_detail_char_5
,p_detail_date_1
,p_detail_date_2
,p_detail_date_3
,p_detail_date_4
,p_detail_date_5
,p_detail_number_1
,p_detail_number_2
,p_detail_number_3
,p_detail_number_4
,p_detail_number_5
,g_date
,g_user_id
,g_date
,g_user_id
,g_login_id
);
|| ' row(s) inserted into xla_analytical_dtl_vals '
,p_level => C_LEVEL_STATEMENT);
(p_location => 'xla_analytical_criteria_pkg.insert_detail_value');
END insert_detail_value;
| exists, or inserts a new record. |
| |
| No validation is done on the parameters. |
| |
| |
+======================================================================*/
l_detail_value_id INTEGER;
SELECT analytical_detail_value_id
INTO l_detail_value_id
FROM xla_analytical_dtl_vals
WHERE analytical_criterion_code = p_anacri_code
AND analytical_criterion_type_code = p_anacri_type_code
AND amb_context_code = p_amb_context_code
--Detail 1
AND NVL( analytical_detail_char_1
,NVL( TO_CHAR( analytical_detail_date_1
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( analytical_detail_number_1
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( p_detail_char_1
,NVL( TO_CHAR( p_detail_date_1
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( p_detail_number_1
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 2
AND NVL( analytical_detail_char_2
,NVL( TO_CHAR( analytical_detail_date_2
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( analytical_detail_number_2
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( p_detail_char_2
,NVL( TO_CHAR( p_detail_date_2
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( p_detail_number_2
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 3
AND NVL( analytical_detail_char_3
,NVL( TO_CHAR( analytical_detail_date_3
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( analytical_detail_number_3
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( p_detail_char_3
,NVL( TO_CHAR( p_detail_date_3
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( p_detail_number_3
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 4
AND NVL( analytical_detail_char_4
,NVL( TO_CHAR( analytical_detail_date_4
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( analytical_detail_number_4
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( p_detail_char_4
,NVL( TO_CHAR( p_detail_date_4
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( p_detail_number_4
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
--Detail 5
AND NVL( analytical_detail_char_5
,NVL( TO_CHAR( analytical_detail_date_5
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( analytical_detail_number_5
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
= NVL( p_detail_char_5
,NVL( TO_CHAR( p_detail_date_5
,'J'||'.'||'HH24MISS'
)
,NVL( TO_CHAR( p_detail_number_5
,'TM'
,'NLS_NUMERIC_CHARACTERS = ''.,'''
)
,'%'
)
)
)
;
|| ' row(s) selected from xla_analytical_dtl_vals '
,p_level => C_LEVEL_STATEMENT);
l_detail_value_id := insert_detail_value
( p_anacri_code => p_anacri_code
,p_anacri_type_code => p_anacri_type_code
,p_amb_context_code => p_amb_context_code
,p_detail_char_1 => p_detail_char_1
,p_detail_char_2 => p_detail_char_2
,p_detail_char_3 => p_detail_char_3
,p_detail_char_4 => p_detail_char_4
,p_detail_char_5 => p_detail_char_5
,p_detail_date_1 => p_detail_date_1
,p_detail_date_2 => p_detail_date_2
,p_detail_date_3 => p_detail_date_3
,p_detail_date_4 => p_detail_date_4
,p_detail_date_5 => p_detail_date_5
,p_detail_number_1 => p_detail_number_1
,p_detail_number_2 => p_detail_number_2
,p_detail_number_3 => p_detail_number_3
,p_detail_number_4 => p_detail_number_4
,p_detail_number_5 => p_detail_number_5
);
PROCEDURE insert_header_detail
( p_ae_header_id IN INTEGER
,p_analytical_detail_value_id IN INTEGER
)
IS
/*======================================================================+
| |
| Private Procedure |
| |
| Description |
| ----------- |
| Inserts a new record in the table xla_ae_header_details. |
| |
+======================================================================*/
l_log_module VARCHAR2 (2000);
l_log_module := C_DEFAULT_MODULE||'.insert_header_detail';
INSERT INTO xla_ae_header_details
( ae_header_id
,analytical_detail_value_id
)
VALUES
( p_ae_header_id
,p_analytical_detail_value_id
);
' row(s) inserted into xla_ae_headers_details '
,p_level => C_LEVEL_STATEMENT);
(p_location => 'xla_analytical_criteria_pkg.insert_header_detail');
END insert_header_detail;
PROCEDURE insert_line_detail
( p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_analytical_detail_value_id IN INTEGER
)
IS
/*======================================================================+
| |
| Private Procedure |
| |
| Description |
| ----------- |
| Inserts a new record in the table xla_ae_line_details |
| |
| |
| Open issues |
| ----------- |
+======================================================================*/
l_log_module VARCHAR2 (2000);
l_log_module := C_DEFAULT_MODULE||'.insert_line_detail';
INSERT INTO xla_ae_line_details
( ae_header_id
,ae_line_num
,analytical_detail_value_id
)
VALUES
( p_ae_header_id
,p_ae_line_num
,p_analytical_detail_value_id
);
' row(s) inserted into xla_ae_line_details '
,p_level => C_LEVEL_STATEMENT);
(p_location => 'xla_analytical_criteria_pkg.insert_line_detail');
END insert_line_detail;
PROCEDURE delete_line_details
( p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_analytical_detail_value_id IN INTEGER
)
IS
/*======================================================================+
| |
| Private Procedure |
| |
| Description |
| ----------- |
| Deletes one or all (if none specified) of the line details of a |
| journal entry line.
| |
| Open issues |
| ----------- |
+======================================================================*/
l_details_count INTEGER;
l_log_module := C_DEFAULT_MODULE||'.delete_line_details';
DELETE
FROM xla_ae_line_details
WHERE ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num;
|| ' row(s) deleted from xla_ae_line_details'
,p_level => C_LEVEL_STATEMENT);
DELETE
FROM xla_ae_line_details
WHERE ae_header_id = p_ae_header_id
AND ae_line_num = p_ae_line_num
AND analytical_detail_value_id = p_analytical_detail_value_id;
|| ' row(s) deleted from xla_ae_line_details'
,p_level => C_LEVEL_STATEMENT);
(p_location => 'xla_analytical_criteria_pkg.delete_line_details');
END delete_line_details;
SELECT 1
FROM xla_ae_line_details xald
WHERE xald.ae_header_id = cp_ae_header_id
AND xald.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
SELECT xah.balancing_flag
INTO l_balancing_flag
FROM xla_analytical_hdrs_b xah
WHERE xah.analytical_criterion_code = p_anacri_code
AND xah.analytical_criterion_type_code = p_anacri_type_code
AND xah.amb_context_code = p_amb_context_code
AND xah.enabled_flag = 'Y';
insert_header_detail
( p_ae_header_id => p_ae_header_id
,p_analytical_detail_value_id => l_detail_value_id
);
SELECT xal.analytical_balance_flag
INTO l_analytical_balance_flag
FROM xla_ae_lines xal
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
IF NOT xla_balances_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
IF NOT xla_balances_calc_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
insert_line_detail
( p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_analytical_detail_value_id => l_detail_value_id
);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_PEND
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
SELECT 1
FROM xla_ae_line_acs xald
WHERE xald.ae_header_id = cp_ae_header_id
AND xald.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
SELECT xah.balancing_flag
INTO l_balancing_flag
FROM xla_analytical_hdrs_b xah
WHERE xah.analytical_criterion_code = p_anacri_code
AND xah.analytical_criterion_type_code = p_anacri_type_code
AND xah.amb_context_code = p_amb_context_code
AND xah.enabled_flag = 'Y';
SELECT xal.analytical_balance_flag
INTO l_analytical_balance_flag
FROM xla_ae_lines xal
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
IF NOT xla_balances_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
IF NOT xla_balances_calc_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_PEND
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
,p_msg => 'No balance update for headers.'
,p_level => C_LEVEL_STATEMENT);
SELECT 1
FROM xla_ae_header_details xahd
WHERE xahd.ae_header_id = cp_ae_header_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_header_details xahd
WHERE xahd.ae_header_id = cp_ae_header_id
AND xahd.analytical_detail_value_id = cp_analytical_detail_value_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_lines xal
,xla_ae_line_details xald
WHERE xal.ae_header_id = cp_ae_header_id
AND xal.application_id = cp_application_id
AND xald.ae_header_id = xal.ae_header_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_line_details xald
WHERE xald.ae_header_id = cp_ae_header_id
AND xald.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
SELECT COUNT(*)
INTO l_balanced_lined_count
FROM xla_ae_lines xal
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE;
IF NOT xla_balances_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => NULL
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
IF NOT xla_balances_calc_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => NULL
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
DELETE
FROM xla_ae_header_details xhd
WHERE xhd.ae_header_id = p_ae_header_id;
|| ' row(s) deleted from xla_ae_header_details'
,p_level => C_LEVEL_STATEMENT);
SELECT xal.ae_line_num
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = p_application_id
AND xah.ae_header_id = p_ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
)
LOOP
--call delete_line_details
delete_line_details
( p_ae_header_id => p_ae_header_id
,p_ae_line_num => i.ae_line_num
,p_analytical_detail_value_id => NULL
);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id;
|| ' row(s) updated to '
|| NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
|| ' in xla_ae_lines'
,p_level => C_LEVEL_STATEMENT);
DELETE
FROM xla_ae_header_details xahd
WHERE xahd.ae_header_id = p_ae_header_id
AND xahd.analytical_detail_value_id = p_analytical_detail_value_id;
|| ' row(s) deleted from xla_ae_header_details'
,p_level => C_LEVEL_STATEMENT);
SELECT xal.analytical_balance_flag
INTO l_analytical_balance_flag
FROM xla_ae_lines xal
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
IF NOT xla_balances_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful.'
|| 'Cannot remove the details.'
,p_level => C_LEVEL_ERROR
);
IF NOT xla_balances_calc_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful.'
|| 'Cannot remove the details.'
,p_level => C_LEVEL_ERROR
);
delete_line_details
( p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_analytical_detail_value_id => NULL
);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
|| ' row(s) updated in xla_ae_lines: '
|| 'analytical_balance_flag updated to '
|| NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
,p_level => C_LEVEL_STATEMENT
);
delete_line_details
( p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_analytical_detail_value_id => p_analytical_detail_value_id
);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num
AND 0 =
(
SELECT count(xald.ae_line_num)
FROM xla_ae_line_details xald
,xla_analytical_dtl_vals xadv
,xla_analytical_hdrs_b xahb
WHERE xald.ae_header_id = p_ae_header_id
AND xald.ae_line_num = p_ae_line_num
AND xadv.analytical_detail_value_id = xald.analytical_detail_value_id
AND xahb.amb_context_code = xadv.amb_context_code
AND xahb.analytical_criterion_code = xadv.analytical_criterion_code
AND xahb.analytical_criterion_type_code = xadv.analytical_criterion_type_code
AND xahb.balancing_flag = 'Y'
);
|| ' row(s) updated in xla_ae_lines: '
|| 'analytical_balance_flag updated to '
|| NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
,p_level => C_LEVEL_STATEMENT
);
SELECT 1
FROM xla_ae_header_acs xahd
WHERE xahd.ae_header_id = cp_ae_header_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_header_acs xahd
WHERE xahd.ae_header_id = cp_ae_header_id
AND xahd.analytical_criterion_code = cp_anacri_code
AND xahd.analytical_criterion_type_code = cp_anacri_type_code
AND xahd.amb_context_code = cp_amb_context_code
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_lines xal
,xla_ae_line_acs xald
WHERE xal.ae_header_id = cp_ae_header_id
AND xal.application_id = cp_application_id
AND xald.ae_header_id = xal.ae_header_id
AND xald.ae_line_num = xal.ae_line_num ---BugNo 10408339 Added for performance issue.
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_line_acs xald
WHERE xald.ae_header_id = cp_ae_header_id
AND xald.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
SELECT COUNT(*)
INTO l_balanced_lined_count
FROM xla_ae_lines xal
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE;
IF NOT xla_balances_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => NULL
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
IF NOT xla_balances_calc_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => NULL
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful'
,p_level => C_LEVEL_ERROR);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id;
|| ' row(s) updated to '
|| NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
|| ' in xla_ae_lines'
,p_level => C_LEVEL_STATEMENT);
SELECT xal.analytical_balance_flag
INTO l_analytical_balance_flag
FROM xla_ae_lines xal
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
IF NOT xla_balances_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful.'
|| 'Cannot remove the details.'
,p_level => C_LEVEL_ERROR
);
IF NOT xla_balances_calc_pkg.single_update
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_update_mode => 'D'
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Balance removal unsuccessful.'
|| 'Cannot remove the details.'
,p_level => C_LEVEL_ERROR
);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num;
|| ' row(s) updated in xla_ae_lines: '
|| 'analytical_balance_flag updated to '
|| NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_ae_lines xal
SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
WHERE xal.application_id = p_application_id
AND xal.ae_header_id = p_ae_header_id
AND xal.ae_line_num = p_ae_line_num
AND 0 =
(
SELECT count(xald.ae_line_num)
FROM xla_ae_line_acs xald
,xla_analytical_hdrs_b xahb
WHERE xald.ae_header_id = p_ae_header_id
AND xald.ae_line_num = p_ae_line_num
AND xahb.amb_context_code = xald.amb_context_code
AND xahb.analytical_criterion_code = xald.analytical_criterion_code
AND xahb.analytical_criterion_type_code = xald.analytical_criterion_type_code
AND xahb.balancing_flag = 'Y'
);
|| ' row(s) updated in xla_ae_lines: '
|| 'analytical_balance_flag updated to '
|| NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
,p_level => C_LEVEL_STATEMENT
);
FUNCTION update_detail_value ( p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_list_of_criteria IN OUT NOCOPY t_list_of_criteria
,p_update_mode IN VARCHAR2
)
RETURN BOOLEAN
IS
/*======================================================================+
| |
| Public Function |
| |
| Description |
| ----------- |
| Refer to the Detail Level Design document |
+======================================================================*/
CURSOR lc_lock_ae_header
( cp_application_id INTEGER
,cp_ae_header_id INTEGER
)
IS
SELECT 1
FROM xla_ae_headers xah
WHERE xah.application_id = cp_application_id
AND xah.ae_header_id = cp_ae_header_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = cp_application_id
AND xah.ae_header_id = cp_ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
l_log_module := C_DEFAULT_MODULE||'.update_detail_value';
(p_location => 'xla_analytical_criteria_pkg.update_detail_value');
IF p_update_mode = 'A'
THEN
IF p_list_of_criteria IS NULL
OR p_list_of_criteria.LAST IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'EXCEPTION:'
||'p_list_of_criteria is empty'
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.update_detail_value');
ELSIF p_update_mode = 'D'
THEN
IF p_list_of_criteria IS NULL
OR p_list_of_criteria.LAST IS NULL
THEN
l_return_value :=remove_criterion
( p_application_id => p_application_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_anacri_code => NULL
,p_anacri_type_code => NULL
,p_amb_context_code => NULL
,p_analytical_detail_value_id => NULL
);
||'Unkown p_update_mode value: ' || p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.update_detail_value');
END update_detail_value;
FUNCTION single_update_detail_value
( p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_analytical_detail_value_id IN INTEGER
,p_anacri_code IN VARCHAR2
,p_anacri_type_code IN VARCHAR2
,p_amb_context_code IN VARCHAR2
,p_update_mode IN VARCHAR2
,p_detail_char_1 IN VARCHAR2 DEFAULT NULL
,p_detail_date_1 IN DATE DEFAULT NULL
,p_detail_number_1 IN NUMBER DEFAULT NULL
,p_detail_char_2 IN VARCHAR2 DEFAULT NULL
,p_detail_date_2 IN DATE DEFAULT NULL
,p_detail_number_2 IN NUMBER DEFAULT NULL
,p_detail_char_3 IN VARCHAR2 DEFAULT NULL
,p_detail_date_3 IN DATE DEFAULT NULL
,p_detail_number_3 IN NUMBER DEFAULT NULL
,p_detail_char_4 IN VARCHAR2 DEFAULT NULL
,p_detail_date_4 IN DATE DEFAULT NULL
,p_detail_number_4 IN NUMBER DEFAULT NULL
,p_detail_char_5 IN VARCHAR2 DEFAULT NULL
,p_detail_date_5 IN DATE DEFAULT NULL
,p_detail_number_5 IN NUMBER DEFAULT NULL
)
RETURN BOOLEAN
IS
/*======================================================================+
| |
| Public Function |
| Obsolete in R12+ Supporting References Re-Architecture |
| No need to maintain xla_analytical_dtl_vals
| From AeLineAcEOImpl.java, call update_balances. |
| |
| Description |
| ----------- |
| Replaced with update_balances in R12+ Re-Architecture |
+======================================================================*/
CURSOR lc_lock_ae_header
( cp_application_id INTEGER
,cp_ae_header_id INTEGER
)
IS
SELECT 1
FROM xla_ae_headers xah
WHERE xah.application_id = cp_application_id
AND xah.ae_header_id = cp_ae_header_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = cp_application_id
AND xah.ae_header_id = cp_ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
l_log_module := C_DEFAULT_MODULE||'.single_update_detail_value';
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
IF p_update_mode = 'A'
THEN
IF p_anacri_code IS NULL
OR p_anacri_type_code IS NULL
OR p_amb_context_code IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'EXCEPTION:'
||'When adding, p_anacri_code, p_anacri_type_code and'
||'p_amb_context_code cannot be NULL'
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
ELSIF p_update_mode = 'D'
THEN
IF p_ae_header_id IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'EXCEPTION:'
||'When deleting p_ae_header_id cannot be NULL: ' ||
p_ae_header_id
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
||'Unkown p_update_mode value: '
|| p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
END single_update_detail_value;
FUNCTION single_update_detail_value
( p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_anacri_code IN VARCHAR2
,p_anacri_type_code IN VARCHAR2
,p_amb_context_code IN VARCHAR2
,p_update_mode IN VARCHAR2
,p_ac1 IN VARCHAR2 DEFAULT NULL
,p_ac2 IN VARCHAR2 DEFAULT NULL
,p_ac3 IN VARCHAR2 DEFAULT NULL
,p_ac4 IN VARCHAR2 DEFAULT NULL
,p_ac5 IN VARCHAR2 DEFAULT NULL
)
RETURN BOOLEAN IS
/*======================================================================+
| |
| Public Function |
|
| Description |
| ----------- |
| Added for R12+ Supporting References Re-Architecture |
| Called From: |
| - AeLineAcEOImpl.java |
+======================================================================*/
CURSOR lc_lock_ae_header
( cp_application_id INTEGER
,cp_ae_header_id INTEGER
)
IS
SELECT 1
FROM xla_ae_headers xah
WHERE xah.application_id = cp_application_id
AND xah.ae_header_id = cp_ae_header_id
FOR UPDATE NOWAIT;
SELECT 1
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = cp_application_id
AND xah.ae_header_id = cp_ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.ae_line_num = cp_ae_line_num
FOR UPDATE NOWAIT;
l_log_module := C_DEFAULT_MODULE||'.update_balances';
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
IF p_update_mode = 'A'
THEN
IF p_anacri_code IS NULL
OR p_anacri_type_code IS NULL
OR p_amb_context_code IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'EXCEPTION:'
||'When adding, p_anacri_code, p_anacri_type_code and'
||'p_amb_context_code cannot be NULL'
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
ELSIF p_update_mode = 'D'
THEN
IF p_ae_header_id IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'EXCEPTION:'
||'When deleting p_ae_header_id cannot be NULL: ' ||
p_ae_header_id
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
||'Unkown p_update_mode value: '
|| p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
(p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
END single_update_detail_value;
SELECT xdtb.view_column_num
FROM xla_analytical_dtls_b xdtb
,xla_analytical_hdrs_b xhtb
WHERE xdtb.analytical_criterion_code = xhtb.analytical_criterion_code
AND xdtb.analytical_criterion_type_code = xhtb.analytical_criterion_type_code
AND xhtb.balancing_flag = p_balance_flag
AND xdtb.data_type_code = cp_data_type_code
AND NVL(xdtb.view_column_num, -1) =
(SELECT NVL( MIN(xdtb2.view_column_num), -1)
FROM xla_analytical_dtls_b xdtb2
,xla_analytical_dtls_b xdtb3
,xla_analytical_hdrs_b xhtb2
WHERE xdtb2.data_type_code = xdtb.data_type_code
AND xdtb2.analytical_criterion_code = xhtb2.analytical_criterion_code
AND xdtb2.analytical_criterion_type_code = xhtb2.analytical_criterion_type_code
AND xhtb2.balancing_flag = p_balance_flag
AND xdtb3.data_type_code (+)= xdtb2.data_type_code
AND xdtb3.view_column_num (+)= xdtb2.view_column_num + 1
AND xdtb3.rowid IS NULL
)
FOR UPDATE NOWAIT;
SELECT count(*)
INTO l_count_in_slot_1
FROM xla_analytical_dtls_b xdtb
WHERE xdtb.data_type_code = p_data_type_code
AND xdtb.view_column_num = 1;
SELECT view_column_num
FROM xla_analytical_dtls_b
WHERE analytical_criterion_code = p_anacri_code
AND analytical_criterion_type_code = p_anacri_type_code
AND analytical_detail_code = p_anacri_detail_code
AND data_type_code = p_data_type_code
AND view_column_num IS NOT NULL; -- bug 4583524
SELECT xdtb.analytical_detail_code
,xdtb.view_column_num
,xdtb.data_type_code
FROM xla_analytical_dtls_b xdtb
WHERE xdtb.analytical_criterion_code = cp_anacri_code
AND xdtb.analytical_criterion_type_code = cp_anacri_type_code
AND xdtb.amb_context_code = cp_amb_context_code
ORDER BY xdtb.grouping_order
FOR UPDATE NOWAIT;
l_update_required BOOLEAN;
SELECT xhdb.enabled_flag
,xhdb.display_in_inquiries_flag
,xhdb.balancing_flag
INTO l_enabled_flag
,l_display_in_inquiries_flag
,l_balance_flag
FROM xla_analytical_hdrs_b xhdb
WHERE xhdb.analytical_criterion_code = p_anacri_code
AND xhdb.analytical_criterion_type_code = p_anacri_type_code
AND xhdb.amb_context_code = p_amb_context_code
FOR UPDATE NOWAIT;
SELECT count(*)
INTO l_count_exist_dvals
FROM xla_analytical_dtl_vals xadv
WHERE xadv.analytical_criterion_code = p_anacri_code
AND xadv.analytical_criterion_type_code = p_anacri_type_code
AND xadv.amb_context_code = p_amb_context_code
AND ROWNUM = 1;
l_update_required := TRUE;
l_update_required := TRUE;
l_update_required := FALSE;
l_update_required := TRUE;
l_update_required := FALSE;
IF l_update_required THEN
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'l_updated_required: '||current_detail.analytical_detail_code||
', l_view_column_number = '||l_view_column_number
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_analytical_dtls_b xdtb
SET xdtb.view_column_num = l_view_column_number
WHERE xdtb.analytical_criterion_code = p_anacri_code
AND xdtb.analytical_criterion_type_code = p_anacri_type_code
AND xdtb.amb_context_code = p_amb_context_code
AND xdtb.analytical_detail_code = current_detail.analytical_detail_code;
SELECT xhdb.analytical_criterion_code
,xhdb.analytical_criterion_type_code
--,xhdb.amb_context_code
,xdtb.analytical_detail_code
,xdtb.grouping_order
,xdtb.view_column_num
FROM xla_analytical_hdrs_b xhdb
,xla_analytical_dtls_b xdtb
WHERE xhdb.display_in_inquiries_flag = 'Y'
AND xdtb.analytical_criterion_code = xhdb.analytical_criterion_code
AND xdtb.analytical_criterion_type_code = xhdb.analytical_criterion_type_code
AND xdtb.amb_context_code = xhdb.amb_context_code
AND xdtb.data_type_code = NVL(cp_data_type_code, xdtb.data_type_code)
GROUP BY xhdb.analytical_criterion_code
,xhdb.analytical_criterion_type_code
--,xhdb.amb_context_code
,xdtb.analytical_detail_code
,xdtb.grouping_order
,xdtb.view_column_num
ORDER BY xdtb.view_column_num
;
SELECT
xald.ae_header_id AE_HEADER_ID
,xald.ae_line_num AE_LINE_NUM
';
SELECT
xahd.ae_header_id AE_HEADER_ID
';
l_selected_field_name_template VARCHAR2(256) :=
'analytical_detail_';
l_select_row_template VARCHAR2(256) :=
',';
SELECT xald.ae_header_id
,xald.ae_line_num';
SELECT xahd.ae_header_id';
l_current_detail_field_name := REPLACE(l_selected_field_name_template, '', i);
l_current_line := REPLACE(l_select_row_template, '', l_current_detail_field_name);
SELECT NVL(MAX(COUNT(1)),0)
INTO l_hdr_ac_count
FROM xla_aad_header_ac_assgns
GROUP BY
amb_context_code
,application_id
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code;
SELECT NVL(MAX(COUNT(1)),0)
INTO l_mpa_hdr_ac_count
FROM xla_mpa_header_ac_assgns
GROUP BY
amb_context_code
,application_id
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code;
SELECT NVL(MAX(COUNT(1)),0)
INTO l_line_ac_count
FROM xla_line_defn_ac_assgns
GROUP BY
amb_context_code
,application_id
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code;
SELECT NVL(MAX(COUNT(1)),0)
INTO l_mpa_line_ac_count
FROM xla_mpa_jlt_ac_assgns
GROUP BY
amb_context_code
,application_id
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code;
SELECT xpr.name
FROM xla_product_rules_vl xpr
WHERE ( xpr.application_id
,xpr.amb_context_code
,xpr.product_rule_type_code
,xpr.product_rule_code
)
IN ( SELECT DISTINCT
xaa.application_id
,xaa.amb_context_code
,xaa.product_rule_type_code
,xaa.product_rule_code
FROM xla_analytical_assgns xaa
WHERE xaa.amb_context_code = p_amb_context_code
AND xaa.analytical_criterion_code = p_anacri_code
AND xaa.analytical_criterion_type_code = p_anacri_type_code
)
AND xpr.compile_status_code IN ('E','N','Y')
-- AND xpr.locking_status_flag = 'N'
FOR UPDATE of compile_status_code NOWAIT;