[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_DR(in_company_code IN VARCHAR2,
in_batch_id IN NUMBER,
in_jnl_date IN DATE,
in_ccid IN NUMBER,
in_gl_link_id IN NUMBER,
in_ccy IN VARCHAR2,
in_alt_jrnl_date IN DATE) is
Begin
/* Private procedure to update debit journal rows within the given batch id. */
/* Updated columns are the transferred date and the link id used by the drilldown */
/* feature in GL which XTR does not have currently. */
Update XTR_JOURNALS
Set TRANSFER_TO_EXTERNAL_GL = trunc(sysdate),
gl_sl_link_id = in_gl_link_id,
alt_journal_date = in_alt_jrnl_date
where batch_id = in_batch_id
and journal_date = in_jnl_date
and code_combination_id = in_ccid
and currency = in_ccy
and (nvl(debit_amount,0) <> 0 or nvl(accounted_dr,0) <> 0);
End UPDATE_DR;
PROCEDURE UPDATE_CR(in_company_code IN VARCHAR2,
in_batch_id IN NUMBER,
in_jnl_date IN DATE,
in_ccid IN NUMBER,
in_gl_link_id IN NUMBER,
in_ccy IN VARCHAR2,
in_alt_jrnl_date IN VARCHAR2) is
Begin
/* Private procedure to update credit journal rows within the given batch id. */
Update XTR_JOURNALS
Set TRANSFER_TO_EXTERNAL_GL = trunc(sysdate),
gl_sl_link_id = in_gl_link_id,
alt_journal_date = in_alt_jrnl_date
where batch_id = in_batch_id
and JOURNAL_DATE = in_jnl_date
and code_combination_id = in_ccid
and currency = in_ccy
and (nvl(credit_amount,0) <> 0 or nvl(accounted_cr,0) <> 0);
End UPDATE_CR;
Select min(start_date) into l_next_start
from gl_period_statuses
where application_id = 101
and set_of_books_id = in_sob_id
and closing_status in ('O','F')
and adjustment_period_flag = 'N'
and start_date >= in_jrnl_date;
Select 1 into l_temp
from gl_period_statuses
where application_id = 101
and set_of_books_id = in_sob_id
and adjustment_period_flag = 'N'
and closing_status in ('O','F')
and in_jrnl_date between start_date and end_date;
Select
PARAMETER_VALUE_CODE
FROM XTR_COMPANY_PARAMETERS WHERE
COMPANY_CODE in (Select distinct company_code from xtr_journals
where batch_id = in_batch_id)
and parameter_code = 'ACCNT_UNBAL';
Select rowid from xtr_journals
Where batch_id = in_batch_id
and journal_date = l_jrnl_date and nvl(accounted_cr,0) <> 0
and accounted_cr = (Select min(accounted_cr) from xtr_journals
Where batch_id = in_batch_id
and journal_date = l_jrnl_date and nvl(accounted_cr,0) <> 0 );
Select rowid from xtr_journals
Where batch_id = in_batch_id
and journal_date = l_jrnl_date and nvl(accounted_dr,0) <> 0
and accounted_dr = (Select min(accounted_dr) from xtr_journals
Where batch_id = in_batch_id
and journal_date = l_jrnl_date and nvl(accounted_dr,0) <> 0 );
Select journal_date,
sum(nvl(accounted_dr,0)) - sum(nvl(accounted_cr,0)) difference
From XTR_JOURNALS
Where batch_id = in_batch_id
Group By journal_date
having sum(nvl(debit_amount,0)) = sum(nvl(credit_amount,0));
Select null
From DUAL
Where exists
(Select null
From XTR_JOURNALS
Where batch_id = in_batch_id
Group By journal_date
Having sum(nvl(accounted_dr,0)) <> sum(nvl(accounted_cr,0)));
update xtr_journals set accounted_dr=accounted_dr+.01
where rowid = l_rowid;
update xtr_journals set accounted_cr=accounted_cr+.01
where rowid = l_rowid;
Select parameter_value_code
from XTR_COMPANY_PARAMETERS
where company_code = in_company
and parameter_code = 'ACCNT_UNBAL';
select PTY.set_of_books_id, SOB.name
from XTR_PARTIES_V PTY,
GL_SETS_OF_BOOKS SOB
where PTY.set_of_books_id = SOB.set_of_books_id
and PTY.party_code = p_company_code;
select parameter_value_code
from XTR_COMPANY_PARAMETERS
where company_code = p_company_code
and parameter_code = 'ACCNT_JNTRM';-- determine the transfer method: SUMMARY or DETAIL
select null row_id,
batch_id batch_id,
journal_date journal_date,
code_combination_id ccid,
currency currency,
sum(nvl(debit_amount,0)) debit,
0 credit,
sum(nvl(accounted_dr,0)) acct_dr,
0 acct_cr,
to_number(null) trans_number,
null date_type,
to_number(null) deal_number,
null amount_type,
null action_code,
null deal_type,
null deal_subtype,
null product_type,
null portfolio_code
from XTR_JOURNALS
where batch_id = G_batch_id
and (nvl(debit_amount,0) <> 0 or nvl(accounted_dr,0) <> 0)
group by batch_id, journal_date, code_combination_id, currency
UNION
select null row_id,
batch_id batch_id,
journal_date journal_date,
code_combination_id ccid,
currency currency,
0 debit,
sum(nvl(credit_amount,0)) credit,
0 acct_dr,
sum(nvl(accounted_cr,0)) acct_cr,
to_number(null) trans_number,
null date_type,
to_number(null) deal_number,
null amount_type,
null action_code,
null deal_type,
null deal_subtype,
null product_type,
null portfolio_code
from XTR_JOURNALS
where batch_id = G_batch_id
and (nvl(credit_amount,0) <> 0 or nvl(accounted_cr,0) <> 0)
group by batch_id, journal_date, code_combination_id, currency;
select rowid row_id,
batch_id batch_id,
journal_date journal_date,
code_combination_id ccid,
currency currency,
(nvl(debit_amount,0)) debit,
nvl(credit_amount,0) credit,
(nvl(accounted_dr,0)) acct_dr,
nvl(accounted_cr,0) acct_cr,
transaction_number trans_number,
date_type date_type,
deal_number deal_number,
amount_type amount_type,
action_code action_code,
deal_type deal_type,
deal_subtype deal_subtype,
product_type product_type,
portfolio_code portfolio_code
from XTR_JOURNALS
where batch_id = G_batch_id
and (nvl(debit_amount,0) <> 0 or nvl(accounted_dr,0) <> 0 or nvl(credit_amount,0) <> 0 or nvl(accounted_cr,0) <> 0);
SELECT user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'Treasury';
select user_je_category_name
from gl_je_categories
where je_category_name = 'Treasury';
select user_conversion_type
from gl_daily_conversion_types
where conversion_type = (select conversion_type
from xtr_parties_v
where party_code = p_company_code);
select gl_interface_control_s.nextval
from dual;
Select XTR_AE_LINK_ID_S.nextval
into l_gl_link_id
from dual;
Insert into GL_INTERFACE(
status,
set_of_books_id,
code_combination_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
entered_dr,
entered_cr,
currency_conversion_date,
user_currency_conversion_type,
accounted_dr,
accounted_cr,
gl_sl_link_id,
group_id,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30)
Values ('NEW',
l_set_of_books,
JNL_REC.ccid,
l_source_name,
l_category_name,
l_journal_date,
JNL_REC.currency,
trunc(sysdate),
nvl(fnd_global.user_id,-1),
'A',
JNL_REC.debit,
JNL_REC.credit,
JNL_REC.journal_date,
l_xchange_type,
JNL_REC.acct_dr,
JNL_REC.acct_cr,
l_gl_link_id,
l_gl_group_id,
JNL_REC.batch_id,
JNL_REC.trans_number,
JNL_REC.date_type,
JNL_REC.deal_number,
JNL_REC.amount_type,
JNL_REC.action_code,
JNL_REC.deal_type,
JNL_REC.deal_subtype,
JNL_REC.product_type,
JNL_REC.portfolio_code);
Update XTR_JOURNALS
Set TRANSFER_TO_EXTERNAL_GL = trunc(sysdate),
gl_sl_link_id = l_gl_link_id,
alt_journal_date = l_journal_date -- bug 3461138
where rowid = JNL_REC.row_id;
UPDATE_DR (in_company_code,
JNL_REC.batch_id,
JNL_REC.journal_date,
JNL_REC.ccid,
l_gl_link_id,
JNL_REC.currency,
l_journal_date);
UPDATE_CR (in_company_code,
JNL_REC.batch_id,
JNL_REC.journal_date,
JNL_REC.ccid,
l_gl_link_id,
JNL_REC.currency,
l_journal_date);
Update XTR_BATCHES
Set GL_GROUP_ID = l_gl_group_id
Where BATCH_ID = in_batch_id;
Update XTR_BATCH_EVENTS
Set AUTHORIZED = 'Y',
AUTHORIZED_BY = fnd_global.user_id,
AUTHORIZED_ON = trunc(sysdate)
Where batch_id = in_batch_id
And event_code = 'JRNLGN';
End If; -- [l_ok_to_xfer... update batch tables after successful xfer]