eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.XTR_REVAL_PROCESS_P dependencies on XTR_DEALS

Line 117: from XTR_DEALS

113: --
114: /*
115: cursor GET_BOND_ISSUE is
116: select distinct currency,bond_issue
117: from XTR_DEALS
118: where deal_type='BOND'
119: and status_code='CURRENT'
120: and maturity_date >l_end_date;
121:

Line 552: and deal_no in (select deal_no from xtr_deals where deal_type = 'RTMM'

548:
549: Cursor c_rtmm_deal is
550: Select * from XTR_RTMM_ELIGIBLE_DEALS_V
551: where eligible_date <= l_batch_end and company_code = l_company_code
552: and deal_no in (select deal_no from xtr_deals where deal_type = 'RTMM'
553: and last_reval_batch_id is null);
554:
555: Cursor c_stock_deal is
556: select * from XTR_STOCK_ELIGIBLE_DEALS_V

Line 735: from XTR_DEALS

731: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
732: if rec.status_code = 'CLOSED' then -- this deal is predeliever/rollover
733: select profit_loss, fx_ro_pd_rate
734: into rel_pl_value, l_fx_rate
735: from XTR_DEALS
736: where deal_no = rec.deal_no;
737:
738: rec.fair_value := rel_pl_value + rec.init_fv;
739: rec.reval_rate := l_fx_rate;

Line 946: from XTR_DEALS

942:
943: if l_close_no is NOT NULL then
944: select decode(l_accounting, 'TRADE', deal_date, start_date)
945: into l_reneg_date
946: from XTR_DEALS
947: where deal_no = l_close_no;
948: end if;
949:
950: xtr_revl_get_fairvalue(rec, l_fv, retcode);

Line 966: from XTR_DEALS

962: rec.fair_value := rel_pl_value + rec.init_fv;
963: -- Insert reval rate equal to new resale deal's rate
964: select interest_rate
965: into rec.reval_rate
966: from XTR_DEALS
967: where deal_no = (select trans_closeout_no
968: from XTR_ROLLOVER_TRANSACTIONS
969: where deal_number = rec.deal_no
970: and transaction_number = rec.trans_no);

Line 1432: from XTR_DEALS

1428: rec.init_fv := 0;
1429: else
1430: select initial_fair_value
1431: into rec.init_fv
1432: from XTR_DEALS
1433: where deal_no = rec.deal_no;
1434: end if;
1435:
1436: if rec.deal_type = 'STOCK' then

Line 1516: l_knock_type XTR_DEALS.knock_type%TYPE;

1512: l_market_set VARCHAR2(30);
1513: l_ric_code XTR_BOND_ISSUES.ric_code%TYPE;
1514: l_dummy NUMBER;
1515: l_dummy1 NUMBER;
1516: l_knock_type XTR_DEALS.knock_type%TYPE;
1517: l_knock_date DATE;
1518: l_strike_price NUMBER;
1519: l_base_ccy VARCHAR2(15);
1520: l_contra_ccy VARCHAR2(15);

Line 1574: from xtr_deals

1570: elsif rec.effective_date <= rec.revldate and rec.status_code = 'EXERCISED' then -- realized
1571: if rec.settle_amount is not null then -- Cash settlement
1572: select exercise_price
1573: into rec.reval_rate
1574: from xtr_deals
1575: where deal_no = rec.deal_no;
1576:
1577: fair_value := rec.settle_amount;
1578: else

Line 1581: from xtr_deals

1577: fair_value := rec.settle_amount;
1578: else
1579: select base_rate, capital_price -- Get exercise price for BDO
1580: into rec.reval_rate, l_strike_price
1581: from xtr_deals
1582: where deal_no = rec.deal_no;
1583:
1584: fair_value := round((rec.face_value * (rec.reval_rate - l_strike_price) /100), l_round);
1585: if rec.deal_subtype in ('BFLOOR', 'SCAP') then

Line 1612: from XTR_DEALS

1608: elsif rec.effective_date <= rec.revldate and rec.status_code = 'EXERCISED' then -- realized
1609: if rec.settle_amount is not null then -- Cash settlement
1610: select settle_rate
1611: into rec.reval_rate
1612: from XTR_DEALS
1613: where deal_no = rec.deal_no;
1614: else -- Create Interest Swap, the reval rate is equal to the interest rate
1615: -- on Pay or Rec side of created swap(other side from Swaption 'Action')
1616: select interest_rate

Line 1618: from XTR_DEALS

1614: else -- Create Interest Swap, the reval rate is equal to the interest rate
1615: -- on Pay or Rec side of created swap(other side from Swaption 'Action')
1616: select interest_rate
1617: into rec.reval_rate
1618: from XTR_DEALS
1619: where int_swap_ref = (select swap_ref from XTR_DEALS
1620: where deal_no = rec.deal_no)
1621: and deal_subtype = (select decode(D.coupon_action, 'REC', 'FUND', 'INVEST')
1622: from XTR_DEALS D where d.deal_no = rec.deal_no);

Line 1619: where int_swap_ref = (select swap_ref from XTR_DEALS

1615: -- on Pay or Rec side of created swap(other side from Swaption 'Action')
1616: select interest_rate
1617: into rec.reval_rate
1618: from XTR_DEALS
1619: where int_swap_ref = (select swap_ref from XTR_DEALS
1620: where deal_no = rec.deal_no)
1621: and deal_subtype = (select decode(D.coupon_action, 'REC', 'FUND', 'INVEST')
1622: from XTR_DEALS D where d.deal_no = rec.deal_no);
1623: end if;

Line 1622: from XTR_DEALS D where d.deal_no = rec.deal_no);

1618: from XTR_DEALS
1619: where int_swap_ref = (select swap_ref from XTR_DEALS
1620: where deal_no = rec.deal_no)
1621: and deal_subtype = (select decode(D.coupon_action, 'REC', 'FUND', 'INVEST')
1622: from XTR_DEALS D where d.deal_no = rec.deal_no);
1623: end if;
1624: xtr_end_fv(rec, fair_value);
1625: else
1626: fair_value := null; -- user need to provide unrealized Fair value

Line 1649: from xtr_deals

1645: FND_FILE.put_line(fnd_file.log, l_buf);
1646: elsif rec.effective_date <= rec.revldate and rec.status_code = 'SETTLED' then -- realized
1647: select settle_rate
1648: into rec.reval_rate
1649: from xtr_deals
1650: where deal_no = rec.deal_no;
1651:
1652: xtr_end_fv(rec, fair_value); -- realized, fair value = ending fair value
1653: else -- unrealized. Using formula

Line 1706: from XTR_DEALS

1702: /************** FXO fair value *******************/
1703: elsif rec.deal_type = 'FXO' then
1704: select knock_type, knock_execute_date
1705: into l_knock_type, l_knock_date
1706: from XTR_DEALS
1707: where deal_no = rec.deal_no;
1708:
1709: if rec.effective_date <= rec.revldate and rec.status_code = 'CURRENT' then
1710: -- This deal should be mature, but not settled yet, create unrealized record.

