[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
lv_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,
account_type,
is_foreign,
balance_side,
account_level,
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 ' ||
prefix_a ||
'.account_segment, ' ||
prefix_a || '.currency_code, ' ||
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,
' ||
prefix_a ||
'.account_type,s.is_foreign,s.balance_side,s.account_level,
' ||
'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'
|| '
FROM (SELECT account_segment,
currency_code,
SUM(func_begin_balance) func_begin_balance,
SUM(orig_begin_balance) orig_begin_balance,
SUM(func_period_net_dr) func_period_net_dr,
SUM(orig_period_net_dr) orig_period_net_dr,
SUM(func_period_net_cr) func_period_net_cr,
SUM(orig_period_net_cr) orig_period_net_cr,
SUM(func_end_balance) func_end_balance,
SUM(orig_end_balance) orig_end_balance,
period_mon,
account_type,
PERIOD_NAME,
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
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_code,period_mon,account_type,PERIOD_NAME,
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) ' ||
prefix_a ||
' LEFT JOIN ja_cn_subsidiary_gt s ON ' ||
prefix_a ||
'.account_segment =s.account_segment_value)';
SELECT *
FROM ja_cn_dff_assignments
WHERE chart_of_accounts_id = pn_coa_id
AND (dff_title_code = 'ACBS' -- Balance Side
OR dff_title_code = 'ACLE' -- Account Level
);
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
WHERE legal_entity_id = pn_legal_entity_id
AND chart_of_accounts_id = pn_coa_id)
AND period_name IN
(SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN ld_start_date AND ld_end_date) AND
(end_date BETWEEN ld_start_date AND ld_end_date)))
AND ledger_id = pn_ledger_id;
SELECT * FROM ja_cn_subsidiary_gt;
SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN ld_start_date AND ld_end_date) AND
(end_date BETWEEN ld_start_date AND ld_end_date))
ORDER BY start_date;
SELECT *
FROM ja_cn_account_balances_gt
ORDER BY start_date,ACCOUNT_SEGMENT;
DELETE FROM JA_CN_LEDGER_LE_BSV_GT;
SELECT start_date
INTO ld_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT end_date
INTO ld_end_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT MEANING
INTO lv_debit
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
AND LOOKUP_CODE = 'D'
AND LANGUAGE = USERENV('LANG');
SELECT MEANING
INTO lv_credit
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
AND LOOKUP_CODE = 'C'
AND LANGUAGE = USERENV('LANG');
SELECT s.flex_value_set_id
INTO ln_flex_value_set_id
FROM fnd_id_flex_segments s
WHERE s.application_id = 101
AND s.id_flex_num = pn_coa_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 = pn_coa_id
AND id_flex_code = 'GL#');
DELETE FROM JA_CN_SUBSIDIARY_GT;
lv_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT
(
ACCOUNT_SEGMENT_VALUE,
BALANCE_SIDE,
account_level
)
select flex_value,' ||
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
WHERE ledger_id = pn_ledger_id);
SELECT currency_code
INTO l_functional_currency_code
FROM gl_ledgers
WHERE ledger_id = pn_ledger_id;
SELECT start_date
INTO ld_current_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = l_current_period;
lv_sql_stmt := lv_sql_stmt4insert_j_line;