[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt4insert_j_line VARCHAR2(30000) := 'INSERT INTO ja_cn_account_balances_gt
(account_segment,
currency_code,
func_begin_balance,
orig_begin_balance,
func_period_net_dr,
func_period_net_cr,
orig_period_net_dr,
orig_period_net_cr,
func_end_balance,
orig_end_balance,
period_mon,
PERIOD_NAME,
START_DATE,
has_third_party,
has_cost_center,
has_personnel,
has_project,
account_type,
is_foreign,
balance_side,
account_level
@COLUMN_CLAUSE
)
(SELECT ' ||
prefix_a ||
'.account_segment, ' ||
prefix_b || '.name, ' ||
prefix_a ||
'.func_begin_balance,
' ||
prefix_a ||
'.orig_begin_balance,
' ||
prefix_a ||
'.func_period_net_dr,
' ||
prefix_a ||
'.func_period_net_cr,
' ||
prefix_a ||
'.orig_period_net_dr,
' ||
prefix_a ||
'.orig_period_net_cr,
' ||
prefix_a ||
'.func_end_balance,
' ||
prefix_a ||
'.orig_end_balance,
' ||
prefix_a ||
'.period_mon,
' ||
prefix_a ||
'.PERIOD_NAME,:1,
s.has_third_party,
s.has_cost_center,
s.has_personnel,
s.has_project,
' ||
prefix_a ||
'.account_type,s.is_foreign,s.balance_side,s.account_level
@PREFIX_COLUMN_CLAUSE ' || '
FROM (SELECT account_segment,
@CURRENCY_CLAUSE1
@SUM_CLAUSE
period_mon,
account_type,
PERIOD_NAME
@COLUMN_CLAUSE
FROM ja_cn_account_balances_v
WHERE period_name = :2
AND account_segment = :3
AND ledger_id = :4
AND company_segment IN
(SELECT bal_seg_value
FROM ja_cn_ledger_le_bsv_gt
WHERE legal_entity_id = :5)
GROUP BY account_segment,@CURRENCY_CLAUSE2 period_mon,account_type,PERIOD_NAME @COLUMN_CLAUSE) ' ||
prefix_a ||
' LEFT JOIN ja_cn_subsidiary_gt s ON ' ||
prefix_a ||
'.account_segment =s.account_segment_value left join fnd_currencies_vl ' ||
prefix_b || ' ON ' || prefix_a ||
'.currency_code=' || prefix_b ||
'.currency_code)';
SELECT *
FROM ja_cn_dff_assignments
WHERE chart_of_accounts_id = p_coa_id
AND (dff_title_code = 'SACC'
OR dff_title_code = 'SATP'
OR dff_title_code = 'SAEE'
OR dff_title_code = 'SAPA'
OR dff_title_code = 'FCRA'
OR dff_title_code = 'ACBS'
OR dff_title_code = 'ACLE');
SELECT DISTINCT account_segment
FROM ja_cn_account_balances_v
WHERE company_segment IN
(SELECT bal_seg_value--segment_value
FROM ja_cn_ledger_le_bsv_gt--ja_cn_legal_companies_all
WHERE legal_entity_id = p_legal_entity
AND chart_of_accounts_id = p_coa_id)
AND period_name IN
(SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
AND application_id = 101
AND ((start_date BETWEEN l_start_date AND l_end_date) AND
(end_date BETWEEN l_start_date AND l_end_date)))
AND ledger_id = p_ledger_id;--set_of_books_id = l_set_of_books_id;
SELECT * FROM ja_cn_subsidiary_gt;
SELECT *
FROM ja_cn_account_balances_gt
ORDER BY start_date
,ACCOUNT_SEGMENT
,PROJECT_NUMBER
,THIRD_PARTY_NUMBER
,COST_CENTER
,PERSONNEL_NUMBER;
SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
AND application_id = 101
AND ((start_date BETWEEN l_start_date AND l_end_date) AND
(end_date BETWEEN l_start_date AND l_end_date))
ORDER BY start_date;
SELECT start_date
INTO l_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
AND application_id = 101
AND period_name = p_start_period;
SELECT end_date
INTO l_end_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
AND application_id = 101
AND period_name = p_end_period;
SELECT s.flex_value_set_id
INTO l_flex_value_set_id
FROM fnd_id_flex_segments s
WHERE s.application_id = 101
AND s.id_flex_num = p_coa_id--l_chart_of_accounts_id
AND s.id_flex_code = 'GL#'
AND s.application_column_name =
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND segment_attribute_type = 'GL_ACCOUNT'
AND attribute_value = 'Y'
AND id_flex_num = p_coa_id
AND id_flex_code = 'GL#');--l_chart_of_accounts_id);
l_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT
(
ACCOUNT_SEGMENT_VALUE,
HAS_THIRD_PARTY,
HAS_COST_CENTER,
HAS_PERSONNEL,
HAS_PROJECT,
IS_FOREIGN,
BALANCE_SIDE,
account_level
)
select flex_value,' ||
nvl(to_char(l_attribute_column4third_party), 'null') || ',' ||
nvl(to_char(l_attribute_column4cost_center), 'null') || ',' ||
nvl(to_char(l_attribute_column4personnel), 'null') || ',' ||
nvl(to_char(l_attribute_column4project), 'null') || ',' ||
nvl(to_char(l_attribute_column4is_foreign), 'null') || ',' ||
nvl(to_char(l_attribute_column4balanceside), 'null') || ',' ||
nvl(to_char(l_attribute_column4account_lev), 'null') || '
from FND_FLEX_VALUES
where
flex_value_set_id=:1 and
flex_value=:2';
SELECT fnd_currencies_vl.NAME
INTO l_functional_currency
FROM fnd_currencies_vl
WHERE currency_code =
(SELECT currency_code
FROM gl_ledgers--gl_sets_of_books
WHERE ledger_id = p_ledger_id);--set_of_books_id = l_set_of_books_id);
SELECT currency_code
INTO l_functional_currency_code
FROM gl_ledgers--gl_sets_of_books
WHERE ledger_id = p_ledger_id;--set_of_books_id = l_set_of_books_id;
SELECT start_date
INTO l_current_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
AND application_id = 101
AND period_name = l_current_period;
l_sql_stmt := REPLACE(l_sql_stmt4insert_j_line,
'@COLUMN_CLAUSE',
l_column_clause);
SELECT COUNT(*)
INTO l_number_a
FROM ja_cn_account_balances_gt
WHERE account_segment = l_account_balances_gbl_tmp_row.account_segment
AND nvl(COST_CENTER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.COST_CENTER, 'NULL')
AND nvl(THIRD_PARTY_NUMBER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.THIRD_PARTY_NUMBER, 'NULL')
AND nvl(PERSONNEL_NUMBER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.PERSONNEL_NUMBER, 'NULL')
AND nvl(PROJECT_NUMBER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.PROJECT_NUMBER, 'NULL')
AND currency_code <>
(SELECT fnd_currencies_vl.NAME
FROM fnd_currencies_vl
WHERE currency_code = l_account_balances_gbl_tmp_row.is_foreign)
AND FUNC_BEGIN_BALANCE <> 0
AND FUNC_END_BALANCE <> 0;
INSERT INTO JA_CN_ERROR_ACCOUNTS
(request_id
,ACCOUNT_SEGMENT
,CURRENCY_CODE
,FUNC_BEGIN_BALANCE
,ORIG_BEGIN_BALANCE
,FUNC_PERIOD_NET_DR
,FUNC_PERIOD_NET_CR
,ORIG_PERIOD_NET_DR
,ORIG_PERIOD_NET_CR
,FUNC_END_BALANCE
,ORIG_END_BALANCE
,PERIOD_MON
,COST_CENTER
,THIRD_PARTY_NUMBER
,PERSONNEL_NUMBER
,PROJECT_NUMBER
,HAS_THIRD_PARTY
,HAS_COST_CENTER
,HAS_PERSONNEL
,HAS_PROJECT
,ACCOUNT_TYPE
,IS_FOREIGN
,BALANCE_SIDE
,ACCOUNT_LEVEL
,PERIOD_NAME
,START_DATE)
SELECT l_na_curr_req_id
,l_account_balances_gbl_tmp_row.account_segment
,CURRENCY_CODE
,FUNC_BEGIN_BALANCE
,ORIG_BEGIN_BALANCE
,FUNC_PERIOD_NET_DR
,FUNC_PERIOD_NET_CR
,ORIG_PERIOD_NET_DR
,ORIG_PERIOD_NET_CR
,FUNC_END_BALANCE
,ORIG_END_BALANCE
,PERIOD_MON
,COST_CENTER
,THIRD_PARTY_NUMBER
,PERSONNEL_NUMBER
,PROJECT_NUMBER
,HAS_THIRD_PARTY
,HAS_COST_CENTER
,HAS_PERSONNEL
,HAS_PROJECT
,ACCOUNT_TYPE
,IS_FOREIGN
,BALANCE_SIDE
,ACCOUNT_LEVEL
,period_name
,start_date
FROM ja_cn_account_balances_gt
WHERE account_segment =
l_account_balances_gbl_tmp_row.account_segment
AND nvl(COST_CENTER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.COST_CENTER, 'NULL')
AND nvl(THIRD_PARTY_NUMBER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.THIRD_PARTY_NUMBER,
'NULL')
AND nvl(PERSONNEL_NUMBER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.PERSONNEL_NUMBER, 'NULL')
AND nvl(PROJECT_NUMBER, 'NULL') =
nvl(l_account_balances_gbl_tmp_row.PROJECT_NUMBER, 'NULL')
AND currency_code <>
(SELECT fnd_currencies_vl.NAME
FROM fnd_currencies_vl
WHERE currency_code =
l_account_balances_gbl_tmp_row.is_foreign)
AND FUNC_BEGIN_BALANCE <> 0
AND FUNC_END_BALANCE <> 0;
INSERT INTO JA_CN_ERROR_ACCOUNTS
(request_id
,ACCOUNT_SEGMENT
,CURRENCY_CODE
,FUNC_BEGIN_BALANCE
,ORIG_BEGIN_BALANCE
,FUNC_PERIOD_NET_DR
,FUNC_PERIOD_NET_CR
,ORIG_PERIOD_NET_DR
,ORIG_PERIOD_NET_CR
,FUNC_END_BALANCE
,ORIG_END_BALANCE
,PERIOD_MON
,COST_CENTER
,THIRD_PARTY_NUMBER
,PERSONNEL_NUMBER
,PROJECT_NUMBER
,HAS_THIRD_PARTY
,HAS_COST_CENTER
,HAS_PERSONNEL
,HAS_PROJECT
,ACCOUNT_TYPE
,IS_FOREIGN
,BALANCE_SIDE
,ACCOUNT_LEVEL
,PERIOD_NAME
,START_DATE)
SELECT l_na_curr_req_id
,l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value
,CURRENCY_CODE
,FUNC_BEGIN_BALANCE
,ORIG_BEGIN_BALANCE
,FUNC_PERIOD_NET_DR
,FUNC_PERIOD_NET_CR
,ORIG_PERIOD_NET_DR
,ORIG_PERIOD_NET_CR
,FUNC_END_BALANCE
,ORIG_END_BALANCE
,PERIOD_MON
,COST_CENTER
,THIRD_PARTY_NUMBER
,PERSONNEL_NUMBER
,PROJECT_NUMBER
,HAS_THIRD_PARTY
,HAS_COST_CENTER
,HAS_PERSONNEL
,HAS_PROJECT
,ACCOUNT_TYPE
,IS_FOREIGN
,BALANCE_SIDE
,ACCOUNT_LEVEL
,period_name
,start_date
FROM ja_cn_account_balances_gt
WHERE account_segment =
l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value
AND currency_code <>
(SELECT fnd_currencies_vl.NAME
FROM fnd_currencies_vl
WHERE currency_code =
l_ja_cn_subsidiary_gbl_tmp_row.is_foreign)
AND FUNC_BEGIN_BALANCE <> 0
AND FUNC_END_BALANCE <> 0;
SELECT COUNT(*)
INTO l_number_b
FROM JA_CN_ERROR_ACCOUNTS
WHERE request_id = l_na_curr_req_id;
DELETE FROM JA_CN_ERROR_ACCOUNTS WHERE REQUEST_ID = l_na_curr_req_id;