[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT actual_flag
INTO act_flag
FROM gl_je_batches
WHERE je_batch_id = jeb_id;
SELECT gl_je_batches_s.nextval
INTO new_jeb_id
FROM dual;
SELECT nvl(max(decode(enable_budgetary_control_flag, 'Y', 'Y', null)),'N'),
nvl(max(decode(enable_je_approval_flag, 'Y', 'Y', null)), 'N')
INTO bc_flag, approval_flag
FROM gl_je_headers jeh, gl_ledgers lgr
WHERE jeh.je_batch_id = jeb_id
AND lgr.ledger_id = jeh.ledger_id;
SELECT journal_approval_flag
INTO approval_flag
FROM gl_je_sources
WHERE je_source_name = 'AutoCopy';--Modified the source from Manual to Autocopy as part of bug7373688
INSERT INTO gl_je_batches
(je_batch_id,
chart_of_accounts_id, period_set_name, accounted_period_type,
name, status, status_verified, budgetary_control_status,
actual_flag, average_journal_flag,
default_effective_date, default_period_name,
date_created, description, control_total,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10, context,
ussgl_transaction_code, org_id, approval_status_code,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT
new_jeb_id,
chart_of_accounts_id, period_set_name, accounted_period_type,
New_Name, 'U', 'N', decode(bc_flag, 'Y', 'R', 'N'),
actual_flag, average_journal_flag,
New_eff_date, New_period_name,
sysdate, description, control_total,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10, context,
ussgl_transaction_code, to_number(x_org_id),
decode(approval_flag, 'Y', 'R', 'Z'),
sysdate, usr_id, sysdate, usr_id, log_id
FROM gl_je_batches
WHERE je_batch_id = jeb_id;
CURSOR select_journals IS
SELECT jeh.je_header_id, jeh.ledger_id, jeh.je_category,
jeh.currency_code, jeh.currency_conversion_type,
jeh.currency_conversion_date, jeh.currency_conversion_rate,
lgr.currency_code
FROM gl_je_headers jeh, gl_ledgers lgr
WHERE jeh.je_batch_id = jeb_id
AND lgr.ledger_id = jeh.ledger_id
--Commented this as part of bug 7581299.
--AND jeh.parent_je_header_id IS NULL; /* See comments above */--Uncommented this as part of bug 7373688.
OPEN select_journals;
FETCH select_journals
INTO jeh_id, ledger_id, je_category,
currency_code, conversion_type, conversion_date,
conversion_rate, funct_curr;
EXIT WHEN select_journals%NOTFOUND;
INSERT INTO gl_je_headers
(je_batch_id, je_header_id, ledger_id,
je_category, je_source, default_effective_date, period_name,
name, currency_code, status, date_created,
multi_bal_seg_flag, actual_flag,
conversion_flag, encumbrance_type_id, budget_version_id,
accrual_rev_flag, accrual_rev_effective_date,
accrual_rev_period_name, accrual_rev_change_sign_flag,
description, control_total,
currency_conversion_type, currency_conversion_date,
currency_conversion_rate, external_reference,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,context,
ussgl_transaction_code, jgzz_recon_context, jgzz_recon_ref,
tax_status_code, reference_date, originating_bal_seg_value,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT
new_jeb_id, gl_je_headers_s.nextval, ledger_id,
je_category, 'AutoCopy', New_eff_date, New_period_name,---Modified the source from Manual to Autocopy as part of bug7373688
decode(parent_je_header_id, NULL, name,
substrb(name, 1, (100 - (lengthb(to_char(je_header_id))+1)))
|| ' ' || to_char(je_header_id)),
currency_code, 'U', sysdate,
'N', actual_flag,
conversion_flag, encumbrance_type_id, budget_version_id,
decode(rev_period, NULL, 'N', 'Y'), rev_date,
rev_period, rev_method,
description, control_total,
conversion_type, conversion_date,
conversion_rate, external_reference,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,context,
ussgl_transaction_code, jgzz_recon_context, jgzz_recon_ref,
'N', reference_date, originating_bal_seg_value,
sysdate, usr_id, sysdate, usr_id, log_id
FROM gl_je_headers
WHERE je_header_id = jeh_id;
INSERT INTO gl_je_lines
(je_header_id, je_line_num, ledger_id,
code_combination_id, period_name, effective_date,
status, entered_dr, entered_cr, accounted_dr, accounted_cr,
description, stat_amount, ignore_rate_flag,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10, context,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19,attribute20,context2,
ussgl_transaction_code,
co_third_party, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT
jeh2.je_header_id, jel.je_line_num, jel.ledger_id,
jel.code_combination_id, jeh2.period_name,
jeh2.default_effective_date,
'U', jel.entered_dr, jel.entered_cr,
decode(jel.ignore_rate_flag, 'Y', jel.accounted_dr,
decode(curr.minimum_accountable_unit,
NULL, round(jeh2.currency_conversion_rate * jel.entered_dr,
precision),
round(jeh2.currency_conversion_rate * jel.entered_dr
/ curr.minimum_accountable_unit)
* curr.minimum_accountable_unit)),
decode(jel.ignore_rate_flag, 'Y', jel.accounted_cr,
decode(curr.minimum_accountable_unit,
NULL, round(jeh2.currency_conversion_rate * jel.entered_cr,
precision),
round(jeh2.currency_conversion_rate * jel.entered_cr
/ curr.minimum_accountable_unit)
* curr.minimum_accountable_unit)),
jel.description, jel.stat_amount, jel.ignore_rate_flag,
jel.attribute1, jel.attribute2, jel.attribute3, jel.attribute4,
jel.attribute5, jel.attribute6, jel.attribute7, jel.attribute8,
jel.attribute9, jel.attribute10, jel.context,
jel.attribute11, jel.attribute12, jel.attribute13, jel.attribute14,
jel.attribute15, jel.attribute16, jel.attribute17, jel.attribute18,
jel.attribute19, jel.attribute20, jel.context2,
jel.ussgl_transaction_code,
jel.co_third_party,
sysdate, usr_id, sysdate, usr_id, log_id
FROM gl_je_headers jeh1, gl_je_headers jeh2, gl_ledgers lgr,
fnd_currencies curr, gl_je_lines jel
WHERE jeh1.je_batch_id = jeb_id
AND jeh2.je_batch_id = new_jeb_id
AND jeh2.name IN (jeh1.name,
substrb(jeh1.name,
1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
|| ' ' || to_char(jeh1.je_header_id))
AND lgr.ledger_id = jeh2.ledger_id
AND curr.currency_code = lgr.currency_code
AND jel.je_header_id = jeh1.je_header_id
AND nvl(jel.tax_line_flag,'N') = 'N';
INSERT INTO gl_je_segment_values
(je_header_id, segment_type_code, segment_value,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT
jeh2.je_header_id, sv.segment_type_code, sv.segment_value,
sysdate, usr_id, sysdate, usr_id, log_id
FROM gl_je_headers jeh1, gl_je_headers jeh2, gl_je_segment_values sv
WHERE jeh1.je_batch_id = jeb_id
AND jeh2.je_batch_id = new_jeb_id
AND jeh2.name IN (jeh1.name,
substrb(jeh1.name,
1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
|| ' ' || to_char(jeh1.je_header_id))
AND sv.je_header_id = jeh1.je_header_id;
INSERT INTO gl_je_lines_recon
(je_header_id, je_line_num, ledger_id,
jgzz_recon_ref,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT jeh2.je_header_id, jel.je_line_num, jel.ledger_id,
rec.jgzz_recon_ref,
sysdate, usr_id, sysdate,
usr_id, log_id
FROM gl_je_batches jeb, gl_je_headers jeh1, gl_je_headers jeh2,
gl_ledgers lgr, gl_je_lines jel,
gl_code_combinations cc, gl_je_lines_recon rec
WHERE jeb.je_batch_id = jeb_id
AND jeb.average_journal_flag = 'N'
AND jeh1.je_batch_id = jeb_id
AND jeh1.actual_flag = 'A'
AND jeh1.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
AND lgr.ledger_id = jeh1.ledger_id
AND lgr.enable_reconciliation_flag = 'Y'
AND jeh2.je_batch_id = new_jeb_id
AND jeh2.name IN (jeh1.name,
substrb(jeh1.name,
1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
|| ' ' || to_char(jeh1.je_header_id))
AND jel.je_header_id = jeh1.je_header_id
AND nvl(jel.tax_line_flag,'N') = 'N'
AND cc.code_combination_id = jel.code_combination_id
AND cc.jgzz_recon_flag = 'Y'
AND rec.je_header_id(+) = jel.je_header_id
AND rec.je_line_num(+) = jel.je_line_num;
UPDATE gl_je_headers jeh
SET (running_total_dr, running_total_cr,
running_total_accounted_dr, running_total_accounted_cr)
= (SELECT sum(nvl(entered_dr,0)), sum(nvl(entered_cr,0)),
sum(nvl(accounted_dr,0)), sum(nvl(accounted_cr,0))
FROM gl_je_lines jel
WHERE jel.je_header_id = jeh.je_header_id)
WHERE jeh.je_batch_id = new_jeb_id;
UPDATE gl_je_batches jeb
SET (running_total_dr, running_total_cr,
running_total_accounted_dr, running_total_accounted_cr)
= (SELECT sum(running_total_dr),
sum(running_total_cr),
sum(running_total_accounted_dr),
sum(running_total_accounted_cr)
FROM gl_je_headers jeh
WHERE jeh.je_batch_id = jeb.je_batch_id)
WHERE jeb.je_batch_id = new_jeb_id;
SELECT rowid, ledger_id, je_category,
substrb(name, 25)
FROM gl_je_headers
WHERE je_batch_id = new_jeb_id;
UPDATE gl_je_headers
SET doc_sequence_id = seq_id,
doc_sequence_value = seq_val
WHERE rowid = row_id;