[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
lv_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,
SETTLEMENT_METHOD_NUMBER,
CURRENCY_CONVERSION_TYPE,
PERIOD_NUM,
JOURNAL_SOURCE,
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 ||
'.journal_number,' ||
prefix_a ||
'.je_category,' ||
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_a || '.currency_code,' ||
'NVL(ROUND(' ||
prefix_a ||
'.currency_conversion_rate,4),1),' ||
prefix_a ||
'.JOURNAL_CREATOR,' ||
prefix_a ||
'.JOURNAL_APPROVER,' ||
prefix_a ||
'.JOURNAL_POSTER,' ||
prefix_a ||
'.PERIOD_NAME, :1,' ||
prefix_a ||
'.SETTLEMENT_METHOD_NUMBER,' ||
prefix_a ||
'.CURRENCY_CONVERSION_TYPE, :2,' || --Updated by Chaoqun for fixing bug 9872212
'jh.je_source,' ||
'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'||
--Comment by Jianchao Chi for new solution to change the logic
--of Legal Entity and Voucher Number, 05-JAN-2011
/*FROM (SELECT je_header_id,
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,
SETTLEMENT_METHOD_NUMBER,
CURRENCY_CONVERSION_TYPE,
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_journal_lines
WHERE period_name = :3
AND account_segment = :4
@AND_CONDITION
AND ledger_id = :5
AND company_segment IN
(SELECT bal_seg_value
FROM ja_cn_ledger_le_bsv_gt
WHERE legal_entity_id = :6)
GROUP BY je_header_id,
voucher_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,
SETTLEMENT_METHOD_NUMBER,
CURRENCY_CONVERSION_TYPE,*/
--Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
--Add ja_cn_voucher_number table, voucher number and legal entity
--are fetched from ja_cn_voucher_number
' FROM (SELECT jcjl.je_header_id,
jcjl.voucher_number journal_number,
jcjl.je_category,
jcjl.description,
jcjl.default_effective_date,
jcjl.je_line_num,
jcjl.account_segment,
SUM(jcjl.accounted_dr) accounted_dr,
SUM(jcjl.accounted_cr) accounted_cr,
SUM(jcjl.entered_dr) entered_dr,
SUM(jcjl.entered_cr) entered_cr,
jcjl.currency_code,
jcjl.currency_conversion_rate,
jcjl.JOURNAL_CREATOR,
jcjl.JOURNAL_APPROVER,
jcjl.JOURNAL_POSTER,
jcjl.PERIOD_NAME,
jcjl.SETTLEMENT_METHOD_NUMBER,
jcjl.CURRENCY_CONVERSION_TYPE,
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 DISTINCT jc.*, jcvn.voucher_number
FROM ja_cn_voucher_number jcvn, ja_cn_journal_lines jc
WHERE jcvn.je_header_id = jc.je_header_id
AND jcvn.je_line_number = jc.je_line_num) jcjl
WHERE jcjl.period_name = :3
AND jcjl.account_segment = :4
@AND_CONDITION
AND jcjl.ledger_id = :5
AND jcjl.company_segment IN
(SELECT bal_seg_value
FROM ja_cn_ledger_le_bsv_gt
WHERE legal_entity_id = :6)
GROUP BY jcjl.je_header_id,
jcjl.voucher_number,
jcjl.account_segment,
jcjl.je_category,
jcjl.description,
jcjl.default_effective_date,
jcjl.je_line_num,
jcjl.currency_code,
jcjl.currency_conversion_rate,
jcjl.JOURNAL_CREATOR,
jcjl.JOURNAL_APPROVER,
jcjl.JOURNAL_POSTER,
jcjl.period_name,
jcjl.SETTLEMENT_METHOD_NUMBER,
jcjl.CURRENCY_CONVERSION_TYPE,
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' ||
' LEFT JOIN GL_JE_HEADERS jh ON ' ||
prefix_a ||
'.je_header_id=jh.je_header_id ';
SELECT DISTINCT account_segment
FROM ja_cn_journal_lines
WHERE company_segment IN
(SELECT bal_seg_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_journal_lines_gt
ORDER BY start_date, to_number(journal_number), to_number(je_line_num);
FND_FILE.put_line(FND_FILE.log, lv_sql_stmt4insert_j_line);
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 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--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 = pn_coa_id
AND id_flex_code = 'GL#');--l_chart_of_accounts_id);
DELETE FROM JA_CN_SUBSIDIARY_GT;
lv_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT (ACCOUNT_SEGMENT_VALUE) select flex_value' ||
' from FND_FLEX_VALUES where flex_value_set_id=:1 and flex_value=:2';
SELECT start_date,period_num
INTO ld_current_start_date, ln_current_period_num
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;
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 user_je_category_name into lv_je_category from GL_JE_CATEGORIES where JE_CATEGORY_NAME=v_row.je_category;