[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
1375: rec.swap_ref := l_tmp.swap_ref;
1376: rec.eligible_date := l_tmp.eligible_date;
1377: rec.status_code := l_tmp.status_code;
1378:
1379: /* determine the period_from value for XTR_REVALUATION_DETAILS */
1380: /* If it's first reval for the deal, use begin date. Otherwise */
1381: /* use batch start date which is already defaulted */
1382: xtr_first_reval(rec, l_first);
1383: if l_first = TRUE then
2229: end if;
2230: end xtr_revl_get_unrel_pl;
2231: --------------------------------------------------------------
2232: /*************************************************************************************/
2233: /* This procedure gets all rates information for XTR_REVALUATION_DETAILS and */
2234: /* XTR_DEALS */
2235: /* For Revaluation table: exchange_rate_one = reval_ccy/SOB ccy on revaluation date */
2236: /* except for FX, exchange_rate_one = base_ccy/SOB ccy on revaluation date */
2237: /* exchange_rate_two for FX = contra_ccy/SOB ccy on revaluation date */
2296: xtr_risk_debug_pkg.dlog('xtr_revl_exchange_rate: ' || 'ex rate ' , rec.deal_ex_rate_one);
2297: xtr_risk_debug_pkg.dpop('xtr_revl_exchange_rate: ' || 'deal_date exchange rate');
2298: END IF;
2299:
2300: -- Get XTR_REVALUATION_DETAILS table exchange_rate_one, exchange_rate_two, and Fx forwad rate
2301: If rec.effective_date <= rec.revldate then -- deal is realized. Used maturity date rate
2302: If rec.deal_type = 'FX' then
2303: GL_CURRENCY_API.get_triangulation_rate
2304: (l_base_ccy, rec.sob_ccy, rec.effective_date, rec.ex_rate_type,
2470: -- not the first reval batch. compare with last batch
2471: l_pre_batch := xtr_get_pre_batchid(rec);
2472: select exchange_rate_one, sob_fair_value_amount
2473: into l_pre_gl_rate, l_pre_sob_fv
2474: from XTR_REVALUATION_DETAILS
2475: where deal_no = rec.deal_no
2476: and transaction_no = rec.trans_no
2477: and nvl(realized_flag, 'N') = 'N'
2478: and batch_id = l_pre_batch;
2611: else
2612: l_pre_batch_id := xtr_get_pre_batchid(rec);
2613: select exchange_rate_one
2614: into l_unrel_start_rate
2615: from XTR_REVALUATION_DETAILS
2616: where batch_id = l_pre_batch_id
2617: and deal_no = rec.deal_no
2618: and nvl(realized_flag, 'N') = 'N';
2619: end if;
2712: else
2713: l_pre_batch_id := xtr_get_pre_batchid(rec);
2714: select exchange_rate_one
2715: into l_unrel_start_rate
2716: from XTR_REVALUATION_DETAILS
2717: where batch_id = l_pre_batch_id
2718: and deal_no = rec.deal_no
2719: and nvl(realized_flag, 'N') = 'N';
2720: end if;
2868: l_unrel_cur_gl NUMBER;
2869: l_rel_cur_gl NUMBER;
2870: l_pre_batch_id NUMBER;
2871: l_rc NUMBER:= 0;
2872: r_rd XTR_REVALUATION_DETAILS%rowtype;
2873: l_trans_rate0 NUMBER;
2874: l_trans_rate1 NUMBER;
2875: l_round NUMBER;
2876:
2965: l_pre_batch_id := xtr_get_pre_batchid(rec);
2966: if l_pre_batch_id <> -1 then
2967: select exchange_rate_one, (period_to + 1)
2968: into l_unrel_start_rate, l_last_unrel_date
2969: from XTR_REVALUATION_DETAILS
2970: where batch_id = l_pre_batch_id
2971: and company_code = rec.company_code
2972: and account_no = rec.account_no
2973: and effective_date = l_bal_date0
3051: end if;
3052:
3053: ----------------------------------------------------------------------
3054: -- Calculate unrealized G/L for the first record of the pair and insert
3055: -- into xtr_revaluation_details table
3056: ----------------------------------------------------------------------
3057: l_unrel_cur_gl := round((l_bal_amt0 * (l_unrel_end_rate - l_unrel_start_rate)), l_round);
3058: rec.deal_subtype := 'INVEST';
3059: rec.fair_value := l_bal_amt0;
3088:
3089: if l_decrease <> 0 then
3090: ----------------------------------------------------------------------
3091: -- Calculate realized G/L for the second record of the pair and insert
3092: -- into xtr_revaluation_details table
3093: ----------------------------------------------------------------------
3094: l_rel_cur_gl := round((l_decrease * (l_ex_rate1 - l_avg_rate0)), l_round);
3095: rec.fair_value := l_bal_amt1;
3096: rec.face_value := l_bal_amt1;
3185: l_pre_batch_id := xtr_get_pre_batchid(rec);
3186: if l_pre_batch_id <> -1 then
3187: select exchange_rate_one, (period_to + 1)
3188: into l_unrel_start_rate, l_last_unrel_date
3189: from XTR_REVALUATION_DETAILS
3190: where batch_id = l_pre_batch_id
3191: and company_code = rec.company_code
3192: and account_no = rec.account_no
3193: and effective_date = l_bal_date0
3335: l_pre_balance NUMBER;
3336: l_unrel_cur_gl NUMBER;
3337: l_rel_cur_gl NUMBER;
3338: l_pre_batch_id NUMBER;
3339: r_rd XTR_REVALUATION_DETAILS%rowtype;
3340: l_product0 VARCHAR2(10);
3341: l_product1 VARCHAR2(10);
3342: l_port0 VARCHAR2(7);
3343: l_port1 VARCHAR2(7);
3464: l_pre_batch_id := xtr_get_pre_batchid(rec);
3465: if l_pre_batch_id <> -1 then
3466: select exchange_rate_one, (period_to +1)
3467: into l_unrel_start_rate, l_last_unrel_date
3468: from XTR_REVALUATION_DETAILS
3469: where batch_id = l_pre_batch_id
3470: and deal_no = rec.deal_no
3471: and deal_type = rec.deal_type
3472: and transaction_no = l_trans_no0
3538: end if;
3539:
3540: ------------------------------------------------------------------------
3541: -- Calculate unrealized G/L for the first record of the pair and insert
3542: -- into xtr_revaluation_details table
3543: ------------------------------------------------------------------------
3544: l_unrel_cur_gl := round((l_bal_amt0 * (l_unrel_end_rate - l_unrel_start_rate)), l_round);
3545: rec.trans_no := l_trans_no0;
3546: rec.fair_value := l_bal_amt0;
3568: xtr_revl_unreal_log(rec, 0, 0, 0, 0, l_unrel_cur_gl, r_rd, retcode);
3569: if l_decrease <> 0 then
3570: ------------------------------------------------------------------------
3571: -- Calculate realized G/L for the second record of the pair and insert
3572: -- into xtr_revaluation_details table
3573: ------------------------------------------------------------------------
3574: l_rel_cur_gl := round((l_decrease * (l_ex_rate1 - l_avg_rate0)), l_round);
3575: rec.trans_no := l_trans_no1;
3576: rec.fair_value := l_bal_amt1;
3660: l_pre_batch_id := xtr_get_pre_batchid(rec);
3661: if l_pre_batch_id <> -1 then
3662: select exchange_rate_one, (period_to +1)
3663: into l_unrel_start_rate, l_last_unrel_date
3664: from XTR_REVALUATION_DETAILS
3665: where batch_id = l_pre_batch_id
3666: and deal_no = rec.deal_no
3667: and deal_type = rec.deal_type
3668: and transaction_no = l_trans_no0
3799: l_rel_curr_gl NUMBER;
3800: l_round NUMBER;
3801: l_int_action VARCHAR2(7);
3802: l_accum_int NUMBER;
3803: r_rd XTR_REVALUATION_DETAILS%rowtype;
3804: l_rc NUMBER := 1; -- return code
3805: l_rowid VARCHAR2(30);
3806: r_err_log err_log; -- record type
3807:
3947: else -- this deal is not the first time reval
3948: l_pre_batch_id := xtr_get_pre_batchid(rec);
3949: select exchange_rate_one
3950: into l_unrel_start_rate
3951: from XTR_REVALUATION_DETAILS
3952: where batch_id = l_pre_batch_id
3953: and deal_no = rec.deal_no
3954: and transaction_no = rec.trans_no
3955: and deal_type = rec.deal_type
4005: if rec.deal_subtype = 'FUND' then
4006: l_rel_curr_gl := l_rel_curr_gl * (-1);
4007: end if;
4008:
4009: -- Insert a realized G/L row into XTR_REVALUATION_DETAILS table
4010: select min(start_date)
4011: into l_rel_start_date
4012: from XTR_ROLLOVER_TRANSACTIONS
4013: where cross_ref_to_trans = rec.trans_no;
4104: retcode NUMBER;
4105: l_buf Varchar2(500);
4106: cursor c_fv is
4107: select fair_value, cumm_gain_loss_amount, reval_rate
4108: from xtr_revaluation_details
4109: where DEAL_NO = rec.deal_no
4110: and TRANSACTION_NO = rec.trans_no
4111: and nvl(realized_flag, 'N') = 'N'
4112: and BATCH_ID = l_batch_id
4113: order by period_to desc; -- bug 4214521 issue 1
4114:
4115: cursor c_disc is
4116: select ni_disc_amount
4117: from xtr_revaluation_details
4118: where DEAL_NO = rec.deal_no
4119: and TRANSACTION_NO = rec.trans_no
4120: and BATCH_ID = l_batch_id;
4121:
4761: select batch_id
4762: from xtr_batches
4763: where COMPANY_CODE = rec.company_code
4764: and PERIOD_END = (select max(b.period_end)
4765: from xtr_batches b, xtr_revaluation_details r
4766: where b.BATCH_ID <> rec.batch_id
4767: and r.deal_no = rec.deal_no
4768: and b.batch_id = r.batch_id)
4769: and nvl(upgrade_batch, 'N') <> 'Y'
5514: l_buff Varchar2(500);
5515:
5516: cursor c_fx is
5517: select 'Y'
5518: from XTR_REVALUATION_DETAILS R,
5519: XTR_BATCHES B
5520: where r.deal_no = rec.deal_no
5521: and r.batch_id = b.batch_id
5522: and r.batch_id <> rec.batch_id
6008: /***************************************************************/
6009: /* This procedure handel hedge associated FX deals revaluations*/
6010: /***************************************************************/
6011: PROCEDURE xtr_revl_fv_fxh (rec IN OUT NOCOPY xtr_revl_rec,
6012: r_rd IN OUT NOCOPY XTR_REVALUATION_DETAILS%rowtype) IS
6013:
6014: /***************************************************/
6015: /* This cursor returns the max of batch end date */
6016: /* that includes the FX deals */
6016: /* that includes the FX deals */
6017: /***************************************************/
6018: cursor C_LAST_END_DATE is
6019: select max(b.period_end)
6020: from xtr_batches b, xtr_revaluation_details r
6021: where b.batch_id = r.batch_id
6022: and b.company_code = rec.company_code
6023: and b.batch_type is NULL
6024: and r.deal_no = rec.deal_no;
6113: xtr_revl_fx_curr_gl(rec, l_hedge_flag, TRUE, currency_gl);
6114: xtr_revl_real_log(rec, rel_pl_value, rec.fair_value, rel_pl_value,
6115: currency_gl, r_rd, retcode);
6116:
6117: -- Also insert the last unrealized g/l info to XTR_REVALUATION_DETAILS
6118: xtr_get_prev_fv_rate(rec, r_prev_hedge);
6119: unrel_pl_value := rel_pl_value - r_prev_hedge.cum_pl;
6120: xtr_revl_fx_curr_gl(rec, l_hedge_flag, FALSE, currency_gl);
6121: xtr_revl_unreal_log(rec, unrel_pl_value, r_prev_hedge.cum_pl,
6352: and reclass_date >= p_start_date
6353: and reclass_date < p_end_date;
6354:
6355: l_buff Varchar2(500);
6356: r_rd XTR_REVALUATION_DETAILS%rowtype;
6357: retcode NUMBER;
6358: l_first BOOLEAN;
6359: l_reclass_id NUMBER;
6360: l_cur_hedge_amt NUMBER;
6501:
6502:
6503:
6504: l_buff Varchar2(500);
6505: r_rd XTR_REVALUATION_DETAILS%rowtype;
6506: retcode NUMBER;
6507: l_reclass_id NUMBER;
6508: l_cur_hedge_amt NUMBER;
6509: l_last_reval BOOLEAN;
6804: r_prev_hedge OUT NOCOPY xtr_prev_hedge) IS
6805:
6806: cursor C_GL_RATE is
6807: select fair_value, exchange_rate_one, cumm_gain_loss_amount
6808: from XTR_REVALUATION_DETAILS
6809: where deal_no = rec.deal_no
6810: and company_code = rec.company_code
6811: and period_to = (select max(period_to)
6812: from xtr_revaluation_details
6808: from XTR_REVALUATION_DETAILS
6809: where deal_no = rec.deal_no
6810: and company_code = rec.company_code
6811: and period_to = (select max(period_to)
6812: from xtr_revaluation_details
6813: where company_code = rec.company_code
6814: and deal_no = rec.deal_no
6815: and nvl(realized_flag, 'N') = 'N');
6816:
6816:
6817: Cursor C_FWD_RATE is
6818: select fair_value, reval_rate, CTR_CURR_SOB_CURR_FWD_RATE,
6819: cumm_gain_loss_amount
6820: from XTR_REVALUATION_DETAILS
6821: where deal_no = rec.deal_no
6822: and company_code = rec.company_code
6823: and period_to = (select max(period_to)
6824: from xtr_revaluation_details
6820: from XTR_REVALUATION_DETAILS
6821: where deal_no = rec.deal_no
6822: and company_code = rec.company_code
6823: and period_to = (select max(period_to)
6824: from xtr_revaluation_details
6825: where company_code = rec.company_code
6826: and deal_no = rec.deal_no
6827: and nvl(realized_flag, 'N') = 'N');
6828:
7632: l_pre_batch := xtr_get_pre_batchid(rec);
7633: select fair_value, face_value, exchange_rate_one, cumm_gain_loss_amount
7634: into bo_rec.start_fair_value, bo_rec.maturity_face_value,
7635: bo_rec.pre_gl_rate, bo_rec.cum_unrel_gl
7636: from XTR_REVALUATION_DETAILS
7637: where batch_id = l_pre_batch
7638: and deal_no = rec.deal_no
7639: and nvl(realized_flag, 'N') = 'N'
7640: and transaction_no = 1;
7715: /*********************************************************/
7716: PROCEDURE xtr_revl_bond_realamc(rec IN OUT NOCOPY xtr_revl_rec,
7717: bo_rec IN xtr_bond_rec) IS
7718: l_buf Varchar2(500);
7719: r_rd XTR_REVALUATION_DETAILS%rowtype;
7720: l_begin_fv NUMBER;
7721: rel_pl_value NUMBER;
7722: fv_sob_amt NUMBER;
7723: rel_sob_gl NUMBER;
7778: PROCEDURE xtr_revl_bond_realmtm(rec IN OUT NOCOPY xtr_revl_rec,
7779: bo_rec IN xtr_bond_rec,
7780: p_resale IN BOOLEAN) IS
7781: l_buf Varchar2(500);
7782: r_rd XTR_REVALUATION_DETAILS%rowtype;
7783: l_begin_fv NUMBER;
7784: l_bond_init_fv NUMBER;
7785: rel_pl_value NUMBER;
7786: fv_sob_amt NUMBER;
7914: bo_rec IN OUT NOCOPY xtr_bond_rec,
7915: p_resale IN BOOLEAN,
7916: p_overwrite IN BOOLEAN) IS
7917: l_buf Varchar2(500);
7918: r_rd XTR_REVALUATION_DETAILS%rowtype;
7919: l_begin_fv NUMBER;
7920: unrel_pl_value NUMBER;
7921: fv_sob_amt NUMBER;
7922: rel_sob_gl NUMBER;
8011: rec.effective_date := rec.maturity_date;
8012: if rec.maturity_date <= rec.revldate then -- deal matured
8013: select realised_pl, reval_rate
8014: into l_mtm_real, l_reval_rate
8015: from XTR_REVALUATION_DETAILS
8016: where batch_id = rec.batch_id
8017: and deal_no = rec.deal_no
8018: and transaction_no = rec.trans_no
8019: and amount_type = 'REAL';
8686: l_pre_batch := xtr_get_pre_batchid(rec);
8687: select quantity, quantity, reval_rate, exchange_rate_one, cumm_gain_loss_amount
8688: into st_rec.init_quantity, st_rec.remaining_quantity, st_rec.prev_price,
8689: st_rec.pre_gl_rate, st_rec.cum_unrel_gl
8690: from XTR_REVALUATION_DETAILS
8691: where batch_id = l_pre_batch
8692: and deal_no = rec.deal_no
8693: and nvl(realized_flag, 'N') = 'N'
8694: and transaction_no = 1;
8756: /*********************************************************/
8757: PROCEDURE xtr_revl_stock_real(rec IN OUT NOCOPY xtr_revl_rec,
8758: st_rec IN xtr_stock_rec) is
8759: l_buf Varchar2(500);
8760: r_rd XTR_REVALUATION_DETAILS%rowtype;
8761: rel_pl_value NUMBER;
8762: fv_sob_amt NUMBER;
8763: rel_sob_gl NUMBER;
8764: unrel_sob_gl NUMBER;
8828: cum_pl_value IN OUT NOCOPY NUMBER,
8829: currency_gl IN OUT NOCOPY NUMBER) IS
8830:
8831: l_buf Varchar2(500);
8832: r_rd XTR_REVALUATION_DETAILS%rowtype;
8833: fv_sob_amt NUMBER;
8834: rel_sob_gl NUMBER;
8835: unrel_sob_gl NUMBER;
8836: l_resale_cum_gl NUMBER;
8889: if l_first = FALSE then -- not the first reval
8890: l_pre_batch := xtr_get_pre_batchid(rec);
8891: select exchange_rate_one
8892: into rec.deal_ex_rate_one
8893: from XTR_REVALUATION_DETAILS
8894: where deal_no = rec.deal_no
8895: and transaction_no = 1
8896: and nvl(realized_flag, 'N') = 'N'
8897: and batch_id = l_pre_batch;
8958: if l_first = FALSE then -- not the first reval
8959: l_pre_batch := xtr_get_pre_batchid(rec);
8960: select fair_value
8961: into l_begin_fv
8962: from XTR_REVALUATION_DETAILS
8963: where deal_no = rec.deal_no
8964: and transaction_no = 1
8965: and nvl(realized_flag, 'N') = 'N'
8966: and batch_id = l_pre_batch;
9814: end t_log_count;
9815:
9816: --------------------------------------------------------
9817: /*****************************************************************/
9818: /* This procedure insert unrealized values into xtr_revaluation_details table*/
9819: /* Also update xtr_deals and xtr_rollover_transactions, */
9820: /* xtr_bank_balance, xtr_intergroup_transfers */
9821: /******************************************************************/
9822: PROCEDURE xtr_revl_unreal_log(
9825: cum_pl_value IN NUMBER,
9826: fv_sob_amt IN NUMBER,
9827: unrel_sob_gl IN NUMBER,
9828: currency_gl IN NUMBER,
9829: r in XTR_REVALUATION_DETAILS%rowtype,
9830: retcode OUT NOCOPY NUMBER,
9831: p_hedge_flag IN VARCHAR2 DEFAULT NULL) IS
9832:
9833: l_ROWID VARCHAR2(20);
9870: for update of last_batch_id NOWAIT;
9871:
9872:
9873: begin
9874: select XTR_REVALUATION_DETAILS_S.NEXTVAL
9875: into l_reval_detail_id from DUAL;
9876:
9877: -- we consider the deal is complete if all FV P/L and currency G/L has been derived
9878: if rec.fair_value is not null and currency_gl is not null
9881: else
9882: l_complete := 'N';
9883: End if;
9884:
9885: XTR_REVALUATION_DETAILS_PKG.INSERT_ROW(
9886: X_ROWID => l_ROWID,
9887: X_REVALUATION_DETAILS_ID=> l_reval_detail_id,
9888: X_REVAL_CURR_FV_AMOUNT => rec.fair_value,
9889: X_COMPLETE_FLAG => l_complete,
10017: end xtr_revl_unreal_log;
10018:
10019: --------------------------------------------------------------------
10020: /*****************************************************************/
10021: /* This procedure insert realized values into xtr_revaluation_details table*/
10022: /* Also update xtr_deals and xtr_rollover_transactions, */
10023: /* xtr_bank_balance, xtr_intergroup_transfers */
10024: /******************************************************************/
10025: PROCEDURE xtr_revl_real_log (
10027: rel_pl_value IN NUMBER,
10028: fv_sob_amt IN NUMBER,
10029: rel_sob_gl IN NUMBER,
10030: currency_gl IN NUMBER,
10031: r in XTR_REVALUATION_DETAILS%rowtype,
10032: retcode OUT NOCOPY NUMBER) IS
10033:
10034: l_ROWID VARCHAR2(20);
10035: l_reval_detail_id NUMBER;
10055: And DEAL_TYPE = 'NI'
10056: for update of FIRST_REVAL_BATCH_ID NOWAIT;
10057:
10058: begin
10059: select XTR_REVALUATION_DETAILS_S.NEXTVAL
10060: into l_reval_detail_id from DUAL;
10061:
10062: -- we consider the deal is complete if all FV P/L and currency G/L has been derived
10063: if rec.fair_value is not null and currency_gl is not null then
10065: else
10066: l_complete := 'N';
10067: End if;
10068:
10069: XTR_REVALUATION_DETAILS_PKG.INSERT_ROW(
10070: X_ROWID => l_ROWID,
10071: X_REVALUATION_DETAILS_ID=> l_reval_detail_id,
10072: X_REVAL_CURR_FV_AMOUNT => rec.fair_value,
10073: X_COMPLETE_FLAG => l_complete,
10361: l_transaction_no IN NUMBER,
10362: l_deal_type IN VARCHAR2) is
10363:
10364: Begin
10365: Delete from xtr_revaluation_details
10366: where deal_no = l_deal_no
10367: and transaction_no = l_transaction_no
10368: and deal_type = l_deal_type;
10369: