[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| massive_update: |
| p_application_id must be not null. |
| load_balance_temp_tables: |
| added support for p_application_id |
| not null when p_request_id not null |
| 29-OCT-03 A. Quaglia Bug3190083 |
| lock_create_balance_statuses: |
| replaced FND_FLEX_APIS |
| get_qualifier_segnum + get_segment_info|
| with get_segment_column |
| in all _srs: |
| added activate/deactivate trace stmts |
| 31-OCT-03 A.Quaglia Bug3202694: |
| massive_update: |
| added p_entity_id |
| old, deprecated API maintained until |
| uptake is done. |
| calculate_balances: |
| if no rows to process exit immediately |
| also when commit_flag <> 'Y' |
| 31-OCT-03 A.Quaglia Replaced other occurences of FND_FLEX_APIS|
| 26-NOV-03 A.Quaglia Bug3264347: |
| massive_update_srs: |
| new param p_dummy |
| massive_update: |
| changed concurrent submission of |
| XLABAOPE (new dummy param in def.) |
| 15-DEC-03 A.Quaglia Bug3315864: |
| move_balances_forward: |
| fixed carry forward of NULL amounts |
| move_identified_bals_forward: |
| fixed carry forward of NULL amounts |
| fixed missing outer join on one cond. |
| fixed running from SQLPlus (no req.id) |
| renamed to move_balances_forward_COMMIT|
| build_line_selection_dyn_stmts: |
| corrected WHEN clause in INSERT ALL |
| 05-MAR-04 A.Quaglia Changed trace handling as per Sandeep's |
| code. |
| 19-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 |
| 29-JUL-04 A.Quaglia Bug3202694: |
| massive_update: |
| removed deprecated API |
| |
| 22-OCT-04 W.Shen New API is added |
| massive_update(p_application_id) |
| This API is for Bulk Event API only |
| It will update the balance for events |
| in events_gt table. |
| Two private functions: Calculate_balances,|
| Load_balance_temp_tables are updated |
| too |
| 11-MAR-05 W. Chan Fixed bug 4220415 - removed join to |
| xla_transaction_entities in |
| load_balance_temp_tables when the |
| entity_id IS NULL |
| |
| 01-APR-05 W. Chan Fixed bug 4277500 - removed join to |
| xla_events in load_balance_temp_tables |
| when the entity_id IS NULL |
| |
| 10-APR-05 W. Shen Fixed bug 4277500 - removed the table |
| 30-NOV-05 V. Kumar Bug 4769611 Modify SQLs in balance calcul- |
| ation routine |
| 19-Jan-05 V. Kumar Removed the code for AC balances |
| 03-Mar-06 V. Kumar Populating GL_SL_LINK_IN in xla_ae_lines |
| 05-Dec-08 karamakr 7608545- Reset l_begin_bal_dr, |
| l_begin_bal_cr to null |
+======================================================================*/
--Generic Procedure/Function template
/*======================================================================+
| |
| Private Function |
| |
| Description |
| ----------- |
| |
| |
| Pseudo-code |
| ----------- |
| |
| |
| Open issues |
| ----------- |
| |
| MUST SOLVE |
| |
| |
| NICE TO SOLVE |
| |
| |
+======================================================================*/
--
-- Private exceptions
--
le_resource_busy EXCEPTION;
,p_balance_status_code_selected IN VARCHAR2
,p_balance_status_code_not_sel IN VARCHAR2
)
RETURN BOOLEAN
IS
/*======================================================================+
| |
| Private Function |
| |
| Description |
| ----------- |
| |
| WARNING: this procedure performs COMMITs |
| |
| |
| |
| Pseudo-code |
| ----------- |
| |
| Open issues |
| ----------- |
| |
| |
+======================================================================*/
CURSOR lc_lock_balance_statuses ( cp_application_id INTEGER
,cp_ledger_id INTEGER
,cp_balance_status_code VARCHAR2
,cp_balance_source_code VARCHAR2
,cp_effective_period_num INTEGER
,cp_request_id INTEGER
)
IS
SELECT xbs.code_combination_id
FROM xla_balance_statuses xbs
WHERE xbs.application_id = cp_application_id
AND xbs.ledger_id = cp_ledger_id
AND xbs.balance_status_code = cp_balance_status_code
AND xbs.balance_source_code = cp_balance_source_code
AND xbs.effective_period_num = cp_effective_period_num
AND xbs.request_id = cp_request_id
FOR UPDATE;
,p_msg => 'p_balance_status_code_selected :' || p_balance_status_code_selected
,p_level => C_LEVEL_STATEMENT);
IF p_balance_status_code_selected IS NULL
AND p_balance_status_code_not_sel IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:'
||'p_balance_status_code_selected and p_balance_status_code_not_sel '
|| 'cannot be both NULL'
,p_level => C_LEVEL_EXCEPTION
);
||'p_balance_status_code_selected and p_balance_status_code_not_sel '
|| 'cannot be both NULL');
SELECT gpssource.period_name
,gpsdest.period_name
,gpsdest.effective_period_num
,gpsdest.period_year
,DECODE( gpssource.period_year
,gpsdest.period_year
,'N'
,'Y'
) first_period_flag
INTO l_source_period_name
,l_dest_period_name
,l_dest_effective_period_num
,l_dest_period_year
,l_dest_first_period_flag
FROM gl_period_statuses gpssource
,gl_period_statuses gpsdest
WHERE gpssource.ledger_id = p_ledger_id
AND gpssource.application_id = 101
AND gpssource.closing_status IN ('O', 'C', 'P')
AND gpssource.adjustment_period_flag = 'N'
AND gpssource.effective_period_num = p_source_effective_period_num
AND gpsdest.ledger_id = p_ledger_id
AND gpsdest.application_id = 101
AND gpsdest.effective_period_num =
( SELECT MIN(gps2.effective_period_num)
FROM gl_period_statuses gps2
WHERE gps2.ledger_id = p_ledger_id
AND gps2.application_id = 101
AND gps2.effective_period_num > p_source_effective_period_num
AND gps2.closing_status IN ('O', 'C', 'P')
AND gps2.adjustment_period_flag = 'N'
);
UPDATE xla_balance_statuses xbsext
SET xbsext.balance_status_code = p_balance_status_code_selected
,xbsext.last_update_date = l_date
,xbsext.last_updated_by = l_user_id
,xbsext.last_update_login = l_login_id
,xbsext.program_update_date = l_date
,xbsext.program_application_id = l_prog_appl_id
,xbsext.program_id = l_prog_id
,xbsext.request_id = NVL(l_req_id, -1)
WHERE xbsext.ROWID IN
(SELECT xbs.ROWID
FROM xla_balance_statuses xbs
,fnd_concurrent_requests fnd
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = NVL( p_balance_source_code
,xbs.balance_source_code
)
AND xbs.effective_period_num = p_source_effective_period_num
AND xbs.balance_status_code IN ( p_balance_status_code_not_sel
,p_balance_status_code_selected
)
AND fnd.request_id(+) = xbs.request_id
--pick up records being handled by this request
--or by any another request which is not running
AND ( NVL(xbs.request_id, -1) = NVL(l_req_id, -1)
OR NVL(fnd.status_code,'N') <> 'R'
)
--handle the case where the procedure is invoked
--outside a concurrent request
UNION
SELECT xbs.ROWID
FROM xla_balance_statuses xbs
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = NVL( p_balance_source_code
,xbs.balance_source_code
)
AND xbs.effective_period_num = p_source_effective_period_num
AND xbs.balance_status_code IN ( p_balance_status_code_not_sel
,p_balance_status_code_selected
)
AND NVL(l_req_id, -1) = -1
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
|| ' xla_balance_statuses updated to '
|| p_balance_status_code_selected
,p_level => C_LEVEL_STATEMENT
);
,cp_balance_status_code => p_balance_status_code_selected
,cp_balance_source_code => 'C'
,cp_effective_period_num => p_source_effective_period_num
,cp_request_id => NVL(l_req_id, -1)
);
INSERT INTO xla_control_balances
( application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
,period_name
,period_year
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,draft_beginning_balance_dr
,draft_beginning_balance_cr
,period_draft_balance_dr
,period_draft_balance_cr
,initial_balance_flag
,first_period_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
SELECT xba.application_id
,xba.ledger_id
,xba.code_combination_id
,xba.party_type_code
,xba.party_id
,xba.party_site_id
,l_dest_period_name
,l_dest_period_year
,DECODE( l_dest_first_period_flag
,'Y'
,DECODE( SIGN ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
-(NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0))
)
,1
,NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
- (NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0))
,NULL
)
,NVL2( xba.beginning_balance_dr
,xba.beginning_balance_dr + NVL(xba.period_balance_dr, 0)
,xba.period_balance_dr
)
) --beginning_balance_dr
,DECODE( l_dest_first_period_flag
,'Y'
,DECODE( SIGN ( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
-(NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0))
)
,1
,NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
- ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0))
,NULL
)
,NVL2( xba.beginning_balance_cr
,xba.beginning_balance_cr + NVL(xba.period_balance_cr, 0)
,xba.period_balance_cr
)
) --beginning_balance_cr
,NULL --period_balance_dr
,NULL --period_balance_cr
,DECODE( l_dest_first_period_flag
,'Y'
,DECODE( SIGN ( NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0)
-( NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0) )
)
,1
,NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0)
- ( NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0) )
,NULL
)
,NVL2( xba.draft_beginning_balance_dr
,xba.draft_beginning_balance_dr + NVL(xba.period_draft_balance_dr, 0)
,xba.period_draft_balance_dr
)
) --draft_beginning_balance_dr
,DECODE( l_dest_first_period_flag
,'Y'
,DECODE( SIGN ( NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0)
-( NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0) )
)
,1
,NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0)
- ( NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0) )
,NULL
)
,NVL2( xba.draft_beginning_balance_cr
,xba.draft_beginning_balance_cr + NVL(xba.period_draft_balance_cr, 0)
,xba.period_draft_balance_cr
)
) --draft_beginning_balance_cr
,NULL --period_draft_balance_dr
,NULL --period_draft_balance_cr
,'N' --initial_balance_flag
,l_dest_first_period_flag --first_period_flag
,SYSDATE --creation_date
,l_user_id --created_by
,SYSDATE --last_update_date
,l_user_id --last_update_by
,l_login_id --last_update_login
,SYSDATE --program_update_date
,l_prog_appl_id --program_application_id
,l_prog_id --program_id
,NVL(l_req_id, -1) --request_id
FROM xla_balance_statuses xbs
,xla_control_balances xba
,xla_control_balances xbanew
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = 'C'
AND xbs.balance_status_code = p_balance_status_code_selected
AND xbs.effective_period_num = p_source_effective_period_num
AND xbs.request_id = NVL(l_req_id, -1)
AND xba.ledger_id = p_ledger_id
AND xba.application_id = p_application_id
AND xba.code_combination_id = xbs.code_combination_id
AND xba.period_name = l_source_period_name
AND xbanew.application_id (+)= p_application_id
AND xbanew.ledger_id (+)= p_ledger_id
AND xbanew.application_id (+)= xba.application_id
AND xbanew.code_combination_id (+)= xba.code_combination_id
AND xbanew.period_name (+)= l_dest_period_name
AND xbanew.party_id (+)= xba.party_id
AND xbanew.party_site_id (+)= xba.party_site_id
AND xbanew.ledger_id IS NULL
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,cp_balance_status_code => p_balance_status_code_selected
,cp_balance_source_code => 'A'
,cp_effective_period_num => p_source_effective_period_num
,cp_request_id => NVL(l_req_id, -1)
);
INSERT INTO xla_ac_balances
( application_id
,ledger_id
,code_combination_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,period_name
,period_year
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,initial_balance_flag
,first_period_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
SELECT xba.application_id
,xba.ledger_id
,xba.code_combination_id
,xba.analytical_criterion_code
,xba.analytical_criterion_type_code
,xba.amb_context_code
,xba.ac1
,xba.ac2
,xba.ac3
,xba.ac4
,xba.ac5
,l_dest_period_name
,l_dest_period_year
,DECODE( l_dest_first_period_flag
,'Y'
,DECODE( SIGN ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
-(NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0))
)
,1
,NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
- (NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0))
,NULL
)
,NVL2( xba.beginning_balance_dr
,xba.beginning_balance_dr + NVL(xba.period_balance_dr, 0)
,xba.period_balance_dr
)
) --beginning_balance_dr
,DECODE( l_dest_first_period_flag
,'Y'
,DECODE( SIGN ( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
-(NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0))
)
,1
,NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
- ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0))
,NULL
)
,NVL2( xba.beginning_balance_cr
,xba.beginning_balance_cr + NVL(xba.period_balance_cr, 0)
,xba.period_balance_cr
)
) --beginning_balance_cr
,NULL --period_balance_dr
,NULL --period_balance_cr
,'N' --initial_balance_flag
,l_dest_first_period_flag
,SYSDATE --creation_date
,l_user_id --created_by
,SYSDATE --last_update_date
,l_user_id --last_update_by
,l_login_id --last_update_login
,SYSDATE --program_update_date
,l_prog_appl_id --program_application_id
,l_prog_id --program_id
,NVL(l_req_id, -1) --request_id
FROM xla_balance_statuses xbs
,xla_ac_balances xba
,xla_ac_balances xbanew
,xla_analytical_hdrs_b xbh
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = 'A'
AND xbs.balance_status_code = p_balance_status_code_selected
AND xbs.effective_period_num = p_source_effective_period_num
AND xbs.request_id = NVL(l_req_id, -1)
AND xba.application_id = p_application_id
AND xba.ledger_id = p_ledger_id
AND xba.application_id = xbs.application_id
AND xba.code_combination_id = xbs.code_combination_id
AND xba.period_name = l_source_period_name
AND xbanew.application_id (+)= p_application_id
AND xbanew.ledger_id (+)= p_ledger_id
AND xbanew.application_id (+)= xba.application_id
AND xbanew.code_combination_id (+)= xba.code_combination_id
AND xbanew.period_name (+)= l_dest_period_name
AND xbanew.analytical_criterion_code (+)= xba.analytical_criterion_code
AND xbanew.analytical_criterion_type_code
(+)= xba.analytical_criterion_type_code
AND xbanew.amb_context_code (+)= xba.amb_context_code
AND xbanew.ac1 (+)= xba.ac1
AND xbanew.ac2 (+)= xba.ac2
AND xbanew.ac3 (+)= xba.ac3
AND xbanew.ac4 (+)= xba.ac4
AND xbanew.ac5 (+)= xba.ac5
AND xbanew.ledger_id IS NULL
AND xbh.analytical_criterion_code = xba.analytical_criterion_code
AND xbh.analytical_criterion_type_code = xba.analytical_criterion_type_code
AND xbh.amb_context_code = xba.amb_context_code
AND ( xba.period_year = l_dest_period_year
OR xbh.year_end_carry_forward_code = 'A'
OR ( xbh.year_end_carry_forward_code = 'B'
AND xbs.account_type IN ('A', 'L', 'O')
)
)
-- Bug 7321087 Begin
AND NOT EXISTS( SELECT 1
FROM xla_ac_balances xba1
WHERE xba1.application_id = xba.application_id
AND xba1.ledger_id = xba.ledger_id
AND xba1.code_combination_id = xba.code_combination_id
AND xba1.analytical_criterion_code = xba.analytical_criterion_code
AND xba1.analytical_criterion_type_code = xba.analytical_criterion_type_code
AND xba1.amb_context_code = xba.amb_context_code
AND NVL(xba1.ac1,C_DUMMY) = NVL(xba.ac1,C_DUMMY)
AND NVL(xba1.ac2,C_DUMMY) = NVL(xba.ac2,C_DUMMY)
AND NVL(xba1.ac3,C_DUMMY) = NVL(xba.ac3,C_DUMMY)
AND NVL(xba1.ac4,C_DUMMY) = NVL(xba.ac4,C_DUMMY)
AND NVL(xba1.ac5,C_DUMMY) = NVL(xba.ac5,C_DUMMY)
AND xba1.period_name = l_dest_period_name)
-- Bug 7321087 End
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
UPDATE xla_balance_statuses xbs
SET xbs.balance_status_code = p_balance_status_code_not_sel
,xbs.effective_period_num = l_dest_effective_period_num
,last_update_date = l_date
,last_updated_by = l_user_id
,last_update_login = l_login_id
,program_update_date = l_date
,program_application_id = l_prog_appl_id
,program_id = l_prog_id
,request_id = NVL(l_req_id, -1)
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = NVL( p_balance_source_code
,xbs.balance_source_code
)
AND xbs.effective_period_num = p_source_effective_period_num
AND xbs.balance_status_code = p_balance_status_code_selected
AND xbs.request_id = NVL(l_req_id, -1);
' xla_balance_statuses updated to '
|| p_balance_status_code_not_sel
,p_level => C_LEVEL_STATEMENT
);
SELECT MIN(xbs.effective_period_num)
INTO l_min_bal_effective_period_num
FROM xla_balance_statuses xbs
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = p_balance_source_code;
SELECT MAX(gps.effective_period_num)
INTO l_latest_effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = p_ledger_id
AND gps.application_id = 101
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N';
FOR i IN ( SELECT gps.effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = p_ledger_id
AND gps.application_id = 101
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num >= l_min_bal_effective_period_num
AND gps.effective_period_num < l_latest_effective_period_num
ORDER BY gps.effective_period_num
)
LOOP
IF NOT move_balances_forward_COMMIT
( p_application_id => p_application_id
,p_ledger_id => p_ledger_id
,p_balance_source_code => p_balance_source_code
,p_source_effective_period_num => i.effective_period_num
,p_balance_status_code_selected => 'O'
,p_balance_status_code_not_sel => 'A'
)
THEN
l_return_value := FALSE;
SELECT 1
FROM xla_bal_ctrl_lines_gt xbct
,xla_balance_statuses xbs
WHERE xbs.application_id = xbct.application_id
AND xbs.ledger_id = xbct.ledger_id
AND xbs.code_combination_id = xbct.code_combination_id
AND xbs.balance_source_code = 'C'
FOR UPDATE OF xbs.ledger_id NOWAIT;
SELECT 1
FROM xla_bal_anacri_lines_gt xblt
,xla_balance_statuses xbs
WHERE xbs.application_id = xblt.application_id
AND xbs.ledger_id = xblt.ledger_id
AND xbs.code_combination_id = xblt.code_combination_id
AND xbs.balance_source_code = 'A'
FOR UPDATE OF xbs.ledger_id NOWAIT;
,p_msg => 'Start inserting in xla_balance_statuses'
,p_level => C_LEVEL_STATEMENT
);
INSERT INTO xla_bal_statuses_gt
( application_id
,ledger_id
,code_combination_id
,balance_source_code
,balance_status_code
,effective_period_num
,natural_account_segment
,balancing_segment
,account_type
)
(
SELECT DISTINCT
xbct.application_id
,xbct.ledger_id
,xbct.code_combination_id
,'C'
,'A'
, -1
,'TO BE DETERMINED'
,'TO BE DETERMINED'
,'?'
FROM xla_bal_ctrl_lines_gt xbct
,xla_balance_statuses xbs
WHERE xbs.application_id (+)= xbct.application_id
AND xbs.ledger_id (+)= xbct.ledger_id
AND xbs.code_combination_id (+)= xbct.code_combination_id
AND xbs.balance_source_code (+)= 'C'
AND xbs.ledger_id IS NULL
UNION ALL
SELECT DISTINCT
xbat.application_id
,xbat.ledger_id
,xbat.code_combination_id
,'A'
,'A'
, -1
,'TO BE DETERMINED'
,'TO BE DETERMINED'
,'?'
FROM xla_bal_anacri_lines_gt xbat
,xla_balance_statuses xbs
WHERE xbs.application_id (+)= xbat.application_id
AND xbs.ledger_id (+)= xbat.ledger_id
AND xbs.code_combination_id (+)= xbat.code_combination_id
AND xbs.balance_source_code (+)= 'A'
AND xbs.ledger_id IS NULL
);
' records inserted in xla_bal_statuses_gt'
,p_level => C_LEVEL_STATEMENT
);
SELECT DISTINCT
ledger_id
FROM xla_bal_statuses_gt xbs
)
LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'ledger_id : ' || i.ledger_id
,p_level => C_LEVEL_STATEMENT
);
SELECT gle.chart_of_accounts_id
,gle.bal_seg_column_name
INTO l_chart_of_accounts_id
,l_balancing_segment_column
FROM gl_ledgers gle
WHERE gle.ledger_id = i.ledger_id;
SELECT MAX(gps2.effective_period_num)
INTO l_latest_open_eff_period_num
FROM gl_period_statuses gps2
WHERE gps2.ledger_id = i.ledger_id
AND gps2.application_id = 101
AND gps2.closing_status IN ('O', 'C', 'P')
AND gps2.adjustment_period_flag = 'N';
UPDATE xla_bal_statuses_gt xbst
SET xbst.effective_period_num = ' ||l_latest_open_eff_period_num || '
,(
xbst.natural_account_segment
,xbst.balancing_segment
,xbst.account_type
)
=
( SELECT ' || l_balancing_segment_column || '
,' || l_account_segment_column || '
,account_type
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = xbst.code_combination_id
)
WHERE xbst.ledger_id = ' || i.ledger_id;
' records updated in xla_bal_statuses_gt'
,p_level => C_LEVEL_STATEMENT
);
INSERT INTO xla_balance_statuses
( application_id
,ledger_id
,code_combination_id
,balance_source_code
,balance_status_code
,effective_period_num
,natural_account_segment
,balancing_segment
,account_type
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
SELECT application_id
,ledger_id
,code_combination_id
,balance_source_code
,balance_status_code
,effective_period_num
,natural_account_segment
,balancing_segment
,account_type
,l_date
,l_user_id
,l_date
,l_user_id
,l_login_id
,l_date
,l_prog_appl_id
,l_prog_id
,NVL(l_req_id, -1)
FROM xla_bal_statuses_gt xbst;
' records inserted in xla_balance_statuses'
,p_level => C_LEVEL_STATEMENT
);
SELECT DISTINCT ctr.application_id
,ctr.ledger_id
,NVL(xba.period_year, ctr.period_year) contributed_period_year
,ctr.period_name
,ctr.period_year
,ctr.effective_period_num
,ctr.code_combination_id
,ctr.party_type_code
,ctr.party_id
,ctr.party_site_id
,ctr.contribution_dr
,ctr.contribution_cr
,ctr.contribution_draft_dr
,ctr.contribution_draft_cr
,NVL2( contribution_dr
,NVL2( contribution_cr
,DECODE( SIGN(contribution_dr - contribution_cr)
,1
,contribution_dr - contribution_cr
,0
)
,DECODE( SIGN(contribution_dr)
,1
,contribution_dr
,0
)
)
,NVL2( contribution_cr
,DECODE( SIGN(contribution_cr)
,-1
,-contribution_cr
,NULL
)
,NULL
)
) net_contribution_dr
,NVL2( contribution_cr
,NVL2( contribution_dr
,DECODE( SIGN(contribution_cr - contribution_dr)
,1
,contribution_cr - contribution_dr
,0
)
,DECODE( SIGN(contribution_cr)
,1
,contribution_cr
,0
)
)
,NVL2( contribution_dr
,DECODE( SIGN(contribution_dr)
,-1
,-contribution_dr
,NULL
)
,NULL
)
) net_contribution_cr
,NVL2( contribution_draft_dr
,NVL2( contribution_draft_cr
,DECODE( SIGN(contribution_draft_dr - contribution_draft_cr)
,1
,contribution_draft_dr - contribution_draft_cr
,0
)
,DECODE( SIGN(contribution_draft_dr)
,1
,contribution_draft_dr
,0
)
)
,NVL2( contribution_draft_cr
,DECODE( SIGN(contribution_draft_cr)
,-1
,-contribution_draft_cr
,NULL
)
,NULL
)
) net_contribution_draft_dr
,NVL2( contribution_draft_cr
,NVL2( contribution_draft_dr
,DECODE( SIGN(contribution_draft_cr - contribution_draft_dr)
,1
,contribution_draft_cr - contribution_draft_dr
,0
)
,DECODE( SIGN(contribution_draft_cr)
,1
,contribution_draft_cr
,0
)
)
,NVL2( contribution_draft_dr
,DECODE( SIGN(contribution_draft_dr)
,-1
,-contribution_draft_dr
,NULL
)
,NULL
)
) net_contribution_draft_cr
,DECODE( SIGN ( NVL(xba.beginning_balance_dr, 0) + NVL(ctr.contribution_dr, 0)
-NVL(xba.beginning_balance_cr,0) - NVL(ctr.contribution_cr, 0)
)
,1
, NVL(ctr.contribution_dr, 0) - NVL(ctr.contribution_cr, 0)
- NVL(xba.beginning_balance_cr, 0)
,- NVL(xba.beginning_balance_dr, 0)
) change_balance_dr
,DECODE( SIGN ( NVL(xba.beginning_balance_cr, 0) + NVL(ctr.contribution_cr , 0)
-NVL(xba.beginning_balance_dr, 0) - NVL(ctr.contribution_dr, 0)
)
,1
, NVL(ctr.contribution_cr, 0) - NVL(ctr.contribution_dr, 0)
- NVL(xba.beginning_balance_dr, 0)
,- NVL(xba.beginning_balance_cr, 0)
) change_balance_cr
,DECODE( SIGN ( NVL(xba.draft_beginning_balance_dr, 0)
+ NVL(ctr.contribution_draft_dr, 0)
- NVL(xba.draft_beginning_balance_cr, 0)
- NVL(ctr.contribution_draft_cr, 0)
)
,1
,NVL(ctr.contribution_draft_dr, 0) - NVL(ctr.contribution_draft_cr, 0)
- NVL(xba.draft_beginning_balance_cr, 0)
,-NVL(xba.draft_beginning_balance_dr, 0)
) change_draft_balance_dr
,DECODE( SIGN ( NVL(xba.draft_beginning_balance_cr, 0)
+ NVL(ctr.contribution_draft_cr, 0)
- NVL(xba.draft_beginning_balance_dr, 0)
- NVL(ctr.contribution_draft_dr, 0)
)
,1
,NVL(ctr.contribution_draft_cr, 0) - NVL(ctr.contribution_draft_dr, 0)
- NVL(xba.draft_beginning_balance_dr, 0)
,-NVL(xba.draft_beginning_balance_cr, 0)
) change_draft_balance_cr
,ctr.balance_status_eff_per_num
FROM xla_bal_ctrl_ctrbs_gt ctr
,xla_control_balances xba
WHERE ctr.application_id = p_application_id
AND ctr.ledger_id = p_ledger_id
AND ctr.effective_period_num = p_effective_period_num
AND xba.application_id (+)= ctr.application_id
AND xba.ledger_id (+)= ctr.ledger_id
AND xba.code_combination_id (+)= ctr.code_combination_id
AND xba.party_type_code (+)= ctr.party_type_code
AND xba.party_id (+)= ctr.party_id
AND xba.party_site_id (+)= ctr.party_site_id
AND xba.first_period_flag (+)= 'Y'
AND xba.period_year (+)>= ctr.period_year;
UPDATE xla_control_balances xba
SET xba.beginning_balance_dr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,xba.beginning_balance_dr
,NVL2( la_contribution_dr (i)
,NVL(xba.beginning_balance_dr, 0) + la_contribution_dr (i)
,xba.beginning_balance_dr
)
)
,xba.beginning_balance_cr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,xba.beginning_balance_cr
,NVL2( la_contribution_cr (i)
,NVL(xba.beginning_balance_cr, 0) + la_contribution_cr (i)
,xba.beginning_balance_cr
)
)
,xba.period_balance_dr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,NVL2( la_contribution_dr (i)
,NVL(xba.period_balance_dr, 0) + la_contribution_dr (i)
,xba.period_balance_dr
)
,xba.period_balance_dr
)
,xba.period_balance_cr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,NVL2( la_contribution_cr (i)
,NVL(xba.period_balance_cr, 0) + la_contribution_cr (i)
,xba.period_balance_cr
)
,xba.period_balance_cr
)
,xba.draft_beginning_balance_dr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,xba.draft_beginning_balance_dr
,NVL2( la_contribution_draft_dr (i)
,NVL(xba.draft_beginning_balance_dr, 0) + la_contribution_draft_dr (i)
,xba.draft_beginning_balance_dr
)
)
,xba.draft_beginning_balance_cr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,xba.draft_beginning_balance_cr
,NVL2( la_contribution_draft_cr (i)
,NVL(xba.draft_beginning_balance_cr, 0) + la_contribution_draft_cr (i)
,xba.draft_beginning_balance_cr
)
)
,xba.period_draft_balance_dr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,NVL2( la_contribution_draft_dr (i)
,NVL(xba.period_draft_balance_dr, 0) + la_contribution_draft_dr (i)
,xba.period_draft_balance_dr
)
,xba.period_draft_balance_dr
)
,xba.period_draft_balance_cr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,NVL2( la_contribution_draft_cr (i)
,NVL(xba.period_draft_balance_cr, 0) + la_contribution_draft_cr (i)
,xba.period_draft_balance_cr
)
,xba.period_draft_balance_cr
)
,first_period_flag =
DECODE( xba.first_period_flag
,'Y'
,DECODE( xba.period_name
,la_contribution_period_name (i)
,'Y'
,'N'
)
,'N'
)
,creation_date = g_date
,created_by = g_user_id
,last_update_date = g_date
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_update_date = g_date
,program_application_id = g_prog_appl_id
,program_id = g_prog_id
,request_id = g_req_id
WHERE xba.application_id = la_application_id(i)
AND xba.ledger_id = la_ledger_id(i)
AND xba.code_combination_id = la_code_combination_id(i)
AND xba.party_type_code = la_party_type_code(i)
AND xba.party_id = la_party_id(i)
AND xba.party_site_id = la_party_site_id(i)
AND xba.initial_balance_flag = 'N'
AND xba.period_name IN
( SELECT gps.period_name
FROM xla_bal_period_stats_gt gps
WHERE gps.ledger_id = la_ledger_id(i)
AND gps.period_year = la_contribution_period_year(i)
AND gps.effective_period_num >= la_contribution_eff_per_num(i)
)
AND la_contributed_period_year(i) = la_contribution_period_year(i);
,p_msg => l_row_count || 'Same year balances updated '
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_control_balances xba
SET xba.beginning_balance_dr =
DECODE( la_change_balance_dr(i)
,0
,xba.beginning_balance_dr
,NVL(xba.beginning_balance_dr, 0)
+ la_change_balance_dr(i)
)
,xba.beginning_balance_cr =
DECODE( la_change_balance_cr(i)
,0
,xba.beginning_balance_cr
,NVL(xba.beginning_balance_cr, 0)
+ la_change_balance_cr(i)
)
,xba.draft_beginning_balance_dr =
DECODE( la_change_draft_balance_dr(i)
,0
,xba.draft_beginning_balance_dr
,NVL(xba.draft_beginning_balance_dr, 0)
+ la_change_draft_balance_dr(i)
)
,xba.draft_beginning_balance_cr =
DECODE( la_change_draft_balance_cr(i)
,0
,xba.draft_beginning_balance_cr
,NVL(xba.draft_beginning_balance_cr, 0)
+ la_change_draft_balance_cr(i)
)
,xba.first_period_flag =
(SELECT gps2.first_period_in_year_flag
FROM xla_bal_period_stats_gt gps2
WHERE gps2.ledger_id = la_ledger_id(i)
AND gps2.period_name = xba.period_name
)
,creation_date = g_date
,created_by = g_user_id
,last_update_date = g_date
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_update_date = g_date
,program_application_id = g_prog_appl_id
,program_id = g_prog_id
,request_id = g_req_id
WHERE xba.application_id = la_application_id(i)
AND xba.ledger_id = la_ledger_id(i)
AND xba.code_combination_id = la_code_combination_id(i)
AND xba.party_type_code = la_party_type_code(i)
AND xba.party_id = la_party_id(i)
AND xba.party_site_id = la_party_site_id(i)
AND xba.initial_balance_flag = 'N'
AND xba.period_name IN
( SELECT gps.period_name
FROM xla_bal_period_stats_gt gps
WHERE gps.ledger_id = la_ledger_id(i)
AND gps.period_year = la_contributed_period_year(i)
)
AND la_contributed_period_year(i) > la_contribution_period_year(i);
|| ' following years balances updated '
,p_level => C_LEVEL_STATEMENT
);
FOR j IN (select nvl(beginning_balance_dr,0) + nvl(period_balance_dr,0) begin_dr
,nvl(beginning_balance_cr,0) + nvl(period_balance_cr,0) begin_cr
from(select xcb.*
from xla_control_balances xcb, gl_period_statuses gps
where xcb.application_id = la_application_id(i)
and xcb.application_id = gps.application_id
and xcb.ledger_id = gps.ledger_id
and xcb.ledger_id = la_ledger_id(i)
and xcb.period_name = gps.period_name
and xcb.code_combination_id = la_code_combination_id(i)
and xcb.party_type_code = la_party_type_code(i)
and xcb.party_id = la_party_id(i)
and xcb.party_site_id = la_party_site_id(i)
and gps.effective_period_num < la_contribution_eff_per_num(i)
order by gps.effective_period_num desc)
where rownum = 1) LOOP
l_begin_bal_dr := j.begin_dr;
INSERT INTO xla_control_balances xba
( application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
,period_name
,period_year
,first_period_flag
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,draft_beginning_balance_dr
,draft_beginning_balance_cr
,period_draft_balance_dr
,period_draft_balance_cr
,initial_balance_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
(
SELECT la_application_id(i)
,la_ledger_id(i)
,la_code_combination_id(i)
,la_party_type_code(i)
,la_party_id(i)
,la_party_site_id(i)
,gps.period_name
,gps.period_year
,DECODE( gps.first_period_in_year_flag
,'Y'
,'Y'
,CASE WHEN l_begin_bal_dr IS NULL AND l_begin_bal_cr IS NULL THEN
DECODE( gps.period_name
,la_contribution_period_name(i)
,'Y'
,'N'
)
ELSE 'N' END
)
,NULLIF(NVL(DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,NULL
,la_contribution_dr(i)
)
,la_net_contribution_dr(i)
),0) + nvl(l_begin_bal_dr,0)
,0) --beginning_balance_dr
,NULLIF(NVL(DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,NULL
,la_contribution_cr(i)
)
,la_net_contribution_cr(i)
),0) + nvl(l_begin_bal_cr,0)
,0) --beginning_balance_cr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,la_contribution_dr(i)
,NULL
)
,NULL
) --period_balance_dr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,la_contribution_cr(i)
,NULL
)
,NULL
) --period_balance_cr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,NULL
,la_contribution_draft_dr(i)
)
,la_net_contribution_draft_dr(i)
) --draft_beginning_balance_dr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,NULL
,la_contribution_draft_cr(i)
)
,la_net_contribution_draft_cr(i)
) --draft_beginning_balance_cr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,la_contribution_draft_dr(i)
,NULL
)
,NULL
) --period_draft_balance_dr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,la_contribution_draft_cr(i)
,NULL
)
,NULL
) --period_draft_balance_cr
,'N'
,g_date
,g_user_id
,g_date
,g_user_id
,g_login_id
,g_date
,g_prog_appl_id
,g_prog_id
,g_req_id
FROM xla_bal_period_stats_gt gps
,xla_control_balances xba
WHERE gps.ledger_id = la_ledger_id (i)
AND gps.effective_period_num >= la_contribution_eff_per_num(i)
AND gps.effective_period_num <= la_balance_stat_eff_per_num(i)
AND xba.application_id (+)= la_application_id(i)
AND xba.ledger_id (+)= la_ledger_id(i)
AND xba.code_combination_id (+)= la_code_combination_id(i)
AND xba.party_type_code (+)= la_party_type_code(i)
AND xba.party_id (+)= la_party_id(i)
AND xba.party_site_id (+)= la_party_site_id(i)
AND xba.period_name (+)= gps.period_name
AND xba.ledger_id IS NULL
);
,p_msg => l_row_count || 'New balances inserted '
,p_level => C_LEVEL_STATEMENT
);
SELECT ctr.application_id
,ctr.ledger_id
,NVL(xba.period_year, ctr.period_year) contributed_period_year
,ctr.period_name
,ctr.period_year
,ctr.effective_period_num
,ctr.code_combination_id
,ctr.analytical_criterion_code
,ctr.analytical_criterion_type_code
,ctr.amb_context_code
,ctr.ac1
,ctr.ac2
,ctr.ac3
,ctr.ac4
,ctr.ac5
,ctr.contribution_dr
,ctr.contribution_cr
,NVL2( contribution_dr
,NVL2( contribution_cr
,DECODE( SIGN(contribution_dr - contribution_cr)
,1
,contribution_dr - contribution_cr
,0
)
,DECODE( SIGN(contribution_dr)
,1
,contribution_dr
,0
)
)
,NVL2( contribution_cr
,DECODE( SIGN(contribution_cr)
,-1
,-contribution_cr
,NULL
)
,NULL
)
) net_contribution_dr
,NVL2( contribution_cr
,NVL2( contribution_dr
,DECODE( SIGN(contribution_cr - contribution_dr)
,1
,contribution_cr - contribution_dr
,0
)
,DECODE( SIGN(contribution_cr)
,1
,contribution_cr
,0
)
)
,NVL2( contribution_dr
,DECODE( SIGN(contribution_dr)
,-1
,-contribution_dr
,NULL
)
,NULL
)
) net_contribution_cr
,DECODE( SIGN ( NVL(xba.beginning_balance_dr, 0) + NVL(ctr.contribution_dr, 0)
-NVL(xba.beginning_balance_cr,0) - NVL(ctr.contribution_cr, 0)
)
,1
, NVL(ctr.contribution_dr, 0) - NVL(ctr.contribution_cr, 0)
- NVL(xba.beginning_balance_cr, 0)
,- NVL(xba.beginning_balance_dr, 0)
) change_balance_dr
,DECODE( SIGN ( NVL(xba.beginning_balance_cr, 0) + NVL(ctr.contribution_cr , 0)
-NVL(xba.beginning_balance_dr, 0) - NVL(ctr.contribution_dr, 0)
)
,1
, NVL(ctr.contribution_cr, 0) - NVL(ctr.contribution_dr, 0)
- NVL(xba.beginning_balance_dr, 0)
,- NVL(xba.beginning_balance_cr, 0)
) change_balance_cr
,ctr.balance_status_eff_per_num
,xbh.year_end_carry_forward_code
,ctr.account_type
FROM xla_bal_ac_ctrbs_gt ctr
,xla_analytical_hdrs_b xbh
,xla_ac_balances xba
WHERE ctr.application_id = p_application_id
AND ctr.ledger_id = p_ledger_id
AND ctr.effective_period_num = p_effective_period_num
AND xbh.analytical_criterion_code = ctr.analytical_criterion_code
AND xbh.analytical_criterion_type_code = ctr.analytical_criterion_type_code
AND xbh.amb_context_code = ctr.amb_context_code
AND xba.application_id (+) = ctr.application_id
AND xba.ledger_id (+) = ctr.ledger_id
AND xba.code_combination_id (+) = ctr.code_combination_id
AND xba.analytical_criterion_code (+) = ctr.analytical_criterion_code
AND xba.analytical_criterion_type_code (+) = ctr.analytical_criterion_type_code
AND xba.amb_context_code (+) = ctr.amb_context_code
AND NVL(xba.ac1 (+) ,'A') = NVL(ctr.ac1,'A')
AND NVL(xba.ac5 (+) ,'A') = NVL(ctr.ac5,'A')
AND NVL(xba.ac2 (+) ,'A') = NVL(ctr.ac2,'A')
AND NVL(xba.ac3 (+) ,'A') = NVL(ctr.ac3,'A')
AND NVL(xba.ac4 (+) ,'A') = NVL(ctr.ac4,'A')
AND xba.first_period_flag (+) = 'Y'
AND xba.period_year (+) >= ctr.period_year;
UPDATE xla_ac_balances xba
SET xba.beginning_balance_dr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,xba.beginning_balance_dr
,NVL2( la_contribution_dr (i)
,NVL(xba.beginning_balance_dr, 0) + la_contribution_dr (i)
,xba.beginning_balance_dr
)
)
,xba.beginning_balance_cr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,xba.beginning_balance_cr
,NVL2( la_contribution_cr (i)
,NVL(xba.beginning_balance_cr, 0) + la_contribution_cr (i)
,xba.beginning_balance_cr
)
)
,xba.period_balance_dr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,NVL2( la_contribution_dr (i)
,NVL(xba.period_balance_dr, 0) + la_contribution_dr (i)
,xba.period_balance_dr
)
,xba.period_balance_dr
)
,xba.period_balance_cr =
DECODE( xba.period_name
,la_contribution_period_name (i)
,NVL2( la_contribution_cr (i)
,NVL(xba.period_balance_cr, 0) + la_contribution_cr (i)
,xba.period_balance_cr
)
,xba.period_balance_cr
)
,first_period_flag =
DECODE( xba.first_period_flag
,'Y'
,DECODE( xba.period_name
,la_contribution_period_name (i)
,'Y'
,'N'
)
,'N'
)
,creation_date = g_date
,created_by = g_user_id
,last_update_date = g_date
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_update_date = g_date
,program_application_id = g_prog_appl_id
,program_id = g_prog_id
,request_id = g_req_id
WHERE xba.application_id = la_application_id(i)
AND xba.ledger_id = la_ledger_id(i)
AND xba.code_combination_id = la_code_combination_id(i)
AND xba.analytical_criterion_code = la_analytical_criterion_code(i)
AND xba.analytical_criterion_type_code
= la_anacri_type_code(i)
AND xba.amb_context_code = la_amb_context_code(i)
AND (xba.ac1 IS NULL or xba.ac1 = la_ac1(i))
AND (xba.ac2 IS NULL or xba.ac2 = la_ac2(i))
AND (xba.ac3 IS NULL or xba.ac3 = la_ac3(i))
AND (xba.ac4 IS NULL or xba.ac4 = la_ac4(i))
AND (xba.ac5 IS NULL or xba.ac5 = la_ac5(i))
AND xba.initial_balance_flag = 'N'
AND xba.period_name IN
( SELECT gps.period_name
FROM xla_bal_period_stats_gt gps
WHERE gps.ledger_id = la_ledger_id(i)
AND gps.period_year = la_contribution_period_year(i)
AND gps.effective_period_num >= la_contribution_eff_per_num(i)
)
AND la_contributed_period_year(i) = la_contribution_period_year(i);
,p_msg => l_row_count || 'Same year balances updated '
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_ac_balances xba
SET xba.beginning_balance_dr =
DECODE( la_change_balance_dr(i)
,0
,xba.beginning_balance_dr
,NVL(xba.beginning_balance_dr, 0)
+ la_change_balance_dr(i)
)
,xba.beginning_balance_cr =
DECODE( la_change_balance_cr(i)
,0
,xba.beginning_balance_cr
,NVL(xba.beginning_balance_cr, 0)
+ la_change_balance_cr(i)
)
,xba.first_period_flag =
(SELECT gps2.first_period_in_year_flag
FROM xla_bal_period_stats_gt gps2
WHERE gps2.ledger_id = la_ledger_id(i)
AND gps2.period_name = xba.period_name
)
,creation_date = g_date
,created_by = g_user_id
,last_update_date = g_date
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_update_date = g_date
,program_application_id = g_prog_appl_id
,program_id = g_prog_id
,request_id = g_req_id
WHERE xba.ledger_id = la_ledger_id(i)
AND xba.application_id = la_application_id(i)
AND xba.code_combination_id = la_code_combination_id(i)
AND xba.analytical_criterion_code = la_analytical_criterion_code(i)
AND xba.analytical_criterion_type_code
= la_anacri_type_code(i)
AND xba.amb_context_code = la_amb_context_code(i)
AND (xba.ac1 IS NULL or xba.ac1 = la_ac1(i))
AND (xba.ac2 IS NULL or xba.ac2 = la_ac2(i))
AND (xba.ac3 IS NULL or xba.ac3 = la_ac3(i))
AND (xba.ac4 IS NULL or xba.ac4 = la_ac4(i))
AND (xba.ac5 IS NULL or xba.ac5 = la_ac5(i))
AND xba.initial_balance_flag = 'N'
AND ( la_year_end_carry_forward_code(i) = 'A'
OR ( la_year_end_carry_forward_code(i) = 'B'
AND la_account_type(i) IN ('A', 'L', 'O')
)
)
AND xba.period_name IN
( SELECT gps.period_name
FROM xla_bal_period_stats_gt gps
WHERE gps.ledger_id = la_ledger_id(i)
AND gps.period_year = la_contributed_period_year(i)
)
AND la_contributed_period_year(i) > la_contribution_period_year(i);
|| ' following years balances updated '
,p_level => C_LEVEL_STATEMENT
);
INSERT INTO xla_ac_balances xba
( application_id
,ledger_id
,code_combination_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,period_name
,period_year
,first_period_flag
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,initial_balance_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
(
SELECT la_application_id(i)
,la_ledger_id(i)
,la_code_combination_id(i)
,la_analytical_criterion_code(i)
,la_anacri_type_code(i)
,la_amb_context_code(i)
,la_ac1(i)
,la_ac2(i)
,la_ac3(i)
,la_ac4(i)
,la_ac5(i)
,gps.period_name
,gps.period_year
,DECODE( gps.first_period_in_year_flag
,'Y'
,'Y'
,DECODE( gps.period_name
,la_contribution_period_name(i)
,'Y'
,'N'
)
)
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,NULL
,la_contribution_dr(i)
)
,la_net_contribution_dr(i)
) --beginning_balance_dr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,NULL
,la_contribution_cr(i)
)
,la_net_contribution_cr(i)
) --beginning_balance_cr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,la_contribution_dr(i)
,NULL
)
,NULL
) --period_balance_dr
,DECODE( gps.period_year
,la_contribution_period_year(i)
,DECODE( gps.period_name
,la_contribution_period_name(i)
,la_contribution_cr(i)
,NULL
)
,NULL
) --period_balance_cr
,'N'
,g_date
,g_user_id
,g_date
,g_user_id
,g_login_id
,g_date
,g_prog_appl_id
,g_prog_id
,g_req_id
FROM xla_bal_period_stats_gt gps
,xla_analytical_hdrs_b xbh
WHERE gps.ledger_id = la_ledger_id (i)
AND gps.effective_period_num >= la_contribution_eff_per_num(i)
AND gps.effective_period_num <= la_balance_stat_eff_per_num(i)
-- bug 6117987
AND NOT EXISTS
(
-- bug 7113937 Removed outer join in where clause
SELECT 1
FROM xla_ac_balances xba
WHERE xba.application_id = la_application_id(i)
AND xba.ledger_id = la_ledger_id (i)
AND xba.code_combination_id = la_code_combination_id(i)
AND xba.analytical_criterion_code = la_analytical_criterion_code(i)
AND xba.analytical_criterion_type_code
= la_anacri_type_code(i)
AND xba.amb_context_code = la_amb_context_code(i)
AND NVL(xba.ac1,C_DUMMY) = NVL(la_ac1(i),C_DUMMY)
AND NVL(xba.ac2,C_DUMMY) = NVL(la_ac2(i),C_DUMMY)
AND NVL(xba.ac3,C_DUMMY) = NVL(la_ac3(i),C_DUMMY)
AND NVL(xba.ac4,C_DUMMY) = NVL(la_ac4(i),C_DUMMY)
AND NVL(xba.ac5,C_DUMMY) = NVL(la_ac5(i),C_DUMMY)
AND xba.period_name = gps.period_name
)
AND xbh.analytical_criterion_code = la_analytical_criterion_code(i)
AND xbh.analytical_criterion_type_code = la_anacri_type_code(i)
AND xbh.amb_context_code = la_amb_context_code(i)
AND ( gps.period_year = la_contribution_period_year(i)
OR xbh.year_end_carry_forward_code = 'A'
OR ( xbh.year_end_carry_forward_code = 'B'
AND la_account_type(i) IN ('A', 'L', 'O')
)
)
);
,p_msg => l_row_count || 'New balances inserted '
,p_level => C_LEVEL_STATEMENT
);
INSERT INTO xla_control_balances xba
( application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
,period_name
,period_year
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,draft_beginning_balance_dr
,draft_beginning_balance_cr
,period_draft_balance_dr
,period_draft_balance_cr
,initial_balance_flag
,first_period_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
(
SELECT xba.application_id
,xba.ledger_id
,xba.code_combination_id
,xba.party_type_code
,xba.party_id
,xba.party_site_id
,gpsnew.period_name
,gpsnew.period_year
--disregarding indentation for readability
,DECODE( gpsnew.period_year
,gpsbs.period_year
,NVL2( xba.period_balance_dr
,NVL2( xba.beginning_balance_dr
,xba.beginning_balance_dr + xba.period_balance_dr
,xba.period_balance_dr
)
,xba.beginning_balance_dr
)
,DECODE( SIGN ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
-( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0) )
)
,1
,NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
- ( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0) )
)
) --beginning_balance_dr
,DECODE( gpsnew.period_year
,gpsbs.period_year
,NVL2( xba.period_balance_cr
,NVL2( xba.beginning_balance_cr
,xba.beginning_balance_cr + xba.period_balance_cr
,xba.period_balance_cr
)
,xba.beginning_balance_cr
)
,DECODE( SIGN ( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
-( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0) )
)
,1
,NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
- ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0) )
)
) --beginning_balance_cr
,NULL --period_balance_dr
,NULL --period_balance_cr
,DECODE( gpsnew.period_year
,gpsbs.period_year
,NVL2( xba.period_draft_balance_dr
,NVL2( xba.draft_beginning_balance_dr
,xba.draft_beginning_balance_dr + xba.period_draft_balance_dr
,xba.period_draft_balance_dr
)
,xba.draft_beginning_balance_dr
)
,DECODE( SIGN ( NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0)
-( NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0) )
)
,1
,NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0)
- ( NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0))
)
) --draft_beginning_balance_dr
,DECODE( gpsnew.period_year
,gpsbs.period_year
,NVL2( xba.period_draft_balance_cr
,NVL2( xba.draft_beginning_balance_cr
,xba.draft_beginning_balance_cr + xba.period_draft_balance_cr
,xba.period_draft_balance_cr
)
,xba.draft_beginning_balance_cr
)
,DECODE( SIGN ( NVL(xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0)
-( NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0) )
)
,1
,NVL (xba.draft_beginning_balance_cr, 0) + NVL(xba.period_draft_balance_cr, 0)
- ( NVL(xba.draft_beginning_balance_dr, 0) + NVL(xba.period_draft_balance_dr, 0) )
)
) --draft_beginning_balance_cr
,NULL --period_draft_balance_dr
,NULL --period_draft_balance_cr
,'N'
,(SELECT DECODE( MAX(gps2.effective_period_num)
,NULL
,'Y'
,'N'
)
FROM gl_period_statuses gps2
WHERE gps2.ledger_id = ledger_id
AND gps2.application_id = 101
AND gps2.closing_status IN ('O','C','P')
AND gps2.adjustment_period_flag = 'N'
AND gps2.period_year = gpsnew.period_year
AND gps2.effective_period_num < gpsnew.effective_period_num
)
,l_date
,l_user_id
,l_date
,l_user_id
,l_login_id
,l_date
,l_prog_appl_id
,l_prog_id
,NVL(l_req_id, -1)
FROM xla_balance_statuses xbs
,gl_period_statuses gpsbs
,xla_control_balances xba
,gl_period_statuses gpsnew
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = 'C'
AND xbs.effective_period_num < p_dest_effective_period_num
AND xbs.code_combination_id = p_code_combination_id
AND gpsbs.ledger_id = p_ledger_id
AND gpsbs.application_id = 101
AND gpsbs.effective_period_num = xbs.effective_period_num
AND xba.ledger_id = p_ledger_id
AND xba.application_id = p_application_id
AND xba.code_combination_id = p_code_combination_id
AND xba.party_type_code = p_party_type_code
AND xba.party_id = p_party_id
AND xba.period_name = gpsbs.period_name
AND gpsnew.ledger_id = p_ledger_id
AND gpsnew.application_id = 101
AND gpsnew.closing_status IN ('O', 'C', 'P')
AND gpsnew.adjustment_period_flag = 'N'
AND gpsnew.effective_period_num <= p_dest_effective_period_num
AND gpsnew.effective_period_num >
(
SELECT MAX(effective_period_num)
FROM xla_control_balances xbanew
,gl_period_statuses gpsint
WHERE xbanew.ledger_id = p_ledger_id
AND xbanew.application_id = p_application_id
AND xbanew.code_combination_id = p_code_combination_id
AND xbanew.party_type_code = p_party_type_code
AND xbanew.party_id = p_party_id
AND xbanew.party_site_id = xba.party_site_id
AND gpsint.ledger_id = p_ledger_id
AND gpsint.application_id = 101
AND gpsint.period_name = xbanew.period_name
)
);
INSERT INTO xla_ac_balances xba
( application_id
,ledger_id
,code_combination_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,period_name
,period_year
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,initial_balance_flag
,first_period_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_update_date
,program_application_id
,program_id
,request_id
)
(
SELECT xba.application_id
,xba.ledger_id
,xba.code_combination_id
,xba.analytical_criterion_code
,xba.analytical_criterion_type_code
,xba.amb_context_code
,xba.ac1
,xba.ac2
,xba.ac3
,xba.ac4
,xba.ac5
,gpsnew.period_name
,gpsnew.period_year
--disregarding indentation for readability
,DECODE( gpsnew.period_year
,gpsbs.period_year
,NVL2( xba.period_balance_dr
,NVL2( xba.beginning_balance_dr
,xba.beginning_balance_dr + xba.period_balance_dr
,xba.period_balance_dr
)
,xba.beginning_balance_dr
)
,DECODE( SIGN ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
-( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0) )
)
,1
,NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0)
- ( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0) )
)
) --beginning_balance_dr
,DECODE( gpsnew.period_year
,gpsbs.period_year
,NVL2( xba.period_balance_cr
,NVL2( xba.beginning_balance_cr
,xba.beginning_balance_cr + xba.period_balance_cr
,xba.period_balance_cr
)
,xba.beginning_balance_cr
)
,DECODE( SIGN ( NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
-( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0) )
)
,1
,NVL(xba.beginning_balance_cr, 0) + NVL(xba.period_balance_cr, 0)
- ( NVL(xba.beginning_balance_dr, 0) + NVL(xba.period_balance_dr, 0) )
)
) --beginning_balance_cr
,NULL --period_balance_dr
,NULL --period_balance_cr
,'N'
,(SELECT DECODE( MAX(gps2.effective_period_num)
,NULL
,'Y'
,'N'
)
FROM gl_period_statuses gps2
WHERE gps2.ledger_id = ledger_id
AND gps2.application_id = 101
AND gps2.closing_status IN ('O','C','P')
AND gps2.adjustment_period_flag = 'N'
AND gps2.period_year = gpsnew.period_year
AND gps2.effective_period_num < gpsnew.effective_period_num
)
,l_date
,l_user_id
,l_date
,l_user_id
,l_login_id
,l_date
,l_prog_appl_id
,l_prog_id
,NVL(l_req_id, -1)
FROM xla_balance_statuses xbs
,gl_period_statuses gpsbs
,xla_ac_balances xba
,gl_period_statuses gpsnew
,xla_analytical_hdrs_b xbh
WHERE xbs.application_id = p_application_id
AND xbs.ledger_id = p_ledger_id
AND xbs.balance_source_code = 'A'
AND xbs.effective_period_num < p_dest_effective_period_num
AND xbs.code_combination_id = p_code_combination_id
AND gpsbs.ledger_id = p_ledger_id
AND gpsbs.application_id = 101
AND gpsbs.effective_period_num = xbs.effective_period_num
AND xba.ledger_id = p_ledger_id
AND xba.application_id = p_application_id
AND xba.code_combination_id = p_code_combination_id
AND xba.analytical_criterion_code = p_analytical_criterion_code
AND xba.analytical_criterion_type_code = p_anacri_type_code
AND xba.amb_context_code = p_amb_context_code
AND xba.ac1 = p_ac1
AND xba.ac2 (+)= p_ac2
AND xba.ac3 (+)= p_ac3
AND xba.ac4 (+)= p_ac4
AND xba.ac5 (+)= p_ac5
AND xba.period_name = gpsbs.period_name
AND xbh.analytical_criterion_code = xba.analytical_criterion_code
AND xbh.analytical_criterion_type_code = xba.analytical_criterion_type_code
AND xbh.amb_context_code = xba.amb_context_code
AND ( xba.period_year = gpsnew.period_year
OR xbh.year_end_carry_forward_code = 'A'
OR ( xbh.year_end_carry_forward_code = 'B'
AND xbs.account_type IN ('A', 'L', 'O')
)
)
AND gpsnew.ledger_id = p_ledger_id
AND gpsnew.application_id = 101
AND gpsnew.closing_status IN ('O', 'C', 'P')
AND gpsnew.adjustment_period_flag = 'N'
AND gpsnew.effective_period_num <= p_dest_effective_period_num
AND gpsnew.effective_period_num >
(
SELECT MAX(effective_period_num)
FROM xla_ac_balances xbanew
,gl_period_statuses gpsint
WHERE xbanew.ledger_id = p_ledger_id
AND xbanew.application_id = p_application_id
AND xbanew.code_combination_id = p_code_combination_id
AND gpsint.ledger_id = p_ledger_id
AND gpsint.application_id = 101
AND gpsint.period_name = xbanew.period_name
)
);
PROCEDURE build_line_selection_dyn_stmts
( p_application_id IN INTEGER
,p_ledger_id IN INTEGER
,p_event_id IN INTEGER
,p_accounting_batch_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_code_combination_id IN INTEGER
,p_period_name IN VARCHAR2
,p_balance_source_code IN VARCHAR2
,p_balance_flag_pre_update IN VARCHAR2
,p_balance_flag_post_update IN VARCHAR2
,p_commit_flag IN VARCHAR2
,p_entry_locking_dyn_stmt OUT NOCOPY VARCHAR2
,p_line_selection_dyn_stmt OUT NOCOPY VARCHAR2
,p_locking_arg_array OUT NOCOPY t_array_varchar -- bug 7551435
,p_lck_bind_var_count OUT NOCOPY INTEGER -- bug 7551435
,p_selection_arg_array OUT NOCOPY t_array_varchar -- bug 7551435
,p_ins_bind_var_num OUT NOCOPY INTEGER -- bug 7551435
)
IS
l_log_module VARCHAR2 (2000); -- bug 7551435
l_log_module := C_DEFAULT_MODULE||'.build_line_selection_dyn_stmts';
' SELECT 1
FROM xla_ae_headers aeh
,xla_ae_lines ael
WHERE ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND aeh.accounting_entry_status_code IN (''D'', ''F'')
';
p_locking_arg_array(p_lck_bind_var_count):=p_balance_flag_pre_update;
p_locking_arg_array(p_lck_bind_var_count):=p_balance_flag_pre_update;
p_locking_arg_array(p_lck_bind_var_count):=p_balance_flag_pre_update;
p_locking_arg_array(p_lck_bind_var_count):=p_balance_flag_pre_update;
' FOR UPDATE NOWAIT';
p_line_selection_dyn_stmt :=
'
INSERT ALL
WHEN control_balance_flag = ''' || p_balance_flag_pre_update || '''
AND NVL( ''' || p_balance_source_code || ''', ''C'') = ''C''
THEN
INTO xla_bal_ctrl_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,NVL(party_site_id, -999)
,accounted_dr
,accounted_cr
)
WHEN analytical_balance_flag = ''' || p_balance_flag_pre_update || '''
AND NVL( ''' || p_balance_source_code || ''', ''A'') = ''A''
THEN
INTO xla_bal_anacri_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,NVL(ael.party_site_id,-999) party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND aeh.accounting_entry_status_code IN (''D'', ''F'')
-- AND ael.control_balance_flag = ''' || p_balance_flag_pre_update || '''
-- AND NVL( ''' || p_balance_source_code || ''', ''C'') = ''C''
';
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND ( ael.control_balance_flag = :' || p_ins_bind_var_num ;
p_selection_arg_array(p_ins_bind_var_num):=p_balance_flag_pre_update;
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' OR ael.analytical_balance_flag = :' || p_ins_bind_var_num||') ';
p_selection_arg_array(p_ins_bind_var_num):=p_balance_flag_pre_update;
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
'
AND ael.control_balance_flag = :' || p_ins_bind_var_num ;
p_selection_arg_array(p_ins_bind_var_num):=p_balance_flag_pre_update;
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
'
AND ael.analytical_balance_flag = :' || p_ins_bind_var_num ;
p_selection_arg_array(p_ins_bind_var_num):=p_balance_flag_pre_update;
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
'
AND aeh.ledger_id = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_ledger_id);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND aeh.period_name = :' || p_ins_bind_var_num ;
p_selection_arg_array(p_ins_bind_var_num):=p_period_name;
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND ael.code_combination_id = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_code_combination_id);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
'
AND ael.application_id = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_application_id);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND aeh.ae_header_id = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_ae_header_id);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND aeh.event_id = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_event_id);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND aeh.accounting_batch_id = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_accounting_batch_id);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND ael.ae_line_num = :' || p_ins_bind_var_num;
p_selection_arg_array(p_ins_bind_var_num):=to_char(p_ae_line_num);
p_line_selection_dyn_stmt := p_line_selection_dyn_stmt ||
' AND ROWNUM <= ' || C_BATCH_COMMIT_SIZE;
,p_msg => 'START of Line Selection statement'
,p_level => C_LEVEL_STATEMENT
);
,p_msg => SUBSTR(p_line_selection_dyn_stmt, 1, 1000)
,p_level => C_LEVEL_STATEMENT
);
,p_msg => SUBSTR(p_line_selection_dyn_stmt, 1001, 1000)
,p_level => C_LEVEL_STATEMENT
);
,p_msg => SUBSTR(p_line_selection_dyn_stmt, 2001, 1000)
,p_level => C_LEVEL_STATEMENT
);
,p_msg => 'END of Line Selection statement'
,p_level => C_LEVEL_STATEMENT
);
,p_msg => 'p_selection_arg_array['||i||']='||p_selection_arg_array(i)
,p_level => C_LEVEL_STATEMENT
);
(p_location => 'xla_balances_pkg.build_line_selection_dyn_stmts');
END build_line_selection_dyn_stmts;
,p_balance_flag_pre_update IN VARCHAR2
,p_entry_locking_dyn_stmt IN VARCHAR2
,p_line_selection_dyn_stmt IN VARCHAR2
,p_locking_arg_array IN t_array_varchar
,p_lck_bind_var_count IN INTEGER
,p_selection_arg_array IN t_array_varchar
,p_ins_bind_var_num IN INTEGER
)
RETURN INTEGER
IS
CURSOR lc_lock_entries_for_event_API
( p_application_id INTEGER
,p_balance_flag_pre_update VARCHAR2
)
IS
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,ael.party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_events xev
,xla_events_gt xeg
,xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE xev.application_id = p_application_id
AND xeg.event_id = xev.event_id
AND xeg.application_id = xev.application_id
AND xev.process_status_code in ('D', 'I')
AND aeh.event_id = xev.event_id
AND aeh.application_id = xev.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND aeh.accounting_entry_status_code IN ('D', 'F')
AND ( ael.control_balance_flag = p_balance_flag_pre_update
OR ael.analytical_balance_flag = p_balance_flag_pre_update
)
FOR UPDATE OF ael.ae_line_num NOWAIT;
,p_balance_flag_pre_update VARCHAR2
)
IS
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,ael.party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_events xev
,xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE xev.request_id = p_request_id
AND xev.application_id = p_application_id
AND xev.process_status_code IN ('D', 'P')
AND aeh.event_id = xev.event_id
AND aeh.application_id = xev.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND ( ael.control_balance_flag = p_balance_flag_pre_update
OR ael.analytical_balance_flag = p_balance_flag_pre_update
)
FOR UPDATE OF ael.ae_line_num NOWAIT;
,p_balance_flag_pre_update VARCHAR2
)
IS
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,ael.party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE aeh.application_id = p_application_id
AND aeh.entity_id = p_entity_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND aeh.accounting_entry_status_code IN ('D', 'F')
AND ( ael.control_balance_flag = p_balance_flag_pre_update
OR ael.analytical_balance_flag = p_balance_flag_pre_update
)
FOR UPDATE OF ael.ae_line_num NOWAIT;
DELETE
FROM xla_bal_ctrl_lines_gt;
DELETE
FROM xla_bal_anacri_lines_gt;
,p_balance_flag_pre_update => p_balance_flag_pre_update
);
INSERT ALL
WHEN control_balance_flag = p_balance_flag_pre_update
THEN
INTO xla_bal_ctrl_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
WHEN analytical_balance_flag = p_balance_flag_pre_update
THEN
INTO xla_bal_anacri_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,NVL(ael.party_site_id,-999) party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_events_gt xeg
,xla_events xe
,xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE xeg.application_id = p_application_id
AND aeh.application_id = xeg.application_id
AND aeh.event_id = xeg.event_id
AND xe.event_id = xeg.event_id
AND xe.application_id = xeg.application_id
AND xe.process_status_code in ('D', 'I')
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND aeh.accounting_entry_status_code IN ('D', 'F')
AND ( ael.control_balance_flag = p_balance_flag_pre_update
OR ael.analytical_balance_flag = p_balance_flag_pre_update
);
,p_balance_flag_pre_update => p_balance_flag_pre_update
);
INSERT ALL
WHEN control_balance_flag = p_balance_flag_pre_update
THEN
INTO xla_bal_ctrl_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
WHEN analytical_balance_flag = p_balance_flag_pre_update
THEN
INTO xla_bal_anacri_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,NVL(ael.party_site_id,-999) party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_events xev
,xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE xev.request_id = p_request_id
AND xev.application_id = p_application_id
AND xev.process_status_code IN ('D', 'P')
AND aeh.application_id = xev.application_id
AND aeh.event_id = xev.event_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND ( ael.control_balance_flag = p_balance_flag_pre_update
OR ael.analytical_balance_flag = p_balance_flag_pre_update
);
,p_balance_flag_pre_update => p_balance_flag_pre_update
);
INSERT ALL
WHEN control_balance_flag = p_balance_flag_pre_update
THEN
INTO xla_bal_ctrl_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,party_type_code
,party_id
,party_site_id
,accounted_dr
,accounted_cr
)
WHEN analytical_balance_flag = p_balance_flag_pre_update
THEN
INTO xla_bal_anacri_lines_gt
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
VALUES
( line_rowid
,ae_header_id
,ae_line_num
,application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,accounting_entry_status_code
,code_combination_id
,accounted_dr
,accounted_cr
)
SELECT ael.ROWID line_rowid
,aeh.ae_header_id
,ael.ae_line_num
,aeh.application_id
,aeh.ledger_id
,aeh.period_name
,gps.period_year
,gps.effective_period_num
,aeh.accounting_entry_status_code
,ael.control_balance_flag
,ael.analytical_balance_flag
,ael.code_combination_id
,ael.party_type_code
,ael.party_id
,NVL(ael.party_site_id,-999) party_site_id
,ael.accounted_dr
,ael.accounted_cr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,gl_period_statuses gps
WHERE aeh.application_id = p_application_id
AND aeh.entity_id = p_entity_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND gps.ledger_id = aeh.ledger_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND aeh.accounting_entry_status_code IN ('D', 'F')
AND ( ael.control_balance_flag = p_balance_flag_pre_update
OR ael.analytical_balance_flag = p_balance_flag_pre_update
);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6),p_selection_arg_array(7);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6),p_selection_arg_array(7),p_selection_arg_array(8);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),
p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6),
p_selection_arg_array(7),p_selection_arg_array(8),p_selection_arg_array(9);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6),p_selection_arg_array(7),p_selection_arg_array(8),
p_selection_arg_array(9);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),
p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6),
p_selection_arg_array(7),p_selection_arg_array(8),p_selection_arg_array(9),
p_selection_arg_array(10),p_selection_arg_array(11);
EXECUTE IMMEDIATE p_line_selection_dyn_stmt using p_selection_arg_array(1),
p_selection_arg_array(2),p_selection_arg_array(3),
p_selection_arg_array(4),p_selection_arg_array(5),p_selection_arg_array(6),
p_selection_arg_array(7),p_selection_arg_array(8),p_selection_arg_array(9),
p_selection_arg_array(10),p_selection_arg_array(11),
p_selection_arg_array(12);
,p_msg => 'Entries selected'
,p_level => C_LEVEL_EVENT
);
|| ' lines inserted in temp tables. '
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_bal_ac_ctrbs_gt;
INSERT
INTO xla_bal_ac_ctrbs_gt
( application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,code_combination_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,contribution_dr
,contribution_cr
,balance_status_eff_per_num
,account_type
)
(
SELECT xbct.application_id
,xbct.ledger_id
,xbct.period_name
,xbct.period_year
,xbct.effective_period_num
,xbct.code_combination_id
,xad.analytical_criterion_code
,xad.analytical_criterion_type_code
,xad.amb_context_code
,xad.ac1
,xad.ac2
,xad.ac3
,xad.ac4
,xad.ac5
,SUM(xbct.accounted_dr) contribution_dr
,SUM(xbct.accounted_cr) contribution_cr
,xbct.balance_status_eff_per_num
,xbct.account_type
FROM xla_bal_anacri_lines_gt xbct
,xla_ae_line_acs xad
WHERE xad.ae_header_id = xbct.ae_header_id
AND xad.ae_line_num = xbct.ae_line_num
GROUP BY xbct.application_id
,xbct.ledger_id
,xbct.effective_period_num
,xbct.period_name
,xbct.period_year
,xbct.balance_status_eff_per_num
,xbct.code_combination_id
,xad.analytical_criterion_code
,xad.analytical_criterion_type_code
,xad.amb_context_code
,xad.ac1
,xad.ac2
,xad.ac3
,xad.ac4
,xad.ac5
,xbct.balance_status_eff_per_num
,xbct.balance_status_code
,xbct.account_type
);
|| ' inserted into xla_bal_ac_ctrbs_gt'
,p_level => C_LEVEL_STATEMENT
);
INSERT
INTO xla_bal_ac_ctrbs_gt
( application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,code_combination_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,contribution_dr
,contribution_cr
,balance_status_eff_per_num
,account_type
)
(
SELECT xbct.application_id
,xbct.ledger_id
,xbct.period_name
,xbct.period_year
,xbct.effective_period_num
,xbct.code_combination_id
,xad.analytical_criterion_code
,xad.analytical_criterion_type_code
,xad.amb_context_code
,xad.ac1
,xad.ac2
,xad.ac3
,xad.ac4
,xad.ac5
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_dr
,NULL
)
) contribution_dr
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_cr
,NULL
)
) contribution_cr
,xbct.balance_status_eff_per_num
,xbct.account_type
FROM xla_bal_anacri_lines_gt xbct
,xla_ae_line_acs xad
WHERE xad.ae_header_id = xbct.ae_header_id
AND xad.ae_line_num = xbct.ae_line_num
GROUP BY xbct.application_id
,xbct.ledger_id
,xbct.effective_period_num
,xbct.period_name
,xbct.period_year
,xbct.balance_status_eff_per_num
,xbct.code_combination_id
,xad.analytical_criterion_code
,xad.analytical_criterion_type_code
,xad.amb_context_code
,xad.ac1
,xad.ac2
,xad.ac3
,xad.ac4
,xad.ac5
,xbct.balance_status_eff_per_num
,xbct.balance_status_code
,xbct.account_type
);
|| ' inserted into xla_bal_ac_ctrbs_gt'
,p_level => C_LEVEL_STATEMENT
);
INSERT
INTO xla_bal_ac_ctrbs_gt
( application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,code_combination_id
,analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5
,contribution_dr
,contribution_cr
,balance_status_eff_per_num
,account_type
)
(
SELECT xbct.application_id
,xbct.ledger_id
,xbct.period_name
,xbct.period_year
,xbct.effective_period_num
,xbct.code_combination_id
,xad.analytical_criterion_code
,xad.analytical_criterion_type_code
,xad.amb_context_code
,xad.ac1
,xad.ac2
,xad.ac3
,xad.ac4
,xad.ac5
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_dr
,NULL
)
) * -1 contribution_dr
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_cr
,NULL
)
) * -1 contribution_cr
,xbct.balance_status_eff_per_num
,xbct.account_type
FROM xla_bal_anacri_lines_gt xbct
,xla_ae_line_acs xad
WHERE xad.ae_header_id = xbct.ae_header_id
AND xad.ae_line_num = xbct.ae_line_num
GROUP BY xbct.application_id
,xbct.ledger_id
,xbct.effective_period_num
,xbct.period_name
,xbct.period_year
,xbct.balance_status_eff_per_num
,xbct.code_combination_id
,xad.analytical_criterion_code
,xad.analytical_criterion_type_code
,xad.amb_context_code
,xad.ac1
,xad.ac2
,xad.ac3
,xad.ac4
,xad.ac5
,xbct.balance_status_eff_per_num
,xbct.balance_status_code
,xbct.account_type
);
|| ' inserted into xla_bal_ac_ctrbs_gt'
,p_level => C_LEVEL_STATEMENT
);
|| ' inserted into xla_bal_ac_ctrbs_gt'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_bal_ctrl_ctrbs_gt;
INSERT
INTO xla_bal_ctrl_ctrbs_gt
( application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,code_combination_id
,party_type_code
,party_id
,party_site_id
,contribution_dr
,contribution_cr
,contribution_draft_dr
,contribution_draft_cr
,balance_status_eff_per_num
)
(
SELECT xbct.application_id
,xbct.ledger_id
,xbct.period_name
,xbct.period_year
,xbct.effective_period_num
,xbct.code_combination_id
,xbct.party_type_code
,xbct.party_id
,xbct.party_site_id
,SUM(xbct.accounted_dr) contribution_dr
,SUM(xbct.accounted_cr) contribution_cr
,SUM(xbct.accounted_dr) * -1 contribution_draft_dr
,SUM(xbct.accounted_cr) * -1 contribution_draft_cr
,xbct.balance_status_eff_per_num
FROM xla_bal_ctrl_lines_gt xbct
GROUP BY xbct.application_id
,xbct.ledger_id
,xbct.effective_period_num
,xbct.period_name
,xbct.period_year
,xbct.balance_status_eff_per_num
,xbct.code_combination_id
,xbct.party_type_code
,xbct.party_id
,xbct.party_site_id
,xbct.balance_status_eff_per_num
,xbct.balance_status_code
);
INSERT
INTO xla_bal_ctrl_ctrbs_gt
( application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,code_combination_id
,party_type_code
,party_id
,party_site_id
,contribution_dr
,contribution_cr
,contribution_draft_dr
,contribution_draft_cr
,balance_status_eff_per_num
)
(
SELECT xbct.application_id
,xbct.ledger_id
,xbct.period_name
,xbct.period_year
,xbct.effective_period_num
,xbct.code_combination_id
,xbct.party_type_code
,xbct.party_id
,xbct.party_site_id
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_dr
,NULL
)
) contribution_dr
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_cr
,NULL
)
) contribution_cr
,SUM(DECODE( xbct.accounting_entry_status_code
,'D'
,xbct.accounted_dr
,NULL
)
) contribution_draft_dr
,SUM(DECODE( xbct.accounting_entry_status_code
,'D'
,xbct.accounted_cr
,NULL
)
) contribution_draft_cr
,xbct.balance_status_eff_per_num
FROM xla_bal_ctrl_lines_gt xbct
GROUP BY xbct.application_id
,xbct.ledger_id
,xbct.effective_period_num
,xbct.period_name
,xbct.period_year
,xbct.balance_status_eff_per_num
,xbct.code_combination_id
,xbct.party_type_code
,xbct.party_id
,xbct.party_site_id
,xbct.balance_status_eff_per_num
,xbct.balance_status_code
);
INSERT
INTO xla_bal_ctrl_ctrbs_gt
( application_id
,ledger_id
,period_name
,period_year
,effective_period_num
,code_combination_id
,party_type_code
,party_id
,party_site_id
,contribution_dr
,contribution_cr
,contribution_draft_dr
,contribution_draft_cr
,balance_status_eff_per_num
)
(
SELECT xbct.application_id
,xbct.ledger_id
,xbct.period_name
,xbct.period_year
,xbct.effective_period_num
,xbct.code_combination_id
,xbct.party_type_code
,xbct.party_id
,xbct.party_site_id
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_dr
,NULL
)
) * -1 contribution_dr
,SUM(DECODE( xbct.accounting_entry_status_code
,'F'
,xbct.accounted_cr
,NULL
)
) * -1 contribution_cr
,SUM(DECODE( xbct.accounting_entry_status_code
,'D'
,xbct.accounted_dr
,NULL
)
) * -1 contribution_draft_dr
,SUM(DECODE( xbct.accounting_entry_status_code
,'D'
,xbct.accounted_cr
,NULL
)
) * -1 contribution_draft_cr
,xbct.balance_status_eff_per_num
FROM xla_bal_ctrl_lines_gt xbct
GROUP BY xbct.application_id
,xbct.ledger_id
,xbct.effective_period_num
,xbct.period_name
,xbct.period_year
,xbct.balance_status_eff_per_num
,xbct.code_combination_id
,xbct.party_type_code
,xbct.party_id
,xbct.party_site_id
,xbct.balance_status_eff_per_num
,xbct.balance_status_code
);
' inserted into xla_bal_ctrl_ctrbs_gt'
,p_level => C_LEVEL_STATEMENT
);
PROCEDURE insert_ledger_period_statuses
( p_ledger_id IN INTEGER
,p_equal_to_eff_per_num IN INTEGER
,p_grt_or_equal_to_eff_per_num IN INTEGER
,p_grteq_1_diff_2_arg1 IN INTEGER
,p_grteq_1_diff_2_arg2 IN INTEGER
,p_grteq_1_less_2_arg1 IN INTEGER
,p_grteq_1_less_2_arg2 IN INTEGER
)
IS
l_row_count NUMBER;
l_log_module := C_DEFAULT_MODULE||'.insert_ledger_period_statuses';
INSERT
INTO xla_bal_period_stats_gt
( ledger_id
,period_name
,effective_period_num
,period_year
,first_period_in_year_flag
)
(
SELECT gps.ledger_id
,gps.period_name
,gps.effective_period_num
,gps.period_year
,( SELECT NVL2( MAX(gps2.effective_period_num)
,'N'
,'Y'
)
FROM gl_period_statuses gps2
WHERE gps2.application_id = 101
AND gps2.ledger_id = gps.ledger_id
AND gps2.closing_status IN ('O','C','P')
AND gps2.adjustment_period_flag = 'N'
AND gps2.period_year = gps.period_year
AND gps2.effective_period_num < gps.effective_period_num
) first_period_in_year_flag
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.closing_status IN ('O','C','P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num = p_equal_to_eff_per_num
AND (gps.ledger_id, gps.period_name)
NOT IN (SELECT xbp.ledger_id, xbp.period_name
FROM xla_bal_period_stats_gt xbp
)
);
INSERT
INTO xla_bal_period_stats_gt
( ledger_id
,period_name
,effective_period_num
,period_year
,first_period_in_year_flag
)
(
SELECT gps.ledger_id
,gps.period_name
,gps.effective_period_num
,gps.period_year
,( SELECT NVL2( MAX(gps2.effective_period_num)
,'N'
,'Y'
)
FROM gl_period_statuses gps2
WHERE gps2.application_id = 101
AND gps2.ledger_id = gps.ledger_id
AND gps2.closing_status IN ('O','C','P')
AND gps2.adjustment_period_flag = 'N'
AND gps2.period_year = gps.period_year
AND gps2.effective_period_num < gps.effective_period_num
) first_period_in_year_flag
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.closing_status IN ('O','C','P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num >= p_grt_or_equal_to_eff_per_num
AND (gps.ledger_id, gps.period_name)
NOT IN (SELECT xbp.ledger_id, xbp.period_name
FROM xla_bal_period_stats_gt xbp
)
);
INSERT
INTO xla_bal_period_stats_gt
( ledger_id
,period_name
,effective_period_num
,period_year
,first_period_in_year_flag
)
(
SELECT gps.ledger_id
,gps.period_name
,gps.effective_period_num
,gps.period_year
,( SELECT NVL2( MAX(gps2.effective_period_num)
,'N'
,'Y'
)
FROM gl_period_statuses gps2
WHERE gps2.application_id = 101
AND gps2.ledger_id = gps.ledger_id
AND gps2.closing_status IN ('O','C','P')
AND gps2.adjustment_period_flag = 'N'
AND gps2.period_year = gps.period_year
AND gps2.effective_period_num < gps.effective_period_num
) first_period_in_year_flag
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.closing_status IN ('O','C','P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num >= p_grteq_1_diff_2_arg1
AND gps.effective_period_num <> p_grteq_1_diff_2_arg2
AND (gps.ledger_id, gps.period_name)
NOT IN (SELECT xbp.ledger_id, xbp.period_name
FROM xla_bal_period_stats_gt xbp
)
);
INSERT
INTO xla_bal_period_stats_gt
( ledger_id
,period_name
,effective_period_num
,period_year
,first_period_in_year_flag
)
(
SELECT gps.ledger_id
,gps.period_name
,gps.effective_period_num
,gps.period_year
,( SELECT NVL2( MAX(gps2.effective_period_num)
,'N'
,'Y'
)
FROM gl_period_statuses gps2
WHERE gps2.application_id = 101
AND gps2.ledger_id = gps.ledger_id
AND gps2.closing_status IN ('O','C','P')
AND gps2.adjustment_period_flag = 'N'
AND gps2.period_year = gps.period_year
AND gps2.effective_period_num < gps.effective_period_num
) first_period_in_year_flag
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.closing_status IN ('O','C','P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num >= p_grteq_1_less_2_arg1
AND gps.effective_period_num < p_grteq_1_less_2_arg2
AND (gps.ledger_id, gps.period_name)
NOT IN (SELECT xbp.ledger_id, xbp.period_name
FROM xla_bal_period_stats_gt xbp
)
);
|| ' inserted into xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
(p_location => 'xla_balances_pkg.insert_ledger_period_statuses');
END insert_ledger_period_statuses;
DELETE
FROM xla_bal_period_stats_gt xbst
WHERE xbst.ledger_id = p_ledger_id;
' deleted from xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => p_first_eff_per_num
,p_grt_or_equal_to_eff_per_num => NULL
,p_grteq_1_diff_2_arg1 => NULL
,p_grteq_1_diff_2_arg2 => NULL
,p_grteq_1_less_2_arg1 => NULL
,p_grteq_1_less_2_arg2 => NULL
);
DELETE
FROM xla_bal_period_stats_gt xbst
WHERE xbst.ledger_id = p_ledger_id
AND ( xbst.effective_period_num < p_first_eff_per_num
OR xbst.effective_period_num > p_first_eff_per_num
);
' deleted from xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_bal_period_stats_gt xbst
WHERE xbst.ledger_id = p_ledger_id
AND xbst.effective_period_num > p_first_eff_per_num;
' deleted from xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_bal_period_stats_gt xbst
WHERE xbst.ledger_id = p_ledger_id;
' deleted from xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => p_first_eff_per_num
,p_grt_or_equal_to_eff_per_num => NULL
,p_grteq_1_diff_2_arg1 => NULL
,p_grteq_1_diff_2_arg2 => NULL
,p_grteq_1_less_2_arg1 => NULL
,p_grteq_1_less_2_arg2 => NULL
);
DELETE
FROM xla_bal_period_stats_gt xbst
WHERE xbst.ledger_id = p_ledger_id
AND xbst.effective_period_num < p_first_eff_per_num;
' deleted from xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => NULL
,p_grt_or_equal_to_eff_per_num => p_first_eff_per_num
,p_grteq_1_diff_2_arg1 => NULL
,p_grteq_1_diff_2_arg2 => NULL
,p_grteq_1_less_2_arg1 => NULL
,p_grteq_1_less_2_arg2 => NULL
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => NULL
,p_grt_or_equal_to_eff_per_num => NULL
,p_grteq_1_diff_2_arg1 => p_first_eff_per_num
,p_grteq_1_diff_2_arg2 => g_cached_ledgers(p_ledger_id)
,p_grteq_1_less_2_arg1 => NULL
,p_grteq_1_less_2_arg2 => NULL
);
DELETE
FROM xla_bal_period_stats_gt xbst
WHERE xbst.ledger_id = p_ledger_id
AND xbst.effective_period_num < p_first_eff_per_num;
' deleted from xla_bal_period_stats_gt'
,p_level => C_LEVEL_STATEMENT
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => NULL
,p_grt_or_equal_to_eff_per_num => NULL
,p_grteq_1_diff_2_arg1 => NULL
,p_grteq_1_diff_2_arg2 => NULL
,p_grteq_1_less_2_arg1 => p_first_eff_per_num
,p_grteq_1_less_2_arg2 => g_cached_ledgers(p_ledger_id)
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => p_first_eff_per_num
,p_grt_or_equal_to_eff_per_num => NULL
,p_grteq_1_diff_2_arg1 => NULL
,p_grteq_1_diff_2_arg2 => NULL
,p_grteq_1_less_2_arg1 => NULL
,p_grteq_1_less_2_arg2 => NULL
);
insert_ledger_period_statuses
( p_ledger_id => p_ledger_id
,p_equal_to_eff_per_num => NULL
,p_grt_or_equal_to_eff_per_num => p_first_eff_per_num
,p_grteq_1_diff_2_arg1 => NULL
,p_grteq_1_diff_2_arg2 => NULL
,p_grteq_1_less_2_arg1 => NULL
,p_grteq_1_less_2_arg2 => NULL
);
,p_update_mode IN VARCHAR2
,p_balance_source_code IN VARCHAR2
,p_called_by_flag IN VARCHAR2
,p_commit_flag IN VARCHAR2
)
RETURN BOOLEAN
IS
/*======================================================================+
| |
| Private Procedure |
| |
| Description |
| ----------- |
| |
| |
| Pseudo-code |
| ----------- |
| |
| |
| Open issues |
| ----------- |
| |
| |
+======================================================================*/
l_temp_rows_inserted INTEGER;
l_balance_flag_pre_update VARCHAR2(1);
l_balance_flag_post_update VARCHAR2(1);
l_line_selection_dyn_stmt VARCHAR2(4000);
l_selection_arg_array t_array_varchar;
,p_msg => 'p_update_mode :' || p_update_mode
,p_level => C_LEVEL_STATEMENT
);
IF p_update_mode = 'A'
THEN
l_balance_flag_pre_update := 'P';
l_balance_flag_post_update := 'Y';
ELSIF p_update_mode = 'D'
THEN
l_balance_flag_pre_update := 'Y';
l_balance_flag_post_update := 'P';
ELSIF p_update_mode = 'F'
THEN
IF p_commit_flag = 'Y'
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' ||
'Unsupported p_update_mode: ' || p_update_mode || ' with p_commit_flag: ' ||
p_commit_flag
,p_level => C_LEVEL_EXCEPTION
);
'Unsupported p_update_mode: ' || p_update_mode || ' with p_commit_flag: ' ||
p_commit_flag);
l_balance_flag_pre_update := 'Y';
l_balance_flag_post_update := 'Y';
ELSIF p_update_mode = 'M'
THEN
l_balance_flag_pre_update := 'P';
l_balance_flag_post_update := 'Y';
'Unkown p_update_mode value: ' || p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
'Unkown p_update_mode value: ' || p_update_mode);
build_line_selection_dyn_stmts
( p_application_id => p_application_id
,p_ledger_id => p_ledger_id
,p_event_id => p_event_id
,p_accounting_batch_id => p_accounting_batch_id
,p_ae_header_id => p_ae_header_id
,p_ae_line_num => p_ae_line_num
,p_code_combination_id => p_code_combination_id
,p_period_name => p_period_name
,p_balance_source_code => p_balance_source_code
,p_balance_flag_pre_update => l_balance_flag_pre_update
,p_balance_flag_post_update => l_balance_flag_post_update
,p_commit_flag => p_commit_flag
,p_entry_locking_dyn_stmt => l_entry_locking_dyn_stmt
,p_line_selection_dyn_stmt => l_line_selection_dyn_stmt
,p_locking_arg_array => l_locking_arg_array
,p_lck_bind_var_count => l_lck_bind_var_count
,p_selection_arg_array => l_selection_arg_array
,p_ins_bind_var_num => l_ins_bind_var_num
);
l_temp_rows_inserted := load_balance_temp_tables
( p_application_id => p_application_id
,p_request_id => p_request_id
,p_entity_id => p_entity_id
,p_called_by_flag => p_called_by_flag
,p_balance_flag_pre_update => l_balance_flag_pre_update
,p_entry_locking_dyn_stmt => l_entry_locking_dyn_stmt
,p_line_selection_dyn_stmt => l_line_selection_dyn_stmt
,p_locking_arg_array => l_locking_arg_array
,p_lck_bind_var_count => l_lck_bind_var_count
,p_selection_arg_array => l_selection_arg_array
,p_ins_bind_var_num => l_ins_bind_var_num
) ;
IF l_temp_rows_inserted = 0
THEN
l_result := TRUE;
ELSIF l_temp_rows_inserted < C_BATCH_COMMIT_SIZE
-- not accurate because of the multi-insert
-- but it works for small calculations
-- which is when the check is more useful
THEN
l_exit_after_calculation := TRUE;
IF l_temp_rows_inserted = 0
THEN
l_result := TRUE;
IF p_update_mode <> 'M'
THEN
IF p_commit_flag = 'Y'
THEN
--If commit flag is set then the balance calculation was not called in
--online mode. Hence we have time to call the open period routine.
FOR l_apps_ldgr IN
(
SELECT DISTINCT
xbct.application_id
,xbct.ledger_id
FROM xla_bal_ctrl_lines_gt xbct
UNION
SELECT DISTINCT
xbat.application_id
,xbat.ledger_id
FROM xla_bal_anacri_lines_gt xbat
)
LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'Moving bals forward for ledger id: '
|| l_apps_ldgr.ledger_id
,p_level => C_LEVEL_STATEMENT
);
END IF; --p_update_mode
UPDATE xla_bal_ctrl_lines_gt xbct
SET (
balance_status_code
,balance_status_eff_per_num
)
=
(
SELECT xbs.balance_status_code
,xbs.effective_period_num
FROM xla_balance_statuses xbs
WHERE xbs.application_id = xbct.application_id
AND xbs.ledger_id = xbct.ledger_id
AND xbs.code_combination_id = xbct.code_combination_id
AND xbs.balance_source_code = 'C'
);
' updated in xla_bal_ctrl_lines_gt'
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_bal_anacri_lines_gt xbat
SET (
balance_status_code
,balance_status_eff_per_num
,account_type
)
=
(
SELECT xbs.balance_status_code
,xbs.effective_period_num
,xbs.account_type
FROM xla_balance_statuses xbs
WHERE xbs.application_id = xbat.application_id
AND xbs.ledger_id = xbat.ledger_id
AND xbs.code_combination_id = xbat.code_combination_id
AND xbs.balance_source_code = 'A'
);
' updated in xla_bal_anacri_lines_gt'
,p_level => C_LEVEL_STATEMENT
);
IF p_update_mode <> 'M'
THEN
--Remove the lines for which the balance status is in maintenance mode.
DELETE
FROM xla_bal_ctrl_lines_gt xbct
WHERE xbct.balance_status_code = 'M';
' lines deleted from xla_bal_ctrl_lines_gt'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_bal_anacri_lines_gt xbat
WHERE xbat.balance_status_code = 'M';
' lines deleted from xla_bal_anacri_lines_gt'
,p_level => C_LEVEL_STATEMENT
);
IF p_update_mode = 'A'
OR p_update_mode = 'M'
THEN
l_operation_code := 'A';
ELSIF p_update_mode = 'D'
THEN
l_operation_code := 'R';
ELSIF p_update_mode = 'F'
THEN
l_operation_code := 'F';
'Unsupported p_update_mode value: ' || p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
'Unsupported p_update_mode value: ' || p_update_mode);
l_temp_rows_inserted := load_ctrl_secondary_temp_tabs
( p_operation_code => l_operation_code
);
IF l_temp_rows_inserted > 0
THEN
FOR i IN
(
SELECT DISTINCT
bclt.application_id
,bclt.ledger_id
,bclt.effective_period_num
FROM xla_bal_ctrl_lines_gt bclt
ORDER BY bclt.application_id
,bclt.ledger_id
,bclt.effective_period_num
)
LOOP
IF p_update_mode = 'M'
THEN
cache_ledger_period_statuses
( p_ledger_id => i.ledger_id
,p_first_eff_per_num => i.effective_period_num
,p_load_single_period => TRUE
);
IF p_update_mode <> 'M'
AND p_commit_flag = 'N'
THEN
--control balances
FOR m IN
(
SELECT DISTINCT
bcct.code_combination_id
,bcct.party_type_code
,bcct.party_id
FROM xla_bal_ctrl_ctrbs_gt bcct
WHERE bcct.application_id = i.application_id
AND bcct.ledger_id = i.ledger_id
AND bcct.effective_period_num = i.effective_period_num
AND bcct.balance_status_eff_per_num < bcct.effective_period_num
)
LOOP
IF NOT move_identified_bals_forward
( p_application_id => i.application_id
,p_ledger_id => i.ledger_id
,p_code_combination_id => m.code_combination_id
,p_dest_effective_period_num => i.effective_period_num
,p_balance_source_code => 'C'
,p_party_type_code => m.party_type_code
,p_party_id => m.party_id
,p_analytical_criterion_code => NULL
,p_anacri_type_code => NULL
,p_amb_context_code => NULL
,p_ac1 => NULL
,p_ac2 => NULL
,p_ac3 => NULL
,p_ac4 => NULL
,p_ac5 => NULL
)
THEN
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'move_identified_bals_forward failed'
,p_level => C_LEVEL_STATEMENT
);
IF p_update_mode <> 'F'
THEN
g_date := SYSDATE;
UPDATE xla_ae_lines ael
SET ael.control_balance_flag = l_balance_flag_post_update
,ael.last_update_date = g_date
,ael.last_updated_by = g_user_id
,ael.last_update_login = g_login_id
,ael.program_update_date = g_date
,ael.program_application_id = g_prog_appl_id
,ael.program_id = g_prog_id
,ael.request_id = g_req_id
WHERE
( ael.ROWID
)
IN
( SELECT xbct.line_rowid
FROM xla_bal_ctrl_lines_gt xbct
)
AND EXISTS (SELECT 1
FROM xla_ae_headers aeh
,gl_period_statuses gps
WHERE ael.ae_header_id = aeh.ae_header_id
AND aeh.application_id = ael.application_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND gps.ledger_id = aeh.ledger_id
AND gps.closing_status IN ('O','C','P'));
|| l_balance_flag_post_update
,p_level => C_LEVEL_STATEMENT
);
END IF; --p_update_mode
END IF; --l_temp_rows_inserted
l_temp_rows_inserted := load_alyt_secondary_temp_tabs
( p_operation_code => l_operation_code
);
IF l_temp_rows_inserted > 0
THEN
FOR i IN
(
SELECT DISTINCT
bclt.application_id
,bclt.ledger_id
,bclt.effective_period_num
FROM xla_bal_anacri_lines_gt bclt
)
LOOP
IF p_update_mode = 'M'
THEN
cache_ledger_period_statuses
( p_ledger_id => i.ledger_id
,p_first_eff_per_num => i.effective_period_num
,p_load_single_period => TRUE
);
IF p_update_mode <> 'M'
AND p_commit_flag = 'N'
THEN
--analytical balances
FOR m IN
(
SELECT DISTINCT
bact.code_combination_id
,bact.analytical_criterion_code
,bact.analytical_criterion_type_code
,bact.amb_context_code
,bact.ac1
,bact.ac2
,bact.ac3
,bact.ac4
,bact.ac5
FROM xla_bal_ac_ctrbs_gt bact
WHERE bact.application_id = i.application_id
AND bact.ledger_id = i.ledger_id
AND bact.effective_period_num = i.effective_period_num
AND bact.balance_status_eff_per_num < bact.effective_period_num
)
LOOP
IF NOT move_identified_bals_forward
( p_application_id => i.application_id
,p_ledger_id => i.ledger_id
,p_code_combination_id => m.code_combination_id
,p_dest_effective_period_num => i.effective_period_num
,p_balance_source_code => 'A'
,p_party_type_code => NULL
,p_party_id => NULL
,p_analytical_criterion_code => m.analytical_criterion_code
,p_anacri_type_code => m.analytical_criterion_type_code
,p_amb_context_code => m.amb_context_code
,p_ac1 => m.ac1
,p_ac2 => m.ac2
,p_ac3 => m.ac3
,p_ac4 => m.ac4
,p_ac5 => m.ac5
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'move_identified_bals_forward failed'
,p_level => C_LEVEL_ERROR
);
IF p_update_mode <> 'F'
THEN
g_date := SYSDATE;
UPDATE xla_ae_lines ael
SET ael.analytical_balance_flag = l_balance_flag_post_update
,ael.last_update_date = g_date
,ael.last_updated_by = g_user_id
,ael.last_update_login = g_login_id
,ael.program_update_date = g_date
,ael.program_application_id = g_prog_appl_id
,ael.program_id = g_prog_id
,ael.request_id = g_req_id
WHERE ( ael.application_id
,ael.ae_header_id
,ael.ae_line_num
)
IN
( SELECT xbct.application_id
,xbct.ae_header_id
,xbct.ae_line_num
FROM xla_bal_anacri_lines_gt xbct
)
AND EXISTS (SELECT 1
FROM xla_ae_headers aeh
,gl_period_statuses gps
WHERE ael.ae_header_id = aeh.ae_header_id
AND aeh.application_id = ael.application_id
AND gps.application_id = 101
AND gps.period_name = aeh.period_name
AND gps.ledger_id = aeh.ledger_id
AND gps.closing_status IN ('O','C','P')); --Bug 7493686
|| l_balance_flag_post_update
,p_level => C_LEVEL_STATEMENT
);
END IF; --p_update_mode
END IF; --l_temp_rows_inserted
FUNCTION event_set_update
(
p_update_mode IN VARCHAR2
) RETURN BOOLEAN
IS
l_return_value BOOLEAN;
l_log_module := C_DEFAULT_MODULE||'.event_set_update';
IF p_update_mode IS NULL
OR p_update_mode NOT IN ('A', 'D')
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' ||
'Unsupported value for p_update_mode: ' || p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
'Unsupported value for p_update_mode: ' || p_update_mode);
,p_update_mode => p_update_mode
,p_balance_source_code => NULL
,p_called_by_flag => NULL
,p_commit_flag => 'N'
);
(p_location => 'xla_balances_pkg.event_set_update');
END event_set_update;
FUNCTION single_update
(
p_application_id IN INTEGER
,p_ae_header_id IN INTEGER
,p_ae_line_num IN INTEGER
,p_update_mode IN VARCHAR2
) RETURN BOOLEAN
IS
l_return_value BOOLEAN ;
l_log_module := C_DEFAULT_MODULE||'.single_update';
IF p_update_mode IS NULL
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' ||
'p_update_mode cannot be NULL'
,p_level => C_LEVEL_EXCEPTION
);
'p_update_mode cannot be NULL');
ELSIF p_update_mode NOT IN ('A', 'D', 'F')
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' ||
'Unsupported value for p_update_mode: ' || p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
'Unsupported value for p_update_mode: ' || p_update_mode);
,p_update_mode => p_update_mode
,p_balance_source_code => NULL
,p_called_by_flag => NULL
,p_commit_flag => 'N'
);
(p_location => 'xla_balances_pkg.single_update');
END single_update;
PROCEDURE massive_update_srs
( p_errbuf OUT NOCOPY VARCHAR2
,p_retcode OUT NOCOPY NUMBER
,p_application_id IN NUMBER
,p_dummy IN VARCHAR2
,p_ledger_id IN NUMBER
,p_accounting_batch_id IN NUMBER
,p_update_mode IN VARCHAR2)
IS
/*======================================================================+
| |
| Public Function |
| |
| Description |
| ----------- |
| Just the SRS wrapper for massive_update in batch mode |
| |
| Pseudo-code |
| ----------- |
| Call massive_update and assign its return code to |
| p_retcode |
| RETURN p_retcode (0=success, 1=warning, 2=error) |
| |
| Open issues |
| ----------- |
| |
| 1) Need to review the value assigned to p_errbuf |
| |
| |
| |
+======================================================================*/
l_commit_flag VARCHAR2(1);
l_log_module := C_DEFAULT_MODULE||'.massive_update_srs';
fnd_file.put_line(fnd_file.log,'p_update_mode: ' || p_update_mode);
IF p_update_mode = 'F'
THEN
l_commit_flag := 'N';
,p_update_mode => p_update_mode
,p_balance_source_code => NULL
,p_called_by_flag => NULL
,p_commit_flag => l_commit_flag
)
THEN
p_retcode := 0;
END massive_update_srs;
FUNCTION massive_update_for_events(p_application_id IN INTEGER)
RETURN boolean
IS
l_log_module VARCHAR2 (2000);
l_log_module := C_DEFAULT_MODULE||'.massive_update_for_events';
,p_update_mode => 'D'
,p_balance_source_code => NULL
,p_called_by_flag => 'E'
,p_commit_flag => 'N'
);
(p_location => 'xla_balances_pkg.massive_update_for_events');
END massive_update_for_events;
FUNCTION massive_update
(
p_application_id IN INTEGER
,p_ledger_id IN INTEGER
,p_entity_id IN INTEGER
,p_event_id IN INTEGER
,p_request_id IN INTEGER
,p_accounting_batch_id IN INTEGER
,p_update_mode IN VARCHAR2
,p_execution_mode IN VARCHAR2
) RETURN BOOLEAN
IS
l_req_id NUMBER;
l_log_module := C_DEFAULT_MODULE||'.massive_update';
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: p_application_id cannot be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_entity_id is not NULL, p_ledger_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_entity_id is not NULL, p_event_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_entity_id is not NULL, p_request_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_entity_id is not NULL, p_accounting_batch_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_request_id is not NULL, p_ledger_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_request_id is not NULL, p_entity_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_request_id is not NULL, p_event_id must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: IF p_request_id is not NULL, p_accounting_batch_id must be NULL');
IF p_update_mode IS NULL
OR p_update_mode NOT IN ('A', 'D', 'F')
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'EXCEPTION:' ||
'Invalid p_update_mode parameter: '|| p_update_mode
,p_level => C_LEVEL_EXCEPTION
);
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: Invalid p_update_mode parameter: '|| p_update_mode);
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: Invalid p_execution_mode parameter: '|| p_execution_mode);
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: When p_execution_mode is C, p_event_id, must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: When p_execution_mode is C, p_request_id, must be NULL');
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: When p_execution_mode is C, p_entity_id, must be NULL');
,argument5 => p_update_mode
);
,p_update_mode => p_update_mode
,p_balance_source_code => NULL
,p_called_by_flag => NULL
,p_commit_flag => 'N'
);
,p_value_1 => 'xla_balances_pkg.massive_update'
,p_token_2 => 'ERROR'
,p_value_2 => 'EXCEPTION: Invalid value for parameter p_execution_mode: ' || p_execution_mode);
(p_location => 'xla_balances_pkg.massive_update');
END massive_update;
l_num_of_records_updated INTEGER;
DELETE
FROM xla_control_balances xcbext
WHERE xcbext.ROWID IN
( SELECT xcb.ROWID
FROM xla_balance_statuses xbs
,xla_control_balances xcb
,gl_period_statuses gps
WHERE xbs.balance_source_code = 'C'
AND xbs.balance_status_code = 'R'
AND xbs.request_id = NVL(l_req_id, -1)
AND xcb.initial_balance_flag = 'N'
AND xcb.application_id = xbs.application_id
AND xcb.ledger_id = xbs.ledger_id
AND xcb.code_combination_id = xbs.code_combination_id
AND ( ( xbs.recreate_party_type_code IS NULL
)
OR ( xbs.recreate_party_type_code IS NOT NULL
AND xcb.party_type_code = xbs.recreate_party_type_code
)
)
AND ( ( xbs.recreate_party_type_code IS NULL
)
OR ( xbs.recreate_party_type_code IS NOT NULL
AND xcb.party_id = xbs.recreate_party_id
)
)
AND ( ( xbs.recreate_party_site_id IS NULL
)
OR ( xbs.recreate_party_site_id IS NOT NULL
AND xcb.party_site_id = xbs.recreate_party_site_id
)
)
AND gps.ledger_id = xcb.ledger_id
AND gps.application_id = 101
AND gps.period_name = xcb.period_name
AND gps.effective_period_num >= xbs.recreate_effective_period_num
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_control_balances deleted'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_ac_balances xabext
WHERE xabext.ROWID IN
( SELECT xab.ROWID
FROM xla_balance_statuses xbs
,xla_ac_balances xab
,gl_period_statuses gps
WHERE xbs.balance_source_code = 'A'
AND xbs.balance_status_code = 'R'
AND xbs.request_id = NVL(l_req_id, -1)
AND xab.initial_balance_flag = 'N'
AND xab.application_id = xbs.application_id
AND xab.ledger_id = xbs.ledger_id
AND xab.code_combination_id = xbs.code_combination_id
AND gps.ledger_id = xab.ledger_id
AND gps.period_name = xab.period_name
AND gps.application_id = 101
AND gps.effective_period_num >= xbs.recreate_effective_period_num
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_ac_balances deleted'
,p_level => C_LEVEL_STATEMENT
);
l_num_of_records_updated := 0;
UPDATE xla_ae_lines ael
SET ael.control_balance_flag =
DECODE( ael.control_balance_flag
,'Y'
,'P'
,ael.control_balance_flag
)
WHERE ael.ROWID IN
(SELECT xal.ROWID
FROM xla_balance_statuses xbs
,xla_ae_headers xah
,xla_ae_lines xal
,gl_period_statuses gps
WHERE xbs.balance_status_code = 'R'
AND xbs.balance_source_code = 'C'
AND xbs.request_id = NVL(l_req_id, -1)
AND xah.ledger_id = xbs.ledger_id
AND xah.application_id = xbs.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xal.code_combination_id = xbs.code_combination_id
AND ( ( xbs.recreate_party_type_code IS NULL
)
OR ( xbs.recreate_party_type_code IS NOT NULL
AND xal.party_type_code = xbs.recreate_party_type_code
)
)
AND ( ( xbs.recreate_party_id IS NULL
)
OR ( xbs.recreate_party_id IS NOT NULL
AND ( xal.party_id = xbs.recreate_party_id
)
)
)
AND ( ( xbs.recreate_party_site_id IS NULL
)
OR ( xbs.recreate_party_site_id IS NOT NULL
AND ( xal.party_site_id = xbs.recreate_party_site_id
)
)
)
AND xal.control_balance_flag <> 'P'
AND gps.ledger_id = xah.ledger_id
AND gps.period_name = xah.period_name
AND gps.application_id = 101
AND gps.effective_period_num >= xbs.recreate_effective_period_num
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
l_num_of_records_updated := SQL%ROWCOUNT;
,p_msg => l_num_of_records_updated || ' xla_ae_lines (ctrl bal) updated to P'
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_ae_lines ael
SET ael.analytical_balance_flag =
DECODE( ael.analytical_balance_flag
,'Y'
,'P'
,ael.analytical_balance_flag
)
WHERE ael.ROWID IN
(SELECT xal.ROWID
FROM xla_balance_statuses xbs
,xla_ae_headers xah
,xla_ae_lines xal
,gl_period_statuses gps
WHERE xbs.balance_status_code = 'R'
AND xbs.balance_source_code = 'A'
AND xbs.request_id = NVL(l_req_id, -1)
AND xah.ledger_id = xbs.ledger_id
AND xah.application_id = xbs.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xal.code_combination_id = xbs.code_combination_id
AND xal.analytical_balance_flag <> 'P'
AND gps.ledger_id = xah.ledger_id
AND gps.period_name = xah.period_name
AND gps.application_id = 101
AND gps.effective_period_num >= xbs.recreate_effective_period_num
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
l_num_of_records_updated := l_num_of_records_updated + SQL%ROWCOUNT;
,p_msg => l_num_of_records_updated || ' xla_ae_lines (ac bal) updated to P'
,p_level => C_LEVEL_STATEMENT
);
IF l_num_of_records_updated = 0
THEN
COMMIT;
UPDATE xla_balance_statuses xbs
SET xbs.balance_status_code = 'B'
,xbs.effective_period_num = (SELECT LEAST( NVL( xbs.recreate_effective_period_num
,MAX(gps.effective_period_num)
)
,xbs.effective_period_num
)
FROM gl_period_statuses gps
WHERE gps.ledger_id = xbs.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num < xbs.recreate_effective_period_num
)
,last_update_date = l_date
,last_updated_by = l_user_id
,last_update_login = l_login_id
,program_update_date = l_date
,program_application_id = l_prog_appl_id
,program_id = l_prog_id
,request_id = NVL(l_req_id, -1)
WHERE xbs.balance_status_code = 'R'
AND xbs.request_id = NVL(l_req_id, -1)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_balance_statuses updated to B'
,p_level => C_LEVEL_STATEMENT
);
SELECT xbs.ledger_id
,MIN(xbs.recreate_effective_period_num)
INTO l_current_ledger_id
,l_current_effective_period_num
FROM xla_balance_statuses xbs
WHERE xbs.balance_status_code = 'B'
AND xbs.request_id = NVL(l_req_id, -1)
AND ROWNUM = 1
GROUP BY xbs.ledger_id;
FOR i IN ( SELECT gps.period_name
,gps.effective_period_num
,( SELECT MAX(gps2.effective_period_num)
FROM gl_period_statuses gps2
WHERE gps2.ledger_id = l_current_ledger_id
AND gps2.application_id = 101
AND gps2.effective_period_num < gps.effective_period_num
AND gps2.closing_status IN ('O', 'C', 'P')
AND gps2.adjustment_period_flag = 'N'
) previous_effective_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = l_current_ledger_id
AND gps.application_id = 101
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
AND gps.effective_period_num >= l_current_effective_period_num
ORDER BY gps.effective_period_num
)
LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'i.previous_effective_period_num: '
|| i.previous_effective_period_num
,p_level => C_LEVEL_STATEMENT
);
,p_balance_status_code_selected => 'Q'
,p_balance_status_code_not_sel => 'B'
);
,p_update_mode => 'M'
,p_balance_source_code => NULL
,p_called_by_flag => NULL
,p_commit_flag => 'Y'
);
UPDATE xla_balance_statuses xbs
SET xbs.balance_status_code = 'C'
,last_update_date = l_date
,last_updated_by = l_user_id
,last_update_login = l_login_id
,program_update_date = l_date
,program_application_id = l_prog_appl_id
,program_id = l_prog_id
,request_id = NVL(l_req_id, -1)
WHERE xbs.balance_status_code = 'B'
AND xbs.request_id = NVL(l_req_id, -1)
AND xbs.ledger_id = l_current_ledger_id;
|| ' xla_balance_statuses updated to C'
,p_level => C_LEVEL_STATEMENT
);
SELECT count(*)
INTO l_warning_count
FROM xla_balance_statuses xbs
WHERE xbs.request_id = NVL(l_req_id, -1)
AND NVL(xbs.warning_flag, 'N') = 'Y';
UPDATE xla_balance_statuses xbs
SET xbs.balance_status_code = 'A'
,last_update_date = l_date
,last_updated_by = l_user_id
,last_update_login = l_login_id
,program_update_date = l_date
,program_application_id = l_prog_appl_id
,program_id = l_prog_id
,request_id = NVL(l_req_id, -1)
WHERE xbs.request_id = NVL(l_req_id, -1)
AND NVL(xbs.warning_flag, 'N') = 'Y'
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
, p_msg => l_row_count || ' xla_balance_statuses updated to A'
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_balance_statuses xbs
SET xbs.balance_status_code = 'A'
,last_update_date = l_date
,last_updated_by = l_user_id
,last_update_login = l_login_id
,program_update_date = l_date
,program_application_id = l_prog_appl_id
,program_id = l_prog_id
,request_id = NVL(l_req_id, -1)
WHERE xbs.balance_status_code = 'C'
AND xbs.request_id = NVL(l_req_id, -1)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_balance_statuses updated to A'
,p_level => C_LEVEL_STATEMENT
);
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_ledgers xgl
WHERE xgl.ledger_id = p_ledger_id;
SELECT gps.effective_period_num
INTO l_starting_eff_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = p_ledger_id
AND gps.application_id = 101
AND gps.period_name = p_starting_period_name;
'AND EXISTS (SELECT 1
FROM xla_ae_headers xah
,xla_ae_lines xal
WHERE xah.application_id = xbs.application_id
AND xah.ledger_id = xbs.ledger_id
AND xal.code_combination_id = xbs.code_combination_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.party_type_code = ''' || p_party_type_code ||'''
';
' INSERT
INTO xla_bal_recreate_gt
( application_id
,ledger_id
,code_combination_id
,balance_source_code
)
SELECT xbs.application_id
,xbs.ledger_id
,xbs.code_combination_id
,xbs.balance_source_code
FROM xla_balance_statuses xbs
,gl_code_combinations gcc
,xla_bal_recreate_gt xbt
WHERE gcc.code_combination_id = xbs.code_combination_id
AND xbt.application_id (+)= xbs.application_id
AND xbt.ledger_id (+)= xbs.ledger_id
AND xbt.code_combination_id (+)= xbs.code_combination_id
AND xbt.balance_source_code (+)= xbs.balance_source_code
AND xbt.application_id IS NULL
AND ROWNUM <= ' || C_BATCH_COMMIT_SIZE || '
';
|| ' records inserted in xla_bal_recreate_gt'
,p_level => C_LEVEL_STATEMENT
);
UPDATE xla_balance_statuses xbsext
SET xbsext.balance_status_code = DECODE( xbsext.balance_status_code
,'A'
,'R'
,xbsext.balance_status_code
)
,xbsext.recreate_party_type_code = DECODE( xbsext.balance_status_code
,'A'
,p_party_type_code
,xbsext.recreate_party_type_code
)
,xbsext.recreate_party_id = DECODE( xbsext.balance_status_code
,'A'
,p_party_id
,xbsext.recreate_party_id
)
,xbsext.recreate_party_site_id = DECODE( xbsext.balance_status_code
,'A'
,p_party_site_id
,xbsext.recreate_party_site_id
)
,xbsext.recreate_effective_period_num = DECODE( xbsext.balance_status_code
,'A'
,NVL( l_starting_eff_period_num
,(SELECT MIN(effective_period_num)
FROM gl_period_statuses gps
WHERE gps.ledger_id = xbsext.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
)
)
,xbsext.recreate_effective_period_num
)
,xbsext.warning_flag = DECODE( xbsext.balance_status_code
,'A'
,'N'
,'C'
,'N'
,'Y'
)
,xbsext.last_update_date = l_date
,xbsext.last_updated_by = l_user_id
,xbsext.last_update_login = l_login_id
,xbsext.program_update_date = l_date
,xbsext.program_application_id = l_prog_appl_id
,xbsext.program_id = l_prog_id
,xbsext.request_id = NVL(l_req_id, -1)
WHERE xbsext.ROWID IN
(SELECT xbs.ROWID
FROM xla_bal_recreate_gt xbt
,xla_balance_statuses xbs
,fnd_concurrent_requests fnd
WHERE xbs.application_id = xbt.application_id
AND xbs.ledger_id = xbt.ledger_id
AND xbs.code_combination_id = xbt.code_combination_id
AND xbs.balance_source_code = xbt.balance_source_code
AND xbs.effective_period_num >= NVL( l_starting_eff_period_num
,xbs.effective_period_num
)
AND fnd.request_id(+) = xbs.request_id
AND ( xbs.balance_status_code = 'A'
OR ( xbs.balance_status_code IN ('R', 'B', 'Q', 'C')
AND nvl(fnd.status_code,'N') <> 'R'
)
)
UNION
SELECT xbs.ROWID
FROM xla_balance_statuses xbs
WHERE xbs.application_id = NVL( p_application_id
,xbs.application_id
)
AND xbs.ledger_id = NVL( p_ledger_id
,xbs.ledger_id
)
AND xbs.effective_period_num >= NVL( l_starting_eff_period_num
,xbs.effective_period_num
)
AND NVL(xbs.request_id, -1) = -1
AND xbs.balance_status_code IN ('A', 'R', 'B', 'Q', 'C')
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_balance_statuses updated to R'
,p_level => C_LEVEL_STATEMENT
);
SELECT count(*)
INTO l_count_unavailable
FROM xla_bal_recreate_gt xbt
,xla_balance_statuses xbs
WHERE xbs.application_id = xbt.application_id
AND xbs.ledger_id = xbt.ledger_id
AND xbs.code_combination_id = xbt.code_combination_id
AND xbs.balance_source_code = xbt.balance_source_code
AND xbs.effective_period_num >= NVL( l_starting_eff_period_num
,xbs.effective_period_num
)
AND ( NVL(xbs.request_id, -1) <> NVL(l_req_id, -1)
OR xbs.balance_status_code NOT IN ('R', 'B', 'Q', 'C')
);
SELECT gps.effective_period_num
INTO l_starting_eff_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = p_ledger_id
AND gps.application_id = 101
AND gps.period_name = p_starting_period_name;
UPDATE xla_balance_statuses xbsext
SET xbsext.balance_status_code = DECODE( xbsext.balance_status_code
,'A'
,'R'
,xbsext.balance_status_code
)
,xbsext.recreate_party_type_code = DECODE( xbsext.balance_status_code
,'A'
,NULL
,xbsext.recreate_party_type_code
)
,xbsext.recreate_party_id = DECODE( xbsext.balance_status_code
,'A'
,NULL
,xbsext.recreate_party_id
)
,xbsext.recreate_party_site_id = DECODE( xbsext.balance_status_code
,'A'
,NULL
,xbsext.recreate_party_site_id
)
,xbsext.recreate_effective_period_num = DECODE( xbsext.balance_status_code
,'A'
,NVL( l_starting_eff_period_num
,(SELECT MIN(effective_period_num)
FROM gl_period_statuses gps
WHERE gps.ledger_id = xbsext.ledger_id
AND gps.application_id = 101
AND gps.closing_status IN ('O', 'C', 'P')
AND gps.adjustment_period_flag = 'N'
)
)
,xbsext.recreate_effective_period_num
)
,xbsext.warning_flag = DECODE( xbsext.balance_status_code
,'A'
,'N'
,'C'
,'N'
,'Y'
)
,xbsext.last_update_date = l_date
,xbsext.last_updated_by = l_user_id
,xbsext.last_update_login = l_login_id
,xbsext.program_update_date = l_date
,xbsext.program_application_id = l_prog_appl_id
,xbsext.program_id = l_prog_id
,xbsext.request_id = l_req_id
WHERE xbsext.ROWID IN
(SELECT xbs.ROWID
FROM xla_balance_statuses xbs
,fnd_concurrent_requests fnd
WHERE xbs.application_id = NVL( p_application_id
,xbs.application_id
)
AND xbs.ledger_id = NVL( p_ledger_id
,xbs.ledger_id
)
AND xbs.effective_period_num >= NVL( l_starting_eff_period_num
,xbs.effective_period_num
)
AND fnd.request_id(+) = xbs.request_id
AND ( xbs.balance_status_code = 'A'
OR ( xbs.balance_status_code IN ('R', 'B', 'Q', 'C')
AND NVL(fnd.status_code,'N') <> 'R'
)
)
UNION
SELECT xbs.ROWID
FROM xla_balance_statuses xbs
WHERE xbs.application_id = NVL( p_application_id
,xbs.application_id
)
AND xbs.ledger_id = NVL( p_ledger_id
,xbs.ledger_id
)
AND xbs.effective_period_num >= NVL( l_starting_eff_period_num
,xbs.effective_period_num
)
AND NVL(xbs.request_id, -1) = -1
AND xbs.balance_status_code IN ('A', 'R', 'B', 'Q', 'C')
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_balance_statuses updated to R'
,p_level => C_LEVEL_STATEMENT
);
SELECT count(*)
INTO l_count_unavailable
FROM xla_balance_statuses xbs
WHERE xbs.application_id = NVL( p_application_id
,xbs.application_id
)
AND xbs.ledger_id = NVL( p_ledger_id
,xbs.ledger_id
)
AND xbs.effective_period_num >= NVL( l_starting_eff_period_num
,xbs.effective_period_num
)
AND ( NVL(xbs.request_id, -1) <> NVL(l_req_id, -1)
OR xbs.balance_status_code NOT IN ('R', 'B', 'Q', 'C')
);
,p_msg => 'This request was unable to select all the requested balances'
,p_level => C_LEVEL_ERROR
);
DELETE
FROM xla_bal_recreate_gt;
,p_msg => l_row_count || ' xla_bal_recreate_gt deleted.'
,p_level => C_LEVEL_STATEMENT
);
,p_msg => 'recreation selection failed'
,p_level => C_LEVEL_ERROR
);
INSERT
INTO xla_bal_synchronize_gt
( chart_of_accounts_id
,code_combination_id
,reference3
)
SELECT gcc.chart_of_accounts_id
,gcc.code_combination_id
,gcc.reference3
FROM gl_code_combinations gcc
,xla_bal_synchronize_gt xbt
WHERE gcc.chart_of_accounts_id = :1
AND gcc.' || l_account_segment_column || ' = :2
AND xbt.chart_of_accounts_id (+)= :3
AND xbt.code_combination_id (+)= gcc.code_combination_id
AND xbt.chart_of_accounts_id IS NULL
AND ROWNUM <= :4
'
USING p_chart_of_accounts_id
,p_account_segment_value
,p_chart_of_accounts_id
,C_BATCH_COMMIT_SIZE;
|| ' records inserted in xla_bal_synchronize_gt'
,p_level => C_LEVEL_STATEMENT
);
SELECT MIN(reference3)
,MAX(reference3)
INTO l_min_reference3
,l_max_reference3
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id;
UPDATE xla_balance_statuses xbsext
SET xbsext.balance_status_code = 'S'
,xbsext.last_update_date = l_date
,xbsext.last_updated_by = l_user_id
,xbsext.last_update_login = l_login_id
,xbsext.program_update_date = l_date
,xbsext.program_application_id = l_prog_appl_id
,xbsext.program_id = l_prog_id
,xbsext.request_id = NVL(l_req_id, -1)
WHERE xbsext.ROWID IN
(SELECT xbs.ROWID
FROM xla_balance_statuses xbs
,fnd_concurrent_requests fnd
WHERE xbs.ledger_id IN
(
SELECT xgl.ledger_id
FROM gl_ledgers xgl
WHERE xgl.chart_of_accounts_id = p_chart_of_accounts_id
)
AND xbs.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
)
AND xbs.balance_source_code = 'C'
AND xbs.balance_status_code IN ('A', 'S')
AND fnd.request_id(+) = xbs.request_id
AND NVL(fnd.status_code,'N') <> 'R'
UNION
SELECT xbs.ROWID
FROM xla_balance_statuses xbs
WHERE xbs.ledger_id IN
(
SELECT xgl.ledger_id
FROM gl_ledgers xgl
WHERE xgl.chart_of_accounts_id = p_chart_of_accounts_id
)
AND xbs.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
)
AND xbs.balance_source_code = 'C'
AND xbs.balance_status_code IN ('A', 'S')
AND NVL(xbs.request_id, -1) = NVL(l_req_id, -1)
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
|| ' xla_balance_statuses updated to S'
,p_level => C_LEVEL_STATEMENT
);
SELECT xbs.code_combination_id
FROM xla_balance_statuses xbs
WHERE xbs.ledger_id IN
(
SELECT xgl.ledger_id
FROM gl_ledgers xgl
,xla_setup_ledgers xsl
WHERE xgl.chart_of_accounts_id = :1
AND xsl.ledger_id = xgl.ledger_id
)
AND xbs.balance_source_code = ''C''
AND xbs.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = :1
)
FOR UPDATE OF xbs.ledger_id
'
USING p_chart_of_accounts_id
,p_chart_of_accounts_id;
SELECT COUNT(*)
INTO l_count_unavailable
FROM xla_balance_statuses xbs
WHERE xbs.balance_status_code <> 'S'
AND ( xbs.application_id
,xbs.ledger_id
,xbs.code_combination_id
)
IN
(
SELECT xbt.application_id
,xbt.ledger_id
,xbt.code_combination_id
FROM xla_bal_recreate_gt xbt
);
UPDATE xla_ae_lines xal
SET xal.control_balance_flag = NULL
WHERE xal.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
)
AND xal.control_balance_flag IS NOT NULL
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_ae_lines updated to N'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_control_balances xcb
WHERE xcb.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_balance_statuses deleted'
,p_level => C_LEVEL_STATEMENT
);
DELETE
FROM xla_balance_statuses xbs
WHERE xbs.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
)
AND xbs.balance_status_code = 'S'
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_balance_statuses deleted'
,p_level => C_LEVEL_STATEMENT
);
SELECT COUNT(*)
INTO l_count_unavailable
FROM xla_balance_statuses xbs
WHERE xbs.balance_source_code = 'C'
AND xbs.code_combination_id IN
(
SELECT xbt.code_combination_id
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
);
UPDATE xla_ae_lines xal
SET xal.control_balance_flag = 'P'
WHERE xal.ROWID IN
( SELECT xal.ROWID
FROM xla_bal_synchronize_gt xbt
,xla_ae_lines xal
,xla_ae_headers xah
,xla_subledgers xsl
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
AND xal.code_combination_id = xbt.code_combination_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.party_type_code IS NOT NULL
AND xal.party_id IS NOT NULL
AND xal.control_balance_flag IS NULL
AND xsl.application_id = xah.application_id
AND xbt.reference3 = xsl.control_account_type_code
)
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_ae_lines updated to P'
,p_level => C_LEVEL_STATEMENT
);
,p_update_mode => 'M'
,p_balance_source_code => NULL
,p_called_by_flag => NULL
,p_commit_flag => 'Y'
);
DELETE
FROM xla_bal_synchronize_gt xbt
WHERE xbt.chart_of_accounts_id = p_chart_of_accounts_id
AND ROWNUM <= C_BATCH_COMMIT_SIZE;
,p_msg => l_row_count || ' xla_bal_synchronize_gt deleted'
,p_level => C_LEVEL_STATEMENT
);
INSERT INTO xla_ae_headers
(
AE_HEADER_ID
,APPLICATION_ID
,LEDGER_ID
,ENTITY_ID
,EVENT_ID
,EVENT_TYPE_CODE
,ACCOUNTING_DATE
,GL_TRANSFER_STATUS_CODE
,JE_CATEGORY_NAME
,ACCOUNTING_ENTRY_STATUS_CODE
,ACCOUNTING_ENTRY_TYPE_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,BALANCE_TYPE_CODE
,PERIOD_NAME
)
VALUES
(
p_ae_header_id
,p_application_id
,p_ledger_id
,p_entity_id
,p_event_id
,p_event_type_code
,p_accounting_date
,p_gl_transfer_status_code
,p_je_category_name
,p_accounting_entry_status_code
,p_accounting_entry_type_code
,l_date
,l_user_id
,l_date
,l_user_id
,p_balance_type_code
,p_period_name
);
INSERT INTO xla_ae_lines
(
AE_HEADER_ID
,AE_LINE_NUM
,CODE_COMBINATION_ID
,CREATION_DATE
,ACCOUNTED_DR
,ACCOUNTED_CR
,CURRENCY_CODE
,ENTERED_DR
,ENTERED_CR
,LAST_UPDATE_DATE
,PARTY_ID
,PARTY_SITE_ID
,PARTY_TYPE_CODE
,CREATED_BY
,LAST_UPDATED_BY
,CONTROL_BALANCE_FLAG
,ANALYTICAL_BALANCE_FLAG
,APPLICATION_ID
,LEDGER_ID --5067260
,ACCOUNTING_DATE --5067260
,GL_SL_LINK_ID --5041325
)
VALUES
(
p_ae_header_id
,p_ae_line_num
,p_code_combination_id
,l_date
,p_accounted_dr
,p_accounted_cr
,p_currency_code
,p_entered_dr
,p_entered_cr
,l_date
,p_party_id
,p_party_site_id
,p_party_type_code
,l_user_id
,l_user_id
,p_control_balance_flag
,p_analytical_balance_flag
,p_application_id
,p_ledger_id
,p_accounting_date
,DECODE(p_accounting_entry_status_code,'F'
,xla_gl_sl_link_id_s.nextval,NULL) --5041325
);
DELETE
FROM xla_ae_lines xal
WHERE xal.ae_header_id = p_ae_header_id;
DELETE
FROM xla_ae_headers xah
WHERE xah.ae_header_id = p_ae_header_id;
SELECT gll.currency_code
INTO l_ledger_currency_code
FROM gl_ledgers gll
WHERE gll.ledger_id = p_ledger_id;
SELECT gps.effective_period_num
,gps.start_date
INTO l_effective_period_num
,l_period_start_date
FROM gl_period_statuses gps
WHERE gps.ledger_id = p_ledger_id
AND gps.period_name = p_period_name
AND gps.application_id = 101;
SELECT xls.application_id
INTO l_application_id
FROM xla_subledgers xls
WHERE xls.application_id = p_application_id;
SELECT xll.ledger_id
INTO l_ledger_id
FROM xla_gl_ledgers_v xll
WHERE xll.ledger_id = p_ledger_id;
SELECT MIN(gps.effective_period_num)
INTO l_first_entry_eff_period_num
FROM gl_period_statuses gps
,xla_ae_headers xah
,xla_ae_lines xal
WHERE gps.ledger_id = p_ledger_id
AND gps.application_id = 101
AND gps.period_name = xah.period_name
AND xah.ledger_id = p_ledger_id
AND xah.application_id = p_application_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.code_combination_id = p_code_combination_id
AND xal.party_type_code = p_party_type_code
AND xal.party_id = p_party_id
AND NVL(xal.party_site_id,-999)= p_party_site_id;
SELECT xba.period_name
,xba.beginning_balance_dr
,xba.beginning_balance_cr
,xba.rowid
INTO l_existing_balance_period_name
,l_existing_begin_balance_dr
,l_existing_begin_balance_cr
,l_existing_balance_rowid
FROM xla_control_balances xba
WHERE xba.ledger_id = p_ledger_id
AND xba.application_id = p_application_id
AND xba.code_combination_id = p_code_combination_id
AND xba.initial_balance_flag = 'Y'
AND xba.party_type_code = p_party_type_code
AND xba.party_id = p_party_id
AND xba.party_site_id = p_party_site_id;
UPDATE xla_control_balances xba
SET xba.initial_balance_flag = 'N'
WHERE xba.rowid = l_existing_balance_rowid;
,p_msg => l_row_count || ' balances updated '
,p_level => C_LEVEL_STATEMENT);
SELECT xla_ae_headers_s.nextval
INTO l_ae_header_id
FROM DUAL;
,p_update_mode => 'A'
,p_balance_source_code => 'C'
,p_called_by_flag => NULL
,p_commit_flag => 'N'
)
THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
trace
( p_module => l_log_module
,p_msg => 'EXCEPTION:' ||
'Failure propagating the initial balance'
,p_level => C_LEVEL_EXCEPTION
);
UPDATE xla_control_balances xba
SET xba.beginning_balance_dr = xba.beginning_balance_dr + xba.period_balance_dr
,xba.beginning_balance_cr = xba.beginning_balance_cr + xba.period_balance_cr
,xba.period_balance_dr = 0
,xba.period_balance_cr = 0
,xba.initial_balance_flag = 'Y'
,xba.first_period_flag = 'Y'
WHERE xba.ledger_id = p_ledger_id
AND xba.application_id = p_application_id
AND xba.code_combination_id = p_code_combination_id
AND xba.initial_balance_flag = 'N'
AND xba.party_type_code = p_party_type_code
AND xba.party_id = p_party_id
AND xba.party_site_id = p_party_site_id
AND xba.period_name = p_period_name;