Line 1739: from xtr_deals

1735: fair_value := 0;
1736: elsif rec.effective_date <= rec.revldate and rec.status_code = 'EXERCISED' then -- realized
1737: select base_rate
1738: into rec.reval_rate
1739: from xtr_deals
1740: where deal_no = (select fxo_deal_no
1741: from xtr_deals
1742: where deal_no = rec.deal_no);
1743: xtr_end_fv(rec, fair_value);

Line 1741: from xtr_deals

1737: select base_rate
1738: into rec.reval_rate
1739: from xtr_deals
1740: where deal_no = (select fxo_deal_no
1741: from xtr_deals
1742: where deal_no = rec.deal_no);
1743: xtr_end_fv(rec, fair_value);
1744: else -- calcualte unrealized fair value using formula
1745: if (l_knock_type = 'I' and l_knock_date is null) or -- Not yet knock-in. fair value is 0

Line 1799: from xtr_deals

1795: fair_value := 0;
1796: elsif rec.effective_date <= rec.revldate and rec.status_code = 'EXERCISED' then -- realized
1797: select settle_rate
1798: into rec.reval_rate
1799: from xtr_deals
1800: where deal_no = rec.deal_no;
1801: xtr_end_fv(rec, fair_value); -- realized, fair value = ending fair value
1802: else -- unrealized. Using formula
1803: if rec.pricing_model = 'BLACK' then

Line 2047: from XTR_DEALS

2043: where principal_action = 'INCRSE'
2044: and DEAL_NUMBER = rec.deal_no
2045: and START_DATE <= rec.revldate
2046: and start_date <> (select start_date
2047: from XTR_DEALS
2048: where deal_no = rec.deal_no);
2049:
2050: select sum(PRINCIPAL_ADJUST)
2051: into l_de_pri_adjust

Line 2057: from XTR_DEALS

2053: where principal_action = 'DECRSE'
2054: and DEAL_NUMBER = rec.deal_no
2055: and START_DATE <= rec.revldate
2056: and start_date <> (select maturity_date
2057: from XTR_DEALS
2058: where deal_no = rec.deal_no);
2059:
2060: l_pri_adjust := nvl(l_in_pri_adjust,0) - nvl(l_de_pri_adjust,0);
2061: else

Line 2070: from XTR_DEALS

2066: and DEAL_NUMBER = rec.deal_no
2067: and START_DATE <= rec.revldate
2068: and START_DATE >= rec.period_start
2069: and start_date <> (select start_date
2070: from XTR_DEALS
2071: where deal_no = rec.deal_no);
2072:
2073: select sum(PRINCIPAL_ADJUST)
2074: into l_de_pri_adjust

