[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION call_update_balances RETURN BOOLEAN IS
l_int_count NUMBER;
l_log_module := C_DEFAULT_MODULE||'.call_update_balances';
UPDATE xla_ctrl_balances_int xib SET code_combination_id =
(SELECT
gcc.code_Combination_id
FROM gl_code_combinations gcc
,gl_ledgers gll
WHERE xib.ledger_id = gll.ledger_id
AND gll.chart_of_accounts_id = gcc.chart_of_accounts_id
AND NVL(xib.segment1,'X') = NVL(gcc.segment1,'X')
AND NVL(xib.segment2,'X') = NVL(gcc.segment2,'X')
AND NVL(xib.segment3,'X') = NVL(gcc.segment3,'X')
AND NVL(xib.segment4,'X') = NVL(gcc.segment4,'X')
AND NVL(xib.segment5,'X') = NVL(gcc.segment5,'X')
AND NVL(xib.segment6,'X') = NVL(gcc.segment6,'X')
AND NVL(xib.segment7,'X') = NVL(gcc.segment7,'X')
AND NVL(xib.segment8,'X') = NVL(gcc.segment8,'X')
AND NVL(xib.segment9,'X') = NVL(gcc.segment9,'X')
AND NVL(xib.segment10,'X') = NVL(gcc.segment10,'X')
AND NVL(xib.segment11,'X') = NVL(gcc.segment11,'X')
AND NVL(xib.segment12,'X') = NVL(gcc.segment12,'X')
AND NVL(xib.segment13,'X') = NVL(gcc.segment13,'X')
AND NVL(xib.segment14,'X') = NVL(gcc.segment14,'X')
AND NVL(xib.segment15,'X') = NVL(gcc.segment15,'X')
AND NVL(xib.segment16,'X') = NVL(gcc.segment16,'X')
AND NVL(xib.segment17,'X') = NVL(gcc.segment17,'X')
AND NVL(xib.segment18,'X') = NVL(gcc.segment18,'X')
AND NVL(xib.segment19,'X') = NVL(gcc.segment19,'X')
AND NVL(xib.segment20,'X') = NVL(gcc.segment20,'X')
AND NVL(xib.segment21,'X') = NVL(gcc.segment21,'X')
AND NVL(xib.segment22,'X') = NVL(gcc.segment22,'X')
AND NVL(xib.segment23,'X') = NVL(gcc.segment23,'X')
AND NVL(xib.segment24,'X') = NVL(gcc.segment24,'X')
AND NVL(xib.segment25,'X') = NVL(gcc.segment25,'X')
AND NVL(xib.segment26,'X') = NVL(gcc.segment26,'X')
AND NVL(xib.segment27,'X') = NVL(gcc.segment27,'X')
AND NVL(xib.segment28,'X') = NVL(gcc.segment28,'X')
AND NVL(xib.segment29,'X') = NVL(gcc.segment29,'X')
AND NVL(xib.segment30,'X') = NVL(gcc.segment30,'X')
) WHERE XIB.CODE_COMBINATION_ID IS NULL;
,p_msg => '# of rows in Interface table updated with ccid ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
update /*+ index(xcb1,xla_control_balances_N99) */ xla_control_balances xcb1 set initial_balance_flag='Y'
where initial_balance_flag<> 'Y'
and (xcb1.application_id , xcb1.ledger_id , xcb1.code_combination_id , xcb1.party_id
, NVL (xcb1.party_site_id, -9999) , NVL (xcb1.party_type_code, ' ') , xcb1.effective_period_num)
in
(select xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id
, NVL (xcb.party_site_id, -9999)
, NVL (xcb.party_type_code, ' ')
,min(xcb.effective_period_num)
from xla_control_balances xcb,
(select application_id, ledger_id, code_combination_id, party_id, party_site_id, party_type_code
from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL) stmp
where stmp.application_id = xcb.application_id
AND stmp.ledger_id = xcb.ledger_id
AND stmp.code_combination_id = xcb.code_combination_id
AND stmp.party_id = xcb.party_id
AND NVL (stmp.party_site_id, -9999) = NVL (xcb.party_site_id, -9999)
AND NVL (stmp.party_type_code, ' ') = NVL (xcb.party_type_code, ' ')
group by xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id
, xcb.party_site_id, xcb.party_type_code);
,p_msg => '# of rows in xla_control_balance table updated with initial_balance_flag (Y)' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.initial_balance_flag ='N' where b.initial_balance_flag ='Y'
and ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') )
in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
and effective_period_num
>
(select min(effective_period_num) from xla_control_balances xcb
where b.application_id = xcb.application_id
AND b.ledger_id = xcb.ledger_id
AND b.code_combination_id = xcb.code_combination_id
AND b.party_id = xcb.party_id
AND NVL (b.party_site_id, -9999) = NVL (xcb.party_site_id, -9999)
AND NVL (b.party_type_code, ' ') = NVL (xcb.party_type_code, ' '));
,p_msg => '# of rows in xla_control_balance table updated with initial_balance_flag (N)' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.first_period_flag='N'
where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
, NVL (xcb.party_type_code, ' '),xcb.effective_period_num
from
xla_control_balances xcb
, gl_period_statuses gps
where
gps.effective_period_num = xcb.effective_period_num
and gps.application_id = 101
and gps.ledger_id = xcb.ledger_id
and gps.period_num > 1
and xcb.first_period_flag = 'Y'
and (xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id , NVL (xcb.party_site_id, -9999) , NVL (xcb.party_type_code, ' ') )
in (select application_id, ledger_id, code_combination_id, party_id, nvl(party_site_id,-9999), nvl(party_type_code,' ')
from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL));
update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.first_period_flag='Y'
where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
, NVL (xcb.party_type_code, ' '),xcb.effective_period_num
from
xla_control_balances xcb
, gl_period_statuses gps
where
gps.effective_period_num = xcb.effective_period_num
and gps.application_id = 101
and gps.ledger_id = xcb.ledger_id
and gps.period_num = 1
and (xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id , NVL (xcb.party_site_id, -9999) , NVL (xcb.party_type_code, ' ') )
in (select application_id, ledger_id, code_combination_id, party_id, nvl(party_site_id,-9999), nvl(party_type_code,' ')
from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL));
,p_msg => '# of rows in xla_control_balance table updated with initial_balance_flag (N)' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
,p_msg => 'Update existing Initial Balances ' || l_log_module
,p_level => C_LEVEL_PROCEDURE);
UPDATE xla_ctrl_balances_int xib
SET(existing_init_balance_dr
,existing_init_balance_cr
,existing_effective_period_num) =
(SELECT beginning_balance_dr
,beginning_balance_cr
,glp.effective_period_num
FROM xla_control_balances xcb
,gl_period_statuses glp
WHERE xcb.period_name = glp.period_name
AND xcb.ledger_id = glp.ledger_id
AND glp.application_id = 101
AND xcb.ledger_id = glp.ledger_id
AND xcb.application_id = xib.application_id
AND xcb.ledger_id = xib.ledger_id
AND xcb.code_combination_id = xib.code_combination_id
AND xcb.party_type_code = xib.party_type_code
AND xcb.party_id = xib.party_id
AND NVL(xcb.party_site_id,-999) = NVL(xib.party_site_id,-999)
AND xcb.initial_balance_flag = 'Y'
)
WHERE message_codes IS NULL
AND status IS NULL;
,p_msg => '# rows in interface table updated with existing_initial_balance -1 : ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
UPDATE xla_ctrl_balances_int xib
SET existing_effective_period_num =
( SELECT min(glp.effective_period_num) effective_period_num
FROM xla_control_balances xcb
,gl_period_statuses glp
WHERE xcb.period_name = glp.period_name
AND xcb.ledger_id = glp.set_of_books_id
AND glp.application_id = 101
AND xib.application_id = xcb.application_id
AND xib.ledger_id = xcb.ledger_id
AND xib.code_combination_id = xcb.code_Combination_id
And xib.party_type_code = xcb.party_type_code
ANd xib.party_id = xcb.party_id
AND NVL(xib.party_site_id,-999) = NVL(xcb.party_site_id,-999)
GROUP BY
xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,xcb.party_site_id)
WHERE message_codes IS NULL
AND status IS NULL
AND existing_effective_period_num is null;
,p_msg => '# rows in interface table updated with existing_initial_balance -2 : ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
UPDATE xla_ctrl_balances_int xib
SET(existing_init_balance_dr
,existing_init_balance_cr
,existing_effective_period_num) =
( SELECT 0,
0,
max(glp.effective_period_num)
FROM gl_period_statuses glp
WHERE xib.ledger_id = glp.set_of_books_id
AND glp.application_id = 101
AND glp.adjustment_period_flag = 'N')
WHERE message_codes IS NULL
AND status IS NULL
AND existing_effective_period_num is null;
,p_msg => '# rows in interface table updated with effective period num (new account groups) : ' || SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
,p_msg => 'Number of Rows update for existing balance:'||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT );
with amount equal to new initial balances is inserted into xla_control_balances */
MERGE INTO xla_control_balances xba
USING (SELECT xin.application_id
,xin.ledger_id
,xin.code_combination_id
,xin.party_type_code
,xin.party_id
,xin.party_site_id
,glp.period_name
,xin.init_balance_dr
,xin.init_balance_cr
,xin.existing_init_balance_dr
,xin.existing_init_balance_cr
,glp.period_year
,glp.period_num
,glp.effective_period_num
FROM xla_ctrl_balances_int xin
,gl_period_statuses glp
WHERE xin.ledger_id = glp.set_of_books_id
AND xin.period_name = glp.period_name
AND glp.application_id = 101
AND xin.message_codes IS NULL
AND xin.status IS NULL ) xib
ON (xba.application_id = xib.application_id
AND xba.ledger_id = xib.ledger_id
AND xba.period_name = xib.period_name
AND xba.code_combination_id = xib.code_combination_id
AND xba.party_type_code = xib.party_type_code
AND xba.party_id = xib.party_id
AND NVL(xba.party_site_id,-999) = NVL(xib.party_site_id,-999) )
WHEN MATCHED THEN
UPDATE
SET xba.beginning_balance_dr = nvl(xib.init_balance_dr,0)+ nvl(xba.beginning_balance_dr,0) - nvl(xib.existing_init_balance_dr,0)
,xba.beginning_balance_cr = nvl(xib.init_balance_cr,0)+ nvl(xba.beginning_balance_cr,0) - nvl(xib.existing_init_balance_cr,0)
,xba.initial_balance_flag = 'Y'
,xba.first_period_flag = 'Y' ---fix to set first_period_flag issue
,xba.last_update_date = g_date
,xba.last_updated_by = g_user_id
,xba.last_update_login = g_login_id
,xba.request_id = g_req_id
,xba.PROGRAM_ID = g_prog_id
,xba.PROGRAM_APPLICATION_ID = g_prog_appl_id
WHEN NOT MATCHED THEN
INSERT(application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
,period_name
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,initial_balance_flag
,first_period_flag
,period_year
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,effective_period_num
)
VALUES
(xib.application_id
,xib.ledger_id
,xib.code_combination_id
,xib.party_type_code
,xib.party_id
,xib.party_site_id
,xib.period_name
,xib.init_balance_dr
,xib.init_balance_cr
,0
,0
,DECODE(nvl(xib.init_balance_dr,0) - nvl(xib.init_balance_cr,0) ,0 ,'N','Y')
,'Y'
,xib.period_year
,g_date
,g_user_id
,g_date
,g_user_id
,g_login_id
,g_req_id
,g_prog_id
,g_prog_appl_id
,xib.effective_period_num);
update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.initial_balance_flag ='N' where b.initial_balance_flag ='Y'
and ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') )
in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
and effective_period_num
>
(select min(effective_period_num) from xla_control_balances xcb
where b.application_id = xcb.application_id
AND b.ledger_id = xcb.ledger_id
AND b.code_combination_id = xcb.code_combination_id
AND b.party_id = xcb.party_id
AND NVL (b.party_site_id, -9999) = NVL (xcb.party_site_id, -9999)
AND NVL (b.party_type_code, ' ') = NVL (xcb.party_type_code, ' '));
1. Select statement will fetch the net initial dr and cr amount for each
year first period ( incase of year in which initial balance is populated
then initial balance period will be treated as first period) for each
set of application_id,ledger_id,code_combination_id,party_type_code,
party_id, party_site_id (this combination will refer as party set info)
xla_ctrl_balances_int.existing_period_num store the minimum period
which exist in xla_control_balance for each party set info
In below select statement Decode(xcb.period_year,round(xib.existing_period_num
will identify the row whether it belong to same period in which initial balance
is populated or not (already we have updated and inserted the row for new initial
balances, refer merge statement to populate initial balance)
In below statement while calculating new Dr and Cr at the beginning we
have - ( beginning amount) , this is done to reverse the existing begninnig
balance amount and replace it with new one. This is done in next update statement
where the new balance amount is added to existing beginning balance. Logically
negative beginning balance in select statement revert the beginning balance
in update statement and we are left with only new value
Finally the new inital balance is calculated as difference of New initial
balance populated in xla_ctrl_balances_int and existing initial balance
in xla_control_balance (if any)
SIGN function is use to identify whether the net amount is going to be added
as Dr or Cr based on differenct of Dr- Cr
2.Update the xla_control_balance by adding new dr and cr amount to existing
beginning balance Dr and Cr
*/
SELECT xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,NVL(xcb.party_site_id,-999)
,xcb.period_year
,(-nvl(beginning_balance_dr,0)
+ DECODE(xcb.period_year
,round(xib.existing_effective_period_num/10000)
,( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
- nvl(existing_init_balance_dr,0)
)
,DECODE(SIGN( ( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
- nvl(existing_init_balance_dr,0)
)
-( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
- nvl(existing_init_balance_cr,0)
)
)
,1
,( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
- nvl(existing_init_balance_dr,0)
)
-( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
- nvl(existing_init_balance_cr,0)
)
,0)
)
) new_dr
,(-nvl(beginning_balance_cr,0)
+ DECODE(xcb.period_year
,round(xib.existing_effective_period_num/10000)
,( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
- nvl(existing_init_balance_cr,0)
)
,DECODE(SIGN( ( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
- nvl(existing_init_balance_dr,0)
)
-( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
- nvl(existing_init_balance_cr,0)
)
)
,-1
,-(( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
- nvl(existing_init_balance_dr,0)
)
-( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
- nvl(existing_init_balance_cr,0)
)
)
,0
)
)
) new_Cr
BULK COLLECT INTO l_array_appl_id
,l_array_ledger_id
,l_array_ccid
,l_array_party_type_code
,l_array_party_id
,l_array_party_site_id
,l_array_period_year
,l_array_new_dr
,l_array_new_cr
FROM xla_control_balances xcb
,xla_ctrl_balances_int xib
WHERE xib.application_id = xcb.application_id
AND xib.ledger_id = xcb.ledger_id
AND xib.code_combination_id = xcb.code_combination_id
AND xib.party_type_code =xcb.party_type_code
AND xib.party_id = xcb.party_id
AND xib.party_site_id = xcb.party_site_id
AND xib.message_codes IS NULL
AND xib.status IS NULL
AND (xcb.first_period_flag = 'Y' OR xcb.initial_balance_flag = 'Y');
UPDATE xla_control_balances
SET beginning_balance_dr = NVL(beginning_balance_dr,0)+ l_array_new_dr(i)
,beginning_balance_cr = NVL(beginning_balance_cr,0) + l_array_new_cr(i)
WHERE application_id = l_array_appl_id(i)
AND ledger_id = l_array_ledger_id(i)
AND code_combination_id = l_array_ccid(i)
AND party_type_code = l_array_party_type_code(i)
AND party_id = l_array_party_id(i)
AND NVL(party_site_id,-999) = l_array_party_site_id(i)
AND period_year = l_array_period_year(i)
AND initial_balance_flag = 'N';
,p_msg => '# rows updated with new Beginning balance : '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
INSERT INTO xla_control_balances
(application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
,period_name
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,initial_balance_flag
,first_period_flag
,period_year
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,effective_period_num)
SELECT application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
,period_name
,beginning_balance_dr
,beginning_balance_cr
,period_balance_dr
,period_balance_cr
,initial_balance_flag
,first_period_flag
,period_year
,g_date
,g_user_id
,g_date
,g_user_id
,g_login_id
,g_req_id
,g_prog_id
,g_prog_appl_id
,effective_period_num
from
(
SELECT xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,xcb.party_site_id
,glp1.period_name
,xcb.beginning_balance_dr
,xcb.beginning_balance_cr
,xcb.period_balance_dr
,xcb.period_balance_cr
,'N' initial_balance_flag
,DECODE(glp1.period_num,1,'Y','N') first_period_flag
,glp1.period_year
,glp1.effective_period_num
FROM xla_ctrl_balances_int xin
,gl_period_statuses glp1
,xla_control_balances xcb
where
xcb.application_id = xin.application_id
AND xcb.ledger_id = xin.ledger_id
AND xcb.code_combination_id = xin.code_combination_id
AND xcb.party_type_code = xcb.party_type_code
AND xcb.party_id = xin.party_id
AND NVL(xcb.party_site_id,-999) = NVL(xin.party_site_id,-999)
AND xin.STATUS is null
AND xcb.initial_balance_flag='Y'
AND glp1.effective_period_num between xcb.effective_period_num and xin.existing_effective_period_num
AND glp1.ledger_id = xin.ledger_id
AND glp1.application_id = 101
AND glp1.closing_status IN ('O', 'C', 'P')
AND glp1.adjustment_period_flag = 'N'
) b
-- begin Bug12655377
where not exists
(
select 'x' from xla_control_balances xcb1 where
xcb1.application_id = b.application_id
AND xcb1.ledger_id = b.ledger_id
AND xcb1.code_combination_id = b.code_combination_id
AND nvl(xcb1.party_type_code,' ') = nvl(b.party_type_code,' ')
AND xcb1.party_id = b.party_id
AND NVL(xcb1.party_site_id,-999) = NVL(b.party_site_id,-999)
AND xcb1.effective_period_num = b.effective_period_num
);
SELECT xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,nvl(xcb.party_site_id,-999)
,min(glp.effective_period_num)
BULK COLLECT INTO
l_array_appl_id
,l_array_ledger_id
,l_array_ccid
,l_array_party_type_code
,l_array_party_id
,l_array_party_site_id
,l_array_period_num
FROM xla_ctrl_balances_int xib
,xla_control_balances xcb
,gl_period_statuses glp
WHERE xib.application_id = xcb.application_id
AND xib.ledger_id = xcb.ledger_id
AND xib.code_combination_id = xcb.code_combination_id
AND xib.party_type_code = xcb.party_type_code
AND xib.party_id = xcb.party_id
AND nvl(xib.party_site_id ,-999) = nvl(xcb.party_site_id,-999)
AND xib.message_codes IS NULL
AND xib.status IS NULL
AND xcb.ledger_id = glp.set_of_books_id
AND glp.application_id = 101
AND xcb.period_name = glp.period_name
AND (nvl(xcb.period_balance_dr,0) <> 0 OR nvl(xcb.period_balance_cr,0) <> 0)
AND xib.init_balance_dr = 0 AND xib.init_balance_cr = 0
GROUP BY xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,nvl(xcb.party_site_id,-999);
DELETE FROM xla_control_balances
WHERE application_id = l_array_appl_id(i)
AND ledger_id = l_array_ledger_id(i)
AND code_combination_id = l_array_ccid(i)
AND party_type_code = l_array_party_type_code(i)
AND party_id = l_array_party_id(i)
AND NVL(party_site_id,-999) = l_array_party_site_id(i)
AND period_name IN (SELECT period_name
FROM gl_period_statuses
WHERE set_of_books_id = l_array_ledger_id(i)
AND application_id =101
AND effective_period_num < l_array_period_num(i))
AND beginning_balance_dr = 0
AND beginning_balance_cr = 0 ;
,p_msg => '# rows with 0 initial balance deleted : '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
UPDATE xla_control_balances
SET first_period_flag = 'Y'
WHERE application_id = l_array_appl_id(i)
AND ledger_id = l_array_ledger_id(i)
AND code_combination_id = l_array_ccid(i)
AND party_type_code = l_array_party_type_code(i)
AND party_id = l_array_party_id(i)
AND NVL(party_site_id,-999) = l_array_party_site_id(i)
AND effective_period_num = l_array_period_num(i);
,p_msg => '# rows updated with first_period_flag = Y after deleting records with 0 initial balance : '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE);
UPDATE xla_ctrl_balances_int
SET status = 'IMPORTED'
,last_update_date = g_date
,last_updated_by = g_user_id
,last_update_login = g_login_id
WHERE message_codes IS NULL
AND status IS NULL;
(p_location => 'xla_ca_balances_pkg.call_update_balances');
END call_update_balances;
UPDATE xla_ctrl_balances_int xin
SET message_codes =
( SELECT NVL2(IB001,IB001||',',NULL)||
NVL2(IB002,IB002||',',NULL)||
NVL2(IB003,IB003||',',NULL)||
NVL2(IB004,IB004||',',NULL)||
NVL2(IB005,IB005||',',NULL)||
NVL2(IB006,IB006||',',NULL)||
NVL2(IB007,IB007||',',NULL)||
NVL2(IB008,IB008||',',NULL)||
NVL2(IB009,IB009||',',NULL)||
NVL2(IB010,IB010||',',NULL)||
NVL2(IB011,IB011||',',NULL)||
NVL2(IB012,IB012||',',NULL)||
NVL2( IB013 ,IB013||',' ,NVL2( IB014 ,IB014||',' ,NULL))||
NVL2(IB015,IB015||',',NULL)||
NVL2(IB016,IB016||',',NULL)||
NVL2(IB017,IB017||',',NULL)||
NVL2(IB018,IB018||',',NULL)||
NVL2(IB019,IB019||',',NULL)||
NVL2(IB020,IB020||',',NULL)||
NVL2(IB021,IB021||',',NULL)||
NVL2(IB023,IB023||',',NULL)||
NVL2(IB024,IB024||',',NULL)|| -- error cot below but not updated, hence bug12674354
NVL2(IB025,IB025||',',NULL)|| -- bug 12674383
NVL2(IB026,IB026||',',NULL)||
NVL2(IB027,IB027||',',NULL)
FROM (
SELECT xib.rowid row_id
,xib.party_id
,NVL2(xls.application_id,NULL,'IB001') IB001
,NVL2(xls.application_id,DECODE(NVL(xls.control_account_type_code,'X'),'X','IB002'
,'N','IB002'),NULL) IB002
,NVL2(gll.ledger_id,NULL,'IB003') IB003
,DECODE(gll.ledger_category_code,'PRIMARY',NULL,
DECODE(NVL(glr.relationship_type_code,'N'),'SUBLEDGER',NULL,'IB004')) IB004
,NVL2(gcc.code_combination_id,NULL,'IB005') IB005
,DECODE(gll.chart_of_accounts_id,gcc.chart_of_accounts_id,NULL,'IB006') IB006
,DECODE(xla_balances_calc_pkg.is_control_account
(nvl(xib.code_combination_id,0)
,NULL
,nvl(xib.ledger_id,0)
,nvl(xib.application_id,0))
,0,NULL,'IB007') IB007
,DECODE(xib.party_type_code,'S',NULL,'C',NULL,'IB008') IB008
,DECODE(xib.party_type_code,'S',NVL2(hzp.party_name,NULL,'IB009')
,'C',NVL2(hca.account_number,NULL,'IB009')) IB009
,DECODE(xib.party_type_code,'S',NVL2(apsa.vendor_site_code,NULL,'IB010')
,'C',NVL2(civ.site_use_code,NULL,'IB010')) IB010
,NVL2(glp.period_name,NULL,'IB011') IB011
,DECODE(SIGN(glp.effective_period_num-NVL(xlp.min_effect_period_num,glp.effective_period_num+1)),1,'IB012',NULL) IB012
,NVL2(xib.init_balance_dr,NULL,NVL2(xib.init_balance_cr,NULL,'IB013')) IB013
,DECODE(SIGN(xib.init_balance_dr),-1,'IB014',DECODE(SIGN(xib.init_balance_cr),-1,'IB014')) IB014
,DECODE(xib.MESSAGE_CODES,NULL,NULL,'IB015') IB015
,DECODE(xib.EXISTING_INIT_BALANCE_DR,NULL,NULL,'IB016') IB016
,DECODE(xib.EXISTING_INIT_BALANCE_CR,NULL,NULL,'IB017') IB017
,DECODE(xib.EXISTING_EFFECTIVE_PERIOD_NUM,NULL,NULL,'IB018') IB018
,DECODE(xib.REQUEST_ID ,NULL,NULL,'IB019') IB019
,DECODE(xib.PROGRAM_APPLICATION_ID ,NULL,NULL,'IB020') IB020
,DECODE(xib.PROGRAM_ID ,NULL,NULL,'IB021') IB021
,DECODE(ABS(SIGN(xib.init_balance_dr))+ABS(SIGN(xib.init_balance_cr)),2,'IB023',NULL) IB023
,DECODE(glp.period_num,1,NULL,'IB024') IB024
,DECODE(glp.closing_status,'C',NULL,'P',NULL,'IB025') IB025
,DECODE(glp.adjustment_period_flag ,'Y','IB026',NULL) IB026
,DECODE(glp.period_num,1,decode(glp2.period_num,1,decode(sign(glp.effective_period_num-xlp.min_effect_period_num),0,NULL,'IB027'),NULL),NULL) IB027
FROM xla_ctrl_balances_int xib
,xla_subledgers xls
,gl_ledgers gll
,gl_ledger_relationships glr
,gl_code_combinations gcc
,ap_suppliers aps
,ap_supplier_sites_all apsa
,hz_parties hzp
,hz_cust_accounts hca
,gl_period_statuses glp
,gl_period_statuses glp2
,(SELECT hcs.site_use_id party_site_id
,hca.cust_account_id party_id
,hcs.site_use_code site_use_code
FROM hz_cust_site_uses_all hcs
,hz_cust_acct_sites_all hcas
,hz_cust_accounts hca
WHERE hcs.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id) civ
,(
SELECT cslp.application_id
,cslp.ledger_id
,cslp.code_combination_id
,cslp.party_type_code
,cslp.party_id
,cslp.party_site_id
,MIN(cslp.effective_period_num) min_effect_period_num
FROM
(SELECT xah.application_id
,xah.ledger_id
,xal.code_combination_id
,xal.party_type_code
,xal.party_id
,xal.party_site_id
,MIN(glp.effective_period_num) effective_period_num
FROM xla_ae_headers xah
,xla_ae_lines xal
,gl_period_statuses glp
,xla_ctrl_balances_int xib
WHERE xah.application_id = xal.application_id
AND xah.ae_header_id = xal.ae_header_id
AND glp.period_name = xah.period_name
AND glp.application_id = 101
AND glp.set_of_books_id = xah.ledger_id
AND xah.application_id = xib.application_id
AND xah.ledger_id = xib.ledger_id
AND xal.code_combination_id = xib.code_combination_id
AND xal.party_type_code = xib.party_type_code
AND xal.party_id = xib.party_id
AND NVL(xal.party_site_id,-999) = NVL(xib.party_site_id,-999)
AND xal.control_balance_flag = 'Y'
GROUP BY xah.application_id
,xah.ledger_id
,xal.code_combination_id
,xal.party_type_code
,xal.party_id
,xal.party_site_id
UNION ALL
SELECT xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,xcb.party_site_id
,MIN(xcb.effective_period_num) effective_period_num
FROM xla_control_balances xcb
,xla_ctrl_balances_int xib
WHERE xcb.application_id = xib.application_id
AND xcb.ledger_id = xib.ledger_id
AND xcb.code_combination_id = xib.code_combination_id
AND xcb.party_type_code = xib.party_type_code
AND xcb.party_id = xib.party_id
AND NVL(xcb.party_site_id,-999) = NVL(xib.party_site_id,-999)
GROUP BY
xcb.application_id
,xcb.ledger_id
,xcb.code_combination_id
,xcb.party_type_code
,xcb.party_id
,xcb.party_site_id) cslp
group by cslp.application_id
, cslp.ledger_id
, cslp.code_combination_id
, cslp.party_type_code
, cslp.party_id
, cslp.party_site_id ) xlp
WHERE xib.application_id = xls.application_Id (+)
AND xib.ledger_Id = gll.ledger_id(+)
AND xib.ledger_id = glr.target_ledger_id (+)
AND 'SUBLEDGER' = glr.relationship_type_code(+)
AND 101 = glr.application_id(+)
AND xib.code_combination_id = gcc.code_combination_id(+)
AND xib.party_id = aps.vendor_id(+)
AND xib.party_site_id = apsa.vendor_site_id(+)
AND aps.party_id = hzp.party_id(+)
AND xib.party_id = apsa.vendor_id(+)
AND xib.party_id = hca.cust_account_id(+)
AND xib.party_site_id = civ.party_site_id(+)
AND xib.party_id = civ.party_id(+)
AND xib.ledger_id = glp.set_of_books_id(+)
AND xib.period_name = glp.period_name(+)
AND 101 = glp.application_id(+)
AND xlp.ledger_id = glp2.set_of_books_id
AND xlp.min_effect_period_num= glp2.effective_period_num
AND 101 = glp2.application_id
AND xib.application_id = xlp.application_id(+)
AND xib.ledger_id = xlp.ledger_id(+)
AND xib.code_combination_id = xlp.code_combination_id(+)
AND xib.party_type_code = xlp.party_type_code(+)
AND xib.party_id = xlp.party_id(+)
AND xib.party_site_id = xlp.party_site_id(+)
AND nvl(xib.status,' ') <> 'IMPORTED' ) xtp
WHERE xtp.row_id = xin.rowid );
UPDATE xla_ctrl_balances_int
SET message_codes = message_codes||'IB022'
WHERE(application_id ,ledger_id ,code_combination_id ,party_type_code ,party_id ,nvl(party_site_id,-999))
IN (SELECT application_id ,ledger_id ,code_combination_id ,party_type_code ,party_id ,nvl(party_site_id,-999)
FROM xla_ctrl_balances_int
WHERE nvl(status, ' ') <> 'IMPORTED'
GROUP BY application_id
,ledger_id
,code_combination_id
,party_type_code
,party_id
,party_site_id
HAVING COUNT(*) >1);
UPDATE xla_ctrl_balances_int
SET status = 'ERROR'
,last_update_date =g_date
,last_updated_by = g_user_id
,last_update_login = g_login_id
WHERE message_codes IS NOT NULL
AND status IS NULL;
DELETE FROM xla_ctrl_balances_int
WHERE message_codes IS NULL
AND nvl(status,' ') = 'IMPORTED';
DELETE FROM xla_ctrl_balances_int;