[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TRANSLATE(i.status,',',' ') AS status,
LTRIM(TO_CHAR(i.set_of_books_id,'999999999999999')) AS set_of_books_id,
TO_CHAR(i.accounting_date,'YYYY/MM/DD') AS accounting_date,
TRANSLATE(i.currency_code,',',' ') AS currency_code,
TO_CHAR(i.date_created,'YYYY/MM/DD') AS date_created,
LTRIM(TO_CHAR(i.created_by,'999999999999999')) AS created_by,
i.actual_flag AS actual_flag,
TRANSLATE(i.user_je_category_name,',',' ') AS user_je_category_name,
TRANSLATE(i.user_je_source_name,',',' ') AS user_je_source_name,
TO_CHAR(i.currency_conversion_date,'YYYY/MM/DD') AS currency_conversion_date,
LTRIM(TO_CHAR(i.encumbrance_type_id,'999999999999999')) AS encumbrance_type_id,
LTRIM(TO_CHAR(i.budget_version_id,'999999999999999')) AS budget_version_id,
TRANSLATE(i.user_currency_conversion_type,',',' ') AS user_currency_conversion_type,
LTRIM(TO_CHAR(i.currency_conversion_rate,'999999999999999999999.999999999999')) AS currency_conversion_rate,
TRANSLATE(i.originating_bal_seg_value,',',' ') AS originating_bal_seg_value,
TRANSLATE(cc.segment1,',',' ') AS segment1,
TRANSLATE(cc.segment2,',',' ') AS segment2,
TRANSLATE(cc.segment3,',',' ') AS segment3,
TRANSLATE(cc.segment4,',',' ') AS segment4,
TRANSLATE(cc.segment5,',',' ') AS segment5,
LTRIM(TO_CHAR(i.entered_dr,'999999999999999999999.999999999999')) AS entered_dr,
LTRIM(TO_CHAR(i.entered_cr,'999999999999999999999.999999999999')) AS entered_cr,
LTRIM(TO_CHAR(i.accounted_dr,'999999999999999999999.999999999999')) AS accounted_dr,
LTRIM(TO_CHAR(i.accounted_cr,'999999999999999999999.999999999999')) AS accounted_cr,
TRANSLATE(i.reference1,',',' ') AS batch_name,
TRANSLATE(i.reference2,',',' ') AS batch_description,
TRANSLATE(i.reference3,',',' ') AS dual_currency_rate,
TRANSLATE(i.reference4,',',' ') AS journal_name,
TRANSLATE(i.reference5,',',' ') AS journal_description,
TRANSLATE(i.reference6,',',' ') AS journal_reference,
TRANSLATE(i.reference7,',',' ') AS journal_reversal_flag,
TRANSLATE(i.reference8,',',' ') AS journal_reversal_period,
TRANSLATE(i.reference9,',',' ') AS journal_reversal_method,
TRANSLATE(i.reference10,',',' ') AS line_description,
TRANSLATE(i.reference21,',',' ') AS line_reference1,
TRANSLATE(i.reference22,',',' ') AS line_reference2,
TRANSLATE(i.reference23,',',' ') AS line_reference3,
TRANSLATE(i.reference24,',',' ') AS line_reference4,
TRANSLATE(i.reference25,',',' ') AS line_reference5,
TRANSLATE(i.reference26,',',' ') AS line_reference6,
TRANSLATE(i.reference27,',',' ') AS line_reference7,
TRANSLATE(i.reference28,',',' ') AS line_reference8,
TRANSLATE(i.reference29,',',' ') AS line_reference9,
TRANSLATE(i.reference30,',',' ') AS line_reference10,
LTRIM(TO_CHAR(i.stat_amount,'999999999999999999999.999999999999')) AS stat_amount,
LTRIM(TO_CHAR(i.group_id,'999999999999999')) AS group_id,
LTRIM(TO_CHAR(i.subledger_doc_sequence_id,'999999999999999')) AS subledger_doc_sequence_id,
LTRIM(TO_CHAR(i.subledger_doc_sequence_value,'999999999999999999999.999999999999')) AS subledger_doc_sequence_value,
TRANSLATE(i.ussgl_transaction_code,',',' ') AS ussgl_transaction_code,
TRANSLATE(i.jgzz_recon_ref,',',' ') AS jgzz_recon_ref,
LTRIM(TO_CHAR(i.gl_sl_link_id,'999999999999999')) AS gl_sl_link_id,
TRANSLATE(i.gl_sl_link_table,',',' ') AS gl_sl_link_table,
TRANSLATE(s.name,',',' ') AS set_of_books_name,
TRANSLATE(f.id_flex_structure_code,',',' ') AS id_flex_structure_code,
LTRIM(TO_CHAR(i.code_combination_id,'999999999999999')) AS code_combination_id,
cc.account_type AS account_type,
cc.enabled_flag AS enabled_flag,
cc.summary_flag AS summary_flag,
TRANSLATE(s.period_set_name,',',' ') AS period_set_name,
TRANSLATE(d.period_name,',',' ') AS period_name,
LTRIM(TO_CHAR(p.period_year,'999999999999999')) AS period_year,
LTRIM(TO_CHAR(p.period_num,'999999999999999')) AS period_num,
LTRIM(TO_CHAR(p.quarter_num,'999999999999999')) AS quarter_num,
LTRIM(TO_CHAR(i.ledger_id,'999999999999999')) AS ledger_id
FROM GL_INTERFACE i,
GL_SETS_OF_BOOKS s,
FND_ID_FLEX_STRUCTURES f,
GL_DATE_PERIOD_MAP d,
GL_PERIODS p,
GL_CODE_COMBINATIONS cc
WHERE f.id_flex_num = s.chart_of_accounts_id
AND f.id_flex_code = 'GL#'
AND f.application_id = 101
AND p.period_name = d.period_name
AND p.period_set_name = d.period_set_name
AND d.accounting_date = trunc(i.accounting_date)
AND d.period_type = s.accounted_period_type
AND d.period_set_name = s.period_set_name
AND s.set_of_books_id = decode(i.ledger_id, -1, i.set_of_books_id, i.ledger_id)
AND cc.code_combination_id (+) = i.code_combination_id
AND i.request_id + 0 = -2;
v_num_line_deleted NUMBER := 0; -- number of lines deleted
DELETE_ERROR EXCEPTION;
SELECT SUBSTRB(TRANSLATE(LTRIM(value),',',' '),
1,
INSTR( TRANSLATE(LTRIM(value),',',' ')||' ' ,' ') - 1)
INTO TEMP_DIR
FROM v$parameter
WHERE name = 'utl_file_dir';
UPDATE GL_INTERFACE
SET request_id = -2;
INSERT INTO GL_INTERFACE_HISTORY(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
encumbrance_type_id,
budget_version_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
average_journal_flag,
originating_bal_seg_value,
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,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
transaction_date,
reference1,
reference2,
reference3,
reference4,
reference5,
reference6,
reference7,
reference8,
reference9,
reference10,
reference11,
reference12,
reference13,
reference14,
reference15,
reference16,
reference17,
reference18,
reference19,
reference20,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30,
je_batch_id,
period_name,
je_header_id,
je_line_num,
chart_of_accounts_id,
functional_currency_code,
code_combination_id,
date_created_in_gl,
warning_code,
status_description,
stat_amount,
group_id,
request_id,
subledger_doc_sequence_id,
subledger_doc_sequence_value,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
context,
context2,
invoice_date,
tax_code,
invoice_identifier,
invoice_amount,
context3,
ussgl_transaction_code,
descr_flex_error_message,
ledger_id)
SELECT i.status,
i.set_of_books_id,
i.accounting_date,
i.currency_code,
i.date_created,
i.created_by,
i.actual_flag,
i.user_je_category_name,
i.user_je_source_name,
i.encumbrance_type_id,
i.budget_version_id,
i.currency_conversion_date,
i.user_currency_conversion_type,
i.currency_conversion_rate,
i.average_journal_flag,
i.originating_bal_seg_value,
i.segment1,
i.segment2,
i.segment3,
i.segment4,
i.segment5,
i.segment6,
i.segment7,
i.segment8,
i.segment9,
i.segment10,
i.segment11,
i.segment12,
i.segment13,
i.segment14,
i.segment15,
i.segment16,
i.segment17,
i.segment18,
i.segment19,
i.segment20,
i.segment21,
i.segment22,
i.segment23,
i.segment24,
i.segment25,
i.segment26,
i.segment27,
i.segment28,
i.segment29,
i.segment30,
i.entered_dr,
i.entered_cr,
i.accounted_dr,
i.accounted_cr,
i.transaction_date,
i.reference1,
i.reference2,
i.reference3,
i.reference4,
i.reference5,
i.reference6,
i.reference7,
i.reference8,
i.reference9,
i.reference10,
i.reference11,
i.reference12,
i.reference13,
i.reference14,
i.reference15,
i.reference16,
i.reference17,
i.reference18,
i.reference19,
i.reference20,
i.reference21,
i.reference22,
i.reference23,
i.reference24,
i.reference25,
i.reference26,
i.reference27,
i.reference28,
i.reference29,
i.reference30,
i.je_batch_id,
i.period_name,
i.je_header_id,
i.je_line_num,
i.chart_of_accounts_id,
i.functional_currency_code,
i.code_combination_id,
i.date_created_in_gl,
i.warning_code,
i.status_description,
i.stat_amount,
i.group_id,
i.request_id,
i.subledger_doc_sequence_id,
i.subledger_doc_sequence_value,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.attribute11,
i.attribute12,
i.attribute13,
i.attribute14,
i.attribute15,
i.attribute16,
i.attribute17,
i.attribute18,
i.attribute19,
i.attribute20,
i.context,
i.context2,
i.invoice_date,
i.tax_code,
i.invoice_identifier,
i.invoice_amount,
i.context3,
i.ussgl_transaction_code,
i.descr_flex_error_message,
i.ledger_id
FROM GL_INTERFACE i,
GL_SETS_OF_BOOKS s,
FND_ID_FLEX_STRUCTURES f,
GL_DATE_PERIOD_MAP d,
GL_PERIODS p
WHERE f.id_flex_num = s.chart_of_accounts_id
AND f.id_flex_code = 'GL#'
AND f.application_id = 101
AND p.period_name = d.period_name
AND p.period_set_name = d.period_set_name
AND d.accounting_date = trunc(i.accounting_date)
AND d.period_type = s.accounted_period_type
AND d.period_set_name = s.period_set_name
AND s.set_of_books_id = decode(i.ledger_id, -1, i.set_of_books_id, i.ledger_id)
AND i.request_id + 0 = -2;
DELETE FROM GL_INTERFACE
WHERE request_id + 0 = -2;
v_num_line_deleted := SQL%ROWCOUNT;
IF (v_num_line_exported <> v_num_line_deleted) THEN
RAISE DELETE_ERROR;
WHEN DELETE_ERROR THEN
fnd_message.set_name('FND', 'DELETE_ERROR');
raise_application_error(-20100, 'DELETE_ERROR');