[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jcc.company_segment
, jcc.account_segment
, jcc.cost_segment
, jcc.project_number
, jcc.project_id
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1) account_type
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
WHERE jcc.ledger_id = p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = pc_flex_value_set_id
AND ffv.flex_value = jcc.account_segment
AND (jcc.company_segment, jcc.account_segment, jcc.cost_segment) IN
(SELECT jcc1.company_segment
, jcc1.account_segment
, jcc1.cost_segment
FROM ja_cn_code_combination_v jcc1
WHERE jcc1.CODE_COMBINATION_ID = pc_RET_EARN_CODE_COMBIN_ID
AND jcc1.ledger_id = p_ledger_id);
SELECT jcc.company_segment
, jcc.account_segment
, jcc.cost_segment
, jcc.project_number
, jcc.project_id
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1) account_type
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
WHERE jcc.ledger_id = p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = pc_flex_value_set_id
AND ffv.flex_value = jcc.account_segment
AND (jcc.company_segment, jcc.account_segment) IN
(SELECT jcc1.company_segment
, jcc1.account_segment
FROM ja_cn_code_combination_v jcc1
WHERE jcc1.CODE_COMBINATION_ID = pc_RET_EARN_CODE_COMBIN_ID
AND jcc1.ledger_id = p_ledger_id);*/
SELECT ifs.flex_value_set_id
INTO l_flex_value_set_id
FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
, gl_ledgers led
, Fnd_Id_Flex_Segments ifs
WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND sav.ID_FLEX_CODE = 'GL#'
AND sav.APPLICATION_ID = 101
AND sav.attribute_value = 'Y'
AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
AND ifs.application_id = 101
AND ifs.Id_Flex_Code = 'GL#'
AND ifs.id_flex_num = led.chart_of_accounts_id
AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
AND led.ledger_id = p_ledger_id;
SELECT COUNT(*)
INTO l_row_count
FROM JA_CN_PERIODS
WHERE period_name = p_period_name
AND ledger_id=p_ledger_id;
INSERT INTO JA_CN_PERIODS
( ledger_id
, START_DATE
, END_DATE
, PERIOD_NAME
, PERIOD_NUM
, PERIOD_YEAR
, FIRST_FISCAL_PERIOD_FLAG
, STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
)
SELECT p_ledger_id
, gp.start_date
, gp.end_date
, p_period_name
, gp.period_num+gp.period_year*1000
, gp.period_year
, decode(gp.period_num,1,'Y','N')
, 'O'
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, fnd_global.LOGIN_ID
FROM gl_ledgers led
, gl_periods gp
WHERE led.ledger_id=p_ledger_id
AND led.period_set_name = gp.period_set_name
AND gp.period_name = p_period_name;
SELECT gp.period_year
, gp.period_num
, decode(gp.period_num,1,'Y','N')
INTO l_period_year
, l_period_num
, l_first_fiscal_period_flag
FROM gl_ledgers led
, gl_periods gp
WHERE led.ledger_id=p_ledger_id
AND led.period_set_name = gp.period_set_name
AND gp.period_name = p_period_name;
SELECT MAX(period_num)
INTO l_prior_period_num
FROM JA_CN_PERIODS jjb
WHERE jjb.ledger_id=p_ledger_id
AND jjb.period_num
SELECT period_name
INTO l_prior_period_name
FROM JA_CN_PERIODS jjb
WHERE jjb.ledger_id=p_ledger_id
AND jjb.period_num =l_prior_period_num;
INSERT INTO ja_cn_account_balances(
ledger_id
, legal_entity_id
, company_segment
, period_name
, currency_code
, cost_center
, third_party_type
, third_party_id
, third_party_number
, project_id
, project_number
, project_source
, account_segment
, account_type
, personnel_id
, personnel_number
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
, func_begin_balance_dr
, func_begin_balance_cr
, orig_begin_balance_dr
, Orig_Begin_Balance_Cr
, func_period_net_dr
, func_period_net_cr
, Orig_Period_Net_Dr
, Orig_Period_Net_Cr
, period_mon
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
SELECT ledger_id
, legal_entity_id
, company_segment
, p_period_name
, currency_code
, cost_center
, third_party_type
, third_party_id
, third_party_number
, project_id
, project_number
, project_source
, account_segment
, account_type
, personnel_id
, personnel_number
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
, func_end_Balance_dr
, func_end_balance_cr
, orig_end_balance_dr
, orig_end_balance_cr
, 0
, 0
, 0
, 0
, l_period_month
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
FROM ja_cn_account_balances_v a
WHERE a.ledger_id = p_ledger_id
AND a.legal_entity_id = p_legal_entity_ID
AND a.period_name = l_prior_period_name;
UPDATE ja_cn_account_balances jab
SET func_begin_balance_dr = 0
, func_begin_balance_cr = 0
, orig_begin_balance_dr = 0
, Orig_Begin_Balance_Cr = 0
WHERE ledger_id = p_ledger_id
AND legal_entity_id = p_legal_entity_ID
AND period_name = p_period_name
AND EXISTS(SELECT *
FROM FND_FLEX_VALUES ffv
WHERE PARENT_FLEX_VALUE_LOW IS NULL
AND FLEX_VALUE_SET_ID = l_flex_value_set_id
AND ffv.flex_value = jab.account_segment
AND substr(COMPILED_VALUE_ATTRIBUTES,5,1) IN ('R','E')
);
SELECT sob.RET_EARN_CODE_COMBINATION_ID
, sob.currency_code
INTO l_RET_EARN_CODE_COMBINATION_ID
, l_ret_currence_code
FROM gl_ledgers sob
WHERE sob.ledger_id = p_ledger_id;
SELECT COUNT(*)
INTO l_row_count
FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
, gl_ledgers sob
, Fnd_Id_Flex_Segments ifs
WHERE sav.ID_FLEX_CODE = 'GL#'
AND sav.APPLICATION_ID = 101
AND sav.attribute_value = 'Y'
AND sav.ID_FLEX_NUM = sob.chart_of_accounts_id
AND ifs.application_id = 101
AND ifs.Id_Flex_Code = 'GL#'
AND ifs.id_flex_num = sob.chart_of_accounts_id
AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
AND sob.ledger_id = p_ledger_id
AND sav.SEGMENT_ATTRIBUTE_TYPE IN ('FA_COST_CTR','GL_SECONDARY_TRACKING');
SELECT jcc.company_segment
, jcc.account_segment
, jcc.cost_segment
, jcc.project_number
, jcc.project_id
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
INTO l_ret_company_segment
, l_ret_account_segment
, l_ret_cost_center_seg
, l_ret_project_number
, l_ret_project_id
, l_ret_account_type
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
WHERE jcc.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
AND jcc.ledger_id = p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
AND ffv.flex_value = jcc.account_segment;
SELECT sob.RET_EARN_CODE_COMBINATION_ID
, gb.begin_balance_dr
, gb.begin_balance_cr
, gb.begin_balance_dr_beq
, gb.begin_balance_cr_beq
, gb.currency_code
INTO l_RET_EARN_CODE_COMBINATION_ID
, l_ret_acct_balance_dr
, l_ret_acct_balance_cr
, l_ret_acct_balance_dr_beq
, l_ret_acct_balance_cr_beq
, l_ret_currence_code
FROM gl_ledgers sob
, gl_balances gb
WHERE sob.ledger_id = p_ledger_id
AND sob.ret_earn_code_combination_id = gb.code_combination_id
AND gb.ledger_id = p_ledger_id
AND gb.period_name = p_period_name
AND gb.actual_flag = 'A'
AND gb.currency_code = sob.currency_code;
SELECT jcc.company_segment
, jcc.account_segment
, jcc.cost_segment
, jcc.project_number
, jcc.project_id
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
INTO l_ret_company_segment
, l_ret_account_segment
, l_ret_cost_center_seg
, l_ret_project_number
, l_ret_project_id
, l_ret_account_type
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
WHERE jcc.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
AND jcc.ledger_id = p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
AND ffv.flex_value = jcc.account_segment;
UPDATE ja_cn_account_balances
SET func_begin_balance_dr = l_ret_acct_balance_dr_beq
, func_begin_balance_cr = l_ret_acct_balance_cr_beq
, orig_begin_balance_dr = l_ret_acct_balance_dr
, Orig_Begin_Balance_Cr = l_ret_acct_balance_cr
, last_updated_by = fnd_global.USER_ID
, last_update_date = SYSDATE
, last_update_login = fnd_global.LOGIN_ID
WHERE ledger_id = p_ledger_id
AND legal_entity_id = p_legal_entity_ID
AND company_segment = l_ret_company_segment
AND period_name = p_period_name
AND currency_code = l_ret_currence_code
AND cost_center = l_ret_cost_center_seg
-- AND project_id = l_ret_project_id
AND nvl(project_number,'0')= nvl(l_ret_project_number,'0')
AND account_segment = l_ret_account_segment;
INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
, COST_CENTER
, THIRD_PARTY_TYPE
, THIRD_PARTY_ID
, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE
, ACCOUNT_SEGMENT
, account_type
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN )
VALUES( p_ledger_id
, p_legal_entity_ID
, l_ret_company_segment
, p_period_name
, l_ret_currence_code
, l_ret_cost_center_seg
, NULL
, NULL
, NULL
, l_ret_project_id
, l_ret_project_number
, 'COA'
, l_ret_account_segment
, l_ret_account_type
, NULL
, NULL
, l_ret_acct_balance_dr_beq
, l_ret_acct_balance_cr_beq
, l_ret_acct_balance_dr
, l_ret_acct_balance_cr
, 0
, 0
, 0
, 0
, l_period_month
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID);
PROCEDURE update_balance
( p_balance_rec IN cnao_balance_rec
, p_current_period_flag IN VARCHAR2
)
IS
l_procedure_name VARCHAR2(20):='update_balance';
put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
, p_balance_rec.PERIOD_NAME||':'||p_balance_rec.PERIOD_MON);
UPDATE ja_cn_account_balances
SET FUNC_PERIOD_NET_DR = FUNC_PERIOD_NET_DR + p_balance_rec.FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR = FUNC_PERIOD_NET_CR + p_balance_rec.FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR = ORIG_PERIOD_NET_DR + p_balance_rec.ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR = ORIG_PERIOD_NET_CR + p_balance_rec.ORIG_PERIOD_NET_CR
, LAST_UPDATED_BY = fnd_global.USER_ID
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE ledger_id = p_balance_rec.ledger_id
AND LEGAL_ENTITY_ID = p_balance_rec.LEGAL_ENTITY_ID
AND COMPANY_SEGMENT = p_balance_rec.COMPANY_SEGMENT
AND PERIOD_NAME = p_balance_rec.PERIOD_NAME
AND CURRENCY_CODE = p_balance_rec.CURRENCY_CODE
/*AND nvl(COST_CENTER,0) = nvl(p_balance_rec.COST_CENTER,0)*/
AND ACCOUNT_SEGMENT = p_balance_rec.ACCOUNT_SEGMENT
AND nvl(THIRD_PARTY_TYPE ,0) = nvl(p_balance_rec.THIRD_PARTY_TYPE ,0) -- Update for updating balance also by customer or supplier
AND nvl(THIRD_PARTY_ID ,0) = nvl(p_balance_rec.THIRD_PARTY_ID ,0)
/*AND nvl(THIRD_PARTY_NUMBER,0) = nvl(p_balance_rec.THIRD_PARTY_NUMBER,0)
AND nvl(PROJECT_ID ,0) = nvl(p_balance_rec.PROJECT_ID ,0)
AND nvl(PROJECT_NUMBER ,0) = nvl(p_balance_rec.PROJECT_NUMBER ,0)
AND nvl(PROJECT_SOURCE ,0) = nvl(p_balance_rec.PROJECT_SOURCE ,0)
AND nvl(PERSONNEL_ID ,0) = nvl(p_balance_rec.PERSONNEL_ID ,0)
AND nvl(PERSONNEL_NUMBER ,0) = nvl(p_balance_rec.PERSONNEL_NUMBER ,0)
AND nvl(PERIOD_MON ,0) = nvl(p_balance_rec.PERIOD_MON ,0)*/
-- Add for CNAO V2
AND nvl(SEGMENT1 ,0) = nvl(p_balance_rec.SEGMENT1 ,0)
AND nvl(SEGMENT2 ,0) = nvl(p_balance_rec.SEGMENT2 ,0)
AND nvl(SEGMENT3 ,0) = nvl(p_balance_rec.SEGMENT3 ,0)
AND nvl(SEGMENT4 ,0) = nvl(p_balance_rec.SEGMENT4 ,0)
AND nvl(SEGMENT5 ,0) = nvl(p_balance_rec.SEGMENT5 ,0)
AND nvl(SEGMENT6 ,0) = nvl(p_balance_rec.SEGMENT6 ,0)
AND nvl(SEGMENT7 ,0) = nvl(p_balance_rec.SEGMENT7 ,0)
AND nvl(SEGMENT8 ,0) = nvl(p_balance_rec.SEGMENT8 ,0)
AND nvl(SEGMENT9 ,0) = nvl(p_balance_rec.SEGMENT9 ,0)
AND nvl(SEGMENT10 ,0) = nvl(p_balance_rec.SEGMENT10 ,0)
AND nvl(SEGMENT11 ,0) = nvl(p_balance_rec.SEGMENT11 ,0)
AND nvl(SEGMENT12 ,0) = nvl(p_balance_rec.SEGMENT12 ,0)
AND nvl(SEGMENT13 ,0) = nvl(p_balance_rec.SEGMENT13 ,0)
AND nvl(SEGMENT14 ,0) = nvl(p_balance_rec.SEGMENT14 ,0)
AND nvl(SEGMENT15 ,0) = nvl(p_balance_rec.SEGMENT15 ,0)
AND nvl(SEGMENT16 ,0) = nvl(p_balance_rec.SEGMENT16 ,0)
AND nvl(SEGMENT17 ,0) = nvl(p_balance_rec.SEGMENT17 ,0)
AND nvl(SEGMENT18 ,0) = nvl(p_balance_rec.SEGMENT18 ,0)
AND nvl(SEGMENT19 ,0) = nvl(p_balance_rec.SEGMENT19 ,0)
AND nvl(SEGMENT20 ,0) = nvl(p_balance_rec.SEGMENT20 ,0)
AND nvl(SEGMENT21 ,0) = nvl(p_balance_rec.SEGMENT21 ,0)
AND nvl(SEGMENT22 ,0) = nvl(p_balance_rec.SEGMENT22 ,0)
AND nvl(SEGMENT23 ,0) = nvl(p_balance_rec.SEGMENT23 ,0)
AND nvl(SEGMENT24 ,0) = nvl(p_balance_rec.SEGMENT24 ,0)
AND nvl(SEGMENT25 ,0) = nvl(p_balance_rec.SEGMENT25 ,0)
AND nvl(SEGMENT26 ,0) = nvl(p_balance_rec.SEGMENT26 ,0)
AND nvl(SEGMENT27 ,0) = nvl(p_balance_rec.SEGMENT27 ,0)
AND nvl(SEGMENT28 ,0) = nvl(p_balance_rec.SEGMENT28 ,0)
AND nvl(SEGMENT29 ,0) = nvl(p_balance_rec.SEGMENT29 ,0)
AND nvl(SEGMENT30 ,0) = nvl(p_balance_rec.SEGMENT30 ,0);
put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
, p_balance_rec.ledger_id
||':'|| p_balance_rec.LEGAL_ENTITY_ID
||':'|| p_balance_rec.COMPANY_SEGMENT
||':'|| p_balance_rec.PERIOD_NAME
||':'|| p_balance_rec.CURRENCY_CODE
-- ||':'|| p_balance_rec.COST_CENTER
||':'|| p_balance_rec.THIRD_PARTY_TYPE --Update for updating balance also by customer or supplier
||':'|| p_balance_rec.THIRD_PARTY_ID
-- ||':'|| p_balance_rec.THIRD_PARTY_NUMBER
-- ||':'|| p_balance_rec.PROJECT_ID
-- ||':'|| p_balance_rec.PROJECT_NUMBER
-- ||':'|| p_balance_rec.PROJECT_SOURCE
||':'|| p_balance_rec.ACCOUNT_SEGMENT
||':'|| p_balance_rec.ACCOUNT_type
||':'|| p_balance_rec.PERSONNEL_ID
||':'|| p_balance_rec.PERSONNEL_NUMBER
-- Add for CNAO V2
||':'|| p_balance_rec.SEGMENT1
||':'|| p_balance_rec.SEGMENT2
||':'|| p_balance_rec.SEGMENT3
||':'|| p_balance_rec.SEGMENT4
||':'|| p_balance_rec.SEGMENT5
||':'|| p_balance_rec.SEGMENT6
||':'|| p_balance_rec.SEGMENT7
||':'|| p_balance_rec.SEGMENT8
||':'|| p_balance_rec.SEGMENT9
||':'|| p_balance_rec.SEGMENT10
||':'|| p_balance_rec.SEGMENT11
||':'|| p_balance_rec.SEGMENT12
||':'|| p_balance_rec.SEGMENT13
||':'|| p_balance_rec.SEGMENT14
||':'|| p_balance_rec.SEGMENT15
||':'|| p_balance_rec.SEGMENT16
||':'|| p_balance_rec.SEGMENT17
||':'|| p_balance_rec.SEGMENT18
||':'|| p_balance_rec.SEGMENT19
||':'|| p_balance_rec.SEGMENT20
||':'|| p_balance_rec.SEGMENT21
||':'|| p_balance_rec.SEGMENT22
||':'|| p_balance_rec.SEGMENT23
||':'|| p_balance_rec.SEGMENT24
||':'|| p_balance_rec.SEGMENT25
||':'|| p_balance_rec.SEGMENT26
||':'|| p_balance_rec.SEGMENT27
||':'|| p_balance_rec.SEGMENT28
||':'|| p_balance_rec.SEGMENT29
||':'|| p_balance_rec.SEGMENT30);
INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER
, THIRD_PARTY_TYPE --Update for updating balance also by customer or supplier
, THIRD_PARTY_ID
/*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*/
, ACCOUNT_SEGMENT
, account_type
, PERSONNEL_ID
, PERSONNEL_NUMBER
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES
( p_balance_rec.ledger_id
, p_balance_rec.LEGAL_ENTITY_ID
, p_balance_rec.COMPANY_SEGMENT
, p_balance_rec.PERIOD_NAME
, p_balance_rec.CURRENCY_CODE
--, p_balance_rec.COST_CENTER
, p_balance_rec.THIRD_PARTY_TYPE --Update for updating balance also by customer or supplier
, p_balance_rec.THIRD_PARTY_ID
/*, p_balance_rec.THIRD_PARTY_NUMBER
, p_balance_rec.PROJECT_ID
, p_balance_rec.PROJECT_NUMBER
, p_balance_rec.PROJECT_SOURCE*/
, p_balance_rec.ACCOUNT_SEGMENT
, p_balance_rec.ACCOUNT_type
, p_balance_rec.PERSONNEL_ID
, p_balance_rec.PERSONNEL_NUMBER
, p_balance_rec.SEGMENT1
, p_balance_rec.SEGMENT2
, p_balance_rec.SEGMENT3
, p_balance_rec.SEGMENT4
, p_balance_rec.SEGMENT5
, p_balance_rec.SEGMENT6
, p_balance_rec.SEGMENT7
, p_balance_rec.SEGMENT8
, p_balance_rec.SEGMENT9
, p_balance_rec.SEGMENT10
, p_balance_rec.SEGMENT11
, p_balance_rec.SEGMENT12
, p_balance_rec.SEGMENT13
, p_balance_rec.SEGMENT14
, p_balance_rec.SEGMENT15
, p_balance_rec.SEGMENT16
, p_balance_rec.SEGMENT17
, p_balance_rec.SEGMENT18
, p_balance_rec.SEGMENT19
, p_balance_rec.SEGMENT20
, p_balance_rec.SEGMENT21
, p_balance_rec.SEGMENT22
, p_balance_rec.SEGMENT23
, p_balance_rec.SEGMENT24
, p_balance_rec.SEGMENT25
, p_balance_rec.SEGMENT26
, p_balance_rec.SEGMENT27
, p_balance_rec.SEGMENT28
, p_balance_rec.SEGMENT29
, p_balance_rec.SEGMENT30
, 0
, 0
, 0
, 0
, p_balance_rec.FUNC_PERIOD_NET_DR
, p_balance_rec.FUNC_PERIOD_NET_CR
, p_balance_rec.ORIG_PERIOD_NET_DR
, p_balance_rec.ORIG_PERIOD_NET_CR
, p_balance_rec.PERIOD_MON
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.login_id
);
UPDATE ja_cn_account_balances
SET FUNC_begin_BALANCE_DR = FUNC_begin_BALANCE_DR + p_balance_rec.FUNC_PERIOD_NET_DR
, FUNC_begin_BALANCE_CR = FUNC_begin_BALANCE_cR + p_balance_rec.FUNC_PERIOD_NET_CR
-- added by jarwang for bug 16572904
--, ORIG_begin_BALANCE_DR = FUNC_begin_BALANCE_DR + p_balance_rec.ORIG_PERIOD_NET_DR
--, ORIG_begin_BALANCE_CR = FUNC_begin_BALANCE_cR + p_balance_rec.ORIG_PERIOD_NET_CR
,ORIG_begin_BALANCE_DR = ORIG_begin_BALANCE_DR + p_balance_rec.ORIG_PERIOD_NET_DR
, ORIG_begin_BALANCE_CR = ORIG_begin_BALANCE_CR + p_balance_rec.ORIG_PERIOD_NET_CR
, LAST_UPDATED_BY = fnd_global.USER_ID
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE ledger_id = p_balance_rec.ledger_id
AND LEGAL_ENTITY_ID = p_balance_rec.LEGAL_ENTITY_ID
AND COMPANY_SEGMENT = p_balance_rec.COMPANY_SEGMENT
AND PERIOD_NAME = p_balance_rec.PERIOD_NAME
AND CURRENCY_CODE = p_balance_rec.CURRENCY_CODE
--AND nvl(COST_CENTER,0) = nvl(p_balance_rec.COST_CENTER,0)
AND ACCOUNT_SEGMENT = p_balance_rec.ACCOUNT_SEGMENT
AND nvl(THIRD_PARTY_TYPE ,0) = nvl(p_balance_rec.THIRD_PARTY_TYPE ,0) -- Update for updating balance also by customer or supplier
AND nvl(THIRD_PARTY_ID ,0) = nvl(p_balance_rec.THIRD_PARTY_ID ,0)
AND nvl(SEGMENT1, 0) = nvl(p_balance_rec.SEGMENT1,0)
AND nvl(SEGMENT2, 0) = nvl(p_balance_rec.SEGMENT2,0)
AND nvl(SEGMENT3, 0) = nvl(p_balance_rec.SEGMENT3,0)
AND nvl(SEGMENT4, 0) = nvl(p_balance_rec.SEGMENT4,0)
AND nvl(SEGMENT5, 0) = nvl(p_balance_rec.SEGMENT5,0)
AND nvl(SEGMENT6, 0) = nvl(p_balance_rec.SEGMENT6,0)
AND nvl(SEGMENT7, 0) = nvl(p_balance_rec.SEGMENT7,0)
AND nvl(SEGMENT8, 0) = nvl(p_balance_rec.SEGMENT8,0)
AND nvl(SEGMENT9, 0) = nvl(p_balance_rec.SEGMENT9,0)
AND nvl(SEGMENT10, 0) = nvl(p_balance_rec.SEGMENT10,0)
AND nvl(SEGMENT11, 0) = nvl(p_balance_rec.SEGMENT11,0)
AND nvl(SEGMENT12, 0) = nvl(p_balance_rec.SEGMENT12,0)
AND nvl(SEGMENT13, 0) = nvl(p_balance_rec.SEGMENT13,0)
AND nvl(SEGMENT14, 0) = nvl(p_balance_rec.SEGMENT14,0)
AND nvl(SEGMENT15, 0) = nvl(p_balance_rec.SEGMENT15,0)
AND nvl(SEGMENT16, 0) = nvl(p_balance_rec.SEGMENT16,0)
AND nvl(SEGMENT17, 0) = nvl(p_balance_rec.SEGMENT17,0)
AND nvl(SEGMENT18, 0) = nvl(p_balance_rec.SEGMENT18,0)
AND nvl(SEGMENT19, 0) = nvl(p_balance_rec.SEGMENT19,0)
AND nvl(SEGMENT20, 0) = nvl(p_balance_rec.SEGMENT20,0)
AND nvl(SEGMENT21, 0) = nvl(p_balance_rec.SEGMENT21,0)
AND nvl(SEGMENT22, 0) = nvl(p_balance_rec.SEGMENT22,0)
AND nvl(SEGMENT23, 0) = nvl(p_balance_rec.SEGMENT23,0)
AND nvl(SEGMENT24, 0) = nvl(p_balance_rec.SEGMENT24,0)
AND nvl(SEGMENT25, 0) = nvl(p_balance_rec.SEGMENT25,0)
AND nvl(SEGMENT26, 0) = nvl(p_balance_rec.SEGMENT26,0)
AND nvl(SEGMENT27, 0) = nvl(p_balance_rec.SEGMENT27,0)
AND nvl(SEGMENT28, 0) = nvl(p_balance_rec.SEGMENT28,0)
AND nvl(SEGMENT29, 0) = nvl(p_balance_rec.SEGMENT29,0)
AND nvl(SEGMENT30, 0) = nvl(p_balance_rec.SEGMENT30,0);
put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
, p_balance_rec.ledger_id
||':'|| p_balance_rec.LEGAL_ENTITY_ID
||':'|| p_balance_rec.COMPANY_SEGMENT
||':'|| p_balance_rec.PERIOD_NAME
||':'|| p_balance_rec.CURRENCY_CODE
||':'|| p_balance_rec.COST_CENTER
||':'|| p_balance_rec.THIRD_PARTY_TYPE
||':'|| p_balance_rec.THIRD_PARTY_ID
||':'|| p_balance_rec.THIRD_PARTY_NUMBER
||':'|| p_balance_rec.PROJECT_ID
||':'|| p_balance_rec.PROJECT_NUMBER
||':'|| p_balance_rec.PROJECT_SOURCE
||':'|| p_balance_rec.ACCOUNT_SEGMENT
||':'|| p_balance_rec.ACCOUNT_type
||':'|| p_balance_rec.PERSONNEL_ID
||':'|| p_balance_rec.PERSONNEL_NUMBER
||':'|| p_balance_rec.SEGMENT1
||':'|| p_balance_rec.SEGMENT2
||':'|| p_balance_rec.SEGMENT3
||':'|| p_balance_rec.SEGMENT4
||':'|| p_balance_rec.SEGMENT5
||':'|| p_balance_rec.SEGMENT6
||':'|| p_balance_rec.SEGMENT7
||':'|| p_balance_rec.SEGMENT8
||':'|| p_balance_rec.SEGMENT9
||':'|| p_balance_rec.SEGMENT10
||':'|| p_balance_rec.SEGMENT11
||':'|| p_balance_rec.SEGMENT12
||':'|| p_balance_rec.SEGMENT13
||':'|| p_balance_rec.SEGMENT14
||':'|| p_balance_rec.SEGMENT15
||':'|| p_balance_rec.SEGMENT16
||':'|| p_balance_rec.SEGMENT17
||':'|| p_balance_rec.SEGMENT18
||':'|| p_balance_rec.SEGMENT19
||':'|| p_balance_rec.SEGMENT20
||':'|| p_balance_rec.SEGMENT21
||':'|| p_balance_rec.SEGMENT22
||':'|| p_balance_rec.SEGMENT23
||':'|| p_balance_rec.SEGMENT24
||':'|| p_balance_rec.SEGMENT25
||':'|| p_balance_rec.SEGMENT26
||':'|| p_balance_rec.SEGMENT27
||':'|| p_balance_rec.SEGMENT28
||':'|| p_balance_rec.SEGMENT29
||':'|| p_balance_rec.SEGMENT30 );
INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER -- Update for updating balance also by customer or supplier
, THIRD_PARTY_TYPE
, THIRD_PARTY_ID
/*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*/
, ACCOUNT_SEGMENT
, account_type
, PERSONNEL_ID
, PERSONNEL_NUMBER
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES
( p_balance_rec.ledger_id
, p_balance_rec.LEGAL_ENTITY_ID
, p_balance_rec.COMPANY_SEGMENT
, p_balance_rec.PERIOD_NAME
, p_balance_rec.CURRENCY_CODE
-- , p_balance_rec.COST_CENTER
, p_balance_rec.THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
, p_balance_rec.THIRD_PARTY_ID
/* , p_balance_rec.THIRD_PARTY_NUMBER
, p_balance_rec.PROJECT_ID
, p_balance_rec.PROJECT_NUMBER
, p_balance_rec.PROJECT_SOURCE*/
, p_balance_rec.ACCOUNT_SEGMENT
, p_balance_rec.ACCOUNT_type
, p_balance_rec.PERSONNEL_ID
, p_balance_rec.PERSONNEL_NUMBER
, p_balance_rec.SEGMENT1
, p_balance_rec.SEGMENT2
, p_balance_rec.SEGMENT3
, p_balance_rec.SEGMENT4
, p_balance_rec.SEGMENT5
, p_balance_rec.SEGMENT6
, p_balance_rec.SEGMENT7
, p_balance_rec.SEGMENT8
, p_balance_rec.SEGMENT9
, p_balance_rec.SEGMENT10
, p_balance_rec.SEGMENT11
, p_balance_rec.SEGMENT12
, p_balance_rec.SEGMENT13
, p_balance_rec.SEGMENT14
, p_balance_rec.SEGMENT15
, p_balance_rec.SEGMENT16
, p_balance_rec.SEGMENT17
, p_balance_rec.SEGMENT18
, p_balance_rec.SEGMENT19
, p_balance_rec.SEGMENT20
, p_balance_rec.SEGMENT21
, p_balance_rec.SEGMENT22
, p_balance_rec.SEGMENT23
, p_balance_rec.SEGMENT24
, p_balance_rec.SEGMENT25
, p_balance_rec.SEGMENT26
, p_balance_rec.SEGMENT27
, p_balance_rec.SEGMENT28
, p_balance_rec.SEGMENT29
, p_balance_rec.SEGMENT30
, p_balance_rec.FUNC_PERIOD_NET_DR
, p_balance_rec.FUNC_PERIOD_NET_CR
, p_balance_rec.ORIG_PERIOD_NET_DR
, p_balance_rec.ORIG_PERIOD_NET_CR
, 0
, 0
, 0
, 0
, p_balance_rec.PERIOD_MON
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.login_id
);
END update_balance;
PROCEDURE update_retained_parent_account
( p_period_name IN VARCHAR2
, p_ledger_id IN NUMBER
, p_legal_entity_ID IN NUMBER
)
IS
l_procedure_name VARCHAR2(30):='update_retained_account';
SELECT led.RET_EARN_CODE_COMBINATION_ID
, led.currency_code
INTO l_RET_EARN_CODE_COMBINATION_ID
, l_ret_currence_code
FROM gl_ledgers led
WHERE led.ledger_id = p_ledger_id;
SELECT DISTINCT Account_Segment
INTO lv_account_number
FROM ja_cn_code_combination_v Jcc
WHERE Jcc.Code_Combination_ID = l_RET_EARN_CODE_COMBINATION_ID;
SELECT COUNT(*)
INTO l_row_count
FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
, gl_ledgers led
, Fnd_Id_Flex_Segments ifs
WHERE sav.ID_FLEX_CODE = 'GL#'
AND sav.APPLICATION_ID = 101
AND sav.attribute_value = 'Y'
AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
AND ifs.application_id = 101
AND ifs.Id_Flex_Code = 'GL#'
AND ifs.id_flex_num = led.chart_of_accounts_id
AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
AND led.ledger_id = p_ledger_id
AND sav.SEGMENT_ATTRIBUTE_TYPE IN ('FA_COST_CTR','GL_SECONDARY_TRACKING');
SELECT period_year
INTO l_period_year
FROM ja_cn_periods
WHERE period_name=p_period_name
AND ledger_id=p_ledger_id;
SELECT max(period_num)
INTO l_last_period_num
FROM ja_cn_periods
WHERE ledger_id=p_ledger_id;
SELECT ifs.flex_value_set_id
INTO l_flex_value_set_id
FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
, gl_ledgers led
, Fnd_Id_Flex_Segments ifs
WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND sav.ID_FLEX_CODE = 'GL#'
AND sav.APPLICATION_ID = 101
AND sav.attribute_value = 'Y'
AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
AND ifs.application_id = 101
AND ifs.Id_Flex_Code = 'GL#'
AND ifs.id_flex_num = led.chart_of_accounts_id
AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
AND led.ledger_id = p_ledger_id;
DELETE
FROM JA_CN_LEDGER_LE_BSV_GT;
INSERT INTO ja_cn_account_balances_post_gt(
LEDGER_ID
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
, THIRD_PARTY_TYPE
, THIRD_PARTY_ID
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, PERIOD_NUM
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
)
SELECT DISTINCT p_ledger_id
, p_legal_entity_ID
, jcc.company_segment
, jcp.period_name
, gb.currency_code
, NULL
, NULL
, jcc.account_segment
, NULL
, NULL
, sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
, sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
, sum(gb.begin_balance_dr)
, sum(gb.begin_balance_cr)
, sum(nvl(gb.PERIOD_NET_DR_BEQ, gb.PERIOD_NET_DR))
, sum(nvl(gb.PERIOD_NET_CR_BEQ, gb.PERIOD_NET_CR))
, sum(gb.PERIOD_NET_DR)
, sum(gb.PERIOD_NET_CR)
, jcp.period_num - jcp.period_year*1000
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
, jcp.period_num
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
, jcc.SEGMENT1
, jcc.SEGMENT2
, jcc.SEGMENT3
, jcc.SEGMENT4
, jcc.SEGMENT5
, jcc.SEGMENT7
, jcc.SEGMENT8
, jcc.SEGMENT9
, jcc.SEGMENT10
, jcc.SEGMENT11
, jcc.SEGMENT12
, jcc.SEGMENT13
, jcc.SEGMENT14
, jcc.SEGMENT15
, jcc.SEGMENT16
, jcc.SEGMENT17
, jcc.SEGMENT18
, jcc.SEGMENT19
, jcc.SEGMENT20
, jcc.SEGMENT21
, jcc.SEGMENT22
, jcc.SEGMENT23
, jcc.SEGMENT24
, jcc.SEGMENT25
, jcc.SEGMENT26
, jcc.SEGMENT27
, jcc.SEGMENT28
, jcc.SEGMENT29
, jcc.SEGMENT30
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
, ja_cn_periods jcp
, gl_balances gb
, ja_cn_code_combination_v jcc1
WHERE jcc.ledger_id = p_ledger_id --parameter: p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id --variable: l_flex_value_set_id
AND ffv.flex_value = jcc.account_segment
AND jcp.ledger_id = jcc.ledger_id
AND jcp.period_num BETWEEN l_period_num AND l_last_period_num --variable: l_period_num and l_last_period_num
AND jcc.account_segment = lv_account_number --variable: lv_account_number
--Balance condition
AND gb.ledger_id = jcc1.ledger_id
AND gb.code_combination_id = jcc1.code_combination_id
AND gb.period_name = jcp.period_name
AND gb.actual_flag = 'A'
--Segment codition
AND jcc1.ledger_id = jcc.ledger_id
AND jcc1.code_combination_id = jcc.code_combination_id
AND jcc1.company_segment = jcc.company_segment
AND jcc1.account_segment = jcc.account_segment
AND nvl(jcc1.SEGMENT1,'0')= nvl(jcc.SEGMENT1,'0')
AND nvl(jcc1.SEGMENT2,'0')= nvl(jcc.SEGMENT2,'0')
AND nvl(jcc1.SEGMENT3,'0')= nvl(jcc.SEGMENT3,'0')
AND nvl(jcc1.SEGMENT4,'0')= nvl(jcc.SEGMENT4,'0')
AND nvl(jcc1.SEGMENT5,'0')= nvl(jcc.SEGMENT5,'0')
AND nvl(jcc1.SEGMENT6,'0')= nvl(jcc.SEGMENT6,'0')
AND nvl(jcc1.SEGMENT7,'0')= nvl(jcc.SEGMENT7,'0')
AND nvl(jcc1.SEGMENT8,'0')= nvl(jcc.SEGMENT8,'0')
AND nvl(jcc1.SEGMENT9,'0')= nvl(jcc.SEGMENT9,'0')
AND nvl(jcc1.SEGMENT10,'0')= nvl(jcc.SEGMENT10,'0')
AND nvl(jcc1.SEGMENT11,'0')= nvl(jcc.SEGMENT11,'0')
AND nvl(jcc1.SEGMENT12,'0')= nvl(jcc.SEGMENT12,'0')
AND nvl(jcc1.SEGMENT13,'0')= nvl(jcc.SEGMENT13,'0')
AND nvl(jcc1.SEGMENT14,'0')= nvl(jcc.SEGMENT14,'0')
AND nvl(jcc1.SEGMENT15,'0')= nvl(jcc.SEGMENT15,'0')
AND nvl(jcc1.SEGMENT16,'0')= nvl(jcc.SEGMENT16,'0')
AND nvl(jcc1.SEGMENT17,'0')= nvl(jcc.SEGMENT17,'0')
AND nvl(jcc1.SEGMENT18,'0')= nvl(jcc.SEGMENT18,'0')
AND nvl(jcc1.SEGMENT19,'0')= nvl(jcc.SEGMENT19,'0')
AND nvl(jcc1.SEGMENT20,'0')= nvl(jcc.SEGMENT20,'0')
AND nvl(jcc1.SEGMENT21,'0')= nvl(jcc.SEGMENT21,'0')
AND nvl(jcc1.SEGMENT22,'0')= nvl(jcc.SEGMENT22,'0')
AND nvl(jcc1.SEGMENT23,'0')= nvl(jcc.SEGMENT23,'0')
AND nvl(jcc1.SEGMENT24,'0')= nvl(jcc.SEGMENT24,'0')
AND nvl(jcc1.SEGMENT25,'0')= nvl(jcc.SEGMENT25,'0')
AND nvl(jcc1.SEGMENT26,'0')= nvl(jcc.SEGMENT26,'0')
AND nvl(jcc1.SEGMENT27,'0')= nvl(jcc.SEGMENT27,'0')
AND nvl(jcc1.SEGMENT28,'0')= nvl(jcc.SEGMENT28,'0')
AND nvl(jcc1.SEGMENT29,'0')= nvl(jcc.SEGMENT29,'0')
AND nvl(jcc1.SEGMENT30,'0')= nvl(jcc.SEGMENT30,'0')
--BSV condition
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = p_ledger_id --parameter: p_ledger_id
AND llbg.Legal_Entity_Id = p_legal_entity_id) --parameter: p_legal_entity_id
GROUP BY jcc.company_segment
, jcp.period_name
, gb.currency_code
, jcc.account_segment
, jcp.period_num - jcp.period_year*1000
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
, jcp.period_num
/*, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID*/
, jcc.SEGMENT1
, jcc.SEGMENT2
, jcc.SEGMENT3
, jcc.SEGMENT4
, jcc.SEGMENT5
, jcc.SEGMENT7
, jcc.SEGMENT8
, jcc.SEGMENT9
, jcc.SEGMENT10
, jcc.SEGMENT11
, jcc.SEGMENT12
, jcc.SEGMENT13
, jcc.SEGMENT14
, jcc.SEGMENT15
, jcc.SEGMENT16
, jcc.SEGMENT17
, jcc.SEGMENT18
, jcc.SEGMENT19
, jcc.SEGMENT20
, jcc.SEGMENT21
, jcc.SEGMENT22
, jcc.SEGMENT23
, jcc.SEGMENT24
, jcc.SEGMENT25
, jcc.SEGMENT26
, jcc.SEGMENT27
, jcc.SEGMENT28
, jcc.SEGMENT29
, jcc.SEGMENT30;
/* INSERT INTO ja_cn_account_balances_post_gt(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER
, THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
, THIRD_PARTY_ID
\*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*\
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, period_num
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
)
SELECT DISTINCT p_ledger_id
, p_legal_entity_ID
, jcc.company_segment
, jcp.period_name
, l_ret_currence_code
-- , NULL
, NULL -- Update for updating balance also by customer or supplier
, NULL
\*, NULL
, NULL
, NULL
, 'COA'*\
, jcc.account_segment
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, jcp.period_num - jcp.period_year*1000
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
, jcp.period_num
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
, jcc.SEGMENT1
, jcc.SEGMENT2
, jcc.SEGMENT3
, jcc.SEGMENT4
, jcc.SEGMENT5
, jcc.SEGMENT7
, jcc.SEGMENT8
, jcc.SEGMENT9
, jcc.SEGMENT10
, jcc.SEGMENT11
, jcc.SEGMENT12
, jcc.SEGMENT13
, jcc.SEGMENT14
, jcc.SEGMENT15
, jcc.SEGMENT16
, jcc.SEGMENT17
, jcc.SEGMENT18
, jcc.SEGMENT19
, jcc.SEGMENT20
, jcc.SEGMENT21
, jcc.SEGMENT22
, jcc.SEGMENT23
, jcc.SEGMENT24
, jcc.SEGMENT25
, jcc.SEGMENT26
, jcc.SEGMENT27
, jcc.SEGMENT28
, jcc.SEGMENT29
, jcc.SEGMENT30
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
, ja_cn_periods jcp
WHERE jcc.ledger_id = p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
AND ffv.flex_value = jcc.account_segment
AND jcp.ledger_id = p_ledger_id
AND jcp.period_num BETWEEN l_period_num AND l_last_period_num*/
/*AND EXISTS((SELECT *
FROM ja_cn_code_combination_v jcc1
WHERE jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
AND jcc1.ledger_id = p_ledger_id
AND jcc1.account_segment=jcc.account_segment
AND nvl(jcc1.project_id,'0') =nvl(jcc.project_id ,'0')
AND nvl(jcc1.project_number,'0') =nvl(jcc.project_number,'0') ) )*//*;*/
INSERT INTO ja_cn_account_balances_post_gt(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER
, THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
, THIRD_PARTY_ID
\*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*\
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, period_num
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30 )
SELECT DISTINCT p_ledger_id
, p_legal_entity_ID
, jcc.company_segment
, jcp.period_name
, l_ret_currence_code
--, NULL
, NULL -- Update for updating balance also by customer or supplier
, NULL
\*, NULL
, NULL
, NULL
, 'COA'*\
, jcc.account_segment
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, jcp.period_num - jcp.period_year*1000
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
, jcp.period_num
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
, jcc.SEGMENT1
, jcc.SEGMENT2
, jcc.SEGMENT3
, jcc.SEGMENT4
, jcc.SEGMENT5
, jcc.SEGMENT7
, jcc.SEGMENT8
, jcc.SEGMENT9
, jcc.SEGMENT10
, jcc.SEGMENT11
, jcc.SEGMENT12
, jcc.SEGMENT13
, jcc.SEGMENT14
, jcc.SEGMENT15
, jcc.SEGMENT16
, jcc.SEGMENT17
, jcc.SEGMENT18
, jcc.SEGMENT19
, jcc.SEGMENT20
, jcc.SEGMENT21
, jcc.SEGMENT22
, jcc.SEGMENT23
, jcc.SEGMENT24
, jcc.SEGMENT25
, jcc.SEGMENT26
, jcc.SEGMENT27
, jcc.SEGMENT28
, jcc.SEGMENT29
, jcc.SEGMENT30
FROM ja_cn_code_combination_v jcc
, FND_FLEX_VALUES ffv
, ja_cn_periods jcp
WHERE jcc.ledger_id = p_ledger_id
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
AND ffv.flex_value = jcc.account_segment
AND jcp.ledger_id=p_ledger_id
AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
\* AND EXISTS((SELECT *
FROM ja_cn_code_combination_v jcc1
WHERE jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
AND jcc1.ledger_id = p_ledger_id
AND jcc1.account_segment=jcc.account_segment
AND jcc1.cost_segment =jcc.cost_segment
AND nvl(jcc1.project_id,'0') =nvl(jcc.project_id ,'0')
AND nvl(jcc1.project_number,'0') =nvl(jcc.project_number,'0') ) )*\;
/* UPDATE ja_cn_account_balances_post_gt u
SET (FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, project_source)=
(SELECT FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, project_source
FROM ja_cn_account_balances
WHERE ledger_id = p_ledger_id
AND period_name = u.period_name
AND LEGAL_ENTITY_ID = p_legal_entity_ID
AND COMPANY_SEGMENT = u.company_segment
AND CURRENCY_CODE = u.currency_code
--AND nvl(COST_CENTER,0) = nvl(u.cost_center,0)
AND THIRD_PARTY_TYPE IS NULL -- Update for updating balance also by customer or supplier
AND THIRD_PARTY_ID IS NULL
\*AND THIRD_PARTY_NUMBER IS NULL
AND nvl(PROJECT_ID,'0')= nvl(u.project_id,'0')
AND nvl(PROJECT_NUMBER,'0')= nvl(u.project_number,'0')
and nvl(PROJECT_source,' ')=nvl(u.PROJECT_source,'COA')*\
AND ACCOUNT_SEGMENT = u.account_segment
-- AND PERSONNEL_ID IS NULL
-- AND PERSONNEL_NUMBER IS NULL
-- CNAO V2
AND nvl(SEGMENT1,'0')= nvl(u.SEGMENT1,'0')
AND nvl(SEGMENT2,'0')= nvl(u.SEGMENT2,'0')
AND nvl(SEGMENT3,'0')= nvl(u.SEGMENT3,'0')
AND nvl(SEGMENT4,'0')= nvl(u.SEGMENT4,'0')
AND nvl(SEGMENT5,'0')= nvl(u.SEGMENT5,'0')
AND nvl(SEGMENT6,'0')= nvl(u.SEGMENT6,'0')
AND nvl(SEGMENT7,'0')= nvl(u.SEGMENT7,'0')
AND nvl(SEGMENT8,'0')= nvl(u.SEGMENT8,'0')
AND nvl(SEGMENT9,'0')= nvl(u.SEGMENT9,'0')
AND nvl(SEGMENT10,'0')= nvl(u.SEGMENT10,'0')
AND nvl(SEGMENT11,'0')= nvl(u.SEGMENT11,'0')
AND nvl(SEGMENT12,'0')= nvl(u.SEGMENT12,'0')
AND nvl(SEGMENT13,'0')= nvl(u.SEGMENT13,'0')
AND nvl(SEGMENT14,'0')= nvl(u.SEGMENT14,'0')
AND nvl(SEGMENT15,'0')= nvl(u.SEGMENT15,'0')
AND nvl(SEGMENT16,'0')= nvl(u.SEGMENT16,'0')
AND nvl(SEGMENT17,'0')= nvl(u.SEGMENT17,'0')
AND nvl(SEGMENT18,'0')= nvl(u.SEGMENT18,'0')
AND nvl(SEGMENT19,'0')= nvl(u.SEGMENT19,'0')
AND nvl(SEGMENT20,'0')= nvl(u.SEGMENT20,'0')
AND nvl(SEGMENT21,'0')= nvl(u.SEGMENT21,'0')
AND nvl(SEGMENT22,'0')= nvl(u.SEGMENT22,'0')
AND nvl(SEGMENT23,'0')= nvl(u.SEGMENT23,'0')
AND nvl(SEGMENT24,'0')= nvl(u.SEGMENT24,'0')
AND nvl(SEGMENT25,'0')= nvl(u.SEGMENT25,'0')
AND nvl(SEGMENT26,'0')= nvl(u.SEGMENT26,'0')
AND nvl(SEGMENT27,'0')= nvl(u.SEGMENT27,'0')
AND nvl(SEGMENT28,'0')= nvl(u.SEGMENT28,'0')
AND nvl(SEGMENT29,'0')= nvl(u.SEGMENT29,'0')
AND nvl(SEGMENT30,'0')= nvl(u.SEGMENT30,'0')
);*/
/* UPDATE ja_cn_account_balances_post_gt u
SET ( FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR)=
( SELECT sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
, sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
, sum(gb.begin_balance_dr)
, sum(gb.begin_balance_cr)
FROM gl_balances gb
, ja_cn_code_combination_v jcc
, ja_cn_code_combination_v jcc1
WHERE gb.ledger_id = p_ledger_id
AND jcc.ledger_id = p_ledger_id
AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
AND jcc.company_segment = u.company_segment
AND jcc.account_segment = u.account_segment
-- AND nvl(jcc.cost_segment,0)= nvl(u.cost_center,0)
-- AND nvl(jcc.project_number,'0') = nvl(u.project_number,'0')
AND gb.currency_code = u.currency_code
AND gb.period_name = u.period_name
AND jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
AND jcc1.ledger_id = p_ledger_id
AND jcc1.company_segment = jcc.company_segment
AND jcc1.account_segment = jcc.account_segment
--AND jcc1.cost_segment = jcc.cost_segment
--AND jcc1.other_columns = jcc.other_columns
AND gb.actual_flag = 'A'
)
WHERE period_mon=1;
UPDATE ja_cn_account_balances_post_gt u
SET ( FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR)=
( SELECT sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
, sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
, sum(gb.begin_balance_dr)
, sum(gb.begin_balance_cr)
FROM gl_balances gb
, ja_cn_code_combination_v jcc
, ja_cn_code_combination_v jcc1
WHERE gb.ledger_id = p_ledger_id
AND jcc.ledger_id = p_ledger_id
AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
AND jcc.company_segment = u.company_segment
AND jcc.account_segment = u.account_segment
-- AND nvl(jcc.cost_segment,0)= nvl(u.cost_center,0)
-- AND nvl(jcc.project_number,'0') = nvl(u.project_number,'0')
AND gb.currency_code = u.currency_code
AND gb.period_name = u.period_name
AND jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
AND jcc1.ledger_id = p_ledger_id
AND jcc1.company_segment = jcc.company_segment
AND jcc1.account_segment = jcc.account_segment
--AND jcc1.cost_segment = jcc.cost_segment
--AND jcc1.other_columns = jcc.other_columns
AND gb.actual_flag = 'A'
)
WHERE period_mon=1;*/
UPDATE ja_cn_account_balances_post_gt u
SET FUNC_BEGIN_BALANCE_DR = nvl(FUNC_BEGIN_BALANCE_DR,0)
, FUNC_BEGIN_BALANCE_CR = nvl(FUNC_BEGIN_BALANCE_CR,0)
, ORIG_BEGIN_BALANCE_DR = nvl(ORIG_BEGIN_BALANCE_DR,0)
, ORIG_BEGIN_BALANCE_CR = nvl(ORIG_BEGIN_BALANCE_CR,0)
, FUNC_PERIOD_NET_DR = nvl(FUNC_PERIOD_NET_DR ,0)
, FUNC_PERIOD_NET_CR = nvl(FUNC_PERIOD_NET_CR ,0)
, ORIG_PERIOD_NET_DR = nvl(ORIG_PERIOD_NET_DR ,0)
, ORIG_PERIOD_NET_CR = nvl(ORIG_PERIOD_NET_CR ,0);
/* --update the other month balance
SELECT max(jcp.period_num - l_period_year*1000)
INTO l_number
FROM ja_cn_periods jcp;
UPDATE ja_cn_account_balances_post_gt u
SET (FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR )=
(SELECT FUNC_BEGIN_BALANCE_DR + FUNC_PERIOD_NET_DR
, FUNC_BEGIN_BALANCE_CR + FUNC_PERIOD_NET_CR
, ORIG_BEGIN_BALANCE_DR + ORIG_PERIOD_NET_DR
, ORIG_BEGIN_BALANCE_CR + ORIG_PERIOD_NET_CR
FROM ja_cn_account_balances_post_gt
WHERE period_num=u.period_num - 1
AND ledger_id = p_ledger_id
AND LEGAL_ENTITY_ID = p_legal_entity_ID
AND COMPANY_SEGMENT = u.company_segment
AND CURRENCY_CODE = u.currency_code
--AND nvl(COST_CENTER,0) = nvl(u.cost_center,0)
AND THIRD_PARTY_TYPE IS NULL -- Update for updating balance also by customer or supplier
AND THIRD_PARTY_ID IS NULL
\*AND THIRD_PARTY_NUMBER IS NULL
AND nvl(PROJECT_ID ,'0') = nvl(u.project_id ,'0')
AND nvl(PROJECT_NUMBER ,'0') = nvl(u.project_number,'0')*\
AND ACCOUNT_SEGMENT = u.account_segment
AND PERSONNEL_ID IS NULL
AND PERSONNEL_NUMBER IS NULL
-- CNAO V2
AND nvl(SEGMENT1,'0')= nvl(u.SEGMENT1,'0')
AND nvl(SEGMENT2,'0')= nvl(u.SEGMENT2,'0')
AND nvl(SEGMENT3,'0')= nvl(u.SEGMENT3,'0')
AND nvl(SEGMENT4,'0')= nvl(u.SEGMENT4,'0')
AND nvl(SEGMENT5,'0')= nvl(u.SEGMENT5,'0')
AND nvl(SEGMENT6,'0')= nvl(u.SEGMENT6,'0')
AND nvl(SEGMENT7,'0')= nvl(u.SEGMENT7,'0')
AND nvl(SEGMENT8,'0')= nvl(u.SEGMENT8,'0')
AND nvl(SEGMENT9,'0')= nvl(u.SEGMENT9,'0')
AND nvl(SEGMENT10,'0')= nvl(u.SEGMENT10,'0')
AND nvl(SEGMENT11,'0')= nvl(u.SEGMENT11,'0')
AND nvl(SEGMENT12,'0')= nvl(u.SEGMENT12,'0')
AND nvl(SEGMENT13,'0')= nvl(u.SEGMENT13,'0')
AND nvl(SEGMENT14,'0')= nvl(u.SEGMENT14,'0')
AND nvl(SEGMENT15,'0')= nvl(u.SEGMENT15,'0')
AND nvl(SEGMENT16,'0')= nvl(u.SEGMENT16,'0')
AND nvl(SEGMENT17,'0')= nvl(u.SEGMENT17,'0')
AND nvl(SEGMENT18,'0')= nvl(u.SEGMENT18,'0')
AND nvl(SEGMENT19,'0')= nvl(u.SEGMENT19,'0')
AND nvl(SEGMENT20,'0')= nvl(u.SEGMENT20,'0')
AND nvl(SEGMENT21,'0')= nvl(u.SEGMENT21,'0')
AND nvl(SEGMENT22,'0')= nvl(u.SEGMENT22,'0')
AND nvl(SEGMENT23,'0')= nvl(u.SEGMENT23,'0')
AND nvl(SEGMENT24,'0')= nvl(u.SEGMENT24,'0')
AND nvl(SEGMENT25,'0')= nvl(u.SEGMENT25,'0')
AND nvl(SEGMENT26,'0')= nvl(u.SEGMENT26,'0')
AND nvl(SEGMENT27,'0')= nvl(u.SEGMENT27,'0')
AND nvl(SEGMENT28,'0')= nvl(u.SEGMENT28,'0')
AND nvl(SEGMENT29,'0')= nvl(u.SEGMENT29,'0')
AND nvl(SEGMENT30,'0')= nvl(u.SEGMENT30,'0'))
WHERE u.period_mon=i;
DELETE ja_cn_account_balances u
WHERE EXISTS
(SELECT *
FROM ja_cn_account_balances_post_gt t
WHERE t.ledger_id =u.ledger_id
AND t.LEGAL_ENTITY_ID =u.LEGAL_ENTITY_ID
AND t.COMPANY_SEGMENT =u.COMPANY_SEGMENT
AND t.CURRENCY_CODE =u.CURRENCY_CODE
AND t.Period_Name =u.Period_Name --Added for fixing bug 9582957
/* AND nvl(t.COST_CENTER,0) =nvl(u.COST_CENTER,0)*/
AND t.ACCOUNT_SEGMENT =u.ACCOUNT_SEGMENT
AND u.ACCOUNT_SEGMENT =lv_account_number --Added for fixing bug 9582957
AND nvl(t.THIRD_PARTY_TYPE ,'0') =nvl(u.THIRD_PARTY_TYPE ,'0') -- Update for updating balance also by customer or supplier
AND nvl(t.THIRD_PARTY_ID ,'0') =nvl(u.THIRD_PARTY_ID ,'0')
/*AND nvl(t.THIRD_PARTY_NUMBER,'0') =nvl(u.THIRD_PARTY_NUMBER,'0')
AND nvl(t.PROJECT_ID ,'0') =nvl(u.PROJECT_ID ,'0')
AND nvl(t.PROJECT_NUMBER ,'0') =nvl(u.PROJECT_NUMBER ,'0')
AND nvl(t.PERSONNEL_ID ,'0') =nvl(u.PERSONNEL_ID ,'0')
AND nvl(t.PERSONNEL_NUMBER ,'0') =nvl(u.PERSONNEL_NUMBER ,'0')*/
-- CNAO V2
AND nvl(t.SEGMENT1, '0') = nvl(u.SEGMENT1,'0')
AND nvl(t.SEGMENT2, '0') = nvl(u.SEGMENT2,'0')
AND nvl(t.SEGMENT3, '0') = nvl(u.SEGMENT3,'0')
AND nvl(t.SEGMENT4, '0') = nvl(u.SEGMENT4,'0')
AND nvl(t.SEGMENT5, '0') = nvl(u.SEGMENT5,'0')
AND nvl(t.SEGMENT6, '0') = nvl(u.SEGMENT6,'0')
AND nvl(t.SEGMENT7, '0') = nvl(u.SEGMENT7,'0')
AND nvl(t.SEGMENT8, '0') = nvl(u.SEGMENT8,'0')
AND nvl(t.SEGMENT9, '0') = nvl(u.SEGMENT9,'0')
AND nvl(t.SEGMENT10, '0') = nvl(u.SEGMENT10,'0')
AND nvl(t.SEGMENT11, '0') = nvl(u.SEGMENT11,'0')
AND nvl(t.SEGMENT12, '0') = nvl(u.SEGMENT12,'0')
AND nvl(t.SEGMENT13, '0') = nvl(u.SEGMENT13,'0')
AND nvl(t.SEGMENT14, '0') = nvl(u.SEGMENT14,'0')
AND nvl(t.SEGMENT15, '0') = nvl(u.SEGMENT15,'0')
AND nvl(t.SEGMENT16, '0') = nvl(u.SEGMENT16,'0')
AND nvl(t.SEGMENT17, '0') = nvl(u.SEGMENT17,'0')
AND nvl(t.SEGMENT18, '0') = nvl(u.SEGMENT18,'0')
AND nvl(t.SEGMENT19, '0') = nvl(u.SEGMENT19,'0')
AND nvl(t.SEGMENT20, '0') = nvl(u.SEGMENT20,'0')
AND nvl(t.SEGMENT21, '0') = nvl(u.SEGMENT21,'0')
AND nvl(t.SEGMENT22, '0') = nvl(u.SEGMENT22,'0')
AND nvl(t.SEGMENT23, '0') = nvl(u.SEGMENT23,'0')
AND nvl(t.SEGMENT24, '0') = nvl(u.SEGMENT24,'0')
AND nvl(t.SEGMENT25, '0') = nvl(u.SEGMENT25,'0')
AND nvl(t.SEGMENT26, '0') = nvl(u.SEGMENT26,'0')
AND nvl(t.SEGMENT27, '0') = nvl(u.SEGMENT27,'0')
AND nvl(t.SEGMENT28, '0') = nvl(u.SEGMENT28,'0')
AND nvl(t.SEGMENT29, '0') = nvl(u.SEGMENT29,'0')
AND nvl(t.SEGMENT30, '0') = nvl(u.SEGMENT30,'0')
);
INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER
, THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
, THIRD_PARTY_ID
/*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*/
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
)
SELECT ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER
, THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
, THIRD_PARTY_ID
/*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*/
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
FROM ja_cn_account_balances_post_gt;
/* -- delete the old parent account balance
DELETE ja_cn_account_balances jcb
WHERE jcb.ledger_id = p_ledger_id
AND jcb.legal_entity_id = p_legal_entity_ID
AND EXISTS (SELECT jcp.ledger_id
FROM ja_cn_periods jcp
, fnd_flex_values ffv
WHERE jcp.period_name=jcb.period_name
AND jcp.ledger_id = p_ledger_id
AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
AND ffv.flex_value_set_id = l_flex_value_set_id
AND ffv.flex_value = jcb.account_segment
AND ffv.summary_flag = 'Y'
);
INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
, COST_CENTER
, THIRD_PARTY_TYPE
, THIRD_PARTY_ID
, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN )
SELECT p_ledger_id
, p_legal_entity_ID
, jcc.company_segment
, gb.period_name
, l_ret_currence_code
, jcc.cost_segment
, NULL
, NULL
, NULL
, NULL
, NULL
, 'COA'
, jcc.account_segment
, NULL
, NULL
, SUM(nvl(gb.begin_balance_dr_beq,0))
, SUM(nvl(gb.begin_balance_cr_beq,0))
, SUM(nvl(gb.begin_balance_dr,0))
, SUM(nvl(gb.begin_balance_cr,0))
, SUM(nvl(gb.period_net_dr_beq,0))
, SUM(nvl(gb.period_net_cr_beq,0))
, SUM(nvl(gb.period_net_dr,0))
, SUM(nvl(gb.period_net_cr,0))
, gb.period_num
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
FROM gl_balances gb
, ja_cn_code_combination_v jcc
, fnd_flex_values ffv
WHERE gb.ledger_id = p_ledger_id
AND jcc.ledger_id = p_ledger_id
AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
AND jcc.account_segment = ffv.flex_value
AND ffv.flex_value_set_id = l_flex_value_set_id
AND ffv.summary_flag = 'Y'
AND gb.actual_flag = 'Y'
AND gb.currency_code = l_ret_currence_code
AND gb.period_year*1000+gb.period_num BETWEEN l_period_num AND l_last_period_num
GROUP BY jcc.company_segment
, gb.period_name
, l_ret_currence_code
, jcc.cost_segment
, jcc.account_segment
, gb.period_num
, ffv.COMPILED_VALUE_ATTRIBUTES;*/
DELETE ja_cn_account_balances jcb
WHERE jcb.ledger_id = p_ledger_id
AND jcb.legal_entity_id = p_legal_entity_ID
AND EXISTS (SELECT jcp.ledger_id
FROM ja_cn_periods jcp
, fnd_flex_values ffv
WHERE jcp.period_name=jcb.period_name
AND jcp.ledger_id = p_ledger_id
AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
AND ffv.flex_value_set_id = l_flex_value_set_id
AND ffv.flex_value = jcb.account_segment
AND ffv.summary_flag = 'Y'
);
DELETE FROM JA_CN_ACCOUNT_CHILDREN_GT;
INSERT INTO JA_CN_ACCOUNT_CHILDREN_GT(
parent_account
, child_account
, summary_flag
, PARENT_TYPE
)
SELECT H.PARENT_FLEX_VALUE
, V.FLEX_VALUE
, V.SUMMARY_FLAG
, substr(v2.compiled_value_attributes,5,1)
FROM FND_FLEX_VALUES V
, FND_FLEX_VALUE_NORM_HIERARCHY H
, FND_FLEX_VALUES V2
WHERE v.flex_value_set_id = l_flex_value_set_id
AND h.flex_value_set_id = l_flex_value_set_id
AND V.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
AND ((V.SUMMARY_FLAG = 'Y' AND H.RANGE_ATTRIBUTE = 'P') OR
(V.SUMMARY_FLAG = 'N' AND H.RANGE_ATTRIBUTE = 'C'))
AND v2.flex_value_set_id = l_flex_value_set_id
AND h.PARENT_FLEX_VALUE = v2.FLEX_VALUE;
INSERT INTO JA_CN_ACCOUNT_CHILDREN_GT(
parent_account
, child_account
, summary_flag
, PARENT_TYPE
)
SELECT DISTINCT jca.parent_account
, v.FLEX_VALUE
, v.SUMMARY_FLAG
, jca.parent_type
FROM JA_CN_ACCOUNT_CHILDREN_GT jca
, FND_FLEX_VALUES v
, FND_FLEX_VALUE_NORM_HIERARCHY h
WHERE jca.child_account = h.parent_flex_value
AND v.flex_value_set_id = l_flex_value_set_id
AND h.flex_value_set_id = l_flex_value_set_id
AND V.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
AND ((V.SUMMARY_FLAG = 'Y' AND H.RANGE_ATTRIBUTE = 'P') OR
(V.SUMMARY_FLAG = 'N' AND H.RANGE_ATTRIBUTE = 'C'))
AND NOT EXISTS(SELECT *
FROM JA_CN_ACCOUNT_CHILDREN_GT t
WHERE t.parent_account = jca.parent_account
AND t.child_account = v.FLEX_VALUE);
DELETE JA_CN_ACCOUNT_CHILDREN_GT WHERE summary_flag = 'Y';
INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
--, COST_CENTER
, THIRD_PARTY_TYPE-- Update for updating balance also by customer or supplier
, THIRD_PARTY_ID
/*, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE*/
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30)
SELECT p_ledger_id
, p_legal_entity_ID
, jcb.company_segment
, jcb.period_name
--Added for fixing bug 9582957 by Chaoqun on 27-May-2010
--Calculate balance based on currency to support foreign currency
--, l_ret_currence_code
, jcb.currency_code
--, NULL
, NULL-- Update for updating balance also by customer or supplier
, NULL
/*, NULL
, NULL
, NULL
, 'COA'*/
, jcc.parent_account
, NULL
, NULL
, SUM(nvl(FUNC_BEGIN_BALANCE_DR,0))
, SUM(nvl(FUNC_BEGIN_BALANCE_CR,0))
, SUM(nvl(FUNC_BEGIN_BALANCE_DR,0))
, SUM(nvl(FUNC_BEGIN_BALANCE_CR,0))
, SUM(nvl(FUNC_PERIOD_NET_DR,0))
, SUM(nvl(FUNC_PERIOD_NET_CR,0))
, SUM(nvl(FUNC_PERIOD_NET_DR,0))
, SUM(nvl(FUNC_PERIOD_NET_CR,0))
, jcb.period_mon
, jcc.parent_type
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM ja_cn_account_balances jcb
, JA_CN_ACCOUNT_CHILDREN_GT jcc
, ja_cn_periods jcp
WHERE jcc.child_account = jcb.account_segment
AND jcp.ledger_id = p_ledger_id
AND jcb.legal_entity_id = p_legal_entity_ID
AND jcb.ledger_id = p_ledger_id
AND jcb.period_name = jcp.period_name
AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
GROUP BY jcb.company_segment
, jcb.period_name
, jcc.parent_account
, jcb.period_mon
, jcc.parent_type
--Added for fixing bug 9582957 by Chaoqun on 27-May-2010
--Calculate balance based on currency to support foreign currency
, jcb.currency_code;
DELETE ja_cn_account_balances_post_gt;*/
/* INSERT INTO ja_cn_account_balances(
ledger_id
, LEGAL_ENTITY_ID
, COMPANY_SEGMENT
, PERIOD_NAME
, CURRENCY_CODE
, COST_CENTER
, THIRD_PARTY_TYPE
, THIRD_PARTY_ID
, THIRD_PARTY_NUMBER
, PROJECT_ID
, PROJECT_NUMBER
, PROJECT_SOURCE
, ACCOUNT_SEGMENT
, PERSONNEL_ID
, PERSONNEL_NUMBER
, FUNC_BEGIN_BALANCE_DR
, FUNC_BEGIN_BALANCE_CR
, ORIG_BEGIN_BALANCE_DR
, ORIG_BEGIN_BALANCE_CR
, FUNC_PERIOD_NET_DR
, FUNC_PERIOD_NET_CR
, ORIG_PERIOD_NET_DR
, ORIG_PERIOD_NET_CR
, PERIOD_MON
, ACCOUNT_TYPE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN )
SELECT p_ledger_id
, p_legal_entity_ID
, jcc.company_segment
, gb.period_name
, l_ret_currence_code
, jcc.cost_segment
, NULL
, NULL
, NULL
, NULL
, NULL
, 'COA'
, jcc.account_segment
, NULL
, NULL
, SUM(nvl(gb.begin_balance_dr_beq,0))
, SUM(nvl(gb.begin_balance_cr_beq,0))
, SUM(nvl(gb.begin_balance_dr,0))
, SUM(nvl(gb.begin_balance_cr,0))
, SUM(nvl(gb.period_net_dr_beq,0))
, SUM(nvl(gb.period_net_cr_beq,0))
, SUM(nvl(gb.period_net_dr,0))
, SUM(nvl(gb.period_net_cr,0))
, gb.period_num
, substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.LOGIN_ID
FROM ja_cn_periods jcp
, ja_cn_account_balances jca
, FND_FLEX_VALUE_CHILDREN_V ffvc
, fnd_flex_values ffv
WHERE jca.account_segment = ffvc.flex_value*/
IF( G_PROC_LEVEL >= g_debug_devel )
THEN
FND_LOG.STRING(G_PROC_LEVEL
,G_MODULE_PREFIX||l_procedure_name||'.end'
,'End procedure');
END update_retained_parent_account;
SELECT sam.SUBSIDIARY_SEGMENT_CODE
FROM JA_CN_SUB_ACC_MAPPING sam
,GL_LEDGER_LE_V gllv
WHERE sam.CHART_OF_ACCOUNTS_ID = gllv.CHART_OF_ACCOUNTS_ID
AND gllv.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
AND gllv.LEGAL_ENTITY_ID = pn_legal_entity_id; --parameter: pn_legal_entity_id;
SELECT DISTINCT gp.period_name,gp.period_year,gp.period_num
FROM /*JA_CN_JOURNAL_LINES jjl
, */gl_periods gp
, gl_ledgers led
WHERE /*jjl.ledger_id = p_ledger_id
AND jjl.legal_entity_id = p_legal_entity_ID
AND jjl.status = 'U'
AND */led.ledger_id = p_ledger_id
--AND jjl.period_name = gp.period_name
AND gp.period_set_name = led.period_set_name
AND gp.period_type = led.accounted_period_type
AND (gp.period_year
SELECT --jjl.journal_number
SUM(nvl(jjl.ENTERED_DR,0)) entered_dr
, SUM(nvl(jjl.ENTERED_CR,0)) entered_cr
, SUM(nvl(jjl.ACCOUNTED_DR,0)) accounted_dr
, SUM(nvl(jjl.ACCOUNTED_CR,0)) accounted_cr
, jjl.CURRENCY_CODE
, jjl.CURRENCY_CONVERSION_RATE
, jjl.COMPANY_SEGMENT
, jjl.CODE_COMBINATION_ID
--, jjl.COST_CENTER
, jjl.THIRD_PARTY_ID --Updated for posting journals by customer or supplier
-- , jjl.THIRD_PARTY_NUMBER
, jjl.third_party_type
/*, jjl.PERSONNEL_ID
, jjl.PERSONNEL_NUMBER
, jjl.PROJECT_ID
, jjl.PROJECT_NUMBER
, jjl.project_source*/
, substr(COMPILED_VALUE_ATTRIBUTES,5,1) account_type
, jjl.segment1
, jjl.segment2
, jjl.segment3
, jjl.segment4
, jjl.segment5
, jjl.segment6
, jjl.segment7
, jjl.segment8
, jjl.segment9
, jjl.segment10
, jjl.segment11
, jjl.segment12
, jjl.segment13
, jjl.segment14
, jjl.segment15
, jjl.segment16
, jjl.segment17
, jjl.segment18
, jjl.segment19
, jjl.segment20
, jjl.segment21
, jjl.segment22
, jjl.segment23
, jjl.segment24
, jjl.segment25
, jjl.segment26
, jjl.segment27
, jjl.segment28
, jjl.segment29
, jjl.segment30
, jjl.ACCOUNT_SEGMENT
, jjl.period_name
FROM JA_CN_JOURNAL_LINES jjl
, FND_FLEX_VALUES ffv
WHERE jjl.ledger_id = p_ledger_id
AND jjl.legal_entity_id = p_legal_entity_ID
AND jjl.status = 'U'
AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
AND ffv.FLEX_VALUE_SET_ID=pc_flex_value_set_id
AND ffv.flex_value =jjl.account_segment
AND jjl.period_name =pc_period_name
GROUP BY --jjl.journal_number
jjl.CURRENCY_CODE
, jjl.CURRENCY_CONVERSION_RATE
, jjl.COMPANY_SEGMENT
, jjl.CODE_COMBINATION_ID
-- , jjl.COST_CENTER
, jjl.THIRD_PARTY_ID --Updated for posting journals by customer or supplier
--, jjl.THIRD_PARTY_NUMBER
, jjl.third_party_type
/*, jjl.PERSONNEL_ID
, jjl.PERSONNEL_NUMBER
, jjl.PROJECT_ID
, jjl.PROJECT_NUMBER
, jjl.project_source*/
, COMPILED_VALUE_ATTRIBUTES
, jjl.segment1
, jjl.segment2
, jjl.segment3
, jjl.segment4
, jjl.segment5
, jjl.segment6
, jjl.segment7
, jjl.segment8
, jjl.segment9
, jjl.segment10
, jjl.segment11
, jjl.segment12
, jjl.segment13
, jjl.segment14
, jjl.segment15
, jjl.segment16
, jjl.segment17
, jjl.segment18
, jjl.segment19
, jjl.segment20
, jjl.segment21
, jjl.segment22
, jjl.segment23
, jjl.segment24
, jjl.segment25
, jjl.segment26
, jjl.segment27
, jjl.segment28
, jjl.segment29
, jjl.segment30
, jjl.ACCOUNT_SEGMENT
, jjl.period_name
;
SELECT period_name
, (period_num - period_year*1000) period_month
FROM ja_cn_periods
WHERE period_num>pc_period_num
AND ledger_id=p_ledger_id
AND status='O';
SELECT period_name
, (period_num - period_year*1000) period_month
FROM ja_cn_periods
WHERE period_num>pc_period_num
AND status='O'
AND period_year=pc_period_year
AND ledger_id=p_ledger_id;
/* Create_Dynamic_Index(pn_ledger_id => p_ledger_id --Delete for fixing bug# 9662105
,pn_legal_entity_id => p_legal_entity_ID
);*/
SELECT gp.period_year
, gp.period_num
INTO l_period_year
, l_period_num
FROM gl_periods gp
, gl_ledgers led
WHERE gp.period_name=p_period_name
AND gp.period_set_name=led.period_set_name
AND led.ledger_id=p_ledger_id;
SELECT ifs.flex_value_set_id
INTO l_flex_value_set_id
FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
, gl_ledgers led
, Fnd_Id_Flex_Segments ifs
WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND sav.ID_FLEX_CODE = 'GL#'
AND sav.APPLICATION_ID = 101
AND sav.attribute_value = 'Y'
AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
AND ifs.application_id = 101
AND ifs.Id_Flex_Code = 'GL#'
AND ifs.id_flex_num = led.chart_of_accounts_id
AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
AND led.ledger_id = p_ledger_id;
SELECT COUNT(*)
INTO l_row_count
FROM ja_cn_periods
WHERE period_name=rec_period.period_name
AND ledger_id=p_ledger_id;
l_balance_rec.THIRD_PARTY_ID := rec_line.third_party_id ; --Updated for posting journals by customer or supplier
update_balance( p_balance_rec => l_balance_rec
, p_current_period_flag => 'Y'
);
update_balance( p_balance_rec => l_balance_rec
, p_current_period_flag => 'N'
);
update_balance( p_balance_rec => l_balance_rec
, p_current_period_flag => 'Y'
);
update_balance( p_balance_rec => l_balance_rec
, p_current_period_flag => 'N'
);
UPDATE ja_cn_journal_lines jl
SET jl.status='P'
WHERE jl.ledger_id = p_ledger_id
AND jl.LEGAL_ENTITY_ID = p_legal_entity_ID
AND jl.PERIOD_NAME = rec_period.period_name
AND jl.status = 'U';
update_retained_parent_account
( p_period_name => l_earliest_changed_period
, p_ledger_id => p_ledger_id
, p_legal_entity_ID => p_legal_entity_ID
);