[Home] [Help]
49:
50: l_exist number;
51:
52: cursor NUM is
53: select XTR_REVALUATION_DETAILS_S.NEXTVAL
54: from DUAL;
55:
56: -- Get Volatility (O, V) rows from Market Price table
57: cursor OPTION_VOLS is
354: and event_code = 'REVAL';
355:
356: rec xtr_revl_rec;
357: l_rc NUMBER;
358: r_rd XTR_REVALUATION_DETAILS%rowtype;
359: l_cur VARCHAR2(1); -- cursor holder
360: l_cur1 VARCHAR2(1);
361: l_dirname VARCHAR2(240);
362: --retcode NUMBER;
420: /********************************************************/
421: PROCEDURE xtr_revl_main(
422: p_batch_id IN NUMBER,
423: rec IN OUT NOCOPY xtr_revl_rec,
424: r_rd IN OUT NOCOPY XTR_REVALUATION_DETAILS%rowtype,
425: retcode OUT NOCOPY NUMBER) IS
426: l_buf Varchar2(500);
427: l_batch_end DATE;
428: l_batch_start DATE;
500: Cursor c_fx_hedge_deal is
501: Select * from XTR_FX_ELIGIBLE_DEALS_V
502: where eligible_date <= l_batch_end and company_code = l_company_code
503: and deal_no in (select h.primary_code
504: from xtr_hedge_relationships H, xtr_revaluation_details R
505: where h.instrument_item_flag = 'U'
506: and r.batch_id = p_batch_id
507: and h.hedge_attribute_id = r.deal_no);
508:
509: Cursor c_fx_deal is
510: Select * from XTR_FX_ELIGIBLE_DEALS_V
511: where eligible_date <= l_batch_end and company_code = l_company_code
512: and deal_no not in (select h.primary_code
513: from xtr_hedge_relationships H, xtr_revaluation_details R
514: where h.instrument_item_flag = 'U'
515: and r.batch_id = p_batch_id
516: and h.hedge_attribute_id = r.deal_no);
517:
622: rec.fair_value := l_fv;
623:
624: if l_tmp.effective_date <= l_batch_end and
625: (rec.status_code = 'EXPIRED' or rec.status_code = 'EXERCISED') then
626: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
627: rel_pl_value := rec.fair_value - rec.init_fv;
628: xtr_revl_exchange_rate(rec, retcode);
629: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
630: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
629: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
630: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
631: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
632:
633: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
634: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
635: unrel_pl_value := rel_pl_value - cum_pl_value;
636: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
637: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
665: rec.fair_value := l_fv;
666:
667: if l_rc = 0 then
668: if l_tmp.effective_date <= l_batch_end and rec.status_code = 'SETTLED' then
669: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
670: rel_pl_value := rec.fair_value - rec.init_fv;
671: xtr_revl_exchange_rate(rec, retcode);
672: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
673: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
672: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
673: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
674: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
675:
676: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
677: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
678: unrel_pl_value := rel_pl_value - cum_pl_value;
679: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
680: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
711:
712: ---
713: /********************* Hedge associated FX revaluation ***********************/
714: /* This process needs to be done after Hedge item been revalued. So system */
715: /* system can find hedge related FX deals based on xtr_revaluation_details */
716: /* table. */
717: /*****************************************************************************/
718: for l_tmp in c_fx_hedge_deal loop
719: xtr_get_deal_value(l_tmp, rec, r_rd);
727: for l_tmp in c_fx_deal loop
728: xtr_get_deal_value(l_tmp, rec, r_rd);
729:
730: if l_tmp.effective_date <= l_batch_end then
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
754: xtr_revl_fx_curr_gl(rec, l_hedge_flag, TRUE, currency_gl);
755: xtr_revl_real_log(rec, rel_pl_value, rec.fair_value, rel_pl_value,
756: currency_gl, r_rd, retcode);
757:
758: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
759: -- For spot, we only show one realized record. This part is not needed
760: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
761: unrel_pl_value := rel_pl_value - cum_pl_value;
762: xtr_revl_fx_curr_gl(rec, l_hedge_flag, FALSE, currency_gl);
796: if l_rc = 0 then
797: if l_tmp.effective_date <= l_batch_end and
798: (rec.status_code = 'EXPIRED' or rec.status_code = 'EXERCISED') then
799: -- The deal is mature. We may calculate realized g/l
800: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
801: rel_pl_value := rec.fair_value - rec.init_fv;
802: xtr_revl_exchange_rate(rec, retcode);
803: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
804: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
803: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
804: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
805: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
806:
807: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
808: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
809: unrel_pl_value := rel_pl_value - cum_pl_value;
810: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
811: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
834: if l_rc = 0 then
835: if l_tmp.effective_date <= l_batch_end and
836: (rec.status_code = 'EXPIRED' or rec.status_code = 'EXERCISED') then
837: -- The deal is mature. We may calculate realized g/l
838: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
839: rel_pl_value := rec.fair_value - rec.init_fv;
840: xtr_revl_exchange_rate(rec, retcode);
841: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
842: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
841: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
842: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
843: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
844:
845: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
846: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
847: unrel_pl_value := rel_pl_value - cum_pl_value;
848: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
849: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
872:
873: if l_rc = 0 then
874: if l_tmp.effective_date <= l_batch_end then
875: -- The deal is mature. We may calculate realized g/l
876: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
877: rel_pl_value := rec.fair_value - rec.init_fv;
878: xtr_revl_exchange_rate(rec, retcode);
879:
880: if rec.discount_yield = 'Y' then
889: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
890: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl,r_rd, retcode);
891: end if;
892:
893: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
894: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
895: unrel_pl_value := rel_pl_value - cum_pl_value;
896: if rec.discount_yield = 'N' then
897: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
979: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
980: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
981: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
982:
983: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
984: unrel_pl_value := rel_pl_value - cum_pl_value;
985: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
986: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
987: xtr_revl_unreal_log(rec, unrel_pl_value, cum_pl_value, fv_sob_amt,
1016: rec.fair_value := l_fv;
1017:
1018: if l_tmp.effective_date <= l_batch_end then
1019: -- The deal is mature. We may calculate realized g/l
1020: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
1021: rel_pl_value := rec.fair_value - rec.init_fv;
1022: xtr_revl_exchange_rate(rec, retcode);
1023: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
1024: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
1023: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
1024: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
1025: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
1026:
1027: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
1028: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
1029: unrel_pl_value := rel_pl_value - cum_pl_value;
1030: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
1031: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
1052:
1053: if l_tmp.effective_date <= l_batch_end and
1054: (rec.status_code = 'EXPIRED' or rec.status_code = 'EXERCISED') then
1055: -- The deal is mature. We may calculate realized g/l
1056: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
1057: rel_pl_value := rec.fair_value - rec.init_fv;
1058: xtr_revl_exchange_rate(rec, retcode);
1059: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
1060: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
1059: xtr_revl_get_curr_gl(rec, null, rel_pl_value,
1060: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
1061: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, currency_gl, r_rd, retcode);
1062:
1063: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
1064: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
1065: unrel_pl_value := rel_pl_value - cum_pl_value;
1066: xtr_revl_get_curr_gl(rec, unrel_pl_value, null,
1067: fv_sob_amt, rel_sob_gl, unrel_sob_gl, currency_gl);
1087:
1088: if l_rc = 0 then
1089: if l_tmp.effective_date <= l_batch_end then
1090: -- The deal is mature. We may calculate realized g/l
1091: -- Insert realized g/l info to XTR_REVALUATION_DETAILS
1092: rel_pl_value := rec.fair_value - rec.init_fv;
1093: xtr_get_fv_from_batch(rec, l_dummy, l_dummy1, cum_pl_value, l_dummy2);
1094: unrel_pl_value := rel_pl_value - cum_pl_value;
1095: xtr_revl_tmm_curr_gl(rec, rel_curr_gl, unrel_curr_gl);
1098: unrel_sob_gl := unrel_pl_value * rec.reval_ex_rate_one;
1099: rel_sob_gl := rel_pl_value * rec.reval_ex_rate_one;
1100: xtr_revl_real_log(rec, rel_pl_value, fv_sob_amt, rel_sob_gl, rel_curr_gl, r_rd, retcode);
1101:
1102: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
1103: xtr_revl_unreal_log(rec, unrel_pl_value, cum_pl_value, fv_sob_amt,
1104: unrel_sob_gl, unrel_curr_gl, r_rd, retcode);
1105:
1106: else -- calculate unrealized g/l
1317: /********************************************************/
1318: PROCEDURE xtr_get_deal_value(
1319: l_tmp IN xtr_eligible_rec,
1320: rec IN OUT NOCOPY xtr_revl_rec,
1321: r_rd OUT NOCOPY XTR_REVALUATION_DETAILS%rowtype) is
1322:
1323: Cursor C_ITEM_TOTAL is
1324: select sum(reference_amount)
1325: from xtr_hedge_relationships
1384: rec.swap_ref := l_tmp.swap_ref;
1385: rec.eligible_date := l_tmp.eligible_date;
1386: rec.status_code := l_tmp.status_code;
1387:
1388: /* determine the period_from value for XTR_REVALUATION_DETAILS */
1389: /* If it's first reval for the deal, use begin date. Otherwise */
1390: /* use batch start date which is already defaulted */
1391: xtr_first_reval(rec, l_first);
1392: if l_first = TRUE then
2246: end if;
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 */
2313: xtr_risk_debug_pkg.dlog('xtr_revl_exchange_rate: ' || 'ex rate ' , rec.deal_ex_rate_one);
2314: xtr_risk_debug_pkg.dpop('xtr_revl_exchange_rate: ' || 'deal_date exchange rate');
2315: END IF;
2316:
2317: -- Get XTR_REVALUATION_DETAILS table exchange_rate_one, exchange_rate_two, and Fx forwad rate
2318: If rec.effective_date <= rec.revldate then -- deal is realized. Used maturity date rate
2319: If rec.deal_type = 'FX' then
2320: GL_CURRENCY_API.get_triangulation_rate
2321: (l_base_ccy, rec.sob_ccy, rec.effective_date, rec.ex_rate_type,
2487: -- not the first reval batch. compare with last batch
2488: l_pre_batch := xtr_get_pre_batchid(rec);
2489: select exchange_rate_one, sob_fair_value_amount
2490: into l_pre_gl_rate, l_pre_sob_fv
2491: from XTR_REVALUATION_DETAILS
2492: where deal_no = rec.deal_no
2493: and transaction_no = rec.trans_no
2494: and nvl(realized_flag, 'N') = 'N'
2495: and batch_id = l_pre_batch;
2628: else
2629: l_pre_batch_id := xtr_get_pre_batchid(rec);
2630: select exchange_rate_one
2631: into l_unrel_start_rate
2632: from XTR_REVALUATION_DETAILS
2633: where batch_id = l_pre_batch_id
2634: and deal_no = rec.deal_no
2635: and nvl(realized_flag, 'N') = 'N';
2636: end if;
2729: else
2730: l_pre_batch_id := xtr_get_pre_batchid(rec);
2731: select exchange_rate_one
2732: into l_unrel_start_rate
2733: from XTR_REVALUATION_DETAILS
2734: where batch_id = l_pre_batch_id
2735: and deal_no = rec.deal_no
2736: and nvl(realized_flag, 'N') = 'N';
2737: end if;
2885: l_unrel_cur_gl NUMBER;
2886: l_rel_cur_gl NUMBER;
2887: l_pre_batch_id NUMBER;
2888: l_rc NUMBER:= 0;
2889: r_rd XTR_REVALUATION_DETAILS%rowtype;
2890: l_trans_rate0 NUMBER;
2891: l_trans_rate1 NUMBER;
2892: l_round NUMBER;
2893:
2982: l_pre_batch_id := xtr_get_pre_batchid(rec);
2983: if l_pre_batch_id <> -1 then
2984: select exchange_rate_one, (period_to + 1)
2985: into l_unrel_start_rate, l_last_unrel_date
2986: from XTR_REVALUATION_DETAILS
2987: where batch_id = l_pre_batch_id
2988: and company_code = rec.company_code
2989: and account_no = rec.account_no
2990: and effective_date = l_bal_date0
3068: end if;
3069:
3070: ----------------------------------------------------------------------
3071: -- Calculate unrealized G/L for the first record of the pair and insert
3072: -- into xtr_revaluation_details table
3073: ----------------------------------------------------------------------
3074: l_unrel_cur_gl := round((l_bal_amt0 * (l_unrel_end_rate - l_unrel_start_rate)), l_round);
3075: rec.deal_subtype := 'INVEST';
3076: rec.fair_value := l_bal_amt0;
3105:
3106: if l_decrease <> 0 then
3107: ----------------------------------------------------------------------
3108: -- Calculate realized G/L for the second record of the pair and insert
3109: -- into xtr_revaluation_details table
3110: ----------------------------------------------------------------------
3111: l_rel_cur_gl := round((l_decrease * (l_ex_rate1 - l_avg_rate0)), l_round);
3112: rec.fair_value := l_bal_amt1;
3113: rec.face_value := l_bal_amt1;
3202: l_pre_batch_id := xtr_get_pre_batchid(rec);
3203: if l_pre_batch_id <> -1 then
3204: select exchange_rate_one, (period_to + 1)
3205: into l_unrel_start_rate, l_last_unrel_date
3206: from XTR_REVALUATION_DETAILS
3207: where batch_id = l_pre_batch_id
3208: and company_code = rec.company_code
3209: and account_no = rec.account_no
3210: and effective_date = l_bal_date0
3352: l_pre_balance NUMBER;
3353: l_unrel_cur_gl NUMBER;
3354: l_rel_cur_gl NUMBER;
3355: l_pre_batch_id NUMBER;
3356: r_rd XTR_REVALUATION_DETAILS%rowtype;
3357: l_product0 VARCHAR2(10);
3358: l_product1 VARCHAR2(10);
3359: l_port0 VARCHAR2(7);
3360: l_port1 VARCHAR2(7);
3481: l_pre_batch_id := xtr_get_pre_batchid(rec);
3482: if l_pre_batch_id <> -1 then
3483: select exchange_rate_one, (period_to +1)
3484: into l_unrel_start_rate, l_last_unrel_date
3485: from XTR_REVALUATION_DETAILS
3486: where batch_id = l_pre_batch_id
3487: and deal_no = rec.deal_no
3488: and deal_type = rec.deal_type
3489: and transaction_no = l_trans_no0
3555: end if;
3556:
3557: ------------------------------------------------------------------------
3558: -- Calculate unrealized G/L for the first record of the pair and insert
3559: -- into xtr_revaluation_details table
3560: ------------------------------------------------------------------------
3561: l_unrel_cur_gl := round((l_bal_amt0 * (l_unrel_end_rate - l_unrel_start_rate)), l_round);
3562: rec.trans_no := l_trans_no0;
3563: rec.fair_value := l_bal_amt0;
3585: xtr_revl_unreal_log(rec, 0, 0, 0, 0, l_unrel_cur_gl, r_rd, retcode);
3586: if l_decrease <> 0 then
3587: ------------------------------------------------------------------------
3588: -- Calculate realized G/L for the second record of the pair and insert
3589: -- into xtr_revaluation_details table
3590: ------------------------------------------------------------------------
3591: l_rel_cur_gl := round((l_decrease * (l_ex_rate1 - l_avg_rate0)), l_round);
3592: rec.trans_no := l_trans_no1;
3593: rec.fair_value := l_bal_amt1;
3677: l_pre_batch_id := xtr_get_pre_batchid(rec);
3678: if l_pre_batch_id <> -1 then
3679: select exchange_rate_one, (period_to +1)
3680: into l_unrel_start_rate, l_last_unrel_date
3681: from XTR_REVALUATION_DETAILS
3682: where batch_id = l_pre_batch_id
3683: and deal_no = rec.deal_no
3684: and deal_type = rec.deal_type
3685: and transaction_no = l_trans_no0
3816: l_rel_curr_gl NUMBER;
3817: l_round NUMBER;
3818: l_int_action VARCHAR2(7);
3819: l_accum_int NUMBER;
3820: r_rd XTR_REVALUATION_DETAILS%rowtype;
3821: l_rc NUMBER := 1; -- return code
3822: l_rowid VARCHAR2(30);
3823: r_err_log err_log; -- record type
3824:
3964: else -- this deal is not the first time reval
3965: l_pre_batch_id := xtr_get_pre_batchid(rec);
3966: select exchange_rate_one
3967: into l_unrel_start_rate
3968: from XTR_REVALUATION_DETAILS
3969: where batch_id = l_pre_batch_id
3970: and deal_no = rec.deal_no
3971: and transaction_no = rec.trans_no
3972: and deal_type = rec.deal_type
4022: if rec.deal_subtype = 'FUND' then
4023: l_rel_curr_gl := l_rel_curr_gl * (-1);
4024: end if;
4025:
4026: -- Insert a realized G/L row into XTR_REVALUATION_DETAILS table
4027: select min(start_date)
4028: into l_rel_start_date
4029: from XTR_ROLLOVER_TRANSACTIONS
4030: where cross_ref_to_trans = rec.trans_no;
4121: retcode NUMBER;
4122: l_buf Varchar2(500);
4123: cursor c_fv is
4124: select fair_value, cumm_gain_loss_amount, reval_rate
4125: from xtr_revaluation_details
4126: where DEAL_NO = rec.deal_no
4127: and TRANSACTION_NO = rec.trans_no
4128: and nvl(realized_flag, 'N') = 'N'
4129: and BATCH_ID = l_batch_id
4130: order by period_to desc; -- bug 4214521 issue 1
4131:
4132: cursor c_disc is
4133: select ni_disc_amount
4134: from xtr_revaluation_details
4135: where DEAL_NO = rec.deal_no
4136: and TRANSACTION_NO = rec.trans_no
4137: and BATCH_ID = l_batch_id;
4138:
4778: select batch_id
4779: from xtr_batches
4780: where COMPANY_CODE = rec.company_code
4781: and PERIOD_END = (select max(b.period_end)
4782: from xtr_batches b, xtr_revaluation_details r
4783: where b.BATCH_ID <> rec.batch_id
4784: and r.deal_no = rec.deal_no
4785: and b.batch_id = r.batch_id)
4786: and nvl(upgrade_batch, 'N') <> 'Y'
5531: l_buff Varchar2(500);
5532:
5533: cursor c_fx is
5534: select 'Y'
5535: from XTR_REVALUATION_DETAILS R,
5536: XTR_BATCHES B
5537: where r.deal_no = rec.deal_no
5538: and r.batch_id = b.batch_id
5539: and r.batch_id <> rec.batch_id
6025: /***************************************************************/
6026: /* This procedure handel hedge associated FX deals revaluations*/
6027: /***************************************************************/
6028: PROCEDURE xtr_revl_fv_fxh (rec IN OUT NOCOPY xtr_revl_rec,
6029: r_rd IN OUT NOCOPY XTR_REVALUATION_DETAILS%rowtype) IS
6030:
6031: /***************************************************/
6032: /* This cursor returns the max of batch end date */
6033: /* that includes the FX deals */
6033: /* that includes the FX deals */
6034: /***************************************************/
6035: cursor C_LAST_END_DATE is
6036: select max(b.period_end)
6037: from xtr_batches b, xtr_revaluation_details r
6038: where b.batch_id = r.batch_id
6039: and b.company_code = rec.company_code
6040: and b.batch_type is NULL
6041: and r.deal_no = rec.deal_no;
6130: xtr_revl_fx_curr_gl(rec, l_hedge_flag, TRUE, currency_gl);
6131: xtr_revl_real_log(rec, rel_pl_value, rec.fair_value, rel_pl_value,
6132: currency_gl, r_rd, retcode);
6133:
6134: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
6135: xtr_get_prev_fv_rate(rec, r_prev_hedge);
6136: unrel_pl_value := rel_pl_value - r_prev_hedge.cum_pl;
6137: xtr_revl_fx_curr_gl(rec, l_hedge_flag, FALSE, currency_gl);
6138: xtr_revl_unreal_log(rec, unrel_pl_value, r_prev_hedge.cum_pl,
6369: and reclass_date >= p_start_date
6370: and reclass_date < p_end_date;
6371:
6372: l_buff Varchar2(500);
6373: r_rd XTR_REVALUATION_DETAILS%rowtype;
6374: retcode NUMBER;
6375: l_first BOOLEAN;
6376: l_reclass_id NUMBER;
6377: l_cur_hedge_amt NUMBER;
6520:
6521:
6522:
6523: l_buff Varchar2(500);
6524: r_rd XTR_REVALUATION_DETAILS%rowtype;
6525: retcode NUMBER;
6526: l_reclass_id NUMBER;
6527: l_cur_hedge_amt NUMBER;
6528: l_last_reval BOOLEAN;
6823: r_prev_hedge OUT NOCOPY xtr_prev_hedge) IS
6824:
6825: cursor C_GL_RATE is
6826: select fair_value, exchange_rate_one, cumm_gain_loss_amount
6827: from XTR_REVALUATION_DETAILS
6828: where deal_no = rec.deal_no
6829: and company_code = rec.company_code
6830: and period_to = (select max(period_to)
6831: from xtr_revaluation_details
6827: from XTR_REVALUATION_DETAILS
6828: where deal_no = rec.deal_no
6829: and company_code = rec.company_code
6830: and period_to = (select max(period_to)
6831: from xtr_revaluation_details
6832: where company_code = rec.company_code
6833: and deal_no = rec.deal_no
6834: and nvl(realized_flag, 'N') = 'N');
6835:
6835:
6836: Cursor C_FWD_RATE is
6837: select fair_value, reval_rate, CTR_CURR_SOB_CURR_FWD_RATE,
6838: cumm_gain_loss_amount
6839: from XTR_REVALUATION_DETAILS
6840: where deal_no = rec.deal_no
6841: and company_code = rec.company_code
6842: and period_to = (select max(period_to)
6843: from xtr_revaluation_details
6839: from XTR_REVALUATION_DETAILS
6840: where deal_no = rec.deal_no
6841: and company_code = rec.company_code
6842: and period_to = (select max(period_to)
6843: from xtr_revaluation_details
6844: where company_code = rec.company_code
6845: and deal_no = rec.deal_no
6846: and nvl(realized_flag, 'N') = 'N');
6847:
7651: l_pre_batch := xtr_get_pre_batchid(rec);
7652: select fair_value, face_value, exchange_rate_one, cumm_gain_loss_amount
7653: into bo_rec.start_fair_value, bo_rec.maturity_face_value,
7654: bo_rec.pre_gl_rate, bo_rec.cum_unrel_gl
7655: from XTR_REVALUATION_DETAILS
7656: where batch_id = l_pre_batch
7657: and deal_no = rec.deal_no
7658: and nvl(realized_flag, 'N') = 'N'
7659: and transaction_no = 1;
7734: /*********************************************************/
7735: PROCEDURE xtr_revl_bond_realamc(rec IN OUT NOCOPY xtr_revl_rec,
7736: bo_rec IN xtr_bond_rec) IS
7737: l_buf Varchar2(500);
7738: r_rd XTR_REVALUATION_DETAILS%rowtype;
7739: l_begin_fv NUMBER;
7740: rel_pl_value NUMBER;
7741: fv_sob_amt NUMBER;
7742: rel_sob_gl NUMBER;
7797: PROCEDURE xtr_revl_bond_realmtm(rec IN OUT NOCOPY xtr_revl_rec,
7798: bo_rec IN xtr_bond_rec,
7799: p_resale IN BOOLEAN) IS
7800: l_buf Varchar2(500);
7801: r_rd XTR_REVALUATION_DETAILS%rowtype;
7802: l_begin_fv NUMBER;
7803: l_bond_init_fv NUMBER;
7804: rel_pl_value NUMBER;
7805: fv_sob_amt NUMBER;
7933: bo_rec IN OUT NOCOPY xtr_bond_rec,
7934: p_resale IN BOOLEAN,
7935: p_overwrite IN BOOLEAN) IS
7936: l_buf Varchar2(500);
7937: r_rd XTR_REVALUATION_DETAILS%rowtype;
7938: l_begin_fv NUMBER;
7939: unrel_pl_value NUMBER;
7940: fv_sob_amt NUMBER;
7941: rel_sob_gl NUMBER;
8030: rec.effective_date := rec.maturity_date;
8031: if rec.maturity_date <= rec.revldate then -- deal matured
8032: select realised_pl, reval_rate
8033: into l_mtm_real, l_reval_rate
8034: from XTR_REVALUATION_DETAILS
8035: where batch_id = rec.batch_id
8036: and deal_no = rec.deal_no
8037: and transaction_no = rec.trans_no
8038: and amount_type = 'REAL';
8705: l_pre_batch := xtr_get_pre_batchid(rec);
8706: select quantity, quantity, reval_rate, exchange_rate_one, cumm_gain_loss_amount
8707: into st_rec.init_quantity, st_rec.remaining_quantity, st_rec.prev_price,
8708: st_rec.pre_gl_rate, st_rec.cum_unrel_gl
8709: from XTR_REVALUATION_DETAILS
8710: where batch_id = l_pre_batch
8711: and deal_no = rec.deal_no
8712: and nvl(realized_flag, 'N') = 'N'
8713: and transaction_no = 1;
8775: /*********************************************************/
8776: PROCEDURE xtr_revl_stock_real(rec IN OUT NOCOPY xtr_revl_rec,
8777: st_rec IN xtr_stock_rec) is
8778: l_buf Varchar2(500);
8779: r_rd XTR_REVALUATION_DETAILS%rowtype;
8780: rel_pl_value NUMBER;
8781: fv_sob_amt NUMBER;
8782: rel_sob_gl NUMBER;
8783: unrel_sob_gl NUMBER;
8847: cum_pl_value IN OUT NOCOPY NUMBER,
8848: currency_gl IN OUT NOCOPY NUMBER) IS
8849:
8850: l_buf Varchar2(500);
8851: r_rd XTR_REVALUATION_DETAILS%rowtype;
8852: fv_sob_amt NUMBER;
8853: rel_sob_gl NUMBER;
8854: unrel_sob_gl NUMBER;
8855: l_resale_cum_gl NUMBER;
8908: if l_first = FALSE then -- not the first reval
8909: l_pre_batch := xtr_get_pre_batchid(rec);
8910: select exchange_rate_one
8911: into rec.deal_ex_rate_one
8912: from XTR_REVALUATION_DETAILS
8913: where deal_no = rec.deal_no
8914: and transaction_no = 1
8915: and nvl(realized_flag, 'N') = 'N'
8916: and batch_id = l_pre_batch;
8977: if l_first = FALSE then -- not the first reval
8978: l_pre_batch := xtr_get_pre_batchid(rec);
8979: select fair_value
8980: into l_begin_fv
8981: from XTR_REVALUATION_DETAILS
8982: where deal_no = rec.deal_no
8983: and transaction_no = 1
8984: and nvl(realized_flag, 'N') = 'N'
8985: and batch_id = l_pre_batch;
9833: end t_log_count;
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(
9844: cum_pl_value IN NUMBER,
9845: fv_sob_amt IN NUMBER,
9846: unrel_sob_gl IN NUMBER,
9847: currency_gl IN NUMBER,
9848: r in XTR_REVALUATION_DETAILS%rowtype,
9849: retcode OUT NOCOPY NUMBER,
9850: p_hedge_flag IN VARCHAR2 DEFAULT NULL) IS
9851:
9852: l_ROWID VARCHAR2(20);
9889: for update of last_batch_id NOWAIT;
9890:
9891:
9892: begin
9893: select XTR_REVALUATION_DETAILS_S.NEXTVAL
9894: into l_reval_detail_id from DUAL;
9895:
9896: -- we consider the deal is complete if all FV P/L and currency G/L has been derived
9897: if rec.fair_value is not null and currency_gl is not null
9900: else
9901: l_complete := 'N';
9902: End if;
9903:
9904: XTR_REVALUATION_DETAILS_PKG.INSERT_ROW(
9905: X_ROWID => l_ROWID,
9906: X_REVALUATION_DETAILS_ID=> l_reval_detail_id,
9907: X_REVAL_CURR_FV_AMOUNT => rec.fair_value,
9908: X_COMPLETE_FLAG => l_complete,
10036: end xtr_revl_unreal_log;
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 (
10046: rel_pl_value IN NUMBER,
10047: fv_sob_amt IN NUMBER,
10048: rel_sob_gl IN NUMBER,
10049: currency_gl IN NUMBER,
10050: r in XTR_REVALUATION_DETAILS%rowtype,
10051: retcode OUT NOCOPY NUMBER) IS
10052:
10053: l_ROWID VARCHAR2(20);
10054: l_reval_detail_id NUMBER;
10074: And DEAL_TYPE = 'NI'
10075: for update of FIRST_REVAL_BATCH_ID NOWAIT;
10076:
10077: begin
10078: select XTR_REVALUATION_DETAILS_S.NEXTVAL
10079: into l_reval_detail_id from DUAL;
10080:
10081: -- we consider the deal is complete if all FV P/L and currency G/L has been derived
10082: if rec.fair_value is not null and currency_gl is not null then
10084: else
10085: l_complete := 'N';
10086: End if;
10087:
10088: XTR_REVALUATION_DETAILS_PKG.INSERT_ROW(
10089: X_ROWID => l_ROWID,
10090: X_REVALUATION_DETAILS_ID=> l_reval_detail_id,
10091: X_REVAL_CURR_FV_AMOUNT => rec.fair_value,
10092: X_COMPLETE_FLAG => l_complete,
10380: l_transaction_no IN NUMBER,
10381: l_deal_type IN VARCHAR2) is
10382:
10383: Begin
10384: Delete from xtr_revaluation_details
10385: where deal_no = l_deal_no
10386: and transaction_no = l_transaction_no
10387: and deal_type = l_deal_type;
10388: