[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select gl.je_header_id,
gl.je_line_num,
gl.code_combination_id,
nvl(gh.ussgl_transaction_code, '*'),
gh.currency_code,
(select sob.currency_code from gl_sets_of_books sob where sob.set_of_books_id = gh.ledger_id),
decode(gl.entered_dr, NULL, 0, 1) + decode(gl.entered_cr, NULL, 0, -1)
from gl_je_headers gh,
gl_je_lines gl
where gh.je_batch_id = p_je_batch_id
and gl.je_header_id = gh.je_header_id
and gh.je_category = 'MTL'
and gh.actual_flag = 'A'
and exists (select 1 from gl_import_references jir
where jir.je_header_id = gl.je_header_id
and jir.je_line_num = gl.je_line_num
and jir.gl_sl_link_table = 'MTA'
and (jir.gl_sl_link_id is null or p_rerun_mode = 'Y') --Flexible Logic
and jir.reference_3 is null);
select gir_rowid,
gl_sl_link_id
from cst_gl_summary_links_temp;
select /*+ ORDERED */
mta.rowid,
mta.inv_sub_ledger_id,
sl.gl_sl_link_id
from cst_gl_summary_links_temp sl,
mtl_transaction_accounts mta
where mta.gl_batch_id = sl.gl_batch_id
and mta.reference_account = sl.reference_account
and sl.gl_currency_code = nvl(mta.currency_code, sl.ledger_currency_code)
and sl.ussgl_transaction_code = nvl(mta.ussgl_transaction_code, '*')
and ( sl.gl_dr_cr_flag = 0
or (sl.gl_dr_cr_flag = 1 and mta.base_transaction_value > 0)
or (sl.gl_dr_cr_flag = -1 and mta.base_transaction_value < 0)
)
and ( mta.gl_sl_link_id is null
--To correct data during During re-runs
or exists ( select 1 from gl_import_references R
where R.gl_sl_link_table = 'MTA'
and R.gl_sl_link_id = mta.gl_sl_link_id
and R.reference_3 is null )) --Support Flexible Logic
and mta.encumbrance_type_id is null
order by sl.gl_sl_link_id;
debug(' >Inserting data in SL');
delete /*+ index(jir gl_import_references_n1) */
from gl_import_references jir
where jir.je_header_id = je_header_id_tab(i)
and jir.je_line_num = je_line_num_tab(i)
and exists
( select /*+ index(jir1 gl_import_references_n1) */ 1
from gl_import_references jir1
where jir1.je_header_id = jir.je_header_id
and jir1.je_line_num = jir.je_line_num
and jir1.je_batch_id = jir.je_batch_id
and nvl(jir1.reference_1, -1) = nvl(jir.reference_1, -1)
and nvl(jir1.reference_2, -1) = nvl(jir.reference_2, -1)
and jir1.reference_3 is null and jir.reference_3 is null
and jir1.gl_sl_link_table = jir.gl_sl_link_table
--and jir1.gl_sl_link_id = jir.gl_sl_link_id --Support Flexible Logic
and ( ( jir.gl_sl_link_id is null and jir1.gl_sl_link_id is null --Flexible Logic
and jir.rowid < jir1.rowid )
--'<' below is deliberatley used to support the flexible logic in c_mta ORDER BY clause
or ( jir.gl_sl_link_id < jir1.gl_sl_link_id ))); --Flexible Logic
debug(' L'||l_count||': Deleted '||SQL%ROWCOUNT||' rows from GIR');
insert into cst_gl_summary_links_temp
( je_header_id,
je_line_num,
gl_batch_id,
reference_account,
gl_currency_code,
ussgl_transaction_code,
gl_dr_cr_flag,
ledger_currency_code,
gir_rowid,
gl_sl_link_id
)
select /*+ index(jir gl_import_references_n1) */
je_header_id_tab(i),
je_line_num_tab(i),
nvl(jir.reference_1, -1),
code_combination_id_tab(i),
gl_currency_code_tab(i),
ussgl_transaction_code_tab(i),
gl_sign_flag_tab(i),
ledger_currency_code_tab(i),
jir.rowid,
nvl(jir.gl_sl_link_id, xla_gl_sl_link_id_s.nextval) --Support Flexible Logic
from gl_import_references jir
where jir.je_header_id = je_header_id_tab(i)
and jir.je_line_num = je_line_num_tab(i);
debug(' L'||l_count||': Inserted '||SQL%ROWCOUNT||' rows in SL');
debug('
select count(*) into l_count
from cst_gl_summary_links_temp;
select count(*) into l_count
from cst_gl_summary_links_temp link1
where link1.gl_dr_cr_flag = 0
and exists ( select 1 from cst_gl_summary_links_temp link2
where link2.gl_batch_id = link1.gl_batch_id
and link2.reference_account = link1.reference_account
and link2.gl_currency_code = link1.gl_currency_code
and link2.ussgl_transaction_code = link1.ussgl_transaction_code
and link2.gl_dr_cr_flag in (1,-1))
and rownum < 2;
should be updated to zero. Hence gl_dr_cr_flag for 2 & 3 should
be updated to zero */
l_stmnt_num := 40;
update cst_gl_summary_links_temp link1
set link1. gl_dr_cr_flag = 0
where link1.gl_dr_cr_flag in (1,-1)
and not exists ( select 1 from cst_gl_summary_links_temp link2
where link1.gl_batch_id = link2.gl_batch_id
and link1.reference_account = link2.reference_account
and link1.gl_currency_code = link2.gl_currency_code
and link1.ussgl_transaction_code = link2.ussgl_transaction_code
and ( ( link1.gl_dr_cr_flag = 1 and link2.gl_dr_cr_flag = -1 )
or ( link1.gl_dr_cr_flag = -1 and link2.gl_dr_cr_flag = 1 )
)
);
update gl_import_references gir
set gir.gl_sl_link_id = gl_sl_link_id_tab(i)
where gir.rowid = gir_rowid_tab(i)
and gir.gl_sl_link_id is null; --Support Flexible Logic
debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in GIR');
update mtl_transaction_accounts mta
set mta.gl_sl_link_id = gl_sl_link_id_tab(i)
where mta.rowid = mta_rowid_tab(i);
debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in MTA');
update /*+ index(xal xla_ae_lines_u1) */ xla_ae_lines xal
set xal.gl_sl_link_id = gl_sl_link_id_tab(i)
where xal.application_id = 707
and xal.gl_sl_link_table = 'MTA'
and (xal.ae_header_id, xal.ae_line_num)
in (select /*+ index(xdl xla_distribution_links_n1) */
xdl.ae_header_id,
xdl.ae_line_num
from xla_distribution_links xdl
where XDL.application_id = 707 /*Added for bug 16217359 */
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = inv_sub_ledger_id_tab(i));
debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in XAL');
debug(' No MTA records to be updated for gl_batch_id : '||p_je_batch_id);
select gl.je_header_id,
gl.je_line_num,
gl.code_combination_id,
nvl(gh.ussgl_transaction_code, '*'),
gh.currency_code,
(select sob.currency_code from gl_sets_of_books sob where sob.set_of_books_id = gh.ledger_id),
decode(gl.entered_dr, NULL, 0, 1) + decode(gl.entered_cr, NULL, 0, -1)
from gl_je_headers gh,
gl_je_lines gl
where gh.je_batch_id = p_je_batch_id
and gl.je_header_id = gh.je_header_id
and gh.je_category = 'WIP'
and gh.actual_flag = 'A'
and exists (select 1 from gl_import_references jir
where jir.je_header_id = gl.je_header_id
and jir.je_line_num = gl.je_line_num
and jir.gl_sl_link_table = 'WTA'
and (jir.gl_sl_link_id is null or p_rerun_mode = 'Y') --Flexible Logic
and jir.reference_3 is null);
select gir_rowid,
gl_sl_link_id
from cst_gl_summary_links_temp;
select /*+ ORDERED */
wta.rowid,
wta.wip_sub_ledger_id,
sl.gl_sl_link_id
from cst_gl_summary_links_temp sl,
wip_transaction_accounts wta
where wta.gl_batch_id = sl.gl_batch_id
and wta.reference_account = sl.reference_account
and sl.gl_currency_code = nvl(wta.currency_code, sl.ledger_currency_code)
and ( sl.gl_dr_cr_flag = 0
or (sl.gl_dr_cr_flag = 1 and wta.base_transaction_value > 0)
or (sl.gl_dr_cr_flag = -1 and wta.base_transaction_value < 0)
)
and ( wta.gl_sl_link_id is null
--To correct data during During re-runs
or exists ( select 1 from gl_import_references R
where R.gl_sl_link_table = 'WTA'
and R.gl_sl_link_id = wta.gl_sl_link_id
and R.reference_3 is null )) --Support Flexible Logic
order by sl.gl_sl_link_id;
debug(' >Inserting data in SL');
delete /*+ index(jir gl_import_references_n1) */
from gl_import_references jir
where jir.je_header_id = je_header_id_tab(i)
and jir.je_line_num = je_line_num_tab(i)
and exists
( select /*+ index(jir1 gl_import_references_n1) */ 1
from gl_import_references jir1
where jir1.je_header_id = jir.je_header_id
and jir1.je_line_num = jir.je_line_num
and jir1.je_batch_id = jir.je_batch_id
and nvl(jir1.reference_1, -1) = nvl(jir.reference_1, -1)
and nvl(jir1.reference_2, -1) = nvl(jir.reference_2, -1)
and jir1.reference_3 is null and jir.reference_3 is null
and jir1.gl_sl_link_table = jir.gl_sl_link_table
--and jir1.gl_sl_link_id = jir.gl_sl_link_id --Support Flexible Logic
and ( ( jir.gl_sl_link_id is null and jir1.gl_sl_link_id is null --Flexible Logic
and jir.rowid < jir1.rowid )
--'<' below is deliberatley used to support the flexible logic in c_wta ORDER BY clause
or ( jir.gl_sl_link_id < jir1.gl_sl_link_id ))); --Flexible Logic
debug(' L'||l_count||': Deleted '||SQL%ROWCOUNT||' rows from GIR');
insert into cst_gl_summary_links_temp
( je_header_id,
je_line_num,
gl_batch_id,
reference_account,
gl_currency_code,
ussgl_transaction_code,
gl_dr_cr_flag,
ledger_currency_code,
gir_rowid,
gl_sl_link_id
)
select /*+ index(jir gl_import_references_n1) */
je_header_id_tab(i),
je_line_num_tab(i),
nvl(jir.reference_1, -1),
code_combination_id_tab(i),
gl_currency_code_tab(i),
ussgl_transaction_code_tab(i),
gl_sign_flag_tab(i),
ledger_currency_code_tab(i),
jir.rowid,
nvl(jir.gl_sl_link_id, xla_gl_sl_link_id_s.nextval) --Support Flexible Logic
from gl_import_references jir
where jir.je_header_id = je_header_id_tab(i)
and jir.je_line_num = je_line_num_tab(i);
debug(' L'||l_count||': Inserted '||SQL%ROWCOUNT||' rows in SL');
debug('
select count(*) into l_count
from cst_gl_summary_links_temp;
select count(*) into l_count
from cst_gl_summary_links_temp link1
where link1.gl_dr_cr_flag = 0
and exists ( select 1 from cst_gl_summary_links_temp link2
where link2.gl_batch_id = link1.gl_batch_id
and link2.reference_account = link1.reference_account
and link2.gl_currency_code = link1.gl_currency_code
and link2.ussgl_transaction_code = link1.ussgl_transaction_code
and link2.gl_dr_cr_flag in (1,-1))
and rownum < 2;
should be updated to zero. Hence gl_dr_cr_flag for 2 & 3 should
be updated to zero */
l_stmnt_num := 40;
update cst_gl_summary_links_temp link1
set link1. gl_dr_cr_flag = 0
where link1.gl_dr_cr_flag in (1,-1)
and not exists ( select 1 from cst_gl_summary_links_temp link2
where link1.gl_batch_id = link2.gl_batch_id
and link1.reference_account = link2.reference_account
and link1.gl_currency_code = link2.gl_currency_code
and link1.ussgl_transaction_code = link2.ussgl_transaction_code
and ( ( link1.gl_dr_cr_flag = 1 and link2.gl_dr_cr_flag = -1 )
or ( link1.gl_dr_cr_flag = -1 and link2.gl_dr_cr_flag = 1 )
)
);
update gl_import_references gir
set gir.gl_sl_link_id = gl_sl_link_id_tab(i)
where gir.rowid = gir_rowid_tab(i)
and gir.gl_sl_link_id is null; --Support Flexible Logic
debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in GIR');
update wip_transaction_accounts wta
set wta.gl_sl_link_id = gl_sl_link_id_tab(i)
where wta.rowid = wta_rowid_tab(i);
debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in WTA');
update /*+ index(xal xla_ae_lines_u1) */ xla_ae_lines xal
set xal.gl_sl_link_id = gl_sl_link_id_tab(i)
where xal.application_id = 707
and xal.gl_sl_link_table = 'WTA'
and (xal.ae_header_id, xal.ae_line_num)
in (select /*+ index(xdl xla_distribution_links_n1) */
xdl.ae_header_id,
xdl.ae_line_num
from xla_distribution_links xdl
where XDL.application_id =707 /*Added for bug 16217359*/
AND xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = wip_sub_ledger_id_tab(i));
debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in XAL');
debug(' No WTA records to be updated for gl_batch_id : '||p_je_batch_id);
PROCEDURE update_mta_wta
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2,
p_ledger_id IN NUMBER)
IS
CURSOR c_glb ( l_from_date IN DATE,
l_to_date IN DATE ) IS
SELECT DISTINCT gh.je_batch_id
FROM gl_period_statuses gps,
gl_je_headers gh
WHERE gps.set_of_books_id = p_ledger_id
AND gps.migration_status_code = 'U'
AND gps.application_id = 401
AND gps.start_date >= l_from_date
AND gps.end_date <= l_to_date
AND gh.ledger_id = gps.set_of_books_id
AND gh.period_name = gps.period_name
AND gh.je_source = 'Cost Management'
AND gh.je_category in ('MTL', 'WIP');
debug('update_mta_wta +');
debug('update_mta_wta -');
debug('EXCEPTION OTHERS update_mta_wta :'||SQLERRM);
END update_mta_wta;