Line 2081: from XTR_DEALS

2077: and DEAL_NUMBER = rec.deal_no
2078: and START_DATE <= rec.revldate
2079: and START_DATE >= rec.period_start
2080: and start_date <> (select maturity_date
2081: from XTR_DEALS
2082: where deal_no = rec.deal_no);
2083:
2084: l_pri_adjust := nvl(l_in_pri_adjust,0) - nvl(l_de_pri_adjust,0);
2085: end if;

Line 2109: from XTR_DEALS

2105: where principal_action = 'INCRSE'
2106: and DEAL_NUMBER = rec.deal_no
2107: and START_DATE <= rec.revldate
2108: and start_date <> (select start_date
2109: from XTR_DEALS
2110: where deal_no = rec.deal_no);
2111:
2112: select sum(PRINCIPAL_ADJUST)
2113: into l_de_pri_adjust

Line 2119: from XTR_DEALS

2115: where principal_action = 'DECRSE'
2116: and DEAL_NUMBER = rec.deal_no
2117: and START_DATE <= rec.revldate
2118: and start_date <> (select maturity_date
2119: from XTR_DEALS
2120: where deal_no = rec.deal_no);
2121:
2122: select sum(pi_amount_received)
2123: into l_recon

Line 2145: from XTR_DEALS

2141: and DEAL_NUMBER = rec.deal_no
2142: and START_DATE <= rec.revldate
2143: and START_DATE >= rec.period_start
2144: and start_date <> (select start_date
2145: from XTR_DEALS
2146: where deal_no = rec.deal_no);
2147: select sum(PRINCIPAL_ADJUST)
2148: into l_de_pri_adjust
2149: from xtr_rollover_transactions

Line 2155: from XTR_DEALS

2151: and DEAL_NUMBER = rec.deal_no
2152: and START_DATE <= rec.revldate
2153: and START_DATE >= rec.period_start
2154: and start_date <> (select maturity_date
2155: from XTR_DEALS
2156: where deal_no = rec.deal_no);
2157:
2158: select sum(pi_amount_received)
2159: into l_recon

Line 2251: /* XTR_DEALS */

2247: end xtr_revl_get_unrel_pl;
2248: --------------------------------------------------------------
2249: /*************************************************************************************/
2250: /* This procedure gets all rates information for XTR_REVALUATION_DETAILS and */
2251: /* XTR_DEALS */
2252: /* For Revaluation table: exchange_rate_one = reval_ccy/SOB ccy on revaluation date */
2253: /* except for FX, exchange_rate_one = base_ccy/SOB ccy on revaluation date */
2254: /* exchange_rate_two for FX = contra_ccy/SOB ccy on revaluation date */
2255: /* ctr_curr_sob_curr_fwd_rate = contra_ccy/SOB FX forward rate */

Line 2290: -- Get XTR_DEALS table exchange_rate_one and exchange_rate_two

2286: rec.deal_ex_rate_two :=NULL;
2287: rec.reval_ex_rate_one :=NULL;
2288: rec.reval_ex_rate_two :=NULL;
2289:
2290: -- Get XTR_DEALS table exchange_rate_one and exchange_rate_two
2291: If rec.deal_type = 'FX' then
2292: GL_CURRENCY_API.get_triangulation_rate
2293: (l_base_ccy, rec.sob_ccy, rec.eligible_date, rec.ex_rate_type,
2294: l_deno, l_numer, rec.deal_ex_rate_one);

Line 4161: from XTR_DEALS

4157: and transaction_number = rec.trans_no;
4158: else
4159: select initial_fair_value
4160: into p_fair_value
4161: from XTR_DEALS
4162: where deal_no = rec.deal_no;
4163: end if;
4164: p_reval_rate := rec.transaction_rate;
4165: p_cumm_unrel_gl := 0;

Line 4233: from xtr_deals

4229:
4230: elsif rec.deal_type = 'IRS' then
4231: select discount
4232: into l_discount
4233: from xtr_deals
4234: where deal_no = rec.deal_no;
4235:
4236: if nvl(l_discount, 'N') = 'N' then -- No principal cashflow, IFV = 0
4237: init_fv := 0;

Line 4241: from xtr_deals

4237: init_fv := 0;
4238: else
4239: select face_value_amount, deal_subtype
4240: into init_fv, l_deal_subtype
4241: from xtr_deals
4242: where deal_no = rec.deal_no;
4243:
4244: if l_deal_subtype = 'FUND' then
4245: init_fv := init_fv * (-1);

Line 4253: from xtr_deals

4249: elsif rec.deal_type = 'FX' then
4250: if l_inclu_cost = 'Y' then
4251: select brokerage_amount
4252: into init_fv
4253: from xtr_deals
4254: where deal_no = rec.deal_no;
4255: else
4256: init_fv := 0;
4257: end if;

Line 4264: from xtr_deals

