[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt4insert_j_line CONSTANT VARCHAR2(30000) := 'INSERT INTO ja_cn_journal_lines_gt
(journal_number,
je_category,
description,
default_effective_date,
je_line_num,
account_segment_value,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr,
currency_code,
currency_conversion_rate,
journal_preparer,
JOURNAL_APPROVER,
JOURNAL_POSTER,
PERIOD_NAME,
START_DATE,
has_third_party,
has_cost_center,
has_personnel,
has_project
@COLUMN_CLAUSE)
SELECT ' ||
prefix_a ||
'.journal_number,' ||
prefix_b ||
'.USER_JE_CATEGORY_NAME,' ||
prefix_a ||
'.description,' ||
prefix_a ||
'.default_effective_date,' ||
prefix_a ||
'.je_line_num,' ||
prefix_a ||
'.account_segment,' ||
prefix_a ||
'.accounted_dr,' ||
prefix_a ||
'.accounted_cr,' ||
prefix_a ||
'.entered_dr,' ||
prefix_a ||
'.entered_cr,' ||
prefix_c || '.name,' ||
prefix_a ||
'.currency_conversion_rate,' ||
prefix_a ||
'.JOURNAL_CREATOR,' ||
prefix_a ||
'.JOURNAL_APPROVER,' ||
prefix_a ||
'.JOURNAL_POSTER,' ||
prefix_a ||
'.PERIOD_NAME, :1,
s.has_third_party,
s.has_cost_center,
s.has_personnel,
s.has_project
@PREFIX_COLUMN_CLAUSE
FROM (SELECT journal_number,
je_category,
description,
default_effective_date,
je_line_num,
account_segment,
SUM(accounted_dr) accounted_dr,
SUM(accounted_cr) accounted_cr,
SUM(entered_dr) entered_dr,
SUM(entered_cr) entered_cr,
currency_code,
currency_conversion_rate,
JOURNAL_CREATOR,
JOURNAL_APPROVER,
JOURNAL_POSTER,
PERIOD_NAME
@COLUMN_CLAUSE
FROM ja_cn_journal_lines
WHERE period_name = :2
AND account_segment = :3
@AND_CONDITION
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 journal_number,
account_segment,
je_category,
description,
default_effective_date,
je_line_num,
currency_code,
currency_conversion_rate,
JOURNAL_CREATOR,
JOURNAL_APPROVER,
JOURNAL_POSTER,
period_name
@COLUMN_CLAUSE
) ' ||
prefix_a ||
' left join JA_CN_SUBSIDIARY_GT s on ' ||
prefix_a ||
'.account_segment=s.ACCOUNT_SEGMENT_VALUE' ||
' LEFT JOIN gl_je_categories_vl ' ||
prefix_b || ' ON ' ||
prefix_a ||
'.je_category=' ||
prefix_b ||
'.JE_CATEGORY_NAME' ||
' LEFT JOIN fnd_currencies_vl ' ||
prefix_c || ' ON ' ||
prefix_a ||
'.currency_code=' ||
prefix_c ||
'.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');
SELECT DISTINCT account_segment
FROM ja_cn_journal_lines
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_id
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
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_journal_lines_gt
ORDER BY start_date, to_number(journal_number), to_number(je_line_num);
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) 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') ||
' from FND_FLEX_VALUES where flex_value_set_id=:1 and flex_value=:2';
'about to insert subsidiary info of account: ' ||
l_account_segment || ' to temp table');
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 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);