[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The insert of the revaluation row (In procedure CALC_REVAL_PROCESS) is
not being currently called from NEW_TRANSFER_TO_GL until we clear up
how / what we are revaluing and if we should be creating Journals for each G/L contra
agst REVAL G/L (all flagged ready for reversal) - should we only be reval g/ls after closeout
to the Balance Sheet (ie only Asset / Liab A/c's should be revalued)
*/
--------------------------------------------------------------------------------------------------------------------------
debug_flag varchar2(1) := 'F';
Select decode(nvl(in_prin_flag,'N'),
'Y', principal_gl,
(decode(nvl(in_int_flag,'N'), 'Y', interest_gl, NULL)))
into l_ccid
From xtr_ig_journal_structures
Where company_code = in_company_code
and cparty_code = in_cpty
and cp_currency = in_curr
and cp_acct_no = in_bank_acct_no;
Select parameter_value_code into l_param_value
from xtr_company_parameters
where company_code = in_company
and parameter_code = 'ACCNT_CLPER';
| from pkg. Journals are now updated on |
| a real-time basis from DB trigger call |
| to the procedure UPDATE_JOURNALS, when |
| a deal is cancelled. |
| 05/24/99 eklau (1) Added p_dummy_date parameter for |
gen_journals| use by concurrent prg validation only. |
| 03/12/01 jhung (1) Add p_period_end,p_batch_id_from, |
| p_batch_id_to, remove p_cutoff_date |
| parameter for introduction of batch_id |
| concept. |
----------------------------------------------------------------------------- */
PROCEDURE Do_Journal_Process
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_source_option IN VARCHAR2,
p_company_code IN VARCHAR2,
p_batch_id_from IN NUMBER,
p_batch_id_to IN NUMBER,
p_cutoff_date IN DATE) IS
--
p_curr_batch_id XTR_BATCHES.BATCH_ID%TYPE;
Select batch_id
From XTR_BATCHES
Where company_code = p_company_code
and batch_type is null
and batch_id between nvl(p_batch_id_from, batch_id) and nvl(p_batch_id_to, batch_id)
Order by batch_id asc;
Select period_start, period_end, upgrade_batch
From XTR_BATCHES
Where batch_id = p_curr_batch_id;
select dealer_code
from xtr_dealer_codes_v
where user_id = fnd_user_id;
select set_of_books_id
from xtr_parties_v
where party_type = 'C'
and party_code = p_company_code;
select parameter_value_code -- replaced a.suspense_ccid
from xtr_parties_v a,
xtr_company_parameters b
where a.party_code = p_company_code
and a.party_code = b.company_code
and b.parameter_code = 'ACCNT_SUSAC';
select pre.batch_id
from XTR_BATCHES PRE,
XTR_BATCHES CUR
where cur.company_code = p_company_code
and cur.batch_id = p_curr_batch_id
and pre.company_code = cur.company_code
and cur.period_start = (pre.period_end + 1)
and pre.upgrade_batch <> 'Y'
and pre.batch_type is null;
select 1
from XTR_BATCH_EVENTS
where batch_id = l_pre_batch_id
and EVENT_CODE = 'JRNLGN';
select 1
from XTR_BATCH_EVENTS
where batch_id = p_curr_batch_id
and event_code = 'ACCRUAL'
and authorized = 'Y';
select 1
from XTR_BATCH_EVENTS
where batch_id = p_curr_batch_id
and event_code = 'JRNLGN';
Select XTR_BATCHES_S.NEXTVAL
from DUAL;
Insert into XTR_BATCHES(batch_id, company_code, period_start, period_end,
gl_group_id, upgrade_batch, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, batch_type)
values (p_curr_batch_id, p_company_code, p_cutoff_date, p_cutoff_date,
null, 'N', fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id, 'J');
| 04/29/99 eklau (1) Changed update of JOURNAL_CREATED |
| in DDA from end of processing for the |
| amount date to upon change in deal_type |
| deal_number, transaction_number, |
| date_type, and amount_date for non 'EXP'|
| deals. For 'EXP' deals, it's upon chg |
| of deal number. |
| (2) Consolidated cursors Q2 and Q4. Q4 |
| originally processed 'CA' and 'IG' deals|
| and Q2 all deals except the above and |
| 'EXP'. |
| (3) Changed Q3 - EXP to be driven off |
| of the DDA table instead of the exposure|
| transactions table. |
| (4) Q5 removed due to change in how we |
| post INTSET to DDA. Should now be |
| handled by Q2. |
| 05/26/99 eklau (1) Added logic to calculate accounted |
| amounts. |
| 08/13/99 eklau (1) Added cursors Q4 and Q5 to handle |
| accruals and revaluations, respectively.|
| 02/19/02 eklau Changed procedure to accomodate new |
| xtr_ig_journal_structure table for |
| re-arch of IG journaling in order to |
| achieve the new company to company IG |
| feature for patchset G. |
--------------------------------------------------------------------- */
FUNCTION GEN_JOURNALS
(in_source_option IN VARCHAR2,
in_company IN VARCHAR2,
in_batch_id IN NUMBER,
in_period_end IN DATE,
in_upgrade_batch IN VARCHAR2) RETURN NUMBER is
--
l_accounted_cr NUMBER;
Select 1
from dual
where exists (select null
from xtr_journals
where batch_id = bid);
Select XTR_BATCH_EVENTS_S.NEXTVAL
From DUAL;
select
e.company_code company_code
,c.deal_number deal_number
,c.currency currency
,a.deal_type deal_type
,a.deal_subtype deal_subtype
,nvl(a.product_type,'NOT APPLIC') product_type
,a.portfolio_code portfolio_code
,c.amount_type amount_type
,decode(a.credit_or_debit,'DR',nvl(c.amount,0),0) dr_amount
,decode(a.credit_or_debit,'CR',nvl(c.amount,0),0) cr_amount
,c.transaction_number transaction_number
,decode(a.get_gl_from_deal,
'Y', g.code_combination_id,
a.code_combination_id) ccid
,c.client_broker_clracct client_broker_clracct
,e.date_type date_type
,e.amount_date amount_date -- Reverted Back to amount_date Bug 5235988
,c.settlement_number settlement_number
,a.get_prin_ccid_from_deal use_prin_ccid_flag
,a.get_int_ccid_from_deal use_int_ccid_flag
,c.cparty_account_no cparty_account_no
,e.cparty_code cparty_code
,a.action_code action_code
,decode(a.deal_type,'CA','-1',c.dual_authorisation_by) validated_by -- Added for 1336492.
,c.account_no co_account_no -- Added for 1336492.
from XTR_JOURNAL_ENTRY_ACTIONS a,
XTR_DEAL_DATE_AMOUNTS c,
XTR_DEAL_DATE_AMOUNTS e, -- To generate Journal for Bank Acct
XTR_JOURNAL_ENTRY_ACTIONS f, -- " " " " " "
XTR_BANK_ACCOUNTS g
where e.company_code = in_company
and e.batch_id is null
and e.amount_date <= in_period_end -- Reverted Back to amount_date Bug 5235988
and e.deal_type NOT IN ('EXP')
and ((e.deal_type = 'BOND' and e.status_code not in ('CLOSED','CANCELLED')) or
(e.deal_type <> 'BOND' and e.status_code <> 'CANCELLED'))
and f.company_code = e.company_code
and f.deal_type = e.deal_type
and f.deal_subtype = e.deal_subtype
and f.product_type = nvl(e.product_type,'NOT APPLIC')
and f.portfolio_code = nvl(e.portfolio_code,'NOTAPPL')
and f.date_type = e.date_type
and e.amount_date between nvl(f.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(f.effective_to, sysdate) -- flex journals-- Reverted Back to amount_date Bug 5235988
and e.date_type <> 'REVAL' -- jhung 3/14
and c.company_code = e.company_code
and c.deal_number = e.deal_number
and (e.deal_type = 'RTMM' and e.amount_date = c.amount_date) --Bug 16503310
and c.transaction_number = e.transaction_number
and c.amount <> 0.00 -- 1336492
and ((e.deal_type = 'BOND' and c.status_code not in ('CLOSED','CANCELLED')) or
(e.deal_type <> 'BOND' and c.status_code <> 'CANCELLED')) -- Bug 3359347.
and a.company_code = c.company_code
and a.deal_type = c.deal_type
and a.deal_subtype = c.deal_subtype
and a.product_type = nvl(c.product_type,'NOT APPLIC')
and a.portfolio_code = nvl(c.portfolio_code,'NOTAPPL')
and a.amount_type = c.amount_type
and nvl(a.action_code,'x') = nvl(c.action_code,'x')
and a.rowid = f.rowid
and g.party_code(+) = c.company_code
and g.account_number(+) = c.account_no
and g.currency(+) = c.currency
and e.company_code IN (SELECT xca.party_code
FROM XTR_COMPANY_AUTHORITIES xca
WHERE xca.DEALER_CODE = xtr_user_access.dealer_code
AND xca.COMPANY_AUTHORISED_FOR_INPUT = 'Y' ) -- bug 5605716
order by e.deal_type, e.deal_number, e.transaction_number, e.date_type, e.amount_date;
select
dda.company_code company_code
,dda.deal_number deal_number
,dda.currency currency
,dda.deal_type deal_type
,dda.deal_subtype deal_subtype
,dda.product_type product_type
,dda.portfolio_code portfolio_code
,dda.amount_type amount_type
,decode(dda.action_code,'PAY',nvl(dda.amount,0),0) debit_amount
,decode(dda.action_code,'REC',nvl(dda.amount,0),0) credit_amount
,dda.transaction_number trans_number
,dda.amount_date amount_date --Reverted Back to amount_date Bug 5235988
,typ.code_combination_id type_ccid
,bnk.code_combination_id bank_ccid
,dda.settlement_number settlement_number
,dda.date_type date_type
,dda.action_code action_code
,dda.dual_authorisation_by validated_by -- Added for 1336492.
from XTR_DEAL_DATE_AMOUNTS_V dda,
XTR_EXPOSURE_TRANSACTIONS exp,
XTR_EXPOSURE_TYPES typ,
XTR_BANK_ACCOUNTS bnk
where dda.company_code = in_company
and dda.amount_date <= in_period_end
and dda.batch_id is null
and dda.settle = 'Y'
and dda.deal_type = 'EXP'
and dda.deal_subtype = 'FIRM'
and dda.company_code = exp.company_code
and dda.transaction_number = exp.transaction_number
and dda.amount <> 0.00 -- 1336492.
and exp.company_code = typ.company_code
and exp.exposure_type = typ.exposure_type
and bnk.party_code (+) = dda.company_code
and bnk.account_number (+) = dda.account_no
order by exp.transaction_number -- 1336492.
;
Select
acc.batch_id batch_id
,acc.company_code company_code
,acc.deal_no deal_number
,acc.currency currency
,acc.deal_type deal_type
,acc.deal_subtype deal_subtype
,acc.product_type product_type
,acc.portfolio_code portfolio_code
,acc.amount_type amount_type
,acc.accrls_amount accrls_amount
,acc.trans_no trans_number
,acc.period_to amount_date
,jea.credit_or_debit credit_or_debit
,jea.code_combination_id ccid
,acc.rowid row_id
,jea.date_type date_type
,jea.action_code action_code
from xtr_accrls_amort acc,
xtr_journal_entry_actions jea
where acc.batch_id = in_batch_id
and jea.company_code = in_company
and jea.date_type = 'ACCRUAL'
and jea.company_code = acc.company_code
and jea.deal_type = acc.deal_type
and jea.deal_subtype = acc.deal_subtype
and jea.product_type = acc.product_type
and jea.portfolio_code = acc.portfolio_code
and jea.amount_type = acc.amount_type
and jea.action_code = acc.action_code
and acc.period_to between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and NVL(JEA.effective_to, sysdate) -- flex journals
and acc.period_to <= in_period_end
and nvl(in_upgrade_batch,'N') <> 'I' -- Do not generate accruals for inaugural batch
order by acc.rowid
;
Select
dnm.rowid row_id
,dnm.batch_id batch_id
,dnm.company_code company_code
,dnm.deal_number deal_number
,dnm.currency reval_currency
,dea.currency deal_currency
,dea.deal_type deal_type
,dea.deal_subtype deal_subtype
,dea.product_type product_type
,dea.portfolio_code portfolio_code
-- ,decode(jea.amount_type,'REAL',nvl(dnm.amount,0),0)
-- ,decode(jea.amount_type,'UNREAL',nvl(dnm.amount,0),0)
,dnm.amount amount
,dnm.transaction_number trans_number
,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
,jea.credit_or_debit credit_or_debit
,jea.code_combination_id ccid
,jea.amount_type amount_type
,jea.action_code action_code
,jea.date_type date_type
from xtr_gain_loss_dnm dnm,
xtr_journal_entry_actions jea,
xtr_deals dea
where dnm.batch_id = in_batch_id
and jea.company_code = in_company
and jea.date_type = dnm.date_type
and jea.amount_type = dnm.amount_type
and jea.action_code = dnm.action
and jea.company_code = dnm.company_code
and jea.deal_type ||'' = dea.deal_type
and ((jea.deal_type in ('FXO','BOND','NI','FRA','FX') and
nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
((nvl(in_upgrade_batch,'N')<> 'I') and
not(jea.deal_type = 'FX' and jea.deal_subtype = 'FORWARD')))
and jea.deal_subtype = dea.deal_subtype
and jea.product_type = dea.product_type
and jea.portfolio_code = dea.portfolio_code
and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
and dnm.deal_number = dea.deal_no
-- and dnm.amount_date <= in_period_end
-- and dnm.rate_error = 'N' -- what do we substitute with this column?
union
----------
-- ONC --
----------
Select
dnm.rowid row_id
,dnm.batch_id batch_id
,dnm.company_code company_code
,dnm.deal_number deal_number
,dnm.currency reval_currency
,ro.currency deal_currency
,ro.deal_type deal_type
,ro.deal_subtype deal_subtype
,ro.product_type product_type
,ro.portfolio_code portfolio_code
,dnm.amount amount
,dnm.transaction_number trans_number
,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
,jea.credit_or_debit credit_or_debit
,jea.code_combination_id ccid
,jea.amount_type amount_type
,jea.action_code action_code
,jea.date_type date_type
from xtr_gain_loss_dnm dnm,
xtr_journal_entry_actions jea,
xtr_rollover_transactions ro
where dnm.batch_id = in_batch_id
and jea.company_code = in_company
and jea.date_type = dnm.date_type
and jea.amount_type = dnm.amount_type
and jea.action_code = dnm.action
and jea.company_code = dnm.company_code
and jea.deal_type ||'' = ro.deal_type
and ro.deal_type = 'ONC'
and ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
(nvl(in_upgrade_batch,'N')<> 'I'))
and jea.deal_subtype = ro.deal_subtype
and jea.product_type = ro.product_type
and jea.portfolio_code = ro.portfolio_code
and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
and dnm.deal_number = ro.deal_number
and dnm.transaction_number = ro.transaction_number
union
--------
-- IG --
--------
Select
dnm.rowid row_id
,dnm.batch_id batch_id
,dnm.company_code company_code
,dnm.deal_number deal_number
,dnm.currency reval_currency
,ig.currency deal_currency
,'IG' deal_type
,decode(sign(ig.balance_out),-1,'FUND','INVEST') deal_subtype
,ig.product_type product_type
,ig.portfolio portfolio_code
,dnm.amount amount
,dnm.transaction_number trans_number
,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
,jea.credit_or_debit credit_or_debit
,jea.code_combination_id ccid
,jea.amount_type amount_type
,jea.action_code action_code
,jea.date_type date_type
from xtr_gain_loss_dnm dnm,
xtr_journal_entry_actions jea,
xtr_intergroup_transfers ig
where dnm.batch_id = in_batch_id
and jea.company_code = in_company
and jea.date_type = dnm.date_type
and jea.amount_type = dnm.amount_type
and jea.action_code = dnm.action
and jea.company_code = dnm.company_code
and jea.deal_type = ig.deal_type
and ig.deal_type = 'IG'
and ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
(nvl(in_upgrade_batch,'N')<> 'I'))
and jea.deal_subtype = decode(sign(ig.balance_out),-1,'FUND','INVEST')
and jea.product_type = ig.product_type
and jea.portfolio_code = ig.portfolio
and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
and dnm.deal_number = ig.deal_number
and dnm.transaction_number = ig.transaction_number
union
--------
-- CA --
--------
Select
dnm.rowid row_id
,dnm.batch_id batch_id
,dnm.company_code company_code
,dnm.deal_number deal_number
,dnm.currency reval_currency
,ba.currency deal_currency
,'CA' deal_type
,decode(sign(bb.balance_cflow),-1,'FUND','INVEST') deal_subtype
,'NOT APPLIC' product_type
,ba.portfolio_code portfolio_code
,dnm.amount amount
,dnm.transaction_number trans_number
,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
,jea.credit_or_debit credit_or_debit
,jea.code_combination_id ccid
,jea.amount_type amount_type
,jea.action_code action_code
,jea.date_type date_type
from xtr_gain_loss_dnm dnm,
xtr_journal_entry_actions jea,
xtr_revaluation_details rd,
xtr_bank_accounts ba,
xtr_bank_balances bb
where dnm.batch_id = in_batch_id
and jea.company_code = in_company -- JEA
and jea.date_type = dnm.date_type -- DNM
and jea.amount_type = dnm.amount_type -- DNM
and jea.action_code = dnm.action -- DNM
and jea.company_code = dnm.company_code -- DNM
and jea.deal_type = rd.deal_type -- JEA
and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
and rd.deal_type = 'CA' -- Reval Details
and rd.deal_no = dnm.deal_number -- Reval Details
and rd.batch_id = dnm.batch_id -- Reval Details
and rd.period_to = dnm.journal_date -- Reval Details
and rd.realized_flag = 'Y' -- Reval Details
and ba.party_code = bb.company_code -- Bank Balances
and ba.account_number = bb.account_number -- Bank Balances
and bb.balance_date = dnm.journal_date -- Bank Balances
and jea.deal_subtype = decode(sign(bb.balance_cflow),
-1,'FUND','INVEST') -- JEA
and jea.product_type = 'NOT APPLIC' -- JEA
and jea.portfolio_code = ba.portfolio_code -- JEA
and ba.party_code = rd.company_code -- Bank Account
and ba.account_number = rd.account_no -- Bank Account
and ba.currency = rd.currencya -- Bank Account
and ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
(nvl(in_upgrade_batch,'N')<> 'I'))
order by 1;
Cursor Q6 is Select
dnm.rowid row_id,
dnm.batch_id batch_id,
dnm.company_code company_code,
dnm.deal_number deal_number,
dnm.currency reval_currency,
dnm.currency deal_currency,
dea.deal_type deal_type,
dea.deal_subtype deal_subtype,
dea.product_type product_type,
dea.portfolio_code portfolio_code,
dnm.amount amount,
dnm.transaction_number trans_number,
dnm.journal_date journal_date,
jea.credit_or_debit credit_or_debit,
jea.code_combination_id ccid,
jea.amount_type amount_type,
jea.action_code action_code,
jea.date_type date_type,
dnm.reval_eff_flag reval_eff_flag
from XTR_GAIN_LOSS_DNM dnm,
XTR_DEALS dea,
XTR_JOURNAL_ENTRY_ACTIONS jea
Where dnm.batch_id = in_batch_id
And jea.company_code = in_company
And jea.company_code = dnm.company_code
And jea.amount_type = dnm.amount_type
And jea.action_code = dnm.action
And jea.deal_type = dea.deal_type
And jea.deal_subtype = dea.deal_subtype
And jea.product_type = dea.product_type
And jea.portfolio_code = dea.portfolio_code
And dea.deal_no = dnm.deal_number
And jea.date_type = dnm.date_type
And dnm.journal_date between nvl(jea.effective_from,
ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate)
And jea.deal_type = 'FX'
And jea.deal_subtype = 'FORWARD'
Order by dnm.deal_number, dnm.reval_eff_flag desc;
Cursor Q7 is Select
dnm.rowid row_id,
dnm.batch_id batch_id,
dnm.company_code company_code,
dnm.deal_number deal_number,
dnm.currency reval_currency,
att.hedge_currency deal_currency,
'HEDGE' deal_type,
str.hedge_type deal_subtype,
str.hedge_approach product_type,
att.strategy_code portfolio_code,
dnm.amount amount,
dnm.transaction_number trans_number,
dnm.journal_date journal_date,
hac.credit_or_debit credit_or_debit,
hac.code_combination_id ccid,
hac.amount_type amount_type,
hac.action_code action_code,
hac.date_type date_type
from XTR_GAIN_LOSS_DNM dnm,
XTR_HEDGE_JOURNAL_ACTIONS hac,
XTR_HEDGE_ATTRIBUTES att,
XTR_HEDGE_STRATEGIES str
Where dnm.batch_id = in_batch_id
And hac.company_code = in_company
And hac.company_code = dnm.company_code
And hac.amount_type = dnm.amount_type
And hac.action_code = dnm.action
And hac.date_type = dnm.date_type
And str.strategy_code = att.strategy_code
And att.hedge_attribute_id = dnm.deal_number
And hac.code_combination_id is NOT NULL
And str.hedge_approach = 'FIRMCOM';
select substr(sob.currency_code,1,3),
cp.parameter_value_code, -- replaced pty.conversion_type,
dct.user_conversion_type
from xtr_parties_v pty,
xtr_company_parameters cp,
gl_sets_of_books sob,
gl_daily_conversion_types dct
where pty.party_code = in_company
and cp.company_code = pty.party_code
and cp.parameter_code = 'ACCNT_EXRTP'
and pty.set_of_books_id = sob.set_of_books_id
and cp.parameter_value_code = dct.conversion_type (+);
select b.upgrade_batch
from XTR_REVALUATION_DETAILS a,
XTR_BATCHES b
where a.company_code = in_company
and a.batch_id = b.batch_id
and nvl(b.upgrade_batch,'N') = 'Y';
SELECT min(journal_date)
FROM XTR_JOURNALS
WHERE batch_id = in_batch_id;
Select param_value
from xtr_pro_param
where param_name = p_param_name;
Select dual_authorisation_by
from xtr_deals
where deal_no = l_deal_no;
Select user_deal_type
from xtr_deal_types
where deal_type = l_deal_type;
Insert into xtr_unval_deals_gt
(deal_type,
deal_number,
trans_number)
values (l_user_deal_type, l_deal_no, l_trans_no_1);
FND_MESSAGE.Set_Token ('EVENT','INSERT_INTO_TEMP_TABLE');
Insert into xtr_unval_deals_gt
(deal_type,
deal_number,
trans_number)
values (l_user_deal_type, l_deal_no, l_trans_no_1);
FND_MESSAGE.Set_Token ('EVENT','Inserting non-validated deal/transaction number into temp table.');
Select *
from xtr_unval_deals_gt;
Delete from xtr_unval_deals_gt;
/* if it is an IG transaction with "dynamic" ccid selection. */
If (Q2_REC.ccid is NULL and Q2_REC.deal_type = 'IG') then
Q2_REC.ccid := GET_IG_CCID (
Q2_REC.use_prin_ccid_flag,
Q2_REC.use_int_ccid_flag,
Q2_REC.company_code,
Q2_REC.cparty_code,
Q2_REC.currency,
Q2_REC.cparty_account_no);
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
portfolio_code,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
settlement_number,
date_type,
action_code)
Values (l_batch_id,
Q2_REC.company_code,
nvl(Q2_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q2_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
Q2_REC.deal_number,
nvl(Q2_REC.transaction_number,999),
Q2_REC.deal_type,
Q2_REC.deal_subtype,
Q2_REC.product_type,
Q2_REC.dr_amount,
Q2_REC.cr_amount,
Q2_REC.ccid,
Q2_REC.amount_type,
G_user,
TRUNC(SYSDATE),
Q2_REC.portfolio_code,
Q2_REC.currency,
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q2_REC.settlement_number, -- Bug 4004772.
Q2_REC.date_type,
Q2_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting journal into table.');
xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Update DDA journal created flag.');
Update XTR_DEAL_DATE_AMOUNTS
set BATCH_ID = l_batch_id
where deal_type = l_prev_deal_type
and deal_number = l_prev_deal_nbr
and transaction_number = l_prev_transaction_nbr
and date_type = l_prev_date_type
and amount_date = l_prev_amount_date --Reverted Back to amount_date Bug 5235988
and batch_id is null; -- prevent overwritting the previous batch_id
xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Update last batch of Q2 data.');
Update XTR_DEAL_DATE_AMOUNTS
set batch_id = l_batch_id
where deal_type = l_prev_deal_type
and date_type = l_prev_date_type
and deal_number = l_prev_deal_nbr
and transaction_number = l_prev_transaction_nbr
and amount_date = l_prev_amount_date --reverted Back to amount_date Bug 5235988
and batch_id is null; -- prevent overwritting the previous batch ID
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
settlement_number,
date_type,
action_code)
Values
(l_batch_id,
Q3_REC.company_code,
nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
Q3_REC.deal_number,
nvl(Q3_REC.trans_number,999),
Q3_REC.deal_type,
Q3_REC.deal_subtype,
Q3_REC.product_type,
Q3_REC.portfolio_code,
Q3_REC.debit_amount,
Q3_REC.credit_amount,
Q3_REC.type_ccid,
Q3_REC.amount_type,
G_user,
trunc(SYSDATE),
Q3_REC.currency,
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q3_REC.settlement_number,
Q3_REC.date_type,
Q3_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting exposure journal into table.');
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
settlement_number,
date_type,
action_code)
Values
(l_batch_id,
Q3_REC.company_code,
nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
Q3_REC.deal_number,
nvl(Q3_REC.trans_number,999),
Q3_REC.deal_type,
Q3_REC.deal_subtype,
Q3_REC.product_type,
Q3_REC.portfolio_code,
Q3_REC.debit_amount,
Q3_REC.credit_amount,
Q3_REC.bank_ccid,
Q3_REC.amount_type,
G_user,
trunc(SYSDATE),
Q3_REC.currency,
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q3_REC.settlement_number,
Q3_REC.date_type,
Q3_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting exposure journal into table.');
Update XTR_DEAL_DATE_AMOUNTS
set BATCH_ID = l_batch_id
where company_code = in_company
and deal_type = 'EXP'
and deal_number = Q3_REC.deal_number
and settlement_number = Q3_REC.settlement_number
and settle = 'Y'
and batch_id is null; -- prevent overwritting the previous batch_id
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
date_type,
action_code)
Values
(Q4_REC.batch_id,
Q4_REC.company_code,
nvl(Q4_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q4_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
Q4_REC.deal_number,
nvl(Q4_REC.trans_number,999),
Q4_REC.deal_type,
Q4_REC.deal_subtype,
Q4_REC.product_type,
Q4_REC.portfolio_code,
decode(Q4_REC.credit_or_debit,'DR',Q4_REC.accrls_amount,0),
decode(Q4_REC.credit_or_debit,'CR',Q4_REC.accrls_amount,0),
Q4_REC.ccid,
Q4_REC.amount_type,
G_user,
trunc(SYSDATE),
Q4_REC.currency,
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q4_REC.date_type,
Q4_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting accruals journal into table');
select currency_buy , currency_sell
into l_curr_buy, l_curr_sell
from
xtr_deals where deal_no = Q5_REC.deal_number;
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
date_type,
action_code)
Values
(Q5_REC.batch_id,
Q5_REC.company_code,
nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
Q5_REC.deal_number,
nvl(Q5_REC.trans_number,999),
Q5_REC.deal_type,
Q5_REC.deal_subtype,
Q5_REC.product_type,
Q5_REC.portfolio_code,
l_debit_amount,
l_credit_amount,
Q5_REC.ccid,
Q5_REC.amount_type,
G_user,
trunc(SYSDATE),
l_curr,
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q5_REC.date_type,
Q5_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
date_type,
action_code)
Values
(Q5_REC.batch_id,
Q5_REC.company_code,
nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
Q5_REC.deal_number,
nvl(Q5_REC.trans_number,999),
Q5_REC.deal_type,
Q5_REC.deal_subtype,
Q5_REC.product_type,
Q5_REC.portfolio_code,
-- decode(l_amount_type,'CCYREAL',0,'CCYAMRL',0,decode(l_dr_or_cr,'DR',l_reval_amt,0)),
-- decode(l_amount_type,'CCYREAL',0,'CCYAMRL',0,decode(l_dr_or_cr,'CR',l_reval_amt,0)),
decode(Q5_REC.amount_type,'CCYREAL',decode(Q5_REC.deal_type,'FX',
decode(Q5_REC.credit_or_debit,'DR',Q5_REC.amount,0),0),'CCYAMRL',0,
decode(Q5_REC.credit_or_debit,'DR',Q5_REC.amount,0)),
decode(Q5_REC.amount_type,'CCYREAL',decode(Q5_REC.deal_type,'FX',
decode(Q5_REC.credit_or_debit,'CR',Q5_REC.amount,0),0),'CCYAMRL',0,
decode(Q5_REC.credit_or_debit,'CR',Q5_REC.amount,0)),
Q5_REC.ccid,
Q5_REC.amount_type,
G_user,
trunc(SYSDATE),
decode(Q5_REC.amount_type, 'CCYREAL', decode(Q5_REC.deal_type, 'FX', Q5_REC.reval_currency, Q5_REC.deal_currency),
'CCYAMRL',Q5_REC.deal_currency,Q5_REC.reval_currency), -- bug 2376980
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q5_REC.date_type,
Q5_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
select currency_buy , currency_sell
into l_curr_buy, l_curr_sell
from
xtr_deals where deal_no = Q6_REC.deal_number;
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
date_type,
action_code)
Values
(Q6_REC.batch_id,
Q6_REC.company_code,
nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
Q6_REC.deal_number,
nvl(Q6_REC.trans_number,999),
Q6_REC.deal_type,
Q6_REC.deal_subtype,
Q6_REC.product_type,
Q6_REC.portfolio_code,
l_debit_amount,
l_credit_amount,
Q6_REC.ccid,
Q6_REC.amount_type,
G_user,
trunc(SYSDATE),
l_curr,
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q6_REC.date_type,
Q6_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
date_type,
action_code)
Values
(Q6_REC.batch_id,
Q6_REC.company_code,
nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
Q6_REC.deal_number,
nvl(Q6_REC.trans_number,999),
Q6_REC.deal_type,
Q6_REC.deal_subtype,
Q6_REC.product_type,
Q6_REC.portfolio_code,
decode(Q6_REC.credit_or_debit,'DR',Q6_REC.amount,0),
decode(Q6_REC.credit_or_debit,'CR',Q6_REC.amount,0),
Q6_REC.ccid,
Q6_REC.amount_type,
G_user,
trunc(SYSDATE),
decode(Q6_REC.amount_type, 'CCYREAL', Q6_REC.deal_currency,
Q6_REC.reval_currency),
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q6_REC.date_type,
Q6_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
Insert into XTR_JOURNALS
(batch_id,
company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
product_type,
portfolio_code,
debit_amount,
credit_amount,
code_combination_id,
amount_type,
created_by,
created_on,
currency,
set_of_books_id,
suspense_gl,
accounted_dr,
accounted_cr,
date_type,
action_code)
Values
(Q7_REC.batch_id,
Q7_REC.company_code,
nvl(Q7_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
nvl(Q7_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
Q7_REC.deal_number,
nvl(Q7_REC.trans_number,999),
Q7_REC.deal_type,
Q7_REC.deal_subtype,
Q7_REC.product_type,
Q7_REC.portfolio_code,
decode(Q7_REC.credit_or_debit,'DR',Q7_REC.amount,0),
decode(Q7_REC.credit_or_debit,'CR',Q7_REC.amount,0),
Q7_REC.ccid,
Q7_REC.amount_type,
G_user,
trunc(SYSDATE),
decode(Q7_REC.amount_type, 'CCYREAL', Q7_REC.deal_currency,
Q7_REC.reval_currency),
G_set_of_books_id,
l_suspense_gl,
l_accounted_dr,
l_accounted_cr,
Q7_REC.date_type,
Q7_REC.action_code);
FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
-- If entire batch was successful, insert XTR_BATCH_EVENTS to add one more row to
-- indicate journal batch has been generated.
---------------------------------------------------------------------------------------
--1336492 If (nvl(l_error_flag,'N') = 'N') then
If (nvl(l_ret_value,0) < 2) then
-- Bug 3805480 begin
-- Added condition to check for RA batch w/o jrnl entries.
-- In such an event, do not create records in xtr_batches and
-- xtr_batch_events table for it.
If (nvl(in_source_option,'R') = 'J') then
l_empty := 0;
Insert into XTR_BATCH_EVENTS
(batch_event_id,
batch_id,
event_code,
authorized,
authorized_by,
authorized_on,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values (l_event_id,
in_batch_id,
'JRNLGN',
'N',
null,
null,
fnd_global.user_id,
l_sysdate,
fnd_global.user_id,
l_sysdate,
fnd_global.login_id);
FND_MESSAGE.Set_Token ('EVENT','Inserting batch event record into table');
Update xtr_batches
set period_start = l_nra_period_from
where batch_id = in_batch_id;
| Update_Journals |
| |
| DESCRIPTION |
| Procedure to delete or reverse journal entries when deals are |
| cancelled |
| CALLED BY |
| Various DB triggers in xtrtrigg.sql |
| XTRINFXR.fmb |
| PARAMETERS |
| l_deal_nos deal number. (required) |
| l_trans_nos transaction number. (required) |
| l_deal_type deal type. (required) |
| HISTORY |
| |
| NOTES |
| Possible permetations of the JNL_REVERSAL_IND, |
| TRANSFER_TO_EXTERNAL_GL, and CANCELLED_IN_GL fields in |
| XTR_JOURNALS Table. |
| |
| Reversal Transfer to Cancelled |
| Indic External G/L in G/L Description |
| --------- ------------ --------- -------------------------------- |
| NULL NULL NULL Journal was created but has not |
| been posted to the G/L. |
| C XX-XXX-XX Y A Transaction was cancelled, the |
| journal has been transferred to |
| the G/L and it has been reversed |
| by creation of a reversal entry |
| in XTR_JOURNALS. |
R NULL NULL Reversal entry created as a |
| result of a deal cancellation or |
| closure. Ineligible for journal |
| clearing. |
| R XX-XXX-XX NULL Reveral entry that has been |
| transferred to the G/L. |
--------------------------------------------------------------------- */
PROCEDURE UPDATE_JOURNALS (l_deal_nos IN NUMBER,
l_trans_nos IN NUMBER,
l_deal_type IN VARCHAR2) is
--
CURSOR SEL_JNL is
select company_code,
journal_date,
orig_journal_date,
deal_number,
transaction_number,
deal_type,
deal_subtype,
amount_type,
debit_amount,
credit_amount,
code_combination_id,
comments,
jnl_reversal_ind,
cancelled_in_gl,
created_by,
created_on,
updated_by,
updated_on,
product_type,
portfolio_code,
audit_indicator,
currency,
transfer_to_external_gl,
rowid,
suspense_gl,
accounted_cr,
accounted_dr,
set_of_books_id
from XTR_JOURNALS
where deal_number=l_deal_nos
and transaction_number=l_trans_nos
and deal_type=l_deal_type;
select dealer_code
from xtr_dealer_codes_v
where user_id = fnd_user_id;
delete from XTR_journals
where rowid=jnl_rec.rowid;
INSERT into XTR_JOURNALS
(COMPANY_CODE,
JOURNAL_DATE,
ORIG_JOURNAL_DATE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
DEAL_TYPE,
DEAL_SUBTYPE,
AMOUNT_TYPE,
DEBIT_AMOUNT,
CREDIT_AMOUNT,
CODE_COMBINATION_ID,
COMMENTS,
CREATED_BY,
CREATED_ON,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
SET_OF_BOOKS_ID,
SUSPENSE_GL,
ACCOUNTED_CR,
ACCOUNTED_DR,
JNL_REVERSAL_IND)
Values
(jnl_rec.COMPANY_CODE,
jnl_rec.JOURNAL_DATE,
jnl_rec.ORIG_JOURNAL_DATE,
jnl_rec.DEAL_NUMBER,
jnl_rec.TRANSACTION_NUMBER,
jnl_rec.DEAL_TYPE,
jnl_rec.DEAL_SUBTYPE,
jnl_rec.AMOUNT_TYPE,
jnl_rec.DEBIT_AMOUNT,
jnl_rec.CREDIT_AMOUNT,
jnl_rec.CODE_COMBINATION_ID,
comment_msg,
G_user,
SYSDATE,
jnl_rec.PRODUCT_TYPE,
jnl_rec.PORTFOLIO_CODE,
jnl_rec.CURRENCY,
jnl_rec.SET_OF_BOOKS_ID,
jnl_rec.SUSPENSE_GL,
jnl_rec.ACCOUNTED_CR,
jnl_rec.ACCOUNTED_DR,
'R');
UPDATE XTR_JOURNALS
set JNL_REVERSAL_IND = 'C',
CANCELLED_IN_GL = 'Y'
where ROWID = jnl_rec.ROWID;
END UPDATE_JOURNALS;
Select party_code, set_of_books_id
from xtr_parties_v
where party_type = 'C'
and (party_code = nvl(p_company_code, party_code));
Select batch_id
from xtr_batches
where batch_id between nvl(p_batch_id_from, batch_id) and nvl(p_batch_id_to, batch_id)
and batch_id in (Select BA.batch_id
from xtr_batches BA,
xtr_batch_events BE
where BA.batch_id = BE.batch_id
and BE.event_code = 'JRNLGN'
and BA.company_code = l_company_code
and BA.upgrade_batch in ('I','N')
and ((p_source_option = 'J' and BA.batch_type = 'J') or
(p_source_option is null and BA.batch_type is null))
and ((nvl(p_incl_transferred,'N') = 'N' and BA.gl_group_id is null) or
(nvl(p_incl_transferred,'N') = 'Y')))
order by period_end, batch_id asc;
Select batch_id
from xtr_batches
where period_end <= nvl(l_cutoff_date, sysdate)
and batch_id in (Select BA.batch_id
from xtr_batches BA,
xtr_batch_events BE
where BA.batch_id = BE.batch_id
and BE.event_code = 'JRNLGN'
and BA.company_code = l_company_code
and BA.upgrade_batch in ('I','N')
and ((p_source_option = 'J' and BA.batch_type = 'J') or
(p_source_option is null and BA.batch_type is null))
and ((nvl(p_incl_transferred,'N') = 'N' and BA.gl_group_id is null) or
(nvl(p_incl_transferred,'N') = 'Y')))
order by period_end, batch_id asc;
select max(journal_date)
from xtr_journals
where batch_id = l_next_bid;
select min(journal_date)
from xtr_journals
where batch_id = l_next_bid;
select period_name
from gl_periods per,
gl_sets_of_books sob,
xtr_parties_v pty
where pty.party_code = l_company_code
and sob.set_of_books_id = pty.set_of_books_id
and sob.period_set_name = per.period_set_name
and sob.accounted_period_type = per.period_type
and p_journal_date between per.start_date and per.end_date
and per.adjustment_period_flag = 'N';