4260: -- For these Options, no transaction cost column shown on form,
4261: -- so we don't need to consider for now.
4262: select premium_action, premium_amount
4263: into l_pre_action, l_pre_amt
4264: from xtr_deals
4265: where deal_no = rec.deal_no;
4266:
4267: if l_pre_action = 'PAY' then
4268: init_fv := l_pre_amt;

Line 4290: from xtr_deals

4286: elsif rec.deal_type = 'BOND' then
4287: select maturity_amount, brokerage_amount,
4288: nvl(base_rate, capital_price), deal_subtype
4289: into l_face_value, l_brk_amt, l_int_rate, l_deal_subtype
4290: from xtr_deals
4291: where deal_no = rec.deal_no;
4292:
4293: if l_inclu_cost = 'Y' then
4294: init_fv := (l_face_value * l_int_rate)/100

Line 4307: from XTR_DEALS

4303:
4304: elsif rec.deal_type = 'STOCK' then
4305: select start_amount, brokerage_amount
4306: into l_face_value, l_brk_amt
4307: from XTR_DEALS
4308: where deal_no = rec.deal_no;
4309:
4310: if l_inclu_cost = 'Y' then
4311: init_fv := l_face_value + nvl(l_brk_amt, 0);

Line 4319: from xtr_deals

4315:
4316: elsif rec.deal_type in ('RTMM', 'TMM') then
4317: select face_value_amount, brokerage_amount, deal_subtype
4318: into l_face_value, l_brk_amt, l_deal_subtype
4319: from xtr_deals
4320: where deal_no = rec.deal_no;
4321:
4322: if l_inclu_cost = 'Y' then
4323: init_fv := l_face_value + nvl(l_brk_amt, 0);

Line 4377: p_deal_no in XTR_DEALS.DEAL_NO%TYPE,

4373:
4374: -------------------------------------------------------------------
4375: Procedure xtr_ins_init_fv
4376: (p_company_code in XTR_PARTY_INFO.PARTY_CODE%TYPE,
4377: p_deal_no in XTR_DEALS.DEAL_NO%TYPE,
4378: p_deal_type in XTR_DEALS.DEAL_TYPE%TYPE,
4379: p_transaction_no in XTR_DEALS.TRANSACTION_NO%TYPE,
4380: p_day_count_type in XTR_DEALS.DAY_COUNT_TYPE%TYPE
4381: )

Line 4378: p_deal_type in XTR_DEALS.DEAL_TYPE%TYPE,

4374: -------------------------------------------------------------------
4375: Procedure xtr_ins_init_fv
4376: (p_company_code in XTR_PARTY_INFO.PARTY_CODE%TYPE,
4377: p_deal_no in XTR_DEALS.DEAL_NO%TYPE,
4378: p_deal_type in XTR_DEALS.DEAL_TYPE%TYPE,
4379: p_transaction_no in XTR_DEALS.TRANSACTION_NO%TYPE,
4380: p_day_count_type in XTR_DEALS.DAY_COUNT_TYPE%TYPE
4381: )
4382: IS

Line 4379: p_transaction_no in XTR_DEALS.TRANSACTION_NO%TYPE,

4375: Procedure xtr_ins_init_fv
4376: (p_company_code in XTR_PARTY_INFO.PARTY_CODE%TYPE,
4377: p_deal_no in XTR_DEALS.DEAL_NO%TYPE,
4378: p_deal_type in XTR_DEALS.DEAL_TYPE%TYPE,
4379: p_transaction_no in XTR_DEALS.TRANSACTION_NO%TYPE,
4380: p_day_count_type in XTR_DEALS.DAY_COUNT_TYPE%TYPE
4381: )
4382: IS
4383: --

Line 4380: p_day_count_type in XTR_DEALS.DAY_COUNT_TYPE%TYPE

4376: (p_company_code in XTR_PARTY_INFO.PARTY_CODE%TYPE,
4377: p_deal_no in XTR_DEALS.DEAL_NO%TYPE,
4378: p_deal_type in XTR_DEALS.DEAL_TYPE%TYPE,
4379: p_transaction_no in XTR_DEALS.TRANSACTION_NO%TYPE,
4380: p_day_count_type in XTR_DEALS.DAY_COUNT_TYPE%TYPE
4381: )
4382: IS
4383: --
4384: -- Purpose: This Procedure inserts the INITIAL_FAIR_VALUE into the

Line 4385: -- tables XTR_DEALS or XTR_ROLLOVER_TRANSACTIONS or others depending

4381: )
4382: IS
4383: --
4384: -- Purpose: This Procedure inserts the INITIAL_FAIR_VALUE into the
4385: -- tables XTR_DEALS or XTR_ROLLOVER_TRANSACTIONS or others depending
4386: -- on the Deal Type.This will be callled from the forms either after
4387: -- the POST-DATA-BASE-COMMIT Trigger or POST-INSERT Triggers or other
4388: -- appropriate triggers.
4389: -- This procedure uses xtr_reval_process_p.xtr_init_fv() for getting

Line 4419: update xtr_deals set initial_fair_value = fv

4415:
4416: If p_deal_type in ('FRA', 'IRS','FX','BDO', 'FXO', 'IRO', 'STOCK',
4417: 'SWPTN','BOND','RTMM', 'TMM') then
4418: If p_transaction_no is NOT NULL then
4419: update xtr_deals set initial_fair_value = fv
4420: where deal_no = p_deal_no and
4421: deal_type = p_deal_type and
4422: transaction_no = p_transaction_no and
4423: company_code = p_company_code;

Line 4425: update xtr_deals set initial_fair_value = fv

4421: deal_type = p_deal_type and
4422: transaction_no = p_transaction_no and
4423: company_code = p_company_code;
4424: Else
4425: update xtr_deals set initial_fair_value = fv
4426: where deal_no = p_deal_no and
4427: deal_type = p_deal_type and
4428: company_code = p_company_code;
4429: End If;

Line 4459: from xtr_deals D,

4455: -------------------------------------------------------
4456: select rt.balance_out, nvl(rt.brokerage_amount, 0), d.year_calc_type,
4457: d.calc_basis, rt.start_date, rt.maturity_date
4458: into l_face_value, l_brk_amt, l_year_calc_type, l_disc_yield, l_start_date, l_end_date
4459: from xtr_deals D,
4460: xtr_rollover_transactions RT
4461: where D.deal_no = p_deal_no
4462: and D.deal_no = RT.deal_number
4463: and RT.transaction_number = p_transaction_no;

Line 4482: from xtr_deals D, xtr_rollover_transactions RT

4478: select rt.balance_out, nvl(rt.brokerage_amount, 0), d.year_calc_type,
4479: d.calc_basis, rt.start_date, rt.maturity_date
4480: into l_face_value, l_brk_amt, l_year_calc_type, l_disc_yield,
4481: l_start_date, l_end_date
4482: from xtr_deals D, xtr_rollover_transactions RT
4483: where D.deal_no = p_deal_no
4484: and D.deal_no = RT.deal_number
4485: and RT.transaction_number = p_transaction_no;
4486:

Line 4660: from XTR_DEALS

4656: if rec.cap_or_floor = 'PAY' then -- paying leg is the default leg of Swap deal
4657: select decode(discount,'Y', (initial_fair_value - face_value_amount),
4658: initial_fair_value)
4659: into end_fv
4660: from XTR_DEALS
4661: where deal_subtype = 'FUND'
4662: and int_swap_ref = (select swap_ref
4663: from XTR_DEALS
4664: where deal_no = rec.deal_no);

Line 4663: from XTR_DEALS

4659: into end_fv
4660: from XTR_DEALS
4661: where deal_subtype = 'FUND'
4662: and int_swap_ref = (select swap_ref
4663: from XTR_DEALS
4664: where deal_no = rec.deal_no);
4665: else -- 'REC'
4666: select decode(discount,'Y', (initial_fair_value - face_value_amount),
4667: initial_fair_value)

Line 4669: from XTR_DEALS

4665: else -- 'REC'
4666: select decode(discount,'Y', (initial_fair_value - face_value_amount),
4667: initial_fair_value)
4668: into end_fv
4669: from XTR_DEALS
4670: where deal_subtype = 'INVEST'
4671: and int_swap_ref = (select swap_ref
4672: from XTR_DEALS
4673: where deal_no = rec.deal_no);

Line 4672: from XTR_DEALS

4668: into end_fv
4669: from XTR_DEALS
4670: where deal_subtype = 'INVEST'
4671: and int_swap_ref = (select swap_ref
4672: from XTR_DEALS
4673: where deal_no = rec.deal_no);
4674: End if;
4675: End if;
4676:

Line 4801: /* XTR_DEALS table. */

4797: /****************************************************************/
4798: /* This procedure get forward rate for the following deal types:*/
4799: /* FRA, IRO, IRS, and TMM */
4800: /* For FRA and IRO, start_date and maturity_date come from */
4801: /* XTR_DEALS table. */
4802: /* For TMM and IRS, start_date and maturity_date come from */
4803: /* XTR_ROLLOVER_TRANSACTIONS table */
4804: /****************************************************************/
4805: PROCEDURE xtr_revl_getprice_fwd(

Line 5642: -- store contra -> SOB currency as exchange_rate_two in xtr_deals table

5638: /* --------------------------------------------------------------------
5639: This part is for later use if we decide to change the definition of exchange rate
5640: ------------------------------------------------------------
5641: -- Store base-> SOB currency as exchange_rate_one and
5642: -- store contra -> SOB currency as exchange_rate_two in xtr_deals table
5643: ------------------------------------------------------------
5644: GL_CURRENCY_API.get_triangulation_rate(l_base_ccy, rec.sob_ccy,
5645: l_begin_date, rec.ex_rate_type,l_deno, l_numer, rec.deal_ex_rate_one);
5646:

Line 7191: from XTR_DEALS

7187: TRANSACTION_NUMBER = rec.trans_no;
7188:
7189: select MARGIN, CALC_BASIS
7190: into l_margin, l_disc_yield_basis
7191: from XTR_DEALS
7192: where deal_no = rec.deal_no;
7193:
7194: l_int_rate := rec.transaction_rate;
7195: l_market_set := rec.MARKET_DATA_SET;

Line 7413: l_bond_issue XTR_DEALS.bond_issue%TYPE;

7409: /*********************************************************/
7410: PROCEDURE xtr_revl_getprice_bond(
7411: rec IN xtr_revl_rec,
7412: p_bond_clean_price OUT NOCOPY NUMBER) IS
7413: l_bond_issue XTR_DEALS.bond_issue%TYPE;
7414: l_ric_code XTR_BOND_ISSUES.ric_code%TYPE;
7415: l_margin XTR_DEALS.margin%TYPE;
7416: l_coupon_action XTR_DEALS.coupon_action%TYPE;
7417: l_side VARCHAR2(1);

Line 7415: l_margin XTR_DEALS.margin%TYPE;

7411: rec IN xtr_revl_rec,
7412: p_bond_clean_price OUT NOCOPY NUMBER) IS
7413: l_bond_issue XTR_DEALS.bond_issue%TYPE;
7414: l_ric_code XTR_BOND_ISSUES.ric_code%TYPE;
7415: l_margin XTR_DEALS.margin%TYPE;
7416: l_coupon_action XTR_DEALS.coupon_action%TYPE;
7417: l_side VARCHAR2(1);
7418: l_bond_yield_m NUMBER;
7419: l_yield NUMBER;

Line 7416: l_coupon_action XTR_DEALS.coupon_action%TYPE;

7412: p_bond_clean_price OUT NOCOPY NUMBER) IS
7413: l_bond_issue XTR_DEALS.bond_issue%TYPE;
7414: l_ric_code XTR_BOND_ISSUES.ric_code%TYPE;
7415: l_margin XTR_DEALS.margin%TYPE;
7416: l_coupon_action XTR_DEALS.coupon_action%TYPE;
7417: l_side VARCHAR2(1);
7418: l_bond_yield_m NUMBER;
7419: l_yield NUMBER;
7420: l_accrued_interest NUMBER;

Line 7446: from XTR_DEALS

7442: End if;
7443:
7444: select MARGIN, COUPON_ACTION
7445: into l_margin, l_coupon_action
7446: from XTR_DEALS
7447: where DEAL_NO = rec.deal_no;
7448:
7449: select RIC_CODE
7450: into l_ric_code

Line 7617: XTR_DEALS D

7613: decode(cp.parameter_value_code, 'TRADE', bond.cross_ref_deal_date,
7614: bond.cross_ref_start_date) resale_rec_date
7615: from XTR_BOND_ALLOC_DETAILS BOND,
7616: XTR_COMPANY_PARAMETERS CP,
7617: XTR_DEALS D
7618: Where bond.deal_no = rec.deal_no
7619: and bond.deal_no = d.deal_no
7620: and bond.batch_id is null
7621: and cp.company_code = d.company_code

Line 7644: bo_rec.maturity_face_value := rec.face_value; -- xtr_deals.maturity_amount

7640:
7641: bo_rec.batch_id := rec.batch_id;
7642: xtr_first_reval(rec, l_first);
7643: if l_first = TRUE then -- first time reval
7644: bo_rec.maturity_face_value := rec.face_value; -- xtr_deals.maturity_amount
7645: bo_rec.start_face_value := rec.face_value;
7646: bo_rec.cum_unrel_gl := 0;
7647: bo_rec.cum_unrel_gl_bal := 0;
7648: bo_rec.start_fair_value := rec.init_fv;

Line 7688: update XTR_DEALS

7684: bo_rec.mtm_real, bo_rec.clean_px, bo_rec.resale_rec_date;
7685: End loop;
7686:
7687: if bo_rec.maturity_face_value = 0 then
7688: update XTR_DEALS
7689: set last_reval_batch_id = rec.batch_id
7690: where deal_no = rec.deal_no;
7691: else
7692: l_resale := FALSE;

Line 7700: update XTR_DEALS

7696: -- Bug 2990046 - Issue #6.
7697: -- Corrected oversight from original flow logic to update last batch id
7698: -- of a deal which has matured, making it ineligible for future revaluation batch.
7699:
7700: update XTR_DEALS
7701: set last_reval_batch_id = rec.batch_id
7702: where deal_no = rec.deal_no;
7703:
7704: -- End 2990046 - Issue #6 additions.

Line 7855: rec.fair_value := rec.fxo_sell_ref_amount; -- xtr_deals.maturity_balance_amount

7851: rec.period_end := rec.maturity_date;
7852: rec.effective_date:= rec.maturity_date;
7853: r_rd.transaction_period := rec.period_end - rec.period_start;
7854: rec.reval_rate := 100;
7855: rec.fair_value := rec.fxo_sell_ref_amount; -- xtr_deals.maturity_balance_amount
7856:
7857: if rec.deal_subtype in ('SHORT', 'ISSUE') then
7858: rec.fair_value := rec.fair_value * (-1);
7859: end if;

Line 7867: from xtr_deals

7863: -- last period's realized MTM gain/loss at time of maturity.
7864:
7865: Select nvl(initial_fair_value,rec.fair_value)
7866: into l_full_init_fv
7867: from xtr_deals
7868: where deal_no = rec.deal_no;
7869:
7870: -- End 3046471 additions.
7871:

Line 7884: rec.face_value := rec.fxo_sell_ref_amount; -- xtr_deals.maturity_balance_amount

7880: where deal_no = rec.deal_no;
7881: --3046471 l_begin_fv := rec.init_fv - l_bond_init_fv;
7882: l_begin_fv := l_full_init_fv - l_bond_init_fv; -- 3046471.
7883: end if;
7884: rec.face_value := rec.fxo_sell_ref_amount; -- xtr_deals.maturity_balance_amount
7885: rel_pl_value := rec.fair_value - l_begin_fv;
7886: End if;
7887:
7888: If rec.reval_ccy = rec.sob_ccy then

Line 8040: rec.face_value := rec.fxo_sell_ref_amount; -- xtr_deals.maturity_balance_amount

8036: and deal_no = rec.deal_no
8037: and transaction_no = rec.trans_no
8038: and amount_type = 'REAL';
8039:
8040: rec.face_value := rec.fxo_sell_ref_amount; -- xtr_deals.maturity_balance_amount
8041: rec.fair_value := rec.fxo_sell_ref_amount;
8042: rec.period_end := rec.maturity_date;
8043: unrel_pl_value := l_mtm_real - bo_rec.cum_unrel_gl_bal;
8044: cum_pl_value := l_mtm_real;

Line 8222: from XTR_DEALS_V

8218:
8219: Begin
8220: select day_count_type, rounding_type, prepaid_interest
8221: into l_day_count_type, l_round_type, l_pre_int
8222: from XTR_DEALS_V
8223: where deal_no = rec.deal_no;
8224:
8225: select rounding_factor
8226: into l_round

Line 8667: XTR_DEALS D

8663: stock.price_per_share, decode(cp.parameter_value_code, 'TRADE',
8664: stock.cross_ref_deal_date, stock.cross_ref_start_date) resale_rec_date
8665: from XTR_STOCK_ALLOC_DETAILS STOCK,
8666: XTR_COMPANY_PARAMETERS CP,
8667: XTR_DEALS D
8668: Where stock.deal_no = rec.deal_no
8669: and stock.deal_no = d.deal_no
8670: and stock.batch_id is null
8671: and cp.company_code = d.company_code

Line 8743: update XTR_DEALS

8739: End loop;
8740:
8741: If st_rec.remaining_quantity = 0 and
8742: nvl(st_rec.resale_rec_date, rec.revldate +1) <= rec.revldate then -- totally resale
8743: update XTR_DEALS
8744: set last_reval_batch_id = rec.batch_id
8745: where deal_no = rec.deal_no;
8746: Else
8747: l_resale := FALSE;

Line 9076: l_stock_issue XTR_DEALS.bond_issue%TYPE;

9072: PROCEDURE xtr_revl_getprice_stock(
9073: rec IN xtr_revl_rec,
9074: p_stock_price OUT NOCOPY NUMBER) IS
9075:
9076: l_stock_issue XTR_DEALS.bond_issue%TYPE;
9077: l_ric_code XTR_BOND_ISSUES.ric_code%TYPE;
9078: l_side VARCHAR2(1);
9079: r_md_in xtr_market_data_p.md_from_set_in_rec_type;
9080: r_md_out xtr_market_data_p.md_from_set_out_rec_type;

Line 9373: from XTR_DEALS_V

9369:
9370: select fixed_or_floating_rate, day_count_type, rounding_type, start_date, margin
9371: into l_fix_float, l_day_count_type, l_round_type, l_deal_begin_date,
9372: l_margin -- Bug 3230779
9373: from XTR_DEALS_V
9374: where deal_no = rec.deal_no;
9375:
9376: select max(transaction_number)
9377: into l_max_trans_no

Line 9678: from xtr_rollover_transactions, xtr_deals

9674: where currency = rec.reval_ccy;
9675:
9676: select all_in_rate, rounding_type
9677: into l_all_in_rate, l_rounding_type -- 5130446
9678: from xtr_rollover_transactions, xtr_deals
9679: where deal_number = rec.deal_no
9680: and deal_number= deal_no
9681: and transaction_number = rec.trans_no;
9682:

Line 9838: /* Also update xtr_deals and xtr_rollover_transactions, */

9834:
9835: --------------------------------------------------------
9836: /*****************************************************************/
9837: /* This procedure insert unrealized values into xtr_revaluation_details table*/
9838: /* Also update xtr_deals and xtr_rollover_transactions, */
9839: /* xtr_bank_balance, xtr_intergroup_transfers */
9840: /******************************************************************/
9841: PROCEDURE xtr_revl_unreal_log(
9842: rec IN xtr_revl_rec,

Line 9862: from XTR_DEALS

9858: r_err_log err_log; -- record type
9859:
9860: Cursor CHK_LOCK_DEAL is
9861: select rowid
9862: from XTR_DEALS
9863: where DEAL_NO = rec.deal_no
9864: and DEAL_TYPE not in ('NI', 'ONC', 'CA', 'IG')
9865: and FIRST_REVAL_BATCH_ID is null
9866: for update of FIRST_REVAL_BATCH_ID NOWAIT;

Line 9960: -- For NI and ONC, we insert into Rollover table, other deal types go to XTR_DEALS

9956: X_QUANTITY => rec.quantity
9957: );
9958:
9959: -- Insert FIRST_REVAL_BATCH_ID to deal table for the first time revaluation
9960: -- For NI and ONC, we insert into Rollover table, other deal types go to XTR_DEALS
9961:
9962: Open CHK_LOCK_DEAL;
9963: Fetch CHK_LOCK_DEAL into l_deal_rowid;
9964: if CHK_LOCK_DEAL%FOUND then

Line 9965: Update XTR_DEALS

9961:
9962: Open CHK_LOCK_DEAL;
9963: Fetch CHK_LOCK_DEAL into l_deal_rowid;
9964: if CHK_LOCK_DEAL%FOUND then
9965: Update XTR_DEALS
9966: Set FIRST_REVAL_BATCH_ID = rec.batch_id,
9967: EXCHANGE_RATE_ONE = rec.deal_ex_rate_one,
9968: EXCHANGE_RATE_TWO = rec.deal_ex_rate_two
9969: Where rowid = l_deal_rowid;

Line 10041: /* Also update xtr_deals and xtr_rollover_transactions, */

10037:
10038: --------------------------------------------------------------------
10039: /*****************************************************************/
10040: /* This procedure insert realized values into xtr_revaluation_details table*/
10041: /* Also update xtr_deals and xtr_rollover_transactions, */
10042: /* xtr_bank_balance, xtr_intergroup_transfers */
10043: /******************************************************************/
10044: PROCEDURE xtr_revl_real_log (
10045: rec IN xtr_revl_rec,

Line 10063: from XTR_DEALS

10059: r_err_log err_log; -- record type
10060:
10061: Cursor CHK_LOCK_DEAL is
10062: select rowid
10063: from XTR_DEALS
10064: where DEAL_NO = rec.deal_no
10065: and ((DEAL_TYPE not in ('NI', 'ONC', 'CA', 'IG', 'BOND', 'STOCK','TMM', 'IRS'))
10066: or (DEAL_TYPE in ('TMM', 'IRS') and rec.effective_date <= rec.revldate))
10067: for update of FIRST_REVAL_BATCH_ID NOWAIT;

Line 10144: -- For NI and ONC, we insert into Rollover table, other deal types go to XTR_DEALS

10140: X_QUANTITY => rec.quantity
10141: );
10142:
10143: -- Insert LAST_REVAL_BATCH_ID to deal table once realized G/L has obtained.
10144: -- For NI and ONC, we insert into Rollover table, other deal types go to XTR_DEALS
10145:
10146: Open CHK_LOCK_DEAL;
10147: Fetch CHK_LOCK_DEAL into l_deal_rowid;
10148: if CHK_LOCK_DEAL%FOUND then

Line 10150: Update XTR_DEALS

10146: Open CHK_LOCK_DEAL;
10147: Fetch CHK_LOCK_DEAL into l_deal_rowid;
10148: if CHK_LOCK_DEAL%FOUND then
10149: close CHK_LOCK_DEAL;
10150: Update XTR_DEALS
10151: Set LAST_REVAL_BATCH_ID = rec.batch_id
10152: Where rowid = l_deal_rowid;
10153: else
10154: Close CHK_LOCK_DEAL;

Line 10325: from XTR_DEALS

10321: p_out OUT NOCOPY BOOLEAN) is
10322:
10323: cursor C_FIRST_DEAL is
10324: select 'Y'
10325: from XTR_DEALS
10326: where deal_no = rec.deal_no
10327: and deal_type not in ('CA', 'IG', 'ONC', 'NI')
10328: and first_reval_batch_id is NOT NULL
10329: and first_reval_batch_id <> rec.batch_id;